-- Oak benchmark dashboard schema for Cloudflare D1.

--

-- Raw JSONL in R2 is the source of truth. These tables are normalized indexes

-- for dashboard queries and can be rebuilt by replaying R2 objects.

PRAGMA foreign_keys = ON;

CREATE TABLE IF NOT EXISTS uploads (

  r2_key TEXT PRIMARY KEY,

  sha256 TEXT NOT NULL,

  bench_id TEXT NOT NULL,

  profile TEXT NOT NULL,

  row_count INTEGER NOT NULL,

  byte_count INTEGER NOT NULL,

  content_type TEXT,

  status TEXT NOT NULL DEFAULT 'accepted',

  error TEXT,

  uploaded_at TEXT NOT NULL

);

CREATE INDEX IF NOT EXISTS idx_uploads_bench_id

  ON uploads (bench_id);

CREATE INDEX IF NOT EXISTS idx_uploads_profile_uploaded

  ON uploads (profile, uploaded_at DESC);

CREATE TABLE IF NOT EXISTS hosts (

  host_id TEXT PRIMARY KEY,

  host TEXT,

  platform TEXT,

  machine TEXT,

  python TEXT,

  runner_id TEXT,

  cpu_model TEXT,

  disk_model TEXT,

  metadata_json TEXT,

  first_seen_at TEXT NOT NULL,

  last_seen_at TEXT NOT NULL

);

-- Run identity is (bench_id, profile): bench_id is a second-resolution

-- timestamp, and two lanes (e.g. core smoke + mount) started in the same

-- second share it. run_key = bench_id || ':' || profile so concurrent lanes

-- never overwrite each other's run metadata. Raw R2 objects are content-keyed

-- (sha256) and unaffected.

CREATE TABLE IF NOT EXISTS benchmark_runs (

  run_key TEXT PRIMARY KEY,

  bench_id TEXT NOT NULL,

  timestamp_utc TEXT NOT NULL,

  profile TEXT NOT NULL,

  source_oak_hash TEXT,

  source_oak_repo TEXT,

  source_oak_status TEXT,

  host_id TEXT,

  raw_r2_key TEXT,

  benchmark_track TEXT,

  command_semantics_version TEXT,

  row_count INTEGER NOT NULL DEFAULT 0,

  subject_versions_json TEXT,

  subject_details_json TEXT,

  uploaded_at TEXT NOT NULL,

  FOREIGN KEY (host_id) REFERENCES hosts (host_id),

  FOREIGN KEY (raw_r2_key) REFERENCES uploads (r2_key)

);

CREATE INDEX IF NOT EXISTS idx_benchmark_runs_profile_time

  ON benchmark_runs (profile, timestamp_utc DESC);

CREATE INDEX IF NOT EXISTS idx_benchmark_runs_bench_id

  ON benchmark_runs (bench_id);

CREATE INDEX IF NOT EXISTS idx_benchmark_runs_source

  ON benchmark_runs (source_oak_hash);

-- Subject identity is subject_id (deterministic hash of run_key + effective

-- subject + kind + binary_sha256). The same subject name MAY appear more than

-- once per run when its binary_sha256 differs — both records are kept so a

-- mixed-binary run never collapses into one subject. Earlier revisions

-- carried an inline UNIQUE (run_key, name) here; it is superseded by

-- subject_id identity (see schema_v2.sql). DBs created with the old inline

-- constraint can be rebuilt by replaying R2 raw objects (header note above).

CREATE TABLE IF NOT EXISTS subjects (

  subject_id TEXT PRIMARY KEY,

  run_key TEXT NOT NULL,

  name TEXT NOT NULL,

  kind TEXT NOT NULL,

  label TEXT,

  version TEXT,

  binary_path TEXT,

  metadata_json TEXT,

  FOREIGN KEY (run_key) REFERENCES benchmark_runs (run_key) ON DELETE CASCADE

);

CREATE INDEX IF NOT EXISTS idx_subjects_name

  ON subjects (name);

