---
estimated_steps: 29
estimated_files: 3
skills_used: []
---

# T01: Alembic migration + SQLAlchemy model + Pydantic schema updates

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`)

## Inputs

- ``backend/models.py` — existing TechniquePage and SourceVideo models`
- ``backend/schemas.py` — existing TechniquePageBase, TechniquePageRead, TechniquePageDetail schemas`
- ``alembic/versions/011_classification_cache_and_stage_rerun.py` — previous migration in chain`

## Expected Output

- ``alembic/versions/012_multi_source_format.py` — new migration adding column and table`
- ``backend/models.py` — updated with TechniquePageVideo model and body_sections_format column`
- ``backend/schemas.py` — updated with SourceVideoSummary, widened body_sections type, source_videos on detail`

## Verification

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')"
