02 — Observation Substrate Apply Report (Workstream A)

Decision: amend the staged DDL before applying

Staged 02_observation_substrate.sql met most of §4 (process_run_id / correlation_id / component_run_id / status / input/output refs / evidence_ref / idempotency_key + partial unique index) but was missing the typed evidence_type dimension. It relied on source_system='dry_run' + evidence_ref.dry_run=true (jsonb). That is too weak to drive scoring safely — a view computing "is this verified?" by parsing jsonb is brittle and easy to fake.

Amendment (/tmp/02b_observation_substrate_v2.sql, sha256 aa4226c0a6b8817c12a1958e3bdc6d62072dbf54a87c7f6a0fdd3492d1ad7123):

• Added evidence_type text NOT NULL DEFAULT 'SIMULATED_DRY_RUN' CHECK (evidence_type IN ('REAL_RUN','DRY_RUN','SIMULATED_DRY_RUN','BACKFILLED_EVIDENCE')) to both tables.
• Safe-by-default: the DEFAULT is the least-privileged value, so any row that forgets to set evidence_type can NEVER reach verified_candidate.
• Added idx_pro_etype / idx_pco_etype indexes on evidence_type.
• Everything else byte-identical to the staged DDL (same PK, FK, status CHECK, idempotency unique indexes).

Schema delivered (§4 checklist)

Requirement	Column	Status
evidence_type REAL/DRY/SIMULATED/BACKFILLED	evidence_type + CHECK	✅ added
source_system	source_system NOT NULL	✅
process_run_id / correlation_id / component_run_id	PK + FK + component_run_id	✅
status	status + CHECK (9 values)	✅
input/output refs	input_ref / output_ref	✅
evidence_ref	evidence_ref jsonb	✅
idempotency_key	idempotency_key + partial UNIQUE	✅
prevent duplicate by idempotency_key	uq_pro_idem / uq_pco_idem WHERE NOT NULL	✅

Rehearsal (BEGIN..ROLLBACK)

• BEFORE: births 1,158,162 · trigger_guard_alerts 129 · tables absent.
• INSIDE TXN: 2 tables + 12 indexes created; births 1,158,162 (unchanged); guard_alerts 129 (unchanged) → no DDL-guard side effect.
• Insert test inside txn: INSERT 0 1 OK.
• CHECK test: inserting evidence_type='NOT_A_TYPE' → ERROR ... violates check constraint "process_run_observation_evidence_type_check". Constraint enforced.
• AFTER ROLLBACK: tables gone; births 1,158,162; guard_alerts 129. Clean net-zero.

Commit (live)

Applied 02b_observation_substrate_v2.sql in autocommit: 2× CREATE TABLE + 12× CREATE INDEX. Post-commit independent verify (container psql + MCP query_pg):

• process_run_observation (16 cols) + process_component_observation (19 cols) present.
• births 1,158,162 · guard_alerts 129 — no births, no guard side effect.

Result: OBSERVATION SUBSTRATE LIVE. No blocker.