# S02: Transcript Ingestion API

**Goal:** POST a transcript JSON file to the API; Creator and Source Video records appear in PostgreSQL
**Demo:** After this: POST a transcript JSON file to the API; Creator and Source Video records appear in PostgreSQL

## Tasks
- [x] **T01: Created POST /api/v1/ingest endpoint that accepts Whisper transcript JSON uploads, auto-detects creators from folder_name, upserts SourceVideo records, bulk-inserts TranscriptSegments, and persists raw JSON to disk** — Create the POST /api/v1/ingest endpoint that accepts a Whisper transcript JSON as multipart UploadFile, parses it, finds-or-creates a Creator record from creator_folder, creates/updates a SourceVideo, bulk-inserts TranscriptSegments, saves the raw JSON to disk, and returns a structured response. Wire the router into main.py.

## Steps

1. Add `python-multipart>=0.0.9` to `backend/requirements.txt` and install it (`pip install python-multipart`).
2. Add `TranscriptIngestResponse` Pydantic schema to `backend/schemas.py` with fields: `video_id: uuid.UUID`, `creator_id: uuid.UUID`, `creator_name: str`, `filename: str`, `segments_stored: int`, `processing_status: str`, `is_reupload: bool`.
3. Create `backend/routers/ingest.py` with an `APIRouter(prefix="/ingest", tags=["ingest"])`. Implement `POST ""` endpoint accepting `file: UploadFile`. Core logic:
   - Read and parse JSON from the uploaded file. Validate required top-level keys: `source_file`, `creator_folder`, `duration_seconds`, `segments`.
   - Implement a `slugify()` helper: lowercase, replace non-alphanumeric chars with hyphens, strip leading/trailing hyphens, collapse consecutive hyphens.
   - Find Creator by `folder_name`. If not found, create one with `name=creator_folder`, `slug=slugify(creator_folder)`, `folder_name=creator_folder`.
   - Check for existing SourceVideo by `(creator_id, filename)`. If found, delete old TranscriptSegments for that video and update the SourceVideo record (upsert). If not found, create new SourceVideo with `content_type="tutorial"`, `processing_status="transcribed"`.
   - Bulk-insert TranscriptSegment rows from `segments` array. Map: `start` → `start_time`, `end` → `end_time`, `text` → `text`, array index → `segment_index`.
   - Save raw JSON to `{transcript_storage_path}/{creator_folder}/{source_file}.json`. Create parent directories with `os.makedirs(..., exist_ok=True)`.
   - Set SourceVideo `transcript_path` to the saved file path.
   - Commit the transaction. Return `TranscriptIngestResponse`.
4. Add structured logging: log at INFO level on successful ingest with creator name, filename, segment count.
5. Import and mount the ingest router in `backend/main.py`: `from routers import ingest` and `app.include_router(ingest.router, prefix="/api/v1")`.
6. Verify: `cd backend && python3 -c "from routers.ingest import router; print([r.path for r in router.routes])"` prints the ingest route.

## Must-Haves

- [ ] `python-multipart` in requirements.txt
- [ ] `TranscriptIngestResponse` schema in schemas.py
- [ ] `POST /api/v1/ingest` endpoint in ingest.py
- [ ] Creator find-or-create by folder_name with slugify
- [ ] SourceVideo upsert by (creator_id, filename)
- [ ] TranscriptSegment bulk insert with segment_index
- [ ] Raw JSON saved to transcript_storage_path
- [ ] Router mounted in main.py
- [ ] Structured logging on successful ingest

## Failure Modes

| Dependency | On error | On timeout | On malformed response |
|------------|----------|-----------|----------------------|
| UploadFile read | Return 400 with 'Invalid file' message | N/A (local read) | Return 422 with JSON parse error details |
| PostgreSQL | Transaction rollback, return 500 | Return 500 with timeout message | N/A |
| Filesystem write | Return 500 with 'Failed to save transcript' | N/A | N/A |

## Negative Tests

- **Malformed inputs**: Non-JSON file upload, JSON missing `segments` key, JSON missing `creator_folder`, empty segments array
- **Error paths**: Invalid JSON syntax, file system permission error
- **Boundary conditions**: Re-upload same file (idempotency), very long creator_folder name, special characters in source_file name

## Verification

- `cd backend && python3 -c "from routers.ingest import router; print([r.path for r in router.routes])"` outputs `['/ingest']`
- `cd backend && python3 -c "from schemas import TranscriptIngestResponse; print(TranscriptIngestResponse.model_fields.keys())"` outputs the expected fields
- `grep -q 'python-multipart' backend/requirements.txt` exits 0
- `grep -q 'ingest' backend/main.py` exits 0
  - Estimate: 45m
  - Files: backend/requirements.txt, backend/schemas.py, backend/routers/ingest.py, backend/main.py
  - Verify: cd backend && python3 -c "from routers.ingest import router; print([r.path for r in router.routes])" && python3 -c "from schemas import TranscriptIngestResponse; print(TranscriptIngestResponse.model_fields.keys())" && grep -q 'python-multipart' requirements.txt && grep -q 'ingest' main.py
