Files
zeekayandHanzo Dev 8083a487c9 feat(managed): Hanzo SQL — per-tenant SQLite product spec, deploy CRs, pg→SQLite ETL fix
Replaces the shared multi-tenant Postgres (ghcr.io/hanzoai/sql:18) with the
per-tenant, SQLite-backed, provision-on-demand model — the relational analog of
docdb (FerretDB-on-SQLite). Builds under sql/managed/, never touches the
Postgres source tree.

Crux decided by real prisma output (evidence/):
- Option A (pglite pg-wire): the UNMODIFIED dataroom pg schema (62 tables, 18
  enums, 11 array cols, @db.* native types) `prisma db push`es and runs a live
  enum+text[] CRUD roundtrip → the pg-wire escape hatch for un-rewritable
  schemas / external ManagedDatabase tenants.
- Option B/C (SQLite dialect): the same schema is rejected at Prisma validation
  (P1012, 11 scalar-list + 6 native-type errors) before any backend — no
  pg-wire-over-SQLite proxy can fix it. postlite rejected.
- Primary path stays file:SQLite (esign/Service.persistence): rewrite the schema
  (arrays→JSON String) + one-time data ETL. esign already runs this in prod.

Deliverables:
- LLM.md: product spec — existing operator model (Service.persistence, SQL/
  ManagedDatabase datastore Kinds), per-tenant layout, provisioning, DNS,
  DATABASE_URL handoff (KMSSecret→env, mirrors docdb MONGO_URI), image
  ghcr.io/hanzoai/sql-managed (semver only), blockers (DatastoreSpec fsGroup gap,
  multi-tenant router, inert credentialsSecret).
- deploy/sql.yaml (path A, SQL Kind, sql-managed image) +
  service-persistence.example.yaml (path C, esign pattern).
- etl/enum-array-parser.patch: fixes a real data-loss bug in esign's
  backfill-pg-to-sqlite.ts — node-pg returns enum arrays (Role[]) as the raw
  string {ADMIN,USER}, aborting the load (User.roles is set for every user).
  Registers node-pg's text-array parser for enum-array OIDs. Validated pg→SQLite
  on 4554 synthetic rows: full parity + byte-exact type fidelity + esign Prisma
  read-back (evidence/etl-pg-to-sqlite-validation.log).
- pgwire/: tested pglite pg-wire prototype (server.mjs, provision.mjs, Dockerfile
  FROM ghcr.io/hanzoai/nodejs:v24.18.0) — the Option-A follow-up.

Co-authored-by: Hanzo Dev <[email protected]>
2026-08-22 15:16:23 -07:00

2.5 KiB

Hanzo SQL — Postgres → SQLite ETL

The canonical, one-way ETL lives with the consuming app, not here (the app owns its schema and its array-column list). The reference implementation is:

/Users/z/work/hanzo/esign/scripts/backfill-pg-to-sqlite.ts

It is idempotent, resumable (keyset-paginated, INSERT OR IGNORE), loads under foreign_keys=OFF, and applies the exact Prisma-on-SQLite storage codec:

Postgres source SQLite target (Prisma 6 codec)
boolean INTEGER 0 / 1
timestamp/timestamptz INTEGER epoch-milliseconds (Date.getTime())
json / jsonb TEXT (verbatim JSON)
text[] / enum _e[] TEXT (JSON array)
bytea BLOB
numeric (Decimal) REAL (via NUMERIC affinity)
bigint INTEGER (int64, lossless)
enum label TEXT (verbatim)

enum-array-parser.patch (REQUIRED)

node-pg parses built-in text[] into a JS array, but a column typed as an array of a custom enum (Role[], WebhookTriggerEvents[]) has a dynamic OID node-pg has no parser for — it returns the raw literal {ADMIN,USER} as a string, which trips the script's fail-closed array guard and aborts the whole load. User.roles is populated for every user, so the unpatched script cannot migrate real sign data. The patch registers node-pg's own text-array parser for every enum-array OID (5 lines).

cd /Users/z/work/hanzo/esign
git apply /Users/z/work/hanzo/sql/managed/etl/enum-array-parser.patch

Validated end-to-end on synthetic data (real Postgres 18 source, esign schema, 4554 rows across all pitfall types → SQLite → esign Prisma client read-back with full row parity and byte-exact type fidelity). See ../evidence/etl-pg-to-sqlite-validation.log.

Run

PG_URL=postgres://user:pass@host:5432/sign \
SQLITE_PATH=/data/sign.db \
MIGRATION_SQL=packages/prisma/migrations/0_init/migration.sql \
node scripts/backfill-pg-to-sqlite.ts --fresh --batch=1000

Constraint: table + column names must match between the Postgres source and the SQLite target (only type-attributes may differ). This holds for a fork that converted its own schema pg→sqlite (esign: zero tables/columns/enums added/removed/renamed). It does not hold against a structurally-diverged upstream — confirm the source is the fork's own pre-conversion Postgres.