-- Small-data experiment decision evidence model
-- Schema version: 1.0.0 | Reference date: 2026-08-02 | Engine: SQLite 3.37+
-- Companion: ../19-small-data-product-analytics-and-causal-decisions.md
--
-- Run only on a new/disposable database:
--   sqlite3 -bail :memory: < indie-developer-playbook/research/19-small-data-experiment-decision-model.sql
--
-- Every experiment, unit, assignment, timestamp, outcome, amount, issue, and
-- evidence pointer in this file is fully synthetic. No raw person name, email,
-- account identifier, IP address, customer payload, credential, or free-form
-- interview text belongs here. *_ref_hash values are opaque synthetic pointers;
-- they do not prove consent, identity, provenance, or anonymity.
--
-- Interpretation boundary
-- - This model is a decision ledger for deliberately small experiments. It is
--   not a p-value, power, significance, or automatic causal-claim oracle.
-- - Assignment is the ITT spine. Exposure, contamination, observed outcome,
--   confirmed missingness, safety, integrity, cost, analysis, and decision are
--   separate immutable facts.
-- - Outcome occurrence must follow assignment and fall inside its registered
--   window. OBSERVED or MISSING classification is allowed only after maturity.
-- - Missing is explicit and never numeric zero. A measured zero is an observed
--   value. ITT means are emitted only when every assigned unit is mature and
--   classified with an observed value; otherwise the ITT mean is NULL while
--   x/n, observed n, and missing n remain visible.
-- - Randomization permits at most a bounded within-contract ITT causal claim;
--   it never proves external validity. A/A is descriptive instrumentation work.
--   Observational comparisons are association only. Switchback causal claims
--   are prohibited while carryover is UNKNOWN or FAILED.
-- - EXPAND is a bounded next-batch action, not universal rollout. It requires
--   complete maturity, zero open integrity issues, and safety PASS.
-- - Append-only does not mean correct. Late evidence is appended, and a new
--   analysis/decision snapshot supersedes an old one without rewriting it.
-- - recorded_at / registered_at are trusted server-side evidence clocks, not
--   client claims. This reference snapshot cannot prove physical insert time.
--   A production adaptation needs a monotone ingestion sequence, rejects
--   client backdating, reruns DQ after late facts, and supersedes stale
--   analysis/decisions before treating an action as current.

PRAGMA foreign_keys = ON;
PRAGMA recursive_triggers = ON;
PRAGMA trusted_schema = OFF;
BEGIN IMMEDIATE;

-- -----------------------------------------------------------------------------
-- Schema, controlled vocabularies, and immutable evidence
-- -----------------------------------------------------------------------------

CREATE TABLE schema_registry (
  schema_id INTEGER PRIMARY KEY CHECK (schema_id=1),
  schema_name TEXT NOT NULL UNIQUE
    CHECK (schema_name='small_data_experiment_decision'),
  schema_version TEXT NOT NULL CHECK (schema_version='1.0.0'),
  engine_floor TEXT NOT NULL CHECK (engine_floor='SQLite 3.37+'),
  applied_at TEXT NOT NULL CHECK (datetime(applied_at) IS NOT NULL),
  data_policy_code TEXT NOT NULL CHECK (
    data_policy_code='SYNTHETIC_ONLY_NO_RAW_PII_OPAQUE_REFERENCES'
  )
) STRICT;

CREATE TABLE model_setting (
  singleton_id INTEGER PRIMARY KEY CHECK (singleton_id=1),
  reference_time TEXT NOT NULL CHECK (datetime(reference_time) IS NOT NULL),
  default_snapshot_as_of TEXT NOT NULL
    CHECK (datetime(default_snapshot_as_of) IS NOT NULL),
  base_currency TEXT NOT NULL
    CHECK (length(base_currency)=3 AND base_currency=upper(base_currency)),
  founder_rate_micros_per_minute INTEGER NOT NULL
    CHECK (founder_rate_micros_per_minute>0),
  bounded_output_limit INTEGER NOT NULL
    CHECK (bounded_output_limit BETWEEN 1 AND 100),
  CHECK (datetime(default_snapshot_as_of)<=datetime(reference_time))
) STRICT;

CREATE TABLE claim_level_dim (
  claim_level_code TEXT PRIMARY KEY CHECK (claim_level_code IN (
    'DESCRIPTIVE_ONLY','ASSOCIATION_ONLY','CAUSAL_ITT_WITHIN_CONTRACT'
  )),
  claim_rank INTEGER NOT NULL UNIQUE CHECK (claim_rank BETWEEN 0 AND 2),
  interpretation TEXT NOT NULL
) STRICT;

CREATE TABLE design_type_dim (
  design_type_code TEXT PRIMARY KEY CHECK (design_type_code IN (
    'RANDOMIZED_PARALLEL','RANDOMIZED_AA','RANDOMIZED_SWITCHBACK',
    'OBSERVATIONAL_ASSOCIATION'
  )),
  design_claim_cap_code TEXT NOT NULL,
  requires_random_assignment INTEGER NOT NULL
    CHECK (requires_random_assignment IN (0,1)),
  requires_period_contract INTEGER NOT NULL
    CHECK (requires_period_contract IN (0,1)),
  requires_carryover_assessment INTEGER NOT NULL
    CHECK (requires_carryover_assessment IN (0,1)),
  FOREIGN KEY (design_claim_cap_code)
    REFERENCES claim_level_dim(claim_level_code)
) STRICT;

CREATE TABLE issue_category_dim (
  issue_category_code TEXT PRIMARY KEY CHECK (issue_category_code IN (
    'SRM','COVERAGE','DUPLICATE','TIME','CONTAMINATION','CARRYOVER',
    'INTERFERENCE'
  )),
  default_blocks_causal INTEGER NOT NULL
    CHECK (default_blocks_causal IN (0,1)),
  default_blocks_expand INTEGER NOT NULL
    CHECK (default_blocks_expand IN (0,1))
) STRICT;

CREATE TABLE evidence_record (
  evidence_id TEXT PRIMARY KEY,
  evidence_class TEXT NOT NULL CHECK (evidence_class IN (
    'E0_INFERENCE','E1_MANUAL_REVIEW','E2_SYSTEM_CONFIG',
    'E3_SYSTEM_EVENT','E4_FIRST_PARTY_AUTHORITATIVE',
    'E5_CUSTOMER_OR_FINANCIAL_AUTHORITATIVE'
  )),
  evidence_domain TEXT NOT NULL CHECK (evidence_domain IN (
    'CONTRACT','ELIGIBILITY','ASSIGNMENT','EXPOSURE','OUTCOME','INTEGRITY',
    'SAFETY','COST','ANALYSIS','DECISION'
  )),
  evidence_status TEXT NOT NULL CHECK (evidence_status IN (
    'CONFIRMED','REFUTED','UNKNOWN','STALE'
  )),
  source_ref_hash TEXT NOT NULL CHECK (
    length(source_ref_hash)=64
    AND source_ref_hash=lower(source_ref_hash)
    AND source_ref_hash NOT GLOB '*[^0-9a-f]*'
  ),
  occurred_at TEXT NOT NULL CHECK (datetime(occurred_at) IS NOT NULL),
  recorded_at TEXT NOT NULL CHECK (datetime(recorded_at) IS NOT NULL),
  reviewer_role_code TEXT NOT NULL,
  CHECK (datetime(occurred_at)<=datetime(recorded_at))
) STRICT;

-- -----------------------------------------------------------------------------
-- Versioned decision and measurement contracts
-- -----------------------------------------------------------------------------

CREATE TABLE decision_contract (
  decision_contract_id TEXT PRIMARY KEY,
  contract_stream_id TEXT NOT NULL,
  contract_version INTEGER NOT NULL CHECK (contract_version>0),
  supersedes_decision_contract_id TEXT,
  experiment_code TEXT NOT NULL UNIQUE,
  design_type_code TEXT NOT NULL,
  analysis_claim_cap_code TEXT NOT NULL,
  decision_question_code TEXT NOT NULL,
  assignment_unit_code TEXT NOT NULL,
  interference_unit_code TEXT NOT NULL,
  assignment_method_version TEXT NOT NULL,
  eligibility_rule_version TEXT NOT NULL,
  allocation_rule_version TEXT NOT NULL,
  decision_rule_version TEXT NOT NULL,
  minimum_planned_units INTEGER NOT NULL CHECK (minimum_planned_units>0),
  enrollment_opens_at TEXT NOT NULL
    CHECK (datetime(enrollment_opens_at) IS NOT NULL),
  enrollment_closes_at TEXT NOT NULL
    CHECK (datetime(enrollment_closes_at) IS NOT NULL),
  final_maturity_at TEXT NOT NULL CHECK (datetime(final_maturity_at) IS NOT NULL),
  max_cash_cost_micros INTEGER NOT NULL CHECK (max_cash_cost_micros>=0),
  max_founder_minutes INTEGER NOT NULL CHECK (max_founder_minutes>=0),
  carryover_plan_code TEXT NOT NULL CHECK (carryover_plan_code IN (
    'NA','WASHOUT_AND_ASSESS','ASSESS_WITHOUT_WASHOUT'
  )),
  approved_at TEXT NOT NULL CHECK (datetime(approved_at) IS NOT NULL),
  recorded_at TEXT NOT NULL CHECK (datetime(recorded_at) IS NOT NULL),
  approved_by_role_code TEXT NOT NULL,
  evidence_id TEXT NOT NULL,
  CHECK (datetime(approved_at)<=datetime(recorded_at)),
  CHECK (datetime(approved_at)<=datetime(enrollment_opens_at)),
  CHECK (datetime(enrollment_opens_at)<datetime(enrollment_closes_at)),
  CHECK (datetime(enrollment_closes_at)<=datetime(final_maturity_at)),
  UNIQUE (contract_stream_id,contract_version),
  FOREIGN KEY (supersedes_decision_contract_id)
    REFERENCES decision_contract(decision_contract_id),
  FOREIGN KEY (design_type_code) REFERENCES design_type_dim(design_type_code),
  FOREIGN KEY (analysis_claim_cap_code)
    REFERENCES claim_level_dim(claim_level_code),
  FOREIGN KEY (evidence_id) REFERENCES evidence_record(evidence_id)
) STRICT;

CREATE TABLE arm_contract (
  arm_id TEXT PRIMARY KEY,
  decision_contract_id TEXT NOT NULL,
  arm_code TEXT NOT NULL,
  arm_role TEXT NOT NULL CHECK (arm_role IN (
    'REFERENCE','COMPARATOR'
  )),
  comparison_pair_code TEXT NOT NULL,
  planned_share_ppm INTEGER NOT NULL CHECK (planned_share_ppm BETWEEN 0 AND 1000000),
  intervention_version TEXT NOT NULL,
  arm_registered_at TEXT NOT NULL CHECK (datetime(arm_registered_at) IS NOT NULL),
  evidence_id TEXT NOT NULL,
  UNIQUE (decision_contract_id,arm_code),
  UNIQUE (decision_contract_id,comparison_pair_code,arm_role),
  FOREIGN KEY (decision_contract_id)
    REFERENCES decision_contract(decision_contract_id),
  FOREIGN KEY (evidence_id) REFERENCES evidence_record(evidence_id)
) STRICT;

CREATE TABLE metric_contract_version (
  metric_contract_id TEXT PRIMARY KEY,
  metric_stream_id TEXT NOT NULL,
  metric_version INTEGER NOT NULL CHECK (metric_version>0),
  supersedes_metric_contract_id TEXT,
  decision_contract_id TEXT NOT NULL,
  metric_code TEXT NOT NULL,
  metric_role TEXT NOT NULL CHECK (metric_role IN (
    'PRIMARY','GUARDRAIL','DIAGNOSTIC'
  )),
  value_kind TEXT NOT NULL CHECK (value_kind IN (
    'BINARY','COUNT','CONTINUOUS'
  )),
  unit_code TEXT NOT NULL,
  favorable_direction TEXT NOT NULL CHECK (favorable_direction IN (
    'HIGHER','LOWER','NONE'
  )),
  numerator_definition_code TEXT NOT NULL,
  denominator_definition_code TEXT NOT NULL,
  aggregation_code TEXT NOT NULL CHECK (aggregation_code='RAW_ITT_MEAN'),
  missing_policy_code TEXT NOT NULL
    CHECK (missing_policy_code='EXPLICIT_MISSING_NEVER_ZERO'),
  window_open_delay_days INTEGER NOT NULL CHECK (window_open_delay_days>=0),
  window_close_delay_days INTEGER NOT NULL CHECK (
    window_close_delay_days>=window_open_delay_days
  ),
  maturity_delay_days INTEGER NOT NULL CHECK (
    maturity_delay_days>=window_close_delay_days
  ),
  decision_threshold REAL,
  threshold_operator TEXT NOT NULL CHECK (threshold_operator IN (
    'GE','LE','NONE'
  )),
  safety_veto INTEGER NOT NULL CHECK (safety_veto IN (0,1)),
  registered_at TEXT NOT NULL CHECK (datetime(registered_at) IS NOT NULL),
  evidence_id TEXT NOT NULL,
  CHECK ((threshold_operator='NONE' AND decision_threshold IS NULL)
      OR (threshold_operator IN ('GE','LE') AND decision_threshold IS NOT NULL)),
  CHECK (safety_veto=0 OR metric_role='GUARDRAIL'),
  UNIQUE (metric_stream_id,metric_version),
  FOREIGN KEY (supersedes_metric_contract_id)
    REFERENCES metric_contract_version(metric_contract_id),
  FOREIGN KEY (decision_contract_id)
    REFERENCES decision_contract(decision_contract_id),
  FOREIGN KEY (evidence_id) REFERENCES evidence_record(evidence_id)
) STRICT;

CREATE TABLE switchback_period_contract (
  period_contract_id TEXT PRIMARY KEY,
  decision_contract_id TEXT NOT NULL,
  arm_id TEXT NOT NULL,
  period_index INTEGER NOT NULL CHECK (period_index>0),
  period_code TEXT NOT NULL,
  scheduled_intervention_version TEXT NOT NULL,
  period_starts_at TEXT NOT NULL CHECK (datetime(period_starts_at) IS NOT NULL),
  period_ends_at TEXT NOT NULL CHECK (datetime(period_ends_at) IS NOT NULL),
  washout_minutes_before INTEGER NOT NULL CHECK (washout_minutes_before>=0),
  registered_at TEXT NOT NULL CHECK (datetime(registered_at) IS NOT NULL),
  UNIQUE (decision_contract_id,arm_id,period_index),
  UNIQUE (decision_contract_id,arm_id,period_code),
  CHECK (datetime(period_starts_at)<datetime(period_ends_at)),
  FOREIGN KEY (decision_contract_id)
    REFERENCES decision_contract(decision_contract_id),
  FOREIGN KEY (arm_id) REFERENCES arm_contract(arm_id)
) STRICT;

-- -----------------------------------------------------------------------------
-- Pre-assignment population, immutable assignment, and actual exposure
-- -----------------------------------------------------------------------------

CREATE TABLE experiment_unit (
  unit_id TEXT PRIMARY KEY,
  decision_contract_id TEXT NOT NULL,
  unit_ref_hash TEXT NOT NULL CHECK (
    length(unit_ref_hash)=64
    AND unit_ref_hash=lower(unit_ref_hash)
    AND unit_ref_hash NOT GLOB '*[^0-9a-f]*'
  ),
  unit_type_code TEXT NOT NULL,
  first_seen_at TEXT NOT NULL CHECK (datetime(first_seen_at) IS NOT NULL),
  recorded_at TEXT NOT NULL CHECK (datetime(recorded_at) IS NOT NULL),
  CHECK (datetime(first_seen_at)<=datetime(recorded_at)),
  UNIQUE (decision_contract_id,unit_ref_hash),
  FOREIGN KEY (decision_contract_id)
    REFERENCES decision_contract(decision_contract_id)
) STRICT;

CREATE TABLE eligibility_review (
  eligibility_review_id TEXT PRIMARY KEY,
  decision_contract_id TEXT NOT NULL,
  unit_id TEXT NOT NULL,
  eligibility_rule_version TEXT NOT NULL,
  eligibility_state TEXT NOT NULL CHECK (eligibility_state IN (
    'PASS','FAIL','UNKNOWN'
  )),
  eligibility_reason_code TEXT NOT NULL,
  assessed_at TEXT NOT NULL CHECK (datetime(assessed_at) IS NOT NULL),
  recorded_at TEXT NOT NULL CHECK (datetime(recorded_at) IS NOT NULL),
  evidence_id TEXT NOT NULL,
  CHECK (datetime(assessed_at)<=datetime(recorded_at)),
  UNIQUE (decision_contract_id,unit_id,eligibility_review_id),
  FOREIGN KEY (decision_contract_id)
    REFERENCES decision_contract(decision_contract_id),
  FOREIGN KEY (unit_id) REFERENCES experiment_unit(unit_id),
  FOREIGN KEY (evidence_id) REFERENCES evidence_record(evidence_id)
) STRICT;

CREATE TABLE pre_assignment_exclusion (
  exclusion_id TEXT PRIMARY KEY,
  decision_contract_id TEXT NOT NULL,
  unit_id TEXT NOT NULL,
  exclusion_rule_version TEXT NOT NULL,
  exclusion_reason_code TEXT NOT NULL,
  excluded_at TEXT NOT NULL CHECK (datetime(excluded_at) IS NOT NULL),
  recorded_at TEXT NOT NULL CHECK (datetime(recorded_at) IS NOT NULL),
  evidence_id TEXT NOT NULL,
  CHECK (datetime(excluded_at)<=datetime(recorded_at)),
  UNIQUE (decision_contract_id,unit_id),
  FOREIGN KEY (decision_contract_id)
    REFERENCES decision_contract(decision_contract_id),
  FOREIGN KEY (unit_id) REFERENCES experiment_unit(unit_id),
  FOREIGN KEY (evidence_id) REFERENCES evidence_record(evidence_id)
) STRICT;

CREATE TABLE experiment_assignment (
  assignment_id TEXT PRIMARY KEY,
  decision_contract_id TEXT NOT NULL,
  unit_id TEXT NOT NULL,
  arm_id TEXT NOT NULL,
  eligibility_review_id TEXT NOT NULL,
  assignment_method_code TEXT NOT NULL CHECK (assignment_method_code IN (
    'HASH_RANDOMIZED','BLOCK_RANDOMIZED','OBSERVED_MEMBERSHIP'
  )),
  assignment_key_ref_hash TEXT NOT NULL CHECK (
    length(assignment_key_ref_hash)=64
    AND assignment_key_ref_hash=lower(assignment_key_ref_hash)
    AND assignment_key_ref_hash NOT GLOB '*[^0-9a-f]*'
  ),
  assigned_at TEXT NOT NULL CHECK (datetime(assigned_at) IS NOT NULL),
  recorded_at TEXT NOT NULL CHECK (datetime(recorded_at) IS NOT NULL),
  analysis_population_code TEXT NOT NULL
    CHECK (analysis_population_code='ITT_INCLUDED'),
  evidence_id TEXT NOT NULL,
  CHECK (datetime(assigned_at)<=datetime(recorded_at)),
  UNIQUE (decision_contract_id,unit_id),
  FOREIGN KEY (decision_contract_id)
    REFERENCES decision_contract(decision_contract_id),
  FOREIGN KEY (unit_id) REFERENCES experiment_unit(unit_id),
  FOREIGN KEY (arm_id) REFERENCES arm_contract(arm_id),
  FOREIGN KEY (eligibility_review_id)
    REFERENCES eligibility_review(eligibility_review_id),
  FOREIGN KEY (evidence_id) REFERENCES evidence_record(evidence_id)
) STRICT;

CREATE TABLE exposure_event (
  exposure_event_id TEXT PRIMARY KEY,
  decision_contract_id TEXT NOT NULL,
  assignment_id TEXT NOT NULL,
  period_contract_id TEXT,
  exposure_kind TEXT NOT NULL CHECK (exposure_kind IN (
    'ASSIGNED_VARIANT','CONTROL_BASELINE','OBSERVED_NATURAL_EXPOSURE',
    'CROSS_ARM_EXPOSURE','EXTERNAL_EXPOSURE','CARRYOVER_EXPOSURE',
    'INTERFERENCE_EXPOSURE'
  )),
  intervention_version TEXT NOT NULL,
  exposure_units REAL NOT NULL CHECK (exposure_units>=0),
  occurred_at TEXT NOT NULL CHECK (datetime(occurred_at) IS NOT NULL),
  recorded_at TEXT NOT NULL CHECK (datetime(recorded_at) IS NOT NULL),
  evidence_id TEXT NOT NULL,
  CHECK (datetime(occurred_at)<=datetime(recorded_at)),
  FOREIGN KEY (decision_contract_id)
    REFERENCES decision_contract(decision_contract_id),
  FOREIGN KEY (assignment_id)
    REFERENCES experiment_assignment(assignment_id),
  FOREIGN KEY (period_contract_id)
    REFERENCES switchback_period_contract(period_contract_id),
  FOREIGN KEY (evidence_id) REFERENCES evidence_record(evidence_id)
) STRICT;

CREATE TABLE contamination_event (
  contamination_event_id TEXT PRIMARY KEY,
  contamination_stream_id TEXT NOT NULL,
  event_sequence INTEGER NOT NULL CHECK (event_sequence>0),
  supersedes_contamination_event_id TEXT,
  decision_contract_id TEXT NOT NULL,
  assignment_id TEXT NOT NULL,
  exposure_event_id TEXT,
  contamination_type TEXT NOT NULL CHECK (contamination_type IN (
    'CROSS_ARM','EXTERNAL','PROTOCOL_DEVIATION','UNKNOWN_SOURCE'
  )),
  contamination_state TEXT NOT NULL CHECK (contamination_state IN (
    'OPEN','RESOLVED','UNKNOWN'
  )),
  blocks_itt_interpretation INTEGER NOT NULL
    CHECK (blocks_itt_interpretation IN (0,1)),
  occurred_at TEXT NOT NULL CHECK (datetime(occurred_at) IS NOT NULL),
  recorded_at TEXT NOT NULL CHECK (datetime(recorded_at) IS NOT NULL),
  evidence_id TEXT NOT NULL,
  CHECK (datetime(occurred_at)<=datetime(recorded_at)),
  UNIQUE (contamination_stream_id,event_sequence),
  FOREIGN KEY (supersedes_contamination_event_id)
    REFERENCES contamination_event(contamination_event_id),
  FOREIGN KEY (decision_contract_id)
    REFERENCES decision_contract(decision_contract_id),
  FOREIGN KEY (assignment_id)
    REFERENCES experiment_assignment(assignment_id),
  FOREIGN KEY (exposure_event_id) REFERENCES exposure_event(exposure_event_id),
  FOREIGN KEY (evidence_id) REFERENCES evidence_record(evidence_id)
) STRICT;

-- -----------------------------------------------------------------------------
-- Expected maturity spine and explicit observed/missing outcomes
-- -----------------------------------------------------------------------------

CREATE TABLE expected_outcome_spine (
  expected_outcome_id TEXT PRIMARY KEY,
  decision_contract_id TEXT NOT NULL,
  assignment_id TEXT NOT NULL,
  metric_contract_id TEXT NOT NULL,
  outcome_window_opens_at TEXT NOT NULL
    CHECK (datetime(outcome_window_opens_at) IS NOT NULL),
  outcome_window_closes_at TEXT NOT NULL
    CHECK (datetime(outcome_window_closes_at) IS NOT NULL),
  expected_maturity_at TEXT NOT NULL
    CHECK (datetime(expected_maturity_at) IS NOT NULL),
  registered_at TEXT NOT NULL CHECK (datetime(registered_at) IS NOT NULL),
  evidence_id TEXT NOT NULL,
  CHECK (datetime(outcome_window_opens_at)<=datetime(outcome_window_closes_at)),
  CHECK (datetime(outcome_window_closes_at)<=datetime(expected_maturity_at)),
  UNIQUE (assignment_id,metric_contract_id),
  FOREIGN KEY (decision_contract_id)
    REFERENCES decision_contract(decision_contract_id),
  FOREIGN KEY (assignment_id)
    REFERENCES experiment_assignment(assignment_id),
  FOREIGN KEY (metric_contract_id)
    REFERENCES metric_contract_version(metric_contract_id),
  FOREIGN KEY (evidence_id) REFERENCES evidence_record(evidence_id)
) STRICT;

CREATE TABLE outcome_observation (
  outcome_observation_id TEXT PRIMARY KEY,
  expected_outcome_id TEXT NOT NULL UNIQUE,
  outcome_state TEXT NOT NULL CHECK (outcome_state IN (
    'OBSERVED_VALUE','MISSING_CONFIRMED'
  )),
  numeric_value REAL,
  outcome_occurred_at TEXT CHECK (
    outcome_occurred_at IS NULL OR datetime(outcome_occurred_at) IS NOT NULL
  ),
  missing_reason_code TEXT,
  assessed_at TEXT NOT NULL CHECK (datetime(assessed_at) IS NOT NULL),
  recorded_at TEXT NOT NULL CHECK (datetime(recorded_at) IS NOT NULL),
  evidence_id TEXT NOT NULL,
  CHECK ((outcome_state='OBSERVED_VALUE'
          AND numeric_value IS NOT NULL
          AND outcome_occurred_at IS NOT NULL
          AND missing_reason_code IS NULL)
      OR (outcome_state='MISSING_CONFIRMED'
          AND numeric_value IS NULL
          AND outcome_occurred_at IS NULL
          AND missing_reason_code IS NOT NULL)),
  CHECK (datetime(assessed_at)<=datetime(recorded_at)),
  FOREIGN KEY (expected_outcome_id)
    REFERENCES expected_outcome_spine(expected_outcome_id),
  FOREIGN KEY (evidence_id) REFERENCES evidence_record(evidence_id)
) STRICT;

-- -----------------------------------------------------------------------------
-- Integrity, safety, and complete experiment cost
-- -----------------------------------------------------------------------------

CREATE TABLE integrity_issue_stream (
  integrity_issue_stream_id TEXT PRIMARY KEY,
  decision_contract_id TEXT NOT NULL,
  issue_category_code TEXT NOT NULL,
  issue_scope_code TEXT NOT NULL,
  blocks_causal_claim INTEGER NOT NULL CHECK (blocks_causal_claim IN (0,1)),
  blocks_expand INTEGER NOT NULL CHECK (blocks_expand IN (0,1)),
  detected_at TEXT NOT NULL CHECK (datetime(detected_at) IS NOT NULL),
  evidence_id TEXT NOT NULL,
  FOREIGN KEY (decision_contract_id)
    REFERENCES decision_contract(decision_contract_id),
  FOREIGN KEY (issue_category_code)
    REFERENCES issue_category_dim(issue_category_code),
  FOREIGN KEY (evidence_id) REFERENCES evidence_record(evidence_id)
) STRICT;

CREATE TABLE integrity_issue_event (
  integrity_issue_event_id TEXT PRIMARY KEY,
  integrity_issue_stream_id TEXT NOT NULL,
  event_sequence INTEGER NOT NULL CHECK (event_sequence>0),
  supersedes_integrity_issue_event_id TEXT,
  issue_state TEXT NOT NULL CHECK (issue_state IN (
    'OPEN','UNKNOWN','RESOLVED','ACCEPTED_LIMITATION'
  )),
  state_reason_code TEXT NOT NULL,
  occurred_at TEXT NOT NULL CHECK (datetime(occurred_at) IS NOT NULL),
  recorded_at TEXT NOT NULL CHECK (datetime(recorded_at) IS NOT NULL),
  evidence_id TEXT NOT NULL,
  CHECK (datetime(occurred_at)<=datetime(recorded_at)),
  UNIQUE (integrity_issue_stream_id,event_sequence),
  FOREIGN KEY (integrity_issue_stream_id)
    REFERENCES integrity_issue_stream(integrity_issue_stream_id),
  FOREIGN KEY (supersedes_integrity_issue_event_id)
    REFERENCES integrity_issue_event(integrity_issue_event_id),
  FOREIGN KEY (evidence_id) REFERENCES evidence_record(evidence_id)
) STRICT;

CREATE TABLE safety_review (
  safety_review_id TEXT PRIMARY KEY,
  decision_contract_id TEXT NOT NULL,
  safety_state TEXT NOT NULL CHECK (safety_state IN (
    'PASS','FAIL','UNKNOWN','NA'
  )),
  guardrail_expected_n INTEGER NOT NULL CHECK (guardrail_expected_n>=0),
  guardrail_observed_n INTEGER NOT NULL CHECK (guardrail_observed_n>=0),
  guardrail_missing_n INTEGER NOT NULL CHECK (guardrail_missing_n>=0),
  reviewed_at TEXT NOT NULL CHECK (datetime(reviewed_at) IS NOT NULL),
  recorded_at TEXT NOT NULL CHECK (datetime(recorded_at) IS NOT NULL),
  evidence_id TEXT NOT NULL,
  CHECK (guardrail_observed_n+guardrail_missing_n<=guardrail_expected_n),
  CHECK (datetime(reviewed_at)<=datetime(recorded_at)),
  UNIQUE (decision_contract_id,safety_review_id),
  FOREIGN KEY (decision_contract_id)
    REFERENCES decision_contract(decision_contract_id),
  FOREIGN KEY (evidence_id) REFERENCES evidence_record(evidence_id)
) STRICT;

CREATE TABLE experiment_cost_event (
  experiment_cost_event_id TEXT PRIMARY KEY,
  decision_contract_id TEXT NOT NULL,
  cost_type_code TEXT NOT NULL CHECK (cost_type_code IN (
    'CASH','FOUNDER_TIME'
  )),
  cost_category_code TEXT NOT NULL CHECK (cost_category_code IN (
    'IMPLEMENTATION','DELIVERY','MEASUREMENT','REVIEW','INCIDENT','OTHER'
  )),
  cash_cost_micros INTEGER,
  founder_minutes INTEGER,
  currency TEXT NOT NULL CHECK (
    length(currency)=3 AND currency=upper(currency)
  ),
  occurred_at TEXT NOT NULL CHECK (datetime(occurred_at) IS NOT NULL),
  recorded_at TEXT NOT NULL CHECK (datetime(recorded_at) IS NOT NULL),
  evidence_id TEXT NOT NULL,
  CHECK ((cost_type_code='CASH' AND cash_cost_micros IS NOT NULL
          AND cash_cost_micros>=0 AND founder_minutes IS NULL)
      OR (cost_type_code='FOUNDER_TIME' AND founder_minutes IS NOT NULL
          AND founder_minutes>=0 AND cash_cost_micros IS NULL)),
  CHECK (datetime(occurred_at)<=datetime(recorded_at)),
  FOREIGN KEY (decision_contract_id)
    REFERENCES decision_contract(decision_contract_id),
  FOREIGN KEY (evidence_id) REFERENCES evidence_record(evidence_id)
) STRICT;

-- -----------------------------------------------------------------------------
-- Immutable analysis and decision snapshots
-- -----------------------------------------------------------------------------

