WIP checkpoint: World discovery metadata + visits model & Alembic migration

backend/alembic/env.py

@@ -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  # noqa: F401
+from app.models import user, session, world, asset, world_visit  # noqa: F401
 
 # this is the Alembic Config object, which provides
 # access to the values within the .ini file in use.

backend/alembic/versions/0001_initial.py

@@ -69,8 +69,7 @@ def upgrade() -> None:
         sa.Column(
             "visibility",
             postgresql.ENUM(
-                "public", "private", "friends_only", name="world_visibility",
-                create_type=False,
+                "public", "private", "friends_only", name="world_visibility"
             ),
             nullable=True,
         ),
@@ -90,7 +89,7 @@ def upgrade() -> None:
             "asset_type",
             postgresql.ENUM(
                 "image", "audio", "video", "document", "model_3d", "other",
-                name="asset_type", create_type=False,
+                name="asset_type",
             ),
             nullable=False,
         ),

backend/alembic/versions/0003_add_discovery_metadata.py

@@ -0,0 +1,104 @@
+"""add discovery metadata to worlds and create world_visits
+
+Revision ID: 0003_add_discovery_metadata
+Revises: 0002_add_date_of_birth_to_users
+Create Date: 2025-01-02 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_discovery_metadata"
+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 discovery metadata columns to worlds and create world_visits."""
+    # Add discovery metadata columns to worlds.
+    op.add_column(
+        "worlds",
+        sa.Column("category", sa.String(length=100), nullable=True),
+    )
+    op.add_column(
+        "worlds",
+        sa.Column(
+            "tags",
+            postgresql.ARRAY(sa.String()),
+            nullable=True,
+            server_default="{}",
+        ),
+    )
+    op.add_column(
+        "worlds",
+        sa.Column("play_count", sa.Integer(), nullable=False, server_default="0"),
+    )
+    op.add_column(
+        "worlds",
+        sa.Column("last_played_at", sa.DateTime(timezone=True), nullable=True),
+    )
+
+    # B-tree index on category, GIN index on tags.
+    op.create_index(
+        "ix_worlds_category",
+        "worlds",
+        ["category"],
+        unique=False,
+    )
+    op.create_index(
+        "ix_worlds_tags",
+        "worlds",
+        ["tags"],
+        unique=False,
+        postgresql_using="gin",
+    )
+
+    # Create world_visits table.
+    op.create_table(
+        "world_visits",
+        sa.Column("id", sa.Integer(), primary_key=True),
+        sa.Column("world_id", sa.Integer(), nullable=False),
+        sa.Column("user_id", sa.Integer(), nullable=True),
+        sa.Column("anonymous_token", sa.String(length=255), nullable=True),
+        sa.Column(
+            "visited_at",
+            sa.DateTime(timezone=True),
+            server_default=sa.func.now(),
+        ),
+        sa.ForeignKeyConstraint(
+            ["world_id"], ["worlds.id"], ondelete="CASCADE"
+        ),
+        sa.ForeignKeyConstraint(
+            ["user_id"], ["users.id"], ondelete="CASCADE"
+        ),
+    )
+    op.create_index(
+        op.f("ix_world_visits_id"), "world_visits", ["id"], unique=False
+    )
+    op.create_index(
+        "ix_world_visits_world_id_visited_at",
+        "world_visits",
+        ["world_id", "visited_at"],
+        unique=False,
+    )
+
+
+def downgrade() -> None:
+    """Drop world_visits and discovery metadata columns from worlds."""
+    op.drop_index(
+        "ix_world_visits_world_id_visited_at", table_name="world_visits"
+    )
+    op.drop_index(op.f("ix_world_visits_id"), table_name="world_visits")
+    op.drop_table("world_visits")
+
+    op.drop_index("ix_worlds_tags", table_name="worlds")
+    op.drop_index("ix_worlds_category", table_name="worlds")
+    op.drop_column("worlds", "last_played_at")
+    op.drop_column("worlds", "play_count")
+    op.drop_column("worlds", "tags")
+    op.drop_column("worlds", "category")

backend/app/models/__init__.py

