Database Schema

acme-proxy stores everything in one database — accounts, orders, the audit trail and the web admin’s own operators. There is no second datastore and no cache. This page describes what is in it and why, for anyone reading the database directly, writing a migration, or trying to understand what a delete cascades to.

Two backends, one schema. SQLite is the default and PostgreSQL is what a multi-node deployment needs; the scheme of database.url picks between them. Everything below describes both — the tables, the constraints, the cascades and the reasoning are the same either way. Where the two differ it is noted inline, and the differences are three: the column types (BLOB/uuid, INTEGER/bigint), the AUTOINCREMENT spelling, and the recipes for reading it by hand at the bottom of this page. The SQL the server issues is written once; crates/store/src/sql.rs is the seam and its //! says what had to fork.

A database is one backend or the other; there is no dual-write mode. Moving between them is acme-proxy transfer, which copies every row of every table and is guarded by crates/store/src/transfer.rs’s column manifest — a migration that adds a column adds it there too, or the copy would silently leave it behind.

There are two migration sets, one per dialect, and both are frozen and append-only — SQLite’s as of 0.1.0, PostgreSQL’s from its first release. A schema change is a new sqlx migrate add file in each, never an edit to a committed one. The PostgreSQL set is deliberately not a transcription of the SQLite one: those files carry table rebuilds that exist only because SQLite cannot add a CHECK, a UNIQUE or a foreign key to an existing table, and no PostgreSQL deployment has that history to replay. The schema is also the only surface frozen before 1.0.0 — the freeze says nothing about configuration keys, which may still be renamed. See Contributing for the three consequences that catch people out.

The tables at a glance

