"""add catalog normalization schema Revision ID: 0057_add_catalog_normalization_schema Revises: 0056_add_master_product_association Create Date: 2026-07-21 11:30:00.000000 """ from __future__ import annotations from alembic import op import sqlalchemy as sa from sqlalchemy.dialects import postgresql revision = "0057_add_catalog_normalization_schema" down_revision = "0056_add_master_product_association" branch_labels = None depends_on = None def upgrade() -> None: op.create_table( "master_assembly_mode", sa.Column("id", sa.Integer(), primary_key=True, autoincrement=True), sa.Column("code", sa.String(length=64), nullable=False), sa.Column("name", sa.Text(), nullable=False), sa.Column("aliases", postgresql.JSONB(astext_type=sa.Text()), nullable=True), 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("code", name="uq_master_assembly_mode_code"), ) op.create_index("ix_master_assembly_mode_code", "master_assembly_mode", ["code"]) op.create_table( "master_location_registry", sa.Column("id", sa.Integer(), primary_key=True, autoincrement=True), sa.Column("code", sa.String(length=128), nullable=False), sa.Column("name", sa.Text(), nullable=False), sa.Column("location_kind", sa.String(length=32), nullable=False, server_default="warehouse"), sa.Column("brand_id", sa.Integer(), sa.ForeignKey("master_brand.id"), nullable=True), sa.Column("channel_code", sa.String(length=32), nullable=True), sa.Column("connection_id", sa.Integer(), nullable=True), sa.Column("aliases", postgresql.JSONB(astext_type=sa.Text()), nullable=True), 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("code", name="uq_master_location_registry_code"), ) op.create_index("ix_master_location_registry_code", "master_location_registry", ["code"]) op.create_index("ix_master_location_registry_location_kind", "master_location_registry", ["location_kind"]) op.create_index("ix_master_location_registry_brand_id", "master_location_registry", ["brand_id"]) op.create_index("ix_master_location_registry_channel_code", "master_location_registry", ["channel_code"]) op.create_index("ix_master_location_registry_connection_id", "master_location_registry", ["connection_id"]) op.create_table( "collection_commerce_profile", sa.Column("id", sa.Integer(), primary_key=True, autoincrement=True), sa.Column("collection_registry_id", sa.Integer(), sa.ForeignKey("master_collection_registry.id"), nullable=True), sa.Column("category_l1", sa.Text(), nullable=True), sa.Column("collection_name", sa.Text(), nullable=True), sa.Column("channel_code", sa.String(length=32), nullable=True), sa.Column("connection_id", sa.Integer(), nullable=True), sa.Column("sample_master_sku", sa.String(length=128), nullable=True), sa.Column("sample_category_path_slug", sa.String(length=512), nullable=True), sa.Column("availability_modes_json", postgresql.JSONB(astext_type=sa.Text()), nullable=True), sa.Column("appointment_url", sa.Text(), nullable=True), sa.Column("sample_cta_label", sa.String(length=64), nullable=True), sa.Column("appointment_cta_label", sa.String(length=64), nullable=True), sa.Column("badges_json", postgresql.JSONB(astext_type=sa.Text()), nullable=True), 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( "collection_registry_id", "channel_code", "connection_id", name="uq_collection_commerce_profile_scope", ), ) op.create_index("ix_collection_commerce_profile_collection_registry_id", "collection_commerce_profile", ["collection_registry_id"]) op.create_index("ix_collection_commerce_profile_collection_name", "collection_commerce_profile", ["collection_name"]) op.create_index("ix_collection_commerce_profile_channel_code", "collection_commerce_profile", ["channel_code"]) op.create_index("ix_collection_commerce_profile_connection_id", "collection_commerce_profile", ["connection_id"]) op.create_index("ix_collection_commerce_profile_sample_master_sku", "collection_commerce_profile", ["sample_master_sku"]) op.create_index( "ix_collection_commerce_profile_sample_category_path_slug", "collection_commerce_profile", ["sample_category_path_slug"], ) op.add_column( "master_product", sa.Column("normalization_status", sa.String(length=32), nullable=False, server_default="pending"), ) op.add_column("master_product", sa.Column("normalized_at", sa.DateTime(timezone=True), nullable=True)) op.add_column("master_product", sa.Column("assembly_mode_id", sa.Integer(), nullable=True)) op.add_column("master_product", sa.Column("base_sku", sa.String(length=128), nullable=True)) op.add_column("master_product", sa.Column("variant_group_code", sa.String(length=128), nullable=True)) op.create_index("ix_master_product_normalization_status", "master_product", ["normalization_status"]) op.create_index("ix_master_product_assembly_mode_id", "master_product", ["assembly_mode_id"]) op.create_index("ix_master_product_base_sku", "master_product", ["base_sku"]) op.create_index("ix_master_product_variant_group_code", "master_product", ["variant_group_code"]) op.create_foreign_key( "fk_master_product_assembly_mode_id", "master_product", "master_assembly_mode", ["assembly_mode_id"], ["id"], ) op.add_column("master_product_location_availability", sa.Column("location_id", sa.Integer(), nullable=True)) op.create_index( "ix_master_product_location_availability_location_id", "master_product_location_availability", ["location_id"], ) op.create_foreign_key( "fk_master_product_location_availability_location_id", "master_product_location_availability", "master_location_registry", ["location_id"], ["id"], ) op.create_table( "catalog_normalization_profile", sa.Column("id", sa.Integer(), primary_key=True, autoincrement=True), sa.Column("code", sa.String(length=128), nullable=False), sa.Column("name", sa.Text(), nullable=False), sa.Column("family_id", sa.Integer(), sa.ForeignKey("master_product_family.id"), nullable=True), sa.Column("config_json", postgresql.JSONB(astext_type=sa.Text()), nullable=True), 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("code", name="uq_catalog_normalization_profile_code"), ) op.create_index("ix_catalog_normalization_profile_code", "catalog_normalization_profile", ["code"]) op.create_index("ix_catalog_normalization_profile_family_id", "catalog_normalization_profile", ["family_id"]) op.create_table( "catalog_normalization_rule", sa.Column("id", sa.Integer(), primary_key=True, autoincrement=True), sa.Column("profile_id", sa.Integer(), sa.ForeignKey("catalog_normalization_profile.id"), nullable=True), sa.Column("rule_code", sa.String(length=128), nullable=False), sa.Column("scope_kind", sa.String(length=32), nullable=False, server_default="sku_prefix"), sa.Column("scope_value", sa.String(length=255), nullable=True), sa.Column("field_name", sa.String(length=128), nullable=True), sa.Column("match_operator", sa.String(length=32), nullable=False, server_default="equals"), sa.Column("match_value", sa.Text(), nullable=True), sa.Column("priority", sa.Integer(), nullable=False, server_default="100"), sa.Column("stop_processing", sa.Boolean(), nullable=False, server_default=sa.true()), sa.Column("rule_status", sa.String(length=32), nullable=False, server_default="active"), 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("rule_code", name="uq_catalog_normalization_rule_code"), ) op.create_index("ix_catalog_normalization_rule_profile_id", "catalog_normalization_rule", ["profile_id"]) op.create_index("ix_catalog_normalization_rule_rule_code", "catalog_normalization_rule", ["rule_code"]) op.create_index("ix_catalog_normalization_rule_scope_kind", "catalog_normalization_rule", ["scope_kind"]) op.create_index("ix_catalog_normalization_rule_scope_value", "catalog_normalization_rule", ["scope_value"]) op.create_index("ix_catalog_normalization_rule_field_name", "catalog_normalization_rule", ["field_name"]) op.create_index("ix_catalog_normalization_rule_rule_status", "catalog_normalization_rule", ["rule_status"]) op.create_table( "catalog_normalization_rule_target", sa.Column("id", sa.Integer(), primary_key=True, autoincrement=True), sa.Column("rule_id", sa.Integer(), sa.ForeignKey("catalog_normalization_rule.id"), nullable=False), sa.Column("target_field", sa.String(length=128), nullable=False), sa.Column("target_value", sa.Text(), nullable=True), sa.Column("target_code", sa.String(length=128), nullable=True), sa.Column("target_payload", postgresql.JSONB(astext_type=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("rule_id", "target_field", name="uq_catalog_normalization_rule_target_field"), ) op.create_index("ix_catalog_normalization_rule_target_rule_id", "catalog_normalization_rule_target", ["rule_id"]) op.create_index( "ix_catalog_normalization_rule_target_target_field", "catalog_normalization_rule_target", ["target_field"], ) op.create_index( "ix_catalog_normalization_rule_target_target_code", "catalog_normalization_rule_target", ["target_code"], ) op.create_table( "catalog_value_alias", sa.Column("id", sa.Integer(), primary_key=True, autoincrement=True), sa.Column("entity_kind", sa.String(length=32), nullable=False), sa.Column("field_name", sa.String(length=128), nullable=False), sa.Column("alias_value", sa.Text(), nullable=False), sa.Column("canonical_code", sa.String(length=128), nullable=False), sa.Column("canonical_label", sa.Text(), nullable=True), sa.Column("match_operator", sa.String(length=32), nullable=False, server_default="equals"), sa.Column("sort_order", 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( "entity_kind", "field_name", "alias_value", name="uq_catalog_value_alias_entity_field_value", ), ) op.create_index("ix_catalog_value_alias_entity_kind", "catalog_value_alias", ["entity_kind"]) op.create_index("ix_catalog_value_alias_field_name", "catalog_value_alias", ["field_name"]) op.create_index("ix_catalog_value_alias_canonical_code", "catalog_value_alias", ["canonical_code"]) op.create_table( "catalog_resolved_field", sa.Column("id", sa.Integer(), primary_key=True, autoincrement=True), sa.Column("master_product_id", sa.Integer(), sa.ForeignKey("master_product.id"), nullable=True), sa.Column("sku", sa.String(length=128), nullable=False), sa.Column("field_name", sa.String(length=128), nullable=False), sa.Column("raw_value", sa.Text(), nullable=True), sa.Column("normalized_value", sa.Text(), nullable=True), sa.Column("canonical_code", sa.String(length=128), nullable=True), sa.Column("source_kind", sa.String(length=32), nullable=False, server_default="rule"), sa.Column("source_rule_id", sa.Integer(), sa.ForeignKey("catalog_normalization_rule.id"), nullable=True), sa.Column("resolution_status", sa.String(length=32), nullable=False, server_default="resolved"), sa.Column("confidence", sa.Numeric(5, 4), nullable=True), sa.Column("payload", postgresql.JSONB(astext_type=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("sku", "field_name", name="uq_catalog_resolved_field_sku_field"), ) op.create_index("ix_catalog_resolved_field_master_product_id", "catalog_resolved_field", ["master_product_id"]) op.create_index("ix_catalog_resolved_field_sku", "catalog_resolved_field", ["sku"]) op.create_index("ix_catalog_resolved_field_field_name", "catalog_resolved_field", ["field_name"]) op.create_index("ix_catalog_resolved_field_canonical_code", "catalog_resolved_field", ["canonical_code"]) op.create_index("ix_catalog_resolved_field_source_kind", "catalog_resolved_field", ["source_kind"]) op.create_index("ix_catalog_resolved_field_source_rule_id", "catalog_resolved_field", ["source_rule_id"]) op.create_index( "ix_catalog_resolved_field_resolution_status", "catalog_resolved_field", ["resolution_status"], ) op.create_table( "catalog_review_queue_item", sa.Column("id", sa.Integer(), primary_key=True, autoincrement=True), sa.Column("master_product_id", sa.Integer(), sa.ForeignKey("master_product.id"), nullable=True), sa.Column("sku", sa.String(length=128), nullable=True), sa.Column("field_name", sa.String(length=128), nullable=True), sa.Column("issue_code", sa.String(length=64), nullable=False), sa.Column("severity", sa.String(length=16), nullable=False, server_default="error"), sa.Column("status", sa.String(length=32), nullable=False, server_default="open"), sa.Column("raw_value", sa.Text(), nullable=True), sa.Column("proposed_value", sa.Text(), nullable=True), sa.Column("source_rule_id", sa.Integer(), sa.ForeignKey("catalog_normalization_rule.id"), nullable=True), sa.Column("context_json", postgresql.JSONB(astext_type=sa.Text()), nullable=True), sa.Column("assigned_to", sa.String(length=128), nullable=True), sa.Column("resolved_at", sa.DateTime(timezone=True), nullable=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()), ) op.create_index("ix_catalog_review_queue_item_master_product_id", "catalog_review_queue_item", ["master_product_id"]) op.create_index("ix_catalog_review_queue_item_sku", "catalog_review_queue_item", ["sku"]) op.create_index("ix_catalog_review_queue_item_field_name", "catalog_review_queue_item", ["field_name"]) op.create_index("ix_catalog_review_queue_item_issue_code", "catalog_review_queue_item", ["issue_code"]) op.create_index("ix_catalog_review_queue_item_status", "catalog_review_queue_item", ["status"]) op.create_index("ix_catalog_review_queue_item_source_rule_id", "catalog_review_queue_item", ["source_rule_id"]) op.create_index( "ix_catalog_review_queue_status_severity", "catalog_review_queue_item", ["status", "severity"], ) op.create_table( "channel_catalog_policy", sa.Column("id", sa.Integer(), primary_key=True, autoincrement=True), sa.Column("channel_code", sa.String(length=32), nullable=False), sa.Column("connection_id", sa.Integer(), nullable=True), sa.Column("policy_name", sa.String(length=128), nullable=True), sa.Column("publish_mode", sa.String(length=32), nullable=False, server_default="selected_scope"), sa.Column("variation_mode", sa.String(length=32), nullable=False, server_default="simple_only"), sa.Column("shell_product_mode", sa.String(length=32), nullable=False, server_default="none"), sa.Column("shell_sku_template", sa.String(length=255), nullable=True), sa.Column("publish_shell_products", sa.Boolean(), nullable=False, server_default=sa.false()), sa.Column("exclude_rta", sa.Boolean(), nullable=False, server_default=sa.false()), sa.Column("include_assembled", sa.Boolean(), nullable=False, server_default=sa.true()), sa.Column("include_accessories_without_brand", sa.Boolean(), nullable=False, server_default=sa.false()), sa.Column("max_axes", sa.Integer(), nullable=True), sa.Column("default_allowed_axes", postgresql.JSONB(astext_type=sa.Text()), nullable=True), sa.Column("notes", sa.Text(), nullable=True), 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("channel_code", "connection_id", name="uq_channel_catalog_policy_channel_connection"), ) op.create_index("ix_channel_catalog_policy_channel_code", "channel_catalog_policy", ["channel_code"]) op.create_index("ix_channel_catalog_policy_connection_id", "channel_catalog_policy", ["connection_id"]) op.create_table( "channel_catalog_policy_scope", sa.Column("id", sa.Integer(), primary_key=True, autoincrement=True), sa.Column("policy_id", sa.Integer(), sa.ForeignKey("channel_catalog_policy.id"), nullable=False), sa.Column("scope_kind", sa.String(length=32), nullable=False), sa.Column("scope_code", sa.String(length=128), nullable=False), sa.Column("action", sa.String(length=16), nullable=False, server_default="include"), sa.Column("priority", sa.Integer(), nullable=False, server_default="100"), 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( "policy_id", "scope_kind", "scope_code", "action", name="uq_channel_catalog_policy_scope_rule", ), ) op.create_index("ix_channel_catalog_policy_scope_policy_id", "channel_catalog_policy_scope", ["policy_id"]) op.create_index("ix_channel_catalog_policy_scope_scope_kind", "channel_catalog_policy_scope", ["scope_kind"]) op.create_index("ix_channel_catalog_policy_scope_scope_code", "channel_catalog_policy_scope", ["scope_code"]) op.create_table( "channel_variation_policy", sa.Column("id", sa.Integer(), primary_key=True, autoincrement=True), sa.Column("channel_code", sa.String(length=32), nullable=False), sa.Column("connection_id", sa.Integer(), nullable=True), sa.Column("scope_kind", sa.String(length=32), nullable=False, server_default="global"), sa.Column("scope_code", sa.String(length=128), nullable=True), sa.Column("variation_mode", sa.String(length=32), nullable=False, server_default="simple_only"), sa.Column("allowed_axes", postgresql.JSONB(astext_type=sa.Text()), nullable=True), sa.Column("axis_priority", postgresql.JSONB(astext_type=sa.Text()), nullable=True), sa.Column("max_axes", sa.Integer(), nullable=True), sa.Column("shell_product_required", sa.Boolean(), nullable=False, server_default=sa.false()), sa.Column("shell_sku_template", sa.String(length=255), nullable=True), sa.Column("parent_group_strategy", sa.String(length=64), nullable=True), sa.Column("notes", sa.Text(), nullable=True), 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( "channel_code", "connection_id", "scope_kind", "scope_code", name="uq_channel_variation_policy_scope", ), ) op.create_index("ix_channel_variation_policy_channel_code", "channel_variation_policy", ["channel_code"]) op.create_index("ix_channel_variation_policy_connection_id", "channel_variation_policy", ["connection_id"]) op.create_index("ix_channel_variation_policy_scope_kind", "channel_variation_policy", ["scope_kind"]) op.create_index("ix_channel_variation_policy_scope_code", "channel_variation_policy", ["scope_code"]) op.create_table( "channel_shell_product", sa.Column("id", sa.Integer(), primary_key=True, autoincrement=True), sa.Column("channel_code", sa.String(length=32), nullable=False), sa.Column("connection_id", sa.Integer(), nullable=True), sa.Column("policy_id", sa.Integer(), sa.ForeignKey("channel_catalog_policy.id"), nullable=True), sa.Column("variation_policy_id", sa.Integer(), sa.ForeignKey("channel_variation_policy.id"), nullable=True), sa.Column("shell_sku", sa.String(length=128), nullable=False), sa.Column("shell_name", sa.Text(), nullable=True), sa.Column("base_sku", sa.String(length=128), nullable=True), sa.Column("variant_group_code", sa.String(length=128), nullable=False), sa.Column("assembly_mode_id", sa.Integer(), sa.ForeignKey("master_assembly_mode.id"), nullable=True), sa.Column("family_id", sa.Integer(), sa.ForeignKey("master_product_family.id"), nullable=True), sa.Column("collection_registry_id", sa.Integer(), sa.ForeignKey("master_collection_registry.id"), nullable=True), sa.Column("shell_status", sa.String(length=32), nullable=False, server_default="planned"), sa.Column("remote_id", sa.String(length=255), nullable=True), sa.Column("payload_json", postgresql.JSONB(astext_type=sa.Text()), nullable=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("channel_code", "connection_id", "shell_sku", name="uq_channel_shell_product_sku"), sa.UniqueConstraint( "channel_code", "connection_id", "variant_group_code", "assembly_mode_id", name="uq_channel_shell_product_group", ), ) op.create_index("ix_channel_shell_product_channel_code", "channel_shell_product", ["channel_code"]) op.create_index("ix_channel_shell_product_connection_id", "channel_shell_product", ["connection_id"]) op.create_index("ix_channel_shell_product_policy_id", "channel_shell_product", ["policy_id"]) op.create_index("ix_channel_shell_product_variation_policy_id", "channel_shell_product", ["variation_policy_id"]) op.create_index("ix_channel_shell_product_shell_sku", "channel_shell_product", ["shell_sku"]) op.create_index("ix_channel_shell_product_base_sku", "channel_shell_product", ["base_sku"]) op.create_index("ix_channel_shell_product_variant_group_code", "channel_shell_product", ["variant_group_code"]) op.create_index("ix_channel_shell_product_assembly_mode_id", "channel_shell_product", ["assembly_mode_id"]) op.create_index("ix_channel_shell_product_family_id", "channel_shell_product", ["family_id"]) op.create_index( "ix_channel_shell_product_collection_registry_id", "channel_shell_product", ["collection_registry_id"], ) op.create_index("ix_channel_shell_product_shell_status", "channel_shell_product", ["shell_status"]) op.create_table( "channel_projection_run", sa.Column("id", sa.Integer(), primary_key=True, autoincrement=True), sa.Column("channel_code", sa.String(length=32), nullable=False), sa.Column("connection_id", sa.Integer(), nullable=True), sa.Column("scope_key", sa.String(length=255), nullable=False, server_default="assigned"), sa.Column("status", sa.String(length=32), nullable=False, server_default="ready"), sa.Column("product_structure", sa.String(length=32), nullable=True), sa.Column("sku_count", sa.Integer(), nullable=False, server_default="0"), sa.Column("started_at", sa.DateTime(timezone=True), nullable=True), sa.Column("completed_at", sa.DateTime(timezone=True), nullable=True), sa.Column("invalidated_at", sa.DateTime(timezone=True), nullable=True), sa.Column("notes", sa.Text(), nullable=True), sa.Column("meta_json", postgresql.JSONB(astext_type=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()), ) op.create_index("ix_channel_projection_run_channel_code", "channel_projection_run", ["channel_code"]) op.create_index("ix_channel_projection_run_connection_id", "channel_projection_run", ["connection_id"]) op.create_index("ix_channel_projection_run_status", "channel_projection_run", ["status"]) op.create_table( "channel_projection_row", sa.Column("id", sa.Integer(), primary_key=True, autoincrement=True), sa.Column("run_id", sa.Integer(), sa.ForeignKey("channel_projection_run.id"), nullable=True), sa.Column("channel_code", sa.String(length=32), nullable=False), sa.Column("connection_id", sa.Integer(), nullable=True), sa.Column("master_sku", sa.String(length=128), nullable=False), sa.Column("channel_sku", sa.String(length=128), nullable=False), sa.Column("product_role", sa.String(length=32), nullable=False, server_default="standalone"), sa.Column("product_structure", sa.String(length=32), nullable=True), sa.Column("projection_status", sa.String(length=32), nullable=False, server_default="ready"), sa.Column("canonical_fields_json", postgresql.JSONB(astext_type=sa.Text()), nullable=True), sa.Column("channel_fields_json", postgresql.JSONB(astext_type=sa.Text()), nullable=True), sa.Column("relation_fields_json", postgresql.JSONB(astext_type=sa.Text()), nullable=True), sa.Column("association_fields_json", postgresql.JSONB(astext_type=sa.Text()), nullable=True), sa.Column("taxonomy_targets_json", postgresql.JSONB(astext_type=sa.Text()), nullable=True), sa.Column("prices_json", postgresql.JSONB(astext_type=sa.Text()), nullable=True), sa.Column("override_codes_json", postgresql.JSONB(astext_type=sa.Text()), nullable=True), sa.Column("price", sa.Numeric(12, 4), nullable=True), sa.Column("payload_hash", sa.String(length=64), nullable=True), sa.Column("source_row_hash", sa.String(length=64), nullable=True), sa.Column("normalized_at", sa.DateTime(timezone=True), nullable=True), sa.Column("published_at", sa.DateTime(timezone=True), 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("channel_code", "connection_id", "master_sku", name="uq_channel_projection_row_master"), sa.UniqueConstraint("channel_code", "connection_id", "channel_sku", name="uq_channel_projection_row_channel"), ) op.create_index("ix_channel_projection_row_run_id", "channel_projection_row", ["run_id"]) op.create_index("ix_channel_projection_row_channel_code", "channel_projection_row", ["channel_code"]) op.create_index("ix_channel_projection_row_connection_id", "channel_projection_row", ["connection_id"]) op.create_index("ix_channel_projection_row_master_sku", "channel_projection_row", ["master_sku"]) op.create_index("ix_channel_projection_row_channel_sku", "channel_projection_row", ["channel_sku"]) op.create_index("ix_channel_projection_row_product_role", "channel_projection_row", ["product_role"]) op.create_index("ix_channel_projection_row_projection_status", "channel_projection_row", ["projection_status"]) op.create_index("ix_channel_projection_row_payload_hash", "channel_projection_row", ["payload_hash"]) op.create_index("ix_channel_projection_row_source_row_hash", "channel_projection_row", ["source_row_hash"]) def downgrade() -> None: op.drop_index( "ix_collection_commerce_profile_sample_category_path_slug", table_name="collection_commerce_profile", ) op.drop_index("ix_collection_commerce_profile_sample_master_sku", table_name="collection_commerce_profile") op.drop_index("ix_collection_commerce_profile_connection_id", table_name="collection_commerce_profile") op.drop_index("ix_collection_commerce_profile_channel_code", table_name="collection_commerce_profile") op.drop_index("ix_collection_commerce_profile_collection_name", table_name="collection_commerce_profile") op.drop_index( "ix_collection_commerce_profile_collection_registry_id", table_name="collection_commerce_profile", ) op.drop_table("collection_commerce_profile") op.drop_index("ix_channel_projection_row_source_row_hash", table_name="channel_projection_row") op.drop_index("ix_channel_projection_row_payload_hash", table_name="channel_projection_row") op.drop_index("ix_channel_projection_row_projection_status", table_name="channel_projection_row") op.drop_index("ix_channel_projection_row_product_role", table_name="channel_projection_row") op.drop_index("ix_channel_projection_row_channel_sku", table_name="channel_projection_row") op.drop_index("ix_channel_projection_row_master_sku", table_name="channel_projection_row") op.drop_index("ix_channel_projection_row_connection_id", table_name="channel_projection_row") op.drop_index("ix_channel_projection_row_channel_code", table_name="channel_projection_row") op.drop_index("ix_channel_projection_row_run_id", table_name="channel_projection_row") op.drop_table("channel_projection_row") op.drop_index("ix_channel_projection_run_status", table_name="channel_projection_run") op.drop_index("ix_channel_projection_run_connection_id", table_name="channel_projection_run") op.drop_index("ix_channel_projection_run_channel_code", table_name="channel_projection_run") op.drop_table("channel_projection_run") op.drop_index("ix_channel_shell_product_shell_status", table_name="channel_shell_product") op.drop_index("ix_channel_shell_product_collection_registry_id", table_name="channel_shell_product") op.drop_index("ix_channel_shell_product_family_id", table_name="channel_shell_product") op.drop_index("ix_channel_shell_product_assembly_mode_id", table_name="channel_shell_product") op.drop_index("ix_channel_shell_product_variant_group_code", table_name="channel_shell_product") op.drop_index("ix_channel_shell_product_base_sku", table_name="channel_shell_product") op.drop_index("ix_channel_shell_product_shell_sku", table_name="channel_shell_product") op.drop_index("ix_channel_shell_product_variation_policy_id", table_name="channel_shell_product") op.drop_index("ix_channel_shell_product_policy_id", table_name="channel_shell_product") op.drop_index("ix_channel_shell_product_connection_id", table_name="channel_shell_product") op.drop_index("ix_channel_shell_product_channel_code", table_name="channel_shell_product") op.drop_table("channel_shell_product") op.drop_index("ix_channel_variation_policy_scope_code", table_name="channel_variation_policy") op.drop_index("ix_channel_variation_policy_scope_kind", table_name="channel_variation_policy") op.drop_index("ix_channel_variation_policy_connection_id", table_name="channel_variation_policy") op.drop_index("ix_channel_variation_policy_channel_code", table_name="channel_variation_policy") op.drop_table("channel_variation_policy") op.drop_index("ix_channel_catalog_policy_scope_scope_code", table_name="channel_catalog_policy_scope") op.drop_index("ix_channel_catalog_policy_scope_scope_kind", table_name="channel_catalog_policy_scope") op.drop_index("ix_channel_catalog_policy_scope_policy_id", table_name="channel_catalog_policy_scope") op.drop_table("channel_catalog_policy_scope") op.drop_index("ix_channel_catalog_policy_connection_id", table_name="channel_catalog_policy") op.drop_index("ix_channel_catalog_policy_channel_code", table_name="channel_catalog_policy") op.drop_table("channel_catalog_policy") op.drop_constraint( "fk_master_product_location_availability_location_id", "master_product_location_availability", type_="foreignkey", ) op.drop_index( "ix_master_product_location_availability_location_id", table_name="master_product_location_availability", ) op.drop_column("master_product_location_availability", "location_id") op.drop_index("ix_catalog_review_queue_status_severity", table_name="catalog_review_queue_item") op.drop_index("ix_catalog_review_queue_item_source_rule_id", table_name="catalog_review_queue_item") op.drop_index("ix_catalog_review_queue_item_status", table_name="catalog_review_queue_item") op.drop_index("ix_catalog_review_queue_item_issue_code", table_name="catalog_review_queue_item") op.drop_index("ix_catalog_review_queue_item_field_name", table_name="catalog_review_queue_item") op.drop_index("ix_catalog_review_queue_item_sku", table_name="catalog_review_queue_item") op.drop_index("ix_catalog_review_queue_item_master_product_id", table_name="catalog_review_queue_item") op.drop_table("catalog_review_queue_item") op.drop_index("ix_catalog_resolved_field_resolution_status", table_name="catalog_resolved_field") op.drop_index("ix_catalog_resolved_field_source_rule_id", table_name="catalog_resolved_field") op.drop_index("ix_catalog_resolved_field_source_kind", table_name="catalog_resolved_field") op.drop_index("ix_catalog_resolved_field_canonical_code", table_name="catalog_resolved_field") op.drop_index("ix_catalog_resolved_field_field_name", table_name="catalog_resolved_field") op.drop_index("ix_catalog_resolved_field_sku", table_name="catalog_resolved_field") op.drop_index("ix_catalog_resolved_field_master_product_id", table_name="catalog_resolved_field") op.drop_table("catalog_resolved_field") op.drop_index("ix_catalog_value_alias_canonical_code", table_name="catalog_value_alias") op.drop_index("ix_catalog_value_alias_field_name", table_name="catalog_value_alias") op.drop_index("ix_catalog_value_alias_entity_kind", table_name="catalog_value_alias") op.drop_table("catalog_value_alias") op.drop_index("ix_catalog_normalization_rule_target_target_code", table_name="catalog_normalization_rule_target") op.drop_index("ix_catalog_normalization_rule_target_target_field", table_name="catalog_normalization_rule_target") op.drop_index("ix_catalog_normalization_rule_target_rule_id", table_name="catalog_normalization_rule_target") op.drop_table("catalog_normalization_rule_target") op.drop_index("ix_catalog_normalization_rule_rule_status", table_name="catalog_normalization_rule") op.drop_index("ix_catalog_normalization_rule_field_name", table_name="catalog_normalization_rule") op.drop_index("ix_catalog_normalization_rule_scope_value", table_name="catalog_normalization_rule") op.drop_index("ix_catalog_normalization_rule_scope_kind", table_name="catalog_normalization_rule") op.drop_index("ix_catalog_normalization_rule_rule_code", table_name="catalog_normalization_rule") op.drop_index("ix_catalog_normalization_rule_profile_id", table_name="catalog_normalization_rule") op.drop_table("catalog_normalization_rule") op.drop_index("ix_catalog_normalization_profile_family_id", table_name="catalog_normalization_profile") op.drop_index("ix_catalog_normalization_profile_code", table_name="catalog_normalization_profile") op.drop_table("catalog_normalization_profile") op.drop_constraint("fk_master_product_assembly_mode_id", "master_product", type_="foreignkey") op.drop_index("ix_master_product_variant_group_code", table_name="master_product") op.drop_index("ix_master_product_base_sku", table_name="master_product") op.drop_index("ix_master_product_assembly_mode_id", table_name="master_product") op.drop_index("ix_master_product_normalization_status", table_name="master_product") op.drop_column("master_product", "variant_group_code") op.drop_column("master_product", "base_sku") op.drop_column("master_product", "assembly_mode_id") op.drop_column("master_product", "normalized_at") op.drop_column("master_product", "normalization_status") op.drop_index("ix_master_location_registry_connection_id", table_name="master_location_registry") op.drop_index("ix_master_location_registry_channel_code", table_name="master_location_registry") op.drop_index("ix_master_location_registry_brand_id", table_name="master_location_registry") op.drop_index("ix_master_location_registry_location_kind", table_name="master_location_registry") op.drop_index("ix_master_location_registry_code", table_name="master_location_registry") op.drop_table("master_location_registry") op.drop_index("ix_master_assembly_mode_code", table_name="master_assembly_mode") op.drop_table("master_assembly_mode")