"""hwonb001 - merchant onboarding tables PRD-HWONB-001 §6 — creates the 4 tables backing the 11-step merchant self-service onboarding wizard (Global Payments Base Boarding): - merchant_onboarding_applications: one row per merchant's underwriting application (status/current_step as state-machine-in-code strings, section_state + per-section JSON snapshots for resume). - merchant_onboarding_owners: repeatable owners/principals (PRD-HWONB-007), Fernet-encrypted ssn_enc. - merchant_onboarding_accounts: repeatable bank accounts (PRD-HWONB-006), Fernet-encrypted routing_number_enc/account_number_enc. - merchant_onboarding_documents: uploaded docs + UMA-adjacent metadata (PRD-HWONB-012), doc_type is the CERT-confirmed 15-code enum (`Tax`, not `TAX`). No caps (<=5 owners, <=3 accounts, exactly-1 individualWithControl, etc.) are enforced at the DB level — those are crud.py's job (Developer agent), per PRD-HWONB-001 §6.1's explicit instruction not to make them unenforceable-in-code via a premature UniqueConstraint. Revision ID: hwonb001_merchant_onboarding Revises: tsys005_mark_ephemeral Create Date: 2026-07-14 """ from typing import Union, Sequence import sqlalchemy as sa from alembic import op revision: str = 'hwonb001_merchant_onboarding' down_revision: Union[str, Sequence[str], None] = 'tsys005_mark_ephemeral' branch_labels = None depends_on = None def upgrade() -> None: op.create_table( "merchant_onboarding_applications", sa.Column("id", sa.Integer(), autoincrement=True, nullable=False), sa.Column("merchant_id", sa.Integer(), nullable=False), sa.Column("tsys_app_id", sa.Integer(), nullable=True), sa.Column("tsys_mid", sa.String(length=25), nullable=True), sa.Column("status", sa.String(length=30), nullable=False, server_default="draft"), sa.Column("current_step", sa.String(length=30), nullable=False, server_default="business"), sa.Column("section_state", sa.JSON(), nullable=True), sa.Column("business_data", sa.JSON(), nullable=True), sa.Column("processing_data", sa.JSON(), nullable=True), sa.Column("addresses_data", sa.JSON(), nullable=True), sa.Column("card_types_data", sa.JSON(), nullable=True), sa.Column("products_data", sa.JSON(), nullable=True), sa.Column("training_activation_data", sa.JSON(), nullable=True), sa.Column("fees_data", sa.JSON(), nullable=True), sa.Column("last_status_polled_at", sa.DateTime(timezone=True), nullable=True), sa.Column("last_tsys_status", sa.String(length=50), nullable=True), sa.Column("tsys_decision_payload", sa.JSON(), nullable=True), sa.Column("submitted_email_sent_at", sa.DateTime(timezone=True), nullable=True), sa.Column("approved_email_sent_at", sa.DateTime(timezone=True), nullable=True), sa.Column("rejected_email_sent_at", sa.DateTime(timezone=True), nullable=True), sa.Column("created_at", sa.DateTime(timezone=True), server_default=sa.text("now()"), nullable=False), sa.Column("updated_at", sa.DateTime(timezone=True), nullable=True), sa.Column("deleted_at", sa.DateTime(timezone=True), nullable=True), sa.ForeignKeyConstraint(["merchant_id"], ["merchants.id"], ondelete="CASCADE"), sa.PrimaryKeyConstraint("id"), ) op.create_index( "ix_merchant_onboarding_applications_merchant_id", "merchant_onboarding_applications", ["merchant_id"], ) op.create_table( "merchant_onboarding_owners", sa.Column("id", sa.Integer(), autoincrement=True, nullable=False), sa.Column("application_id", sa.Integer(), nullable=False), sa.Column("first_name", sa.String(length=11), nullable=False), sa.Column("last_name", sa.String(length=15), nullable=False), sa.Column("contact_title", sa.String(length=10), nullable=False), sa.Column("phone_country_code", sa.String(length=5), nullable=False), sa.Column("phone_number", sa.String(length=20), nullable=False), sa.Column("email", sa.String(length=255), nullable=False), sa.Column("dob", sa.Date(), nullable=False), sa.Column("owner_percent", sa.Integer(), nullable=False), sa.Column("non_us_citizen", sa.Boolean(), nullable=False, server_default=sa.false()), sa.Column("ssn_enc", sa.Text(), nullable=True), sa.Column("credit_pull_waive", sa.Boolean(), nullable=True, server_default=sa.false()), sa.Column("street_number", sa.String(length=6), nullable=True), sa.Column("street_name", sa.String(length=19), nullable=True), sa.Column("street_direction", sa.String(length=2), nullable=True), sa.Column("street_type", sa.String(length=10), nullable=True), sa.Column("unit_number", sa.String(length=5), nullable=True), sa.Column("city", sa.String(length=16), nullable=True), sa.Column("state", sa.String(length=2), nullable=True), sa.Column("zip_code", sa.String(length=9), nullable=True), sa.Column("country", sa.String(length=3), nullable=True), sa.Column("passport_country", sa.String(length=100), nullable=True), sa.Column("passport_number", sa.String(length=50), nullable=True), sa.Column("address_line1", sa.String(length=100), nullable=True), sa.Column("address_line2", sa.String(length=100), nullable=True), sa.Column("foreign_city", sa.String(length=100), nullable=True), sa.Column("foreign_state", sa.String(length=50), nullable=True), sa.Column("foreign_zip_code", sa.String(length=20), nullable=True), sa.Column("foreign_country", sa.String(length=100), nullable=True), sa.Column("app_signer", sa.Boolean(), nullable=False, server_default=sa.false()), sa.Column("personal_guarantor", sa.Boolean(), nullable=False, server_default=sa.false()), sa.Column("beneficial_owner", sa.Boolean(), nullable=False, server_default=sa.false()), sa.Column("individual_with_control", sa.Boolean(), nullable=False, server_default=sa.false()), sa.Column("tsys_owner_ref", sa.String(length=100), nullable=True), sa.Column("tsys_synced_at", sa.DateTime(timezone=True), nullable=True), sa.Column("tsys_errors", sa.JSON(), nullable=True), sa.Column("created_at", sa.DateTime(timezone=True), server_default=sa.text("now()"), nullable=False), sa.Column("updated_at", sa.DateTime(timezone=True), nullable=True), sa.Column("deleted_at", sa.DateTime(timezone=True), nullable=True), sa.ForeignKeyConstraint( ["application_id"], ["merchant_onboarding_applications.id"], ondelete="CASCADE" ), sa.PrimaryKeyConstraint("id"), ) op.create_index( "ix_merchant_onboarding_owners_application_id", "merchant_onboarding_owners", ["application_id"], ) op.create_table( "merchant_onboarding_accounts", sa.Column("id", sa.Integer(), autoincrement=True, nullable=False), sa.Column("application_id", sa.Integer(), nullable=False), sa.Column("routing_number_enc", sa.Text(), nullable=False), sa.Column("account_number_enc", sa.Text(), nullable=False), sa.Column("account_type", sa.String(length=1), nullable=False), sa.Column("usage_types", sa.JSON(), nullable=True), sa.Column("is_default", sa.Boolean(), nullable=False, server_default=sa.false()), sa.Column("tsys_account_ref", sa.String(length=100), nullable=True), sa.Column("tsys_synced_at", sa.DateTime(timezone=True), nullable=True), sa.Column("tsys_errors", sa.JSON(), nullable=True), sa.Column("created_at", sa.DateTime(timezone=True), server_default=sa.text("now()"), nullable=False), sa.Column("updated_at", sa.DateTime(timezone=True), nullable=True), sa.Column("deleted_at", sa.DateTime(timezone=True), nullable=True), sa.ForeignKeyConstraint( ["application_id"], ["merchant_onboarding_applications.id"], ondelete="CASCADE" ), sa.PrimaryKeyConstraint("id"), ) op.create_index( "ix_merchant_onboarding_accounts_application_id", "merchant_onboarding_accounts", ["application_id"], ) op.create_table( "merchant_onboarding_documents", sa.Column("id", sa.Integer(), autoincrement=True, nullable=False), sa.Column("application_id", sa.Integer(), nullable=False), sa.Column("doc_type", sa.String(length=10), nullable=False), sa.Column("file_name", sa.String(length=255), nullable=False), sa.Column("content_type", sa.String(length=100), nullable=False), sa.Column("size_bytes", sa.Integer(), nullable=False), sa.Column("s3_key", sa.String(length=500), nullable=False), sa.Column("tsys_upload_ref", sa.String(length=100), nullable=True), sa.Column("tsys_synced_at", sa.DateTime(timezone=True), nullable=True), sa.Column("tsys_errors", sa.JSON(), nullable=True), sa.Column("created_at", sa.DateTime(timezone=True), server_default=sa.text("now()"), nullable=False), sa.Column("updated_at", sa.DateTime(timezone=True), nullable=True), sa.Column("deleted_at", sa.DateTime(timezone=True), nullable=True), sa.ForeignKeyConstraint( ["application_id"], ["merchant_onboarding_applications.id"], ondelete="CASCADE" ), sa.PrimaryKeyConstraint("id"), ) op.create_index( "ix_merchant_onboarding_documents_application_id", "merchant_onboarding_documents", ["application_id"], ) def downgrade() -> None: op.drop_index( "ix_merchant_onboarding_documents_application_id", table_name="merchant_onboarding_documents", ) op.drop_table("merchant_onboarding_documents") op.drop_index( "ix_merchant_onboarding_accounts_application_id", table_name="merchant_onboarding_accounts", ) op.drop_table("merchant_onboarding_accounts") op.drop_index( "ix_merchant_onboarding_owners_application_id", table_name="merchant_onboarding_owners", ) op.drop_table("merchant_onboarding_owners") op.drop_index( "ix_merchant_onboarding_applications_merchant_id", table_name="merchant_onboarding_applications", ) op.drop_table("merchant_onboarding_applications")