# S03 Research: Data Model + Migration

## Summary

Straightforward data model expansion. Three changes needed: (1) add `body_sections_format` column to `technique_pages`, (2) create `technique_page_videos` association table, (3) update API response schemas to expose both fields. No new technology, no ambiguous requirements — established Alembic + SQLAlchemy patterns already in codebase.

## Recommendation

Light-touch migration + model/schema update. Single Alembic migration file. Model changes in `models.py`, API schema changes in `schemas.py`, router query updates in `techniques.py` to populate `source_videos`.

---

## Implementation Landscape

### Current State

**TechniquePage model** (`backend/models.py`, line ~195):
- `body_sections: Mapped[dict | None] = mapped_column(JSONB, nullable=True)` — stores either old flat dict or new v2 list-of-objects
- No `body_sections_format` column — the pipeline Pydantic schema (`pipeline/schemas.py`) has `body_sections_format='v2'` but it's never persisted to DB
- No association table linking technique pages to source videos

**Video-to-page relationship today**: Indirect via `key_moments` table — `KeyMoment.source_video_id` + `KeyMoment.technique_page_id`. You can derive contributing videos with a JOIN, but there's no direct record. S04 needs explicit tracking for composition history.

**API schemas** (`backend/schemas.py`):
- `TechniquePageBase.body_sections: dict | None` — must become `list | dict | None` to accept v2 format
- No `body_sections_format` field on any read schema
- No `source_videos` field on detail response

**Alembic chain**: `001_initial` → ... → `011_cls_cache_rerun`. Next revision = `012`.

**Migration runner** (`alembic/env.py`): Async PostgreSQL via `asyncpg`, `sys.path` bootstrapped for both local and Docker. DB URL from `DATABASE_URL` env var or `alembic.ini` fallback (`postgresql+asyncpg://chrysopedia:changeme@localhost:5433/chrysopedia`).

### What Needs to Change

#### 1. New Alembic migration (`alembic/versions/012_multi_source_format.py`)

```
technique_pages:
  + body_sections_format  VARCHAR(20)  DEFAULT 'v1'  NOT NULL

technique_page_videos (new 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 DEFAULT now()
  UNIQUE(technique_page_id, source_video_id)
```

Default `body_sections_format` to `'v1'` for existing rows. New pages from the v2 pipeline will set `'v2'`. This lets the frontend discriminate rendering: format-1 pages use old dict iteration, format-2 pages use nested section rendering (S05).

The `technique_page_videos` table is a simple many-to-many join. `added_at` tracks when a video was composed into the page (useful for S06 admin UI composition history).

#### 2. Model updates (`backend/models.py`)

Add to `TechniquePage`:
```python
body_sections_format: Mapped[str] = mapped_column(
    String(20), default="v1", server_default="v1", nullable=False
)
```

New association model:
```python
class TechniquePageVideo(Base):
    __tablename__ = "technique_page_videos"
    __table_args__ = (
        UniqueConstraint("technique_page_id", "source_video_id", name="uq_page_video"),
    )
    id: Mapped[uuid.UUID] = _uuid_pk()
    technique_page_id: Mapped[uuid.UUID] = mapped_column(
        ForeignKey("technique_pages.id", ondelete="CASCADE"), nullable=False
    )
    source_video_id: Mapped[uuid.UUID] = mapped_column(
        ForeignKey("source_videos.id", ondelete="CASCADE"), nullable=False
    )
    added_at: Mapped[datetime] = mapped_column(default=_now, server_default=func.now())
```

Add relationships on `TechniquePage`:
```python
source_video_links: Mapped[list[TechniquePageVideo]] = sa_relationship(back_populates="technique_page")
```

And on `SourceVideo` (optional, for reverse navigation):
```python
technique_page_links: Mapped[list[TechniquePageVideo]] = sa_relationship(back_populates="source_video")
```

#### 3. API schema updates (`backend/schemas.py`)

`TechniquePageBase`:
- Change `body_sections: dict | None = None` → `body_sections: list | dict | None = None`
- Add `body_sections_format: str = "v1"`

`TechniquePageRead` (inherits from Base, so gets both automatically).

`TechniquePageDetail`:
- Add `source_videos: list[SourceVideoSummary] = Field(default_factory=list)`
- New lightweight schema:
```python
class SourceVideoSummary(BaseModel):
    model_config = ConfigDict(from_attributes=True)
    id: uuid.UUID
    filename: str
    content_type: str
    added_at: datetime | None = None
```

#### 4. Router query update (`backend/routers/techniques.py`)

In `get_technique()` (the detail endpoint): eagerly load `source_video_links` → `source_video`, build `source_videos` list on the response. The list endpoint doesn't need source_videos (too heavy for a list view).

#### 5. Pipeline write path (`backend/pipeline/stages.py`)

When creating/updating a TechniquePage in `stage5_synthesis`:
- Set `body_sections_format = "v2"` on the page
- After creating/updating the page, upsert a `TechniquePageVideo` row for the current video

This is S04 scope (pipeline compose-or-create logic), but the model/migration must support it. S03 just needs to ensure the columns and tables exist.

### Constraints and Gotchas

1. **Default value for existing rows**: `body_sections_format` must default to `'v1'` so existing pages don't break. The migration's `server_default='v1'` handles this.

2. **JSONB type flexibility**: The `body_sections` DB column is JSONB, which already accepts any JSON type (list, dict, null). No DDL change needed — only the Pydantic schema type annotation needs widening.

3. **Backfill `technique_page_videos`**: Existing pages don't have entries in the new table. We could backfill from `key_moments` JOINs, but the roadmap demo only requires the table to exist and API to expose it — actual population happens in S04. Empty `source_videos: []` for existing pages is fine.

4. **Migration must run on production DB (ub01:5433)**: Use `docker exec chrysopedia-api alembic upgrade head` per the project's existing deployment pattern.

### Task Decomposition Seams

Three natural tasks:
1. **Migration file** — `alembic/versions/012_multi_source_format.py` (standalone, can be verified with `alembic upgrade head`)
2. **Model + Schema** — `models.py` (new column + model), `schemas.py` (response fields) — verifiable with import check
3. **Router wiring** — `techniques.py` detail endpoint loads source_videos — verifiable with API call to `/techniques/{slug}`

Tasks 2 and 3 could be one task since they're small, but splitting keeps verification clean.

### Verification Strategy

- `alembic upgrade head` runs clean (no errors, table and column exist)
- `python -c "from models import TechniquePageVideo, TechniquePage"` — imports succeed
- `curl localhost:8000/techniques/{slug}` response includes `body_sections_format` and `source_videos` fields
- For local testing without Docker: SQLAlchemy model import + Pydantic schema serialization test
