Skip to content

SQL Generator (app.sql_gen)

Authoritative for the backend/app/sql_gen/ package, the per-metric routing surface, and the lineage columns on metrics_catalog. Pair with data-dictionary.md (the input artifact) and architecture.md § Boot order. Per ADR-PROTO-002, ADR-PROTO-004, ADR-PROTO-005.

Why this package exists

The Add Widget Clarifier (ADR-005, widget-builder.md) produces a WidgetSpec with a metric: MetricDefinition block (ADR-007). To resolve actual data for a Databricks-routed widget, three things must happen at request time:

  1. The metric's source_query template must be templated against the user's filters and bound parameters.
  2. The bound SQL must pass a safety layer (SELECT-only, allowlisted tables, LIMIT injection) — Prompt 3, lives in backend/app/sql_gen/safety.py.
  3. The SQL must execute against the right backend per config/metric_routing.yaml.

This package owns the dictionary loader + type mapper + routing config + boot-time validator (Prompt 2) AND the Bedrock-backed generator + safety layer + RFC 7807 route surface (Prompt 3). See docs/plans/active/prompt-3-sql-generator.md for the execution log.

Module map

Module Source Role
data_dictionary backend/app/sql_gen/data_dictionary.py Pydantic models — ColumnSchema, TableSchema, JoinKey, KpiDefinition, DataDictionary.
dictionary_loader backend/app/sql_gen/dictionary_loader.py stdlib-csv reader. Returns a DataDictionary; LRU-cached on (root, mtime).
type_mapper backend/app/sql_gen/type_mapper.py to_databricks_type() + equivalent(). Single arbiter of what a dictionary data_type means.
routing backend/app/sql_gen/routing.py YAML loader + boot validator (validate_routing_against_catalog).
exceptions backend/app/sql_gen/exceptions.py SqlGenError hierarchy mapped 1:1 to RFC 7807 kind values by the route layer.
safety backend/app/sql_gen/safety.py sqlglot-based validate(sql, ...) -> SafetyResult. Single-SELECT, allowlist, LIMIT injection / cap, forbidden constructs.
prompts backend/app/sql_gen/prompts.py build_anchored_prompt + curated few-shots for the three Databricks-routed metrics.
generator backend/app/sql_gen/generator.py End-to-end orchestrator: catalog → routing → subset → prompt → Bedrock → safety → optional execute → log.
routes backend/app/sql_gen/routes.py FastAPI router. Mounted at /v1/widgets; resolves to POST /v1/widgets/sql/generate.
__init__ backend/app/sql_gen/__init__.py Re-exports the public surface.

Read path

flowchart LR
    csvs["data-dictionary/<br/>4 CSVs + guidelines.md"]
    yaml["config/metric_routing.yaml"]
    catalog[("Postgres<br/>metrics_catalog")]

    loader["dictionary_loader.load_dictionary<br/>(LRU cache on root+mtime)"]
    mapper["type_mapper.to_databricks_type"]
    routes["routing.load_routing"]
    validator["routing.validate_routing_against_catalog"]

    csvs -->|read once| loader
    loader -->|DataDictionary| mapper
    yaml -->|read once| routes
    routes --> validator
    catalog --> validator
    validator -->|RuntimeError on miss| boot[("api lifespan")]

Dictionary loader

backend/app/sql_gen/dictionary_loader.py

  • Default root resolution. _resolve_default_root() walks: settings.data_dictionary_root → /app/data-dictionary (container default) → walk up from this file looking for data-dictionary/ (so pytest works against the repo).
  • Cache key. LRU-cached on (root_str, mtime_signature). The mtime tuple is the per-file stat().st_mtime for all five files; touching any CSV invalidates the cache on the next call without restarting the process.
  • Sparse-schema policy. A schema present in table_catalog.csv but absent from column_dictionary.csv is recorded in DataDictionary.sparse_schemas and the loader emits a WARN log. The loader does NOT raise. Today no schema is sparse — test_no_sparse_schemas_today in backend/tests/test_dictionary_loader.py asserts the empty list so a future revision dropping a schema's columns surfaces here.
  • ai_query_guidelines.md as DataDictionary.guidelines. Verbatim. Prompt 3's Bedrock system prompt will splice this in directly.
  • subset_for_tables(["l3_asurion.ev_claim", ...]). Returns a copy scoped to the requested FQNs plus any joins/KPIs that touch them. The SQL generator (Prompt 3) uses this to send a minimal slice to Bedrock instead of the full catalog.

