DataOps-platform/migrations/versions/20260811_486_trusted_delivery_database_boundary.py

"""Put WP06 state behind a dedicated database owner and write gateway.

The runtime login is deliberately not a member of either role established
here.  Role creation is performed only by the one-shot role-init process;
this migration therefore fails closed if a deployment skipped that step.
"""

from alembic import op

revision = "20260811_486"
down_revision = "20260811_485"
branch_labels = None
depends_on = None


_TABLES = (
    "trusted_delivery_policy_versions",
    "trusted_delivery_policy_transitions",
    "trusted_delivery_grants",
    "trusted_delivery_deliveries",
    "trusted_delivery_receipts",
    "trusted_delivery_legal_holds",
    "trusted_delivery_hold_releases",
    "trusted_delivery_evidence",
    "trusted_delivery_subscriptions",
    "trusted_delivery_subscription_deliveries",
    "trusted_delivery_control_profile_versions",
    "trusted_delivery_control_profile_transitions",
    "trusted_delivery_capability_approvals",
    "trusted_delivery_supply_chain_gates",
    "trusted_delivery_control_evidence",
    "trusted_delivery_destruction_approvals",
)


def upgrade() -> None:
    op.execute(
        """
        DO $$ BEGIN
          IF NOT EXISTS (SELECT 1 FROM pg_roles WHERE rolname='dataops_trusted_delivery_owner')
             OR NOT EXISTS (SELECT 1 FROM pg_roles WHERE rolname='dataops_trusted_delivery_writer') THEN
            RAISE EXCEPTION 'trusted-delivery role-init is required before migration 20260811_486';
          END IF;
          IF NOT pg_has_role(current_user, 'dataops_trusted_delivery_owner', 'MEMBER')
             OR NOT pg_has_role(current_user, 'dataops_trusted_delivery_writer', 'MEMBER') THEN
            RAISE EXCEPTION 'migrator must be pre-granted trusted-delivery owner and writer roles';
          END IF;
          IF (SELECT rolcreaterole OR rolsuper FROM pg_roles WHERE rolname=current_user) THEN
            RAISE EXCEPTION 'migration identity must not retain CREATEROLE or superuser';
          END IF;
        END $$;

        CREATE TABLE public.trusted_delivery_approval_facts (
          approval_ref VARCHAR(200) PRIMARY KEY,
          approval_digest CHAR(64) NOT NULL CHECK(approval_digest ~ '^[0-9a-f]{64}$'),
          actor_uid UUID NOT NULL REFERENCES public.users(id),
          operation VARCHAR(40) NOT NULL CHECK(operation IN ('activate_policy','rollback_policy','activate_control_profile','rollback_control_profile','grant','release_hold','destruction')),
          status VARCHAR(20) NOT NULL CHECK(status IN ('approved','revoked','expired')),
          expires_at TIMESTAMPTZ NOT NULL,
          signed_receipt_digest CHAR(64) NOT NULL CHECK(signed_receipt_digest ~ '^[0-9a-f]{64}$'),
          created_at TIMESTAMPTZ NOT NULL DEFAULT clock_timestamp()
        );

        CREATE OR REPLACE FUNCTION public.trusted_delivery_write(p_operation text, p_payload jsonb)
        RETURNS jsonb
        LANGUAGE plpgsql
        SECURITY DEFINER
        SET search_path = pg_catalog, public
        AS $trusted_delivery_write$
        DECLARE
          v_uid uuid;
          v_policy uuid;
          v_existing record;
          v_approval text;
          v_digest text;
          v_actor uuid;
        BEGIN
          IF p_operation NOT IN ('create_policy','transition_policy','create_grant','create_subscription')
             OR jsonb_typeof(p_payload) <> 'object' THEN
            RAISE EXCEPTION 'trusted-delivery write is not permitted';
          END IF;
          IF p_operation='create_policy' THEN
            IF NOT (p_payload ?& ARRAY['uid','code','version','selector','resource_rules','created_by'])
               OR p_payload - ARRAY['uid','code','version','selector','resource_rules','created_by','current_version'] <> '{}'::jsonb
               OR jsonb_typeof(p_payload->'selector')<>'object' OR jsonb_typeof(p_payload->'resource_rules')<>'object' THEN
              RAISE EXCEPTION 'trusted-delivery policy payload is closed';
            END IF;
            v_uid := (p_payload->>'uid')::uuid;
            v_actor := (p_payload->>'created_by')::uuid;
            IF NOT EXISTS (SELECT 1 FROM public.users WHERE id=v_actor AND status='active') THEN RAISE EXCEPTION 'trusted-delivery actor is unavailable'; END IF;
            INSERT INTO public.trusted_delivery_policy_versions(uid,code,version,status,selector,resource_rules,created_by,current_version)
            VALUES(v_uid,p_payload->>'code',p_payload->>'version','draft',p_payload->'selector',p_payload->'resource_rules',v_actor,COALESCE((p_payload->>'current_version')::int,1));
            RETURN jsonb_build_object('uid',v_uid::text,'code',p_payload->>'code','version',p_payload->>'version','status','draft','current_version',COALESCE((p_payload->>'current_version')::int,1));
          END IF;
          IF p_operation='transition_policy' THEN
            IF NOT (p_payload ?& ARRAY['code','version','operation','idempotency_key','request_digest','actor_uid','approval_ref','approval_digest'])
               OR p_payload - ARRAY['code','version','operation','idempotency_key','request_digest','actor_uid','approval_ref','approval_digest'] <> '{}'::jsonb THEN
              RAISE EXCEPTION 'trusted-delivery transition payload is closed';
            END IF;
            v_approval:=p_payload->>'approval_ref'; v_digest:=p_payload->>'approval_digest'; v_actor:=(p_payload->>'actor_uid')::uuid;
            IF NOT EXISTS (SELECT 1 FROM public.trusted_delivery_approval_facts WHERE approval_ref=v_approval AND approval_digest=v_digest AND actor_uid=v_actor AND operation=CASE WHEN p_payload->>'operation'='activate' THEN 'activate_policy' ELSE 'rollback_policy' END AND status='approved' AND expires_at>clock_timestamp()) THEN
              RAISE EXCEPTION 'trusted-delivery transition requires protected approval fact';
            END IF;
            SELECT policy_uid,request_digest INTO v_existing FROM public.trusted_delivery_policy_transitions WHERE idempotency_key=p_payload->>'idempotency_key';
            IF FOUND THEN
              IF v_existing.request_digest <> p_payload->>'request_digest' THEN RAISE EXCEPTION 'trusted delivery idempotency conflict'; END IF;
              RETURN (SELECT jsonb_build_object('uid',uid::text,'code',code,'version',version,'status',status,'current_version',current_version) FROM public.trusted_delivery_policy_versions WHERE uid=v_existing.policy_uid);
            END IF;
            SELECT uid INTO v_policy FROM public.trusted_delivery_policy_versions WHERE code=p_payload->>'code' AND version=p_payload->>'version' FOR UPDATE;
            IF NOT FOUND THEN RAISE EXCEPTION 'trusted delivery policy version was not found'; END IF;
            PERFORM 1 FROM public.trusted_delivery_policy_versions WHERE code=p_payload->>'code' AND status='active' FOR UPDATE;
            UPDATE public.trusted_delivery_policy_versions SET status='retired',current_version=current_version+1 WHERE code=p_payload->>'code' AND status='active' AND uid<>v_policy;
            UPDATE public.trusted_delivery_policy_versions SET status='active',current_version=current_version+CASE WHEN status='active' THEN 0 ELSE 1 END WHERE uid=v_policy;
            INSERT INTO public.trusted_delivery_policy_transitions(uid,policy_uid,operation,idempotency_key,request_digest,approval_ref,approval_digest,actor_uid)
            VALUES(gen_random_uuid(),v_policy,p_payload->>'operation',p_payload->>'idempotency_key',p_payload->>'request_digest',v_approval,v_digest,v_actor);
            RETURN (SELECT jsonb_build_object('uid',uid::text,'code',code,'version',version,'status',status,'current_version',current_version) FROM public.trusted_delivery_policy_versions WHERE uid=v_policy);
          END IF;
          IF p_operation='create_grant' THEN
            IF NOT (p_payload ?& ARRAY['uid','policy_uid','subject_uid','asset_uid','provider','idempotency_key','request_digest','expires_at','status','reason_code','created_by','approval_ref','approval_digest','purpose','environment','target_capability'])
               OR p_payload - ARRAY['uid','policy_uid','subject_uid','asset_uid','provider','idempotency_key','request_digest','expires_at','status','reason_code','current_version','created_by','approval_ref','approval_digest','purpose','environment','target_capability'] <> '{}'::jsonb THEN RAISE EXCEPTION 'trusted-delivery grant payload is closed'; END IF;
            v_approval:=p_payload->>'approval_ref'; v_digest:=p_payload->>'approval_digest'; v_actor:=(p_payload->>'created_by')::uuid;
            IF NOT EXISTS (SELECT 1 FROM public.trusted_delivery_approval_facts WHERE approval_ref=v_approval AND approval_digest=v_digest AND actor_uid=v_actor AND operation='grant' AND status='approved' AND expires_at>clock_timestamp()) THEN RAISE EXCEPTION 'trusted-delivery grant requires protected approval fact'; END IF;
            INSERT INTO public.trusted_delivery_grants(uid,policy_uid,subject_uid,asset_uid,provider,idempotency_key,request_digest,expires_at,status,reason_code,current_version,created_by,approval_ref,approval_digest,purpose,environment,target_capability)
            VALUES((p_payload->>'uid')::uuid,(p_payload->>'policy_uid')::uuid,(p_payload->>'subject_uid')::uuid,(p_payload->>'asset_uid')::uuid,p_payload->>'provider',p_payload->>'idempotency_key',p_payload->>'request_digest',(p_payload->>'expires_at')::timestamptz,p_payload->>'status',p_payload->>'reason_code',COALESCE((p_payload->>'current_version')::int,1),v_actor,v_approval,v_digest,p_payload->>'purpose',p_payload->>'environment',p_payload->'target_capability');
            RETURN jsonb_build_object('uid', p_payload->>'uid', 'status', p_payload->>'status');
          END IF;
          IF p_operation='create_subscription' THEN
            IF NOT (p_payload ?& ARRAY['uid','grant_uid','asset_uid','trigger','purpose','expires_at','idempotency_key','request_digest','status','created_by'])
               OR p_payload - ARRAY['uid','grant_uid','asset_uid','trigger','purpose','expires_at','idempotency_key','request_digest','status','current_version','created_by'] <> '{}'::jsonb
               OR jsonb_typeof(p_payload->'trigger')<>'object' THEN RAISE EXCEPTION 'trusted-delivery subscription payload is closed'; END IF;
            INSERT INTO public.trusted_delivery_subscriptions(uid,grant_uid,asset_uid,trigger,purpose,expires_at,idempotency_key,request_digest,status,current_version,created_by)
            VALUES((p_payload->>'uid')::uuid,(p_payload->>'grant_uid')::uuid,(p_payload->>'asset_uid')::uuid,p_payload->'trigger',p_payload->>'purpose',(p_payload->>'expires_at')::timestamptz,p_payload->>'idempotency_key',p_payload->>'request_digest',p_payload->>'status',COALESCE((p_payload->>'current_version')::int,1),(p_payload->>'created_by')::uuid);
            RETURN jsonb_build_object('uid',p_payload->>'uid','grant_uid',p_payload->>'grant_uid','asset_uid',p_payload->>'asset_uid','trigger',p_payload->'trigger','purpose',p_payload->>'purpose','expires_at',p_payload->>'expires_at','status',p_payload->>'status','current_version',COALESCE((p_payload->>'current_version')::int,1));
          END IF;
          RAISE EXCEPTION 'trusted-delivery write operation is not implemented';
        END $trusted_delivery_write$;

        REVOKE ALL ON FUNCTION public.trusted_delivery_write(text,jsonb) FROM PUBLIC;
        GRANT EXECUTE ON FUNCTION public.trusted_delivery_write(text,jsonb) TO dataops_app_runtime;
        """
    )
    for table in _TABLES:
        op.execute(f"ALTER TABLE public.{table} OWNER TO dataops_trusted_delivery_owner")
        op.execute(
            f"REVOKE INSERT, UPDATE, DELETE, TRUNCATE ON TABLE public.{table} FROM dataops_app_runtime"
        )
        op.execute(f"GRANT SELECT ON TABLE public.{table} TO dataops_app_runtime")
        op.execute(f"GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE public.{table} TO dataops_trusted_delivery_writer")
    op.execute("ALTER TABLE public.trusted_delivery_approval_facts OWNER TO dataops_trusted_delivery_owner")
    op.execute("REVOKE ALL ON TABLE public.trusted_delivery_approval_facts FROM PUBLIC, dataops_app_runtime")
    op.execute("GRANT SELECT ON TABLE public.trusted_delivery_approval_facts TO dataops_trusted_delivery_writer")
    op.execute("ALTER FUNCTION public.trusted_delivery_write(text,jsonb) OWNER TO dataops_trusted_delivery_writer")


def downgrade() -> None:
    op.execute(
        """
        DO $$ BEGIN
          IF EXISTS (SELECT 1 FROM public.trusted_delivery_approval_facts) THEN
            RAISE EXCEPTION 'downgrade requires approved trusted-delivery approval-fact archival migration';
          END IF;
        END $$;
        REVOKE EXECUTE ON FUNCTION public.trusted_delivery_write(text,jsonb) FROM dataops_app_runtime;
        DROP FUNCTION public.trusted_delivery_write(text,jsonb);
        DROP TABLE public.trusted_delivery_approval_facts;
        """
    )