GPT Review — 23-P3D4C1 Prompt rev1

Date: 2026-05-08
Reviewer: GPT-5.5 Thinking / Incomex Hội đồng AI
Reviewed: knowledge/dev/laws/dieu44-trien-khai/prompts/23-p3d4c1-staging-outbox-worker-notification-implementation-prompt.md rev1

Verdict

REV2 REQUIRED — do not dispatch rev1.

The prompt direction is correct and follows P3D4C0: staging/outbox first, O(1) birth hook, delayed PG-native worker, Directus/Nuxt deferred. Opus also correctly added CHECK constraint extension, worker log table, and explicit rollback.

However rev1 contains several execution-risk issues that must be patched before approval.

Accepted parts

• Correct roadmap: P3D4C1 before P3D4C2 Directus exposure.
• Correct boundary: no Directus/Nuxt/Hermes/Codex.
• Correct hot-path rule: birth trigger only appends to pending/outbox; no COUNT/JOIN/rollup/batch detection/latest_readers/vector on AI write path.
• Correct event taxonomy target: new_piece_created, document_imported plus existing P3D2 events.
• Correct idea to extend both event-type and event-type-stream CHECK constraints.
• Correct need for worker observability table.
• Correct need for rollback in reverse order.

Required patches for rev2

P1 — Worker log table must be created before worker function

Rev1 creates fn_iu_notification_worker_tick() before iu_notification_worker_log, but the function inserts into that table. Patch order:

1. create iu_notification_worker_log;
2. then create worker function.

P2 — Worker must group by stable key = COALESCE(source_document_ref, import_batch_ref)

Rev1 groups only by source_document_ref. This violates P3D4C0 design and loses import-batch support.

Patch worker logic to use:

stable_group_key = COALESCE(source_document_ref, import_batch_ref)

Rows with NULL stable key must never group and must become piece-level new_piece_created.

P3 — Worker SQL should be simplified and made idempotent/safe

Rev1 grouped update query is too complex and risk-prone. Patch to use a clear temporary/CTE flow:

• lock eligible rows with FOR UPDATE SKIP LOCKED;
• identify rollup groups by stable key and threshold;
• insert rollup events;
• mark exactly rows in emitted rollup groups processed;
• insert piece-level events for remaining eligible rows;
• mark exactly those piece-level rows processed.

Report must include counts: eligible rows, rollup rows, piece rows, skipped rows.

P4 — Advisory lock must be exception-safe

Rev1 unlocks normally but has no exception block. Patch worker with BEGIN ... EXCEPTION ... ensuring pg_advisory_unlock(...) happens on error and error is logged.

P5 — pg_cron installation must be preflighted realistically

CREATE EXTENSION pg_cron may fail if the extension is not available or not configured in the server. Patch Step 1 to:

• check pg_available_extensions;
• check whether pg_cron is already installed;
• attempt install only if available and admin/superuser context exists;
• if unavailable or server requires shared_preload_libraries/restart/config, STOP and report pg_cron_available=FAIL with no partial implementation.

No external scheduler fallback.

P6 — cron schedule must be idempotent

Rev1 calls cron.schedule('iu-notification-worker', ...) directly. Patch to avoid duplicate jobs:

• check existing job by jobname first;
• unschedule/reuse only with explicit safe path;
• report cron_job_existing=... and cron_schedule_created=PASS|ALREADY_PRESENT|FAIL.

P7 — dot_config must be inventoried, not assumed

Rev1 says create minimal dot_config if missing. That may create a new config convention without review.

Patch:

• preflight inspect whether dot_config exists and its columns/types;
• if compatible, insert keys;
• if absent/incompatible, create a Pack-local iu_notification_config only if justified and explicitly included in rollback, OR STOP for GPT review.

Preferred: reuse existing config table if compatible. Do not silently invent a global config convention.

P8 — Config values must be validated/bounded at read time

Worker must clamp or validate:

• debounce seconds: default 90, allowed 60–300;
• batch threshold: default 2, allowed 2–50.

Invalid config values must fall back to defaults or return controlled error; no uncontrolled cast failure.

P9 — Rollback must not blindly drop source/batch columns if data exists

Rev1 rollback drops source_document_ref and import_batch_ref unconditionally. Patch:

• only drop those columns if all values are NULL and user/agent confirms safe;
• otherwise leave columns and document manual cleanup.

P10 — Rollback CHECK constraint revert must account for data created by new event types

If new_piece_created / document_imported rows exist, reverting CHECK constraints to old values will fail or require deleting events.

Patch rollback to either:

• delete only test/pilot events created by P3D4C1 before reverting; and verify zero new event types remain; or
• refuse CHECK revert and report manual review required.

Never delete production notification events silently.

P11 — Report fields must match earlier GPT directive

Rev1 verification fields are good but should add/restore:

phase_status=PASS|FAIL
pg_cron_available=PASS|FAIL
pg_cron_installed_or_verified=PASS|FAIL|ALREADY_PRESENT
source_document_ref_added=PASS|FAIL|ALREADY_PRESENT
import_batch_ref_added=PASS|FAIL|ALREADY_PRESENT
pending_table_created=PASS|FAIL
birth_hook_created=PASS|FAIL
event_taxonomy_extended=PASS|FAIL
worker_function_created=PASS|FAIL
worker_log_table_created=PASS|FAIL
cron_schedule_created=PASS|FAIL|ALREADY_PRESENT
hot_path_joins=0
hot_path_aggregations=0
hot_path_rollups=0
hot_path_latest_readers=0
hot_path_vector_ops=0
hot_path_expected_complexity=O(1)
stable_key_rule=source_document_ref_or_import_batch_ref
no_stable_key_behavior=PIECE_LEVEL_ONLY
batch_rollup_event_verified=PASS|FAIL
unrelated_pieces_not_rolled_up=PASS|FAIL
p3d2_existing_events_regression=PASS|FAIL
rollback_plan=PASS|FAIL
next_required_pack=P3D4C2_DIRECTUS_NOTIFICATION_BOARD_EXPOSURE_PROMPT_REVIEW

P12 — Tests must avoid unreliable wall-clock waiting

Rev1 says wait then manual tick. Patch tests to use deterministic timestamps for pilot pending rows or set debounce_seconds safely in a test transaction/controlled config, then restore.

P13 — Test data cleanup must be explicit

Prompt must require pilot/test rows to be tagged with unique source values and cleaned up or documented. Report must include cleanup verification. Do not leave pilot events/pending rows unless explicitly approved.

P14 — Git commit step must be clarified

Rev1 includes a Git commit although the pack is PG DDL/runtime. Patch to either:

• remove Git commit from execution, or
• only commit if the agent creates/updates repository migration files/scripts.

Do not make a meaningless repo commit for live PG-only changes.

Directive to Opus

Patch the prompt to rev2 at:

knowledge/dev/laws/dieu44-trien-khai/prompts/23-p3d4c1-staging-outbox-worker-notification-implementation-prompt.md

Do not dispatch after patch. Return for GPT/User review.

Hard boundaries unchanged

• No implementation during prompt patch.
• No PG mutation during prompt patch.
• No Directus mutation.
• No Nuxt code.
• No Hermes.
• No Codex dispatch.
• No external scheduler/tool.
• No heavy computation on AI hot path.
• No raw body/payload/vector exposure.

Summary

P3D4C1 rev1 has the correct strategic shape but is not dispatch-safe yet. Rev2 must fix worker ordering, stable-key grouping, exception-safe lock handling, pg_cron/config preflight, rollback safety, deterministic tests, and report fields.