-- Service productization operating model
-- Schema version: 1.2.0 | Reference date: 2026-08-01 | Engine: SQLite 3.40+
-- Companion: ../14-service-to-software-productization.md
--
-- Run on a new/disposable database:
--   sqlite3 :memory: < indie-developer-playbook/research/14-service-productization-model.sql
--
-- All fixture records are fictional. This model intentionally keeps four axes
-- separate: commercial motion, delivery mode, delegation level, and risk tier.
-- None is inferred from another.
--
-- Privacy boundary
-- - No name, email, phone, address, free-form customer text, source document,
--   prompt, output, or raw payload belongs in this model.
-- - Customer and evidence references are opaque IDs or 64-character lowercase
--   hexadecimal hashes. Hashes are evidence pointers, not proof of lawful use.
-- - Fixture printf() hashes demonstrate shape only; they are not cryptographic.
--
-- Money boundary
-- - *_micros is one millionth of the row currency (JPY 1 = 1,000,000 micros).
-- - Invoice, payment, refund, cash contribution, and fully-loaded contribution
--   remain separate. The commercial gate uses contracted + accepted work;
--   realized value and collected cash remain independent evidence, never inferred.
-- - Founder internal time is a management cost, not necessarily cash expense.

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

CREATE TABLE schema_registry (
  schema_id INTEGER PRIMARY KEY CHECK (schema_id = 1),
  schema_name TEXT NOT NULL UNIQUE CHECK (schema_name = 'service_productization'),
  schema_version TEXT NOT NULL CHECK (schema_version = '1.2.0'),
  engine_floor TEXT NOT NULL CHECK (engine_floor = 'SQLite 3.40+'),
  applied_at TEXT NOT NULL CHECK (datetime(applied_at) IS NOT NULL),
  pii_policy_code TEXT NOT NULL CHECK (pii_policy_code = 'NO_PERSONAL_DATA_HASHED_EVIDENCE_ONLY')
) STRICT;

CREATE TABLE model_settings (
  singleton_id INTEGER PRIMARY KEY CHECK (singleton_id = 1),
  event_time_as_of TEXT NOT NULL CHECK (datetime(event_time_as_of) IS NOT NULL),
  data_watermark_at TEXT NOT NULL CHECK (datetime(data_watermark_at) IS NOT NULL),
  base_currency TEXT NOT NULL UNIQUE
    CHECK (length(base_currency) = 3 AND base_currency = upper(base_currency)),
  default_founder_rate_micros_per_minute INTEGER NOT NULL
    CHECK (default_founder_rate_micros_per_minute > 0),
  CHECK (datetime(data_watermark_at) <= datetime(event_time_as_of))
) STRICT;

-- Independent axes. Ordinals describe progression only within their own axis.
CREATE TABLE commercial_motion_dim (
  commercial_motion_code TEXT PRIMARY KEY,
  requires_payment INTEGER NOT NULL CHECK (requires_payment IN (0,1)),
  CHECK (commercial_motion_code IN (
    'pilot','production','renewal','expansion'
  ))
) STRICT;

CREATE TABLE delivery_mode_dim (
  delivery_mode_code TEXT PRIMARY KEY,
  productization_order INTEGER NOT NULL UNIQUE CHECK (productization_order BETWEEN 0 AND 4),
  CHECK (delivery_mode_code IN (
    'manual_bespoke','managed_standard','founder_internal',
    'customer_assisted','customer_self_service'
  ))
) STRICT;

CREATE TABLE promotion_gate_domain_dim (
  domain_code TEXT PRIMARY KEY CHECK (domain_code IN (
    'customer_value','independent_accounts','natural_cycles','customer_effort',
    'capacity_planned_work','fully_loaded_contribution','acceptance_oracle',
    'data_rights','maximum_loss','release_telemetry',
    'target_mode_independent_value'
  )),
  fail_policy TEXT NOT NULL CHECK (fail_policy IN ('PAUSE','STOP'))
) STRICT;

CREATE TABLE delivery_mode_required_gate_domain (
  delivery_mode_code TEXT NOT NULL,
  domain_code TEXT NOT NULL,
  PRIMARY KEY (delivery_mode_code,domain_code),
  FOREIGN KEY (delivery_mode_code) REFERENCES delivery_mode_dim(delivery_mode_code),
  FOREIGN KEY (domain_code) REFERENCES promotion_gate_domain_dim(domain_code)
) STRICT;

CREATE TABLE delegation_level_dim (
  delegation_level_code TEXT PRIMARY KEY,
  delegation_order INTEGER NOT NULL UNIQUE CHECK (delegation_order BETWEEN 0 AND 4),
  CHECK (delegation_level_code IN (
    'L0_READ','L1_DRAFT','L2_REVERSIBLE_WRITE',
    'L3_EXTERNAL_EXECUTE_APPROVAL','L4_PREAUTHORIZED_EXECUTE'
  ))
) STRICT;

CREATE TABLE risk_tier_dim (
  risk_tier_code TEXT PRIMARY KEY,
  risk_order INTEGER NOT NULL UNIQUE CHECK (risk_order BETWEEN 0 AND 3),
  CHECK (risk_tier_code IN ('R0','R1','R2','R3'))
) STRICT;

CREATE TABLE promotion_action_dim (
  action_code TEXT PRIMARY KEY CHECK (action_code IN (
    'PROMOTE','LIMITED_CANARY','MAINTAIN','HOLD','REVERSE','PAUSE','STOP'
  ))
) STRICT;

-- Chapter-authoritative exception vocabulary. These are classifications, not
-- free text; each event stores both the observed cause and chosen treatment.
CREATE TABLE exception_cause_dim (
  cause_class TEXT PRIMARY KEY CHECK (cause_class IN (
    'input_contract','valid_standard_variation','product_defect',
    'model_or_rule_uncertainty','external_dependency','customer_dependency',
    'customer_specific_request','security_privacy_safety_legal','unknown'
  ))
) STRICT;

CREATE TABLE exception_treatment_dim (
  treatment_code TEXT PRIMARY KEY CHECK (treatment_code IN (
    'auto_handled','planned_review','correction','customer_action_requested',
    'support','founder_rescue','pause','stop_and_escalate',
    'compensating_action','scope_change','separate_SOW'
  ))
) STRICT;

-- No customer attributes that can identify a natural person are stored.
CREATE TABLE customer_account (
  account_id TEXT PRIMARY KEY,
  account_ref_hash TEXT NOT NULL UNIQUE,
  segment_code TEXT NOT NULL
    CHECK (length(segment_code) BETWEEN 1 AND 40
       AND segment_code = upper(segment_code)
       AND segment_code NOT GLOB '*[^A-Z0-9_]*'),
  created_at TEXT NOT NULL CHECK (datetime(created_at) IS NOT NULL),
  recorded_at TEXT NOT NULL CHECK (datetime(recorded_at) IS NOT NULL),
  CHECK (length(account_id) BETWEEN 1 AND 64
     AND account_id NOT GLOB '*[^A-Za-z0-9:_-]*'),
  CHECK (length(account_ref_hash) = 64
     AND account_ref_hash = lower(account_ref_hash)
     AND account_ref_hash NOT GLOB '*[^0-9a-f]*'),
  CHECK (datetime(recorded_at) >= datetime(created_at))
) STRICT;

-- Workflow scope is operational truth and may be packaged by more than one
-- commercial offer. It is deliberately not UNIQUE to an offer_version.
CREATE TABLE workflow_scope_version (
  workflow_id TEXT NOT NULL,
  workflow_version INTEGER NOT NULL CHECK (workflow_version >= 1),
  segment_code TEXT NOT NULL,
  data_class_code TEXT NOT NULL CHECK (data_class_code IN (
    'synthetic','internal_nonpersonal','customer_confidential_nonpersonal','personal_data_prohibited'
  )),
  effective_from TEXT NOT NULL CHECK (datetime(effective_from) IS NOT NULL),
  superseded_at TEXT CHECK (
    superseded_at IS NULL OR (
      datetime(superseded_at) IS NOT NULL
      AND datetime(superseded_at) > datetime(effective_from)
    )
  ),
  definition_ref_hash TEXT NOT NULL,
  recorded_at TEXT NOT NULL CHECK (datetime(recorded_at) IS NOT NULL),
  PRIMARY KEY (workflow_id,workflow_version,segment_code,data_class_code),
  CHECK (length(workflow_id) BETWEEN 1 AND 50
     AND workflow_id = upper(workflow_id)
     AND workflow_id NOT GLOB '*[^A-Z0-9_]*'),
  CHECK (length(segment_code) BETWEEN 1 AND 40
     AND segment_code = upper(segment_code)
     AND segment_code NOT GLOB '*[^A-Z0-9_]*'),
  CHECK (length(definition_ref_hash) = 64
     AND definition_ref_hash = lower(definition_ref_hash)
     AND definition_ref_hash NOT GLOB '*[^0-9a-f]*'),
  CHECK (datetime(recorded_at) <= datetime(effective_from))
) STRICT;

-- One immutable commercial/service promise version. The four axes are columns
-- with separate foreign keys; a code in one axis never implies another.
CREATE TABLE offer_version (
  offer_version_id TEXT PRIMARY KEY,
  offer_code TEXT NOT NULL,
  version_number INTEGER NOT NULL CHECK (version_number >= 1),
  workflow_id TEXT NOT NULL,
  workflow_version INTEGER NOT NULL CHECK (workflow_version >= 1),
  segment_code TEXT NOT NULL,
  data_class_code TEXT NOT NULL CHECK (data_class_code IN (
    'synthetic','internal_nonpersonal','customer_confidential_nonpersonal','personal_data_prohibited'
  )),
  effective_from TEXT NOT NULL CHECK (datetime(effective_from) IS NOT NULL),
  effective_to TEXT CHECK (
    effective_to IS NULL OR (
      datetime(effective_to) IS NOT NULL
      AND datetime(effective_to) > datetime(effective_from)
    )
  ),
  commercial_motion_code TEXT NOT NULL,
  delivery_mode_code TEXT NOT NULL,
  delegation_level_code TEXT NOT NULL,
  risk_tier_code TEXT NOT NULL,
  standard_scope_code TEXT NOT NULL,
  list_price_micros INTEGER NOT NULL CHECK (list_price_micros >= 0),
  currency TEXT NOT NULL CHECK (length(currency) = 3 AND currency = upper(currency)),
  included_jobs_per_cycle INTEGER NOT NULL CHECK (included_jobs_per_cycle >= 1),
  outcome_maturity_days INTEGER NOT NULL CHECK (outcome_maturity_days >= 0),
  evidence_ref_hash TEXT NOT NULL,
  recorded_at TEXT NOT NULL CHECK (datetime(recorded_at) IS NOT NULL),
  UNIQUE (offer_code, version_number),
  UNIQUE (offer_version_id, commercial_motion_code),
  UNIQUE (
    offer_version_id,workflow_id,workflow_version,segment_code,data_class_code,
    commercial_motion_code
  ),
  CHECK (length(offer_version_id) BETWEEN 1 AND 64
     AND offer_version_id NOT GLOB '*[^A-Za-z0-9:_-]*'),
  CHECK (length(offer_code) BETWEEN 1 AND 50
     AND offer_code = upper(offer_code)
     AND offer_code NOT GLOB '*[^A-Z0-9_]*'),
  CHECK (length(standard_scope_code) BETWEEN 1 AND 50
     AND standard_scope_code = upper(standard_scope_code)
     AND standard_scope_code NOT GLOB '*[^A-Z0-9_]*'),
  CHECK (length(workflow_id) BETWEEN 1 AND 50
     AND workflow_id = upper(workflow_id)
     AND workflow_id NOT GLOB '*[^A-Z0-9_]*'),
  CHECK (length(segment_code) BETWEEN 1 AND 40
     AND segment_code = upper(segment_code)
     AND segment_code NOT GLOB '*[^A-Z0-9_]*'),
  CHECK (length(evidence_ref_hash) = 64
     AND evidence_ref_hash = lower(evidence_ref_hash)
     AND evidence_ref_hash NOT GLOB '*[^0-9a-f]*'),
  CHECK (datetime(recorded_at) <= datetime(effective_from)),
  FOREIGN KEY (commercial_motion_code) REFERENCES commercial_motion_dim(commercial_motion_code),
  FOREIGN KEY (delivery_mode_code) REFERENCES delivery_mode_dim(delivery_mode_code),
  FOREIGN KEY (delegation_level_code) REFERENCES delegation_level_dim(delegation_level_code),
  FOREIGN KEY (risk_tier_code) REFERENCES risk_tier_dim(risk_tier_code),
  FOREIGN KEY (currency) REFERENCES model_settings(base_currency),
  FOREIGN KEY (workflow_id,workflow_version,segment_code,data_class_code)
    REFERENCES workflow_scope_version(
      workflow_id,workflow_version,segment_code,data_class_code
    )
) STRICT;

CREATE TABLE offer_standard_step (
  offer_version_id TEXT NOT NULL,
  step_code TEXT NOT NULL,
  step_order INTEGER NOT NULL CHECK (step_order >= 1),
  delivery_mode_code TEXT NOT NULL,
  required_in_standard_scope INTEGER NOT NULL CHECK (required_in_standard_scope IN (0,1)),
  standard_minutes_budget INTEGER NOT NULL CHECK (standard_minutes_budget >= 0),
  permitted_execution_kind TEXT NOT NULL CHECK (permitted_execution_kind IN (
    'human_only','deterministic_tool','ai_assist','ai_agent_with_approval'
  )),
  evidence_ref_hash TEXT NOT NULL,
  recorded_at TEXT NOT NULL CHECK (datetime(recorded_at) IS NOT NULL),
  PRIMARY KEY (offer_version_id, step_code),
  UNIQUE (offer_version_id, step_order),
  UNIQUE (offer_version_id,step_code,recorded_at),
  CHECK (length(step_code) BETWEEN 1 AND 50
     AND step_code = upper(step_code)
     AND step_code NOT GLOB '*[^A-Z0-9_]*'),
  CHECK (length(evidence_ref_hash) = 64
     AND evidence_ref_hash = lower(evidence_ref_hash)
     AND evidence_ref_hash NOT GLOB '*[^0-9a-f]*'),
  FOREIGN KEY (offer_version_id) REFERENCES offer_version(offer_version_id)
  ,FOREIGN KEY (delivery_mode_code) REFERENCES delivery_mode_dim(delivery_mode_code)
) STRICT;

-- One commercial cycle owns invoice/payment/refund once. Revenue is never copied
-- to each service job.
CREATE TABLE commercial_cycle (
  commercial_cycle_id TEXT PRIMARY KEY,
  account_id TEXT NOT NULL,
  offer_version_id TEXT NOT NULL,
  commercial_motion_code TEXT NOT NULL,
  period_start TEXT NOT NULL CHECK (datetime(period_start) IS NOT NULL),
  period_end TEXT NOT NULL CHECK (datetime(period_end) IS NOT NULL),
  payment_due_at TEXT NOT NULL CHECK (datetime(payment_due_at) IS NOT NULL),
  contracted_amount_micros INTEGER NOT NULL CHECK (contracted_amount_micros > 0),
  agreement_ref_hash TEXT NOT NULL,
  currency TEXT NOT NULL CHECK (length(currency) = 3 AND currency = upper(currency)),
  recorded_at TEXT NOT NULL CHECK (datetime(recorded_at) IS NOT NULL),
  UNIQUE (commercial_cycle_id, account_id, offer_version_id, commercial_motion_code),
  UNIQUE (account_id, offer_version_id, period_start),
  CHECK (length(commercial_cycle_id) BETWEEN 1 AND 64
     AND commercial_cycle_id NOT GLOB '*[^A-Za-z0-9:_-]*'),
  CHECK (datetime(period_end) > datetime(period_start)),
  CHECK (datetime(payment_due_at) >= datetime(period_start)),
  CHECK (datetime(recorded_at) <= datetime(period_start)),
  CHECK (length(agreement_ref_hash) = 64
     AND agreement_ref_hash = lower(agreement_ref_hash)
     AND agreement_ref_hash NOT GLOB '*[^0-9a-f]*'),
  FOREIGN KEY (account_id) REFERENCES customer_account(account_id),
  FOREIGN KEY (offer_version_id, commercial_motion_code)
    REFERENCES offer_version(offer_version_id, commercial_motion_code),
  FOREIGN KEY (commercial_motion_code) REFERENCES commercial_motion_dim(commercial_motion_code),
  FOREIGN KEY (currency) REFERENCES model_settings(base_currency)
) STRICT;

CREATE TABLE commercial_event (
  commercial_event_id TEXT PRIMARY KEY,
  commercial_cycle_id TEXT NOT NULL,
  event_type TEXT NOT NULL CHECK (event_type IN (
    'invoice_issued','credit_note_issued','payment_received','refund_paid'
  )),
  amount_micros INTEGER NOT NULL CHECK (amount_micros > 0),
  currency TEXT NOT NULL CHECK (length(currency) = 3 AND currency = upper(currency)),
  source_event_ref_hash TEXT NOT NULL UNIQUE,
  occurred_at TEXT NOT NULL CHECK (datetime(occurred_at) IS NOT NULL),
  recorded_at TEXT NOT NULL CHECK (datetime(recorded_at) IS NOT NULL),
  CHECK (length(commercial_event_id) BETWEEN 1 AND 64
     AND commercial_event_id NOT GLOB '*[^A-Za-z0-9:_-]*'),
  CHECK (datetime(recorded_at) >= datetime(occurred_at)),
  CHECK (length(source_event_ref_hash) = 64
     AND source_event_ref_hash = lower(source_event_ref_hash)
     AND source_event_ref_hash NOT GLOB '*[^0-9a-f]*'),
  FOREIGN KEY (commercial_cycle_id) REFERENCES commercial_cycle(commercial_cycle_id),
  FOREIGN KEY (currency) REFERENCES model_settings(base_currency)
) STRICT;

-- One row per eligible service unit. State changes are separate append-only
-- service_job_state_event rows.
CREATE TABLE service_job (
  service_job_id TEXT PRIMARY KEY,
  account_id TEXT NOT NULL,
  offer_version_id TEXT NOT NULL,
  commercial_cycle_id TEXT NOT NULL,
  commercial_motion_code TEXT NOT NULL,
  delivery_mode_code TEXT NOT NULL,
  delegation_level_code TEXT NOT NULL,
  risk_tier_code TEXT NOT NULL,
  source_job_ref_hash TEXT NOT NULL UNIQUE,
  scope_class TEXT NOT NULL CHECK (scope_class IN ('standard','custom','unknown')),
  eligibility_state TEXT NOT NULL CHECK (eligibility_state IN ('eligible','excluded')),
  exclusion_reason_code TEXT,
  eligible_at TEXT NOT NULL CHECK (datetime(eligible_at) IS NOT NULL),
  outcome_maturity_due_at TEXT NOT NULL CHECK (datetime(outcome_maturity_due_at) IS NOT NULL),
  recorded_at TEXT NOT NULL CHECK (datetime(recorded_at) IS NOT NULL),
  UNIQUE (service_job_id, offer_version_id),
  UNIQUE (service_job_id, commercial_cycle_id),
  UNIQUE (service_job_id, offer_version_id, commercial_motion_code),
  CHECK (length(service_job_id) BETWEEN 1 AND 64
     AND service_job_id NOT GLOB '*[^A-Za-z0-9:_-]*'),
  CHECK (datetime(outcome_maturity_due_at) >= datetime(eligible_at)),
  CHECK (datetime(recorded_at) >= datetime(eligible_at)),
  CHECK (
    (eligibility_state = 'eligible' AND exclusion_reason_code IS NULL)
    OR (eligibility_state = 'excluded' AND exclusion_reason_code IS NOT NULL)
  ),
  CHECK (exclusion_reason_code IS NULL OR (
    length(exclusion_reason_code) BETWEEN 1 AND 40
    AND exclusion_reason_code = upper(exclusion_reason_code)
    AND exclusion_reason_code NOT GLOB '*[^A-Z0-9_]*'
  )),
  CHECK (length(source_job_ref_hash) = 64
     AND source_job_ref_hash = lower(source_job_ref_hash)
     AND source_job_ref_hash NOT GLOB '*[^0-9a-f]*'),
  FOREIGN KEY (account_id) REFERENCES customer_account(account_id),
  FOREIGN KEY (offer_version_id) REFERENCES offer_version(offer_version_id),
  FOREIGN KEY (
    commercial_cycle_id, account_id, offer_version_id, commercial_motion_code
  ) REFERENCES commercial_cycle(
    commercial_cycle_id, account_id, offer_version_id, commercial_motion_code
  ),
  FOREIGN KEY (commercial_motion_code) REFERENCES commercial_motion_dim(commercial_motion_code),
  FOREIGN KEY (delivery_mode_code) REFERENCES delivery_mode_dim(delivery_mode_code),
  FOREIGN KEY (delegation_level_code) REFERENCES delegation_level_dim(delegation_level_code),
  FOREIGN KEY (risk_tier_code) REFERENCES risk_tier_dim(risk_tier_code)
) STRICT;

CREATE TABLE service_job_state_event (
  state_event_id TEXT PRIMARY KEY,
  service_job_id TEXT NOT NULL,
  event_sequence INTEGER NOT NULL CHECK (event_sequence >= 1),
  execution_state TEXT NOT NULL CHECK (execution_state IN (
    'queued','started','completed','failed','cancelled'
  )),
  evidence_ref_hash 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),
  UNIQUE (service_job_id, event_sequence),
  CHECK (length(state_event_id) BETWEEN 1 AND 64
     AND state_event_id NOT GLOB '*[^A-Za-z0-9:_-]*'),
  CHECK (datetime(recorded_at) >= datetime(occurred_at)),
  CHECK (length(evidence_ref_hash) = 64
     AND evidence_ref_hash = lower(evidence_ref_hash)
     AND evidence_ref_hash NOT GLOB '*[^0-9a-f]*'),
  FOREIGN KEY (service_job_id) REFERENCES service_job(service_job_id)
) STRICT;

-- One row per step attempt. It is not the current job state.
CREATE TABLE job_step_run (
  job_step_run_id TEXT PRIMARY KEY,
  service_job_id TEXT NOT NULL,
  offer_version_id TEXT NOT NULL,
  step_code TEXT NOT NULL,
  run_ordinal INTEGER NOT NULL CHECK (run_ordinal >= 1),
  execution_kind TEXT NOT NULL CHECK (execution_kind IN (
    'human','deterministic_tool','ai_assist','ai_agent'
  )),
  delivery_mode_code TEXT NOT NULL,
  delegation_level_code TEXT NOT NULL,
  run_state TEXT NOT NULL CHECK (run_state IN ('started','completed','failed','aborted')),
  result_state TEXT NOT NULL CHECK (result_state IN ('pass','fail','unknown','not_applicable')),
  is_rework INTEGER NOT NULL CHECK (is_rework IN (0,1)),
  input_ref_hash TEXT NOT NULL,
  output_ref_hash TEXT,
  started_at TEXT NOT NULL CHECK (datetime(started_at) IS NOT NULL),
  completed_at TEXT CHECK (completed_at IS NULL OR datetime(completed_at) IS NOT NULL),
  recorded_at TEXT NOT NULL CHECK (datetime(recorded_at) IS NOT NULL),
  UNIQUE (service_job_id, step_code, run_ordinal),
  UNIQUE (job_step_run_id, service_job_id),
  UNIQUE (job_step_run_id, service_job_id, offer_version_id, step_code),
  UNIQUE (job_step_run_id, service_job_id, step_code),
  CHECK (length(job_step_run_id) BETWEEN 1 AND 64
     AND job_step_run_id NOT GLOB '*[^A-Za-z0-9:_-]*'),
  CHECK (
    (run_state = 'started' AND completed_at IS NULL AND result_state = 'unknown')
    OR (run_state <> 'started' AND completed_at IS NOT NULL)
  ),
  CHECK (completed_at IS NULL OR datetime(completed_at) >= datetime(started_at)),
  CHECK (datetime(recorded_at) >= datetime(coalesce(completed_at,started_at))),
  CHECK (length(input_ref_hash) = 64
     AND input_ref_hash = lower(input_ref_hash)
     AND input_ref_hash NOT GLOB '*[^0-9a-f]*'),
  CHECK (output_ref_hash IS NULL OR (
    length(output_ref_hash) = 64
    AND output_ref_hash = lower(output_ref_hash)
    AND output_ref_hash NOT GLOB '*[^0-9a-f]*'
  )),
  FOREIGN KEY (service_job_id, offer_version_id)
    REFERENCES service_job(service_job_id, offer_version_id),
  FOREIGN KEY (offer_version_id, step_code)
    REFERENCES offer_standard_step(offer_version_id, step_code),
  FOREIGN KEY (delivery_mode_code) REFERENCES delivery_mode_dim(delivery_mode_code),
  FOREIGN KEY (delegation_level_code) REFERENCES delegation_level_dim(delegation_level_code)
) STRICT;

-- Labor costs live here once. Non-labor variable costs use variable_cost_event.
CREATE TABLE human_work_log (
  human_work_log_id TEXT PRIMARY KEY,
  service_job_id TEXT NOT NULL,
  job_step_run_id TEXT,
  worker_class TEXT NOT NULL CHECK (worker_class IN ('founder','contractor','customer')),
  work_class TEXT NOT NULL CHECK (work_class IN (
    'standard_delivery','planned_review','correction','support','incident',
    'founder_rescue','customer_review','onboarding','sales'
  )),
  minutes INTEGER NOT NULL CHECK (minutes > 0),
  internal_rate_micros_per_minute INTEGER NOT NULL CHECK (internal_rate_micros_per_minute >= 0),
  cash_cost_micros INTEGER NOT NULL CHECK (cash_cost_micros >= 0),
  source_event_ref_hash TEXT NOT NULL UNIQUE,
  occurred_at TEXT NOT NULL CHECK (datetime(occurred_at) IS NOT NULL),
  recorded_at TEXT NOT NULL CHECK (datetime(recorded_at) IS NOT NULL),
  CHECK (length(human_work_log_id) BETWEEN 1 AND 64
     AND human_work_log_id NOT GLOB '*[^A-Za-z0-9:_-]*'),
  CHECK (datetime(recorded_at) >= datetime(occurred_at)),
  CHECK (
    (worker_class = 'founder' AND internal_rate_micros_per_minute > 0 AND cash_cost_micros = 0)
    OR (worker_class = 'contractor' AND internal_rate_micros_per_minute = 0)
    OR (worker_class = 'customer' AND internal_rate_micros_per_minute = 0 AND cash_cost_micros = 0)
  ),
  CHECK (
    work_class IN ('onboarding','support','sales')
    OR job_step_run_id IS NOT NULL
  ),
  CHECK (length(source_event_ref_hash) = 64
     AND source_event_ref_hash = lower(source_event_ref_hash)
     AND source_event_ref_hash NOT GLOB '*[^0-9a-f]*'),
  FOREIGN KEY (service_job_id) REFERENCES service_job(service_job_id),
  FOREIGN KEY (job_step_run_id, service_job_id)
    REFERENCES job_step_run(job_step_run_id, service_job_id)
) STRICT;

CREATE TABLE variable_cost_event (
  variable_cost_event_id TEXT PRIMARY KEY,
  service_job_id TEXT NOT NULL,
  commercial_cycle_id TEXT NOT NULL,
  cost_class TEXT NOT NULL CHECK (cost_class IN (
    'provider','infrastructure','payment_fee','materials','other_nonlabor'
  )),
  amount_micros INTEGER NOT NULL CHECK (amount_micros >= 0),
  currency TEXT NOT NULL CHECK (length(currency) = 3 AND currency = upper(currency)),
  source_event_ref_hash TEXT NOT NULL UNIQUE,
  occurred_at TEXT NOT NULL CHECK (datetime(occurred_at) IS NOT NULL),
  recorded_at TEXT NOT NULL CHECK (datetime(recorded_at) IS NOT NULL),
  CHECK (length(variable_cost_event_id) BETWEEN 1 AND 64
     AND variable_cost_event_id NOT GLOB '*[^A-Za-z0-9:_-]*'),
  CHECK (datetime(recorded_at) >= datetime(occurred_at)),
  CHECK (length(source_event_ref_hash) = 64
     AND source_event_ref_hash = lower(source_event_ref_hash)
     AND source_event_ref_hash NOT GLOB '*[^0-9a-f]*'),
  FOREIGN KEY (service_job_id, commercial_cycle_id)
    REFERENCES service_job(service_job_id, commercial_cycle_id),
  FOREIGN KEY (commercial_cycle_id) REFERENCES commercial_cycle(commercial_cycle_id),
  FOREIGN KEY (currency) REFERENCES model_settings(base_currency)
) STRICT;

