# Database and schedules

> A SQLite database per application, PostgreSQL for a real need, rows that belong to one person, requests spaced for a site, and actions that run on their own.

Each application can have its own database, SQLite unless it needs PostgreSQL, and its actions can also
run on a schedule, with nobody calling them.

The examples use `$TOKEN`, `$API_KEY` and `$APP_ID` as [Build an
application](/docs/build-an-application/#the-credentials-in-the-examples) defines them.

## Its database

> A page's server knows the person only by `x-apigami-user-ref`, which Apigami's session sets on every
> route of your app's own address: a visitor, the side panel and a page on your own site get none, and
> anyone can load a page, so it renders nothing personal without it. In the page, apigami.js holds the
> person's token, and `apigami.call()` fetches their data through an action. An action learns who it
> serves only from its run token: `GET /v1/me` answers `user_ref`, the person, the same in every browser
> of theirs. Key every per-person row on `user_ref`. Never pick the person from the input, from the most
> recent row, from `install_ref` or `browser_pseudonym`, which name a browser, or from a person a URL
> names. An OAuth callback reads the person in `x-apigami-user-ref`, and checks its `state` against a
> `__Host-` cookie its `/oauth/start` set.

Take the SQLite database by default:

```json
"x-apigami": { "database": "sqlite" }
```

`true` means the same. Take the PostgreSQL database, `"database": "postgres"`, only for a real need: a schema
or a library already written for PostgreSQL, queries past 30 seconds, many concurrent writes, a PostgreSQL add-on.
A PostgreSQL database is billed markedly more: a flat fee, then its operations and its storage. A SQLite
database costs almost nothing while the application serves little.

The database is created the first time a version declaring it runs or deploys, and every version of the
application shares it. Once it exists, it stays, even for a version that stops declaring it, and the
application keeps its kind: a version declaring the other one is refused, so use the other kind in a new
application. An application that declares none has none.

| | SQLite (default) | PostgreSQL |
|---|---|---|
| In your code | `env.DB` | `env.DATABASE_URL` and `env.DIRECT_URL`, one connection per request |
| Size | 10 GB at most | no fixed cap, storage billed |
| One query | 30 seconds | 60 seconds by default ([Timeouts](#timeouts)) |
| Concurrency | one query at a time per database, about 1,000 a second at 1 ms each | 45 direct connections |
| One statement | 100 KB, 100 parameters, 2 MB per row | PostgreSQL's own limits |
| Queries per request your code receives | 1,000 | no cap of its own |
| Backup | back to any minute of the last 30 days, reversible | one copy a day the database changed, kept 7 days, no return to the minute |
| Schema | files of `migrations/`, applied by Apigami ([Migrations](#migrations-migrations)) | by hand, with `app_query` |

To restore either, write to contact@apigami.com: a restore applies to every version, since they share the
database. Deleting the application deletes a PostgreSQL database at once; a SQLite database is kept 30 days,
during which it can still be restored, then deleted.

A row that belongs to one person carries their `user_ref`, and every query that reads or writes it
filters on the `user_ref` the token names. `GET /v1/me` answers it, once per invocation: each call of
an action gets a token of its own, even when a page calls it again with the same token, so there is
nothing to keep from one call to the next:

```ts title="actions/basket.ts"
// The person this invocation serves, from its own token: X-Apigami-Run is new on every call.
export const personOf = async (env, token: string) => {
  const me = await fetch(`${env.APIGAMI_API_URL}/v1/me`, { headers: { Authorization: `Bearer ${token}` } })
  if (!me.ok) return null
  const run = await me.json()
  return run.user_ref
}

export const readBasket = async (request: Request, env) => {
  const token = request.headers.get('X-Apigami-Run')
  const userRef = token ? await personOf(env, token) : null
  // A run that serves nobody, a key without X-Apigami-User-Ref for one, has no basket to read.
  if (!userRef) return Response.json({ error: 'this action serves one person, and its token names none' }, { status: 401 })
  const { results: items } = await env.DB.prepare('select item, quantity from basket_items where user_ref = ?1 order by item')
    .bind(userRef).all()
  return Response.json({ items })
}
```

`env.DB.prepare(sql)` takes `?1`, `?2` placeholders; `.bind(...values)` fills them, then `.all()` answers
`{ results }`, `.first()` the first row or null, and `.run()` runs a write. `env.DB.batch([...])` runs
several bound statements as one all-or-nothing transaction. Never assemble a statement from strings.

`user_ref` is the same in every browser of the person and for every application of your group.
`install_ref` and `browser_pseudonym` change with the browser, and the input is whatever the caller
sent: none of them says who the person is.

### Migrations: `migrations/`

The schema of a SQLite database lives in the version, one file per change:

```sql title="migrations/0001_basket_items.sql"
create table basket_items (
  user_ref text not null,
  item text not null,
  quantity integer not null,
  primary key (user_ref, item)
);
```

A file is named `migrations/NNNN.sql` or `migrations/NNNN_name.sql`, four digits giving its order. Apigami
applies the new ones before each try and each deployment, on the database production reads, and records
them in `apigami_migrations`: read that table, never write it. So a migration only adds, a table, a column,
an index, and a file once applied is never changed: add a new one.

That table has one row per applied file: `number` (the four digits, as an integer), `name` (what follows
the underscore, null without one), `sha256` (of the file, which is how a changed file is refused) and
`applied_at` (UTC, ISO 8601):

```sql
select number, name, applied_at from apigami_migrations order by number;
```

A file runs as one batch, so it holds
neither `BEGIN` nor `COMMIT` (`migration_transaction`), and foreign keys stay on: `PRAGMA foreign_keys` is
refused; rebuild a table under `PRAGMA defer_foreign_keys = on`. A file that fails is not applied, and the
try or the deployment stops on it. A `create table if not exists` from your code, as the
[watch example](/docs/app-example-watch/) does, works for a table no migration creates. With
`"postgres"`, `migrations/` is ignored (`migrations_ignored`): create the tables with `app_query`, before the
code that reads them.

Read or change the rows without going through your own code, with `app_query` or over HTTP:

```sh
curl -s -X POST "https://api.eu.apigami.com/v1/apps/$APP_ID/database/queries" \
  -H "Authorization: Bearer $TOKEN" -H 'Content-Type: application/json' \
  -d '{"sql":"select item, quantity from basket_items where user_ref = ?1","params":["usr_…"]}'

curl -s "https://api.eu.apigami.com/v1/apps/$APP_ID/database" -H "Authorization: Bearer $TOKEN"
```

One database for every version means a migration applied while trying a version changes what
production reads. That is deliberate — there is one truth — and it is worth knowing before the
migration, not after.

### Timeouts

A SQLite query stops at 30 s, and nothing lifts it: split the work, or index what the query searches.

Every connection to a PostgreSQL database starts with `statement_timeout` at 60 s: a query that runs longer is
cancelled, so one stuck query cannot hold the database for everyone.

It is a default, not a ceiling. `app_query` sets it to its `timeout_ms`, 10 s by default and 60 s at most, and
stops waiting for the answer a second after that; the SQL it runs can still change `statement_timeout`. A
migration that needs longer runs from an action, through `DIRECT_URL`, within the action's 900 seconds of wall
clock and 300 seconds of computing time ([Limits](/docs/app-server/#limits)), and lifts the limit for its own
connection first:

```sql
set statement_timeout = '14min';
create index if not exists basket_items_by_item on basket_items (item);
```

Through `DATABASE_URL`, set it for one transaction with `set local statement_timeout = '5min'` inside
`begin` … `commit`, up to 10 minutes. `alter role current_user set statement_timeout = …` changes the default
for every later connection; keep it finite rather than 0.

Never hold a transaction open around a call to the browser: `POST /call` may wait up to 35 s for a
browser coming back online, and the transaction keeps its locks for all of that wait.

### Space your requests to a site

Apigami puts no rate limit on the requests your code sends to a site. When a site wants them
spaced, keep one row per person in your database and take the next slot in one statement: a SQLite
database runs one statement at a time, so two runs of the same person never get the same slot.

```sql title="migrations/0002_site_pace.sql"
create table site_pace (user_ref text primary key, next_at integer not null);
```

```ts
// One request every two seconds per person, whichever run sends it. Times in milliseconds.
const waitForTurn = async (env, userRef: string) => {
  const now = Date.now()
  const { next_at } = await env.DB.prepare(`insert into site_pace (user_ref, next_at) values (?1, ?2 + 2000)
    on conflict (user_ref) do update set next_at = max(site_pace.next_at, ?2) + 2000
    returning next_at`).bind(userRef, now).first()
  await new Promise((resolve) => setTimeout(resolve, next_at - 2000 - now))
}
```

Call `waitForTurn(env, run.user_ref)` before each `request.fetch` to that site.

## Triggers: `x-apigami.crons`

`x-apigami.crons` declares global recurring actions in the version. Apigami owns their schedule.
Each declaration has an `id`,
`action`, `interval_minutes`, `audience: "application"` and `input`. One minute is the shortest
interval. The action receives `input` as its JSON body and must use a method other than GET or HEAD.
There is no per-person cron audience: a global action can add queued tasks with a `user_ref` for each
person it serves. It lists them with `GET /v1/users` and calls with `env.APIGAMI_KEY`, the key of the
version in production, refused on any other ([The server](/docs/app-server/#the-code-an-entrypoint-that-answers-http)).

A trigger starts paused. Enable it after deploying its version:

```sh
curl -s -X PATCH "https://api.eu.apigami.com/v1/apps/$APP_ID/crons/refresh" \
  -H "Authorization: Bearer $TOKEN" -H 'Content-Type: application/json' -d '{"paused":false}'
```

`$TOKEN` is your author access token. A pause survives version changes. `environment: "staging"` selects the
version served at the staging address; production and staging keep separate queues and pause states.
`app_queues_write` with `action: "pause"` or `"resume"` and `cron_id: "refresh"` offers the same controls.
A pause may include an ISO `until` date to resume automatically, even across version changes.
`app_run` with `trigger: "refresh"` tries its declared input with no person, even if you are connected. `GET /v1/me` under the trigger's token returns
`user_ref: null`; browser work belongs to the tasks that name a person.

**When it runs.** One pending occurrence is kept per trigger. Finishing it and recording its successor
commit together. Missed intervals are skipped, and the next occurrence is always in the future. A
minute sweep repairs a missing successor. A failed attempt follows the queue retry policy before
its occurrence ends; `Retry-After` on a 429 or 503 postpones it without spending a failure attempt.
An attempt fails when the code throws or uses more than its 300 seconds of computing time
([Limits](/docs/app-server/#limits)).

`GET /v1/apps/{app}/crons` lists each trigger with its pause and last start. `app_status` shows the same
summary; `app_runs` includes the trigger id and environment in the run's output. Queue task logs retain
attempt outcomes for seven days.

## Managing queues through MCP

Use `app_queues` for reads and `app_queues_write` for changes. Both take `app_id` and
`environment: "production" | "staging"`, defaulting to production. The read tool defaults to
`view: "queues"`; `"tasks"` reads pending work and `"logs"` reads seven days of history.
All views accept `queue`, `cursor` and `limit`. Tasks also accept `tag`, `user_ref` and `task_id`;
logs accept `task_id`. Reuse the returned `next` with the same view and filters.

For writes, choose an `action`:

- `configure`: `queue` and complete `settings`, with exactly one `internal_app_path` or `external_url`;
  omitted setting fields take their defaults.
- `pause` or `resume`: exactly one `queue`, `tag` or `cron_id`; `pause` optionally takes `until`.
- `add_tasks`: a `tasks` array of at most 1000 entries, in already configured queues; an existing id
  replaces that task's pending version.
- `remove_tasks`: either `task_ids` or `tag`, plus `confirm: true`; `purge`: `queue` and `confirm: true`.
  Reserved work may still run; purging keeps the queue settings.
- `requeue`: the `cursor` of an abandoned or expired entry from `app_queues view: "logs"`. Current
  access is checked again. An existing task with that id causes 409 and remains unchanged.

Write responses point back to the relevant `app_queues` view. Trigger declarations still belong in
the version manifest; these tools cannot create or edit them.