SupaInsurTech · Supabase · Database triggers
The transcript recovery worker that kept queueing reports.
Every minute, an InsurTech platform checked whether pending call transcripts needed recovery. Before it could contact the provider, its database updated a lease on each transcript it claimed.
That small update also refreshed call outcomes and queued daily reports, even though the transcript itself had not changed.
After a trigger change, the recovery RPC averaged 3.23 ms instead of 131.99 ms across comparable production windows. The whole worker averaged 1.21 seconds instead of 1.37 seconds. Those are different measurements: the worker also does other work and waits on the transcription provider.
How the worker got here
The platform added recording transcription and recovery in early October. A scheduled worker checked pending submissions every minute. It could claim up to five due transcripts at a time, setting a 15-minute recovery lease so another worker would not claim the same records concurrently.
Later, the same transcript records became inputs to automatic call outcomes and daily reports. Those features needed to react when a transcript arrived, changed state, or was corrected. Row-level triggers kept the derived information current.
By October 24, the recovery lease update shared the same trigger path as those meaningful transcript changes. The database was doing report and outcome work when the worker had only moved the next recovery time.
Follow the work beyond the RPC
While building SupaBeast, we investigated the path using saved production query statistics, application code, and database definitions. This was a hands-on investigation; automated AI analysis was off.
The recovery RPC was called regularly in production. Its top-level statement looked like a short claim-and-update operation, but each claimed row fired two downstream triggers. One refreshed the call outcome. The other requested daily-report work for affected reports.
The cost depended on the data connected to a transcript. In an isolated synthetic case with five due transcripts, the original path made 10 report requests. A case with shared historical reports made 140. Those counts describe the test fixture, not measured production enqueue rates.
Looking at the RPC alone would not explain those requests. The extra work sat behind its update.
Change the trigger boundary
The correction was to distinguish a lease-only update from a transcript update. We split the insert/delete handling from the update triggers and added this condition to the update triggers:
WHEN (
OLD.recovery_after IS NOT DISTINCT FROM NEW.recovery_after
OR (to_jsonb(OLD) - 'recovery_after')
IS DISTINCT FROM (to_jsonb(NEW) - 'recovery_after')
)The condition keeps the original behavior when the lease is unchanged, including a no-op update. It also keeps the triggers when the lease and another field change together. The triggers skip their work only when recovery_after changes and the rest of the row is identical.
Comparing the rest of the row also covers fields added later. A future transcript field does not need to be added to a hand-maintained list before its changes can refresh outcomes and reports. PostgreSQL evaluates a row-level trigger’s WHEN condition using the old and new row values.
The recovery RPC, account scoping, batch size, lease duration, locking, provider reconciliation, and permissions stayed in place. Inserts and deletes retained the behavior their original triggers handled.
Verify on the customer’s PostgreSQL version
Before touching production, we recreated the relevant schema in a network-isolated PostgreSQL 17.6 container, matching the customer’s server release. The rows were synthetic. We measured the full recovery RPC with its real triggers enabled, not just the lease update in isolation.
For five due transcripts, median RPC execution across 40 measured samples per phase fell from 13.12 ms to 0.48 ms. The 10 report requests in that scenario fell to zero. In the shared-report scenario, 140 requests fell to zero.
The tests compared RPC responses and resulting transcript and outcome data. They covered meaningful updates, no-op writes, inserts, deletes, a future column, permissions, repeat migration, rollback, and two workers claiming disjoint batches. Those checks established behavior in the fixture; production performance still had to be measured after release.
What production showed
On October 26, the change reached production through the application’s normal release process. The worker already had pending transcripts, so we let its scheduled traffic exercise the change. We did not manufacture failures or reset recovery leases.
We compared the same production RPC using pg_stat_statements: ten calls over roughly ten minutes before deployment, and ten over roughly ten minutes after the backend deployment completed. The database, role, query identity, query text hash, and statistics entry matched. The counters had not been reset.
| Measurement | Before | After | Change |
|---|---|---|---|
| Mean recovery RPC execution | 131.99 ms | 3.23 ms | 97.6% lower |
| Logical buffer hits per RPC | 5,922 | 434 | 92.7% lower |
| Mean whole-worker execution | 1.37 s | 1.21 s | 11.4% lower |
For the RPC figures, we subtracted cumulative execution time and buffer counters between snapshots, then divided by the calls in each window. An earlier 75-call production window averaged 152.38 ms per RPC. A separate longer pre-change interval covered 1,961 calls and averaged 152.61 ms.
The worker figures come from the platform’s recorded function execution time for ten requests in each corresponding window. All 20 returned HTTP 200. Because the worker also performs other operations and calls the provider, that 11.4% change cannot be attributed entirely to the SQL fix.
What the numbers show
This was a small observational comparison, not a controlled production load test. Batch composition and other activity could vary between calls. The submitted backlog was 119 transcripts at both ends of the post-release window, so these results do not establish faster provider completion or a cleared backlog.
Nor do they measure how quickly an existing transcript appears on the Calls screen. That read follows a different path. The result here is less database work when the recovery worker claims pending transcripts.
After deployment, the database contained the expected triggers. The relevant function and view definitions, service-only RPC permissions, and scheduled worker remained intact.
Where to look in your own system
Find a recurring job that updates bookkeeping fields on rows other parts of the application watch. Trace that write through every trigger before deciding the update is cheap. Then compare the work for a bookkeeping-only change with the work for a real business-data change.
The important test is whether the optimization preserves the events downstream systems actually need. A skipped trigger is useful only if a real transcript correction still reaches the outcome and report that depend on it.