Database Setup

Rack Gateway stores users, API tokens, sessions, settings and the audit log in PostgreSQL. Give each gateway its own database, with an admin user for migrations and a separate, narrower app user for the gateway itself (see Database Users and Audit Roles).

Requirements

Requirement	Minimum	Recommended
PostgreSQL version	17 (what development and CI run)	17
Storage	10GB	50GB+ (scales with audit logs)
Memory	256MB	1GB+
Connections (`max_connections`)	60 (default pools, see below)	100+

Connection Configuration

Connection String

Set the DATABASE_URL environment variable:

DATABASE_URL=postgres://user:password@host:5432/rack_gateway

Alternative: Individual Variables

Use PostgreSQL standard environment variables:

PGHOST=database.example.com
PGPORT=5432
PGUSER=rack_gateway
PGPASSWORD=your-password
PGDATABASE=rack_gateway

Connection Pool

Each gateway process has two connection pools, both sized by these variables: one for requests, audit writes and the audit anchor lock, and one for the background job queue. A process can therefore open up to twice DB_MAX_OPEN_CONNS connections, and the job queue’s pool keeps DB_MAX_IDLE_CONNS open.

Variable	Default	Description
`DB_MAX_OPEN_CONNS`	`10`	Maximum connections per pool
`DB_MAX_IDLE_CONNS`	`3`	Idle connections to keep warm, per pool
`DB_CONN_MAX_LIFETIME`	`30m`	Connection lifetime before recycling
`DB_CONN_MAX_IDLE_TIME`	`10m`	Idle time before closing

Sizing Guidelines

Workload	Max Open	Max Idle	`max_connections` needed	Notes
Small (<100 users)	10	3	60	Default settings
Medium (<1000 users)	25	5	150
Large (>1000 users)	50	10	300	Increase with load

A request holds a connection for milliseconds. The hourly audit anchor write holds one for the length of its S3 calls (at most a minute), and an audit write gives up after 10 seconds, including the wait for a connection, so don’t go below the default.

Database Provisioning

Use a PostgreSQL server where you can create roles: the migrations need the audit roles below, and the gateway should connect as a user that can’t change the audit log.

AWS RDS
Docker (Dev)

Create an RDS PostgreSQL instance:

resource "aws_db_instance" "gateway" {
  identifier     = "rack-gateway"
  engine         = "postgres"
  engine_version = "17.11"
  instance_class = "db.t3.medium"

  allocated_storage     = 50
  max_allocated_storage = 200
  storage_type          = "gp3"
  storage_encrypted     = true

  db_name  = "rack_gateway"
  username = "rack_gateway_admin"
  password = var.db_password

  vpc_security_group_ids = [aws_security_group.rds.id]
  db_subnet_group_name   = aws_db_subnet_group.private.name

  backup_retention_period = 30
  backup_window           = "03:00-04:00"
  maintenance_window      = "sun:04:00-sun:05:00"

  deletion_protection = true
  skip_final_snapshot = false

  performance_insights_enabled = true
}

For local development:

The repository’s docker-compose.yml runs postgres:17-alpine and the task helpers create the audit roles for you (scripts/audit-roles.sql). For your own container:

services:
  postgres:
    image: postgres:17
    environment:
      POSTGRES_USER: rack_gateway
      POSTGRES_PASSWORD: development
      POSTGRES_DB: rack_gateway
    volumes:
      - pgdata:/var/lib/postgresql/data
    ports:
      - "5432:5432"

volumes:
  pgdata:

Then create the audit roles before the first migration: psql "$DATABASE_URL" -f scripts/audit-roles.sql.

Database Users and Audit Roles

The audit log lives in the audit schema, owned by a role the gateway never uses. The migrations expect three roles that don’t log in to exist already:

Role	Holds	Granted to
`audit_owner`	Owns the `audit` schema, its table and functions	The admin user (so migrations can create them)
`audit_writer`	Appending events through `audit.append_audit_event`	The app user
`audit_reader`	Reading events	The app user

scripts/audit-roles.sql creates them (it’s safe to run again); with Terraform, use postgresql_role resources.

Then use two login users:

Admin user (for example the RDS master user, rack_gateway_admin): owns the database’s tables and runs migrate, reset-db and maintenance. It must be a member of audit_owner.
App user (for example rack_gateway_app): what the gateway connects as. It can read and write the ordinary tables, append to the audit log and read it, but it can’t update or delete audit events, change the audit functions, or alter the schema.

-- As the admin user, after creating the audit roles
GRANT audit_owner TO rack_gateway_admin;

CREATE ROLE rack_gateway_app WITH LOGIN PASSWORD '...';
GRANT audit_writer, audit_reader TO rack_gateway_app;
GRANT CONNECT ON DATABASE rack_gateway TO rack_gateway_app;
GRANT USAGE ON SCHEMA public TO rack_gateway_app;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO rack_gateway_app;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO rack_gateway_app;
-- Tables and sequences later migrations create
ALTER DEFAULT PRIVILEGES FOR ROLE rack_gateway_admin IN SCHEMA public
  GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO rack_gateway_app;
