DataOps-platform/migrations/versions/20260723_160_rule_execution_evidence.py

"""Make governed rule execution evidence durable and idempotent."""

from alembic import op

revision = "20260723_160"
down_revision = "20260723_150"
branch_labels = None
depends_on = None


def upgrade() -> None:
    op.execute(
        """
        ALTER TABLE public.rule_runs
            ADD COLUMN evidence_key CHAR(64),
            ADD COLUMN commit_outcome VARCHAR(30) NOT NULL
                DEFAULT 'not_applicable'
                CHECK (commit_outcome IN (
                    'not_applicable','not_committed','committed','unknown'
                )),
            ADD COLUMN public_result JSONB,
            ADD COLUMN failure_code VARCHAR(100),
            ADD COLUMN updated_at TIMESTAMPTZ NOT NULL
                DEFAULT CURRENT_TIMESTAMP;

        ALTER TABLE public.rule_runs
            ADD CONSTRAINT rule_runs_evidence_key UNIQUE (evidence_key);

        ALTER TABLE public.rule_violation_samples
            ADD COLUMN artifact_digest CHAR(64),
            ADD COLUMN schema_hash CHAR(64),
            ADD COLUMN handoff_status VARCHAR(20) NOT NULL
                DEFAULT 'legacy'
                CHECK (handoff_status IN (
                    'legacy','pending','ready','failed'
                )),
            ADD COLUMN failure_code VARCHAR(100),
            ADD COLUMN updated_at TIMESTAMPTZ NOT NULL
                DEFAULT CURRENT_TIMESTAMP;

        DO $$
        BEGIN
            IF EXISTS (
                SELECT 1
                FROM public.rule_violation_samples
                GROUP BY rule_run_id
                HAVING COUNT(*) > 1
            ) THEN
                RAISE EXCEPTION
                    'violation sample migration found duplicate rule_run_id';
            END IF;
            IF EXISTS (
                SELECT 1
                FROM public.rule_violation_samples
                WHERE sample_count > 100
            ) THEN
                RAISE EXCEPTION
                    'violation sample migration found rows above 100';
            END IF;
        END
        $$;

        ALTER TABLE public.rule_violation_samples
            DROP CONSTRAINT IF EXISTS
                rule_violation_samples_sample_count_check,
            ADD CONSTRAINT rule_violation_samples_sample_count_check
                CHECK (sample_count >= 0 AND sample_count <= 100),
            ADD CONSTRAINT rule_violation_samples_rule_run_key
                UNIQUE (rule_run_id);

        CREATE INDEX idx_rule_violation_samples_retention
            ON public.rule_violation_samples
            (handoff_status, expires_at, id);
        """
    )


def downgrade() -> None:
    raise RuntimeError(
        "rule execution evidence is forward-only and cannot be downgraded"
    )