---
module: reports.queries
title: Saved Queries
tab: Architect
order: 30
audience: dev
---

> **Architect view.** Grounded in `hiveops-reports` source. Cross-links: [platform.data-architecture], [platform.kafka], [platform.service-ownership], [technical.reports].

## Ownership & scope

Saved Queries are **entirely owned by hiveops-reports** ([platform.service-ownership]). No other service reads or writes `report_queries`; there is **no Kafka topic and no cross-service call** in the query CRUD path. This is a self-contained sub-feature of the report builder, not a distributed flow.

The only external touch is indirect: the **ATM picker** that populates a query's `atm_ids` is fed by the reports-local `device_summary` mirror (endpoint `GET /api/devices/atms`), which is itself Kafka-fed from `hiveops.device.events` — see [technical.reports] and [platform.kafka]. Queries store bare `Long` ATM ids; they do not resolve or validate them against that mirror at save time.

## Components

| Layer | Class | Role |
|---|---|---|
| Controller | `controller.ReportQueryController` | REST at `/api/reports/templates/{templateId}/queries` |
| Service | `service.ReportQueryService` | CRUD + owner enforcement + schedule pointer |
| Entity | `entity.ReportQuery` | maps `report_queries`; `toFilterOverrides()` = the date/ATM → filter-map logic |
| Repository | `repository.ReportQueryRepository` | `findByTemplateIdOrderByCreatedAtAsc`, `findByIdAndTemplateId`, `findById` |
| DTOs | `CreateQueryRequest`, `UpdateQueryRequest`, `ReportQueryResponse` | request/response shaping |
| Consumer | `service.ReportRunService` | reads a query's filters into a run |

## Data model

Table **`report_queries`** (Flyway `V3__add_report_queries.sql`):

| Column | Type | Notes |
|---|---|---|
| `id` | BIGSERIAL PK | |
| `uuid` | UUID UNIQUE | `gen_random_uuid()` default; also set app-side |
| `template_id` | BIGINT FK → `report_templates(id)` | **ON DELETE CASCADE** — delete a template, its queries go with it |
| `name` | VARCHAR(255) NOT NULL | |
| `date_range_type` | VARCHAR(20) | enum, default `ALL_TIME` |
| `date_from` / `date_to` | DATE | used only when `CUSTOM` |
| `atm_ids` | JSONB | `List<Long>`; empty/null ⇒ all ATMs |
| `created_at` / `updated_at` | TIMESTAMPTZ | Hibernate `@CreationTimestamp`/`@UpdateTimestamp` |

Index: `idx_report_queries_template (template_id)`.

Two FK pointers added in the same V3 migration tie queries into the report lifecycle:

- `report_templates.schedule_query_id BIGINT FK → report_queries(id)` **ON DELETE SET NULL** — which query drives the cron schedule.
- `report_runs.query_id BIGINT FK → report_queries(id)` **ON DELETE SET NULL** — which query a run used (audit).

See [platform.data-architecture] for the DB-per-service model (`hiveiq_reports`, split `DB_HOST/DB_PORT/DB_NAME` connection).

## Enum → filter resolution (the core logic)

`ReportQuery.DateRangeType` = `LAST_7_DAYS, LAST_30_DAYS, THIS_MONTH, LAST_MONTH, CUSTOM, ALL_TIME`.

`ReportQuery.toFilterOverrides()` turns a query into a `Map<String,Object>` at **run time**, resolving relative ranges against `LocalDate.now(ZoneOffset.UTC)`:

- `LAST_7_DAYS` → `dateFrom = today-7`, `dateTo = today`
- `LAST_30_DAYS` → `dateFrom = today-30`, `dateTo = today`
- `THIS_MONTH` → `dateFrom = firstOfMonth`, `dateTo = today`
- `LAST_MONTH` → `dateFrom = firstOfPrevMonth`, `dateTo = lastOfPrevMonth`
- `CUSTOM` → stored `date_from`/`date_to`
- `ALL_TIME` → no date keys
- Non-empty `atm_ids` → `atmIds` key added.

## Data flow — query → report run

Queries feed the **executor** stage of the report pipeline (the `executor → Kafka → record-store` pattern is at the *distribution* end of reports, not here — see [technical.reports]):

**Manual run** — `POST /api/reports/templates/{id}/run` with `{ queryId, filterOverrides }`:
1. `ReportRunService.triggerUserRun` loads the template (`findByIdAndCreatedByUserId` OR `findByIdAndSharedTrue`).
2. `resolveFilters(queryId, overrides)` builds the effective filter map:
   - if `queryId != null`: `effective.putAll(query.toFilterOverrides())`
   - then `effective.putAll(overrides)` — **request overrides win over the saved query**.
3. A `report_runs` row is persisted with `query_id` + `filter_overrides`, then `executeAsync` runs the fetch using the **caller's forwarded bearer token**.

**Scheduled run** — `ReportSchedulerService` (`@Scheduled` fixed-delay) → `triggerScheduledRun(template)`:
1. `queryId = template.getScheduleQueryId()`.
2. `resolveFilters(queryId, null)` — no overrides, so the query is authoritative.
3. Run executes under a self-minted ADMIN JWT from `InternalTokenGenerator` (not a user token). See [technical.reports] gotchas.

Downstream, the resolved `filterOverrides` (dateFrom/dateTo/atmIds among them) are passed to the data-source fetcher (`INCIDENTS`/`TRANSACTIONS`/`FLEET_TASKS`/etc.), which makes live paginated HTTP calls to incident/journal/fleet. Queries never store fleet data.

## Boundaries & invariants

- **One template ↔ many queries**, ordered by `created_at`. **One template ↔ at most one scheduled query** (`schedule_query_id`, overwritten by `setScheduled`, nulled by `clearScheduled` or by deleting the pointed-to query).
- **Owner isolation** is enforced only at the service layer via `created_by_user_id`, not by DB constraint or role. Shared templates are runnable by others (`findByIdAndSharedTrue`) but query **management** stays owner-only.
- No events emitted on query create/update/delete — nothing to reconcile, nothing downstream to notify.

## Related

- Service-wide build/data facts: [technical.reports]
- Platform patterns: [platform.data-architecture], [platform.kafka], [platform.service-ownership]
