04 — Rollback Package

Authoring of the forward migration was paired with a rollback SQL and two pg_dump-Fc backups, so Phase 1 can be reverted without state ambiguity.

Files

Path	Bytes	Purpose
/opt/incomex/backups/dieu45-phase1/pre-dieu45-phase1-20260526T110807Z.dump	82,775,116	pre-apply snapshot
/opt/incomex/backups/dieu45-phase1/post-dieu45-phase1-20260526T112144Z.dump	82,809,926	post-apply snapshot
/root/dieu45-phase1/050-dieu45-phase1-minimal-job-substrate.sql	679 lines	forward migration
/root/dieu45-phase1/050-rollback.sql	37 lines	reverse migration
/root/dieu45-phase1/050-proof.sql	139 lines	bounded BEGIN/ROLLBACK proof

Rollback script (verbatim)

BEGIN;

DROP TRIGGER IF EXISTS tr_queue_heartbeat_updated_at ON public.queue_heartbeat;
DROP TRIGGER IF EXISTS tr_job_queue_updated_at      ON public.job_queue;

DROP VIEW IF EXISTS public.v_job_dead_letter_summary;
DROP VIEW IF EXISTS public.v_job_queue_backlog;
DROP VIEW IF EXISTS public.v_queue_health;

DROP FUNCTION IF EXISTS public.fn_queue_stale_check(integer);
DROP FUNCTION IF EXISTS public.fn_queue_heartbeat_tick(text, text, text, uuid, text, jsonb);
DROP FUNCTION IF EXISTS public.fn_job_fail_or_retry(uuid, text, text, boolean);
DROP FUNCTION IF EXISTS public.fn_job_move_to_dead_letter(uuid, text, text);
DROP FUNCTION IF EXISTS public.fn_job_ack(uuid, text, text);
DROP FUNCTION IF EXISTS public.fn_job_claim(text, text[], integer);
DROP FUNCTION IF EXISTS public.fn_job_enqueue(text, text, text, jsonb, text, text, integer, timestamptz, text, integer, uuid);
DROP FUNCTION IF EXISTS public.fn_job_queue_updated_at();

DROP TABLE IF EXISTS public.queue_heartbeat;
DROP TABLE IF EXISTS public.job_dead_letter;
DROP TABLE IF EXISTS public.job_queue;

DELETE FROM public.dot_config WHERE key IN (
    'queue.job_substrate.enabled',
    'queue.worker.enabled',
    'queue.notify.enabled',
    'queue.heartbeat.enabled',
    'queue.heartbeat.stale_threshold_seconds',
    'queue.retry.max_attempts_default',
    'queue.retry.backoff_base_sec',
    'queue.lease.duration_sec'
);

COMMIT;

Drop order rationale

Triggers → views → functions → tables → config keys. Functions are dropped before their tables because some reference table types (%ROWTYPE). Views are dropped first to remove any dependency on the tables.

IF EXISTS everywhere so the script is idempotent and won't error if partially rolled back.

Rollback safety properties

• Local-only: all operations on objects created by 050, none on pre-existing tables.
• Wrapped in BEGIN/COMMIT so partial rollback never leaves an inconsistent set.
• Data-loss scope: only data in the 3 new tables. At Phase 1 exit all 3 are empty, so a clean rollback loses nothing.
• pg_dump restore as fallback: pg_restore -U workflow_admin -d directus --clean --if-exists pre-…dump regenerates pre-state exactly.

How to execute

ssh contabo 'docker cp /root/dieu45-phase1/050-rollback.sql postgres:/tmp/050-rb.sql && \
  docker exec postgres psql -U workflow_admin -d directus -v ON_ERROR_STOP=1 -f /tmp/050-rb.sql'

Note

The rollback script is archived but not executed. Phase 1 PASS leaves the substrate in place for Phase 2.