ALTER DEFAULT PRIVILEGES FOR ROLE rack_gateway_admin IN SCHEMA public
  GRANT USAGE, SELECT ON SEQUENCES TO rack_gateway_app;

The repository’s convox.yml gives the gateway service the app user (DATABASE_URL) and the admin service the admin user (RGW_DATABASE_URL=${ADMIN_DATABASE_URL}; scripts/start-gateway.sh unsets ADMIN_DATABASE_URL before the server starts).

Migrations

Automatic Migrations

Only development builds (compiled with the testbypass tag) with DEV_MODE=true run migrations when the gateway starts. The published image never does: run rack-gateway-api migrate before starting a new release.

Manual Migrations

Run migrations manually:

Run them as the admin user:

# Via Docker (the binary is /app/rack-gateway-api in the image)
docker run --rm -e DATABASE_URL="$ADMIN_DATABASE_URL" -e COLUMN_ENCRYPTION_KEY \
  docspringcom/rack-gateway:vX.Y.Z ./rack-gateway-api migrate

# Via Convox: against the new release, before promoting it
convox run admin ./rack-gateway-api migrate -a rack-gateway --release <release-id>

Migration Safety

migrate applies only migrations missing from schema_migrations, so it’s safe to run again
Each migration runs in a transaction: a failure rolls it back, and migrate exits non-zero naming it
It also runs the job queue’s (River) migrations

Database Reset

Development Reset

In a development (testbypass) build:

export RESET_RACK_GATEWAY_DATABASE=DELETE_ALL_DATA
export DEV_MODE=true
rack-gateway-api reset-db

Emergency Production Reset

export RESET_RACK_GATEWAY_DATABASE=DELETE_ALL_DATA
export DISABLE_DATABASE_ENVIRONMENT_CHECK=1
rack-gateway-api reset-db

Safety Guards

The reset command enforces safety checks:

Check	Purpose	Override
`RESET_RACK_GATEWAY_DATABASE=DELETE_ALL_DATA`	Explicit confirmation	Required
Development mode or environment check	Prevent accidental prod reset	`DISABLE_DATABASE_ENVIRONMENT_CHECK=1`

Schema Overview

The gateway creates these tables:

Table	Purpose
`users`	User accounts and roles
`user_sessions`	Active user sessions
`api_tokens`	API tokens for CI/CD
`mfa_methods`	MFA registrations (TOTP, WebAuthn)
`mfa_backup_codes`	MFA recovery codes
`trusted_devices`	Remembered MFA devices
`audit.audit_event`	Complete audit trail
`deploy_approval_requests`	Deploy workflow state
`settings`	Configuration settings
`user_resources`	User ↔ resource audit links
`cli_login_states`	CLI OAuth state tracking
`rgw_internal_metadata`	Environment markers
`slack_integration`	Slack OAuth tokens
`mfa_attempts`, `used_totp_steps`, `webauthn_challenges`	MFA rate limiting, TOTP replay protection, single-use WebAuthn challenges
`audit_verification_status`	The latest audit verification run (display only)
`river_*`	The background job queue
`schema_migrations`	Migration tracking

Backup and Recovery

Backup Strategy

For production, implement regular backups:

Frequency	Retention	Purpose
Hourly	24 hours	Point-in-time recovery
Daily	30 days	Operational recovery
Weekly	1 year	Compliance/audit

RDS Automated Backups

resource "aws_db_instance" "gateway" {
  # ... other config
  backup_retention_period = 30  # days
  backup_window           = "03:00-04:00"  # UTC

  # Enable PITR
  delete_automated_backups = false
}

Manual Backup

# Export full database
pg_dump -h $PGHOST -U $PGUSER $PGDATABASE | gzip > backup-$(date +%Y%m%d).sql.gz

# Export specific tables
pg_dump -h $PGHOST -U $PGUSER -t audit.audit_event $PGDATABASE > audit-backup.sql

Recovery

# Restore full backup
gunzip -c backup-20240115.sql.gz | psql -h $PGHOST -U $PGUSER $PGDATABASE

# Restore from RDS snapshot
aws rds restore-db-instance-from-db-snapshot \
  --db-instance-identifier rack-gateway-restored \
  --db-snapshot-identifier rds:rack-gateway-2024-01-15-03-00

Audit Log Considerations

Storage Growth

Audit logs grow continuously. Estimate storage needs:

Events/Day	Annual Storage	Notes
1,000	~5 GB	Small team
10,000	~50 GB	Medium team
100,000	~500 GB	Large/busy

Log Retention

The gateway never deletes audit events, so the database keeps them as long as it exists: retention is a matter of keeping its backups. Two settings control the copies elsewhere:

# CloudWatch retention for the container logs, including the stdout request log (convox.yml appSettings)
convox env set LOG_RETENTION_DAYS=400 -a rack-gateway

