---
estimated_steps: 47
estimated_files: 5
skills_used: []
---

# T01: Add SearchLog model, migration, schemas, logging hook, and popular endpoint

Write all backend code for search query logging and the popular searches endpoint.

## Steps

1. Add `SearchLog` model to `backend/models.py`:
   - Columns: `id` (Integer PK autoincrement), `query` (String, indexed), `scope` (String), `result_count` (Integer), `created_at` (DateTime, indexed, default=_now)
   - Table name: `search_log`

2. Create Alembic migration `alembic/versions/013_add_search_log.py`:
   - Create `search_log` table with columns matching the model
   - Add index on `created_at` and `query` columns
   - Use the same pattern as existing migrations (see `012_multi_source_format.py` for style)

3. Add Pydantic schemas to `backend/schemas.py`:
   - `PopularSearchItem(BaseModel)`: `query: str`, `count: int`
   - `PopularSearchesResponse(BaseModel)`: `items: list[PopularSearchItem]`, `cached: bool = False`

4. Add non-blocking search logging to `backend/routers/search.py`:
   - Import `asyncio`, `SearchLog` from models, `async_session` from database
   - Add `_log_search(query, scope, result_count)` async helper that opens its own session, INSERTs a SearchLog row, commits, and catches all exceptions with `logger.warning`
   - In the `search()` handler, after computing `result` but before returning, call `asyncio.create_task(_log_search(q, scope, result['total']))` — but only if `q.strip()` is non-empty

5. Add `GET /popular` endpoint to `backend/routers/search.py`:
   - Import `json` stdlib, `get_redis` from redis_client, `func`/`text` from sqlalchemy
   - Check Redis key `chrysopedia:popular_searches` first
   - On cache hit: parse JSON, return `PopularSearchesResponse(items=..., cached=True)`
   - On cache miss: query `search_log` with `SELECT LOWER(query) as q, COUNT(*) as cnt FROM search_log WHERE created_at > now() - interval '7 days' GROUP BY LOWER(query) ORDER BY cnt DESC LIMIT 10`
   - Use SQLAlchemy `func.lower()`, `func.count()`, and `func.now()` for the query
   - Serialize result to JSON, SET in Redis with `ex=300` (5-min TTL per D025)
   - Return `PopularSearchesResponse(items=..., cached=False)`
   - Close Redis client after use

## Must-Haves

- [ ] SearchLog model with correct columns and indexes
- [ ] Alembic migration creates search_log table
- [ ] PopularSearchItem and PopularSearchesResponse schemas
- [ ] Non-blocking _log_search with fire-and-forget pattern
- [ ] Empty queries not logged
- [ ] Popular endpoint with Redis read-through cache (300s TTL)
- [ ] LOWER(query) normalization in popular query

## Verification

- `cd backend && python -c "from models import SearchLog; print(SearchLog.__tablename__)"` prints `search_log`
- `cd backend && python -c "from schemas import PopularSearchItem, PopularSearchesResponse; print('OK')"` prints OK
- `cd backend && python -c "from routers.search import router; routes = [r.path for r in router.routes]; assert '/popular' in routes; print('OK')"` prints OK

## Failure Modes

| Dependency | On error | On timeout | On malformed response |
|------------|----------|-----------|----------------------|
| PostgreSQL (log INSERT) | Catch all, log WARNING, don't fail request | Same — fire-and-forget task dies silently | N/A — we control the data |
| Redis (cache read/write) | Fall through to DB query | Fall through to DB query | Return uncached DB results |
| PostgreSQL (popular query) | Return 500 (standard FastAPI) | Return 500 | N/A |

## Negative Tests

- Empty query string: `_log_search` should not be called
- Redis down: `/popular` should still return results from DB
- No search_log rows: `/popular` should return `{items: [], cached: false}`

## Inputs

- ``backend/models.py` — existing models and _now() helper`
- ``backend/schemas.py` — existing Pydantic schemas`
- ``backend/routers/search.py` — existing search endpoint to add logging hook and new route`
- ``backend/database.py` — async_session factory for fire-and-forget INSERT`
- ``backend/redis_client.py` — get_redis() for cache operations`
- ``alembic/versions/012_multi_source_format.py` — reference for migration style`

## Expected Output

- ``backend/models.py` — SearchLog model added`
- ``backend/schemas.py` — PopularSearchItem and PopularSearchesResponse added`
- ``backend/routers/search.py` — _log_search helper, asyncio.create_task call, GET /popular endpoint added`
- ``alembic/versions/013_add_search_log.py` — new migration creating search_log table`

## Verification

cd backend && python -c "from models import SearchLog; from schemas import PopularSearchItem, PopularSearchesResponse; from routers.search import router; routes = [r.path for r in router.routes]; assert '/popular' in routes; print('ALL OK')"