Stdlib only — no pandas. The full catalog parses in ~10ms.

Type mapper

backend/app/sql_gen/type_mapper.py

The dictionary's column_dictionary.csv data_type column uses "<canonical>; <source-system-alias>" pairs (e.g. 'string; varchar', 'timestamp; timestamp(3)') plus simple atoms ('bigint', 'decimal(38,0)').

Dictionary value Databricks DDL type
string; varchar STRING
varchar STRING
int; integer INT
bigint BIGINT
double DOUBLE
float FLOAT
boolean / bool BOOLEAN
date DATE
timestamp; timestamp(3) TIMESTAMP
decimal(38,0) DECIMAL(38,0) (precision/scale preserved)

equivalent(a, b) collapses both sides through to_databricks_type (or, if the input is already a Databricks DDL string from DESCRIBE TABLE, normalises whitespace + uppercase) and compares.

Discipline rule. When the dictionary grows a new type, ADD it to _CANONICAL_TYPES rather than special-casing at the call site. The contract is "every dictionary type maps to exactly one Databricks DDL type, and equivalent is reflexive on that mapping". to_databricks_type raises ValueError on unknown types so dictionary drift surfaces at boot/seeder time, not as an opaque Databricks DDL parser error 30 seconds into make seed-databricks-l3.

Routing config

config/metric_routing.yaml. 13 entries today (10 Postgres + 3 Databricks).

1
2
3
4
5
routing:
  <metric_name>:
    backend: postgres | databricks
    sql_template: source_query | <inline-template>
    cache_seconds: <int>
  • Keyed by metric.name (snake_case), not metric_id. UUIDs are minted at boot from the catalog seeder; pinning routing to UUID would force YAML regeneration after every demo-reset. Names are stable across seedings.
  • sql_template: source_query is the default. It tells the data resolver to use metrics_catalog.source_query from the row. Inline templates are reserved for the §C.4.5 template-fallback path Prompt 3 will introduce.
  • Cache TTLs. Postgres metrics use 15-300s. Databricks metrics default to 300s because Serverless Starter cold-start is real and 300s caches across a demo while staying fresh enough to feel live.

A defensive entry exists for custom_widget_placeholder — a synthesized name from backend/app/widgets/nodes/spec_synthesizer.py that lands in the catalog if a custom widget gets persisted with an unresolved metric. Without the routing entry, that catalog row would crash boot (it did once — see lessons-learned.md § Old custom-widget orphan caught by routing fail-loud).

Boot validator (fail-loud)

backend/app/sql_gen/routing.py — validate_routing_against_catalog(conn). Wired into backend/app/main.py lifespan after the seeders so the validator observes the just-inserted catalog rows.

Direction Severity Behaviour
metrics_catalog row → no routing entry FATAL — raises RuntimeError Backend exits non-zero. Docker keeps the container in a restart loop. Operator sees the full traceback in docker compose logs api.
Routing entry → no catalog row WARN Logged. Useful for staging routing for an upcoming seeder. 
sequenceDiagram
    participant lifespan as app.main.lifespan
    participant val as validate_routing_against_catalog
    participant pg as Postgres
    participant yaml as config/metric_routing.yaml
    lifespan->>val: validate_routing_against_catalog(conn)
    val->>pg: SELECT name FROM metrics_catalog
    val->>yaml: load_routing()
    alt every catalog name has a routing entry
        val-->>lifespan: ok (logs counts)
    else any catalog name lacks an entry
        val-->>lifespan: raise RuntimeError("Unmapped metric_ids ...")
        lifespan-->>lifespan: lifespan never yields → container exits
    end

