SQLite Is Enough

I’ve written that a practice management system is a relational database, some views, and an API, and that the firm’s institutional knowledge belongs in a folder you own. This one’s about the third thing, which sits between them: where structured data goes when Markdown files stop being the right shape for it.

For a small firm, the answer is one SQLite file. Not Postgres, not MySQL, not a hosted database service with a connection string and a monthly bill. A file.

Say that out loud and somebody always pushes back: SQLite’s a toy, a starter database, something you outgrow. It isn’t. It’s the most widely deployed database engine in existence, it’s in the browser you’re reading this in, and the limits that would push you off it are limits a solo or three-lawyer practice doesn’t have. What a small practice does have is an ops budget of zero. That’s the real constraint, and nobody designs for it.

Every example below is made up — fictional matters, fictional captions, a fictional file number. No client data appears in this article. I ran every SQL statement and every Python snippet here against SQLite 3.51.0 through Python’s standard-library sqlite3 module before publishing, and I flag the spots where I’m relying on documented behavior instead of a test.

Why local-first fits a law practice

Four things about the work, and they all point the same way.

The concurrency is one lawyer. Not one lawyer at a time. One lawyer, plus a few background jobs that index a folder or pull a calendar overnight. The one workload SQLite is genuinely bad at, lots of simultaneous writers, doesn’t exist here.

The working set is small. A practice with several hundred matters, tens of thousands of documents, and a decade of events is a database measured in hundreds of megabytes. Queries that would need careful indexing at a hundred million rows run instantly at a hundred thousand.

Backup is “copy a file.” Not a dump, not a replication topology, not a point-in-time recovery procedure you’ve never tested. One file, plus one catch I’ll come back to.

The data is confidential. This is the one that changes the math instead of just making it simpler. A database on your own machine has no network listener, no credentials to leak, no vendor employee with production access, and no breach-notification path that runs through somebody else’s incident response. What you have to protect is the disk and whoever’s holding the laptop.

Minn. R. Prof. Conduct 1.6(c) requires a lawyer to “make reasonable efforts to prevent the inadvertent or unauthorized disclosure of, or unauthorized access to, information relating to the representation of a client,” and Comment [17] lists the factors that go into “reasonable” — among them “the sensitivity of the information,” “the likelihood of disclosure if additional safeguards are not employed,” “the cost of employing additional safeguards,” and “the difficulty of implementing the safeguards.” Cost and difficulty are the two factors a technical choice actually moves, and fewer moving parts are cheaper to secure. That’s an engineering point, not ethics advice. Every lawyer answers the rest in their own jurisdiction.

Put a database server up against all that and here’s what it buys you: nothing you need, plus a service to patch, back up, monitor, and explain to whoever inherits your practice.

The schema

Here’s a real starting schema for what I’d call a practice brain — a layer you can query over matters, documents, what happened, and what’s owed. It’s not a practice management system, and it’s not where your trust ledger goes. It’s the layer that lets you ask questions.

CREATE TABLE matters (
    id          INTEGER PRIMARY KEY,
    file_no     TEXT    NOT NULL UNIQUE,
    caption     TEXT    NOT NULL,
    status      TEXT    NOT NULL DEFAULT 'open'
                CHECK (status IN ('open','stayed','closed')),
    opened_on   TEXT    NOT NULL,
    closed_on   TEXT
);

CREATE TABLE documents (
    id          INTEGER PRIMARY KEY,
    matter_id   INTEGER NOT NULL REFERENCES matters(id) ON DELETE CASCADE,
    rel_path    TEXT    NOT NULL,
    title       TEXT    NOT NULL,
    kind        TEXT    NOT NULL DEFAULT 'other',
    doc_date    TEXT,
    sha256      TEXT,
    body        TEXT,                       -- extracted / OCR'd text
    added_at    TEXT    NOT NULL DEFAULT (datetime('now')),
    UNIQUE (matter_id, rel_path)
);
CREATE INDEX documents_by_matter ON documents(matter_id, doc_date);

CREATE TABLE observations (
    id           INTEGER PRIMARY KEY,
    matter_id    INTEGER REFERENCES matters(id) ON DELETE CASCADE,
    document_id  INTEGER REFERENCES documents(id) ON DELETE SET NULL,
    kind         TEXT    NOT NULL,          -- posture | opposing | deadline | note
    body         TEXT    NOT NULL,
    source       TEXT    NOT NULL,          -- where this came from
    observed_at  TEXT    NOT NULL,          -- when it was true
    recorded_at  TEXT    NOT NULL DEFAULT (datetime('now')),  -- when we learned it
    supersedes   INTEGER REFERENCES observations(id)
);
CREATE INDEX observations_by_matter ON observations(matter_id, observed_at);

