06 — Backfill Auxiliary Engine (P4)

File: sql/04_backfill_engine.sql (sha256 9cd1a02c…28d0e5). Author-mode. Principle: label/ledger before write; never fabricate birth rows.

Components

• _backfill_birth_ledger (cols: collection_name, entity_code, first_detected, last_seen, decision, decided_by, birth_applied_at, notes) — PK (collection, entity_code); decision in {PENDING, BIRTH_APPROVED, EXEMPT, QUARANTINE, RETIRE}. One durable row per ever-seen unborn entity (idempotent).
• _backfill_cursor (collection_name, last_id_checked, last_run_at, rows_seen) — no-skip / no-infinite-rescan snapshot.
• fn_backfill_scan() — reads v_birth_orphan, upserts ledger rows (ON CONFLICT refreshes last_seen), updates cursor. No birth write. Returns per-collection newly_logged / total_pending.
• v_backfill_backlog — read-only coverage proof grouped by collection and decision.

Backlog ledger (live targets)

Collection	Unborn	Remediation
pivot_definitions	26	Per-row owner decision then birth via lawful DOT path (re-touch row so the AFTER-INSERT/refresh birth trigger fires; never hand-insert birth_registry). RP-paused, so this waits behind the gate.
dot_iu_command_catalog	54	ONBOARD FIRST — add to collection_registry + meta_catalog managed, set birth_code_column = command_name, then birth. Until onboarded it is EXEMPT-pending-onboarding, not BIRTH_APPROVED.
82 zero-birth managed collections	collection-grain	Triage: many are views/link tables legitimately exempt; classify each into EXEMPT vs needs-birth before any Stage-2 blocking.
filesystem DOTs	via P2	FILE_NO_REGISTRY rows then register via lawful DOT then birth.

Idempotency / safety guarantees

• Re-running fn_backfill_scan() never duplicates ledger rows (PK + ON CONFLICT).
• Engine performs no birth creation — only labels. Birth is an explicit, audited, per-decision owner step (template in file section 5, Steps A–C with post-write existence stamp).
• Respects the forbidden rule "no fake birth rows to hide gaps": counts only move when a real birth row appears (Step C verifies existence in birth_registry).

Rollback

Drop in reverse order: view v_backfill_backlog, function fn_backfill_scan, tables _backfill_cursor and _backfill_birth_ledger. Exact statements in sql/99_rollback_all.sql.