Find the right tables in a large schema
In this tutorial, you’ll ask a 63-table schema for its monthly recurring revenue, with retrieval choosing the tables the model sees. That’s RAG (retrieval-augmented generation): AskDB retrieves part of the schema for each question. The first answer is wrong, because retrieval leaves out the table that holds the prices. You’ll find out why, describe that table and the revenue term, and get the right figure.
Before you start
Section titled “Before you start”You need:
- Node.js 22.23.4 or newer.
- An OpenAI API key. The tutorial uses it for embeddings and for the SQL.
- The Quickstart helps but isn’t required. This tutorial uses the same commands on a bigger database.
Fetch the example and install its packages. gitpick copies one folder of a GitHub repo:
npx gitpick Ygilany/AskDB/tree/main/examples/find-the-right-tables askdb-saascd askdb-saasnpm installRun every command on this page in the askdb-saas folder. The folder is examples/find-the-right-tables in the AskDB repo. Its finished/ folder holds the files you’ll write, to compare with yours.
The output on this page is from recorded runs with text-embedding-3-small and gpt-4o-mini on 2026-11-16. The model writes the SQL, so yours can differ.
1. Create the database
Section titled “1. Create the database”Load the example’s SQL into a new SQLite file:
node make-saas.mjsCreated saas.db: 63 tablessaas.db is a made-up SaaS business: customers, plans, subscriptions, invoices, usage, support tickets and more. Only three tables have rows:
| Table | Rows |
|---|---|
customers | 4 customers |
plans | Basic at $9 a month, Pro at $29 a month |
subscriptions | 5 subscriptions, each on one plan through plan_id |
A subscription is active when its status is A (active) or P (past due), it has started, and it hasn’t ended:
| Subscription | Plan | Status | Active today? |
|---|---|---|---|
| 1 | Pro | A, started 2026-01-05 | Yes |
| 2 | Basic | C (cancelled), ended 2026-05-10 | No |
| 3 | Pro | T (trial) | No |
| 4 | Basic | P (past due), started 2026-04-20 | Yes |
| 5 | Pro | A, starts 2099-01-01 | No |
So the right monthly recurring revenue is $29 + $9 = $38. Keep that number in mind.
2. Set up AskDB and describe subscriptions
Section titled “2. Set up AskDB and describe subscriptions”Create the config. askdb init writes askdb.config.ts and an .env.example that lists the variables it reads. Copy that to .env:
npx askdb init --yes --database sqlite --sqlite-file ./saas.dbcp .env.example .envOpen .env and put your key after OPENAI_API_KEY=. Leave OPENAI_MODEL empty. The askdb commands read .env themselves. Then read the database’s structure into the schema artifact (the files that describe your tables to the model):
npx askdb introspectWrote askdb/schema.json: 63 tables (sqlite).Column names don’t say which subscriptions are active. The example’s enrichment/subscriptions.md holds that rule, the one Enrich your schema writes. Copy it into the artifact:
mkdir -p askdb/tablescp enrichment/subscriptions.md askdb/tables/## Common query language
- An active subscription has status A or P (past due still counts), started_at on or before today, and ended_at NULL or after today. A trial (T) is not active.The file’s front matter, not shown here, explains the one-letter status codes.
3. Index the schema
Section titled “3. Index the schema”A prompt with 63 tables is long and costly, and it buries the few tables a question needs. Retrieval sends only those. AskDB splits the artifact into chunks: one for each table, each column and each rule you wrote. For each question, it finds the 8 chunks nearest in meaning and puts only the tables they point to in the prompt.
- Your script: asks a question
- → finds the 8 nearest chunks
- AskDB: finds the 8 nearest chunks
- → lists each table they point to
- AskDB: lists each table they point to
- → writes SQL from those tables only: the prompt
- Your model: writes SQL from those tables only
askdb init set rag.embedder to "mock", a word-matching stand-in for tests. Switch to a real embedding model in askdb.config.ts. Name the model under ai.embedding, then set rag.embedder to "ai":
import { defineConfig, env, type AskDbConfig } from "@askdb/config";
export default defineConfig({ ai: { provider: "openai", providerConfig: { openai: { apiKey: env("OPENAI_API_KEY"), }, }, language: { model: env("OPENAI_MODEL"), }, embedding: { model: "text-embedding-3-small", }, }, introspection: { provider: "sqlite", providerConfig: { sqlite: { file: "./saas.db", }, }, outputDir: "./askdb", }, rag: { embedder: "ai", store: "file", storeConfig: { file: {}, }, },} satisfies AskDbConfig);Then index the artifact:
npx askdb rag index{ "schemaId": "askdb", "chunksTotal": 292, "chunksIndexed": 292, "chunksReused": 0, "sensitiveExcluded": 0, "sensitiveIncluded": 0}The index is in askdb/schema.embeddings.bin and askdb/schema.embeddings.json. AskDB embedded the chunks’ text only; it never reads the database’s rows.
4. Ask about revenue
Section titled “4. Ask about revenue”The example’s ask.mjs asks “What’s our monthly recurring revenue from active subscriptions?” It passes AskDB a retriever built on the index you just made. It prints the tables retrieval put in the prompt, the SQL, and the result of running that SQL on saas.db, read-only. --env-file=.env gives the script your key, and with OPENAI_MODEL empty it uses gpt-4o-mini:
node --env-file=.env ask.mjsTables in the prompt: subscription_events, subscriptions, usage_recordsSELECT SUM(CASE WHEN status IN ('A', 'P') THEN 1 ELSE 0 END) * 10 AS monthly_recurring_revenueFROM subscriptionsWHERE (status IN ('A', 'P')) AND started_at <= date('now') AND (ended_at IS NULL OR ended_at > date('now'));┌─────────┬───────────────────────────┐│ (index) │ monthly_recurring_revenue │├─────────┼───────────────────────────┤│ 0 │ 20 │└─────────┴───────────────────────────┘The answer is $20, not $38. Notice three things:
plansisn’t in the prompt. The prices live there, so the model never saw a price.- The model guessed one. It counted the active subscriptions and multiplied by 10. In three recorded runs, it guessed $10 twice and $100 once.
- Nothing failed. The SQL is a valid read-only query, so AskDB’s SQL checks pass. They check the query’s shape, not whether its numbers are right.
If your first answer comes out right, the model guessed well this time. Look at the first line: plans is still missing from the prompt.
To see why, look at what retrieval returned. askdb rag query prints the chunks it would hand the model:
npx askdb rag query --question "What's our monthly recurring revenue from active subscriptions?"0.439 table:public.subscriptions#cql0.385 table:public.usage_records0.383 table:public.subscriptions0.381 table:public.usage_records#subscription_id0.361 table:public.subscriptions#status0.355 table:public.subscriptions#id0.349 table:public.subscriptions#plan_id0.341 table:public.subscription_events#idThe command prints JSON. This block shows each result’s score, higher meaning nearer, and its id, shortened. #cql is the common query language section you copied, and any other name after # is a column. All 8 results come from subscription and usage tables. The plans table has no text beyond its own name and its column names: id, name and monthly_price. Nothing about it looks near the question.
5. Describe plans and the revenue term
Section titled “5. Describe plans and the revenue term”Retrieval can only match the words a chunk holds. So give plans words people use, and write down what monthly recurring revenue means.
First, describe the table. Create askdb/tables/plans.md:
---id: 'table:public.plans'name: plansschemaId: askdbaliases: [subscription plans, pricing plans, pricing tiers]columns: - id: 'table:public.plans#monthly_price' description: The plan's price per month, in US dollars.---# Table: plans
The subscription plans a customer can be on, with each plan's monthly price. Every subscription points to one plan through subscriptions.plan_id.
## Business context
- Monthly recurring revenue (MRR) is the sum of plans.monthly_price over active subscriptions, joined through subscriptions.plan_id.The aliases, the description and the business context each become text that retrieval can match. The aliases and description reach the model whenever plans is in the prompt. The business context reaches it when retrieval returns its chunk.
Then write down the term itself. A concept is a business term that spans tables, written in askdb/concepts.md. Revenue needs both subscriptions and plans, so it’s a concept. Create the file:
---concepts: - id: concept:mrr label: Monthly recurring revenue synonyms: [MRR, recurring revenue, monthly revenue] links: [table:public.subscriptions, table:public.plans] description: The sum of plans.monthly_price over active subscriptions. Join subscriptions.plan_id to plans.id.---
# ConceptsWhen retrieval returns a concept, AskDB puts every table in its links into the prompt. So a question about revenue brings both subscriptions and plans, even if neither table’s own chunks rank high.
6. Index and ask again
Section titled “6. Index and ask again”Index again. AskDB embeds only the chunks that are new or changed: two new ones (the business context and the concept) and two changed ones (the plans table and its price column):
npx askdb rag index{ "schemaId": "askdb", "chunksTotal": 294, "chunksIndexed": 4, "chunksReused": 290, "sensitiveExcluded": 0, "sensitiveIncluded": 0}Ask the same question:
node --env-file=.env ask.mjsTables in the prompt: plans, subscriptions, usage_recordsSELECT SUM(plans.monthly_price) AS monthly_recurring_revenueFROM subscriptionsJOIN plans ON subscriptions.plan_id = plans.idWHERE (subscriptions.status IN ('A', 'P')) AND subscriptions.started_at <= date('now') AND (subscriptions.ended_at IS NULL OR subscriptions.ended_at > date('now'));┌─────────┬───────────────────────────┐│ (index) │ monthly_recurring_revenue │├─────────┼───────────────────────────┤│ 0 │ 38 │└─────────┴───────────────────────────┘plans is in the prompt, the model joins it, and the answer is $38. All three recorded runs returned 38.
askdb rag query shows what changed. The concept and the plans business context (#biz) now come first:
0.593 concept:mrr0.576 table:public.plans#biz0.439 table:public.subscriptions#cql0.388 table:public.plans0.385 table:public.usage_records0.383 table:public.subscriptions0.381 table:public.usage_records#subscription_id0.361 table:public.subscriptions#statusWhat you built
Section titled “What you built”You indexed a 63-table schema, found a question that retrieval answered wrong, and fixed it with two files that belong in the artifact, next to schema.json. Along the way:
- Retrieval decides what the model sees. A table that no retrieved chunk points to isn’t in the prompt, and the model can’t use columns it never saw.
- A wrong answer can pass every check. The SQL checks test the query’s shape. Test the questions your users ask, and check the numbers.
askdb rag queryshows why. Run it with a question to see the chunks retrieval returns, before the model is involved.- Describe the tables a question reaches through a join. Aliases, a description and business context give retrieval words to match.
- A term that spans tables is a concept. Its
linksbring every table it needs into the prompt together.
Use with your agent
Section titled “Use with your agent”To hand this task to a coding agent, copy the prompt and paste it into the agent.
My AskDB project uses retrieval (
rag.embedder: "ai"), and some answers are wrong because a table the SQL needs isn’t in the prompt. Follow https://askdb.tools/find-the-right-tables.md for the method.For each question I give you:
- Run
npx askdb rag query --question "<question>"and list the tables the results point to.- Name each table the right SQL needs that is missing.
- For each missing table, write or extend
askdb/tables/<table>.md: a description, aliases people use, and business context that names the measures it feeds.- If a term spans several tables, add a concept to
askdb/concepts.mdwhoselinksname every table it needs. https://askdb.tools/reference/schema-artifact-format.md lists the fields.- Run
npx askdb rag index, then the query again, and confirm every needed table appears.Write only
askdb/tables/*.mdandaskdb/concepts.md. Never put a row or a value from the database in those files. AskDB returns SQL; this project runs it.
Read next
Section titled “Read next”- RAG for large schemas: Set up retrieval in your own app, with a pgvector store and
ask(). - Enrich your schema: Write the rules and descriptions a model needs, and test them.
- The schema artifact: What each file in the artifact holds, and what reaches the model.
- Schema artifact format: Every field of a table file and of
concepts.md.
Docs for agentsllms.txtAGENTS.md
© 2026 Yahya Gilany

