DB DevOps API

ChangeOps surface: scripts, releases, target connections, executions, approvals, and the enterprise audit trail. Base path /api/db-devops, Professional+ tier (audit is Enterprise, metrics is any licensed tier), authenticated. Governed by the 5000/min DB DevOps limiter. This surface persists to Postgres via the repository layer; the legacy in-memory /api/migrations environment endpoints (IDE side) are a separate, non-durable subsystem.

Scripts — /api/db-devops/scripts

POST /api/db-devops/scripts

curl -X POST http://localhost:5002/api/db-devops/scripts \\
  -H "Authorization: Bearer YOUR_TOKEN" -H "Content-Type: application/json" \\
  -d '{ "name": "add-users-index",
        "sql_content": "CREATE INDEX idx_users_email ON users(email);",
        "execution_mode": "release", "environment": "production" }'
// 201 -> full script row; status "draft". Blast radius is computed asynchronously.

Releases — /api/db-devops/releases

Connections — /api/db-devops/connections

Target databases for execution. The connection record's config JSONB holds dialect-specific fields plus Manager metadata (groupings, environment), so the IDE and DB DevOps share one connection list. Passwords are encrypted at rest and masked on read.

// POST /connections/:id/test
{ "success": true,  "message": "Connection successful", "latency_ms": 45 }
{ "success": false, "message": "ECONNREFUSED 10.0.0.5:5432" }
// GET /connections/:id/databases
[ { "name": "myapp_production", "size_bytes": 52428800 },
  { "name": "myapp_staging",    "size_bytes": 31457280 } ]

Database enumeration queries the dialect's catalog (Postgres pg_database, MySQL information_schema.schemata, SQL Server sys.databases, Mongo listDatabases, Oracle v$pdbs).

Executions — /api/db-devops/executions

Two concerns share this router: durable status records (start/complete/fail/rollback transitions) and live execution against a target, which opens a driver pool (pg / mysql2 / mssql / oracledb / mongodb) after decrypting the target password.

POST /api/db-devops/executions/run

// body
{ "script_id": "uuid", "connection_id": "uuid", "database": "optional-override" }
// 200
{ "status": "COMPLETED", "rows_affected": 1, "execution_time_ms": 45, "result": [ ... ] }
// 500
{ "status": "FAILED", "error": "relation \\"users\\" already exists" }

POST /api/db-devops/executions/run-release

Each script runs against its own target_connection_id/target_database, falling back to the request's connection_id/database. Aggregate status is FAILED if any script fails; the per-script results[] pinpoints where it stopped.

{ "release_id": "uuid", "connection_id": "uuid", "database": "optional" }
// 200
{ "status": "COMPLETED", "results": [
    { "script_id": "abc", "script_name": "create-users-table", "status": "COMPLETED", "execution_time_ms": 32 },
    { "script_id": "def", "script_name": "add-users-index",    "status": "COMPLETED", "execution_time_ms": 18 } ] }
// partial failure
{ "status": "FAILED", "results": [
    { "script_id": "abc", "status": "COMPLETED", "execution_time_ms": 32 },
    { "script_id": "def", "status": "FAILED", "error": "syntax error at or near \\"SELEC\\"" } ] }

Approvals — /api/db-devops/approvals

Multi-signature approval workflow over approval_requests + approval_signatures. A request is satisfied when the required signatures are collected.

Audit — /api/db-devops/audit (Enterprise)

Append-only, tamper-evident audit trail. Every mutating request is recorded by auditMiddleware with sensitive fields redacted. Retention purge is the only permitted deletion.

GET /api/db-devops/audit/integrity

Recomputes the tamper-evident hash chain and reports whether the trail is intact. Any break identifies the first offending row so an operator can locate the tampering or gap.

{ "valid": true, "checked": 10432, "brokenAt": null }

Settings — /api/db-devops/settings

K/V store (AI provider, approval policy, notification/session config). Sensitive values are encrypted at rest.

Discovery — /api/db-devops/discovery

Fleet inventory: hosts, scans, discovered databases, and tags.

Metrics — /api/db-devops/metrics

GET /api/db-devops/metrics returns aggregate ChangeOps counters (scripts, releases, connections, pending approvals, execution outcomes) for dashboards. Any licensed tier.

Notifications — /api/notifications

The backend also emits these over Socket.IO (role-aware broadcast); polling these endpoints is the supported HTTP fallback.

Data protection — /api/data-protection

Subject-rights operations over user data.

IDE routes (Starter+)

The interactive IDE calls the connection/query/schema families directly. Connections are shared with DB DevOps.

// POST /api/queries/execute
{ "connectionId": "conn-uuid", "query": "SELECT * FROM users LIMIT 10" }
// ->
{ "data": [ { "id": 1, "email": "admin@company.com" } ],
  "columns": ["id","email"], "rowCount": 1, "executionTime": 12 }

executionTime is server-measured database time in ms (excludes browser round-trip).