@@ -2,5 +2,6 @@ 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.world_visit import WorldVisit
 
-__all__ = ["User", "Session", "World", "Asset"]
+__all__ = ["User", "Session", "World", "Asset", "WorldVisit"]

backend/app/models/world.py

@@ -1,4 +1,4 @@
-from sqlalchemy import Column, Integer, String, Text, DateTime, ForeignKey
+from sqlalchemy import Column, Integer, String, Text, DateTime, ForeignKey, ARRAY, Index
 from sqlalchemy.dialects.postgresql import ENUM
 from sqlalchemy.orm import relationship
 from sqlalchemy.sql import func
@@ -27,5 +27,17 @@ class World(Base):
     created_at = Column(DateTime(timezone=True), server_default=func.now())
     updated_at = Column(DateTime(timezone=True), onupdate=func.now())
 
+    # Discovery metadata
+    category = Column(String(100), nullable=True, index=True)
+    tags = Column(ARRAY(String), nullable=True, server_default="{}")
+    play_count = Column(Integer, nullable=False, server_default="0")
+    last_played_at = Column(DateTime(timezone=True), nullable=True)
+
+    # GIN index on tags for efficient tag-based queries
+    __table_args__ = (
+        Index("ix_worlds_tags", "tags", postgresql_using="gin"),
+    )
+
     owner = relationship("User", back_populates="worlds")
     assets = relationship("Asset", back_populates="world", cascade="all, delete-orphan")
+    visits = relationship("WorldVisit", back_populates="world", cascade="all, delete-orphan")

backend/app/models/world_visit.py

@@ -0,0 +1,26 @@
+from sqlalchemy import Column, DateTime, ForeignKey, Index, Integer, String
+from sqlalchemy.orm import relationship
+from sqlalchemy.sql import func
+
+from app.database import Base
+
+
+class WorldVisit(Base):
+    __tablename__ = "world_visits"
+
+    id = Column(Integer, primary_key=True, index=True)
+    world_id = Column(
+        Integer, ForeignKey("worlds.id", ondelete="CASCADE"), nullable=False
+    )
+    user_id = Column(
+        Integer, ForeignKey("users.id", ondelete="CASCADE"), nullable=True
+    )
+    anonymous_token = Column(String(255), nullable=True)
+    visited_at = Column(DateTime(timezone=True), server_default=func.now())
+
+    __table_args__ = (
+        Index("ix_world_visits_world_id_visited_at", "world_id", "visited_at"),
+    )
+
+    world = relationship("World", back_populates="visits")
+    user = relationship("User")

backend/docker-compose.yml

@@ -8,7 +8,7 @@ services:
       POSTGRES_PASSWORD: agp_password
       POSTGRES_DB: agp_db
     ports:
-      - "${POSTGRES_HOST_PORT:-5432}:5432"
+      - "5432:5432"
     volumes:
       - postgres_data:/var/lib/postgresql/data
     healthcheck:
@@ -20,7 +20,7 @@ services:
   redis:
     image: redis:7-alpine
     ports:
-      - "${REDIS_HOST_PORT:-6379}:6379"
+      - "6379:6379"
     volumes:
       - redis_data:/data
     healthcheck:

docs/planning/client-server-wiring.md