CREATE TABLE analysis_snapshot (
  analysis_snapshot_id TEXT PRIMARY KEY,
  decision_contract_id TEXT NOT NULL,
  snapshot_sequence INTEGER NOT NULL CHECK (snapshot_sequence>0),
  supersedes_analysis_snapshot_id TEXT,
  snapshot_as_of TEXT NOT NULL CHECK (datetime(snapshot_as_of) IS NOT NULL),
  evidence_cutoff_at TEXT NOT NULL
    CHECK (datetime(evidence_cutoff_at) IS NOT NULL),
  analysis_claim_cap_code TEXT NOT NULL,
  carryover_state TEXT NOT NULL CHECK (carryover_state IN (
    'NA','RESOLVED','UNKNOWN','FAILED'
  )),
  maturity_gate TEXT NOT NULL CHECK (maturity_gate IN (
    'COMPLETE','INCOMPLETE'
  )),
  integrity_gate TEXT NOT NULL CHECK (integrity_gate IN (
    'PASS','BLOCKED','UNKNOWN'
  )),
  safety_gate TEXT NOT NULL CHECK (safety_gate IN (
    'PASS','FAIL','UNKNOWN','NA'
  )),
  analysis_state TEXT NOT NULL CHECK (analysis_state IN (
    'READY','INCOMPLETE','BLOCKED','INCONCLUSIVE'
  )),
  assigned_n INTEGER NOT NULL CHECK (assigned_n>=0),
  expected_outcome_n INTEGER NOT NULL CHECK (expected_outcome_n>=0),
  matured_expected_n INTEGER NOT NULL CHECK (matured_expected_n>=0),
  classified_outcome_n INTEGER NOT NULL CHECK (classified_outcome_n>=0),
  open_integrity_issue_n INTEGER NOT NULL CHECK (open_integrity_issue_n>=0),
  blocking_causal_issue_n INTEGER NOT NULL CHECK (blocking_causal_issue_n>=0),
  blocking_expand_issue_n INTEGER NOT NULL CHECK (blocking_expand_issue_n>=0),
  cash_cost_micros INTEGER NOT NULL CHECK (cash_cost_micros>=0),
  founder_minutes INTEGER NOT NULL CHECK (founder_minutes>=0),
  founder_time_cost_micros INTEGER NOT NULL CHECK (founder_time_cost_micros>=0),
  fully_loaded_experiment_cost_micros INTEGER NOT NULL
    CHECK (fully_loaded_experiment_cost_micros>=0),
  safety_review_id TEXT NOT NULL,
  created_at TEXT NOT NULL CHECK (datetime(created_at) IS NOT NULL),
  evidence_id TEXT NOT NULL,
  CHECK (datetime(evidence_cutoff_at)<=datetime(snapshot_as_of)),
  CHECK (datetime(snapshot_as_of)<=datetime(created_at)),
  CHECK (matured_expected_n<=expected_outcome_n),
  CHECK (classified_outcome_n<=matured_expected_n),
  CHECK (founder_time_cost_micros+cash_cost_micros
         =fully_loaded_experiment_cost_micros),
  UNIQUE (decision_contract_id,snapshot_sequence),
  FOREIGN KEY (decision_contract_id)
    REFERENCES decision_contract(decision_contract_id),
  FOREIGN KEY (supersedes_analysis_snapshot_id)
    REFERENCES analysis_snapshot(analysis_snapshot_id),
  FOREIGN KEY (analysis_claim_cap_code)
    REFERENCES claim_level_dim(claim_level_code),
  FOREIGN KEY (safety_review_id) REFERENCES safety_review(safety_review_id),
  FOREIGN KEY (evidence_id) REFERENCES evidence_record(evidence_id)
) STRICT;

CREATE TABLE analysis_arm_readout (
  analysis_arm_readout_id TEXT PRIMARY KEY,
  analysis_snapshot_id TEXT NOT NULL,
  arm_id TEXT NOT NULL,
  metric_contract_id TEXT NOT NULL,
  assigned_n INTEGER NOT NULL CHECK (assigned_n>=0),
  expected_n INTEGER NOT NULL CHECK (expected_n>=0),
  matured_expected_n INTEGER NOT NULL CHECK (matured_expected_n>=0),
  classified_n INTEGER NOT NULL CHECK (classified_n>=0),
  observed_n INTEGER NOT NULL CHECK (observed_n>=0),
  missing_n INTEGER NOT NULL CHECK (missing_n>=0),
  observed_sum REAL,
  observed_mean REAL,
  raw_itt_mean REAL,
  readout_state TEXT NOT NULL CHECK (readout_state IN (
    'COMPLETE','MISSING','IMMATURE','NO_EXPECTATION'
  )),
  created_at TEXT NOT NULL CHECK (datetime(created_at) IS NOT NULL),
  evidence_id TEXT NOT NULL,
  CHECK (matured_expected_n<=expected_n),
  CHECK (classified_n<=matured_expected_n),
  CHECK (observed_n+missing_n=classified_n),
  CHECK ((observed_n=0 AND observed_sum IS NULL AND observed_mean IS NULL)
      OR (observed_n>0 AND observed_sum IS NOT NULL AND observed_mean IS NOT NULL)),
  CHECK (raw_itt_mean IS NULL
      OR (missing_n=0 AND observed_n=assigned_n
          AND classified_n=matured_expected_n
          AND matured_expected_n=expected_n AND expected_n=assigned_n)),
  UNIQUE (analysis_snapshot_id,arm_id,metric_contract_id),
  FOREIGN KEY (analysis_snapshot_id)
    REFERENCES analysis_snapshot(analysis_snapshot_id),
  FOREIGN KEY (arm_id) REFERENCES arm_contract(arm_id),
  FOREIGN KEY (metric_contract_id)
    REFERENCES metric_contract_version(metric_contract_id),
  FOREIGN KEY (evidence_id) REFERENCES evidence_record(evidence_id)
) STRICT;

CREATE TABLE decision_snapshot (
  decision_snapshot_id TEXT PRIMARY KEY,
  decision_contract_id TEXT NOT NULL,
  decision_sequence INTEGER NOT NULL CHECK (decision_sequence>0),
  supersedes_decision_snapshot_id TEXT,
  analysis_snapshot_id TEXT NOT NULL,
  decision_state TEXT NOT NULL CHECK (decision_state IN (
    'STOP','PAUSE','UNKNOWN','INCONCLUSIVE','CHANGE','MAINTAIN','EXPAND'
  )),
  claimed_interpretation_code TEXT NOT NULL,
  action_scope_code TEXT NOT NULL,
  decided_at TEXT NOT NULL CHECK (datetime(decided_at) IS NOT NULL),
  recorded_at TEXT NOT NULL CHECK (datetime(recorded_at) IS NOT NULL),
  decided_by_role_code TEXT NOT NULL,
  decision_ref_hash TEXT NOT NULL CHECK (
    length(decision_ref_hash)=64
    AND decision_ref_hash=lower(decision_ref_hash)
    AND decision_ref_hash NOT GLOB '*[^0-9a-f]*'
  ),
  evidence_id TEXT NOT NULL,
  CHECK (datetime(decided_at)<=datetime(recorded_at)),
  UNIQUE (decision_contract_id,decision_sequence),
  FOREIGN KEY (decision_contract_id)
    REFERENCES decision_contract(decision_contract_id),
  FOREIGN KEY (supersedes_decision_snapshot_id)
    REFERENCES decision_snapshot(decision_snapshot_id),
  FOREIGN KEY (analysis_snapshot_id)
    REFERENCES analysis_snapshot(analysis_snapshot_id),
  FOREIGN KEY (claimed_interpretation_code)
    REFERENCES claim_level_dim(claim_level_code),
  FOREIGN KEY (evidence_id) REFERENCES evidence_record(evidence_id)
) STRICT;

-- Recompute each immutable arm readout against the owning snapshot's two
-- clocks.  This view deliberately does not use the model-wide default as-of:
-- old snapshots must remain reproducible after later facts are appended.
CREATE VIEW v_snapshot_itt_arm_readout AS
WITH readout_key AS (
  SELECT s.analysis_snapshot_id,s.decision_contract_id,s.snapshot_as_of,
         s.evidence_cutoff_at,a.arm_id,a.arm_code,a.arm_role,
         a.comparison_pair_code,m.metric_contract_id,m.metric_code,m.metric_role
  FROM analysis_snapshot s
  JOIN arm_contract a
    ON a.decision_contract_id=s.decision_contract_id
   AND datetime(a.arm_registered_at)<=datetime(s.evidence_cutoff_at)
  JOIN metric_contract_version m
    ON m.decision_contract_id=s.decision_contract_id
   AND datetime(m.registered_at)<=datetime(s.evidence_cutoff_at)
), fact AS (
  SELECT k.analysis_snapshot_id,k.decision_contract_id,k.snapshot_as_of,
         k.evidence_cutoff_at,k.arm_id,k.metric_contract_id,x.assignment_id,
         e.expected_outcome_id,e.expected_maturity_at,
         o.outcome_observation_id,o.outcome_state,o.numeric_value
  FROM readout_key k
  LEFT JOIN experiment_assignment x
    ON x.decision_contract_id=k.decision_contract_id
   AND x.arm_id=k.arm_id
   AND datetime(x.recorded_at)<=datetime(k.evidence_cutoff_at)
  LEFT JOIN expected_outcome_spine e
    ON e.assignment_id=x.assignment_id
   AND e.metric_contract_id=k.metric_contract_id
   AND datetime(e.registered_at)<=datetime(k.evidence_cutoff_at)
  LEFT JOIN outcome_observation o
    ON o.expected_outcome_id=e.expected_outcome_id
   AND datetime(o.recorded_at)<=datetime(k.evidence_cutoff_at)
   AND datetime(o.assessed_at)<=datetime(k.snapshot_as_of)
), rollup AS (
  SELECT k.analysis_snapshot_id,k.decision_contract_id,k.arm_id,k.arm_code,
         k.arm_role,k.comparison_pair_code,k.metric_contract_id,k.metric_code,
         k.metric_role,count(DISTINCT f.assignment_id) AS assigned_n,
         count(f.expected_outcome_id) AS expected_n,
         coalesce(sum(CASE
           WHEN datetime(f.expected_maturity_at)<=datetime(k.snapshot_as_of)
             THEN 1 ELSE 0 END),0) AS matured_expected_n,
         count(f.outcome_observation_id) AS classified_n,
         coalesce(sum(CASE WHEN f.outcome_state='OBSERVED_VALUE'
                           THEN 1 ELSE 0 END),0) AS observed_n,
         coalesce(sum(CASE WHEN f.outcome_state='MISSING_CONFIRMED'
                           THEN 1 ELSE 0 END),0) AS missing_n,
         sum(CASE WHEN f.outcome_state='OBSERVED_VALUE'
                  THEN f.numeric_value END) AS observed_sum
  FROM readout_key k
  LEFT JOIN fact f
    ON f.analysis_snapshot_id=k.analysis_snapshot_id
   AND f.arm_id=k.arm_id
   AND f.metric_contract_id=k.metric_contract_id
  GROUP BY k.analysis_snapshot_id,k.decision_contract_id,k.arm_id,k.arm_code,
           k.arm_role,k.comparison_pair_code,k.metric_contract_id,k.metric_code,
           k.metric_role
)
SELECT analysis_snapshot_id,decision_contract_id,arm_id,arm_code,arm_role,
       comparison_pair_code,metric_contract_id,metric_code,metric_role,
       assigned_n,expected_n,matured_expected_n,classified_n,observed_n,
       missing_n,observed_sum,
       CASE WHEN observed_n>0
            THEN observed_sum*1.0/observed_n END AS observed_mean,
       CASE WHEN assigned_n>0 AND missing_n=0 AND observed_n=assigned_n
                  AND classified_n=matured_expected_n
                  AND matured_expected_n=expected_n
                  AND expected_n=assigned_n
            THEN observed_sum*1.0/assigned_n END AS raw_itt_mean,
       CASE WHEN expected_n=0 THEN 'NO_EXPECTATION'
            WHEN matured_expected_n<expected_n THEN 'IMMATURE'
            WHEN classified_n<matured_expected_n THEN 'MISSING'
            WHEN missing_n>0 THEN 'MISSING'
            WHEN observed_n=assigned_n AND expected_n=assigned_n
              THEN 'COMPLETE'
            ELSE 'NO_EXPECTATION' END AS readout_state
FROM rollup;

-- Facts that can change an experiment decision after an analysis cutoff.
-- Conservative actions may react immediately, but EXPAND requires a refreshed
-- analysis whenever one of these facts was recorded before the decision.
CREATE VIEW v_decision_relevant_fact AS
SELECT decision_contract_id,'ARM_CONTRACT' AS fact_type,
       arm_id AS fact_id,arm_registered_at AS fact_recorded_at
FROM arm_contract
UNION ALL
SELECT decision_contract_id,'METRIC_CONTRACT',
       metric_contract_id AS fact_id,registered_at AS fact_recorded_at
FROM metric_contract_version
UNION ALL
SELECT decision_contract_id,'SWITCHBACK_PERIOD',period_contract_id,registered_at
FROM switchback_period_contract
UNION ALL
SELECT decision_contract_id,'ASSIGNMENT',assignment_id,recorded_at
FROM experiment_assignment
UNION ALL
SELECT decision_contract_id,'EXPOSURE',exposure_event_id,recorded_at
FROM exposure_event
UNION ALL
SELECT decision_contract_id,'CONTAMINATION',contamination_event_id,recorded_at
FROM contamination_event
UNION ALL
SELECT decision_contract_id,'EXPECTED_OUTCOME',expected_outcome_id,registered_at
FROM expected_outcome_spine
UNION ALL
SELECT x.decision_contract_id,'OUTCOME',o.outcome_observation_id,o.recorded_at
FROM outcome_observation o
JOIN expected_outcome_spine x USING (expected_outcome_id)
UNION ALL
SELECT decision_contract_id,'INTEGRITY_STREAM',integrity_issue_stream_id,detected_at
FROM integrity_issue_stream
UNION ALL
SELECT s.decision_contract_id,'INTEGRITY',e.integrity_issue_event_id,e.recorded_at
FROM integrity_issue_event e
JOIN integrity_issue_stream s USING (integrity_issue_stream_id)
UNION ALL
SELECT decision_contract_id,'SAFETY',safety_review_id,recorded_at
FROM safety_review
UNION ALL
SELECT decision_contract_id,'COST',experiment_cost_event_id,recorded_at
FROM experiment_cost_event;

-- -----------------------------------------------------------------------------
-- Required semantic triggers
-- -----------------------------------------------------------------------------

CREATE TRIGGER decision_contract_claim_cap_guard
BEFORE INSERT ON decision_contract
BEGIN
  SELECT RAISE(ABORT,'causal claim exceeds design claim cap')
  WHERE (SELECT claim_rank FROM claim_level_dim
         WHERE claim_level_code=NEW.analysis_claim_cap_code)
      > (SELECT c.claim_rank
         FROM design_type_dim d
         JOIN claim_level_dim c
           ON c.claim_level_code=d.design_claim_cap_code
         WHERE d.design_type_code=NEW.design_type_code);
  SELECT RAISE(ABORT,'switchback requires an explicit carryover plan')
  WHERE NEW.design_type_code='RANDOMIZED_SWITCHBACK'
    AND NEW.carryover_plan_code='NA';
  SELECT RAISE(ABORT,'non-switchback design cannot declare a carryover plan')
  WHERE NEW.design_type_code<>'RANDOMIZED_SWITCHBACK'
    AND NEW.carryover_plan_code<>'NA';
END;

CREATE TRIGGER metric_version_guard
BEFORE INSERT ON metric_contract_version
BEGIN
  SELECT RAISE(ABORT,'metric v1 cannot supersede another metric')
  WHERE NEW.metric_version=1 AND NEW.supersedes_metric_contract_id IS NOT NULL;
  SELECT RAISE(ABORT,'metric version after v1 requires exact predecessor')
  WHERE NEW.metric_version>1 AND NOT EXISTS (
    SELECT 1 FROM metric_contract_version p
    WHERE p.metric_contract_id=NEW.supersedes_metric_contract_id
      AND p.metric_stream_id=NEW.metric_stream_id
      AND p.metric_version=NEW.metric_version-1
      AND p.decision_contract_id=NEW.decision_contract_id
  );
  SELECT RAISE(ABORT,'metric version registration must follow its predecessor')
  WHERE NEW.metric_version>1 AND EXISTS (
    SELECT 1 FROM metric_contract_version p
    WHERE p.metric_contract_id=NEW.supersedes_metric_contract_id
      AND datetime(NEW.registered_at)<=datetime(p.registered_at)
  );
END;

CREATE TRIGGER period_contract_guard
BEFORE INSERT ON switchback_period_contract
BEGIN
  SELECT RAISE(ABORT,'period contract is allowed only for switchback design')
  WHERE (SELECT design_type_code FROM decision_contract
         WHERE decision_contract_id=NEW.decision_contract_id)
        <>'RANDOMIZED_SWITCHBACK';
  SELECT RAISE(ABORT,'period arm must belong to the same decision contract')
  WHERE NOT EXISTS (
    SELECT 1 FROM arm_contract a
    WHERE a.arm_id=NEW.arm_id
      AND a.decision_contract_id=NEW.decision_contract_id
  );
END;

CREATE TRIGGER eligibility_scope_guard
BEFORE INSERT ON eligibility_review
BEGIN
  SELECT RAISE(ABORT,'eligibility unit must belong to decision contract')
  WHERE NOT EXISTS (
    SELECT 1 FROM experiment_unit u
    WHERE u.unit_id=NEW.unit_id
      AND u.decision_contract_id=NEW.decision_contract_id
  );
  SELECT RAISE(ABORT,'eligibility review must use contracted rule version')
  WHERE NEW.eligibility_rule_version<>(
    SELECT eligibility_rule_version FROM decision_contract
    WHERE decision_contract_id=NEW.decision_contract_id
  );
END;

CREATE TRIGGER exclusion_pre_assignment_guard
BEFORE INSERT ON pre_assignment_exclusion
BEGIN
  SELECT RAISE(ABORT,'post-assignment exclusion is prohibited; preserve ITT')
  WHERE EXISTS (
    SELECT 1 FROM experiment_assignment a
    WHERE a.decision_contract_id=NEW.decision_contract_id
      AND a.unit_id=NEW.unit_id
  );
END;

CREATE TRIGGER assignment_semantic_guard
BEFORE INSERT ON experiment_assignment
BEGIN
  SELECT RAISE(ABORT,'one assignment per unit per decision contract')
  WHERE EXISTS (
    SELECT 1 FROM experiment_assignment a
    WHERE a.decision_contract_id=NEW.decision_contract_id
      AND a.unit_id=NEW.unit_id
  );
  SELECT RAISE(ABORT,'assignment requires pre-assignment PASS eligibility')
  WHERE NOT EXISTS (
    SELECT 1 FROM eligibility_review e
    WHERE e.eligibility_review_id=NEW.eligibility_review_id
      AND e.decision_contract_id=NEW.decision_contract_id
      AND e.unit_id=NEW.unit_id
      AND e.eligibility_state='PASS'
      AND datetime(e.assessed_at)<=datetime(NEW.assigned_at)
      AND datetime(e.recorded_at)<=datetime(NEW.assigned_at)
  );
  SELECT RAISE(ABORT,'excluded unit cannot be assigned')
  WHERE EXISTS (
    SELECT 1 FROM pre_assignment_exclusion x
    WHERE x.decision_contract_id=NEW.decision_contract_id
      AND x.unit_id=NEW.unit_id
      AND datetime(x.excluded_at)<=datetime(NEW.assigned_at)
  );
  SELECT RAISE(ABORT,'assignment unit and arm must match decision contract')
  WHERE NOT EXISTS (
    SELECT 1 FROM experiment_unit u
    JOIN arm_contract a ON a.arm_id=NEW.arm_id
    WHERE u.unit_id=NEW.unit_id
      AND u.decision_contract_id=NEW.decision_contract_id
      AND a.decision_contract_id=NEW.decision_contract_id
  );
  SELECT RAISE(ABORT,'assignment outside contracted enrollment window')
  WHERE NOT EXISTS (
    SELECT 1 FROM decision_contract c
    WHERE c.decision_contract_id=NEW.decision_contract_id
      AND datetime(NEW.assigned_at)>=datetime(c.enrollment_opens_at)
      AND datetime(NEW.assigned_at)<=datetime(c.enrollment_closes_at)
  );
  SELECT RAISE(ABORT,'randomized design requires randomized assignment method')
  WHERE (SELECT requires_random_assignment FROM design_type_dim d
         JOIN decision_contract c USING (design_type_code)
         WHERE c.decision_contract_id=NEW.decision_contract_id)=1
    AND NEW.assignment_method_code NOT IN ('HASH_RANDOMIZED','BLOCK_RANDOMIZED');
  SELECT RAISE(ABORT,'observational design requires observed membership')
  WHERE (SELECT design_type_code FROM decision_contract
         WHERE decision_contract_id=NEW.decision_contract_id)
        ='OBSERVATIONAL_ASSOCIATION'
    AND NEW.assignment_method_code<>'OBSERVED_MEMBERSHIP';
END;

CREATE TRIGGER exposure_semantic_guard
BEFORE INSERT ON exposure_event
BEGIN
  SELECT RAISE(ABORT,'exposure cannot occur before assignment')
  WHERE NOT EXISTS (
    SELECT 1 FROM experiment_assignment a
    WHERE a.assignment_id=NEW.assignment_id
      AND a.decision_contract_id=NEW.decision_contract_id
      AND datetime(NEW.occurred_at)>=datetime(a.assigned_at)
  );
  SELECT RAISE(ABORT,'switchback exposure requires matching period contract')
  WHERE NEW.period_contract_id IS NOT NULL AND NOT EXISTS (
    SELECT 1 FROM switchback_period_contract p
    JOIN experiment_assignment a ON a.assignment_id=NEW.assignment_id
    WHERE p.period_contract_id=NEW.period_contract_id
      AND p.decision_contract_id=NEW.decision_contract_id
      AND p.arm_id=a.arm_id
      AND p.scheduled_intervention_version=NEW.intervention_version
      AND datetime(NEW.occurred_at)>=datetime(p.period_starts_at)
      AND datetime(NEW.occurred_at)<datetime(p.period_ends_at)
  );
END;

CREATE TRIGGER contamination_semantic_guard
BEFORE INSERT ON contamination_event
BEGIN
  SELECT RAISE(ABORT,'contamination cannot occur before assignment')
  WHERE NOT EXISTS (
    SELECT 1 FROM experiment_assignment a
    WHERE a.assignment_id=NEW.assignment_id
      AND a.decision_contract_id=NEW.decision_contract_id
      AND datetime(NEW.occurred_at)>=datetime(a.assigned_at)
  );
  SELECT RAISE(ABORT,'contamination event sequence requires exact predecessor')
  WHERE NEW.event_sequence>1 AND NOT EXISTS (
    SELECT 1 FROM contamination_event p
    WHERE p.contamination_event_id=NEW.supersedes_contamination_event_id
      AND p.contamination_stream_id=NEW.contamination_stream_id
      AND p.event_sequence=NEW.event_sequence-1
  );
  SELECT RAISE(ABORT,'first contamination event cannot supersede another event')
  WHERE NEW.event_sequence=1 AND NEW.supersedes_contamination_event_id IS NOT NULL;
END;

CREATE TRIGGER expected_outcome_semantic_guard
BEFORE INSERT ON expected_outcome_spine
BEGIN
  SELECT RAISE(ABORT,'expected outcome assignment and metric must match contract')
  WHERE NOT EXISTS (
    SELECT 1 FROM experiment_assignment a
    JOIN metric_contract_version m
      ON m.metric_contract_id=NEW.metric_contract_id
    WHERE a.assignment_id=NEW.assignment_id
      AND a.decision_contract_id=NEW.decision_contract_id
      AND m.decision_contract_id=NEW.decision_contract_id
  );
  SELECT RAISE(ABORT,'expected outcome window cannot open before assignment')
  WHERE datetime(NEW.outcome_window_opens_at)<datetime((
    SELECT assigned_at FROM experiment_assignment
    WHERE assignment_id=NEW.assignment_id
  ));
  SELECT RAISE(ABORT,'expected outcome must be registered before its window opens')
  WHERE datetime(NEW.registered_at)>datetime(NEW.outcome_window_opens_at);
  SELECT RAISE(ABORT,'expected outcome must use metric version current at assignment')
  WHERE EXISTS (
    SELECT 1
    FROM metric_contract_version chosen
    JOIN experiment_assignment a ON a.assignment_id=NEW.assignment_id
    JOIN metric_contract_version later
      ON later.metric_stream_id=chosen.metric_stream_id
     AND later.metric_version>chosen.metric_version
     AND later.decision_contract_id=chosen.decision_contract_id
    WHERE chosen.metric_contract_id=NEW.metric_contract_id
      AND datetime(later.registered_at)<=datetime(a.assigned_at)
  );
END;

CREATE TRIGGER outcome_semantic_guard
BEFORE INSERT ON outcome_observation
BEGIN
  SELECT RAISE(ABORT,'outcome classification cannot be recorded before maturity')
  WHERE datetime(NEW.assessed_at)<datetime((
    SELECT expected_maturity_at FROM expected_outcome_spine
    WHERE expected_outcome_id=NEW.expected_outcome_id
  ));
  SELECT RAISE(ABORT,'observed outcome cannot occur before assignment or outside window')
  WHERE NEW.outcome_state='OBSERVED_VALUE' AND NOT EXISTS (
    SELECT 1
    FROM expected_outcome_spine s
    JOIN experiment_assignment a USING (assignment_id)
    WHERE s.expected_outcome_id=NEW.expected_outcome_id
      AND datetime(NEW.outcome_occurred_at)>=datetime(a.assigned_at)
      AND datetime(NEW.outcome_occurred_at)>=datetime(s.outcome_window_opens_at)
      AND datetime(NEW.outcome_occurred_at)<=datetime(s.outcome_window_closes_at)
  );
  SELECT RAISE(ABORT,'binary outcome must be measured zero or one')
  WHERE NEW.outcome_state='OBSERVED_VALUE'
    AND (SELECT value_kind
         FROM expected_outcome_spine s
         JOIN metric_contract_version m USING (metric_contract_id)
         WHERE s.expected_outcome_id=NEW.expected_outcome_id)='BINARY'
    AND NEW.numeric_value NOT IN (0.0,1.0);
END;

CREATE TRIGGER integrity_issue_stream_semantic_guard
BEFORE INSERT ON integrity_issue_stream
BEGIN
  SELECT RAISE(ABORT,'integrity issue block flags must match category defaults')
  WHERE NOT EXISTS (
    SELECT 1 FROM issue_category_dim d
    WHERE d.issue_category_code=NEW.issue_category_code
      AND d.default_blocks_causal=NEW.blocks_causal_claim
      AND d.default_blocks_expand=NEW.blocks_expand
  );
END;

CREATE TRIGGER safety_review_semantic_guard
BEFORE INSERT ON safety_review
BEGIN
  SELECT RAISE(ABORT,'safety review guardrail counts do not reconcile to review-time facts')
  WHERE NEW.guardrail_expected_n<>(
      SELECT count(*) FROM expected_outcome_spine x
      JOIN metric_contract_version m USING (metric_contract_id)
      WHERE x.decision_contract_id=NEW.decision_contract_id
        AND m.metric_role='GUARDRAIL'
        AND datetime(x.registered_at)<=datetime(NEW.reviewed_at)
    )
    OR NEW.guardrail_observed_n<>(
      SELECT count(*) FROM outcome_observation o
      JOIN expected_outcome_spine x USING (expected_outcome_id)
      JOIN metric_contract_version m USING (metric_contract_id)
      WHERE x.decision_contract_id=NEW.decision_contract_id
        AND m.metric_role='GUARDRAIL'
        AND o.outcome_state='OBSERVED_VALUE'
        AND datetime(x.registered_at)<=datetime(NEW.reviewed_at)
        AND datetime(o.assessed_at)<=datetime(NEW.reviewed_at)
        AND datetime(o.recorded_at)<=datetime(NEW.reviewed_at)
    )
    OR NEW.guardrail_missing_n<>(
      SELECT count(*) FROM outcome_observation o
      JOIN expected_outcome_spine x USING (expected_outcome_id)
      JOIN metric_contract_version m USING (metric_contract_id)
      WHERE x.decision_contract_id=NEW.decision_contract_id
        AND m.metric_role='GUARDRAIL'
        AND o.outcome_state='MISSING_CONFIRMED'
        AND datetime(x.registered_at)<=datetime(NEW.reviewed_at)
        AND datetime(o.assessed_at)<=datetime(NEW.reviewed_at)
        AND datetime(o.recorded_at)<=datetime(NEW.reviewed_at)
    );
  SELECT RAISE(ABORT,'safety NA is valid only when no guardrail outcomes are expected')
  WHERE (NEW.safety_state='NA')<>(NEW.guardrail_expected_n=0);
  SELECT RAISE(ABORT,'safety PASS requires complete guardrail classification with no missing outcomes')
  WHERE NEW.safety_state='PASS'
    AND (NEW.guardrail_observed_n<>NEW.guardrail_expected_n
      OR NEW.guardrail_missing_n<>0);
END;

CREATE TRIGGER integrity_event_sequence_guard
BEFORE INSERT ON integrity_issue_event
BEGIN
  SELECT RAISE(ABORT,'integrity issue event after v1 requires exact predecessor')
  WHERE NEW.event_sequence>1 AND NOT EXISTS (
    SELECT 1 FROM integrity_issue_event p
    WHERE p.integrity_issue_event_id=NEW.supersedes_integrity_issue_event_id
      AND p.integrity_issue_stream_id=NEW.integrity_issue_stream_id
      AND p.event_sequence=NEW.event_sequence-1
  );
  SELECT RAISE(ABORT,'first integrity issue event cannot supersede another event')
  WHERE NEW.event_sequence=1 AND NEW.supersedes_integrity_issue_event_id IS NOT NULL;
END;

