# 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:

```yaml
# 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](/en/docs/table-indexes-constraints) and [Validation](/en/docs/table-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.

:::callout
**Additive applies live, destructive waits for a restart** (DEC-024). This relaxes the older "edit config → restart → tables ready" rule only for the safe additive case. A failed live-migration **aborts the publish** — the version is not advanced and the live app is not swapped, so a broken migration never leaves routes pointing at a missing table.
:::

## 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](/en/docs/activity-monitoring) 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.      |

## Related Pages

- [Database Infrastructure](/en/docs/database-infrastructure) — the SQLite/PostgreSQL engines migrations target.
- [Table Indexes & Constraints](/en/docs/table-indexes-constraints) — the index and constraint definitions migrations apply.
- [Validation](/en/docs/table-validation) — field- and table-level rules tracked across evolution.
- [Activity Monitoring](/en/docs/activity-monitoring) — the audit stream correlated against schema versions.
- [Admin Dashboard](/en/docs/admin-dashboard) — `config/version` reports the active runtime and build.