Moving ever-async-prod from SQLite to Postgres

This is a plan, not a change log

Nothing in here has been executed. No manifest, no config and no database has been touched to write it. Everything under What is true today was read with get/exec … psql -Atc/gh api and nothing else.

The plan also contains work that does not exist yet — the row copier has to be written (see Step 1). The delivery claim that makes a second replica safe did not exist when this was written and now does: Storage::claim_pending_nudge has shipped in all four backends, with a live Postgres race test (see §4). Read both sections before estimating this.

Why this exists

ever-async-prod runs one replica against a SQLite file on a ReadWriteOnce Ceph RBD volume, with strategy: Recreate. Recreate is not a preference — an RWO volume cannot be attached to the new pod until the old one has let go, so a rolling update would deadlock on multi-attach. The consequences are:

• every image roll is a full outage of roughly 40s (old pod terminates, volume detaches, new pod schedules, attaches, boots, passes the startup probe);
• the app cannot scale horizontally at all, so there is no redundancy for a node drain, an eviction, or a bad node;
• SQLite's single writer is load-bearing for correctness today: it is what makes claim_event and /async off trivially consistent, precisely because there is only one process.

The exit is the Storage trait. Two Postgres-capable backends already exist — crates/plugins/storage-orm (SeaORM, one query set for SQLite/Postgres/MySQL) and crates/plugins/storage-postgres (hand-written tokio-postgres) — and crates/plugins/storage-orm/tests/postgres_parity.rs runs them side by side against one server asserting that each reads what the other wrote. See Multi-tenancy → Picking a database for why the choice is a config line rather than a fork.

0. What is true today

Verified 2026-08-08 against the live fleet. Re-check anything here that is older than a day before you act on it.

Fact	Value	Read with
Prod pods	1 × web-… on k8s-w5, Running	kubectl -n ever-async-prod get pods -o wide
Volume	PVC everasync-data, 5Gi, ceph-rbd, RWO, Bound	kubectl -n ever-async-prod get pvc
Deployment	replicas: 1, strategy: Recreate, no config checksum annotation	apps/ever-async-prod/deploy-web.json
Config	[storage] driver = "sqlite", path = "/app/data/everasync.db"	apps/ever-async-prod/cm-everasync-config.json
On disk	everasync.db 4 KiB, -wal 326 KiB, -shm 32 KiB; 4.9G free on the PVC	kubectl -n ever-async-prod exec deploy/web -- ls -la /app/data
Image contents	no sqlite3 binary in the container	… exec deploy/web -- sh -c 'command -v sqlite3'
ArgoCD app	Synced / Healthy, automated with selfHeal: true, prune: false	kubectl -n argocd get application ever-async-prod -o json
Secrets	everasync-secrets is a hand-made Secret (1 key). No ExternalSecret in the namespace	kubectl -n ever-async-prod get secret,externalsecret
Velero PVB	daily-all-20260808060042-2s5vv, volume data, Completed, 370 616 B, 06:37:36Z	see §2
Velero parent	daily-all-20260808060042 — 🛑 PartiallyFailed, 1 error, 229 warnings, expires 2026-08-22	see §2
CNPG cluster	databases/pg, 3/3, healthy, primary pg-1	kubectl get cluster.postgresql.cnpg.io -A
Postgres version	18.4 — not the 17 the backends were tested against	psql -Atc "show server_version"
Tenants on it	70 databases, incl. 🛑 gauzy (24 GB, production), trigger, githands, 40+ CMS DBs	psql -Atc "select datname …"
Connections	max_connections = 300, 207 in use (189 idle) → ~93 free	psql -Atc "select count(*) from pg_stat_activity"
Postgres nodes	exactly 3 — ever-db-w1/w2/w3, 8 vCPU each, workload=postgres	kubectl get nodes -l workload=postgres
Backups (CNPG)	pg-nightly not suspended, 0 0 2 * * *, newest Backup completed, backupId 20260808T020001	see §2
WAL archiving	last_archived_time 2026-08-08 15:15Z > last_failed_time 2026-07-29 19:05Z ✅	see §2
🛑 Cluster.status.lastSuccessfulBackup	2026-07-05T17:36:02Z — 34 days stale, and wrong	see the trap
Digest loop	[digest] is commented out in prod, so no digest is being delivered	apps/ever-async-prod/cm-everasync-config.json

Two facts on that list drive most of what follows.

The database is 4 KiB and the WAL is 326 KiB. In WAL mode the committed rows live in everasync.db-wal until a checkpoint, which is why the main file looks empty. A file-level copy of the three files is only as consistent as the instant each was read; the migration must therefore either quiesce the writer or go through SQLite's own snapshot path, never cp. It also means the total payload is ~370 KB — this is a minutes operation, not an hours one.

selfHeal: true. A hand kubectl scale or kubectl edit on this namespace is reverted by ArgoCD within seconds. Every state change in this plan that touches the Deployment or the ConfigMap therefore has to be a commit to ever-co/k8s-gitops, which auto-syncs — so the commit is the live change and needs a claim.

1. The decision the owner has to make

Where does Ever Async's Postgres live: on the fleet's shared CNPG cluster (Cluster/pg in namespace databases), or in a database of its own?

This is the only decision in the document that is not mechanical, and it is genuinely two-sided. Both options are written out at full strength before the recommendation.

Context that constrains both options

