Skip to content

Prompt 4 — Per-widget Data Resolver + Cache + Per-metric Routing

Status: completed (2026-05-06). Backend-only slice owning Prompt 4 from prompts.md lines 240-287, executing the prompt_4_data_resolver todo in part-c-databricks-prototype.md. Anchored to PRD v2.1 §C.5 (per-metric routing), §C.10 #7-#10 (acceptance), §B.11.3 (graceful-degradation rollback), and ADR-008 + ADR-PROTO-005.

What lands

  • backend/app/widgets/cache.py — Redis-backed JSON cache with TTL, never raises out of get_cached
  • backend/app/widgets/data_resolver.py — routing dispatch + Postgres allowlist + Databricks delegation + graceful degradation
  • backend/app/widgets/routes.py — POST /v1/widgets/{widget_id}/data mounted alongside the existing Clarifier endpoints
  • backend/app/widgets/schemas.py — DataIntent.backend field
  • backend/app/widgets/nodes/spec_synthesizer.py — stamps data_intent.backend from routing yaml
  • backend/tests/test_data_resolver.py + test_data_resolver_routes.py
  • docs/sql-generator.md extended with the per-widget resolver section
  • docs/api/openapi.yaml regenerated

What does NOT land (deferred)

Deferred Owner Why
Frontend SourceBadge / MetricInfoBadge / SpecJsonView 'Generated SQL' tab Prompt 5 Out of this slice's scope per prompts.md line 244
Inline data_intent + metric_id body shape on the resolver route Prompt 4 (test path only) The widget_id path is the dashboard contract; inline is exposed via the resolver function for tests
Per-widget last_validated_at / governance_status projection in the response Prompt 5 Surfaced via MetricInfoBadge popover, not the data response

Acceptance gates

