Database and migrations

Supported databases#

• SQLite is the local default.
• PostgreSQL is required by the production readiness validator.

The API initializes SQLAlchemy tables at startup and also runs a migration helper. The repository additionally contains Alembic revisions 001_baseline and 002_approval_requests.

Apply Alembic revisions#

Set DATABASE_URL for the target environment, then run:

alembic upgrade head

Inspect the generated SQL or apply migrations in a staging database before production.

Verify current revision#

alembic current
alembic history

Legacy migration note#

dashboard/api/MIGRATION.md says the repository has no Alembic revisions and provides manual SQL for an older safety-layer change. That statement is now stale because revision files exist. Existing databases created before Alembic need an explicit reconciliation plan; do not blindly run both the manual SQL and Alembic against the same schema.

This reconciliation needs owner verification before a production migration runbook can be considered complete.

Backup and rollback#

The repository does not ship an automated backup or downgrade policy. Before a production schema change:

1. Create a provider-native database backup.
2. Record the current Alembic revision.
3. Test the forward migration against a restored copy.
4. Verify the API readiness endpoint and critical tenant queries.
5. Keep rollback ownership and recovery time explicit in the change plan.