Predicate codegen – one predicate, three targets

Every full-stack app pays the dual-write tax for filter logic: a WHERE clause for the API, a filter() for the client, a Zod refinement for validation. Three sources of truth, three drift surfaces, three bugs waiting to happen.

Directive's FactPredicate is a data form – a JSON object describing "what must be true." Because its operator set ($eq, $gte, $in, $matches, $between, $contains, $all / $any / $not) is a proper subset of every modern query language, the same predicate can be evaluated on the client, compiled to parameterized SQL on the server, mapped to a MongoDB query, or rendered as a PostgREST querystring – without rewriting it.

One predicate, three targets

import {
  predicateToSQL,
  predicateToMongo,
  predicateToPostgrest,
  evaluatePredicate,
} from "@directive-run/core";

const adults = {
  age: { $gte: 18 },
  status: { $in: ["active", "pending"] },
};

// Client
evaluatePredicate(adults, user); // boolean

// Server (Postgres)
predicateToSQL(adults, { table: "users" });
// → {
//     sql: "SELECT * FROM users WHERE (age >= $1 AND status = ANY($2))",
//     where: "(age >= $1 AND status = ANY($2))",
//     params: [18, ["active", "pending"]],
//   }

// Server (MongoDB)
predicateToMongo(adults);
// → { age: { $gte: 18 }, status: { $in: ["active", "pending"] } }

// Edge (PostgREST)
predicateToPostgrest(adults);
// → "age=gte.18&status=in.%28active%2Cpending%29"

predicateToSQL

predicateToSQL(predicate, options): { sql, where, params }

Returns:

• sql – the full SELECT … FROM table WHERE … statement.
• where – just the WHERE clause body (no leading WHERE).
• params – values in $1, $2, … order, for the driver's parameter array.

predicateToWhere for non-SELECT queries

import { predicateToWhere } from "@directive-run/core";

const { where, params } = predicateToWhere({ age: { $gte: 18 } });

await db.query(
  `UPDATE users SET tier = 'adult' WHERE ${where}`,
  params,
);

Dialect support

Default is Postgres-style $1, $2. For MySQL / SQLite, pass a placeholder generator:

predicateToSQL(adults, { table: "users", placeholder: () => "?" });

Operator mapping

predicateToMongo

predicateToMongo(predicate, options?): Record<string, unknown>

Field names beginning with $ are rejected – that's the injection vector for $where JavaScript-evaluation. Dotted field names (sub-document paths like "user.role") are rejected by default; opt in with allowDottedPaths: true when you genuinely need them.

The result is plain JSON – no BSON, no driver-specific types – so it survives JSON.stringify round-trips and can be sent over the wire.

predicateToPostgrest

predicateToPostgrest(predicate, options?): string

Returns a PostgREST querystring (no leading ?). querystring mode URL-encodes reserved characters; raw mode leaves them readable for logging / debugging.

predicateToPostgrest({ age: { $gte: 18 } }, { mode: "raw" });
// → "age=gte.18"

predicateToPostgrest(
  { $any: [{ tier: "gold" }, { score: { $gte: 100 } }] },
  { mode: "raw" },
);
// → "or=(tier.eq.gold,score.gte.100)"

Safety guarantees

SQL injection by construction

Every operand flows through the params array – never the SQL string. The only thing interpolated literally into the SQL is the table name and the column names, both regex-validated against [A-Za-z_][A-Za-z0-9_]* (optionally dotted for qualified names).

// SAFE – even with a malicious-looking value:
predicateToSQL(
  { name: "Robert'); DROP TABLE Students;--" },
  { table: "users" },
);
// → { sql: "SELECT * FROM users WHERE name = $1",
//     params: ["Robert'); DROP TABLE Students;--"] }

The pgolfer's classic "Bobby Tables" attack becomes a literal string parameter. The database sees one bound parameter – never SQL.

Mongo $where injection blocked

predicateToMongo rejects any field name beginning with $. This is the only way an attacker-controlled predicate could land a top-level $where (which evaluates server-side JavaScript) – closed by construction:

predicateToMongo({ $where: "function(){return true}" });
// → throws: field name "$where" starts with "$" – reserved for Mongo operators

Combinator-and-sibling-key rejection

A predicate like { $all: [aiPredicate], tenant_id: "abc" } would silently drop the tenant_id clause if combinators and sibling keys were both honored – a real cross-tenant data leak waiting to happen. All three codegens throw when a combinator coexists with sibling keys:

predicateToSQL(
  { $all: [aiPredicate], tenant_id: req.user.tenant_id },
  { table: "rows" },
);
// → throws: $all cannot coexist with sibling keys (tenant_id)

The fix is to nest: { $all: [aiPredicate, { tenant_id: req.user.tenant_id }] }.

Depth limit

All three codegens enforce a 64-level recursion limit, the same as evaluatePredicate. Catches cyclic spec objects and DoS attacks via absurdly nested combinators.

allowedKeys allowlists

For predicates that come from untrusted sources (AI agents, public APIs, user-built rule editors), pass an allowedKeys list. Any predicate key not in the list throws before the SQL is built:

predicateToSQL(
  aiGeneratedPredicate,
  {
    table: "users",
    allowedKeys: ["age", "status", "tier"], // SSN/email NOT in list
  },
);
// → throws if the AI emitted `{ ssn: { $eq: "..." } }`

LIKE wildcard escaping

%, _, and \ inside $startsWith / $endsWith / $contains operands are automatically escaped. The generated LIKE clauses carry an explicit ESCAPE '\' so the escape character behaves identically across standard_conforming_strings settings and MySQL's NO_BACKSLASH_ESCAPES mode.

select projection validation

select accepts "*", a single column identifier, or an array of column identifiers. Free-form SQL is rejected – build the wrapper SQL yourself with predicateToWhere for COUNT, JOIN, or other constructs:

predicateToSQL({ x: 1 }, { table: "u", select: "*, password FROM admin --" });
// → throws: invalid column identifier

Effects-only operators are rejected

$changed requires a prev snapshot – meaningful in the Directive runtime, meaningless on a SQL row. All three codegens throw [Directive] predicateTo*: $changed is an effects-only operator rather than silently producing wrong queries.

Real-world patterns

Same filter on client and server

// shared/filters.ts
export const activeAdults = {
  age: { $gte: 18 },
  status: { $eq: "active" },
} as const;

// client/UserList.tsx
import { evaluatePredicate } from "@directive-run/core";
import { activeAdults } from "@/shared/filters";

const visible = users.filter((u) => evaluatePredicate(activeAdults, u));

// server/api/users.ts
import { predicateToSQL } from "@directive-run/core";
import { activeAdults } from "@/shared/filters";

const { sql, params } = predicateToSQL(activeAdults, { table: "users" });
const rows = await pg.query(sql, params);

AI-generated query, safely

Pair this with an LLM emitting a FactPredicate (JSON, validated by the type system before it ever reaches a database) and pass it through predicateToSQL with an allowedKeys list. Three layers of defense:

1. The predicate type system rejects unknown fact keys at parse time.
2. The codegen rejects keys not in the allowlist at compile time.
3. Combinator-and-sibling-key rejection blocks the "AI ANDs my tenant check" silent-drop attack.

No string concatenation. No eval. No prompt-injected DROP TABLE.

Edge runtimes

PostgREST sits in front of Postgres; Supabase exposes it directly. The predicateToPostgrest output drops straight into fetch:

const qs = predicateToPostgrest(predicate, { allowedKeys });
const res = await fetch(`${SUPABASE_URL}/rest/v1/users?${qs}`, {
  headers: { apikey: KEY },
});

Zero cold-start, no Node runtime, no driver – the predicate compiles to a querystring in microseconds at the edge.

Limitations

• Single table per call. No JOINs. A predicate describes a row predicate; joins describe relationships and need a separate API.
• $between is decomposed to $gte + $lte in Mongo and PostgREST (age=gte.18&age=lte.65) for portability.
• Mongo $contains is string-only. For array element-membership use $elemMatch or $in directly.
• $matches flag semantics differ between JS, Postgres, and Mongo – the i flag is portable; multiline / dotall flags are not.
• RegExp operands reach the server. Catastrophic-backtracking patterns (/(a+)+/, /(.+)*/) will burn database CPU. Treat $matches operands from untrusted sources with caution.

Reference

• API: predicateToSQL, predicateToWhere, predicateToMongo, predicateToPostgrest
• Types: PredicateToSqlOptions, PredicateToSqlResult, PredicateToMongoOptions, PredicateToPostgrestOptions
• Related: Data-form definitions, Rules diff, Predicate backtest