Database Migration Procedure

The rule: the schema moves before the image that expects it, every time, by the same mechanism, and a roll that cannot move the schema says so loudly instead of rolling anyway.

How the schema moves

There is one migration program (Memex.Database.Migration, built as memex-migration:<tag> beside memex-portal-ai:<tag> from the same commit — the two share DbVersion.Latest). It reads admin.mesh_nodes.db_version, applies every V##_* above it, writes the new version, then runs the always-on reconcile (partition access, doc backfill, embeddings, Orleans clustering, searchable schemas) and exits. It is idempotent: at latest it applies nothing and reconciles.

Two things mint the Job that runs it:

The portal's DbVersionGate is the backstop, not the mechanism: it refuses to start a pod whose build expects a db_version the database has not reached. That refusal is correct — and it is invisible from the front door: the crash-looping pod is out of the Service endpoints, the old ReplicaSet keeps serving, and https://…/ answers 200 the whole time.

🚨 The wedge this procedure exists for (2026-09-03)

Both AKS portals had been rolling images by kubectl set image (the self-updater) for days without a helm upgrade — memex since revision 28 (Aug 30), memex-cloud since revision 21 (Aug 30). Plugins PR #1216 added V55 (the pg_notify payload carries node_type; DbVersion.Latest = 55). The next self-update rolled memex-portal-ai to a build expecting 55; nothing minted a migration Job; the DB stayed at 54:

crit: Memex.Portal.Distributed.DbVersionGate[0]
      DB migration incomplete: admin.mesh_nodes.db_version=54 < expected 55. … Refusing to start.

memex: 45 restarts over ~7 h behind a 200. memex-cloud: the same, while its eight old pods kept running pre-#1216 code — the very fan-out storm #1216 fixes (1,917 slow 201-schema UNIONs per 30 min per pod) — so the site was up and unusable at the same time.

kubernetes.io/change-cause still read "roll to 3.0.0-rc8.ci.5083 … 2026-08-23" at deployment revision 641: set image never updates it. Read meshweaver.io/self-update-rolled-at and the container image instead.

The routine

1. Adding a migration (developer). Drop V##_*.cs under Memex.Database.Migration/Migrations and bump DbVersion.Latest; MigrationRegistry.VerifyComplete() refuses a mismatch at startup. Say so in the PR title — a reviewer should see "schema" without opening files. Nothing else: the self-updater runs it.

2. One-time grant per install (operator). The self-updater creates Jobs under memex-portal-sa; the chart's memex-portal/rbac.yaml grants batch/jobs create,get,list,delete. An install that has not been helm upgraded since that rule landed answers the Job POST with 403 — and the roll is refused (see the table below), because that helm upgrade is the same act that mints the migration Job. Do it once and it stops.

3. Every automatic roll. RunMigrationAsync(tag) creates memex-migration-su-<tag> (same ConfigMap and Secret as the helm Job, same image tag as the portal it precedes), waits for status.succeeded within SelfUpdate:MigrationJobTimeout (30 min), and only then patches the portal image. The rule is that only Completed proves the schema moved, and the four other outcomes are not one case:

NotSupported is the one non-success outcome that still rolls, and deliberately: an install that can never migrate would otherwise be frozen for ever, and silently, which is the worse failure shape. What makes that safe to leave is that the roll is no longer indistinguishable from a migrated one — the record says UNMIGRATED and says what to update.

3b. Every MANUAL roll — the Updates tab's Apply button — follows the same rule. It is a route that patches, so it migrates first and refuses on the same outcomes; the decision is literally the same predicate (SelfUpdateVerdict.MayPatchAfter), read by both, because two copies of it drift. The button had no migration step at all until #4764: it honoured the release-availability gate, the combo gate and the control-lane route and then called PatchToVersionAsync directly, so an admin click made exactly the image-only roll the poller had stopped making — and left no verdict anywhere, which is worse than the poller's blind branch. A refusal now renders on the tab's own result line, with the two cases kept apart: a migration that RAN and broke sends you to the Job's log, one that could not be created sends you to the helm upgrade.

🚨 Why Forbidden is a refusal and not the same permissive case — it read as one until #4764. Measured on memex-cloud 2026-09-19: a roll that patched the image with nothing established about the schema put the new pod in CrashLoopBackOff on DbVersionGate 3,319 ms into its boot, restarts=2 within three minutes, while four old pods kept answering 200 — desired 4, ready 4 all on the OLD ReplicaSet, updated 1, unavailable 1. Nothing converged, nothing rolled back, and the policy record still said the roll was made. A 403 costs an operator nothing extra to clear, and the helm upgrade that clears it runs the migration in the same act.

