BI-P060/P061/P103/P104/P105 — dashboard model + versioning, BI SLO, benchmark harness, materialization #36

Merged

admin merged 6 commits from feat/bi-p060-p061-p103-p105-dashboards-slo-materialization into main 2026-09-07 02:53:38 +00:00

Conversation 0 Commits 6 Files Changed 21

+6484

admin commented 2026-09-07 01:43:25 +00:00

Owner

Copy Link

Copy Source

Five BI prompts extending the analytics core merged in 0pt#34 and the query/metric/quality layer in 0pt#35. DashboardDefinition and DashboardVersion already existed as canonical entities with tables in sql/008; this gives them behaviour rather than new names.

Documentation: docs/analytics/BI_DASHBOARDS_SLO_MATERIALIZATION.md.

The finding worth reading first

A pre-aggregate that is one second old can be wrong by 5%. Against a live ClickHouse 26.3.10.60 with 2M rows and a real materialized view, a GDPR erasure (ALTER TABLE … DELETE) on the source left the aggregate untouched — an MV is an INSERT trigger and never sees a mutation. It would have reported 801 571 002 against a true 761 580 554: 39 990 448 overstated, 5.251% — with every freshness check passing and staleness at 1 second. The guarded router refused (state_not_serving); the counterfactual, a router that checks only staleness, serves it, and that was measured rather than assumed. evidence/bi-p105/materialization-drift.json.

BI-P060 — analytics dashboard model

src/analytics/dashboards/dashboard-document.ts. Engine-neutral document (layout / filters / widgets / parameters / refresh / permissions / version) whose widgets reference a metric, a contract query and a visualization spec — never a vendor id.

The DoD is a function: planDashboard() turns a document into runnable AnalyticsQueryRequest objects (BI-P016) plus render specs. The test imports a document that came FROM Superset, plans it, and greps the serialized plan for vendor traces — after first asserting the detector finds those traces in the source document, because a clean grep over a broken detector is how this test passes for the wrong reason.

Counterfactual as a test: a widget whose only content is a Superset slice id is refused — precisely the document that cannot be reconstructed. vendor_ref survives for round-tripping but is excluded from the canonical hash, so re-exporting into a new Superset instance does not fabricate a new dashboard version. Query widgets go through parseAnalyticsQueryRequest, so a dashboard is not a new SQL surface and cannot name a tenant.

BI-P061 — dashboard versioning

sql/010 + src/analytics/dashboards/dashboard-release.ts. DRAFT → PUBLISHED → SUPERSEDED as a ledger rather than a column on the frozen version, because sql/008 already makes DashboardVersion immutable at the database and a lifecycle column there would have meant weakening that.

Rollback mints a new version carrying the old document, so "we went back to v3" is a timestamped fact (rollback_of_version = 3) instead of a hole where v4 used to be. It is verified by canonical hash, and documentOf() refuses to serve a stored document whose hash no longer matches its release.

The claim worth attacking is "only one release can be live". The application check is read-then-write, so it is not the guarantee — measured both ways against Postgres 16.15 (evidence/bi-p061/dashboard-release-race.json):

run	partial unique index	committed	published after
guard_absent	dropped	2	2 — two live dashboards
guard_present	in place	1 (loser 23505)	1

The database also refuses un-publishing, superseded → published, repointing a release at another version, rewriting document_hash/published_at, deleting a release, a published row with no published_at, and a second open draft.

BI-P103 — BI SLO

src/analytics/slo/. Derived from aud0#34's seven BI usage event types (BI-P076) rather than a second measurement path — aud0's own doc records that zero producers emit them, so 0pt becomes the producer.

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 dataset property and report delivery is N0TIFY's receipt — declared unmeasured rather than proxied), and low_confidence (a p99 over 12 samples is the 12th-slowest request wearing a percentile's clothes).

The producer is an allowlist built field by named field. The test feeds it an execution record carrying SQL text, a database error message quoting a personnummer, filter VALUES, a tenant id and a result sample, then greps the emitted events — after proving the grep finds those strings in the input.

BI-P104 — benchmark harness

src/analytics/bench/benchmark-harness.ts, six dataset profiles × ClickHouse 26.3.10.60 and DuckDB 1.5.5, 2 warmups discarded, 9 timed repetitions, evidence/bi-p104/benchmark.json.

Overlapping ranges are indistinguishable whatever the medians say — three of six real comparisons came out that way, one with a 1.6x median gap. 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. Every engine BI-P104 names appears in the evidence, and the five nothing measured carry a reason each — the running Trino belongs to another lane, so benchmarking through it would have measured their configuration.

