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.