This is the literal mechanism described in PRD v2.1 §C.5.3. It mirrors ADR-008 fail-loud discipline into the routing path: a metric without a routing answer would silently fall back to mock data at request time, which is exactly the silent-fallback bug Part C exists to refuse.

Lineage columns on metrics_catalog

Schema in db/init.sql AND backend/app/metrics/catalog.py _TABLE_DDL (dual-DDL source of truth — see lessons-learned.md). The lineage block:

Column Type Purpose
source_schema TEXT (nullable) Warehouse schema, e.g. 'l3_asurion'. NULL for synthetic Postgres metrics.
source_table TEXT (nullable) Bare table name, e.g. 'ev_claim'.
source_query TEXT (nullable) Parameterised SQL with {time_window_start} placeholders. NULL for Postgres metrics (their formula is Holistics-style pseudo-SQL per ADR-007, not executable).
last_validated_at TIMESTAMPTZ Set by future schema-validation runs.
validation_status TEXT, CHECK in ('valid', 'drift_detected', 'unknown') Reserved for §B.5.4 governance.
governance_status TEXT, CHECK in ('approved', 'pending', 'deprecated') Reserved for §B.5.4 governance.
approved_by TEXT Reserved for §B.5.4 governance.

All optional and backward-compatible — v1 widget specs deserialize unchanged. The Pydantic mirror lives in backend/app/metrics/schemas.py; the frontend mirror lives in frontend/src/widgets/types.ts.

How a metric becomes Databricks-routed

Concrete example using claim_volume_l3_asurion. Source: backend/app/metrics/seed.py.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
CreateMetricBody(
    name="claim_volume_l3_asurion",
    label="Claim Volume (Asurion)",
    entity="ev_claim",                     # bare table name (MetricEntity Literal)
    source_schema="l3_asurion",            # the schema part lives here
    source_table="ev_claim",
    source_query=(
        "SELECT COUNT(DISTINCT claim_id) AS claim_count "
        "FROM l3_asurion.ev_claim "
        "WHERE claim_started_dttm_utc >= {time_window_start}"
    ),
    ...
)

Wired via config/metric_routing.yaml:

1
2
3
4
claim_volume_l3_asurion:
  backend: databricks
  sql_template: source_query
  cache_seconds: 300

At runtime the data resolver (Prompt 4, not yet in the code) will: read the routing entry → see backend: databricks → look up metrics_catalog.source_query → template {time_window_start} against the request → execute via app.databricks.client.

For the three live demo metrics, see demo-queries.md.

Prompt 3 — Generator + safety + route surface

Per PRD v2.1 §C.4. The SQL generator turns a (data_intent, metric_id) pair into a validated, optionally-executed Databricks SQL query. Catalog-anchored (ADR-PROTO-002), fail-loud (ADR-008).

Request flow (PRD v2.1 §C.4.2)

sequenceDiagram
    participant fe as Frontend / curl
    participant route as routes.py<br/>RFC 7807 mapper
    participant gen as generator.py<br/>orchestrator
    participant cat as metrics_catalog (Postgres)
    participant rcfg as routing.yaml
    participant dict as DataDictionary<br/>(LRU cached)
    participant llm as BedrockLlm<br/>Haiku 4.5, 4096 tokens
    participant safe as safety.py<br/>sqlglot dialect=databricks
    participant dbx as Databricks SQL Warehouse
    participant log as sql_generation_log

    fe->>route: POST /v1/widgets/sql/generate
    route->>gen: generate_sql(data_intent, metric_id, dry_run)
    gen->>cat: get_metric(by UUID or name)
    gen->>rcfg: routing.routing[metric.name]
    note right of gen: backend != databricks → MetricNotDatabricks (422)
    gen->>dict: subset_for_tables([source_schema.source_table, …])
    gen->>llm: generate_json(flat tool schema, max_tokens=4096)
    note right of llm: any failure → BedrockUnavailable<br/>(per-metric template_fallback opt-in)
    gen->>safe: validate(sql, allowlist, max_rows, default_limit)
    note right of safe: rejects forbidden_construct,<br/>multi_statement, non_allowlisted_table,<br/>limit_too_high; injects LIMIT if absent
    alt dry_run=true
        gen-->>route: GenerationResult (executed=false)
    else dry_run=false
        gen->>dbx: execute(modified_sql, timeout)
        gen-->>route: GenerationResult (executed=true)
    end
    gen->>log: INSERT one row (success OR failure)
    route-->>fe: 200 GenerateSqlResponse

