# Storage

cronpad stores everything durable in two SQLite files and keeps its delivery queue in a third, rebuildable one. Nothing else needs to run. `cronpad.db` is small and holds everything needed to schedule. `history.db` is large and holds runs, attempts and logs of what happened. Litestream streams those two to object storage continuously; the queue is never backed up. One node comfortably handles on the order of a thousand runs per second; the first real limit is high availability, not throughput.

## What lives where

| Store | Contents | If it's lost |
|---|---|---|
| `cronpad.db` (control) | Users, workspaces, API keys, secrets, jobs (including each job's next fire time), alert channels and rules | Restore from Litestream. Restores in seconds because it stays small. |
| `history.db` (history) | Runs, attempts, audit log, alert outbox, daily statistics | Restore from Litestream in the background, or start empty. Scheduling doesn't depend on it. |
| `cronpad-queue.db` (queue) | The `due` and `inflight` queues, the dispatcher lock | Nothing to restore. cronpad rebuilds the queue from `cronpad.db` automatically at startup. |

## Why SQLite

- **Self-hosting stays simple.** No database or queue server to install, tune, upgrade or back up separately.
- **The workload suits it.** Writes are small, mostly appends, and cronpad groups them into as few transactions as possible (whatever arrived while the previous commit ran, up to 500 commands) through a single writer connection per file, which is exactly how SQLite is fastest.
- **Reads are local.** Dashboard and API queries are indexed lookups on a local file, with no network hop.
- **Backups are continuous.** Litestream ships every change to S3-compatible storage within about a second and supports point-in-time restore. See [Backups](../self-hosting/backups.md).

## Why three files

SQLite allows one writer per file, so each file is its own write queue.

- **Control and history.** Recording a burst of run results never delays a job edit, and the file you need to resume scheduling after a disaster stays tiny. History can be pruned, restored later, or discarded without touching jobs.
- **The queue.** It takes two or three small writes per run (claim, next occurrence, acknowledgement). In its own file those never compete with the API or run recording, and the critical control file doesn't churn. Because it can always be rebuilt, it is left out of backups entirely.

## How far it scales

These are planning numbers. The `just loadtest` tool measures your hardware; the cronpad repository records real results.

| Dimension | Planning figure | What it depends on |
|---|---|---|
| Sustained runs per second | ~1,000 (≈86 M per day) | Disk fsync latency. Local NVMe is far faster than network block storage. |
| Writes per run | ~4 rows, batched | Run start, run finish, attempt, job counters |
| History size | ~300–500 bytes per run without response bodies | [Retention](../platform/data-retention.md) keeps it bounded |
| Jobs | Millions | Each active job costs one queue row (~100 bytes) and one job row |

### Measured local workload

These runs measure the authenticated API → queue → worker → local HTTP echo
pipeline on an Apple M5 Pro with 15 logical CPUs, 24 GiB RAM and a local SSD,
with the default group-commit result writer. Each run created jobs
firing once per second for 60 seconds. Every delivery succeeded with zero
duplicates; schedules extended beyond the measurement so the numbers measure
sustained delivery rather than a simultaneous job-completion burst. Loadgen uses
256 workers; the pipeline fixture below uses 1024. The shipped worker-count
default is 64, so these are tuned load scenarios.

| Mode | CPU budget | Runs/s | Runs/min | Start lag p50 / p95 / p99 |
|---|---|---:|---:|---|
| Durable | Default (15) | 1 | 60 | 2 / 3 / 4 ms |
| Durable | Default (15) | 200 | 12,000 | 37 / 49 / 54 ms |
| Durable | `GOMAXPROCS=2` | 200 | 12,000 | 43 / 58 / 60 ms |
| Fast | Default (15) | 200 | 12,000 | 28 / 37 / 39 ms |
| Fast | `GOMAXPROCS=2` | 200 | 12,000 | 21 / 35 / 40 ms |
| Durable | Default (15) | 500 | 30,000 | 67 / 115 / 139 ms |

The 200 runs/s rows are the median of three runs each, with each percentile taken
separately. Single runs varied by up to ±10 ms; one two-CPU durable run reached a
p99 of 89 ms.

SQLite + WAL growth across all three files was about 11 MB per 12,000 runs
without response bodies; it is not a per-row storage estimate. A two-CPU limit on
this machine does not reproduce a two-vCPU cloud VM's disk or contention, and
network block storage adds latency to every commit.

The separate pipeline integration test delivered 1,000 jobs/s for 30 seconds:
all 30,000 runs, zero missed or duplicate occurrences, start lag p99 161–192 ms
in durable mode and 56–141 ms in fast mode across its quota profiles and two
separate runs. That
isolates the delivery pipeline rather than job-creation/API throughput.

Earlier measurements, taken while the queue was still a Redis server and results
were flushed on a fixed ticker, are kept below for the comparisons they made. The
fixed 5 ms window measured 47 / 64 / 80 ms at 200 jobs/s, and a single job waited
about 12 ms; the 50 ms window before it measured about 165 ms median. Opt-in fast
mode measured 13 / 24 / 29 ms by default and 19 / 36 / 43 ms with two CPUs at 200
jobs/s. Daily quota checks use durable constant-time counters. Read
[Delivery guarantees](delivery-guarantees.md#durable-and-fast-delivery) before
enabling fast mode.

In an earlier 50ms-flush comparison, with 50,000 expired rows prefilled and the janitor running every second during
the same 60-second workload, throughput remained 200/s with no missed or duplicate
runs. Lag p50/p95/p99 was **106/162/165 ms** by default and **95/164/167 ms** with
two CPUs. All 50,000 old rows were rolled up and pruned; net SQLite + WAL size
fell by 3,050,872 and 3,026,632 bytes respectively. Maintenance flushes pending
results before its own short transaction, which can also reduce batch wait time.
The automated comparison allows at most 100 ms percentile noise and 5% throughput
noise; both comparisons passed.

### Higher load

The same machine (durable mode, default CPUs, 30-second runs) at higher offered load. "Aligned" means every job is due at the same instant each second, the worst case; "spread" uses `-stagger`.

| Runs/min | Delivered | Duplicates | Start lag p50 / p95 / p99, aligned | p99, spread |
|---:|---|---:|---|---:|
| 30,000 | all 15,000 | 0 | 64 / 106 / 111 ms | |
| 60,000 | all 30,000 | 0 | 115 / 200 / 211 ms | 2 ms |
| 120,000 | all 60,000 | 0 | 208 / 368 / 393 ms | 2 ms |

The aligned 120,000/min run briefly reported dispatcher backpressure: cronpad slowed admission and delivered everything late rather than dropping work. In spread runs, loadgen's run count included a few runs that fired after its measurement window closed, so their count check is not a duplicate measurement. On the earlier Redis queue the aligned p99 was 151, 320 and 599 ms at the same loads.

`go run ./tools/loadgen -stagger` spreads job anchors across each second;
`-complete-at-end` includes a simultaneous schedule-completion burst. Use
`-janitor -prefill 50000` to overlap retention with delivery, and `-dir PATH` to
retain the scratch databases for inspection. Use `-flush 5ms` or `-flush 50ms` to compare a fixed flush window with the default group commit, and
`-delivery-mode fast` for opt-in delivery measurements.

### The limits, in the order you'd reach them

1. **No high availability.** One process owns the database files. A restart or deploy pauses scheduling for a few seconds. Runs due in that window start late; none are lost.
2. **Restore time.** Litestream restore time grows with file size. The control/history split keeps the critical file small.
3. **Single-writer throughput**, beyond a few thousand runs per second.

When you need zero-downtime deploys or much more throughput, the path is moving the control database to Postgres and running several cronpad nodes; the dispatcher lock already ensures only one of them schedules at a time. Data access goes through a single storage interface, so this is an addition, not a rewrite.

## Rules that follow from this

- Run exactly **one** cronpad process per database file. On Kubernetes: one replica, `strategy: Recreate`, a `ReadWriteOnce` volume.
- Keep the database on local or block storage. **Never on NFS or SMB**: SQLite file locking is unreliable on network filesystems.
- Keep the queue file on the same volume as the databases (the default). Don't back it up or restore it; cronpad rebuilds it.

See the [production checklist](../self-hosting/production-checklist.md).