172 lines
7.8 KiB
Python
172 lines
7.8 KiB
Python
"""control0001 — initial control-plane schema
|
|
|
|
Creates the control-plane registry tables (MULTI_TENANT_PLAN.md §3):
|
|
plans, plan_features, superadmins, tenants, tenant_domains,
|
|
provisioning_jobs, tenant_audit.
|
|
|
|
Raw MySQL DDL with INFORMATION_SCHEMA existence checks so the migration is
|
|
safe to re-run (CLAUDE.md Rule 14). InnoDB + utf8mb4 throughout. Targets the
|
|
CONTROL database only — never a tenant DB.
|
|
"""
|
|
|
|
import sqlalchemy as sa
|
|
from alembic import op
|
|
|
|
revision = 'control0001_init'
|
|
down_revision = None
|
|
branch_labels = None
|
|
depends_on = None
|
|
|
|
|
|
def _table_exists(bind, table: str) -> bool:
|
|
result = bind.execute(sa.text(
|
|
"SELECT COUNT(*) FROM information_schema.tables "
|
|
"WHERE table_schema = DATABASE() AND table_name = :t"
|
|
), {'t': table})
|
|
return result.scalar() > 0
|
|
|
|
|
|
def upgrade():
|
|
bind = op.get_bind()
|
|
|
|
if not _table_exists(bind, 'plans'):
|
|
op.execute(sa.text("""
|
|
CREATE TABLE plans (
|
|
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
|
|
code VARCHAR(32) NOT NULL,
|
|
name VARCHAR(100) NOT NULL,
|
|
active TINYINT(1) NOT NULL DEFAULT 1,
|
|
max_users INT NULL,
|
|
max_facilities INT NULL,
|
|
max_inspections_month INT NULL,
|
|
max_issues_month INT NULL,
|
|
allow_custom_domain TINYINT(1) NOT NULL DEFAULT 0,
|
|
allow_mobile_api TINYINT(1) NOT NULL DEFAULT 0,
|
|
allow_scheduled_reports TINYINT(1) NOT NULL DEFAULT 0,
|
|
allow_branding TINYINT(1) NOT NULL DEFAULT 0,
|
|
price_cents INT NULL,
|
|
billing_period VARCHAR(16) NULL,
|
|
created_at DATETIME NOT NULL,
|
|
UNIQUE KEY uq_plans_code (code)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
|
|
"""))
|
|
|
|
if not _table_exists(bind, 'plan_features'):
|
|
op.execute(sa.text("""
|
|
CREATE TABLE plan_features (
|
|
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
|
|
plan_id INT NOT NULL,
|
|
feature_key VARCHAR(64) NOT NULL,
|
|
enabled TINYINT(1) NOT NULL DEFAULT 0,
|
|
CONSTRAINT fk_plan_features_plan FOREIGN KEY (plan_id)
|
|
REFERENCES plans(id) ON DELETE CASCADE,
|
|
UNIQUE KEY uq_plan_feature (plan_id, feature_key),
|
|
INDEX ix_plan_features_plan (plan_id)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
|
|
"""))
|
|
|
|
if not _table_exists(bind, 'superadmins'):
|
|
op.execute(sa.text("""
|
|
CREATE TABLE superadmins (
|
|
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
|
|
username VARCHAR(100) NOT NULL,
|
|
email VARCHAR(255) NOT NULL,
|
|
password_hash VARCHAR(255) NOT NULL,
|
|
active TINYINT(1) NOT NULL DEFAULT 1,
|
|
created_at DATETIME NOT NULL,
|
|
UNIQUE KEY uq_superadmins_username (username),
|
|
UNIQUE KEY uq_superadmins_email (email)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
|
|
"""))
|
|
|
|
if not _table_exists(bind, 'tenants'):
|
|
op.execute(sa.text("""
|
|
CREATE TABLE tenants (
|
|
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
|
|
slug VARCHAR(63) NOT NULL,
|
|
name VARCHAR(150) NOT NULL,
|
|
plan_id INT NOT NULL,
|
|
status ENUM('provisioning','active','suspended','deleted')
|
|
NOT NULL DEFAULT 'provisioning',
|
|
db_host VARCHAR(255) NOT NULL,
|
|
db_port INT NOT NULL DEFAULT 3306,
|
|
db_name VARCHAR(64) NOT NULL,
|
|
db_user VARCHAR(64) NOT NULL,
|
|
db_password_enc TEXT NULL,
|
|
alembic_head VARCHAR(64) NULL,
|
|
created_at DATETIME NOT NULL,
|
|
suspended_at DATETIME NULL,
|
|
notes TEXT NULL,
|
|
CONSTRAINT fk_tenants_plan FOREIGN KEY (plan_id)
|
|
REFERENCES plans(id),
|
|
UNIQUE KEY uq_tenants_slug (slug),
|
|
UNIQUE KEY uq_tenants_db_name (db_name),
|
|
INDEX ix_tenants_plan (plan_id)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
|
|
"""))
|
|
|
|
if not _table_exists(bind, 'tenant_domains'):
|
|
op.execute(sa.text("""
|
|
CREATE TABLE tenant_domains (
|
|
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
|
|
tenant_id INT NOT NULL,
|
|
domain VARCHAR(255) NOT NULL,
|
|
kind ENUM('subdomain','custom') NOT NULL,
|
|
is_primary TINYINT(1) NOT NULL DEFAULT 0,
|
|
verified TINYINT(1) NOT NULL DEFAULT 0,
|
|
verification_token VARCHAR(64) NULL,
|
|
tls_status ENUM('pending','active','failed')
|
|
NOT NULL DEFAULT 'pending',
|
|
created_at DATETIME NOT NULL,
|
|
CONSTRAINT fk_tenant_domains_tenant FOREIGN KEY (tenant_id)
|
|
REFERENCES tenants(id) ON DELETE CASCADE,
|
|
UNIQUE KEY uq_tenant_domains_domain (domain),
|
|
INDEX ix_tenant_domains_tenant (tenant_id)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
|
|
"""))
|
|
|
|
if not _table_exists(bind, 'provisioning_jobs'):
|
|
op.execute(sa.text("""
|
|
CREATE TABLE provisioning_jobs (
|
|
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
|
|
tenant_id INT NULL,
|
|
action ENUM('create_db','migrate','seed','suspend','resume','delete')
|
|
NOT NULL,
|
|
status ENUM('queued','running','ok','failed')
|
|
NOT NULL DEFAULT 'queued',
|
|
log TEXT NULL,
|
|
created_at DATETIME NOT NULL,
|
|
finished_at DATETIME NULL,
|
|
CONSTRAINT fk_provisioning_jobs_tenant FOREIGN KEY (tenant_id)
|
|
REFERENCES tenants(id) ON DELETE SET NULL,
|
|
INDEX ix_provisioning_jobs_tenant (tenant_id)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
|
|
"""))
|
|
|
|
if not _table_exists(bind, 'tenant_audit'):
|
|
op.execute(sa.text("""
|
|
CREATE TABLE tenant_audit (
|
|
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
|
|
superadmin_id INT NULL,
|
|
action VARCHAR(50) NOT NULL,
|
|
tenant_id INT NULL,
|
|
details TEXT NULL,
|
|
ip_address VARCHAR(45) NULL,
|
|
created_at DATETIME NOT NULL,
|
|
CONSTRAINT fk_tenant_audit_superadmin FOREIGN KEY (superadmin_id)
|
|
REFERENCES superadmins(id) ON DELETE SET NULL,
|
|
CONSTRAINT fk_tenant_audit_tenant FOREIGN KEY (tenant_id)
|
|
REFERENCES tenants(id) ON DELETE SET NULL,
|
|
INDEX ix_tenant_audit_tenant (tenant_id),
|
|
INDEX ix_tenant_audit_created (created_at)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
|
|
"""))
|
|
|
|
|
|
def downgrade():
|
|
bind = op.get_bind()
|
|
for table in ('tenant_audit', 'provisioning_jobs', 'tenant_domains',
|
|
'tenants', 'superadmins', 'plan_features', 'plans'):
|
|
if _table_exists(bind, table):
|
|
op.execute(sa.text(f'DROP TABLE {table}'))
|