Skip to main content
View as Markdown

Schema Migrations

Sovrium evolves your database schema automatically. It diffs the application configuration against the physical database, generates the appropriate SQL, and executes it inside a transaction — no hand-written migration files. The system validates checksums to detect drift, supports rollback to recover from failures, and records a complete audit trail of every change.

There are two trigger points: boot-time evolution (the config changed since last start) and live-migration on publish (a draft published over the API). Additive changes apply live without a restart; destructive changes are deferred to a restart for safety.

Automatic Schema Evolution

When the configuration differs from the database, Sovrium detects the change and applies the migration automatically — all within a transaction so a partial failure rolls back cleanly.

Supported structural changes:

# Before
tables:
  - id: 1
    name: users
    fields:
      - { id: 1, name: email, type: email }

# After — add a phone field; Sovrium generates ADD COLUMN
tables:
  - id: 1
    name: users
    fields:
      - { id: 1, name: email, type: email }
      - { id: 2, name: phone, type: single-line-text }
Change class Examples
Structural Add/remove/rename fields and tables.
Field property Type change, constraint, default, options, required toggle.
Index & view Add/drop indexes; create/update saved views.

Field IDs are the rename anchor — keep the id stable and change the name to rename a column without losing data. See Table Indexes & Constraints and Validation for the per-field properties migrations track.

Checksum Validation

Sovrium fingerprints the schema to skip unnecessary migration work and detect drift.

  1. On the first migration, Sovrium computes a SHA-256 checksum of the schema and stores it.
  2. On each subsequent startup it compares the current schema's checksum against the stored one.
  3. Unchanged → the server starts quickly, running no migrations.
  4. Changed → Sovrium executes the necessary migrations and saves the new checksum.

This makes restarts cheap when nothing changed and guarantees the database matches the declared schema when something did.

Live-Migration on Publish

Publishing a draft over the admin API makes it the live schema without a restart — and for additive data-structure changes, the physical table or column is created on the running server immediately.

Change Applied Why
Additive (add table, add column) Live — table/column exists on the running server and is immediately queryable. Adding structure cannot lose data; safe to apply against live traffic.
Destructive (drop/rename column or table) Deferred to restart A drop/rename against a running server with traffic risks irreversible data loss without an operator's deliberate action.

The publish pipeline runs migrate-before-swap: validate → optimistic-concurrency check → apply additive DDL → insert version row → swap live app → re-register routes. Because the DDL runs before the route swap, the newly registered /api/tables/:slug/records routes never point at a table that does not yet exist.

Rollback

When a migration fails, the transaction rolls back and the schema is left in its prior consistent state. Rollback is currently available programmatically; dedicated CLI rollback commands (migrate:rollback, --to <version>, --force) are planned. The version ledger records every applied schema version, so a prior version snapshot can be re-applied.

Audit Trail

The migration system records, for each migration:

  • Migration timestamp
  • Schema version number
  • Schema checksum (SHA-256)
  • Complete schema snapshot
  • Rollback operations and reason

This audit trail is the source of truth for "which schema produced this database" — auditors cross-reference it against the activity log to correlate data changes with the schema version in force at the time.

Error Handling

Migrations fail loud and safe. Each of these scenarios aborts before or during execution and rolls back any partial work:

Scenario Behavior
Invalid schema Validation errors are surfaced before any migration starts.
Migration failure A SQL execution error rolls back the transaction.
Connection error The database being unavailable aborts the run.
Constraint violation A foreign-key or unique-constraint failure rolls back.