---
id: S02
parent: M001
milestone: M001
provides:
  - POST /api/v1/ingest endpoint accepting Whisper transcript JSON
  - Creator and SourceVideo records in PostgreSQL with TranscriptSegments
  - Raw transcript JSON persisted to transcript_storage_path
  - pytest-asyncio test infrastructure with async fixtures and ASGI client
  - TranscriptIngestResponse Pydantic schema
requires:
  []
affects:
  - S03
key_files:
  - backend/routers/ingest.py
  - backend/schemas.py
  - backend/main.py
  - backend/requirements.txt
  - backend/tests/conftest.py
  - backend/tests/test_ingest.py
  - backend/tests/fixtures/sample_transcript.json
  - backend/pytest.ini
  - backend/models.py
key_decisions:
  - Used NullPool for test engine to avoid asyncpg connection contention in pytest-asyncio
  - Fixed _now() helper to return naive UTC datetimes for asyncpg TIMESTAMP WITHOUT TIME ZONE compatibility
  - Used slugify helper inline in ingest.py rather than a shared utils module
  - Set file_path to {creator_folder}/{source_file} for new SourceVideo records
patterns_established:
  - pytest-asyncio integration test pattern: function-scoped NullPool engine + ASGI transport client with dependency overrides
  - Multipart JSON file upload pattern for FastAPI endpoints
  - Creator auto-detection from folder_name with find-or-create and slugify
observability_surfaces:
  - INFO-level structured logging on successful ingest (creator name, filename, segment count)
  - pytest output with per-test pass/fail and timing via -v flag
drill_down_paths:
  - .gsd/milestones/M001/slices/S02/tasks/T01-SUMMARY.md
  - .gsd/milestones/M001/slices/S02/tasks/T02-SUMMARY.md
duration: ""
verification_result: passed
completed_at: 2026-03-29T22:19:19.537Z
blocker_discovered: false
---

# S02: Transcript Ingestion API

**Delivered POST /api/v1/ingest endpoint with creator auto-detection, SourceVideo upsert, TranscriptSegment bulk insert, raw JSON persistence, and 6 passing integration tests against real PostgreSQL.**

## What Happened

This slice built the transcript ingestion API — the critical bridge between Whisper transcription output (S01) and the LLM extraction pipeline (S03).

**T01 — Ingest Endpoint** created `POST /api/v1/ingest` accepting multipart JSON uploads. The endpoint parses Whisper transcript JSON, finds-or-creates a Creator by `folder_name` (with slugify), upserts a SourceVideo by `(creator_id, filename)` — deleting old segments on re-upload for idempotency — bulk-inserts TranscriptSegments with `segment_index`, saves the raw JSON to the configured `transcript_storage_path`, and returns a structured `TranscriptIngestResponse`. Error handling covers invalid files (400), malformed JSON (422), and DB/filesystem failures (500). The router is mounted in `main.py` under `/api/v1`.

**T02 — Integration Tests** established the pytest + pytest-asyncio test infrastructure with async fixtures: function-scoped engine with NullPool for connection isolation, ASGI transport test client with dependency overrides, and a sample transcript fixture. Six tests prove: (1) happy-path creator+video+segments creation, (2) existing creator reuse, (3) idempotent re-upload (same video_id, no duplicate segments), (4) raw JSON persistence to disk, (5) invalid JSON rejection, (6) missing fields rejection. During T02, a bug was found and fixed in `models.py` — the `_now()` helper produced timezone-aware datetimes that asyncpg rejects for TIMESTAMP WITHOUT TIME ZONE columns.

All slice verification checks pass: router routes, schema fields, dependency presence, main.py wiring, 6/6 tests green, docker compose config valid.

## Verification

All verification checks passed:
1. `python -m pytest tests/test_ingest.py -v` — 6/6 tests passed in 2.93s
2. `from routers.ingest import router; print(router.routes)` — outputs `['/ingest']`
3. `from schemas import TranscriptIngestResponse; print(model_fields.keys())` — shows all 7 fields
4. `grep -q 'python-multipart' requirements.txt` — exits 0
5. `grep -q 'ingest' main.py` — exits 0
6. `docker compose config > /dev/null` — exits 0

## Requirements Advanced

- R002 — POST /api/v1/ingest endpoint accepts transcript JSON uploads, creates/updates Creator and SourceVideo records, stores transcript data in PostgreSQL, and persists raw JSON to filesystem. 6 integration tests prove the full flow.

## Requirements Validated

- R002 — 6 passing integration tests: happy-path ingestion creates Creator+SourceVideo+5 TranscriptSegments, re-upload is idempotent (same video_id, segments replaced not doubled), existing creators are reused, raw JSON saved to disk, invalid/malformed JSON rejected with proper HTTP status codes.

## New Requirements Surfaced

None.

## Requirements Invalidated or Re-scoped

None.

## Deviations

Fixed bug in backend/models.py _now() function during T02 — changed from datetime.now(timezone.utc) to datetime.now(timezone.utc).replace(tzinfo=None) to match TIMESTAMP WITHOUT TIME ZONE column types. This was discovered during integration testing and was not in the original plan.

## Known Limitations

Tests require a running PostgreSQL instance on localhost:5433 — they are integration tests, not unit tests. No SQLite fallback. The test database (chrysopedia_test) must be created manually before running tests.

## Follow-ups

None.

## Files Created/Modified

- `backend/routers/ingest.py` — New file — POST /api/v1/ingest endpoint with creator auto-detection, SourceVideo upsert, TranscriptSegment bulk insert, raw JSON persistence
- `backend/schemas.py` — Added TranscriptIngestResponse Pydantic model with 7 fields
- `backend/main.py` — Mounted ingest router under /api/v1 prefix
- `backend/requirements.txt` — Added python-multipart, pytest, pytest-asyncio, httpx dependencies
- `backend/models.py` — Fixed _now() to return naive UTC datetimes for asyncpg compatibility
- `backend/tests/conftest.py` — New file — async test fixtures: NullPool engine, ASGI client, sample transcript path
- `backend/tests/test_ingest.py` — New file — 6 integration tests for ingest endpoint
- `backend/tests/fixtures/sample_transcript.json` — New file — 5-segment sample transcript JSON fixture
- `backend/pytest.ini` — New file — asyncio_mode = auto configuration
