life-echo/api/alembic/versions/0008_eval_regression_platform.py

"""内部回归评测平台表（eval_*）

Revision ID: 0008_eval_regression_platform
Revises: 0007_drop_chunk_content_tsv
"""

from typing import Sequence, Union

import sqlalchemy as sa
from sqlalchemy.dialects import postgresql

from alembic import op

revision: str = "0008_eval_regression_platform"
down_revision: Union[str, None] = "0007_drop_chunk_content_tsv"
branch_labels: Union[str, Sequence[str], None] = None
depends_on: Union[str, Sequence[str], None] = None


def _has_table(table_name: str) -> bool:
    bind = op.get_bind()
    inspector = sa.inspect(bind)
    return inspector.has_table(table_name)


def _index_names(table_name: str) -> set[str]:
    bind = op.get_bind()
    inspector = sa.inspect(bind)
    return {index["name"] for index in inspector.get_indexes(table_name)}


def upgrade() -> None:
    if not _has_table("eval_regression_sets"):
        op.create_table(
            "eval_regression_sets",
            sa.Column("id", sa.String(), nullable=False),
            sa.Column("name", sa.String(), nullable=False),
            sa.Column("description", sa.Text(), nullable=True),
            sa.Column(
                "created_at",
                sa.DateTime(timezone=True),
                server_default=sa.text("now()"),
                nullable=False,
            ),
            sa.PrimaryKeyConstraint("id"),
        )
    if not _has_table("eval_versions"):
        op.create_table(
            "eval_versions",
            sa.Column("id", sa.String(), nullable=False),
            sa.Column("name", sa.String(), nullable=False),
            sa.Column("runner_kind", sa.String(), nullable=False),
            sa.Column(
                "config_json", postgresql.JSONB(astext_type=sa.Text()), nullable=True
            ),
            sa.Column(
                "created_at",
                sa.DateTime(timezone=True),
                server_default=sa.text("now()"),
                nullable=False,
            ),
            sa.PrimaryKeyConstraint("id"),
        )
    if not _has_table("eval_cases"):
        op.create_table(
            "eval_cases",
            sa.Column("id", sa.String(), nullable=False),
            sa.Column("regression_set_id", sa.String(), nullable=False),
            sa.Column("source_conversation_id", sa.String(), nullable=True),
            sa.Column("source_user_id", sa.String(), nullable=True),
            sa.Column("title", sa.String(), nullable=True),
            sa.Column(
                "user_utterances",
                postgresql.JSONB(astext_type=sa.Text()),
                nullable=False,
            ),
            sa.Column("reference_memoir_markdown", sa.Text(), nullable=True),
            sa.Column(
                "is_protected",
                sa.Boolean(),
                server_default=sa.text("false"),
                nullable=False,
            ),
            sa.Column("meta", postgresql.JSONB(astext_type=sa.Text()), nullable=True),
            sa.Column(
                "created_at",
                sa.DateTime(timezone=True),
                server_default=sa.text("now()"),
                nullable=False,
            ),
            sa.ForeignKeyConstraint(
                ["regression_set_id"],
                ["eval_regression_sets.id"],
                ondelete="CASCADE",
            ),
            sa.PrimaryKeyConstraint("id"),
        )
    indexes = _index_names("eval_cases")
    if "ix_eval_cases_source_conversation_id" not in indexes:
        op.create_index(
            "ix_eval_cases_source_conversation_id",
            "eval_cases",
            ["source_conversation_id"],
            unique=False,
        )
    if "ix_eval_cases_source_user_id" not in indexes:
        op.create_index(
            "ix_eval_cases_source_user_id",
            "eval_cases",
            ["source_user_id"],
            unique=False,
        )
    if not _has_table("eval_experiments"):
        op.create_table(
            "eval_experiments",
            sa.Column("id", sa.String(), nullable=False),
            sa.Column("name", sa.String(), nullable=False),
            sa.Column("regression_set_id", sa.String(), nullable=False),
            sa.Column("baseline_version_id", sa.String(), nullable=False),
            sa.Column("candidate_version_id", sa.String(), nullable=False),
            sa.Column("rubric_pack", sa.String(), nullable=False),
            sa.Column(
                "composite_weights_json",
                postgresql.JSONB(astext_type=sa.Text()),
                nullable=True,
            ),
            sa.Column("status", sa.String(), nullable=False),
            sa.Column("error_message", sa.Text(), nullable=True),
            sa.Column(
                "created_at",
                sa.DateTime(timezone=True),
                server_default=sa.text("now()"),
                nullable=False,
            ),
            sa.Column("completed_at", sa.DateTime(timezone=True), nullable=True),
            sa.ForeignKeyConstraint(
                ["baseline_version_id"],
                ["eval_versions.id"],
            ),
            sa.ForeignKeyConstraint(
                ["candidate_version_id"],
                ["eval_versions.id"],
            ),
            sa.ForeignKeyConstraint(
                ["regression_set_id"],
                ["eval_regression_sets.id"],
            ),
            sa.PrimaryKeyConstraint("id"),
        )
    if not _has_table("eval_runs"):
        op.create_table(
            "eval_runs",
            sa.Column("id", sa.String(), nullable=False),
            sa.Column("experiment_id", sa.String(), nullable=False),
            sa.Column("case_id", sa.String(), nullable=False),
            sa.Column("side", sa.String(), nullable=False),
            sa.Column("status", sa.String(), nullable=False),
            sa.Column("error_message", sa.Text(), nullable=True),
            sa.Column("memoir_markdown", sa.Text(), nullable=True),
            sa.Column("conversation_score_total", sa.Float(), nullable=True),
            sa.Column("memoir_score_total", sa.Float(), nullable=True),
            sa.Column("composite_score", sa.Float(), nullable=True),
            sa.Column(
                "judge_bundle_json",
                postgresql.JSONB(astext_type=sa.Text()),
                nullable=True,
            ),
            sa.Column("started_at", sa.DateTime(timezone=True), nullable=True),
            sa.Column("completed_at", sa.DateTime(timezone=True), nullable=True),
            sa.ForeignKeyConstraint(
                ["case_id"],
                ["eval_cases.id"],
            ),
            sa.ForeignKeyConstraint(
                ["experiment_id"],
                ["eval_experiments.id"],
                ondelete="CASCADE",
            ),
            sa.PrimaryKeyConstraint("id"),
            sa.UniqueConstraint(
                "experiment_id",
                "case_id",
                "side",
                name="uq_eval_run_experiment_case_side",
            ),
        )
    if not _has_table("eval_run_turns"):
        op.create_table(
            "eval_run_turns",
            sa.Column("id", sa.String(), nullable=False),
            sa.Column("run_id", sa.String(), nullable=False),
            sa.Column("turn_index", sa.Integer(), nullable=False),
            sa.Column("user_utterance", sa.Text(), nullable=False),
            sa.Column("assistant_reply", sa.Text(), nullable=True),
            sa.Column("duration_ms", sa.Integer(), nullable=True),
            sa.Column(
                "judge_scores_json",
                postgresql.JSONB(astext_type=sa.Text()),
                nullable=True,
            ),
            sa.Column("judge_rationale", sa.Text(), nullable=True),
            sa.ForeignKeyConstraint(
                ["run_id"],
                ["eval_runs.id"],
                ondelete="CASCADE",
            ),
            sa.PrimaryKeyConstraint("id"),
            sa.UniqueConstraint("run_id", "turn_index", name="uq_eval_run_turn_index"),
        )
    if not _has_table("eval_gate_verdicts"):
        op.create_table(
            "eval_gate_verdicts",
            sa.Column("id", sa.String(), nullable=False),
            sa.Column("experiment_id", sa.String(), nullable=False),
            sa.Column("passed", sa.Boolean(), nullable=False),
            sa.Column("mean_composite_delta", sa.Float(), nullable=True),
            sa.Column(
                "protected_regressions_json",
                postgresql.JSONB(astext_type=sa.Text()),
                nullable=True,
            ),
            sa.Column(
                "details_json",
                postgresql.JSONB(astext_type=sa.Text()),
                nullable=True,
            ),
            sa.Column(
                "computed_at",
                sa.DateTime(timezone=True),
                server_default=sa.text("now()"),
                nullable=False,
            ),
            sa.ForeignKeyConstraint(
                ["experiment_id"],
                ["eval_experiments.id"],
                ondelete="CASCADE",
            ),
            sa.PrimaryKeyConstraint("id"),
            sa.UniqueConstraint("experiment_id"),
        )


def downgrade() -> None:
    if _has_table("eval_gate_verdicts"):
        op.drop_table("eval_gate_verdicts")
    if _has_table("eval_run_turns"):
        op.drop_table("eval_run_turns")
    if _has_table("eval_runs"):
        op.drop_table("eval_runs")
    if _has_table("eval_experiments"):
        op.drop_table("eval_experiments")
    if _has_table("eval_cases"):
        indexes = _index_names("eval_cases")
        if "ix_eval_cases_source_user_id" in indexes:
            op.drop_index("ix_eval_cases_source_user_id", table_name="eval_cases")
        if "ix_eval_cases_source_conversation_id" in indexes:
            op.drop_index(
                "ix_eval_cases_source_conversation_id", table_name="eval_cases"
            )
        op.drop_table("eval_cases")
    if _has_table("eval_versions"):
        op.drop_table("eval_versions")
    if _has_table("eval_regression_sets"):
        op.drop_table("eval_regression_sets")