Chapter 74 · Tools and actions

Querying the database as a tool

Parameterised tools against generated SQL. Why text-to-SQL is the wrong default for an internal assistant, and how to get most of its flexibility with none of its risk.

11 min read

Chapter 12 routed relational data to live queries. This chapter builds those tools, and it starts with the decision that most projects get wrong.

Parameterised tools against generated SQL

// Option A: a parameterised tool per question shape.
check_inventory({ sku, warehouse? })

// Option B: text-to-SQL. One tool, arbitrary flexibility.
query_database({ sql: 'select ... from inventory where ...' })

B is more capable and it is the wrong default for a system like this. Four reasons, and only the first is the obvious one.

Permissions. Every query in sections 4 and 5 carried a permission filter in the where clause. Generated SQL either contains that filter — written by a model, correctly, every time — or it does not. Neither is acceptable, and wrapping it in a restricted view solves it only for the cases you anticipated.

Correctness is unverifiable. A parameterised tool returns a wrong answer when the arguments are wrong, which is measurable. Generated SQL returns a plausible number from a join nobody reviewed, and nothing distinguishes a correct aggregation from a subtly wrong one.

Performance. A generated query against a 40-million-row table without the right index is a production incident with a chat interface in front of it.

Schema coupling. Text-to-SQL requires the schema in context — hundreds of tokens, stale the moment a migration lands, and a disclosure of internal structure.

Text-to-SQL trades a bounded failure mode for an unbounded one. For an assistant answering recurring operational questions, the bound is worth more than the flexibility.

⚠️ There is a legitimate place for it: an analyst-facing tool, against a read replica, with a restricted role, where the user can read the SQL and is qualified to judge it. That is a different product from Atlas and the controls below do not apply to it.

The parameterised tool, properly

// src/tools/inventory.ts
export const checkInventory = registerTool({
  name: 'check_inventory',
  kind: 'read',
  description:
    'Returns current on-hand and allocated quantities for a SKU. Use for any ' +
    'question about stock, availability, how many are on hand, or whether ' +
    'something can ship. Do not use for pricing or lead times. Returns one ' +
    'row per warehouse with on_hand, allocated and updated_at. Quantities are ' +
    'live and change throughout the day.',

  schema: z.object({
    sku: z.string().regex(/^SKU-\d{4}$/),
    warehouse: z.enum(['dallas', 'reno', 'columbus']).optional(),
  }),

  async run(args, scope) {
    const { rows } = await replica.query(
      `select w.slug as warehouse, i.on_hand, i.allocated,
              i.on_hand - i.allocated as available, i.updated_at
         from inventory i
         join warehouses w on w.id = i.warehouse_id
        where i.sku = $1
          and ($2::text is null or w.slug = $2)
          -- ⚠️ The permission filter, in the query, as everywhere else.
          and w.id = any($3)
        order by w.slug`,
      [args.sku, args.warehouse ?? null, scope.warehouseIds],
    )

    if (rows.length === 0) {
      // Distinguish "no such SKU" from "no permitted warehouse". The first
      // is a correction the model can act on; the second must not reveal
      // that the SKU exists elsewhere. Chapter 63's permission rule.
      const exists = await replica.query(`select 1 from products where sku = $1`, [args.sku])
      return exists.rowCount === 0
        ? { ok: false, error: 'not_found', message: `No product with SKU ${args.sku}.` }
        : { ok: true, data: [], message: 'No stock in the warehouses available to you.' }
    }

    return {
      ok: true,
      data: rows,
      // Chapter 69: echo what was resolved, so the answer names it.
      resolved: { sku: args.sku, productName: await productName(args.sku) },
      // Chapter 15: the model can state completeness truthfully.
      complete: !args.warehouse,
      asOf: max(rows.map((r) => r.updated_at)),
    }
  },
})

Five things in that function come from earlier chapters and none is optional: the permission filter, the not-found distinction, the resolved echo, the completeness flag, and the asOf timestamp.

Covering flexibility without generated SQL

The objection to parameterised tools is that users ask things you did not anticipate. Three techniques cover most of it.

Optional dimensions

// One tool, several shapes, all bounded.
schema: z.object({
  sku: z.string().regex(/^SKU-\d{4}$/).optional(),
  warehouse: z.enum(['dallas', 'reno', 'columbus']).optional(),
  belowReorderPoint: z.boolean().optional(),
  category: z.enum(CATEGORIES).optional(),
}).refine((a) => a.sku || a.category || a.belowReorderPoint, {
  // ⚠️ At least one filter. Without this, an omitted argument returns the
  // whole table — which is a different and worse failure than an error.
  message: 'Provide a sku, a category, or belowReorderPoint.',
})

A bounded aggregate tool

// Aggregation is chapter 47's unanswerable-by-retrieval case. It is
// answerable by SQL, and it does not need generated SQL.
export const inventorySummary = registerTool({
  name: 'inventory_summary',
  description:
    'Counts and totals across inventory. Use for "how many SKUs", "what is ' +
    'the total value", "which warehouses are low". Returns one row per group.',
  schema: z.object({
    groupBy: z.enum(['warehouse', 'category', 'status']),
    metric: z.enum(['count', 'total_units', 'total_value']),
    filter: z.object({
      belowReorderPoint: z.boolean().optional(),
      category: z.enum(CATEGORIES).optional(),
    }).optional(),
  }),
  async run(args, scope) {
    // ⚠️ Column names from an allow-list, never interpolated from input.
    const groupColumn = { warehouse: 'w.slug', category: 'p.category', status: 'i.status' }[args.groupBy]
    const metricExpr = {
      count: 'count(*)', total_units: 'sum(i.on_hand)',
      total_value: 'sum(i.on_hand * p.unit_price)',
    }[args.metric]
    // ...
  },
})

