218 lines
11 KiB
Python
218 lines
11 KiB
Python
"""initial schema
|
|
|
|
Revision ID: 0001_initial_schema
|
|
Revises:
|
|
Create Date: 2026-03-07 22:40:00.000000
|
|
"""
|
|
|
|
from typing import Sequence, Union
|
|
|
|
from alembic import op
|
|
import sqlalchemy as sa
|
|
|
|
|
|
revision: str = "0001_initial_schema"
|
|
down_revision: Union[str, Sequence[str], None] = None
|
|
branch_labels: Union[str, Sequence[str], None] = None
|
|
depends_on: Union[str, Sequence[str], None] = None
|
|
|
|
|
|
chat_type_enum = sa.Enum("PRIVATE", "GROUP", "CHANNEL", name="chattype")
|
|
chat_role_enum = sa.Enum("OWNER", "ADMIN", "MEMBER", name="chatmemberrole")
|
|
message_type_enum = sa.Enum(
|
|
"TEXT",
|
|
"IMAGE",
|
|
"VIDEO",
|
|
"AUDIO",
|
|
"VOICE",
|
|
"FILE",
|
|
"CIRCLE_VIDEO",
|
|
name="messagetype",
|
|
)
|
|
|
|
|
|
def upgrade() -> None:
|
|
op.create_table(
|
|
"users",
|
|
sa.Column("id", sa.Integer(), nullable=False),
|
|
sa.Column("username", sa.String(length=50), nullable=False),
|
|
sa.Column("email", sa.String(length=255), nullable=False),
|
|
sa.Column("password_hash", sa.String(length=255), nullable=False),
|
|
sa.Column("avatar_url", sa.String(length=512), nullable=True),
|
|
sa.Column("email_verified", sa.Boolean(), nullable=False, server_default=sa.false()),
|
|
sa.Column("created_at", sa.DateTime(timezone=True), server_default=sa.func.now(), nullable=False),
|
|
sa.Column("updated_at", sa.DateTime(timezone=True), server_default=sa.func.now(), nullable=False),
|
|
sa.PrimaryKeyConstraint("id", name=op.f("pk_users")),
|
|
)
|
|
op.create_index(op.f("ix_users_id"), "users", ["id"], unique=False)
|
|
op.create_index(op.f("ix_users_username"), "users", ["username"], unique=True)
|
|
op.create_index(op.f("ix_users_email"), "users", ["email"], unique=True)
|
|
op.create_index(op.f("ix_users_email_verified"), "users", ["email_verified"], unique=False)
|
|
|
|
op.create_table(
|
|
"chats",
|
|
sa.Column("id", sa.Integer(), nullable=False),
|
|
sa.Column("type", chat_type_enum, nullable=False),
|
|
sa.Column("title", sa.String(length=255), nullable=True),
|
|
sa.Column("created_at", sa.DateTime(timezone=True), server_default=sa.func.now(), nullable=False),
|
|
sa.PrimaryKeyConstraint("id", name=op.f("pk_chats")),
|
|
)
|
|
op.create_index(op.f("ix_chats_id"), "chats", ["id"], unique=False)
|
|
op.create_index(op.f("ix_chats_type"), "chats", ["type"], unique=False)
|
|
|
|
op.create_table(
|
|
"chat_members",
|
|
sa.Column("id", sa.Integer(), nullable=False),
|
|
sa.Column("chat_id", sa.Integer(), nullable=False),
|
|
sa.Column("user_id", sa.Integer(), nullable=False),
|
|
sa.Column("role", chat_role_enum, nullable=False),
|
|
sa.Column("joined_at", sa.DateTime(timezone=True), server_default=sa.func.now(), nullable=False),
|
|
sa.ForeignKeyConstraint(["chat_id"], ["chats.id"], name=op.f("fk_chat_members_chat_id_chats"), ondelete="CASCADE"),
|
|
sa.ForeignKeyConstraint(["user_id"], ["users.id"], name=op.f("fk_chat_members_user_id_users"), ondelete="CASCADE"),
|
|
sa.PrimaryKeyConstraint("id", name=op.f("pk_chat_members")),
|
|
sa.UniqueConstraint("chat_id", "user_id", name=op.f("uq_chat_members_chat_id_user_id")),
|
|
)
|
|
op.create_index(op.f("ix_chat_members_id"), "chat_members", ["id"], unique=False)
|
|
op.create_index(op.f("ix_chat_members_chat_id"), "chat_members", ["chat_id"], unique=False)
|
|
op.create_index(op.f("ix_chat_members_user_id"), "chat_members", ["user_id"], unique=False)
|
|
|
|
op.create_table(
|
|
"messages",
|
|
sa.Column("id", sa.Integer(), nullable=False),
|
|
sa.Column("chat_id", sa.Integer(), nullable=False),
|
|
sa.Column("sender_id", sa.Integer(), nullable=False),
|
|
sa.Column("type", message_type_enum, nullable=False),
|
|
sa.Column("text", sa.Text(), nullable=True),
|
|
sa.Column("created_at", sa.DateTime(timezone=True), server_default=sa.func.now(), nullable=False),
|
|
sa.Column("updated_at", sa.DateTime(timezone=True), server_default=sa.func.now(), nullable=False),
|
|
sa.ForeignKeyConstraint(["chat_id"], ["chats.id"], name=op.f("fk_messages_chat_id_chats"), ondelete="CASCADE"),
|
|
sa.ForeignKeyConstraint(["sender_id"], ["users.id"], name=op.f("fk_messages_sender_id_users"), ondelete="CASCADE"),
|
|
sa.PrimaryKeyConstraint("id", name=op.f("pk_messages")),
|
|
)
|
|
op.create_index(op.f("ix_messages_id"), "messages", ["id"], unique=False)
|
|
op.create_index(op.f("ix_messages_chat_id"), "messages", ["chat_id"], unique=False)
|
|
op.create_index(op.f("ix_messages_sender_id"), "messages", ["sender_id"], unique=False)
|
|
|
|
op.create_table(
|
|
"attachments",
|
|
sa.Column("id", sa.Integer(), nullable=False),
|
|
sa.Column("message_id", sa.Integer(), nullable=False),
|
|
sa.Column("file_url", sa.String(length=1024), nullable=False),
|
|
sa.Column("file_type", sa.String(length=64), nullable=False),
|
|
sa.Column("file_size", sa.Integer(), nullable=False),
|
|
sa.ForeignKeyConstraint(["message_id"], ["messages.id"], name=op.f("fk_attachments_message_id_messages"), ondelete="CASCADE"),
|
|
sa.PrimaryKeyConstraint("id", name=op.f("pk_attachments")),
|
|
)
|
|
op.create_index(op.f("ix_attachments_id"), "attachments", ["id"], unique=False)
|
|
op.create_index(op.f("ix_attachments_message_id"), "attachments", ["message_id"], unique=False)
|
|
|
|
op.create_table(
|
|
"email_verification_tokens",
|
|
sa.Column("id", sa.Integer(), nullable=False),
|
|
sa.Column("user_id", sa.Integer(), nullable=False),
|
|
sa.Column("token", sa.String(length=255), nullable=False),
|
|
sa.Column("expires_at", sa.DateTime(timezone=True), nullable=False),
|
|
sa.Column("created_at", sa.DateTime(timezone=True), nullable=False),
|
|
sa.ForeignKeyConstraint(
|
|
["user_id"], ["users.id"], name=op.f("fk_email_verification_tokens_user_id_users"), ondelete="CASCADE"
|
|
),
|
|
sa.PrimaryKeyConstraint("id", name=op.f("pk_email_verification_tokens")),
|
|
)
|
|
op.create_index(op.f("ix_email_verification_tokens_id"), "email_verification_tokens", ["id"], unique=False)
|
|
op.create_index(op.f("ix_email_verification_tokens_user_id"), "email_verification_tokens", ["user_id"], unique=False)
|
|
op.create_index(op.f("ix_email_verification_tokens_token"), "email_verification_tokens", ["token"], unique=True)
|
|
op.create_index(
|
|
op.f("ix_email_verification_tokens_expires_at"), "email_verification_tokens", ["expires_at"], unique=False
|
|
)
|
|
|
|
op.create_table(
|
|
"password_reset_tokens",
|
|
sa.Column("id", sa.Integer(), nullable=False),
|
|
sa.Column("user_id", sa.Integer(), nullable=False),
|
|
sa.Column("token", sa.String(length=255), nullable=False),
|
|
sa.Column("expires_at", sa.DateTime(timezone=True), nullable=False),
|
|
sa.Column("created_at", sa.DateTime(timezone=True), nullable=False),
|
|
sa.ForeignKeyConstraint(["user_id"], ["users.id"], name=op.f("fk_password_reset_tokens_user_id_users"), ondelete="CASCADE"),
|
|
sa.PrimaryKeyConstraint("id", name=op.f("pk_password_reset_tokens")),
|
|
)
|
|
op.create_index(op.f("ix_password_reset_tokens_id"), "password_reset_tokens", ["id"], unique=False)
|
|
op.create_index(op.f("ix_password_reset_tokens_user_id"), "password_reset_tokens", ["user_id"], unique=False)
|
|
op.create_index(op.f("ix_password_reset_tokens_token"), "password_reset_tokens", ["token"], unique=True)
|
|
op.create_index(op.f("ix_password_reset_tokens_expires_at"), "password_reset_tokens", ["expires_at"], unique=False)
|
|
|
|
op.create_table(
|
|
"email_logs",
|
|
sa.Column("id", sa.Integer(), nullable=False),
|
|
sa.Column("recipient", sa.String(length=255), nullable=False),
|
|
sa.Column("subject", sa.String(length=255), nullable=False),
|
|
sa.Column("body", sa.Text(), nullable=False),
|
|
sa.Column("created_at", sa.DateTime(timezone=True), server_default=sa.func.now(), nullable=False),
|
|
sa.PrimaryKeyConstraint("id", name=op.f("pk_email_logs")),
|
|
)
|
|
op.create_index(op.f("ix_email_logs_id"), "email_logs", ["id"], unique=False)
|
|
op.create_index(op.f("ix_email_logs_recipient"), "email_logs", ["recipient"], unique=False)
|
|
|
|
op.create_table(
|
|
"notification_logs",
|
|
sa.Column("id", sa.Integer(), nullable=False),
|
|
sa.Column("user_id", sa.Integer(), nullable=False),
|
|
sa.Column("event_type", sa.String(length=64), nullable=False),
|
|
sa.Column("payload", sa.String(length=1024), nullable=False),
|
|
sa.Column("created_at", sa.DateTime(timezone=True), server_default=sa.func.now(), nullable=False),
|
|
sa.PrimaryKeyConstraint("id", name=op.f("pk_notification_logs")),
|
|
)
|
|
op.create_index(op.f("ix_notification_logs_id"), "notification_logs", ["id"], unique=False)
|
|
op.create_index(op.f("ix_notification_logs_user_id"), "notification_logs", ["user_id"], unique=False)
|
|
op.create_index(op.f("ix_notification_logs_event_type"), "notification_logs", ["event_type"], unique=False)
|
|
|
|
|
|
def downgrade() -> None:
|
|
op.drop_index(op.f("ix_notification_logs_event_type"), table_name="notification_logs")
|
|
op.drop_index(op.f("ix_notification_logs_user_id"), table_name="notification_logs")
|
|
op.drop_index(op.f("ix_notification_logs_id"), table_name="notification_logs")
|
|
op.drop_table("notification_logs")
|
|
|
|
op.drop_index(op.f("ix_email_logs_recipient"), table_name="email_logs")
|
|
op.drop_index(op.f("ix_email_logs_id"), table_name="email_logs")
|
|
op.drop_table("email_logs")
|
|
|
|
op.drop_index(op.f("ix_password_reset_tokens_expires_at"), table_name="password_reset_tokens")
|
|
op.drop_index(op.f("ix_password_reset_tokens_token"), table_name="password_reset_tokens")
|
|
op.drop_index(op.f("ix_password_reset_tokens_user_id"), table_name="password_reset_tokens")
|
|
op.drop_index(op.f("ix_password_reset_tokens_id"), table_name="password_reset_tokens")
|
|
op.drop_table("password_reset_tokens")
|
|
|
|
op.drop_index(op.f("ix_email_verification_tokens_expires_at"), table_name="email_verification_tokens")
|
|
op.drop_index(op.f("ix_email_verification_tokens_token"), table_name="email_verification_tokens")
|
|
op.drop_index(op.f("ix_email_verification_tokens_user_id"), table_name="email_verification_tokens")
|
|
op.drop_index(op.f("ix_email_verification_tokens_id"), table_name="email_verification_tokens")
|
|
op.drop_table("email_verification_tokens")
|
|
|
|
op.drop_index(op.f("ix_attachments_message_id"), table_name="attachments")
|
|
op.drop_index(op.f("ix_attachments_id"), table_name="attachments")
|
|
op.drop_table("attachments")
|
|
|
|
op.drop_index(op.f("ix_messages_sender_id"), table_name="messages")
|
|
op.drop_index(op.f("ix_messages_chat_id"), table_name="messages")
|
|
op.drop_index(op.f("ix_messages_id"), table_name="messages")
|
|
op.drop_table("messages")
|
|
|
|
op.drop_index(op.f("ix_chat_members_user_id"), table_name="chat_members")
|
|
op.drop_index(op.f("ix_chat_members_chat_id"), table_name="chat_members")
|
|
op.drop_index(op.f("ix_chat_members_id"), table_name="chat_members")
|
|
op.drop_table("chat_members")
|
|
|
|
op.drop_index(op.f("ix_chats_type"), table_name="chats")
|
|
op.drop_index(op.f("ix_chats_id"), table_name="chats")
|
|
op.drop_table("chats")
|
|
|
|
op.drop_index(op.f("ix_users_email_verified"), table_name="users")
|
|
op.drop_index(op.f("ix_users_email"), table_name="users")
|
|
op.drop_index(op.f("ix_users_username"), table_name="users")
|
|
op.drop_index(op.f("ix_users_id"), table_name="users")
|
|
op.drop_table("users")
|
|
|
|
message_type_enum.drop(op.get_bind(), checkfirst=False)
|
|
chat_role_enum.drop(op.get_bind(), checkfirst=False)
|
|
chat_type_enum.drop(op.get_bind(), checkfirst=False)
|