12 KiB
hip, title, author, type, category, status, created, requires
| hip | title | author | type | category | status | created | requires |
|---|---|---|---|---|---|---|---|
| 0029 | Relational Database Standard | Hanzo AI Team | Standards Track | Infrastructure | Active | 2025-01-15 | HIP-0, HIP-14 |
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
2. **Beego ORM auto-sync** (IAM only, legacy)
- Controlled by `autoMigrate` in `conf/app.conf`
- MUST be `false` in production after initial setup
3. **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
-- e.g., iam, cloud, console, kms, platform, commerce _ -- 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:
```sql
-- 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_URLfor each service KMSSecretCRDs 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)
References
- HIP-0: Architecture
- HIP-14: Application Deployment Standard
- PostgreSQL 16 Documentation
- pgvector
- Infisical KMS
Copyright
Copyright and related rights waived via CC0.