-- Founder-led first-revenue acquisition evidence model
-- Schema version: 1.0.0 | Reference date: 2026-08-02 | Engine: SQLite 3.40+
-- Companion: ../16-founder-led-first-revenue.md
-- Template: ../templates/16-founder-led-acquisition-pack.md
--
-- Run only on a new/disposable database:
--   sqlite3 :memory: < indie-developer-playbook/research/16-founder-led-acquisition-model.sql
--
-- All fixture identifiers, source references, facts, amounts, rates, dates, and
-- outcomes are synthetic. They are data-shape examples, not contacts, legal
-- conclusions, sales benchmarks, or recommended outreach volume.
--
-- Privacy boundary
-- - Store no name, email address, phone number, profile, raw reply, transcript,
--   credential, customer secret, or inferred sensitive/protected attribute.
-- - *_ref_hash values are opaque 64-character lowercase hexadecimal examples.
-- - Restricted systems, not this analytical companion, remain the source of truth.
--
-- Time and money boundary
-- - occurred_at is event time; recorded_at is ledger-ingest time.
-- - outcome_cutoff_at freezes a decision snapshot. Later replies remain visible
--   only as late replies and never backfill the frozen numerator.
-- - *_micros is one millionth of the row currency (JPY 1 = 1,000,000 micros).
-- - Founder-set caps and hourly value are fixture inputs, never universal targets.
--
-- Intentional v1 boundaries
-- - External execution is EMAIL + HUMAN_ONLY. FORM, platform, phone, and other
--   channels remain recorded research candidates but fail closed for sending.
-- - Meeting recording/AI permission and artifact retention belong to a separate
--   privacy/data-handling ledger and are not modeled as meeting status.
-- - Economics stop at cost per Qualified. Contracted, first-cash, paid-in-full,
--   and customer CAC states belong to the Chapter 11 revenue ledger.

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 = 'founder_led_acquisition'),
  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_PII_OPAQUE_REFERENCES'
  ),
  benchmark_policy_code TEXT NOT NULL CHECK (
    benchmark_policy_code = 'FOUNDER_SET_LIMITS_NOT_MARKET_BENCHMARKS'
  )
) STRICT;

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

CREATE TABLE gate_state_dim (
  gate_state_code TEXT PRIMARY KEY CHECK (gate_state_code IN (
    'STOP','PAUSE','UNKNOWN','READY_TO_SEND'
  )),
  priority_order INTEGER NOT NULL UNIQUE CHECK (priority_order BETWEEN 1 AND 4)
) STRICT;

CREATE TABLE segment_contract (
  batch_id TEXT PRIMARY KEY,
  segment_id TEXT NOT NULL,
  segment_version INTEGER NOT NULL CHECK (segment_version > 0),
  cohort_start_at TEXT NOT NULL CHECK (datetime(cohort_start_at) IS NOT NULL),
  selection_close_at TEXT NOT NULL CHECK (datetime(selection_close_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_review_at TEXT NOT NULL CHECK (datetime(maturity_review_at) IS NOT NULL),
  max_selected_accounts INTEGER NOT NULL CHECK (max_selected_accounts > 0),
  max_contacts_per_account INTEGER NOT NULL CHECK (max_contacts_per_account > 0),
  max_attempts_per_contact INTEGER NOT NULL CHECK (max_attempts_per_contact > 0),
  min_attempt_spacing_minutes INTEGER NOT NULL CHECK (min_attempt_spacing_minutes >= 0),
  max_founder_minutes INTEGER NOT NULL CHECK (max_founder_minutes > 0),
  max_cash_micros INTEGER NOT NULL CHECK (max_cash_micros >= 0),
  primary_outcome_code TEXT NOT NULL
    CHECK (primary_outcome_code = 'QUALIFIED_ACCOUNT'),
  attribution_rule_version TEXT NOT NULL,
  contract_ref_hash 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),
  CHECK (datetime(cohort_start_at) <= datetime(selection_close_at)),
  CHECK (datetime(selection_close_at) <= datetime(outcome_cutoff_at)),
  CHECK (datetime(outcome_cutoff_at) <= datetime(maturity_review_at)),
  CHECK (datetime(outcome_cutoff_at) <= datetime(snapshot_as_of)),
  CHECK (datetime(approved_at) <= datetime(recorded_at)),
  CHECK (length(contract_ref_hash)=64
         AND contract_ref_hash=lower(contract_ref_hash)
         AND contract_ref_hash NOT GLOB '*[^0-9a-f]*')
) STRICT;

CREATE TABLE acquisition_account (
  account_id TEXT PRIMARY KEY,
  batch_id TEXT NOT NULL,
  eligibility_status TEXT NOT NULL
    CHECK (eligibility_status IN ('ELIGIBLE','EXCLUDED','UNKNOWN')),
  selected_for_batch INTEGER NOT NULL CHECK (selected_for_batch IN (0,1)),
  selected_at TEXT CHECK (selected_at IS NULL OR datetime(selected_at) IS NOT NULL),
  account_source_ref_hash TEXT NOT NULL,
  domain_ref_hash TEXT NOT NULL,
  occurred_at TEXT NOT NULL CHECK (datetime(occurred_at) IS NOT NULL),
  recorded_at TEXT NOT NULL CHECK (datetime(recorded_at) IS NOT NULL),
  CHECK ((selected_for_batch=1 AND selected_at IS NOT NULL)
      OR (selected_for_batch=0 AND selected_at IS NULL)),
  CHECK (datetime(occurred_at) <= datetime(recorded_at)),
  CHECK (length(account_source_ref_hash)=64
         AND account_source_ref_hash=lower(account_source_ref_hash)
         AND account_source_ref_hash NOT GLOB '*[^0-9a-f]*'),
  CHECK (length(domain_ref_hash)=64
         AND domain_ref_hash=lower(domain_ref_hash)
         AND domain_ref_hash NOT GLOB '*[^0-9a-f]*'),
  UNIQUE (batch_id,account_id),
  FOREIGN KEY (batch_id) REFERENCES segment_contract(batch_id)
) STRICT;

CREATE TABLE trigger_evidence (
  trigger_evidence_id TEXT PRIMARY KEY,
  batch_id TEXT NOT NULL,
  account_id TEXT NOT NULL,
  trigger_type_code TEXT NOT NULL,
  evidence_status TEXT NOT NULL CHECK (evidence_status IN (
    'CONFIRMED','REFUTED','UNKNOWN','STALE'
  )),
  observed_fact_ref_hash TEXT NOT NULL,
  source_snapshot_ref_hash TEXT NOT NULL,
  hypothesis_ref_hash TEXT NOT NULL,
  occurred_at TEXT NOT NULL CHECK (datetime(occurred_at) IS NOT NULL),
  recorded_at TEXT NOT NULL CHECK (datetime(recorded_at) IS NOT NULL),
  valid_until TEXT NOT NULL CHECK (datetime(valid_until) IS NOT NULL),
  reviewed_by_role TEXT NOT NULL,
  CHECK (datetime(occurred_at) <= datetime(recorded_at)),
  CHECK (length(observed_fact_ref_hash)=64
         AND observed_fact_ref_hash=lower(observed_fact_ref_hash)
         AND observed_fact_ref_hash NOT GLOB '*[^0-9a-f]*'),
  CHECK (length(source_snapshot_ref_hash)=64
         AND source_snapshot_ref_hash=lower(source_snapshot_ref_hash)
         AND source_snapshot_ref_hash NOT GLOB '*[^0-9a-f]*'),
  CHECK (length(hypothesis_ref_hash)=64
         AND hypothesis_ref_hash=lower(hypothesis_ref_hash)
         AND hypothesis_ref_hash NOT GLOB '*[^0-9a-f]*'),
  FOREIGN KEY (batch_id,account_id)
    REFERENCES acquisition_account(batch_id,account_id)
) STRICT;

CREATE TABLE contact (
  contact_id TEXT PRIMARY KEY,
  batch_id TEXT NOT NULL,
  account_id TEXT NOT NULL,
  role_code TEXT NOT NULL CHECK (role_code IN (
    'USER','PROCESS_OWNER','VALUE_DECIDER','BUYER','SECURITY_LEGAL',
    'FINANCE','UNKNOWN'
  )),
  channel_code TEXT NOT NULL CHECK (channel_code IN (
    'EMAIL','PHONE','FORM','PLATFORM_MESSAGE','REFERRAL','EVENT','OTHER'
  )),
  address_owner_code TEXT NOT NULL CHECK (address_owner_code IN (
    'ORGANIZATION','ROLE','NATURAL_PERSON','UNKNOWN'
  )),
  source_type_code TEXT NOT NULL CHECK (source_type_code IN (
    'DIRECT_CONSENT','BUSINESS_CARD','PUBLIC_BUSINESS_ADDRESS',
    'EXISTING_RELATIONSHIP','REFERRAL_PERMISSION','PERMISSIONED_PLATFORM',
    'PURCHASED','SCRAPED','UNKNOWN'
  )),
  provenance_status TEXT NOT NULL CHECK (provenance_status IN (
    'CONFIRMED','REFUTED','UNKNOWN','STALE'
  )),
  address_ref_hash TEXT NOT NULL,
  source_snapshot_ref_hash TEXT NOT NULL,
  domain_ref_hash TEXT NOT NULL,
  occurred_at TEXT NOT NULL CHECK (datetime(occurred_at) IS NOT NULL),
  recorded_at TEXT NOT NULL CHECK (datetime(recorded_at) IS NOT NULL),
  CHECK (datetime(occurred_at) <= datetime(recorded_at)),
  CHECK (length(address_ref_hash)=64
         AND address_ref_hash=lower(address_ref_hash)
         AND address_ref_hash NOT GLOB '*[^0-9a-f]*'),
  CHECK (length(source_snapshot_ref_hash)=64
         AND source_snapshot_ref_hash=lower(source_snapshot_ref_hash)
         AND source_snapshot_ref_hash NOT GLOB '*[^0-9a-f]*'),
  CHECK (length(domain_ref_hash)=64
         AND domain_ref_hash=lower(domain_ref_hash)
         AND domain_ref_hash NOT GLOB '*[^0-9a-f]*'),
  UNIQUE (batch_id,contact_id),
  FOREIGN KEY (batch_id,account_id)
    REFERENCES acquisition_account(batch_id,account_id)
) STRICT;

-- A review stream is one exact contact x channel x purpose x sender tuple.
CREATE TABLE contactability_review (
  contactability_review_id TEXT PRIMARY KEY,
  review_stream_id TEXT NOT NULL,
  review_sequence INTEGER NOT NULL CHECK (review_sequence > 0),
  supersedes_review_id TEXT,
  batch_id TEXT NOT NULL,
  account_id TEXT NOT NULL,
  contact_id TEXT NOT NULL,
  channel_code TEXT NOT NULL,
  exact_purpose_code TEXT NOT NULL,
  exact_sender_id TEXT NOT NULL,
  candidate_basis_code TEXT NOT NULL CHECK (candidate_basis_code IN (
    'EXPLICIT_CONSENT','ADDRESS_NOTIFICATION','EXISTING_RELATIONSHIP',
    'PUBLIC_BUSINESS_ADDRESS','RECIPIENT_REQUESTED','PERMISSIONED_PLATFORM',
    'UNKNOWN'
  )),
  channel_check_matrix_version TEXT NOT NULL,
  review_result TEXT NOT NULL CHECK (review_result IN ('PASS','STOP','UNKNOWN')),
  specific_email_law_result TEXT NOT NULL
    CHECK (specific_email_law_result IN ('PASS','STOP','UNKNOWN','NOT_APPLICABLE')),
  specified_commercial_transactions_result TEXT NOT NULL CHECK (
    specified_commercial_transactions_result IN (
      'PASS','STOP','UNKNOWN','NOT_APPLICABLE'
    )
  ),
  privacy_purpose_result TEXT NOT NULL
    CHECK (privacy_purpose_result IN ('PASS','STOP','UNKNOWN','NOT_APPLICABLE')),
  platform_terms_result TEXT NOT NULL
    CHECK (platform_terms_result IN ('PASS','STOP','UNKNOWN','NOT_APPLICABLE')),
  consent_or_exception_record_result TEXT NOT NULL
    CHECK (consent_or_exception_record_result IN ('PASS','STOP','UNKNOWN')),
  no_solicitation_public_source_result TEXT NOT NULL CHECK (
    no_solicitation_public_source_result IN (
      'PASS','STOP','UNKNOWN','NOT_APPLICABLE'
    )
  ),
  sender_identity_result TEXT NOT NULL
    CHECK (sender_identity_result IN ('PASS','STOP','UNKNOWN')),
  sender_address_display_result TEXT NOT NULL
    CHECK (sender_address_display_result IN ('PASS','STOP','UNKNOWN')),
  complaint_inquiry_contact_display_result TEXT NOT NULL
    CHECK (complaint_inquiry_contact_display_result IN ('PASS','STOP','UNKNOWN')),
  opt_out_result TEXT NOT NULL CHECK (opt_out_result IN ('PASS','STOP','UNKNOWN')),
  authentication_result TEXT NOT NULL
    CHECK (authentication_result IN ('PASS','STOP','UNKNOWN')),
  not_applicable_basis_ref_hash TEXT,
  evidence_package_ref_hash TEXT NOT NULL,
  occurred_at TEXT NOT NULL CHECK (datetime(occurred_at) IS NOT NULL),
  recorded_at TEXT NOT NULL CHECK (datetime(recorded_at) IS NOT NULL),
  valid_until TEXT NOT NULL CHECK (datetime(valid_until) IS NOT NULL),
  reviewed_by_role TEXT NOT NULL,
  CHECK (datetime(occurred_at) <= datetime(recorded_at)),
  CHECK (not_applicable_basis_ref_hash IS NULL
      OR (length(not_applicable_basis_ref_hash)=64
          AND not_applicable_basis_ref_hash=lower(not_applicable_basis_ref_hash)
          AND not_applicable_basis_ref_hash NOT GLOB '*[^0-9a-f]*')),
  CHECK ('NOT_APPLICABLE' NOT IN (
      specific_email_law_result,specified_commercial_transactions_result,
      privacy_purpose_result,platform_terms_result,
      no_solicitation_public_source_result
    ) OR not_applicable_basis_ref_hash IS NOT NULL),
  CHECK (candidate_basis_code<>'PUBLIC_BUSINESS_ADDRESS'
      OR no_solicitation_public_source_result IN ('PASS','STOP','UNKNOWN')),
  CHECK (
    (review_result='STOP' AND 'STOP' IN (
      specific_email_law_result,specified_commercial_transactions_result,
      privacy_purpose_result,platform_terms_result,
      consent_or_exception_record_result,no_solicitation_public_source_result,
      sender_identity_result,sender_address_display_result,
      complaint_inquiry_contact_display_result,opt_out_result,
      authentication_result
    ))
    OR (review_result='UNKNOWN' AND 'STOP' NOT IN (
      specific_email_law_result,specified_commercial_transactions_result,
      privacy_purpose_result,platform_terms_result,
      consent_or_exception_record_result,no_solicitation_public_source_result,
      sender_identity_result,sender_address_display_result,
      complaint_inquiry_contact_display_result,opt_out_result,
      authentication_result
    ) AND 'UNKNOWN' IN (
      specific_email_law_result,specified_commercial_transactions_result,
      privacy_purpose_result,platform_terms_result,
      consent_or_exception_record_result,no_solicitation_public_source_result,
      sender_identity_result,sender_address_display_result,
      complaint_inquiry_contact_display_result,opt_out_result,
      authentication_result
    ))
    OR (review_result='PASS' AND 'STOP' NOT IN (
      specific_email_law_result,specified_commercial_transactions_result,
      privacy_purpose_result,platform_terms_result,
      consent_or_exception_record_result,no_solicitation_public_source_result,
      sender_identity_result,sender_address_display_result,
      complaint_inquiry_contact_display_result,opt_out_result,
      authentication_result
    ) AND 'UNKNOWN' NOT IN (
      specific_email_law_result,specified_commercial_transactions_result,
      privacy_purpose_result,platform_terms_result,
      consent_or_exception_record_result,no_solicitation_public_source_result,
      sender_identity_result,sender_address_display_result,
      complaint_inquiry_contact_display_result,opt_out_result,
      authentication_result
    ))
  ),
  CHECK (length(evidence_package_ref_hash)=64
         AND evidence_package_ref_hash=lower(evidence_package_ref_hash)
         AND evidence_package_ref_hash NOT GLOB '*[^0-9a-f]*'),
  UNIQUE (review_stream_id,review_sequence),
  UNIQUE (
    batch_id,contact_id,channel_code,exact_purpose_code,exact_sender_id,
    review_sequence
  ),
  UNIQUE (batch_id,contactability_review_id),
  FOREIGN KEY (batch_id,account_id)
    REFERENCES acquisition_account(batch_id,account_id),
  FOREIGN KEY (batch_id,contact_id) REFERENCES contact(batch_id,contact_id),
  FOREIGN KEY (supersedes_review_id)
    REFERENCES contactability_review(contactability_review_id)
) STRICT;

CREATE TABLE suppression_event (
  suppression_event_id TEXT PRIMARY KEY,
  suppression_stream_id TEXT NOT NULL,
  event_sequence INTEGER NOT NULL CHECK (event_sequence > 0),
  supersedes_suppression_event_id TEXT,
  scope_code TEXT NOT NULL CHECK (scope_code IN (
    'CONTACT','ACCOUNT','DOMAIN','ALL'
  )),
  scope_ref TEXT NOT NULL,
  channel_scope TEXT NOT NULL,
  purpose_scope TEXT NOT NULL,
  product_scope TEXT NOT NULL,
  sender_scope TEXT NOT NULL,
  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),
  action_code TEXT NOT NULL CHECK (action_code IN ('SUPPRESS','RELEASE')),
  reason_code TEXT NOT NULL CHECK (reason_code IN (
    'EXPLICIT_OPT_OUT','LEGAL_STOP','COMPLAINT','SPAM_REPORT','HARD_BOUNCE',
    'WRONG_RECIPIENT','SOFT_BOUNCE','DEFERRED','INVESTIGATION',
    'PROVIDER_STOP','OTHER'
  )),
  source_evidence_ref_hash TEXT NOT NULL,
  authority_role TEXT NOT NULL,
  occurred_at TEXT NOT NULL CHECK (datetime(occurred_at) IS NOT NULL),
  recorded_at TEXT NOT NULL CHECK (datetime(recorded_at) IS NOT NULL),
  CHECK (datetime(occurred_at) <= datetime(recorded_at)),
  CHECK (datetime(effective_from) <= datetime(occurred_at)),
  CHECK (effective_until IS NULL
      OR datetime(effective_from) < datetime(effective_until)),
  CHECK (effective_until IS NULL OR (
    action_code='SUPPRESS' AND reason_code IN (
      'SOFT_BOUNCE','DEFERRED','INVESTIGATION','PROVIDER_STOP'
    )
  )),
  CHECK (length(source_evidence_ref_hash)=64
         AND source_evidence_ref_hash=lower(source_evidence_ref_hash)
         AND source_evidence_ref_hash NOT GLOB '*[^0-9a-f]*'),
  UNIQUE (suppression_stream_id,event_sequence),
  FOREIGN KEY (supersedes_suppression_event_id)
    REFERENCES suppression_event(suppression_event_id)
) STRICT;

CREATE TABLE claim_evidence (
  claim_evidence_id TEXT PRIMARY KEY,
  claim_class_code TEXT NOT NULL CHECK (claim_class_code IN (
    'OBSERVED_FACT','PROBLEM_HYPOTHESIS','OWN_CAPABILITY','MEASURED_OUTCOME',
    'THIRD_PARTY_FACT','OFFER_TERM'
  )),
  fact_or_hypothesis_code TEXT NOT NULL
    CHECK (fact_or_hypothesis_code IN ('FACT','HYPOTHESIS')),
  review_result TEXT NOT NULL CHECK (review_result IN ('PASS','STOP','UNKNOWN')),
  exact_claim_ref_hash TEXT NOT NULL,
  source_evidence_ref_hash TEXT NOT NULL,
  occurred_at TEXT NOT NULL CHECK (datetime(occurred_at) IS NOT NULL),
  recorded_at TEXT NOT NULL CHECK (datetime(recorded_at) IS NOT NULL),
  valid_until TEXT NOT NULL CHECK (datetime(valid_until) IS NOT NULL),
  reviewed_by_role TEXT NOT NULL,
  CHECK (datetime(occurred_at) <= datetime(recorded_at)),
  CHECK (length(exact_claim_ref_hash)=64
         AND exact_claim_ref_hash=lower(exact_claim_ref_hash)
         AND exact_claim_ref_hash NOT GLOB '*[^0-9a-f]*'),
  CHECK (length(source_evidence_ref_hash)=64
         AND source_evidence_ref_hash=lower(source_evidence_ref_hash)
         AND source_evidence_ref_hash NOT GLOB '*[^0-9a-f]*')
) STRICT;

CREATE TABLE message_version (
  message_version_id TEXT PRIMARY KEY,
  batch_id TEXT NOT NULL,
  channel_code TEXT NOT NULL,
  exact_purpose_code TEXT NOT NULL,
  subject_ref_hash TEXT NOT NULL,
  body_ref_hash TEXT NOT NULL,
  cta_ref_hash TEXT NOT NULL,
  sender_identity_version TEXT NOT NULL,
  approved_at TEXT CHECK (approved_at IS NULL OR datetime(approved_at) IS NOT NULL),
  approval_recorded_at TEXT
    CHECK (approval_recorded_at IS NULL OR datetime(approval_recorded_at) IS NOT NULL),
  effective_from TEXT CHECK (effective_from IS NULL OR datetime(effective_from) IS NOT NULL),
  retired_at TEXT CHECK (retired_at IS NULL OR datetime(retired_at) IS NOT NULL),
  retirement_recorded_at TEXT
    CHECK (retirement_recorded_at IS NULL OR datetime(retirement_recorded_at) IS NOT NULL),
  CHECK ((approved_at IS NULL AND approval_recorded_at IS NULL
          AND effective_from IS NULL)
      OR (approved_at IS NOT NULL AND approval_recorded_at IS NOT NULL
          AND effective_from IS NOT NULL)),
  CHECK (approved_at IS NULL OR datetime(approved_at) <= datetime(approval_recorded_at)),
  CHECK ((retired_at IS NULL AND retirement_recorded_at IS NULL)
      OR (retired_at IS NOT NULL AND retirement_recorded_at IS NOT NULL)),
  CHECK (retired_at IS NULL OR datetime(retired_at) <= datetime(retirement_recorded_at)),
  CHECK (retired_at IS NULL OR datetime(effective_from) < datetime(retired_at)),
  CHECK (length(subject_ref_hash)=64
         AND subject_ref_hash=lower(subject_ref_hash)
         AND subject_ref_hash NOT GLOB '*[^0-9a-f]*'),
  CHECK (length(body_ref_hash)=64
         AND body_ref_hash=lower(body_ref_hash)
         AND body_ref_hash NOT GLOB '*[^0-9a-f]*'),
  CHECK (length(cta_ref_hash)=64
         AND cta_ref_hash=lower(cta_ref_hash)
         AND cta_ref_hash NOT GLOB '*[^0-9a-f]*'),
  UNIQUE (batch_id,message_version_id),
  FOREIGN KEY (batch_id) REFERENCES segment_contract(batch_id)
) STRICT;

CREATE TABLE message_claim (
  message_version_id TEXT NOT NULL,
  claim_evidence_id TEXT NOT NULL,
  claim_order INTEGER NOT NULL CHECK (claim_order > 0),
  PRIMARY KEY (message_version_id,claim_evidence_id),
  UNIQUE (message_version_id,claim_order),
  FOREIGN KEY (message_version_id) REFERENCES message_version(message_version_id),
  FOREIGN KEY (claim_evidence_id) REFERENCES claim_evidence(claim_evidence_id)
) STRICT;

-- Blocked candidates remain queryable. Only APPROVED + derived READY_TO_SEND may
-- become an attempt; this keeps negative gate fixtures out of the send ledger.
CREATE TABLE exact_send_request (
  send_request_id TEXT PRIMARY KEY,
  batch_id TEXT NOT NULL,
  account_id TEXT NOT NULL,
  contact_id TEXT NOT NULL,
  channel_code TEXT NOT NULL,
  exact_purpose_code TEXT NOT NULL,
  product_code TEXT NOT NULL,
  exact_sender_id TEXT NOT NULL,
  segment_contract_ref_hash TEXT NOT NULL,
  trigger_evidence_id TEXT NOT NULL,
  contactability_review_id TEXT NOT NULL,
  message_version_id TEXT NOT NULL,
  final_body_ref_hash TEXT NOT NULL,
  claim_set_ref_hash TEXT NOT NULL,
  scheduled_send_at TEXT NOT NULL CHECK (datetime(scheduled_send_at) IS NOT NULL),
  request_recorded_at TEXT NOT NULL CHECK (datetime(request_recorded_at) IS NOT NULL),
  prior_unknown_receipt_review_ref_hash TEXT,
  approval_status TEXT NOT NULL
    CHECK (approval_status IN ('PENDING','APPROVED','REJECTED')),
  human_approved_by_role TEXT,
  approved_at TEXT CHECK (approved_at IS NULL OR datetime(approved_at) IS NOT NULL),
  approval_recorded_at TEXT
    CHECK (approval_recorded_at IS NULL OR datetime(approval_recorded_at) IS NOT NULL),
  approval_expires_at TEXT CHECK (
    approval_expires_at IS NULL OR datetime(approval_expires_at) IS NOT NULL
  ),
  CHECK ((approval_status='APPROVED'
          AND human_approved_by_role IS NOT NULL
          AND approved_at IS NOT NULL
          AND approval_recorded_at IS NOT NULL
          AND approval_expires_at IS NOT NULL)
      OR (approval_status<>'APPROVED'
          AND human_approved_by_role IS NULL
          AND approved_at IS NULL
          AND approval_recorded_at IS NULL
          AND approval_expires_at IS NULL)),
  CHECK (approval_status<>'APPROVED'
      OR datetime(approved_at) <= datetime(approval_recorded_at)),
  CHECK (approval_status<>'APPROVED'
      OR datetime(approval_recorded_at) <= datetime(scheduled_send_at)),
  CHECK (approval_status<>'APPROVED'
      OR datetime(scheduled_send_at) <= datetime(approval_expires_at)),
  CHECK (length(final_body_ref_hash)=64
         AND final_body_ref_hash=lower(final_body_ref_hash)
         AND final_body_ref_hash NOT GLOB '*[^0-9a-f]*'),
  CHECK (length(claim_set_ref_hash)=64
         AND claim_set_ref_hash=lower(claim_set_ref_hash)
         AND claim_set_ref_hash NOT GLOB '*[^0-9a-f]*'),
  CHECK (length(segment_contract_ref_hash)=64
         AND segment_contract_ref_hash=lower(segment_contract_ref_hash)
         AND segment_contract_ref_hash NOT GLOB '*[^0-9a-f]*'),
  CHECK (prior_unknown_receipt_review_ref_hash IS NULL
      OR (length(prior_unknown_receipt_review_ref_hash)=64
          AND prior_unknown_receipt_review_ref_hash=lower(prior_unknown_receipt_review_ref_hash)
          AND prior_unknown_receipt_review_ref_hash NOT GLOB '*[^0-9a-f]*')),
  UNIQUE (batch_id,send_request_id),
  FOREIGN KEY (batch_id,account_id)
    REFERENCES acquisition_account(batch_id,account_id),
  FOREIGN KEY (batch_id,contact_id) REFERENCES contact(batch_id,contact_id),
  FOREIGN KEY (trigger_evidence_id) REFERENCES trigger_evidence(trigger_evidence_id),
  FOREIGN KEY (batch_id,contactability_review_id)
    REFERENCES contactability_review(batch_id,contactability_review_id),
  FOREIGN KEY (batch_id,message_version_id)
    REFERENCES message_version(batch_id,message_version_id)
) STRICT;

