.env.example

# SPDX-FileCopyrightText: 2025 Blackcat Informatics® Inc.
# SPDX-License-Identifier: MIT

# ----------------------------------------------------------------------------
# Core Data Environment Template
# Copy this file to `.env` and customize values for your environment.
# ----------------------------------------------------------------------------

# PostgreSQL runtime
POSTGRES_SUPERUSER=postgres
# Store credentials in ./secrets/*. The manage CLI reads POSTGRES_SUPERUSER_PASSWORD_FILE
# and falls back to POSTGRES_SUPERUSER_PASSWORD only when provided explicitly.
POSTGRES_SUPERUSER_PASSWORD=
POSTGRES_SUPERUSER_PASSWORD_FILE=./secrets/postgres_superuser_password
POSTGRES_DB=postgres
# PostgreSQL listens on 5433 by default so PgBouncer can own 5432
# This ensures pooled connections are the default path for clients
POSTGRES_PORT=5433
PG_VERSION=18

# Application databases and owners to create automatically.
# Format: db_name:db_owner:owner_password
DATABASES_TO_CREATE=app_main:app_user:change_me,analytics:analytics_user:change_me

# Docker image build metadata
# Set *_TAG values to the stack release you want to consume (default: latest).
# Helpers such as ci-verify/ci-up also respect CORE_DATA_STACK_TAG / CORE_DATA_STACK_REGISTRY:
# CORE_DATA_STACK_TAG=18.4-v1.0.11
# CORE_DATA_STACK_REGISTRY=ghcr.io/paudley/core_data
POSTGRES_IMAGE_NAME=ghcr.io/paudley/core_data/postgres
POSTGRES_IMAGE_TAG=latest
CORE_DATA_BUILD_IMAGE=0
AGE_VERSION=PG18/v1.7.0-rc0

# Published container images (override to point at a private registry if needed)
VALKEY_IMAGE=ghcr.io/paudley/core_data/valkey:latest
RABBITMQ_IMAGE=ghcr.io/paudley/core_data/rabbitmq:latest
PGBOUNCER_IMAGE=ghcr.io/paudley/core_data/pgbouncer:latest
MEMCACHED_IMAGE=ghcr.io/paudley/core_data/memcached:latest
NETWORK_PROBE_IMAGE=ghcr.io/paudley/core_data/network-probe:latest
NETWORK_GUARD_IMAGE=ghcr.io/paudley/core_data/network-guard:latest

# Runtime resource limits
POSTGRES_MEMORY_LIMIT=4g
POSTGRES_CPU_LIMIT=2
POSTGRES_SHM_SIZE=1g

# Optional host overrides for PGDATA/WAL/pgBackRest if you prefer bind mounts over named volumes.
# PG_DATA_DIR=./data/postgres_data
# PG_WAL_DIR=./data/postgres_wal
# CORE_DATA_PGBACKREST_REPO_DIR=./data/pgbackrest_repo

# Container paths for persistent mounts (kept in sync with docker-compose.yml volume_prep command)
POSTGRES_DATA_MOUNT_PATH=/var/lib/postgresql/data
POSTGRES_WAL_MOUNT_PATH=/var/lib/postgresql/wal
POSTGRES_BACKREST_MOUNT_PATH=/var/lib/pgbackrest

# Core PostgreSQL tuning (map directly into postgresql.conf)
POSTGRES_MAX_CONNECTIONS=200
POSTGRES_LISTEN_ADDRESSES=0.0.0.0
PG_SHARED_BUFFERS=1GB
PG_EFFECTIVE_CACHE_SIZE=3GB
PG_WORK_MEM=16MB
PG_MAINTENANCE_WORK_MEM=256MB
PG_RANDOM_PAGE_COST=1.1
PG_EFFECTIVE_IO_CONCURRENCY=200
PG_MAX_WAL_SIZE=2GB
PG_MIN_WAL_SIZE=1GB
PG_WAL_KEEP_SIZE=2GB
PG_MAX_WAL_SENDERS=10
PG_CHECKPOINT_COMPLETION_TARGET=0.9
PG_LOG_MIN_DURATION_STATEMENT=500