4. Verifying. helm list -n <ns> against kubectl get deploy memex-portal-deployment -n <ns> -o jsonpath='{.spec.template.spec.containers[0].image}': divergence means the schema and the code are on different clocks. A migration Job's success line is literal: Database migration completed. Version: N. Anything else is not a pass.

Recovery when a pod is refusing on db_version

Do not helm upgrade with the values on file — they pin the image the chart last knew (rc8 on both portals that day), and an upgrade would roll the portal back. Mint the Job by hand at the tag the deployment is already on:

apiVersion: batch/v1
kind: Job
metadata: { name: memex-migration-v55-manual, namespace: <ns> }
spec:
  backoffLimit: 6
  ttlSecondsAfterFinished: 3600
  template:
    spec:
      restartPolicy: Never
      containers:
        - name: memex-migration
          image: meshweaver.azurecr.io/memex-migration:<the deployment's tag>
          envFrom:
            - configMapRef: { name: memex-migration-config }
            - secretRef: { name: memex-migration-secrets }

az aks command invoke … --file job.yaml --command "kubectl apply -f job.yaml", then wait for Database migration completed. Version: N. The gate is db_version < expected, so migrating forward never breaks the pods still serving on the older build. (Break-glass form: a HelmRelease deploy Hosting/InstanceAction runs the same Job through helm upgrade with no hand-applied manifest — OperatingFromThePortal.)

🚨 Why a migration deadlocks under load — and what to do until the fix ships

The migration runner's schema initialisation drives mw_auth_mirror_heal_batch — per partition schema it CREATE OR REPLACEs the access trigger functions, re-installs triggers on mesh_nodes, and re-runs rebuild_user_effective_permissions() (an ACCESS EXCLUSIVE rename-swap of the permission table). A live pod meanwhile holds ACCESS SHARE on those same tables across a multi-schema UNION, or a row lock on access inside the very trigger being replaced. Opposite orders ⇒ 40P01 deadlock detected, and Postgres kills the migration. On memex-cloud it died five times out of five at eight replicas, before it had even read db_version; at three replicas it completed in 11 minutes. Two consequences:

• A migration image can fail under load even with no new V## — the heal batch runs regardless.
• Until the heal batch yields instead of waiting (lock_timeout + retry per batch, MeshWeaver.Plugins), run a contended migration in a quieter window: pause the rollout, scale the portal down (the site stays up on fewer pods, and fewer pods also means fewer fan-out queries), run the Job, scale back, resume.

🚨 A long-running Job is not a stuck Job — measure it before you kill it

The schema steps finish in seconds to minutes; the always-on reconcile that follows (partition access, doc backfill, embeddings) is a loop over every partition schema, and on a large install it runs for hours. Elapsed time therefore says nothing about health, and "it has been running all day, it can never finish" is a conclusion that has been reached — and been wrong.

Measured on memex-cloud 2026-09-07: Job memex-migration-28 was described as a backfill that "can never finish within its own design" and slated for deletion after 9 h 50 m. Its own log said otherwise — Current DB version: 55 (the schema half had completed 90 s in), 165 of the 220 partition schemas the run had announced, 74,246 rows embedded, and a steady 200 rows per 81–86 s. It was left to run, and it finished on its own ~2 h later: Database migration completed. Version: 55, Job Complete 1/1, duration 10 h, then ttlSecondsAfterFinished removed it with no manual step. Nothing needed intervention at any point — both portals served HTTP 200 throughout.

Read these five before deciding, in this order — they are all in the Job itself:

Deleting a reconcile that is progressing costs the un-embedded tail: those rows stay NULL and their partitions answer vector search poorly until some later migration reaches them again. The per-row writes are committed as they go, so a delete loses only the row in flight — but it also loses the remaining work, which nothing re-queues on its own.

When it genuinely is stuck, the tell is a counter that does not move (or 40P01 in the log, which is the deadlock above), never the elapsed time. And the durable fix for the long tail is not a kill: it is the budgeted, batched reconcile — a bounded slice per Job that reports its remainder and resumes on the next one — so no single Job has to finish the whole backfill.

🚨 The budget is not retroactive, so "it should have stopped after ten minutes" is not a reason to kill one. The migration image carries its own budget: memex-migration-28 ran 3.0.0-ci.8009, which predates both the ten-minute Job budget (MeshWeaver#3630) and the batched backfill (MeshWeaver.Plugins#1488). A Job only becomes time-bounded once its environment rolls onto a set built after those landed — until then a migration running for hours is behaving exactly as its image was built to, and the readings above are the only way to judge it.

Related

• Deployment — AKS — the runbook this procedure is the schema half of.
• Release & Self-Update Strategy — what rolls, when.
• Postgres Schema Architecture — one schema per partition, which is why every migration step is a loop.