Document Ingestion Pipeline
Ingestion is the unglamorous half of every retrieval system, and it is where the real failures start. If a document got mangled on the way in, no amount of clever querying gets the truth back out. The naive version is a script: loop over files, chunk, embed, insert. It works exactly once. Run it again and every chunk exists twice, search returns duplicates, and you paid the embedding bill twice for the privilege.
The property that separates a pipeline from a script is idempotency: run it on the same folder five times and the result is identical to running it once. You get there by making the pipeline check before it works. Every source file gets a content hash; unchanged hash means skip, changed hash means the old chunks die and new ones replace them, in one transaction, so no query ever sees half a document. This also gives you the thing scripts never have: a table you can query to ask "what is actually in my index, and when did it get there."
The other discipline is treating ingestion as a state machine rather than a loop. Each document moves through discovered, parsed, chunked, embedded, indexed, or lands in failed with an error message attached. When file 412 of 500 is a corrupt PDF, it fails alone and visibly; the other 499 proceed, and the pipeline can resume from wherever it stopped without redoing finished work. A loop that dies at 412 and restarts from zero re-embeds 411 documents to get one file further, and at API prices that habit shows up on your invoice (§4.6).
Prerequisites
- Postgres with pgvector, with a chunks table (the pgvector-rag template's schema is assumed; this spec extends its `documents` table).
- An embeddings API key in an environment variable.
- The document set, and a note of which formats are in it: the parser list below must match reality.
# Ingestion pipeline spec: [project name]
Build an idempotent pipeline: [source folder / bucket] → parsed text →
chunks → embeddings → indexed rows. Resumable after any failure. Safe to
re-run on the same input, always, by design.
## Source of truth
- Input: [./corpus, or s3://bucket/prefix]
- Formats to support: [.md, .pdf, .html, .docx. List only what exists;
every parser is a liability you maintain]
- Identity of a document: its [path / URI]. Content hash (sha256 of raw
bytes) decides whether it changed.
## State: extend the documents table
```sql
ALTER TABLE documents
ADD COLUMN status text NOT NULL DEFAULT 'discovered'
CHECK (status IN ('discovered','parsed','chunked','embedded','indexed','failed')),
ADD COLUMN error text, -- populated only when status = 'failed'
ADD COLUMN chunk_count int,
ADD COLUMN ingested_at timestamptz;
```
This table IS the pipeline's memory. Progress, failures, and "what's in
the index" are all one SELECT away. No state in script variables.
## Stages (each independently resumable)
1. DISCOVER. Walk the source. For each file: compute sha256.
- New URI → insert row, status 'discovered'.
- Known URI, same hash → SKIP ENTIRELY. This one comparison is what
makes re-runs free instead of a full re-bill.
- Known URI, new hash → mark 'discovered' with the new hash (stage 4
handles replacing its chunks).
- Known URI missing from source → see Deletions below.
2. PARSE → plain text. Per-format parsers; strip boilerplate [nav, headers,
footers]. A document whose extracted text is under [50] chars is
'failed' with reason "no text extracted", not silently indexed as junk.
3. CHUNK. [~500 tokens, 50 overlap, paragraph boundaries first]. Store
chunk_index. Prepend the document title to each chunk's text: chunks
travel alone at query time, and an untitled fragment loses its context.
4. EMBED + INDEX. Batch [64] chunks per API call. Per document, in ONE
transaction: delete existing chunks, insert new ones, set status
'indexed', set ingested_at. The transaction is the idempotency: a crash
mid-document leaves the old version intact and queryable, never a mix.
## Failure policy
- API errors: retry [3] times, exponential backoff. Then mark the DOCUMENT
'failed' with the error text and MOVE ON. One bad file must never stop
the run; one bad file stopping the run is the classic ingestion bug.
- Exit report: counts per status, plus every failed URI and its reason.
- `--retry-failed` flag: re-attempt failed documents only.
## Deletions
Files gone from the source get status ['indexed' kept but excluded /
chunks deleted. Decide now]. Default: delete their chunks. Retrieval
serving chunks of documents someone deliberately removed is how a RAG
system quotes the policy you retired last quarter.
## Cost guard
Before embedding, print: documents to process, estimated tokens, estimated
cost at [$X per 1M tokens]. If estimate exceeds [$Y], stop and ask me.
A re-run that should cost $0 estimating $40 means the skip logic broke;
this guard catches that bug before the invoice does.
## CLI contract
- `ingest run`: full pass, idempotent
- `ingest run --dry-run`: discovery + report only; nothing written, no API calls
- `ingest status`: counts per status, most recent failures
- `ingest retry-failed`
- `ingest reindex --all`: force re-embed (embedding model changed). Asks
for confirmation and shows the cost estimate first.
## Acceptance tests (write these, run them)
- [ ] Run twice on identical input: second run embeds 0 chunks, spends $0
- [ ] Modify one file, run: exactly that document's chunks are replaced
- [ ] Corrupt one PDF, run: it lands in 'failed' with a reason, others index
- [ ] Kill the process mid-run, run again: completes; no duplicate chunks
(assert with a UNIQUE (document_id, chunk_index) violation check)
- [ ] Delete a source file, run: its chunks no longer appear in retrievalAdaptation notes:
- Source is a website instead of a folder: discovery becomes a crawl, identity becomes the normalized URL, and hash still decides change. Add politeness (rate limit, robots.txt) or the site you're indexing will notice you the wrong way.
- Continuous ingestion (watch folder, webhook on upload) is the same machine triggered per document instead of per run. Because every stage is idempotent, duplicate webhook deliveries cost nothing, which is the point of building it this way first.
- PDFs deserve their own paranoia: scanned pages parse to empty text or garbage. The minimum-length check catches empty; for garbage, sample a few parsed outputs by eye before trusting a new corpus at scale.
- The mistake: skipping the mid-run kill test because it feels contrived. It isn't. Laptops sleep, API keys expire mid-batch, deploys restart workers. Resumability is the difference between "run it again" and an evening of manual duplicate cleanup with SQL you're writing angry.
- If a document's chunk count changes between versions, the transactional delete-then-insert in stage 4 already handles it. Resist any "update chunks in place" cleverness; replacement is simpler and correct, and clever is where the duplicates come from.