# =============================================================================
# PostgreSQL Transaction Pooling Optimizations
# =============================================================================

# Plan cache mode: auto (default), force_generic_plan, force_custom_plan
# 'auto' allows PostgreSQL to cache plans after 5 executions
# With transaction pooling, 'auto' works well as server connections persist
PG_PLAN_CACHE_MODE=auto

# JIT compilation - enabled by default, useful for complex queries
# Threshold controls when JIT kicks in (default 100000)
PG_JIT_ENABLED=on
PG_JIT_ABOVE_COST=100000

# Parallel query workers (useful even with pooling for complex queries)
PG_MAX_PARALLEL_WORKERS_PER_GATHER=4
PG_MAX_PARALLEL_WORKERS=8
PG_PARALLEL_TUPLE_COST=0.01
PG_PARALLEL_SETUP_COST=1000

# TCP keepalive - detect dead connections quickly (important for poolers)
PG_TCP_KEEPALIVES_IDLE=60
PG_TCP_KEEPALIVES_INTERVAL=10
PG_TCP_KEEPALIVES_COUNT=6

# Idle session timeout (0=disabled) - defense against leaked connections
# Complements PgBouncer's client_idle_timeout
PG_IDLE_SESSION_TIMEOUT=0

# Temp file limit per session (-1=unlimited, or value like 10GB)
PG_TEMP_FILE_LIMIT=-1

# TLS configuration (self-signed certificates generated if files absent)
POSTGRES_SSL_ENABLED=on
POSTGRES_SSL_CERT_FILE=/var/lib/postgresql/data/tls/server.crt
POSTGRES_SSL_KEY_FILE=/var/lib/postgresql/data/tls/server.key
POSTGRES_SSL_SELF_SIGNED_SUBJECT=/CN=core_data_postgres
POSTGRES_SSL_SELF_SIGNED_DAYS=730

# Networking
DOCKER_NETWORK_NAME=core_data_network
DOCKER_NETWORK_SUBNET=172.25.0.0/16
NETWORK_GUARD_CHECK_INTERVAL=30
BACKUPS_HOST_PATH=./backups
COMPOSE_PROFILES=valkey,pgbouncer,memcached,rabbitmq

# Container execution context - UIDs/GIDs must match the user baked into each pre-built image.
# WARNING: Mismatch between these values and the image's baked-in UID causes permission errors.
# If building locally with CORE_DATA_BUILD_IMAGE=1, you may need to adjust these values.
POSTGRES_UID=1000
POSTGRES_GID=1000
POSTGRES_RUNTIME_USER=postgres
POSTGRES_RUNTIME_GECOS=Core\ Data\ PostgreSQL\ Administrator
POSTGRES_RUNTIME_HOME=/home/postgres
# PgBouncer runs as the postgres user (UID 100) in the pgbouncer image
PGBOUNCER_UID=100
PGBOUNCER_GID=102
# Valkey uses UID 999 in the valkey image
VALKEY_UID=999
VALKEY_GID=1000
# RabbitMQ uses UID 100, GID 101 in the rabbitmq image
RABBITMQ_UID=100
RABBITMQ_GID=101
# Shared secrets group - all service containers are members of this group
# Secrets files are owned by this group with mode 640 (owner+group readable)
# Note: 65533 is nogroup in Alpine, so we use 65532
SECRETS_GID=65532

# Logging driver tuning
POSTGRES_LOG_MAX_SIZE=100m
POSTGRES_LOG_MAX_FILE=5
POSTGRES_LOG_MODE=non-blocking
POSTGRES_LOG_BUFFER=4m

