Building an Editable Blog System That Still Works on GitHub Pages
How to keep a blog editable with SQLite and tooling while deploying static pages for GitHub Pages compatibility and SEO.
GitHub Pages is static. Editorial workflows are dynamic. The trick is separating authoring/query concerns from deployment constraints without duplicating truth.
My solution: SQLite as source of truth + static page generation at build time.
01. Content Model
Posts live in SQLite with normalized topics and metadata. This supports querying, filtering, and authoring updates without touching many flat files by hand.
- posts: slug, title, summary, category, hero, body_html, status.
- post_topics: many-to-one tags with stable ordering.
- status: published or hidden for governance.
02. Build-Time Static Generation
Build scripts read published rows and generate static blog post pages, posts.json manifest, RSS feed, and sitemap plus robots files.
This gives full static deploy compatibility while preserving dynamic authoring internally.
03. Runtime Query Behavior
On the blog index, SQLite-backed querying powers filtering/search semantics. On direct post routes, static HTML guarantees fast loading and crawler-friendly content.
This hybrid avoids the tradeoff between editability and static hosting requirements.
04. SEO + Reliability Benefits
- Canonical URLs and OG tags are deterministic per post.
- RSS and sitemap stay synchronized with published status.
- Hidden posts are excluded from generated artifacts.
- Static output removes runtime dependency risk on public pages.
05. Why This Architecture Scales
It supports editor-friendly workflows today and cleaner migration paths later. If needed, the same SQLite model can feed APIs, admin tools, or headless delivery without redoing content structure.