CREATE TRIGGER analysis_snapshot_semantic_guard
BEFORE INSERT ON analysis_snapshot
BEGIN
  SELECT RAISE(ABORT,'analysis snapshot v1 cannot supersede another snapshot')
  WHERE NEW.snapshot_sequence=1
    AND NEW.supersedes_analysis_snapshot_id IS NOT NULL;
  SELECT RAISE(ABORT,'analysis snapshot after v1 requires exact same-contract predecessor')
  WHERE NEW.snapshot_sequence>1 AND NOT EXISTS (
    SELECT 1 FROM analysis_snapshot p
    WHERE p.analysis_snapshot_id=NEW.supersedes_analysis_snapshot_id
      AND p.decision_contract_id=NEW.decision_contract_id
      AND p.snapshot_sequence=NEW.snapshot_sequence-1
  );
  SELECT RAISE(ABORT,'analysis snapshot chain clocks must be monotone')
  WHERE NEW.snapshot_sequence>1 AND EXISTS (
    SELECT 1 FROM analysis_snapshot p
    WHERE p.analysis_snapshot_id=NEW.supersedes_analysis_snapshot_id
      AND p.decision_contract_id=NEW.decision_contract_id
      AND p.snapshot_sequence=NEW.snapshot_sequence-1
      AND (datetime(NEW.evidence_cutoff_at)<datetime(p.evidence_cutoff_at)
        OR datetime(NEW.snapshot_as_of)<datetime(p.snapshot_as_of)
        OR datetime(NEW.created_at)<=datetime(p.created_at))
  );
  SELECT RAISE(ABORT,'analysis causal claim exceeds decision contract cap')
  WHERE (SELECT claim_rank FROM claim_level_dim
         WHERE claim_level_code=NEW.analysis_claim_cap_code)
      > (SELECT l.claim_rank
         FROM decision_contract c
         JOIN claim_level_dim l
           ON l.claim_level_code=c.analysis_claim_cap_code
         WHERE c.decision_contract_id=NEW.decision_contract_id);
  SELECT RAISE(ABORT,'switchback causal claim requires resolved carryover')
  WHERE (SELECT design_type_code FROM decision_contract
         WHERE decision_contract_id=NEW.decision_contract_id)
        ='RANDOMIZED_SWITCHBACK'
    AND NEW.analysis_claim_cap_code='CAUSAL_ITT_WITHIN_CONTRACT'
    AND NEW.carryover_state<>'RESOLVED';
  SELECT RAISE(ABORT,'non-switchback analysis must mark carryover NA')
  WHERE (SELECT design_type_code FROM decision_contract
         WHERE decision_contract_id=NEW.decision_contract_id)
        <>'RANDOMIZED_SWITCHBACK'
    AND NEW.carryover_state<>'NA';
  SELECT RAISE(ABORT,'analysis assigned count does not reconcile')
  WHERE NEW.assigned_n<>(
    SELECT count(*) FROM experiment_assignment a
    WHERE a.decision_contract_id=NEW.decision_contract_id
      AND datetime(a.recorded_at)<=datetime(NEW.evidence_cutoff_at)
  );
  SELECT RAISE(ABORT,'analysis expected outcome count does not reconcile')
  WHERE NEW.expected_outcome_n<>(
    SELECT count(*) FROM expected_outcome_spine s
    WHERE s.decision_contract_id=NEW.decision_contract_id
      AND datetime(s.registered_at)<=datetime(NEW.evidence_cutoff_at)
  );
  SELECT RAISE(ABORT,'analysis matured expected count does not reconcile')
  WHERE NEW.matured_expected_n<>(
    SELECT count(*) FROM expected_outcome_spine s
    WHERE s.decision_contract_id=NEW.decision_contract_id
      AND datetime(s.registered_at)<=datetime(NEW.evidence_cutoff_at)
      AND datetime(s.expected_maturity_at)<=datetime(NEW.snapshot_as_of)
  );
  SELECT RAISE(ABORT,'analysis classified outcome count does not reconcile')
  WHERE NEW.classified_outcome_n<>(
    SELECT count(*)
    FROM outcome_observation o
    JOIN expected_outcome_spine s USING (expected_outcome_id)
    WHERE s.decision_contract_id=NEW.decision_contract_id
      AND datetime(o.recorded_at)<=datetime(NEW.evidence_cutoff_at)
      AND datetime(o.assessed_at)<=datetime(NEW.snapshot_as_of)
  );
  SELECT RAISE(ABORT,'analysis maturity gate does not reconcile')
  WHERE (NEW.maturity_gate='COMPLETE')<>(
    NEW.expected_outcome_n=NEW.matured_expected_n
    AND NEW.matured_expected_n=NEW.classified_outcome_n
  );
  SELECT RAISE(ABORT,'analysis open integrity issue count does not reconcile')
  WHERE NEW.open_integrity_issue_n<>(
    SELECT count(*)
    FROM integrity_issue_stream s
    JOIN integrity_issue_event e
      ON e.integrity_issue_stream_id=s.integrity_issue_stream_id
    WHERE s.decision_contract_id=NEW.decision_contract_id
      AND datetime(e.recorded_at)<=datetime(NEW.evidence_cutoff_at)
      AND e.event_sequence=(
        SELECT max(e2.event_sequence)
        FROM integrity_issue_event e2
        WHERE e2.integrity_issue_stream_id=s.integrity_issue_stream_id
          AND datetime(e2.recorded_at)<=datetime(NEW.evidence_cutoff_at)
      )
      AND e.issue_state IN ('OPEN','UNKNOWN')
  );
  SELECT RAISE(ABORT,'analysis blocking causal issue count does not reconcile')
  WHERE NEW.blocking_causal_issue_n<>(
    SELECT count(*)
    FROM integrity_issue_stream s
    JOIN integrity_issue_event e
      ON e.integrity_issue_stream_id=s.integrity_issue_stream_id
    WHERE s.decision_contract_id=NEW.decision_contract_id
      AND s.blocks_causal_claim=1
      AND datetime(e.recorded_at)<=datetime(NEW.evidence_cutoff_at)
      AND e.event_sequence=(
        SELECT max(e2.event_sequence)
        FROM integrity_issue_event e2
        WHERE e2.integrity_issue_stream_id=s.integrity_issue_stream_id
          AND datetime(e2.recorded_at)<=datetime(NEW.evidence_cutoff_at)
      )
      AND e.issue_state IN ('OPEN','UNKNOWN')
  );
  SELECT RAISE(ABORT,'analysis blocking expand issue count does not reconcile')
  WHERE NEW.blocking_expand_issue_n<>(
    SELECT count(*)
    FROM integrity_issue_stream s
    JOIN integrity_issue_event e
      ON e.integrity_issue_stream_id=s.integrity_issue_stream_id
    WHERE s.decision_contract_id=NEW.decision_contract_id
      AND s.blocks_expand=1
      AND datetime(e.recorded_at)<=datetime(NEW.evidence_cutoff_at)
      AND e.event_sequence=(
        SELECT max(e2.event_sequence)
        FROM integrity_issue_event e2
        WHERE e2.integrity_issue_stream_id=s.integrity_issue_stream_id
          AND datetime(e2.recorded_at)<=datetime(NEW.evidence_cutoff_at)
      )
      AND e.issue_state IN ('OPEN','UNKNOWN')
  );
  SELECT RAISE(ABORT,'analysis integrity gate does not reconcile')
  WHERE (NEW.open_integrity_issue_n=0 AND NEW.integrity_gate<>'PASS')
     OR (NEW.open_integrity_issue_n>0 AND NEW.integrity_gate<>'BLOCKED');
  SELECT RAISE(ABORT,'analysis safety review does not match contract or gate')
  WHERE NOT EXISTS (
    SELECT 1 FROM safety_review s
    WHERE s.safety_review_id=NEW.safety_review_id
      AND s.decision_contract_id=NEW.decision_contract_id
      AND s.safety_state=NEW.safety_gate
      AND datetime(s.recorded_at)<=datetime(NEW.evidence_cutoff_at)
  );
  SELECT RAISE(ABORT,'analysis must use latest safety review at evidence cutoff')
  WHERE NEW.safety_review_id IS NOT (
    SELECT s.safety_review_id FROM safety_review s
    WHERE s.decision_contract_id=NEW.decision_contract_id
      AND datetime(s.recorded_at)<=datetime(NEW.evidence_cutoff_at)
    ORDER BY datetime(s.recorded_at) DESC,datetime(s.reviewed_at) DESC,
             s.safety_review_id DESC
    LIMIT 1
  );
  SELECT RAISE(ABORT,'analysis cost does not reconcile')
  WHERE NEW.cash_cost_micros<>coalesce((
      SELECT sum(cash_cost_micros) FROM experiment_cost_event c
      WHERE c.decision_contract_id=NEW.decision_contract_id
        AND c.cost_type_code='CASH'
        AND datetime(c.recorded_at)<=datetime(NEW.evidence_cutoff_at)
    ),0)
    OR NEW.founder_minutes<>coalesce((
      SELECT sum(founder_minutes) FROM experiment_cost_event c
      WHERE c.decision_contract_id=NEW.decision_contract_id
        AND c.cost_type_code='FOUNDER_TIME'
        AND datetime(c.recorded_at)<=datetime(NEW.evidence_cutoff_at)
    ),0)
    OR NEW.founder_time_cost_micros<>(
      NEW.founder_minutes*(SELECT founder_rate_micros_per_minute
                           FROM model_setting WHERE singleton_id=1)
    );
  SELECT RAISE(ABORT,'causal analysis cannot retain blocking causal integrity issues')
  WHERE NEW.analysis_claim_cap_code='CAUSAL_ITT_WITHIN_CONTRACT'
    AND NEW.blocking_causal_issue_n<>0;
END;

CREATE TRIGGER analysis_arm_readout_guard
BEFORE INSERT ON analysis_arm_readout
BEGIN
  SELECT RAISE(ABORT,'analysis readout arm and metric must match snapshot contract')
  WHERE NOT EXISTS (
    SELECT 1 FROM analysis_snapshot s
    JOIN arm_contract a ON a.arm_id=NEW.arm_id
    JOIN metric_contract_version m
      ON m.metric_contract_id=NEW.metric_contract_id
    WHERE s.analysis_snapshot_id=NEW.analysis_snapshot_id
      AND a.decision_contract_id=s.decision_contract_id
      AND m.decision_contract_id=s.decision_contract_id
  );
  SELECT RAISE(ABORT,'analysis readout cannot predate its snapshot')
  WHERE datetime(NEW.created_at)<datetime((
    SELECT created_at FROM analysis_snapshot
    WHERE analysis_snapshot_id=NEW.analysis_snapshot_id
  ));
  SELECT RAISE(ABORT,'analysis readout does not reconcile to snapshot source facts')
  WHERE NOT EXISTS (
    SELECT 1 FROM v_snapshot_itt_arm_readout r
    WHERE r.analysis_snapshot_id=NEW.analysis_snapshot_id
      AND r.arm_id=NEW.arm_id
      AND r.metric_contract_id=NEW.metric_contract_id
      AND r.assigned_n=NEW.assigned_n
      AND r.expected_n=NEW.expected_n
      AND r.matured_expected_n=NEW.matured_expected_n
      AND r.classified_n=NEW.classified_n
      AND r.observed_n=NEW.observed_n
      AND r.missing_n=NEW.missing_n
      AND r.observed_sum IS NEW.observed_sum
      AND r.observed_mean IS NEW.observed_mean
      AND r.raw_itt_mean IS NEW.raw_itt_mean
      AND r.readout_state=NEW.readout_state
  );
  SELECT RAISE(ABORT,'missing outcomes cannot be aggregated as zero')
  WHERE NEW.missing_n>0 AND NEW.raw_itt_mean IS NOT NULL;
  SELECT RAISE(ABORT,'raw ITT mean requires complete assigned outcomes')
  WHERE NEW.raw_itt_mean IS NOT NULL AND NOT (
    NEW.missing_n=0 AND NEW.observed_n=NEW.assigned_n
    AND NEW.classified_n=NEW.matured_expected_n
    AND NEW.matured_expected_n=NEW.expected_n
    AND NEW.expected_n=NEW.assigned_n
  );
  SELECT RAISE(ABORT,'observed mean does not reconcile to observed values')
  WHERE NEW.observed_n>0
    AND abs(NEW.observed_mean-(NEW.observed_sum/NEW.observed_n))>0.000000001;
  SELECT RAISE(ABORT,'raw ITT mean does not reconcile to assigned denominator')
  WHERE NEW.raw_itt_mean IS NOT NULL
    AND abs(NEW.raw_itt_mean-(NEW.observed_sum/NEW.assigned_n))>0.000000001;
END;

CREATE TRIGGER decision_snapshot_semantic_guard
BEFORE INSERT ON decision_snapshot
BEGIN
  SELECT RAISE(ABORT,'decision snapshot v1 cannot supersede another decision')
  WHERE NEW.decision_sequence=1
    AND NEW.supersedes_decision_snapshot_id IS NOT NULL;
  SELECT RAISE(ABORT,'decision snapshot after v1 requires exact same-contract predecessor')
  WHERE NEW.decision_sequence>1 AND NOT EXISTS (
    SELECT 1 FROM decision_snapshot p
    WHERE p.decision_snapshot_id=NEW.supersedes_decision_snapshot_id
      AND p.decision_contract_id=NEW.decision_contract_id
      AND p.decision_sequence=NEW.decision_sequence-1
  );
  SELECT RAISE(ABORT,'decision snapshot chronology cannot regress')
  WHERE NEW.decision_sequence>1 AND EXISTS (
    SELECT 1 FROM decision_snapshot p
    WHERE p.decision_snapshot_id=NEW.supersedes_decision_snapshot_id
      AND p.decision_contract_id=NEW.decision_contract_id
      AND p.decision_sequence=NEW.decision_sequence-1
      AND (datetime(NEW.decided_at)<datetime(p.decided_at)
        OR datetime(NEW.recorded_at)<=datetime(p.recorded_at))
  );
  SELECT RAISE(ABORT,'decision and analysis must belong to same contract')
  WHERE NOT EXISTS (
    SELECT 1 FROM analysis_snapshot a
    WHERE a.analysis_snapshot_id=NEW.analysis_snapshot_id
      AND a.decision_contract_id=NEW.decision_contract_id
  );
  SELECT RAISE(ABORT,'decision cannot precede referenced analysis')
  WHERE datetime(NEW.decided_at)<datetime((
    SELECT created_at FROM analysis_snapshot
    WHERE analysis_snapshot_id=NEW.analysis_snapshot_id
  ));
  SELECT RAISE(ABORT,'decision cannot precede a referenced analysis readout')
  WHERE EXISTS (
    SELECT 1 FROM analysis_arm_readout r
    WHERE r.analysis_snapshot_id=NEW.analysis_snapshot_id
      AND datetime(r.created_at)>datetime(NEW.decided_at)
  );
  SELECT RAISE(ABORT,'decision must use latest analysis available when decided')
  WHERE EXISTS (
    SELECT 1 FROM analysis_snapshot chosen
    JOIN analysis_snapshot later
      ON later.decision_contract_id=chosen.decision_contract_id
     AND later.snapshot_sequence>chosen.snapshot_sequence
     AND datetime(later.created_at)<=datetime(NEW.decided_at)
    WHERE chosen.analysis_snapshot_id=NEW.analysis_snapshot_id
  );
  SELECT RAISE(ABORT,'decision causal claim exceeds analysis claim cap')
  WHERE (SELECT claim_rank FROM claim_level_dim
         WHERE claim_level_code=NEW.claimed_interpretation_code)
      > (SELECT l.claim_rank FROM analysis_snapshot a
         JOIN claim_level_dim l
           ON l.claim_level_code=a.analysis_claim_cap_code
         WHERE a.analysis_snapshot_id=NEW.analysis_snapshot_id);
  SELECT RAISE(ABORT,'switchback causal claim requires resolved carryover')
  WHERE (SELECT c.design_type_code FROM decision_contract c
         WHERE c.decision_contract_id=NEW.decision_contract_id)
        ='RANDOMIZED_SWITCHBACK'
    AND NEW.claimed_interpretation_code='CAUSAL_ITT_WITHIN_CONTRACT'
    AND (SELECT carryover_state FROM analysis_snapshot
         WHERE analysis_snapshot_id=NEW.analysis_snapshot_id)<>'RESOLVED';
  SELECT RAISE(ABORT,'EXPAND requires complete maturity, closed integrity, and safety PASS')
  WHERE NEW.decision_state='EXPAND' AND EXISTS (
    SELECT 1 FROM analysis_snapshot a
    WHERE a.analysis_snapshot_id=NEW.analysis_snapshot_id
      AND (a.maturity_gate<>'COMPLETE'
        OR a.open_integrity_issue_n<>0
        OR a.blocking_expand_issue_n<>0
        OR a.safety_gate<>'PASS')
  );
  SELECT RAISE(ABORT,'EXPAND requires reanalysis after newer decision-relevant facts')
  WHERE NEW.decision_state='EXPAND' AND EXISTS (
    SELECT 1 FROM analysis_snapshot a
    JOIN v_decision_relevant_fact f
      ON f.decision_contract_id=a.decision_contract_id
    WHERE a.analysis_snapshot_id=NEW.analysis_snapshot_id
      AND datetime(f.fact_recorded_at)>datetime(a.evidence_cutoff_at)
      AND datetime(f.fact_recorded_at)<=datetime(NEW.decided_at)
  );
  SELECT RAISE(ABORT,'EXPAND cannot proceed with current blocking integrity issues')
  WHERE NEW.decision_state='EXPAND' AND EXISTS (
    SELECT 1 FROM analysis_snapshot a
    JOIN integrity_issue_stream s
      ON s.decision_contract_id=a.decision_contract_id
     AND s.blocks_expand=1
    WHERE a.analysis_snapshot_id=NEW.analysis_snapshot_id
      AND (NOT EXISTS (
        SELECT 1 FROM integrity_issue_event any_event
        WHERE any_event.integrity_issue_stream_id=s.integrity_issue_stream_id
          AND datetime(any_event.recorded_at)<=datetime(NEW.decided_at)
      ) OR EXISTS (
        SELECT 1 FROM integrity_issue_event e
        WHERE e.integrity_issue_stream_id=s.integrity_issue_stream_id
          AND datetime(e.recorded_at)<=datetime(NEW.decided_at)
          AND e.event_sequence=(
            SELECT max(e2.event_sequence) FROM integrity_issue_event e2
            WHERE e2.integrity_issue_stream_id=s.integrity_issue_stream_id
              AND datetime(e2.recorded_at)<=datetime(NEW.decided_at)
          )
          AND e.issue_state IN ('OPEN','UNKNOWN')
      ))
  );
  SELECT RAISE(ABORT,'EXPAND cannot proceed with current open contamination')
  WHERE NEW.decision_state='EXPAND' AND EXISTS (
    SELECT 1 FROM contamination_event c
    WHERE c.decision_contract_id=NEW.decision_contract_id
      AND datetime(c.recorded_at)<=datetime(NEW.decided_at)
      AND c.event_sequence=(
        SELECT max(c2.event_sequence) FROM contamination_event c2
        WHERE c2.contamination_stream_id=c.contamination_stream_id
          AND datetime(c2.recorded_at)<=datetime(NEW.decided_at)
      )
      AND c.contamination_state IN ('OPEN','UNKNOWN')
      AND c.blocks_itt_interpretation=1
  );
  SELECT RAISE(ABORT,'EXPAND requires abnormal exposures to have current integrity blockers')
  WHERE NEW.decision_state='EXPAND' AND EXISTS (
    SELECT 1 FROM exposure_event x
    WHERE x.decision_contract_id=NEW.decision_contract_id
      AND datetime(x.recorded_at)<=datetime(NEW.decided_at)
      AND x.exposure_kind IN ('CROSS_ARM_EXPOSURE','INTERFERENCE_EXPOSURE')
      AND NOT EXISTS (
        SELECT 1 FROM integrity_issue_stream s
        JOIN integrity_issue_event e USING (integrity_issue_stream_id)
        WHERE s.decision_contract_id=x.decision_contract_id
          AND s.issue_category_code=CASE x.exposure_kind
            WHEN 'CROSS_ARM_EXPOSURE' THEN 'CONTAMINATION'
            ELSE 'INTERFERENCE' END
          AND datetime(e.recorded_at)<=datetime(NEW.decided_at)
          AND e.event_sequence=(
            SELECT max(e2.event_sequence) FROM integrity_issue_event e2
            WHERE e2.integrity_issue_stream_id=s.integrity_issue_stream_id
              AND datetime(e2.recorded_at)<=datetime(NEW.decided_at)
          )
          AND e.issue_state IN ('OPEN','UNKNOWN')
      )
  );
  SELECT RAISE(ABORT,'EXPAND requires latest decision-time safety review PASS')
  WHERE NEW.decision_state='EXPAND' AND NOT EXISTS (
    SELECT 1 FROM safety_review s
    WHERE s.decision_contract_id=NEW.decision_contract_id
      AND datetime(s.recorded_at)<=datetime(NEW.decided_at)
      AND s.safety_state='PASS'
      AND s.safety_review_id=(
        SELECT s2.safety_review_id FROM safety_review s2
        WHERE s2.decision_contract_id=NEW.decision_contract_id
          AND datetime(s2.recorded_at)<=datetime(NEW.decided_at)
        ORDER BY datetime(s2.recorded_at) DESC,datetime(s2.reviewed_at) DESC,
                 s2.safety_review_id DESC
        LIMIT 1
      )
  );
  SELECT RAISE(ABORT,'EXPAND requires current cost facts to match analysis and contract caps')
  WHERE NEW.decision_state='EXPAND' AND EXISTS (
    SELECT 1 FROM analysis_snapshot a
    JOIN decision_contract c USING (decision_contract_id)
    WHERE a.analysis_snapshot_id=NEW.analysis_snapshot_id
      AND (a.cash_cost_micros<>coalesce((
             SELECT sum(x.cash_cost_micros) FROM experiment_cost_event x
             WHERE x.decision_contract_id=a.decision_contract_id
               AND x.cost_type_code='CASH'
               AND datetime(x.recorded_at)<=datetime(NEW.decided_at)
           ),0)
        OR a.founder_minutes<>coalesce((
             SELECT sum(x.founder_minutes) FROM experiment_cost_event x
             WHERE x.decision_contract_id=a.decision_contract_id
               AND x.cost_type_code='FOUNDER_TIME'
               AND datetime(x.recorded_at)<=datetime(NEW.decided_at)
           ),0)
        OR coalesce((
             SELECT sum(x.cash_cost_micros) FROM experiment_cost_event x
             WHERE x.decision_contract_id=a.decision_contract_id
               AND x.cost_type_code='CASH'
               AND datetime(x.recorded_at)<=datetime(NEW.decided_at)
           ),0)>c.max_cash_cost_micros
        OR coalesce((
             SELECT sum(x.founder_minutes) FROM experiment_cost_event x
             WHERE x.decision_contract_id=a.decision_contract_id
               AND x.cost_type_code='FOUNDER_TIME'
               AND datetime(x.recorded_at)<=datetime(NEW.decided_at)
           ),0)>c.max_founder_minutes)
  );
  SELECT RAISE(ABORT,'EXPAND requires readouts that still match snapshot source facts')
  WHERE NEW.decision_state='EXPAND' AND EXISTS (
    SELECT 1 FROM analysis_arm_readout r
    LEFT JOIN v_snapshot_itt_arm_readout x
      ON x.analysis_snapshot_id=r.analysis_snapshot_id
     AND x.arm_id=r.arm_id
     AND x.metric_contract_id=r.metric_contract_id
    WHERE r.analysis_snapshot_id=NEW.analysis_snapshot_id
      AND (x.arm_id IS NULL OR r.assigned_n<>x.assigned_n
        OR r.expected_n<>x.expected_n
        OR r.matured_expected_n<>x.matured_expected_n
        OR r.classified_n<>x.classified_n
        OR r.observed_n<>x.observed_n OR r.missing_n<>x.missing_n
        OR r.observed_sum IS NOT x.observed_sum
        OR r.observed_mean IS NOT x.observed_mean
        OR r.raw_itt_mean IS NOT x.raw_itt_mean
        OR r.readout_state<>x.readout_state)
  );
  SELECT RAISE(ABORT,'EXPAND requires complete raw ITT readouts without missing outcomes')
  WHERE NEW.decision_state='EXPAND' AND EXISTS (
    SELECT 1 FROM analysis_arm_readout r
    JOIN metric_contract_version m USING (metric_contract_id)
    WHERE r.analysis_snapshot_id=NEW.analysis_snapshot_id
      AND m.metric_role IN ('PRIMARY','GUARDRAIL')
      AND (r.readout_state<>'COMPLETE' OR r.raw_itt_mean IS NULL)
  );
  SELECT RAISE(ABORT,'EXPAND requires complete primary and guardrail readouts for every arm')
  WHERE NEW.decision_state='EXPAND' AND EXISTS (
    SELECT 1 FROM analysis_snapshot s
    JOIN arm_contract a
      ON a.decision_contract_id=s.decision_contract_id
     AND datetime(a.arm_registered_at)<=datetime((
       SELECT max(assigned_at) FROM experiment_assignment
       WHERE decision_contract_id=s.decision_contract_id
     ))
    JOIN metric_contract_version m
      ON m.decision_contract_id=s.decision_contract_id
     AND datetime(m.registered_at)<=datetime((
       SELECT max(assigned_at) FROM experiment_assignment
       WHERE decision_contract_id=s.decision_contract_id
     ))
    WHERE s.analysis_snapshot_id=NEW.analysis_snapshot_id
      AND m.metric_role IN ('PRIMARY','GUARDRAIL')
      AND NOT EXISTS (
        SELECT 1 FROM metric_contract_version later
        WHERE later.metric_stream_id=m.metric_stream_id
          AND later.metric_version>m.metric_version
          AND datetime(later.registered_at)<=datetime((
            SELECT max(assigned_at) FROM experiment_assignment
            WHERE decision_contract_id=s.decision_contract_id
          ))
      )
      AND NOT EXISTS (
        SELECT 1 FROM analysis_arm_readout r
        WHERE r.analysis_snapshot_id=s.analysis_snapshot_id
          AND r.arm_id=a.arm_id
          AND r.metric_contract_id=m.metric_contract_id
          AND r.readout_state='COMPLETE'
          AND r.raw_itt_mean IS NOT NULL
      )
  );
END;

-- Every table above is append-only. A correction is a new evidence/event,
-- analysis snapshot, or decision snapshot; never UPDATE or DELETE.

CREATE TRIGGER no_update_schema_registry BEFORE UPDATE ON schema_registry
BEGIN SELECT RAISE(ABORT,'append-only: schema_registry cannot be updated'); END;
CREATE TRIGGER no_delete_schema_registry BEFORE DELETE ON schema_registry
BEGIN SELECT RAISE(ABORT,'append-only: schema_registry cannot be deleted'); END;
CREATE TRIGGER no_update_model_setting BEFORE UPDATE ON model_setting
BEGIN SELECT RAISE(ABORT,'append-only: model_setting cannot be updated'); END;
CREATE TRIGGER no_delete_model_setting BEFORE DELETE ON model_setting
BEGIN SELECT RAISE(ABORT,'append-only: model_setting cannot be deleted'); END;
CREATE TRIGGER no_update_claim_level_dim BEFORE UPDATE ON claim_level_dim
BEGIN SELECT RAISE(ABORT,'append-only: claim_level_dim cannot be updated'); END;
CREATE TRIGGER no_delete_claim_level_dim BEFORE DELETE ON claim_level_dim
BEGIN SELECT RAISE(ABORT,'append-only: claim_level_dim cannot be deleted'); END;
CREATE TRIGGER no_update_design_type_dim BEFORE UPDATE ON design_type_dim
BEGIN SELECT RAISE(ABORT,'append-only: design_type_dim cannot be updated'); END;
CREATE TRIGGER no_delete_design_type_dim BEFORE DELETE ON design_type_dim
BEGIN SELECT RAISE(ABORT,'append-only: design_type_dim cannot be deleted'); END;
CREATE TRIGGER no_update_issue_category_dim BEFORE UPDATE ON issue_category_dim
BEGIN SELECT RAISE(ABORT,'append-only: issue_category_dim cannot be updated'); END;
CREATE TRIGGER no_delete_issue_category_dim BEFORE DELETE ON issue_category_dim
BEGIN SELECT RAISE(ABORT,'append-only: issue_category_dim cannot be deleted'); END;
CREATE TRIGGER no_update_evidence_record BEFORE UPDATE ON evidence_record
BEGIN SELECT RAISE(ABORT,'append-only: evidence_record cannot be updated'); END;
CREATE TRIGGER no_delete_evidence_record BEFORE DELETE ON evidence_record
BEGIN SELECT RAISE(ABORT,'append-only: evidence_record cannot be deleted'); END;
CREATE TRIGGER no_update_decision_contract BEFORE UPDATE ON decision_contract
BEGIN SELECT RAISE(ABORT,'append-only: decision_contract cannot be updated'); END;
CREATE TRIGGER no_delete_decision_contract BEFORE DELETE ON decision_contract
BEGIN SELECT RAISE(ABORT,'append-only: decision_contract cannot be deleted'); END;
CREATE TRIGGER no_update_arm_contract BEFORE UPDATE ON arm_contract
BEGIN SELECT RAISE(ABORT,'append-only: arm_contract cannot be updated'); END;
CREATE TRIGGER no_delete_arm_contract BEFORE DELETE ON arm_contract
BEGIN SELECT RAISE(ABORT,'append-only: arm_contract cannot be deleted'); END;
CREATE TRIGGER no_update_metric_contract_version BEFORE UPDATE ON metric_contract_version
BEGIN SELECT RAISE(ABORT,'append-only: metric_contract_version cannot be updated'); END;
CREATE TRIGGER no_delete_metric_contract_version BEFORE DELETE ON metric_contract_version
BEGIN SELECT RAISE(ABORT,'append-only: metric_contract_version cannot be deleted'); END;
CREATE TRIGGER no_update_switchback_period_contract BEFORE UPDATE ON switchback_period_contract
BEGIN SELECT RAISE(ABORT,'append-only: switchback_period_contract cannot be updated'); END;
CREATE TRIGGER no_delete_switchback_period_contract BEFORE DELETE ON switchback_period_contract
BEGIN SELECT RAISE(ABORT,'append-only: switchback_period_contract cannot be deleted'); END;
CREATE TRIGGER no_update_experiment_unit BEFORE UPDATE ON experiment_unit
BEGIN SELECT RAISE(ABORT,'append-only: experiment_unit cannot be updated'); END;
CREATE TRIGGER no_delete_experiment_unit BEFORE DELETE ON experiment_unit
BEGIN SELECT RAISE(ABORT,'append-only: experiment_unit cannot be deleted'); END;
CREATE TRIGGER no_update_eligibility_review BEFORE UPDATE ON eligibility_review
BEGIN SELECT RAISE(ABORT,'append-only: eligibility_review cannot be updated'); END;
CREATE TRIGGER no_delete_eligibility_review BEFORE DELETE ON eligibility_review
BEGIN SELECT RAISE(ABORT,'append-only: eligibility_review cannot be deleted'); END;
CREATE TRIGGER no_update_pre_assignment_exclusion BEFORE UPDATE ON pre_assignment_exclusion
BEGIN SELECT RAISE(ABORT,'append-only: pre_assignment_exclusion cannot be updated'); END;
CREATE TRIGGER no_delete_pre_assignment_exclusion BEFORE DELETE ON pre_assignment_exclusion
BEGIN SELECT RAISE(ABORT,'append-only: pre_assignment_exclusion cannot be deleted'); END;
CREATE TRIGGER no_update_experiment_assignment BEFORE UPDATE ON experiment_assignment
BEGIN SELECT RAISE(ABORT,'append-only: experiment_assignment cannot be updated'); END;
CREATE TRIGGER no_delete_experiment_assignment BEFORE DELETE ON experiment_assignment
BEGIN SELECT RAISE(ABORT,'append-only: experiment_assignment cannot be deleted'); END;
CREATE TRIGGER no_update_exposure_event BEFORE UPDATE ON exposure_event
BEGIN SELECT RAISE(ABORT,'append-only: exposure_event cannot be updated'); END;
CREATE TRIGGER no_delete_exposure_event BEFORE DELETE ON exposure_event
BEGIN SELECT RAISE(ABORT,'append-only: exposure_event cannot be deleted'); END;
CREATE TRIGGER no_update_contamination_event BEFORE UPDATE ON contamination_event
BEGIN SELECT RAISE(ABORT,'append-only: contamination_event cannot be updated'); END;
CREATE TRIGGER no_delete_contamination_event BEFORE DELETE ON contamination_event
BEGIN SELECT RAISE(ABORT,'append-only: contamination_event cannot be deleted'); END;
CREATE TRIGGER no_update_expected_outcome_spine BEFORE UPDATE ON expected_outcome_spine
BEGIN SELECT RAISE(ABORT,'append-only: expected_outcome_spine cannot be updated'); END;
CREATE TRIGGER no_delete_expected_outcome_spine BEFORE DELETE ON expected_outcome_spine
BEGIN SELECT RAISE(ABORT,'append-only: expected_outcome_spine cannot be deleted'); END;
CREATE TRIGGER no_update_outcome_observation BEFORE UPDATE ON outcome_observation
BEGIN SELECT RAISE(ABORT,'append-only: outcome_observation cannot be updated'); END;
CREATE TRIGGER no_delete_outcome_observation BEFORE DELETE ON outcome_observation
BEGIN SELECT RAISE(ABORT,'append-only: outcome_observation cannot be deleted'); END;
CREATE TRIGGER no_update_integrity_issue_stream BEFORE UPDATE ON integrity_issue_stream
BEGIN SELECT RAISE(ABORT,'append-only: integrity_issue_stream cannot be updated'); END;
CREATE TRIGGER no_delete_integrity_issue_stream BEFORE DELETE ON integrity_issue_stream
BEGIN SELECT RAISE(ABORT,'append-only: integrity_issue_stream cannot be deleted'); END;
CREATE TRIGGER no_update_integrity_issue_event BEFORE UPDATE ON integrity_issue_event
BEGIN SELECT RAISE(ABORT,'append-only: integrity_issue_event cannot be updated'); END;
CREATE TRIGGER no_delete_integrity_issue_event BEFORE DELETE ON integrity_issue_event
BEGIN SELECT RAISE(ABORT,'append-only: integrity_issue_event cannot be deleted'); END;
CREATE TRIGGER no_update_safety_review BEFORE UPDATE ON safety_review
BEGIN SELECT RAISE(ABORT,'append-only: safety_review cannot be updated'); END;
CREATE TRIGGER no_delete_safety_review BEFORE DELETE ON safety_review
BEGIN SELECT RAISE(ABORT,'append-only: safety_review cannot be deleted'); END;
CREATE TRIGGER no_update_experiment_cost_event BEFORE UPDATE ON experiment_cost_event
BEGIN SELECT RAISE(ABORT,'append-only: experiment_cost_event cannot be updated'); END;
CREATE TRIGGER no_delete_experiment_cost_event BEFORE DELETE ON experiment_cost_event
BEGIN SELECT RAISE(ABORT,'append-only: experiment_cost_event cannot be deleted'); END;
CREATE TRIGGER no_update_analysis_snapshot BEFORE UPDATE ON analysis_snapshot
BEGIN SELECT RAISE(ABORT,'append-only: analysis_snapshot cannot be updated'); END;
CREATE TRIGGER no_delete_analysis_snapshot BEFORE DELETE ON analysis_snapshot
BEGIN SELECT RAISE(ABORT,'append-only: analysis_snapshot cannot be deleted'); END;
CREATE TRIGGER no_update_analysis_arm_readout BEFORE UPDATE ON analysis_arm_readout
BEGIN SELECT RAISE(ABORT,'append-only: analysis_arm_readout cannot be updated'); END;
CREATE TRIGGER no_delete_analysis_arm_readout BEFORE DELETE ON analysis_arm_readout
BEGIN SELECT RAISE(ABORT,'append-only: analysis_arm_readout cannot be deleted'); END;
CREATE TRIGGER no_update_decision_snapshot BEFORE UPDATE ON decision_snapshot
BEGIN SELECT RAISE(ABORT,'append-only: decision_snapshot cannot be updated'); END;
CREATE TRIGGER no_delete_decision_snapshot BEFORE DELETE ON decision_snapshot
BEGIN SELECT RAISE(ABORT,'append-only: decision_snapshot cannot be deleted'); END;

