Capture query statistics for usage pattern analysis

[cbeach47]

Describe the issue

1. Define query statistics for understanding query patterns - frequency of data elements requested, data sources used, frequency of data not found to answer user queries
2. Enhance Query Planner to gather query statistics
3. Enhance MDR to store query statistic
4. Implement query statistics visualizations

Additional context

Created on Thu Aug 7 21:56:09 UTC 2025 in the previous ticket tracking system as LIF-286

Open state: Backlog

[dereck-symmetry]

Instrumenting the Query Planner — investigation for #341

Status: investigation only, no code written. Prepared from issue-341-investigate-usage-statistics at commit 97ec8b1.

What it would actually take to capture query usage statistics — and three honest ways to go, including one that costs a single PR because the Query Planner already computes everything the issue asks for.

---

Recommendation

Start with Option A — structured logs — and write the statistics spec so B and C stay open.

The Query Planner already derives all three statistics the issue names and then discards them, so turning four existing log lines into structured JSON is close to free, and it's the cheapest way to find out whether anyone actually reads this data.

Options B and C build admin/monitoring storage inside MDR that ADR 0002 and #1041 have already decided belongs in a control-plane service. Doing that now means doing it twice. I'd hold them until #512 gets scheduled or the control-plane service lands.

---

What the issue asks for

Four items, from LIF-286 in the previous tracker:

1. Define query statistics for understanding query patterns — frequency of data elements requested, data sources used, frequency of data not found.
2. Enhance the Query Planner to gather them.
3. Enhance MDR to store them.
4. Implement visualizations.

---

What's already there

Every read in LIF converges on the Query Planner. The GraphQL API delegates to it, Learner Data Export calls it directly, and the Advisor reaches it through the MCP server and GraphQL. That makes it the one place worth instrumenting — and it is already doing the work.

flowchart LR
    ADV[Advisor] --> MCP[MCP Server]
    MCP --> GQL[GraphQL API]
    GQL -->|query| QP
    LDE[Learner Data Export] -->|query| QP
    QP["Query Planner<br/>paths · plan · misses"]
    QP -->|read| QC[Query Cache]
    QP -->|fetch| ORCH[Orchestrator]
    ORCH -->|adapters| SRC[Data sources]
    QP -.->|structured logs| CW["CloudWatch Logs<br/>OPTION A — no new edge"]
    QP ==>|new dependency| MDRDB["MDR query_statistics tables<br/>OPTIONS B / C"]

    classDef hub stroke:#2E8F88,stroke-width:2px
    classDef new stroke:#C07A2A,stroke-width:2px
    class QP hub
    class MDRDB new

Loading

The decision is what happens below the planner: Option A writes structured lines to a log sink that already exists; Options B and C add the first Query Planner → MDR write edge — the integration ADR 0007 explicitly declined.

All three statistics are computed on the hot path today and thrown away:

Statistic	Already computed at	Fate today
Elements requested	get_lif_fragment_paths_from_query() — query_planner_service/core.py:81	Passed to the planner, then dropped
Sources used	LIFQueryPlanPart.information_source_id / .adapter_id — core.py:101	Sent to the Orchestrator, then dropped
Not found — before fetch	get_lif_fragment_paths_not_found_in_lif_record() — core.py:84	Logged at INFO (core.py:93), then dropped
Not found — after fetch	part_result.error and empty fragments — core.py:243	Logged at ERROR, then dropped

MDR also has a clean precedent to copy if we go that way: the #1033 DeveloperApiKeys vertical runs migration → SQLModel → DTO → service → mounted router, with an idempotent V1.5 migration that creates in public and back-fills existing tenant_* schemas.

---

Where it would have to live

Four accepted or proposed ADRs constrain this more than the issue text suggests.

