Building an Editable Blog That Still Works on GitHub Pages
How SQLite authoring, deterministic static generation, and a public-only build artifact create an editable blog without shipping a runtime database.
[systems debrief] 5 sections
GitHub Pages serves static files. A comfortable editorial workflow wants drafts, structured metadata, search, and a way to update one post without hand-editing several generated artifacts. The architecture works when those concerns are separated instead of pretending a static host is a database server.
On this site, SQLite is the authoring source of truth and a build step turns published rows into a clean static site. The database is committed for reproducible authoring, but it is never copied into the public artifact.
01. The authoring model
The database keeps post metadata, HTML bodies, status, and ordered topics. A small local writer runs only on loopback and saves back to SQLite. That gives me one place to edit without requiring a public CMS or shipping an authenticated admin surface.
- posts: slug, title, summary, category, dates, hero metadata, body, and status.
- post_topics: normalized tags with stable display order.
- status: a publication decision, not a CSS class applied after deployment.
Keeping HTML in the database is a tradeoff: binary diffs are less readable than Markdown diffs. The benefit is that the writer, generator, and query tools all use one schema. Focused checks and generated-page diffs make the resulting public change reviewable.
02. The build is the publication boundary
The build reads only rows marked published. It writes one static page per post,
refreshes the blog manifest, RSS feed, sitemap, robots metadata, and adjacent-post navigation,
then assembles an allowlisted dist/ directory.
That distinction matters. A hidden draft can exist in the authoring database without appearing in
the manifest, sitemap, RSS, generated pages, or deployment artifact. The database, local writer,
templates, and preview-only routes are also rejected from dist/.
Hidden content is safe only when the build excludes it, not when the interface merely declines to link to it.
03. The public site does not query SQLite
This is the easy detail to describe incorrectly. GitHub Pages cannot open the authoring database
on the server. The blog index fetches a generated posts.json manifest and performs
search, category filtering, pagination, sharing, and bookmarks in the browser. Article bodies
live in generated static HTML, so a direct post URL remains complete without client-side database
access.
The manifest contains public metadata only. It is useful for progressive enhancement, but it is not the canonical content store and it does not need to carry every article body.
04. Determinism keeps generated content reviewable
Generated files become frustrating when a build changes timestamps, ordering, or formatting for no content reason. The generator sorts posts deterministically, derives metadata from stored values, and can be built twice to prove the second output is identical.
Publication checks then verify that canonical URLs, Open Graph metadata, RSS, sitemap entries, and static pages agree. This turns “the generator ran” into a stronger claim: the public artifact is complete, contains only allowed files, and can be reproduced from the committed source.
05. What this architecture is—and is not
This is a good fit for one author, modest content volume, and Git-based review. It avoids a hosted CMS, a production database, and a public admin attack surface. It also means edits require a local tool and a build, and collaborators cannot review the SQLite body as comfortably as a plain-text source.
If those tradeoffs change, the schema can feed a different editor or deployment target. Until then, the important property is simple: authoring can be dynamic while publication stays static, inspectable, and intentionally small.