02 — Live PG Queue/Outbox/Event Inventory

Date: 2026-05-26 | Mode: read-only query_pg in directus database. Scope: Everything live that looks like queue / outbox / event / worker / lease / cleanup / retry / cursor.

§1. PostgreSQL version + extensions

version: PostgreSQL 16.13 (Debian 16.13-1.pgdg13+1) on x86_64-pc-linux-gnu, compiled by gcc 14.2.0
extensions:
  - btree_gist 1.7
  - pgcrypto   1.3
  - plpgsql    1.0
  - postgres_fdw 1.1

pg_cron: NOT installed. No scheduler extension. Workers are invoked externally (Hermes, Codex, Directus, or manual SQL call).

LISTEN/NOTIFY: confirmed used in one place — fn_kb_notify_vector_sync → channel kb_vector_sync consumed by pg_vector_listener.py daemon (per current-state/reports/pg-qdrant-sync-implementation). The universal event_outbox does not publish via NOTIFY today.

§2. Tables (universal event substrate)

Schema.Table	Rows	Purpose	Key columns
`public.event_outbox`	131,746	Durable event store (L3). Append-only.	id uuid, event_domain, event_type, event_stream, delivery_lane, event_severity, event_subject_table, event_subject_ref text, canonical_address, actor_ref, source_system, correlation_id, payload_classification, safe_payload jsonb, occurred_at, created_at
`public.event_pending`	0	Transient staging (L2 input). Retry surface (error_count, last_error).	id, event_domain, event_type_hint, entity_table, entity_ref, canonical_address, actor_ref, correlation_id, capture_payload jsonb, created_at, processed_at, error_count int, last_error
`public.event_read`	131,407	Per-actor read state. Implicit-self-read pattern.	event_id (FK), actor_ref, read_at, read_status_source default 'explicit'
`public.event_subscription`	3	Routing config (recipient × domain × type × stream).	recipient_ref, event_domain, event_type, event_stream, scope_subject_table, scope_filter jsonb, mute
`public.event_type_registry`	31	Whitelist of valid (domain, type, stream, lane).	event_domain, event_type, event_stream, delivery_lane, default_severity, description, active

2.1 CHECK constraints (event_outbox — the canonical contract)

event_domain ∈ {iu, birth_registry, governance, tac, kg, system, dot, health, piece}
event_stream ∈ {comment, review, update, birth, task, alert, health}
delivery_lane ∈ {immediate, delayed}
event_severity ∈ {info, warning, critical} OR NULL
payload_classification ∈ {safe_metadata, restricted}
safe_payload MUST NOT contain keys:
  body, content, raw, vector, embedding, secret, token, password, ssn, personal_data
actor_ref, canonical_address: btrim ≠ ''

2.2 Volume + cadence

event_outbox     min created_at: 2026-05-08 10:00  max: 2026-05-26 04:30  count: 131,746
event_read       count: 131,407 (~99.7% of events have a read row — mostly self-read)

Top event types by count:

system / issue_opened / alert / immediate              131,407   (system_issues open events)
iu / piece_added_to_collection / update / delayed          220
iu / structure_op_applied / update / immediate              44
iu / collection_created / update / delayed                  43
iu / piece_reordered / update / delayed                     17
iu / structure_piece_deprecated / update / immediate        12
iu / collection_rendered / update / delayed                  3

The volume is dominated by system/issue_opened alerts — this is the universal substrate proving itself on Điều 22 / system_issues, not on cutting workflow yet (only 7 cutting-related events in 18 days).

2.3 event_type_registry — 31 registered types

Domain	Types	Lanes
`iu`	16 (collection_, piece_, structure_*, template.instance_auto_composed, version_applied)	mix of immediate + delayed
`piece`	6 (created/updated/merged/split/superseded/retired)	all immediate
`staging`	5 (record_created/approved/consumed/rejected/cleaned — D36 carry-forward)	all delayed
`system`	4 (issue_opened/resolved/archived/red_zone_violation)	mix; red_zone_violation `active=false`

§3. Tables (worker / routing / dead-letter / lease)

