feat(discovery): world discovery metadata and world_visits model with migration 0015

backend/alembic/versions/0015_add_discovery_metadata.py

@@ -0,0 +1,62 @@
+"""add discovery metadata to worlds and create world_visits table
+
+Revision ID: 0015_add_discovery_metadata
+Revises: 0014_add_payout_requests
+Create Date: 2026-09-15 00:00:00.000000
+
+docs/planning/discovery.md (task b377353b8167).
+"""
+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 = "0015_add_discovery_metadata"
+down_revision: Union[str, None] = "0014_add_payout_requests"
+branch_labels: Union[str, Sequence[str], None] = None
+depends_on: Union[str, Sequence[str], None] = None
+
+
+def upgrade() -> None:
+    # 1. Add discovery metadata columns to worlds table
+    op.add_column("worlds", sa.Column("category", sa.String(length=100), nullable=True))
+    op.add_column("worlds", sa.Column("tags", postgresql.ARRAY(sa.String()), server_default="{}", nullable=True))
+    op.add_column("worlds", sa.Column("play_count", sa.Integer(), server_default="0", nullable=False))
+    op.add_column("worlds", sa.Column("last_played_at", sa.DateTime(timezone=True), nullable=True))
+
+    op.create_index("ix_worlds_category", "worlds", ["category"], unique=False)
+    op.create_index("ix_worlds_tags", "worlds", ["tags"], unique=False, postgresql_using="gin")
+
+    # 2. Create world_visits table
+    op.create_table(
+        "world_visits",
+        sa.Column("id", sa.Integer(), autoincrement=True, nullable=False),
+        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.text("now()"), nullable=False),
+        sa.ForeignKeyConstraint(["world_id"], ["worlds.id"], ondelete="CASCADE"),
+        sa.ForeignKeyConstraint(["user_id"], ["users.id"], ondelete="SET NULL"),
+        sa.PrimaryKeyConstraint("id"),
+    )
+    op.create_index("ix_world_visits_world_id", "world_visits", ["world_id"], unique=False)
+    op.create_index("ix_world_visits_user_id", "world_visits", ["user_id"], unique=False)
+    op.create_index("ix_world_visits_visited_at", "world_visits", ["visited_at"], unique=False)
+    op.create_index("ix_world_visits_world_visited_at", "world_visits", ["world_id", "visited_at"], unique=False)
+
+
+def downgrade() -> None:
+    op.drop_index("ix_world_visits_world_visited_at", table_name="world_visits")
+    op.drop_index("ix_world_visits_visited_at", table_name="world_visits")
+    op.drop_index("ix_world_visits_user_id", table_name="world_visits")
+    op.drop_index("ix_world_visits_world_id", table_name="world_visits")
+    op.drop_table("world_visits")
+
+    op.drop_index("ix_worlds_tags", table_name="worlds", postgresql_using="gin")
+    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

@@ -15,6 +15,7 @@ from app.models.marketplace import (
     CatalogItem, MarketplaceListing, Purchase, CreatorBalance,
     ItemType, ListingStatus, PurchaseStatus,
 )
+from app.models.world_visit import WorldVisit
 
 __all__ = [
     "User", "Session", "World", "Asset", "AvatarLoadout",
@@ -26,4 +27,5 @@ __all__ = [
     "Wallet", "Transaction", "TransactionType",
     "CatalogItem", "MarketplaceListing", "Purchase", "CreatorBalance",
     "ItemType", "ListingStatus", "PurchaseStatus",
+    "WorldVisit",
 ]

backend/app/models/world.py

@@ -1,5 +1,5 @@
-from sqlalchemy import Column, Integer, String, Text, DateTime, ForeignKey
-from sqlalchemy.dialects.postgresql import ENUM
+from sqlalchemy import Column, Integer, String, Text, DateTime, ForeignKey, Index
+from sqlalchemy.dialects.postgresql import ENUM, ARRAY
 from sqlalchemy.orm import relationship
 from sqlalchemy.sql import func
 import enum
@@ -29,11 +29,19 @@ class World(Base):
         ),
         default=WorldVisibility.PUBLIC,
     )
