IU PG-native DDL Authoring Brief

Date: 2026-05-21 Macro: v0.6-iu-pg-native-ddl-authoring-brief Result: IU_PG_NATIVE_DDL_BRIEF_READY Scope: authoring brief + acceptance checklist only Production mutation: NONE DDL executed: NO DB write: NO

G0 Context Check

KB read/upload works. Read before authoring:

• GPT ruling: knowledge/dev/laws/dieu44-trien-khai/reviews/dot-iu-cutter-v0.6-iu-pg-native-trigger-model-ready-gpt-ruling-2026-05-21.md
• Prior report: knowledge/dev/laws/dieu44-trien-khai/v0.6-iu-pg-native-trigger-model-survey-design/iu-pg-native-trigger-model-survey-design-report-2026-05-21.md
• OR/Constitution context: OR v7.58 and Constitution v4.6.3 search hits via Agent Data.

This document is a brief for the next DDL package. It does not contain execution approval and must not be treated as runnable production DDL.

3 Cau Tuyen Ngon

1. Vinh vien: add a stable IU-to-SQL substrate, not per-workflow patches.
2. Nham duoc khong: DDL must encode object kinds, idempotency, enable/disable, lifecycle, and route dry-run so wrong triggers fail closed.
3. 100% tu dong: after later approved DDL + trigger macros, SQL/IU events flow through metadata routes, queue/outbox, and DOT verifiers without manual handoff.

Live Schema Foundation From Prior Report

Use these facts as authoring assumptions; re-check read-only immediately before any future execution:

• public.information_unit: 158 rows; UUID id; unique canonical_address; identity_profile jsonb; version_anchor_ref uuid; parent_or_container_ref uuid; lifecycle fields.
• public.unit_version: 165 rows; UUID id; unit_id uuid; content_hash; version_seq; unique (unit_id, version_seq); content_profile jsonb.
• public.iu_lifecycle_log: 60 rows; FK to IU; lifecycle transition metadata.
• cutter_governance: manifest/review/verify/change-set/affected-row/signature tables exist.
• public.dot_config: 73 rows; simple key/value config.
• public.universal_edges: 2199 rows; generic graph exists, but source_id/target_id are integer and cannot directly address IU UUID rows.
• Trigger guard exists: database event triggers for trigger DDL/drop and guard alert/exception tables.
• Event substrate exists: event_outbox, event_pending, event_read, event_subscription, event_type_registry.
• IU notification substrate exists: iu_notification_event/read, plus triggers from unit_edit_draft, unit_edit_comment, unit_version.
• Missing today: no iu_sql_link, no route registry, no IU event types in event_type_registry, no direct IU rows in event_outbox.

G1 Object Brief

1. public.iu_sql_link

Purpose: authoritative sidecar link between IU/version and SQL/Directus objects.

Minimum columns:

id uuid PK default gen_random_uuid()
unit_id uuid NOT NULL FK -> public.information_unit(id)
unit_version_id uuid NULL FK -> public.unit_version(id)
canonical_address text NOT NULL
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
lifecycle_status text NOT NULL default 'active'
enabled boolean NOT NULL default false
idempotency_key text NOT NULL
metadata jsonb NOT NULL default '{}'
created_at timestamptz NOT NULL default now()
updated_at timestamptz NOT NULL default now()
created_by text NOT NULL
updated_by text NOT NULL

Minimum checks/indexes:

• PK on id.
• FK unit_id -> information_unit(id).
• FK unit_version_id -> unit_version(id).
• UNIQUE idempotency_key.
• INDEX (unit_id, enabled, lifecycle_status).
• INDEX (object_kind, object_schema, object_name).
• GIN index on row_pk only if row-level lookup is required in first package; otherwise defer.
• CHECK object_kind IN ('schema','table','collection','row','function','trigger','view','directus_flow','vector_collection','report','external_endpoint').
• CHECK direction IN ('inbound','outbound','bidirectional').
• CHECK lifecycle_status IN ('draft','active','disabled','retired').
• CHECK row/object shape:
  • row requires row_pk.
  • function requires object_schema, object_name, function_identity.
  • trigger requires object_schema, object_name, trigger_name.

2. public.iu_sql_event_route

Purpose: inbound route registry for SQL event -> IU/workflow.

Minimum columns:

id uuid PK default gen_random_uuid()
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
target_event_domain text NOT NULL default 'iu_sql'
target_event_type text NOT NULL
target_event_stream text NOT NULL
link_role_filter text NULL
enabled boolean NOT NULL default false
dry_run boolean NOT NULL default true
fail_closed boolean NOT NULL default true
idempotency_policy jsonb NOT NULL default '{}'
payload_contract jsonb NOT NULL default '{}'
metadata jsonb NOT NULL default '{}'
created_at timestamptz NOT NULL default now()
updated_at timestamptz NOT NULL default now()
created_by text NOT NULL
updated_by text NOT NULL

Minimum checks/indexes:

