These sources concern how data stays correct and how it gets found. Most involve SQLite or DuckDB, which run inside the program using them. This page lists eight sources, oldest first where dated.
Bloom Filters
This is a teaching post with interactive diagrams of a 32-bit filter. It walks through hashing, false positives, tuning, and why items cannot be removed. Evidence is worked examples, graphs, and two real-world uses. The interactive widgets could not be run in the text copy.
Stevens: a hackable AI assistant using a single SQLite table and a handful of cron jobs
The post describes Stevens, a household assistant that sends a daily brief. Its memory is one SQLite table fed by scheduled importers. Evidence is the author's own working project and a code link. No tests or comparisons are given.
The World's 2.75B Buildings
The post covers GlobalBuildingAtlas, a world dataset of building footprints. The author converts 1.1 TB of GeoJSON into 210 GB of Parquet. Evidence is the author's own DuckDB queries and map renderings. Some compare other datasets.
How To Corrupt An SQLite Database File
This is the SQLite project's own catalogue of corruption causes. It groups them into eight kinds, from rogue writes to library bugs. Evidence is the maintainers' incident reports. It also gives a full account of corruption bugs from 2009 to 2013.
DuckDB Internals: Why is DuckDB Fast? (Part 1)
This is part one of three, written by the CEO of Greybeam. It covers in-process execution, the parser, optimizer, pipelines, and columnar storage. Evidence is explanation, code snippets, and cited papers. It offers no benchmarks of its own.
Prefer STRICT tables in SQLite
The post argues that STRICT tables catch type mistakes that normal SQLite tables allow. It lists benefits and drawbacks, and notes that the SQLite developers disagree. Evidence is short code examples and one quick, admittedly hacky, speed test.
How we tracked down a 16-year-old SQLite bug
This is a postmortem by Tailscale about 19 corruption incidents in its control plane databases. It describes forensic logging, a support contract with the SQLite developers, and a diagnostic shim. The evidence is the company's own production experience. A custom warning later fired in production.
SQLite performance tuning
The gist lists pragma settings to run on each connection. Others are run by hand. It adds notes on how write-ahead logging can misbehave. The source is short and offers the author's experience, with no benchmarks.
Connections to other subjects
These links are this wiki's own reading, not claims the sources make.
- How we tracked down a 16-year-old SQLite bug and Hunting a 16-year-old SQLite bug with TLA+: is dqlite affected?, from Checkable specs for coding agents. These describe one bug from two sides. Tailscale needed six months and 19 incidents to find it through telemetry. The model checker reached a counterexample in 20 states, once people knew what to model. Finding and confirming turn out to be different jobs.
- Bloom Filters and A catalog of side effects, from Compilers and interpreters. A Bloom filter may say maybe, but never a false no. An over-approximated effect may block a valid reorder, but never permits a wrong one. Both stay safe by erring in one chosen direction. What would a one-sided guarantee look like for agent memory?
- Stevens: a hackable AI assistant using a single SQLite table and a handful of cron jobs and The components of agent memory, from File-based agent memory. Martin calls memory a prediction of what will be needed later. Litt's assistant predicts with a calendar. It sends the coming week's entries plus undated background. A date column may be the cheapest forecast of relevance available.
- Bloom Filters and I accidentally turned LLM memory into program analysis, from File-based agent memory. Commenters report models that keep using conclusions already disproved. A standard Bloom filter has a similar difficulty: items cannot be deleted. Counting variants allow it, but give up the promise of no false negatives. Forgetting may be the expensive operation in any memory.
Glossary
- Bloom filter
- A compact structure that tests whether an item might be in a set.
- Building footprint
- The outline of a building as seen from above.
- Checkpoint
- The step that copies logged changes back into the main database file.
- Column affinity
- SQLite's habit of converting values toward a column's declared type without rejecting them.
- Counting Bloom filter
- A variant that uses counters instead of single bits so items can be removed.
- Cron job
- A task that a computer runs automatically on a schedule.
- DuckDB
- A database engine that runs queries on local files.
- Expression index
- A database index built on a computed value instead of a plain column.
- False positive
- An answer that says an item is present when it never was added.
- Flexible typing
- SQLite's default behavior of letting any column hold any kind of value.
- Fsync
- An operating system call that forces data onto the disk.
- GeoJSON
- A text format for storing map shapes and their attributes.
- H3
- A system that divides the globe into hexagonal cells.
- Hash function
- A function that turns any input into a fixed-size number.
- Hot journal
- A leftover journal file from an interrupted write that SQLite needs for crash recovery.
- In-process database
- A database that runs as a library inside your program.
- LoD1
- A simple 3D building model, in which each building is a flat-topped block.
- Memory mapping
- A way to read a file as if it were part of memory.
- Overture
- A public map data project that includes a buildings dataset.
- Parquet
- A common columnar file format for analytics data.
- Pipeline breaker
- An operation that must see all its input before it can produce output.
- POSIX advisory lock
- A file lock on Unix systems that only works if every program cooperates.
- Pragma
- A SQLite command that changes a setting of the database engine.
- PRAGMA integrity_check
- A SQLite command that scans a database file for signs of corruption.
- QGIS
- A free desktop program for viewing and editing maps.
- RAG
- A method that fetches relevant stored text for a language model.
- Row group
- A fixed-size chunk of rows stored column by column.
- Shim
- A thin wrapper placed around existing code to observe or alter its behavior.
- STRICT table
- A SQLite table option that enforces each column's declared type.
- Vacuum
- A command that rewrites the database file to remove unused space.
- Val.town
- A hosting platform offering scheduled jobs, SQLite storage, and email handling.
- Virtual filesystem (VFS)
- SQLite's layer that handles reading and writing files on a given operating system.
- Write-ahead log
- A file where changes are recorded first and merged into the database later.
- Write-ahead log (WAL)
- A SQLite mode that logs changes to a separate file first.
- Zone map
- A stored minimum and maximum for a block of data.