"""prd012: create hw_platform_bank_accounts and fee_settlement_runs tables. hw_platform_bank_accounts Stores the HubWallet-owned ACH destination accounts used to collect platform fees via daily settlement sweeps. Sensitive account and routing numbers are stored AES-256-GCM encrypted (base64-encoded ciphertext). The is_active flag is enforced at the service layer (atomic replace-on-create swap pattern) rather than at the DB level, so no unique index is created on it here. fee_settlement_runs Immutable financial audit record for each daily platform-fee settlement attempt. settlement_date is UNIQUE — exactly one run per calendar day. total_fee_cents is BIGINT to safely accommodate > $21 K/day accumulation. No deleted_at column — this table is append-only. Revision ID: prd012_add_fee_settlement_tables Revises: prd012_add_card_network_to_pricing_rate Create Date: 2026-05-14 """ from typing import Union, Sequence import sqlalchemy as sa from alembic import op revision: str = "prd012_add_fee_settlement_tables" down_revision: Union[str, Sequence[str], None] = "prd012_add_card_network_to_pricing_rate" branch_labels = None depends_on = None def upgrade() -> None: # ------------------------------------------------------------------ # hw_platform_bank_accounts # ------------------------------------------------------------------ op.create_table( "hw_platform_bank_accounts", sa.Column("id", sa.Integer(), autoincrement=True, nullable=False), # Human-readable label for the admin UI (e.g. "Primary ACH Account") sa.Column("nickname", sa.String(100), nullable=False), sa.Column("account_holder_name", sa.String(200), nullable=False), sa.Column("bank_name", sa.String(200), nullable=True), # AES-256-GCM encrypted, base64-encoded ciphertext — never store plaintext sa.Column("routing_number_enc", sa.Text(), nullable=False), sa.Column("account_number_enc", sa.Text(), nullable=False), # Last 4 digits of the account number for display purposes sa.Column("account_number_last4", sa.String(4), nullable=False), sa.Column( "account_type", sa.String(10), nullable=False, server_default="checking", ), sa.Column( "is_active", sa.Boolean(), nullable=False, server_default=sa.text("false"), ), # FK to users.id (superuser who created the record); nullable for seeded rows sa.Column("created_by_user_id", sa.Integer(), 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), # Constraints sa.CheckConstraint( "account_type IN ('checking', 'savings')", name="ck_hw_platform_bank_accounts_account_type", ), sa.ForeignKeyConstraint( ["created_by_user_id"], ["users.id"], name="fk_hw_platform_bank_accounts_created_by", ondelete="SET NULL", ), sa.PrimaryKeyConstraint("id"), ) # Index to quickly locate the current active account for sweep jobs op.create_index( "ix_hw_platform_bank_accounts_is_active", "hw_platform_bank_accounts", ["is_active"], ) # Index supporting FK lookups and audit queries by creator op.create_index( "ix_hw_platform_bank_accounts_created_by_user_id", "hw_platform_bank_accounts", ["created_by_user_id"], ) # ------------------------------------------------------------------ # fee_settlement_runs # ------------------------------------------------------------------ op.create_table( "fee_settlement_runs", sa.Column("id", sa.Integer(), autoincrement=True, nullable=False), # One run per calendar day — enforced by UNIQUE constraint sa.Column("settlement_date", sa.Date(), nullable=False), # Which bank account was targeted for this sweep sa.Column("settlement_account_id", sa.Integer(), nullable=False), # Summary counters — updated as transactions are claimed into this run sa.Column( "transaction_count", sa.Integer(), nullable=False, server_default=sa.text("0"), ), # BIGINT: platform could accumulate hundreds of millions of cents per day sa.Column( "total_fee_cents", sa.BigInteger(), nullable=False, server_default=sa.text("0"), ), sa.Column( "status", sa.String(20), nullable=False, server_default="pending", ), # TSYS / processor transaction reference for the ACH debit sa.Column("tsys_transaction_id", sa.String(255), nullable=True), # How this run was initiated sa.Column( "triggered_by", sa.String(20), nullable=False, server_default="scheduler", ), sa.Column( "initiated_at", sa.DateTime(timezone=True), server_default=sa.text("now()"), nullable=False, ), sa.Column("settled_at", sa.DateTime(timezone=True), nullable=True), # Retry tracking for failed settlement attempts (max retries enforced in service) sa.Column( "retry_count", sa.SmallInteger(), nullable=False, server_default=sa.text("0"), ), sa.Column("last_error", sa.Text(), 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), # NOTE: no deleted_at — fee_settlement_runs is an immutable financial record # Constraints sa.CheckConstraint( "status IN ('pending', 'processing', 'settled', 'failed', 'skipped')", name="ck_fee_settlement_runs_status", ), sa.CheckConstraint( "triggered_by IN ('scheduler', 'manual')", name="ck_fee_settlement_runs_triggered_by", ), sa.CheckConstraint( "total_fee_cents >= 0", name="ck_fee_settlement_runs_total_fee_cents_non_negative", ), sa.CheckConstraint( "transaction_count >= 0", name="ck_fee_settlement_runs_transaction_count_non_negative", ), sa.CheckConstraint( "retry_count >= 0", name="ck_fee_settlement_runs_retry_count_non_negative", ), sa.ForeignKeyConstraint( ["settlement_account_id"], ["hw_platform_bank_accounts.id"], name="fk_fee_settlement_runs_settlement_account", ondelete="RESTRICT", ), sa.UniqueConstraint("settlement_date", name="uq_fee_settlement_runs_settlement_date"), sa.PrimaryKeyConstraint("id"), ) # Index to support the daily "find today's run" lookup op.create_index( "ix_fee_settlement_runs_settlement_date", "fee_settlement_runs", ["settlement_date"], ) # Index for FK lookups (join from fee_settlement_runs back to bank account) op.create_index( "ix_fee_settlement_runs_settlement_account_id", "fee_settlement_runs", ["settlement_account_id"], ) # Index to filter runs by status (admin UI "show failed runs" query) op.create_index( "ix_fee_settlement_runs_status", "fee_settlement_runs", ["status"], ) def downgrade() -> None: # Drop fee_settlement_runs first because transactions.fee_settlement_run_id # (added in the next migration) references this table. # If that migration has already been applied, its downgrade must run first. op.drop_index("ix_fee_settlement_runs_status", table_name="fee_settlement_runs") op.drop_index("ix_fee_settlement_runs_settlement_account_id", table_name="fee_settlement_runs") op.drop_index("ix_fee_settlement_runs_settlement_date", table_name="fee_settlement_runs") op.drop_table("fee_settlement_runs") op.drop_index("ix_hw_platform_bank_accounts_created_by_user_id", table_name="hw_platform_bank_accounts") op.drop_index("ix_hw_platform_bank_accounts_is_active", table_name="hw_platform_bank_accounts") op.drop_table("hw_platform_bank_accounts")