• UNIQUE route_code.
• INDEX (enabled, dry_run, source_object_kind).
• INDEX (source_schema, source_table, sql_event) where source_table IS NOT NULL.
• CHECK source_object_kind IN ('table','row','function','trigger','view').
• CHECK sql_event IN ('insert','update','delete','call','ddl_create','ddl_alter','ddl_drop').
• CHECK target_event_domain IN ('iu','iu_sql') unless GPT/User approves broader domains.
• CHECK enabled=false OR dry_run=true for first DDL package if execution is not authorized.

3. public.iu_outbound_route

Purpose: outbound route registry for IU lifecycle/version/link events -> SQL/workflow/vector/report.

Minimum columns:

id uuid PK default gen_random_uuid()
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
target_ref text NOT NULL
enabled boolean NOT NULL default false
dry_run boolean NOT NULL default true
fail_closed boolean NOT NULL default true
idempotency_policy jsonb NOT NULL default '{}'
retry_policy jsonb NOT NULL default '{}'
payload_contract jsonb NOT NULL default '{}'
metadata jsonb NOT NULL default '{}'
created_at timestamptz NOT NULL default now()
updated_at timestamptz NOT NULL default now()
created_by text NOT NULL
updated_by text NOT NULL

Minimum checks/indexes:

• UNIQUE route_code.
• INDEX (event_domain, event_type, event_stream, enabled).
• INDEX (target_kind, target_ref).
• CHECK target_kind IN ('sql_function','workflow','vector','report','directus_flow','external_endpoint').
• CHECK event_domain IN ('iu','iu_sql') for first package.
• CHECK enabled=false OR dry_run=true for first DDL package if execution is not authorized.

4. Optional Attempt/Delivery Table

Prefer using existing event_outbox/event_pending first. Add a route attempt table only if DDL authoring needs auditable delivery attempts without changing current event tables.

Candidate: public.iu_route_attempt

id uuid PK default gen_random_uuid()
route_code text NOT NULL
route_kind text NOT NULL              -- inbound|outbound
event_ref uuid NULL
idempotency_key text NOT NULL
attempt_no int NOT NULL default 1
status text NOT NULL default 'pending'
error_code text NULL
error_detail text NULL
payload_snapshot jsonb NOT NULL default '{}'
started_at timestamptz NOT NULL default now()
finished_at timestamptz NULL

Indexes/checks:

• UNIQUE (idempotency_key, attempt_no).
• INDEX (route_code, status, started_at DESC).
• CHECK route_kind IN ('inbound','outbound').
• CHECK status IN ('pending','dry_run','sent','skipped','failed','disabled').

Open point: if GPT/User approves adding idempotency_key to event_pending, iu_route_attempt can be deferred.

G2 Metadata Contract

Required contract across objects:

IU id: information_unit.id
canonical address: information_unit.canonical_address, copied into iu_sql_link.canonical_address for stable query/readability
SQL object kind: schema/table/row/function/trigger/view/collection
SQL object identity: object_schema + object_name + row_pk/function_identity/trigger_name
direction: inbound/outbound/bidirectional
event type: event_domain + event_type + event_stream
lifecycle coupling: link lifecycle_status + route enabled/dry_run/fail_closed
idempotency: deterministic idempotency_key on link and event route attempt
disable flag: enabled=false must stop route without dropping trigger or deleting metadata

Minimum 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}
}

Minimum route metadata:

{
  "owner": {"owner_ref": "DOT_OR_GPT_USER_DECISION"},
  "safety": {"requires_dry_run_first": true, "requires_dot_scan": true},
  "compat": {"event_outbox": true, "iu_notification_event_bridge": false}
}

G3 Trigger Route Model

Inbound: SQL event -> IU/workflow

Authoring target:

SQL table/function/trigger event
-> later PG trigger/function resolves active iu_sql_link
-> writes metadata-only event row to event_pending or event_outbox
-> worker/DOT reads route and emits workflow/review/draft action
-> safety-net DOT scan reconciles source change count vs event count

Connection point:

• First DDL package should create only route/link tables and optional helper definitions.
• Do not create live triggers in this authoring brief.
• Later first controlled trigger should attach to one harmless source table with route enabled=false then dry_run=true only after sandbox PASS.

Fail-closed rules:

• Missing route => no event, but DOT scanner reports missing route if link requires inbound.
• Disabled route => no downstream action; optional dry-run event may be logged only if explicitly designed.
• Missing/ambiguous iu_sql_link => event rejected to pending/attempt with status failed or skipped; no workflow mutation.
• Payload contract mismatch => no downstream action.
• Unknown event type not in event_type_registry => no insert to event_outbox; author must add registry candidate first.

Retry/queue behavior:

• PG trigger must not call HTTP or external services.
• Retry belongs to worker/DOT, not row trigger.
• Use event_pending for unresolved/pending inbound events or event_outbox for registered immediate events.
• Idempotency must make duplicate trigger firing harmless.

Outbound: IU lifecycle/version/change -> SQL/workflow/vector/report

Authoring target:

fn_iu_create / fn_iu_enact / unit_version insert / iu_sql_link change
-> emit IU event to event_outbox through helper or bridge
-> iu_outbound_route selects target
-> worker/DOT executes approved target kind
-> route attempt/evidence recorded

Connection point:

• Reuse existing IU triggers:
  • unit_version AFTER INSERT -> fn_iu_notif_version
  • unit_edit_draft AFTER INSERT -> fn_iu_notif_draft
  • unit_edit_comment AFTER INSERT -> fn_iu_notif_comment
• Later bridge can mirror iu_notification_event into event_outbox after IU event types are registered.
• Do not alter fn_iu_create, fn_iu_enact, fn_iu_gateway_write_guard, or immutability functions in the first DDL package.

Idempotency Keys

Use deterministic text keys:

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}:{content_hash_or_tx_marker}

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

route attempt:
iu_attempt:{route_kind}:{route_code}:{event_ref_or_subject}:{attempt_subject_hash}

Open constraint: event_outbox currently has a partial unique index on (event_domain,event_type,event_subject_table,event_subject_ref) for delivery_lane='immediate'. The DDL author should either fit that shape or propose an explicit idempotency_key addition in a separate reviewed migration.

Rollback / Disable Strategy

• Default all new route rows to enabled=false, dry_run=true, fail_closed=true.
• Disabling means route row update only in a later approved macro; no trigger drop required.
• Trigger rollback path is: disable route -> verify no new event movement -> only then consider trigger DDL rollback.
• Link retirement means lifecycle_status='retired', not delete.
• Preserve history through cutter_governance.cut_change_set and cut_change_set_affected_row in execution macros.
• Trigger guard exceptions must be explicit, temporary, and reviewed.

G4 Acceptance Checklist For Next DDL Package

Schema checks

• DDL package contains exactly the approved objects: iu_sql_link, iu_sql_event_route, iu_outbound_route; optional iu_route_attempt only if justified.
• No ALTER TABLE to current IU/lifecycle/event tables unless GPT/User explicitly approves.
• No trigger creation in first DDL authoring package unless explicitly scoped as sandbox-only.
• All tables use UUID PK and gen_random_uuid().
• All metadata columns default to '{}'::jsonb.
• All route tables default to enabled=false, dry_run=true, fail_closed=true.

FK/check/index checks

• iu_sql_link.unit_id FK to information_unit(id).
• iu_sql_link.unit_version_id FK to unit_version(id).
• idempotency_key unique on link.
• route_code unique on both route tables.
• CHECK constraints for object kinds, direction, lifecycle, sql_event, target_kind.
• Indexes support lookup by IU, object identity, route enabled/dry-run, event type.
• No cross-schema FK to catalog internals like pg_proc; use validation helper/view instead.

No production mutation / dry-run checks

• Package review confirms DDL authored only, not executed.
• Sandbox execution plan is separate.
• Production execution approval absent => STOP.
• No DB write, no Directus mutation, no Nuxt/current pipeline change in authoring macro.

Rollback/disable checks

• Routes can be disabled without dropping triggers.
• Link can be retired without deleting rows.
• Any future trigger has matching disable route and trigger guard plan.
• DOT verifier planned for missing/duplicate events.

Trigger off/on tests for later sandbox macro

• With route disabled: source event causes no downstream action.
• With route dry-run: source event records candidate only.
• With duplicate event: only one outbox/pending/attempt survives by idempotency.
• With ambiguous link: fail closed, no workflow mutation.
• With route enabled in sandbox only: worker consumes event and records evidence.

Event substrate compatibility

• IU event types are proposed for event_type_registry before outbox emission.
• event_outbox insert shape respects existing required columns.
• Existing outbox idempotency index is not violated accidentally.
• iu_notification_event remains intact; bridge is additive and can stay disabled.

Open Decisions For GPT/User

1. Should first DDL package add idempotency_key to event_pending, or create iu_route_attempt instead?
2. Should iu_sql_link.canonical_address be denormalized as required, or always joined from information_unit?
3. Should route tables live in public or cutter_governance? Brief defaults to public for Directus visibility and event substrate adjacency.
4. Should first controlled trigger target event_pending or event_outbox?
5. Should universal_edges remain untouched for IU UUID links, or should a later graph bridge be authored?
6. Should event registry seed rows be in the same DDL package as DML candidates, or separate DOT seed package?
7. What is the first harmless source table for sandbox controlled trigger?

Final Verdict

IU_PG_NATIVE_DDL_BRIEF_READY

This brief is sufficient for the next macro to write a DDL package without re-reading the full prior survey. It intentionally stops before SQL authoring/execution.

STOP / Route Back

Stop after KB upload and route to GPT/User. Next authorized macro should be DDL authoring only, followed by sandbox, then first controlled trigger.