storage-format
It details the SQLite file format, including page types, cell structures, and memory management mechanisms.
Install
mkdir -p .claude/skills/storage-format && curl -L -o skill.zip "https://agentskills.codes/api/skills/download/2096" && unzip -o skill.zip -d .claude/skills/storage-format && rm skill.zipInstalls to .claude/skills/storage-format
Activation
This is the description your AI agent reads to decide when to run this skill — the better it matches your request, the more reliably it fires.
SQLite file format, B-trees, pages, cells, overflow, freelist that is used in tursodbKey capabilities
- →Parse SQLite database file headers
- →Navigate B-tree page structures
- →Decode table and index cell formats
- →Manage freelist and overflow pages
How it works
The skill provides a guide to the SQLite on-disk format, including page types, cell layouts, and record serialization, with specific references to Turso's internal implementation.
Inputs & outputs
When to use storage-format
- →Debugging low-level database file corruption
- →Optimizing database storage structures
- →Understanding SQLite internal organization
About this skill
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,PageTypeenumcore/storage/btree.rs- B-tree operations (large file)core/storage/pager.rs- Page managementcore/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;"
References
When not to use it
- →Modifying database files without backup
Prerequisites
Limitations
- →Requires understanding of big-endian integer formats
- →Limited to SQLite format 3
How it compares
It provides direct access to internal storage structures rather than relying on high-level SQL abstraction layers.
Compared to similar skills
storage-format side by side with the closest alternatives in the catalog.
| Skill | Installs | Updated | Safety | Difficulty |
|---|---|---|---|---|
| storage-format (this skill) | 2 | 6mo | Review | Advanced |
| postgresql-table-design | 30 | 4mo | No flags | Intermediate |
| agentdb-advanced-features | 7 | 9mo | Review | Advanced |
| event-store-design | 5 | 2mo | No flags | Advanced |
Try saying
Example prompts that trigger this skill in your AI assistant.
More by tursodatabase
View all by tursodatabase →You might also like
postgresql-table-design
wshobson
Design a PostgreSQL-specific schema. Covers best-practices, data types, indexing, constraints, performance patterns, and advanced features
agentdb-advanced-features
ruvnet
Master advanced AgentDB features including QUIC synchronization, multi-database management, custom distance metrics, hybrid search, and distributed systems integration. Use when building distributed AI systems, multi-agent coordination, or advanced vector search applications.
event-store-design
wshobson
Design and implement event stores for event-sourced systems. Use when building event sourcing infrastructure, choosing event store technologies, or implementing event persistence patterns.
database-design
davila7
Database design principles and decision-making. Schema design, indexing strategy, ORM selection, serverless databases.
database-schema-designer
davila7
Design robust, scalable database schemas for SQL and NoSQL databases. Provides normalization guidelines, indexing strategies, migration patterns, constraint design, and performance optimization. Ensures data integrity, query performance, and maintainable data models.
nosql-expert
davila7
Expert guidance for distributed NoSQL databases (Cassandra, DynamoDB). Focuses on mental models, query-first modeling, single-table design, and avoiding hot partitions in high-scale systems.