Why Per-User SQLite
Most B2C SaaS apps use a single shared database with a user_id column on every table. It works, but it comes with trade-offs: you need row-level security, complex queries, and careful indexing to avoid one user's data leaking into another's queries.
Per-user SQLite flips this model on its head.
How It Works
Each user gets their own SQLite file at data/users/{userId}/user.db. When the user logs in, their database is opened and migrations are run if needed. All queries for that user's data hit their own isolated file.
data/
meta.db ← shared: users, sessions, subscriptions
users/
abc123/
user.db ← user A's data
def456/
user.db ← user B's data
The Benefits
True isolation
No WHERE user_id = ? required. Every query runs against a database that only contains that user's data. Cross-user data leaks are architecturally impossible.
Performance
SQLite is extremely fast for single-user workloads. Write-ahead logging (SQLite's default write mode) lets reads keep happening while a write is in progress, instead of blocking. With per-user files, each user's recently-read data stays in its own dedicated slice of memory, ready for the next request, no other user's traffic ever pushes it out to make room for their own.
Simple backups
Want to backup or export a user's data? Copy one file. Done.
Easy migrations
Migrations run lazily when a user's database is first opened. You can add columns, create tables, and evolve the schema without coordinating a global migration run.
The Trade-off
Per-user SQLite buys isolation, speed, and zero query-time tenancy logic by giving up something in return: cheap queries across users. No JOIN spans two people's data, because there's no single database that holds both. That's a great deal for some products and a bad one for others.
Best Use Cases for Warpkit
This pattern fits products where each user's data is its own independent silo:
- Personal productivity tools, note apps, journals, habit trackers, personal CRMs
- Per-user AI tools, chat assistants, personal knowledge bases, document Q&A where each user's history and context stay private by construction
- Solo analytics and dashboards, personal finance trackers, fitness logs, time trackers
- Internal tools and admin panels where each operator's workspace is independent
- API/dev-tool SaaS where usage and data are scoped to one account
- Content tools where a user's drafts and projects never need to be queried alongside anyone else's
It's the wrong fit, at least today, for products built around one shared graph rather than many independent ones:
- Social apps, feeds, follows, comments, anything that joins across users at query time
- Marketplaces, cross-listing search, matching buyers to sellers
- Real-time collaboration, shared documents, multiplayer boards, live cursors across users
- B2B tools with shared team workspaces where multiple people write into the same dataset
Rule of thumb: "many users, N independent silos" is close to free isolation with this pattern. "Many users, one shared graph" fights it the whole way, and a shared-schema database is the right call.
Postgres Is Now Available Too
That second category, shared-graph products, now has its own fork: a Postgres/Drizzle version of this same template, same auth/billing/jobs patterns, different tradeoff. It's included in the same purchase, and it's live: see the Postgres docs or the comparison post for when to reach for it instead of this one. This template keeps optimizing for the isolated, per-user shape it was built for; the fork exists for the products that need the other shape, not to replace this one.
Getting Started
Your per-user migrations live in src/db/user-migrations.ts. Add a new entry to the USER_MIGRATIONS array for each schema change:
{
id: '002_add_tags',
run: (db) => {
db.run(`ALTER TABLE example_items ADD COLUMN tags TEXT`)
}
}
Migrations are idempotent and run in order. The framework tracks which ones have been applied in a _warpkit_migrations table inside each user's database.