Cluster/pg is owned by ArgoCD Application databases, which runs with automated sync deliberately OFF (manual sync, prune: false). That cuts both ways: a bad commit will not auto-apply to the database that backs production Gauzy, and equally, a good commit does nothing until someone syncs it on purpose. Whichever option is chosen, the DB spec is edited in ever-co/k8s-gitops at apps/databases/ and then synced deliberately — never with kubectl edit, because here selfHeal will not rescue a hand edit, it will silently let it drift.

Option A — put it on the shared CNPG cluster pg

Add a role and a Database CR next to the ones already there (apps/databases/database-cloc.yaml is the template: owner, cluster.name: pg, ensure: present, databaseReclaimPolicy: retain).

For:

• It is already proven, backed up and monitored. Nightly ScheduledBackup to the barman-cloud object store, WAL archiving verified healthy, a PodMonitor, an operator that handles failover, and 34 days of it working. A new cluster starts with none of that and has to earn it.
• The marginal cost is genuinely tiny. 370 KB against a 24 GB gauzy database on a cluster already carrying 70 databases. Ever Async's write pattern is a handful of small inserts per chat message and one ON CONFLICT DO NOTHING per webhook. In storage and IOPS terms this is noise.
• Zero new operational surface. No second cluster to upgrade across major versions, no second backup destination to prove, no second set of alerts, no second thing that is nobody's job at 2 a.m.
• It matches how every other app on this fleet gets a database — cloc, trigger, githands, ever_works and the CMS fleet all live here. A one-off arrangement for Ever Async is a thing future maintainers have to discover.

Against — and this is the real risk:

• Blast radius. 🛑 pg backs production Ever Gauzy (api.gauzy.co), the only production system on this fleet. Anything that takes pg down takes Gauzy down. Ever Async would be joining that failure domain.
• The connection budget is the sharp edge, not the disk. max_connections = 300 and 207 are already in use. The ORM backend's default pool is DEFAULT_POOL_SIZE = 16 per process. Three replicas at the default would take 48 of the ~93 remaining connections — over half the headroom — and a connection exhaustion on pg is a Gauzy outage, not an Ever Async one. This is a real noisy-neighbour vector and it is not hypothetical.
• Coupled maintenance. A pg major upgrade, a switchover or a restore test now has Ever Async in its blast radius too, so the change window has one more consumer to coordinate.
• Shared pg_stat_statements, shared page cache, shared WAL. A pathological query from any tenant is felt by all of them.

Option B — a dedicated Postgres for Ever Async

A second CNPG Cluster (its own name, its own namespace or a distinct name in databases), its own ObjectStore + ScheduledBackup, its own monitoring.

For:

• Isolation of the logical failure domain. A runaway Ever Async query, a bad migration, a connection leak, cannot reach Gauzy.
• Independent lifecycle. Upgrade, restart, restore or resize it without a conversation about production Gauzy.
• Its own connection budget, sized for one app instead of borrowed from a shared pool that is already 69% consumed.
• It is the shape the product would take at a real SaaS scale, so it is not wasted work if app-async.ever.co grows.

Against:

• A database is not a deployment; it is a standing commitment. Backups that are proven restorable, WAL archiving that is watched, alerts that route, a major-version upgrade path, and a restore rehearsal. The fleet's own history is the argument here: the cluster that already exists has a status field that has been silently lying for 34 days (see the trap). A second cluster doubles the number of places that can lie.
• 🛑 "Dedicated" would not actually be isolated on this fleet. There are exactly three nodes labelled workload=postgres — ever-db-w1/w2/w3 — and the existing pg runs one instance on each, on local-path storage, which is node-local disk. A second three-instance cluster lands on the same three nodes and the same three disks. It would get its own connection limit and its own catalog, but it would share the page cache, the disk queue and the node's memory with production Gauzy. So Option B buys logical isolation and pays full operational price, while buying much less physical isolation than the word "dedicated" suggests. Real isolation needs new nodes, which is a hardware decision, not a database decision.
• Roughly 3 × (1 vCPU + 4–16 GiB) of committed capacity on nodes that already host the most important database on the fleet.

Recommendation

Take Option A — put Ever Async on the shared pg cluster — with the connection budget capped explicitly, and revisit if the workload ever stops being small.

The reasoning, in order of weight:

1. The dominant risk in Option A is connections, and connections are directly controllable. Set pool_size = 5 in [storage] and the whole app at three replicas costs at most 15 connections out of ~93 free — around 5% of the server's limit. That is a number that can be asserted in review and alerted on. Contrast it with Option B's dominant risk — an unproven backup on a second cluster — which is controllable only by ongoing discipline, and which this fleet has already demonstrated is easy to get wrong.
2. Option B's headline benefit is mostly not available here. With only three workload=postgres nodes and node-local storage, a "dedicated" cluster shares the physical resource that actually matters with Gauzy anyway. Paying the full operational price for partial isolation is a bad trade.
3. The workload is genuinely tiny and bounded. 370 KB of state today; the write rate is bounded by Slack message volume in one workspace. If that assumption ever breaks — a real multi-tenant SaaS on app-async.ever.co — that is the trigger to revisit, and the migration Option A → Option B is a pg_dump | psql of a small database plus one url_env change. Option A does not foreclose Option B. The reverse is also true, but Option B costs its price from day one.
4. It keeps Ever Async consistent with every other app on the fleet, which is worth more than it sounds for a service that one person will be debugging at some point without this document in front of them.

The conditions attached to that recommendation — treat these as part of the decision, not as footnotes:

