Database Operations

Database Operations

PulseStream uses two PostgreSQL databases:

• CP database — Control Plane (organizations, users, workspaces, billing)
• Agent database — API Server (incidents, analyses, telemetry)

Migrations

Check Current State

# CP database
kubectl exec -n <namespace> deployment/control-plane -- \
  uv run alembic -c migrations/alembic.ini current

# Agent database
kubectl exec -n <namespace> deployment/api-server -- \
  uv run alembic -c migrations/alembic.ini current

Create a New Migration

# From service root directory
cd control-plane  # or api-server
alembic -c migrations/alembic.ini revision --autogenerate -m "short_description"

Caution

• Keep names SHORT (max 30 chars descriptive part)
• Never edit a migration after it’s been applied
• alembic_version uses VARCHAR(255), not the default VARCHAR(32)

Apply Migrations (Kubernetes)

Init containers handle migrations automatically on pod restart:

kubectl rollout restart deployment/<service> -n <namespace>

Local Database Reset (Tilt)

Wipe all data and re-run migrations:

bash scripts/tilt-reset-db.sh

This drops CP (port 5433) and Agent (port 5434) databases, restarts deployments, and re-runs migrations.

Schema Drift Detection

Schema drift tests catch model changes without migrations at PR time — no DB needed:

make test-cp-unit    # includes schema drift check
make test-api-pr-gate  # includes schema drift check

Skip with SKIP_SCHEMA_DRIFT_CHECK=1 for comment-only edits.

Emergency: Stuck Migration

If a migration fails partway:

1. Check alembic_version table for current revision
2. Do not manually modify the migration file
3. Create a new migration that fixes the issue
4. If the database is in an inconsistent state, restore from backup

Key Gotcha

The async env.py must use .begin(), not .connect(). Using .connect() opens an implicit transaction that silently rolls back — migrations appear to succeed but nothing is committed.