# v0.28.0 — Transactional Inbox & Outbox Patterns

> **Full technical details:** [v0.28.0.md-full.md](v0.28.0.md-full.md)

**Status: ✅ Released** | **Scope: Large** (~6 weeks)

> Reliable event messaging built entirely inside PostgreSQL — no Kafka,
> no Debezium, no external infrastructure required.

---

## What problem does this solve?

Modern applications often need to communicate between services: "an order was
placed, notify the warehouse system." The naive approach — write to the
database, *then* publish the event to a message broker — has a fatal flaw:
if the application crashes between those two steps, either the database row
exists with no event published, or the event was published but the database
write failed. This is called the *dual-write problem*, and it causes data
inconsistencies that are extremely difficult to debug.

v0.28.0 solves this entirely inside PostgreSQL, with no external
infrastructure required.

---

## The Transactional Outbox

Think of the **outbox** as a guaranteed delivery queue built directly into
your database. When a stream table is refreshed and its result changes,
pg_trickle automatically writes a record of those changes into a companion
table — the outbox — *in the same database transaction as the refresh*.
Either both succeed, or neither does. There is no window where the data
changed but the event was not recorded.

An external relay process (your code, or the built-in one from v0.29.0)
then reads the outbox and publishes to whatever downstream system you use:
Kafka, NATS, a webhook, an SQS queue, or anything else. The relay can
crash and restart safely — it just picks up where it left off.

For large batches of changes (more than 10,000 rows by default), pg_trickle
uses a *claim-check* pattern: the outbox row carries only a lightweight
summary, and the full row data is stored in a companion table that the relay
reads in bounded memory chunks. This means delivery is never blocked by the
size of a delta.

---

## The Transactional Inbox

The **inbox** is the mirror image: a production-grade table for *receiving*
events from outside. Call `create_inbox('my_inbox')` and pg_trickle
automatically creates:

- A **pending messages** stream table showing all unprocessed events
- A **dead-letter queue** stream table for messages that have failed too
  many times
- A **statistics** stream table tracking processing throughput and error
  rates

Applications insert events into the inbox with `ON CONFLICT DO NOTHING`
for automatic deduplication — the same event published twice only creates
one row. If a message processor crashes mid-flight, the message stays
pending and will be picked up again.

---

## Consumer Groups (Kafka-style, built into PostgreSQL)

For high-throughput scenarios where multiple relay processes share a single
outbox, **consumer groups** let them coordinate safely — exactly like Kafka
consumer groups, but with zero extra infrastructure. Each relay claims a
batch under a *visibility timeout* (similar to Amazon SQS), and if the relay
crashes its batch automatically becomes available for another relay to claim
after the timeout expires.

Live dashboards of consumer health — lag, last heartbeat, active leases —
are maintained as stream tables that can feed directly into Grafana.

---

## Ordered Message Processing

For use cases where the order of messages matters — financial transactions,
audit trails, order management — `enable_inbox_ordering()` creates a
`next_<inbox>` stream table that surfaces *only the next expected message*
for each entity (customer, order, account). Out-of-order arrivals are
withheld until the preceding message has been processed. A separate gap
detection stream table automatically alerts when a message appears to be
permanently missing.

Priority queues let critical messages use a one-second refresh schedule
while background messages use thirty seconds, with no interference between
tiers.

---

## Scope

v0.28.0 is a substantial release: six weeks of solo engineering effort
covering the full outbox/inbox stack, consumer groups, ordered processing,
benchmarks, and documentation. The result is a self-contained, reliable
event-driven messaging system that needs nothing outside PostgreSQL.

---

*Next: [v0.29.0 — Relay CLI](v0.29.0.md)*