You
SQLite has no date type. What do people store?
ChatGPT
Integer Unix seconds for anything you compare or sort, and ISO-8601 text only when humans read the file directly. Mixing the two in one column is the failure mode, because comparisons then silently sort text against numbers.
You
You
My SQLite file never shrinks after deletes.
ChatGPT
Freed pages are reused, not returned, unless you ask:
PRAGMA auto_vacuum = INCREMENTAL; -- set before the first table is created
PRAGMA incremental_vacuum(1000);
On an existing database only a full VACUUM reclaims space, and it needs room for a second copy of the file while it runs.
You
Can SQLite do decent full text search on a few hundred thousand rows?
ChatGPT
Yes, FTS5 handles that size comfortably:
CREATE VIRTUAL TABLE notes_fts USING fts5(title, body, content=notes);
INSERT INTO notes_fts(notes_fts) VALUES('rebuild');
SELECT title, snippet(notes_fts, 1, '<b>', '</b>', '...', 12)
FROM notes_fts WHERE notes_fts MATCH 'archive NEAR/5 export';
The snippet function is the part people miss: it returns the matching fragment, which is what makes results readable rather than a list of titles.