# Logical backup sidecar
LOGICAL_BACKUP_INTERVAL_SECONDS=86400
LOGICAL_BACKUP_RETENTION_DAYS=7
LOGICAL_BACKUP_OUTPUT=/backups/logical
LOGICAL_BACKUP_HOST_OUTPUT=./backups/logical
LOGICAL_BACKUP_EXCLUDE=postgres

# ValKey memory cache
VALKEY_PORT=6379
# Host port that Docker binds to; override if 6379 is unavailable locally.
VALKEY_HOST_PORT=6379
VALKEY_APPENDONLY=yes
VALKEY_MAXMEMORY=256mb
VALKEY_MAXMEMORY_POLICY=allkeys-lru
VALKEY_DATABASES=16
VALKEY_PASSWORD_FILE=./secrets/valkey_password

# PgBouncer connection pooling
# PgBouncer listens on both 5432 (default PostgreSQL port) and 6432 (legacy)
# This makes pooled connections the default path - clients must explicitly
# connect to port 5433 (POSTGRES_PORT) to bypass pooling
# Docker maps both host ports to the single container port (PGBOUNCER_PORT)
PGBOUNCER_PORT=6432
PGBOUNCER_HOST_PORT=6432
PGBOUNCER_EXTRA_HOST_PORT=5432
PGBOUNCER_POOL_MODE=session
PGBOUNCER_MAX_CLIENT_CONN=200
PGBOUNCER_DEFAULT_POOL_SIZE=20
PGBOUNCER_RESERVE_POOL_SIZE=5
PGBOUNCER_RESERVE_POOL_TIMEOUT=5
PGBOUNCER_MIN_POOL_SIZE=5
PGBOUNCER_AUTH_USER=pgbouncer_auth
PGBOUNCER_AUTH_PASSWORD_FILE=./secrets/pgbouncer_auth_password
PGBOUNCER_STATS_USER=pgbouncer_stats
PGBOUNCER_STATS_PASSWORD_FILE=./secrets/pgbouncer_stats_password
PGBOUNCER_ADMIN_USERS=postgres
PGBOUNCER_STATS_USERS=pgbouncer_stats

# PgBouncer TLS configuration (client-side SSL for connections to PgBouncer)
# Self-signed certificates are generated automatically if files are absent
# sslmode options: disable, allow, prefer, require, verify-ca, verify-full
PGBOUNCER_CLIENT_TLS_SSLMODE=require
PGBOUNCER_CLIENT_TLS_CERT_FILE=/tmp/pgbouncer/tls/server.crt
PGBOUNCER_CLIENT_TLS_KEY_FILE=/tmp/pgbouncer/tls/server.key
PGBOUNCER_CLIENT_TLS_SELF_SIGNED_SUBJECT=/CN=core_data_pgbouncer
PGBOUNCER_CLIENT_TLS_SELF_SIGNED_DAYS=730

# =============================================================================
# PgBouncer Transaction Mode Compatibility (PgBouncer 1.21+)
# =============================================================================

# Prepared Statements - THE key setting for ORM/driver compatibility
# Caches N prepared statements per server connection, re-prepares on connection change
# Memory: ~5KB * pool_size * max_prepared_statements (~100MB for 1024 with pool_size=20)
PGBOUNCER_MAX_PREPARED_STATEMENTS=1024

# Startup parameters to accept but not track (driver compatibility)
# extra_float_digits: JDBC, Go lib/pq, .NET | options: libpq, psql, node-postgres
PGBOUNCER_IGNORE_STARTUP_PARAMETERS=extra_float_digits,options

# Extra parameters to track across connection reassignments
PGBOUNCER_TRACK_EXTRA_PARAMETERS=IntervalStyle

# Append client IP:port to application_name for debugging
PGBOUNCER_APPLICATION_NAME_ADD_HOST=1

# =============================================================================
# PgBouncer Connection Lifecycle
# =============================================================================

# Recycle server connections after N seconds (prevents memory bloat)
PGBOUNCER_SERVER_LIFETIME=1800