Mapping an enum to a column through a literal object is the pattern that makes this safe. The input can only be one of three strings and the SQL contains no input at all.

A saved-query tool for the long tail

// Named queries, written and reviewed by a person, parameterised. This is
// where a genuinely novel question goes after it has been asked twice.
export const SAVED_QUERIES = {
  slow_moving_stock: {
    description: 'SKUs with no movement in N days, by warehouse.',
    params: z.object({ days: z.number().int().min(7).max(365) }),
    sql: `select w.slug, i.sku, i.on_hand, max(m.moved_at) as last_movement
            from inventory i join warehouses w on w.id = i.warehouse_id
            left join movements m on m.sku = i.sku and m.warehouse_id = i.warehouse_id
           where w.id = any($2)
           group by 1, 2, 3
          having coalesce(max(m.moved_at), 'epoch') < now() - make_interval(days => $1)
           order by 1, 4`,
  },
} as const

The operational point: a question asked twice becomes a saved query in ten minutes, reviewed by whoever owns the data. That covers the long tail at a rate real usage can sustain, and every query in the set has been read by a person.

Guards every database tool needs

export async function runQuery<T>(sql: string, params: unknown[], tool: string): Promise<T[]> {
  // 1. A statement timeout. A tool that can run for 30 seconds is a tool
  //    that can exhaust the pool during an incident.
  const client = await replica.connect()
  try {
    await client.query(`set local statement_timeout = '3s'`)

    // 2. A row cap, above the plausible maximum. Hitting it is a bug
    //    signal, not a result to truncate silently.
    const { rows } = await client.query(`${sql} limit ${MAX_ROWS + 1}`, params)
    if (rows.length > MAX_ROWS) {
      logger.warn('tool.row_cap_hit', { tool, cap: MAX_ROWS })
      throw new ToolError('too_many_rows',
        'That returned more rows than I can handle. Narrow it down.')
    }
    return rows
  } finally {
    client.release()
  }
}

// 3. A read-only role, so a mistake cannot write. Belt and braces over
//    the read/write split in chapter 70.
export const replica = new Pool({
  connectionString: secrets.replicaUrl,   // role has SELECT only
  max: 4,                                  // ⚠️ bounded, separate from the app pool
})

The separate bounded pool matters during an incident: a tool storm cannot starve the application of connections, because it never shares them.

Returning results the model can read

// ❌ Raw rows. The model re-derives the units and often gets them wrong.
[{ sku: 'SKU-4411', on_hand: 1240, allocated: 900, updated_at: '2026-11-03T14:22:11Z' }]

// ✅ Rendered, with units and the derived value the question needs.
{
  rows: [{ warehouse: 'Dallas', on_hand: 1240, allocated: 900, available: 340,
           updated: '2 hours ago' }],
  resolved: { sku: 'SKU-4411', productName: 'Hydraulic seal, 40mm' },
  complete: true,
  note: 'Quantities are live as of 14:22 today.',
}

Computing available in the query rather than letting the model subtract is the same principle as computing the comparison in code: arithmetic that can be done deterministically should be.

The failure mode: the tool that returned everything

inventory_summary ships without the .refine() requiring a filter.

A rep asks: "how much stock do we have?" The model calls it with groupBy: 'warehouse' and no filter, which is correct and returns three rows. Fine.

Then a rep asks: "what stock do we have that isn't moving?" The model calls check_inventory with no sku — because the schema allows it — and gets 180,000 rows. The row cap fires, the tool errors, and the model apologises.

That is the good case. The bad case is before the row cap existed: 180,000 rows serialised into the context, the request failing on token limits after 40 seconds and a large bill, and a connection held for the duration.

// Three guards, and the first is the one that should exist from the start.
// 1. The schema refinement. An unfiltered query is not a valid request.
// 2. The row cap, which errors rather than truncating.
// 3. The statement timeout and the bounded pool.

// And the test, which is one line per tool and worth having for all of them.
test.each(DB_TOOLS)('$name rejects an unfiltered request', async (tool) => {
  const result = await executeToolRequest({ tool: tool.name, arguments: {} },
                                          SCOPE.finance, freshContext())
  expect(result.ok).toBe(false)
})

Every parameter being optional means the empty request is valid, and the empty request is almost always "return the whole table". Require at least one filter, and make the requirement part of the schema so the model sees it in the error.

Key points

Parameterised tools over text-to-SQL for an internal assistant: permissions, verifiability, performance and schema coupling all argue the same way.
Text-to-SQL is legitimate for an analyst-facing tool against a replica where the user can read the SQL.
Put the permission filter in every tool's query, exactly as in retrieval.
Distinguish "not found" from "not permitted", and do not reveal existence in the second case.
Echo the resolved entity, flag completeness, and return an asOf timestamp.
Cover flexibility with optional dimensions, a bounded aggregate tool, and saved queries for the long tail — a question asked twice becomes a reviewed query in ten minutes.
Map enums to columns through a literal object. The SQL then contains no input.
Statement timeout, row cap that errors rather than truncates, read-only role, and a separate bounded pool.
Compute derived values in the query. Arithmetic the database can do should not be the model's job.
Require at least one filter in the schema, or the empty request returns the whole table.

Next: calling an internal API as a tool.