Skip to content

PostgreSQL — Overview

OWNER: boris, yoanna ALSO_USED_BY: urszula, maxim, piotr, ashley, floris LAST_VERIFIED: 2026-03-26 GE_STACK_VERSION: PostgreSQL 16.x

Overview

PostgreSQL is the ONLY database permitted in GE projects. Every client project, every internal system, every data store uses PostgreSQL. No MySQL. No MongoDB. No SQLite. No DynamoDB.

PostgreSQL is the single source of truth (SSOT) for all application state. The filesystem is an optional audit trail only.

Why PostgreSQL

CHECK: Agent is selecting a database. IF: The project needs a database (which is always). THEN: Use PostgreSQL. No discussion.

Decision rationale (founding decision, never revisited):

Criterion PostgreSQL Alternatives
ACID compliance Full Varies
JSON support JSONB (indexed, queryable) MongoDB (no ACID)
Row-level security Native RLS Application-level only
Full-text search Built-in tsvector/tsquery Requires Elasticsearch
Extensions PostGIS, pgcrypto, pg_stat_statements Limited
EU hosting UpCloud Frankfurt Varies
ISO 27001 / SOC 2 Mature audit capabilities Varies
Drizzle ORM support First-class Varies

GE Version Pinning

PostgreSQL 16.x (UpCloud managed)

CHECK: Agent is provisioning a database. IF: PostgreSQL version differs from 16.x. THEN: Use 16.x unless Joshua (Innovation) has approved an upgrade.

Configuration Standards

Connection

All GE services connect via DATABASE_URL environment variable:

postgresql://{user}:{password}@{host}:{port}/{database}?sslmode=require

CHECK: Agent is configuring database access. IF: Connection string is hardcoded in source code. THEN: STOP. Move to environment variable DATABASE_URL. IF: SSL mode is not require. THEN: STOP. All GE connections use SSL. No exceptions.

Server Parameters (GE defaults)

# Connection limits
max_connections = 100
# Memory
shared_buffers = 256MB          # 25% of available RAM
effective_cache_size = 768MB    # 75% of available RAM
work_mem = 4MB                  # Per-operation sort/hash memory
maintenance_work_mem = 64MB     # For VACUUM, CREATE INDEX

# WAL
wal_level = replica             # For point-in-time recovery
max_wal_size = 1GB

# Locale
timezone = 'UTC'                # Always UTC at database level
lc_messages = 'en_US.UTF-8'

# Logging
log_min_duration_statement = 200  # Log queries slower than 200ms
log_statement = 'ddl'             # Log all DDL statements

CHECK: Agent is modifying PostgreSQL configuration. IF: Changing max_connections above 200. THEN: STOP. Use PgBouncer connection pooling instead. READ_ALSO: wiki/docs/stack/postgresql/performance.md

Database Layout (Admin-UI)

The GE admin-ui database contains these schema domains:

Domain Tables Description
auth credentials, sessions WebAuthn authentication
agents agents, learnings Agent identities and knowledge
tasks tasks Task execution tracking
clients clients Client organizations
projects projects Project tracking
teams team_capacity Multi-team scaling
billing wallets, budgets, consumption Commercialization
financial invoices, line_items Invoicing
knowledge session_learnings, knowledge_patterns Learning pipeline
collaboration discussions, votes Multi-agent consensus
workpackages work_packages, work_package_deps DAG execution
communication messages, chat_sessions Agent chat
metrics agent_metrics Observability
hitl questions, escalations Human-in-the-loop
system audit_log, billing_config System configuration

Client Project Databases

Each client project gets its own PostgreSQL database. Multi-tenant isolation strategy depends on the project: READ_ALSO: wiki/docs/stack/postgresql/multi-tenant.md

# Naming convention
{client_short_name}_{project_name}
# Examples
acme_webshop
brightside_portal

CHECK: Agent is creating a database for a client project. IF: Database name uses camelCase or spaces. THEN: Use snake_case with underscores.

Backup and Recovery

  • Automated backups: UpCloud managed, daily, 7-day retention.
  • Point-in-time recovery: WAL archiving enabled.
  • Manual backup: 
    pg_dump -Fc -f backup.dump $DATABASE_URL
  • Restore: 
    pg_restore -d $DATABASE_URL backup.dump

Extensions (GE approved)

Extension Purpose Approved
pgcrypto UUID generation, encryption Yes
pg_stat_statements Query performance tracking Yes
pg_trgm Trigram text search Yes
uuid-ossp UUID functions Yes
btree_gin GIN index for scalars Case-by-case
PostGIS Geospatial Case-by-case

CHECK: Agent needs a PostgreSQL extension. IF: Extension is in the approved list above. THEN: Add CREATE EXTENSION IF NOT EXISTS {name}; in a migration. IF: Extension is not approved. THEN: Raise a discussion. Boris and Piotr must approve.

GE-Specific Conventions

  1. UTC everywhere — database timezone is UTC. Application handles display tz.
  2. All timestamps WITH TIME ZONE — no timestamp without time zone.
  3. UUIDs for entity IDs — except agent IDs (human-readable text).
  4. snake_case for all SQL objects — tables, columns, indexes, functions.
  5. SSL required — all connections use sslmode=require.
  6. pg_stat_statements enabled — for query performance monitoring.
  7. Audit logging — DDL changes logged, append-only audit_log table.

Cross-References

READ_ALSO: wiki/docs/stack/postgresql/indexing.md READ_ALSO: wiki/docs/stack/postgresql/multi-tenant.md READ_ALSO: wiki/docs/stack/postgresql/performance.md READ_ALSO: wiki/docs/stack/postgresql/security.md READ_ALSO: wiki/docs/stack/postgresql/pitfalls.md READ_ALSO: wiki/docs/stack/postgresql/checklist.md READ_ALSO: wiki/docs/stack/drizzle/index.md READ_ALSO: wiki/docs/development/standards/naming.md