Storage Format
Explain and reason about SQLite on-disk layout (pages, B-trees, overflow, freelist) when building or debugging Turso/libSQL-backed storage.
Overview
storage-format is an agent skill most often used in Build (also Operate errors, Ship perf) that documents SQLite file format, B-trees, pages, and freelist behavior in TursoDB.
Install
npx skills add https://github.com/tursodatabase/turso --skill storage-formatWhat is this skill?
- Maps SQLite database file layout: header page, B-tree pages, overflow, and freelist
- Documents first 100-byte header fields (magic, page size, WAL version, schema cookie, encoding)
- Covers page type flags for interior/leaf table and index B-trees plus overflow handling
- States page-size rules (power of 2, 512–65536 bytes, default 4096) and big-endian integer convention
- Database header is the first 100 bytes on page 1
- Default page size 4096 bytes (powers of 2 from 512 to 65536)
Adoption & trust: 709 installs on skills.sh; 19.1k GitHub stars; 3/3 security scanners passed (skills.sh audits).
What problem does it solve?
You are changing or troubleshooting Turso/SQLite storage without a precise mental model of pages, B-trees, and header fields.
Who is it for?
Backend devs and infra-minded solos building on Turso who need byte-level SQLite literacy for tools, incidents, or advanced indexing decisions.
Skip if: Builders who only need CRUD SQL and ORM usage with no interest in file format or page internals.
When should I use this skill?
When implementing or debugging Turso/libSQL features that depend on SQLite on-disk layout, B-trees, or freelist behavior.
What do I get? / Deliverables
You and your agent share an accurate SQLite on-disk reference so designs, debug sessions, and reviews align with Turso’s storage engine realities.
- Accurate storage-format explanations in design or debug notes
- Agent answers grounded in header and page-type tables
Recommended Skills
Journey fit
Spans multiple journey phases - primary shelf plus alternate fits below.
Storage internals matter during backend implementation when schema, indexes, and engine behavior affect correctness and performance. Backend subphase covers database engines and persistence layers tied to Turso’s SQLite-format lineage.
Where it fits
Design a page inspector or migration that must respect SQLite leaf versus interior table flags.
Interpret corruption or recovery scenarios using freelist trunk pages and overflow chains.
Choose page size and index strategies with awareness of B-tree depth and cell capacity.
How it compares
Reference skill for SQLite physical layout—not a hosted DB provisioning integration or ORM codegen skill.
Common Questions / FAQ
Who is storage-format for?
Developers using Turso or libSQL who want agents to cite correct page types, header offsets, and B-tree rules during backend or ops work.
When should I use storage-format?
In Build (backend) when designing storage tools or migrations; in Operate (errors) when investigating corruption or WAL issues; in Ship (perf) when tuning page size and index depth.
Is storage-format safe to install?
Read-only format documentation; review Security Audits on this Prism page for the parent Turso skill repo before trusting agent suggestions that touch production files.
SKILL.md
READMESKILL.md - Storage Format
# Storage Format Guide ## Database File Structure ``` ┌─────────────────────────────┐ │ Page 1: Header + Schema │ ← First 100 bytes = DB header ├─────────────────────────────┤ │ Page 2..N: B-tree pages │ ← Tables and indexes │ Overflow pages │ │ Freelist pages │ └─────────────────────────────┘ ``` Page size: power of 2, 512-65536 bytes. Default 4096. ## Database Header (First 100 Bytes) | Offset | Size | Field | |--------|------|-------| | 0 | 16 | Magic: `"SQLite format 3\0"` | | 16 | 2 | Page size (big-endian) | | 18 | 1 | Write format version (1=rollback, 2=WAL) | | 19 | 1 | Read format version | | 24 | 4 | Change counter | | 28 | 4 | Database size in pages | | 32 | 4 | First freelist trunk page | | 36 | 4 | Total freelist pages | | 40 | 4 | Schema cookie | | 56 | 4 | Text encoding (1=UTF8, 2=UTF16LE, 3=UTF16BE) | All multi-byte integers: **big-endian**. ## Page Types | Flag | Type | Purpose | |------|------|---------| | 0x02 | Interior index | Index B-tree internal node | | 0x05 | Interior table | Table B-tree internal node | | 0x0a | Leaf index | Index B-tree leaf | | 0x0d | Leaf table | Table B-tree leaf | | - | Overflow | Payload exceeding cell capacity | | - | Freelist | Unused pages (trunk or leaf) | ## B-tree Structure Two B-tree types: - **Table B-tree**: 64-bit rowid keys, stores row data - **Index B-tree**: Arbitrary keys (index columns + rowid) ``` Interior page: [ptr0] key1 [ptr1] key2 [ptr2] ... │ │ │ ▼ ▼ ▼ child child child pages pages pages Leaf page: key1:data key2:data key3:data ... ``` Page 1 always root of `sqlite_schema` table. ## Cell Format ### Table Leaf Cell ``` [payload_size: varint] [rowid: varint] [payload] [overflow_ptr: u32?] ``` ### Table Interior Cell ``` [left_child_page: u32] [rowid: varint] ``` ### Index Cells Similar but key is arbitrary (columns + rowid), not just rowid. ## Record Format (Payload) ``` [header_size: varint] [type1: varint] [type2: varint] ... [data1] [data2] ... ``` Serial types: | Type | Meaning | |------|---------| | 0 | NULL | | 1-4 | 1/2/3/4 byte signed int | | 5 | 6 byte signed int | | 6 | 8 byte signed int | | 7 | IEEE 754 float | | 8 | Integer 0 | | 9 | Integer 1 | | ≥12 even | BLOB, length=(N-12)/2 | | ≥13 odd | Text, length=(N-13)/2 | ## Overflow Pages When payload exceeds threshold, excess stored in overflow chain: ``` [next_page: u32] [data...] ``` Last page has next_page=0. ## Freelist Linked list of trunk pages, each containing leaf page numbers: ``` Trunk: [next_trunk: u32] [leaf_count: u32] [leaf_pages: u32...] ``` ## Turso Implementation Key files: - `core/storage/sqlite3_ondisk.rs` - On-disk format, `PageType` enum - `core/storage/btree.rs` - B-tree operations (large file) - `core/storage/pager.rs` - Page management - `core/storage/buffer_pool.rs` - Page caching ## Debugging Storage ```bash # Integrity check cargo run --bin tursodb test.db "PRAGMA integrity_check;" # Page count cargo run --bin tursodb test.db "PRAGMA page_count;" # Freelist info cargo run --bin tursodb test.db "PRAGMA freelist_count;" ``` ## References - [SQLite File Format](https://sqlite.org/fileformat.html) - [SQLite B-Tree Module](https://sqlite.org/btreemodule.html) - [SQLite Internals: Pages & B-trees](https://fly.io/blog/sqlite-internals-btree/)