- [x] **T02: Added 6 integration tests proving ingestion, creator auto-detection, and idempotent re-upload against real PostgreSQL** — Set up pytest + pytest-asyncio test infrastructure and write integration tests for the ingest endpoint. Tests run against a real PostgreSQL database using httpx.AsyncClient on the FastAPI app.

## Steps

1. Add test dependencies to `backend/requirements.txt`: `pytest>=8.0`, `pytest-asyncio>=0.24`, `python-multipart>=0.0.9` (if not already present).
2. Install: `cd backend && pip install pytest pytest-asyncio`.
3. Create `tests/conftest.py` with:
   - Import `create_async_engine`, `async_sessionmaker` from SQLAlchemy.
   - Create a test database URL fixture using env var `TEST_DATABASE_URL` with default `postgresql+asyncpg://chrysopedia:changeme@localhost:5433/chrysopedia_test`.
   - `@pytest_asyncio.fixture` for `db_engine` that creates all tables via `Base.metadata.create_all` at setup and drops them at teardown (use `run_sync`).
   - `@pytest_asyncio.fixture` for `db_session` that yields an AsyncSession.
   - `@pytest_asyncio.fixture` for `client` that patches `get_session` dependency override on the FastAPI app and yields an `httpx.AsyncClient` with `ASGITransport`.
   - `@pytest.fixture` for `sample_transcript_path` returning `tests/fixtures/sample_transcript.json`.
   - `@pytest.fixture` for `tmp_transcript_dir` using `tmp_path` to override `transcript_storage_path`.
4. Create `tests/test_ingest.py` with `@pytest.mark.asyncio` tests:
   - `test_ingest_creates_creator_and_video`: POST sample_transcript.json → 200, response has video_id, creator_id, segments_stored=5, creator_name='Skope'. Query DB to confirm Creator with folder_name='Skope' and slug='skope' exists. Confirm SourceVideo with processing_status='transcribed' exists. Confirm 5 TranscriptSegment rows with segment_index 0-4.
   - `test_ingest_reuses_existing_creator`: Pre-create a Creator with folder_name='Skope'. POST transcript → response creator_id matches pre-created ID. Only 1 Creator row in DB.
   - `test_ingest_idempotent_reupload`: POST same transcript twice → second returns is_reupload=True, same video_id. Still only 5 segments (not 10). Only 1 SourceVideo row.
   - `test_ingest_saves_json_to_disk`: POST transcript → raw JSON file exists at the expected path in tmp_transcript_dir.
   - `test_ingest_rejects_invalid_json`: POST a file with invalid JSON → 400/422 error.
   - `test_ingest_rejects_missing_fields`: POST JSON without `creator_folder` → 400/422 error.
5. Add a `pytest.ini` or `pyproject.toml` section with `asyncio_mode = "auto"` to avoid per-test markers.

## Must-Haves

- [ ] pytest and pytest-asyncio installed and configured
- [ ] conftest.py with async DB fixtures and ASGI test client
- [ ] test_ingest_creates_creator_and_video passes
- [ ] test_ingest_reuses_existing_creator passes
- [ ] test_ingest_idempotent_reupload passes
- [ ] test_ingest_saves_json_to_disk passes
- [ ] test_ingest_rejects_invalid_json passes
- [ ] test_ingest_rejects_missing_fields passes
- [ ] All tests pass: `cd backend && python -m pytest tests/test_ingest.py -v`

## Failure Modes

| Dependency | On error | On timeout | On malformed response |
|------------|----------|-----------|----------------------|
| PostgreSQL (test DB) | Tests skip or fail with clear connection error | pytest-asyncio timeout | N/A |
| FastAPI test client | Test fails with assertion error | httpx timeout | Response schema mismatch caught by assertions |

## Verification

- `cd backend && python -m pytest tests/test_ingest.py -v` — all 6 tests pass
- `docker compose config` exits 0 (no regressions)

## Observability Impact

- Signals added/changed: test output showing pass/fail per test case with timing
- How a future agent inspects this: `cd backend && python -m pytest tests/test_ingest.py -v --tb=short`
- Failure state exposed: pytest output shows which assertion failed, with full diff
  - Estimate: 45m
  - Files: backend/requirements.txt, tests/conftest.py, tests/test_ingest.py, pytest.ini
  - Verify: cd backend && python -m pytest tests/test_ingest.py -v && docker compose config > /dev/null 2>&1