Schema.Table	Rows	Purpose
`public.iu_route_worker_cursor`	1	Per (worker_name, event_domain) cursor: `last_created_at`, `last_event_id`, `last_run_at`, `events_seen`, `attempts_written`, `dead_lettered`, `last_run_summary jsonb`. Live cursor: `iu_outbound_default` on domain `iu`, last_run_at 2026-05-22 11:31, events_seen 68, attempts_written 67, dead_lettered 0.
`public.iu_route_dead_letter`	0	DLQ. Cols: worker_name, event_ref uuid, event_domain, event_type, event_stream, route_code, idempotency_key, failure_code, failure_detail, attempts ≥1, event_snapshot jsonb, first_failed_at, last_failed_at, resolved_at, resolution ∈ {replayed, discarded, superseded}, resolution_note. CHECK: `(resolved_at IS NULL) = (resolution IS NULL)`.
`public.iu_sql_event_route`	1	SQL-event-to-domain-event router (generic trigger-IN bridge). Cols: route_code, source_object_kind ∈ {table,row,function,trigger,view}, source_schema/table/function_identity/trigger_name, sql_event ∈ {insert,update,delete,call,ddl_create,ddl_alter,ddl_drop}, target_event_domain ∈ {iu,iu_sql}, target_event_type, target_event_stream, enabled, dry_run, fail_closed, idempotency_policy jsonb, payload_contract jsonb. Safety CHECK: `(enabled = false) OR (dry_run = true)` — currently dry-run only. Live row: `iu_sql.iu_sql_link.insert` (disabled, dry-run, fail-closed).
`public.dot_iu_runtime_lease`	0	Named-lease primitive replacing pg_try_advisory_lock. Cols: lease_name PK, lease_holder, lease_token uuid, acquired_at, expires_at, renewed_at. Pair fns `fn_dot_iu_runtime_lease_acquire/release`.

§4. Tables (lifecycle, staging, audit, ledger — adjacent)

Schema.Table	Rows (relevant)	Purpose
`iu_core.iu_staging_record`	(cutting workflow)	7-state lifecycle (pending/pending_review/approved/consumed/rejected/expired/cleaned); idempotency_key, content_hash, byte_len, expires_at, approved_at, approved_by, approval_doc_id, consumed_by_run_id, cleaned_at, vector_excluded.
`iu_core.iu_staging_payload`		Per-record payloads: part_index, part_name, payload_kind ∈ {json,text,blob_ref}, content_hash, byte_len.
`public.iu_lifecycle_log`		Append-only lifecycle transitions for information_unit (from_status, to_status, transition_type, reason, performed_by, performed_at, review_decision_id, change_set_id).
`public.iu_vector_sync_point`	152	Vector-sync state per (unit_id, parent_piece_id, chunk_index). No staging path (structural NVSZ guarantee).
`public.iu_auto_instantiate_event_log`	33	Idempotency log: (template_collection_id, instance_collection_id, event_id, idempotency_key, emit_mode, triggered_by). Proof the consumer (`fn_iu_auto_instantiate_from_event`) works.
`public.dot_iu_command_run`		Per-call run ledger: run_id, command_name, category, run_mode ∈ {plan,apply,verify}, run_status ∈ {planned,applied,verified,refused,failed}, mutating, params_digest, gate_snapshot jsonb, evidence jsonb, actor.
`public.dot_iu_command_catalog`		DOT command registry: command_name, category, mutating, reversible, target_functions[].
`public.entity_audit_queue`	1	Directus-managed audit queue (separate scope; uses {audit_status, audit_owner, audit_deadline, audit_reason_code, resolved_at}).
`public.iu_notification_event`	0	Legacy runtime per `23-P3D4C0X §I` — empty; superseded by event_outbox.
`public.iu_notification_read`	0	Legacy — empty.
`public.inbox`, `tasks`, `task_checkpoints`, `task_comments`, `os_tasks`, `os_task_files`, `os_deal_stages`, `ai_tasks`, `universal_rule_runs`, `universal_rule_run_results`	(Directus-managed)	Directus-side task/run tables — not part of the PG-native queue substrate, but useful to know about.

§5. Functions

5.1 Emitters

fn_iu_emit_event(...) — generic event emit into event_outbox.
fn_iu_emit_collection_event(...)
fn_iu_piece_emit_event(...)
fn_iu_lifecycle_log_emit_piece_event_trg() — TRIGGER function that emits piece events from lifecycle_log writes.

5.2 Worker / routing

fn_iu_route_worker_run(...) — main worker (events_seen / attempts_written / dead_lettered observed live).
fn_iu_route_worker_health(...) — observability probe.
fn_iu_route_worker_enabled() — reads dot_config.iu_core.route_worker_enabled. Live value: true.
fn_iu_route_deliver(...) — delivery primitive.
fn_iu_route_dead_letter_replay(...) — DLQ recovery.
fn_iu_outbound_route_delivery_guard(...) — delivery-side guard.

5.3 Consumers

fn_iu_auto_instantiate_from_event(...) — produces new instance collections from template events; idempotency-logged.
fn_iu_structure_consumer(...) — structure event consumer.

5.4 Lease

fn_dot_iu_runtime_lease_acquire(...), fn_dot_iu_runtime_lease_release(...)
fn_dot_iu_operator_runtime_enabled() — reads dot_config.iu_core.operator_runtime_enabled.

