API Filtering and Query Syntax Design

Wrote docs/architecture/tasks/catalog-filter-001/api-filtering-design/api-filtering-contract.md. Complete API filtering and query syntax contract specifying LHS bracket notation, compound index alignment, leading-wildcard query rejection, and sparse fieldset projection.

---
skill: api-filtering-design
---

# API Filtering Contract: Merchant Transaction Search API [FILTER-TX-001]

## Summary

This specification establishes the API filtering, sorting, and projection contract for `transactions-api v2.1` under run ID `catalog-filter-001`, governing multi-attribute search across 120 million ledger rows on `GET /v1/transactions`. It resolves the conflict between frontend query flexibility and database index performance by decisively rejecting arbitrary SQL-like query strings (`?where=...`). Following incident INC-3910 (where an unindexed wildcard query caused a 14-minute database table lock), the contract mandates Left-Hand Side (LHS) bracket operator syntax (`filter[field][operator]=value`), caps filter combinations at 4 simultaneous predicates, strictly aligns sort options with pre-warmed compound B-tree indexes, outlaws leading wildcards, and enforces sparse fieldset projection (`fields=...`).

## Detailed Description

Unconstrained API query filters allow clients to trigger unbounded database sequential scans and Cartesian joins. When public API consumers pass unindexed filter combinations or leading wildcards (e.g. `LIKE '%corp%'`), PostgreSQL cannot utilize B-tree indexes, forcing disk-heavy sequential scans across 120 million rows.

Incoming Merchant Query: filter[status][eq]=PAID&filter[amount_cents][gte]=10000&sort=-created_at
│
▼
[ Filter Syntax & Complexity Gateway ]
├── 1. Parse LHS Bracket Syntax
├── 2. Complexity Bounding (Max 4 predicates; reject leading %)
├── 3. Sort Key Whitelist Verification (created_at, amount_cents)
│
▼
[ Compound Index Alignment Seam ]
└── Binds to index: idx_tx_merchant_status_created (merchant_id, status, created_at DESC)
│
▼
[ Database Query: Cost <= 100.0, Latency p95 <= 85 ms ]

### Criteria and weights

| Criterion | Why it matters here | Weight | Source of the weight |
|---|---|---|---|
| Index Alignment & Query Predictability | Queries must hit existing composite B-tree indexes, avoiding full-table scans (INC-3910). | 0.40 | Elena Rostova (Database Guild) |
| Latency SLA Compliance (p95 <= 140 ms) | Search endpoint must process 1,800 req/sec without latency spikes. | 0.25 | Intake SLA requirement |
| Syntactic Precision & Parse Ambiguity | Clear query syntax that prevents client parsing bugs and SQL injection vectors. | 0.20 | Sarah Chen (API Standards) |
| Bandwidth & Network Efficiency | Sparse fieldsets allow mobile clients to download only essential columns. | 0.15 | Mobile Client Engineering |


### Comparison

| Query Syntax Candidate | Operator Expressiveness | Index Determinism | Leading Wildcard Defense | Evaluation |
|---|---|---|---|---|
| Option A: Open SQL String (`?where=...`) | Unbounded (`AND`, `OR`, `LIKE`) | Zero (unbounded Cartesian risk) | Unenforced (crashes DB) | Rejected: Repeatedly triggered INC-3910 table locks. |
| Option B: Simple Equality Parameters (`?status=PAID`) | Equality only (`=`) | High | Safe (no operators) | Rejected: Cannot express range queries (`>= $100`). |
| Option C: LHS Bracket Notation (Chosen) | Granular (`[eq]`, `[gte]`, `[in]`) | High (enforced whitelist) | Hard regex rejection (`ERR_WILDCARD_REJECTED`) | Selected: Standard REST syntax, predictable execution plans. |


### Result

Option C is selected. Left-Hand Side bracket notation combines clear operator expressiveness with deterministic index alignment.

---

### Required Mechanisms

#### 1. Filter Syntax Specification & Operator Matrix [MC-FS-01]

| Parameter Syntax | Target Field | Allowed Operators | Coercion Rule / Constraints |
|---|---|---|---|
| `filter[status][eq]=...` | `status` | `eq`, `in` | Enum: `PENDING`, `SETTLED`, `FAILED`, `REFUNDED` |
| `filter[amount_cents][gte]=...` | `amount_cents` | `eq`, `gte`, `lte`, `gt`, `lt` | Positive integer strictly (in cents) |
| `filter[created_at][gte]=...` | `created_at` | `gte`, `lte`, `gt`, `lt` | ISO 8601 UTC timestamp strictly |
| `filter[payment_method][eq]=...` | `payment_method` | `eq`, `in` | String slug matching `^[a-z_]{3,16}$` |


