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

43 lines
2.0 KiB
JavaScript

// Hanzo SQL — pg-wire endpoint over embedded Postgres (pglite), one tenant per process.
//
// This is the OPTION-A follow-up path (see ../LLM.md): a real `postgres://`
// endpoint for a single tenant whose data lives in one embedded Postgres data
// directory on a PVC. pglite is Postgres compiled to WASM — FULL pg dialect
// (enums, arrays, gen_random_uuid, citext, timestamptz, bytea), so an unmodified
// Prisma `provider = "postgresql"` schema runs against it with no rewrite. It is
// the endpoint for consumers that cannot adopt the file:SQLite path (schemas we
// cannot rewrite, or external ManagedDatabase customers who demand pg-wire).
//
// One process serves ONE tenant DB. Many tenants = many pods, each its own CR +
// PVC + Service (the operator's per-CR StatefulSet/Service machinery — no bespoke
// router). Fronting many tenant files from one process is a later optimization
// (routing on the pg-wire startup `database` param to a per-tenant handler); it
// is intentionally NOT built here.
//
// pglite is single-connection/single-writer by construction; that matches the
// per-tenant embedded model. It is not a replacement for a shared multi-writer
// cluster.
import { PGlite } from '@electric-sql/pglite'
import { PGLiteSocketServer } from '@electric-sql/pglite-socket'
import { mkdirSync } from 'node:fs'
const dataDir = process.env.HANZO_SQL_DATA || '/data/db'
const host = process.env.HANZO_SQL_HOST || '0.0.0.0'
const port = Number(process.env.HANZO_SQL_PORT || 5432)
mkdirSync(dataDir, { recursive: true }) // pglite mkdirs only the leaf, not parents
const db = await PGlite.create(dataDir)
await db.waitReady
const server = new PGLiteSocketServer({ db, host, port })
await server.start()
console.log(`[hanzo-sql] pg-wire on ${host}:${port} data=${dataDir}`)
const shutdown = async (sig) => {
console.log(`[hanzo-sql] ${sig}: draining`)
try { await server.stop() } finally { await db.close() }
process.exit(0)
}
process.on('SIGTERM', () => shutdown('SIGTERM'))
process.on('SIGINT', () => shutdown('SIGINT'))