-- -----------------------------------------------------------------------------
-- Derived current state and raw, non-inferential ITT readouts
-- -----------------------------------------------------------------------------

CREATE VIEW v_current_integrity_issue AS
SELECT s.integrity_issue_stream_id,s.decision_contract_id,
       s.issue_category_code,s.issue_scope_code,s.blocks_causal_claim,
       s.blocks_expand,e.integrity_issue_event_id,e.event_sequence,
       e.issue_state,e.state_reason_code,e.occurred_at,e.recorded_at
FROM integrity_issue_stream s
JOIN integrity_issue_event e USING (integrity_issue_stream_id)
WHERE e.event_sequence=(
  SELECT max(e2.event_sequence)
  FROM integrity_issue_event e2
  WHERE e2.integrity_issue_stream_id=s.integrity_issue_stream_id
);

CREATE VIEW v_current_contamination AS
SELECT e.*
FROM contamination_event e
WHERE e.event_sequence=(
  SELECT max(e2.event_sequence)
  FROM contamination_event e2
  WHERE e2.contamination_stream_id=e.contamination_stream_id
);

CREATE VIEW v_raw_itt_arm_readout AS
WITH arm_n AS (
  SELECT decision_contract_id,arm_id,count(*) AS assigned_n
  FROM experiment_assignment
  GROUP BY decision_contract_id,arm_id
), spine_roll AS (
  SELECT s.decision_contract_id,a.arm_id,s.metric_contract_id,
         count(*) AS expected_n,
         sum(CASE WHEN datetime(s.expected_maturity_at)<=datetime(ms.default_snapshot_as_of)
                  THEN 1 ELSE 0 END) AS matured_expected_n,
         count(o.outcome_observation_id) AS classified_n,
         sum(CASE WHEN o.outcome_state='OBSERVED_VALUE' THEN 1 ELSE 0 END)
           AS observed_n,
         sum(CASE WHEN o.outcome_state='MISSING_CONFIRMED' THEN 1 ELSE 0 END)
           AS missing_n,
         sum(CASE WHEN o.outcome_state='OBSERVED_VALUE'
                  THEN o.numeric_value END) AS observed_sum
  FROM expected_outcome_spine s
  JOIN experiment_assignment a USING (assignment_id)
  CROSS JOIN model_setting ms
  LEFT JOIN outcome_observation o USING (expected_outcome_id)
  WHERE ms.singleton_id=1
    AND datetime(s.registered_at)<=datetime(ms.default_snapshot_as_of)
    AND (o.outcome_observation_id IS NULL
      OR datetime(o.recorded_at)<=datetime(ms.default_snapshot_as_of))
  GROUP BY s.decision_contract_id,a.arm_id,s.metric_contract_id
)
SELECT r.decision_contract_id,r.arm_id,a.arm_code,a.arm_role,
       a.comparison_pair_code,r.metric_contract_id,m.metric_code,m.metric_role,
       n.assigned_n,r.expected_n,r.matured_expected_n,r.classified_n,
       r.observed_n,r.missing_n,r.observed_sum,
       CASE WHEN r.observed_n>0
            THEN r.observed_sum*1.0/r.observed_n END AS observed_mean,
       CASE WHEN r.missing_n=0 AND r.observed_n=n.assigned_n
                  AND r.classified_n=r.matured_expected_n
                  AND r.matured_expected_n=r.expected_n
                  AND r.expected_n=n.assigned_n
            THEN r.observed_sum*1.0/n.assigned_n END AS raw_itt_mean,
       CASE WHEN r.matured_expected_n<r.expected_n THEN 'IMMATURE'
            WHEN r.classified_n<r.matured_expected_n THEN 'MISSING'
            WHEN r.missing_n>0 THEN 'MISSING'
            WHEN r.observed_n=n.assigned_n AND r.expected_n=n.assigned_n
              THEN 'COMPLETE'
            ELSE 'NO_EXPECTATION' END AS readout_state
FROM spine_roll r
JOIN arm_n n USING (decision_contract_id,arm_id)
JOIN arm_contract a USING (arm_id)
JOIN metric_contract_version m USING (metric_contract_id);

CREATE VIEW v_raw_itt_pair_readout AS
SELECT ref.decision_contract_id,c.design_type_code,ref.comparison_pair_code,
       ref.metric_contract_id,ref.metric_code,
       ref.arm_id AS reference_arm_id,ref.arm_code AS reference_arm_code,
       cmp.arm_id AS comparator_arm_id,cmp.arm_code AS comparator_arm_code,
       ref.assigned_n AS reference_assigned_n,
       cmp.assigned_n AS comparator_assigned_n,
       ref.observed_n AS reference_observed_n,
       cmp.observed_n AS comparator_observed_n,
       ref.missing_n AS reference_missing_n,
       cmp.missing_n AS comparator_missing_n,
       ref.observed_mean AS reference_observed_mean,
       cmp.observed_mean AS comparator_observed_mean,
       CASE WHEN ref.observed_mean IS NOT NULL AND cmp.observed_mean IS NOT NULL
            THEN cmp.observed_mean-ref.observed_mean END
         AS observed_mean_difference,
       ref.raw_itt_mean AS reference_raw_itt_mean,
       cmp.raw_itt_mean AS comparator_raw_itt_mean,
       CASE WHEN ref.raw_itt_mean IS NOT NULL AND cmp.raw_itt_mean IS NOT NULL
            THEN cmp.raw_itt_mean-ref.raw_itt_mean END
         AS raw_itt_mean_difference,
       CASE c.design_type_code
         WHEN 'RANDOMIZED_PARALLEL' THEN 'GATED_CAUSAL_ITT_CANDIDATE'
         WHEN 'RANDOMIZED_AA' THEN 'DESCRIPTIVE_AA_DIAGNOSTIC'
         WHEN 'RANDOMIZED_SWITCHBACK' THEN 'DESCRIPTIVE_UNTIL_CARRYOVER_RESOLVED'
         ELSE 'ASSOCIATION_ONLY'
       END AS interpretation_boundary
FROM v_raw_itt_arm_readout ref
JOIN v_raw_itt_arm_readout cmp
  ON cmp.decision_contract_id=ref.decision_contract_id
 AND cmp.comparison_pair_code=ref.comparison_pair_code
 AND cmp.metric_contract_id=ref.metric_contract_id
JOIN decision_contract c USING (decision_contract_id)
WHERE ref.arm_role='REFERENCE' AND cmp.arm_role='COMPARATOR'
  AND ref.metric_role='PRIMARY';

CREATE VIEW v_experiment_cost_readout AS
SELECT c.decision_contract_id,
       coalesce(sum(CASE WHEN c.cost_type_code='CASH'
                         THEN c.cash_cost_micros ELSE 0 END),0)
         AS cash_cost_micros,
       coalesce(sum(CASE WHEN c.cost_type_code='FOUNDER_TIME'
                         THEN c.founder_minutes ELSE 0 END),0)
         AS founder_minutes,
       coalesce(sum(CASE WHEN c.cost_type_code='FOUNDER_TIME'
                         THEN c.founder_minutes ELSE 0 END),0)
         *(SELECT founder_rate_micros_per_minute
           FROM model_setting WHERE singleton_id=1)
         AS founder_time_cost_micros,
       coalesce(sum(CASE WHEN c.cost_type_code='CASH'
                         THEN c.cash_cost_micros ELSE 0 END),0)
       +coalesce(sum(CASE WHEN c.cost_type_code='FOUNDER_TIME'
                         THEN c.founder_minutes ELSE 0 END),0)
         *(SELECT founder_rate_micros_per_minute
           FROM model_setting WHERE singleton_id=1)
         AS fully_loaded_experiment_cost_micros
FROM experiment_cost_event c
GROUP BY c.decision_contract_id;

CREATE VIEW v_analysis_pair_readout AS
SELECT s.analysis_snapshot_id,ref.metric_contract_id,m.metric_code,
       ref.arm_id AS reference_arm_id,cmp.arm_id AS comparator_arm_id,
       ref.assigned_n AS reference_assigned_n,
       cmp.assigned_n AS comparator_assigned_n,
       ref.observed_n AS reference_observed_n,
       cmp.observed_n AS comparator_observed_n,
       ref.missing_n AS reference_missing_n,
       cmp.missing_n AS comparator_missing_n,
       ref.observed_mean AS reference_observed_mean,
       cmp.observed_mean AS comparator_observed_mean,
       CASE WHEN ref.observed_mean IS NOT NULL AND cmp.observed_mean IS NOT NULL
            THEN cmp.observed_mean-ref.observed_mean END
         AS observed_mean_difference,
       ref.raw_itt_mean AS reference_raw_itt_mean,
       cmp.raw_itt_mean AS comparator_raw_itt_mean,
       CASE WHEN ref.raw_itt_mean IS NOT NULL AND cmp.raw_itt_mean IS NOT NULL
            THEN cmp.raw_itt_mean-ref.raw_itt_mean END
         AS raw_itt_mean_difference
FROM analysis_snapshot s
JOIN analysis_arm_readout ref USING (analysis_snapshot_id)
JOIN arm_contract ar ON ar.arm_id=ref.arm_id AND ar.arm_role='REFERENCE'
JOIN analysis_arm_readout cmp
  ON cmp.analysis_snapshot_id=ref.analysis_snapshot_id
 AND cmp.metric_contract_id=ref.metric_contract_id
JOIN arm_contract ac ON ac.arm_id=cmp.arm_id AND ac.arm_role='COMPARATOR'
 AND ac.comparison_pair_code=ar.comparison_pair_code
JOIN metric_contract_version m ON m.metric_contract_id=ref.metric_contract_id
WHERE m.metric_role='PRIMARY';

-- -----------------------------------------------------------------------------
-- Fully synthetic fixtures
-- -----------------------------------------------------------------------------

INSERT INTO schema_registry VALUES (
  1,'small_data_experiment_decision','1.0.0','SQLite 3.37+',
  '2026-08-02T00:00:00Z','SYNTHETIC_ONLY_NO_RAW_PII_OPAQUE_REFERENCES'
);

INSERT INTO model_setting VALUES (
  1,'2026-08-02T12:00:00Z','2026-08-01T23:59:59Z','JPY',
  100000000,50
);

INSERT INTO claim_level_dim VALUES
  ('DESCRIPTIVE_ONLY',0,'Counts and observed values without effect attribution'),
  ('ASSOCIATION_ONLY',1,'Observed arm association without causal attribution'),
  ('CAUSAL_ITT_WITHIN_CONTRACT',2,
   'Bounded assignment effect for the registered eligible population and window');

INSERT INTO design_type_dim VALUES
  ('RANDOMIZED_PARALLEL','CAUSAL_ITT_WITHIN_CONTRACT',1,0,0),
  ('RANDOMIZED_AA','DESCRIPTIVE_ONLY',1,0,0),
  ('RANDOMIZED_SWITCHBACK','CAUSAL_ITT_WITHIN_CONTRACT',1,1,1),
  ('OBSERVATIONAL_ASSOCIATION','ASSOCIATION_ONLY',0,0,0);

INSERT INTO issue_category_dim VALUES
  ('SRM',1,1),('COVERAGE',1,1),('DUPLICATE',1,1),('TIME',1,1),
  ('CONTAMINATION',1,1),('CARRYOVER',1,1),('INTERFERENCE',1,1);

WITH e(evidence_id,evidence_class,evidence_domain,evidence_status,n,
       occurred_at,recorded_at,reviewer_role_code) AS (
  VALUES
  ('EV_CONTRACT','E2_SYSTEM_CONFIG','CONTRACT','CONFIRMED',1,
   '2026-05-01T00:00:00Z','2026-05-01T01:00:00Z','FOUNDER'),
  ('EV_ELIGIBILITY','E1_MANUAL_REVIEW','ELIGIBILITY','CONFIRMED',2,
   '2026-05-30T00:00:00Z','2026-05-30T01:00:00Z','EXPERIMENT_OWNER'),
  ('EV_ASSIGNMENT','E3_SYSTEM_EVENT','ASSIGNMENT','CONFIRMED',3,
   '2026-06-01T00:00:00Z','2026-06-01T00:05:00Z','SYSTEM'),
  ('EV_EXPOSURE','E3_SYSTEM_EVENT','EXPOSURE','CONFIRMED',4,
   '2026-06-02T00:00:00Z','2026-06-02T00:05:00Z','SYSTEM'),
  ('EV_OUTCOME','E4_FIRST_PARTY_AUTHORITATIVE','OUTCOME','CONFIRMED',5,
   '2026-06-10T00:00:00Z','2026-06-10T01:00:00Z','MEASUREMENT_OWNER'),
  ('EV_INTEGRITY','E1_MANUAL_REVIEW','INTEGRITY','CONFIRMED',6,
   '2026-06-11T00:00:00Z','2026-06-11T01:00:00Z','REVIEWER'),
  ('EV_SAFETY','E1_MANUAL_REVIEW','SAFETY','CONFIRMED',7,
   '2026-06-12T00:00:00Z','2026-06-12T01:00:00Z','SAFETY_OWNER'),
  ('EV_COST','E4_FIRST_PARTY_AUTHORITATIVE','COST','CONFIRMED',8,
   '2026-06-12T00:00:00Z','2026-06-12T01:00:00Z','FOUNDER'),
  ('EV_ANALYSIS','E1_MANUAL_REVIEW','ANALYSIS','CONFIRMED',9,
   '2026-08-01T00:00:00Z','2026-08-01T01:00:00Z','ANALYST'),
  ('EV_DECISION','E1_MANUAL_REVIEW','DECISION','CONFIRMED',10,
   '2026-08-01T02:00:00Z','2026-08-01T03:00:00Z','FOUNDER')
)
INSERT INTO evidence_record
SELECT evidence_id,evidence_class,evidence_domain,evidence_status,
       printf('%064x',n),occurred_at,recorded_at,reviewer_role_code
FROM e;

INSERT INTO decision_contract VALUES
  ('DC_RP_V1','DCS_RP',1,NULL,'SYN_RANDOM_PARALLEL',
   'RANDOMIZED_PARALLEL','CAUSAL_ITT_WITHIN_CONTRACT',
   'ACCEPTED_OUTCOME_NEXT_BATCH','SYN_ACCOUNT','SYN_ACCOUNT',
   'HASH_V1','ELIG_V1','ALLOC_50_50_V1','GATED_DIRECTIONAL_V1',8,
   '2026-06-01T00:00:00Z','2026-06-01T23:59:59Z',
   '2026-06-10T00:00:00Z',100000000000,300,'NA',
   '2026-05-01T02:00:00Z','2026-05-01T02:05:00Z','FOUNDER','EV_CONTRACT'),
  ('DC_AA_V1','DCS_AA',1,NULL,'SYN_RANDOM_AA',
   'RANDOMIZED_AA','DESCRIPTIVE_ONLY',
   'INSTRUMENTATION_SYMMETRY','SYN_ACCOUNT','SYN_ACCOUNT',
   'HASH_V1','ELIG_V1','ALLOC_50_50_V1','AA_DIAGNOSTIC_V1',4,
   '2026-06-15T00:00:00Z','2026-06-15T23:59:59Z',
   '2026-06-22T00:00:00Z',50000000000,120,'NA',
   '2026-05-01T02:10:00Z','2026-05-01T02:15:00Z','FOUNDER','EV_CONTRACT'),
  ('DC_SW_V1','DCS_SW',1,NULL,'SYN_SWITCHBACK',
   'RANDOMIZED_SWITCHBACK','CAUSAL_ITT_WITHIN_CONTRACT',
   'TASK_TIME_BY_SEQUENCE','SYN_WORKSPACE','SYN_WORKSPACE',
   'BLOCK_RANDOM_V1','ELIG_V1','SEQUENCE_50_50_V1','CARRYOVER_GATED_V1',4,
   '2026-07-01T00:00:00Z','2026-07-01T23:59:59Z',
   '2026-07-10T00:00:00Z',50000000000,150,'WASHOUT_AND_ASSESS',
   '2026-05-01T02:20:00Z','2026-05-01T02:25:00Z','FOUNDER','EV_CONTRACT'),
  ('DC_OBS_V1','DCS_OBS',1,NULL,'SYN_OBSERVATIONAL',
   'OBSERVATIONAL_ASSOCIATION','ASSOCIATION_ONLY',
   'NATURAL_EXPOSURE_ASSOCIATION','SYN_ACCOUNT','SYN_ACCOUNT',
   'OBSERVED_V1','ELIG_V1','MATCHED_DESCRIPTION_V1','ASSOCIATION_V1',6,
   '2026-07-15T00:00:00Z','2026-07-15T23:59:59Z',
   '2026-07-24T00:00:00Z',30000000000,90,'NA',
   '2026-05-01T02:30:00Z','2026-05-01T02:35:00Z','FOUNDER','EV_CONTRACT');

INSERT INTO arm_contract VALUES
  ('ARM_RP_C','DC_RP_V1','CONTROL','REFERENCE','PAIR_RP',500000,
   'BASELINE_V1','2026-05-02T00:00:00Z','EV_CONTRACT'),
  ('ARM_RP_T','DC_RP_V1','TREATMENT','COMPARATOR','PAIR_RP',500000,
   'ASSISTED_V1','2026-05-02T00:00:00Z','EV_CONTRACT'),
  ('ARM_AA_L','DC_AA_V1','AA_LEFT','REFERENCE','PAIR_AA',500000,
   'BASELINE_IDENTICAL_V1','2026-05-02T00:00:00Z','EV_CONTRACT'),
  ('ARM_AA_R','DC_AA_V1','AA_RIGHT','COMPARATOR','PAIR_AA',500000,
   'BASELINE_IDENTICAL_V1','2026-05-02T00:00:00Z','EV_CONTRACT'),
  ('ARM_SW_AB','DC_SW_V1','SEQUENCE_AB','REFERENCE','PAIR_SW',500000,
   'SEQUENCE_AB_V1','2026-05-02T00:00:00Z','EV_CONTRACT'),
  ('ARM_SW_BA','DC_SW_V1','SEQUENCE_BA','COMPARATOR','PAIR_SW',500000,
   'SEQUENCE_BA_V1','2026-05-02T00:00:00Z','EV_CONTRACT'),
  ('ARM_OBS_U','DC_OBS_V1','UNEXPOSED','REFERENCE','PAIR_OBS',500000,
   'NATURAL_UNEXPOSED_V1','2026-05-02T00:00:00Z','EV_CONTRACT'),
  ('ARM_OBS_E','DC_OBS_V1','EXPOSED','COMPARATOR','PAIR_OBS',500000,
   'NATURAL_EXPOSED_V1','2026-05-02T00:00:00Z','EV_CONTRACT');

INSERT INTO metric_contract_version VALUES
  ('MET_RP_PRI_V1','MS_RP_PRI',1,NULL,'DC_RP_V1','ACCEPTED_OUTCOME',
   'PRIMARY','BINARY','OUTCOME_PER_ASSIGNED_UNIT','HIGHER',
   'ACCEPTED_OUTCOME_COUNT_V1','ALL_ASSIGNED_ELIGIBLE_UNITS_V1',
   'RAW_ITT_MEAN','EXPLICIT_MISSING_NEVER_ZERO',1,7,9,0.10,'GE',0,
   '2026-05-03T00:00:00Z','EV_CONTRACT'),
  ('MET_RP_PRI_V2','MS_RP_PRI',2,'MET_RP_PRI_V1','DC_RP_V1',
   'ACCEPTED_OUTCOME','PRIMARY','BINARY','OUTCOME_PER_ASSIGNED_UNIT','HIGHER',
   'ACCEPTED_OUTCOME_COUNT_V2','ALL_PREASSIGNMENT_ELIGIBLE_ASSIGNED_UNITS_V2',
   'RAW_ITT_MEAN','EXPLICIT_MISSING_NEVER_ZERO',1,7,9,0.10,'GE',0,
   '2026-05-20T00:00:00Z','EV_CONTRACT'),
  ('MET_RP_SAFE_V1','MS_RP_SAFE',1,NULL,'DC_RP_V1','CRITICAL_SAFETY_EVENT',
   'GUARDRAIL','BINARY','EVENT_PER_ASSIGNED_UNIT','LOWER',
   'CRITICAL_EVENT_COUNT_V1','ALL_ASSIGNED_ELIGIBLE_UNITS_V1',
   'RAW_ITT_MEAN','EXPLICIT_MISSING_NEVER_ZERO',1,7,9,0.0,'LE',1,
   '2026-05-03T00:00:00Z','EV_CONTRACT'),
  ('MET_RP_DIAG_V1','MS_RP_DIAG',1,NULL,'DC_RP_V1','FOUNDER_MINUTES',
   'DIAGNOSTIC','CONTINUOUS','MINUTES_PER_ASSIGNED_UNIT','LOWER',
   'FOUNDER_MINUTES_SUM_V1','ALL_ASSIGNED_ELIGIBLE_UNITS_V1',
   'RAW_ITT_MEAN','EXPLICIT_MISSING_NEVER_ZERO',1,7,9,NULL,'NONE',0,
   '2026-05-03T00:00:00Z','EV_CONTRACT'),
  ('MET_AA_PRI_V1','MS_AA_PRI',1,NULL,'DC_AA_V1','EVENT_CAPTURED',
   'PRIMARY','BINARY','EVENT_PER_ASSIGNED_UNIT','NONE',
   'CAPTURED_EVENT_COUNT_V1','ALL_ASSIGNED_ELIGIBLE_UNITS_V1',
   'RAW_ITT_MEAN','EXPLICIT_MISSING_NEVER_ZERO',1,5,7,NULL,'NONE',0,
   '2026-05-03T00:00:00Z','EV_CONTRACT'),
  ('MET_SW_PRI_V1','MS_SW_PRI',1,NULL,'DC_SW_V1','TASK_SECONDS',
   'PRIMARY','CONTINUOUS','SECONDS_PER_ASSIGNED_UNIT','LOWER',
   'TASK_SECONDS_SUM_V1','ALL_ASSIGNED_SEQUENCES_V1',
   'RAW_ITT_MEAN','EXPLICIT_MISSING_NEVER_ZERO',1,7,9,-5.0,'LE',0,
   '2026-05-03T00:00:00Z','EV_CONTRACT'),
  ('MET_SW_SAFE_V1','MS_SW_SAFE',1,NULL,'DC_SW_V1','CRITICAL_SAFETY_EVENT',
   'GUARDRAIL','BINARY','EVENT_PER_ASSIGNED_UNIT','LOWER',
   'CRITICAL_EVENT_COUNT_V1','ALL_ASSIGNED_SEQUENCES_V1',
   'RAW_ITT_MEAN','EXPLICIT_MISSING_NEVER_ZERO',1,7,9,0.0,'LE',1,
   '2026-05-03T00:00:00Z','EV_CONTRACT'),
  ('MET_OBS_PRI_V1','MS_OBS_PRI',1,NULL,'DC_OBS_V1','ACCEPTED_OUTCOME',
   'PRIMARY','BINARY','OUTCOME_PER_ASSIGNED_UNIT','HIGHER',
   'ACCEPTED_OUTCOME_COUNT_V1','ALL_CLASSIFIED_COHORT_UNITS_V1',
   'RAW_ITT_MEAN','EXPLICIT_MISSING_NEVER_ZERO',1,7,9,NULL,'NONE',0,
   '2026-05-03T00:00:00Z','EV_CONTRACT');

INSERT INTO switchback_period_contract VALUES
  ('PER_SW_AB_1','DC_SW_V1','ARM_SW_AB',1,'PERIOD_1','VARIANT_A_V1',
   '2026-07-02T00:00:00Z','2026-07-03T00:00:00Z',60,
   '2026-05-04T00:00:00Z'),
  ('PER_SW_AB_2','DC_SW_V1','ARM_SW_AB',2,'PERIOD_2','VARIANT_B_V1',
   '2026-07-04T00:00:00Z','2026-07-05T00:00:00Z',60,
   '2026-05-04T00:00:00Z'),
  ('PER_SW_BA_1','DC_SW_V1','ARM_SW_BA',1,'PERIOD_1','VARIANT_B_V1',
   '2026-07-02T00:00:00Z','2026-07-03T00:00:00Z',60,
   '2026-05-04T00:00:00Z'),
  ('PER_SW_BA_2','DC_SW_V1','ARM_SW_BA',2,'PERIOD_2','VARIANT_A_V1',
   '2026-07-04T00:00:00Z','2026-07-05T00:00:00Z',60,
   '2026-05-04T00:00:00Z');

WITH u(unit_id,decision_contract_id,n,first_seen_at,recorded_at) AS (
  VALUES
  ('RP01','DC_RP_V1',101,'2026-05-30T00:00:00Z','2026-05-30T01:00:00Z'),
  ('RP02','DC_RP_V1',102,'2026-05-30T00:00:00Z','2026-05-30T01:00:00Z'),
  ('RP03','DC_RP_V1',103,'2026-05-30T00:00:00Z','2026-05-30T01:00:00Z'),
  ('RP04','DC_RP_V1',104,'2026-05-30T00:00:00Z','2026-05-30T01:00:00Z'),
  ('RP05','DC_RP_V1',105,'2026-05-30T00:00:00Z','2026-05-30T01:00:00Z'),
  ('RP06','DC_RP_V1',106,'2026-05-30T00:00:00Z','2026-05-30T01:00:00Z'),
  ('RP07','DC_RP_V1',107,'2026-05-30T00:00:00Z','2026-05-30T01:00:00Z'),
  ('RP08','DC_RP_V1',108,'2026-05-30T00:00:00Z','2026-05-30T01:00:00Z'),
  ('RPX','DC_RP_V1',109,'2026-05-30T00:00:00Z','2026-05-30T01:00:00Z'),
  ('AA01','DC_AA_V1',201,'2026-06-14T00:00:00Z','2026-06-14T01:00:00Z'),
  ('AA02','DC_AA_V1',202,'2026-06-14T00:00:00Z','2026-06-14T01:00:00Z'),
  ('AA03','DC_AA_V1',203,'2026-06-14T00:00:00Z','2026-06-14T01:00:00Z'),
  ('AA04','DC_AA_V1',204,'2026-06-14T00:00:00Z','2026-06-14T01:00:00Z'),
  ('SW01','DC_SW_V1',301,'2026-06-30T00:00:00Z','2026-06-30T01:00:00Z'),
  ('SW02','DC_SW_V1',302,'2026-06-30T00:00:00Z','2026-06-30T01:00:00Z'),
  ('SW03','DC_SW_V1',303,'2026-06-30T00:00:00Z','2026-06-30T01:00:00Z'),
  ('SW04','DC_SW_V1',304,'2026-06-30T00:00:00Z','2026-06-30T01:00:00Z'),
  ('OB01','DC_OBS_V1',401,'2026-07-14T00:00:00Z','2026-07-14T01:00:00Z'),
  ('OB02','DC_OBS_V1',402,'2026-07-14T00:00:00Z','2026-07-14T01:00:00Z'),
  ('OB03','DC_OBS_V1',403,'2026-07-14T00:00:00Z','2026-07-14T01:00:00Z'),
  ('OB04','DC_OBS_V1',404,'2026-07-14T00:00:00Z','2026-07-14T01:00:00Z'),
  ('OB05','DC_OBS_V1',405,'2026-07-14T00:00:00Z','2026-07-14T01:00:00Z'),
  ('OB06','DC_OBS_V1',406,'2026-07-14T00:00:00Z','2026-07-14T01:00:00Z')
)
INSERT INTO experiment_unit
SELECT unit_id,decision_contract_id,printf('%064x',n),'SYNTHETIC_UNIT',
       first_seen_at,recorded_at
FROM u;

INSERT INTO eligibility_review
SELECT 'EL_'||unit_id,decision_contract_id,unit_id,'ELIG_V1',
       CASE WHEN unit_id='RPX' THEN 'FAIL' ELSE 'PASS' END,
       CASE WHEN unit_id='RPX' THEN 'PREASSIGNMENT_SCOPE_FAIL'
            ELSE 'PREASSIGNMENT_ELIGIBLE' END,
       CASE decision_contract_id
         WHEN 'DC_RP_V1' THEN '2026-05-31T10:00:00Z'
         WHEN 'DC_AA_V1' THEN '2026-06-14T10:00:00Z'
         WHEN 'DC_SW_V1' THEN '2026-06-30T10:00:00Z'
         ELSE '2026-07-14T10:00:00Z' END,
       CASE decision_contract_id
         WHEN 'DC_RP_V1' THEN '2026-05-31T10:05:00Z'
         WHEN 'DC_AA_V1' THEN '2026-06-14T10:05:00Z'
         WHEN 'DC_SW_V1' THEN '2026-06-30T10:05:00Z'
         ELSE '2026-07-14T10:05:00Z' END,
       'EV_ELIGIBILITY'
FROM experiment_unit;

INSERT INTO pre_assignment_exclusion VALUES (
  'EX_RPX','DC_RP_V1','RPX','ELIG_V1','PREASSIGNMENT_SCOPE_FAIL',
  '2026-05-31T10:10:00Z','2026-05-31T10:15:00Z','EV_ELIGIBILITY'
);

WITH x(unit_id,decision_contract_id,arm_id,method_code,assigned_at,n) AS (
  VALUES
  ('RP01','DC_RP_V1','ARM_RP_C','HASH_RANDOMIZED','2026-06-01T10:00:00Z',1001),
  ('RP02','DC_RP_V1','ARM_RP_C','HASH_RANDOMIZED','2026-06-01T10:00:00Z',1002),
  ('RP03','DC_RP_V1','ARM_RP_C','HASH_RANDOMIZED','2026-06-01T10:00:00Z',1003),
  ('RP04','DC_RP_V1','ARM_RP_C','HASH_RANDOMIZED','2026-06-01T10:00:00Z',1004),
  ('RP05','DC_RP_V1','ARM_RP_T','HASH_RANDOMIZED','2026-06-01T10:00:00Z',1005),
  ('RP06','DC_RP_V1','ARM_RP_T','HASH_RANDOMIZED','2026-06-01T10:00:00Z',1006),
  ('RP07','DC_RP_V1','ARM_RP_T','HASH_RANDOMIZED','2026-06-01T10:00:00Z',1007),
  ('RP08','DC_RP_V1','ARM_RP_T','HASH_RANDOMIZED','2026-06-01T10:00:00Z',1008),
  ('AA01','DC_AA_V1','ARM_AA_L','HASH_RANDOMIZED','2026-06-15T10:00:00Z',2001),
  ('AA02','DC_AA_V1','ARM_AA_L','HASH_RANDOMIZED','2026-06-15T10:00:00Z',2002),
  ('AA03','DC_AA_V1','ARM_AA_L','HASH_RANDOMIZED','2026-06-15T10:00:00Z',2003),
  ('AA04','DC_AA_V1','ARM_AA_R','HASH_RANDOMIZED','2026-06-15T10:00:00Z',2004),
  ('SW01','DC_SW_V1','ARM_SW_AB','BLOCK_RANDOMIZED','2026-07-01T10:00:00Z',3001),
  ('SW02','DC_SW_V1','ARM_SW_AB','BLOCK_RANDOMIZED','2026-07-01T10:00:00Z',3002),
  ('SW03','DC_SW_V1','ARM_SW_BA','BLOCK_RANDOMIZED','2026-07-01T10:00:00Z',3003),
  ('SW04','DC_SW_V1','ARM_SW_BA','BLOCK_RANDOMIZED','2026-07-01T10:00:00Z',3004),
  ('OB01','DC_OBS_V1','ARM_OBS_U','OBSERVED_MEMBERSHIP','2026-07-15T10:00:00Z',4001),
  ('OB02','DC_OBS_V1','ARM_OBS_U','OBSERVED_MEMBERSHIP','2026-07-15T10:00:00Z',4002),
  ('OB03','DC_OBS_V1','ARM_OBS_U','OBSERVED_MEMBERSHIP','2026-07-15T10:00:00Z',4003),
  ('OB04','DC_OBS_V1','ARM_OBS_E','OBSERVED_MEMBERSHIP','2026-07-15T10:00:00Z',4004),
  ('OB05','DC_OBS_V1','ARM_OBS_E','OBSERVED_MEMBERSHIP','2026-07-15T10:00:00Z',4005),
  ('OB06','DC_OBS_V1','ARM_OBS_E','OBSERVED_MEMBERSHIP','2026-07-15T10:00:00Z',4006)
)
INSERT INTO experiment_assignment
SELECT 'AS_'||unit_id,decision_contract_id,unit_id,arm_id,'EL_'||unit_id,
       method_code,printf('%064x',n),assigned_at,datetime(assigned_at,'+1 minute'),
       'ITT_INCLUDED','EV_ASSIGNMENT'
