Transactional Outbox in NestJS: Lessons from a Geofencing Microservice

Every event-driven service eventually runs into the same crack in the floor. You write a row, then you publish an event about that row. Those are two different systems, and nothing makes them atomic.

I hit this building a geofencing system: three NestJS services where a location service evaluates whether a user entered or exited a PostGIS polygon, and a logging service consumes those transitions from Kafka. The interesting part was never the geometry. It was making sure that a transition which happened in PostgreSQL could never fail to arrive in Kafka.

The dual-write problem

The naive version looks completely reasonable:

async recordLocation(userId: string, point: Point) {
  const transition = await this.prisma.$transaction(async (tx) => {
    // ... PostGIS containment query, diff against previous state
    return tx.userAreaState.update({ where: { userId }, data: { areaIds } });
  });

  await this.kafka.send({ topic: "area-transitions", messages: [...] }); // ← the crack
}

The transaction commits. Then the process is OOM-killed, or the broker is mid-election, or the pod is rolled during a deploy. The database now says the user is inside the area. Kafka never heard about it. No exception was thrown anywhere that mattered, no retry will fire, and nothing in your logs marks the moment. The state is simply, permanently inconsistent — and you will find out weeks later when someone asks why a report is missing entries.

Reversing the order does not help. Publish first and crash before the commit, and now you have announced a transition that never happened. Consumers act on a lie, which is worse.

One transaction, two writes

The outbox pattern removes the second system from the critical path. Instead of publishing to Kafka, you write the event into the same database, in the same transaction as the state change. A separate process publishes it afterwards.

If the transaction commits, the event exists. If it rolls back, the event never existed. There is no in-between state, because there is only one system involved.

The table that ended up carrying this:

CREATE TABLE location.outbox_events (
  id             bigserial PRIMARY KEY,
  event_id       uuid        NOT NULL UNIQUE,
  aggregate_type text        NOT NULL,
  aggregate_id   text        NOT NULL,
  event_type     text        NOT NULL,
  partition_key  text        NOT NULL,
  payload        jsonb       NOT NULL,
  status         text        NOT NULL DEFAULT 'pending',
  attempts       int         NOT NULL DEFAULT 0,
  available_at   timestamptz NOT NULL DEFAULT now(),
  published_at   timestamptz,
  created_at     timestamptz NOT NULL DEFAULT now(),
  updated_at     timestamptz NOT NULL DEFAULT now()
);

-- The relay only ever scans for due, unpublished work.
CREATE INDEX outbox_events_due_idx
  ON location.outbox_events (available_at)
  WHERE status = 'pending';

Four columns here earn their place and are worth being explicit about:

• event_id is generated by the producer, not the broker. It is what makes consumers able to deduplicate, and it must be stable across republishes of the same event.
• partition_key is captured at write time so ordering does not depend on whatever the relay happens to know later. For this service it is the user id, which keeps all transitions for one user on one partition and therefore in order.
• attempts + available_at turn the table into its own retry queue. A failed publish does not block the ones behind it; it just schedules itself further out.
• The partial index matters more than it looks. Without it, the relay's polling query degrades into a scan over every event ever published, and this table only grows.

The write side then does exactly one thing:

async recordLocation(userId: string, point: Point) {
  return this.prisma.$transaction(async (tx) => {
    // Serialise per user so two concurrent points cannot both compute a
    // transition from the same "previous" state.
    await tx.$executeRaw`SELECT pg_advisory_xact_lock(hashtext(${userId}))`;

    const previous = await this.readAreaState(tx, userId);
    const current = await this.areasContaining(tx, point);

    const entered = difference(current, previous);
    const exited = difference(previous, current);
    if (entered.length === 0 && exited.length === 0) return null;

    await this.writeAreaState(tx, userId, current);

    await tx.outboxEvent.createMany({
      data: [...entered, ...exited].map((areaId) => ({
        eventId: randomUUID(),
        aggregateType: "user_area_state",
        aggregateId: userId,
        eventType: entered.includes(areaId) ? "area.entered" : "area.exited",
        partitionKey: userId,
        payload: { userId, areaId, occurredAt: point.timestamp },
      })),
    });
  });
}

Note what is not in there: no Kafka client, no await producer.send, no try/catch around a network call. The handler's failure modes collapse back down to "the transaction committed" or "it didn't".

Transitions are a set difference, not an event

A detail specific to geofencing, but it generalises: a location update is not "the user entered area X". A point can sit inside several overlapping polygons at once, so the only correct model is comparing two sets.

const entered = current.filter((id) => !previous.includes(id));
const exited = previous.filter((id) => !current.includes(id));

Modelling this as a single "current area" field is the kind of shortcut that works in every test you write and then breaks the first time two polygons overlap in production.

The relay: publishing without losing or duplicating work

A worker polls the table, claims a batch, publishes, and marks the rows done. The only genuinely subtle part is the claim — multiple relay instances will run during a rolling deploy, and they must not publish the same rows.

FOR UPDATE SKIP LOCKED is what makes this safe:

