WIP checkpoint: Plan: Discovery — Browse, Search & Home

2026-08-18 16:46:58 +00:00
8 changed files with 152 additions and 846 deletions
@@ -1,329 +0,0 @@
 """trust & safety: reports, moderation_actions, appeals + user role/ban/suspend
 Revision ID: 0003_trust_safety
 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_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:
    """Create trust & safety tables and extend users table."""
    bind = op.get_bind()
    # ── ENUM types ──────────────────────────────────────────────────────
    # user_role
    user_role = postgresql.ENUM("user", "moderator", "admin", name="user_role")
    user_role.create(bind, checkfirst=True)
    # report_target_type
    report_target_type = postgresql.ENUM(
        "user", "world", "asset", "chat_message", name="report_target_type"
    )
    report_target_type.create(bind, checkfirst=True)
    # reason_category
    reason_category = postgresql.ENUM(
        "harassment",
        "hate_speech",
        "sexual_content",
        "violence",
        "spam",
        "self_harm",
        "other",
        name="reason_category",
    )
    reason_category.create(bind, checkfirst=True)
    # report_status
    report_status = postgresql.ENUM(
        "open", "in_review", "resolved", "dismissed", name="report_status"
    )
    report_status.create(bind, checkfirst=True)
    # resolution
    resolution_enum = postgresql.ENUM(
        "action_taken", "no_action", "false_positive", name="resolution"
    )
    resolution_enum.create(bind, checkfirst=True)
    # moderation_action_type
    moderation_action_type = postgresql.ENUM(
        "warn", "suspend", "ban", "restrict", "remove", "restore",
        name="moderation_action_type",
    )
    moderation_action_type.create(bind, checkfirst=True)
    # moderation_target_type
    moderation_target_type = postgresql.ENUM(
        "user", "world", "asset", "chat_message", name="moderation_target_type"
    )
    moderation_target_type.create(bind, checkfirst=True)
    # appeal_status
    appeal_status = postgresql.ENUM(
        "pending", "approved", "rejected", name="appeal_status"
    )
    appeal_status.create(bind, checkfirst=True)
    # ── Extend users table ──────────────────────────────────────────────
    op.add_column(
        "users",
        sa.Column(
            "role",
            postgresql.ENUM("user", "moderator", "admin", name="user_role"),
            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(), nullable=False),
        sa.Column(
            "target_type",
            postgresql.ENUM(
                "user", "world", "asset", "chat_message", name="report_target_type"
            ),
            nullable=False,
        ),
        sa.Column("target_id", sa.String(length=64), nullable=False),
        sa.Column(
            "reason_category",
            postgresql.ENUM(
                "harassment",
                "hate_speech",
                "sexual_content",
                "violence",
                "spam",
                "self_harm",
                "other",
                name="reason_category",
            ),
            nullable=False,
        ),
        sa.Column(
            "details", sa.Text(), nullable=True,
        ),
        sa.Column(
            "status",
            postgresql.ENUM(
                "open", "in_review", "resolved", "dismissed", name="report_status"
            ),
            nullable=False,
            server_default="open",
        ),
        sa.Column("resolved_by", sa.Integer(), nullable=True),
        sa.Column(
            "resolution",
            postgresql.ENUM(
                "action_taken", "no_action", "false_positive", name="resolution"
            ),
            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(),
            nullable=False,
        ),
        sa.Column(
            "updated_at",
            sa.DateTime(timezone=True),
            onupdate=sa.func.now(),
            nullable=True,
        ),
        sa.ForeignKeyConstraint(["reporter_id"], ["users.id"], ondelete="CASCADE"),
        sa.ForeignKeyConstraint(["resolved_by"], ["users.id"], ondelete="SET NULL"),
        sa.UniqueConstraint(
            "reporter_id", "target_type", "target_id",
            name="uq_reporter_target",
        ),
    )
    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
    )
    op.create_index(
        op.f("ix_reports_status"), "reports", ["status"], 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(), nullable=False),
        sa.Column("target_user_id", sa.Integer(), nullable=True),
        sa.Column(
            "target_type",
            postgresql.ENUM(
                "user", "world", "asset", "chat_message",
                name="moderation_target_type",
            ),
            nullable=False,
        ),
        sa.Column("target_id", sa.String(length=64), nullable=True),
        sa.Column(
            "action",
            postgresql.ENUM(
                "warn", "suspend", "ban", "restrict", "remove", "restore",
                name="moderation_action_type",
            ),
            nullable=False,
        ),
        sa.Column("reason", sa.Text(), nullable=True),
        sa.Column("report_id", sa.Integer(), 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,
        ),
        sa.ForeignKeyConstraint(["moderator_id"], ["users.id"], ondelete="CASCADE"),
        sa.ForeignKeyConstraint(
            ["target_user_id"], ["users.id"], ondelete="CASCADE"
        ),
        sa.ForeignKeyConstraint(["report_id"], ["reports.id"], ondelete="SET NULL"),
    )
    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,
    )
    op.create_index(
        op.f("ix_moderation_actions_target_user_id"),
        "moderation_actions",
        ["target_user_id"],
        unique=False,
    )
    op.create_index(
        op.f("ix_moderation_actions_report_id"),
        "moderation_actions",
        ["report_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(), nullable=False
        ),
        sa.Column("user_id", sa.Integer(), nullable=False),
        sa.Column("reason", sa.Text(), nullable=False),
        sa.Column(
            "status",
            postgresql.ENUM(
                "pending", "approved", "rejected", name="appeal_status"
            ),
            nullable=False,
            server_default="pending",
        ),
        sa.Column("reviewed_by", sa.Integer(), 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.ForeignKeyConstraint(
            ["moderation_action_id"],
            ["moderation_actions.id"],
            ondelete="CASCADE",
        ),
        sa.ForeignKeyConstraint(["user_id"], ["users.id"], ondelete="CASCADE"),
        sa.ForeignKeyConstraint(
            ["reviewed_by"], ["users.id"], ondelete="SET NULL"
        ),
    )
    op.create_index(op.f("ix_appeals_id"), "appeals", ["id"], unique=False)
    op.create_index(
        op.f("ix_appeals_moderation_action_id"),
        "appeals",
        ["moderation_action_id"],
        unique=False,
    )
    op.create_index(
        op.f("ix_appeals_user_id"),
        "appeals",
        ["user_id"],
        unique=False,
    )
    op.create_index(
        op.f("ix_appeals_status"),
        "appeals",
        ["status"],
        unique=False,
    )
 def downgrade() -> None:
    """Remove trust & safety tables and revert users table."""
    op.drop_index(op.f("ix_appeals_status"), table_name="appeals")
    op.drop_index(op.f("ix_appeals_user_id"), table_name="appeals")
    op.drop_index(op.f("ix_appeals_moderation_action_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_report_id"), table_name="moderation_actions")
    op.drop_index(op.f("ix_moderation_actions_target_user_id"), table_name="moderation_actions")
    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_status"), table_name="reports")
    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(bind, checkfirst=True)
    postgresql.ENUM(name="moderation_target_type").drop(bind, checkfirst=True)
    postgresql.ENUM(name="moderation_action_type").drop(bind, checkfirst=True)
    postgresql.ENUM(name="resolution").drop(bind, checkfirst=True)
    postgresql.ENUM(name="report_status").drop(bind, checkfirst=True)
    postgresql.ENUM(name="reason_category").drop(bind, checkfirst=True)
    postgresql.ENUM(name="report_target_type").drop(bind, checkfirst=True)
    postgresql.ENUM(name="user_role").drop(bind, checkfirst=True)
@@ -2,8 +2,5 @@ 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.report import Report
 from app.models.moderation_action import ModerationAction
 from app.models.appeal import Appeal
-__all__ = ["User", "Session", "World", "Asset", "Report", "ModerationAction", "Appeal"]
+__all__ = ["User", "Session", "World", "Asset"]
@@ -1,47 +0,0 @@
 """Appeal model for Trust & Safety."""
 from datetime import datetime
 from typing import Optional
 from sqlalchemy import (
    Column,
    Integer,
    Text,
    DateTime,
    ForeignKey,
 )
 from sqlalchemy.dialects.postgresql import ENUM
 from sqlalchemy.orm import relationship
 from sqlalchemy.sql import func
 import enum
 from app.database import Base
 class AppealStatus(str, enum.Enum):
    PENDING = "pending"
    APPROVED = "approved"
    REJECTED = "rejected"
 class Appeal(Base):
    __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(
        ENUM(AppelStatus, name="appeal_status", create_type=False),
        default=AppealStatus.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(), nullable=False)
    # Relationships
    moderation_action = relationship("ModerationAction", back_populates="appeals")
    user = relationship("User", foreign_keys=[user_id])
    reviewer = relationship("User", foreign_keys=[reviewed_by])
@@ -1,63 +0,0 @@
 """ModerationAction model for Trust & Safety."""
 from datetime import datetime
 from typing import Optional
 from sqlalchemy import (
    Column,
    Integer,
    String,
    Text,
    DateTime,
    ForeignKey,
 )
 from sqlalchemy.dialects.postgresql import ENUM
 from sqlalchemy.orm import relationship
 from sqlalchemy.sql import func
 import enum
 from app.database import Base
 class ModerationActionType(str, enum.Enum):
    WARN = "warn"
    SUSPEND = "suspend"
    BAN = "ban"
    RESTRICT = "restrict"
    REMOVE = "remove"
    RESTORE = "restore"
 class ModerationTargetType(str, enum.Enum):
    USER = "user"
    WORLD = "world"
    ASSET = "asset"
    CHAT_MESSAGE = "chat_message"
 class ModerationAction(Base):
    __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="CASCADE"), nullable=True)
    target_type = Column(
        ENUM(ModerationTargetType, name="moderation_target_type", create_type=False),
        nullable=False,
    )
    target_id = Column(String(64), nullable=True)
    action = Column(
        ENUM(ModerationActionType, name="moderation_action_type", create_type=False),
        nullable=False,
    )
    reason = Column(Text, nullable=True)
    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(), nullable=False)
    # Relationships
    moderator = relationship("User", foreign_keys=[moderator_id])
    target_user = relationship("User", foreign_keys=[target_user_id])
    report = relationship("Report", foreign_keys=[report_id])
    appeals = relationship("Appeal", back_populates="moderation_action", cascade="all, delete-orphan")
@@ -1,88 +0,0 @@
 """Report model for Trust & Safety."""
 from datetime import datetime
 from typing import Optional
 from sqlalchemy import (
    Column,
    Integer,
    String,
    Text,
    DateTime,
    ForeignKey,
    UniqueConstraint,
 )
 from sqlalchemy.dialects.postgresql import ENUM
 from sqlalchemy.orm import relationship
 from sqlalchemy.sql import func
 import enum
 from app.database import Base
 class ReportTargetType(str, enum.Enum):
    USER = "user"
    WORLD = "world"
    ASSET = "asset"
    CHAT_MESSAGE = "chat_message"
 class ReasonCategory(str, enum.Enum):
    HARASSMENT = "harassment"
    HATE_SPEECH = "hate_speech"
    SEXUAL_CONTENT = "sexual_content"
    VIOLENCE = "violence"
    SPAM = "spam"
    SELF_HARM = "self_harm"
    OTHER = "other"
 class ReportStatus(str, enum.Enum):
    OPEN = "open"
    IN_REVIEW = "in_review"
    RESOLVED = "resolved"
    DISMISSED = "dismissed"
 class Resolution(str, enum.Enum):
    ACTION_TAKEN = "action_taken"
    NO_ACTION = "no_action"
    FALSE_POSITIVE = "false_positive"
 class Report(Base):
    __tablename__ = "reports"
    id = Column(Integer, primary_key=True, autoincrement=True)
    reporter_id = Column(Integer, ForeignKey("users.id"), nullable=False)
    target_type = Column(
        ENUM(ReportTargetType, name="report_target_type", create_type=False),
        nullable=False,
    )
    target_id = Column(String(64), nullable=False)
    reason_category = Column(
        ENUM(ReasonCategory, name="reason_category", create_type=False),
        nullable=False,
    )
    details = Column(Text, nullable=True)
    status = Column(
        ENUM(ReportStatus, name="report_status", create_type=False),
        default=ReportStatus.OPEN,
    )
    resolved_by = Column(Integer, ForeignKey("users.id"), nullable=True)
    resolution = Column(
        ENUM(Resolution, name="resolution", create_type=False),
        nullable=True,
    )
    resolved_at = Column(DateTime(timezone=True), nullable=True)
    created_at = Column(DateTime(timezone=True), server_default=func.now())
    updated_at = Column(DateTime(timezone=True), onupdate=func.now())
    # Relationships
    reporter = relationship("User", foreign_keys=[reporter_id])
    resolved_by_user = relationship("User", foreign_keys=[resolved_by])
    __table_args__ = (
        UniqueConstraint("reporter_id", "target_type", "target_id",
                         name="uq_reporter_target"),
    )
@@ -1,20 +1,13 @@
 from datetime import date
 from typing import Optional
-from sqlalchemy import Column, Integer, String, Boolean, DateTime, Date, Enum
+from sqlalchemy import Column, Integer, String, Boolean, DateTime, Date
 from sqlalchemy.orm import relationship
 from sqlalchemy.sql import func
 import enum
 from app.database import Base
 class UserRole(str, enum.Enum):
    USER = "user"
    MODERATOR = "moderator"
    ADMIN = "admin"
 class User(Base):
    __tablename__ = "users"
@@ -24,24 +17,12 @@ class User(Base):
    hashed_password = Column(String(255), nullable=False)
    date_of_birth = Column(Date, nullable=True)
    is_active = Column(Boolean, default=True)
    role = Column(
        Enum(UserRole, name="user_role"),
        nullable=False,
        server_default="user",
    )
    banned_until = Column(DateTime(timezone=True), nullable=True)
    suspended_until = Column(DateTime(timezone=True), nullable=True)
    created_at = Column(DateTime(timezone=True), server_default=func.now())
    updated_at = Column(DateTime(timezone=True), onupdate=func.now())
    sessions = relationship("Session", back_populates="user", cascade="all, delete-orphan")
    worlds = relationship("World", back_populates="owner")
    assets = relationship("Asset", back_populates="owner")
    reports_made = relationship("Report", foreign_keys="Report.reporter_id", back_populates="reporter")
    reports_resolved = relationship("Report", foreign_keys="Report.resolved_by", back_populates="resolved_by_user")
    moderation_actions_as_moderator = relationship("ModerationAction", foreign_keys="ModerationAction.moderator_id", back_populates="moderator")
    moderation_actions_as_target = relationship("ModerationAction", foreign_keys="ModerationAction.target_user_id", back_populates="target_user")
    appeals = relationship("Appeal", foreign_keys="Appeal.user_id", back_populates="user")
    @property
    def is_adult(self) -> bool:
@@ -0,0 +1,150 @@
 # Discovery — Browse, Search & Home — Plan
 > **Status:** Planned
 > **Owner:** Platform team
 > **Feature ID:** `discovery`
 > **Godot version:** 4.6.2 stable
 > **Backend:** FastAPI + SQLAlchemy (async) + PostgreSQL + Redis
 ## 1. Overview
 This document breaks the **Discovery** feature into user stories and
 right-sized implementation tasks. The feature delivers:
 - **Home / discover page** — a default listing of experiences a player lands on.
 - **Search** — find worlds by name and by tag.
 - **Categories** — a single category per world plus free-form tags, with
  filtering.
 - **Simple sorting** — newest, most-played, and trending. Trending starts as a
  basic **recent-activity heuristic** (weighted recent visits); no real
  recommendation model for MVP.
 Without this, nothing else built (experiences, creator content) is actually
 reachable by a real user. Discovery is the front door.
 ## 2. Why this matters
 The platform's value is *reachable* content. Worlds and creator assets already
 exist in the data model (`World`, `Asset`), but there is currently:
 - **No public listing** of worlds (`/api/worlds` requires auth and returns only
  PUBLIC worlds or your own worlds, ordered by `created_at` only).
 - **No search** by name or tag.
 - **No category/tag metadata** on worlds at all.
 - **No play-count or recency signal** to power "most-played" or "trending".
 - **No client UI** for browsing.
 Discovery closes that gap with a small, dependency-free metadata layer plus a
 read-optimized listing endpoint.
 ## 3. Existing constraints (do not re-build)
 - **Auth:** JWT bearer tokens; `get_current_user` / `CurrentUser` dependency in
  `app/dependencies.py`. Discovery **listing** must work both for anonymous
  visitors and authenticated users, so it needs an *optional* auth dependency
  (not `CurrentUser`, which returns 401).
 - **DB:** async SQLAlchemy + Alembic; migration head is `0002_add_date_of_birth_to_users`.
 - **Models:** `World` (id, name, description, owner_id, visibility, created_at,
  updated_at). `User`, `Asset` (owner_id, is_public) exist. No tags/categories/
  play-count fields yet.
 - **Routers:** `worlds.py` already owns CRUD under `/api/worlds`. Discovery adds
  a new read-focused router rather than overloading it.
 - **Redis:** configured (`REDIS_URL`) but unused; **not required** for discovery
  MVP (Postgres queries are sufficient at this scale).
 - **Client:** Godot 4.6.2, minimal `main_menu.gd`; no networking client, no
  discovery UI.
 ## 4. Key design decisions
 | Concern | Decision |
 |---|---|
 | Listing transport | REST (`GET /api/discovery/worlds`) — no WebSocket needed for browse |
 | Anonymous browsing | Listing/read endpoints accept **optional** auth; visibility filtering treats anonymous users as "PUBLIC only" |
 | Categories | Single `category` string column on `worlds` (indexed). Free-form for MVP; a distinct-categories list is derived with a `SELECT DISTINCT` |
 | Tags | PostgreSQL `ARRAY(String)` column on `worlds` with a GIN index for `@>` tag containment search |
 | Play count | `play_count` integer (default 0) on `worlds`, incremented atomically on visit |
 | Recency signal | `last_played_at` timestamp on `worlds` + append-only `world_visits` table for the trending heuristic |
 | Trending (MVP) | Weighted score over recent visits: `sum(decay_weight(age))` over the last N days, no ML |
 | Sorting | `newest` (created_at desc), `most_played` (play_count desc), `trending` (score desc) |
 | Pagination | `skip`/`limit` (limit capped at 100), consistent with existing routers |
 | Visibility | Discovery only surfaces `PUBLIC` worlds; never leaks private/friends_only worlds |
 ## 5. User stories
 ### US-1 Home / discover feed
 As a player, I can open a home/discover page and see a default list of
 experiences without searching.
 **Acceptance criteria**
 - Returns PUBLIC worlds only, newest-first by default.
 - Paginated with a stable total count.
 - Works anonymously and authenticated.
 - Each result includes id, name, description, category, tags, owner, play_count,
  and created_at.
 ### US-2 Search
 As a player, I can search experiences by name and by tag.
 **Acceptance criteria**
 - Case-insensitive substring search on `name`.
 - Tag search matches worlds containing *any* of the requested tags.
 - Search combines with category filter and sort.
 ### US-3 Categories & sorting
 As a player, I can filter by category and sort by newest, most-played, or
 trending.
 **Acceptance criteria**
 - `category` filter matches exactly (case-insensitive).
 - Distinct category list is available for UI dropdowns.
 - `newest`, `most_played`, and `trending` sorts are supported.
 - `most_played` uses `play_count`; `trending` uses the recent-visits heuristic.
 ### US-4 Visit tracking
 As a player, opening a world records a visit so counts and trending stay fresh.
 **Acceptance criteria**
 - `POST /api/worlds/{id}/visit` increments `play_count` and updates
  `last_played_at`.
 - Appends a `world_visits` row (world_id, user_id or anonymous marker,
  visited_at).
 - Idempotent per request; rate-limitable later.
 ### US-5 Godot client
 As a player, I can browse, search, filter, and sort experiences from the game UI.
 **Acceptance criteria**
 - An HTTP API client handles the discovery endpoints (JWT + anonymous).
 - A discover/home screen lists experiences with search, category filter, and
  sort controls.
 - Results render name, category, tags, play count, and description.
 ## 6. Task breakdown
 Right-sized to 1–3 files / one coherent piece of functionality each.
 | # | Task | Story | Depends on |
 |---|------|-------|-----------|
 | 1 | World discovery metadata + visits model & Alembic migration | US-2, US-3, US-4 | — |
 | 2 | World visit tracking endpoint (play count + last played + visit log) | US-4 | 1 |
 | 3 | Browse + search endpoint (name/tag/category, newest + most-played) | US-1, US-2, US-3 | 1, 2 |
 | 4 | Trending sort (recent-activity heuristic over `world_visits`) | US-3 | 3 |
 | 5 | Godot discovery API client (HTTP + optional JWT) | US-5 | 3 |
 | 6 | Godot discover/home UI (feed, search, category filter, sort) | US-5 | 4, 5 |
 ## 7. Phasing
 - **Phase 1 (data + core read API):** tasks 1–3 (metadata, visit tracking,
  browse/search).
 - **Phase 2 (trending):** task 4 — the only non-trivial query; isolated so it
  can be tuned without touching the browse surface.
 - **Phase 3 (client):** tasks 5–6, each landing after its backend dependency.
 ## 8. Out of scope for this feature
 - Real recommendation models / personalization (collaborative filtering, ML).
 - Full-text search (Postgres FTS); MVP uses ILIKE substring match.
 - Faceted search with multiple simultaneous category/tag AND-semantics.
 - Featured/editorial curation and admin-curated carousels.
 - Search result relevance scoring beyond the three explicit sorts.
 - Analytics dashboards; `world_visits` is append-only signal, not a BI surface.
@@ -1,295 +0,0 @@
 # Economy — Currency, Marketplace & Creator Payouts — Plan
 > **Status:** Planned
 > **Owner:** Platform team
 > **Feature ID:** `economy`
 > **Godot version:** 4.6.2 stable
 > **Backend:** FastAPI + SQLAlchemy (async) + PostgreSQL + Redis
 ## 1. Overview
 This document breaks the **Economy** feature into user stories and right-sized
 implementation tasks. The feature delivers:
 - **Virtual currency ("Credits")** — a single, server-authoritative currency
  with an immutable, append-only transaction ledger.
 - **Premium purchase** — buying Credits with real money through a pluggable
  payment-provider hook.
 - **Marketplace** — user-generated content (UGC) listings where creators sell
  items (assets, avatar items, experience passes) to players for Credits.
 - **Creator payouts** — accumulating creator earnings and paying them out to
  external accounts through a pluggable payout-provider hook.
 - **Godot client** — wallet/transaction history, marketplace browse/buy/sell,
  and creator payout screens.
 The economy is the monetization backbone of the platform. Because it moves
 virtual value between real people, correctness (no double-spend, no lost funds,
 no balance drift) and safety (fraud prevention, moderation, audit) are treated
 as **blocking requirements**, not add-ons.
 ## 2. Why correctness is the differentiator
 The platform is a creator economy (players buy UGC from other players). The
 economy is the highest-risk surface after chat: a single arithmetic or
 concurrency bug can mint or destroy value and irreversibly erode trust.
 Correctness is therefore designed in from the start:
 - **One currency, integer units.** Credits are whole-number integers. There are
  no floats, no rounding, and no client-computed totals.
 - **Append-only ledger.** Every balance change is a row in a `transactions`
  table with a signed amount, a `balance_after` snapshot, a reference to the
  event that caused it, and an **idempotency key** so retries cannot double
  credit or double debit.
 - **Atomic, locked mutations.** Balance changes run in a single database
  transaction with row locking (`SELECT ... FOR UPDATE`) so concurrent
  purchases/payouts cannot race.
 - **Escrow hold.** Marketplace proceeds are held in `pending_escrow` for a
  configurable window before becoming available to the seller, so fraudulent
  or moderated sales can be reversed without clawing back paid-out funds.
 - **Server-authoritative fees.** The platform commission is computed
  server-side from a configured rate; the client never supplies the fee or the
  seller's net.
 ## 3. Existing constraints (do not re-build)
 - **Auth:** JWT bearer tokens; `get_current_user` / `CurrentUser` dependency in
  `app/dependencies.py`. All economy endpoints reuse this.
 - **DB:** async SQLAlchemy + Alembic; migration `0001_initial` is the head.
  Existing tables: `users`, `sessions`, `worlds`, `assets`.
 - **Redis:** configured (`REDIS_URL`) but unused so far; adopted here for
  idempotency short-caching and rate limiting.
 - **Models:** `User` (owner_id), `Asset` (owner_id, is_public, asset_type).
  Marketplace items reference existing `assets` rows and/or avatar items.
 - **Content moderation:** `docs/architecture/content_moderation.md` defines
  flag vocabulary, age gate, and moderation actions. Marketplace items and
  listings must inherit and enforce these rules (see §7).
 - **Client:** Godot 4.6.2, minimal `main_menu.gd`; no economy UI yet.
 ## 4. Key design decisions
 | Concern | Decision |
 |---|---|
 | Currency model | Single virtual currency **"Credits" (CR)**, integer units. One wallet row per user; no fractional amounts. |
 | Ledger | Append-only `transactions` table: signed `amount`, `balance_after`, `reference_type`/`reference_id`, unique `idempotency_key`. |
 | Concurrency | `SELECT ... FOR UPDATE` on the wallet row inside the mutation transaction; never read-modify-write from stale data. |
 | Payments | Pluggable `PaymentProvider` interface with a **no-op default** that simulates a confirmed payment for dev/testing (clearly flagged). Stripe/PayPal later. |
 | Marketplace items | `catalog_items` (what is sold) + `marketplace_listings` (a seller's offer with a price/status). One item can have multiple listings over time. |
 | Purchase flow | Atomic transaction: debit buyer → hold funds in escrow (`pending_escrow`) → grant item. Platform fee computed server-side. |
 | Escrow | Configurable hold window (default 72h) before `pending_escrow` becomes available seller earnings. Refunds during the window reverse the sale. |
 | Fees | Single configurable `MARKETPLACE_FEE_RATE` (default `0.30`). `seller_net = floor(price * (1 - fee_rate))`. |
 | Creator balance | `creator_balances` table: `available` (earned, payable), `pending` (inside an open payout request), `lifetime_earned`. Updated in the same transaction as the sale completion. |
 | Payouts | Pluggable `PayoutProvider.pay()` with a no-op default (simulated external ref). Minimum payout threshold, admin review for large payouts. |
 | Idempotency | Every money-moving request carries a client-generated `Idempotency-Key`; duplicate keys return the original result. |
 | Rate limiting | Redis token-bucket dependency reused from the social feature for purchase/listing/payout endpoints. |
 | Moderation | Listings enforce content flags + age gate; moderators can `delist`/`remove`; fraud/chargeback reversal is an audited admin action. |
 ## 5. User stories
 ### US-1 Currency & wallet
 As a player, I can see my Credit balance and transaction history, and the system
 credits/debits my wallet only through server-authoritative events.
 **Acceptance criteria**
 - Exactly one wallet per user, created lazily on first use.
 - Balance is always a non-negative integer; no operation can make it negative.
 - Every mutation appends an immutable ledger row with amount, resulting balance,
  reference, and idempotency key.
 - Retrying a request with the same idempotency key does not double-apply.
 - `GET` endpoints expose current balance and paged transaction history.
 ### US-2 Premium purchase (buy Credits)
 As a player, I can purchase Credits with real money and have them credited only
 after the payment is confirmed.
 **Acceptance criteria**
 - A purchase session is created through the payment provider (or the no-op
  default) and is idempotent.
 - Credits are granted exactly once, only after a verified provider confirmation
  (webhook/confirm endpoint), never on client claim.
 - Purchase amounts are bounded and server-defined; clients cannot pick arbitrary
  prices.
 - Confirmation is idempotent and audited (provider reference recorded).
 ### US-3 Marketplace listing (sell)
 As a creator, I can list a catalog item for sale at a Credit price and
 manage/remove my listings.
 **Acceptance criteria**
 - A listing references a valid `catalog_item` owned by the seller (or an
  authorized seller).
 - Price is bounded (min/max) and set server-side; status transitions are
  explicit (`active`, `sold`, `delisted`, `removed_by_moderation`).
 - Only the seller (or a moderator) can update/remove a listing.
 - Listings are searchable/filterable (item type, price range, owner).
 ### US-4 Marketplace purchase (buy)
 As a player, I can buy a listed item. The purchase is atomic, holds funds in
 escrow, grants the item, and splits the payment between platform and seller.
 **Acceptance criteria**
 - A single transaction debits the buyer, holds funds in escrow, and grants the
  item — no partial states on failure.
 - Platform fee and seller net are computed server-side from the configured rate.
 - After the escrow hold window, funds move from escrow to the seller's
  available balance.
 - Refunds during the hold window reverse the sale and return funds to the buyer.
 - Concurrent purchases of a one-of-a-kind listing cannot both succeed.
 ### US-5 Creator payouts
 As a creator, I can view my earned balance, request a payout, and track its
 status; admins can review and approve/reject payouts.
 **Acceptance criteria**
 - Available balance is derived only from completed (escrow-released) sales and
  completed payouts; it never goes negative.
 - Payout requests are bounded (min threshold, max per period) and rate-limited.
 - Large payouts require admin review; all payout requests carry an audit trail
  (requested by, reviewed by, provider reference, status).
 - The payout provider is called exactly once; idempotent on retry.
 ### US-6 Godot client
 As a player, I can manage Credits, browse/buy/sell marketplace items, and manage
 payouts from the game UI.
 **Acceptance criteria**
 - An API client handles JWT auth, HTTP, and idempotency keys for all economy
  calls.
 - Dedicated screens: wallet + transaction history, marketplace browse/buy,
  sell-listing, and creator payout.
 - The client never computes balances, fees, or seller nets — it only displays
  server responses.
 ## 6. Task breakdown
 Right-sized to 1–3 files / one coherent piece of functionality each. Task IDs
 are sequential for dependency chaining.
 | # | Task | Story | Depends on |
 |---|------|-------|-----------|
 | 1 | Wallet & ledger models + Alembic migration (`wallets`, `transactions`) | US-1 | — |
 | 2 | Wallet service (atomic credit/debit, idempotency, locking) + wallet/transactions API | US-1 | 1 |
 | 3 | Premium purchase: `PaymentProvider` interface + purchase/confirm endpoints | US-2 | 1 |
 | 4 | Catalog item & listing models + migration (`catalog_items`, `marketplace_listings`, `creator_balances`) | US-3 | 1 |
 | 5 | Marketplace listing API (create/list/search/update/remove) | US-3 | 4 |
 | 6 | Marketplace purchase service + API (atomic escrow, fee split, item grant) | US-4 | 2, 4, 5 |
 | 7 | Creator payout request models + API (`payout_requests`) | US-5 | 2, 6 |
 | 8 | Payout admin review + `PayoutProvider` + audit | US-5 | 7 |
 | 9 | Marketplace moderation + content-flag/age-gate enforcement | cross-cutting | 5, 6 |
 | 10 | Godot economy API client (auth + HTTP + idempotency) | US-6 | 2 |
 | 11 | Godot wallet & transaction history UI | US-6 | 10 |
 | 12 | Godot marketplace browse/buy UI | US-6 | 6, 10 |
 | 13 | Godot selling & payout UI | US-6 | 5, 7, 10 |
 ## 7. Phasing
 - **Phase 1 (currency + payments):** tasks 1–3. The foundation — ledger and
  money movement must be rock-solid before anything spends Credits.
 - **Phase 2 (marketplace):** tasks 4–6. Listing and atomic purchase with escrow.
 - **Phase 3 (payouts):** tasks 7–8. Turn released escrow into creator payouts.
 - **Phase 4 (moderation):** task 9. Lock down the marketplace against mature
  content and abuse.
 - **Phase 5 (client):** tasks 10–13, each landing after its backend dependency.
 ## 8. Data model sketch
 ```text
 wallets
  id                bigint PK
  user_id           int UNIQUE FK -> users.id
  balance           bigint NOT NULL DEFAULT 0  CHECK (balance >= 0)
  created_at        timestamptz
  updated_at        timestamptz
 transactions        (append-only ledger)
  id                bigint PK
  wallet_id         bigint FK -> wallets.id
  type              enum(credit_purchase, marketplace_purchase,
                        marketplace_sale, escrow_release, payout,
                        refund, admin_adjustment)
  amount            bigint NOT NULL            (signed)
  balance_after     bigint NOT NULL
  reference_type    text                        (e.g. "purchase", "sale", "payout_request")
  reference_id      text
  idempotency_key   text UNIQUE
  metadata_json     jsonb
  created_at        timestamptz
 catalog_items
  id                bigint PK
  owner_id          int FK -> users.id
  asset_id          int FK -> assets.id (nullable; null for non-asset virtual items)
  item_type         enum(asset, avatar_item, experience_pass)
  name              text
  description       text
  metadata_json     jsonb
  created_at        timestamptz
 marketplace_listings
  id                bigint PK
  catalog_item_id   bigint FK -> catalog_items.id
  seller_id         int FK -> users.id
  price             bigint NOT NULL CHECK (price > 0)
  status            enum(active, sold, delisted, removed_by_moderation)
  created_at        timestamptz
  updated_at        timestamptz
 purchases
  id                bigint PK
  listing_id        bigint FK -> marketplace_listings.id
  buyer_id          int FK -> users.id
  seller_id         int FK -> users.id
  price             bigint NOT NULL
  platform_fee      bigint NOT NULL
  seller_net        bigint NOT NULL
  status            enum(pending_escrow, completed, refunded, reversed)
  escrow_hold_until timestamptz
  created_at        timestamptz
 creator_balances
  id                bigint PK
  user_id           int UNIQUE FK -> users.id
  available         bigint NOT NULL DEFAULT 0
  pending           bigint NOT NULL DEFAULT 0
  lifetime_earned   bigint NOT NULL DEFAULT 0
  updated_at        timestamptz
 payout_requests
  id                bigint PK
  creator_id        int FK -> users.id
  amount            bigint NOT NULL CHECK (amount > 0)
  status            enum(pending, approved, paid, rejected)
  external_ref      text
  provider_ref      text
  requested_by      int FK -> users.id
  reviewed_by       int FK -> users.id (nullable)
  reviewed_at       timestamptz
  created_at        timestamptz
 ```
 ## 9. Security, anti-fraud & audit
 - **Server-authoritative balances/fees:** clients never compute totals; amounts
  are validated server-side against bounds and the current listing.
 - **Row locking:** wallet and creator-balance mutations use `SELECT ... FOR
  UPDATE` within one transaction.
 - **Idempotency:** every money-moving endpoint requires an `Idempotency-Key`;
  duplicate keys return the stored original outcome.
 - **Rate limiting:** purchase, listing, and payout endpoints are token-bucket
  rate-limited per user.
 - **Escrow & reversibility:** funds are not payable until the hold window
  elapses; chargebacks/refunds reverse before release.
 - **Moderation:** listings carry the item's effective content flags; mature
  items require age verification (per `content_moderation.md`); moderators can
  `delist`/`remove` and trigger audited reversals.
 - **Audit:** the ledger plus `payout_requests` review fields form an append-only
  audit trail for every movement of value.
 ## 10. Out of scope for this feature
 - Real Stripe/PayPal/KYC integrations (provider interfaces leave the hooks; the
  defaults are deterministic no-ops).
 - Subscriptions, recurring billing, or premium memberships.
 - Gift cards, promotional codes, or user-to-user Credit transfers.
 - Partial refunds, chargeback disputes, and seller tax/VAT handling.
 - In-world vending machines / scripted economy triggers (future, built on this
  ledger via the scripting sandbox).