FROM x;

-- Exposure is downstream of immutable assignment.  The switchback fixture keeps
-- the scheduled period exposures separate from explicitly flagged spillover.
WITH x(exposure_event_id,decision_contract_id,assignment_id,exposure_kind,
       intervention_version,occurred_at) AS (
  VALUES
  ('XP_RP01','DC_RP_V1','AS_RP01','CONTROL_BASELINE','BASELINE_V1','2026-06-02T10:00:00Z'),
  ('XP_RP02','DC_RP_V1','AS_RP02','CONTROL_BASELINE','BASELINE_V1','2026-06-02T10:01:00Z'),
  ('XP_RP03','DC_RP_V1','AS_RP03','CONTROL_BASELINE','BASELINE_V1','2026-06-02T10:02:00Z'),
  ('XP_RP04','DC_RP_V1','AS_RP04','CONTROL_BASELINE','BASELINE_V1','2026-06-02T10:03:00Z'),
  ('XP_RP05','DC_RP_V1','AS_RP05','ASSIGNED_VARIANT','ASSISTED_V1','2026-06-02T10:04:00Z'),
  ('XP_RP06','DC_RP_V1','AS_RP06','ASSIGNED_VARIANT','ASSISTED_V1','2026-06-02T10:05:00Z'),
  ('XP_RP07','DC_RP_V1','AS_RP07','ASSIGNED_VARIANT','ASSISTED_V1','2026-06-02T10:06:00Z'),
  ('XP_RP08','DC_RP_V1','AS_RP08','ASSIGNED_VARIANT','ASSISTED_V1','2026-06-02T10:07:00Z'),
  ('XP_AA01','DC_AA_V1','AS_AA01','ASSIGNED_VARIANT','BASELINE_IDENTICAL_V1','2026-06-16T10:00:00Z'),
  ('XP_AA02','DC_AA_V1','AS_AA02','ASSIGNED_VARIANT','BASELINE_IDENTICAL_V1','2026-06-16T10:01:00Z'),
  ('XP_AA03','DC_AA_V1','AS_AA03','ASSIGNED_VARIANT','BASELINE_IDENTICAL_V1','2026-06-16T10:02:00Z'),
  ('XP_AA04','DC_AA_V1','AS_AA04','ASSIGNED_VARIANT','BASELINE_IDENTICAL_V1','2026-06-16T10:03:00Z'),
  ('XP_OB04','DC_OBS_V1','AS_OB04','OBSERVED_NATURAL_EXPOSURE','NATURAL_EXPOSED_V1','2026-07-16T10:00:00Z'),
  ('XP_OB05','DC_OBS_V1','AS_OB05','OBSERVED_NATURAL_EXPOSURE','NATURAL_EXPOSED_V1','2026-07-16T10:01:00Z'),
  ('XP_OB06','DC_OBS_V1','AS_OB06','OBSERVED_NATURAL_EXPOSURE','NATURAL_EXPOSED_V1','2026-07-16T10:02:00Z')
)
INSERT INTO exposure_event
SELECT exposure_event_id,decision_contract_id,assignment_id,NULL,exposure_kind,
       intervention_version,1.0,occurred_at,datetime(occurred_at,'+5 minutes'),
       'EV_EXPOSURE'
FROM x;

WITH x(exposure_event_id,assignment_id,period_contract_id) AS (
  VALUES
  ('XP_SW01_P1','AS_SW01','PER_SW_AB_1'),('XP_SW01_P2','AS_SW01','PER_SW_AB_2'),
  ('XP_SW02_P1','AS_SW02','PER_SW_AB_1'),('XP_SW02_P2','AS_SW02','PER_SW_AB_2'),
  ('XP_SW03_P1','AS_SW03','PER_SW_BA_1'),('XP_SW03_P2','AS_SW03','PER_SW_BA_2'),
  ('XP_SW04_P1','AS_SW04','PER_SW_BA_1'),('XP_SW04_P2','AS_SW04','PER_SW_BA_2')
)
INSERT INTO exposure_event
SELECT x.exposure_event_id,p.decision_contract_id,x.assignment_id,
       x.period_contract_id,'ASSIGNED_VARIANT',p.scheduled_intervention_version,
       1.0,datetime(p.period_starts_at,'+12 hours'),
       datetime(p.period_starts_at,'+12 hours','+5 minutes'),'EV_EXPOSURE'
FROM x JOIN switchback_period_contract p USING (period_contract_id);

INSERT INTO exposure_event VALUES
  ('XP_SW01_CROSS','DC_SW_V1','AS_SW01',NULL,'CROSS_ARM_EXPOSURE',
   'VARIANT_B_V1',1.0,'2026-07-02T18:00:00Z','2026-07-02T18:05:00Z','EV_EXPOSURE'),
  ('XP_SW03_INTERF','DC_SW_V1','AS_SW03',NULL,'INTERFERENCE_EXPOSURE',
   'PEER_SPILLOVER_V1',1.0,'2026-07-04T18:00:00Z','2026-07-04T18:05:00Z','EV_EXPOSURE');

INSERT INTO contamination_event VALUES (
  'CT_SW_CROSS_1','CTS_SW_CROSS',1,NULL,'DC_SW_V1','AS_SW01',
  'XP_SW01_CROSS','CROSS_ARM','OPEN',1,
  '2026-07-02T18:00:00Z','2026-07-02T18:10:00Z','EV_INTEGRITY'
);

-- The expected_outcome_spine is preregistered after assignment but before the
-- measurement window.  It is the denominator and maturity source of truth.
WITH required_metric(decision_contract_id,metric_contract_id) AS (
  VALUES
  ('DC_RP_V1','MET_RP_PRI_V2'),('DC_RP_V1','MET_RP_SAFE_V1'),
  ('DC_RP_V1','MET_RP_DIAG_V1'),('DC_AA_V1','MET_AA_PRI_V1'),
  ('DC_SW_V1','MET_SW_PRI_V1'),('DC_SW_V1','MET_SW_SAFE_V1'),
  ('DC_OBS_V1','MET_OBS_PRI_V1')
), windows(decision_contract_id,opens_at,closes_at,matures_at,registered_at) AS (
  VALUES
  ('DC_RP_V1','2026-06-02T00:00:00Z','2026-06-08T23:59:59Z','2026-06-10T00:00:00Z','2026-06-01T12:00:00Z'),
  ('DC_AA_V1','2026-06-16T00:00:00Z','2026-06-20T23:59:59Z','2026-06-22T00:00:00Z','2026-06-15T12:00:00Z'),
  ('DC_SW_V1','2026-07-02T00:00:00Z','2026-07-08T23:59:59Z','2026-07-10T00:00:00Z','2026-07-01T12:00:00Z'),
  ('DC_OBS_V1','2026-07-16T00:00:00Z','2026-07-22T23:59:59Z','2026-07-24T00:00:00Z','2026-07-15T12:00:00Z')
)
INSERT INTO expected_outcome_spine
SELECT 'EO_'||a.unit_id||'_'||r.metric_contract_id,a.decision_contract_id,
       a.assignment_id,r.metric_contract_id,w.opens_at,w.closes_at,
       w.matures_at,w.registered_at,'EV_CONTRACT'
FROM experiment_assignment a
JOIN required_metric r USING (decision_contract_id)
JOIN windows w USING (decision_contract_id);

-- Observed fixtures.  Missingness is a classification with no numeric value;
-- it is never substituted with zero in either the raw or snapshot readout.
WITH v(unit_id,metric_contract_id,numeric_value,occurred_at) AS (
  VALUES
  ('RP01','MET_RP_PRI_V2',0.0,'2026-06-05T10:00:00Z'),
  ('RP02','MET_RP_PRI_V2',1.0,'2026-06-05T10:01:00Z'),
  ('RP03','MET_RP_PRI_V2',0.0,'2026-06-05T10:02:00Z'),
  ('RP04','MET_RP_PRI_V2',1.0,'2026-06-05T10:03:00Z'),
  ('RP05','MET_RP_PRI_V2',1.0,'2026-06-05T10:04:00Z'),
  ('RP06','MET_RP_PRI_V2',1.0,'2026-06-05T10:05:00Z'),
  ('RP07','MET_RP_PRI_V2',1.0,'2026-06-05T10:06:00Z'),
  ('RP08','MET_RP_PRI_V2',0.0,'2026-06-05T10:07:00Z'),
  ('RP01','MET_RP_SAFE_V1',0.0,'2026-06-06T10:00:00Z'),
  ('RP02','MET_RP_SAFE_V1',0.0,'2026-06-06T10:01:00Z'),
  ('RP03','MET_RP_SAFE_V1',0.0,'2026-06-06T10:02:00Z'),
  ('RP04','MET_RP_SAFE_V1',0.0,'2026-06-06T10:03:00Z'),
  ('RP05','MET_RP_SAFE_V1',0.0,'2026-06-06T10:04:00Z'),
  ('RP06','MET_RP_SAFE_V1',0.0,'2026-06-06T10:05:00Z'),
  ('RP07','MET_RP_SAFE_V1',0.0,'2026-06-06T10:06:00Z'),
  ('RP08','MET_RP_SAFE_V1',0.0,'2026-06-06T10:07:00Z'),
  ('RP01','MET_RP_DIAG_V1',20.0,'2026-06-07T10:00:00Z'),
  ('RP02','MET_RP_DIAG_V1',25.0,'2026-06-07T10:01:00Z'),
  ('RP03','MET_RP_DIAG_V1',22.0,'2026-06-07T10:02:00Z'),
  ('RP04','MET_RP_DIAG_V1',24.0,'2026-06-07T10:03:00Z'),
  ('RP05','MET_RP_DIAG_V1',18.0,'2026-06-07T10:04:00Z'),
  ('RP06','MET_RP_DIAG_V1',16.0,'2026-06-07T10:05:00Z'),
  ('RP07','MET_RP_DIAG_V1',19.0,'2026-06-07T10:06:00Z'),
  ('RP08','MET_RP_DIAG_V1',17.0,'2026-06-07T10:07:00Z'),
  ('AA01','MET_AA_PRI_V1',1.0,'2026-06-18T10:00:00Z'),
  ('AA02','MET_AA_PRI_V1',1.0,'2026-06-18T10:01:00Z'),
  ('AA04','MET_AA_PRI_V1',1.0,'2026-06-18T10:03:00Z'),
  ('SW01','MET_SW_PRI_V1',50.0,'2026-07-07T10:00:00Z'),
  ('SW02','MET_SW_PRI_V1',55.0,'2026-07-07T10:01:00Z'),
  ('SW03','MET_SW_PRI_V1',45.0,'2026-07-07T10:02:00Z'),
  ('SW04','MET_SW_PRI_V1',48.0,'2026-07-07T10:03:00Z'),
  ('SW01','MET_SW_SAFE_V1',0.0,'2026-07-08T10:00:00Z'),
  ('SW02','MET_SW_SAFE_V1',0.0,'2026-07-08T10:01:00Z'),
  ('SW03','MET_SW_SAFE_V1',0.0,'2026-07-08T10:02:00Z'),
  ('SW04','MET_SW_SAFE_V1',0.0,'2026-07-08T10:03:00Z'),
  ('OB01','MET_OBS_PRI_V1',0.0,'2026-07-20T10:00:00Z'),
  ('OB02','MET_OBS_PRI_V1',0.0,'2026-07-20T10:01:00Z'),
  ('OB03','MET_OBS_PRI_V1',1.0,'2026-07-20T10:02:00Z'),
  ('OB04','MET_OBS_PRI_V1',1.0,'2026-07-20T10:03:00Z'),
  ('OB05','MET_OBS_PRI_V1',1.0,'2026-07-20T10:04:00Z'),
  ('OB06','MET_OBS_PRI_V1',0.0,'2026-07-20T10:05:00Z')
)
INSERT INTO outcome_observation
SELECT 'OO_'||v.unit_id||'_'||v.metric_contract_id,
       'EO_'||v.unit_id||'_'||v.metric_contract_id,'OBSERVED_VALUE',
       v.numeric_value,v.occurred_at,NULL,s.expected_maturity_at,
       datetime(s.expected_maturity_at,'+5 minutes'),'EV_OUTCOME'
FROM v JOIN expected_outcome_spine s
  ON s.expected_outcome_id='EO_'||v.unit_id||'_'||v.metric_contract_id;

INSERT INTO outcome_observation VALUES (
  'OO_AA03_MET_AA_PRI_V1','EO_AA03_MET_AA_PRI_V1','MISSING_CONFIRMED',
  NULL,NULL,'SOURCE_COVERAGE_GAP','2026-06-22T00:00:00Z',
  '2026-06-22T00:05:00Z','EV_OUTCOME'
);

-- All seven named integrity categories are represented.  The parallel test's
-- duplicate/time alerts are resolved append-only; A/A and switchback retain
-- deliberately open blockers to demonstrate bounded decisions.
INSERT INTO integrity_issue_stream VALUES
  ('IS_RP_DUP','DC_RP_V1','DUPLICATE','PRIMARY_EVENT_KEY',1,1,'2026-06-10T02:00:00Z','EV_INTEGRITY'),
  ('IS_RP_TIME','DC_RP_V1','TIME','OUTCOME_CLOCK',1,1,'2026-06-10T02:10:00Z','EV_INTEGRITY'),
  ('IS_AA_SRM','DC_AA_V1','SRM','ARM_ALLOCATION',1,1,'2026-06-22T02:00:00Z','EV_INTEGRITY'),
  ('IS_AA_COV','DC_AA_V1','COVERAGE','PRIMARY_CAPTURE',1,1,'2026-06-22T02:10:00Z','EV_INTEGRITY'),
  ('IS_SW_CONT','DC_SW_V1','CONTAMINATION','CROSS_ARM_EXPOSURE',1,1,'2026-07-10T02:00:00Z','EV_INTEGRITY'),
  ('IS_SW_CARRY','DC_SW_V1','CARRYOVER','WASHOUT_ASSESSMENT',1,1,'2026-07-10T02:10:00Z','EV_INTEGRITY'),
  ('IS_SW_INTERF','DC_SW_V1','INTERFERENCE','PEER_SPILLOVER',1,1,'2026-07-10T02:20:00Z','EV_INTEGRITY');

INSERT INTO integrity_issue_event VALUES
  ('IE_RP_DUP_1','IS_RP_DUP',1,NULL,'OPEN','POSSIBLE_DUPLICATE_KEYS','2026-06-10T02:00:00Z','2026-06-10T02:05:00Z','EV_INTEGRITY'),
  ('IE_RP_DUP_2','IS_RP_DUP',2,'IE_RP_DUP_1','RESOLVED','KEY_AUDIT_CONFIRMED_UNIQUE','2026-06-11T02:00:00Z','2026-06-11T02:05:00Z','EV_INTEGRITY'),
  ('IE_RP_TIME_1','IS_RP_TIME',1,NULL,'OPEN','CLOCK_ALIGNMENT_UNVERIFIED','2026-06-10T02:10:00Z','2026-06-10T02:15:00Z','EV_INTEGRITY'),
  ('IE_RP_TIME_2','IS_RP_TIME',2,'IE_RP_TIME_1','RESOLVED','CLOCK_ALIGNMENT_CONFIRMED','2026-06-11T02:10:00Z','2026-06-11T02:15:00Z','EV_INTEGRITY'),
  ('IE_AA_SRM_1','IS_AA_SRM',1,NULL,'OPEN','OBSERVED_ALLOCATION_3_TO_1','2026-06-22T02:00:00Z','2026-06-22T02:05:00Z','EV_INTEGRITY'),
  ('IE_AA_COV_1','IS_AA_COV',1,NULL,'OPEN','ONE_ASSIGNED_OUTCOME_MISSING','2026-06-22T02:10:00Z','2026-06-22T02:15:00Z','EV_INTEGRITY'),
  ('IE_SW_CONT_1','IS_SW_CONT',1,NULL,'OPEN','CROSS_ARM_EXPOSURE_CONFIRMED','2026-07-10T02:00:00Z','2026-07-10T02:05:00Z','EV_INTEGRITY'),
  ('IE_SW_CARRY_1','IS_SW_CARRY',1,NULL,'UNKNOWN','CARRYOVER_NOT_RESOLVED','2026-07-10T02:10:00Z','2026-07-10T02:15:00Z','EV_INTEGRITY'),
  ('IE_SW_INTERF_1','IS_SW_INTERF',1,NULL,'OPEN','PEER_SPILLOVER_CONFIRMED','2026-07-10T02:20:00Z','2026-07-10T02:25:00Z','EV_INTEGRITY');

INSERT INTO safety_review VALUES
  ('SR_RP','DC_RP_V1','PASS',8,8,0,'2026-06-12T10:00:00Z','2026-06-12T10:05:00Z','EV_SAFETY'),
  ('SR_AA','DC_AA_V1','NA',0,0,0,'2026-06-23T10:00:00Z','2026-06-23T10:05:00Z','EV_SAFETY'),
  ('SR_SW','DC_SW_V1','PASS',4,4,0,'2026-07-11T10:00:00Z','2026-07-11T10:05:00Z','EV_SAFETY'),
  ('SR_OBS','DC_OBS_V1','NA',0,0,0,'2026-07-25T10:00:00Z','2026-07-25T10:05:00Z','EV_SAFETY');

INSERT INTO experiment_cost_event VALUES
  ('CE_RP_CASH','DC_RP_V1','CASH','IMPLEMENTATION',5000000000,NULL,'JPY','2026-06-12T11:00:00Z','2026-06-12T11:05:00Z','EV_COST'),
  ('CE_RP_TIME','DC_RP_V1','FOUNDER_TIME','REVIEW',NULL,180,'JPY','2026-06-12T11:10:00Z','2026-06-12T11:15:00Z','EV_COST'),
  ('CE_AA_CASH','DC_AA_V1','CASH','MEASUREMENT',1000000000,NULL,'JPY','2026-06-23T11:00:00Z','2026-06-23T11:05:00Z','EV_COST'),
  ('CE_AA_TIME','DC_AA_V1','FOUNDER_TIME','REVIEW',NULL,60,'JPY','2026-06-23T11:10:00Z','2026-06-23T11:15:00Z','EV_COST'),
  ('CE_SW_CASH','DC_SW_V1','CASH','DELIVERY',2000000000,NULL,'JPY','2026-07-11T11:00:00Z','2026-07-11T11:05:00Z','EV_COST'),
  ('CE_SW_TIME','DC_SW_V1','FOUNDER_TIME','INCIDENT',NULL,90,'JPY','2026-07-11T11:10:00Z','2026-07-11T11:15:00Z','EV_COST'),
  ('CE_OBS_TIME','DC_OBS_V1','FOUNDER_TIME','MEASUREMENT',NULL,45,'JPY','2026-07-25T11:00:00Z','2026-07-25T11:05:00Z','EV_COST');

INSERT INTO analysis_snapshot VALUES
  ('AN_RP_1','DC_RP_V1',1,NULL,'2026-08-01T12:00:00Z','2026-08-01T11:59:59Z',
   'CAUSAL_ITT_WITHIN_CONTRACT','NA','COMPLETE','PASS','PASS','READY',
   8,24,24,24,0,0,0,5000000000,180,18000000000,23000000000,
   'SR_RP','2026-08-01T12:05:00Z','EV_ANALYSIS'),
  ('AN_AA_1','DC_AA_V1',1,NULL,'2026-08-01T12:00:00Z','2026-08-01T11:59:59Z',
   'DESCRIPTIVE_ONLY','NA','COMPLETE','BLOCKED','NA','BLOCKED',
   4,4,4,4,2,2,2,1000000000,60,6000000000,7000000000,
   'SR_AA','2026-08-01T12:05:00Z','EV_ANALYSIS'),
  ('AN_SW_1','DC_SW_V1',1,NULL,'2026-08-01T12:00:00Z','2026-08-01T11:59:59Z',
   'DESCRIPTIVE_ONLY','UNKNOWN','COMPLETE','BLOCKED','PASS','BLOCKED',
   4,8,8,8,3,3,3,2000000000,90,9000000000,11000000000,
   'SR_SW','2026-08-01T12:05:00Z','EV_ANALYSIS'),
  ('AN_OBS_1','DC_OBS_V1',1,NULL,'2026-08-01T12:00:00Z','2026-08-01T11:59:59Z',
   'ASSOCIATION_ONLY','NA','COMPLETE','PASS','NA','READY',
   6,6,6,6,0,0,0,0,45,4500000000,4500000000,
   'SR_OBS','2026-08-01T12:05:00Z','EV_ANALYSIS');

INSERT INTO analysis_arm_readout
SELECT 'AR_'||r.analysis_snapshot_id||'_'||r.arm_id||'_'||r.metric_contract_id,
       r.analysis_snapshot_id,r.arm_id,r.metric_contract_id,r.assigned_n,
       r.expected_n,r.matured_expected_n,r.classified_n,r.observed_n,r.missing_n,
       r.observed_sum,r.observed_mean,r.raw_itt_mean,r.readout_state,
       '2026-08-01T12:10:00Z','EV_ANALYSIS'
FROM v_snapshot_itt_arm_readout r
WHERE r.expected_n>0;

INSERT INTO decision_snapshot VALUES
  ('DS_RP_1','DC_RP_V1',1,NULL,'AN_RP_1','EXPAND',
   'CAUSAL_ITT_WITHIN_CONTRACT','BOUNDED_NEXT_BATCH',
   '2026-08-01T13:00:00Z','2026-08-01T13:05:00Z','FOUNDER',printf('%064x',5001),'EV_DECISION'),
  ('DS_AA_1','DC_AA_V1',1,NULL,'AN_AA_1','CHANGE',
   'DESCRIPTIVE_ONLY','REPAIR_INSTRUMENTATION_AND_RERUN_AA',
   '2026-08-01T13:00:00Z','2026-08-01T13:05:00Z','FOUNDER',printf('%064x',5002),'EV_DECISION'),
  ('DS_SW_1','DC_SW_V1',1,NULL,'AN_SW_1','PAUSE',
   'DESCRIPTIVE_ONLY','RESOLVE_CARRYOVER_CONTAMINATION_INTERFERENCE',
   '2026-08-01T13:00:00Z','2026-08-01T13:05:00Z','FOUNDER',printf('%064x',5003),'EV_DECISION'),
  ('DS_OBS_1','DC_OBS_V1',1,NULL,'AN_OBS_1','MAINTAIN',
   'ASSOCIATION_ONLY','KEEP_AS_ASSOCIATION_AND_DESIGN_RANDOMIZED_FOLLOWUP',
   '2026-08-01T13:00:00Z','2026-08-01T13:05:00Z','FOUNDER',printf('%064x',5004),'EV_DECISION');

-- -----------------------------------------------------------------------------
-- Named data-quality contract (DQ01-DQ102)
-- -----------------------------------------------------------------------------

CREATE TEMP VIEW v_dq_results AS
SELECT 'DQ01' AS dq_id,'FOREIGN_KEYS_ENABLED' AS check_name,
       CASE WHEN (SELECT foreign_keys FROM pragma_foreign_keys)=1
            THEN 'PASS' ELSE 'FAIL' END AS status,
       CASE WHEN (SELECT foreign_keys FROM pragma_foreign_keys)=1
            THEN 0 ELSE 1 END AS failing_row_count
UNION ALL
SELECT 'DQ02','ALL_25_DOMAIN_TABLES_ARE_STRICT',
       CASE WHEN (SELECT count(*) FROM pragma_table_list
                  WHERE schema='main' AND type='table'
                    AND name NOT LIKE 'sqlite_%' AND strict=1)=25
                  AND NOT EXISTS (SELECT 1 FROM pragma_table_list
                                  WHERE schema='main' AND type='table'
                                    AND name NOT LIKE 'sqlite_%' AND strict=0)
            THEN 'PASS' ELSE 'FAIL' END,
       (SELECT count(*) FROM pragma_table_list WHERE schema='main'
          AND type='table' AND name NOT LIKE 'sqlite_%' AND strict=0)
       +abs((SELECT count(*) FROM pragma_table_list WHERE schema='main'
             AND type='table' AND name NOT LIKE 'sqlite_%' AND strict=1)-25)
UNION ALL
SELECT 'DQ03','SCHEMA_IDENTITY_AND_ENGINE_CONTRACT',
       CASE WHEN EXISTS (SELECT 1 FROM schema_registry WHERE schema_id=1
                          AND schema_name='small_data_experiment_decision'
                          AND schema_version='1.0.0'
                          AND engine_floor='SQLite 3.37+')
            THEN 'PASS' ELSE 'FAIL' END,
       CASE WHEN EXISTS (SELECT 1 FROM schema_registry WHERE schema_id=1
                          AND schema_name='small_data_experiment_decision'
                          AND schema_version='1.0.0'
                          AND engine_floor='SQLite 3.37+')
            THEN 0 ELSE 1 END
UNION ALL
SELECT 'DQ04','SYNTHETIC_NO_RAW_PII_POLICY',
       CASE WHEN (SELECT data_policy_code FROM schema_registry WHERE schema_id=1)
                    ='SYNTHETIC_ONLY_NO_RAW_PII_OPAQUE_REFERENCES'
            THEN 'PASS' ELSE 'FAIL' END,
       CASE WHEN (SELECT data_policy_code FROM schema_registry WHERE schema_id=1)
                    ='SYNTHETIC_ONLY_NO_RAW_PII_OPAQUE_REFERENCES'
            THEN 0 ELSE 1 END
UNION ALL
SELECT 'DQ05','EVIDENCE_REFERENCE_HASH_FORMAT',
       CASE WHEN NOT EXISTS (SELECT 1 FROM evidence_record
                             WHERE length(source_ref_hash)<>64
                                OR source_ref_hash<>lower(source_ref_hash)
                                OR source_ref_hash GLOB '*[^0-9a-f]*')
            THEN 'PASS' ELSE 'FAIL' END,
       (SELECT count(*) FROM evidence_record WHERE length(source_ref_hash)<>64
          OR source_ref_hash<>lower(source_ref_hash)
          OR source_ref_hash GLOB '*[^0-9a-f]*')
UNION ALL
SELECT 'DQ06','EVIDENCE_EVENT_PRECEDES_RECORD',
       CASE WHEN NOT EXISTS (SELECT 1 FROM evidence_record
                             WHERE datetime(occurred_at)>datetime(recorded_at))
            THEN 'PASS' ELSE 'FAIL' END,
       (SELECT count(*) FROM evidence_record
        WHERE datetime(occurred_at)>datetime(recorded_at))
UNION ALL
SELECT 'DQ07','APPEND_ONLY_TRIGGER_PAIRS_PRESENT',
       CASE WHEN (SELECT count(*) FROM sqlite_schema WHERE type='trigger'
                  AND (name LIKE 'no_update_%' OR name LIKE 'no_delete_%'))=50
            THEN 'PASS' ELSE 'FAIL' END,
       abs((SELECT count(*) FROM sqlite_schema WHERE type='trigger'
            AND (name LIKE 'no_update_%' OR name LIKE 'no_delete_%'))-50)
UNION ALL
SELECT 'DQ08','NO_INFERENTIAL_ORACLE_FIELDS',
       CASE WHEN NOT EXISTS (
         SELECT 1 FROM sqlite_schema WHERE type='table' AND (
           lower(sql) LIKE '%p_value%' OR lower(sql) LIKE '%pvalue%'
           OR lower(sql) LIKE '%significance%' OR lower(sql) LIKE '%statistical_power%'
           OR lower(sql) LIKE '%confidence_interval%'))
            THEN 'PASS' ELSE 'FAIL' END,
       (SELECT count(*) FROM sqlite_schema WHERE type='table' AND (
          lower(sql) LIKE '%p_value%' OR lower(sql) LIKE '%pvalue%'
          OR lower(sql) LIKE '%significance%' OR lower(sql) LIKE '%statistical_power%'
          OR lower(sql) LIKE '%confidence_interval%'))
UNION ALL
SELECT 'DQ09','MODEL_SETTINGS_ARE_BOUNDED',
       CASE WHEN EXISTS (SELECT 1 FROM model_setting WHERE singleton_id=1
                          AND founder_rate_micros_per_minute>0
                          AND bounded_output_limit BETWEEN 1 AND 50
                          AND datetime(default_snapshot_as_of)
                              <=datetime(reference_time))
            THEN 'PASS' ELSE 'FAIL' END,
       CASE WHEN EXISTS (SELECT 1 FROM model_setting WHERE singleton_id=1
                          AND founder_rate_micros_per_minute>0
                          AND bounded_output_limit BETWEEN 1 AND 50
                          AND datetime(default_snapshot_as_of)
                              <=datetime(reference_time))
            THEN 0 ELSE 1 END
UNION ALL
SELECT 'DQ10','CLAIM_LEVEL_ORDER_IS_TOTAL',
       CASE WHEN (SELECT count(*) FROM claim_level_dim)=3
                  AND (SELECT min(claim_rank) FROM claim_level_dim)=0
                  AND (SELECT max(claim_rank) FROM claim_level_dim)=2
                  AND (SELECT count(DISTINCT claim_rank) FROM claim_level_dim)=3
            THEN 'PASS' ELSE 'FAIL' END,
       CASE WHEN (SELECT count(*) FROM claim_level_dim)=3
                  AND (SELECT min(claim_rank) FROM claim_level_dim)=0
                  AND (SELECT max(claim_rank) FROM claim_level_dim)=2
                  AND (SELECT count(DISTINCT claim_rank) FROM claim_level_dim)=3
            THEN 0 ELSE 1 END
UNION ALL
SELECT 'DQ11','DESIGN_CLAIM_CAPS_RESOLVE',
       CASE WHEN NOT EXISTS (
         SELECT 1 FROM design_type_dim d
         LEFT JOIN claim_level_dim l
           ON l.claim_level_code=d.design_claim_cap_code
         WHERE l.claim_level_code IS NULL) THEN 'PASS' ELSE 'FAIL' END,
       (SELECT count(*) FROM design_type_dim d LEFT JOIN claim_level_dim l
          ON l.claim_level_code=d.design_claim_cap_code
        WHERE l.claim_level_code IS NULL)
UNION ALL
SELECT 'DQ12','CONTRACTS_PRECEDE_ENROLLMENT',
       CASE WHEN NOT EXISTS (SELECT 1 FROM decision_contract
                             WHERE datetime(approved_at)>=datetime(enrollment_opens_at)
                                OR datetime(enrollment_opens_at)>datetime(enrollment_closes_at)
                                OR datetime(enrollment_closes_at)>datetime(final_maturity_at))
            THEN 'PASS' ELSE 'FAIL' END,
       (SELECT count(*) FROM decision_contract
        WHERE datetime(approved_at)>=datetime(enrollment_opens_at)
           OR datetime(enrollment_opens_at)>datetime(enrollment_closes_at)
           OR datetime(enrollment_closes_at)>datetime(final_maturity_at))
UNION ALL
SELECT 'DQ13','METRIC_VERSION_CHAINS_ARE_EXACT',
       CASE WHEN NOT EXISTS (
         SELECT 1 FROM metric_contract_version m
         WHERE (m.metric_version=1 AND m.supersedes_metric_contract_id IS NOT NULL)
            OR (m.metric_version>1 AND NOT EXISTS (
              SELECT 1 FROM metric_contract_version p
              WHERE p.metric_contract_id=m.supersedes_metric_contract_id
                AND p.metric_stream_id=m.metric_stream_id
                AND p.metric_version=m.metric_version-1)))
            THEN 'PASS' ELSE 'FAIL' END,
       (SELECT count(*) FROM metric_contract_version m
        WHERE (m.metric_version=1 AND m.supersedes_metric_contract_id IS NOT NULL)
           OR (m.metric_version>1 AND NOT EXISTS (
             SELECT 1 FROM metric_contract_version p
             WHERE p.metric_contract_id=m.supersedes_metric_contract_id
               AND p.metric_stream_id=m.metric_stream_id
               AND p.metric_version=m.metric_version-1)))