-- Each exception_key is an append-only event stream. Cause and treatment use
-- bounded chapter vocabulary; treatment changes are new events, never updates.
CREATE TABLE exception_event (
  exception_event_id TEXT PRIMARY KEY,
  exception_key TEXT NOT NULL,
  service_job_id TEXT NOT NULL,
  job_step_run_id TEXT NOT NULL,
  step_code TEXT NOT NULL,
  event_sequence INTEGER NOT NULL CHECK (event_sequence >= 1),
  event_type TEXT NOT NULL CHECK (event_type IN ('opened','reclassified','resolved','reopened')),
  cause_class TEXT NOT NULL,
  treatment_code TEXT NOT NULL,
  severity TEXT NOT NULL CHECK (severity IN ('low','medium','high','critical','unknown')),
  responsibility_code TEXT NOT NULL CHECK (responsibility_code IN (
    'founder_process','product','provider','customer_dependency','shared','unknown'
  )),
  nonwaivable INTEGER NOT NULL CHECK (nonwaivable IN (0,1)),
  evidence_ref_hash 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),
  UNIQUE (exception_key, event_sequence),
  CHECK (length(exception_event_id) BETWEEN 1 AND 64
     AND exception_event_id NOT GLOB '*[^A-Za-z0-9:_-]*'),
  CHECK (length(exception_key) BETWEEN 1 AND 64
     AND exception_key NOT GLOB '*[^A-Za-z0-9:_-]*'),
  CHECK (length(step_code) BETWEEN 1 AND 50
     AND step_code = upper(step_code)
     AND step_code NOT GLOB '*[^A-Z0-9_]*'),
  CHECK (datetime(recorded_at) >= datetime(occurred_at)),
  CHECK (length(evidence_ref_hash) = 64
     AND evidence_ref_hash = lower(evidence_ref_hash)
     AND evidence_ref_hash NOT GLOB '*[^0-9a-f]*'),
  FOREIGN KEY (job_step_run_id,service_job_id,step_code)
    REFERENCES job_step_run(job_step_run_id,service_job_id,step_code),
  FOREIGN KEY (cause_class) REFERENCES exception_cause_dim(cause_class),
  FOREIGN KEY (treatment_code) REFERENCES exception_treatment_dim(treatment_code)
) STRICT;

-- Contractual acceptance and realized value are independent append-only streams.
CREATE TABLE acceptance_event (
  acceptance_event_id TEXT PRIMARY KEY,
  acceptance_key TEXT NOT NULL,
  service_job_id TEXT NOT NULL,
  event_sequence INTEGER NOT NULL CHECK (event_sequence >= 1),
  event_type TEXT NOT NULL CHECK (event_type IN ('observed','retracted')),
  acceptance_state TEXT NOT NULL CHECK (acceptance_state IN ('accepted','rejected','unknown')),
  acceptance_source TEXT NOT NULL CHECK (acceptance_source IN (
    'customer_owner','customer_user','contract_oracle','provider_proxy'
  )),
  evidence_ref_hash 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),
  UNIQUE (service_job_id,event_sequence),
  CHECK (acceptance_key = 'ACCEPTANCE:' || service_job_id),
  CHECK (length(acceptance_event_id) BETWEEN 1 AND 64
     AND acceptance_event_id NOT GLOB '*[^A-Za-z0-9:_-]*'),
  CHECK (event_type <> 'retracted' OR acceptance_state = 'unknown'),
  CHECK (datetime(recorded_at) >= datetime(occurred_at)),
  CHECK (length(evidence_ref_hash) = 64
     AND evidence_ref_hash = lower(evidence_ref_hash)
     AND evidence_ref_hash NOT GLOB '*[^0-9a-f]*'),
  FOREIGN KEY (service_job_id) REFERENCES service_job(service_job_id)
) STRICT;

-- Value confirmation is not acceptance and is never inferred from completion,
-- invoice, or cash collection. One job has exactly one VALUE: stream identity.
CREATE TABLE value_confirmation (
  value_confirmation_event_id TEXT PRIMARY KEY,
  confirmation_key TEXT NOT NULL,
  service_job_id TEXT NOT NULL,
  event_sequence INTEGER NOT NULL CHECK (event_sequence >= 1),
  event_type TEXT NOT NULL CHECK (event_type IN ('observed','retracted')),
  value_state TEXT NOT NULL CHECK (value_state IN ('confirmed','not_confirmed','unknown')),
  confirmation_source TEXT NOT NULL CHECK (confirmation_source IN (
    'customer_owner','customer_user','provider_proxy','system_check'
  )),
  independence_state TEXT NOT NULL CHECK (independence_state IN (
    'independent','assisted','not_applicable','unknown'
  )),
  evidence_ref_hash 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),
  UNIQUE (service_job_id, event_sequence),
  CHECK (confirmation_key = 'VALUE:' || service_job_id),
  CHECK (length(value_confirmation_event_id) BETWEEN 1 AND 64
     AND value_confirmation_event_id NOT GLOB '*[^A-Za-z0-9:_-]*'),
  CHECK (event_type <> 'retracted' OR value_state = 'unknown'),
  CHECK (datetime(recorded_at) >= datetime(occurred_at)),
  CHECK (length(evidence_ref_hash) = 64
     AND evidence_ref_hash = lower(evidence_ref_hash)
     AND evidence_ref_hash NOT GLOB '*[^0-9a-f]*'),
  FOREIGN KEY (service_job_id) REFERENCES service_job(service_job_id)
) STRICT;

CREATE TABLE automation_candidate (
  automation_candidate_id TEXT PRIMARY KEY,
  offer_version_id TEXT NOT NULL,
  step_code TEXT NOT NULL,
  commercial_motion_code TEXT NOT NULL,
  target_delivery_mode_code TEXT NOT NULL,
  target_delegation_level_code TEXT NOT NULL,
  candidate_risk_tier_code TEXT NOT NULL,
  min_mature_source_jobs INTEGER NOT NULL CHECK (min_mature_source_jobs >= 1),
  min_contracted_accepted_outcomes INTEGER NOT NULL
    CHECK (min_contracted_accepted_outcomes >= 1),
  min_exception_evidence_jobs INTEGER NOT NULL CHECK (min_exception_evidence_jobs >= 0),
  min_standard_scope_rate_ppm INTEGER NOT NULL CHECK (min_standard_scope_rate_ppm BETWEEN 0 AND 1000000),
  min_rescue_sample_jobs INTEGER NOT NULL CHECK (min_rescue_sample_jobs >= 1),
  max_p90_rescue_minutes INTEGER NOT NULL CHECK (max_p90_rescue_minutes >= 0),
  max_any_rescue_minutes INTEGER NOT NULL CHECK (max_any_rescue_minutes >= max_p90_rescue_minutes),
  max_open_high_exceptions INTEGER NOT NULL CHECK (max_open_high_exceptions >= 0),
  min_consented_migrations INTEGER NOT NULL CHECK (min_consented_migrations >= 0),
  requires_rollback_evidence INTEGER NOT NULL CHECK (requires_rollback_evidence IN (0,1)),
  requires_owner_approval INTEGER NOT NULL CHECK (requires_owner_approval IN (0,1)),
  hypothesis_ref_hash TEXT NOT NULL,
  created_at TEXT NOT NULL CHECK (datetime(created_at) IS NOT NULL),
  recorded_at TEXT NOT NULL CHECK (datetime(recorded_at) IS NOT NULL),
  UNIQUE (
    automation_candidate_id,offer_version_id,step_code,commercial_motion_code
  ),
  CHECK (length(automation_candidate_id) BETWEEN 1 AND 64
     AND automation_candidate_id NOT GLOB '*[^A-Za-z0-9:_-]*'),
  CHECK (datetime(recorded_at) >= datetime(created_at)),
  CHECK (length(hypothesis_ref_hash) = 64
     AND hypothesis_ref_hash = lower(hypothesis_ref_hash)
     AND hypothesis_ref_hash NOT GLOB '*[^0-9a-f]*'),
  FOREIGN KEY (offer_version_id, step_code)
    REFERENCES offer_standard_step(offer_version_id, step_code),
  FOREIGN KEY (commercial_motion_code) REFERENCES commercial_motion_dim(commercial_motion_code),
  FOREIGN KEY (target_delivery_mode_code) REFERENCES delivery_mode_dim(delivery_mode_code),
  FOREIGN KEY (target_delegation_level_code) REFERENCES delegation_level_dim(delegation_level_code),
  FOREIGN KEY (candidate_risk_tier_code) REFERENCES risk_tier_dim(risk_tier_code)
) STRICT;

CREATE TABLE candidate_source_job (
  automation_candidate_id TEXT NOT NULL,
  service_job_id TEXT NOT NULL,
  evidence_role TEXT NOT NULL CHECK (evidence_role IN (
    'positive','boundary','exception','failure'
  )),
  evidence_ref_hash TEXT NOT NULL,
  linked_at TEXT NOT NULL CHECK (datetime(linked_at) IS NOT NULL),
  recorded_at TEXT NOT NULL CHECK (datetime(recorded_at) IS NOT NULL),
  PRIMARY KEY (automation_candidate_id, service_job_id),
  CHECK (datetime(recorded_at) >= datetime(linked_at)),
  CHECK (length(automation_candidate_id) BETWEEN 1 AND 64
     AND automation_candidate_id NOT GLOB '*[^A-Za-z0-9:_-]*'),
  CHECK (length(service_job_id) BETWEEN 1 AND 64
     AND service_job_id NOT GLOB '*[^A-Za-z0-9:_-]*'),
  CHECK (length(evidence_ref_hash) = 64
     AND evidence_ref_hash = lower(evidence_ref_hash)
     AND evidence_ref_hash NOT GLOB '*[^0-9a-f]*'),
  FOREIGN KEY (automation_candidate_id) REFERENCES automation_candidate(automation_candidate_id),
  FOREIGN KEY (service_job_id) REFERENCES service_job(service_job_id)
) STRICT;

-- Canonical opportunity link. A job-level source row alone is not evidence: the
-- exact candidate step attempt must bind to the same offer and motion.
CREATE TABLE candidate_source_step_run (
  automation_candidate_id TEXT NOT NULL,
  service_job_id TEXT NOT NULL,
  job_step_run_id TEXT NOT NULL,
  offer_version_id TEXT NOT NULL,
  step_code TEXT NOT NULL,
  commercial_motion_code TEXT NOT NULL,
  linked_at TEXT NOT NULL CHECK (datetime(linked_at) IS NOT NULL),
  recorded_at TEXT NOT NULL CHECK (datetime(recorded_at) IS NOT NULL),
  PRIMARY KEY (automation_candidate_id,service_job_id),
  UNIQUE (automation_candidate_id,job_step_run_id),
  CHECK (datetime(recorded_at) >= datetime(linked_at)),
  CHECK (length(job_step_run_id) BETWEEN 1 AND 64
     AND job_step_run_id NOT GLOB '*[^A-Za-z0-9:_-]*'),
  FOREIGN KEY (automation_candidate_id,service_job_id)
    REFERENCES candidate_source_job(automation_candidate_id,service_job_id),
  FOREIGN KEY (
    automation_candidate_id,offer_version_id,step_code,commercial_motion_code
  ) REFERENCES automation_candidate(
    automation_candidate_id,offer_version_id,step_code,commercial_motion_code
  ),
  FOREIGN KEY (service_job_id,offer_version_id,commercial_motion_code)
    REFERENCES service_job(service_job_id,offer_version_id,commercial_motion_code),
  FOREIGN KEY (job_step_run_id,service_job_id,offer_version_id,step_code)
    REFERENCES job_step_run(job_step_run_id,service_job_id,offer_version_id,step_code)
) STRICT;

-- A frozen, independently recorded evidence snapshot precedes the human action.
-- Approval, rollback, and pause evidence live here, never on the decision row.
CREATE TABLE promotion_evidence_snapshot (
  promotion_evidence_snapshot_id TEXT PRIMARY KEY,
  automation_candidate_id TEXT NOT NULL,
  snapshot_sequence INTEGER NOT NULL CHECK (snapshot_sequence >= 1),
  exposure_state TEXT NOT NULL CHECK (exposure_state IN (
    'pre_exposure','active_exposure'
  )),
  target_delivery_mode_code TEXT NOT NULL,
  target_delegation_level_code TEXT NOT NULL,
  risk_tier_code TEXT NOT NULL,
  evidence_as_of TEXT NOT NULL CHECK (datetime(evidence_as_of) IS NOT NULL),
  evidence_watermark_at TEXT NOT NULL CHECK (datetime(evidence_watermark_at) IS NOT NULL),
  independent_pause_blocker_code TEXT CHECK (
    independent_pause_blocker_code IS NULL OR independent_pause_blocker_code IN (
      'DATA_RIGHTS','ACCEPTANCE_ORACLE','OWNER','ROLLBACK_COMPENSATION',
      'REPRESENTATIVE_INPUT','MAX_LOSS_BOUNDARY','STALE_SAFETY_AUTHORITY_FALLBACK'
    )
  ),
  rollback_evidence_ref_hash TEXT,
  owner_approval_ref_hash TEXT,
  snapshot_ref_hash 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),
  UNIQUE (automation_candidate_id,snapshot_sequence),
  UNIQUE (promotion_evidence_snapshot_id,automation_candidate_id),
  UNIQUE (
    promotion_evidence_snapshot_id,automation_candidate_id,
    target_delivery_mode_code,target_delegation_level_code,risk_tier_code
  ),
  CHECK (length(promotion_evidence_snapshot_id) BETWEEN 1 AND 64
     AND promotion_evidence_snapshot_id NOT GLOB '*[^A-Za-z0-9:_-]*'),
  CHECK (datetime(evidence_watermark_at) <= datetime(evidence_as_of)),
  CHECK (datetime(evidence_as_of) <= datetime(occurred_at)),
  CHECK (datetime(recorded_at) >= datetime(occurred_at)),
  CHECK (rollback_evidence_ref_hash IS NULL OR (
    length(rollback_evidence_ref_hash) = 64
    AND rollback_evidence_ref_hash = lower(rollback_evidence_ref_hash)
    AND rollback_evidence_ref_hash NOT GLOB '*[^0-9a-f]*'
  )),
  CHECK (owner_approval_ref_hash IS NULL OR (
    length(owner_approval_ref_hash) = 64
    AND owner_approval_ref_hash = lower(owner_approval_ref_hash)
    AND owner_approval_ref_hash NOT GLOB '*[^0-9a-f]*'
  )),
  CHECK (length(snapshot_ref_hash) = 64
     AND snapshot_ref_hash = lower(snapshot_ref_hash)
     AND snapshot_ref_hash NOT GLOB '*[^0-9a-f]*'),
  FOREIGN KEY (automation_candidate_id) REFERENCES automation_candidate(automation_candidate_id),
  FOREIGN KEY (target_delivery_mode_code) REFERENCES delivery_mode_dim(delivery_mode_code),
  FOREIGN KEY (target_delegation_level_code) REFERENCES delegation_level_dim(delegation_level_code),
  FOREIGN KEY (risk_tier_code) REFERENCES risk_tier_dim(risk_tier_code)
) STRICT;

-- A cutoff pointer alone is not a frozen snapshot: later backdated source rows
-- could otherwise rewrite an already-recorded decision. This immutable child
-- materializes the complete derived candidate metric vector before any action.
CREATE TABLE promotion_metric_snapshot (
  promotion_evidence_snapshot_id TEXT PRIMARY KEY,
  automation_candidate_id TEXT NOT NULL,
  workflow_id TEXT NOT NULL,
  workflow_version INTEGER NOT NULL CHECK (workflow_version >= 1),
  segment_code TEXT NOT NULL,
  data_class_code TEXT NOT NULL CHECK (data_class_code IN (
    'synthetic','internal_nonpersonal','customer_confidential_nonpersonal','personal_data_prohibited'
  )),
  step_code TEXT NOT NULL,
  source_step_delivery_mode_code TEXT NOT NULL,
  commercial_motion_code TEXT NOT NULL,
  visible_source_jobs INTEGER NOT NULL CHECK (visible_source_jobs >= 0),
  mature_source_jobs INTEGER NOT NULL CHECK (mature_source_jobs >= 0),
  mature_standard_scope_jobs INTEGER NOT NULL CHECK (mature_standard_scope_jobs >= 0),
  standard_scope_rate_ppm INTEGER CHECK (standard_scope_rate_ppm BETWEEN 0 AND 1000000),
  accepted_outcomes INTEGER NOT NULL CHECK (accepted_outcomes >= 0),
  value_confirmed_outcomes INTEGER NOT NULL CHECK (value_confirmed_outcomes >= 0),
  contracted_accepted_outcomes INTEGER NOT NULL CHECK (contracted_accepted_outcomes >= 0),
  cash_collected_value_confirmed_outcomes INTEGER NOT NULL
    CHECK (cash_collected_value_confirmed_outcomes >= 0),
  value_confirmed_without_cash_outcomes INTEGER NOT NULL
    CHECK (value_confirmed_without_cash_outcomes >= 0),
  exception_evidence_jobs INTEGER NOT NULL CHECK (exception_evidence_jobs >= 0),
  rescue_sample_jobs INTEGER NOT NULL CHECK (rescue_sample_jobs >= 0),
  p90_rescue_minutes INTEGER CHECK (p90_rescue_minutes >= 0),
  max_rescue_minutes INTEGER CHECK (max_rescue_minutes >= 0),
  open_high_exceptions INTEGER NOT NULL CHECK (open_high_exceptions >= 0),
  open_critical_exceptions INTEGER NOT NULL CHECK (open_critical_exceptions >= 0),
  open_nonwaivable_exceptions INTEGER NOT NULL CHECK (open_nonwaivable_exceptions >= 0),
  open_security_exceptions INTEGER NOT NULL CHECK (open_security_exceptions >= 0),
  open_unknown_exceptions INTEGER NOT NULL CHECK (open_unknown_exceptions >= 0),
  consented_migrations INTEGER NOT NULL CHECK (consented_migrations >= 0),
  verified_migrations INTEGER NOT NULL CHECK (verified_migrations >= 0),
  metric_snapshot_ref_hash TEXT NOT NULL,
  materialized_at TEXT NOT NULL CHECK (datetime(materialized_at) IS NOT NULL),
  CHECK (mature_source_jobs <= visible_source_jobs),
  CHECK (mature_standard_scope_jobs <= mature_source_jobs),
  CHECK (accepted_outcomes <= mature_source_jobs),
  CHECK (value_confirmed_outcomes <= accepted_outcomes),
  CHECK (contracted_accepted_outcomes <= accepted_outcomes),
  CHECK (cash_collected_value_confirmed_outcomes <= value_confirmed_outcomes),
  CHECK (value_confirmed_without_cash_outcomes
         = value_confirmed_outcomes - cash_collected_value_confirmed_outcomes),
  CHECK (length(workflow_id) BETWEEN 1 AND 50
     AND workflow_id = upper(workflow_id)
     AND workflow_id NOT GLOB '*[^A-Z0-9_]*'),
  CHECK (length(segment_code) BETWEEN 1 AND 40
     AND segment_code = upper(segment_code)
     AND segment_code NOT GLOB '*[^A-Z0-9_]*'),
  CHECK (length(step_code) BETWEEN 1 AND 50
     AND step_code = upper(step_code)
     AND step_code NOT GLOB '*[^A-Z0-9_]*'),
  CHECK (length(metric_snapshot_ref_hash) = 64
     AND metric_snapshot_ref_hash = lower(metric_snapshot_ref_hash)
     AND metric_snapshot_ref_hash NOT GLOB '*[^0-9a-f]*'),
  FOREIGN KEY (promotion_evidence_snapshot_id,automation_candidate_id)
    REFERENCES promotion_evidence_snapshot(
      promotion_evidence_snapshot_id,automation_candidate_id
    ),
  FOREIGN KEY (source_step_delivery_mode_code)
    REFERENCES delivery_mode_dim(delivery_mode_code),
  FOREIGN KEY (commercial_motion_code)
    REFERENCES commercial_motion_dim(commercial_motion_code)
) STRICT;

CREATE TABLE promotion_gate_evidence (
  promotion_evidence_snapshot_id TEXT NOT NULL,
  domain_code TEXT NOT NULL,
  result_code TEXT NOT NULL CHECK (result_code IN ('PASS','FAIL','UNKNOWN')),
  evidence_ref_hash TEXT NOT NULL,
  observed_at TEXT NOT NULL CHECK (datetime(observed_at) IS NOT NULL),
  recorded_at TEXT NOT NULL CHECK (datetime(recorded_at) IS NOT NULL),
  PRIMARY KEY (promotion_evidence_snapshot_id,domain_code),
  CHECK (datetime(recorded_at) >= datetime(observed_at)),
  CHECK (length(evidence_ref_hash) = 64
     AND evidence_ref_hash = lower(evidence_ref_hash)
     AND evidence_ref_hash NOT GLOB '*[^0-9a-f]*'),
  FOREIGN KEY (promotion_evidence_snapshot_id)
    REFERENCES promotion_evidence_snapshot(promotion_evidence_snapshot_id),
  FOREIGN KEY (domain_code) REFERENCES promotion_gate_domain_dim(domain_code)
) STRICT;

-- Recorded action remains immutable and points to a prior independent snapshot.
-- UNKNOWN is a gate state, never a human action; MAINTAIN is an action.
CREATE TABLE promotion_decision (
  promotion_decision_id TEXT PRIMARY KEY,
  automation_candidate_id TEXT NOT NULL,
  promotion_evidence_snapshot_id TEXT NOT NULL UNIQUE,
  decision_sequence INTEGER NOT NULL CHECK (decision_sequence >= 1),
  decision_code TEXT NOT NULL CHECK (decision_code IN (
    'PROMOTE','LIMITED_CANARY','MAINTAIN','HOLD','REVERSE','PAUSE','STOP'
  )),
  target_delivery_mode_code TEXT NOT NULL,
  target_delegation_level_code TEXT NOT NULL,
  risk_tier_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),
  UNIQUE (automation_candidate_id, decision_sequence),
  CHECK (length(promotion_decision_id) BETWEEN 1 AND 64
     AND promotion_decision_id NOT GLOB '*[^A-Za-z0-9:_-]*'),
  CHECK (datetime(recorded_at) >= datetime(occurred_at)),
  FOREIGN KEY (
    promotion_evidence_snapshot_id,automation_candidate_id,
    target_delivery_mode_code,target_delegation_level_code,risk_tier_code
  ) REFERENCES promotion_evidence_snapshot(
    promotion_evidence_snapshot_id,automation_candidate_id,
    target_delivery_mode_code,target_delegation_level_code,risk_tier_code
  ),
  FOREIGN KEY (decision_code) REFERENCES promotion_action_dim(action_code)
) STRICT;

-- One migration_id is an append-only stream, including consent and rollback.
CREATE TABLE customer_migration (
  customer_migration_event_id TEXT PRIMARY KEY,
  migration_id TEXT NOT NULL,
  automation_candidate_id TEXT NOT NULL,
  account_id TEXT NOT NULL,
  from_offer_version_id TEXT NOT NULL,
  to_offer_version_id TEXT NOT NULL,
  event_sequence INTEGER NOT NULL CHECK (event_sequence >= 1),
  migration_state TEXT NOT NULL CHECK (migration_state IN (
    'proposed','consented','scheduled','migrated','verified','declined','rolled_back'
  )),
  consent_evidence_ref_hash TEXT,
  evidence_ref_hash 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),
  UNIQUE (migration_id, event_sequence),
  CHECK (length(customer_migration_event_id) BETWEEN 1 AND 64
     AND customer_migration_event_id NOT GLOB '*[^A-Za-z0-9:_-]*'),
  CHECK (length(migration_id) BETWEEN 1 AND 64
     AND migration_id NOT GLOB '*[^A-Za-z0-9:_-]*'),
  CHECK (from_offer_version_id <> to_offer_version_id),
  CHECK (
    migration_state IN ('proposed','declined')
    OR consent_evidence_ref_hash IS NOT NULL
  ),
  CHECK (datetime(recorded_at) >= datetime(occurred_at)),
  CHECK (consent_evidence_ref_hash IS NULL OR (
    length(consent_evidence_ref_hash) = 64
    AND consent_evidence_ref_hash = lower(consent_evidence_ref_hash)
    AND consent_evidence_ref_hash NOT GLOB '*[^0-9a-f]*'
  )),
  CHECK (length(evidence_ref_hash) = 64
     AND evidence_ref_hash = lower(evidence_ref_hash)
     AND evidence_ref_hash NOT GLOB '*[^0-9a-f]*'),
  FOREIGN KEY (automation_candidate_id) REFERENCES automation_candidate(automation_candidate_id),
  FOREIGN KEY (account_id) REFERENCES customer_account(account_id),
  FOREIGN KEY (from_offer_version_id) REFERENCES offer_version(offer_version_id),
  FOREIGN KEY (to_offer_version_id) REFERENCES offer_version(offer_version_id)
) STRICT;

-- Weekly review is a decision event plus an evidence-snapshot pointer, not a
-- mutable dashboard row. The four axes remain explicit.
CREATE TABLE weekly_review (
  weekly_review_event_id TEXT PRIMARY KEY,
  review_key TEXT NOT NULL,
  review_sequence INTEGER NOT NULL CHECK (review_sequence >= 1),
  review_week_start TEXT NOT NULL CHECK (
    length(review_week_start) = 10 AND date(review_week_start) IS NOT NULL
  ),
  offer_version_id TEXT NOT NULL,
  automation_candidate_id TEXT NOT NULL,
  commercial_motion_code TEXT NOT NULL,
  delivery_mode_code TEXT NOT NULL,
  delegation_level_code TEXT NOT NULL,
  risk_tier_code TEXT NOT NULL,
  evidence_as_of TEXT NOT NULL CHECK (datetime(evidence_as_of) IS NOT NULL),
  evidence_watermark_at TEXT NOT NULL CHECK (datetime(evidence_watermark_at) IS NOT NULL),
  review_decision_code TEXT NOT NULL CHECK (review_decision_code IN (
    'continue','change','pause','stop','promote'
  )),
  bottleneck_code TEXT NOT NULL CHECK (bottleneck_code IN (
    'scope','rescue','exception','value','payment','migration','none'
  )),
  next_action_code TEXT NOT NULL CHECK (next_action_code IN (
    'observe','reduce_scope','improve_tool','collect_payment',
    'migrate','rollback','stop'
  )),
  snapshot_ref_hash 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),
  UNIQUE (review_key, review_sequence),
  CHECK (length(weekly_review_event_id) BETWEEN 1 AND 64
     AND weekly_review_event_id NOT GLOB '*[^A-Za-z0-9:_-]*'),
  CHECK (length(review_key) BETWEEN 1 AND 96
     AND review_key NOT GLOB '*[^A-Za-z0-9:_-]*'),
  CHECK (datetime(evidence_watermark_at) <= datetime(evidence_as_of)),
  CHECK (datetime(evidence_as_of) <= datetime(occurred_at)),
  CHECK (datetime(recorded_at) >= datetime(occurred_at)),
  CHECK (length(snapshot_ref_hash) = 64
     AND snapshot_ref_hash = lower(snapshot_ref_hash)
     AND snapshot_ref_hash NOT GLOB '*[^0-9a-f]*'),
  FOREIGN KEY (offer_version_id) REFERENCES offer_version(offer_version_id),
  FOREIGN KEY (automation_candidate_id) REFERENCES automation_candidate(automation_candidate_id),
  FOREIGN KEY (commercial_motion_code) REFERENCES commercial_motion_dim(commercial_motion_code),
  FOREIGN KEY (delivery_mode_code) REFERENCES delivery_mode_dim(delivery_mode_code),
  FOREIGN KEY (delegation_level_code) REFERENCES delegation_level_dim(delegation_level_code),
  FOREIGN KEY (risk_tier_code) REFERENCES risk_tier_dim(risk_tier_code)
) STRICT;