Measured: DuckDB faster on small_saas (1.6–8.2x) and vehiq_market (2.4–13.3x), slower on large_event (0.2–0.9x, count DISTINCT over 5M rows), indistinguishable on medium_business, wide_table and geospatial.

BI-P105 — materialization / pre-aggregation

src/analytics/materialization/. Eight named refusals, fail-closed. drift_never_verified is the one usually omitted and the one that matters on launch day: a brand-new aggregate is exactly as unproven as a drifted one. A missing bucket counts as drift even when every value present is correct; an empty comparison is insufficient_data, never match. Grain rollup is restricted, not assumed — a week aggregate cannot answer a monthly question.

Measured improvement, honestly: p50 3.4x, but the range spans 0.6x–7.5x on an idle host, so no single figure is quoted.

Findings from writing the tests

1. The one-draft-per-dashboard index makes a two-draft publish race impossible to even construct, so the race is run as two create-and-publish transactions — what a "save and publish" button does.
2. An UPDATE matching zero rows raises nothing, which reads as "refused" to a careless test. The immutability test asserts the row exists first.
3. buildEvidence() gave an engine declared available that produced no run no reason — the dangerous case, since it reads as "we could have and chose not to say". Fixed.
4. DuckDB's progress bar writes to stdout and corrupted the first benchmark transcript. Disabled explicitly; JSON read from a sentinel.
5. A ±10% spread (the 126x–153x budget-overshoot pair) is statistically stable by CV — the problem was never instability, it is that any single figure quoted from it is ~20% wrong. The harness keeps the ends attached to the middle.

What is NOT proven

• No dashboard has been rendered — BI-P060 produces a plan; drawing it is DES1GN#9 / siax-portal#5.
• DashboardReleaseStore has no Postgres adapter yet: the lifecycle is proven in-memory, the schema against live Postgres. That is the one place the two could drift apart.
• Zero real SLO events are emitted in production, so every objective would today report insufficient_data — the honest empty state, deliberately visible.
• No materialization is deployed and nothing schedules a refresh or a periodic drift check yet (ACT0/N0D territory).
• Trino, Superset, MetricFlow and Perspective are unmeasured, each with its reason in the evidence.

Tests

904 pass repo-wide (166 skipped: infra-gated suites without their backend). New: 18 BI-P060, 23 BI-P061 (8 unit + 15 live Postgres), 14 BI-P103, 14 BI-P104 (10 unit + 4 live engines), 23 BI-P105 (19 unit + 4 live ClickHouse).

🤖 Generated with Claude Code

Five BI prompts extending the analytics core merged in `0pt#34` and the query/metric/quality layer in `0pt#35`. `DashboardDefinition` and `DashboardVersion` already existed as canonical entities with tables in `sql/008`; this gives them behaviour rather than new names. Documentation: `docs/analytics/BI_DASHBOARDS_SLO_MATERIALIZATION.md`. ## The finding worth reading first **A pre-aggregate that is one second old can be wrong by 5%.** Against a live ClickHouse 26.3.10.60 with 2M rows and a real materialized view, a GDPR erasure (`ALTER TABLE … DELETE`) on the source left the aggregate untouched — an MV is an INSERT trigger and never sees a mutation. It would have reported **801 571 002 against a true 761 580 554: 39 990 448 overstated, 5.251%** — with every freshness check passing and staleness at **1 second**. The guarded router refused (`state_not_serving`); the counterfactual, a router that checks only staleness, **serves it**, and that was measured rather than assumed. `evidence/bi-p105/materialization-drift.json`. ## BI-P060 — analytics dashboard model `src/analytics/dashboards/dashboard-document.ts`. Engine-neutral document (layout / filters / widgets / parameters / refresh / permissions / version) whose widgets reference a metric, a contract query and a visualization spec — never a vendor id. The DoD is a function: `planDashboard()` turns a document into runnable `AnalyticsQueryRequest` objects (BI-P016) plus render specs. The test imports a document that came FROM Superset, plans it, and greps the serialized plan for vendor traces — after first asserting the detector finds those traces in the source document, because a clean grep over a broken detector is how this test passes for the wrong reason. Counterfactual as a test: a widget whose only content is a Superset slice id is **refused** — precisely the document that cannot be reconstructed. `vendor_ref` survives for round-tripping but is excluded from the canonical hash, so re-exporting into a new Superset instance does not fabricate a new dashboard version. Query widgets go through `parseAnalyticsQueryRequest`, so a dashboard is not a new SQL surface and cannot name a tenant. ## BI-P061 — dashboard versioning `sql/010` + `src/analytics/dashboards/dashboard-release.ts`. `DRAFT → PUBLISHED → SUPERSEDED` as a ledger rather than a column on the frozen version, because `sql/008` already makes `DashboardVersion` immutable at the database and a lifecycle column there would have meant weakening that. Rollback **mints a new version** carrying the old document, so "we went back to v3" is a timestamped fact (`rollback_of_version = 3`) instead of a hole where v4 used to be. It is verified by canonical hash, and `documentOf()` refuses to serve a stored document whose hash no longer matches its release. The claim worth attacking is "only one release can be live". The application check is read-then-write, so it is **not** the guarantee — measured both ways against Postgres 16.15 (`evidence/bi-p061/dashboard-release-race.json`): | run | partial unique index | committed | published after | | --- | --- | --- | --- | | `guard_absent` | dropped | 2 | **2 — two live dashboards** | | `guard_present` | in place | 1 (loser `23505`) | 1 | The database also refuses un-publishing, `superseded → published`, repointing a release at another version, rewriting `document_hash`/`published_at`, deleting a release, a `published` row with no `published_at`, and a second open draft. ## BI-P103 — BI SLO `src/analytics/slo/`. Derived from `aud0#34`'s seven BI usage event types (BI-P076) rather than a second measurement path — aud0's own doc records that zero producers emit them, so 0pt becomes the producer. 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 dataset property and report delivery is N0TIFY's receipt — declared unmeasured rather than proxied), and **`low_confidence`** (a p99 over 12 samples is the 12th-slowest request wearing a percentile's clothes). The producer is an allowlist built field by named field. The test feeds it an execution record carrying SQL text, a database error message quoting a personnummer, filter VALUES, a tenant id and a result sample, then greps the emitted events — after proving the grep finds those strings in the input. ## BI-P104 — benchmark harness `src/analytics/bench/benchmark-harness.ts`, six dataset profiles × ClickHouse 26.3.10.60 and DuckDB 1.5.5, 2 warmups discarded, 9 timed repetitions, `evidence/bi-p104/benchmark.json`. Overlapping ranges are **`indistinguishable`** whatever the medians say — three of six real comparisons came out that way, one with a 1.6x median gap. 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. Every engine BI-P104 names appears in the evidence, and the five nothing measured carry a reason each — the running Trino belongs to another lane, so benchmarking through it would have measured their configuration. Measured: DuckDB faster on small_saas (1.6–8.2x) and vehiq_market (2.4–13.3x), **slower** on large_event (0.2–0.9x, `count DISTINCT` over 5M rows), indistinguishable on medium_business, wide_table and geospatial. ## BI-P105 — materialization / pre-aggregation `src/analytics/materialization/`. Eight named refusals, fail-closed. `drift_never_verified` is the one usually omitted and the one that matters on launch day: a brand-new aggregate is exactly as unproven as a drifted one. A missing bucket counts as drift even when every value present is correct; an empty comparison is `insufficient_data`, never `match`. Grain rollup is restricted, not assumed — a `week` aggregate cannot answer a monthly question. Measured improvement, honestly: p50 3.4x, but the range spans **0.6x–7.5x** on an idle host, so no single figure is quoted. ## Findings from writing the tests 1. The one-draft-per-dashboard index makes a two-draft publish race impossible to even construct, so the race is run as two create-and-publish transactions — what a "save and publish" button does. 2. An `UPDATE` matching zero rows raises nothing, which reads as "refused" to a careless test. The immutability test asserts the row exists first. 3. `buildEvidence()` gave an engine declared *available* that produced no run **no reason** — the dangerous case, since it reads as "we could have and chose not to say". Fixed. 4. DuckDB's progress bar writes to **stdout** and corrupted the first benchmark transcript. Disabled explicitly; JSON read from a sentinel. 5. A ±10% spread (the 126x–153x budget-overshoot pair) is *statistically stable* by CV — the problem was never instability, it is that any single figure quoted from it is ~20% wrong. The harness keeps the ends attached to the middle. ## What is NOT proven - No dashboard has been rendered — BI-P060 produces a plan; drawing it is `DES1GN#9` / `siax-portal#5`. - `DashboardReleaseStore` has no Postgres adapter yet: the lifecycle is proven in-memory, the schema against live Postgres. That is the one place the two could drift apart. - Zero real SLO events are emitted in production, so every objective would today report `insufficient_data` — the honest empty state, deliberately visible. - No materialization is deployed and nothing schedules a refresh or a periodic drift check yet (ACT0/N0D territory). - Trino, Superset, MetricFlow and Perspective are unmeasured, each with its reason in the evidence. ## Tests 904 pass repo-wide (166 skipped: infra-gated suites without their backend). New: 18 BI-P060, 23 BI-P061 (8 unit + 15 live Postgres), 14 BI-P103, 14 BI-P104 (10 unit + 4 live engines), 23 BI-P105 (19 unit + 4 live ClickHouse). 🤖 Generated with [Claude Code](https://claude.com/claude-code)

