# BI-P060, P061, P103, P104, P105 — dashboards, SLOs, benchmarks, pre-aggregation Extends the analytics core (`0pt#34`, BI-P002..P012) and the query/metric/quality layer (`0pt#35`). Nothing there is rebuilt: `DashboardDefinition` and `DashboardVersion` were already canonical entities with tables in `sql/008`, and these prompts give them behaviour. Organised, like its predecessor, around what is **proven** and what is **not**. --- ## Where things live | prompt | module | live proof | | ---------------------------- | ---------------------------------------------------------- | ----------------------------------------------------------------------------------------------- | | BI-P060 dashboard model | `src/analytics/dashboards/dashboard-document.ts` | unit (pure model + planner) | | BI-P061 dashboard versioning | `src/analytics/dashboards/dashboard-release.ts`, `sql/010` | `tests/analytics/dashboard-release-postgres.realinfra.test.ts`, Postgres 16.15 | | BI-P103 BI SLO | `src/analytics/slo/bi-slo.ts`, `slo/usage-events.ts` | unit; measurement path is `aud0#34`'s seven event types | | BI-P104 benchmark harness | `src/analytics/bench/benchmark-harness.ts` | `tests/integration/bi-p104-benchmark.integration.test.ts`, ClickHouse 26.3.10.60 + DuckDB 1.5.5 | | BI-P105 materialization | `src/analytics/materialization/` | `tests/integration/materialization-drift-clickhouse.integration.test.ts` | Evidence: `evidence/bi-p061/dashboard-release-race.json`, `evidence/bi-p104/benchmark.json`, `evidence/bi-p105/materialization-drift.json`. --- ## BI-P060 — the dashboard is a document, not a Superset export `layout_json` was a string, and a string holds anything. The canonical document carries `layout / filters / widgets / parameters / refresh / permissions / version`, and a widget references **a metric, a contract query and a visualization spec** — never a vendor's internal id. The DoD is a function. `planDashboard(document, { parameters })` returns, per widget, a runnable `AnalyticsQueryRequest` (BI-P016) plus a render spec. If it returns a plan, the dashboard can be drawn by anything that executes the contract and draws the twelve canonical visualization types. Rules that are enforced rather than documented: - a widget whose only content is a vendor id is **refused** — that is exactly the document that cannot be reconstructed; - `vendor_ref` may carry a Superset/Metabase id for round-tripping, but it is excluded from the canonical hash, so re-exporting into a new Superset instance does not fabricate a new dashboard version; - vendor-native keys (`viz_type`, `form_data`, `position_json`, `slice_id`, …) are refused with a message that names the rule; - query widgets go through `parseAnalyticsQueryRequest`, so a dashboard cannot become an SQL surface and cannot name a tenant; - a locked filter cannot be switched off by a viewer — locked filters are how a saved dashboard pins its scope, and removing one silently widens the result. --- ## BI-P061 — releases are a ledger `DRAFT → PUBLISHED → SUPERSEDED`, stored in `opt_analytics_dashboard_release` (`sql/010`) rather than as a column on the version row: `sql/008` already makes `DashboardVersion` immutable at the database, and a lifecycle column there would have meant weakening that. The **document** is frozen; the **release** moves. The database enforces the state machine, not the application: | attempted | result | | ------------------------------------------- | -------------------------------------------------- | | `published → draft` (un-publishing) | refused — it is how a rollback gets faked | | `superseded → published` | refused | | repointing a release at another version | refused (every content column is frozen on UPDATE) | | rewriting `document_hash` or `published_at` | refused | | `DELETE` on a release | refused — the ledger has no eraser | | a `published` row with no `published_at` | refused by CHECK | | a second open draft for one dashboard | refused by a partial unique index | **Rollback mints a new version.** It does not resurrect the old release row. "We went back to v3" becomes a timestamped fact (v5 carries `rollback_of_version = 3`) instead of a hole in the history where v4 used to be. The restore is verified by BI-P060 canonical hash, and `documentOf()` refuses to serve a stored document whose hash no longer matches its release. ### The concurrency claim, measured both ways `DashboardReleaseService.publish()` checks for a live release before writing. That check is a read-then-write race and is **not** the guarantee. `evidence/bi-p061/dashboard-release-race.json`, Postgres 16.15: | run | partial unique index | transactions committed | published releases after | | --------------- | -------------------- | ---------------------- | --------------------------- | | `guard_absent` | dropped | 2 | **2 — two live dashboards** | | `guard_present` | in place | 1 (loser `23505`) | 1 | Reproduce with `node scripts/bi-p061-release-race.mjs`. --- ## BI-P103 — SLOs measured from AUD0, or declared unmeasured The measurement path is `aud0#34`'s seven BI usage event types (BI-P076). aud0's own doc records that zero producers emit them; `src/analytics/slo/usage-events.ts` makes 0pt the producer. There is deliberately no second telemetry path — two measurement systems disagree, and the disagreement surfaces during an incident. | workload | objectives | | --------------------- | ---------------------------------------------------------------- | | interactive dashboard | availability, p95 first query, p95 cached, error rate, freshness | | exploration | p95, p99, max result, timeout | | report | completion SLA, delivery SLA | Three verdicts that are not "met": - **`insufficient_data`** — an availability objective over an empty window is silence, not 100%. A started query with no terminal event is _unresolved_, not a success. - **`not_measurable_here`** — dashboard **freshness** is a property of the dataset (BI-P026 / BI-P105 watermark) and report **delivery** is N0TIFY's receipt. Neither exists in the seven event types, so both are declared unmeasured rather than proxied by completion. - **`low_confidence`** — a p99 over 12 samples is the 12th-slowest request wearing a percentile's clothes. Reported with its sample size, not judged. The producer is an allowlist built field by named field. `compiled_sql`, `error_message`, `tenant_id`, filter **values** and result samples exist on the 0pt-side telemetry type and are never mapped; a `query_hash` that is not a 64-hex digest is dropped rather than reshaped. ### Honest status `BI_SLO_CATALOGUE`'s targets are **starting thresholds, not derived baselines**. No production BI traffic exists yet, so the first real baselines will come from the first weeks of emitted events. The evaluator is built so that absence shows up as `insufficient_data` until then, rather than as a green dashboard. --- ## BI-P104 — the harness refuses to quote one number Six dataset profiles × two engines, 2 warmups discarded, 9 timed repetitions, statistics from the shipped module. `evidence/bi-p104/benchmark.json`. - **Overlapping ranges are `indistinguishable`**, whatever the medians say. Three of the six real comparisons came out that way, one with a 1.6x median gap — the headline a noisy host manufactures for you. - **Every sentence carries the range, the sample size and the stability**, so it cannot be trimmed to "4.4x faster" without visibly deleting the caveats. - **Under 5 samples nothing is comparable.** A three-run benchmark is an anecdote. - **Every engine BI-P104 names appears**, and the five nothing measured carry a reason each. Measured (warm cache, one idle host, these query shapes only): | dataset | verdict, DuckDB 1.5.5 vs ClickHouse 26.3.10.60 | | --------------- | ------------------------------------------------- | | small_saas | faster, 1.6x–8.2x (median 4.4x) | | medium_business | indistinguishable (medians 1.6x apart) | | vehiq_market | faster, 2.4x–13.3x (median 5.8x) | | large_event | **slower**, 0.2x–0.9x (`count DISTINCT`, 5M rows) | | wide_table | indistinguishable | | geospatial | indistinguishable | Not exercised, with reasons in the evidence: **trino** (the running instance belongs to another lane; benchmarking through it would measure their configuration), **semantic_query** (MetricFlow has never executed in 0pt), **superset**, **native_charts** (DES1GN owns rendering), **perspective**. --- ## BI-P105 — freshness semantics, and why freshness is not correctness ### The vocabulary | term | definition | | ------------------------------ | ------------------------------------------------------------------------------- | | `data_watermark` | the newest **source** timestamp the aggregate represents. Absent = **UNKNOWN**. | | staleness | `now − data_watermark`, in seconds. `undefined` when the watermark is unknown. | | `max_staleness_seconds` | the freshness contract. Older than this and the aggregate stops serving. | | `drift_check_interval_seconds` | how often the aggregate must be re-proved against its source to keep serving. | | `last_drift_verdict` | `match` / `drifted` / `insufficient_data` from the last comparison. | **UNKNOWN is never treated as current.** This is the same doctrine BI-P026 applies to result freshness: a missing watermark is a refusal, not a zero. ### The routing rule A materialization serves **only** while it is provably current _and_ provably undrifted. Eight named refusals, each a real way an aggregate becomes a confident liar: `coverage_miss` (metric/dimension/grain not carried — including a filter on a dimension the aggregate already summed across) · `watermark_unknown` · `staleness_exceeded` · `window_beyond_watermark` (the question reaches past the newest data; answering would silently shorten the period) · `drift_never_verified` · `drift_verification_stale` · `drift_detected` · `state_not_serving`. `drift_never_verified` is the one people leave out and the one that matters on launch day: a brand-new aggregate is exactly as unproven as a drifted one. Grain rollup is restricted, not assumed: a `day` aggregate answers `day` and coarser, never `hour`; a `week` aggregate answers only `week`, because weekly buckets do not compose into calendar months. ### Drift detection - **A missing bucket is drift** even when every value present is correct — late arrivals and a materialized view created after the backfill both look like this, and a value-only comparison passes while the total is wrong. - **An extra bucket is drift** — the source rows are gone and the aggregate is not. - **An empty comparison is `insufficient_data`, never `match`** (the BI-P025 false green, restated for pre-aggregates), and it takes the aggregate out of service rather than annotating it. - **Both tolerances must hold.** 0.1% of a 40M bucket is 40 000 kr of invisible error; an absolute-only bound flags every rounding difference on a large bucket. ### The proof, on a live ClickHouse `evidence/bi-p105/materialization-drift.json`, ClickHouse 26.3.10.60, 2M rows, a real materialized view: | step | result | | ------------------------ | ----------------------------------------------------------------------------------- | | after backfill | `match`, 48 buckets, 801 571 002 on both sides | | GDPR erasure | `ALTER TABLE sales DELETE …`. An MV is an INSERT trigger and never sees a mutation. | | what would be served | 801 571 002 against a true 761 580 554 — **39 990 448 overstated, 5.251%** | | staleness at that moment | **1 second.** Every freshness check passed. | | guarded router | `base_table` (`state_not_serving`) | | counterfactual | a staleness-only router **serves it** — measured, not assumed | | after rebuild | `match`, and routing returns to the materialization | That last row of the counterfactual is the whole point of the prompt: staleness is not correctness. The aggregate was one second old and wrong by 5%. ### Measured improvement, honestly Same drill, base table vs pre-aggregate, 9 repetitions interleaved: p50 3.4x, but the run-to-run range spans **0.6x–7.5x** on an otherwise idle host — one sample had the pre-aggregate slower than the base table's best run. Quoting "3.4x" as a property of the system would be misleading, which is why BI-P104's harness exists. --- ## What is NOT proven here - **No dashboard has been rendered.** BI-P060 produces a plan; drawing it is DES1GN (`DES1GN#9`) and `siax-portal#5`. Nothing in this PR proves a pixel. - **The release service has no Postgres adapter yet.** The lifecycle is proven in-memory and the _schema_ is proven against live Postgres; wiring `DashboardReleaseStore` to `postgres.js` is the next step and is the only place where the two could drift apart. - **Zero real SLO events have been emitted in production.** The producer and the evaluator are tested; no BI surface calls the producer yet, so every objective would today report `insufficient_data`. That is the honest empty state, and it is deliberately visible rather than hidden behind a default. - **No materialization is deployed.** The router and detector are proven against a live ClickHouse in a throwaway database. Nothing schedules a refresh or a periodic drift check yet — that belongs with ACT0/N0D scheduling. - **Trino, Superset, MetricFlow and Perspective are unmeasured**, for the reasons recorded in the benchmark evidence.