Droplet ORM
Guide
GitHub
Guide
GitHub
    • Introduction
    • Registry
    • Data Pools
    • Entities
    • Entity Fields
    • MySQL Indexes
    • Code Generation
    • Engine
    • Context
    • Schema Update
    • CRUD Operations
    • Transactions
    • Search
    • Elasticsearch
    • MySQL Queries
    • ClickHouse Queries
    • ClickHouse Schema
    • NATS and JetStream
    • Entity Change Events (CDC)
    • Consumers
    • Tasks and Job Runs
    • Transactional Outbox
    • Context Cache
    • Redis Cache
    • Fake Delete
    • Lifecycle Callbacks
    • Metrics
    • Redis Operations
    • Distributed Lock
    • Queries Log
    • Testing
    • Migrating from fluxaorm

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:

ErrorMeaning
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 transactionThe 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 IDOutbox rowPublished to NATS
cdcnoneyes, post-commit (lost if the publish fails)
cdc;outboxpending inside the transaction, dispatched post-commit once the publish succeededyes, and the relay republishes rows still pending
outboxstored (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():

  1. Serialises the change envelope once and queues an INSERT into cdc_outbox on the same database pipeline as the entity statement. A bare single-entity Save therefore opens a transaction for the two statements; inside ctx.Transaction(...) the row rolls back with the entity. The row is pending for a cdc;outbox entity and stored for an outbox-only entity.
  2. 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 deterministic Nats-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.
  3. Flips the published rows to dispatched (one UPDATE ... WHERE ID IN (...) per Save) and sets DispatchedAt.

Failure modes:

  • Publish fails: the row stays pending; Save returns *orm.PostCommitError. The relay republishes it later.
  • Mark fails after a successful publish: Save returns a *PostCommitError wrapping orm.ErrOutboxDispatchMark (cdc outbox dispatch mark failed). The row stays pending, the relay republishes it, and JetStream's duplicate window absorbs the second copy. The two cases are distinguishable with errors.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
    }
}
  • minAge skips rows still racing the ORM's own post-commit mark. Keep it well below the CDC stream's DuplicateWindow (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, not Fetched: a batch that fails to publish stays pending, so paging on Fetched would 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 EntityName is no longer on the feed (entity renamed or untagged since the row was written) is reported as outbox relay: entity '%s' has no registered publisher rather 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>; Fetched is still set.
  • Without a registered CDCOutboxEntity the 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)
  • PurgeCDCOutbox deletes up to limit rows in a terminal status (dispatched or stored) older than olderThan and 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.
  • CDCOutboxBacklog returns the number of pending rows 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:

  • Save writes no outbox rows, for cdc;outbox and for outbox-only entities alike, and publishes nothing.
  • The outbox tag, the registered CDCOutboxEntity and the Validate() rules stay as they are, so switching the producer is a one-line change.
  • RelayCDCOutbox does not look at the producer: rows that are still pending from before the switch are republished to cdc.<database>.<table>. Drain the backlog (CDCOutboxBacklog returns 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)
        }
    }
}
Edit this page
Last Updated: 11/16/26, 1:49 PM
Prev
Tasks and Job Runs
Next
Context Cache