Compare commits
2 Commits
| Author | SHA1 | Date | |
|---|---|---|---|
 architect-bot
|
10dcd6eff2 | ||
|
architect-bot
|
ca06b459ec |
@@ -8,7 +8,7 @@ from sqlalchemy.ext.asyncio import create_async_engine
|
||||
|
||||
from app.config import settings
|
||||
from app.database import Base
|
||||
from app.models import user, session, world, asset, conversation, message # noqa: F401
|
||||
from app.models import user, session, world, asset, friendship, follow, block # noqa: F401
|
||||
|
||||
# this is the Alembic Config object, which provides
|
||||
# access to the values within the .ini file in use.
|
||||
|
||||
@@ -0,0 +1,136 @@
|
||||
"""social graph: friendships, follows, blocks
|
||||
|
||||
Revision ID: 0002_social_graph
|
||||
Revises: 0001_initial
|
||||
Create Date: 2026-08-18 00:00:00.000000
|
||||
"""
|
||||
from typing import Sequence, Union
|
||||
|
||||
from alembic import op
|
||||
import sqlalchemy as sa
|
||||
|
||||
# revision identifiers, used by Alembic.
|
||||
revision: str = "0002_social_graph"
|
||||
down_revision: Union[str, None] = "0001_initial"
|
||||
branch_labels: Union[str, Sequence[str], None] = None
|
||||
depends_on: Union[str, Sequence[str], None] = None
|
||||
|
||||
|
||||
def upgrade() -> None:
|
||||
"""Create friendships, follows, and blocks tables."""
|
||||
op.create_table(
|
||||
"friendships",
|
||||
sa.Column("id", sa.Integer(), nullable=False),
|
||||
sa.Column("requester_id", sa.Integer(), nullable=False),
|
||||
sa.Column("addressee_id", sa.Integer(), nullable=False),
|
||||
sa.Column("status", sa.String(length=20), server_default="pending", nullable=False),
|
||||
sa.Column("created_at", sa.DateTime(timezone=True), server_default=sa.func.now(), nullable=False),
|
||||
sa.Column("updated_at", sa.DateTime(timezone=True), nullable=True),
|
||||
sa.CheckConstraint(
|
||||
"status IN ('pending', 'accepted')",
|
||||
name="ck_friendships_status",
|
||||
),
|
||||
sa.CheckConstraint(
|
||||
"requester_id <> addressee_id",
|
||||
name="ck_friendships_no_self",
|
||||
),
|
||||
sa.ForeignKeyConstraint(
|
||||
["addressee_id"],
|
||||
["users.id"],
|
||||
ondelete="CASCADE",
|
||||
),
|
||||
sa.ForeignKeyConstraint(
|
||||
["requester_id"],
|
||||
["users.id"],
|
||||
ondelete="CASCADE",
|
||||
),
|
||||
sa.PrimaryKeyConstraint("id"),
|
||||
sa.UniqueConstraint(
|
||||
"requester_id",
|
||||
"addressee_id",
|
||||
name="uq_friendships_requester_addressee",
|
||||
),
|
||||
)
|
||||
op.create_index(op.f("ix_friendships_id"), "friendships", ["id"], unique=False)
|
||||
op.create_index(op.f("ix_friendships_addressee_id"), "friendships", ["addressee_id"], unique=False)
|
||||
op.create_index(op.f("ix_friendships_requester_id"), "friendships", ["requester_id"], unique=False)
|
||||
|
||||
op.create_table(
|
||||
"follows",
|
||||
sa.Column("id", sa.Integer(), nullable=False),
|
||||
sa.Column("follower_id", sa.Integer(), nullable=False),
|
||||
sa.Column("followed_id", sa.Integer(), nullable=False),
|
||||
sa.Column("created_at", sa.DateTime(timezone=True), server_default=sa.func.now(), nullable=False),
|
||||
sa.CheckConstraint(
|
||||
"follower_id <> followed_id",
|
||||
name="ck_follows_no_self",
|
||||
),
|
||||
sa.ForeignKeyConstraint(
|
||||
["followed_id"],
|
||||
["users.id"],
|
||||
ondelete="CASCADE",
|
||||
),
|
||||
sa.ForeignKeyConstraint(
|
||||
["follower_id"],
|
||||
["users.id"],
|
||||
ondelete="CASCADE",
|
||||
),
|
||||
sa.PrimaryKeyConstraint("id"),
|
||||
sa.UniqueConstraint(
|
||||
"follower_id",
|
||||
"followed_id",
|
||||
name="uq_follows_follower_followed",
|
||||
),
|
||||
)
|
||||
op.create_index(op.f("ix_follows_id"), "follows", ["id"], unique=False)
|
||||
op.create_index(op.f("ix_follows_followed_id"), "follows", ["followed_id"], unique=False)
|
||||
op.create_index(op.f("ix_follows_follower_id"), "follows", ["follower_id"], unique=False)
|
||||
|
||||
op.create_table(
|
||||
"blocks",
|
||||
sa.Column("id", sa.Integer(), nullable=False),
|
||||
sa.Column("blocker_id", sa.Integer(), nullable=False),
|
||||
sa.Column("blocked_id", sa.Integer(), nullable=False),
|
||||
sa.Column("created_at", sa.DateTime(timezone=True), server_default=sa.func.now(), nullable=False),
|
||||
sa.CheckConstraint(
|
||||
"blocker_id <> blocked_id",
|
||||
name="ck_blocks_no_self",
|
||||
),
|
||||
sa.ForeignKeyConstraint(
|
||||
["blocked_id"],
|
||||
["users.id"],
|
||||
ondelete="CASCADE",
|
||||
),
|
||||
sa.ForeignKeyConstraint(
|
||||
["blocker_id"],
|
||||
["users.id"],
|
||||
ondelete="CASCADE",
|
||||
),
|
||||
sa.PrimaryKeyConstraint("id"),
|
||||
sa.UniqueConstraint(
|
||||
"blocker_id",
|
||||
"blocked_id",
|
||||
name="uq_blocks_blocker_blocked",
|
||||
),
|
||||
)
|
||||
op.create_index(op.f("ix_blocks_id"), "blocks", ["id"], unique=False)
|
||||
op.create_index(op.f("ix_blocks_blocked_id"), "blocks", ["blocked_id"], unique=False)
|
||||
op.create_index(op.f("ix_blocks_blocker_id"), "blocks", ["blocker_id"], unique=False)
|
||||
|
||||
|
||||
def downgrade() -> None:
|
||||
"""Drop friendships, follows, and blocks tables."""
|
||||
op.drop_index(op.f("ix_blocks_blocker_id"), table_name="blocks")
|
||||
op.drop_index(op.f("ix_blocks_blocked_id"), table_name="blocks")
|
||||
op.drop_index(op.f("ix_blocks_id"), table_name="blocks")
|
||||
op.drop_table("blocks")
|
||||
|
||||
op.drop_index(op.f("ix_follows_follower_id"), table_name="follows")
|
||||
op.drop_index(op.f("ix_follows_followed_id"), table_name="follows")
|
||||
op.drop_index(op.f("ix_follows_id"), table_name="follows")
|
||||
op.drop_table("follows")
|
||||
|
||||
op.drop_index(op.f("ix_friendships_requester_id"), table_name="friendships")
|
||||
op.drop_index(op.f("ix_friendships_addressee_id"), table_name="friendships")
|
||||
op.drop_index(op.f("ix_friendships_id"), table_name="friendships")
|
||||
op.drop_table("friendships")
|
||||
@@ -1,228 +0,0 @@
|
||||
"""chat data models
|
||||
|
||||
Revision ID: 1a1983be633a
|
||||
Revises: 0001_initial
|
||||
Create Date: 2026-08-18 15:06:05.948446
|
||||
"""
|
||||
from typing import Sequence, Union
|
||||
|
||||
from alembic import op
|
||||
import sqlalchemy as sa
|
||||
from sqlalchemy.dialects import postgresql
|
||||
|
||||
# revision identifiers, used by Alembic.
|
||||
revision: str = "1a1983be633a"
|
||||
down_revision: Union[str, None] = "0001_initial"
|
||||
branch_labels: Union[str, Sequence[str], None] = None
|
||||
depends_on: Union[str, Sequence[str], None] = None
|
||||
|
||||
|
||||
def upgrade() -> None:
|
||||
"""Create chat tables: conversations, conversation_participants,
|
||||
messages, message_reports."""
|
||||
|
||||
# ── ENUM types ──────────────────────────────────────────────────
|
||||
|
||||
msg_report_reason = postgresql.ENUM(
|
||||
"spam", "inappropriate", "off_topic", "other",
|
||||
name="message_report_reason",
|
||||
)
|
||||
msg_report_reason.create(op.get_bind(), checkfirst=True)
|
||||
|
||||
msg_report_status = postgresql.ENUM(
|
||||
"pending", "resolved", "dismissed",
|
||||
name="message_report_status",
|
||||
)
|
||||
msg_report_status.create(op.get_bind(), checkfirst=True)
|
||||
|
||||
# ── conversations ───────────────────────────────────────────────
|
||||
|
||||
op.create_table(
|
||||
"conversations",
|
||||
sa.Column("id", sa.Integer(), primary_key=True),
|
||||
sa.Column(
|
||||
"latest_message_id",
|
||||
sa.Integer(),
|
||||
sa.ForeignKey("messages.id", ondelete="SET NULL"),
|
||||
nullable=True,
|
||||
),
|
||||
sa.Column(
|
||||
"created_at",
|
||||
sa.DateTime(timezone=True),
|
||||
server_default=sa.func.now(),
|
||||
),
|
||||
sa.Column(
|
||||
"updated_at",
|
||||
sa.DateTime(timezone=True),
|
||||
server_default=sa.func.now(),
|
||||
onupdate=sa.func.now(),
|
||||
),
|
||||
)
|
||||
op.create_index(op.f("ix_conversations_id"), "conversations", ["id"], unique=False)
|
||||
|
||||
# Unique pair constraint — populated later via participants
|
||||
op.create_unique_constraint(
|
||||
"uq_conversation_participants",
|
||||
"conversations",
|
||||
["participant_1_id", "participant_2_id"],
|
||||
)
|
||||
|
||||
# ── conversation_participants ───────────────────────────────────
|
||||
|
||||
op.create_table(
|
||||
"conversation_participants",
|
||||
sa.Column("id", sa.Integer(), primary_key=True),
|
||||
sa.Column(
|
||||
"conversation_id",
|
||||
sa.Integer(),
|
||||
sa.ForeignKey("conversations.id", ondelete="CASCADE"),
|
||||
nullable=False,
|
||||
),
|
||||
sa.Column(
|
||||
"user_id",
|
||||
sa.Integer(),
|
||||
sa.ForeignKey("users.id", ondelete="CASCADE"),
|
||||
nullable=False,
|
||||
),
|
||||
sa.Column("left_at", sa.DateTime(timezone=True), nullable=True),
|
||||
sa.Column("last_read_at", sa.DateTime(timezone=True), nullable=True),
|
||||
sa.Column(
|
||||
"created_at",
|
||||
sa.DateTime(timezone=True),
|
||||
server_default=sa.func.now(),
|
||||
),
|
||||
)
|
||||
op.create_index(
|
||||
op.f("ix_conversation_participants_id"),
|
||||
"conversation_participants",
|
||||
["id"],
|
||||
unique=False,
|
||||
)
|
||||
op.create_index(
|
||||
op.f("ix_conversation_participants_user_id"),
|
||||
"conversation_participants",
|
||||
["user_id"],
|
||||
unique=False,
|
||||
)
|
||||
op.create_unique_constraint(
|
||||
"uq_conversation_user",
|
||||
"conversation_participants",
|
||||
["conversation_id", "user_id"],
|
||||
)
|
||||
|
||||
# ── messages ────────────────────────────────────────────────────
|
||||
|
||||
op.create_table(
|
||||
"messages",
|
||||
sa.Column("id", sa.Integer(), primary_key=True),
|
||||
sa.Column(
|
||||
"sender_id",
|
||||
sa.Integer(),
|
||||
sa.ForeignKey("users.id", ondelete="CASCADE"),
|
||||
nullable=False,
|
||||
),
|
||||
sa.Column(
|
||||
"conversation_id",
|
||||
sa.Integer(),
|
||||
sa.ForeignKey("conversations.id", ondelete="CASCADE"),
|
||||
nullable=True,
|
||||
),
|
||||
sa.Column(
|
||||
"world_id",
|
||||
sa.Integer(),
|
||||
sa.ForeignKey("worlds.id", ondelete="CASCADE"),
|
||||
nullable=True,
|
||||
),
|
||||
sa.Column("room_id", sa.String(length=36), nullable=True),
|
||||
sa.Column("body", sa.Text(), nullable=False),
|
||||
sa.Column("is_filtered", sa.Boolean(), nullable=False, server_default="false"),
|
||||
sa.Column(
|
||||
"created_at",
|
||||
sa.DateTime(timezone=True),
|
||||
server_default=sa.func.now(),
|
||||
),
|
||||
)
|
||||
op.create_index(op.f("ix_messages_id"), "messages", ["id"], unique=False)
|
||||
op.create_index(
|
||||
"ix_messages_conversation_created",
|
||||
"messages",
|
||||
["conversation_id", "created_at"],
|
||||
)
|
||||
op.create_index(
|
||||
"ix_messages_world_created",
|
||||
"messages",
|
||||
["world_id", "created_at"],
|
||||
)
|
||||
|
||||
# ── message_reports ─────────────────────────────────────────────
|
||||
|
||||
op.create_table(
|
||||
"message_reports",
|
||||
sa.Column("id", sa.Integer(), primary_key=True),
|
||||
sa.Column(
|
||||
"message_id",
|
||||
sa.Integer(),
|
||||
sa.ForeignKey("messages.id", ondelete="CASCADE"),
|
||||
nullable=False,
|
||||
),
|
||||
sa.Column(
|
||||
"reporter_id",
|
||||
sa.Integer(),
|
||||
sa.ForeignKey("users.id", ondelete="SET NULL"),
|
||||
nullable=False,
|
||||
),
|
||||
sa.Column(
|
||||
"reason",
|
||||
postgresql.ENUM(
|
||||
"spam", "inappropriate", "off_topic", "other",
|
||||
name="message_report_reason",
|
||||
create_type=False,
|
||||
),
|
||||
nullable=False,
|
||||
),
|
||||
sa.Column(
|
||||
"status",
|
||||
postgresql.ENUM(
|
||||
"pending", "resolved", "dismissed",
|
||||
name="message_report_status",
|
||||
create_type=False,
|
||||
),
|
||||
nullable=False,
|
||||
server_default="pending",
|
||||
),
|
||||
sa.Column(
|
||||
"moderator_id",
|
||||
sa.Integer(),
|
||||
sa.ForeignKey("users.id", ondelete="SET NULL"),
|
||||
nullable=True,
|
||||
),
|
||||
sa.Column("resolved_at", sa.DateTime(timezone=True), nullable=True),
|
||||
sa.Column(
|
||||
"created_at",
|
||||
sa.DateTime(timezone=True),
|
||||
server_default=sa.func.now(),
|
||||
),
|
||||
)
|
||||
op.create_index(op.f("ix_message_reports_id"), "message_reports", ["id"], unique=False)
|
||||
|
||||
|
||||
def downgrade() -> None:
|
||||
"""Drop chat tables in reverse dependency order."""
|
||||
op.drop_index(op.f("ix_message_reports_id"), table_name="message_reports")
|
||||
op.drop_table("message_reports")
|
||||
|
||||
op.drop_index(op.f("ix_messages_world_created"), table_name="messages")
|
||||
op.drop_index(op.f("ix_messages_conversation_created"), table_name="messages")
|
||||
op.drop_index(op.f("ix_messages_id"), table_name="messages")
|
||||
op.drop_table("messages")
|
||||
|
||||
op.drop_index(op.f("ix_conversation_participants_user_id"), table_name="conversation_participants")
|
||||
op.drop_index(op.f("ix_conversation_participants_id"), table_name="conversation_participants")
|
||||
op.drop_table("conversation_participants")
|
||||
|
||||
op.drop_index(op.f("ix_conversations_id"), table_name="conversations")
|
||||
op.drop_table("conversations")
|
||||
|
||||
# Drop ENUM types
|
||||
postgresql.ENUM(name="message_report_status").drop(op.get_bind(), checkfirst=True)
|
||||
postgresql.ENUM(name="message_report_reason").drop(op.get_bind(), checkfirst=True)
|
||||
@@ -2,16 +2,17 @@ from app.models.user import User
|
||||
from app.models.session import Session
|
||||
from app.models.world import World
|
||||
from app.models.asset import Asset
|
||||
from app.models.conversation import Conversation, ConversationParticipant
|
||||
from app.models.message import Message, MessageReport
|
||||
from app.models.friendship import Friendship, FriendshipStatus
|
||||
from app.models.follow import Follow
|
||||
from app.models.block import Block
|
||||
|
||||
__all__ = [
|
||||
"User",
|
||||
"Session",
|
||||
"World",
|
||||
"Asset",
|
||||
"Conversation",
|
||||
"ConversationParticipant",
|
||||
"Message",
|
||||
"MessageReport",
|
||||
"Friendship",
|
||||
"FriendshipStatus",
|
||||
"Follow",
|
||||
"Block",
|
||||
]
|
||||
|
||||
@@ -0,0 +1,48 @@
|
||||
"""Block model: a hard deny that also hides content and prevents DMs."""
|
||||
|
||||
from sqlalchemy import CheckConstraint, Column, DateTime, ForeignKey, Integer, UniqueConstraint
|
||||
from sqlalchemy.orm import relationship
|
||||
from sqlalchemy.sql import func
|
||||
|
||||
from app.database import Base
|
||||
|
||||
|
||||
class Block(Base):
|
||||
__tablename__ = "blocks"
|
||||
__table_args__ = (
|
||||
UniqueConstraint(
|
||||
"blocker_id",
|
||||
"blocked_id",
|
||||
name="uq_blocks_pair",
|
||||
),
|
||||
CheckConstraint(
|
||||
"blocker_id <> blocked_id",
|
||||
name="ck_blocks_no_self",
|
||||
),
|
||||
)
|
||||
|
||||
id = Column(Integer, primary_key=True, index=True)
|
||||
blocker_id = Column(
|
||||
Integer,
|
||||
ForeignKey("users.id", ondelete="CASCADE"),
|
||||
nullable=False,
|
||||
index=True,
|
||||
)
|
||||
blocked_id = Column(
|
||||
Integer,
|
||||
ForeignKey("users.id", ondelete="CASCADE"),
|
||||
nullable=False,
|
||||
index=True,
|
||||
)
|
||||
created_at = Column(DateTime(timezone=True), server_default=func.now())
|
||||
|
||||
blocker = relationship(
|
||||
"User",
|
||||
foreign_keys=[blocker_id],
|
||||
back_populates="blocks_issued",
|
||||
)
|
||||
blocked = relationship(
|
||||
"User",
|
||||
foreign_keys=[blocked_id],
|
||||
back_populates="blocks_received",
|
||||
)
|
||||
@@ -1,94 +0,0 @@
|
||||
"""Conversation and participant models for DM chat."""
|
||||
|
||||
from sqlalchemy import (
|
||||
Column,
|
||||
Integer,
|
||||
String,
|
||||
DateTime,
|
||||
ForeignKey,
|
||||
UniqueConstraint,
|
||||
Index,
|
||||
)
|
||||
from sqlalchemy.orm import relationship
|
||||
from sqlalchemy.sql import func
|
||||
|
||||
from app.database import Base
|
||||
|
||||
|
||||
class Conversation(Base):
|
||||
"""A DM conversation between two users.
|
||||
|
||||
Each conversation is uniquely identified by the pair of participant
|
||||
user IDs (regardless of order). A conversation is soft-deletable
|
||||
via ``left_at`` on the participant row.
|
||||
"""
|
||||
|
||||
__tablename__ = "conversations"
|
||||
|
||||
id = Column(Integer, primary_key=True, index=True)
|
||||
# The most recent message's ID in this conversation (denormalised
|
||||
# for fast listing; updated by triggers or app logic).
|
||||
latest_message_id = Column(
|
||||
Integer, ForeignKey("messages.id", ondelete="SET NULL"), nullable=True
|
||||
)
|
||||
created_at = Column(DateTime(timezone=True), server_default=func.now())
|
||||
updated_at = Column(DateTime(timezone=True), server_default=func.now(), onupdate=func.now())
|
||||
|
||||
# Relationships
|
||||
latest_message = relationship(
|
||||
"Message",
|
||||
foreign_keys=[latest_message_id],
|
||||
lazy="select",
|
||||
)
|
||||
participants = relationship(
|
||||
"ConversationParticipant",
|
||||
back_populates="conversation",
|
||||
cascade="all, delete-orphan",
|
||||
order_by="ConversationParticipant.id",
|
||||
)
|
||||
|
||||
__table_args__ = (
|
||||
# Ensure exactly one conversation per unique pair of users
|
||||
UniqueConstraint("participant_1_id", "participant_2_id", name="uq_conversation_participants"),
|
||||
)
|
||||
|
||||
def __repr__(self) -> str:
|
||||
return f"<Conversation id={self.id}>"
|
||||
|
||||
|
||||
class ConversationParticipant(Base):
|
||||
"""Link table between conversations and users.
|
||||
|
||||
Stores per-participant metadata such as when they left the
|
||||
conversation (for soft-delete / archival).
|
||||
"""
|
||||
|
||||
__tablename__ = "conversation_participants"
|
||||
|
||||
id = Column(Integer, primary_key=True, index=True)
|
||||
conversation_id = Column(
|
||||
Integer,
|
||||
ForeignKey("conversations.id", ondelete="CASCADE"),
|
||||
nullable=False,
|
||||
)
|
||||
user_id = Column(
|
||||
Integer,
|
||||
ForeignKey("users.id", ondelete="CASCADE"),
|
||||
nullable=False,
|
||||
)
|
||||
# When the participant left (NULL = still active).
|
||||
left_at = Column(DateTime(timezone=True), nullable=True)
|
||||
# Timestamp of the last message read by this participant.
|
||||
last_read_at = Column(DateTime(timezone=True), nullable=True)
|
||||
created_at = Column(DateTime(timezone=True), server_default=func.now())
|
||||
|
||||
conversation = relationship("Conversation", back_populates="participants")
|
||||
user = relationship("User")
|
||||
|
||||
__table_args__ = (
|
||||
UniqueConstraint("conversation_id", "user_id", name="uq_conversation_user"),
|
||||
Index("ix_conversation_participants_user_id", "user_id"),
|
||||
)
|
||||
|
||||
def __repr__(self) -> str:
|
||||
return f"<ConversationParticipant conversation_id={self.conversation_id} user_id={self.user_id}>"
|
||||
@@ -0,0 +1,48 @@
|
||||
"""Follow model: a one-way relationship requiring no approval."""
|
||||
|
||||
from sqlalchemy import CheckConstraint, Column, DateTime, ForeignKey, Integer, UniqueConstraint
|
||||
from sqlalchemy.orm import relationship
|
||||
from sqlalchemy.sql import func
|
||||
|
||||
from app.database import Base
|
||||
|
||||
|
||||
class Follow(Base):
|
||||
__tablename__ = "follows"
|
||||
__table_args__ = (
|
||||
UniqueConstraint(
|
||||
"follower_id",
|
||||
"followed_id",
|
||||
name="uq_follows_pair",
|
||||
),
|
||||
CheckConstraint(
|
||||
"follower_id <> followed_id",
|
||||
name="ck_follows_no_self",
|
||||
),
|
||||
)
|
||||
|
||||
id = Column(Integer, primary_key=True, index=True)
|
||||
follower_id = Column(
|
||||
Integer,
|
||||
ForeignKey("users.id", ondelete="CASCADE"),
|
||||
nullable=False,
|
||||
index=True,
|
||||
)
|
||||
followed_id = Column(
|
||||
Integer,
|
||||
ForeignKey("users.id", ondelete="CASCADE"),
|
||||
nullable=False,
|
||||
index=True,
|
||||
)
|
||||
created_at = Column(DateTime(timezone=True), server_default=func.now())
|
||||
|
||||
follower = relationship(
|
||||
"User",
|
||||
foreign_keys=[follower_id],
|
||||
back_populates="following",
|
||||
)
|
||||
followed = relationship(
|
||||
"User",
|
||||
foreign_keys=[followed_id],
|
||||
back_populates="followers",
|
||||
)
|
||||
@@ -0,0 +1,74 @@
|
||||
"""Friendship model: a one-directional request that becomes mutual on accept."""
|
||||
|
||||
from sqlalchemy import (
|
||||
CheckConstraint,
|
||||
Column,
|
||||
DateTime,
|
||||
ForeignKey,
|
||||
Integer,
|
||||
String,
|
||||
UniqueConstraint,
|
||||
)
|
||||
from sqlalchemy.orm import relationship
|
||||
from sqlalchemy.sql import func
|
||||
|
||||
from app.database import Base
|
||||
|
||||
|
||||
class FriendshipStatus:
|
||||
"""String values for the friendship.status column."""
|
||||
|
||||
PENDING = "pending"
|
||||
ACCEPTED = "accepted"
|
||||
|
||||
|
||||
class Friendship(Base):
|
||||
__tablename__ = "friendships"
|
||||
__table_args__ = (
|
||||
UniqueConstraint(
|
||||
"requester_id",
|
||||
"addressee_id",
|
||||
name="uq_friendships_pair",
|
||||
),
|
||||
CheckConstraint(
|
||||
"requester_id <> addressee_id",
|
||||
name="ck_friendships_no_self",
|
||||
),
|
||||
CheckConstraint(
|
||||
"status IN ('pending', 'accepted')",
|
||||
name="ck_friendships_status",
|
||||
),
|
||||
)
|
||||
|
||||
id = Column(Integer, primary_key=True, index=True)
|
||||
requester_id = Column(
|
||||
Integer,
|
||||
ForeignKey("users.id", ondelete="CASCADE"),
|
||||
nullable=False,
|
||||
index=True,
|
||||
)
|
||||
addressee_id = Column(
|
||||
Integer,
|
||||
ForeignKey("users.id", ondelete="CASCADE"),
|
||||
nullable=False,
|
||||
index=True,
|
||||
)
|
||||
status = Column(
|
||||
String(20),
|
||||
nullable=False,
|
||||
default=FriendshipStatus.PENDING,
|
||||
server_default=FriendshipStatus.PENDING,
|
||||
)
|
||||
created_at = Column(DateTime(timezone=True), server_default=func.now())
|
||||
updated_at = Column(DateTime(timezone=True), onupdate=func.now())
|
||||
|
||||
requester = relationship(
|
||||
"User",
|
||||
foreign_keys=[requester_id],
|
||||
back_populates="sent_friend_requests",
|
||||
)
|
||||
addressee = relationship(
|
||||
"User",
|
||||
foreign_keys=[addressee_id],
|
||||
back_populates="received_friend_requests",
|
||||
)
|
||||
@@ -1,96 +0,0 @@
|
||||
"""Message models — messages, message reports."""
|
||||
|
||||
from sqlalchemy import (
|
||||
Column,
|
||||
Integer,
|
||||
String,
|
||||
Text,
|
||||
DateTime,
|
||||
ForeignKey,
|
||||
Boolean,
|
||||
Index,
|
||||
)
|
||||
from sqlalchemy.dialects.postgresql import ENUM
|
||||
from sqlalchemy.orm import relationship
|
||||
from sqlalchemy.sql import func
|
||||
import enum
|
||||
|
||||
from app.database import Base
|
||||
|
||||
|
||||
class MessageReportStatus(str, enum.Enum):
|
||||
PENDING = "pending"
|
||||
RESOLVED = "resolved"
|
||||
DISMISSED = "dismissed"
|
||||
|
||||
|
||||
class MessageReportReason(str, enum.Enum):
|
||||
SPAM = "spam"
|
||||
INAPPROPRIATE = "inappropriate"
|
||||
OFF_TOPIC = "off_topic"
|
||||
OTHER = "other"
|
||||
|
||||
|
||||
class Message(Base):
|
||||
__tablename__ = "messages"
|
||||
|
||||
id = Column(Integer, primary_key=True, index=True)
|
||||
sender_id = Column(
|
||||
Integer, ForeignKey("users.id", ondelete="CASCADE"), nullable=False
|
||||
)
|
||||
conversation_id = Column(
|
||||
Integer, ForeignKey("conversations.id", ondelete="CASCADE"), nullable=True
|
||||
)
|
||||
world_id = Column(
|
||||
Integer, ForeignKey("worlds.id", ondelete="CASCADE"), nullable=True
|
||||
)
|
||||
room_id = Column(String(36), nullable=True)
|
||||
body = Column(Text, nullable=False)
|
||||
is_filtered = Column(Boolean, default=False, nullable=False)
|
||||
created_at = Column(DateTime(timezone=True), server_default=func.now())
|
||||
|
||||
# Relationships
|
||||
sender = relationship("User", back_populates="sent_messages")
|
||||
conversation = relationship("Conversation", back_populates="messages")
|
||||
world = relationship("World")
|
||||
reports = relationship(
|
||||
"MessageReport", back_populates="message", cascade="all, delete-orphan"
|
||||
)
|
||||
|
||||
# Indexes for history queries
|
||||
__table_args__ = (
|
||||
Index("ix_messages_conversation_created", "conversation_id", "created_at"),
|
||||
Index("ix_messages_world_created", "world_id", "created_at"),
|
||||
)
|
||||
|
||||
|
||||
class MessageReport(Base):
|
||||
__tablename__ = "message_reports"
|
||||
|
||||
id = Column(Integer, primary_key=True, index=True)
|
||||
message_id = Column(
|
||||
Integer, ForeignKey("messages.id", ondelete="CASCADE"), nullable=False
|
||||
)
|
||||
reporter_id = Column(
|
||||
Integer, ForeignKey("users.id", ondelete="SET NULL"), nullable=False
|
||||
)
|
||||
reason = Column(
|
||||
ENUM(MessageReportReason, name="message_report_reason", create_type=False),
|
||||
nullable=False,
|
||||
)
|
||||
details = Column(Text, nullable=True)
|
||||
status = Column(
|
||||
ENUM(MessageReportStatus, name="message_report_status", create_type=False),
|
||||
default=MessageReportStatus.PENDING,
|
||||
nullable=False,
|
||||
)
|
||||
moderator_id = Column(
|
||||
Integer, ForeignKey("users.id", ondelete="SET NULL"), nullable=True
|
||||
)
|
||||
resolved_at = Column(DateTime(timezone=True), nullable=True)
|
||||
created_at = Column(DateTime(timezone=True), server_default=func.now())
|
||||
|
||||
# Relationships
|
||||
message = relationship("Message", back_populates="reports")
|
||||
reporter = relationship("User", foreign_keys=[reporter_id])
|
||||
moderator = relationship("User", foreign_keys=[moderator_id])
|
||||
@@ -19,14 +19,41 @@ class User(Base):
|
||||
sessions = relationship("Session", back_populates="user", cascade="all, delete-orphan")
|
||||
worlds = relationship("World", back_populates="owner")
|
||||
assets = relationship("Asset", back_populates="owner")
|
||||
sent_messages = relationship("Message", back_populates="sender")
|
||||
reported_messages = relationship(
|
||||
"MessageReport",
|
||||
foreign_keys="MessageReport.reporter_id",
|
||||
back_populates="reporter",
|
||||
|
||||
# Social graph
|
||||
sent_friend_requests = relationship(
|
||||
"Friendship",
|
||||
foreign_keys="Friendship.requester_id",
|
||||
back_populates="requester",
|
||||
cascade="all, delete-orphan",
|
||||
)
|
||||
moderated_reports = relationship(
|
||||
"MessageReport",
|
||||
foreign_keys="MessageReport.moderator_id",
|
||||
back_populates="moderator",
|
||||
received_friend_requests = relationship(
|
||||
"Friendship",
|
||||
foreign_keys="Friendship.addressee_id",
|
||||
back_populates="addressee",
|
||||
cascade="all, delete-orphan",
|
||||
)
|
||||
following = relationship(
|
||||
"Follow",
|
||||
foreign_keys="Follow.follower_id",
|
||||
back_populates="follower",
|
||||
cascade="all, delete-orphan",
|
||||
)
|
||||
followers = relationship(
|
||||
"Follow",
|
||||
foreign_keys="Follow.followed_id",
|
||||
back_populates="followed",
|
||||
cascade="all, delete-orphan",
|
||||
)
|
||||
blocks_issued = relationship(
|
||||
"Block",
|
||||
foreign_keys="Block.blocker_id",
|
||||
back_populates="blocker",
|
||||
cascade="all, delete-orphan",
|
||||
)
|
||||
blocks_received = relationship(
|
||||
"Block",
|
||||
foreign_keys="Block.blocked_id",
|
||||
back_populates="blocked",
|
||||
cascade="all, delete-orphan",
|
||||
)
|
||||
|
||||
Reference in New Issue
Block a user