-- AI distribution evidence-to-settled-revenue model
-- Schema version: 1.0.0 | Reference date: 2026-08-02 | Engine: SQLite 3.40+
-- Companion: ../17-ai-distribution-to-settled-revenue.md
-- Template: ../templates/17-ai-distribution-evidence-pack.md
--
-- Run only on a new/disposable database:
--   sqlite3 :memory: < indie-developer-playbook/research/17-ai-distribution-evidence-model.sql
--
-- Every identifier, URL, policy fact, region decision, event, amount, date,
-- outcome, and cost below is synthetic. Fixtures are data-shape examples, not
-- product availability claims, benchmarks, accounting advice, or live receipts.
--
-- Privacy and authority boundary
-- - This public companion stores no raw prompt, query text, person name, email,
--   address, IP address, account credential, OAuth token, payment credential,
--   order detail, bank account, or customer secret.
-- - *_ref_hash values are synthetic opaque references. They do not prove
--   anonymity, consent, provenance, authenticity, or a lawful processing basis.
-- - Provider control-plane facts, verified provider events, first-party state,
--   customer acceptance, and bank receipts remain different evidence classes.
--
-- Time, amount, and interpretation boundary
-- - occurred_at is event time; recorded_at is append-ledger ingest time.
-- - outcome_cutoff_at limits eligible occurrence; snapshot_as_of limits evidence
--   known at a decision. A late ingest or correction never rewrites a snapshot.
-- - *_micros is one millionth of the row currency (JPY 1 = 1,000,000 micros).
-- - Stage order is a presentation order, never a forced monotone state machine.
-- - N/A is route-contract inapplicability; UNKNOWN is missing/unsafe evidence;
--   ZERO is a measured zero with a known denominator; none are interchangeable.
--
-- Intentional v1 boundaries
-- - Four motions are modeled: SEARCH_CITATION, OPENAI_PLUGIN, MCP_REGISTRY, and
--   AGENTIC_COMMERCE. A production system may split providers more finely.
-- - No impression population is inferred from probes, referrals, or outcomes.
-- - Tax/accounting recognition, identity resolution, fraud models, inventory,
--   payment credentials, and raw bank data live in restricted source systems.
-- - The economics view is a cash decision bridge, not a general ledger.
-- - Stage evidence demonstrates append-only correction/supersession. Commerce,
--   payout, bank, cost, time, and retention rows are a bounded decision-snapshot
--   companion, not a complete operational event store; production systems must
--   retain their provider/resource revisions and immutable source ledgers.

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='ai_distribution_evidence'),
  schema_version TEXT NOT NULL CHECK (schema_version='1.0.0'),
  engine_floor TEXT NOT NULL CHECK (engine_floor='SQLite 3.40+'),
  applied_at TEXT NOT NULL CHECK (datetime(applied_at) IS NOT NULL),
  data_policy_code TEXT NOT NULL CHECK (
    data_policy_code='SYNTHETIC_ONLY_NO_RAW_PII_OPAQUE_REFERENCES'
  )
) STRICT;

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

CREATE TABLE evidence_class_dim (
  evidence_class TEXT PRIMARY KEY CHECK (evidence_class IN (
    'E0_INFERENCE','E1_MANUAL_OBSERVATION','E2_PROVIDER_CONTROL_PLANE',
    'E3_VERIFIED_PROVIDER_EVENT','E4_FIRST_PARTY_AUTHORITATIVE',
    'E5_CUSTOMER_OR_BANK_AUTHORITATIVE'
  )),
  strength_rank INTEGER NOT NULL UNIQUE CHECK (strength_rank BETWEEN 0 AND 5)
) STRICT;

CREATE TABLE canonical_stage_dim (
  canonical_stage TEXT PRIMARY KEY,
  display_order INTEGER NOT NULL UNIQUE CHECK (display_order BETWEEN 10 AND 180),
  stage_domain TEXT NOT NULL CHECK (stage_domain IN (
    'ELIGIBILITY','ARTIFACT','DISTRIBUTION','DISCOVERY','RUNTIME',
    'OUTCOME','COMMERCE','CASH','RETENTION'
  ))
) STRICT;

CREATE TABLE motion_contract (
  motion_contract_id TEXT PRIMARY KEY,
  contract_version INTEGER NOT NULL CHECK (contract_version>0),
  motion_type TEXT NOT NULL CHECK (motion_type IN (
    'SEARCH_CITATION','OPENAI_PLUGIN','MCP_REGISTRY','AGENTIC_COMMERCE'
  )),
  provider_code TEXT NOT NULL,
  publisher_entity_ref TEXT NOT NULL,
  region_code TEXT NOT NULL,
  product_type TEXT NOT NULL,
  cohort_code TEXT NOT NULL,
  primary_outcome_code TEXT NOT NULL,
  exact_denominator_code TEXT NOT NULL,
  commercial_owner_rule_version TEXT NOT NULL,
  known_unobservable_stages TEXT NOT NULL,
  cohort_start_at TEXT NOT NULL CHECK (datetime(cohort_start_at) IS NOT NULL),
  outcome_cutoff_at TEXT NOT NULL CHECK (datetime(outcome_cutoff_at) IS NOT NULL),
  snapshot_as_of TEXT NOT NULL CHECK (datetime(snapshot_as_of) IS NOT NULL),
  maturity_at TEXT NOT NULL CHECK (datetime(maturity_at) IS NOT NULL),
  max_founder_minutes INTEGER NOT NULL CHECK (max_founder_minutes>0),
  max_cash_micros INTEGER NOT NULL CHECK (max_cash_micros>=0),
  max_live_journeys INTEGER NOT NULL CHECK (max_live_journeys>=0),
  currency TEXT NOT NULL CHECK (length(currency)=3 AND currency=upper(currency)),
  approved_by_role TEXT NOT NULL,
  approved_at TEXT NOT NULL CHECK (datetime(approved_at) IS NOT NULL),
  contract_ref_hash TEXT NOT NULL CHECK (
    length(contract_ref_hash)=64 AND contract_ref_hash=lower(contract_ref_hash)
    AND contract_ref_hash NOT GLOB '*[^0-9a-f]*'
  ),
  CHECK (datetime(cohort_start_at)<=datetime(outcome_cutoff_at)),
  CHECK (datetime(outcome_cutoff_at)<=datetime(maturity_at)),
  CHECK (datetime(outcome_cutoff_at)<=datetime(snapshot_as_of)),
  UNIQUE (motion_type,provider_code,region_code,product_type,cohort_code)
) STRICT;

CREATE TABLE policy_snapshot (
  policy_snapshot_id TEXT PRIMARY KEY,
  provider_code TEXT NOT NULL,
  program_code TEXT NOT NULL,
  document_title TEXT NOT NULL,
  source_url TEXT NOT NULL CHECK (source_url LIKE 'https://%'),
  source_publisher TEXT NOT NULL,
  source_captured_at TEXT NOT NULL CHECK (datetime(source_captured_at) IS NOT NULL),
  source_content_hash TEXT NOT NULL CHECK (
    length(source_content_hash)=64 AND source_content_hash=lower(source_content_hash)
    AND source_content_hash NOT GLOB '*[^0-9a-f]*'
  ),
  snapshot_status TEXT NOT NULL CHECK (snapshot_status IN (
    'CURRENT','PREVIEW','UPCOMING','DEPRECATED','CONFLICTING'
  )),
  effective_from TEXT NOT NULL CHECK (datetime(effective_from) IS NOT NULL),
  effective_until TEXT CHECK (
    effective_until IS NULL OR datetime(effective_until) IS NOT NULL
  ),
  protocol_version TEXT NOT NULL,
  applies_region TEXT NOT NULL,
  applies_product TEXT NOT NULL,
  eligibility_effect TEXT NOT NULL CHECK (eligibility_effect IN (
    'ALLOWS','DENIES','UNKNOWN'
  )),
  conflict_group TEXT,
  exact_fact_ref TEXT NOT NULL,
  unsupported_claim_ref TEXT NOT NULL,
  recheck_due_at TEXT NOT NULL CHECK (datetime(recheck_due_at) IS NOT NULL),
  reviewed_by_role TEXT NOT NULL,
  reviewed_at TEXT NOT NULL CHECK (datetime(reviewed_at) IS NOT NULL),
  CHECK ((snapshot_status='CONFLICTING' AND conflict_group IS NOT NULL)
      OR (snapshot_status<>'CONFLICTING' AND conflict_group IS NULL)),
  CHECK (effective_until IS NULL
      OR datetime(effective_from)<datetime(effective_until)),
  CHECK (snapshot_status<>'UPCOMING'
      OR datetime(effective_from)>datetime(source_captured_at))
) STRICT;

CREATE TABLE motion_policy_link (
  motion_contract_id TEXT NOT NULL,
  policy_snapshot_id TEXT NOT NULL,
  link_role TEXT NOT NULL CHECK (link_role IN (
    'EXECUTION_SCOPE','PROTOCOL_CONTRACT','PREVIEW_OPTION','CONFLICT_NOTE'
  )),
  PRIMARY KEY (motion_contract_id,policy_snapshot_id,link_role),
  FOREIGN KEY (motion_contract_id) REFERENCES motion_contract(motion_contract_id),
  FOREIGN KEY (policy_snapshot_id) REFERENCES policy_snapshot(policy_snapshot_id)
) STRICT;

CREATE TABLE eligibility_review (
  eligibility_review_id TEXT PRIMARY KEY,
  motion_contract_id TEXT NOT NULL UNIQUE,
  review_version INTEGER NOT NULL CHECK (review_version>0),
  publisher_status TEXT NOT NULL CHECK (publisher_status IN ('PASS','FAIL','UNKNOWN','NA')),
  region_status TEXT NOT NULL CHECK (region_status IN ('PASS','FAIL','UNKNOWN','NA')),
  product_status TEXT NOT NULL CHECK (product_status IN ('PASS','FAIL','UNKNOWN','NA')),
  plan_role_status TEXT NOT NULL CHECK (plan_role_status IN ('PASS','FAIL','UNKNOWN','NA')),
  endpoint_status TEXT NOT NULL CHECK (endpoint_status IN ('PASS','FAIL','UNKNOWN','NA')),
  auth_status TEXT NOT NULL CHECK (auth_status IN ('PASS','FAIL','UNKNOWN','NA')),
  privacy_support_status TEXT NOT NULL CHECK (privacy_support_status IN ('PASS','FAIL','UNKNOWN','NA')),
  payment_fulfillment_status TEXT NOT NULL CHECK (payment_fulfillment_status IN ('PASS','FAIL','UNKNOWN','NA')),
  current_gate TEXT NOT NULL CHECK (current_gate IN (
    'STOP','NOT_ELIGIBLE','PAUSE','UNKNOWN','READY_TEST','READY_LIVE'
  )),
  gate_reason_ref TEXT NOT NULL,
  permitted_action TEXT NOT NULL,
  prohibited_action TEXT NOT NULL,
  decided_by_role TEXT NOT NULL,
  decided_at TEXT NOT NULL CHECK (datetime(decided_at) IS NOT NULL),
  recheck_due_at TEXT NOT NULL CHECK (datetime(recheck_due_at) IS NOT NULL),
  FOREIGN KEY (motion_contract_id) REFERENCES motion_contract(motion_contract_id)
) STRICT;

CREATE TRIGGER validate_eligibility_review_before_insert
BEFORE INSERT ON eligibility_review
BEGIN
  SELECT RAISE(ABORT,'READY_LIVE requires every eligibility dimension PASS')
  WHERE NEW.current_gate='READY_LIVE' AND (
    NEW.publisher_status<>'PASS' OR NEW.region_status<>'PASS'
    OR NEW.product_status<>'PASS' OR NEW.plan_role_status<>'PASS'
    OR NEW.endpoint_status<>'PASS' OR NEW.auth_status<>'PASS'
    OR NEW.privacy_support_status<>'PASS'
    OR NEW.payment_fulfillment_status<>'PASS'
  );
  SELECT RAISE(ABORT,'READY_LIVE requires a linked CURRENT allowing exact scope')
  WHERE NEW.current_gate='READY_LIVE' AND NOT EXISTS (
    SELECT 1
    FROM motion_contract m
    JOIN motion_policy_link l ON l.motion_contract_id=m.motion_contract_id
    JOIN policy_snapshot p ON p.policy_snapshot_id=l.policy_snapshot_id
    WHERE m.motion_contract_id=NEW.motion_contract_id
      AND l.link_role='EXECUTION_SCOPE'
      AND p.snapshot_status='CURRENT'
      AND p.eligibility_effect='ALLOWS'
      AND p.applies_region IN (m.region_code,'ALL')
      AND p.applies_product IN (m.product_type,'ALL')
      AND datetime(p.effective_from)<=datetime(NEW.decided_at)
      AND (p.effective_until IS NULL
        OR datetime(NEW.decided_at)<datetime(p.effective_until))
  );
  SELECT RAISE(ABORT,'NOT_ELIGIBLE requires at least one explicit FAIL')
  WHERE NEW.current_gate='NOT_ELIGIBLE'
    AND NEW.publisher_status<>'FAIL' AND NEW.region_status<>'FAIL'
    AND NEW.product_status<>'FAIL' AND NEW.plan_role_status<>'FAIL'
    AND NEW.endpoint_status<>'FAIL' AND NEW.auth_status<>'FAIL'
    AND NEW.privacy_support_status<>'FAIL'
    AND NEW.payment_fulfillment_status<>'FAIL';
END;

CREATE TABLE route_stage_contract (
  motion_contract_id TEXT NOT NULL,
  path_version INTEGER NOT NULL CHECK (path_version>0),
  canonical_stage TEXT NOT NULL,
  applicability TEXT NOT NULL CHECK (applicability IN ('REQUIRED','OPTIONAL','NA')),
  minimum_evidence_class TEXT,
  denominator_code TEXT,
  source_of_truth_code TEXT,
  approved_by_role TEXT NOT NULL,
  approved_at TEXT NOT NULL CHECK (datetime(approved_at) IS NOT NULL),
  PRIMARY KEY (motion_contract_id,canonical_stage),
  FOREIGN KEY (motion_contract_id) REFERENCES motion_contract(motion_contract_id),
  FOREIGN KEY (canonical_stage) REFERENCES canonical_stage_dim(canonical_stage),
  FOREIGN KEY (minimum_evidence_class) REFERENCES evidence_class_dim(evidence_class),
  CHECK ((applicability='NA' AND minimum_evidence_class IS NULL
          AND denominator_code IS NULL AND source_of_truth_code IS NULL)
      OR (applicability<>'NA' AND minimum_evidence_class IS NOT NULL
          AND denominator_code IS NOT NULL AND source_of_truth_code IS NOT NULL))
) STRICT;

CREATE TABLE artifact_revision (
  artifact_revision_id TEXT PRIMARY KEY,
  artifact_id TEXT NOT NULL,
  motion_contract_id TEXT NOT NULL,
  revision_sequence INTEGER NOT NULL CHECK (revision_sequence>0),
  supersedes_artifact_revision_id TEXT,
  release_ref TEXT NOT NULL,
  artifact_kind TEXT NOT NULL CHECK (artifact_kind IN (
    'WEB_CONTENT','PLUGIN_PACKAGE','MCP_SERVER_METADATA','COMMERCE_BUNDLE'
  )),
  content_ref_hash TEXT NOT NULL CHECK (
    length(content_ref_hash)=64 AND content_ref_hash=lower(content_ref_hash)
    AND content_ref_hash NOT GLOB '*[^0-9a-f]*'
  ),
  schema_version_ref TEXT NOT NULL,
  validation_status TEXT NOT NULL CHECK (validation_status IN ('PASS','FAIL','UNKNOWN')),
  security_review_status TEXT NOT NULL CHECK (security_review_status IN ('PASS','FAIL','UNKNOWN')),
  compatibility_status TEXT NOT NULL CHECK (compatibility_status IN ('PASS','FAIL','UNKNOWN')),
  rollback_ref TEXT NOT NULL,
  approved_by_role TEXT NOT NULL,
  approved_at TEXT NOT NULL CHECK (datetime(approved_at) IS NOT NULL),
  recorded_at TEXT NOT NULL CHECK (datetime(recorded_at) IS NOT NULL),
  UNIQUE (artifact_id,revision_sequence),
  FOREIGN KEY (motion_contract_id) REFERENCES motion_contract(motion_contract_id),
  FOREIGN KEY (supersedes_artifact_revision_id)
    REFERENCES artifact_revision(artifact_revision_id),
  CHECK (datetime(approved_at)<=datetime(recorded_at))
) STRICT;

CREATE TRIGGER validate_artifact_revision_before_insert
BEFORE INSERT ON artifact_revision
BEGIN
  SELECT RAISE(ABORT,'first artifact revision cannot supersede another revision')
  WHERE NEW.revision_sequence=1 AND NEW.supersedes_artifact_revision_id IS NOT NULL;
  SELECT RAISE(ABORT,'later artifact revision requires exact prior revision')
  WHERE NEW.revision_sequence>1 AND NOT EXISTS (
    SELECT 1 FROM artifact_revision p
    WHERE p.artifact_revision_id=NEW.supersedes_artifact_revision_id
      AND p.artifact_id=NEW.artifact_id
      AND p.motion_contract_id=NEW.motion_contract_id
      AND p.revision_sequence=NEW.revision_sequence-1
      AND NOT EXISTS (
        SELECT 1 FROM artifact_revision n
        WHERE n.supersedes_artifact_revision_id=p.artifact_revision_id
      )
  );
END;

CREATE TABLE journey (
  journey_id TEXT PRIMARY KEY,
  motion_contract_id TEXT NOT NULL,
  artifact_revision_id TEXT NOT NULL,
  eligibility_review_id TEXT NOT NULL,
  environment TEXT NOT NULL CHECK (environment IN ('TEST','SANDBOX','LIVE')),
  cohort_member_ref TEXT NOT NULL,
  commercial_owner_key TEXT NOT NULL UNIQUE,
  provider_declared_origin TEXT NOT NULL,
  first_known_touch TEXT NOT NULL,
  incremental_claim_permitted INTEGER NOT NULL CHECK (incremental_claim_permitted IN (0,1)),
  started_at TEXT NOT NULL CHECK (datetime(started_at) IS NOT NULL),
  recorded_at TEXT NOT NULL CHECK (datetime(recorded_at) IS NOT NULL),
  CHECK (datetime(started_at)<=datetime(recorded_at)),
  FOREIGN KEY (motion_contract_id) REFERENCES motion_contract(motion_contract_id),
  FOREIGN KEY (artifact_revision_id) REFERENCES artifact_revision(artifact_revision_id),
  FOREIGN KEY (eligibility_review_id) REFERENCES eligibility_review(eligibility_review_id)
) STRICT;

CREATE TRIGGER validate_journey_before_insert
BEFORE INSERT ON journey
BEGIN
  SELECT RAISE(ABORT,'journey artifact must belong to its motion')
  WHERE NOT EXISTS (
    SELECT 1 FROM artifact_revision a
    WHERE a.artifact_revision_id=NEW.artifact_revision_id
      AND a.motion_contract_id=NEW.motion_contract_id
  );
  SELECT RAISE(ABORT,'journey eligibility review must belong to its motion')
  WHERE NOT EXISTS (
    SELECT 1 FROM eligibility_review e
    WHERE e.eligibility_review_id=NEW.eligibility_review_id
      AND e.motion_contract_id=NEW.motion_contract_id
  );
  SELECT RAISE(ABORT,'LIVE journey requires READY_LIVE eligibility')
  WHERE NEW.environment='LIVE' AND NOT EXISTS (
    SELECT 1 FROM eligibility_review e
    WHERE e.eligibility_review_id=NEW.eligibility_review_id
      AND e.current_gate='READY_LIVE'
  );
END;

