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

2026-08-18 16:46:58 +00:00
7 changed files with 154 additions and 835 deletions
@@ -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, moderation  # noqa: F401
+from app.models import user, session, world, asset  # noqa: F401
 # this is the Alembic Config object, which provides
 # access to the values within the .ini file in use.
@@ -1,310 +0,0 @@
 """add moderation schema
 Revision ID: 0003_add_moderation_schema
 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_moderation_schema"
 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 moderation tables and extend users table."""
    # ── ENUM types ──────────────────────────────────────────────
    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(
        "upheld", "modified", "dismissed",
        name="resolution",
    )
    resolution_enum.create(op.get_bind(), checkfirst=True)
    moderation_action_type_enum = postgresql.ENUM(
        "warn", "suspend", "ban", "restrict", "remove", "restore",
        name="moderation_action_type",
    )
    moderation_action_type_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)
    user_role_enum = postgresql.ENUM(
        "user", "moderator", "admin",
        name="user_role",
    )
    user_role_enum.create(op.get_bind(), checkfirst=True)
    # ── users table extensions ──────────────────────────────────
    op.add_column(
        "users",
        sa.Column(
            "role",
            postgresql.ENUM(
                "user", "moderator", "admin", name="user_role",
                create_type=False,
            ),
            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.Integer(), primary_key=True),
        sa.Column("reporter_id", sa.Integer(), nullable=False),
        sa.Column(
            "target_type",
            postgresql.ENUM(
                "user", "world", "asset", "chat_message",
                name="target_type", create_type=False,
            ),
            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", create_type=False,
            ),
            nullable=False,
        ),
        sa.Column("details", sa.Text(), nullable=True),
        sa.Column(
            "status",
            postgresql.ENUM(
                "open", "in_review", "resolved", "dismissed",
                name="report_status", create_type=False,
            ),
            nullable=False,
            server_default="open",
        ),
        sa.Column("resolved_by", sa.Integer(), nullable=True),
        sa.Column(
            "resolution",
            postgresql.ENUM(
                "upheld", "modified", "dismissed",
                name="resolution", create_type=False,
            ),
            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),
            server_default=sa.func.now(),
            nullable=False,
        ),
        sa.Column("resolved_at", sa.DateTime(timezone=True), nullable=True),
        sa.ForeignKeyConstraint(
            ["reporter_id"], ["users.id"], ondelete="CASCADE",
        ),
        sa.ForeignKeyConstraint(
            ["resolved_by"], ["users.id"], ondelete="SET NULL",
        ),
    )
    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,
    )
    op.create_index(
        op.f("ix_reports_target"), "reports", ["target_type", "target_id"], unique=False,
    )
    # ── moderation_actions table ────────────────────────────────
    op.create_table(
        "moderation_actions",
        sa.Column("id", sa.Integer(), primary_key=True),
        sa.Column("moderator_id", sa.Integer(), nullable=False),
        sa.Column("target_user_id", sa.Integer(), nullable=False),
        sa.Column(
            "target_type",
            postgresql.ENUM(
                "user", "world", "asset", "chat_message",
                name="target_type", create_type=False,
            ),
            nullable=False,
        ),
        sa.Column("target_id", sa.String(length=64), nullable=False),
        sa.Column(
            "action",
            postgresql.ENUM(
                "warn", "suspend", "ban", "restrict", "remove", "restore",
                name="moderation_action_type", create_type=False,
            ),
            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="SET NULL",
        ),
        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_target"),
        "moderation_actions", ["target_type", "target_id"], unique=False,
    )
    # ── appeals table ───────────────────────────────────────────
    op.create_table(
        "appeals",
        sa.Column("id", sa.Integer(), primary_key=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", create_type=False,
            ),
            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:
    """Drop moderation 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_target"), 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_target"), table_name="reports")
    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(op.get_bind(), checkfirst=True)
    postgresql.ENUM(name="moderation_action_type").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="user_role").drop(op.get_bind(), checkfirst=True)
@@ -2,30 +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.moderation import (
    Report,
    ModerationAction,
    Appeal,
    TargetType,
    ReasonCategory,
    ReportStatus,
    Resolution,
    ModerationActionType,
    AppealStatus,
 )
-__all__ = [
+__all__ = ["User", "Session", "World", "Asset"]
    "User",
    "Session",
    "World",
    "Asset",
    "Report",
    "ModerationAction",
    "Appeal",
    "TargetType",
    "ReasonCategory",
    "ReportStatus",
    "Resolution",
    "ModerationActionType",
    "AppealStatus",
 ]
@@ -1,186 +0,0 @@
 """Moderation data models for Trust & Safety."""
 import enum
 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
 from app.database import Base
 class TargetType(str, enum.Enum):
    """Type of entity being reported or moderated."""
    USER = "user"
    WORLD = "world"
    ASSET = "asset"
    CHAT_MESSAGE = "chat_message"
 class ReasonCategory(str, enum.Enum):
    """Category of reason for a report."""
    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):
    """Status of a report."""
    OPEN = "open"
    IN_REVIEW = "in_review"
    RESOLVED = "resolved"
    DISMISSED = "dismissed"
 class Resolution(str, enum.Enum):
    """Resolution outcome of a report."""
    UPHELD = "upheld"
    MODIFIED = "modified"
    DISMISSED = "dismissed"
 class ModerationActionType(str, enum.Enum):
    """Type of moderation action taken."""
    WARN = "warn"
    SUSPEND = "suspend"
    BAN = "ban"
    RESTRICT = "restrict"
    REMOVE = "remove"
    RESTORE = "restore"
 class AppealStatus(str, enum.Enum):
    """Status of an appeal."""
    PENDING = "pending"
    APPROVED = "approved"
    REJECTED = "rejected"
 class Report(Base):
    """A report filed by a user against a target entity."""
    __tablename__ = "reports"
    id = Column(Integer, primary_key=True, index=True)
    reporter_id = Column(
        Integer, ForeignKey("users.id", ondelete="CASCADE"), nullable=False
    )
    target_type = Column(
        ENUM(TargetType, name="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,
        nullable=False,
    )
    resolved_by = Column(
        Integer, ForeignKey("users.id", ondelete="SET NULL"), nullable=True
    )
    resolution = Column(
        ENUM(Resolution, name="resolution", create_type=False),
        nullable=True,
    )
    created_at = Column(
        DateTime(timezone=True), server_default=func.now(), nullable=False
    )
    updated_at = Column(
        DateTime(timezone=True),
        server_default=func.now(),
        onupdate=func.now(),
        nullable=False,
    )
    resolved_at = Column(DateTime(timezone=True), nullable=True)
    # Relationships
    reporter = relationship("User", foreign_keys=[reporter_id])
    resolver = relationship("User", 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 by a moderator against a user or entity."""
    __tablename__ = "moderation_actions"
    id = Column(Integer, primary_key=True, index=True)
    moderator_id = Column(
        Integer, ForeignKey("users.id", ondelete="SET NULL"), nullable=False
    )
    target_user_id = Column(
        Integer, ForeignKey("users.id", ondelete="CASCADE"), nullable=False
    )
    target_type = Column(
        ENUM(TargetType, name="target_type", create_type=False),
        nullable=False,
    )
    target_id = Column(String(64), nullable=False)
    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", back_populates="moderation_actions")
    appeals = relationship(
        "Appeal", back_populates="moderation_action", cascade="all, delete-orphan"
    )
 class Appeal(Base):
    """An appeal filed by a user against a moderation action."""
    __tablename__ = "appeals"
    id = Column(Integer, primary_key=True, index=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(AppealStatus, name="appeal_status", create_type=False),
        default=AppealStatus.PENDING,
        nullable=False,
    )
    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,21 +1,13 @@
 from datetime import date
 from typing import Optional
-from sqlalchemy import Column, Integer, String, Boolean, DateTime, Date, func
+from sqlalchemy import Column, Integer, String, Boolean, DateTime, Date
 from sqlalchemy.dialects.postgresql import ENUM
 from sqlalchemy.orm import relationship
-import enum
+from sqlalchemy.sql import func
 from app.database import Base
 class UserRole(str, enum.Enum):
    """User role for moderation and access control."""
    USER = "user"
    MODERATOR = "moderator"
    ADMIN = "admin"
 class User(Base):
    __tablename__ = "users"
@@ -25,13 +17,6 @@ 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", create_type=False),
        default=UserRole.USER,
        nullable=False,
    )
    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())
@@ -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).