262 lines
11 KiB
Python
262 lines
11 KiB
Python
"""Frontend MVP v0.3 contracts: categories, optional templates, criterion UUIDs.
|
|
|
|
Revision ID: 0005_frontend_mvp_v03
|
|
Revises: 0004_dynamic_attributes
|
|
Create Date: 2026-10-04
|
|
"""
|
|
|
|
from collections.abc import Sequence
|
|
|
|
import sqlalchemy as sa
|
|
from alembic import op
|
|
from sqlalchemy.dialects import postgresql
|
|
|
|
revision: str = "0005_frontend_mvp_v03"
|
|
down_revision: str | None = "0004_dynamic_attributes"
|
|
branch_labels: str | Sequence[str] | None = None
|
|
depends_on: str | Sequence[str] | None = None
|
|
|
|
|
|
def upgrade() -> None:
|
|
op.create_table(
|
|
"product_categories",
|
|
sa.Column("id", sa.String(64), primary_key=True),
|
|
sa.Column("name", sa.String(256), nullable=False),
|
|
sa.Column("status", sa.String(32), nullable=False, server_default="ACTIVE"),
|
|
)
|
|
op.execute("INSERT INTO product_categories (id, name, status) VALUES ('notebook', 'Notebooks', 'ACTIVE')")
|
|
|
|
op.add_column("source_categories", sa.Column("product_category_id", sa.String(64)))
|
|
op.create_foreign_key(
|
|
"fk_source_categories_product_category",
|
|
"source_categories",
|
|
"product_categories",
|
|
["product_category_id"],
|
|
["id"],
|
|
)
|
|
op.execute(
|
|
"""
|
|
UPDATE source_categories
|
|
SET product_category_id = canonical_category
|
|
WHERE canonical_category IS NOT NULL
|
|
AND EXISTS (SELECT 1 FROM product_categories WHERE id = source_categories.canonical_category)
|
|
"""
|
|
)
|
|
|
|
op.add_column("criterion_definitions", sa.Column("id", postgresql.UUID(as_uuid=True)))
|
|
op.execute("UPDATE criterion_definitions SET id = gen_random_uuid() WHERE id IS NULL")
|
|
op.alter_column("criterion_definitions", "id", nullable=False)
|
|
op.add_column(
|
|
"criterion_definitions",
|
|
sa.Column("origin", sa.String(16), nullable=False, server_default="SOURCE"),
|
|
)
|
|
op.add_column("criterion_definitions", sa.Column("source", sa.String(64)))
|
|
op.add_column("criterion_definitions", sa.Column("product_category_id", sa.String(64)))
|
|
op.add_column("criterion_definitions", sa.Column("label", sa.String(256)))
|
|
op.add_column("criterion_definitions", sa.Column("scoring_attribute_key", sa.String(128)))
|
|
op.execute("UPDATE criterion_definitions SET label = key WHERE label IS NULL")
|
|
op.execute("UPDATE criterion_definitions SET scoring_attribute_key = key")
|
|
op.execute(
|
|
"""
|
|
UPDATE criterion_definitions
|
|
SET product_category_id = category
|
|
WHERE category IS NOT NULL
|
|
AND EXISTS (SELECT 1 FROM product_categories WHERE id = criterion_definitions.category)
|
|
"""
|
|
)
|
|
op.alter_column("criterion_definitions", "label", nullable=False)
|
|
op.create_foreign_key(
|
|
"fk_criterion_definitions_product_category",
|
|
"criterion_definitions",
|
|
"product_categories",
|
|
["product_category_id"],
|
|
["id"],
|
|
)
|
|
|
|
op.add_column("project_criteria", sa.Column("criterion_definition_id", postgresql.UUID(as_uuid=True)))
|
|
op.add_column(
|
|
"project_criteria",
|
|
sa.Column("enabled_for_discovery", sa.Boolean(), nullable=False, server_default=sa.true()),
|
|
)
|
|
op.add_column("project_criteria", sa.Column("created_at", sa.DateTime(timezone=True)))
|
|
op.execute(
|
|
"""
|
|
UPDATE project_criteria
|
|
SET enabled_for_discovery = mandatory,
|
|
created_at = timezone('utc', now())
|
|
"""
|
|
)
|
|
op.execute(
|
|
"""
|
|
UPDATE project_criteria AS pc
|
|
SET criterion_definition_id = cd.id
|
|
FROM criterion_definitions AS cd
|
|
WHERE pc.criterion_key = cd.key
|
|
"""
|
|
)
|
|
op.alter_column("project_criteria", "criterion_definition_id", nullable=False)
|
|
op.alter_column("project_criteria", "created_at", nullable=False)
|
|
op.drop_constraint("project_criteria_criterion_key_fkey", "project_criteria", type_="foreignkey")
|
|
|
|
op.add_column("criterion_mappings", sa.Column("criterion_definition_id", postgresql.UUID(as_uuid=True)))
|
|
op.execute(
|
|
"""
|
|
UPDATE criterion_mappings AS cm
|
|
SET criterion_definition_id = cd.id
|
|
FROM criterion_definitions AS cd
|
|
WHERE cm.criterion_key = cd.key
|
|
"""
|
|
)
|
|
op.alter_column("criterion_mappings", "criterion_definition_id", nullable=False)
|
|
op.drop_constraint("criterion_mappings_criterion_key_fkey", "criterion_mappings", type_="foreignkey")
|
|
|
|
op.drop_constraint("criterion_definitions_pkey", "criterion_definitions", type_="primary")
|
|
op.create_primary_key("criterion_definitions_pkey", "criterion_definitions", ["id"])
|
|
op.create_unique_constraint("uq_criterion_definition_key", "criterion_definitions", ["key"])
|
|
op.create_foreign_key(
|
|
"fk_project_criteria_definition",
|
|
"project_criteria",
|
|
"criterion_definitions",
|
|
["criterion_definition_id"],
|
|
["id"],
|
|
)
|
|
op.create_foreign_key(
|
|
"fk_criterion_mappings_definition",
|
|
"criterion_mappings",
|
|
"criterion_definitions",
|
|
["criterion_definition_id"],
|
|
["id"],
|
|
)
|
|
|
|
op.create_table(
|
|
"source_attribute_mappings",
|
|
sa.Column("id", postgresql.UUID(as_uuid=True), primary_key=True),
|
|
sa.Column("source_id", sa.String(64), sa.ForeignKey("sources.id"), nullable=False),
|
|
sa.Column("source_category_id", postgresql.UUID(as_uuid=True), sa.ForeignKey("source_categories.id")),
|
|
sa.Column("raw_name", sa.String(256), nullable=False),
|
|
sa.Column(
|
|
"criterion_definition_id",
|
|
postgresql.UUID(as_uuid=True),
|
|
sa.ForeignKey("criterion_definitions.id"),
|
|
nullable=False,
|
|
),
|
|
sa.Column("status", sa.String(32), nullable=False),
|
|
sa.Column("created_at", sa.DateTime(timezone=True), nullable=False),
|
|
sa.Column("updated_at", sa.DateTime(timezone=True), nullable=False),
|
|
)
|
|
|
|
op.add_column("buying_projects", sa.Column("product_category_id", sa.String(64)))
|
|
op.add_column("buying_projects", sa.Column("market", sa.String(8)))
|
|
op.add_column("buying_projects", sa.Column("currency", sa.String(3)))
|
|
op.add_column("buying_projects", sa.Column("target_budget", sa.Numeric(12, 2)))
|
|
op.add_column("buying_projects", sa.Column("template_id", sa.String(128)))
|
|
op.add_column("buying_projects", sa.Column("template_version", sa.Integer()))
|
|
op.add_column("buying_projects", sa.Column("criteria_version", sa.Integer(), nullable=False, server_default="1"))
|
|
op.add_column("buying_projects", sa.Column("updated_at", sa.DateTime(timezone=True)))
|
|
op.execute(
|
|
"""
|
|
UPDATE buying_projects
|
|
SET template_id = profile_id,
|
|
template_version = profile_version,
|
|
product_category_id = 'notebook',
|
|
market = 'DE',
|
|
currency = 'EUR',
|
|
updated_at = created_at
|
|
"""
|
|
)
|
|
op.execute(
|
|
"""
|
|
UPDATE buying_projects AS bp
|
|
SET criteria_version = COALESCE((
|
|
SELECT MAX(version) FROM buying_project_criteria WHERE project_id = bp.id
|
|
), 1)
|
|
"""
|
|
)
|
|
op.alter_column("buying_projects", "product_category_id", nullable=False)
|
|
op.alter_column("buying_projects", "market", nullable=False)
|
|
op.alter_column("buying_projects", "currency", nullable=False)
|
|
op.alter_column("buying_projects", "updated_at", nullable=False)
|
|
op.create_foreign_key(
|
|
"fk_buying_projects_product_category",
|
|
"buying_projects",
|
|
"product_categories",
|
|
["product_category_id"],
|
|
["id"],
|
|
)
|
|
op.drop_constraint("fk_buying_projects_profile", "buying_projects", type_="foreignkey")
|
|
op.drop_constraint("ck_buying_projects_interval", "buying_projects", type_="check")
|
|
op.drop_column("buying_projects", "profile_id")
|
|
op.drop_column("buying_projects", "profile_version")
|
|
op.drop_column("buying_projects", "schedule_interval_minutes")
|
|
op.create_foreign_key(
|
|
"fk_buying_projects_template",
|
|
"buying_projects",
|
|
"product_profiles",
|
|
["template_id", "template_version"],
|
|
["id", "version"],
|
|
)
|
|
|
|
op.add_column(
|
|
"discovery_searches",
|
|
sa.Column("schedule_interval_minutes", sa.Integer(), nullable=False, server_default="180"),
|
|
)
|
|
op.add_column("search_runs", sa.Column("new_count", sa.Integer(), nullable=False, server_default="0"))
|
|
op.add_column("search_runs", sa.Column("changed_count", sa.Integer(), nullable=False, server_default="0"))
|
|
op.add_column("search_runs", sa.Column("unchanged_count", sa.Integer(), nullable=False, server_default="0"))
|
|
op.add_column("search_runs", sa.Column("missing_count", sa.Integer(), nullable=False, server_default="0"))
|
|
|
|
op.add_column(
|
|
"product_observation_attributes",
|
|
sa.Column("criterion_definition_id", postgresql.UUID(as_uuid=True)),
|
|
)
|
|
op.add_column("product_observation_attributes", sa.Column("raw_name", sa.String(256)))
|
|
op.create_foreign_key(
|
|
"fk_observation_attributes_definition",
|
|
"product_observation_attributes",
|
|
"criterion_definitions",
|
|
["criterion_definition_id"],
|
|
["id"],
|
|
)
|
|
op.execute(
|
|
"""
|
|
UPDATE product_observation_attributes AS poa
|
|
SET criterion_definition_id = cd.id,
|
|
raw_name = poa.attribute_key
|
|
FROM criterion_definitions AS cd
|
|
WHERE poa.attribute_key = cd.scoring_attribute_key
|
|
OR poa.attribute_key = cd.key
|
|
"""
|
|
)
|
|
|
|
|
|
def downgrade() -> None:
|
|
op.drop_constraint("fk_observation_attributes_definition", "product_observation_attributes", type_="foreignkey")
|
|
op.drop_column("product_observation_attributes", "raw_name")
|
|
op.drop_column("product_observation_attributes", "criterion_definition_id")
|
|
op.drop_column("search_runs", "missing_count")
|
|
op.drop_column("search_runs", "unchanged_count")
|
|
op.drop_column("search_runs", "changed_count")
|
|
op.drop_column("search_runs", "new_count")
|
|
op.drop_column("discovery_searches", "schedule_interval_minutes")
|
|
op.drop_constraint("fk_buying_projects_template", "buying_projects", type_="foreignkey")
|
|
op.add_column("buying_projects", sa.Column("profile_id", sa.String(128)))
|
|
op.add_column("buying_projects", sa.Column("profile_version", sa.Integer()))
|
|
op.add_column(
|
|
"buying_projects",
|
|
sa.Column("schedule_interval_minutes", sa.Integer(), nullable=False, server_default="180"),
|
|
)
|
|
op.execute("UPDATE buying_projects SET profile_id = template_id, profile_version = template_version")
|
|
op.alter_column("buying_projects", "profile_id", nullable=False)
|
|
op.alter_column("buying_projects", "profile_version", nullable=False)
|
|
op.drop_constraint("fk_buying_projects_product_category", "buying_projects", type_="foreignkey")
|
|
op.drop_column("buying_projects", "updated_at")
|
|
op.drop_column("buying_projects", "criteria_version")
|
|
op.drop_column("buying_projects", "template_version")
|
|
op.drop_column("buying_projects", "template_id")
|
|
op.drop_column("buying_projects", "target_budget")
|
|
op.drop_column("buying_projects", "currency")
|
|
op.drop_column("buying_projects", "market")
|
|
op.drop_column("buying_projects", "product_category_id")
|
|
op.drop_table("source_attribute_mappings")
|
|
raise NotImplementedError("full downgrade of criterion UUID primary key is not supported")
|