-- Oak benchmark dashboard schema v2 (ADDITIVE migration over schema.sql).

--

-- Apply after schema.sql. Only ALTER TABLE ... ADD COLUMN and new tables /

-- indexes: nothing existing is renamed, retyped, or dropped, so replaying R2

-- raw objects against either schema version stays valid.

PRAGMA foreign_keys = ON;

-- Runner identity (ADR-0007) and lane context on measurements. Null means

-- the producing lane did not record the signal (ADR-0002), never 0/''.

ALTER TABLE measurements ADD COLUMN runner_id TEXT;

ALTER TABLE measurements ADD COLUMN runner_class TEXT;

ALTER TABLE measurements ADD COLUMN load_tier TEXT;

ALTER TABLE measurements ADD COLUMN netshape_profile TEXT;

-- p95 sits between the existing p90/p99 columns; nearest-rank like the rest.

ALTER TABLE aggregates ADD COLUMN p95_ms REAL;

-- Subject identity (review fix): identity is the deterministic subject_id,

-- a stable hash of (run_key, effective subject, subject_kind, binary_sha256)

-- computed at ingest. Two different binary_sha256 values for the same subject

-- name in one run are two distinct subject records; `name` stays the plain

-- subject string and is no longer an identity. The ingest upsert keys

-- ON CONFLICT(subject_id), so this explicit unique index names the conflict

-- target (it mirrors the PRIMARY KEY; redundant on disk, additive and

-- self-documenting).

--

-- Supersession note: schema.sql originally declared an inline

-- UNIQUE (run_key, name) on subjects. SQLite cannot drop an inline table

-- constraint via ALTER TABLE, so no additive migration statement can remove

-- it from already-created databases. Resolution, additive-only:

--   * schema.sql's CREATE TABLE (which only runs on fresh databases, via

--     IF NOT EXISTS) no longer carries the inline UNIQUE — fresh/rebuilt

--     databases accept multiple shas per name;

--   * databases created before this revision still carry the old inline

--     constraint and will reject a second sha for one name; they are

--     rebuildable by replaying R2 raw objects (the schema.sql header

--     documents R2 as the source of truth), which sheds the constraint;

--   * measurements, aggregates, and comparisons all reference subjects by

--     subject_id (foreign keys + dashboard_comparisons view joins), so

--     nothing else changes.

CREATE UNIQUE INDEX IF NOT EXISTS idx_subjects_subject_id

  ON subjects (subject_id);

-- Non-unique lookup index replacing the implicit index the old inline

-- UNIQUE (run_key, name) provided on fresh databases.

CREATE INDEX IF NOT EXISTS idx_subjects_run_name

  ON subjects (run_key, name);

-- Committed Git Baseline Books (ADR-0006): compact JSON summaries served by

-- GET /api/baseline-book/:id. book_json is oakbench.baseline_book.book_json

-- output; manifest_json is the provenance manifest.

CREATE TABLE IF NOT EXISTS baseline_books (

  book_id TEXT PRIMARY KEY,

  runner_class TEXT,

  manifest_json TEXT,

  book_json TEXT NOT NULL,

  created_utc TEXT NOT NULL

);

CREATE INDEX IF NOT EXISTS idx_baseline_books_runner_class

  ON baseline_books (runner_class, created_utc DESC);

-- Repro-bundle verification results served by GET /api/verifications.

CREATE TABLE IF NOT EXISTS verifications (

  id INTEGER PRIMARY KEY AUTOINCREMENT,

  bundle_sha256 TEXT NOT NULL,

  verdict TEXT NOT NULL,

  detail_json TEXT,

  created_utc TEXT NOT NULL

);

CREATE INDEX IF NOT EXISTS idx_verifications_bundle

  ON verifications (bundle_sha256, created_utc DESC);

-- Stored target scorecards (oakbench.scorecard.evaluate_targets output);

-- GET /api/scorecard returns the most recent row.

CREATE TABLE IF NOT EXISTS scorecards (

  id INTEGER PRIMARY KEY AUTOINCREMENT,

  bench_id TEXT,

  profile TEXT,

  scorecard_json TEXT NOT NULL,

  created_utc TEXT NOT NULL

);

CREATE INDEX IF NOT EXISTS idx_scorecards_created

  ON scorecards (created_utc DESC);

-- HMAC-verified GitHub webhook deliveries (POST /webhook/github).

CREATE TABLE IF NOT EXISTS webhook_events (

  id INTEGER PRIMARY KEY AUTOINCREMENT,

  event TEXT,

  delivery_id TEXT,

  payload_json TEXT,

  signature_valid INTEGER NOT NULL DEFAULT 0,

  created_utc TEXT NOT NULL

);

CREATE INDEX IF NOT EXISTS idx_webhook_events_created

  ON webhook_events (created_utc DESC);

-- Which dimensions breached for a regression verdict (review fix): JSON

-- array string like '["p90"]'; null/'[]' when no breach. Without this the

-- computed regression_dimensions field died at ingest and readers could not

-- tell a p50 breach from a p90 breach.

ALTER TABLE comparisons ADD COLUMN regression_dimensions TEXT;

-- Refresh the readback view to expose the new column (views carry no data;

-- drop+recreate is a definition refresh, not a destructive migration).

DROP VIEW IF EXISTS dashboard_comparisons;

CREATE VIEW dashboard_comparisons AS

SELECT

  c.comparison_id,

  c.run_key,

  r.bench_id,

  r.timestamp_utc,

  r.profile,

  r.host_id,

  target.name AS target_subject,

  baseline.name AS baseline_subject,

  c.baseline_kind,

  c.scenario,

  c.operation,

  c.p50_delta_ms,

  c.p50_slower_pct,

  c.p90_delta_ms,

  c.p90_slower_pct,

  c.p99_delta_ms,

  c.p99_slower_pct,

  c.avg_token_delta,

  c.token_higher_pct,

  c.avg_output_byte_delta,

  c.output_byte_higher_pct,

  c.avg_tool_call_delta,

  c.tool_call_higher_pct,

  c.failure_rate_delta_pp,

  c.state,

  c.is_regression,

  c.regression_dimensions,

  c.severity_score

FROM comparisons c

JOIN benchmark_runs r ON r.run_key = c.run_key

JOIN subjects target ON target.subject_id = c.target_subject_id

JOIN subjects baseline ON baseline.subject_id = c.baseline_subject_id;