How We Eliminated Redundant DB Scans on Every Screening Request

Release: v0.1.119 · Performance · PERF-18

The Problem

Every time a compliance team member screened a person via the POST /api/people route, the platform fetched the entire sanctionedEntities table fresh from the database — thousands of rows, on every single request.

The OFSI consolidated list is updated at most once per day, via the nightly sync job. Yet the application had no mechanism to reuse that data between requests. Under any meaningful load — multiple case workers running simultaneous screenings, or automated batch checks — this translated directly into thousands of redundant full table scans per day.

The Fix

We wrapped the sanctions list fetch in Next.js unstable_cache, which provides server-side memoisation with tag-based invalidation:

// src/app/api/people/route.ts

const getCachedEntities = unstable_cache(
  async () =>
    db.select({ /* entity fields */ }).from(sanctionedEntities),
  ['active-sanctions-list'],
  {
    tags: ['sanctions-list'],
    revalidate: 3600, // 1 hour TTL as a safety net
  }
);

The cache is keyed to 'active-sanctions-list' and tagged with 'sanctions-list'. A 1-hour revalidation window acts as a safety net, but the primary invalidation mechanism is explicit: when a new OFSI import batch completes, revalidateTag('sanctions-list') is called to flush the cache immediately.

// Called at the end of the nightly sync pipeline
revalidateTag('sanctions-list');

This means:

• Between syncs: all screening requests share a single cached read of sanctionedEntities.
• After a sync: the cache is purged and the next request repopulates it with the latest data.

Why This Matters

Scenario	Before	After
10 concurrent screenings	10 full table scans	1 DB read, 9 cache hits
500 screenings between nightly syncs	500 full table scans	1 DB read per sync cycle
Post-import accuracy	Always fresh	Immediately invalidated on import

Database load from the people screening route is now effectively constant relative to the sync cycle rather than scaling linearly with screening volume.

Accuracy Guarantee

This change does not affect screening accuracy. The cache is invalidated synchronously at the end of every import batch, so any entity added or removed from the OFSI list is reflected in the very next screening request after the nightly sync completes. The 1-hour TTL is a fallback only — under normal operation, explicit invalidation via revalidateTag is the mechanism that keeps the cache current.

No Action Required

This is a server-side infrastructure change. There are no API contract changes, no configuration required, and no changes to how screening results are returned or displayed.