Database table schemas

This page lists every table the framework creates, with its columns and the DDL for each database. To create the tables yourself, start with Managing the schema with a migration tool. All stores support six dialects: H2, HSQLDB, MySQL, MariaDB, PostgreSQL, and SQL Server. Wherever MariaDB reuses the MySQL DDL verbatim, its collapsible block says so instead of repeating the statement.

Managing the schema with a migration tool

The framework creates its tables at startup. To create them with Flyway, Liquibase or another tool, switch that off — each section below names its Schema creation property — and apply the bundled scripts before the application starts. The framework still inserts its own rows at startup (the partition count, each read-model topic’s counter), whoever created the tables.

Where the scripts are

Module (JAR) Path Scripts

spring-ddd-eventsourcing-jdbc

db/eventsourcing/<dialect>/

event-store-global-position-schema.sql, event-store-schema.sql, snapshot-schema.sql

spring-ddd-messaging-topic-jdbc

db/messaging/<dialect>/

  • Capture topic: event-capture-global-position-schema.sql, event-capture-records-schema.sql, partition-metadata-schema.sql

  • High-water mark and compaction: high-water-mark-store-schema.sql, topic-compaction-schema.sql

spring-ddd-messaging-consumer-jdbc

db/messaging/<dialect>/

consumer-checkpoints-schema.sql, consumer-undelivered-messages-schema.sql, consumer-skipped-messages-schema.sql, partition-ownership-schema.sql, partition-members-schema.sql, partition-instances-schema.sql

spring-ddd-cqrs-jdbc

db/cqrs/<dialect>/

read-model-topic-schema.sql, read-model-epochs-schema.sql

spring-ddd-cqrs-jdbc-query

db/cqrs/<dialect>/

query-table-schema.sql

spring-ddd-saga-jdbc

db/saga/<dialect>/

create-saga-table.sql, create-deadline-table.sql, create-deadline-lock-table.sql

spring-ddd-starter-domain-events-modulith-jdbc

db/domainevents-metadata/

schema-<dialect>.sql

The dialect folders are postgres, mysql (also used for MariaDB), h2, hsqldb and sqlserver. The saga scripts also have a mariadb folder, and the domain-event metadata scripts are named schema-postgresql.sql, schema-mysql.sql, schema-mariadb.sql, schema-h2.sql, schema-hsqldb.sql and schema-sqlserver.sql. consumer-checkpoints-schema.sql sits directly in db/messaging/ and is shared by all dialects except SQL Server, which has its own copy in db/messaging/sqlserver/. consumer-undelivered-messages-schema.sql sits directly in db/messaging/ too and is shared by PostgreSQL, H2 and HSQLDB; MySQL (also used for MariaDB) and SQL Server have their own copies in db/messaging/mysql/ and db/messaging/sqlserver/. consumer-skipped-messages-schema.sql has the same three copies.

event-store-schema.sql and snapshot-schema.sql name their table by the placeholders ${event_store_table} and ${aggregate_snapshots_table}, which the framework fills at startup once per table: with the shared names below for every aggregate without @EventTable, and with each aggregate’s own names otherwise. Index and constraint names are built from the same placeholder. An aggregate declaring @EventTable uses its own names, for example BANK_ACCOUNT_EVENTS and BANK_ACCOUNT_SNAPSHOTS for a BankAccount aggregate, with spring.ddd.eventsourcing.jdbc.table-prefix before a derived one. A migration tool must fill them too, as described below.
create-saga-table.sql names its table by the placeholder ${saga_instances_table}, which the framework fills at startup once per instance table: with the shared name for every saga without @SagaTable, and with each @SagaTable target otherwise, spring.ddd.saga.jdbc.table-prefix before a derived one.
The scripts under db/messaging/, db/cqrs/, db/eventsourcing/ and db/saga/ name each table by a placeholder, which the framework fills at startup with the configured name: jdbc.table-prefix before jdbc.table-names.<table> of the table’s area. Index and constraint names are built from the same placeholder, idx_${partition_members_table}_instance_id for example. MySQL and SQL Server keep an index name’s case as written and compare index names case-insensitively, so a derived index name reads in mixed case, for example idx_PARTITION_MEMBERS_instance_id; this is cosmetic. A migration tool must fill them too: give the tool the names as placeholder values (see the examples below) or copy the script and replace each placeholder with the table’s name. The DDL on this page shows every placeholder filled with its default name. A placeholder’s value is the table’s full name, its area’s prefix included. With spring.ddd.messaging.jdbc.table-prefix: ACME_, ${consumers_table} takes ACME_CONSUMERS.
Two applications can share one schema only if every area has its own jdbc.table-prefix in both applications. An area left unprefixed in both is shared between them and mixes their data; for the messaging area that includes the topics' high-water marks. A name written in an annotation (@EventTable, @QueryProjection(table = …​), @SagaTable) takes no prefix, so each application writes its own there.
Placeholder Property Default

${consumers_table}

spring.ddd.messaging.jdbc.table-names.consumers

CONSUMERS

${consumer_checkpoints_table}

spring.ddd.messaging.jdbc.table-names.consumer-checkpoints

CONSUMER_CHECKPOINTS

${consumer_undelivered_messages_table}

spring.ddd.messaging.jdbc.table-names.consumer-undelivered-messages

CONSUMER_UNDELIVERED_MESSAGES

${consumer_skipped_messages_table}

spring.ddd.messaging.jdbc.table-names.consumer-skipped-messages

CONSUMER_SKIPPED_MESSAGES

${partition_ownership_table}

spring.ddd.messaging.jdbc.table-names.partition-ownership

PARTITION_OWNERSHIP

${partition_members_table}

spring.ddd.messaging.jdbc.table-names.partition-members

PARTITION_MEMBERS

${partition_instances_table}

spring.ddd.messaging.jdbc.table-names.partition-instances

PARTITION_INSTANCES

${event_capture_records_table}

spring.ddd.messaging.jdbc.table-names.event-capture-records

EVENT_CAPTURE_RECORDS

${event_capture_global_position_table}

spring.ddd.messaging.jdbc.table-names.event-capture-global-position

EVENT_CAPTURE_GLOBAL_POSITION

${high_water_mark_store_table}

spring.ddd.messaging.jdbc.table-names.high-water-mark-store

HIGH_WATER_MARK_STORE

${partition_metadata_table}

spring.ddd.partitioning.jdbc.table-names.partition-metadata

PARTITION_METADATA

${event_store_table}

spring.ddd.eventsourcing.jdbc.table-names.event-store

EVENT_STORE

${aggregate_snapshots_table}

spring.ddd.eventsourcing.jdbc.table-names.aggregate-snapshots

AGGREGATE_SNAPSHOTS

${event_store_global_position_table}

spring.ddd.eventsourcing.jdbc.table-names.event-store-global-position

EVENT_STORE_GLOBAL_POSITION

${read_model_capture_records_table}

spring.ddd.messaging.jdbc.table-names.read-model-capture-records

READ_MODEL_CAPTURE_RECORDS

${read_model_capture_positions_table}

spring.ddd.messaging.jdbc.table-names.read-model-capture-positions

READ_MODEL_CAPTURE_POSITIONS

${topic_compaction_table}

spring.ddd.messaging.jdbc.table-names.topic-compaction

TOPIC_COMPACTION

${read_model_epochs_table}

spring.ddd.cqrs.jdbc.table-names.read-model-epochs

READ_MODEL_EPOCHS

${saga_instances_table}

spring.ddd.saga.jdbc.table-names.saga-instances, or each @SagaTable target

SAGA_INSTANCES

${saga_deadlines_table}

spring.ddd.saga.jdbc.table-names.saga-deadlines

SAGA_DEADLINES

${saga_deadline_locks_table}

spring.ddd.saga.jdbc.table-names.saga-deadline-locks

SAGA_DEADLINE_LOCKS

${query_table}

the projection’s name with spring.ddd.cqrs.jdbc.table-prefix before it, or @QueryProjection(table = "…​")

none

query-table-schema.sql also contains ${query_columns}, the column definitions of the projection’s @QueryField properties; write them yourself in the copy of the script.

What to take care of

  • Apply a position script before the table that draws from it: event-store-global-position-schema.sql before any event table, event-capture-global-position-schema.sql before event-capture-records-schema.sql.

  • PARTITION_METADATA is needed by the event store and by the capture topic.

  • On PostgreSQL keep the global-position sequence at CACHE 1, or the high-water mark refuses to advance.

  • Write events only through the framework: a row whose position is drawn any other way can be passed by the high-water mark while its transaction is still open.

  • Apply read-model-topic-schema.sql and topic-compaction-schema.sql to every datasource holding a persistent projection with listeners.

  • On PostgreSQL every datasource holding read-model topics also needs high-water-mark-store-schema.sql, and its read-model positions sequence (READ_MODEL_CAPTURE_POSITIONS by default) must stay at CACHE 1, or the read-model topics' high-water mark refuses to advance.

  • A query table’s read models are the states of their messages in the read-model records table (READ_MODEL_CAPTURE_RECORDS by default), so keep a query table in the same database as that table, and give its message_id the type of its id.

  • Never delete from the read-model records table yourself: it holds the only copy of every query projection’s read models. A query table row whose message is gone reads as no read model until its next change, so rebuild the projection instead. Compaction and tombstone retention delete from it on their own schedules.

  • Tables of JPA entities and JPA read models are Hibernate’s; validate them with spring.jpa.hibernate.ddl-auto: validate.

Flyway example

spring:
  flyway:
    locations: classpath:db/migration   # your own migration directory
    placeholders:
      event_store_global_position_table: ACME_EVENT_STORE_GLOBAL_POSITION
  ddd:
    eventsourcing:
      jdbc:
        initialize-schema: never
        table-prefix: ACME_

Copy the bundled scripts out of the JAR (under db/eventsourcing/<dialect>/) and place the results inside your Flyway migration directory as versioned migrations — Flyway does not pick them up from their bundled location, because those filenames do not follow its V naming convention. Flyway applies all scripts in filename order, so name event-store-global-position-schema.sql with a lower version prefix than the event and snapshot scripts (for example V1global_position.sql, V2bank_account_events.sql, V3bank_account_snapshots.sql).

Flyway replaces ${...} placeholders in a migration from spring.flyway.placeholders.<name>. A shared name therefore needs only its value, the full name as in the example above. A placeholder takes one value for every script, so for a table named per aggregate or per saga copy the script once per table and replace its placeholder in the copy.

Liquibase example

Create a changelog that applies the scripts in the correct order, with one changeset pair per distinct aggregate table name. Liquibase replaces ${...} in the content of a sql or sqlFile change from the changelog parameters, which Spring Boot takes from spring.liquibase.parameters.<name>, so the bundled position script can be applied from the JAR as it is:

# db/changelog/db.changelog-master.yaml
databaseChangeLog:
  - changeSet:
      id: 1-global-position
      author: spring-ddd
      changes:
        - sqlFile:
            path: db/eventsourcing/postgres/event-store-global-position-schema.sql
            relativeToChangelogFile: false
  - changeSet:
      id: 2-bank-account-events
      author: spring-ddd
      changes:
        - sqlFile:
            path: db/migrations/bank_account_events.sql  (1)
            relativeToChangelogFile: false
  - changeSet:
      id: 3-bank-account-snapshots
      author: spring-ddd
      changes:
        - sqlFile:
            path: db/migrations/bank_account_snapshots.sql  (2)
            relativeToChangelogFile: false
1 A copy of event-store-schema.sql with ${event_store_table} replaced by ACME_BANK_ACCOUNT_EVENTS.
2 A copy of snapshot-schema.sql with ${aggregate_snapshots_table} replaced by ACME_BANK_ACCOUNT_SNAPSHOTS.

Then point Liquibase at the changelog and disable auto-creation:

spring:
  liquibase:
    change-log: classpath:db/changelog/db.changelog-master.yaml
    parameters:
      event_store_global_position_table: ACME_EVENT_STORE_GLOBAL_POSITION  # fills the position script
  ddd:
    eventsourcing:
      jdbc:
        initialize-schema: never
        table-prefix: ACME_

Event store

Module: spring-ddd-eventsourcing-jdbc.
Schema creation: spring.ddd.eventsourcing.jdbc.initialize-schema (default always).

The event store creates one event table per @EventSourced aggregate and, when snapshots are enabled, one snapshot table per aggregate. Tables are created only when at least one @EventSourced aggregate is detected.

Event-store table

Stores the ordered event stream for a single aggregate type. The table name is derived in SCREAMING_SNAKE_CASE from the aggregate class name, or overridden with @EventTable. The representative name account_event is used in the DDL below.

Column Type Notes

id

UUID | VARCHAR(36) | CHAR(36)

Primary key, a time-ordered UUIDv7. UUID on H2 and PostgreSQL; VARCHAR(36) on HSQLDB and SQL Server; CHAR(36) on MySQL/MariaDB.

aggregate_id

VARCHAR(255)

Not null. Serialised aggregate identity.

aggregate_type

VARCHAR(255)

Not null. Fully-qualified aggregate class name.

sequence_no

BIGINT

Not null. Per-aggregate monotonic sequence starting at 0. Unique composite constraint with aggregate_id.

event_type

VARCHAR(255)

Not null. Fully-qualified event class name.

event_revision

INT

Not null, default 0. Schema revision for upcasting.

payload

CLOB | JSON | LONGTEXT | NVARCHAR(MAX)

Not null. JSON event payload. JSON on PostgreSQL; CLOB on H2/HSQLDB; LONGTEXT on MySQL/MariaDB; NVARCHAR(MAX) on SQL Server. Kept exactly as written on every engine.

metadata

CLOB | JSON | LONGTEXT | NVARCHAR(MAX)

Nullable. Optional event metadata. Same type as payload.

occurred_at

TIMESTAMP | TIMESTAMPTZ | DATETIME(6) | DATETIME2

Not null, defaults to current timestamp. TIMESTAMPTZ on PostgreSQL; DATETIME(6) on MySQL/MariaDB; DATETIME2 on SQL Server; TIMESTAMP on H2/HSQLDB.

partition_key

SMALLINT

Not null. Stable FNV-1a bucket of the aggregate id in [0, N), where N is the fixed partition count (spring.ddd.partitioning.partitions). Stamped at append time; never recomputed. Indexed together with global_position (idx_<event table>_partition) so a consumer can read one partition’s events in position order.

global_position

BIGINT

Not null. Total-order position drawn from the global sequence or table. Indexed, and indexed again as the second column of idx_<event table>_partition.