• pool_size is set explicitly to 5 and never left at the default 16.
• The app connects to the session endpoint pg-rw.databases.svc.cluster.local:5432, not the PgBouncer pooler on 192.168.1.185. The pooler runs poolMode: transaction with no max_prepared_statements, and sqlx (under SeaORM) uses named prepared statements — that combination is a known breakage. It is not proven broken here, which is exactly why it should not be introduced in the same change as the storage swap. If connection count later becomes a problem, moving to the pooler is a separate, testable change.
• An alert exists for connection saturation on pg before Ever Async's replica count is raised.
• The revisit trigger is written down: more than one paying tenant, or a sustained write rate above the "one Slack workspace" order of magnitude.

2. Backups-first

Per the fleet's non-negotiable rule: prove the backups are alive before touching anything data-bearing. "Configured" is not "working". Two independent systems have to be proven here, because two different data sets are at risk: Ever Async's own SQLite file (Velero) and the shared Postgres (CNPG).

Record the answers — the actual timestamps and ids, not "checked" — in the MAINTENANCE.md claim before the first state-changing command.

2a. Ever Async's own data — Velero

A PodVolumeBackup for ever-async-prod/data completed on the morning of 2026-08-08: daily-all-20260808060042-2s5vv, phase Completed, 370 616 bytes, finished 06:37:36Z. Re-verify it — and re-verify it again immediately before the freeze, not just at the start of the day:

kubectl -n velero get podvolumebackups \
  -o jsonpath='{range .items[?(@.spec.pod.namespace=="ever-async-prod")]}{.metadata.name}{"\t"}{.spec.volume}{"\t"}{.status.phase}{"\t"}{.status.completionTimestamp}{"\t"}{.status.progress.totalBytes}{"\n"}{end}'

Expected: one line, volume data, phase Completed, a completionTimestamp from today, and a byte count in the same order of magnitude as the file on the PVC. A byte count far below ~370 KB means it did not capture the WAL and the backup is not the database you think it is.

Two traps in that one command

kubectl -n velero get backup does not mean what you want. The short name backup resolves to CNPG's backups.postgresql.cnpg.io, not Velero's. Asking for the parent backup by short name returns Error from server (NotFound): backups.postgresql.cnpg.io "daily-all-…" not found, which reads like "the backup is missing" and is actually "you asked the wrong API". Always spell it backups.velero.io.

A completed PodVolumeBackup does not mean the parent backup succeeded. The parent of this morning's PVB is daily-all-20260808060042, and its phase is 🛑 PartiallyFailed — 1 error, 229 warnings, 13 279/13 279 items. The Ever Async volume is fine; something else in that 13 000-item sweep is not. Check both:

kubectl -n velero get backups.velero.io --sort-by=.status.startTimestamp \
  -o custom-columns=NAME:.metadata.name,PHASE:.status.phase,ERR:.status.errors,WARN:.status.warnings,EXPIRES:.status.expiration | tail -3

PartiallyFailed does not block this migration — the specific volume completed — but it must be read and stated in the claim, not glossed. Do not report "the backup is green" when the parent object says otherwise.

Neither of those checks proves the backup is restorable. Nobody has restored it. The strongest available proof, and the one this plan actually leans on, is different and cheaper: the migration itself takes a VACUUM INTO snapshot onto the same PVC before it reads anything (see Step 4). That snapshot is a single, self-contained, checkpointed SQLite file sitting next to the original — and the original is not modified at all. That is the rollback, and it is verifiable by inspection. Velero is the second line, for the case where the whole PVC is lost.

If you want the Velero restore genuinely proven, do it as a separate exercise before migration day: restore the PVB into a scratch namespace with a different PVC name and open the file. Do not fold a first-ever restore test into the migration window.

2b. The Postgres side — CNPG

Three checks, in this order. All are read-only.

# 1. The schedule is not suspended, and it actually ran.
kubectl -n databases get scheduledbackup pg-nightly \
  -o jsonpath='suspend={.spec.suspend}{"\n"}schedule={.spec.schedule}{"\n"}lastScheduleTime={.status.lastScheduleTime}{"\n"}'

Expected today: suspend= (absent, i.e. not suspended), schedule=0 0 2 * * *, lastScheduleTime from last night. A suspend=true or a lastScheduleTime older than about 26 hours stops the migration.

# 2. The newest Backup objects completed, with a real backupId and no error.
kubectl -n databases get backups.postgresql.cnpg.io \
  --sort-by=.metadata.creationTimestamp \
  -o custom-columns=NAME:.metadata.name,PHASE:.status.phase,ID:.status.backupId,ERROR:.status.error | tail -3

