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

# Database

> The managed database of a fullstack app as its own primitive: SQL, the table catalogue, a REST passthrough over every table, and a migration ledger.

Every `fullstack` [app](https://vetta.sh/docs/api/apps.md) comes with a managed Postgres database. These routes reach it
directly — no connection string, no client library — so an agent, the SDK or the CLI can work with
data the way they work with any other resource.

All routes live under one app: `/v1/apps/{id}/db/…`. `{id}` is an `app_` id, an app **name**, or the
word **`default`**, which resolves the organization's only `fullstack` app — the standalone feeling
for the common case of one app. With two or more `fullstack` apps `default` answers
`409 ambiguous_app`, with the candidate ids in the message; with none, `404 not_found`. Every answer
carries the resolved app in a `vetta-app-id` response header: a script that creates a second app
later pins the one it meant by passing that id instead of `default`.

The database exists only once the app is `active`: a `frontend_only` app answers
`501 feature_not_configured`, and a still-provisioning backend answers a retryable `job_not_ready`.

## Run SQL

`POST /v1/apps/{id}/db/query` — scope `agents:write`

**query** `string` *required*

The SQL to run.

```json Response theme={"system"}
{
  "object": "app_db_result",
  "rows": [{ "count": 42 }],
  "row_count": 1
}
```

## List tables

`GET /v1/apps/{id}/db/tables` — scope `agents:read`

Every user table in the database, including the `vetta_migrations` ledger once a migration has
run. Not paginated: one database has a bounded catalogue.

```json Response theme={"system"}
{
  "data": [
    { "object": "db_table", "name": "todos", "schema": "public", "rows": 12, "size_bytes": 16384 }
  ]
}
```

`rows` and `size_bytes` are estimates from the database's statistics and may be `null` on a table
that has never been analysed.

## REST

| Route | Scope | Does |
| - | - | - |
| `GET /v1/apps/{id}/db/rest/*` | `agents:read` | Read rows: `…/rest/todos?done=eq.false&select=id,title&order=id`. |
| `HEAD /v1/apps/{id}/db/rest/*` | `agents:read` | The same read without the body — with `Prefer: count=exact`, just the count in `Content-Range`. |
| `POST /v1/apps/{id}/db/rest/*` | `agents:write` | Insert the JSON body's rows. |
| `PATCH /v1/apps/{id}/db/rest/*` | `agents:write` | Update the rows the filter selects with the JSON body. |
| `PUT /v1/apps/{id}/db/rest/*` | `agents:write` | Upsert one row by its primary key filter. |
| `DELETE /v1/apps/{id}/db/rest/*` | `agents:write` | Delete the rows the filter selects. |

A passthrough to the database's REST layer: every table is a resource under `…/db/rest/`, filters and
ordering ride the query string, and JSON goes in and out. `/v1/apps/{id}/db/rest/todos?done=eq.false&order=id` reads
open todos; `POST …/rest/todos` with a JSON body inserts; `PATCH …/rest/todos?id=eq.7` updates;
`DELETE …/rest/todos?id=eq.7` deletes. Filters are `column=op.value`: `eq`, `neq`, `gt`, `gte`,
`lt`, `lte`, `like`, `ilike`, `is`, `in`, and their `not.` negations.

Three request headers are forwarded — `Prefer` (`return=representation` to get the written rows
back, `count=exact` for a total), `Range` and `Content-Type` — and the upstream status and JSON body
come back verbatim, with the `Content-Range` header when the upstream set it.

> [!WARNING]
>
> **This is admin access.** Calls run as the database's service role, so the row-level security
> policies your app enforces for its own users do not apply here. Every call — read or write — is
> recorded in the [audit log](https://vetta.sh/docs/api/audit-logs.md) as `app.db_rest` with its method, path and status; the
> body is never recorded.

## Apply a migration

`POST /v1/apps/{id}/db/migrations` — scope `agents:write`. **`Idempotency-Key` is required.**

**name** `string` *required*

Lowercase letters, digits, `_` and `-`; up to 128 characters. The name is what makes the migration idempotent.

**sql** `string` *required*

The SQL to run. It is hashed (SHA-256) and recorded alongside the name.

A migration is applied **once per name**, tracked in a `vetta_migrations` table the platform keeps
on the app's own database:

* new name → the SQL runs and the response is `201` with the ledger row;
* same name, same SQL → nothing runs and the response is `200` with the existing row — a replay;
* same name, **different** SQL → `409 version_conflict`; a migration's SQL is immutable, so write a
  new one.

A newly applied migration is recorded in the audit log as `app.db_migrated` and pushed to
[webhook](https://vetta.sh/docs/api/webhooks.md) subscribers as `app.db.migrated` with `{ app_id, migration_id, name }`; a
replay is neither.

```json Response theme={"system"}
{
  "id": "dbm_01H...",
  "object": "db_migration",
  "name": "0001_todos",
  "sha256": "9f86d081884c7d659a2feaa0c55ad015a3bf4f1b2b0b822cd15d6c15b0f00a08",
  "applied_at": "2026-09-06T12:00:00.000Z",
  "statements": 1
}
```

## List migrations

`GET /v1/apps/{id}/db/migrations` — scope `agents:read`

The ledger, in `applied_at` order, as `{ "data": db_migration[] }`.

## Errors

| Code | Status | When |
| - | - | - |
| `ambiguous_app` | 409 | `default` (or an omitted app in the SDK/CLI) and the organization holds several `fullstack` apps. The message lists the candidate ids; name one. |
| `not_found` | 404 | No such app — or, for `default`, no `fullstack` app at all. |
| `feature_not_configured` | 501 | The app is `frontend_only` and has no database. |
| `job_not_ready` | 409 | The backend is still provisioning; retry. |
| `validation_failed` | 400 | A REST path that escapes the table space, a bad migration name, or a missing `Idempotency-Key`. |
| `version_conflict` | 409 | A migration name re-applied with different SQL. |
| `forbidden` | 403 | A write without `agents:write`. |


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