ADR	What it says	Consequence for #341
0002 / #1041	Admin, monitoring and key/identity belong in a control-plane service. MDR is the interim host, and the pattern is self-contained bricks with no imports into MDR-domain services.	Query statistics are monitoring. Building them in MDR means building them where they've already been decided not to live.
0004	Components are bare capability; product-tier must never depend on demo or deployment bricks.	The Query Planner is product-tier. Emission must be opt-in, config-driven and fail-open — a bare planner has to run with statistics off.
0005	The MDR UI is kept clean; ops dashboards go to the control-plane UI, which is deferred until its service exists.	Source latency and error dashboards have no home yet. Schema-element usage is defensible in the MDR UI — it's bullet 2 of open issue #512.
0007	Decided against Query Planner ↔ MDR integration.	This is the reverse direction — the planner writing rather than reading — but it needs a new ADR that references and scopes 0007.

---

Four gaps that block a naive implementation

These apply to any option, including the cheap one.

#	Gap	Why it matters	Fix
1	No org identity in the Query Planner	One instance runs per org (lif-query-planner-org1/2/3) but nothing in its env or config says which. Every statistic collapses into one bucket.	New LIF_ORG_KEY env var; compose + CloudFormation params.
2	No caller identity	The planner can't tell GraphQL traffic from LDE from MCP, so "who asks for what" is unanswerable.	Optional X-LIF-Client header from graphql_client and LDE.
3	The sync path counts twice	POST /query calls run_query() once with first_run=True and again after polling (base core.py:109, then :126). Naive emission doubles every orchestrated query.	Guard emission on first_run.
4	Async correlation rides a leaky store	JOB_STORE is in-memory and prune_job_store() is commented out at its only call site (core.py:129). Anything correlated through it is lost on restart.	Accept the loss, or use a two-phase write (see decisions below).

---

Three ways to go

Costs are rough PR counts, not calendar estimates.

	Option A — structured logs only	Option B — instrument and store, no UI	Option C — full vertical with a usage view
Cost	~1 PR, plus gaps 1–3	~4 PRs, phases 1–3	~6 PRs, phases 1–4
What changes	The four existing log lines become structured JSON; analysis happens in CloudWatch Logs Insights.	New MDR tables, a write endpoint and aggregate read endpoints, plus an emitter brick in the planner.	Everything in B, plus a Usage view under Explore in the MDR UI.
You get	Element frequency, source usage and not-found rate, per org, queryable ad hoc.	Durable history, real aggregates over an API, and a foundation #512 can build on.	Closes #341 outright, and the second bullet of #512 with it.
You give up	No dashboards, no retention policy of our own, no cross-org rollups. Analysis needs console access, and log retention bounds history.	Nothing user-facing — someone still has to call the API to see anything.	Builds monitoring inside MDR that ADR 0002 sends to the control plane — likely built twice.
New dependencies	None. No new edge.	First Query Planner → MDR write edge.	The write edge, plus a UI surface ADR 0005 wants kept clean.

---

A scope question worth settling first

#341 sits under #1131, "EPIC: Performance Improvements" — but the statistics it defines are usage-pattern statistics, not performance statistics. Which elements get asked for, which sources answer, and what's missing inform schema and source design; they say nothing about latency.

If the epic's real question is what's slow, the useful subset is per-source latency and cache-hit rate — and Option A already covers it. Worth confirming intent with the epic owners before committing to B or C, because the answer changes which option is even relevant.

Important

Hard constraint, whichever option wins. LIFQuery.filter carries a LIFPersonIdentifier. It must never be logged or persisted. #512 is explicit that this is org-level, not user-level, analysis — so enforce it at the event-builder boundary where it can be tested, not by convention at each call site.

---

If we build the full vertical

The phases are a real sequence — each depends on the one before it.

Phase 1 — Define the statistics

An ADR scoping ADR 0007, plus docs/design/components/query-statistics.md. Fixes the event shape and the privacy rule.

This is the only phase worth doing regardless of which option wins — it's what keeps B and C open.