-- Append-only guards. Corrections are new events/versions, never UPDATE/DELETE.
CREATE TRIGGER immutable_commercial_motion_update BEFORE UPDATE ON commercial_motion_dim
BEGIN SELECT RAISE(ABORT, 'commercial_motion_dim is immutable'); END;
CREATE TRIGGER immutable_commercial_motion_delete BEFORE DELETE ON commercial_motion_dim
BEGIN SELECT RAISE(ABORT, 'commercial_motion_dim is immutable'); END;
CREATE TRIGGER immutable_delivery_mode_update BEFORE UPDATE ON delivery_mode_dim
BEGIN SELECT RAISE(ABORT, 'delivery_mode_dim is immutable'); END;
CREATE TRIGGER immutable_delivery_mode_delete BEFORE DELETE ON delivery_mode_dim
BEGIN SELECT RAISE(ABORT, 'delivery_mode_dim is immutable'); END;
CREATE TRIGGER immutable_delegation_level_update BEFORE UPDATE ON delegation_level_dim
BEGIN SELECT RAISE(ABORT, 'delegation_level_dim is immutable'); END;
CREATE TRIGGER immutable_delegation_level_delete BEFORE DELETE ON delegation_level_dim
BEGIN SELECT RAISE(ABORT, 'delegation_level_dim is immutable'); END;
CREATE TRIGGER immutable_risk_tier_update BEFORE UPDATE ON risk_tier_dim
BEGIN SELECT RAISE(ABORT, 'risk_tier_dim is immutable'); END;
CREATE TRIGGER immutable_risk_tier_delete BEFORE DELETE ON risk_tier_dim
BEGIN SELECT RAISE(ABORT, 'risk_tier_dim is immutable'); END;
CREATE TRIGGER immutable_promotion_action_update BEFORE UPDATE ON promotion_action_dim
BEGIN SELECT RAISE(ABORT, 'promotion_action_dim is immutable'); END;
CREATE TRIGGER immutable_promotion_action_delete BEFORE DELETE ON promotion_action_dim
BEGIN SELECT RAISE(ABORT, 'promotion_action_dim is immutable'); END;
CREATE TRIGGER immutable_exception_cause_update BEFORE UPDATE ON exception_cause_dim
BEGIN SELECT RAISE(ABORT, 'exception_cause_dim is immutable'); END;
CREATE TRIGGER immutable_exception_cause_delete BEFORE DELETE ON exception_cause_dim
BEGIN SELECT RAISE(ABORT, 'exception_cause_dim is immutable'); END;
CREATE TRIGGER immutable_exception_treatment_update BEFORE UPDATE ON exception_treatment_dim
BEGIN SELECT RAISE(ABORT, 'exception_treatment_dim is immutable'); END;
CREATE TRIGGER immutable_exception_treatment_delete BEFORE DELETE ON exception_treatment_dim
BEGIN SELECT RAISE(ABORT, 'exception_treatment_dim is immutable'); END;
CREATE TRIGGER immutable_customer_account_update BEFORE UPDATE ON customer_account
BEGIN SELECT RAISE(ABORT, 'customer_account is immutable'); END;
CREATE TRIGGER immutable_customer_account_delete BEFORE DELETE ON customer_account
BEGIN SELECT RAISE(ABORT, 'customer_account is immutable'); END;
CREATE TRIGGER immutable_gate_domain_update BEFORE UPDATE ON promotion_gate_domain_dim
BEGIN SELECT RAISE(ABORT, 'promotion_gate_domain_dim is immutable'); END;
CREATE TRIGGER immutable_gate_domain_delete BEFORE DELETE ON promotion_gate_domain_dim
BEGIN SELECT RAISE(ABORT, 'promotion_gate_domain_dim is immutable'); END;
CREATE TRIGGER immutable_required_gate_update BEFORE UPDATE ON delivery_mode_required_gate_domain
BEGIN SELECT RAISE(ABORT, 'delivery_mode_required_gate_domain is immutable'); END;
CREATE TRIGGER immutable_required_gate_delete BEFORE DELETE ON delivery_mode_required_gate_domain
BEGIN SELECT RAISE(ABORT, 'delivery_mode_required_gate_domain is immutable'); END;
CREATE TRIGGER freeze_required_gate_insert_after_snapshot
BEFORE INSERT ON delivery_mode_required_gate_domain
WHEN EXISTS (
  SELECT 1 FROM promotion_evidence_snapshot p
  WHERE p.target_delivery_mode_code = NEW.delivery_mode_code
)
BEGIN SELECT RAISE(ABORT, 'required gate policy is frozen after first snapshot'); END;
CREATE TRIGGER immutable_workflow_scope_update BEFORE UPDATE ON workflow_scope_version
BEGIN SELECT RAISE(ABORT, 'workflow_scope_version is immutable'); END;
CREATE TRIGGER immutable_workflow_scope_delete BEFORE DELETE ON workflow_scope_version
BEGIN SELECT RAISE(ABORT, 'workflow_scope_version is immutable'); END;
CREATE TRIGGER immutable_offer_version_update BEFORE UPDATE ON offer_version
BEGIN SELECT RAISE(ABORT, 'offer_version is immutable'); END;
CREATE TRIGGER immutable_offer_version_delete BEFORE DELETE ON offer_version
BEGIN SELECT RAISE(ABORT, 'offer_version is immutable'); END;
CREATE TRIGGER immutable_offer_step_update BEFORE UPDATE ON offer_standard_step
BEGIN SELECT RAISE(ABORT, 'offer_standard_step is immutable'); END;
CREATE TRIGGER immutable_offer_step_delete BEFORE DELETE ON offer_standard_step
BEGIN SELECT RAISE(ABORT, 'offer_standard_step is immutable'); END;
CREATE TRIGGER immutable_commercial_cycle_update BEFORE UPDATE ON commercial_cycle
BEGIN SELECT RAISE(ABORT, 'commercial_cycle is immutable'); END;
CREATE TRIGGER immutable_commercial_cycle_delete BEFORE DELETE ON commercial_cycle
BEGIN SELECT RAISE(ABORT, 'commercial_cycle is immutable'); END;
CREATE TRIGGER immutable_commercial_event_update BEFORE UPDATE ON commercial_event
BEGIN SELECT RAISE(ABORT, 'commercial_event is immutable'); END;
CREATE TRIGGER immutable_commercial_event_delete BEFORE DELETE ON commercial_event
BEGIN SELECT RAISE(ABORT, 'commercial_event is immutable'); END;
CREATE TRIGGER immutable_service_job_update BEFORE UPDATE ON service_job
BEGIN SELECT RAISE(ABORT, 'service_job is immutable'); END;
CREATE TRIGGER immutable_service_job_delete BEFORE DELETE ON service_job
BEGIN SELECT RAISE(ABORT, 'service_job is immutable'); END;
CREATE TRIGGER immutable_job_state_update BEFORE UPDATE ON service_job_state_event
BEGIN SELECT RAISE(ABORT, 'service_job_state_event is immutable'); END;
CREATE TRIGGER immutable_job_state_delete BEFORE DELETE ON service_job_state_event
BEGIN SELECT RAISE(ABORT, 'service_job_state_event is immutable'); END;
CREATE TRIGGER immutable_step_run_update BEFORE UPDATE ON job_step_run
BEGIN SELECT RAISE(ABORT, 'job_step_run is immutable'); END;
CREATE TRIGGER immutable_step_run_delete BEFORE DELETE ON job_step_run
BEGIN SELECT RAISE(ABORT, 'job_step_run is immutable'); END;
CREATE TRIGGER immutable_human_work_update BEFORE UPDATE ON human_work_log
BEGIN SELECT RAISE(ABORT, 'human_work_log is immutable'); END;
CREATE TRIGGER immutable_human_work_delete BEFORE DELETE ON human_work_log
BEGIN SELECT RAISE(ABORT, 'human_work_log is immutable'); END;
CREATE TRIGGER immutable_variable_cost_update BEFORE UPDATE ON variable_cost_event
BEGIN SELECT RAISE(ABORT, 'variable_cost_event is immutable'); END;
CREATE TRIGGER immutable_variable_cost_delete BEFORE DELETE ON variable_cost_event
BEGIN SELECT RAISE(ABORT, 'variable_cost_event is immutable'); END;
CREATE TRIGGER immutable_exception_update BEFORE UPDATE ON exception_event
BEGIN SELECT RAISE(ABORT, 'exception_event is immutable'); END;
CREATE TRIGGER immutable_exception_delete BEFORE DELETE ON exception_event
BEGIN SELECT RAISE(ABORT, 'exception_event is immutable'); END;
CREATE TRIGGER immutable_acceptance_update BEFORE UPDATE ON acceptance_event
BEGIN SELECT RAISE(ABORT, 'acceptance_event is immutable'); END;
CREATE TRIGGER immutable_acceptance_delete BEFORE DELETE ON acceptance_event
BEGIN SELECT RAISE(ABORT, 'acceptance_event is immutable'); END;
CREATE TRIGGER immutable_value_update BEFORE UPDATE ON value_confirmation
BEGIN SELECT RAISE(ABORT, 'value_confirmation is immutable'); END;
CREATE TRIGGER immutable_value_delete BEFORE DELETE ON value_confirmation
BEGIN SELECT RAISE(ABORT, 'value_confirmation is immutable'); END;
CREATE TRIGGER immutable_candidate_update BEFORE UPDATE ON automation_candidate
BEGIN SELECT RAISE(ABORT, 'automation_candidate is immutable'); END;
CREATE TRIGGER immutable_candidate_delete BEFORE DELETE ON automation_candidate
BEGIN SELECT RAISE(ABORT, 'automation_candidate is immutable'); END;
CREATE TRIGGER immutable_source_job_update BEFORE UPDATE ON candidate_source_job
BEGIN SELECT RAISE(ABORT, 'candidate_source_job is immutable'); END;
CREATE TRIGGER immutable_source_job_delete BEFORE DELETE ON candidate_source_job
BEGIN SELECT RAISE(ABORT, 'candidate_source_job is immutable'); END;
CREATE TRIGGER immutable_source_step_run_update BEFORE UPDATE ON candidate_source_step_run
BEGIN SELECT RAISE(ABORT, 'candidate_source_step_run is immutable'); END;
CREATE TRIGGER immutable_source_step_run_delete BEFORE DELETE ON candidate_source_step_run
BEGIN SELECT RAISE(ABORT, 'candidate_source_step_run is immutable'); END;
CREATE TRIGGER immutable_promotion_snapshot_update BEFORE UPDATE ON promotion_evidence_snapshot
BEGIN SELECT RAISE(ABORT, 'promotion_evidence_snapshot is immutable'); END;
CREATE TRIGGER immutable_promotion_snapshot_delete BEFORE DELETE ON promotion_evidence_snapshot
BEGIN SELECT RAISE(ABORT, 'promotion_evidence_snapshot is immutable'); END;
CREATE TRIGGER immutable_promotion_metric_snapshot_update BEFORE UPDATE ON promotion_metric_snapshot
BEGIN SELECT RAISE(ABORT, 'promotion_metric_snapshot is immutable'); END;
CREATE TRIGGER immutable_promotion_metric_snapshot_delete BEFORE DELETE ON promotion_metric_snapshot
BEGIN SELECT RAISE(ABORT, 'promotion_metric_snapshot is immutable'); END;
CREATE TRIGGER immutable_promotion_gate_evidence_update BEFORE UPDATE ON promotion_gate_evidence
BEGIN SELECT RAISE(ABORT, 'promotion_gate_evidence is immutable'); END;
CREATE TRIGGER immutable_promotion_gate_evidence_delete BEFORE DELETE ON promotion_gate_evidence
BEGIN SELECT RAISE(ABORT, 'promotion_gate_evidence is immutable'); END;
CREATE TRIGGER immutable_promotion_update BEFORE UPDATE ON promotion_decision
BEGIN SELECT RAISE(ABORT, 'promotion_decision is immutable'); END;
CREATE TRIGGER immutable_promotion_delete BEFORE DELETE ON promotion_decision
BEGIN SELECT RAISE(ABORT, 'promotion_decision is immutable'); END;
CREATE TRIGGER immutable_migration_update BEFORE UPDATE ON customer_migration
BEGIN SELECT RAISE(ABORT, 'customer_migration is immutable'); END;
CREATE TRIGGER immutable_migration_delete BEFORE DELETE ON customer_migration
BEGIN SELECT RAISE(ABORT, 'customer_migration is immutable'); END;
CREATE TRIGGER immutable_weekly_review_update BEFORE UPDATE ON weekly_review
BEGIN SELECT RAISE(ABORT, 'weekly_review is immutable'); END;
CREATE TRIGGER immutable_weekly_review_delete BEFORE DELETE ON weekly_review
BEGIN SELECT RAISE(ABORT, 'weekly_review is immutable'); END;

-- Cross-row invariants that CHECK/FK cannot express in SQLite.
CREATE TRIGGER validate_offer_version_no_overlap_before_insert
BEFORE INSERT ON offer_version
WHEN EXISTS (
  SELECT 1
  FROM offer_version prior
  WHERE prior.offer_code = NEW.offer_code
    AND datetime(prior.effective_from)
        < datetime(coalesce(NEW.effective_to,'9999-12-31T23:59:59Z'))
    AND datetime(NEW.effective_from)
        < datetime(coalesce(prior.effective_to,'9999-12-31T23:59:59Z'))
)
BEGIN SELECT RAISE(ABORT, 'offer versions for one offer_code may not overlap'); END;

CREATE TRIGGER validate_candidate_metadata_before_insert
BEFORE INSERT ON automation_candidate
WHEN NOT EXISTS (
  SELECT 1
  FROM offer_version o
  JOIN offer_standard_step os USING (offer_version_id)
  WHERE o.offer_version_id = NEW.offer_version_id
    AND o.commercial_motion_code = NEW.commercial_motion_code
    AND os.step_code = NEW.step_code
    AND datetime(o.effective_from) <= datetime(NEW.created_at)
    AND datetime(o.recorded_at) <= datetime(NEW.recorded_at)
    AND datetime(os.recorded_at) <= datetime(NEW.recorded_at)
)
BEGIN SELECT RAISE(ABORT, 'candidate metadata was not visible when candidate was recorded'); END;

CREATE TRIGGER validate_offer_step_before_insert
BEFORE INSERT ON offer_standard_step
WHEN NOT EXISTS (
  SELECT 1 FROM offer_version o
  WHERE o.offer_version_id = NEW.offer_version_id
    AND datetime(NEW.recorded_at) >= datetime(o.recorded_at)
    AND datetime(NEW.recorded_at) <= datetime(o.effective_from)
) OR EXISTS (
  SELECT 1 FROM commercial_cycle c WHERE c.offer_version_id = NEW.offer_version_id
) OR EXISTS (
  SELECT 1 FROM service_job j WHERE j.offer_version_id = NEW.offer_version_id
) OR EXISTS (
  SELECT 1 FROM automation_candidate c WHERE c.offer_version_id = NEW.offer_version_id
)
BEGIN SELECT RAISE(ABORT, 'offer steps freeze before offer effectiveness or first use'); END;

CREATE TRIGGER validate_commercial_cycle_offer_before_insert
BEFORE INSERT ON commercial_cycle
WHEN NOT EXISTS (
  SELECT 1
  FROM offer_version o
  JOIN customer_account a ON a.account_id = NEW.account_id
  WHERE o.offer_version_id = NEW.offer_version_id
    AND o.commercial_motion_code = NEW.commercial_motion_code
    AND a.segment_code = o.segment_code
    AND datetime(a.recorded_at) <= datetime(NEW.recorded_at)
    AND datetime(o.recorded_at) <= datetime(NEW.recorded_at)
    AND datetime(o.effective_from) <= datetime(NEW.period_start)
    AND (
      o.effective_to IS NULL
      OR datetime(NEW.period_start) < datetime(o.effective_to)
    )
)
BEGIN SELECT RAISE(ABORT, 'commercial cycle must start within its visible offer interval'); END;

CREATE TRIGGER validate_service_job_cycle_before_insert
BEFORE INSERT ON service_job
WHEN NOT EXISTS (
  SELECT 1
  FROM commercial_cycle c
  WHERE c.commercial_cycle_id = NEW.commercial_cycle_id
    AND c.account_id = NEW.account_id
    AND c.offer_version_id = NEW.offer_version_id
    AND c.commercial_motion_code = NEW.commercial_motion_code
    AND datetime(c.recorded_at) <= datetime(NEW.recorded_at)
    AND datetime(c.period_start) <= datetime(NEW.eligible_at)
    AND datetime(NEW.eligible_at) < datetime(c.period_end)
)
BEGIN SELECT RAISE(ABORT, 'service job must use a visible cycle covering its eligibility time'); END;

CREATE TRIGGER validate_candidate_source_step_run_before_insert
BEFORE INSERT ON candidate_source_step_run
WHEN NOT EXISTS (
  SELECT 1
  FROM automation_candidate c
  JOIN service_job j ON j.service_job_id = NEW.service_job_id
  JOIN job_step_run r ON r.job_step_run_id = NEW.job_step_run_id
  WHERE c.automation_candidate_id = NEW.automation_candidate_id
    AND c.offer_version_id = NEW.offer_version_id
    AND c.step_code = NEW.step_code
    AND c.commercial_motion_code = NEW.commercial_motion_code
    AND j.offer_version_id = NEW.offer_version_id
    AND j.commercial_motion_code = NEW.commercial_motion_code
    AND r.service_job_id = NEW.service_job_id
    AND r.offer_version_id = NEW.offer_version_id
    AND r.step_code = NEW.step_code
    AND r.run_state IN ('completed','failed','aborted')
    AND r.completed_at IS NOT NULL
    AND datetime(c.created_at) <= datetime(NEW.linked_at)
    AND datetime(c.recorded_at) <= datetime(NEW.recorded_at)
    AND datetime(j.eligible_at) <= datetime(NEW.linked_at)
    AND datetime(j.recorded_at) <= datetime(NEW.recorded_at)
    AND datetime(r.completed_at) <= datetime(NEW.linked_at)
    AND datetime(r.recorded_at) <= datetime(NEW.recorded_at)
)
BEGIN SELECT RAISE(ABORT, 'candidate source must be the exact visible terminal step run'); END;

CREATE TRIGGER validate_exception_stream_before_insert
BEFORE INSERT ON exception_event
WHEN
  (NEW.event_sequence = 1 AND NEW.event_type <> 'opened')
  OR (NEW.event_sequence = 1 AND EXISTS (
        SELECT 1 FROM exception_event e WHERE e.exception_key = NEW.exception_key
      ))
  OR (NEW.event_sequence > 1 AND NOT EXISTS (
        SELECT 1
        FROM exception_event p
        WHERE p.exception_key = NEW.exception_key
          AND p.event_sequence = NEW.event_sequence - 1
          AND p.service_job_id = NEW.service_job_id
          AND p.job_step_run_id = NEW.job_step_run_id
          AND p.step_code = NEW.step_code
          AND datetime(p.occurred_at) <= datetime(NEW.occurred_at)
          AND datetime(p.recorded_at) <= datetime(NEW.recorded_at)
          AND (
            (p.event_type IN ('opened','reclassified','reopened')
             AND NEW.event_type IN ('reclassified','resolved'))
            OR (p.event_type = 'resolved' AND NEW.event_type = 'reopened')
          )
      ))
BEGIN SELECT RAISE(ABORT, 'invalid exception identity, sequence, time, or transition'); END;

CREATE TRIGGER validate_service_job_state_stream_before_insert
BEFORE INSERT ON service_job_state_event
WHEN (NEW.event_sequence > 1 AND NOT EXISTS (
  SELECT 1 FROM service_job_state_event p
  WHERE p.service_job_id = NEW.service_job_id
    AND p.event_sequence = NEW.event_sequence - 1
    AND datetime(p.occurred_at) <= datetime(NEW.occurred_at)
    AND datetime(p.recorded_at) <= datetime(NEW.recorded_at)
    AND (
      (p.execution_state = 'queued' AND NEW.execution_state IN ('started','cancelled'))
      OR (p.execution_state = 'started'
          AND NEW.execution_state IN ('completed','failed','cancelled'))
    )
))
BEGIN SELECT RAISE(ABORT, 'invalid service-job state sequence, time, or transition'); END;

CREATE TRIGGER validate_migration_stream_before_insert
BEFORE INSERT ON customer_migration
WHEN NOT EXISTS (
    SELECT 1
    FROM automation_candidate c
    JOIN offer_version from_o ON from_o.offer_version_id = NEW.from_offer_version_id
    JOIN offer_version to_o ON to_o.offer_version_id = NEW.to_offer_version_id
    JOIN offer_standard_step to_s
      ON to_s.offer_version_id = to_o.offer_version_id
     AND to_s.step_code = c.step_code
    WHERE c.automation_candidate_id = NEW.automation_candidate_id
      AND c.offer_version_id = NEW.from_offer_version_id
      AND datetime(c.recorded_at) <= datetime(NEW.recorded_at)
      AND datetime(from_o.recorded_at) <= datetime(NEW.recorded_at)
      AND datetime(to_o.recorded_at) <= datetime(NEW.recorded_at)
      AND datetime(to_s.recorded_at) <= datetime(NEW.recorded_at)
      AND EXISTS (
        SELECT 1
        FROM commercial_cycle source_cycle
        WHERE source_cycle.account_id = NEW.account_id
          AND source_cycle.offer_version_id = NEW.from_offer_version_id
          AND source_cycle.commercial_motion_code = c.commercial_motion_code
          AND datetime(source_cycle.period_start) <= datetime(NEW.occurred_at)
          AND datetime(source_cycle.recorded_at) <= datetime(NEW.recorded_at)
      )
      AND from_o.workflow_id = to_o.workflow_id
      AND from_o.segment_code = to_o.segment_code
      AND from_o.data_class_code = to_o.data_class_code
      AND to_s.delivery_mode_code = c.target_delivery_mode_code
  )
  OR (NEW.event_sequence = 1 AND NEW.migration_state <> 'proposed')
  OR (NEW.event_sequence > 1 AND NOT EXISTS (
    SELECT 1 FROM customer_migration p
    WHERE p.migration_id = NEW.migration_id
      AND p.event_sequence = NEW.event_sequence - 1
      AND p.automation_candidate_id = NEW.automation_candidate_id
      AND p.account_id = NEW.account_id
      AND p.from_offer_version_id = NEW.from_offer_version_id
      AND p.to_offer_version_id = NEW.to_offer_version_id
      AND datetime(p.occurred_at) <= datetime(NEW.occurred_at)
      AND datetime(p.recorded_at) <= datetime(NEW.recorded_at)
      AND (
        (p.migration_state = 'proposed'
         AND NEW.migration_state IN ('consented','declined'))
        OR (p.migration_state = 'consented'
            AND NEW.migration_state IN ('scheduled','migrated','declined'))
        OR (p.migration_state = 'scheduled'
            AND NEW.migration_state IN ('migrated','rolled_back'))
        OR (p.migration_state = 'migrated'
            AND NEW.migration_state IN ('verified','rolled_back'))
        OR (p.migration_state = 'verified' AND NEW.migration_state = 'rolled_back')
      )
  ))
BEGIN SELECT RAISE(ABORT, 'invalid migration identity, sequence, time, or transition'); END;

CREATE TRIGGER validate_acceptance_stream_before_insert
BEFORE INSERT ON acceptance_event
WHEN
  (NEW.event_sequence = 1 AND NEW.event_type <> 'observed')
  OR (NEW.event_sequence > 1 AND NOT EXISTS (
        SELECT 1 FROM acceptance_event p
        WHERE p.service_job_id = NEW.service_job_id
          AND p.event_sequence = NEW.event_sequence - 1
          AND datetime(p.occurred_at) <= datetime(NEW.occurred_at)
          AND datetime(p.recorded_at) <= datetime(NEW.recorded_at)
          AND ((p.event_type = 'observed' AND NEW.event_type IN ('observed','retracted'))
            OR (p.event_type = 'retracted' AND NEW.event_type = 'observed'))
      ))
BEGIN SELECT RAISE(ABORT, 'invalid acceptance sequence, time, or transition'); END;

CREATE TRIGGER validate_value_stream_before_insert
BEFORE INSERT ON value_confirmation
WHEN
  (NEW.event_sequence = 1 AND NEW.event_type <> 'observed')
  OR (NEW.event_sequence > 1 AND NOT EXISTS (
        SELECT 1 FROM value_confirmation p
        WHERE p.service_job_id = NEW.service_job_id
          AND p.event_sequence = NEW.event_sequence - 1
          AND datetime(p.occurred_at) <= datetime(NEW.occurred_at)
          AND datetime(p.recorded_at) <= datetime(NEW.recorded_at)
          AND ((p.event_type = 'observed' AND NEW.event_type IN ('observed','retracted'))
            OR (p.event_type = 'retracted' AND NEW.event_type = 'observed'))
      ))
BEGIN SELECT RAISE(ABORT, 'invalid value sequence, time, or transition'); END;

CREATE TRIGGER validate_promotion_snapshot_before_insert
BEFORE INSERT ON promotion_evidence_snapshot
WHEN NOT EXISTS (
  SELECT 1
  FROM automation_candidate c
  JOIN offer_version o USING (offer_version_id)
  JOIN offer_standard_step os
    ON os.offer_version_id = c.offer_version_id AND os.step_code = c.step_code
  WHERE c.automation_candidate_id = NEW.automation_candidate_id
    AND datetime(c.created_at) <= datetime(NEW.evidence_as_of)
    AND datetime(c.recorded_at) <= datetime(NEW.evidence_watermark_at)
    AND datetime(o.effective_from) <= datetime(NEW.evidence_as_of)
    AND datetime(o.recorded_at) <= datetime(NEW.evidence_watermark_at)
    AND datetime(os.recorded_at) <= datetime(NEW.evidence_watermark_at)
    AND EXISTS (
      SELECT 1 FROM delivery_mode_required_gate_domain r
      WHERE r.delivery_mode_code = NEW.target_delivery_mode_code
    )
) OR (
  NEW.snapshot_sequence = 1 AND EXISTS (
    SELECT 1 FROM promotion_evidence_snapshot s
    WHERE s.automation_candidate_id = NEW.automation_candidate_id
  )
) OR (
  NEW.snapshot_sequence > 1 AND NOT EXISTS (
    SELECT 1 FROM promotion_evidence_snapshot p
    WHERE p.automation_candidate_id = NEW.automation_candidate_id
      AND p.snapshot_sequence = NEW.snapshot_sequence - 1
      AND datetime(p.evidence_as_of) <= datetime(NEW.evidence_as_of)
      AND datetime(p.evidence_watermark_at) <= datetime(NEW.evidence_watermark_at)
      AND datetime(p.occurred_at) <= datetime(NEW.occurred_at)
      AND datetime(p.recorded_at) <= datetime(NEW.occurred_at)
  )
)
BEGIN SELECT RAISE(ABORT, 'invalid or premature promotion evidence snapshot'); END;

CREATE TRIGGER validate_promotion_decision_order_before_insert
BEFORE INSERT ON promotion_decision
WHEN NOT EXISTS (
  SELECT 1
  FROM promotion_evidence_snapshot s
  JOIN promotion_metric_snapshot m USING (promotion_evidence_snapshot_id)
  WHERE s.promotion_evidence_snapshot_id = NEW.promotion_evidence_snapshot_id
    AND s.automation_candidate_id = NEW.automation_candidate_id
    AND datetime(s.recorded_at) <= datetime(NEW.occurred_at)
    AND datetime(m.materialized_at) <= datetime(NEW.occurred_at)
) OR (
  NEW.decision_sequence > 1 AND NOT EXISTS (
    SELECT 1 FROM promotion_decision p
    WHERE p.automation_candidate_id = NEW.automation_candidate_id
      AND p.decision_sequence = NEW.decision_sequence - 1
      AND datetime(p.occurred_at) <= datetime(NEW.occurred_at)
      AND datetime(p.recorded_at) <= datetime(NEW.occurred_at)
  )
)
BEGIN SELECT RAISE(ABORT, 'decision must follow its independent snapshot and prior action'); END;