Override: @EventTable(name = "…") on the aggregate class.

DDL — all dialects 
-- PostgreSQL
CREATE TABLE IF NOT EXISTS account_event (
    id              UUID         PRIMARY KEY,
    aggregate_id    VARCHAR(255) NOT NULL,
    aggregate_type  VARCHAR(255) NOT NULL,
    sequence_no     BIGINT       NOT NULL,
    event_type      VARCHAR(255) NOT NULL,
    event_revision  INT          NOT NULL DEFAULT 0,
    payload         JSON         NOT NULL,
    metadata        JSON,
    occurred_at     TIMESTAMPTZ  NOT NULL DEFAULT now(),
    partition_key   SMALLINT     NOT NULL,
    global_position BIGINT       NOT NULL,
    UNIQUE (aggregate_id, sequence_no)
);

CREATE INDEX IF NOT EXISTS idx_account_event_aggregate
    ON account_event (aggregate_id, aggregate_type, sequence_no);

CREATE INDEX IF NOT EXISTS idx_account_event_global
    ON account_event (global_position);

CREATE INDEX IF NOT EXISTS idx_account_event_partition
    ON account_event (partition_key, global_position);

-- H2
CREATE TABLE IF NOT EXISTS account_event (
    id              UUID         PRIMARY KEY,
    aggregate_id    VARCHAR(255) NOT NULL,
    aggregate_type  VARCHAR(255) NOT NULL,
    sequence_no     BIGINT       NOT NULL,
    event_type      VARCHAR(255) NOT NULL,
    event_revision  INT          NOT NULL DEFAULT 0,
    payload         CLOB         NOT NULL,
    metadata        CLOB,
    occurred_at     TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
    partition_key   SMALLINT     NOT NULL,
    global_position BIGINT       NOT NULL,
    UNIQUE (aggregate_id, sequence_no)
);

CREATE INDEX IF NOT EXISTS idx_account_event_aggregate
    ON account_event (aggregate_id, aggregate_type, sequence_no);

CREATE INDEX IF NOT EXISTS idx_account_event_global
    ON account_event (global_position);

CREATE INDEX IF NOT EXISTS idx_account_event_partition
    ON account_event (partition_key, global_position);

-- HSQLDB
CREATE TABLE IF NOT EXISTS account_event (
    id              VARCHAR(36)  PRIMARY KEY,
    aggregate_id    VARCHAR(255) NOT NULL,
    aggregate_type  VARCHAR(255) NOT NULL,
    sequence_no     BIGINT       NOT NULL,
    event_type      VARCHAR(255) NOT NULL,
    event_revision  INT          DEFAULT 0 NOT NULL,
    payload         CLOB         NOT NULL,
    metadata        CLOB,
    occurred_at     TIMESTAMP    DEFAULT CURRENT_TIMESTAMP NOT NULL,
    partition_key   SMALLINT     NOT NULL,
    global_position BIGINT       NOT NULL,
    UNIQUE (aggregate_id, sequence_no)
);

CREATE INDEX IF NOT EXISTS idx_account_event_aggregate
    ON account_event (aggregate_id, aggregate_type, sequence_no);

CREATE INDEX IF NOT EXISTS idx_account_event_global
    ON account_event (global_position);

CREATE INDEX IF NOT EXISTS idx_account_event_partition
    ON account_event (partition_key, global_position);

-- MySQL / MariaDB
CREATE TABLE IF NOT EXISTS account_event (
    id              CHAR(36)     NOT NULL PRIMARY KEY,
    aggregate_id    VARCHAR(255) NOT NULL,
    aggregate_type  VARCHAR(255) NOT NULL,
    sequence_no     BIGINT       NOT NULL,
    event_type      VARCHAR(255) NOT NULL,
    event_revision  INT          NOT NULL DEFAULT 0,
    payload         LONGTEXT     NOT NULL,
    metadata        LONGTEXT,
    occurred_at     DATETIME(6)  NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
    partition_key   SMALLINT     NOT NULL,
    global_position BIGINT       NOT NULL,
    UNIQUE KEY uq_account_event_aggregate_sequence (aggregate_id, sequence_no),
    UNIQUE KEY uq_account_event_global (global_position),
    INDEX idx_account_event_aggregate (aggregate_id, aggregate_type, sequence_no),
    INDEX idx_account_event_partition (partition_key, global_position)
);

-- SQL Server
IF OBJECT_ID(N'account_event', N'U') IS NULL
    CREATE TABLE account_event (
        id              VARCHAR(36)   NOT NULL PRIMARY KEY,
        aggregate_id    VARCHAR(255)  NOT NULL,
        aggregate_type  VARCHAR(255)  NOT NULL,
        sequence_no     BIGINT        NOT NULL,
        event_type      VARCHAR(255)  NOT NULL,
        event_revision  INT           NOT NULL DEFAULT 0,
        payload         NVARCHAR(MAX) NOT NULL,
        metadata        NVARCHAR(MAX),
        occurred_at     DATETIME2     NOT NULL DEFAULT SYSUTCDATETIME(),
        partition_key   SMALLINT      NOT NULL,
        global_position BIGINT        NOT NULL,
        CONSTRAINT uq_account_event_sequence UNIQUE (aggregate_id, sequence_no)
    );

IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = 'idx_account_event_aggregate'
               AND object_id = OBJECT_ID(N'account_event'))
    CREATE INDEX idx_account_event_aggregate
        ON account_event (aggregate_id, aggregate_type, sequence_no);

IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = 'idx_account_event_global'
               AND object_id = OBJECT_ID(N'account_event'))
    CREATE INDEX idx_account_event_global
        ON account_event (global_position);

IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = 'idx_account_event_partition'
               AND object_id = OBJECT_ID(N'account_event'))
    CREATE INDEX idx_account_event_partition
        ON account_event (partition_key, global_position);

Snapshot table

Stores periodic aggregate snapshots to avoid replaying the full event stream on load. Created only when spring.ddd.eventsourcing.snapshot.enabled is true (default). The table name defaults to the shared snapshot table (spring.ddd.eventsourcing.jdbc.table-names.aggregate-snapshots), or is overridden with @EventTable(snapshots = "…"). The representative name account_event_snapshots is used below.

Column Type Notes

id

UUID | VARCHAR(36) | CHAR(36)

Primary key, a time-ordered UUIDv7. UUID on H2 and PostgreSQL; VARCHAR(36) on HSQLDB and SQL Server; CHAR(36) on MySQL/MariaDB.

aggregate_id

VARCHAR(255)

Not null. Serialised aggregate identity.

aggregate_type

VARCHAR(255)

Not null. Fully-qualified aggregate class name.

sequence_no

BIGINT

Not null. Event sequence number at the time the snapshot was taken.

snapshot_version

INT

Not null, default 1. Snapshot schema revision.

payload

CLOB | JSON | LONGTEXT | NVARCHAR(MAX)

Not null. Serialised aggregate state. JSON on PostgreSQL; CLOB on H2/HSQLDB; LONGTEXT on MySQL/MariaDB; NVARCHAR(MAX) on SQL Server.

created_at

TIMESTAMP | TIMESTAMPTZ | DATETIME(6) | DATETIME2

Not null, defaults to current timestamp. TIMESTAMPTZ on PostgreSQL; DATETIME(6) on MySQL/MariaDB; DATETIME2 on SQL Server; TIMESTAMP on H2/HSQLDB.

Override: @EventTable(snapshots = "…") on the aggregate class. Schema gate: spring.ddd.eventsourcing.snapshot.enabled (default true).

DDL — all dialects 
-- PostgreSQL
CREATE TABLE IF NOT EXISTS account_event_snapshots (
    id               UUID         PRIMARY KEY,
    aggregate_id     VARCHAR(255) NOT NULL,
    aggregate_type   VARCHAR(255) NOT NULL,
    sequence_no      BIGINT       NOT NULL,
    snapshot_version INT          NOT NULL DEFAULT 1,
    payload          JSON         NOT NULL,
    created_at       TIMESTAMPTZ  NOT NULL DEFAULT now(),
    UNIQUE (aggregate_id, sequence_no)
);

CREATE INDEX IF NOT EXISTS idx_account_event_snapshots_lookup
    ON account_event_snapshots (aggregate_id, aggregate_type, snapshot_version, sequence_no DESC);

-- H2
CREATE TABLE IF NOT EXISTS account_event_snapshots (
    id               UUID         PRIMARY KEY,
    aggregate_id     VARCHAR(255) NOT NULL,
    aggregate_type   VARCHAR(255) NOT NULL,
    sequence_no      BIGINT       NOT NULL,
    snapshot_version INT          NOT NULL DEFAULT 1,
    payload          CLOB         NOT NULL,
    created_at       TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE (aggregate_id, sequence_no)
);

CREATE INDEX IF NOT EXISTS idx_account_event_snapshots_lookup
    ON account_event_snapshots (aggregate_id, aggregate_type, snapshot_version, sequence_no DESC);

-- HSQLDB
CREATE TABLE IF NOT EXISTS account_event_snapshots (
    id               VARCHAR(36)  PRIMARY KEY,
    aggregate_id     VARCHAR(255) NOT NULL,
    aggregate_type   VARCHAR(255) NOT NULL,
    sequence_no      BIGINT       NOT NULL,
    snapshot_version INT          DEFAULT 1 NOT NULL,
    payload          CLOB         NOT NULL,
    created_at       TIMESTAMP    DEFAULT CURRENT_TIMESTAMP NOT NULL,
    UNIQUE (aggregate_id, sequence_no)
);

CREATE INDEX IF NOT EXISTS idx_account_event_snapshots_lookup
    ON account_event_snapshots (aggregate_id, aggregate_type, snapshot_version, sequence_no DESC);

-- MySQL / MariaDB
CREATE TABLE IF NOT EXISTS account_event_snapshots (
    id               CHAR(36)     NOT NULL PRIMARY KEY,
    aggregate_id     VARCHAR(255) NOT NULL,
    aggregate_type   VARCHAR(255) NOT NULL,
    sequence_no      BIGINT       NOT NULL,
    snapshot_version INT          NOT NULL DEFAULT 1,
    payload          LONGTEXT     NOT NULL,
    created_at       DATETIME(6)  NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
    UNIQUE KEY uq_account_event_snapshots_sequence (aggregate_id, sequence_no),
    INDEX idx_account_event_snapshots_lookup (aggregate_id, aggregate_type, snapshot_version, sequence_no)
);

-- SQL Server
IF OBJECT_ID(N'account_event_snapshots', N'U') IS NULL
    CREATE TABLE account_event_snapshots (
        id               VARCHAR(36)   NOT NULL PRIMARY KEY,
        aggregate_id     VARCHAR(255)  NOT NULL,
        aggregate_type   VARCHAR(255)  NOT NULL,
        sequence_no      BIGINT        NOT NULL,
        snapshot_version INT           NOT NULL DEFAULT 1,
        payload          NVARCHAR(MAX) NOT NULL,
        created_at       DATETIME2     NOT NULL DEFAULT SYSUTCDATETIME(),
        CONSTRAINT uq_account_event_snapshots_sequence UNIQUE (aggregate_id, sequence_no)
    );

IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = 'idx_account_event_snapshots_lookup'
               AND object_id = OBJECT_ID(N'account_event_snapshots'))
    CREATE INDEX idx_account_event_snapshots_lookup
        ON account_event_snapshots (aggregate_id, aggregate_type, snapshot_version, sequence_no DESC);

EVENT_STORE_GLOBAL_POSITION

Global position counter shared by all per-aggregate event tables. PostgreSQL uses a native sequence, drawn by the event store’s insert only after the appending transaction has its transaction id; the global_position column has no default. MySQL, MariaDB, H2, HSQLDB, and SQL Server use a single-row counter table instead, locked by each append until it commits and restored when the append rolls back, so their positions never have a gap (see Choosing a database).

Object Type Notes

EVENT_STORE_GLOBAL_POSITION

SEQUENCE or TABLE

PostgreSQL: CREATE SEQUENCE. MySQL, MariaDB, H2, HSQLDB, SQL Server: a single-row table with id TINYINT PK and val BIGINT, seeded with (1, 0); the seed never overwrites an existing row.
DDL — all dialects 
-- PostgreSQL
CREATE SEQUENCE IF NOT EXISTS EVENT_STORE_GLOBAL_POSITION START WITH 1 CACHE 1;

-- H2 / HSQLDB
CREATE TABLE IF NOT EXISTS EVENT_STORE_GLOBAL_POSITION (
    id  TINYINT NOT NULL PRIMARY KEY,
    val BIGINT  NOT NULL
);
MERGE INTO EVENT_STORE_GLOBAL_POSITION t
    USING (VALUES (1, 0)) AS s (id, val) ON t.id = s.id
    WHEN NOT MATCHED THEN INSERT (id, val) VALUES (s.id, s.val);

-- MySQL / MariaDB
CREATE TABLE IF NOT EXISTS EVENT_STORE_GLOBAL_POSITION (
    id  TINYINT NOT NULL PRIMARY KEY,
    val BIGINT  NOT NULL
);
INSERT INTO EVENT_STORE_GLOBAL_POSITION (id, val) VALUES (1, 0)
    ON DUPLICATE KEY UPDATE id = id;

-- SQL Server
IF OBJECT_ID(N'EVENT_STORE_GLOBAL_POSITION', N'U') IS NULL
    CREATE TABLE EVENT_STORE_GLOBAL_POSITION (
        id  TINYINT NOT NULL PRIMARY KEY,
        val BIGINT  NOT NULL
    );
INSERT INTO EVENT_STORE_GLOBAL_POSITION (id, val)
    SELECT 1, 0
    WHERE NOT EXISTS (SELECT 1 FROM EVENT_STORE_GLOBAL_POSITION WITH (UPDLOCK, HOLDLOCK) WHERE id = 1);

The PostgreSQL CACHE 1 clause is load-bearing, not tuning: the high-water mark reads the sequence’s last value as the highest position handed out, and a larger cache reports values a session has reserved but hands out later, whose events the mark would then pass. The framework owns this sequence and enforces the clause at schema init (an idempotent ALTER SEQUENCE), so it also corrects a sequence left over with a wider cache. When you manage the schema yourself, keep the clause: on a sequence with a larger cache the detector fails every cycle with an IllegalStateException naming the fix, logged at WARN on the first failure and at DEBUG afterwards, and the high-water mark stays where it is until the sequence is CACHE 1 again. After setting the cache back to 1, restart every application that appends events: a session that reserved values under the wider cache still hands them out until its connection closes.

