DOMAIN:DATABASE¶

OWNER_PRIMARY: boris (DBA Alfa)
OWNER_SECONDARY: yoanna (DBA Bravo)
UPDATED: 2026-03-24
SCOPE: all client projects, all teams, internal GE infrastructure

DATABASE:OVERVIEW¶

PostgreSQL is GE's ONLY persistent data store. Redis is cache/queue only — never SSOT.
Every client project uses PostgreSQL. No exceptions. No MongoDB. No DynamoDB. No Firebase.
Drizzle ORM is the application-layer interface. Raw SQL only when Drizzle cannot express the query.

OWNERSHIP¶

BORIS owns: schema design, migration review, performance tuning, compliance evidence generation, backup verification, index strategy, query optimization. Alfa team DBA.
YOANNA owns: same responsibilities for Bravo team. Independent review authority.
URSZULA uses: Drizzle ORM schema definitions, migration authoring, application queries.
MAXIM uses: Drizzle ORM schema definitions, migration authoring, application queries.
ARJAN uses: PostgreSQL deployment, networking, connection pooling infrastructure, backup infrastructure.
RUTGER uses: production database operations, monitoring, incident response.

DECISION AUTHORITY¶

Boris and Yoanna have VETO on any migration that touches production.
Schema changes require DBA review before merge — no exceptions.
Index additions in production require DBA sign-off (wrong index = write performance regression).
Any ALTER TABLE on a table with >1M rows requires a migration plan reviewed by DBA.

DATABASE:THREE_ZONE_ARCHITECTURE¶

GE operates three database zones. Code flows Zone 1 → Zone 2 → Zone 3. Data never flows backward.

ZONE_1:DEVELOPMENT¶

PROVIDER: local PostgreSQL (Docker or k3s pod)
HOST: localhost or k3s service
PORT: 5432 (standard)
PURPOSE: developer iteration, schema prototyping, migration testing
BACKUP: none — ephemeral, rebuild from migrations
DATA: synthetic seed data only — NEVER production data
CONNECTION: direct, no pooler
SSL: not required
NOTE: every developer gets their own database instance — no shared dev DB
NOTE: Drizzle Kit drizzle-kit push allowed here for rapid prototyping
NOTE: drizzle-kit generate for migration files once schema is stable

ZONE_2:STAGING¶

PROVIDER: UpCloud Managed PostgreSQL (smallest tier)
HOST: per-project staging hostname from UpCloud
PORT: assigned by UpCloud (usually 25060)
PURPOSE: migration testing against real managed DB, integration testing, pre-production verification
BACKUP: UpCloud automatic daily (7-day retention)
DATA: anonymized subset of production OR synthetic data — NEVER raw production PII
CONNECTION: PgBouncer (transaction mode) in front
SSL: required — sslmode=require minimum
NOTE: staging DB must match production PostgreSQL major version exactly
NOTE: run every migration on staging first — production deploy only after staging success
NOTE: load testing runs here, not in production
ALERT: if staging migration takes >30 seconds, it WILL be worse in production — rethink approach

ZONE_3:PRODUCTION¶

PROVIDER: UpCloud Managed PostgreSQL
HOST: per-project production hostname from UpCloud
PORT: assigned by UpCloud (usually 25060)
PURPOSE: live client data, SSOT for all application state
BACKUP: UpCloud automatic daily + GE-managed WAL archiving for point-in-time recovery
DATA: real client data — PII, financial, sensitive
CONNECTION: PgBouncer (transaction mode) mandatory — direct connections forbidden
SSL: required — sslmode=verify-full with CA certificate
NOTE: no DDL statements outside of reviewed migration scripts
NOTE: no manual data manipulation — all changes through application or reviewed SQL scripts
NOTE: connection limit is hard — UpCloud managed DB has fixed max_connections
NOTE: Boris/Yoanna must approve production migration execution
ALERT: production incidents escalate to Rutger (ops) + Boris/Yoanna (DBA)

DATABASE:TECHNOLOGY_STACK¶

