# S02 — Transcript Ingestion API — Research

**Date:** 2026-03-29

## Summary

S02 adds a single FastAPI endpoint: `POST /api/v1/ingest` that accepts a Whisper transcript JSON file, creates/finds the Creator record (auto-detected from `creator_folder`), creates a SourceVideo record, stores TranscriptSegment rows, saves the JSON file to the filesystem, and sets `processing_status = "transcribed"`. This is straightforward CRUD using established patterns from S01 — async SQLAlchemy sessions, Pydantic v2 schemas, and the router-per-domain convention.

The main design decisions are: (1) accept the file as `UploadFile` (multipart) or as a JSON body — `UploadFile` is better since the transcript files will be multi-MB and the API should also save the raw JSON to disk; (2) auto-create Creator records from `creator_folder` with slugified names; (3) handle idempotency for re-uploads of the same video (upsert by filename+creator).

R002 is the primary requirement. R012 (incremental content addition) is partially addressed — new creators are auto-detected and existing creator records are reused.

## Recommendation

Build one new router (`backend/routers/ingest.py`) with a single `POST /api/v1/ingest` endpoint that accepts `UploadFile`. It parses the JSON, finds-or-creates a Creator (by `folder_name`), creates a SourceVideo, bulk-inserts TranscriptSegments, saves the raw file to `transcript_storage_path`, and returns the created SourceVideo with segment count. Use a single DB transaction for atomicity. Add `python-multipart` to requirements.txt for file upload support.

## Implementation Landscape

### Key Files

- `backend/routers/ingest.py` — **New file.** The ingestion endpoint. Follows the pattern from `backend/routers/creators.py`: `APIRouter` with prefix, `Depends(get_session)`, structured logging. Core logic: parse JSON → find/create Creator → create SourceVideo → bulk insert TranscriptSegments → save file to disk → return response.
- `backend/schemas.py` — **Modify.** Add `TranscriptIngestResponse` schema (returning created video ID, creator ID, segment count, processing status). Add `TranscriptSegmentWord` schema if we want to validate the word-level data (optional — the words array is in the JSON but not stored in the DB per current schema).
- `backend/main.py` — **Modify.** Import and mount the `ingest` router under `/api/v1`.
- `backend/requirements.txt` — **Modify.** Add `python-multipart>=0.0.9` (required by FastAPI for `UploadFile`).
- `tests/test_ingest.py` — **New file.** Integration test using `httpx.AsyncClient` against the FastAPI app with a test PostgreSQL database. Uses `tests/fixtures/sample_transcript.json` as test input.

### Existing Infrastructure (no changes needed)

- `backend/models.py` — Creator, SourceVideo, TranscriptSegment models are already defined with correct columns and relationships. `Creator.folder_name` exists for matching. `SourceVideo.transcript_path` exists for storing the filesystem path. `TranscriptSegment.segment_index` exists for ordering.
- `backend/database.py` — `get_session` async dependency already exists and works.
- `backend/config.py` — `transcript_storage_path` setting already exists (defaults to `/data/transcripts`).
- `tests/fixtures/sample_transcript.json` — Sample transcript with 5 segments in the exact Whisper output format: `{source_file, creator_folder, duration_seconds, segments: [{start, end, text, words: [{word, start, end}]}]}`.

### Transcript JSON Shape (from Whisper script output)

```
{
  "source_file": "Skope — Sound Design Masterclass pt1.mp4",
  "creator_folder": "Skope",
  "duration_seconds": 3847,
  "segments": [
    {
      "start": 0.0,
      "end": 4.52,
      "text": "Hey everyone welcome back...",
      "words": [{"word": "Hey", "start": 0.0, "end": 0.28}, ...]
    }
  ]
}
```

### Creator Auto-Detection Logic

1. Extract `creator_folder` from transcript JSON (e.g., `"Skope"`)
2. `SELECT * FROM creators WHERE folder_name = :folder_name`
3. If not found: create with `name = creator_folder`, `slug = slugify(creator_folder)`, `folder_name = creator_folder`
4. If found: reuse existing creator ID

