AskDB Architecture

Natural language → schema-grounded SQL. A layered monorepo of reusable contracts, database integrations, and first-party surfaces.

System Overview The central runtime boundary and high-level data path

Core Principle

AskDB returns SQL for review. The host application owns approval, execution, database roles, tenant policy, network controls, and audit logging. @askdb/core never executes generated SQL.

User / Agent Question Host Surface CLI / HTTP API Studio / Custom App loads schema, creates model, calls ask() host-owned boundary Schema v2 directory / JSON / bundle @askdb/core normalize schema assemble prompt call model validate SQL return artifact AskDialect engine-specific gen + validate BYO LanguageModel OpenAI / Azure / Bedrock … Checked SQL output artifact Host-owned Execution (outside AskDB API) Optional @askdb/rag retriever — focused DDL
High-level system boundary. AskDB returns SQL; execution stays host-owned.

Package Map All packages in the pnpm monorepo — scope, purpose, and boundaries

Core contracts & libraries
Integration packages (DB connectors)
First-party product surfaces (apps)
RAG / enrichment layer

Reusable Contracts & Libraries

@askdb/core
v0.5.0-beta · packages/core
The NL→SQL pipeline engine and the built-in dialect specs (ADR 0029). Provides ask() orchestration, Schema v2 loading/parsing, enrichment suggestions, operating modes, Pino structured logging, and retrieval input. Central contract for all other packages.
library contract
No database drivers · No generated-SQL execution · No engine-specific connector
@askdb/introspect
packages/introspect
Engine-agnostic Connector<TInput> contract, introspection orchestrator, and Schema v2 renderer. Defines the intermediate SQL IR (SqlSchema, SqlTable, SqlColumn, SqlForeignKey, …) that every connector produces.
library contract
No default connector · No engine-specific input union · No standalone binary
@askdb/rag
packages/rag
Deterministic Schema v2 chunking (table, column, cql, question, concept, relationship chunks), BYO Embedder / VectorStore / Retriever interfaces, and built-in adapters: in-memory, file-backed, pgvector. All stores (createMemoryStore, createFileStore, createPgvectorStore) and the embedder (createAiSdkEmbedder) are exported from the root barrel — no sub-path imports required. Also ships a setup-store CLI command for pgvector schema provisioning.
rag library
Optional layer — core SQL generation still flows through @askdb/core + dialect
@askdb/enrich
packages/enrich
Headless Schema v2 authoring helpers: loadWorkspace(), table draft builders, saveTable(), concepts editing, markdown preservation with frontmatter, suggestion context builders, and bundleSchemaDirectory(). Shared logic for TUI and Studio.
library headless
No terminal or browser UI · No introspection · No database connection

Integration Packages (Database Connectors)

@askdb/postgres
packages/postgres
Re-export of core's Postgres dialect spec (postgresDialect), createPostgresConnector() with live and from-export modes, catalog SQL templates (POSTGRES_TEMPLATE_BUNDLE), and optional pg-backed catalog runner.
connector dialect
pg is optional peer — only for live introspection. Generated SQL still executes outside AskDB.
@askdb/prisma
packages/prisma
Offline Prisma schema-file connector. Reads one schema.prisma or a directory of .prisma files via @prisma/internals and renders Schema v2 physical metadata. Auto-discovers the Prisma project root.
connector offline
No live database connection · No SQL dialect · Pair output with a dialect for SQL generation
@askdb/mysql
packages/mysql
MySQL/MariaDB integration. Exports MYSQL_DIALECT, MARIADB_DIALECT, createMysqlConnector(), and a mysql2-backed catalog runner. NL→SQL prompt and validator live in @askdb/core.
connector dialect
Introspection connector only · Dialect spec lives in @askdb/core
@askdb/sqlite
packages/sqlite
SQLite integration. Exports SQLITE_DIALECT, createSqliteConnector(), and a better-sqlite3-backed catalog runner for reading SQLite database files.
connector dialect
Introspection connector only · Dialect spec lives in @askdb/core
@askdb/sqlserver
packages/sqlserver
Microsoft SQL Server (T-SQL) integration. Exports SQLSERVER_DIALECT, createSqlServerConnector(), and an mssql-backed catalog runner.
connector dialect
Introspection connector only · Dialect spec lives in @askdb/core

First-party Product Surfaces (apps/)

