I kept running into the same uncomfortable thought while building Fitnis queries: if I gave the model raw SQL access, I'd spend more time defending query edges than shipping features.

I needed the model to answer real analytics questions from Postgres, but I also needed tight control over what it could express.

The custom pipeline in action!

The problem

In Fitnis (my AI-based workout tracker), users ask things like "how many workouts did I do this month?" or "what's my heaviest bench press?", and the agent needs to go fetch that data from Postgres.

The tempting shortcut is a generic execute_sql tool, but that means the model is writing raw SQL strings, and you're trying to validate them after the fact.

I didn't want to be in the business of parsing arbitrary SQL to figure out if it's safe.

The solution: A structured query DSL

Instead of SQL, I had the model generate a structured JSON object that describes what it wants. A server-side QueryBuilder then compiles that into parameterised SQL with all the safety stuff baked in. That's basically the whole idea.

So when a user asks "how many workouts did I do this month?", the model generates something like this:

{
  "select": [{ "expression": { "type": "count_all" }, "alias": "total" }],
  "from": "workouts",
  "where": {
    "type": "comparison",
    "operator": ">=",
    "left": { "type": "column", "column": "started_at" },
    "right": {
      "type": "date_function",
      "function": "date_trunc",
      "unit": "month",
      "expression": { "type": "date_function", "function": "current_date" }
    }
  },
  "explanation": "Count workouts started this month"
}

...and the server turns that into safe, parameterised SQL. The model never touches SQL directly; it just expresses intent.

Two schemas: compact for the LLM, strict for the server

This ended up being the key design decision. I built two separate Zod schemas: a compact one that the LLM sees as its tool input, and a strict one that the server validates against before compiling.

The compact schema uses shallow nesting and z.record(z.unknown()) for recursive structures. Because the full recursive schema would've been massive (way too many tokens for a tool definition), I needed something the model could actually work with:

// Compact nested placeholder; avoids infinite recursion in JSON Schema
const nestedExpr = z.record(z.unknown());
 
const exprSchema = z.discriminatedUnion("type", [
  z.object({
    type: z.literal("column"),
    table: z.string().optional(),
    column: z.string(),
  }),
  z.object({
    type: z.literal("literal"),
    value: z.union([z.string(), z.number(), z.boolean(), z.null()]),
  }),
  z.object({ type: z.literal("count_all") }),
  z.object({
    type: z.literal("aggregate"),
    function: z.enum(["count", "sum", "avg", "min", "max"]),
    expression: nestedExpr,
  }),
  z.object({
    type: z.literal("date_function"),
    function: z.enum(["date_trunc", "extract", "current_date", "current_timestamp"]),
    unit: z.string().optional(),
    expression: nestedExpr.optional(),
  }),
  // ... more expression types
]);

The compact schema is intentionally permissive; z.record(z.unknown()) will accept basically anything for nested expressions.

That's fine, because it's not the security boundary. The strict server-side schema is. It does full recursive validation, column whitelisting and structural checks before anything gets compiled to SQL. All the stuff that would've made the LLM-facing schema too large to fit in a tool definition; it lives here instead.

Teaching it which tables exist

Zod schemas tell the model what's valid, but I found that inputExamples were way more effective at teaching it how to structure real queries. A few-shot example is worth a thousand words of documentation:

'execute-query': tool({
  description: QUERY_SCHEMA_DESCRIPTION,
  inputSchema: toolQuerySchema,
  inputExamples: [
    {
      input: {
        select: [{ expression: { type: 'count_all' }, alias: 'total' }],
        from: 'workouts',
        where: {
          type: 'comparison',
          operator: '>=',
          left: { type: 'column', column: 'started_at' },
          right: { type: 'date_function', function: 'date_trunc', unit: 'month',
            expression: { type: 'date_function', function: 'current_date' } },
        },
        explanation: 'Count workouts started this month',
      },
    },
  ],
  // ...
})

The tool description itself includes an inline table reference so the model knows exactly what columns exist (no guessing, no hallucinating column names):

TABLES & COLUMNS:
- workouts: id, name, description, scheduled_at, started_at, completed_at
- workout_exercises: id, workout_id, exercise_id, target_sets, target_reps, weight
- workout_sets: id, workout_id, exercise_id, set_number, actual_reps, weight
- exercises (shared): id, name, description, muscle_group

What if the model guesses wrong?

Sometimes the model generates a query spec that doesn't pass validation; maybe it references a column that doesn't exist, or tries to nest conditions too deep. When that happens, the QueryBuilder throws a QueryBuildError with a descriptive message, and that error gets returned to the model as the tool result. The model then self-corrects and tries again with a valid spec. Because the error messages are specific (e.g. "Column 'user_id' cannot be referenced directly"), the model almost always gets it right on the second attempt.

