Under the Hood: Faster Rescreening with Batch Database Operations

Release: v0.1.75 · Track: Performance (PERF-17)

The Problem

The nightly rescreening loop iterates over every monitored person and, for each one, independently:

1. Deletes any pending match records (WHERE personId = X AND status = 'pending')
2. Updates the person's status to either 'clear' or 'matched'

With N people in the database, this produced 3 × N individual SQL statements per rescreening run. Each statement is a separate round-trip to the database, and under realistic compliance workloads (hundreds to thousands of monitored individuals), the cumulative overhead was measurable.

The Fix

The rescreening loop in src/lib/batch-rescreen.ts now collects all person IDs before issuing any writes, then executes the minimum number of statements needed:

// Before — executed N times
DELETE FROM matches WHERE personId = ? AND status = 'pending'
UPDATE people SET status = 'clear'   WHERE id = ?
UPDATE people SET status = 'matched' WHERE id = ?

// After — executed once per category
DELETE FROM matches WHERE personId IN (?, ?, ...) AND status = 'pending'
UPDATE people SET status = 'clear'   WHERE id IN (?, ?, ...)
UPDATE people SET status = 'matched' WHERE id IN (?, ?, ...)

The implementation accumulates two arrays during the loop — one for IDs transitioning to 'clear' and one for 'matched' — then flushes them as single bulk UPDATE statements using Drizzle's sql template or WHERE id IN (...) syntax. Match deletions are handled similarly with a single bulk DELETE.

What This Means in Practice

Metric	Before	After
DB statements per rescreen run (N people)	3 × N	3 (fixed)
Round-trips for 1,000 people	~3,000	~3
Behaviour change for end users	—	None

Who Is Affected

All deployments running scheduled or on-demand rescreening benefit automatically — no configuration changes are required. The improvement is most significant for compliance teams monitoring large populations.