audit_events grows unbounded (live.view, ~400k rows/day) and starves the online DB backup

[davlaw]

Correction (see comment below): the claim in "Why it's unbounded in practice" that db_audit_prune() is never called on a schedule is wrong — it runs hourly from the audit-insert path (db_audit.c:182). The real issue is that retention is time-only with a 365-day default, so the hourly prune deletes zero rows indefinitely at this event rate. A separate latent bug (unbounded DELETE holding the global DB mutex, measured at 2m31s for 5.3M rows) is described in the comment.

On a 7-camera production install, audit_events reached 5,946,202 rows / ~4.06 GB — 95% of a 4.27 GB database — and the resulting write volume made sqlite3_backup_step()-based scheduled backups unable to complete at all. No scheduled backup succeeded in 24+ hours.

Composition

dbstat on a 4.27 GB database:

audit_events                     2908 MB
idx_audit_events_target           386 MB
sqlite_autoindex_audit_events_1   286 MB
idx_audit_events_action_outcome   240 MB
idx_audit_events_occurred         130 MB
idx_audit_events_principal        107 MB
                                 -------
                                 4057 MB   (95% of the database)

By action:

live.view           5,913,081   (99.4%)
system.admin           29,791
recordings.replay       1,763
storage.configure         939
snapshot.create           552
auth.login                 26

Daily live.view volume, accelerating:

2026-09-08  216,537
2026-09-09  311,059
2026-09-10  218,235
2026-09-11  335,016
2026-09-12  417,029   (partial day)

Data goes back only to 2026-08-24 — 19 days to 4 GB.

Why it's unbounded in practice

Two things combine:

1. audit_retention_days defaults to 365 (AUDIT_RETENTION_DEFAULT_DAYS, and the system_settings seed in the embedded migrations).
2. db_audit_prune() is never called on a schedule. Its only call site in the tree is src/web/api_handlers_audit.c:303, inside the retention-settings update handler — so pruning happens only as a side effect of an admin explicitly PATCHing the retention setting. There is no timer, no startup prune, no periodic maintenance hook.

So on any install where nobody manually pokes that endpoint, the table grows forever. At ~400k rows/day, 365-day retention is effectively infinite — the disk gives out long before the retention window does.

Each live.view insert also writes 6 B-trees (table + 5 indexes), so the write amplification is substantial.

Why this breaks backups

SQLite restarts an online backup from page one whenever the source database is modified through a different connection than the backup's. db_backup.c opens its own read-only source connection, so every audit insert counts. With live.view writing continuously, sqlite3_backup_step() never reaches the end of the database: the destination plateaus (~800 MB in our case) and the loop re-copies indefinitely at ~100% CPU until something aborts it.

Measured on the same 4.27 GB database:

	result
Scheduled online backup, cameras live	never completes (47 attempts, 0 successes in 24h)
Cold .backup via sqlite3 CLI, service stopped	6.4 seconds, quick_check = ok

Backup success rate degraded as the table grew — roughly 5-7 successes/day against an expected 24 over the preceding week, then 0.

Secondary effect: a failed attempt restarts ~1 second later, so a core is pinned continuously.

Suggested directions

1. Don't write a durable audit row per live.view request. It's a high-frequency read with little forensic value at per-request granularity — sampling, coalescing per session/interval, or dropping it entirely would remove 99.4% of the volume.
2. Run db_audit_prune() on a schedule (startup + periodic), rather than only as a side effect of a settings write.
3. Reconsider the 365-day default, or make retention size-aware — a row-count/byte ceiling would bound this regardless of event rate.

Happy to open a PR for (2) and/or (3) if you have a preference on shape — (1) seems like your call on what the audit trail is meant to guarantee.

🤖 Generated with Claude Code

[davlaw]

Correction to the original report — I got one of the two claims wrong.

I wrote that db_audit_prune() is never called on a schedule. That's incorrect, and I should have traced the insert path before filing. Automatic pruning does exist: src/database/db_audit.c:182, at the end of the audit-insert path, runs prune_locked() once an hour:

int64_t now = (int64_t)time(NULL);
if (rc == SQLITE_DONE && now - last_automatic_prune_at >= 3600) {
    int deleted = 0;
    if (prune_locked(db, &deleted) == 0) { ... }
}

So the scheduling half of the issue is already solved. Apologies for the noise.

