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

"""Add governed device-quality issue remediation lifecycle."""

from alembic import op

revision = "20260729_340"
down_revision = "20260729_330"
branch_labels = None
depends_on = None


def upgrade() -> None:
    op.execute(
        """
        CREATE TABLE public.device_quality_issues (
            uid UUID PRIMARY KEY,
            issue_code VARCHAR(32) NOT NULL UNIQUE,
            source_violation_uid UUID NOT NULL,
            source_run_uid UUID NOT NULL
                REFERENCES public.device_quality_runs(uid) ON DELETE RESTRICT,
            rule_code VARCHAR(120) NOT NULL,
            severity VARCHAR(20) NOT NULL
                CHECK (severity IN ('info','warning','error','critical')),
            priority VARCHAR(20) NOT NULL
                CHECK (priority IN ('low','medium','high','critical')),
            asset_uid UUID NOT NULL
                REFERENCES public.device_assets(uid) ON DELETE RESTRICT,
            field_name VARCHAR(300) NOT NULL,
            source_uid UUID,
            source_mapping_uid UUID,
            message VARCHAR(1000) NOT NULL,
            evidence JSONB NOT NULL,
            recurrence_key CHAR(64) NOT NULL,
            occurrence_number INTEGER NOT NULL CHECK (occurrence_number > 0),
            status VARCHAR(30) NOT NULL DEFAULT 'open'
                CHECK (
                    status IN (
                        'open','assigned','in_progress',
                        'pending_review','closed'
                    )
                ),
            assignee_uid UUID
                REFERENCES public.users(id) ON DELETE RESTRICT,
            due_at TIMESTAMPTZ,
            current_version INTEGER NOT NULL DEFAULT 1
                CHECK (current_version > 0),
            created_by UUID NOT NULL
                REFERENCES public.users(id) ON DELETE RESTRICT,
            updated_by UUID NOT NULL
                REFERENCES public.users(id) ON DELETE RESTRICT,
            created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
            closed_at TIMESTAMPTZ
        );
        CREATE UNIQUE INDEX uq_device_quality_open_recurrence
            ON public.device_quality_issues(recurrence_key)
            WHERE status <> 'closed';
        CREATE INDEX idx_device_quality_issues_worklist
            ON public.device_quality_issues(
                status, priority, due_at, created_at DESC
            );
        CREATE INDEX idx_device_quality_issues_assignee
            ON public.device_quality_issues(
                assignee_uid, status, due_at
            );
        CREATE INDEX idx_device_quality_issues_recurrence
            ON public.device_quality_issues(
                recurrence_key, occurrence_number DESC
            );
        CREATE INDEX idx_device_quality_issues_source
            ON public.device_quality_issues(
                source_run_uid, source_violation_uid
            );

        CREATE TABLE public.device_quality_issue_remediations (
            uid UUID PRIMARY KEY,
            issue_uid UUID NOT NULL
                REFERENCES public.device_quality_issues(uid) ON DELETE CASCADE,
            round_number INTEGER NOT NULL CHECK (round_number > 0),
            summary VARCHAR(2000) NOT NULL,
            evidence_refs JSONB NOT NULL,
            submitted_by UUID NOT NULL
                REFERENCES public.users(id) ON DELETE RESTRICT,
            submitted_at TIMESTAMPTZ NOT NULL,
            review_status VARCHAR(20) NOT NULL DEFAULT 'pending'
                CHECK (review_status IN ('pending','passed','failed')),
            reviewed_by UUID
                REFERENCES public.users(id) ON DELETE RESTRICT,
            reviewed_at TIMESTAMPTZ,
            review_note VARCHAR(2000),
            verification_run_uid UUID
                REFERENCES public.device_quality_runs(uid) ON DELETE RESTRICT,
            UNIQUE (issue_uid, round_number)
        );
        CREATE INDEX idx_device_quality_issue_remediations
            ON public.device_quality_issue_remediations(
                issue_uid, round_number DESC
            );

        CREATE TABLE public.device_quality_issue_timeline (
            uid UUID PRIMARY KEY,
            issue_uid UUID NOT NULL
                REFERENCES public.device_quality_issues(uid) ON DELETE CASCADE,
            action VARCHAR(40) NOT NULL,
            from_status VARCHAR(30),
            to_status VARCHAR(30) NOT NULL
                CHECK (
                    to_status IN (
                        'open','assigned','in_progress',
                        'pending_review','closed'
                    )
                ),
            actor_uid UUID NOT NULL
                REFERENCES public.users(id) ON DELETE RESTRICT,
            note VARCHAR(2000),
            payload JSONB NOT NULL,
            created_at TIMESTAMPTZ NOT NULL
        );
        CREATE INDEX idx_device_quality_issue_timeline
            ON public.device_quality_issue_timeline(
                issue_uid, created_at, uid
            );
        """
    )


def downgrade() -> None:
    # Remediation and review evidence is retained during application rollback.
    pass