---
estimated_steps: 41
estimated_files: 4
skills_used: []
---

# T01: Build transcript ingestion endpoint with creator auto-detection and idempotent upsert

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

## Inputs

- ``backend/models.py` — Creator, SourceVideo, TranscriptSegment ORM models with column definitions`
- ``backend/database.py` — get_session async dependency for DB access`
- ``backend/config.py` — Settings with transcript_storage_path`
- ``backend/schemas.py` — existing Pydantic schema patterns (Base/Create/Read convention)`
- ``backend/routers/creators.py` — existing router pattern to follow (APIRouter, Depends, logging)`
- ``backend/main.py` — existing router mounting pattern`
- ``tests/fixtures/sample_transcript.json` — reference for expected JSON shape`

## Expected Output

- ``backend/routers/ingest.py` — new ingestion endpoint router with POST handler`
- ``backend/schemas.py` — modified with TranscriptIngestResponse schema`
- ``backend/requirements.txt` — modified with python-multipart dependency`
- ``backend/main.py` — modified to mount ingest router`

## Verification

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