CREATE TABLE journey_correlation_boundary (
  correlation_boundary_id TEXT PRIMARY KEY,
  journey_id TEXT NOT NULL,
  boundary_code TEXT NOT NULL CHECK (boundary_code IN (
    'PROVIDER_TO_LANDING','LANDING_TO_ACCOUNT','ACCOUNT_TO_CHECKOUT',
    'CHECKOUT_TO_PAYMENT','PAYMENT_TO_ORDER','PAYOUT_TO_BANK'
  )),
  upstream_ref TEXT NOT NULL,
  downstream_ref TEXT,
  link_state TEXT NOT NULL CHECK (link_state IN ('VERIFIED','UNKNOWN_LINK','NA')),
  evidence_ref TEXT NOT NULL,
  permitted_use TEXT NOT NULL,
  link_basis TEXT NOT NULL CHECK (link_basis IN (
    'SIGNED_PARAMETER','CONSENTED_FIRST_PARTY','AUTHORITATIVE_OBJECT',
    'BANK_RECONCILIATION','NONE','NOT_APPLICABLE'
  )),
  reviewed_at TEXT NOT NULL CHECK (datetime(reviewed_at) IS NOT NULL),
  UNIQUE (journey_id,boundary_code),
  FOREIGN KEY (journey_id) REFERENCES journey(journey_id),
  CHECK ((link_state='VERIFIED' AND downstream_ref IS NOT NULL
          AND link_basis NOT IN ('NONE','NOT_APPLICABLE'))
      OR (link_state='UNKNOWN_LINK' AND downstream_ref IS NULL
          AND link_basis='NONE')
      OR (link_state='NA' AND downstream_ref IS NULL
          AND link_basis='NOT_APPLICABLE'))
) STRICT;

CREATE TABLE stage_evidence_event (
  stage_event_id TEXT PRIMARY KEY,
  evidence_stream_id TEXT NOT NULL,
  event_revision INTEGER NOT NULL CHECK (event_revision>0),
  correction_kind TEXT NOT NULL CHECK (correction_kind IN ('ASSERT','CORRECT','RETRACT')),
  supersedes_event_id TEXT,
  journey_id TEXT NOT NULL,
  motion_contract_id TEXT NOT NULL,
  artifact_revision_id TEXT NOT NULL,
  policy_snapshot_id TEXT NOT NULL,
  canonical_stage TEXT NOT NULL,
  observation_state TEXT NOT NULL CHECK (observation_state IN (
    'OBSERVED','FAILED','UNKNOWN','ZERO','RETRACTED'
  )),
  evidence_class TEXT NOT NULL,
  evidence_source TEXT NOT NULL CHECK (evidence_source IN (
    'INFERENCE','MANUAL','PROVIDER_CONTROL','PROVIDER_EVENT',
    'FIRST_PARTY','CUSTOMER','BANK'
  )),
  source_system TEXT NOT NULL,
  source_event_id TEXT NOT NULL,
  source_revision TEXT NOT NULL,
  causal_sequence INTEGER,
  ingest_sequence INTEGER NOT NULL CHECK (ingest_sequence>0),
  occurred_at TEXT NOT NULL CHECK (datetime(occurred_at) IS NOT NULL),
  provider_created_at TEXT CHECK (
    provider_created_at IS NULL OR datetime(provider_created_at) IS NOT NULL
  ),
  recorded_at TEXT NOT NULL CHECK (datetime(recorded_at) IS NOT NULL),
  environment TEXT NOT NULL CHECK (environment IN ('TEST','SANDBOX','LIVE')),
  denominator_count INTEGER CHECK (denominator_count IS NULL OR denominator_count>=0),
  result_count INTEGER CHECK (result_count IS NULL OR result_count>=0),
  signature_status TEXT NOT NULL CHECK (signature_status IN (
    'VERIFIED','FAILED','NOT_APPLICABLE','UNKNOWN'
  )),
  payload_ref_hash TEXT NOT NULL CHECK (
    length(payload_ref_hash)=64 AND payload_ref_hash=lower(payload_ref_hash)
    AND payload_ref_hash NOT GLOB '*[^0-9a-f]*'
  ),
  data_class TEXT NOT NULL CHECK (data_class='NO_RAW_PII_OPAQUE_REF'),
  reviewed_by_role TEXT NOT NULL,
  reviewed_at TEXT NOT NULL CHECK (datetime(reviewed_at) IS NOT NULL),
  UNIQUE (evidence_stream_id,event_revision),
  UNIQUE (environment,source_system,source_event_id),
  FOREIGN KEY (supersedes_event_id) REFERENCES stage_evidence_event(stage_event_id),
  FOREIGN KEY (journey_id) REFERENCES journey(journey_id),
  FOREIGN KEY (motion_contract_id) REFERENCES motion_contract(motion_contract_id),
  FOREIGN KEY (artifact_revision_id) REFERENCES artifact_revision(artifact_revision_id),
  FOREIGN KEY (policy_snapshot_id) REFERENCES policy_snapshot(policy_snapshot_id),
  FOREIGN KEY (canonical_stage) REFERENCES canonical_stage_dim(canonical_stage),
  FOREIGN KEY (evidence_class) REFERENCES evidence_class_dim(evidence_class),
  CHECK (datetime(occurred_at)<=datetime(recorded_at)),
  CHECK ((observation_state='ZERO' AND denominator_count>0 AND result_count=0)
      OR (observation_state<>'ZERO')),
  CHECK ((correction_kind='RETRACT' AND observation_state='RETRACTED')
      OR (correction_kind<>'RETRACT' AND observation_state<>'RETRACTED'))
) STRICT;

CREATE TRIGGER validate_stage_evidence_before_insert
BEFORE INSERT ON stage_evidence_event
BEGIN
  SELECT RAISE(ABORT,'stage evidence journey/motion/artifact/environment mismatch')
  WHERE NOT EXISTS (
    SELECT 1 FROM journey j
    WHERE j.journey_id=NEW.journey_id
      AND j.motion_contract_id=NEW.motion_contract_id
      AND j.artifact_revision_id=NEW.artifact_revision_id
      AND j.environment=NEW.environment
  );
  SELECT RAISE(ABORT,'stage evidence policy is not linked to motion')
  WHERE NOT EXISTS (
    SELECT 1 FROM motion_policy_link l
    WHERE l.motion_contract_id=NEW.motion_contract_id
      AND l.policy_snapshot_id=NEW.policy_snapshot_id
  );
  SELECT RAISE(ABORT,'N/A route stage cannot receive an evidence event')
  WHERE EXISTS (
    SELECT 1 FROM route_stage_contract r
    WHERE r.motion_contract_id=NEW.motion_contract_id
      AND r.canonical_stage=NEW.canonical_stage
      AND r.applicability='NA'
  );
  SELECT RAISE(ABORT,'route stage contract is missing')
  WHERE NOT EXISTS (
    SELECT 1 FROM route_stage_contract r
    WHERE r.motion_contract_id=NEW.motion_contract_id
      AND r.canonical_stage=NEW.canonical_stage
  );
  SELECT RAISE(ABORT,'positive stage evidence is below required evidence class')
  WHERE NEW.observation_state IN ('OBSERVED','ZERO') AND EXISTS (
    SELECT 1
    FROM route_stage_contract r
    JOIN evidence_class_dim need ON need.evidence_class=r.minimum_evidence_class
    JOIN evidence_class_dim got ON got.evidence_class=NEW.evidence_class
    WHERE r.motion_contract_id=NEW.motion_contract_id
      AND r.canonical_stage=NEW.canonical_stage
      AND got.strength_rank<need.strength_rank
  );
  SELECT RAISE(ABORT,'evidence class/source authority boundary violated')
  WHERE NOT (
    (NEW.evidence_class='E0_INFERENCE' AND NEW.evidence_source='INFERENCE')
    OR (NEW.evidence_class='E1_MANUAL_OBSERVATION' AND NEW.evidence_source='MANUAL')
    OR (NEW.evidence_class='E2_PROVIDER_CONTROL_PLANE' AND NEW.evidence_source='PROVIDER_CONTROL')
    OR (NEW.evidence_class='E3_VERIFIED_PROVIDER_EVENT' AND NEW.evidence_source='PROVIDER_EVENT')
    OR (NEW.evidence_class='E4_FIRST_PARTY_AUTHORITATIVE' AND NEW.evidence_source='FIRST_PARTY')
    OR (NEW.evidence_class='E5_CUSTOMER_OR_BANK_AUTHORITATIVE'
        AND NEW.evidence_source IN ('CUSTOMER','BANK'))
  );
  SELECT RAISE(ABORT,'E3 provider event requires verified signature')
  WHERE NEW.evidence_class='E3_VERIFIED_PROVIDER_EVENT'
    AND NEW.signature_status<>'VERIFIED';
  SELECT RAISE(ABORT,'bank cash reconciliation requires E5 bank authority')
  WHERE NEW.canonical_stage='BANK_CASH_RECONCILED' AND NOT (
    NEW.evidence_class='E5_CUSTOMER_OR_BANK_AUTHORITATIVE'
    AND NEW.evidence_source='BANK'
  );
  SELECT RAISE(ABORT,'accepted outcome requires first-party or customer authority')
  WHERE NEW.canonical_stage='ACCEPTED_OUTCOME'
    AND NEW.evidence_class NOT IN (
      'E4_FIRST_PARTY_AUTHORITATIVE','E5_CUSTOMER_OR_BANK_AUTHORITATIVE'
    );
  SELECT RAISE(ABORT,'LIVE evidence requires READY_LIVE eligibility')
  WHERE NEW.environment='LIVE' AND NOT EXISTS (
    SELECT 1 FROM journey j
    JOIN eligibility_review e ON e.eligibility_review_id=j.eligibility_review_id
    WHERE j.journey_id=NEW.journey_id AND e.current_gate='READY_LIVE'
  );
  SELECT RAISE(ABORT,'first evidence revision must be ASSERT without predecessor')
  WHERE NEW.event_revision=1
    AND (NEW.correction_kind<>'ASSERT' OR NEW.supersedes_event_id IS NOT NULL);
  SELECT RAISE(ABORT,'correction/retraction requires exact unsuperseded predecessor')
  WHERE NEW.event_revision>1 AND NOT EXISTS (
    SELECT 1 FROM stage_evidence_event p
    WHERE p.stage_event_id=NEW.supersedes_event_id
      AND p.evidence_stream_id=NEW.evidence_stream_id
      AND p.event_revision=NEW.event_revision-1
      AND p.journey_id=NEW.journey_id
      AND p.motion_contract_id=NEW.motion_contract_id
      AND p.canonical_stage=NEW.canonical_stage
      AND NOT EXISTS (
        SELECT 1 FROM stage_evidence_event n
        WHERE n.supersedes_event_id=p.stage_event_id
      )
  );
  SELECT RAISE(ABORT,'later evidence revision must be CORRECT or RETRACT')
  WHERE NEW.event_revision>1 AND NEW.correction_kind='ASSERT';
END;

-- Evidence ledgers and approved contracts are append-only in this companion.
CREATE TRIGGER immutable_motion_contract_update
BEFORE UPDATE ON motion_contract BEGIN SELECT RAISE(ABORT,'motion contract is immutable'); END;
CREATE TRIGGER immutable_motion_contract_delete
BEFORE DELETE ON motion_contract BEGIN SELECT RAISE(ABORT,'motion contract is immutable'); END;
CREATE TRIGGER immutable_policy_snapshot_update
BEFORE UPDATE ON policy_snapshot BEGIN SELECT RAISE(ABORT,'policy snapshot is immutable'); END;
CREATE TRIGGER immutable_policy_snapshot_delete
BEFORE DELETE ON policy_snapshot BEGIN SELECT RAISE(ABORT,'policy snapshot is immutable'); END;
CREATE TRIGGER immutable_artifact_revision_update
BEFORE UPDATE ON artifact_revision BEGIN SELECT RAISE(ABORT,'artifact revision is immutable'); END;
CREATE TRIGGER immutable_artifact_revision_delete
BEFORE DELETE ON artifact_revision BEGIN SELECT RAISE(ABORT,'artifact revision is immutable'); END;
CREATE TRIGGER immutable_stage_evidence_update
BEFORE UPDATE ON stage_evidence_event BEGIN SELECT RAISE(ABORT,'stage evidence is append-only'); END;
CREATE TRIGGER immutable_stage_evidence_delete
BEFORE DELETE ON stage_evidence_event BEGIN SELECT RAISE(ABORT,'stage evidence is append-only'); END;

CREATE TABLE commerce_chain (
  commerce_chain_id TEXT PRIMARY KEY,
  journey_id TEXT NOT NULL UNIQUE,
  motion_contract_id TEXT NOT NULL,
  artifact_revision_id TEXT NOT NULL,
  eligibility_review_id TEXT NOT NULL,
  environment TEXT NOT NULL CHECK (environment IN ('TEST','SANDBOX','LIVE')),
  seller_region_code TEXT NOT NULL,
  product_type TEXT NOT NULL,
  checkout_id TEXT NOT NULL UNIQUE,
  payment_provider_code TEXT NOT NULL,
  idempotency_key_ref TEXT NOT NULL,
  checkout_protocol_version TEXT NOT NULL,
  price_inventory_revision TEXT NOT NULL,
  currency TEXT NOT NULL CHECK (length(currency)=3 AND currency=upper(currency)),
  amount_micros INTEGER NOT NULL CHECK (amount_micros>0),
  buyer_authorization_status TEXT NOT NULL CHECK (
    buyer_authorization_status IN ('PASS','FAIL','UNKNOWN')
  ),
  allowance_review_status TEXT NOT NULL CHECK (
    allowance_review_status IN ('PASS','FAIL','UNKNOWN','NA')
  ),
  checkout_state TEXT NOT NULL CHECK (checkout_state IN (
    'CREATED','READY','COMPLETED','CANCELED','FAILED','UNKNOWN'
  )),
  payment_id TEXT NOT NULL UNIQUE,
  payment_state TEXT NOT NULL CHECK (payment_state IN (
    'CREATED','REQUIRES_ACTION','AUTHORIZED','CAPTURED','SUCCEEDED',
    'FAILED','CANCELED','UNKNOWN'
  )),
  payment_source_event_id TEXT NOT NULL,
  payment_signature_status TEXT NOT NULL CHECK (
    payment_signature_status IN ('VERIFIED','FAILED','UNKNOWN','NOT_APPLICABLE')
  ),
  authoritative_readback_at TEXT CHECK (
    authoritative_readback_at IS NULL OR datetime(authoritative_readback_at) IS NOT NULL
  ),
  order_id TEXT UNIQUE,
  order_state TEXT NOT NULL CHECK (order_state IN (
    'NOT_CREATED','CREATED','ACCEPTED','CANCELED','FAILED','UNKNOWN'
  )),
  outbox_event_ref TEXT,
  fulfillment_type TEXT NOT NULL CHECK (fulfillment_type IN (
    'PHYSICAL','DIGITAL','SAAS','API','SERVICE'
  )),
  unknown_retry_policy TEXT NOT NULL
    CHECK (unknown_retry_policy='READ_BACK_SAME_IDEMPOTENCY_KEY_NO_AUTO_RETRY'),
  created_at TEXT NOT NULL CHECK (datetime(created_at) IS NOT NULL),
  recorded_at TEXT NOT NULL CHECK (datetime(recorded_at) IS NOT NULL),
  data_class TEXT NOT NULL CHECK (data_class='NO_RAW_PII_OPAQUE_REF'),
  UNIQUE (environment,payment_provider_code,idempotency_key_ref),
  UNIQUE (environment,payment_provider_code,payment_source_event_id),
  FOREIGN KEY (journey_id) REFERENCES journey(journey_id),
  FOREIGN KEY (motion_contract_id) REFERENCES motion_contract(motion_contract_id),
  FOREIGN KEY (artifact_revision_id) REFERENCES artifact_revision(artifact_revision_id),
  FOREIGN KEY (eligibility_review_id) REFERENCES eligibility_review(eligibility_review_id),
  CHECK (datetime(created_at)<=datetime(recorded_at)),
  CHECK ((order_state='NOT_CREATED' AND order_id IS NULL AND outbox_event_ref IS NULL)
      OR (order_state<>'NOT_CREATED' AND order_id IS NOT NULL)),
  CHECK (payment_state<>'UNKNOWN'
      OR unknown_retry_policy='READ_BACK_SAME_IDEMPOTENCY_KEY_NO_AUTO_RETRY')
) STRICT;

CREATE TRIGGER validate_commerce_chain_before_insert
BEFORE INSERT ON commerce_chain
BEGIN
  SELECT RAISE(ABORT,'commerce chain identity does not match journey')
  WHERE NOT EXISTS (
    SELECT 1 FROM journey j
    WHERE j.journey_id=NEW.journey_id
      AND j.motion_contract_id=NEW.motion_contract_id
      AND j.artifact_revision_id=NEW.artifact_revision_id
      AND j.eligibility_review_id=NEW.eligibility_review_id
      AND j.environment=NEW.environment
  );
  SELECT RAISE(ABORT,'LIVE commerce requires READY_LIVE eligibility')
  WHERE NEW.environment='LIVE' AND NOT EXISTS (
    SELECT 1 FROM eligibility_review e
    WHERE e.eligibility_review_id=NEW.eligibility_review_id
      AND e.current_gate='READY_LIVE'
  );
  SELECT RAISE(ABORT,'commerce scope must match the motion contract')
  WHERE NOT EXISTS (
    SELECT 1 FROM motion_contract m
    WHERE m.motion_contract_id=NEW.motion_contract_id
      AND m.region_code=NEW.seller_region_code
      AND m.product_type=NEW.product_type
      AND m.currency=NEW.currency
  );
  SELECT RAISE(ABORT,'successful payment requires verified provider event and read-back')
  WHERE NEW.payment_state IN ('CAPTURED','SUCCEEDED') AND (
    NEW.payment_signature_status<>'VERIFIED'
    OR NEW.authoritative_readback_at IS NULL
  );
  SELECT RAISE(ABORT,'completed checkout requires buyer authorization')
  WHERE NEW.checkout_state='COMPLETED'
    AND NEW.buyer_authorization_status<>'PASS';
  SELECT RAISE(ABORT,'succeeded payment and created order remain explicit states')
  WHERE NEW.payment_state='SUCCEEDED' AND NEW.order_state='NOT_CREATED';
END;

CREATE TABLE fulfillment_event (
  fulfillment_event_id TEXT PRIMARY KEY,
  fulfillment_stream_id TEXT NOT NULL,
  event_revision INTEGER NOT NULL CHECK (event_revision>0),
  supersedes_fulfillment_event_id TEXT,
  commerce_chain_id TEXT NOT NULL,
  event_kind TEXT NOT NULL CHECK (event_kind IN ('FULFILLMENT','ENTITLEMENT')),
  event_state TEXT NOT NULL CHECK (event_state IN (
    'PENDING','DELIVERED','GRANTED','FAILED','REVOKED','CANCELED','UNKNOWN'
  )),
  evidence_class TEXT NOT NULL CHECK (evidence_class IN (
    'E4_FIRST_PARTY_AUTHORITATIVE','E5_CUSTOMER_OR_BANK_AUTHORITATIVE'
  )),
  source_system TEXT NOT NULL,
  source_event_id 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),
  environment TEXT NOT NULL CHECK (environment IN ('TEST','SANDBOX','LIVE')),
  payload_ref_hash TEXT NOT NULL CHECK (
    length(payload_ref_hash)=64 AND payload_ref_hash=lower(payload_ref_hash)
    AND payload_ref_hash NOT GLOB '*[^0-9a-f]*'
  ),
  UNIQUE (fulfillment_stream_id,event_revision),
  FOREIGN KEY (supersedes_fulfillment_event_id)
    REFERENCES fulfillment_event(fulfillment_event_id),
  FOREIGN KEY (commerce_chain_id) REFERENCES commerce_chain(commerce_chain_id),
  FOREIGN KEY (evidence_class) REFERENCES evidence_class_dim(evidence_class),
  CHECK (datetime(occurred_at)<=datetime(recorded_at))
) STRICT;

CREATE TRIGGER validate_fulfillment_event_before_insert
BEFORE INSERT ON fulfillment_event
BEGIN
  SELECT RAISE(ABORT,'fulfillment environment must match commerce chain')
  WHERE NOT EXISTS (
    SELECT 1 FROM commerce_chain c
    WHERE c.commerce_chain_id=NEW.commerce_chain_id
      AND c.environment=NEW.environment
  );
  SELECT RAISE(ABORT,'first fulfillment revision cannot supersede another event')
  WHERE NEW.event_revision=1 AND NEW.supersedes_fulfillment_event_id IS NOT NULL;
  SELECT RAISE(ABORT,'fulfillment revision requires exact unsuperseded predecessor')
  WHERE NEW.event_revision>1 AND NOT EXISTS (
    SELECT 1 FROM fulfillment_event p
    WHERE p.fulfillment_event_id=NEW.supersedes_fulfillment_event_id
      AND p.fulfillment_stream_id=NEW.fulfillment_stream_id
      AND p.event_revision=NEW.event_revision-1
      AND p.commerce_chain_id=NEW.commerce_chain_id
      AND p.event_kind=NEW.event_kind
      AND NOT EXISTS (
        SELECT 1 FROM fulfillment_event n
        WHERE n.supersedes_fulfillment_event_id=p.fulfillment_event_id
      )
  );