Capture topic

Module: spring-ddd-messaging-topic-jdbc.
Schema creation: spring.ddd.messaging.jdbc.initialize-schema (default always), while spring.ddd.messaging.capture.event.enabled (default true).

A topic of domain events, in position order, published outside the event store — independent of the event store’s per-aggregate tables, and excluding event-sourced event types, which already have the event store’s own topic. The capture tap appends to it as events are published. Unlike the event store, it has retention: every message at or below the topic start (the topic_start of its high-water-mark row) has been deleted. A projection or saga that only needs the total order of everything published, rather than one aggregate’s own stream, reads this topic instead of every event table individually.

EVENT_CAPTURE_RECORDS

Stores one row per captured event, already partitioned and already encoded by the time it reaches this table.

Column Type Notes

id

UUID | VARCHAR(36) | CHAR(36)

Primary key, a time-ordered UUIDv7. UUID on H2 and PostgreSQL; VARCHAR(36) on HSQLDB and SQL Server; CHAR(36) on MySQL/MariaDB. Never read back; it exists only so a captured row carries the same kind of identity as an event-store row.

global_position

BIGINT

Not null. This topic’s own total-order position, numbered independently of the event store’s global_position; see EVENT_CAPTURE_GLOBAL_POSITION below. Unique, and indexed again as the second column of idx_event_capture_records_partition.

partition_key

SMALLINT

Not null. The event’s partition, stamped before the append and never recomputed. Indexed together with global_position (idx_event_capture_records_partition) so a consumer can read one partition’s messages in position order.

event_type

VARCHAR(255)

Not null. The stored event-type identifier the codec registered it under, e.g. account.AccountOpened.

event_revision

INT

Not null, default 0. Schema revision for upcasting.

payload

CLOB | JSON | LONGTEXT | NVARCHAR(MAX)

