---
module: reports.queries
title: Saved Queries
tab: Claude
order: 40
audience: dev
---

> Terse agent reference. Only confirmed facts. Owner service: **hiveops-reports** (all query CRUD is self-contained; no other service, no Kafka topic in this path).

## Owns
- **Service:** `hiveops-reports` (Spring Boot). DB `hiveiq_reports`. Table **`report_queries`**.
- Nothing else reads/writes `report_queries`. No producer/consumer for queries.

## Endpoints (all under JWT; `anyRequest().authenticated()`, no role gate)
Base: `/api/reports/templates/{templateId}/queries` — `ReportQueryController`

| Method | Path | Action | Success |
|---|---|---|---|
| GET | `/api/reports/templates/{templateId}/queries` | list (ordered by created_at) | 200 |
| POST | `/api/reports/templates/{templateId}/queries` | create | 201 |
| PUT | `/api/reports/templates/{templateId}/queries/{queryId}` | update (partial; only non-null fields) | 200 |
| DELETE | `/api/reports/templates/{templateId}/queries/{queryId}` | delete (nulls `schedule_query_id` if it pointed here) | 204 |
| POST | `/api/reports/templates/{templateId}/queries/{queryId}/set-scheduled` | set template's scheduled query | 204 |
| DELETE | `/api/reports/templates/{templateId}/queries/scheduled` | clear scheduled query | 204 |

Related (consume a query): `POST /api/reports/templates/{id}/run` body `{ queryId?, filterOverrides? }`.

## Request bodies
- **Create** (`CreateQueryRequest`): `name` (required, NotBlank), `dateRangeType` (default `ALL_TIME`), `dateFrom`, `dateTo`, `atmIds: Long[]`.
- **Update** (`UpdateQueryRequest`): same fields, all optional; null field = unchanged.
- `dateRangeType` enum: `LAST_7_DAYS | LAST_30_DAYS | THIS_MONTH | LAST_MONTH | CUSTOM | ALL_TIME`. Anything else → 400 "Invalid dateRangeType".

## DB
- Table `report_queries`: `id, uuid, template_id (FK report_templates ON DELETE CASCADE), name, date_range_type, date_from, date_to, atm_ids (JSONB), created_at, updated_at`. Index `idx_report_queries_template`.
- `report_templates.schedule_query_id` (FK → report_queries, ON DELETE SET NULL).
- `report_runs.query_id` (FK → report_queries, ON DELETE SET NULL).
- Migration: `V3__add_report_queries.sql`. DB conn = split `DB_HOST/DB_PORT/DB_NAME` (NOT `DB_URL`).

## Kafka
- **None for queries.** (Service-level: reports produces `hiveops.reports.completed`, consumes `hiveops.device.events` — unrelated to query CRUD.)

## Auth / access
- JWT required; principal = `ReportsPrincipal(userId, email, role, institutionKey)`.
- Access is **owner-scoped**, not role-scoped: every action does `templateRepository.findByIdAndCreatedByUserId(templateId, principal.userId())` → 404 "Report template not found" if caller isn't the template creator.
- No `MSP_ADMIN`/`BCOS_ADMIN` requirement on these endpoints.

## Gotchas
- 404 "Report template not found" ⇒ caller isn't the template's `created_by_user_id` (or wrong id) — **not** a missing query.
- 404 "Query not found" ⇒ `findByIdAndTemplateId` miss (queryId not under that templateId).
- Deleting the scheduled query auto-nulls `schedule_query_id`; schedule then runs all-time/all-ATM.
- On run: query filters merged first, then request `filterOverrides` merged after → **overrides win**.
- Date ranges resolve at run time against `LocalDate.now(UTC)`, not save time. `CUSTOM` uses stored dates.
- Empty/null `atm_ids` ⇒ all ATMs.
- Only one scheduled query per template (`setScheduled` overwrites).
- ATM picker list comes from reports-local `device_summary` (Kafka-fed, can be stale).

## Do NOT
- Do NOT add a role/`@PreAuthorize` claim for these endpoints — enforcement is ownership via `created_by_user_id`.
- Do NOT invent a `deviceIds` field: backend DTOs accept **only `atmIds`**. (Frontend `api.ts` types list an optional `deviceIds`, but the entity/DTO/migration have no such column — it is silently dropped.)
- Do NOT expect a Kafka event on query create/update/delete — there is none.
- Do NOT treat an empty/failed report as a query bug — data comes from the run's downstream fetch (incident/journal/fleet), not the query.
- Do NOT run DB queries against the services VM — `hiveiq_reports` is on the DB VM `10.10.10.188` (ProxyJump).
- Do NOT assume `resolveFilters` validates the query belongs to the run's template — it loads by `queryId` alone (see uncertainties).
