# S04 Research: Reindex Existing Corpus Through LightRAG

## Summary

Straightforward data pipeline task: extract text from PostgreSQL (technique pages + key moments), format it for LightRAG's entity extraction, and submit via the REST API. The main risks are throughput (LightRAG processes one job at a time with LLM-powered entity extraction) and content formatting (maximizing entity/relationship yield from the text).

## Recommendation

Build a standalone Python reindex script (`backend/scripts/reindex_lightrag.py`) that:
1. Queries all technique pages with their key moments and creator info from PostgreSQL
2. Formats each technique page as a rich text document (title, creator, category, summary, body sections, key moment summaries)
3. Submits documents one at a time via `POST /documents/text` with polling for pipeline completion between submissions
4. Tracks progress with logging and supports resume (skip already-indexed documents)

Index at technique page granularity (90 documents), not key moment granularity (1378). Each technique page document includes its key moment summaries inline, giving LightRAG the full context for entity extraction. This matches the knowledge graph's natural unit — a technique is the entity, not an individual moment.

## Implementation Landscape

### Content Inventory

| Entity | Count | Total Chars | Avg Chars |
|--------|-------|-------------|-----------|
| Technique pages (body) | 90 | 430,702 | 4,786 |
| Technique pages (summary) | 90 | 41,347 | 459 |
| Key moments (summary) | 1,378 | 543,978 | 395 |
| Creators | 18 | — | — |
| Source videos | 383 | — | — |

### LightRAG API

- **Insert single:** `POST /documents/text` — `{text: string, file_source?: string}`
- **Insert batch:** `POST /documents/texts` — `{texts: string[], file_sources?: string[]}`
- **Pipeline status:** `GET /documents/pipeline_status` — returns `{busy: bool, ...}`
- **Document list:** `GET /documents` — returns `{statuses: {processed: [...], ...}}`
- **Status counts:** `GET /documents/status_counts`

**Critical constraint:** Pipeline is serial — only one job processes at a time (`busy` flag). Each document triggers LLM calls to DGX Sparks for entity extraction. The test document (336 chars) took ~2 minutes to process. Estimated time for 90 technique pages (avg 5,200 chars each with key moments): **3-6 hours**.

### Body Sections Formats

Two formats coexist in the database:
- **v1 (11 pages):** Flat JSON dict — keys are section headings, values are prose strings
- **v2 (79 pages):** JSON array of objects — `{heading, content, subsections: [{heading, content}]}`

The reindex script must handle both formats to extract plain text.

### LightRAG Current State

- 2 documents indexed (1 processed, 1 processing from accidental test insert)
- 10 entities from test doc: Copycatt, Kick Drum, LFO Modulation, Mid-Range Reese Bass, OTT, Serum, Sidechain Compression, Sub Bass, Valhalla Room, Wavetable Position
- Entity types configured: Creator, Technique, Plugin, Synthesizer, Effect, Genre, DAW, SamplePack, SignalChain, Concept, Frequency, SoundDesignElement

### Database Access

The API container uses async SQLAlchemy (asyncpg). The reindex script should use **sync psycopg2** directly (or the sync SQLAlchemy engine from pipeline stages) to avoid async complexity. Connect to `chrysopedia-db:5432` from within Docker network, or `ub01:5433` from outside.

### Document Text Formatting Strategy

For each technique page, compose a text document like:

```
Technique: {title}
Creator: {creator_name}
Category: {topic_category}
Tags: {comma-separated topic_tags}
Plugins: {comma-separated plugins}

Summary: {summary}

{section_heading}
{section_content}

[repeat for all sections]

Key Moments from Source Videos:
- {moment_title}: {moment_summary}
[repeat for all key moments linked to this technique page]
```

This format gives LightRAG maximum context for extracting entities (Creator, Plugin, Technique, Effect, etc.) and relationships between them.

### File Source Naming

Use `file_source` parameter as `technique:{slug}` (e.g., `technique:parallel-stereo-processing-keota`). This enables checking the documents list for already-indexed pages (resume support).

## Natural Task Seams

### T01: Build reindex script with text extraction and formatting
- Query technique pages + joins (creator, key moments)
- Handle v1 and v2 body_sections formats
- Format as rich text documents
- Local verification: run against DB, check output text for a sample page

### T02: Add LightRAG submission with progress tracking and resume
- Submit documents via `POST /documents/text`
- Poll `GET /documents/pipeline_status` for completion between submissions
- Track progress via logging (N/90 complete)
- Resume support: check `GET /documents` for already-processed file_sources
- Run the full reindex on ub01

### T03: Verify graph quality and query results
- Check entity/relationship counts via graph API
- Run sample queries covering different topic categories
- Verify key entities are present (all 18 creators, major plugins like Serum/OTT/Vital, technique types)
- Document the graph state for S06 (Forgejo KB update)

## Constraints and Risks

1. **Throughput:** ~3-6 hours for 90 documents. Script must be robust to interruption and support resume.
2. **LLM rate limits:** DGX Sparks may throttle under sustained load. The `max_async: 4` setting in LightRAG limits concurrent LLM calls.
3. **Memory:** Large technique pages (up to 16K chars) shouldn't be a problem for LightRAG's chunking, but monitor for failures on the largest pages.
4. **Existing test data:** Two documents already in LightRAG from S01 testing. The script should handle these gracefully (skip or note them).
5. **No v2 body section format:** Script must correctly flatten nested subsections from v2 format into plain text.

## Verification Approach

1. Script runs without error against real DB on ub01
2. `GET /documents/status_counts` shows 90+ processed documents
3. `GET /graph/label/list` shows entities from all 18 creators
4. `POST /query` with music production questions returns relevant results citing multiple creators and techniques
5. All existing Chrysopedia services remain healthy during and after reindex
