72 lines
3.1 KiB
Markdown
72 lines
3.1 KiB
Markdown
# Database migrations policy
|
||
|
||
This document describes how database schema changes are handled for Backupchecks .
|
||
|
||
## Overview
|
||
|
||
- The baseline schema is defined in `backend/app/models.py`.
|
||
- On application startup, the following is executed inside `create_app()`:
|
||
1. `db.create_all()` – creates any missing tables and columns defined in the models.
|
||
2. `run_migrations()` – executes in-image SQL migrations from `backend/app/migrations.py`.
|
||
|
||
This approach allows:
|
||
- Clean databases to be created automatically.
|
||
- Existing databases to be upgraded in-place without manual SQL.
|
||
- Safe repeated restarts: migrations are idempotent and can be run multiple times.
|
||
|
||
## Adding migrations
|
||
|
||
When you change the schema in a way that is not automatically covered by `db.create_all()` (for example,
|
||
altering column nullability, adding constraints, backfilling data), follow these steps:
|
||
|
||
1. Add or adjust the corresponding model(s) in `models.py`.
|
||
2. In `migrations.py`:
|
||
- Add a new function, for example:
|
||
|
||
- `def migrate_xyz():`
|
||
- Perform the required SQL using `db.get_engine()` and `sqlalchemy.text`.
|
||
- Always check the current state first (e.g. whether a column or constraint already exists).
|
||
- Call this function from `run_migrations()` in the correct order.
|
||
|
||
3. Do NOT remove older migration functions. They must remain so that:
|
||
- Existing databases can still be upgraded from older versions.
|
||
- New installations run all migrations but older ones become no-ops because their checks see that the
|
||
changes are already applied.
|
||
|
||
4. Each migration must be **idempotent**:
|
||
- It should detect whether its change is already in place and then exit without error.
|
||
- This allows `run_migrations()` to be executed on every startup.
|
||
|
||
## Current migrations (initial set)
|
||
|
||
Implemented in `backend/app/migrations.py`:
|
||
|
||
- `migrate_add_username_to_users()`
|
||
- Adds a `username` column to the `users` table if it does not exist.
|
||
- Backfills `username` from `email` where possible.
|
||
- Sets `username` to `NOT NULL`.
|
||
- Adds a UNIQUE constraint on `users.username`.
|
||
|
||
- `migrate_make_email_nullable()`
|
||
- Ensures the `email` column on `users` is nullable.
|
||
- If the column is currently `NOT NULL`, the migration executes:
|
||
`ALTER TABLE "users" ALTER COLUMN email DROP NOT NULL`.
|
||
|
||
- `run_migrations()`
|
||
- Calls the above migrations in order.
|
||
- Logs progress to stdout so changes are visible in container / Portainer logs.
|
||
|
||
- `migrate_reporting_report_config()`
|
||
- Adds `report_definitions.report_config` (TEXT) if it does not exist.
|
||
- Stores the JSON report definition for the reporting UI (selected columns, chart types, filters) so the same definition can later be reused for PDF export.
|
||
|
||
## Future changes
|
||
|
||
- Every time you introduce a non-trivial schema change, update:
|
||
- `backend/app/models.py`
|
||
- `backend/app/migrations.py`
|
||
- This document (`docs/migrations.md`) – add a short description of the new migration.
|
||
|
||
- When sharing the repository state (for example in a ZIP), always include the current `migrations.py`
|
||
and this document so the migration history and policy are clear.
|