UNION ALL
SELECT 'DQ14','PRIMARY_GUARDRAIL_DIAGNOSTIC_ROLES_PRESENT',
       CASE WHEN (SELECT count(DISTINCT metric_role) FROM metric_contract_version)=3
            THEN 'PASS' ELSE 'FAIL' END,
       3-(SELECT count(DISTINCT metric_role) FROM metric_contract_version)
UNION ALL
SELECT 'DQ15','MISSING_POLICY_NEVER_ZERO',
       CASE WHEN NOT EXISTS (SELECT 1 FROM metric_contract_version
                             WHERE missing_policy_code<>'EXPLICIT_MISSING_NEVER_ZERO')
            THEN 'PASS' ELSE 'FAIL' END,
       (SELECT count(*) FROM metric_contract_version
        WHERE missing_policy_code<>'EXPLICIT_MISSING_NEVER_ZERO')
UNION ALL
SELECT 'DQ16','ONE_CURRENT_PRIMARY_AT_ASSIGNMENT',
       CASE WHEN NOT EXISTS (
         SELECT 1 FROM decision_contract c WHERE (
           SELECT count(*) FROM metric_contract_version m
           WHERE m.decision_contract_id=c.decision_contract_id
             AND m.metric_role='PRIMARY'
             AND datetime(m.registered_at)<=datetime((
               SELECT min(assigned_at) FROM experiment_assignment
               WHERE decision_contract_id=c.decision_contract_id))
             AND NOT EXISTS (
               SELECT 1 FROM metric_contract_version later
               WHERE later.metric_stream_id=m.metric_stream_id
                 AND later.metric_version>m.metric_version
                 AND datetime(later.registered_at)<=datetime((
                   SELECT min(assigned_at) FROM experiment_assignment
                   WHERE decision_contract_id=c.decision_contract_id))))<>1)
            THEN 'PASS' ELSE 'FAIL' END,
       (SELECT count(*) FROM decision_contract c WHERE (
          SELECT count(*) FROM metric_contract_version m
          WHERE m.decision_contract_id=c.decision_contract_id
            AND m.metric_role='PRIMARY'
            AND datetime(m.registered_at)<=datetime((SELECT min(assigned_at)
                FROM experiment_assignment WHERE decision_contract_id=c.decision_contract_id))
            AND NOT EXISTS (SELECT 1 FROM metric_contract_version later
                WHERE later.metric_stream_id=m.metric_stream_id
                  AND later.metric_version>m.metric_version
                  AND datetime(later.registered_at)<=datetime((SELECT min(assigned_at)
                    FROM experiment_assignment WHERE decision_contract_id=c.decision_contract_id))))<>1)
UNION ALL
SELECT 'DQ17','ARM_PLANNED_ALLOCATIONS_SUM_TO_ONE',
       CASE WHEN NOT EXISTS (SELECT 1 FROM arm_contract GROUP BY decision_contract_id
                             HAVING sum(planned_share_ppm)<>1000000)
            THEN 'PASS' ELSE 'FAIL' END,
       (SELECT count(*) FROM (SELECT decision_contract_id FROM arm_contract
        GROUP BY decision_contract_id HAVING sum(planned_share_ppm)<>1000000))
UNION ALL
SELECT 'DQ18','ONE_REFERENCE_AND_COMPARATOR_PER_PAIR',
       CASE WHEN NOT EXISTS (
         SELECT 1 FROM arm_contract GROUP BY decision_contract_id,comparison_pair_code
         HAVING sum(arm_role='REFERENCE')<>1 OR sum(arm_role='COMPARATOR')<>1)
            THEN 'PASS' ELSE 'FAIL' END,
       (SELECT count(*) FROM (SELECT decision_contract_id,comparison_pair_code
        FROM arm_contract GROUP BY decision_contract_id,comparison_pair_code
        HAVING sum(arm_role='REFERENCE')<>1 OR sum(arm_role='COMPARATOR')<>1))
UNION ALL
SELECT 'DQ19','AA_ARMS_HAVE_IDENTICAL_INTERVENTION',
       CASE WHEN (SELECT count(DISTINCT intervention_version) FROM arm_contract
                  WHERE decision_contract_id='DC_AA_V1')=1
            THEN 'PASS' ELSE 'FAIL' END,
       (SELECT count(DISTINCT intervention_version) FROM arm_contract
        WHERE decision_contract_id='DC_AA_V1')-1
UNION ALL
SELECT 'DQ20','ASSIGNMENT_METHOD_MATCHES_DESIGN',
       CASE WHEN NOT EXISTS (
         SELECT 1 FROM experiment_assignment a JOIN decision_contract c USING (decision_contract_id)
         JOIN design_type_dim d USING (design_type_code)
         WHERE (d.requires_random_assignment=1
                AND a.assignment_method_code NOT IN ('HASH_RANDOMIZED','BLOCK_RANDOMIZED'))
            OR (c.design_type_code='OBSERVATIONAL_ASSOCIATION'
                AND a.assignment_method_code<>'OBSERVED_MEMBERSHIP'))
            THEN 'PASS' ELSE 'FAIL' END,
       (SELECT count(*) FROM experiment_assignment a
        JOIN decision_contract c USING (decision_contract_id)
        JOIN design_type_dim d USING (design_type_code)
        WHERE (d.requires_random_assignment=1
               AND a.assignment_method_code NOT IN ('HASH_RANDOMIZED','BLOCK_RANDOMIZED'))
           OR (c.design_type_code='OBSERVATIONAL_ASSOCIATION'
               AND a.assignment_method_code<>'OBSERVED_MEMBERSHIP'))
UNION ALL
SELECT 'DQ21','ASSIGNMENT_REFERENCE_HASH_FORMAT',
       CASE WHEN NOT EXISTS (SELECT 1 FROM experiment_assignment
                             WHERE length(assignment_key_ref_hash)<>64
                                OR assignment_key_ref_hash<>lower(assignment_key_ref_hash)
                                OR assignment_key_ref_hash GLOB '*[^0-9a-f]*')
            THEN 'PASS' ELSE 'FAIL' END,
       (SELECT count(*) FROM experiment_assignment
        WHERE length(assignment_key_ref_hash)<>64
           OR assignment_key_ref_hash<>lower(assignment_key_ref_hash)
           OR assignment_key_ref_hash GLOB '*[^0-9a-f]*')
UNION ALL
SELECT 'DQ22','PASS_ELIGIBILITY_PRECEDES_ASSIGNMENT',
       CASE WHEN NOT EXISTS (
         SELECT 1 FROM experiment_assignment a LEFT JOIN eligibility_review e
           ON e.eligibility_review_id=a.eligibility_review_id
         WHERE e.eligibility_state<>'PASS'
            OR datetime(e.assessed_at)>datetime(a.assigned_at)
            OR e.unit_id<>a.unit_id OR e.decision_contract_id<>a.decision_contract_id)
            THEN 'PASS' ELSE 'FAIL' END,
       (SELECT count(*) FROM experiment_assignment a LEFT JOIN eligibility_review e
          ON e.eligibility_review_id=a.eligibility_review_id
        WHERE e.eligibility_state<>'PASS' OR datetime(e.assessed_at)>datetime(a.assigned_at)
           OR e.unit_id<>a.unit_id OR e.decision_contract_id<>a.decision_contract_id)
UNION ALL
SELECT 'DQ23','ONE_ASSIGNMENT_PER_UNIT_AND_CONTRACT',
       CASE WHEN NOT EXISTS (SELECT 1 FROM experiment_assignment
                             GROUP BY decision_contract_id,unit_id HAVING count(*)<>1)
            THEN 'PASS' ELSE 'FAIL' END,
       (SELECT count(*) FROM (SELECT decision_contract_id,unit_id
        FROM experiment_assignment GROUP BY decision_contract_id,unit_id HAVING count(*)<>1))
UNION ALL
SELECT 'DQ24','ASSIGNMENT_UNIT_ARM_CONTRACT_SCOPE',
       CASE WHEN NOT EXISTS (
         SELECT 1 FROM experiment_assignment a JOIN experiment_unit u USING (unit_id)
         JOIN arm_contract r USING (arm_id)
         WHERE u.decision_contract_id<>a.decision_contract_id
            OR r.decision_contract_id<>a.decision_contract_id)
            THEN 'PASS' ELSE 'FAIL' END,
       (SELECT count(*) FROM experiment_assignment a JOIN experiment_unit u USING (unit_id)
        JOIN arm_contract r USING (arm_id)
        WHERE u.decision_contract_id<>a.decision_contract_id
           OR r.decision_contract_id<>a.decision_contract_id)
UNION ALL
SELECT 'DQ25','ASSIGNMENTS_INSIDE_ENROLLMENT_WINDOW',
       CASE WHEN NOT EXISTS (
         SELECT 1 FROM experiment_assignment a JOIN decision_contract c USING (decision_contract_id)
         WHERE datetime(a.assigned_at)<datetime(c.enrollment_opens_at)
            OR datetime(a.assigned_at)>datetime(c.enrollment_closes_at))
            THEN 'PASS' ELSE 'FAIL' END,
       (SELECT count(*) FROM experiment_assignment a JOIN decision_contract c USING (decision_contract_id)
        WHERE datetime(a.assigned_at)<datetime(c.enrollment_opens_at)
           OR datetime(a.assigned_at)>datetime(c.enrollment_closes_at))
UNION ALL
SELECT 'DQ26','ALL_ASSIGNMENTS_RETAINED_IN_ITT',
       CASE WHEN NOT EXISTS (SELECT 1 FROM experiment_assignment
                             WHERE analysis_population_code<>'ITT_INCLUDED')
            THEN 'PASS' ELSE 'FAIL' END,
       (SELECT count(*) FROM experiment_assignment
        WHERE analysis_population_code<>'ITT_INCLUDED')
UNION ALL
SELECT 'DQ27','FIXTURES_REACH_PLANNED_MINIMUM_N',
       CASE WHEN NOT EXISTS (
         SELECT 1 FROM decision_contract c WHERE c.minimum_planned_units<>(
           SELECT count(*) FROM experiment_assignment a
           WHERE a.decision_contract_id=c.decision_contract_id))
            THEN 'PASS' ELSE 'FAIL' END,
       (SELECT count(*) FROM decision_contract c WHERE c.minimum_planned_units<>(
         SELECT count(*) FROM experiment_assignment a
         WHERE a.decision_contract_id=c.decision_contract_id))
UNION ALL
SELECT 'DQ28','EXCLUSIONS_ARE_PREASSIGNMENT_AND_UNASSIGNED',
       CASE WHEN NOT EXISTS (
         SELECT 1 FROM pre_assignment_exclusion x JOIN decision_contract c USING (decision_contract_id)
         WHERE datetime(x.excluded_at)>=datetime(c.enrollment_opens_at)
            OR EXISTS (SELECT 1 FROM experiment_assignment a
                       WHERE a.decision_contract_id=x.decision_contract_id
                         AND a.unit_id=x.unit_id))
            THEN 'PASS' ELSE 'FAIL' END,
       (SELECT count(*) FROM pre_assignment_exclusion x
        JOIN decision_contract c USING (decision_contract_id)
        WHERE datetime(x.excluded_at)>=datetime(c.enrollment_opens_at)
           OR EXISTS (SELECT 1 FROM experiment_assignment a
                      WHERE a.decision_contract_id=x.decision_contract_id
                        AND a.unit_id=x.unit_id))
UNION ALL
SELECT 'DQ29','SPINE_USES_METRIC_CURRENT_AT_ASSIGNMENT',
       CASE WHEN NOT EXISTS (
         SELECT 1 FROM expected_outcome_spine s
         JOIN experiment_assignment a USING (assignment_id)
         JOIN metric_contract_version m USING (metric_contract_id)
         WHERE datetime(m.registered_at)>datetime(a.assigned_at)
            OR EXISTS (SELECT 1 FROM metric_contract_version later
                       WHERE later.metric_stream_id=m.metric_stream_id
                         AND later.metric_version>m.metric_version
                         AND datetime(later.registered_at)<=datetime(a.assigned_at)))
            THEN 'PASS' ELSE 'FAIL' END,
       (SELECT count(*) FROM expected_outcome_spine s
        JOIN experiment_assignment a USING (assignment_id)
        JOIN metric_contract_version m USING (metric_contract_id)
        WHERE datetime(m.registered_at)>datetime(a.assigned_at)
           OR EXISTS (SELECT 1 FROM metric_contract_version later
                      WHERE later.metric_stream_id=m.metric_stream_id
                        AND later.metric_version>m.metric_version
                        AND datetime(later.registered_at)<=datetime(a.assigned_at)))
UNION ALL
SELECT 'DQ30','EACH_ASSIGNMENT_HAS_EXPECTED_OUTCOMES',
       CASE WHEN NOT EXISTS (
         SELECT 1 FROM experiment_assignment a WHERE NOT EXISTS (
           SELECT 1 FROM expected_outcome_spine s WHERE s.assignment_id=a.assignment_id))
            THEN 'PASS' ELSE 'FAIL' END,
       (SELECT count(*) FROM experiment_assignment a WHERE NOT EXISTS (
         SELECT 1 FROM expected_outcome_spine s WHERE s.assignment_id=a.assignment_id))
UNION ALL
SELECT 'DQ31','NO_EXPOSURE_BEFORE_ASSIGNMENT',
       CASE WHEN NOT EXISTS (
         SELECT 1 FROM exposure_event e JOIN experiment_assignment a USING (assignment_id)
         WHERE datetime(e.occurred_at)<datetime(a.assigned_at))
            THEN 'PASS' ELSE 'FAIL' END,
       (SELECT count(*) FROM exposure_event e JOIN experiment_assignment a USING (assignment_id)
        WHERE datetime(e.occurred_at)<datetime(a.assigned_at))
UNION ALL
SELECT 'DQ32','EXPOSURE_CONTRACT_SCOPE',
       CASE WHEN NOT EXISTS (
         SELECT 1 FROM exposure_event e JOIN experiment_assignment a USING (assignment_id)
         WHERE e.decision_contract_id<>a.decision_contract_id)
            THEN 'PASS' ELSE 'FAIL' END,
       (SELECT count(*) FROM exposure_event e JOIN experiment_assignment a USING (assignment_id)
        WHERE e.decision_contract_id<>a.decision_contract_id)
UNION ALL
SELECT 'DQ33','SWITCHBACK_PERIOD_EXPOSURES_MATCH_SCHEDULE',
       CASE WHEN NOT EXISTS (
         SELECT 1 FROM exposure_event e JOIN switchback_period_contract p USING (period_contract_id)
         JOIN experiment_assignment a USING (assignment_id)
         WHERE p.arm_id<>a.arm_id OR p.decision_contract_id<>e.decision_contract_id
            OR p.scheduled_intervention_version<>e.intervention_version
            OR datetime(e.occurred_at)<datetime(p.period_starts_at)
            OR datetime(e.occurred_at)>=datetime(p.period_ends_at))
            THEN 'PASS' ELSE 'FAIL' END,
       (SELECT count(*) FROM exposure_event e JOIN switchback_period_contract p USING (period_contract_id)
        JOIN experiment_assignment a USING (assignment_id)
        WHERE p.arm_id<>a.arm_id OR p.decision_contract_id<>e.decision_contract_id
           OR p.scheduled_intervention_version<>e.intervention_version
           OR datetime(e.occurred_at)<datetime(p.period_starts_at)
           OR datetime(e.occurred_at)>=datetime(p.period_ends_at))
UNION ALL
SELECT 'DQ34','TWO_SCHEDULED_SWITCHBACK_EXPOSURES_PER_UNIT',
       CASE WHEN NOT EXISTS (
         SELECT 1 FROM experiment_assignment a WHERE a.decision_contract_id='DC_SW_V1'
           AND (SELECT count(*) FROM exposure_event e WHERE e.assignment_id=a.assignment_id
                AND e.period_contract_id IS NOT NULL)<>2)
            THEN 'PASS' ELSE 'FAIL' END,
       (SELECT count(*) FROM experiment_assignment a WHERE a.decision_contract_id='DC_SW_V1'
          AND (SELECT count(*) FROM exposure_event e WHERE e.assignment_id=a.assignment_id
               AND e.period_contract_id IS NOT NULL)<>2)
UNION ALL
SELECT 'DQ35','SWITCHBACK_PERIODS_ORDERED_WITH_WASHOUT',
       CASE WHEN NOT EXISTS (
         SELECT 1 FROM switchback_period_contract p
         JOIN switchback_period_contract n ON n.arm_id=p.arm_id
          AND n.period_index=p.period_index+1
         WHERE datetime(p.period_ends_at)>datetime(n.period_starts_at)
            OR (julianday(n.period_starts_at)-julianday(p.period_ends_at))*1440
               <n.washout_minutes_before)
            THEN 'PASS' ELSE 'FAIL' END,
       (SELECT count(*) FROM switchback_period_contract p
        JOIN switchback_period_contract n ON n.arm_id=p.arm_id
         AND n.period_index=p.period_index+1
        WHERE datetime(p.period_ends_at)>datetime(n.period_starts_at)
           OR (julianday(n.period_starts_at)-julianday(p.period_ends_at))*1440
              <n.washout_minutes_before)
UNION ALL
SELECT 'DQ36','OBSERVATIONAL_UNEXPOSED_HAS_NO_NATURAL_EXPOSURE',
       CASE WHEN NOT EXISTS (
         SELECT 1 FROM experiment_assignment a JOIN arm_contract r USING (arm_id)
         JOIN exposure_event e USING (assignment_id)
         WHERE a.decision_contract_id='DC_OBS_V1' AND r.arm_role='REFERENCE'
           AND e.exposure_kind='OBSERVED_NATURAL_EXPOSURE')
            THEN 'PASS' ELSE 'FAIL' END,
       (SELECT count(*) FROM experiment_assignment a JOIN arm_contract r USING (arm_id)
        JOIN exposure_event e USING (assignment_id)
        WHERE a.decision_contract_id='DC_OBS_V1' AND r.arm_role='REFERENCE'
          AND e.exposure_kind='OBSERVED_NATURAL_EXPOSURE')
UNION ALL
SELECT 'DQ37','OBSERVATIONAL_EXPOSED_HAS_NATURAL_EXPOSURE',
       CASE WHEN NOT EXISTS (
         SELECT 1 FROM experiment_assignment a JOIN arm_contract r USING (arm_id)
         WHERE a.decision_contract_id='DC_OBS_V1' AND r.arm_role='COMPARATOR'
           AND NOT EXISTS (SELECT 1 FROM exposure_event e WHERE e.assignment_id=a.assignment_id
                           AND e.exposure_kind='OBSERVED_NATURAL_EXPOSURE'))
            THEN 'PASS' ELSE 'FAIL' END,
       (SELECT count(*) FROM experiment_assignment a JOIN arm_contract r USING (arm_id)
        WHERE a.decision_contract_id='DC_OBS_V1' AND r.arm_role='COMPARATOR'
          AND NOT EXISTS (SELECT 1 FROM exposure_event e WHERE e.assignment_id=a.assignment_id
                          AND e.exposure_kind='OBSERVED_NATURAL_EXPOSURE'))
UNION ALL
SELECT 'DQ38','CONTAMINATION_EVENT_CHRONOLOGY',
       CASE WHEN NOT EXISTS (
         SELECT 1 FROM contamination_event c JOIN experiment_assignment a USING (assignment_id)
         WHERE datetime(c.occurred_at)<datetime(a.assigned_at)
            OR datetime(c.occurred_at)>datetime(c.recorded_at))
            THEN 'PASS' ELSE 'FAIL' END,
       (SELECT count(*) FROM contamination_event c JOIN experiment_assignment a USING (assignment_id)
        WHERE datetime(c.occurred_at)<datetime(a.assigned_at)
           OR datetime(c.occurred_at)>datetime(c.recorded_at))
UNION ALL
SELECT 'DQ39','ONE_CURRENT_CONTAMINATION_EVENT_PER_STREAM',
       CASE WHEN (SELECT count(*) FROM v_current_contamination)
                    =(SELECT count(DISTINCT contamination_stream_id) FROM contamination_event)
            THEN 'PASS' ELSE 'FAIL' END,
       abs((SELECT count(*) FROM v_current_contamination)
           -(SELECT count(DISTINCT contamination_stream_id) FROM contamination_event))
UNION ALL
SELECT 'DQ40','OPEN_CONTAMINATION_HAS_INTEGRITY_BLOCKER',
       CASE WHEN NOT EXISTS (
         SELECT 1 FROM v_current_contamination c
         WHERE c.contamination_state IN ('OPEN','UNKNOWN') AND NOT EXISTS (
           SELECT 1 FROM v_current_integrity_issue i
           WHERE i.decision_contract_id=c.decision_contract_id
             AND i.issue_category_code='CONTAMINATION'
             AND i.issue_state IN ('OPEN','UNKNOWN')))
            THEN 'PASS' ELSE 'FAIL' END,
       (SELECT count(*) FROM v_current_contamination c
        WHERE c.contamination_state IN ('OPEN','UNKNOWN') AND NOT EXISTS (
          SELECT 1 FROM v_current_integrity_issue i
          WHERE i.decision_contract_id=c.decision_contract_id
            AND i.issue_category_code='CONTAMINATION'
            AND i.issue_state IN ('OPEN','UNKNOWN')))
UNION ALL
SELECT 'DQ41','EXPECTED_SPINE_CONTRACT_SCOPE',
       CASE WHEN NOT EXISTS (
         SELECT 1 FROM expected_outcome_spine s JOIN experiment_assignment a USING (assignment_id)
         JOIN metric_contract_version m USING (metric_contract_id)
         WHERE s.decision_contract_id<>a.decision_contract_id
            OR s.decision_contract_id<>m.decision_contract_id)
            THEN 'PASS' ELSE 'FAIL' END,
       (SELECT count(*) FROM expected_outcome_spine s JOIN experiment_assignment a USING (assignment_id)
        JOIN metric_contract_version m USING (metric_contract_id)
        WHERE s.decision_contract_id<>a.decision_contract_id
           OR s.decision_contract_id<>m.decision_contract_id)
UNION ALL
SELECT 'DQ42','EXPECTED_SPINE_TIME_ORDER',
       CASE WHEN NOT EXISTS (
         SELECT 1 FROM expected_outcome_spine s JOIN experiment_assignment a USING (assignment_id)
         WHERE datetime(s.registered_at)>datetime(s.outcome_window_opens_at)
            OR datetime(s.outcome_window_opens_at)<datetime(a.assigned_at)
            OR datetime(s.outcome_window_opens_at)>datetime(s.outcome_window_closes_at)
            OR datetime(s.outcome_window_closes_at)>datetime(s.expected_maturity_at))
            THEN 'PASS' ELSE 'FAIL' END,
       (SELECT count(*) FROM expected_outcome_spine s JOIN experiment_assignment a USING (assignment_id)
        WHERE datetime(s.registered_at)>datetime(s.outcome_window_opens_at)
           OR datetime(s.outcome_window_opens_at)<datetime(a.assigned_at)
           OR datetime(s.outcome_window_opens_at)>datetime(s.outcome_window_closes_at)
           OR datetime(s.outcome_window_closes_at)>datetime(s.expected_maturity_at))
UNION ALL
SELECT 'DQ43','ONE_EXPECTATION_PER_ASSIGNMENT_METRIC',
       CASE WHEN NOT EXISTS (SELECT 1 FROM expected_outcome_spine
                             GROUP BY assignment_id,metric_contract_id HAVING count(*)<>1)
            THEN 'PASS' ELSE 'FAIL' END,
       (SELECT count(*) FROM (SELECT assignment_id,metric_contract_id
        FROM expected_outcome_spine GROUP BY assignment_id,metric_contract_id HAVING count(*)<>1))
UNION ALL
SELECT 'DQ44','OBSERVED_OUTCOMES_INSIDE_REGISTERED_WINDOW',
       CASE WHEN NOT EXISTS (
         SELECT 1 FROM outcome_observation o JOIN expected_outcome_spine s USING (expected_outcome_id)
         JOIN experiment_assignment a USING (assignment_id)
         WHERE o.outcome_state='OBSERVED_VALUE'
           AND (datetime(o.outcome_occurred_at)<datetime(a.assigned_at)
             OR datetime(o.outcome_occurred_at)<datetime(s.outcome_window_opens_at)
             OR datetime(o.outcome_occurred_at)>datetime(s.outcome_window_closes_at)))
            THEN 'PASS' ELSE 'FAIL' END,
       (SELECT count(*) FROM outcome_observation o JOIN expected_outcome_spine s USING (expected_outcome_id)
        JOIN experiment_assignment a USING (assignment_id)
        WHERE o.outcome_state='OBSERVED_VALUE'
          AND (datetime(o.outcome_occurred_at)<datetime(a.assigned_at)
            OR datetime(o.outcome_occurred_at)<datetime(s.outcome_window_opens_at)
            OR datetime(o.outcome_occurred_at)>datetime(s.outcome_window_closes_at)))
UNION ALL
SELECT 'DQ45','OUTCOME_CLASSIFICATION_AFTER_MATURITY',
       CASE WHEN NOT EXISTS (
         SELECT 1 FROM outcome_observation o JOIN expected_outcome_spine s USING (expected_outcome_id)
         WHERE datetime(o.assessed_at)<datetime(s.expected_maturity_at))
            THEN 'PASS' ELSE 'FAIL' END,
       (SELECT count(*) FROM outcome_observation o JOIN expected_outcome_spine s USING (expected_outcome_id)
        WHERE datetime(o.assessed_at)<datetime(s.expected_maturity_at))
UNION ALL
SELECT 'DQ46','OUTCOME_STATE_VALUE_SHAPE',
       CASE WHEN NOT EXISTS (
         SELECT 1 FROM outcome_observation WHERE
           (outcome_state='OBSERVED_VALUE' AND (numeric_value IS NULL OR outcome_occurred_at IS NULL
                                                OR missing_reason_code IS NOT NULL))
           OR (outcome_state='MISSING_CONFIRMED' AND (numeric_value IS NOT NULL
                 OR outcome_occurred_at IS NOT NULL OR missing_reason_code IS NULL)))
            THEN 'PASS' ELSE 'FAIL' END,
       (SELECT count(*) FROM outcome_observation WHERE
          (outcome_state='OBSERVED_VALUE' AND (numeric_value IS NULL OR outcome_occurred_at IS NULL
                                               OR missing_reason_code IS NOT NULL))
          OR (outcome_state='MISSING_CONFIRMED' AND (numeric_value IS NOT NULL
                OR outcome_occurred_at IS NOT NULL OR missing_reason_code IS NULL)))
UNION ALL
SELECT 'DQ47','BINARY_OUTCOMES_ARE_ZERO_OR_ONE',
       CASE WHEN NOT EXISTS (
         SELECT 1 FROM outcome_observation o JOIN expected_outcome_spine s USING (expected_outcome_id)
         JOIN metric_contract_version m USING (metric_contract_id)
         WHERE o.outcome_state='OBSERVED_VALUE' AND m.value_kind='BINARY'
           AND o.numeric_value NOT IN (0.0,1.0))
            THEN 'PASS' ELSE 'FAIL' END,
       (SELECT count(*) FROM outcome_observation o JOIN expected_outcome_spine s USING (expected_outcome_id)
        JOIN metric_contract_version m USING (metric_contract_id)
        WHERE o.outcome_state='OBSERVED_VALUE' AND m.value_kind='BINARY'
          AND o.numeric_value NOT IN (0.0,1.0))
UNION ALL
SELECT 'DQ48','MISSING_OUTCOME_HAS_REASON_NOT_NUMBER',
       CASE WHEN NOT EXISTS (SELECT 1 FROM outcome_observation
                             WHERE outcome_state='MISSING_CONFIRMED'
                               AND (numeric_value IS NOT NULL OR missing_reason_code IS NULL))
            THEN 'PASS' ELSE 'FAIL' END,
       (SELECT count(*) FROM outcome_observation WHERE outcome_state='MISSING_CONFIRMED'
        AND (numeric_value IS NOT NULL OR missing_reason_code IS NULL))
UNION ALL
SELECT 'DQ49','ALL_MATURED_EXPECTATIONS_CLASSIFIED',
       CASE WHEN NOT EXISTS (
         SELECT 1 FROM expected_outcome_spine s CROSS JOIN model_setting ms
         WHERE ms.singleton_id=1
           AND datetime(s.expected_maturity_at)<=datetime(ms.default_snapshot_as_of)
           AND NOT EXISTS (SELECT 1 FROM outcome_observation o
                           WHERE o.expected_outcome_id=s.expected_outcome_id))
            THEN 'PASS' ELSE 'FAIL' END,
       (SELECT count(*) FROM expected_outcome_spine s CROSS JOIN model_setting ms
        WHERE ms.singleton_id=1
          AND datetime(s.expected_maturity_at)<=datetime(ms.default_snapshot_as_of)
          AND NOT EXISTS (SELECT 1 FROM outcome_observation o
                          WHERE o.expected_outcome_id=s.expected_outcome_id))
UNION ALL
SELECT 'DQ50','RAW_READOUT_ASSIGNED_N_RECONCILES',
       CASE WHEN NOT EXISTS (
         SELECT 1 FROM v_raw_itt_arm_readout r WHERE r.assigned_n<>(
           SELECT count(*) FROM experiment_assignment a
           WHERE a.decision_contract_id=r.decision_contract_id AND a.arm_id=r.arm_id))
            THEN 'PASS' ELSE 'FAIL' END,
       (SELECT count(*) FROM v_raw_itt_arm_readout r WHERE r.assigned_n<>(
         SELECT count(*) FROM experiment_assignment a
         WHERE a.decision_contract_id=r.decision_contract_id AND a.arm_id=r.arm_id))
UNION ALL
SELECT 'DQ51','RAW_READOUT_EXPECTED_N_RECONCILES',
       CASE WHEN NOT EXISTS (
         SELECT 1 FROM v_raw_itt_arm_readout r WHERE r.expected_n<>(
           SELECT count(*) FROM expected_outcome_spine s
           JOIN experiment_assignment a USING (assignment_id)
           WHERE s.decision_contract_id=r.decision_contract_id AND a.arm_id=r.arm_id
             AND s.metric_contract_id=r.metric_contract_id))
            THEN 'PASS' ELSE 'FAIL' END,
       (SELECT count(*) FROM v_raw_itt_arm_readout r WHERE r.expected_n<>(
         SELECT count(*) FROM expected_outcome_spine s JOIN experiment_assignment a USING (assignment_id)
         WHERE s.decision_contract_id=r.decision_contract_id AND a.arm_id=r.arm_id
           AND s.metric_contract_id=r.metric_contract_id))
UNION ALL
SELECT 'DQ52','OBSERVED_PLUS_MISSING_EQUALS_CLASSIFIED',
       CASE WHEN NOT EXISTS (SELECT 1 FROM v_raw_itt_arm_readout
                             WHERE observed_n+missing_n<>classified_n)
            THEN 'PASS' ELSE 'FAIL' END,
       (SELECT count(*) FROM v_raw_itt_arm_readout
        WHERE observed_n+missing_n<>classified_n)
UNION ALL
SELECT 'DQ53','MISSING_FORCES_NULL_RAW_ITT_MEAN',
       CASE WHEN NOT EXISTS (SELECT 1 FROM v_raw_itt_arm_readout
                             WHERE missing_n>0 AND raw_itt_mean IS NOT NULL)
            THEN 'PASS' ELSE 'FAIL' END,
       (SELECT count(*) FROM v_raw_itt_arm_readout
        WHERE missing_n>0 AND raw_itt_mean IS NOT NULL)
UNION ALL
SELECT 'DQ54','COMPLETE_READOUT_HAS_RAW_ITT_MEAN',
       CASE WHEN NOT EXISTS (SELECT 1 FROM v_raw_itt_arm_readout
                             WHERE readout_state='COMPLETE' AND raw_itt_mean IS NULL)
            THEN 'PASS' ELSE 'FAIL' END,
       (SELECT count(*) FROM v_raw_itt_arm_readout
        WHERE readout_state='COMPLETE' AND raw_itt_mean IS NULL)