Expected: phase=completed, a non-empty backupId (this morning's was 20260808T020001), and an empty ERROR column. phase=completed with an empty backupId is not a backup.

# 3. WAL archiving on the PRIMARY — the check that catches a silently dead archiver.
PRIMARY=$(kubectl -n databases get cluster pg -o jsonpath='{.status.currentPrimary}')
kubectl -n databases exec "$PRIMARY" -c postgres -- psql -U postgres -Atc \
  "select last_archived_time,
          last_failed_time,
          last_archived_time > coalesce(last_failed_time, '-infinity') as healthy
     from pg_stat_archiver;"

Expected: healthy is t. Today it reads 2026-08-08 15:15:59Z | 2026-07-29 19:05:45Z | t — archiving is current and the last failure is ten days stale. If healthy is f, the base backups are being taken but the WAL stream between them is broken, so point-in-time recovery is not available: stop and fix that first.

🛑 The trap: the cluster's own backup status lies

Do not judge CNPG backup health from Cluster.status. Under the barman-cloud plugin (which this cluster uses — plugins: [{name: barman-cloud.cloudnative-pg.io, isWALArchiver: true}]), the operator does not keep those fields current. Live, right now:

Cluster/pg .status.lastSuccessfulBackup      = 2026-07-05T17:36:02Z   ← 34 days stale
Cluster/pg .status.firstRecoverabilityPoint  = 2026-07-05T17:36:02Z   ← same lie
Backup/pg-nightly-20260808020000 .status     = completed, backupId 20260808T020001  ← the truth

Reading lastSuccessfulBackup would tell you backups have been broken for five weeks. They have not been. The authoritative sources are the individual Backup objects and pg_stat_archiver, which is why they are checks 2 and 3 above. This trap has burned this fleet before; it is written into the fleet rules for that reason.

The restore point to record

Write all four of these into the claim before the first state-changing command:

What	Value on 2026-08-08
Velero PVB for ever-async-prod/data	daily-all-20260808060042-2s5vv, Completed, 370 616 B, 06:37:36Z
Velero parent backup + honest status	daily-all-20260808060042, PartiallyFailed (1 err / 229 warn), expires 2026-08-22
Newest CNPG backup	pg-nightly-20260808020000, completed, backupId 20260808T020001
WAL archiving	last_archived_time 15:15:59Z > last_failed_time 2026-07-29 → healthy

Plus, once Step 4 has run, the path and byte size of the VACUUM INTO snapshot on the PVC. That file is the primary restore point for this change.

3. The migration

Short version: quiesce the single writer, take a self-contained SQLite snapshot on the same volume, copy ten tables into a fresh Postgres database with a purpose-written tool, compare counts and spot-check contents, flip one config line, restart. Every step has an undo that does not depend on the step after it.

Step 0 — claim it

E:\Coding\_LOCAL\MAINTENANCE.md, before the first state-changing command, then commit and push — an unpushed claim does not exist. The claim must name two targets, because two are being touched:

• ever-k8s / ever-async-prod — the app, its ConfigMap, its Deployment.
• ever-k8s / databases (CNPG pg) — 🛑 a shared dependency that also backs production Ever Gauzy. Creating a role and a database on it is a live change to a shared resource, and another agent holding databases blocks this work entirely.

Also note in the claim that any push to ever-co/k8s-gitops under apps/ever-async-prod/ auto-syncs, so the commit is the live change.

Step 1 — write the copier (it does not exist)

There is no migration tool today, and no off-the-shelf one is a good fit. It has to be written. Being plain about that: everasync has exactly four subcommands — serve, init, doctor, auth (crates/cli/src/main.rs) — and none of them moves data. The Storage trait cannot be used to copy either: it exposes load_pending_nudges() but has no way to enumerate events, seen_events, policies, settings, nudges, contexts, tenants or workspace_bindings. A copier built on the trait is not possible without widening the trait.

What to build

A new subcommand, everasync migrate-storage, implemented as crates/plugins/storage-orm/src/copy.rs and wired into the CLI beside the existing four:

everasync migrate-storage --from sqlite:///app/data/<snapshot>.db \
                          --to-env EVERASYNC_DATABASE_URL \
                          [--dry-run] [--allow-nonempty]

It lives in storage-orm because that crate already owns the single description of every table (src/entity.rs) — a copier anywhere else would be an eleventh place the schema is written down, which is the exact bug that crate exists to end. It needs no new dependencies: sea-orm, chrono, serde_json and tracing are already in [workspace.dependencies] and already used there.

Required behaviour, in order:

1. Refuse anything but sqlite: for --from, and anything but postgres:/postgresql: for --to. Reuse the existing backend_for().
2. Open the source as a bare Database::connect, never through OrmStorage::connect. 🛑 This is the single most important line in the spec. OrmStorage::connect runs Migrator::up — against a pre-tenancy file that rebuilds four tables, and against any file it sets PRAGMA journal_mode=WAL and writes seaql_migrations. The source is production's only copy of its own data and must come back byte-identical.
3. Open the target through OrmStorage::connect, so the schema is created by exactly the migrations the app will run at boot (baseline + digest_indexes), under the Postgres advisory lock, with seaql_migrations correctly stamped. Do not hand-write DDL.
4. Refuse to run if any target table is non-empty, unless --allow-nonempty. Three tables — events, nudges, contexts — are append-only logs with no primary key in any backend, so a second run would silently double them. Refusing by default makes the retry story "drop the database, recreate, re-run", which is clean.
5. Copy table by table with Entity::find().all() → Entity::insert_many() in batches (500 is fine at this size). There are no foreign keys anywhere in the schema, so order does not matter. For the seven keyed tables (tenants, memberships, workspace_bindings, seen_events, policies, settings, pending_nudges) attach .on_conflict(...do_nothing()) so a partial retry is idempotent; the three keyless log tables get a plain insert and rely on rule 4.
6. Count both sides per table and print the comparison, exiting non-zero on any mismatch. The verification in Step 5 is then something the tool does, not something a human remembers to do.
7. --dry-run reads and reports without writing anything to the target.

Reading a file written by SqliteStorage through the SeaORM entities is sound: that backend already creates the current, tenant-scoped schema (crates/core/src/storage.rs, SCOPED_TABLES + TENANCY_TABLES), with the same table and column names. The only type nuances are is_edit INTEGER → bool and INTEGER → BIGINT, both of which are SQLite affinities that sqlx decodes correctly — and the test below is what proves it rather than asserting it.

Tests that must ship with it

• Source is untouched. Hash the source file before and after a copy and assert equality. This is the test that protects production's only copy; write it first.
• Round trip through the real writer. Build a SQLite database with SqliteStorage (already a dev-dependency path in this workspace), write at least one row into all ten tables, copy to a second SQLite database, assert per-table counts and spot-check content: a charter, a policy JSON blob, and a pending_nudges row whose tenant comes from the column and not the JSON blob (see storage-orm/src/map.rs).
• Live SQLite → Postgres, #[ignore]d and gated on EVERASYNC_TEST_PG_URL like the existing tests/live_postgres.rs, so CI never needs a database.
• A non-empty target is refused without --allow-nonempty.

Alternatives considered, and why not

• pgloader. It really does SQLite → Postgres. But it introduces a new tool and container image to the fleet, needs its own type mapping (is_edit INTEGER → SQL boolean), and produces tables whose provenance is pgloader rather than the app's own migrations — so the app's first boot runs Migrator::up against a schema it did not create, and any type difference (text vs varchar(255)) shows up later as a surprise rather than now as a test failure.
• sqlite3 .dump + hand-edited SQL. 🛑 There is no sqlite3 binary in the image (verified), so this needs a second container anyway — and it ends with hand-edited SQL run against production data with no test behind it.
• Start Postgres empty and let the app re-learn. Worth stating honestly, because it is cheap: the log tables (events, nudges, contexts) only feed digest counters, and seen_events only matters for a few minutes of webhook dedup. What is not reconstructible is user intent — the charter and the per-conversation policies (every /async off anyone has ever typed) — plus workspace_bindings. If the copier turns out to be more work than the owner wants, a defensible reduced scope is "copy settings, policies, workspace_bindings, tenants, memberships, pending_nudges; let the three log tables start empty", accepting a one-off discontinuity in digest counters. It is the same tool with a --tables flag, so it saves verification effort, not code. At 370 KB there is no reason to take it.

Step 2 — create the role and the database

In ever-co/k8s-gitops, apps/databases/, following database-cloc.yaml:

• a role ever_async with a generated password, and
• database-ever-async.yaml — owner: ever_async, cluster.name: pg, ensure: present, name: ever_async, databaseReclaimPolicy: retain, finalizer cnpg.io/deleteDatabase.

🛑 The databases Application is manual-sync. The commit changes nothing until it is synced on purpose, and selfHeal will not correct a hand edit here — it will let it drift silently. Commit, then sync databases deliberately, then confirm with kubectl -n databases get databases.postgresql.cnpg.io.

The connection URL is a secret and never goes in the ConfigMap. Two ways to deliver it, and the choice should be made now rather than drifted into:

• Preferred: an ExternalSecret reading OpenBao at kv/ever-co/ever-async/prod, the way the rest of the fleet does it. There is no ExternalSecret in this namespace today, so this is new work — but it is the shape the app README already says the secrets should arrive in.
• Interim: add the key to the existing hand-made everasync-secrets Secret.

Either way, 🛑 an OpenBao write is not live until the k8s Secret has been rewritten and the pod restarted. The default refresh is an hour. Force the sync, restart, and then verify the value the process got, not the one the Secret holds:

kubectl -n ever-async-prod annotate externalsecret everasync-secrets \
  force-sync=$(date +%s) --overwrite
kubectl -n ever-async-prod rollout restart deploy/web
kubectl -n ever-async-prod exec deploy/web -- sh -c 'printenv EVERASYNC_DATABASE_URL | cut -c1-11'

The cut keeps the scheme and nothing else on screen — enough to prove the variable arrived, not enough to leak a password.

Undo for this step: none needed. An unused role and an empty database on a cluster that already has 70 of them cost nothing and are not in any path. Leave them if the migration is abandoned; do not delete anything.

Step 3 — rehearse in dev, then stage

Run the entire remainder of this plan against ever-async-dev, then ever-async-stage, before prod. Both are single-replica, both are the same shape, and the cascade develop → stage → main is how this repo promotes anything.

Two things this rehearsal exists to prove, both currently unverified:

1. 🛑 Postgres 18.4 vs the tested 17. The backends' live tests are written for and have been run against postgres:17-alpine (see the module docs in storage-orm/src/lib.rs). The fleet runs 18.4. Nothing in the code is version-sensitive on its face, but "should be fine" is not a check. Run the ignored suites against an 18 container before touching prod:

   docker run -d --name everasync-pg18 -e POSTGRES_PASSWORD=postgres \
     -e POSTGRES_DB=everasync_test -p 45432:5432 postgres:18-alpine
   export EVERASYNC_TEST_PG_URL=postgresql://postgres:postgres@127.0.0.1:45432/everasync_test
   cargo test -p ever-async-storage-orm      -- --ignored
   cargo test -p ever-async-storage-postgres -- --ignored
   docker rm -f everasync-pg18

2. Pool behaviour under the chosen pool_size. Confirm on stage that a single replica at pool_size = 5 holds ~5 connections and not 16, with select count(*) from pg_stat_activity where usename = 'ever_async';.

Step 4 — freeze and snapshot

The writer must be stopped before the file is read, because a live WAL is a moving target and the alternative — copying while the app writes — silently loses every row written between the copy and the cutover.

Freeze via git, not via kubectl. 🛑 kubectl scale deploy/web --replicas=0 is reverted by ArgoCD selfHeal within seconds. Commit replicas: 0 to apps/ever-async-prod/deploy-web.json and let auto-sync scale it down; that keeps the freeze in git and one git revert away.

kubectl -n ever-async-prod get pods -w   # wait until zero pods remain

Then run a one-shot Job that mounts everasync-data (now free, since RWO has no consumer) with the same ghcr.io/ever-co/everasync:prod image, runAsUser: 999, fsGroup: 999, and:

1. VACUUM INTO '/app/data/premigration-YYYYMMDD.db' — a single, checkpointed, self-contained SQLite file next to the original. This is the primary rollback artefact and it costs ~370 KB on a volume with 4.9 GB free.
2. everasync migrate-storage --from sqlite:///app/data/premigration-YYYYMMDD.db --to-env EVERASYNC_DATABASE_URL.

Apply that Job by hand (kubectl create -f) and delete it when it has finished — do not put it in apps/ever-async-prod/. The Application runs prune: false, so a one-shot object committed there would live in the cluster forever with nothing to remove it. This is one of the few cases where hand-kubectl is the correct tool, precisely because the object is not meant to persist.

Undo: git revert the replicas commit. The app comes back on the untouched SQLite file, exactly as it was. Nothing about the Postgres side has been consumed yet.

Step 5 — verify, before anyone can see it

While still frozen, with the app not yet pointed at Postgres:

• Row counts. The copier prints per-table counts for both sides and exits non-zero on a mismatch. Keep that output; paste it into the change log.

• Content spot-check, run by hand against the target so it is not the same code checking itself:

  -- the charter: one row per tenant, and it must round-trip verbatim
  select tenant, key, json from settings where key = 'charter';
  -- policy overrides: every /async off anyone ever typed
  select tenant, channel, conversation, json from policies order by 1,2,3;
  -- workspace bindings: what resolves an inbound webhook to a tenant
  select * from workspace_bindings;
  -- nudges that are mid-flight and must survive the cutover
  select tenant, message_key, due_at_ms from pending_nudges order by due_at_ms;

  Compare each against the same query on the snapshot file. These four tables are the ones carrying user intent; the log tables only need their counts to match.

• Schema provenance. select * from seaql_migrations; must list both migrations, proving the tables were created by the app's own migrator and not by the copier.

If anything mismatches: drop the database, recreate it from the Database CR, and re-run the copier. Nothing in production has changed yet — the app is still configured for SQLite and the file is still intact.

Step 6 — cut over

One commit to ever-co/k8s-gitops, containing both changes so they land together:

[storage]
driver = "orm"
url_env = "EVERASYNC_DATABASE_URL"
pool_size = 5
# path = "/app/data/everasync.db"   # pre-2026-08 SQLite file, kept on the PVC as the
#                                   # rollback source. Uncomment (and drop the two
#                                   # lines above) to go back.

and replicas: 1 restored in deploy-web.json.

Notes that matter:

• driver = "orm", not "postgres". Both open the same tables and tests/postgres_parity.rs asserts they agree, but the ORM backend is the one with versioned migrations and a single query set, and it is the one whose concurrency behaviour has been raced against a real server. driver = "postgres" stays available as a second rollback rung (see the table below).

• Comment path out, do not delete it. Additive and reversible by default; it also makes the rollback a one-line uncomment.

• Keep the PVC and its volumeMount. They hold the original file and the pre-migration snapshot. Removing them is a separate decision for a later day, and only the owner's to make.

• 🛑 The ConfigMap is mounted with subPath, which is a one-shot copy — it never hot-reloads. ArgoCD will update the ConfigMap and change nothing in a running pod. The Deployment carries no config checksum annotation (metadata.annotations: {}), so nothing rolls the pods automatically either. A restart is mandatory:

  kubectl -n ever-async-prod rollout restart deploy/web
  kubectl -n ever-async-prod rollout status  deploy/web --timeout=300s

  Here the scale-up from 0 does that for free, but the same rule applies to every later config change. (Optional, additive follow-up: add a checksum/config annotation to the pod template so a ConfigMap change rolls the pods by itself. Separate change, separate review.)

Step 7 — verify live

# The pod reports the backend it actually opened.
kubectl -n ever-async-prod logs deploy/web | grep -E 'orm storage ready|starting everasync'
kubectl -n ever-async-prod exec deploy/web -- everasync doctor --config /app/everasync.toml

doctor reports the storage backend and health-checks every plugin. Then:

• GET /healthz answers.
• The dashboard shows the charter and the per-conversation policies that were there before — this is the human-visible proof the copy worked.
• Connection count is what was budgeted: select count(*) from pg_stat_activity where usename = 'ever_async'; → ~5, not ~16.
• Send one real message through the Slack ingress and confirm exactly one nudge, and that a redelivery of the same event is counted as a duplicate (ever_async_events_duplicate_total).
• pg itself is unbothered: total pg_stat_activity has grown by ~5, not by 50.

Rollback, step by step

After	Rollback	Cost
Step 2 (role + DB created)	Nothing to undo — leave the empty database in place	none
Step 4 (frozen, snapshot taken)	git revert the replicas: 0 commit	back on SQLite, unchanged
Step 5 (copy verified, not cut over)	Drop and recreate the target DB; revert the freeze	none — prod never used it
Step 6, within the window	Revert the ConfigMap commit (uncomment path, driver = "sqlite"), rollout restart	back on the untouched SQLite file; any rows written to Postgres in the window are lost
Step 6, after hours of live traffic	Same revert, but it discards everything written since cutover	a data-loss decision — the owner's call, not an operator's reflex
driver = "orm" misbehaves at runtime	Flip to driver = "postgres" + url_env, same database, restart	keeps all data; see the caveat below

The last rung is real but has one caveat worth knowing before relying on it: the ORM migrations create key and indexed columns as varchar(255) while the hand-written backend's own DDL uses text. Against ORM-created tables the hand-written backend's CREATE TABLE IF NOT EXISTS is a no-op, so it simply operates on the narrower columns. Every value that lands in one — Slack team, channel and message ids, composed message keys — is far short of 255 characters, so this is a theoretical limit rather than a practical one, but it is a difference and it should not be discovered during an incident.

Rollback window: the honest cutover is clean for as long as nothing important has been written to Postgres. State a window in the claim — "clean rollback until 18:00" — so the decision is made deliberately rather than at the moment it stops being cheap.

4. What becomes possible after — and what must be re-verified

Postgres removes the storage reason for one replica. It does not by itself make this app safe to run twice, and that distinction is the most important thing in this section.

What is unlocked

• replicas: 2 or more. The state store stops being a file that only one process can hold.
• strategy: RollingUpdate with maxSurge: 1 / maxUnavailable: 0, instead of Recreate. Recreate is only there because an RWO volume cannot be attached twice.
• The ~40s deploy gap disappears. No detach/attach, and a new pod becomes ready before the old one is removed, so an image roll is invisible.
• Real availability. With hostname anti-affinity, a node drain or eviction stops being an outage — which is the actual reason to do this, more than throughput.
• Golden rule 6 ("everything that can run multi-node runs on all nodes") stops being waived for this app.

🛑 Three things must be re-verified before the replica count goes above 1

a. Webhook dedup — correct by construction, but confirm the edge. claim_event is a single INSERT … ON CONFLICT (tenant, message_key) DO NOTHING whose affected-row count is the answer (storage-orm/src/lib.rs), so exactly one caller in the fleet is told true for a given key. tests/live_postgres.rs already races this against a real server, which is the concurrency proof the task rests on. The edge to confirm: in crates/core/src/pipeline.rs the claim is skipped for edits — if !event.is_edit && !claim_event(...) — so a redelivered edit is processed by every replica that receives it. Today an edit only leads to cancel_nudge, which is idempotent, so the effect is benign. Confirm that is still true rather than assuming it, and re-confirm whenever edit handling grows.

b. The nudge scheduler is in-process. ✅ Fixed — this is no longer the blocker.

NudgeScheduler (crates/core/src/scheduler.rs) is still a DashMap of tokio timers, per process, and it will stay that way: timers cannot elect anything across processes. What was missing was a claim on delivery, and it now exists.

Storage::claim_pending_nudge(tenant, message_key) -> Result<bool> is implemented in all four backends as a single atomic statement — DELETE FROM pending_nudges WHERE tenant = ? AND message_key = ?, with RETURNING in storage-postgres and the same delete's rows_affected in storage-orm (which also serves MySQL, where DELETE … RETURNING does not exist). pending_nudges is now the same kind of gate seen_events already was. Both original failures are closed:

• Restore-on-boot fans out — and that is now the design. Every replica restores every pending nudge and arms its own timer; all N fire; exactly one wins the claim and delivers. It also makes the restore robust, since no nudge is tied to a replica that may not come back. The restore is wired up: Pipeline::start_background is called from ever_async_server::run_with_sso, which run delegates to, so every everasync serve restores the queue exactly once. (It previously had zero call sites, so every deploy silently dropped every pending nudge — that is fixed, and crates/server/src/lib.rs has a test that starts the real run and watches it act on a row already in storage.)
• Overdue nudges are bounded. A nudge whose grace period ended while the pod was down is re-armed only if it is overdue by ≤ [nudges] max_overdue_secs (default 900s). Staler ones are claimed and dropped, counted in ever_async_nudges_stale_dropped_total. This matters most exactly here: the first boot after a long Postgres cutover or a failed rollout is the moment a naive restore would dump hours of backlog into a channel at once.
• Cancellation now travels. cancel_nudge still only aborts this process's task, but it deletes the row — so a timer surviving on another replica finds nothing to claim and stays silent. This was the nastier of the two, because a button press or /async off leaves no author follow-up for the self-correction re-check to find.

Two design points worth knowing before relying on it:

• Order. The self-correction check runs first, the claim second. The claim is irreversible — it deletes the row and nothing re-creates it — so it is taken as late as possible, immediately before the send; and self-correction stays a decision about the message rather than about who won a race. The drop path claims too, so the row is deleted once and ever_async_nudges_cancelled_total counts once fleet-wide.
• Delivery is at-most-once. A nudge whose send fails after the claim is gone, not retried. Deliberate: restoring the row would turn a delivery that timed out but actually landed into a second nudge hours later.

Evidence, not assertion. crates/core/src/pipeline.rs has two Pipeline instances over one shared Storage — two "replicas" — both arming the same nudge, asserting the channel received exactly one ephemeral; with the claim removed it receives two. tests/live_postgres.rs in both storage crates races 16–32 real connections at claim_pending_nudge behind a barrier and asserts one winner; against the naive read-then-write on a real Postgres, all 16 racers won. tests/postgres_parity.rs asserts the two backends share the one gate, so a rolling restart with a pod on each cannot double-nudge.

Still watch on stage: ever_async_nudges_claim_lost_total should be ~0 at one replica and grow with replicas - 1 per nudge above that. Watch ever_async_nudges_stale_dropped_total too — 0 through a rolling restart; anything else means the pods were down longer than [nudges] max_overdue_secs and says how many nudges that cost.

c. The digest loop is per-process. spawn_digest_loop (crates/core/src/pipeline.rs) starts a tokio::time::interval in every process, so N replicas deliver N digests per interval. It also delivers only DEFAULT_TENANT's counters, which the code documents.

⚠️ This loop now actually runs — start_background is called from the serve path, and it starts the digest whenever [digest] enabled = true. Before that wiring it was dead code, so "we have not enabled it" and "it cannot fire" were the same sentence; they are two different sentences now. enabled defaults to false, and a core test asserts the restore does not switch it on.

This is not a blocker today — [digest] is commented out in the prod ConfigMap, so nothing is being delivered. But it becomes one the moment the digest is enabled with more than one replica. Either keep [digest] enabled false while replicas > 1, or give the loop the same treatment as the nudge: a conditional update on a digest_last_sent_ms key in the existing settings table, so exactly one replica per interval wins. Write down which of the two was chosen.

The order

1. Postgres cutover (this document). Replicas stay at 1.
2. Ship claim_pending_nudge + its race test ✅ shipped — see §4b above. Wire start_background into the serve path ✅ shipped with a staleness cutoff ([nudges] max_overdue_secs, default 900s). Still verify both on stage with 2 replicas: restart both, confirm the timers fan out and the author gets exactly one nudge, and confirm ever_async_nudges_claim_lost_total moved on the losing pod. Then stop both pods for longer than the cutoff, start them, and confirm no ancient nudge is delivered while ever_async_nudges_stale_dropped_total moves once for the fleet, not once per pod. The unit and live tests prove the gates; only stage proves the deployment.
3. Decide the digest question (leader-elect it, or keep it disabled while scaled out). 🛑 It is commented out in the prod ConfigMap today, so the default answer is "leave it disabled" — but write the decision down.
4. Sessions: local and oauth keep their session store in process memory. Either stay on the token provider, or accept that a sign-in only works against the pod that issued it.
5. Only then: replicas: 2, strategy: RollingUpdate, hostname anti-affinity, still off the ever-db-* nodes.

Steps 1 and 4 are separate changes on separate days. Bundling them means that if the first nudge duplication is reported, there is no way to tell whether it came from the storage swap or the replica count.

5. Do not start this until

Every line is a hard gate. Any single "no" stops the work.

1. The owner has chosen shared pg or a dedicated cluster, in writing, having read §1. This document recommends shared pg; it does not decide it.
2. MAINTENANCE.md is claimed and pushed, naming both ever-async-prod and databases (CNPG pg), and no other agent holds databases, ArgoCD, or Ceph.
3. The Velero PVB for ever-async-prod/data has been re-verified today — Completed, from today, ~370 KB — and the parent backups.velero.io object's real phase has been read and written into the claim, including if it is PartiallyFailed as it was on 2026-08-08.
4. The CNPG checks all pass: pg-nightly not suspended with a lastScheduleTime under 26 hours old; the newest Backup completed with a non-empty backupId and an empty error; pg_stat_archiver showing last_archived_time > last_failed_time. 🛑 Not read from Cluster.status.lastSuccessfulBackup, which is currently 34 days stale and wrong.
5. The copier exists, is merged, and its tests pass — including the one that proves the source file is byte-identical after a copy.
6. The ignored live suites have been run against Postgres 18, not only 17.
7. The whole plan has been rehearsed end to end on ever-async-dev and then ever-async-stage, including the rollback.
8. The secret delivery path is decided and working — ExternalSecret from OpenBao preferred — and EVERASYNC_DATABASE_URL has been verified inside the pod, not just inside the Secret.
9. pool_size is set to 5 in the committed config, and the current free-connection headroom on pg has been re-measured on the day (it was ~93 of 300 on 2026-08-08).
10. A window is agreed in which a few minutes of ever-async-prod downtime is acceptable, and a rollback deadline is written into the claim.
11. Nobody is mid-change on Ever Gauzy production. 🛑 pg backs api.gauzy.co; this work must not overlap a Gauzy release, migration or incident.
12. Replica count stays at 1 through this change. Scaling out is §4, on a different day. claim_pending_nudge has since shipped, so the nudge blocker is closed — that changes what §4 costs, not whether §4 is a separate change.

What this document could not verify

Stated plainly so nothing here reads as more settled than it is:

• No restore has ever been tested from the Velero backup of this PVC. Backups are proven to exist and complete; restorability is inferred, not demonstrated. The VACUUM INTO snapshot in Step 4 is the rollback this plan actually relies on, precisely for that reason.
• The one error and 229 warnings in daily-all-20260808060042 were not traced. The Ever Async volume completed; what else in that 13 279-item sweep did not is unknown and outside this task.
• Neither storage backend has been exercised against PostgreSQL 18. The live tests are written for postgres:17-alpine; the fleet runs 18.4. Step 3 closes this and it is cheap, but until it runs it is an open assumption.
• The row counts inside the production SQLite file are unknown. The database was not opened — there is no sqlite3 in the image and reading a live WAL from a second process was not worth the risk for a number the copier will print anyway.
• PgBouncer transaction pooling with sqlx prepared statements was not tested here. The recommendation to use the session endpoint is based on the pooler's committed config (poolMode: transaction, no max_prepared_statements) and the known interaction, not on an observed failure on this fleet.
• Whether a second CNPG cluster would fit comfortably on ever-db-w1/w2/w3 was estimated, not measured — node CPU and memory capacity were read, but disk headroom and I/O saturation on the local-path volumes were not.

Related

• Self-hosting → Scaling later — the same two blockers, from the self-hosted angle.
• Multi-tenancy → Picking a database — why sqlite, postgres and orm are one config line apart.
• Operations — /metrics, the digest, and everasync doctor.
• apps/ever-async-prod/README.md in ever-co/k8s-gitops — the deployment shape, the subPath gotcha, and the secret rotation procedure.