Skip to main content

Persistence

Databases​

DatabaseMinimum versionModuleUUID columnTime column
PostgreSQL16oncebox-postgresqlUUIDTIMESTAMPTZ
MySQL8.0.13oncebox-mysqlBINARY(16)DATETIME(6), UTC
Oracle23oncebox-oracleRAW(16)TIMESTAMP WITH TIME ZONE

Add exactly one dialect module. The starter declares all three as optional, so none is on your classpath by default, and the matching one is selected from the database the DataSource points at.

Repositories use Spring's JdbcTemplate directly; no JPA or Hibernate is involved, and the outbox works the same whether your application uses JPA, jOOQ or plain JDBC - as long as they share the transaction manager and DataSource.

JdbcTemplate​

oncebox works through its own template, the bean onceboxJdbcTemplate, built from the application's DataSource. It is declared with defaultCandidate = false:

  • application code that injects a JdbcTemplate by type gets Spring Boot's jdbcTemplate, and the spring.jdbc.template.* settings apply to it as usual;
  • those settings (max-rows, query-timeout, fetch-size) do not reach oncebox queries;
  • oncebox beans get their template by name.

To tune the template oncebox uses, declare your own bean under the same name, also not a default candidate, in any configuration class of the application:

@Configuration
class OnceboxJdbcConfiguration {

@Bean(defaultCandidate = false)
JdbcTemplate onceboxJdbcTemplate(DataSource dataSource) {
JdbcTemplate template = new JdbcTemplate(dataSource);
template.setQueryTimeout(30);
return template;
}
}
warning

Build it from the DataSource your transaction manager uses. A template on another DataSource writes events outside the business transaction, and the outbox loses its atomicity without an error.

Leave out defaultCandidate = false and your bean becomes an ordinary candidate: Spring Boot then backs off from its own jdbcTemplate, and every JdbcTemplate injected by type in the application is the oncebox one again.

Tables​

TableNeeded whenContents
outbox_eventsthe outbox is enabledEvents waiting for, in, or after delivery
oncebox_jobsoutbox or inbox cleanup is enabledDistributed locks of the cleanup jobs
outbox_dlq_eventsthe outbox and its DLQ are enabledEvents that exhausted their retries
inbox_eventsthe inbox is enabledIds of consumed events

How the tables get created is set by oncebox.schema.mode: oncebox applies its migrations with Flyway, with Liquibase or straight through JDBC, or leaves the schema to you, which is the default. See Schema Migrations. The DDL below is the PostgreSQL version; the migrations for every dialect are in oncebox-starter/src/main/resources/db/oncebox.

outbox_events​

PostgreSQL:

CREATE TABLE IF NOT EXISTS outbox_events (
id UUID PRIMARY KEY,
status VARCHAR(50) NOT NULL,
event_type VARCHAR(255) NOT NULL,
payload_type VARCHAR(255) NOT NULL,
payload TEXT NOT NULL,
retry_count INTEGER NOT NULL,
next_retry_at TIMESTAMPTZ NOT NULL DEFAULT clock_timestamp(),
created_at TIMESTAMPTZ NOT NULL DEFAULT clock_timestamp(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT clock_timestamp(),
lock_token UUID
);

CREATE INDEX IF NOT EXISTS idx_outbox_by_event_type_and_next_retry_at
ON outbox_events(event_type, next_retry_at)
WHERE status = 'PENDING';

CREATE INDEX IF NOT EXISTS idx_outbox_by_status_and_updated_at
ON outbox_events(status, updated_at)
WHERE status IN ('IN_PROCESS', 'FAILED', 'PROCESSED');
ColumnMeaning
idUUIDv7 event id, sent as oncebox_event_id
statusPENDING, IN_PROCESS, PROCESSED, FAILED
event_typeEvent type from oncebox.outbox.events
payload_typeClass name of the payload
payloadSerialized payload, JSON by default
retry_countFailed attempts so far
next_retry_atWhen the event is due; set on insert and after each failure
created_atWhen the event was stored
updated_atLast status change; the age used by stuck recovery and cleanup
lock_tokenToken of the capture that holds the event, see fencing

outbox_dlq_events​

PostgreSQL:

CREATE TABLE IF NOT EXISTS outbox_dlq_events (
id UUID PRIMARY KEY,
status VARCHAR(50) NOT NULL,
dlq_status VARCHAR(50) NOT NULL,
event_type VARCHAR(255) NOT NULL,
payload_type VARCHAR(255) NOT NULL,
payload TEXT NOT NULL,
retry_count INTEGER NOT NULL,
next_retry_at TIMESTAMPTZ NOT NULL,
created_at TIMESTAMPTZ NOT NULL,
updated_at TIMESTAMPTZ NOT NULL,
moved_at TIMESTAMPTZ NOT NULL DEFAULT clock_timestamp()
);

CREATE INDEX IF NOT EXISTS idx_outbox_dlq_by_moved_at
ON outbox_dlq_events(moved_at)
WHERE dlq_status = 'TO_RETRY';
ColumnMeaning
idThe event id, the same as in outbox_events
statusThe outbox status the event was moved with, FAILED
dlq_statusMOVED, IN_PROCESS, TO_RETRY, RESOLVED
event_typeEvent type from oncebox.outbox.events
payload_typeClass name of the payload
payloadSerialized payload, JSON by default
retry_countFailed attempts before the event was moved
next_retry_atCopied from outbox_events
created_atWhen the event was first stored in outbox_events
updated_atLast status change in the DLQ; the age used by cleanup
moved_atWhen the event entered the DLQ; the order of the DLQ

inbox_events​

CREATE TABLE IF NOT EXISTS inbox_events (
id UUID PRIMARY KEY,
consumed_at TIMESTAMPTZ NOT NULL DEFAULT clock_timestamp()
);

CREATE INDEX IF NOT EXISTS idx_inbox_by_consumed_at ON inbox_events(consumed_at);
ColumnMeaning
idId of a consumed event, read from oncebox_event_id
consumed_atWhen the event was consumed; the age used by cleanup

The primary key on id is what makes deduplication safe under concurrency: two instances that receive the same event at once cannot both insert it.

oncebox_jobs​

CREATE TABLE IF NOT EXISTS oncebox_jobs (
job_name VARCHAR(255) PRIMARY KEY,
lock_until TIMESTAMPTZ NOT NULL,
locked_by UUID,
locked_at TIMESTAMPTZ NOT NULL,
lock_at_least_for BIGINT NOT NULL,
lock_at_most_for BIGINT NOT NULL
);

One row per cleanup job: outbox-processed-cleanup, outbox-dlq-cleanup and inbox-cleanup. A row is inserted when the job is enabled, with a lock that has already expired.

ColumnMeaning
job_nameName of the cleanup job
lock_untilUntil when the lock is held; an instance takes the lock only once this moment has passed
locked_byId of the instance that took the lock last; NULL until the job first runs
locked_atWhen the lock was taken last
lock_at_least_forMilliseconds the lock is held at least, even when the job finishes earlier
lock_at_most_forMilliseconds after which a lock its holder did not release expires

Taking the lock sets lock_until to lock_at_most_for from now. Releasing it sets lock_until to the later of now and locked_at plus lock_at_least_for, so a job that finishes quickly is not started again by another instance right away. See distributed lock for the settings.

Time​

All timestamps are written and compared by the database, see Core Concepts. MySQL stores UTC wall time in DATETIME(6) columns and needs 8.0.13+ for expression defaults.

MySQL isolation level​

Run the outbox at READ COMMITTED on MySQL

Under InnoDB's default REPEATABLE READ, the SELECT ... FOR UPDATE SKIP LOCKED polling query takes next-key (gap) locks, and several instances polling the same table deadlock instead of each taking a disjoint batch. READ COMMITTED disables gap locking. The outbox never needs repeatable reads, so this is safe.

spring:
datasource:
hikari:
transaction-isolation: TRANSACTION_READ_COMMITTED

PostgreSQL and Oracle are not affected.

Table growth​

outbox_events holds processed events until cleanup removes them (oncebox.outbox.clean-up.ttl, default 24h), and inbox_events holds ids for oncebox.inbox.clean-up.ttl. Size the TTLs to your write rate: a high-volume service with a long TTL keeps millions of rows, and every capture and cleanup query pays for them.