DataOps-platform/migrations/versions/20260722_110_data_research_ontology.py

"""Add ontology control-plane state and immutable versions."""

from alembic import op


revision = "20260722_110"
down_revision = "20260722_105"
branch_labels = None
depends_on = None


def upgrade() -> None:
    op.execute(
        """
        CREATE TABLE IF NOT EXISTS public.ontologies (
            uid UUID PRIMARY KEY,
            code VARCHAR(120) NOT NULL UNIQUE,
            name VARCHAR(300) NOT NULL,
            owner_uid VARCHAR(100) NOT NULL,
            status VARCHAR(20) NOT NULL DEFAULT 'draft',
            draft_revision INTEGER NOT NULL DEFAULT 0,
            active_version_uid UUID,
            created_by VARCHAR(100),
            created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
        );
        CREATE TABLE IF NOT EXISTS public.ontology_versions (
            uid UUID PRIMARY KEY,
            ontology_uid UUID NOT NULL REFERENCES public.ontologies(uid),
            version INTEGER NOT NULL,
            parent_version_uid UUID REFERENCES public.ontology_versions(uid),
            status VARCHAR(20) NOT NULL
                CHECK (status IN ('draft','published','superseded')),
            graph_document JSONB NOT NULL,
            content_hash VARCHAR(64) NOT NULL,
            created_by VARCHAR(100),
            created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
            published_at TIMESTAMPTZ,
            UNIQUE (ontology_uid, version)
        );
        ALTER TABLE public.ontologies
            ADD CONSTRAINT fk_ontologies_active_version
            FOREIGN KEY (active_version_uid)
            REFERENCES public.ontology_versions(uid);
        CREATE TABLE IF NOT EXISTS public.ontology_domain_links (
            ontology_uid UUID NOT NULL REFERENCES public.ontologies(uid),
            domain_uid UUID NOT NULL,
            role VARCHAR(20) NOT NULL
                CHECK (role IN ('owner','contributor','consumer')),
            PRIMARY KEY (ontology_uid, domain_uid)
        );
        CREATE TABLE IF NOT EXISTS public.ontology_change_sets (
            uid UUID PRIMARY KEY,
            ontology_uid UUID NOT NULL REFERENCES public.ontologies(uid),
            base_version_uid UUID REFERENCES public.ontology_versions(uid),
            status VARCHAR(20) NOT NULL DEFAULT 'draft',
            changes JSONB NOT NULL DEFAULT '[]'::jsonb,
            decisions JSONB NOT NULL DEFAULT '[]'::jsonb,
            created_by VARCHAR(100),
            created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
        );
        CREATE TABLE IF NOT EXISTS public.ontology_publish_runs (
            uid UUID PRIMARY KEY,
            ontology_uid UUID NOT NULL REFERENCES public.ontologies(uid),
            version_uid UUID NOT NULL REFERENCES public.ontology_versions(uid),
            idempotency_key VARCHAR(128) NOT NULL UNIQUE,
            status VARCHAR(20) NOT NULL,
            validation_result JSONB NOT NULL DEFAULT '{}'::jsonb,
            actor_uid VARCHAR(100),
            created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
            finished_at TIMESTAMPTZ
        );
        CREATE INDEX IF NOT EXISTS idx_ontology_versions_ontology
            ON public.ontology_versions(ontology_uid, version DESC);
        """
    )


def downgrade() -> None:
    # Published semantic history is preserved during application rollback.
    pass