UNION ALL
SELECT 'DQ55','OBSERVED_MEAN_USES_OBSERVED_DENOMINATOR',
       CASE WHEN NOT EXISTS (SELECT 1 FROM v_raw_itt_arm_readout
                             WHERE observed_n>0 AND abs(observed_mean-observed_sum/observed_n)>0.000000001)
            THEN 'PASS' ELSE 'FAIL' END,
       (SELECT count(*) FROM v_raw_itt_arm_readout
        WHERE observed_n>0 AND abs(observed_mean-observed_sum/observed_n)>0.000000001)
UNION ALL
SELECT 'DQ56','ALL_SEVEN_INTEGRITY_CATEGORIES_EXERCISED',
       CASE WHEN (SELECT count(DISTINCT issue_category_code) FROM integrity_issue_stream)=7
            THEN 'PASS' ELSE 'FAIL' END,
       7-(SELECT count(DISTINCT issue_category_code) FROM integrity_issue_stream)
UNION ALL
SELECT 'DQ57','INTEGRITY_EVENT_PREDECESSORS_EXACT',
       CASE WHEN NOT EXISTS (
         SELECT 1 FROM integrity_issue_event e WHERE
           (e.event_sequence=1 AND e.supersedes_integrity_issue_event_id IS NOT NULL)
           OR (e.event_sequence>1 AND NOT EXISTS (
             SELECT 1 FROM integrity_issue_event p
             WHERE p.integrity_issue_event_id=e.supersedes_integrity_issue_event_id
               AND p.integrity_issue_stream_id=e.integrity_issue_stream_id
               AND p.event_sequence=e.event_sequence-1)))
            THEN 'PASS' ELSE 'FAIL' END,
       (SELECT count(*) FROM integrity_issue_event e WHERE
          (e.event_sequence=1 AND e.supersedes_integrity_issue_event_id IS NOT NULL)
          OR (e.event_sequence>1 AND NOT EXISTS (
            SELECT 1 FROM integrity_issue_event p
            WHERE p.integrity_issue_event_id=e.supersedes_integrity_issue_event_id
              AND p.integrity_issue_stream_id=e.integrity_issue_stream_id
              AND p.event_sequence=e.event_sequence-1)))
UNION ALL
SELECT 'DQ58','INTEGRITY_EVENT_CHRONOLOGY',
       CASE WHEN NOT EXISTS (
         SELECT 1 FROM integrity_issue_event e JOIN integrity_issue_stream s USING (integrity_issue_stream_id)
         WHERE datetime(e.occurred_at)<datetime(s.detected_at)
            OR datetime(e.occurred_at)>datetime(e.recorded_at))
            THEN 'PASS' ELSE 'FAIL' END,
       (SELECT count(*) FROM integrity_issue_event e JOIN integrity_issue_stream s USING (integrity_issue_stream_id)
        WHERE datetime(e.occurred_at)<datetime(s.detected_at)
           OR datetime(e.occurred_at)>datetime(e.recorded_at))
UNION ALL
SELECT 'DQ59','ONE_CURRENT_INTEGRITY_EVENT_PER_STREAM',
       CASE WHEN (SELECT count(*) FROM v_current_integrity_issue)
                    =(SELECT count(*) FROM integrity_issue_stream)
            THEN 'PASS' ELSE 'FAIL' END,
       abs((SELECT count(*) FROM v_current_integrity_issue)
           -(SELECT count(*) FROM integrity_issue_stream))
UNION ALL
SELECT 'DQ60','PARALLEL_DUPLICATE_AND_TIME_RESOLVED',
       CASE WHEN (SELECT count(*) FROM v_current_integrity_issue
                  WHERE decision_contract_id='DC_RP_V1'
                    AND issue_category_code IN ('DUPLICATE','TIME')
                    AND issue_state='RESOLVED')=2
            THEN 'PASS' ELSE 'FAIL' END,
       abs((SELECT count(*) FROM v_current_integrity_issue
            WHERE decision_contract_id='DC_RP_V1'
              AND issue_category_code IN ('DUPLICATE','TIME')
              AND issue_state='RESOLVED')-2)
UNION ALL
SELECT 'DQ61','AA_SRM_REMAINS_OPEN',
       CASE WHEN EXISTS (SELECT 1 FROM v_current_integrity_issue
                         WHERE decision_contract_id='DC_AA_V1'
                           AND issue_category_code='SRM' AND issue_state='OPEN')
            THEN 'PASS' ELSE 'FAIL' END,
       CASE WHEN EXISTS (SELECT 1 FROM v_current_integrity_issue
                         WHERE decision_contract_id='DC_AA_V1'
                           AND issue_category_code='SRM' AND issue_state='OPEN')
            THEN 0 ELSE 1 END
UNION ALL
SELECT 'DQ62','AA_COVERAGE_REMAINS_OPEN',
       CASE WHEN EXISTS (SELECT 1 FROM v_current_integrity_issue
                         WHERE decision_contract_id='DC_AA_V1'
                           AND issue_category_code='COVERAGE' AND issue_state='OPEN')
            THEN 'PASS' ELSE 'FAIL' END,
       CASE WHEN EXISTS (SELECT 1 FROM v_current_integrity_issue
                         WHERE decision_contract_id='DC_AA_V1'
                           AND issue_category_code='COVERAGE' AND issue_state='OPEN')
            THEN 0 ELSE 1 END
UNION ALL
SELECT 'DQ63','SWITCHBACK_CARRYOVER_REMAINS_UNKNOWN',
       CASE WHEN EXISTS (SELECT 1 FROM v_current_integrity_issue
                         WHERE decision_contract_id='DC_SW_V1'
                           AND issue_category_code='CARRYOVER' AND issue_state='UNKNOWN')
            THEN 'PASS' ELSE 'FAIL' END,
       CASE WHEN EXISTS (SELECT 1 FROM v_current_integrity_issue
                         WHERE decision_contract_id='DC_SW_V1'
                           AND issue_category_code='CARRYOVER' AND issue_state='UNKNOWN')
            THEN 0 ELSE 1 END
UNION ALL
SELECT 'DQ64','SWITCHBACK_CONTAMINATION_REMAINS_OPEN',
       CASE WHEN EXISTS (SELECT 1 FROM v_current_integrity_issue
                         WHERE decision_contract_id='DC_SW_V1'
                           AND issue_category_code='CONTAMINATION' AND issue_state='OPEN')
                  AND EXISTS (SELECT 1 FROM v_current_contamination
                              WHERE decision_contract_id='DC_SW_V1'
                                AND contamination_state='OPEN')
            THEN 'PASS' ELSE 'FAIL' END,
       CASE WHEN EXISTS (SELECT 1 FROM v_current_integrity_issue
                         WHERE decision_contract_id='DC_SW_V1'
                           AND issue_category_code='CONTAMINATION' AND issue_state='OPEN')
                  AND EXISTS (SELECT 1 FROM v_current_contamination
                              WHERE decision_contract_id='DC_SW_V1'
                                AND contamination_state='OPEN')
            THEN 0 ELSE 1 END
UNION ALL
SELECT 'DQ65','SWITCHBACK_INTERFERENCE_REMAINS_OPEN',
       CASE WHEN EXISTS (SELECT 1 FROM v_current_integrity_issue
                         WHERE decision_contract_id='DC_SW_V1'
                           AND issue_category_code='INTERFERENCE' AND issue_state='OPEN')
            THEN 'PASS' ELSE 'FAIL' END,
       CASE WHEN EXISTS (SELECT 1 FROM v_current_integrity_issue
                         WHERE decision_contract_id='DC_SW_V1'
                           AND issue_category_code='INTERFERENCE' AND issue_state='OPEN')
            THEN 0 ELSE 1 END
UNION ALL
SELECT 'DQ66','ISSUE_STREAM_BLOCK_FLAGS_MATCH_CATEGORY_DEFAULTS',
       CASE WHEN NOT EXISTS (
         SELECT 1 FROM integrity_issue_stream s JOIN issue_category_dim d USING (issue_category_code)
         WHERE s.blocks_causal_claim<>d.default_blocks_causal
            OR s.blocks_expand<>d.default_blocks_expand)
            THEN 'PASS' ELSE 'FAIL' END,
       (SELECT count(*) FROM integrity_issue_stream s JOIN issue_category_dim d USING (issue_category_code)
        WHERE s.blocks_causal_claim<>d.default_blocks_causal
           OR s.blocks_expand<>d.default_blocks_expand)
UNION ALL
SELECT 'DQ67','ABNORMAL_EXPOSURES_HAVE_MATCHING_OPEN_ISSUES',
       CASE WHEN NOT EXISTS (
         SELECT 1 FROM exposure_event e WHERE e.exposure_kind IN ('CROSS_ARM_EXPOSURE','INTERFERENCE_EXPOSURE')
           AND NOT EXISTS (SELECT 1 FROM v_current_integrity_issue i
             WHERE i.decision_contract_id=e.decision_contract_id
               AND i.issue_state IN ('OPEN','UNKNOWN')
               AND i.issue_category_code=CASE e.exposure_kind
                 WHEN 'CROSS_ARM_EXPOSURE' THEN 'CONTAMINATION' ELSE 'INTERFERENCE' END))
            THEN 'PASS' ELSE 'FAIL' END,
       (SELECT count(*) FROM exposure_event e
        WHERE e.exposure_kind IN ('CROSS_ARM_EXPOSURE','INTERFERENCE_EXPOSURE')
          AND NOT EXISTS (SELECT 1 FROM v_current_integrity_issue i
            WHERE i.decision_contract_id=e.decision_contract_id
              AND i.issue_state IN ('OPEN','UNKNOWN')
              AND i.issue_category_code=CASE e.exposure_kind
                WHEN 'CROSS_ARM_EXPOSURE' THEN 'CONTAMINATION' ELSE 'INTERFERENCE' END))
UNION ALL
SELECT 'DQ68','FIXTURE_OPEN_ISSUE_COUNTS_EXACT',
       CASE WHEN (SELECT count(*) FROM v_current_integrity_issue
                  WHERE decision_contract_id='DC_RP_V1' AND issue_state IN ('OPEN','UNKNOWN'))=0
                  AND (SELECT count(*) FROM v_current_integrity_issue
                       WHERE decision_contract_id='DC_AA_V1' AND issue_state IN ('OPEN','UNKNOWN'))=2
                  AND (SELECT count(*) FROM v_current_integrity_issue
                       WHERE decision_contract_id='DC_SW_V1' AND issue_state IN ('OPEN','UNKNOWN'))=3
                  AND (SELECT count(*) FROM v_current_integrity_issue
                       WHERE decision_contract_id='DC_OBS_V1' AND issue_state IN ('OPEN','UNKNOWN'))=0
            THEN 'PASS' ELSE 'FAIL' END,
       CASE WHEN (SELECT count(*) FROM v_current_integrity_issue
                  WHERE decision_contract_id='DC_RP_V1' AND issue_state IN ('OPEN','UNKNOWN'))=0
                  AND (SELECT count(*) FROM v_current_integrity_issue
                       WHERE decision_contract_id='DC_AA_V1' AND issue_state IN ('OPEN','UNKNOWN'))=2
                  AND (SELECT count(*) FROM v_current_integrity_issue
                       WHERE decision_contract_id='DC_SW_V1' AND issue_state IN ('OPEN','UNKNOWN'))=3
                  AND (SELECT count(*) FROM v_current_integrity_issue
                       WHERE decision_contract_id='DC_OBS_V1' AND issue_state IN ('OPEN','UNKNOWN'))=0
            THEN 0 ELSE 1 END
UNION ALL
SELECT 'DQ69','COST_EVENT_VALUE_SHAPE',
       CASE WHEN NOT EXISTS (
         SELECT 1 FROM experiment_cost_event WHERE
           (cost_type_code='CASH' AND (cash_cost_micros IS NULL OR founder_minutes IS NOT NULL))
           OR (cost_type_code='FOUNDER_TIME' AND (founder_minutes IS NULL OR cash_cost_micros IS NOT NULL)))
            THEN 'PASS' ELSE 'FAIL' END,
       (SELECT count(*) FROM experiment_cost_event WHERE
          (cost_type_code='CASH' AND (cash_cost_micros IS NULL OR founder_minutes IS NOT NULL))
          OR (cost_type_code='FOUNDER_TIME' AND (founder_minutes IS NULL OR cash_cost_micros IS NOT NULL)))
UNION ALL
SELECT 'DQ70','COST_EVENT_CHRONOLOGY',
       CASE WHEN NOT EXISTS (SELECT 1 FROM experiment_cost_event
                             WHERE datetime(occurred_at)>datetime(recorded_at))
            THEN 'PASS' ELSE 'FAIL' END,
       (SELECT count(*) FROM experiment_cost_event
        WHERE datetime(occurred_at)>datetime(recorded_at))
UNION ALL
SELECT 'DQ71','COST_CURRENCY_MATCHES_BASE',
       CASE WHEN NOT EXISTS (SELECT 1 FROM experiment_cost_event
                             WHERE currency<>(SELECT base_currency FROM model_setting WHERE singleton_id=1))
            THEN 'PASS' ELSE 'FAIL' END,
       (SELECT count(*) FROM experiment_cost_event
        WHERE currency<>(SELECT base_currency FROM model_setting WHERE singleton_id=1))
UNION ALL
SELECT 'DQ72','CASH_COST_WITHIN_CONTRACT_CAP',
       CASE WHEN NOT EXISTS (
         SELECT 1 FROM v_experiment_cost_readout r JOIN decision_contract c USING (decision_contract_id)
         WHERE r.cash_cost_micros>c.max_cash_cost_micros)
            THEN 'PASS' ELSE 'FAIL' END,
       (SELECT count(*) FROM v_experiment_cost_readout r JOIN decision_contract c USING (decision_contract_id)
        WHERE r.cash_cost_micros>c.max_cash_cost_micros)
UNION ALL
SELECT 'DQ73','FOUNDER_MINUTES_WITHIN_CONTRACT_CAP',
       CASE WHEN NOT EXISTS (
         SELECT 1 FROM v_experiment_cost_readout r JOIN decision_contract c USING (decision_contract_id)
         WHERE r.founder_minutes>c.max_founder_minutes)
            THEN 'PASS' ELSE 'FAIL' END,
       (SELECT count(*) FROM v_experiment_cost_readout r JOIN decision_contract c USING (decision_contract_id)
        WHERE r.founder_minutes>c.max_founder_minutes)
UNION ALL
SELECT 'DQ74','SAFETY_REVIEW_RECONCILES_TO_REVIEW_TIME_GUARDRAILS',
       CASE WHEN NOT EXISTS (
         SELECT 1 FROM safety_review s WHERE
           s.guardrail_expected_n<>(
             SELECT count(*) FROM expected_outcome_spine x
             JOIN metric_contract_version m USING (metric_contract_id)
             WHERE x.decision_contract_id=s.decision_contract_id
               AND m.metric_role='GUARDRAIL'
               AND datetime(x.registered_at)<=datetime(s.reviewed_at))
           OR s.guardrail_observed_n<>(
             SELECT count(*) FROM outcome_observation o
             JOIN expected_outcome_spine x USING (expected_outcome_id)
             JOIN metric_contract_version m USING (metric_contract_id)
             WHERE x.decision_contract_id=s.decision_contract_id
               AND m.metric_role='GUARDRAIL'
               AND o.outcome_state='OBSERVED_VALUE'
               AND datetime(x.registered_at)<=datetime(s.reviewed_at)
               AND datetime(o.assessed_at)<=datetime(s.reviewed_at)
               AND datetime(o.recorded_at)<=datetime(s.reviewed_at))
           OR s.guardrail_missing_n<>(
             SELECT count(*) FROM outcome_observation o
             JOIN expected_outcome_spine x USING (expected_outcome_id)
             JOIN metric_contract_version m USING (metric_contract_id)
             WHERE x.decision_contract_id=s.decision_contract_id
               AND m.metric_role='GUARDRAIL'
               AND o.outcome_state='MISSING_CONFIRMED'
               AND datetime(x.registered_at)<=datetime(s.reviewed_at)
               AND datetime(o.assessed_at)<=datetime(s.reviewed_at)
               AND datetime(o.recorded_at)<=datetime(s.reviewed_at))
           OR (s.safety_state='NA')<>(s.guardrail_expected_n=0)
           OR (s.safety_state='PASS'
               AND (s.guardrail_observed_n<>s.guardrail_expected_n
                 OR s.guardrail_missing_n<>0)))
            THEN 'PASS' ELSE 'FAIL' END,
       (SELECT count(*) FROM safety_review s WHERE
          s.guardrail_expected_n<>(
            SELECT count(*) FROM expected_outcome_spine x
            JOIN metric_contract_version m USING (metric_contract_id)
            WHERE x.decision_contract_id=s.decision_contract_id
              AND m.metric_role='GUARDRAIL'
              AND datetime(x.registered_at)<=datetime(s.reviewed_at))
          OR s.guardrail_observed_n<>(
            SELECT count(*) FROM outcome_observation o
            JOIN expected_outcome_spine x USING (expected_outcome_id)
            JOIN metric_contract_version m USING (metric_contract_id)
            WHERE x.decision_contract_id=s.decision_contract_id
              AND m.metric_role='GUARDRAIL'
              AND o.outcome_state='OBSERVED_VALUE'
              AND datetime(x.registered_at)<=datetime(s.reviewed_at)
              AND datetime(o.assessed_at)<=datetime(s.reviewed_at)
              AND datetime(o.recorded_at)<=datetime(s.reviewed_at))
          OR s.guardrail_missing_n<>(
            SELECT count(*) FROM outcome_observation o
            JOIN expected_outcome_spine x USING (expected_outcome_id)
            JOIN metric_contract_version m USING (metric_contract_id)
            WHERE x.decision_contract_id=s.decision_contract_id
              AND m.metric_role='GUARDRAIL'
              AND o.outcome_state='MISSING_CONFIRMED'
              AND datetime(x.registered_at)<=datetime(s.reviewed_at)
              AND datetime(o.assessed_at)<=datetime(s.reviewed_at)
              AND datetime(o.recorded_at)<=datetime(s.reviewed_at))
          OR (s.safety_state='NA')<>(s.guardrail_expected_n=0)
          OR (s.safety_state='PASS'
              AND (s.guardrail_observed_n<>s.guardrail_expected_n
                OR s.guardrail_missing_n<>0)))
UNION ALL
SELECT 'DQ75','EXPAND_HAS_PASSING_SAFETY',
       CASE WHEN NOT EXISTS (
         SELECT 1 FROM decision_snapshot d JOIN analysis_snapshot a USING (analysis_snapshot_id)
         WHERE d.decision_state='EXPAND' AND a.safety_gate<>'PASS')
            THEN 'PASS' ELSE 'FAIL' END,
       (SELECT count(*) FROM decision_snapshot d JOIN analysis_snapshot a USING (analysis_snapshot_id)
        WHERE d.decision_state='EXPAND' AND a.safety_gate<>'PASS')
UNION ALL
SELECT 'DQ76','ANALYSIS_SNAPSHOT_TIME_ORDER',
       CASE WHEN NOT EXISTS (SELECT 1 FROM analysis_snapshot
                             WHERE datetime(evidence_cutoff_at)>datetime(snapshot_as_of)
                                OR datetime(snapshot_as_of)>datetime(created_at))
            THEN 'PASS' ELSE 'FAIL' END,
       (SELECT count(*) FROM analysis_snapshot
        WHERE datetime(evidence_cutoff_at)>datetime(snapshot_as_of)
           OR datetime(snapshot_as_of)>datetime(created_at))
UNION ALL
SELECT 'DQ77','ANALYSIS_SNAPSHOT_COUNTS_RECONCILE',
       CASE WHEN NOT EXISTS (
         SELECT 1 FROM analysis_snapshot a WHERE
           a.assigned_n<>(SELECT count(*) FROM experiment_assignment x
                           WHERE x.decision_contract_id=a.decision_contract_id
                             AND datetime(x.recorded_at)<=datetime(a.evidence_cutoff_at))
           OR a.expected_outcome_n<>(SELECT count(*) FROM expected_outcome_spine x
                           WHERE x.decision_contract_id=a.decision_contract_id
                             AND datetime(x.registered_at)<=datetime(a.evidence_cutoff_at))
           OR a.classified_outcome_n<>(SELECT count(*) FROM outcome_observation o
                           JOIN expected_outcome_spine x USING (expected_outcome_id)
                           WHERE x.decision_contract_id=a.decision_contract_id
                             AND datetime(o.recorded_at)<=datetime(a.evidence_cutoff_at)))
            THEN 'PASS' ELSE 'FAIL' END,
       (SELECT count(*) FROM analysis_snapshot a WHERE
          a.assigned_n<>(SELECT count(*) FROM experiment_assignment x
                          WHERE x.decision_contract_id=a.decision_contract_id
                            AND datetime(x.recorded_at)<=datetime(a.evidence_cutoff_at))
          OR a.expected_outcome_n<>(SELECT count(*) FROM expected_outcome_spine x
                          WHERE x.decision_contract_id=a.decision_contract_id
                            AND datetime(x.registered_at)<=datetime(a.evidence_cutoff_at))
          OR a.classified_outcome_n<>(SELECT count(*) FROM outcome_observation o
                          JOIN expected_outcome_spine x USING (expected_outcome_id)
                          WHERE x.decision_contract_id=a.decision_contract_id
                            AND datetime(o.recorded_at)<=datetime(a.evidence_cutoff_at)))
UNION ALL
SELECT 'DQ78','ANALYSIS_CLAIM_WITHIN_CONTRACT_CAP',
       CASE WHEN NOT EXISTS (
         SELECT 1 FROM analysis_snapshot a JOIN decision_contract c USING (decision_contract_id)
         JOIN claim_level_dim actual ON actual.claim_level_code=a.analysis_claim_cap_code
         JOIN claim_level_dim cap ON cap.claim_level_code=c.analysis_claim_cap_code
         WHERE actual.claim_rank>cap.claim_rank)
            THEN 'PASS' ELSE 'FAIL' END,
       (SELECT count(*) FROM analysis_snapshot a JOIN decision_contract c USING (decision_contract_id)
        JOIN claim_level_dim actual ON actual.claim_level_code=a.analysis_claim_cap_code
        JOIN claim_level_dim cap ON cap.claim_level_code=c.analysis_claim_cap_code
        WHERE actual.claim_rank>cap.claim_rank)
UNION ALL
SELECT 'DQ79','CAUSAL_ANALYSIS_HAS_NO_BLOCKING_CAUSAL_ISSUE',
       CASE WHEN NOT EXISTS (SELECT 1 FROM analysis_snapshot
                             WHERE analysis_claim_cap_code='CAUSAL_ITT_WITHIN_CONTRACT'
                               AND blocking_causal_issue_n<>0)
            THEN 'PASS' ELSE 'FAIL' END,
       (SELECT count(*) FROM analysis_snapshot
        WHERE analysis_claim_cap_code='CAUSAL_ITT_WITHIN_CONTRACT'
          AND blocking_causal_issue_n<>0)
UNION ALL
SELECT 'DQ80','UNKNOWN_SWITCHBACK_CARRYOVER_CAPS_CAUSAL_CLAIM',
       CASE WHEN NOT EXISTS (
         SELECT 1 FROM analysis_snapshot a JOIN decision_contract c USING (decision_contract_id)
         WHERE c.design_type_code='RANDOMIZED_SWITCHBACK' AND a.carryover_state='UNKNOWN'
           AND a.analysis_claim_cap_code='CAUSAL_ITT_WITHIN_CONTRACT')
            THEN 'PASS' ELSE 'FAIL' END,
       (SELECT count(*) FROM analysis_snapshot a JOIN decision_contract c USING (decision_contract_id)
        WHERE c.design_type_code='RANDOMIZED_SWITCHBACK' AND a.carryover_state='UNKNOWN'
          AND a.analysis_claim_cap_code='CAUSAL_ITT_WITHIN_CONTRACT')
UNION ALL
SELECT 'DQ81','IMMUTABLE_READOUT_MATCHES_OWNING_SNAPSHOT',
       CASE WHEN NOT EXISTS (
         SELECT 1 FROM analysis_arm_readout a
         LEFT JOIN v_snapshot_itt_arm_readout r
           ON r.analysis_snapshot_id=a.analysis_snapshot_id AND r.arm_id=a.arm_id
          AND r.metric_contract_id=a.metric_contract_id
         WHERE r.arm_id IS NULL OR a.assigned_n<>r.assigned_n OR a.expected_n<>r.expected_n
            OR a.matured_expected_n<>r.matured_expected_n OR a.classified_n<>r.classified_n
            OR a.observed_n<>r.observed_n OR a.missing_n<>r.missing_n
            OR a.observed_sum IS NOT r.observed_sum OR a.observed_mean IS NOT r.observed_mean
            OR a.raw_itt_mean IS NOT r.raw_itt_mean OR a.readout_state<>r.readout_state)
            THEN 'PASS' ELSE 'FAIL' END,
       (SELECT count(*) FROM analysis_arm_readout a
        LEFT JOIN v_snapshot_itt_arm_readout r
          ON r.analysis_snapshot_id=a.analysis_snapshot_id AND r.arm_id=a.arm_id
         AND r.metric_contract_id=a.metric_contract_id
        WHERE r.arm_id IS NULL OR a.assigned_n<>r.assigned_n OR a.expected_n<>r.expected_n
           OR a.matured_expected_n<>r.matured_expected_n OR a.classified_n<>r.classified_n
           OR a.observed_n<>r.observed_n OR a.missing_n<>r.missing_n
           OR a.observed_sum IS NOT r.observed_sum OR a.observed_mean IS NOT r.observed_mean
           OR a.raw_itt_mean IS NOT r.raw_itt_mean OR a.readout_state<>r.readout_state)
UNION ALL
SELECT 'DQ82','SNAPSHOT_MISSING_IS_NOT_ZERO_IMPUTED',
       CASE WHEN NOT EXISTS (SELECT 1 FROM analysis_arm_readout
                             WHERE missing_n>0 AND raw_itt_mean IS NOT NULL)
            THEN 'PASS' ELSE 'FAIL' END,
       (SELECT count(*) FROM analysis_arm_readout WHERE missing_n>0 AND raw_itt_mean IS NOT NULL)
UNION ALL
SELECT 'DQ83','PAIR_DIFFERENCE_FORMULAS_RECONCILE',
       CASE WHEN NOT EXISTS (
         SELECT 1 FROM v_analysis_pair_readout
         WHERE observed_mean_difference IS NOT (comparator_observed_mean-reference_observed_mean)
            OR raw_itt_mean_difference IS NOT (comparator_raw_itt_mean-reference_raw_itt_mean))
            THEN 'PASS' ELSE 'FAIL' END,
       (SELECT count(*) FROM v_analysis_pair_readout
        WHERE observed_mean_difference IS NOT (comparator_observed_mean-reference_observed_mean)
           OR raw_itt_mean_difference IS NOT (comparator_raw_itt_mean-reference_raw_itt_mean))
UNION ALL
SELECT 'DQ84','DECISION_CLAIM_WITHIN_ANALYSIS_CAP',
       CASE WHEN NOT EXISTS (
         SELECT 1 FROM decision_snapshot d JOIN analysis_snapshot a USING (analysis_snapshot_id)
         JOIN claim_level_dim actual ON actual.claim_level_code=d.claimed_interpretation_code
         JOIN claim_level_dim cap ON cap.claim_level_code=a.analysis_claim_cap_code
         WHERE actual.claim_rank>cap.claim_rank)
            THEN 'PASS' ELSE 'FAIL' END,
       (SELECT count(*) FROM decision_snapshot d JOIN analysis_snapshot a USING (analysis_snapshot_id)
        JOIN claim_level_dim actual ON actual.claim_level_code=d.claimed_interpretation_code
        JOIN claim_level_dim cap ON cap.claim_level_code=a.analysis_claim_cap_code
        WHERE actual.claim_rank>cap.claim_rank)
UNION ALL
SELECT 'DQ85','EXPAND_GATES_COMPLETE_CLEAN_SAFE_AND_COMPLETE_ITT',
       CASE WHEN NOT EXISTS (
         SELECT 1 FROM decision_snapshot d JOIN analysis_snapshot a USING (analysis_snapshot_id)
         WHERE d.decision_state='EXPAND' AND (
           a.maturity_gate<>'COMPLETE' OR a.open_integrity_issue_n<>0
           OR a.blocking_expand_issue_n<>0 OR a.safety_gate<>'PASS'
           OR EXISTS (SELECT 1 FROM analysis_arm_readout r
                      JOIN metric_contract_version m USING (metric_contract_id)
                      WHERE r.analysis_snapshot_id=a.analysis_snapshot_id
                        AND m.metric_role IN ('PRIMARY','GUARDRAIL')
                        AND (r.readout_state<>'COMPLETE' OR r.raw_itt_mean IS NULL))
           OR EXISTS (
             SELECT 1 FROM arm_contract arm
             JOIN metric_contract_version m
               ON m.decision_contract_id=arm.decision_contract_id
             WHERE arm.decision_contract_id=a.decision_contract_id
               AND datetime(arm.arm_registered_at)<=datetime((
                 SELECT max(assigned_at) FROM experiment_assignment
                 WHERE decision_contract_id=a.decision_contract_id))
               AND m.metric_role IN ('PRIMARY','GUARDRAIL')
               AND datetime(m.registered_at)<=datetime((
                 SELECT max(assigned_at) FROM experiment_assignment
                 WHERE decision_contract_id=a.decision_contract_id))
               AND NOT EXISTS (
                 SELECT 1 FROM metric_contract_version later
                 WHERE later.metric_stream_id=m.metric_stream_id
                   AND later.metric_version>m.metric_version
                   AND datetime(later.registered_at)<=datetime((
                     SELECT max(assigned_at) FROM experiment_assignment
                     WHERE decision_contract_id=a.decision_contract_id)))
               AND NOT EXISTS (
                 SELECT 1 FROM analysis_arm_readout r
                 WHERE r.analysis_snapshot_id=a.analysis_snapshot_id
                   AND r.arm_id=arm.arm_id
                   AND r.metric_contract_id=m.metric_contract_id
                   AND r.readout_state='COMPLETE'
                   AND r.raw_itt_mean IS NOT NULL))))
            THEN 'PASS' ELSE 'FAIL' END,
       (SELECT count(*) FROM decision_snapshot d JOIN analysis_snapshot a USING (analysis_snapshot_id)
        WHERE d.decision_state='EXPAND' AND (
          a.maturity_gate<>'COMPLETE' OR a.open_integrity_issue_n<>0
          OR a.blocking_expand_issue_n<>0 OR a.safety_gate<>'PASS'
          OR EXISTS (SELECT 1 FROM analysis_arm_readout r
                     JOIN metric_contract_version m USING (metric_contract_id)
                     WHERE r.analysis_snapshot_id=a.analysis_snapshot_id
                       AND m.metric_role IN ('PRIMARY','GUARDRAIL')
                       AND (r.readout_state<>'COMPLETE' OR r.raw_itt_mean IS NULL))
          OR EXISTS (
            SELECT 1 FROM arm_contract arm
            JOIN metric_contract_version m
              ON m.decision_contract_id=arm.decision_contract_id
            WHERE arm.decision_contract_id=a.decision_contract_id
              AND datetime(arm.arm_registered_at)<=datetime((
                SELECT max(assigned_at) FROM experiment_assignment
                WHERE decision_contract_id=a.decision_contract_id))
              AND m.metric_role IN ('PRIMARY','GUARDRAIL')
              AND datetime(m.registered_at)<=datetime((
                SELECT max(assigned_at) FROM experiment_assignment
                WHERE decision_contract_id=a.decision_contract_id))
              AND NOT EXISTS (
                SELECT 1 FROM metric_contract_version later
                WHERE later.metric_stream_id=m.metric_stream_id
                  AND later.metric_version>m.metric_version
                  AND datetime(later.registered_at)<=datetime((
                    SELECT max(assigned_at) FROM experiment_assignment
                    WHERE decision_contract_id=a.decision_contract_id)))
              AND NOT EXISTS (
                SELECT 1 FROM analysis_arm_readout r
                WHERE r.analysis_snapshot_id=a.analysis_snapshot_id
                  AND r.arm_id=arm.arm_id
                  AND r.metric_contract_id=m.metric_contract_id
                  AND r.readout_state='COMPLETE'
                  AND r.raw_itt_mean IS NOT NULL))))
