08 — Rollback & Recovery Package

All three mutations are reversible. Local SQL: /tmp/rp_agg_commit.sql, /tmp/rp_agg_rollback.sql, /tmp/rp_status_views.sql, /tmp/rp_agg_rehearsal.sql.

Pre-images (for exact restore)

object	pre-image
pivot_definitions count	37 (before) → 39 (after)
parent graph hash	91576fb60f9331d8ae8cb86998504c70 (before) → 8c5d25a894275b80dc0fc7054af337cb (after)
birth_registry	1,132,011 (before) → 1,132,013 (after)
PIV-104 filter_spec	{"filters":[{"op":"=","field":"status","value":"published"}]} (before) → {"filters":[]} (after)

Rollback script (/tmp/rp_agg_rollback.sql)

BEGIN;
-- (D) restore PIV-104 published-only filter
UPDATE pivot_definitions
  SET filter_spec='{"filters":[{"op":"=","field":"status","value":"published"}]}'::jsonb, updated_at=now()
  WHERE code='PIV-104';
-- (A) remove the two added aggregate pivots (one statement)
DELETE FROM pivot_definitions WHERE code IN ('PIV-311','PIV-313');
-- optional birth cleanup (leaves phantoms if skipped):
-- DELETE FROM birth_registry WHERE entity_code IN ('PIV-311','PIV-313') AND collection_name='pivot_definitions';
COMMIT;
-- (E) drop the two additive status views (fully reversible, no data)
DROP VIEW IF EXISTS v_rp_drill_reconciliation;
DROP VIEW IF EXISTS v_rp_aggregate_candidate_register;

Important rollback caveat — birth rows

Deleting PIV-311/PIV-313 from pivot_definitions leaves their two birth_registry rows as registered-but-absent (phantom) because there is no birth-retire mechanism. To fully clean, run the commented DELETE FROM birth_registry … line (owner decision). In practice rollback is not expected — both pivots are legitimate FINAL totals.

Reversibility summary

mutation	reverse	clean?
INSERT PIV-311/313	DELETE the 2 rows	leaves 2 birth rows unless birth-cleanup line run
UPDATE PIV-104 filter	restore pre-image filter	fully clean (UPDATE)
CREATE 2 views	DROP VIEW	fully clean (no data)

Recovery verification (after any rollback)

SELECT count(*) FROM pivot_definitions;                         -- expect 37
SELECT filter_spec FROM pivot_definitions WHERE code='PIV-104'; -- expect published filter
SELECT count(*) FROM pg_class WHERE relname IN
  ('v_rp_drill_reconciliation','v_rp_aggregate_candidate_register'); -- expect 0