zhifu-platform/migrations/versions/0024_channel_inbox.py

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

revision = "0024_channel_inbox"
down_revision = "0023_stream_window"
branch_labels = None
depends_on = None


def upgrade():
    op.add_column("conversations", sa.Column("channel", sa.String(16), nullable=False,
                                            server_default="h5"))
    op.alter_column("conversations", "channel", server_default=None, existing_type=sa.String(16))
    op.add_column("conversations", sa.Column(
        "external_subject", sa.String(128, collation="utf8mb4_bin"), nullable=True))
    op.create_table("channel_keys",
        sa.Column("tenant_id", mysql.BINARY(16), primary_key=True),
        sa.Column("store_id", mysql.BINARY(16), primary_key=True),
        sa.Column("key_id", sa.String(64, collation="utf8mb4_bin"), primary_key=True),
        sa.Column("credential_env", sa.String(128), nullable=False),
        sa.Column("valid_from", mysql.DATETIME(fsp=6), nullable=False),
        sa.Column("valid_until", mysql.DATETIME(fsp=6), nullable=False),
        sa.Column("enabled", sa.Boolean(), nullable=False),
        sa.ForeignKeyConstraint(["tenant_id", "store_id"],
            ["channel_applications.tenant_id", "channel_applications.store_id"]))
    op.create_table("channel_nonces",
        sa.Column("tenant_id", mysql.BINARY(16), primary_key=True),
        sa.Column("store_id", mysql.BINARY(16), primary_key=True),
        sa.Column("nonce_hash", mysql.BINARY(32), primary_key=True),
        sa.Column("expires_at", mysql.DATETIME(fsp=6), nullable=False),
        sa.ForeignKeyConstraint(["tenant_id", "store_id"],
            ["channel_applications.tenant_id", "channel_applications.store_id"]))
    op.create_table("channel_inbox",
        sa.Column("id", mysql.BINARY(16), primary_key=True),
        sa.Column("tenant_id", mysql.BINARY(16), nullable=False),
        sa.Column("store_id", mysql.BINARY(16), nullable=False),
        sa.Column("external_message_id", sa.String(128, collation="utf8mb4_bin"), nullable=False),
        sa.Column("external_subject", sa.String(128, collation="utf8mb4_bin"), nullable=False),
        sa.Column("actor_id", mysql.BINARY(16), nullable=False),
        sa.Column("request_hash", mysql.BINARY(32), nullable=False),
        sa.Column("response", sa.JSON(), nullable=False),
        sa.Column("created_at", mysql.DATETIME(fsp=6), nullable=False),
        sa.ForeignKeyConstraint(["tenant_id", "store_id"],
            ["channel_applications.tenant_id", "channel_applications.store_id"]),
        sa.UniqueConstraint("tenant_id", "store_id", "external_message_id",
            name="uq_channel_inbox_message"))


def downgrade():
    op.drop_table("channel_inbox")
    op.drop_table("channel_nonces")
    op.drop_table("channel_keys")
    op.drop_column("conversations", "external_subject")
    op.drop_column("conversations", "channel")