P10B-1C-PF — D32 Insert SQL Inspection Preflight v0.2

Date: 2026-04-29 Mode: READ-ONLY file inspection (no SQL executed) Verdict: PASS

---

1. File existence + SHA match

All 5 files present at /tmp/p10b-1b/ and SHA256 matches P10B-1B exactly:

File	SHA256	Match
candidate-units.json	b62d48a0…b7626	✅
insert-candidate.sql	2b756685…b82a0a	✅
render.sql	a44bda97…f92d106	✅
rollback.sql	42dba174…48464c	✅
verify-counts.sql	65973b78…c6def6	✅

2. Transaction structure analysis

• Line 4: BEGIN; ✅
• Lines 6–10: CREATE TEMP TABLE _d32_lu_map ... ON COMMIT DROP;
• Lines 11–177: Single DO $D32$ ... END $D32$; PL/pgSQL block (no nested EXCEPTION / ROLLBACK).
• Lines 172–176: Post-block in-tx verification SELECTs.
• Lines 178–180: Decision point — both COMMIT and ROLLBACK are commented out:

  -- Decision point: COMMIT to persist, ROLLBACK to abort.-- ROLLBACK;  -- default safe stop for dry-run-- COMMIT;    -- only when authorized
  

ROLLBACK occurrences: 2 (both inside comments). COMMIT occurrences: 3 (line 10 ON COMMIT DROP, plus two commented decision lines). BEGIN occurrences: 2 (line 4 tx open, line 14 inside DO block — PL/pgSQL local, not nested SQL tx).

File does NOT terminate with a bare ROLLBACK;. The transaction is left open at EOF; the executor must explicitly append/uncomment COMMIT; or ROLLBACK;.

ROLLBACK→COMMIT replacement: SAFE & UNAMBIGUOUS

Replacement is realized by uncommenting line 180 (-- COMMIT;) — there is exactly one labelled COMMIT; directive line and exactly one labelled ROLLBACK; directive line, both clearly tagged. No bare ROLLBACK exists elsewhere to disambiguate. ✅

3. INSERT targets (comment-stripped)

tac_publication:        1
tac_logical_unit:       23
_d32_lu_map:            23  (session temp, allowed)
tac_unit_version:       23
tac_publication_member: 23
─────────────────────────────
Total INSERT statements: 93

No unexpected targets. Counts of real-table inserts match expectation 1+23+23+23 = 70. ✅

4. Destructive command scan (comment-stripped)

Token	Count	Context	Verdict
DROP	1	ON COMMIT DROP (temp-table cleanup, line 10)	safe
UPDATE	26	All inside WITH ins AS (INSERT ... RETURNING id) UPDATE _d32_lu_map SET uv_id = ins.id ... — write target is the session temp table only	safe
DELETE / update	several	All inside body string literals of LU bodies (e.g. policy text "ON UPDATE RESTRICT, ON DELETE RESTRICT")	safe
ALTER / TRUNCATE	0	—	—
Non-TEMP CREATE	0	—	—

No destructive operation touches real (persisted) tables. ✅

5. Sample mapping (line ranges + columns + linkage)

ROOT — D38-DIEU32-ROOT

• LU insert line 22 → tac_logical_unit (canonical_address, doc_code, parent_id=NULL, sort_order=0, section_type='heading', owner='incomex_council', lifecycle_status='draft_only'). RETURNING id captured into _d32_lu_map.
• UV insert line 47 → tac_unit_version (logical_unit_id via (SELECT lu_id FROM _d32_lu_map WHERE canonical_address='D38-DIEU32-ROOT'), version_number=1, title='ĐIỀU 32: LUẬT PHÊ DUYỆT — v1.1 BAN HÀNH', body=NULL, description = title ('ĐIỀU 32: LUẬT PHÊ DUYỆT — v1.1 BAN HÀNH'), lifecycle_status='draft', review_state='unreviewed', provenance='PROV-AI'). CTE: UPDATE _d32_lu_map SET uv_id=ins.id.
• PM insert line 147 → tac_publication_member (publication_id=v_pub_id, logical_unit_id=lu_id, unit_version_id=uv_id, render_order=0). Linkage: subquery SELECT v_pub_id, lu_id, uv_id, 0 FROM _d32_lu_map WHERE canonical_address='D38-DIEU32-ROOT'.
• parent_id resolution: NULL (root).

S1 — D38-DIEU32-S1 (leaf paragraph)

• LU insert line 24: parent_id via (SELECT lu_id FROM _d32_lu_map WHERE canonical_address='D38-DIEU32-ROOT'), sort_order=2, section_type='paragraph'.
• UV insert line 51: title='§1. Mục đích', body = full Vietnamese body text (one paragraph, untruncated), description='§1. Mục đích' (title stub, NOT body).
• PM insert line 149: render_order=2.

S2-P1 — D38-DIEU32-S2-P1 (child of S2)

• LU insert line 33: parent_id via (SELECT lu_id FROM _d32_lu_map WHERE canonical_address='D38-DIEU32-S2'), sort_order=1, section_type='principle'.
• UV insert line 74: title='§2.1 DOT 100%', body = full body, description='§2.1 DOT 100%'.
• PM insert line 158: render_order=4.

General column mapping:

• LU columns: canonical_address, doc_code, parent_id, sort_order, section_type, owner, lifecycle_status (constant 'draft_only').
• UV columns: logical_unit_id, version_number=1, title, body (full body or NULL for headings), description = title stub (always title-only, not full body), lifecycle_status='draft', review_state='unreviewed', provenance='PROV-AI'.
• PM columns: publication_id (from v_pub_id DECLARE'd in DO block), logical_unit_id, unit_version_id, render_order. Linkage uses _d32_lu_map join + literal v_pub_id.

6. render.sql — parameter needs / scope / output

• No parameter needed. Internally scoped via WHERE doc_code='DIEU-32' AND version='v1.1'.
• Recursive CTE tree with materialized-path array → preorder DFS.
• Output columns: depth, render_order, canonical_address, section_type, title, body (body coalesced to ''). ORDER BY path. ✅
• Scope correctly limited to DIEU-32 v1.1.

7. verify-counts.sql — parameter needs / scope

• No parameter needed. Hard-scoped to doc_code='DIEU-32' AND version='v1.1' and canonical_address LIKE 'D38-DIEU32-%'.
• 4 SELECTs returning (t, actual, expected): pub=1, lu=23, uv=23, pm=23. ✅

8. rollback.sql (informational)

Standalone BEGIN tx (separate from insert-candidate); deletes in FK-safe order PM → UV → (NULL parent_id) → LU → publication, then 4 verification counts (each must be 0). Final COMMIT/ROLLBACK both commented (operator decision).

9. Verdict

PASS. All preflight invariants satisfied:

• ✅ 5 files exist + SHA match
• ✅ Transaction structure clear; commented COMMIT/ROLLBACK directive lines unambiguous
• ✅ INSERT targets exactly the 4 expected tables + temp; counts 1/23/23/23
• ✅ No destructive SQL outside _d32_lu_map (temp) and ON COMMIT DROP
• ✅ Sample mapping (ROOT/S1/S2-P1) verified across LU+UV+PM
• ✅ render.sql / verify-counts.sql self-scoped (no parameter required)

Recommendation: Proceed to P10B-1C execute upon Opus review + GPT authorize. To run as commit, the executor must uncomment line 180 of insert-candidate.sql (-- COMMIT; → COMMIT;) — or, if dry-run only, uncomment line 179 (-- ROLLBACK; → ROLLBACK;).