Files
mtusi2026lab7/app/db/models.py

489 lines
14 KiB
Python

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)
# Ключ оригинала в конечном S3-prefix.
object_key: Mapped[str] = mapped_column(
String(512),
unique=True,
nullable=False,
)
# Ключ исходного файла в durable staging.
staging_key: Mapped[str | None] = mapped_column(
String(512),
nullable=True,
)
status: Mapped[str] = mapped_column(
String(16),
nullable=False,
default="ready",
)
error_code: Mapped[str | None] = mapped_column(
String(64),
nullable=True,
)
pipeline_version: Mapped[int] = mapped_column(
nullable=False,
default=1,
server_default="1", )
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,
)
__table_args__ = (
UniqueConstraint(
"staging_key",
name="uq_attachments_staging_key",
),
)
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"),
)
class ProcessingJob(Base):
__tablename__ = "processing_jobs"
id: Mapped[uuid.UUID] = mapped_column(
UUID(as_uuid=True),
primary_key=True,
default=uuid.uuid4,
)
attachment_id: Mapped[uuid.UUID] = mapped_column(
UUID(as_uuid=True),
ForeignKey("attachments.id", ondelete="CASCADE"),
nullable=False,
)
pipeline_version: Mapped[int] = mapped_column(
nullable=False,
default=1,
server_default="1", )
attempt: Mapped[int] = mapped_column(
nullable=False,
default=1,
server_default="1", )
status: Mapped[str] = mapped_column(
String(16),
nullable=False,
default="queued",
server_default="queued", )
available_at: Mapped[datetime] = mapped_column(
DateTime(timezone=True),
default=utc_now,
nullable=False,
)
lease_until: Mapped[datetime | None] = mapped_column(
DateTime(timezone=True),
nullable=True,
)
worker_id: Mapped[str | None] = mapped_column(
String(128),
nullable=True,
)
last_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,
)
__table_args__ = (
UniqueConstraint(
"attachment_id",
"pipeline_version",
name="uq_processing_job_attachment_pipeline",
),
Index(
"ix_processing_jobs_status_available",
"status",
"available_at",
),
Index(
"ix_processing_jobs_lease_until",
"lease_until",
),
)
class AttachmentVariant(Base):
__tablename__ = "attachment_variants"
id: Mapped[uuid.UUID] = mapped_column(
UUID(as_uuid=True),
primary_key=True,
default=uuid.uuid4,
)
attachment_id: Mapped[uuid.UUID] = mapped_column(
UUID(as_uuid=True),
ForeignKey("attachments.id", ondelete="CASCADE"),
nullable=False,
)
variant: Mapped[str] = mapped_column(
String(32),
nullable=False,
)
object_key: Mapped[str] = mapped_column(
String(512),
unique=True,
nullable=False,
)
content_type: Mapped[str] = mapped_column(
String(32),
nullable=False,
)
width: Mapped[int] = mapped_column(nullable=False)
height: Mapped[int] = mapped_column(nullable=False)
size_bytes: Mapped[int] = mapped_column(nullable=False)
sha256: Mapped[str] = mapped_column(
String(64),
nullable=False,
)
created_at: Mapped[datetime] = mapped_column(
DateTime(timezone=True),
default=utc_now,
nullable=False,
)
__table_args__ = (
UniqueConstraint(
"attachment_id",
"variant",
name="uq_attachment_variant",
),
Index(
"ix_attachment_variants_attachment_id",
"attachment_id",
),
)
class OutboxEvent(Base):
__tablename__ = "outbox_events"
id: Mapped[uuid.UUID] = mapped_column(
UUID(as_uuid=True),
primary_key=True,
default=uuid.uuid4,
)
event_type: Mapped[str] = mapped_column(
String(128),
nullable=False,
)
aggregate_id: Mapped[uuid.UUID] = mapped_column(
UUID(as_uuid=True),
nullable=False,
)
payload: Mapped[dict] = mapped_column(
JSON,
nullable=False,
)
status: Mapped[str] = mapped_column(
String(16),
nullable=False,
default="pending",
server_default="pending", )
attempts: Mapped[int] = mapped_column(
nullable=False,
default=0,
server_default="0", )
created_at: Mapped[datetime] = mapped_column(
DateTime(timezone=True),
default=utc_now,
nullable=False,
)
published_at: Mapped[datetime | None] = mapped_column(
DateTime(timezone=True),
nullable=True,
)
last_error: Mapped[str | None] = mapped_column(
String(256),
nullable=True,
)
__table_args__ = (
Index(
"ix_outbox_events_status_created",
"status",
"created_at",
),
)