RAG Storage Schema

CREATE EXTENSION IF NOT EXISTS vector WITH SCHEMA extensions;
 
CREATE TABLE rag_documents (
  id            uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id     uuid NOT NULL,
  source_uri    text NOT NULL,
  title         text,
  content_hash  text NOT NULL,  -- sha256 of normalized source
  created_at    timestamptz DEFAULT now(),
  UNIQUE (tenant_id, source_uri)
);
 
CREATE TABLE rag_chunks (
  id            uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id     uuid NOT NULL,
  document_id   uuid NOT NULL REFERENCES rag_documents(id) ON DELETE CASCADE,
  chunk_index   int NOT NULL,
  content       text NOT NULL,
  token_count   int,
  metadata      jsonb NOT NULL DEFAULT '{}',
  embedding     extensions.vector(1536),
  content_tsv   tsvector GENERATED ALWAYS AS (
    to_tsvector('english', content)
  ) STORED,
  UNIQUE (document_id, chunk_index)
);

-- Tenant-scoped indexes
CREATE INDEX rag_chunks_tenant_hnsw ON rag_chunks
  USING hnsw (embedding extensions.vector_cosine_ops)
  WHERE embedding IS NOT NULL;
 
CREATE INDEX rag_chunks_tenant_fts ON rag_chunks USING gin (content_tsv);
CREATE INDEX rag_chunks_metadata ON rag_chunks USING gin (metadata jsonb_path_ops);
CREATE INDEX rag_chunks_tenant ON rag_chunks (tenant_id);
 
-- Idempotent ingest: skip if source unchanged
INSERT INTO rag_documents (tenant_id, source_uri, title, content_hash)
VALUES ($1, $2, $3, $4)
ON CONFLICT (tenant_id, source_uri) DO UPDATE
  SET content_hash = EXCLUDED.content_hash,
      title = EXCLUDED.title
  WHERE rag_documents.content_hash IS DISTINCT FROM EXCLUDED.content_hash
RETURNING id, (xmax = 0) AS inserted;
 
-- Retrieval with tenant filter + hybrid-ready columns
SELECT c.id,
       c.content,
       d.title AS source_title,
       c.metadata->>'page' AS page,
       c.embedding <=> $2 AS distance
FROM rag_chunks c
JOIN rag_documents d ON d.id = c.document_id
WHERE c.tenant_id = $1
  AND c.embedding IS NOT NULL
ORDER BY c.embedding <=> $2
LIMIT 8;

{
  "page": 12,
  "section": "Replication",
  "heading": "Streaming standby",
  "embed_model": "text-embedding-3-small",
  "embed_version": "2026-03-01"
}

ALTER TABLE rag_chunks ENABLE ROW LEVEL SECURITY;
 
CREATE POLICY tenant_isolation ON rag_chunks
  USING (tenant_id = current_setting('app.tenant_id')::uuid);

ALTER TABLE rag_chunks
  ADD COLUMN embed_status text NOT NULL DEFAULT 'pending'
    CHECK (embed_status IN ('pending', 'ready', 'failed'));