S3 anchors are locked for the bucket’s default Object Lock retention, set on the bucket (see S3 WORM Storage).

Audit Anchoring

For tamper-evident logs, enable S3 WORM anchoring:

convox env set \
  AUDIT_ANCHOR_BUCKET=audit-anchors-prod \
  AUDIT_ANCHOR_CHAIN_ID=production

See S3 WORM Storage for setup details.

Performance Tuning

PostgreSQL Configuration

For dedicated gateway databases:

-- Connection limits
ALTER SYSTEM SET max_connections = 100;

-- Memory settings
ALTER SYSTEM SET shared_buffers = '256MB';
ALTER SYSTEM SET work_mem = '16MB';
ALTER SYSTEM SET maintenance_work_mem = '64MB';

-- Write performance
ALTER SYSTEM SET wal_buffers = '16MB';
ALTER SYSTEM SET checkpoint_completion_target = 0.9;

-- Query optimization
ALTER SYSTEM SET effective_cache_size = '768MB';
ALTER SYSTEM SET random_page_cost = 1.1;  -- For SSD

SELECT pg_reload_conf();

Index Maintenance

The gateway creates necessary indexes automatically. For high-volume deployments, monitor and maintain:

-- Check index usage
SELECT
  relname AS table,
  indexrelname AS index,
  idx_scan AS scans,
  idx_tup_read AS tuples_read,
  idx_tup_fetch AS tuples_fetched
FROM pg_stat_user_indexes
ORDER BY idx_scan DESC;

-- Reindex if needed (as the admin user)
REINDEX TABLE audit.audit_event;

Monitoring

Key Metrics

Monitor these PostgreSQL metrics:

Metric	Warning Threshold	Critical Threshold
Connection usage	70%	90%
Replication lag	1s	10s
Transaction rate	-	Baseline + 200%
Disk usage	70%	85%

CloudWatch (RDS)

resource "aws_cloudwatch_metric_alarm" "db_connections" {
  alarm_name          = "rack-gateway-db-connections"
  comparison_operator = "GreaterThanThreshold"
  evaluation_periods  = 2
  metric_name         = "DatabaseConnections"
  namespace           = "AWS/RDS"
  period              = 300
  statistic           = "Average"
  threshold           = 40

  dimensions = {
    DBInstanceIdentifier = aws_db_instance.gateway.identifier
  }
}

Query Performance

Enable slow query logging:

ALTER SYSTEM SET log_min_duration_statement = 1000;  -- Log queries > 1s
SELECT pg_reload_conf();

Security

Network Isolation

Place database in private subnet
Use security groups to restrict access
Enable SSL for connections

resource "aws_security_group" "rds" {
  name        = "rack-gateway-rds"
  vpc_id      = var.vpc_id

  ingress {
    from_port       = 5432
    to_port         = 5432
    protocol        = "tcp"
    security_groups = [aws_security_group.gateway.id]
  }

  egress {
    from_port   = 0
    to_port     = 0
    protocol    = "-1"
    cidr_blocks = ["0.0.0.0/0"]
  }
}

Encryption

Enable encryption at rest (RDS default)
Use TLS for connections in transit, and verify the server: sslmode=require encrypts the connection but accepts any certificate, so whoever can intercept the traffic can pose as the database
Consider KMS for key management

# Verify the database's certificate and hostname. The image ships Amazon RDS's CA bundle;
# for another database, point PGSSLROOTCERT at its CA.
PGSSLMODE=verify-full
PGSSLROOTCERT=/app/certs/rds-global-bundle.pem

convox.yml sets both. DATABASE_URL has to name the database by the hostname on its certificate (an RDS instance’s endpoint, not an IP address or an alias). PGSSLMODE only ever makes the URL’s own sslmode stricter.

Access Control

Connect the gateway as the app user, never the admin user: see Database Users and Audit Roles.

Troubleshooting

Connection Issues

# Test connection
psql $DATABASE_URL -c "SELECT 1"

# Check connection count
psql $DATABASE_URL -c "SELECT count(*) FROM pg_stat_activity"

# View active connections
psql $DATABASE_URL -c "SELECT * FROM pg_stat_activity WHERE datname = 'rack_gateway'"

Migration Failures

# Check migration status
psql $DATABASE_URL -c "SELECT * FROM schema_migrations ORDER BY version"

# The migrate command prints the migration that failed and why; if it reports a missing role,
# create the audit roles (scripts/audit-roles.sql) and grant audit_owner to the admin user

Performance Issues

-- Find slow queries
SELECT query, calls, mean_exec_time, total_exec_time
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

-- Check table sizes
SELECT relname, pg_size_pretty(pg_total_relation_size(relid))
FROM pg_stat_user_tables
ORDER BY pg_total_relation_size(relid) DESC;

Next Steps

Convox Deployment - Deploy the gateway
S3 WORM Storage - Audit anchoring
Production Checklist - Go-live preparation