RAG Data Modeling and Ingestion Pipelines

A reliable RAG ingestion system turns an authorized source version into parsed content, semantically bounded chunks, lexical representations, embeddings, and a published retrieval projection. Each stage is versioned, idempotent, observable, and reversible. New versions become visible atomically only after validation; deleted or revoked sources stop retrieval promptly across every derived copy.

What will you be able to do?

• Model documents, versions, access, chunks, embeddings, and ingestion runs.
• Design idempotent staged ingestion and atomic publication.
• Preserve citation coordinates and source checksums.
• Rebuild or delete projections without losing canonical truth.

Prerequisites and mental model

Separate control plane from data plane:

Tables commonly include documents, document_versions, document_acl, ingestion_runs, chunks, embedding_models, and chunk_embeddings. Chunk identity must be stable within a version; content checksum supports idempotency.

How should publication work?

1. Register immutable source version and checksum.
2. Create an ingestion run with parser/chunker/model versions.
3. Parse and chunk in bounded retryable jobs.
4. Store chunk text, ordinal, section/page/offset coordinates, token estimate, and checksum.
5. Generate lexical vector and embedding with per-item status.
6. Validate counts, coverage, dimensions, ACL, citations, and sample retrieval.
7. In a short transaction, mark the complete version active and previous active version superseded.
8. Retire old projections later according to rollback/retention.

Avoid a transaction across parsing/model calls. Unique stage keys make retries idempotent.

Why is this AI-native?

The ingestion pipeline is as important as the chat prompt. A response can be grounded only if source/version, access, chunk boundaries, model, and publication state are reliable. Store failure reason and partial progress without making incomplete content retrievable.

Prove it worked

Run the same source/version twice and prove no duplicate active chunks. Change the source checksum and prove a new version is created. Inject failure halfway and prove no partial version is published. Revoke access and measure time until every retrieval path excludes it.

Production judgment

• Parsing untrusted files needs sandboxing, size/type limits, malware/content controls, and timeouts.
• Token/chunk estimates are model-specific.
• Exactly-once external processing is unrealistic; design at-least-once with idempotent writes.
• Rebuild capacity and provider rate limits affect recovery time.

Common mistakes

• Partial document retrieved: rows visible before validation → stage then atomically publish.
• Duplicate chunks after retry: no unique idempotency key → key by version/chunker/chunk identity.
• Citation cannot open source: coordinates/version absent → preserve resolvable lineage.

AI Pair-Programmer Prompt

Hands-on exercise

Write the relational table contract and state machine for one document ingestion, including failed and superseded states.

Acceptance criterion: incomplete rows cannot be retrieved, retries do not duplicate, citations resolve, and previous version can remain live until cutover.

Knowledge check

1. Why publish atomically?
2. What makes ingestion retry-safe?
3. Why keep old versions temporarily?

---

Answers

1. Users must not retrieve partially parsed/embedded content.
2. Stable stage identities, uniqueness, checksums, and idempotent state transitions.
3. For rollback, citation history, evaluation comparison, or retention policy.

Completion checklist

• Source and every derived stage are versioned.
• Partial work is never active.
• Retry, revoke, delete, and rebuild are tested.
• Citations have exact source coordinates.

Primary references

• PostgreSQL full-text search
• pgvector
• PostgreSQL transactions