CREATE TRIGGER validate_promotion_gate_evidence_before_insert
BEFORE INSERT ON promotion_gate_evidence
WHEN NOT EXISTS (
  SELECT 1 FROM promotion_metric_snapshot m
  WHERE m.promotion_evidence_snapshot_id = NEW.promotion_evidence_snapshot_id
) OR NOT EXISTS (
  SELECT 1 FROM promotion_evidence_snapshot s
  WHERE s.promotion_evidence_snapshot_id = NEW.promotion_evidence_snapshot_id
    AND datetime(NEW.observed_at) <= datetime(s.evidence_as_of)
    AND datetime(NEW.recorded_at) <= datetime(s.evidence_watermark_at)
) OR EXISTS (
  SELECT 1 FROM promotion_decision d
  WHERE d.promotion_evidence_snapshot_id = NEW.promotion_evidence_snapshot_id
)
BEGIN SELECT RAISE(ABORT, 'gate evidence must predate and freeze before the action'); END;

CREATE TRIGGER validate_weekly_review_stream_before_insert
BEFORE INSERT ON weekly_review
WHEN NOT EXISTS (
  SELECT 1
  FROM automation_candidate c
  JOIN offer_version o ON o.offer_version_id = NEW.offer_version_id
  WHERE c.automation_candidate_id = NEW.automation_candidate_id
    AND c.offer_version_id = NEW.offer_version_id
    AND c.commercial_motion_code = NEW.commercial_motion_code
    AND o.commercial_motion_code = NEW.commercial_motion_code
    AND datetime(c.created_at) <= datetime(NEW.evidence_as_of)
    AND datetime(c.recorded_at) <= datetime(NEW.evidence_watermark_at)
    AND datetime(o.effective_from) <= datetime(NEW.evidence_as_of)
    AND datetime(o.recorded_at) <= datetime(NEW.evidence_watermark_at)
) OR (
  NEW.review_sequence = 1 AND EXISTS (
    SELECT 1 FROM weekly_review w WHERE w.review_key = NEW.review_key
  )
) OR (
  NEW.review_sequence > 1 AND NOT EXISTS (
    SELECT 1
    FROM weekly_review p
    WHERE p.review_key = NEW.review_key
      AND p.review_sequence = NEW.review_sequence - 1
      AND p.review_week_start = NEW.review_week_start
      AND p.offer_version_id = NEW.offer_version_id
      AND p.automation_candidate_id = NEW.automation_candidate_id
      AND p.commercial_motion_code = NEW.commercial_motion_code
      AND datetime(p.evidence_as_of) <= datetime(NEW.evidence_as_of)
      AND datetime(p.evidence_watermark_at) <= datetime(NEW.evidence_watermark_at)
      AND datetime(p.occurred_at) <= datetime(NEW.occurred_at)
      AND datetime(p.recorded_at) <= datetime(NEW.recorded_at)
  )
)
BEGIN SELECT RAISE(ABORT, 'invalid weekly review identity, sequence, time, or candidate scope'); END;

-- -----------------------------------------------------------------------------
-- Current/as-of projections. Raw rows stay immutable; every operational status
-- below is derived at the model's event-time and ingestion-watermark cutoff.
-- -----------------------------------------------------------------------------

CREATE VIEW v_offer_version_asof AS
SELECT o.*
FROM offer_version o
CROSS JOIN model_settings s
WHERE datetime(o.recorded_at) <= datetime(s.data_watermark_at)
  AND datetime(o.effective_from) <= datetime(s.event_time_as_of);

CREATE VIEW v_current_offer_version AS
WITH ranked AS (
  SELECT o.*,
         row_number() OVER (
           PARTITION BY offer_code
           ORDER BY datetime(effective_from) DESC, version_number DESC
         ) AS row_rank
  FROM v_offer_version_asof o
  CROSS JOIN model_settings s
  WHERE o.effective_to IS NULL
     OR datetime(o.effective_to) > datetime(s.event_time_as_of)
)
SELECT * FROM ranked WHERE row_rank = 1;

CREATE VIEW v_commercial_cycle_asof AS
SELECT c.*
FROM commercial_cycle c
CROSS JOIN model_settings s
WHERE datetime(c.recorded_at) <= datetime(s.data_watermark_at)
  AND datetime(c.period_start) <= datetime(s.event_time_as_of);

CREATE VIEW v_commercial_event_asof AS
SELECT e.*
FROM commercial_event e
CROSS JOIN model_settings s
WHERE datetime(e.occurred_at) <= datetime(s.event_time_as_of)
  AND datetime(e.recorded_at) <= datetime(s.data_watermark_at);

CREATE VIEW v_service_job_asof AS
SELECT j.*
FROM service_job j
CROSS JOIN model_settings s
WHERE datetime(j.eligible_at) <= datetime(s.event_time_as_of)
  AND datetime(j.recorded_at) <= datetime(s.data_watermark_at);

CREATE VIEW v_service_job_state_event_asof AS
SELECT e.*
FROM service_job_state_event e
CROSS JOIN model_settings s
WHERE datetime(e.occurred_at) <= datetime(s.event_time_as_of)
  AND datetime(e.recorded_at) <= datetime(s.data_watermark_at);

CREATE VIEW v_current_service_job_state AS
WITH ranked AS (
  SELECT e.*,
         row_number() OVER (
           PARTITION BY service_job_id
           ORDER BY event_sequence DESC, datetime(occurred_at) DESC, state_event_id DESC
         ) AS row_rank
  FROM v_service_job_state_event_asof e
  JOIN v_service_job_asof j USING (service_job_id)
)
SELECT * FROM ranked WHERE row_rank = 1;

CREATE VIEW v_job_step_run_asof AS
SELECT r.*
FROM job_step_run r
CROSS JOIN model_settings s
WHERE datetime(r.started_at) <= datetime(s.event_time_as_of)
  AND (r.completed_at IS NULL OR datetime(r.completed_at) <= datetime(s.event_time_as_of))
  AND datetime(r.recorded_at) <= datetime(s.data_watermark_at);

CREATE VIEW v_human_work_log_asof AS
SELECT h.*
FROM human_work_log h
CROSS JOIN model_settings s
WHERE datetime(h.occurred_at) <= datetime(s.event_time_as_of)
  AND datetime(h.recorded_at) <= datetime(s.data_watermark_at);

CREATE VIEW v_variable_cost_event_asof AS
SELECT v.*
FROM variable_cost_event v
CROSS JOIN model_settings s
WHERE datetime(v.occurred_at) <= datetime(s.event_time_as_of)
  AND datetime(v.recorded_at) <= datetime(s.data_watermark_at);

CREATE VIEW v_exception_event_asof AS
SELECT e.*
FROM exception_event e
CROSS JOIN model_settings s
WHERE datetime(e.occurred_at) <= datetime(s.event_time_as_of)
  AND datetime(e.recorded_at) <= datetime(s.data_watermark_at);

CREATE VIEW v_current_exception AS
WITH ranked AS (
  SELECT e.*,
         row_number() OVER (
           PARTITION BY exception_key
           ORDER BY event_sequence DESC, datetime(occurred_at) DESC, exception_event_id DESC
         ) AS row_rank
  FROM v_exception_event_asof e
  JOIN v_service_job_asof j USING (service_job_id)
)
SELECT *, CASE WHEN event_type = 'resolved' THEN 0 ELSE 1 END AS is_open
FROM ranked
WHERE row_rank = 1;

CREATE VIEW v_acceptance_event_asof AS
SELECT a.*
FROM acceptance_event a
CROSS JOIN model_settings s
WHERE datetime(a.occurred_at) <= datetime(s.event_time_as_of)
  AND datetime(a.recorded_at) <= datetime(s.data_watermark_at);

CREATE VIEW v_current_acceptance AS
WITH ranked AS (
  SELECT a.*,
         row_number() OVER (
           PARTITION BY service_job_id
           ORDER BY event_sequence DESC,datetime(occurred_at) DESC,
                    acceptance_event_id DESC
         ) AS row_rank
  FROM v_acceptance_event_asof a
  JOIN v_service_job_asof j USING (service_job_id)
)
SELECT * FROM ranked WHERE row_rank = 1;

CREATE VIEW v_value_confirmation_asof AS
SELECT v.*
FROM value_confirmation v
CROSS JOIN model_settings s
WHERE datetime(v.occurred_at) <= datetime(s.event_time_as_of)
  AND datetime(v.recorded_at) <= datetime(s.data_watermark_at);

CREATE VIEW v_current_value_confirmation AS
WITH ranked AS (
  SELECT v.*,
         row_number() OVER (
           PARTITION BY service_job_id
           ORDER BY event_sequence DESC, datetime(occurred_at) DESC,
                    value_confirmation_event_id DESC
         ) AS row_rank
  FROM v_value_confirmation_asof v
  JOIN v_service_job_asof j USING (service_job_id)
)
SELECT * FROM ranked WHERE row_rank = 1;

CREATE VIEW v_automation_candidate_asof AS
SELECT c.*
FROM automation_candidate c
CROSS JOIN model_settings s
WHERE datetime(c.created_at) <= datetime(s.event_time_as_of)
  AND datetime(c.recorded_at) <= datetime(s.data_watermark_at);

CREATE VIEW v_promotion_evidence_snapshot_asof AS
SELECT p.*
FROM promotion_evidence_snapshot p
CROSS JOIN model_settings s
WHERE datetime(p.occurred_at) <= datetime(s.event_time_as_of)
  AND datetime(p.recorded_at) <= datetime(s.data_watermark_at);

CREATE VIEW v_current_promotion_evidence_snapshot AS
WITH ranked AS (
  SELECT p.*,
         row_number() OVER (
           PARTITION BY automation_candidate_id
           ORDER BY snapshot_sequence DESC,datetime(occurred_at) DESC,
                    promotion_evidence_snapshot_id DESC
         ) AS row_rank
  FROM v_promotion_evidence_snapshot_asof p
)
SELECT * FROM ranked WHERE row_rank = 1;

CREATE VIEW v_promotion_decision_asof AS
SELECT d.*
FROM promotion_decision d
CROSS JOIN model_settings s
WHERE datetime(d.occurred_at) <= datetime(s.event_time_as_of)
  AND datetime(d.recorded_at) <= datetime(s.data_watermark_at);

CREATE VIEW v_current_promotion_decision AS
WITH ranked AS (
  SELECT d.*,
         row_number() OVER (
           PARTITION BY automation_candidate_id
           ORDER BY decision_sequence DESC, datetime(occurred_at) DESC,
                    promotion_decision_id DESC
         ) AS row_rank
  FROM v_promotion_decision_asof d
)
SELECT * FROM ranked WHERE row_rank = 1;

CREATE VIEW v_customer_migration_asof AS
SELECT m.*
FROM customer_migration m
CROSS JOIN model_settings s
WHERE datetime(m.occurred_at) <= datetime(s.event_time_as_of)
  AND datetime(m.recorded_at) <= datetime(s.data_watermark_at);

CREATE VIEW v_current_customer_migration AS
WITH ranked AS (
  SELECT m.*,
         row_number() OVER (
           PARTITION BY migration_id
           ORDER BY event_sequence DESC, datetime(occurred_at) DESC,
                    customer_migration_event_id DESC
         ) AS row_rank
  FROM v_customer_migration_asof m
)
SELECT * FROM ranked WHERE row_rank = 1;

CREATE VIEW v_weekly_review_asof AS
SELECT w.*
FROM weekly_review w
CROSS JOIN model_settings s
WHERE datetime(w.occurred_at) <= datetime(s.event_time_as_of)
  AND datetime(w.recorded_at) <= datetime(s.data_watermark_at);

CREATE VIEW v_current_weekly_review AS
WITH ranked AS (
  SELECT w.*,
         row_number() OVER (
           PARTITION BY review_key
           ORDER BY review_sequence DESC, datetime(occurred_at) DESC,
                    weekly_review_event_id DESC
         ) AS row_rank
  FROM v_weekly_review_asof w
)
SELECT * FROM ranked WHERE row_rank = 1;

-- Commercial facts remain at commercial-cycle grain. Job joins never replicate
-- revenue, payment, or refund rows.
CREATE VIEW v_commercial_cycle_facts AS
WITH event_totals AS (
  SELECT commercial_cycle_id,
         sum(CASE WHEN event_type = 'invoice_issued' THEN amount_micros ELSE 0 END)
           AS invoiced_micros,
         sum(CASE WHEN event_type = 'credit_note_issued' THEN amount_micros ELSE 0 END)
           AS credit_note_micros,
         sum(CASE WHEN event_type = 'payment_received' THEN amount_micros ELSE 0 END)
           AS payment_received_micros,
         sum(CASE WHEN event_type = 'refund_paid' THEN amount_micros ELSE 0 END)
           AS refund_paid_micros
  FROM v_commercial_event_asof
  GROUP BY commercial_cycle_id
), normalized AS (
  SELECT c.*,
         coalesce(e.invoiced_micros,0) AS invoiced_micros,
         coalesce(e.credit_note_micros,0) AS credit_note_micros,
         coalesce(e.payment_received_micros,0) AS payment_received_micros,
         coalesce(e.refund_paid_micros,0) AS refund_paid_micros,
         max(coalesce(e.invoiced_micros,0) - coalesce(e.credit_note_micros,0),0)
           AS net_invoiced_micros,
         coalesce(e.payment_received_micros,0) - coalesce(e.refund_paid_micros,0)
           AS net_cash_revenue_micros
  FROM v_commercial_cycle_asof c
  LEFT JOIN event_totals e USING (commercial_cycle_id)
)
SELECT n.*,
       max(net_invoiced_micros - net_cash_revenue_micros,0) AS outstanding_micros,
       CASE
         WHEN net_invoiced_micros <= 0 THEN 'no_invoice'
         WHEN net_cash_revenue_micros >= net_invoiced_micros THEN 'paid'
         WHEN net_cash_revenue_micros > 0 THEN 'partial'
         ELSE 'unpaid'
       END AS payment_state
FROM normalized n;

CREATE VIEW v_service_job_work_facts AS
SELECT j.service_job_id,
       coalesce(sum(CASE WHEN h.worker_class = 'founder' THEN h.minutes ELSE 0 END),0)
         AS founder_minutes,
       coalesce(sum(CASE WHEN h.worker_class = 'founder' AND h.work_class = 'founder_rescue'
                         THEN h.minutes ELSE 0 END),0) AS founder_rescue_minutes,
       coalesce(sum(CASE WHEN h.worker_class = 'founder'
                         THEN h.minutes * h.internal_rate_micros_per_minute ELSE 0 END),0)
         AS founder_internal_cost_micros,
       coalesce(sum(CASE WHEN h.worker_class = 'contractor' THEN h.cash_cost_micros ELSE 0 END),0)
         AS contractor_cash_cost_micros,
       coalesce(sum(CASE WHEN h.worker_class = 'customer' THEN h.minutes ELSE 0 END),0)
         AS customer_minutes
FROM v_service_job_asof j
LEFT JOIN v_human_work_log_asof h USING (service_job_id)
GROUP BY j.service_job_id;

CREATE VIEW v_service_job_variable_cost_facts AS
SELECT j.service_job_id,
       coalesce(sum(v.amount_micros),0) AS nonlabor_variable_cost_micros
FROM v_service_job_asof j
LEFT JOIN v_variable_cost_event_asof v USING (service_job_id)
GROUP BY j.service_job_id;

CREATE VIEW v_service_job_exception_facts AS
SELECT j.service_job_id,
       count(e.exception_key) AS current_exception_count,
       count(CASE WHEN e.is_open = 1 THEN 1 END) AS open_exception_count,
       count(CASE WHEN e.is_open = 1 AND e.severity = 'high' THEN 1 END)
         AS open_high_exception_count,
       count(CASE WHEN e.is_open = 1 AND e.severity = 'critical' THEN 1 END)
         AS open_critical_exception_count,
       count(CASE WHEN e.is_open = 1 AND e.nonwaivable = 1 THEN 1 END)
         AS open_nonwaivable_exception_count
FROM v_service_job_asof j
LEFT JOIN v_current_exception e USING (service_job_id)
GROUP BY j.service_job_id;

-- One row per service job. Contractual acceptance, realized value, and cycle
-- cash are separate facts. Cycle money amounts remain only at cycle grain.
CREATE VIEW v_service_job_facts AS
SELECT j.service_job_id,
       j.account_id,
       j.offer_version_id,
       ov.workflow_id,
       ov.workflow_version,
       ov.segment_code,
       ov.data_class_code,
       j.commercial_cycle_id,
       j.commercial_motion_code,
       j.delivery_mode_code,
       j.delegation_level_code,
       j.risk_tier_code,
       j.scope_class,
       j.eligibility_state,
       j.eligible_at,
       j.outcome_maturity_due_at,
       coalesce(sj.execution_state,'unknown') AS execution_state,
       coalesce(a.acceptance_state,'unknown') AS acceptance_state,
       a.acceptance_source,
       coalesce(vc.value_state,'unknown') AS value_state,
       vc.confirmation_source,
       coalesce(vc.independence_state,'unknown') AS independence_state,
       CASE WHEN cf.contracted_amount_micros > 0 THEN 1 ELSE 0 END
         AS contracted_paid_job,
       CASE WHEN cf.payment_state = 'paid' THEN 1 ELSE 0 END AS cash_collected,
       CASE WHEN j.eligibility_state = 'eligible'
                  AND datetime(j.outcome_maturity_due_at) <= datetime(ms.event_time_as_of)
            THEN 1 ELSE 0 END AS outcome_mature,
       CASE WHEN j.eligibility_state = 'eligible'
                  AND datetime(j.outcome_maturity_due_at) <= datetime(ms.event_time_as_of)
                  AND sj.execution_state = 'completed'
                  AND a.acceptance_state = 'accepted'
                  AND a.acceptance_source IN ('customer_owner','customer_user','contract_oracle')
            THEN 1 ELSE 0 END AS accepted_outcome,
       CASE WHEN j.eligibility_state = 'eligible'
                  AND datetime(j.outcome_maturity_due_at) <= datetime(ms.event_time_as_of)
                  AND sj.execution_state = 'completed'
                  AND a.acceptance_state = 'accepted'
                  AND a.acceptance_source IN ('customer_owner','customer_user','contract_oracle')
                  AND vc.value_state = 'confirmed'
                  AND vc.confirmation_source IN ('customer_owner','customer_user')
            THEN 1 ELSE 0 END AS value_confirmed_outcome,
       CASE WHEN j.eligibility_state = 'eligible'
                  AND datetime(j.outcome_maturity_due_at) <= datetime(ms.event_time_as_of)
                  AND sj.execution_state = 'completed'
                  AND a.acceptance_state = 'accepted'
                  AND a.acceptance_source IN ('customer_owner','customer_user','contract_oracle')
                  AND cf.contracted_amount_micros > 0
            THEN 1 ELSE 0 END AS contracted_accepted_outcome,
       CASE WHEN j.eligibility_state = 'eligible'
                  AND datetime(j.outcome_maturity_due_at) <= datetime(ms.event_time_as_of)
                  AND sj.execution_state = 'completed'
                  AND a.acceptance_state = 'accepted'
                  AND a.acceptance_source IN ('customer_owner','customer_user','contract_oracle')
                  AND vc.value_state = 'confirmed'
                  AND vc.confirmation_source IN ('customer_owner','customer_user')
                  AND cf.payment_state = 'paid'
            THEN 1 ELSE 0 END AS cash_collected_value_confirmed_outcome,
       CASE WHEN j.scope_class = 'standard' THEN 1 ELSE 0 END AS standard_scope,
       wf.founder_minutes,
       wf.founder_rescue_minutes,
       wf.founder_internal_cost_micros,
       wf.contractor_cash_cost_micros,
       wf.customer_minutes,
       vf.nonlabor_variable_cost_micros,
       ef.current_exception_count,
       ef.open_exception_count,
       ef.open_high_exception_count,
       ef.open_critical_exception_count,
       ef.open_nonwaivable_exception_count
FROM v_service_job_asof j
CROSS JOIN model_settings ms
JOIN offer_version ov USING (offer_version_id)
LEFT JOIN v_current_service_job_state sj USING (service_job_id)
LEFT JOIN v_current_acceptance a USING (service_job_id)
LEFT JOIN v_current_value_confirmation vc USING (service_job_id)
JOIN v_commercial_cycle_facts cf USING (commercial_cycle_id)
JOIN v_service_job_work_facts wf USING (service_job_id)
JOIN v_service_job_variable_cost_facts vf USING (service_job_id)
JOIN v_service_job_exception_facts ef USING (service_job_id);

-- Standard-scope denominator: every eligible job whose outcome clock matured,
-- including failures and unpaid work.
CREATE VIEW v_standard_scope_rate AS
SELECT offer_version_id,
       workflow_id,
       workflow_version,
       segment_code,
       data_class_code,
       commercial_motion_code,
       delivery_mode_code,
       delegation_level_code,
       risk_tier_code,
       count(*) AS mature_eligible_jobs,
       sum(standard_scope) AS standard_scope_jobs,
       cast(1000000.0 * sum(standard_scope) / count(*) AS INTEGER)
         AS standard_scope_rate_ppm,
       round(100.0 * sum(standard_scope) / count(*),1) AS standard_scope_rate_pct
FROM v_service_job_facts
WHERE outcome_mature = 1
GROUP BY offer_version_id, workflow_id, workflow_version, segment_code,
         data_class_code, commercial_motion_code, delivery_mode_code,
         delegation_level_code, risk_tier_code;

-- Founder minutes includes work spent on failures, cash-uncollected work, and
-- rescue in the same mature cohort. Its denominator is contracted + accepted
-- outcomes; value confirmation and cash collection remain separate columns.
CREATE VIEW v_founder_minutes_per_accepted_outcome AS
SELECT offer_version_id,
       workflow_id,
       workflow_version,
       segment_code,
       data_class_code,
       commercial_motion_code,
       delivery_mode_code,
       delegation_level_code,
       risk_tier_code,
       count(*) AS mature_eligible_jobs,
       sum(accepted_outcome) AS accepted_outcomes,
       sum(value_confirmed_outcome) AS value_confirmed_outcomes,
       sum(contracted_accepted_outcome) AS contracted_accepted_outcomes,
       sum(cash_collected_value_confirmed_outcome)
         AS cash_collected_value_confirmed_outcomes,
       sum(value_confirmed_outcome) - sum(cash_collected_value_confirmed_outcome)
         AS value_confirmed_without_cash_outcomes,
       sum(founder_minutes) AS founder_minutes_all_mature_jobs,
       CASE WHEN sum(contracted_accepted_outcome) > 0
            THEN round(1.0 * sum(founder_minutes) / sum(contracted_accepted_outcome),1)
            ELSE NULL END AS founder_minutes_per_contracted_accepted_outcome
FROM v_service_job_facts
WHERE outcome_mature = 1
GROUP BY offer_version_id, workflow_id, workflow_version, segment_code,
         data_class_code, commercial_motion_code, delivery_mode_code,
         delegation_level_code, risk_tier_code;

-- SQLite 3.40 has no percentile_cont. This is the nearest-rank p90 over per-job
-- founder rescue minutes, including zero-rescue jobs in the mature denominator.
CREATE VIEW v_rescue_minutes_p90 AS
WITH ranked AS (
  SELECT offer_version_id,
         workflow_id,
         workflow_version,
         segment_code,
         data_class_code,
         commercial_motion_code,
         delivery_mode_code,
         delegation_level_code,
         risk_tier_code,
         service_job_id,
         founder_rescue_minutes,
         row_number() OVER (
           PARTITION BY offer_version_id, workflow_id, workflow_version,
                        segment_code, data_class_code, commercial_motion_code,
                        delivery_mode_code, delegation_level_code, risk_tier_code
           ORDER BY founder_rescue_minutes, service_job_id
         ) AS rescue_rank,
         count(*) OVER (
           PARTITION BY offer_version_id, workflow_id, workflow_version,
                        segment_code, data_class_code, commercial_motion_code,
                        delivery_mode_code, delegation_level_code, risk_tier_code
         ) AS cohort_jobs
  FROM v_service_job_facts
  WHERE outcome_mature = 1
)
SELECT offer_version_id,
       workflow_id,
       workflow_version,
       segment_code,
       data_class_code,
       commercial_motion_code,
       delivery_mode_code,
       delegation_level_code,
       risk_tier_code,
       cohort_jobs AS mature_eligible_jobs,
       founder_rescue_minutes AS p90_rescue_minutes
FROM ranked
WHERE rescue_rank = cast((9 * cohort_jobs + 9) / 10 AS INTEGER);

CREATE VIEW v_exception_classification AS
SELECT j.offer_version_id,
       o.workflow_id,
       o.workflow_version,
       o.segment_code,
       o.data_class_code,
       j.commercial_motion_code,
       j.delivery_mode_code,
       j.delegation_level_code,
       j.risk_tier_code,
       e.cause_class,
       e.treatment_code,
       e.severity,
       CASE WHEN e.is_open = 1 THEN 'open' ELSE 'resolved' END AS exception_state,
       count(*) AS exception_count,
       count(DISTINCT e.service_job_id) AS affected_jobs,
       sum(CASE WHEN e.nonwaivable = 1 THEN 1 ELSE 0 END) AS nonwaivable_count
FROM v_current_exception e
JOIN v_service_job_asof j USING (service_job_id)
JOIN offer_version o USING (offer_version_id)
GROUP BY j.offer_version_id, o.workflow_id, o.workflow_version,
         o.segment_code, o.data_class_code,
         j.commercial_motion_code, j.delivery_mode_code,
         j.delegation_level_code, j.risk_tier_code,
         e.cause_class, e.treatment_code, e.severity, exception_state;

-- Contribution remains one row per commercial cycle. Cash contribution excludes
-- founder internal cost; fully-loaded contribution includes it.
CREATE VIEW v_fully_loaded_contribution AS
WITH job_costs AS (
  SELECT commercial_cycle_id,
         count(*) AS service_jobs,
         sum(accepted_outcome) AS accepted_outcomes,
         sum(contracted_accepted_outcome) AS contracted_accepted_outcomes,
         sum(nonlabor_variable_cost_micros) AS nonlabor_variable_cost_micros,
         sum(contractor_cash_cost_micros) AS contractor_cash_cost_micros,
         sum(founder_internal_cost_micros) AS founder_internal_cost_micros,
         sum(founder_minutes) AS founder_minutes
  FROM v_service_job_facts
  GROUP BY commercial_cycle_id
)
SELECT c.commercial_cycle_id,
       c.account_id,
       c.offer_version_id,
       c.commercial_motion_code,
       c.currency,
       c.payment_state,
       c.net_invoiced_micros,
       c.net_cash_revenue_micros,
       c.outstanding_micros,
       coalesce(j.service_jobs,0) AS service_jobs,
       coalesce(j.accepted_outcomes,0) AS accepted_outcomes,
       coalesce(j.contracted_accepted_outcomes,0) AS contracted_accepted_outcomes,
       coalesce(j.nonlabor_variable_cost_micros,0) AS nonlabor_variable_cost_micros,
       coalesce(j.contractor_cash_cost_micros,0) AS contractor_cash_cost_micros,
       coalesce(j.founder_internal_cost_micros,0) AS founder_internal_cost_micros,
       coalesce(j.founder_minutes,0) AS founder_minutes,
       c.net_cash_revenue_micros
         - coalesce(j.nonlabor_variable_cost_micros,0)
         - coalesce(j.contractor_cash_cost_micros,0) AS cash_contribution_micros,
       c.net_cash_revenue_micros
         - coalesce(j.nonlabor_variable_cost_micros,0)
         - coalesce(j.contractor_cash_cost_micros,0)
         - coalesce(j.founder_internal_cost_micros,0) AS fully_loaded_contribution_micros
FROM v_commercial_cycle_facts c
LEFT JOIN job_costs j USING (commercial_cycle_id);

-- -----------------------------------------------------------------------------
-- Candidate evidence at explicit cutoffs. Historical decision rows never see
-- later job, payment, migration, exception, or work events.
-- -----------------------------------------------------------------------------

CREATE VIEW v_candidate_evidence_cutoff AS
SELECT 'CURRENT:' || c.automation_candidate_id AS cutoff_id,
       'current' AS cutoff_kind,
       NULL AS promotion_evidence_snapshot_id,
       c.automation_candidate_id,
       s.event_time_as_of AS evidence_as_of,
       s.data_watermark_at AS evidence_watermark_at
FROM v_automation_candidate_asof c
CROSS JOIN model_settings s
UNION ALL
SELECT p.promotion_evidence_snapshot_id AS cutoff_id,
       'snapshot' AS cutoff_kind,
       p.promotion_evidence_snapshot_id,
       p.automation_candidate_id,
       p.evidence_as_of,
       p.evidence_watermark_at
