"""add explainable evaluations and the unified daily demand package Revision ID: 20260731_11 Revises: 20260731_10 Create Date: 2026-07-31 """ from __future__ import annotations from collections.abc import Sequence import sqlalchemy as sa from alembic import op revision: str = "20260731_11" down_revision: str | None = "20260731_10" branch_labels: str | Sequence[str] | None = None depends_on: str | Sequence[str] | None = None def _pipeline_table_options() -> dict[str, str]: bind = op.get_bind() if bind.dialect.name != "mysql": return {} row = bind.execute( sa.text( "SELECT CHARACTER_SET_NAME, COLLATION_NAME " "FROM INFORMATION_SCHEMA.COLUMNS " "WHERE TABLE_SCHEMA = DATABASE() " "AND TABLE_NAME = 'pipeline_run' " "AND COLUMN_NAME = 'run_id'" ) ).one() return { "mysql_charset": str(row.CHARACTER_SET_NAME), "mysql_collate": str(row.COLLATION_NAME), } def upgrade() -> None: table_options = _pipeline_table_options() op.create_table( "strategy_version", sa.Column("strategy_version_id", sa.String(36), primary_key=True), sa.Column("version_key", sa.String(64), nullable=False), sa.Column("status", sa.String(24), nullable=False), sa.Column("definition_json", sa.JSON(), nullable=False), sa.Column("change_reason", sa.Text(), nullable=False), sa.Column("created_by", sa.String(128), nullable=False), sa.Column("activated_at", sa.DateTime(), nullable=True), sa.Column("created_at", sa.DateTime(), server_default=sa.func.now(), nullable=False), sa.UniqueConstraint("version_key", name="uk_strategy_version_key"), **table_options, ) op.create_index( "idx_strategy_version_status", "strategy_version", ["status", "activated_at"], ) op.create_table( "demand_evaluation_snapshot", sa.Column("evaluation_id", sa.String(36), primary_key=True), sa.Column( "platform_demand_version_id", sa.String(36), sa.ForeignKey( "platform_demand_version.platform_demand_version_id", ondelete="RESTRICT", ), nullable=False, ), sa.Column( "run_id", sa.String(36), sa.ForeignKey("pipeline_run.run_id", ondelete="RESTRICT"), nullable=False, ), sa.Column( "strategy_version_id", sa.String(36), sa.ForeignKey("strategy_version.strategy_version_id", ondelete="RESTRICT"), nullable=False, ), sa.Column("biz_dt", sa.String(8), nullable=False), sa.Column("validity_score", sa.Numeric(6, 5), nullable=False), sa.Column("local_supply_priority", sa.Numeric(6, 5), nullable=False), sa.Column("data_confidence", sa.Numeric(6, 5), nullable=False), sa.Column("posterior_state", sa.String(24), nullable=False), sa.Column("action_tier", sa.String(32), nullable=False), sa.Column("metrics_json", sa.JSON(), nullable=False), sa.Column("missing_dimensions_json", sa.JSON(), nullable=False), sa.Column("decision_reason", sa.Text(), nullable=False), sa.Column("created_at", sa.DateTime(), server_default=sa.func.now(), nullable=False), sa.UniqueConstraint( "platform_demand_version_id", name="uk_demand_evaluation_version", ), **table_options, ) op.create_index( "idx_demand_evaluation_biz", "demand_evaluation_snapshot", ["biz_dt", "action_tier"], ) op.create_table( "demand_score_contribution", sa.Column("contribution_id", sa.String(36), primary_key=True), sa.Column( "evaluation_id", sa.String(36), sa.ForeignKey("demand_evaluation_snapshot.evaluation_id", ondelete="RESTRICT"), nullable=False, ), sa.Column("score_type", sa.String(24), nullable=False), sa.Column("dimension", sa.String(64), nullable=False), sa.Column("raw_value_json", sa.JSON(), nullable=False), sa.Column("normalized_value", sa.Numeric(8, 7), nullable=False), sa.Column("configured_weight", sa.Numeric(8, 7), nullable=False), sa.Column("effective_weight", sa.Numeric(8, 7), nullable=False), sa.Column("contribution", sa.Numeric(8, 7), nullable=False), sa.Column("data_status", sa.String(24), nullable=False), sa.Column("reason", sa.Text(), nullable=False), sa.Column("created_at", sa.DateTime(), server_default=sa.func.now(), nullable=False), sa.UniqueConstraint( "evaluation_id", "score_type", "dimension", name="uk_demand_score_contribution", ), **table_options, ) op.create_index( "idx_demand_score_contribution_eval", "demand_score_contribution", ["evaluation_id"], ) op.create_table( "daily_demand_package", sa.Column("demand_package_id", sa.String(36), primary_key=True), sa.Column( "run_id", sa.String(36), sa.ForeignKey("pipeline_run.run_id", ondelete="RESTRICT"), nullable=False, ), sa.Column( "evidence_package_id", sa.String(36), sa.ForeignKey("evidence_package.package_id", ondelete="RESTRICT"), nullable=False, ), sa.Column( "strategy_version_id", sa.String(36), sa.ForeignKey("strategy_version.strategy_version_id", ondelete="RESTRICT"), nullable=False, ), sa.Column("biz_dt", sa.String(8), nullable=False), sa.Column("package_version", sa.Integer(), nullable=False), sa.Column("status", sa.String(24), nullable=False), sa.Column("content_hash", sa.String(64), nullable=False), sa.Column("item_count", sa.Integer(), nullable=False), sa.Column("published_at", sa.DateTime(), nullable=True), sa.Column("created_at", sa.DateTime(), server_default=sa.func.now(), nullable=False), sa.UniqueConstraint("run_id", name="uk_daily_demand_package_run"), **table_options, ) op.create_index( "idx_daily_demand_package_biz", "daily_demand_package", ["biz_dt", "status"], ) op.create_table( "daily_demand_task", sa.Column("task_id", sa.String(36), primary_key=True), sa.Column( "demand_package_id", sa.String(36), sa.ForeignKey("daily_demand_package.demand_package_id", ondelete="RESTRICT"), nullable=False, ), sa.Column( "platform_demand_version_id", sa.String(36), sa.ForeignKey( "platform_demand_version.platform_demand_version_id", ondelete="RESTRICT", ), nullable=False, ), sa.Column( "evaluation_id", sa.String(36), sa.ForeignKey("demand_evaluation_snapshot.evaluation_id", ondelete="RESTRICT"), nullable=False, ), sa.Column("action_tier", sa.String(32), nullable=False), sa.Column("search_terms_json", sa.JSON(), nullable=False), sa.Column("exclude_terms_json", sa.JSON(), nullable=False), sa.Column("hit_rules_json", sa.JSON(), nullable=False), sa.Column("hypotheses_json", sa.JSON(), nullable=False), sa.Column("evidence_gaps_json", sa.JSON(), nullable=False), sa.Column("existing_content_json", sa.JSON(), nullable=False), sa.Column("allocation_json", sa.JSON(), nullable=False), sa.Column("task_payload_json", sa.JSON(), nullable=False), sa.Column("created_at", sa.DateTime(), server_default=sa.func.now(), nullable=False), sa.UniqueConstraint( "demand_package_id", "platform_demand_version_id", name="uk_daily_demand_task_version", ), **table_options, ) op.create_index( "idx_daily_demand_task_tier", "daily_demand_task", ["demand_package_id", "action_tier"], ) def downgrade() -> None: op.drop_index("idx_daily_demand_task_tier", table_name="daily_demand_task") op.drop_table("daily_demand_task") op.drop_index( "idx_daily_demand_package_biz", table_name="daily_demand_package", ) op.drop_table("daily_demand_package") op.drop_index( "idx_demand_score_contribution_eval", table_name="demand_score_contribution", ) op.drop_table("demand_score_contribution") op.drop_index( "idx_demand_evaluation_biz", table_name="demand_evaluation_snapshot", ) op.drop_table("demand_evaluation_snapshot") op.drop_index("idx_strategy_version_status", table_name="strategy_version") op.drop_table("strategy_version")