@@ -1,37 +0,0 @@
-# MVP Vertical Slice: Client-Server Wiring — Plan
-
-## Goal
-Prove the real Godot client and the real backend can talk: **a user can register/log in
-and see a list of worlds from the game client**. Nothing more. Gameplay, join-a-world,
-chat, avatar, Discovery search/tags/trending, Social/Economy/Trust & Safety are all
-explicitly out of scope.
-
-## Verified backend contract (as of this writing)
-- `POST /api/auth/register` — JSON body `{username, email, password, date_of_birth}`.
-  `date_of_birth` is a `YYYY-MM-DD` string. Returns `201 {access_token, token_type}`.
-  Rejects under-18 with `400`.
-- `POST /api/auth/login` — OAuth2 **form-encoded** (`application/x-www-form-urlencoded`)
-  `username` + `password` (this is NOT a JSON endpoint). Returns `{access_token, token_type}`.
-- `GET /api/worlds` — **requires auth** (`CurrentUser`), returns a JSON array of
-  `{id, name, description, visibility, owner_id, created_at}`. There is no unauthenticated
-  public-worlds path, so the worlds list screen can only be shown after login/register.
-- `GET /api/health` — unauthenticated, `{status, app}`.
-- Base URL: uvicorn default `http://127.0.0.1:8000` (must be configurable in the client).
-- CORS is irrelevant to the native Godot `HTTPRequest` client (no browser origin).
-
-## Task breakdown
-| # | Type | Task | Depends on |
-|---|------|------|-----------|
-| 1 | architecture | `ApiClient` autoload: HTTP + JWT storage + error contract | — |
-| 2 | implementation | Main-menu **login** wiring (form-encoded) | 1 |
-| 3 | implementation | Main-menu **register** wiring (JSON + age gate) | 2 |
-| 4 | implementation | **World list** screen consuming `GET /api/worlds` | 2 |
-| 5 | testing | End-to-end headless smoke test + manual verify | 1,3,4 |
-
-## Design decisions captured (resolved in task 1)
-- Token storage: in-memory singleton property for the MVP; a `save_to_disk` seam is left
-  open but not required yet (no re-login on restart in this slice).
-- Error contract: every call returns a discriminated result (`ok`/`error`) so callers
-  never crash on network failure; non-2xx surfaces `{status, detail}` from the backend.
-- Base URL: read from a ProjectSetting, defaulting to `http://127.0.0.1:8000`.
-- Login is form-encoded; register is JSON — the client helper must support both content types.

docs/planning/creator_studio.md