Not null. The encoded event payload. JSON on PostgreSQL — not JSONB, like the event store’s tables: JSONB decomposes and reserializes the document (key order, whitespace, number formatting), so it would return a payload textually different from the one written, while JSON and every other engine’s column return it byte for byte. The capture topic is read by position, by partition and by message key, never by payload content, so `JSONB’s indexing advantages buy nothing here. CLOB on H2/HSQLDB; LONGTEXT on MySQL/MariaDB; NVARCHAR(MAX) on SQL Server.

metadata

CLOB | JSON | LONGTEXT | NVARCHAR(MAX)

Nullable. The encoded metadata envelope, or absent when the event carried none. Same type as payload.

occurred_at

TIMESTAMP | TIMESTAMPTZ | DATETIME(6) | DATETIME2

Not null. When the event occurred, carried over from the message rather than defaulted by the database. TIMESTAMPTZ on PostgreSQL; DATETIME(6) on MySQL/MariaDB; DATETIME2 on SQL Server; TIMESTAMP on H2/HSQLDB. Stored as the UTC wall clock on every engine whose column carries no time zone (H2, HSQLDB, MySQL/MariaDB, SQL Server), so the age a retention policy measures does not depend on the zone of the JVM that wrote or deleted it.

message_key

VARCHAR(255) | NVARCHAR(255)

Nullable. The event’s message key, e.g. an account id, as the string it is partitioned by; null for a message without one. A materialization of one read model reads by it, through idx_event_capture_records_key together with global_position. Compares exactly: utf8mb4_bin on MySQL/MariaDB, NVARCHAR(255) with a binary collation on SQL Server, so keys that differ only in case are two keys; keys that differ only in trailing spaces compare equal on MySQL, MariaDB, SQL Server and HSQLDB, and a materialization still keeps them apart. An event whose key is longer than 255 characters is refused when it is published.
DDL — all dialects 
-- PostgreSQL
CREATE TABLE IF NOT EXISTS EVENT_CAPTURE_RECORDS (
    id              UUID         PRIMARY KEY,
    global_position BIGINT       NOT NULL,
    partition_key   SMALLINT     NOT NULL,
    event_type      VARCHAR(255) NOT NULL,
    event_revision  INT          NOT NULL DEFAULT 0,
    payload         JSON         NOT NULL,
    metadata        JSON,
    occurred_at     TIMESTAMPTZ  NOT NULL,
    message_key     VARCHAR(255),
    UNIQUE (global_position)
);

CREATE INDEX IF NOT EXISTS idx_event_capture_records_partition
    ON EVENT_CAPTURE_RECORDS (partition_key, global_position);

CREATE INDEX IF NOT EXISTS idx_event_capture_records_key
    ON EVENT_CAPTURE_RECORDS (message_key, global_position);

-- H2
CREATE TABLE IF NOT EXISTS EVENT_CAPTURE_RECORDS (
    id              UUID         PRIMARY KEY,
    global_position BIGINT       NOT NULL,
    partition_key   SMALLINT     NOT NULL,
    event_type      VARCHAR(255) NOT NULL,
    event_revision  INT          NOT NULL DEFAULT 0,
    payload         CLOB         NOT NULL,
    metadata        CLOB,
    occurred_at     TIMESTAMP    NOT NULL,
    message_key     VARCHAR(255),
    UNIQUE (global_position)
);

CREATE INDEX IF NOT EXISTS idx_event_capture_records_partition
    ON EVENT_CAPTURE_RECORDS (partition_key, global_position);

CREATE INDEX IF NOT EXISTS idx_event_capture_records_key
    ON EVENT_CAPTURE_RECORDS (message_key, global_position);

-- HSQLDB
CREATE TABLE IF NOT EXISTS EVENT_CAPTURE_RECORDS (
    id              VARCHAR(36)  PRIMARY KEY,
    global_position BIGINT       NOT NULL,
    partition_key   SMALLINT     NOT NULL,
    event_type      VARCHAR(255) NOT NULL,
    event_revision  INT          DEFAULT 0 NOT NULL,
    payload         CLOB         NOT NULL,
    metadata        CLOB,
    occurred_at     TIMESTAMP    NOT NULL,
    message_key     VARCHAR(255),
    UNIQUE (global_position)
);

CREATE INDEX IF NOT EXISTS idx_event_capture_records_partition
    ON EVENT_CAPTURE_RECORDS (partition_key, global_position);

CREATE INDEX IF NOT EXISTS idx_event_capture_records_key
    ON EVENT_CAPTURE_RECORDS (message_key, global_position);

-- MySQL / MariaDB
CREATE TABLE IF NOT EXISTS EVENT_CAPTURE_RECORDS (
    id              CHAR(36)     NOT NULL PRIMARY KEY,
    global_position BIGINT       NOT NULL,
    partition_key   SMALLINT     NOT NULL,
    event_type      VARCHAR(255) NOT NULL,
    event_revision  INT          NOT NULL DEFAULT 0,
    payload         LONGTEXT     NOT NULL,
    metadata        LONGTEXT,
    occurred_at     DATETIME(6)  NOT NULL,
    message_key     VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin,
    UNIQUE KEY uq_event_capture_records_global_position (global_position),
    INDEX idx_event_capture_records_partition (partition_key, global_position),
    INDEX idx_event_capture_records_key (message_key, global_position)
);

-- SQL Server
IF OBJECT_ID(N'EVENT_CAPTURE_RECORDS', N'U') IS NULL
    CREATE TABLE EVENT_CAPTURE_RECORDS (
        id              VARCHAR(36)   NOT NULL PRIMARY KEY,
        global_position BIGINT        NOT NULL,
        partition_key   SMALLINT      NOT NULL,
        event_type      VARCHAR(255)  NOT NULL,
        event_revision  INT           NOT NULL DEFAULT 0,
        payload         NVARCHAR(MAX) NOT NULL,
        metadata        NVARCHAR(MAX),
        occurred_at     DATETIME2     NOT NULL,
        message_key     NVARCHAR(255) COLLATE Latin1_General_100_BIN2,
        CONSTRAINT uq_event_capture_records_global_position UNIQUE (global_position)
    );

IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = 'idx_event_capture_records_partition'
               AND object_id = OBJECT_ID(N'EVENT_CAPTURE_RECORDS'))
    CREATE INDEX idx_event_capture_records_partition
        ON EVENT_CAPTURE_RECORDS (partition_key, global_position);

IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = 'idx_event_capture_records_key'
               AND object_id = OBJECT_ID(N'EVENT_CAPTURE_RECORDS'))
    CREATE INDEX idx_event_capture_records_key
        ON EVENT_CAPTURE_RECORDS (message_key, global_position);

EVENT_CAPTURE_GLOBAL_POSITION

Global position counter for the capture topic, numbered independently of the event store’s own global-position counter (EVENT_STORE_GLOBAL_POSITION by default). On PostgreSQL it is a native sequence, drawn only after the appending transaction has its transaction id, so concurrent appends are never serialized against each other; the sequence must stay CACHE 1, for the same reason as the event store’s, and the framework enforces the clause the same way. On MySQL, MariaDB, H2, HSQLDB, and SQL Server it is a single-row counter table instead, locked by each append until it commits and restored when the append rolls back — appends against these engines serialize on this row.

Object Type Notes

EVENT_CAPTURE_GLOBAL_POSITION

SEQUENCE or TABLE

PostgreSQL: CREATE SEQUENCE. MySQL, MariaDB, H2, HSQLDB, SQL Server: a single-row table with id TINYINT PK and val BIGINT, seeded with (1, 0); the seed never overwrites an existing row.
DDL — all dialects 
-- PostgreSQL
CREATE SEQUENCE IF NOT EXISTS EVENT_CAPTURE_GLOBAL_POSITION START WITH 1 CACHE 1;

-- H2 / HSQLDB
CREATE TABLE IF NOT EXISTS EVENT_CAPTURE_GLOBAL_POSITION (
    id  TINYINT NOT NULL PRIMARY KEY,
    val BIGINT  NOT NULL
);
MERGE INTO EVENT_CAPTURE_GLOBAL_POSITION t
    USING (VALUES (1, 0)) AS s (id, val) ON t.id = s.id
    WHEN NOT MATCHED THEN INSERT (id, val) VALUES (s.id, s.val);

-- MySQL / MariaDB
CREATE TABLE IF NOT EXISTS EVENT_CAPTURE_GLOBAL_POSITION (
    id  TINYINT NOT NULL PRIMARY KEY,
    val BIGINT  NOT NULL
);
INSERT INTO EVENT_CAPTURE_GLOBAL_POSITION (id, val) VALUES (1, 0)
    ON DUPLICATE KEY UPDATE id = id;

-- SQL Server
IF OBJECT_ID(N'EVENT_CAPTURE_GLOBAL_POSITION', N'U') IS NULL
    CREATE TABLE EVENT_CAPTURE_GLOBAL_POSITION (
        id  TINYINT NOT NULL PRIMARY KEY,
        val BIGINT  NOT NULL
    );
INSERT INTO EVENT_CAPTURE_GLOBAL_POSITION (id, val)
    SELECT 1, 0
    WHERE NOT EXISTS (SELECT 1 FROM EVENT_CAPTURE_GLOBAL_POSITION WITH (UPDLOCK, HOLDLOCK) WHERE id = 1);

Read-model topics

Module: spring-ddd-cqrs-jdbc, included by the JDBC and the JPA CQRS starters.
Schema creation: spring.ddd.messaging.jdbc.initialize-schema (default always), decided per datasource.

While spring.ddd.messaging.capture.read-model.enabled is true (the default), every persistent projection with listeners has a read-model topic in the datasource of its read models. The @QueryProjection stores read each read model from the records table (READ_MODEL_CAPTURE_RECORDS by default), so the tables are created in their datasource too, even when no projection there has a topic. Each apply appends the read model’s state after the apply to the projection’s topic, or a tombstone carrying a deleted read model’s last state, in the transaction that writes the read model, so the two commit together. An append only inserts. Compaction deletes a read model’s superseded messages shortly after a newer message of its key commits, outside the apply, every minute by default (spring.ddd.messaging.capture.read-model.compaction.schedule); until then a consumer reading behind may meet them, applies them in order and converges on the latest. Compaction keeps its progress in the compaction table (TOPIC_COMPACTION by default), one row per topic in the topic’s datasource. A tombstone stays until tombstone retention deletes it, which it does only when spring.ddd.messaging.capture.read-model.retention.tombstones.max-age is set, the tombstone is older than that, and both compaction and every consumer of the topic have passed it. A consumer gone from the application holds it back until it is unsubscribed from the topic. On PostgreSQL every topic in a datasource draws its positions from one sequence, so appends never wait for each other, and a topic’s positions have holes where other topics' appends or rolled-back ones drew. The topics' high-water mark is one row of the high-water-mark table (HIGH_WATER_MARK_STORE by default) in the same datasource, under the name read-model-topics. A message becomes readable once that mark passes it, which takes up to two polls of spring.ddd.messaging.high-water-mark.polling.interval after its commit, so each step from an upstream projection to a downstream one waits that long. On the other databases each topic numbers its own positions, and its appends are serialized on its counter row until each commits.

READ_MODEL_CAPTURE_RECORDS

Stores one row per message, for every topic in the datasource.

Column Type Notes

id

UUID | VARCHAR(36) | CHAR(36)

Primary key, a time-ordered UUIDv7. UUID on H2 and PostgreSQL; VARCHAR(36) on HSQLDB and SQL Server; CHAR(36) with the ascii_bin collation on MySQL/MariaDB, so it compares with a query table’s message_id whatever the server’s default collation.

topic

VARCHAR(255) | NVARCHAR(255)

Not null. The projection’s name. NVARCHAR(255) with a binary collation on SQL Server.

topic_position

BIGINT

Not null. The message’s position in its topic, drawn from the datasource’s positions sequence on PostgreSQL and from the topic’s row of the positions table elsewhere. Unique per topic. Compaction deletes superseded messages, and on PostgreSQL other topics draw from the same sequence, so a topic’s positions have holes.

partition_key

SMALLINT

Not null. The partition the read-model key hashes into, stamped when the message is appended. Indexed with topic and topic_position (idx_read_model_capture_records_partition) so a consumer can read one partition of a topic in position order.

read_model_key

VARCHAR(255) | NVARCHAR(255)

Not null. The read model’s key, as the projection’s store keys it. Indexed with topic and topic_position (idx_read_model_capture_records_key) to find a read model’s messages. NVARCHAR(255) with a binary collation on SQL Server, utf8mb4_bin on MySQL/MariaDB.

read_model_type

VARCHAR(255) | NVARCHAR(255)

Not null. The read model’s type. NVARCHAR(255) on SQL Server.

read_model_revision

INT

Not null, default 0. Always 0 for now.

payload

TEXT | LONGVARCHAR | LONGTEXT | NVARCHAR(MAX)

Not null. The read model’s state after the apply, serialized with the read-model JSON mapper; a tombstone’s last state. TEXT on PostgreSQL and H2, LONGVARCHAR on HSQLDB, LONGTEXT on MySQL and MariaDB, NVARCHAR(MAX) on SQL Server.

metadata

TEXT | LONGVARCHAR | LONGTEXT | NVARCHAR(MAX)

Null when the message that caused the apply carried no metadata. Otherwise that message’s metadata, which the downstream listeners receive with the read model, serialized as JSON. Same type as payload.

cascade_depth

INT

Not null. How many applies along a chain led to the message: 1 for a message an event’s apply appended.

deleted

BOOLEAN | BIT

Not null. Whether the message is a tombstone. BIT on SQL Server.

occurred_at

TIMESTAMP | TIMESTAMPTZ | DATETIME(6) | DATETIME2

Not null. When the message was appended. TIMESTAMPTZ on PostgreSQL; DATETIME(6) on MySQL/MariaDB; DATETIME2 on SQL Server; TIMESTAMP on H2/HSQLDB. Stored as the UTC wall clock on every engine whose column carries no time zone.

READ_MODEL_CAPTURE_POSITIONS

On PostgreSQL a sequence, START WITH 1 CACHE 1, shared by every topic in the datasource; CACHE 1 is load-bearing, as for the global-position sequences. At startup a sequence behind the highest position stored or held by the topics' high-water mark, because it was recreated, is moved past it. Elsewhere a table with one row per topic, seeded at startup for every persistent projection with listeners; a seed never overwrites an existing row. There, an appending transaction increments its topic’s row and holds it locked until it commits, and a rollback restores it, so the committed value is the topic’s readable end.

Column Type Notes

topic

VARCHAR(255) | NVARCHAR(255)

Primary key. The projection’s name. NVARCHAR(255) with a binary collation on SQL Server.

val

BIGINT

Not null. The topic’s last position handed out.
DDL — all dialects 
-- PostgreSQL
CREATE SEQUENCE IF NOT EXISTS READ_MODEL_CAPTURE_POSITIONS START WITH 1 CACHE 1;

ALTER SEQUENCE IF EXISTS READ_MODEL_CAPTURE_POSITIONS CACHE 1;

CREATE TABLE IF NOT EXISTS READ_MODEL_CAPTURE_RECORDS (
    id                  UUID         PRIMARY KEY,
    topic               VARCHAR(255) NOT NULL,
    topic_position      BIGINT       NOT NULL,
    partition_key       SMALLINT     NOT NULL,
    read_model_key      VARCHAR(255) NOT NULL,
    read_model_type     VARCHAR(255) NOT NULL,
    read_model_revision INT          NOT NULL DEFAULT 0,
    payload             TEXT         NOT NULL,
    metadata            TEXT,
    cascade_depth       INT          NOT NULL,
    deleted             BOOLEAN      NOT NULL,
    occurred_at         TIMESTAMPTZ  NOT NULL,
    CONSTRAINT uq_read_model_capture_records_position UNIQUE (topic, topic_position)
);

CREATE INDEX IF NOT EXISTS idx_read_model_capture_records_partition
    ON READ_MODEL_CAPTURE_RECORDS (topic, partition_key, topic_position);

CREATE INDEX IF NOT EXISTS idx_read_model_capture_records_key
    ON READ_MODEL_CAPTURE_RECORDS (topic, read_model_key, topic_position);

-- H2
CREATE TABLE IF NOT EXISTS READ_MODEL_CAPTURE_POSITIONS (
    topic       VARCHAR(255) NOT NULL PRIMARY KEY,
    val         BIGINT       NOT NULL
);

CREATE TABLE IF NOT EXISTS READ_MODEL_CAPTURE_RECORDS (
    id                  UUID         PRIMARY KEY,
    topic               VARCHAR(255) NOT NULL,
    topic_position      BIGINT       NOT NULL,
    partition_key       SMALLINT     NOT NULL,
    read_model_key      VARCHAR(255) NOT NULL,
    read_model_type     VARCHAR(255) NOT NULL,
    read_model_revision INT          NOT NULL DEFAULT 0,
    payload             TEXT         NOT NULL,
    metadata            TEXT,
    cascade_depth       INT          NOT NULL,
    deleted             BOOLEAN      NOT NULL,
    occurred_at         TIMESTAMP    NOT NULL,
    CONSTRAINT uq_read_model_capture_records_position UNIQUE (topic, topic_position)
);

CREATE INDEX IF NOT EXISTS idx_read_model_capture_records_partition
    ON READ_MODEL_CAPTURE_RECORDS (topic, partition_key, topic_position);

CREATE INDEX IF NOT EXISTS idx_read_model_capture_records_key
    ON READ_MODEL_CAPTURE_RECORDS (topic, read_model_key, topic_position);

-- HSQLDB
CREATE TABLE IF NOT EXISTS READ_MODEL_CAPTURE_POSITIONS (
    topic       VARCHAR(255) NOT NULL PRIMARY KEY,
    val         BIGINT       NOT NULL
);

CREATE TABLE IF NOT EXISTS READ_MODEL_CAPTURE_RECORDS (
    id                  VARCHAR(36)  PRIMARY KEY,
    topic               VARCHAR(255) NOT NULL,
    topic_position      BIGINT       NOT NULL,
    partition_key       SMALLINT     NOT NULL,
    read_model_key      VARCHAR(255) NOT NULL,
    read_model_type     VARCHAR(255) NOT NULL,
    read_model_revision INT          DEFAULT 0 NOT NULL,
    payload             LONGVARCHAR  NOT NULL,
    metadata            LONGVARCHAR,
    cascade_depth       INT          NOT NULL,
    deleted             BOOLEAN      NOT NULL,
    occurred_at         TIMESTAMP    NOT NULL,
    CONSTRAINT uq_read_model_capture_records_position UNIQUE (topic, topic_position)
);

CREATE INDEX IF NOT EXISTS idx_read_model_capture_records_partition
    ON READ_MODEL_CAPTURE_RECORDS (topic, partition_key, topic_position);

CREATE INDEX IF NOT EXISTS idx_read_model_capture_records_key
    ON READ_MODEL_CAPTURE_RECORDS (topic, read_model_key, topic_position);

-- MySQL / MariaDB
CREATE TABLE IF NOT EXISTS READ_MODEL_CAPTURE_POSITIONS (
    topic       VARCHAR(255) NOT NULL PRIMARY KEY,
    val         BIGINT       NOT NULL
) DEFAULT CHARSET = utf8mb4;

CREATE TABLE IF NOT EXISTS READ_MODEL_CAPTURE_RECORDS (
    id                  CHAR(36)     CHARACTER SET ascii COLLATE ascii_bin NOT NULL PRIMARY KEY,
    topic               VARCHAR(255) NOT NULL,
    topic_position      BIGINT       NOT NULL,
    partition_key       SMALLINT     NOT NULL,
    read_model_key      VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL,
    read_model_type     VARCHAR(255) NOT NULL,
    read_model_revision INT          NOT NULL DEFAULT 0,
    payload             LONGTEXT     NOT NULL,
    metadata            LONGTEXT,
    cascade_depth       INT          NOT NULL,
    deleted             BOOLEAN      NOT NULL,
    occurred_at         DATETIME(6)  NOT NULL,
    UNIQUE KEY uq_read_model_capture_records_position (topic, topic_position),
    INDEX idx_read_model_capture_records_key (topic, read_model_key, topic_position),
    INDEX idx_read_model_capture_records_partition (topic, partition_key, topic_position)
) DEFAULT CHARSET = utf8mb4;

-- SQL Server
IF OBJECT_ID(N'READ_MODEL_CAPTURE_POSITIONS', N'U') IS NULL
    CREATE TABLE READ_MODEL_CAPTURE_POSITIONS (
        topic       NVARCHAR(255) COLLATE Latin1_General_100_BIN2 NOT NULL PRIMARY KEY,
        val         BIGINT NOT NULL
    );

IF OBJECT_ID(N'READ_MODEL_CAPTURE_RECORDS', N'U') IS NULL
    CREATE TABLE READ_MODEL_CAPTURE_RECORDS (
        id                  VARCHAR(36)   NOT NULL PRIMARY KEY,
        topic               NVARCHAR(255) COLLATE Latin1_General_100_BIN2 NOT NULL,
        topic_position      BIGINT        NOT NULL,
        partition_key       SMALLINT      NOT NULL,
        read_model_key      NVARCHAR(255) COLLATE Latin1_General_100_BIN2 NOT NULL,
        read_model_type     NVARCHAR(255) NOT NULL,
        read_model_revision INT           NOT NULL DEFAULT 0,
        payload             NVARCHAR(MAX) NOT NULL,
        metadata            NVARCHAR(MAX),
        cascade_depth       INT           NOT NULL,
        deleted             BIT           NOT NULL,
        occurred_at         DATETIME2     NOT NULL,
        CONSTRAINT uq_read_model_capture_records_position UNIQUE (topic, topic_position)
    );

IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = 'idx_read_model_capture_records_partition'
               AND object_id = OBJECT_ID(N'READ_MODEL_CAPTURE_RECORDS'))
    CREATE INDEX idx_read_model_capture_records_partition
        ON READ_MODEL_CAPTURE_RECORDS (topic, partition_key, topic_position);

IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = 'idx_read_model_capture_records_key'
               AND object_id = OBJECT_ID(N'READ_MODEL_CAPTURE_RECORDS'))
    CREATE INDEX idx_read_model_capture_records_key
        ON READ_MODEL_CAPTURE_RECORDS (topic, read_model_key, topic_position);

TOPIC_COMPACTION

How far compaction has deleted each topic’s superseded messages: one row per topic, in the datasource the topic lives in. The read-model topics create it with their own tables, under the same schema-creation setting; its script ships in spring-ddd-messaging-topic-jdbc. A topic has no row until its first compaction run records progress, and tombstone retention deletes none of its tombstones until then. It is the topic’s own progress, not a consumer’s checkpoint: unsubscribing a consumer never touches it. Test database cleanup empties it with the read-model topics' messages.

Column Type Notes

topic_name

VARCHAR(255) | NVARCHAR(255)

Primary key. The topic’s name, read-model:<projection> for a read-model topic. NVARCHAR(255) with a binary collation on SQL Server.

compacted_through

BIGINT

Not null. The position through which every superseded message of the topic is deleted. Only ever rises.
DDL — all dialects 
-- PostgreSQL / H2 / HSQLDB
CREATE TABLE IF NOT EXISTS TOPIC_COMPACTION (
    topic_name        VARCHAR(255) NOT NULL PRIMARY KEY,
    compacted_through BIGINT       NOT NULL
)

-- MySQL / MariaDB
CREATE TABLE IF NOT EXISTS TOPIC_COMPACTION (
    topic_name        VARCHAR(255) NOT NULL PRIMARY KEY,
    compacted_through BIGINT       NOT NULL
) DEFAULT CHARSET = utf8mb4

-- SQL Server
IF OBJECT_ID(N'TOPIC_COMPACTION', N'U') IS NULL
    CREATE TABLE TOPIC_COMPACTION (
        topic_name        NVARCHAR(255) COLLATE Latin1_General_100_BIN2 NOT NULL PRIMARY KEY,
        compacted_through BIGINT NOT NULL
    )

Consumers

Module: spring-ddd-messaging-consumer-jdbc.
Schema creation: spring.ddd.messaging.jdbc.initialize-schema (default always).

CONSUMERS

One row per consumer, such as a projection: whether it is running, paused by an operator, halted by the framework or being rebuilt, how often it was rebuilt, and, for a projection, the declaration its read models were derived under. Every instance running the consumer reads the same row, and every change of its state is one write of that row. The row is created when the consumer first subscribes to a topic, or when a projection first records its declaration, and removed when the consumer is unsubscribed from the last topic it has checkpoints on; a consumer without a row is running and was never rebuilt. CONSUMER_CHECKPOINTS, CONSUMER_UNDELIVERED_MESSAGES and CONSUMER_SKIPPED_MESSAGES reference the row by its id, under foreign keys: the database refuses to delete a consumer’s row while any of their rows reference it. A projection whose declaration alone is cleared takes the declaration it runs with at its next start, silently. On databases whose collation ignores case, accents or trailing spaces, consumer and type names must differ in more than that, as for CONSUMER_CHECKPOINTS.

Column Type Notes

id

CHAR(36) | VARCHAR(36)

Primary key, a UUID the framework generates. CHAR(36) with the ascii character set on MySQL/MariaDB, VARCHAR(36) on SQL Server.

consumer_type

VARCHAR(32)

Not null. The kind of consumer, e.g. projection; unique together with consumer_name.

consumer_name

VARCHAR(255)

Not null. The consumer’s name, e.g. account-summary; unique together with consumer_type.

state

VARCHAR(16)

Nullable. NULL while the consumer runs, PAUSED while an operator has paused it, HALTED while the framework has halted it, REBUILDING from the moment a rebuild begins until it has reset the consumer’s progress.

halt_reason

VARCHAR(32)

Nullable. Why the consumer is halted: MISSING_HISTORY, APPLY_ERROR or DECLARATION_CHANGED. NULL unless state is HALTED.

epoch

BIGINT

Nullable. How often the consumer was rebuilt; NULL until its first rebuild.

declaration_fingerprint

VARCHAR(255)

Nullable. For a projection whose read models are not in memory, what derives them, compared at every start: four hashes, then, after a ;, the four of the declaration the projection had before the last rebuild that changed it. A rebuild replaces it. NULL for other consumers.

state_since

TIMESTAMP

Not null. When the consumer entered its current state and halt_reason: written when the row is created, then by every change of either, by the application’s clock. DATETIME(6) on MySQL and MariaDB, DATETIME2 on SQL Server.
DDL — all dialects 
-- PostgreSQL / H2 / HSQLDB
CREATE TABLE IF NOT EXISTS CONSUMERS (
    id                       CHAR(36)     NOT NULL PRIMARY KEY,
    consumer_type            VARCHAR(32)  NOT NULL,
    consumer_name            VARCHAR(255) NOT NULL,
    state                    VARCHAR(16)  NULL,
    halt_reason              VARCHAR(32)  NULL,
    epoch                    BIGINT       NULL,
    declaration_fingerprint  VARCHAR(255) NULL,
    state_since              TIMESTAMP    NOT NULL,
    CONSTRAINT uq_consumers UNIQUE (consumer_type, consumer_name)
);

-- MySQL / MariaDB
CREATE TABLE IF NOT EXISTS CONSUMERS (
    id                       CHAR(36)     CHARACTER SET ascii NOT NULL PRIMARY KEY,
    consumer_type            VARCHAR(32)  NOT NULL,
    consumer_name            VARCHAR(255) NOT NULL,
    state                    VARCHAR(16)  NULL,
    halt_reason              VARCHAR(32)  NULL,
    epoch                    BIGINT       NULL,
    declaration_fingerprint  VARCHAR(255) NULL,
    state_since              DATETIME(6)  NOT NULL,
    UNIQUE KEY uq_consumers (consumer_type, consumer_name)
);

-- SQL Server
IF OBJECT_ID(N'CONSUMERS', N'U') IS NULL
CREATE TABLE CONSUMERS (
    id                       VARCHAR(36)  NOT NULL PRIMARY KEY,
    consumer_type            VARCHAR(32)  NOT NULL,
    consumer_name            VARCHAR(255) NOT NULL,
    state                    VARCHAR(16)  NULL,
    halt_reason              VARCHAR(32)  NULL,
    epoch                    BIGINT       NULL,
    declaration_fingerprint  VARCHAR(255) NULL,
    state_since              DATETIME2    NOT NULL,
    CONSTRAINT uq_consumers UNIQUE (consumer_type, consumer_name)
);

CONSUMER_CHECKPOINTS

How far each consumer, such as a projection, has read each partition of the topics it consumes. A consumer resumes after its checkpoint, so every position after it is delivered again after a restart. A checkpoint only moves forward, and only over positions whose applies have all completed, even when later positions have already been applied. Over positions that hold nothing for the consumer to apply, a checkpoint is saved at most once a minute, so it may trail what the consumer has read by up to a minute’s worth of positions; after a restart those are read again, and nothing is applied twice. Deleting from a topic stops at the slowest checkpoint of the consumers subscribed to it, and a checkpoint that has recorded no position yet holds the whole topic. Every such checkpoint holds that line until its consumer is unsubscribed from the topic, which removes its rows; none expires on its own. A consumer that went past history its topic no longer held carries an incomplete-history mark on every row of its read of that topic. The mark survives restarts, only ever rises, and goes only with the rows, when the consumer is unsubscribed from the topic. Whether a consumer is running, paused by an operator or halted by the framework is kept on its row in CONSUMERS. The messages a consumer could not apply yet are kept in CONSUMER_UNDELIVERED_MESSAGES, beside its checkpoints. Unsubscribing the consumer from the last topic it has checkpoints on removes its row in CONSUMERS too, which leaves it running, and its rows in PARTITION_OWNERSHIP too unless the application still runs it on another topic. Where the consumer’s writes share this table’s DataSource, a checkpoint can advance in the same transaction as the apply it records. On databases whose collation ignores case, accents or trailing spaces — MySQL, MariaDB and SQL Server by default, and HSQLDB for trailing spaces — consumer, source and type names must differ in more than that: subscribing a consumer whose names the database does not distinguish from an existing one’s, such as account-summary and Account-Summary (case) or account-summary and account-summary ` (a trailing space), fails. Subscribing a consumer whose names contain characters the column type cannot store fails as well. On SQL Server with a UTF-8 or DBCS collation, a `VARCHAR(255) column holds fewer than 255 characters when names use characters stored in more than one byte, so shorter names apply there.

Column Type Notes

consumer_id

CHAR(36) | VARCHAR(36)

Primary key, part 1. The consumer’s id in CONSUMERS, under a foreign key; its type and name live there.

source_name

VARCHAR(255)

Primary key, part 2. The name of the topic read, e.g. the event store.

partition_no

SMALLINT

Primary key, part 3. The partition a record is applied in: for a projection, the partition its read-model key hashes into, whatever partition the topic stored the record in.

position

BIGINT

Nullable. The position up to which every record of this partition has been applied; NULL until the consumer has read anything. Never moves backwards.

incomplete_through

BIGINT

Nullable. The incomplete-history mark: the highest position after which this consumer found the topic’s history resuming when it went past history the topic no longer held. Set on every partition of the consumer’s read of that topic. NULL while that read is complete as far as the framework knows.

epoch

BIGINT

Nullable. The consumer’s epoch this checkpoint was last reset or advanced under; NULL before the consumer’s first rebuild. A checkpoint advances only under an epoch at least as high as its own, so a poll that began before a rebuild cannot move a reset checkpoint back.

replay_through

BIGINT

Nullable. While a rebuild replays, how far this source is replayed; empty otherwise. The consumer reads no further until every checkpoint reached it and the read models the replay did not re-create are deleted.
DDL — all dialects 
-- PostgreSQL / H2 / HSQLDB
CREATE TABLE IF NOT EXISTS CONSUMER_CHECKPOINTS (
    consumer_id          CHAR(36)     NOT NULL,
    source_name          VARCHAR(255) NOT NULL,
    partition_no         SMALLINT     NOT NULL,
    position             BIGINT       NULL,
    incomplete_through   BIGINT       NULL,
    replay_through       BIGINT       NULL,
    epoch                BIGINT       NULL,
    PRIMARY KEY (consumer_id, source_name, partition_no),
    CONSTRAINT fk_consumer_checkpoints_consumer FOREIGN KEY (consumer_id) REFERENCES CONSUMERS (id)
);

-- MySQL / MariaDB
CREATE TABLE IF NOT EXISTS CONSUMER_CHECKPOINTS (
    consumer_id          CHAR(36)     CHARACTER SET ascii NOT NULL,
    source_name          VARCHAR(255) NOT NULL,
    partition_no         SMALLINT     NOT NULL,
    position             BIGINT       NULL,
    incomplete_through   BIGINT       NULL,
    replay_through       BIGINT       NULL,
    epoch                BIGINT       NULL,
    PRIMARY KEY (consumer_id, source_name, partition_no),
    CONSTRAINT fk_consumer_checkpoints_consumer FOREIGN KEY (consumer_id) REFERENCES CONSUMERS (id)
);

-- SQL Server
IF OBJECT_ID(N'CONSUMER_CHECKPOINTS', N'U') IS NULL
CREATE TABLE CONSUMER_CHECKPOINTS (
    consumer_id          VARCHAR(36)  NOT NULL,
    source_name          VARCHAR(255) NOT NULL,
    partition_no         SMALLINT     NOT NULL,
    position             BIGINT       NULL,
    incomplete_through   BIGINT       NULL,
    replay_through       BIGINT       NULL,
    epoch                BIGINT       NULL,
    PRIMARY KEY (consumer_id, source_name, partition_no),
    CONSTRAINT fk_consumer_checkpoints_consumer FOREIGN KEY (consumer_id) REFERENCES CONSUMERS (id)
);

CONSUMER_UNDELIVERED_MESSAGES

The messages a consumer could not apply yet, one row per message and key: for a projection, the read model the message failed for; for a route consumer, the route key. Each row keeps a copy of its message, which is what a retry applies, so retention and compaction delete the message from its topic without waiting for it. The copy costs storage per row: a message held for several keys is copied once per key, and so is every message of a key held behind a dead letter, every later message of a DEAD_LETTER_KEY key included. A consumer without undelivered messages has no rows here, and a message below the consumer’s checkpoint without a row is done. A failing message is recorded as FAILED and retried on later polls under its consumer’s error handler’s retry policy, spring.ddd.messaging.consumer.delivery-retry by default; the count and the next retry are kept here, so the budget survives restarts and moves to another instance. Later messages of the same key are recorded as WAITING behind it and applied in position order once it applies; the consumer’s other keys keep flowing. A message whose budget is spent is a dead letter, its status naming the outcome its error handler decided: HALTED halted the consumer, and resuming the consumer retries it with a fresh budget while resuming it past the poison message deletes it; DEAD_LETTER_KEY and DEAD_LETTER wait for an operator. Once a key’s message dies as DEAD_LETTER_KEY, the key’s later messages are recorded as DEAD_LETTER_KEY too, behind it, with a failure_count of 0, and are applied in order once an operator retries or deletes it. A rebuild deletes the consumer’s rows, and unsubscribing a consumer from a topic deletes that topic’s rows.

Column Type Notes

id

CHAR(36) | VARCHAR(36)

Primary key, a UUID. CHAR(36) with the ascii character set on MySQL/MariaDB, VARCHAR(36) on SQL Server.

consumer_id

CHAR(36) | VARCHAR(36)

Not null. The consumer’s id in CONSUMERS, under a foreign key, as in CONSUMER_CHECKPOINTS.

source_name

VARCHAR(255)

Not null. The topic the message lies in.

resolved_key

VARCHAR(255) | NVARCHAR(255)

Not null. The key the message waits for, e.g. an account id; empty for a message without a key, for one whose key could not be read, and for a route key longer than 255 characters. Compares exactly: utf8mb4_bin on MySQL/MariaDB, NVARCHAR(255) with a binary collation on SQL Server.

key_hash

BIGINT

Not null. A 64-bit hash of resolved_key, for the unique constraint only.

message_position

BIGINT

Not null. The message’s position in its topic. Unique per consumer, source, partition and key (uq_consumer_undelivered_messages, with partition_no and key_hash).

partition_no

SMALLINT

Not null. The partition the key is applied in, as in CONSUMER_CHECKPOINTS.

status

VARCHAR(16)

Not null. FAILED (retried at next_retry_at), WAITING (behind an earlier message of its key), or a dead letter, its budget spent: HALTED (it halted the consumer and holds its key; a resume or a start retries it), DEAD_LETTER_KEY (a key’s first dead letter, holding the key’s later messages dead behind it, or with failure_count 0 and no failure_reason, such a later message; waits for an operator) or DEAD_LETTER (holds nothing; waits for an operator).

failure_count

INT

Not null. How often the message failed; 0 while it waits, after a resume, and for a later message of a DEAD_LETTER_KEY key, which is not tried.

next_retry_at

TIMESTAMP | DATETIME(6) | DATETIME2

Nullable. When a FAILED message is tried again, in UTC; empty for at once, and for every other status.

failure_reason

VARCHAR(1000) | NVARCHAR(1000)

Nullable. The last failure’s class and message, cut to 1,000 characters; NULL for a message that has not failed.

recorded_at

TIMESTAMP | DATETIME(6) | DATETIME2

Not null. When the message was recorded as undelivered, in UTC.

unresolved

BOOLEAN | BIT

Not null. Whether the message’s keys could not be told when it was recorded: it is recorded in every partition it was read in, and nothing after it there is applied before it while it is retried, nor once it is dead, unless its status is DEAD_LETTER.

message_copy

CLOB | TEXT | LONGTEXT | NVARCHAR(MAX)

Nullable. The message as its topic stored it, which a retry applies: an event as stored, so an upcaster deployed since applies to it, and a read model’s message as published. TEXT on PostgreSQL, CLOB on H2 and HSQLDB, LONGTEXT (utf8mb4) on MySQL/MariaDB. Empty when the topic no longer held the message as the row was written; its retry then applies nothing.
DDL — all dialects 
-- PostgreSQL (TEXT) / H2, HSQLDB (CLOB)
CREATE TABLE IF NOT EXISTS CONSUMER_UNDELIVERED_MESSAGES (
    id               CHAR(36)      NOT NULL PRIMARY KEY,
    consumer_id      CHAR(36)      NOT NULL,
    source_name      VARCHAR(255)  NOT NULL,
    resolved_key     VARCHAR(255)  NOT NULL,
    key_hash         BIGINT        NOT NULL,
    message_position BIGINT        NOT NULL,
    partition_no     SMALLINT      NOT NULL,
    status           VARCHAR(16)   NOT NULL,
    failure_count    INT           NOT NULL,
    next_retry_at    TIMESTAMP     NULL,
    failure_reason   VARCHAR(1000) NULL,
    recorded_at      TIMESTAMP     NOT NULL,
    unresolved       BOOLEAN       NOT NULL,
    message_copy     TEXT          NULL,
    CONSTRAINT uq_consumer_undelivered_messages
        UNIQUE (consumer_id, source_name, message_position, partition_no, key_hash),
    CONSTRAINT fk_consumer_undelivered_messages_consumer FOREIGN KEY (consumer_id) REFERENCES CONSUMERS (id)
);

-- MySQL / MariaDB
CREATE TABLE IF NOT EXISTS CONSUMER_UNDELIVERED_MESSAGES (
    id               CHAR(36)      CHARACTER SET ascii NOT NULL PRIMARY KEY,
    consumer_id      CHAR(36)      CHARACTER SET ascii NOT NULL,
    source_name      VARCHAR(255)  NOT NULL,
    resolved_key     VARCHAR(255)  CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL,
    key_hash         BIGINT        NOT NULL,
    message_position BIGINT        NOT NULL,
    partition_no     SMALLINT      NOT NULL,
    status           VARCHAR(16)   NOT NULL,
    failure_count    INT           NOT NULL,
    next_retry_at    DATETIME(6)   NULL,
    failure_reason   VARCHAR(1000) NULL,
    recorded_at      DATETIME(6)   NOT NULL,
    unresolved       BOOLEAN       NOT NULL,
    message_copy     LONGTEXT      CHARACTER SET utf8mb4 NULL,
    UNIQUE KEY uq_consumer_undelivered_messages
        (consumer_id, source_name, message_position, partition_no, key_hash),
    CONSTRAINT fk_consumer_undelivered_messages_consumer FOREIGN KEY (consumer_id) REFERENCES CONSUMERS (id)
);

-- SQL Server
IF OBJECT_ID(N'CONSUMER_UNDELIVERED_MESSAGES', N'U') IS NULL
CREATE TABLE CONSUMER_UNDELIVERED_MESSAGES (
    id               VARCHAR(36)    NOT NULL PRIMARY KEY,
    consumer_id      VARCHAR(36)    NOT NULL,
    source_name      VARCHAR(255)   NOT NULL,
    resolved_key     NVARCHAR(255)  COLLATE Latin1_General_100_BIN2 NOT NULL,
    key_hash         BIGINT         NOT NULL,
    message_position BIGINT         NOT NULL,
    partition_no     SMALLINT       NOT NULL,
    status           VARCHAR(16)    NOT NULL,
    failure_count    INT            NOT NULL,
    next_retry_at    DATETIME2      NULL,
    failure_reason   NVARCHAR(1000) NULL,
    recorded_at      DATETIME2      NOT NULL,
    unresolved       BIT            NOT NULL,
    message_copy     NVARCHAR(MAX)  NULL,
    CONSTRAINT uq_consumer_undelivered_messages
        UNIQUE (consumer_id, source_name, message_position, partition_no, key_hash),
    CONSTRAINT fk_consumer_undelivered_messages_consumer FOREIGN KEY (consumer_id) REFERENCES CONSUMERS (id)
);

CONSUMER_SKIPPED_MESSAGES

The messages a consumer skipped, so that every instance passes over them: one an operator deleted, one the consumer was resumed past as a poison message, and one its error handler decided to skip. A skip is kept for the key it was made for, or for every key of a message whose keys could not be told. A partition that meets a skipped message later, on any instance, passes over it without recording it, spending a budget on it or halting. A consumer that skipped nothing has no rows here; every poll reads the consumer’s rows with one probe of idx_consumer_skipped_messages_position. A row is deleted once every partition that can still meet the message has its checkpoint at or past it and no undelivered message lies at its position; a partition still held back by a message whose keys could not be told, or one whose instance is down, keeps it. A rebuild deletes the consumer’s rows, and unsubscribing a consumer from a topic deletes that topic’s rows.

Column Type Notes

id

CHAR(36) | VARCHAR(36)

Primary key, a UUID derived from the consumer, the topic, the partition, the position and the key, so a skip recorded twice is one row. CHAR(36) with the ascii character set on MySQL/MariaDB, VARCHAR(36) on SQL Server.

consumer_id

CHAR(36) | VARCHAR(36)

Not null. The consumer’s id in CONSUMERS, under a foreign key, as in CONSUMER_CHECKPOINTS.

source_name

VARCHAR(255)

Not null. The topic the message lies in.

partition_no

SMALLINT

Not null. The partition the message was skipped in. On a topic with one position space, such as the event store, a skip holds in every partition.

message_position

BIGINT

Not null. The message’s position in its topic. Indexed with consumer_id and source_name (idx_consumer_skipped_messages_position).

resolved_key

VARCHAR(255) | NVARCHAR(255)

Not null. The key the message was skipped for, as in CONSUMER_UNDELIVERED_MESSAGES; empty for a message without a key. Compares exactly: utf8mb4_bin on MySQL/MariaDB, NVARCHAR(255) with a binary collation on SQL Server.

every_key

BOOLEAN | BIT

Not null. Whether the message was skipped for every key: its keys could not be told, so an instance that can tell them later applies none of them.
DDL — all dialects 
-- PostgreSQL / H2 / HSQLDB
CREATE TABLE IF NOT EXISTS CONSUMER_SKIPPED_MESSAGES (
    id               CHAR(36)     NOT NULL PRIMARY KEY,
    consumer_id      CHAR(36)     NOT NULL,
    source_name      VARCHAR(255) NOT NULL,
    partition_no     SMALLINT     NOT NULL,
    message_position BIGINT       NOT NULL,
    resolved_key     VARCHAR(255) NOT NULL,
    every_key        BOOLEAN      NOT NULL,
    CONSTRAINT fk_consumer_skipped_messages_consumer FOREIGN KEY (consumer_id) REFERENCES CONSUMERS (id)
);

CREATE INDEX IF NOT EXISTS idx_consumer_skipped_messages_position
    ON CONSUMER_SKIPPED_MESSAGES (consumer_id, source_name, message_position);

-- MySQL / MariaDB
CREATE TABLE IF NOT EXISTS CONSUMER_SKIPPED_MESSAGES (
    id               CHAR(36)     CHARACTER SET ascii NOT NULL PRIMARY KEY,
    consumer_id      CHAR(36)     CHARACTER SET ascii NOT NULL,
    source_name      VARCHAR(255) NOT NULL,
    partition_no     SMALLINT     NOT NULL,
    message_position BIGINT       NOT NULL,
    resolved_key     VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL,
    every_key        BOOLEAN      NOT NULL,
    INDEX idx_consumer_skipped_messages_position (consumer_id, source_name, message_position),
    CONSTRAINT fk_consumer_skipped_messages_consumer FOREIGN KEY (consumer_id) REFERENCES CONSUMERS (id)
);

-- SQL Server
IF OBJECT_ID(N'CONSUMER_SKIPPED_MESSAGES', N'U') IS NULL
CREATE TABLE CONSUMER_SKIPPED_MESSAGES (
    id               VARCHAR(36)   NOT NULL PRIMARY KEY,
    consumer_id      VARCHAR(36)   NOT NULL,
    source_name      VARCHAR(255)  NOT NULL,
    partition_no     SMALLINT      NOT NULL,
    message_position BIGINT        NOT NULL,
    resolved_key     NVARCHAR(255) COLLATE Latin1_General_100_BIN2 NOT NULL,
    every_key        BIT           NOT NULL,
    CONSTRAINT fk_consumer_skipped_messages_consumer FOREIGN KEY (consumer_id) REFERENCES CONSUMERS (id)
);

IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = 'idx_consumer_skipped_messages_position'
               AND object_id = OBJECT_ID(N'CONSUMER_SKIPPED_MESSAGES'))
    CREATE INDEX idx_consumer_skipped_messages_position
        ON CONSUMER_SKIPPED_MESSAGES (consumer_id, source_name, message_position);

Rebuild epochs

Module: spring-ddd-cqrs-jdbc.
Schema creation: spring.ddd.cqrs.jdbc.initialize-schema (default always).

READ_MODEL_EPOCHS

Created in every datasource holding a persistent projection’s read models, whether or not spring.ddd.messaging.capture.read-model.enabled is on. A row is written with each read model a projection creates, in the same transaction, and removed with it: it names the rebuild whose replay created the read model, or 0 for one created outside a rebuild. A rebuild counts a read model as re-created once its row carries the rebuild’s count; once its replay is complete it deletes the read models whose rows carry a lower one, unless the replay met missing history. A read model written past the projection, so without a row, is never deleted by a rebuild.

Column Type Notes

projection

VARCHAR(255) | NVARCHAR(255)

Primary key, with read_model_key. The projection’s name. NVARCHAR(255) with a binary collation on SQL Server, utf8mb4_bin on MySQL/MariaDB.

read_model_key

VARCHAR(255) | NVARCHAR(255)

Primary key, with projection. The read model’s key, as the projection’s store keys it. Collated as projection, so keys that differ only in case are two read models. The primary key is nonclustered on SQL Server.

epoch

BIGINT

Not null. The count of the rebuild whose replay created the read model, 0 when it was created outside a rebuild.
DDL — all dialects 
-- PostgreSQL, H2, HSQLDB
CREATE TABLE IF NOT EXISTS READ_MODEL_EPOCHS (
    projection     VARCHAR(255) NOT NULL,
    read_model_key VARCHAR(255) NOT NULL,
    epoch          BIGINT       NOT NULL,
    CONSTRAINT pk_read_model_epochs PRIMARY KEY (projection, read_model_key)
);

-- MySQL / MariaDB
CREATE TABLE IF NOT EXISTS READ_MODEL_EPOCHS (
    projection     VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL,
    read_model_key VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL,
    epoch          BIGINT       NOT NULL,
    PRIMARY KEY (projection, read_model_key)
) DEFAULT CHARSET = utf8mb4;

-- SQL Server
IF OBJECT_ID(N'READ_MODEL_EPOCHS', N'U') IS NULL
    CREATE TABLE READ_MODEL_EPOCHS (
        projection     NVARCHAR(255) COLLATE Latin1_General_100_BIN2 NOT NULL,
        read_model_key NVARCHAR(255) COLLATE Latin1_General_100_BIN2 NOT NULL,
        epoch          BIGINT        NOT NULL,
        CONSTRAINT pk_read_model_epochs PRIMARY KEY NONCLUSTERED (projection, read_model_key)
    );

Read models

Module: spring-ddd-cqrs-jdbc-query.
Schema creation: spring.ddd.cqrs.jdbc.initialize-schema (default always).

Query table

Each @QueryProjection owns one table, which no other projection shares. It is named after the projection unless table names it: every character of the name other than an ASCII letter, a digit or becomes , so account-summary is stored in account_summary. Table and index names are at most 63 characters long. A row holds one read model: its key, the id of its latest message in the projection’s read-model topic, and a copy of each @QueryField value in a column of its own, indexed as idx_<table>_<column>. The read model itself is the state that message carries in the read-model records table; every read looks it up there by the message’s id. Keys compare exactly, so keys that differ only in case are two read models; keys that differ only in trailing spaces are one on MySQL, MariaDB, SQL Server and HSQLDB. An existing table is never altered, but it is checked at startup against the projection’s declaration, whether the framework or the application created it. The startup fails, naming the table, the column, both shapes and the remedy, when:

  • the table is missing and spring.ddd.cqrs.jdbc.initialize-schema did not create it;

  • it lacks read_model_id or message_id, so it is not a query table: set a distinct table;

  • its message_id cannot hold a message id as the records table’s id does — a UUID on PostgreSQL and H2, a character type of at least 36 characters elsewhere, not NVARCHAR on SQL Server;

  • a @QueryField column is missing, or cannot hold every value of its kind;

  • a @QueryField(unique = true) column has no unique index of its own, or a column not declared unique has one.

For the last three, drop the table and delete the projection’s rows from CONSUMER_SKIPPED_MESSAGES, CONSUMER_UNDELIVERED_MESSAGES, CONSUMER_CHECKPOINTS and CONSUMERS, in that order: the table is created again in the declared shape — by spring.ddd.cqrs.jdbc.initialize-schema, or by your own DDL where that creates nothing — and the projection fills it from the start of its topics. ProjectionRebuild cannot do this: the application does not start, and a rebuild keeps the table. Read models the refill does not re-create stay on the read-model topic, so a projection that reads it keeps them.

A column passes when it holds every value the store writes, so an application’s own DDL may choose a wider type:

Kind Passes

TEXT

any character type of at least 255 characters, e.g. VARCHAR(500) or TEXT

NUMBER, integral

BIGINT

NUMBER, decimal

DECIMAL or NUMERIC with at least as many integer digits and as much scale as declared, or PostgreSQL’s unconstrained NUMERIC; e.g. DECIMAL(24,4) for DECIMAL(19,2)

TEMPORAL

DATE for a date; TIME, or TIMESTAMP (DATETIME on MySQL and MariaDB, DATETIME2 on SQL Server), with at least 6 fractional digits for a time or an instant

BOOLEAN

BOOLEAN, BIT, or TINYINT on MySQL and MariaDB

Only the connection’s current schema is looked in. A unique index with INCLUDE columns counts as a multi-column index, so its column counts as having no unique index of its own.

Columns the projection does not declare are ignored, and a plain index is not required.

Column Type Notes

read_model_id

VARCHAR(255)

Primary key. The read model’s key in its canonical string form, e.g. A-1. Binary collation on MySQL, MariaDB and SQL Server; NVARCHAR(255) on SQL Server.

message_id

UUID | CHAR(36) | VARCHAR(36)

Not null. The id of the read model’s latest message in the read-model records table, whose state is the read model as JSON; a property added with a default, or removed, needs no migration. Every save points it at the new message, so a save or delete of a read model that changed since it was read conflicts and is retried. Same type as the read-model records table’s id: UUID on PostgreSQL and H2, CHAR(36) with the ascii_bin collation on MySQL and MariaDB, VARCHAR(36) on HSQLDB and SQL Server (not NVARCHAR, which would make each lookup scan).

one per @QueryField

by kind

Nullable. The property’s value, copied on every save. TEXT: VARCHAR(255) (NVARCHAR(255) on SQL Server). NUMBER: BIGINT for integral types, DECIMAL(38,0) for BigInteger, DECIMAL(precision,scale) for BigDecimal. TEMPORAL: DATE, TIME(6) or TIMESTAMP(6) in UTC (DATETIME(6) on MySQL and MariaDB, DATETIME2(6) on SQL Server). BOOLEAN: BOOLEAN (BIT on SQL Server). unique = true makes the index unique; read models without a value do not count against it.
DDL — all dialects, for a projection account-summary with @QueryField(unique = true) val iban: String and @QueryField(type = QueryFieldType.NUMBER) val balance: BigDecimal 
-- PostgreSQL / H2 (HSQLDB: message_id VARCHAR(36))
CREATE TABLE IF NOT EXISTS account_summary (
    read_model_id   VARCHAR(255) NOT NULL,
    message_id      UUID         NOT NULL,
    iban            VARCHAR(255),
    balance         DECIMAL(38,10),
    PRIMARY KEY (read_model_id)
);
CREATE UNIQUE INDEX idx_account_summary_iban ON account_summary (iban);
CREATE INDEX idx_account_summary_balance ON account_summary (balance);

-- MySQL / MariaDB
CREATE TABLE IF NOT EXISTS account_summary (
    read_model_id   VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL,
    message_id      CHAR(36)     CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
    iban            VARCHAR(255),
    balance         DECIMAL(38,10),
    PRIMARY KEY (read_model_id)
) DEFAULT CHARSET = utf8mb4;
CREATE UNIQUE INDEX idx_account_summary_iban ON account_summary (iban);
CREATE INDEX idx_account_summary_balance ON account_summary (balance);

-- SQL Server
IF OBJECT_ID(N'account_summary', N'U') IS NULL
    CREATE TABLE account_summary (
        read_model_id   NVARCHAR(255) COLLATE Latin1_General_100_BIN2 NOT NULL,
        message_id      VARCHAR(36)   NOT NULL,
        iban            NVARCHAR(255),
        balance         DECIMAL(38,10),
        PRIMARY KEY (read_model_id)
    );
CREATE UNIQUE INDEX idx_account_summary_iban ON account_summary (iban) WHERE iban IS NOT NULL;
CREATE INDEX idx_account_summary_balance ON account_summary (balance);

Sagas

Module: spring-ddd-saga-jdbc.
Schema creation: spring.ddd.saga.jdbc.initialize-schema (default always).

SAGA_INSTANCES

Stores the serialised state of each saga instance. The table name defaults to SAGA_INSTANCES (spring.ddd.saga.jdbc.table-names.saga-instances), or is set per saga with @SagaTable on the saga class.

Column Type Notes

id

UUID | VARCHAR(36) | CHAR(36)

Primary key, a time-ordered UUIDv7. UUID on H2 and PostgreSQL; VARCHAR(36) on HSQLDB and SQL Server; CHAR(36) on MySQL/MariaDB.

saga_type

VARCHAR(255)

Not null. Fully-qualified saga class name. Unique together with saga_id.

saga_id

VARCHAR(255)

Not null. Saga identity value. Unique together with saga_type.

state_json

TEXT | LONGVARCHAR | LONGTEXT | NVARCHAR(MAX)

Not null. Serialised saga state. TEXT on H2 and PostgreSQL; LONGVARCHAR on HSQLDB; LONGTEXT on MySQL/MariaDB; NVARCHAR(MAX) on SQL Server.

version

BIGINT

Not null. Optimistic-lock version. A new instance starts at a random version, so an instance started again under the id of a deleted one never repeats a version the earlier one held.

completed_at

TIMESTAMP | DATETIME(6) | DATETIME2

Nullable. The completion timestamp; null while the saga is active. DATETIME(6) on MySQL/MariaDB; DATETIME2 on SQL Server; TIMESTAMP elsewhere. Drives retention.

Override: spring.ddd.saga.jdbc.table-names.saga-instances, or @SagaTable on the saga class.

DDL — all dialects 
-- PostgreSQL / H2
CREATE TABLE IF NOT EXISTS SAGA_INSTANCES (
    id          UUID         NOT NULL PRIMARY KEY,
    saga_type   VARCHAR(255) NOT NULL,
    saga_id     VARCHAR(255) NOT NULL,
    state_json  TEXT         NOT NULL,
    version     BIGINT       NOT NULL,
    completed_at TIMESTAMP,
    UNIQUE (saga_type, saga_id)
)

-- HSQLDB
CREATE TABLE IF NOT EXISTS SAGA_INSTANCES (
    id          VARCHAR(36)  NOT NULL PRIMARY KEY,
    saga_type   VARCHAR(255) NOT NULL,
    saga_id     VARCHAR(255) NOT NULL,
    state_json  LONGVARCHAR  NOT NULL,
    version     BIGINT       NOT NULL,
    completed_at TIMESTAMP,
    UNIQUE (saga_type, saga_id)
)

-- MySQL
CREATE TABLE IF NOT EXISTS SAGA_INSTANCES (
    id          CHAR(36)     NOT NULL PRIMARY KEY,
    saga_type   VARCHAR(255) NOT NULL,
    saga_id     VARCHAR(255) NOT NULL,
    state_json  LONGTEXT     NOT NULL,
    version     BIGINT       NOT NULL,
    completed_at DATETIME(6),
    UNIQUE (saga_type, saga_id)
)

-- MariaDB — same DDL as MySQL above

-- SQL Server
IF NOT EXISTS (SELECT * FROM sys.tables WHERE name = 'SAGA_INSTANCES')
CREATE TABLE SAGA_INSTANCES (
    id          VARCHAR(36)   NOT NULL PRIMARY KEY,
    saga_type   VARCHAR(255)  NOT NULL,
    saga_id     VARCHAR(255)  NOT NULL,
    state_json  NVARCHAR(MAX) NOT NULL,
    version     BIGINT        NOT NULL,
    completed_at DATETIME2,
    UNIQUE (saga_type, saga_id)
)

SAGA_DEADLINES

Stores pending saga deadlines (scheduled timeouts). The table name defaults to SAGA_DEADLINES; its index is idx_<table>_due_at.

Column Type Notes

id

UUID | VARCHAR(36) | CHAR(36)

Primary key, a time-ordered UUIDv7. UUID on H2 and PostgreSQL; VARCHAR(36) on HSQLDB and SQL Server; CHAR(36) on MySQL/MariaDB.

saga_type

VARCHAR(255)

Not null.

saga_id

VARCHAR(255)

Not null.

name

VARCHAR(255)

Not null. Deadline name within the saga. Unique together with saga_type and saga_id.

due_at

TIMESTAMP | DATETIME(6) | DATETIME2

Not null. When the deadline fires. Indexed for efficient polling. DATETIME(6) on MySQL/MariaDB; DATETIME2 on SQL Server; TIMESTAMP elsewhere.

payload_json

TEXT | LONGVARCHAR | LONGTEXT | NVARCHAR(MAX)

Nullable. Optional payload attached to the deadline.

metadata_json

TEXT | LONGVARCHAR | LONGTEXT | NVARCHAR(MAX)

Nullable. Saga metadata snapshot at deadline scheduling time.

Override: spring.ddd.saga.jdbc.table-names.saga-deadlines.

DDL — all dialects 
-- PostgreSQL / H2
CREATE TABLE IF NOT EXISTS SAGA_DEADLINES (
    id            UUID         NOT NULL PRIMARY KEY,
    saga_type     VARCHAR(255) NOT NULL,
    saga_id       VARCHAR(255) NOT NULL,
    name          VARCHAR(255) NOT NULL,
    due_at        TIMESTAMP    NOT NULL,
    payload_json  TEXT,
    metadata_json TEXT,
    UNIQUE (saga_type, saga_id, name)
);

CREATE INDEX IF NOT EXISTS idx_SAGA_DEADLINES_due_at
    ON SAGA_DEADLINES (due_at);

-- HSQLDB
CREATE TABLE IF NOT EXISTS SAGA_DEADLINES (
    id            VARCHAR(36)  NOT NULL PRIMARY KEY,
    saga_type     VARCHAR(255) NOT NULL,
    saga_id       VARCHAR(255) NOT NULL,
    name          VARCHAR(255) NOT NULL,
    due_at        TIMESTAMP    NOT NULL,
    payload_json  LONGVARCHAR,
    metadata_json LONGVARCHAR,
    UNIQUE (saga_type, saga_id, name)
);

CREATE INDEX IF NOT EXISTS idx_SAGA_DEADLINES_due_at
    ON SAGA_DEADLINES (due_at);

-- MySQL
CREATE TABLE IF NOT EXISTS SAGA_DEADLINES (
    id            CHAR(36)     NOT NULL PRIMARY KEY,
    saga_type     VARCHAR(255) NOT NULL,
    saga_id       VARCHAR(255) NOT NULL,
    name          VARCHAR(255) NOT NULL,
    due_at        DATETIME(6)  NOT NULL,
    payload_json  LONGTEXT,
    metadata_json LONGTEXT,
    UNIQUE (saga_type, saga_id, name),
    INDEX idx_SAGA_DEADLINES_due_at (due_at)
)

-- MariaDB — same DDL as MySQL above

-- SQL Server
IF NOT EXISTS (SELECT * FROM sys.tables WHERE name = 'SAGA_DEADLINES')
CREATE TABLE SAGA_DEADLINES (
    id            VARCHAR(36)   NOT NULL PRIMARY KEY,
    saga_type     VARCHAR(255)  NOT NULL,
    saga_id       VARCHAR(255)  NOT NULL,
    name          VARCHAR(255)  NOT NULL,
    due_at        DATETIME2     NOT NULL,
    payload_json  NVARCHAR(MAX),
    metadata_json NVARCHAR(MAX),
    UNIQUE (saga_type, saga_id, name)
);

IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = 'idx_SAGA_DEADLINES_due_at'
               AND object_id = OBJECT_ID(N'SAGA_DEADLINES'))
    CREATE INDEX idx_SAGA_DEADLINES_due_at
        ON SAGA_DEADLINES (due_at);

SAGA_DEADLINE_LOCKS

Distributed lock table that ensures only one node polls for expired deadlines at a time, under the lock name saga-deadline-delivery. It is created with the deadline table unless the application supplies its own deadline lease. The table name defaults to SAGA_DEADLINE_LOCKS.

Column Type Notes

lock_name

VARCHAR(255)

Primary key. Name of the lock.

owner

VARCHAR(255)

Not null. Node identifier holding the lock.

expires_at

TIMESTAMP | DATETIME(6) | DATETIME2

Not null. Lock expiry. DATETIME(6) on MySQL/MariaDB; DATETIME2 on SQL Server; TIMESTAMP elsewhere.

Override: spring.ddd.saga.jdbc.table-names.saga-deadline-locks.

DDL — all dialects 
-- PostgreSQL / H2 / HSQLDB
CREATE TABLE IF NOT EXISTS SAGA_DEADLINE_LOCKS (
    lock_name  VARCHAR(255) NOT NULL,
    owner      VARCHAR(255) NOT NULL,
    expires_at TIMESTAMP    NOT NULL,
    PRIMARY KEY (lock_name)
)

-- MySQL
CREATE TABLE IF NOT EXISTS SAGA_DEADLINE_LOCKS (
    lock_name  VARCHAR(255) NOT NULL,
    owner      VARCHAR(255) NOT NULL,
    expires_at DATETIME(6)  NOT NULL,
    PRIMARY KEY (lock_name)
)

-- MariaDB — same DDL as MySQL above

-- SQL Server
IF NOT EXISTS (SELECT * FROM sys.tables WHERE name = 'SAGA_DEADLINE_LOCKS')
CREATE TABLE SAGA_DEADLINE_LOCKS (
    lock_name  VARCHAR(255) NOT NULL,
    owner      VARCHAR(255) NOT NULL,
    expires_at DATETIME2    NOT NULL,
    PRIMARY KEY (lock_name)
)

High-water mark and partitioning

Module: spring-ddd-messaging-topic-jdbc (HIGH_WATER_MARK_STORE, PARTITION_METADATA) and spring-ddd-messaging-consumer-jdbc (PARTITION_INSTANCES, PARTITION_OWNERSHIP, PARTITION_MEMBERS).
Schema creation: HIGH_WATER_MARK_STORE by spring.ddd.messaging.jdbc.initialize-schema; PARTITION_METADATA by the same switch while event capture (spring.ddd.messaging.capture.event.enabled) is on and, where an event store uses it, spring.ddd.eventsourcing.jdbc.initialize-schema (each default always).

HIGH_WATER_MARK_STORE

One row per topic, holding that topic’s high-water mark: the gapless, commit-safe end up to which the topic is read. The event store is one such topic; every source the framework advances a mark for has a row of its own, with its own lease. On PostgreSQL the read-model topics of a datasource share one row, read-model-topics, in that datasource’s own HIGH_WATER_MARK_STORE. On MySQL, MariaDB, H2, HSQLDB, and SQL Server the mark follows the committed event-store global-position counter, which only ever holds committed positions. On PostgreSQL the mark first records the sequence’s last handed-out value as a pending end position, together with a transaction id assigned right after reading it, and moves to that end position once every transaction with a lower id has ended. Every append took its transaction id before its position, so a position appended through the event store is passed only once its transaction has committed or rolled back. One instance at a time advances the mark under a lease whose expiry is computed on the database clock. A topic’s row is seeded at 0 when missing. The same row records the topic’s start once messages have been deleted; only the capture topic has retention, and the event store’s row keeps a start of 0 for good.

Column Type Notes

topic_name

VARCHAR(255)

Primary key. The topic’s name, the same identity a consumer records its checkpoint under.

high_water_mark

BIGINT

Not null. The highest position up to which every position is committed or provably abandoned. Never moves backwards.

pending_end

BIGINT

Nullable. PostgreSQL only: the highest position handed out when last read, waiting for the transactions that may still hold positions up to it to end; NULL when nothing is pending.

pending_horizon

BIGINT

Nullable. PostgreSQL only: the transaction id assigned right after pending_end was read; pending_end becomes the mark once no transaction with a lower id is in flight. NULL when nothing is pending. Transaction ids are local to a database cluster: after the database moves to another cluster, for example by dump and restore or logical replication, a horizon that is not below a freshly assigned id is discarded together with pending_end, and the end position is recorded afresh.

topic_start

BIGINT

Not null, default 0. The position the topic is retained after: every message at or below it has been deleted, and 0 means nothing has. A consumer whose checkpoint is at or above it has lost nothing. Only ever moves forward. Written only for the capture topic; the event store is the aggregates' source of truth and has no retention.

lease_owner

VARCHAR(255)

Nullable. The instance currently allowed to advance the mark; NULL when the lease is free.

lease_expires_at

TIMESTAMP WITH TIME ZONE | DATETIME(6) | DATETIME2

Nullable. Database-clock expiry of the lease. TIMESTAMP WITH TIME ZONE on PostgreSQL, H2, and HSQLDB; DATETIME(6) in UTC on MySQL/MariaDB; DATETIME2 on SQL Server.
DDL — all dialects 
-- PostgreSQL / H2 / HSQLDB
CREATE TABLE IF NOT EXISTS HIGH_WATER_MARK_STORE (
    topic_name          VARCHAR(255)             NOT NULL,
    high_water_mark   BIGINT                   NOT NULL,
    pending_end       BIGINT                   NULL,
    pending_horizon   BIGINT                   NULL,
    topic_start         BIGINT                   DEFAULT 0 NOT NULL,
    lease_owner       VARCHAR(255)             NULL,
    lease_expires_at  TIMESTAMP WITH TIME ZONE NULL,
    PRIMARY KEY (topic_name)
);

-- MySQL / MariaDB
CREATE TABLE IF NOT EXISTS HIGH_WATER_MARK_STORE (
    topic_name          VARCHAR(255) NOT NULL,
    high_water_mark   BIGINT       NOT NULL,
    pending_end       BIGINT       NULL,
    pending_horizon   BIGINT       NULL,
    topic_start         BIGINT       NOT NULL DEFAULT 0,
    lease_owner       VARCHAR(255) NULL,
    lease_expires_at  DATETIME(6)  NULL,
    PRIMARY KEY (topic_name)
);

-- SQL Server
IF OBJECT_ID(N'HIGH_WATER_MARK_STORE', N'U') IS NULL
CREATE TABLE HIGH_WATER_MARK_STORE (
    topic_name          VARCHAR(255) NOT NULL,
    high_water_mark   BIGINT       NOT NULL,
    pending_end       BIGINT       NULL,
    pending_horizon   BIGINT       NULL,
    topic_start         BIGINT       NOT NULL DEFAULT 0,
    lease_owner       VARCHAR(255) NULL,
    lease_expires_at  DATETIME2    NULL,
    PRIMARY KEY (topic_name)
);

PARTITION_METADATA

Singleton table (always one row with id = 1) that persists and guards the fixed partition count N. Every database that holds a partitioned topic, the event store’s or the capture topic’s, keeps its own row. When both topics write to the same database, they share that row, and each verifies it against the configured count. On first startup the row is seeded from spring.ddd.partitioning.partitions. On every later startup the configured value is verified against the persisted value. Startup fails fast if they differ, because the partition count is immutable once initialized. The event store creates the table with its own tables when at least one @EventSourced aggregate is detected. The capture topic creates it with its own tables. The row is seeded and verified whatever the schema-creation setting.

Column Type Notes

id

SMALLINT

Primary key. Always 1.

partitions

INT

Not null. The fixed partition count N, seeded from spring.ddd.partitioning.partitions on first startup.
DDL — all dialects 
-- PostgreSQL / H2 / HSQLDB / MySQL / MariaDB
CREATE TABLE IF NOT EXISTS PARTITION_METADATA (
    id         SMALLINT NOT NULL,
    partitions INT      NOT NULL,
    PRIMARY KEY (id)
)

-- SQL Server
IF OBJECT_ID(N'PARTITION_METADATA', N'U') IS NULL
    CREATE TABLE PARTITION_METADATA (
        id         SMALLINT NOT NULL,
        partitions INT      NOT NULL,
        PRIMARY KEY (id)
    )

PARTITION_INSTANCES

Which instances are live: one row per running instance that holds partition leases, refreshed by its coordinator every heartbeat interval with a single statement, however many consumers and partitions it holds. The instance’s memberships and every partition lease it holds are live exactly while its row is; once the row has lapsed, another instance takes its partitions over within one rebalance interval. A graceful stop deletes the row, whether the instance stops or only its last consumer that holds partition leases does; after a crash it lapses, and another instance’s rebalance deletes it. The row is written before the instance’s first membership or claim, and only while it runs a consumer that holds partition leases.

With schema creation off, the table must exist before the first consumer that holds partition leases starts: otherwise the startup fails, naming the table and the script that creates it.

Column Type Notes

instance_id

VARCHAR(255)

Primary key. The instance’s id, random for every run; never its host name, which a restarted instance keeps. As instance_id in PARTITION_OWNERSHIP and PARTITION_MEMBERS.

host_name

VARCHAR(255)

Nullable. For operators: the host the instance runs on; NULL when it cannot be resolved.

started_at

TIMESTAMP WITH TIME ZONE | DATETIME(6) | DATETIME2

Not null. For operators: when the row last became live.

last_heartbeat

TIMESTAMP WITH TIME ZONE | DATETIME(6) | DATETIME2

Not null. For operators: the last heartbeat that refreshed the row.

expires_at

TIMESTAMP WITH TIME ZONE | DATETIME(6) | DATETIME2

Not null. Database-clock time at which the instance lapses without a heartbeat. TIMESTAMP WITH TIME ZONE on PostgreSQL, H2, and HSQLDB; DATETIME(6) in UTC on MySQL/MariaDB; DATETIME2 in UTC on SQL Server.
DDL — all dialects 
-- PostgreSQL / H2 / HSQLDB
CREATE TABLE IF NOT EXISTS PARTITION_INSTANCES (
    instance_id     VARCHAR(255)             NOT NULL,
    host_name       VARCHAR(255)             NULL,
    started_at      TIMESTAMP WITH TIME ZONE NOT NULL,
    last_heartbeat  TIMESTAMP WITH TIME ZONE NOT NULL,
    expires_at      TIMESTAMP WITH TIME ZONE NOT NULL,
    PRIMARY KEY (instance_id)
);

-- MySQL / MariaDB
CREATE TABLE IF NOT EXISTS PARTITION_INSTANCES (
    instance_id     VARCHAR(255) NOT NULL,
    host_name       VARCHAR(255) NULL,
    started_at      DATETIME(6)  NOT NULL,
    last_heartbeat  DATETIME(6)  NOT NULL,
    expires_at      DATETIME(6)  NOT NULL,
    PRIMARY KEY (instance_id)
);

-- SQL Server
IF OBJECT_ID(N'PARTITION_INSTANCES', N'U') IS NULL
CREATE TABLE PARTITION_INSTANCES (
    instance_id     VARCHAR(255) NOT NULL,
    host_name       VARCHAR(255) NULL,
    started_at      DATETIME2    NOT NULL,
    last_heartbeat  DATETIME2    NOT NULL,
    expires_at      DATETIME2    NOT NULL,
    PRIMARY KEY (instance_id)
);

PARTITION_OWNERSHIP

Which instance may apply each partition of a consumer, such as a projection: a lease with an owner and a generation, held by at most one instance at a time. A lease has no expiry of its own: it is live while its owner’s row in PARTITION_INSTANCES is, so a row here is written only when a lease is claimed, taken over, handed back or released. A projection whose read models are in the database holds one lease per partition: by default its partitions are spread over the instances running it, and one set to distribution: single-owner holds all of them together. Every apply that shares the checkpoint’s transaction first checks the lease under the generation it claimed and locks its row, so the apply of an instance that has lost the lease rolls back, and no other instance takes the lease over until the apply commits. Taking over a free lease, or one whose owner has lapsed, raises its generation. A graceful stop releases the lease. A consumer’s row is inserted by its first claim, and its rows are deleted when it is unsubscribed from the last topic it has checkpoints on and the application no longer runs it on any topic.

With schema creation off, the table must exist before the first projection whose read models are in the database starts: otherwise the startup fails, naming the table and the script that creates it.

Column Type Notes

consumer_type

VARCHAR(32)

Primary key, part 1. The kind of consumer, e.g. projection, as in CONSUMER_CHECKPOINTS.

consumer_name

VARCHAR(255)

Primary key, part 2. The consumer’s name, e.g. account-summary.

partition_no

SMALLINT

Primary key, part 3. The partition leased, as in CONSUMER_CHECKPOINTS; every partition has its own row.

instance_id

VARCHAR(255)

Nullable. The instance holding the lease, as in PARTITION_INSTANCES; NULL while it is free.

generation

BIGINT

Not null, default 0. Rises by one with every takeover. An apply passes its check only under the generation it claimed, so a former holder cannot commit once another instance has taken over.
DDL — all dialects 
-- PostgreSQL / H2 / HSQLDB
CREATE TABLE IF NOT EXISTS PARTITION_OWNERSHIP (
    consumer_type     VARCHAR(32)              NOT NULL,
    consumer_name     VARCHAR(255)             NOT NULL,
    partition_no      SMALLINT                 NOT NULL,
    instance_id       VARCHAR(255)             NULL,
    generation        BIGINT                   NOT NULL DEFAULT 0,
    PRIMARY KEY (consumer_type, consumer_name, partition_no)
);
CREATE INDEX IF NOT EXISTS idx_partition_ownership_instance_id ON PARTITION_OWNERSHIP (instance_id);

-- MySQL / MariaDB
CREATE TABLE IF NOT EXISTS PARTITION_OWNERSHIP (
    consumer_type     VARCHAR(32)  NOT NULL,
    consumer_name     VARCHAR(255) NOT NULL,
    partition_no      SMALLINT     NOT NULL,
    instance_id       VARCHAR(255) NULL,
    generation        BIGINT       NOT NULL DEFAULT 0,
    PRIMARY KEY (consumer_type, consumer_name, partition_no),
    INDEX idx_partition_ownership_instance_id (instance_id)
);

-- SQL Server
IF OBJECT_ID(N'PARTITION_OWNERSHIP', N'U') IS NULL
CREATE TABLE PARTITION_OWNERSHIP (
    consumer_type     VARCHAR(32)  NOT NULL,
    consumer_name     VARCHAR(255) NOT NULL,
    partition_no      SMALLINT     NOT NULL,
    instance_id       VARCHAR(255) NULL,
    generation        BIGINT       NOT NULL DEFAULT 0,
    PRIMARY KEY (consumer_type, consumer_name, partition_no)
);
IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = 'idx_partition_ownership_instance_id'
               AND object_id = OBJECT_ID(N'PARTITION_OWNERSHIP'))
    CREATE INDEX idx_partition_ownership_instance_id ON PARTITION_OWNERSHIP (instance_id);

PARTITION_MEMBERS

Which instances currently run each consumer, such as a projection: one row per instance and consumer, written when the instance joins the consumer and deleted when it leaves. A row is live while its instance’s row in PARTITION_INSTANCES is. The coordinator assigns a consumer’s partitions by rendezvous hashing over the live members only, so a consumer’s single-owner lease lands on one of the instances that run it. A graceful stop deletes the instance’s rows; after a crash they lapse with its instance row, and another instance’s rebalance deletes them. A consumer’s row is inserted when the consumer registers with the coordinator; a heartbeat does not touch this table.

With schema creation off, the table must exist before the first consumer that holds partition leases starts.

Column Type Notes

consumer_type

VARCHAR(32)

Primary key, part 1. The kind of consumer, e.g. projection, as in PARTITION_OWNERSHIP.

consumer_name

VARCHAR(255)

Primary key, part 2. The consumer’s name, e.g. account-summary.

instance_id

VARCHAR(255)

Primary key, part 3. The id of the instance that runs the consumer, as in PARTITION_INSTANCES.
DDL — all dialects 
-- PostgreSQL / H2 / HSQLDB
CREATE TABLE IF NOT EXISTS PARTITION_MEMBERS (
    consumer_type  VARCHAR(32)  NOT NULL,
    consumer_name  VARCHAR(255) NOT NULL,
    instance_id    VARCHAR(255) NOT NULL,
    PRIMARY KEY (consumer_type, consumer_name, instance_id)
);
CREATE INDEX IF NOT EXISTS idx_partition_members_instance_id ON PARTITION_MEMBERS (instance_id);

-- MySQL / MariaDB
CREATE TABLE IF NOT EXISTS PARTITION_MEMBERS (
    consumer_type  VARCHAR(32)  NOT NULL,
    consumer_name  VARCHAR(255) NOT NULL,
    instance_id    VARCHAR(255) NOT NULL,
    PRIMARY KEY (consumer_type, consumer_name, instance_id),
    INDEX idx_partition_members_instance_id (instance_id)
);

-- SQL Server
IF OBJECT_ID(N'PARTITION_MEMBERS', N'U') IS NULL
CREATE TABLE PARTITION_MEMBERS (
    consumer_type  VARCHAR(32)  NOT NULL,
    consumer_name  VARCHAR(255) NOT NULL,
    instance_id    VARCHAR(255) NOT NULL,
    PRIMARY KEY (consumer_type, consumer_name, instance_id)
);
IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = 'idx_partition_members_instance_id'
               AND object_id = OBJECT_ID(N'PARTITION_MEMBERS'))
    CREATE INDEX idx_partition_members_instance_id ON PARTITION_MEMBERS (instance_id);

Durable domain-event metadata

Module: spring-ddd-starter-domain-events-modulith-jdbc.
Schema creation: spring.modulith.events.jdbc.schema-initialization.enabled (owned by Spring Modulith).

This table is created alongside the Spring Modulith EVENT_PUBLICATION table and stores serialised MessageMetadata keyed to the Modulith publication ID. It enables metadata (tenant ID, actor, clock) to survive an application restart and be restored when Modulith resubmits outstanding publications. Spring Modulith’s event publication tables, this one included, take no table-prefix.

EVENT_PUBLICATION_METADATA

Column Type Notes

PUBLICATION_ID

VARCHAR(36)

Primary key. Matches EVENT_PUBLICATION.ID (UUID as string).

COMPLETION_DATE

TIMESTAMP WITH TIME ZONE | DATETIME(6) | DATETIMEOFFSET

Nullable. Mirrors the Modulith completion date for retention alignment. TIMESTAMP(9) WITH TIME ZONE on H2; TIMESTAMP WITH TIME ZONE on HSQLDB and PostgreSQL; DATETIME(6) on MySQL/MariaDB; DATETIMEOFFSET on SQL Server. Indexed.

METADATA

CLOB | LONGVARCHAR | TEXT | LONGTEXT | NVARCHAR(MAX)

Not null. Serialised MessageMetadata JSON. CLOB on H2; LONGVARCHAR on HSQLDB; TEXT on PostgreSQL; LONGTEXT on MySQL/MariaDB; NVARCHAR(MAX) on SQL Server.
DDL — all dialects 
-- PostgreSQL
CREATE TABLE IF NOT EXISTS EVENT_PUBLICATION_METADATA (
  PUBLICATION_ID  VARCHAR(36)              NOT NULL,
  COMPLETION_DATE TIMESTAMP WITH TIME ZONE,
  METADATA        TEXT                     NOT NULL,
  PRIMARY KEY (PUBLICATION_ID)
);

CREATE INDEX IF NOT EXISTS idx_event_pub_meta_completion
  ON EVENT_PUBLICATION_METADATA (COMPLETION_DATE);

-- H2
CREATE TABLE IF NOT EXISTS EVENT_PUBLICATION_METADATA (
  PUBLICATION_ID  VARCHAR(36)                 NOT NULL,
  COMPLETION_DATE TIMESTAMP(9) WITH TIME ZONE,
  METADATA        CLOB                        NOT NULL,
  PRIMARY KEY (PUBLICATION_ID)
);

CREATE INDEX IF NOT EXISTS idx_event_pub_meta_completion
  ON EVENT_PUBLICATION_METADATA (COMPLETION_DATE);

-- HSQLDB
CREATE TABLE IF NOT EXISTS EVENT_PUBLICATION_METADATA (
  PUBLICATION_ID  VARCHAR(36)              NOT NULL,
  COMPLETION_DATE TIMESTAMP WITH TIME ZONE,
  METADATA        LONGVARCHAR              NOT NULL,
  PRIMARY KEY (PUBLICATION_ID)
);

CREATE INDEX IF NOT EXISTS idx_event_pub_meta_completion
  ON EVENT_PUBLICATION_METADATA (COMPLETION_DATE);

-- MySQL
CREATE TABLE IF NOT EXISTS EVENT_PUBLICATION_METADATA (
  PUBLICATION_ID  VARCHAR(36)  NOT NULL,
  COMPLETION_DATE DATETIME(6)  NULL,
  METADATA        LONGTEXT     NOT NULL,
  PRIMARY KEY (PUBLICATION_ID),
  INDEX idx_event_pub_meta_completion (COMPLETION_DATE)
);

-- MariaDB — same DDL as MySQL above

-- SQL Server
IF OBJECT_ID(N'EVENT_PUBLICATION_METADATA', N'U') IS NULL
CREATE TABLE EVENT_PUBLICATION_METADATA (
  PUBLICATION_ID  VARCHAR(36)    NOT NULL,
  COMPLETION_DATE DATETIMEOFFSET,
  METADATA        NVARCHAR(MAX)  NOT NULL,
  PRIMARY KEY (PUBLICATION_ID)
);

IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = 'idx_event_pub_meta_completion'
               AND object_id = OBJECT_ID(N'EVENT_PUBLICATION_METADATA'))
  CREATE INDEX idx_event_pub_meta_completion
    ON EVENT_PUBLICATION_METADATA (COMPLETION_DATE);

Related pages