RAG for large schemas

Scale-up guide

Big databases need retrieval. @askdb/rag chunks the same schema artifact and indexes it in a vector store. Per question, AskDB retrieves the relevant slice instead of stuffing the whole thing into every prompt.

When you need it

A rule of thumb: if your schema artifact has more than ~22 tables, or if rendered DDL routinely exceeds 8K tokens, you’ll save money and improve generation quality by retrieving instead of stuffing.

When you pass a retriever, ask() retrieves on every call, unless you also pass the schema’s chunk count: see Wire retrieval into ask().

Indexing chunks the schema artifact and stores the chunks’ embeddings, once per schema change. Each ask() call embeds only the question, retrieves the nearest chunks, and lists in the prompt each table they point to.

Install

npm install @askdb/rag ai

pnpm add @askdb/rag ai

yarn add @askdb/rag ai

For the pgvector store (recommended), add the Postgres driver:

npm install pg

pnpm add pg

yarn add pg

You’ll also need an embedding model. The Vercel AI SDK providers from Bring your own model supply them too, such as OpenAI’s text-embedding-3-small, which the examples below use.

Set up pgvector

pgvector is the recommended store: it lives in Postgres alongside your other data, you already know how to back it up, and AskDB ships ready-made DDL.

There are three provisioning paths — pick the one that matches your deploy story.

Path 1: Studio (local development)

Studio creates the pgvector table the first time it builds the index. Configure the embedding model and the store in askdb.config.ts:

import { defineConfig, env } from "@askdb/config";

export default defineConfig({
  ai: {
    provider: "openai",
    providerConfig: { openai: { apiKey: env("OPENAI_API_KEY") } },
    embedding: { model: "text-embedding-3-small" },
  },
  rag: {
    embedder: "ai",
    store: "pgvector",
    storeConfig: {
      pgvector: {
        databaseUrl: env("PGVECTOR_URL"),
      },
    },
  },
  // ...
});

rag.embedder: "ai" embeds with the ai.embedding model, and ai.embedding.model is required: AskDB has no default embedding model. Its provider defaults to ai.provider; to keep a language model from a provider without embeddings (such as Anthropic), set ai.embedding.provider and give that provider a connection. You don’t set the vector width: AskDB learns it from the model when it first builds the index, and creates the pgvector table at that width. Set ai.embedding.dimensions only to request a smaller size from a model that lets you choose one.

Launch Studio (npx askdb studio --schema ./my-app.schema) and build the index from the RAG Index page; the first build creates the table.

Path 2: `askdb rag setup-store` (CI / staging)

One idempotent command. All DDL uses IF NOT EXISTS so it’s safe to re-run on every deploy. setup-store has no embedding model to ask, so pass the width yours returns (1536 for text-embedding-3-small; detectEmbeddingDimensions(embedder) tells you for any model).

npx askdb rag setup-store \
  --pg-url "$PGVECTOR_URL" \
  --dimensions 1536

--dimensions is required, and 1536 here is text-embedding-3-small’s width: use your model’s. --pg-url and --pg-table fall back to rag.storeConfig.pgvector in askdb.config.*. If the table already exists with different dimensions, the command fails instead of silently keeping the old size. See askdb rag for every flag.

Drop it into a deploy step:

- name: Provision pgvector schema
  run: npx askdb rag setup-store --pg-url "${{ secrets.PGVECTOR_URL }}" --dimensions 1536  # text-embedding-3-small's width

Path 3: Explicit migration (production)

For production teams that want all DDL in a versioned migration file:

import { createPgvectorStore } from "@askdb/rag/stores/pgvector";

const store = createPgvectorStore({
  connectionString: process.env.DATABASE_URL!,
  dimensions: 1536, // your embedding model's width; detectEmbeddingDimensions(embedder) tells you
});

console.log(store.setupSql());

Paste the output (CREATE EXTENSION, CREATE TABLE, CREATE INDEX) into Flyway, Liquibase, Drizzle, or whatever migration runner your team uses.

Index the schema artifact

