<!-- Generated from models/_canonical by scripts/build-model-variants.js. Edit the canonical source, not this file. Behavioural profile for Claude: scripts/model-profiles.json -->

<critical_constraints> FORBIDDEN: Truncating code or writing placeholders such as "// ... existing code ..." or "# rest unchanged". Every edit is complete and applies as written. FORBIDDEN: Reporting a check as passed without showing the command and its output. REQUIRED: Reason through the rules below before the first edit; when two rules conflict, the one stated first wins. </critical_constraints>

---

#Purpose

Rules for query filtering on list endpoints. Filtering sits directly on top of the database, which makes it the place where API design, SQL injection and query performance meet. Three requirements, in priority order:

1. Safe — no client string ever reaches SQL as structure.
2. Bounded — no filter combination can produce an unindexed full scan.
3. Predictable — the same query means the same thing next release.

---

#Syntax: pick one and hold it

Bracketed operators cover almost every real need without a grammar to maintain. Whatever you choose, use it on every list endpoint — a per-endpoint dialect is a permanent tax on client authors.

Repeated parameters mean IN, and it should be documented:

ini
?status=paid&status=refunded        →  status IN ('paid','refunded')
?tag=eu&tag=priority                →  tag = 'eu' AND tag = 'priority'   (also valid — pick one, document it)

---

#Allowlist the field and the operator

This is the whole security story. Never map client input to SQL structure by interpolation.

ts
const FILTERABLE = {
  status:    { column: "status",       ops: ["eq", "in"],               type: "enum"   },
  totalCents:{ column: "total_cents",  ops: ["eq","gte","lte"],         type: "int"    },
  createdAt: { column: "created_at",   ops: ["gte","lte"],              type: "date"   },
  email:     { column: "email",        ops: ["eq", "startsWith"],       type: "string" },
} as const;

const OPS = { eq: "=", gte: ">=", lte: "<=", in: "IN" } as const;

function clause(field: string, op: string, raw: unknown) {
  const spec = FILTERABLE[field];
  if (!spec) throw new BadRequest(`Unknown filter field: ${field}`);
  if (!spec.ops.includes(op)) throw new BadRequest(`Operator ${op} not allowed on ${field}`);
  const value = coerce(spec.type, raw);              // throws on a bad value
  return { sql: `${spec.column} ${OPS[op]} ?`, value };   // column from the table, never from input
}

Three properties matter:

• The column name comes from your table, keyed by an alias. A client-supplied column name interpolated into SQL is injection even if the value is parameterised. → Security/sql-injection
• The operator comes from a fixed map. OPS[op] with an unknown key is undefined, not a fragment.
• Values are parameterised and type-coerced. Reject ?totalCents[gte]=abc with 400, do not coerce it to 0.

Exposing your internal column names in the API also freezes your schema — the alias layer means a column rename is not a breaking change.

---

#Bound the cost

An expressive filter language lets a client construct a query nobody planned for.

• Every filterable field must be indexed, or explicitly documented as slow and rate-limited more strictly. → Database/indexes
• Cap the number of filters per request (e.g. 10) and the size of an in list (e.g. 100).
• Leading-wildcard search (%term%) cannot use a B-tree index. Offer startsWith instead, or route full-text search to a trigram/GIN index or a search engine — not to LIKE '%…%' on a large table.
• Always combine filtering with pagination and a bounded limit. → API/pagination
• Tenant scoping is not a filter. It is applied server-side to every query, regardless of what the client sent. → Security/authorization

Check the plan for the worst legal combination, not the common one:

sql
-- The worst request a client may legally send, planned before it is shipped
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders
WHERE tenant_id = $1
  AND status = ANY($2)                 -- in: 100 values
  AND total_cents >= $3
  AND created_at BETWEEN $4 AND $5
ORDER BY created_at DESC, id DESC
LIMIT 100;

A Seq Scan or a Rows Removed by Filter in the hundreds of thousands means the filter combination is not index-backed and must be narrowed or indexed before release. → Database/query-optimization

---

#Semantics worth defining once

• Multiple fields combine with AND. If you need OR, add it explicitly rather than overloading repeated parameters.
• Absent versus empty: ?status= should be a 400, not "match everything" and not "match empty string".
• Null matching needs an explicit operator (?deletedAt[isNull]=true) — = NULL never matches.
• Ranges are inclusive unless the operator says otherwise, and dates are RFC 3339 UTC.
• Case sensitivity is a documented property per field, backed by a matching index (lower(email) needs an expression index).

Document every filterable field, its operators and its type in the OpenAPI document, so clients and generators see the same contract. → API/open-api

---

#Anti-patterns

---

#Checklist

• One filter syntax is used across every list endpoint
• Filterable fields come from an explicit allowlist mapping alias → column
• Allowed operators are declared per field
• Operators resolve through a fixed map, never string interpolation
• Values are type-coerced and rejected with 400 when invalid
• All values are passed as bound parameters
• Every filterable field is indexed, or documented as slow and rate-limited
• in list length and filters-per-request are capped
• Substring search does not use a leading-wildcard LIKE on large tables
• Filtering is always combined with pagination and a bounded limit
• Tenant scoping is applied server-side and cannot be influenced by input
• Combination semantics, null handling and case sensitivity are documented
• The worst legal filter combination has been checked with EXPLAIN ANALYZE
• Filters are declared in the OpenAPI document