106 lines
4.7 KiB
Python
106 lines
4.7 KiB
Python
"""control0002 — Stripe billing columns (MT-8)
|
|
|
|
Adds billing state columns to the control-plane `tenants` table and
|
|
`stripe_price_id` to the `plans` table.
|
|
|
|
All column additions use INFORMATION_SCHEMA existence checks so the
|
|
migration is safe to re-run (CLAUDE.md Rule 14). No ENUM change to the
|
|
existing `tenants.status` column — `subscription_status` is a separate
|
|
nullable column tracking billing lifecycle independently of operational state.
|
|
"""
|
|
|
|
import sqlalchemy as sa
|
|
from alembic import op
|
|
|
|
revision = 'control0002_billing'
|
|
down_revision = 'control0001_init'
|
|
branch_labels = None
|
|
depends_on = None
|
|
|
|
|
|
def _column_exists(bind, table: str, column: str) -> bool:
|
|
result = bind.execute(sa.text(
|
|
"SELECT COUNT(*) FROM information_schema.columns "
|
|
"WHERE table_schema = DATABASE() AND table_name = :t AND column_name = :c"
|
|
), {'t': table, 'c': column})
|
|
return result.scalar() > 0
|
|
|
|
|
|
def _index_exists(bind, table: str, index_name: str) -> bool:
|
|
result = bind.execute(sa.text(
|
|
"SELECT COUNT(*) FROM information_schema.statistics "
|
|
"WHERE table_schema = DATABASE() AND table_name = :t AND index_name = :i"
|
|
), {'t': table, 'i': index_name})
|
|
return result.scalar() > 0
|
|
|
|
|
|
def upgrade():
|
|
bind = op.get_bind()
|
|
|
|
# ── plans: stripe_price_id ─────────────────────────────────────────────
|
|
if not _column_exists(bind, 'plans', 'stripe_price_id'):
|
|
op.execute(sa.text(
|
|
"ALTER TABLE plans ADD COLUMN stripe_price_id VARCHAR(100) NULL"
|
|
))
|
|
|
|
# ── tenants: stripe_customer_id ────────────────────────────────────────
|
|
if not _column_exists(bind, 'tenants', 'stripe_customer_id'):
|
|
op.execute(sa.text(
|
|
"ALTER TABLE tenants ADD COLUMN stripe_customer_id VARCHAR(64) NULL"
|
|
))
|
|
if not _index_exists(bind, 'tenants', 'ix_tenants_stripe_customer'):
|
|
op.execute(sa.text(
|
|
"ALTER TABLE tenants ADD INDEX ix_tenants_stripe_customer (stripe_customer_id)"
|
|
))
|
|
|
|
# ── tenants: stripe_subscription_id ───────────────────────────────────
|
|
if not _column_exists(bind, 'tenants', 'stripe_subscription_id'):
|
|
op.execute(sa.text(
|
|
"ALTER TABLE tenants ADD COLUMN stripe_subscription_id VARCHAR(64) NULL"
|
|
))
|
|
if not _index_exists(bind, 'tenants', 'ix_tenants_stripe_sub'):
|
|
op.execute(sa.text(
|
|
"ALTER TABLE tenants ADD INDEX ix_tenants_stripe_sub (stripe_subscription_id)"
|
|
))
|
|
|
|
# ── tenants: subscription_status ──────────────────────────────────────
|
|
if not _column_exists(bind, 'tenants', 'subscription_status'):
|
|
op.execute(sa.text(
|
|
"ALTER TABLE tenants ADD COLUMN subscription_status "
|
|
"ENUM('trial','active','past_due','cancelled') NULL"
|
|
))
|
|
if not _index_exists(bind, 'tenants', 'ix_tenants_sub_status'):
|
|
op.execute(sa.text(
|
|
"ALTER TABLE tenants ADD INDEX ix_tenants_sub_status (subscription_status)"
|
|
))
|
|
|
|
# ── tenants: trial_ends_at ─────────────────────────────────────────────
|
|
if not _column_exists(bind, 'tenants', 'trial_ends_at'):
|
|
op.execute(sa.text(
|
|
"ALTER TABLE tenants ADD COLUMN trial_ends_at DATETIME NULL"
|
|
))
|
|
|
|
# ── tenants: current_period_end ────────────────────────────────────────
|
|
if not _column_exists(bind, 'tenants', 'current_period_end'):
|
|
op.execute(sa.text(
|
|
"ALTER TABLE tenants ADD COLUMN current_period_end DATETIME NULL"
|
|
))
|
|
|
|
# ── tenants: billing_email ─────────────────────────────────────────────
|
|
if not _column_exists(bind, 'tenants', 'billing_email'):
|
|
op.execute(sa.text(
|
|
"ALTER TABLE tenants ADD COLUMN billing_email VARCHAR(255) NULL"
|
|
))
|
|
|
|
|
|
def downgrade():
|
|
bind = op.get_bind()
|
|
|
|
for col in ('billing_email', 'current_period_end', 'trial_ends_at',
|
|
'subscription_status', 'stripe_subscription_id', 'stripe_customer_id'):
|
|
if _column_exists(bind, 'tenants', col):
|
|
op.execute(sa.text(f"ALTER TABLE tenants DROP COLUMN {col}"))
|
|
|
|
if _column_exists(bind, 'plans', 'stripe_price_id'):
|
|
op.execute(sa.text("ALTER TABLE plans DROP COLUMN stripe_price_id"))
|