FROM promotion_evidence_snapshot p;

CREATE VIEW v_candidate_cutoff_job_base AS
SELECT k.cutoff_id,
       k.cutoff_kind,
       k.promotion_evidence_snapshot_id,
       k.automation_candidate_id,
       k.evidence_as_of,
       k.evidence_watermark_at,
       l.evidence_role,
       l.service_job_id,
       sr.job_step_run_id AS candidate_job_step_run_id,
       j.offer_version_id,
       j.commercial_motion_code,
       j.commercial_cycle_id,
       j.scope_class,
       j.eligibility_state,
       j.outcome_maturity_due_at,
       r.run_state AS candidate_step_run_state,
       r.result_state AS candidate_step_result_state,
       CASE WHEN cc.contracted_amount_micros > 0 THEN 1 ELSE 0 END
         AS contracted_paid_job,
       coalesce((
         SELECT se.execution_state
         FROM service_job_state_event se
         WHERE se.service_job_id = j.service_job_id
           AND datetime(se.occurred_at) <= datetime(k.evidence_as_of)
           AND datetime(se.recorded_at) <= datetime(k.evidence_watermark_at)
         ORDER BY se.event_sequence DESC, datetime(se.occurred_at) DESC,
                  se.state_event_id DESC
         LIMIT 1
       ),'unknown') AS execution_state,
       coalesce((
         SELECT a.acceptance_state
         FROM acceptance_event a
         WHERE a.service_job_id = j.service_job_id
           AND datetime(a.occurred_at) <= datetime(k.evidence_as_of)
           AND datetime(a.recorded_at) <= datetime(k.evidence_watermark_at)
         ORDER BY a.event_sequence DESC,datetime(a.occurred_at) DESC,
                  a.acceptance_event_id DESC
         LIMIT 1
       ),'unknown') AS acceptance_state,
       (
         SELECT a.acceptance_source
         FROM acceptance_event a
         WHERE a.service_job_id = j.service_job_id
           AND datetime(a.occurred_at) <= datetime(k.evidence_as_of)
           AND datetime(a.recorded_at) <= datetime(k.evidence_watermark_at)
         ORDER BY a.event_sequence DESC,datetime(a.occurred_at) DESC,
                  a.acceptance_event_id DESC
         LIMIT 1
       ) AS acceptance_source,
       coalesce((
         SELECT vc.value_state
         FROM value_confirmation vc
         WHERE vc.service_job_id = j.service_job_id
           AND datetime(vc.occurred_at) <= datetime(k.evidence_as_of)
           AND datetime(vc.recorded_at) <= datetime(k.evidence_watermark_at)
         ORDER BY vc.event_sequence DESC, datetime(vc.occurred_at) DESC,
                  vc.value_confirmation_event_id DESC
         LIMIT 1
       ),'unknown') AS value_state,
       (
         SELECT vc.confirmation_source
         FROM value_confirmation vc
         WHERE vc.service_job_id = j.service_job_id
           AND datetime(vc.occurred_at) <= datetime(k.evidence_as_of)
           AND datetime(vc.recorded_at) <= datetime(k.evidence_watermark_at)
         ORDER BY vc.event_sequence DESC, datetime(vc.occurred_at) DESC,
                  vc.value_confirmation_event_id DESC
         LIMIT 1
       ) AS confirmation_source,
       CASE WHEN
         coalesce((
           SELECT sum(CASE WHEN ce.event_type = 'invoice_issued' THEN ce.amount_micros
                           WHEN ce.event_type = 'credit_note_issued' THEN -ce.amount_micros
                           ELSE 0 END)
           FROM commercial_event ce
           WHERE ce.commercial_cycle_id = j.commercial_cycle_id
             AND datetime(ce.occurred_at) <= datetime(k.evidence_as_of)
             AND datetime(ce.recorded_at) <= datetime(k.evidence_watermark_at)
         ),0) > 0
         AND coalesce((
           SELECT sum(CASE WHEN ce.event_type = 'payment_received' THEN ce.amount_micros
                           WHEN ce.event_type = 'refund_paid' THEN -ce.amount_micros
                           ELSE 0 END)
           FROM commercial_event ce
           WHERE ce.commercial_cycle_id = j.commercial_cycle_id
             AND datetime(ce.occurred_at) <= datetime(k.evidence_as_of)
             AND datetime(ce.recorded_at) <= datetime(k.evidence_watermark_at)
         ),0) >= coalesce((
           SELECT sum(CASE WHEN ce.event_type = 'invoice_issued' THEN ce.amount_micros
                           WHEN ce.event_type = 'credit_note_issued' THEN -ce.amount_micros
                           ELSE 0 END)
           FROM commercial_event ce
           WHERE ce.commercial_cycle_id = j.commercial_cycle_id
             AND datetime(ce.occurred_at) <= datetime(k.evidence_as_of)
             AND datetime(ce.recorded_at) <= datetime(k.evidence_watermark_at)
         ),0)
         THEN 1 ELSE 0 END AS cash_collected,
       coalesce((
         SELECT sum(h.minutes)
         FROM human_work_log h
         WHERE h.service_job_id = j.service_job_id
           AND h.job_step_run_id = sr.job_step_run_id
           AND h.worker_class = 'founder'
           AND h.work_class = 'founder_rescue'
           AND datetime(h.occurred_at) <= datetime(k.evidence_as_of)
           AND datetime(h.recorded_at) <= datetime(k.evidence_watermark_at)
       ),0) AS founder_rescue_minutes
FROM v_candidate_evidence_cutoff k
JOIN automation_candidate c
  ON c.automation_candidate_id = k.automation_candidate_id
JOIN candidate_source_job l
  ON l.automation_candidate_id = k.automation_candidate_id
JOIN candidate_source_step_run sr
  ON sr.automation_candidate_id = l.automation_candidate_id
 AND sr.service_job_id = l.service_job_id
JOIN service_job j ON j.service_job_id = sr.service_job_id
JOIN commercial_cycle cc ON cc.commercial_cycle_id = j.commercial_cycle_id
JOIN job_step_run r ON r.job_step_run_id = sr.job_step_run_id
JOIN offer_version o ON o.offer_version_id = c.offer_version_id
JOIN offer_standard_step os
  ON os.offer_version_id = c.offer_version_id AND os.step_code = c.step_code
WHERE datetime(l.linked_at) <= datetime(k.evidence_as_of)
  AND datetime(l.recorded_at) <= datetime(k.evidence_watermark_at)
  AND datetime(sr.linked_at) <= datetime(k.evidence_as_of)
  AND datetime(sr.recorded_at) <= datetime(k.evidence_watermark_at)
  AND sr.offer_version_id = c.offer_version_id
  AND sr.step_code = c.step_code
  AND sr.commercial_motion_code = c.commercial_motion_code
  AND j.offer_version_id = c.offer_version_id
  AND j.commercial_motion_code = c.commercial_motion_code
  AND r.service_job_id = j.service_job_id
  AND r.offer_version_id = c.offer_version_id
  AND r.step_code = c.step_code
  AND r.run_state IN ('completed','failed','aborted')
  AND datetime(r.completed_at) <= datetime(k.evidence_as_of)
  AND datetime(r.recorded_at) <= datetime(k.evidence_watermark_at)
  AND datetime(c.created_at) <= datetime(k.evidence_as_of)
  AND datetime(c.recorded_at) <= datetime(k.evidence_watermark_at)
  AND datetime(o.effective_from) <= datetime(k.evidence_as_of)
  AND datetime(o.recorded_at) <= datetime(k.evidence_watermark_at)
  AND datetime(os.recorded_at) <= datetime(k.evidence_watermark_at)
  AND datetime(cc.period_start) <= datetime(k.evidence_as_of)
  AND datetime(cc.recorded_at) <= datetime(k.evidence_watermark_at)
  AND datetime(j.eligible_at) <= datetime(k.evidence_as_of)
  AND datetime(j.recorded_at) <= datetime(k.evidence_watermark_at);

CREATE VIEW v_candidate_cutoff_job_facts AS
SELECT b.*,
       CASE WHEN b.eligibility_state = 'eligible'
                  AND datetime(b.outcome_maturity_due_at) <= datetime(b.evidence_as_of)
            THEN 1 ELSE 0 END AS outcome_mature,
       CASE WHEN b.eligibility_state = 'eligible'
                  AND datetime(b.outcome_maturity_due_at) <= datetime(b.evidence_as_of)
                  AND b.execution_state = 'completed'
                  AND b.candidate_step_run_state = 'completed'
                  AND b.candidate_step_result_state = 'pass'
                  AND b.acceptance_state = 'accepted'
                  AND b.acceptance_source IN ('customer_owner','customer_user','contract_oracle')
            THEN 1 ELSE 0 END AS accepted_outcome,
       CASE WHEN b.eligibility_state = 'eligible'
                  AND datetime(b.outcome_maturity_due_at) <= datetime(b.evidence_as_of)
                  AND b.execution_state = 'completed'
                  AND b.candidate_step_run_state = 'completed'
                  AND b.candidate_step_result_state = 'pass'
                  AND b.acceptance_state = 'accepted'
                  AND b.acceptance_source IN ('customer_owner','customer_user','contract_oracle')
                  AND b.value_state = 'confirmed'
                  AND b.confirmation_source IN ('customer_owner','customer_user')
            THEN 1 ELSE 0 END AS value_confirmed_outcome,
       CASE WHEN b.eligibility_state = 'eligible'
                  AND datetime(b.outcome_maturity_due_at) <= datetime(b.evidence_as_of)
                  AND b.execution_state = 'completed'
                  AND b.candidate_step_run_state = 'completed'
                  AND b.candidate_step_result_state = 'pass'
                  AND b.acceptance_state = 'accepted'
                  AND b.acceptance_source IN ('customer_owner','customer_user','contract_oracle')
                  AND b.contracted_paid_job = 1
            THEN 1 ELSE 0 END AS contracted_accepted_outcome,
       CASE WHEN b.eligibility_state = 'eligible'
                  AND datetime(b.outcome_maturity_due_at) <= datetime(b.evidence_as_of)
                  AND b.execution_state = 'completed'
                  AND b.candidate_step_run_state = 'completed'
                  AND b.candidate_step_result_state = 'pass'
                  AND b.acceptance_state = 'accepted'
                  AND b.acceptance_source IN ('customer_owner','customer_user','contract_oracle')
                  AND b.value_state = 'confirmed'
                  AND b.confirmation_source IN ('customer_owner','customer_user')
                  AND b.cash_collected = 1
            THEN 1 ELSE 0 END AS cash_collected_value_confirmed_outcome,
       CASE WHEN b.scope_class = 'standard' THEN 1 ELSE 0 END AS standard_scope
FROM v_candidate_cutoff_job_base b;

CREATE VIEW v_candidate_cutoff_exception_current AS
SELECT k.cutoff_id,
       k.automation_candidate_id,
       e.exception_key,
       e.service_job_id,
       e.job_step_run_id,
       e.step_code,
       e.cause_class,
       e.treatment_code,
       e.severity,
       e.nonwaivable,
       CASE WHEN e.event_type = 'resolved' THEN 0 ELSE 1 END AS is_open
FROM v_candidate_cutoff_job_base b
JOIN v_candidate_evidence_cutoff k USING (cutoff_id,automation_candidate_id)
JOIN exception_event e
  ON e.service_job_id = b.service_job_id
 AND e.job_step_run_id = b.candidate_job_step_run_id
WHERE datetime(e.occurred_at) <= datetime(k.evidence_as_of)
  AND datetime(e.recorded_at) <= datetime(k.evidence_watermark_at)
  AND NOT EXISTS (
    SELECT 1
    FROM exception_event later
    WHERE later.exception_key = e.exception_key
      AND later.event_sequence > e.event_sequence
      AND datetime(later.occurred_at) <= datetime(k.evidence_as_of)
      AND datetime(later.recorded_at) <= datetime(k.evidence_watermark_at)
  );

CREATE VIEW v_candidate_cutoff_migration_current AS
SELECT k.cutoff_id,
       k.automation_candidate_id,
       m.migration_id,
       m.account_id,
       m.migration_state
FROM v_candidate_evidence_cutoff k
JOIN customer_migration m
  ON m.automation_candidate_id = k.automation_candidate_id
WHERE datetime(m.occurred_at) <= datetime(k.evidence_as_of)
  AND datetime(m.recorded_at) <= datetime(k.evidence_watermark_at)
  AND NOT EXISTS (
    SELECT 1
    FROM customer_migration later
    WHERE later.migration_id = m.migration_id
      AND later.event_sequence > m.event_sequence
      AND datetime(later.occurred_at) <= datetime(k.evidence_as_of)
      AND datetime(later.recorded_at) <= datetime(k.evidence_watermark_at)
  );

CREATE VIEW v_candidate_evidence_by_cutoff AS
WITH mature_jobs AS (
  SELECT *
  FROM v_candidate_cutoff_job_facts
  WHERE outcome_mature = 1
), ranked_rescue AS (
  SELECT m.*,
         row_number() OVER (
           PARTITION BY cutoff_id, automation_candidate_id
           ORDER BY founder_rescue_minutes, service_job_id
         ) AS rescue_rank,
         count(*) OVER (
           PARTITION BY cutoff_id, automation_candidate_id
         ) AS rescue_sample_jobs,
         max(founder_rescue_minutes) OVER (
           PARTITION BY cutoff_id, automation_candidate_id
         ) AS max_rescue_minutes
  FROM mature_jobs m
), rescue AS (
  SELECT cutoff_id,
         automation_candidate_id,
         rescue_sample_jobs,
         max_rescue_minutes,
         founder_rescue_minutes AS p90_rescue_minutes
  FROM ranked_rescue
  WHERE rescue_rank = cast((9 * rescue_sample_jobs + 9) / 10 AS INTEGER)
), jobs AS (
  SELECT cutoff_id,
         automation_candidate_id,
         count(*) AS visible_source_jobs,
         sum(outcome_mature) AS mature_source_jobs,
         sum(CASE WHEN outcome_mature = 1 THEN standard_scope ELSE 0 END)
           AS mature_standard_scope_jobs,
         sum(accepted_outcome) AS accepted_outcomes,
         sum(value_confirmed_outcome) AS value_confirmed_outcomes,
         sum(contracted_accepted_outcome) AS contracted_accepted_outcomes,
         sum(cash_collected_value_confirmed_outcome)
           AS cash_collected_value_confirmed_outcomes,
         sum(value_confirmed_outcome) - sum(cash_collected_value_confirmed_outcome)
           AS value_confirmed_without_cash_outcomes,
         count(DISTINCT CASE
           WHEN outcome_mature = 1 AND evidence_role IN ('boundary','exception','failure')
           THEN service_job_id END) AS exception_evidence_jobs
  FROM v_candidate_cutoff_job_facts
  GROUP BY cutoff_id, automation_candidate_id
), exceptions AS (
  SELECT cutoff_id,
         automation_candidate_id,
         count(CASE WHEN is_open = 1 AND severity = 'high' THEN 1 END)
           AS open_high_exceptions,
         count(CASE WHEN is_open = 1 AND severity = 'critical' THEN 1 END)
           AS open_critical_exceptions,
         count(CASE WHEN is_open = 1 AND nonwaivable = 1 THEN 1 END)
           AS open_nonwaivable_exceptions,
         count(CASE WHEN is_open = 1
                          AND cause_class = 'security_privacy_safety_legal'
                    THEN 1 END) AS open_security_exceptions,
         count(CASE WHEN is_open = 1 AND cause_class = 'unknown' THEN 1 END)
           AS open_unknown_exceptions
  FROM v_candidate_cutoff_exception_current
  GROUP BY cutoff_id, automation_candidate_id
), migrations AS (
  SELECT cutoff_id,
         automation_candidate_id,
         count(DISTINCT CASE WHEN migration_state IN (
           'consented','scheduled','migrated','verified'
         ) THEN account_id END) AS consented_migrations,
         count(DISTINCT CASE WHEN migration_state = 'verified'
                             THEN account_id END) AS verified_migrations
  FROM v_candidate_cutoff_migration_current
  GROUP BY cutoff_id, automation_candidate_id
)
SELECT k.cutoff_id,
       k.cutoff_kind,
       k.promotion_evidence_snapshot_id,
       k.automation_candidate_id,
       k.evidence_as_of,
       k.evidence_watermark_at,
       o.workflow_id,
       o.workflow_version,
       o.segment_code,
       o.data_class_code,
       c.step_code,
       os.delivery_mode_code AS source_step_delivery_mode_code,
       c.commercial_motion_code,
       c.target_delivery_mode_code,
       c.target_delegation_level_code,
       c.candidate_risk_tier_code,
       coalesce(j.visible_source_jobs,0) AS visible_source_jobs,
       coalesce(j.mature_source_jobs,0) AS mature_source_jobs,
       coalesce(j.mature_standard_scope_jobs,0) AS mature_standard_scope_jobs,
       CASE WHEN coalesce(j.mature_source_jobs,0) > 0
            THEN cast(1000000.0 * j.mature_standard_scope_jobs / j.mature_source_jobs AS INTEGER)
            ELSE NULL END AS standard_scope_rate_ppm,
       CASE WHEN coalesce(j.mature_source_jobs,0) > 0
            THEN round(100.0 * j.mature_standard_scope_jobs / j.mature_source_jobs,1)
            ELSE NULL END AS standard_scope_rate_pct,
       coalesce(j.accepted_outcomes,0) AS accepted_outcomes,
       coalesce(j.value_confirmed_outcomes,0) AS value_confirmed_outcomes,
       coalesce(j.contracted_accepted_outcomes,0) AS contracted_accepted_outcomes,
       coalesce(j.cash_collected_value_confirmed_outcomes,0)
         AS cash_collected_value_confirmed_outcomes,
       coalesce(j.value_confirmed_without_cash_outcomes,0)
         AS value_confirmed_without_cash_outcomes,
       coalesce(j.exception_evidence_jobs,0) AS exception_evidence_jobs,
       coalesce(r.rescue_sample_jobs,0) AS rescue_sample_jobs,
       r.p90_rescue_minutes,
       r.max_rescue_minutes,
       coalesce(x.open_high_exceptions,0) AS open_high_exceptions,
       coalesce(x.open_critical_exceptions,0) AS open_critical_exceptions,
       coalesce(x.open_nonwaivable_exceptions,0) AS open_nonwaivable_exceptions,
       coalesce(x.open_security_exceptions,0) AS open_security_exceptions,
       coalesce(x.open_unknown_exceptions,0) AS open_unknown_exceptions,
       coalesce(m.consented_migrations,0) AS consented_migrations,
       coalesce(m.verified_migrations,0) AS verified_migrations
FROM v_candidate_evidence_cutoff k
JOIN automation_candidate c USING (automation_candidate_id)
JOIN offer_version o USING (offer_version_id)
JOIN offer_standard_step os
  ON os.offer_version_id = c.offer_version_id AND os.step_code = c.step_code
LEFT JOIN jobs j USING (cutoff_id, automation_candidate_id)
LEFT JOIN rescue r USING (cutoff_id, automation_candidate_id)
LEFT JOIN exceptions x USING (cutoff_id, automation_candidate_id)
LEFT JOIN migrations m USING (cutoff_id, automation_candidate_id);

CREATE VIEW v_candidate_evidence AS
SELECT *
FROM v_candidate_evidence_by_cutoff
WHERE cutoff_kind = 'current';

CREATE TRIGGER validate_promotion_metric_snapshot_before_insert
BEFORE INSERT ON promotion_metric_snapshot
WHEN EXISTS (
  SELECT 1 FROM promotion_gate_evidence g
  WHERE g.promotion_evidence_snapshot_id = NEW.promotion_evidence_snapshot_id
) OR EXISTS (
  SELECT 1 FROM promotion_decision d
  WHERE d.promotion_evidence_snapshot_id = NEW.promotion_evidence_snapshot_id
) OR NOT EXISTS (
  SELECT 1
  FROM promotion_evidence_snapshot p
  JOIN v_candidate_evidence_by_cutoff e
    ON e.cutoff_id = p.promotion_evidence_snapshot_id
   AND e.automation_candidate_id = p.automation_candidate_id
  WHERE p.promotion_evidence_snapshot_id = NEW.promotion_evidence_snapshot_id
    AND p.automation_candidate_id = NEW.automation_candidate_id
    AND datetime(p.recorded_at) <= datetime(NEW.materialized_at)
    AND e.workflow_id = NEW.workflow_id
    AND e.workflow_version = NEW.workflow_version
    AND e.segment_code = NEW.segment_code
    AND e.data_class_code = NEW.data_class_code
    AND e.step_code = NEW.step_code
    AND e.source_step_delivery_mode_code = NEW.source_step_delivery_mode_code
    AND e.commercial_motion_code = NEW.commercial_motion_code
    AND e.visible_source_jobs = NEW.visible_source_jobs
    AND e.mature_source_jobs = NEW.mature_source_jobs
    AND e.mature_standard_scope_jobs = NEW.mature_standard_scope_jobs
    AND e.standard_scope_rate_ppm IS NEW.standard_scope_rate_ppm
    AND e.accepted_outcomes = NEW.accepted_outcomes
    AND e.value_confirmed_outcomes = NEW.value_confirmed_outcomes
    AND e.contracted_accepted_outcomes = NEW.contracted_accepted_outcomes
    AND e.cash_collected_value_confirmed_outcomes
        = NEW.cash_collected_value_confirmed_outcomes
    AND e.value_confirmed_without_cash_outcomes
        = NEW.value_confirmed_without_cash_outcomes
    AND e.exception_evidence_jobs = NEW.exception_evidence_jobs
    AND e.rescue_sample_jobs = NEW.rescue_sample_jobs
    AND e.p90_rescue_minutes IS NEW.p90_rescue_minutes
    AND e.max_rescue_minutes IS NEW.max_rescue_minutes
    AND e.open_high_exceptions = NEW.open_high_exceptions
    AND e.open_critical_exceptions = NEW.open_critical_exceptions
    AND e.open_nonwaivable_exceptions = NEW.open_nonwaivable_exceptions
    AND e.open_security_exceptions = NEW.open_security_exceptions
    AND e.open_unknown_exceptions = NEW.open_unknown_exceptions
    AND e.consented_migrations = NEW.consented_migrations
    AND e.verified_migrations = NEW.verified_migrations
)
BEGIN SELECT RAISE(ABORT, 'promotion metric snapshot must exactly freeze visible cutoff facts before evidence/action'); END;

-- Readiness is derived from an independent, pre-decision snapshot. It contains
-- no recorded action. STOP > PAUSE > UNKNOWN > READY_TO_DECIDE.
CREATE VIEW v_promotion_gate_readiness AS
WITH domain_stats AS (
  SELECT p.promotion_evidence_snapshot_id,
         count(r.domain_code) AS required_domain_count,
         count(e.domain_code) AS observed_required_domain_count,
         sum(CASE WHEN e.result_code = 'PASS' THEN 1 ELSE 0 END)
           AS passed_required_domain_count,
         sum(CASE WHEN e.result_code = 'FAIL' AND d.fail_policy = 'STOP'
                  THEN 1 ELSE 0 END) AS stop_domain_failures,
         sum(CASE WHEN e.result_code = 'FAIL' AND d.fail_policy = 'PAUSE'
                  THEN 1 ELSE 0 END) AS pause_domain_failures,
         sum(CASE WHEN e.result_code = 'UNKNOWN' OR e.domain_code IS NULL
                  THEN 1 ELSE 0 END) AS unknown_or_missing_domains,
         sum(CASE WHEN r.domain_code IN (
                    'acceptance_oracle','data_rights','maximum_loss','release_telemetry'
                  ) AND (e.result_code IS NULL OR e.result_code = 'UNKNOWN')
                  THEN 1 ELSE 0 END) AS unresolved_safety_authority_fallback_domains
  FROM promotion_evidence_snapshot p
  JOIN delivery_mode_required_gate_domain r
    ON r.delivery_mode_code = p.target_delivery_mode_code
  JOIN promotion_gate_domain_dim d USING (domain_code)
  LEFT JOIN promotion_gate_evidence e
    ON e.promotion_evidence_snapshot_id = p.promotion_evidence_snapshot_id
   AND e.domain_code = r.domain_code
  GROUP BY p.promotion_evidence_snapshot_id
), gates AS (
  SELECT p.promotion_evidence_snapshot_id,
         p.automation_candidate_id,
         p.snapshot_sequence,
         p.exposure_state,
         p.independent_pause_blocker_code,
         e.workflow_id,
         e.workflow_version,
         e.segment_code,
         e.data_class_code,
         e.step_code,
         e.source_step_delivery_mode_code,
         e.commercial_motion_code,
         p.target_delivery_mode_code,
         p.target_delegation_level_code,
         p.risk_tier_code,
         e.mature_source_jobs,
         e.standard_scope_rate_ppm,
         e.contracted_accepted_outcomes,
         e.cash_collected_value_confirmed_outcomes,
         e.value_confirmed_without_cash_outcomes,
         e.exception_evidence_jobs,
         e.rescue_sample_jobs,
         e.p90_rescue_minutes,
         e.max_rescue_minutes,
         e.open_high_exceptions,
         e.open_critical_exceptions,
         e.open_nonwaivable_exceptions,
         e.open_security_exceptions,
         e.open_unknown_exceptions,
         e.consented_migrations,
         ds.required_domain_count,
         ds.observed_required_domain_count,
         ds.passed_required_domain_count,
         ds.stop_domain_failures,
         ds.pause_domain_failures,
         ds.unknown_or_missing_domains,
         ds.unresolved_safety_authority_fallback_domains,
         CASE WHEN p.target_delivery_mode_code = c.target_delivery_mode_code
                    AND p.target_delegation_level_code = c.target_delegation_level_code
                    AND p.risk_tier_code = c.candidate_risk_tier_code
              THEN 1 ELSE 0 END AS axis_binding_gate,
         CASE WHEN e.mature_source_jobs >= c.min_mature_source_jobs
              THEN 1 ELSE 0 END AS mature_sample_gate,
         CASE WHEN e.contracted_accepted_outcomes >= c.min_contracted_accepted_outcomes
              THEN 1 ELSE 0 END AS contracted_acceptance_gate,
         CASE WHEN e.exception_evidence_jobs >= c.min_exception_evidence_jobs
              THEN 1 ELSE 0 END AS counterevidence_gate,
         CASE WHEN e.standard_scope_rate_ppm IS NOT NULL
                    AND e.standard_scope_rate_ppm >= c.min_standard_scope_rate_ppm
              THEN 1 ELSE 0 END AS standard_scope_gate,
         CASE WHEN e.rescue_sample_jobs >= c.min_rescue_sample_jobs
              THEN 1 ELSE 0 END AS rescue_sample_gate,
         CASE WHEN e.p90_rescue_minutes IS NOT NULL
                    AND e.p90_rescue_minutes <= c.max_p90_rescue_minutes
              THEN 1 ELSE 0 END AS rescue_p90_gate,
         CASE WHEN e.max_rescue_minutes IS NOT NULL
                    AND e.max_rescue_minutes <= c.max_any_rescue_minutes
              THEN 1 ELSE 0 END AS rescue_max_gate,
         CASE WHEN e.consented_migrations >= c.min_consented_migrations
              THEN 1 ELSE 0 END AS migration_gate,
         CASE WHEN c.requires_rollback_evidence = 0
                    OR p.rollback_evidence_ref_hash IS NOT NULL
              THEN 1 ELSE 0 END AS rollback_gate,
         CASE WHEN c.requires_owner_approval = 0
                    OR p.owner_approval_ref_hash IS NOT NULL
              THEN 1 ELSE 0 END AS owner_approval_gate,
         CASE WHEN e.open_high_exceptions <= c.max_open_high_exceptions
              THEN 1 ELSE 0 END AS high_exception_gate
  FROM promotion_evidence_snapshot p
  JOIN automation_candidate c USING (automation_candidate_id)
  JOIN domain_stats ds USING (promotion_evidence_snapshot_id)
  JOIN promotion_metric_snapshot e
    ON e.promotion_evidence_snapshot_id = p.promotion_evidence_snapshot_id
   AND e.automation_candidate_id = p.automation_candidate_id
)
SELECT g.*,
       CASE
         WHEN open_security_exceptions > 0 OR stop_domain_failures > 0
           OR open_critical_exceptions > 0 OR open_nonwaivable_exceptions > 0
           THEN 'STOP'
         WHEN independent_pause_blocker_code IS NOT NULL OR high_exception_gate = 0
           OR pause_domain_failures > 0
           THEN 'PAUSE'
         WHEN open_unknown_exceptions > 0
           OR unknown_or_missing_domains > 0
           OR passed_required_domain_count <> required_domain_count
           OR axis_binding_gate = 0
           OR mature_sample_gate = 0
           OR contracted_acceptance_gate = 0
           OR counterevidence_gate = 0
           OR standard_scope_gate = 0
           OR rescue_sample_gate = 0
           OR rescue_p90_gate = 0
           OR rescue_max_gate = 0
           OR migration_gate = 0
           OR rollback_gate = 0
           OR owner_approval_gate = 0
           THEN 'UNKNOWN'
         ELSE 'READY_TO_DECIDE'
       END AS derived_gate_state,
       CASE WHEN open_unknown_exceptions > 0
                    OR rollback_gate = 0 OR owner_approval_gate = 0
                    OR unresolved_safety_authority_fallback_domains > 0
              THEN 1 ELSE 0 END AS safety_authority_fallback_stale