CREATE TABLE tasks (
    id           INTEGER PRIMARY KEY,
    matter_id    INTEGER REFERENCES matters(id) ON DELETE CASCADE,
    title        TEXT    NOT NULL,
    due_on       TEXT,
    status       TEXT    NOT NULL DEFAULT 'open'
                 CHECK (status IN ('open','done','cancelled')),
    created_at   TEXT    NOT NULL DEFAULT (datetime('now')),
    completed_at TEXT
);
CREATE INDEX tasks_open_by_due ON tasks(due_on) WHERE status = 'open';

A few of these choices need explaining. Dates are TEXT in ISO-8601 (2026-08-15) because SQLite has no date type and ISO-8601 strings sort and compare correctly as text. That’s the documented, intended approach, not a hack. CHECK constraints on status columns cost nothing and catch the typo that would otherwise quietly create a matter in state 'Open' that never shows up in a report again. tasks_open_by_due is a partial index: it only covers open tasks, which are the only ones you ever look up by due date, so it stays small forever.

The observation log only grows

observations is the table that makes this worth building, and it works differently from the rest. You don’t update an observation. You insert a new one, set supersedes if it replaces an earlier one, and leave the old row alone.

Here’s why. In a practice, the record of what you knew and when you knew it matters on its own. A row you overwrote is a fact you destroyed. An append-only log lets you ask the question a mutable table can’t answer:

-- What did this file look like as of March 15?
SELECT observed_at, kind, body
  FROM observations
 WHERE matter_id = 1 AND observed_at <= '2026-03-15'
 ORDER BY observed_at;

Against the fictional data I loaded, that returned the two observations recorded before that date and correctly left out the April one — what you knew that day, not what you know now.

The two timestamps are the other half. observed_at is when the fact was true; recorded_at is when it got into the system. Those differ all the time — you find out on Thursday about something that happened three weeks ago — and keeping them apart is the difference between a log you can reason about and one that quietly rewrites history every time you backfill.

Current state then becomes a query instead of a column:

SELECT kind, body, observed_at FROM (
    SELECT kind, body, observed_at,
           ROW_NUMBER() OVER (
               PARTITION BY kind ORDER BY observed_at DESC, id DESC) AS rn
      FROM observations WHERE matter_id = 1
) WHERE rn = 1;

Window functions have been in SQLite since 3.25. That query returned the latest posture note and the latest opposing-counsel note and nothing else, which is exactly the “where does this stand” summary you want at the top of a matter page.

Open the connection right

Three pragmas, and two of them aren’t defaults.

import sqlite3

def connect(path):
    conn = sqlite3.connect(path, timeout=30.0)
    conn.row_factory = sqlite3.Row
    conn.execute("PRAGMA journal_mode = WAL")     # persistent: set once, stays
    conn.execute("PRAGMA foreign_keys = ON")      # per-connection, OFF by default
    conn.execute("PRAGMA busy_timeout = 5000")
    conn.execute("PRAGMA synchronous = NORMAL")   # safe under WAL
    return conn

foreign_keys is off by default and is per-connection. I checked: a fresh sqlite3.connect reports PRAGMA foreign_keys = 0. Every REFERENCES clause in the schema above is decoration until you turn it on, on every connection, including the one your background job opens at 3 a.m. With it on, inserting a document against a matter that doesn’t exist raised IntegrityError: FOREIGN KEY constraint failed. That’s what you want.

WAL mode is what lets you live with a background job. In the default rollback journal, a writer blocks readers. In WAL, readers and one writer run at the same time. I tested it: with one connection holding an open BEGIN IMMEDIATE transaction and an uncommitted insert, a second connection read the table normally, saw the committed row count, and didn’t see the uncommitted row. That’s exactly where you are when an indexer is walking your document folder while you run a query.

busy_timeout is what you set so you don’t lose. SQLite allows one writer at a time. A second writer that can’t get the lock retries until the timeout runs out, then raises. In my test, a second connection with a 500 ms timeout failed with OperationalError: database is locked — which is correct, and which is what you’ll see in production if a background writer hangs onto a transaction longer than your patience. Set the timeout generously and keep write transactions short.

WAL creates two sidecar files, -wal and -shm, next to your database. They matter for backup, below.

FTS5 is the feature you’re not using

For this kind of work, nothing in SQLite gets ignored more than this. And it’s the reason a homemade practice brain can do something your practice management system can’t: search the contents of your documents, ranked, in milliseconds, offline.

Create an external-content FTS5 table — one that indexes another table’s columns without making a second copy of the text:

CREATE VIRTUAL TABLE documents_fts USING fts5(
    title, body,
    content='documents',
    content_rowid='id',
    tokenize='porter unicode61'
);

content='documents' means the index stores no copy of your text; it points at the base table. porter adds English stemming, so a search for allege finds alleges and allegation. In my test it found both.

