All Docs
FeaturesCalmony Sanctions MonitorUpdated March 11, 2026

Under the Hood: How We Fixed Full Table Scans on the Matches Table

Under the Hood: How We Fixed Full Table Scans on the Matches Table

Release: v0.1.74
Category: Performance

The problem

The matches table is the heart of the sanctions screening platform — every screening lookup, every batch rescreen, every pending review count touches it. As match data accumulates over time, the absence of indexes means the database must read every row in the table to satisfy even the most targeted queries. This is a full table scan, and its cost grows linearly with data volume.

Three query patterns in particular were affected:

  1. Per-person screening lookups — matches.personId is used in every screening lookup and match review query. Without an index, finding all matches for a single person requires scanning all matches for every person.
  2. Pending review counts — matches.status is filtered in every pending review count rendered in the monitoring dashboard. Without an index, each count requires a full scan.
  3. Batch rescreening — Batch rescreen operations filter on both personId and status simultaneously. A single-column index can only partially satisfy this; a composite index covers the full predicate.

Auth tables had the same problem: accounts.userId and sessions.userId were unindexed, adding unnecessary overhead to every login and session resolution.

The fix

Five indexes were added to src/db/schema.ts:

// matches table
index('matches_person_id_idx').on(table.personId)
index('matches_status_idx').on(table.status)
index('matches_person_status_idx').on(table.personId, table.status) // composite

// auth tables
index('accounts_user_id_idx').on(table.userId)
index('sessions_user_id_idx').on(table.userId)

Why a composite index for batch rescreening?

A query such as WHERE personId = $1 AND status = $2 can be satisfied entirely by the composite index (personId, status) — the database locates the matching personId partition first, then filters by status within it, without touching any other rows. The standalone matches_person_id_idx index remains useful for queries that filter on personId alone (e.g. fetching all matches for a person regardless of status).

What this means in practice

  • Screening lookups return faster, regardless of how many total matches exist in the table.
  • The monitoring dashboard pending review counts no longer degrade as the match backlog grows.
  • Nightly batch rescreening — which deletes and rewrites matches for large populations — completes in less time.
  • Login and session resolution are faster, reducing latency on every authenticated request.

Do I need to do anything?

If you are running a self-hosted deployment, apply the latest database migration to create the new indexes. No application configuration changes are required.