DB DevOps API

ChangeOps surface: scripts, releases, target connections, executions, approvals, and the audit trail. Base path /api/db-devops, needs an active license, 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; approval_status "pending". Blast radius is computed asynchronously.

PUT /scripts/:id checks a new environment or target_connection_id the same way. Other failures answer 500 with SCRIPT_CHANGE_FAILED and a fixed message; the server log has the cause.

Releases — /api/db-devops/releases

Invalid fields answer 400 with the reason. The sign-off status, the signed-off fields and executed_at are set only by sign-off and by running the release; a create or update that tries to change them answers 400 "Sign-off status changes only through sign-off".

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.

GET /api/db-devops/connections/:id/databases

Lists the instance's databases through its engine. listed is false for engines without a list of databases, with a message saying the connection's database is used.

{ "listed": true, "databases": [ { "name": "0", "size_bytes": null, "detail": "12 keys", "system": false } ] }
{ "listed": false, "databases": [], "message": "Cassandra has no list of databases; scripts for Demo Cassandra run against the database set on the connection" }

// POST /connections/:id/test
{ "success": true,  "message": "Connection successful", "latency_ms": 45 }
{ "success": false, "message": "ECONNREFUSED 10.0.0.5:5432" }

Databases are read through the engine adapter (PostgreSQL pg_database, MySQL information_schema.schemata, SQL Server sys.databases, MongoDB listDatabases, Oracle v$pdbs, Redis numbered databases), using the connection's settings including SSH tunnels.

Executions — /api/db-devops/executions

The server writes an execution record only when it runs SQL against a target (one per script run, including each script of a release run), opening a driver pool (pg / mysql2 / mssql / oracledb / mongodb) after decrypting the target password. Records cannot be created or changed through the API; only a rollback changes one.

A malformed or unknown execution id answers 404 on every /executions/:id route, and /executions/release-progress/:releaseId answers 404 for a release that does not exist. Both run routes take an optional mode of transactional, halt or continue; any other value is refused with 400 before anything runs. A run continues on the server whether or not the request that started it is still open: with "wait": false the run, release and rollback routes answer 202 as soon as the run has started, and a run that takes longer than a proxy or load balancer allows for one request still finishes and is recorded. Checks that refuse a run (400, 404, 409) answer before anything starts either way. A run naming a script or connection that does not exist is 404 and leaves no record.

POST /api/db-devops/executions/run

// body
{ "script_id": "uuid", "connection_id": "uuid", "database": "optional-override", "mode": "transactional | halt | continue", "wait": false }
// 202 with "wait": false: the record, status RUNNING, at once; read GET /executions/:id for its progress and outcome
{ "id": "uuid", "status": "RUNNING", "progress": null, "heartbeat_at": "2026-10-21T15:00:00Z" }
// otherwise the answer waits for the run. 200 when the run completes: its execution record, the engine output and the rollback captured before it ran ({ rollbackSql, irreversible, notes })
{ "id": "uuid", "status": "COMPLETED", "rows_affected": 1, "execution_time_ms": 45,
  "output": "1 statement(s) executed. 1 row(s) affected.",
  "executed_sql": "...", "sql_sha256": "...", "script_revision": 3, "target_database": "app",
  "rollback": { "rollbackSql": "DROP TABLE IF EXISTS \\"users\\";", "irreversible": [], "notes": [] } }
// 200 when the SQL fails: the record with status FAILED and the driver error
{ "id": "uuid", "status": "FAILED", "error_message": "relation \\"users\\" already exists" }
// 400 unknown mode, 404 unknown script or connection, 409 not approved or a dependency has not run

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

Each script's -- @check lines are evaluated before its SQL, and a failing check stops the release like any failed script. Each script runs against its own target_connection_id/target_database, falling back to the request's connection_id; the request's database applies only to scripts that run on that connection without a target database of their own. Each script's execution record names the connection and database it actually ran against. Aggregate status is FAILED if any script fails; the per-script results[] pinpoints where it stopped.