Safety layer (PRD v2.1 §C.4.3)

backend/app/sql_gen/safety.py

Check kind Action on failure
Statement type — single SELECT only multi_statement Reject. Compound (UNION/INTERSECT/EXCEPT), DDL, or DML at top level all fall here.
Multi-statement input (semicolons) multi_statement / forbidden_construct Reject. parse() walks every statement; a trailing DROP after a leading SELECT is the canonical injection shape — caught here.
Forbidden constructs — DROP/DELETE/INSERT/UPDATE/MERGE/TRUNCATE/ALTER/CREATE forbidden_construct Reject. Matched against the parsed AST node types AND the stringly-typed Command wrapper, so vendor DDL can't sneak through.
Table allowlist non_allowlisted_table Reject. Allowlist is the FQNs in the dictionary subset for the metric's source table + joinable parents. CTE aliases are excluded from the check (they're not warehouse tables).
LIMIT injection (passing check limit_injected) Auto-inject LIMIT default_limit when absent. Modified SQL is what the orchestrator executes; original SQL is preserved in the log row.
LIMIT cap limit_too_high Reject when LIMIT N and N > max_result_rows.
sqlglot parse failure parse_error Reject. Always parses with dialect='databricks' per lessons-learned § sqlglot rejects valid SQL because of dialect.

Every parse uses sqlglot.parse_one(sql, dialect='databricks') AND sqlglot.parse(sql, dialect='databricks') (latter for multi-statement detection). Default dialect rejects valid Databricks constructs (STRUCT<…>, backtick identifiers, INTERVAL literals); the explicit dialect is non-negotiable.

Bedrock model + schema discipline (PRD v2.1 §C.4.5)

backend/app/sql_gen/generator.py:59

  • Model: Haiku 4.5 (us.anthropic.claude-haiku-4-5-20251001-v1:0) with max_tokens=4096. Sonnet 4 lacks tool-use access in the hackathon AWS account; the default max_tokens=1024 truncates non-trivial SQL.
  • Tool schema is FLAT. _SUBMIT_SQL_SCHEMA is { sql: string, tables_used: string[], explanation: string }. No top-level oneOf. No nested required fields. Anthropic tool-use rejects top-level oneOf; Haiku 4.5 silently drops deeply-nested fields.
  • Bedrock failure → 503, never silent fallback. _call_bedrock collapses LlmError and BuilderModeError into BedrockUnavailable. The orchestrator branches on config.template_fallback[metric_name] — present → opt-in templated SQL with source='databricks_template_only'; absent → 503. The default config map is empty, so the default behaviour is fail-loud.

Route shape

backend/app/sql_gen/routes.py — POST /v1/widgets/sql/generate. Five RFC 7807 paths plus the SQL-execute Databricks family:

HTTP kind Triggered by
422 free_text_rejected Request omitted metric_id (ADR-PROTO-002 anchoring rule).
404 metric_not_found metric_id does not resolve in metrics_catalog (UUID OR name).
422 metric_not_databricks Metric's routing entry is backend: postgres, or it lacks source_schema / source_table.
422 safety_violation Generated SQL failed at least one safety check. Body carries check_kind (one of forbidden_construct, non_allowlisted_table, multi_statement, limit_too_high, parse_error).
503 bedrock_unavailable LLM call failed AND the metric is not in template_fallback. Body carries retry_after_s.
503 databricks_* Execute-time Databricks failure (only on dry_run=false). Same RFC 7807 family as /v1/databricks/health.

