hip-0029

HIP-29: Relational Database Standard. Status Active. Hanzo's own standard — read this before implementing against it.

HIP-0029: Relational Database Standard

Abstract

This proposal defines the relational database standard for all Hanzo services. Hanzo SQL provides relational data storage via SQL, deployed as in-cluster StatefulSets on each Kubernetes Kubernetes cluster. Every Hanzo service that requires persistent relational storage MUST connect to the cluster-local SQL instance following this specification.

Repository: github.com/hanzoai/postgres Image: ghcr.io/hanzoai/sql:latest

Specification

npx prisma migrate dev # Development npx prisma migrate deploy # Production ```

Beego ORM auto-sync (IAM only, legacy)

Controlled by autoMigrate in conf/app.conf
MUST be false in production after initial setup

Raw SQL migrations (Go services, shell scripts)

Stored in migrations/ directory
Numbered sequentially: 001_initial.sql, 002_add_vectors.sql
Applied via psql or a lightweight migration tool

All migrations MUST be:

Idempotent: Safe to run multiple times (IF NOT EXISTS, IF NOT EXISTS)
Forward-only: No down migrations in production
Reviewed: Schema changes require PR review from infrastructure team
Tested: Run against a test database before production

Database Naming Convention

<service>        -- e.g., iam, cloud, console, kms, platform, commerce
<service>_<env>  -- only if running multiple environments in one cluster

Database names MUST be lowercase, use underscores (not hyphens), and match the service name exactly. The exception is hanzo_cloud which is a legacy name retained for backward compatibility.

User and Role Standard

Each database MUST have a dedicated user:

-- Per-database user (created by KMS bootstrap)
CREATE USER iam_user WITH PASSWORD '<from-kms>';
GRANT ALL PRIVILEGES ON DATABASE iam TO iam_user;

CREATE USER cloud_user WITH PASSWORD '<from-kms>';
GRANT ALL PRIVILEGES ON DATABASE cloud TO cloud_user;

A superuser hanzo exists for administrative operations (backups, extension installation, new database creation). Service applications MUST NOT use the superuser account.

Implementation

Container Image

The Hanzo SQL image is built from the official SQL 16 image with extensions pre-installed:

FROM postgres:16-bookworm

# Install pgvector
RUN apt-get update && \
    apt-get install -y postgresql-16-pgvector && \
    rm -rf /var/lib/apt/lists/*

# Install additional extensions
RUN apt-get update && \
    apt-get install -y postgresql-16-pg-trgm && \
    rm -rf /var/lib/apt/lists/*

# Custom entrypoint for extension initialization
COPY init-extensions.sh /docker-entrypoint-initdb.d/

# Custom postgresql.conf
COPY postgresql.conf /etc/postgresql/postgresql.conf

CMD ["postgres", "-c", "config_file=/etc/postgresql/postgresql.conf"]

Image: ghcr.io/hanzoai/sql:latest Base: postgres:16-bookworm Architectures: linux/amd64, linux/arm64

Kubernetes StatefulSet

apiVersion: apps/v1
kind: StatefulSet
metadata:
  name: postgres
  namespace: hanzo
spec:
  serviceName: postgres
  replicas: 1
  selector:
    matchLabels:
      app: postgres
  template:
    metadata:
      labels:
        app: postgres
    spec:
      containers:
        - name: postgres
          image: ghcr.io/hanzoai/sql:latest
          ports:
            - containerPort: 5432
          env:
            - name: POSTGRES_USER
              valueFrom:
                secretKeyRef:
                  name: postgres-credentials
                  key: username
            - name: POSTGRES_PASSWORD
              valueFrom:
                secretKeyRef:
                  name: postgres-credentials
                  key: password
          volumeMounts:
            - name: postgres-data
              mountPath: /var/lib/postgresql/data
          resources:
            requests:
              memory: "1Gi"
              cpu: "500m"
            limits:
              memory: "2Gi"
              cpu: "2000m"
          livenessProbe:
            exec:
              command: ["pg_isready", "-U", "hanzo"]
            initialDelaySeconds: 30
            periodSeconds: 10
          readinessProbe:
            exec:
              command: ["pg_isready", "-U", "hanzo"]
            initialDelaySeconds: 5
            periodSeconds: 5
  volumeClaimTemplates:
    - metadata:
        name: postgres-data
      spec:
        accessModes: ["ReadWriteOnce"]
        storageClassName: do-block-storage
        resources:
          requests:
            storage: 50Gi
---
apiVersion: v1
kind: Service
metadata:
  name: postgres
  namespace: hanzo
spec:
  selector:
    app: postgres
  ports:
    - port: 5432
      targetPort: 5432
  clusterIP: None  # Headless for StatefulSet

Backup Strategy

Backups run via a Kubernetes CronJob:

apiVersion: batch/v1
kind: CronJob
metadata:
  name: postgres-backup
  namespace: hanzo
spec:
  schedule: "0 */6 * * *"  # Every 6 hours
  jobTemplate:
    spec:
      template:
        spec:
          containers:
            - name: backup
              image: ghcr.io/hanzoai/sql:latest
              command:
                - /bin/sh
                - -c
                - |
                  TIMESTAMP=$(date +%Y%m%d_%H%M%S)
                  BACKUP_DIR=/backups/${TIMESTAMP}
                  mkdir -p ${BACKUP_DIR}

                  # Dump each database separately
                  for db in iam cloud console hanzo_cloud kms platform; do
                    pg_dump -h localhost -U hanzo \
                      -Fc --no-owner --no-acl \
                      ${db} > ${BACKUP_DIR}/${db}.dump
                  done

                  # Upload to S3-compatible storage
                  aws s3 sync ${BACKUP_DIR} \
                    s3://hanzo-backups/postgres/${TIMESTAMP}/ \
                    --endpoint-url ${S3_ENDPOINT}

                  # Clean up local
                  rm -rf ${BACKUP_DIR}

                  # Prune backups older than 30 days
                  aws s3 ls s3://hanzo-backups/postgres/ \
                    --endpoint-url ${S3_ENDPOINT} | \
                    awk '{print $2}' | \
                    while read dir; do
                      dir_date=$(echo $dir | tr -d '/')
                      if [ $(date -d "$dir_date" +%s 2>/dev/null || echo 0) -lt \
                           $(date -d '30 days ago' +%s) ]; then
                        aws s3 rm --recursive \
                          s3://hanzo-backups/postgres/${dir} \
                          --endpoint-url ${S3_ENDPOINT}
                      fi
                    done
              envFrom:
                - secretRef:
                    name: postgres-credentials
                - secretRef:
                    name: s3-backup-credentials
          restartPolicy: OnFailure

