Database Operations

Owner: Backend Engineering Last reviewed: 2026-Q2

This page covers PostgreSQL, Alembic migrations, pgvector, backup/restore expectations, and local database operations.

Local Setup

• Run commands from backend/py unless noted otherwise.
• Configure DB env vars from backend/py/env.example: DB_URL, DB_USER, DB_PASSWORD, DB_NAME.
• Apply migrations:

alembic upgrade head

• Start backend after the DB is reachable and migrations are applied:

python main.py

Migration Rules

• Prefer additive, backward-compatible migrations.
• Avoid destructive changes in the same deploy as code that depends on the destruction.
• For column renames or type changes, use expand/migrate/contract where practical.
• Keep migrations deterministic and free of external service calls.
• Review indexes for new query paths, especially permission, sharing, document, and ordering queries.

Creating A Migration

alembic revision --autogenerate -m "short description"

Then inspect the generated file manually:

• Confirm table and column names.
• Confirm nullable/default behavior.
• Confirm foreign keys and cascade behavior.
• Confirm indexes and unique constraints.
• Add manual data migration logic if autogenerate cannot infer intent.

Downgrades And Rollback

• Every migration should have a considered downgrade, even if the downgrade is intentionally limited.
• Do not downgrade production data without an incident commander or explicit release owner.
• If a deploy fails after a migration, decide whether to roll forward, roll back app code only, or run a downgrade based on data compatibility.

Backup And Restore

• Production and showcase databases should have regular backups configured at the platform level.
• Restore procedures must be tested before relying on them during an incident.
• Before destructive migrations, confirm recent backup availability and expected restore time.

pgvector

• Vector search depends on PostgreSQL pgvector support.
• Verify extension availability when provisioning a new database.
• Embedding dimensions must match the configured embedding model.
• Index choices should be reviewed when document volume or query latency grows.

Data Safety Checklist

• Does the migration affect ownership, permissions, sharing, documents, notifications, or action-point placement?
• Can old app code still run against the new schema during deployment?
• Can new app code tolerate old data?
• Are backfills bounded and observable?
• Is rollback documented?