DataOps-platform/migrations/versions/20260801_420_unified_responsibilities.py

"""Add hierarchical, delegated and policy-driven governance responsibility."""

from alembic import op


revision = "20260801_420"
down_revision = "20260731_410"
branch_labels = None
depends_on = None


def upgrade() -> None:
    op.execute(
        """
        ALTER TABLE public.governance_responsibility_scopes
            DROP CONSTRAINT IF EXISTS
                governance_responsibility_scopes_resource_type_check;
        ALTER TABLE public.governance_responsibility_scopes
            ADD CONSTRAINT
                governance_responsibility_scopes_resource_type_check
            CHECK (
                resource_type IN (
                    'business_domain','device_asset','device_ontology',
                    'device_mapping','device_quality','fault_classification',
                    'quality_issue','organization','data_asset','semantic_term',
                    'data_standard','quality_policy','data_product','agent'
                )
            ) NOT VALID;
        ALTER TABLE public.governance_responsibility_scopes
            VALIDATE CONSTRAINT
                governance_responsibility_scopes_resource_type_check;

        ALTER TABLE public.governance_responsibility_assignments
            DROP CONSTRAINT IF EXISTS
                governance_responsibility_assignments_responsibility_role_check;
        ALTER TABLE public.governance_responsibility_assignments
            ADD CONSTRAINT
                governance_responsibility_assignments_responsibility_role_check
            CHECK (
                responsibility_role IN (
                    'organization_owner','domain_owner','data_steward',
                    'data_architect','asset_manager','term_steward',
                    'standard_owner','quality_owner','product_owner','agent_owner'
                )
            ) NOT VALID;
        ALTER TABLE public.governance_responsibility_assignments
            VALIDATE CONSTRAINT
                governance_responsibility_assignments_responsibility_role_check;

        CREATE TABLE public.governance_responsibility_hierarchy (
            uid UUID PRIMARY KEY,
            resource_type VARCHAR(40) NOT NULL,
            resource_uid VARCHAR(120) NOT NULL,
            parent_type VARCHAR(40) NOT NULL,
            parent_uid VARCHAR(120) NOT NULL,
            revision INTEGER NOT NULL DEFAULT 1 CHECK (revision > 0),
            updated_by UUID NOT NULL REFERENCES public.users(id),
            created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
            UNIQUE (resource_type, resource_uid),
            CHECK (
                resource_type IN (
                    'organization','business_domain','data_asset','semantic_term',
                    'data_standard','quality_policy','data_product','agent'
                )
            ),
            CHECK (
                parent_type IN (
                    'organization','business_domain','data_asset','semantic_term',
                    'data_standard','quality_policy','data_product','agent'
                )
            ),
            CHECK (
                resource_type <> parent_type OR resource_uid <> parent_uid
            )
        );
        CREATE INDEX idx_governance_responsibility_hierarchy_parent
            ON public.governance_responsibility_hierarchy(
                parent_type, parent_uid
            );

        CREATE TABLE public.governance_responsibility_delegations (
            uid UUID PRIMARY KEY,
            source_user_uid UUID NOT NULL REFERENCES public.users(id),
            delegate_user_uid UUID NOT NULL REFERENCES public.users(id),
            scope_type VARCHAR(40),
            scope_uid VARCHAR(120),
            responsibility_role VARCHAR(40),
            delegation_type VARCHAR(30) NOT NULL CHECK (
                delegation_type IN ('temporary','departure_transfer')
            ),
            starts_at TIMESTAMPTZ NOT NULL,
            ends_at TIMESTAMPTZ,
            reason VARCHAR(500) NOT NULL,
            status VARCHAR(20) NOT NULL CHECK (
                status IN ('active','revoked','expired')
            ),
            current_version INTEGER NOT NULL DEFAULT 1 CHECK (
                current_version > 0
            ),
            created_by UUID NOT NULL REFERENCES public.users(id),
            updated_by UUID NOT NULL REFERENCES public.users(id),
            created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
            CHECK (source_user_uid <> delegate_user_uid),
            CHECK ((scope_type IS NULL) = (scope_uid IS NULL)),
            CHECK (
                scope_type IS NULL OR scope_type IN (
                    'organization','business_domain','data_asset','semantic_term',
                    'data_standard','quality_policy','data_product','agent'
                )
            ),
            CHECK (
                responsibility_role IS NULL OR responsibility_role IN (
                    'organization_owner','domain_owner','data_steward',
                    'data_architect','asset_manager','term_steward',
                    'standard_owner','quality_owner','product_owner','agent_owner'
                )
            ),
            CHECK (
                (delegation_type = 'temporary' AND ends_at > starts_at)
                OR
                (delegation_type = 'departure_transfer' AND ends_at IS NULL)
            )
        );
        CREATE UNIQUE INDEX uq_governance_responsibility_active_delegation
            ON public.governance_responsibility_delegations(
                source_user_uid,
                COALESCE(scope_type, ''), COALESCE(scope_uid, ''),
                COALESCE(responsibility_role, '')
            ) WHERE status = 'active';
        CREATE INDEX idx_governance_responsibility_delegate
            ON public.governance_responsibility_delegations(
                delegate_user_uid, status, ends_at
            );

        CREATE TABLE public.governance_responsibility_policies (
            uid UUID PRIMARY KEY,
            code VARCHAR(120) NOT NULL UNIQUE,
            name VARCHAR(300) NOT NULL,
            policy_type VARCHAR(30) NOT NULL CHECK (
                policy_type IN ('central_policy','joint_review')
            ),
            scope_type VARCHAR(40) NOT NULL CHECK (
                scope_type IN (
                    'organization','business_domain','data_asset','semantic_term',
                    'data_standard','quality_policy','data_product','agent'
                )
            ),
            scope_uid VARCHAR(120) NOT NULL,
            status VARCHAR(20) NOT NULL CHECK (
                status IN ('draft','published','retired')
            ),
            current_version INTEGER NOT NULL DEFAULT 1 CHECK (
                current_version > 0
            ),
            active_version_uid UUID,
            created_by UUID NOT NULL REFERENCES public.users(id),
            created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
        );

        CREATE TABLE public.governance_responsibility_policy_versions (
            uid UUID PRIMARY KEY,
            policy_uid UUID NOT NULL
                REFERENCES public.governance_responsibility_policies(uid),
            version INTEGER NOT NULL CHECK (version > 0),
            status VARCHAR(20) NOT NULL CHECK (
                status IN ('draft','published','superseded')
            ),
            definition JSONB NOT NULL,
            created_by UUID NOT NULL REFERENCES public.users(id),
            created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
            published_by UUID REFERENCES public.users(id),
            published_at TIMESTAMPTZ,
            UNIQUE (policy_uid, version),
            CHECK (jsonb_typeof(definition) = 'object')
        );
        ALTER TABLE public.governance_responsibility_policies
            ADD CONSTRAINT governance_responsibility_active_version_fk
            FOREIGN KEY (active_version_uid)
            REFERENCES public.governance_responsibility_policy_versions(uid);
        CREATE UNIQUE INDEX uq_governance_responsibility_published_version
            ON public.governance_responsibility_policy_versions(policy_uid)
            WHERE status = 'published';
        CREATE INDEX idx_governance_responsibility_policy_scope
            ON public.governance_responsibility_policies(
                scope_type, scope_uid, status
            );
        """
    )


def downgrade() -> None:
    raise RuntimeError(
        "responsibility delegation and policy evidence is retained; "
        "downgrade requires an approved archival migration"
    )