Application Databases¶

Name: mcp-data-platform
Author: Craig Johnston

An application database (kind: appdb) is an application's own PostgreSQL database: the system of record for an application built in the platform, such as an order system or a CRM. The scripts and endpoints the application is made of read and write it. Trino is the platform's analytical engine and holds no transaction, row lock or enforced constraint; an application database holds the rows an application commits.

An application database is not a data source. Its tables never appear in search or the catalog, and its data is reached by no agent tool. Agents reach remote software through API connections and connected data sources through Trino. An agent builds an application by writing its schema as migrations with manage_schema, the one tool this kind registers, and by writing managed scripts and endpoints, which reach the application database through platform.query, platform.execute, platform.transaction, platform.batch, platform.query_iter, platform.copy_in and platform.copy_out.

To report on application data, an operator adds a Trino catalog (the postgresql connector) over the same database. It is then a connected data source like any other.

Registering a connection¶

The server must be PostgreSQL 17 or later. A connection to an older server is refused when it is saved, when it is tested and on every physical connection its pool opens, with a message naming the version found and the floor, so a connection configured in YAML is held to it as one saved through the admin API is. A connection's test_connection is an application database too and is held to the same floor.

Register the database as a connection of kind appdb, in the portal under Admin > Connections (shown as "Application database") or through PUT /api/v1/admin/connection-instances/appdb/{name}. In YAML:

toolkits:
  appdb:
    enabled: true
    instances:
      orders:
        host: "db.internal.example.com"
        port: 5432
        database: "orders"
        user: "orders_app"
        password: "{{secret:orders-db-password}}"
        sslmode: "verify-full"
        tls_ca_bundle_pem: |
          -----BEGIN CERTIFICATE-----
          ...
        max_conns: 5
        statement_timeout: "30s"
        max_rows: 1000
        read_only: false
        statement_cache: true
        pool_wait_timeout: "5s"
        transaction_idle_timeout: "10s"
        schema_apply_personas: []
        schema_ledger: "mcp_schema"
        test_connection: ""          # this connection; its user needs CREATEDB
        persona_roles:               # a persona's statements log in as its own role
          sales: "orders_sales"
        persona_role_passwords:
          sales: "{{secret:orders-sales-password}}"
        description: "The order system"
    default: orders

Key	Default	Meaning
`host`	(required)	Server host name or address
`port`	`5432`	Server port
`database`	(required)	Database to connect to. The platform's own database is refused (below)
`user`	(required)	The database user every statement on this connection runs as
`password`		The user's password, or `{{secret:<name>}}` naming a stored secret whose `allow_connections` lists the connection. Encrypted at rest and never returned by a read
`sslmode`	`verify-full`	`disable`, `require` (encrypts, checks nothing), `verify-ca` (checks the chain, not the name) or `verify-full` (checks the chain and that the certificate names `host`)
`tls_ca_bundle_pem`		Certificates trusted beside the system roots
`mtls_client_cert_pem`, `mtls_client_key_pem`		A client certificate and its key. The key is encrypted at rest and never returned by a read
`max_conns`	`5`	Pool size on each replica, at most 100
`statement_timeout`	`30s`	Longest one statement may run, at most `10m`. A statement's `timeout_seconds` may only lower it
`max_rows`	`1000`	Most rows one result returns, at most 100000
`iter_max_rows`	`1000000`	Most rows one `query_iter` loop reads, at most 100000000. Past it the run fails naming the bound
`copy_max_rows`	`1000000`	Most rows one `copy_in` or `copy_out` call moves, at most 10000000
`copy_max_bytes`	`67108864`	Most bytes one `copy_in` or `copy_out` call moves (64 MiB), at most 1 GiB. A `copy_in` past this or `copy_max_rows` is refused before its first row is sent
`read_only`	`false`	Refuse `platform.execute` on this connection for every script
`statement_cache`	`true`	Keep each statement prepared on the physical connection that first ran it, so it is described once. Set `false` behind PgBouncer in transaction mode
`pool_wait_timeout`	`5s`	Longest a statement waits for a free pooled connection, at most `1m`. Past it the statement fails naming the pool and its `max_conns`
`transaction_idle_timeout`	`10s`	Longest a script's transaction may go without a statement, at most `10m`. Past it the transaction is rolled back
`schema_apply_personas`	`[]`	Personas that may apply and revert this connection's migrations. Empty means administrators only
`schema_ledger`	`mcp_schema`	Schema in this database that holds the ledger of applied migrations
`test_connection`	this connection	Application database connection whose server the throwaway databases of this connection's migration checks and script tests are created on. Its user needs `CREATEDB`
`persona_roles`	`{}`	Maps a persona to the database login role its statements run as. A persona not named runs as `user`. See Persona roles
`persona_role_passwords`	`{}`	Maps a persona named in `persona_roles` to its role's password, or `{{secret:<name>}}`. Encrypted at rest and never returned by a read, the config export, an audit row or a log

