"""Add master catalog source-of-truth tables. Revision ID: 0025_add_master_catalog_tables Revises: 0024_add_source_product_snapshots Create Date: 2026-06-01 00:00:00.000000 """ from alembic import op import sqlalchemy as sa from sqlalchemy.dialects.postgresql import JSONB revision = "0025_add_master_catalog_tables" down_revision = "0024_add_source_product_snapshots" branch_labels = None depends_on = None def upgrade() -> None: op.create_table( "master_product", sa.Column("id", sa.Integer(), primary_key=True, autoincrement=True), sa.Column("sku", sa.String(128), nullable=False), sa.Column("source_item", sa.String(128), nullable=True), sa.Column("name", sa.Text(), nullable=True), sa.Column("product_family", sa.String(64), nullable=True), sa.Column("category_l1", sa.Text(), nullable=True), sa.Column("category_l2", sa.Text(), nullable=True), sa.Column("category_l3", sa.Text(), nullable=True), sa.Column("brand", sa.Text(), nullable=True), sa.Column("manufacturer", sa.Text(), nullable=True), sa.Column("collection", sa.Text(), nullable=True), sa.Column("item_size", sa.Text(), nullable=True), sa.Column("item_style", sa.Text(), nullable=True), sa.Column("assembly_type", sa.String(32), nullable=True), sa.Column("stocked", sa.Boolean(), nullable=True), sa.Column("status", sa.String(32), nullable=True), sa.Column("raw_payload", JSONB, nullable=True), sa.Column("row_hash", sa.String(64), nullable=False), sa.Column("is_active", sa.Boolean(), nullable=False, server_default=sa.true()), sa.Column("created_at", sa.DateTime(timezone=True), nullable=False, server_default=sa.func.now()), sa.Column("updated_at", sa.DateTime(timezone=True), nullable=False, server_default=sa.func.now()), sa.UniqueConstraint("sku", name="uq_master_product_sku"), ) op.create_index("ix_master_product_sku", "master_product", ["sku"]) op.create_index("ix_master_product_family", "master_product", ["product_family"]) op.create_table( "master_product_attribute_value", sa.Column("id", sa.Integer(), primary_key=True, autoincrement=True), sa.Column("product_id", sa.Integer(), sa.ForeignKey("master_product.id"), nullable=False), sa.Column("sku", sa.String(128), nullable=False), sa.Column("attribute_code", sa.String(128), nullable=False), sa.Column("value", sa.Text(), nullable=True), sa.Column("source_label", sa.String(128), nullable=True), sa.Column("updated_at", sa.DateTime(timezone=True), nullable=False, server_default=sa.func.now()), sa.UniqueConstraint("product_id", "attribute_code", name="uq_master_product_attr_product_code"), ) op.create_index("ix_master_product_attribute_value_sku", "master_product_attribute_value", ["sku"]) op.create_index("ix_master_product_attribute_value_code", "master_product_attribute_value", ["attribute_code"]) op.create_table( "master_product_price", sa.Column("id", sa.Integer(), primary_key=True, autoincrement=True), sa.Column("product_id", sa.Integer(), sa.ForeignKey("master_product.id"), nullable=False), sa.Column("sku", sa.String(128), nullable=False), sa.Column("price_type", sa.String(32), nullable=False), sa.Column("amount", sa.Numeric(12, 4), nullable=True), sa.Column("currency", sa.String(8), nullable=False, server_default="USD"), sa.Column("source_label", sa.String(128), nullable=True), sa.Column("updated_at", sa.DateTime(timezone=True), nullable=False, server_default=sa.func.now()), sa.UniqueConstraint("product_id", "price_type", name="uq_master_product_price_product_type"), ) op.create_index("ix_master_product_price_sku", "master_product_price", ["sku"]) op.create_index("ix_master_product_price_type", "master_product_price", ["price_type"]) op.create_table( "master_product_location_availability", sa.Column("id", sa.Integer(), primary_key=True, autoincrement=True), sa.Column("product_id", sa.Integer(), sa.ForeignKey("master_product.id"), nullable=False), sa.Column("sku", sa.String(128), nullable=False), sa.Column("location_code", sa.String(64), nullable=False), sa.Column("is_available", sa.Boolean(), nullable=False, server_default=sa.false()), sa.Column("source_value", sa.Text(), nullable=True), sa.Column("source_label", sa.String(128), nullable=True), sa.Column("updated_at", sa.DateTime(timezone=True), nullable=False, server_default=sa.func.now()), sa.UniqueConstraint("product_id", "location_code", name="uq_master_product_location_product_location"), ) op.create_index("ix_master_product_location_availability_sku", "master_product_location_availability", ["sku"]) op.create_index( "ix_master_product_location_availability_location", "master_product_location_availability", ["location_code"], ) op.create_table( "product_channel_assignment", sa.Column("id", sa.Integer(), primary_key=True, autoincrement=True), sa.Column("product_id", sa.Integer(), sa.ForeignKey("master_product.id"), nullable=False), sa.Column("sku", sa.String(128), nullable=False), sa.Column("channel_code", sa.String(32), nullable=False), sa.Column("assignment_status", sa.String(32), nullable=False, server_default="active"), sa.Column("reason", sa.Text(), nullable=True), sa.Column("payload", JSONB, nullable=True), sa.Column("updated_at", sa.DateTime(timezone=True), nullable=False, server_default=sa.func.now()), sa.UniqueConstraint("product_id", "channel_code", name="uq_product_channel_assignment_product_channel"), ) op.create_index("ix_product_channel_assignment_sku", "product_channel_assignment", ["sku"]) op.create_index("ix_product_channel_assignment_channel_code", "product_channel_assignment", ["channel_code"]) op.create_table( "product_attribute_mapping", sa.Column("id", sa.Integer(), primary_key=True, autoincrement=True), sa.Column("source_label", sa.String(128), nullable=False), sa.Column("source_column_name", sa.Text(), nullable=True), sa.Column("source_column_key", sa.String(255), nullable=False), sa.Column("canonical_code", sa.String(128), nullable=False), sa.Column("canonical_label", sa.Text(), nullable=True), sa.Column("target_scope", sa.String(32), nullable=False, server_default="canonical"), sa.Column("data_type", sa.String(32), nullable=False, server_default="text"), sa.Column("transform_rule", JSONB, nullable=True), sa.Column("priority", sa.Integer(), nullable=False, server_default="100"), sa.Column("is_active", sa.Boolean(), nullable=False, server_default=sa.true()), sa.Column("notes", sa.Text(), nullable=True), sa.Column("created_at", sa.DateTime(timezone=True), nullable=False, server_default=sa.func.now()), sa.Column("updated_at", sa.DateTime(timezone=True), nullable=False, server_default=sa.func.now()), sa.UniqueConstraint( "source_label", "source_column_key", "target_scope", name="uq_product_attribute_mapping_source_target", ), ) op.create_index("ix_product_attribute_mapping_source_column_key", "product_attribute_mapping", ["source_column_key"]) op.create_index("ix_product_attribute_mapping_canonical_code", "product_attribute_mapping", ["canonical_code"]) op.create_table( "master_attribute_definition", sa.Column("id", sa.Integer(), primary_key=True, autoincrement=True), sa.Column("attribute_code", sa.String(128), nullable=False), sa.Column("label", sa.Text(), nullable=True), sa.Column("product_family", sa.String(64), nullable=True), sa.Column("purpose", sa.String(32), nullable=False, server_default="presentation"), sa.Column("data_type", sa.String(32), nullable=False, server_default="text"), sa.Column("target_scopes", JSONB, nullable=True), sa.Column("is_required", sa.Boolean(), nullable=False, server_default=sa.false()), sa.Column("is_active", sa.Boolean(), nullable=False, server_default=sa.true()), sa.Column("notes", sa.Text(), nullable=True), sa.Column("created_at", sa.DateTime(timezone=True), nullable=False, server_default=sa.func.now()), sa.Column("updated_at", sa.DateTime(timezone=True), nullable=False, server_default=sa.func.now()), sa.UniqueConstraint("attribute_code", name="uq_master_attribute_definition_code"), ) op.create_index("ix_master_attribute_definition_attribute_code", "master_attribute_definition", ["attribute_code"]) op.create_table( "channel_publish_state", sa.Column("id", sa.Integer(), primary_key=True, autoincrement=True), sa.Column("sku", sa.String(128), nullable=False), sa.Column("channel_code", sa.String(32), nullable=False), sa.Column("payload_hash", sa.String(64), nullable=True), sa.Column("last_payload", JSONB, nullable=True), sa.Column("last_published_at", sa.DateTime(timezone=True), nullable=True), sa.Column("last_status", sa.String(32), nullable=True), sa.Column("last_error", sa.Text(), nullable=True), sa.Column("updated_at", sa.DateTime(timezone=True), nullable=False, server_default=sa.func.now()), sa.UniqueConstraint("sku", "channel_code", name="uq_channel_publish_state_sku_channel"), ) op.create_index("ix_channel_publish_state_sku", "channel_publish_state", ["sku"]) op.create_index("ix_channel_publish_state_channel_code", "channel_publish_state", ["channel_code"]) def downgrade() -> None: op.drop_index("ix_channel_publish_state_channel_code", table_name="channel_publish_state") op.drop_index("ix_channel_publish_state_sku", table_name="channel_publish_state") op.drop_table("channel_publish_state") op.drop_index("ix_master_attribute_definition_attribute_code", table_name="master_attribute_definition") op.drop_table("master_attribute_definition") op.drop_index("ix_product_attribute_mapping_canonical_code", table_name="product_attribute_mapping") op.drop_index("ix_product_attribute_mapping_source_column_key", table_name="product_attribute_mapping") op.drop_table("product_attribute_mapping") op.drop_index("ix_product_channel_assignment_channel_code", table_name="product_channel_assignment") op.drop_index("ix_product_channel_assignment_sku", table_name="product_channel_assignment") op.drop_table("product_channel_assignment") op.drop_index("ix_master_product_location_availability_location", table_name="master_product_location_availability") op.drop_index("ix_master_product_location_availability_sku", table_name="master_product_location_availability") op.drop_table("master_product_location_availability") op.drop_index("ix_master_product_price_type", table_name="master_product_price") op.drop_index("ix_master_product_price_sku", table_name="master_product_price") op.drop_table("master_product_price") op.drop_index("ix_master_product_attribute_value_code", table_name="master_product_attribute_value") op.drop_index("ix_master_product_attribute_value_sku", table_name="master_product_attribute_value") op.drop_table("master_product_attribute_value") op.drop_index("ix_master_product_family", table_name="master_product") op.drop_index("ix_master_product_sku", table_name="master_product") op.drop_table("master_product")