SQLite Is Enough

I have 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 article is about the third thing, which sits between them: the place structured data goes when Markdown files stop being the right shape for it.

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

That recommendation gets a specific kind of pushback — that SQLite is a toy, a starter database, something you outgrow. It is not. It is the most widely deployed database engine in existence, it is in the browser you are reading this in, and the constraints that would push you off it are constraints a solo or three-lawyer practice does not have. What it does have is an ops budget of zero, which is the actual binding constraint and the one nobody designs for.

Every example below is invented — 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 places where I am relying on documented behavior rather than a test.

Why local-first fits a law practice specifically

Four properties of the work, and each of them points the same way.

The concurrency is one lawyer. Not one lawyer at a time — one lawyer, plus some background jobs that index a folder or pull a calendar overnight. The workload SQLite is genuinely bad at, many simultaneous writers, does not 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.

The backup story is “copy a file.” Not a dump, not a replication topology, not a point-in-time recovery procedure you have never tested. One file, plus one caveat I will come back to.

The data is confidential. This is the one that changes the calculus rather than merely simplifying it. 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. The disclosure surface is the disk and the person 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.” I quote it because cost and difficulty are the factors a technical decision actually moves, and a design with fewer components to secure is cheaper to secure. That is an observation about architecture, not ethics advice; every lawyer answers the rest in their own jurisdiction.

Against all that, running a database server buys you: nothing you need, and a service to patch, back up, monitor, and explain to whoever inherits your practice.

The schema

Here is a real starting schema for what I would call a practice brain — a queryable layer over matters, documents, what happened, and what is owed. It is not a practice management system and it is not where your trust ledger goes. It is 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 choices worth defending. 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 — this is the documented, intended approach, not a hack. CHECK constraints on status columns are free and catch the typo that would otherwise silently produce a matter in state 'Open' that never appears in a report again. tasks_open_by_due is a partial index: it only covers open tasks, which is the only kind you ever query by due date, and it stays small forever.

The observation log is append-only

observations is the table that makes this worth building, and it works differently from the others. You do not update an observation — you insert a new one, set supersedes if it replaces an earlier one, and leave the old row alone. The table only grows.

The reason is that in a practice, the record of what you knew and when you knew it is itself important. A row you overwrote is a fact you destroyed. An append-only log lets you ask the question a mutable table cannot 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 excluded the April one — the state of knowledge on the day, not the state of knowledge now.

The two timestamps are the other half of it. observed_at is when the fact was true; recorded_at is when it entered the system. Those differ constantly — you learn on Thursday about something that happened three weeks ago — and keeping them separate is the difference between a log you can reason about and a log that quietly rewrites history every time you backfill.

Current state is then a query rather than 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 on a matter page.

Open the connection correctly

Three pragmas, and two of them are not 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 decorative 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 nonexistent matter raised IntegrityError: FOREIGN KEY constraint failed, which is the whole point.

WAL mode is what makes a background job survivable. In the default rollback journal, a writer blocks readers. In WAL, readers and one writer proceed concurrently. I tested this directly: 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 did not see the uncommitted row. That is the situation you are actually in when an indexer is walking your document folder while you run a query.

busy_timeout is what you set instead of losing. SQLite allows one writer at a time; a second writer that cannot get the lock retries until the timeout expires and then raises. In my test, a second connection with a 500 ms timeout failed with OperationalError: database is locked — which is correct behavior, and which is what you will see in production if a background writer holds a transaction longer than your patience. Set the timeout generously and keep write transactions short.

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

FTS5 is the feature you are not using

This is the most underused thing in SQLite for this use case, and it is the reason a homemade practice brain can do something your practice management system cannot: search the contents of your documents, ranked, in milliseconds, offline.

Create an external-content FTS5 table — one that indexes another table’s columns without duplicating 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 do not update themselves. You keep them in sync with triggers, and the delete syntax is the part everyone gets wrong — you insert a special 'delete' command row rather than 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 must carry the old column values, because FTS5 uses them to work out which index entries to remove. Pass the new values and the index silently rots.

I tested the rot case and the correct case. 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 disagrees with the base table. Run it in your nightly job. It is the cheapest data-quality check in the system.

Now the query. bm25() returns a relevance score — more negative is better, which trips everyone up, so 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 is richer than most people expect. All of these ran against my fictional corpus:

The snippet returned for the phrase query was:

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

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

One caveat I did not test at scale: FTS5 indexes a scanned OCR layer as faithfully as it indexes clean text, so a bad OCR pass produces a confidently useless index. Index quality is upstream of the database.

Migrations without a framework

You will change this schema. Do it with PRAGMA user_version, an integer SQLite stores 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 on every startup. On a fresh file it went 0 → 3 and applied all three steps; run immediately again it applied nothing. Each step is atomic — if step three raises, steps one and two are committed, user_version is 2, and rerunning resumes at three.

Two rules. Never edit a migration that has shipped — add a new one. And note that PRAGMA user_version cannot 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 naive cp of a live SQLite database can produce 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 is explicit: 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 safe only when no transaction is in progress, and then only if you take the -wal file along with it. That is a precondition you cannot verify from a cron job.

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 preserved at 3, the row counts matched, and the FTS5 index worked in the copy. The snapshot’s journal mode was delete rather than WAL, which is the point — it is 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 a location that is not the same disk. Then restore one and open it, because a backup you have not restored is a hypothesis.

A trap worth showing

Here is a bug I hit writing the examples for this article, because it is the single most common SQL mistake 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;

Joining two one-to-many tables to the same parent produces 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 slightly less elegant and actually correct:

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. The general lesson: a dashboard number that is wrong is more dangerous than a dashboard you do not have, 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 large one.

When SQLite is the wrong answer

I would rather say this plainly than have you find out later.

Multiple concurrent writers across machines. SQLite allows one writer at a time and relies on the filesystem to implement locking correctly. Over a network share — SMB, NFS, or a consumer sync folder — that assumption does not hold. 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.” The failure mode is a corrupt file rather than an error message. If two people’s laptops need to write to the same database, you need a server. This is a hard line, not a tuning problem.

A team that needs shared server-side access. If the answer to “who can query this” is more than the people sitting at that machine, you are building a service, and a service wants a database designed for one.

A hosted multi-tenant product. If you are selling software to other firms and their data lives on your infrastructure, this article does not apply to 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 division is what makes the one-folder architecture and the database complement each other instead of competing.

Anything with a bright-line compliance obligation. The trust-accounting caution from the earlier article stands without qualification. This is not where your client-funds ledger goes.

Outside those, a single file will carry a small firm further than you expect, and the day you outgrow it you will know — a specific thing will stop working, rather than a vague feeling that you should have chosen something more serious.

The point

Every hour spent administering infrastructure produced nothing for a client and has to be recovered 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 requires no administration is not a compromise. It is the correct engineering answer to the actual problem, and it happens to be the cheap one. It is 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.