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

"""Add governed device semantic codes, immutable versions, and reviews."""

from alembic import op

revision = "20260729_300"
down_revision = "20260729_290"
branch_labels = None
depends_on = None


def upgrade() -> None:
    op.execute(
        """
        CREATE TABLE public.device_semantic_codes (
            uid UUID PRIMARY KEY,
            ontology_uid UUID NOT NULL
                REFERENCES public.ontologies(uid) ON DELETE RESTRICT,
            code_type VARCHAR(20) NOT NULL
                CHECK (code_type IN ('fault','cause','action')),
            canonical_code VARCHAR(120) NOT NULL,
            canonical_name VARCHAR(300) NOT NULL,
            definition TEXT,
            status VARCHAR(20) NOT NULL DEFAULT 'draft'
                CHECK (
                    status IN (
                        'draft','in_review','published','rejected','retired'
                    )
                ),
            current_version INTEGER NOT NULL DEFAULT 1
                CHECK (current_version > 0),
            source_mappings JSONB NOT NULL DEFAULT '[]'::jsonb,
            evidence_uids JSONB NOT NULL DEFAULT '[]'::jsonb,
            suggestion_source VARCHAR(20) NOT NULL DEFAULT 'manual'
                CHECK (suggestion_source IN ('manual','rule','ai')),
            confidence DOUBLE PRECISION
                CHECK (
                    confidence IS NULL
                    OR (confidence >= 0 AND confidence <= 1)
                ),
            created_by VARCHAR(100),
            updated_by VARCHAR(100),
            created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
            UNIQUE (ontology_uid, code_type, canonical_code)
        );
        CREATE INDEX idx_device_semantic_codes_lookup
            ON public.device_semantic_codes(
                ontology_uid, code_type, status, canonical_code
            );
        CREATE INDEX idx_device_semantic_codes_updated
            ON public.device_semantic_codes(updated_at DESC);

        CREATE TABLE public.device_semantic_code_versions (
            uid UUID PRIMARY KEY,
            code_uid UUID NOT NULL
                REFERENCES public.device_semantic_codes(uid)
                ON DELETE CASCADE,
            version INTEGER NOT NULL CHECK (version > 0),
            snapshot JSONB NOT NULL,
            created_by VARCHAR(100),
            created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
            UNIQUE (code_uid, version)
        );
        CREATE INDEX idx_device_semantic_code_versions_created
            ON public.device_semantic_code_versions(
                code_uid, created_at DESC
            );

        CREATE TABLE public.device_semantic_code_reviews (
            uid UUID PRIMARY KEY,
            code_uid UUID NOT NULL
                REFERENCES public.device_semantic_codes(uid)
                ON DELETE CASCADE,
            version INTEGER NOT NULL CHECK (version > 0),
            decision VARCHAR(20) NOT NULL
                CHECK (decision IN ('approve','reject')),
            reason VARCHAR(1000) NOT NULL DEFAULT '',
            actor_uid VARCHAR(100) NOT NULL,
            created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
        );
        CREATE INDEX idx_device_semantic_code_reviews_created
            ON public.device_semantic_code_reviews(
                code_uid, created_at DESC
            );
        """
    )


def downgrade() -> None:
    # Governed code dictionaries and their approval evidence are retained.
    pass