DataOps-platform/migrations/versions/20260729_270_governance_responsibilities.py

"""Add versioned and audited governance responsibility matrices."""

from alembic import op


revision = "20260729_270"
down_revision = "20260724_260"
branch_labels = None
depends_on = None


def upgrade() -> None:
    op.execute(
        """
        CREATE TABLE IF NOT EXISTS public.governance_responsibility_scopes (
            id UUID PRIMARY KEY,
            resource_type VARCHAR(40) NOT NULL
                CHECK (
                    resource_type IN (
                        'business_domain','device_asset','device_ontology',
                        'device_mapping','fault_classification','quality_issue'
                    )
                ),
            resource_uid VARCHAR(120) NOT NULL,
            revision INTEGER NOT NULL DEFAULT 0 CHECK (revision >= 0),
            updated_by UUID REFERENCES public.users(id) ON DELETE SET NULL,
            created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
            UNIQUE (resource_type, resource_uid)
        );

        CREATE TABLE IF NOT EXISTS public.governance_responsibility_assignments (
            id UUID PRIMARY KEY,
            scope_id UUID NOT NULL
                REFERENCES public.governance_responsibility_scopes(id)
                ON DELETE CASCADE,
            user_id UUID NOT NULL REFERENCES public.users(id) ON DELETE RESTRICT,
            responsibility_role VARCHAR(32) NOT NULL
                CHECK (
                    responsibility_role IN (
                        'domain_owner','data_steward',
                        'data_architect','asset_manager'
                    )
                ),
            raci_role VARCHAR(16) NOT NULL
                CHECK (
                    raci_role IN (
                        'responsible','accountable','consulted','informed'
                    )
                ),
            assigned_by UUID REFERENCES public.users(id) ON DELETE SET NULL,
            assigned_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
            UNIQUE (scope_id, user_id, responsibility_role, raci_role)
        );
        CREATE INDEX IF NOT EXISTS idx_governance_responsibility_user
            ON public.governance_responsibility_assignments(user_id);

        CREATE TABLE IF NOT EXISTS public.governance_responsibility_audit_events (
            id BIGSERIAL PRIMARY KEY,
            scope_id UUID REFERENCES public.governance_responsibility_scopes(id)
                ON DELETE SET NULL,
            resource_type VARCHAR(40) NOT NULL,
            resource_uid VARCHAR(120) NOT NULL,
            actor_uid UUID REFERENCES public.users(id) ON DELETE SET NULL,
            action VARCHAR(40) NOT NULL,
            before_state JSONB NOT NULL,
            after_state JSONB NOT NULL,
            created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
        );
        CREATE INDEX IF NOT EXISTS idx_governance_responsibility_audit_scope
            ON public.governance_responsibility_audit_events(
                resource_type, resource_uid, created_at DESC
            );
        """
    )


def downgrade() -> None:
    # Responsibility history is retained during application rollback.
    pass