mirror of
https://github.com/hanzoai/sql.git
synced 2026-11-15 19:38:30 +00:00
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]>
77 lines
4.2 KiB
Plaintext
77 lines
4.2 KiB
Plaintext
### Hanzo SQL — pg->SQLite ETL end-to-end validation (synthetic data, no prod)
|
|
### source: real Hanzo Postgres 18 (ghcr.io/hanzoai/sql:18), pre-conversion esign schema, 4554 seeded rows
|
|
### target: fresh SQLite built from esign packages/prisma/migrations/0_init/migration.sql
|
|
### script: esign/scripts/backfill-pg-to-sqlite.ts + enum-array-parser.patch
|
|
|
|
===== BACKFILL RUN =====
|
|
[backfill] registered enum-array parsers for 29 OID(s)
|
|
[backfill] User: 1000 rows
|
|
[backfill] User: 2000 rows
|
|
[backfill] User: 3000 rows
|
|
[backfill] User: 3002 rows
|
|
[backfill] User: 3002 rows ✓
|
|
[backfill] UserSecurityAuditLog: 1000 rows
|
|
[backfill] UserSecurityAuditLog: 1000 rows ✓
|
|
[backfill] Passkey: 501 rows
|
|
[backfill] Passkey: 501 rows ✓
|
|
[backfill] SiteSettings: 51 rows
|
|
[backfill] SiteSettings: 51 rows ✓
|
|
[backfill] done — 4554 rows copied into /private/tmp/claude-501/-Users-z-work-hanzo-hanzo-ai/e413594f-4028-4b99-be20-41bf17b66c0a/scratchpad/sqltest/sign.db
|
|
|
|
===== PARITY + TYPE-EXACT ANCHORS =====
|
|
== ROW-COUNT PARITY (pg vs sqlite) ==
|
|
User pg= 3002 sqlite= 3002 OK
|
|
Passkey pg= 501 sqlite= 501 OK
|
|
UserSecurityAuditLog pg= 1000 sqlite= 1000 OK
|
|
SiteSettings pg= 51 sqlite= 51 OK
|
|
parity: ALL OK
|
|
|
|
== ANCHOR: User [email protected] (enum-array, datetime->epoch-ms, bool, scalar enum) ==
|
|
{"t_roles":"text","roles":"[\"ADMIN\",\"USER\"]","t_ev":"integer","emailVerified":1783168496789,"disabled":1,"twoFactorEnabled":0,"identityProvider":"GOOGLE"}
|
|
expect roles=TEXT ["ADMIN","USER"] emailVerified=INTEGER 1783168496789
|
|
|
|
== ANCHOR: User anchor-null (null datetime) ==
|
|
{"roles":"[\"USER\"]","emailVerified":null,"t":"null"}
|
|
|
|
== ANCHOR: Passkey pk-anchor (bytea->BLOB, bigint, string-array, null) ==
|
|
file:///private/tmp/claude-501/-Users-z-work-hanzo-hanzo-ai/e413594f-4028-4b99-be20-41bf17b66c0a/scratchpad/sqltest/validate.mjs:25
|
|
const pk = s.prepare(`select typeof("credentialId") t_cid, hex("credentialId") cid, hex("credentialPublicKey") cpk, typeof(counter) t_ctr, counter, transports, "credentialBackedUp", "lastUsedAt" from "Passkey" where id='pk-anchor'`).get()
|
|
^
|
|
|
|
RangeError: Value is too large to be represented as a JavaScript number: 9007199254740993
|
|
at file:///private/tmp/claude-501/-Users-z-work-hanzo-hanzo-ai/e413594f-4028-4b99-be20-41bf17b66c0a/scratchpad/sqltest/validate.mjs:25:234
|
|
at process.processTicksAndRejections (node:internal/process/task_queues:104:5) {
|
|
code: 'ERR_OUT_OF_RANGE'
|
|
}
|
|
|
|
Node.js v26.4.0
|
|
== ANCHOR: Passkey pk-anchor (bytea->BLOB, bigint int64, string-array, null) ==
|
|
{"t_cid":"blob","cid":"DEADBEEF00","cpk":"0001020304","t_ctr":"integer","counter":"9007199254740993n","transports":"[\"usb\",\"nfc\",\"internal\"]","credentialBackedUp":"1n","lastUsedAt":null}
|
|
expect cid=DEADBEEF00 cpk=0001020304 counter=9007199254740993n transports=["usb","nfc","internal"] backedUp=1 lastUsedAt=null
|
|
|
|
== ANCHOR: SiteSettings ss-anchor (jsonb->TEXT verbatim, bool) ==
|
|
{"t":"text","data":"{\"a\":1,\"b\":[true,null,\"x\"],\"nested\":{\"k\":\"v\"}}","enabled":0}
|
|
|
|
== ENUM-ARRAY distribution (User.roles as JSON TEXT) ==
|
|
{"roles":"[\"USER\"]","n":2001}
|
|
{"roles":"[\"ADMIN\",\"USER\"]","n":1001}
|
|
|
|
== SCALAR ENUM distribution (UserSecurityAuditLog.type) ==
|
|
{"type":"PASSKEY_CREATED","n":200}
|
|
{"type":"PASSWORD_UPDATE","n":200}
|
|
{"type":"SIGN_IN","n":200}
|
|
{"type":"SIGN_IN_FAIL","n":200}
|
|
{"type":"SIGN_OUT","n":200}
|
|
|
|
== counter int64 fidelity: max/min counter ==
|
|
{"mx":"9007199254740993n","mn":"1000n"}
|
|
|
|
===== esign PRISMA CLIENT READ-BACK =====
|
|
esign Prisma client: User.count() = 3002
|
|
User anchor emailVerified = 2026-07-04T12:34:56.789Z (instanceof Date: true ) roles = ["ADMIN","USER"] identityProvider = GOOGLE disabled = true
|
|
User anchor-null emailVerified = null
|
|
Passkey anchor credentialId = deadbeef00 (is Uint8Array: true ) counter = 9007199254740993n (typeof bigint) transports = ["usb","nfc","internal"]
|
|
SiteSettings anchor data = {"a":1,"b":[true,null,"x"],"nested":{"k":"v"}} (typeof object) enabled = false
|
|
UserSecurityAuditLog.count() = 1000
|
|
ESIGN PRISMA READ-BACK = PASS
|