P3D — Birth System B3-A3 Trigger Install Design
P3D — Birth System B3-A3 Trigger Install Design
Date: 2026-05-12 Mode: DESIGN ONLY
1. Goal
Install missing birth INSERT triggers for live clean candidates. Only fn_birth_registry_auto used for new installs.
2. Live clean candidate rule (ALL must be true)
□ coverage_status = 'BIRTH_REQUIRED' (live from collection_registry)
□ coverage_scope_status = 'IN_SCOPE' (live)
□ Has physical BASE TABLE in resolved schema (information_schema.tables)
□ Has species_collection_map row (live)
□ NOT already wired to accepted birth function by function OID
□ NOT policy-exempt (coverage_status not EXEMPT_*)
□ NOT birth_registry (exempt per B3-A2)
□ NOT using scoped sibling already (governance_relations, law_dot_enforcement, law_jurisdiction)
□ No existing trigger name collision (trg_birth_<collection> absent in pg_trigger)
Candidate list re-computed live every execution. Evidence snapshot (9 collections) is reference only.
3. Accepted birth functions
| Function | Scope | OID source |
|---|---|---|
| fn_birth_registry_auto | Global — used for ALL new B3-A3 installs | SELECT oid FROM pg_proc WHERE proname='fn_birth_registry_auto' |
| fn_birth_registry_auto_id | Scoped sibling — accepted ONLY for governance_relations, law_dot_enforcement, law_jurisdiction | Not used for new installs |
B3-A3 creates triggers binding to fn_birth_registry_auto only. Scoped sibling collections are excluded from install (already wired).
4. Trigger naming
Convention: trg_birth_<collection_name>
Rationale: matches DOT convention, matches existing variant trigger names (trg_birth_governance_relations etc.), matches readiness rerun probe expectations. NOT birth_trigger_<collection> (legacy Directus pattern — those are the duplicates in B3-A-DUP).
5. Duplicate trigger handling
B3-A3 does NOT clean up 18 duplicate pairs. That's B3-A-DUP.
B3-A3 MUST check: before CREATE TRIGGER, verify no existing trigger on this collection binds to fn_birth_registry_auto OID. If one exists → ALREADY_WIRED_OK, skip. This prevents accidentally creating a duplicate.
6. Smoke verification
No test INSERTs in B3-A3. Verification = catalog wiring only:
- pg_trigger exists for collection
- tgfoid matches fn_birth_registry_auto OID
- tgtype bits confirm AFTER + ROW + INSERT
If smoke INSERT is ever needed → separate explicitly approved step.
7. Transaction safety (all required)
□ Advisory lock (pg_try_advisory_xact_lock)
□ Single transaction
□ Compiled SQL preview logged before execution
□ Rollback SQL logged before execution
□ Post-install in-transaction verification (per trigger)
□ Post-commit verification (total trigger count)
□ STOP on any mismatch
B3-A3 Trigger Install Design | 2026-05-12