Database Schema

Sagaweaw persists all state in PostgreSQL, MySQL 8+, or H2. Flyway automatically selects the correct migration based on the detected database ({vendor}). Here is the complete schema.

Entity Diagram

┌─────────────────┐       ┌─────────────────┐
│     sagas       │───────│   saga_steps    │
└─────────────────┘       └─────────────────┘
         │
         │        ┌─────────────────┐
         ├────────│  saga_events    │
         │        └─────────────────┘
         │
         │        ┌─────────────────┐
         ├────────│ outbox_messages │
         │        └─────────────────┘
         │
         │        ┌─────────────────┐
         └────────│  dead_letters   │
                  └─────────────────┘

Horizontal scaling

Sagaweaw supports horizontal scaling — multiple instances sharing the same database with no extra coordination. Each saga records the instance that created it in the instance_id column.

Tables

`sagas`

Main table that stores the state of each saga in execution.

CREATE TABLE sagas (
    id              VARCHAR(36)                 NOT NULL,
    name            VARCHAR(255)                NOT NULL,
    status          VARCHAR(50)                 NOT NULL,
    context_json    JSONB                       NOT NULL DEFAULT '{}',
    created_at      TIMESTAMP(6) WITH TIME ZONE NOT NULL,
    updated_at      TIMESTAMP(6) WITH TIME ZONE NOT NULL,
    completed_at    TIMESTAMP(6) WITH TIME ZONE,
    idempotency_key VARCHAR(255),
    version         INTEGER                     NOT NULL DEFAULT 0,
    PRIMARY KEY (id)
);

CREATE UNIQUE INDEX idx_sagas_idempotency ON sagas (idempotency_key)
    WHERE idempotency_key IS NOT NULL;
CREATE INDEX idx_sagas_status     ON sagas (status);
CREATE INDEX idx_sagas_name       ON sagas (name);
CREATE INDEX idx_sagas_created_at ON sagas (created_at);

Column	Type	Description
`id`	VARCHAR(36)	Saga UUID
`name`	VARCHAR	Saga name (e.g. `"pix-payment"`)
`status`	VARCHAR	`STARTED`, `EXECUTING`, `COMPLETED`, `COMPENSATING`, `COMPENSATED`, `FAILED`
`context_json`	JSONB	Serialized context (immutable between steps)
`version`	INTEGER	Version for optimistic locking
`idempotency_key`	VARCHAR	Key to prevent duplicate execution

`saga_steps`

State of each step within a saga.

CREATE TABLE saga_steps (
    id             VARCHAR(36)                 NOT NULL,
    saga_id        VARCHAR(36)                 NOT NULL,
    step_name      VARCHAR(255)                NOT NULL,
    step_order     INTEGER                     NOT NULL,
    status         VARCHAR(50)                 NOT NULL DEFAULT 'PENDING',
    attempt        INTEGER                     NOT NULL DEFAULT 0,
    max_attempts   INTEGER                     NOT NULL DEFAULT 3,
    next_retry_at  TIMESTAMP(6) WITH TIME ZONE,
    last_error     TEXT,
    error_trace    TEXT,
    input_payload  JSONB,
    output_payload JSONB,
    executed_at    TIMESTAMP(6) WITH TIME ZONE,
    completed_at   TIMESTAMP(6) WITH TIME ZONE,
    duration_ms    BIGINT,
    PRIMARY KEY (id),
    CONSTRAINT fk_saga_steps_saga FOREIGN KEY (saga_id) REFERENCES sagas (id)
);

CREATE INDEX idx_saga_steps_saga_id ON saga_steps (saga_id);
CREATE INDEX idx_saga_steps_retry   ON saga_steps (status, next_retry_at);

Column	Type	Description
`step_name`	VARCHAR	Step name (e.g. `"validate-dict"`)
`step_order`	INTEGER	Execution order (0, 1, 2…)
`status`	VARCHAR	`PENDING`, `EXECUTING`, `COMPLETED`, `FAILED`, `COMPENSATING`, `COMPENSATED`
`attempt`	INTEGER	Current attempt
`max_attempts`	INTEGER	Maximum attempts (enables "attempt X/Y" display)
`next_retry_at`	TIMESTAMP	When the next retry is scheduled
`last_error`	TEXT	Last error message
`error_trace`	TEXT	Full stack trace of the last error
`input_payload`	JSONB	Serialized context at step entry
`output_payload`	JSONB	Serialized context at step exit (used by compensator)
`duration_ms`	BIGINT	Execution duration in milliseconds

