Filtering: An Allowlist With an Index Bill

When the docs say GET /orders?status=paid&created_after=2026-01-01, they are not describing a convenience — they are promising that this *shape of query* is a supported operation at production scale, indefinitely. The client cannot know or care whether the predicate is served by an index seek or a 40-million-row scan; the contract made both look identical. Which means the filter list is really a list of query plans you have agreed to fund, and every field you add to it adds rows to that bill (see What an API Contract Actually Is).

This is why the design position is an allowlist: a small, explicit set of filterable fields, each with defined operators (status equals one of an enum; created_after/created_before bound a range) and each backed by a deliberate index decision. The opposite default — "we pass query params through to the WHERE clause" — ships fast and fails in two directions at once: performance (any unindexed field becomes a scan endpoint the moment someone filters on it) and security (client-controlled field names flowing toward SQL is the posture injection lives in, even when your ORM technically escapes everything).

Filters are input, so they get the full Validation Errors: Feedback, Not Verdicts treatment: typed values (created_after must parse as a date — a silently ignored typo'd date returns the *unfiltered* collection, which for a reconciliation job is a correctness bug, not a UX flaw), enum values checked against the enum, and unknown filter fields rejected, not ignored. Silently ignoring ?staus=paid returns every order while the client believes it asked for a subset — the most expensive kind of wrong answer, an all-2xx one.

The subtle trap is combinations. Four filterable fields plus a sort order look like four promises; they are closer to dozens, because clients combine them: status + created_after, customer_id + status + sort by total… A composite index on (status, created_at) serves the first beautifully and does nothing for customer_id + total. Each *combination pattern* is its own query plan, and B-tree index mechanics (leftmost-prefix rules, one range condition, sort direction) decide which combinations one index can cover — the contract you can afford is downstream of index design, which is why filter design is a conversation with whoever owns the schema (see Cursor Pagination: An Opaque Bookmark, Not a Position for the same bill arriving via ordering).

You do not need an index per combination — you need a *policy* for combinations. The workable options: keep the filterable set small enough that the handful of real combinations are each covered (the usual answer); designate some filters as always-required so the index prefix is guaranteed (customer_id required on a per-tenant API — which is also the authorization boundary doing double duty, see Authorization Design in the Contract); or explicitly tier the contract — "these combinations are fast; anything else may be slow or rejected". What is not workable is silence, where the docs imply every combination is equal and the database disagrees at 2 a.m.

Filters also interact with Pagination: Choosing How Lists End in one way worth engineering deliberately: the filter set is part of the traversal's identity. A cursor minted under status=paid must not continue under status=refunded — bind the filter hash into the cursor and reject mismatches (see Cursor Pagination: An Opaque Bookmark, Not a Position). And every filtered traversal still needs the total ordering and tiebreaker; a filter that shrinks the result does not excuse the ordering contract.

Sometimes the requirement is genuinely open-ended — an analytics surface, an admin power-search, an integration platform. The answer is still not "pass params to the WHERE clause"; it is recognizing that you are now building a *query API*, a different product with different machinery: a defined grammar with a parser (never string concatenation), per-field capability declarations, query cost estimation with rejection of expensive shapes, tight result and time limits, and usually a separate datastore fed by replication so the analyst's five-way filter never competes with checkout for the primary's buffer pool. What GraphQL Costs tells the same story from the GraphQL side; Search Is a Different Contract Than Filtering tells it for relevance-ranked text.

The decision discipline: start from measured consumer tasks, not imagined flexibility. Filter parameters are trivially additive — shipping four fields today and adding a fifth next quarter when telemetry shows clients client-side-filtering on it is cheap (see the observe list below). Shipping twenty speculative fields today means twenty standing promises, most unused, some unindexed, all load-bearing the moment one consumer automates against them. Filters are the clearest case in API design where the small contract is the strong one.