Database Migrations

Server schema is managed by Alembic. See ADR 0007 for the decision and its consequences. ADR 0002 describes the earlier hand-applied-DDL era and is superseded; it no longer describes how the server works.

Server (Alembic)

Layout

Path	Purpose
server/alembic.ini	Config. Sets script_location = %(here)s/app/alembic and prepend_sys_path = ..
server/app/alembic/env.py	Reads DATABASE_URL from the environment and fails fast if unset. Imports app.models so SQLModel.metadata is populated.
server/app/alembic/versions/	Migration scripts. 39d8921c6309_baseline_schema.py is the baseline covering all 38 table models.
server/app/models/__init__.py	Must import every table model. Autogenerate only sees what is registered in SQLModel.metadata.

The app itself no longer creates schema: create_db_and_tables() was removed from the FastAPI lifespan in server/app/main.py.

Running Alembic

Run from server/, not server/app/. alembic.ini sets prepend_sys_path = ., so the app package is importable only from that directory. DATABASE_URL must be set.

cd server
export DATABASE_URL='mysql+pymysql://sapot:sapot@127.0.0.1:3306/sapot_dev'

alembic current                     # what revision this database is on
alembic history                     # all known revisions
alembic upgrade head                # apply everything outstanding
alembic check                       # fail if models have drifted from migrations

Changing the schema

1. Edit the SQLModel class in server/app/models/.
2. If you added a new model file, import it in server/app/models/__init__.py. Autogenerate cannot see a model that nothing imports, and will silently omit its table.
3. Generate the migration:

   alembic revision --autogenerate -m "short description"

4. Review the generated script. Autogenerate is a starting point, not an answer. It emits # ### commands auto generated by Alembic - please adjust! ### for a reason. A known case: the generated downgrade() emits a drop_index() per index before its drop_table(), which fails on MySQL with error 1553 when the index backs a foreign key. DROP TABLE drops its own indexes anyway, so those calls were removed from the baseline.
5. Verify:

   alembic upgrade head
   alembic check          # must report "No new upgrade operations detected"

6. Regenerate the derived docs from the repo root, since tables.md and erd.md are drift-checked in CI:

   python3 scripts/generate_db_docs.py

What CI enforces

