S140 Phase 2B Report — FK to Edge Sync Triggers

Session: S140 | Date: 2026-03-19 | Status: COMPLETE

Assembly Gate

Q0: YES — PG triggers. Assembly First #1. Source-of-Truth: FK. Mirror: universal_edges. One-way sync only.

FK Survey

9 FK relationships across 7 source collections:

• checkpoint_instances.checkpoint_type_id -> checkpoint_types
• task_comments.task_id -> tasks
• task_comments.workflow_id -> workflows
• workflow_change_requests.task_id -> tasks
• workflow_change_requests.workflow_id -> workflows
• workflows.task_id -> tasks
• workflows.parent_workflow_id -> workflows (self-ref)
• workflow_steps.workflow_id -> workflows
• workflow_steps.checkpoint_set_id -> checkpoint_sets
• taxonomy.parent_id -> taxonomy (self-ref)

Functions + Triggers Created

fn_sync_fk_to_edge(source, source_id, source_code, fk_column, old_target, new_target, target, edge_type)

Core sync function. Handles INSERT (create edges), UPDATE (update target), DELETE (remove edges). Creates bidirectional edges: BELONGS_TO forward + CONTAINS reverse. Idempotent: ON CONFLICT DO UPDATE.

6 AFTER Triggers

• trg_after_sync_edges_checkpoint_instances
• trg_after_sync_edges_task_comments
• trg_after_sync_edges_wcr
• trg_after_sync_edges_workflows
• trg_after_sync_edges_workflow_steps
• trg_after_sync_edges_taxonomy

All registered in trigger_registry (TRG-EDGE-001 to 006). fn_sync_fk_to_edge registered in derived_objects_registry (DRV-004).

Test Results (BEGIN...ROLLBACK)

Test	Result
INSERT entity -> edges auto-created (FWD + REV)	PASS
UPDATE FK -> edge target updated	PASS
DELETE entity -> edges cleaned up (0 remaining)	PASS
Idempotency: backfill re-run -> 0 new inserts	PASS (450 before = 450 after)

Verification

#	Check	Result
1	FK survey complete	PASS (9 FK, 7 collections)
2	fn_sync_fk_to_edge test	PASS
3	6 triggers created	PASS
4	INSERT -> edge auto	PASS
5	UPDATE FK -> edge follows	PASS
6	DELETE -> edge cleanup	PASS
7	Idempotent backfill	PASS (450=450)
8	6 triggers in trigger_registry	PASS
9	DRV-004 in derived_objects	PASS
10	verify_counts 0 MISMATCH	PASS
11	Health Check 12/12	PASS
12	Smoke test 17/17	PASS