A Searchable Memory: Archiving Text History Into a Queryable Database

A Searchable Memory: Archiving Text History Into a Queryable Database

Why build this at all

Sometimes you want to check whether you handled a conversation right, or talk through what happened without pulling another person into it — and a phone’s native messaging app isn’t built for that kind of review. SMS Archive and Search turns years of text history into something actually searchable: a personal database that doesn’t depend on the phone still having the messages, and doesn’t depend on the carrier either.

The pipeline

SMS Backup & Restore (a free Android app) exports SMS/MMS on a schedule — incrementals every few hours, plus a periodic full backup — to a cloud drive. From there, my NAS’s cloud-sync feature pulls those exports down automatically, landing them somewhere an AI agent’s machine can actually reach without me moving anything by hand. A watcher script polls for new export files, confirms each has genuinely finished transferring (not just that it exists), splits large exports into safe chunks at message boundaries, and imports into a SQLite database — deduping by a content hash of contact + date + sender + body, so re-processing the same file twice is always a safe no-op. The watcher also classifies each file as full versus incremental and cleans up accordingly: incrementals get deleted from storage right after import (already safely in the DB), while only the most recent full backup is kept as a genuine from-scratch fallback.

The step that determines whether the whole project works or doesn’t

Before writing a single line of the importer, I verified the one link the entire pipeline actually depends on: does a file exported from the phone actually reach storage the AI agent’s machine can read, automatically, with no manual step? That’s the thing worth testing with one file before building anything else — if that connection doesn’t actually work (wrong sync direction, wrong account, a permissions issue), nothing built on top of it will either, and it’s far cheaper to catch with one test file than after the importer, splitter, and watcher are all built around an assumption that never held.

Two real bugs that only showed up under real data

• A loose full/incremental detection bug. My first pass at classifying export files did a naive text match for a tag identifying full vs. incremental exports — which broke on a real file where a stylesheet reference near the top of the XML happened to contain a matching substring before the actual root element did. The fix was anchoring the check to the real root element, not “the first line containing this text.”
• A silent large-file failure that wasn’t what it looked like. A sufficiently large export died mid-import in a way that looked like it should be a memory issue — it wasn’t; it was an execution-time ceiling in the automation environment running the script, not an OS-level resource limit. The fix (chunking large files defensively, split only at safe message boundaries) mattered regardless of the specific cause, but tracing the actual cause first kept me from “fixing” the wrong layer.

Treating this data like what it actually is

This is a full, permanent personal text history sitting in one file — I treat the database itself like a genuinely sensitive document (backup encryption, real file permissions, being deliberate about what else has access to the machine). Agent access starts read-only by default; write access, if ever granted, requires a backup first for anything non-trivially reversible, and stays out of the pipeline’s own design decisions (retention policy, schedule) without a direct check-in first.

What it demonstrates

Real debugging under real, messy data — both bugs here only showed up once actual export files were run through the pipeline, not in a clean test case, and both required tracing past the surface symptom to the real cause rather than patching the first plausible guess. It’s also a clear example of testing the riskiest, most foundational assumption first (the storage-to-AI link) before investing in anything built on top of it, and of treating a sensitive personal dataset with commensurate care rather than default-open access just because it’s convenient.

Part of the Homelab AI Blueprints series — see all nine blueprints for building and operating a self-hosted, AI-agent-run home server.