Skip to main content

Migration Guide

Purpose​

All backend schema changes go through Flyway migrations under lumie-backend/app/src/main/resources/db/migration/public/. This page is a how-to guide for adding or reviewing a migration without breaking tenant isolation, ownership boundaries, or rollout order.

Prerequisites​

  • A workspace checkout that includes lumie-backend/gradlew and the migration directory lumie-backend/app/src/main/resources/db/migration/public/
  • A chosen target table family: platform-owned or tenant-scoped
  • If you plan to run :app:flywayInfo, the backend's database configuration must already be available in your local environment or dev setup

Naming And Placement​

RuleRequirement
Locationlumie-backend/app/src/main/resources/db/migration/public/
NameV<number>__short_description.sql
OrderingNext unused integer version
ScopeOne coherent schema change or data repair
RollbackExplain when not safely reversible

Step 1: Pick The Next Version Number​

cd /path/to/Lumie/lumie-backend
rg --files app/src/main/resources/db/migration/public | sort -V | tail -n 10

Expected success signal: the last few migration filenames appear in numeric order so you can choose the next unused V<number>__...sql.

Step 2: Draft The Migration In The Backend Repo​

Create the new file in:

app/src/main/resources/db/migration/public/V<number>__short_description.sql

Use one migration for one coherent schema change. If the work spans unrelated surfaces, split it before review.

Do not edit an existing V<number>__*.sql file. Lumie's workspace hooks block writes to already-created versioned Flyway migrations because the dev cluster can apply a new migration immediately through Tilt. After Flyway records a checksum, changing the same file breaks backend startup with a checksum mismatch. Add a new migration version instead.

Step 3: Apply The Correct Ownership Pattern​

Use this checklist for new tenant-owned tables:

tenant_id bigint not null
alter table <table> enable row level security;
alter table <table> force row level security;
create policy tenant_isolation on <table>
  using (tenant_id = nullif(current_setting('app.tenant_id', true), '')::bigint)
  with check (tenant_id = nullif(current_setting('app.tenant_id', true), '')::bigint);

Also add indexes that match access patterns. Most tenant-scoped lookup indexes should start with tenant_id.

Expected success signal: every new tenant table carries tenant_id, RLS is enabled and forced, and the policy keys off current_setting('app.tenant_id').

Use the platform checklist only when the table is intentionally Lumie-owned cross-tenant data.

Platform tables may omit RLS only when the table is intentionally Lumie-owned cross-tenant data. The migration comment must make that explicit.

Examples:

  • tenants
  • plans
  • subscriptions
  • billing_keys
  • payment_transactions
  • event_publication
  • shedlock

Expected success signal: the migration comment or surrounding DDL makes it clear that application-layer authorization, not RLS, owns access control.

Step 4: Prefer Safe Change Patterns​

ChangePreferred pattern
Add nullable columnAdd column, deploy code, backfill later if needed
Add non-null columnAdd nullable/default, backfill, then enforce not null
Add index on large tableUse concurrent index where supported by migration tooling
Add tenant-safe FKInclude (id, tenant_id) unique index on referenced table
Repair RLSBackfill tenant_id, enforce not-null, then enable/force RLS
Rename conceptKeep compatibility in code and docs until all callers are moved

Step 5: Review The Diff​

cd /path/to/Lumie/lumie-backend
git diff -- app/src/main/resources/db/migration/public

Expected success signal: the diff shows only the intended migration file or follow-up edits in the same schema change.

Use this review checklist before you finish:

  1. Does the table belong to platform scope or tenant scope?
  2. If tenant-scoped, does it have tenant_id, RLS, and tenant-aware indexes?
  3. Does application code set tenant context before touching it?
  4. Are hard FKs crossing module boundaries intentionally chosen?
  5. Does the migration avoid destructive live-data rewrites?
  6. Does the relevant product documentation need an update?

Verification​

cd /path/to/Lumie/lumie-backend
rg -n "tenant_id|ENABLE ROW LEVEL SECURITY|FORCE  ROW LEVEL SECURITY|tenant_isolation" \
  app/src/main/resources/db/migration/public

Expected success signal: tenant-scoped migrations consistently show the tenant column plus the RLS statements and policy definition.

cd /path/to/Lumie/lumie-backend
./gradlew :app:flywayInfo

Expected success signal: Flyway lists your new version once in order. Depending on the local database state, it should appear as pending before apply or successful after apply.

Common Failures​

  • Duplicate migration version numbers cause Flyway ordering conflicts.
  • Editing an already-created versioned migration causes Flyway checksum mismatch. Use a new V<number>__*.sql file for follow-up DDL or data repair.
  • A tenant-owned table is added without FORCE ROW LEVEL SECURITY, leaving a bypass path for the runtime role.
  • A new index omits tenant_id even though the primary access path is tenant-scoped.
  • A hard FK is added across a module boundary that the code intentionally keeps as a soft reference.
  • :app:flywayInfo fails because the backend database environment is not available locally yet.