@@ -1,182 +0,0 @@
-# Creator Studio — Extended Authoring Tools
-
-> **Feature:** Creator Studio — Extended Authoring Tools
-> **Status:** Planning
-> **Owner:** Platform team
-
-## Feature Overview
-
-Extends the in-game world editor (M6) toward a real authoring tool with:
-1. **Asset import pipeline** (glTF models/textures)
-2. **Script editor** with sandboxed API surface exposed clearly
-3. **Experience publishing flow** (draft → review → live)
-4. **Basic analytics** for creators (plays, session length)
-
-## User Stories
-
-### US-1: Asset Import Pipeline
-_As a creator, I can import glTF models and textures into my experience so that I can use custom 3D assets._
-
-### US-2: Script Editor with Sandboxed API
-_As a creator, I can write and test scripts in the editor with autocomplete/docs for the available API surface._
-
-### US-3: Experience Publishing Flow
-_As a creator, I can publish my experience through a draft → review → live pipeline so that players can discover and play it._
-
-### US-4: Creator Analytics
-_As a creator, I can see how many plays and average session length my experiences receive so that I can understand player engagement._
-
----
-
-## Task Breakdown
-
-### US-1: Asset Import Pipeline
-
-#### T1.1: Backend — glTF Asset Upload with Metadata Extraction
-- **Type:** implementation
-- **Files:** `backend/app/routers/assets.py` (extend upload endpoint), `backend/app/models/asset.py` (add glTF-specific metadata fields)
-- **Scope:** Extend the existing `upload_asset_file` endpoint in `assets.py` to detect `.glb`/`.gltf` files and extract glTF metadata (mesh_count, vertex_count, has_animation, texture_refs, bounding_box) from the embedded JSON. Store extracted metadata in `metadata_json`. Add a `has_gltf_metadata` computed property. The Asset model already has `asset_type = MODEL_3D` and `metadata_json` — this task adds the extraction logic.
-- **Acceptance:** POST `/api/assets/{id}/upload` with a `.glb` file returns asset with `metadata_json` containing `mesh_count`, `vertex_count`, `has_animation`, `texture_refs`. GET lists include these fields. File stored on disk. Non-glTF files are unaffected.
-- **Dependencies:** None (builds on existing asset infrastructure)
-
-#### T1.2: Backend — Public Asset Catalog Endpoint
-- **Type:** implementation
-- **Files:** `backend/app/routers/assets.py` (new endpoint)
-- **Scope:** Add `GET /api/assets/catalog` endpoint that returns all public glTF assets with their metadata (name, thumbnail_url, mesh_count, vertex_count, tags). Add filtering by tags and pagination. This is what the editor's asset palette queries. Uses the existing `Asset.is_public` field.
-- **Acceptance:** Catalog endpoint returns paginated list of public assets; filtering by tag works; pagination params respected; returns 401 without auth.
-- **Dependencies:** T1.1
-
-#### T1.3: Godot — glTF Import Utility (Editor-Side)
-- **Type:** implementation
-- **Files:** `scripts/gltf_importer.gd` (new), `scripts/asset_catalog.gd` (new)
-- **Scope:** Godot-side utility that connects to the catalog API, downloads glTF files, and converts them to Godot-compatible resources (`.res` files with embedded mesh data). Provides `import_gltf(url: String) -> Resource` function. Handles texture extraction and mesh merging. Uses the ApiClient autoload (from client-server-wiring plan) for HTTP.
-- **Acceptance:** `import_gltf()` downloads a glTF from the catalog, converts mesh to `Mesh` resource, returns a ready-to-use Godot resource. Texture files stored in `user://assets/`.
-- **Dependencies:** T1.2, client-server-wiring ApiClient (task 1 from that plan)
-
-### US-2: Script Editor with Sandboxed API
-
-> **Design note from `scripting_sandbox.md`:** Godot 4 has no built-in GDScript sandbox. The recommended approach is a **data-driven DSL** (JSON/Resource) interpreted by trusted GDScript, not arbitrary code execution. This task follows that recommendation.
-
-#### T2.1: Backend — Script Model & CRUD Endpoints
-- **Type:** architecture
-- **Files:** `backend/app/models/script.py` (new), `backend/app/routers/scripts.py` (new), `backend/app/main.py` (register router), `backend/app/models/__init__.py` (export)
-- **Scope:** Create `Script` model (id, name, world_id, owner_id, source_code TEXT, version INT, script_type ENUM[gdscript, dsl], dsl_payload JSON, created_at, updated_at). Add full CRUD endpoints: `POST /api/scripts/` (create), `GET /api/scripts/` (list), `GET /api/scripts/{id}` (get), `PUT /api/scripts/{id}` (update), `DELETE /api/scripts/{id}` (delete). Scripts are owned by users and associated with a world. `dsl_payload` stores the JSON/Resource data for the DSL approach; `source_code` stores raw GDScript if used.
-- **Acceptance:** All CRUD endpoints work; scripts are owned by creator; list endpoint filters by owner/world; update increments version; DSL and gdscript types supported.
-- **Dependencies:** None (new model + router)
-
-#### T2.2: Godot — Script API Surface Definition
-- **Type:** architecture
-- **Files:** `scripts/editor_script_api.gd` (new)
-- **Scope:** Define the public API surface available to creator scripts. This is a `RefCounted` class that wraps the Godot engine's capabilities into a safe, documented interface. Includes: `WorldAPI` (get_tile, get_object, set_property), `PlayerAPI` (get_position, get_health), `AudioAPI` (play_sound, play_music), `NetworkAPI` (send_event, broadcast). Each method has documented parameters and return types. Self-documenting via `##` doc comments for autocomplete.
-- **Acceptance:** `EditorScriptAPI` class exposes all documented methods; methods are callable from user scripts; API surface is self-documenting.
-- **Dependencies:** None (standalone definition)
-
-#### T2.3: Godot — In-Game Script Editor UI
-- **Type:** implementation
-- **Files:** `scenes/editor_script_editor.tscn` (new), `scripts/editor_script_editor.gd` (new)
-- **Scope:** A `Control`-based UI panel that provides a script editor within the game. Features: code editor with syntax highlighting (basic), autocomplete dropdown, run button (calls backend `/run` endpoint), output console (shows stdout/stderr), API reference panel (shows available methods). Integrates with the existing editor toolbar.
-- **Acceptance:** Editor panel opens from toolbar; code editor accepts input; autocomplete shows API methods; run button executes script and shows output; API reference panel is browsable.
-- **Dependencies:** T2.1, T2.2
-
-### US-3: Experience Publishing Flow
-
-#### T3.1: Backend — Experience Model & Status Transitions
-- **Type:** architecture
-- **Files:** `backend/app/models/experience.py` (new), `backend/app/routers/experiences.py` (new), `backend/app/main.py` (register router), `backend/app/models/__init__.py` (export)
-- **Scope:** Create `Experience` model (id, world_id FK→worlds, title, description, status ENUM[DRAFT,SUBMITTED,IN_REVIEW,APPROVED,REJECTED,LIVE], published_at, review_notes, reviewer_id FK→users). Status transitions enforced: DRAFT→SUBMITTED→IN_REVIEW→APPROVED→LIVE; REJECTED→DRAFT (resubmit). Add endpoints: `POST /api/experiences/` (create draft), `POST /api/experiences/{id}/submit` (submit for review), `POST /api/experiences/{id}/approve` (approve), `POST /api/experiences/{id}/reject` (reject), `GET /api/experiences/` (list with status filter). An experience is a **publishing wrapper** around a World — the world's visibility is set to PUBLIC when experience goes LIVE.
-- **Acceptance:** All CRUD + transition endpoints work; status transitions enforced; rejected can be resubmitted; list filters by status; approve sets world visibility to PUBLIC; only world owner can submit; only moderators can approve/reject.
-- **Dependencies:** None (new model + router)
-
-#### T3.2: Backend — Review Queue & Moderation Integration
-- **Type:** implementation
-- **Files:** `backend/app/routers/experiences.py` (add endpoints)
-- **Scope:** Add review queue endpoint `GET /api/experiences/review-queue` (returns experiences in IN_REVIEW status). Add `POST /api/experiences/{id}/review` endpoint for moderators to add review notes and change status. Integrates with content moderation: when an experience is submitted, its world's content flags are computed and attached to the review.
-- **Acceptance:** Review queue returns IN_REVIEW experiences; review endpoint accepts notes and status change; content flags attached on submit; only moderators can review.
-- **Dependencies:** T3.1
-
-#### T3.3: Godot — Publishing UI Panel
-- **Type:** implementation
-- **Files:** `scenes/editor_publish_panel.tscn` (new), `scripts/editor_publish_panel.gd` (new)
-- **Scope:** A `Control`-based panel in the editor that lets creators manage their experience's publishing state. Features: title/description editor, status badge (DRAFT/SUBMITTED/LIVE), submit button, review notes display, publish history timeline. Integrates with the existing editor toolbar.
-- **Acceptance:** Panel shows current status; submit button calls backend; review notes displayed; publish history shown; status badge updates on refresh.
-- **Dependencies:** T3.1, T3.2
-
-#### T3.4: Godot — Experience List & Discovery
-- **Type:** implementation
-- **Files:** `scenes/experience_list.tscn` (new), `scripts/experience_list.gd` (new)
-- **Scope:** A UI panel that lists all experiences available to the player (LIVE status). Features: search/filter, experience cards (title, description, creator, play count), join button. Queries `GET /api/experiences/?status=LIVE` and displays results.
-- **Acceptance:** Lists LIVE experiences; search filters by title/description; cards show metadata; join button loads the experience's world.
-- **Dependencies:** T3.1
-
-### US-4: Creator Analytics
-
-#### T4.1: Backend — Session & Play Tracking Models
-- **Type:** architecture
-- **Files:** `backend/app/models/analytics.py` (new), `backend/app/routers/analytics.py` (new), `backend/app/main.py` (register router), `backend/app/models/__init__.py` (export)
-- **Scope:** Create `PlaySession` model (id, experience_id FK→experiences, player_id FK→users, started_at, ended_at, duration_seconds, chunk_loads INT, interactions_count INT). Create `PlayEvent` model (id, experience_id FK→experiences, player_id FK→users, event_type, payload JSON, created_at). Add endpoints: `POST /api/analytics/session/start` (start session), `POST /api/analytics/session/end` (end session), `POST /api/analytics/event` (log event). Note: `sessions` table is taken by auth tokens; this uses `play_sessions` table name.
-- **Acceptance:** Session start/end endpoints work; events logged; duration calculated correctly; endpoints accept valid experience/player IDs.
-- **Dependencies:** None (new models + routers)
-
-#### T4.2: Backend — Creator Dashboard Analytics Endpoint
-- **Type:** implementation
-- **Files:** `backend/app/routers/analytics.py` (add endpoint)
-- **Scope:** Add `GET /api/analytics/experience/{id}` endpoint that returns aggregated analytics for an experience: total_plays, unique_players, avg_session_length, total_play_time, plays_over_time (daily breakdown), top_chunks_loaded, top_interactions. Data is aggregated from PlaySession and PlayEvent tables.
-- **Acceptance:** Returns all aggregated metrics; daily breakdown covers last 30 days; only owner or moderator can view; empty experience returns zeros.
-- **Dependencies:** T4.1
-
-#### T4.3: Godot — Analytics Tracking in Game
-- **Type:** implementation
-- **Files:** `scripts/analytics_tracker.gd` (new)
-- **Scope:** A singleton-like service that tracks player sessions and events during gameplay. Automatically starts session on world load, tracks chunk loads, interactions, and time spent. Sends periodic heartbeat events to backend. Handles session end on world unload or disconnect.
-- **Acceptance:** Session starts on world load; events logged during play; heartbeat sends every 60s; session ends on world unload; handles disconnect gracefully.
-- **Dependencies:** T4.1
-
-#### T4.4: Godot — Creator Analytics Dashboard UI
-- **Type:** implementation
-- **Files:** `scenes/creator_analytics_panel.tscn` (new), `scripts/creator_analytics_panel.gd` (new)
-- **Scope:** A `Control`-based panel in the editor that displays analytics for the current experience. Features: total plays counter, avg session length, plays over time chart (simple bar chart), top chunks, top interactions. Queries `GET /api/analytics/experience/{id}` and renders data.
-- **Acceptance:** Panel shows all metrics; chart renders bar chart of daily plays; data refreshes on button press; handles empty data gracefully.
-- **Dependencies:** T4.2, T4.3
-
----
-
-## Database Migration Plan
-
-New tables needed (in order):
-1. `scripts` — script storage
-2. `experiences` — publishing state (wraps worlds)
-3. `play_sessions` — session tracking (note: `sessions` table already exists for auth tokens)
-4. `play_events` — granular events
-
-Migration file: `backend/alembic/versions/0003_creator_studio.py`
-
----
-
-## Task Dependencies Summary
-
-```
-T1.1 (glTF upload) ──→ T1.2 (catalog API) ──→ T1.3 (Godot importer)
-T2.1 (script model) ──→ T2.3 (editor UI)
-T2.2 (API surface) ──→ T2.3 (editor UI)
-T3.1 (experience model) ──→ T3.2 (review queue) ──→ T3.3 (publish UI)
-T3.1 ──→ T3.4 (discovery list)
-T4.1 (tracking model) ──→ T4.2 (dashboard endpoint) ──→ T4.4 (analytics UI)
-T4.1 ──→ T4.3 (game tracker) ──→ T4.4 (analytics UI)
-```
-
-## Implementation Order Recommendation
-
-1. **T1.1** — Foundation: asset upload with glTF metadata extraction
-2. **T2.1** — Foundation: script model + CRUD
-3. **T3.1** — Foundation: experience model + status transitions
-4. **T4.1** — Foundation: analytics tracking model
-5. **T1.2** — Asset catalog for editor
-6. **T2.2** — Script API surface definition
-7. **T3.2** — Review queue integration
-8. **T4.2** — Analytics dashboard endpoint
-9. **T1.3** — Godot glTF importer (depends on ApiClient from client-server-wiring)
-10. **T2.3** — Script editor UI
-11. **T3.3** — Publishing UI panel
-12. **T3.4** — Experience discovery list
-13. **T4.3** — Analytics tracker in game
-14. **T4.4** — Analytics dashboard UI