FROM gates g;

CREATE VIEW v_current_promotion_gate_readiness AS
SELECT g.*
FROM v_promotion_gate_readiness g
JOIN v_current_promotion_evidence_snapshot p
  USING (promotion_evidence_snapshot_id);

CREATE VIEW v_promotion_action_compatibility_matrix AS
SELECT g.*,
       a.action_code,
       CASE
         WHEN g.derived_gate_state = 'STOP' AND g.exposure_state = 'active_exposure'
           THEN CASE WHEN a.action_code IN ('REVERSE','STOP') THEN 1 ELSE 0 END
         WHEN g.derived_gate_state = 'STOP'
           THEN CASE WHEN a.action_code = 'STOP' THEN 1 ELSE 0 END
         WHEN g.derived_gate_state = 'PAUSE' AND g.exposure_state = 'active_exposure'
           THEN CASE WHEN a.action_code IN ('PAUSE','REVERSE','STOP') THEN 1 ELSE 0 END
         WHEN g.derived_gate_state = 'PAUSE'
           THEN CASE WHEN a.action_code IN ('HOLD','PAUSE','STOP') THEN 1 ELSE 0 END
         WHEN g.derived_gate_state = 'UNKNOWN'
              AND g.exposure_state = 'active_exposure'
              AND g.safety_authority_fallback_stale = 1
           THEN CASE WHEN a.action_code IN ('PAUSE','REVERSE','STOP') THEN 1 ELSE 0 END
         WHEN g.derived_gate_state = 'UNKNOWN' AND g.exposure_state = 'active_exposure'
           THEN CASE WHEN a.action_code IN ('HOLD','MAINTAIN','REVERSE','STOP') THEN 1 ELSE 0 END
         WHEN g.derived_gate_state = 'UNKNOWN'
           THEN CASE WHEN a.action_code IN ('HOLD','PAUSE','STOP') THEN 1 ELSE 0 END
         WHEN g.derived_gate_state = 'READY_TO_DECIDE'
           THEN 1
         ELSE 0
       END AS action_compatible
FROM v_promotion_gate_readiness g
CROSS JOIN promotion_action_dim a;

-- Recorded action is evaluated only after independent readiness exists.
CREATE VIEW v_promotion_decision_compatibility AS
SELECT d.promotion_decision_id,
       d.automation_candidate_id,
       d.decision_sequence,
       d.decision_code,
       m.*,
       m.action_compatible AS recorded_decision_compatible
FROM promotion_decision d
JOIN v_promotion_action_compatibility_matrix m
  ON m.promotion_evidence_snapshot_id = d.promotion_evidence_snapshot_id
 AND m.automation_candidate_id = d.automation_candidate_id
 AND m.action_code = d.decision_code;

CREATE VIEW v_current_promotion_decision_compatibility AS
SELECT c.*
FROM v_promotion_decision_compatibility c
JOIN v_current_promotion_decision d USING (promotion_decision_id);

-- Invalid action/state pairs are rejected at write time as well as exposed by
-- the compatibility view. In particular STOP never accepts HOLD/MAINTAIN.
CREATE TRIGGER validate_promotion_decision_compatibility_before_insert
BEFORE INSERT ON promotion_decision
WHEN NOT EXISTS (
  SELECT 1
  FROM v_promotion_action_compatibility_matrix m
  WHERE m.promotion_evidence_snapshot_id = NEW.promotion_evidence_snapshot_id
    AND m.automation_candidate_id = NEW.automation_candidate_id
    AND m.action_code = NEW.decision_code
    AND m.action_compatible = 1
)
BEGIN SELECT RAISE(ABORT, 'recorded action is incompatible with independent gate state'); END;

-- -----------------------------------------------------------------------------
-- Fully fictional fixture
-- -----------------------------------------------------------------------------

INSERT INTO schema_registry VALUES (
  1,'service_productization','1.2.0','SQLite 3.40+',
  '2026-06-01T00:00:00Z','NO_PERSONAL_DATA_HASHED_EVIDENCE_ONLY'
);

INSERT INTO model_settings VALUES (
  1,'2026-07-31T23:59:59Z','2026-07-31T23:50:00Z','JPY',100000000
);

INSERT INTO commercial_motion_dim VALUES
  ('pilot',1),('production',1),
  ('renewal',1),('expansion',1);

INSERT INTO delivery_mode_dim VALUES
  ('manual_bespoke',0),('managed_standard',1),('founder_internal',2),
  ('customer_assisted',3),('customer_self_service',4);

INSERT INTO promotion_gate_domain_dim VALUES
  ('customer_value','PAUSE'),('independent_accounts','PAUSE'),
  ('natural_cycles','PAUSE'),('customer_effort','PAUSE'),
  ('capacity_planned_work','PAUSE'),('fully_loaded_contribution','PAUSE'),
  ('acceptance_oracle','STOP'),('data_rights','STOP'),
  ('maximum_loss','STOP'),('release_telemetry','PAUSE'),
  ('target_mode_independent_value','PAUSE');

-- The two customer-facing target modes require every chapter gate domain.
INSERT INTO delivery_mode_required_gate_domain(delivery_mode_code,domain_code)
SELECT m.delivery_mode_code,d.domain_code
FROM delivery_mode_dim m CROSS JOIN promotion_gate_domain_dim d
WHERE m.delivery_mode_code IN ('customer_assisted','customer_self_service');

-- Provider-side modes still require core safety, value, capacity, and economics.
INSERT INTO delivery_mode_required_gate_domain VALUES
  ('manual_bespoke','customer_value'),
  ('manual_bespoke','fully_loaded_contribution'),
  ('manual_bespoke','acceptance_oracle'),
  ('manual_bespoke','data_rights'),
  ('manual_bespoke','maximum_loss'),
  ('managed_standard','customer_value'),
  ('managed_standard','natural_cycles'),
  ('managed_standard','capacity_planned_work'),
  ('managed_standard','fully_loaded_contribution'),
  ('managed_standard','acceptance_oracle'),
  ('managed_standard','data_rights'),
  ('managed_standard','maximum_loss'),
  ('founder_internal','customer_value'),
  ('founder_internal','natural_cycles'),
  ('founder_internal','capacity_planned_work'),
  ('founder_internal','fully_loaded_contribution'),
  ('founder_internal','acceptance_oracle'),
  ('founder_internal','data_rights'),
  ('founder_internal','maximum_loss'),
  ('founder_internal','release_telemetry');

INSERT INTO delegation_level_dim VALUES
  ('L0_READ',0),('L1_DRAFT',1),('L2_REVERSIBLE_WRITE',2),
  ('L3_EXTERNAL_EXECUTE_APPROVAL',3),('L4_PREAUTHORIZED_EXECUTE',4);

INSERT INTO risk_tier_dim VALUES ('R0',0),('R1',1),('R2',2),('R3',3);

INSERT INTO promotion_action_dim VALUES
  ('PROMOTE'),('LIMITED_CANARY'),('MAINTAIN'),('HOLD'),('REVERSE'),('PAUSE'),('STOP');

INSERT INTO exception_cause_dim VALUES
  ('input_contract'),('valid_standard_variation'),('product_defect'),
  ('model_or_rule_uncertainty'),('external_dependency'),('customer_dependency'),
  ('customer_specific_request'),('security_privacy_safety_legal'),('unknown');

INSERT INTO exception_treatment_dim VALUES
  ('auto_handled'),('planned_review'),('correction'),
  ('customer_action_requested'),('support'),('founder_rescue'),('pause'),
  ('stop_and_escalate'),('compensating_action'),('scope_change'),('separate_SOW');

INSERT INTO workflow_scope_version(
  workflow_id,workflow_version,segment_code,data_class_code,
  effective_from,superseded_at,definition_ref_hash,recorded_at
) VALUES
  ('MONTHLY_CLOSE',1,'SMALL_AGENCY','customer_confidential_nonpersonal',
   '2026-06-01T00:00:00Z','2026-07-01T00:00:00Z',printf('%064x',181),
   '2026-05-15T00:00:00Z'),
  ('MONTHLY_CLOSE',2,'SMALL_AGENCY','customer_confidential_nonpersonal',
   '2026-07-01T00:00:00Z',NULL,printf('%064x',182),
   '2026-06-15T00:00:00Z');

WITH RECURSIVE n(i) AS (
  VALUES(1) UNION ALL SELECT i + 1 FROM n WHERE i < 4
)
INSERT INTO customer_account(
  account_id,account_ref_hash,segment_code,created_at,recorded_at
)
SELECT printf('ACC-%02d',i), printf('%064x',100 + i), 'SMALL_AGENCY',
       '2026-05-01T00:00:00Z','2026-05-01T01:00:00Z'
FROM n;

INSERT INTO offer_version(
  offer_version_id,offer_code,version_number,
  workflow_id,workflow_version,segment_code,data_class_code,
  effective_from,effective_to,
  commercial_motion_code,delivery_mode_code,delegation_level_code,risk_tier_code,
  standard_scope_code,list_price_micros,currency,included_jobs_per_cycle,
  outcome_maturity_days,evidence_ref_hash,recorded_at
) VALUES
  ('OV-CLOSE-1','MONTHLY_CLOSE_PACK',1,
   'MONTHLY_CLOSE',1,'SMALL_AGENCY','customer_confidential_nonpersonal',
   '2026-06-01T00:00:00Z','2026-07-01T00:00:00Z',
   'pilot','manual_bespoke','L0_READ','R2','MONTHLY_CLOSE_V1',
   300000000000,'JPY',10,14,printf('%064x',201),'2026-05-20T00:00:00Z'),
  ('OV-CLOSE-2','MONTHLY_CLOSE_PACK',2,
   'MONTHLY_CLOSE',2,'SMALL_AGENCY','customer_confidential_nonpersonal',
   '2026-07-01T00:00:00Z',NULL,
   'production','customer_assisted','L1_DRAFT','R2','MONTHLY_CLOSE_V2',
   200000000000,'JPY',10,14,printf('%064x',202),'2026-06-20T00:00:00Z'),
  -- A second commercial package may reference the same operational scope.
  ('OV-CLOSE-ALT','MONTHLY_CLOSE_ASSISTED',1,
   'MONTHLY_CLOSE',2,'SMALL_AGENCY','customer_confidential_nonpersonal',
   '2026-07-01T00:00:00Z',NULL,
   'renewal','managed_standard','L1_DRAFT','R2','MONTHLY_CLOSE_V2_ALT',
   180000000000,'JPY',6,14,printf('%064x',203),'2026-06-20T00:00:00Z');

INSERT INTO offer_standard_step(
  offer_version_id,step_code,step_order,delivery_mode_code,
  required_in_standard_scope,standard_minutes_budget,permitted_execution_kind,
  evidence_ref_hash,recorded_at
) VALUES
  ('OV-CLOSE-1','INGEST',1,'managed_standard',1,10,'deterministic_tool',printf('%064x',211),'2026-05-20T00:00:00Z'),
  ('OV-CLOSE-1','RECONCILE',2,'founder_internal',1,20,'ai_assist',printf('%064x',212),'2026-05-20T00:00:00Z'),
  ('OV-CLOSE-1','APPROVE',3,'manual_bespoke',1,15,'human_only',printf('%064x',213),'2026-05-20T00:00:00Z'),
  ('OV-CLOSE-1','EXPORT',4,'managed_standard',1,5,'deterministic_tool',printf('%064x',214),'2026-05-20T00:00:00Z'),
  ('OV-CLOSE-2','INGEST',1,'customer_assisted',1,8,'deterministic_tool',printf('%064x',221),'2026-06-20T00:00:00Z'),
  ('OV-CLOSE-2','RECONCILE',2,'customer_assisted',1,12,'ai_agent_with_approval',printf('%064x',222),'2026-06-20T00:00:00Z'),
  ('OV-CLOSE-2','APPROVE',3,'customer_assisted',1,10,'human_only',printf('%064x',223),'2026-06-20T00:00:00Z'),
  ('OV-CLOSE-2','EXPORT',4,'customer_self_service',1,3,'deterministic_tool',printf('%064x',224),'2026-06-20T00:00:00Z');

INSERT INTO commercial_cycle(
  commercial_cycle_id,account_id,offer_version_id,commercial_motion_code,
  period_start,period_end,payment_due_at,contracted_amount_micros,
  agreement_ref_hash,currency,recorded_at
) VALUES
  ('CYCLE-01','ACC-01','OV-CLOSE-1','pilot','2026-06-01T00:00:00Z','2026-07-01T00:00:00Z','2026-06-15T00:00:00Z',300000000000,printf('%064x',301),'JPY','2026-05-25T00:00:00Z'),
  ('CYCLE-02','ACC-02','OV-CLOSE-1','pilot','2026-06-01T00:00:00Z','2026-07-01T00:00:00Z','2026-06-15T00:00:00Z',150000000000,printf('%064x',302),'JPY','2026-05-25T00:00:00Z'),
  ('CYCLE-03','ACC-03','OV-CLOSE-1','pilot','2026-06-01T00:00:00Z','2026-07-01T00:00:00Z','2026-06-20T00:00:00Z',200000000000,printf('%064x',303),'JPY','2026-05-25T00:00:00Z'),
  ('CYCLE-04','ACC-04','OV-CLOSE-1','pilot','2026-06-01T00:00:00Z','2026-07-01T00:00:00Z','2026-06-20T00:00:00Z',100000000000,printf('%064x',304),'JPY','2026-05-25T00:00:00Z'),
  ('CYCLE-05','ACC-01','OV-CLOSE-2','production','2026-07-01T00:00:00Z','2026-08-01T00:00:00Z','2026-07-15T00:00:00Z',150000000000,printf('%064x',305),'JPY','2026-06-25T00:00:00Z');

INSERT INTO commercial_event(
  commercial_event_id,commercial_cycle_id,event_type,amount_micros,currency,
  source_event_ref_hash,occurred_at,recorded_at
) VALUES
  ('CE-01-I','CYCLE-01','invoice_issued',300000000000,'JPY',printf('%064x',401),'2026-06-01T01:00:00Z','2026-06-01T01:05:00Z'),
  ('CE-01-P','CYCLE-01','payment_received',300000000000,'JPY',printf('%064x',402),'2026-06-12T01:00:00Z','2026-06-12T01:05:00Z'),
  ('CE-02-I','CYCLE-02','invoice_issued',150000000000,'JPY',printf('%064x',403),'2026-06-01T01:00:00Z','2026-06-01T01:05:00Z'),
  ('CE-03-I','CYCLE-03','invoice_issued',200000000000,'JPY',printf('%064x',404),'2026-06-01T01:00:00Z','2026-06-01T01:05:00Z'),
  ('CE-03-P','CYCLE-03','payment_received',200000000000,'JPY',printf('%064x',405),'2026-06-18T01:00:00Z','2026-06-18T01:05:00Z'),
  ('CE-04-I','CYCLE-04','invoice_issued',100000000000,'JPY',printf('%064x',406),'2026-06-01T01:00:00Z','2026-06-01T01:05:00Z'),
  ('CE-04-P','CYCLE-04','payment_received',100000000000,'JPY',printf('%064x',407),'2026-06-18T01:00:00Z','2026-06-18T01:05:00Z'),
  -- Stored but beyond both cutoffs; current views must not see it.
  ('CE-03-FUTURE-REFUND','CYCLE-03','refund_paid',200000000000,'JPY',printf('%064x',408),'2026-08-02T00:00:00Z','2026-08-02T00:05:00Z');

WITH RECURSIVE n(i) AS (
  VALUES(1) UNION ALL SELECT i + 1 FROM n WHERE i < 13
)
INSERT INTO service_job(
  service_job_id,account_id,offer_version_id,commercial_cycle_id,
  commercial_motion_code,delivery_mode_code,delegation_level_code,risk_tier_code,
  source_job_ref_hash,scope_class,eligibility_state,exclusion_reason_code,
  eligible_at,outcome_maturity_due_at,recorded_at
)
SELECT printf('JOB-%02d',i),
       CASE WHEN i <= 5 THEN 'ACC-01'
            WHEN i <= 8 THEN 'ACC-02'
            WHEN i <= 12 THEN 'ACC-03' ELSE 'ACC-04' END,
       'OV-CLOSE-1',
       CASE WHEN i <= 5 THEN 'CYCLE-01'
            WHEN i <= 8 THEN 'CYCLE-02'
            WHEN i <= 12 THEN 'CYCLE-03' ELSE 'CYCLE-04' END,
       'pilot','managed_standard','L1_DRAFT','R2',
       printf('%064x',1000 + i),
       CASE WHEN i IN (9,10) THEN 'custom' ELSE 'standard' END,
       'eligible',NULL,
       printf('2026-06-%02dT08:00:00Z',min(i + 1,28)),
       CASE WHEN i = 11 THEN '2026-08-15T00:00:00Z'
            ELSE '2026-07-01T00:00:00Z' END,
       CASE WHEN i = 12 THEN '2026-08-05T00:00:00Z'
            ELSE printf('2026-06-%02dT09:00:00Z',min(i + 1,28)) END
FROM n;

-- Wrong-offer/wrong-motion adversarial source: it is a valid job and exact
-- RECONCILE run, but can never bind to CAND-RECON's OV-CLOSE-1/pilot tuple.
INSERT INTO service_job(
  service_job_id,account_id,offer_version_id,commercial_cycle_id,
  commercial_motion_code,delivery_mode_code,delegation_level_code,risk_tier_code,
  source_job_ref_hash,scope_class,eligibility_state,exclusion_reason_code,
  eligible_at,outcome_maturity_due_at,recorded_at
) VALUES (
  'JOB-14','ACC-01','OV-CLOSE-2','CYCLE-05','production','customer_assisted',
  'L1_DRAFT','R2',printf('%064x',1014),'standard','eligible',NULL,
  '2026-07-02T08:00:00Z','2026-07-20T00:00:00Z','2026-07-02T09:00:00Z'
);

WITH RECURSIVE n(i) AS (
  VALUES(1) UNION ALL SELECT i + 1 FROM n WHERE i < 13
)
INSERT INTO service_job_state_event(
  state_event_id,service_job_id,event_sequence,execution_state,evidence_ref_hash,
  occurred_at,recorded_at
)
SELECT printf('JSE-%02d',i),printf('JOB-%02d',i),1,
       CASE WHEN i = 9 THEN 'failed' ELSE 'completed' END,
       printf('%064x',1100 + i),
       CASE WHEN i = 11 THEN '2026-07-20T10:00:00Z'
            ELSE '2026-07-02T10:00:00Z' END,
       CASE WHEN i = 12 THEN '2026-08-05T01:00:00Z'
            WHEN i = 11 THEN '2026-07-20T10:05:00Z'
            ELSE '2026-07-02T10:05:00Z' END
FROM n;

INSERT INTO service_job_state_event VALUES (
  'JSE-14','JOB-14',1,'completed',printf('%064x',1114),
  '2026-07-05T10:00:00Z','2026-07-05T10:05:00Z'
);

WITH RECURSIVE n(i) AS (
  VALUES(1) UNION ALL SELECT i + 1 FROM n WHERE i < 13
)
INSERT INTO job_step_run(
  job_step_run_id,service_job_id,offer_version_id,step_code,run_ordinal,
  execution_kind,delivery_mode_code,delegation_level_code,run_state,result_state,
  is_rework,input_ref_hash,output_ref_hash,started_at,completed_at,recorded_at
)
SELECT printf('RUN-%02d',i),printf('JOB-%02d',i),'OV-CLOSE-1','RECONCILE',1,
       'ai_assist','founder_internal','L1_DRAFT',
       CASE WHEN i = 9 THEN 'failed' ELSE 'completed' END,
       CASE WHEN i = 9 THEN 'fail' ELSE 'pass' END,
       0,printf('%064x',1200 + i),printf('%064x',1300 + i),
       printf('2026-06-%02dT10:00:00Z',min(i + 1,28)),
       printf('2026-06-%02dT10:20:00Z',min(i + 1,28)),
       CASE WHEN i = 12 THEN '2026-08-05T02:00:00Z'
            ELSE printf('2026-06-%02dT10:25:00Z',min(i + 1,28)) END
FROM n;

INSERT INTO job_step_run(
  job_step_run_id,service_job_id,offer_version_id,step_code,run_ordinal,
  execution_kind,delivery_mode_code,delegation_level_code,run_state,result_state,
  is_rework,input_ref_hash,output_ref_hash,started_at,completed_at,recorded_at
) VALUES
  ('RUN-14','JOB-14','OV-CLOSE-2','RECONCILE',1,'ai_agent','customer_assisted',
   'L1_DRAFT','completed','pass',0,printf('%064x',1214),printf('%064x',1314),
   '2026-07-05T10:00:00Z','2026-07-05T10:20:00Z','2026-07-05T10:25:00Z'),
  -- Terminal timestamps are beyond the global as-of and must not leak.
  ('RUN-01-FUTURE','JOB-01','OV-CLOSE-1','APPROVE',1,'human','manual_bespoke',
   'L1_DRAFT','completed','pass',0,printf('%064x',1298),printf('%064x',1398),
   '2026-07-30T10:00:00Z','2026-08-02T10:20:00Z','2026-08-02T10:25:00Z');

-- The high-risk EXPORT opportunity is a distinct attempt from RECONCILE.
INSERT INTO job_step_run(
  job_step_run_id,service_job_id,offer_version_id,step_code,run_ordinal,
  execution_kind,delivery_mode_code,delegation_level_code,run_state,result_state,
  is_rework,input_ref_hash,output_ref_hash,started_at,completed_at,recorded_at
) VALUES (
  'RUN-13-EXPORT','JOB-13','OV-CLOSE-1','EXPORT',1,
  'deterministic_tool','managed_standard','L1_DRAFT','completed','pass',0,
  printf('%064x',1213),printf('%064x',1313),
  '2026-06-14T10:30:00Z','2026-06-14T10:35:00Z','2026-06-14T10:40:00Z'
);

-- Twenty standard founder minutes per job. JOB-12 is intentionally late-loaded.
WITH RECURSIVE n(i) AS (
  VALUES(1) UNION ALL SELECT i + 1 FROM n WHERE i < 13
)
INSERT INTO human_work_log(
  human_work_log_id,service_job_id,job_step_run_id,worker_class,work_class,
  minutes,internal_rate_micros_per_minute,cash_cost_micros,
  source_event_ref_hash,occurred_at,recorded_at
)
SELECT printf('HW-STD-%02d',i),printf('JOB-%02d',i),printf('RUN-%02d',i),
       'founder','standard_delivery',20,100000000,0,
       printf('%064x',1400 + i),
       printf('2026-06-%02dT10:30:00Z',min(i + 1,28)),
       CASE WHEN i = 12 THEN '2026-08-05T03:00:00Z'
            ELSE printf('2026-06-%02dT10:35:00Z',min(i + 1,28)) END
FROM n;

INSERT INTO human_work_log(
  human_work_log_id,service_job_id,job_step_run_id,worker_class,work_class,
  minutes,internal_rate_micros_per_minute,cash_cost_micros,
  source_event_ref_hash,occurred_at,recorded_at
) VALUES
  ('HW-R-03','JOB-03','RUN-03','founder','founder_rescue',5,100000000,0,printf('%064x',1503),'2026-06-04T11:00:00Z','2026-06-04T11:05:00Z'),
  ('HW-R-04','JOB-04','RUN-04','founder','founder_rescue',10,100000000,0,printf('%064x',1504),'2026-06-05T11:00:00Z','2026-06-05T11:05:00Z'),
  ('HW-R-05','JOB-05','RUN-05','founder','founder_rescue',15,100000000,0,printf('%064x',1505),'2026-06-06T11:00:00Z','2026-06-06T11:05:00Z'),
  ('HW-R-06','JOB-06','RUN-06','founder','founder_rescue',20,100000000,0,printf('%064x',1506),'2026-06-07T11:00:00Z','2026-06-07T11:05:00Z'),
  ('HW-R-07','JOB-07','RUN-07','founder','founder_rescue',30,100000000,0,printf('%064x',1507),'2026-06-08T11:00:00Z','2026-06-08T11:05:00Z'),
  ('HW-R-08','JOB-08','RUN-08','founder','founder_rescue',40,100000000,0,printf('%064x',1508),'2026-06-09T11:00:00Z','2026-06-09T11:05:00Z'),
  ('HW-R-09','JOB-09','RUN-09','founder','founder_rescue',120,100000000,0,printf('%064x',1509),'2026-06-10T11:00:00Z','2026-06-10T11:05:00Z'),
  ('HW-R-13','JOB-13','RUN-13','founder','founder_rescue',60,100000000,0,printf('%064x',1513),'2026-06-14T11:00:00Z','2026-06-14T11:05:00Z'),
  ('HW-C-01','JOB-01','RUN-01','contractor','planned_review',10,0,2000000000,printf('%064x',1591),'2026-06-02T12:00:00Z','2026-06-02T12:05:00Z');

WITH RECURSIVE n(i) AS (
  VALUES(1) UNION ALL SELECT i + 1 FROM n WHERE i < 13
)
INSERT INTO variable_cost_event(
  variable_cost_event_id,service_job_id,commercial_cycle_id,cost_class,
  amount_micros,currency,source_event_ref_hash,occurred_at,recorded_at
)
SELECT printf('VC-%02d',i),printf('JOB-%02d',i),
       CASE WHEN i <= 5 THEN 'CYCLE-01'
            WHEN i <= 8 THEN 'CYCLE-02'
            WHEN i <= 12 THEN 'CYCLE-03' ELSE 'CYCLE-04' END,
       'provider',1000000000,'JPY',printf('%064x',1600 + i),
       printf('2026-06-%02dT12:00:00Z',min(i + 1,28)),
       CASE WHEN i = 12 THEN '2026-08-05T04:00:00Z'
            ELSE printf('2026-06-%02dT12:05:00Z',min(i + 1,28)) END
FROM n;

-- Value observations occur after the early decision cutoff. JOB-11 is observed
-- but not mature; JOB-12 is recorded after the global watermark.
WITH RECURSIVE n(i) AS (
  VALUES(1) UNION ALL SELECT i + 1 FROM n WHERE i < 13
)
INSERT INTO acceptance_event(
  acceptance_event_id,acceptance_key,service_job_id,event_sequence,
  event_type,acceptance_state,acceptance_source,evidence_ref_hash,
  occurred_at,recorded_at
)
SELECT printf('ACCEPT-%02d',i),'ACCEPTANCE:' || printf('JOB-%02d',i),
       printf('JOB-%02d',i),1,'observed',
       CASE WHEN i IN (8,9) THEN 'rejected' ELSE 'accepted' END,
       'customer_owner',printf('%064x',1650 + i),'2026-07-03T10:00:00Z',
       CASE WHEN i = 12 THEN '2026-08-05T04:30:00Z'
            ELSE '2026-07-03T10:05:00Z' END
FROM n;

INSERT INTO acceptance_event VALUES (
  'ACCEPT-14','ACCEPTANCE:JOB-14','JOB-14',1,'observed','accepted',
  'customer_owner',printf('%064x',1664),
  '2026-07-06T10:00:00Z','2026-07-06T10:05:00Z'
);

