HorizonRAG

About us

What did you build? (and why)

We built HorizonRAG, a document Q&A system where the entire AI data pipeline — embedding, DiskANN vector search, and grounded answer generation — runs inside Azure HorizonDB (the PostgreSQL team's new managed Postgres) via the azure_ai extension. The app is a thin client; the database does the AI work.

We built it to push HorizonDB beyond "store and search vectors" into a self-maintaining, AI-native data layer, and we focused on two capabilities that data teams actually struggle with:

• Version control for data. Most RAG systems treat documents as disposable — edit in place and the old state (and its embeddings) is gone. We added document versioning inside the database: every save creates an immutable version snapshot, and a trigger archives the affected chunks in the same transaction as the edit. On save or rollback we re-embed only the chunks that actually changed (via a chunk_diff SQL function), so history is complete but embedding cost stays low — giving you Git-like history, a timeline slider, and full rollback for your knowledge base.
• Personalization via a preference model. Instead of relying solely on Azure OpenAI to generate one generic answer, we layer a preference model over retrieval and generation, so answers can be tailored to a user's role, reading level, or prior choices rather than being one-size-fits-all. The model stays swappable, so the source of truth remains in HorizonDB while who the answer is for becomes a tunable dimension.

The original guarantee still holds and underpins all of this: when a document's text changes, the DB regenerates its embedding in the same transaction, so text and vectors never drift. Versioning and personalization both build on that consistency backbone.

Who is it for?

• The Azure HorizonDB / PostgreSQL product team first — this is a concrete showcase (and stress test) of HorizonDB's in-database AI story: azure_ai, vector search, DiskANN, and triggers doing real RAG work, plus a roadmap of features (versioning, preference-based personalization) that make HorizonDB more compelling than a bolt-on vector store.
• Developers and teams building document Q&A, knowledge bases, or compliance/policy systems on Postgres who need answers that are always current, auditable (version history), and tailored to the reader.

What is the business value?

• A stronger HorizonDB: We turn HorizonDB from a vector store into a self-maintaining, versioned, personalized RAG engine — differentiating features that help the PostgreSQL team win RAG workloads against bolt-on vector databases.
• Auditability and trust (versioning): Immutable version history plus rollback means no data loss, a full audit trail, and the ability to answer "what did this document say last week?" — essential for regulated, policy, and enterprise content.
• Lower AI cost (delta re-embedding): Re-embedding only changed chunks instead of whole documents directly cuts embedding spend; the design even logs chunks_reembedded per save so savings are measurable.
• Higher relevance (personalization): A preference model means the same grounded facts can be delivered in the most useful form per user, improving satisfaction and adoption without sacrificing the single source of truth.
• Simpler architecture: Embedding, search, generation, and version history all live in one database — no separate sync pipeline, index job, or external store to operate.
• Enterprise-ready posture: Parameterized SQL only, validation at the boundary, structured { error } responses, and strict secret handling.

Why would the customer use or need this?

Teams putting AI on their own data hit three walls: drift (the AI answers from a stale embedding), no history (an edit silently destroys the previous truth, with no audit trail or undo), and generic answers (one response for every user). HorizonRAG addresses all three inside HorizonDB: re-embedding is tied to the data change so answers stay current; versioning makes every change reversible and auditable; and the preference model tailors answers per user. Our demo makes it tangible — edit a document, save, and the answer correctly flips (e.g., capital "Avalon" → "Brightport") with a "Source of truth" citation, with no re-indexing and no sync job, because the chunks were re-embedded in the same transaction.

What are the next steps?

• Ship document versioning end to end (already fully designed with a 3-task plan):
  • DB: document_versions + chunk_versions tables, current_version_id, the chunk_diff function, the in-transaction versioning trigger, and 30-day retention that always keeps the latest version (infra/migrations/0002_versioning.sql).
  • API: save, list versions, get version, and rollback endpoints with selective re-embedding (versions.js, versionControl.js).
  • UI: a timeline slider, read-only version view, and one-click restore (VersionTimeline.tsx, VersionView.tsx).
• Mature the preference model: persist per-user preferences in HorizonDB, let it re-rank retrieved chunks and shape generation, and measure relevance lift against the Azure OpenAI-only baseline (keeping the model swappable).
• Feed HorizonDB product learnings back to the PostgreSQL team: delta re-embedding cost data, trigger atomicity behavior, and DiskANN performance on versioned corpora — evidence for where HorizonDB shines and where it can improve.
• Production hardening: scale testing on larger corpora, a multi-user merge strategy (today is last-write-wins), and richer cost dashboards from the chunks_reembedded logs.

Quick start — full local stack (API + web)

Run the real backend + frontend against HorizonDB with two npm scripts (PowerShell required):

npm run all          # first time: setup + start in one go

Or run the steps separately:

npm run setup        # install deps for app/server & app/web, verify DB credentials
npm run start:local  # start the API (:3001) and web (:5173); Ctrl+C stops both

Then open http://localhost:5173.

Credentials are read at runtime from the first of these that exists (all git-ignored — never commit secrets):

• app/server/.env (developer overrides — wins)
• infra/.env.local (created by infra/provision*.ps1)
• .env.team (shared team credentials at repo root)

If none exist, npm run setup stops and tells you which file to provide. Useful flags: npm run setup -- -Force reinstalls deps even if node_modules exists.

The scripts hardcode no secrets, so any teammate with their own env file can run them.

Planning & Design

Upcoming feature: Document versioning. See .github/VERSION_CONTROL_DESIGN.md for full design, schema, and 3-task implementation plan. Task breakdown, GitHub issues, and dependencies are in:

• tasks/version-control-planning.md — overview + task dependency diagram
• .github/ISSUE_TEMPLATE_1_DB.md — DB schema & migration task
• .github/ISSUE_TEMPLATE_2_API.md — Backend API routes task
• .github/ISSUE_TEMPLATE_3_UI.md — Frontend timeline slider task

---

A polished, frontend-only demo of the RAG document lifecycle:

Open → Edit → Save → Re-embedding… → ✅ Re-embedded → Ask AI → answer changes

No backend required. All I/O is mocked with timeouts behind a swappable service layer.

Run (frontend-only demo)

npm install
npm run dev      # http://localhost:5173
npm run build    # type-check + production build

Demo script (≈30s)

1. On the Live Document tab, click the code window and change Supports Feature A → Supports Feature B. Note the ● 1 change pill and the red/green diff with the CHANGED badge.
2. Click Save → the button shows Saving… then Updating… as the stage moves through saving → reembedding, and the view switches to the AI Agent tab.
3. The bottom process pipeline lights up stage-by-stage: Edit → Save → Re-embed → Ask again → Answer changed.
4. On the AI Agent tab, ask "What feature does Azure PostgreSQL support?" again → the answer is now Feature B, flagged with an Answer updated badge and a "Source of truth" citation.

Architecture

src/
  App.tsx       # Single-file demo: state machine + all inline components
  types.ts      # Shared types (currently unused by App.tsx)
  App.css       # All styling (Microsoft Fabric teal/green theme)
  main.tsx      # React entry point

App.tsx is self-contained. It defines the orchestrating App component plus these inline sub-components:

• LiveDocument — click-to-edit code window with a red/green diff of the Feature A → B change and a Save button.
• AIChatbot — free-text chat (Enter / Send) with a "Source of truth" citation card and confidence bars.
• BottomPipeline — the 5-step process strip (Edit → Save → Re-embed → Ask again → Answer changed) that highlights the current stage.

The top tab row holds the HORIZON RAG and MICROSOFT ASCII art on either side of the Live Document / AI Agent tab buttons.

The components/ folder (DocumentEditor.tsx, SaveStatus.tsx, ReEmbeddingStatus.tsx, AiChatPanel.tsx, Toast.tsx) and services/mockApi.ts are not currently wired into App.tsx. They remain as a reference for splitting the monolith back out / swapping in real APIs.

State management

A single Stage state machine drives the whole UX:

idle → editing → saving → reembedding → ready → asking → answered

(plus an error stage for demo-safe recovery). Key state in App:

• documentText — the editable source; hasChanges = documentText !== INITIAL_DOC.
• messages — the chat transcript.
• activeTab — "document" | "agent"; Save auto-switches to the AI Agent tab.

The answer is derived from the current documentText via buildAnswer() only when you re-ask after saving — so the answer flips from Feature A to Feature B only once the full Save + Re-embed cycle has run, mirroring real RAG behavior. Mock timing is inline via sleep() (no external service layer).

Swapping in real APIs

App.tsx currently inlines its mock timing via sleep(). To go real, replace the bodies of handleSave() and handleAskAgain() (or extract them into src/services/mockApi.ts and import) — keep the same shapes the UI expects:

// before (mock, inside handleSave)
setStage("saving");
await sleep(750);
setStage("reembedding");
await sleep(1200);

// after (real) — UI logic untouched
setStage("saving");
await fetch(`/api/documents/${id}`, { method: "PUT", body: documentText });
setStage("reembedding");
await fetch(`/api/documents/${id}/embed`, { method: "POST" });

Mock step (in App.tsx)	Real endpoint (suggested)
handleSave → saving	PUT /api/documents/:id
handleSave → reembedding	POST /api/documents/:id/embed
handleAskAgain → buildAnswer	POST /api/chat (RAG retrieval + LLM)

Recommended UX improvements for a live demo

• Speed knobs: expose the delay() durations so you can shorten them on stage.
• Embedding progress bar: swap the spinner for a determinate bar if the real embed API streams progress (chunks done / total).
• Diff highlight: briefly highlight what changed in the document after save.
• "Stale answer" warning: if the user asks while isDirty, badge the answer as "based on the previously embedded version."
• Optimistic + retry: show an error toast with a Retry action if a real call fails.
• Source citations: the chat already shows the retrieved snippet — wire it to scroll/highlight the matching line in the editor for a stronger "grounding" story.
• Keyboard: Enter asks the question (already wired); add Ctrl/Cmd+S to save.