DEV Community: Michelle Wiginton

Building a Custom Modbus TCP Client in .NET: What a Production-Grade Poller Actually Needs

Michelle Wiginton — Mon, 20 Jul 2026 18:01:49 +0000

Most Modbus tutorials in .NET end at the same place: install a library, call ReadHoldingRegisters, print a number. That is enough to prove a device is reachable. It is not enough to run anything.

The moment you point that code at more than one device and leave it running, the questions change. What happens when a device stops responding? What happens when one slow device blocks the others? Why is that float reading 3.4e38 instead of 480? Where does the data actually go?

This article covers the layer that answers those questions: the application code you build around a Modbus library. Everything here runs against a simulated device included in the code, so you can execute the whole thing locally with nothing but the .NET SDK. All values and device names are illustrative.

A short Modbus refresher

Modbus TCP is a request/reply protocol. A client opens a TCP connection (port 502 by convention) and asks a device for data. The device answers. There is no subscription and no push, so the client polls on a schedule.

Data lives in four spaces:

Space	Size	Access
Coils	1 bit	read/write
Discrete inputs	1 bit	read
Input registers	16 bit	read
Holding registers	16 bit	read/write

Function code 3 reads holding registers and is the one you will use most. A request carries a transaction ID, a unit ID, a starting address, and a register count. The response carries the raw 16-bit words back.

Two properties of that design drive most of what follows. Registers are only 16 bits wide, so anything larger spans several of them. And the wire format is big-endian, which will not match your CPU.

Just use a library, then keep going

Do not reimplement Modbus framing for production. NModbus, FluentModbus, and EasyModbusTCP all handle the wire protocol, and FluentModbus ships a server implementation that is useful for testing.

What none of them give you is a client. They give you a read call. The distance between a read call and something you can deploy is:

connection lifecycle across devices that disappear and come back
a polling schedule per device that tolerates slow or dead peers
decoding raw registers into typed values you can reason about
somewhere for the data to go, decoupled from how it was acquired
enough visibility to know when a device went quiet

That is the custom part. It is specific to your system, which is exactly why no package solves it for you.

The code below writes the framing directly against System.Net.Sockets so the example has zero dependencies and you can read every byte. In a real build, swap that piece for a library and keep the structure around it.

Component 1: decoding registers into typed values

This is where most first attempts break, so start here.

A 32-bit float occupies two consecutive registers. To reconstruct it you concatenate both words in the order the device sent them and read the result as big-endian. Get the order wrong and you do not get an error, you get a plausible-looking wrong number, or a value like 1.7e38.

Rather than scatter that logic through the codebase, describe the device as data:

enum RegisterType { Float32, UInt16Enum }

record RegisterDefinition(string Name, ushort Address, RegisterType Type, string Unit);

record Reading(string DeviceName, string PointName, object Value, string Unit, DateTime TimestampUtc);

static class RegisterDecoder
{
    public static object Decode(RegisterDefinition def, ushort[] regs, int offset)
    {
        switch (def.Type)
        {
            case RegisterType.Float32:
                Span<byte> buf = stackalloc byte[4];
                BinaryPrimitives.WriteUInt16BigEndian(buf.Slice(0, 2), regs[offset]);
                BinaryPrimitives.WriteUInt16BigEndian(buf.Slice(2, 2), regs[offset + 1]);
                return Math.Round(BinaryPrimitives.ReadSingleBigEndian(buf), 2);

            case RegisterType.UInt16Enum:
                return regs[offset] switch
                {
                    0 => "Stopped",
                    1 => "Running",
                    _ => "Fault"
                };

            default:
                throw new NotSupportedException();
        }
    }
}

A register map then becomes a list, not code:

var map = new List<RegisterDefinition>
{
    new("Voltage",   0, RegisterType.Float32,    "V"),
    new("Frequency", 2, RegisterType.Float32,    "Hz"),
    new("Status",    4, RegisterType.UInt16Enum, ""),
};

Two things worth knowing before you fight them. Vendors disagree about word order, so some devices need the two registers swapped before decoding, which is why a WordSwapped flag usually appears in the map eventually. And documentation often numbers registers from 1 while the protocol numbers them from 0, which is a very common source of off-by-one addressing.

Component 2: connection management

BinaryPrimitives handles endianness. The socket handles nothing.

Set explicit timeouts. Without them a silent device leaves you waiting on a read that never returns, and one stalled device can quietly stop your entire pipeline.

public async Task ConnectAsync(CancellationToken ct)
{
    _client = new TcpClient { ReceiveTimeout = 1000, SendTimeout = 1000 };
    await _client.ConnectAsync(_host, _port, ct);
    _stream = _client.GetStream();
}

Also, never assume one ReadAsync returns a whole frame. TCP is a stream, not a message queue. A response can arrive in pieces, and the header tells you how many bytes to expect, so read until you have them all:

private async Task ReadExactAsync(byte[] buffer, CancellationToken ct)
{
    int read = 0;
    while (read < buffer.Length)
    {
        int n = await _stream!.ReadAsync(buffer.AsMemory(read, buffer.Length - read), ct);
        if (n == 0) throw new IOException("Connection closed by device.");
        read += n;
    }
}

Skipping that check is the classic bug that works perfectly on localhost and fails intermittently on a real network.

Component 3: the polling engine

Each device gets its own loop and its own interval. Independent loops mean a dead device cannot delay a healthy one.

Failures are normal, not exceptional. Devices reboot, networks drop, technicians unplug things. The loop treats a failure as an expected state and backs off exponentially instead of hammering a device that is down:

catch (Exception ex)
{
    _failures++;
    conn?.Dispose();
    conn = null;

    var delay = TimeSpan.FromMilliseconds(
        Math.Min(200 * Math.Pow(2, _failures - 1), 3000));

    Console.WriteLine($"{_cfg.Name}: read failed ({ex.GetType().Name}), " +
                      $"retry #{_failures} in {delay.TotalMilliseconds:F0}ms");

    await Task.Delay(delay, ct);
}

One detail is easy to get wrong, and I got it wrong first while writing this. Reset the failure counter after a successful read, not a successful connect. If you reset on connect, a device that accepts connections but immediately drops them looks healthy every cycle, the counter returns to zero, and the backoff never grows. You reconnect in a tight loop forever. The captured output below shows the corrected behavior.

Component 4: a pluggable sink

Polling and delivery are separate concerns. Keep them apart behind an interface:

interface IReadingSink
{
    Task WriteAsync(IReadOnlyList<Reading> readings, CancellationToken ct);
}

The demo uses a console sink. A real deployment might implement the same interface over Azure Event Hubs, MQTT, Kafka, or a time-series database, and a batching decorator that buffers readings and flushes on an interval slots in without the polling loop knowing about it.

The payoff is testability. The polling logic never knows where data lands, so you can verify it against a fake sink and a simulator with no cloud account involved.

Running it

The full program starts two simulated devices, points three pollers at them (the third at a port where nothing is listening), and takes one device offline mid-run. Real captured output:

[18:50:21] device-01: connected
[18:50:21] device-02: connected
[18:50:21] device-03: read failed (SocketException), retry #1 in 200ms
  [18:50:21] device-01  Voltage    = 478.99   V
  [18:50:21] device-01  Frequency  = 59.92    Hz
  [18:50:21] device-01  Status     = Running
  [18:50:21] device-02  Voltage    = 481.08   V
  [18:50:21] device-02  Frequency  = 59.98    Hz
  [18:50:21] device-02  Status     = Stopped
[18:50:21] device-03: read failed (SocketException), retry #2 in 400ms
[18:50:22] device-03: read failed (SocketException), retry #3 in 800ms
  [18:50:22] device-01  Voltage    = 481.09   V
  [18:50:22] device-01  Frequency  = 60.03    Hz
  [18:50:22] device-01  Status     = Running
[18:50:22] device-03: read failed (SocketException), retry #4 in 1600ms
  [18:50:23] device-02  Voltage    = 481.94   V
  [18:50:23] device-02  Frequency  = 59.92    Hz
  [18:50:23] device-02  Status     = Stopped
[18:50:24] device-03: read failed (SocketException), retry #5 in 3000ms
[18:50:25] *** simulating device-02 going offline ***
  [18:50:25] device-01  Voltage    = 479.28   V
  [18:50:25] device-01  Frequency  = 60.1     Hz
  [18:50:25] device-01  Status     = Fault
[18:50:27] device-02: read failed (IOException), retry #1 in 200ms
[18:50:27] device-03: read failed (SocketException), retry #6 in 3000ms
  [18:50:27] device-01  Voltage    = 480.82   V
  [18:50:27] device-01  Frequency  = 60.04    Hz
  [18:50:27] device-01  Status     = Fault
[18:50:27] device-02: connected
[18:50:27] device-02: read failed (IOException), retry #2 in 400ms
[18:50:28] device-02: connected
[18:50:28] device-02: read failed (IOException), retry #3 in 800ms
  [18:50:28] device-01  Voltage    = 478.37   V
  [18:50:28] device-01  Frequency  = 59.93    Hz
  [18:50:28] device-01  Status     = Running
[18:50:28] device-02: connected
[18:50:28] device-02: read failed (IOException), retry #4 in 1600ms
Shutdown complete.

The behavior worth noting: device-01 polls on schedule the entire time, unaffected by two failing neighbors. device-03, which never existed, walks its backoff up to the 3 second ceiling instead of spinning. device-02 degrades gracefully when pulled offline, and its retry counter climbs correctly because it only resets on a successful read.

What this buys you

Failure isolation. One bad device costs you that device, not the fleet.

Testability. A simulator plus a fake sink means the whole pipeline runs in CI.

Decoupled evolution. Protocol handling, decoding, and delivery change independently. Swapping Event Hubs for Kafka touches one class.

Configuration over code. New device model, new register map entry, no redeploy of polling logic.

Operability. Per-device failure counts and timestamps tell you which device went quiet and when.

When not to build this

If you have one device, read it occasionally, and can tolerate a crash and restart, use a library directly in twenty lines and stop reading. The structure here earns its cost when you have a fleet, uneven reliability, and a downstream system that expects a steady stream.

If your devices already speak MQTT or OPC UA, prefer those. Modbus is a 1979 protocol with no security model and no push, and you should not add a polling layer you do not need.

Takeaways

The interesting problems in industrial data collection are not in the protocol. They are in everything wrapped around it: endianness, partial reads, timeouts, backoff, isolation, and a clean seam between acquisition and delivery.

Four rules worth keeping:

Treat the register map as configuration, not code.
Assume every read can fail, and back off when it does.
Reset failure counters on successful reads, not successful connects.
Keep the sink behind an interface so polling never knows where data goes.

The complete runnable program, simulator included, is roughly 330 lines and requires only the .NET SDK.

Supabase Queues in Production: Dead-Letter Queues, Retries, and Poison Messages with pgmq

Michelle Wiginton — Mon, 13 Jul 2026 16:42:42 +0000

Supabase Queues gives you a durable message queue with one migration and zero new infrastructure. The official docs walk you through creating a queue, sending a message, and reading it back, and then they stop.

Here's what they don't tell you, and it's the part that matters in production:

TL;DR: pgmq (the extension behind Supabase Queues) has no built in dead letter queue, no retry limit, and no failure alerting. A message that always fails will be retried forever. You build all three yourself: use the read_ct column as a free retry counter, divert messages that exceed your retry budget to a second queue (your DLQ), and watch pgmq.metrics() on a schedule to alert when something is stuck. This article contains the complete SQL and worker code to do it.

I recently moved my Next.js dashboard logic into Postgres and liked that approach enough to try it somewhere new: this time I pushed background jobs into the database on a fresh, standalone project. This post is everything I had to figure out that the docs skipped: the five failure modes that will bite you, and the fix for each one, tested end to end on a real Supabase project.

Why put your job queue in Postgres at all?

The traditional background job setup for a Next.js + Supabase app is a separate queue service (Redis + BullMQ, or SQS) plus a long running worker on Railway or a VPS. That's two more services to deploy, monitor, and pay for.

pgmq takes the position that for most apps, the queue can just be Postgres tables and functions. Your messages live in the same database you already back up. Enqueueing can join a transaction with the rest of your write. And "deploying the queue" is a migration file.

The tradeoff is that pgmq gives you the storage and delivery semantics of a queue, and almost none of the operational conveniences of a managed one. That's the gap this article fills.

The 60 second happy path

For completeness, here's the minimal setup. On Supabase, enable the extension (Dashboard → Integrations → Queues, or SQL):

create extension if not exists pgmq;

-- Our running example: a queue of transactional email jobs
select pgmq.create('email_jobs');

Send, read, and archive:

-- Enqueue a job
select pgmq.send(
  queue_name => 'email_jobs',
  msg        => '{"template": "welcome", "user_id": "8f3c2a10-...."}'::jsonb
);

-- Read up to 5 messages; each becomes invisible to other readers for 60 seconds
select * from pgmq.read(
  queue_name => 'email_jobs',
  vt         => 60,
  qty        => 5
);

-- Done with message 1? Archive it (moves it to an archive table)
select pgmq.archive(queue_name => 'email_jobs', msg_id => 1);

That's the whole API surface you need for now: send, read, archive, plus delete, pop, set_vt, and metrics, which we'll get to. The official quickstart covers the dashboard UI if you prefer clicking.

Now for the parts that aren't in the quickstart.

The mental model: what pgmq actually tracks

Every queue is a table (pgmq.q_email_jobs) plus an archive table (pgmq.a_email_jobs). Here's what read() actually hands back (psql expanded display):

-[ RECORD 1 ]+---------------------------------------------------------------------------
msg_id       | 7
read_ct      | 1
enqueued_at  | 2026-07-07 13:53:49.094925-05
last_read_at | 2026-07-07 13:53:49.145811-05
vt           | 2026-07-07 13:54:49.145811-05
message      | {"user_id": "8f3c2a10-4b6d-4e21-9c58-2f7a1d0e6b33", "template": "welcome"}
headers      |