+    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", index=True)
+    last_played_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())
 
+    __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")
-    # world_content.py / world_persistence_impl.md §4.1. Added here (only)
-    # so PC1/PC2 code depending on this module is otherwise untouched.
     rooms = relationship("WorldRoom", 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,27 @@
+"""World visit tracking model for discovery analytics.
+
+docs/planning/discovery.md (task b377353b8167).
+"""
+
+from sqlalchemy import Column, Integer, String, DateTime, ForeignKey, Index
+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, index=True)
+    user_id = Column(Integer, ForeignKey("users.id", ondelete="SET NULL"), nullable=True, index=True)
+    anonymous_token = Column(String(255), nullable=True)
+    visited_at = Column(DateTime(timezone=True), server_default=func.now(), nullable=False, index=True)
+
+    __table_args__ = (
+        Index("ix_world_visits_world_visited_at", "world_id", "visited_at"),
+    )
+
+    world = relationship("World", back_populates="visits")
+    user = relationship("User")

backend/app/routers/worlds.py

@@ -27,6 +27,8 @@ class WorldCreate(BaseModel):
     name: str = Field(..., min_length=1, max_length=255)
     description: str | None = None
     visibility: WorldVisibility = WorldVisibility.PUBLIC
+    category: str | None = None
+    tags: list[str] | None = None
 
 
 class WorldUpdate(BaseModel):
@@ -34,6 +36,8 @@ class WorldUpdate(BaseModel):
     name: str | None = Field(None, min_length=1, max_length=255)
     description: str | None = None
     visibility: WorldVisibility | None = None
+    category: str | None = None
+    tags: list[str] | None = None
 
 
 class WorldResponse(BaseModel):
@@ -42,12 +46,17 @@ class WorldResponse(BaseModel):
     name: str
     description: str | None
     visibility: WorldVisibility
+    category: str | None = None
+    tags: list[str] | None = None
+    play_count: int = 0
+    last_played_at: datetime | None = None
     owner_id: int
     created_at: datetime | None = None
 
     model_config = {"from_attributes": True}
 
 
+
 # --- Endpoints ---
 
 @router.get("/", response_model=list[WorldResponse])
@@ -159,7 +168,10 @@ async def create_world(
         description=world_data.description,
         owner_id=current_user.id,
         visibility=world_data.visibility,
+        category=world_data.category,
+        tags=world_data.tags or [],
     )
+
     db.add(world)
     await db.flush()  # assigns world.id, needed for the room's FK
     await WorldStore(db).get_or_create_main_room(world.id)

backend/tests/conftest.py

