# S01: Search Query Logging + Popular Searches API

**Goal:** Backend search query logging and popular searches API endpoint with Redis caching.
**Demo:** After this: GET /api/v1/search/popular returns top 10 queries from last 7 days, cached in Redis. search_log table has rows from real searches.

## Tasks
- [x] **T01: Added SearchLog model, Alembic migration 013, Pydantic schemas, fire-and-forget search logging, and GET /search/popular endpoint with 5-minute Redis cache** — 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}`
  - Estimate: 25m
  - Files: backend/models.py, backend/schemas.py, backend/routers/search.py, alembic/versions/013_add_search_log.py, backend/redis_client.py
  - Verify: 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')"
- [x] **T02: Deployed search logging and popular searches endpoint to ub01, ran Alembic migration 013, verified end-to-end with real HTTP requests** — Push code to ub01, rebuild containers, run the Alembic migration, and verify the full flow with real HTTP requests.

## Steps

1. Commit changes locally and push to origin (the repo is at `/vmPool/r/repos/xpltdco/chrysopedia` on ub01):
   - `git add backend/models.py backend/schemas.py backend/routers/search.py alembic/versions/013_add_search_log.py`
   - `git commit -m 'feat: search query logging + popular searches endpoint (M015/S01)'`
   - `git push origin main`

2. SSH to ub01 and deploy:
   - `ssh ub01 'cd /vmPool/r/repos/xpltdco/chrysopedia && git pull && docker compose build chrysopedia-api chrysopedia-worker && docker compose up -d'`
   - Wait for containers to be healthy

3. Run Alembic migration:
   - `ssh ub01 'docker exec chrysopedia-api alembic upgrade head'`
   - Verify migration applied: `ssh ub01 "docker exec chrysopedia-db psql -U chrysopedia -c '\d search_log'"`

4. Restart web container (KNOWLEDGE: nginx stale DNS after rebuild):
   - `ssh ub01 'docker compose restart chrysopedia-web-8096'`

5. Verify search logging:
   - `curl -s 'http://ub01:8096/api/v1/search?q=reverb&scope=all'` — should return search results
   - `ssh ub01 "docker exec chrysopedia-db psql -U chrysopedia -c \"SELECT * FROM search_log ORDER BY created_at DESC LIMIT 5;\""` — should show the logged query

6. Verify popular endpoint:
   - `curl -s 'http://ub01:8096/api/v1/search/popular' | python3 -m json.tool` — should return JSON with items array
   - Run a few more searches with different terms, wait, then re-check popular (cache TTL is 5 min, so first call goes to DB)

## Must-Haves

- [ ] search_log table exists in PostgreSQL on ub01
- [ ] Real search request creates a row in search_log
- [ ] GET /api/v1/search/popular returns valid JSON with items array
- [ ] Empty search query does NOT create a search_log row

## Verification

- `curl -sf 'http://ub01:8096/api/v1/search/popular' | python3 -c "import sys,json; d=json.load(sys.stdin); assert 'items' in d; print('OK')"` succeeds
- `ssh ub01 "docker exec chrysopedia-db psql -U chrysopedia -t -c 'SELECT count(*) FROM search_log;'"` returns > 0 after a search
  - Estimate: 15m
  - Verify: curl -sf 'http://ub01:8096/api/v1/search/popular' | python3 -c "import sys,json; d=json.load(sys.stdin); assert 'items' in d; print('POPULAR OK')" && ssh ub01 "docker exec chrysopedia-db psql -U chrysopedia -t -c 'SELECT count(*) FROM search_log;'" | grep -q '[1-9]' && echo 'LOGGING OK'