Security layers baked into the builder

Onto the fun bit - the guardrails. The QueryBuilder enforces multiple safety constraints. I wanted these to be structural; not "please don't do this" validation, but stuff that isn't really possible to get around.

Read-only by design. The builder can only produce SELECT statements. There's no syntax in the DSL for INSERT, UPDATE, or DELETE; mutations simply aren't expressible.
Automatic user scoping. Every query gets a WHERE user_id = $1 injected automatically. The model never sees or references user_id, and even if it tries, the builder rejects it. Because user scoping is handled entirely by the builder, the model has no path to querying another user's data:

if (fromConfig.hasUserId) {
  conditions.push(`${spec.from}.user_id = $1`);
} else if (!fromConfig.isShared) {
  // Must filter via joined table
  const userIdTable = spec.joins.find((j) => schemaConfig.tables[j.table]?.hasUserId);
  if (userIdTable) {
    conditions.push(`${userIdTable.table}.user_id = $1`);
  }
  // else: validateJoins() has already rejected queries that would reach here
}

(The one exception is shared reference tables like exercises; they contain no user-owned data, so they skip user scoping entirely. They're read-only lookup tables that every user can query)

RLS: On top of this, I've got Postgres RLS enabled on every user-facing table; so even if the builder had a bug and missed the user scope, the database itself would reject the query. Belt and suspenders.
Column whitelisting: Every column reference gets validated against a known schema config. Because the builder rejects anything it doesn't recognise, the model can't reference internal columns or probe the schema:

private validateColumnRef(table: string | undefined, column: string): void {
  if (column === 'user_id') {
    throw new QueryBuildError('Column user_id cannot be referenced directly');
  }
  // ... validate against known columns
}

Complexity limits: I added hard caps to prevent the model from generating queries that could hammer the database. These are generous enough for any reasonable user question, but they stop runaway joins or deeply nested conditions:

const MAX_JOINS = 3;
const MAX_SELECT_EXPRESSIONS = 10;
const MAX_CONDITION_DEPTH = 4;

Enforced LIMIT: Every query gets a LIMIT clause, even if the model doesn't specify one. Because there's no legitimate reason for a fitness app query to return thousands of rows:

const limit = Math.min(spec.limit ?? DEFAULT_LIMIT, MAX_LIMIT);
parts.push(`LIMIT $${this.paramIndex++}`);
this.params.push(limit);

Parameterised values, whitelisted identifiers. All literal values become parameterised placeholders ($2, $3, etc.), and identifiers like table names, column names and date-trunc units are validated against a strict whitelist before being interpolated.
Because the model never produces SQL strings (only structured JSON) and every part of the output is either parameterised or whitelisted, SQL injection isn't really possible.

What this ended up getting me

No SQL injection surface because the model generates JSON, not SQL strings, so there's nothing to inject into
Automatic user isolation since every query is scoped to the authenticated user without the model needing to know about it
Self-correcting queries because validation errors go back to the model, and it almost always fixes them on retry
Bounded complexity from hard caps on joins, conditions and result sizes, so the model can't accidentally write a query that takes down the database
A compact tool interface because the two-schema approach keeps the LLM's tool definition small (~token-efficient) while the server does the heavy validation

What's still rough

The strict schema + query builder layer adds real maintenance overhead. Every time I add a new query capability, I need to update schema definitions, validation rules and compiler branches in lockstep. It's worth it for safety, but it's definitely not a "quick and dirty" setup.

TL;DR

If you're giving an LLM database access, don't let it write raw SQL; have it generate structured query specs that you compile server-side with all the guardrails baked in.

Takeaways

Two separate schemas (compact for the model, strict for the server). The recursive Zod schema that the server needs for full validation would've been massive as a tool definition. Keeping them separate meant the model gets a small, learnable interface and the server gets thorough validation. Both sides win.
inputExamples over documentation. I spent time writing detailed schema descriptions, but the few-shot examples ended up doing most of the heavy lifting. The model learns structure from examples way faster than from prose.
Structural security over validation-based security. Instead of trying to detect and reject dangerous queries, I made it so the model can't express dangerous queries in the first place. User scoping, column whitelisting, complexity limits; they're all enforced at the compilation layer, not the input layer.
Descriptive error messages back to the model. When a query spec fails validation, the model gets a clear explanation of what went wrong. This turns validation errors into a feedback loop rather than a dead end.

Letting an LLM loose on my database