Skill

Review

Audit score 70

storage-format

tursodatabase/turso

Understand SQLite file format, B-trees, pages, and storage internals used by Turso.

What is storage-format?

This skill explains the on-disk structure of SQLite databases as implemented in Turso, including the database header, page types, B-tree organization, cell formats, and freelist management. Use it when debugging storage issues, optimizing database performance, or contributing to Turso's storage layer.

Decode database header fields (magic, page size, encoding, freelist pointers)
Identify and interpret page types (interior/leaf table/index, overflow, freelist)
Parse B-tree structure and cell formats for both table and index pages
Understand record serialization with varint encoding and serial type codes
Trace overflow chains for payloads exceeding cell capacity
Navigate freelist trunk and leaf pages to track free space

How to install storage-format

npx skills add https://github.com/tursodatabase/turso --skill storage-format

Claude Code

Cursor

Windsurf

Cline

How to use storage-format

1.Review the database header structure to extract metadata like page size and encoding
2.Identify page types using the flag byte to determine if a page is a B-tree node, overflow, or freelist page
3.Parse B-tree interior and leaf cells using the cell format specification
4.Decode record payloads by reading the header size, serial types, and corresponding data fields
5.Follow overflow page chains when payload data spans multiple pages
6.Use the provided Turso source file references (sqlite3_ondisk.rs, btree.rs, pager.rs) to cross-reference implementation details

Use cases

Good for

Debugging storage corruption or integrity check failures in a Turso database
Analyzing page layout and B-tree structure to optimize query performance
Implementing custom storage tools or migration utilities that read SQLite files directly
Understanding how Turso's pager and buffer pool interact with on-disk pages
Tracing freelist management to diagnose space reclamation issues

Who it's for

Turso contributors working on storage and pager modules
Database engineers optimizing SQLite-based systems
Developers building custom tooling around Turso databases
Anyone reverse-engineering or auditing SQLite file format compliance

storage-format FAQ

What is the default page size in Turso/SQLite?

The default page size is 4096 bytes, but it can be any power of 2 between 512 and 65536 bytes. The actual size is stored in the database header at offset 16.

How do I check if a page is a B-tree leaf or interior node?

Read the first byte of the page (the page type flag). Values 0x0a and 0x0d indicate leaf pages (index and table respectively), while 0x02 and 0x05 indicate interior pages.

What happens when a row's data is too large for a single cell?

Excess payload is stored in overflow pages, linked via a chain. The cell contains a pointer to the first overflow page, and each overflow page points to the next until the last page (which has next_page=0).

How is the freelist organized?

The freelist is a linked list of trunk pages. Each trunk page contains a pointer to the next trunk and a list of leaf page numbers representing free pages. The first freelist trunk page number is stored in the database header.

What encoding formats does SQLite support?

Three text encodings: UTF-8 (value 1), UTF-16 Little-Endian (value 2), and UTF-16 Big-Endian (value 3). The encoding is specified in the database header at offset 56.

Full instructions (SKILL.md)

Source of truth, from tursodatabase/turso.

name: storage-format description: SQLite file format, B-trees, pages, cells, overflow, freelist that is used in tursodb

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

# 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;"