feat(discovery): world visit tracking endpoint with atomic play_count increment, last_played_at, and deduplication

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/main.py

@@ -9,7 +9,7 @@ from app.config import settings
 from app.routers import (
     auth, users, worlds, assets, avatar, world_content, internal,
     presence, groups, social_friends, profiles, chat_dm, chat_reports,
-    chat_world, wallet, payments, marketplace, payouts,
+    chat_world, wallet, payments, marketplace, payouts, discovery,
 )
 from app.gateway import ws
 
@@ -62,7 +62,9 @@ app.include_router(wallet.router, prefix="/api/wallet", tags=["wallet"])
 app.include_router(payments.router, prefix="/api/payments", tags=["payments"])
 app.include_router(marketplace.router, prefix="/api/marketplace", tags=["marketplace"])
 app.include_router(payouts.router, prefix="/api/payouts", tags=["payouts"])
+app.include_router(discovery.router, prefix="/api/discovery", tags=["discovery"])
 app.include_router(ws.router, prefix="/api", tags=["websocket"])
+
 app.include_router(ws.router, prefix="/v1", tags=["websocket"])
 
 

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 import Column, Integer, String, Text, DateTime, ForeignKey, Index
-from sqlalchemy.dialects.postgresql import ENUM
+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/discovery.py

@@ -0,0 +1,134 @@
+"""Discovery router - visit tracking and world browsing.
+
+docs/planning/discovery.md (tasks 42726e60fe10, 300da1dcddf3, 33706f401ad9).
+"""
+
+from datetime import datetime, timedelta, timezone
+from typing import Annotated
+
+from fastapi import APIRouter, Depends, Header, HTTPException, Query, Request, status
+from pydantic import BaseModel
+from sqlalchemy import func, select, update
+from sqlalchemy.ext.asyncio import AsyncSession
+
+from app.database import get_db
+from app.dependencies import OptionalUser
+from app.models.world import World, WorldVisibility
+from app.models.world_visit import WorldVisit
+
+router = APIRouter()
+
+DbDep = Annotated[AsyncSession, Depends(get_db)]
+
+
+class VisitRequest(BaseModel):
+    anonymous_token: str | None = None
+    idempotency_key: str | None = None
+
+
+class VisitResponse(BaseModel):
+    play_count: int
+    last_played_at: datetime | None = None
+
+    model_config = {"from_attributes": True}
+
+
+DEDUP_WINDOW_SECONDS = 60
+
+
+@router.post("/worlds/{world_id}/visit", response_model=VisitResponse)
+async def record_world_visit(
+    world_id: int,
+    request: Request,
+    db: DbDep,
+    current_user: OptionalUser = None,
+    visit_data: VisitRequest | None = None,
+    x_idempotency_key: Annotated[str | None, Header(alias="X-Idempotency-Key")] = None,
+    idempotency_key: str | None = Query(None),
+):
+    """Record a visit to a world.
+
+    - Increments worlds.play_count atomically (World.play_count + 1 in UPDATE).
+    - Updates worlds.last_played_at to func.now().
+    - Records row in world_visits (with world_id, user_id nullable, visited_at).
+    - Checks visibility: 404 if world not found or not PUBLIC and not owned by user.
+    - Idempotent: repeated calls within deduplication window or with same token/key do not duplicate.
+    """
+    # 1. Fetch world
+    res = await db.execute(select(World).where(World.id == world_id))
+    world = res.scalar_one_or_none()
+    if not world:
+        raise HTTPException(status_code=status.HTTP_404_NOT_FOUND, detail="World not found")
+
+    # 2. Check visibility: public or owned by requesting user
+    if world.visibility != WorldVisibility.PUBLIC:
+        if not current_user or world.owner_id != current_user.id:
+            raise HTTPException(status_code=status.HTTP_404_NOT_FOUND, detail="World not found")
+
+    # Determine token / idempotency key
+    explicit_key = (
+        (visit_data.idempotency_key if visit_data else None)
+        or (visit_data.anonymous_token if visit_data else None)
+        or x_idempotency_key
+        or idempotency_key
+    )
+
+    fallback_host = request.client.host if request.client else "unknown_client"
+    dedup_key = explicit_key or (f"client_{fallback_host}" if not current_user else None)
+
+    # 3. Deduplication check
+    cutoff = datetime.now(timezone.utc) - timedelta(seconds=DEDUP_WINDOW_SECONDS)
+    is_duplicate = False
+
+    if current_user:
+        dedup_res = await db.execute(
+            select(WorldVisit).where(
+                WorldVisit.world_id == world_id,
+                WorldVisit.user_id == current_user.id,
+                WorldVisit.visited_at >= cutoff,
+            ).limit(1)
+        )
+        if dedup_res.scalar_one_or_none():
+            is_duplicate = True
+    elif dedup_key:
+        dedup_res = await db.execute(
+            select(WorldVisit).where(
+                WorldVisit.world_id == world_id,
+                WorldVisit.anonymous_token == dedup_key,
+                WorldVisit.visited_at >= cutoff,
+            ).limit(1)
+        )
+        if dedup_res.scalar_one_or_none():
+            is_duplicate = True
+
+    if is_duplicate:
+        return VisitResponse(
+            play_count=world.play_count,
+            last_played_at=world.last_played_at,
+        )
+
+    # 4. Atomic increment and visit log in single transaction
+    await db.execute(
+        update(World)
+        .where(World.id == world_id)
+        .values(
+            play_count=World.play_count + 1,
+            last_played_at=func.now(),
+        )
+    )
+
+    visit = WorldVisit(
+        world_id=world_id,
+        user_id=current_user.id if current_user else None,
+        anonymous_token=dedup_key if not current_user else None,
+    )
+    db.add(visit)
+    await db.commit()
+
+    # Re-fetch or refresh to return updated fields
+    await db.refresh(world)
+
+    return VisitResponse(
+        play_count=world.play_count,
+        last_played_at=world.last_played_at,
+    )

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()