@@ -45,6 +45,8 @@ TEST_TABLES = (
     # `worlds` the way world_rooms/world_chunks would be. List all three
     # explicitly rather than rely on cascade for some of them.
     "world_chunk_revisions", "world_chunks", "world_rooms",
+    # Discovery (app/models/world_visit.py) -- FK worlds.id with ON DELETE CASCADE
+    "world_visits",
     "worlds",
     # Social graph (app/models/social.py) -- all three FK users with
     # ON DELETE CASCADE, but list explicitly for clarity/consistency.

backend/tests/test_discovery_models.py

@@ -0,0 +1,149 @@
+"""Model-level integration tests for Discovery metadata and WorldVisit.
+
+docs/planning/discovery.md (task b377353b8167).
+"""
+
+import pytest
+from fastapi.testclient import TestClient
+from sqlalchemy import select
+from sqlalchemy.ext.asyncio import async_sessionmaker, create_async_engine
+from sqlalchemy.orm import selectinload
+from sqlalchemy.pool import NullPool
+
+from app.config import settings
+from app.main import app
+from app.models.user import User
+from app.models.world import World, WorldVisibility
+from app.models.world_visit import WorldVisit
+
+
+def _register(client: TestClient, username: str) -> int:
+    resp = client.post(
+        "/api/auth/register",
+        json={
+            "username": username,
+            "email": f"{username}@example.com",
+            "password": "secret123",
+            "date_of_birth": "1990-01-01",
+        },
+    )
+    assert resp.status_code == 201, resp.text
+    headers = {"Authorization": f"Bearer {resp.json()['access_token']}"}
+    me = client.get("/api/users/me", headers=headers)
+    assert me.status_code == 200, me.text
+    return me.json()["id"]
+
+
+def _session_factory():
+    engine = create_async_engine(settings.DATABASE_URL, poolclass=NullPool)
+    return engine, async_sessionmaker(engine, expire_on_commit=False)
+
+
+@pytest.mark.asyncio
+async def test_world_discovery_metadata_and_visits():
+    client = TestClient(app)
+    user_id = _register(client, "discoverer_1")
+    visitor_id = _register(client, "visitor_1")
+
+    engine, sf = _session_factory()
+    try:
+        async with sf() as session:
+            # 1. Create a world with discovery metadata
+            world = World(
+                name="Cyber Adventure",
+                description="An epic adventure world",
+                owner_id=user_id,
+                visibility=WorldVisibility.PUBLIC,
+                category="Adventure",
+                tags=["cyberpunk", "rpg", "quest"],
+            )
+            session.add(world)
+            await session.commit()
+            await session.refresh(world)
+
+            assert world.id is not None
+            assert world.category == "Adventure"
+            assert world.tags == ["cyberpunk", "rpg", "quest"]
+            assert world.play_count == 0
+            assert world.last_played_at is None
+
+            # 2. Add authenticated visit
+            visit1 = WorldVisit(
+                world_id=world.id,
+                user_id=visitor_id,
+            )
+            # 3. Add anonymous visit
+            visit2 = WorldVisit(
+                world_id=world.id,
+                user_id=None,
+                anonymous_token="anon_token_12345",
+            )
+            session.add_all([visit1, visit2])
+            await session.commit()
+
+            # Query world with visits
+            res = await session.execute(
+                select(World).where(World.id == world.id).options(selectinload(World.visits))
+            )
+            w = res.scalar_one()
+            assert len(w.visits) == 2
+            v_user = next(v for v in w.visits if v.user_id == visitor_id)
+            assert v_user.visited_at is not None
+            v_anon = next(v for v in w.visits if v.user_id is None)
+            assert v_anon.anonymous_token == "anon_token_12345"
+
+        # 4. Verify cascade delete on world
+        async with sf() as session:
+            res = await session.execute(select(World).where(World.id == world.id))
+            w = res.scalar_one()
+            await session.delete(w)
+            await session.commit()
+
+            # Visits should be gone
+            res_v = await session.execute(select(WorldVisit).where(WorldVisit.world_id == world.id))
+            assert len(res_v.scalars().all()) == 0
+
+    finally:
+        await engine.dispose()
+
+
+@pytest.mark.asyncio
+async def test_user_delete_sets_null_on_world_visits():
+    client = TestClient(app)
+    owner_id = _register(client, "world_owner_2")
+    visitor_id = _register(client, "temp_visitor_2")
+
+    engine, sf = _session_factory()
+    try:
+        async with sf() as session:
+            world = World(
+                name="Chill Lobby",
+                owner_id=owner_id,
+                visibility=WorldVisibility.PUBLIC,
+                category="Social",
+                tags=["hangout"],
+            )
+            session.add(world)
+            await session.commit()
+            await session.refresh(world)
+
+            visit = WorldVisit(world_id=world.id, user_id=visitor_id)
+            session.add(visit)
+            await session.commit()
+            await session.refresh(visit)
+            visit_id = visit.id
+
+            # Delete visitor user
+            visitor_user = await session.get(User, visitor_id)
+            await session.delete(visitor_user)
+            await session.commit()
+
+            # In a new session, query from database to see ON DELETE SET NULL effect
+            async with sf() as session:
+                v = await session.get(WorldVisit, visit_id)
+                assert v is not None
+                assert v.user_id is None
+                assert v.world_id == world.id
+
+    finally:
+        await engine.dispose()