External-content tables don’t update themselves. You keep them in sync with triggers, and the delete syntax is where everybody trips — you insert a special 'delete' command row instead of issuing a DELETE:

CREATE TRIGGER documents_ai AFTER INSERT ON documents BEGIN
    INSERT INTO documents_fts(rowid, title, body)
    VALUES (new.id, new.title, new.body);
END;

CREATE TRIGGER documents_ad AFTER DELETE ON documents BEGIN
    INSERT INTO documents_fts(documents_fts, rowid, title, body)
    VALUES ('delete', old.id, old.title, old.body);
END;

CREATE TRIGGER documents_au AFTER UPDATE ON documents BEGIN
    INSERT INTO documents_fts(documents_fts, rowid, title, body)
    VALUES ('delete', old.id, old.title, old.body);
    INSERT INTO documents_fts(rowid, title, body)
    VALUES (new.id, new.title, new.body);
END;

The 'delete' row has to carry the old column values, because FTS5 uses them to figure out which index entries to remove. Pass the new values and the index silently rots.

I tested both the rot case and the right way. After updating a document’s body, a search for a word only in the old text returned 0 rows and a word only in the new text returned 1. After deleting a document, a search for a word unique to it returned 0. And SQLite will tell you if you got it wrong:

INSERT INTO documents_fts(documents_fts) VALUES ('integrity-check');

That statement raises if the index and the base table disagree. Put it in your nightly job. It’s the cheapest data-quality check in the system.

Now the query. bm25() returns a relevance score, and more negative is better. That catches everybody, so yes, ORDER BY rank ascending is correct. The extra arguments are per-column weights:

SELECT d.rel_path, m.file_no, d.title,
       snippet(documents_fts, 1, '[', ']', ' … ', 12) AS hit,
       bm25(documents_fts, 2.0, 1.0) AS rank
  FROM documents_fts
  JOIN documents d ON d.id = documents_fts.rowid
  JOIN matters   m ON m.id = d.matter_id
 WHERE documents_fts MATCH ?
 ORDER BY rank
 LIMIT 10;

bm25(documents_fts, 2.0, 1.0) weights a hit in title twice as heavily as one in body. snippet() returns the matching text with the hit bracketed — column index 1 is body, and the last argument is the token budget.

The query language does more than most people expect. Every one of these ran against my fictional corpus:

Here’s the snippet the phrase query returned:

Plaintiff alleges the defendant furnished inaccurate information
to a [consumer reporting agency].

That’s ranked full-text search over your documents, with highlighted context, on a laptop, with no service running and no data leaving the building — maybe two hundred lines of code from nothing to working.

One catch I didn’t test at scale: FTS5 indexes a scanned OCR layer just as faithfully as clean text, so a bad OCR pass gets you a confidently useless index. Garbage in, garbage indexed. That problem lives upstream of the database.

Migrations without a framework

You will change this schema. Do it with PRAGMA user_version, an integer SQLite keeps in the file header and otherwise ignores.

MIGRATIONS = []

def migration(fn):
    MIGRATIONS.append(fn)
    return fn

@migration
def m001_core(conn):
    conn.executescript(""" ... the CREATE TABLEs above ... """)

@migration
def m002_fts(conn):
    conn.executescript(""" ... the FTS5 table and triggers ... """)

def migrate(conn):
    current = conn.execute("PRAGMA user_version").fetchone()[0]
    for i, step in enumerate(MIGRATIONS, start=1):
        if i <= current:
            continue
        with conn:                       # one transaction per step
            step(conn)
            conn.execute(f"PRAGMA user_version = {i}")

Run it every time the program starts. On a fresh file it went 0 → 3 and applied all three steps; run again right away, it applied nothing. Each step is atomic — if step three raises, steps one and two are committed, user_version is 2, and rerunning picks up at three.

Two rules. Never edit a migration that’s shipped. Add a new one. And PRAGMA user_version can’t be parameterized: PRAGMA user_version = ? raises OperationalError: near "?": syntax error, so that one value gets interpolated, and it had better be an integer you control.

Backup: VACUUM INTO, not cp

A plain cp of a live SQLite database can give you a corrupt copy, because the file can be mid-write and because in WAL mode the committed state is split between the database file and the -wal file. SQLite’s own corruption documentation spells it out: a background process that copies the file mid-transaction produces a backup that “might contain some old and some new content, and thus be corrupt.” Copying is only safe when no transaction is running, and only if you take the -wal file with it. A cron job has no way to know that.

The one-liner:

VACUUM INTO '/path/to/snapshot-2026-07-31.db';