Closes issue bullet 1. Docs only.

Phase 2 — MDR storage

An idempotent V1.6__query_statistics.sql following the #1033 pattern; a self-contained query_statistics_store component with its models defined in the brick rather than in mdr_sql_model.py, so extraction to the control plane stays a redeploy; a router at /query_statistics; a new mdr__auth__service_api_key__query_planner; and a retention knob with a prune path.

Closes issue bullet 3. Write endpoint takes batches; reads are aggregates.

Phase 3 — Query Planner emission

A pure query_statistics component that builds an event from the query, plan, cache result and orchestration results — no I/O, fully unit-testable per ADR 0004. A separate thin client brick doing batched fire-and-forget POSTs, deliberately not folded into mdr_client, so the eventual control-plane move is a URL change. Off by default, bounded queue, drop on backpressure, exceptions swallowed.

Closes issue bullet 2. Bricks go in lif_query_planner_api and lif_mdr_api only — not the three Dagster projects.

Phase 4 — Visualization

A Usage view under Explore in the MDR UI: element frequency and coverage gaps. Source latency and error views stay API-only until the control-plane UI exists.

Note that mdr-frontend has no charting library today — it's Radix Themes only — and top-N sorted bars need nothing more than CSS, so hold off on adding recharts until a time-series view demands it.

Closes issue bullet 4, and bullet 2 of #512.

Proposed shape of the data

A three-table star, rows rather than JSONB, because every visualization is a GROUP BY over elements or sources:

• QueryStatistics — one row per query: OrgKey, ClientId, Timestamp, CacheHit, Outcome, DurationMs, CorrelationId (the orchestrator run_id).
• QueryStatisticElements — one row per requested fragment path: FragmentPath, ServedFromCache, Fulfilled.
• QueryStatisticSources — one row per plan part: InformationSourceId, AdapterId, PathCount, Error, DurationMs.

The highest-value read is /query_statistics/gaps — most requested, least often found. That single endpoint is arguably the whole point of the feature.

---

Two decisions for the group

1. Tenant isolation for the stats tables. Statistics arrive from a service principal, which routes to mdr__tenant_routing__service_schema. So one shared table with an OrgKey column is the simple answer, and it matches "org level, not user level". But if MDR-as-SaaS means tenant A must never see tenant B's query patterns, this needs the clone_lif_schema treatment plus an X-API-Tenant-Schema header from the planner.

→ Simple version first, or isolated from the start?

2. Lifecycle for in-flight queries. A single terminal event is simpler, but loses every query that times out or dies with a planner restart — and that biases the "data not found" metric exactly where it matters most. The alternative is a two-phase write: emit PENDING at plan time, upsert on completion via run_id. Costs an extra round-trip on the hot path, makes stalled queries visible.

→ I lean two-phase, for precisely that reason.

---

Risks

• Person data leaking into statistics. The filter carries an identifier. Enforce exclusion in the event builder, and test it.
• Write amplification on the hot path. The planner answers every read in the system. Batch, fire-and-forget, bound the queue, never block a query.
• Coupling the product tier to MDR. Options B and C put a control-plane dependency on a component adopters are meant to run standalone. Opt-in and fail-open are not optional here.
• Building it twice. EPIC: Extract a LIF control-plane service (identity + developer keys + admin/monitoring) from MDR #1041 has already decided this storage moves. Every line written into MDR now is a line to migrate later.

---

Line references are to components/lif/query_planner_service/core.py and bases/lif/query_planner_restapi/core.py at commit 97ec8b1.

Related: #512 Schema Usage Analysis · #1041 Control-plane extraction · #1131 Performance epic · ADRs 0002, 0004, 0005, 0007.

[dereck-symmetry]

Blocked on #1269 before Option A can proceed

Picking this up to implement Option A (structured logs), per the 2026-08-29 investigation above. Grounding it in the current code first turned up something that changes the order of work.