UPDATE location.outbox_events
SET status = 'processing', attempts = attempts + 1, updated_at = now()
WHERE id IN (
  SELECT id
  FROM location.outbox_events
  WHERE status = 'pending' AND available_at <= now()
  ORDER BY id
  FOR UPDATE SKIP LOCKED
  LIMIT 100
)
RETURNING *;

Two workers running this concurrently get disjoint batches. The second does not block waiting on the first's locks — it skips the locked rows and takes the next ones.

Then publish and settle:

async drain() {
  const batch = await this.claimBatch(100);
  if (batch.length === 0) return 0;

  for (const event of batch) {
    try {
      await this.producer.send({
        topic: TOPIC,
        messages: [{
          key: event.partitionKey,
          value: JSON.stringify(event.payload),
          headers: { "event-id": event.eventId, "event-type": event.eventType },
        }],
      });
      await this.markPublished(event.id);
    } catch (error) {
      // Exponential backoff, capped. The row stays claimable.
      const backoffMs = Math.min(2 ** event.attempts * 1000, 5 * 60_000);
      await this.reschedule(event.id, backoffMs, error);
    }
  }

  return batch.length;
}

Two operational details that are easy to get wrong:

status = 'processing' needs a reaper. If a relay dies after claiming rows but before publishing them, those rows sit in processing forever. A periodic query that returns rows stuck in processing past a timeout back to pending is not optional — without it, a single crash silently strands events.

Ordering is per key, not global. The loop above publishes sequentially, which preserves order within the batch. If you parallelise it for throughput, you must partition the work by partition_key, or transitions for one user can be reordered on their way to the topic.

The part people skip: at-least-once means duplicates are guaranteed

Here is the trade the outbox makes, stated plainly: it converts "events might be lost" into "events might be delivered more than once." That is a very good trade. It is not a free one.

The duplicate window is real and unavoidable. The relay publishes to Kafka, the broker accepts the message, and the relay crashes before markPublished commits. On restart the row is still pending, so it publishes again. Kafka's idempotent producer does not save you here — that deduplicates retries within a producer session, not a republish after a restart.

So every consumer must be idempotent. In the logging service this is a unique constraint doing the work:

CREATE TABLE logging.transition_logs (
  id         bigserial PRIMARY KEY,
  event_id   uuid NOT NULL,
  user_id    text NOT NULL,
  area_id    text NOT NULL,
  event_type text NOT NULL,
  occurred_at timestamptz NOT NULL,
  CONSTRAINT transition_logs_event_id_key UNIQUE (event_id)
);

await this.prisma.$executeRaw`
  INSERT INTO logging.transition_logs (event_id, user_id, area_id, event_type, occurred_at)
  VALUES (${eventId}::uuid, ${userId}, ${areaId}, ${eventType}, ${occurredAt})
  ON CONFLICT (event_id) DO NOTHING
`;

The database enforces the invariant. There is no read-then-write race to reason about, no cache to keep warm, and it stays correct if you scale the consumer to twelve pods.

Stale events and the watermark

Duplicates are one failure mode; out-of-order and stale replays are another. If a consumer is rebuilt from the beginning of the topic, or a delayed batch lands after a newer one, you can apply an old transition on top of a newer state.

A per-user watermark makes that rejectable:

CREATE TABLE location.user_processing_watermarks (
  user_id       text PRIMARY KEY,
  last_event_at timestamptz NOT NULL
);

The rule is simply: for a given user, an event whose timestamp is at or before last_event_at is dropped. Combined with per-user partitioning, this makes replays safe rather than merely unlikely.

What it costs

I would not reach for this on every service, and it is worth being honest about the bill:

• Latency. Events are no longer published at commit time. The relay's poll interval becomes the floor on end-to-end delay. Polling every 200ms means the p99 gains roughly that much.
• A table that grows forever. Published rows need archiving or partition-based pruning. Skipping this is how you find out about it — through a disk alert.
• A worker to operate. Another process to deploy, monitor, and alert on. The metric that matters is outbox lag: the age of the oldest pending row. If that number climbs, delivery has stopped and nothing else will tell you.
• Eventual consistency, explicitly. Read models are behind by the relay interval plus consumer lag. That has to be acceptable to the product, not just to the engineers.

If a lost event is genuinely tolerable — a metrics ping, a cache warm — publish directly and keep the simplicity. The outbox is for events that represent something that happened and that another system's correctness depends on.

The checklist

If you take one thing from this, take the fact that the pattern is not really about the table. It is about admitting that the network will fail in the middle of your handler, and designing so that recovery is automatic instead of archaeological.

• State change and event insert share one transaction — no exceptions.
• event_id is generated by the producer and carried end to end.
• The relay claims work with FOR UPDATE SKIP LOCKED.
• Rows stuck in processing are reaped back to pending.
• Failures back off via attempts / available_at instead of blocking the queue.
• Every consumer deduplicates on event_id at the database level.
• Ordering guarantees are per partition key, and the key is stored with the event.
• Outbox lag is a monitored, alerting metric.
• Published rows are pruned on a schedule.

The service this came from is on GitHub as backzso/geofence-case — NestJS, Prisma, PostGIS, and KafkaJS, with the outbox, advisory locks, and watermarks wired up end to end. An earlier version of this write-up was first published on Medium.