Skip to content

Commit 10d502d

Browse files
committed
t2174: feat(opencode): weekly DB maintenance routine (r913) for SQLite lock mitigation
Recurring "database is locked" errors in opencode sessions even with a single active TUI. Live log shows `+5015ms database is locked` from session.processor, confirming busy_timeout=5000ms IS honored but writers hold the lock longer — typically on large bash-tool output writes (see anomalyco/opencode#21000). Diagnostic found 10 opencode processes holding 20+ FDs on a 1.3 GB DB. The architectural fixes (sharding, retry-with-backoff) live upstream in anomalyco/opencode#21215, #21000, #20935, #21579 — all open. Until those land, smaller DB = shorter lock windows. This adds a weekly SQLite maintenance routine that runs wal_checkpoint(TRUNCATE), PRAGMA optimize, and a conditional VACUUM (size > 500 MB OR free-page fraction > 10%). - NEW .agents/scripts/opencode-db-maintenance-helper.sh — check/report/maintain/ auto/help subcommands. Silent no-op if opencode not installed. Refuses maintain when opencode processes are active unless --force. Auto mode throttled to 6-day cadence via last-run.json state. State under ~/.aidevops/.agent-workspace/work/opencode-maintenance/. - NEW .agents/scripts/tests/test-opencode-db-maintenance.sh — 11 sandbox assertions via XDG_DATA_HOME + HOME redirect. All 11 pass. - EDIT .agents/scripts/routines/core-routines.sh — register r913 in get_core_routine_entries and add describe_r913() (mirrors r906 calendar- scheduled pattern). Sunday 04:00 weekly. Ships to all users via aidevops update's _create_core_routine_issues. - NEW .agents/advisories/opencode-db-maintenance-2026-04.advisory — for existing users whose routines repos predate r913. - NEW .agents/reference/opencode-maintenance.md — user docs covering the problem, routine, subcommands, and architectural boundary (why we can't fix busy_timeout, mmap_size, or retry-with-backoff from outside opencode). Resolves #19629
1 parent db015de commit 10d502d

7 files changed

Lines changed: 1225 additions & 0 deletions

File tree

Lines changed: 34 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,34 @@
1+
[ROUTINE AVAILABLE] Weekly OpenCode DB Maintenance (r913, April 2026)
2+
3+
If you use OpenCode and occasionally see "database is locked" errors
4+
that halt your session mid-turn, this routine addresses it.
5+
6+
Root cause (anomalyco/opencode #21215, #21000, #20935): opencode's
7+
SQLite session DB can grow to 1+ GB. Each opencode process opens
8+
multiple connections, so even a single TUI is multi-writer at the
9+
SQLite layer. Long writes exceed the 5s busy_timeout and surface
10+
as session-halting lock errors.
11+
12+
The new r913 routine (weekly Sunday 04:00) runs wal_checkpoint,
13+
PRAGMA optimize, and a conditional VACUUM to keep the DB compact
14+
and the WAL truncated — shorter lock windows, fewer failures.
15+
16+
For NEW aidevops installs: r913 is seeded automatically.
17+
18+
For EXISTING installs (you already have a routines repo), enable it:
19+
20+
cd ~/Git/aidevops-routines # or your private routines repo path
21+
# Add to TODO.md under "## Core Routines":
22+
- [x] r913 Weekly opencode DB maintenance repeat:weekly(sun@04:00) ~2m run:scripts/opencode-db-maintenance-helper.sh auto
23+
24+
Or run it ad-hoc any time:
25+
26+
opencode-db-maintenance-helper.sh report # current DB stats
27+
opencode-db-maintenance-helper.sh check # preflight
28+
opencode-db-maintenance-helper.sh maintain # run once (close TUIs first)
29+
30+
Dismiss this advisory once enabled:
31+
32+
aidevops security dismiss opencode-db-maintenance-2026-04
33+
34+
Upstream tracking: https://github.com/anomalyco/opencode/issues/21215
Lines changed: 163 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,163 @@
1+
<!-- SPDX-License-Identifier: MIT -->
2+
<!-- SPDX-FileCopyrightText: 2025-2026 Marcus Quinn -->
3+
4+
# OpenCode Database Maintenance
5+
6+
Periodic SQLite maintenance for opencode's session database to reduce
7+
"database is locked" errors under concurrent session load.
8+
9+
## The problem
10+
11+
opencode stores session state in `~/.local/share/opencode/opencode.db`
12+
(SQLite with WAL journaling). Under heavy use:
13+
14+
- **DB grows large**. Active users routinely accumulate 1 GB+ of message
15+
and part rows. `message` and `part` dominate the schema (typical ratio:
16+
65% message, 30% part, 5% indexes).
17+
- **Multiple connections per process**. Each opencode binary opens at
18+
least 2 DB connections — one for reads, one for writes. A single TUI
19+
with 10+ FDs on the DB is already multi-writer from SQLite's perspective.
20+
- **WAL single-writer limit**. SQLite WAL allows concurrent readers but
21+
only *one* writer at a time. When a write transaction runs long
22+
(large tool outputs are a common trigger — see anomalyco/opencode
23+
#21000), other writers block.
24+
- **busy_timeout caps retry time**. opencode applies `PRAGMA busy_timeout
25+
= 5000ms` on its connections. When a writer exceeds 5 seconds, retries
26+
exhaust and SQLite returns `SQLITE_BUSY`, surfacing as the red "database
27+
is locked" banner. The session halts mid-turn.
28+
29+
The architectural fix is upstream — per-session-tree sharding (PR
30+
anomalyco/opencode#21579) or switching to a multi-writer backend. Until
31+
that lands, the best mitigation is to keep the DB compact and the WAL
32+
truncated so write transactions hold the lock for as short a time as
33+
possible.
34+
35+
## What this routine does
36+
37+
`r913 Weekly opencode DB maintenance` runs every Sunday at 04:00 local
38+
time and invokes `opencode-db-maintenance-helper.sh auto`, which:
39+
40+
1. **Guards**. Exits 0 silently if opencode isn't installed. Throttles
41+
if the last successful run was within 6 days. Refuses to run while
42+
opencode processes are active (requires exclusive DB access).
43+
2. **wal_checkpoint(TRUNCATE)**. Folds pending WAL writes back into the
44+
main DB and truncates the WAL file to zero. Prevents WAL bloat during
45+
the following week's burst writes.
46+
3. **PRAGMA optimize**. Refreshes query planner statistics so common
47+
session queries pick the right indexes.
48+
4. **VACUUM** (conditional). Rewrites the DB file, reclaiming free pages
49+
from deleted rows (opencode prunes old sessions in the background).
50+
Runs when the DB is larger than 500 MB or free pages exceed 10% of
51+
total pages. Typical reclaim: 20–40% on DBs with heavy prune activity.
52+
53+
## Subcommands
54+
55+
```bash
56+
opencode-db-maintenance-helper.sh check # preflight: DB exists, no locks, integrity OK
57+
opencode-db-maintenance-helper.sh report # stats (size, pages, free list, top tables)
58+
opencode-db-maintenance-helper.sh maintain # run once (refuses if processes active)
59+
opencode-db-maintenance-helper.sh maintain --force # run anyway (may cause session errors)
60+
opencode-db-maintenance-helper.sh auto # scheduled mode (silent, throttled)
61+
opencode-db-maintenance-helper.sh help
62+
```
63+
64+
### Sample output
65+
66+
```
67+
== OpenCode DB Report ==
68+
69+
Path: /Users/alice/.local/share/opencode/opencode.db
70+
DB size: 1.22 GB
71+
WAL size: 3.0 MB
72+
Pages: 320588 (page_size=4096B)
73+
Free pages: 29312 (9.14% of total)
74+
75+
PRAGMAs (fresh CLI connection — not what opencode uses):
76+
journal_mode = wal
77+
synchronous = 1
78+
busy_timeout = 0
79+
mmap_size = 0
80+
81+
Top 5 tables/indexes by size:
82+
message 793.3 MB
83+
part 561.2 MB
84+
part_message_id_id_idx 12.0 MB
85+
part_session_idx 6.7 MB
86+
sqlite_autoindex_part_1 6.7 MB
87+
```
88+
89+
### Why the PRAGMA section says `busy_timeout = 0`
90+
91+
The PRAGMAs displayed are from a *fresh* `sqlite3` CLI connection, not
92+
from opencode's internal connections. opencode applies
93+
`PRAGMA busy_timeout = 5000` at connection open time, but that setting
94+
is per-connection and does not persist to the DB file. The report
95+
surfaces the SQLite defaults so you can see what the DB file itself
96+
knows about — useful when diagnosing edge cases where opencode opens
97+
a new connection path that forgets to apply the standard pragmas.
98+
99+
## Configuration
100+
101+
All thresholds overrideable via environment variables:
102+
103+
| Variable | Default | Description |
104+
|---|---|---|
105+
| `VACUUM_FREELIST_THRESHOLD` | `0.10` | VACUUM if free-page fraction >= this |
106+
| `FORCE_VACUUM_SIZE_MB` | `500` | Always VACUUM above this size (MB) |
107+
| `AUTO_MIN_SECONDS_BETWEEN` | `518400` | Throttle for `auto` mode (6 days) |
108+
109+
Example: force VACUUM on every run regardless of fragmentation:
110+
111+
```bash
112+
FORCE_VACUUM_SIZE_MB=0 VACUUM_FREELIST_THRESHOLD=0.0 \
113+
opencode-db-maintenance-helper.sh maintain
114+
```
115+
116+
## Safety
117+
118+
- **Never runs with active opencode processes** in `auto` or plain
119+
`maintain` mode. A running opencode TUI holds WAL locks; VACUUM
120+
requires exclusive access and would fail or (worse) conflict. The
121+
check uses `pgrep` for `opencode-ai/bin/.opencode`.
122+
- **No data loss from VACUUM**. SQLite VACUUM rewrites the DB file
123+
page by page, preserving every row. If interrupted, SQLite's
124+
journal restores the previous state.
125+
- **Throttled**. `auto` mode skips if `last-run.json` shows success
126+
within the last 6 days. Manual `maintain` is not throttled.
127+
128+
## State
129+
130+
- `~/.aidevops/.agent-workspace/work/opencode-maintenance/last-run.json`
131+
— last run outcome, timestamps, reclaimed bytes, VACUUM decision
132+
- `~/.aidevops/.agent-workspace/work/opencode-maintenance/maintenance.log`
133+
— append-only history
134+
135+
## Related upstream issues
136+
137+
- [anomalyco/opencode#21215](https://github.com/anomalyco/opencode/issues/21215)
138+
— opencode run: concurrent sessions crash with SQLITE_BUSY
139+
- [anomalyco/opencode#21000](https://github.com/anomalyco/opencode/issues/21000)
140+
— Bash tool hangs on fast-exiting processes and locks database on massive output
141+
- [anomalyco/opencode#20935](https://github.com/anomalyco/opencode/issues/20935)
142+
— Per-session-tree database sharding (architectural fix, open)
143+
- [anomalyco/opencode#21579](https://github.com/anomalyco/opencode/pull/21579)
144+
— Harden per-session SQLite sharding (PR, open)
145+
- [anomalyco/opencode#19521](https://github.com/anomalyco/opencode/issues/19521)
146+
— database is locked (original report)
147+
148+
## What aidevops cannot fix from outside opencode
149+
150+
These require opencode source changes (tracked upstream):
151+
152+
- Raising `busy_timeout` from 5000ms → 30000ms (per-connection, must
153+
be set by opencode at connection open)
154+
- Enabling `mmap_size` (per-connection — would significantly reduce
155+
read contention by memory-mapping the DB file)
156+
- Retry-with-exponential-backoff on `SQLITE_BUSY` in the write path
157+
(write wrapper change in opencode's session.processor service)
158+
- Switching long `BEGIN IMMEDIATE` transactions to `BEGIN DEFERRED`
159+
for read paths (transaction-mode choice in application code)
160+
- Session-tree DB sharding (PR #21579)
161+
162+
Periodic VACUUM + checkpoint is the only mitigation applicable from
163+
outside opencode's process.

0 commit comments

Comments
 (0)