91000x · 06 — Rollback Package

Phase: F (rollback component)
Status: AUTHORED, READY
Date: 2026-05-26

Apply channel for rollback

Same channel as apply:

ssh contabo 'docker exec -i postgres psql -U workflow_admin -d directus -v ON_ERROR_STOP=1' << EOSQL
…rollback body…
EOSQL

Pre-rollback safety check

Before running ANY rollback, confirm no row currently in pending_review AND no row consumed_by mark/verify functions in the past 90d:

SELECT count(*) FROM iu_core.iu_staging_record WHERE lifecycle_status = 'pending_review';
SELECT count(*) FROM dot_iu_command_run
  WHERE command_name IN ('dot_iu_mark_article','dot_iu_verify_mark_manifest')
    AND created_at > now() - INTERVAL '90 days';

If pending_review count > 0, the 037 lifecycle-CHECK rollback (which drops pending_review from the vocab) will REFUSE. Resolve those rows first via fn_iu_verify_mark apply or fn_iu_staging_unregister.

Rollback 039 (run FIRST when rolling back the full stack)

BEGIN;
DELETE FROM dot_iu_command_catalog WHERE command_name='dot_iu_verify_mark_manifest';
DROP FUNCTION IF EXISTS fn_iu_verify_mark(uuid, boolean, text, text, text);
COMMIT;

Rollback 038

BEGIN;
DELETE FROM dot_iu_command_catalog WHERE command_name='dot_iu_mark_article';
DROP FUNCTION IF EXISTS fn_iu_mark_create_manifest(jsonb, text, jsonb, text, text, text, text, text);
COMMIT;

Rollback 037 (run LAST)

BEGIN;

DELETE FROM dot_iu_command_catalog WHERE command_name IN ('dot_iu_staging_cleanup','dot_iu_staging_unregister');
DROP FUNCTION IF EXISTS fn_iu_staging_unregister(uuid, boolean, text);
DROP FUNCTION IF EXISTS fn_iu_staging_cleanup(boolean, text);

-- Retention rows: ON CONFLICT preserved D36's rows; rollback should NOT delete D36 rows.
-- 037 did not create new retention rows (ON CONFLICT DO NOTHING was a no-op); no DELETE needed.

ALTER TABLE iu_core.iu_staging_record DROP CONSTRAINT IF EXISTS iu_staging_record_expiry_ceiling_chk;

-- Restore prior consumed_consistency (looser; original required only consumed_at + consumed_by_run_id)
ALTER TABLE iu_core.iu_staging_record DROP CONSTRAINT IF EXISTS iu_staging_record_consumed_consistency_chk;
ALTER TABLE iu_core.iu_staging_record ADD CONSTRAINT iu_staging_record_consumed_consistency_chk
  CHECK (((lifecycle_status = 'consumed') <= ((consumed_at IS NOT NULL) AND (consumed_by_run_id IS NOT NULL))));

-- Restore prior lifecycle vocab (drops pending_review — refuses if any row currently in pending_review)
ALTER TABLE iu_core.iu_staging_record DROP CONSTRAINT IF EXISTS iu_staging_record_lifecycle_chk;
ALTER TABLE iu_core.iu_staging_record ADD CONSTRAINT iu_staging_record_lifecycle_chk
  CHECK (lifecycle_status IN ('pending','approved','consumed','rejected','expired','cleaned'));

COMMIT;

Full disaster rollback (from pg_dump)

If selective rollback won't fly:

# Drop and restore the entire directus DB from pre-apply backup
docker exec postgres pg_restore -U workflow_admin -d directus --clean --if-exists /tmp/pre-91000x-20260526T010854Z.dump

Caveats:

• --clean drops all existing objects in directus DB before restore — this WILL also drop anything created or altered by other writers between 01:08:54 UTC and the rollback moment. Coordinate with Directus, Nuxt, and any other live writers.
• A safer alternative: dump current state first (pg_dump -Fc … post-current.dump), restore pre-state to a side DB, diff schemas, then surgically reverse only the 91000x deltas.

Verifying clean rollback

After 037+038+039 rollback (in 039 → 038 → 037 order):

SELECT count(*) AS should_be_zero FROM dot_iu_command_catalog
  WHERE command_name IN ('dot_iu_staging_cleanup','dot_iu_staging_unregister',
                         'dot_iu_mark_article','dot_iu_verify_mark_manifest');
-- expected: 0

SELECT count(*) AS should_be_zero FROM pg_proc
  WHERE proname IN ('fn_iu_staging_cleanup','fn_iu_staging_unregister',
                    'fn_iu_mark_create_manifest','fn_iu_verify_mark');
-- expected: 0

SELECT pg_get_constraintdef(oid) AS lifecycle_chk FROM pg_constraint
  WHERE conname='iu_staging_record_lifecycle_chk';
-- expected: 6-vocab without 'pending_review'

SELECT count(*) AS expiry_ceiling_should_be_zero FROM pg_constraint
  WHERE conname='iu_staging_record_expiry_ceiling_chk';
-- expected: 0

Rollback NOT needed for 040/041

Neither migration was applied — no rollback path required.

Operational note

This rollback set is intended for emergency use. The normal forward path is 100000x which fixes 040/041 schema drift and unblocks the full MARK → CUT pipeline. Rolling back 037-039 also removes the pending_review lifecycle state — any future MARK writer will need to use pending as initial state, or re-apply 037 first.

Cross-links

• 90000x reports 02-04 — original rollback authoring (this file refines for live patch state).
• [[feedback-honest-channel-block-beats-partial-trigger]] — rollback safety doctrine.