03 — Row-Level Orphan / Phantom Detector (P1)

File: sql/01_detector_views.sql (sha256 9a664b50…564c6e). Author-mode, not applied. Pure read-only views; rollback = DROP VIEW (exact lines in file). Verified live: none of the three views exist yet.

Views

1. v_birth_orphan — entity ROW exists, no matching birth_registry row. UNION over the 5 critical families with their real identity columns (dot_tools.code, pivot_definitions.code, entity_species.code, collection_registry.code, dot_iu_command_catalog.command_name). Columns: collection_name, entity_code, label, active, orphan_reason (MISSING_CODE|NO_BIRTH_ROW), critical_family. Detects: missing birth row; wrong/missing entity_code; critical-family unborn. Expected counts: pivot 26 active, iu 54, dot_tools 0.
2. v_birth_phantom — birth_registry row exists but entity row gone. Columns add phantom_class (SYNTHETIC_TEST|REAL_MISSING) so chaos/codex seeds don't mask real loss. Expected: dot_tools 283 SYNTHETIC_TEST, 0 REAL_MISSING.
3. v_birth_coverage_status — per managed collection (+ injected critical families absent from meta_catalog): table_exists, birth_rows, has_birth, has_birth_trigger, has_birth_gate, in_meta_catalog, critical_family. Surfaces the 82 zero-birth managed collections and the "trigger-but-no-gate" families.

Detection requirements coverage (per macro P1 list)

Required	View	Met
row exists, no birth	v_birth_orphan	✅
birth exists, entity gone	v_birth_phantom	✅
collection has rows but no birth coverage	v_birth_coverage_status (has_birth=false)	✅
wrong/missing entity_code	v_birth_orphan (MISSING_CODE)	✅
duplicate birth mapping	live check showed 0 dup for dot_tools; add-on query in file comments; can extend with a v_birth_dup GROUP BY HAVING>1	◑ (0 today; helper noted)
entity-code mapping unresolvable	v_birth_orphan covers null/empty code	✅
critical-family missing coverage	v_birth_coverage_status + v_birth_orphan.critical_family	✅

Apply / rollback (operator)

ssh contabo "docker exec -i postgres psql -U workflow_admin -d directus" < sql/01_detector_views.sql
# verify:
SELECT collection_name, count(*) FROM v_birth_orphan GROUP BY 1 ORDER BY 1;
SELECT phantom_class, count(*) FROM v_birth_phantom GROUP BY 1;
# rollback: DROP VIEW v_birth_coverage_status, v_birth_phantom, v_birth_orphan;

These three views are safe to apply now — read-only, no base mutation, instant rollback — and are the minimum live signal the system is missing today.