# Close idle server connections after N seconds
PGBOUNCER_SERVER_IDLE_TIMEOUT=300

# Backend connection timeout (fail fast)
PGBOUNCER_SERVER_CONNECT_TIMEOUT=10

# Backend login retry interval
PGBOUNCER_SERVER_LOGIN_RETRY=5

# =============================================================================
# PgBouncer Client Timeout Protection
# =============================================================================

# Max time client waits in queue for connection (surfaces pool exhaustion)
PGBOUNCER_QUERY_WAIT_TIMEOUT=30

# Close idle client connections after N seconds (prevents leaks)
PGBOUNCER_CLIENT_IDLE_TIMEOUT=3600

# =============================================================================
# PgBouncer DNS Failover
# =============================================================================

# DNS cache lifetime (balance lookups vs failover speed)
PGBOUNCER_DNS_MAX_TTL=30

# Negative DNS cache (quick recovery from DNS failures)
PGBOUNCER_DNS_NXDOMAIN_TTL=5

# Memcached hot object cache
MEMCACHED_PORT=11211
MEMCACHED_MEMORY_MB=128
MEMCACHED_MAX_CONNECTIONS=1024
MEMCACHED_THREADS=4

# RabbitMQ messaging
RABBITMQ_PORT=5672
RABBITMQ_HOST_PORT=5672
RABBITMQ_MANAGEMENT_PORT=15672
RABBITMQ_MANAGEMENT_HOST_PORT=15672
RABBITMQ_STREAM_PORT=5552
RABBITMQ_STREAM_HOST_PORT=5552
RABBITMQ_DEFAULT_USER=coredata
RABBITMQ_DEFAULT_PASS_FILE=./secrets/rabbitmq_default_pass
RABBITMQ_ERLANG_COOKIE_FILE=./secrets/rabbitmq_erlang_cookie
RABBITMQ_DATA_MOUNT_PATH=/var/lib/rabbitmq
# Pre-built RabbitMQ image uses UID 100, GID 101 (the rabbitmq user baked into the image).
RABBITMQ_UID=100
RABBITMQ_GID=101
# Container resource limits (0 = unlimited).
RABBITMQ_MEMORY_LIMIT=0
RABBITMQ_CPU_LIMIT=0.0
# Erlang VM tuning flags passed via RABBITMQ_SERVER_ADDITIONAL_ERL_ARGS.
# +sbwt none          — disable speculative scheduler busy-waiting (saves CPU)
# +sbwtdcpu none      — disable dirty-CPU scheduler busy-waiting
# +sbwtdio none       — disable dirty-IO scheduler busy-waiting
# +stbt ts            — bind scheduler threads to topology (reduces context switches)
RABBITMQ_SERVER_ADDITIONAL_ERL_ARGS="+sbwt none +sbwtdcpu none +sbwtdio none +stbt ts"

# Time zone for containers
TZ=UTC

# CI workflow defaults
CORE_DATA_CI_MIN_DISK_MB=4096
CORE_DATA_CI_OUTPUT_PATH=./backups/ci-output.json
CORE_DATA_REQUIRE_ATTESTATION=0
CORE_DATA_ATTESTATION_REPO=paudley/core_data

# Daily maintenance tuning (optional)
# DAILY_PG_STAT_LIMIT=100
# DAILY_BUFFERCACHE_LIMIT=50
# DAILY_DEAD_TUPLE_THRESHOLD=100000
# DAILY_DEAD_TUPLE_RATIO=0.2
# DAILY_REPLICATION_LAG_THRESHOLD=300
# DAILY_INDEX_MIN_SIZE_MB=10
# DAILY_HTML_REPORT=true

# Permission health check (runs on container startup)
# Validates and auto-repairs permissions for database owners across all extension schemas.
# Set to 0 to disable the health check entirely.
CORE_DATA_PERMISSION_HEALTHCHECK=1
# Set to 0 to report permission issues without auto-repairing.
CORE_DATA_PERMISSION_REPAIR=1