Dev.to WebDev 🛠 Dev 👁 0 📖 6 min read

SQLite as my CMS: three gates that stop me shipping broken static sites

I run a bilingual site — English and Chinese, 170+ long articles, all static HTML. No CMS, no admin panel, no React. The only "database" is a single SQLite file sitting in the repo, and a PHP CLI turns it into a dist/ fo

I run a bilingual site — English and Chinese, 170+ long articles, all static HTML. No CMS, no admin panel, no React. The only "database" is a single SQLite file sitting in the repo, and a PHP CLI turns it into a dist/ folder that gets uploaded.

This is not a "look how clever my stack is" post. It is a list of the things that broke, and the specific gates I added so they stop breaking. If you generate any volume of content into static HTML, most of this will apply to you regardless of language.

Why SQLite and not a CMS

The first version used a headless CMS. It lasted about three weeks. The problems were boring and fatal:

  • Every article needed a title, a description, a slug, a cover image, a language, tags, a publish date, and a set of SEO fields. A CMS gives you a form for that. A form is fine for one article and hostile for 170.
  • Bulk operations — "add this section to every page", "check every description against a length rule" — are impossible in a CMS UI and one SQL statement in SQLite.
  • Version control. A .db file diffs badly, but it backs up trivially and it is one file to restore. A CMS is a service you have to trust.

So the shape became: SQLite is the single write target, generated HTML is disposable, and anything that only exists in dist/ is a bug waiting to happen.

db.sqlite  --[generate]-->  dist/*.html  --[upload]-->  CDN
   ^
   +-- the only thing backed up

One rule keeps this honest: never hand-edit a file in dist/. When I catch myself fixing a typo directly in the built HTML, that is a signal the DB is missing a step, not a signal that a quick edit is fine. Every hand-edit is silently reverted on the next build — usually two weeks later, after I have forgotten about it.

Gate one: content rules are code, not vibes

Editorial rules rot the moment they live in a document nobody opens. Mine are a function called against every row before it can be written:

// simplified, but this is the actual shape
$errors = [];

if ($words($content) < 1200)              $errors[] = "body too short";
if ($inlinks($content) < 2)               $errors[] = "needs 2-3 internal links";
if ($inlinks($content) > 3)               $errors[] = "too many internal links";
if (count($faq) < 3)                      $errors[] = "needs 3+ FAQ items";
foreach ($faq as $q) {
    if ($words($q['a']) > 60)             $errors[] = "FAQ answer over 60 words";
}
if (preg_match('/\b(delve|seamless|crucial|pivotal)\b/i', $content))
                                          $errors[] = "banned filler word";
if ($words($desc) > 160)                  $errors[] = "meta description too long";

Two of these rules look arbitrary and are not:

The FAQ limit is 60 words, and the number matters less than having it. I originally wrote "each answer should be 100–200 words" in a notes file, then implemented 60 in the gate. Six weeks of content went through believing the notes. The gate was right and the notes were wrong, which is the whole argument for putting the rule in code: prose rules drift, if statements do not.

The description limit has to be measured after HTML entity decoding. A description containing a curly apostrophe or an em dash is stored as &#8217; and &#8212;. Counting the raw string says 158 characters. Counting what the browser renders says 154. Counting the raw string is the version that lets a 165-character description through, gets truncated in search results, and looks like a mistake you cannot reproduce. Decode first, then count:

$len = mb_strlen(html_entity_decode($desc, ENT_QUOTES | ENT_HTML5, 'UTF-8'), 'UTF-8');

That one line cost me an afternoon and I have not had a truncated snippet since.

Gate two: the artifact gate checks the output, not the input

The content gate proves the rows are good. It says nothing about whether the build did what I think it did. So there is a second, dumber checker that walks dist/ and asserts things about the files:

  • No .php, .py, .db, .sqlite, .sql, .log, .ini anywhere in the output tree.
  • No .bak* files. Every backup I have ever taken has ended up in the wrong folder once.
  • Every published row has a matching .html file.
  • Every internal link resolves to a file that exists.
  • Every page has exactly one <title> and one <link rel="canonical">.

The file-extension check is the one that earned its keep. A helper script wrote a .bak copy of a template next to the original, inside the folder that gets uploaded. Nothing breaks — the site still works — but the backup template, with its placeholder text and its half-finished markup, is now publicly fetchable at a guessable URL. Nothing in a content-level check would ever see that, because it is not a content problem. It is a "what is actually in the folder" problem.

That is the reason the two gates are separate. They ask different questions and they fail for different reasons.

Gate three: fetch the deployed site like a stranger

The last gate runs after upload and just does HTTP requests against the live domain: homepage 200, a handful of article URLs 200, the sitemap parses and its count is not zero, robots.txt does not accidentally Disallow: /.

It is five lines of curl and it has never once been interesting. That is the point. A smoke test that is boring is a smoke test you will keep running.

The recurring bug: silent skips

Every serious incident on this site has the same shape. A row fails a condition the build treats as "skip this one", the build exits zero, and nothing anywhere says a page is missing.

The concrete case: the generator refuses to emit an article whose cover image field is empty, because a page with a broken hero image looks worse than no page. Reasonable rule. But the report says published: 170, the upload succeeds, and the two articles that were skipped are simply not there. I found out five days later by accident, when I noticed a topic I remembered writing was not in the sitemap.

The fix was not to loosen the rule. The fix was to make skipping loud:

SKIP  id=391  reason=cover_empty
SKIP  id=393  reason=cover_empty
published=170  skipped=2

If a build can decide not to emit something, it has to print what it dropped. Exit code zero means "the program ran", not "you got what you expected", and the gap between those two sentences is where every quiet disaster lives.

Bilingual means two sites, not one site twice

The last thing worth saying, because it is the mistake most likely to be made by anyone doing this for the first time: I do not translate. English articles live under /en/, Chinese under /zh/, they cover different topics in different depth, and they are not translations of each other. Each page gets its own canonical pointing at itself. Cross-language hreflang is only declared where two pages are genuinely the same page, which for now is exactly one pair — the two homepages.

Machine-translating 170 articles would have been faster and would have produced 340 pages of near-duplicate text, half of it in a voice nobody uses. Two independent corpora is more work and it is the only version that does not need to be undone later.

What I would tell someone starting this today

  • Put the rules in code before you have 20 articles, not after 200. Retrofitting a gate means auditing everything it now rejects, and you will not do it.
  • Back up the source database before every write, not before every "important" write. You will not correctly predict which write is important.
  • Make the build print what it skipped. Exit codes lie by omission.
  • Keep the generated output disposable. If restoring a page requires a file that only exists in the built folder, you have a database with extra steps and no backup.

The site this runs is at jiunotes.top — English and Chinese travel writing about China, plus a set of browser-side tools at jiunotes.top/tech/tools. The operational notes for the pipeline itself live at ops.jiunotes.top.

Compiled and checked by Jiu — About · Methodology

📰 Read the original article on Dev.to WebDev

Originally published by Dev.to WebDev. Aggregated on AIWithGhost for educational purposes — full credit and traffic to the original publisher.