{ "release_id": "uuid", "connection_id": "uuid", "database": "optional", "wait": false }
// 202 with "wait": false, at once
{ "release_id": "uuid", "run_id": "uuid", "status": "RUNNING", "scripts_total": 2 }
// GET /executions/release-progress/:releaseId?since=<epoch ms> then gives each script's execution_id, status, started_at and progress, and run: { id, status, error_message, started_at, finished_at }
// otherwise the answer waits for the run. 200
{ "status": "COMPLETED", "results": [
    { "script_id": "abc", "script_name": "create-users-table", "execution_id": "uuid", "status": "COMPLETED", "execution_time_ms": 32 },
    { "script_id": "def", "script_name": "add-users-index",    "execution_id": "uuid", "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. A malformed or unknown request id answers 404 on every /approvals/:id route.

Environments and Groupings — /api/db-devops/environments, /api/db-devops/groupings

Every role can read both lists; creating, changing and deleting needs the Release Manager or Admin role. Names are unique regardless of case; an environment name is at most 50 characters and a grouping name at most 100. A refusal is { "error": "..." } with the reason.

A connection belongs to one environment. When PUT /environments/:id adds a grouping, every connection in that grouping moves into the environment, out of any other one; when it removes a grouping, the connections it brought in leave the environment unless another grouping still assigned there covers them. The response lists what changed:

{ "id": "env_...", "name": "Staging", "groupings": ["us-east"], ...,
  "referencesUpdated": 0,
  "moved":      [ { "id": "...", "name": "orders-east", "from": "Development" } ],
  "unassigned": [] }

Assigning a grouping that holds another database type, or one that mixes types, is 409: a script is sent to every target in an environment as written, so an environment holds one database type.

The same rule applies from the connection side. When a connection is created or saved (POST/PUT /api/connections, /api/connections/bulk, POST/PUT /api/db-devops/connections) with a grouping it was not in before, and that grouping is assigned to exactly one environment, the connection is saved in that environment. It is 409 (a failed row in bulk upload) when an environment the grouping is assigned to targets another database type, when the new groupings belong to two different environments, or when the same request places the connection in another environment. A grouping assigned to several environments only checks the database type.

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

Append-only, tamper-evident audit trail. Every mutating request is recorded by auditMiddleware with sensitive fields redacted, and every entry records the client address (see TRUST_PROXY). Reading needs an administrator, or a role that grants audit:read (Read and export the Audit Log), and an active license; other accounts get 403 Insufficient permissions. The purge is the only permitted deletion, and only an administrator can run it.

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, "brokenSeq": null, "total": 10432 }

Settings — /api/db-devops/settings

K/V store (approval policy, session and password rules, identity providers). Any signed-in user can read settings; only admins can change them. Values of secret settings (API keys, secret access keys, tokens, and credential headers) are write-only: reads and the save response return [REDACTED] in their place.

The Session & Security keys are checked on PUT and batch, and a value outside its range is refused with 400 naming the key and the range (a batch is refused whole): sessionTimeoutMinutes 1 to 1440, maxLoginAttempts 1 to 100, lockoutDurationMinutes 1 to 1440, minPasswordLength 12 to 200, and enforcePasswordComplexity and autoLogoutOnInactivity true or false.

Discovery — /api/db-devops/discovery

Fleet inventory: hosts, scans, discovered databases, and tags. A scan connects with the host's stored credentials and lists what the engine holds: databases on PostgreSQL, TimescaleDB, CockroachDB, MySQL, MariaDB, SQL Server and MongoDB (with their size where the engine reports it), schemas (users) on Oracle (the host needs its service_name), keyspaces on Cassandra, databases on Neo4j, logical databases with their key counts on Redis, and indices with their document count and size on Elasticsearch. A SQLite host is refused with 400, since SQLite has no server to scan. Host responses never include the stored password; password_set says whether one is stored. A scan that cannot connect answers 400 with the engine's reason and records it on the host. Reads are open to signed-in users; every change needs the Release Manager or Admin role (403 otherwise) and the Configuration section.

Metrics — /api/db-devops/metrics

GET /api/db-devops/metrics?days=30&tz=America/New_York returns the numbers behind the Metrics screen for one time window. Any active license, signed-in callers, any role. days is 7, 30 (the default), 90 or 365; anything else is 400 with code INVALID_METRICS_WINDOW. tz names the time zone the days are counted in (the Metrics screen sends the browser's); without it days follow the database server's date. A tz that is not a time zone name, or that the database does not know, is 400 with code INVALID_TIME_ZONE. 7, 30 and 90 days are counted per day; 365 is counted per month over the current month and the eleven before it. Every series has one entry per period, including periods with no activity. If the database cannot be read the answer is 500 with code METRICS_FAILED.

{ "window": { "days": 30, "bucket": "day", "since": "2026-09-08", "until": "2026-10-07", "timeZone": "America/New_York" },
  "deployment": { "runs": 10, "completed": 6, "failed": 2, "other": 2,
                  "successRate": 75, "avgDurationMs": 31,
                  "byStatus": [{ "name": "COMPLETED", "count": 6 }],
                  "series": [{ "period": "2026-10-07", "completed": 6, "failed": 2, "other": 2 }],
                  "byConnection": [{ "connection": "Orders", "runs": 7, "avgMs": 40 }],
                  "failureCauses": [{ "name": "relation \\"orders_old\\" does not exist", "count": 2 }] },
  "releases": { "total": 5, "created": 2, "signedOff": 1, "byStatus": [...], "series": [{ "period", "created", "signedOff" }], "largest": [{ "release", "scripts" }] },
  "approvals": { "requests": 6, "decided": 4, "rejected": 1, "rejectionRate": 25, "avgHoursToDecision": 2.5,
                 "approvers": 2, "byStatus": [...], "topApprovers": [{ "name", "count" }], "series": [{ "period", "requests", "rejected", "hours" }] },
  "scripts": { "total": 12, "created": 4, "contributors": 2, "byMode": [...], "topContributors": [...], "series": [{ "period", "count" }] },
  "system": { "users": 5, "activeUsers": 4, "connections": 5, "connectionsBySurface": [{ "name": "devops", "count": 3 }],
              "auditEvents": 10, "auditSeries": [{ "period", "count" }] } }

• successRate is completed runs over completed and failed runs, to one decimal; other counts runs that are pending, running or rolled back. successRate, avgDurationMs, rejectionRate and avgHoursToDecision are null when there is nothing to measure.
• avgDurationMs and byConnection[].avgMs average completed runs. Runs on a deleted connection are reported as "Deleted connection".
• failureCauses groups failed runs by the database's error message, without the runner's "Statement N failed" prefix, cut at 160 characters, up to ten.
• Runs are dated by when they started; scripts, releases and audit entries by when they were created; approvals by when they were requested. period is YYYY-MM-DD, or YYYY-MM for 365 days.
• total for releases and scripts, and everything under system except the audit counts, cover all time rather than the window.

Notifications — /api/notifications

Notifications always belong to the signed-in user; another user's notification, or a malformed id, is 404. Each notification carries the id of its item in metadata (scriptId, approvalId or releaseId) and an action_url of the form /?app=manager&view=scripts|approvals|releases&open=<id>.

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 (any active license)

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

GET /api/databases/:connectionId/object-definition

Reads the current definition of one object (the browser calls it as /api/object-definition?connectionId=…). Query parameters: objectType, objectName, and optionally schemaName and database (another database on the same instance). Names are sent to the database as values, never as SQL.

{ "definition": "CREATE OR REPLACE VIEW app.recent_orders AS ..." }
{ "definition": null, "message": "The view app.recent_orders does not exist in shop" }

// 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).