Compare commits

..

2 Commits

9 changed files with 512 additions and 150 deletions
+1 -1
View File
@@ -8,7 +8,7 @@ from sqlalchemy.ext.asyncio import create_async_engine
from app.config import settings from app.config import settings
from app.database import Base from app.database import Base
from app.models import user, session, world, asset, group # noqa: F401 from app.models import user, session, world, asset # noqa: F401
# this is the Alembic Config object, which provides # this is the Alembic Config object, which provides
# access to the values within the .ini file in use. # access to the values within the .ini file in use.
+14 -9
View File
@@ -19,16 +19,16 @@ depends_on: Union[str, Sequence[str], None] = None
def upgrade() -> None: def upgrade() -> None:
"""Upgrade database schema.""" """Upgrade database schema."""
# Create ENUM types (create once, reuse below) # Create ENUM types
world_visibility_enum = postgresql.ENUM( world_visibility = postgresql.ENUM(
"public", "private", "friends_only", name="world_visibility" "public", "private", "friends_only", name="world_visibility"
) )
world_visibility_enum.create(op.get_bind(), checkfirst=True) world_visibility.create(op.get_bind(), checkfirst=True)
asset_type_enum = postgresql.ENUM( asset_type = postgresql.ENUM(
"image", "audio", "video", "document", "model_3d", "other", name="asset_type" "image", "audio", "video", "document", "model_3d", "other", name="asset_type"
) )
asset_type_enum.create(op.get_bind(), checkfirst=True) asset_type.create(op.get_bind(), checkfirst=True)
# Create users table # Create users table
op.create_table( op.create_table(
@@ -59,7 +59,7 @@ def upgrade() -> None:
) )
op.create_index(op.f("ix_sessions_token"), "sessions", ["token"], unique=True) op.create_index(op.f("ix_sessions_token"), "sessions", ["token"], unique=True)
# Create worlds table — reuse world_visibility_enum (no second create) # Create worlds table
op.create_table( op.create_table(
"worlds", "worlds",
sa.Column("id", sa.Integer(), primary_key=True), sa.Column("id", sa.Integer(), primary_key=True),
@@ -68,7 +68,9 @@ def upgrade() -> None:
sa.Column("owner_id", sa.Integer(), nullable=False), sa.Column("owner_id", sa.Integer(), nullable=False),
sa.Column( sa.Column(
"visibility", "visibility",
world_visibility_enum, postgresql.ENUM(
"public", "private", "friends_only", name="world_visibility"
),
nullable=True, nullable=True,
), ),
sa.Column("created_at", sa.DateTime(timezone=True), server_default=sa.func.now()), sa.Column("created_at", sa.DateTime(timezone=True), server_default=sa.func.now()),
@@ -77,7 +79,7 @@ def upgrade() -> None:
) )
op.create_index(op.f("ix_worlds_id"), "worlds", ["id"], unique=False) op.create_index(op.f("ix_worlds_id"), "worlds", ["id"], unique=False)
# Create assets table — reuse asset_type_enum (no second create) # Create assets table
op.create_table( op.create_table(
"assets", "assets",
sa.Column("id", sa.Integer(), primary_key=True), sa.Column("id", sa.Integer(), primary_key=True),
@@ -85,7 +87,10 @@ def upgrade() -> None:
sa.Column("description", sa.Text(), nullable=True), sa.Column("description", sa.Text(), nullable=True),
sa.Column( sa.Column(
"asset_type", "asset_type",
asset_type_enum, postgresql.ENUM(
"image", "audio", "video", "document", "model_3d", "other",
name="asset_type",
),
nullable=False, nullable=False,
), ),
sa.Column("file_path", sa.String(length=512), nullable=False), sa.Column("file_path", sa.String(length=512), nullable=False),
@@ -1,78 +0,0 @@
"""add groups and group_members tables
Revision ID: 0003_add_groups_and_members
Revises: 0002_add_date_of_birth_to_users
Create Date: 2025-01-01 00:00:00.000000
"""
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 = "0003_add_groups_and_members"
down_revision: Union[str, None] = "0002_add_date_of_birth_to_users"
branch_labels: Union[str, Sequence[str], None] = None
depends_on: Union[str, Sequence[str], None] = None
def upgrade() -> None:
"""Create groups and group_members tables."""
# Create group_member_role ENUM type
group_member_role = postgresql.ENUM(
"owner", "admin", "member", name="group_member_role"
)
group_member_role.create(op.get_bind(), checkfirst=True)
# Create groups table
op.create_table(
"groups",
sa.Column("id", sa.Integer(), primary_key=True, index=True),
sa.Column("name", sa.String(length=255), nullable=False),
sa.Column("description", sa.Text(), nullable=True),
sa.Column(
"owner_id",
sa.Integer(),
sa.ForeignKey("users.id", ondelete="CASCADE"),
nullable=False,
),
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()),
)
# Create group_members table
op.create_table(
"group_members",
sa.Column(
"group_id",
sa.Integer(),
sa.ForeignKey("groups.id", ondelete="CASCADE"),
primary_key=True,
),
sa.Column(
"user_id",
sa.Integer(),
sa.ForeignKey("users.id", ondelete="CASCADE"),
primary_key=True,
),
sa.Column(
"role",
postgresql.ENUM(
"owner", "admin", "member", name="group_member_role", create_type=False
),
nullable=False,
default="member",
),
sa.Column("joined_at", sa.DateTime(timezone=True), server_default=sa.func.now()),
)
def downgrade() -> None:
"""Drop group_members and groups tables."""
op.drop_table("group_members")
op.drop_table("groups")
# Drop group_member_role ENUM type
postgresql.ENUM(name="group_member_role").drop(op.get_bind(), checkfirst=True)
@@ -0,0 +1,268 @@
"""add trust & safety columns and tables
Revision ID: 0003_trust_safety
Revises: 0002_add_date_of_birth_to_users
Create Date: 2025-07-10 00:00:00.000000
"""
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 = "0003_trust_safety"
down_revision: Union[str, None] = "0002_add_date_of_birth_to_users"
branch_labels: Union[str, Sequence[str], None] = None
depends_on: Union[str, Sequence[str], None] = None
def upgrade() -> None:
"""Add role/banned_until/suspended_until to users, and create
reports, moderation_actions, and appeals tables."""
# ── ENUM types ──────────────────────────────────────────────
role_enum = postgresql.ENUM("user", "moderator", "admin", name="role")
role_enum.create(op.get_bind(), checkfirst=True)
target_type_enum = postgresql.ENUM(
"user", "world", "asset", "chat_message", name="target_type"
)
target_type_enum.create(op.get_bind(), checkfirst=True)
reason_category_enum = postgresql.ENUM(
"harassment",
"hate_speech",
"sexual_content",
"violence",
"spam",
"self_harm",
"other",
name="reason_category",
)
reason_category_enum.create(op.get_bind(), checkfirst=True)
report_status_enum = postgresql.ENUM(
"open", "in_review", "resolved", "dismissed", name="report_status"
)
report_status_enum.create(op.get_bind(), checkfirst=True)
resolution_enum = postgresql.ENUM(
"action_taken",
"action_not_needed",
"duplicate",
"escalated",
name="resolution",
)
resolution_enum.create(op.get_bind(), checkfirst=True)
moderation_action_enum = postgresql.ENUM(
"warn", "suspend", "ban", "restrict", "remove", "restore",
name="moderation_action",
)
moderation_action_enum.create(op.get_bind(), checkfirst=True)
appeal_status_enum = postgresql.ENUM(
"pending", "approved", "rejected", name="appeal_status"
)
appeal_status_enum.create(op.get_bind(), checkfirst=True)
# ── users table changes ─────────────────────────────────────
op.add_column(
"users",
sa.Column(
"role",
role_enum,
nullable=False,
server_default="user",
),
)
op.add_column(
"users",
sa.Column("banned_until", sa.DateTime(timezone=True), nullable=True),
)
op.add_column(
"users",
sa.Column("suspended_until", sa.DateTime(timezone=True), nullable=True),
)
# ── reports table ───────────────────────────────────────────
op.create_table(
"reports",
sa.Column("id", sa.BigInteger(), primary_key=True, autoincrement=True),
sa.Column(
"reporter_id", sa.Integer(), sa.ForeignKey("users.id", ondelete="CASCADE"), nullable=False
),
sa.Column(
"target_type",
target_type_enum,
nullable=False,
),
sa.Column("target_id", sa.String(length=64), nullable=False),
sa.Column(
"reason_category",
reason_category_enum,
nullable=False,
),
sa.Column("details", sa.Text(), nullable=True),
sa.Column(
"status",
report_status_enum,
nullable=False,
server_default="open",
),
sa.Column(
"resolved_by",
sa.Integer(),
sa.ForeignKey("users.id", ondelete="SET NULL"),
nullable=True,
),
sa.Column(
"resolution",
resolution_enum,
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),
onupdate=sa.func.now(),
nullable=False,
),
sa.Column("resolved_at", sa.DateTime(timezone=True), nullable=True),
sa.UniqueConstraint("reporter_id", "target_type", "target_id"),
)
op.create_index(op.f("ix_reports_id"), "reports", ["id"], unique=False)
op.create_index(op.f("ix_reports_reporter_id"), "reports", ["reporter_id"], unique=False)
# ── moderation_actions table ────────────────────────────────
op.create_table(
"moderation_actions",
sa.Column("id", sa.BigInteger(), primary_key=True, autoincrement=True),
sa.Column(
"moderator_id",
sa.Integer(),
sa.ForeignKey("users.id", ondelete="CASCADE"),
nullable=False,
),
sa.Column(
"target_user_id",
sa.Integer(),
sa.ForeignKey("users.id", ondelete="SET NULL"),
nullable=True,
),
sa.Column(
"target_type",
target_type_enum,
nullable=False,
),
sa.Column("target_id", sa.String(length=64), nullable=False),
sa.Column(
"action",
moderation_action_enum,
nullable=False,
),
sa.Column("reason", sa.Text(), nullable=False),
sa.Column(
"report_id",
sa.Integer(),
sa.ForeignKey("reports.id", ondelete="SET NULL"),
nullable=True,
),
sa.Column("expires_at", sa.DateTime(timezone=True), nullable=True),
sa.Column("revoked_at", sa.DateTime(timezone=True), nullable=True),
sa.Column(
"created_at",
sa.DateTime(timezone=True),
server_default=sa.func.now(),
nullable=False,
),
)
op.create_index(op.f("ix_moderation_actions_id"), "moderation_actions", ["id"], unique=False)
op.create_index(
op.f("ix_moderation_actions_moderator_id"),
"moderation_actions",
["moderator_id"],
unique=False,
)
# ── appeals table ───────────────────────────────────────────
op.create_table(
"appeals",
sa.Column("id", sa.BigInteger(), primary_key=True, autoincrement=True),
sa.Column(
"moderation_action_id",
sa.Integer(),
sa.ForeignKey("moderation_actions.id", ondelete="CASCADE"),
nullable=False,
),
sa.Column(
"user_id",
sa.Integer(),
sa.ForeignKey("users.id", ondelete="CASCADE"),
nullable=False,
),
sa.Column("reason", sa.Text(), nullable=False),
sa.Column(
"status",
appeal_status_enum,
nullable=False,
server_default="pending",
),
sa.Column(
"reviewed_by",
sa.Integer(),
sa.ForeignKey("users.id", ondelete="SET NULL"),
nullable=True,
),
sa.Column("reviewed_at", sa.DateTime(timezone=True), nullable=True),
sa.Column(
"created_at",
sa.DateTime(timezone=True),
server_default=sa.func.now(),
nullable=False,
),
sa.UniqueConstraint("moderation_action_id", "user_id"),
)
op.create_index(op.f("ix_appeals_id"), "appeals", ["id"], unique=False)
op.create_index(op.f("ix_appeals_user_id"), "appeals", ["user_id"], unique=False)
def downgrade() -> None:
"""Remove trust & safety tables and columns."""
op.drop_index(op.f("ix_appeals_user_id"), table_name="appeals")
op.drop_index(op.f("ix_appeals_id"), table_name="appeals")
op.drop_table("appeals")
op.drop_index(op.f("ix_moderation_actions_moderator_id"), table_name="moderation_actions")
op.drop_index(op.f("ix_moderation_actions_id"), table_name="moderation_actions")
op.drop_table("moderation_actions")
op.drop_index(op.f("ix_reports_reporter_id"), table_name="reports")
op.drop_index(op.f("ix_reports_id"), table_name="reports")
op.drop_table("reports")
op.drop_column("users", "suspended_until")
op.drop_column("users", "banned_until")
op.drop_column("users", "role")
# Drop ENUM types
postgresql.ENUM(name="appeal_status").drop(op.get_bind(), checkfirst=True)
postgresql.ENUM(name="moderation_action").drop(op.get_bind(), checkfirst=True)
postgresql.ENUM(name="resolution").drop(op.get_bind(), checkfirst=True)
postgresql.ENUM(name="report_status").drop(op.get_bind(), checkfirst=True)
postgresql.ENUM(name="reason_category").drop(op.get_bind(), checkfirst=True)
postgresql.ENUM(name="target_type").drop(op.get_bind(), checkfirst=True)
postgresql.ENUM(name="role").drop(op.get_bind(), checkfirst=True)
+2 -2
View File
@@ -2,6 +2,6 @@ from app.models.user import User
from app.models.session import Session from app.models.session import Session
from app.models.world import World from app.models.world import World
from app.models.asset import Asset from app.models.asset import Asset
from app.models.group import Group, GroupMember, GroupMemberRole from app.models.moderation import Report, ModerationAction, Appeal
__all__ = ["User", "Session", "World", "Asset", "Group", "GroupMember", "GroupMemberRole"] __all__ = ["User", "Session", "World", "Asset", "Report", "ModerationAction", "Appeal"]
-56
View File
@@ -1,56 +0,0 @@
import enum
from sqlalchemy import Column, Integer, String, Text, DateTime, ForeignKey, Enum
from sqlalchemy.orm import relationship
from sqlalchemy.sql import func
from app.database import Base
class GroupMemberRole(str, enum.Enum):
OWNER = "owner"
ADMIN = "admin"
MEMBER = "member"
class Group(Base):
__tablename__ = "groups"
id = Column(Integer, primary_key=True, index=True)
name = Column(String(255), nullable=False)
description = Column(Text, nullable=True)
owner_id = Column(Integer, ForeignKey("users.id", ondelete="CASCADE"), nullable=False)
created_at = Column(DateTime(timezone=True), server_default=func.now())
updated_at = Column(DateTime(timezone=True), server_default=func.now(), onupdate=func.now())
owner = relationship("User", back_populates="owned_groups")
members = relationship(
"GroupMember",
back_populates="group",
cascade="all, delete-orphan",
order_by="GroupMember.joined_at",
)
class GroupMember(Base):
__tablename__ = "group_members"
group_id = Column(
Integer,
ForeignKey("groups.id", ondelete="CASCADE"),
primary_key=True,
)
user_id = Column(
Integer,
ForeignKey("users.id", ondelete="CASCADE"),
primary_key=True,
)
role = Column(
Enum(GroupMemberRole, name="group_member_role", create_type=False),
nullable=False,
default=GroupMemberRole.MEMBER,
)
joined_at = Column(DateTime(timezone=True), server_default=func.now())
group = relationship("Group", back_populates="members")
user = relationship("User", back_populates="group_memberships")
+225
View File
@@ -0,0 +1,225 @@
"""Trust & Safety models: reports, moderation actions, and appeals."""
from datetime import datetime
from typing import Optional
from sqlalchemy import (
CheckConstraint,
Column,
DateTime,
Enum,
ForeignKey,
Integer,
String,
Text,
UniqueConstraint,
func,
)
from sqlalchemy.dialects.postgresql import ENUM as PG_ENUM
from sqlalchemy.orm import relationship
from app.database import Base
# ---------------------------------------------------------------------------
# ENUM helpers — created explicitly in Alembic, not via SQLAlchemy create_type
# ---------------------------------------------------------------------------
_role_enum = PG_ENUM(
"user", "moderator", "admin",
name="user_role",
create_type=False,
)
_target_type_enum = PG_ENUM(
"user", "world", "asset", "chat_message",
name="target_type",
create_type=False,
)
_reason_category_enum = PG_ENUM(
"harassment", "hate_speech", "sexual_content", "violence",
"spam", "self_harm", "other",
name="reason_category",
create_type=False,
)
_report_status_enum = PG_ENUM(
"open", "in_review", "resolved", "dismissed",
name="report_status",
create_type=False,
)
_resolution_enum = PG_ENUM(
"action_taken", "action_not_needed", "duplicate", "escalated",
name="resolution",
create_type=False,
)
_moderation_action_enum = PG_ENUM(
"warn", "suspend", "ban", "restrict", "remove", "restore",
name="moderation_action",
create_type=False,
)
_appeal_status_enum = PG_ENUM(
"pending", "approved", "rejected",
name="appeal_status",
create_type=False,
)
class Report(Base):
"""A user-submitted report about another entity."""
__tablename__ = "reports"
id = Column(Integer, primary_key=True, autoincrement=True)
reporter_id = Column(
Integer,
ForeignKey("users.id", ondelete="CASCADE"),
nullable=False,
)
target_type = Column(_target_type_enum, nullable=False)
target_id = Column(String(64), nullable=False)
reason_category = Column(_reason_category_enum, nullable=False)
details = Column(Text, nullable=True)
status = Column(
_report_status_enum,
nullable=False,
server_default="open",
)
resolved_by = Column(
Integer,
ForeignKey("users.id", ondelete="SET NULL"),
nullable=True,
)
resolution = Column(_resolution_enum, nullable=True)
created_at = Column(
DateTime(timezone=True),
server_default=func.now(),
)
updated_at = Column(
DateTime(timezone=True),
onupdate=func.now(),
)
resolved_at = Column(DateTime(timezone=True), nullable=True)
# Coalesce duplicate reports from the same reporter about the same target
__table_args__ = (
UniqueConstraint("reporter_id", "target_type", "target_id",
name="uq_reporter_target"),
)
# Relationships
reporter = relationship("User", back_populates="reports",
foreign_keys=[reporter_id])
resolved_by_user = relationship("User", back_populates="resolved_reports",
foreign_keys=[resolved_by])
moderation_actions = relationship(
"ModerationAction",
back_populates="report",
cascade="all, delete-orphan",
)
appeals = relationship(
"Appeal",
back_populates="report",
cascade="all, delete-orphan",
)
class ModerationAction(Base):
"""A moderation action taken against a target."""
__tablename__ = "moderation_actions"
id = Column(Integer, primary_key=True, autoincrement=True)
moderator_id = Column(
Integer,
ForeignKey("users.id", ondelete="CASCADE"),
nullable=False,
)
target_user_id = Column(
Integer,
ForeignKey("users.id", ondelete="SET NULL"),
nullable=True,
)
target_type = Column(_target_type_enum, nullable=False)
target_id = Column(String(64), nullable=False)
action = Column(_moderation_action_enum, nullable=False)
reason = Column(Text, nullable=False)
report_id = Column(
Integer,
ForeignKey("reports.id", ondelete="SET NULL"),
nullable=True,
)
expires_at = Column(DateTime(timezone=True), nullable=True)
revoked_at = Column(DateTime(timezone=True), nullable=True)
created_at = Column(
DateTime(timezone=True),
server_default=func.now(),
)
# Relationships
moderator = relationship("User", back_populates="moderation_actions",
foreign_keys=[moderator_id])
target_user = relationship("User", back_populates="moderation_actions_target",
foreign_keys=[target_user_id])
report = relationship(
"Report",
back_populates="moderation_actions",
)
appeals = relationship(
"Appeal",
back_populates="moderation_action",
cascade="all, delete-orphan",
)
class Appeal(Base):
"""An appeal against a moderation action."""
__tablename__ = "appeals"
id = Column(Integer, primary_key=True, autoincrement=True)
moderation_action_id = Column(
Integer,
ForeignKey("moderation_actions.id", ondelete="CASCADE"),
nullable=False,
)
user_id = Column(
Integer,
ForeignKey("users.id", ondelete="CASCADE"),
nullable=False,
)
reason = Column(Text, nullable=False)
status = Column(
_appeal_status_enum,
nullable=False,
server_default="pending",
)
reviewed_by = Column(
Integer,
ForeignKey("users.id", ondelete="SET NULL"),
nullable=True,
)
reviewed_at = Column(DateTime(timezone=True), nullable=True)
created_at = Column(
DateTime(timezone=True),
server_default=func.now(),
)
# Prevent duplicate open appeals per sanction
__table_args__ = (
UniqueConstraint("moderation_action_id", "user_id",
name="uq_action_user_appeal"),
)
# Relationships
moderation_action = relationship(
"ModerationAction",
back_populates="appeals",
)
user = relationship("User", back_populates="appeals")
reviewed_by_user = relationship("User", back_populates="reviewed_appeals",
foreign_keys=[reviewed_by])
-2
View File
@@ -23,8 +23,6 @@ class User(Base):
sessions = relationship("Session", back_populates="user", cascade="all, delete-orphan") sessions = relationship("Session", back_populates="user", cascade="all, delete-orphan")
worlds = relationship("World", back_populates="owner") worlds = relationship("World", back_populates="owner")
assets = relationship("Asset", back_populates="owner") assets = relationship("Asset", back_populates="owner")
owned_groups = relationship("Group", back_populates="owner")
group_memberships = relationship("GroupMember", back_populates="user")
@property @property
def is_adult(self) -> bool: def is_adult(self) -> bool:
+2 -2
View File
@@ -8,7 +8,7 @@ services:
POSTGRES_PASSWORD: agp_password POSTGRES_PASSWORD: agp_password
POSTGRES_DB: agp_db POSTGRES_DB: agp_db
ports: ports:
- "${POSTGRES_PORT:-5432}:5432" - "5432:5432"
volumes: volumes:
- postgres_data:/var/lib/postgresql/data - postgres_data:/var/lib/postgresql/data
healthcheck: healthcheck:
@@ -20,7 +20,7 @@ services:
redis: redis:
image: redis:7-alpine image: redis:7-alpine
ports: ports:
- "${REDIS_PORT:-6379}:6379" - "6379:6379"
volumes: volumes:
- redis_data:/data - redis_data:/data
healthcheck: healthcheck: