> ## Documentation Index
> Fetch the complete documentation index at: https://docs.polpo.sh/llms.txt
> Use this file to discover all available pages before exploring further.

# Databases

> Typed application records shared by agents and application backends

Data stores structured records alongside file [Volumes](/docs/platform/storage).
A project can have multiple named databases, each containing tables with a
versioned schema and explicit access grants. The Schema selector is before
Environment; choose a table in the side navigation to browse its records.

Each database is a logical resource containing tables. The PostgreSQL provider
implements it as a dedicated schema within the configured application database.
The HTTP `/data`, SDK `data()` and custom-tool `ctx.data` interfaces use the generic
Data namespace; agent tools use `database_*`.

<Note>
  Data is opt-in. Your project must have Data enabled before its API and dashboard
  are available. Disabling Data preserves its contents.
</Note>

## Create a database

Open **Databases** in the project dashboard, select **Live** or **Test**, then choose
**Create database**. Define the tables with a schema such as:

```json theme={null}
{
  "tables": {
    "contacts": {
      "columns": {
        "name": { "type": "text" },
        "email": { "type": "text", "unique": true },
        "notes": { "type": "text", "nullable": true }
      }
    }
  }
}
```

Supported column types are `text`, `integer`, `number`, `boolean`, `timestamp`,
`uuid` and `json`. Every row also has `_id`, `_version`, `_created_at` and
`_updated_at`. Tables can have indexes and foreign keys within the same database.

Databases have immutable UUIDs. Renaming a database preserves its records and
grants. **Edit schema** supports adding tables, nullable columns and indexes.
Use **Migrations** for supported SQL schema changes and backfills. The additive
**Edit schema** API preserves existing columns; destructive SQL changes require
explicit intent.

## Connect an application

Use your normal Polpo API key from **API Keys**. It provides full Data access
within its existing organization/project scope, including schema administration.
Keep the key in your application backend. The application authenticates its users
and enforces their permissions
before calling Polpo. Data does not provide built-in application users or
per-user row security.

```ts theme={null}
import { PolpoClient } from "@polpo-ai/sdk";

// Runs on your application server.
const polpo = new PolpoClient({
  baseUrl: "https://YOUR_PROJECT.polpo.cloud",
  apiKey: process.env.POLPO_API_KEY,
});
const contacts = polpo.data("RESOURCE_UUID").table("contacts");
const contact = await contacts.insert(
  { name: "Mario", email: "mario@example.com" },
  { idempotencyKey: "contact-import-42" },
);
const page = await contacts.list({ limit: 25 });
await contacts.update(contact._id, { name: "Maria" }, contact._version);
```

The same key calls the other Polpo APIs allowed by its project scope. Neon
credentials remain managed by Polpo. Manage and revoke keys from **API Keys**.
Live and Test keys access separate databases and grants.

## Grant an agent access

In the database's **Access** tab, grant an agent read-only or read/write access,
optionally restricted to tables. Select agents from the project catalog, or manage
an agent's access from its **Databases** tab. These grants are independent of the
application's Polpo API key. Enable `database_*` in its tool policy. Available
tools include `database_list`, `database_describe`, `database_read`, `database_insert`,
`database_update`, `database_delete`, `database_upsert`, `database_transaction`
and `database_query`.

`database_read` queries one table with typed filters, ordering and pagination.
`database_transaction` executes a batch of record operations in one database:
all commit or all roll back. `database_query` runs scoped SQL, including joins and
aggregates, with a default of 20 returned rows and maximum of 50. Mutations require
explicit write mode and write grants.
`database_delete` deletes a record, not the database.

For example, read the latest 20 paid orders with `database_read`:

```json theme={null}
{
  "resource": "shop",
  "table": "orders",
  "filter": { "status": { "eq": "paid" } },
  "orderBy": [{ "column": "_created_at", "direction": "desc" }],
  "limit": 20
}
```

Custom tools receive the same scope through `ctx.data`:

```ts theme={null}
const [page] = await ctx.data.execute("RESOURCE_UUID", {
  operations: [{ op: "list", table: "contacts", limit: 25 }],
});
```

Grant revocation applies to subsequent operations, including cached agents and
sandbox custom tools. Deleting an agent revokes its Data grants and capabilities
in both Live and Test. Recreating an agent with the same name does not restore
old capabilities. Operations already authorized and executing are not cancelled
retroactively. Durable project tasks and schedules use **Live** grants.
Request-scoped agent execution uses the authenticated request environment.
Sandbox capabilities expire within 31 minutes; a longer task must start a new
run to obtain renewed Data access.

## Concurrency and limits

Updates and deletes require the expected row version. Refresh and resolve a
conflict if another caller changed the row. Atomic batches execute through
`polpo.data(id).transaction(operations, { idempotencyKey })`. Reusing a key with
an identical request returns the original result; changing its body is rejected.
The key is scoped to the caller and resource.

Requests are limited to 256 KiB, batches to 100 operations, and transaction
results to 1 MiB. Oversized results roll back the batch. Reads return at most 200
rows per page; bounded offset pagination does not provide a snapshot across
concurrent writes. Use the returned `nextOffset` to continue, up to offset 10,000.

## SQL queries and mutations

`data.query()` executes one parameterized statement within one logical database.
The PostgreSQL provider supports `SELECT` with joins, aggregates, subqueries,
`UNION` and `VALUES`; `INSERT`, `UPDATE`, `DELETE` and `ON CONFLICT` require
explicit `mode: "write"`. Every referenced table must be granted for its operation.
Use unqualified logical table names and `$1`, `$2`, … parameters for values.

```ts theme={null}
const database = polpo.data("crm");
const counts = await database.query({
  sql: "SELECT count(*) AS total FROM contacts WHERE name = $1",
  params: ["Maria"],
});
await database.query({
  sql: "UPDATE contacts SET name = $1 WHERE _id = $2 AND _version = $3 RETURNING *",
  params: ["Mario", contact._id, contact._version],
  mode: "write",
  idempotencyKey: "rename-customer-42",
});
```

SQL writes generate IDs and advance `_version` and `_updated_at` automatically.
Unlike typed updates, SQL requires the caller to include a version predicate when
optimistic concurrency is needed; zero affected rows means the predicate did not
match. The result contains `rows`, `rowCount` and `truncated`. Read `rowCount` is
the returned row count, not a full-table count. Write `rowCount` includes all
affected rows even when the returned page is truncated. `maxRows` defaults to 200
and cannot exceed 200. Result-size failures roll back mutations. Text columns
hold at most 64 KiB of UTF-8 text; numbers and timestamps must be finite.

This is a bounded PostgreSQL subset, not an unrestricted PostgreSQL connection.
Cross-database/schema access, catalogs, role/session changes, procedural SQL,
CTEs, table functions, arbitrary functions and server extensions are rejected.
Only supported column types and an explicit function allowlist are accepted.
Provider capabilities declare the SQL dialect and migration support; a provider
without SQL returns `data_invalid`. Use typed operations for portable record access.

## SQL migrations

Administrators can apply ordered DDL and data backfills atomically:

```ts theme={null}
const current = await database.describe();
await database.migrateSql({
  id: "customer_source",
  expectedVersion: current.schemaVersion,
  statements: [
    { sql: "ALTER TABLE contacts ADD COLUMN source text" },
    { sql: "UPDATE contacts SET source = $1", params: ["import"] },
    { sql: "ALTER TABLE contacts ALTER COLUMN source SET NOT NULL" },
    { sql: "CREATE INDEX customer_source ON contacts(source)" },
  ],
});
const history = await database.migrations();
```

Each item contains exactly one statement. Supported DDL is `CREATE TABLE`,
`ALTER TABLE` (add/drop/rename column, rename table, set/drop not-null, supported
type changes) and ordinary `CREATE/DROP INDEX` and `DROP TABLE`. Column constraints
support `NOT NULL`, `UNIQUE` and same-database `REFERENCES`; Polpo adds system columns.
Defaults, triggers, custom types and arbitrary ORM migration SQL are not supported.
Drops and type changes require `allowDestructive: true`; cascading drops are rejected.
A failure rolls back schema, backfills, catalog version and migration history.

A migration ID is immutable: retrying an identical request returns its original
result, while changing a recorded request is a conflict. History includes ID,
checksum, applied schema version and timestamp. Keep the original expectedVersion
when retrying the same migration. Each new migration uses the current version.
Migrations require `manage`; agent tools and `ctx.data` do not expose administration.

