# Backend Performance & Creator Features — Session Workplan

> **Stream:** Backend — run in a separate Claude Code session while GSD executes M016
> **Conflict zone:** `backend/` only — zero frontend changes
> **Deploy cadence:** commit-build-redeploy after each task group

---

## Goal

Fix critical performance bottlenecks in the admin pipeline API, implement auto-avatar fetching for creators, and lay the backend groundwork for creator landing page improvements. Purely backend Python — no frontend changes (the creators 422 fix moved to M016 S02).

---

## Task Groups (execute sequentially)

### 1. Critical Fixes (do first, immediate impact)

#### 1a. Fix `worker-status` async event loop blocking
**File:** `backend/routers/pipeline.py` lines 1266-1313
**Problem:** The endpoint is `async def` but calls three synchronous Celery inspect methods (`inspector.active()`, `.reserved()`, `.stats()`), each with a 1-second timeout. This blocks the entire uvicorn event loop for ~3 seconds, stalling ALL concurrent API requests.
**Evidence:** Every parallel API call during page load takes ~3,024ms instead of their natural 6-38ms.
**Fix:**
```python
import asyncio

active = await asyncio.to_thread(inspector.active) or {}
reserved = await asyncio.to_thread(inspector.reserved) or {}
stats = await asyncio.to_thread(inspector.stats) or {}
```
Also consider: reduce inspect timeout to 0.5s, add Redis cache with 10-15s TTL to avoid repeated slow calls.
**Impact:** Page load drops from ~3s to ~50ms.

#### ~~1b. Fix creators endpoint 422 error~~ → Moved to M016 S02
The frontend `fetchCreators({ limit: 200 })` fix (AdminPipeline.tsx line 1126) is now part of M016's pipeline UI fixes slice, since that slice already owns AdminPipeline.tsx.

---

### 2. Pipeline API Performance

#### 2a. Rewrite `stale-pages` to eliminate N+1 queries
**File:** `backend/routers/pipeline.py` lines 906-973
**Problem:** Loads ALL technique pages, then runs a separate query per page for latest version + another for creator name. Currently 44 extra queries for 22 pages. Fast today (~30ms) but degrades linearly.
**Fix:** Single query joining `technique_pages` with a lateral/window subquery for latest version + join to creators.

#### 2b. Add pagination to videos endpoint
**File:** `backend/routers/pipeline.py` lines 72-188
**Problem:** Returns all 43 videos (23KB) with no offset/limit. Client-side filtering only.
**Fix:** Add `offset`, `limit`, `status`, `creator_id` query params. Return paginated response with `total` count. Frontend can adopt server-side filtering later (or the M016 frontend stream can wire it up).

#### 2c. Optimize `_find_dynamic_related` for technique pages
**File:** `backend/routers/techniques.py` lines 33-111
**Problem:** Loads ALL technique pages into memory to score relatedness in Python. O(n) in total page count.
**Fix:** Move scoring to SQL (keyword overlap via `ts_rank` or simple tag intersection) or cache related links per technique page with invalidation on new page creation.

---

### 3. Auto-Avatar Integration (TheAudioDB)

Research concluded: **TheAudioDB is the best first source** — free, no OAuth, no caching restrictions, decent coverage for established artists.

#### 3a. Database migration
Add to `Creator` model:
- `avatar_url: String | None` — stored image URL or local path
- `avatar_source: String` — enum: `"generated"`, `"theaudiodb"`, `"manual"`
- `avatar_fetched_at: DateTime | None` — for cache invalidation

Alembic migration (will be 014 or later depending on M015 state).

#### 3b. TheAudioDB lookup service
New file: `backend/services/avatar.py`
- `async def fetch_avatar(creator_name: str, creator_genres: list[str]) -> AvatarResult | None`
- Calls `https://www.theaudiodb.com/api/v1/json/{key}/search.php?s={name}`
- Confidence scoring: name match via `thefuzz.fuzz.token_sort_ratio` (threshold ≥ 85%), genre overlap as tiebreaker
- Returns `strArtistThumb` URL if confident match, None otherwise
- Handle: no results, multiple results, missing image fields

