Transactional Outbox
An entity tagged orm:"cdc" publishes its change event to the CDC change feed after the database commit. If NATS is unreachable in that moment the rows are durable but the event is gone: Save returns a *PostCommitError and nothing will publish the event later. The outbox closes that gap. With orm:"cdc;outbox" every write also inserts a row into the cdc_outbox table inside the write's own transaction; a relay you run on a schedule republishes any row whose event never made it onto the feed, and JetStream deduplication makes the republish a no-op when the original did arrive.
The outbox belongs to the default setup, where the ORM itself produces the change feed. With CDCProducerExternal (Debezium reads the binlog) no outbox rows are written at all.
Enabling the Outbox
type UserEntity struct {
ID uint64 `orm:"cdc;outbox"`
Email string `orm:"required;length=255"`
Name string `orm:"required;length=100"`
}
registry.RegisterEntity(UserEntity{}, orm.CDCOutboxEntity{})
orm.CDCOutboxEntity is the outbox table. Droplet ORM owns the struct so the column set is a compile-time fact on both sides: the library writes it with its own SQL, your application reads it through the generated provider. Register it like any entity and it gets its table, provider and enum like any other.
registry.Validate() rules:
| Error | Meaning |
|---|---|
entity '%s' is tagged orm:"outbox" but orm.CDCOutboxEntity is not registered; add registry.RegisterEntity(orm.CDCOutboxEntity{}) | Missing table. |
entity '%s' is tagged orm:"outbox" on mysql pool '%s' but the outbox table is on pool '%s'; the outbox row would not be in the same transaction | The outbox must live on the same MySQL pool as every entity that writes to it - otherwise the row is not in the source write's transaction, which is the entire guarantee. |
The two tags
Tag on ID | Outbox row | Published to NATS |
|---|---|---|
cdc | none | yes, post-commit (lost if the publish fails) |
cdc;outbox | pending inside the transaction, dispatched post-commit once the publish succeeded | yes, and the relay republishes rows still pending |
outbox | stored (born terminal) | no - nothing listens to the entity, so nothing is published; the table is a transactional change log |
The Outbox Table
type CDCOutboxEntity struct {
ID uint64 `orm:"table=cdc_outbox"`
Status string `orm:"enum=pending,dispatched,stored;enumName=CDCOutboxStatus;required"`
EntityName string `orm:"required;length=100"` // generated entity struct name, e.g. "UserEntity"
EntityID uint64 `orm:"required"`
Op uint8 `orm:"required"` // orm.DirtyOp: 1 insert, 2 update, 3 delete (soft delete included)
Payload string `orm:"length=max;required"` // the exact envelope JSON that goes on the wire
DispatchedAt *time.Time `orm:"time"`
CreatedAt time.Time `orm:"time"`
}
// Indexes: {Status, CreatedAt}
Status constants: CDCOutboxPending ("pending"), CDCOutboxDispatched ("dispatched"), CDCOutboxStored ("stored").
Payload is the Debezium envelope of the change (before, after, op, ts_ms, source), byte for byte what is published. A row records EntityName and nothing about where the event goes: one entity is one subject (cdc.<database>.<table>), so the relay derives it.
Generated read side (table cdc_outbox becomes entity CdcOutbox):
rows, err := entities.CdcOutboxProvider.SearchMany(ctx, orm.NewQuery().
Filter(entities.CdcOutboxProvider.Fields.Status.Is(enums.CDCOutboxStatusList.Pending)).
SortByASC(entities.CdcOutboxProvider.Fields.CreatedAt).
Pager(orm.NewPager(1, 100)))
for _, row := range rows {
row.GetStatus() // enums.CDCOutboxStatus
row.GetEntityName() // string
row.GetEntityID() // uint64
row.GetOp() // uint64
row.GetPayload() // string
row.GetDispatchedAt() // *time.Time
row.GetCreatedAt() // time.Time
}
Treat the table as read-only
Droplet ORM inserts and updates outbox rows with its own SQL. Use the provider for dashboards and admin screens; use RelayCDCOutbox / PurgeCDCOutbox to change state.
Row Lifecycle
For each written outbox entity, ctx.Save():
- Serialises the change envelope once and queues an
INSERTintocdc_outboxon the same database pipeline as the entity statement. A bare single-entitySavetherefore opens a transaction for the two statements; insidectx.Transaction(...)the row rolls back with the entity. The row ispendingfor acdc;outboxentity andstoredfor anoutbox-only entity. - After the commit, publishes the event to
cdc.<database>.<table>using the same bytes stored in the row, so the message and the row are byte-identical and share the deterministicNats-Msg-Id(<subject>:<hash of payload>). The bytes must be kept rather than rebuilt: the envelope carries a timestamp, so a second serialisation would get a different id. - Flips the published rows to
dispatched(oneUPDATE ... WHERE ID IN (...)perSave) and setsDispatchedAt.
Failure modes:
- Publish fails: the row stays
pending;Savereturns*orm.PostCommitError. The relay republishes it later. - Mark fails after a successful publish:
Savereturns a*PostCommitErrorwrappingorm.ErrOutboxDispatchMark(cdc outbox dispatch mark failed). The row stayspending, the relay republishes it, and JetStream's duplicate window absorbs the second copy. The two cases are distinguishable witherrors.Is(err, orm.ErrOutboxDispatchMark)- "not delivered" versus "delivered twice".
Relaying Pending Rows
func RelayCDCOutbox(ctx Context, minAge time.Duration, limit int) (CDCOutboxRelayResult, error)
type CDCOutboxRelayResult struct {
Fetched int // rows read
Dispatched int // rows actually published and marked - loop on this
Published map[string]int // messages per NATS pool
}
RelayCDCOutbox selects up to limit rows with Status = 'pending' and CreatedAt <= now - minAge, ordered by ID, rebuilds each message from EntityName (which gives the subject cdc.<database>.<table>) and Payload, publishes them in one PublishBatch to the CDC stream's pool, and then marks them dispatched. From there the CDC reader forwards them to listeners like any other change. It lives in the library rather than in your code because the write side lives there: the relay must produce exactly the subject and Nats-Msg-Id the inline publish would have, or every recovery becomes a duplicate delivery instead of a no-op.
for {
res, err := orm.RelayCDCOutbox(ctx, 30*time.Second, 500)
if err != nil {
log.Printf("outbox relay: %v", err) // rows in a failed batch stay pending
}
if res.Dispatched == 0 {
break
}
}
minAgeskips rows still racing the ORM's own post-commit mark. Keep it well below the CDC stream'sDuplicateWindow(CDCStreamOptions.DuplicateWindow, 2 minutes by default) and run the relay often enough that a row is republished inside that window; a republish that arrives later is stored as a second message. 30 seconds is a reasonable value.- Page on
Dispatched, notFetched: a batch that fails to publish stayspending, so paging onFetchedwould re-read the same rows forever. - Delivery is at-least-once: the publish happens before the mark, so a row is never recorded as delivered before it is.
- A row whose
EntityNameis no longer on the feed (entity renamed or untagged since the row was written) is reported asoutbox relay: entity '%s' has no registered publisherrather than published to a subject nothing filters. The whole call returns at the first such row, before anything is published. - A failed batch is reported as
republish %d outbox events to pool %s: <cause>;Fetchedis still set. - Without a registered
CDCOutboxEntitythe function returns a zero result and no error.
Purging and Backlog
func PurgeCDCOutbox(ctx Context, olderThan time.Duration, limit int) (int, error)
func CDCOutboxBacklog(ctx Context) (depth int, oldest time.Duration, err error)
PurgeCDCOutboxdeletes up tolimitrows in a terminal status (dispatchedorstored) older thanolderThanand returns how many went. Pending rows are never deleted however old they get: one of those is an undelivered event, and dropping it is exactly the silent loss the outbox exists to prevent. A stuck relay therefore shows up as a growing backlog, not as vanished events.CDCOutboxBacklogreturns the number ofpendingrows and the age of the oldest. Both are alertable: a stalled relay is otherwise completely silent, because the rows are safe and nothing errors.
Both are no-ops (zero, nil) when CDCOutboxEntity is not registered.
Operations
A typical maintenance job:
func outboxMaintenance(ctx orm.Context) {
// Every 30-60 seconds.
for {
res, err := orm.RelayCDCOutbox(ctx, 30*time.Second, 500)
if err != nil {
log.Printf("outbox relay: %v", err)
}
if res.Dispatched == 0 {
break
}
}
depth, oldest, err := orm.CDCOutboxBacklog(ctx)
if err == nil && depth > 0 && oldest > 5*time.Minute {
alert("cdc outbox backlog", depth, oldest)
}
// Hourly is enough for the purge; keep the table bounded.
if _, err := orm.PurgeCDCOutbox(ctx, 7*24*time.Hour, 5000); err != nil {
log.Printf("outbox purge: %v", err)
}
}
The relay's publishes show up as orm_nats_operations_seconds{operation="publish"} and orm_nats_publish_batch_size on the CDC stream's pool, and as PUBLISH_BATCH in the NATS query log (orm.QueryLoggerOptions{Nats: true}). The CDC reader and the consumers count relayed messages like any other in orm_cdc_messages_total; duplicates absorbed by the stream are never delivered and never counted. See NATS and JetStream.
Deployment
CDCOutboxEntity is registered like any entity: GetAlters creates the cdc_outbox table and its index (Schema update), and Generate emits CdcOutboxProvider and enums.CDCOutboxStatus (Code generation). The outbox needs no NATS topology of its own: it publishes to the CDC stream that every cdc entity already uses.
External Producer
With registry.RegisterCDCStream(orm.CDCStreamOptions{Producer: orm.CDCProducerExternal}) a Debezium connector writes the change feed from the binlog (see Entity events). The binlog is then the delivery guarantee, and the outbox steps out of the write path:
Savewrites no outbox rows, forcdc;outboxand foroutbox-only entities alike, and publishes nothing.- The
outboxtag, the registeredCDCOutboxEntityand theValidate()rules stay as they are, so switching the producer is a one-line change. RelayCDCOutboxdoes not look at the producer: rows that are stillpendingfrom before the switch are republished tocdc.<database>.<table>. Drain the backlog (CDCOutboxBacklogreturns zero) before switching, then keep the purge running until the table is empty.
Complete Example
package main
import (
"context"
"errors"
"log"
"os/signal"
"syscall"
"time"
"github.com/DropletLive/orm"
"myapp/entities"
)
type UserEntity struct {
ID uint64 `orm:"cdc;outbox"`
Email string `orm:"required;length=255"`
Name string `orm:"required;length=100"`
}
func main() {
registry := orm.NewRegistry()
registry.RegisterMySQL("user:password@tcp(localhost:3306)/app", orm.DefaultPoolCode, &orm.MySQLOptions{})
registry.RegisterRedis("localhost:6379", 0, orm.DefaultPoolCode, nil)
registry.RegisterNats([]string{"nats://localhost:4222"}, orm.DefaultPoolCode, nil)
registry.RegisterEntity(UserEntity{}, orm.CDCOutboxEntity{})
registry.RegisterConsumer(orm.ConsumerDef{Name: "user-indexer", Entities: []any{UserEntity{}}})
engine, err := registry.Validate()
if err != nil {
panic(err)
}
defer engine.Nats(orm.DefaultPoolCode).Close()
appCtx, stop := signal.NotifyContext(context.Background(), syscall.SIGINT, syscall.SIGTERM)
defer stop()
ctx := engine.NewContext(appCtx)
// Tables (cdc_outbox included), then streams and durables.
alters, err := orm.GetAlters(ctx)
if err != nil {
panic(err)
}
for _, alter := range alters {
if err := alter.Exec(ctx); err != nil {
panic(err)
}
}
natsAlters, err := orm.GetNatsAlters(ctx)
if err != nil {
panic(err)
}
for _, alter := range natsAlters {
if err := alter.Exec(ctx); err != nil {
panic(err)
}
}
// The write: entity row + outbox row in one transaction, then publish to the feed, then mark.
u := entities.UserEntityProvider.New(ctx)
u.SetEmail("ann@example.com").SetName("Ann")
if err := ctx.Save(u); err != nil {
var postCommit *orm.PostCommitError
switch {
case errors.Is(err, orm.ErrOutboxDispatchMark):
log.Printf("published but not marked; relay will republish, stream dedups: %v", err)
case errors.As(err, &postCommit):
log.Printf("committed but not published; relay will publish: %v", err)
default:
panic(err)
}
}
// CDC reader: forwards the feed to the entity stream.
go loop(appCtx, "cdc-reader", orm.NewCDCReader(engine), 64)
// Consumer for the events (real and relayed alike).
indexer := orm.NewConsumer(engine, entities.ConsumerUserIndexer).
OnUserEntity(func(ctx orm.Context, ev *entities.UserEntityDirtyEvent) error {
log.Printf("user %d %s", ev.ID, ev.Op)
return nil
}).
Build()
go loop(appCtx, "user-indexer", indexer, 32)
// Relay + purge cron.
go func() {
ticker := time.NewTicker(30 * time.Second)
defer ticker.Stop()
for {
select {
case <-appCtx.Done():
return
case <-ticker.C:
cronCtx := engine.NewContext(appCtx)
for {
res, err := orm.RelayCDCOutbox(cronCtx, 30*time.Second, 500)
if err != nil {
log.Printf("outbox relay: %v", err)
}
if res.Dispatched == 0 {
break
}
}
if depth, oldest, err := orm.CDCOutboxBacklog(cronCtx); err == nil && depth > 0 {
log.Printf("outbox backlog: %d pending, oldest %s", depth, oldest)
}
if _, err := orm.PurgeCDCOutbox(cronCtx, 7*24*time.Hour, 5000); err != nil {
log.Printf("outbox purge: %v", err)
}
}
}
}()
<-appCtx.Done()
}
func loop(ctx context.Context, name string, consumer orm.StreamConsumer, batch int) {
for ctx.Err() == nil {
if err := consumer.Consume(ctx, batch, time.Second); err != nil && ctx.Err() == nil {
log.Printf("%s: %v", name, err)
time.Sleep(time.Second)
}
}
}