There’s a temptation, when you spot a problem in a personal project, to just fix it. Open the file, start editing, ship. It feels productive. It often isn’t.

This week I migrated the state layer of my personal AI assistant from a collection of flat JSON files to SQLite. The migration itself took about 20 minutes of actual work, spread across four automated phases. The planning took two hours. And the planning was the most valuable part.

Here’s why.

The Problem: JSON Files Everywhere

My assistant — a self-hosted OpenClaw instance — runs dozens of automated tasks: hourly email checks, daily news digests, monthly financial summaries, health tracking. Each of these needed to track state between runs. Over time, that state had accumulated into a small forest of JSON and JSONL files:

  • An email processed cache: a dict of 1,460 email IDs with their scores, senders, and subjects — rewritten from scratch on every hourly run
  • An email training log: 4,300+ append-only lines tracking which emails I’d flagged as important or spam
  • A sent articles list: 450+ URLs to prevent the news digest from sending the same story twice
  • Bank statement transactions: 600 rows from parsed PDF statements, used for monthly spend analysis
  • Four tiny config-like state files for various scripts

None of this was wrong, exactly. It worked. But it was getting fragile. The email cache file was 400 KB and growing with no cleanup mechanism. The training log had no way to query “which senders have I ignored most?” without loading the whole file. And crucially: the bank transaction file had 15 silent duplicates that nobody had ever noticed, because a flat list has no deduplication.

SQLite solves all of this — indexed lookups, proper deduplication via UNIQUE constraints, queryable history. One file to back up instead of seven.

The Plan: Writing It Down Before Starting

Before touching any code, I wrote a full migration plan in a Google Doc. This sounds obvious. In practice, most people skip it for personal projects. I’m glad I didn’t.

The plan forced me to think through things I would have hit mid-migration:

What stays JSON? Not everything should be migrated. Human-edited config files — merchant category mappings, budget allocations, email filter rules — are better as JSON. They’re read by humans, edited by humans, and live in version control. Migrating them to SQLite would add friction with no benefit. The plan made me be explicit about what wasn’t being migrated.

What are the real risks? The bank transaction data is critical. I use it for monthly financial reviews. A silent data corruption there is a real problem. That deserved a full test suite written before the migration ran — not after. Planning forced me to think about data integrity as a first-class concern, not an afterthought.

How do you roll back? This was the question that produced the most useful planning output. I defined a rollback procedure: back up all scripts and data files first, document the exact commands to restore them, define the criteria for deciding when to roll back (a table of symptoms and decisions). Having this written down in advance means you’re not making panicked decisions in the middle of a broken production system.

What are the GitHub constraints? Part of my email processing system is published as open source. Any SQLite migration had to keep the public repo clean of personal data — real email addresses, training history, my actual inbox patterns. The plan identified that the database file needed to be gitignored, that the one-time migration script should never be committed, and that the public test suite needed new fixtures using temporary in-memory databases.

The Migration: Four Clean Phases

With the plan written, the migration itself was methodical.

Phase 1: Email system. Replaced the processed cache (JSON dict) and training log (JSONL append) with two SQLite tables. Added injectable db_path arguments to the processor and trainer so tests never touch real data. Added 17 new tests to the public repo — all passing alongside the existing 11.

Phase 2: News digest state. Replaced the sent-articles list with a sent_articles table. A bonus: going forward, each article is stored with its source and title, not just a hash. Historical data has nulls for those fields, which is honest.

Phase 3: Bank transactions. This was the critical one. Tests first — 17 tests covering deduplication, data integrity, query correctness, and edge cases. Then migration. Result: 600 source records → 585 unique rows. The 15 silent duplicates were real: the same transaction appearing in two overlapping statement PDFs, stored twice in the old JSON list. SQLite’s UNIQUE constraint caught them on import. Monthly totals for the last three months matched exactly between the old and new storage.

Phase 4: Small state files. Four tiny JSON files consolidated into a single kv_state key-value table and a shared helper module. Cleaner, but not the valuable part of the day.

One File, One Backup

The most operationally satisfying outcome: everything is now in a single file — clawdbot.db — backed up nightly to Google Drive via SQLite’s built-in hot backup (which is safe even while the database is being written to). The backup script keeps 30 daily snapshots and auto-prunes older ones.

Previously, “backing up the state” meant remembering which JSON files existed and where they lived. Now it’s one file.

What Planning Actually Bought Me

The migration would probably have worked without a written plan. But:

  • I would have started with the email system without thinking about the bank data, and might have skipped the test-first discipline for the critical tables
  • I would have forgotten about the GitHub constraints until mid-phase and had to stop
  • I wouldn’t have defined rollback criteria in advance, which means I’d have been guessing under pressure if something had gone wrong
  • I might have migrated the human-edited config files too, creating unnecessary churn

Two hours of planning, 20 minutes of execution. The ratio felt right.

The principle generalises beyond migrations. Any change to a production system you depend on — even a personal one — benefits from writing down what you’re doing, why, what the risks are, and how you’ll know if it went wrong. It doesn’t need to be a formal spec. A Google Doc with bullet points is fine. The act of writing forces the thinking.

This assistant runs on OpenClaw — a self-hosted personal AI framework. The email processing engine is open source on GitHub.