BL-026 / BL-027 - the two lessons of the Aug 21 outage, logged

BL-026: no version stamp - 'is live current?' took mid-outage fingerprinting;
the fix shape is a git SHA baked at build, served by /api/health, shown on
the admin diagnostics card (D13 candidate).

BL-027: migrations rehearsed on SQLite only - the dialect drift class behind
the outage stays unguarded beyond the one pinned instance; the fix shape is
an offline postgresql-dialect render in the runbook and/or a probe.

Items: backlog only, no code.

Co-Authored-By: Claude Fable 5 <noreply@anthropic.com>

docs/waves/backlog.md

@@ -554,3 +554,43 @@ deliberately deferred.
   entry rather than a drive-by.
 - **Suggested wave or follow-up:** next housekeeping pass, with the check
   widened so it cannot recur.
+
+### BL-026 — No version stamp: "is live current?" cannot be answered from the app
+
+- **Found during:** the 2026-08-21 outage triage (the question that started it)
+- **Where:** `Dockerfile` / build, `server/app.py` `/api/health`, admin console
+- **What:** the app carries no record of what code it is running. `/api/health`
+  returns `{"ok": true}` and nothing identifies the deployed commit, so
+  answering "is the live site on the latest code?" took fingerprinting
+  (probing for files/routes that only exist after certain merges) in the
+  middle of an outage. The fix: bake the git SHA into the image at build time
+  (`ARG GIT_SHA`), return it from `/api/health`
+  (`{"ok": true, "version": "<sha>"}`), and show it on the admin console's
+  diagnostics card. Then currency is one glance against `git log -1`.
+- **Why not now:** new scope — needs its own item id per the working rules
+  (D13 is the natural next), and it touches the image build, which deserves a
+  deploy alongside someone with host access.
+- **Suggested wave or follow-up:** next housekeeping pass; ~1 task including a
+  probe check that /api/health carries a version field.
+
+### BL-027 — Migrations are rehearsed on SQLite only; production is Postgres
+
+- **Found during:** the 2026-08-21 production outage (D6's `material_items`
+  migration crash-looped the api container)
+- **Where:** `DEPLOYMENT.md` (the update/deploy steps), `tests/`
+- **What:** the migration chain is verified end-to-end on scratch SQLite, but
+  production runs Postgres, and the dialects disagree exactly where it hurts:
+  `server_default=sa.text('1')` on a Boolean passed every SQLite rehearsal and
+  was refused by Postgres at deploy (DatatypeMismatch), taking the API down
+  until the table was created by hand. The hotfix (64eac0c) fixed that one
+  instance and pinned the Boolean-default class in `materials_check`; the
+  CLASS of dialect drift is still unguarded. Two cheap layers: (1) a runbook
+  step — render `alembic upgrade --sql` for the postgresql dialect and read it
+  before restarting (offline, needs no live DB; this render would have shown
+  `DEFAULT 1` on a boolean); (2) better, a probe that renders every migration
+  for the postgresql dialect on each run and fails on anything the dialect
+  rejects or on known-bad patterns.
+- **Why not now:** the outage is resolved and the one known instance is fixed
+  and pinned; the systematic guard is its own small task, not a hotfix rider.
+- **Suggested wave or follow-up:** next housekeeping pass, paired with BL-026
+  (both are "deploys should be boring" work).

server/alembic/versions/a1b8c6d4e2f9_material_items_project_list.py

@@ -32,7 +32,11 @@ def upgrade() -> None:
         sa.Column('code', sa.String(length=80), nullable=False, server_default=''),
         sa.Column('description', sa.String(length=300), nullable=False, server_default=''),
         sa.Column('unit', sa.String(length=20), nullable=False, server_default=''),
-        sa.Column('active', sa.Boolean(), nullable=False, server_default=sa.text('1')),
+        # sa.true(), not sa.text('1'): SQLite coerces integer 1 to boolean,
+        # Postgres refuses it (DatatypeMismatch) - found when this migration
+        # took down the wp.controls.dev api container on 2026-08-21. The
+        # location-taxonomy migration next door had it right all along.
+        sa.Column('active', sa.Boolean(), nullable=False, server_default=sa.true()),
         sa.Column('sort', sa.Integer(), nullable=False, server_default='0'),
         sa.Column('created_at', sa.DateTime(timezone=True), nullable=True),
         sa.PrimaryKeyConstraint('id'),

tests/materials_check.py

@@ -54,6 +54,20 @@ def main():
     chk("locations and materials are both instances of it - not a copy beside it",
         "locList = WPListImport(" in suite and "matList = WPListImport(" in suite
         and "function locImport(dryRun){ locList.importText" in suite)
+    # The 2026-08-21 outage, pinned: a Boolean server_default of sa.text('1')
+    # passes on SQLite (which coerces 1) and crash-loops Postgres at deploy
+    # (DatatypeMismatch). Every migration must say sa.true()/sa.false().
+    import re as _re
+    bad = []
+    vdir = os.path.join(ROOT, "server", "alembic", "versions")
+    for fn in sorted(os.listdir(vdir)):
+        if not fn.endswith(".py"):
+            continue
+        for ln in open(os.path.join(vdir, fn), encoding="utf-8"):
+            if "Boolean" in ln and "server_default" in ln and                not _re.search(r"server_default=sa\.(true|false)\(\)", ln):
+                bad.append("%s: %s" % (fn, ln.strip()[:90]))
+    chk("no migration gives a Boolean a non-portable server_default "
+        "(sa.true()/sa.false() only)", not bad, ascii_(bad[:3]))
     model = open(os.path.join(ROOT, "server", "models.py"), encoding="utf-8").read()
     mat_block = model[model.find("class MaterialItem"):model.find("class WpFile")]
     cols = re.findall(r"^\s+(\w+): Mapped", mat_block, re.M)