Skip to content

Commit 6f6c0e5

Browse files
authored
t2174: feat(opencode): weekly DB maintenance routine (r913) for SQLite lock mitigation (#19630)
* 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 * t2174: drop redundant advisory — aidevops update already seeds new core routines The advisory was added under the mistaken assumption that existing users' routines repos needed manual intervention to pick up r913. They don't. aidevops update runs setup.sh --non-interactive, which invokes _setup_run_non_interactive → setup_routines → detect_and_create_all → init_personal → _create_core_routine_issues. That function iterates over EVERY entry in get_core_routine_entries and idempotently creates a tracking issue for each one (routine-log-helper.sh create-issue is idempotent per t1964). So on the next auto-update after this PR merges, every existing user's routines repo automatically gets r913's tracking issue seeded. No advisory, no manual step, nothing for the user to notice or dismiss. Advisories are for changes that require manual user action (credentials, config edits, new permissions). Adding a new core routine that ships with everything it needs is not one of those. Brief and TODO.md updated to reflect the simpler flow. * t2174: refactor cmd_maintain into helper functions to clear complexity gate CI's shell function complexity check flagged cmd_maintain at 126 body lines (threshold 100). Split it into four focused functions: - _maintain_preflight: args validation, opencode/sqlite check, process refusal - _maintain_should_vacuum: threshold evaluation (size + freelist fraction) - _maintain_run_steps: the three sqlite operations (checkpoint/optimize/vacuum) - _maintain_write_state: last-run.json write and summary output - cmd_maintain: orchestrator (~50 lines) All four helpers communicate via return codes and stdout lines (print_* go to stderr so they don't contaminate command substitution). Return code 100 from _maintain_preflight signals "clean no-op" (opencode not installed) so the orchestrator can distinguish it from actual errors. Behaviour unchanged — 11/11 tests still pass. No new complexity violations per complexity-regression-helper.sh. * t2174: address CodeRabbit review — ordering, dead var, fence lang, polish bundle Six findings from CodeRabbit on PR #19630: Actionable: - _opencode_process_count: broaden pgrep regex from "opencode-ai/bin/\.opencode" to "(^|/)\.?opencode($|[[:space:]])" + add lsof fallback on DB file. Covers Homebrew, manual, and npm installs; dedups by PID. - cmd_auto: translate cmd_maintain exit 2 (opencode active) to exit 0 so scheduler doesn't flag expected "busy" skips as failures. Nitpicks: - describe_r913 moved from between r911 and r912 to after r912, restoring strict numeric ordering of describe_rNNN functions. - Removed unused HOME_BACKUP export in test-opencode-db-maintenance.sh (HOME is set per-invocation via _run_helper subprocess, never mutated in the parent shell). - _db_size_human: replaced bc-dependent printf floats with pure-bash integer fixed-point math. Fixes the "536870912.0 MB" failure mode on bc-less hosts. - VACUUM: capture stderr into a variable before print_error so SQLite error text doesn't spam the terminal ahead of our own message. - cmd_auto throttle parsing: jq-first with grep/sed fallback, matching the existing pattern in cmd_report. - opencode-maintenance.md: added "text" language tag to sample-output fence for markdownlint MD040. All 11 tests still pass; ShellCheck clean. For #19629
1 parent dc529d8 commit 6f6c0e5

6 files changed

Lines changed: 1276 additions & 0 deletions

File tree

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+
```text
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)