(The last_read_at and headers columns are recent additions to pgmq; if your platform bundles an older release you won't see them, and nothing in this article depends on them.)

Three of these fields carry the entire failure-handling story:

msg_id: unique per queue, assigned at send time.

vt: the visibility timeout, stored as a timestamp. When you read() a message with vt => 60, pgmq sets this to now() + 60 seconds. Until that moment passes, no other read() call will return this message. If your worker finishes, it archives or deletes the message and the story ends. If your worker crashes, the timeout expires and the message simply becomes visible again. Nothing "fails"; the message just comes back.

read_ct: increments every time the message is read. First delivery is read_ct = 1.

That last field is the whole trick. pgmq doesn't know whether your worker succeeded or failed; it only knows how many times a message has been handed out. A message that has been read six times and is still in the queue has failed (or crashed a worker) five or six times. read_ct is a free retry counter, and everything below builds on it.

Failure mode #1: the infinite retry loop

Here's the scenario the docs never mention. Enqueue a message your worker can't handle:

-- template is null; the worker will throw on this every time
select pgmq.send('email_jobs', '{"template": null}'::jsonb);

Your worker reads it, throws, and doesn't archive it. Sixty seconds later the visibility timeout expires and the message is back. The worker reads it again. Throws again. Forever.

Watch it happen:

select msg_id, read_ct, enqueued_at
from pgmq.q_email_jobs;

 msg_id | read_ct |          enqueued_at
--------+---------+-------------------------------
      2 |      14 | 2026-07-07 13:51:25.886937-05
(1 row)

Fourteen deliveries, fourteen failures, and no error surfaced by pgmq.

This is called a poison message, and a single one consumes capacity on every worker run indefinitely. pgmq has no max_retries setting, so it will keep re-delivering the message until you intervene.

The fix: build a dead-letter queue with `read_ct`

Does pgmq have a built-in dead-letter queue? No, and this isn't a workaround I invented. It's how pgmq's own creators run it in production: check read_ct on read, and when it exceeds a threshold, move the message to a second queue instead of processing it.

First, create the DLQ. It's just another queue:

select pgmq.create('email_jobs_dlq');

Then wrap pgmq.read() in a function that enforces your retry budget. This is the centerpiece of the whole setup:

create or replace function public.read_with_dlq(
  p_queue_name  text,
  p_vt          integer default 60,
  p_qty         integer default 5,
  p_max_read_ct integer default 5
)
returns setof pgmq.message_record
language plpgsql
security definer
set search_path = ''
as $$
declare
  msg pgmq.message_record;
begin
  for msg in
    select * from pgmq.read(p_queue_name, p_vt, p_qty)
  loop
    if msg.read_ct > p_max_read_ct then
      -- Poison message: divert to the DLQ with context, then archive the original
      perform pgmq.send(
        queue_name => p_queue_name || '_dlq',
        msg => jsonb_build_object(
          'original_msg_id', msg.msg_id,
          'read_ct',         msg.read_ct,
          'enqueued_at',     msg.enqueued_at,
          'dead_lettered_at', now(),
          'payload',         msg.message
        )
      );
      perform pgmq.archive(p_queue_name, msg.msg_id);
    else
      return next msg;
    end if;
  end loop;
end;
$$;

-- Only the backend worker should call this
revoke execute on function public.read_with_dlq(text, integer, integer, integer)
  from public, anon, authenticated;
grant execute on function public.read_with_dlq(text, integer, integer, integer)
  to service_role;

The retry math is worth spelling out, because read_ct includes the current read. With p_max_read_ct = 5: reads 1 through 5 hand the message to your worker (five real processing attempts). On read 6, read_ct = 6 > 5, and the message is diverted without being processed. So p_max_read_ct literally means "maximum processing attempts."

I verified this by sending the same poisoned payload through a fresh cycle: calls 1 through 5 to read_with_dlq() each returned the message, and call 6 returned zero rows: the message had been diverted. Here's what landed in the DLQ (expanded display):

-[ RECORD 1 ]---------------------------------------------------------------
msg_id  | 1
message | {"payload": {"template": null}, "read_ct": 6,
          "enqueued_at": "2026-07-07T18:51:39.838618+00:00",
          "original_msg_id": 3,
          "dead_lettered_at": "2026-07-07T18:51:40.175338+00:00"}

Two design notes. I wrap the original payload in an envelope with read_ct and timestamps so that each dead message carries its own history, which makes the DLQ far easier to investigate; the record above is exactly what that looks like. And I archive() the original rather than delete() it, so the main queue's archive table remains a complete record of everything that ever passed through.

Reprocessing dead letters after you fix the underlying bug is a two-liner per message:

-- Inspect what's dead
select msg_id, message from pgmq.q_email_jobs_dlq;

-- Re-enqueue the original payload, then archive the DLQ entry
select pgmq.send('email_jobs', (message -> 'payload')::jsonb)
from pgmq.q_email_jobs_dlq
where msg_id = 1;

select pgmq.archive('email_jobs_dlq', 1);

Note that the re-enqueued message gets a fresh msg_id and a fresh read_ct of zero, so its retry budget resets. That matters for idempotency, which we'll hit in failure mode #3.

Failure mode #2: the visibility timeout is a bet, and you can lose it both ways

Every read() requires a vt, and that number is a bet on how long processing takes.

Bet too low and a slow job's message becomes visible again while the first worker is still processing it. A second worker picks it up, and now the same job is running twice concurrently. If the job sends an email, two emails go out. This isn't a crash (nothing errors), which makes it miserable to debug.

Bet too high and a genuinely crashed worker's message stays invisible for the full window. With vt => 3600, a job that died at second 5 waits 59 more minutes before anything retries it.

The rule of thumb: set vt to your worst realistic processing time (p99, not average) plus a small buffer. For a batch worker, remember the clock starts at read time, so a message processed last in a batch of ten needs the timeout to cover the nine jobs ahead of it.

For jobs with rare-but-legitimate long runs, don't inflate the global timeout; extend it per message while you work. pgmq.set_vt pushes a specific message's visibility further into the future:

-- "I'm still working on message 42; give me 120 more seconds"
select pgmq.set_vt('email_jobs', 42, 120);

Call it from your worker as a heartbeat during long operations, and you get short timeouts (fast crash recovery) and safe long jobs.

Failure mode #3: "exactly-once" doesn't mean what you think

Supabase's marketing says messages are delivered exactly once, but read the fine print: exactly once within a customizable visibility window. Across a message's lifetime, pgmq is an at-least-once system. A worker that crashes after doing the work but before archiving the message guarantees a redelivery. So does a too-short vt. So does re-enqueueing from the DLQ.

The consequence: your job handler must be idempotent, safe to run twice with the same input. For jobs that write to your own database, idempotency is often free (insert ... on conflict do update is naturally re-runnable). For jobs with external side effects (emails, payment API calls, push notifications), you need a claim check. Postgres makes this trivially cheap:

create table public.processed_jobs (
  idempotency_key text primary key,
  processed_at    timestamptz not null default now()
);

create or replace function public.claim_job(p_key text)
returns boolean
language sql
security definer
set search_path = ''
as $$
  insert into public.processed_jobs (idempotency_key)
  values (p_key)
  on conflict (idempotency_key) do nothing
  returning true;
$$;

revoke execute on function public.claim_job(text) from public, anon, authenticated;
grant execute on function public.claim_job(text) to service_role;

The returning true only fires when the insert actually happens. On a duplicate, the function returns null, which is falsy in your worker's JavaScript, so the check reads naturally: if (claimed) { doTheWork(); }.

One subtlety: put the idempotency key in the message payload at send time (something business-meaningful like email:welcome:8f3c2a10:2026-07-07), not derived from msg_id. DLQ reprocessing assigns a new msg_id, and a key based on it would happily let the side effect fire twice.

And an honest tradeoff: claim-before-work means a crash between claiming and finishing leaves that job permanently done-but-not-done. For emails, that's the right failure direction: at-most-once beats double-sending. For jobs where a miss is worse than a duplicate, claim after the work instead and treat the table purely as dedup.

Failure mode #4: overlapping workers (and the advisory-lock trap)

The standard Supabase architecture is pg_cron invoking an Edge Function worker every minute. What happens when a run takes longer than a minute? The next tick fires and now two workers are draining the queue concurrently.

Often, that's fine. The visibility timeout already guarantees two workers can't read the same message at the same time, so overlap just means the queue drains faster. If your jobs are idempotent (see above) and your downstream can take the parallelism, let it happen.

If you genuinely need single-flight (say the job hammers a rate-limited third-party API), the tempting answer is pg_try_advisory_lock(). Don't use it through the Data API. Advisory locks are session-scoped, and when your worker calls it via PostgREST/RPC, the "session" is a pooled connection that goes back into the pool still holding the lock. Your queue silently stops processing and nothing in any log tells you why.

The pooler-safe alternative is a lease row claimed with one atomic statement:

create table public.worker_leases (
  worker_name text primary key,
  lease_until timestamptz not null
);

create or replace function public.claim_worker_lease(
  p_worker  text,
  p_seconds integer default 55
)
returns boolean
language sql
security definer
set search_path = ''
as $$
  insert into public.worker_leases (worker_name, lease_until)
  values (p_worker, now() + make_interval(secs => p_seconds))
  on conflict (worker_name) do update
    set lease_until = excluded.lease_until
    where worker_leases.lease_until < now()
  returning true;
$$;

revoke execute on function public.claim_worker_lease(text, integer) from public, anon, authenticated;
grant execute on function public.claim_worker_lease(text, integer) to service_role;

If another worker holds an unexpired lease, the where clause blocks the update, no row returns, and the caller gets null. The lease expires on its own, so a crashed worker can't deadlock the system. Set the lease duration just under your cron interval (55 seconds for a every-minute tick) and each tick either claims the minute or exits immediately.

Failure mode #5: nobody finds out

Here's the operational difference between pgmq and SQS that will actually hurt you: when a managed queue's DLQ receives a message, you can wire an alarm. When a pg_cron-driven pgmq worker fails, you get a row in a log table that no one is looking at. Skipped runs are not retried. If your project gets paused or hits its connection ceiling, every schedule stops, silently.

The fix is to make the database check on itself. pgmq.metrics() returns everything you need:

select * from pgmq.metrics('email_jobs');

 queue_name | queue_length | newest_msg_age_sec | oldest_msg_age_sec | total_messages |          scrape_time          | queue_visible_length
------------+--------------+--------------------+--------------------+----------------+-------------------------------+----------------------
 email_jobs |            3 |                  3 |                 57 |              6 | 2026-07-07 13:52:49.777577-05 |                    3
(1 row)

(Recent pgmq releases also report queue_visible_length, messages that are currently visible rather than locked mid-read by a worker. Older versions omit that column.)

Two numbers matter. DLQ queue_length above zero means jobs are dying. oldest_msg_age_sec on the main queue climbing past your worst-case latency means the worker itself is stuck or gone: the failure that catches everyone, because no individual job errored.

Wire both to a webhook (Slack, Discord, anything that makes a phone buzz) with pg_net, on a schedule. Store the webhook URL in Supabase Vault first:

select vault.create_secret('https://hooks.slack.com/services/XXX/YYY/ZZZ', 'alerts_webhook');

create or replace function public.check_queue_health()
returns void
language plpgsql
security definer
set search_path = ''
as $$
declare
  v_dlq_depth  bigint;
  v_oldest_age integer;
  v_webhook    text;
begin
  select queue_length       into v_dlq_depth  from pgmq.metrics('email_jobs_dlq');
  select oldest_msg_age_sec into v_oldest_age from pgmq.metrics('email_jobs');

  if coalesce(v_dlq_depth, 0) > 0 or coalesce(v_oldest_age, 0) > 900 then
    select decrypted_secret into v_webhook
    from vault.decrypted_secrets
    where name = 'alerts_webhook';

    perform net.http_post(
      url     := v_webhook,
      headers := '{"Content-Type": "application/json"}'::jsonb,
      body    := jsonb_build_object(
        'text', format(
          'Queue alert. DLQ depth: %s, oldest pending message: %ss',
          coalesce(v_dlq_depth, 0),
          coalesce(v_oldest_age, 0)
        )
      )
    );
  end if;
end;
$$;

select cron.schedule('queue-health-check', '*/5 * * * *', 'select public.check_queue_health()');

Fifteen minutes of setup, and your Postgres-native queue now has better failure visibility than most teams' Redis setup.

The worker: an Edge Function that ties it together

Everything above composes into one worker. pg_cron triggers it every minute; it claims the lease, reads through the DLQ wrapper, claims each job's idempotency key, does the work, and archives.

First, the worker needs a way to archive messages over RPC. The pgmq schema isn't exposed through the Data API, so give it a minimal wrapper:

create or replace function public.queue_archive(p_queue_name text, p_msg_id bigint)
returns boolean
language sql
security definer
set search_path = ''
as $$
  select pgmq.archive(p_queue_name, p_msg_id);
$$;

revoke execute on function public.queue_archive(text, bigint) from public, anon, authenticated;
grant execute on function public.queue_archive(text, bigint) to service_role;

The Edge Function itself:

import { createClient } from "jsr:@supabase/supabase-js@2";

const QUEUE = "email_jobs";
const BATCH_SIZE = 10;
const VT_SECONDS = 60;
const MAX_ATTEMPTS = 5;

const supabase = createClient(
  Deno.env.get("SUPABASE_URL")!,
  Deno.env.get("SUPABASE_SERVICE_ROLE_KEY")!,
);

Deno.serve(async (req) => {
  // Only pg_cron (which sends the service role key) may invoke this
  const auth = req.headers.get("Authorization");
  if (auth !== `Bearer ${Deno.env.get("SUPABASE_SERVICE_ROLE_KEY")}`) {
    return new Response("Unauthorized", { status: 401 });
  }

  // Single-flight: skip this tick if the previous run is still going
  const { data: gotLease } = await supabase.rpc("claim_worker_lease", {
    p_worker: "email_jobs_worker",
    p_seconds: 55,
  });
  if (!gotLease) {
    return Response.json({ skipped: "lease held by another run" });
  }

  // Read a batch; poison messages are diverted to the DLQ inside this call
  const { data: messages, error } = await supabase.rpc("read_with_dlq", {
    p_queue_name: QUEUE,
    p_vt: VT_SECONDS,
    p_qty: BATCH_SIZE,
    p_max_read_ct: MAX_ATTEMPTS,
  });
  if (error) {
    return new Response(error.message, { status: 500 });
  }

  let processed = 0;
  let failed = 0;

  for (const msg of messages ?? []) {
    // Payloads enqueued without a key (e.g. ad-hoc sends) fall back to msg_id
    const key = msg.message.idempotency_key ?? `msg:${msg.msg_id}`;
    try {
      const { data: claimed } = await supabase.rpc("claim_job", { p_key: key });
      if (claimed) {
        await sendEmail(msg.message); // your actual job logic
      }
      // Archive whether we did the work or a previous run already had:
      // either way, this message is finished.
      await supabase.rpc("queue_archive", {
        p_queue_name: QUEUE,
        p_msg_id: msg.msg_id,
      });
      processed++;
    } catch (err) {
      // Deliberately do nothing: the visibility timeout will expire,
      // read_ct will increment, and the message retries next run.
      console.error(`msg ${msg.msg_id} failed:`, err);
      failed++;
    }
  }

  return Response.json({ processed, failed });
});

async function sendEmail(payload: Record<string, unknown>) {
  if (!payload.template) {
    throw new Error("template missing; this message can never succeed");
  }
  // ...render the template and call your email provider (Resend, Postmark, SES)
}

Notice what the catch block does: nothing. That's the design. Failure handling isn't code in the worker; it's the visibility timeout, read_ct, and the DLQ threshold doing their jobs. The worker's only responsibilities are to do the work and archive on success.

Schedule it with pg_cron, pulling the URL and key from Vault rather than hardcoding them (the cron.job table is readable, so a hardcoded service role key in a cron command is a leak):

select vault.create_secret('https://YOUR-PROJECT-REF.supabase.co', 'project_url');
select vault.create_secret('YOUR-SERVICE-ROLE-KEY', 'service_role_key');

select cron.schedule(
  'drain-email-jobs',
  '* * * * *',
  $$
  select net.http_post(
    url := (select decrypted_secret from vault.decrypted_secrets where name = 'project_url')
           || '/functions/v1/queue-worker',
    headers := jsonb_build_object(
      'Content-Type', 'application/json',
      'Authorization', 'Bearer ' ||
        (select decrypted_secret from vault.decrypted_secrets where name = 'service_role_key')
    ),
    body := '{}'::jsonb
  );
  $$
);

Enqueueing from your app (without exposing the queue)

Supabase offers a toggle to expose queues to clients over PostgREST via the pgmq_public schema. If you use it, you must enable RLS on the underlying pgmq.q_* tables and grant per-function permissions per role; the tables ship with RLS off.

My take: for most apps, skip that entirely. Don't hand clients queue primitives; hand them one intent-shaped RPC that validates and enqueues on their behalf:

create or replace function public.enqueue_email(p_template text)
returns bigint
language plpgsql
security definer
set search_path = ''
as $$
declare
  v_msg_id bigint;
begin
  if auth.uid() is null then
    raise exception 'authentication required';
  end if;

  if p_template is null
     or p_template not in ('welcome', 'weekly_digest', 'export_ready') then
    raise exception 'unknown template';
  end if;

  select pgmq.send(
    queue_name => 'email_jobs',
    msg => jsonb_build_object(
      'template',        p_template,
      'user_id',         auth.uid(),
      'idempotency_key', format('email:%s:%s:%s', p_template, auth.uid(), to_char(now(), 'YYYY-MM-DD'))
    )
  ) into v_msg_id;

  return v_msg_id;
end;
$$;

grant execute on function public.enqueue_email(text) to authenticated;

From a Next.js server action or route handler, enqueueing is one call:

const { data: msgId, error } = await supabase.rpc("enqueue_email", {
  p_template: "export_ready",
});

The wrapper validates input, stamps the requester, and bakes in the idempotency key: three things a raw pgmq_public.send grant would leave to hope.

Housekeeping: the archive grows forever

One quiet cost of archiving everything: pgmq.a_email_jobs never shrinks on its own. Every completed and dead-lettered message accumulates there. It's useful history, until it's a multi-gigabyte table you're paying to store. Add a retention job:

select cron.schedule(
  'purge-email-jobs-archive',
  '0 3 * * *',
  $$ delete from pgmq.a_email_jobs where archived_at < now() - interval '30 days' $$
);

Thirty days is enough runway to investigate any incident; adjust to taste.

When you should NOT use pgmq

Being honest about limits is the difference between an architecture and a hammer.

Skip pgmq and reach for SQS, Pub/Sub, or RabbitMQ when you need sustained throughput in the many-thousands-of-messages-per-second range (your queue would be competing with your app for the same Postgres resources), push-based delivery instead of polling, fan-out/routing topologies (pgmq is point-to-point: one queue, competing consumers), or org-level isolation between the queue and the primary database, so a queue stampede can't degrade user-facing queries.

For a typical SaaS or side project doing hundreds to a few thousand background jobs a day (emails, data refreshes, report generation, webhook processing), pgmq plus the patterns above is genuinely enough, and it's one less system to run.

FAQ

Does pgmq have a built-in dead-letter queue?
No. You implement one by checking each message's read_ct on read and moving messages that exceed your retry budget to a second queue. The read_with_dlq() function in this article is a complete implementation.

How do I limit retries in Supabase Queues?
There's no retry setting. Use read_ct, which increments on every read, as your attempt counter: divert any message whose read_ct exceeds your maximum to a dead-letter queue instead of handing it to the worker.

What happens if my worker crashes while processing a message?
Nothing immediately. When the message's visibility timeout expires, it becomes visible again, gets re-read by the next worker run, and its read_ct increments. Crashes and failures look identical to pgmq; both just mean the message wasn't archived.

Is pgmq exactly-once delivery?
Only within a single visibility window. Over a message's lifetime, treat pgmq as at-least-once: crashes, short timeouts, and DLQ reprocessing can all cause redelivery. Make handlers idempotent, especially around external side effects like email.

What visibility timeout should I use?
Your worst realistic (p99) processing time plus a buffer, remembering that for batch reads, the clock starts at read time, so the last message in a batch needs the timeout to cover everything ahead of it. For occasional long jobs, keep the timeout short and extend it per message with pgmq.set_vt() as a heartbeat.

What's the difference between pop() and read()?
pop() reads and deletes in one step; if your worker then crashes, the message is gone (at-most-once). read() leaves the message invisible until you explicitly archive or delete it, so failures retry (at-least-once). Use read() for anything you can't afford to lose.

Can browser clients send messages to a Supabase Queue directly?
Yes, Supabase can expose wrapper functions via the pgmq_public schema, but you must then enable RLS on the queue tables and manage per-role grants. A simpler, safer pattern for most apps is a single security definer RPC that validates input and enqueues on the client's behalf.

How do I monitor queue depth and failures?
select * from pgmq.metrics('queue_name') returns queue_length and oldest_msg_age_sec. Schedule a pg_cron job that checks your DLQ's depth and your main queue's oldest-message age, and fires a webhook via pg_net when either crosses a threshold.

What's the difference between Supabase Cron and Supabase Queues?
Cron (pg_cron) is a scheduler that runs something at a time. Queues (pgmq) is a message store that holds work until a consumer takes it. They compose: pg_cron ticks every minute to wake the worker; the queue holds the actual jobs, retry state, and backlog.

What do I need to run pgmq on Supabase?
Postgres 15.6.1.143 or later on the Supabase platform; enable it under Integrations → Queues or with create extension pgmq. Outside Supabase, pgmq installs as an extension or as plain SQL on stock Postgres.

Tested with pgmq 1.11.2 on PostgreSQL 16.14, July 2026. Every SQL block in this article was executed in order against a clean database: the happy path, the poison-message loop (read_ct reached 14), dead-lettering after exactly 5 attempts via read_with_dlq(), duplicate-claim rejection in claim_job(), lease contention in claim_worker_lease(), and DLQ reprocessing (the re-enqueued message came back with a fresh read_ct of 0). All output blocks are unedited captures from those runs. The Edge Function, pg_cron, pg_net, and Vault snippets follow Supabase's documented APIs; the database functions they call are the ones tested above.

How to Fix TypeScript TS2589: "Type instantiation is excessively deep and possibly infinite"

Michelle Wiginton — Mon, 06 Jul 2026 17:25:36 +0000

TypeScript error TS2589 happens when the compiler stops evaluating a type because the type instantiation has become too deep. The type may be infinite, but it may also be finite and simply too expensive for TypeScript to evaluate.

The most common fixes are to reduce recursive type work, add a depth limit, avoid generating huge unions, or change the API so TypeScript validates one value instead of enumerating every possible value.

Examples in this article were tested with TypeScript 5.5.4.

What is TS2589 in TypeScript?

TS2589 is the TypeScript compiler error:

Type instantiation is excessively deep and possibly infinite.

It means TypeScript was evaluating a generic type and hit an internal safety limit. This protects the compiler from spending too much time or memory expanding recursive or highly nested types.

This can happen even when the type is not logically infinite. A type can have a valid stopping condition and still require too many expansion steps for the compiler to complete.

What causes TS2589?

TS2589 is usually caused by repeated type expansion. Common triggers include:

recursive conditional types
recursive object path builders
recursive tuple builders
template literal types that parse strings
mapped types over deeply nested objects
conditional types that distribute over large unions
generic library types that compose several advanced type operations

The underlying issue is compiler cost. TypeScript is being asked to prove or construct a type through many nested instantiations.

A real-world example: type-safe form field paths

Many form libraries, validation helpers, and query builders use string paths to refer to nested values:

"customer"
"customer.billingAddress"
"customer.billingAddress.street"

It is natural to want TypeScript to validate those strings. Given a form model like this:

type CheckoutForm = {
  customer: {
    billingAddress: {
      street: string;
      postalCode: string;
    };
  };
  payment: {
    cardNumber: string;
    expirationDate: string;
  };
};

you might want a type that produces a union like this:

type CheckoutField =
  | "customer"
  | "customer.billingAddress"
  | "customer.billingAddress.street"
  | "customer.billingAddress.postalCode"
  | "payment"
  | "payment.cardNumber"
  | "payment.expirationDate";

That union can power autocomplete and prevent typos:

function registerField(name: CheckoutField) {
  // register a field with a form library
}

registerField("customer.billingAddress.street");
registerField("payment.cardNumber");

So far, this is a useful and reasonable type-level feature.

Where the useful type becomes expensive

The field path idea is useful because it catches invalid strings at compile time. The cost is that TypeScript has to recursively walk the object type and build a string literal union.

A generic implementation might look like this:

type Primitive = string | number | boolean | bigint | symbol | null | undefined;

type FieldPath<T> = T extends Primitive
  ? never
  : {
      [Key in keyof T & string]: T[Key] extends Primitive
        ? Key
        : Key | `${Key}.${FieldPath<T[Key]>}`;
    }[keyof T & string];

This type walks through an object and builds a union of valid dot-separated paths. It is the bridge between the practical API and the TS2589 error: the same recursion that gives helpful autocomplete can become too expensive on a large enough input.

For each key:

if the value is primitive, the path is just the key
if the value is another object, the path can be the key or the key followed by a nested path

For the CheckoutForm type, FieldPath<CheckoutForm> is useful:

type CheckoutField = FieldPath<CheckoutForm>;

const field: CheckoutField = "customer.billingAddress.street";

The problem appears when this helper receives a very deep object type. That can happen with generated API types, CMS schemas, configuration objects, database query result types, or heavily nested validation schemas.

Here is a simplified generated schema type:

type GeneratedCheckoutSchema<
  Depth extends number,
  Steps extends unknown[] = []
> = Steps["length"] extends Depth
  ? {
      cardNumber: string;
      expirationDate: string;
      securityCode: string;
    }
  : {
      checkout: GeneratedCheckoutSchema<Depth, [unknown, ...Steps]>;
    };

This represents a deeply nested object. At the leaf, it has fields like cardNumber, expirationDate, and securityCode. Until it reaches that leaf, it keeps nesting under checkout.

Now apply the field path type:

type CheckoutField = FieldPath<GeneratedCheckoutSchema<1000>>;

At this point, TypeScript reports:

Type instantiation is excessively deep and possibly infinite.

The field path helper is not obviously wrong. The generated schema is not necessarily impossible to describe. The problem is the amount of type-level work.

To build the full path union, TypeScript has to keep expanding:

"checkout"
"checkout.checkout"
"checkout.checkout.checkout"
"checkout.checkout.checkout.checkout"

and so on, until it reaches the leaf fields. With a depth of 1,000, that is too much recursive type instantiation.

How do you fix TS2589?

To fix TS2589, reduce the amount of type-level work TypeScript has to perform. In practice, that usually means one of these approaches:

Add a depth limit to recursive utility types.
Return a fallback type after a safe recursion limit.
Avoid generating huge unions unless the union is truly needed.
Validate one provided value instead of enumerating every valid value.
Prevent unnecessary distribution over large unions.
Simplify public types when perfect precision is too expensive.

The right fix depends on what the type is trying to provide: exactness, autocomplete, validation, or a convenient public API.

Fix 1: Add a depth limit

One reliable fix is to add an explicit depth limit. Instead of asking TypeScript to compute paths forever, decide how deep the type should be precise.

Here is a capped version of the field path helper:

type Primitive = string | number | boolean | bigint | symbol | null | undefined;

type FieldPathWithDepth<
  T,
  MaxDepth extends number,
  Depth extends unknown[] = []
> = T extends Primitive
  ? never
  : Depth["length"] extends MaxDepth
    ? string
    : {
        [Key in keyof T & string]: T[Key] extends Primitive
          ? Key
          : Key | `${Key}.${FieldPathWithDepth<
              T[Key],
              MaxDepth,
              [unknown, ...Depth]
            >}`;
      }[keyof T & string];

This version adds a Depth parameter. Each recursive step pushes one item into the Depth tuple:

[unknown, ...Depth]

Then the type checks whether the maximum depth has been reached:

Depth["length"] extends MaxDepth

If it has, the type returns string:

? string

That fallback is a design choice. It means "TypeScript will validate paths up to this depth, but beyond that, accept a string instead of continuing to recurse."

You could choose a different fallback depending on your API:

string if deeper paths should still be allowed
never if deeper paths should be rejected
a branded fallback type if you want to make the limit visible

The capped helper can now be used like this:

type CheckoutField = FieldPathWithDepth<GeneratedCheckoutSchema<1000>, 6>;

This gives useful type safety for the first six levels and avoids asking TypeScript to enumerate all 1,000 levels.

This approach is often the best fix for public utility types. If a type can receive arbitrarily deep user input, it should usually have a depth limit.

Use this approach when:

exactness past a certain depth is not important
the type is part of a public API
the input can be deeply nested
predictable compile performance matters
a fallback type is better than a compiler failure

For form field paths, this tradeoff is usually reasonable. Most real forms are not designed around hundreds of meaningful nested levels. If a generated type happens to be very deep, preserving perfect autocomplete at every level may not be worth breaking the compiler.

Fix 2: Avoid generating every possible path

Sometimes the best fix is to change the API shape.

The original FieldPath<T> approach eagerly generates a union of every valid path in the object. That can be expensive:

type CheckoutField = FieldPath<CheckoutForm>;

This is useful for autocomplete, but it is not always necessary. If callers usually provide one path at a time, you can validate the specific path they provided instead of generating the entire union up front.

Here is a type that resolves the value at a single path:

type PathValue<T, Path extends string> =
  Path extends `${infer Key}.${infer Rest}`
    ? Key extends keyof T
      ? PathValue<T[Key], Rest>
      : never
    : Path extends keyof T
      ? T[Path]
      : never;

This type reads a path string from left to right.

Given:

type Street = PathValue<CheckoutForm, "customer.billingAddress.street">;

the result is:

type Street = string;

You can use that in an API:

declare function getFieldValue<T, Path extends string>(
  form: T,
  path: Path & (PathValue<T, Path> extends never ? never : Path)
): PathValue<T, Path>;

Then:

declare const checkoutForm: CheckoutForm;

const street = getFieldValue(
  checkoutForm,
  "customer.billingAddress.street"
);

This approach does not need to generate the full union of every possible field path. It only evaluates the path the caller actually passed.

That is a different tradeoff. You may get less complete autocomplete than a full path union, depending on how the API is used, but the compiler does much less work. For large schemas, that can be the difference between a usable type and TS2589.

Use this approach when:

callers usually provide one path at a time
generating every possible path is too expensive
exact validation of a provided path is enough
the full path union is mostly used for convenience

This is often a better design for very large generated models.

Fix 3: Avoid unnecessary distributive conditional types

Recursive object paths are not the only source of TS2589. Large unions can also trigger excessive type work, especially when combined with conditional types.

In TypeScript, a conditional type distributes over a union when the checked type is a naked type parameter:

type Distributive<T> = T extends string ? T[] : never;

Given a union, TypeScript applies the conditional type to each member:

type Result = Distributive<"a" | "b" | "c">;
// "a"[] | "b"[] | "c"[]

Distribution is often useful, but it can become expensive when the union is large or when the distributed branch performs more recursive work.

If distribution is not needed, wrap the type parameter in a tuple:

type NonDistributive<T> = [T] extends [string] ? T[] : never;

This makes TypeScript evaluate the union as a whole rather than distributing across every member.

The principle is the same as the field path example: reduce the amount of type-level work TypeScript has to perform.

How to diagnose TS2589 in real code

When TS2589 appears in a real project, start with the error location. TypeScript will usually point at the type alias, generic helper, or inferred expression that triggered the expansion.

From there, look for patterns that commonly cause deep instantiation:

recursive conditional types
recursive object path builders
recursive tuple builders
template literal types that parse strings recursively
mapped types over nested objects
conditional types that distribute over large unions
generic types that call other generic types repeatedly

Then reduce the input size and see whether the error disappears.

For example, if this fails:

type CheckoutField = FieldPath<GeneratedCheckoutSchema<1000>>;

try a smaller version:

type CheckoutField = FieldPath<GeneratedCheckoutSchema<10>>;

If the smaller input compiles, the type is probably not inherently broken. It is failing because the compiler has to expand it too deeply.

This same technique applies to application code. Try a smaller object, smaller tuple, shorter string, or smaller union. If the error disappears, the issue is likely type expansion depth.

Choosing the right TS2589 fix

There is no single correct fix for every TS2589 error. The right solution depends on what the type is trying to accomplish.

If the type is a convenience utility, add a depth limit. This is usually the most practical fix. A type that works up to a reasonable depth is better than a type that fails on large inputs.

If the type must remain exact, try to reduce the amount of recursive work. Batch operations where possible. Avoid processing only one key, one tuple item, or one string segment per recursive step if the type can safely process more.

If the type is part of a public API, consider simplifying the exposed type. Library users usually care more about stable, understandable types than maximum theoretical precision.

If the type generates a large union for autocomplete, consider whether you can validate a specific user-provided value instead. Generating every possible value is often more expensive than checking one value.

If the type distributes over a large union, avoid unnecessary distribution. Preventing distribution can significantly reduce compiler work.

TS2589 checklist

When you encounter TS2589, work through this checklist:

Find the type named in the error span.
Look for recursion, distribution, template literal parsing, or nested mapped types.
Try a smaller input and see whether the error disappears.
If smaller inputs compile, treat the issue as type expansion depth.
Add a recursion budget if exactness has a reasonable limit.
Restructure the type or API if exactness is required.
Avoid generating large unions unless the union is truly needed.
Reduce distribution over large unions where possible.
Prefer a simpler public type when the precise type is too expensive.

The goal is not to make TypeScript accept any possible type-level program. The goal is to design types that are useful, maintainable, and affordable for the compiler to evaluate.

FAQ

Does TS2589 mean my type is infinite?

Not always. TS2589 means TypeScript stopped evaluating a type because the instantiation became too deep. The type may be infinite, but it may also be finite and too expensive to evaluate.

What is the fastest way to fix TS2589?

The fastest fix is usually to add a depth limit to the recursive type or replace the precise type with a simpler fallback after a safe depth.

Why do field path types cause TS2589?

Field path types often generate a union of every valid nested string path in an object. On deeply nested or generated types, that recursive union can require too many type instantiations.

Should I use string as a fallback?

Use string when deeper values should still be accepted but no longer need precise autocomplete. Use never when deeper values should be rejected.

Can large unions cause TS2589?

Yes. Large unions can cause TS2589, especially when conditional types distribute over each union member and each branch performs additional recursive work.

Summary

TS2589 is usually a compiler depth limit, not proof that your type is truly infinite.
Recursive conditional types, template literal types, mapped types, and large unions are common causes.
A type-safe field path helper is a realistic example of a useful type that can become too expensive.
The safest fix is often to add a depth limit.
Another strong fix is to avoid generating every possible string path and validate one provided path instead.

Conclusion

TS2589 means TypeScript stopped evaluating a type because the instantiation became too deep. The type may be infinite, but it may also be finite and simply too expensive.

The field path example demonstrates a common real-world cause. A type that generates every valid nested path can be useful for forms and schemas, but it can also require a lot of recursive type work.

There are two practical ways to respond:

add a depth limit so the type stays predictable
change the API so TypeScript validates one provided path instead of generating every possible path

The main lesson is that advanced TypeScript types should be designed with compiler cost in mind. A good type is not only correct. It also needs to compile reliably.

I Moved My Next.js Dashboard Logic Into Postgres. My Frontend Got Boring (And That's the Point).

Michelle Wiginton — Tue, 30 Jun 2026 15:29:44 +0000

My dashboard had a useMemo doing arithmetic it had no business doing.

It was a Pokémon TCG Pocket collection tracker, but that part doesn't matter. What matters is that the home page needed to show three things: overall completion, completion per set, and which set you were closest to finishing. The way I'd built it, the browser was fetching every card and every owned record, then grinding through the math on each render to figure all of that out.

It worked. It also got slower and harder to read every time the data grew or I added a metric. So I moved the aggregation out of React and into Postgres, and the surprising result was that my frontend got boring. Fewer hooks, less state, almost nothing left to break.

That's the whole argument of this post: aggregation belongs in the database, and when you put it there, the React code that's left over is the kind of boring you actually want in the layer your users touch.

What "fetching everything into React" actually looks like

Here's the shape of the original dashboard. Load all the cards, load the user's owned rows, then derive everything on the client.

const [cards, setCards] = useState<Card[]>([]);
const [owned, setOwned] = useState<OwnedCard[]>([]);

useEffect(() => {
  (async () => {
    const { data: allCards } = await supabase.from("cards").select("*");
    const { data: ownedRows } = await supabase
      .from("user_cards")
      .select("card_id");
    setCards(allCards ?? []);
    setOwned(ownedRows ?? []);
  })();
}, []);

const ownedIds = useMemo(() => new Set(owned.map((o) => o.card_id)), [owned]);

const perSet = useMemo(() => {
  const groups: Record<string, { total: number; have: number }> = {};
  for (const card of cards) {
    const g = (groups[card.set_id] ??= { total: 0, have: 0 });
    g.total += 1;
    if (ownedIds.has(card.id)) g.have += 1;
  }
  return groups;
}, [cards, ownedIds]);

const overall = useMemo(() => {
  const total = cards.length;
  const have = cards.filter((c) => ownedIds.has(c.id)).length;
  return total ? have / total : 0;
}, [cards, ownedIds]);

It's not bad code. But look at what it's costing:

The payload grows with the collection. I'm shipping every card row to the browser just to count them. The user never sees most of that data. It exists only to be reduced to a few numbers.
The logic can't be tested without mounting a component. The "closest set to finishing" rule lives inside hooks, tangled up with fetching and state.
Every new metric is more client state. Want a "rarest card you're missing" stat? That's another loop, another useMemo, another thing that re-runs on render.

I was using React as a database query engine. It's not one.

Why this is the wrong layer

Counting, grouping, and computing ratios across rows is a set operation. It's the thing relational databases were built to do, indexed and compiled, sitting right next to the data.

When I do it in React instead, I pay for the same work twice. Once to serialize every row and send it over the wire, and again to recompute the aggregates in JavaScript on the client. The database already knew the answer was "you own 41 of 86 cards in this set." I just declined to ask it that question and asked for all 86 rows instead.

This isn't really a performance micro-optimization. It's a question of which layer owns which job. Aggregation is a database job. I'd put it in the wrong place.

Moving it into an RPC function

Supabase lets you call Postgres functions directly with rpc(), so I wrote one that returns exactly the rows the dashboard needs: one row per set, with the counts already computed.

create or replace function public.get_home_set_completion_rows()
returns table (
  set_id text,
  set_name text,
  set_slug text,
  release_date date,
  image_url text,
  data_version text,
  total_cards integer,
  owned_cards integer,
  missing_cards integer
)
language sql
stable
security invoker
set search_path = public
as $$
  select
    sets.id as set_id,
    sets.name as set_name,
    sets.slug as set_slug,
    sets.release_date,
    sets.image_url,
    sets.data_version,
    count(cards.id)::integer as total_cards,
    count(user_cards.card_id)::integer as owned_cards,
    (count(cards.id) - count(user_cards.card_id))::integer as missing_cards
  from public.sets
  left join public.cards
    on cards.set_id = sets.id
  left join public.user_cards
    on user_cards.card_id = cards.id
    and user_cards.user_id = auth.uid()
  group by
    sets.id,
    sets.name,
    sets.slug,
    sets.release_date,
    sets.image_url,
    sets.data_version
  order by
    sets.release_date desc nulls last,
    sets.id;
$$;

The left join against user_cards filtered by auth.uid() is doing the per-user work right inside the query. Sets the user has zero cards from still show up with owned = 0, which is what I want.

And the call site collapses to this:

const { data } = await supabase.rpc("get_home_set_completion_rows");

That's it. No fetching everything, no client-side grouping, no Set of owned IDs. The component receives compact, purpose-built rows and maps over them. Overall completion is a sum of two columns. "Closest to finishing" is a sort. Both are now trivial because the hard part already happened in the database.

The React file went from a pile of hooks to a Server Component that calls one function and renders the result. That's the "my frontend got boring" moment, and deleting all that state felt great.

When a view beats a function (and vice versa)

I reached for an RPC function here because of that auth.uid() filter, since the result depends on who's asking. But it's worth knowing when a plain view is the better tool:

Use a view when the aggregation is stable, reusable, and doesn't need parameters. A view reads like a table, composes into other queries, and is the simplest thing that works. Card counts per set that aren't user-specific would be a great view.
Use a function when you need arguments (a user ID, a set filter, a scope toggle) or multi-step logic that a single select can't express cleanly.

One footgun worth flagging: both views and functions interact with row-level security, and security definer versus security invoker decides whose permissions the code runs under. A security definer function bypasses the caller's RLS, which is occasionally what you want and frequently a way to leak other users' data by accident. If you're using RLS to protect per-user rows, and you should be, make sure your function respects it rather than quietly stepping around it.

The objections worth taking seriously.

A few reasonable complaints, because this trade isn't free.

"Now my logic is split across two languages and harder to grep." True. I moved complexity, I didn't delete it. The aggregation now lives in SQL instead of TypeScript, so there's a context switch to read the whole story. My argument is just that it lands in the right place, with the database owning database work, and that the SQL version is shorter and more declarative than the loops it replaced.

"SQL is harder to test." I'd push back on this one. The function is deterministic: give it fixture rows and it produces the same counts every time, with no component to mount. And separating it out made the logic that correctly stayed in TypeScript easier to test too. My pack-recommendation engine, which sums pull probabilities for missing cards and ranks packs by expected value, is real application logic, not aggregation, so it belongs in app code. Once it wasn't tangled up with fetching and dashboard math, I could unit-test it on its own. The point isn't "move everything into Postgres." It's "move the aggregation, keep the decisions."

"This is just more infrastructure to manage." Fair, but it's version-controlled SQL living next to the rest of the schema, not a magic black box. It migrates like everything else.

The rule of thumb

If you take one thing from this post, take this:

Push aggregation and filtering down to the database. Keep decisions and presentation in the app.

Or, more bluntly: if React is looping to count things, that loop probably wants to be a GROUP BY.

The frontend got boring, with smaller payloads, fewer hooks, and less state to corrupt, and boring is exactly what you want from the layer your users actually touch. The interesting, opinionated, easy-to-get-wrong logic moved to where it could be expressed simply and tested in isolation.

The genuinely contested part is where you draw the line. I keep ranking and business decisions in app code and push counting and filtering into the database, but I know people who'd move more in either direction. Where do you draw it?

(Full code, if you want to see the whole thing wired together: mwiginton/pocketdex-tracker. Or try the live app: pocketdex-tracker.vercel.app.)