GPT Review — P3D Pack 1 Phase 5C1 rev2 Candidate Not Approved: Advisory Lock + Execution Log Needed

Date: 2026-05-11 Reviewer: GPT-5.5 Thinking / Incomex Hội đồng AI Reviewed:

• knowledge/dev/laws/dieu44-trien-khai/prompts/p3d-pack1-phase5c1-species-mapping-qt001-backfill-prompt-DRAFT.md revision 4
• knowledge/dev/laws/dieu44-trien-khai/reports/p3d-pack1-phase5c1-rev2-transaction-safe-patch-report.md
• prior GPT review requiring transaction safety

Verdict

5C1 rev2 is much improved but NOT approved for Agent dispatch yet.

The prompt now has a single transaction, repeated in-transaction checks, stop-gate semantics, precommit vs committed key distinction, and post-rollback verification. Those fixes are accepted.

However, because the live probe showed species_collection_map has no UNIQUE constraint and taxonomy/schema constraints are mostly application-side, the prompt still needs two production-grade safeguards:

1. an advisory transaction lock to prevent concurrent duplicate execution;
2. an always-on execution log so rollback keys survive even if KB upload fails.

What is accepted

1. Species identity remains correct and unchanged.
2. Single transaction model is accepted.
3. Repeated stale-sensitive checks inside transaction are accepted.
4. management_mode='observed' trap check is accepted.
5. Precommit capture vs committed rollback keys distinction is accepted.
6. Default verification via introspection is accepted.
7. Post-rollback verification is accepted.

Remaining blockers

1. Missing advisory lock / concurrency guard

In-transaction checks reduce stale-read risk but do not fully prevent duplicate writes if two agents run the same prompt concurrently and tables lack UNIQUE constraints.

The probe already found:

species_collection_map has no UNIQUE on collection_name nor (species_code, collection_name)

Therefore rev3 must take a transaction-scoped advisory lock before any write check/insert/update.

Required pattern:

BEGIN;
SELECT pg_advisory_xact_lock(hashtext('p3d_pack1_phase5c1_information_unit_species_mapping_backfill'));
-- repeat collision/dedupe/capture checks
-- writes
-- verification
COMMIT;

The lock key string is an approved pack-scope execution constant. It is not schema/data hardcode.

If lock cannot be acquired or DB does not support the function, STOP and report.

2. Missing always-on execution log before transaction

The prompt says committed rollback keys are written after COMMIT to KB/VPS. But if KB upload fails after commit, rollback evidence can become hard to recover unless the full execution log already captured returned IDs.

Rev3 must require an execution log from the start, for example:

exec > >(tee -a /opt/incomex/logs/p3d-pack1-phase5c1-<timestamp>.log) 2>&1

or equivalent.

The execution log must include:

• precommit target capture;
• captured_species_id;
• captured_mapping_id;
• committed_backfill_birth_ids;
• COMMIT/ROLLBACK outcome;
• final report path.

If KB report upload fails but VPS log contains committed rollback keys, status may be PARTIAL_LOGGED. If both KB and VPS logging fail, the Agent must report a critical failure immediately.

3. Commit-time report semantics need one more flag

Add report flags:

advisory_lock_acquired=true|false
execution_log_path=<path>
execution_log_contains_committed_keys=true|false
kb_report_upload_status=success|failed
rollback_reference_available=kb|vps_log|both|none

Required next action

Patch prompt to rev3 dispatch candidate. Do not execute.

Open:

P3D_PACK1_PHASE5C1_REV3_ADVISORY_LOCK_AND_LOGGED_EXECUTION

Status

phase5c1_rev2_candidate=NOT_APPROVED_FOR_DISPATCH
reason=advisory_lock_and_execution_log_required
species_identity_locked=true
agent_dispatch_allowed=false
seed_allowed=false
backfill_allowed=false
migration_allowed=false
next_action=OPUS_PATCH_5C1_PROMPT_TO_REV3_ADVISORY_LOCK_LOGGED_EXECUTION