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

"""Add governed device entity candidates, merges, and rollbacks."""

from alembic import op

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


def upgrade() -> None:
    op.execute(
        """
        CREATE TABLE public.device_entity_match_candidates (
            uid UUID PRIMARY KEY,
            left_asset_uid UUID NOT NULL
                REFERENCES public.device_assets(uid) ON DELETE RESTRICT,
            right_asset_uid UUID NOT NULL
                REFERENCES public.device_assets(uid) ON DELETE RESTRICT,
            canonical_asset_uid UUID
                REFERENCES public.device_assets(uid) ON DELETE RESTRICT,
            status VARCHAR(20) NOT NULL DEFAULT 'pending'
                CHECK (
                    status IN (
                        'pending','merged','rejected','rolled_back'
                    )
                ),
            suggestion_source VARCHAR(20) NOT NULL
                CHECK (suggestion_source IN ('rule','ai','manual')),
            confidence DOUBLE PRECISION NOT NULL
                CHECK (confidence >= 0 AND confidence <= 1),
            explanation JSONB NOT NULL DEFAULT '[]'::jsonb,
            evidence_uids JSONB NOT NULL DEFAULT '[]'::jsonb,
            model_provider VARCHAR(200),
            model_name VARCHAR(200),
            current_version INTEGER NOT NULL DEFAULT 1
                CHECK (current_version > 0),
            created_by VARCHAR(100),
            reviewed_by VARCHAR(100),
            created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
            CHECK (left_asset_uid <> right_asset_uid)
        );
        CREATE UNIQUE INDEX uq_device_entity_open_pair
            ON public.device_entity_match_candidates(
                left_asset_uid, right_asset_uid
            )
            WHERE status IN ('pending','merged');
        CREATE INDEX idx_device_entity_candidates_status
            ON public.device_entity_match_candidates(
                status, suggestion_source, updated_at DESC
            );

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

        CREATE TABLE public.device_entity_merge_events (
            uid UUID PRIMARY KEY,
            candidate_uid UUID NOT NULL UNIQUE
                REFERENCES public.device_entity_match_candidates(uid)
                ON DELETE CASCADE,
            canonical_asset_uid UUID NOT NULL
                REFERENCES public.device_assets(uid) ON DELETE RESTRICT,
            member_asset_uid UUID NOT NULL
                REFERENCES public.device_assets(uid) ON DELETE RESTRICT,
            review_uid UUID NOT NULL
                REFERENCES public.device_entity_match_reviews(uid)
                ON DELETE RESTRICT,
            snapshot JSONB NOT NULL,
            actor_uid VARCHAR(100) NOT NULL,
            created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
        );
        CREATE INDEX idx_device_entity_merge_canonical
            ON public.device_entity_merge_events(
                canonical_asset_uid, created_at DESC
            );
        CREATE INDEX idx_device_entity_merge_member
            ON public.device_entity_merge_events(
                member_asset_uid, created_at DESC
            );

        CREATE TABLE public.device_entity_merge_rollbacks (
            uid UUID PRIMARY KEY,
            merge_uid UUID NOT NULL UNIQUE
                REFERENCES public.device_entity_merge_events(uid)
                ON DELETE CASCADE,
            candidate_uid UUID NOT NULL
                REFERENCES public.device_entity_match_candidates(uid)
                ON DELETE CASCADE,
            reason VARCHAR(1000) NOT NULL,
            snapshot JSONB NOT NULL,
            actor_uid VARCHAR(100) NOT NULL,
            created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
        );
        CREATE INDEX idx_device_entity_rollbacks_candidate
            ON public.device_entity_merge_rollbacks(
                candidate_uid, created_at DESC
            );
        """
    )


def downgrade() -> None:
    # Candidate, review, merge, and rollback evidence is retained.
    pass