SQLite schema examples
SQLite schemas for embedded and local-first applications, generated with SQLite's type affinity in mind. Open any of them in the free drawDB editor and export ready-to-run SQLite DDL — tables, keys, indexes and comments.
6 schemas here, from 9 to 12 tables (66 tables and 527 columns in total), written for SQLite. Each one ships an ER diagram you can open and edit, a column-by-column reference, design notes explaining the trade-offs, and SQL for all six dialects. Recurring themes across the set: local-first, sqlite.
Expense tracker & personal finance database schema
A local-first SQLite personal finance database: accounts, transactions in integer minor units, a nested category tree, per-period budgets with rollover, recurring rules, statement imports with auto-categorisation, receipts, and transfers modelled as a linked pair of legs.
Habit tracker app database schema
A small local-first SQLite schema for habit tracking: habits with date-versioned schedules, one row per completion, pauses that skip a streak instead of breaking it, reminders, and streaks derived from check-ins rather than stored as a counter.
Note-taking app database schema with backlinks
A local-first SQLite notes database: notebooks and notes, wiki-style backlinks parsed from note bodies into a link graph, tags, attachments, a full-text search index, revision history and last-writer-wins sync with a conflict record.
Password manager & encrypted vault database schema
A local-first SQLite vault where the secrets are ciphertext the database cannot read: vaults as key boundaries, encrypted item fields, blind indexes for the few searches that must still work, TOTP seeds, password history, sharing and an audit log.
Local-first podcast app database schema (SQLite)
How a podcast client stores its data on the device: subscribed shows, episodes mirrored from RSS feeds, playback positions, download state, an ordered queue, per-show settings, feed sync cursors and an offline outbox.
Recipe and meal planning app database schema
A local-first SQLite schema for a cooking app: recipes with quantified ingredients, units and ingredient-specific conversions, timed steps, meal plans by day, shopping lists that merge quantities across recipes, pantry stock and favourites.