All failure responses are Content-Type: application/problem+json literally; the route's tests assert this.

Anchored prompt construction

backend/app/sql_gen/prompts.py

  • build_anchored_prompt(...) returns (system, user).
  • The system prompt is stable across calls — encodes the structural contract (single SELECT, allowlisted tables, submit_sql tool with three flat fields, mandatory LIMIT, anti-pattern list).
  • The user prompt carries the request slice: the metric_anchor block (rendered from CatalogMetric), the dictionary subset (rendered compactly, ~2 KB for the 2-table demo subset), the verbatim ai_query_guidelines.md per ADR-PROTO-004, 1–3 hand-curated few-shots from _FEW_SHOTS, and the user's data_intent.
  • Few-shots are the highest-quality lever in the design (PRD §C.4.4). Adding a new Databricks-routed metric requires adding an entry to _FEW_SHOTS BEFORE enabling routing.

sql_generation_log

DDL: db/init.sql lines 184-204 AND _SQL_GEN_LOG_DDL in backend/app/metrics/catalog.py — same dual-DDL discipline as metrics_catalog. The lifespan calls ensure_sql_generation_log_table after _ensure_widgets_table so existing dev DBs pick it up without make demo-reset.

Column Purpose
request_id UUID surfaced in the API response body — cross-reference back to a row from a curl receipt.
metric_id NULL on free-text rejection; set otherwise. Index: idx_sqlgenlog_metric (metric_id, created_at DESC).
generated_sql Post-LIMIT-injection SQL (or empty string on Bedrock failure).
validation_result SafetyResult.model_dump_json() — full per-rule report.
executed Always FALSE on dry_run=true, safety reject, Bedrock failure.
error <kind>:<message> on any failure path.
input_hash sha256 of (metric_name + canonical data_intent) so repeat calls dedup.

Tests

Test Source What it asserts
test_dictionary_loader.py backend/tests/test_dictionary_loader.py All 4 schemas present, seeded tables have expected column counts, joins/kpis load, subset_for_tables scopes correctly, guidelines surface verbatim, LRU cache + invalidation work, type mapper handles every dictionary atom and rejects unknowns.
test_routing_validator.py backend/tests/test_routing_validator.py YAML parses; validator passes when catalog matches; fail-loud raises with the unmapped name in the message; reverse-direction WARN appears in caplog; missing-file + missing-block errors.
test_sql_safety.py backend/tests/test_sql_safety.py 25 cases. Each adversarial case asserts BOTH is_safe=False AND the specific check.kind. Forbidden constructs (8 variants), multi-statement, UNION, allowlist (incl. JOIN exfil), LIMIT injection / cap, parse-error robustness, config validation, plus Databricks-flavored positives (backticks, INTERVAL, CTE, GROUP BY/ORDER BY).
test_sql_generator.py backend/tests/test_sql_generator.py Unit half (RecordingLlm test double inside the file): wiring contract — system prompt markers, dictionary subset injection, few-shot count, max_tokens=4096, flat schema (no oneOf), log row written on every code path, BedrockUnavailable raised on stub failure (no silent fallback), template_fallback opt-in produces source='databricks_template_only', per-metric isolation. Live half (@pytest.mark.bedrock_live, opt-in via pytest -m bedrock_live): real Bedrock call against the 3 demo metrics, asserts safety pass + tables_used ⊆ {l3_asurion.ev_claim, l3_asurion.ev_product_catalog}.
test_sql_generator_routes.py backend/tests/test_sql_generator_routes.py FastAPI TestClient covers every RFC 7807 path: 422 free_text_rejected (missing + blank), 404 metric_not_found, 422 metric_not_databricks, 422 safety_violation (forbidden_construct + non_allowlisted_table), 503 bedrock_unavailable, 200 happy-path. Each error asserts Content-Type: application/problem+json literally + the kind discriminator + a sql_generation_log row written.
test_data_resolver.py backend/tests/test_data_resolver.py 19 tests covering the per-widget resolver. Postgres real-rows + latency, Databricks happy / dry_run with stubs, every graceful-degradation path (databricks_unavailable, databricks_auth_error, bedrock_unavailable, safety_violation), cache hit / miss / refresh, dry_run never cached, unknown-widget 404, no-metric legacy degradation. Stubs (StubLlm, StubDatabricksClient) defined in-file per CLAUDE.md "no mocks in production code".