erDiagram
    accounts ||--o{ orders : "account_id"
    orders ||--o{ authorizations : "order_id"
    authorizations ||--o{ challenges : "authz_id"
    orders ||--o| upstream_orders : "order_id"

    admin_users ||--o{ admin_sessions : "user_id"
    admin_users ||--o{ admin_recovery_codes : "user_id"

    accounts {
        blob id PK
        text profile "UNIQUE(profile, pubkey)"
        blob pubkey
        text status "CHECK valid|deactivated|revoked"
        blob eab_kid "no FK - see below"
        text created_ip
        text last_seen_ip
    }
    orders {
        blob id PK
        text profile
        blob account_id FK
        text status "CHECK pending|ready|processing|valid|invalid"
        text identifiers "JSON array"
        text replaces "RFC 9773 certID"
        text certificate "PEM chain"
        text cert_serial
        integer cert_not_after "what the leaf says - see below"
        integer revoked_at
    }
    authorizations {
        blob id PK
        blob order_id FK
        text identifier "JSON, UNIQUE(order_id, identifier)"
        text status "CHECK pending|valid|invalid|deactivated|expired|revoked"
    }
    challenges {
        blob id PK
        blob authz_id FK
        text type "CHECK http-01|dns-01|tls-alpn-01, UNIQUE(authz_id, type)"
        text token
        text status "CHECK pending|processing|valid|invalid"
    }
    upstream_orders {
        blob order_id PK "also the concurrency guard"
        text upstream_order_url
        blob csr_der
        text client_ip "parked request context"
    }

    eab_keys {
        blob kid PK
        blob secret "retrievable on purpose"
        text profile "NULL = every endpoint"
        text status "CHECK active|revoked"
    }
    nonces {
        text value PK
        integer created_at
    }
    audit_log {
        integer id PK "AUTOINCREMENT"
        text event "no CHECK - the Rust enum"
        text outcome "CHECK success|failure"
        text account_id "no FK, deliberately"
        text order_id "no FK, deliberately"
        text identifiers "frozen into the row"
    }

    jobs {
        blob id PK
        text kind "no CHECK - see below"
        text dedup_key "partial UNIQUE(kind, dedup_key)"
        text payload "JSON, the subject's identity"
        text status "CHECK ready|running|done|failed|cancelled"
        integer run_at "the durable schedule"
        integer attempts "incremented at claim"
        integer deadline "give up after this"
        integer lease_until "when a dead runner's row is reclaimed"
        text lease_owner "which runner holds it"
    }

    admin_users {
        blob id PK
        text username UK
        text password_hash "one-way"
        blob totp_secret
        text status "CHECK active|disabled"
        text role "no CHECK - NULL reads as admin"
    }
    admin_sessions {
        text token_hash PK "SHA-256 of the token"
        blob user_id FK
        text state "CHECK pending_mfa|active"
        integer mfa_attempts
    }
    admin_recovery_codes {
        blob id PK
        blob user_id FK
        text code_hash
        integer used_at "stamped, not deleted"
    }

    revocations {
        text issuer PK "SHA-256 of the CA's SPKI"
        text serial PK
        integer revoked_at "the first one stands"
        integer not_after "NULL is never pruned"
    }
    crls {
        text issuer PK
        integer crl_number "moves only by compare-and-swap"
        blob der "what GET /crl serves"
        integer next_update
    }
    http01_tokens {
        text token PK "the upstream's own token"
        text key_authorization
        integer expires_at "a backstop, swept hourly"
    }

The diagram has three clusters, and the two things worth noticing are the edges that are not drawn:

• The ACME graph — accounts → orders → authorizations → challenges, with upstream_orders hanging off an order and eab_keys and nonces standing alone.
• audit_log, jobs and a local CA’s revocations, crls, plus the relay’s http01_tokens, all connected to nothing. That is policy in each case, not an omission. The audit trail and the revocation ledger must outlive what they describe, the job queue is generic (both below), and the last three are state every role process shares (ADR 0008).
• The admin island — admin_users and its two children — which never joins to accounts. An admin_users row is an operator of this server; an accounts row is a client key that asks it for certificates. They are different populations and the schema says so.

Profiles are a database boundary

accounts.profile and orders.profile are NOT NULL, and accounts is keyed UNIQUE(profile, pubkey). One client key presenting itself at two endpoints is two independent accounts with separate orders and separate authorizations — see Profiles & Routing.

eab_keys.profile is the one nullable member of the set, and NULL means “valid at every endpoint” rather than “unknown”.

Request-path lookups always take the profile. The admin CLI deliberately uses unscoped lookups (find_any_by_id, find_any_by_kid), because an operator holding an id wants the row, not a reminder about which endpoint it belongs to.

Every foreign key is indexed and cascades

SQLite indexes primary keys and UNIQUE constraints and nothing else, so before 20260727120000_indexes_and_constraints.sql every order read and every challenge trigger was a full table scan. That migration rebuilt the four ACME tables to add both halves at once:

• ON DELETE CASCADE on every foreign key, so an account or an order can genuinely be deleted. On SQLite this depends on the foreign_keys pragma, which crates/store/src/db.rs pins on for every connection; PostgreSQL always enforces them.
• An index on every foreign key: idx_orders_account_id, idx_authorizations_order, idx_challenges_authz.

Two later indexes serve one query each: idx_orders_cert_serial on (profile, cert_serial), which POST /revokeCert uses on every request, and idx_orders_created_at / idx_orders_status_created_at, which the web admin’s newest-first cross-account listing needs and the ACME path never did.

idx_orders_replaces_claim is different — it is a partial unique index on (profile, replaces) where replaces IS NOT NULL AND status != 'invalid'. It is not a lookup index at all; it is RFC 9773 §5’s “already replaced?” rule enforced in SQL, which is what makes 409 alreadyReplaced race-free and what lets an order that fails release its claim. See Renewal Information.

CHECK constraints hold the state machines

Every status column carries a CHECK (status IN (…)) — accounts, orders, authorizations, challenges, jobs, upstream_orders, eab_keys and admin_users — as do challenges.type, admin_sessions.state, and audit_log’s outcome and actor_kind.

They are there because a typo in a status would otherwise park a row in a state nothing can read back, and the row would look fine. With the constraint it is a failed write at the moment of the mistake.

Open vocabularies carry none: audit_log.event (its CHECK was dropped by 20260909120000), admin_users.role and jobs.kind are validated by a Rust enum instead, because each grows with features and a CHECK would cost a table rebuild per new word. See ADR 0005.

This is also why a new CHECK is expensive: SQLite cannot add one to an existing table, so it needs a full table rebuild in a new migration. Several constraints were therefore declared before anything wrote them — admin_users.totp_secret/totp_pending_secret/totp_last_step and admin_sessions.state’s 'pending_mfa' value are the worked example, added in 20260808120000 and only used once the second factor shipped.

The audit trail has no foreign keys, deliberately

audit_log names an account_id and an order_id with no constraint behind either. An audit row has to survive the account or order it describes being deleted — a CASCADE there would destroy the evidence along with its subject, which is the one thing an audit trail may not do. The identifiers are frozen into the row for the same reason, rather than being read back through a join that may no longer resolve.

Two more consequences of that decision:

• id is INTEGER PRIMARY KEY AUTOINCREMENT, not a plain rowid. An operator types this id, and AUTOINCREMENT is what stops SQLite handing out the rowid of a purged row a second time.
• outcome is denormalized from event and written from the single definition in AuditEvent::outcome, so “show me everything that was refused” is an index lookup rather than event LIKE '%_failed' written out in three front ends.

Rows are only ever INSERTed. There is no setter and no UPDATE against this table anywhere in the crate; the only statement that removes anything is the retention sweep. See Audit Trail.

revocations follows the same rule for the same reason. A local CA’s revocation must outlive an order an operator deletes, or the serial would drop off the CRL, so it has no foreign key to orders either.

accounts.eab_kid is a similar deliberate non-key: it records which credential was used at registration, but an EAB credential is revocable and the account outlives it, so there is no constraint tying the two together.

The job queue is generic, and its schema says so

jobs is the second table with no foreign key, for a different reason than audit_log’s. A queue is generic: payload names whatever kind means — a local order today, a certificate serial or nothing at all tomorrow — so a typed foreign key would either be wrong for every other kind or force one nullable column per kind. A job whose subject was deleted is retired by its handler (“the order no longer exists”), which is a terminal outcome recorded in last_error, not an orphan nothing sweeps.

Three more shapes worth knowing before touching it:

• kind carries no CHECK, unlike every other enum-ish column here. A kind is registered in code by whichever subsystem owns it, and SQLite cannot alter a CHECK without a table rebuild, so every future kind would cost one. The runner claims only the kinds its registry holds, so an unrecognised one is left alone rather than mis-run — which is also what lets an older binary meet a row a newer one wrote.
• The identity index is partial: UNIQUE(kind, dedup_key) WHERE status IN ('ready', 'running'). Only a live job holds an identity. A plain UNIQUE would let one finished job block its own key for ever, which is fatal for a periodic kind whose key is a constant and wrong for an order retried after a failure.
• attempts increments when the row is claimed, not when it completes, so a job that reliably kills the process still exhausts its budget instead of crash-looping. The same reasoning is why the reclaim sweep leaves the counter alone.

status = 'cancelled' is written by jobs cancel and its panel and API twins. It was declared before anything wrote it — the admin_sessions.state = 'pending_mfa' treatment, where a CHECK was written before anything filled it precisely so no rebuild would be needed later.

Two expiry columns on orders, and neither is the order’s

orders.not_after is the validity the client asked for in newOrder (RFC 8555 §7.4) — usually NULL, and clamped by the signer when set. orders.cert_not_after is what the issued leaf actually says, stamped by Order::finalize from the same DER that cert_serial and cert_pubkey come from. The order object’s own expires is a third thing again, and is not a column here.

cert_not_after has three meaningful states:

• an epoch second;
• NULL — issued before the column existed; the expiry sweep backfills it;
• a negative sentinel — the sweep looked and the chain would not parse. Writing NULL back would have it re-parsed on every pass for ever.

It is optional where cert_serial is not: a chain whose serial cannot be read cannot be revoked, so it is a failed issuance, while an unreadable validity is only housekeeping. Its index is partial on certificate IS NOT NULL AND revoked_at IS NULL, which is the expiry digest’s own predicate.

An order holding a live certificate is never deleted

The order row is a certificate’s only record: revokeCert and order revoke find it by serial, the expiry digest lists it, and renewal information (RFC 9773) is derived from it. Deleting it would make a certificate that is still trusted impossible to revoke.

So account delete, order delete and eab delete --delete-accounts are refused, on every surface, while any order they would remove holds a live certificate — issued, not revoked, and not yet expired (cert_not_after NULL, negative or in the future). The check runs before the confirmation prompt and again inside the DELETE itself (live_certificate! in crates/store/src/order.rs), so a certificate issued in between is not lost. There is no override flag. The daily order_sweep follows the same rule: a valid order is never swept, whatever its age.

Secrets are stored three different ways, on purpose

The three storage shapes in this schema are not an inconsistency — each one follows from what the server has to do with the value later.

admin_recovery_codes.code_hash follows the password shape — a recovery code is only ever compared. It is a table rather than a JSON column so that consuming one is UPDATE … WHERE id = ? AND used_at IS NULL with rows_affected deciding a race, the same primitive nonces uses. used_at is stamped rather than deleted, so “7 of 10 remaining” is a count and a spent code leaves a trail.

admin_users.totp_secret and totp_pending_secret are plaintext BLOBs on purpose: verification recomputes the HMAC, so the server needs the same bytes back every attempt. That is eab_keys.secret’s situation, not a password’s, and any wrapping key would live in the same directory as the database.

Columns nothing ever compares against

accounts.created_ip/created_ptr/last_seen_ip/last_seen_ptr, orders.created_ip/created_ptr, admin_sessions.created_ip/user_agent, and audit_log’s client_ip/client_ptr/user_agent are forensics only. No code path compares a live request against any of them.

That is a decision, not an oversight. Pinning an identity to an address breaks CGNAT and mobile clients; pinning it to a User-Agent breaks on the next browser update. They answer “who asked for this, and from where” after the fact, and nothing else.

One column is compared, and only to decide whether to send a message: admin_users.known_login_ips, the operator’s last five distinct sign-in addresses, decides whether a sign-in is reported as coming from a new address. It never allows or denies anything.

None of them reaches an ACME object either — the wire format is RFC 8555’s and stays that way. They surface through the admin CLI and the web admin only.

Ids are UUID v7, stored as bytes

Every row this server creates is keyed by a UUID version 7 (RFC 9562 §5.7), minted in one place, acme_proxy_store::id::mint. Ids created close together share a prefix, and they sort by creation; why that matters, and the rule that an id’s Rust type says where it came from, are in ADR 0004.

On SQLite the column holds the sixteen bytes, not the thirty-six characters of the rendering; on PostgreSQL it is a native uuid, and sqlx maps the same Rust type to both. Nothing on the wire changes either way — an id is still rendered by Uuid::to_string, so account URLs, kids, order URLs and every admin API member are the same lower-case hyphenated form they always were.

What changes is an ad-hoc query, and only on SQLite, where an id column prints as a blob and wants hex():

# Readable ids.
sqlite3 sqlite.db "SELECT lower(hex(id)), profile, status FROM accounts;"

# Looking one up by the id from a URL or a log line.
sqlite3 sqlite.db "SELECT status FROM orders
                    WHERE id = unhex(replace('6ba7b810-9dad-41d1-80b4-00c04fd430c8','-',''));"

PostgreSQL needs neither, since it prints and parses the hyphenated form:

psql -c "SELECT id, profile, status FROM accounts;"
psql -c "SELECT status FROM orders WHERE id = '6ba7b810-9dad-41d1-80b4-00c04fd430c8';"

A few columns look like ids and are not, so they stay text: orders.replaces is an RFC 9773 certID, audit_log.actor_id may be an account id or an admin username, audit_log.account_id and order_id name a row that may already be gone, and request_id is whatever the caller sent.

Rows created before this changed were converted in place and keep their v4 ids, so a table holds both versions and only the v7s sort by creation.

Reading it directly

On SQLite, the file is sqlite.db by default and is opened in WAL mode, so there are normally sqlite.db-wal and sqlite.db-shm beside it. Copying only sqlite.db gives you a database missing every recent write; back up all three, or use sqlite3 sqlite.db ".backup backup.db", which is consistent by construction. On PostgreSQL, back it up the way you back up any other database — pg_dump is consistent by construction and there are no sidecar files to miss.

Ids are stored as bytes on SQLite, so they need hex() on the way out and unhex() on the way in; on PostgreSQL they are a native uuid and need neither. See Ids are UUID v7, stored as bytes.

The recipes below are SQLite’s. On PostgreSQL the ids need no wrapping and the epoch-second columns read with to_timestamp(created_at) in place of datetime(created_at,'unixepoch').

# What has this account been issued?
sqlite3 sqlite.db "SELECT lower(hex(id)), status, identifiers,
                          datetime(created_at,'unixepoch')
                     FROM orders
                    WHERE account_id = unhex(replace('…','-',''))
                    ORDER BY created_at DESC;"

# Everything refused in the last day.
sqlite3 sqlite.db "SELECT datetime(created_at,'unixepoch'), event, profile, client_ip, reason
                     FROM audit_log WHERE outcome = 'failure'
                      AND created_at > strftime('%s','now','-1 day');"

# Which migrations have run.
sqlite3 sqlite.db "SELECT version, description, success FROM _sqlx_migrations;"

Read-only inspection of a running server is safe under WAL, and on PostgreSQL by its own MVCC. Writing to the database behind the server’s back is not — the CHECK constraints will catch a bad status, but nothing will re-sync the in-memory state a handler is holding. Use the Admin CLI instead.