Design: the schema viewer, the Report Builder, and the Analytics configurations page
- Status: Design only. Nothing built. Written 2026-08-28.
- Issue:
#987: a configurations page, a BigQuery-sourced schema viewer, and the BigQuery page becomes the Report Builder - Gating audit:
bigquery-description-audit.md. It came back BUILD IT. - Test plan: not yet written. It is due via the
test-planskill before any of this is presented for review, and it belongs with the first deliverable that has a runtime surface.
How does the schema reader enumerate?
From the datasets API first, then each dataset's OWN INFORMATION_SCHEMA. Never a region sweep.
Nine round trips instead of one per region. At this size that is a fine trade, and the reason has to travel with the code because the cheaper-looking option fails silently.
The evidence, because a conclusion without it gets optimized away.
While running the audit, the obvious implementation dropped a dataset and said nothing:
| Query | Result |
|---|---|
Datasets API (list_dataset_ids) | Returns billing_exports_gcp, location US |
`region-us`.INFORMATION_SCHEMA.TABLES | Does not return its table |
`lantern-app-dev.billing_exports_gcp.INFORMATION_SCHEMA.TABLES | Returns cud_subscriptions_export, a BASE TABLE, immediately |
That dataset holds 20 columns. A region sweep reports no error, renders no empty state, and simply does not show it. During the audit this nearly became the written claim "that dataset is empty", which would have been wrong in a document other people would then have trusted.
A second reason, independent of the bug: INFORMATION_SCHEMA is region-scoped, so a region sweep hard-codes an assumption about which regions exist. This project already spans US and us-central1, and nothing stops a third appearing. Per-dataset enumeration has no such assumption.
This paragraph must be reproduced as a comment at the reader's call site, naming billing_exports_gcp and the 20 columns. The next person will see nine round trips, conclude one sweep is obviously better, and be wrong for a reason they cannot infer from the code.
A view must resolve to its base table.
BigQuery views do not inherit column descriptions. analytics has two views, recent_user_events and recent_system_events, both showing zero described columns, and both read from events, whose 12 columns are fully described.
So the reader should follow a view to its base table for column definitions rather than reporting the view as undocumented. That closes both gaps without anyone writing a description, and hand-writing 20 duplicate descriptions on the views would create exactly the drift problem the next section is about.
The reader mostly EXISTS already, and it drops the one field this needs
GET /analytics/admin/bq-schema is already live, and it already enumerates correctly.
services/api/analytics/src/services/bqSchema.service.js powers the Query Console's schema browser. It calls bq.getDatasets(), then ds.getTables(), then tbl.getMetadata() per table. That is the datasets-API-first approach recommended above, already in production, with no region sweep anywhere.
So the recommendation is not new work. It is a description of what the service already does, and the reason it must not be "optimized" into a region sweep later.
It throws away every description.
The column mapper keeps three fields and drops the fourth:
columns: fields.map((f) => ({
name: f.name,
type: f.type,
mode: f.mode || 'NULLABLE',
}))f.description is present in the BigQuery field metadata and is not carried through. The same is true one level up (metadata.description for the table, taken only for metadata.type) and one level up again (the dataset's own description, not read at all).
This is the single cheapest piece of work in the project. The schema viewer's entire premise, definitions living in BigQuery and the portal reading them, is three fields on an endpoint that already works.
Why the dataset description costs a second call, and why the obvious alternative is worse.
getDatasets() does not carry the description, so the service makes one ds.getMetadata() call per dataset. Someone will eventually ask why it does not just query INFORMATION_SCHEMA instead. Two answers, and the FIRST one is a trap rather than a reason.
INFORMATION_SCHEMA.SCHEMATA_OPTIONS DOES expose it. A first attempt to check this concluded it does not, and the reason it looked absent is the region trap above.
SCHEMATA_OPTIONS is region-scoped like every other INFORMATION_SCHEMA view. Querying region-us returns five described datasets and no analytics, because analytics lives in us-central1. Querying region-us-central1 returns analytics and logs and none of the billing ones.
| Scanned | Datasets with a description |
|---|---|
`region-us` | billing_attrib, billing_marts, billing_norm, billing_raw, ops |
`region-us-central1` | analytics, logs |
So a single-region query looking for analytics comes back empty, and "the field is not exposed" is the natural and wrong conclusion. That is the same failure this section already documents for tables, reproduced independently by a second person on the same day while trying to verify the first finding. It is a strong argument that the trap is easy rather than obvious.
The real reason is cost, and it is decisive.
INFORMATION_SCHEMA is queried by running a BigQuery JOB. getMetadata() is a metadata read. The service's own header says it creates no jobs and scans no data, so this path is free; routing the dataset description through SQL would turn one free metadata call per dataset into nine billable query jobs to fetch nine short strings.
So getMetadata() is right, for a better reason than "it is the only way". It is not the only way; it is the free one.
The same call already carries what the retention panel needs.
tbl.getMetadata() returns timePartitioning (including expirationMs), clustering.fields and requirePartitionFilter in the same response that is already being fetched and discarded. One endpoint change serves the schema viewer AND a live retention panel.
Are the descriptions any GOOD?
Counting them was the audit. This is the read-through, done while drawing the metric set because understanding the columns was required for both.
Yes. Good enough to present as authoritative, which is the bar that matters.
Not one is a placeholder, and not one is wrong. Several are genuinely excellent, and the good ones share a shape: they name the TRAP, not just the field.
| Column | Description | Why it is good |
|---|---|---|
ad_delivery_daily.merchant_id | "Minted merchants/{id} doc id owning the offer (linked via users/{uid}.merchantId, never the auth uid; 'unknown' for pre-instrumentation events)" | Names the wrong id someone would reach for, and the sentinel value |
event_counts_daily.count_trusted | "Rows with service_id IS NOT NULL (server-signed, the trusted lens; #691). count remains raw volume including client-reported rows. NULL on rows aggregated before this column existed." | Defines itself against its sibling AND warns about its own history |
events.client_event_time | "Client-stamped time the event happened (whole seconds, client-claimed, sanity-windowed at ingest; #613). timestamp remains the trusted server receive-time." | Draws the trust boundary explicitly |
events.session_id | "Per-visit session id... Grouping context, not identity." | Pre-empts the privacy misreading |
ad_delivery_daily.unique_users | "Distinct users contributing to this cell. K-anonymity gate basis only; never surfaced to merchants." | States its own access rule |
Seven are thin, and all seven only restate the column name.
claims ("offer_claimed count"), clicks ("sponsored_ad_clicked count"), redemptions ("offer_redeemed count"), offer_id ("Offer document ID"), event_id ("Unique event identifier (UUID)"), entity_id ("Related entity ID (venue, offer, etc.)"), timestamp ("Event timestamp (server-side)").
Three of those are more useful than they look. claims, clicks and redemptions map a column to the exact event name behind it, which is the one thing a reader building a metric needs. They read as restatements and are not.
The genuinely weak one is entity_id, whose "etc." hides an enumeration that entity_type states properly right beside it ("venue, offer, lantern, wave, chat, feature"). Worth a one-line improvement, in BigQuery, not in the portal.
Nothing here blocks the viewer. A bad description would be worse than a missing one, because the viewer presents it as authoritative. There are none.
What keeps the descriptions current?
Nothing, and this is an OPEN QUESTION rather than work for today.
Descriptions are hand-maintained. No schema change requires one, nothing checks for one, and a column can be renamed or re-purposed while its description quietly goes on describing the old thing. The portal is about to present these as the source of truth, which raises the cost of that drift.
Recommendation, if it is ever picked up: a COMPLETENESS check, never an agreement check.
| Check | Mechanically checkable? | Worth building? |
|---|---|---|
Every column in analytics has a non-empty description | Yes | Yes. One query, one threshold |
| A new column arrives without a description | Yes, same query in CI | Yes |
| The description still MATCHES what the column holds | No | No. It would be a green light that means nothing |
The third row is the trap. A guard that compares two copies proves they match, never that either is right, and shipping one produces confident green over an unread field. Completeness is the half that is real.
Not built today. Her standing rule is that optimizations run last unless actively blocking, and this blocks nothing.
What is the bounded metric set?
The builder is only as good as the metrics it offers, and this list is that. Everything below comes from a table whose columns are fully described, so every metric and dimension can carry its BigQuery definition straight into the UI.
Rule 1: a metric and its dimensions come from the SAME table. No joins in v1.
event_counts_daily and ad_delivery_daily are both daily rollups with no shared key beyond day and environment. Joining them would produce a fan-out that reads as real numbers, so the builder picks a source first and offers only that source's metrics and dimensions.
Source A: event_counts_daily, product activity
| Metric | Expression | Definition shown to the user |
|---|---|---|
| Events | SUM(count) | Raw event volume, including client-reported rows |
| Events, server-signed | SUM(count_trusted) | Only rows with a service_id, the trusted lens |
Dimensions: day (always available), event_name, event_tier (auto or registered), environment (production or development).
The two count metrics are a NAMED CHOICE, never a default.
They mean different things and the difference is the whole point of #691. A builder that offers "Events" without saying which lens hands someone a number they cannot interpret. Both appear in the list, each with its definition.
count_trusted is NULL before the column existed, which silently under-reports old windows.
Its own description says so. A trusted-lens query over a window that predates the column returns a smaller number with no indication why. The builder must detect that and say it in the result, not in a tooltip: if any row in the selected range has count_trusted IS NULL, the result carries a line naming the affected days. The alternative is a chart that is quietly wrong, which is the failure this whole project is meant to avoid.
Source B: ad_delivery_daily, the offer funnel
| Metric | Expression | Definition shown to the user |
|---|---|---|
| Offers filled | SUM(fills) | Offer committed to a slot |
| Impressions | SUM(impressions) | Card verified in viewport |
| Clicks | SUM(clicks) | sponsored_ad_clicked |
| Claims | SUM(claims) | offer_claimed |
| Redemptions | SUM(redemptions) | offer_redeemed |
Derived rates, each a ratio of two metrics already in the list:
| Rate | Expression |
|---|---|
| Click-through rate | clicks / impressions |
| Claim rate | claims / impressions |
| Redemption rate | redemptions / claims |
| Impression rate | impressions / fills |
Dimensions: day, merchant_id, offer_id, placement (hero, inline, feed, unknown), target_audience (nearby, lantern, frequent, new, unknown), environment.
Every rate needs a zero-denominator rule, decided once, in the builder.
redemptions / claims on a day with no claims is not zero, it is undefined. Rendering it as 0% invents a fact. The builder returns no value and the UI shows the empty-value glyph, which the admin portal already has a component for.
What is deliberately NOT in the metric set
| Excluded | Why |
|---|---|
unique_users | Its own description: "K-anonymity gate basis only; never surfaced to merchants." It exists to gate, not to report |
user_id, session_id from events | Identity and grouping context. A bounded builder is not the surface for them |
entity_id | Free-form id against an "etc." enumeration; no bounded dimension can be drawn from it yet |
metadata (JSON) | Unbounded by construction. This is what Query Console is for |
Raw events as a source | It is the raw table. Offering it turns a bounded builder back into a query console, which already exists and stays |
The exclusions are also the k-anonymity boundary, and that boundary is NOT decided here.
The merchant-facing report creator is downstream and needs a k-anonymity gate. #874 is the open policy question behind it. Nothing in this design answers it, and the admin builder needs no gate because admins already see everything. Keeping unique_users out of the metric set is not the gate; it is the column's own stated rule.
The retention panel is hard-coded, and it is already WRONG
What moves to the configurations page must not be the panel that exists today.
DataRetentionPanel in BigQueryWorkspace.jsx renders a hand-written RETENTION_POLICIES constant, with a "Status: Active" pill that is a literal string. Checked against what BigQuery actually reports, it is wrong in three ways:
| What the panel says | What BigQuery says | |
|---|---|---|
analytics.events clustered on event_name | Clustered on event_name, user_id | Incomplete |
analytics.event_counts_daily clustering not set | Clustered on event_name | Wrong |
analytics.ad_delivery_daily absent entirely | Partitioned on day, no expiration | A whole table missing |
The two things it gets right are the expirations: events really is 90 days, and event_counts_daily really has none.
It also omits something operationally important that it could have shown: analytics.events has require_partition_filter = true, so a query without a partition filter does not run slowly, it FAILS. An admin reading a retention page is exactly the person who needs to know that.
So retention moves as a LIVE panel, not as a copied constant.
Moving a hand-written table that is already wrong in three ways, onto a new page, would ship a known-wrong surface and reset the drift clock on it. The data is available in the getMetadata() call the schema endpoint already makes.
This is the same principle as the descriptions and it is worth stating once: the portal reads from BigQuery, it does not restate BigQuery. A hand-written copy of a system's state is a copy that starts drifting the moment it is written, and this panel is the proof.
What does each bigquery/* route become?
First, a correction to the scoping: one of the two named redundancies is already fixed.
The project README says analytics/bigquery/console and analytics/query-console are "two routes for the same job". They are not, as of #958. AdminShell.jsx renders bigquery/console as a <Navigate> redirect to query-console, with a comment saying the old path is kept deliberately so saved links, docs and bookmarks do not fall into the catch-all. That is correct and should stay.
The real redundancy is the other one: three routes render one component.
| Route today | Renders | Becomes |
|---|---|---|
bigquery | Redirect to bigquery/export | Redirect to the Report Builder's landing tab |
bigquery/export | BigQueryWorkspace | Stays. Export health, sync windows and backfill coverage is a real, distinct job and nothing else does it |
bigquery/reports | BigQueryWorkspace | Becomes the Report Builder. This is the route the guided builder lands on |
bigquery/retention | BigQueryWorkspace | Moves to the Analytics configurations page. Retention is a setting, and it stops being at home here once this page changes job |
bigquery/console | Redirect to query-console | Stays a redirect. Already correct |
query-console | QueryConsolePage | Unchanged. Raw SQL for admins |
bigquery/retention keeps its path as a redirect when it moves, and that is a HOUSE CONVENTION rather than a judgment call.
Without a redirect the catch-all swallows the URL into the Dashboard with no explanation. The repo has now done this four times: billing, bigquery/console (#958), dashboards/venue-activity when Venue Activity moved into Venues (#985), and this one. Task 1's comment on the third cites the first two by name, which is how a convention stays visible.
A moved route keeps its old path as a <Navigate> redirect with a comment. That is the rule; retention is its fourth application, not a new idea.
Routing is NOT part of this deliverable.
Task 1 is actively rewriting AdminShell.jsx's nav tree and route table. Nothing above has been edited into it. The route change is sequenced by the PM against task 1 and is deliverable 3.
The Analytics configurations page
It lives under Analytics, not under Settings.
Configuration lives with the thing it configures. The operator ruled on 2026-08-28 that venue configuration MOVES OUT of Settings into the Venues section, and a central Settings bucket is what she is dismantling. A new analytics config page filed into Settings on the same day would contradict that.
It carries retention, and NOTHING else, and it says so.
The page opens with one real setting and a stated scope. It is not padded with plausible settings nobody asked for, because a config page filled speculatively becomes a junk drawer and the next person adds to it precisely because it is already there.
The scope line the page carries: this page holds settings that govern how analytics DATA is stored and kept. A new setting belongs here if it changes what is retained, where it lives, or for how long. Anything that changes what is MEASURED belongs with the event taxonomy, and anything that changes what is SHOWN belongs with the surface showing it.
That gives a future addition a test to pass rather than a gap to fill.
The schema viewer: one component, two mounts
Its own route as a browsable reference, AND an embedded panel where it is needed.
- The operator's ask reads as a reference surface: information on each dataset, hover for detail, the tables and params within the tables. That is something you browse.
- It also serves the Query Console at least as much as the Report Builder, since anyone writing raw SQL needs the schema more than someone picking from a bounded list does.
- And it is most useful at the moment you are choosing a metric, which argues for the panel.
Both, from one component. The repo already has this shape: ConfigVenue carries an embedded mode for exactly this reason. Follow that convention rather than inventing a second one.
The property this whole project exists to create
When two surfaces disagree about what a column means, the WAREHOUSE is the tiebreak.
Every definition the portal shows is BigQuery's own column description, shortened where long and never reworded into a different claim. The schema viewer reads them directly. The Report Builder's metric definitions are the same strings.
So if the viewer and the builder ever disagree, neither one wins: they are both wrong against the warehouse, and the fix is one edit in BigQuery that both surfaces pick up.
This is the structural answer to the two-copies problem, and it is fragile in one specific way.
The repo has been bitten by two copies of a behaviour three times in two days: the address join in two services, the placement labels in three components, the allowlist against its writers. Each time the fix was one implementation with two call sites rather than two implementations that agree today.
A definition is the same shape. A future contributor who "improves" a definition in the portal, rather than in BigQuery, breaks the property silently: the portal now says something the warehouse does not, nothing fails, and the next person to read either one has no way to tell which is authoritative.
So the rule is: a definition is edited in BigQuery, never in the portal. If a definition reads badly, that is a bq update on the column, and both surfaces improve at once. entity_id is the live example, whose "etc." hides an enumeration its sibling states properly.
A rule this project should state once, not per feature
A warning about a number belongs IN THE RESULT, never in a tooltip.
It comes up here for count_trusted's NULL window, but it is not specific to that metric.
The person who most needs the warning is the one who screenshots the chart and never hovers. A tooltip is where a wrong number hides: it satisfies a reviewer asking "is it disclosed" while reaching nobody who reads the output. Anything that changes how a number should be interpreted, a truncated total, a capped count, an incomplete window, a suppressed cell, renders as part of the result.
This is the same class of error as a capped count rendered as a total, which this branch has already fixed twice.
What is still open
- Where the configurations page sits in the nav, which is an
AdminShell.jsxdecision and therefore task 1's file. Sequenced by the PM. - The
entity_iddescription, the one weak definition found. A one-line fix in BigQuery, not in the portal. - Whether the
bq-schemaendpoint change ships with the viewer or ahead of it. It is three fields and it unblocks both the viewer and the live retention panel.