Supabase → SQLite
I built the most recent version of this site with Supabase. PostgreSQL for the database, GoTrue for auth, PGMQ for job queues, PostgREST for the API. I liked the results. It was everything I needed, managed by someone else (well, by me, but with nice tooling).
Then I tried to deploy it.
I have been self-hosting this site on a 1GB Linode for years. I wanted blue-green deployment — two instances side-by-side, zero-downtime deploys, instant rollback. The full Supabase stack would not even start. There was not enough RAM.
I tried to optimize, excluded unused services, and kept only the essentials. One instance could boot, but two was not possible.
What are my options?
- Upgrade the Linode. 2GB for $12/month, 4GB for $24/month. But I did not know which would even be enough.
- Use Supabase as a hosted service. I could leverage the free tier or upgrade to $25/month. But that meant abandoning self-hosting.
- Rethink the stack entirely.
Rethinking the stack
I did not plan the migration top-down. I have a solid unit test suite, and integrations exercising the core micropub API. I needed to find the next safe change. What can I replace without breaking everything else?
Build the foundation
Before I could replace PostgreSQL, I needed a migration runner — something like supabase migrate, but for SQLite. This was a tool I had been wanting for other projects anyway.
I built stig: forward-only migrations, filesystem snapshots for local rollback, and schema-aware codegen so I could maintain the types I had previously generated. I collapsed the existing PostgreSQL migrations into a single SQLite schema, with support for moving forward as I had been.
Rewrite data access
With the foundation in place, I rewrote the data access layers: posts, references, syndications, OAuth grants. Sync FFI calls instead of async queries. When the tests passed, and the data was intact, PostgreSQL was essentially gone.
Replace the queue
PGMQ is a PostgreSQL extension. Now that the database was SQLite, a PostgreSQL queue made no sense. I swapped to Honker, a SQLite extension for in-process job queues. It uses commit-wake instead of polling, and no separate service is needed.
Swap authentication
GoTrue is a full-featured auth service — OAuth providers, magic links, session management, user invitations. For a production SaaS, it is great; but for a personal site with one admin user, it is overkill.
But I was not starting from scratch. I had already built an IndieAuth translation layer on top of GoTrue — the application was already speaking IndieAuth; GoTrue was just the backend. So replacing it with a users table, bcrypt, and JWTs was straightforward. The IndieAuth interface stayed the same. I just swapped what was underneath. When auth tests passed, the cascade was complete.
Remove Supabase
Each replacement made the next one obvious. Each test gate made the next one safe.
shgit rm -rf supabase/
Deleted the migrations, the env vars, the Docker config, and updated the docs. The satisfaction of that commit was disproportionate to the effort. It felt like closing a chapter.
The new stack:
- SQLite 3.46.0 with JSONB (via
@db/sqliteFFI) - Honker (in-process job queue, SQLite extension)
- Local users + bcrypt + JWT
- stig (Rust-based migration tool)
- Everything in-process with Deno
The result:
- Staging and production running side-by-side under 1GB
- Zero-downtime promotes and rollbacks
- Additional services: 0