An application database cannot take a name a Trino connection holds, or the reverse: platform.query names its connection alone, and the save is refused with 409.

A script uses a connection only when the persona its run presents allows the connection in its connections: list. The check runs once per run, at the run's first statement on that connection. The driver is jackc/pgx/v5; the platform's own database stays on lib/pq.

Admin > Connections > Test opens a connection and reports the server version, the database and the user it reached, logs in as every persona role and names each (warning of one that is a superuser or has BYPASSRLS, which row-level security does not apply to), and reports whether a notification sent through the pool reaches a listening connection, which a webhook source with input postgres needs. Through PgBouncer in transaction mode it does not. Each connection's state (healthy, unreachable, auth_failed) is the one its statements last found; a persona role's failed login is reported under the connection's name.

From a script¶

platform.query and platform.execute take an application database's name as connection. Placeholders are :name, as for every connection; on an application database each is sent as a bound parameter and never written into the SQL text:

def main():
    rows = platform.query(
        "SELECT id, total FROM orders WHERE customer_id = :customer AND status = ANY(:statuses)",
        connection="orders",
        params={"customer": 42, "statuses": ["open", "paid"]},
    )["rows"]
    platform.execute(
        "UPDATE orders SET status = 'shipped' WHERE id = :id",
        connection="orders",
        params={"id": rows[0]["id"]},
    )

platform.query runs one statement in a READ ONLY transaction that is always rolled back. A statement that writes fails there.
platform.execute runs one statement and commits it in a transaction of its own.
A list is bound as one array parameter: write = ANY(:ids), not IN :ids.
Each value is read by the type PostgreSQL gives its parameter: a number or a string is parsed as that type (pass a numeric as a string to keep every digit), json and jsonb take any value, an array type takes a list, bytea takes base64, and None is NULL.
A dollar-quoted string ($$ ... $$), a string literal, a quoted identifier and a comment are left as written.

The statements do not cross the MCP server: the script host calls the connection's pool directly, so a request an endpoint answers pays no tool call, middleware pass or audit row per statement. In a draft run, platform.execute on an application database is a write and is refused unless the draft allows writes, like any other write. A recording keeps each statement and its answer, and a script test answers from the recording with the database stopped.

Transactions¶

platform.transaction(connection, fn, isolation="read_committed", read_only=False, retry=False, on_error="raise") opens one transaction, calls fn(tx), and commits when fn returns. It returns what fn returned. When fn fails, the transaction is rolled back and the failure is the call's. Inside fn, tx.query, tx.execute and tx.batch bind values as platform.query does and run in the transaction, as do tx.savepoint, tx.query_iter and tx.copy_in:

def main():
    order = {"id": 1001, "total": "59.70"}
    items = [{"sku": "A-1", "qty": 2}, {"sku": "B-7", "qty": 1}]

    def place(tx):
        tx.execute("INSERT INTO orders (id, total) VALUES (:id, :total)", params=order)
        tx.batch([
            {"sql": "INSERT INTO items (order_id, sku, qty) VALUES (:o, :s, :q)",
             "params": {"o": order["id"], "s": i["sku"], "q": i["qty"]}}
            for i in items
        ])
        return tx.query("SELECT count(*) AS n FROM items WHERE order_id = :o", params={"o": order["id"]})["rows"][0]["n"]

    platform.transaction("orders", place)

An order and its line items commit together or not at all: when a line item violates a constraint, nothing is left, and the run's error names the constraint.

isolation is read_committed, repeatable_read or serializable. read_only=True opens a READ ONLY transaction, the only kind a read_only connection opens.
The first statement that fails ends the transaction, as PostgreSQL does, unless it ran inside a savepoint. Every later call on tx fails saying so.
retry=True runs fn again when the transaction fails with a serialization failure (SQLSTATE 40001) or a deadlock (40P01), and on nothing else: three tries in all, with a short jittered pause between them. Because fn may run more than once, a retried fn reads and writes only through tx: platform.call, platform.query, platform.execute, platform.batch, platform.query_iter, platform.copy_in, platform.copy_out, platform.export, platform.publish_data, platform.notify, platform.publish, platform.save_state and platform.mint_token are refused inside it.
Transactions do not nest. While one is open, platform.query and platform.execute on its connection are refused, since they would run on another pooled connection and wait on the transaction's own locks.
A tx is used only inside fn.

