Skip to content

PostgreSQL Service

Purpose: Primary database for Fulcrum state, users, policies, and audit logs Audience: Backend Engineers, DevOps Source of Truth: TRUTH_MAP.md

Last Updated: February 1, 2026

Purpose

PostgreSQL serves as Fulcrum's primary database, storing: - User accounts and tenant information - Policies and policy evaluations - Execution envelopes and state - Audit trail events - Budget allocations and usage

Key Feature: Row Level Security (RLS) provides automatic tenant isolation.

Configuration

Variable Required Default Description
POSTGRES_CONN_STR Yes - Full connection string
DATABASE_URL Yes - Alternative connection string
POSTGRES_USER No fulcrum Database username
POSTGRES_PASSWORD No fulcrum Database password
POSTGRES_DB No fulcrum Database name

Connection String Format

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

Connection

Environment Connection
Local (Docker) postgresql://fulcrum:fulcrum@localhost:5432/fulcrum
Railway (Production) timescaledb-docker.railway.internal:5432
Database Name fulcrum_metrics
App Schema fulcrum
Metrics Schema metrics

Note: Production uses POSTGRES_CONN_STR from Doppler/Railway secrets.

Health Check

# Local
pg_isready -h localhost -p 5432 -U fulcrum

# With psql
psql "$POSTGRES_CONN_STR" -c "SELECT 1;"

# Check RLS is enabled
psql "$POSTGRES_CONN_STR" -c "SELECT tablename, rowsecurity FROM pg_tables WHERE schemaname = 'public';"

Schema Overview

Core Tables

Table Purpose RLS
tenants Tenant accounts No (superuser only)
users User accounts Yes
policies Policy definitions Yes
envelopes Execution envelopes Yes
events Audit trail Yes
budgets Budget allocations Yes

TimescaleDB Hypertables

Table Purpose Retention
metrics Time-series metrics 90 days
cost_events Token/cost tracking 90 days

Row Level Security

Every table with tenant_id has RLS policies:

-- Enable RLS
ALTER TABLE envelopes ENABLE ROW LEVEL SECURITY;

-- Create policy
CREATE POLICY tenant_isolation ON envelopes
    USING (tenant_id = current_setting('fulcrum.current_tenant')::uuid);

-- Set tenant context (done by application)
SET fulcrum.current_tenant = 'tenant-uuid';

Failure Modes

Failure Impact Detection Recovery
Connection timeout API returns 503 Health check fails Restart connection pool
Disk full Writes fail Monitoring alert Expand storage
RLS bypassed Data leak Audit log review Immediate patch
Connection exhaustion New requests fail Pool metrics Increase pool size

Backup & Recovery

Railway Managed

  • Automatic daily backups (7-day retention)
  • Point-in-time recovery available
  • Access via Railway dashboard

Manual Backup

pg_dump "$POSTGRES_CONN_STR" > backup_$(date +%Y%m%d).sql

Performance Tuning

-- Check slow queries
SELECT query, calls, mean_time, total_time
FROM pg_stat_statements
ORDER BY mean_time DESC
LIMIT 10;

-- Check index usage
SELECT relname, idx_scan, seq_scan
FROM pg_stat_user_tables
WHERE seq_scan > idx_scan;

Back to Runbooks | Documentation