Capstone 3: Build a Governed PostgreSQL RAG Assistant

Build the complete AI-native layer for SignalDesk: versioned documents, ACLs, staged ingestion, full-text and pgvector retrieval, rank fusion, citations, scoped memory, a safe ticket-summary tool, human approval for sending replies, evaluation datasets, traces, budgets, fallback, re-embedding migration, deletion propagation, restore, and adversarial tenant testing.

What will you be able to do?

• Operate a RAG pipeline as a governed data product.
• Measure retrieval, generation, authorization, latency, and cost separately.
• Expose bounded semantic tools with approval and idempotency.
• Rebuild and recover AI projections from canonical truth.

Prerequisites and mental model

Complete the whole book and Capstone 2. The deliverable is not a chatbot UI. It is evidence that an authorized question receives supported context and a safe answer under normal, stale, malicious, failure, and recovery conditions.

Required system

1. Immutable document versions, ACLs, checksums, and staged states.
2. Idempotent parse/chunk/embed jobs with exact citation coordinates.
3. pgvector exact baseline and evaluated approximate index where justified.
4. PostgreSQL full-text retrieval and versioned fusion/rerank settings.
5. Policy-before-ranking tenant/group/document authorization.
6. Bounded context, no-answer behavior, and claim-support citations.
7. Scoped conversation memory with retention and deletion.
8. Semantic summarize_ticket read tool and send_reply proposal/approval/execution.
9. Thirty-plus evaluation cases covering exact, semantic, stale, unauthorized, malicious, and no-answer inputs.
10. Stage traces, SLOs, cost units, drift monitoring, canary rollout, and rollback.
11. Embedding-model migration with dual versions.
12. Restore canonical state, rebuild derived data, and prove recovery time.

Adversarial tests

• document prompt injection asks for another tenant;
• user asks to reveal system/catalog secrets;
• revoked document remains in cache;
• approximate index returns too few filtered candidates;
• model invents citation or tool parameter;
• approval is replayed after ticket changes;
• embedding provider fails halfway;
• model is unavailable while ticket operations continue;
• deletion request removes source, chunks, embeddings, memory, cache, and governed traces.

Prove it worked: rubric

Authorization is never averaged into a quality score.

Common mistakes

• Great demo, no dataset: no reproducible quality → version cases and baselines.
• RAG uses latest rows blindly: publication/version state ignored → retrieve active validated versions.
• Model can send directly: proposal and authority conflated → approval state machine.

AI Pair-Programmer Prompt

Hands-on exercise

Run the full adversarial suite against a candidate release and compare it to the baseline.

Acceptance criterion: all hard gates pass, quality improvements are measured with variance, and rollback is demonstrated.

Knowledge check

1. What is the first hard gate?
2. Why keep exact vector search?
3. What should recovery rebuild rather than restore as truth?

---

Answers

1. Authorization/isolation with zero leakage.
2. It provides an approximate-recall baseline and diagnostic truth.
3. Chunks/search vectors/embeddings and other derived projections, from canonical sources and lineage.

Completion checklist

• Every required system component exists.
• Adversarial tests pass with evidence.
• Dataset, configuration, and results are versioned.
• Restore and rebuild meet declared RPO/RTO.

Primary references

• PostgreSQL full-text search
• pgvector
• PostgreSQL row security