DataOps-platform/migrations/versions/20260730_370_governance_domain_templates.py

"""Add generic governance object types and versioned domain templates."""

from alembic import op

revision = "20260730_370"
down_revision = "20260730_360"
branch_labels = None
depends_on = None


def upgrade() -> None:
    op.execute(
        """
        CREATE TABLE public.governance_domain_templates (
            uid UUID PRIMARY KEY,
            template_code VARCHAR(64) NOT NULL UNIQUE,
            name VARCHAR(200) NOT NULL,
            lifecycle_status VARCHAR(20) NOT NULL
                CHECK (lifecycle_status IN ('draft','active','retired')),
            current_version INTEGER NOT NULL CHECK (current_version >= 1),
            content_hash CHAR(64) NOT NULL,
            definition JSONB NOT NULL,
            created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
            CHECK (jsonb_typeof(definition) = 'object')
        );

        CREATE TABLE public.governance_domain_template_versions (
            uid UUID PRIMARY KEY,
            template_uid UUID NOT NULL
                REFERENCES public.governance_domain_templates(uid),
            version INTEGER NOT NULL CHECK (version >= 1),
            content_hash CHAR(64) NOT NULL,
            definition JSONB NOT NULL,
            actor_uid UUID NOT NULL REFERENCES public.users(id),
            created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
            UNIQUE (template_uid, version),
            CHECK (jsonb_typeof(definition) = 'object')
        );

        CREATE TABLE public.governance_object_types (
            uid UUID PRIMARY KEY,
            template_uid UUID NOT NULL
                REFERENCES public.governance_domain_templates(uid),
            type_code VARCHAR(64) NOT NULL,
            name VARCHAR(200) NOT NULL,
            lifecycle_status VARCHAR(20) NOT NULL
                CHECK (lifecycle_status IN ('draft','active','retired')),
            current_version INTEGER NOT NULL CHECK (current_version >= 1),
            stable_uid_prefix VARCHAR(16) NOT NULL,
            source_identity_fields JSONB NOT NULL,
            definition JSONB NOT NULL,
            created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
            UNIQUE (template_uid, type_code),
            CHECK (jsonb_typeof(source_identity_fields) = 'array'),
            CHECK (jsonb_array_length(source_identity_fields) > 0),
            CHECK (jsonb_typeof(definition) = 'object')
        );

        CREATE TABLE public.governance_domain_template_imports (
            uid UUID PRIMARY KEY,
            template_uid UUID NOT NULL
                REFERENCES public.governance_domain_templates(uid),
            operation VARCHAR(20) NOT NULL
                CHECK (operation IN ('import','rollback')),
            status VARCHAR(20) NOT NULL
                CHECK (status IN ('applied','failed')),
            version INTEGER NOT NULL CHECK (version >= 1),
            target_version INTEGER,
            before_state JSONB,
            after_state JSONB NOT NULL,
            diff JSONB NOT NULL,
            actor_uid UUID NOT NULL REFERENCES public.users(id),
            created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
            CHECK (before_state IS NULL OR jsonb_typeof(before_state) = 'object'),
            CHECK (jsonb_typeof(after_state) = 'object'),
            CHECK (jsonb_typeof(diff) = 'object')
        );

        CREATE INDEX idx_governance_domain_template_versions_created
            ON public.governance_domain_template_versions(
                template_uid, version DESC
            );
        CREATE INDEX idx_governance_object_types_status
            ON public.governance_object_types(
                template_uid, lifecycle_status, type_code
            );
        CREATE INDEX idx_governance_domain_template_imports_created
            ON public.governance_domain_template_imports(
                template_uid, created_at DESC
            );
        """
    )


def downgrade() -> None:
    raise RuntimeError(
        "governance domain template history is append-only; "
        "schema downgrade requires an approved archival migration"
    )