.github/workflows/migration-check.yml runs on any change under server/app/** or to server/alembic.ini, against a MySQL 8.0 service container:

Step	Catches
alembic upgrade head	A migration that does not actually apply.
alembic check	A model change with no matching migration.
alembic downgrade base	A migration that does not reverse cleanly.

It runs against MySQL rather than SQLite on purpose: the error-1553 downgrade failure above passes on SQLite and fails on MySQL.

This job is the only automated guard against drift. server/app/tests/conftest.py builds its schema with SQLModel.metadata.create_all(), so the test suite exercises the models directly and cannot detect that a migration has fallen behind them.

Deployment

server/runserver.sh runs alembic upgrade head as a deploy step before starting gunicorn. Deploying code no longer requires a separate manual DDL step.

Note that alembic downgrade is a CI verification tool, not a production rollback procedure. Downgrading the baseline drops every table. To recover from a bad deploy, restore from backup per runbooks.md.

One-time cutover for existing databases

A database created by the old create_db_and_tables() path already has the tables but no alembic_version row. Running alembic upgrade head against it will try to CREATE TABLE tables that already exist and fail. Such a database must be stamped instead.

1. Back up first (see runbooks.md).
2. Bring the database level with the baseline by applying any outstanding DDL from the table below. The baseline describes the schema after these changes, so a database missing them is not equivalent to it.
3. Record it as being at the baseline without re-running it:

   alembic stamp head

4. Confirm:

   alembic current    # expect 39d8921c6309
   alembic check      # expect "No new upgrade operations detected"

From that point on, ordinary alembic upgrade head applies.

Outstanding pre-Alembic DDL

These changes were made to the models before Alembic existed and were applied by hand. They are folded into the baseline, so a new database gets them automatically. A database created before them needs them applied during step 2 above.

Date	Change	DDL
2026-07-26	message.content: VARCHAR(255) → TEXT. A 2000-char plaintext message (the client-side cap, MAX_MESSAGE_LENGTH) and E2E-encrypted base64 ciphertext both overflow 255 chars, so /sync/push failed with a generic 500 Internal Sync Error (issue #174).	ALTER TABLE message MODIFY COLUMN content TEXT NOT NULL;
2026-07-28	callparticipant.call_id: FK target conversation.id → call.id. Copy-paste bug; inserting a CallParticipant row against a real dev database either violated the FK or silently stored the wrong id (issue #270).	ALTER TABLE callparticipant DROP FOREIGN KEY <existing_fk_name>; ALTER TABLE callparticipant ADD CONSTRAINT callparticipant_ibfk_call FOREIGN KEY (call_id) REFERENCES call(id) ON DELETE CASCADE; (find <existing_fk_name> via SHOW CREATE TABLE callparticipant;)

Verify after applying:

SHOW COLUMNS FROM message LIKE 'content';    -- expect Type = text
SHOW CREATE TABLE callparticipant;           -- expect call_id REFERENCES call(id)

The table is callparticipant, not call_participant: SQLModel derives the name from the class name (CallParticipant) with no underscore, and no __tablename__ override is set.

---

GSM-module database note

The GSM module's actual datastore is MariaDB, configured via DB_PATH in GSM-module/GSM-fastapi/config.py (default mysql+pymysql://sapot:sapot@localhost:3306/sapot_db; see environment-config.md). The repository also has a committed GSM-module/GSM-fastapi/sapot.db SQLite file, but it is a stale, unused artifact — no code path reads it. It should be deleted from the repo rather than treated as a fallback database.

The GSM module has no migration tooling of its own. Alembic covers the server database only.

---

Mobile App (WatermelonDB)

The mobile app uses its own versioned migration tool: WatermelonDB's schemaMigrations() (mobile-app/sapot-mobile-app/features/shared/core/database/migrations.ts), paired with a versioned appSchema() (schema.ts, currently version 11).

Mechanism

• schema.ts declares the current shape of every table via tableSchema() and a single version number.
• migrations.ts declares an ordered list of { toVersion, steps } entries. Each step is addColumns({ table, columns }) or createTable({ name, columns }).
• On app start, WatermelonDB compares the on-device schema version to schema.ts's version and replays any migrations with toVersion greater than the stored version, in order.
• There is no dropColumns/dropTable step used in this codebase — the migration history is purely additive.

Version-by-version history (v4 → v11)

Version	Changes
v4	Add peers.email_verified (boolean, optional)
v5	Add messages.updated_at; add conversations.updated_at, conversations.title; create message_receipts (message, user, status, updated_at); create calls (conversation, initiator, call_type, status, start_time, end_time, updated_at); create call_participants (call, user, joined_at, left_at)
v6	Add message_receipts.created_at, message_receipts.is_deleted; add calls.created_at, calls.is_deleted; add call_participants.updated_at, call_participants.created_at, call_participants.is_deleted; add conversations.is_deleted; add conversation_participants.created_at, conversation_participants.updated_at
v7	Add peers.phone_number_verified (boolean, optional)
v8	Add messages.linked_message_id (string, optional — paired a P2P message with its SMS duplicate for the dual-send UX; removed from all read/write paths, column retained unused per the additive-only convention above)
v9	Add peers.role (string, optional); add messages.is_encrypted (boolean, optional)
v10	Add peers.is_guest (boolean, optional)
v11	Add peers.last_seen_at (number, optional)

Versions 1–3 predate the current migration file (the schema for those versions is not recoverable from migrations.ts; only v4 onward is tracked).

Known source quirk

schema.ts's peers table declares phone_number_verified twice in its column list (an accidental duplicate entry). WatermelonDB does not error on this, but it is a defect in the source worth fixing separately — not a docs issue.

Server contrast

Both sides are now versioned, but the trigger differs: WatermelonDB replays pending migrations automatically when the client starts, whereas the server applies them at deploy time via alembic upgrade head in runserver.sh. The mobile history is also purely additive, while Alembic migrations may alter or drop.