> ## 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](/docs/api/apps) 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`

<ParamField body="query" type="string" required>The SQL to run.</ParamField>

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

## 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.

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

`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](/docs/api/audit-logs) as `app.db_rest` with its method, path and status; the
  body is never recorded.
</Warning>

## Apply a migration

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

<ParamField body="name" type="string" required>Lowercase letters, digits, `_` and `-`; up to 128 characters. The name is what makes the migration idempotent.</ParamField>
<ParamField body="sql" type="string" required>The SQL to run. It is hashed (SHA-256) and recorded alongside the name.</ParamField>

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](/docs/api/webhooks) subscribers as `app.db.migrated` with `{ app_id, migration_id, name }`; a
replay is neither.

<ResponseExample>
  ```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
  }
  ```
</ResponseExample>

## 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`.                                                                                                                  |