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

"""Add immutable device quality policies, runs, results, and evidence."""

from alembic import op

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


def upgrade() -> None:
    op.execute(
        """
        CREATE TABLE public.device_quality_profiles (
            uid UUID PRIMARY KEY,
            code VARCHAR(120) NOT NULL UNIQUE,
            name VARCHAR(300) NOT NULL,
            created_by VARCHAR(120) NOT NULL,
            created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
        );

        CREATE TABLE public.device_quality_profile_versions (
            uid UUID PRIMARY KEY,
            profile_uid UUID NOT NULL
                REFERENCES public.device_quality_profiles(uid)
                ON DELETE RESTRICT,
            version INTEGER NOT NULL CHECK (version > 0),
            status VARCHAR(20) NOT NULL DEFAULT 'draft'
                CHECK (status IN ('draft','published','superseded')),
            rules JSONB NOT NULL,
            content_hash CHAR(64) NOT NULL,
            created_by VARCHAR(120) NOT NULL,
            published_by VARCHAR(120),
            created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
            published_at TIMESTAMPTZ,
            UNIQUE (profile_uid, version),
            UNIQUE (profile_uid, content_hash)
        );
        CREATE UNIQUE INDEX uq_device_quality_active_profile
            ON public.device_quality_profile_versions(profile_uid)
            WHERE status = 'published';
        CREATE INDEX idx_device_quality_profile_versions
            ON public.device_quality_profile_versions(
                profile_uid, version DESC
            );

        CREATE TABLE public.device_quality_runs (
            uid UUID PRIMARY KEY,
            policy_version_uid UUID NOT NULL
                REFERENCES public.device_quality_profile_versions(uid)
                ON DELETE RESTRICT,
            policy_hash CHAR(64) NOT NULL,
            source_uid UUID
                REFERENCES public.ingestion_sources(uid)
                ON DELETE RESTRICT,
            status VARCHAR(20) NOT NULL
                CHECK (status IN ('success','failed')),
            total_assets INTEGER NOT NULL CHECK (total_assets >= 0),
            total_violations INTEGER NOT NULL CHECK (total_violations >= 0),
            score DOUBLE PRECISION NOT NULL
                CHECK (score >= 0 AND score <= 100),
            created_by VARCHAR(120) NOT NULL,
            created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
        );
        CREATE INDEX idx_device_quality_runs_created
            ON public.device_quality_runs(created_at DESC, uid);
        CREATE INDEX idx_device_quality_runs_policy
            ON public.device_quality_runs(policy_version_uid, created_at DESC);

        CREATE TABLE public.device_quality_rule_results (
            uid UUID PRIMARY KEY,
            run_uid UUID NOT NULL
                REFERENCES public.device_quality_runs(uid)
                ON DELETE CASCADE,
            rule_code VARCHAR(120) NOT NULL,
            severity VARCHAR(20) NOT NULL
                CHECK (severity IN ('info','warning','error','critical')),
            weight DOUBLE PRECISION NOT NULL
                CHECK (weight >= 0 AND weight <= 100),
            status VARCHAR(30) NOT NULL
                CHECK (status IN ('passed','violated','not_applicable')),
            evaluated_count INTEGER NOT NULL CHECK (evaluated_count >= 0),
            violation_count INTEGER NOT NULL CHECK (
                violation_count >= 0
                AND violation_count <= evaluated_count
            ),
            sampled_count INTEGER NOT NULL CHECK (
                sampled_count >= 0
                AND sampled_count <= violation_count
            ),
            pass_rate DOUBLE PRECISION NOT NULL
                CHECK (pass_rate >= 0 AND pass_rate <= 1),
            weighted_score DOUBLE PRECISION NOT NULL CHECK (
                weighted_score >= 0 AND weighted_score <= weight
            ),
            created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
            UNIQUE (run_uid, rule_code)
        );
        CREATE INDEX idx_device_quality_rule_results
            ON public.device_quality_rule_results(
                run_uid, severity, rule_code
            );

        CREATE TABLE public.device_quality_violation_samples (
            uid UUID PRIMARY KEY,
            run_uid UUID NOT NULL
                REFERENCES public.device_quality_runs(uid)
                ON DELETE CASCADE,
            rule_code VARCHAR(120) NOT NULL,
            severity VARCHAR(20) NOT NULL
                CHECK (severity IN ('info','warning','error','critical')),
            asset_uid UUID NOT NULL
                REFERENCES public.device_assets(uid)
                ON DELETE RESTRICT,
            field_name VARCHAR(300) NOT NULL,
            source_uid UUID
                REFERENCES public.ingestion_sources(uid)
                ON DELETE RESTRICT,
            source_mapping_uid UUID
                REFERENCES public.device_asset_source_mappings(uid)
                ON DELETE RESTRICT,
            message VARCHAR(1000) NOT NULL,
            evidence JSONB NOT NULL,
            created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
            expires_at TIMESTAMPTZ NOT NULL,
            UNIQUE (run_uid, rule_code, asset_uid, field_name)
        );
        CREATE INDEX idx_device_quality_violation_run
            ON public.device_quality_violation_samples(
                run_uid, rule_code, asset_uid
            );
        CREATE INDEX idx_device_quality_violation_retention
            ON public.device_quality_violation_samples(expires_at);

        CREATE TABLE public.device_quality_asset_scores (
            uid UUID PRIMARY KEY,
            run_uid UUID NOT NULL
                REFERENCES public.device_quality_runs(uid)
                ON DELETE CASCADE,
            asset_uid UUID NOT NULL
                REFERENCES public.device_assets(uid)
                ON DELETE RESTRICT,
            asset_type VARCHAR(40) NOT NULL,
            status VARCHAR(30) NOT NULL
                CHECK (status IN ('passed','violated','not_applicable')),
            evaluated_rule_count INTEGER NOT NULL
                CHECK (evaluated_rule_count >= 0),
            violation_count INTEGER NOT NULL CHECK (
                violation_count >= 0
                AND violation_count <= evaluated_rule_count
            ),
            score DOUBLE PRECISION NOT NULL
                CHECK (score >= 0 AND score <= 100),
            created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
            UNIQUE (run_uid, asset_uid)
        );
        CREATE INDEX idx_device_quality_asset_scores
            ON public.device_quality_asset_scores(
                run_uid, score, asset_uid
            );
        """
    )


def downgrade() -> None:
    # Quality policies, runs, scores, and violation evidence are retained.
    pass