Two of the four log lines Option A proposes to convert already violate this issue's own hard constraint — the one stated in the callout above, that LIFPersonIdentifier must never be logged or persisted. Verified against main at 97304e9 by rendering the actual log output, not by reading field names:

• query_planner_service/core.py:220 writes person_id **and the full fragments payload` at INFO on every orchestration callback.
• query_planner_service/core.py:104 writes LIFQueryPlanPart.person_id at INFO.

And the worst site is one the investigation did not name — query_planner_service/util.py:169 logs the matched field values for every requested path, in a loop, on every query:

INFO | Matched path 'person.name' with values: [{'givenName': ['John'], 'familyName': 'Doe'}]

LOG_LEVEL defaults to INFO (components/lif/logging/core.py:4), so this is live in dev and demo.

Structuring these lines as JSON without redacting them first would make the PII machine-parseable and indexable rather than incidental — strictly worse. So the privacy fix goes first, in its own PR, tracked as #1269.

What this means for #341

No change to the recommendation — Option A is still the right call, and the scope stays as this investigation defined it. Two adjustments:

1. URGENT: learner PII (identifiers and field values) is logged at INFO on every query #1269 lands first. Option A then structures lines that are already clean.
2. Gap 4 is stale. It cites prune_job_store() being commented out at its only call site; that was re-enabled in Issue #1262: fix: prune the Query Planner JOB_STORE at its existing call site #1267 (Closes #1262), with tests. Async correlation through JOB_STORE is still lost on restart, so the underlying caveat holds, but the specific defect named there is fixed.

The Option A PR itself will be planner-local: structured emission from the Query Planner's own log lines plus the first_run double-count guard (gap 3). Deliberately not in scope:

• lif/logging — it is plain-text basicConfig today and is packaged by 14 projects. A JSON formatter there is the widest blast radius in the repo. Emitting a JSON payload inside the planner's own messages gets the same CloudWatch Logs Insights queryability with none of that reach.
• Gap 1 (LIF_ORG_KEY) and gap 2 (X-LIF-Client) — both greenfield (neither string exists anywhere in the repo today), and gap 1 reaches cloudformation/*.params. Each is independently shippable and will get its own issue. Until gap 1 lands, statistics collapse into a single bucket across org1/2/3 — that is a real limitation of the first PR and will be stated in it, not hidden.

[dereck-symmetry]

Closing as delivered under Option A (structured logs), the scope chosen in the 2026-08-29 investigation above.

Shipped:

• Issue #341: feat: emit Query Planner query statistics as structured log events #1273: the Query Planner emits one LIF_QUERY_STATISTICS JSON event per query (elements requested, sources used, paths not found, outcome), with no person data and the first_run double-count guarded.
• Issue #1271: feat: record which organization's Query Planner emitted each statistic #1302 (Query Planner has no org identity: LIF_ORG_KEY, so every statistic collapses into one bucket #1271): org_key from LIF_ORG_KEY, so the three orgs no longer collapse into one bucket.
• Issue #1272: feat: record the Query Planner's caller from an optional X-LIF-Client header #1301 (Query Planner cannot identify its caller: add an optional X-LIF-Client header #1272): client from an optional X-LIF-Client header, so GraphQL, Learner Data Export and MCP traffic are told apart.

The event is defined in components/lif/query_planner_service/statistics.py and can be queried in CloudWatch Logs Insights with filter @message like /LIF_QUERY_STATISTICS/.

Not done, deliberately: bullet 3 (MDR storage) and bullet 4 (visualizations). ADR 0002 and #1041 place monitoring storage in the control plane rather than MDR, and ADR 0005 does the same for dashboards. That work continues under #512 (org-level usage frequency).

Deploy note: dev and demo record org_key: "unknown" until each QP stack is redeployed with aws-deploy.sh --only-stack {env}-lif-query-planner-org{1,2,3}.