Skip to content

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.

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:

Terminal window
npx gitpick Ygilany/AskDB/tree/main/examples/find-the-right-tables askdb-saas
cd askdb-saas
npm install

Run 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.

Load the example’s SQL into a new SQLite file:

Terminal window
node make-saas.mjs
Created saas.db: 63 tables

saas.db is a made-up SaaS business: customers, plans, subscriptions, invoices, usage, support tickets and more. Only three tables have rows:

TableRows
customers4 customers
plansBasic at $9 a month, Pro at $29 a month
subscriptions5 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:

SubscriptionPlanStatusActive today?
1ProA, started 2026-01-05Yes
2BasicC (cancelled), ended 2026-05-10No
3ProT (trial)No
4BasicP (past due), started 2026-04-20Yes
5ProA, starts 2099-01-01No

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:

Terminal window
npx askdb init --yes --database sqlite --sqlite-file ./saas.db
cp .env.example .env

Open .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):

Terminal window
npx askdb introspect
Wrote 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:

Terminal window
mkdir -p askdb/tables
cp enrichment/subscriptions.md askdb/tables/
askdb/tables/subscriptions.md
## 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.

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 scriptAskDBYour modelasks a questionfinds the 8 nearest chunkslists each table they pointtowrites SQL from thosetables onlythe promptYour scriptasks a questionAskDBfinds the 8 nearest chunksAskDBlists each table they point toYour modelwrites SQL from those tables onlythe 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
Retrieval decides what the model sees. A table no retrieved chunk points to isn’t in the prompt.

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":

askdb.config.ts
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:

Terminal window
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.

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:

Terminal window
node --env-file=.env ask.mjs
Tables in the prompt: subscription_events, subscriptions, usage_records
SELECT SUM(CASE
WHEN status IN ('A', 'P')
THEN 1
ELSE 0
END) * 10 AS monthly_recurring_revenue
FROM subscriptions
WHERE (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:

  • plans isn’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:

Terminal window
npx askdb rag query --question "What's our monthly recurring revenue from active subscriptions?"
0.439 table:public.subscriptions#cql
0.385 table:public.usage_records
0.383 table:public.subscriptions
0.381 table:public.usage_records#subscription_id
0.361 table:public.subscriptions#status
0.355 table:public.subscriptions#id
0.349 table:public.subscriptions#plan_id
0.341 table:public.subscription_events#id

The 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.

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:

askdb/tables/plans.md
---
id: 'table:public.plans'
name: plans
schemaId: askdb
aliases: [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:

askdb/concepts.md
---
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.
---
# Concepts

When 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.

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):

Terminal window
npx askdb rag index
{
"schemaId": "askdb",
"chunksTotal": 294,
"chunksIndexed": 4,
"chunksReused": 290,
"sensitiveExcluded": 0,
"sensitiveIncluded": 0
}

Ask the same question:

Terminal window
node --env-file=.env ask.mjs
Tables in the prompt: plans, subscriptions, usage_records
SELECT SUM(plans.monthly_price) AS monthly_recurring_revenue
FROM subscriptions
JOIN plans ON subscriptions.plan_id = plans.id
WHERE (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:mrr
0.576 table:public.plans#biz
0.439 table:public.subscriptions#cql
0.388 table:public.plans
0.385 table:public.usage_records
0.383 table:public.subscriptions
0.381 table:public.usage_records#subscription_id
0.361 table:public.subscriptions#status

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 query shows 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 links bring every table it needs into the prompt together.

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:

  1. Run npx askdb rag query --question "<question>" and list the tables the results point to.
  2. Name each table the right SQL needs that is missing.
  3. 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.
  4. If a term spans several tables, add a concept to askdb/concepts.md whose links name every table it needs. https://askdb.tools/reference/schema-artifact-format.md lists the fields.
  5. Run npx askdb rag index, then the query again, and confirm every needed table appears.

Write only askdb/tables/*.md and askdb/concepts.md. Never put a row or a value from the database in those files. AskDB returns SQL; this project runs it.

Docs for agentsllms.txtAGENTS.md

© 2026 Yahya Gilany