# S03: Data Model + Migration

**Goal:** Add `body_sections_format` column to technique_pages, create `technique_page_videos` association table, and expose both in the API response.
**Demo:** After this: Alembic migration runs clean. API response includes body_sections_format and source_videos fields.

## Tasks
- [x] **T01: Added body_sections_format column, technique_page_videos association table, and SourceVideoSummary schema for multi-source technique pages** — Create the Alembic migration for the new column and table, update the SQLAlchemy models, and widen the Pydantic API schemas.

This is the data layer foundation — migration file, model changes, and schema changes. The router wiring comes in T02.

## Steps

1. Create `alembic/versions/012_multi_source_format.py`:
   - Add `body_sections_format` column to `technique_pages`: `VARCHAR(20)`, `NOT NULL`, `server_default='v1'`
   - Create `technique_page_videos` table: `id` (UUID PK), `technique_page_id` (UUID FK → technique_pages.id ON DELETE CASCADE), `source_video_id` (UUID FK → source_videos.id ON DELETE CASCADE), `added_at` (TIMESTAMP, server_default=now()), UNIQUE constraint on (technique_page_id, source_video_id)
   - Downgrade: drop `technique_page_videos` table, drop `body_sections_format` column

2. Update `backend/models.py`:
   - Add `body_sections_format` mapped column to `TechniquePage`: `String(20)`, default `'v1'`, server_default `'v1'`, nullable=False
   - Add `TechniquePageVideo` model class with fields matching migration, plus `UniqueConstraint` named `uq_page_video`
   - Add `sa_relationship` on `TechniquePage`: `source_video_links: Mapped[list[TechniquePageVideo]]` with back_populates
   - Add `sa_relationship` on `TechniquePageVideo` back to both `TechniquePage` and `SourceVideo`
   - Note: use `sa_relationship` (the alias) per KNOWLEDGE.md rule about column names shadowing ORM functions

3. Update `backend/schemas.py`:
   - Change `TechniquePageBase.body_sections` type from `dict | None` to `list | dict | None`
   - Add `body_sections_format: str = "v1"` to `TechniquePageBase`
   - Add `SourceVideoSummary` schema: `id` (UUID), `filename` (str), `content_type` (str), `added_at` (datetime | None)
   - Add `source_videos: list[SourceVideoSummary] = Field(default_factory=list)` to `TechniquePageDetail`

## Must-Haves

- [ ] Migration file has both upgrade and downgrade
- [ ] `body_sections_format` defaults to `'v1'` for existing rows
- [ ] `TechniquePageVideo` has CASCADE deletes on both FKs
- [ ] Unique constraint on (technique_page_id, source_video_id)
- [ ] `body_sections` schema type widened to accept list or dict
- [ ] `SourceVideoSummary` schema uses `from_attributes=True`

## Verification

- `cd backend && python -c "from models import TechniquePageVideo, TechniquePage; print(TechniquePage.body_sections_format); print('OK')"` succeeds
- `cd backend && python -c "from schemas import SourceVideoSummary, TechniquePageDetail; print('OK')"` succeeds
- `alembic upgrade head` runs without error (tested via Docker: `docker exec chrysopedia-api alembic upgrade head`)
  - Estimate: 30m
  - Files: alembic/versions/012_multi_source_format.py, backend/models.py, backend/schemas.py
  - Verify: cd backend && python -c "from models import TechniquePageVideo, TechniquePage; assert hasattr(TechniquePage, 'body_sections_format'); print('models OK')" && python -c "from schemas import SourceVideoSummary, TechniquePageDetail; print('schemas OK')"
- [x] **T02: Wired source_videos and body_sections_format into technique detail API response with eager-loaded association table** — Update the technique detail endpoint to eagerly load source_video_links and build the source_videos list on the response. Verify end-to-end with an API call.

## Steps

1. Update `backend/routers/techniques.py` `get_technique()` function:
   - Add `selectinload(TechniquePage.source_video_links).selectinload(TechniquePageVideo.source_video)` to the query options
   - After building existing response fields, build `source_videos` list from `page.source_video_links`
   - Each item: `SourceVideoSummary(id=link.source_video.id, filename=link.source_video.filename, content_type=link.source_video.content_type.value, added_at=link.added_at)`
   - Add the import for `TechniquePageVideo` and `SourceVideoSummary` at the top of the file
   - Pass `source_videos=source_videos` to the `TechniquePageDetail(...)` constructor

2. Verify the endpoint returns the new fields:
   - Existing pages should return `body_sections_format: "v1"` (inherited from schema default + model default)
   - Existing pages should return `source_videos: []` (no rows in association table yet)

## Must-Haves

- [ ] `selectinload` for source_video_links chained to source_video
- [ ] `source_videos` list built from association table rows
- [ ] Existing pages return empty `source_videos` list (not null, not omitted)
- [ ] `body_sections_format` appears in response

## Verification

- Pick any existing technique slug from the DB
- `curl -s http://ub01:8096/techniques/{slug} | python3 -m json.tool | grep -E 'body_sections_format|source_videos'` shows both fields
- Or if testing locally: verify the router code by reading the constructed response and confirming both fields are passed
  - Estimate: 20m
  - Files: backend/routers/techniques.py
  - Verify: ssh ub01 'docker exec chrysopedia-api alembic upgrade head' && ssh ub01 'curl -s http://localhost:8000/techniques/$(docker exec chrysopedia-db psql -U chrysopedia -tAc "SELECT slug FROM technique_pages LIMIT 1") | python3 -m json.tool | grep -E "body_sections_format|source_videos"'