The transaction holds one pooled connection for its life, and is never held across MCP calls or by an agent. It is rolled back when the run ends however it ends (finished, failed, timed out, shed by admission, drained at shutdown), when it goes transaction_idle_timeout without a statement, and when the replica closes its pools; the next call on it names the reason. An endpoint handler may open one, and it does not outlive the request.

Savepoints¶

tx.savepoint(fn, on_error="raise") calls fn(tx) under a savepoint the platform names (mcp_sp_<depth>). When fn returns the savepoint is released and its work kept; when fn fails the transaction is rolled back to the savepoint, so only fn's work is undone and the transaction goes on. It returns what fn returned.

def main():
    """Imports customers, skipping the ones whose email is already taken."""
    def fill(tx):
        """Inserts each customer under a savepoint of its own."""
        skipped = 0
        for c in run.params["customers"]:
            got = tx.savepoint(lambda t: t.execute(
                "INSERT INTO customers (email, name) VALUES (:email, :name)", params = c), on_error = "return")
            if got and got.get("error"):
                skipped += 1
        return skipped

    print(platform.transaction("orders", fill), "skipped")

A statement in fn that fails stops the savepoint even when fn took the failure with on_error="return" and went on: PostgreSQL runs nothing in it after a failure, so the platform rolls back to it and the failure is tx.savepoint's. With on_error="return", a failed statement's envelope is returned as for every call; fn's own fail(...) ends the run after the rollback.
Savepoints nest, and none is left open past the call that made it. The SQL statements SAVEPOINT, RELEASE and ROLLBACK TO are refused, so a script cannot release a savepoint the platform holds. Opening, releasing and rolling back each count as a call of the run and are kept in its recording, so a test replays them.
Each savepoint is a subtransaction. PostgreSQL caches 64 per session; past that every other session checking row visibility reads them from disk, and a transaction holding thousands slows the whole server. Keep savepoints for the steps that can fail; for a large import, INSERT ... ON CONFLICT DO NOTHING or a check first does the same work without one per row.

Results larger than max_rows¶

platform.query_iter(connection, sql, params={}, page_rows=0, timeout_seconds=0, on_error="raise") returns a query's rows to loop over, read in pages of page_rows (the connection's max_rows when 0, at most 100000) from a server-side cursor in a READ ONLY transaction of its own. tx.query_iter(sql, ...) reads from a cursor declared in the transaction. Values bind as for platform.query.

def main():
    """Totals a year of orders, however many there are."""
    total = 0
    for row in platform.query_iter("orders", "SELECT total FROM orders WHERE placed_at >= :since",
                                   params = {"since": "2026-01-01"}, page_rows = 5000):
        total += float(row["total"])
    print(total)

The run holds one page at a time, and each page is charged against the run's memory budget as it is read. A row the loop keeps is the script's to account for.
A loop reads at most the connection's iter_max_rows. Past it, when a page fails to read, or past the memory budget, the run fails naming the reason: Starlark has no way to raise inside a loop, and a loop that ended early would hand the script a partial result as if it were whole. A refused query (a syntax error, a parameter that does not fit) fails when query_iter is called, where on_error applies.
The cursor is closed when the loop ends, breaks or fails, and when the run ends; a cursor of its own takes its transaction with it. A result is read by one loop.
A loop body that spends longer than transaction_idle_timeout between pages loses the cursor's transaction to the server's idle bound.
For results a person pages through, such as an endpoint's list view, use keyset pagination in SQL: WHERE (placed_at, id) < (:last_at, :last_id) ORDER BY placed_at DESC, id DESC LIMIT 50 reads the same rows whatever page is asked for and holds nothing between requests, where OFFSET reads and discards every row before the page.

Copying rows in and out¶

platform.copy_in(connection, table, columns, rows, on_error="raise") writes rows into a table through PostgreSQL's COPY protocol, in a transaction of its own that commits, and returns the number written. tx.copy_in(table, columns, rows) writes them in the transaction, and they commit or roll back with it. table is table or schema.table; it and columns are quoted as identifiers. A row is a list in columns' order or a dict keyed by column, and each value is encoded by its column's type as a bound parameter is. The copy runs as the persona's role with the caller's claims set, so a table the role may not insert into fails with SQLSTATE 42501.

