Files
canina/scripts/auth-schema-reconcile.sql

47 lines
2.9 KiB
PL/PgSQL

-- Independently approved reconciliation; NOT a Prisma history migration.
-- Run with psql -X -v ON_ERROR_STOP=1. Do not wrap in migrate deploy/db push.
BEGIN;
SET LOCAL search_path = public, pg_catalog;
SET LOCAL lock_timeout = '3s';
SET LOCAL statement_timeout = '30s';
SET LOCAL idle_in_transaction_session_timeout = '60s';
SELECT pg_advisory_xact_lock(20261025, 1);
DO $$
DECLARE x record;
BEGIN
IF to_regclass('public.users') IS NULL THEN RAISE EXCEPTION 'users missing'; END IF;
FOR x IN SELECT * FROM (VALUES ('auth_expires_at','timestamp with time zone'),('stage_test_session','character varying(64)'),('stage_test_generation','uuid')) AS expected(name,typ) LOOP
IF EXISTS (SELECT 1 FROM pg_attribute WHERE attrelid='public.users'::regclass AND attname=x.name AND NOT attisdropped) THEN
IF NOT EXISTS (SELECT 1 FROM pg_attribute a LEFT JOIN pg_attrdef d ON d.adrelid=a.attrelid AND d.adnum=a.attnum WHERE a.attrelid='public.users'::regclass AND a.attname=x.name AND NOT a.attisdropped AND format_type(a.atttypid,a.atttypmod)=x.typ AND NOT a.attnotnull AND d.oid IS NULL) THEN
RAISE EXCEPTION 'unexpected existing users column %',x.name;
END IF;
END IF;
END LOOP;
IF to_regclass('public.prepare_workflow_outbox') IS NOT NULL THEN
RAISE EXCEPTION 'outbox already exists: inspect equivalence before any repeat execution';
END IF;
FOR x IN SELECT * FROM (VALUES ('orchestration_tasks'),('orchestration_runs'),('orchestration_budget_ledger')) AS refs(name) LOOP
IF NOT EXISTS (SELECT 1 FROM pg_attribute a WHERE a.attrelid=to_regclass('public.'||x.name) AND a.attname='id' AND a.atttypid='uuid'::regtype AND NOT a.attisdropped AND a.attnotnull) THEN
RAISE EXCEPTION 'missing UUID reference %',x.name;
END IF;
END LOOP;
END $$;
ALTER TABLE public.users ADD COLUMN IF NOT EXISTS auth_expires_at timestamptz,
ADD COLUMN IF NOT EXISTS stage_test_session varchar(64),
ADD COLUMN IF NOT EXISTS stage_test_generation uuid;
CREATE TABLE public.prepare_workflow_outbox (
id uuid PRIMARY KEY,
task_id uuid NOT NULL UNIQUE REFERENCES public.orchestration_tasks(id) ON DELETE RESTRICT,
status varchar(20) NOT NULL DEFAULT 'PENDING' CHECK (status IN ('PENDING','DONE')),
attempts integer NOT NULL DEFAULT 0 CHECK (attempts BETWEEN 0 AND 3),
lease_owner varchar(100), lease_token uuid, lease_until timestamptz, heartbeat_at timestamptz,
run_id uuid REFERENCES public.orchestration_runs(id) ON DELETE RESTRICT,
reservation_id uuid REFERENCES public.orchestration_budget_ledger(id) ON DELETE RESTRICT,
created_at timestamptz NOT NULL DEFAULT now(),
not_before timestamptz
);
CREATE INDEX prepare_workflow_outbox_status_lease_until_idx ON public.prepare_workflow_outbox(status,lease_until);
CREATE INDEX prepare_workflow_outbox_due_idx ON public.prepare_workflow_outbox(status,not_before,created_at);
-- Deliberately no _prisma_migrations writes or unrelated DDL.
COMMIT;