CREATE TABLE contact_attempt (
  attempt_id TEXT PRIMARY KEY,
  send_request_id TEXT NOT NULL UNIQUE,
  batch_id TEXT NOT NULL,
  account_id TEXT NOT NULL,
  contact_id TEXT NOT NULL,
  attempt_sequence INTEGER NOT NULL CHECK (attempt_sequence > 0),
  execution_preflight_at TEXT NOT NULL
    CHECK (datetime(execution_preflight_at) IS NOT NULL),
  suppression_watermark_at TEXT NOT NULL
    CHECK (datetime(suppression_watermark_at) IS NOT NULL),
  idempotency_key_ref_hash TEXT NOT NULL UNIQUE,
  execution_authority_code TEXT NOT NULL
    CHECK (execution_authority_code='HUMAN_ONLY'),
  auto_retry_authorized INTEGER NOT NULL CHECK (auto_retry_authorized=0),
  retry_of_attempt_id TEXT,
  provider_message_ref_hash TEXT NOT NULL UNIQUE,
  send_receipt_status TEXT NOT NULL
    CHECK (send_receipt_status IN ('ACCEPTED','REJECTED','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),
  CHECK (datetime(occurred_at) <= datetime(recorded_at)),
  CHECK (datetime(execution_preflight_at)=datetime(occurred_at)),
  CHECK (datetime(suppression_watermark_at)=datetime(execution_preflight_at)),
  CHECK (length(idempotency_key_ref_hash)=64
         AND idempotency_key_ref_hash=lower(idempotency_key_ref_hash)
         AND idempotency_key_ref_hash NOT GLOB '*[^0-9a-f]*'),
  CHECK (length(provider_message_ref_hash)=64
         AND provider_message_ref_hash=lower(provider_message_ref_hash)
         AND provider_message_ref_hash NOT GLOB '*[^0-9a-f]*'),
  UNIQUE (batch_id,contact_id,attempt_sequence),
  UNIQUE (batch_id,attempt_id),
  FOREIGN KEY (batch_id,account_id)
    REFERENCES acquisition_account(batch_id,account_id),
  FOREIGN KEY (batch_id,contact_id) REFERENCES contact(batch_id,contact_id),
  FOREIGN KEY (batch_id,send_request_id)
    REFERENCES exact_send_request(batch_id,send_request_id),
  FOREIGN KEY (retry_of_attempt_id) REFERENCES contact_attempt(attempt_id)
) STRICT;

CREATE TABLE delivery_event (
  delivery_event_id TEXT PRIMARY KEY,
  attempt_id TEXT NOT NULL,
  event_sequence INTEGER NOT NULL CHECK (event_sequence > 0),
  supersedes_delivery_event_id TEXT,
  outcome_code TEXT NOT NULL CHECK (outcome_code IN (
    'ACCEPTED','DELIVERED_SIGNAL','SOFT_BOUNCE','HARD_BOUNCE','DEFERRED',
    'REJECTED','UNKNOWN'
  )),
  suppression_event_id TEXT,
  provider_receipt_ref_hash TEXT NOT NULL,
  occurred_at TEXT NOT NULL CHECK (datetime(occurred_at) IS NOT NULL),
  recorded_at TEXT NOT NULL CHECK (datetime(recorded_at) IS NOT NULL),
  CHECK (datetime(occurred_at) <= datetime(recorded_at)),
  CHECK ((outcome_code='HARD_BOUNCE' AND suppression_event_id IS NOT NULL)
      OR (outcome_code<>'HARD_BOUNCE' AND suppression_event_id IS NULL)),
  CHECK (length(provider_receipt_ref_hash)=64
         AND provider_receipt_ref_hash=lower(provider_receipt_ref_hash)
         AND provider_receipt_ref_hash NOT GLOB '*[^0-9a-f]*'),
  UNIQUE (attempt_id,event_sequence),
  FOREIGN KEY (attempt_id) REFERENCES contact_attempt(attempt_id),
  FOREIGN KEY (suppression_event_id)
    REFERENCES suppression_event(suppression_event_id),
  FOREIGN KEY (supersedes_delivery_event_id)
    REFERENCES delivery_event(delivery_event_id)
) STRICT;

CREATE TABLE reply_event (
  reply_event_id TEXT PRIMARY KEY,
  thread_id TEXT NOT NULL,
  reply_sequence INTEGER NOT NULL CHECK (reply_sequence > 0),
  supersedes_reply_event_id TEXT,
  batch_id TEXT NOT NULL,
  account_id TEXT NOT NULL,
  contact_id TEXT NOT NULL,
  causally_related_attempt_id TEXT NOT NULL,
  reply_actor_code TEXT NOT NULL CHECK (reply_actor_code IN (
    'HUMAN','AUTO_REPLY','SYSTEM','UNKNOWN'
  )),
  outcome_code TEXT NOT NULL CHECK (outcome_code IN (
    'POSITIVE','QUESTION','REFERRAL','WRONG_PERSON','EXPLICIT_NO','NOT_NOW',
    'PROBLEM_ABSENT','NO_AUTHORITY','LEGAL_STOP','OTHER','UNKNOWN'
  )),
  raw_reply_ref_hash TEXT NOT NULL,
  classification_ref_hash TEXT NOT NULL,
  classified_by_role TEXT NOT NULL,
  occurred_at TEXT NOT NULL CHECK (datetime(occurred_at) IS NOT NULL),
  recorded_at TEXT NOT NULL CHECK (datetime(recorded_at) IS NOT NULL),
  CHECK (datetime(occurred_at) <= datetime(recorded_at)),
  CHECK (reply_actor_code='HUMAN' OR outcome_code IN ('OTHER','UNKNOWN')),
  CHECK (length(raw_reply_ref_hash)=64
         AND raw_reply_ref_hash=lower(raw_reply_ref_hash)
         AND raw_reply_ref_hash NOT GLOB '*[^0-9a-f]*'),
  CHECK (length(classification_ref_hash)=64
         AND classification_ref_hash=lower(classification_ref_hash)
         AND classification_ref_hash NOT GLOB '*[^0-9a-f]*'),
  UNIQUE (thread_id,reply_sequence),
  FOREIGN KEY (batch_id,account_id)
    REFERENCES acquisition_account(batch_id,account_id),
  FOREIGN KEY (batch_id,contact_id) REFERENCES contact(batch_id,contact_id),
  FOREIGN KEY (batch_id,causally_related_attempt_id)
    REFERENCES contact_attempt(batch_id,attempt_id),
  FOREIGN KEY (supersedes_reply_event_id) REFERENCES reply_event(reply_event_id)
) STRICT;

-- Provider/recipient complaint signals are not delivery outcomes. Keeping them
-- in a separate append-only stream prevents a later complaint from rewriting the
-- delivery fact while still giving the execution gate a higher-priority STOP.
CREATE TABLE recipient_feedback_event (
  feedback_event_id TEXT PRIMARY KEY,
  feedback_stream_id TEXT NOT NULL,
  event_sequence INTEGER NOT NULL CHECK (event_sequence > 0),
  supersedes_feedback_event_id TEXT,
  batch_id TEXT NOT NULL,
  account_id TEXT NOT NULL,
  contact_id TEXT NOT NULL,
  attempt_id TEXT NOT NULL,
  feedback_code TEXT NOT NULL CHECK (feedback_code IN (
    'COMPLAINT','SPAM_REPORT','EXPLICIT_OPT_OUT','LEGAL_STOP','UNKNOWN'
  )),
  verification_status TEXT NOT NULL CHECK (verification_status IN (
    'VERIFIED','UNRESOLVED','REFUTED'
  )),
  suppression_event_id TEXT,
  feedback_ref_hash TEXT NOT NULL,
  occurred_at TEXT NOT NULL CHECK (datetime(occurred_at) IS NOT NULL),
  recorded_at TEXT NOT NULL CHECK (datetime(recorded_at) IS NOT NULL),
  CHECK (datetime(occurred_at) <= datetime(recorded_at)),
  CHECK (verification_status<>'VERIFIED'
      OR (feedback_code<>'UNKNOWN' AND suppression_event_id IS NOT NULL)),
  CHECK (verification_status<>'REFUTED' OR suppression_event_id IS NULL),
  CHECK (length(feedback_ref_hash)=64
         AND feedback_ref_hash=lower(feedback_ref_hash)
         AND feedback_ref_hash NOT GLOB '*[^0-9a-f]*'),
  UNIQUE (feedback_stream_id,event_sequence),
  FOREIGN KEY (batch_id,account_id)
    REFERENCES acquisition_account(batch_id,account_id),
  FOREIGN KEY (batch_id,contact_id) REFERENCES contact(batch_id,contact_id),
  FOREIGN KEY (batch_id,attempt_id) REFERENCES contact_attempt(batch_id,attempt_id),
  FOREIGN KEY (suppression_event_id)
    REFERENCES suppression_event(suppression_event_id),
  FOREIGN KEY (supersedes_feedback_event_id)
    REFERENCES recipient_feedback_event(feedback_event_id)
) STRICT;

CREATE TABLE meeting_event (
  meeting_event_id TEXT PRIMARY KEY,
  meeting_id TEXT NOT NULL,
  event_sequence INTEGER NOT NULL CHECK (event_sequence > 0),
  supersedes_meeting_event_id TEXT,
  batch_id TEXT NOT NULL,
  account_id TEXT NOT NULL,
  source_reply_event_id TEXT NOT NULL,
  status_code TEXT NOT NULL CHECK (status_code IN (
    'SCHEDULED','HELD','CANCELLED','NO_SHOW','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),
  CHECK (datetime(occurred_at) <= datetime(recorded_at)),
  UNIQUE (meeting_id,event_sequence),
  FOREIGN KEY (batch_id,account_id)
    REFERENCES acquisition_account(batch_id,account_id),
  FOREIGN KEY (source_reply_event_id) REFERENCES reply_event(reply_event_id),
  FOREIGN KEY (supersedes_meeting_event_id)
    REFERENCES meeting_event(meeting_event_id)
) STRICT;

-- Deliberate boundary: recording permission, AI-processing permission, artifact
-- retention, and transcript access belong to a separate privacy/data-handling
-- ledger. They are intentionally not fields on MeetingStatusEvent.

CREATE TABLE discovery_note (
  discovery_note_id TEXT PRIMARY KEY,
  batch_id TEXT NOT NULL,
  account_id TEXT NOT NULL,
  meeting_id TEXT NOT NULL,
  note_version INTEGER NOT NULL CHECK (note_version > 0),
  source_review_status TEXT NOT NULL CHECK (source_review_status IN (
    'CONFIRMED','REFUTED','UNKNOWN'
  )),
  note_ref_hash TEXT NOT NULL,
  disconfirming_evidence_ref_hash TEXT 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),
  occurred_at TEXT NOT NULL CHECK (datetime(occurred_at) IS NOT NULL),
  recorded_at TEXT NOT NULL CHECK (datetime(recorded_at) IS NOT NULL),
  reviewed_by_role TEXT NOT NULL,
  CHECK (datetime(occurred_at) <= datetime(recorded_at)),
  CHECK (datetime(outcome_cutoff_at) <= datetime(snapshot_as_of)),
  CHECK (length(note_ref_hash)=64
         AND note_ref_hash=lower(note_ref_hash)
         AND note_ref_hash NOT GLOB '*[^0-9a-f]*'),
  CHECK (length(disconfirming_evidence_ref_hash)=64
         AND disconfirming_evidence_ref_hash=lower(disconfirming_evidence_ref_hash)
         AND disconfirming_evidence_ref_hash NOT GLOB '*[^0-9a-f]*'),
  UNIQUE (batch_id,discovery_note_id),
  FOREIGN KEY (batch_id,account_id)
    REFERENCES acquisition_account(batch_id,account_id)
) STRICT;

CREATE TABLE source_touch (
  source_touch_id TEXT PRIMARY KEY,
  batch_id TEXT NOT NULL,
  account_id TEXT NOT NULL,
  touch_role_code TEXT NOT NULL CHECK (touch_role_code IN (
    'FIRST_TOUCH','ASSIST'
  )),
  source_code TEXT NOT NULL,
  source_evidence_ref_hash TEXT NOT NULL,
  occurred_at TEXT NOT NULL CHECK (datetime(occurred_at) IS NOT NULL),
  recorded_at TEXT NOT NULL CHECK (datetime(recorded_at) IS NOT NULL),
  CHECK (datetime(occurred_at) <= datetime(recorded_at)),
  CHECK (length(source_evidence_ref_hash)=64
         AND source_evidence_ref_hash=lower(source_evidence_ref_hash)
         AND source_evidence_ref_hash NOT GLOB '*[^0-9a-f]*'),
  UNIQUE (batch_id,source_touch_id),
  FOREIGN KEY (batch_id,account_id)
    REFERENCES acquisition_account(batch_id,account_id)
) STRICT;

CREATE UNIQUE INDEX one_first_touch_per_account_batch
ON source_touch(batch_id,account_id)
WHERE touch_role_code='FIRST_TOUCH';

CREATE TABLE qualification_handoff (
  qualification_package_id TEXT PRIMARY KEY,
  package_version INTEGER NOT NULL CHECK (package_version > 0),
  batch_id TEXT NOT NULL,
  account_id TEXT NOT NULL,
  discovery_note_id TEXT NOT NULL,
  first_touch_source_id TEXT NOT NULL,
  opportunity_id TEXT NOT NULL UNIQUE,
  proposed_motion_code TEXT NOT NULL
    CHECK (proposed_motion_code IN ('PILOT','PRODUCTION')),
  package_ref_hash TEXT 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),
  occurred_at TEXT NOT NULL CHECK (datetime(occurred_at) IS NOT NULL),
  recorded_at TEXT NOT NULL CHECK (datetime(recorded_at) IS NOT NULL),
  reviewed_by_role TEXT NOT NULL,
  CHECK (datetime(occurred_at) <= datetime(recorded_at)),
  CHECK (datetime(outcome_cutoff_at) <= datetime(snapshot_as_of)),
  CHECK (length(package_ref_hash)=64
         AND package_ref_hash=lower(package_ref_hash)
         AND package_ref_hash NOT GLOB '*[^0-9a-f]*'),
  UNIQUE (batch_id,qualification_package_id),
  FOREIGN KEY (batch_id,account_id)
    REFERENCES acquisition_account(batch_id,account_id),
  FOREIGN KEY (batch_id,discovery_note_id)
    REFERENCES discovery_note(batch_id,discovery_note_id),
  FOREIGN KEY (batch_id,first_touch_source_id)
    REFERENCES source_touch(batch_id,source_touch_id)
) STRICT;

CREATE TABLE handoff_requirement_evidence (
  qualification_package_id TEXT NOT NULL,
  requirement_code TEXT NOT NULL CHECK (requirement_code IN (
    'RECENT_WORKFLOW','IMPACT_BASELINE','NEXT_OCCURRENCE','STANDARD_SCOPE',
    'DATA_PERMISSION_SAFETY','STAKEHOLDER_PATH','BUDGET_PURCHASE_PATH',
    'MUTUAL_NEXT_ACTIONS','FOUNDER_DELIVERY_FEASIBILITY'
  )),
  result_code TEXT NOT NULL CHECK (result_code IN (
    'CONFIRMED','PASS','REFUTED','STOP','UNKNOWN'
  )),
  evidence_ref_hash TEXT NOT NULL,
  occurred_at TEXT NOT NULL CHECK (datetime(occurred_at) IS NOT NULL),
  recorded_at TEXT NOT NULL CHECK (datetime(recorded_at) IS NOT NULL),
  CHECK (datetime(occurred_at) <= datetime(recorded_at)),
  CHECK (length(evidence_ref_hash)=64
         AND evidence_ref_hash=lower(evidence_ref_hash)
         AND evidence_ref_hash NOT GLOB '*[^0-9a-f]*'),
  PRIMARY KEY (qualification_package_id,requirement_code),
  FOREIGN KEY (qualification_package_id)
    REFERENCES qualification_handoff(qualification_package_id)
) STRICT;

CREATE TABLE acquisition_decision (
  decision_event_id TEXT PRIMARY KEY,
  decision_stream_id TEXT NOT NULL,
  decision_sequence INTEGER NOT NULL CHECK (decision_sequence > 0),
  supersedes_decision_event_id TEXT,
  batch_id TEXT NOT NULL,
  account_id TEXT NOT NULL,
  decision_code TEXT NOT NULL CHECK (decision_code IN (
    'CONTINUE','QUALIFIED','LOST','NURTURE','PAUSE','STOP','UNKNOWN'
  )),
  primary_reason_code TEXT NOT NULL CHECK (primary_reason_code IN (
    'PROBLEM_ABSENT','LOW_IMPACT','WRONG_TIMING','WRONG_PERSON',
    'NO_PURCHASE_PATH','SCOPE_MISMATCH','TRUST_OR_PROOF',
    'SECURITY_PRIVACY_LEGAL','PRICE_VALUE_MISMATCH','CHANNEL_REJECTION',
    'QUALIFIED','INSUFFICIENT_EVIDENCE','OTHER'
  )),
  change_domain_code TEXT NOT NULL CHECK (change_domain_code IN (
    'SEGMENT','TRIGGER','MESSAGE','OFFER','PROOF','CHANNEL','NONE','UNKNOWN'
  )),
  qualification_package_id TEXT,
  stop_evidence_ref_hash TEXT,
  restart_trigger_ref_hash TEXT,
  earliest_restart_at TEXT
    CHECK (earliest_restart_at IS NULL OR datetime(earliest_restart_at) IS NOT NULL),
  decision_evidence_ref_hash TEXT 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),
  occurred_at TEXT NOT NULL CHECK (datetime(occurred_at) IS NOT NULL),
  recorded_at TEXT NOT NULL CHECK (datetime(recorded_at) IS NOT NULL),
  decision_owner_role TEXT NOT NULL,
  CHECK (datetime(occurred_at) <= datetime(recorded_at)),
  CHECK (datetime(outcome_cutoff_at) <= datetime(snapshot_as_of)),
  CHECK ((decision_code='NURTURE'
          AND restart_trigger_ref_hash IS NOT NULL
          AND earliest_restart_at IS NOT NULL)
      OR (decision_code<>'NURTURE'
          AND restart_trigger_ref_hash IS NULL
          AND earliest_restart_at IS NULL)),
  CHECK (decision_code<>'NURTURE'
      OR datetime(earliest_restart_at)>datetime(occurred_at)),
  CHECK ((decision_code='QUALIFIED' AND qualification_package_id IS NOT NULL
          AND primary_reason_code='QUALIFIED' AND change_domain_code='NONE')
      OR (decision_code<>'QUALIFIED' AND qualification_package_id IS NULL)),
  CHECK ((decision_code='STOP' AND stop_evidence_ref_hash IS NOT NULL)
      OR (decision_code<>'STOP' AND stop_evidence_ref_hash IS NULL)),
  CHECK (primary_reason_code<>'WRONG_PERSON'
      OR decision_code IN ('CONTINUE','PAUSE','UNKNOWN')),
  CHECK (decision_code<>'LOST'
      OR change_domain_code NOT IN ('NONE','UNKNOWN')),
  CHECK (length(decision_evidence_ref_hash)=64
         AND decision_evidence_ref_hash=lower(decision_evidence_ref_hash)
         AND decision_evidence_ref_hash NOT GLOB '*[^0-9a-f]*'),
  CHECK (restart_trigger_ref_hash IS NULL
      OR (length(restart_trigger_ref_hash)=64
          AND restart_trigger_ref_hash=lower(restart_trigger_ref_hash)
          AND restart_trigger_ref_hash NOT GLOB '*[^0-9a-f]*')),
  CHECK (stop_evidence_ref_hash IS NULL
      OR (length(stop_evidence_ref_hash)=64
          AND stop_evidence_ref_hash=lower(stop_evidence_ref_hash)
          AND stop_evidence_ref_hash NOT GLOB '*[^0-9a-f]*')),
  UNIQUE (decision_stream_id,decision_sequence),
  FOREIGN KEY (batch_id,account_id)
    REFERENCES acquisition_account(batch_id,account_id),
  FOREIGN KEY (qualification_package_id)
    REFERENCES qualification_handoff(qualification_package_id),
  FOREIGN KEY (supersedes_decision_event_id)
    REFERENCES acquisition_decision(decision_event_id)
) STRICT;

CREATE TABLE acquisition_cost_event (
  cost_event_id TEXT PRIMARY KEY,
  batch_id TEXT NOT NULL,
  account_id TEXT,
  cost_kind_code TEXT NOT NULL CHECK (cost_kind_code IN (
    'CASH','FOUNDER_TIME'
  )),
  component_code TEXT NOT NULL CHECK (component_code IN (
    'DATA_TOOL','SEND_PROVIDER','EVENT_PARTNER','TRAVEL_OTHER',
    'ACCOUNT_RESEARCH','LEGAL_PRIVACY_TERMS_REVIEW','MESSAGE_CLAIM_REVIEW',
    'SEND_CRM','REPLY_HANDLING','MEETING_DISCOVERY','ANALYSIS'
  )),
  cash_micros INTEGER,
  founder_minutes INTEGER,
  currency TEXT NOT NULL,
  source_event_ref_hash TEXT NOT NULL UNIQUE,
  occurred_at TEXT NOT NULL CHECK (datetime(occurred_at) IS NOT NULL),
  recorded_at TEXT NOT NULL CHECK (datetime(recorded_at) IS NOT NULL),
  CHECK (datetime(occurred_at) <= datetime(recorded_at)),
  CHECK ((cost_kind_code='CASH' AND cash_micros IS NOT NULL
          AND cash_micros >= 0 AND founder_minutes IS NULL
          AND component_code IN (
            'DATA_TOOL','SEND_PROVIDER','EVENT_PARTNER','TRAVEL_OTHER'
          ))
      OR (cost_kind_code='FOUNDER_TIME' AND founder_minutes IS NOT NULL
          AND founder_minutes >= 0 AND cash_micros IS NULL
          AND component_code IN (
            'ACCOUNT_RESEARCH','LEGAL_PRIVACY_TERMS_REVIEW',
            'MESSAGE_CLAIM_REVIEW','SEND_CRM','REPLY_HANDLING',
            'MEETING_DISCOVERY','ANALYSIS'
          ))),
  CHECK (length(source_event_ref_hash)=64
         AND source_event_ref_hash=lower(source_event_ref_hash)
         AND source_event_ref_hash NOT GLOB '*[^0-9a-f]*'),
  FOREIGN KEY (batch_id) REFERENCES segment_contract(batch_id),
  FOREIGN KEY (account_id) REFERENCES acquisition_account(account_id),
  FOREIGN KEY (currency) REFERENCES model_settings(base_currency)
) STRICT;

-- -----------------------------------------------------------------------------
-- Event-chain and future-evidence guards
-- -----------------------------------------------------------------------------

CREATE TRIGGER validate_contactability_review_before_insert
BEFORE INSERT ON contactability_review
BEGIN
  SELECT CASE WHEN datetime(NEW.occurred_at)>datetime((
    SELECT event_time_as_of FROM model_settings WHERE singleton_id=1
  )) OR datetime(NEW.recorded_at)>datetime((
    SELECT event_time_as_of FROM model_settings WHERE singleton_id=1
  )) THEN RAISE(ABORT,'contactability review exceeds model as-of') END;
  SELECT CASE WHEN NEW.review_sequence=1 AND NEW.supersedes_review_id IS NOT NULL
    THEN RAISE(ABORT,'first review cannot supersede another review') END;
  SELECT CASE WHEN NEW.review_sequence>1 AND NOT EXISTS (
    SELECT 1 FROM contactability_review p
    WHERE p.contactability_review_id=NEW.supersedes_review_id
      AND p.review_stream_id=NEW.review_stream_id
      AND p.review_sequence=NEW.review_sequence-1
      AND p.contact_id=NEW.contact_id
      AND p.channel_code=NEW.channel_code
      AND p.exact_purpose_code=NEW.exact_purpose_code
      AND p.exact_sender_id=NEW.exact_sender_id
      AND datetime(p.occurred_at)<=datetime(NEW.occurred_at)
      AND datetime(p.recorded_at)<=datetime(NEW.recorded_at)
  ) THEN RAISE(ABORT,'review stream is not an exact append-only tuple chain') END;
END;

CREATE TRIGGER validate_suppression_event_before_insert
BEFORE INSERT ON suppression_event
BEGIN
  SELECT CASE WHEN datetime(NEW.occurred_at)>datetime((
    SELECT event_time_as_of FROM model_settings WHERE singleton_id=1
  )) OR datetime(NEW.recorded_at)>datetime((
    SELECT event_time_as_of FROM model_settings WHERE singleton_id=1
  )) THEN RAISE(ABORT,'suppression event exceeds model as-of') END;
  SELECT CASE WHEN NEW.event_sequence=1
                   AND NEW.supersedes_suppression_event_id IS NOT NULL
    THEN RAISE(ABORT,'first suppression event cannot supersede') END;
  SELECT CASE WHEN NEW.event_sequence>1 AND NOT EXISTS (
    SELECT 1 FROM suppression_event p
    WHERE p.suppression_event_id=NEW.supersedes_suppression_event_id
      AND p.suppression_stream_id=NEW.suppression_stream_id
      AND p.event_sequence=NEW.event_sequence-1
      AND p.scope_code=NEW.scope_code
      AND p.scope_ref=NEW.scope_ref
      AND p.channel_scope=NEW.channel_scope
      AND p.purpose_scope=NEW.purpose_scope
      AND p.product_scope=NEW.product_scope
      AND p.sender_scope=NEW.sender_scope
      AND datetime(p.occurred_at)<=datetime(NEW.occurred_at)
      AND datetime(p.recorded_at)<=datetime(NEW.recorded_at)
  ) THEN RAISE(ABORT,'suppression stream is not an append-only scope chain') END;
  SELECT CASE WHEN NEW.action_code='RELEASE' AND NOT EXISTS (
    SELECT 1 FROM suppression_event p
    WHERE p.suppression_event_id=NEW.supersedes_suppression_event_id
      AND p.suppression_stream_id=NEW.suppression_stream_id
      AND p.event_sequence=NEW.event_sequence-1
      AND p.action_code='SUPPRESS'
      AND p.scope_code=NEW.scope_code AND p.scope_ref=NEW.scope_ref
      AND p.channel_scope=NEW.channel_scope
      AND p.purpose_scope=NEW.purpose_scope
      AND p.product_scope=NEW.product_scope
      AND p.sender_scope=NEW.sender_scope
      AND datetime(p.effective_from)<=datetime(NEW.occurred_at)
      AND (p.effective_until IS NULL
           OR datetime(NEW.occurred_at)<datetime(p.effective_until))
  ) THEN RAISE(ABORT,'release must exactly supersede one active suppression') END;
END;

-- One row per potential blocker makes precedence observable and auditable.
CREATE VIEW v_send_gate_findings AS
SELECT r.send_request_id,4 AS priority_order,'READY_TO_SEND' AS gate_state_code,
       'ready_seed' AS finding_code
FROM exact_send_request r
UNION ALL
SELECT r.send_request_id,1,'STOP','request_rejected'
FROM exact_send_request r WHERE r.approval_status='REJECTED'
UNION ALL
SELECT r.send_request_id,3,'UNKNOWN','request_not_human_approved'
FROM exact_send_request r WHERE r.approval_status='PENDING'
UNION ALL
SELECT r.send_request_id,3,'UNKNOWN','approval_time_or_expiry_invalid'
FROM exact_send_request r
WHERE r.approval_status='APPROVED' AND (
  datetime(r.approval_recorded_at)>datetime(r.scheduled_send_at)
  OR datetime(r.approval_expires_at)<datetime(r.scheduled_send_at)
)
UNION ALL
SELECT r.send_request_id,3,'UNKNOWN','send_or_request_is_future_evidence'
FROM exact_send_request r CROSS JOIN model_settings m
WHERE datetime(r.scheduled_send_at)>datetime(m.event_time_as_of)
   OR datetime(r.request_recorded_at)>datetime(m.event_time_as_of)
   OR (r.approval_recorded_at IS NOT NULL
       AND datetime(r.approval_recorded_at)>datetime(m.event_time_as_of))
UNION ALL
SELECT r.send_request_id,1,'STOP','account_not_eligible_selected'
FROM exact_send_request r
JOIN acquisition_account a
  ON a.batch_id=r.batch_id AND a.account_id=r.account_id
WHERE a.eligibility_status<>'ELIGIBLE' OR a.selected_for_batch<>1
UNION ALL
SELECT r.send_request_id,1,'STOP','segment_contract_hash_mismatch'
FROM exact_send_request r JOIN segment_contract b ON b.batch_id=r.batch_id
WHERE r.segment_contract_ref_hash<>b.contract_ref_hash
UNION ALL
SELECT r.send_request_id,1,'STOP','unsupported_channel_v1_email_only'
FROM exact_send_request r
WHERE r.channel_code<>'EMAIL'
UNION ALL
SELECT r.send_request_id,3,'UNKNOWN','contact_provenance_not_confirmed'
FROM exact_send_request r
JOIN contact c ON c.batch_id=r.batch_id AND c.contact_id=r.contact_id
WHERE c.account_id<>r.account_id OR c.provenance_status<>'CONFIRMED'
   OR c.channel_code<>r.channel_code
UNION ALL
SELECT r.send_request_id,3,'UNKNOWN','review_tuple_mismatch_or_future'
FROM exact_send_request r
LEFT JOIN contactability_review v
  ON v.contactability_review_id=r.contactability_review_id
WHERE v.contactability_review_id IS NULL
   OR v.batch_id<>r.batch_id OR v.account_id<>r.account_id
   OR v.contact_id<>r.contact_id OR v.channel_code<>r.channel_code
   OR v.exact_purpose_code<>r.exact_purpose_code
   OR v.exact_sender_id<>r.exact_sender_id
   OR datetime(v.occurred_at)>datetime(r.scheduled_send_at)
   OR datetime(v.recorded_at)>datetime(r.scheduled_send_at)
UNION ALL
SELECT r.send_request_id,1,'STOP','current_contactability_stop'
FROM exact_send_request r
WHERE EXISTS (
  SELECT 1 FROM contactability_review v
  WHERE v.contact_id=r.contact_id AND v.channel_code=r.channel_code
    AND v.exact_purpose_code=r.exact_purpose_code
    AND v.exact_sender_id=r.exact_sender_id
    AND v.review_result='STOP'
    AND datetime(v.occurred_at)<=datetime(r.scheduled_send_at)
    AND datetime(v.recorded_at)<=datetime(r.scheduled_send_at)
    AND NOT EXISTS (
      SELECT 1 FROM contactability_review later
      WHERE later.review_stream_id=v.review_stream_id
        AND later.review_sequence>v.review_sequence
        AND datetime(later.occurred_at)<=datetime(r.scheduled_send_at)
        AND datetime(later.recorded_at)<=datetime(r.scheduled_send_at)
    )
)
UNION ALL
SELECT r.send_request_id,1,'STOP','current_contactability_component_stop'
FROM exact_send_request r
WHERE EXISTS (
  SELECT 1 FROM contactability_review v
  WHERE v.contact_id=r.contact_id AND v.channel_code=r.channel_code
    AND v.exact_purpose_code=r.exact_purpose_code
    AND v.exact_sender_id=r.exact_sender_id
    AND 'STOP' IN (
      v.specific_email_law_result,v.specified_commercial_transactions_result,
      v.privacy_purpose_result,v.platform_terms_result,
      v.consent_or_exception_record_result,
      v.no_solicitation_public_source_result,v.sender_identity_result,
      v.sender_address_display_result,
      v.complaint_inquiry_contact_display_result,v.opt_out_result,
      v.authentication_result
    )
    AND datetime(v.occurred_at)<=datetime(r.scheduled_send_at)
    AND datetime(v.recorded_at)<=datetime(r.scheduled_send_at)
    AND NOT EXISTS (
      SELECT 1 FROM contactability_review later
      WHERE later.review_stream_id=v.review_stream_id
        AND later.review_sequence>v.review_sequence
        AND datetime(later.occurred_at)<=datetime(r.scheduled_send_at)
        AND datetime(later.recorded_at)<=datetime(r.scheduled_send_at)
    )
)
UNION ALL
SELECT r.send_request_id,3,'UNKNOWN','current_contactability_unknown_or_stale'
FROM exact_send_request r
WHERE EXISTS (
  SELECT 1 FROM contactability_review v
  WHERE v.contact_id=r.contact_id AND v.channel_code=r.channel_code
    AND v.exact_purpose_code=r.exact_purpose_code
    AND v.exact_sender_id=r.exact_sender_id
    AND (v.review_result='UNKNOWN'
         OR datetime(v.valid_until)<datetime(r.scheduled_send_at))
    AND datetime(v.occurred_at)<=datetime(r.scheduled_send_at)
    AND datetime(v.recorded_at)<=datetime(r.scheduled_send_at)
    AND NOT EXISTS (
      SELECT 1 FROM contactability_review later
      WHERE later.review_stream_id=v.review_stream_id
        AND later.review_sequence>v.review_sequence
        AND datetime(later.occurred_at)<=datetime(r.scheduled_send_at)
        AND datetime(later.recorded_at)<=datetime(r.scheduled_send_at)
    )
)
UNION ALL
SELECT r.send_request_id,3,'UNKNOWN','current_contactability_component_unknown'
FROM exact_send_request r
WHERE EXISTS (
  SELECT 1 FROM contactability_review v
  WHERE v.contact_id=r.contact_id AND v.channel_code=r.channel_code
    AND v.exact_purpose_code=r.exact_purpose_code
    AND v.exact_sender_id=r.exact_sender_id
    AND 'UNKNOWN' IN (
      v.specific_email_law_result,v.specified_commercial_transactions_result,
      v.privacy_purpose_result,v.platform_terms_result,
      v.consent_or_exception_record_result,
      v.no_solicitation_public_source_result,v.sender_identity_result,
      v.sender_address_display_result,
      v.complaint_inquiry_contact_display_result,v.opt_out_result,
      v.authentication_result
    )
    AND datetime(v.occurred_at)<=datetime(r.scheduled_send_at)
    AND datetime(v.recorded_at)<=datetime(r.scheduled_send_at)
    AND NOT EXISTS (
      SELECT 1 FROM contactability_review later
      WHERE later.review_stream_id=v.review_stream_id
        AND later.review_sequence>v.review_sequence
        AND datetime(later.occurred_at)<=datetime(r.scheduled_send_at)
        AND datetime(later.recorded_at)<=datetime(r.scheduled_send_at)
    )
)
UNION ALL
SELECT r.send_request_id,3,'UNKNOWN','referenced_review_is_not_current'
FROM exact_send_request r
JOIN contactability_review v
  ON v.contactability_review_id=r.contactability_review_id
WHERE EXISTS (
  SELECT 1 FROM contactability_review later
  WHERE later.review_stream_id=v.review_stream_id
    AND later.review_sequence>v.review_sequence
    AND datetime(later.occurred_at)<=datetime(r.scheduled_send_at)
    AND datetime(later.recorded_at)<=datetime(r.scheduled_send_at)
)
UNION ALL
SELECT r.send_request_id,1,'STOP','active_suppression_stop'
FROM exact_send_request r JOIN contact c ON c.contact_id=r.contact_id
WHERE EXISTS (
  SELECT 1 FROM suppression_event s
  WHERE s.action_code='SUPPRESS'
    AND s.reason_code IN (
      'EXPLICIT_OPT_OUT','LEGAL_STOP','COMPLAINT','SPAM_REPORT','HARD_BOUNCE',
      'WRONG_RECIPIENT'
    )
    AND (s.channel_scope='ALL' OR s.channel_scope=r.channel_code)
    AND (s.purpose_scope='ALL' OR s.purpose_scope=r.exact_purpose_code)
    AND (s.product_scope='ALL' OR s.product_scope=r.product_code)
    AND (s.sender_scope='ALL' OR s.sender_scope=r.exact_sender_id)
    AND datetime(s.effective_from)<=datetime(r.scheduled_send_at)
    AND (s.effective_until IS NULL
         OR datetime(r.scheduled_send_at)<datetime(s.effective_until))
    AND (s.scope_code='ALL'
      OR (s.scope_code='CONTACT' AND s.scope_ref=r.contact_id)
      OR (s.scope_code='ACCOUNT' AND s.scope_ref=r.account_id)
      OR (s.scope_code='DOMAIN' AND s.scope_ref=c.domain_ref_hash))
    AND datetime(s.occurred_at)<=datetime(r.scheduled_send_at)
    AND datetime(s.recorded_at)<=datetime(r.scheduled_send_at)
    AND NOT EXISTS (
      SELECT 1 FROM suppression_event later
      WHERE later.suppression_stream_id=s.suppression_stream_id
        AND later.event_sequence>s.event_sequence
        AND datetime(later.occurred_at)<=datetime(r.scheduled_send_at)
        AND datetime(later.recorded_at)<=datetime(r.scheduled_send_at)
    )
)
UNION ALL
SELECT r.send_request_id,2,'PAUSE','active_suppression_pause'
FROM exact_send_request r JOIN contact c ON c.contact_id=r.contact_id
WHERE EXISTS (
  SELECT 1 FROM suppression_event s
  WHERE s.action_code='SUPPRESS'
    AND s.reason_code IN (
      'SOFT_BOUNCE','DEFERRED','INVESTIGATION','PROVIDER_STOP','OTHER'
    )
    AND (s.channel_scope='ALL' OR s.channel_scope=r.channel_code)
    AND (s.purpose_scope='ALL' OR s.purpose_scope=r.exact_purpose_code)
    AND (s.product_scope='ALL' OR s.product_scope=r.product_code)
    AND (s.sender_scope='ALL' OR s.sender_scope=r.exact_sender_id)
    AND datetime(s.effective_from)<=datetime(r.scheduled_send_at)
    AND (s.effective_until IS NULL
         OR datetime(r.scheduled_send_at)<datetime(s.effective_until))
    AND (s.scope_code='ALL'
      OR (s.scope_code='CONTACT' AND s.scope_ref=r.contact_id)
      OR (s.scope_code='ACCOUNT' AND s.scope_ref=r.account_id)
      OR (s.scope_code='DOMAIN' AND s.scope_ref=c.domain_ref_hash))
    AND datetime(s.occurred_at)<=datetime(r.scheduled_send_at)
    AND datetime(s.recorded_at)<=datetime(r.scheduled_send_at)
    AND NOT EXISTS (
      SELECT 1 FROM suppression_event later
      WHERE later.suppression_stream_id=s.suppression_stream_id
        AND later.event_sequence>s.event_sequence
        AND datetime(later.occurred_at)<=datetime(r.scheduled_send_at)
        AND datetime(later.recorded_at)<=datetime(r.scheduled_send_at)
    )
)
UNION ALL
SELECT r.send_request_id,1,'STOP','message_retired_or_wrong_scope'
FROM exact_send_request r
LEFT JOIN message_version m ON m.message_version_id=r.message_version_id
WHERE m.message_version_id IS NULL OR m.batch_id<>r.batch_id
   OR m.channel_code<>r.channel_code
   OR m.exact_purpose_code<>r.exact_purpose_code
   OR (m.retired_at IS NOT NULL
       AND datetime(m.retired_at)<=datetime(r.scheduled_send_at))
UNION ALL
SELECT r.send_request_id,3,'UNKNOWN','message_not_approved_effective_or_recorded'
FROM exact_send_request r JOIN message_version m
  ON m.message_version_id=r.message_version_id
WHERE m.approved_at IS NULL OR m.approval_recorded_at IS NULL
   OR m.effective_from IS NULL
   OR datetime(m.approved_at)>datetime(r.scheduled_send_at)
   OR datetime(m.approval_recorded_at)>datetime(r.scheduled_send_at)
   OR datetime(m.effective_from)>datetime(r.scheduled_send_at)
   OR (m.retirement_recorded_at IS NOT NULL
       AND datetime(m.retirement_recorded_at)>datetime((
         SELECT event_time_as_of FROM model_settings WHERE singleton_id=1
       )))
UNION ALL
SELECT r.send_request_id,1,'STOP','claim_stop'
FROM exact_send_request r
WHERE EXISTS (
  SELECT 1 FROM message_claim mc JOIN claim_evidence c USING (claim_evidence_id)
  WHERE mc.message_version_id=r.message_version_id AND c.review_result='STOP'
)
UNION ALL
SELECT r.send_request_id,3,'UNKNOWN','claim_missing_unknown_stale_or_future'
FROM exact_send_request r
WHERE NOT EXISTS (
        SELECT 1 FROM message_claim mc
        WHERE mc.message_version_id=r.message_version_id
      )
   OR EXISTS (
        SELECT 1 FROM message_claim mc JOIN claim_evidence c USING (claim_evidence_id)
        WHERE mc.message_version_id=r.message_version_id
          AND (c.review_result='UNKNOWN'
            OR datetime(c.valid_until)<datetime(r.scheduled_send_at)
            OR datetime(c.occurred_at)>datetime(r.scheduled_send_at)
            OR datetime(c.recorded_at)>datetime(r.scheduled_send_at))
      )
UNION ALL
SELECT r.send_request_id,1,'STOP','trigger_refuted_or_wrong_account'
FROM exact_send_request r LEFT JOIN trigger_evidence t
  ON t.trigger_evidence_id=r.trigger_evidence_id
WHERE t.trigger_evidence_id IS NULL OR t.batch_id<>r.batch_id
   OR t.account_id<>r.account_id OR t.evidence_status='REFUTED'
UNION ALL
SELECT r.send_request_id,3,'UNKNOWN','trigger_unknown_stale_or_future'
FROM exact_send_request r JOIN trigger_evidence t
  ON t.trigger_evidence_id=r.trigger_evidence_id
WHERE t.evidence_status IN ('UNKNOWN','STALE')
   OR datetime(t.valid_until)<datetime(r.scheduled_send_at)
   OR datetime(t.occurred_at)>datetime(r.scheduled_send_at)
   OR datetime(t.recorded_at)>datetime(r.scheduled_send_at)
UNION ALL
SELECT r.send_request_id,2,'PAUSE','attempt_cap_reached'
FROM exact_send_request r JOIN segment_contract b ON b.batch_id=r.batch_id
WHERE (SELECT count(*) FROM contact_attempt a
       WHERE a.batch_id=r.batch_id AND a.contact_id=r.contact_id
         AND datetime(a.occurred_at)<datetime(r.scheduled_send_at)
         AND datetime(a.recorded_at)<=datetime(r.scheduled_send_at))
      >= b.max_attempts_per_contact
UNION ALL
SELECT r.send_request_id,2,'PAUSE','attempt_spacing_not_elapsed'
FROM exact_send_request r JOIN segment_contract b ON b.batch_id=r.batch_id
WHERE (SELECT max(a.occurred_at) FROM contact_attempt a
       WHERE a.batch_id=r.batch_id AND a.contact_id=r.contact_id
         AND datetime(a.occurred_at)<datetime(r.scheduled_send_at)
         AND datetime(a.recorded_at)<=datetime(r.scheduled_send_at)) IS NOT NULL
  AND datetime(r.scheduled_send_at) < datetime(
        (SELECT max(a.occurred_at) FROM contact_attempt a
         WHERE a.batch_id=r.batch_id AND a.contact_id=r.contact_id
           AND datetime(a.occurred_at)<datetime(r.scheduled_send_at)
           AND datetime(a.recorded_at)<=datetime(r.scheduled_send_at)),
        '+' || b.min_attempt_spacing_minutes || ' minutes')
UNION ALL
SELECT r.send_request_id,2,'PAUSE','prior_soft_bounce_or_deferred'
FROM exact_send_request r
WHERE EXISTS (
  SELECT 1 FROM contact_attempt a JOIN delivery_event d USING (attempt_id)
  WHERE a.batch_id=r.batch_id AND a.contact_id=r.contact_id
    AND d.outcome_code IN ('SOFT_BOUNCE','DEFERRED')
    AND datetime(d.occurred_at)<datetime(r.scheduled_send_at)
    AND datetime(d.recorded_at)<=datetime(r.scheduled_send_at)
)
UNION ALL
SELECT r.send_request_id,1,'STOP','prior_hard_bounce'
FROM exact_send_request r
WHERE EXISTS (
  SELECT 1 FROM contact_attempt a JOIN delivery_event d USING (attempt_id)
  WHERE a.batch_id=r.batch_id AND a.contact_id=r.contact_id
    AND d.outcome_code='HARD_BOUNCE'
    AND datetime(d.occurred_at)<datetime(r.scheduled_send_at)
    AND datetime(d.recorded_at)<=datetime(r.scheduled_send_at)
)
UNION ALL
SELECT r.send_request_id,1,'STOP','prior_complaint_or_stop_feedback'
FROM exact_send_request r
WHERE EXISTS (
  SELECT 1 FROM recipient_feedback_event f
  WHERE f.batch_id=r.batch_id
    AND (f.contact_id=r.contact_id OR f.account_id=r.account_id)
    AND f.verification_status='VERIFIED'
    AND f.feedback_code IN (
      'COMPLAINT','SPAM_REPORT','EXPLICIT_OPT_OUT','LEGAL_STOP'
    )
    AND datetime(f.occurred_at)<datetime(r.scheduled_send_at)
    AND datetime(f.recorded_at)<=datetime(r.scheduled_send_at)
    AND NOT EXISTS (
      SELECT 1 FROM recipient_feedback_event later
      WHERE later.feedback_stream_id=f.feedback_stream_id
        AND later.event_sequence>f.event_sequence
        AND datetime(later.occurred_at)<=datetime(r.scheduled_send_at)
        AND datetime(later.recorded_at)<=datetime(r.scheduled_send_at)
    )
)
UNION ALL
SELECT r.send_request_id,2,'PAUSE','unresolved_feedback_requires_review'
FROM exact_send_request r
WHERE EXISTS (
  SELECT 1 FROM recipient_feedback_event f
  WHERE f.batch_id=r.batch_id
    AND (f.contact_id=r.contact_id OR f.account_id=r.account_id)
    AND f.verification_status='UNRESOLVED'
    AND datetime(f.occurred_at)<datetime(r.scheduled_send_at)
    AND datetime(f.recorded_at)<=datetime(r.scheduled_send_at)
    AND NOT EXISTS (
      SELECT 1 FROM recipient_feedback_event later
      WHERE later.feedback_stream_id=f.feedback_stream_id
        AND later.event_sequence>f.event_sequence
        AND datetime(later.occurred_at)<=datetime(r.scheduled_send_at)
        AND datetime(later.recorded_at)<=datetime(r.scheduled_send_at)
    )
)
UNION ALL
SELECT r.send_request_id,3,'UNKNOWN','prior_unknown_receipt_requires_human_review'
FROM exact_send_request r
WHERE r.prior_unknown_receipt_review_ref_hash IS NULL AND EXISTS (
  SELECT 1 FROM contact_attempt a
  WHERE a.batch_id=r.batch_id AND a.contact_id=r.contact_id
    AND a.send_receipt_status='UNKNOWN'
    AND datetime(a.occurred_at)<datetime(r.scheduled_send_at)
    AND datetime(a.recorded_at)<=datetime(r.scheduled_send_at)
)
UNION ALL
SELECT r.send_request_id,1,'STOP','prior_legal_stop_reply'
FROM exact_send_request r
WHERE EXISTS (
  SELECT 1 FROM reply_event p
  WHERE p.batch_id=r.batch_id
    AND (p.contact_id=r.contact_id OR p.account_id=r.account_id)
    AND p.reply_actor_code='HUMAN' AND p.outcome_code='LEGAL_STOP'
    AND datetime(p.occurred_at)<datetime(r.scheduled_send_at)
    AND datetime(p.recorded_at)<=datetime(r.scheduled_send_at)
);

CREATE VIEW v_send_gate AS
SELECT f.send_request_id,
       CASE min(f.priority_order)
         WHEN 1 THEN 'STOP'
         WHEN 2 THEN 'PAUSE'
         WHEN 3 THEN 'UNKNOWN'
         ELSE 'READY_TO_SEND'
       END AS derived_gate_state,
       group_concat(
         CASE WHEN f.priority_order<4 THEN f.finding_code END,'|'
       ) AS blocking_findings
FROM v_send_gate_findings f
GROUP BY f.send_request_id;

CREATE TRIGGER validate_contact_attempt_before_insert
BEFORE INSERT ON contact_attempt
BEGIN
  SELECT CASE WHEN datetime(NEW.occurred_at)>datetime((
    SELECT event_time_as_of FROM model_settings WHERE singleton_id=1
  )) OR datetime(NEW.recorded_at)>datetime((
    SELECT event_time_as_of FROM model_settings WHERE singleton_id=1
  )) THEN RAISE(ABORT,'attempt exceeds model as-of') END;
  SELECT CASE WHEN NOT EXISTS (
    SELECT 1 FROM exact_send_request r
    WHERE r.send_request_id=NEW.send_request_id
      AND r.batch_id=NEW.batch_id AND r.account_id=NEW.account_id
      AND r.contact_id=NEW.contact_id AND r.approval_status='APPROVED'
      AND datetime(r.scheduled_send_at)=datetime(NEW.occurred_at)
      AND datetime(r.approval_recorded_at)<=datetime(NEW.occurred_at)
      AND datetime(r.approval_expires_at)>=datetime(NEW.occurred_at)
  ) THEN RAISE(ABORT,'attempt lacks exact current human approval') END;
  SELECT CASE WHEN coalesce((
    SELECT derived_gate_state FROM v_send_gate
    WHERE send_request_id=NEW.send_request_id
  ),'UNKNOWN')<>'READY_TO_SEND'
    THEN RAISE(ABORT,'attempt gate is STOP PAUSE or UNKNOWN') END;
  SELECT CASE WHEN NEW.execution_authority_code<>'HUMAN_ONLY'
                   OR NEW.auto_retry_authorized<>0
    THEN RAISE(ABORT,'automatic retry is outside v1 execution authority') END;
  SELECT CASE WHEN EXISTS (
    SELECT 1 FROM contact_attempt prior
    WHERE prior.batch_id=NEW.batch_id AND prior.contact_id=NEW.contact_id
      AND prior.send_receipt_status='UNKNOWN'
      AND datetime(prior.occurred_at)<datetime(NEW.occurred_at)
      AND datetime(prior.recorded_at)<=datetime(NEW.occurred_at)
  ) AND NOT EXISTS (
    SELECT 1 FROM exact_send_request r JOIN contact_attempt prior
      ON prior.attempt_id=NEW.retry_of_attempt_id
    WHERE r.send_request_id=NEW.send_request_id
      AND r.prior_unknown_receipt_review_ref_hash IS NOT NULL
      AND prior.batch_id=NEW.batch_id AND prior.contact_id=NEW.contact_id
      AND prior.send_receipt_status='UNKNOWN'
      AND datetime(r.approved_at)>datetime(prior.recorded_at)
  ) THEN RAISE(ABORT,'unknown receipt requires explicit human retry review') END;
  SELECT CASE WHEN NEW.attempt_sequence<>(
    SELECT count(*)+1 FROM contact_attempt a
    WHERE a.batch_id=NEW.batch_id AND a.contact_id=NEW.contact_id
      AND datetime(a.occurred_at)<datetime(NEW.occurred_at)
  ) THEN RAISE(ABORT,'attempt sequence is not contiguous') END;
  SELECT CASE WHEN (
    SELECT count(*) FROM contact_attempt a
    WHERE a.batch_id=NEW.batch_id AND a.contact_id=NEW.contact_id
      AND datetime(a.occurred_at)<datetime(NEW.occurred_at)
  ) >= (
    SELECT max_attempts_per_contact FROM segment_contract
    WHERE batch_id=NEW.batch_id
  ) THEN RAISE(ABORT,'attempt cap reached') END;
  SELECT CASE WHEN datetime(NEW.occurred_at) < datetime(
    (SELECT max(a.occurred_at) FROM contact_attempt a
     WHERE a.batch_id=NEW.batch_id AND a.contact_id=NEW.contact_id
       AND datetime(a.occurred_at)<datetime(NEW.occurred_at)),
    '+' || (SELECT min_attempt_spacing_minutes FROM segment_contract
            WHERE batch_id=NEW.batch_id) || ' minutes'
  ) THEN RAISE(ABORT,'minimum attempt spacing not elapsed') END;
END;

CREATE TRIGGER validate_delivery_event_before_insert
BEFORE INSERT ON delivery_event
BEGIN
  SELECT CASE WHEN datetime(NEW.occurred_at)>datetime((
    SELECT event_time_as_of FROM model_settings WHERE singleton_id=1
  )) OR datetime(NEW.recorded_at)>datetime((
    SELECT event_time_as_of FROM model_settings WHERE singleton_id=1
  )) THEN RAISE(ABORT,'delivery exceeds model as-of') END;
  SELECT CASE WHEN NOT EXISTS (
    SELECT 1 FROM contact_attempt a WHERE a.attempt_id=NEW.attempt_id
      AND datetime(a.occurred_at)<=datetime(NEW.occurred_at)
      AND datetime(a.recorded_at)<=datetime(NEW.recorded_at)
  ) THEN RAISE(ABORT,'delivery predates its attempt evidence') END;
  SELECT CASE WHEN NEW.outcome_code='HARD_BOUNCE' AND NOT EXISTS (
    SELECT 1 FROM suppression_event s
    JOIN contact_attempt a ON a.attempt_id=NEW.attempt_id
    JOIN exact_send_request r ON r.send_request_id=a.send_request_id
    JOIN contact c ON c.contact_id=a.contact_id
    WHERE s.suppression_event_id=NEW.suppression_event_id
      AND s.action_code='SUPPRESS' AND s.reason_code='HARD_BOUNCE'
      AND (s.channel_scope='ALL' OR s.channel_scope=r.channel_code)
      AND (s.purpose_scope='ALL' OR s.purpose_scope=r.exact_purpose_code)
      AND (s.product_scope='ALL' OR s.product_scope=r.product_code)
      AND (s.sender_scope='ALL' OR s.sender_scope=r.exact_sender_id)
      AND (s.scope_code='ALL'
        OR (s.scope_code='CONTACT' AND s.scope_ref=a.contact_id)
        OR (s.scope_code='ACCOUNT' AND s.scope_ref=a.account_id)
        OR (s.scope_code='DOMAIN' AND s.scope_ref=c.domain_ref_hash))
      AND datetime(s.effective_from)<=datetime(NEW.occurred_at)
      AND datetime(s.occurred_at)<=datetime(NEW.occurred_at)
      AND datetime(s.recorded_at)<=datetime(NEW.recorded_at)
  ) THEN RAISE(ABORT,'hard bounce requires exact active suppression evidence') END;
  SELECT CASE WHEN NEW.event_sequence=1
                   AND NEW.supersedes_delivery_event_id IS NOT NULL
    THEN RAISE(ABORT,'first delivery event cannot supersede') END;
  SELECT CASE WHEN NEW.event_sequence>1 AND NOT EXISTS (
    SELECT 1 FROM delivery_event p
    WHERE p.delivery_event_id=NEW.supersedes_delivery_event_id
      AND p.attempt_id=NEW.attempt_id
      AND p.event_sequence=NEW.event_sequence-1
      AND datetime(p.occurred_at)<=datetime(NEW.occurred_at)
      AND datetime(p.recorded_at)<=datetime(NEW.recorded_at)
  ) THEN RAISE(ABORT,'delivery stream is not append-only') END;
END;

CREATE TRIGGER validate_reply_event_before_insert
BEFORE INSERT ON reply_event
BEGIN
  SELECT CASE WHEN datetime(NEW.occurred_at)>datetime((
    SELECT event_time_as_of FROM model_settings WHERE singleton_id=1
  )) OR datetime(NEW.recorded_at)>datetime((
    SELECT event_time_as_of FROM model_settings WHERE singleton_id=1
  )) THEN RAISE(ABORT,'reply exceeds model as-of') END;
  SELECT CASE WHEN NOT EXISTS (
    SELECT 1 FROM contact_attempt a
    WHERE a.attempt_id=NEW.causally_related_attempt_id
      AND a.batch_id=NEW.batch_id AND a.account_id=NEW.account_id
      AND a.contact_id=NEW.contact_id
      AND datetime(a.occurred_at)<=datetime(NEW.occurred_at)
      AND datetime(a.recorded_at)<=datetime(NEW.recorded_at)
  ) THEN RAISE(ABORT,'reply does not follow its exact attempt') END;
  SELECT CASE WHEN NEW.reply_sequence=1
                   AND NEW.supersedes_reply_event_id IS NOT NULL
    THEN RAISE(ABORT,'first reply event cannot supersede') END;
  SELECT CASE WHEN NEW.reply_sequence>1 AND NOT EXISTS (
    SELECT 1 FROM reply_event p
    WHERE p.reply_event_id=NEW.supersedes_reply_event_id
      AND p.thread_id=NEW.thread_id
      AND p.reply_sequence=NEW.reply_sequence-1
      AND datetime(p.occurred_at)<=datetime(NEW.occurred_at)
      AND datetime(p.recorded_at)<=datetime(NEW.recorded_at)
  ) THEN RAISE(ABORT,'reply stream is not append-only') END;
END;

CREATE TRIGGER validate_feedback_event_before_insert
BEFORE INSERT ON recipient_feedback_event
BEGIN
  SELECT CASE WHEN datetime(NEW.occurred_at)>datetime((
    SELECT event_time_as_of FROM model_settings WHERE singleton_id=1
  )) OR datetime(NEW.recorded_at)>datetime((
    SELECT event_time_as_of FROM model_settings WHERE singleton_id=1
  )) THEN RAISE(ABORT,'feedback exceeds model as-of') END;
  SELECT CASE WHEN NOT EXISTS (
    SELECT 1 FROM contact_attempt a
    WHERE a.attempt_id=NEW.attempt_id AND a.batch_id=NEW.batch_id
      AND a.account_id=NEW.account_id AND a.contact_id=NEW.contact_id
      AND datetime(a.occurred_at)<=datetime(NEW.occurred_at)
      AND datetime(a.recorded_at)<=datetime(NEW.recorded_at)
  ) THEN RAISE(ABORT,'feedback does not follow its exact attempt') END;
  SELECT CASE WHEN NEW.verification_status='VERIFIED' AND NOT EXISTS (
    SELECT 1 FROM suppression_event s
    JOIN contact_attempt a ON a.attempt_id=NEW.attempt_id
    JOIN exact_send_request r ON r.send_request_id=a.send_request_id
    JOIN contact c ON c.contact_id=a.contact_id
    WHERE s.suppression_event_id=NEW.suppression_event_id
      AND s.action_code='SUPPRESS'
      AND s.reason_code=NEW.feedback_code
      AND (s.channel_scope='ALL' OR s.channel_scope=r.channel_code)
      AND (s.purpose_scope='ALL' OR s.purpose_scope=r.exact_purpose_code)
      AND (s.product_scope='ALL' OR s.product_scope=r.product_code)
      AND (s.sender_scope='ALL' OR s.sender_scope=r.exact_sender_id)
      AND (s.scope_code='ALL'
        OR (s.scope_code='CONTACT' AND s.scope_ref=a.contact_id)
        OR (s.scope_code='ACCOUNT' AND s.scope_ref=a.account_id)
        OR (s.scope_code='DOMAIN' AND s.scope_ref=c.domain_ref_hash))
      AND datetime(s.effective_from)<=datetime(NEW.occurred_at)
      AND datetime(s.occurred_at)<=datetime(NEW.occurred_at)
      AND datetime(s.recorded_at)<=datetime(NEW.recorded_at)
  ) THEN RAISE(ABORT,'verified feedback requires exact STOP suppression') END;
  SELECT CASE WHEN NEW.event_sequence=1
                   AND NEW.supersedes_feedback_event_id IS NOT NULL
    THEN RAISE(ABORT,'first feedback event cannot supersede') END;
  SELECT CASE WHEN NEW.event_sequence>1 AND NOT EXISTS (
    SELECT 1 FROM recipient_feedback_event p
    WHERE p.feedback_event_id=NEW.supersedes_feedback_event_id
      AND p.feedback_stream_id=NEW.feedback_stream_id
      AND p.event_sequence=NEW.event_sequence-1
      AND p.batch_id=NEW.batch_id AND p.account_id=NEW.account_id
      AND p.contact_id=NEW.contact_id AND p.attempt_id=NEW.attempt_id
      AND datetime(p.occurred_at)<=datetime(NEW.occurred_at)
      AND datetime(p.recorded_at)<=datetime(NEW.recorded_at)
  ) THEN RAISE(ABORT,'feedback stream is not append-only') END;
END;

CREATE TRIGGER validate_meeting_event_before_insert
BEFORE INSERT ON meeting_event
BEGIN
  SELECT CASE WHEN datetime(NEW.occurred_at)>datetime((
    SELECT event_time_as_of FROM model_settings WHERE singleton_id=1
  )) OR datetime(NEW.recorded_at)>datetime((
    SELECT event_time_as_of FROM model_settings WHERE singleton_id=1
  )) THEN RAISE(ABORT,'meeting exceeds model as-of') END;
  SELECT CASE WHEN NOT EXISTS (
    SELECT 1 FROM reply_event r
    WHERE r.reply_event_id=NEW.source_reply_event_id
      AND r.batch_id=NEW.batch_id AND r.account_id=NEW.account_id
      AND r.reply_actor_code='HUMAN'
      AND datetime(r.occurred_at)<=datetime(NEW.occurred_at)
      AND datetime(r.recorded_at)<=datetime(NEW.recorded_at)
  ) THEN RAISE(ABORT,'meeting lacks prior human reply evidence') END;
  SELECT CASE WHEN NEW.event_sequence=1
                   AND NEW.supersedes_meeting_event_id IS NOT NULL
    THEN RAISE(ABORT,'first meeting event cannot supersede') END;
  SELECT CASE WHEN NEW.event_sequence>1 AND NOT EXISTS (
    SELECT 1 FROM meeting_event p
    WHERE p.meeting_event_id=NEW.supersedes_meeting_event_id
      AND p.meeting_id=NEW.meeting_id
      AND p.event_sequence=NEW.event_sequence-1
      AND datetime(p.occurred_at)<=datetime(NEW.occurred_at)
      AND datetime(p.recorded_at)<=datetime(NEW.recorded_at)
  ) THEN RAISE(ABORT,'meeting stream is not append-only') END;
END;

CREATE TRIGGER validate_discovery_note_before_insert
BEFORE INSERT ON discovery_note
BEGIN
  SELECT CASE WHEN datetime(NEW.occurred_at)>datetime((
    SELECT event_time_as_of FROM model_settings WHERE singleton_id=1
  )) OR datetime(NEW.recorded_at)>datetime((
    SELECT event_time_as_of FROM model_settings WHERE singleton_id=1
  )) THEN RAISE(ABORT,'discovery note exceeds model as-of') END;
  SELECT CASE WHEN NOT EXISTS (
    SELECT 1 FROM meeting_event m
    WHERE m.meeting_id=NEW.meeting_id AND m.batch_id=NEW.batch_id
      AND m.account_id=NEW.account_id AND m.status_code='HELD'
      AND datetime(m.occurred_at)<=datetime(NEW.occurred_at)
      AND datetime(m.recorded_at)<=datetime(NEW.recorded_at)
      AND NOT EXISTS (
        SELECT 1 FROM meeting_event later
        WHERE later.meeting_id=m.meeting_id
          AND later.event_sequence>m.event_sequence
          AND datetime(later.occurred_at)<=datetime(NEW.occurred_at)
          AND datetime(later.recorded_at)<=datetime(NEW.recorded_at)
      )
  ) THEN RAISE(ABORT,'discovery requires a current held meeting') END;
END;

CREATE TRIGGER validate_source_touch_before_insert
BEFORE INSERT ON source_touch
BEGIN
  SELECT CASE WHEN datetime(NEW.occurred_at)>datetime((
    SELECT event_time_as_of FROM model_settings WHERE singleton_id=1
  )) OR datetime(NEW.recorded_at)>datetime((
    SELECT event_time_as_of FROM model_settings WHERE singleton_id=1
  )) THEN RAISE(ABORT,'source touch exceeds model as-of') END;
END;

CREATE TRIGGER validate_qualification_handoff_before_insert
BEFORE INSERT ON qualification_handoff
BEGIN
  SELECT CASE WHEN datetime(NEW.occurred_at)>datetime((
    SELECT event_time_as_of FROM model_settings WHERE singleton_id=1
  )) OR datetime(NEW.recorded_at)>datetime((
    SELECT event_time_as_of FROM model_settings WHERE singleton_id=1
  )) THEN RAISE(ABORT,'handoff exceeds model as-of') END;
  SELECT CASE WHEN NOT EXISTS (
    SELECT 1 FROM discovery_note d
    WHERE d.discovery_note_id=NEW.discovery_note_id
      AND d.batch_id=NEW.batch_id AND d.account_id=NEW.account_id
      AND d.source_review_status='CONFIRMED'
      AND datetime(d.occurred_at)<=datetime(NEW.occurred_at)
      AND datetime(d.recorded_at)<=datetime(NEW.recorded_at)
      AND datetime(d.outcome_cutoff_at)<=datetime(NEW.outcome_cutoff_at)
      AND datetime(d.snapshot_as_of)<=datetime(NEW.snapshot_as_of)
  ) THEN RAISE(ABORT,'handoff lacks reviewed discovery evidence') END;
  SELECT CASE WHEN NOT EXISTS (
    SELECT 1 FROM source_touch s
    WHERE s.source_touch_id=NEW.first_touch_source_id
      AND s.batch_id=NEW.batch_id AND s.account_id=NEW.account_id
      AND s.touch_role_code='FIRST_TOUCH'
      AND datetime(s.occurred_at)<=datetime(NEW.occurred_at)
      AND datetime(s.recorded_at)<=datetime(NEW.recorded_at)
  ) THEN RAISE(ABORT,'handoff lacks the exact first touch') END;
END;

CREATE VIEW v_handoff_readiness AS
SELECT h.qualification_package_id,h.batch_id,h.account_id,h.opportunity_id,
       count(e.requirement_code) AS represented_requirement_count,
       sum(CASE WHEN e.result_code IN ('CONFIRMED','PASS') THEN 1 ELSE 0 END)
         AS satisfied_requirement_count,
       sum(CASE WHEN e.result_code IN ('REFUTED','STOP') THEN 1 ELSE 0 END)
         AS stop_requirement_count,
       sum(CASE WHEN e.result_code='UNKNOWN' THEN 1 ELSE 0 END)
         AS unknown_requirement_count,
       CASE
         WHEN sum(CASE WHEN e.result_code IN ('REFUTED','STOP') THEN 1 ELSE 0 END)>0
           THEN 'STOP'
         WHEN count(e.requirement_code)<9
           OR sum(CASE WHEN e.result_code='UNKNOWN' THEN 1 ELSE 0 END)>0
           THEN 'UNKNOWN'
         WHEN sum(CASE WHEN e.result_code IN ('CONFIRMED','PASS') THEN 1 ELSE 0 END)=9
           THEN 'READY_FOR_CHAPTER_11'
         ELSE 'UNKNOWN'
       END AS derived_handoff_state
FROM qualification_handoff h
LEFT JOIN handoff_requirement_evidence e
  ON e.qualification_package_id=h.qualification_package_id
 AND datetime(e.occurred_at)<=datetime(h.outcome_cutoff_at)
 AND datetime(e.recorded_at)<=datetime(h.snapshot_as_of)
GROUP BY h.qualification_package_id,h.batch_id,h.account_id,h.opportunity_id;

CREATE TRIGGER validate_decision_before_insert
BEFORE INSERT ON acquisition_decision
BEGIN
  SELECT CASE WHEN datetime(NEW.occurred_at)>datetime((
    SELECT event_time_as_of FROM model_settings WHERE singleton_id=1
  )) OR datetime(NEW.recorded_at)>datetime((
    SELECT event_time_as_of FROM model_settings WHERE singleton_id=1
  )) THEN RAISE(ABORT,'decision exceeds model as-of') END;
  SELECT CASE WHEN datetime(NEW.outcome_cutoff_at)>datetime(NEW.occurred_at)
    THEN RAISE(ABORT,'decision predates its evidence cutoff') END;
  SELECT CASE WHEN NEW.decision_sequence=1
                   AND NEW.supersedes_decision_event_id IS NOT NULL
    THEN RAISE(ABORT,'first decision cannot supersede') END;
  SELECT CASE WHEN NEW.decision_sequence>1 AND NOT EXISTS (
    SELECT 1 FROM acquisition_decision p
    WHERE p.decision_event_id=NEW.supersedes_decision_event_id
      AND p.decision_stream_id=NEW.decision_stream_id
      AND p.decision_sequence=NEW.decision_sequence-1
      AND p.batch_id=NEW.batch_id AND p.account_id=NEW.account_id
      AND datetime(p.occurred_at)<=datetime(NEW.occurred_at)
      AND datetime(p.recorded_at)<=datetime(NEW.recorded_at)
  ) THEN RAISE(ABORT,'decision stream is not append-only') END;
  SELECT CASE WHEN NEW.decision_code='QUALIFIED' AND NOT EXISTS (
    SELECT 1 FROM v_handoff_readiness h
    JOIN qualification_handoff q USING (qualification_package_id)
    WHERE h.qualification_package_id=NEW.qualification_package_id
      AND h.batch_id=NEW.batch_id AND h.account_id=NEW.account_id
      AND h.derived_handoff_state='READY_FOR_CHAPTER_11'
      AND datetime(q.outcome_cutoff_at)=datetime(NEW.outcome_cutoff_at)
      AND datetime(q.snapshot_as_of)=datetime(NEW.snapshot_as_of)
      AND datetime(q.occurred_at)<=datetime(NEW.occurred_at)
      AND datetime(q.recorded_at)<=datetime(NEW.recorded_at)
  ) THEN RAISE(ABORT,'QUALIFIED requires a ready evidence handoff') END;
END;

CREATE TRIGGER validate_cost_event_before_insert
BEFORE INSERT ON acquisition_cost_event
BEGIN
  SELECT CASE WHEN datetime(NEW.occurred_at)>datetime((
    SELECT event_time_as_of FROM model_settings WHERE singleton_id=1
  )) OR datetime(NEW.recorded_at)>datetime((
    SELECT event_time_as_of FROM model_settings WHERE singleton_id=1
  )) THEN RAISE(ABORT,'cost event exceeds model as-of') END;
END;

-- All operational evidence tables are append-only. Corrections use a new event,
-- stream sequence, or version row rather than UPDATE/DELETE.
CREATE TRIGGER immutable_segment_contract_update BEFORE UPDATE ON segment_contract
BEGIN SELECT RAISE(ABORT,'segment contract version is immutable'); END;
CREATE TRIGGER immutable_segment_contract_delete BEFORE DELETE ON segment_contract
BEGIN SELECT RAISE(ABORT,'segment contract version is immutable'); END;
CREATE TRIGGER immutable_acquisition_account_update BEFORE UPDATE ON acquisition_account
BEGIN SELECT RAISE(ABORT,'account selection record is immutable'); END;
CREATE TRIGGER immutable_acquisition_account_delete BEFORE DELETE ON acquisition_account
BEGIN SELECT RAISE(ABORT,'account selection record is immutable'); END;
CREATE TRIGGER immutable_trigger_evidence_update BEFORE UPDATE ON trigger_evidence
BEGIN SELECT RAISE(ABORT,'trigger evidence version is immutable'); END;
CREATE TRIGGER immutable_trigger_evidence_delete BEFORE DELETE ON trigger_evidence
BEGIN SELECT RAISE(ABORT,'trigger evidence version is immutable'); END;
CREATE TRIGGER immutable_contact_update BEFORE UPDATE ON contact
BEGIN SELECT RAISE(ABORT,'contact provenance record is immutable'); END;
CREATE TRIGGER immutable_contact_delete BEFORE DELETE ON contact
BEGIN SELECT RAISE(ABORT,'contact provenance record is immutable'); END;
CREATE TRIGGER immutable_contactability_review_update BEFORE UPDATE ON contactability_review
BEGIN SELECT RAISE(ABORT,'contactability review is append-only'); END;
CREATE TRIGGER immutable_contactability_review_delete BEFORE DELETE ON contactability_review
BEGIN SELECT RAISE(ABORT,'contactability review is append-only'); END;
CREATE TRIGGER immutable_suppression_event_update BEFORE UPDATE ON suppression_event
BEGIN SELECT RAISE(ABORT,'suppression event is append-only'); END;
CREATE TRIGGER immutable_suppression_event_delete BEFORE DELETE ON suppression_event
BEGIN SELECT RAISE(ABORT,'suppression event is append-only'); END;
CREATE TRIGGER immutable_contact_attempt_update BEFORE UPDATE ON contact_attempt
BEGIN SELECT RAISE(ABORT,'contact attempt is append-only'); END;
CREATE TRIGGER immutable_contact_attempt_delete BEFORE DELETE ON contact_attempt
BEGIN SELECT RAISE(ABORT,'contact attempt is append-only'); END;
CREATE TRIGGER immutable_delivery_event_update BEFORE UPDATE ON delivery_event
BEGIN SELECT RAISE(ABORT,'delivery event is append-only'); END;
CREATE TRIGGER immutable_delivery_event_delete BEFORE DELETE ON delivery_event
BEGIN SELECT RAISE(ABORT,'delivery event is append-only'); END;
CREATE TRIGGER immutable_feedback_event_update BEFORE UPDATE ON recipient_feedback_event
BEGIN SELECT RAISE(ABORT,'feedback event is append-only'); END;
CREATE TRIGGER immutable_feedback_event_delete BEFORE DELETE ON recipient_feedback_event
BEGIN SELECT RAISE(ABORT,'feedback event is append-only'); END;
CREATE TRIGGER immutable_reply_event_update BEFORE UPDATE ON reply_event
BEGIN SELECT RAISE(ABORT,'reply event is append-only'); END;
CREATE TRIGGER immutable_reply_event_delete BEFORE DELETE ON reply_event
BEGIN SELECT RAISE(ABORT,'reply event is append-only'); END;
CREATE TRIGGER immutable_meeting_event_update BEFORE UPDATE ON meeting_event
BEGIN SELECT RAISE(ABORT,'meeting event is append-only'); END;
CREATE TRIGGER immutable_meeting_event_delete BEFORE DELETE ON meeting_event
BEGIN SELECT RAISE(ABORT,'meeting event is append-only'); END;
CREATE TRIGGER immutable_discovery_note_update BEFORE UPDATE ON discovery_note
BEGIN SELECT RAISE(ABORT,'discovery note is append-only'); END;
CREATE TRIGGER immutable_discovery_note_delete BEFORE DELETE ON discovery_note
BEGIN SELECT RAISE(ABORT,'discovery note is append-only'); END;
CREATE TRIGGER immutable_source_touch_update BEFORE UPDATE ON source_touch
BEGIN SELECT RAISE(ABORT,'source touch is append-only'); END;
CREATE TRIGGER immutable_source_touch_delete BEFORE DELETE ON source_touch
BEGIN SELECT RAISE(ABORT,'source touch is append-only'); END;
CREATE TRIGGER immutable_claim_evidence_update BEFORE UPDATE ON claim_evidence
BEGIN SELECT RAISE(ABORT,'claim evidence version is immutable'); END;
CREATE TRIGGER immutable_claim_evidence_delete BEFORE DELETE ON claim_evidence
BEGIN SELECT RAISE(ABORT,'claim evidence version is immutable'); END;
CREATE TRIGGER immutable_message_version_update BEFORE UPDATE ON message_version
BEGIN SELECT RAISE(ABORT,'message version is immutable'); END;
CREATE TRIGGER immutable_message_version_delete BEFORE DELETE ON message_version
BEGIN SELECT RAISE(ABORT,'message version is immutable'); END;
CREATE TRIGGER immutable_message_claim_update BEFORE UPDATE ON message_claim
BEGIN SELECT RAISE(ABORT,'message claim set is immutable'); END;
CREATE TRIGGER immutable_message_claim_delete BEFORE DELETE ON message_claim
BEGIN SELECT RAISE(ABORT,'message claim set is immutable'); END;
CREATE TRIGGER immutable_exact_send_request_update BEFORE UPDATE ON exact_send_request
BEGIN SELECT RAISE(ABORT,'exact send request and approval are immutable'); END;
CREATE TRIGGER immutable_exact_send_request_delete BEFORE DELETE ON exact_send_request
BEGIN SELECT RAISE(ABORT,'exact send request and approval are immutable'); END;
CREATE TRIGGER immutable_qualification_handoff_update BEFORE UPDATE ON qualification_handoff
BEGIN SELECT RAISE(ABORT,'qualification handoff is append-only'); END;
CREATE TRIGGER immutable_qualification_handoff_delete BEFORE DELETE ON qualification_handoff
BEGIN SELECT RAISE(ABORT,'qualification handoff is append-only'); END;
CREATE TRIGGER immutable_handoff_requirement_update BEFORE UPDATE ON handoff_requirement_evidence
BEGIN SELECT RAISE(ABORT,'handoff requirement evidence is append-only'); END;
CREATE TRIGGER immutable_handoff_requirement_delete BEFORE DELETE ON handoff_requirement_evidence
BEGIN SELECT RAISE(ABORT,'handoff requirement evidence is append-only'); END;
CREATE TRIGGER immutable_acquisition_decision_update BEFORE UPDATE ON acquisition_decision
BEGIN SELECT RAISE(ABORT,'acquisition decision is append-only'); END;
CREATE TRIGGER immutable_acquisition_decision_delete BEFORE DELETE ON acquisition_decision
BEGIN SELECT RAISE(ABORT,'acquisition decision is append-only'); END;
CREATE TRIGGER immutable_acquisition_cost_event_update BEFORE UPDATE ON acquisition_cost_event
BEGIN SELECT RAISE(ABORT,'acquisition cost event is append-only'); END;
CREATE TRIGGER immutable_acquisition_cost_event_delete BEFORE DELETE ON acquisition_cost_event
BEGIN SELECT RAISE(ABORT,'acquisition cost event is append-only'); END;

-- -----------------------------------------------------------------------------
-- Frozen-cutoff analytical views
-- -----------------------------------------------------------------------------

CREATE VIEW v_cutoff_attempt AS
SELECT a.*
FROM contact_attempt a JOIN segment_contract b USING (batch_id)
WHERE datetime(a.occurred_at)<=datetime(b.outcome_cutoff_at)
  AND datetime(a.recorded_at)<=datetime(b.snapshot_as_of);

CREATE VIEW v_cutoff_human_reply AS
SELECT r.*
FROM reply_event r JOIN segment_contract b USING (batch_id)
WHERE r.reply_actor_code='HUMAN'
  AND datetime(r.occurred_at)<=datetime(b.outcome_cutoff_at)
  AND datetime(r.recorded_at)<=datetime(b.snapshot_as_of);

CREATE VIEW v_late_human_reply AS
SELECT r.*
FROM reply_event r JOIN segment_contract b USING (batch_id)
CROSS JOIN model_settings m
WHERE r.reply_actor_code='HUMAN'
  AND datetime(r.occurred_at)>datetime(b.outcome_cutoff_at)
  AND datetime(r.occurred_at)<=datetime(m.event_time_as_of)
  AND datetime(r.recorded_at)<=datetime(m.event_time_as_of);

-- Late events happened after the outcome cutoff. Restatement candidates happened
-- on/before the outcome cutoff but were recorded after snapshot_as_of. Neither is
-- silently inserted into a frozen snapshot; a later snapshot may consume them.
CREATE VIEW v_post_cutoff_event_inventory AS
SELECT r.batch_id,'REPLY' AS event_domain,r.reply_event_id AS event_id,
       r.occurred_at,r.recorded_at,b.outcome_cutoff_at AS outcome_cutoff_at,
       b.snapshot_as_of AS snapshot_as_of,
       CASE WHEN datetime(r.occurred_at)>datetime(b.outcome_cutoff_at)
              THEN 'LATE_EVENT'
            ELSE 'RESTATEMENT_CANDIDATE' END AS late_or_restatement
FROM reply_event r JOIN segment_contract b USING (batch_id)
CROSS JOIN model_settings m
WHERE datetime(r.recorded_at)<=datetime(m.event_time_as_of)
  AND (datetime(r.occurred_at)>datetime(b.outcome_cutoff_at)
    OR (datetime(r.occurred_at)<=datetime(b.outcome_cutoff_at)
        AND datetime(r.recorded_at)>datetime(b.snapshot_as_of)))
UNION ALL
SELECT a.batch_id,'DELIVERY',d.delivery_event_id,d.occurred_at,d.recorded_at,
       b.outcome_cutoff_at,b.snapshot_as_of,
       CASE WHEN datetime(d.occurred_at)>datetime(b.outcome_cutoff_at)
              THEN 'LATE_EVENT'
            ELSE 'RESTATEMENT_CANDIDATE' END
FROM delivery_event d JOIN contact_attempt a USING (attempt_id)
JOIN segment_contract b ON b.batch_id=a.batch_id CROSS JOIN model_settings m
WHERE datetime(d.recorded_at)<=datetime(m.event_time_as_of)
  AND (datetime(d.occurred_at)>datetime(b.outcome_cutoff_at)
    OR (datetime(d.occurred_at)<=datetime(b.outcome_cutoff_at)
        AND datetime(d.recorded_at)>datetime(b.snapshot_as_of)))
UNION ALL
SELECT f.batch_id,'COMPLAINT',f.feedback_event_id,f.occurred_at,f.recorded_at,
       b.outcome_cutoff_at,b.snapshot_as_of,
       CASE WHEN datetime(f.occurred_at)>datetime(b.outcome_cutoff_at)
              THEN 'LATE_EVENT'
            ELSE 'RESTATEMENT_CANDIDATE' END
FROM recipient_feedback_event f JOIN segment_contract b USING (batch_id)
CROSS JOIN model_settings m
WHERE f.feedback_code IN ('COMPLAINT','SPAM_REPORT')
  AND datetime(f.recorded_at)<=datetime(m.event_time_as_of)
  AND (datetime(f.occurred_at)>datetime(b.outcome_cutoff_at)
    OR (datetime(f.occurred_at)<=datetime(b.outcome_cutoff_at)
        AND datetime(f.recorded_at)>datetime(b.snapshot_as_of)))
UNION ALL
SELECT f.batch_id,'FEEDBACK',f.feedback_event_id,f.occurred_at,f.recorded_at,
       b.outcome_cutoff_at,b.snapshot_as_of,
       CASE WHEN datetime(f.occurred_at)>datetime(b.outcome_cutoff_at)
              THEN 'LATE_EVENT'
            ELSE 'RESTATEMENT_CANDIDATE' END
FROM recipient_feedback_event f JOIN segment_contract b USING (batch_id)
CROSS JOIN model_settings m
WHERE f.feedback_code NOT IN ('COMPLAINT','SPAM_REPORT')
  AND datetime(f.recorded_at)<=datetime(m.event_time_as_of)
  AND (datetime(f.occurred_at)>datetime(b.outcome_cutoff_at)
    OR (datetime(f.occurred_at)<=datetime(b.outcome_cutoff_at)
        AND datetime(f.recorded_at)>datetime(b.snapshot_as_of)))
UNION ALL
SELECT m.batch_id,'MEETING',m.meeting_event_id,m.occurred_at,m.recorded_at,
       b.outcome_cutoff_at,b.snapshot_as_of,
       CASE WHEN datetime(m.occurred_at)>datetime(b.outcome_cutoff_at)
              THEN 'LATE_EVENT'
            ELSE 'RESTATEMENT_CANDIDATE' END
FROM meeting_event m JOIN segment_contract b USING (batch_id)
CROSS JOIN model_settings s
WHERE datetime(m.recorded_at)<=datetime(s.event_time_as_of)
  AND (datetime(m.occurred_at)>datetime(b.outcome_cutoff_at)
    OR (datetime(m.occurred_at)<=datetime(b.outcome_cutoff_at)
        AND datetime(m.recorded_at)>datetime(b.snapshot_as_of)))
UNION ALL
SELECT b.batch_id,'SUPPRESSION',s.suppression_event_id,s.occurred_at,s.recorded_at,
       b.outcome_cutoff_at,b.snapshot_as_of,
       CASE WHEN datetime(s.occurred_at)>datetime(b.outcome_cutoff_at)
              THEN 'LATE_EVENT'
            ELSE 'RESTATEMENT_CANDIDATE' END
FROM suppression_event s CROSS JOIN segment_contract b CROSS JOIN model_settings m
WHERE datetime(s.recorded_at)<=datetime(m.event_time_as_of)
  AND (datetime(s.occurred_at)>datetime(b.outcome_cutoff_at)
    OR (datetime(s.occurred_at)<=datetime(b.outcome_cutoff_at)
        AND datetime(s.recorded_at)>datetime(b.snapshot_as_of)))
  AND EXISTS (
    SELECT 1 FROM acquisition_account a
    LEFT JOIN contact c ON c.batch_id=a.batch_id AND c.account_id=a.account_id
    WHERE a.batch_id=b.batch_id AND (
      s.scope_code='ALL'
      OR (s.scope_code='ACCOUNT' AND s.scope_ref=a.account_id)
      OR (s.scope_code='CONTACT' AND s.scope_ref=c.contact_id)
      OR (s.scope_code='DOMAIN' AND s.scope_ref=c.domain_ref_hash)
    )
  );

CREATE VIEW v_cutoff_latest_contactability AS
SELECT v.*
FROM contactability_review v JOIN segment_contract b USING (batch_id)
WHERE datetime(v.occurred_at)<=datetime(b.outcome_cutoff_at)
  AND datetime(v.recorded_at)<=datetime(b.snapshot_as_of)
  AND NOT EXISTS (
    SELECT 1 FROM contactability_review later
    WHERE later.review_stream_id=v.review_stream_id
      AND later.review_sequence>v.review_sequence
      AND datetime(later.occurred_at)<=datetime(b.outcome_cutoff_at)
      AND datetime(later.recorded_at)<=datetime(b.snapshot_as_of)
  );

CREATE VIEW v_cutoff_latest_delivery AS
SELECT d.*,a.batch_id,a.account_id,a.contact_id
FROM delivery_event d JOIN contact_attempt a USING (attempt_id)
JOIN segment_contract b ON b.batch_id=a.batch_id
WHERE datetime(d.occurred_at)<=datetime(b.outcome_cutoff_at)
  AND datetime(d.recorded_at)<=datetime(b.snapshot_as_of)
  AND NOT EXISTS (
    SELECT 1 FROM delivery_event later
    WHERE later.attempt_id=d.attempt_id
      AND later.event_sequence>d.event_sequence
      AND datetime(later.occurred_at)<=datetime(b.outcome_cutoff_at)
      AND datetime(later.recorded_at)<=datetime(b.snapshot_as_of)
  );

CREATE VIEW v_cutoff_latest_meeting AS
SELECT m.*
FROM meeting_event m JOIN segment_contract b USING (batch_id)
WHERE datetime(m.occurred_at)<=datetime(b.outcome_cutoff_at)
  AND datetime(m.recorded_at)<=datetime(b.snapshot_as_of)
  AND NOT EXISTS (
    SELECT 1 FROM meeting_event later
    WHERE later.meeting_id=m.meeting_id
      AND later.event_sequence>m.event_sequence
      AND datetime(later.occurred_at)<=datetime(b.outcome_cutoff_at)
      AND datetime(later.recorded_at)<=datetime(b.snapshot_as_of)
  );

CREATE VIEW v_cutoff_latest_decision AS
SELECT d.*
FROM acquisition_decision d JOIN segment_contract b USING (batch_id)
WHERE datetime(d.occurred_at)<=datetime(b.outcome_cutoff_at)
  AND datetime(d.recorded_at)<=datetime(b.snapshot_as_of)
  AND NOT EXISTS (
    SELECT 1 FROM acquisition_decision later
    WHERE later.decision_stream_id=d.decision_stream_id
      AND later.decision_sequence>d.decision_sequence
      AND datetime(later.occurred_at)<=datetime(b.outcome_cutoff_at)
      AND datetime(later.recorded_at)<=datetime(b.snapshot_as_of)
  );

CREATE VIEW v_account_funnel AS
SELECT b.batch_id,
  (SELECT count(*) FROM acquisition_account a
   WHERE a.batch_id=b.batch_id AND a.eligibility_status='ELIGIBLE')
    AS eligible_accounts,
  (SELECT count(*) FROM acquisition_account a
   WHERE a.batch_id=b.batch_id AND a.eligibility_status='ELIGIBLE'
     AND a.selected_for_batch=1) AS selected_accounts,
  (SELECT count(DISTINCT a.account_id) FROM v_cutoff_attempt a
   WHERE a.batch_id=b.batch_id) AS attempted_accounts,
  (SELECT count(DISTINCT r.account_id) FROM v_cutoff_human_reply r
   WHERE r.batch_id=b.batch_id) AS human_replied_accounts,
  (SELECT count(DISTINCT a.account_id) FROM v_cutoff_attempt a
   WHERE a.batch_id=b.batch_id AND NOT EXISTS (
     SELECT 1 FROM v_cutoff_human_reply r
     WHERE r.batch_id=a.batch_id AND r.account_id=a.account_id
   )) AS no_human_reply_by_cutoff_accounts,
  (SELECT count(DISTINCT r.account_id) FROM v_late_human_reply r
   WHERE r.batch_id=b.batch_id) AS late_reply_accounts,
  (SELECT count(DISTINCT m.account_id) FROM v_cutoff_latest_meeting m
   WHERE m.batch_id=b.batch_id AND m.status_code='HELD') AS held_meeting_accounts,
  (SELECT count(DISTINCT d.account_id) FROM discovery_note d
   WHERE d.batch_id=b.batch_id AND d.source_review_status='CONFIRMED'
     AND datetime(d.occurred_at)<=datetime(b.outcome_cutoff_at)
     AND datetime(d.recorded_at)<=datetime(b.snapshot_as_of))
    AS discovery_evidenced_accounts,
  (SELECT count(DISTINCT d.account_id) FROM v_cutoff_latest_decision d
   WHERE d.batch_id=b.batch_id AND d.decision_code='QUALIFIED')
    AS qualified_accounts,
  (SELECT count(*) FROM acquisition_account a
   WHERE a.batch_id=b.batch_id AND a.selected_for_batch=1
     AND NOT EXISTS (
       SELECT 1 FROM contact c
       WHERE c.batch_id=a.batch_id AND c.account_id=a.account_id
     )) AS selected_without_contact_accounts
FROM segment_contract b;

CREATE VIEW v_contact_funnel AS
SELECT b.batch_id,
  (SELECT count(DISTINCT v.contact_id) FROM v_cutoff_latest_contactability v
   WHERE v.batch_id=b.batch_id) AS reviewed_contacts,
  (SELECT count(DISTINCT v.contact_id) FROM v_cutoff_latest_contactability v
   WHERE v.batch_id=b.batch_id AND v.review_result='PASS'
     AND datetime(v.valid_until)>=datetime(b.outcome_cutoff_at))
    AS contactable_pass_contacts_at_cutoff,
  (SELECT count(DISTINCT v.contact_id) FROM v_cutoff_latest_contactability v
   WHERE v.batch_id=b.batch_id AND v.review_result='STOP') AS stop_contacts,
  (SELECT count(DISTINCT v.contact_id) FROM v_cutoff_latest_contactability v
   WHERE v.batch_id=b.batch_id AND (
     v.review_result='UNKNOWN'
     OR datetime(v.valid_until)<datetime(b.outcome_cutoff_at)
   )) AS unknown_or_stale_contacts,
  (SELECT count(DISTINCT a.contact_id) FROM v_cutoff_attempt a
   WHERE a.batch_id=b.batch_id) AS attempted_contacts,
  (SELECT count(DISTINCT r.contact_id) FROM v_cutoff_human_reply r
   WHERE r.batch_id=b.batch_id) AS human_replied_contacts,
  (SELECT count(DISTINCT a.contact_id) FROM v_cutoff_attempt a
   WHERE a.batch_id=b.batch_id AND NOT EXISTS (
     SELECT 1 FROM v_cutoff_human_reply r
     WHERE r.batch_id=a.batch_id AND r.contact_id=a.contact_id
   )) AS no_human_reply_by_cutoff_contacts,
  (SELECT count(DISTINCT r.contact_id) FROM v_cutoff_human_reply r
   WHERE r.batch_id=b.batch_id AND r.outcome_code='WRONG_PERSON')
    AS wrong_person_contacts,
  (SELECT count(DISTINCT r.contact_id) FROM v_late_human_reply r
   WHERE r.batch_id=b.batch_id) AS late_reply_contacts
FROM segment_contract b;

CREATE VIEW v_attempt_funnel AS
SELECT b.batch_id,
  (SELECT count(*) FROM v_cutoff_attempt a
   WHERE a.batch_id=b.batch_id) AS attempted_sends,
  (SELECT count(DISTINCT a.attempt_id) FROM v_cutoff_attempt a
   WHERE a.batch_id=b.batch_id AND EXISTS (
     SELECT 1 FROM delivery_event d
     WHERE d.attempt_id=a.attempt_id AND d.outcome_code IN (
       'ACCEPTED','DELIVERED_SIGNAL'
     ) AND datetime(d.occurred_at)<=datetime(b.outcome_cutoff_at)
       AND datetime(d.recorded_at)<=datetime(b.snapshot_as_of)
   )) AS accepted_signal_attempts,
  (SELECT count(*) FROM v_cutoff_latest_delivery d
   WHERE d.batch_id=b.batch_id AND d.outcome_code='DELIVERED_SIGNAL')
    AS current_delivered_signal_attempts,
  (SELECT count(*) FROM v_cutoff_latest_delivery d
   WHERE d.batch_id=b.batch_id AND d.outcome_code='HARD_BOUNCE')
    AS current_hard_bounce_attempts,
  (SELECT count(*) FROM v_cutoff_attempt a
   WHERE a.batch_id=b.batch_id AND (
     NOT EXISTS (SELECT 1 FROM v_cutoff_latest_delivery d
                 WHERE d.attempt_id=a.attempt_id)
     OR EXISTS (SELECT 1 FROM v_cutoff_latest_delivery d
                WHERE d.attempt_id=a.attempt_id AND d.outcome_code='UNKNOWN')
   )) AS delivery_unknown_attempts,
  (SELECT count(DISTINCT r.causally_related_attempt_id)
   FROM v_cutoff_human_reply r WHERE r.batch_id=b.batch_id)
    AS human_replied_attempts,
  (SELECT count(*) FROM v_cutoff_attempt a
   WHERE a.batch_id=b.batch_id AND NOT EXISTS (
     SELECT 1 FROM v_cutoff_human_reply r
     WHERE r.causally_related_attempt_id=a.attempt_id
   )) AS no_human_reply_by_cutoff_attempts,
  (SELECT count(DISTINCT r.causally_related_attempt_id)
   FROM v_late_human_reply r WHERE r.batch_id=b.batch_id)
    AS late_reply_attempts
FROM segment_contract b;

CREATE VIEW v_account_attribution AS
SELECT a.batch_id,a.account_id,
  (SELECT s.source_touch_id FROM source_touch s
   JOIN segment_contract b ON b.batch_id=s.batch_id
   WHERE s.batch_id=a.batch_id AND s.account_id=a.account_id
     AND s.touch_role_code='FIRST_TOUCH'
     AND datetime(s.occurred_at)<=datetime(b.outcome_cutoff_at)
     AND datetime(s.recorded_at)<=datetime(b.snapshot_as_of)
   ORDER BY datetime(s.occurred_at),s.source_touch_id LIMIT 1)
    AS first_touch_source_id,
  (SELECT s.source_code FROM source_touch s
   JOIN segment_contract b ON b.batch_id=s.batch_id
   WHERE s.batch_id=a.batch_id AND s.account_id=a.account_id
     AND s.touch_role_code='FIRST_TOUCH'
     AND datetime(s.occurred_at)<=datetime(b.outcome_cutoff_at)
     AND datetime(s.recorded_at)<=datetime(b.snapshot_as_of)
   ORDER BY datetime(s.occurred_at),s.source_touch_id LIMIT 1)
    AS first_touch_source_code,
  (SELECT group_concat(x.source_touch_id,'|') FROM (
     SELECT s.source_touch_id FROM source_touch s
     JOIN segment_contract b ON b.batch_id=s.batch_id
     WHERE s.batch_id=a.batch_id AND s.account_id=a.account_id
       AND s.touch_role_code='ASSIST'
       AND datetime(s.occurred_at)<=datetime(b.outcome_cutoff_at)
       AND datetime(s.recorded_at)<=datetime(b.snapshot_as_of)
     ORDER BY datetime(s.occurred_at),s.source_touch_id
   ) x) AS assist_touch_ids,
  (SELECT group_concat(x.source_code,'|') FROM (
     SELECT s.source_code FROM source_touch s
     JOIN segment_contract b ON b.batch_id=s.batch_id
     WHERE s.batch_id=a.batch_id AND s.account_id=a.account_id
       AND s.touch_role_code='ASSIST'
       AND datetime(s.occurred_at)<=datetime(b.outcome_cutoff_at)
       AND datetime(s.recorded_at)<=datetime(b.snapshot_as_of)
     ORDER BY datetime(s.occurred_at),s.source_touch_id
   ) x) AS assist_source_codes
FROM acquisition_account a;

CREATE VIEW v_batch_acquisition_economics AS
SELECT b.batch_id,m.base_currency,
  coalesce((SELECT sum(c.cash_micros) FROM acquisition_cost_event c
    WHERE c.batch_id=b.batch_id AND c.cost_kind_code='CASH'
      AND datetime(c.occurred_at)<=datetime(b.outcome_cutoff_at)
      AND datetime(c.recorded_at)<=datetime(b.snapshot_as_of)),0)
    AS cash_cost_micros,
  coalesce((SELECT sum(c.founder_minutes) FROM acquisition_cost_event c
    WHERE c.batch_id=b.batch_id AND c.cost_kind_code='FOUNDER_TIME'
      AND datetime(c.occurred_at)<=datetime(b.outcome_cutoff_at)
      AND datetime(c.recorded_at)<=datetime(b.snapshot_as_of)),0)
    AS founder_minutes,
  coalesce((SELECT sum(c.founder_minutes) FROM acquisition_cost_event c
    WHERE c.batch_id=b.batch_id AND c.cost_kind_code='FOUNDER_TIME'
      AND datetime(c.occurred_at)<=datetime(b.outcome_cutoff_at)
      AND datetime(c.recorded_at)<=datetime(b.snapshot_as_of)),0)
      * m.founder_internal_hourly_value_micros / 60
    AS founder_time_cost_micros,
  coalesce((SELECT sum(c.cash_micros) FROM acquisition_cost_event c
    WHERE c.batch_id=b.batch_id AND c.cost_kind_code='CASH'
      AND datetime(c.occurred_at)<=datetime(b.outcome_cutoff_at)
      AND datetime(c.recorded_at)<=datetime(b.snapshot_as_of)),0)
    + coalesce((SELECT sum(c.founder_minutes) FROM acquisition_cost_event c
      WHERE c.batch_id=b.batch_id AND c.cost_kind_code='FOUNDER_TIME'
        AND datetime(c.occurred_at)<=datetime(b.outcome_cutoff_at)
        AND datetime(c.recorded_at)<=datetime(b.snapshot_as_of)),0)
      * m.founder_internal_hourly_value_micros / 60
    AS fully_loaded_cost_micros,
  (SELECT count(DISTINCT d.account_id) FROM v_cutoff_latest_decision d
   WHERE d.batch_id=b.batch_id AND d.decision_code='QUALIFIED')
    AS qualified_accounts
FROM segment_contract b CROSS JOIN model_settings m;

-- A Qualified opportunity is not yet a customer. These are acquisition costs per
-- Qualified account, not CAC. Contract-effective, first-cash-received, and
-- paid-in-full CAC belong to Chapter 11's outcome and post-Qualified cost ledgers.
CREATE VIEW v_batch_cost_per_qualified AS
SELECT e.*,
  CASE WHEN e.qualified_accounts=0 THEN NULL
       ELSE e.cash_cost_micros/e.qualified_accounts END
    AS cash_cost_per_qualified_micros,
  CASE WHEN e.qualified_accounts=0 THEN NULL
       ELSE e.founder_time_cost_micros/e.qualified_accounts END
    AS founder_time_cost_per_qualified_micros,
  CASE WHEN e.qualified_accounts=0 THEN NULL
       ELSE e.fully_loaded_cost_micros/e.qualified_accounts END
    AS fully_loaded_cost_per_qualified_micros,
  CASE WHEN e.qualified_accounts=0 THEN 'NO_QUALIFIED'
       ELSE 'MATURE_AT_CUTOFF' END AS cost_per_qualified_state
FROM v_batch_acquisition_economics e;

-- -----------------------------------------------------------------------------
-- Fully synthetic fixture
-- -----------------------------------------------------------------------------

INSERT INTO schema_registry VALUES (
  1,'founder_led_acquisition','1.0.0','SQLite 3.40+',
  '2026-08-02T00:00:00Z','SYNTHETIC_ONLY_NO_PII_OPAQUE_REFERENCES',
  'FOUNDER_SET_LIMITS_NOT_MARKET_BENCHMARKS'
);

INSERT INTO model_settings VALUES (
  1,'2026-08-02T12:00:00Z','2026-08-02T12:00:00Z','JPY',6000000000
);

INSERT INTO gate_state_dim VALUES
  ('STOP',1),('PAUSE',2),('UNKNOWN',3),('READY_TO_SEND',4);

INSERT INTO segment_contract(
  batch_id,segment_id,segment_version,cohort_start_at,selection_close_at,
  outcome_cutoff_at,snapshot_as_of,maturity_review_at,
  max_selected_accounts,max_contacts_per_account,max_attempts_per_contact,
  min_attempt_spacing_minutes,max_founder_minutes,max_cash_micros,
  primary_outcome_code,attribution_rule_version,contract_ref_hash,
  approved_by_role,approved_at,recorded_at
) VALUES (
  'SYN_BATCH_01','SYN_SEGMENT_CLOSE',1,
  '2026-07-01T00:00:00Z','2026-07-15T23:59:59Z',
  '2026-07-31T23:59:59Z','2026-07-31T23:59:59Z',
  '2026-08-02T12:00:00Z',13,1,2,1440,900,20000000000,
  'QUALIFIED_ACCOUNT','FIRST_TOUCH_PLUS_UNWEIGHTED_ASSISTS_V1',
  printf('%064x',1001),'SYN_FOUNDER_REVIEWER',
  '2026-06-30T09:00:00Z','2026-06-30T09:05:00Z'
);

INSERT INTO acquisition_account(
  account_id,batch_id,eligibility_status,selected_for_batch,selected_at,
  account_source_ref_hash,domain_ref_hash,occurred_at,recorded_at
)
WITH fixture(account_id,n,selected) AS (VALUES
  ('SYN_ACC_QUAL',1101,1),
  ('SYN_ACC_LATE',1102,1),
  ('SYN_ACC_SUPPRESS',1103,1),
  ('SYN_ACC_REVIEW_UNKNOWN',1104,1),
  ('SYN_ACC_BOUNCE',1105,1),
  ('SYN_ACC_LEGAL',1106,1),
  ('SYN_ACC_RETIRED',1107,1),
  ('SYN_ACC_CLAIM_UNKNOWN',1108,1),
  ('SYN_ACC_CLAIM_STOP',1109,1),
  ('SYN_ACC_STALE_TRIGGER',1110,1),
  ('SYN_ACC_NO_CONTACT',1111,1),
  ('SYN_ACC_INVESTIGATION',1112,1),
  ('SYN_ACC_FORM',1113,1),
  ('SYN_ACC_NOT_SELECTED',1114,0)
)
SELECT account_id,'SYN_BATCH_01','ELIGIBLE',selected,
       CASE selected WHEN 1 THEN '2026-07-03T09:00:00Z' END,
       printf('%064x',n),printf('%064x',n+100),
       '2026-07-02T09:00:00Z','2026-07-03T09:05:00Z'
FROM fixture;

INSERT INTO trigger_evidence(
  trigger_evidence_id,batch_id,account_id,trigger_type_code,evidence_status,
  observed_fact_ref_hash,source_snapshot_ref_hash,hypothesis_ref_hash,
  occurred_at,recorded_at,valid_until,reviewed_by_role
)
WITH fixture(trigger_id,account_id,n,valid_until) AS (VALUES
  ('SYN_TRIG_QUAL','SYN_ACC_QUAL',1201,'2026-08-15T00:00:00Z'),
  ('SYN_TRIG_LATE','SYN_ACC_LATE',1202,'2026-08-15T00:00:00Z'),
  ('SYN_TRIG_SUPPRESS','SYN_ACC_SUPPRESS',1203,'2026-08-15T00:00:00Z'),
  ('SYN_TRIG_REVIEW_UNKNOWN','SYN_ACC_REVIEW_UNKNOWN',1204,'2026-08-15T00:00:00Z'),
  ('SYN_TRIG_BOUNCE','SYN_ACC_BOUNCE',1205,'2026-08-15T00:00:00Z'),
  ('SYN_TRIG_LEGAL','SYN_ACC_LEGAL',1206,'2026-08-15T00:00:00Z'),
  ('SYN_TRIG_RETIRED','SYN_ACC_RETIRED',1207,'2026-08-15T00:00:00Z'),
  ('SYN_TRIG_CLAIM_UNKNOWN','SYN_ACC_CLAIM_UNKNOWN',1208,'2026-08-15T00:00:00Z'),
  ('SYN_TRIG_CLAIM_STOP','SYN_ACC_CLAIM_STOP',1209,'2026-08-15T00:00:00Z'),
  ('SYN_TRIG_STALE','SYN_ACC_STALE_TRIGGER',1210,'2026-07-20T00:00:00Z'),
  ('SYN_TRIG_INVESTIGATION','SYN_ACC_INVESTIGATION',1212,'2026-08-15T00:00:00Z'),
  ('SYN_TRIG_FORM','SYN_ACC_FORM',1213,'2026-08-15T00:00:00Z')
)
SELECT trigger_id,'SYN_BATCH_01',account_id,'PUBLIC_WORKFLOW_SIGNAL','CONFIRMED',
       printf('%064x',n),printf('%064x',n+100),printf('%064x',n+200),
       '2026-07-03T10:00:00Z','2026-07-03T10:05:00Z',valid_until,
       'SYN_RESEARCH_REVIEWER'
FROM fixture;

INSERT INTO contact(
  contact_id,batch_id,account_id,role_code,channel_code,address_owner_code,
  source_type_code,provenance_status,address_ref_hash,source_snapshot_ref_hash,
  domain_ref_hash,occurred_at,recorded_at
)
WITH fixture(contact_id,account_id,n,channel_code) AS (VALUES
  ('SYN_CONTACT_QUAL','SYN_ACC_QUAL',1301,'EMAIL'),
  ('SYN_CONTACT_LATE','SYN_ACC_LATE',1302,'EMAIL'),
  ('SYN_CONTACT_SUPPRESS','SYN_ACC_SUPPRESS',1303,'EMAIL'),
  ('SYN_CONTACT_REVIEW_UNKNOWN','SYN_ACC_REVIEW_UNKNOWN',1304,'EMAIL'),
  ('SYN_CONTACT_BOUNCE','SYN_ACC_BOUNCE',1305,'EMAIL'),
  ('SYN_CONTACT_LEGAL','SYN_ACC_LEGAL',1306,'EMAIL'),
  ('SYN_CONTACT_RETIRED','SYN_ACC_RETIRED',1307,'EMAIL'),
  ('SYN_CONTACT_CLAIM_UNKNOWN','SYN_ACC_CLAIM_UNKNOWN',1308,'EMAIL'),
  ('SYN_CONTACT_CLAIM_STOP','SYN_ACC_CLAIM_STOP',1309,'EMAIL'),
  ('SYN_CONTACT_STALE','SYN_ACC_STALE_TRIGGER',1310,'EMAIL'),
  ('SYN_CONTACT_INVESTIGATION','SYN_ACC_INVESTIGATION',1312,'EMAIL'),
  ('SYN_CONTACT_FORM','SYN_ACC_FORM',1313,'FORM')
)
SELECT contact_id,'SYN_BATCH_01',account_id,'PROCESS_OWNER',channel_code,
       'ROLE','PUBLIC_BUSINESS_ADDRESS','CONFIRMED',printf('%064x',n),
       printf('%064x',n+100),printf('%064x',n-100),
       '2026-07-04T09:00:00Z','2026-07-04T09:05:00Z'
FROM fixture;

INSERT INTO contactability_review(
  contactability_review_id,review_stream_id,review_sequence,supersedes_review_id,
  batch_id,account_id,contact_id,channel_code,exact_purpose_code,exact_sender_id,
  candidate_basis_code,channel_check_matrix_version,review_result,
  specific_email_law_result,specified_commercial_transactions_result,
  privacy_purpose_result,platform_terms_result,
  consent_or_exception_record_result,no_solicitation_public_source_result,
  sender_identity_result,sender_address_display_result,
  complaint_inquiry_contact_display_result,opt_out_result,authentication_result,
  not_applicable_basis_ref_hash,evidence_package_ref_hash,occurred_at,
  recorded_at,valid_until,reviewed_by_role
)
WITH fixture(review_id,contact_id,account_id,n,channel_code,result) AS (VALUES
  ('SYN_REVIEW_QUAL','SYN_CONTACT_QUAL','SYN_ACC_QUAL',1401,'EMAIL','PASS'),
  ('SYN_REVIEW_LATE','SYN_CONTACT_LATE','SYN_ACC_LATE',1402,'EMAIL','PASS'),
  ('SYN_REVIEW_SUPPRESS','SYN_CONTACT_SUPPRESS','SYN_ACC_SUPPRESS',1403,'EMAIL','STOP'),
  ('SYN_REVIEW_UNKNOWN','SYN_CONTACT_REVIEW_UNKNOWN','SYN_ACC_REVIEW_UNKNOWN',1404,'EMAIL','UNKNOWN'),
  ('SYN_REVIEW_BOUNCE','SYN_CONTACT_BOUNCE','SYN_ACC_BOUNCE',1405,'EMAIL','PASS'),
  ('SYN_REVIEW_LEGAL','SYN_CONTACT_LEGAL','SYN_ACC_LEGAL',1406,'EMAIL','PASS'),
  ('SYN_REVIEW_RETIRED','SYN_CONTACT_RETIRED','SYN_ACC_RETIRED',1407,'EMAIL','PASS'),
  ('SYN_REVIEW_CLAIM_UNKNOWN','SYN_CONTACT_CLAIM_UNKNOWN','SYN_ACC_CLAIM_UNKNOWN',1408,'EMAIL','PASS'),
  ('SYN_REVIEW_CLAIM_STOP','SYN_CONTACT_CLAIM_STOP','SYN_ACC_CLAIM_STOP',1409,'EMAIL','PASS'),
  ('SYN_REVIEW_STALE','SYN_CONTACT_STALE','SYN_ACC_STALE_TRIGGER',1410,'EMAIL','PASS'),
  ('SYN_REVIEW_INVESTIGATION','SYN_CONTACT_INVESTIGATION','SYN_ACC_INVESTIGATION',1412,'EMAIL','PASS'),
  ('SYN_REVIEW_FORM','SYN_CONTACT_FORM','SYN_ACC_FORM',1413,'FORM','PASS')
)
SELECT review_id,'SYN_STREAM_'||review_id,1,NULL,'SYN_BATCH_01',account_id,
       contact_id,channel_code,'FOUNDER_RESEARCH','SYN_SENDER_01',
       CASE channel_code WHEN 'EMAIL' THEN 'PUBLIC_BUSINESS_ADDRESS'
                         ELSE 'PERMISSIONED_PLATFORM' END,
       CASE channel_code WHEN 'EMAIL' THEN 'SYN_EMAIL_JP_MATRIX_V1'
                         ELSE 'SYN_NON_EMAIL_MATRIX_V1' END,
       result,
       CASE channel_code WHEN 'FORM' THEN 'NOT_APPLICABLE'
            ELSE CASE result WHEN 'STOP' THEN 'STOP'
                             WHEN 'UNKNOWN' THEN 'UNKNOWN' ELSE 'PASS' END END,
       CASE channel_code WHEN 'FORM' THEN 'NOT_APPLICABLE' ELSE 'PASS' END,
       CASE result WHEN 'UNKNOWN' THEN 'UNKNOWN' ELSE 'PASS' END,
       CASE channel_code WHEN 'FORM' THEN 'PASS' ELSE 'NOT_APPLICABLE' END,
       'PASS',
       CASE channel_code WHEN 'FORM' THEN 'NOT_APPLICABLE'
            ELSE CASE result WHEN 'STOP' THEN 'STOP'
                             WHEN 'UNKNOWN' THEN 'UNKNOWN' ELSE 'PASS' END END,
       'PASS','PASS','PASS','PASS','PASS',printf('%064x',n+500),
       printf('%064x',n),
       '2026-07-05T09:00:00Z','2026-07-05T09:05:00Z',
       '2026-08-15T00:00:00Z','SYN_CONTACTABILITY_REVIEWER'
FROM fixture;

INSERT INTO suppression_event(
  suppression_event_id,suppression_stream_id,event_sequence,
  supersedes_suppression_event_id,scope_code,scope_ref,channel_scope,
  purpose_scope,product_scope,sender_scope,effective_from,effective_until,
  action_code,reason_code,source_evidence_ref_hash,authority_role,
  occurred_at,recorded_at
) VALUES
  ('SYN_SUPPRESS_EXPLICIT','SYN_SUPPRESS_STREAM_EXPLICIT',1,NULL,
   'CONTACT','SYN_CONTACT_SUPPRESS','EMAIL','FOUNDER_RESEARCH','SYN_PRODUCT_01',
   'SYN_SENDER_01','2026-07-06T08:00:00Z',NULL,'SUPPRESS','EXPLICIT_OPT_OUT',
   printf('%064x',1501),'SYN_COMPLIANCE_REVIEWER',
   '2026-07-06T08:00:00Z','2026-07-06T08:05:00Z'),
  ('SYN_SUPPRESS_INVESTIGATION','SYN_SUPPRESS_STREAM_INVESTIGATION',1,NULL,
   'ACCOUNT','SYN_ACC_INVESTIGATION','EMAIL','FOUNDER_RESEARCH','SYN_PRODUCT_01',
   'ALL','2026-07-06T08:00:00Z',NULL,'SUPPRESS','INVESTIGATION',
   printf('%064x',1502),'SYN_COMPLIANCE_REVIEWER',
   '2026-07-06T08:00:00Z','2026-07-06T08:05:00Z'),
  ('SYN_SUPPRESS_HARD_BOUNCE','SYN_SUPPRESS_STREAM_HARD_BOUNCE',1,NULL,
   'CONTACT','SYN_CONTACT_BOUNCE','EMAIL','FOUNDER_RESEARCH','SYN_PRODUCT_01',
   'SYN_SENDER_01','2026-07-18T09:10:00Z',NULL,'SUPPRESS','HARD_BOUNCE',
   printf('%064x',1503),'SYN_DELIVERY_REVIEWER',
   '2026-07-18T09:10:00Z','2026-07-18T09:11:00Z'),
  ('SYN_SUPPRESS_COMPLAINT_RESTATE','SYN_SUPPRESS_STREAM_COMPLAINT',1,NULL,
   'ACCOUNT','SYN_ACC_LEGAL','EMAIL','FOUNDER_RESEARCH','SYN_PRODUCT_01','ALL',
   '2026-07-22T08:00:00Z',NULL,'SUPPRESS','COMPLAINT',printf('%064x',1504),
   'SYN_FEEDBACK_REVIEWER','2026-07-22T08:00:00Z','2026-08-01T08:00:00Z');

INSERT INTO claim_evidence(
  claim_evidence_id,claim_class_code,fact_or_hypothesis_code,review_result,
  exact_claim_ref_hash,source_evidence_ref_hash,occurred_at,recorded_at,
  valid_until,reviewed_by_role
) VALUES
  ('SYN_CLAIM_FACT','OBSERVED_FACT','FACT','PASS',printf('%064x',1601),
   printf('%064x',1602),'2026-06-30T08:00:00Z','2026-06-30T08:05:00Z',
   '2026-08-15T00:00:00Z','SYN_CLAIM_REVIEWER'),
  ('SYN_CLAIM_HYPOTHESIS','PROBLEM_HYPOTHESIS','HYPOTHESIS','PASS',
   printf('%064x',1603),printf('%064x',1604),'2026-06-30T08:00:00Z',
   '2026-06-30T08:05:00Z','2026-08-15T00:00:00Z','SYN_CLAIM_REVIEWER'),
  ('SYN_CLAIM_UNKNOWN','OWN_CAPABILITY','FACT','UNKNOWN',printf('%064x',1605),
   printf('%064x',1606),'2026-06-30T08:00:00Z','2026-06-30T08:05:00Z',
   '2026-08-15T00:00:00Z','SYN_CLAIM_REVIEWER'),
  ('SYN_CLAIM_STOP','MEASURED_OUTCOME','FACT','STOP',printf('%064x',1607),
   printf('%064x',1608),'2026-06-30T08:00:00Z','2026-06-30T08:05:00Z',
   '2026-08-15T00:00:00Z','SYN_CLAIM_REVIEWER');

INSERT INTO message_version(
  message_version_id,batch_id,channel_code,exact_purpose_code,
  subject_ref_hash,body_ref_hash,cta_ref_hash,sender_identity_version,
  approved_at,approval_recorded_at,effective_from,retired_at,
  retirement_recorded_at
) VALUES
  ('SYN_MSG_GOOD','SYN_BATCH_01','EMAIL','FOUNDER_RESEARCH',
   printf('%064x',1701),printf('%064x',1702),printf('%064x',1703),'SYN_SENDER_V1',
   '2026-07-01T08:00:00Z','2026-07-01T08:05:00Z','2026-07-02T00:00:00Z',NULL,NULL),
  ('SYN_MSG_RETIRED','SYN_BATCH_01','EMAIL','FOUNDER_RESEARCH',
   printf('%064x',1711),printf('%064x',1712),printf('%064x',1713),'SYN_SENDER_V1',
   '2026-07-01T08:00:00Z','2026-07-01T08:05:00Z','2026-07-02T00:00:00Z',
   '2026-07-20T00:00:00Z','2026-07-20T00:05:00Z'),
  ('SYN_MSG_CLAIM_UNKNOWN','SYN_BATCH_01','EMAIL','FOUNDER_RESEARCH',
   printf('%064x',1721),printf('%064x',1722),printf('%064x',1723),'SYN_SENDER_V1',
   '2026-07-01T08:00:00Z','2026-07-01T08:05:00Z','2026-07-02T00:00:00Z',NULL,NULL),
  ('SYN_MSG_CLAIM_STOP','SYN_BATCH_01','EMAIL','FOUNDER_RESEARCH',
   printf('%064x',1731),printf('%064x',1732),printf('%064x',1733),'SYN_SENDER_V1',
   '2026-07-01T08:00:00Z','2026-07-01T08:05:00Z','2026-07-02T00:00:00Z',NULL,NULL),
  ('SYN_MSG_FORM','SYN_BATCH_01','FORM','FOUNDER_RESEARCH',
   printf('%064x',1741),printf('%064x',1742),printf('%064x',1743),'SYN_SENDER_V1',
   '2026-07-01T08:00:00Z','2026-07-01T08:05:00Z','2026-07-02T00:00:00Z',NULL,NULL);

INSERT INTO message_claim VALUES
  ('SYN_MSG_GOOD','SYN_CLAIM_FACT',1),
  ('SYN_MSG_GOOD','SYN_CLAIM_HYPOTHESIS',2),
  ('SYN_MSG_RETIRED','SYN_CLAIM_FACT',1),
  ('SYN_MSG_RETIRED','SYN_CLAIM_HYPOTHESIS',2),
  ('SYN_MSG_CLAIM_UNKNOWN','SYN_CLAIM_UNKNOWN',1),
  ('SYN_MSG_CLAIM_STOP','SYN_CLAIM_STOP',1),
  ('SYN_MSG_FORM','SYN_CLAIM_FACT',1);

-- Requests that become attempts are APPROVED. Negative fixtures stay PENDING;
-- STOP/PAUSE/UNKNOWN precedence determines why execution remains impossible.
INSERT INTO exact_send_request(
  send_request_id,batch_id,account_id,contact_id,channel_code,
  exact_purpose_code,product_code,exact_sender_id,segment_contract_ref_hash,
  trigger_evidence_id,contactability_review_id,message_version_id,
  final_body_ref_hash,claim_set_ref_hash,scheduled_send_at,request_recorded_at,
  prior_unknown_receipt_review_ref_hash,approval_status,human_approved_by_role,
  approved_at,approval_recorded_at,approval_expires_at
) VALUES
  ('SYN_SEND_QUAL_1','SYN_BATCH_01','SYN_ACC_QUAL','SYN_CONTACT_QUAL','EMAIL',
   'FOUNDER_RESEARCH','SYN_PRODUCT_01','SYN_SENDER_01',printf('%064x',1001),
   'SYN_TRIG_QUAL','SYN_REVIEW_QUAL','SYN_MSG_GOOD',printf('%064x',1801),
   printf('%064x',1802),'2026-07-10T09:00:00Z','2026-07-09T09:00:00Z',NULL,
   'APPROVED','SYN_SEND_REVIEWER','2026-07-09T10:00:00Z',
   '2026-07-09T10:05:00Z','2026-07-10T10:00:00Z'),
  ('SYN_SEND_QUAL_SPACING','SYN_BATCH_01','SYN_ACC_QUAL','SYN_CONTACT_QUAL','EMAIL',
   'FOUNDER_RESEARCH','SYN_PRODUCT_01','SYN_SENDER_01',printf('%064x',1001),
   'SYN_TRIG_QUAL','SYN_REVIEW_QUAL','SYN_MSG_GOOD',printf('%064x',1803),
   printf('%064x',1804),'2026-07-10T10:00:00Z','2026-07-10T09:30:00Z',NULL,
   'PENDING',NULL,NULL,NULL,NULL),
  ('SYN_SEND_LATE_1','SYN_BATCH_01','SYN_ACC_LATE','SYN_CONTACT_LATE','EMAIL',
   'FOUNDER_RESEARCH','SYN_PRODUCT_01','SYN_SENDER_01',printf('%064x',1001),
   'SYN_TRIG_LATE','SYN_REVIEW_LATE','SYN_MSG_GOOD',printf('%064x',1811),
   printf('%064x',1812),'2026-07-12T09:00:00Z','2026-07-11T09:00:00Z',NULL,
   'APPROVED','SYN_SEND_REVIEWER','2026-07-11T10:00:00Z',
   '2026-07-11T10:05:00Z','2026-07-12T10:00:00Z'),
  ('SYN_SEND_LATE_2','SYN_BATCH_01','SYN_ACC_LATE','SYN_CONTACT_LATE','EMAIL',
   'FOUNDER_RESEARCH','SYN_PRODUCT_01','SYN_SENDER_01',printf('%064x',1001),
   'SYN_TRIG_LATE','SYN_REVIEW_LATE','SYN_MSG_GOOD',printf('%064x',1813),
   printf('%064x',1814),'2026-07-14T09:00:00Z','2026-07-13T09:00:00Z',NULL,
   'APPROVED','SYN_SEND_REVIEWER','2026-07-13T10:00:00Z',
   '2026-07-13T10:05:00Z','2026-07-14T10:00:00Z'),
  ('SYN_SEND_LATE_CAP','SYN_BATCH_01','SYN_ACC_LATE','SYN_CONTACT_LATE','EMAIL',
   'FOUNDER_RESEARCH','SYN_PRODUCT_01','SYN_SENDER_01',printf('%064x',1001),
   'SYN_TRIG_LATE','SYN_REVIEW_LATE','SYN_MSG_GOOD',printf('%064x',1815),
   printf('%064x',1816),'2026-07-16T09:00:00Z','2026-07-15T09:00:00Z',NULL,
   'PENDING',NULL,NULL,NULL,NULL),
  ('SYN_SEND_SUPPRESS','SYN_BATCH_01','SYN_ACC_SUPPRESS','SYN_CONTACT_SUPPRESS','EMAIL',
   'FOUNDER_RESEARCH','SYN_PRODUCT_01','SYN_SENDER_01',printf('%064x',1001),
   'SYN_TRIG_SUPPRESS','SYN_REVIEW_SUPPRESS','SYN_MSG_GOOD',printf('%064x',1821),
   printf('%064x',1822),'2026-07-12T09:00:00Z','2026-07-11T09:00:00Z',NULL,
   'PENDING',NULL,NULL,NULL,NULL),
  ('SYN_SEND_REVIEW_UNKNOWN','SYN_BATCH_01','SYN_ACC_REVIEW_UNKNOWN',
   'SYN_CONTACT_REVIEW_UNKNOWN','EMAIL','FOUNDER_RESEARCH','SYN_PRODUCT_01',
   'SYN_SENDER_01',printf('%064x',1001),'SYN_TRIG_REVIEW_UNKNOWN',
   'SYN_REVIEW_UNKNOWN','SYN_MSG_GOOD',printf('%064x',1823),printf('%064x',1824),
   '2026-07-12T09:00:00Z','2026-07-11T09:00:00Z',NULL,
   'PENDING',NULL,NULL,NULL,NULL),
  ('SYN_SEND_BOUNCE_1','SYN_BATCH_01','SYN_ACC_BOUNCE','SYN_CONTACT_BOUNCE','EMAIL',
   'FOUNDER_RESEARCH','SYN_PRODUCT_01','SYN_SENDER_01',printf('%064x',1001),
   'SYN_TRIG_BOUNCE','SYN_REVIEW_BOUNCE','SYN_MSG_GOOD',printf('%064x',1831),
   printf('%064x',1832),'2026-07-18T09:00:00Z','2026-07-17T09:00:00Z',NULL,
   'APPROVED','SYN_SEND_REVIEWER','2026-07-17T10:00:00Z',
   '2026-07-17T10:05:00Z','2026-07-18T10:00:00Z'),
  ('SYN_SEND_BOUNCE_2','SYN_BATCH_01','SYN_ACC_BOUNCE','SYN_CONTACT_BOUNCE','EMAIL',
   'FOUNDER_RESEARCH','SYN_PRODUCT_01','SYN_SENDER_01',printf('%064x',1001),
   'SYN_TRIG_BOUNCE','SYN_REVIEW_BOUNCE','SYN_MSG_GOOD',printf('%064x',1833),
   printf('%064x',1834),'2026-07-20T09:00:00Z','2026-07-19T09:00:00Z',NULL,
   'PENDING',NULL,NULL,NULL,NULL),
  ('SYN_SEND_LEGAL_1','SYN_BATCH_01','SYN_ACC_LEGAL','SYN_CONTACT_LEGAL','EMAIL',
   'FOUNDER_RESEARCH','SYN_PRODUCT_01','SYN_SENDER_01',printf('%064x',1001),
   'SYN_TRIG_LEGAL','SYN_REVIEW_LEGAL','SYN_MSG_GOOD',printf('%064x',1841),
   printf('%064x',1842),'2026-07-21T09:00:00Z','2026-07-20T09:00:00Z',NULL,
   'APPROVED','SYN_SEND_REVIEWER','2026-07-20T10:00:00Z',
   '2026-07-20T10:05:00Z','2026-07-21T10:00:00Z'),
  ('SYN_SEND_LEGAL_2','SYN_BATCH_01','SYN_ACC_LEGAL','SYN_CONTACT_LEGAL','EMAIL',
   'FOUNDER_RESEARCH','SYN_PRODUCT_01','SYN_SENDER_01',printf('%064x',1001),
   'SYN_TRIG_LEGAL','SYN_REVIEW_LEGAL','SYN_MSG_GOOD',printf('%064x',1843),
   printf('%064x',1844),'2026-07-23T09:00:00Z','2026-07-22T09:00:00Z',NULL,
   'PENDING',NULL,NULL,NULL,NULL),
  ('SYN_SEND_RETIRED','SYN_BATCH_01','SYN_ACC_RETIRED','SYN_CONTACT_RETIRED','EMAIL',
   'FOUNDER_RESEARCH','SYN_PRODUCT_01','SYN_SENDER_01',printf('%064x',1001),
   'SYN_TRIG_RETIRED','SYN_REVIEW_RETIRED','SYN_MSG_RETIRED',printf('%064x',1851),
   printf('%064x',1852),'2026-07-25T09:00:00Z','2026-07-24T09:00:00Z',NULL,
   'PENDING',NULL,NULL,NULL,NULL),
  ('SYN_SEND_CLAIM_UNKNOWN','SYN_BATCH_01','SYN_ACC_CLAIM_UNKNOWN',
   'SYN_CONTACT_CLAIM_UNKNOWN','EMAIL','FOUNDER_RESEARCH','SYN_PRODUCT_01',
   'SYN_SENDER_01',printf('%064x',1001),'SYN_TRIG_CLAIM_UNKNOWN',
   'SYN_REVIEW_CLAIM_UNKNOWN','SYN_MSG_CLAIM_UNKNOWN',printf('%064x',1853),
   printf('%064x',1854),'2026-07-25T09:00:00Z','2026-07-24T09:00:00Z',NULL,
   'PENDING',NULL,NULL,NULL,NULL),
  ('SYN_SEND_CLAIM_STOP','SYN_BATCH_01','SYN_ACC_CLAIM_STOP',
   'SYN_CONTACT_CLAIM_STOP','EMAIL','FOUNDER_RESEARCH','SYN_PRODUCT_01',
   'SYN_SENDER_01',printf('%064x',1001),'SYN_TRIG_CLAIM_STOP',
   'SYN_REVIEW_CLAIM_STOP','SYN_MSG_CLAIM_STOP',printf('%064x',1855),
   printf('%064x',1856),'2026-07-25T09:00:00Z','2026-07-24T09:00:00Z',NULL,
   'PENDING',NULL,NULL,NULL,NULL),
  ('SYN_SEND_STALE','SYN_BATCH_01','SYN_ACC_STALE_TRIGGER','SYN_CONTACT_STALE','EMAIL',
   'FOUNDER_RESEARCH','SYN_PRODUCT_01','SYN_SENDER_01',printf('%064x',1001),
   'SYN_TRIG_STALE','SYN_REVIEW_STALE','SYN_MSG_GOOD',printf('%064x',1857),
   printf('%064x',1858),'2026-07-25T09:00:00Z','2026-07-24T09:00:00Z',NULL,
   'PENDING',NULL,NULL,NULL,NULL),
  ('SYN_SEND_INVESTIGATION','SYN_BATCH_01','SYN_ACC_INVESTIGATION',
   'SYN_CONTACT_INVESTIGATION','EMAIL','FOUNDER_RESEARCH','SYN_PRODUCT_01',
   'SYN_SENDER_01',printf('%064x',1001),'SYN_TRIG_INVESTIGATION',
   'SYN_REVIEW_INVESTIGATION','SYN_MSG_GOOD',printf('%064x',1859),
   printf('%064x',1860),'2026-07-25T09:00:00Z','2026-07-24T09:00:00Z',NULL,
   'PENDING',NULL,NULL,NULL,NULL),
  ('SYN_SEND_FORM','SYN_BATCH_01','SYN_ACC_FORM','SYN_CONTACT_FORM','FORM',
   'FOUNDER_RESEARCH','SYN_PRODUCT_01','SYN_SENDER_01',printf('%064x',1001),
   'SYN_TRIG_FORM','SYN_REVIEW_FORM','SYN_MSG_FORM',printf('%064x',1861),
   printf('%064x',1862),'2026-07-25T09:00:00Z','2026-07-24T09:00:00Z',NULL,
   'PENDING',NULL,NULL,NULL,NULL),
  ('SYN_SEND_AFTER_UNRESOLVED','SYN_BATCH_01','SYN_ACC_LATE','SYN_CONTACT_LATE',
   'EMAIL','FOUNDER_RESEARCH','SYN_PRODUCT_01','SYN_SENDER_01',
   printf('%064x',1001),'SYN_TRIG_LATE','SYN_REVIEW_LATE','SYN_MSG_GOOD',
   printf('%064x',1863),printf('%064x',1864),'2026-08-02T11:00:00Z',
   '2026-08-02T09:00:00Z',NULL,'PENDING',NULL,NULL,NULL,NULL);

INSERT INTO contact_attempt(
  attempt_id,send_request_id,batch_id,account_id,contact_id,attempt_sequence,
  execution_preflight_at,suppression_watermark_at,idempotency_key_ref_hash,
  execution_authority_code,auto_retry_authorized,retry_of_attempt_id,
  provider_message_ref_hash,send_receipt_status,occurred_at,recorded_at
) VALUES
  ('SYN_ATTEMPT_QUAL_1','SYN_SEND_QUAL_1','SYN_BATCH_01','SYN_ACC_QUAL',
   'SYN_CONTACT_QUAL',1,'2026-07-10T09:00:00Z','2026-07-10T09:00:00Z',
   printf('%064x',1901),'HUMAN_ONLY',0,NULL,printf('%064x',1902),'ACCEPTED',
   '2026-07-10T09:00:00Z','2026-07-10T09:01:00Z'),
  ('SYN_ATTEMPT_LATE_1','SYN_SEND_LATE_1','SYN_BATCH_01','SYN_ACC_LATE',
   'SYN_CONTACT_LATE',1,'2026-07-12T09:00:00Z','2026-07-12T09:00:00Z',
   printf('%064x',1911),'HUMAN_ONLY',0,NULL,printf('%064x',1912),'ACCEPTED',
   '2026-07-12T09:00:00Z','2026-07-12T09:01:00Z'),
  ('SYN_ATTEMPT_LATE_2','SYN_SEND_LATE_2','SYN_BATCH_01','SYN_ACC_LATE',
   'SYN_CONTACT_LATE',2,'2026-07-14T09:00:00Z','2026-07-14T09:00:00Z',
   printf('%064x',1913),'HUMAN_ONLY',0,NULL,printf('%064x',1914),'ACCEPTED',
   '2026-07-14T09:00:00Z','2026-07-14T09:01:00Z'),
  ('SYN_ATTEMPT_BOUNCE_1','SYN_SEND_BOUNCE_1','SYN_BATCH_01','SYN_ACC_BOUNCE',
   'SYN_CONTACT_BOUNCE',1,'2026-07-18T09:00:00Z','2026-07-18T09:00:00Z',
   printf('%064x',1921),'HUMAN_ONLY',0,NULL,printf('%064x',1922),'ACCEPTED',
   '2026-07-18T09:00:00Z','2026-07-18T09:01:00Z'),
  ('SYN_ATTEMPT_LEGAL_1','SYN_SEND_LEGAL_1','SYN_BATCH_01','SYN_ACC_LEGAL',
   'SYN_CONTACT_LEGAL',1,'2026-07-21T09:00:00Z','2026-07-21T09:00:00Z',
   printf('%064x',1931),'HUMAN_ONLY',0,NULL,printf('%064x',1932),'ACCEPTED',
   '2026-07-21T09:00:00Z','2026-07-21T09:01:00Z');

INSERT INTO delivery_event(
  delivery_event_id,attempt_id,event_sequence,supersedes_delivery_event_id,
  outcome_code,suppression_event_id,provider_receipt_ref_hash,occurred_at,recorded_at
) VALUES
  ('SYN_DELIVERY_QUAL_ACCEPT','SYN_ATTEMPT_QUAL_1',1,NULL,'ACCEPTED',
   NULL,printf('%064x',2001),'2026-07-10T09:01:00Z','2026-07-10T09:02:00Z'),
  ('SYN_DELIVERY_QUAL_SIGNAL','SYN_ATTEMPT_QUAL_1',2,'SYN_DELIVERY_QUAL_ACCEPT',
   'DELIVERED_SIGNAL',NULL,printf('%064x',2002),'2026-07-10T09:05:00Z',
   '2026-07-10T09:06:00Z'),
  ('SYN_DELIVERY_LATE_1','SYN_ATTEMPT_LATE_1',1,NULL,'ACCEPTED',
   NULL,printf('%064x',2011),'2026-07-12T09:01:00Z','2026-07-12T09:02:00Z'),
  ('SYN_DELIVERY_LATE_2_ACCEPT','SYN_ATTEMPT_LATE_2',1,NULL,'ACCEPTED',
   NULL,printf('%064x',2012),'2026-07-14T09:01:00Z','2026-07-14T09:02:00Z'),
  ('SYN_DELIVERY_LATE_2_SIGNAL','SYN_ATTEMPT_LATE_2',2,
   'SYN_DELIVERY_LATE_2_ACCEPT','DELIVERED_SIGNAL',NULL,printf('%064x',2013),
   '2026-08-01T08:00:00Z','2026-08-01T08:01:00Z'),
  ('SYN_DELIVERY_BOUNCE_ACCEPT','SYN_ATTEMPT_BOUNCE_1',1,NULL,'ACCEPTED',
   NULL,printf('%064x',2021),'2026-07-18T09:01:00Z','2026-07-18T09:02:00Z'),
  ('SYN_DELIVERY_BOUNCE_HARD','SYN_ATTEMPT_BOUNCE_1',2,
   'SYN_DELIVERY_BOUNCE_ACCEPT','HARD_BOUNCE','SYN_SUPPRESS_HARD_BOUNCE',
   printf('%064x',2022),
   '2026-07-18T09:10:00Z','2026-07-18T09:11:00Z'),
  ('SYN_DELIVERY_LEGAL_ACCEPT','SYN_ATTEMPT_LEGAL_1',1,NULL,'ACCEPTED',
   NULL,printf('%064x',2031),'2026-07-21T09:01:00Z','2026-07-21T09:02:00Z');

INSERT INTO recipient_feedback_event(
  feedback_event_id,feedback_stream_id,event_sequence,
  supersedes_feedback_event_id,batch_id,account_id,contact_id,attempt_id,
  feedback_code,verification_status,suppression_event_id,feedback_ref_hash,
  occurred_at,recorded_at
) VALUES (
  'SYN_FEEDBACK_LEGAL_COMPLAINT','SYN_FEEDBACK_STREAM_LEGAL',1,NULL,
  'SYN_BATCH_01','SYN_ACC_LEGAL','SYN_CONTACT_LEGAL','SYN_ATTEMPT_LEGAL_1',
  'COMPLAINT','VERIFIED','SYN_SUPPRESS_COMPLAINT_RESTATE',printf('%064x',2032),
  '2026-07-22T08:00:00Z',
  '2026-08-01T08:00:00Z'),
  ('SYN_FEEDBACK_LATE_UNRESOLVED','SYN_FEEDBACK_STREAM_LATE',1,NULL,
   'SYN_BATCH_01','SYN_ACC_LATE','SYN_CONTACT_LATE','SYN_ATTEMPT_LATE_2',
   'UNKNOWN','UNRESOLVED',NULL,printf('%064x',2033),
   '2026-08-01T10:00:00Z','2026-08-01T10:05:00Z'
);

INSERT INTO reply_event(
  reply_event_id,thread_id,reply_sequence,supersedes_reply_event_id,batch_id,
  account_id,contact_id,causally_related_attempt_id,reply_actor_code,
  outcome_code,raw_reply_ref_hash,classification_ref_hash,
  classified_by_role,occurred_at,recorded_at
) VALUES
  ('SYN_REPLY_QUAL','SYN_THREAD_QUAL',1,NULL,'SYN_BATCH_01','SYN_ACC_QUAL',
   'SYN_CONTACT_QUAL','SYN_ATTEMPT_QUAL_1','HUMAN','POSITIVE',
   printf('%064x',2101),printf('%064x',2102),'SYN_REPLY_REVIEWER',
   '2026-07-11T08:00:00Z','2026-07-11T08:05:00Z'),
  ('SYN_REPLY_LATE','SYN_THREAD_LATE',1,NULL,'SYN_BATCH_01','SYN_ACC_LATE',
   'SYN_CONTACT_LATE','SYN_ATTEMPT_LATE_2','HUMAN','QUESTION',
   printf('%064x',2111),printf('%064x',2112),'SYN_REPLY_REVIEWER',
   '2026-08-01T09:00:00Z','2026-08-01T09:05:00Z'),
  ('SYN_REPLY_LEGAL','SYN_THREAD_LEGAL',1,NULL,'SYN_BATCH_01','SYN_ACC_LEGAL',
   'SYN_CONTACT_LEGAL','SYN_ATTEMPT_LEGAL_1','HUMAN','LEGAL_STOP',
   printf('%064x',2121),printf('%064x',2122),'SYN_REPLY_REVIEWER',
   '2026-07-22T08:00:00Z','2026-07-22T08:05:00Z');

INSERT INTO suppression_event(
  suppression_event_id,suppression_stream_id,event_sequence,
  supersedes_suppression_event_id,scope_code,scope_ref,channel_scope,
  purpose_scope,product_scope,sender_scope,effective_from,effective_until,
  action_code,reason_code,source_evidence_ref_hash,authority_role,
  occurred_at,recorded_at
) VALUES (
  'SYN_SUPPRESS_LEGAL_RESTATE','SYN_SUPPRESS_STREAM_LEGAL',1,NULL,
  'ACCOUNT','SYN_ACC_LEGAL','EMAIL','FOUNDER_RESEARCH','SYN_PRODUCT_01','ALL',
  '2026-07-22T08:05:00Z',NULL,'SUPPRESS','LEGAL_STOP',printf('%064x',2131),
  'SYN_COMPLIANCE_REVIEWER','2026-07-22T08:05:00Z','2026-08-01T08:10:00Z'
);

INSERT INTO meeting_event(
  meeting_event_id,meeting_id,event_sequence,supersedes_meeting_event_id,
  batch_id,account_id,source_reply_event_id,status_code,occurred_at,recorded_at
) VALUES
  ('SYN_MEETING_QUAL_SCHEDULED','SYN_MEETING_QUAL',1,NULL,'SYN_BATCH_01',
   'SYN_ACC_QUAL','SYN_REPLY_QUAL','SCHEDULED',
   '2026-07-12T08:00:00Z','2026-07-12T08:05:00Z'),
  ('SYN_MEETING_QUAL_HELD','SYN_MEETING_QUAL',2,'SYN_MEETING_QUAL_SCHEDULED',
   'SYN_BATCH_01','SYN_ACC_QUAL','SYN_REPLY_QUAL','HELD',
   '2026-07-15T08:00:00Z','2026-07-15T09:00:00Z'),
  ('SYN_MEETING_QUAL_RESTATE','SYN_MEETING_QUAL',3,'SYN_MEETING_QUAL_HELD',
   'SYN_BATCH_01','SYN_ACC_QUAL','SYN_REPLY_QUAL','HELD',
   '2026-07-15T08:01:00Z','2026-08-01T08:20:00Z');

INSERT INTO discovery_note(
  discovery_note_id,batch_id,account_id,meeting_id,note_version,
  source_review_status,note_ref_hash,disconfirming_evidence_ref_hash,
  outcome_cutoff_at,snapshot_as_of,occurred_at,recorded_at,reviewed_by_role
) VALUES (
  'SYN_DISCOVERY_QUAL','SYN_BATCH_01','SYN_ACC_QUAL','SYN_MEETING_QUAL',1,
  'CONFIRMED',printf('%064x',2201),printf('%064x',2202),
  '2026-07-16T08:00:00Z','2026-07-16T09:05:00Z',
  '2026-07-16T09:00:00Z','2026-07-16T09:05:00Z',
  'SYN_DISCOVERY_REVIEWER'
);

INSERT INTO source_touch(
  source_touch_id,batch_id,account_id,touch_role_code,source_code,
  source_evidence_ref_hash,occurred_at,recorded_at
) VALUES
  ('SYN_TOUCH_QUAL_FIRST','SYN_BATCH_01','SYN_ACC_QUAL','FIRST_TOUCH',
   'PUBLIC_DIRECTORY',printf('%064x',2301),'2026-07-02T08:00:00Z','2026-07-02T08:05:00Z'),
  ('SYN_TOUCH_QUAL_ASSIST','SYN_BATCH_01','SYN_ACC_QUAL','ASSIST',
   'PERMITTED_PEER_INTRO',printf('%064x',2302),'2026-07-08T08:00:00Z','2026-07-08T08:05:00Z'),
  ('SYN_TOUCH_LATE_FIRST','SYN_BATCH_01','SYN_ACC_LATE','FIRST_TOUCH',
   'PUBLIC_DIRECTORY',printf('%064x',2303),'2026-07-02T08:00:00Z','2026-07-02T08:05:00Z'),
  ('SYN_TOUCH_BOUNCE_FIRST','SYN_BATCH_01','SYN_ACC_BOUNCE','FIRST_TOUCH',
   'PERMISSIONED_EVENT',printf('%064x',2304),'2026-07-02T08:00:00Z','2026-07-02T08:05:00Z'),
  ('SYN_TOUCH_LEGAL_FIRST','SYN_BATCH_01','SYN_ACC_LEGAL','FIRST_TOUCH',
   'PUBLIC_DIRECTORY',printf('%064x',2305),'2026-07-02T08:00:00Z','2026-07-02T08:05:00Z');

INSERT INTO qualification_handoff(
  qualification_package_id,package_version,batch_id,account_id,
  discovery_note_id,first_touch_source_id,opportunity_id,proposed_motion_code,
  package_ref_hash,outcome_cutoff_at,snapshot_as_of,occurred_at,recorded_at,
  reviewed_by_role
) VALUES (
  'SYN_HANDOFF_QUAL',1,'SYN_BATCH_01','SYN_ACC_QUAL','SYN_DISCOVERY_QUAL',
  'SYN_TOUCH_QUAL_FIRST','SYN_OPPORTUNITY_QUAL','PILOT',printf('%064x',2401),
  '2026-07-16T09:30:00Z','2026-07-16T10:05:00Z',
  '2026-07-16T10:00:00Z','2026-07-16T10:05:00Z',
  'SYN_HANDOFF_REVIEWER'
);

INSERT INTO handoff_requirement_evidence(
  qualification_package_id,requirement_code,result_code,evidence_ref_hash,
  occurred_at,recorded_at
)
WITH requirements(requirement_code,n,result_code) AS (VALUES
  ('RECENT_WORKFLOW',2411,'CONFIRMED'),
  ('IMPACT_BASELINE',2412,'CONFIRMED'),
  ('NEXT_OCCURRENCE',2413,'CONFIRMED'),
  ('STANDARD_SCOPE',2414,'CONFIRMED'),
  ('DATA_PERMISSION_SAFETY',2415,'PASS'),
  ('STAKEHOLDER_PATH',2416,'CONFIRMED'),
  ('BUDGET_PURCHASE_PATH',2417,'CONFIRMED'),
  ('MUTUAL_NEXT_ACTIONS',2418,'CONFIRMED'),
  ('FOUNDER_DELIVERY_FEASIBILITY',2419,'PASS')
)
SELECT 'SYN_HANDOFF_QUAL',requirement_code,result_code,printf('%064x',n),
       '2026-07-16T09:30:00Z','2026-07-16T09:35:00Z'
FROM requirements;

INSERT INTO acquisition_decision(
  decision_event_id,decision_stream_id,decision_sequence,
  supersedes_decision_event_id,batch_id,account_id,decision_code,
  primary_reason_code,change_domain_code,qualification_package_id,
  stop_evidence_ref_hash,restart_trigger_ref_hash,earliest_restart_at,
  decision_evidence_ref_hash,outcome_cutoff_at,snapshot_as_of,occurred_at,
  recorded_at,decision_owner_role
) VALUES (
  'SYN_DECISION_QUAL','SYN_DECISION_STREAM_QUAL',1,NULL,'SYN_BATCH_01',
  'SYN_ACC_QUAL','QUALIFIED','QUALIFIED','NONE','SYN_HANDOFF_QUAL',NULL,NULL,
  NULL,printf('%064x',2501),'2026-07-16T09:30:00Z',
  '2026-07-16T10:05:00Z','2026-07-17T09:00:00Z',
  '2026-07-17T09:05:00Z','SYN_FOUNDER_REVIEWER'
);

INSERT INTO acquisition_cost_event(
  cost_event_id,batch_id,account_id,cost_kind_code,component_code,cash_micros,
  founder_minutes,currency,source_event_ref_hash,occurred_at,recorded_at
) VALUES
  ('SYN_COST_CASH_DATA','SYN_BATCH_01',NULL,'CASH','DATA_TOOL',5000000000,NULL,
   'JPY',printf('%064x',2601),'2026-07-05T08:00:00Z','2026-07-05T08:05:00Z'),
  ('SYN_COST_CASH_SEND','SYN_BATCH_01',NULL,'CASH','SEND_PROVIDER',1000000000,NULL,
   'JPY',printf('%064x',2602),'2026-07-15T08:00:00Z','2026-07-15T08:05:00Z'),
  ('SYN_COST_CASH_OTHER','SYN_BATCH_01',NULL,'CASH','TRAVEL_OTHER',6000000000,NULL,
   'JPY',printf('%064x',2603),'2026-07-16T08:00:00Z','2026-07-16T08:05:00Z'),
  ('SYN_COST_TIME_RESEARCH','SYN_BATCH_01',NULL,'FOUNDER_TIME','ACCOUNT_RESEARCH',
   NULL,180,'JPY',printf('%064x',2611),'2026-07-05T08:00:00Z','2026-07-05T08:05:00Z'),
  ('SYN_COST_TIME_REVIEW','SYN_BATCH_01',NULL,'FOUNDER_TIME',
   'LEGAL_PRIVACY_TERMS_REVIEW',NULL,90,'JPY',printf('%064x',2612),
   '2026-07-06T08:00:00Z','2026-07-06T08:05:00Z'),
  ('SYN_COST_TIME_MESSAGE','SYN_BATCH_01',NULL,'FOUNDER_TIME','MESSAGE_CLAIM_REVIEW',
   NULL,60,'JPY',printf('%064x',2613),'2026-07-07T08:00:00Z','2026-07-07T08:05:00Z'),
  ('SYN_COST_TIME_SEND','SYN_BATCH_01',NULL,'FOUNDER_TIME','SEND_CRM',NULL,60,
   'JPY',printf('%064x',2614),'2026-07-21T08:00:00Z','2026-07-21T08:05:00Z'),
  ('SYN_COST_TIME_REPLY','SYN_BATCH_01',NULL,'FOUNDER_TIME','REPLY_HANDLING',NULL,60,
   'JPY',printf('%064x',2615),'2026-07-22T08:00:00Z','2026-07-22T08:05:00Z'),
  ('SYN_COST_TIME_MEETING','SYN_BATCH_01','SYN_ACC_QUAL','FOUNDER_TIME',
   'MEETING_DISCOVERY',NULL,120,'JPY',printf('%064x',2616),
   '2026-07-15T08:00:00Z','2026-07-15T09:05:00Z'),
  ('SYN_COST_TIME_ANALYSIS','SYN_BATCH_01',NULL,'FOUNDER_TIME','ANALYSIS',NULL,30,
   'JPY',printf('%064x',2617),'2026-07-31T08:00:00Z','2026-07-31T08:05:00Z');

-- -----------------------------------------------------------------------------
-- Data-quality and invariant assertions
-- -----------------------------------------------------------------------------

CREATE TEMP VIEW v_assertion_results AS
SELECT 'DQ01' AS check_id,'error' AS severity,'foreign_keys_resolve' AS check_name,
       (SELECT count(*) FROM pragma_foreign_key_check) AS failure_count,
       'Every reference resolves inside the disposable fixture' AS expectation
UNION ALL SELECT 'DQ02','error','selected_account_cap',
       (SELECT count(*) FROM segment_contract b WHERE (
          SELECT count(*) FROM acquisition_account a
          WHERE a.batch_id=b.batch_id AND a.selected_for_batch=1
        )>b.max_selected_accounts),
       'Selected account count never exceeds the founder-set batch cap'
UNION ALL SELECT 'DQ03','error','contact_per_account_cap',
       (SELECT count(*) FROM (
          SELECT c.batch_id,c.account_id,count(*) AS n,b.max_contacts_per_account
          FROM contact c JOIN segment_contract b USING (batch_id)
          GROUP BY c.batch_id,c.account_id
          HAVING n>b.max_contacts_per_account
        )),
       'Contact count never exceeds the founder-set per-account cap'
UNION ALL SELECT 'DQ04','error','attempt_per_contact_cap',
       (SELECT count(*) FROM (
          SELECT a.batch_id,a.contact_id,count(*) AS n,b.max_attempts_per_contact
          FROM contact_attempt a JOIN segment_contract b USING (batch_id)
          GROUP BY a.batch_id,a.contact_id
          HAVING n>b.max_attempts_per_contact
        )),
       'Attempt count never exceeds the founder-set per-contact cap'
UNION ALL SELECT 'DQ05','error','attempt_spacing',
       (SELECT count(*) FROM (
          SELECT a.batch_id,a.contact_id,a.occurred_at,
                 lag(a.occurred_at) OVER (
                   PARTITION BY a.batch_id,a.contact_id
                   ORDER BY datetime(a.occurred_at),a.attempt_sequence
                 ) AS prior_occurred_at,
                 b.min_attempt_spacing_minutes
          FROM contact_attempt a JOIN segment_contract b USING (batch_id)
        ) x
        WHERE prior_occurred_at IS NOT NULL
          AND (julianday(occurred_at)-julianday(prior_occurred_at))*1440
              < min_attempt_spacing_minutes-0.000001),
       'Attempts obey the batch minimum spacing'
UNION ALL SELECT 'DQ06','error','attempt_sequence_contiguous',
       (SELECT count(*) FROM (
          SELECT attempt_sequence,
                 row_number() OVER (
                   PARTITION BY batch_id,contact_id
                   ORDER BY datetime(occurred_at),attempt_sequence
                 ) AS expected_sequence
          FROM contact_attempt
        ) WHERE attempt_sequence<>expected_sequence),
       'Attempt sequences are contiguous per batch and contact'
UNION ALL SELECT 'DQ07','error','attempts_only_from_ready_approved_requests',
       (SELECT count(*) FROM contact_attempt a
        JOIN exact_send_request r USING (send_request_id)
        JOIN v_send_gate g USING (send_request_id)
        WHERE r.approval_status<>'APPROVED'
           OR g.derived_gate_state<>'READY_TO_SEND'),
       'Only exact human-approved requests that remain READY become attempts'
UNION ALL SELECT 'DQ08','error','atomic_preflight_and_idempotency',
       (SELECT count(*) FROM contact_attempt
        WHERE datetime(execution_preflight_at)<>datetime(occurred_at)
           OR datetime(suppression_watermark_at)<>datetime(occurred_at)
           OR length(idempotency_key_ref_hash)<>64),
       'Each attempt has a send-time preflight, current suppression watermark, and key'
UNION ALL SELECT 'DQ09','error','human_only_no_auto_retry',
       (SELECT count(*) FROM contact_attempt
        WHERE execution_authority_code<>'HUMAN_ONLY' OR auto_retry_authorized<>0),
       'v1 execution cannot retry automatically'
UNION ALL SELECT 'DQ10','error','selected_eligible_contract_bound',
       (SELECT count(*) FROM contact_attempt a
        JOIN exact_send_request r USING (send_request_id)
        JOIN acquisition_account ac
          ON ac.batch_id=a.batch_id AND ac.account_id=a.account_id
        JOIN segment_contract b ON b.batch_id=a.batch_id
        WHERE ac.eligibility_status<>'ELIGIBLE' OR ac.selected_for_batch<>1
           OR r.segment_contract_ref_hash<>b.contract_ref_hash),
       'Every send binds the immutable selected/eligible account and contract hash'
UNION ALL SELECT 'DQ11','error','ready_fixture_set',
       CASE WHEN (SELECT count(*) FROM v_send_gate
                  WHERE derived_gate_state='READY_TO_SEND')=5
              AND NOT EXISTS (
                SELECT 1 FROM v_send_gate
                WHERE send_request_id IN (
                  'SYN_SEND_QUAL_1','SYN_SEND_LATE_1','SYN_SEND_LATE_2',
                  'SYN_SEND_BOUNCE_1','SYN_SEND_LEGAL_1'
                ) AND derived_gate_state<>'READY_TO_SEND'
              ) THEN 0 ELSE 1 END,
       'Exactly the five synthetic approved sends are READY'
UNION ALL SELECT 'DQ12','error','contactability_stop_gate',
       CASE WHEN EXISTS (SELECT 1 FROM v_send_gate
          WHERE send_request_id='SYN_SEND_SUPPRESS'
            AND derived_gate_state='STOP'
            AND instr(blocking_findings,'current_contactability_stop')>0)
          THEN 0 ELSE 1 END,
       'A STOP contactability review rejects execution'
UNION ALL SELECT 'DQ13','error','contactability_unknown_gate',
       CASE WHEN EXISTS (SELECT 1 FROM v_send_gate
          WHERE send_request_id='SYN_SEND_REVIEW_UNKNOWN'
            AND derived_gate_state='UNKNOWN') THEN 0 ELSE 1 END,
       'An UNKNOWN contactability review fails closed'
UNION ALL SELECT 'DQ14','error','active_suppression_stop_gate',
       CASE WHEN EXISTS (SELECT 1 FROM v_send_gate
          WHERE send_request_id='SYN_SEND_SUPPRESS'
            AND derived_gate_state='STOP'
            AND instr(blocking_findings,'active_suppression_stop')>0)
          THEN 0 ELSE 1 END,
       'An exact-scope active hard-stop suppression is STOP'
UNION ALL SELECT 'DQ15','error','investigation_pause_gate',
       CASE WHEN EXISTS (SELECT 1 FROM v_send_gate
          WHERE send_request_id='SYN_SEND_INVESTIGATION'
            AND derived_gate_state='PAUSE') THEN 0 ELSE 1 END,
       'An exact-scope investigation outranks UNKNOWN and yields PAUSE'
UNION ALL SELECT 'DQ16','error','retired_message_stop_gate',
       CASE WHEN EXISTS (SELECT 1 FROM v_send_gate
          WHERE send_request_id='SYN_SEND_RETIRED'
            AND derived_gate_state='STOP') THEN 0 ELSE 1 END,
       'A retired message version is STOP'
UNION ALL SELECT 'DQ17','error','stopped_claim_gate',
       CASE WHEN EXISTS (SELECT 1 FROM v_send_gate
          WHERE send_request_id='SYN_SEND_CLAIM_STOP'
            AND derived_gate_state='STOP') THEN 0 ELSE 1 END,
       'A STOP claim rejects the exact message'
UNION ALL SELECT 'DQ18','error','unknown_claim_gate',
       CASE WHEN EXISTS (SELECT 1 FROM v_send_gate
          WHERE send_request_id='SYN_SEND_CLAIM_UNKNOWN'
            AND derived_gate_state='UNKNOWN') THEN 0 ELSE 1 END,
       'An UNKNOWN claim fails closed'
UNION ALL SELECT 'DQ19','error','stale_trigger_gate',
       CASE WHEN EXISTS (SELECT 1 FROM v_send_gate
          WHERE send_request_id='SYN_SEND_STALE'
            AND derived_gate_state='UNKNOWN') THEN 0 ELSE 1 END,
       'A trigger beyond valid_until fails closed'
UNION ALL SELECT 'DQ20','error','spacing_pause_gate',
       CASE WHEN EXISTS (SELECT 1 FROM v_send_gate
          WHERE send_request_id='SYN_SEND_QUAL_SPACING'
            AND derived_gate_state='PAUSE') THEN 0 ELSE 1 END,
       'An attempt before the founder-set interval is PAUSE'
UNION ALL SELECT 'DQ21','error','attempt_cap_pause_gate',
       CASE WHEN EXISTS (SELECT 1 FROM v_send_gate
          WHERE send_request_id='SYN_SEND_LATE_CAP'
            AND derived_gate_state='PAUSE') THEN 0 ELSE 1 END,
       'A request after the founder-set attempt cap is PAUSE'
UNION ALL SELECT 'DQ22','error','hard_bounce_stop_gate',
       CASE WHEN EXISTS (SELECT 1 FROM v_send_gate
          WHERE send_request_id='SYN_SEND_BOUNCE_2'
            AND derived_gate_state='STOP'
            AND instr(blocking_findings,'prior_hard_bounce')>0)
          THEN 0 ELSE 1 END,
       'A prior hard bounce is STOP, not an automatic retry'
UNION ALL SELECT 'DQ23','error','legal_stop_gate',
       CASE WHEN EXISTS (SELECT 1 FROM v_send_gate
          WHERE send_request_id='SYN_SEND_LEGAL_2'
            AND derived_gate_state='STOP'
            AND instr(blocking_findings,'prior_legal_stop_reply')>0)
          THEN 0 ELSE 1 END,
       'A prior legal-stop human reply is STOP at account/contact scope'
UNION ALL SELECT 'DQ24','error','email_only_v1_gate',
       CASE WHEN EXISTS (SELECT 1 FROM v_send_gate
          WHERE send_request_id='SYN_SEND_FORM'
            AND derived_gate_state='STOP'
            AND instr(blocking_findings,'unsupported_channel_v1_email_only')>0)
          THEN 0 ELSE 1 END,
       'Non-email execution is fail-closed in v1'
UNION ALL SELECT 'DQ25','error','blocked_requests_never_attempted',
       (SELECT count(*) FROM v_send_gate g JOIN contact_attempt a USING (send_request_id)
        WHERE g.derived_gate_state<>'READY_TO_SEND'),
       'STOP, PAUSE, and UNKNOWN requests never enter the attempt ledger'
UNION ALL SELECT 'DQ26','error','no_send_after_hard_bounce',
       (SELECT count(*) FROM contact_attempt later
        WHERE EXISTS (
          SELECT 1 FROM contact_attempt prior JOIN delivery_event d
            ON d.attempt_id=prior.attempt_id
          WHERE prior.batch_id=later.batch_id
            AND prior.contact_id=later.contact_id
            AND d.outcome_code='HARD_BOUNCE'
            AND datetime(d.occurred_at)<datetime(later.occurred_at)
            AND datetime(d.recorded_at)<=datetime(later.occurred_at)
        )),
       'No attempt occurs after a known hard bounce'
UNION ALL SELECT 'DQ27','error','no_send_after_legal_stop_or_complaint',
       (SELECT count(*) FROM contact_attempt later
        WHERE EXISTS (
          SELECT 1 FROM reply_event r
          WHERE r.batch_id=later.batch_id
            AND (r.contact_id=later.contact_id OR r.account_id=later.account_id)
            AND r.outcome_code='LEGAL_STOP'
            AND datetime(r.occurred_at)<datetime(later.occurred_at)
            AND datetime(r.recorded_at)<=datetime(later.occurred_at)
        ) OR EXISTS (
          SELECT 1 FROM recipient_feedback_event f
          WHERE f.batch_id=later.batch_id
            AND (f.contact_id=later.contact_id OR f.account_id=later.account_id)
            AND f.feedback_code IN ('COMPLAINT','SPAM_REPORT')
            AND datetime(f.occurred_at)<datetime(later.occurred_at)
            AND datetime(f.recorded_at)<=datetime(later.occurred_at)
        )),
       'No attempt occurs after a legal stop or recorded complaint'
UNION ALL SELECT 'DQ28','error','no_future_evidence',
       (SELECT count(*) FROM (
          SELECT occurred_at,recorded_at FROM acquisition_account
          UNION ALL SELECT occurred_at,recorded_at FROM trigger_evidence
          UNION ALL SELECT occurred_at,recorded_at FROM contact
          UNION ALL SELECT occurred_at,recorded_at FROM contactability_review
          UNION ALL SELECT occurred_at,recorded_at FROM suppression_event
          UNION ALL SELECT occurred_at,recorded_at FROM claim_evidence
          UNION ALL SELECT occurred_at,recorded_at FROM contact_attempt
          UNION ALL SELECT occurred_at,recorded_at FROM delivery_event
          UNION ALL SELECT occurred_at,recorded_at FROM recipient_feedback_event
          UNION ALL SELECT occurred_at,recorded_at FROM reply_event
          UNION ALL SELECT occurred_at,recorded_at FROM meeting_event
          UNION ALL SELECT occurred_at,recorded_at FROM discovery_note
          UNION ALL SELECT occurred_at,recorded_at FROM source_touch
          UNION ALL SELECT occurred_at,recorded_at FROM qualification_handoff
          UNION ALL SELECT occurred_at,recorded_at FROM handoff_requirement_evidence
          UNION ALL SELECT occurred_at,recorded_at FROM acquisition_decision
          UNION ALL SELECT occurred_at,recorded_at FROM acquisition_cost_event
        ) e CROSS JOIN model_settings m
        WHERE datetime(e.occurred_at)>datetime(m.event_time_as_of)
           OR datetime(e.recorded_at)>datetime(m.event_time_as_of)),
       'No event occurrence or recording exceeds model event_time_as_of'
UNION ALL SELECT 'DQ29','error','immutable_evidence_tables',
       (SELECT count(*) FROM (
          SELECT 'segment_contract' AS table_name
          UNION ALL SELECT 'acquisition_account'
          UNION ALL SELECT 'trigger_evidence'
          UNION ALL SELECT 'contact'
          UNION ALL SELECT 'contactability_review'
          UNION ALL SELECT 'suppression_event'
          UNION ALL SELECT 'claim_evidence'
          UNION ALL SELECT 'message_version'
          UNION ALL SELECT 'message_claim'
          UNION ALL SELECT 'exact_send_request'
          UNION ALL SELECT 'contact_attempt'
          UNION ALL SELECT 'delivery_event'
          UNION ALL SELECT 'recipient_feedback_event'
          UNION ALL SELECT 'reply_event'
          UNION ALL SELECT 'meeting_event'
          UNION ALL SELECT 'discovery_note'
          UNION ALL SELECT 'source_touch'
          UNION ALL SELECT 'qualification_handoff'
          UNION ALL SELECT 'handoff_requirement_evidence'
          UNION ALL SELECT 'acquisition_decision'
          UNION ALL SELECT 'acquisition_cost_event'
        ) required
        WHERE NOT EXISTS (
          SELECT 1 FROM sqlite_schema s
          WHERE s.type='trigger' AND s.tbl_name=required.table_name
            AND s.name LIKE 'immutable_%_update'
        ) OR NOT EXISTS (
          SELECT 1 FROM sqlite_schema s
          WHERE s.type='trigger' AND s.tbl_name=required.table_name
            AND s.name LIKE 'immutable_%_delete'
        )),
       'Mutable master/version and event evidence reject UPDATE and DELETE'
UNION ALL SELECT 'DQ30','error','attempt_trigger_rechecks_gate_and_limits',
       CASE WHEN EXISTS (
          SELECT 1 FROM sqlite_schema WHERE type='trigger'
            AND name='validate_contact_attempt_before_insert'
            AND instr(sql,'v_send_gate')>0
            AND instr(sql,'attempt cap reached')>0
            AND instr(sql,'minimum attempt spacing not elapsed')>0
            AND instr(sql,'unknown receipt requires explicit human retry review')>0
        ) THEN 0 ELSE 1 END,
       'Execution-time trigger atomically rechecks gate, cap, spacing, and retry review'
UNION ALL SELECT 'DQ31','error','complaint_is_separate_feedback_fact',
       CASE WHEN NOT EXISTS (
          SELECT 1 FROM pragma_table_info('delivery_event')
          WHERE name='feedback_code'
        ) AND EXISTS (
          SELECT 1 FROM sqlite_schema WHERE type='table'
            AND name='recipient_feedback_event'
        ) AND NOT EXISTS (
          SELECT 1 FROM delivery_event WHERE outcome_code='COMPLAINT'
        ) THEN 0 ELSE 1 END,
       'A complaint never overwrites delivery; it lives in feedback evidence'
UNION ALL SELECT 'DQ32','error','reply_timing_is_snapshot_derived',
       CASE WHEN NOT EXISTS (
          SELECT 1 FROM pragma_table_info('reply_event') WHERE name='timing_class'
        ) AND EXISTS (
          SELECT 1 FROM sqlite_schema WHERE type='view' AND name='v_cutoff_human_reply'
            AND instr(sql,'outcome_cutoff_at')>0 AND instr(sql,'snapshot_as_of')>0
        ) THEN 0 ELSE 1 END,
       'Reply timing is derived from occurrence and recording against each snapshot'
UNION ALL SELECT 'DQ33','error','nonhuman_reply_cannot_be_positive',
       (SELECT count(*) FROM reply_event
        WHERE reply_actor_code<>'HUMAN' AND outcome_code NOT IN ('OTHER','UNKNOWN')),
       'Auto/system/unknown replies cannot carry human business outcomes'
UNION ALL SELECT 'DQ34','error','account_funnel_fixture',
       CASE WHEN EXISTS (
          SELECT 1 FROM v_account_funnel WHERE batch_id='SYN_BATCH_01'
            AND eligible_accounts=14 AND selected_accounts=13
            AND attempted_accounts=4 AND human_replied_accounts=2
            AND no_human_reply_by_cutoff_accounts=2 AND late_reply_accounts=1
            AND held_meeting_accounts=1 AND discovery_evidenced_accounts=1
            AND qualified_accounts=1 AND selected_without_contact_accounts=1
        ) THEN 0 ELSE 1 END,
       'Account-grain funnel preserves selected, no-response, late, and Qualified counts'
UNION ALL SELECT 'DQ35','error','account_no_response_identity',
       (SELECT count(*) FROM v_account_funnel
        WHERE attempted_accounts<>human_replied_accounts
                               +no_human_reply_by_cutoff_accounts),
       'At cutoff every attempted account is replied or explicit no-response'
UNION ALL SELECT 'DQ36','error','contact_funnel_fixture',
       CASE WHEN EXISTS (
          SELECT 1 FROM v_contact_funnel WHERE batch_id='SYN_BATCH_01'
            AND reviewed_contacts=12 AND contactable_pass_contacts_at_cutoff=10
            AND stop_contacts=1 AND unknown_or_stale_contacts=1
            AND attempted_contacts=4 AND human_replied_contacts=2
            AND no_human_reply_by_cutoff_contacts=2 AND wrong_person_contacts=0
            AND late_reply_contacts=1
        ) THEN 0 ELSE 1 END,
       'Contact-grain funnel remains separate from account and attempt counts'
UNION ALL SELECT 'DQ37','error','attempt_funnel_fixture',
       CASE WHEN EXISTS (
          SELECT 1 FROM v_attempt_funnel WHERE batch_id='SYN_BATCH_01'
            AND attempted_sends=5 AND accepted_signal_attempts=5
            AND current_delivered_signal_attempts=1
            AND current_hard_bounce_attempts=1 AND delivery_unknown_attempts=0
            AND human_replied_attempts=2
            AND no_human_reply_by_cutoff_attempts=3 AND late_reply_attempts=1
        ) THEN 0 ELSE 1 END,
       'Attempt-grain delivery and reply counts do not masquerade as accounts'
UNION ALL SELECT 'DQ38','error','late_reply_does_not_backfill_cutoff',
       CASE WHEN EXISTS (
          SELECT 1 FROM v_late_human_reply
          WHERE account_id='SYN_ACC_LATE'
        ) AND NOT EXISTS (
          SELECT 1 FROM v_cutoff_human_reply
          WHERE account_id='SYN_ACC_LATE'
        ) AND EXISTS (
          SELECT 1 FROM v_account_funnel
          WHERE no_human_reply_by_cutoff_accounts=2 AND late_reply_accounts=1
        ) THEN 0 ELSE 1 END,
       'A late reply remains visible without changing the frozen no-response numerator'
UNION ALL SELECT 'DQ39','error','late_and_restatement_inventory',
       CASE WHEN (SELECT count(*) FROM v_post_cutoff_event_inventory)=7
          AND EXISTS (SELECT 1 FROM v_post_cutoff_event_inventory
                      WHERE event_domain='REPLY' AND late_or_restatement='LATE_EVENT')
          AND EXISTS (SELECT 1 FROM v_post_cutoff_event_inventory
                      WHERE event_domain='DELIVERY' AND late_or_restatement='LATE_EVENT')
          AND EXISTS (SELECT 1 FROM v_post_cutoff_event_inventory
                      WHERE event_domain='COMPLAINT'
                        AND late_or_restatement='RESTATEMENT_CANDIDATE')
          AND EXISTS (SELECT 1 FROM v_post_cutoff_event_inventory
                      WHERE event_domain='FEEDBACK'
                        AND late_or_restatement='LATE_EVENT')
          AND EXISTS (SELECT 1 FROM v_post_cutoff_event_inventory
                      WHERE event_domain='MEETING'
                        AND late_or_restatement='RESTATEMENT_CANDIDATE')
          AND EXISTS (SELECT 1 FROM v_post_cutoff_event_inventory
                      WHERE event_domain='SUPPRESSION'
                        AND late_or_restatement='RESTATEMENT_CANDIDATE')
          THEN 0 ELSE 1 END,
       'Reply, delivery, feedback, complaint, meeting, and suppression late facts are explicit'
UNION ALL SELECT 'DQ40','error','attempted_accounts_have_one_first_touch',
       (SELECT count(*) FROM (
          SELECT a.batch_id,a.account_id FROM v_cutoff_attempt a
          GROUP BY a.batch_id,a.account_id
        ) x WHERE (SELECT count(*) FROM source_touch s
                   WHERE s.batch_id=x.batch_id AND s.account_id=x.account_id
                     AND s.touch_role_code='FIRST_TOUCH')<>1),
       'Each attempted account has exactly one first-touch fact'
UNION ALL SELECT 'DQ41','error','first_touch_and_assist_are_not_weighted',
       CASE WHEN EXISTS (
          SELECT 1 FROM v_account_attribution
          WHERE account_id='SYN_ACC_QUAL'
            AND first_touch_source_id='SYN_TOUCH_QUAL_FIRST'
            AND assist_touch_ids='SYN_TOUCH_QUAL_ASSIST'
        ) AND NOT EXISTS (
          SELECT 1 FROM pragma_table_info('source_touch')
          WHERE name LIKE '%weight%' OR name LIKE '%credit%'
        ) THEN 0 ELSE 1 END,
       'First touch is singular; assists are facts without arbitrary fractional credit'
UNION ALL SELECT 'DQ42','error','qualified_handoff_has_nine_requirements',
       CASE WHEN EXISTS (
          SELECT 1 FROM v_handoff_readiness
          WHERE qualification_package_id='SYN_HANDOFF_QUAL'
            AND represented_requirement_count=9
            AND satisfied_requirement_count=9
            AND stop_requirement_count=0 AND unknown_requirement_count=0
            AND derived_handoff_state='READY_FOR_CHAPTER_11'
        ) THEN 0 ELSE 1 END,
       'Qualified handoff is ready only with all nine evidence requirements'
UNION ALL SELECT 'DQ43','error','qualified_decision_same_snapshot',
       (SELECT count(*) FROM acquisition_decision d
        JOIN qualification_handoff h
          ON h.qualification_package_id=d.qualification_package_id
        WHERE d.decision_code='QUALIFIED' AND (
          datetime(d.outcome_cutoff_at)<>datetime(h.outcome_cutoff_at)
          OR datetime(d.snapshot_as_of)<>datetime(h.snapshot_as_of)
        )),
       'QUALIFIED consumes the exact handoff outcome cutoff and snapshot-as-of'
UNION ALL SELECT 'DQ44','error','qualified_only_when_handoff_ready',
       (SELECT count(*) FROM acquisition_decision d
        LEFT JOIN v_handoff_readiness h
          ON h.qualification_package_id=d.qualification_package_id
        WHERE d.decision_code='QUALIFIED'
          AND (h.derived_handoff_state IS NULL
            OR h.derived_handoff_state<>'READY_FOR_CHAPTER_11'
            OR h.batch_id<>d.batch_id OR h.account_id<>d.account_id)),
       'No positive feeling, meeting, or incomplete package can create Qualified'
UNION ALL SELECT 'DQ45','error','economics_fixture_totals',
       CASE WHEN EXISTS (
          SELECT 1 FROM v_batch_acquisition_economics
          WHERE batch_id='SYN_BATCH_01' AND cash_cost_micros=12000000000
            AND founder_minutes=600 AND founder_time_cost_micros=60000000000
            AND fully_loaded_cost_micros=72000000000 AND qualified_accounts=1
        ) THEN 0 ELSE 1 END,
       'Cash, founder minutes, time value, and fully-loaded cost remain separate'
UNION ALL SELECT 'DQ46','error','founder_time_formula',
       (SELECT count(*) FROM v_batch_acquisition_economics e
        CROSS JOIN model_settings m
        WHERE e.founder_time_cost_micros<>
          e.founder_minutes*m.founder_internal_hourly_value_micros/60),
       'Founder time cost is minutes / 60 x internal hourly value'
UNION ALL SELECT 'DQ47','error','qualified_cost_is_not_named_cac',
       CASE WHEN EXISTS (
          SELECT 1 FROM sqlite_schema WHERE type='view'
            AND name='v_batch_cost_per_qualified'
        ) AND NOT EXISTS (
          SELECT 1 FROM sqlite_schema WHERE type='view' AND name LIKE '%cac%'
        ) THEN 0 ELSE 1 END,
       'Pre-customer Qualified economics are named cost-per-Qualified, not CAC'
UNION ALL SELECT 'DQ48','error','fully_loaded_cost_reconciles',
       (SELECT count(*) FROM v_batch_cost_per_qualified
        WHERE fully_loaded_cost_micros<>
              cash_cost_micros+founder_time_cost_micros
           OR fully_loaded_cost_per_qualified_micros<>
              cash_cost_per_qualified_micros
              +founder_time_cost_per_qualified_micros),
       'Fully-loaded cost reconciles exactly to cash plus founder-time value'
UNION ALL SELECT 'DQ49','error','selected_without_contact_visible',
       CASE WHEN EXISTS (
          SELECT 1 FROM v_account_funnel
          WHERE batch_id='SYN_BATCH_01' AND selected_without_contact_accounts=1
        ) THEN 0 ELSE 1 END,
       'Selected accounts without a contact remain visible in the denominator'
UNION ALL SELECT 'DQ50','error','suppression_scope_is_explicit',
       (SELECT count(*) FROM suppression_event
        WHERE channel_scope='' OR purpose_scope='' OR product_scope=''
           OR sender_scope='' OR datetime(effective_from) IS NULL
           OR (effective_until IS NOT NULL AND (
             action_code<>'SUPPRESS' OR reason_code NOT IN (
               'SOFT_BOUNCE','DEFERRED','INVESTIGATION','PROVIDER_STOP'
             )
           ))),
       'Suppression carries channel, purpose, product, sender, and effective-time scope'
UNION ALL SELECT 'DQ51','error','delivery_current_fact_not_overwritten',
       CASE WHEN EXISTS (SELECT 1 FROM v_cutoff_latest_delivery
                         WHERE attempt_id='SYN_ATTEMPT_BOUNCE_1'
                           AND outcome_code='HARD_BOUNCE')
          AND EXISTS (SELECT 1 FROM v_cutoff_latest_delivery
                      WHERE attempt_id='SYN_ATTEMPT_LEGAL_1'
                        AND outcome_code='ACCEPTED')
          AND EXISTS (SELECT 1 FROM v_cutoff_latest_delivery
                      WHERE attempt_id='SYN_ATTEMPT_LATE_2'
                        AND outcome_code='ACCEPTED')
          THEN 0 ELSE 1 END,
       'Cutoff delivery uses latest known delivery without complaint or late-event leakage'
UNION ALL SELECT 'DQ52','error','meeting_restatement_does_not_backfill',
       CASE WHEN EXISTS (SELECT 1 FROM v_cutoff_latest_meeting
                         WHERE meeting_id='SYN_MEETING_QUAL'
                           AND meeting_event_id='SYN_MEETING_QUAL_HELD')
          AND NOT EXISTS (SELECT 1 FROM v_cutoff_latest_meeting
                          WHERE meeting_event_id='SYN_MEETING_QUAL_RESTATE')
          THEN 0 ELSE 1 END,
       'A meeting fact recorded after snapshot_as_of does not backfill the snapshot'
UNION ALL SELECT 'DQ53','error','frozen_views_use_two_time_axes',
       (SELECT count(*) FROM (
          SELECT 'v_cutoff_attempt' AS view_name
          UNION ALL SELECT 'v_cutoff_human_reply'
          UNION ALL SELECT 'v_cutoff_latest_contactability'
          UNION ALL SELECT 'v_cutoff_latest_delivery'
          UNION ALL SELECT 'v_cutoff_latest_meeting'
          UNION ALL SELECT 'v_cutoff_latest_decision'
        ) required WHERE NOT EXISTS (
          SELECT 1 FROM sqlite_schema s WHERE s.type='view'
            AND s.name=required.view_name
            AND instr(s.sql,'outcome_cutoff_at')>0
            AND instr(s.sql,'snapshot_as_of')>0
        )),
       'Every frozen view filters occurred_at by cutoff and recorded_at by snapshot'
UNION ALL SELECT 'DQ54','error','contactability_summary_matches_components',
       (SELECT count(*) FROM contactability_review v WHERE
          (v.review_result='PASS' AND (
            v.specific_email_law_result NOT IN ('PASS','NOT_APPLICABLE')
            OR v.specified_commercial_transactions_result
               NOT IN ('PASS','NOT_APPLICABLE')
            OR v.privacy_purpose_result NOT IN ('PASS','NOT_APPLICABLE')
            OR v.platform_terms_result NOT IN ('PASS','NOT_APPLICABLE')
            OR v.consent_or_exception_record_result<>'PASS'
            OR v.no_solicitation_public_source_result
               NOT IN ('PASS','NOT_APPLICABLE')
            OR v.sender_identity_result<>'PASS'
            OR v.sender_address_display_result<>'PASS'
            OR v.complaint_inquiry_contact_display_result<>'PASS'
            OR v.opt_out_result<>'PASS'
            OR v.authentication_result<>'PASS'
          )) OR (v.review_result='STOP' AND 'STOP' NOT IN (
            v.specific_email_law_result,v.specified_commercial_transactions_result,
            v.privacy_purpose_result,v.platform_terms_result,
            v.consent_or_exception_record_result,
            v.no_solicitation_public_source_result,v.sender_identity_result,
            v.sender_address_display_result,
            v.complaint_inquiry_contact_display_result,v.opt_out_result,
            v.authentication_result
          )) OR (v.review_result='UNKNOWN' AND 'UNKNOWN' NOT IN (
            v.specific_email_law_result,v.specified_commercial_transactions_result,
            v.privacy_purpose_result,v.platform_terms_result,
            v.consent_or_exception_record_result,
            v.no_solicitation_public_source_result,v.sender_identity_result,
            v.sender_address_display_result,
            v.complaint_inquiry_contact_display_result,v.opt_out_result,
            v.authentication_result
          )) OR ('NOT_APPLICABLE' IN (
            v.specific_email_law_result,v.specified_commercial_transactions_result,
            v.privacy_purpose_result,v.platform_terms_result,
            v.no_solicitation_public_source_result
          ) AND v.not_applicable_basis_ref_hash IS NULL)),
       'Summary PASS/STOP/UNKNOWN agrees with each required review component'
UNION ALL SELECT 'DQ55','error','decision_compatibility',
       (SELECT count(*) FROM acquisition_decision d
        WHERE (d.primary_reason_code='WRONG_PERSON'
               AND d.decision_code IN ('LOST','STOP','QUALIFIED'))
           OR (d.decision_code='NURTURE'
               AND (d.restart_trigger_ref_hash IS NULL
                 OR d.earliest_restart_at IS NULL
                 OR datetime(d.earliest_restart_at)<=datetime(d.occurred_at)))
           OR (d.decision_code='STOP' AND d.stop_evidence_ref_hash IS NULL)
           OR (d.decision_code='LOST'
               AND d.change_domain_code IN ('NONE','UNKNOWN'))),
       'Wrong-person, Nurture, STOP, and Lost decisions obey compatibility rules'
UNION ALL SELECT 'DQ56','error','cost_caps_are_founder_set',
       (SELECT count(*) FROM v_batch_acquisition_economics e
        JOIN segment_contract b USING (batch_id)
        WHERE e.cash_cost_micros>b.max_cash_micros
           OR e.founder_minutes>b.max_founder_minutes),
       'Synthetic costs remain within the explicit founder-set loss caps'
UNION ALL SELECT 'DQ57','error','gate_priority_order',
       CASE WHEN (SELECT group_concat(gate_state_code,'>') FROM (
          SELECT gate_state_code FROM gate_state_dim ORDER BY priority_order
        ))='STOP>PAUSE>UNKNOWN>READY_TO_SEND' THEN 0 ELSE 1 END,
       'Gate precedence is STOP > PAUSE > UNKNOWN > READY_TO_SEND'
UNION ALL SELECT 'DQ58','error','fixture_identifiers_are_opaque_synthetic',
       (SELECT count(*) FROM acquisition_account WHERE account_id NOT LIKE 'SYN_%')
       +(SELECT count(*) FROM contact WHERE contact_id NOT LIKE 'SYN_%')
       +(SELECT count(*) FROM contact_attempt WHERE attempt_id NOT LIKE 'SYN_%'),
       'Public fixture keys are opaque synthetic IDs, never contact strings'
UNION ALL SELECT 'DQ59','error','no_fractional_attribution_fields',
       (SELECT count(*) FROM pragma_table_info('source_touch')
        WHERE name LIKE '%weight%' OR name LIKE '%credit%' OR name LIKE '%percent%'),
       'Attribution records chronology, not invented multi-touch fractions'
UNION ALL SELECT 'DQ60','error','all_no_response_denominators_reconcile',
       (SELECT count(*) FROM v_contact_funnel
        WHERE attempted_contacts<>human_replied_contacts
                               +no_human_reply_by_cutoff_contacts)
       +(SELECT count(*) FROM v_attempt_funnel
         WHERE attempted_sends<>human_replied_attempts
                               +no_human_reply_by_cutoff_attempts),
       'Contact and attempt funnels preserve every no-response denominator'
UNION ALL SELECT 'DQ61','error','complaint_restatement_is_separate',
       CASE WHEN EXISTS (
          SELECT 1 FROM recipient_feedback_event
          WHERE feedback_event_id='SYN_FEEDBACK_LEGAL_COMPLAINT'
        ) AND EXISTS (
          SELECT 1 FROM v_post_cutoff_event_inventory
          WHERE event_id='SYN_FEEDBACK_LEGAL_COMPLAINT'
            AND event_domain='COMPLAINT'
            AND late_or_restatement='RESTATEMENT_CANDIDATE'
        ) THEN 0 ELSE 1 END,
       'A late-recorded complaint is reviewable without rewriting delivery'
UNION ALL SELECT 'DQ62','error','single_use_send_request_and_idempotency',
       CASE WHEN NOT EXISTS (
          SELECT send_request_id FROM contact_attempt
          GROUP BY send_request_id HAVING count(*)>1
        ) AND NOT EXISTS (
          SELECT idempotency_key_ref_hash FROM contact_attempt
          GROUP BY idempotency_key_ref_hash HAVING count(*)>1
        ) THEN 0 ELSE 1 END,
       'An exact approval and idempotency key can execute at most once'
UNION ALL SELECT 'DQ63','error','approval_not_expired_at_execution',
       (SELECT count(*) FROM contact_attempt a JOIN exact_send_request r USING (send_request_id)
        WHERE datetime(r.approved_at)>datetime(a.occurred_at)
           OR datetime(r.approval_recorded_at)>datetime(a.occurred_at)
           OR datetime(r.approval_expires_at)<datetime(a.occurred_at)),
       'Every attempt executes after approval recording and before approval expiry'
UNION ALL SELECT 'DQ64','error','unknown_receipt_retry_is_fail_closed',
       CASE WHEN EXISTS (
          SELECT 1 FROM sqlite_schema WHERE type='trigger'
            AND name='validate_contact_attempt_before_insert'
            AND instr(sql,'unknown receipt requires explicit human retry review')>0
        ) AND NOT EXISTS (
          SELECT 1 FROM contact_attempt WHERE auto_retry_authorized<>0
        ) THEN 0 ELSE 1 END,
       'UNKNOWN/no receipt cannot cause an automatic retry'
UNION ALL SELECT 'DQ65','error','hard_stop_feedback_gate_is_present',
       CASE WHEN EXISTS (
          SELECT 1 FROM sqlite_schema WHERE type='view' AND name='v_send_gate_findings'
            AND instr(sql,'prior_complaint_or_stop_feedback')>0
            AND instr(sql,'recipient_feedback_event')>0
        ) THEN 0 ELSE 1 END,
       'Complaint, spam, explicit opt-out, and legal-stop feedback are direct STOP inputs'
UNION ALL SELECT 'DQ66','error','qualified_cost_per_qualified_fixture',
       CASE WHEN EXISTS (
          SELECT 1 FROM v_batch_cost_per_qualified
          WHERE batch_id='SYN_BATCH_01'
            AND cash_cost_per_qualified_micros=12000000000
            AND founder_time_cost_per_qualified_micros=60000000000
            AND fully_loaded_cost_per_qualified_micros=72000000000
            AND cost_per_qualified_state='MATURE_AT_CUTOFF'
        ) THEN 0 ELSE 1 END,
       'Cost per Qualified is explicit; customer CAC remains out of scope'
UNION ALL SELECT 'DQ67','error','only_pause_suppression_may_expire',
       (SELECT count(*) FROM suppression_event
        WHERE effective_until IS NOT NULL AND (
          action_code<>'SUPPRESS' OR reason_code NOT IN (
            'SOFT_BOUNCE','DEFERRED','INVESTIGATION','PROVIDER_STOP'
          )
        )),
       'Verified STOP suppressions never auto-expire; only a PAUSE class may expire'
UNION ALL SELECT 'DQ68','error','release_exactly_supersedes_active_scope',
       CASE WHEN EXISTS (
          SELECT 1 FROM sqlite_schema WHERE type='trigger'
            AND name='validate_suppression_event_before_insert'
            AND instr(sql,'release must exactly supersede one active suppression')>0
            AND instr(sql,'p.channel_scope=NEW.channel_scope')>0
            AND instr(sql,'p.purpose_scope=NEW.purpose_scope')>0
            AND instr(sql,'p.product_scope=NEW.product_scope')>0
            AND instr(sql,'p.sender_scope=NEW.sender_scope')>0
        ) AND EXISTS (
          SELECT 1 FROM sqlite_schema WHERE type='view'
            AND name='v_send_gate_findings'
            AND instr(sql,'suppression_stream_id')>0
            AND instr(sql,'later.event_sequence>s.event_sequence')>0
        ) THEN 0 ELSE 1 END,
       'RELEASE clears only its exact stream; overlapping active streams remain blockers'
UNION ALL SELECT 'DQ69','error','hard_bounce_has_stop_suppression',
       (SELECT count(*) FROM delivery_event d
        LEFT JOIN suppression_event s
          ON s.suppression_event_id=d.suppression_event_id
        WHERE d.outcome_code='HARD_BOUNCE' AND (
          s.suppression_event_id IS NULL OR s.action_code<>'SUPPRESS'
          OR s.reason_code<>'HARD_BOUNCE' OR s.effective_until IS NOT NULL
        )),
       'Every hard bounce carries a non-expiring exact STOP suppression'
UNION ALL SELECT 'DQ70','error','feedback_verification_controls_gate',
       CASE WHEN NOT EXISTS (
          SELECT 1 FROM recipient_feedback_event f
          LEFT JOIN suppression_event s
            ON s.suppression_event_id=f.suppression_event_id
          WHERE f.verification_status='VERIFIED' AND (
            s.suppression_event_id IS NULL OR s.action_code<>'SUPPRESS'
            OR s.reason_code<>f.feedback_code
          )
        ) AND EXISTS (
          SELECT 1 FROM v_send_gate
          WHERE send_request_id='SYN_SEND_AFTER_UNRESOLVED'
            AND derived_gate_state='PAUSE'
            AND instr(blocking_findings,'unresolved_feedback_requires_review')>0
        ) THEN 0 ELSE 1 END,
       'Verified stop feedback has suppression; unresolved feedback is at least PAUSE'
UNION ALL SELECT 'DQ71','error','event_revisions_follow_exact_causal_chain',
       (SELECT count(*) FROM delivery_event e
        WHERE e.event_sequence>1 AND NOT EXISTS (
          SELECT 1 FROM delivery_event p
          WHERE p.delivery_event_id=e.supersedes_delivery_event_id
            AND p.attempt_id=e.attempt_id
            AND p.event_sequence=e.event_sequence-1
        ))
       +(SELECT count(*) FROM recipient_feedback_event e
         WHERE e.event_sequence>1 AND NOT EXISTS (
           SELECT 1 FROM recipient_feedback_event p
           WHERE p.feedback_event_id=e.supersedes_feedback_event_id
             AND p.feedback_stream_id=e.feedback_stream_id
             AND p.event_sequence=e.event_sequence-1
         ))
       +(SELECT count(*) FROM suppression_event e
         WHERE e.event_sequence>1 AND NOT EXISTS (
           SELECT 1 FROM suppression_event p
           WHERE p.suppression_event_id=e.supersedes_suppression_event_id
             AND p.suppression_stream_id=e.suppression_stream_id
             AND p.event_sequence=e.event_sequence-1
         ))
       +(SELECT count(*) FROM meeting_event e
         WHERE e.event_sequence>1 AND NOT EXISTS (
           SELECT 1 FROM meeting_event p
           WHERE p.meeting_event_id=e.supersedes_meeting_event_id
             AND p.meeting_id=e.meeting_id
             AND p.event_sequence=e.event_sequence-1
         )),
       'Current facts follow source sequence and exact supersession, never ingest order'
UNION ALL SELECT 'DQ72','error','meeting_status_excludes_data_permissions',
       (SELECT count(*) FROM pragma_table_info('meeting_event')
        WHERE name LIKE '%consent%' OR name LIKE '%recording%'
           OR name LIKE '%transcript%' OR name LIKE '%artifact%'
           OR name LIKE '%ai_processing%'),
       'MeetingStatusEvent does not falsely double as recording/AI data-handling consent'
UNION ALL SELECT 'DQ73','error','email_contactability_required_checks_exist',
       CASE WHEN (SELECT count(*) FROM pragma_table_info('contactability_review')
          WHERE name IN (
            'specified_commercial_transactions_result',
            'sender_address_display_result',
            'complaint_inquiry_contact_display_result',
            'consent_or_exception_record_result',
            'channel_check_matrix_version',
            'not_applicable_basis_ref_hash',
            'no_solicitation_public_source_result'
          ))=7 THEN 0 ELSE 1 END,
       'Email review records all explicit legal, display, exception, matrix, and refusal checks'
UNION ALL SELECT 'DQ74','error','public_address_no_solicitation_fail_closed',
       (SELECT count(*) FROM contactability_review
        WHERE candidate_basis_code='PUBLIC_BUSINESS_ADDRESS'
          AND no_solicitation_public_source_result IN ('STOP','UNKNOWN')
          AND review_result='PASS'),
       'A public-address basis cannot PASS when no-solicitation evidence is STOP/UNKNOWN'
UNION ALL SELECT 'DQ75','error','ambiguous_contactability_revision_rejected',
       CASE WHEN EXISTS (
          SELECT 1 FROM sqlite_schema WHERE type='table'
            AND name='contactability_review'
            AND instr(sql,'batch_id,contact_id,channel_code,exact_purpose_code,exact_sender_id')>0
        ) AND EXISTS (
          SELECT 1 FROM sqlite_schema WHERE type='trigger'
            AND name='validate_contactability_review_before_insert'
            AND instr(sql,'exact append-only tuple chain')>0
        ) THEN 0 ELSE 1 END,
       'Ambiguous parallel review order is rejected instead of guessed from ingest time'
UNION ALL SELECT 'DQ76','error','feedback_late_event_is_explicit',
       CASE WHEN EXISTS (
          SELECT 1 FROM v_post_cutoff_event_inventory
          WHERE event_id='SYN_FEEDBACK_LATE_UNRESOLVED'
            AND event_domain='FEEDBACK' AND late_or_restatement='LATE_EVENT'
        ) THEN 0 ELSE 1 END,
       'Unresolved feedback after cutoff is explicit and cannot backfill the snapshot'
UNION ALL SELECT 'DQ77','error','cost_model_stops_before_customer_cac',
       CASE WHEN NOT EXISTS (
          SELECT 1 FROM sqlite_schema
          WHERE (type='table' OR type='view')
            AND (name LIKE '%first_cash%' OR name LIKE '%paid_in_full%'
                 OR name LIKE '%customer_cac%')
        ) THEN 0 ELSE 1 END,
       'This model stops at Qualified and does not collapse first-cash or paid-in-full CAC';

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

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

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

COMMIT;

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

PRAGMA foreign_key_check;
PRAGMA integrity_check;

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

SELECT count(*) AS assertion_count,
       sum(CASE WHEN status='PASS' THEN 1 ELSE 0 END) AS assertion_pass_count,
       sum(CASE WHEN status='FAIL' THEN 1 ELSE 0 END) AS assertion_fail_count
FROM v_assertion_report;

SELECT send_request_id,derived_gate_state,blocking_findings
FROM v_send_gate
ORDER BY send_request_id;

SELECT * FROM v_account_funnel ORDER BY batch_id;
SELECT * FROM v_contact_funnel ORDER BY batch_id;
SELECT * FROM v_attempt_funnel ORDER BY batch_id;

SELECT batch_id,account_id,first_touch_source_id,first_touch_source_code,
       assist_touch_ids,assist_source_codes
FROM v_account_attribution
WHERE first_touch_source_id IS NOT NULL OR assist_touch_ids IS NOT NULL
ORDER BY batch_id,account_id;

SELECT event_domain,late_or_restatement,count(*) AS event_count
FROM v_post_cutoff_event_inventory
GROUP BY event_domain,late_or_restatement
ORDER BY event_domain,late_or_restatement;

SELECT qualification_package_id,batch_id,account_id,opportunity_id,
       represented_requirement_count,satisfied_requirement_count,
       stop_requirement_count,unknown_requirement_count,derived_handoff_state
FROM v_handoff_readiness
ORDER BY qualification_package_id;

SELECT batch_id,base_currency,cash_cost_micros,founder_minutes,
       founder_time_cost_micros,fully_loaded_cost_micros,qualified_accounts,
       cash_cost_per_qualified_micros,founder_time_cost_per_qualified_micros,
       fully_loaded_cost_per_qualified_micros,cost_per_qualified_state
FROM v_batch_cost_per_qualified
ORDER BY batch_id;
