import uuid from datetime import datetime, timezone from sqlalchemy import ( DateTime, JSON, ForeignKey, Index, String, Text, UniqueConstraint, ) from sqlalchemy.dialects.postgresql import UUID from sqlalchemy.orm import Mapped, mapped_column, relationship from app.db.base import Base def utc_now() -> datetime: return datetime.now(timezone.utc) class User(Base): __tablename__ = "users" id: Mapped[uuid.UUID] = mapped_column( UUID(as_uuid=True), primary_key=True, default=uuid.uuid4, ) username: Mapped[str] = mapped_column( String(32), unique=True, nullable=False, index=True, ) display_name: Mapped[str] = mapped_column( String(100), nullable=False, ) password_hash: Mapped[str] = mapped_column( String(255), nullable=False, ) created_at: Mapped[datetime] = mapped_column( DateTime(timezone=True), default=utc_now, nullable=False, ) memberships: Mapped[list["ChatMember"]] = relationship( back_populates="user", cascade="all, delete-orphan", ) messages: Mapped[list["Message"]] = relationship( back_populates="sender", ) class Chat(Base): __tablename__ = "chats" id: Mapped[uuid.UUID] = mapped_column( UUID(as_uuid=True), primary_key=True, default=uuid.uuid4, ) title: Mapped[str] = mapped_column( String(100), nullable=False, ) created_at: Mapped[datetime] = mapped_column( DateTime(timezone=True), default=utc_now, nullable=False, ) members: Mapped[list["ChatMember"]] = relationship( back_populates="chat", cascade="all, delete-orphan", ) messages: Mapped[list["Message"]] = relationship( back_populates="chat", cascade="all, delete-orphan", ) class ChatMember(Base): __tablename__ = "chat_members" chat_id: Mapped[uuid.UUID] = mapped_column( UUID(as_uuid=True), ForeignKey("chats.id", ondelete="CASCADE"), primary_key=True, ) user_id: Mapped[uuid.UUID] = mapped_column( UUID(as_uuid=True), ForeignKey("users.id", ondelete="CASCADE"), primary_key=True, ) chat: Mapped["Chat"] = relationship( back_populates="members", ) user: Mapped["User"] = relationship( back_populates="memberships", ) class Message(Base): __tablename__ = "messages" id: Mapped[uuid.UUID] = mapped_column( UUID(as_uuid=True), primary_key=True, default=uuid.uuid4, ) chat_id: Mapped[uuid.UUID] = mapped_column( UUID(as_uuid=True), ForeignKey("chats.id", ondelete="CASCADE"), nullable=False, ) sender_id: Mapped[uuid.UUID] = mapped_column( UUID(as_uuid=True), ForeignKey("users.id"), nullable=False, ) text: Mapped[str] = mapped_column( Text, nullable=False, ) attachment_ids: Mapped[list] = mapped_column(JSON, nullable=False, default=list) created_at: Mapped[datetime] = mapped_column( DateTime(timezone=True), default=utc_now, nullable=False, ) chat: Mapped["Chat"] = relationship( back_populates="messages", ) sender: Mapped["User"] = relationship( back_populates="messages", ) __table_args__ = ( Index( "ix_messages_chat_created_id", "chat_id", "created_at", "id", ), ) class Session(Base): __tablename__ = "sessions" id: Mapped[uuid.UUID] = mapped_column( UUID(as_uuid=True), primary_key=True, default=uuid.uuid4, ) token_hash: Mapped[str] = mapped_column( String(64), unique=True, nullable=False, index=True, ) user_id: Mapped[uuid.UUID] = mapped_column( UUID(as_uuid=True), ForeignKey("users.id", ondelete="CASCADE"), nullable=False, ) expires_at: Mapped[datetime] = mapped_column( DateTime(timezone=True), nullable=False, ) created_at: Mapped[datetime] = mapped_column( DateTime(timezone=True), default=utc_now, nullable=False, ) user: Mapped["User"] = relationship() class IdempotencyRecord(Base): __tablename__ = "idempotency_records" id: Mapped[uuid.UUID] = mapped_column( UUID(as_uuid=True), primary_key=True, default=uuid.uuid4 ) user_id: Mapped[uuid.UUID] = mapped_column( UUID(as_uuid=True), ForeignKey("users.id", ondelete="CASCADE"), nullable=False ) method: Mapped[str] = mapped_column(String(10), nullable=False) path: Mapped[str] = mapped_column(String(255), nullable=False) idempotency_key: Mapped[str] = mapped_column(String(128), nullable=False) request_hash: Mapped[str] = mapped_column(String(64), nullable=False) # NULL is visible only inside the creating transaction; the message and # idempotency row are committed atomically. message_id: Mapped[uuid.UUID | None] = mapped_column( UUID(as_uuid=True), ForeignKey("messages.id", ondelete="CASCADE"), nullable=True ) created_at: Mapped[datetime] = mapped_column( DateTime(timezone=True), default=utc_now, nullable=False ) expires_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), nullable=False) __table_args__ = ( UniqueConstraint( "user_id", "method", "path", "idempotency_key", name="uq_idempotency_scope_key", ), Index("ix_idempotency_records_expires_at", "expires_at"), ) class Attachment(Base): __tablename__ = "attachments" id: Mapped[uuid.UUID] = mapped_column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4) chat_id: Mapped[uuid.UUID] = mapped_column(UUID(as_uuid=True), ForeignKey("chats.id", ondelete="CASCADE"), nullable=False, index=True) owner_id: Mapped[uuid.UUID] = mapped_column(UUID(as_uuid=True), ForeignKey("users.id", ondelete="CASCADE"), nullable=False) filename: Mapped[str] = mapped_column(String(255), nullable=False) content_type: Mapped[str] = mapped_column(String(32), nullable=False) size_bytes: Mapped[int] = mapped_column(nullable=False) sha256: Mapped[str] = mapped_column(String(64), nullable=False) object_key: Mapped[str] = mapped_column(String(512), unique=True, nullable=False) status: Mapped[str] = mapped_column(String(16), nullable=False, default="ready") error_code: Mapped[str | None] = mapped_column(String(64), nullable=True) created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=utc_now, nullable=False) updated_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=utc_now, onupdate=utc_now, nullable=False) class AttachmentUpload(Base): __tablename__ = "attachment_uploads" id: Mapped[uuid.UUID] = mapped_column( UUID(as_uuid=True), primary_key=True, default=uuid.uuid4, ) chat_id: Mapped[uuid.UUID] = mapped_column( UUID(as_uuid=True), ForeignKey("chats.id", ondelete="CASCADE"), nullable=False, ) owner_id: Mapped[uuid.UUID] = mapped_column( UUID(as_uuid=True), ForeignKey("users.id", ondelete="CASCADE"), nullable=False, ) filename: Mapped[str] = mapped_column( String(255), nullable=False, ) content_type: Mapped[str] = mapped_column( String(32), nullable=False, ) expected_size_bytes: Mapped[int] = mapped_column( nullable=False, ) expected_sha256: Mapped[str] = mapped_column( String(64), nullable=False, ) object_key: Mapped[str] = mapped_column( String(512), unique=True, nullable=False, ) status: Mapped[str] = mapped_column( String(16), nullable=False, default="pending", ) expires_at: Mapped[datetime] = mapped_column( DateTime(timezone=True), nullable=False, ) created_at: Mapped[datetime] = mapped_column( DateTime(timezone=True), default=utc_now, nullable=False, ) completed_at: Mapped[datetime | None] = mapped_column( DateTime(timezone=True), nullable=True, ) __table_args__ = ( Index("ix_attachment_uploads_chat_id", "chat_id"), Index("ix_attachment_uploads_owner_id", "owner_id"), Index("ix_attachment_uploads_expires_at", "expires_at"), )