END;

CREATE TABLE cash_bridge_line (
  cash_line_id TEXT PRIMARY KEY,
  commerce_chain_id TEXT NOT NULL,
  line_type TEXT NOT NULL CHECK (line_type IN (
    'GROSS_CONSIDERATION','TAX_HELD','DISCOUNT','CREDIT','REFUND',
    'PROVIDER_FEE','FX_PAYOUT_FEE','DISPUTE_LOSS','DISPUTE_FEE'
  )),
  amount_micros INTEGER NOT NULL CHECK (amount_micros>=0),
  currency TEXT NOT NULL CHECK (length(currency)=3 AND currency=upper(currency)),
  source_authority TEXT NOT NULL CHECK (source_authority IN (
    'PROVIDER_EVENT','FIRST_PARTY'
  )),
  source_event_id 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),
  environment TEXT NOT NULL CHECK (environment IN ('TEST','SANDBOX','LIVE')),
  payload_ref_hash TEXT NOT NULL CHECK (
    length(payload_ref_hash)=64 AND payload_ref_hash=lower(payload_ref_hash)
    AND payload_ref_hash NOT GLOB '*[^0-9a-f]*'
  ),
  FOREIGN KEY (commerce_chain_id) REFERENCES commerce_chain(commerce_chain_id),
  CHECK (datetime(occurred_at)<=datetime(recorded_at))
) STRICT;

CREATE TRIGGER validate_cash_bridge_line_before_insert
BEFORE INSERT ON cash_bridge_line
BEGIN
  SELECT RAISE(ABORT,'cash line environment/currency must match commerce chain')
  WHERE NOT EXISTS (
    SELECT 1 FROM commerce_chain c
    WHERE c.commerce_chain_id=NEW.commerce_chain_id
      AND c.environment=NEW.environment AND c.currency=NEW.currency
  );
END;

CREATE TABLE payout_event (
  payout_event_id TEXT PRIMARY KEY,
  commerce_chain_id TEXT NOT NULL,
  payout_ref TEXT NOT NULL UNIQUE,
  payout_state TEXT NOT NULL CHECK (payout_state IN ('INITIATED','PAID','FAILED','UNKNOWN')),
  amount_micros INTEGER NOT NULL CHECK (amount_micros>=0),
  currency TEXT NOT NULL CHECK (length(currency)=3 AND currency=upper(currency)),
  source_event_id TEXT NOT NULL UNIQUE,
  signature_status TEXT NOT NULL CHECK (signature_status IN ('VERIFIED','FAILED','UNKNOWN')),
  occurred_at TEXT NOT NULL CHECK (datetime(occurred_at) IS NOT NULL),
  recorded_at TEXT NOT NULL CHECK (datetime(recorded_at) IS NOT NULL),
  environment TEXT NOT NULL CHECK (environment IN ('TEST','SANDBOX','LIVE')),
  FOREIGN KEY (commerce_chain_id) REFERENCES commerce_chain(commerce_chain_id),
  CHECK (datetime(occurred_at)<=datetime(recorded_at)),
  CHECK (payout_state<>'PAID' OR signature_status='VERIFIED')
) STRICT;

CREATE TRIGGER validate_payout_event_before_insert
BEFORE INSERT ON payout_event
BEGIN
  SELECT RAISE(ABORT,'payout environment/currency must match commerce chain')
  WHERE NOT EXISTS (
    SELECT 1 FROM commerce_chain c
    WHERE c.commerce_chain_id=NEW.commerce_chain_id
      AND c.environment=NEW.environment AND c.currency=NEW.currency
  );
END;

CREATE TABLE bank_reconciliation (
  bank_reconciliation_id TEXT PRIMARY KEY,
  commerce_chain_id TEXT NOT NULL,
  payout_event_id TEXT NOT NULL UNIQUE,
  bank_transaction_ref_hash TEXT NOT NULL UNIQUE CHECK (
    length(bank_transaction_ref_hash)=64
    AND bank_transaction_ref_hash=lower(bank_transaction_ref_hash)
    AND bank_transaction_ref_hash NOT GLOB '*[^0-9a-f]*'
  ),
  bank_amount_micros INTEGER NOT NULL CHECK (bank_amount_micros>=0),
  currency TEXT NOT NULL CHECK (length(currency)=3 AND currency=upper(currency)),
  reconciliation_state TEXT NOT NULL CHECK (reconciliation_state IN (
    'MATCHED','PARTIAL','FAILED','UNKNOWN'
  )),
  difference_micros INTEGER NOT NULL,
  evidence_class TEXT NOT NULL
    CHECK (evidence_class='E5_CUSTOMER_OR_BANK_AUTHORITATIVE'),
  source_authority TEXT NOT NULL CHECK (source_authority='BANK'),
  received_at TEXT NOT NULL CHECK (datetime(received_at) IS NOT NULL),
  recorded_at TEXT NOT NULL CHECK (datetime(recorded_at) IS NOT NULL),
  environment TEXT NOT NULL CHECK (environment='LIVE'),
  FOREIGN KEY (commerce_chain_id) REFERENCES commerce_chain(commerce_chain_id),
  FOREIGN KEY (payout_event_id) REFERENCES payout_event(payout_event_id),
  FOREIGN KEY (evidence_class) REFERENCES evidence_class_dim(evidence_class),
  CHECK (datetime(received_at)<=datetime(recorded_at)),
  CHECK (reconciliation_state<>'MATCHED' OR difference_micros=0)
) STRICT;

CREATE TRIGGER validate_bank_reconciliation_before_insert
BEFORE INSERT ON bank_reconciliation
BEGIN
  SELECT RAISE(ABORT,'bank reconciliation requires matching paid LIVE payout')
  WHERE NOT EXISTS (
    SELECT 1 FROM payout_event p
    WHERE p.payout_event_id=NEW.payout_event_id
      AND p.commerce_chain_id=NEW.commerce_chain_id
      AND p.environment='LIVE' AND p.payout_state='PAID'
      AND p.currency=NEW.currency
      AND (NEW.reconciliation_state<>'MATCHED'
        OR p.amount_micros=NEW.bank_amount_micros)
  );
END;

CREATE TABLE variable_cost_ledger (
  cost_event_id TEXT PRIMARY KEY,
  motion_contract_id TEXT NOT NULL,
  commerce_chain_id TEXT,
  cost_type TEXT NOT NULL CHECK (cost_type IN (
    'COGS','AI_STORAGE_DELIVERY','CHANNEL','REFUND_RESCUE_OTHER'
  )),
  amount_micros INTEGER NOT NULL CHECK (amount_micros>=0),
  currency TEXT NOT NULL CHECK (length(currency)=3 AND currency=upper(currency)),
  environment TEXT NOT NULL CHECK (environment IN ('TEST','SANDBOX','LIVE')),
  source_event_id 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),
  FOREIGN KEY (motion_contract_id) REFERENCES motion_contract(motion_contract_id),
  FOREIGN KEY (commerce_chain_id) REFERENCES commerce_chain(commerce_chain_id),
  CHECK (datetime(occurred_at)<=datetime(recorded_at))
) STRICT;

CREATE TABLE founder_time_ledger (
  time_event_id TEXT PRIMARY KEY,
  motion_contract_id TEXT NOT NULL,
  commerce_chain_id TEXT,
  activity_type TEXT NOT NULL CHECK (activity_type IN (
    'DISTRIBUTION','SUPPORT','FOUNDER_RESCUE'
  )),
  minutes INTEGER NOT NULL CHECK (minutes>0),
  environment TEXT NOT NULL CHECK (environment IN ('TEST','SANDBOX','LIVE')),
  source_event_id 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),
  FOREIGN KEY (motion_contract_id) REFERENCES motion_contract(motion_contract_id),
  FOREIGN KEY (commerce_chain_id) REFERENCES commerce_chain(commerce_chain_id),
  CHECK (datetime(occurred_at)<=datetime(recorded_at))
) STRICT;

CREATE TABLE retention_observation (
  retention_observation_id TEXT PRIMARY KEY,
  commerce_chain_id TEXT NOT NULL UNIQUE,
  maturity_state TEXT NOT NULL CHECK (maturity_state IN ('PROVISIONAL','MATURE','UNKNOWN')),
  retention_state TEXT NOT NULL CHECK (retention_state IN ('RETAINED','CHURNED','UNKNOWN')),
  evidence_class TEXT NOT NULL CHECK (evidence_class IN (
    'E4_FIRST_PARTY_AUTHORITATIVE','E5_CUSTOMER_OR_BANK_AUTHORITATIVE'
  )),
  observation_window_end TEXT NOT NULL CHECK (datetime(observation_window_end) IS NOT NULL),
  renewal_window_end TEXT NOT NULL CHECK (datetime(renewal_window_end) IS 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),
  environment TEXT NOT NULL CHECK (environment IN ('TEST','SANDBOX','LIVE')),
  evidence_ref TEXT NOT NULL,
  FOREIGN KEY (commerce_chain_id) REFERENCES commerce_chain(commerce_chain_id),
  FOREIGN KEY (evidence_class) REFERENCES evidence_class_dim(evidence_class),
  CHECK (datetime(occurred_at)<=datetime(recorded_at)),
  CHECK (maturity_state<>'MATURE' OR datetime(occurred_at)>=datetime(observation_window_end)),
  CHECK (maturity_state<>'MATURE' OR retention_state<>'UNKNOWN')
) STRICT;

CREATE TRIGGER immutable_commerce_chain_update
BEFORE UPDATE ON commerce_chain BEGIN SELECT RAISE(ABORT,'commerce chain is immutable'); END;
CREATE TRIGGER immutable_commerce_chain_delete
BEFORE DELETE ON commerce_chain BEGIN SELECT RAISE(ABORT,'commerce chain is immutable'); END;
CREATE TRIGGER immutable_fulfillment_update
BEFORE UPDATE ON fulfillment_event BEGIN SELECT RAISE(ABORT,'fulfillment ledger is append-only'); END;
CREATE TRIGGER immutable_fulfillment_delete
BEFORE DELETE ON fulfillment_event BEGIN SELECT RAISE(ABORT,'fulfillment ledger is append-only'); END;
CREATE TRIGGER immutable_cash_line_update
BEFORE UPDATE ON cash_bridge_line BEGIN SELECT RAISE(ABORT,'cash bridge is append-only'); END;
CREATE TRIGGER immutable_cash_line_delete
BEFORE DELETE ON cash_bridge_line BEGIN SELECT RAISE(ABORT,'cash bridge is append-only'); END;

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

INSERT INTO schema_registry VALUES (
  1,'ai_distribution_evidence','1.0.0','SQLite 3.40+',
  '2026-08-02T00:00:00Z','SYNTHETIC_ONLY_NO_RAW_PII_OPAQUE_REFERENCES'
);
INSERT INTO model_setting VALUES (
  1,'2026-08-20T00:00:00Z','2026-08-15T00:00:00Z','JPY',6000000000
);

INSERT INTO evidence_class_dim VALUES
 ('E0_INFERENCE',0),('E1_MANUAL_OBSERVATION',1),
 ('E2_PROVIDER_CONTROL_PLANE',2),('E3_VERIFIED_PROVIDER_EVENT',3),
 ('E4_FIRST_PARTY_AUTHORITATIVE',4),
 ('E5_CUSTOMER_OR_BANK_AUTHORITATIVE',5);

INSERT INTO canonical_stage_dim VALUES
 ('ELIGIBLE',10,'ELIGIBILITY'),
 ('ARTIFACT_VALIDATED',20,'ARTIFACT'),
 ('SUBMITTED',30,'DISTRIBUTION'),
 ('APPROVED',40,'DISTRIBUTION'),
 ('PUBLISHED_OR_INDEXED',50,'DISTRIBUTION'),
 ('EXACTLY_RESOLVABLE',60,'DISTRIBUTION'),
 ('SURFACED',70,'DISCOVERY'),
 ('CITED_OR_SELECTED',80,'DISCOVERY'),
 ('VISITED_OR_INSTALLED',90,'DISCOVERY'),
 ('CONNECTED',100,'RUNTIME'),
 ('INVOKED',110,'RUNTIME'),
 ('ACCEPTED_OUTCOME',120,'OUTCOME'),
 ('CHECKOUT_CREATED',130,'COMMERCE'),
 ('PAYMENT_SUCCEEDED',140,'COMMERCE'),
 ('ORDER_CREATED',150,'COMMERCE'),
 ('FULFILLED_OR_ENTITLED',160,'COMMERCE'),
 ('BANK_CASH_RECONCILED',170,'CASH'),
 ('RETAINED',180,'RETENTION');

INSERT INTO motion_contract VALUES
 ('MOT_SEARCH_SYN',1,'SEARCH_CITATION','SEARCH_PROVIDER_SYN','ENTITY_SYN_JP',
  'JP','WEB_SAAS','COHORT_SEARCH_SYN','RETAINED_NET_CONTRIBUTION',
  'ELIGIBLE_FIXED_PROBE_AND_LIVE_JOURNEY','OWNER_RULE_SYN_V1',
  'TOTAL_PROVIDER_IMPRESSIONS_UNKNOWN',
  '2026-08-01T00:00:00Z','2026-08-14T23:59:59Z','2026-08-15T00:00:00Z',
  '2026-08-14T23:59:59Z',240,3000000000,3,'JPY','FOUNDER',
  '2026-07-31T10:00:00Z',lower(hex(zeroblob(32)))),
 ('MOT_PLUGIN_SYN',1,'OPENAI_PLUGIN','PLUGIN_PROVIDER_SYN','ENTITY_SYN_JP',
  'JP','EXISTING_SAAS_ENTITLEMENT','COHORT_PLUGIN_SYN','ACCEPTED_OUTCOME',
  'ELIGIBLE_INSTALLED_JOBS','OWNER_RULE_SYN_V1',
  'PROVIDER_IMPRESSIONS_AND_SUGGESTIONS_UNKNOWN',
  '2026-08-01T00:00:00Z','2026-08-10T23:59:59Z','2026-08-15T00:00:00Z',
  '2026-08-14T00:00:00Z',180,1000000000,2,'JPY','FOUNDER',
  '2026-07-31T10:05:00Z',lower(hex(zeroblob(32)))),
 ('MOT_MCP_SYN',1,'MCP_REGISTRY','MCP_REGISTRY_SYN','ENTITY_SYN_JP',
  'ALL','PUBLIC_MCP_SERVER','COHORT_MCP_SYN','EXACT_RESOLUTION',
  'SCHEDULED_EXACT_LOOKUPS','OWNER_RULE_SYN_V1',
  'DOWNSTREAM_AGGREGATOR_INSTALL_BASE_UNKNOWN',
  '2026-08-01T00:00:00Z','2026-08-10T23:59:59Z','2026-08-15T00:00:00Z',
  '2026-08-14T00:00:00Z',120,500000000,0,'JPY','FOUNDER',
  '2026-07-31T10:10:00Z',lower(hex(zeroblob(32)))),
 ('MOT_AGENTIC_SYN',1,'AGENTIC_COMMERCE','COMMERCE_PROVIDER_SYN','ENTITY_SYN_JP',
  'JP','PHYSICAL_GOOD','COHORT_AGENTIC_SYN','RETAINED_NET_CONTRIBUTION',
  'AUTHORIZED_SANDBOX_CHECKOUTS','OWNER_RULE_SYN_V1',
  'LIVE_PROGRAM_ACCESS_NOT_AVAILABLE_IN_SYNTHETIC_SCOPE',
  '2026-08-01T00:00:00Z','2026-08-10T23:59:59Z','2026-08-15T00:00:00Z',
  '2026-08-14T00:00:00Z',120,500000000,0,'JPY','FOUNDER',
  '2026-07-31T10:15:00Z',lower(hex(zeroblob(32))));

INSERT INTO policy_snapshot VALUES
 ('POL_SEARCH_CURRENT_SYN','SEARCH_PROVIDER_SYN','AI_SEARCH_VISIBILITY_SYN',
  'Synthetic current search program scope','https://example.invalid/synthetic/search-current',
  'SYNTHETIC_PUBLISHER','2026-07-30T00:00:00Z',lower(hex(zeroblob(32))),
  'CURRENT','2026-07-01T00:00:00Z',NULL,'search-contract-syn-v1','JP','WEB_SAAS',
  'ALLOWS',NULL,'SYN_FACT_SEARCH_CURRENT','SYN_NONCLAIM_NO_IMPRESSION_GUARANTEE',
  '2026-08-30T00:00:00Z','HUMAN_REVIEWER','2026-07-31T08:00:00Z'),
 ('POL_PLUGIN_CURRENT_SYN','PLUGIN_PROVIDER_SYN','PLUGIN_DIRECTORY_SYN',
  'Synthetic current plugin program scope','https://example.invalid/synthetic/plugin-current',
  'SYNTHETIC_PUBLISHER','2026-07-30T00:00:00Z',lower(hex(zeroblob(32))),
  'CURRENT','2026-07-01T00:00:00Z',NULL,'plugin-contract-syn-v2','JP',
  'EXISTING_SAAS_ENTITLEMENT','ALLOWS',NULL,'SYN_FACT_PLUGIN_CURRENT',
  'SYN_NONCLAIM_NO_DISCOVERY_GUARANTEE','2026-08-30T00:00:00Z',
  'HUMAN_REVIEWER','2026-07-31T08:05:00Z'),
 ('POL_MCP_PREVIEW_SYN','MCP_REGISTRY_SYN','MCP_REGISTRY_PREVIEW_SYN',
  'Synthetic preview registry contract','https://example.invalid/synthetic/mcp-preview',
  'SYNTHETIC_PUBLISHER','2026-07-30T00:00:00Z',lower(hex(zeroblob(32))),
  'PREVIEW','2026-07-01T00:00:00Z',NULL,'registry-syn-v1.8','ALL',
  'PUBLIC_MCP_SERVER','UNKNOWN',NULL,'SYN_FACT_PREVIEW_CAN_CHANGE',
  'SYN_NONCLAIM_NO_DOWNSTREAM_INSTALL','2026-08-10T00:00:00Z',
  'HUMAN_REVIEWER','2026-07-31T08:10:00Z'),
 ('POL_AGENTIC_CURRENT_DENY_SYN','COMMERCE_PROVIDER_SYN','AGENTIC_SELLER_SYN',
  'Synthetic current restricted agentic scope',
  'https://example.invalid/synthetic/agentic-current',
  'SYNTHETIC_PUBLISHER','2026-07-30T00:00:00Z',lower(hex(zeroblob(32))),
  'CURRENT','2026-07-01T00:00:00Z',NULL,'commerce-syn-v1','JP','PHYSICAL_GOOD',
  'DENIES',NULL,'SYN_FACT_CURRENT_SCOPE_DENIES','SYN_NONCLAIM_PROTOCOL_NOT_ELIGIBILITY',
  '2026-08-15T00:00:00Z','HUMAN_REVIEWER','2026-07-31T08:15:00Z'),
 ('POL_AGENTIC_UPCOMING_SYN','COMMERCE_PROVIDER_SYN','AGENTIC_SELLER_SYN',
  'Synthetic upcoming agentic option','https://example.invalid/synthetic/agentic-upcoming',
  'SYNTHETIC_PUBLISHER','2026-07-30T00:00:00Z',lower(hex(zeroblob(32))),
  'UPCOMING','2026-10-01T00:00:00Z',NULL,'commerce-syn-v-next','JP','PHYSICAL_GOOD',
  'UNKNOWN',NULL,'SYN_FACT_ANNOUNCED_NOT_EFFECTIVE','SYN_NONCLAIM_NO_LIVE_FORECAST',
  '2026-09-15T00:00:00Z','HUMAN_REVIEWER','2026-07-31T08:20:00Z'),
 ('POL_MCP_CONFLICT_A_SYN','MCP_REGISTRY_SYN','MCP_REGISTRY_STATUS_SYN',
  'Synthetic prose status A','https://example.invalid/synthetic/mcp-conflict-a',
  'SYNTHETIC_PUBLISHER','2026-07-30T00:00:00Z',lower(hex(zeroblob(32))),
  'CONFLICTING','2026-07-01T00:00:00Z',NULL,'prose-syn-a','ALL','PUBLIC_MCP_SERVER',
  'UNKNOWN','CONFLICT_MCP_STATUS_SYN','SYN_FACT_CONFLICT_A','SYN_NONCLAIM_UNRESOLVED',
  '2026-08-03T00:00:00Z','HUMAN_REVIEWER','2026-07-31T08:25:00Z'),
 ('POL_MCP_CONFLICT_B_SYN','MCP_REGISTRY_SYN','MCP_REGISTRY_STATUS_SYN',
  'Synthetic API status B','https://example.invalid/synthetic/mcp-conflict-b',
  'SYNTHETIC_PUBLISHER','2026-07-30T00:00:00Z',lower(hex(zeroblob(32))),
  'CONFLICTING','2026-07-01T00:00:00Z',NULL,'api-syn-b','ALL','PUBLIC_MCP_SERVER',
  'UNKNOWN','CONFLICT_MCP_STATUS_SYN','SYN_FACT_CONFLICT_B','SYN_NONCLAIM_UNRESOLVED',
  '2026-08-03T00:00:00Z','HUMAN_REVIEWER','2026-07-31T08:30:00Z');