## CLI and HTTP

```bash theme={null}
polpo data list
polpo data describe crm
polpo data create resource.json
polpo data execute crm operations.json
polpo data query crm query.json
polpo data migrate-sql crm migration.json
polpo data migrations crm
```

`resource.json` contains `{ "name": "crm", "schema": { ... } }`.
`operations.json` contains `{ "operations": [ ... ], "idempotencyKey": "..." }`.
`query.json` contains `{sql,params?,mode?,maxRows?,idempotencyKey?}`;
`migration.json` contains the migration object shown above. The CLI also supports
additive `migrate`, `rename` and version-checked `delete` with explicit
confirmation.

The CLI uses your authenticated Polpo session and selected project. To use a normal
Polpo API key directly, set `POLPO_API_KEY` and pass `--url` with the API origin
and `--project` with the project UUID. The key selects Live or Test; there is no
separate CLI environment override. For the standard self-hosted server use
`--url http://localhost:3890/api`.

| Method | Endpoint |
| - | - |
| GET / POST | `/v1/data` |
| GET / PATCH / DELETE | `/v1/data/:resource` |
| PUT | `/v1/data/:resource/schema` |
| POST | `/v1/data/:resource/transactions` |
| POST | `/v1/data/:resource/query` |
| GET / POST | `/v1/data/:resource/migrations` |

Cloud also provides project administration routes:

| Method | Path | Purpose |
| - | - | - |
| GET | `/v1/data/_backend` | Managed backend state for the selected environment. |
| GET | `/v1/data/_grants` | Current agent grants. |
| PUT | `/v1/data/_grants/:agent` | Replace one agent's grants with `{ "grants": [...] }`. |

These routes require project administrator access. Agent grants reference immutable
resource UUIDs and may restrict tables. Live and Test grants are separate; the
dashboard selects its environment while API keys use their bound environment.

Schema and database administration requires administrator access. Record tools
cannot create, migrate or delete databases. API errors have stable `data_*`
codes and never expose SQL or provider credentials.

To create a database through the normal API, send a body containing `name` and
`schema` to `POST /v1/data` using administrator credentials. The schema uses the
same `tables` definition shown above.
The SDK equivalent is `polpo.createData({ name, schema })`. This administration
capability is available through HTTP, SDK, CLI and the dashboard. Agent tools and
custom-tool `ctx.data` expose record access according to their agent grants.
Normal project API keys also support schema administration. Dashboard members
can read/write records and run record SQL; owner/admin accounts manage schemas,
migrations and agent grants.

## MCP and builder

The remote MCP and the in-product builder expose `polpo_databases_*` tools for
list/get/create/rename/delete, schema evolution, transactions, SQL query/mutate,
SQL migrations/history, backend status and agent grants. They use the authenticated
Polpo account with an explicit `projectId` and optional `environment` (`live` by
default). `polpo_databases_query` is read-only; SQL writes use
`polpo_databases_mutate`. Existing MCP OAuth read/write scopes still apply.
These account-management tools are distinct from the agents' `database_*` tools.
See the [MCP tool catalog](/developers/frameworks/mcp#databases) for all 14 tool
names and their inputs. The selected project must have Data enabled; account
membership and administrative roles are checked for each operation.

## Agent directories

An agent enables `database_*` in `.polpo/agents/<name>/agent.json`. Databases are
project resources, not files inside an agent directory. There is no `data` field
or `data/` subdirectory. Grants stay in Cloud administration. `polpo deploy` and
`polpo pull` do not apply migrations or copy records and grants; use explicit
API/SDK/CLI migration steps. Application user management remains your application's
responsibility.

## Hosting

The interface is provider-independent. OSS self-hosting currently uses a separate
PostgreSQL application database; managed Polpo provisions it on Neon. Logical
databases have separate schemas and transaction roles. Managed resources sharing
a Neon branch also share compute and the branch's backup/restore lifecycle;
independent per-resource restore is not provided.

The OSS dashboard includes **Databases** at `/data`: a Schema selector, table
navigation, record editing, SQL queries/mutations and migrations/history. It uses
the configured runtime backend. Live/Test selection, Neon provisioning controls
and the agent grant editor belong to the managed host; self-hosted grants remain
runtime configuration.


This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.