WITH RECURSIVE n(i) AS (
  VALUES(1) UNION ALL SELECT i + 1 FROM n WHERE i < 13
)
INSERT INTO value_confirmation(
  value_confirmation_event_id,confirmation_key,service_job_id,event_sequence,
  event_type,value_state,confirmation_source,independence_state,
  evidence_ref_hash,occurred_at,recorded_at
)
SELECT printf('VAL-%02d',i),'VALUE:' || printf('JOB-%02d',i),printf('JOB-%02d',i),1,
       'observed',
       CASE WHEN i IN (8,9) THEN 'not_confirmed' ELSE 'confirmed' END,
       'customer_owner',
       CASE WHEN i IN (8,9) THEN 'not_applicable' ELSE 'independent' END,
       printf('%064x',1700 + i),'2026-07-15T10:00:00Z',
       CASE WHEN i = 12 THEN '2026-08-05T05:00:00Z'
            ELSE '2026-07-15T10:05:00Z' END
FROM n;

INSERT INTO value_confirmation VALUES (
  'VAL-14','VALUE:JOB-14','JOB-14',1,'observed','confirmed','customer_owner',
  'independent',printf('%064x',1714),
  '2026-07-07T10:00:00Z','2026-07-07T10:05:00Z'
);

INSERT INTO exception_event(
  exception_event_id,exception_key,service_job_id,job_step_run_id,step_code,
  event_sequence,event_type,
  cause_class,treatment_code,severity,responsibility_code,nonwaivable,
  evidence_ref_hash,occurred_at,recorded_at
) VALUES
  ('EXE-08-1','EX-JOB-08','JOB-08','RUN-08','RECONCILE',1,'opened','input_contract','customer_action_requested','medium','customer_dependency',0,printf('%064x',1801),'2026-07-05T08:00:00Z','2026-07-05T08:05:00Z'),
  ('EXE-08-2','EX-JOB-08','JOB-08','RUN-08','RECONCILE',2,'resolved','input_contract','auto_handled','low','shared',0,printf('%064x',1802),'2026-07-16T08:00:00Z','2026-07-16T08:05:00Z'),
  ('EXE-09-1','EX-JOB-09','JOB-09','RUN-09','RECONCILE',1,'opened','customer_specific_request','planned_review','medium','founder_process',0,printf('%064x',1803),'2026-07-05T08:00:00Z','2026-07-05T08:05:00Z'),
  ('EXE-09-2','EX-JOB-09','JOB-09','RUN-09','RECONCILE',2,'resolved','customer_specific_request','scope_change','low','founder_process',0,printf('%064x',1804),'2026-07-16T08:00:00Z','2026-07-16T08:05:00Z'),
  ('EXE-10-1','EX-JOB-10','JOB-10','RUN-10','RECONCILE',1,'opened','product_defect','founder_rescue','medium','product',0,printf('%064x',1805),'2026-07-05T08:00:00Z','2026-07-05T08:05:00Z'),
  ('EXE-10-2','EX-JOB-10','JOB-10','RUN-10','RECONCILE',2,'resolved','product_defect','correction','low','product',0,printf('%064x',1806),'2026-07-16T08:00:00Z','2026-07-16T08:05:00Z'),
  -- Low severity still vetoes because the cause class itself is security/legal.
  ('EXE-13-1','EX-JOB-13','JOB-13','RUN-13-EXPORT','EXPORT',1,'opened','security_privacy_safety_legal','stop_and_escalate','low','product',0,printf('%064x',1813),'2026-07-05T08:00:00Z','2026-07-05T08:05:00Z'),
  ('EXE-UNKNOWN-1','EX-UNKNOWN-JOB-01','JOB-01','RUN-01','RECONCILE',1,'opened','unknown','pause','low','unknown',0,printf('%064x',1814),'2026-08-10T08:00:00Z','2026-08-10T08:05:00Z');

INSERT INTO automation_candidate(
  automation_candidate_id,offer_version_id,step_code,commercial_motion_code,
  target_delivery_mode_code,target_delegation_level_code,candidate_risk_tier_code,
  min_mature_source_jobs,min_contracted_accepted_outcomes,min_exception_evidence_jobs,
  min_standard_scope_rate_ppm,min_rescue_sample_jobs,max_p90_rescue_minutes,
  max_any_rescue_minutes,max_open_high_exceptions,min_consented_migrations,
  requires_rollback_evidence,requires_owner_approval,hypothesis_ref_hash,
  created_at,recorded_at
) VALUES
  ('CAND-RECON','OV-CLOSE-1','RECONCILE','pilot',
   'customer_assisted','L2_REVERSIBLE_WRITE','R2',
   10,6,3,800000,10,45,120,0,1,1,1,printf('%064x',1901),
   '2026-07-01T00:00:00Z','2026-07-01T00:05:00Z'),
  ('CAND-AUTOSEND','OV-CLOSE-1','EXPORT','pilot',
   'customer_self_service','L4_PREAUTHORIZED_EXECUTE','R3',
   1,1,1,1000000,1,60,60,0,0,1,1,printf('%064x',1902),
   '2026-07-01T00:00:00Z','2026-07-01T00:05:00Z'),
  ('CAND-UNKNOWN','OV-CLOSE-1','RECONCILE','pilot',
   'customer_assisted','L2_REVERSIBLE_WRITE','R2',
   1,1,0,1000000,1,0,0,0,0,1,1,printf('%064x',1903),
   '2026-08-06T00:00:00Z','2026-08-06T00:05:00Z');

WITH RECURSIVE n(i) AS (
  VALUES(1) UNION ALL SELECT i + 1 FROM n WHERE i < 10
)
INSERT INTO candidate_source_job(
  automation_candidate_id,service_job_id,evidence_role,evidence_ref_hash,
  linked_at,recorded_at
)
SELECT 'CAND-RECON',printf('JOB-%02d',i),
       CASE WHEN i = 8 THEN 'boundary'
            WHEN i = 9 THEN 'failure'
            WHEN i = 10 THEN 'exception'
            ELSE 'positive' END,
       printf('%064x',2000 + i),'2026-07-05T00:00:00Z','2026-07-05T00:05:00Z'
FROM n;

INSERT INTO candidate_source_job VALUES
  ('CAND-AUTOSEND','JOB-13','failure',printf('%064x',2013),'2026-07-05T00:00:00Z','2026-07-05T00:05:00Z');

INSERT INTO candidate_source_job VALUES
  ('CAND-UNKNOWN','JOB-01','boundary',printf('%064x',2024),
   '2026-08-06T01:00:00Z','2026-08-06T01:05:00Z');

-- These job-level links are deliberate adversarial fixtures. Without an exact
-- canonical step-run link they must never enter candidate evidence.
INSERT INTO candidate_source_job VALUES
  ('CAND-RECON','JOB-11','positive',printf('%064x',2021),'2026-07-05T00:00:00Z','2026-07-05T00:05:00Z'),
  ('CAND-AUTOSEND','JOB-01','positive',printf('%064x',2022),'2026-07-05T00:00:00Z','2026-07-05T00:05:00Z'),
  ('CAND-RECON','JOB-14','positive',printf('%064x',2023),'2026-07-08T00:00:00Z','2026-07-08T00:05:00Z');

WITH RECURSIVE n(i) AS (
  VALUES(1) UNION ALL SELECT i + 1 FROM n WHERE i < 10
)
INSERT INTO candidate_source_step_run(
  automation_candidate_id,service_job_id,job_step_run_id,offer_version_id,
  step_code,commercial_motion_code,linked_at,recorded_at
)
SELECT 'CAND-RECON',printf('JOB-%02d',i),printf('RUN-%02d',i),'OV-CLOSE-1',
       'RECONCILE','pilot','2026-07-05T00:00:00Z','2026-07-05T00:05:00Z'
FROM n;

INSERT INTO candidate_source_step_run VALUES
  ('CAND-AUTOSEND','JOB-13','RUN-13-EXPORT','OV-CLOSE-1','EXPORT','pilot',
   '2026-07-05T00:00:00Z','2026-07-05T00:05:00Z'),
  ('CAND-UNKNOWN','JOB-01','RUN-01','OV-CLOSE-1','RECONCILE','pilot',
   '2026-08-06T01:00:00Z','2026-08-06T01:05:00Z');

INSERT INTO customer_migration(
  customer_migration_event_id,migration_id,automation_candidate_id,account_id,
  from_offer_version_id,to_offer_version_id,event_sequence,migration_state,
  consent_evidence_ref_hash,evidence_ref_hash,occurred_at,recorded_at
) VALUES
  ('MIG-01-1','MIG-01','CAND-RECON','ACC-01','OV-CLOSE-1','OV-CLOSE-2',1,'proposed',NULL,printf('%064x',2101),'2026-07-18T00:00:00Z','2026-07-18T00:05:00Z'),
  ('MIG-01-2','MIG-01','CAND-RECON','ACC-01','OV-CLOSE-1','OV-CLOSE-2',2,'consented',printf('%064x',2191),printf('%064x',2102),'2026-07-20T00:00:00Z','2026-07-20T00:05:00Z'),
  ('MIG-01-3','MIG-01','CAND-RECON','ACC-01','OV-CLOSE-1','OV-CLOSE-2',3,'migrated',printf('%064x',2191),printf('%064x',2103),'2026-07-25T00:00:00Z','2026-07-25T00:05:00Z'),
  ('MIG-01-4','MIG-01','CAND-RECON','ACC-01','OV-CLOSE-1','OV-CLOSE-2',4,'verified',printf('%064x',2191),printf('%064x',2104),'2026-07-28T00:00:00Z','2026-07-28T00:05:00Z'),
  ('MIG-02-1','MIG-02','CAND-RECON','ACC-02','OV-CLOSE-1','OV-CLOSE-2',1,'proposed',NULL,printf('%064x',2111),'2026-07-18T00:00:00Z','2026-07-18T00:05:00Z'),
  ('MIG-02-2','MIG-02','CAND-RECON','ACC-02','OV-CLOSE-1','OV-CLOSE-2',2,'declined',NULL,printf('%064x',2112),'2026-07-22T00:00:00Z','2026-07-22T00:05:00Z');

INSERT INTO promotion_evidence_snapshot(
  promotion_evidence_snapshot_id,automation_candidate_id,snapshot_sequence,exposure_state,
  target_delivery_mode_code,target_delegation_level_code,risk_tier_code,
  evidence_as_of,evidence_watermark_at,independent_pause_blocker_code,
  rollback_evidence_ref_hash,owner_approval_ref_hash,snapshot_ref_hash,
  occurred_at,recorded_at
) VALUES
  ('SNAP-RECON-01','CAND-RECON',1,'pre_exposure',
   'customer_assisted','L2_REVERSIBLE_WRITE','R2',
   '2026-07-10T23:00:00Z','2026-07-10T22:50:00Z',NULL,
   NULL,NULL,printf('%064x',2251),
   '2026-07-10T23:02:00Z','2026-07-10T23:05:00Z'),
  ('SNAP-RECON-02','CAND-RECON',2,'pre_exposure',
   'customer_assisted','L2_REVERSIBLE_WRITE','R2',
   '2026-07-29T23:00:00Z','2026-07-29T22:50:00Z',NULL,
   printf('%064x',2203),printf('%064x',2204),printf('%064x',2252),
   '2026-07-29T23:02:00Z','2026-07-29T23:05:00Z'),
  ('SNAP-RECON-03','CAND-RECON',3,'active_exposure',
   'customer_assisted','L2_REVERSIBLE_WRITE','R2',
   '2026-07-30T23:00:00Z','2026-07-30T22:50:00Z',NULL,
   printf('%064x',2205),printf('%064x',2206),printf('%064x',2253),
   '2026-07-30T23:02:00Z','2026-07-30T23:05:00Z'),
  ('SNAP-RECON-04','CAND-RECON',4,'active_exposure',
   'customer_assisted','L2_REVERSIBLE_WRITE','R2',
   '2026-08-05T22:00:00Z','2026-08-05T21:50:00Z',
   'STALE_SAFETY_AUTHORITY_FALLBACK',
   printf('%064x',2207),printf('%064x',2208),printf('%064x',2254),
   '2026-08-05T22:02:00Z','2026-08-05T22:05:00Z'),
  ('SNAP-AUTOSEND-01','CAND-AUTOSEND',1,'pre_exposure',
   'customer_self_service','L4_PREAUTHORIZED_EXECUTE','R3',
   '2026-07-29T23:00:00Z','2026-07-29T22:50:00Z',NULL,
   printf('%064x',2211),printf('%064x',2212),printf('%064x',2261),
   '2026-07-29T23:02:00Z','2026-07-29T23:05:00Z'),
  ('SNAP-UNKNOWN-01','CAND-UNKNOWN',1,'pre_exposure',
   'customer_assisted','L2_REVERSIBLE_WRITE','R2',
   '2026-08-15T23:00:00Z','2026-08-15T22:50:00Z',NULL,
   printf('%064x',2213),printf('%064x',2214),printf('%064x',2262),
   '2026-08-15T23:02:00Z','2026-08-15T23:05:00Z');

INSERT INTO promotion_metric_snapshot(
  promotion_evidence_snapshot_id,automation_candidate_id,
  workflow_id,workflow_version,segment_code,data_class_code,step_code,
  source_step_delivery_mode_code,commercial_motion_code,
  visible_source_jobs,mature_source_jobs,mature_standard_scope_jobs,
  standard_scope_rate_ppm,accepted_outcomes,value_confirmed_outcomes,
  contracted_accepted_outcomes,cash_collected_value_confirmed_outcomes,
  value_confirmed_without_cash_outcomes,exception_evidence_jobs,
  rescue_sample_jobs,p90_rescue_minutes,max_rescue_minutes,
  open_high_exceptions,open_critical_exceptions,open_nonwaivable_exceptions,
  open_security_exceptions,open_unknown_exceptions,
  consented_migrations,verified_migrations,
  metric_snapshot_ref_hash,materialized_at
)
SELECT e.promotion_evidence_snapshot_id,e.automation_candidate_id,
       e.workflow_id,e.workflow_version,e.segment_code,e.data_class_code,e.step_code,
       e.source_step_delivery_mode_code,e.commercial_motion_code,
       e.visible_source_jobs,e.mature_source_jobs,e.mature_standard_scope_jobs,
       e.standard_scope_rate_ppm,e.accepted_outcomes,e.value_confirmed_outcomes,
       e.contracted_accepted_outcomes,e.cash_collected_value_confirmed_outcomes,
       e.value_confirmed_without_cash_outcomes,e.exception_evidence_jobs,
       e.rescue_sample_jobs,e.p90_rescue_minutes,e.max_rescue_minutes,
       e.open_high_exceptions,e.open_critical_exceptions,e.open_nonwaivable_exceptions,
       e.open_security_exceptions,e.open_unknown_exceptions,
       e.consented_migrations,e.verified_migrations,
       printf('%064x',2350 + row_number() OVER (
         ORDER BY e.promotion_evidence_snapshot_id
       )),
       p.recorded_at
FROM v_candidate_evidence_by_cutoff e
JOIN promotion_evidence_snapshot p USING (promotion_evidence_snapshot_id)
WHERE e.cutoff_kind = 'snapshot';

-- SNAP-RECON-01 deliberately omits customer_value. Every other fixture
-- snapshot carries a complete required-domain set. Missing is not PASS.
INSERT INTO promotion_gate_evidence(
  promotion_evidence_snapshot_id,domain_code,result_code,evidence_ref_hash,
  observed_at,recorded_at
)
SELECT p.promotion_evidence_snapshot_id,r.domain_code,'PASS',
       printf('%064x',2400 + row_number() OVER (
         ORDER BY p.promotion_evidence_snapshot_id,r.domain_code
       )),
       datetime(p.evidence_watermark_at,'-2 minutes'),
       datetime(p.evidence_watermark_at,'-1 minute')
FROM promotion_evidence_snapshot p
JOIN delivery_mode_required_gate_domain r
  ON r.delivery_mode_code = p.target_delivery_mode_code
WHERE NOT (
  p.promotion_evidence_snapshot_id = 'SNAP-RECON-01'
  AND r.domain_code = 'customer_value'
);

INSERT INTO promotion_decision(
  promotion_decision_id,automation_candidate_id,promotion_evidence_snapshot_id,
  decision_sequence,decision_code,target_delivery_mode_code,
  target_delegation_level_code,risk_tier_code,occurred_at,recorded_at
) VALUES
  ('PD-RECON-01','CAND-RECON','SNAP-RECON-01',1,'PAUSE',
   'customer_assisted','L2_REVERSIBLE_WRITE','R2',
   '2026-07-10T23:10:00Z','2026-07-10T23:15:00Z'),
  ('PD-RECON-02','CAND-RECON','SNAP-RECON-02',2,'LIMITED_CANARY',
   'customer_assisted','L2_REVERSIBLE_WRITE','R2',
   '2026-07-30T00:00:00Z','2026-07-30T00:05:00Z'),
  ('PD-RECON-03','CAND-RECON','SNAP-RECON-03',3,'PROMOTE',
   'customer_assisted','L2_REVERSIBLE_WRITE','R2',
   '2026-07-31T00:00:00Z','2026-07-31T00:05:00Z'),
  -- Raw future reversal is stored but absent from current/as-of action views.
  ('PD-RECON-04','CAND-RECON','SNAP-RECON-04',4,'REVERSE',
   'customer_assisted','L2_REVERSIBLE_WRITE','R2',
   '2026-08-05T23:00:00Z','2026-08-05T23:05:00Z'),
  ('PD-AUTOSEND-01','CAND-AUTOSEND','SNAP-AUTOSEND-01',1,'STOP',
   'customer_self_service','L4_PREAUTHORIZED_EXECUTE','R3',
   '2026-07-30T00:00:00Z','2026-07-30T00:05:00Z'),
  ('PD-UNKNOWN-01','CAND-UNKNOWN','SNAP-UNKNOWN-01',1,'PAUSE',
   'customer_assisted','L2_REVERSIBLE_WRITE','R2',
   '2026-08-16T00:00:00Z','2026-08-16T00:05:00Z');

INSERT INTO weekly_review(
  weekly_review_event_id,review_key,review_sequence,review_week_start,
  offer_version_id,automation_candidate_id,commercial_motion_code,
  delivery_mode_code,delegation_level_code,risk_tier_code,
  evidence_as_of,evidence_watermark_at,review_decision_code,bottleneck_code,
  next_action_code,snapshot_ref_hash,occurred_at,recorded_at
) VALUES
  ('WR-RECON-01','WEEK-CAND-RECON-2026-07-06',1,'2026-07-06',
   'OV-CLOSE-1','CAND-RECON','pilot','founder_internal','L1_DRAFT','R2',
   '2026-07-10T23:00:00Z','2026-07-10T22:50:00Z','continue','value','observe',
   printf('%064x',2301),'2026-07-10T23:20:00Z','2026-07-10T23:25:00Z'),
  ('WR-RECON-02','WEEK-CAND-RECON-2026-07-27',1,'2026-07-27',
   'OV-CLOSE-1','CAND-RECON','pilot','customer_assisted','L2_REVERSIBLE_WRITE','R2',
   '2026-07-29T23:00:00Z','2026-07-29T22:50:00Z','promote','none','migrate',
   printf('%064x',2302),'2026-07-30T00:10:00Z','2026-07-30T00:15:00Z'),
  ('WR-AUTOSEND-01','WEEK-CAND-AUTOSEND-2026-07-27',1,'2026-07-27',
   'OV-CLOSE-1','CAND-AUTOSEND','pilot','customer_self_service','L4_PREAUTHORIZED_EXECUTE','R3',
   '2026-07-29T23:00:00Z','2026-07-29T22:50:00Z','stop','exception','stop',
   printf('%064x',2303),'2026-07-30T00:10:00Z','2026-07-30T00:15:00Z');

-- -----------------------------------------------------------------------------
-- Executable assertions. failure_count must be zero for every error row.
-- -----------------------------------------------------------------------------

-- TEMP keeps SQLite schema-introspection virtual tables out of persistent schema
-- objects, so these executable checks remain compatible with trusted_schema=OFF.
CREATE TEMP VIEW v_assertion_results AS
SELECT 'DQ01' AS check_id, 'error' AS severity,
       'required_grain_tables_present' AS check_name,
       CASE WHEN (
         SELECT count(*) FROM sqlite_schema
         WHERE type = 'table' AND name IN (
           'workflow_scope_version','offer_version','commercial_cycle','service_job',
           'job_step_run','human_work_log','exception_event','acceptance_event',
           'value_confirmation','automation_candidate','candidate_source_job',
           'candidate_source_step_run','promotion_evidence_snapshot',
           'promotion_metric_snapshot','promotion_gate_evidence',
           'promotion_decision','customer_migration','weekly_review'
         )
       ) = 18 THEN 0 ELSE 1 END AS failure_count,
       'All required grains exist as physical tables' AS expectation
UNION ALL SELECT 'DQ02','error','append_only_guards_present',
       CASE WHEN (SELECT count(*) FROM sqlite_schema
                  WHERE type = 'trigger' AND name LIKE 'immutable_%') = 64
            THEN 0 ELSE 1 END,
       'Thirty-two immutable raw/version/policy tables each reject UPDATE and DELETE'
UNION ALL SELECT 'DQ03','error','four_axes_are_separate_service_job_columns',
       CASE WHEN (
         SELECT count(*) FROM pragma_table_info('service_job')
         WHERE name IN (
           'commercial_motion_code','delivery_mode_code',
           'delegation_level_code','risk_tier_code'
         )
       ) = 4 THEN 0 ELSE 1 END,
       'Commercial motion, delivery mode, delegation, and risk are independent columns'
UNION ALL SELECT 'DQ04','error','productization_scope_tuple_present',
       CASE WHEN (
         SELECT count(*) FROM pragma_table_info('offer_version')
         WHERE name IN ('workflow_id','workflow_version','segment_code','data_class_code')
       ) = 4 THEN 0 ELSE 1 END,
       'Offer version fixes workflow/version/segment/data-class promotion scope'
UNION ALL SELECT 'DQ05','error','step_delivery_mode_is_independent',
       CASE WHEN (
         (SELECT count(*) FROM pragma_table_info('offer_standard_step')
          WHERE name = 'delivery_mode_code') = 1
         AND (SELECT count(*) FROM pragma_table_info('job_step_run')
              WHERE name = 'delivery_mode_code') = 1
       ) THEN 0 ELSE 1 END,
       'Standard step and run each store delivery mode separately from execution/delegation'
UNION ALL SELECT 'DQ06','error','forbidden_personal_data_columns',
       (SELECT count(*) FROM (
          SELECT name FROM pragma_table_info('customer_account') UNION ALL
          SELECT name FROM pragma_table_info('offer_version') UNION ALL
          SELECT name FROM pragma_table_info('service_job') UNION ALL
          SELECT name FROM pragma_table_info('job_step_run') UNION ALL
          SELECT name FROM pragma_table_info('human_work_log') UNION ALL
          SELECT name FROM pragma_table_info('exception_event') UNION ALL
          SELECT name FROM pragma_table_info('acceptance_event') UNION ALL
          SELECT name FROM pragma_table_info('value_confirmation') UNION ALL
          SELECT name FROM pragma_table_info('automation_candidate') UNION ALL
          SELECT name FROM pragma_table_info('candidate_source_step_run') UNION ALL
          SELECT name FROM pragma_table_info('promotion_evidence_snapshot') UNION ALL
          SELECT name FROM pragma_table_info('promotion_gate_evidence') UNION ALL
          SELECT name FROM pragma_table_info('promotion_decision') UNION ALL
          SELECT name FROM pragma_table_info('customer_migration') UNION ALL
          SELECT name FROM pragma_table_info('weekly_review')
        ) columns_to_scan
        WHERE lower(name) IN (
          'name','full_name','email','phone','address','personal_id',
          'raw_payload','customer_text','document_text','prompt_text','output_text'
        ) OR lower(name) LIKE '%email%' OR lower(name) LIKE '%phone%'),
       'No personal identifiers or raw customer content surface exists'
UNION ALL SELECT 'DQ07','error','offer_version_overlap',
       (SELECT count(*)
        FROM offer_version a JOIN offer_version b
          ON a.offer_code = b.offer_code AND a.version_number < b.version_number
        WHERE datetime(a.effective_from) < datetime(coalesce(b.effective_to,'9999-12-31'))
          AND datetime(b.effective_from) < datetime(coalesce(a.effective_to,'9999-12-31'))),
       'Versions of the same offer do not overlap in effective time'
UNION ALL SELECT 'DQ08','error','visible_job_fact_cardinality',
       CASE WHEN (SELECT count(*) FROM v_service_job_facts)
                    = (SELECT count(*) FROM v_service_job_asof)
            THEN 0 ELSE 1 END,
       'Current fact projection stays one row per visible service job'
UNION ALL SELECT 'DQ09','error','future_or_late_job_leakage',
       (SELECT count(*) FROM v_service_job_asof WHERE service_job_id = 'JOB-12'),
       'A job recorded after the watermark is absent from current views'
UNION ALL SELECT 'DQ10','error','future_commercial_event_leakage',
       (SELECT count(*) FROM v_commercial_event_asof
        WHERE commercial_event_id = 'CE-03-FUTURE-REFUND'),
       'A future refund cannot alter the current commercial snapshot'
UNION ALL SELECT 'DQ11','error','asof_view_cutoff_violation',
       (SELECT count(*) FROM (
          SELECT occurred_at,recorded_at FROM v_commercial_event_asof UNION ALL
          SELECT occurred_at,recorded_at FROM v_service_job_state_event_asof UNION ALL
          SELECT occurred_at,recorded_at FROM v_human_work_log_asof UNION ALL
          SELECT occurred_at,recorded_at FROM v_variable_cost_event_asof UNION ALL
          SELECT occurred_at,recorded_at FROM v_exception_event_asof UNION ALL
          SELECT occurred_at,recorded_at FROM v_acceptance_event_asof UNION ALL
          SELECT occurred_at,recorded_at FROM v_value_confirmation_asof
        ) visible_events CROSS JOIN model_settings s
        WHERE datetime(visible_events.occurred_at) > datetime(s.event_time_as_of)
           OR datetime(visible_events.recorded_at) > datetime(s.data_watermark_at)),
       'Every visible event obeys both event-time and ingestion cutoffs'
UNION ALL SELECT 'DQ12','error','premature_outcome_counted',
       (SELECT count(*) FROM v_service_job_facts
        WHERE datetime(outcome_maturity_due_at) > datetime((SELECT event_time_as_of FROM model_settings))
          AND (outcome_mature = 1 OR accepted_outcome = 1
               OR value_confirmed_outcome = 1 OR contracted_accepted_outcome = 1
               OR cash_collected_value_confirmed_outcome = 1)),
       'Observed future-maturity value remains provisional'
UNION ALL SELECT 'DQ13','error','acceptance_value_cash_recombined',
       (SELECT count(*) FROM v_service_job_facts
        WHERE cash_collected_value_confirmed_outcome = 1
          AND (accepted_outcome <> 1 OR value_confirmed_outcome <> 1
               OR cash_collected <> 1)),
       'Combined cash/value outcome can only derive from three independent facts'
UNION ALL SELECT 'DQ14','error','candidate_semantic_split_fixture_mismatch',
       (SELECT CASE WHEN contracted_accepted_outcomes = 8
                          AND cash_collected_value_confirmed_outcomes = 6
                          AND value_confirmed_without_cash_outcomes = 2
                    THEN 0 ELSE 1 END
        FROM v_candidate_evidence
        WHERE automation_candidate_id = 'CAND-RECON'),
       'Contracted acceptance, value, and cash remain visibly separate'
UNION ALL SELECT 'DQ15','error','candidate_scope_fixture_mismatch',
       (SELECT CASE WHEN mature_source_jobs = 10
                          AND mature_standard_scope_jobs = 8
                          AND standard_scope_rate_ppm = 800000
                    THEN 0 ELSE 1 END
        FROM v_candidate_evidence
        WHERE automation_candidate_id = 'CAND-RECON'),
       'Candidate evidence has 8/10 standard-scope mature jobs'
