Database Setup & Monitoring

The metadata store is a single Postgres database (db_devops) holding all application state. This page covers provisioning, migration strategy, pooling, logging, and the health surface. It is not one of the customer databases the IDE connects to, treat it as an internal control-plane datastore.

Provisioning

Postgres 13+. The bundled Compose and Helm chart run postgres:16-alpine; bring-your-own managed Postgres (RDS, Cloud SQL, Azure Database) is fully supported at 13 or newer. The application role needs DML plus CREATE TABLE/ALTER for the migration runner on first boot.

CREATE DATABASE db_devops;
CREATE USER simcha WITH PASSWORD 'strong_password_here';
GRANT ALL PRIVILEGES ON DATABASE db_devops TO simcha;

Migrations

Numbered, idempotent SQL migrations ship in server/database/migrations/ (001_initial_schema.sql through 011_script_environment.sql) and are tracked in a schema_migrations table. Two application modes:

1. Implicit (MIGRATE_ON_BOOT=true, default), the entrypoint drains pending migrations before serving. Correct for single-instance and Compose stacks.
2. Explicit, run the runner as a discrete step and set MIGRATE_ON_BOOT=false on app replicas so exactly one authoritative path applies schema. Required for HA/rolling deployments to avoid concurrent-migration races.

   node dist/server/database/migrate.js   # in-container
   npm run migrate                        # via npm
   # Helm applies this as a pre-install/pre-upgrade Job when migration.enabled=true

Schema surface

• users, id, email, bcrypt password_hash, role, is_active, MFA columns (secret/enabled/backup-codes/enrolled-at, secret encrypted at rest)
• scripts, sql_content, target_connection_id, target_database, environment, approval_status, content_hash, dependencies (JSON)
• releases / release_scripts, deployable bundles grouping scripts by target environment
• connections, managed targets with AES-256-GCM-encrypted password + SSH credentials, ssl_config, config_json (JSONB)
• executions, run history: status, timings, duration_ms, rows_affected, error, executor
• approval_requests + approval_signatures, multi-sig workflow
• repo_connections, git/cloud sources for change scripts
• license, activated key records (60s in-process cache TTL, see Environment Variables)
• audit_logs + auth_audit_log, request-level trail for every mutating verb, sensitive fields auto-redacted, with retention indexes on created_at/user/action/entity
• notifications + notification_preferences, discovery inventory tables, schema_migrations

Connection Pooling

The pool is pg with defaults DB_POOL_MAX=20, DB_IDLE_TIMEOUT_MS=30000, DB_CONNECTION_TIMEOUT_MS=2000. Under high fan-out (many replicas × pool max), front Postgres with PgBouncer in transaction mode rather than lifting DB_POOL_MAX past your server's max_connections budget.

Managed Postgres Recommendations

• Prefer a managed service for automated backups, failover, and patching. In Helm, set postgres.enabled=false and point externalPostgres.host at it.
• Enforce TLS, require at minimum, verify-full where you can pin the CA.
• Least-privilege role, dedicated service user with DML + migration DDL only; never the postgres superuser.
• Backups, daily with 7+ day retention and periodic restore drills.
• Sizing, the metadata footprint is small; a burstable small instance covers most fleets. Scale for script/execution volume and concurrency, not raw data size.

Logging

Structured logging is Pino to stdout, no file sink. In production, JSON at info and above; in development, pretty-printed at debug. Keep LOG_STDOUT_ONLY=true under any orchestrator. Every line carries a request id propagated end-to-end via X-Request-Id, so a single request is traceable across the Next.js proxy and the Express backend, correlate on that field in your aggregator.

Health Surface

GET /api/health              # overall status + DB readiness
GET /api/health/migrations   # applied-migration inventory

{ "status": "ok", "uptime": 86400, "database": "ready" }

/api/health returns STARTING while the metadata DB is unreachable (boot retries 10× with 3s backoff), then ok once migrations are applied and auth is initialized, wire it to container liveness/readiness probes and LB target checks, and gate readiness on ok so traffic never lands mid-migration. /api/health/migrations exists for deploy validators asserting the expected schema version post-rollout.

Monitoring

• Probe /api/health every 30-60s from your monitoring system.
• Trace by X-Request-Id across proxy and backend.
• Alert on 5xx rate and on 429 spikes (rate-limit pressure or a missing real-client-IP header).
• Watch Postgres, connection count vs. pool ceiling, query latency, replication lag, disk. Use the managed service's telemetry.