---

title: 04 — BEGIN..ROLLBACK View/Function Rehearsal (Branch D) date: 2026-05-31 verdict: GREEN — all 9 candidate objects compiled; rollback clean; 0 leftover; idle_tx 0 mutation: none committed (BEGIN..ROLLBACK only, workflow_admin)

04 — View/Function Rehearsal (Branch D)

Channel: ssh contabo → docker exec -i postgres psql -U workflow_admin -d directus -v ON_ERROR_STOP=1, single transaction, SET LOCAL lock_timeout=3s / statement_timeout=20s / idle_in_transaction_session_timeout=40s, ROLLBACK at end. Full SQL in doc 12.

Pre-flight probe (PASS)

BEGIN; CREATE VIEW v_tmp_probe_rollback…; SELECT (exists)=1; ROLLBACK; SELECT (exists)=0; idle_tx=0 → workflow_admin can CREATE, rollback removes it, no leaked transaction.

Candidate objects & per-object verdict

#	object	compiled	rows (in-tx)	key columns	no-hardcode risk	rollback proof	readiness
1	v_count_integrity	✅	160 (corrected)	code, counted, actual_count, orphan_count, phantom_count_derived, drift_flag, pivot_backed, count_integrity_status	leaf rule from columns (OK); counted honestly flagged pivot_backed t/f	gone after ROLLBACK	rehearsal-proven
2	v_registries_pivot_tree	✅	37	node_code, parent_code, is_root, has_children	none	gone	proven but FLAT (see below)
3	v_living_lists	✅	160 (corrected)	code, list_count, pivot_code, pivot_backed	none	gone	rehearsal-proven
4	v_count_drift	✅	3	code, gap, drift_side	none	gone	rehearsal-proven (matches doc 03)
5	fn_registries_pivot_node_substrate(text)	✅	1/call	node_code, source_object, source_location, source_model, record_count, has_pivot	none	gone	rehearsal-proven
6	v_registry_leaf_set	✅	160	leaf rule columns	leaf rule data-driven	gone	rehearsal-proven
7	v_registry_label_grouping_required	✅	160 (27 exceed)	list_size, max_ungrouped_threshold_default, exceeds_default_ceiling	⚠ literal 50 (THRESHOLD_REGISTRY_GAP, doc 06)	gone	proven w/ caveat
8	registry_pin (table)	✅	1 insert	scope CHECK in('global','user','role','team')	none	gone	rehearsal-proven (doc 07)
9	corrected v_count_integrity (fan-out-free)	✅	160	—	—	gone	canonical

Post-ROLLBACK existence proof: post_views leftover = 0, post_tables leftover = 0, post_fn leftover = 0, idle in transaction = 0. Entry == exit.

Critical finding 1 — a naive pivot JOIN re-introduces the double-count

First-pass v_count_integrity/v_living_lists used … LEFT JOIN pivot_definitions pd ON pd.source_object = split_part(source_location,':',2). Because some leaf tables map to multiple pivots, the join fanned 160 → 172 rows and inflated the invariant:

	naive JOIN	corrected (scalar EXISTS)	true leaf (doc 03)
rows	172	160	160
Σ counted	3,917,005	1,954,686	1,954,686
Σ phantom	110,102	36,798	36,798

→ Mandatory rule: the integrity/coverage views must compute pivot-backing with scalar EXISTS(…) or LEFT JOIN LATERAL (… LIMIT 1), never a row-multiplying join. This is the same disguised-double-count Đ28 forbids, re-entering through SQL rather than Nuxt. The corrected view reproduces the leaf invariant exactly.

Critical finding 2 — the drill tree is structurally FLAT today

v_registries_pivot_tree over pivot_definitions: 37 nodes, 0 with children, 37 roots (because parent_code is NULL on all 37). The tree view compiles but is non-functional as a tree. → Populating parent_code (doc 02 EXTEND) is a hard prerequisite for any backend-driven drill (doc 05).

Critical finding 3 — source_location is not a reliable pivot key

fn_registries_pivot_node_substrate('CAT-006') returned source_object='dot/bin/', has_pivot=f — yet CAT-006 is PIV-007 (dot_tools). The File:-prefixed source_location does not map to pivot_definitions.source_object. So the naive coverage count (13/160 pivot-backed) under-reports true coverage for model-B rows. A dedicated meta_catalog.pivot_code mapping column is needed (additional EXTEND beyond parent_code).

What this proves for readiness

• The additive view/function/table set is DDL-valid and rollback-safe under workflow_admin → READY_FOR_VIEW_COMMIT once RG1+RG2 are human-accepted (doc 10).
• It is NOT yet correct-by-construction: the canonical view must be the scalar-EXISTS form; parent_code and a pivot_code map must be populated; the 50 literal must move to a PG threshold column.