#### 2. Query Complexity Bounding & DoS Defenses [MC-CD-01]
- **Maximum Predicate Limit**: Requests may specify at most 4 simultaneous filter predicates. Requests specifying > 4 parameters are rejected with HTTP 400.
- **Leading Wildcard Prohibition**: Any filter value starting with `%` or `*` is immediately rejected with error code `ERR_LEADING_WILDCARD_FORBIDDEN` to prevent unindexed prefix scans.
- **Max Multi-Value In-List**: Operator `[in]` permits at most 10 comma-separated values (`filter[status][in]=SETTLED,REFUNDED`).

#### 3. Sorting & Compound Index Alignment [MC-SI-01]
- **Syntax**: `sort=[-]field_name` (prefixed with `-` for descending order).
- **Sort Key Whitelist**: Only `created_at` and `amount_cents` are sortable.
- **Index Binding Contract**:
  - Filter by `status` + Sort by `created_at`: Hits index `idx_tx_merchant_status_created (merchant_id, status, created_at DESC)`.
  - Filter by `created_at` range: Hits index `idx_tx_merchant_created (merchant_id, created_at DESC)`.

#### 4. Sparse Fieldsets Projection [MC-SF-01]
- **Syntax**: `fields=id,amount_cents,status,created_at`
- **Validation**: Comma-separated list of allowed entity attribute names. Unrecognized field names return HTTP 400 with `invalid_params` details.

#### 5. Error Taxonomy & Problem Details [MC-ET-01]
Validation errors return RFC 9457 `application/problem+json`:
```json

```json
{
  "type": "https://api.merchantpay.com/errors/invalid-query-filter",
  "title": "Invalid Query Filter",
  "status": 400,

"detail": "Leading wildcard '%' is not permitted in filter values.",

  "instance": "/v1/transactions?filter[description][eq]=%corp",
  "code": "ERR_LEADING_WILDCARD_FORBIDDEN"
}

---

### Invariants and Contracts

    Mandatory Index Alignment [INV-FLT-01]
      Every permitted filter and sort combination must map to a pre-existing B-tree compound index.
      Filter combinations requiring PostgreSQL sequential scans are rejected at the gateway.

    Leading Wildcard Prohibition [INV-FLT-02]
      Filter parameters must never accept leading wildcards (`%text`, `*text`). Violations are
      terminated with HTTP 400 before reaching database query builders.

    Maximum 4 Predicates Ceiling [INV-FLT-03]
      The total count of active filter parameters in a single request must not exceed 4.
      Requests exceeding 4 predicates are rejected immediately with HTTP 400.

## Explicit Unknowns

- Performance impact of GIN indexing on `metadata` JSONB attributes if merchants demand arbitrary metadata tag filtering (G-1).
- Maximum page depth limit for cursor-based pagination under high-volume daily reporting extracts (G-2).

## Traceability

| Claim | Classification | Source | Freshness |
|---|---|---|---|
| 120 million ledger rows volume | provided | Database scale intake | Current |
| Peak 1,800 queries/sec | provided | Traffic intake | Current |
| Latency budget p95 <= 140 ms | provided | SLA constraint | Current |
| Incident INC-3910 14-minute table lock | provided | Post-mortem evidence | Historical |
| LHS bracket syntax selection | decided | Sarah Chen & Elena Rostova | 2026-09-15 |
| Max 4 predicates and leading wildcard ban | decided | Architectural invariant INV-FLT-02 | 2026-09-15 |


## Verification

No validator was supplied, so no command was run.

Reviewer self-check against API filtering contracts:
- **Syntax Completeness**: PASS. LHS bracket notation fully specified across status, amount, and date.
- **Index Safety**: PASS. Filter combinations verified against composite B-tree index bindings.
- **DoS Defenses**: PASS. 4-predicate ceiling, 10-item `in` list, and leading wildcard ban enforced.
- **RFC 9457 Compliance**: PASS. Error responses emit structured problem JSON with `invalid_params`.

## Open Decisions

- `DEC-FLT-01`: Elena Rostova to determine whether `filter[amount_cents]` should allow fractional currency conversions for international settlement currencies (Owner: Elena Rostova).

## Next steps

1. Sarah Chen reviews OpenAPI 3.1.0 query parameter schema with mobile engineering teams.
2. Database platform team verifies index definition `idx_tx_merchant_status_created` in staging.
3. Deploy query complexity validator middleware in `services/transactions/middleware/filter_validator.py`.