INSERT INTO motion_policy_link VALUES
 ('MOT_SEARCH_SYN','POL_SEARCH_CURRENT_SYN','EXECUTION_SCOPE'),
 ('MOT_SEARCH_SYN','POL_SEARCH_CURRENT_SYN','PROTOCOL_CONTRACT'),
 ('MOT_PLUGIN_SYN','POL_PLUGIN_CURRENT_SYN','EXECUTION_SCOPE'),
 ('MOT_PLUGIN_SYN','POL_PLUGIN_CURRENT_SYN','PROTOCOL_CONTRACT'),
 ('MOT_MCP_SYN','POL_MCP_PREVIEW_SYN','PROTOCOL_CONTRACT'),
 ('MOT_MCP_SYN','POL_MCP_CONFLICT_A_SYN','CONFLICT_NOTE'),
 ('MOT_MCP_SYN','POL_MCP_CONFLICT_B_SYN','CONFLICT_NOTE'),
 ('MOT_AGENTIC_SYN','POL_AGENTIC_CURRENT_DENY_SYN','EXECUTION_SCOPE'),
 ('MOT_AGENTIC_SYN','POL_AGENTIC_CURRENT_DENY_SYN','PROTOCOL_CONTRACT'),
 ('MOT_AGENTIC_SYN','POL_AGENTIC_UPCOMING_SYN','PREVIEW_OPTION');

INSERT INTO eligibility_review VALUES
 ('ELIG_SEARCH_SYN','MOT_SEARCH_SYN',1,'PASS','PASS','PASS','PASS','PASS','PASS',
  'PASS','PASS','READY_LIVE','SYN_ALL_CURRENT_GATES_PASS',
  'CAPPED_LIVE_PROBE_AND_JOURNEY','NO_UNCAPPED_LIVE','HUMAN_REVIEWER',
  '2026-07-31T09:00:00Z','2026-08-10T00:00:00Z'),
 ('ELIG_PLUGIN_SYN','MOT_PLUGIN_SYN',1,'PASS','PASS','PASS','PASS','PASS','PASS',
  'PASS','PASS','READY_LIVE','SYN_EXISTING_ENTITLEMENT_RUNTIME',
  'CAPPED_EXISTING_ACCOUNT_RUNTIME','NO_PLUGIN_CHECKOUT','HUMAN_REVIEWER',
  '2026-07-31T09:05:00Z','2026-08-10T00:00:00Z'),
 ('ELIG_MCP_SYN','MOT_MCP_SYN',1,'PASS','PASS','PASS','PASS','PASS','PASS',
  'PASS','PASS','READY_TEST','SYN_PREVIEW_AND_CONFLICTING_SOURCES',
  'SANDBOX_CONFORMANCE_AND_EXACT_LOOKUP','NO_LIVE_REVENUE_FORECAST',
  'HUMAN_REVIEWER','2026-07-31T09:10:00Z','2026-08-03T00:00:00Z'),
 ('ELIG_AGENTIC_SYN','MOT_AGENTIC_SYN',1,'PASS','FAIL','PASS','PASS','PASS','PASS',
  'PASS','PASS','NOT_ELIGIBLE','SYN_CURRENT_REGION_SCOPE_DENIED',
  'SANDBOX_CONFORMANCE_ONLY','NO_LIVE_CHECKOUT_OR_REVENUE','HUMAN_REVIEWER',
  '2026-07-31T09:15:00Z','2026-08-15T00:00:00Z');

-- Every route declares all canonical stages; N/A is contract data, not a null event.
INSERT INTO route_stage_contract
SELECT 'MOT_SEARCH_SYN',1,s.canonical_stage,
  CASE
    WHEN s.canonical_stage IN ('SUBMITTED','APPROVED','CONNECTED') THEN 'NA'
    WHEN s.canonical_stage='SURFACED' THEN 'OPTIONAL'
    ELSE 'REQUIRED' END,
  CASE
    WHEN s.canonical_stage IN ('SUBMITTED','APPROVED','CONNECTED') THEN NULL
    WHEN s.canonical_stage IN ('SURFACED','CITED_OR_SELECTED') THEN 'E1_MANUAL_OBSERVATION'
    WHEN s.canonical_stage IN ('ELIGIBLE','PUBLISHED_OR_INDEXED','EXACTLY_RESOLVABLE') THEN 'E2_PROVIDER_CONTROL_PLANE'
    WHEN s.canonical_stage='PAYMENT_SUCCEEDED' THEN 'E3_VERIFIED_PROVIDER_EVENT'
    WHEN s.canonical_stage IN ('ACCEPTED_OUTCOME','BANK_CASH_RECONCILED','RETAINED') THEN 'E5_CUSTOMER_OR_BANK_AUTHORITATIVE'
    ELSE 'E4_FIRST_PARTY_AUTHORITATIVE' END,
  CASE WHEN s.canonical_stage IN ('SUBMITTED','APPROVED','CONNECTED') THEN NULL
       ELSE 'SYN_DENOM_'||s.canonical_stage END,
  CASE WHEN s.canonical_stage IN ('SUBMITTED','APPROVED','CONNECTED') THEN NULL
       WHEN s.canonical_stage IN ('SURFACED','CITED_OR_SELECTED') THEN 'FIXED_MANUAL_PROBE'
       WHEN s.canonical_stage IN ('ELIGIBLE','PUBLISHED_OR_INDEXED','EXACTLY_RESOLVABLE') THEN 'PROVIDER_CONTROL_PLANE'
       WHEN s.canonical_stage='PAYMENT_SUCCEEDED' THEN 'VERIFIED_PROVIDER_EVENT'
       WHEN s.canonical_stage IN ('ACCEPTED_OUTCOME','RETAINED') THEN 'CUSTOMER_ACCEPTANCE_LEDGER'
       WHEN s.canonical_stage='BANK_CASH_RECONCILED' THEN 'BANK_RECONCILIATION'
       ELSE 'FIRST_PARTY_LEDGER' END,
  'FOUNDER','2026-07-31T09:30:00Z'
FROM canonical_stage_dim s;

INSERT INTO route_stage_contract
SELECT 'MOT_PLUGIN_SYN',1,s.canonical_stage,
  CASE
    WHEN s.canonical_stage IN ('CHECKOUT_CREATED','PAYMENT_SUCCEEDED','ORDER_CREATED',
      'FULFILLED_OR_ENTITLED','BANK_CASH_RECONCILED','RETAINED') THEN 'NA'
    WHEN s.canonical_stage IN ('SURFACED','CITED_OR_SELECTED') THEN 'OPTIONAL'
    ELSE 'REQUIRED' END,
  CASE
    WHEN s.canonical_stage IN ('CHECKOUT_CREATED','PAYMENT_SUCCEEDED','ORDER_CREATED',
      'FULFILLED_OR_ENTITLED','BANK_CASH_RECONCILED','RETAINED') THEN NULL
    WHEN s.canonical_stage IN ('SURFACED','CITED_OR_SELECTED') THEN 'E1_MANUAL_OBSERVATION'
    WHEN s.canonical_stage IN ('ELIGIBLE','SUBMITTED','APPROVED','PUBLISHED_OR_INDEXED',
      'EXACTLY_RESOLVABLE','VISITED_OR_INSTALLED') THEN 'E2_PROVIDER_CONTROL_PLANE'
    ELSE 'E4_FIRST_PARTY_AUTHORITATIVE' END,
  CASE WHEN s.canonical_stage IN ('CHECKOUT_CREATED','PAYMENT_SUCCEEDED','ORDER_CREATED',
      'FULFILLED_OR_ENTITLED','BANK_CASH_RECONCILED','RETAINED') THEN NULL
       ELSE 'SYN_DENOM_'||s.canonical_stage END,
  CASE WHEN s.canonical_stage IN ('CHECKOUT_CREATED','PAYMENT_SUCCEEDED','ORDER_CREATED',
      'FULFILLED_OR_ENTITLED','BANK_CASH_RECONCILED','RETAINED') THEN NULL
       WHEN s.canonical_stage IN ('SURFACED','CITED_OR_SELECTED') THEN 'FIXED_MANUAL_PROBE'
       WHEN s.canonical_stage IN ('ELIGIBLE','SUBMITTED','APPROVED','PUBLISHED_OR_INDEXED',
         'EXACTLY_RESOLVABLE','VISITED_OR_INSTALLED') THEN 'PROVIDER_CONTROL_PLANE'
       ELSE 'FIRST_PARTY_LEDGER' END,
  'FOUNDER','2026-07-31T09:35:00Z'
FROM canonical_stage_dim s;

INSERT INTO route_stage_contract
SELECT 'MOT_MCP_SYN',1,s.canonical_stage,
  CASE
    WHEN s.canonical_stage IN ('SUBMITTED','APPROVED','SURFACED','CITED_OR_SELECTED',
      'CHECKOUT_CREATED','PAYMENT_SUCCEEDED','ORDER_CREATED','FULFILLED_OR_ENTITLED',
      'BANK_CASH_RECONCILED','RETAINED') THEN 'NA'
    WHEN s.canonical_stage IN ('VISITED_OR_INSTALLED','CONNECTED','INVOKED','ACCEPTED_OUTCOME') THEN 'OPTIONAL'
    ELSE 'REQUIRED' END,
  CASE
    WHEN s.canonical_stage IN ('SUBMITTED','APPROVED','SURFACED','CITED_OR_SELECTED',
      'CHECKOUT_CREATED','PAYMENT_SUCCEEDED','ORDER_CREATED','FULFILLED_OR_ENTITLED',
      'BANK_CASH_RECONCILED','RETAINED') THEN NULL
    WHEN s.canonical_stage IN ('ELIGIBLE','PUBLISHED_OR_INDEXED','EXACTLY_RESOLVABLE',
      'VISITED_OR_INSTALLED') THEN 'E2_PROVIDER_CONTROL_PLANE'
    ELSE 'E4_FIRST_PARTY_AUTHORITATIVE' END,
  CASE WHEN s.canonical_stage IN ('SUBMITTED','APPROVED','SURFACED','CITED_OR_SELECTED',
      'CHECKOUT_CREATED','PAYMENT_SUCCEEDED','ORDER_CREATED','FULFILLED_OR_ENTITLED',
      'BANK_CASH_RECONCILED','RETAINED') THEN NULL
       ELSE 'SYN_DENOM_'||s.canonical_stage END,
  CASE WHEN s.canonical_stage IN ('SUBMITTED','APPROVED','SURFACED','CITED_OR_SELECTED',
      'CHECKOUT_CREATED','PAYMENT_SUCCEEDED','ORDER_CREATED','FULFILLED_OR_ENTITLED',
      'BANK_CASH_RECONCILED','RETAINED') THEN NULL
       WHEN s.canonical_stage IN ('ELIGIBLE','PUBLISHED_OR_INDEXED','EXACTLY_RESOLVABLE',
         'VISITED_OR_INSTALLED') THEN 'REGISTRY_OR_AGGREGATOR_CONTROL_PLANE'
       ELSE 'FIRST_PARTY_LEDGER' END,
  'FOUNDER','2026-07-31T09:40:00Z'
FROM canonical_stage_dim s;

INSERT INTO route_stage_contract
SELECT 'MOT_AGENTIC_SYN',1,s.canonical_stage,
  CASE
    WHEN s.canonical_stage='VISITED_OR_INSTALLED' THEN 'NA'
    WHEN s.canonical_stage IN ('SUBMITTED','SURFACED','CONNECTED') THEN 'OPTIONAL'
    ELSE 'REQUIRED' END,
  CASE
    WHEN s.canonical_stage='VISITED_OR_INSTALLED' THEN NULL
    WHEN s.canonical_stage IN ('ELIGIBLE','SUBMITTED','APPROVED','PUBLISHED_OR_INDEXED',
      'EXACTLY_RESOLVABLE','SURFACED') THEN 'E2_PROVIDER_CONTROL_PLANE'
    WHEN s.canonical_stage IN ('CITED_OR_SELECTED','PAYMENT_SUCCEEDED') THEN 'E3_VERIFIED_PROVIDER_EVENT'
    WHEN s.canonical_stage IN ('BANK_CASH_RECONCILED','RETAINED') THEN 'E5_CUSTOMER_OR_BANK_AUTHORITATIVE'
    ELSE 'E4_FIRST_PARTY_AUTHORITATIVE' END,
  CASE WHEN s.canonical_stage='VISITED_OR_INSTALLED' THEN NULL
       ELSE 'SYN_DENOM_'||s.canonical_stage END,
  CASE WHEN s.canonical_stage='VISITED_OR_INSTALLED' THEN NULL
       WHEN s.canonical_stage IN ('ELIGIBLE','SUBMITTED','APPROVED','PUBLISHED_OR_INDEXED',
         'EXACTLY_RESOLVABLE','SURFACED') THEN 'PROVIDER_CONTROL_PLANE'
       WHEN s.canonical_stage IN ('CITED_OR_SELECTED','PAYMENT_SUCCEEDED') THEN 'VERIFIED_PROVIDER_EVENT'
       WHEN s.canonical_stage='BANK_CASH_RECONCILED' THEN 'BANK_RECONCILIATION'
       WHEN s.canonical_stage='RETAINED' THEN 'CUSTOMER_RETENTION_EVIDENCE'
       ELSE 'FIRST_PARTY_LEDGER' END,
  'FOUNDER','2026-07-31T09:45:00Z'
FROM canonical_stage_dim s;

INSERT INTO artifact_revision VALUES
 ('REV_SEARCH_SYN_1','ART_SEARCH_SYN','MOT_SEARCH_SYN',1,NULL,'release-search-syn-1',
  'WEB_CONTENT',lower(hex(zeroblob(32))),'web-schema-syn-v1','PASS','PASS','PASS',
  'rollback-search-syn-1','FOUNDER','2026-07-31T10:00:00Z','2026-07-31T10:01:00Z'),
 ('REV_PLUGIN_SYN_1','ART_PLUGIN_SYN','MOT_PLUGIN_SYN',1,NULL,'release-plugin-syn-1',
  'PLUGIN_PACKAGE',lower(hex(zeroblob(32))),'plugin-schema-syn-v1','PASS','PASS','PASS',
  'rollback-plugin-syn-1','FOUNDER','2026-07-31T10:05:00Z','2026-07-31T10:06:00Z'),
 ('REV_PLUGIN_SYN_2','ART_PLUGIN_SYN','MOT_PLUGIN_SYN',2,'REV_PLUGIN_SYN_1',
  'release-plugin-syn-2','PLUGIN_PACKAGE',lower(hex(zeroblob(32))),
  'plugin-schema-syn-v2','PASS','PASS','PASS','rollback-plugin-syn-2','FOUNDER',
  '2026-08-01T10:05:00Z','2026-08-01T10:06:00Z'),
 ('REV_MCP_SYN_1','ART_MCP_SYN','MOT_MCP_SYN',1,NULL,'release-mcp-syn-1',
  'MCP_SERVER_METADATA',lower(hex(zeroblob(32))),'registry-schema-syn-v1.8',
  'PASS','PASS','PASS','rollback-mcp-syn-1','FOUNDER',
  '2026-07-31T10:10:00Z','2026-07-31T10:11:00Z'),
 ('REV_AGENTIC_SYN_1','ART_AGENTIC_SYN','MOT_AGENTIC_SYN',1,NULL,
  'release-agentic-syn-1','COMMERCE_BUNDLE',lower(hex(zeroblob(32))),
  'ucp-schema-syn-v1','PASS','PASS','PASS','rollback-agentic-syn-1','FOUNDER',
  '2026-07-31T10:15:00Z','2026-07-31T10:16:00Z');

INSERT INTO journey VALUES
 ('JNY_SEARCH_LIVE_SYN','MOT_SEARCH_SYN','REV_SEARCH_SYN_1','ELIG_SEARCH_SYN','LIVE',
  'COHORT_MEMBER_SEARCH_SYN_1','COMMERCIAL_OWNER_SYN_SEARCH_1','PROVIDER_ORIGIN_UNKNOWN',
  'SIGNED_SEARCH_REFERRAL_SYN',0,'2026-08-02T00:00:00Z','2026-08-02T00:00:01Z'),
 ('JNY_SEARCH_ZERO_SYN','MOT_SEARCH_SYN','REV_SEARCH_SYN_1','ELIG_SEARCH_SYN','LIVE',
  'COHORT_MEMBER_SEARCH_SYN_2','COMMERCIAL_OWNER_SYN_SEARCH_2','PROVIDER_ORIGIN_UNKNOWN',
  'NO_VERIFIED_TOUCH_SYN',0,'2026-08-02T00:10:00Z','2026-08-02T00:10:01Z'),
 ('JNY_PLUGIN_LIVE_SYN','MOT_PLUGIN_SYN','REV_PLUGIN_SYN_2','ELIG_PLUGIN_SYN','LIVE',
  'COHORT_MEMBER_PLUGIN_SYN_1','COMMERCIAL_OWNER_SYN_PLUGIN_1','PLUGIN_PROVIDER_SYN',
  'VERIFIED_INSTALL_SYN',0,'2026-08-02T01:00:00Z','2026-08-02T01:00:01Z'),
 ('JNY_MCP_SANDBOX_SYN','MOT_MCP_SYN','REV_MCP_SYN_1','ELIG_MCP_SYN','SANDBOX',
  'COHORT_MEMBER_MCP_SYN_1','COMMERCIAL_OWNER_SYN_MCP_1','REGISTRY_SYN',
  'EXACT_LOOKUP_SYN',0,'2026-08-02T02:00:00Z','2026-08-02T02:00:01Z'),
 ('JNY_AGENTIC_SANDBOX_SYN','MOT_AGENTIC_SYN','REV_AGENTIC_SYN_1',
  'ELIG_AGENTIC_SYN','SANDBOX','COHORT_MEMBER_AGENTIC_SYN_1',
  'COMMERCIAL_OWNER_SYN_AGENTIC_1','AGENTIC_PROVIDER_SYN','SANDBOX_SELECTION_SYN',0,
  '2026-08-02T03:00:00Z','2026-08-02T03:00:01Z');