UNION ALL
SELECT 'DQ86','PARALLEL_FIXTURE_RAW_ITT_AND_EXPAND_EXACT',
       CASE WHEN EXISTS (
         SELECT 1 FROM v_analysis_pair_readout p JOIN decision_snapshot d USING (analysis_snapshot_id)
         WHERE p.analysis_snapshot_id='AN_RP_1' AND p.metric_contract_id='MET_RP_PRI_V2'
           AND abs(p.reference_raw_itt_mean-0.5)<0.000000001
           AND abs(p.comparator_raw_itt_mean-0.75)<0.000000001
           AND abs(p.raw_itt_mean_difference-0.25)<0.000000001
           AND d.decision_state='EXPAND') THEN 'PASS' ELSE 'FAIL' END,
       CASE WHEN EXISTS (
         SELECT 1 FROM v_analysis_pair_readout p JOIN decision_snapshot d USING (analysis_snapshot_id)
         WHERE p.analysis_snapshot_id='AN_RP_1' AND p.metric_contract_id='MET_RP_PRI_V2'
           AND abs(p.reference_raw_itt_mean-0.5)<0.000000001
           AND abs(p.comparator_raw_itt_mean-0.75)<0.000000001
           AND abs(p.raw_itt_mean_difference-0.25)<0.000000001
           AND d.decision_state='EXPAND') THEN 0 ELSE 1 END
UNION ALL
SELECT 'DQ87','AA_FIXTURE_SRM_MISSING_AND_CHANGE_EXACT',
       CASE WHEN EXISTS (
         SELECT 1 FROM v_analysis_pair_readout p JOIN decision_snapshot d USING (analysis_snapshot_id)
         WHERE p.analysis_snapshot_id='AN_AA_1' AND p.reference_assigned_n=3
           AND p.comparator_assigned_n=1 AND p.reference_missing_n=1
           AND p.reference_raw_itt_mean IS NULL AND d.decision_state='CHANGE')
            THEN 'PASS' ELSE 'FAIL' END,
       CASE WHEN EXISTS (
         SELECT 1 FROM v_analysis_pair_readout p JOIN decision_snapshot d USING (analysis_snapshot_id)
         WHERE p.analysis_snapshot_id='AN_AA_1' AND p.reference_assigned_n=3
           AND p.comparator_assigned_n=1 AND p.reference_missing_n=1
           AND p.reference_raw_itt_mean IS NULL AND d.decision_state='CHANGE')
            THEN 0 ELSE 1 END
UNION ALL
SELECT 'DQ88','SWITCHBACK_FIXTURE_DIFF_CARRYOVER_AND_PAUSE_EXACT',
       CASE WHEN EXISTS (
         SELECT 1 FROM v_analysis_pair_readout p JOIN analysis_snapshot a USING (analysis_snapshot_id)
         JOIN decision_snapshot d USING (analysis_snapshot_id)
         WHERE p.analysis_snapshot_id='AN_SW_1' AND p.metric_contract_id='MET_SW_PRI_V1'
           AND abs(p.raw_itt_mean_difference-(-6.0))<0.000000001
           AND a.carryover_state='UNKNOWN' AND d.decision_state='PAUSE')
            THEN 'PASS' ELSE 'FAIL' END,
       CASE WHEN EXISTS (
         SELECT 1 FROM v_analysis_pair_readout p JOIN analysis_snapshot a USING (analysis_snapshot_id)
         JOIN decision_snapshot d USING (analysis_snapshot_id)
         WHERE p.analysis_snapshot_id='AN_SW_1' AND p.metric_contract_id='MET_SW_PRI_V1'
           AND abs(p.raw_itt_mean_difference-(-6.0))<0.000000001
           AND a.carryover_state='UNKNOWN' AND d.decision_state='PAUSE')
            THEN 0 ELSE 1 END
UNION ALL
SELECT 'DQ89','OBSERVATIONAL_FIXTURE_ASSOCIATION_AND_MAINTAIN_EXACT',
       CASE WHEN EXISTS (
         SELECT 1 FROM v_analysis_pair_readout p JOIN analysis_snapshot a USING (analysis_snapshot_id)
         JOIN decision_snapshot d USING (analysis_snapshot_id)
         WHERE p.analysis_snapshot_id='AN_OBS_1' AND p.metric_contract_id='MET_OBS_PRI_V1'
           AND abs(p.reference_raw_itt_mean-(1.0/3.0))<0.000000001
           AND abs(p.comparator_raw_itt_mean-(2.0/3.0))<0.000000001
           AND abs(p.raw_itt_mean_difference-(1.0/3.0))<0.000000001
           AND a.analysis_claim_cap_code='ASSOCIATION_ONLY'
           AND d.decision_state='MAINTAIN') THEN 'PASS' ELSE 'FAIL' END,
       CASE WHEN EXISTS (
         SELECT 1 FROM v_analysis_pair_readout p JOIN analysis_snapshot a USING (analysis_snapshot_id)
         JOIN decision_snapshot d USING (analysis_snapshot_id)
         WHERE p.analysis_snapshot_id='AN_OBS_1' AND p.metric_contract_id='MET_OBS_PRI_V1'
           AND abs(p.reference_raw_itt_mean-(1.0/3.0))<0.000000001
           AND abs(p.comparator_raw_itt_mean-(2.0/3.0))<0.000000001
           AND abs(p.raw_itt_mean_difference-(1.0/3.0))<0.000000001
           AND a.analysis_claim_cap_code='ASSOCIATION_ONLY'
           AND d.decision_state='MAINTAIN') THEN 0 ELSE 1 END
UNION ALL
SELECT 'DQ90','DECISION_STATE_VOCABULARY_AND_FIXTURE_CARDINALITY',
       CASE WHEN (SELECT count(*) FROM decision_snapshot)=4
                  AND NOT EXISTS (SELECT 1 FROM decision_snapshot WHERE decision_state NOT IN
                    ('STOP','PAUSE','UNKNOWN','INCONCLUSIVE','CHANGE','MAINTAIN','EXPAND'))
            THEN 'PASS' ELSE 'FAIL' END,
       CASE WHEN (SELECT count(*) FROM decision_snapshot)=4
                  AND NOT EXISTS (SELECT 1 FROM decision_snapshot WHERE decision_state NOT IN
                    ('STOP','PAUSE','UNKNOWN','INCONCLUSIVE','CHANGE','MAINTAIN','EXPAND'))
            THEN 0 ELSE 1 END
UNION ALL
SELECT 'DQ91','FOURTEEN_IMMUTABLE_ARM_METRIC_READOUTS',
       CASE WHEN (SELECT count(*) FROM analysis_arm_readout)=14
            THEN 'PASS' ELSE 'FAIL' END,
       abs((SELECT count(*) FROM analysis_arm_readout)-14)
UNION ALL
SELECT 'DQ92','FIXTURE_FULLY_LOADED_COSTS_EXACT',
       CASE WHEN (SELECT fully_loaded_experiment_cost_micros FROM v_experiment_cost_readout
                  WHERE decision_contract_id='DC_RP_V1')=23000000000
                  AND (SELECT fully_loaded_experiment_cost_micros FROM v_experiment_cost_readout
                       WHERE decision_contract_id='DC_AA_V1')=7000000000
                  AND (SELECT fully_loaded_experiment_cost_micros FROM v_experiment_cost_readout
                       WHERE decision_contract_id='DC_SW_V1')=11000000000
                  AND (SELECT fully_loaded_experiment_cost_micros FROM v_experiment_cost_readout
                       WHERE decision_contract_id='DC_OBS_V1')=4500000000
            THEN 'PASS' ELSE 'FAIL' END,
       CASE WHEN (SELECT fully_loaded_experiment_cost_micros FROM v_experiment_cost_readout
                  WHERE decision_contract_id='DC_RP_V1')=23000000000
                  AND (SELECT fully_loaded_experiment_cost_micros FROM v_experiment_cost_readout
                       WHERE decision_contract_id='DC_AA_V1')=7000000000
                  AND (SELECT fully_loaded_experiment_cost_micros FROM v_experiment_cost_readout
                       WHERE decision_contract_id='DC_SW_V1')=11000000000
                  AND (SELECT fully_loaded_experiment_cost_micros FROM v_experiment_cost_readout
                       WHERE decision_contract_id='DC_OBS_V1')=4500000000
            THEN 0 ELSE 1 END
UNION ALL
SELECT 'DQ93','FIXTURE_SAFETY_STATES_EXACT',
       CASE WHEN (SELECT safety_state FROM safety_review WHERE safety_review_id='SR_RP')='PASS'
                  AND (SELECT safety_state FROM safety_review WHERE safety_review_id='SR_AA')='NA'
                  AND (SELECT safety_state FROM safety_review WHERE safety_review_id='SR_SW')='PASS'
                  AND (SELECT safety_state FROM safety_review WHERE safety_review_id='SR_OBS')='NA'
            THEN 'PASS' ELSE 'FAIL' END,
       CASE WHEN (SELECT safety_state FROM safety_review WHERE safety_review_id='SR_RP')='PASS'
                  AND (SELECT safety_state FROM safety_review WHERE safety_review_id='SR_AA')='NA'
                  AND (SELECT safety_state FROM safety_review WHERE safety_review_id='SR_SW')='PASS'
                  AND (SELECT safety_state FROM safety_review WHERE safety_review_id='SR_OBS')='NA'
            THEN 0 ELSE 1 END
UNION ALL
SELECT 'DQ94','FIXTURE_EXPECTED_OUTCOME_COUNTS_EXACT',
       CASE WHEN (SELECT count(*) FROM expected_outcome_spine WHERE decision_contract_id='DC_RP_V1')=24
                  AND (SELECT count(*) FROM expected_outcome_spine WHERE decision_contract_id='DC_AA_V1')=4
                  AND (SELECT count(*) FROM expected_outcome_spine WHERE decision_contract_id='DC_SW_V1')=8
                  AND (SELECT count(*) FROM expected_outcome_spine WHERE decision_contract_id='DC_OBS_V1')=6
            THEN 'PASS' ELSE 'FAIL' END,
       CASE WHEN (SELECT count(*) FROM expected_outcome_spine WHERE decision_contract_id='DC_RP_V1')=24
                  AND (SELECT count(*) FROM expected_outcome_spine WHERE decision_contract_id='DC_AA_V1')=4
                  AND (SELECT count(*) FROM expected_outcome_spine WHERE decision_contract_id='DC_SW_V1')=8
                  AND (SELECT count(*) FROM expected_outcome_spine WHERE decision_contract_id='DC_OBS_V1')=6
            THEN 0 ELSE 1 END
UNION ALL
SELECT 'DQ95','FIXTURES_USE_OPAQUE_SYNTHETIC_REFERENCES',
       CASE WHEN NOT EXISTS (SELECT 1 FROM experiment_unit
                             WHERE unit_type_code<>'SYNTHETIC_UNIT')
                  AND NOT EXISTS (SELECT 1 FROM decision_contract
                                  WHERE experiment_code NOT LIKE 'SYN_%')
            THEN 'PASS' ELSE 'FAIL' END,
       (SELECT count(*) FROM experiment_unit WHERE unit_type_code<>'SYNTHETIC_UNIT')
       +(SELECT count(*) FROM decision_contract WHERE experiment_code NOT LIKE 'SYN_%')
UNION ALL
SELECT 'DQ96','BOUNDED_OUTPUT_LIMIT_ENFORCED_BY_SETTING',
       CASE WHEN (SELECT bounded_output_limit FROM model_setting WHERE singleton_id=1)
                    BETWEEN 1 AND 50 THEN 'PASS' ELSE 'FAIL' END,
       CASE WHEN (SELECT bounded_output_limit FROM model_setting WHERE singleton_id=1)
                    BETWEEN 1 AND 50 THEN 0 ELSE 1 END
UNION ALL
SELECT 'DQ97','ANALYSIS_SNAPSHOT_CHAIN_IS_EXACT_AND_MONOTONE',
       CASE WHEN NOT EXISTS (
         SELECT 1 FROM analysis_snapshot a
         WHERE (a.snapshot_sequence=1
                AND a.supersedes_analysis_snapshot_id IS NOT NULL)
            OR (a.snapshot_sequence>1 AND NOT EXISTS (
              SELECT 1 FROM analysis_snapshot p
              WHERE p.analysis_snapshot_id=a.supersedes_analysis_snapshot_id
                AND p.decision_contract_id=a.decision_contract_id
                AND p.snapshot_sequence=a.snapshot_sequence-1))
            OR (a.snapshot_sequence>1 AND EXISTS (
              SELECT 1 FROM analysis_snapshot p
              WHERE p.analysis_snapshot_id=a.supersedes_analysis_snapshot_id
                AND p.decision_contract_id=a.decision_contract_id
                AND p.snapshot_sequence=a.snapshot_sequence-1
                AND (datetime(a.evidence_cutoff_at)<datetime(p.evidence_cutoff_at)
                  OR datetime(a.snapshot_as_of)<datetime(p.snapshot_as_of)
                  OR datetime(a.created_at)<=datetime(p.created_at))))
       ) THEN 'PASS' ELSE 'FAIL' END,
       (SELECT count(*) FROM analysis_snapshot a
        WHERE (a.snapshot_sequence=1
               AND a.supersedes_analysis_snapshot_id IS NOT NULL)
           OR (a.snapshot_sequence>1 AND NOT EXISTS (
             SELECT 1 FROM analysis_snapshot p
             WHERE p.analysis_snapshot_id=a.supersedes_analysis_snapshot_id
               AND p.decision_contract_id=a.decision_contract_id
               AND p.snapshot_sequence=a.snapshot_sequence-1))
           OR (a.snapshot_sequence>1 AND EXISTS (
             SELECT 1 FROM analysis_snapshot p
             WHERE p.analysis_snapshot_id=a.supersedes_analysis_snapshot_id
               AND p.decision_contract_id=a.decision_contract_id
               AND p.snapshot_sequence=a.snapshot_sequence-1
               AND (datetime(a.evidence_cutoff_at)<datetime(p.evidence_cutoff_at)
                 OR datetime(a.snapshot_as_of)<datetime(p.snapshot_as_of)
                 OR datetime(a.created_at)<=datetime(p.created_at)))))
UNION ALL
SELECT 'DQ98','DECISION_SNAPSHOT_CHAIN_IS_EXACT_AND_MONOTONE',
       CASE WHEN NOT EXISTS (
         SELECT 1 FROM decision_snapshot d
         WHERE (d.decision_sequence=1
                AND d.supersedes_decision_snapshot_id IS NOT NULL)
            OR (d.decision_sequence>1 AND NOT EXISTS (
              SELECT 1 FROM decision_snapshot p
              WHERE p.decision_snapshot_id=d.supersedes_decision_snapshot_id
                AND p.decision_contract_id=d.decision_contract_id
                AND p.decision_sequence=d.decision_sequence-1))
            OR (d.decision_sequence>1 AND EXISTS (
              SELECT 1 FROM decision_snapshot p
              WHERE p.decision_snapshot_id=d.supersedes_decision_snapshot_id
                AND p.decision_contract_id=d.decision_contract_id
                AND p.decision_sequence=d.decision_sequence-1
                AND (datetime(d.decided_at)<datetime(p.decided_at)
                  OR datetime(d.recorded_at)<=datetime(p.recorded_at))))
       ) THEN 'PASS' ELSE 'FAIL' END,
       (SELECT count(*) FROM decision_snapshot d
        WHERE (d.decision_sequence=1
               AND d.supersedes_decision_snapshot_id IS NOT NULL)
           OR (d.decision_sequence>1 AND NOT EXISTS (
             SELECT 1 FROM decision_snapshot p
             WHERE p.decision_snapshot_id=d.supersedes_decision_snapshot_id
               AND p.decision_contract_id=d.decision_contract_id
               AND p.decision_sequence=d.decision_sequence-1))
           OR (d.decision_sequence>1 AND EXISTS (
             SELECT 1 FROM decision_snapshot p
             WHERE p.decision_snapshot_id=d.supersedes_decision_snapshot_id
               AND p.decision_contract_id=d.decision_contract_id
               AND p.decision_sequence=d.decision_sequence-1
               AND (datetime(d.decided_at)<datetime(p.decided_at)
                 OR datetime(d.recorded_at)<=datetime(p.recorded_at)))))
UNION ALL
SELECT 'DQ99','DECISION_USES_LATEST_AVAILABLE_COMPLETED_ANALYSIS',
       CASE WHEN NOT EXISTS (
         SELECT 1 FROM decision_snapshot d
         JOIN analysis_snapshot a USING (analysis_snapshot_id)
         WHERE datetime(d.decided_at)<datetime(a.created_at)
            OR EXISTS (
              SELECT 1 FROM analysis_arm_readout r
              WHERE r.analysis_snapshot_id=d.analysis_snapshot_id
                AND datetime(r.created_at)>datetime(d.decided_at))
            OR EXISTS (
              SELECT 1 FROM analysis_snapshot later
              WHERE later.decision_contract_id=d.decision_contract_id
                AND later.snapshot_sequence>a.snapshot_sequence
                AND datetime(later.created_at)<=datetime(d.decided_at))
       ) THEN 'PASS' ELSE 'FAIL' END,
       (SELECT count(*) FROM decision_snapshot d
        JOIN analysis_snapshot a USING (analysis_snapshot_id)
        WHERE datetime(d.decided_at)<datetime(a.created_at)
           OR EXISTS (
             SELECT 1 FROM analysis_arm_readout r
             WHERE r.analysis_snapshot_id=d.analysis_snapshot_id
               AND datetime(r.created_at)>datetime(d.decided_at))
           OR EXISTS (
             SELECT 1 FROM analysis_snapshot later
             WHERE later.decision_contract_id=d.decision_contract_id
               AND later.snapshot_sequence>a.snapshot_sequence
               AND datetime(later.created_at)<=datetime(d.decided_at)))
UNION ALL
SELECT 'DQ100','EXPAND_IS_FRESH_AND_CURRENTLY_CLEAN_SAFE',
       CASE WHEN NOT EXISTS (
         SELECT 1 FROM decision_snapshot d
         JOIN analysis_snapshot a USING (analysis_snapshot_id)
         WHERE d.decision_state='EXPAND' AND (
           EXISTS (
             SELECT 1 FROM v_decision_relevant_fact f
             WHERE f.decision_contract_id=d.decision_contract_id
               AND datetime(f.fact_recorded_at)>datetime(a.evidence_cutoff_at)
               AND datetime(f.fact_recorded_at)<=datetime(d.decided_at))
           OR EXISTS (
             SELECT 1 FROM integrity_issue_stream s
             JOIN integrity_issue_event e USING (integrity_issue_stream_id)
             WHERE s.decision_contract_id=d.decision_contract_id
               AND s.blocks_expand=1
               AND datetime(e.recorded_at)<=datetime(d.decided_at)
               AND e.event_sequence=(
                 SELECT max(e2.event_sequence) FROM integrity_issue_event e2
                 WHERE e2.integrity_issue_stream_id=s.integrity_issue_stream_id
                   AND datetime(e2.recorded_at)<=datetime(d.decided_at))
               AND e.issue_state IN ('OPEN','UNKNOWN'))
           OR NOT EXISTS (
             SELECT 1 FROM safety_review s
             WHERE s.decision_contract_id=d.decision_contract_id
               AND datetime(s.recorded_at)<=datetime(d.decided_at)
               AND s.safety_state='PASS'
               AND s.safety_review_id=(
                 SELECT s2.safety_review_id FROM safety_review s2
                 WHERE s2.decision_contract_id=d.decision_contract_id
                   AND datetime(s2.recorded_at)<=datetime(d.decided_at)
                 ORDER BY datetime(s2.recorded_at) DESC,
                          datetime(s2.reviewed_at) DESC,s2.safety_review_id DESC
                 LIMIT 1)))
       ) THEN 'PASS' ELSE 'FAIL' END,
       (SELECT count(*) FROM decision_snapshot d
        JOIN analysis_snapshot a USING (analysis_snapshot_id)
        WHERE d.decision_state='EXPAND' AND (
          EXISTS (
            SELECT 1 FROM v_decision_relevant_fact f
            WHERE f.decision_contract_id=d.decision_contract_id
              AND datetime(f.fact_recorded_at)>datetime(a.evidence_cutoff_at)
              AND datetime(f.fact_recorded_at)<=datetime(d.decided_at))
          OR EXISTS (
            SELECT 1 FROM integrity_issue_stream s
            JOIN integrity_issue_event e USING (integrity_issue_stream_id)
            WHERE s.decision_contract_id=d.decision_contract_id
              AND s.blocks_expand=1
              AND datetime(e.recorded_at)<=datetime(d.decided_at)
              AND e.event_sequence=(
                SELECT max(e2.event_sequence) FROM integrity_issue_event e2
                WHERE e2.integrity_issue_stream_id=s.integrity_issue_stream_id
                  AND datetime(e2.recorded_at)<=datetime(d.decided_at))
              AND e.issue_state IN ('OPEN','UNKNOWN'))
          OR NOT EXISTS (
            SELECT 1 FROM safety_review s
            WHERE s.decision_contract_id=d.decision_contract_id
              AND datetime(s.recorded_at)<=datetime(d.decided_at)
              AND s.safety_state='PASS'
              AND s.safety_review_id=(
                SELECT s2.safety_review_id FROM safety_review s2
                WHERE s2.decision_contract_id=d.decision_contract_id
                  AND datetime(s2.recorded_at)<=datetime(d.decided_at)
                ORDER BY datetime(s2.recorded_at) DESC,
                         datetime(s2.reviewed_at) DESC,s2.safety_review_id DESC
                LIMIT 1))))
UNION ALL
SELECT 'DQ101','HARDENING_VIEWS_AND_GUARDS_ARE_PRESENT',
       CASE WHEN (SELECT count(*) FROM sqlite_schema
                  WHERE type='view' AND name IN (
                    'v_snapshot_itt_arm_readout','v_decision_relevant_fact'))=2
                  AND instr((SELECT sql FROM sqlite_schema
                             WHERE type='trigger'
                               AND name='analysis_snapshot_semantic_guard'),
                            'exact same-contract predecessor')>0
                  AND instr((SELECT sql FROM sqlite_schema
                             WHERE type='trigger'
                               AND name='analysis_arm_readout_guard'),
                            'snapshot source facts')>0
                  AND instr((SELECT sql FROM sqlite_schema
                             WHERE type='trigger'
                               AND name='decision_snapshot_semantic_guard'),
                            'latest analysis available')>0
                  AND instr((SELECT sql FROM sqlite_schema
                             WHERE type='trigger'
                               AND name='decision_snapshot_semantic_guard'),
                            'complete primary and guardrail readouts')>0
                  AND instr((SELECT sql FROM sqlite_schema
                             WHERE type='trigger'
                               AND name='decision_snapshot_semantic_guard'),
                            'current cost facts')>0
                  AND instr((SELECT sql FROM sqlite_schema
                             WHERE type='trigger'
                               AND name='integrity_issue_stream_semantic_guard'),
                            'block flags must match category defaults')>0
                  AND instr((SELECT sql FROM sqlite_schema
                             WHERE type='trigger'
                               AND name='safety_review_semantic_guard'),
                            'guardrail counts do not reconcile')>0
            THEN 'PASS' ELSE 'FAIL' END,
       CASE WHEN (SELECT count(*) FROM sqlite_schema
                  WHERE type='view' AND name IN (
                    'v_snapshot_itt_arm_readout','v_decision_relevant_fact'))=2
                  AND instr((SELECT sql FROM sqlite_schema
                             WHERE type='trigger'
                               AND name='analysis_snapshot_semantic_guard'),
                            'exact same-contract predecessor')>0
                  AND instr((SELECT sql FROM sqlite_schema
                             WHERE type='trigger'
                               AND name='analysis_arm_readout_guard'),
                            'snapshot source facts')>0
                  AND instr((SELECT sql FROM sqlite_schema
                             WHERE type='trigger'
                               AND name='decision_snapshot_semantic_guard'),
                            'latest analysis available')>0
                  AND instr((SELECT sql FROM sqlite_schema
                             WHERE type='trigger'
                               AND name='decision_snapshot_semantic_guard'),
                            'complete primary and guardrail readouts')>0
                  AND instr((SELECT sql FROM sqlite_schema
                             WHERE type='trigger'
                               AND name='decision_snapshot_semantic_guard'),
                            'current cost facts')>0
                  AND instr((SELECT sql FROM sqlite_schema
                             WHERE type='trigger'
                               AND name='integrity_issue_stream_semantic_guard'),
                            'block flags must match category defaults')>0
                  AND instr((SELECT sql FROM sqlite_schema
                             WHERE type='trigger'
                               AND name='safety_review_semantic_guard'),
                            'guardrail counts do not reconcile')>0
            THEN 0 ELSE 1 END
UNION ALL
SELECT 'DQ102','ANALYSIS_BINDS_LATEST_SAFETY_AT_CUTOFF',
       CASE WHEN NOT EXISTS (
         SELECT 1 FROM analysis_snapshot a
         WHERE a.safety_review_id IS NOT (
           SELECT s.safety_review_id FROM safety_review s
           WHERE s.decision_contract_id=a.decision_contract_id
             AND datetime(s.recorded_at)<=datetime(a.evidence_cutoff_at)
           ORDER BY datetime(s.recorded_at) DESC,datetime(s.reviewed_at) DESC,
                    s.safety_review_id DESC
           LIMIT 1)
       ) THEN 'PASS' ELSE 'FAIL' END,
       (SELECT count(*) FROM analysis_snapshot a
        WHERE a.safety_review_id IS NOT (
          SELECT s.safety_review_id FROM safety_review s
          WHERE s.decision_contract_id=a.decision_contract_id
            AND datetime(s.recorded_at)<=datetime(a.evidence_cutoff_at)
          ORDER BY datetime(s.recorded_at) DESC,datetime(s.reviewed_at) DESC,
                   s.safety_review_id DESC
          LIMIT 1));

CREATE TEMP VIEW v_assertion_report AS
SELECT count(*) AS check_count,
       count(DISTINCT dq_id) AS distinct_check_count,
       sum(status='PASS') AS pass_count,
       sum(status='FAIL') AS failure_count,
       coalesce(sum(failing_row_count),0) AS failing_row_count
FROM v_dq_results;

CREATE TEMP TABLE assertion_guard (
  run_label TEXT PRIMARY KEY,
  check_count INTEGER NOT NULL CHECK (check_count=102),
  distinct_check_count INTEGER NOT NULL CHECK (distinct_check_count=102),
  failure_count INTEGER NOT NULL CHECK (failure_count=0),
  failing_row_count INTEGER NOT NULL CHECK (failing_row_count=0)
) STRICT;

-- This statement aborts and leaves the transaction uncommitted if any named
-- assertion fails.  sqlite3 -bail therefore exits non-zero on a bad fixture.
INSERT INTO assertion_guard
SELECT 'DQ01_DQ102',check_count,distinct_check_count,failure_count,
       failing_row_count
FROM v_assertion_report;

COMMIT;

-- -----------------------------------------------------------------------------
-- Bounded, non-inferential final outputs
-- -----------------------------------------------------------------------------

PRAGMA foreign_key_check;
PRAGMA integrity_check;

SELECT check_count,distinct_check_count,pass_count,failure_count,
       failing_row_count
FROM v_assertion_report;

SELECT dq_id,check_name,status,failing_row_count
FROM v_dq_results
WHERE status='FAIL'
ORDER BY dq_id
LIMIT (SELECT bounded_output_limit FROM model_setting WHERE singleton_id=1);

SELECT p.decision_contract_id,p.design_type_code,p.metric_code,
       p.reference_arm_code,p.reference_observed_n||'/'||p.reference_assigned_n
         AS reference_x_over_n,
       p.reference_missing_n,p.reference_observed_mean,p.reference_raw_itt_mean,
       p.comparator_arm_code,p.comparator_observed_n||'/'||p.comparator_assigned_n
         AS comparator_x_over_n,
       p.comparator_missing_n,p.comparator_observed_mean,p.comparator_raw_itt_mean,
       p.observed_mean_difference,p.raw_itt_mean_difference,
       p.interpretation_boundary
FROM v_raw_itt_pair_readout p
ORDER BY p.decision_contract_id,p.metric_contract_id
LIMIT (SELECT bounded_output_limit FROM model_setting WHERE singleton_id=1);

SELECT a.decision_contract_id,a.analysis_claim_cap_code,a.carryover_state,
       a.maturity_gate,a.integrity_gate,a.safety_gate,a.analysis_state,
       a.assigned_n,a.expected_outcome_n,a.matured_expected_n,
       a.classified_outcome_n,a.open_integrity_issue_n,
       a.blocking_causal_issue_n,a.blocking_expand_issue_n,
       d.decision_state,d.claimed_interpretation_code,d.action_scope_code
FROM analysis_snapshot a
JOIN decision_snapshot d USING (analysis_snapshot_id)
ORDER BY a.decision_contract_id
LIMIT (SELECT bounded_output_limit FROM model_setting WHERE singleton_id=1);

SELECT decision_contract_id,issue_category_code,issue_scope_code,issue_state,
       blocks_causal_claim,blocks_expand,state_reason_code
FROM v_current_integrity_issue
ORDER BY decision_contract_id,issue_category_code
LIMIT (SELECT bounded_output_limit FROM model_setting WHERE singleton_id=1);

SELECT decision_contract_id,cash_cost_micros,founder_minutes,
       founder_time_cost_micros,fully_loaded_experiment_cost_micros
FROM v_experiment_cost_readout
ORDER BY decision_contract_id
LIMIT (SELECT bounded_output_limit FROM model_setting WHERE singleton_id=1);

-- Suggested negative probes (run individually on a disposable database after
-- loading this file; each must abort with the exact message shown):
-- 1. UPDATE experiment_assignment SET arm_id='ARM_RP_T' WHERE assignment_id='AS_RP01';
--    -> append-only: experiment_assignment cannot be updated
-- 2. INSERT an exposure for AS_RP01 with occurred_at before 2026-06-01T10:00:00Z.
--    -> exposure cannot occur before assignment
-- 3. INSERT a MISSING_CONFIRMED outcome with numeric_value=0.
--    -> CHECK constraint failed (outcome_observation value-shape CHECK)
-- 4. INSERT an EXPAND decision against AN_AA_1.
--    -> EXPAND requires complete maturity, closed integrity, and safety PASS
-- 5. INSERT a causal switchback analysis with carryover_state='UNKNOWN'.
--    -> switchback causal claim requires resolved carryover
-- 6. INSERT analysis sequence 99 with a predecessor from another contract.
--    -> analysis snapshot after v1 requires exact same-contract predecessor
-- 7. After decision v1, reference a new analysis whose created_at is later than
--    the v2 decision's decided_at (while the v2 decision still follows v1).
--    -> decision cannot precede referenced analysis
-- 8. Append analysis sequence 2, then decide later against sequence 1.
--    -> decision must use latest analysis available when decided
-- 9. INSERT a self-consistent but source-inconsistent arm readout.
--    -> analysis readout does not reconcile to snapshot source facts
-- 10. Append a decision-relevant fact after the analysis cutoff, then EXPAND.
--     -> EXPAND requires reanalysis after newer decision-relevant facts
-- 11. Bind an analysis to an older safety review than the cutoff-time latest.
--     -> analysis must use latest safety review at evidence cutoff
-- 12. INSERT an integrity stream whose block flags differ from its category.
--     -> integrity issue block flags must match category defaults
-- 13. Append current OPEN contamination, then try to EXPAND.
--     -> EXPAND cannot proceed with current open contamination
-- 14. Register a newer pre-assignment primary metric without exact readouts,
--     then substitute the same number of obsolete-version rows and EXPAND.
--     -> EXPAND requires complete primary and guardrail readouts for every arm
-- 15. INSERT safety PASS with counts that differ from review-time guardrails.
--     -> safety review guardrail counts do not reconcile to review-time facts
-- 16. Append a cost fact that is logically inside the decision clock without
--     refreshing analysis, then EXPAND.
--     -> EXPAND requires current cost facts to match analysis and contract caps