CREATE TABLE IF NOT EXISTS measurements (

  measurement_id INTEGER PRIMARY KEY AUTOINCREMENT,

  run_key TEXT NOT NULL,

  subject_id TEXT NOT NULL,

  scenario TEXT NOT NULL,

  operation TEXT NOT NULL,

  run_index INTEGER NOT NULL,

  is_canonical INTEGER NOT NULL DEFAULT 1,

  elapsed_ms REAL,

  estimated_tokens_total INTEGER,

  estimated_tokens_input INTEGER,

  estimated_tokens_output INTEGER,

  token_source TEXT,

  raw_output_bytes INTEGER,

  stdout_bytes INTEGER,

  stderr_bytes INTEGER,

  output_truncated INTEGER NOT NULL DEFAULT 0,

  tool_call_count INTEGER,

  vcs_tool_call_count INTEGER,

  terminal_tool_call_count INTEGER,

  returncode INTEGER NOT NULL,

  command_json TEXT,

  stderr TEXT,

  row_json TEXT NOT NULL,

  created_at TEXT NOT NULL,

  FOREIGN KEY (run_key) REFERENCES benchmark_runs (run_key) ON DELETE CASCADE,

  FOREIGN KEY (subject_id) REFERENCES subjects (subject_id) ON DELETE CASCADE

);

CREATE INDEX IF NOT EXISTS idx_measurements_group

  ON measurements (run_key, subject_id, scenario, operation);

CREATE INDEX IF NOT EXISTS idx_measurements_dashboard

  ON measurements (scenario, operation, is_canonical, returncode);

CREATE TABLE IF NOT EXISTS aggregates (

  run_key TEXT NOT NULL,

  subject_id TEXT NOT NULL,

  profile TEXT NOT NULL,

  scenario TEXT NOT NULL,

  operation TEXT NOT NULL,

  is_canonical INTEGER NOT NULL DEFAULT 1,

  sample_count INTEGER NOT NULL,

  failure_count INTEGER NOT NULL,

  failure_rate REAL,

  p50_ms REAL,

  p90_ms REAL,

  p95_ms REAL,

  p99_ms REAL,

  mean_ms REAL,

  min_ms REAL,

  max_ms REAL,

  avg_tokens REAL,

  avg_output_bytes REAL,

  truncated_count INTEGER NOT NULL DEFAULT 0,

  avg_tool_calls REAL,

  updated_at TEXT NOT NULL,

  PRIMARY KEY (run_key, subject_id, scenario, operation),

  FOREIGN KEY (run_key) REFERENCES benchmark_runs (run_key) ON DELETE CASCADE,

  FOREIGN KEY (subject_id) REFERENCES subjects (subject_id) ON DELETE CASCADE

);

CREATE INDEX IF NOT EXISTS idx_aggregates_dashboard

  ON aggregates (profile, scenario, operation, is_canonical);

CREATE TABLE IF NOT EXISTS comparisons (

  comparison_id INTEGER PRIMARY KEY AUTOINCREMENT,

  run_key TEXT NOT NULL,

  target_subject_id TEXT NOT NULL,

  baseline_subject_id TEXT NOT NULL,

  baseline_kind TEXT NOT NULL,

  profile TEXT NOT NULL,

  scenario TEXT NOT NULL,

  operation TEXT NOT NULL,

  p50_delta_ms REAL,

  p50_slower_pct REAL,

  p90_delta_ms REAL,

  p90_slower_pct REAL,

  p99_delta_ms REAL,

  p99_slower_pct REAL,

  avg_token_delta REAL,

  token_higher_pct REAL,

  avg_output_byte_delta REAL,

  output_byte_higher_pct REAL,

  avg_tool_call_delta REAL,

  tool_call_higher_pct REAL,

  failure_rate_delta_pp REAL,

  threshold_pct REAL NOT NULL,

  min_delta_ms REAL NOT NULL,

  is_regression INTEGER NOT NULL DEFAULT 0,

  state TEXT NOT NULL DEFAULT 'ok',

  severity_score REAL NOT NULL DEFAULT 0,

  updated_at TEXT NOT NULL,

  FOREIGN KEY (run_key) REFERENCES benchmark_runs (run_key) ON DELETE CASCADE,

  FOREIGN KEY (target_subject_id) REFERENCES subjects (subject_id) ON DELETE CASCADE,

  FOREIGN KEY (baseline_subject_id) REFERENCES subjects (subject_id) ON DELETE CASCADE,

  UNIQUE (run_key, target_subject_id, baseline_subject_id, scenario, operation)

);

CREATE INDEX IF NOT EXISTS idx_comparisons_dashboard

  ON comparisons (profile, is_regression DESC, severity_score DESC, scenario, operation);

CREATE VIEW IF NOT EXISTS 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.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;