Backup schedule: Every 6 hours Retention: 30 days Storage: S3-compatible (MinIO in-cluster or DO Spaces) Format: pg_dump custom format (-Fc) for selective restore

Restore Procedure

# Download backup
aws s3 cp s3://hanzo-backups/postgres/20260215_060000/iam.dump ./iam.dump \
  --endpoint-url ${S3_ENDPOINT}

# Restore to database
pg_restore -h localhost -U hanzo \
  -d iam --clean --if-exists --no-owner \
  ./iam.dump

Monitoring

PostgreSQL metrics are exposed via postgres_exporter sidecar to Prometheus:

| Metric | Alert Threshold | Description | |--------|----------------|-------------| | pg_stat_activity_count | > 150 | Active connection count | | pg_database_size_bytes | > 40GB | Database size | | pg_stat_bgwriter_buffers_backend | Increasing | Shared buffer pressure | | pg_replication_lag_seconds | > 60s | Replication lag (when enabled) | | pg_up | 0 | SQL is down |

Security

Network Isolation

apiVersion: networking.k8s.io/v1
kind: NetworkPolicy
metadata:
  name: postgres-network-policy
  namespace: hanzo
spec:
  podSelector:
    matchLabels:
      app: postgres
  policyTypes:
    - Ingress
  ingress:
    - from:
        - namespaceSelector:
            matchLabels:
              name: hanzo
      ports:
        - protocol: TCP
          port: 5432

Only pods in the hanzo namespace can connect. All other traffic is denied.

pg_hba.conf

# TYPE  DATABASE  USER       ADDRESS        METHOD
local   all       all                       trust
host    all       all        127.0.0.1/8     md5
host    all       all        0.0.0.0/0      reject

Local socket connections (within the pod): trusted
Pod network (127.0.0.1/8): password authentication
Everything else: rejected

Credential Management

All database passwords are managed by Hanzo KMS (kms.hanzo.ai):

KMS stores DATABASE_URL for each service
KMSSecret CRDs sync secrets into Kubernetes
Pods mount secrets as environment variables
Password rotation: update in KMS, restart affected pods

# KMSSecret resource for IAM database credentials
apiVersion: secrets.lux.network/v1alpha1
kind: KMSSecret
metadata:
  name: iam-database
  namespace: hanzo
spec:
  hostAPI: https://kms.hanzo.ai/api
  authentication:
    universalAuth:
      secretsScope:
        envSlug: prod
        secretsPath: /iam
      credentialsRef:
        secretName: kms-universal-auth
        secretNamespace: hanzo
  managedSecretReference:
    secretName: iam-database-url
    secretNamespace: hanzo
    secretType: Opaque

Encryption

In transit: Not required within cluster (pod-to-pod traffic is encrypted

at the CNI level on Kubernetes). If cross-cluster replication is ever added, TLS MUST be enabled on the replication connection.

At rest: DigitalOcean block storage volumes are encrypted at the

infrastructure level. PVC data inherits this encryption.

Compatibility

IAM Dual-Engine Support

Hanzo IAM supports both MySQL and SQL. For local development convenience, MySQL is available:

# Local dev with MySQL
docker compose -f compose.mysql.yml up -d
cp conf/app.mysql.conf conf/app.conf

# Staging/Production MUST use SQL
cp conf/app.dev.conf conf/app.conf  # SQL config

The driverName in conf/app.conf determines the engine:

driverName = postgres   # Production (REQUIRED)
driverName = mysql      # Local development only

Connection Pooling

For services with high connection churn (API gateways, serverless functions), connection pooling via PgBouncer MAY be deployed as a sidecar:

# PgBouncer sidecar (optional, per-service)
- name: pgbouncer
  image: bitnami/pgbouncer:latest
  env:
    - name: POSTGRESQL_HOST
      value: localhost
    - name: PGBOUNCER_POOL_MODE
      value: transaction
    - name: PGBOUNCER_MAX_CLIENT_CONN
      value: "200"
    - name: PGBOUNCER_DEFAULT_POOL_SIZE
      value: "20"

This is optional. Most services maintain persistent connections and do not require pooling.

Future Work

Phase 3: Logical Replication for Cross-Cluster Sync

If the cluster and lux-k8s need shared data (e.g., unified user directory):

the cluster postgres → Logical Replication → lux-k8s postgres
    (publisher)                              (subscriber)