How a Supabase trigger refactor cut call-update time by 96%

September 25: start with a correctness rule

The first attribution implementation landed on September 25, 2026. It needed to answer a business question: which incoming call led to an application’s submission?

That required more than joining two IDs. Calls and customers could match by phone number, explicit application link, or a verified handoff. Submission timing mattered. Conflicting evidence had to remain reviewable.

The first design took a straightforward approach: when relevant data changed, recalculate attribution against the final transaction state. A shared transaction-level advisory lock serialized that work.

The central loop visited every application:

FOR app IN
  SELECT * FROM public.live_transfer_applications
  ORDER BY application_id
LOOP
  -- Rebuild this application's attribution.
END LOOP;

That favored a simple correctness model over maintaining an affected-record set. Its cost grew with the application history—even when one customer’s records changed.

September 29–30: the relationship graph expanded

September 29: native calls entered the reporting system and connected to CRM leads and quote intake. An answered call now carried contact identity, ownership, and session relationships into the workflow.

September 30: attribution expanded to follow native calls through their CRM lead, intake session, submission, and agency ownership.

Those features added useful context. They also increased the work required to evaluate a candidate call. The full-history refresh still sat underneath the combined system.

Customer and application writes were already watched by the reporting triggers. The newer integrations added relationships and paths through them. A focused action could still require the database to revisit unrelated applications before its transaction finished.

By September 30, five days after the first attribution implementation, the slow quote handoff had prompted a performance investigation.

Deferred work still made the request wait

The triggers marked attribution dirty as relevant rows changed. A deferred constraint trigger then performed the refresh against the transaction’s final state.

“Deferred” moved the work to transaction completion. It still ran synchronously before that transaction could finish.

Inside the shared advisory lock, the old implementation looped through every application. For each one, it loaded submission evidence and customer phone relationships, assembled candidate calls, evaluated attribution rules, built JSON evidence, and attempted an attribution upsert.

Revision triggers could suppress an unchanged stored result. That did not remove the reads, joins, evidence construction, and upsert attempt needed to discover that nothing changed.

This was why looking only at the initiating write could miss the expensive part: the deferred trigger carried the full-history work.

What SupaBeast refactored

SupaBeast replaced the single dirty flag with transaction-scoped dependency information.

The trigger retained the old and new application, customer, phone, call, and link keys. After acquiring the existing advisory lock, it resolved which applications those changes could affect and passed that set into the recomputation function:

PERFORM private.recompute_live_transfer_attributions(
  private.affected_live_transfer_applications(changes)
);

The attribution rules stayed in the calculation. The outer loop now received affected application IDs rather than the complete history.

Keeping both old and new keys mattered. A phone correction could invalidate the previous match as well as create a new one. Removing a link still required revisiting the application it used to connect.

Resolving the affected set after lock acquisition also preserved visibility of concurrent call and application commits. The refactor narrowed the work without moving attribution to an eventually consistent background job.

The indexes supported the new access path

Limiting the recomputation loop was only part of the fix. The database also needed to find affected records efficiently.

The refactor added indexes for application phone numbers, CRM calls by lead, normalized customer phone numbers, and call-agent lookups. A GIN index on attribution candidates supported reverse lookups from a changed call into existing JSON evidence.

That last lookup covered a subtle case: a current phone or relationship might no longer point to an application, while its previously recorded attribution still referenced the changed call.

Administrative review actions also used scoped recomputation. The full-history entrypoint remained available for explicit repair and backfill.

Measured result: 1,893 ms to 71 ms

We timed the same call update with progressively larger application histories. Each history contained two matching applications; the remaining applications were unrelated to the changed call.

At 10,002 applications, the refactor removed 96% of the elapsed database time from that write. The original path took roughly 1.9 seconds; the scoped path took 71 milliseconds.

The measurements used the historical trigger implementations on the same local Supabase/PostgreSQL 17.6 schema, indexes, and synthetic records. Times are medians of six paired runs after a warm-up pair, measured inside PostgreSQL from the update through execution of all deferred constraint triggers.

Both variants had the supporting indexes available. The comparison isolates the change in recomputation strategy. The earlier 2.5-second quote observation identified the workflow; this table measures the underlying call-write path.

Same attribution, substantially less work

Every measured pair compared the resulting application IDs, statuses, credited calls, attribution basis, candidate evidence, and source revisions. They matched at all three history sizes.

The existing regression checks also covered ownership changes, phone corrections, removed links, deletions, and simultaneous call/application writes. The scoped path had to agree with a full rebuild.

The two matching applications explained the smaller work scope. The 1,893-to-71 ms timing showed its performance impact.

The shared advisory lock remained. Narrowing the work inside it shortened the measured write; the change did not remove serialization from the attribution protocol.

Five days of features can outgrow a starting design

The original design gave the reporting system a simple way to keep answers consistent. Native calling and quote integration expanded the relationships it evaluated, while the full-history refresh remained on the write path.

SupaBeast followed an interactive action into that shared database work, identified the architectural mismatch, and refactored the recomputation boundary.

The result was a call update that processed the applications it could affect, preserved the attribution result, and completed its database work in 71 ms instead of 1,893 ms in the measured 10,002-application scenario.

SupaBeast draws on more than 100 backend optimization patterns we’re codifying into an AI review agent, so teams can keep revisiting these decisions as their Supabase systems evolve.