# System tables

The runtime needs tables of its own, for example to hold events until
they are delivered. Jadpo generates them, names them with the prefix
`jadpo_`, and keeps them in the migration snapshots like any entity, so
drift in them fails `jadpo check` too.

## Tables in every store that holds an entity

The tables that must commit with an action's own writes go in each store
an action writes, so an action's outbox and
delivery records sit beside the writes they commit with. Jadpo gives them
to every store that holds an entity, since any of them can be written:

| Table | Holds | Described in |
|---|---|---|
| `jadpo_outbox` | Each event an action emitted, with its trace context and how deep in a chain it is | [Jobs and events](jobs-and-events.md) |
| `jadpo_deliveries` | Each event's delivery to each subscriber: status, attempts, next attempt, last failure, and while a circuit breaker holds it, its connection and time held | [Jobs and events](jobs-and-events.md) |
| `jadpo_handled_deliveries` | Each delivery a subscriber's action handled, committed with its writes in the store it writes | [Jobs and events](jobs-and-events.md) |
| `jadpo_idempotency_keys` | Each idempotent request's key, digest and stored response | [Idempotent writes](idempotent-writes.md) |
| `jadpo_operator_actions` | What an operator did to a delivery with `jadpo deliveries`: when, which action, the item and its state then, the operator's name and the database role | [Jobs and events](jobs-and-events.md) |
| `jadpo_circuit_breakers` | Each connection's circuit breaker over the deliveries beside it: its state, the run of failures, when it opened, its pauses and probe, and when its alert was raised | [Jobs and events](jobs-and-events.md) |

A subscriber's record that it handled a delivery goes in the store the
subscriber writes, committing with its writes, and refers to no table in
another store
([Delivery: at least once, safe to repeat](delivery-at-least-once-safe-to-repeat.md)).

An action with several changing calls keeps a record of them
([Several changing calls in one action](several-changing-calls-in-one-action.md)):

- `jadpo_step_records`: each run's key, action, build, step shape, first
  start, the generation of the runner holding it, whether it diverged,
  and when the alert on unfinished records was raised for it;
- `jadpo_steps`: each step's operation, its request's fingerprint, when it
  was sent and its sealed answer.

They go in the store such an action writes, or `events: { store: }`'s,
only where one is kept. A resumable single keyed changing call with an
existing record store uses these tables for dispatch details only, with
no request fingerprints or encrypted answers, so the provider's safe key
window also bounds its retries ([Integrations](external-services.md)).

## Tables for sign-in

Sign-in reads a credential with its caller's record in one statement, so
these tables go in the store of the entity they belong to
([Identity](identity.md)). Secrets in them are kept only as verifiers:
a verifier is a one-way digest that Jadpo can check a secret against, but
can't turn back into the secret.

- `jadpo_sessions`, browser sessions and API tokens: each one's user, the
  verifiers of its token and a browser session's CSRF token, and when it
  started and ends. Only in the store the principal's user entity lives
  in.
- `jadpo_service_keys`: each key's service, its verifier, and when it was
  issued and expires. Only in the store its service entity lives in.
- `jadpo_passkeys`: each passkey's user, its credential ID, the user
  handle its device keeps and the `WebAuthnCredential` it is; and
  `jadpo_passkey_challenges`: each ceremony under way, by its challenge's
  verifier, with what it is bound to and when it expires. Beside the
  sessions they start.
- `jadpo_sign_in_requests`, for magic links: one row per address, by its
  digest, with the latest link's and code's verifiers and the counts the
  limits read. Beside the sessions they start.

## Tables for schedules and other state

Schedule state, each schedule's lease included, goes in the store
`schedules: { store: }` names in `app.jadpo`, or the only store when there
is one ([Schedules](schedules.md)):

- `jadpo_schedules`: each schedule's next due time, lease and last tick (a
  time the schedule was due to run);
- `jadpo_schedule_runs`: each run and skipped tick.

They are created when a schedule is declared, with
`jadpo_operator_actions` for what operators did to those runs.

Other features that only keep state name their store the same way
(`flags: { store: main }`), and with several stores a missing one is a
compile error naming the feature. Other runtime state (multi-step call
records and batch runs beside the outbox, OAuth grants, poll state, and
webhook and queue de-duplication) follows the same rules and is always in
a database, never a cache
([Built-in services](built-in-services.md)).

Every store holding runtime state, including a store holding only AI
usage, also keeps `jadpo_housekeeping`: the owner, lease and last completed
hourly cleanup pass ([Jobs and events](jobs-and-events.md)). It is
generated and migrated with the other system tables.

The manifest holds a `system_table` node for each.
`jadpo_migrations`, which `jadpo migrate apply` keeps, is the runtime's
own and in no snapshot.

## Encodings in system tables

A system table that keeps a value beyond a request records the encoding
it was written in, an `encoding` `smallint`, so a later release reads it
by the form it has
([Changing a standard type's encoding](standard-type-encodings.md)).
Those values are the outbox's payload, a passkey's credential, and a
batch run's saved values and `key:`.

The runtime always writes an encoding, but the column may be `NULL`, for a
row kept before encodings were recorded. A migration adds it to a table
that may hold rows already, with no default, so the same column is the
same everywhere. A passkey with none is read in the form passkeys were
first kept in. A project whose migrations predate the column takes one
migration adding it when `jadpo check` asks (`migration.out_of_date`).