admin added 1 commit 2026-09-07 01:43:25 +00:00

feat(analytics): BI-P060 — engine-neutral dashboard model that plans without Superset ... 2af126dfc4

BI-P002 named DashboardDefinition/DashboardVersion and sql/008 stored them,
but `layout_json` was a string, and a string holds anything — including a
Superset export only Superset can read back. This gives the document a body
and makes the DoD checkable rather than asserted.

planDashboard() is the DoD: a document in, runnable AnalyticsQueryRequest
objects (BI-P016) plus visualization specs out. The test greps the whole
serialized plan for vendor traces after first proving the detector CAN find
one in the source document — a clean grep over a broken detector is the usual
way this test passes for the wrong reason.

The counterfactual is a test too: a widget whose only content is a Superset
slice id is REFUSED, because that is exactly the document that cannot be
reconstructed. vendor_ref survives for round-tripping but is excluded from
the canonical hash, so re-exporting to a new Superset instance does not
fabricate a new dashboard version.

No SQL surface: query widgets are parsed by parseAnalyticsQueryRequest, so
`sql`, `where` and `tenant_id` are refused structurally rather than by
review. 18 tests.

Co-Authored-By: Claude Opus 5 <noreply@anthropic.com>

CODEOWNERS rules requested review from sax3l 2026-09-07 01:43:25 +00:00

admin added 1 commit 2026-09-07 01:51:07 +00:00

feat(analytics): BI-P061 — dashboard releases, with the publish race measured both ways ... 1a12e8a882

DRAFT -> PUBLISHED -> SUPERSEDED as a ledger (sql/010) rather than a column on
the frozen version: sql/008 already makes DashboardVersion immutable at the
database, and a lifecycle column there would have meant weakening that.

Rollback mints a NEW version carrying the old document and publishes it, so
"we went back to v3" is a timestamped fact (v5 = rollback_of_version 3) rather
than a hole where v4 used to be. It is verified by BI-P060 canonical hash, and
documentOf() refuses to serve a stored document whose hash no longer matches
the release — a dashboard that renders an unapproved definition is worse than
one that will not open.

The claim worth attacking is "only one release can be live". The application
check is read-then-write, so it is NOT the guarantee, and the evidence says so
in both directions against Postgres 16.15:

  guard_absent   partial unique index dropped -> BOTH concurrent publishes
                 commit, dashboard ends with TWO live versions
  guard_present  index in place -> loser fails 23505 on
                 opt_analytics_dashboard_release_one_published, one live

evidence/bi-p061/dashboard-release-race.json, reproducible with
scripts/bi-p061-release-race.mjs.

Two findings while writing the tests, both fixed here rather than papered over:
the one-draft-per-dashboard index makes the two-draft race impossible to even
construct (so the race is run as two create-and-publish transactions, which is
what a "save and publish" button does), and an UPDATE matching zero rows
raises nothing — which reads as "refused" to a careless test, so the
immutability test asserts the row exists first.

23 tests: 8 unit + 15 against live Postgres.

Co-Authored-By: Claude Opus 5 <noreply@anthropic.com>

admin added 1 commit 2026-09-07 01:56:35 +00:00

feat(analytics): BI-P105 — a pre-aggregate serves only while it is provably undrifted ... 96ff601248

A slow query is a complaint; a drifted pre-aggregate answers instantly, with
confidence, and is wrong. So the router is fail-closed on eight named reasons,
and the one people leave out — drift_never_verified — is the one that matters
on launch day: a brand-new aggregate is exactly as unproven as a drifted one.

Proved against ClickHouse 26.3.10.60, 2M rows, a real materialized view
(evidence/bi-p105/materialization-drift.json):

  after backfill      match, 48 buckets, 801 571 002 both sides
  GDPR erasure        ALTER TABLE ... DELETE on the source. An MV is an INSERT
                      trigger and never sees a mutation, so the aggregate kept
                      the deleted revenue: it would have reported 801 571 002
                      against a true 761 580 554 — 39 990 448 overstated,
                      5.251%, with NOTHING stale. Staleness at the moment of
                      the decision: 1 second.
  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