#### 3c. Celery worker task
New task: `tasks.fetch_creator_avatar`
- Called on creator creation or manually via admin endpoint
- Runs TheAudioDB lookup → downloads image → stores locally (or stores URL)
- Updates `Creator.avatar_url`, `avatar_source`, `avatar_fetched_at`
- Falls back gracefully — if no match, leaves fields null (frontend already renders generated SVG as fallback)

#### 3d. Admin endpoint for manual trigger
`POST /admin/pipeline/creators/{id}/fetch-avatar` — triggers the worker task for a specific creator.
`POST /admin/pipeline/creators/fetch-all-avatars` — batch trigger for all creators missing avatars.

#### 3e. Wire avatar_url into creators API responses
Add `avatar_url` to `CreatorBrowseItem` and `CreatorDetail` schemas. The frontend `CreatorAvatar` component already accepts an `imageUrl` prop — it will just work once the API returns it.

---

### 4. Creator Landing Page API Groundwork

#### 4a. Social links model + migration
Add to `Creator` model:
- `social_links: JSON | None` — structured as `{"spotify": "url", "instagram": "url", "bandcamp": "url", "website": "url", ...}`
- `bio: Text | None` — short creator bio/description
- `featured: Boolean` — flag for homepage featuring

#### 4b. Creator detail endpoint enhancement
Expand `GET /api/v1/creators/{slug}` to return:
- `social_links`
- `bio`
- `avatar_url`
- `technique_count`
- Full technique list with titles, slugs, created_at
- Genre breakdown

#### 4c. Admin endpoint for creator profile editing
`PUT /admin/pipeline/creators/{id}` — update `bio`, `social_links`, `featured` flag, manually set `avatar_url`.

---

### 5. Embed Tab Investigation
The "Embed" tab under pipeline jobs is non-functional. Before building, need to:
- Read the existing frontend component to understand what it expects
- Determine what "Embed" should show (embedding vectors? embed codes? embedded content?)
- If it's about Qdrant vector embeddings: add an endpoint to query embedding status per technique page
- If it's about iframe embed codes: generate shareable snippet per technique

**This task starts as investigation — scope will be defined after reading the code.**

---

## General Load Time Optimization (apply throughout)

As each endpoint is touched, also consider:
- Add `Cache-Control` headers for public GET endpoints (technique pages, creators, search suggestions)
- Add Redis caching (30s-5min TTL) for expensive or frequently-hit endpoints
- Ensure database indexes exist on commonly filtered/sorted columns
- Consider adding `select_in_loading` for SQLAlchemy relationships to avoid implicit lazy loads

---

## Key Files

| File | What changes |
|------|-------------|
| `backend/routers/pipeline.py` | worker-status async fix, stale-pages rewrite, videos pagination, avatar admin endpoints |
| `backend/routers/creators.py` | Creator detail expansion, social links, admin editing |
| `backend/routers/techniques.py` | Related techniques optimization |
| `backend/models.py` | Creator model additions (avatar, social_links, bio) |
| `backend/schemas.py` | New response schemas |
| `backend/services/avatar.py` | New — TheAudioDB integration |
| `backend/tasks.py` | New avatar fetch task |
| `alembic/versions/014_*.py` | Migration for creator columns |

---

## Merge Coordination with M016

These two streams have **zero file overlap:**
- **M016 touches:** `frontend/src/` only — `App.css`, `Home.tsx`, `TechniquePage.tsx`, `TableOfContents.tsx`, `AdminPipeline.tsx`, new frontend components, static assets
- **This session touches:** `backend/` only — routers, models, schemas, services, tasks, `alembic/`

No merge conflicts expected. The creators 422 fix (the former single overlap point) now lives in M016 S02.

For avatar/social-links frontend wiring: this session ships the API, M016 (or a follow-up) consumes it. No conflict — just sequencing.