Run make test to execute all six inside the api container. Live Bedrock tests run via docker exec -e AWS_PROFILE=… 2026-hackathon-api-1 pytest tests/test_sql_generator.py -m bedrock_live.

Per-widget data resolver

Owns POST /v1/widgets/{widget_id}/data — the dispatch layer the dashboard renderers (Prompt 5) consume to fetch live data per widget. Built on top of the SQL Generator: Databricks-routed widgets delegate to app.sql_gen.generate_sql under the hood; Postgres-routed widgets run a small allowlisted SQL map directly. Per PRD v2.1 §B.7.1 + §C.5, ADR-PROTO-005, ADR-008. Execution log: docs/plans/active/prompt-4-data-resolver.md.

Module map

Module Source Role
data_resolver backend/app/widgets/data_resolver.py resolve_widget_data(widget_id, refresh, dry_run) orchestrator. Loads widget → routing lookup → cache lookup → Postgres path OR Databricks path → cache write → response.
cache backend/app/widgets/cache.py Redis-backed JSON cache. get_cached / set_cached / build_cache_key. Defensive — Redis-down yields cache miss, never raises.
route backend/app/widgets/routes.py → resolve_data_endpoint FastAPI handler at POST /v1/widgets/{widget_id}/data. Returns DataResolverResponse on 200; 4xx are application/problem+json.

Request / response

sequenceDiagram
    autonumber
    participant Browser
    participant Route as POST /v1/widgets/{id}/data
    participant R as data_resolver
    participant C as Redis cache
    participant PG as Postgres
    participant SG as sql_gen.generate_sql
    participant DB as Databricks

    Browser->>Route: { refresh: false, dry_run: false }
    Route->>R: resolve_widget_data(widget_id, ...)
    R->>R: load widget + routing entry
    alt cache hit
        R->>C: get_cached(key)
        C-->>R: CachedEntry
        R-->>Route: cache_hit=true freshness=Ns
    else cache miss + backend=postgres
        R->>PG: SELECT … (allowlist)
        PG-->>R: rows
        R->>C: set_cached(entry, ttl=routing.cache_seconds)
        R-->>Route: source=postgres executed=true
    else cache miss + backend=databricks
        R->>SG: generate_sql(data_intent, metric, dry_run)
        SG->>DB: catalog → safety → execute
        alt happy path
            DB-->>SG: rows
            SG-->>R: GenerationResult (sql + rows)
            R-->>Route: source=bedrock executed=true generated_sql=…
        else databricks/bedrock/safety failure
            SG-->>R: typed exception
            R-->>Route: 200 + live_data_unavailable=true + data=spec.mock_data
        end
    end
    Route-->>Browser: DataResolverResponse

Routing dispatch

The routing config (config/metric_routing.yaml) has already been validated at boot by validate_routing_against_catalog. The resolver re-reads it once at module import and dispatches on MetricRouting.backend:

Backend Path Latency target Failure → response
postgres _run_postgres — small allowlisted SQL map keyed by metric.name. Runs via SQLAlchemy text(). <300ms p95 (PRD §C.10 #8) Metric not in _POSTGRES_QUERIES → graceful degradation source='postgres_unmapped'. SQL execution failure → source='postgres_query_error'. Both 200 + live_data_unavailable=true.
databricks _run_databricks — pure delegation to app.sql_gen.generate_sql. Re-fetches actual rows after the orchestrator's row-count-only pass. <3s p95 (PRD §C.10 #7) DatabricksError / BedrockUnavailable / SafetyViolation → 200 + live_data_unavailable=true with source carrying the failure kind. Caller errors (MetricNotFound, FreeTextRejected) still bubble as 4xx.

The Postgres allowlist today covers active_issues_count, claims_in_progress_count, cost_avoided_mtd, critical_alerts_count_15min, nba_taken_pct. Adding more is a one-line edit to _POSTGRES_QUERIES in data_resolver.py.

Graceful-degradation contract (acceptance #10)

Per PRD v2.1 §C.10 #10 and ADR-008, a warehouse-down or Bedrock-down event MUST return 200 with live_data_unavailable=true — structurally distinct from MockLlm substitution:

Field Value on graceful degradation
HTTP status 200 (NOT 500, NOT 503)
data The widget's baked-in spec.mock_data (set by the Clarifier at widget creation; mocks-as-opt-in)
source One of databricks_unavailable, databricks_auth_error, databricks_timeout, databricks_query_error, bedrock_unavailable, safety_violation, postgres_unmapped, postgres_query_error, no_metric
live_data_unavailable true
error_kind matches source
error_detail the upstream typed exception's message (e.g. "Bedrock invoke failed: ExpiredTokenException")
backend the routing decision is preserved (postgres or databricks) so the frontend SourceBadge knows which color band applies

Caller errors are different: an unknown widget_id → 404 application/problem+json kind='widget_not_found'. A metric_id that doesn't exist (only reachable when the resolver is called with an inline override) → 404 metric_not_found. These are NOT warehouse problems and don't degrade.

Cache

Key shape: widget:data:{widget_id}:{spec_hash}:{intent_hash} where: - spec_hash = first 12 hex chars of sha256(json.dumps(spec, sort_keys=True)). A spec edit naturally invalidates every cached row for that widget. - intent_hash = same, computed against the request's data_intent.

TTL = MetricRouting.cache_seconds from the routing yaml (Postgres metrics: 15-300s; Databricks metrics: 300s).

Behavior:

  • refresh=false (default): cache hit returns immediately with cache_hit=true freshness_seconds=age.
  • refresh=true: bypasses cache; the fresh result IS still written back so the next call gets a hit.
  • dry_run=true: never reads or writes cache (it's a user-driven preview).
  • Redis-down: silent miss + WARN log. The dashboard keeps working; cached rows just don't survive the outage.

Tests + receipts

19 tests in backend/tests/test_data_resolver.py cover every path including in-file StubLlm + StubDatabricksClient doubles for the Databricks path. Live curl receipts under artifacts/prompt-4-data-resolver/20260506/ prove the Postgres real-rows + latency (5-13ms vs 300ms bar) and the graceful-degradation contract end-to-end against an expired AWS Bedrock token.

Frontend wiring (Prompt 5)

The dashboard rail consumes this endpoint via the useWidgetData SWR-style hook in frontend/src/widgets/useWidgetData.ts, refresh interval driven by spec.data_intent.refresh_seconds per ADR-007. Each tile carries a SourceBadge chip whose color band is a literal switch on the resolver's source enum (postgres → green, bedrock / databricks → purple "Databricks · live" with click-to-expand SQL, databricks_template_only → amber "template", any live_data_unavailable=true source → amber "Mock · live data unavailable"). The default branch is assertNever(source) — a future resolver-side enum addition surfaces as a TypeScript compile error rather than a silent green pill. Live receipts in artifacts/prompt-5-frontend-wiring/20260506-120610/ include 5 sequential cached Databricks-routed curls (p95=56.3ms steady-state), a healthy-state dashboard screenshot (purple Databricks · 7s ago tile showing real claim_count=5000), a Databricks-down screenshot (amber chip on the one Databricks-routed tile while the v1 KPI strip + Postgres-routed tiles are visually unchanged), and a restored-state screenshot proving the chip flips back. Mirrors PRD v2.1 §C.10 #9 + #10.

Cross-references