The CLI 1.1.13 notes mention a fix for something that had been quietly compounding: every background task and subagent start appended duplicate grant and settings rows, so the conversation database grew without bound.
That one landed close to home. As an indie developer I run a handful of wallpaper apps, and the daily asset pipeline — generating eight derivative sets from source material, checking for duplicates and country of origin, sorting everything into thirty categories — is split across background tasks and subagents, one per stage. That shape produces a lot of starts per day. A bug that costs you something on every start hits this kind of setup first.
Upgrading stops the bleeding, but only for rows not yet written. What is already in the file stays there. So before upgrading, I wanted to count what had accumulated.
Two of my assumptions about deleting and reclaiming turned out to be wrong, and the second one was expensive. Here is how both failed, and what I ended up putting into the actual operation.
Counting Without Knowing the Schema
I gave up on one thing immediately: tracking the conversation database schema across versions. The CLI moved from 1.1.12 to 1.1.13 in a matter of days, and table and column names are internal implementation details. They are not something you should build tooling against.
Instead I only used methods that work with zero schema knowledge. SQLite gives you sqlite_master and dbstat, which is enough to get per-table page usage without knowing a single table name.
Opening read-only matters too. With a file:...?mode=ro URI you can audit while the CLI is running. Outside the brief exclusive window a writer holds right at commit, reads go through fine, and in WAL mode reads succeed even while a write transaction is open. I confirmed both journal modes locally.
The script below is what runs in the weekly audit.
#!/usr/bin/env python3
"""Audit a conversation database by table size and by duplicate appends.
Usage: python3 agdb_audit.py /path/to/conversation.db
Opens read-only, so it is safe to run while the CLI is up."""
import sys, sqlite3, os
def open_ro(path):
# mode=ro means we never have to stop the writer.
return sqlite3.connect(f"file:{path}?mode=ro", uri=True)
def table_bytes(cur):
"""Per-table page usage via dbstat, or None if dbstat is unavailable."""
try:
rows = cur.execute(
"SELECT name, SUM(pgsize) FROM dbstat GROUP BY name ORDER BY 2 DESC"
).fetchall()
except sqlite3.OperationalError:
return None
return [(n, b) for n, b in rows if b]
def table_bytes_fallback(cur):
"""For builds without dbstat: sum the real byte length of every column."""
out = []
names = [r[0] for r in cur.execute(
"SELECT name FROM sqlite_master WHERE type='table' AND name NOT LIKE 'sqlite_%'")]
for n in names:
cnt = cur.execute(f'SELECT COUNT(*) FROM "{n}"').fetchone()[0]
if cnt == 0:
out.append((n, 0, 0))
continue
cols = [r[1] for r in cur.execute(f'PRAGMA table_info("{n}")')]
expr = " + ".join(f'COALESCE(LENGTH(CAST("{c}" AS BLOB)),0)' for c in cols)
total = cur.execute(f'SELECT SUM({expr}) FROM "{n}"').fetchone()[0] or 0
out.append((n, total, cnt))
return sorted(out, key=lambda r: -r[1])
def duplicate_report(cur, min_repeat=3, top=8):
"""Count rows that match on every non-primary-key column.
Append-on-every-start bloat shows up here."""
result = []
names = [r[0] for r in cur.execute(
"SELECT name FROM sqlite_master WHERE type='table' AND name NOT LIKE 'sqlite_%'")]
for n in names:
info = list(cur.execute(f'PRAGMA table_info("{n}")'))
cols = [r[1] for r in info if not r[5]] # r[5] == 1 marks a PK column
if not cols:
continue
key = ", ".join(f'"{c}"' for c in cols)
total = cur.execute(f'SELECT COUNT(*) FROM "{n}"').fetchone()[0]
if total == 0:
continue
distinct = cur.execute(
f'SELECT COUNT(*) FROM (SELECT DISTINCT {key} FROM "{n}")').fetchone()[0]
dup = total - distinct
if dup <= 0:
continue
worst = cur.execute(
f'SELECT COUNT(*) AS c, {key} FROM "{n}" GROUP BY {key} '
f'HAVING c >= ? ORDER BY c DESC LIMIT ?', (min_repeat, top)).fetchall()
result.append((n, total, distinct, dup, worst))
return sorted(result, key=lambda r: -r[3])
def main():
if len(sys.argv) < 2:
print(__doc__)
sys.exit(2)
path = sys.argv[1]
if not os.path.exists(path):
print(f"No such file: {path}")
sys.exit(1)
con = open_ro(path)
cur = con.cursor()
psize = cur.execute("PRAGMA page_size").fetchone()[0]
pages = cur.execute("PRAGMA page_count").fetchone()[0]
free = cur.execute("PRAGMA freelist_count").fetchone()[0]
size = os.path.getsize(path)
print(f"[file] {size:,} bytes / page_size={psize} / page_count={pages:,}")
print(f"[file] freelist={free:,} pages = {free * psize:,} bytes")
print("\n[size breakdown]")
tb = table_bytes(cur)
if tb:
for n, b in tb[:12]:
print(f" {b:>12,} bytes {b * 100.0 / size:5.1f}% {n}")
else:
print(" dbstat unavailable, showing approximate row-data totals")
for n, b, cnt in table_bytes_fallback(cur)[:12]:
print(f" {b:>12,} bytes {cnt:>9,} rows {n}")
print("\n[duplicate appends]")
rep = duplicate_report(cur)
if not rep:
print(" no fully duplicated rows")
for n, total, distinct, dup, worst in rep[:6]:
print(f" {n}: {dup:,} of {total:,} rows are duplicates"
f" ({distinct:,} distinct / {dup * 100.0 / total:.1f}%)")
for row in worst[:3]:
vals = " | ".join(str(v)[:40] for v in row[1:])
print(f" x{row[0]:<5} {vals}")
con.close()
if __name__ == "__main__":
main()When dbstat Is There and When It Is Not
dbstat is a virtual table, available only in SQLite builds compiled with SQLITE_ENABLE_DBSTAT_VTAB. The SQLite 3.37.2 bundled with the Python 3 on my machine has it. Where it is missing, the script does not fail silently — it falls back to summing the real byte length of every column of every row.
That approximation excludes page overhead and indexes, so it reports less than the file size. The ranking of which table dominates does not change, which is the part you actually make decisions with.
Reading the Output
Run against a database that reproduces the append-on-every-start pattern (roughly four hundred conversations), the output looks like this.
[file] 4,046,848 bytes / page_size=4096 / page_count=988
[file] freelist=0 pages = 0 bytes
[size breakdown]
2,469,888 bytes 61.0% messages
925,696 bytes 22.9% settings
618,496 bytes 15.3% grants
16,384 bytes 0.4% conversations
[duplicate appends]
settings: 12,000 of 24,000 rows are duplicates (12,000 distinct / 50.0%)
grants: 8,800 of 9,600 rows are duplicates (800 distinct / 91.7%)
x12 conv-0000 | tool.run_command | {"allow": ["git status", "npm test"]}
x12 conv-0000 | tool.write_file | {"allow": ["src/**"]}
That 91.7% jumps out. It is where you want to start, and it is where I started.
One caveat before the numbers: every figure in this article comes from a database I built locally to reproduce the append pattern described in the published fix. It is not a measurement of the product's own database. Read it as a demonstration of the mechanism, not as a benchmark of your installation.
Deletion Reclaims Exactly Zero Bytes
I removed the duplicate rows and checked the file size. It had not moved.
Three deletion strategies, each applied to a fresh copy of the same database:
| What was deleted | Before | Right after delete | Freelist growth |
|---|---|---|---|
| Duplicate grants only | 4,046,848 bytes | 4,046,848 bytes | 135 pages (552,960 bytes) |
| Duplicate settings only | 4,046,848 bytes | 4,046,848 bytes | 1 page (4,096 bytes) |
| Half the old conversations, messages included | 4,046,848 bytes | 4,046,848 bytes | 305 pages (1,249,280 bytes) |
A SQLite DELETE returns emptied pages to the freelist and never truncates the file, so the space is available for the next insert. That is sensible for a single-process application, but if your goal was disk headroom, deletion accomplished nothing at all.
That was the first wrong assumption: I felt like I had cleaned up, and not one byte came back.