Database migrations

All schema changes in StackRivet go through Flyway. There are no manual production database changes that get documented afterwards — the migration is the change, in version control, applied automatically on startup.

Where migrations live

Inside the owning module:

<module>/src/main/resources/db/migration/
  common/        # SQL that is portable across databases
  mysql/         # MySQL-specific (dialect differences)
  postgresql/    # PostgreSQL-specific

If a statement is portable, put it in common/; if it relies on dialect-specific syntax, split it into mysql/ and postgresql/. StackRivet supports MySQL 8.4 LTS and PostgreSQL 18.4.

Naming

V<yyyyMMdd>_<seq>__<description>.sql
# e.g. V20260601_010__customer_management.sql

Keep the migration description ≤ 200 characters — a longer description can overflow Flyway’s history column and break the baseline.

Table conventions

Tables use the sr_ prefix (and sr_<module>_<entity> for module tables) so StackRivet’s schema never collides inside a customer’s database. Carry the standard columns:

CREATE TABLE sr_cm_customer (
    id              VARCHAR(32)   NOT NULL,
    tenant_id       VARCHAR(32)   NOT NULL DEFAULT 'default',
    name            VARCHAR(128)  NOT NULL,
    status          VARCHAR(16)   NOT NULL DEFAULT 'active',
    avatar_asset_id VARCHAR(32),                              -- an asset reference, never a URL
    created_at      TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at      TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    created_by      VARCHAR(32),
    updated_by      VARCHAR(32),
    deleted         TINYINT       NOT NULL DEFAULT 0,
    deleted_at      TIMESTAMP     NULL,
    version         INT           NOT NULL DEFAULT 0,
    PRIMARY KEY (id)
);

CREATE INDEX idx_cm_customer_tenant_status ON sr_cm_customer (tenant_id, status);
CREATE INDEX idx_cm_customer_name          ON sr_cm_customer (name);

Column	Why
id, created_at, updated_at	Required on every core table
created_by, updated_by	On auditable tables
tenant_id	Tenant identifier (default 'default')
deleted, deleted_at	Soft-deletable tables
version	Optimistic locking

Add an index for every column you’ll filter on — the code generator’s index-risk check flags query fields that lack one.

Apply

Migrations run automatically when the app starts (java -jar stackrivet-app/... or mvn -pl stackrivet-app spring-boot:run). Confirm with the health check after startup:

curl http://127.0.0.1:8080/actuator/health   # {"status":"UP"}

Recover from a failed migration

If startup reports a Flyway checksum mismatch or a baseline drift (typically a half-applied migration in development), clear the failed history row and re-migrate — development databases only:

DELETE FROM flyway_schema_history WHERE success = 0;

Then restart the app. Never edit an already-applied migration in place; add a new one.

Repeatable migrations

A repeatable migration (R__…) re-applies whenever its checksum changes. If you edit one, either commit the new checksum or clear the failed history row — don’t leave a success = 0 row, or later migrations won’t apply.