gerardpaligot/confily/exposed-migration

Exposed Migration Skill

When a Migration Is Required

A migration must be created whenever you change an Exposed table definition in a way that alters the existing database schema. SchemaUtils.create() only creates tables that do not exist yet — it never modifies existing ones.

If you skip the migration, the new column/index will exist in code but not in the production database, causing runtime crashes on the next deployment.

Project Infrastructure

The migration system lives at:

backend/src/main/java/com/paligot/confily/backend/internals/infrastructure/exposed/migrations/
  Migration.kt           — interface every migration implements
  MigrationsTable.kt     — schema_migrations table that tracks what has been applied
  MigrationManager.kt    — runs pending migrations at startup, records applied ones
  MigrationRegistry.kt   — ordered list of all migrations
  versions/              — one file per migration

DatabaseFactory.init() calls MigrationManager(MigrationRegistry.allMigrations).migrate(database) after SchemaUtils.create(). Each migration is run exactly once: its id is written to schema_migrations on success and skipped on all subsequent startups.

How to Create a Migration

1. Create the migration class

Add a new file in versions/ named after what the migration does:

// backend/.../migrations/versions/AddMyNewColumnMigration.kt
package com.paligot.confily.backend.internals.infrastructure.exposed.migrations.versions
 
import com.paligot.confily.backend.some.infrastructure.exposed.SomeTable
import com.paligot.confily.backend.internals.infrastructure.exposed.migrations.Migration
import org.jetbrains.exposed.sql.SchemaUtils
 
object AddMyNewColumnMigration : Migration {
    override val id = "YYYYMMDD_add_my_new_column"
    override val description = "Add <column_name> to <table_name> for <reason>"
 
    override fun up() {
        SchemaUtils.createMissingTablesAndColumns(SomeTable)
    }
}

2. Register it in MigrationRegistry

Open MigrationRegistry.kt and append the new migration at the end of allMigrations:

val allMigrations: List<Migration> = listOf(
    // ... existing migrations ...
    AddMyNewColumnMigration
)

Do not reorder existing entries — migrations are applied in list order and each runs only once.

ID Convention

Use YYYYMMDD_short_snake_case_description, e.g.:

• 20260326_add_activities_external_provider
• 20260401_add_partner_video_url

The id must be unique across all migrations and sortable chronologically. The MigrationManager sorts by id before applying, so the date prefix guarantees correct ordering.

Patterns for Common Changes

Add nullable columns to an existing table

Just add the column to the table object and call SchemaUtils.createMissingTablesAndColumns:

override fun up() {
    SchemaUtils.createMissingTablesAndColumns(TargetTable)
}

Execute raw SQL (e.g. drop a column, add a partial index)

Use exec() inside a Transaction:

override fun up() {
    exec("ALTER TABLE my_table DROP COLUMN IF EXISTS old_column")
}

up() is always called inside an active transaction (from MigrationManager), so exec() is available directly.

Data migration

Perform data manipulation with Exposed DSL inside up():

override fun up() {
    SchemaUtils.createMissingTablesAndColumns(TargetTable)
    TargetTable.update {
        it[newColumn] = "default_value"
    }
}

Rules

1. Never modify an already-applied migration. Once an id is in schema_migrations, that migration will never run again. Fix mistakes with a new migration.
2. Never reorder `MigrationRegistry.allMigrations`. The list order controls application order for new databases.
3. Keep `up()` idempotent where possible. Use IF NOT EXISTS, IF EXISTS, and createMissingTablesAndColumns so re-runs on a partially migrated database are safe.
4. One logical change per migration. Small, focused migrations are easier to debug and roll forward.
5. Always provide a meaningful `description`. It appears in logs and schema_migrations for auditability.