UNION ALL SELECT 'DQ16','error','candidate_rescue_fixture_mismatch',
       (SELECT CASE WHEN rescue_sample_jobs = 10
                          AND p90_rescue_minutes = 40
                          AND max_rescue_minutes = 120
                    THEN 0 ELSE 1 END
        FROM v_candidate_evidence
        WHERE automation_candidate_id = 'CAND-RECON'),
       'Candidate exposes sample size, nearest-rank p90, and all-job maximum'
UNION ALL SELECT 'DQ17','error','historical_decision_future_leakage',
       (SELECT CASE WHEN contracted_accepted_outcomes = 8
                          AND value_confirmed_outcomes = 0
                          AND cash_collected_value_confirmed_outcomes = 0
                          AND consented_migrations = 0
                    THEN 0 ELSE 1 END
        FROM v_candidate_evidence_by_cutoff
        WHERE cutoff_id = 'SNAP-RECON-01'),
       'Early independent snapshot sees acceptance but not later value/migration evidence'
UNION ALL SELECT 'DQ18','error','historical_unknown_gate_mismatch',
       (SELECT CASE WHEN derived_gate_state = 'UNKNOWN' THEN 0 ELSE 1 END
        FROM v_promotion_gate_readiness
        WHERE promotion_evidence_snapshot_id = 'SNAP-RECON-01'),
       'Missing early evidence derives UNKNOWN'
UNION ALL SELECT 'DQ19','error','promotion_ready_gate_mismatch',
       (SELECT CASE WHEN derived_gate_state = 'READY_TO_DECIDE'
                          AND passed_required_domain_count = required_domain_count
                          AND rescue_sample_gate = 1
                          AND rescue_p90_gate = 1
                          AND rescue_max_gate = 1
                    THEN 0 ELSE 1 END
        FROM v_promotion_gate_readiness
        WHERE promotion_evidence_snapshot_id = 'SNAP-RECON-02'),
       'Ready requires every domain plus sample, p90, max, scope, and approvals'
UNION ALL SELECT 'DQ20','error','security_cause_veto_mismatch',
       (SELECT CASE WHEN derived_gate_state = 'STOP'
                          AND open_security_exceptions = 1
                          AND open_critical_exceptions = 0
                          AND open_nonwaivable_exceptions = 0
                    THEN 0 ELSE 1 END
        FROM v_promotion_gate_readiness
        WHERE promotion_evidence_snapshot_id = 'SNAP-AUTOSEND-01'),
       'Open security/privacy/safety/legal cause vetoes even at low severity'
UNION ALL SELECT 'DQ21','error','incompatible_recorded_action',
       (SELECT count(*) FROM v_promotion_decision_compatibility
        WHERE recorded_decision_compatible <> 1),
       'Every stored action is compatible with its prior independent gate state'
UNION ALL SELECT 'DQ22','error','current_standard_scope_fixture_mismatch',
       (SELECT CASE WHEN mature_eligible_jobs = 11
                          AND standard_scope_jobs = 9
                          AND standard_scope_rate_ppm = 818181
                    THEN 0 ELSE 1 END
        FROM v_standard_scope_rate
        WHERE offer_version_id = 'OV-CLOSE-1'),
       'Current mature cohort keeps failures and the veto job in its scope denominator'
UNION ALL SELECT 'DQ23','error','founder_minutes_fixture_mismatch',
       (SELECT CASE WHEN founder_minutes_all_mature_jobs = 520
                          AND contracted_accepted_outcomes = 9
                          AND cash_collected_value_confirmed_outcomes = 7
                          AND founder_minutes_per_contracted_accepted_outcome = 57.8
                    THEN 0 ELSE 1 END
        FROM v_founder_minutes_per_accepted_outcome
        WHERE offer_version_id = 'OV-CLOSE-1'),
       'Founder minutes include failures and rescue before contracted acceptance division'
UNION ALL SELECT 'DQ24','error','current_rescue_p90_fixture_mismatch',
       (SELECT CASE WHEN mature_eligible_jobs = 11 AND p90_rescue_minutes = 60
                    THEN 0 ELSE 1 END
        FROM v_rescue_minutes_p90
        WHERE offer_version_id = 'OV-CLOSE-1'),
       'Nearest-rank p90 includes zero-rescue mature jobs and the critical-veto job'
UNION ALL SELECT 'DQ25','error','exception_taxonomy_and_projection',
       CASE WHEN (SELECT sum(exception_count) FROM v_exception_classification)
                    = (SELECT count(*) FROM v_current_exception)
                  AND (SELECT count(*) FROM exception_cause_dim) = 9
                  AND (SELECT count(*) FROM exception_treatment_dim) = 11
            THEN 0 ELSE 1 END,
       'Exact bounded cause/treatment vocabularies neither drop nor multiply current streams'
UNION ALL SELECT 'DQ26','error','cycle_01_contribution_fixture_mismatch',
       (SELECT CASE WHEN cash_contribution_micros = 293000000000
                          AND fully_loaded_contribution_micros = 280000000000
                    THEN 0 ELSE 1 END
        FROM v_fully_loaded_contribution WHERE commercial_cycle_id = 'CYCLE-01'),
       'Paid cycle subtracts nonlabor cash, contractor cash, and founder internal cost once'
UNION ALL SELECT 'DQ27','error','unpaid_cycle_contribution_fixture_mismatch',
       (SELECT CASE WHEN payment_state = 'unpaid'
                          AND net_cash_revenue_micros = 0
                          AND fully_loaded_contribution_micros = -18000000000
                    THEN 0 ELSE 1 END
        FROM v_fully_loaded_contribution WHERE commercial_cycle_id = 'CYCLE-02'),
       'Unpaid delivery has zero revenue and negative fully-loaded contribution'
UNION ALL SELECT 'DQ28','error','cycle_revenue_join_multiplication',
       CASE WHEN (SELECT sum(net_cash_revenue_micros) FROM v_commercial_cycle_facts)
                    = (SELECT sum(net_cash_revenue_micros) FROM v_fully_loaded_contribution)
            THEN 0 ELSE 1 END,
       'Cycle revenue remains one-time after joining job costs'
UNION ALL SELECT 'DQ29','error','founder_minutes_reconciliation',
       CASE WHEN (
         SELECT coalesce(sum(minutes),0) FROM v_human_work_log_asof
         WHERE worker_class = 'founder'
       ) = (
         SELECT coalesce(sum(founder_minutes),0) FROM v_service_job_work_facts
       ) THEN 0 ELSE 1 END,
       'Founder work logs aggregate once to job facts'
UNION ALL SELECT 'DQ30','error','variable_cost_reconciliation',
       CASE WHEN (SELECT coalesce(sum(amount_micros),0) FROM v_variable_cost_event_asof)
                    = (SELECT coalesce(sum(nonlabor_variable_cost_micros),0)
                       FROM v_service_job_variable_cost_facts)
            THEN 0 ELSE 1 END,
       'Nonlabor cost events aggregate once to job facts'
UNION ALL SELECT 'DQ31','error','value_stream_split',
       (SELECT count(*) FROM (
          SELECT service_job_id
          FROM value_confirmation
          GROUP BY service_job_id
          HAVING count(DISTINCT confirmation_key) <> 1
        )),
       'Each job has exactly one deterministic VALUE: stream identity'
UNION ALL SELECT 'DQ32','error','exception_stream_identity_mismatch',
       (SELECT count(*) FROM (
          SELECT exception_key
          FROM exception_event
          GROUP BY exception_key
          HAVING count(DISTINCT service_job_id) <> 1
              OR count(DISTINCT job_step_run_id) <> 1
              OR count(DISTINCT step_code) <> 1
        )),
       'An exception stream cannot move between job, run, or step identities'
UNION ALL SELECT 'DQ33','error','event_sequence_time_reversal',
       (SELECT count(*) FROM (
          SELECT exception_key AS stream_id,event_sequence,occurred_at,
                 lag(occurred_at) OVER (PARTITION BY exception_key ORDER BY event_sequence) AS prior_at
          FROM exception_event
          UNION ALL
          SELECT migration_id,event_sequence,occurred_at,
                 lag(occurred_at) OVER (PARTITION BY migration_id ORDER BY event_sequence)
          FROM customer_migration
          UNION ALL
          SELECT confirmation_key,event_sequence,occurred_at,
                 lag(occurred_at) OVER (PARTITION BY confirmation_key ORDER BY event_sequence)
          FROM value_confirmation
          UNION ALL
          SELECT acceptance_key,event_sequence,occurred_at,
                 lag(occurred_at) OVER (PARTITION BY acceptance_key ORDER BY event_sequence)
          FROM acceptance_event
        ) streams
        WHERE prior_at IS NOT NULL AND datetime(occurred_at) < datetime(prior_at)),
       'Append-only event sequence never reverses occurrence time'
UNION ALL SELECT 'DQ34','error','current_migration_cardinality',
       CASE WHEN (SELECT count(*) FROM v_current_customer_migration)
                    = (SELECT count(DISTINCT migration_id) FROM v_customer_migration_asof)
            THEN 0 ELSE 1 END,
       'Current migration has exactly one row per visible stream'
UNION ALL SELECT 'DQ35','error','weekly_review_cutoff_violation',
       (SELECT count(*) FROM weekly_review
        WHERE datetime(evidence_watermark_at) > datetime(evidence_as_of)
           OR datetime(evidence_as_of) > datetime(occurred_at)
           OR datetime(occurred_at) > datetime(recorded_at)),
       'Weekly reviews cannot use future evidence or reverse audit time'
UNION ALL SELECT 'DQ36','error','current_offer_fixture_mismatch',
       (SELECT CASE WHEN offer_version_id = 'OV-CLOSE-2' THEN 0 ELSE 1 END
        FROM v_current_offer_version WHERE offer_code = 'MONTHLY_CLOSE_PACK'),
       'The current offer is derived from effective-time versions'
UNION ALL SELECT 'DQ37','error','candidate_scope_tuple_missing',
       (SELECT count(*) FROM v_candidate_evidence
        WHERE workflow_id IS NULL OR workflow_version IS NULL OR segment_code IS NULL
           OR data_class_code IS NULL OR step_code IS NULL),
       'Every promotion candidate exposes workflow/version/step/segment/data-class scope'
UNION ALL SELECT 'DQ38','error','decision_axis_binding_failure',
       (SELECT count(*) FROM v_promotion_decision_compatibility
        WHERE decision_code IN ('PROMOTE','LIMITED_CANARY','STOP')
          AND axis_binding_gate = 0),
       'Recorded terminal promotion decisions bind explicit target delivery/delegation/risk axes'
UNION ALL SELECT 'DQ39','error','step_mode_inferred_from_execution_or_delegation',
       (SELECT CASE WHEN execution_kind = 'ai_assist'
                          AND delivery_mode_code = 'founder_internal'
                          AND delegation_level_code = 'L1_DRAFT'
                    THEN 0 ELSE 1 END
        FROM job_step_run WHERE job_step_run_id = 'RUN-01'),
       'Fixture demonstrates three independent step-level classifications'
UNION ALL SELECT 'DQ40','error','global_snapshot_order_invalid',
       (SELECT count(*) FROM model_settings
        WHERE datetime(data_watermark_at) > datetime(event_time_as_of)),
       'The ingestion watermark never exceeds event-time as-of'
UNION ALL SELECT 'DQ41','error','future_reversal_leakage',
       CASE WHEN EXISTS (
                    SELECT 1 FROM promotion_decision
                    WHERE promotion_decision_id = 'PD-RECON-04'
                      AND decision_code = 'REVERSE'
                  )
                  AND NOT EXISTS (
                    SELECT 1 FROM v_promotion_decision_asof
                    WHERE promotion_decision_id = 'PD-RECON-04'
                  )
            THEN 0 ELSE 1 END,
       'A stored future reversal proves vocabulary support without leaking into the snapshot'
UNION ALL SELECT 'DQ42','error','current_decision_fixture_mismatch',
       CASE WHEN EXISTS (
                    SELECT 1 FROM v_current_promotion_decision
                    WHERE automation_candidate_id = 'CAND-RECON'
                      AND promotion_decision_id = 'PD-RECON-03'
                      AND decision_code = 'PROMOTE'
                  )
            THEN 0 ELSE 1 END,
       'Current decision is the latest visible immutable event, not a future reversal'
UNION ALL SELECT 'DQ43','error','decision_ready_is_not_automatic_promotion',
       (SELECT count(*) FROM v_promotion_decision_compatibility
        WHERE promotion_decision_id IN ('PD-RECON-02','PD-RECON-03')
          AND (derived_gate_state <> 'READY_TO_DECIDE'
               OR recorded_decision_compatible <> 1)),
       'Ready evidence supports canary or promotion but remains distinct from the human decision'
UNION ALL SELECT 'DQ44','error','rescue_tail_gate_missing',
       (SELECT count(*) FROM v_promotion_gate_readiness
        WHERE promotion_evidence_snapshot_id IN ('SNAP-RECON-02','SNAP-RECON-03')
          AND (rescue_sample_gate <> 1 OR rescue_p90_gate <> 1
               OR rescue_max_gate <> 1)),
       'Promotion readiness requires sample size, p90, and worst-case rescue gates together'
UNION ALL SELECT 'DQ45','error','effective_version_write_guards_missing',
       CASE WHEN (
         SELECT count(*) FROM sqlite_schema
         WHERE type = 'trigger' AND name IN (
           'validate_offer_version_no_overlap_before_insert',
           'validate_offer_step_before_insert',
           'validate_commercial_cycle_offer_before_insert',
           'validate_service_job_cycle_before_insert',
           'freeze_required_gate_insert_after_snapshot'
         )
       ) = 5 THEN 0 ELSE 1 END,
       'Offer intervals, definition children, operational use, and gate policy have write-time guards'
UNION ALL SELECT 'DQ46','error','exposure_action_policy_mismatch',
       CASE WHEN
         (SELECT count(*) FROM v_promotion_action_compatibility_matrix
          WHERE promotion_evidence_snapshot_id = 'SNAP-AUTOSEND-01'
            AND action_compatible = 1) = 1
         AND EXISTS (
           SELECT 1 FROM v_promotion_action_compatibility_matrix
           WHERE promotion_evidence_snapshot_id = 'SNAP-AUTOSEND-01'
             AND action_code = 'STOP' AND action_compatible = 1
         )
         AND (SELECT count(*) FROM v_promotion_action_compatibility_matrix
              WHERE promotion_evidence_snapshot_id = 'SNAP-RECON-01'
                AND action_compatible = 1) = 3
         AND NOT EXISTS (
           SELECT 1 FROM v_promotion_action_compatibility_matrix
           WHERE promotion_evidence_snapshot_id = 'SNAP-RECON-01'
             AND action_code NOT IN ('HOLD','PAUSE','STOP')
             AND action_compatible = 1
         )
         AND (SELECT count(*) FROM v_promotion_action_compatibility_matrix
              WHERE promotion_evidence_snapshot_id IN ('SNAP-RECON-02','SNAP-RECON-03')
                AND action_compatible = 1) = 14
         AND (SELECT count(*) FROM v_promotion_action_compatibility_matrix
              WHERE promotion_evidence_snapshot_id = 'SNAP-RECON-04'
                AND action_compatible = 1) = 3
         AND NOT EXISTS (
           SELECT 1 FROM v_promotion_action_compatibility_matrix
           WHERE promotion_evidence_snapshot_id = 'SNAP-RECON-04'
             AND action_code NOT IN ('PAUSE','REVERSE','STOP')
             AND action_compatible = 1
         )
       THEN 0 ELSE 1 END,
       'Pre/active exposure and stale safety evidence permit only the chapter-authorized actions'
UNION ALL SELECT 'DQ47','error','candidate_commercial_cycle_cutoff_leakage',
       (SELECT count(*)
        FROM v_candidate_cutoff_job_base b
        JOIN commercial_cycle c USING (commercial_cycle_id)
        WHERE datetime(c.period_start) > datetime(b.evidence_as_of)
           OR datetime(c.recorded_at) > datetime(b.evidence_watermark_at)),
       'Candidate evidence never derives contracted status from a cycle invisible at its cutoff'
UNION ALL SELECT 'DQ48','error','service_job_cycle_time_mismatch',
       (SELECT count(*)
        FROM service_job j
        JOIN commercial_cycle c USING (commercial_cycle_id)
        WHERE datetime(c.recorded_at) > datetime(j.recorded_at)
           OR datetime(j.eligible_at) < datetime(c.period_start)
           OR datetime(j.eligible_at) >= datetime(c.period_end)),
       'Every service job uses a previously visible commercial cycle covering eligibility time'
UNION ALL SELECT 'DQ49','error','migration_source_membership_missing',
       (SELECT count(*)
        FROM customer_migration m
        JOIN automation_candidate c USING (automation_candidate_id)
        WHERE NOT EXISTS (
          SELECT 1 FROM commercial_cycle source_cycle
          WHERE source_cycle.account_id = m.account_id
            AND source_cycle.offer_version_id = m.from_offer_version_id
            AND source_cycle.commercial_motion_code = c.commercial_motion_code
            AND datetime(source_cycle.period_start) <= datetime(m.occurred_at)
            AND datetime(source_cycle.recorded_at) <= datetime(m.recorded_at)
        )),
       'A migration account must have visible membership in the candidate source offer'
UNION ALL SELECT 'DQ50','error','migration_gate_account_dedupe_mismatch',
       (SELECT CASE WHEN consented_migrations = 1 AND verified_migrations = 1
                    THEN 0 ELSE 1 END
        FROM v_candidate_evidence_by_cutoff
        WHERE cutoff_id = 'SNAP-RECON-03'),
       'Migration readiness counts distinct source accounts, not repeat stream identifiers'
UNION ALL SELECT 'DQ51','error','promotion_metric_snapshot_cardinality',
       CASE WHEN (SELECT count(*) FROM promotion_metric_snapshot)
                      = (SELECT count(*) FROM promotion_evidence_snapshot)
                  AND (SELECT count(*) FROM promotion_metric_snapshot) = 6
            THEN 0 ELSE 1 END,
       'Every evidence cutoff has exactly one immutable materialized metric vector'
UNION ALL SELECT 'DQ52','error','promotion_metric_snapshot_capture_mismatch',
       (SELECT count(*)
        FROM promotion_metric_snapshot m
        JOIN v_candidate_evidence_by_cutoff e
          ON e.promotion_evidence_snapshot_id = m.promotion_evidence_snapshot_id
         AND e.automation_candidate_id = m.automation_candidate_id
        WHERE e.workflow_id <> m.workflow_id
           OR e.workflow_version <> m.workflow_version
           OR e.segment_code <> m.segment_code
           OR e.data_class_code <> m.data_class_code
           OR e.step_code <> m.step_code
           OR e.visible_source_jobs <> m.visible_source_jobs
           OR e.mature_source_jobs <> m.mature_source_jobs
           OR e.standard_scope_rate_ppm IS NOT m.standard_scope_rate_ppm
           OR e.accepted_outcomes <> m.accepted_outcomes
           OR e.value_confirmed_outcomes <> m.value_confirmed_outcomes
           OR e.contracted_accepted_outcomes <> m.contracted_accepted_outcomes
           OR e.cash_collected_value_confirmed_outcomes
              <> m.cash_collected_value_confirmed_outcomes
           OR e.exception_evidence_jobs <> m.exception_evidence_jobs
           OR e.p90_rescue_minutes IS NOT m.p90_rescue_minutes
           OR e.max_rescue_minutes IS NOT m.max_rescue_minutes
           OR e.open_security_exceptions <> m.open_security_exceptions
           OR e.consented_migrations <> m.consented_migrations),
       'At capture time, stored promotion metrics exactly match the bounded source projection'
UNION ALL SELECT 'DQ53','error','readiness_not_using_frozen_metrics',
       CASE WHEN EXISTS (
         SELECT 1 FROM sqlite_schema
         WHERE type = 'view' AND name = 'v_promotion_gate_readiness'
           AND instr(sql,'JOIN promotion_metric_snapshot') > 0
       ) THEN 0 ELSE 1 END,
       'Readiness and action compatibility consume the immutable metric child, not live raw facts'
UNION ALL SELECT 'DQ54','error','decision_vocabulary_mutable',
       CASE WHEN (
         SELECT count(*) FROM sqlite_schema
         WHERE type = 'trigger' AND name IN (
           'immutable_promotion_action_update','immutable_promotion_action_delete',
           'immutable_promotion_metric_snapshot_update',
           'immutable_promotion_metric_snapshot_delete'
         )
       ) = 4 THEN 0 ELSE 1 END,
       'Decision vocabulary and frozen metric vectors reject retroactive mutation'
UNION ALL SELECT 'DQ55','error','weekly_review_stream_guard_missing',
       CASE WHEN EXISTS (
         SELECT 1 FROM sqlite_schema
         WHERE type = 'trigger' AND name = 'validate_weekly_review_stream_before_insert'
       ) THEN 0 ELSE 1 END,
       'Weekly review corrections preserve candidate scope, identity, sequence, and monotone time'
UNION ALL SELECT 'DQ56','error','account_offer_segment_mismatch',
       (SELECT count(*)
        FROM commercial_cycle c
        JOIN customer_account a USING (account_id)
        JOIN offer_version o USING (offer_version_id)
        WHERE a.segment_code <> o.segment_code
           OR datetime(a.recorded_at) > datetime(c.recorded_at)),
       'Commercial membership binds an immutable account segment to the offer segment'
UNION ALL SELECT 'DQ57','error','migration_metadata_future_leakage',
       (SELECT count(*)
        FROM customer_migration m
        JOIN automation_candidate c USING (automation_candidate_id)
        JOIN offer_version from_o ON from_o.offer_version_id = m.from_offer_version_id
        JOIN offer_version to_o ON to_o.offer_version_id = m.to_offer_version_id
        JOIN offer_standard_step to_s
          ON to_s.offer_version_id = to_o.offer_version_id
         AND to_s.step_code = c.step_code
        WHERE datetime(c.recorded_at) > datetime(m.recorded_at)
           OR datetime(from_o.recorded_at) > datetime(m.recorded_at)
           OR datetime(to_o.recorded_at) > datetime(m.recorded_at)
           OR datetime(to_s.recorded_at) > datetime(m.recorded_at)),
       'Migration evidence never back-references candidate or destination metadata not yet visible'
UNION ALL SELECT 'DQ58','error','null_assertion_normalization_failure',
       (WITH probe(failure_count) AS (VALUES(NULL),(0),(2))
        SELECT CASE WHEN sum(
          CASE WHEN failure_count IS NULL OR failure_count <> 0 THEN 1 ELSE 0 END
        ) = 2 THEN 0 ELSE 1 END FROM probe),
       'NULL and nonzero assertion results both make the final guard nonzero'
UNION ALL SELECT 'DQ59','error','promotion_snapshot_construction_order_unguarded',
       CASE WHEN EXISTS (
         SELECT 1 FROM sqlite_schema
         WHERE type = 'trigger'
           AND name = 'validate_promotion_gate_evidence_before_insert'
           AND instr(sql,'promotion_metric_snapshot') > 0
       ) THEN 0 ELSE 1 END,
       'Gate-domain children cannot strand a header by preceding its frozen metric vector'
UNION ALL SELECT 'DQ60','error','decision_precedes_metric_materialization',
       (SELECT count(*)
        FROM promotion_decision d
        JOIN promotion_metric_snapshot m USING (promotion_evidence_snapshot_id)
        WHERE datetime(m.materialized_at) > datetime(d.occurred_at)),
       'A recorded action must occur after its immutable metric vector was materialized'
UNION ALL SELECT 'DQ61','error','decision_sequence_chronology_reversal',
       (SELECT count(*)
        FROM promotion_decision d
        JOIN promotion_decision p
          ON p.automation_candidate_id = d.automation_candidate_id
         AND p.decision_sequence = d.decision_sequence - 1
        WHERE datetime(p.occurred_at) > datetime(d.occurred_at)
           OR datetime(p.recorded_at) > datetime(d.occurred_at)),
       'A later decision sequence cannot occur before the prior action was known'
UNION ALL SELECT 'DQ62','error','snapshot_sequence_chronology_reversal',
       (SELECT count(*)
        FROM promotion_evidence_snapshot s
        JOIN promotion_evidence_snapshot p
          ON p.automation_candidate_id = s.automation_candidate_id
         AND p.snapshot_sequence = s.snapshot_sequence - 1
        WHERE datetime(p.occurred_at) > datetime(s.occurred_at)
           OR datetime(p.recorded_at) > datetime(s.occurred_at)),
       'A later evidence snapshot cannot occur before the prior snapshot was known';

CREATE TEMP VIEW v_assertion_report AS
SELECT check_id,severity,check_name,failure_count,
       CASE WHEN failure_count = 0 THEN 'PASS' ELSE 'FAIL' END AS status,
       expectation
FROM v_assertion_results;

-- A failed error assertion violates this CHECK and makes sqlite3 return nonzero.
CREATE TABLE assertion_guard (
  guard_id INTEGER PRIMARY KEY CHECK (guard_id = 1),
  error_failure_count INTEGER NOT NULL CHECK (error_failure_count = 0)
) STRICT;

INSERT INTO assertion_guard(guard_id,error_failure_count)
SELECT 1,coalesce(sum(
  CASE WHEN failure_count IS NULL OR failure_count <> 0 THEN 1 ELSE 0 END
),0)
FROM v_assertion_results
WHERE severity = 'error';

COMMIT;

-- -----------------------------------------------------------------------------
-- Bounded query pack
-- -----------------------------------------------------------------------------

PRAGMA foreign_key_check;

SELECT check_id,severity,check_name,failure_count,status
FROM v_assertion_report
ORDER BY check_id;

SELECT count(*) AS assertion_failure_rows
FROM v_assertion_report
WHERE status = 'FAIL';

SELECT offer_version_id,workflow_id,workflow_version,segment_code,data_class_code,
       commercial_motion_code,delivery_mode_code,delegation_level_code,risk_tier_code,
       mature_eligible_jobs,standard_scope_jobs,standard_scope_rate_pct
FROM v_standard_scope_rate
ORDER BY offer_version_id,delivery_mode_code;

SELECT offer_version_id,mature_eligible_jobs,accepted_outcomes,
       contracted_accepted_outcomes,value_confirmed_outcomes,
       cash_collected_value_confirmed_outcomes,value_confirmed_without_cash_outcomes,
       founder_minutes_all_mature_jobs,
       founder_minutes_per_contracted_accepted_outcome
FROM v_founder_minutes_per_accepted_outcome
ORDER BY offer_version_id;

SELECT offer_version_id,mature_eligible_jobs,p90_rescue_minutes
FROM v_rescue_minutes_p90
ORDER BY offer_version_id;

SELECT offer_version_id,workflow_id,workflow_version,segment_code,data_class_code,
       commercial_motion_code,delivery_mode_code,delegation_level_code,risk_tier_code,
       cause_class,treatment_code,severity,exception_state,
       exception_count,affected_jobs,nonwaivable_count
FROM v_exception_classification
ORDER BY offer_version_id,cause_class,treatment_code,severity,exception_state;

SELECT commercial_cycle_id,commercial_motion_code,payment_state,
       net_cash_revenue_micros,nonlabor_variable_cost_micros,
       contractor_cash_cost_micros,founder_internal_cost_micros,
       cash_contribution_micros,fully_loaded_contribution_micros
FROM v_fully_loaded_contribution
ORDER BY commercial_cycle_id;

SELECT automation_candidate_id,workflow_id,workflow_version,step_code,segment_code,
       data_class_code,mature_source_jobs,standard_scope_rate_pct,
       contracted_accepted_outcomes,value_confirmed_outcomes,
       cash_collected_value_confirmed_outcomes,value_confirmed_without_cash_outcomes,
       exception_evidence_jobs,
       rescue_sample_jobs,p90_rescue_minutes,max_rescue_minutes,
       open_high_exceptions,open_critical_exceptions,consented_migrations
FROM v_candidate_evidence
ORDER BY automation_candidate_id;

SELECT promotion_decision_id,automation_candidate_id,decision_code,
       promotion_evidence_snapshot_id,exposure_state,
       mature_sample_gate,contracted_acceptance_gate,counterevidence_gate,
       standard_scope_gate,rescue_sample_gate,rescue_p90_gate,rescue_max_gate,
       migration_gate,rollback_gate,owner_approval_gate,
       open_high_exceptions,open_critical_exceptions,
       derived_gate_state,recorded_decision_compatible
FROM v_promotion_decision_compatibility
ORDER BY automation_candidate_id,decision_sequence;