INSERT INTO journey_correlation_boundary VALUES
 ('CORR_SEARCH_PROVIDER_SYN','JNY_SEARCH_LIVE_SYN','PROVIDER_TO_LANDING',
  'PROVIDER_LINK_SYN','LANDING_SESSION_SYN','VERIFIED','SIGNED_REF_SYN','ATTRIBUTION_ONLY',
  'SIGNED_PARAMETER','2026-08-02T00:05:00Z'),
 ('CORR_SEARCH_ACCOUNT_UNKNOWN_SYN','JNY_SEARCH_LIVE_SYN','LANDING_TO_ACCOUNT',
  'LANDING_SESSION_SYN',NULL,'UNKNOWN_LINK','NO_CONSENTED_JOIN_SYN','NO_JOIN',
  'NONE','2026-08-02T00:06:00Z'),
 ('CORR_SEARCH_CHECKOUT_SYN','JNY_SEARCH_LIVE_SYN','ACCOUNT_TO_CHECKOUT',
  'ACCOUNT_SYN','CHECKOUT_SEARCH_SYN','VERIFIED','FIRST_PARTY_SYN','COMMERCIAL_OPERATIONS',
  'CONSENTED_FIRST_PARTY','2026-08-04T00:00:00Z'),
 ('CORR_SEARCH_PAYMENT_SYN','JNY_SEARCH_LIVE_SYN','CHECKOUT_TO_PAYMENT',
  'CHECKOUT_SEARCH_SYN','PAYMENT_SEARCH_SYN','VERIFIED','PROVIDER_OBJECT_SYN','RECONCILIATION',
  'AUTHORITATIVE_OBJECT','2026-08-04T00:01:00Z'),
 ('CORR_SEARCH_ORDER_SYN','JNY_SEARCH_LIVE_SYN','PAYMENT_TO_ORDER',
  'PAYMENT_SEARCH_SYN','ORDER_SEARCH_SYN','VERIFIED','FIRST_PARTY_ORDER_SYN','FULFILLMENT',
  'AUTHORITATIVE_OBJECT','2026-08-04T00:02:00Z'),
 ('CORR_PLUGIN_BANK_NA_SYN','JNY_PLUGIN_LIVE_SYN','PAYOUT_TO_BANK',
  'NO_NEW_PAYMENT_SYN',NULL,'NA','EXISTING_ENTITLEMENT_MOTION_SYN','NOT_APPLICABLE',
  'NOT_APPLICABLE','2026-08-04T00:03:00Z');

CREATE TEMP TABLE stage_fixture (
  stage_event_id TEXT, evidence_stream_id TEXT, event_revision INTEGER,
  correction_kind TEXT, supersedes_event_id TEXT, journey_id TEXT,
  motion_contract_id TEXT, artifact_revision_id TEXT, policy_snapshot_id TEXT,
  canonical_stage TEXT, observation_state TEXT, evidence_class TEXT,
  evidence_source TEXT, source_system TEXT, source_event_id TEXT,
  source_revision TEXT, causal_sequence INTEGER, ingest_sequence INTEGER,
  occurred_at TEXT, recorded_at TEXT, environment TEXT,
  denominator_count INTEGER, result_count INTEGER, signature_status TEXT
);

INSERT INTO stage_fixture VALUES
 -- Search: distribution, accepted outcome, commerce, bank, and retained cash.
 ('EV_SEARCH_ELIG_SYN','STR_SEARCH_ELIG_SYN',1,'ASSERT',NULL,'JNY_SEARCH_LIVE_SYN',
  'MOT_SEARCH_SYN','REV_SEARCH_SYN_1','POL_SEARCH_CURRENT_SYN','ELIGIBLE','OBSERVED',
  'E2_PROVIDER_CONTROL_PLANE','PROVIDER_CONTROL','search_control_syn','src_search_elig_syn',
  '1',1,1,'2026-08-02T00:00:00Z','2026-08-02T00:00:02Z','LIVE',1,1,'NOT_APPLICABLE'),
 ('EV_SEARCH_ART_SYN','STR_SEARCH_ART_SYN',1,'ASSERT',NULL,'JNY_SEARCH_LIVE_SYN',
  'MOT_SEARCH_SYN','REV_SEARCH_SYN_1','POL_SEARCH_CURRENT_SYN','ARTIFACT_VALIDATED','OBSERVED',
  'E4_FIRST_PARTY_AUTHORITATIVE','FIRST_PARTY','release_ledger_syn','src_search_art_syn',
  '1',1,2,'2026-08-02T00:01:00Z','2026-08-02T00:01:01Z','LIVE',1,1,'NOT_APPLICABLE'),
 ('EV_SEARCH_INDEX_SYN','STR_SEARCH_INDEX_SYN',1,'ASSERT',NULL,'JNY_SEARCH_LIVE_SYN',
  'MOT_SEARCH_SYN','REV_SEARCH_SYN_1','POL_SEARCH_CURRENT_SYN','PUBLISHED_OR_INDEXED','OBSERVED',
  'E2_PROVIDER_CONTROL_PLANE','PROVIDER_CONTROL','search_control_syn','src_search_index_syn',
  '1',2,3,'2026-08-02T00:02:00Z','2026-08-02T00:02:02Z','LIVE',1,1,'NOT_APPLICABLE'),
 ('EV_SEARCH_EXACT_1_SYN','STR_SEARCH_EXACT_SYN',1,'ASSERT',NULL,'JNY_SEARCH_LIVE_SYN',
  'MOT_SEARCH_SYN','REV_SEARCH_SYN_1','POL_SEARCH_CURRENT_SYN','EXACTLY_RESOLVABLE','FAILED',
  'E2_PROVIDER_CONTROL_PLANE','PROVIDER_CONTROL','search_control_syn','src_search_exact_fail_syn',
  '1',3,4,'2026-08-02T00:03:00Z','2026-08-02T00:03:02Z','LIVE',1,0,'NOT_APPLICABLE'),
 ('EV_SEARCH_EXACT_2_SYN','STR_SEARCH_EXACT_SYN',2,'CORRECT','EV_SEARCH_EXACT_1_SYN',
  'JNY_SEARCH_LIVE_SYN','MOT_SEARCH_SYN','REV_SEARCH_SYN_1','POL_SEARCH_CURRENT_SYN',
  'EXACTLY_RESOLVABLE','OBSERVED','E2_PROVIDER_CONTROL_PLANE','PROVIDER_CONTROL',
  'search_control_syn','src_search_exact_correct_syn','2',4,5,
  '2026-08-02T00:04:00Z','2026-08-02T00:04:02Z','LIVE',1,1,'NOT_APPLICABLE'),
 ('EV_SEARCH_SURFACE_SYN','STR_SEARCH_SURFACE_SYN',1,'ASSERT',NULL,'JNY_SEARCH_LIVE_SYN',
  'MOT_SEARCH_SYN','REV_SEARCH_SYN_1','POL_SEARCH_CURRENT_SYN','SURFACED','OBSERVED',
  'E1_MANUAL_OBSERVATION','MANUAL','fixed_probe_syn','src_search_surface_syn',
  'probe-v1',NULL,6,'2026-08-02T00:05:00Z','2026-08-02T00:06:00Z','LIVE',1,1,'NOT_APPLICABLE'),
 ('EV_SEARCH_CITE_SYN','STR_SEARCH_CITE_SYN',1,'ASSERT',NULL,'JNY_SEARCH_LIVE_SYN',
  'MOT_SEARCH_SYN','REV_SEARCH_SYN_1','POL_SEARCH_CURRENT_SYN','CITED_OR_SELECTED','OBSERVED',
  'E1_MANUAL_OBSERVATION','MANUAL','fixed_probe_syn','src_search_cite_syn',
  'probe-v1',NULL,7,'2026-08-02T00:05:00Z','2026-08-02T00:06:01Z','LIVE',1,1,'NOT_APPLICABLE'),
 ('EV_SEARCH_VISIT_SYN','STR_SEARCH_VISIT_SYN',1,'ASSERT',NULL,'JNY_SEARCH_LIVE_SYN',
  'MOT_SEARCH_SYN','REV_SEARCH_SYN_1','POL_SEARCH_CURRENT_SYN','VISITED_OR_INSTALLED','OBSERVED',
  'E4_FIRST_PARTY_AUTHORITATIVE','FIRST_PARTY','web_analytics_syn','src_search_visit_syn',
  '1',1,8,'2026-08-02T00:07:00Z','2026-08-02T00:07:01Z','LIVE',1,1,'NOT_APPLICABLE'),
 ('EV_SEARCH_INVOKE_SYN','STR_SEARCH_INVOKE_SYN',1,'ASSERT',NULL,'JNY_SEARCH_LIVE_SYN',
  'MOT_SEARCH_SYN','REV_SEARCH_SYN_1','POL_SEARCH_CURRENT_SYN','INVOKED','OBSERVED',
  'E4_FIRST_PARTY_AUTHORITATIVE','FIRST_PARTY','job_ledger_syn','src_search_invoke_syn',
  '1',2,9,'2026-08-03T00:00:00Z','2026-08-03T00:00:01Z','LIVE',1,1,'NOT_APPLICABLE'),
 ('EV_SEARCH_OUTCOME_SYN','STR_SEARCH_OUTCOME_SYN',1,'ASSERT',NULL,'JNY_SEARCH_LIVE_SYN',
  'MOT_SEARCH_SYN','REV_SEARCH_SYN_1','POL_SEARCH_CURRENT_SYN','ACCEPTED_OUTCOME','OBSERVED',
  'E5_CUSTOMER_OR_BANK_AUTHORITATIVE','CUSTOMER','acceptance_ledger_syn','src_search_accept_syn',
  '1',3,10,'2026-08-03T00:10:00Z','2026-08-03T00:10:01Z','LIVE',1,1,'NOT_APPLICABLE'),
 ('EV_SEARCH_CHECKOUT_SYN','STR_SEARCH_CHECKOUT_SYN',1,'ASSERT',NULL,'JNY_SEARCH_LIVE_SYN',
  'MOT_SEARCH_SYN','REV_SEARCH_SYN_1','POL_SEARCH_CURRENT_SYN','CHECKOUT_CREATED','OBSERVED',
  'E4_FIRST_PARTY_AUTHORITATIVE','FIRST_PARTY','commerce_ledger_syn','src_search_checkout_syn',
  '1',4,11,'2026-08-04T00:00:00Z','2026-08-04T00:00:01Z','LIVE',1,1,'NOT_APPLICABLE'),
 ('EV_SEARCH_PAYMENT_SYN','STR_SEARCH_PAYMENT_SYN',1,'ASSERT',NULL,'JNY_SEARCH_LIVE_SYN',
  'MOT_SEARCH_SYN','REV_SEARCH_SYN_1','POL_SEARCH_CURRENT_SYN','PAYMENT_SUCCEEDED','OBSERVED',
  'E3_VERIFIED_PROVIDER_EVENT','PROVIDER_EVENT','payment_webhook_syn','src_search_payment_syn',
  'evt-v1',5,12,'2026-08-04T00:01:00Z','2026-08-04T00:01:02Z','LIVE',1,1,'VERIFIED'),
 ('EV_SEARCH_ORDER_SYN','STR_SEARCH_ORDER_SYN',1,'ASSERT',NULL,'JNY_SEARCH_LIVE_SYN',
  'MOT_SEARCH_SYN','REV_SEARCH_SYN_1','POL_SEARCH_CURRENT_SYN','ORDER_CREATED','OBSERVED',
  'E4_FIRST_PARTY_AUTHORITATIVE','FIRST_PARTY','order_ledger_syn','src_search_order_syn',
  '1',6,13,'2026-08-04T00:02:00Z','2026-08-04T00:02:01Z','LIVE',1,1,'NOT_APPLICABLE'),
 ('EV_SEARCH_ENTITLE_SYN','STR_SEARCH_ENTITLE_SYN',1,'ASSERT',NULL,'JNY_SEARCH_LIVE_SYN',
  'MOT_SEARCH_SYN','REV_SEARCH_SYN_1','POL_SEARCH_CURRENT_SYN','FULFILLED_OR_ENTITLED','OBSERVED',
  'E4_FIRST_PARTY_AUTHORITATIVE','FIRST_PARTY','entitlement_ledger_syn','src_search_entitle_syn',
  '1',7,14,'2026-08-04T00:03:00Z','2026-08-04T00:03:01Z','LIVE',1,1,'NOT_APPLICABLE'),
 ('EV_SEARCH_BANK_SYN','STR_SEARCH_BANK_SYN',1,'ASSERT',NULL,'JNY_SEARCH_LIVE_SYN',
  'MOT_SEARCH_SYN','REV_SEARCH_SYN_1','POL_SEARCH_CURRENT_SYN','BANK_CASH_RECONCILED','OBSERVED',
  'E5_CUSTOMER_OR_BANK_AUTHORITATIVE','BANK','bank_reconciliation_syn','src_search_bank_syn',
  '1',8,15,'2026-08-06T00:00:00Z','2026-08-06T00:05:00Z','LIVE',1,1,'NOT_APPLICABLE'),
 ('EV_SEARCH_RETAIN_SYN','STR_SEARCH_RETAIN_SYN',1,'ASSERT',NULL,'JNY_SEARCH_LIVE_SYN',
  'MOT_SEARCH_SYN','REV_SEARCH_SYN_1','POL_SEARCH_CURRENT_SYN','RETAINED','OBSERVED',
  'E5_CUSTOMER_OR_BANK_AUTHORITATIVE','CUSTOMER','retention_ledger_syn','src_search_retain_syn',
  '1',9,16,'2026-08-14T00:00:00Z','2026-08-14T00:05:00Z','LIVE',1,1,'NOT_APPLICABLE'),
 -- Measured zero at the frozen snapshot; the positive correction arrives later.
 ('EV_SEARCH_ZERO_1_SYN','STR_SEARCH_ZERO_CITE_SYN',1,'ASSERT',NULL,'JNY_SEARCH_ZERO_SYN',
  'MOT_SEARCH_SYN','REV_SEARCH_SYN_1','POL_SEARCH_CURRENT_SYN','CITED_OR_SELECTED','ZERO',
  'E1_MANUAL_OBSERVATION','MANUAL','fixed_probe_syn','src_search_zero_syn',
  'probe-v1',NULL,17,'2026-08-05T00:00:00Z','2026-08-05T00:01:00Z','LIVE',4,0,'NOT_APPLICABLE'),
 ('EV_SEARCH_ZERO_2_SYN','STR_SEARCH_ZERO_CITE_SYN',2,'CORRECT','EV_SEARCH_ZERO_1_SYN',
  'JNY_SEARCH_ZERO_SYN','MOT_SEARCH_SYN','REV_SEARCH_SYN_1','POL_SEARCH_CURRENT_SYN',
  'CITED_OR_SELECTED','OBSERVED','E1_MANUAL_OBSERVATION','MANUAL','fixed_probe_syn',
  'src_search_late_correction_syn','probe-v1-correction',NULL,18,
  '2026-08-09T00:00:00Z','2026-08-16T00:00:00Z','LIVE',4,1,'NOT_APPLICABLE'),
 -- Plugin: public catalog and runtime are parallel; no new-commerce stages apply.
 ('EV_PLUGIN_ELIG_SYN','STR_PLUGIN_ELIG_SYN',1,'ASSERT',NULL,'JNY_PLUGIN_LIVE_SYN',
  'MOT_PLUGIN_SYN','REV_PLUGIN_SYN_2','POL_PLUGIN_CURRENT_SYN','ELIGIBLE','OBSERVED',
  'E2_PROVIDER_CONTROL_PLANE','PROVIDER_CONTROL','plugin_control_syn','src_plugin_elig_syn',
  '2',1,19,'2026-08-02T01:00:00Z','2026-08-02T01:00:02Z','LIVE',1,1,'NOT_APPLICABLE'),
 ('EV_PLUGIN_ART_SYN','STR_PLUGIN_ART_SYN',1,'ASSERT',NULL,'JNY_PLUGIN_LIVE_SYN',
  'MOT_PLUGIN_SYN','REV_PLUGIN_SYN_2','POL_PLUGIN_CURRENT_SYN','ARTIFACT_VALIDATED','OBSERVED',
  'E4_FIRST_PARTY_AUTHORITATIVE','FIRST_PARTY','release_ledger_syn','src_plugin_art_syn',
  '2',2,20,'2026-08-02T01:01:00Z','2026-08-02T01:01:01Z','LIVE',1,1,'NOT_APPLICABLE'),
 ('EV_PLUGIN_SUBMIT_SYN','STR_PLUGIN_SUBMIT_SYN',1,'ASSERT',NULL,'JNY_PLUGIN_LIVE_SYN',
  'MOT_PLUGIN_SYN','REV_PLUGIN_SYN_2','POL_PLUGIN_CURRENT_SYN','SUBMITTED','OBSERVED',
  'E2_PROVIDER_CONTROL_PLANE','PROVIDER_CONTROL','plugin_control_syn','src_plugin_submit_syn',
  '2',3,21,'2026-08-02T01:02:00Z','2026-08-02T01:02:02Z','LIVE',1,1,'NOT_APPLICABLE'),
 ('EV_PLUGIN_APPROVE_SYN','STR_PLUGIN_APPROVE_SYN',1,'ASSERT',NULL,'JNY_PLUGIN_LIVE_SYN',
  'MOT_PLUGIN_SYN','REV_PLUGIN_SYN_2','POL_PLUGIN_CURRENT_SYN','APPROVED','OBSERVED',
  'E2_PROVIDER_CONTROL_PLANE','PROVIDER_CONTROL','plugin_control_syn','src_plugin_approve_syn',
  '2',4,22,'2026-08-02T01:03:00Z','2026-08-02T01:03:02Z','LIVE',1,1,'NOT_APPLICABLE'),
 ('EV_PLUGIN_PUBLISH_SYN','STR_PLUGIN_PUBLISH_SYN',1,'ASSERT',NULL,'JNY_PLUGIN_LIVE_SYN',
  'MOT_PLUGIN_SYN','REV_PLUGIN_SYN_2','POL_PLUGIN_CURRENT_SYN','PUBLISHED_OR_INDEXED','OBSERVED',
  'E2_PROVIDER_CONTROL_PLANE','PROVIDER_CONTROL','plugin_control_syn','src_plugin_publish_syn',
  '2',5,23,'2026-08-05T01:04:00Z','2026-08-05T01:04:02Z','LIVE',1,1,'NOT_APPLICABLE'),
 ('EV_PLUGIN_EXACT_SYN','STR_PLUGIN_EXACT_SYN',1,'ASSERT',NULL,'JNY_PLUGIN_LIVE_SYN',
  'MOT_PLUGIN_SYN','REV_PLUGIN_SYN_2','POL_PLUGIN_CURRENT_SYN','EXACTLY_RESOLVABLE','OBSERVED',
  'E2_PROVIDER_CONTROL_PLANE','PROVIDER_CONTROL','plugin_control_syn','src_plugin_exact_syn',
  '2',6,24,'2026-08-05T01:05:00Z','2026-08-05T01:05:02Z','LIVE',1,1,'NOT_APPLICABLE'),
 ('EV_PLUGIN_INSTALL_SYN','STR_PLUGIN_INSTALL_SYN',1,'ASSERT',NULL,'JNY_PLUGIN_LIVE_SYN',
  'MOT_PLUGIN_SYN','REV_PLUGIN_SYN_2','POL_PLUGIN_CURRENT_SYN','VISITED_OR_INSTALLED','OBSERVED',
  'E2_PROVIDER_CONTROL_PLANE','PROVIDER_CONTROL','plugin_control_syn','src_plugin_install_syn',
  '2',7,25,'2026-08-03T01:06:00Z','2026-08-03T01:06:02Z','LIVE',1,1,'NOT_APPLICABLE'),
 ('EV_PLUGIN_CONNECT_SYN','STR_PLUGIN_CONNECT_SYN',1,'ASSERT',NULL,'JNY_PLUGIN_LIVE_SYN',
  'MOT_PLUGIN_SYN','REV_PLUGIN_SYN_2','POL_PLUGIN_CURRENT_SYN','CONNECTED','OBSERVED',
  'E4_FIRST_PARTY_AUTHORITATIVE','FIRST_PARTY','oauth_ledger_syn','src_plugin_connect_syn',
  '2',8,26,'2026-08-03T01:07:00Z','2026-08-03T01:07:01Z','LIVE',1,1,'NOT_APPLICABLE'),
 ('EV_PLUGIN_INVOKE_SYN','STR_PLUGIN_INVOKE_SYN',1,'ASSERT',NULL,'JNY_PLUGIN_LIVE_SYN',
  'MOT_PLUGIN_SYN','REV_PLUGIN_SYN_2','POL_PLUGIN_CURRENT_SYN','INVOKED','OBSERVED',
  'E4_FIRST_PARTY_AUTHORITATIVE','FIRST_PARTY','mcp_gateway_syn','src_plugin_invoke_syn',
  '2',9,27,'2026-08-04T01:08:00Z','2026-08-04T01:08:01Z','LIVE',1,1,'NOT_APPLICABLE'),
 ('EV_PLUGIN_OUTCOME_SYN','STR_PLUGIN_OUTCOME_SYN',1,'ASSERT',NULL,'JNY_PLUGIN_LIVE_SYN',
  'MOT_PLUGIN_SYN','REV_PLUGIN_SYN_2','POL_PLUGIN_CURRENT_SYN','ACCEPTED_OUTCOME','OBSERVED',
  'E4_FIRST_PARTY_AUTHORITATIVE','FIRST_PARTY','outcome_ledger_syn','src_plugin_outcome_syn',
  '2',10,28,'2026-08-04T01:09:00Z','2026-08-04T01:09:01Z','LIVE',1,1,'NOT_APPLICABLE'),
 -- MCP preview is sandbox-only; publication does not invent downstream installs.
 ('EV_MCP_ELIG_SYN','STR_MCP_ELIG_SYN',1,'ASSERT',NULL,'JNY_MCP_SANDBOX_SYN',
  'MOT_MCP_SYN','REV_MCP_SYN_1','POL_MCP_PREVIEW_SYN','ELIGIBLE','UNKNOWN',
  'E2_PROVIDER_CONTROL_PLANE','PROVIDER_CONTROL','registry_control_syn','src_mcp_elig_syn',
  '1',1,29,'2026-08-02T02:00:00Z','2026-08-02T02:00:02Z','SANDBOX',1,0,'NOT_APPLICABLE'),
 ('EV_MCP_ART_SYN','STR_MCP_ART_SYN',1,'ASSERT',NULL,'JNY_MCP_SANDBOX_SYN',
  'MOT_MCP_SYN','REV_MCP_SYN_1','POL_MCP_PREVIEW_SYN','ARTIFACT_VALIDATED','OBSERVED',
  'E4_FIRST_PARTY_AUTHORITATIVE','FIRST_PARTY','release_ledger_syn','src_mcp_art_syn',
  '1',2,30,'2026-08-02T02:01:00Z','2026-08-02T02:01:01Z','SANDBOX',1,1,'NOT_APPLICABLE'),
 ('EV_MCP_PUBLISH_SYN','STR_MCP_PUBLISH_SYN',1,'ASSERT',NULL,'JNY_MCP_SANDBOX_SYN',
  'MOT_MCP_SYN','REV_MCP_SYN_1','POL_MCP_PREVIEW_SYN','PUBLISHED_OR_INDEXED','OBSERVED',
  'E2_PROVIDER_CONTROL_PLANE','PROVIDER_CONTROL','registry_control_syn','src_mcp_publish_syn',
  '1',3,31,'2026-08-02T02:02:00Z','2026-08-02T02:02:02Z','SANDBOX',1,1,'NOT_APPLICABLE'),
 ('EV_MCP_EXACT_SYN','STR_MCP_EXACT_SYN',1,'ASSERT',NULL,'JNY_MCP_SANDBOX_SYN',
  'MOT_MCP_SYN','REV_MCP_SYN_1','POL_MCP_PREVIEW_SYN','EXACTLY_RESOLVABLE','OBSERVED',
  'E2_PROVIDER_CONTROL_PLANE','PROVIDER_CONTROL','registry_control_syn','src_mcp_exact_syn',
  '1',4,32,'2026-08-02T02:03:00Z','2026-08-02T02:03:02Z','SANDBOX',1,1,'NOT_APPLICABLE'),
 -- Agentic commerce can be exercised in sandbox while current live scope fails.
 ('EV_AGENTIC_ELIG_SYN','STR_AGENTIC_ELIG_SYN',1,'ASSERT',NULL,'JNY_AGENTIC_SANDBOX_SYN',
  'MOT_AGENTIC_SYN','REV_AGENTIC_SYN_1','POL_AGENTIC_CURRENT_DENY_SYN','ELIGIBLE','FAILED',
  'E2_PROVIDER_CONTROL_PLANE','PROVIDER_CONTROL','agentic_control_syn','src_agentic_elig_syn',
  '1',1,33,'2026-08-02T03:00:00Z','2026-08-02T03:00:02Z','SANDBOX',1,0,'NOT_APPLICABLE'),
 ('EV_AGENTIC_ART_SYN','STR_AGENTIC_ART_SYN',1,'ASSERT',NULL,'JNY_AGENTIC_SANDBOX_SYN',
  'MOT_AGENTIC_SYN','REV_AGENTIC_SYN_1','POL_AGENTIC_CURRENT_DENY_SYN','ARTIFACT_VALIDATED','OBSERVED',
  'E4_FIRST_PARTY_AUTHORITATIVE','FIRST_PARTY','release_ledger_syn','src_agentic_art_syn',
  '1',2,34,'2026-08-02T03:01:00Z','2026-08-02T03:01:01Z','SANDBOX',1,1,'NOT_APPLICABLE'),
 ('EV_AGENTIC_SELECT_SYN','STR_AGENTIC_SELECT_SYN',1,'ASSERT',NULL,'JNY_AGENTIC_SANDBOX_SYN',
  'MOT_AGENTIC_SYN','REV_AGENTIC_SYN_1','POL_AGENTIC_CURRENT_DENY_SYN','CITED_OR_SELECTED','OBSERVED',
  'E3_VERIFIED_PROVIDER_EVENT','PROVIDER_EVENT','agentic_webhook_syn','src_agentic_select_syn',
  '1',3,35,'2026-08-02T03:02:00Z','2026-08-02T03:02:02Z','SANDBOX',1,1,'VERIFIED'),
 ('EV_AGENTIC_CHECKOUT_SYN','STR_AGENTIC_CHECKOUT_SYN',1,'ASSERT',NULL,'JNY_AGENTIC_SANDBOX_SYN',
  'MOT_AGENTIC_SYN','REV_AGENTIC_SYN_1','POL_AGENTIC_CURRENT_DENY_SYN','CHECKOUT_CREATED','OBSERVED',
  'E4_FIRST_PARTY_AUTHORITATIVE','FIRST_PARTY','commerce_ledger_syn','src_agentic_checkout_syn',
  '1',4,36,'2026-08-03T03:00:00Z','2026-08-03T03:00:01Z','SANDBOX',1,1,'NOT_APPLICABLE'),
 ('EV_AGENTIC_PAYMENT_SYN','STR_AGENTIC_PAYMENT_SYN',1,'ASSERT',NULL,'JNY_AGENTIC_SANDBOX_SYN',
  'MOT_AGENTIC_SYN','REV_AGENTIC_SYN_1','POL_AGENTIC_CURRENT_DENY_SYN','PAYMENT_SUCCEEDED','OBSERVED',
  'E3_VERIFIED_PROVIDER_EVENT','PROVIDER_EVENT','agentic_webhook_syn','src_agentic_payment_syn',
  '1',5,37,'2026-08-03T03:01:00Z','2026-08-03T03:01:02Z','SANDBOX',1,1,'VERIFIED'),
 ('EV_AGENTIC_ORDER_SYN','STR_AGENTIC_ORDER_SYN',1,'ASSERT',NULL,'JNY_AGENTIC_SANDBOX_SYN',
  'MOT_AGENTIC_SYN','REV_AGENTIC_SYN_1','POL_AGENTIC_CURRENT_DENY_SYN','ORDER_CREATED','OBSERVED',
  'E4_FIRST_PARTY_AUTHORITATIVE','FIRST_PARTY','order_ledger_syn','src_agentic_order_syn',
  '1',6,38,'2026-08-03T03:02:00Z','2026-08-03T03:02:01Z','SANDBOX',1,1,'NOT_APPLICABLE'),
 ('EV_AGENTIC_FULFILL_SYN','STR_AGENTIC_FULFILL_SYN',1,'ASSERT',NULL,'JNY_AGENTIC_SANDBOX_SYN',
  'MOT_AGENTIC_SYN','REV_AGENTIC_SYN_1','POL_AGENTIC_CURRENT_DENY_SYN','FULFILLED_OR_ENTITLED','OBSERVED',
  'E4_FIRST_PARTY_AUTHORITATIVE','FIRST_PARTY','fulfillment_ledger_syn','src_agentic_fulfill_syn',
  '1',7,39,'2026-08-03T03:03:00Z','2026-08-03T03:03:01Z','SANDBOX',1,1,'NOT_APPLICABLE');

