IU PG-native Trigger Model Survey + Design Report

Date: 2026-05-21 Mission: v0.6-iu-pg-native-trigger-model-survey-design Result: IU_PG_NATIVE_TRIGGER_MODEL_READY Production mutation: NONE DDL executed: NO DB write: NO

Progress

B0 Skill + foundation read
KB read: OR / Constitution / mission docs
G0 PRECHECK-LIVE
G1 Live schema survey
G2 Current capability map
G3 Design
G4 Roadmap
G5 KB upload packet prepared

3 cau Tuyen ngon

Vinh vien: IU phai tro thanh PG-native event center, lien ket voi SQL object bang registry/link metadata thay vi patch rieng tung workflow.
Nham duoc khong: moi trigger/link sau nay phai co registry, idempotency key, enable/disable flag, DOT verifier va read-only precheck truoc khi write.
100% tu dong: SQL event va IU event di qua queue/event log + worker/DOT; sau khi migration duoc duyet thi add/split/merge/delete khong dua vao thao tac tay.

0. Documents Read

KB via search_knowledge() main process, no background agent:

knowledge/dev/ssot/operating-rules.md - OR v7.58, 2026-05-01.
knowledge/dev/laws/constitution.md - Constitution current search hit v4.6.3.
knowledge/dev/laws/dieu44-trien-khai/reviews/dot-iu-cutter-v0.6-o8-blocked-live-write-gap-gpt-ruling-2026-05-21.md.
knowledge/dev/laws/dieu44-trien-khai/v0.5-post-enactment-closeout-release-readiness/*.
knowledge/dev/laws/dieu44-trien-khai/v0.5-lifecycle-ddl-ratification-runbook/*.
knowledge/dev/laws/dieu44-trien-khai/roadmaps/p3d-information-unit-text-as-code-roadmap-nom-na-2026-05-10.md.
knowledge/dev/laws/dieu44-trien-khai/design/04-information-unit-profile-schema.md.
Local workspace search for THIEU/THIEU/Mieng thong tin/IU/text-as-code found no extra local "THIEU" file; KB found the "mieng thong tin" roadmap above.

Mission-specific KB constraints retained:

O8 decisive gap: deployed v0.6 has no production-write code path; this mission does not alter that.
Post-enactment closeout: Constitution cut pipeline is complete for first document; no mutation in this macro.
Lifecycle DDL ratification: lifecycle SQL/function fingerprints are ratified; any new DDL must be a separate authoring/review/sandbox/execution macro.

G0 PRECHECK-LIVE

Read-only access confirmed. No production mutation performed.

Paste output:

$ ssh root@38.242.240.89 hostname
vmi3080463

$ ssh root@38.242.240.89 whoami
root

$ docker ps --format names/images/status
pg-restore-test-20260520T031054Z  postgres:16  Up 26 hours
postgres                         postgres:16  Up 4 weeks (healthy)
incomex-directus                 directus/directus:11.5  Up 12 days (healthy)
incomex-agent-data               agent-data-local:latest  Up 45 hours (healthy)
incomex-qdrant                   qdrant/qdrant:latest  Up 2 months (healthy)

$ BEGIN READ ONLY; SELECT current_database(), current_user, current_setting('transaction_read_only'), version(); ROLLBACK;
directus|directus|on|PostgreSQL 16.13 (Debian 16.13-1.pgdg13+1) ... 64-bit

KB read/upload path is available through MCP Agent Data. Upload is performed after this file is finalized.

G1 Live Schema Survey

Core IU tables

Paste output summary:

public.information_unit  approx_rows=158
public.unit_version      approx_rows=165
public.iu_lifecycle_log  approx_rows=60
public.iu_lifecycle_vocab approx_rows=4

information_unit columns:

id uuid PK default gen_random_uuid()
canonical_address text UNIQUE NOT NULL
unit_kind text NOT NULL
lifecycle_status text NOT NULL default draft
content_anchor_ref text NULL
version_anchor_ref uuid FK -> unit_version.id
owner_ref text NOT NULL
parent_or_container_ref uuid NULL
conformance_status text NOT NULL default open
identity_profile jsonb NOT NULL default {}
created_at/updated_at timestamptz NOT NULL
created_by/updated_by text NOT NULL
deleted_at timestamptz NULL
sort_order int NULL
doc_code/section_type/section_code text NULL

unit_version columns:

id uuid PK default gen_random_uuid()
unit_id uuid FK -> information_unit.id
body text NOT NULL
content_hash text NOT NULL
version_seq int NOT NULL
lifecycle_status text NOT NULL default draft
content_profile jsonb NOT NULL default {}
created_at timestamptz NOT NULL
created_by text NOT NULL
title/description/review_state/provenance/editor nullable
enacted_at/updated_at nullable
UNIQUE(unit_id, version_seq)

iu_lifecycle_log columns:

id uuid PK default gen_random_uuid()
unit_id uuid FK -> information_unit.id
canonical_address text NOT NULL
version_anchor_ref uuid NULL
from_status/to_status/transition_type text NOT NULL
reason text NULL
performed_by text NOT NULL
performed_at timestamptz NOT NULL default now()
review_decision_id uuid NULL
change_set_id uuid NULL
tool_revision text NULL
metadata jsonb NOT NULL default {}

Lifecycle and cut governance

Live rows:

information_unit lifecycle_status:
draft|98
enacted|60

unit_version lifecycle_status/review_state:
draft||105
enacted||60

iu_lifecycle_log:
draft|enacted|enact|60

cutter_governance:
manifest_envelope|2
manifest_unit_block|61
cut_change_set|2
cut_change_set_affected_row|61
review_decision|3
verify_result|2
dot_pair_signature|4
decision_backlog_entry|2

cut affected rows:
public.information_unit|insert|60
cutter_governance/none|apply|1

cutter_governance schema exists and is readable. Important tables/views found:

manifest_envelope, manifest_unit_block
review_decision, verify_result
cut_change_set, cut_change_set_affected_row
dot_pair_signature
decision_backlog_entry/history/dependency/sweep_log
canonical_address_alias
observe views for governance rows

dot_config and graph substrate

Live:

public.dot_config approx_rows=73
columns: key text PK, value text, description text, updated_at timestamptz

public.universal_edges approx_rows=2199
columns include source_collection/source_id/source_code, target_collection/target_id/target_code,
edge_type/edge_subtype, metadata jsonb, status, valid_from/to, confidence, provenance.

Current universal_edges limitation for IU:

SELECT source_collection,target_collection,edge_type,count(*)
WHERE source/target IN ('information_unit','unit_version') ...
=> 0 rows

Also universal_edges.source_id and target_id are integer, while information_unit.id and unit_version.id are uuid. Therefore it cannot directly represent IU<->SQL row links without either UUID support, metadata indirection, or a dedicated link table.

Trigger/function survey

IU functions found:

fn_iu_create, fn_iu_edit, fn_iu_save, fn_iu_enact, fn_iu_verify_invariants,
fn_iu_gateway_write_guard, fn_iu_enacted_immut, fn_uv_enacted_immut,
fn_iu_notif_version, fn_iu_notif_draft, fn_iu_notif_comment, fn_iu_mark_read

Selected fingerprints:

fn_iu_create md5=3017892a5ac605a6daeaa5348e2a6cdf len=5443
fn_iu_enact  md5=6ca9bc39e2d2be93dd8a71739fa80dc4 len=8674
fn_iu_gateway_write_guard md5=6907fa4e5e46b5617d7dfecbd86326d7 len=1364
fn_iu_verify_invariants md5=6d005323d15eb4802f22802470b2c966 len=2746

Triggers on IU:

information_unit:
trg_aa_iu_gateway_write_guard BEFORE INSERT OR UPDATE -> fn_iu_gateway_write_guard()
trg_birth_information_unit AFTER INSERT -> fn_birth_registry_auto('__birth_synthetic_id__')
trg_iu_birth_gate_layer1 BEFORE INSERT -> fn_iu_birth_gate_layer1()
trg_iu_birth_gate_layer2 CONSTRAINT AFTER INSERT OR UPDATE DEFERRABLE -> fn_iu_birth_gate_layer2()
trg_iu_enacted_immut BEFORE DELETE OR UPDATE -> fn_iu_enacted_immut()
trg_iu_updated_at BEFORE UPDATE -> fn_iu_updated_at()

unit_version:
trg_aa_iu_notif_version AFTER INSERT -> fn_iu_notif_version()
trg_aa_uv_gateway_write_guard BEFORE INSERT OR UPDATE -> fn_iu_gateway_write_guard()
trg_uv_enacted_immut BEFORE DELETE OR UPDATE -> fn_uv_enacted_immut()

Database-level trigger guard exists:

evt_trigger_guard_ddl  ddl_command_end  enabled  {CREATE TRIGGER,ALTER TABLE} -> fn_evt_trigger_guard
evt_trigger_guard_drop sql_drop         enabled  {DROP TRIGGER} -> fn_evt_trigger_guard_drop

trigger_guard_alerts count=119
trigger_guard_exceptions count=27

Existing event substrate

Tables found:

event_outbox approx_rows=90621
event_pending approx_rows=0
event_read approx_rows=90591
event_subscription approx_rows=3
event_type_registry approx_rows=4
iu_notification_event approx_rows=0
iu_notification_read approx_rows=0
unit_edit_draft approx_rows=13
unit_edit_comment approx_rows=14

event_outbox columns include:

event_domain, event_type, event_stream, delivery_lane, event_subject_table,
event_subject_ref, canonical_address, actor_ref, source_system, correlation_id,
payload_classification, safe_payload, occurred_at, created_at

Idempotency already exists for immediate outbox:

CREATE UNIQUE INDEX idx_event_outbox_idempotent
ON public.event_outbox(event_domain,event_type,event_subject_table,event_subject_ref)
WHERE delivery_lane='immediate'

Current registered event types are only:

system|issue_opened|alert|immediate
system|issue_resolved|update|immediate
system|issue_archived|update|immediate
system|red_zone_violation|alert|delayed|inactive

Current IU notification is separate from event_outbox:

unit_edit_draft AFTER INSERT -> iu_notification_event('draft_created')
unit_edit_comment AFTER INSERT -> iu_notification_event('comment_added')
unit_version AFTER INSERT -> iu_notification_event('version_applied')

No IU rows currently appear in event_outbox.

G2 Current Capability Map

Present

IU identity: information_unit.id, canonical_address, identity_profile.
IU versioning: unit_version, version_seq, content_hash, version_anchor_ref.
IU lifecycle: lifecycle_status, iu_lifecycle_log, fn_iu_enact, immutability triggers.
IU parent placeholder: parent_or_container_ref exists but live count is 0.
Cut governance: manifest, review, verify, change-set, affected-row log exists under cutter_governance.
Generic graph: universal_edges exists and is populated for other domains.
Generic event substrate: event_outbox/event_pending/event_type_registry/event_subscription/event_read exists.
IU local notification: iu_notification_event/read exists for draft/comment/version events.
Trigger guard: database event triggers detect trigger DDL/drop.

Missing for IU <-> SQL object mapping

No dedicated iu_sql_link or equivalent table.
No typed representation for SQL object kinds: schema, table/collection, row, function, trigger, view, Directus flow, vector/report.
No canonical row identity model for arbitrary PG row PKs, especially uuid/text/composite keys.
universal_edges cannot directly link IU UUID rows because it uses integer source/target IDs.
No link from information_unit to table_registry, collection_registry, trigger_registry, pg_proc, or pg_trigger.
No route registry saying which SQL table/function/trigger emits which IU event.
No inbound SQL-event -> IU/workflow trigger model.
No outbound IU-event -> SQL/workflow/vector/report route model.
No active IU event types in event_type_registry.
No idempotency key on event_pending; outbox idempotency exists but is subject-level only.

G3 Design

Model 1: `iu_sql_link`

Purpose: authoritative PG-native bridge between IU and SQL/Directus objects.

Minimum columns for next DDL authoring:

id uuid PK default gen_random_uuid()
unit_id uuid NOT NULL FK -> information_unit(id)
unit_version_id uuid NULL FK -> unit_version(id)
link_role text NOT NULL
object_kind text NOT NULL
object_schema text NULL
object_name text NOT NULL
collection_name text NULL
row_pk jsonb NULL
function_identity text NULL
trigger_name text NULL
object_fingerprint text NULL
direction text NOT NULL default 'bidirectional'
lifecycle_status text NOT NULL default 'active'
enabled boolean NOT NULL default true
idempotency_key text NOT NULL UNIQUE
metadata jsonb NOT NULL default {}
created_at/updated_at timestamptz NOT NULL
created_by/updated_by text NOT NULL

Allowed object_kind:

schema, table, collection, row, function, trigger, view, directus_flow, vector_collection, report, external_endpoint

Allowed link_role:

represents, describes, governs, source_of_truth_for, derived_from, emits_event_for,
consumes_event_from, validates, renders, indexes, reports_on

Validation rule:

table/collection/row must resolve to pg_class or Directus directus_collections.
function must resolve to pg_proc by schema + function_identity.
trigger must resolve to pg_trigger and optionally trigger_registry.
row must carry row_pk as JSONB, e.g. {"id":"...","pk_type":"uuid"} or composite key object.
No FK to arbitrary target rows; validation belongs in DOT precheck and optional PG helper function.

Model 2: inbound event model - SQL event -> IU/workflow

Use trigger pair:

Primary realtime trigger on source SQL table/function event.
Safety-net DOT scan/cron that reconciles source table changes with iu_sql_link and event logs.

Route metadata table proposed: iu_sql_event_route.

id uuid PK
route_code text UNIQUE NOT NULL
source_object_kind text NOT NULL
source_schema text NULL
source_table text NULL
source_function_identity text NULL
source_trigger_name text NULL
sql_event text NOT NULL               -- insert/update/delete/call/ddl
target_event_domain text NOT NULL      -- iu_sql
target_event_type text NOT NULL        -- sql_row_changed/sql_function_called/sql_trigger_changed
target_event_stream text NOT NULL      -- inbound/workflow/review
link_role_filter text NULL
enabled boolean NOT NULL default false
dry_run boolean NOT NULL default true
idempotency_policy jsonb NOT NULL
payload_contract jsonb NOT NULL
created_at/updated_at timestamptz NOT NULL

Inbound flow:

SQL row/function/trigger event
-> PG trigger builds stable event_subject_ref and idempotency key
-> resolve active iu_sql_link
-> insert into event_pending or event_outbox with event_domain='iu_sql'
-> worker/DOT consumes event
-> optional workflow/change-set/draft/review action
-> verify scanner checks no missing events

Do not call external HTTP from PG trigger. PG trigger only writes metadata event row.

Model 3: outbound event model - IU event -> SQL/workflow/vector/report

Outbound should reuse event_outbox, with new event_type_registry rows for IU:

iu|unit_created|workflow|immediate
iu|unit_enacted|workflow|immediate
iu|version_applied|vector|immediate
iu|draft_created|review|immediate
iu|comment_added|review|immediate
iu|sql_link_changed|workflow|immediate
iu|unit_split_requested|workflow|immediate
iu|unit_merge_requested|workflow|immediate
iu|unit_retired|workflow|immediate

Outbound route metadata table proposed: iu_outbound_route.

id uuid PK
route_code text UNIQUE NOT NULL
event_domain text NOT NULL
event_type text NOT NULL
event_stream text NOT NULL
target_kind text NOT NULL              -- sql_function, workflow, vector, report, directus_flow
target_ref text NOT NULL
enabled boolean NOT NULL default false
dry_run boolean NOT NULL default true
idempotency_policy jsonb NOT NULL
retry_policy jsonb NOT NULL
payload_contract jsonb NOT NULL
created_at/updated_at timestamptz NOT NULL

Outbound flow:

fn_iu_create / fn_iu_enact / unit_version insert / link change
-> event_outbox row
-> route worker/DOT reads iu_outbound_route
-> execute approved target: SQL function/workflow/vector/report
-> write delivery result and lifecycle/report evidence

Bridge plan: keep iu_notification_event for UI/read board short term; add a bridge trigger/function that mirrors IU notification events into event_outbox only after event registry rows and idempotency are ratified.

Metadata contract for IU as event center

Minimum metadata split:

information_unit.identity_profile:

{
  "doc": {"doc_code": "...", "canonical_address": "..."},
  "ownership": {"owner_ref": "..."},
  "sql_links": {"primary_link_id": "...", "link_count": 0},
  "event_center": {"enabled": true, "inbound_enabled": false, "outbound_enabled": false}
}

unit_version.content_profile:

{
  "content": {"hash": "...", "word_count": 0, "source_span": {}},
  "vector": {"sync_status": "pending", "chunk_count": 0},
  "events": {"emits": ["version_applied"], "requires_reindex": true}
}

iu_sql_link.metadata:

{
  "validation": {"last_checked_at": null, "last_result": "unknown"},
  "dependency": {"on_split": "copy|move|block", "on_merge": "merge|block", "on_delete": "retire|block"},
  "trigger": {"inbound_route_codes": [], "outbound_route_codes": []},
  "rollback": {"disable_first": true, "last_disable_reason": null}
}

Idempotency

Required key formats:

iu_sql_link:
iu_sql_link:{unit_id}:{object_kind}:{object_schema}:{object_name}:{row_pk_hash}:{link_role}

inbound SQL event:
iu_sql_in:{route_code}:{source_schema}.{source_table}:{pk_hash}:{op}:{txid_or_content_hash}

outbound IU event:
iu_out:{event_domain}:{event_type}:{unit_id}:{unit_version_id_or_ref}:{content_hash_or_lifecycle_seq}

Add unique idempotency index where missing. event_outbox already has a partial idempotency-like unique index for immediate events; event_pending needs either idempotency_key or deterministic subject fields before controlled triggers are enabled.

Rollback / disable

Every new route/trigger must support:

enabled=false route stop without dropping trigger.
dry_run=true mode that records candidate events but performs no downstream action.
valid_from/valid_until optional validity window for iu_sql_link.
trigger guard exception only by explicit, expiring approval.
rollback through governance change-set, not ad hoc DDL.
DOT verify comparing source SQL change count vs event rows.

G4 Roadmap

DDL authoring macro:
- Write DDL only, no execution.
- Add iu_sql_link, iu_sql_event_route, iu_outbound_route.
- Add event registry rows as DML candidate only through DOT/governance.
- Add helper validation functions as authored SQL with fingerprints.
Sandbox macro:
- Apply DDL to restore-test/sandbox only.
- Seed one IU link to a harmless table row.
- Verify no Directus/Nuxt changes required.
First controlled trigger macro:
- Enable one inbound route in dry-run.
- Create PG trigger that only inserts metadata event rows.
- Add safety-net DOT scanner.
- Paste real SQL counts: source mutations vs pending/outbox rows.
Outbound bridge macro:
- Register IU event types in event_type_registry.
- Bridge iu_notification_event -> event_outbox in dry-run/sandbox first.
- Add vector/report route disabled by default.
Add/split/merge/delete dependency:
- Add operation policy to iu_sql_link.metadata.dependency.
- add: create link after IU create.
- split: copy/move links according to policy; block if ambiguous.
- merge: require conflict resolver for links with same object/role.
- delete: no physical delete; retire IU/link and preserve dependency history.

Compatibility With Current Cut/Lifecycle Pipeline

Compatible if the next macros follow these boundaries:

Do not alter existing fn_iu_create, fn_iu_enact, lifecycle DDL, or v0.6 orchestrator in this survey/design macro.
Keep current information_unit and unit_version columns as-is; add sidecar link/route tables first.
Reuse cutter_governance.cut_change_set and cut_change_set_affected_row for all approved link/route mutations.
Keep iu_lifecycle_log as lifecycle history; do not overload it with SQL event delivery status.
Use event_outbox/event_pending for event movement after event type registry is expanded.
Preserve trigger guard; new trigger DDL must be authored, reviewed, sandboxed, and verified before live execution.

DDL Required Later, Not Executed Here

Author in a separate macro:

CREATE TABLE public.iu_sql_link (...)
CREATE TABLE public.iu_sql_event_route (...)
CREATE TABLE public.iu_outbound_route (...)
Optional: add idempotency_key text + unique partial index to event_pending, or create iu_event_inbox if changing event_pending is deemed too broad.
Optional: add view v_iu_sql_link_resolved joining iu_sql_link to catalog tables.
Optional: add validation helper fn_iu_sql_link_validate(link_id uuid).
Optional: add outbox bridge helper fn_iu_emit_event(...) so IU functions/triggers do not duplicate event insert logic.

No ALTER TABLE, CREATE TABLE, INSERT, UPDATE, DELETE, trigger creation, flow mutation, or Directus mutation was executed in this mission.

Verdict

IU_PG_NATIVE_TRIGGER_MODEL_READY

Exact reason: live read-only survey was sufficient, no schema contradiction was found, and the missing layer can be added as sidecar metadata/route/event tables compatible with current IU lifecycle and cutter governance.

OR / TD / Handoff

OR update: not required. This is a survey/design report, no new operating law enacted.
TD update: roadmap items are captured in G4 above; no production task row was written.
Handoff: next macro should be DDL authoring only, followed by sandbox, then first controlled trigger. Do not self-advance to live DDL.