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

"""Create the conditional, database-enforced WP10 tenant foundation."""

from alembic import op

revision = "20260817_501"
down_revision = "20260813_500"
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_foundation_owner')
             OR NOT EXISTS (SELECT 1 FROM pg_roles WHERE rolname='dataops_app_runtime') THEN
            RAISE EXCEPTION 'tenant foundation roles must be provisioned before migration';
          END IF;
          IF NOT pg_has_role(current_user, 'dataops_tenant_foundation_owner', 'MEMBER') THEN
            RAISE EXCEPTION 'migration identity lacks tenant foundation owner membership';
          END IF;
        END $$;

        CREATE TABLE public.tenants (
          tenant_id text PRIMARY KEY CHECK (tenant_id ~ '^[a-z][a-z0-9-]{0,62}$'),
          delivery_mode text NOT NULL CHECK (delivery_mode IN ('private_single_tenant','shared_control_plane','saas')),
          state text NOT NULL CHECK (state IN ('provisioning','active','frozen','recovering','deletion_candidate','deleted')),
          lease_fence bigint NOT NULL DEFAULT 0 CHECK (lease_fence >= 0),
          created_at timestamptz NOT NULL DEFAULT clock_timestamp(),
          updated_at timestamptz NOT NULL DEFAULT clock_timestamp()
        );
        CREATE TABLE public.tenant_quotas (
          tenant_id text NOT NULL REFERENCES public.tenants(tenant_id) ON DELETE RESTRICT,
          quota_name text NOT NULL CHECK (quota_name IN ('records','storage_objects','background_tasks','events','models','plugins','connectors','api_concurrency')),
          limit_units numeric(30,6) NOT NULL CHECK (limit_units > 0),
          reserved_units numeric(30,6) NOT NULL DEFAULT 0 CHECK (reserved_units >= 0 AND reserved_units <= limit_units),
          lease_fence bigint NOT NULL DEFAULT 0 CHECK (lease_fence >= 0),
          updated_at timestamptz NOT NULL DEFAULT clock_timestamp(),
          PRIMARY KEY (tenant_id,quota_name)
        );
        CREATE TABLE public.tenant_quota_reservations (
          reservation_uid uuid PRIMARY KEY DEFAULT gen_random_uuid(),
          tenant_id text NOT NULL REFERENCES public.tenants(tenant_id) ON DELETE RESTRICT,
          quota_name text NOT NULL,
          idempotency_key text NOT NULL CHECK (idempotency_key ~ '^[A-Za-z0-9._:-]{1,120}$'),
          request_digest char(64) NOT NULL,
          amount numeric(30,6) NOT NULL CHECK (amount > 0),
          lease_fence bigint NOT NULL CHECK (lease_fence > 0),
          status text NOT NULL CHECK (status IN ('reserved','settled','released')),
          created_at timestamptz NOT NULL DEFAULT clock_timestamp(),
          updated_at timestamptz NOT NULL DEFAULT clock_timestamp(),
          UNIQUE (tenant_id,quota_name,idempotency_key)
        );
        CREATE TABLE public.tenant_lifecycle_events (
          event_uid uuid PRIMARY KEY DEFAULT gen_random_uuid(),
          tenant_id text NOT NULL REFERENCES public.tenants(tenant_id) ON DELETE RESTRICT,
          idempotency_key text NOT NULL CHECK (idempotency_key ~ '^[A-Za-z0-9._:-]{1,120}$'),
          request_digest char(64) NOT NULL,
          prior_state text NOT NULL,
          next_state text NOT NULL,
          lease_fence bigint NOT NULL,
          approval_ref text,
          backup_digest char(64),
          retention_seconds bigint,
          created_at timestamptz NOT NULL DEFAULT clock_timestamp(),
          UNIQUE (tenant_id,idempotency_key)
        );
        CREATE TABLE public.tenant_audit_events (
          event_uid uuid PRIMARY KEY DEFAULT gen_random_uuid(),
          tenant_id text NOT NULL REFERENCES public.tenants(tenant_id) ON DELETE RESTRICT,
          event_type text NOT NULL CHECK (event_type ~ '^[a-z][a-z0-9_.-]{0,80}$'),
          payload_digest char(64) NOT NULL,
          lease_fence bigint NOT NULL DEFAULT 0,
          created_at timestamptz NOT NULL DEFAULT clock_timestamp()
        );
        CREATE TABLE public.tenant_resource_manifests (
          manifest_uid uuid PRIMARY KEY DEFAULT gen_random_uuid(),
          tenant_id text NOT NULL REFERENCES public.tenants(tenant_id) ON DELETE RESTRICT,
          resource_type text NOT NULL CHECK (resource_type IN ('object','graph','cache','index','keys','connectors','models','plugins','backup','audit_export')),
          namespace text NOT NULL CHECK (namespace ~ '^[a-z][a-z0-9-]{0,62}/[a-z_]+/v1/[a-z0-9][a-z0-9._-]{0,120}$'),
          digest char(64) NOT NULL,
          provider_enabled boolean NOT NULL DEFAULT false CHECK (provider_enabled=false),
          created_at timestamptz NOT NULL DEFAULT clock_timestamp(),
          UNIQUE (tenant_id,resource_type,namespace)
        );

        ALTER TABLE public.tenants OWNER TO dataops_tenant_foundation_owner;
        ALTER TABLE public.tenant_quotas OWNER TO dataops_tenant_foundation_owner;
        ALTER TABLE public.tenant_quota_reservations OWNER TO dataops_tenant_foundation_owner;
        ALTER TABLE public.tenant_lifecycle_events OWNER TO dataops_tenant_foundation_owner;
        ALTER TABLE public.tenant_audit_events OWNER TO dataops_tenant_foundation_owner;
        ALTER TABLE public.tenant_resource_manifests OWNER TO dataops_tenant_foundation_owner;
        ALTER TABLE public.tenant_quotas ENABLE ROW LEVEL SECURITY;
        ALTER TABLE public.tenant_quota_reservations ENABLE ROW LEVEL SECURITY;
        ALTER TABLE public.tenant_lifecycle_events ENABLE ROW LEVEL SECURITY;
        ALTER TABLE public.tenant_audit_events ENABLE ROW LEVEL SECURITY;
        ALTER TABLE public.tenant_resource_manifests ENABLE ROW LEVEL SECURITY;
        ALTER TABLE public.tenant_quotas FORCE ROW LEVEL SECURITY;
        ALTER TABLE public.tenant_quota_reservations FORCE ROW LEVEL SECURITY;
        ALTER TABLE public.tenant_lifecycle_events FORCE ROW LEVEL SECURITY;
        ALTER TABLE public.tenant_audit_events FORCE ROW LEVEL SECURITY;
        ALTER TABLE public.tenant_resource_manifests FORCE ROW LEVEL SECURITY;
        CREATE POLICY tenant_quota_scope ON public.tenant_quotas
          USING (tenant_id=current_setting('dataops.tenant_id',true))
          WITH CHECK (tenant_id=current_setting('dataops.tenant_id',true));
        CREATE POLICY tenant_quota_reservation_scope ON public.tenant_quota_reservations
          USING (tenant_id=current_setting('dataops.tenant_id',true))
          WITH CHECK (tenant_id=current_setting('dataops.tenant_id',true));
        CREATE POLICY tenant_lifecycle_scope ON public.tenant_lifecycle_events
          USING (tenant_id=current_setting('dataops.tenant_id',true))
          WITH CHECK (tenant_id=current_setting('dataops.tenant_id',true));
        CREATE POLICY tenant_audit_scope ON public.tenant_audit_events
          USING (tenant_id=current_setting('dataops.tenant_id',true))
          WITH CHECK (tenant_id=current_setting('dataops.tenant_id',true));
        CREATE POLICY tenant_manifest_scope ON public.tenant_resource_manifests
          USING (tenant_id=current_setting('dataops.tenant_id',true))
          WITH CHECK (tenant_id=current_setting('dataops.tenant_id',true));
        REVOKE ALL ON TABLE public.tenants,public.tenant_quotas,public.tenant_quota_reservations,
          public.tenant_lifecycle_events,public.tenant_audit_events,public.tenant_resource_manifests
          FROM PUBLIC,dataops_app_runtime;
        GRANT SELECT ON public.tenant_quotas,public.tenant_quota_reservations,public.tenant_lifecycle_events,
          public.tenant_audit_events,public.tenant_resource_manifests TO dataops_app_runtime;

        CREATE FUNCTION public.tenant_foundation_write(p_action text,p_payload jsonb)
        RETURNS jsonb LANGUAGE plpgsql SECURITY DEFINER SET search_path=pg_catalog,public AS $tenant_write$
        DECLARE v_tenant text; v_digest text; v_amount numeric(30,6); v_quota record; v_reservation record;
        DECLARE v_fence bigint; v_mode text;
        BEGIN
          IF p_action NOT IN ('provision','reserve_quota') OR jsonb_typeof(p_payload)<>'object' THEN
            RAISE EXCEPTION 'tenant_payload_closed';
          END IF;
          IF p_action='provision' THEN
            IF NOT (p_payload ?& ARRAY['tenant_id','delivery_mode','idempotency_key'])
               OR p_payload-ARRAY['tenant_id','delivery_mode','idempotency_key']<>'{}'::jsonb THEN RAISE EXCEPTION 'tenant_payload_closed'; END IF;
            v_tenant:=p_payload->>'tenant_id'; v_mode:=p_payload->>'delivery_mode';
            IF v_tenant !~ '^[a-z][a-z0-9-]{0,62}$' OR v_mode NOT IN ('private_single_tenant','shared_control_plane','saas')
               OR p_payload->>'idempotency_key' !~ '^[A-Za-z0-9._:-]{1,120}$' THEN RAISE EXCEPTION 'tenant_payload_invalid'; END IF;
            PERFORM set_config('dataops.tenant_id',v_tenant,true);
            INSERT INTO public.tenants(tenant_id,delivery_mode,state) VALUES(v_tenant,v_mode,'active') ON CONFLICT (tenant_id) DO NOTHING;
            INSERT INTO public.tenant_quotas(tenant_id,quota_name,limit_units) VALUES
              (v_tenant,'records',1),(v_tenant,'storage_objects',1),(v_tenant,'background_tasks',1),(v_tenant,'events',1),
              (v_tenant,'models',1),(v_tenant,'plugins',1),(v_tenant,'connectors',1),(v_tenant,'api_concurrency',1)
            ON CONFLICT (tenant_id,quota_name) DO NOTHING;
            INSERT INTO public.tenant_audit_events(tenant_id,event_type,payload_digest) VALUES
              (v_tenant,'tenant.provision',encode(sha256(convert_to(p_payload::text,'utf8')),'hex'));
            RETURN jsonb_build_object('tenant_id',v_tenant,'state','active','lease_fence',0);
          END IF;
          IF NOT (p_payload ?& ARRAY['tenant_id','quota_name','amount','idempotency_key'])
             OR p_payload-ARRAY['tenant_id','quota_name','amount','idempotency_key']<>'{}'::jsonb THEN RAISE EXCEPTION 'tenant_payload_closed'; END IF;
          v_tenant:=p_payload->>'tenant_id'; v_digest:=encode(sha256(convert_to(p_payload::text,'utf8')),'hex');
          IF v_tenant !~ '^[a-z][a-z0-9-]{0,62}$' OR p_payload->>'quota_name' NOT IN ('records','storage_objects','background_tasks','events','models','plugins','connectors','api_concurrency')
             OR p_payload->>'idempotency_key' !~ '^[A-Za-z0-9._:-]{1,120}$' OR p_payload->>'amount' !~ '^[0-9]+(\\.[0-9]{1,6})?$' THEN RAISE EXCEPTION 'tenant_payload_invalid'; END IF;
          v_amount:=(p_payload->>'amount')::numeric;
          IF v_amount<=0 THEN RAISE EXCEPTION 'tenant_payload_invalid'; END IF;
          PERFORM set_config('dataops.tenant_id',v_tenant,true);
          PERFORM pg_advisory_xact_lock(hashtext(v_tenant || ':' || (p_payload->>'quota_name')));
          SELECT * INTO v_reservation FROM public.tenant_quota_reservations WHERE tenant_id=v_tenant AND quota_name=p_payload->>'quota_name' AND idempotency_key=p_payload->>'idempotency_key' FOR UPDATE;
          IF FOUND THEN
            IF v_reservation.request_digest<>v_digest THEN RAISE EXCEPTION 'tenant_replay_conflict'; END IF;
            RETURN jsonb_build_object('tenant_id',v_tenant,'lease_fence',v_reservation.lease_fence,'status',v_reservation.status);
          END IF;
          SELECT * INTO v_quota FROM public.tenant_quotas WHERE tenant_id=v_tenant AND quota_name=p_payload->>'quota_name' FOR UPDATE;
          IF NOT FOUND OR NOT EXISTS(SELECT 1 FROM public.tenants WHERE tenant_id=v_tenant AND state='active') THEN RAISE EXCEPTION 'tenant_not_active'; END IF;
          IF v_quota.limit_units-v_quota.reserved_units<v_amount THEN RAISE EXCEPTION 'tenant_quota_exhausted'; END IF;
          UPDATE public.tenant_quotas SET reserved_units=reserved_units+v_amount,lease_fence=lease_fence+1,updated_at=clock_timestamp()
           WHERE tenant_id=v_tenant AND quota_name=p_payload->>'quota_name' AND lease_fence=v_quota.lease_fence RETURNING lease_fence INTO v_fence;
          IF NOT FOUND THEN RAISE EXCEPTION 'tenant_fence_conflict'; END IF;
          INSERT INTO public.tenant_quota_reservations(tenant_id,quota_name,idempotency_key,request_digest,amount,lease_fence,status)
            VALUES(v_tenant,p_payload->>'quota_name',p_payload->>'idempotency_key',v_digest,v_amount,v_fence,'reserved');
          INSERT INTO public.tenant_audit_events(tenant_id,event_type,payload_digest,lease_fence) VALUES(v_tenant,'quota.reserve',v_digest,v_fence);
          RETURN jsonb_build_object('tenant_id',v_tenant,'lease_fence',v_fence,'status','reserved');
        END; $tenant_write$;
        ALTER FUNCTION public.tenant_foundation_write(text,jsonb) OWNER TO dataops_tenant_foundation_owner;
        REVOKE ALL ON FUNCTION public.tenant_foundation_write(text,jsonb) FROM PUBLIC;
        GRANT EXECUTE ON FUNCTION public.tenant_foundation_write(text,jsonb) TO dataops_app_runtime;
        """
    )


def downgrade() -> None:
    op.execute(
        """
        REVOKE EXECUTE ON FUNCTION public.tenant_foundation_write(text,jsonb) FROM dataops_app_runtime;
        DROP FUNCTION IF EXISTS public.tenant_foundation_write(text,jsonb);
        DROP TABLE IF EXISTS public.tenant_resource_manifests;
        DROP TABLE IF EXISTS public.tenant_audit_events;
        DROP TABLE IF EXISTS public.tenant_lifecycle_events;
        DROP TABLE IF EXISTS public.tenant_quota_reservations;
        DROP TABLE IF EXISTS public.tenant_quotas;
        DROP TABLE IF EXISTS public.tenants;
        """
    )