`saga_events`

Immutable event log for complete audit. Only receives INSERT, never UPDATE.

CREATE TABLE saga_events (
    id         VARCHAR(36)                 NOT NULL,
    saga_id    VARCHAR(36)                 NOT NULL,
    step_name  VARCHAR(255),
    event_type VARCHAR(100)                NOT NULL,
    payload    JSONB,
    created_at TIMESTAMP(6) WITH TIME ZONE NOT NULL,
    PRIMARY KEY (id),
    CONSTRAINT fk_saga_events_saga FOREIGN KEY (saga_id) REFERENCES sagas (id)
);

CREATE INDEX idx_saga_events_saga_id ON saga_events (saga_id);
CREATE INDEX idx_saga_events_created ON saga_events (created_at);

Column	Type	Description
`event_type`	VARCHAR	`SAGA_STARTED`, `STEP_STARTED`, `STEP_COMPLETED`, `STEP_FAILED`, `COMPENSATION_STARTED`, etc.
`payload`	JSONB	Event data (error message, duration, etc.)

Query the event history of a saga via API:

GET /api/sagas/{id}/events

`outbox_messages`

Messages for the Transactional Outbox pattern. No FK on saga_id to support saga archiving.

CREATE TABLE outbox_messages (
    id               VARCHAR(36)                 NOT NULL,
    saga_id          VARCHAR(255)                NOT NULL,
    step_name        VARCHAR(255),
    topic            VARCHAR(255)                NOT NULL,
    payload          JSONB                       NOT NULL,
    headers          JSONB,
    published        BOOLEAN                     NOT NULL DEFAULT FALSE,
    publish_attempts INTEGER                     NOT NULL DEFAULT 0,
    created_at       TIMESTAMP(6) WITH TIME ZONE NOT NULL,
    published_at     TIMESTAMP(6) WITH TIME ZONE,
    PRIMARY KEY (id)
);

CREATE INDEX idx_outbox_unpublished ON outbox_messages (published, created_at);

Column	Type	Description
`topic`	VARCHAR	Target Kafka topic
`headers`	JSONB	Additional message headers
`publish_attempts`	INTEGER	Publication attempts
`published`	BOOLEAN	Whether it has been published

`dead_letters`

Sagas that exhausted all attempts and need manual intervention. No FK on saga_id to persist the forensic data even after saga removal.

CREATE TABLE dead_letters (
    id               VARCHAR(36)                 NOT NULL,
    saga_id          VARCHAR(255)                NOT NULL,
    step_name        VARCHAR(255)                NOT NULL,
    error_message    TEXT,
    error_trace      TEXT,
    context_snapshot TEXT,
    created_at       TIMESTAMP(6) WITH TIME ZONE NOT NULL,
    reprocessed      BOOLEAN                     NOT NULL DEFAULT FALSE,
    reprocessed_at   TIMESTAMP(6) WITH TIME ZONE,
    reprocessed_by   VARCHAR(255),
    PRIMARY KEY (id)
);

CREATE INDEX idx_dead_letters_saga_id  ON dead_letters (saga_id);
CREATE INDEX idx_dead_letters_unreproc ON dead_letters (reprocessed, created_at);

Column	Type	Description
`error_trace`	TEXT	Full stack trace
`context_snapshot`	TEXT	Snapshot of the saga context at the time of failure
`reprocessed`	BOOLEAN	Whether it was reprocessed via API or manually
`reprocessed_by`	VARCHAR	Identification of who reprocessed (`"api"` or user)

Migrations per Database

Sagaweaw uses Flyway with the {vendor} placeholder to automatically select the correct migration:

resources/
└── db/migration/sagaweaw/
    ├── postgresql/
    │   └── V1__sagaweaw_schema.sql   ← JSONB, TIMESTAMP(6) WITH TIME ZONE
    ├── mysql/
    │   └── V1__sagaweaw_schema.sql   ← JSON, DATETIME(6), TINYINT(1)
    └── h2/
        └── V1__sagaweaw_schema.sql   ← JSON, TIMESTAMP WITH TIME ZONE

No extra configuration is needed — just point datasource.url to the desired database.

Entity Diagram​

Tables​

sagas​

saga_steps​

saga_events​

outbox_messages​

dead_letters​

Migrations per Database​

Next Steps​