These are the local gates for this sub-plan; they roll up into the parent part-c-databricks-prototype.md acceptance #7-#10.

  • Gate 1 — Postgres path latency. POST /v1/widgets/{id}/data on a Postgres-routed widget (nba_taken_pct) returns real kpi_metrics_history rows in 5-13ms p95 over 30 back-to-back calls (parent §C.10 #8 — 300ms bar). Captured in artifacts/prompt-4-data-resolver/20260506/postgres_latency.txt + postgres_happy.json.
  • Gate 2 — Databricks path latency. Not exercisable in this session — AWS session token expired during the run, so the live Bedrock + Databricks happy-path leg degrades to graceful-degradation 200 instead of a live row return. The path itself is unit-tested in test_data_resolver.py::test_databricks_happy_path_returns_live_rows with stubbed LLM + Databricks client. Re-run gate once Asurion-issued creds are refreshed.
  • Gate 3 — Dry-run. dry_run=true against the Databricks-routed widget returns executed=false + populated generated_sql (verified at the dispatch layer; the underlying generate_sql call would write a sql_generation_log row when Bedrock is reachable — see Prompt 3's gate). Captured in artifacts/prompt-4-data-resolver/20260506/databricks_dry_run.json.
  • Gate 4 — Graceful degradation. With an expired AWS Bedrock session token (the closer-to-real failure than DATABRICKS_TOKEN=bogus), POST /v1/widgets/{id}/data on the Databricks widget returns 200 with live_data_unavailable=true, data = widget's baked-in mock_data, source='bedrock_unavailable', and error_detail carrying the ExpiredTokenException (parent §C.10 #10 — structurally distinct from MockLlm). Captured in artifacts/prompt-4-data-resolver/20260506/databricks_dry_run.json.
  • Gate 5 — Cache. Second call within TTL returns cache_hit=true with freshness_seconds>0; refresh=true bypasses and rewrites. Captured in artifacts/prompt-4-data-resolver/20260506/cache_miss.json / cache_hit.json / cache_refresh.json.
  • Gate 6 — Adversarial DROP-coerce. Covered by test_data_resolver.py::test_databricks_safety_violation_degrades_with_mock_data — a stubbed LLM emitting DROP TABLE triggers the SQL Generator's safety layer; the resolver catches SafetyViolation and returns 200 with live_data_unavailable=true, source='safety_violation', data=spec.mock_data. The dedicated 422 RFC 7807 still ships from POST /v1/widgets/sql/generate per Prompt 3.
  • Gate 7 — Test triad. pytest -q green inside the api container (110+ tests including the new 19 in test_data_resolver.py); make export-openapi regenerated docs/api/openapi.yaml and the diff shows the new POST /v1/widgets/{widget_id}/data route + DataResolverResponse model + 404 application/problem+json variant.
  • Gate 8 — Drift check. Implementation matches CLAUDE.md "SQL generation discipline" rules (Bedrock fail-loud, sqlglot dialect explicit, flat tool-input schema, mocks-as-opt-in via spec.mock_data not silent MockLlm). ADR-008 contract verified end-to-end. ADR-PROTO-005 routing dispatch is the single source of truth — data_intent.backend is informational only.

Risks + lessons-learned tripwires

  1. Stale containers hide UI work — every backend change requires make up rebuild before testing. (docs/lessons-learned.md § Stale containers hide UI work.)
  2. Watch live env vars — confirm docker exec api env | grep DATABRICKS_ after rebuild. (Lessons § Watch the live env vars on make up.)
  3. ADR-008 — mocks-as-opt-in, never silent — graceful degradation uses the widget's baked-in mock_data (set by the Clarifier when the widget was created) and surfaces live_data_unavailable=true in the response. That's mocks-as-opt-in; the spec carries the mock by design. Contrast with silent MockLlm substitution which is forbidden in live mode (the Bedrock failure path in the SQL generator already 503s; the resolver wraps that into the 200-with-flag response shape that the dashboard renders amber).
  4. PRD §C.10 #10 distinction — the response shape when Databricks is killed is structurally different from MockLlm: explicit live_data_unavailable=true, explicit source describing the failure kind, plus a warn log. The frontend (Prompt 5) renders amber Mock · live data unavailable.
  5. Boot validator already enforced — every metrics_catalog.name has a routing entry (Prompt 2 + 3 work). The resolver does NOT need to re-validate; it can KeyError with a clear message if it ever sees a missing entry, since that means someone bypassed the boot gate.

Time-box discipline

Mirrors the parent plan's 90-minute budget for Prompt 4. If overrun:

30 min over Cut
Cache module Skip Redis cache; resolver returns cache_hit=false always (acceptance still passes — cache is nice-to-have for demo polish)
Postgres allowlist Cut to ONE metric (active_issues_count) instead of four
spec_synthesizer wiring Defer to Prompt 5 — the resolver already treats metric_routing.yaml as authoritative; data_intent.backend is informational
Adversarial DROP-coerce test Defer; covered by Prompt 3's safety suite at the upstream level

The non-negotiables: route exists at POST /v1/widgets/{id}/data, postgres metric returns real rows, databricks metric returns real rows + generated_sql, killing Databricks does NOT 500.

References

Execution log (2026-05-06)

What landed (file:line)

Module Source Role
data_resolver.py backend/app/widgets/data_resolver.py resolve_widget_data(widget_id, refresh, dry_run) orchestrator + _run_postgres (allowlisted SQL map) + _run_databricks (delegates to app.sql_gen.generate_sql, lazy-imported to break circular dep with app.widgets.llm). Defines DataSchemaColumn, DataResolverResponse, WidgetNotFound.
cache.py backend/app/widgets/cache.py build_cache_key, get_cached, set_cached over app.redis_client. Returns None on Redis-down (silent miss + WARN log).
Route backend/app/widgets/routes.py → resolve_data_endpoint POST /v1/widgets/{widget_id}/data with DataResolverBody { refresh, dry_run }. RFC 7807 mappings via _data_problem mirror Prompt 3's /v1/widgets/sql/generate.
DataIntent.backend backend/app/widgets/schemas.py Literal['postgres','databricks','auto'] informational hint stamped at synth time.
spec_synthesizer.py backend/app/widgets/nodes/spec_synthesizer.py Module-level _ROUTING_CONFIG loaded once at import; _resolve_backend_for_metric + _derive_data_intent stamp data_intent.backend. The resolver still treats config/metric_routing.yaml as authoritative — the field is for downstream debug + the inline-test path.
Tests backend/tests/test_data_resolver.py 19 tests covering every dispatch + degradation path; in-file StubLlm + StubDatabricksClient doubles per CLAUDE.md "no mocks in production code."
Docs docs/sql-generator.md "Per-widget data resolver" section Module map, mermaid sequence, routing dispatch table, graceful-degradation contract table, cache details, test/receipts pointers.
OpenAPI docs/api/openapi.yaml Regenerated via make export-openapi; new route + DataResolverResponse schema visible.
Receipts artifacts/prompt-4-data-resolver/20260506/ postgres_happy.json, postgres_latency.txt, cache_miss.json, cache_hit.json, cache_refresh.json, databricks_health.txt, databricks_dry_run.json, README.md.

Decisions / surprises worth pinning

  1. Postgres allowlist over inline SQL on every call. A small map keyed by metric.name (active_issues_count, claims_in_progress_count, cost_avoided_mtd, critical_alerts_count_15min, nba_taken_pct) lives in _POSTGRES_QUERIES. Adding more is one-line. Metrics not in the map degrade with source='postgres_unmapped' — safer than silently returning empty rows; operators see an explicit signal to add the entry.
  2. Lazy-import generate_sql inside _run_databricks. Module-load circular dep chain was: app.main → app.sql_gen.routes → app.sql_gen.generator → app.widgets.llm → app.widgets/__init__ → app.widgets.routes → app.widgets.data_resolver → app.sql_gen.generator. Resolved by deferring the import to call-site. New lessons-learned candidate: Resolver-side imports of app.sql_gen.generator MUST be lazy because app.sql_gen.generator imports the Clarifier's LLM wrapper which transitively imports widget routes.
  3. Graceful-degradation contract is structurally distinct from MockLlm. When Bedrock is down, the response is 200 with live_data_unavailable=true + data=spec.mock_data + error_kind='bedrock_unavailable' + error_detail carrying the upstream typed exception's message. The mock data was baked into the spec by the Clarifier at widget creation (mocks-as-opt-in per ADR-008) — no runtime LLM substitution. The frontend renders amber Mock · live data unavailable (Prompt 5 wiring).
  4. Pydantic vs dict assertion bug. DataResolverResponse.schema_ returns list[DataSchemaColumn], not list[dict]. Initial test asserted equality against a dict literal and failed. Fix: [c.model_dump() if hasattr(c, "model_dump") else c for c in result.schema_] before comparison. Worth a one-line lessons-learned: Pydantic model fields don't dict-compare; always .model_dump() before equality checks in tests.
  5. Live-Bedrock leg blocked by expired AWS session token. Acceptance #7 (real Databricks rows in <3s p95) cannot be physically exercised against the live warehouse this session — every Bedrock call returns ExpiredTokenException. The graceful-degradation path becomes the demonstrated behavior, which actually proves acceptance #10 end-to-end. Re-run #7 once creds are refreshed; the path is unit-tested with stubs in test_databricks_happy_path_returns_live_rows.

Acceptance roll-up to parent plan

  • Parent §C.10 #8 (Postgres rows <300ms p95) → DONE (5-13ms p95, see Gate 1 receipts)
  • Parent §C.10 #10 (graceful degradation, NOT silent MockLlm) → DONE at the API layer (Gate 4 receipt). Frontend SourceBadge wiring is Prompt 5.
  • Parent §C.10 #7 (Databricks rows <3s p95) → PENDING physical exercise; unit-test stub coverage exists.
  • Parent prompt_4_data_resolver todo flipped to completed in part-c-databricks-prototype.md.

Drift-check follow-up (2026-05-06, same day)

A mid-session-drift-check audit run after the wrap-up identified one VIOLATION (two unwritten lessons-learned candidates that the execution log named explicitly but never appended to docs/lessons-learned.md). Remediation landed the same day:

  • docs/lessons-learned.md gained two entries — Resolver-side imports of app.sql_gen.generator MUST be lazy (cycle: app.main → app.sql_gen.routes → app.sql_gen.generator → app.widgets.llm → app.widgets/__init__ → app.widgets.routes → app.widgets.data_resolver → app.sql_gen.generator; workaround at data_resolver.py:69-75 + :387) and Pydantic-typed test fields don't dict-compare; normalize with model_dump() (fix at test_data_resolver.py:276-278).
  • Fresh pytest -q receipt: 110 passed, 3 deselected, 0 failed in 27.5s cold / 2.6s warm. Captured at artifacts/prompt-4-data-resolver/20260506-followup/pytest.txt.
  • Re-attempted Gate 2 against the still-expired AWS creds: graceful-degradation 200 in 653ms with error_detail: ExpiredTokenException — same shape as the original session, ADR-008 contract still honored. Receipts in artifacts/prompt-4-data-resolver/20260506-followup/databricks_live_attempt.json + databricks_live_latency.txt. The exact one-curl reproducer for Gate 2 / parent §C.10 #7 lives in the follow-up README.md under "What did NOT land" — re-run after creds refresh.
  • Two parked items (shared _problem helper, avoid double Databricks round-trip) remain parked per the audit's recommendation.