Schema Ops API

Schema snapshots, export, comparison, drift detection, linting and pre-flight checks. Base path /api/db-devops, needs an active license, signed-in callers. Reading, exporting, comparing and linting are open to any role; capturing or deleting snapshots, scanning for drift, acknowledging alerts and running pre-flight checks need a Release Manager or admin (403 otherwise). Every route that names a connection reads that connection's engine and database from the connection itself; a request may still send dialect, but one that does not match the connection is 400 (DIALECT_MISMATCH). Snapshots, export and comparison support PostgreSQL (and CockroachDB, TimescaleDB), MySQL/MariaDB, SQLite and SQL Server, with sampled approximations for MongoDB, Neo4j and Redis; any other engine is 400 (UNSUPPORTED_DIALECT).

Snapshots and export

MethodEndpointDescription
POST/schema/snapshotCapture the live schema of a connection (Release Manager or admin): { "connectionId", "schema"?, "label"?, "baseline"? } (label up to 255 characters). schema limits the snapshot to one schema (on MySQL/MariaDB, one database); one that does not exist is 404 (SCHEMA_NOT_FOUND). A connection's first snapshot becomes its drift baseline unless baseline is false; baseline: true makes any capture the baseline. Answers 201 with { id, createdAt, isBaseline, resolvedAlerts, snapshot }
GET/schema/enginesThe engines each action supports: { snapshot, export, checks, seed }, each a list of connection types (seed is the engines seed plans support)
POST/schema/liveRead the live schema of { "connectionId", "schema"? } without saving it (any role). Answers { snapshot }; the Schema ERD uses it
GET/schema/snapshots?connectionId=A connection's snapshots, newest first, each with is_baseline and the schema_name it was limited to (without the schema body)
POST/schema/snapshots/:id/baselineMake a snapshot its connection's drift baseline (Release Manager or admin). Every unresolved alert of the connection is resolved as rebaselined. Answers { id, connectionId, isBaseline, resolvedAlerts }
GET/schema/snapshots/:idOne snapshot with its schema
DELETE/schema/snapshots/:idDelete a snapshot (Release Manager or admin). Deleting the baseline leaves the connection without one and resolves its unresolved alerts as baseline_deleted. Answers { success, wasBaseline, resolvedAlerts }
POST/schema/exportRender DDL from { "snapshotId" } or from the live schema of { "connectionId", "schema"? }. Answers { dialect, capturedAt, ddl, extension }, where extension is the script format (sql, js, cql, cypher or json); export is available for every engine but Redis, which is 400 (UNSUPPORTED_DIALECT) saying it stores no schema. On PostgreSQL the DDL creates, in order, schemas, enum types, sequences, tables (identity columns as GENERATED ... AS IDENTITY, enum columns with their schema), sequence ownership, foreign keys, indexes and views (each after the views it reads), so it recreates the schema in an empty database

Comparison and plans

MethodEndpointDescription
POST/schema/diffCompare fromSnapshotId with toSnapshotId, or with the live schema of toConnectionId. Answers { operations, sql, extension, fromDialect, toDialect }; sql and extension are null on Redis
POST/schema/planThe SQL that would bring fromConnectionId to the schema in desiredSnapshotId. Answers { operations, sql, extension, fromDialect, desiredDialect }

Both sides must come from the same database engine; comparing, for example, a MySQL snapshot with a PostgreSQL connection is 400 (DIALECT_MISMATCH). A snapshot limited to one schema is compared with the same schema of the live connection, and a schema the connection does not have counts as empty, so a plan for it creates the schema. On PostgreSQL, plans also create and drop enum types and sequences, add appended enum values, and flag removed or reordered enum values for a hand-written change. Snapshots taken before enum types and sequences were recorded are compared without them, and a view an older snapshot also listed as a table is compared as a view only. Each operation has a kind (for example add_column, drop_table), the object it touches and a severity of info (additive), warning (changes something) or error (destructive).

Drift

MethodEndpointDescription
POST/schema/driftCompare a connection's live schema with its baseline (Release Manager or admin): { "connectionId" }. Each difference without an unresolved alert becomes an open alert; each unresolved alert whose difference is gone is resolved as gone. Answers { baselineId, operations, newAlerts, resolvedAlerts }
GET/schema/drift/:connectionIdA connection's alerts, newest first, with acknowledged_by/acknowledged_at and resolved_at/resolved_by/resolution. ?status=open|acknowledged|resolved filters by state; ?acknowledged=true|false by the acknowledged flag alone
POST/schema/drift/:alertId/ackAcknowledge an unresolved alert (Release Manager or admin). Acknowledging again keeps the first acknowledgement and answers alreadyAcknowledged: true; a resolved alert is 409 (ALERT_RESOLVED)

An alert is open until it is acknowledged or resolved; an acknowledged alert stays unresolved until its difference is gone or the baseline changes. A rescan never records a second alert for a difference that already has an unresolved one. A connection without a baseline is 400 (NO_BASELINE). baselineId may still be sent; one that does not exist is 404 (BASELINE_NOT_FOUND), one from another connection 400 (BASELINE_OTHER_CONNECTION), and any snapshot that is not the connection's baseline 409 (NOT_BASELINE).

Lint and pre-flight checks

MethodEndpointDescription
POST/lintLint SQL: { "sql", "dialect" }. dialect is one of postgresql, mysql, mariadb, mssql, sqlserver, oracle, sqlite, cockroachdb, timescaledb, clickhouse, redshift, aurora_postgresql, aurora_mysql, yugabytedb, tidb, supabase, planetscale, cassandra, mongodb, redis, neo4j, elasticsearch; MongoDB, Redis, Neo4j and Elasticsearch scripts are read in their own language and checked against their own rules. Answers { findings, scriptHash, dialect }, each finding with ruleId, severity, message, lineNumber and remediation. Comments, string literals, quoted identifiers and dollar-quoted bodies are not matched, and the last statement is checked whether or not it ends with a semicolon. A rule that cannot check the script is reported as a lint.rule_failed warning
GET/lint/rulesThe rule catalog and the accepted dialects: { rules: [{ id, severity, dialects, description }], dialects }
POST/preflightEvaluate the -- @check <label>: <boolean expression> lines of { "sql" } against { "connectionId", "database"? } (Release Manager or admin), in order, read-only, stopping at the first that fails. Answers { checksFound, allPassed, results }; a check containing a semicolon is 400 (INVALID_CHECK). Supported on PostgreSQL and compatible engines, MySQL/MariaDB and compatible engines, SQLite and SQL Server; other engines are 400 (UNSUPPORTED_DIALECT)

Errors

Errors answer { "error": "...", "code": "..." }.

Previous
← Compliance Rules API
Next
Seed Data API →