Slug generation: lowercase, replace spaces/special chars with hyphens. Use a simple function — no need for `python-slugify` dependency; a 3-line regex approach suffices for folder names.

### SourceVideo Content Type

The transcript JSON does NOT include a `content_type` field. The `SourceVideo.content_type` column is NOT NULL with enum values `tutorial|livestream|breakdown|short_form`. Options:
- **Recommended:** Default to `"tutorial"` at ingestion; allow optional override via query param or form field. The LLM pipeline (S03) can reclassify later.
- Alternative: Make the column nullable (requires migration). Not worth it — defaulting is simpler.

### Idempotency

To support re-uploading the same transcript (R012 incremental):
- Check for existing `SourceVideo` by `(creator_id, filename)` before creating
- If exists: delete old TranscriptSegments, update the SourceVideo record, re-insert segments
- This makes the endpoint idempotent — uploading the same file twice produces the same result

### Build Order

1. **Add `python-multipart` to requirements.txt** — unblocks UploadFile usage
2. **Add response schema to `schemas.py`** — small change, defines the API contract
3. **Create `backend/routers/ingest.py`** — the main work: endpoint, creator auto-detection, segment insertion, file storage
4. **Mount router in `main.py`** — one import + one `include_router` line
5. **Write integration test** — verify with sample_transcript.json against real DB

Steps 1-2 are independent. Step 3 is the bulk of work. Steps 4-5 depend on 3.

### Verification Approach

1. **Unit verification:** `python3 -c "from routers import ingest; print(ingest.router.routes)"` — confirms router imports and routes are registered.
2. **Integration test:** Start a test PostgreSQL container, run the FastAPI app with `httpx.AsyncClient`, POST the sample transcript, assert:
   - Response 200 with `video_id`, `creator_id`, `segments_stored` count
   - Creator record exists in DB with `folder_name = "Skope"` and `slug = "skope"`
   - SourceVideo record exists with `filename = "Skope — Sound Design Masterclass pt1.mp4"`, `processing_status = "transcribed"`
   - 5 TranscriptSegment rows exist with correct `segment_index` ordering
   - JSON file saved to configured `transcript_storage_path`
3. **Idempotency test:** POST the same file twice → no duplicate records, same video ID returned
4. **Docker compose config still validates:** `docker compose config` exits 0

## Constraints

- `python-multipart` must be added to `backend/requirements.txt` — FastAPI raises `RuntimeError` without it when using `UploadFile`.
- `SourceVideo.content_type` is NOT NULL with an enum constraint — ingestion must provide a valid value. Default to `"tutorial"`.
- Filesystem write to `transcript_storage_path` — in Docker this maps to `/vmPool/r/services/chrysopedia_data/transcripts`. For local testing, use a temp directory or `./data/transcripts`.
- Creator `slug` must be unique — the slugify function must produce deterministic, URL-safe slugs from folder names.

## Common Pitfalls

- **Missing `python-multipart`** — FastAPI silently accepts `UploadFile` in type hints but raises at runtime. Must be in requirements.txt.
- **Async file I/O** — `UploadFile.read()` is async but filesystem writes with `open()` are blocking. For transcript JSONs (typically <10MB), blocking writes in async handlers are acceptable. If concerned, use `aiofiles` but it's not necessary at this scale.
- **Transaction scope** — Creator creation + SourceVideo creation + segment bulk insert should be in ONE transaction. If segment insertion fails, don't leave orphaned Creator/Video records. Use `session.begin()` or rely on the session's default transaction + `await session.commit()` at the end.

## Skills Discovered

| Technology | Skill | Status |
|------------|-------|--------|
| FastAPI | wshobson/agents@fastapi-templates | available (9.6K installs) — general FastAPI patterns |
| FastAPI | mindrally/skills@fastapi-python | available (4.3K installs) — Python FastAPI patterns |
