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

# T01: Build reindex script with text extraction, formatting, and LightRAG submission

## Description

Create `backend/scripts/reindex_lightrag.py` — a standalone script that:
1. Connects to PostgreSQL using the sync engine pattern from pipeline/stages.py
2. Queries all technique pages with creator and key moment joins
3. Formats each page as a rich text document for LightRAG entity extraction
4. Submits documents via POST /documents/text with polling for pipeline completion
5. Supports resume (skips already-processed file_sources)
6. Logs progress (N/90)

## Failure Modes

| Dependency | On error | On timeout | On malformed response |
|------------|----------|-----------|----------------------|
| PostgreSQL | Exit with error message | Connection timeout → retry once | Log and skip page |
| LightRAG API | Log error, continue to next doc | Poll timeout (10min) → log warning, continue | Log raw response, skip doc |

## Load Profile

- **Shared resources**: PostgreSQL connection (single session), LightRAG API (serial)
- **Per-operation cost**: 1 DB query for all pages (eager load), 1 POST + N polls per page
- **10x breakpoint**: LightRAG serial processing is the bottleneck; DB is one-shot

## Steps

1. Read `backend/pipeline/stages.py` lines 224-245 to understand the sync engine pattern. Read `backend/models.py` for TechniquePage, KeyMoment, Creator model definitions.
2. Create `backend/scripts/reindex_lightrag.py` with:
   - Sync SQLAlchemy engine using the same `DATABASE_URL` → psycopg2 conversion pattern
   - `format_technique_page(page)` function that handles both v1 (flat dict) and v2 (nested list) `body_sections_format`
   - Document text format: `Technique: {title}\nCreator: {creator}\nCategory: {topic_category}\nTags: {tags}\nPlugins: {plugins}\n\nSummary: {summary}\n\n{sections}\n\nKey Moments from Source Videos:\n- {moment_title}: {moment_summary}`
   - `get_processed_sources(lightrag_url)` — calls GET /documents to get already-processed file_sources for resume
   - `submit_document(lightrag_url, text, file_source)` — POST /documents/text
   - `wait_for_pipeline(lightrag_url, timeout=600)` — poll GET /documents/pipeline_status every 10s until busy=false
   - `main()` with argparse: `--lightrag-url` (default http://chrysopedia-lightrag:9621), `--db-url` (from env DATABASE_URL), `--dry-run` (format and print without submitting), `--limit N` (process only first N pages for testing)
3. Handle v1 body_sections: iterate `dict.items()` → `heading: content` pairs
4. Handle v2 body_sections: iterate list of `{heading, content, subsections}` objects, flatten subsections
5. Add `--dry-run` mode that formats all pages and prints the first one fully, plus stats (total chars, page count)
6. Test by running via `ssh ub01 'docker exec chrysopedia-api python3 /app/scripts/reindex_lightrag.py --dry-run --limit 3'` to verify text extraction works for both v1 and v2 pages
7. Test submission with `--limit 2` (non-dry-run) to verify end-to-end: submit 2 new pages, poll for completion, check they appear in GET /documents/status_counts

## Must-Haves

- [ ] Script connects to PostgreSQL and queries all 90 technique pages with creator + key moments
- [ ] v1 body_sections (flat dict) correctly extracted to plain text
- [ ] v2 body_sections (nested list with subsections) correctly extracted to plain text
- [ ] Document format includes title, creator, category, tags, plugins, summary, sections, key moments
- [ ] file_source set to `technique:{slug}` for each document
- [ ] Resume: GET /documents checked and already-processed sources skipped
- [ ] Pipeline polling: waits for busy=false between submissions
- [ ] --dry-run and --limit flags work
- [ ] Progress logging: `[N/90] Submitted: {slug}` and `[N/90] Processed: {slug}`

## Verification

- `ssh ub01 'docker exec chrysopedia-api python3 /app/scripts/reindex_lightrag.py --dry-run --limit 3'` exits 0 and prints formatted text for 3 pages
- `ssh ub01 'curl -sf http://localhost:9621/documents/status_counts'` shows increase after `--limit 2` non-dry-run

## Inputs

- `backend/pipeline/stages.py` — sync engine pattern (lines 224-245)
- `backend/models.py` — TechniquePage, KeyMoment, Creator model definitions
- `backend/config.py` — DATABASE_URL configuration

## Expected Output

- `backend/scripts/reindex_lightrag.py` — complete reindex script

## Inputs

- ``backend/pipeline/stages.py` — sync engine pattern`
- ``backend/models.py` — TechniquePage, KeyMoment, Creator models`
- ``backend/config.py` — DATABASE_URL`

## Expected Output

- ``backend/scripts/reindex_lightrag.py` — complete reindex script with extraction, formatting, submission, polling, and resume`

## Verification

ssh ub01 'docker exec chrysopedia-api python3 /app/scripts/reindex_lightrag.py --dry-run --limit 3' exits 0 and prints formatted technique page text