POSTGRESQL¶

VERSION: 16.x (current target — verify per project)
WHY: ACID compliance, JSONB for flexible schemas, excellent extension ecosystem, mature tooling
EXTENSIONS_APPROVED: pgcrypto, pg_trgm, uuid-ossp, citext, pg_stat_statements
EXTENSIONS_REQUIRE_APPROVAL: PostGIS, pgvector, pg_partman, timescaledb
EXTENSIONS_FORBIDDEN: anything not in approved/require-approval lists — discuss first

DRIZZLE_ORM¶

VERSION: latest stable (pin in package.json)
WHY: TypeScript-native, SQL-like syntax (no magic), excellent PostgreSQL support, migration generation
CONFIG: drizzle.config.ts at project root
SCHEMA: src/db/schema/ directory — one file per logical domain
MIGRATIONS: drizzle/migrations/ directory — generated by drizzle-kit generate
NOTE: Drizzle is a thin layer — learn PostgreSQL first, Drizzle syntax second
NOTE: when Drizzle cannot express a query, use sql template literal — do not fight the ORM

PGBOUNCER¶

MODE: transaction (NOT session — wastes connections)
PURPOSE: connection multiplexing — 100 app connections share 20 DB connections
CONFIG: managed by Arjan (infrastructure) — DBA advises on pool sizing
NOTE: prepared statements do not work in transaction mode — Drizzle handles this correctly
NOTE: SET statements do not persist across queries in transaction mode

DATABASE:DOMAIN_PAGES¶

Page	Purpose
schema-design	Naming conventions, data types, constraints, indexes, PK strategy
migrations	Zero-downtime migrations, Drizzle workflow, dangerous operations
multi-tenant	Tenant isolation strategies, RLS deep dive, decision tree
performance	EXPLAIN ANALYZE, query optimization, vacuum, partitioning
compliance-evidence	ISO 27001 / SOC 2 evidence generation from DB ops
pitfalls	Anti-patterns, LLM-specific mistakes, ORM traps
thought-leaders	Community resources, key people, essential reading

DATABASE:QUICK_REFERENCE¶

CONNECT_TO_STAGING¶

psql "postgresql://user:password@staging-host:25060/dbname?sslmode=require"

CONNECT_TO_PRODUCTION¶

# NEVER connect directly — use PgBouncer endpoint
psql "postgresql://user:password@pgbouncer-host:6432/dbname?sslmode=verify-full&sslrootcert=ca.pem"

CHECK_ACTIVE_CONNECTIONS¶

SELECT count(*), state, usename
FROM pg_stat_activity
WHERE datname = current_database()
GROUP BY state, usename
ORDER BY count DESC;

CHECK_TABLE_SIZES¶

SELECT relname, pg_size_pretty(pg_total_relation_size(relid))
FROM pg_catalog.pg_statio_user_tables
ORDER BY pg_total_relation_size(relid) DESC
LIMIT 20;

CHECK_INDEX_USAGE¶

SELECT schemaname, relname, indexrelname, idx_scan, idx_tup_read, idx_tup_fetch
FROM pg_stat_user_indexes
ORDER BY idx_scan ASC
LIMIT 20;

CHECK_SLOW_QUERIES¶

-- Requires pg_stat_statements extension
SELECT query, calls, mean_exec_time, total_exec_time
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 20;

DATABASE:ESCALATION¶

ISSUE: migration failure in staging → Boris/Yoanna investigate, fix, re-run
ISSUE: migration failure in production → STOP. Do not retry. Boris/Yoanna + Rutger investigate.
ISSUE: connection pool exhaustion → Rutger (immediate ops) + Boris/Yoanna (root cause)
ISSUE: slow query impacting users → Rutger (immediate mitigation) + Boris/Yoanna (optimization)
ISSUE: data corruption suspected → STOP ALL WRITES. Rutger + Boris/Yoanna + Arjan. Incident protocol.
ISSUE: backup verification failure → Boris/Yoanna investigate within 24 hours — compliance requirement.