[FEATURE]: Make SQLite PRAGMA settings configurable for long-running deployments #35892

The story

I was debugging why my web UI was getting slower the longer opencode ran. After a few months the SSE /global/event endpoint would routinely take 5-10 seconds to start streaming, then hiccup every few seconds. I dug into the DB and discovered three things I think are worth making configurable:

1. busy_timeout = 0 — the moment anything else is writing, SQLite just gives up instead of waiting. The agent loop and the web UI share one process and one DB. During a streaming response (lots of event inserts) the web frontend started getting database is locked errors mid-SSE instead of waiting 100ms.

2. synchronous = FULL — every commit does an fsync(). For an event-sourcing DB with many small writes this is the bottleneck. On spinning disks or high-latency storage it's the difference between 200 writes/s and 20.

3. cache_size = 2000 pages ≈ 8 MB — way too small once the DB passes ~100 MB. After a few months the event table alone was ~1 GB / ~170k rows on my install, the cache was completely cold, and every read turned into a disk seek.

I worked around it externally via sqlite3 CLI with PRAGMA busy_timeout=60000, but that's a per-connection setting the opencode process can't see. So this issue is really about making these settings reachable from opencode's own config.

What triggered the discovery: the event table

The reason the cache mismatch became so painful is the event table. opencode uses an event-sourcing pattern there — every state change (agent tool call, message part update, session update) is written as a row. After ~3 months of running, this single table was ~1 GB / ~170k rows in my install. The relevant types:

• message.part.updated.1 — 109k rows
• message.updated.1 — 47k rows
• session.updated.1 — 12k rows

So once the DB crosses ~100 MB, the default 8 MB cache is essentially useless, and the busy_timeout=0 becomes actively harmful (every concurrent commit is a coin flip on whether it fails immediately). This isn't about defaults being "wrong" in the abstract — it's about defaults being tuned for a developer tool used for an hour, with no path out for a long-running server-style deployment.

What I'd like

A database section in opencode.json:

{
  "database": {
    "busyTimeout": 5000,
    "synchronous": "NORMAL",
    "cacheSizeKb": 65536
  }
}

Equivalent CLI flags on opencode web:

• --busy-timeout <ms>
• --synchronous NORMAL|FULL|OFF|EXTRA
• --cache-size <KB>

Defaults stay exactly as-is for anyone not touching this section.

Validation

• Bad values fail at startup with a clear message, not silently
• Precedence: CLI > config > default (so one-off debugging via flag still works)

Reproduction (rough)

A user running opencode continuously for 3+ months with multiple chat sessions should be able to see PRAGMA busy_timeout returning 0 via:

sqlite3 ~/.local/share/opencode/opencode.db "PRAGMA busy_timeout; PRAGMA synchronous; PRAGMA cache_size;"

…and event table reaching the order of 100k+ rows / hundreds of MB.

Stretch goals (separate discussion if interested)

• journal_mode already defaults to WAL — could expose for symmetry
• An opencode db vacuum / opencode db gc subcommand for cleaning up old event rows
• A database.walAutocheckpoint knob for tuning checkpoint frequency