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

57 lines
2.2 KiB
YAML

# Hanzo SQL — managed pg-wire endpoint (OPTION-A follow-up path).
#
# Declarative provisioning via the existing `SQL` datastore Kind (operator
# facade over DatastoreSpec — same StatefulSet + ClusterIP Service + headless
# `<name>-hs` + per-replica PVC machinery that backs `docdb`). This reuse is the
# one-way: no new CRD. The only change from the legacy Postgres `crs/sql.yaml`
# is the image — swap `ghcr.io/hanzoai/sql:18` (the shared Postgres we are
# retiring) for the embedded-Postgres pg-wire endpoint.
#
# A tenant that needs an isolated `postgres://` = one such CR (or a
# `ManagedDatabase` CR, the paid isolated-tenant facade). Consumer DNS =
# `<name>.<ns>.svc.cluster.local:5432`. The DATABASE_URL is synced to the
# consumer via a KMSSecret CR → k8s Secret → env `DATABASE_URL` valueFrom
# secretKeyRef (same path docdb uses for MONGO_URI).
apiVersion: hanzo.ai/v1
kind: SQL
metadata:
name: hanzo-sql
namespace: hanzo
spec:
type: postgresql # pg-wire; endpoint speaks the Postgres protocol
image:
repository: ghcr.io/hanzoai/sql-managed
tag: "v0.1.0" # semver only — never :latest, never sha
pullPolicy: IfNotPresent
replicas: 1 # pglite is single-writer per data dir; do not scale >1
storage:
storageClassName: do-block-storage
size: 10Gi
retentionPolicy: Retain
ports:
- name: data
containerPort: 5432
protocol: TCP
env:
- name: HANZO_SQL_DATA
value: /data/db
# The operator creates the `data` PVC (volumeClaimTemplate) but does NOT
# auto-mount it — the CR must mount it explicitly.
volumeMounts:
- name: data
mountPath: /data
serviceAliases:
- postgres
imagePullSecrets:
- name: ghcr-secret
partOf: data
resources:
requests: { cpu: 50m, memory: 128Mi }
limits: { cpu: "1", memory: 1Gi }
# KNOWN GAP (blocker for this follow-up): DatastoreSpec has no `fsGroup`
# (only the `Service` Kind does). The sql-managed image runs nonroot (65532)
# and cannot write a root-owned DO-block PVC without an fsGroup. Fix = add
# `fsGroup` to DatastoreSpec in operator/src/crd.rs + set it to 65532 here.
# Until then, host the endpoint on the `Service` Kind (has fsGroup+persistence)
# or run the image as root.