5.5 Staging lifecycle (cutting)

fn_iu_op_mark_file, fn_iu_op_verify_mark, fn_iu_op_cut, fn_iu_op_verify_cut, fn_iu_op_cleanup_dry_run — operator aliases.
fn_iu_mark_create_manifest, fn_iu_verify_mark, fn_iu_cut_from_manifest, fn_iu_verify_cut_result, fn_iu_staging_cleanup, fn_iu_staging_unregister.
iu_core.fn_iu_staging_healthcheck.

5.6 Retention / cleanup

fn_iu_core_retention_cleanup(...) — gated by dot_config.iu_core.retention_enabled (live: false).

5.7 Vector sync (independent pipeline)

fn_iu_vector_sync_record, fn_iu_vector_sync_record_v2, fn_iu_vector_sync_enabled.
fn_kb_notify_vector_sync — emits pg_notify('kb_vector_sync', …) consumed by external daemon.

§6. Views (already exposing event/queue state)

public.v_iu_event_backlog
public.v_iu_composer_event_backlog
public.v_iu_auto_instantiate_event_log
public.v_iu_auto_instantiate_event_summary
public.v_iu_route_dead_letter_open
public.v_iu_collection_manifest
public.v_iu_core_retention_candidates
public.v_dot_iu_command_registry
public.v_dot_iu_command_run_health
public.v_piece_event_outbox
iu_core.v_iu_staging_record
iu_core.v_iu_staging_payload_observability

§7. dot_config keys controlling the runtime

event.global.delivery_lane_default        = 'immediate'
event.system.poc_status                   = 'enabled'
iu_core.composer_enabled                  = 'false'
iu_core.operator_runtime_enabled          = 'false'
iu_core.retention_enabled                 = 'false'
iu_core.route_worker_enabled              = 'true'   ← worker live
piece_event_runtime.dry_run_only          = 'true'
piece_event_runtime.emit_enabled          = 'false'
hc_executor_max_runtime_seconds           = '600'

The runtime is in a "production-cautious" posture: worker is on, composer/operator/piece-runtime/retention are gated off pending explicit operator action.

§8. Triggers

Querying information_schema.triggers for any trigger named like *event*|*emit*|*notif*|*route*|*outbox* or on event/queue/notif/route/lease/audit tables returned 0 rows.

Interpretation: emission happens inside the domain mutator functions (e.g. fn_iu_collection_create calls fn_iu_emit_collection_event internally), not via AFTER triggers on the source tables. Exception: fn_iu_lifecycle_log_emit_piece_event_trg exists as a TRIGGER function and likely attaches to iu_lifecycle_log — not verified by name here because the trigger inventory came back empty under the pattern filter (the trigger may be named differently). This is one of the open questions to confirm in the design phase.

§9. What is missing from a "system-wide queue" perspective

Capability	Present?	Notes
Durable append-only event store	✅ `event_outbox`	131k rows, fully constrained
Transient staging with retry counter	✅ `event_pending`	empty — worker not building up backlog
Dead-letter queue	✅ `iu_route_dead_letter`	empty
Worker cursor / observability	✅ `iu_route_worker_cursor`	1 worker tracked
Per-actor read state	✅ `event_read`	131k
Subscription / routing	✅ `event_subscription`	only 3 rows configured
Vocab whitelist	✅ `event_type_registry`	31 types
Lease primitive	✅ `dot_iu_runtime_lease`	empty
Trigger-IN SQL bridge	✅ `iu_sql_event_route`	1 row, dry-run only
Idempotency log per consumer	✅ `iu_auto_instantiate_event_log`	33 successful
Payload-classification CHECK	✅	hard no-secret rule live
In-DB scheduler	❌ no pg_cron	external invocation only
LISTEN/NOTIFY for event substrate	❌	only kb_vector_sync uses it
Generic job (not event) substrate	❌	no `job_outbox` / `worker_jobs` / `task_outbox`
Per-event priority	❌	only 2-lane `immediate/delayed`
Per-domain rate-limit / back-pressure	❌	no quota config
Trigger-OUT unified spec	❌	each consumer (auto_instantiate, structure_consumer) is bespoke
Cross-DB / federation	❌	single-PG-instance
Job priority / deadline	❌	not modeled

§10. Survey integrity

All queries used query_pg (READ ONLY transaction, statement_timeout 5s, hard LIMIT 500).
Zero INSERT / UPDATE / DELETE / DDL executed.
No dot_config mutation.
Worker iu_outbound_default cursor unchanged by this survey (last_run_at remained 2026-05-22 11:31 — meaning no one tripped the worker during the survey, which is expected for a read-only role).