Database and schedules

Markdown

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 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:

"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)
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)	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:

// 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:

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):

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 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:

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), and lifts the limit for its own connection first:

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.

create table site_pace (user_ref text primary key, next_at integer not null);

// 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).

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

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

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.