# 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 | [Background work](/docs/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 | [Background work](/docs/jobs-and-events.md) |
| `jadpo_handled_deliveries` | Each delivery a subscriber's action handled, committed with its writes in the store it writes | [Background work](/docs/jobs-and-events.md) |
| `jadpo_idempotency_keys` | Each idempotent request's key, digest and stored response | [Idempotent writes](/docs/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 | [Background work](/docs/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 | [Background work](/docs/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](/docs/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](/docs/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 ([External services](/docs/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
([Sign-in and identity](/docs/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.
- `jadpo_sign_in_links`: each way in and subject a first sign-in linked to
  a user, when `on_first_sign_in:` decides an external token's first
  sign-in ([A person the app has not seen](/docs/a-person-the-app-has-not-seen.md)).
  Only in the store the principal's user entity lives in.

## The audit trail's table

An audited entity's history commits with the changes it records, so it
goes in the store the entity lives in, created when an entity there has
an `audit:` ([Audit trail](/docs/audit-trail.md)):

- `jadpo_audit`: each change to an audited record, by the entity's born
  name, the record's `id` and its place among the record's changes; when
  it was made, what kind of change, who made it, through which action,
  request and trace, and each changed field before and after as text,
  with the encoding that was written in. Rows are only ever added, then
  deleted when the entity's audit retention ends, or with their record
  when erasure deletes it.

## 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](/docs/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. The flags keep `jadpo_flags`, each
flag's state, and `jadpo_flag_changes`, each change `jadpo flags` made
and who made it ([Feature flags](/docs/feature-flags.md)). 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](/docs/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 ([Background work](/docs/jobs-and-events.md)). It is
generated and migrated with the other system tables.

A store whose entities have `File` fields keeps `jadpo_files`, how many
fields hold each stored file, which a trigger on each table with a `File`
column keeps as rows change; it is how a file's bytes are deleted once no
record holds them ([Deleting and erasing](/docs/files.md#deleting-and-erasing)).
Its migration makes the trigger, and counts the files existing rows hold.

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](/docs/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`).