Table Schema

Open Outbox Relay operates on a specific database contract. While the Relay currently implements PostgreSQL as its primary storage engine, the schema is designed to be database-agnostic to support future storage implementations.

The Logical Contract

To support the Relay’s polling, locking, and retry logic, the underlying table must implement the following functional fields:

Column	Type	Description
`event_id`	`UUID`	Primary key. A unique identifier for the event, typically generated by the application.
`event_type`	`TEXT`	The destination topic or subject (e.g., `user.signup`).
`partition_key`	`TEXT`	(Optional) Used by the Relay to determine broker-side partitioning for ordering.
`payload`	`BYTEA`	The raw message body. Stored as binary to remain data-agnostic.
`headers`	`JSONB`	(Optional) Key-value metadata to be included in the message headers. Defaults to `{}`.
`status`	`TEXT`	Internal state: `PENDING`, `DELIVERING`, `DELIVERED`, or `DEAD`.
`attempts`	`INT`	Counter for how many times the Relay has tried to publish this event.
`last_error`	`TEXT`	Stores the error message from the last failed attempt for debugging.
`available_at`	`TIMESTAMPTZ`	When the event is eligible for polling. Used to handle exponential backoff.
`locked_by`	`TEXT`	The unique ID (Hostname/Relay ID) of the worker currently processing this event.
`locked_at`	`TIMESTAMPTZ`	Timestamp of when a Relay instance claimed this event for processing.
`delivered_at`	`TIMESTAMPTZ`	Recorded when the broker acknowledges receipt of the message.
`created_at`	`TIMESTAMPTZ`	When the application first inserted the event into the outbox.
`updated_at`	`TIMESTAMPTZ`	Automatically updated on every state change.

PostgreSQL Implementation

Use the following DDL to create your outbox table. You can customize the table name via the STORAGE_URL or environment variables, but the column names must remain as specified.

-- =============================================================================
-- Open Outbox Core Schema
-- =============================================================================
-- This schema is optimized for high-concurrency "At-Least-Once" delivery.
-- It uses partial indexes to minimize overhead and Postgres-specific types
-- (UUID, BYTEA, JSONB) for maximum efficiency and flexibility.
-- =============================================================================

-- Safety: Clear existing structure for development/migrations.
DROP TABLE IF EXISTS openoutbox_events CASCADE;

CREATE TABLE openoutbox_events (
    -- Unique identifier for the event. Usually generated by the application
    -- to allow for idempotent inserts.
    event_id      UUID PRIMARY KEY,

    -- Categorizes the message (e.g., 'user.created', 'order.placed').
    -- Useful for publishers to route messages to specific topics/subjects.
    event_type    TEXT NOT NULL,

    -- Optional key used to ensure message ordering in systems like Kafka or NATS.
    -- Messages with the same partition_key are usually routed to the same consumer.
    partition_key TEXT,

    -- The raw message body. Stored as BYTEA (binary) to remain agnostic of
    -- serialization formats like JSON, Protobuf, or Avro.
    payload       BYTEA NOT NULL,

    -- Extensible metadata container for tracing (e.g., span IDs),
    -- content-type, or custom application headers.
    headers       JSONB NOT NULL DEFAULT '{}',

    -- Lifecycle state machine:
    -- PENDING:    Ready for pickup.
    -- DELIVERING: Currently locked by a relay instance (lease active).
    -- DELIVERED:  Successfully published to the broker.
    -- DEAD:       Exceeded max retries; requires manual intervention.
    status        TEXT NOT NULL DEFAULT 'PENDING',

    -- Tracks how many times publishing has been attempted.
    attempts      INT NOT NULL DEFAULT 0,

    -- Stores the error message from the last failed attempt for debugging.
    last_error    TEXT,

    -- The "Next Run" timestamp. Supports scheduled events and exponential backoff.
    -- The relay will not pick up events until NOW() >= available_at.
    available_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),

    -- Identity of the relay instance that currently holds the lock.
    -- Prevents multiple relays from processing the same event.
    locked_by     TEXT,

    -- Timestamp when the current lock was acquired. Used by the "Reaper"
    -- to identify and reset abandoned leases (stuck processes).
    locked_at     TIMESTAMPTZ,

    -- When the event reached its final successful state. Useful for auditing and pruning.
    delivered_at  TIMESTAMPTZ,

    -- Standard audit timestamps.
    created_at    TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at    TIMESTAMPTZ NOT NULL DEFAULT NOW(),

    -- Strict enforcement of the state machine values.
    CONSTRAINT check_valid_status CHECK (
        status IN ('PENDING', 'DELIVERING', 'DELIVERED', 'DEAD')
    )
);

-- -----------------------------------------------------------------------------
-- PERFORMANCE INDEXES (The "Critical Path")
-- -----------------------------------------------------------------------------

-- The "Pickup" Index: Essential for ClaimBatch.
-- It allows the Relay to find the oldest available PENDING events without a full table scan.
-- The partial index keeps it extremely fast even as the table grows.
CREATE INDEX IF NOT EXISTS idx_openoutbox_processing_queue
    ON public.openoutbox_events (available_at ASC, created_at ASC)
    WHERE status = 'PENDING';

-- The "Reaper" Index: Used to find events stuck in 'DELIVERING' state.
-- Allows the background reaper task to quickly find and reset expired leases.
CREATE INDEX IF NOT EXISTS idx_openoutbox_stuck_leases
    ON public.openoutbox_events (locked_at ASC)
    WHERE status = 'DELIVERING';

-- -----------------------------------------------------------------------------
-- TELEMETRY INDEXES (Optimizing for GetStats)
-- -----------------------------------------------------------------------------

-- Optimizes the COUNT query for pending messages.
-- We use include instead of adding the attempts to
-- the index to make sure updates remain HOT (Heap Only Tuple),
-- preventing index bloat and locking during high-frequency retries.
CREATE INDEX IF NOT EXISTS idx_openoutbox_metrics_count
  ON public.openoutbox_events (status)
  INCLUDE (attempts)
  WHERE status = 'PENDING';

-- Optimizes the "Oldest Age" query by allowing the DB to find the minimum
-- created_at within the PENDING subset.
CREATE INDEX IF NOT EXISTS idx_openoutbox_metrics_lag
    ON public.openoutbox_events (status, created_at ASC)
    WHERE status = 'PENDING';

Maintenance indexes

The postgres implementation also uses these indexes for maintenance operations:

-- -----------------------------------------------------------------------------
-- MAINTENANCE INDEXES (Optimizing for relay-cli prune)
-- -----------------------------------------------------------------------------

-- Pruning DELIVERED: Minimizes I/O when cleaning up successfully processed events.
-- We use a partial index because DELIVERED rows typically make up 90%+ of the table.
CREATE INDEX IF NOT EXISTS idx_openoutbox_prune_delivered
    ON openoutbox_events (delivered_at)
    WHERE status = 'DELIVERED';

-- Pruning DEAD: Allows for fast identification of abandoned events based on their last update.
CREATE INDEX IF NOT EXISTS idx_openoutbox_prune_dead
    ON openoutbox_events (updated_at)
    WHERE status = 'DEAD';