# S01 Research — Search Query Logging + Popular Searches API

**Depth:** Light — all patterns established in codebase, no new technology.

## Summary

This slice adds: (1) a `search_log` PostgreSQL table, (2) non-blocking INSERT on every search request, (3) a `GET /api/v1/search/popular` endpoint that returns top-10 queries from the last 7 days with Redis read-through cache (5-min TTL), and (4) Alembic migration. All building blocks exist in the codebase already.

**Requirements targeted:** R035 (Popular Search Terms Display) — this slice delivers the backend half (logging + API). S04 delivers the frontend display.

## Implementation Landscape

### Existing Code

| File | Role | Relevance |
|---|---|---|
| `backend/routers/search.py` | Search router (`/api/v1/search`) | Add logging hook inside `search()`, add new `/popular` endpoint |
| `backend/models.py` | All SQLAlchemy models | Add `SearchLog` model |
| `backend/schemas.py` | Pydantic response models | Add `PopularSearchItem` + `PopularSearchesResponse` |
| `backend/database.py` | Async engine + session factory | Use `async_session()` for fire-and-forget INSERT |
| `backend/redis_client.py` | `get_redis()` → `redis.asyncio` client | Use for cache get/set |
| `backend/config.py` | Settings with `redis_url` | Already has Redis URL |
| `backend/main.py` | Router mounting at `/api/v1` | Search router already mounted, no changes needed |
| `alembic/` | Migration infrastructure | New migration for `search_log` table |

### SearchLog Model Design

```
search_log table:
  id: int (PK, autoincrement)
  query: str (indexed) — the raw search query
  scope: str — "all" | "topics" | "creators"
  result_count: int — number of results returned
  created_at: datetime (indexed) — naive UTC timestamp
```

Index on `created_at` for the 7-day window query. Index on `query` for the GROUP BY aggregation.

### Non-blocking INSERT Pattern

The search endpoint is async. To avoid slowing the response, use `asyncio.create_task()` with a helper that opens its own session, INSERTs, commits, and handles errors silently (log WARNING on failure). This is the same "non-blocking side-effect" pattern documented in KNOWLEDGE.md.

```python
async def _log_search(query: str, scope: str, result_count: int) -> None:
    try:
        async with async_session() as session:
            session.add(SearchLog(query=query, scope=scope, result_count=result_count))
            await session.commit()
    except Exception:
        logger.warning("Failed to log search query", exc_info=True)
```

Call `asyncio.create_task(_log_search(...))` after computing results but before returning the response.

### Popular Searches Endpoint

`GET /api/v1/search/popular` — no auth required.

Logic:
1. Check Redis key `chrysopedia:popular_searches` 
2. If cache hit → return parsed JSON
3. If cache miss → query `search_log` table:
   ```sql
   SELECT LOWER(query), COUNT(*) as cnt 
   FROM search_log 
   WHERE created_at > now() - interval '7 days'
   GROUP BY LOWER(query)
   ORDER BY cnt DESC
   LIMIT 10
   ```
4. Serialize to JSON, SET in Redis with 300s TTL (5 minutes per D025)
5. Return response

Response schema:
```python
class PopularSearchItem(BaseModel):
    query: str
    count: int

class PopularSearchesResponse(BaseModel):
    items: list[PopularSearchItem]
    cached: bool = False
```

### Alembic Migration

Next migration: `013_add_search_log.py`. The `alembic/versions/` directory has migrations up to `012_multi_source_format.py`. Alembic runs inside Docker: `docker exec chrysopedia-api alembic upgrade head`.

### Key Constraints

- **Naive datetimes:** Use `_now()` helper from models.py (strips tzinfo) per KNOWLEDGE rule about asyncpg rejecting timezone-aware datetimes.
- **Non-blocking:** `asyncio.create_task()` — don't await the INSERT in the request path.
- **Redis TTL:** 300 seconds (5 min) per D025.
- **Cache key:** `chrysopedia:popular_searches` per M015-CONTEXT.md integration points.
- **LOWER(query):** Normalize case so "Reverb" and "reverb" count as one term.

## Recommendation

### Task Decomposition (3 tasks)

**T01: SearchLog model + Alembic migration** (~10 min)
- Add `SearchLog` class to `models.py`
- Generate Alembic migration (`013_add_search_log.py`)
- Run migration on ub01
- Verify: `docker exec chrysopedia-db psql -U chrysopedia -c "\d search_log"`

**T02: Search logging hook + popular endpoint** (~15 min)  
- Add `PopularSearchItem` and `PopularSearchesResponse` to `schemas.py`
- Add `_log_search()` async helper and `asyncio.create_task()` call in `search()` handler
- Add `GET /popular` endpoint to search router with Redis read-through cache
- Verify: curl search → check DB row; curl popular → check response + Redis key

**T03: Deploy + integration verify** (~5 min)
- Rebuild containers on ub01
- Run a few real searches via the UI
- Confirm `search_log` rows appear
- Confirm `/api/v1/search/popular` returns results
- Restart nginx if needed (KNOWLEDGE: stale DNS after rebuild)

### Risks

None significant. Volume is tiny (~dozens of searches/day). The fire-and-forget task pattern is standard Python asyncio. Redis client is already available.
