DataOps-platform/migrations/versions/20260817_517_tenant_independent_approval_facts.py

"""Require independently issued, single-use approval facts for recovery/delete."""
from alembic import op

revision="20260817_517"
down_revision="20260817_516"
branch_labels=None
depends_on=None
def upgrade() -> None:
 op.execute(r"""
  DO $$ BEGIN IF NOT EXISTS(SELECT 1 FROM pg_roles WHERE rolname='dataops_tenant_approver') THEN RAISE EXCEPTION 'tenant approver role must be provisioned by role-init'; END IF; END $$;
  CREATE TABLE public.tenant_approval_facts (approval_uid uuid PRIMARY KEY,tenant_id text NOT NULL REFERENCES public.tenants(tenant_id),action text NOT NULL CHECK(action IN ('begin_recovery','delete')),request_digest char(64) NOT NULL,lease_fence bigint NOT NULL,issuer_id uuid NOT NULL,reviewer_id uuid NOT NULL,issued_at timestamptz NOT NULL DEFAULT clock_timestamp(),expires_at timestamptz NOT NULL,consumed_at timestamptz,CHECK(issuer_id<>reviewer_id),CHECK(expires_at>issued_at));
  ALTER TABLE public.tenant_approval_facts OWNER TO dataops_tenant_foundation_owner; REVOKE ALL ON public.tenant_approval_facts FROM PUBLIC,dataops_app_runtime,dataops_tenant_control,dataops_tenant_approver;
  CREATE FUNCTION public.tenant_approver_issue(p jsonb) RETURNS jsonb LANGUAGE plpgsql SECURITY DEFINER SET search_path=pg_catalog,public AS $a$
   DECLARE u uuid; t text; a text; d text; f bigint; i uuid; r uuid; x integer;
   BEGIN IF NOT pg_has_role(session_user,'dataops_tenant_approver','MEMBER') THEN RAISE EXCEPTION 'tenant_approver_identity_required'; END IF; IF jsonb_typeof(p)<>'object' OR NOT(p ?& ARRAY['approval_uid','tenant_id','action','request_digest','lease_fence','issuer_id','reviewer_id','ttl_seconds']) THEN RAISE EXCEPTION 'tenant_payload_closed'; END IF; u:=(p->>'approval_uid')::uuid;t:=p->>'tenant_id';a:=p->>'action';d:=p->>'request_digest';f:=(p->>'lease_fence')::bigint;i:=(p->>'issuer_id')::uuid;r:=(p->>'reviewer_id')::uuid;x:=(p->>'ttl_seconds')::integer; IF a NOT IN('begin_recovery','delete') OR d !~ '^[0-9a-f]{64}$' OR i=r OR x<1 OR x>600 THEN RAISE EXCEPTION 'tenant_approval_invalid'; END IF; INSERT INTO public.tenant_approval_facts VALUES(u,t,a,d,f,i,r,clock_timestamp(),clock_timestamp()+make_interval(secs=>x),NULL); RETURN jsonb_build_object('approval_uid',u); END;$a$;
  ALTER FUNCTION public.tenant_approver_issue(jsonb) OWNER TO dataops_tenant_foundation_owner; REVOKE ALL ON FUNCTION public.tenant_approver_issue(jsonb) FROM PUBLIC,dataops_app_runtime,dataops_tenant_control; GRANT EXECUTE ON FUNCTION public.tenant_approver_issue(jsonb) TO dataops_tenant_approver;
  CREATE FUNCTION public.tenant_control_lifecycle_approved_mutate(c uuid,n uuid,q jsonb) RETURNS jsonb LANGUAGE plpgsql SECURITY DEFINER SET search_path=pg_catalog,public AS $l$
   DECLARE cl public.tenant_control_claims%ROWTYPE; ap uuid; ac text;
   BEGIN SELECT * INTO cl FROM public.tenant_control_claims WHERE claim_uid=c FOR UPDATE; IF NOT FOUND THEN RAISE EXCEPTION 'tenant_claim_denied'; END IF; ac:=q->>'operation'; IF ac IN('begin_recovery','delete') THEN ap:=(q->>'approval_ref')::uuid; UPDATE public.tenant_approval_facts SET consumed_at=clock_timestamp() WHERE approval_uid=ap AND consumed_at IS NULL AND expires_at>clock_timestamp() AND tenant_id=cl.tenant_id AND action=ac AND request_digest=cl.request_digest AND lease_fence=(q->>'expected_fence')::bigint AND issuer_id<>reviewer_id; IF NOT FOUND THEN RAISE EXCEPTION 'tenant_approval_denied'; END IF; END IF; RETURN public.tenant_control_lifecycle_mutate(c,n,q); END;$l$;
  ALTER FUNCTION public.tenant_control_lifecycle_approved_mutate(uuid,uuid,jsonb) OWNER TO dataops_tenant_foundation_owner; REVOKE ALL ON FUNCTION public.tenant_control_lifecycle_approved_mutate(uuid,uuid,jsonb) FROM PUBLIC,dataops_app_runtime; GRANT EXECUTE ON FUNCTION public.tenant_control_lifecycle_approved_mutate(uuid,uuid,jsonb) TO dataops_tenant_control;
 """)
def downgrade() -> None:
 op.execute("DO $$ BEGIN IF EXISTS(SELECT 1 FROM public.tenant_approval_facts LIMIT 1) THEN RAISE EXCEPTION 'tenant approval downgrade refused'; END IF; END $$; DROP FUNCTION public.tenant_control_lifecycle_approved_mutate(uuid,uuid,jsonb); DROP FUNCTION public.tenant_approver_issue(jsonb); DROP TABLE public.tenant_approval_facts;")