askdb (CLI)
apps/cli · bin: askdb
Batteries-included askdb binary. Commands: ask (NL→SQL), introspect (schema discovery — engine and connection URL now resolved from askdb.config.ts without flags), enrich (launch TUI), studio (launch browser UI), init (bootstrap config), bundle (pack schema directory). Composed from all reusable packages.
CLI batteries-included
Product surface that composes packages · Not the reusable contract layer
@askdb/http-api
apps/http-api · bin: askdb-http
Minimal HTTP wrapper around @askdb/core. Exposes POST /ask → SQL, GET /health. Accepts inline schemaJson or resolves from host.schemaPath in askdb.config.ts. Supports x-correlation-id header, request-body mode, and explain.
HTTP REST
No duplicate NL→SQL implementation · No SQL execution
@askdb/studio
apps/studio · bin: askdb-studio
Local browser UI serving a React SPA. Routes: /api/workspace, /api/tables/:id, /api/suggest, /api/rag/status, /api/rag/index, /api/rag/query, /api/ask. Integrates enrichment, RAG exploration, and sample NL→SQL with token usage tracking. RAG vector store is configurable via askdb.config.ts rag.store field: memory, file (default), or pgvector (auto-provisions schema via ensureSchema()). Default schema directory resolved from config without requiring a CLI flag.
browser UI React
Authoring surface over Schema v2 · Not a connector package
@askdb/tui
packages/tui · bin: askdb-tui
Maintained terminal enrichment surface built on Ink + React. Provides interactive table/column editing, concept authoring, AI suggestion review (when OPENAI_API_KEY is set), and schema bundling. Operates on Schema v2 files only.
TUI Ink/React
Operates on files · Does not introspect or connect to databases
@askdb/docs-site
apps/docs-site · Astro + Starlight
Static documentation site built with Astro/Starlight. Mirrors MDX content from the docs directory, providing searchable guides for architecture, connectors, RAG, Studio, TUI, and the HTTP API.
static site Astro
Markdown files remain canonical for architecture and contracts

Dependency Graph Who imports whom — intentionally layered by responsibility

APPS LIBS CORE askdb CLI http-api POST /ask studio Browser UI tui Terminal UI docs-site Astro / Starlight introspect Connector contract postgres PG dialect + connector prisma Prisma connector mysql MySQL connector sqlite SQLite connector sqlserver MSSQL connector enrich Workspace helpers rag Chunking + indexing @askdb/core ask() · schema v2 · modes · logging enrichment suggestions · retrieval types dialect specs (postgres, mysql, sqlite, …) AI provider helpers · Pino logger optional peer
Solid arrows = direct import dependencies. Dashed = app-layer dependencies. Arrows point from consumer to provider. All roads lead to @askdb/core.

Dependency Matrix

Package Direct Dependencies Optional Peer Dependencies
@askdb/core ai, pino, zod, gray-matter —
@askdb/ai ai Optional peers: AI SDK provider packages (e.g. @ai-sdk/openai), loaded lazily by the built-in providers
@askdb/introspect — —
@askdb/postgres @askdb/core, @askdb/introspect pg (live introspection only)
@askdb/prisma @askdb/introspect, @prisma/internals —
@askdb/mysql @askdb/core, @askdb/introspect mysql2
@askdb/sqlite @askdb/core, @askdb/introspect better-sqlite3
@askdb/sqlserver @askdb/core, @askdb/introspect mssql
@askdb/enrich @askdb/core —
@askdb/rag @askdb/core pg (pgvector adapter), AI SDK embedder packages
@askdb/tui @askdb/core, @askdb/enrich, ink, react OPENAI_API_KEY for suggestions
@askdb/studio @askdb/core, @askdb/enrich, @askdb/rag, @askdb/postgres, react, vite AI SDK provider packages for RAG embeddings
askdb CLI @askdb/core, @askdb/introspect, @askdb/postgres, @askdb/prisma (optional peer), @askdb/studio (dynamic) pg, model provider packages
@askdb/http-api @askdb/core Model provider packages

Data Flows The four primary runtime flows through the system

1 · NL→SQL Pipeline (ask())

1 Host Surface question + schema model + dialect 2 Normalize load schema v2 resolve mode Optional RAG retrieve chunks synthesize DDL (if retriever supplied) 3 Dialect.generate build NL→SQL prompt call model (AI SDK) 4 BYO Model LLM generates SQL returns text 5 Extract + Validate parse SQL block heuristic guardrails 6 Return SQL { sql, explain? } Host receives SQL approves & executes Sensitive Handling sensitive=true columns tagged (sensitive) in DDL or omitted when --omit-sensitive-from-prompt ask({ question, schema, model, dialect }) Operating Modes schema_only · bounded_results
NL→SQL pipeline through ask(). RAG is an optional pre-step that replaces the full DDL with focused chunks.

