Migrate the shared economy DB from MariaDB to Postgres (off shared host)

Speculative / future. Captured so the context isn't lost if we pull the trigger.

Why

We're starting to hit the limits of the shared Bloom MariaDB hosting (one DB, one user, no tuning, connection-capped, no replicas, noisy neighbours). If we move off that host, it's the natural moment to move to Postgres rather than MariaDB→MariaDB: better performance, MVCC that suits our multi-writer ledger far better than InnoDB on a shared host, and nicer features. We already run Postgres in-cluster (CNPG; tesks uses it), so the infra target basically exists.

The one design decision that matters most: account_balances_mat

Tempting to "drop the balance triggers for a real materialized view" — but a vanilla Postgres MATERIALIZED VIEW is the wrong tool: it's refresh-on-demand (stale until REFRESH, full recompute), and balances are read on the hot path (every overdraft check). The real options:

1. Drop the materialization, compute on the fly — covering index on ledger_postings(account_id, amount) makes SUM(amount) WHERE account_id = ? an index-only scan; always correct, no triggers, no staleness. The clean win. Caveat: the whale SYSTEM accounts (ChestShop System #4 ≈ 1.26M postings, plus other faucets) make that SUM non-trivial — but PAR-158/159 (collapsing the two-hop SYSTEM-account bridge) shrink them, and worst case we snapshot just the whales.
2. pg_ivm (incremental view maintenance extension) — the "matview that stays live" you'd actually want, but it's an extension (custom CNPG image) and is trigger-like delta maintenance under the hood.
3. Port the trigger-maintained table to PL/pgSQL — least change, keeps the triggers.

Recommendation: #1 if the whale problem is tractable; fall back to a trigger-maintained table for the SYSTEM whales only. Prove this against a copy of prod data first — it's the single decision that determines whether this is a clean simplification or just a trigger port.

Measured surface (as of 2026-06)

Schema — economy-flyway, 23 migrations: mechanical: AUTO_INCREMENT×16 → identity, ENUM×15 → native enums/checks, TINYINT×19 → boolean, UNSIGNED×43 → drop, BINARY(16) throughout. Hard: the account_balances_mat triggers (AFTER INSERT/UPDATE on ledger_postings, ON DUPLICATE KEY) — ledger-balance integrity, must be bit-perfect. 5 GENERATED … VIRTUAL columns incl. two "virtual-column + unique-NULL" tricks (one-personal-per-player, one-current-job) → become first-class partial unique indexes (nicer, but a redesign that must preserve exact semantics).

Mappers — 4 MyBatis codebases, 243 annotation-SQL statements (~70 carry MySQL-isms): ON DUPLICATE KEY×16 → ON CONFLICT … DO UPDATE; uuid_to_bin×27; IF()×10 → CASE; HEX/UNHEX×7; useGeneratedKeys/LAST_INSERT_ID×9 → RETURNING; INTERVAL×35 (syntax differs). MyBatis itself is dialect-agnostic — it's the SQL strings.

Kysely read paths — economy-explorer, 17 DAL files: swap MysqlDialect+mysql2 → PostgresDialect+pg; fix ~13 DATE_FORMAT/TIMESTAMPDIFF/CONCAT_WS (money-flow + calendar analytics) + the UUID binds. Tailwind: the explorer is now read-only (ADT-14) — all upsert/write complexity is gone, so this is purely query-dialect + driver.

Key lever: native uuid vs bytea

• bytea ≈ drop-in for BINARY(16): minimal ripple, keep the byte↔UUID dance (now centralized in :common/UuidBin — ADT-22).
• native uuid: cleaner forever, but ripples through all 27 uuid_to_bin sites, ~15 type-handler files, the Kysely uuidToBin, and the explorer.

Also in scope (not always remembered)

• Test harnesses: 4 embedded-MariaDB setups (MariaDB4j in treasury/business/rest-api + EmbeddedMariaDb + the explorer's integration MariaDB) → Postgres equivalents (Testcontainers / embedded-postgres). The ADT-5 kysely-codegen drift gate just regenerates types off the new schema.
• Data migration + cutover: live economy data MariaDB→Postgres (binary UUIDs, the materialized balances), in a maintenance window, with zero tolerance for losing a balance. Its own careful one-shot.

Favourable factors

Flyway, Kysely, and MyBatis all support Postgres natively; the schema is a single source of truth (economy-flyway); Postgres already runs in-cluster (CNPG); DECIMAL(19,2) → numeric is a no-op (money math untouched); UUID conversion centralized in :common; explorer write paths already retired.

Rough sizing

~4–6 weeks focused solo, of which ~1–1.5 weeks is genuinely risky (the balance design, the UUID-type decision, the data cutover); the rest is broad-but-mechanical dialect find-replace (~70 mapper statements, 17 DAL files, 23 migrations) + test-harness ports.

Suggested first step if we go

De-risk the scary part before committing: spike option #1 (on-the-fly indexed balances) and the ledger_postings trigger rewrite against a copy of prod data, focusing on the whale accounts. That result decides the whole shape.