INSERT INTO stage_evidence_event (
  stage_event_id,evidence_stream_id,event_revision,correction_kind,
  supersedes_event_id,journey_id,motion_contract_id,artifact_revision_id,
  policy_snapshot_id,canonical_stage,observation_state,evidence_class,
  evidence_source,source_system,source_event_id,source_revision,causal_sequence,
  ingest_sequence,occurred_at,provider_created_at,recorded_at,environment,
  denominator_count,result_count,signature_status,payload_ref_hash,data_class,
  reviewed_by_role,reviewed_at
)
SELECT stage_event_id,evidence_stream_id,event_revision,correction_kind,
  supersedes_event_id,journey_id,motion_contract_id,artifact_revision_id,
  policy_snapshot_id,canonical_stage,observation_state,evidence_class,
  evidence_source,source_system,source_event_id,source_revision,causal_sequence,
  ingest_sequence,occurred_at,
  CASE WHEN evidence_source IN ('PROVIDER_CONTROL','PROVIDER_EVENT') THEN occurred_at ELSE NULL END,
  recorded_at,environment,denominator_count,result_count,signature_status,
  lower(hex(zeroblob(32))),'NO_RAW_PII_OPAQUE_REF','HUMAN_REVIEWER',recorded_at
FROM stage_fixture
ORDER BY ingest_sequence;

DROP TABLE stage_fixture;

INSERT INTO commerce_chain VALUES
 ('CHAIN_SEARCH_SYN','JNY_SEARCH_LIVE_SYN','MOT_SEARCH_SYN','REV_SEARCH_SYN_1',
  'ELIG_SEARCH_SYN','LIVE','JP','WEB_SAAS','CHECKOUT_SEARCH_SYN','PAYMENT_SYN',
  'IDEMP_SEARCH_SYN','checkout-schema-syn-v1','price-inventory-syn-1','JPY',
  12000000000,'PASS','PASS','COMPLETED','PAYMENT_SEARCH_SYN','SUCCEEDED',
  'provider-payment-event-syn','VERIFIED','2026-08-04T00:01:03Z','ORDER_SEARCH_SYN',
  'CREATED','OUTBOX_SEARCH_SYN','SAAS','READ_BACK_SAME_IDEMPOTENCY_KEY_NO_AUTO_RETRY',
  '2026-08-04T00:00:00Z','2026-08-04T00:02:01Z','NO_RAW_PII_OPAQUE_REF'),
 ('CHAIN_AGENTIC_SANDBOX_SYN','JNY_AGENTIC_SANDBOX_SYN','MOT_AGENTIC_SYN',
  'REV_AGENTIC_SYN_1','ELIG_AGENTIC_SYN','SANDBOX','JP','PHYSICAL_GOOD',
  'CHECKOUT_AGENTIC_SYN','PAYMENT_SYN','IDEMP_AGENTIC_SYN','ucp-syn-v1',
  'price-inventory-syn-2','JPY',5000000000,'PASS','PASS','COMPLETED',
  'PAYMENT_AGENTIC_SYN','SUCCEEDED','provider-agentic-payment-event-syn','VERIFIED',
  '2026-08-03T03:01:03Z','ORDER_AGENTIC_SYN','CREATED','OUTBOX_AGENTIC_SYN',
  'PHYSICAL','READ_BACK_SAME_IDEMPOTENCY_KEY_NO_AUTO_RETRY',
  '2026-08-03T03:00:00Z','2026-08-03T03:02:01Z','NO_RAW_PII_OPAQUE_REF');

INSERT INTO fulfillment_event VALUES
 ('FUL_SEARCH_SYN','FUL_STREAM_SEARCH_SYN',1,NULL,'CHAIN_SEARCH_SYN','ENTITLEMENT',
  'GRANTED','E4_FIRST_PARTY_AUTHORITATIVE','entitlement_ledger_syn',
  'fulfillment-search-source-syn','2026-08-04T00:03:00Z','2026-08-04T00:03:01Z',
  'LIVE',lower(hex(zeroblob(32)))),
 ('FUL_AGENTIC_SYN','FUL_STREAM_AGENTIC_SYN',1,NULL,'CHAIN_AGENTIC_SANDBOX_SYN',
  'FULFILLMENT','DELIVERED','E4_FIRST_PARTY_AUTHORITATIVE','fulfillment_ledger_syn',
  'fulfillment-agentic-source-syn','2026-08-03T03:03:00Z','2026-08-03T03:03:01Z',
  'SANDBOX',lower(hex(zeroblob(32))));

INSERT INTO cash_bridge_line VALUES
 ('CASH_SEARCH_GROSS_SYN','CHAIN_SEARCH_SYN','GROSS_CONSIDERATION',12000000000,'JPY',
  'PROVIDER_EVENT','cash-search-gross-source-syn','2026-08-04T00:01:00Z',
  '2026-08-04T00:01:02Z','LIVE',lower(hex(zeroblob(32)))),
 ('CASH_SEARCH_TAX_SYN','CHAIN_SEARCH_SYN','TAX_HELD',1000000000,'JPY',
  'FIRST_PARTY','cash-search-tax-source-syn','2026-08-04T00:02:00Z',
  '2026-08-04T00:02:01Z','LIVE',lower(hex(zeroblob(32)))),
 ('CASH_SEARCH_REFUND_SYN','CHAIN_SEARCH_SYN','REFUND',1000000000,'JPY',
  'PROVIDER_EVENT','cash-search-refund-source-syn','2026-08-05T00:00:00Z',
  '2026-08-05T00:00:02Z','LIVE',lower(hex(zeroblob(32)))),
 ('CASH_SEARCH_FEE_SYN','CHAIN_SEARCH_SYN','PROVIDER_FEE',400000000,'JPY',
  'PROVIDER_EVENT','cash-search-fee-source-syn','2026-08-05T00:01:00Z',
  '2026-08-05T00:01:02Z','LIVE',lower(hex(zeroblob(32)))),
 ('CASH_AGENTIC_GROSS_SYN','CHAIN_AGENTIC_SANDBOX_SYN','GROSS_CONSIDERATION',
  5000000000,'JPY','PROVIDER_EVENT','cash-agentic-gross-source-syn',
  '2026-08-03T03:01:00Z','2026-08-03T03:01:02Z','SANDBOX',
  lower(hex(zeroblob(32))));

INSERT INTO payout_event VALUES
 ('PAYOUT_SEARCH_SYN','CHAIN_SEARCH_SYN','PAYOUT_REF_SEARCH_SYN','PAID',9600000000,
  'JPY','payout-search-source-syn','VERIFIED','2026-08-06T00:00:00Z',
  '2026-08-06T00:00:02Z','LIVE');

INSERT INTO bank_reconciliation VALUES
 ('BANKREC_SEARCH_SYN','CHAIN_SEARCH_SYN','PAYOUT_SEARCH_SYN',
  '1111111111111111111111111111111111111111111111111111111111111111',
  9600000000,'JPY','MATCHED',0,'E5_CUSTOMER_OR_BANK_AUTHORITATIVE','BANK',
  '2026-08-06T00:00:00Z','2026-08-06T00:05:00Z','LIVE');

INSERT INTO variable_cost_ledger VALUES
 ('COST_SEARCH_COGS_SYN','MOT_SEARCH_SYN','CHAIN_SEARCH_SYN','COGS',1000000000,
  'JPY','LIVE','cost-search-cogs-source-syn','2026-08-04T00:03:00Z',
  '2026-08-04T00:03:01Z'),
 ('COST_SEARCH_AI_SYN','MOT_SEARCH_SYN','CHAIN_SEARCH_SYN','AI_STORAGE_DELIVERY',
  200000000,'JPY','LIVE','cost-search-ai-source-syn','2026-08-04T00:03:00Z',
  '2026-08-04T00:03:01Z'),
 ('COST_SEARCH_CHANNEL_SYN','MOT_SEARCH_SYN','CHAIN_SEARCH_SYN','CHANNEL',500000000,
  'JPY','LIVE','cost-search-channel-source-syn','2026-08-02T00:00:00Z',
  '2026-08-02T00:00:01Z'),
 ('COST_PLUGIN_CHANNEL_SYN','MOT_PLUGIN_SYN',NULL,'CHANNEL',300000000,
  'JPY','LIVE','cost-plugin-channel-source-syn','2026-08-02T01:00:00Z',
  '2026-08-02T01:00:01Z'),
 ('COST_AGENTIC_TEST_SYN','MOT_AGENTIC_SYN','CHAIN_AGENTIC_SANDBOX_SYN','CHANNEL',
  100000000,'JPY','SANDBOX','cost-agentic-test-source-syn','2026-08-02T03:00:00Z',
  '2026-08-02T03:00:01Z');

INSERT INTO founder_time_ledger VALUES
 ('TIME_SEARCH_SUPPORT_SYN','MOT_SEARCH_SYN','CHAIN_SEARCH_SYN','SUPPORT',20,'LIVE',
  'time-search-support-source-syn','2026-08-05T00:00:00Z','2026-08-05T00:00:01Z'),
 ('TIME_SEARCH_RESCUE_SYN','MOT_SEARCH_SYN','CHAIN_SEARCH_SYN','FOUNDER_RESCUE',10,'LIVE',
  'time-search-rescue-source-syn','2026-08-05T00:10:00Z','2026-08-05T00:10:01Z'),
 ('TIME_PLUGIN_DISTRIBUTION_SYN','MOT_PLUGIN_SYN',NULL,'DISTRIBUTION',60,'LIVE',
  'time-plugin-source-syn','2026-08-02T01:00:00Z','2026-08-02T01:00:01Z'),
 ('TIME_AGENTIC_TEST_SYN','MOT_AGENTIC_SYN','CHAIN_AGENTIC_SANDBOX_SYN','DISTRIBUTION',
  30,'SANDBOX','time-agentic-test-source-syn','2026-08-02T03:00:00Z',
  '2026-08-02T03:00:01Z');

INSERT INTO retention_observation VALUES
 ('RET_SEARCH_SYN','CHAIN_SEARCH_SYN','MATURE','RETAINED',
  'E5_CUSTOMER_OR_BANK_AUTHORITATIVE','2026-08-13T23:59:59Z',
  '2026-08-13T23:59:59Z','2026-08-14T00:00:00Z','2026-08-14T00:05:00Z',
  'LIVE','customer-retention-evidence-syn'),
 ('RET_AGENTIC_SYN','CHAIN_AGENTIC_SANDBOX_SYN','PROVISIONAL','UNKNOWN',
  'E4_FIRST_PARTY_AUTHORITATIVE','2026-08-30T00:00:00Z','2026-09-30T00:00:00Z',
  '2026-08-03T03:03:00Z','2026-08-03T03:03:01Z','SANDBOX',
  'sandbox-no-retention-claim-syn');

-- -----------------------------------------------------------------------------
-- Fail-closed current-state, decision, and economics views
-- -----------------------------------------------------------------------------

CREATE VIEW v_policy_scope_status AS
SELECT m.motion_contract_id,m.motion_type,m.region_code,m.product_type,
       e.current_gate,
       max(CASE WHEN p.snapshot_status='CURRENT' AND l.link_role='EXECUTION_SCOPE'
                     AND p.eligibility_effect='ALLOWS'
                     AND p.applies_region IN (m.region_code,'ALL')
                     AND p.applies_product IN (m.product_type,'ALL')
                THEN 1 ELSE 0 END) AS current_scope_allows,
       max(CASE WHEN p.snapshot_status='CURRENT' AND l.link_role='EXECUTION_SCOPE'
                     AND p.eligibility_effect='DENIES'
                THEN 1 ELSE 0 END) AS current_scope_denies,
       max(CASE WHEN p.snapshot_status='PREVIEW' THEN 1 ELSE 0 END) AS has_preview,
       max(CASE WHEN p.snapshot_status='UPCOMING' THEN 1 ELSE 0 END) AS has_upcoming,
       max(CASE WHEN p.snapshot_status='CONFLICTING' THEN 1 ELSE 0 END) AS has_conflict
FROM motion_contract m
JOIN eligibility_review e ON e.motion_contract_id=m.motion_contract_id
LEFT JOIN motion_policy_link l ON l.motion_contract_id=m.motion_contract_id
LEFT JOIN policy_snapshot p ON p.policy_snapshot_id=l.policy_snapshot_id
GROUP BY m.motion_contract_id,m.motion_type,m.region_code,m.product_type,e.current_gate;

