Run safely in production

Production checklist

AskDB runs its SQL checks on the SQL before it’s returned. Those checks catch common model mistakes, but they aren’t a security boundary. Your application and database own everything that depends on runtime context. This page is the checklist for that side of the boundary. For what AskDB does and doesn’t check, see Safety boundaries.

Database role

Connect under a role that can’t write — even if AskDB’s SQL checks miss something. This is the single most important defense.

CREATE ROLE askdb_reader LOGIN PASSWORD '…';
GRANT CONNECT ON DATABASE myapp TO askdb_reader;
GRANT USAGE ON SCHEMA public TO askdb_reader;
-- Grant only the tables AskDB is meant to query. Leave sensitive tables out.
GRANT SELECT ON public.orders, public.customers TO askdb_reader;
-- Start every transaction this role opens as read-only.
ALTER ROLE askdb_reader SET default_transaction_read_only = on;

Table grants alone don’t make a Postgres role read-only. Every role can still call some built-in functions that write, for example lo_from_bytea, which creates a large object. AskDB’s function denylist blocks the known ones, but this role exists for SQL that gets past those checks. With default_transaction_read_only on, such a call fails with cannot execute lo_from_bytea() in a read-only transaction. The setting applies to new sessions of that role, and a session can change its own default, so also run each generated query inside a read-only transaction (BEGIN READ ONLY … ROLLBACK on Postgres, START TRANSACTION READ ONLY on MySQL).

Use that role’s credentials in the connection string AskDB’s consumers use. Don’t reuse your application’s admin user.

System catalogs

On Postgres you can’t take catalog read access away from a single role. pg_catalog and information_schema are readable through PUBLIC, which every role belongs to, so a REVOKE … FROM askdb_reader on them has no effect. Through the catalogs, askdb_reader can see the names and columns of every table in the database, view definitions, and function source code. It still can’t read rows from a table it has no SELECT on.

If that metadata matters, pick one of these:

Accept catalog visibility. Most deployments do. Keep the data itself out of reach with the table and column grants above, which the catalogs don’t bypass.
Use a dedicated database. Catalogs such as pg_class describe only the objects in the current database, so a database (for example a reporting copy) that holds only the tables AskDB may query shows only those tables. Cluster-wide catalogs such as pg_roles and pg_database still list every role and database.
Revoke from PUBLIC. REVOKE SELECT ON pg_catalog.pg_class, pg_catalog.pg_proc FROM PUBLIC; blocks direct reads of those catalogs, but for every role in that database: your application, psql’s \d, and the role AskDB’s introspection runs as lose them too, so grant them back to the roles that need them. It also doesn’t hide everything: views such as pg_tables, casts such as 'public.secrets'::regclass, and functions such as pg_get_functiondef still return catalog data. Test it against your own tooling before you rely on it.

Connection limits

Set a statement timeout and a small pool. Generated SQL should be fast — a slow query is usually a sign of a malformed question or a missing index, not a real long-running report.

import { Pool } from "pg";

const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
  max: 10,
  statement_timeout: 5000,        // 5s — adjust to your workload
  idle_in_transaction_session_timeout: 5000,
});
// An idle client whose connection drops emits on the pool; without a listener, the process exits.
pool.on("error", (err) => console.error({ err }, "idle database client error"));

Row limits

The SQL ask() returns has no row limit, so a question like “show me every row in this 100M-row table” can load the whole table into your server and your UI. Cap the rows in your application: Cap the rows a query returns has a helper for each engine that keeps the query’s ORDER BY.

Audit logging

Log every question, every generated SQL string, the user who asked, and the outcome. Keep it in your existing logging system — AskDB doesn’t log on your behalf.

import { createAskDbLogger } from "@askdb/core";
import { logger } from "./logger.js";

// askdb initialized at startup with createAskDb() — see Embed in a Node app.
const correlationId = crypto.randomUUID();
logger.info({ correlationId, userId, question }, "askdb.question");

try {
  // AskDB's own pipeline events, under the same correlation ID.
  const askdbLogger = createAskDbLogger({ correlationId, level: "info" });
  const { sql, tenantParams } = await askdb.ask(question, { logger: askdbLogger });
  logger.info({ correlationId, sql }, "askdb.generated");

  // runCapped: see Cap the rows a query returns.
  const { rows } = await runCapped(sql, tenantParams);
  logger.info({ correlationId, rowCount: rows.length }, "askdb.executed");
  return rows;
} catch (error) {
  logger.error({ correlationId, error }, "askdb.error");
  throw error;
}

createAskDbLogger writes AskDB’s structured pipeline events as JSON to stderr, each tagged with the correlation ID; createAskDbLogger lists its options. To send them to your own Pino logger instead, pass that as logger: it fits AskDbLogger.

Approval flows

For high-stakes questions — anything that touches sensitive data, or that runs against production reporting databases — show the SQL to a human before executing.

A common pattern:

Call ask() and return { sql } (no execution).
Render the SQL in the UI with the question for context.
On user confirmation, POST the SQL back to a separate execution endpoint that runs it under the read-only role.

This works because AskDB returns checked SQL as a value — your application controls when, if, and how it runs.

Sensitive columns

Mark sensitive tables and columns in the schema artifact, and decide whether the prompt tags them or leaves them out. Keep sensitive columns out of prompts covers marking them, the prompt setting, checking the SQL, the database role and the RAG index.

Multi-tenancy

If your app is multi-tenant, make the database the tenant boundary: run generated SQL under Postgres row-level security keyed on a per-request setting, or your engine’s equivalent. Enforce tenancy in the database covers the policies, the per-request transaction, and the checks that show the setup works.

On AskDB’s side, declare tenant policy in the schema artifact and pass a tenantScope to every ask() call. AskDB prompts the model to filter by tenant, binds your tenant IDs, and runs the tenant check, a heuristic that looks for a tenant predicate on each scoped table. That check doesn’t prove the query is scoped correctly. By default (tenantSqlMode: "sql-params") the IDs are driver parameters, never part of the SQL text: run result.sql with result.tenantParams, or result.unboundSql with result.params. Keep that default in production. sql-only inlines the IDs as literals, for tools that take one SQL string. See Multi-tenancy.