DataOps-platform/deployment/migrations/versions/20260722_105_data_research_elements.py

"""Add versioned data-element governance and candidate decisions."""

from alembic import op


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


def upgrade() -> None:
    op.execute(
        """
        CREATE TABLE IF NOT EXISTS public.data_elements (
            uid UUID PRIMARY KEY,
            code VARCHAR(120) NOT NULL UNIQUE,
            current_version INTEGER NOT NULL DEFAULT 1,
            status VARCHAR(20) NOT NULL DEFAULT 'draft'
                CHECK (status IN ('draft','in_review','published','deprecated','retired')),
            business_domain_uids JSONB NOT NULL DEFAULT '[]'::jsonb,
            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.data_element_versions (
            uid UUID PRIMARY KEY,
            data_element_uid UUID NOT NULL
                REFERENCES public.data_elements(uid),
            version INTEGER NOT NULL,
            status VARCHAR(20) NOT NULL,
            snapshot JSONB NOT NULL,
            evidence_uids JSONB NOT NULL DEFAULT '[]'::jsonb,
            created_by VARCHAR(100),
            created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
            UNIQUE (data_element_uid, version)
        );

        CREATE TABLE IF NOT EXISTS public.candidate_decisions (
            uid UUID PRIMARY KEY,
            candidate_uid UUID NOT NULL
                REFERENCES public.extraction_candidates(uid),
            action VARCHAR(20) NOT NULL
                CHECK (action IN ('reuse','create','map','ignore')),
            data_element_uid UUID
                REFERENCES public.data_elements(uid),
            evidence_uids JSONB NOT NULL DEFAULT '[]'::jsonb,
            payload JSONB NOT NULL DEFAULT '{}'::jsonb,
            actor_uid VARCHAR(100),
            reason VARCHAR(1000),
            created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
            UNIQUE (candidate_uid)
        );

        CREATE INDEX IF NOT EXISTS idx_data_elements_status
            ON public.data_elements(status, updated_at);
        CREATE INDEX IF NOT EXISTS idx_data_element_versions_element
            ON public.data_element_versions(data_element_uid, version DESC);
        """
    )


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