"""cart001 - Add Cart Plugin tables: merchant_widget_keys, cart_sessions, cart_session_items Creates: - merchant_widget_keys - cart_sessions - cart_session_items Adds partial unique index on (widget_key_id, idempotency_key) WHERE idempotency_key IS NOT NULL. Revision ID: cart001_add_cart_plugin_tables Revises: tax001_taxjar_integration Create Date: 2026-04-06 """ from typing import Union, Sequence import sqlalchemy as sa from alembic import op revision: str = "cart001_add_cart_plugin_tables" down_revision: Union[str, Sequence[str], None] = "tax001_taxjar_integration" branch_labels = None depends_on = None def upgrade() -> None: # ── merchant_widget_keys ────────────────────────────────────────────────── op.create_table( "merchant_widget_keys", sa.Column("id", sa.Integer, primary_key=True, autoincrement=True), sa.Column("merchant_id", sa.Integer, sa.ForeignKey("merchants.id"), nullable=False), sa.Column("display_name", sa.String(100), nullable=False), sa.Column("public_key", sa.String(64), unique=True, nullable=False), sa.Column("webhook_secret_encrypted", sa.Text, nullable=True), sa.Column("allowed_origins", sa.JSON, nullable=True), sa.Column("webhook_url", sa.String(500), nullable=True), sa.Column("checkout_mode", sa.String(20), nullable=False, server_default="auto"), sa.Column("is_active", sa.Boolean, nullable=False, server_default=sa.text("true")), sa.Column("created_at", sa.DateTime, server_default=sa.text("now()"), nullable=False), sa.Column("updated_at", sa.DateTime, nullable=True), sa.Column("revoked_at", sa.DateTime, nullable=True), sa.Column("deleted_at", sa.DateTime, nullable=True), ) op.create_index("ix_merchant_widget_keys_id", "merchant_widget_keys", ["id"]) op.create_index("ix_merchant_widget_keys_public_key", "merchant_widget_keys", ["public_key"]) op.create_index("ix_merchant_widget_keys_merchant_id", "merchant_widget_keys", ["merchant_id"]) # ── cart_sessions ───────────────────────────────────────────────────────── op.create_table( "cart_sessions", sa.Column("id", sa.Integer, primary_key=True, autoincrement=True), sa.Column("token", sa.String(64), unique=True, nullable=False), sa.Column("merchant_id", sa.Integer, sa.ForeignKey("merchants.id"), nullable=False), sa.Column("widget_key_id", sa.Integer, sa.ForeignKey("merchant_widget_keys.id"), nullable=False), sa.Column("status", sa.String(20), nullable=False, server_default="PENDING"), sa.Column("currency", sa.String(10), nullable=False, server_default="USD"), sa.Column("origin", sa.String(500), nullable=True), sa.Column("return_url", sa.String(500), nullable=True), sa.Column("checkout_mode", sa.String(20), nullable=True), sa.Column("customer_name", sa.String(255), nullable=True), sa.Column("customer_email", sa.String(255), nullable=True), sa.Column("payer_user_id", sa.Integer, sa.ForeignKey("users.id"), nullable=True), sa.Column("billing_info", sa.JSON, nullable=True), sa.Column("discount_code", sa.String(100), nullable=True), sa.Column("discount_amount", sa.Integer, nullable=False, server_default="0"), sa.Column("tip_amount", sa.Integer, nullable=False, server_default="0"), sa.Column("tax_amount", sa.Integer, nullable=False, server_default="0"), sa.Column("subtotal", sa.Integer, nullable=False, server_default="0"), sa.Column("total", sa.Integer, nullable=False, server_default="0"), sa.Column("transaction_id", sa.Integer, sa.ForeignKey("transactions.id"), nullable=True), sa.Column("provider_txn_ref", sa.String(255), nullable=True, comment="Provider transaction reference (e.g. Payrix TXN ID)"), sa.Column("metadata", sa.JSON, nullable=True), sa.Column("last_webhook_attempt_at", sa.DateTime, nullable=True), sa.Column("last_webhook_status", sa.Integer, nullable=True), sa.Column("idempotency_key", sa.String(128), nullable=True), sa.Column("expires_at", sa.DateTime, nullable=True), sa.Column("created_at", sa.DateTime, server_default=sa.text("now()"), nullable=False), sa.Column("updated_at", sa.DateTime, nullable=True), sa.CheckConstraint( "status IN ('PENDING','PAID','EXPIRED','CANCELLED')", name="ck_cart_sessions_status", ), ) op.create_index("ix_cart_sessions_id", "cart_sessions", ["id"]) op.create_index("ix_cart_sessions_token", "cart_sessions", ["token"], unique=True) op.create_index("ix_cart_sessions_merchant_id", "cart_sessions", ["merchant_id"]) op.create_index("ix_cart_sessions_widget_key_id", "cart_sessions", ["widget_key_id"]) op.create_index("ix_cart_sessions_status", "cart_sessions", ["status"]) # Partial unique index: only one session per (widget_key_id, idempotency_key) when key is non-null. op.create_index( "ix_cart_sessions_idempotency_partial", "cart_sessions", ["widget_key_id", "idempotency_key"], unique=True, postgresql_where=sa.text("idempotency_key IS NOT NULL"), ) # ── cart_session_items ──────────────────────────────────────────────────── op.create_table( "cart_session_items", sa.Column("id", sa.Integer, primary_key=True, autoincrement=True), sa.Column("session_id", sa.Integer, sa.ForeignKey("cart_sessions.id"), nullable=False), sa.Column("external_id", sa.String(128), nullable=True), sa.Column("name", sa.String(255), nullable=False), sa.Column("description", sa.Text, nullable=True), sa.Column("quantity", sa.Integer, nullable=False, server_default="1"), sa.Column("unit_price", sa.Integer, nullable=False, server_default="0"), sa.Column("image_url", sa.String(500), nullable=True), sa.Column("metadata", sa.JSON, nullable=True), ) op.create_index("ix_cart_session_items_id", "cart_session_items", ["id"]) op.create_index("ix_cart_session_items_session_id", "cart_session_items", ["session_id"]) def downgrade() -> None: # cart_session_items indexes (no named indexes beyond PK — but drop the two we created) op.drop_index("ix_cart_session_items_session_id", table_name="cart_session_items") op.drop_index("ix_cart_session_items_id", table_name="cart_session_items") op.drop_table("cart_session_items") # cart_sessions indexes — drop all named indexes before dropping the table op.drop_index("ix_cart_sessions_idempotency_partial", table_name="cart_sessions") op.drop_index("ix_cart_sessions_status", table_name="cart_sessions") op.drop_index("ix_cart_sessions_widget_key_id", table_name="cart_sessions") op.drop_index("ix_cart_sessions_merchant_id", table_name="cart_sessions") op.drop_index("ix_cart_sessions_token", table_name="cart_sessions") op.drop_index("ix_cart_sessions_id", table_name="cart_sessions") op.drop_table("cart_sessions") # merchant_widget_keys indexes op.drop_index("ix_merchant_widget_keys_merchant_id", table_name="merchant_widget_keys") op.drop_index("ix_merchant_widget_keys_public_key", table_name="merchant_widget_keys") op.drop_index("ix_merchant_widget_keys_id", table_name="merchant_widget_keys") op.drop_table("merchant_widget_keys")