CREATE VIEW v_effective_stage_event AS
SELECT e.*
FROM stage_evidence_event e
JOIN motion_contract m ON m.motion_contract_id=e.motion_contract_id
WHERE datetime(e.occurred_at)<=datetime(m.outcome_cutoff_at)
  AND datetime(e.recorded_at)<=datetime(m.snapshot_as_of)
  AND NOT EXISTS (
    SELECT 1 FROM stage_evidence_event n
    WHERE n.evidence_stream_id=e.evidence_stream_id
      AND n.event_revision>e.event_revision
      AND datetime(n.occurred_at)<=datetime(m.outcome_cutoff_at)
      AND datetime(n.recorded_at)<=datetime(m.snapshot_as_of)
  )
  AND e.observation_state<>'RETRACTED';

CREATE VIEW v_stage_event_timing_inventory AS
SELECT e.stage_event_id,e.evidence_stream_id,e.event_revision,e.motion_contract_id,
       e.journey_id,e.canonical_stage,e.environment,e.observation_state,
       CASE
         WHEN datetime(e.occurred_at)>datetime(m.outcome_cutoff_at) THEN 'POST_CUTOFF'
         WHEN datetime(e.recorded_at)>datetime(m.snapshot_as_of) THEN 'LATE_INGEST'
         WHEN EXISTS (
           SELECT 1 FROM stage_evidence_event n
           WHERE n.evidence_stream_id=e.evidence_stream_id
             AND n.event_revision>e.event_revision
             AND datetime(n.occurred_at)<=datetime(m.outcome_cutoff_at)
             AND datetime(n.recorded_at)<=datetime(m.snapshot_as_of)
         ) THEN 'SUPERSEDED_AS_OF'
         WHEN e.observation_state='RETRACTED' THEN 'RETRACTED_AS_OF'
         ELSE 'EFFECTIVE_AS_OF'
       END AS timing_state
FROM stage_evidence_event e
JOIN motion_contract m ON m.motion_contract_id=e.motion_contract_id;

CREATE VIEW v_journey_stage_snapshot AS
WITH stage_flags AS (
  SELECT j.journey_id,j.motion_contract_id,j.environment,r.canonical_stage,
         r.applicability,r.minimum_evidence_class,d.display_order,
         max(CASE WHEN e.observation_state='OBSERVED' THEN 1 ELSE 0 END) AS has_observed,
         max(CASE WHEN e.observation_state='FAILED' THEN 1 ELSE 0 END) AS has_failed,
         max(CASE WHEN e.observation_state='ZERO' THEN 1 ELSE 0 END) AS has_zero,
         max(CASE WHEN e.observation_state='UNKNOWN' THEN 1 ELSE 0 END) AS has_unknown,
         count(e.stage_event_id) AS effective_event_count
  FROM journey j
  JOIN route_stage_contract r ON r.motion_contract_id=j.motion_contract_id
  JOIN canonical_stage_dim d ON d.canonical_stage=r.canonical_stage
  LEFT JOIN v_effective_stage_event e
    ON e.journey_id=j.journey_id AND e.canonical_stage=r.canonical_stage
  GROUP BY j.journey_id,j.motion_contract_id,j.environment,r.canonical_stage,
           r.applicability,r.minimum_evidence_class,d.display_order
)
SELECT *,
       CASE
         WHEN applicability='NA' THEN 'N/A'
         WHEN has_observed=1 THEN 'OBSERVED'
         WHEN has_failed=1 THEN 'FAILED'
         WHEN has_zero=1 THEN '0'
         ELSE 'UNKNOWN'
       END AS snapshot_state
FROM stage_flags;

CREATE VIEW v_first_missing_required_stage AS
SELECT s.journey_id,s.motion_contract_id,s.environment,s.canonical_stage,
       s.display_order,s.snapshot_state
FROM v_journey_stage_snapshot s
WHERE s.applicability='REQUIRED' AND s.snapshot_state<>'OBSERVED'
  AND NOT EXISTS (
    SELECT 1 FROM v_journey_stage_snapshot p
    WHERE p.journey_id=s.journey_id AND p.applicability='REQUIRED'
      AND p.snapshot_state<>'OBSERVED' AND p.display_order<s.display_order
  );

CREATE VIEW v_motion_stage_summary AS
SELECT motion_contract_id,environment,canonical_stage,applicability,
       count(*) AS journey_count,
       sum(CASE WHEN snapshot_state='OBSERVED' THEN 1 ELSE 0 END) AS observed_count,
       sum(CASE WHEN snapshot_state='FAILED' THEN 1 ELSE 0 END) AS failed_count,
       sum(CASE WHEN snapshot_state='UNKNOWN' THEN 1 ELSE 0 END) AS unknown_count,
       sum(CASE WHEN snapshot_state='0' THEN 1 ELSE 0 END) AS measured_zero_count,
       sum(CASE WHEN snapshot_state='N/A' THEN 1 ELSE 0 END) AS not_applicable_count
FROM v_journey_stage_snapshot
GROUP BY motion_contract_id,environment,canonical_stage,applicability;

CREATE VIEW v_journey_decision AS
SELECT j.journey_id,j.motion_contract_id,j.environment,e.current_gate,
       f.canonical_stage AS first_missing_required_stage,
       f.snapshot_state AS first_missing_state,
       CASE
         WHEN e.current_gate='STOP' THEN 'STOP'
         WHEN e.current_gate='NOT_ELIGIBLE' THEN 'NOT_ELIGIBLE'
         WHEN e.current_gate='PAUSE' THEN 'PAUSE'
         WHEN e.current_gate='UNKNOWN' THEN 'UNKNOWN'
         WHEN j.environment='LIVE' AND e.current_gate<>'READY_LIVE' THEN 'STOP'
         WHEN f.canonical_stage IS NOT NULL THEN 'COLLECT_EVIDENCE'
         WHEN j.environment='LIVE' THEN 'READY_LIVE_EVIDENCE_COMPLETE'
         ELSE 'READY_TEST_EVIDENCE_COMPLETE'
       END AS derived_decision
FROM journey j
JOIN eligibility_review e ON e.eligibility_review_id=j.eligibility_review_id
LEFT JOIN v_first_missing_required_stage f ON f.journey_id=j.journey_id;

CREATE VIEW v_commercial_owner_dedup AS
SELECT commercial_owner_key,count(*) AS owner_claim_count,
       min(motion_contract_id) AS owning_motion_contract_id,
       min(journey_id) AS owning_journey_id
FROM journey
GROUP BY commercial_owner_key;

CREATE VIEW v_chain_economics AS
WITH cash AS (
  SELECT c.commerce_chain_id,
         sum(CASE WHEN l.line_type='GROSS_CONSIDERATION' THEN l.amount_micros ELSE 0 END) AS gross_micros,
         sum(CASE WHEN l.line_type='GROSS_CONSIDERATION' THEN l.amount_micros
                  ELSE -l.amount_micros END) AS processor_net_micros,
         sum(CASE WHEN l.line_type='REFUND' THEN l.amount_micros ELSE 0 END) AS refund_micros,
         sum(CASE WHEN l.line_type IN ('DISPUTE_LOSS','DISPUTE_FEE')
                  THEN l.amount_micros ELSE 0 END) AS dispute_micros,
         sum(CASE WHEN l.line_type IN ('PROVIDER_FEE','FX_PAYOUT_FEE')
                  THEN l.amount_micros ELSE 0 END) AS provider_fee_micros
  FROM commerce_chain c
  LEFT JOIN cash_bridge_line l ON l.commerce_chain_id=c.commerce_chain_id
                              AND l.environment='LIVE'
  GROUP BY c.commerce_chain_id
), payout AS (
  SELECT commerce_chain_id,sum(CASE WHEN environment='LIVE' AND payout_state='PAID'
                                     THEN amount_micros ELSE 0 END) AS paid_payout_micros
  FROM payout_event GROUP BY commerce_chain_id
), bank AS (
  SELECT commerce_chain_id,sum(CASE WHEN reconciliation_state='MATCHED'
                                     THEN bank_amount_micros ELSE 0 END) AS bank_cash_micros
  FROM bank_reconciliation GROUP BY commerce_chain_id
), costs AS (
  SELECT commerce_chain_id,sum(amount_micros) AS variable_cost_micros
  FROM variable_cost_ledger WHERE environment='LIVE' AND commerce_chain_id IS NOT NULL
  GROUP BY commerce_chain_id
), time_cost AS (
  SELECT t.commerce_chain_id,sum(t.minutes) AS founder_minutes,
         (sum(t.minutes)*s.founder_hourly_value_micros)/60 AS founder_time_cost_micros
  FROM founder_time_ledger t CROSS JOIN model_setting s
  WHERE t.environment='LIVE' AND t.commerce_chain_id IS NOT NULL
  GROUP BY t.commerce_chain_id
)
SELECT c.commerce_chain_id,c.journey_id,c.motion_contract_id,c.environment,c.currency,
       coalesce(ca.gross_micros,0) AS gross_micros,
       coalesce(ca.refund_micros,0) AS refund_micros,
       coalesce(ca.dispute_micros,0) AS dispute_micros,
       coalesce(ca.provider_fee_micros,0) AS provider_fee_micros,
       coalesce(ca.processor_net_micros,0) AS processor_net_micros,
       coalesce(p.paid_payout_micros,0) AS paid_payout_micros,
       coalesce(b.bank_cash_micros,0) AS bank_cash_micros,
       coalesce(k.variable_cost_micros,0) AS variable_cost_micros,
       coalesce(t.founder_minutes,0) AS founder_minutes,
       coalesce(t.founder_time_cost_micros,0) AS founder_time_cost_micros,
       r.maturity_state,r.retention_state,
       CASE WHEN c.environment='LIVE' AND r.maturity_state='MATURE'
                  AND r.retention_state='RETAINED'
            THEN coalesce(b.bank_cash_micros,0)-coalesce(k.variable_cost_micros,0)
                 -coalesce(t.founder_time_cost_micros,0)
            ELSE NULL END AS retained_net_settled_contribution_micros,
       CASE WHEN c.environment<>'LIVE' THEN 'TEST_ONLY'
            WHEN r.maturity_state<>'MATURE' THEN 'PROVISIONAL'
            WHEN coalesce(b.bank_cash_micros,0)=0 THEN 'UNRECONCILED'
            WHEN r.retention_state='RETAINED' THEN 'MATURE_RETAINED'
            ELSE 'MATURE_NOT_RETAINED' END AS economics_state
FROM commerce_chain c
LEFT JOIN cash ca ON ca.commerce_chain_id=c.commerce_chain_id
LEFT JOIN payout p ON p.commerce_chain_id=c.commerce_chain_id
LEFT JOIN bank b ON b.commerce_chain_id=c.commerce_chain_id
LEFT JOIN costs k ON k.commerce_chain_id=c.commerce_chain_id
LEFT JOIN time_cost t ON t.commerce_chain_id=c.commerce_chain_id
LEFT JOIN retention_observation r ON r.commerce_chain_id=c.commerce_chain_id;

CREATE VIEW v_motion_economics AS
WITH chain_rollup AS (
  SELECT motion_contract_id,
         sum(CASE WHEN environment='LIVE' THEN bank_cash_micros ELSE 0 END) AS bank_cash_micros,
         sum(CASE WHEN environment='LIVE' THEN retained_net_settled_contribution_micros ELSE 0 END)
           AS retained_contribution_micros,
         sum(CASE WHEN environment='LIVE' AND economics_state='MATURE_RETAINED'
                  THEN 1 ELSE 0 END) AS mature_retained_customers
  FROM v_chain_economics GROUP BY motion_contract_id
), cost_rollup AS (
  SELECT motion_contract_id,sum(amount_micros) AS live_cash_cost_micros
  FROM variable_cost_ledger WHERE environment='LIVE' GROUP BY motion_contract_id
)
SELECT m.motion_contract_id,m.motion_type,m.currency,
       coalesce(c.bank_cash_micros,0) AS bank_cash_micros,
       coalesce(c.retained_contribution_micros,0) AS retained_contribution_micros,
       coalesce(c.mature_retained_customers,0) AS mature_retained_customers,
       coalesce(k.live_cash_cost_micros,0) AS live_cash_cost_micros,
       CASE WHEN coalesce(c.mature_retained_customers,0)=0 THEN NULL
            ELSE coalesce(k.live_cash_cost_micros,0)/c.mature_retained_customers END
         AS cash_cost_per_retained_customer_micros,
       CASE WHEN coalesce(c.mature_retained_customers,0)=0
            THEN 'UNDEFINED_ZERO_RETAINED_CUSTOMERS'
            ELSE 'DEFINED_MATURE_COHORT' END AS ratio_state
FROM motion_contract m
LEFT JOIN chain_rollup c ON c.motion_contract_id=m.motion_contract_id
LEFT JOIN cost_rollup k ON k.motion_contract_id=m.motion_contract_id;

-- -----------------------------------------------------------------------------
-- Executable data-quality assertions. Any failing error assertion aborts setup.
-- -----------------------------------------------------------------------------

CREATE TEMP TABLE dq_assertion_result (
  check_id TEXT PRIMARY KEY,
  check_name TEXT NOT NULL UNIQUE,
  failure_count INTEGER NOT NULL,
  expectation TEXT NOT NULL
) STRICT;

