# S03: Data Model + Migration — UAT

**Milestone:** M014
**Written:** 2026-04-03T01:20:27.897Z

## UAT: S03 — Data Model + Migration

### Preconditions
- Chrysopedia stack running on ub01 (all containers healthy)
- Migration 012 already applied via `docker exec chrysopedia-api alembic upgrade head`
- At least one technique page exists in the database

### Test 1: Migration Applied Cleanly
1. SSH to ub01: `ssh ub01`
2. Check migration history: `docker exec chrysopedia-api alembic current`
3. **Expected:** Output includes revision for 012_multi_source_format (head)
4. Verify column exists: `docker exec chrysopedia-db psql -U chrysopedia -c "\d technique_pages" | grep body_sections_format`
5. **Expected:** `body_sections_format | character varying(20) | not null | ... | 'v1'`
6. Verify table exists: `docker exec chrysopedia-db psql -U chrysopedia -c "\d technique_page_videos"`
7. **Expected:** Table with columns: id (uuid), technique_page_id (uuid), source_video_id (uuid), added_at (timestamp)

### Test 2: Existing Pages Have v1 Default
1. Query: `docker exec chrysopedia-db psql -U chrysopedia -tAc "SELECT DISTINCT body_sections_format FROM technique_pages"`
2. **Expected:** Only `v1` returned (all existing rows defaulted)

### Test 3: API Response Includes New Fields
1. Get a slug: `SLUG=$(docker exec chrysopedia-db psql -U chrysopedia -tAc "SELECT slug FROM technique_pages LIMIT 1")`
2. Fetch detail: `curl -s http://ub01:8096/api/v1/techniques/$SLUG | python3 -m json.tool`
3. **Expected:** Response contains `"body_sections_format": "v1"` and `"source_videos": []`

### Test 4: Empty source_videos is Array Not Null
1. Same curl as Test 3
2. Parse: `curl -s http://ub01:8096/api/v1/techniques/$SLUG | python3 -c "import sys,json; d=json.load(sys.stdin); assert isinstance(d['source_videos'], list); assert len(d['source_videos']) == 0; print('OK')"`
3. **Expected:** Prints OK (empty array, not null or missing)

### Test 5: Unique Constraint on Association Table
1. Insert a test row: `docker exec chrysopedia-db psql -U chrysopedia -c "INSERT INTO technique_page_videos (id, technique_page_id, source_video_id) SELECT gen_random_uuid(), tp.id, sv.id FROM technique_pages tp, source_videos sv LIMIT 1"`
2. Repeat the same insert
3. **Expected:** Second insert fails with unique constraint violation (uq_page_video)
4. Cleanup: `docker exec chrysopedia-db psql -U chrysopedia -c "DELETE FROM technique_page_videos"`

### Test 6: CASCADE Delete Behavior
1. Note: This is destructive — use only on test data or verify constraint definition instead
2. Verify FK definitions: `docker exec chrysopedia-db psql -U chrysopedia -c "SELECT conname, confdeltype FROM pg_constraint WHERE conrelid = 'technique_page_videos'::regclass AND contype = 'f'"`
3. **Expected:** Both foreign keys show `confdeltype = 'c'` (CASCADE)

### Edge Cases
- **Migration downgrade:** `docker exec chrysopedia-api alembic downgrade -1` should drop technique_page_videos table and body_sections_format column cleanly (run only in test environment)
