Database Migrations

Causet uses two different migration systems depending on which database is being changed.

Two migration systems

Flyway migrations (control plane)

The control plane database (causet_control_plane) uses Flyway for versioned migrations. Migrations live in:

apps/causet-saas-cloud/src/main/resources/db/migration/
  V1__create_platforms.sql
  V2__create_applications.sql
  V3__create_releases.sql
  ...

Flyway runs automatically when causet-saas-cloud starts. If the database is behind the application version, pending migrations are applied in order.

You do not run these manually. If causet-saas-cloud fails to start, check the Flyway migration log:

docker compose logs causet-saas-cloud | grep -i flyway

Common Flyway issues:

• Checksum mismatch: a migration file was modified after being applied. Never edit a committed migration file.
• Failed migration: a SQL error left the migration in a failed state. Fix the underlying issue and re-run.

IR-driven DDL (projection tables)

Projection tables are created and altered by the causet-projection-worker based on the compiled IR artifact (causet.projections.json). This is not Flyway — there are no numbered migration files.

When you deploy a new release to a fork, the projection worker:

1. Fetches the new causet.projections.json from S3
2. For each projection defined in the IR, compares the declared schema to the current state of the tenant’s schema
3. Issues CREATE TABLE IF NOT EXISTS and ALTER TABLE ADD COLUMN IF NOT EXISTS statements as needed

Example: adding a column

You add a venue_name field to user_concert_memory in your .causet file:

# projections/concert_memory.projections.causet
projections:
  user_concert_memory:
    # ...existing fields...
    fields:
      user_id:     TEXT
      show_id:     TEXT
      checked_in:  BOOLEAN
      venue_name:  TEXT          # new field

After recompiling and deploying the new release, the projection worker issues:

ALTER TABLE my_platform_concert_app_main.user_concert_memory
  ADD COLUMN IF NOT EXISTS venue_name TEXT;

The column is added. Existing rows get NULL for venue_name until the next event is processed for that entity.

Additive only

IR-driven DDL is additive only. Removing a field from your DSL definition does not drop the column — it simply stops writing to it.

This is intentional:

• Dropping columns requires a coordinated migration with no live readers
• Old events that were derived with the old field still produce valid UPSERTs
• Rollback to an old IR version does not break because the column still exists

To remove a column permanently:

1. Remove the field from the DSL, compile, and deploy (the column stops being written)
2. Verify no application code reads the column
3. Drop it manually:

ALTER TABLE my_platform_concert_app_main.user_concert_memory
  DROP COLUMN venue_name;

Warning: Only drop columns manually after verifying no active release writes to them. The projection worker will fail if it tries to UPSERT a value into a column that no longer exists.

Tenant schema creation

Tenant schemas are created by the projection worker when a release is first deployed to a fork. The schema name:

{platformId}_{applicationId}_{forkId}

Special characters are sanitized. Total length must not exceed 63 characters (PostgreSQL identifier limit).

-- Example schema
CREATE SCHEMA IF NOT EXISTS my_platform_concert_app_production;
SET search_path = my_platform_concert_app_production;
CREATE TABLE IF NOT EXISTS user_concert_memory (...);

Schema rollback

To roll back to a previous IR version:

1. In causet-saas-cloud, activate the previous release on the fork
2. Services fetch the previous IR from S3
3. The projection worker resumes using the previous projection definitions

The rollback is safe because IR-driven DDL is additive. Columns added by the new release still exist in the database but are ignored by the old IR. No data is lost.

Verifying DDL was applied

After deploying a release, verify the expected tables and columns exist:

psql postgresql://projections-db:5432/projections
 
-- List tables in tenant schema
\dt my_platform_concert_app_main.*
 
-- Check column types
\d my_platform_concert_app_main.user_concert_memory
 
-- Verify index exists
\di my_platform_concert_app_main.*

Common migration issues

Stale IR (recompile needed)

If the projection worker logs an error like field not found in IR: venue_name, the running IR does not match the DSL. Recompile and upload a new IR version before deploying.

Missing tenant schema

If the tenant schema does not exist, the projection worker was likely never deployed to that fork, or the deploy failed before DDL was applied. Re-deploy the release.

Column type mismatch

IR-driven DDL does not change column types — only adds columns. If you change a field from TEXT to BIGINT, the column type does not update automatically. Change the column type manually and ensure existing data is compatible:

ALTER TABLE my_platform_concert_app_main.user_concert_memory
  ALTER COLUMN created_at TYPE BIGINT USING created_at::BIGINT;

Schema name too long

If {platformId}_{applicationId}_{forkId} exceeds 63 characters, the schema cannot be created. Shorten the platform ID, application ID, or fork name. The compiler warns about this at compile time if the names can be determined.