"""hwonb017 - sync schema drift (indexes, constraints, types) Revision ID: 84c34db41e88 Revises: hwonb016_card_types_casing_backfill Create Date: 2026-07-16 11:12:32.546152 """ from typing import Sequence, Union from alembic import op import sqlalchemy as sa from sqlalchemy.dialects import postgresql # revision identifiers, used by Alembic. revision: str = '84c34db41e88' down_revision: Union[str, Sequence[str], None] = 'hwonb016_card_types_casing_backfill' branch_labels: Union[str, Sequence[str], None] = None depends_on: Union[str, Sequence[str], None] = None def safe(fn): """Run one drift-sync DDL op in a savepoint; environments migrated via a different branch history (e.g. d4e5f6a7b8c9 -> 2c8f635b25c5) may already be in the target state, so tolerate "already exists" / "does not exist".""" bind = op.get_bind() sp = bind.begin_nested() try: fn() except sa.exc.ProgrammingError: sp.rollback() else: sp.commit() def drop_fk(table: str, column: str) -> None: """Drop whichever foreign key currently guards ``table.column``. upgrade() recreates these FKs with create_foreign_key(None, ...), so the name is whatever Postgres picked, and it differs between environments that reached this revision by different paths. Alembic cannot compile DROP CONSTRAINT from a None name (CompileError, raised before any SQL is emitted, so safe() can't catch it), hence the catalog lookup. No-op when the table or the constraint is already gone. """ name = op.get_bind().execute(sa.text(""" SELECT con.conname FROM pg_constraint con JOIN pg_class rel ON rel.oid = con.conrelid JOIN pg_attribute att ON att.attrelid = con.conrelid AND att.attnum = ANY(con.conkey) WHERE rel.relname = :t AND att.attname = :c AND con.contype = 'f' LIMIT 1 """), {"t": table, "c": column}).scalar() if name: op.drop_constraint(name, table, type_='foreignkey') def upgrade() -> None: """Upgrade schema.""" # ### commands auto generated by Alembic - please adjust! ### safe(lambda: op.create_index(op.f('ix_admin_audit_log_admin_user_id'), 'admin_audit_log', ['admin_user_id'], unique=False)) safe(lambda: op.create_index(op.f('ix_admin_audit_log_target_id'), 'admin_audit_log', ['target_id'], unique=False)) safe(lambda: op.create_foreign_key(None, 'admin_audit_log', 'users', ['admin_user_id'], ['id'])) safe(lambda: op.drop_constraint(op.f('admin_invites_token_hash_key'), 'admin_invites', type_='unique')) safe(lambda: op.drop_index(op.f('ix_admin_invites_token_hash'), table_name='admin_invites')) safe(lambda: op.create_index(op.f('ix_admin_invites_token_hash'), 'admin_invites', ['token_hash'], unique=True)) safe(lambda: op.drop_constraint(op.f('admin_roles_slug_key'), 'admin_roles', type_='unique')) safe(lambda: op.drop_index(op.f('ix_admin_roles_slug'), table_name='admin_roles')) safe(lambda: op.create_index(op.f('ix_admin_roles_slug'), 'admin_roles', ['slug'], unique=True)) safe(lambda: op.drop_constraint(op.f('admin_user_roles_user_id_key'), 'admin_user_roles', type_='unique')) safe(lambda: op.drop_index(op.f('ix_admin_user_roles_user_id'), table_name='admin_user_roles')) safe(lambda: op.create_index(op.f('ix_admin_user_roles_user_id'), 'admin_user_roles', ['user_id'], unique=True)) safe(lambda: op.alter_column('api_request_logs', 'request_body', existing_type=postgresql.JSONB(astext_type=sa.Text()), type_=sa.JSON(), existing_nullable=True)) safe(lambda: op.alter_column('api_request_logs', 'response_body', existing_type=postgresql.JSONB(astext_type=sa.Text()), type_=sa.JSON(), existing_nullable=True)) safe(lambda: op.alter_column('api_request_logs', 'error_message', existing_type=sa.TEXT(), type_=sa.String(), existing_nullable=True)) safe(lambda: op.alter_column('auth_sessions', 'geo_latitude', existing_type=sa.DOUBLE_PRECISION(precision=53), comment=None, existing_comment='DOUBLE PRECISION; stored as Float in SA', existing_nullable=True)) safe(lambda: op.alter_column('auth_sessions', 'device_type', existing_type=sa.VARCHAR(length=50), comment=None, existing_comment='mobile | tablet | desktop | bot', existing_nullable=True)) safe(lambda: op.alter_column('auth_sessions', 'device_brand', existing_type=sa.VARCHAR(length=100), comment=None, existing_comment='e.g. Apple, Samsung, Dell', existing_nullable=True)) safe(lambda: op.alter_column('cart_sessions', 'provider_txn_ref', existing_type=sa.VARCHAR(length=255), comment=None, existing_comment='Provider transaction reference (e.g. Payrix TXN ID)', existing_nullable=True)) safe(lambda: op.drop_constraint(op.f('cart_sessions_token_key'), 'cart_sessions', type_='unique')) safe(lambda: op.drop_index(op.f('ix_cart_sessions_idempotency_partial'), table_name='cart_sessions', postgresql_where='(idempotency_key IS NOT NULL)')) safe(lambda: op.alter_column('checkout_activities', 'event_type', existing_type=sa.VARCHAR(length=64), type_=sa.String(length=100), existing_nullable=False)) safe(lambda: op.alter_column('checkout_activities', 'description', existing_type=sa.TEXT(), nullable=False)) safe(lambda: op.alter_column('checkout_activities', 'created_at', existing_type=postgresql.TIMESTAMP(timezone=True), type_=sa.DateTime(), existing_nullable=False, existing_server_default=sa.text('now()'))) safe(lambda: op.drop_index(op.f('ix_checkout_activities_id'), table_name='checkout_activities')) safe(lambda: op.alter_column('checkout_line_items', 'description', existing_type=sa.TEXT(), type_=sa.String(length=255), existing_nullable=True)) safe(lambda: op.alter_column('checkout_line_items', 'quantity', existing_type=sa.INTEGER(), type_=sa.Float(), existing_nullable=False, existing_server_default=sa.text('1'))) safe(lambda: op.alter_column('checkout_line_items', 'discount_amount', existing_type=sa.DOUBLE_PRECISION(precision=53), nullable=False, existing_server_default=sa.text('0'))) safe(lambda: op.drop_index(op.f('ix_checkout_line_items_id'), table_name='checkout_line_items')) safe(lambda: op.drop_index(op.f('ix_checkout_line_items_product_id'), table_name='checkout_line_items')) safe(lambda: op.alter_column('checkout_links', 'last_clicked_at', existing_type=postgresql.TIMESTAMP(timezone=True), type_=sa.DateTime(), existing_nullable=True)) safe(lambda: op.alter_column('checkout_links', 'created_at', existing_type=postgresql.TIMESTAMP(timezone=True), type_=sa.DateTime(), existing_nullable=False, existing_server_default=sa.text('now()'))) safe(lambda: op.alter_column('checkout_links', 'revoked_at', existing_type=postgresql.TIMESTAMP(timezone=True), type_=sa.DateTime(), existing_nullable=True)) safe(lambda: op.drop_index(op.f('ix_checkout_links_id'), table_name='checkout_links')) safe(lambda: op.drop_index(op.f('ix_checkout_links_status'), table_name='checkout_links')) safe(lambda: op.alter_column('checkout_settings', 'default_redirect_url', existing_type=sa.TEXT(), type_=sa.String(length=500), existing_nullable=True)) safe(lambda: op.alter_column('checkout_settings', 'default_payer_verification_type', existing_type=sa.TEXT(), type_=sa.String(length=20), existing_nullable=True, existing_server_default=sa.text("'email'::text"))) safe(lambda: op.alter_column('checkout_settings', 'created_at', existing_type=postgresql.TIMESTAMP(timezone=True), type_=sa.DateTime(), existing_nullable=False, existing_server_default=sa.text('now()'))) safe(lambda: op.alter_column('checkout_settings', 'updated_at', existing_type=postgresql.TIMESTAMP(timezone=True), type_=sa.DateTime(), existing_nullable=True)) safe(lambda: op.drop_index(op.f('ix_checkout_settings_id'), table_name='checkout_settings')) safe(lambda: op.drop_constraint(op.f('uq_checkout_settings_merchant_id'), 'checkout_settings', type_='unique')) safe(lambda: op.alter_column('checkouts', 'checkout_literal', existing_type=sa.VARCHAR(length=50), type_=sa.String(length=20), existing_nullable=True)) safe(lambda: op.alter_column('checkouts', 'currency', existing_type=sa.VARCHAR(length=10), type_=sa.String(length=3), existing_nullable=False, existing_server_default=sa.text("'USD'::character varying"))) safe(lambda: op.alter_column('checkouts', 'payment_frequency', existing_type=sa.VARCHAR(length=50), nullable=False)) safe(lambda: op.alter_column('checkouts', 'expires_at', existing_type=postgresql.TIMESTAMP(timezone=True), type_=sa.DateTime(), existing_nullable=True)) safe(lambda: op.alter_column('checkouts', 'redirect_url', existing_type=sa.TEXT(), type_=sa.String(length=500), existing_nullable=True)) safe(lambda: op.alter_column('checkouts', 'created_at', existing_type=postgresql.TIMESTAMP(timezone=True), type_=sa.DateTime(), existing_nullable=False, existing_server_default=sa.text('now()'))) safe(lambda: op.alter_column('checkouts', 'updated_at', existing_type=postgresql.TIMESTAMP(timezone=True), type_=sa.DateTime(), existing_nullable=True)) safe(lambda: op.alter_column('checkouts', 'deleted_at', existing_type=postgresql.TIMESTAMP(timezone=True), type_=sa.DateTime(), existing_nullable=True)) safe(lambda: op.drop_index(op.f('ix_checkouts_status'), table_name='checkouts')) safe(lambda: op.alter_column('email_change_requests', 'user_id', existing_type=sa.INTEGER(), comment=None, existing_comment='FK → users.id; multiple in-flight requests per user are allowed', existing_nullable=False)) safe(lambda: op.alter_column('email_change_requests', 'new_email', existing_type=sa.VARCHAR(length=255), comment=None, existing_comment='The email address the user wants to change to', existing_nullable=False)) safe(lambda: op.alter_column('email_change_requests', 'token_hash', existing_type=sa.VARCHAR(length=64), comment=None, existing_comment='SHA-256 hex digest of the one-time confirmation token', existing_nullable=False)) safe(lambda: op.alter_column('email_change_requests', 'expires_at', existing_type=postgresql.TIMESTAMP(timezone=True), comment=None, existing_comment='Token expiry; typically now() + 24h', existing_nullable=False)) safe(lambda: op.alter_column('email_change_requests', 'confirmed_at', existing_type=postgresql.TIMESTAMP(timezone=True), comment=None, existing_comment='Populated when the user clicks the confirmation link; NULL = pending', existing_nullable=True)) safe(lambda: op.drop_index(op.f('idx_email_change_requests_expires_pending'), table_name='email_change_requests', postgresql_where='(confirmed_at IS NULL)')) safe(lambda: op.drop_index(op.f('idx_email_change_requests_token_hash'), table_name='email_change_requests')) safe(lambda: op.drop_constraint(op.f('uq_email_change_requests_token_hash'), 'email_change_requests', type_='unique')) safe(lambda: op.create_index(op.f('ix_email_change_requests_token_hash'), 'email_change_requests', ['token_hash'], unique=True)) safe(lambda: op.create_index(op.f('ix_email_change_requests_user_id'), 'email_change_requests', ['user_id'], unique=False)) safe(lambda: op.drop_table_comment( 'email_change_requests', existing_comment='Tracks pending email-change confirmation flows. A token is generated server-side, hashed (SHA-256) before storage, and emailed in plaintext to new_email. Confirmed by matching hash.', schema=None )) safe(lambda: op.drop_index(op.f('ix_interchange_rates_card_network'), table_name='interchange_rates')) safe(lambda: op.drop_constraint(op.f('uq_api_keys_key_id'), 'merchant_api_keys', type_='unique')) safe(lambda: op.drop_index(op.f('ix_merchant_api_keys_key_id'), table_name='merchant_api_keys')) safe(lambda: op.create_index(op.f('ix_merchant_api_keys_key_id'), 'merchant_api_keys', ['key_id'], unique=True)) safe(lambda: op.alter_column('merchant_audit_log', 'created_at', existing_type=postgresql.TIMESTAMP(), nullable=False, existing_server_default=sa.text('now()'))) safe(lambda: op.drop_constraint(op.f('uq_merchant_billing_methods_merchant'), 'merchant_billing_methods', type_='unique')) safe(lambda: op.drop_index(op.f('ix_merchant_billing_methods_merchant_id'), table_name='merchant_billing_methods')) safe(lambda: op.create_index(op.f('ix_merchant_billing_methods_merchant_id'), 'merchant_billing_methods', ['merchant_id'], unique=True)) safe(lambda: op.alter_column('merchant_invites', 'created_at', existing_type=postgresql.TIMESTAMP(), nullable=False, existing_server_default=sa.text('now()'))) safe(lambda: op.create_index(op.f('ix_merchant_onboarding_accounts_id'), 'merchant_onboarding_accounts', ['id'], unique=False)) safe(lambda: op.drop_constraint(op.f('merchant_onboarding_accounts_application_id_fkey'), 'merchant_onboarding_accounts', type_='foreignkey')) safe(lambda: op.create_foreign_key(None, 'merchant_onboarding_accounts', 'merchant_onboarding_applications', ['application_id'], ['id'])) safe(lambda: op.create_index(op.f('ix_merchant_onboarding_applications_id'), 'merchant_onboarding_applications', ['id'], unique=False)) safe(lambda: op.drop_constraint(op.f('merchant_onboarding_applications_merchant_id_fkey'), 'merchant_onboarding_applications', type_='foreignkey')) safe(lambda: op.create_foreign_key(None, 'merchant_onboarding_applications', 'merchants', ['merchant_id'], ['id'])) safe(lambda: op.create_index(op.f('ix_merchant_onboarding_documents_id'), 'merchant_onboarding_documents', ['id'], unique=False)) safe(lambda: op.drop_constraint(op.f('merchant_onboarding_documents_application_id_fkey'), 'merchant_onboarding_documents', type_='foreignkey')) safe(lambda: op.create_foreign_key(None, 'merchant_onboarding_documents', 'merchant_onboarding_applications', ['application_id'], ['id'])) safe(lambda: op.create_index(op.f('ix_merchant_onboarding_owners_id'), 'merchant_onboarding_owners', ['id'], unique=False)) safe(lambda: op.drop_constraint(op.f('merchant_onboarding_owners_application_id_fkey'), 'merchant_onboarding_owners', type_='foreignkey')) safe(lambda: op.create_foreign_key(None, 'merchant_onboarding_owners', 'merchant_onboarding_applications', ['application_id'], ['id'])) safe(lambda: op.drop_index(op.f('ix_merchant_webhook_deliveries_endpoint_id'), table_name='merchant_webhook_deliveries')) safe(lambda: op.create_index(op.f('ix_merchant_webhook_deliveries_webhook_endpoint_id'), 'merchant_webhook_deliveries', ['webhook_endpoint_id'], unique=False)) safe(lambda: op.drop_constraint(op.f('fk_webhook_deliveries_endpoint_id'), 'merchant_webhook_deliveries', type_='foreignkey')) safe(lambda: op.create_foreign_key(None, 'merchant_webhook_deliveries', 'merchant_webhook_endpoints', ['webhook_endpoint_id'], ['id'])) safe(lambda: op.drop_constraint(op.f('merchant_widget_keys_public_key_key'), 'merchant_widget_keys', type_='unique')) safe(lambda: op.drop_index(op.f('ix_merchant_widget_keys_public_key'), table_name='merchant_widget_keys')) safe(lambda: op.create_index(op.f('ix_merchant_widget_keys_public_key'), 'merchant_widget_keys', ['public_key'], unique=True)) safe(lambda: op.alter_column('password_policies', 'id', existing_type=sa.INTEGER(), comment=None, existing_comment='Surrogate primary key', existing_nullable=False, autoincrement=True)) safe(lambda: op.alter_column('password_policies', 'merchant_id', existing_type=sa.INTEGER(), comment=None, existing_comment='NULL = global default policy; UNIQUE NULLS DISTINCT enforced below', existing_nullable=True)) safe(lambda: op.alter_column('password_policies', 'max_age_days', existing_type=sa.INTEGER(), comment=None, existing_comment='NULL = password never expires', existing_nullable=True)) safe(lambda: op.alter_column('password_policies', 'grace_period_days', existing_type=sa.INTEGER(), comment=None, existing_comment='Days the user may still log in after password expiry before forced reset', existing_nullable=False, existing_server_default=sa.text('7'))) safe(lambda: op.alter_column('password_policies', 'history_count', existing_type=sa.INTEGER(), comment=None, existing_comment='Number of previous password hashes retained to prevent reuse', existing_nullable=False, existing_server_default=sa.text('5'))) safe(lambda: op.alter_column('password_policies', 'deleted_at', existing_type=postgresql.TIMESTAMP(timezone=True), comment=None, existing_comment='Soft delete timestamp', existing_nullable=True)) safe(lambda: op.drop_index(op.f('uq_password_policies_global_default'), table_name='password_policies', postgresql_where='((merchant_id IS NULL) AND (deleted_at IS NULL))')) safe(lambda: op.drop_index(op.f('uq_password_policies_merchant_id'), table_name='password_policies', postgresql_where='((merchant_id IS NOT NULL) AND (deleted_at IS NULL))')) safe(lambda: op.create_index(op.f('ix_password_policies_merchant_id'), 'password_policies', ['merchant_id'], unique=False)) safe(lambda: op.drop_table_comment( 'password_policies', existing_comment='Per-merchant password complexity and rotation policy. The row with merchant_id IS NULL is the global default applied to any merchant that has not defined a custom policy.', schema=None )) safe(lambda: op.alter_column('payment_requests', 'device_info', existing_type=postgresql.JSONB(astext_type=sa.Text()), type_=sa.JSON(), existing_nullable=True)) safe(lambda: op.alter_column('payment_requests_authorizations', 'status', existing_type=sa.VARCHAR(length=50), nullable=True, existing_server_default=sa.text("'PENDING'::character varying"))) safe(lambda: op.drop_index(op.f('uq_plans_name_active'), table_name='plans', postgresql_where='(deleted_at IS NULL)')) safe(lambda: op.drop_index(op.f('ix_pricing_templates_is_active'), table_name='pricing_templates')) safe(lambda: op.drop_index(op.f('uq_products_category_merchant_code'), table_name='products_category', postgresql_where='(deleted_at IS NULL)')) safe(lambda: op.alter_column('provider_transactions', 'provider_txn_id', existing_type=sa.VARCHAR(length=255), comment='TSYS transactionID — use for refunds/voids', existing_nullable=True)) safe(lambda: op.alter_column('provider_transactions', 'host_reference_number', existing_type=sa.VARCHAR(length=255), comment='TSYS hostReferenceNumber (bank ref, not usable for refunds)', existing_nullable=True)) safe(lambda: op.alter_column('provider_transactions', 'response_code', existing_type=sa.VARCHAR(length=50), comment='approvalCode / responseCode from provider', existing_nullable=True)) safe(lambda: op.alter_column('provider_transactions', 'card_type', existing_type=sa.VARCHAR(length=50), comment='e.g. VISA, MC, AMEX', existing_nullable=True)) safe(lambda: op.alter_column('provider_transactions', 'masked_card_number', existing_type=sa.VARCHAR(length=50), comment='Masked/last-4 card number from provider response', existing_nullable=True)) safe(lambda: op.alter_column('roles', 'created_at', existing_type=postgresql.TIMESTAMP(), nullable=False, existing_server_default=sa.text('now()'))) safe(lambda: op.drop_constraint(op.f('uq_roles_slug'), 'roles', type_='unique')) safe(lambda: op.alter_column('roles_permissions', 'role_id', existing_type=sa.INTEGER(), nullable=True)) safe(lambda: op.alter_column('roles_permissions', 'permission_id', existing_type=sa.INTEGER(), nullable=True)) safe(lambda: op.drop_index(op.f('ix_scheduler_logs_created_at'), table_name='scheduler_logs')) safe(lambda: op.drop_index(op.f('ix_scheduler_logs_task_name_created_at'), table_name='scheduler_logs')) safe(lambda: op.drop_index(op.f('ix_scheduler_logs_task_name_run_status'), table_name='scheduler_logs')) safe(lambda: op.alter_column('site_templates', 'id', existing_type=sa.INTEGER(), comment=None, existing_comment='Surrogate primary key', existing_nullable=False, autoincrement=True)) safe(lambda: op.drop_table_comment( 'site_templates', existing_comment='Platform-level template store (PRD-010). Replaces notification_templates. No merchant_id — all templates are shared platform defaults rendered with per-merchant branding injected at runtime.', schema=None )) safe(lambda: op.drop_index(op.f('ix_subscriptions_merchant_status_next_billing'), table_name='subscriptions')) safe(lambda: op.drop_index(op.f('ix_subscriptions_status_dunning_next_retry'), table_name='subscriptions')) safe(lambda: op.drop_constraint(op.f('uq_subscriptions_subscription_id'), 'subscriptions', type_='unique')) safe(lambda: op.drop_index(op.f('ix_transactions_deleted_at'), table_name='transactions')) safe(lambda: op.drop_index(op.f('ix_transactions_fee_settlement_run_id'), table_name='transactions', postgresql_where='(fee_settlement_run_id IS NOT NULL)')) safe(lambda: op.drop_index(op.f('ix_transactions_notes_note_id'), table_name='transactions_notes')) safe(lambda: op.drop_index(op.f('ix_transactions_notes_transaction_id'), table_name='transactions_notes')) safe(lambda: op.alter_column('user_notification_preferences', 'user_id', existing_type=sa.INTEGER(), comment=None, existing_comment='FK → users.id; one-to-one via UNIQUE constraint', existing_nullable=False)) safe(lambda: op.alter_column('user_notification_preferences', 'updated_at', existing_type=postgresql.TIMESTAMP(timezone=True), nullable=True, existing_server_default=sa.text('now()'))) safe(lambda: op.drop_constraint(op.f('uq_user_notification_preferences_user_id'), 'user_notification_preferences', type_='unique')) safe(lambda: op.create_index(op.f('ix_user_notification_preferences_user_id'), 'user_notification_preferences', ['user_id'], unique=True)) safe(lambda: op.drop_table_comment( 'user_notification_preferences', existing_comment='One-to-one with users; stores per-user notification opt-in flags', schema=None )) safe(lambda: op.alter_column('user_password_history', 'id', existing_type=sa.INTEGER(), comment=None, existing_comment='Surrogate primary key', existing_nullable=False, autoincrement=True)) safe(lambda: op.alter_column('user_password_history', 'user_id', existing_type=sa.INTEGER(), comment=None, existing_comment='FK → users.id; CASCADE DELETE', existing_nullable=False)) safe(lambda: op.alter_column('user_password_history', 'hashed_password', existing_type=sa.VARCHAR(length=255), comment=None, existing_comment='bcrypt hash — never plaintext', existing_nullable=False)) safe(lambda: op.drop_index(op.f('idx_user_password_history_user_created'), table_name='user_password_history')) safe(lambda: op.create_index(op.f('ix_user_password_history_user_id'), 'user_password_history', ['user_id'], unique=False)) safe(lambda: op.drop_table_comment( 'user_password_history', existing_comment='Stores hashed copies of previous passwords to enforce non-reuse policy', schema=None )) safe(lambda: op.drop_constraint(op.f('user_preferences_user_id_key'), 'user_preferences', type_='unique')) safe(lambda: op.alter_column('user_roles', 'created_at', existing_type=postgresql.TIMESTAMP(), nullable=False, existing_server_default=sa.text('now()'))) safe(lambda: op.alter_column('users', 'address_line_1', existing_type=sa.VARCHAR(length=255), comment=None, existing_comment='Primary street address', existing_nullable=True)) safe(lambda: op.alter_column('users', 'address_line_2', existing_type=sa.VARCHAR(length=255), comment=None, existing_comment='Apartment, suite, unit, etc.', existing_nullable=True)) safe(lambda: op.alter_column('users', 'country', existing_type=sa.VARCHAR(length=100), comment=None, existing_comment='ISO country code; defaults to US', existing_nullable=True, existing_server_default=sa.text("'US'::character varying"))) safe(lambda: op.alter_column('users', 'avatar_file_id', existing_type=sa.INTEGER(), comment=None, existing_comment='FK to files table; preferred source for avatar image', existing_nullable=True)) safe(lambda: op.alter_column('users', 'twofa_enabled', existing_type=sa.BOOLEAN(), comment=None, existing_comment='Whether TOTP/2FA is currently enabled for this user', existing_nullable=False, existing_server_default=sa.text('false'))) safe(lambda: op.alter_column('users', 'last_password_changed_at', existing_type=postgresql.TIMESTAMP(timezone=True), comment=None, existing_comment='Timestamp of the most recent password change; NULL until first explicit change', existing_nullable=True)) safe(lambda: op.drop_constraint(op.f('webhook_events_provider_event_id_key'), 'webhook_events', type_='unique')) # ### end Alembic commands ### def downgrade() -> None: """Downgrade schema.""" # ### commands auto generated by Alembic - please adjust! ### op.create_unique_constraint(op.f('webhook_events_provider_event_id_key'), 'webhook_events', ['provider_event_id'], postgresql_nulls_not_distinct=False) op.alter_column('users', 'last_password_changed_at', existing_type=postgresql.TIMESTAMP(timezone=True), comment='Timestamp of the most recent password change; NULL until first explicit change', existing_nullable=True) op.alter_column('users', 'twofa_enabled', existing_type=sa.BOOLEAN(), comment='Whether TOTP/2FA is currently enabled for this user', existing_nullable=False, existing_server_default=sa.text('false')) op.alter_column('users', 'avatar_file_id', existing_type=sa.INTEGER(), comment='FK to files table; preferred source for avatar image', existing_nullable=True) op.alter_column('users', 'country', existing_type=sa.VARCHAR(length=100), comment='ISO country code; defaults to US', existing_nullable=True, existing_server_default=sa.text("'US'::character varying")) op.alter_column('users', 'address_line_2', existing_type=sa.VARCHAR(length=255), comment='Apartment, suite, unit, etc.', existing_nullable=True) op.alter_column('users', 'address_line_1', existing_type=sa.VARCHAR(length=255), comment='Primary street address', existing_nullable=True) op.alter_column('user_roles', 'created_at', existing_type=postgresql.TIMESTAMP(), nullable=True, existing_server_default=sa.text('now()')) op.create_unique_constraint(op.f('user_preferences_user_id_key'), 'user_preferences', ['user_id'], postgresql_nulls_not_distinct=False) op.create_table_comment( 'user_password_history', 'Stores hashed copies of previous passwords to enforce non-reuse policy', existing_comment=None, schema=None ) op.drop_index(op.f('ix_user_password_history_user_id'), table_name='user_password_history') op.create_index(op.f('idx_user_password_history_user_created'), 'user_password_history', ['user_id', sa.literal_column('created_at DESC')], unique=False) op.alter_column('user_password_history', 'hashed_password', existing_type=sa.VARCHAR(length=255), comment='bcrypt hash — never plaintext', existing_nullable=False) op.alter_column('user_password_history', 'user_id', existing_type=sa.INTEGER(), comment='FK → users.id; CASCADE DELETE', existing_nullable=False) op.alter_column('user_password_history', 'id', existing_type=sa.INTEGER(), comment='Surrogate primary key', existing_nullable=False, autoincrement=True) op.create_table_comment( 'user_notification_preferences', 'One-to-one with users; stores per-user notification opt-in flags', existing_comment=None, schema=None ) op.drop_index(op.f('ix_user_notification_preferences_user_id'), table_name='user_notification_preferences') op.create_unique_constraint(op.f('uq_user_notification_preferences_user_id'), 'user_notification_preferences', ['user_id'], postgresql_nulls_not_distinct=False) op.alter_column('user_notification_preferences', 'updated_at', existing_type=postgresql.TIMESTAMP(timezone=True), nullable=False, existing_server_default=sa.text('now()')) op.alter_column('user_notification_preferences', 'user_id', existing_type=sa.INTEGER(), comment='FK → users.id; one-to-one via UNIQUE constraint', existing_nullable=False) op.create_index(op.f('ix_transactions_notes_transaction_id'), 'transactions_notes', ['transaction_id'], unique=False) op.create_index(op.f('ix_transactions_notes_note_id'), 'transactions_notes', ['note_id'], unique=False) op.create_index(op.f('ix_transactions_fee_settlement_run_id'), 'transactions', ['fee_settlement_run_id'], unique=False, postgresql_where='(fee_settlement_run_id IS NOT NULL)') op.create_index(op.f('ix_transactions_deleted_at'), 'transactions', ['deleted_at'], unique=False) op.create_unique_constraint(op.f('uq_subscriptions_subscription_id'), 'subscriptions', ['subscription_id'], postgresql_nulls_not_distinct=False) op.create_index(op.f('ix_subscriptions_status_dunning_next_retry'), 'subscriptions', ['status', 'dunning_next_retry_at'], unique=False) op.create_index(op.f('ix_subscriptions_merchant_status_next_billing'), 'subscriptions', ['merchant_id', 'status', 'next_billing_date'], unique=False) op.create_table_comment( 'site_templates', 'Platform-level template store (PRD-010). Replaces notification_templates. No merchant_id — all templates are shared platform defaults rendered with per-merchant branding injected at runtime.', existing_comment=None, schema=None ) op.alter_column('site_templates', 'id', existing_type=sa.INTEGER(), comment='Surrogate primary key', existing_nullable=False, autoincrement=True) op.create_index(op.f('ix_scheduler_logs_task_name_run_status'), 'scheduler_logs', ['task_name', 'run_status'], unique=False) op.create_index(op.f('ix_scheduler_logs_task_name_created_at'), 'scheduler_logs', ['task_name', sa.literal_column('created_at DESC')], unique=False) op.create_index(op.f('ix_scheduler_logs_created_at'), 'scheduler_logs', ['created_at'], unique=False) op.alter_column('roles_permissions', 'permission_id', existing_type=sa.INTEGER(), nullable=False) op.alter_column('roles_permissions', 'role_id', existing_type=sa.INTEGER(), nullable=False) op.create_unique_constraint(op.f('uq_roles_slug'), 'roles', ['slug'], postgresql_nulls_not_distinct=False) op.alter_column('roles', 'created_at', existing_type=postgresql.TIMESTAMP(), nullable=True, existing_server_default=sa.text('now()')) op.alter_column('provider_transactions', 'masked_card_number', existing_type=sa.VARCHAR(length=50), comment=None, existing_comment='Masked/last-4 card number from provider response', existing_nullable=True) op.alter_column('provider_transactions', 'card_type', existing_type=sa.VARCHAR(length=50), comment=None, existing_comment='e.g. VISA, MC, AMEX', existing_nullable=True) op.alter_column('provider_transactions', 'response_code', existing_type=sa.VARCHAR(length=50), comment=None, existing_comment='approvalCode / responseCode from provider', existing_nullable=True) op.alter_column('provider_transactions', 'host_reference_number', existing_type=sa.VARCHAR(length=255), comment=None, existing_comment='TSYS hostReferenceNumber (bank ref, not usable for refunds)', existing_nullable=True) op.alter_column('provider_transactions', 'provider_txn_id', existing_type=sa.VARCHAR(length=255), comment=None, existing_comment='TSYS transactionID — use for refunds/voids', existing_nullable=True) op.create_index(op.f('uq_products_category_merchant_code'), 'products_category', ['merchant_id', 'code'], unique=True, postgresql_where='(deleted_at IS NULL)') op.create_index(op.f('ix_pricing_templates_is_active'), 'pricing_templates', ['is_active'], unique=False) op.create_index(op.f('uq_plans_name_active'), 'plans', ['name'], unique=True, postgresql_where='(deleted_at IS NULL)') op.alter_column('payment_requests_authorizations', 'status', existing_type=sa.VARCHAR(length=50), nullable=False, existing_server_default=sa.text("'PENDING'::character varying")) op.alter_column('payment_requests', 'device_info', existing_type=sa.JSON(), type_=postgresql.JSONB(astext_type=sa.Text()), existing_nullable=True) op.create_table_comment( 'password_policies', 'Per-merchant password complexity and rotation policy. The row with merchant_id IS NULL is the global default applied to any merchant that has not defined a custom policy.', existing_comment=None, schema=None ) op.drop_index(op.f('ix_password_policies_merchant_id'), table_name='password_policies') op.create_index(op.f('uq_password_policies_merchant_id'), 'password_policies', ['merchant_id'], unique=True, postgresql_where='((merchant_id IS NOT NULL) AND (deleted_at IS NULL))') op.create_index(op.f('uq_password_policies_global_default'), 'password_policies', [sa.literal_column('(merchant_id IS NULL)')], unique=True, postgresql_where='((merchant_id IS NULL) AND (deleted_at IS NULL))') op.alter_column('password_policies', 'deleted_at', existing_type=postgresql.TIMESTAMP(timezone=True), comment='Soft delete timestamp', existing_nullable=True) op.alter_column('password_policies', 'history_count', existing_type=sa.INTEGER(), comment='Number of previous password hashes retained to prevent reuse', existing_nullable=False, existing_server_default=sa.text('5')) op.alter_column('password_policies', 'grace_period_days', existing_type=sa.INTEGER(), comment='Days the user may still log in after password expiry before forced reset', existing_nullable=False, existing_server_default=sa.text('7')) op.alter_column('password_policies', 'max_age_days', existing_type=sa.INTEGER(), comment='NULL = password never expires', existing_nullable=True) op.alter_column('password_policies', 'merchant_id', existing_type=sa.INTEGER(), comment='NULL = global default policy; UNIQUE NULLS DISTINCT enforced below', existing_nullable=True) op.alter_column('password_policies', 'id', existing_type=sa.INTEGER(), comment='Surrogate primary key', existing_nullable=False, autoincrement=True) op.drop_index(op.f('ix_merchant_widget_keys_public_key'), table_name='merchant_widget_keys') op.create_index(op.f('ix_merchant_widget_keys_public_key'), 'merchant_widget_keys', ['public_key'], unique=False) op.create_unique_constraint(op.f('merchant_widget_keys_public_key_key'), 'merchant_widget_keys', ['public_key'], postgresql_nulls_not_distinct=False) drop_fk('merchant_webhook_deliveries', 'webhook_endpoint_id') safe(lambda: op.create_foreign_key(op.f('fk_webhook_deliveries_endpoint_id'), 'merchant_webhook_deliveries', 'merchant_webhook_endpoints', ['webhook_endpoint_id'], ['id'], ondelete='CASCADE')) op.drop_index(op.f('ix_merchant_webhook_deliveries_webhook_endpoint_id'), table_name='merchant_webhook_deliveries') op.create_index(op.f('ix_merchant_webhook_deliveries_endpoint_id'), 'merchant_webhook_deliveries', ['webhook_endpoint_id'], unique=False) drop_fk('merchant_onboarding_owners', 'application_id') safe(lambda: op.create_foreign_key(op.f('merchant_onboarding_owners_application_id_fkey'), 'merchant_onboarding_owners', 'merchant_onboarding_applications', ['application_id'], ['id'], ondelete='CASCADE')) op.drop_index(op.f('ix_merchant_onboarding_owners_id'), table_name='merchant_onboarding_owners') drop_fk('merchant_onboarding_documents', 'application_id') safe(lambda: op.create_foreign_key(op.f('merchant_onboarding_documents_application_id_fkey'), 'merchant_onboarding_documents', 'merchant_onboarding_applications', ['application_id'], ['id'], ondelete='CASCADE')) op.drop_index(op.f('ix_merchant_onboarding_documents_id'), table_name='merchant_onboarding_documents') drop_fk('merchant_onboarding_applications', 'merchant_id') safe(lambda: op.create_foreign_key(op.f('merchant_onboarding_applications_merchant_id_fkey'), 'merchant_onboarding_applications', 'merchants', ['merchant_id'], ['id'], ondelete='CASCADE')) op.drop_index(op.f('ix_merchant_onboarding_applications_id'), table_name='merchant_onboarding_applications') drop_fk('merchant_onboarding_accounts', 'application_id') safe(lambda: op.create_foreign_key(op.f('merchant_onboarding_accounts_application_id_fkey'), 'merchant_onboarding_accounts', 'merchant_onboarding_applications', ['application_id'], ['id'], ondelete='CASCADE')) op.drop_index(op.f('ix_merchant_onboarding_accounts_id'), table_name='merchant_onboarding_accounts') op.alter_column('merchant_invites', 'created_at', existing_type=postgresql.TIMESTAMP(), nullable=True, existing_server_default=sa.text('now()')) op.drop_index(op.f('ix_merchant_billing_methods_merchant_id'), table_name='merchant_billing_methods') op.create_index(op.f('ix_merchant_billing_methods_merchant_id'), 'merchant_billing_methods', ['merchant_id'], unique=False) op.create_unique_constraint(op.f('uq_merchant_billing_methods_merchant'), 'merchant_billing_methods', ['merchant_id'], postgresql_nulls_not_distinct=False) op.alter_column('merchant_audit_log', 'created_at', existing_type=postgresql.TIMESTAMP(), nullable=True, existing_server_default=sa.text('now()')) op.drop_index(op.f('ix_merchant_api_keys_key_id'), table_name='merchant_api_keys') op.create_index(op.f('ix_merchant_api_keys_key_id'), 'merchant_api_keys', ['key_id'], unique=False) op.create_unique_constraint(op.f('uq_api_keys_key_id'), 'merchant_api_keys', ['key_id'], postgresql_nulls_not_distinct=False) op.create_index(op.f('ix_interchange_rates_card_network'), 'interchange_rates', ['card_network'], unique=False) op.create_table_comment( 'email_change_requests', 'Tracks pending email-change confirmation flows. A token is generated server-side, hashed (SHA-256) before storage, and emailed in plaintext to new_email. Confirmed by matching hash.', existing_comment=None, schema=None ) op.drop_index(op.f('ix_email_change_requests_user_id'), table_name='email_change_requests') op.drop_index(op.f('ix_email_change_requests_token_hash'), table_name='email_change_requests') op.create_unique_constraint(op.f('uq_email_change_requests_token_hash'), 'email_change_requests', ['token_hash'], postgresql_nulls_not_distinct=False) op.create_index(op.f('idx_email_change_requests_token_hash'), 'email_change_requests', ['token_hash'], unique=False) op.create_index(op.f('idx_email_change_requests_expires_pending'), 'email_change_requests', ['expires_at'], unique=False, postgresql_where='(confirmed_at IS NULL)') op.alter_column('email_change_requests', 'confirmed_at', existing_type=postgresql.TIMESTAMP(timezone=True), comment='Populated when the user clicks the confirmation link; NULL = pending', existing_nullable=True) op.alter_column('email_change_requests', 'expires_at', existing_type=postgresql.TIMESTAMP(timezone=True), comment='Token expiry; typically now() + 24h', existing_nullable=False) op.alter_column('email_change_requests', 'token_hash', existing_type=sa.VARCHAR(length=64), comment='SHA-256 hex digest of the one-time confirmation token', existing_nullable=False) op.alter_column('email_change_requests', 'new_email', existing_type=sa.VARCHAR(length=255), comment='The email address the user wants to change to', existing_nullable=False) op.alter_column('email_change_requests', 'user_id', existing_type=sa.INTEGER(), comment='FK → users.id; multiple in-flight requests per user are allowed', existing_nullable=False) op.create_index(op.f('ix_checkouts_status'), 'checkouts', ['status'], unique=False) op.alter_column('checkouts', 'deleted_at', existing_type=sa.DateTime(), type_=postgresql.TIMESTAMP(timezone=True), existing_nullable=True) op.alter_column('checkouts', 'updated_at', existing_type=sa.DateTime(), type_=postgresql.TIMESTAMP(timezone=True), existing_nullable=True) op.alter_column('checkouts', 'created_at', existing_type=sa.DateTime(), type_=postgresql.TIMESTAMP(timezone=True), existing_nullable=False, existing_server_default=sa.text('now()')) op.alter_column('checkouts', 'redirect_url', existing_type=sa.String(length=500), type_=sa.TEXT(), existing_nullable=True) op.alter_column('checkouts', 'expires_at', existing_type=sa.DateTime(), type_=postgresql.TIMESTAMP(timezone=True), existing_nullable=True) op.alter_column('checkouts', 'payment_frequency', existing_type=sa.VARCHAR(length=50), nullable=True) op.alter_column('checkouts', 'currency', existing_type=sa.String(length=3), type_=sa.VARCHAR(length=10), existing_nullable=False, existing_server_default=sa.text("'USD'::character varying")) op.alter_column('checkouts', 'checkout_literal', existing_type=sa.String(length=20), type_=sa.VARCHAR(length=50), existing_nullable=True) op.create_unique_constraint(op.f('uq_checkout_settings_merchant_id'), 'checkout_settings', ['merchant_id'], postgresql_nulls_not_distinct=False) op.create_index(op.f('ix_checkout_settings_id'), 'checkout_settings', ['id'], unique=False) op.alter_column('checkout_settings', 'updated_at', existing_type=sa.DateTime(), type_=postgresql.TIMESTAMP(timezone=True), existing_nullable=True) op.alter_column('checkout_settings', 'created_at', existing_type=sa.DateTime(), type_=postgresql.TIMESTAMP(timezone=True), existing_nullable=False, existing_server_default=sa.text('now()')) op.alter_column('checkout_settings', 'default_payer_verification_type', existing_type=sa.String(length=20), type_=sa.TEXT(), existing_nullable=True, existing_server_default=sa.text("'email'::text")) op.alter_column('checkout_settings', 'default_redirect_url', existing_type=sa.String(length=500), type_=sa.TEXT(), existing_nullable=True) op.create_index(op.f('ix_checkout_links_status'), 'checkout_links', ['status'], unique=False) op.create_index(op.f('ix_checkout_links_id'), 'checkout_links', ['id'], unique=False) op.alter_column('checkout_links', 'revoked_at', existing_type=sa.DateTime(), type_=postgresql.TIMESTAMP(timezone=True), existing_nullable=True) op.alter_column('checkout_links', 'created_at', existing_type=sa.DateTime(), type_=postgresql.TIMESTAMP(timezone=True), existing_nullable=False, existing_server_default=sa.text('now()')) op.alter_column('checkout_links', 'last_clicked_at', existing_type=sa.DateTime(), type_=postgresql.TIMESTAMP(timezone=True), existing_nullable=True) op.create_index(op.f('ix_checkout_line_items_product_id'), 'checkout_line_items', ['product_id'], unique=False) op.create_index(op.f('ix_checkout_line_items_id'), 'checkout_line_items', ['id'], unique=False) op.alter_column('checkout_line_items', 'discount_amount', existing_type=sa.DOUBLE_PRECISION(precision=53), nullable=True, existing_server_default=sa.text('0')) op.alter_column('checkout_line_items', 'quantity', existing_type=sa.Float(), type_=sa.INTEGER(), existing_nullable=False, existing_server_default=sa.text('1')) op.alter_column('checkout_line_items', 'description', existing_type=sa.String(length=255), type_=sa.TEXT(), existing_nullable=True) op.create_index(op.f('ix_checkout_activities_id'), 'checkout_activities', ['id'], unique=False) op.alter_column('checkout_activities', 'created_at', existing_type=sa.DateTime(), type_=postgresql.TIMESTAMP(timezone=True), existing_nullable=False, existing_server_default=sa.text('now()')) op.alter_column('checkout_activities', 'description', existing_type=sa.TEXT(), nullable=True) op.alter_column('checkout_activities', 'event_type', existing_type=sa.String(length=100), type_=sa.VARCHAR(length=64), existing_nullable=False) op.create_index(op.f('ix_cart_sessions_idempotency_partial'), 'cart_sessions', ['widget_key_id', 'idempotency_key'], unique=True, postgresql_where='(idempotency_key IS NOT NULL)') op.create_unique_constraint(op.f('cart_sessions_token_key'), 'cart_sessions', ['token'], postgresql_nulls_not_distinct=False) op.alter_column('cart_sessions', 'provider_txn_ref', existing_type=sa.VARCHAR(length=255), comment='Provider transaction reference (e.g. Payrix TXN ID)', existing_nullable=True) op.alter_column('auth_sessions', 'device_brand', existing_type=sa.VARCHAR(length=100), comment='e.g. Apple, Samsung, Dell', existing_nullable=True) op.alter_column('auth_sessions', 'device_type', existing_type=sa.VARCHAR(length=50), comment='mobile | tablet | desktop | bot', existing_nullable=True) op.alter_column('auth_sessions', 'geo_latitude', existing_type=sa.DOUBLE_PRECISION(precision=53), comment='DOUBLE PRECISION; stored as Float in SA', existing_nullable=True) op.alter_column('api_request_logs', 'error_message', existing_type=sa.String(), type_=sa.TEXT(), existing_nullable=True) op.alter_column('api_request_logs', 'response_body', existing_type=sa.JSON(), type_=postgresql.JSONB(astext_type=sa.Text()), existing_nullable=True) op.alter_column('api_request_logs', 'request_body', existing_type=sa.JSON(), type_=postgresql.JSONB(astext_type=sa.Text()), existing_nullable=True) op.drop_index(op.f('ix_admin_user_roles_user_id'), table_name='admin_user_roles') op.create_index(op.f('ix_admin_user_roles_user_id'), 'admin_user_roles', ['user_id'], unique=False) op.create_unique_constraint(op.f('admin_user_roles_user_id_key'), 'admin_user_roles', ['user_id'], postgresql_nulls_not_distinct=False) op.drop_index(op.f('ix_admin_roles_slug'), table_name='admin_roles') op.create_index(op.f('ix_admin_roles_slug'), 'admin_roles', ['slug'], unique=False) op.create_unique_constraint(op.f('admin_roles_slug_key'), 'admin_roles', ['slug'], postgresql_nulls_not_distinct=False) op.drop_index(op.f('ix_admin_invites_token_hash'), table_name='admin_invites') op.create_index(op.f('ix_admin_invites_token_hash'), 'admin_invites', ['token_hash'], unique=False) op.create_unique_constraint(op.f('admin_invites_token_hash_key'), 'admin_invites', ['token_hash'], postgresql_nulls_not_distinct=False) drop_fk('admin_audit_log', 'admin_user_id') op.drop_index(op.f('ix_admin_audit_log_target_id'), table_name='admin_audit_log') op.drop_index(op.f('ix_admin_audit_log_admin_user_id'), table_name='admin_audit_log') # ### end Alembic commands ###