That runs inside a transaction, takes a consistent view of the database, and writes a fresh, defragmented, single-file copy — no -wal, no -shm, nothing to forget. I ran it against a live database with a second connection attached, then opened the snapshot: PRAGMA integrity_check returned ok, user_version was still 3, the row counts matched, and the FTS5 index worked in the copy. The snapshot’s journal mode was delete, not WAL, and that’s the point. It’s a plain, portable, single file.

From Python, the backup API does the same job and can run incrementally:

dest = sqlite3.connect("/path/to/snapshot.db")
with dest:
    conn.backup(dest)          # safe against a live source
dest.close()

Either one, on a schedule, to somewhere that isn’t the same disk. Then restore one and open it. A backup you’ve never restored is a hypothesis.

A trap I fell into

I hit this bug writing the examples for this article, and it’s the most common SQL mistake there is in this kind of dashboard query.

-- WRONG: fans out
SELECT m.file_no,
       COUNT(DISTINCT d.id) AS docs,
       SUM(CASE WHEN t.status='open' THEN 1 ELSE 0 END) AS open_tasks
  FROM matters m
  LEFT JOIN documents d ON d.matter_id = m.id
  LEFT JOIN tasks     t ON t.matter_id = m.id
 GROUP BY m.id;

Join two one-to-many tables to the same parent and you get the cross product. My fictional matter had 3 documents and 2 open tasks; that query reported open_tasks: 6. The COUNT(DISTINCT) on documents hid half the problem, which is worse than if it had been wrong too. Correlated subqueries are a little less elegant and actually right:

CREATE VIEW open_matter_load AS
SELECT m.file_no, m.caption,
       (SELECT COUNT(*) FROM documents d WHERE d.matter_id = m.id) AS docs,
       (SELECT COUNT(*) FROM tasks t
         WHERE t.matter_id = m.id AND t.status='open') AS open_tasks,
       (SELECT MIN(t.due_on) FROM tasks t
         WHERE t.matter_id = m.id AND t.status='open') AS next_due,
       (SELECT MAX(o.observed_at) FROM observations o
         WHERE o.matter_id = m.id) AS last_seen
  FROM matters m
 WHERE m.status = 'open';

That version returned 3 and 2. A wrong number on a dashboard is more dangerous than no dashboard at all, and aggregate queries over joins are where wrong numbers come from. Check them against a hand count on a small dataset before you trust them on a big one.

When SQLite is the wrong answer

Several writers at once, on different machines. SQLite allows one writer at a time and counts on the filesystem to handle locking correctly. Over a network share — SMB, NFS, or a consumer sync folder — you can’t count on that. SQLite’s documentation names network filesystems and NFS specifically, and says that if two processes access the database at the same time on a filesystem with buggy locking, “database corruption might result.” When it fails, you don’t get an error message. You get a corrupt file. If two people’s laptops need to write to the same database, you need a server. That’s a hard line, not something you tune your way past.

A team that needs shared server-side access. If the answer to “who can query this” goes beyond the people sitting at that machine, you’re building a service, and a service wants a database built for one.

A hosted multi-tenant product. If you’re selling software to other firms and their data lives on your infrastructure, this article isn’t for you.

Very large binary payloads. Keep PDFs on the filesystem and store the path and hash. The database indexes the text; the folder holds the bytes. That split is what lets the one-folder architecture and the database work together instead of fighting.

Anything with a bright-line compliance obligation. The trust-accounting warning from the earlier article stands, no exceptions. This is not where your client-funds ledger goes.

Outside those, one file will carry a small firm further than you’d think. And the day you outgrow it, you’ll know, because something specific will stop working. It won’t be a vague feeling that you should have picked something more serious.

Why this matters

Every hour you spend babysitting infrastructure produced nothing for a client, and it has to get paid for somewhere in what an hour of your time costs. That cost is the floor under legal services, and the floor is what prices people out.

A database that needs no babysitting isn’t a compromise. It’s the right engineering answer to the real problem, and it happens to be the cheap one. It’s one file. Start it today, and put the first VACUUM INTO on a schedule before you put anything real in it.

Sources

• SQLite — FTS5 (external content tables, sync triggers, bm25(), snippet(), integrity-check), WAL mode, PRAGMA user_version / foreign_keys / busy_timeout, VACUUM INTO, backup API, date and time functions, how to corrupt a database file (network filesystems; copying a live database)
• Python sqlite3 module — Connection.backup(), Row, transaction handling. All examples tested against SQLite 3.51.0.
• Minn. R. Prof. Conduct 1.6(c) and cmt. [17] (reasonable efforts to prevent inadvertent or unauthorized disclosure; factors including sensitivity of the information, likelihood of disclosure, cost of employing additional safeguards, and difficulty of implementing them) — verified against the current rule text

General commentary on practice management and tooling. Not legal advice and not ethics advice. No client information appears in this article — every matter, caption, file number, document, and observation above is invented. Questions about any of this: Send us a message or call 612-470-6529.