Drift detection treats a MISSING bucket as drift even when every value present
is correct (late-arriving rows are the sneakiest failure), and an empty
comparison as insufficient_data rather than match — the same false green
BI-P025 found in a quality check over an empty table.

The measured speed-up is reported as a distribution, not a figure: p50 3.4x,
but the run-to-run range spans 0.6x-7.5x on one idle host, so quoting "3.4x"
as a property of the system would be misleading (BI-P104 doctrine).

19 unit tests + 4 against live ClickHouse.

Co-Authored-By: Claude Opus 5 <noreply@anthropic.com>

admin added 1 commit 2026-09-07 02:01:19 +00:00

feat(analytics): BI-P103 — BI SLOs measured from AUD0's usage events, or declared unmeasured ... ed6e62809d

aud0#34 merged seven BI usage event types and recorded that zero producers
emit them. This makes 0pt the producer and derives the SLOs from those events
rather than opening a second, private measurement path that would disagree
with aud0 during an incident.

Three honest verdicts instead of one number:

  insufficient_data     an availability objective over an empty window is
                        SILENCE, not 100%. Every objective in an empty window
                        comes back insufficient_data and fully_measured is
                        false. A started query with no terminal event counts
                        as unresolved — not as 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 is in aud0's seven 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 and not judged.

The producer is an allowlist built field by named field, never a spread. The
test feeds it an execution record carrying SQL text, a database error message
quoting a personnummer, filter VALUES, a tenant id and a result sample, then
greps the emitted events — after first proving the grep finds those strings in
the input, so a clean result cannot be a broken detector.

14 tests.

Co-Authored-By: Claude Opus 5 <noreply@anthropic.com>

admin added 1 commit 2026-09-07 02:06:44 +00:00

feat(analytics): BI-P104 — a benchmark harness that refuses to quote one number ... f33cc8d32f

Six dataset profiles (small SaaS, medium business, VehIQ market, large event,
wide table, geospatial), one representative question each, run on ClickHouse
26.3.10.60 and DuckDB 1.5.5 with 2 warmups discarded and 9 timed repetitions.
evidence/bi-p104/benchmark.json.

The harness's job is to stop the benchmark from lying:

  - overlapping ranges are INDISTINGUISHABLE whatever the medians say. Three
    of the six real comparisons came out that way, one of them with a 1.6x
    median gap — which is exactly the headline a noisy host manufactures.
  - 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 at all. A three-run benchmark is an
    anecdote.
  - every engine BI-P104 names appears in the evidence, and the five nothing
    measured carry a reason each. The running Trino belongs to another lane,
    so benchmarking through it would have measured their configuration.

Measured shape, warm cache, one idle host, these query shapes only: DuckDB
faster on small_saas (1.6-8.2x) and vehiq_market (2.4-13.3x), SLOWER on
large_event (0.2-0.9x, count DISTINCT over 5M rows), indistinguishable on
medium_business, wide_table and geospatial.

One finding from the harness's own tests: an engine declared available that
produced no run came out of buildEvidence() with no reason — the dangerous
case, since it reads as "we could have and chose not to say". It now gets a
generated reason. A second from the runs: DuckDB's progress bar writes to
STDOUT and corrupted the first transcript; it is disabled explicitly and the
JSON is read from a sentinel.

10 unit tests + 4 against real engines.

Co-Authored-By: Claude Opus 5 <noreply@anthropic.com>

admin added 1 commit 2026-09-07 02:08:25 +00:00

docs(analytics): BI-P060/P061/P103/P104/P105 — what is proven, and what is not ... 02b993faf9

Includes BI-P105's freshness semantics as its DoD asks: watermark, staleness,
the eight refusal reasons, grain rollup limits, and the sentence the evidence
exists to support — the drifted aggregate was ONE SECOND old and overstated
revenue by 5.251%.

Co-Authored-By: Claude Opus 5 <noreply@anthropic.com>

admin merged commit into main 2026-09-07 02:53:38 +00:00

Sign in to join this conversation.

Reviewers

No Reviewers

sax3l

Labels

No items

No labels

Milestone

No items

No Milestone

Projects

Clear projects

No projects

Assignees

Clear assignees

siax-bot siax-ci siax-healer (siax-healer (CI self-healing agent)) admin (Simon Axelsson)

No Assignees

1 Participants

Notifications

Subscribe

Due Date

No due date set.

Dependencies

No dependencies set.

Reference: admin/0pt#36