Query Templates

Query templates are a reusable library of named SQL queries over the data warehouse. Each template has an ID, human-readable name, description, SQL, and one or more categories: Overview, Credit, Deposits, Risk, Compliance, Growth, or Dashboard — the last is the tag that pins a template to the operators' home dashboard.

Templates can be edited and deleted after creation. People refer to templates by name; clients use their IDs to select the intended template. Names need not be unique. Creating or updating a template stores its SQL without executing it or checking it against the warehouse schema.

Shipped catalog

The Lana application owns a catalog of default queries covering bank snapshots, currency composition, loan risk, account restrictions, customer growth, monthly deposit inflows, and summary counts and balances.

Defaults are installed automatically at startup, identified by their fixed IDs rather than names. Seeding is idempotent and concurrency-safe: existing templates are preserved rather than overwritten, and missing defaults are added. Changing a shipped name does not create another template. Deleting a shipped template is respected: startup seeding does not reinstall a template that was explicitly deleted.

Partner summaries exclude closed partners. Obligation summaries exclude completed obligations and break overdue and defaulted obligations out by their current lifecycle status, not scheduled transition dates. The margin-call watchlist includes zero-collateral facilities with outstanding debt and shows them first. The frozen/closed account summary shows only the 10 most recently modified accounts currently in either state, not accounts that necessarily changed state today.

The shipped SQL is parsed and checked against the read-only query policy in memory before seeding. This does not execute the queries or check whether the referenced warehouse tables or columns exist. The catalog is tested for unique, non-nil IDs and read-only SQL, independently of a running warehouse.

Admin panel

Open Reports → Query Templates to browse the library. The paginated list can be sorted by name or creation time and filtered by category. Open a template to inspect its description, categories and copyable SQL. Add Query Template and the detail page's Edit open full-page forms — SQL authoring has the whole content area, so completion tooltips are never constrained by a dialog. Delete asks for confirmation and removes the template from the library; the detail page returns to the list afterwards.

The SQL editor autocompletes as you type: SQL keywords from the PostgreSQL dialect (the dialect the warehouse query validator parses), and the modeled data-mart layer — table names after FROM/JOIN, columns after SELECT/WHERE — with each model's dbt description shown in the completion details. The create and edit pages also have a Test run button that executes the SQL currently in the editor without saving the template, under the same read-only policy and row cap as the detail page's run.

Running a template

Select Run query on the detail page to execute its SQL. Queries do not run automatically when opening a template. Each run fetches fresh results from the warehouse, whose data reflects its latest refresh rather than live banking activity.

A single row with one column is shown as a value or text; other results appear in a table. Empty results, loading and execution errors are explicit. Queries are limited to 500 rows, with a notice when more rows exist than can be displayed. There is no per-query time limit. Running requires report-read permission in addition to access to the template library.

Exporting results

The Export action on the template page downloads the query result as a file: plain CSV or gzipped CSV (several times smaller, for large results). Exporting runs the query again on the server under the same read-only safeguards — it does not depend on a prior preview run — but with a higher row limit of 100,000 rows; the payload reports the exported row count and warns when that limit truncated the file. Exports require the same report-read permission as running a query.

Dashboard

A template on the dashboard is a template tagged with the Dashboard category, alongside its functional categories. Pinning is therefore template editing: toggle the Dashboard chip in the template's form and save. This requires query-template update permission; dashboard viewers can see the results of pinned queries, so pin only queries suitable for that audience. The dashboard API exposes display metadata and results, not SQL; browsing the SQL still requires query-template-viewer access.

Six catalog queries are installed pre-tagged with the Dashboard category:

Query	Other category
Active facilities	Credit
Customers	Growth
Checking accounts	Deposits
Partners (total, active, new)	Growth
Outstanding disbursed principal by currency	Credit
Collateral held by currency	Risk

Subsequent application starts preserve operator edits, including removing all tags. The latest-completed-bank-day query remains available in the library but is not tagged initially.

Cards are ordered by template name, using the library's value/text/table renderer. Each query runs on its own read-only connection with a 500-row cap. One failed query does not hide successful cards. Currency queries return native amounts as text to preserve decimal precision.

Refresh results reruns the queries without the Apollo cache. Last data update reports the latest warehouse pipeline completion time, including intraday refreshes; rerunning queries does not rebuild the warehouse or change that timestamp. The pending-actions list remains separate.

Before the first warehouse refresh, the freshness indicator shows its no-refresh state. An unbuilt mart produces a per-card error, not a fabricated zero. With no selected templates, the dashboard displays an empty state.

Admin API

• queryTemplate(id:) retrieves a template, including its SQL.
• queryTemplates(first:, after:, sort:, filter:) lists templates with cursor-based pagination, sortable by name or creation time and filterable by category (a row matches a filter when the category is among its tags).
• queryTemplateCreate(input:) creates a template with at least one category.
• queryTemplateUpdate(input:) updates a template's name, description, SQL, or categories. Updating with unchanged values writes no new events; updating an unknown ID returns a NOT_FOUND error payload, and an empty category list an AT_LEAST_ONE_CATEGORY_REQUIRED error payload.
• queryTemplateDelete(input:) deletes a template, returning the deleted ID; deleting an unknown ID returns a NOT_FOUND error payload.
• dashboard returns generic query cards and lastRefreshedAt.

The existing dwRunReadonlyQuery API can execute a template's SQL on demand. Execution validates the query and applies the warehouse read-only safeguards; storing a template does not guarantee that it will execute successfully. dwReadonlyQueryExportLinkGenerate(input:) exports the same validated query as a CSV or gzipped CSV file and returns its download link, the exported row count, and whether the 100,000-row export cap was hit.

Permissions

The query-template viewer permission allows listing and reading templates. The writer permission allows creating, updating, and deleting them. Administrators have both permissions; bank managers have viewer permission. Query execution uses the existing warehouse-query permission, independently of template management.

Creation, updates, and deletes are authorized and audited, with the template event and successful write audit committed together. SQL and free-text inputs are omitted from those spans.