08 — Rollback and Recovery Package

Pre-image (backup of affected rows)

All 13 child codes had parent_code = NULL before commit:

MTX-L2-ATOM, PIV-101, PIV-102, PIV-103, PIV-104, PIV-105, PIV-106,
PIV-201, PIV-202, PIV-203, PIV-204, PIV-205, PIV-206   → all NULL

Pre-commit parent_code-hash = 66c0692600dc6a0a1589f00e75862f03. Post-commit parent_code-hash = e0e89070781db28af753e20814432afb.

Rollback script (one statement; saved at /tmp/rp_parent_graph_rollback.sql)

BEGIN;
UPDATE pivot_definitions SET parent_code = NULL
WHERE code IN ('PIV-101','PIV-102','PIV-106','PIV-201','PIV-202','PIV-203',
               'PIV-204','PIV-205','PIV-206','PIV-104','PIV-105','PIV-103','MTX-L2-ATOM');
-- expect: roots back to 37, children 0
SELECT count(*) FILTER (WHERE parent_code IS NULL) AS roots, count(parent_code) AS children
FROM pivot_definitions;
COMMIT;

Reverting fires the same designed statement-level count-refresh (cache only) — safe and reversible. Because every pre-image was NULL, this restores byte-identical pre-commit parent_code state.

Channel to apply rollback

ssh contabo "docker exec -i -e PGCLIENTENCODING=UTF8 postgres psql -U workflow_admin -d directus -v ON_ERROR_STOP=1" < /tmp/rp_parent_graph_rollback.sql

Local SQL artifacts (this macro)

file	purpose
/tmp/rp_parent_graph_rehearsal.sql	BEGIN..ROLLBACK validation (24/6/13/0/0/0 + reversibility)
/tmp/rp_refresh_diff.sql	row-grain count-refresh side-effect diff
/tmp/rp_parent_graph_commit.sql	the committed statement + pre/post verification
/tmp/rp_parent_graph_rollback.sql	the rollback
/tmp/rp_aggregate_pivot_rehearsal.sql	PIV-311/313 add rehearsal (rolled back)

Recovery posture

• The mutation is a single metadata column on 13 rows. Worst case (UI regression on hierarchy) → run the rollback above; tree returns to honest-flat; the node contract reverts to has_children=false with no view change.
• No base/source/business table was mutated; no backup/restore of data is needed.
• The aggregate-pivot adds (PIV-311/313) were rehearsed and rolled back — nothing to recover there.