INSERT INTO dq_assertion_result VALUES
 ('DQ01','schema_registry_exact',
  (SELECT count(*) FROM schema_registry
   WHERE schema_name<>'ai_distribution_evidence' OR schema_version<>'1.0.0'),
  'The exact schema identity and version are installed'),
 ('DQ02','six_evidence_classes',
  abs((SELECT count(*) FROM evidence_class_dim)-6),
  'E0 through E5 exist exactly once'),
 ('DQ03','eighteen_canonical_stages',
  abs((SELECT count(*) FROM canonical_stage_dim)-18),
  'The route vocabulary has 18 independent stages'),
 ('DQ04','four_required_motion_types',
  abs((SELECT count(DISTINCT motion_type) FROM motion_contract)-4),
  'Search, plugin, registry, and agentic commerce are modeled'),
 ('DQ05','one_contract_per_fixture_motion_type',
  (SELECT count(*) FROM (SELECT motion_type FROM motion_contract GROUP BY motion_type HAVING count(*)<>1)),
  'Each synthetic motion type has one exact contract'),
 ('DQ06','policy_statuses_are_separate',
  4-(SELECT count(DISTINCT snapshot_status) FROM policy_snapshot
     WHERE snapshot_status IN ('CURRENT','PREVIEW','UPCOMING','CONFLICTING')),
  'CURRENT, PREVIEW, UPCOMING, and CONFLICTING all remain explicit'),
 ('DQ07','conflict_has_two_sources',
  CASE WHEN (SELECT count(*) FROM policy_snapshot
             WHERE conflict_group='CONFLICT_MCP_STATUS_SYN')=2 THEN 0 ELSE 1 END,
  'A conflicting claim retains both source snapshots'),
 ('DQ08','upcoming_not_yet_effective',
  (SELECT count(*) FROM policy_snapshot
   WHERE snapshot_status='UPCOMING'
     AND datetime(effective_from)<=datetime(source_captured_at)),
  'Upcoming scope cannot masquerade as current scope'),
 ('DQ09','ready_live_has_current_allowing_scope',
  (SELECT count(*) FROM v_policy_scope_status
   WHERE current_gate='READY_LIVE' AND current_scope_allows<>1),
  'Every READY_LIVE review is backed by an exact CURRENT ALLOWS snapshot'),
 ('DQ10','agentic_current_scope_denied',
  CASE WHEN EXISTS (SELECT 1 FROM v_policy_scope_status
                    WHERE motion_contract_id='MOT_AGENTIC_SYN'
                      AND current_scope_denies=1 AND has_upcoming=1)
       THEN 0 ELSE 1 END,
  'Current denial and future option are not merged'),
 ('DQ11','agentic_fail_closed_gate',
  CASE WHEN EXISTS (SELECT 1 FROM eligibility_review
                    WHERE motion_contract_id='MOT_AGENTIC_SYN'
                      AND current_gate='NOT_ELIGIBLE' AND region_status='FAIL')
       THEN 0 ELSE 1 END,
  'The synthetic JP agentic scope is not LIVE-ready'),
 ('DQ12','registry_preview_test_only_gate',
  CASE WHEN EXISTS (SELECT 1 FROM eligibility_review
                    WHERE motion_contract_id='MOT_MCP_SYN' AND current_gate='READY_TEST')
       THEN 0 ELSE 1 END,
  'Preview/conflicting registry scope is restricted to test'),
 ('DQ13','all_route_stage_cells_declared',
  abs((SELECT count(*) FROM route_stage_contract)-72),
  'Four routes each declare all 18 canonical stages'),
 ('DQ14','each_motion_has_eighteen_stage_cells',
  (SELECT count(*) FROM (SELECT motion_contract_id FROM route_stage_contract
                         GROUP BY motion_contract_id HAVING count(*)<>18)),
  'No route silently omits a stage'),
 ('DQ15','na_has_no_fake_evidence_contract',
  (SELECT count(*) FROM route_stage_contract WHERE applicability='NA'
   AND (minimum_evidence_class IS NOT NULL OR denominator_code IS NOT NULL
        OR source_of_truth_code IS NOT NULL)),
  'N/A does not carry a denominator or pretend to need evidence'),
 ('DQ16','applicable_stage_has_evidence_contract',
  (SELECT count(*) FROM route_stage_contract WHERE applicability<>'NA'
   AND (minimum_evidence_class IS NULL OR denominator_code IS NULL
        OR source_of_truth_code IS NULL)),
  'Required and optional stages have evidence, denominator, and authority'),
 ('DQ17','search_route_specific_na',
  abs((SELECT count(*) FROM route_stage_contract
       WHERE motion_contract_id='MOT_SEARCH_SYN' AND applicability='NA')-3),
  'Search makes submitted, approved, and connected N/A'),
 ('DQ18','plugin_commerce_is_na',
  abs((SELECT count(*) FROM route_stage_contract
       WHERE motion_contract_id='MOT_PLUGIN_SYN' AND applicability='NA')-6),
  'Existing-entitlement plugin motion does not invent new commerce'),
 ('DQ19','registry_has_no_native_commerce',
  abs((SELECT count(*) FROM route_stage_contract
       WHERE motion_contract_id='MOT_MCP_SYN' AND applicability='NA')-10),
  'Registry publication does not become checkout or payment'),
 ('DQ20','agentic_install_stage_is_na',
  CASE WHEN EXISTS (SELECT 1 FROM route_stage_contract
                    WHERE motion_contract_id='MOT_AGENTIC_SYN'
                      AND canonical_stage='VISITED_OR_INSTALLED' AND applicability='NA')
       THEN 0 ELSE 1 END,
  'The selected agentic route does not invent an install stage'),
 ('DQ21','artifact_revision_chain_exact',
  (SELECT count(*) FROM artifact_revision a WHERE a.revision_sequence>1 AND NOT EXISTS (
    SELECT 1 FROM artifact_revision p
    WHERE p.artifact_revision_id=a.supersedes_artifact_revision_id
      AND p.artifact_id=a.artifact_id AND p.revision_sequence=a.revision_sequence-1)),
  'Artifact revisions point to the exact prior revision'),
 ('DQ22','plugin_artifact_revision_two_current',
  CASE WHEN EXISTS (SELECT 1 FROM artifact_revision
                    WHERE artifact_revision_id='REV_PLUGIN_SYN_2'
                      AND supersedes_artifact_revision_id='REV_PLUGIN_SYN_1')
       THEN 0 ELSE 1 END,
  'Plugin metadata/release revision is explicit'),
 ('DQ23','journey_artifact_motion_matches',
  (SELECT count(*) FROM journey j JOIN artifact_revision a
   ON a.artifact_revision_id=j.artifact_revision_id
   WHERE a.motion_contract_id<>j.motion_contract_id),
  'A journey cannot mix artifact and motion revisions'),
 ('DQ24','no_live_journey_on_nonready_gate',
  (SELECT count(*) FROM journey j JOIN eligibility_review e
   ON e.eligibility_review_id=j.eligibility_review_id
   WHERE j.environment='LIVE' AND e.current_gate<>'READY_LIVE'),
  'Only READY_LIVE scopes create LIVE journeys'),
 ('DQ25','test_and_live_journeys_separate',
  CASE WHEN (SELECT count(*) FROM journey WHERE environment='LIVE')=3
         AND (SELECT count(*) FROM journey WHERE environment='SANDBOX')=2
       THEN 0 ELSE 1 END,
  'Fixture contains isolated LIVE and SANDBOX journeys'),
 ('DQ26','commercial_owner_once',
  (SELECT count(*) FROM v_commercial_owner_dedup WHERE owner_claim_count<>1),
  'A commercial outcome has exactly one owner key'),
 ('DQ27','verified_correlation_has_both_sides',
  (SELECT count(*) FROM journey_correlation_boundary
   WHERE link_state='VERIFIED' AND downstream_ref IS NULL),
  'Verified joins require explicit downstream identity'),
 ('DQ28','unknown_link_is_not_guessed',
  (SELECT count(*) FROM journey_correlation_boundary
   WHERE link_state='UNKNOWN_LINK' AND (downstream_ref IS NOT NULL OR link_basis<>'NONE')),
  'UNKNOWN_LINK remains unknown rather than being heuristically joined'),
 ('DQ29','unknown_link_fixture_visible',
  CASE WHEN EXISTS (SELECT 1 FROM journey_correlation_boundary
                    WHERE correlation_boundary_id='CORR_SEARCH_ACCOUNT_UNKNOWN_SYN'
                      AND link_state='UNKNOWN_LINK' AND downstream_ref IS NULL)
       THEN 0 ELSE 1 END,
  'A deliberately unjoinable provider-to-account boundary remains visible'),
 ('DQ30','stage_event_count_fixture',
  abs((SELECT count(*) FROM stage_evidence_event)-39),
  'The bounded synthetic evidence fixture is complete'),
 ('DQ31','no_event_on_na_stage',
  (SELECT count(*) FROM stage_evidence_event e JOIN route_stage_contract r
   ON r.motion_contract_id=e.motion_contract_id AND r.canonical_stage=e.canonical_stage
   WHERE r.applicability='NA'),
  'N/A stages never receive an evidence event'),
 ('DQ32','evidence_authority_mapping_exact',
  (SELECT count(*) FROM stage_evidence_event WHERE NOT (
    (evidence_class='E0_INFERENCE' AND evidence_source='INFERENCE')
    OR (evidence_class='E1_MANUAL_OBSERVATION' AND evidence_source='MANUAL')
    OR (evidence_class='E2_PROVIDER_CONTROL_PLANE' AND evidence_source='PROVIDER_CONTROL')
    OR (evidence_class='E3_VERIFIED_PROVIDER_EVENT' AND evidence_source='PROVIDER_EVENT')
    OR (evidence_class='E4_FIRST_PARTY_AUTHORITATIVE' AND evidence_source='FIRST_PARTY')
    OR (evidence_class='E5_CUSTOMER_OR_BANK_AUTHORITATIVE'
        AND evidence_source IN ('CUSTOMER','BANK')))),
  'Manual, provider, first-party, customer, and bank facts stay separate'),
 ('DQ33','verified_provider_events_signed',
  (SELECT count(*) FROM stage_evidence_event
   WHERE evidence_class='E3_VERIFIED_PROVIDER_EVENT' AND signature_status<>'VERIFIED'),
  'E3 events are signature verified'),
 ('DQ34','bank_stage_e5_bank_only',
  (SELECT count(*) FROM stage_evidence_event WHERE canonical_stage='BANK_CASH_RECONCILED'
   AND (evidence_class<>'E5_CUSTOMER_OR_BANK_AUTHORITATIVE' OR evidence_source<>'BANK')),
  'Bank cash cannot be proven by a provider dashboard'),
 ('DQ35','accepted_outcome_authoritative',
  (SELECT count(*) FROM stage_evidence_event WHERE canonical_stage='ACCEPTED_OUTCOME'
   AND evidence_class NOT IN ('E4_FIRST_PARTY_AUTHORITATIVE',
                              'E5_CUSTOMER_OR_BANK_AUTHORITATIVE')),
  'Accepted outcomes use first-party or customer evidence'),
 ('DQ36','positive_evidence_meets_route_minimum',
  (SELECT count(*) FROM stage_evidence_event e
   JOIN route_stage_contract r ON r.motion_contract_id=e.motion_contract_id
                              AND r.canonical_stage=e.canonical_stage
   JOIN evidence_class_dim got ON got.evidence_class=e.evidence_class
   JOIN evidence_class_dim need ON need.evidence_class=r.minimum_evidence_class
   WHERE e.observation_state IN ('OBSERVED','ZERO') AND got.strength_rank<need.strength_rank),
  'A positive event cannot be weaker than the route contract'),
 ('DQ37','event_identity_matches_journey',
  (SELECT count(*) FROM stage_evidence_event e JOIN journey j ON j.journey_id=e.journey_id
   WHERE e.motion_contract_id<>j.motion_contract_id
      OR e.artifact_revision_id<>j.artifact_revision_id OR e.environment<>j.environment),
  'Evidence is bound to one journey, motion, artifact, and environment'),
 ('DQ38','event_times_are_ordered',
  (SELECT count(*) FROM stage_evidence_event
   WHERE datetime(occurred_at)>datetime(recorded_at)),
  'Occurred and recorded times are not collapsed'),
 ('DQ39','source_events_deduplicated',
  (SELECT count(*) FROM (SELECT environment,source_system,source_event_id
                         FROM stage_evidence_event GROUP BY 1,2,3 HAVING count(*)>1)),
  'Webhook retry/source duplicates cannot become additional outcomes'),
 ('DQ40','evidence_revision_chain_exact',
  (SELECT count(*) FROM stage_evidence_event e WHERE e.event_revision>1 AND NOT EXISTS (
    SELECT 1 FROM stage_evidence_event p
    WHERE p.stage_event_id=e.supersedes_event_id
      AND p.evidence_stream_id=e.evidence_stream_id
      AND p.event_revision=e.event_revision-1)),
  'Corrections follow exact source revision chains'),
 ('DQ41','superseded_event_not_effective',
  CASE WHEN NOT EXISTS (SELECT 1 FROM v_effective_stage_event
                        WHERE stage_event_id='EV_SEARCH_EXACT_1_SYN')
         AND EXISTS (SELECT 1 FROM v_effective_stage_event
                     WHERE stage_event_id='EV_SEARCH_EXACT_2_SYN')
       THEN 0 ELSE 1 END,
  'The corrected exact-lookup fact replaces only its predecessor'),
 ('DQ42','late_correction_does_not_backfill_snapshot',
  CASE WHEN EXISTS (SELECT 1 FROM v_effective_stage_event
                    WHERE stage_event_id='EV_SEARCH_ZERO_1_SYN')
         AND NOT EXISTS (SELECT 1 FROM v_effective_stage_event
                         WHERE stage_event_id='EV_SEARCH_ZERO_2_SYN')
       THEN 0 ELSE 1 END,
  'A correction recorded after snapshot_as_of cannot rewrite the frozen snapshot'),
 ('DQ43','late_ingest_is_visible',
  CASE WHEN EXISTS (SELECT 1 FROM v_stage_event_timing_inventory
                    WHERE stage_event_id='EV_SEARCH_ZERO_2_SYN'
                      AND timing_state='LATE_INGEST')
       THEN 0 ELSE 1 END,
  'Late evidence remains inspectable outside the frozen numerator'),
 ('DQ44','na_snapshot_distinct',
  CASE WHEN EXISTS (SELECT 1 FROM v_journey_stage_snapshot
                    WHERE journey_id='JNY_SEARCH_LIVE_SYN'
                      AND canonical_stage='CONNECTED' AND snapshot_state='N/A')
       THEN 0 ELSE 1 END,
  'N/A is rendered explicitly in the stage snapshot'),
 ('DQ45','unknown_snapshot_distinct',
  CASE WHEN EXISTS (SELECT 1 FROM v_journey_stage_snapshot
                    WHERE journey_id='JNY_SEARCH_ZERO_SYN'
                      AND canonical_stage='ELIGIBLE' AND snapshot_state='UNKNOWN')
       THEN 0 ELSE 1 END,
  'Missing required evidence is UNKNOWN, not zero'),
 ('DQ46','measured_zero_snapshot_distinct',
  CASE WHEN EXISTS (SELECT 1 FROM v_journey_stage_snapshot
                    WHERE journey_id='JNY_SEARCH_ZERO_SYN'
                      AND canonical_stage='CITED_OR_SELECTED' AND snapshot_state='0')
       THEN 0 ELSE 1 END,
  'A measured zero retains its known denominator'),
 ('DQ47','search_live_required_path_complete',
  CASE WHEN NOT EXISTS (SELECT 1 FROM v_first_missing_required_stage
                        WHERE journey_id='JNY_SEARCH_LIVE_SYN')
       THEN 0 ELSE 1 END,
  'The mature live search fixture closes its declared required path'),
 ('DQ48','plugin_required_path_complete',
  CASE WHEN NOT EXISTS (SELECT 1 FROM v_first_missing_required_stage
                        WHERE journey_id='JNY_PLUGIN_LIVE_SYN')
       THEN 0 ELSE 1 END,
  'The existing-entitlement plugin fixture reaches accepted outcome'),
 ('DQ49','plugin_optional_discovery_unknown',
  CASE WHEN EXISTS (SELECT 1 FROM v_journey_stage_snapshot
                    WHERE journey_id='JNY_PLUGIN_LIVE_SYN' AND canonical_stage='SURFACED'
                      AND applicability='OPTIONAL' AND snapshot_state='UNKNOWN')
       THEN 0 ELSE 1 END,
  'Unknown provider suggestion exposure is not fabricated'),
 ('DQ50','plugin_checkout_na',
  CASE WHEN EXISTS (SELECT 1 FROM v_journey_stage_snapshot
                    WHERE journey_id='JNY_PLUGIN_LIVE_SYN' AND canonical_stage='CHECKOUT_CREATED'
                      AND snapshot_state='N/A')
       THEN 0 ELSE 1 END,
  'Existing-account use is not counted as a new checkout'),
 ('DQ51','registry_downstream_install_unknown',
  CASE WHEN EXISTS (SELECT 1 FROM v_journey_stage_snapshot
                    WHERE journey_id='JNY_MCP_SANDBOX_SYN'
                      AND canonical_stage='VISITED_OR_INSTALLED'
                      AND applicability='OPTIONAL' AND snapshot_state='UNKNOWN')
       THEN 0 ELSE 1 END,
  'Registry publication does not imply downstream installation'),
 ('DQ52','agentic_first_gate_failed',
  CASE WHEN EXISTS (SELECT 1 FROM v_first_missing_required_stage
                    WHERE journey_id='JNY_AGENTIC_SANDBOX_SYN'
                      AND canonical_stage='ELIGIBLE' AND snapshot_state='FAILED')
       THEN 0 ELSE 1 END,
  'Current program ineligibility remains the first failure despite sandbox success'),
 ('DQ53','parallel_state_not_forced_monotone',
  CASE WHEN EXISTS (SELECT 1 FROM stage_evidence_event p
                    JOIN stage_evidence_event i ON i.journey_id=p.journey_id
                    WHERE p.stage_event_id='EV_PLUGIN_PUBLISH_SYN'
                      AND i.stage_event_id='EV_PLUGIN_INSTALL_SYN'
                      AND datetime(i.occurred_at)<datetime(p.occurred_at))
       THEN 0 ELSE 1 END,
  'Catalog and installed/runtime ledgers can evolve in parallel'),
 ('DQ54','commerce_chain_fixture_count',
  abs((SELECT count(*) FROM commerce_chain)-2),
  'One live chain and one sandbox chain exercise commerce'),
 ('DQ55','only_one_live_commerce_chain',
  abs((SELECT count(*) FROM commerce_chain WHERE environment='LIVE')-1),
  'Sandbox commerce is isolated from live commerce'),
 ('DQ56','commerce_identity_matches_journey',
  (SELECT count(*) FROM commerce_chain c JOIN journey j ON j.journey_id=c.journey_id
   WHERE c.motion_contract_id<>j.motion_contract_id
      OR c.artifact_revision_id<>j.artifact_revision_id OR c.environment<>j.environment),
  'Commerce uses the same motion, artifact, review, and environment'),
 ('DQ57','idempotency_keys_unique',
  (SELECT count(*) FROM (SELECT environment,payment_provider_code,idempotency_key_ref
                         FROM commerce_chain GROUP BY 1,2,3 HAVING count(*)>1)),
  'An external payment write has a single-use provider/environment key'),
 ('DQ58','successful_payment_verified_and_read_back',
  (SELECT count(*) FROM commerce_chain WHERE payment_state='SUCCEEDED'
   AND (payment_signature_status<>'VERIFIED' OR authoritative_readback_at IS NULL)),
  'Payment success requires verified provider evidence and authoritative read-back'),
 ('DQ59','payment_and_order_remain_distinct',
  (SELECT count(*) FROM commerce_chain
   WHERE payment_id=order_id OR payment_state='SUCCEEDED' AND order_state='NOT_CREATED'),
  'Payment and order are explicit, separately keyed states'),
 ('DQ60','fulfillment_environment_matches_chain',
  (SELECT count(*) FROM fulfillment_event f JOIN commerce_chain c
   ON c.commerce_chain_id=f.commerce_chain_id WHERE f.environment<>c.environment),
  'Test fulfillment cannot satisfy a live order'),
 ('DQ61','product_specific_fulfillment_types',
  CASE WHEN EXISTS (SELECT 1 FROM fulfillment_event
                    WHERE commerce_chain_id='CHAIN_SEARCH_SYN' AND event_kind='ENTITLEMENT')
         AND EXISTS (SELECT 1 FROM fulfillment_event
                     WHERE commerce_chain_id='CHAIN_AGENTIC_SANDBOX_SYN'
                       AND event_kind='FULFILLMENT')
       THEN 0 ELSE 1 END,
  'SaaS entitlement and physical fulfillment remain distinct'),
 ('DQ62','search_processor_net_bridge',
  CASE WHEN EXISTS (SELECT 1 FROM v_chain_economics
                    WHERE commerce_chain_id='CHAIN_SEARCH_SYN'
                      AND gross_micros=12000000000
                      AND refund_micros=1000000000
                      AND provider_fee_micros=400000000
                      AND processor_net_micros=9600000000)
       THEN 0 ELSE 1 END,
  'Gross, tax, refund, fee, and processor net reconcile'),
 ('DQ63','paid_payout_matches_bank',
  CASE WHEN EXISTS (SELECT 1 FROM v_chain_economics
                    WHERE commerce_chain_id='CHAIN_SEARCH_SYN'
                      AND paid_payout_micros=9600000000
                      AND bank_cash_micros=9600000000)
       THEN 0 ELSE 1 END,
  'Provider payout is separately matched to bank cash'),
 ('DQ64','bank_receipt_is_e5_authority',
  (SELECT count(*) FROM bank_reconciliation
   WHERE evidence_class<>'E5_CUSTOMER_OR_BANK_AUTHORITATIVE'
      OR source_authority<>'BANK' OR environment<>'LIVE'),
  'Only live bank authority closes settlement'),
 ('DQ65','search_variable_cost_fixture',
  CASE WHEN EXISTS (SELECT 1 FROM v_chain_economics
                    WHERE commerce_chain_id='CHAIN_SEARCH_SYN'
                      AND variable_cost_micros=1700000000)
       THEN 0 ELSE 1 END,
  'COGS, AI/delivery, and channel cash costs are explicit'),
 ('DQ66','search_founder_time_fixture',
  CASE WHEN EXISTS (SELECT 1 FROM v_chain_economics
                    WHERE commerce_chain_id='CHAIN_SEARCH_SYN'
                      AND founder_minutes=30 AND founder_time_cost_micros=3000000000)
       THEN 0 ELSE 1 END,
  'Support and rescue time are valued explicitly'),
 ('DQ67','retained_contribution_fixture',
  CASE WHEN EXISTS (SELECT 1 FROM v_chain_economics
                    WHERE commerce_chain_id='CHAIN_SEARCH_SYN'
                      AND retained_net_settled_contribution_micros=4900000000
                      AND economics_state='MATURE_RETAINED')
       THEN 0 ELSE 1 END,
  'Mature retained contribution uses bank cash once, then subtracts costs and time'),
 ('DQ68','sandbox_cash_excluded',
  CASE WHEN EXISTS (SELECT 1 FROM v_chain_economics
                    WHERE commerce_chain_id='CHAIN_AGENTIC_SANDBOX_SYN'
                      AND gross_micros=0 AND bank_cash_micros=0
                      AND retained_net_settled_contribution_micros IS NULL
                      AND economics_state='TEST_ONLY')
       THEN 0 ELSE 1 END,
  'Sandbox checkout, payment, order, and fulfillment contribute no live cash'),
 ('DQ69','zero_customer_ratio_undefined',
  CASE WHEN EXISTS (SELECT 1 FROM v_motion_economics
                    WHERE motion_contract_id='MOT_PLUGIN_SYN'
                      AND mature_retained_customers=0
                      AND live_cash_cost_micros=300000000
                      AND cash_cost_per_retained_customer_micros IS NULL
                      AND ratio_state='UNDEFINED_ZERO_RETAINED_CUSTOMERS')
       THEN 0 ELSE 1 END,
  'Spend with zero retained customers is undefined, never zero CAC'),
 ('DQ70','append_only_and_validation_triggers_present',
  CASE WHEN (SELECT count(*) FROM sqlite_schema
             WHERE type='trigger' AND name LIKE 'immutable_%')>=12
         AND (SELECT count(*) FROM sqlite_schema
              WHERE type='trigger' AND name LIKE 'validate_%')>=8
       THEN 0 ELSE 1 END,
  'Important contracts, evidence, commerce, and cash DML fail closed');

CREATE TABLE assertion_guard (
  guard_id INTEGER PRIMARY KEY CHECK (guard_id=1),
  failed_assertion_count INTEGER NOT NULL CHECK (failed_assertion_count=0)
) STRICT;

INSERT INTO assertion_guard
SELECT 1,sum(CASE WHEN failure_count=0 THEN 0 ELSE 1 END)
FROM dq_assertion_result;

COMMIT;

-- -----------------------------------------------------------------------------
-- Bounded output pack
-- -----------------------------------------------------------------------------

PRAGMA foreign_key_check;
PRAGMA integrity_check;

SELECT check_id,check_name,failure_count,
       CASE WHEN failure_count=0 THEN 'PASS' ELSE 'FAIL' END AS status
FROM dq_assertion_result ORDER BY check_id;

SELECT count(*) AS assertion_count,
       sum(CASE WHEN failure_count=0 THEN 1 ELSE 0 END) AS pass_count,
       sum(CASE WHEN failure_count<>0 THEN 1 ELSE 0 END) AS fail_count
FROM dq_assertion_result;

SELECT motion_contract_id,motion_type,region_code,product_type,current_gate,
       current_scope_allows,current_scope_denies,has_preview,has_upcoming,has_conflict
FROM v_policy_scope_status ORDER BY motion_contract_id;

SELECT journey_id,motion_contract_id,environment,current_gate,
       first_missing_required_stage,first_missing_state,derived_decision
FROM v_journey_decision ORDER BY journey_id;

SELECT journey_id,canonical_stage,applicability,snapshot_state,effective_event_count
FROM v_journey_stage_snapshot
WHERE journey_id IN ('JNY_SEARCH_ZERO_SYN','JNY_MCP_SANDBOX_SYN')
  AND canonical_stage IN ('ELIGIBLE','PUBLISHED_OR_INDEXED','CITED_OR_SELECTED',
                          'VISITED_OR_INSTALLED','BANK_CASH_RECONCILED')
ORDER BY journey_id,display_order;

SELECT stage_event_id,canonical_stage,timing_state
FROM v_stage_event_timing_inventory
WHERE timing_state<>'EFFECTIVE_AS_OF'
ORDER BY stage_event_id;

SELECT commerce_chain_id,motion_contract_id,environment,currency,gross_micros,
       processor_net_micros,paid_payout_micros,bank_cash_micros,
       variable_cost_micros,founder_minutes,founder_time_cost_micros,
       retained_net_settled_contribution_micros,economics_state
FROM v_chain_economics ORDER BY commerce_chain_id;

SELECT motion_contract_id,motion_type,bank_cash_micros,retained_contribution_micros,
       mature_retained_customers,live_cash_cost_micros,
       cash_cost_per_retained_customer_micros,ratio_state
FROM v_motion_economics ORDER BY motion_contract_id;
