[Feat]: knowledge-base agent (PDF/PNG/URL → markdown → RAG → graphRAG)

[FireTable]

Feature: knowledge-base agent (PDF/PNG/URL → markdown → RAG → graphRAG)

Now that users can upload attachments (see #12), the next step is to make those attachments searchable. This issue is for the knowledge-base pipeline that ingests uploaded files, converts them to clean markdown, runs RAG + graph-RAG over the chunks, and exposes a tool-call surface so the chat agent can ground its answers.

Relates to #12 — the KB is a downstream consumer of the R2 attachments. #12 ships the storage half; this issue ships the ingestion + retrieval half.

Goals

1. Ingestion pipeline: take an attachment row from [Feat]: chat attachments backed by Cloudflare R2 #12 (or a fresh URL), run it through PDF/OCR → markdown → chunks → embeddings → graph edges, and persist everything in Postgres.
2. Hybrid search: combine BM25 (Postgres tsvector) + vector similarity (pgvector) via Reciprocal Rank Fusion. Pure vector or pure BM25 is not enough; we want both.
3. Graph RAG: extract entities + relationships during ingestion, store an explicit edge graph in Postgres (no separate Neo4j). Retrieval should be able to traverse the graph starting from chunk hits.
4. Tool-call surface: the chat agent gets new tools (search_kb, search_graph, list_documents) that query the KB. Lazy-registered per rule [Bug]: Memory tab Socials row missing email per linked provider #10.
5. Reuse community standards at every stage — don't write a PDF parser, don't write a chunker, don't write a graph extractor. Ship wiring code only.

Pipeline overview

[attachment row from #12 / new URL]
        │
        ▼
(1) Source fetch       ← R2 GET for uploaded files; HTTP fetch for URLs
        │
        ▼
(2) Format detection   ← content-type + magic bytes (libmagic)
        │
        ▼
(3) Clean → Markdown   ← Marker (PDF, esp. with images/formulas) / Turndown (HTML) /
        │                 plain read (txt, md); OCR via PaddleOCR / Tesseract for scans
        ▼
(4) Chunk              ← semantic / recursive chunker; preserves heading + page boundaries
        │
        ▼
(5) Embed              ← OpenAI text-embedding-3-small (or BGE-M3 self-hosted)
        │
        ▼
(6) Graph extract      ← LightRAG or Microsoft GraphRAG entity/relation extraction;
        │                 OR a lighter prompt-based extractor for low-volume docs
        ▼
(7) Persist            ← kb_documents / kb_chunks (pgvector) / kb_edges / kb_entities
        │
        ▼
(8) Background re-index ← nightly job recomputes stale chunks (not part of MVP, separate issue)

Steps (1)-(7) run as a background job triggered by the upload webhook from #12, so the chat UI never blocks on ingestion. Progress is observable in the Memory tab (or a new "Knowledge" tab — design decision).

Stage-by-stage — community-standard picks

(3) Clean → Markdown

This is the highest-friction stage; PDF parsing with images + formulas is notoriously bad if you roll your own. Community standard in 2026:

Tool	What it does well	Why pick it
marker by DataLab	PDF → markdown with formulas (LaTeX preserved), images, tables	Fastest + best accuracy for academic / mixed PDFs. PyTorch, GPU optional, CPU works.
docling by IBM	PDF/DOCX/PPTX → structured markdown, deep table understanding	Strong on enterprise layouts (invoices, reports). Heavier than marker.
pymupdf4llm	Fast PDF → markdown via PyMuPDF; loses some structure	Good for trivial PDFs (text-only). Not enough for scanned or formula-heavy.
marker-pdf (newer name)	Same as marker	Track under the same project.

Pick: marker as the default; fall back to docling for documents where marker chokes (tables with merged cells). Don't write our own. Both are MIT/Apache.

OCR for scanned PDFs / images

The project is TS-driven, but the PDF/OCR ecosystem is Python. Three options:

Option	Pros	Cons
A. Sidecar Python service in docker-compose.yml	Run marker + PaddleOCR as a separate container, expose a small HTTP API. Most flexible.	One more service to operate.
B. Deno Deploy Sandbox for execute_code (already wired)	Already have it via #10 rule; can we run Python in it?	Deno Deploy Sandbox is TS-first; Python requires their Python sandbox beta — verify it handles PyTorch / heavy ML libs.
C. Hosted API (e.g. Mistral OCR, AWS Textract)	No infra.	Cost + external dependency, breaks self-host-first stance.

Recommendation: option A — a tiny Python sidecar (backend/kb/) running marker + a thin FastAPI. PyTorch image is heavy but well-known; Caddy / start.sh already manage multiple services, so adding one more is small. Mounted volume for any model weights.

(4) Chunking

Tool	Notes
chonkie	Modern, very fast, semantic + recursive chunkers. Py-first.
LangChain RecursiveCharacterTextSplitter (TS)	Available today in our TS stack.
LlamaIndex SentenceSplitter	Py; ships with full LlamaIndex pipeline.

Pick: chonkie for its semantic chunker (preserves heading hierarchy from marker output). The TS LangChain splitter is fine as a fallback for pure-text inputs.

(5) Embeddings

Hosted: OpenAI text-embedding-3-small (1536 dims, cheap). Self-host: BGE-M3 via Hugging Face TEI.

Recommendation: hosted OpenAI for MVP (we already depend on it for the chat model — one fewer moving part). BGE-M3 swap is a config change later.

(6) Graph RAG

This is where it's easy to over-engineer.

Tool	Notes
Microsoft GraphRAG	Canonical; entity + community detection; LLM-heavy.
LightRAG	Lighter, incremental, similar API surface.
LlamaIndex PropertyGraphIndex (Py)	Integrates with the LlamaIndex pipeline.
nano-graphrag	Tiny single-file implementation; educational, not production.

Pick: LightRAG for the MVP. Microsoft GraphRAG's community detection is overkill until we know our corpus shape. LightRAG also handles incremental updates, which we need because new uploads arrive continuously.

Entity + edge extraction happens during ingestion (LLM call per chunk batch), then persisted to kb_entities + kb_edges. Retrieval uses the resulting graph to expand a query's neighborhood.

Vector + BM25 store

Already running Postgres 16. Add:

• pgvector extension — vector similarity (<=> operator).
• Built-in tsvector + pg_trgm — BM25-style full-text + fuzzy. Don't pull in a separate search engine.

Reciprocal Rank Fusion (RRF) in a single SQL query or in TS — pick SQL so the SQL stays the source of truth for ranking.

DB schema additions

All under lib/kb/schema.ts (mirrors lib/memory/, lib/threads/). New tables:

// One row per ingested source.
export const kbDocument = pgTable("kb_document", {
  id: text("id").primaryKey(),
  userId: text("user_id").notNull().references(() => user.id, { onDelete: "cascade" }),
  // FK to attachment.id (#12) when ingested from an upload; null for URL-sourced.
  attachmentId: text("attachment_id").references(() => attachment.id, { onDelete: "set null" }),
  // Source identity — null when attachmentId is set.
  sourceUrl: text("source_url"),
  title: text("title").notNull(),
  contentType: text("content_type").notNull(),
  markdown: text("markdown").notNull(),         // cleaned output from (3)
  status: text("status").notNull(),             // 'pending' | 'ingesting' | 'ready' | 'failed'
  errorMessage: text("error_message"),
  createdAt: timestamp("created_at").defaultNow().notNull(),
  updatedAt: timestamp("updated_at").defaultNow().notNull(),
}, (t) => [index("kb_document_user_idx").on(t.userId)]);

// Chunks with their embedding + tsvector.
export const kbChunk = pgTable("kb_chunk", {
  id: text("id").primaryKey(),
  documentId: text("document_id").notNull().references(() => kbDocument.id, { onDelete: "cascade" }),
  ordinal: integer("ordinal").notNull(),         // order within the document
  content: text("content").notNull(),
  embedding: vector("embedding", { dimensions: 1536 }),   // pgvector
  tsv: generatedAlwaysAs(tsvector("content")),            // Postgres generated tsvector
  createdAt: timestamp("created_at").defaultNow().notNull(),
}, (t) => [
  index("kb_chunk_document_idx").on(t.documentId),
  index("kb_chunk_embedding_idx").using("hnsw", t.embedding.op("vector_cosine_ops")),  // pgvector HNSW
  index("kb_chunk_tsv_idx").using("gin", t.tsv),                                          // GIN for tsvector
]);

// Entities extracted by LightRAG / equivalent.
export const kbEntity = pgTable("kb_entity", {
  id: text("id").primaryKey(),
  documentId: text("document_id").notNull().references(() => kbDocument.id, { onDelete: "cascade" }),
  name: text("name").notNull(),
  type: text("type"),                            // 'person' | 'org' | 'concept' | …
  description: text("description"),
}, (t) => [
  index("kb_entity_document_idx").on(t.documentId),
  index("kb_entity_name_idx").on(t.name),
]);

// Edges (relations) between entities.
export const kbEdge = pgTable("kb_edge", {
  id: text("id").primaryKey(),
  documentId: text("document_id").notNull().references(() => kbDocument.id, { onDelete: "cascade" }),
  fromEntityId: text("from_entity_id").notNull().references(() => kbEntity.id, { onDelete: "cascade" }),
  toEntityId: text("to_entity_id").notNull().references(() => kbEntity.id, { onDelete: "cascade" }),
  relation: text("relation").notNull(),         // 'works_for', 'cites', 'mentions', …
  weight: real("weight").default(1.0),
}, (t) => [index("kb_edge_from_idx").on(t.fromEntityId),
            index("kb_edge_to_idx").on(t.toEntityId)]);

// Citation back-reference — connects a chat message to the chunks that grounded it.
export const kbCitation = pgTable("kb_citation", {
  id: text("id").primaryKey(),
  messageId: text("message_id").notNull(),      // LangGraph message id
  chunkId: text("chunk_id").notNull().references(() => kbChunk.id, { onDelete: "cascade" }),
  score: real("score").notNull(),                // similarity / RRF score
  rank: integer("rank").notNull(),              // 0-indexed position in the merged ranking
}, (t) => [index("kb_citation_message_idx").on(t.messageId),
            index("kb_citation_chunk_idx").on(t.chunkId)]);

user_id is on kb_document (not duplicated on chunks) — per-user isolation is enforced by joining through kb_document. Cross-user reads return 404 (rule from docs/AUTH.md).

Add migrations; update docs/DB.md (rule #1 spirit).

Tool-call surface (rule #10 — lazy-register)

Add to backend/tool/:

// null when pgvector extension isn't available or KB tables are empty.
export const searchKbTool: StructuredTool | null = process.env.PG_EXTENSION_PGVECTOR
  ? tool(/* hybrid RRF query: BM25 + vector + graph expansion */, { name: "search_kb", schema: … })
  : null;

export const searchGraphTool: StructuredTool | null = process.env.PG_EXTENSION_PGVECTOR
  ? tool(/* entity-seeded traversal */, { name: "search_graph", schema: … })
  : null;

export const listDocumentsTool: StructuredTool | null = tool(/* paginated list of kb_document rows */, …);

When KB is empty / unconfigured, the model gracefully degrades — search_kb returns [], the agent continues without grounding (existing behavior). Per rule #10.

When the model cites a chunk, the ToolMessage payload should include the citation row ids so the frontend (or the citation-saving side-effect on triggerBackgroundAgentNode) can persist kb_citation rows. Update docs/TOOLS.md (rule #10) and docs/INTERRUPT.md (rule #10).

Frontend — Settings → Knowledge Base tab

Add a Knowledge Base tab under /settings, alongside the existing Memory tab. Style mirrors components/settings/memory-view.tsx (same bg-muted/30 chrome, same collapsible sections, same shadcn primitives — rule #11).

What it shows

For the current user only — userId is the only filter applied at the API and at the render layer:

• Document list (paginated): one row per kb_document, columns
  • Title (truncate, hover for full)
  • Source badge: attachment (from [Feat]: chat attachments backed by Cloudflare R2 #12) or URL
  • Status: pending / ingesting / ready / failed with icon
    • pending — <Loader2 /> spinner
    • ingesting — <Loader2 /> + progress bar (from the sidecar's progress callback if available)
    • ready — <CheckCircle2 />
    • failed — <AlertCircle /> + hover for error_message
  • createdAt (relative — formatDistanceToNow style we already use elsewhere)
  • Action menu: View, Retry (failed only), Delete
• Row detail (lazy-loaded on click, same pattern as components/observability/):
  • First N chunks with their text + a citation count
  • Entity list (extracted names) with click-through to search_graph
  • Markdown preview pane (read-only, scrollable) — XSS-safe render of the cleaned output
• Empty state: "No documents yet — upload a file or paste a URL to get started."
• Stats card at the top: total documents, total chunks, total entities (matches the existing observability stat-card pattern in aggregate.ts)

Why this layout

• Mirrors memory-view.tsx so users (and the implementation cost) don't have to learn a new pattern. rule [Feat]: Public landing page at / (replace auto-redirect to /chat) #11 says "use the existing primitives."
• Per-user scoping is enforced server-side (where(eq(kbDocument.userId, user.id))), but the UI never suggests otherwise — there's no "share document" button, no org filter, no global search.
• The lazy-loaded row detail matches lib/observability/transform.ts + components/observability/ — we don't ship the entire chunk list with every list response.

Routing

app/settings/page.tsx already hosts the tabs (Memory is one). The new tab uses the same basePaths={{ settings: "/settings" }} plumbing from app/auth-shell.tsx (which is currently ["memory"]-only) — extend the tabs config to include "knowledge-base", then mirror the memory-view.tsx component as components/settings/kb-view.tsx.

The AuthProvider's settings-tab system supports multiple tabs via the plugin pattern (see how memorySettingsPlugin is registered today); the KB tab is a sibling plugin / extension — keep it consistent with whatever pattern Memory uses, don't fork the layout.

Chat composer — @ mention a knowledge-base document

In addition to the settings tab, the chat composer must let users mention a KB document inline (à la Slack / Notion). Typing @ in the composer opens a popover listing the user's ready documents; selecting one inserts a chip that travels with the message and grounds the model's response.

assistant-ui's built-in mention system

Verified against @assistant-ui/react@0.14.26/dist/unstable/useMentionAdapter.d.ts (installed in this repo):

import { unstable_useMentionAdapter } from "@assistant-ui/react";

const kbMention = unstable_useMentionAdapter({
  items: docs.map((d) => ({
    id: d.id,
    type: "kb-document",
    label: d.title,
    description: `${d.chunkCount} chunks · ${d.status}`,
    icon: "kb-doc",
    metadata: { attachmentId: d.attachmentId, sourceUrl: d.sourceUrl },
  })),
  iconMap: { "kb-doc": KbDocIcon },
  onInserted: (item) => console.log("inserted", item),
});

This returns { adapter, directive, iconMap, fallbackIcon } which spreads into <ComposerPrimitive.Unstable_TriggerPopover char="@" {...kbMention} />. The popover + arrow-key navigation + filtering come for free.

References:

• unstable_useMentionAdapter — dist/unstable/useMentionAdapter.d.ts:66-71.
• Unstable_Mention shape — id | type | label | description? | icon? | metadata?.
• Unstable_TriggerPopover primitives — dist/primitives/composer.d.ts:23 (under ComposerPrimitive).
• Doc: https://www.assistant-ui.com/docs/guides/triggers (canonical "Triggers / Mentions" guide).

Backend-side resolution of the mention

The mention directive rides the message as a directive string (e.g. @[kb-document:abc123]). Two viable resolutions; pick one and document it:

1. Server-side pre-fetch (recommended): the chat runtime intercepts the directive in backend/agent.ts (likely in callModelNode or a new pre-model hook), resolves the IDs against kb_document (user-scoped 404 check), runs the hybrid search / top-k chunk retrieval, and injects the chunks into the model context as a <mentioned-documents> block. Pro: deterministic, no tool round-trip latency, works with any model. Con: chat latency grows with top_k × chunk_size.
2. Tool-call resolution: strip the directive before sending, let the model see the bare @kb-document:abc123 token, and have it call search_kb({ documentId: "abc123" }). Pro: model picks what to read. Con: extra round-trips, and the model might skip the call.

Recommendation: option 1 — pre-fetch the first K chunks per mentioned doc, append a <mentioned-documents> system block. Keep top_k small (default 4) so latency stays bounded. The search_kb tool stays available for the model to dive deeper when the user asks follow-ups.

Edge cases

• Multiple mentions in one message — pre-fetch each in parallel; budget the total chunks (e.g. min(top_k, 32 / mentionCount)) so the prompt doesn't explode.
• Stale doc — if a kb_document is failed or deleted between mention and send, the directive resolves to a no-op + a soft warning appended to the system message.
• Cross-user mention — already impossible: the popover only lists kb_document rows for the current userId; even if a crafted message arrived, the resolution query enforces the user filter (returns 404 → silently dropped).
• Self-reference — a user mentioning their own document is the common case; no special handling needed.
• Composer focus + accessibility — the popover is keyboard-navigable out of the box (assistant-ui's popover handles arrow keys, Enter, Esc). Don't override.

TDD

1. tests/frontend/chat/kb-mention.test.tsx — render the composer with unstable_useMentionAdapter mock-injected with two fake docs; type @; assert the popover shows the labels; arrow-down + Enter; assert the chip is inserted.
2. tests/backend/chat/resolve-mentions.test.ts — given a message containing @[kb-document:abc123]; assert the resolver calls searchKbTool's hybrid search and the resulting chunks land in the system message.
3. tests/backend/chat/resolve-mentions.test.ts — given an unknown / deleted / other-user's doc id; assert the mention is silently dropped + no tool call is made.
4. XSS: titles in the popover are rendered as text (assistant-ui handles this), but a render test confirms no <script> survives the metadata round-trip.

Acceptance (chat-mention specific)

• Typing @ in the composer opens a popover listing only the current user's ready documents.
• Selecting a doc inserts a chip; the chip travels with the message.
• On send, the model receives the document's relevant chunks and cites them in its reply.
• Failed / deleted / unknown mentions don't crash the chat; they're dropped silently.
• Cross-user mentions are impossible end-to-end (no API path exposes another user's doc).
• Keyboard navigation works (arrow keys, Enter, Esc).
• No regression to the existing attachment upload flow ([Feat]: chat attachments backed by Cloudflare R2 #12) — composer can carry both attachments AND mentions simultaneously.

Acceptance (frontend-specific)

• Settings → Knowledge Base tab renders with the same chrome as the Memory tab.
• Lists only the current user's documents; no UI affordance to view other users' data.
• Status icons match the four states; failed rows expose error_message on hover.
• Row click lazy-loads the detail (chunks + entities + markdown preview); not in the initial list payload.
• Empty state matches the existing memory-view.tsx empty-state style.
• Delete + Retry actions call the right API routes (DELETE /api/kb/documents/[id], POST /api/kb/documents/[id]/retry).
• XSS: title, source URL, and markdown preview are rendered as text (or sanitized markdown) — never as raw HTML. Add a render test with <script> in title.

Architecture choices to confirm before implementing

These are real decisions, not bikeshedding. Surface them so the user (or this issue's reviewer) can pick:

1. Python sidecar vs Deno Deploy Sandbox: confirmed sidecar — but verify Deno Deploy's Python sandbox tier can run marker + PyTorch before committing. If yes, drop the sidecar.
2. Embedding model: OpenAI text-embedding-3-small for MVP. BGE-M3 swap requires TEI container (one more service).
3. Graph RAG flavor: LightRAG. Microsoft GraphRAG's community detection is heavy and our corpus shape is unknown.
4. Chunking: chonkie semantic chunker, with LangChain recursive as a pure-text fallback.
5. Where the ingestion job runs: the existing background_agent graph (extend it) vs a new ingest_kb_agent graph registered in langgraph.json. Prefer the latter — clean separation, parallel branch.

Out of scope

• OCR on screenshots inside PDFs — marker's image pipeline handles extraction, but we do NOT OCR the extracted images for inline text in this MVP. Future issue.
• Multi-tenant scoping — KB is per-user only; no orgs / shared workspaces yet.
• Incremental re-ingestion on file edit — re-ingestion is whole-document for now.
• Citation UI in the chat thread — the data model is in place; rendering is a follow-up polish issue.
• Query rewriting / HyDE / re-ranking — start with raw hybrid + graph expansion; add re-ranking once we have evaluation data.

Acceptance criteria

• User uploads a PDF (the kind with tables, images, formulas) → it appears in the KB with searchable markdown.
• User uploads a PNG / scanned image → OCR runs, text is searchable.
• User pastes a URL → markdown is extracted and stored.
• Document appears in the Settings → Knowledge Base tab with the correct status, scoped to the current userId only.
• search_kb returns relevant chunks via hybrid (vector + BM25), and search_graph expands with entity neighbors.
• Tool calls produce kb_citation rows linking chat message → chunk.
• Per-user isolation: user A cannot see user B's documents (404, matching docs/AUTH.md).
• KB unconfigured → tools gracefully no-op, model behavior unchanged.
• pnpm lint, pnpm typecheck, pnpm test green; new tests cover ingestion (mocked Python sidecar) + hybrid search SQL + tool-call lazy registration.
• docs/APIS.md updated if new routes added (rule feat(001): stage 1 — user auth (Better Auth + email verification + thread ownership) #1).
• docs/TOOLS.md updated with the new tools (rule [Bug]: Memory tab Socials row missing email per linked provider #10).
• docs/DB.md updated with the new schema.
• docs/INTERRUPT.md updated if the citation save side-effect changes the interrupt flow.

TDD plan (rule #2)

1. Ingestion test (RED → GREEN): tests/api/kb/ingest.test.ts — mock the Python sidecar; assert that on POST /api/kb/ingest with an attachment id, the orchestrator:
   • Calls the sidecar's /clean endpoint.
   • Chunks the result.
   • Embeds via OpenAI (mocked).
   • Persists kb_document, kb_chunk, kb_entity, kb_edge rows.
   • Sets status to ready.
2. Hybrid search test: tests/api/kb/search.test.ts — seed two documents with known chunks; query with a keyword + a paraphrased question; assert both BM25 and vector hits appear in the merged result, and that RRF rank is monotonic.
3. Graph search test: tests/api/kb/graph.test.ts — seed an entity graph; query with an entity mention; assert the traversal returns neighbor chunks.
4. Tool-call test: tests/backend/tools/search-kb.test.ts — assert searchKbTool is null when PG_EXTENSION_PGVECTOR is unset, and a working tool otherwise; mock the DB and assert the tool returns the expected schema.
5. Citation test: tests/api/kb/citations.test.ts — when search_kb is invoked via the chat runtime, the resulting ToolMessage carries citation ids; triggerBackgroundAgent (or the new ingest_kb_agent) persists them.
6. XSS coverage (carried over from [Feat]: chat attachments backed by Cloudflare R2 #12): markdown rendered from user-uploaded documents is sanitized before being inserted into a future thread UI; for now, the markdown itself is just stored, but list_documents returns titles / snippets that the UI must render as text, not HTML. Add a render test for that.
7. Frontend test: tests/frontend/settings/kb-view.test.tsx — render with a mocked list response (2 ready docs, 1 failed); assert status icons + per-user scoping (no other user's rows appear even if API returns them) + delete action. RED → GREEN.
8. Chat-mention test: tests/frontend/chat/kb-mention.test.tsx — typing @ opens the popover; arrow + Enter inserts a chip. RED → GREEN.
9. Mention resolver test: tests/backend/chat/resolve-mentions.test.ts — message with @[kb-document:abc123] resolves to chunks in the system prompt; unknown ids are dropped silently. RED → GREEN.

Notes for the implementer

• Don't write a PDF parser. If marker doesn't fit on a document, swap to docling, but never write our own.
• Don't write a chunker. chonkie + LangChain cover the cases. The "one true chunker" debate is a tarpit.
• Don't write a graph extractor from scratch. LightRAG / GraphRAG are mature; even the prompt template is opinionated and worth reusing.
• The Python sidecar is a thin shim — it should do marker --output md, embed via HTTP to OpenAI, and return JSON. The TS orchestrator owns chunking + DB writes. That keeps the hot path in our stack and the ML model I/O in Python.
• Don't break rule [Bug]: Memory tab Socials row missing email per linked provider #10: tools must be null when the KB isn't ready. The model should never see a tool that 500s.
• Citation back-references are the bridge between this issue and [Feat]: chat attachments backed by Cloudflare R2 #12 — when kb_citation lands in the DB, the frontend can render hover-cards linking the message back to the source chunk / original file.
• Per-user isolation is non-negotiable; cross-user reads must 404 (matches the existing thread-isolation pattern).

Related

• [Feat]: chat attachments backed by Cloudflare R2 #12 — chat attachments backed by Cloudflare R2 (the upstream source of attachment rows this issue ingests; the chat-mention popover reads from this same storage pipeline once KB ingestion is wired).
• backend/store.ts — existing Postgres-backed store for memory + thread summaries; KB reuses the same DB but adds the pgvector extension.
• backend/background-agent.ts — existing turn-end side-effect graph; the new ingest_kb_agent mirrors this pattern.
• backend/tool/ — lazy-registration site (rule [Bug]: Memory tab Socials row missing email per linked provider #10).
• docs/AUTH.md § Data isolation — cross-user 404 rule.
• docs/DB.md — schema conventions; new tables follow the same shape.
• docs/TOOLS.md — tool inventory; must be updated with the new tools.
• docs/APIS.md — new ingest / search endpoints (rule feat(001): stage 1 — user auth (Better Auth + email verification + thread ownership) #1).
• docs/INTERRUPT.md — interrupt contract for the new tools.
• @assistant-ui/react ContentPart — citation UI will eventually render here.

[FireTable]

Tested-fixture suggestion for the KB pipeline.

This dataset is small + varied enough to be a good smoke / golden-truth corpus while we build out the pipeline:

1. Ingestion smoke test — drop a few PDFs / docs into the ingest path; verify the cleaned markdown matches a checked-in snapshot. Any change in marker / chonkie / embedding output shows up as a diff.
2. Hybrid search regression — seed a fixed set of docs from this repo, run a battery of canned queries against search_kb, assert the top-k chunk ids are stable. Catches RRF / BM25 / vector regressions.
3. Graph-RAG evaluation — entity + edge extraction output can be diffed against a hand-labeled subset. Cheap way to spot when LightRAG swaps break the graph.
4. End-to-end @mention test — mention one of the docs in a chat, assert the resolver pulls the right chunks into the system prompt. Real corpus, no synthetic fixtures.

Worth pulling in (alongside any other public KB-shaped dataset) once lib/kb/ lands. Treat as test-only data — do not ingest into production.

[FireTable]

M1 sync render — done (deprecates itself)

#12's PDF path landed as sync render in `confirm/route.ts`: server-side
mupdf at 200 DPI, one PNG per page uploaded back to R2, response carries
`pageImages: [{url, pageIndex, width, height}, ...]`. `r2-adapter.ts`
parses that and folds the images into the message content as N
`{type:"image", image:url}` parts. Model sees vision-OCR'd pages,
the SDK's hardcoded `source_type:"base64"` file-part bug is sidestepped.

End-to-end works. But it's a transitional state — the
"derived representation" lives in the wrong layer:

Symptom	Why it's wrong
`r2-adapter` has a `pageImages` branch	Adapter should be content-type-agnostic; the M2 version (this issue) lets it stay simple
`confirm` does real work (rendering)	Should be a thin status flip; heavy work belongs in the KB agent
`state.messages` carries image parts after send	Original file part is gone; can't re-resolve on re-send
Re-uploading same PDF: dedup short-circuits and the model again sees the broken file part	`skipUpload: true` skips confirm, so no `pageImages` — the broken-flow regression lives on

Schema discussion we ran through (recording so the rejected paths don't
come back):

• ❌ `attachments.derived_r2_keys: text[]` — single column, holds all
  binary derivatives. Solves the dedup hole but doesn't scale: markdown
  is text not an R2 key, embedding vectors don't fit, each new
  representation needs a new key-naming convention.
• ❌ `attachment_artifacts` side table — generic, future-proof, but
  it duplicates the work `kb_document` + chunks already do.
• ❌ `extract_data` jsonb on `attachments` — column inflation per
  derived type, can't query, can't cascade cleanly.
• ✅ This issue's `kb_document` + `kb_chunk` etc. — KB owns ALL
  representations. Single source of truth.

M1 → M2 transition plan

1. Land M1 (sync render) as-is — already done. Users can upload PDFs
   today; document the limitation in `docs/ATTACHMENTS.md`.
2. M2 (this issue) replaces M1:
   • Revert `confirm/route.ts` PDF render branch.
   • Revert `r2-adapter.ts` `pageImages` parsing.
   • New graph node `attachment-kb-injector` runs before `chatModelNode`,
     rewrites file parts in the invoke-time message array
     (not `state.messages`) using KB lookups.
   • Hit → model sees KB's preferred representation. Miss → enqueue
     for the background agent, model sees the original file part for
     this turn (first-message window; see UX options in the issue
     plan).
3. `attachments` table stays minimal. `kb_document` gets the optional
   `attachment_id` FK you already specced.

UX decision for the first-message window

The three options from earlier discussion:

• (a) Model says "give me a moment" while background processes.
• (b) Stream a "processing…" placeholder, swap in real response when KB finishes.
• (c) Sync fallback inside the injector on first-time PDFs (duplicate work, but first message is correct).

My pick: (c) for v1 — duplicate work on first message only (~1-2s
mupdf render for 10 pages), but the model is correct on the very
first turn. (a)/(b) require explicit UX work the chat runtime doesn't
have today. Switch to (a)/(b) when KB lookup latency dominates and
the cost of doing it twice is real.

[FireTable]

M1 attempt — sync PDF render in `confirm` (reverted 2026-07-09)

Logged here so the next iteration doesn't repeat the same shape.

What was tried

A prototype landed end-to-end:

• `app/api/attachments/[id]/confirm/route.ts` — branched on
  `contentType === "application/pdf"` after the HeadObject +
  status-flip. Pulled bytes via a new `getObject` helper in
  `lib/r2/client.ts`, dynamic-imported `mupdf` (Node, 5 MB
  wasm), rendered every page at 200 DPI to PNG, uploaded each PNG
  back to R2 via a new `putObject` helper, returned
  `pageImages: [{url, pageIndex, width, height}, ...]`.
• `lib/attachments/r2-adapter.ts` — parsed the new `pageImages`
  field; when present, folded them into `CompleteAttachment.content`
  as N `{type:"image", image:url, filename:"x.pdf#pN"}` parts.
  When absent, fell back to the existing file-part path.
• `package.json` — added `mupdf@^1.28.0`.

What worked

• End-to-end: PDF → R2 → confirm renders → page images in R2 →
  `r2-adapter` consumes → model sees vision-OCR'd pages. The
  assistant-ui SDK's hardcoded `source_type:"base64"` for file
  parts is sidestepped because the content part type is now
  `image`, which routes through the clean `image_url` branch.
• gpt-4o-mini: ~85–765 vision tokens/page (image tokens cap
  regardless of resolution); 10-page PDF ≈ 0.001 USD.
• Vision OCR preserves column layouts that text extraction cannot
  recover (issue [Feat]: chat attachments backed by Cloudflare R2 #12 comment 2026-07-09 06:22, 11-path probe).

Why it was reverted

Four interlocking problems, none individually fatal but together
wrong enough to defer:

1. Dedup hole. The Q2 short-circuit returns
   `skipUpload: true` from `presign`; `r2-adapter` returns
   immediately without calling `confirm`. So a re-upload of the
   same PDF goes through `skipUpload` and the model again sees
   the broken file part. Three fixes were considered:

   • Always call `confirm` even on dedup → loses dedup's
     R2 bandwidth saving, plus re-renders every send.
   • Store page-image URLs on `attachments` → column
     inflation (`derived_r2_keys: text[]`, etc.); not a clean
     general shape; overlaps with KB's own artifacts.
   • Move rendering out of the request path entirely → which is
     what this issue proposes.

2. Schema 异味. Where do "page images" live in the DB? Three
   candidates, all rejected:

   • `extract_data: jsonb` column on `attachments` — one
     column per derived type; not queryable; can't cascade.
   • Single `derived_r2_keys: text[]` column — doesn't fit
     text-typed representations (markdown, OCR text) and embedding
     vectors; key-naming convention is the schema.
   • Separate `attachment_artifacts` table — generic but
     duplicates the work `kb_document` + `kb_chunk` already
     do once this issue lands.

3. First-message latency. Adding the render to `confirm`
   makes every PDF confirm 1–2 s slower (mupdf 200 DPI, 10 pages
   ≈ 1 s; uploads of larger PDFs spike further). The user waits
   for that before the model invocation even starts. The
   background-agent path this issue sketches is async by design.

4. `state.messages` mutation. The render-time swap from
   `{type:"file"}` to N `{type:"image"}` mutates what's
   persisted in the checkpointer. On re-render of the same
   thread, the renderer shows the image parts as if they were
   the original upload — slightly lossy if you ever want to
   recover the source PDF URL from the message. Keeping the file
   part intact in state and rewriting only at invoke time
   preserves the original artifact reference.

Current state (post-revert)

• The four files from the M1 attempt are reverted to
  `2d48132`. `mupdf` dep is no longer installed.
• The default allow-list in `lib/attachments/r2-adapter.ts`
  and `.env.example` is now `image/png,image/jpeg,image/webp`
  — PDF (and other non-image types) are rejected at presign time.
  Override via `NEXT_PUBLIC_R2_ALLOWED_CONTENT_TYPES` if a
  non-image flow needs to re-enable.
• The chat composer no longer surfaces a PDF picker. Existing
  `attachments` rows from before the revert are unaffected (the
  allow-list only gates new uploads, not historical reads).

What this issue (#13) owns instead

• All attachment-derived representations live under `kb_document`
  • `kb_chunk` (markdown, OCR text, embeddings) and a separate
    image-artifact path for the page renders.
• The `attachment-kb-injector` graph node rewrites the file
  part at the invoke boundary, not at upload time. `state.messages`
  keeps the file part; `invoke messages` carries the KB-derived
  form. Re-renders work the same way as the original message.
• Background `ingest_kb_agent` handles the slow work; chat
  first-message latency stays bounded.
• The schema 异味 disappears: `attachments` stays minimal; KB
  tables own all derived representations. The same code path
  services chat injection AND future RAG.

UX pick for first-message window

The three options are still on the table; recording my pick
so the implementer doesn't re-litigate:

• (a) Model says "give me a moment" while background processes.
• (b) Stream "processing…" placeholder, swap in real response when
  KB finishes.
• (c) Sync fallback inside the injector on first-time PDFs
  (duplicate work, but first message is correct).

Pick (c) for v1. mupdf is fast (~1 s for 10 pages at 200 DPI);
duplicate work on the first send only. No new UX plumbing
required. Switch to (a) or (b) when KB lookup latency
dominates or the duplicate cost becomes measurable.

[FireTable]

KB + GraphRAG Schema Research — Issue #13

Research survey of mainstream RAG / GraphRAG storage designs (RAGFlow, LightRAG, Microsoft GraphRAG, Haystack PgvectorDocumentStore, LlamaIndex PGVectorStore, Cognee, txtai), plus the Postgres primitives we plan to lean on (pgvector HNSW / halfvec / binary_quantize re-ranking, tsvector + GIN, JSONB jsonb_path_ops, ParadeDB RRF). Goal: pick a concrete schema and indexes that fit our Next.js + LangGraph + Drizzle + Postgres + R2 stack, migrating the existing attachments table into a real per-user knowledge base with hybrid retrieval and Graph RAG.

---

1. Per-system summary

RAGFlow — Splits storage deliberately: relational metadata in MySQL (document, knowledgebase, task, file, dialog) and the actual chunk bodies / embeddings / KG in Elasticsearch. Document and Knowledgebase carry chunk_num counters and Task.chunk_ids (a serialized ID list); chunks themselves never touch MySQL. KG runs inside the doc engine, not as relational tables. Multi-tenancy via tenant_id + created_by. (RAGFlow db_models.py)

LightRAG — Pure Postgres storage stack with explicit per-table DDL in TABLES = {...}. All KV/vector tables use the composite PK (workspace, id) for row-level multi-tenancy (8 KV tables + 3 VDB tables). Document status (LIGHTRAG_DOC_STATUS) carries chunks_list, content_hash, track_id, status, error_msg — i.e., it doubles as the async-job record. Graph storage uses Apache AGE (Cypher), with no community_reports PG table at all — community summaries are recomputed or kept out-of-band. Upserts use COALESCE(NULLIF(EXCLUDED.col, ''), table.col) to avoid clobbering on partial writes. (LightRAG postgres_impl.py)

Microsoft GraphRAG — File-based pipeline: all outputs are Parquet (create_final_entities, _relationships, _communities, _community_reports, _text_units, _documents, _nodes, _edges, _covariates). Communities are hierarchical Leiden with parent/children/level. Important caveat: community (the short id) is NOT stable across runs — a period and a per-run incrementing human_readable_id exist to support incremental merges, but downstream code must remember a run_id if it wants to pin a community across re-indexing. (GraphRAG outputs)

Haystack PgvectorDocumentStore — Single haystack_documents table holding content + embedding + meta + blob_data all inline (binary files inlined rather than pointer-based). Hybrid via tsvector + GIN alongside an HNSW vector index on the same row.

LlamaIndex PGVectorStore — One table per index (data_<index_name>), JSONB metadata_ with per-key typed projections, GENERATED tsvector column wired to hybrid search, and a node_id back-reference for joining against the doc store.

Cognee — Three-way split (relational data + Dataset + DatasetData, plus vector and graph). The most ACL-heavy design reviewed: explicit tenant_id + delegation grants. Useful reference if we want per-document sharing later.

txtai — sections / objects / embeddings tables with BLOB-encoded vectors. Portable across SQLite/Postgres via with_variant. Tidy if we want the KB engine reusable for local dev without a pgvector build.

---

2. Patterns worth borrowing

#	Pattern	Why for us	Source
1	Composite PK (workspace, id) on every table (LightRAG).	Our multi-tenancy primitive is per-user (auth user id). Putting user_id as the leading PK column makes WHERE user_id = $1 AND id = ... an index-only scan and lets us drop a workspace with one DELETE per table. Matches rule #10's lazy-register pattern: zero-config keys become a free multi-tenant DB without a tenant column on every row.	LightRAG TABLES
2	content_hash partial unique index for dedup (RAGFlow Document.content_hash xxhash128 + LightRAG partial index WHERE content_hash IS NOT NULL AND content_hash <> '').	Chat uploads will re-promote the same file repeatedly. A unique constraint keyed on (user_id, content_hash) lets us short-circuit ingestion and link to the existing doc instead of double-billing R2 / embedding costs. Partial because legacy rows may have NULL.	RAGFlow Document, LightRAG doc_status indexes
3	Embedding versioning via (model_id, embedding) + active-model table (pgvector README + our own model.ts env story).	Our backend already carries modelKwargs: { reasoning_split: true } per provider swap (CLAUDE.md project notes). Storing model_id next to the vector means we can re-embed in the background when we upgrade the model and ship per-version results side-by-side; queries pick the row whose model_id matches the active entry.	pgvector README
4	Counter columns + serialized id list for progress reporting (RAGFlow: Document.chunk_num, Task.chunk_ids).	The observability panel already exists; ingestion UI needs cheap progress reads. Storing processed_chunks / total_chunks + processed_chunk_ids JSONB on kb_ingest_job lets us render progress without re-counting the chunk table. Avoids a SELECT COUNT(*) per UI poll.	RAGFlow Document/Task
5	RRF hybrid as a SQL expression, not a vendor function (ParadeDB docs — hybrid_rank() is on the roadmap, not shipped).	Our retrieval endpoint is one Postgres connection away; embedding an RRF CTE (SUM(1.0 / (60 + rank)) over UNION ALL legs) keeps us on stock pgvector + a tsvector column, no new extensions. Standard RRF k=60 smoothing.	ParadeDB hybrid docs

---

3. Recommended table set (Drizzle-friendly)

Naming follows project convention (snake_case tables, kb_ prefix to keep distinct from chat attachments/threads). Every table carries user_id (from session) as a leading PK column for workspace isolation. Times default to now(). JSONB uses jsonb_path_ops where we filter by containment.

kb_document

Parent record of every ingested file.

Column	Type	Notes
id	uuid PK
user_id	text PK	leading PK column, matches user.id
r2_key	text	pointer to binary in R2 (mirrors attachments.storage_key)
filename	text not null
mime_type	text not null
byte_size	bigint not null
content_hash	text not null	xxhash128 — partial unique idx below
parser	text not null	enum: marker-pdf, chonkie-text, image-ocr
parser_config	jsonb not null default '{}'::jsonb	marker pages, table_context_size, etc. (RAGFlow-style)
status	text not null	pending / parsing / chunking / embedding / graph_extracting / ready / failed
chunk_count	integer not null default 0	counter, not derived
entity_count	integer not null default 0
relation_count	integer not null default 0
community_run_id	uuid null	latest Leiden run that includes this doc
error_message	text null
created_at / updated_at	timestamptz not null default now()

Indexes:

• PK (user_id, id)
• partial unique (user_id, content_hash) WHERE content_hash <> '' (Pattern feat(002-web-tools): web search/fetch tools, suggestions, rebrand #2)
• btree (user_id, status) for "my in-flight docs" dashboard
• btree (user_id, created_at desc) for listing

Rationale: pattern follows RAGFlow Document (counters + status + parser_config) and LightRAG LIGHTRAG_DOC_STATUS (status, error_msg). R2 key keeps the binary out of Postgres (per attachments precedent).

kb_chunk

Chunks with positional metadata from marker-pdf / chonkie.

Column	Type	Notes
id	uuid PK
user_id	text PK
document_id	uuid not null	FK kb_document(id) ON DELETE CASCADE
order_index	integer not null	preserves source order
content	text not null	cleaned markdown / plain text
tokens	integer not null	chonkie token count
start_offset / end_offset	integer	chonkie start_index / end_index
page_numbers	integer[]	chonkie page_numbers — supports WHERE :page = ANY(page_numbers)
block_ids	text[]	marker-pdf /page/N/BlockType/i ids
bboxes	jsonb	array of [[x,y],[x,y],[x,y],[x,y]] per block, indexed via jsonb_path_ops for containment
section_path	text[]	marker-pdf section_hierarchy flattened; useful for "find chunks under §3.2"
embedding	vector(1536) null	dim tied to active embedding model
embedding_model_id	text null	nullable until embedded; supports Pattern #3 versioning
embedding_halfvec	halfvec(1536) null	populated by trigger / background re-embed; indexable by halfvec HNSW
embedding_bit	bit(1536) null	for binary_quantize re-rank (pgvector README §3)
tsv	tsvector GENERATED	(setweight(to_tsvector('english', title), 'A') || to_tsvector('english', content)) — or just content (LlamaIndex pattern)
metadata	jsonb not null default '{}'::jsonb	per-chunk user metadata

Indexes:

• PK (user_id, id)
• btree (user_id, document_id, order_index) — preserves per-doc order
• GIN (tsv) — keyword leg of hybrid (pgvector / Postgres docs; matches Haystack / LlamaIndex)
• HNSW (embedding halfvec_cosine_ops) WITH (m=16, ef_construction=64) — fallback for precision
• HNSW ((embedding::halfvec) halfvec_cosine_ops) — half-precision ANN when dim ≤4000 (pgvector README §2)
• HNSW ((binary_quantize(embedding)::bit(1536)) bit_hamming_ops) — for re-rank pass (pgvector README §3)
• GIN (bboxes jsonb_path_ops) — bbox containment
• GIN (metadata jsonb_path_ops) — filter by metadata containment
• btree (user_id, embedding_model_id) WHERE embedding IS NOT NULL — for re-embed jobs

Rationale: LlamaIndex's GENERATED tsvector + JSONB metadata projection pattern is the closest analogue; chonkie's start_index / end_index / page_numbers / block_ids / bboxes map 1:1 to the marker-pdf polygon bbox + section_hierarchy schema.

kb_entity

Graph nodes from extraction.

Column	Type	Notes
id	uuid PK
user_id	text PK
name	text not null	entity title
type	text not null	matches GraphRAG entities.type (person / organization / geo / event / custom)
description	text not null	LM-derived, LM-merged across text units
description_embedding	vector(1536) null	entity-level vector for "find similar entities"
frequency	integer not null default 0	GraphRAG entities.frequency
degree	integer not null default 0	GraphRAG entities.degree
chunk_ids	uuid[] not null default '{}'	source chunks (LightRAG LIGHTRAG_VDB_ENTITY.chunk_ids style)
community_id	uuid null	FK kb_community(id) — Leiden cluster assignment
run_id	uuid not null	latest Leiden run that produced this row
metadata	jsonb not null default '{}'::jsonb
created_at / updated_at	timestamptz

Indexes:

• PK (user_id, id)
• unique (user_id, name, run_id) — entity uniqueness is per-run
• btree (user_id, community_id) — community → entities
• HNSW (description_embedding vector_cosine_ops) WITH (m=16, ef_construction=64) — entity-level retrieval
• btree (user_id, type) — facet filters

Rationale: Mirrors GraphRAG create_final_entities schema, but in relational form so we can join cheaply against kb_chunk. The (user_id, name, run_id) unique constraint mirrors GraphRAG's per-run incrementing id — community/relation IDs are not stable across re-extraction, so we thread run_id everywhere.

kb_relation

Graph edges.

Column	Type	Notes
id	uuid PK
user_id	text PK
source_id	uuid not null	FK kb_entity(id)
target_id	uuid not null	FK kb_entity(id)
description	text not null	LM-derived
weight	real not null default 1	GraphRAG relationships.weight
combined_degree	integer not null default 0	GraphRAG relationships.combined_degree
chunk_ids	uuid[] not null default '{}'
run_id	uuid not null

Indexes:

• PK (user_id, id)
• unique (user_id, source_id, target_id, run_id)
• btree (user_id, source_id) + (user_id, target_id) — adjacency lookups (replaces Apache AGE for our scale; see Open Q feat(001): stage 1 — user auth (Better Auth + email verification + thread ownership) #1)
• btree (user_id, run_id, weight desc) — top-N edges per run

Rationale: Mirrors GraphRAG create_final_relationships. We store adjacency as relational edges rather than Cypher — see Open Q #1 on graph store choice.

kb_community

Leiden clusters.

Column	Type	Notes
id	uuid PK
user_id	text PK
run_id	uuid not null	one Leiden run per user per (re-)index
level	integer not null	0 = leaf, N = root
parent_id	uuid null self-FK	GraphRAG communities.parent
title	text not null
entity_ids	uuid[] not null default '{}'
size	integer not null default 0	entity count
period	timestamptz	GraphRAG period for incremental merges

Indexes:

• PK (user_id, id)
• btree (user_id, run_id, level)
• btree (user_id, parent_id) — hierarchy traversal
• btree (user_id, size desc) WHERE level = 0 — largest communities for summaries

Rationale: Mirrors GraphRAG create_final_communities. run_id is the price of non-stable community ids (Open Q #2).

kb_community_report

LLM-generated summaries.

Column	Type	Notes
id	uuid PK
user_id	text PK
community_id	uuid not null unique	1:1 with kb_community; uniqueness forces a single canonical report per community
run_id	uuid not null
level	integer not null	denormalized for fast filtering
title	text not null
summary	text not null
full_content	text not null
rank	real not null	GraphRAG community_reports.rank
rating_explanation	text not null
findings	jsonb not null	array of {summary, explanation}
full_content_json	jsonb not null	raw LM output (GraphRAG keeps this for query-prompt tuning)
created_at	timestamptz not null default now()

Indexes:

• PK (user_id, id)
• unique (user_id, community_id)
• btree (user_id, run_id, rank desc) — "top communities for this run"

Rationale: Mirrors GraphRAG create_final_community_reports. The full_content_json column is kept because the GraphRAG docs explicitly note it's retained for query-summarization prompt tuning — useful even if we don't use it day one.

kb_ingest_job

Async task tracking. One row per (document, pipeline-stage).

Column	Type	Notes
id	uuid PK
user_id	text PK
document_id	uuid not null	FK kb_document(id)
stage	text not null	parse / chunk / embed / extract_entities / extract_relations / leiden / summarize_communities
status	text not null	queued / running / succeeded / failed / cancelled
progress	real not null default 0	0..1
processed_units	integer not null default 0	chunks embedded, entities extracted, etc.
total_units	integer not null default 0
processed_unit_ids	jsonb not null default '[]'::jsonb	RAGFlow Task.chunk_ids analogue
error_message	text null
attempt	integer not null default 1	retry count
run_id	uuid null	non-null for leiden / summarize_communities stages
started_at / finished_at	timestamptz null
created_at	timestamptz not null default now()

Indexes:

• PK (user_id, id)
• btree (user_id, document_id, stage, created_at desc) — "latest job per (doc, stage)"
• partial (user_id, status) WHERE status IN ('queued','running') — worker pickup query
• btree (user_id, run_id) — join back to community/run

Rationale: Splits the single LightRAG LIGHTRAG_DOC_STATUS.status row into one-row-per-stage so a parser crash doesn't lock out an embed retry. Mirrors RAGFlow's Task table for queue dispatch while keeping a single status snapshot on kb_document for cheap reads.

kb_attachment_link

Promote-from-chat bridge.

Column	Type	Notes
id	uuid PK
user_id	text PK
attachment_id	uuid not null	FK attachments(id) ON DELETE CASCADE
kb_document_id	uuid not null	FK kb_document(id) ON DELETE CASCADE
promoted_by_message_id	uuid null	the chat message that triggered promotion
created_at	timestamptz not null default now()

Indexes:

• PK (user_id, id)
• unique (user_id, attachment_id) — one KB doc per attachment
• btree (user_id, kb_document_id)

Rationale: The existing attachments table (issue #12) is the R2-backed chat-upload store. kb_attachment_link is the explicit promotion record so we can (a) backfill chat history → KB, (b) preserve the R2 object and content_hash from the upload, and (c) clean up when a chat attachment is deleted without orphaning the KB doc (FK CASCADE).

---

4. Indexes — full inventory

• HNSW on chunks: CREATE INDEX kb_chunk_embed_hnsw ON kb_chunk USING hnsw ((embedding::halfvec(1536)) halfvec_cosine_ops) WITH (m = 16, ef_construction = 64) — default HNSW params from pgvector README §6. (pgvector README)
• Binary-quantize re-rank index: CREATE INDEX kb_chunk_embed_bit ON kb_chunk USING hnsw ((binary_quantize(embedding)::bit(1536)) bit_hamming_ops) — supports the "candidate from bit Hamming → re-rank top-K with exact cosine" pattern. (pgvector README §3)
• GIN tsvector: CREATE INDEX kb_chunk_tsv ON kb_chunk USING gin (tsv) — keyword leg. (Pattern feat(backend): code sub-agent + lazy-register key-needing tools + shared UI primitives #5; Postgres GIN docs.)
• GIN jsonb_path_ops: one for kb_chunk.metadata, one for kb_chunk.bboxes, both USING gin (col jsonb_path_ops) — supports @> containment with smaller indexes than default GIN. (Postgres JSONB docs; ParadeDB uses the same pattern.)
• Partial unique (user_id, content_hash) WHERE content_hash <> '' on kb_document — file dedup (Pattern feat(002-web-tools): web search/fetch tools, suggestions, rebrand #2; LightRAG partial idx).
• Composite PK (user_id, ...) on every table — row-level multi-tenancy (Pattern feat(001): stage 1 — user auth (Better Auth + email verification + thread ownership) #1; LightRAG TABLES).
• Embedding-version partial index: (user_id, embedding_model_id) WHERE embedding IS NOT NULL — supports re-embed sweeps that scan only completed chunks (Pattern refactor(backend): split agent into router + subgraphs; ask_location via interrupt; USE_SUBGRAPH toggle #3).
• Job pickup partial index: (user_id, status) WHERE status IN ('queued','running') — keeps the worker poll query tiny as succeeded rows accumulate.

We deliberately skip ivfflat (needs training, not friendly to online ingest — pgvector README §1).

---

5. Retrieval query — concrete RRF hybrid

One round-trip: keyword leg (tsvector + GIN) UNION ALL vector leg (HNSW) UNION ALL community-report leg (HNSW over description_embedding joins), RRF-summed.

WITH q AS (
  SELECT websearch_to_tsquery('english', $1) AS tsq,
         $2::vector(1536)                  AS qvec,
         $3::uuid                          AS user_id
),
kw AS (
  SELECT c.id, RANK() OVER (ORDER BY ts_rank_cd(tsv, (SELECT tsq FROM q)) DESC) AS rk
  FROM kb_chunk c, q
  WHERE c.user_id = (SELECT user_id FROM q)
    AND c.tsv @@ (SELECT tsq FROM q)
  ORDER BY rk
  LIMIT 20
),
vec AS (
  SELECT c.id, RANK() OVER (ORDER BY c.embedding <=> (SELECT qvec FROM q)) AS rk
  FROM kb_chunk c, q
  WHERE c.user_id = (SELECT user_id FROM q)
    AND c.embedding IS NOT NULL
  ORDER BY c.embedding <=> (SELECT qvec FROM q)
  LIMIT 20
),
graph AS (
  SELECT er.chunk_id AS id, RANK() OVER (ORDER BY er.score DESC) AS rk
  FROM (
    SELECT unnest(e.chunk_ids) AS chunk_id,
           e.description_embedding <=> (SELECT qvec FROM q) AS score
    FROM kb_entity e, q
    WHERE e.user_id = (SELECT user_id FROM q)
      AND e.description_embedding IS NOT NULL
    ORDER BY score
    LIMIT 20
  ) er
),
rrf AS (
  SELECT id, 1.0 / (60 + rk) AS s FROM kw
  UNION ALL
  SELECT id, 1.0 / (60 + rk) AS s FROM vec
  UNION ALL
  SELECT id, 1.0 / (60 + rk) AS s FROM graph
)
SELECT c.id,
       c.document_id,
       c.content,
       c.page_numbers,
       c.section_path,
       c.bboxes,
       sum(rrf.s) AS score
FROM rrf
JOIN kb_chunk c ON c.id = rrf.id AND c.user_id = $3
GROUP BY c.id
ORDER BY score DESC
LIMIT 10;

websearch_to_tsquery + ts_rank_cd cover density-aware ranking; unnest(e.chunk_ids) lifts entity hits back to chunk ids so the union is chunk-shaped. RRF k=60 per ParadeDB docs. (Reference: ParadeDB hybrid RRF.)

For Graph RAG local search we'd add a fourth leg that joins kb_relation and expands to neighbor chunks via kb_entity.chunk_ids. For global search we'd ORDER BY rank DESC LIMIT 5 over kb_community_report for the user's active run_id and pass the top summaries into the LLM as context.

---

6. Open questions / trade-offs

1. Graph store: relational edges vs Apache AGE. LightRAG uses Apache AGE for the KG. Our scale (per-user KBs, not enterprise-wide) probably doesn't justify AGE — kb_entity + kb_relation with (source_id, target_id) indexes gives us 2-hop adjacency in two self-joins and keeps everything inside one extension footprint (pgvector). Counter-argument: AGE's Cypher expresses multi-hop patterns more cleanly and gives us transitive closure for free. Recommendation: start with relational edges (this report's design); revisit if we end up wanting ≥3-hop traversal patterns. (LightRAG AGE setup)

2. Community id stability via run_id. GraphRAG explicitly documents that community ids are not stable across runs; LightRAG doesn't persist communities to PG at all. We've threaded run_id through kb_community / kb_community_report / kb_entity / kb_relation so every KG artifact is per-run. Open: how do we expose this to users? Pin a "default" run_id per user and only re-run Leiden on user-triggered re-index? Or auto-promote to a new run when ≥10% of documents change? Recommendation: start with user-triggered re-runs + a single "active" run_id column on kb_document (already in the schema as community_run_id).

3. Hybrid extension choice: stock tsvector vs ParadeDB pg_search. Stock tsvector + GIN keeps us dependency-free and works in dev without an extension build. ParadeDB's pg_search gives better BM25 (vs ts_rank's TF-only heuristics) and a clean ||| operator. Recommendation: ship with tsvector first; if recall suffers on long docs, add pg_search and switch the keyword leg only (RRF CTE doesn't care which side produces the rank).

4. Binary_quantize re-rank: ship now or defer? It's documented (pgvector README §3) but adds one index per chunk table and one extra query step. With ~10k chunks per user it's cheap; at 1M it starts to matter. Recommendation: build the bit index from day one (cheap to maintain, useful for debugging); only route through it when LIMIT > 100 or query latency > budget.

5. Attachment → KB promotion UX. kb_attachment_link exists, but we still need to decide: (a) auto-promote on every upload, (b) user-confirmed "Add to KB" button in the composer, (c) background sweeper that promotes anything mentioned in chat. (a) wastes embeddings on throwaway screenshots; (c) is the most useful but the most code. Recommendation: ship (b) first; queue (c) for follow-up since it needs a citation rewrite pass over the existing chat to pick which attachments to promote.

---

TL;DR for the implementation plan: one schema with eight tables (kb_document, kb_chunk, kb_entity, kb_relation, kb_community, kb_community_report, kb_ingest_job, kb_attachment_link), composite (user_id, id) PKs everywhere, partial unique on content_hash for dedup, GENERATED tsvector + HNSW + bit-Hamming indexes on kb_chunk, RRF CTE as the single retrieval path, run_id threaded through the KG tables for community-id stability, and an explicit decision to start with relational edges instead of Apache AGE.

[FireTable]

Counter-proposal: collapse to 2 tables + tag-based "graph"

The 8-table research above is solid but ships a lot of infrastructure for an MVP. Here's the monkey-version that gets ~80% of the benefit for ~25% of the schema, stays in TS, and skips the Python sidecar entirely. Should be readable in one sitting — the existing comment is the reference survey, this is the counter-proposal.

What graphRAG actually solves (the principle)

Three query classes fail under pure vector search:

1. Multi-hop — "Who supervised the project that acquired Acme?" No single chunk contains the answer; you have to chain facts.
2. Aggregation — "What are ALL the risks across Q3 reports?" Top-k caps at k and biases toward a single chunk.
3. Entity-ambiguity — "Tell me about Saturn" (planet vs. car vs. rocket). Query embedding often misses the right entity.

GraphRAG fixes these via entity + edge extraction, then traversal / PPR over the graph. (HippoRAG 2 arXiv 2505.14856, LightRAG arXiv 2410.05779.)

The insight: the "graph" can be implicit

You don't need kb_entity + kb_relation tables. Two chunks that both mention "Acme" are one hop apart in an implicit co-occurrence graph. For multi-hop queries, that's enough — find chunks that share entities with the query.

What's lost by dropping explicit edges: typed relations ("X acquired Y" vs. "was acquired by Y"), PageRank-style retrieval, and 3+ hop traversal. For MVP, none of these break real queries.

Simplified schema (2 tables; keep what's already in the issue body)

export const kbDocument = pgTable("kb_document", {
  id: text("id").primaryKey(),
  userId: text("user_id").notNull().references(() => user.id, { onDelete: "cascade" }),
  // FK attachment.id (#12) when ingested from chat; null for URL-sourced
  attachmentId: text("attachment_id").references(() => attachment.id, { onDelete: "set null" }),
  sourceUrl: text("source_url"),
  title: text("title").notNull(),
  contentType: text("content_type").notNull(),
  status: text("status").notNull(),                       // 'pending' | 'parsing' | 'ready' | 'failed'
  contentHash: text("content_hash").notNull(),           // dedup
  errorMessage: text("error_message"),
  createdAt: timestamp("created_at").defaultNow().notNull(),
  updatedAt: timestamp("updated_at").defaultNow().notNull(),
}, (t) => [
  uniqueIndex("kb_document_user_hash_uniq").on(t.userId, t.contentHash),  // partial: WHERE content_hash <> ''
  index("kb_document_user_idx").on(t.userId),
]);

// Chunks with embeddings + extracted entity tags (the "graph" lives here)
export const kbChunk = pgTable("kb_chunk", {
  id: text("id").primaryKey(),
  documentId: text("document_id").notNull().references(() => kbDocument.id, { onDelete: "cascade" }),
  ordinal: integer("ordinal").notNull(),
  content: text("content").notNull(),
  embedding: vector("embedding", { dimensions: 1536 }),
  entities: text("entities").array().notNull().default(sql`'{}'::text[]`),  // implicit graph
  tsv: generatedAlwaysAs(tsvector("content")),
  createdAt: timestamp("created_at").defaultNow().notNull(),
}, (t) => [
  index("kb_chunk_document_idx").on(t.documentId),
  index("kb_chunk_embedding_idx").using("hnsw", t.embedding.op("vector_cosine_ops")),
  index("kb_chunk_tsv_idx").using("gin", t.tsv),
  index("kb_chunk_entities_idx").using("gin", t.entities),                  // tag-overlap expansion
]);

kb_attachment_link is unnecessary — kb_document.attachment_id is the FK (the issue body already specced this; M2 comment agrees). kb_ingest_job collapses into kb_document.status plus the attachments webhook that flips pending → parsing → ready (RAGFlow does the same). kb_entity / kb_relation / kb_community / kb_community_report are deferred — see "when to add back".

Retrieval: hybrid RRF + tag expansion (the "graph leg")

WITH q AS (SELECT websearch_to_tsquery('english', $1) AS tsq,
                  $2::vector(1536)                    AS qvec,
                  $3::text                            AS uid,
                  $4::text[]                          AS qents),
kw AS (SELECT id, row_number() OVER (ORDER BY ts_rank_cd(tsv, q.tsq) DESC) AS rk
       FROM kb_chunk, q WHERE user_id = q.uid AND tsv @@ q.tsq LIMIT 50),
vec AS (SELECT id, row_number() OVER (ORDER BY embedding <=> q.qvec) AS rk
        FROM kb_chunk, q WHERE user_id = q.uid AND embedding IS NOT NULL LIMIT 50),
tag AS (SELECT id, row_number() OVER (ORDER BY cardinality(entities)) AS rk
        FROM kb_chunk, q
        WHERE user_id = q.uid AND entities && q.qents                      -- query-extracted entities
        LIMIT 30),
fused AS (
  SELECT id, 1.0/(60+rk) AS s FROM kw
  UNION ALL SELECT id, 1.0/(60+rk) FROM vec
  UNION ALL SELECT id, 1.0/(60+rk) FROM tag
)
SELECT c.id, c.document_id, c.content, c.entities,
       sum(s) AS score, count(*) AS legs_hit
FROM fused f JOIN kb_chunk c ON c.id = f.id
GROUP BY c.id ORDER BY score DESC LIMIT 10;

Three legs, one round-trip:

• kw (tsvector + GIN) — keyword recall
• vec (pgvector HNSW cosine) — semantic recall
• tag (GIN on entities, && overlap with query-extracted entity array) — the "graph" signal

A chunk hit by all three legs is the high-confidence core; legs_hit surfaces the certainty to the LLM prompt. This is what nano-graphrag's community step degrades to when you skip Leiden clustering.

Ingestion: pure TS, no Python sidecar

Step	TS pick
PDF → text	unpdf (modern wrapper around pdfjs-dist v4, TS-first)
HTML → markdown	turndown (npm, well-maintained)
OCR for scans	tesseract.js (100+ languages)
Chunking	@langchain/textsplitters (already in our stack)
Embedding	OpenAI SDK (already in our stack)
Entity extraction	Prompt-based, response_format: { type: "json_schema" } → string[]

What's lost vs. marker / PaddleOCR: table structure in PDFs, formula OCR, inline image extraction. For MVP that's acceptable — index flattened text, link the original R2 object for human reference. The M1 sync-render comment already covers the "render at request time" fallback if a user needs vision OCR for a specific doc.

When to add complexity back

Trigger	Add
Real query needs typed relations (X acquired Y vs. was acquired by Y)	kb_relation table
Queries become "summarize all topics in doc X"	Leiden community detection (LightRAG/MS GraphRAG territory)
PDF tables / formulas matter to a user	Python sidecar (marker + PaddleOCR)
100k+ chunks per user, pgvector+tsvector slow	Neo4j / Apache AGE for the KG leg

Acceptance delta

The original acceptance list still holds. Schema changes:

• Drop: kb_entity, kb_relation, kb_community, kb_community_report, kb_ingest_job, kb_attachment_link
• Add: entities TEXT[] on kb_chunk (with GIN index) — this is the entire graph
• Defer: Python sidecar; halfvec / binary_quantize re-rank paths; community reports

Sources

• HippoRAG 2: arXiv 2505.14856 — PPR over a triples list, storage = parquet
• LightRAG: HKUDS/LightRAG, arXiv 2410.05779
• nano-graphrag: gusye1234/nano-graphrag — ~800 LOC reference impl showing the minimum storage slots
• MiniRAG: arXiv 2501.06713 — explicit graph but at the smallest reasonable footprint
• MS GraphRAG + minimum-storage mode: FalkorDB post — community_reports alone = ~75% of storage
• ParadeDB RRF reference: paradedb.com/docs/v2/sorting/hybrid
• pgvector README: pgvector/pgvector