Index your schema’s chunks. If none of the paths above created the table yet, ensureSchema() creates it at the width the embedding model returns:

import {
  buildSchemaIndex,
  createAiSdkEmbedder,
  createPgvectorStore,
  detectEmbeddingDimensions,
  loadChunkerSourcesFromDir,
} from "@askdb/rag";
import { openai } from "@ai-sdk/openai";

const embedder = createAiSdkEmbedder({
  model: openai.textEmbeddingModel("text-embedding-3-small"),
});

const store = createPgvectorStore({
  connectionString: process.env.PGVECTOR_URL!,
  dimensions: await detectEmbeddingDimensions(embedder), // the width the model returns
});
await store.ensureSchema(); // creates the table if needed; refuses an existing table of another width

const { retriever, stats } = await buildSchemaIndex({
  schema: loadChunkerSourcesFromDir("./my-app.schema"),
  store,
  embedder,
  embedderId: "openai:text-embedding-3-small", // changes force a full re-embed
  lockFilePath: "./my-app.schema/schema.lock.json", // records embedder, store, dimensions
});

buildSchemaIndex chunks the artifact, embeds only the chunks the store doesn’t already hold, and returns a ready-to-use retriever plus indexing stats. A changed embedderId re-embeds every chunk, and force: true re-embeds them unconditionally. The lock file records the embedder, store and vector width.

Wire retrieval into `ask()`

Pass the retriever to ask(). AskDB calls it with the question. The prompt then lists, in full, each table a retrieved chunk points to, instead of every table. Each listed table shows its foreign keys to the other listed tables. Retrieved common query language and business context go under their table, each listed column’s note goes on its line, and the retrieved concepts, example questions and tenant policy text follow the tables. Business context and column notes reach the prompt since 1.0.0-beta.53, with or without retrieval; before that, they only steered retrieval. Privacy model lists everything that reaches the model.

With a tenant policy, the prompt’s tenant block names only those tables too. A table outside them appears there only on a join path a retrieved table needs, and a root whose table wasn’t retrieved appears by its label and placeholder (#771).

If you just ran buildSchemaIndex in the same process, pass its returned retriever directly. To retrieve in a separate process (the common production shape), rebuild one from the store:

import { createRetriever } from "@askdb/rag";

// askdb initialized at startup — see Embed in a Node app.
const retriever = createRetriever({ store, embedder });

const { sql } = await askdb.ask(question, { retriever });

With only a retriever, ask() retrieves on every call. To let a small schema skip retrieval, also pass totalSchemaChunkCount, the stats.chunksTotal that buildSchemaIndex returns. At or below retrievalThresholdChunks (30 by default), ask() then sends the whole schema instead.

Sensitive fields and embeddings

Sensitive content stays out of the index by default. The chunker leaves out the text you wrote about sensitive tables and columns (descriptions, aliases, primary entity, enum values, notes, common query language, example questions and business context), and any text that names a sensitive column, whichever table it describes. Column names and types stay available to the model through ask()’s prompt. Privacy model says what the embedding model receives.

Set chunkOptions: { includeSensitiveDescribable: true } only if your trust model allows embedding that content. Chunks kept this way carry sensitive: true. With omitSensitiveIdentifiersFromNlToSqlPrompt on, ask() leaves the text of any retrieved chunk that carries sensitive: true or names a sensitive column out of the prompt, whichever retriever returned it, and still lists the tables the chunk points to. See the schema artifact contract on GitHub for the full rules.

Custom table or dimensions

Several schemas can share one table: chunk ids include the schema id, and reindexing one schema never removes another schema’s chunks. Use a separate table when deployments use different embedding models or dimensions. To isolate multiple AskDB deployments in the same Postgres database, pass a custom table name and a matching embedding dimension:

npx askdb rag setup-store \
  --pg-url "$DATABASE_URL" \
  --pg-table myapp_rag_chunks \
  --dimensions 1536

1536 is text-embedding-3-small’s width; pass your model’s. Match the same table value in askdb.config.ts under rag.storeConfig.pgvector.table, which Studio and askdb rag read; with it set there, askdb rag needs no --pg-table.