backend/tests/test_discovery_visit_api.py

@@ -0,0 +1,164 @@
+"""API tests for world visit tracking endpoint.
+
+docs/planning/discovery.md (task 42726e60fe10).
+"""
+
+import pytest
+from fastapi.testclient import TestClient
+from sqlalchemy import select
+from sqlalchemy.ext.asyncio import async_sessionmaker, create_async_engine
+from sqlalchemy.pool import NullPool
+
+from app.config import settings
+from app.main import app
+from app.models.world import World, WorldVisibility
+from app.models.world_visit import WorldVisit
+
+
+def _register(client: TestClient, username: str) -> tuple[int, dict[str, str]]:
+    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
+    token = resp.json()["access_token"]
+    headers = {"Authorization": f"Bearer {token}"}
+    me = client.get("/api/users/me", headers=headers)
+    assert me.status_code == 200, me.text
+    return me.json()["id"], headers
+
+
+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_visit_world_atomic_increment_authenticated():
+    client = TestClient(app)
+    owner_id, owner_headers = _register(client, "visit_owner_1")
+    visitor_id, visitor_headers = _register(client, "visitor_user_1")
+
+    # Create world
+    resp = client.post(
+        "/api/worlds/",
+        headers=owner_headers,
+        json={"name": "Neon City", "visibility": "public", "category": "Roleplay"},
+    )
+    assert resp.status_code == 201, resp.text
+    world_id = resp.json()["id"]
+
+    # Visitor visits the world
+    v_resp = client.post(
+        f"/api/discovery/worlds/{world_id}/visit",
+        headers=visitor_headers,
+    )
+    assert v_resp.status_code == 200, v_resp.text
+    data = v_resp.json()
+    assert data["play_count"] == 1
+    assert data["last_played_at"] is not None
+
+    # Verify DB state
+    engine, sf = _session_factory()
+    try:
+        async with sf() as session:
+            w = (await session.execute(select(World).where(World.id == world_id))).scalar_one()
+            assert w.play_count == 1
+            assert w.last_played_at is not None
+
+            visits = (
+                await session.execute(
+                    select(WorldVisit).where(WorldVisit.world_id == world_id)
+                )
+            ).scalars().all()
+            assert len(visits) == 1
+            assert visits[0].user_id == visitor_id
+    finally:
+        await engine.dispose()
+
+
+@pytest.mark.asyncio
+async def test_visit_world_anonymous_and_idempotency():
+    client = TestClient(app)
+    owner_id, owner_headers = _register(client, "visit_owner_2")
+
+    resp = client.post(
+        "/api/worlds/",
+        headers=owner_headers,
+        json={"name": "Space Station", "visibility": "public"},
+    )
+    world_id = resp.json()["id"]
+
+    # Anonymous visit with token
+    anon_token = "guest_token_999"
+    v_resp1 = client.post(
+        f"/api/discovery/worlds/{world_id}/visit",
+        json={"anonymous_token": anon_token},
+    )
+    assert v_resp1.status_code == 200, v_resp1.text
+    assert v_resp1.json()["play_count"] == 1
+
+    # Idempotent second call with same anonymous token
+    v_resp2 = client.post(
+        f"/api/discovery/worlds/{world_id}/visit",
+        json={"anonymous_token": anon_token},
+    )
+    assert v_resp2.status_code == 200, v_resp2.text
+    assert v_resp2.json()["play_count"] == 1
+
+    engine, sf = _session_factory()
+    try:
+        async with sf() as session:
+            visits = (
+                await session.execute(
+                    select(WorldVisit).where(WorldVisit.world_id == world_id)
+                )
+            ).scalars().all()
+            assert len(visits) == 1
+            assert visits[0].user_id is None
+            assert visits[0].anonymous_token == anon_token
+    finally:
+        await engine.dispose()
+
+
+@pytest.mark.asyncio
+async def test_visit_world_visibility_and_404():
+    client = TestClient(app)
+    owner_id, owner_headers = _register(client, "visit_owner_3")
+    other_id, other_headers = _register(client, "other_user_3")
+
+    # 404 on non-existent world
+    res_404 = client.post("/api/discovery/worlds/999999/visit", headers=owner_headers)
+    assert res_404.status_code == 404
+
+    # Private world
+    resp = client.post(
+        "/api/worlds/",
+        headers=owner_headers,
+        json={"name": "Secret World", "visibility": "private"},
+    )
+    priv_world_id = resp.json()["id"]
+
+    # Other user gets 404
+    other_res = client.post(
+        f"/api/discovery/worlds/{priv_world_id}/visit",
+        headers=other_headers,
+    )
+    assert other_res.status_code == 404
+
+    # Anonymous user gets 404
+    anon_res = client.post(f"/api/discovery/worlds/{priv_world_id}/visit")
+    assert anon_res.status_code == 404
+
+    # Owner gets 200
+    owner_res = client.post(
+        f"/api/discovery/worlds/{priv_world_id}/visit",
+        headers=owner_headers,
+    )
+    assert owner_res.status_code == 200
+    assert owner_res.json()["play_count"] == 1