Database

SAT keeps all state in one PostgreSQL database: users and credentials, companies, agents, goals, projects, tasks, runs with their transcripts, approvals, routines and the audit log. The schema is managed by Alembic, and the API applies migrations itself when it starts.

RequirementsCopy Anchor Link

• PostgreSQL 16. Any PostgreSQL 16 works, managed (for example Cloud SQL for PostgreSQL) or self-run; the cli-e2e CI job uses postgres:16.
• PostgreSQL only. Migration 002 changes constraints on existing tables, which SQLite cannot do without Alembic batch mode, so the API fails to initialise on SQLite. The pytest suite uses an in-memory SQLite database but builds tables with Base.metadata.create_all, not migrations.
• Driver. psycopg2-binary (synchronous SQLAlchemy 2). asyncpg is installed but not used.
• Row locks. Run claims use SELECT ... FOR UPDATE SKIP LOCKED, and task numbering locks the company row. Both rely on PostgreSQL semantics.

Connection settingsCopy Anchor Link

NullPool means each API request opens and closes a TCP connection to PostgreSQL. With one API instance and modest traffic this is acceptable, even on a small database instance; see Scaling before raising traffic. The SSE endpoint closes its session as soon as membership is checked, so an open event stream does not hold a database connection.

If your database has no public IP, which is recommended, you cannot connect to it from a laptop. Use your provider's export and import for data access, or a temporary job inside the private network.

Migrations at startupCopy Anchor Link

On startup the API's lifespan handler calls init_db() (database/connection.py):

1. If the database has a users table but no alembic_version table (it was created by the old create_all code), it is stamped at revision 001 first, so only newer migrations run.
2. alembic upgrade head runs in-process.
3. If anything fails, the error is logged as Database initialization failed, the API keeps serving, and /health answers 503 with "database": "initialization failed" until a revision starts cleanly. A deploy that gates on /health therefore refuses a release whose migrations fail.

RevisionsCopy Anchor Link

Revisions are in apps/api/alembic/versions, numbered and linear.

Each revision has a downgrade(). Downgrades drop tables and columns, and with them their data.

Check the current revision:

cd apps/api
DATABASE_URL=postgresql://... .venv/bin/alembic current
DATABASE_URL=postgresql://... .venv/bin/alembic history

Writing a migrationCopy Anchor Link

cd apps/api
export DATABASE_URL=postgresql://sat@127.0.0.1:5433/sat_main
.venv/bin/alembic upgrade head
.venv/bin/alembic revision --autogenerate -m "short description"

.venv/bin/alembic upgrade head
.venv/bin/alembic downgrade -1 && .venv/bin/alembic upgrade head
cd ../.. && SAT_API_URL=http://localhost:8080 scripts/03-testing/cli-smoke.sh

BackupsCopy Anchor Link

SAT has no backup job of its own; use your database's backups. Recommended:

• Automated daily backups, kept for at least 7 days.
• Point-in-time recovery, so you can restore to just before a bad change.
• An on-demand backup before any migration that changes or removes data.
• A restore tested on a scratch instance from time to time.

On Cloud SQL, for example:

gcloud sql instances describe <instance> --project <project> \
  --format 'yaml(settings.backupConfiguration)'
gcloud sql instances patch <instance> --project <project> \
  --backup-start-time 03:00 --enable-point-in-time-recovery --retained-backups-count 14
gcloud sql backups create --instance <instance> --project <project> --description "before release <sha>"

With a self-run PostgreSQL, pg_dump and pg_restore do the same job:

pg_dump --format custom --file sat_main-$(date +%Y%m%d).dump "$DATABASE_URL"
pg_restore --clean --if-exists --dbname "$DATABASE_URL" sat_main-20261005.dump

Export and restoreCopy Anchor Link

On Cloud SQL, logical exports go through a Cloud Storage bucket. The instance's service account needs write access on the bucket to export and read access to import.

# Export the database to a SQL file
gcloud sql export sql <instance> gs://<bucket>/sat_main-$(date +%Y%m%d).sql \
  --database sat_main --project <project>

# Import a SQL file into an instance
gcloud sql import sql <instance> gs://<bucket>/sat_main-20261005.sql \
  --database sat_main --user <database user> --project <project>

Restore a whole instance from an automated backup (this overwrites all data on the target):

gcloud sql backups list --instance <instance> --project <project>
gcloud sql backups restore <backup-id> --restore-instance <instance> \
  --backup-instance <instance> --project <project>

After a restore, restart the API (deploy the current revision again, or on Cloud Run gcloud run services update <service> --update-env-vars RESTORED_AT=<timestamp>) so it reconnects and re-runs init_db(). Runs that were running at backup time will be failed by lease expiry on the next claim or scheduler sweep.

Data growthCopy Anchor Link

There is no retention or cleanup job. These tables only grow:

RelatedCopy Anchor Link