2 · Introspection Flow

CLI / Library introspect() @askdb/introspect introspect() calls connector.describe() calls renderToSchemaV2() writes schema.json Postgres (live) CatalogQueryRunner + pg Postgres (export) from-bundle CSV files Prisma file schema.prisma parser SQL IR (SqlSchema) Tables · Columns · FKs renderToSchemaV2() emit schema.json create tables/ dir Schema v2 Dir schema.json + tables/*.md MySQL · SQLite · MSSQL connectors follow same pattern
Introspection: connectors read from any source (live DB, export bundle, schema file) and emit a uniform SQL IR that renders into Schema v2.

3 · Enrichment Flow

schema.json physical layer tables · columns · FKs @askdb/enrich loadWorkspace() buildTableDraft() saveTable() bundleSchemaDirectory() @askdb/tui Ink terminal UI table/col/concept edit @askdb/studio React browser UI suggest + RAG explore tables/*.md descriptions · aliases · CQL concepts.md domain vocabulary schema bundle JSON optional distribution artifact read-only @askdb/core loadSchema() → ask()
Enrichment adds the describable layer on top of the physical schema. TUI and Studio share workspace logic via @askdb/enrich.

4 · RAG Indexing & Retrieval Flow

Schema v2 Dir schema.json + *.md chunkSchema() table chunks column chunks cql · question concept · relationship BYO Embedder AI SDK helper AI helper or custom impl schema.lock.json content hashes BYO VectorStore in-memory (default) file-backed pgvector or custom impl Retriever fn question → QueryResult[] @askdb/core ask({ retriever }) synthesizes focused DDL
RAG indexes Schema v2 chunks once (skipping sensitive content by default). The retriever focuses the DDL block in ask() for large schemas, improving prompt quality and reducing token usage.

Schema v2 Format The describable-schema artifact — the single source of truth for NL→SQL context

On-disk Layout

my-app.schema/
  schema.json           # physical layer
  tables/
    users.md            # describable layer
    orders.md
    order_items.md
  concepts.md           # optional vocabulary
  schema.lock.json      # RAG content hashes
  schema.embeddings.json  # RAG vectors (JSON)
  schema.embeddings.bin   # RAG vectors (binary)

schema.json Structure

{
  "version": 2,
  "schemaId": "my-app",
  "provider": "postgres",  // optional
  "tables": [{
    "id": "table:public.users",
    "name": "users",
    "schema": "public",
    "columns": [{
      "id": "table:public.users#email",
      "name": "email",
      "type": "text",
      "nullable": false,
      "sensitive": true   // PII flag
    }]
  }]
}

RAG Chunk Types

table
Table name, description, aliases, primary entity
column
Column name, type, description, aliases
cql
Common query language snippets per table
question
Example questions from table markdown
concept
Cross-table domain vocabulary from concepts.md
relationship
Foreign key relationships between tables

Extension Points BYO interfaces and seams for custom integrations

Extension Point Interface Defined In When to Implement
SQL Dialect AskDialect or DialectSpec @askdb/core Add a database dialect or custom SQL generation policy. Built-in IDs: postgres, mysql, mariadb, sqlite, sqlserver, cockroachdb
Introspection Connector Connector<TInput> @askdb/introspect Add a new schema metadata source (new DB engine, export format, schema file type)
Catalog Runner CatalogQueryRunner @askdb/introspect Use a different live database client while keeping the connector unchanged
RAG Embedder Embedder = (texts: string[]) => Promise<number[][]> @askdb/rag Use a different embedding provider. Built-in helpers: AI SDK, OpenAI
Vector Store VectorStore (upsert/query/delete) @askdb/rag Use a different vector database. Built-in: in-memory, file-backed, pgvector
Retriever Retriever = (params) => Promise<QueryResult[]> @askdb/rag / @askdb/core Custom retrieval policy, hybrid search, or filtering logic
Enrichment UI Workspace, draft builders, save helpers @askdb/enrich Build a custom authoring UI without depending on TUI or Studio
Product Surface Compose packages into a workflow App packages or consumer app CLI, HTTP server, MCP server, browser SDK, Slack bot — host owns auth, policy, execution

Environment & Configuration askdb.config.ts keys and bootstrap

Key Configuration

Keys in askdb.config.ts. Bind any value to an environment variable of your choosing with env("NAME"); the CLI loads .env first. AskDB reads one variable of its own from the environment, ASKDB_DEBUG.

Config keyPurpose
ai.provideropenai | azure | foundry | google | anthropic | gateway
ai.providerConfig.<provider>.apiKeyAPI key for NL→SQL and enrichment suggestions (e.g. env("OPENAI_API_KEY"))
ai.language.modelModel ID (OpenAI default: gpt-4o-mini)
modes.askdbModeschema_only | bounded_results
modes.omitSensitiveFromPromptOmit sensitive column/table names from DDL
logging.levelPino log level: trace|debug|info|warn|error|fatal|silent
logging.correlationIdOverride correlation ID for log lines
introspection.providerConfig.postgres.databaseUrlPostgres URL for live introspection (e.g. env("DATABASE_URL"))
introspection.providerConfig.mysql.databaseUrlMySQL connection URL for introspection
introspection.providerConfig.sqlserver.databaseUrlSQL Server connection URL for introspection
introspection.providerConfig.sqlite.fileSQLite file path for introspection
host.schemaPathSchema path for HTTP API server
host.schemaJsonInline schema JSON for HTTP API
rag.embeddermock | ai — selects RAG embedder backend
ai.embedding.modelEmbedding model for rag.embedder: "ai"
ai.embedding.dimensionsEmbedding vector size to request
rag.storeConfig.pgvector.databaseUrlPostgres connection string for pgvector RAG store
rag.storeConfig.pgvector.indexStrategyivfflat | hnsw | none — pgvector index type
dev.mockSqlBypass live model in tests

CLI Commands Reference

CommandDescription
askdb initCreate askdb.config.ts and install @askdb/config
askdb ask -s <schema> -q <question>Generate SQL from schema + question
askdb introspect --out my.schemaIntrospect using engine + URL from askdb.config.ts (zero-flag mode)
askdb introspect --url $DB_URL --out my.schemaLive Postgres introspection (explicit URL)
askdb introspect --prisma --out my.schemaPrisma schema-file introspection
askdb rag setup-store --pg-url $URL --dimensions <n>Auto-provision pgvector extension, table, and indexes
askdb enrich --schema my.schemaLaunch terminal enrichment UI
askdb studio --schema my.schemaLaunch browser Studio UI
askdb bundle <dir> --out bundle.jsonBundle schema directory to JSON

HTTP API Endpoints

EndpointDescription
GET /healthHealth check
POST /askNL→SQL: { question, schemaJson?, mode?, explain? } → { ok, sql, explain?, correlationId }

Studio API Routes

RoutePurpose
GET /api/workspaceLoad workspace state
POST /api/tables/:idSave table draft
POST /api/suggestAI enrichment suggestions
GET /api/rag/statusRAG index status
POST /api/rag/indexBuild / rebuild RAG index
POST /api/rag/queryQuery RAG chunks
POST /api/askSample NL→SQL with RAG option

Build & Toolchain

📦
pnpm 11
Workspace package manager
⚡
Turborepo
Incremental monorepo builds
🔷
TypeScript 5.7
All packages · ESM-first
🧪
Vitest 3
Unit + integration tests

Install Profiles Which packages to install for each workflow

Workflow Install Optional Peers Notes
Minimal Postgres SQL generation @askdb/core ai AI SDK provider package (e.g. @ai-sdk/openai), optionally with @askdb/ai for config-driven model selection Neither @askdb/postgres nor pg needed. Uses ask({ dialect: "postgres" }); the spec is core's (ADR 0029).
Live Postgres introspection @askdb/introspect @askdb/postgres pg Or supply your own CatalogQueryRunner.
Air-gapped Postgres introspection @askdb/introspect @askdb/postgres None From-export bundles only. Templates from POSTGRES_TEMPLATE_BUNDLE.
Prisma schema-file introspection @askdb/introspect @askdb/prisma None declared Produces Schema v2 physical metadata. No dialect included.
Terminal enrichment (TUI) @askdb/tui OpenAI key for suggestions; manual authoring works without it Operates on Schema v2 files only.
Local browser Studio @askdb/studio AI SDK provider for suggestions; pg for pgvector RAG Local authoring UI, sample SQL checks, RAG exploration.
RAG (in-memory or file) @askdb/rag @askdb/core Embedder package No pg needed.
RAG with pgvector @askdb/rag @askdb/core pg + embedder package Use createPgvectorStore(); call store.ensureSchema() once to auto-provision extension, table, and indexes. Or run askdb rag setup-store --dimensions <n>. Studio auto-provisions on every request when rag.store = "pgvector".
Batteries-included CLI askdb Model provider key (env) Pulls first-party integrations automatically.
HTTP API server @askdb/http-api Model provider key (env) Set host.schemaPath in askdb.config.ts or accept inline schemaJson.