---
module: reports.queries
title: Saved Queries
tab: Internal
order: 20
audience: internal
---

> **Internal ops/support view.** Grounded in `hiveops-reports` source (`ReportQueryController`, `ReportQueryService`, `ReportQuery` entity, V3 migration). Verify against code before relying on specifics.

## What this is, operationally

A Saved Query is a named filter preset (date range + optional ATM list) attached to **one report template**. It does two jobs:
1. A ready-made option when a user clicks **Run** on a template.
2. The filter set used for a template's **scheduled** run (`report_templates.schedule_query_id`).

All of it lives in one service — **hiveops-reports** owns the `report_queries` table and the endpoints. There is no cross-service call, Kafka topic, or downstream dependency for query CRUD itself.

## Who can do what (ownership, not roles)

- Every `/api/reports/**` request just needs a **valid JWT** (`SecurityConfig` → `anyRequest().authenticated()`). There is **no `@PreAuthorize` role gate** on the queries endpoints — no `MSP_ADMIN`/`BCOS_ADMIN` requirement.
- Access is **owner-scoped**, not role-scoped. `ReportQueryService.requireTemplateOwner()` calls `templateRepository.findByIdAndCreatedByUserId(templateId, userId)`. A user can only list/create/edit/delete/schedule queries on **templates they created**.
- Consequence: a template belonging to user A is invisible to user B — including its queries. There is no admin override to manage another user's queries through this API; support must act as the owning user or touch the DB directly.

## First things to check when it misbehaves

| Symptom | Likely cause | Check |
|---|---|---|
| "Report template not found" (404) on any query action | Caller is **not the template creator** (or wrong template id). | `SELECT id, name, created_by_user_id FROM report_templates WHERE id = ?;` — compare to the caller's `sub` (userId) in their JWT. |
| "Query not found" (404) on edit/delete/set-scheduled | queryId not linked to that templateId (`findByIdAndTemplateId`), or query was deleted. | `SELECT id, template_id, name FROM report_queries WHERE id = ?;` |
| Scheduled report ran with no date/ATM filter | Template has `schedule_query_id = NULL`, or its query was deleted (delete auto-nulls it). | `SELECT id, schedule_enabled, schedule_query_id FROM report_templates WHERE id = ?;` |
| "Set Scheduled" button missing / schedule does nothing | Template needs a schedule turned on first; a template needs at least one query before it can run on a schedule. | Confirm `schedule_enabled = true` and at least one row in `report_queries` for that template. |
| Query saved but ATM filter ignored on run | `atm_ids` empty ⇒ **All ATMs** by design; or run-time `filterOverrides` in the Run request overrode the query (overrides win — see below). | `SELECT atm_ids FROM report_queries WHERE id = ?;` |
| "Invalid dateRangeType" (400) | Client sent a value outside the enum. | Allowed: `LAST_7_DAYS`, `LAST_30_DAYS`, `THIS_MONTH`, `LAST_MONTH`, `CUSTOM`, `ALL_TIME`. |
| ATM picker empty when creating a query | Backing device list comes from the reports `device_summary` mirror (Kafka-fed), which can go stale. | Check `hiveops.device.events` consumer lag; see [technical.reports]. |

## Behaviour that trips people up

- **Delete auto-clears the schedule.** Deleting the query that a template points to as `schedule_query_id` nulls that pointer (`ReportQueryService.delete`) — the template's schedule then runs with **no query filters** (all-time, all ATMs) until a new one is set.
- **Only one scheduled query per template.** `setScheduled` overwrites `schedule_query_id`; there is no multi-select.
- **Run-time overrides beat the saved query.** On a manual Run, if the caller passes `filterOverrides` (e.g. an ad-hoc ATM selection), those keys are merged **after** the query's filters and win. Scheduled runs pass no overrides, so the query is authoritative there.
- **Date ranges are computed at run time, in UTC.** `LAST_30_DAYS` etc. resolve against `LocalDate.now(UTC)` when the report runs, not when the query was saved. `CUSTOM` uses the stored `date_from`/`date_to`.
- **Queries don't fetch data.** A query only supplies `dateFrom`/`dateTo`/`atmIds` into the run's `filterOverrides`. If a report comes back empty or fails, the query is rarely the cause — look at the run and the downstream fetch (incident/journal/fleet). See [technical.reports].

## Direct DB peek (support)

DB `hiveiq_reports` on the database VM (`10.10.10.188`, via ProxyJump — not the services VM):

```sql
-- All queries for a template
SELECT id, name, date_range_type, date_from, date_to, atm_ids, created_at
FROM report_queries WHERE template_id = ? ORDER BY created_at;

-- Which query (if any) drives the schedule
SELECT t.id, t.name, t.schedule_enabled, t.schedule_query_id, q.name AS scheduled_query
FROM report_templates t LEFT JOIN report_queries q ON q.id = t.schedule_query_id
WHERE t.id = ?;
```

## Related tabs

- User-facing steps: this module's **Overview** tab.
- Build/data-flow detail: **Architect** tab and [technical.reports].