The actual mechanism is narrower, and I think still worth addressing. Retention is expressed purely in time, and the default is 365 days. On this install the hourly prune had been running the whole time and deleting zero rows every single time, because the oldest row was only 19 days old. At ~400k live.view rows/day, a 365-day window means roughly 146 million rows would have to accumulate before retention engages at all — the disk runs out long before the policy does. Time-based retention alone can't bound a table whose row rate is this high.

A second, concrete bug I hit while cleaning this up. prune_locked() issues a single unbounded statement:

"DELETE FROM audit_events WHERE occurred_at < ?;"

while holding the global database mutex, and it is invoked from the audit-insert path. That's fine today only because it always deletes nothing. The moment it actually has work — the first prune after retention finally elapses, or an admin lowering audit_retention_days on an existing table — it blocks the entire database for the duration.

I measured this directly on the 5.9M-row table (service stopped, so no contention working against it):

DELETE FROM audit_events WHERE action='live.view' AND occurred_at < ...;
  5,333,676 rows -- real 2m31s

Two and a half minutes holding the global DB mutex, reached from an ordinary audit write. On this install that would have stalled recording, detection, and the HTTP API together.

So the revised suggestions:

1. Bound the prune per invocation — delete in capped batches and let the hourly tick catch up over time, rather than one unbounded DELETE. The portable form (no dependence on SQLITE_ENABLE_UPDATE_DELETE_LIMIT, which isn't available in every build) is roughly:

   DELETE FROM audit_events
    WHERE id IN (SELECT id FROM audit_events
                  WHERE occurred_at < ? ORDER BY id LIMIT ?);

   This alone removes the multi-minute stall, and it makes lowering the retention setting safe on an existing large table.
2. Add a volume ceiling alongside the age window — a max row count or byte budget, so retention engages on growth rate rather than only on calendar age.
3. Unchanged from the original report: a durable audit row per live.view request is what generates the volume in the first place, and that one is your call on what the audit trail is meant to guarantee.

Happy to send a PR for (1), and (2) if you want it — (1) seems worth doing regardless of what's decided about (3), since it's a latent stall on any install that ever accumulates a meaningful backlog.

🤖 Generated with Claude Code

[davlaw]

Follow-up with a much sharper diagnosis of where the volume actually comes from — this supersedes my vaguer "live.view is audited per request" framing.

It isn't live-view streaming. It's audit_log_authorization() (src/web/audit_log.c:175) writing a durable audit_events row for every authorization decision, including allowed ones.

Outcome split across the 5.9M live.view rows on this install:

allowed   5,909,601   (99.94%)
denied        3,480

A representative row:

{"event_type":"authorization.decision","method":"GET",
 "path":"/api/detection/results/Speaker","decision_source":"policy_grant",
 "policy_version":2,"explanation":"Allowed by Administrator role grant",
 "grant_uuid":"...","role_uuid":"..."}

So the generator is the web UI polling /api/detection/results/<stream> roughly once a second per stream per open tab. Every one of those polls is an authorized read that produces a permanent audit row. By client:

192.168.5.47      4,784,627   (80%)
100.114.179.93      771,935   (tailscale)
192.168.5.234       356,518
127.0.0.1                 1

Three ordinary browser clients, ~400k rows/day.

Why I don't think pruning alone solves this. I pruned this install from 4.27 GB down to 420 MB. Scheduled backups started working again (57s under live load, vs never completing in 30 minutes before). But six hours later the database is back to 554 MB and scheduled backups are already failing again — 4 completed against 6 aborted, including a 3.5-hour gap with none succeeding.

That's because the thing defeating the online backup is the write rate, not the size: SQLite restarts an online backup from page one whenever the source is written through a different connection, and ~5 authorization rows/second means sqlite3_backup_step() keeps getting reset before it reaches the end. Shrinking the database improves the odds of winning that race but doesn't change the race.

So #605 (bounded prune batches) and any retention tuning are both worth having, but neither addresses the generator. The options that would:

• don't durably audit allowed decisions on read paths — keep denials (the security-relevant half, 3,480 rows here) and all mutations
• or coalesce: one row per principal/action/target per interval, rather than per request
• or make per-decision auditing opt-in, off by default

Happy to implement whichever shape you prefer. Flagging it rather than sending a patch because "what the audit trail must guarantee" is a policy decision, and dropping allowed-read records is exactly the kind of thing that shouldn't be decided from outside the project.

🤖 Generated with Claude Code