zhifu-platform/migrations/versions/0016_platform.py

import sqlalchemy as sa
from alembic import op
from sqlalchemy.dialects import mysql

revision = "0016_platform"
down_revision = "0015_merchant"
branch_labels = None
depends_on = None


def upgrade():
    op.create_table(
        "platform_accounts",
        sa.Column("id", mysql.BINARY(16), primary_key=True),
        sa.Column("username", sa.String(128), nullable=False, unique=True),
        sa.Column("password_hash", sa.String(512), nullable=False),
        sa.Column("status", sa.String(16), nullable=False),
        sa.Column("permissions", sa.JSON(), nullable=False),
        sa.Column("created_at", mysql.DATETIME(fsp=6), nullable=False),
        sa.CheckConstraint("status IN ('ACTIVE','SUSPENDED')", name="status"),
    )
    op.create_table(
        "platform_sessions",
        sa.Column("token_hash", mysql.BINARY(32), primary_key=True),
        sa.Column("account_id", mysql.BINARY(16), nullable=False),
        sa.Column("expires_at", mysql.DATETIME(fsp=6), nullable=False),
        sa.Column("revoked", sa.Boolean(), nullable=False),
        sa.ForeignKeyConstraint(["account_id"], ["platform_accounts.id"]),
    )
    op.create_table(
        "tenant_registrations",
        sa.Column("tenant_id", mysql.BINARY(16), primary_key=True),
        sa.Column("contact", sa.String(100), nullable=False),
        sa.Column("status", sa.String(16), nullable=False),
        sa.Column("created_at", mysql.DATETIME(fsp=6), nullable=False),
        sa.ForeignKeyConstraint(["tenant_id"], ["tenants.id"]),
        sa.CheckConstraint("status IN ('PENDING','COMPLETE')", name="status"),
    )
    op.create_table(
        "platform_audit",
        sa.Column("id", mysql.BINARY(16), primary_key=True),
        sa.Column("actor_id", mysql.BINARY(16), nullable=False),
        sa.Column("action", sa.String(128), nullable=False),
        sa.Column("object_id", mysql.BINARY(16), nullable=False),
        sa.Column("object_name", sa.String(128), nullable=False),
        sa.Column("before", sa.JSON(), nullable=False),
        sa.Column("after", sa.JSON(), nullable=False),
        sa.Column("reason", sa.String(1000), nullable=False),
        sa.Column("request_id", sa.String(36), nullable=False),
        sa.Column("created_at", mysql.DATETIME(fsp=6), nullable=False),
        sa.ForeignKeyConstraint(["actor_id"], ["platform_accounts.id"]),
    )
    op.create_index("ix_platform_audit_time", "platform_audit", ["created_at", "id"])
    op.create_table(
        "platform_commands",
        sa.Column("actor_id", mysql.BINARY(16), primary_key=True),
        sa.Column("route", sa.String(128), primary_key=True),
        sa.Column("key", sa.String(64, collation="utf8mb4_bin"), primary_key=True),
        sa.Column("request_hash", mysql.BINARY(32), nullable=False),
        sa.Column("response", sa.JSON(), nullable=False),
        sa.ForeignKeyConstraint(["actor_id"], ["platform_accounts.id"]),
    )
    op.create_table(
        "channel_deliveries",
        sa.Column("id", mysql.BINARY(16), primary_key=True),
        sa.Column("event_id", mysql.BINARY(16), nullable=False),
        sa.Column("tenant_id", mysql.BINARY(16), nullable=False),
        sa.Column("store_id", mysql.BINARY(16), nullable=False),
        sa.Column("status", sa.String(16), nullable=False),
        sa.Column("attempts", mysql.BIGINT(unsigned=True), nullable=False),
        sa.Column("version", mysql.BIGINT(unsigned=True), nullable=False),
        sa.Column("lease_token", mysql.BIGINT(unsigned=True), nullable=False),
        sa.Column("lease_until", mysql.DATETIME(fsp=6)),
        sa.Column("next_attempt_at", mysql.DATETIME(fsp=6)),
        sa.Column("last_error", sa.String(128)),
        sa.Column("response_status", sa.Integer()),
        sa.Column("created_at", mysql.DATETIME(fsp=6), nullable=False),
        sa.Column("sent_at", mysql.DATETIME(fsp=6)),
        sa.ForeignKeyConstraint(["event_id"], ["business_events.id"]),
        sa.ForeignKeyConstraint(
            ["tenant_id", "store_id"],
            ["channel_applications.tenant_id", "channel_applications.store_id"],
        ),
        sa.UniqueConstraint("event_id", "tenant_id", "store_id", name="uq_channel_delivery_event"),
        sa.CheckConstraint("status IN ('QUEUED','SENDING','FAILED','SENT')", name="status"),
    )
    op.create_index(
        "ix_channel_delivery_due", "channel_deliveries", ["status", "next_attempt_at", "id"],
    )


def downgrade():
    op.drop_table("channel_deliveries")
    op.drop_table("platform_commands")
    op.drop_table("platform_audit")
    op.drop_table("tenant_registrations")
    op.drop_table("platform_sessions")
    op.drop_table("platform_accounts")