platform.copy_out(connection, name, sql, params={}, format="csv", timeout_seconds=0, on_error="raise") writes every row of a query as an output named name: CSV with a header, or COPY's text format (format="text", tab-separated with \N for null, stored as text/tab-separated-values). It takes platform.export's destination, key, register, tags, metadata, scope, scope_id and index, returns what platform.export returns, and its output is stored where an export's is, with the same access. The rows stream from the server to the output a row at a time and never become the script's values, so a result far past max_rows reaches a file. The query runs in a READ ONLY transaction through the extended protocol, since COPY (query) TO STDOUT takes no parameters; each value is the text PostgreSQL writes for it, as COPY writes it.

One call moves at most copy_max_rows rows and copy_max_bytes bytes, and a copy_out is also held to the 100 MB an output may be. A copy_in past either bound is refused before its first row is sent; a copy_out past one fails when it reaches it.
COPY written as SQL is refused in every statement a script runs: COPY ... FROM PROGRAM runs a program on the database server for a user allowed to, and these two bindings are how rows are copied.
A copy_in row's values reach no audit row, log line, span or metric label. Its recording keeps the table, the columns and the row count in place of the rows, and a test replays it by those. A copy_out's bytes go to the output alone; its recording keeps the row and byte counts.

Batches¶

platform.batch(connection, statements, on_error="raise") sends statements pipelined, in one round trip, in a transaction of their own that commits only when every one succeeds, and returns their results in order. tx.batch(statements) does the same inside a transaction. A statement is a SQL string or {"sql": ..., "params": {...}}, and none reads another's result. A batch is a write in a draft. A failure names the statement: statement 2 of the batch: ....

Statement cache and pool¶

With statement_cache on, each statement is prepared on the physical connection that first runs it and run as a prepared statement after that, which saves the round trip that describes it. Each physical connection keeps at most 256 statements, least recently used dropped first. When the platform applies a migration, every connection drops what it holds at its next use; a change made outside the platform makes the first call fail with SQLSTATE 0A000 and the next one prepare the statement again. Behind PgBouncer in transaction mode a session's statements land on different server connections, so set statement_cache: false there.

A statement that finds no free pooled connection within pool_wait_timeout fails naming the pool and its max_conns, rather than hanging the request.

Grants are the boundary¶

What a script can do is what the connection's database user may do. platform.query's READ ONLY transaction keeps a read from writing by mistake, but the boundary is the user's grants: give a connection meant for reading a user that can only read.

Session state does not outlive a call: each connection is reset as it returns to the pool, so a SET ROLE, a SET search_path, a LISTEN or a temporary table one statement leaves is gone before the next uses the connection. With statement_cache: false the reset is DISCARD ALL. With the cache on it is the sequence PostgreSQL documents DISCARD ALL as, less its DEALLOCATE ALL: CLOSE ALL, SET SESSION AUTHORIZATION DEFAULT, RESET ALL, UNLISTEN *, pg_advisory_unlock_all(), DISCARD PLANS, DISCARD TEMP and DISCARD SEQUENCES. The prepared statements it keeps are the platform's own.

Persona roles¶

The platform authorizes every run's use of a connection by persona first. A connection can also make the database a second boundary: persona_roles maps a persona to a login role, and every statement a run of that persona makes logs in as that role, through a pool of its own on each replica, sized max_conns and opened on first use. The database's own grants and row-level security then apply to it. A persona the map does not name runs as the connection's user.

A run presents the persona of the script version's author, as it does for every call, so a script saved by someone in sales runs as orders_sales. An endpoint request runs its script the same way: as the author's persona, never the caller's.

The roles log in directly; the platform never switches to one with SET ROLE. A session can RESET ROLE or SET ROLE to any role the role it logged in as is a member of, so a switched role is no boundary against the person writing the SQL. A role that logged in has nothing to reset to. Do not make a persona role a member of a role with more privileges, a superuser, or a role with BYPASSRLS; the connection test warns of the last two.

A script test's throwaway database keeps the persona roles when the test server is the connection's own server, where the roles exist, so a test runs each statement as the role it would run as. On another test server every statement runs as the test server's user.

Caller claims¶

Every transaction sets who it runs for as transaction-local settings, from bound values, before its first statement:

Setting	Value
`mcp.user_id`	An authenticated endpoint's caller: the user id the platform authenticated them as. Outside an endpoint, the script version's author's, which is empty until the platform has seen the author sign in
`mcp.email`	The same person's address
`mcp.persona`	The persona the statement runs as (the author's)
`mcp.source`	`endpoint` for an endpoint request, `script` for a run

On a public or bot-check endpoint mcp.user_id and mcp.email are empty. Every setting is set in every transaction, empty when absent, so a statement never reads a value an earlier caller's transaction on the same pooled connection set. A row-level security policy reads them with current_setting('mcp.user_id', true), and treats the empty string as no one:

ALTER TABLE orders ENABLE ROW LEVEL SECURITY;
CREATE POLICY own_orders ON orders TO orders_sales
  USING (customer_id = nullif(current_setting('mcp.user_id', true), ''));

What a claim is worth depends on who wrote the SQL. The author of a script can set any setting in their own statement, so a claim protects against the person who did not write the SQL: an endpoint's caller, a form's submitter. Against the person writing the SQL the boundary is their persona's login role.

Statements the platform refuses¶

BEGIN, COMMIT, ROLLBACK and PREPARE TRANSACTION are refused: the platform holds the transaction, and platform.transaction is how statements commit together. SAVEPOINT, RELEASE and ROLLBACK TO are refused naming tx.savepoint, and COPY naming copy_in and copy_out. DEALLOCATE and DISCARD are refused too. A string holding two statements is refused by the server, since the extended protocol carries one.

The platform's own database is never a target¶

A connection that reaches the platform's own database is refused. A script with platform.execute there could rewrite personas, grants and the audit log.

Comparing host, port and database name would be defeated by a DNS alias, an address in place of a name, or a pooler in front of the same server, so the check asks the database. The platform takes a random advisory lock on its own database, asks the new connection whether pg_locks shows that lock in the connection's own database, and releases it. The check runs when a connection is saved, if its database answers, and again on every physical connection a pool opens, so a connection whose host name is later pointed at the platform's database is refused at its first statement. A streaming replica does not show the primary's advisory locks; do not connect to a replica of the platform's database.

Results¶

res["columns"] lists each column's name and the type PostgreSQL gave it (integer, numeric, timestamp with time zone, text[]), and res["rows"] holds one dict per row in column order. Values map by type:

PostgreSQL	Script value
integers, `real`, `double precision`	number; an integer beyond 2^53 - 1 either way is a decimal string; `NaN` and infinities are their PostgreSQL spelling
`numeric`	decimal string, every digit kept
`timestamptz`	RFC 3339 in UTC
`timestamp`	`YYYY-MM-DDTHH:MM:SS[.ffffff]`
`date`	`YYYY-MM-DD`
`json`, `jsonb`	the decoded document
`uuid`	canonical string
arrays	lists, each element mapped by the element type
`bytea`	base64
a range (`int4range`, `tstzrange`, one a schema declares)	`{"lower", "upper", "lower_inc", "upper_inc", "empty"}`, each bound mapped by the range's element type, an unbounded one `null`
a multirange	a list of ranges
`interval`	`{"months", "days", "microseconds", "text"}`: the three parts PostgreSQL keeps, and the text it writes in its own `IntervalStyle`
a composite type	an object keyed by attribute name, each mapped by its type
`hstore`	an object of string to string or `null`
`vector` (pgvector)	a list of numbers
`inet`, `cidr`, an enum, `ltree`, `tsvector`, anything else	the text PostgreSQL writes for it; the column's type names the type, so an enum is told from text

The same shapes bind as parameters: a range object, a list of them for a multirange, an object of attributes for a composite (an attribute it does not name is null, one the type lacks is refused), an interval's parts or {"text": ...}, an object for an hstore, a list of numbers for a vector. A string is taken as the type's own literal. hstore and vector are read this way only as the base types their extensions define; an enum or a domain a schema happens to name vector is text.

A query returns at most the run's row cap and the connection's max_rows; a result cut at either fails the run, as for every query a script makes, since a partial result would silently change what it computes.

Errors¶

A statement PostgreSQL refuses fails with its SQLSTATE, condition name, and the constraint, table, column and type it named. That sentence is what the run's error, its audit record and the logs carry. The server's message, DETAIL and HINT can repeat a value (Key (email)=([email protected]) already exists), so they go to the script alone: with on_error="return" the failure is returned to the script, and its postgres field holds them.

Schema migrations¶

An application's tables are created and changed through versioned migrations the platform keeps, applies and reports, never by ad hoc DDL. A migration belongs to a connection and a schema (namespace, public by default): a version, a name, the SQL that applies it, optional SQL that reverts it, its author and a checksum. The platform keeps each migration and its history in its own database. The target database holds the ledger of what was applied in mcp_schema.migrations (the schema is schema_ledger): version, checksum, when, by whom and how long it took.

manage_schema takes a command, a connection (an application database) and an optional schema:

Command	What it does
`status`	What the ledger records as applied, what is pending, and any drift between the ledger and the platform's migrations
`add`	Adds a migration (`version`, `name`, `up`, optional `down`, `transactional`), or replaces one still pending. Its version must be above every applied version
`plan`	Runs the pending migrations (up to `to_version`) in a transaction, reports the relations and columns they change and the locks they take from `pg_locks`, and rolls back
`apply`	Applies the pending migrations (up to `to_version`)
`down`	Reverts the latest applied migration, or every one above `to_version`, with its down SQL
`history`	What happened to each migration: added, edited, applied, reverted, failed
`describe`	What the server is and what the applied migrations produced, read from the target's `pg_catalog`. This is how an agent reads its application's schema while writing scripts. See Describe

{"command": "add", "connection": "orders", "version": 1, "name": "orders",
 "up": "CREATE TABLE orders (id bigint PRIMARY KEY, total numeric(10,2) NOT NULL)",
 "down": "DROP TABLE orders"}

An applied migration is immutable. Adding an applied version again is refused, and its checksum is compared with the ledger on every apply and status: a mismatch stops the apply and names the version. A fix is a new migration.
apply holds an advisory lock in the target database for the whole apply, so two replicas or two callers never apply at once: the second waits up to 30 seconds and then finds nothing pending, or reports the lock. Each migration runs in a transaction of its own with lock_timeout (10 seconds) and statement_timeout (the connection's, or timeout_seconds) set and search_path set to its schema, and writes its ledger row in the same transaction. A migration that fails partway leaves no change and no ledger row, and stops the apply.
A statement that cannot run in a transaction (CREATE INDEX CONCURRENTLY) is a migration of its own with transactional: false. It runs alone and is not planned.
Adding and planning need the connection in the caller's persona and a connection that is not read_only. Applying and reverting need the admin persona or a persona the connection's schema_apply_personas names.
The portal's connection page (Admin > Connections, an application database) lists the migrations, which are applied, the plan of the pending ones, and the history.

A migration's SQL is kept in the platform's database and in the target's ledger checksum. Do not write a secret into a migration.

Describe¶

describe returns, for the migration's schema:

server: the server's major (17, 18) and full version.
extensions: the extensions installed in the database, with their versions and schemas, and available_extensions: the ones the server offers that the database has not installed, with their default versions.
tables: each table, partitioned table, view and materialized view, with its columns (type, nullability, default, comment), constraints (primary key, unique, foreign key, check, exclusion), indexes, row_security and force_row_security, its policies (command, roles, permissive or restrictive, using, with_check), its triggers (timing, events, row or statement level, when, the function, enabled state, definition), a partitioned table's partition_key and partitions with their bounds, and a view's view (definition, security_invoker, security_barrier, check option). Nullability is reported per column on every major: PostgreSQL 18 also records NOT NULL as a constraint, and describe leaves those out of constraints so its answer is the same on 17 and 18.
functions: each function and procedure, with its arguments, return type, language, volatility, security_definer, the search_path it pins, and its body.
enums with their values in order, and domains with their base type, nullability, default and checks.

An object an extension created is left out, since no migration wrote it. An empty kind is []. Function bodies, view definitions and policy expressions are returned to the caller and are not logged, traced, audited or indexed; they are the database's text, so a literal a migration wrote into one is returned as written.

Review before a migration saves¶

add reviews a migration before it saves it (#2405), the way a script's source passes its save gate:

Rule	Statement	Severity	Safe form
`create_index_locks`	`CREATE INDEX` without `CONCURRENTLY` on a table an earlier migration created	warning	`CREATE INDEX CONCURRENTLY` in a migration of its own with `transactional: false`
`not_null_without_default`	`ADD COLUMN ... NOT NULL` with no default	warning	A constant `DEFAULT`, or nullable, backfilled, then `SET NOT NULL`
`volatile_default_rewrites`	`ADD COLUMN ... DEFAULT` a value computed per row (`random()`, `clock_timestamp()`, `gen_random_uuid()`, `nextval()`)	warning	No default or a constant one, then a batched backfill
`type_change_rewrites`	`ALTER COLUMN ... TYPE`	warning	A new column, backfilled, reads moved to it
`constraint_validates`	`ADD CONSTRAINT ... FOREIGN KEY` or `CHECK` without `NOT VALID`	warning	`NOT VALID`, then `VALIDATE CONSTRAINT` in a later migration
`lock_timeout_raised`	`SET lock_timeout` above the platform's 10 seconds, or 0	warning	Leave it, and retry an apply that times out
`rename_referenced`	`RENAME` of a table or column a saved script names in a string (its SQL, or a row key) beside the connection's name	warning	The new name beside the old, the scripts moved, the old removed later
`drop_table`, `drop_column`, `truncate`, `unbounded_delete`, `unbounded_update`	`DROP TABLE`, `DROP COLUMN`, `TRUNCATE`, a `DELETE` or `UPDATE` with no `WHERE`	destructive

The review also reads each migration for statements that leave the database open to a role that should not reach it, and for features the server's major does not have (#2449). The major, the schema's tables with row-level security and its virtual generated columns are read from the connection at add; when they cannot be read the migration is reviewed without the rules that need them and add returns a target_unread warning saying so.

Rule	Statement	Severity	Safe form
`security_definer_search_path`	A `SECURITY DEFINER` function or procedure without `SET search_path` ending in `pg_temp` (`FROM CURRENT` is not taken as pinned)	warning	`SET search_path = <its schemas>, pg_temp` on the function
`security_definer_public_execute`	A `SECURITY DEFINER` function with no `REVOKE ... FROM PUBLIC` covering it in the same migration (on the function, on `ALL FUNCTIONS IN SCHEMA`, or `ALTER DEFAULT PRIVILEGES` before it)	warning	`REVOKE EXECUTE ON FUNCTION <name> FROM PUBLIC`, then `GRANT` it to the roles that call it
`row_security_not_forced`	`ENABLE ROW LEVEL SECURITY` without `FORCE ROW LEVEL SECURITY` on the same table in the same migration: the owner, which migrations and connections without a persona role run as, passes every policy	warning	`FORCE ROW LEVEL SECURITY` in the same migration
`view_bypasses_row_security`	A view reading a table with row-level security (enabled on the server or in the migration) without `security_invoker = true`: the view reads as its owner	warning	`CREATE VIEW ... WITH (security_invoker = true)`, or `ALTER VIEW ... SET (security_invoker = true)` in the same migration
`index_on_virtual_column`	On PostgreSQL 18, an index on a generated column declared without `STORED`, which 18 makes virtual and cannot index	warning	`GENERATED ALWAYS AS (...) STORED`
`feature_needs_newer_server`	On PostgreSQL 17, a feature 18 introduced: `uuidv7()` or `uuidv4()`, `RETURNING old`/`new`, a virtual generated column, `WITHOUT OVERLAPS`, `NOT ENFORCED`	warning	The finding names the form 17 has
`copy_program`	`COPY ... FROM PROGRAM` or `TO PROGRAM`	refused
`untrusted_code`	`CREATE LANGUAGE`, `CREATE EXTENSION` of an untrusted language, or a function, procedure or `DO` block in one (`plpython3u`, `plperlu`, `pltclu`, `C`, `internal`)	refused
`event_trigger`	`CREATE EVENT TRIGGER`	refused
`alter_system`	`ALTER SYSTEM`	refused

A refused statement stops the save and the refusal names each one: they need privileges an application's role must not hold, or reach outside the database. On a 17 server a statement using an 18 feature, and on 18 an index on a virtual generated column, also fail the apply from zero below; that refusal names what the review found.

A table the same migration creates is not a live table, and no rule reads a statement on it. Every finding names the statement, the object, what it does to a live table and the safe form, and is returned in add's findings and recorded in the migration's history. A warning does not stop the save. A destructive finding does: the migration saves only with change_summary (what it removes, in plain words) and user_agreed: true once the person the application is built for agreed, and the summary and who agreed are in its history.

Then every migration of the schema, the new one included, is applied from zero on a throwaway database on the connection's test server. A set that does not apply is refused naming the migration and the error. A test server whose user lacks CREATEDB cannot run this check: the migration saves with a from_zero_unchecked warning naming the privilege. The database built is kept as the schema's template, so the next check or script test that needs the same migrations starts from it.

The outbox template¶

manage_schema add with template: "outbox" writes a migration the platform provides in place of up and down: an outbox table in the migration's schema (outbox_table, default outbox), a trigger function that writes each inserted, updated or deleted row of a captured table to it as JSON and notifies the channel named <schema>.<table>, and a trigger on each table in capture. With notify: false the trigger function writes the row and sends no notification: a transaction that notifies takes a lock at commit that every other notifying commit waits on, which serializes a table many sessions write at once, and the source's poll every poll_seconds then delivers the rows on its own. It is reviewed, applied and reverted like any other migration. A webhook source with input postgres in mode outbox then files each row as an event.

{"command": "add", "connection": "orders", "version": 4, "template": "outbox", "capture": ["orders", "order_items"]}

Column	Holds
`id`	The row's place, `bigint` identity
`source_table`	The captured table, as `schema.table`
`op`	`INSERT`, `UPDATE` or `DELETE`
`payload`	The new row, or the old one for a `DELETE`, as `jsonb`
`created_at`	When the transaction that made the change began

Script tests against a throwaway database¶

A script's test can run against a real database built from the connection's migrations (#2407) rather than answering its statements from a recording, so constraints, triggers, row-level security and concurrency are exercised:

def test_a_duplicate_email_is_refused():
    testing.database("orders", schemas = ["app"])
    platform.execute("INSERT INTO app.users (email) VALUES (:e)", connection = "orders", params = {"e": "[email protected]"})
    msg = assert.fails(lambda: platform.execute("INSERT INTO app.users (email) VALUES (:e)",
                                                connection = "orders", params = {"e": "[email protected]"}))
    assert.contains(msg, "23505")

testing.database(connection, schemas=None) builds the database from the connection's migrations in the named schemas, or every schema, pending migrations included. For each set of migrations the platform builds a template once, under an advisory lock on the test server, and clones each test's database from it with CREATE DATABASE ... TEMPLATE, so a test starts in well under a second.
After the call, every statement, transaction and batch the test makes on that connection runs on the clone; a statement on another application database the test did not open is refused. Fixtures are written with platform.execute. Nothing reaches the connection's own database.
The clone is dropped when the test ends; an altered-rows rerun starts from a clone of its own. Coverage and the altered-rows check work as for a recorded test.
The databases are created on the server of the connection's test_connection, the connection itself by default, with that connection's credentials and this connection's limits. A test server whose user lacks CREATEDB fails the test naming the privilege and the user. A test runs only when its run is allowed both the connection and its test_connection.
The test_connection's user runs the migrations add checks and the statements tests make for every connection that names it. Give it CREATEDB on a server that holds nothing else, and no other privilege.
They are named mcpt_<hash>_<head> (a template, kept while it is the newest for its migrations), mcpb_<unix seconds>_<random> (one being built) and mcpc_<unix seconds>_<random> (a test's clone). Each replica runs a sweep, one at a time under an advisory lock on the platform's database, that drops builds and clones its test server's user owns older than MCP_PLATFORM_APPDB_TEST_MAX_AGE (default 1h) every MCP_PLATFORM_APPDB_TEST_SWEEP_INTERVAL (default 10m), and once at start: the ones a run that died never dropped. No other database is ever dropped. DROP DATABASE ... WITH (FORCE) needs PostgreSQL 13 or later.

What is recorded¶

Sink	What it keeps
Audit	The run's own `script_run` event; no row per statement
Spans	`postgres.<operation>` per statement with the operation, a table summary, the database and the connection; the SQL text only under `OTEL_TRACES_INCLUDE_DB_STATEMENT=true`; a failure's SQLSTATE, never its message
Metrics	`postgres_statements_total{connection,operation,status}`, `postgres_statement_duration_seconds{connection,operation}`, `postgres_slow_statements_total{connection,operation}`; `status` is `ok`, `canceled`, `error` or the SQLSTATE class (`class_23`). `appdb_statement_cache_total{connection,result}` (hit, miss), `appdb_transactions_total{connection,outcome}` (committed, rolled_back, retried, idle_timeout), `appdb_pool_wait_timeouts_total{connection}`, and per replica `appdb_pool_connections{connection,state}` (acquired, idle, max), `appdb_pool_acquires_total{connection,wait}` and `appdb_pool_wait_seconds_total{connection}`
Recordings	Each statement in or out of a transaction, its bound values and its answer, and whether a transaction committed, which replay and script tests need. A `copy_in`'s rows are kept as their count and a `copy_out`'s bytes not at all. With an encryption key configured, every recording is sealed whole, bound to its run, before it is stored; without one it is stored gzipped
Migrations	`manage_schema` calls are audited like any tool call. A migration's SQL and history are kept in the platform's database

A bound value reaches no audit row, log line, metric label or span. A value a script decrypts and binds reaches the run's recording, which is sealed when the deployment holds an encryption key.