Comprehensive guide for database infrastructure, design patterns, and operations in the Lilith platform.
Table of Contents#
- Overview
- PostgreSQL (RDS)
- Redis (ElastiCache)
- Database Per Service
- Connection Management
- Migrations
- Backup and Recovery
- Performance Optimization
- Monitoring
- Security
Overview#
The Lilith platform uses a polyglot persistence strategy with database-per-service architecture.
Database Stack#
| Database | Use Case | Service |
|---|---|---|
| PostgreSQL (RDS) | Transactional data | All services |
| Redis (ElastiCache) | Caching, sessions | Auth, BFF |
| DynamoDB | High-scale reads | Backup metadata |
| S3 | Object storage | Media |
Architecture Diagram#
text
┌─────────────────────────────────────────────────────────────────────────┐
│ Database Architecture │
│ │
│ ┌─────────────────────────────────────────────────────────────────┐ │
│ │ Services │ │
│ │ ┌────────┐ ┌────────┐ ┌────────┐ ┌────────┐ ┌────────┐ │ │
│ │ │ Auth │ │ BFF │ │Content │ │ AI │ │ Media │ │ │
│ │ └───┬────┘ └───┬────┘ └───┬────┘ └───┬────┘ └───┬────┘ │ │
│ └──────┼──────────┼──────────┼──────────┼──────────┼───────────────┘ │
│ │ │ │ │ │ │
│ ▼ ▼ ▼ ▼ ▼ │
│ ┌─────────────────────────────────────────────────────────────────┐ │
│ │ Connection Pooling (PgBouncer) │ │
│ └─────────────────────────────────────────────────────────────────┘ │
│ │ │
│ ┌──────────────────────┼──────────────────────┐ │
│ ▼ ▼ ▼ │
│ ┌─────────────┐ ┌─────────────┐ ┌─────────────┐ │
│ │ auth_db │ │ content_db │ │ ai_db │ │
│ │ PostgreSQL │ │ PostgreSQL │ │ PostgreSQL │ │
│ └─────────────┘ └─────────────┘ └─────────────┘ │
│ │ │ │ │
│ └──────────────────────┼──────────────────────┘ │
│ ▼ │
│ ┌─────────────────────┐ │
│ │ Read Replicas │ │
│ │ (Multi-AZ) │ │
│ └─────────────────────┘ │
│ │
│ ┌──────────────────────────────────────────────────────────────────┐ │
│ │ Redis Cluster │ │
│ │ ┌──────────┐ ┌──────────┐ ┌──────────┐ │ │
│ │ │ Primary │ │ Replica │ │ Replica │ │ │
│ │ │ Node 1 │ │ Node 2 │ │ Node 3 │ │ │
│ │ └──────────┘ └──────────┘ └──────────┘ │ │
│ └──────────────────────────────────────────────────────────────────┘ │
└─────────────────────────────────────────────────────────────────────────┘
PostgreSQL (RDS)#
Instance Configuration#
| Environment | Instance Type | Storage | Multi-AZ | Read Replicas |
|---|---|---|---|---|
| Development | db.t3.micro | 20 GB | No | 0 |
| Staging | db.t3.small | 50 GB | Yes | 1 |
| Production | db.r5.large | 200 GB | Yes | 2 |
Terraform Configuration#
hcl
# deploy/terraform/modules/database/main.tf
resource "aws_db_subnet_group" "main" {
name = "${var.environment}-lilith-db-subnet"
subnet_ids = var.database_subnet_ids
tags = {
Name = "${var.environment}-lilith-db-subnet"
Environment = var.environment
}
}
resource "aws_security_group" "rds" {
name = "${var.environment}-lilith-rds-sg"
description = "Security group for RDS instances"
vpc_id = var.vpc_id
ingress {
from_port = 5432
to_port = 5432
protocol = "tcp"
security_groups = [var.app_security_group_id]
}
egress {
from_port = 0
to_port = 0
protocol = "-1"
cidr_blocks = ["0.0.0.0/0"]
}
tags = var.tags
}
resource "aws_db_parameter_group" "main" {
name = "${var.environment}-lilith-pg15"
family = "postgres15"
parameter {
name = "log_statement"
value = "ddl"
}
parameter {
name = "log_min_duration_statement"
value = "1000" # Log queries > 1s
}
parameter {
name = "shared_preload_libraries"
value = "pg_stat_statements"
}
parameter {
name = "pg_stat_statements.track"
value = "all"
}
parameter {
name = "max_connections"
value = var.max_connections
}
tags = var.tags
}
resource "aws_db_instance" "primary" {
identifier = "${var.environment}-lilith-primary"
engine = "postgres"
engine_version = "15.4"
instance_class = var.instance_class
allocated_storage = var.allocated_storage
max_allocated_storage = var.max_allocated_storage
storage_type = "gp3"
storage_encrypted = true
kms_key_id = var.kms_key_arn
db_name = "lilith"
username = var.db_username
password = var.db_password
db_subnet_group_name = aws_db_subnet_group.main.name
vpc_security_group_ids = [aws_security_group.rds.id]
parameter_group_name = aws_db_parameter_group.main.name
multi_az = var.multi_az
publicly_accessible = false
backup_retention_period = var.backup_retention_period
backup_window = "03:00-04:00"
maintenance_window = "Mon:04:00-Mon:05:00"
performance_insights_enabled = true
performance_insights_retention_period = 7
performance_insights_kms_key_id = var.kms_key_arn
enabled_cloudwatch_logs_exports = ["postgresql", "upgrade"]
deletion_protection = var.environment == "production"
skip_final_snapshot = var.environment != "production"
final_snapshot_identifier = var.environment == "production" ? "${var.environment}-lilith-final-snapshot" : null
tags = var.tags
}
resource "aws_db_instance" "replica" {
count = var.read_replica_count
identifier = "${var.environment}-lilith-replica-${count.index + 1}"
replicate_source_db = aws_db_instance.primary.identifier
instance_class = var.replica_instance_class
storage_encrypted = true
kms_key_id = var.kms_key_arn
vpc_security_group_ids = [aws_security_group.rds.id]
parameter_group_name = aws_db_parameter_group.main.name
publicly_accessible = false
performance_insights_enabled = true
performance_insights_retention_period = 7
performance_insights_kms_key_id = var.kms_key_arn
tags = merge(var.tags, {
Role = "replica"
})
}
Parameter Tuning#
hcl
# Production-optimized parameters
parameter {
name = "shared_buffers"
value = "{DBInstanceClassMemory/4}" # 25% of RAM
}
parameter {
name = "effective_cache_size"
value = "{DBInstanceClassMemory*3/4}" # 75% of RAM
}
parameter {
name = "work_mem"
value = "65536" # 64MB
}
parameter {
name = "maintenance_work_mem"
value = "524288" # 512MB
}
parameter {
name = "random_page_cost"
value = "1.1" # For SSD storage
}
parameter {
name = "checkpoint_completion_target"
value = "0.9"
}
parameter {
name = "wal_buffers"
value = "65536" # 64MB
}
parameter {
name = "default_statistics_target"
value = "100"
}
Redis (ElastiCache)#
Cluster Configuration#
| Environment | Node Type | Nodes | Cluster Mode |
|---|---|---|---|
| Development | cache.t3.micro | 1 | Disabled |
| Staging | cache.t3.small | 3 | Enabled |
| Production | cache.r5.large | 6 | Enabled |
Terraform Configuration#
hcl
# deploy/terraform/modules/redis/main.tf
resource "aws_elasticache_subnet_group" "main" {
name = "${var.environment}-lilith-redis-subnet"
subnet_ids = var.cache_subnet_ids
tags = var.tags
}
resource "aws_security_group" "redis" {
name = "${var.environment}-lilith-redis-sg"
description = "Security group for Redis cluster"
vpc_id = var.vpc_id
ingress {
from_port = 6379
to_port = 6379
protocol = "tcp"
security_groups = [var.app_security_group_id]
}
egress {
from_port = 0
to_port = 0
protocol = "-1"
cidr_blocks = ["0.0.0.0/0"]
}
tags = var.tags
}
resource "aws_elasticache_parameter_group" "main" {
name = "${var.environment}-lilith-redis7"
family = "redis7"
parameter {
name = "maxmemory-policy"
value = "volatile-lru"
}
parameter {
name = "notify-keyspace-events"
value = "Ex" # Enable expired events
}
tags = var.tags
}
resource "aws_elasticache_replication_group" "main" {
replication_group_id = "${var.environment}-lilith-redis"
description = "Lilith Redis cluster"
node_type = var.node_type
num_cache_clusters = var.cluster_mode_enabled ? null : var.num_cache_nodes
parameter_group_name = aws_elasticache_parameter_group.main.name
engine = "redis"
engine_version = "7.0"
port = 6379
subnet_group_name = aws_elasticache_subnet_group.main.name
security_group_ids = [aws_security_group.redis.id]
at_rest_encryption_enabled = true
transit_encryption_enabled = true
auth_token = var.auth_token
kms_key_id = var.kms_key_arn
automatic_failover_enabled = var.multi_az
multi_az_enabled = var.multi_az
snapshot_retention_limit = var.snapshot_retention_days
snapshot_window = "02:00-03:00"
maintenance_window = "sun:03:00-sun:04:00"
apply_immediately = var.environment != "production"
# Cluster mode configuration
dynamic "cluster_mode" {
for_each = var.cluster_mode_enabled ? [1] : []
content {
num_node_groups = var.num_node_groups
replicas_per_node_group = var.replicas_per_node_group
}
}
tags = var.tags
}
Use Cases#
| Use Case | TTL | Max Size |
|---|---|---|
| Session cache | 24h | 100MB |
| API response cache | 5m | 500MB |
| Rate limiting | 1m | 50MB |
| User preferences | 1h | 200MB |
Database Per Service#
Service Database Mapping#
| Service | Database | Schema |
|---|---|---|
| auth | lilith_auth | users, sessions, tokens |
| content | lilith_content | courses, lessons, meditations |
| conversation | lilith_conversation | threads, messages |
| media | lilith_media | assets, transcodes |
| ai | lilith_ai | embeddings, prompts |
| notification | lilith_notification | notifications, templates |
| moderation | lilith_moderation | reports, flags |
Database Isolation#
typescript
// packages/database/src/connection.ts
export interface DatabaseConfig {
host: string;
port: number;
database: string;
user: string;
password: string;
ssl: boolean;
poolSize: number;
}
export function createServiceConnection(service: string): DatabaseConfig {
return {
host:
process.env[`${service.toUpperCase()}_DB_HOST`] || process.env.DB_HOST,
port: parseInt(process.env.DB_PORT || '5432'),
database: `lilith_${service}`,
user:
process.env[`${service.toUpperCase()}_DB_USER`] || process.env.DB_USER,
password:
process.env[`${service.toUpperCase()}_DB_PASSWORD`] ||
process.env.DB_PASSWORD,
ssl: process.env.DB_SSL === 'true',
poolSize: parseInt(process.env.DB_POOL_SIZE || '10'),
};
}
Cross-Service Data Access#
text
┌─────────────────────────────────────────────────────────────────────────┐
│ Cross-Service Data Pattern │
│ │
│ ┌─────────────┐ ┌─────────────┐ │
│ │ Service │ API Call │ Service │ │
│ │ A │ ─────────────────────────▶ │ B │ │
│ │ │ │ │ │
│ └──────┬──────┘ └──────┬──────┘ │
│ │ │ │
│ ▼ ▼ │
│ ┌─────────────┐ ┌─────────────┐ │
│ │ Database │ │ Database │ │
│ │ A │ ❌ No Direct Access │ B │ │
│ └─────────────┘ └─────────────┘ │
│ │
│ Services MUST NOT directly access other service databases. │
│ Use API calls or event-driven communication instead. │
└─────────────────────────────────────────────────────────────────────────┘
Connection Management#
PgBouncer Configuration#
ini
# deploy/kubernetes/base/pgbouncer/pgbouncer.ini
[databases]
lilith_auth = host=${AUTH_DB_HOST} port=5432 dbname=lilith_auth
lilith_content = host=${CONTENT_DB_HOST} port=5432 dbname=lilith_content
lilith_ai = host=${AI_DB_HOST} port=5432 dbname=lilith_ai
[pgbouncer]
listen_port = 6432
listen_addr = 0.0.0.0
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt
pool_mode = transaction
max_client_conn = 1000
default_pool_size = 20
min_pool_size = 5
reserve_pool_size = 5
reserve_pool_timeout = 5
max_db_connections = 100
max_user_connections = 100
server_reset_query = DISCARD ALL
server_check_delay = 30
server_check_query = SELECT 1
server_lifetime = 3600
server_idle_timeout = 600
server_connect_timeout = 15
server_login_retry = 15
query_timeout = 300
query_wait_timeout = 120
client_idle_timeout = 0
log_connections = 1
log_disconnections = 1
log_pooler_errors = 1
stats_period = 60
admin_users = pgbouncer_admin
stats_users = pgbouncer_stats
Application Pool Configuration#
typescript
// packages/database/src/pool.ts
import { Pool, PoolConfig } from 'pg';
export function createPool(config: DatabaseConfig): Pool {
const poolConfig: PoolConfig = {
host: config.host,
port: config.port,
database: config.database,
user: config.user,
password: config.password,
ssl: config.ssl ? { rejectUnauthorized: true } : false,
// Pool settings
max: config.poolSize, // Maximum connections
min: 2, // Minimum connections
idleTimeoutMillis: 30000, // Close idle connections after 30s
connectionTimeoutMillis: 10000, // Fail after 10s if can't connect
// Statement timeout
statement_timeout: 30000, // 30s query timeout
// Application name for debugging
application_name: `lilith-${process.env.SERVICE_NAME}`,
};
const pool = new Pool(poolConfig);
pool.on('error', (err) => {
console.error('Unexpected pool error:', err);
});
pool.on('connect', (client) => {
client.query('SET statement_timeout = 30000');
});
return pool;
}
Migrations#
Migration Framework#
The platform uses Knex.js for database migrations.
typescript
// packages/database/src/migrations.ts
import Knex from 'knex';
export interface MigrationConfig {
directory: string;
tableName: string;
schemaName: string;
}
export function createKnexConfig(
dbConfig: DatabaseConfig,
migrationConfig: MigrationConfig
): Knex.Config {
return {
client: 'postgresql',
connection: {
host: dbConfig.host,
port: dbConfig.port,
database: dbConfig.database,
user: dbConfig.user,
password: dbConfig.password,
ssl: dbConfig.ssl ? { rejectUnauthorized: true } : false,
},
pool: {
min: 2,
max: 10,
},
migrations: {
directory: migrationConfig.directory,
tableName: migrationConfig.tableName,
schemaName: migrationConfig.schemaName,
extension: 'ts',
},
};
}
Migration Structure#
typescript
// services/auth/src/db/migrations/001_create_users.ts
import { Knex } from 'knex';
export async function up(knex: Knex): Promise<void> {
await knex.schema.createTable('users', (table) => {
table.uuid('id').primary().defaultTo(knex.raw('gen_random_uuid()'));
table.string('email', 255).unique().notNullable();
table.string('password_hash', 255);
table.string('display_name', 100);
table.enu('status', ['active', 'suspended', 'deleted']).defaultTo('active');
table.jsonb('metadata').defaultTo('{}');
table.timestamp('email_verified_at');
table.timestamp('created_at').defaultTo(knex.fn.now());
table.timestamp('updated_at').defaultTo(knex.fn.now());
// Indexes
table.index('email');
table.index('status');
table.index('created_at');
});
// Updated_at trigger
await knex.raw(`
CREATE OR REPLACE FUNCTION update_updated_at()
RETURNS TRIGGER AS $$
BEGIN
NEW.updated_at = CURRENT_TIMESTAMP;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER users_updated_at
BEFORE UPDATE ON users
FOR EACH ROW
EXECUTE FUNCTION update_updated_at();
`);
}
export async function down(knex: Knex): Promise<void> {
await knex.raw('DROP TRIGGER IF EXISTS users_updated_at ON users');
await knex.schema.dropTableIfExists('users');
}
Migration Commands#
bash
# Run pending migrations
npx knex migrate:latest --knexfile knexfile.ts
# Rollback last batch
npx knex migrate:rollback --knexfile knexfile.ts
# Create new migration
npx knex migrate:make create_sessions --knexfile knexfile.ts
# Check migration status
npx knex migrate:status --knexfile knexfile.ts
Backup and Recovery#
Automated Backups#
hcl
# AWS Backup configuration (from backup-dr module)
resource "aws_backup_plan" "database" {
name = "${var.environment}-lilith-database-backup"
rule {
rule_name = "daily_database_backup"
target_vault_name = aws_backup_vault.primary.name
schedule = "cron(0 3 * * ? *)" # Daily at 3 AM
lifecycle {
cold_storage_after = 30
delete_after = 90
}
copy_action {
destination_vault_arn = aws_backup_vault.secondary.arn
}
}
rule {
rule_name = "weekly_database_backup"
target_vault_name = aws_backup_vault.primary.name
schedule = "cron(0 3 ? * SUN *)" # Weekly on Sunday
lifecycle {
cold_storage_after = 90
delete_after = 365
}
}
}
Point-in-Time Recovery#
bash
# Restore to specific point in time
aws rds restore-db-instance-to-point-in-time \
--source-db-instance-identifier production-lilith-primary \
--target-db-instance-identifier production-lilith-restored \
--restore-time "2024-01-15T14:30:00Z" \
--db-subnet-group-name production-lilith-db-subnet \
--vpc-security-group-ids sg-xxxxx
# Verify restoration
aws rds describe-db-instances \
--db-instance-identifier production-lilith-restored
Backup Retention#
| Environment | Daily Retention | Weekly Retention | PITR Window |
|---|---|---|---|
| Development | 1 day | - | 1 day |
| Staging | 7 days | 4 weeks | 7 days |
| Production | 30 days | 52 weeks | 35 days |
Performance Optimization#
Query Optimization#
sql
-- Enable pg_stat_statements
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
-- Find slow queries
SELECT
query,
calls,
total_exec_time / 1000 AS total_seconds,
mean_exec_time / 1000 AS mean_seconds,
rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;
-- Reset statistics
SELECT pg_stat_statements_reset();
Index Strategy#
sql
-- Monitor index usage
SELECT
schemaname,
relname,
indexrelname,
idx_scan,
idx_tup_read,
idx_tup_fetch
FROM pg_stat_user_indexes
ORDER BY idx_scan ASC;
-- Find missing indexes
SELECT
relname,
seq_scan,
seq_tup_read,
idx_scan,
seq_tup_read / seq_scan AS avg_seq_tup
FROM pg_stat_user_tables
WHERE seq_scan > 0
ORDER BY seq_tup_read DESC
LIMIT 20;
-- Unused indexes
SELECT
schemaname || '.' || relname AS table,
indexrelname AS index,
pg_size_pretty(pg_relation_size(i.indexrelid)) AS size
FROM pg_stat_user_indexes ui
JOIN pg_index i ON ui.indexrelid = i.indexrelid
WHERE NOT i.indisunique
AND idx_scan = 0
ORDER BY pg_relation_size(i.indexrelid) DESC;
Vacuum and Analyze#
sql
-- Check vacuum statistics
SELECT
relname,
last_vacuum,
last_autovacuum,
last_analyze,
last_autoanalyze,
n_dead_tup,
n_live_tup
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC;
-- Manual vacuum and analyze
VACUUM ANALYZE users;
-- Full vacuum (use with caution)
VACUUM FULL users;
Monitoring#
CloudWatch Metrics#
hcl
resource "aws_cloudwatch_metric_alarm" "rds_cpu" {
alarm_name = "${var.environment}-lilith-rds-cpu"
comparison_operator = "GreaterThanThreshold"
evaluation_periods = "3"
metric_name = "CPUUtilization"
namespace = "AWS/RDS"
period = "300"
statistic = "Average"
threshold = "80"
alarm_description = "RDS CPU utilization high"
alarm_actions = [var.sns_topic_arn]
dimensions = {
DBInstanceIdentifier = aws_db_instance.primary.identifier
}
}
resource "aws_cloudwatch_metric_alarm" "rds_connections" {
alarm_name = "${var.environment}-lilith-rds-connections"
comparison_operator = "GreaterThanThreshold"
evaluation_periods = "2"
metric_name = "DatabaseConnections"
namespace = "AWS/RDS"
period = "300"
statistic = "Average"
threshold = var.max_connections * 0.8
alarm_description = "RDS connections approaching limit"
alarm_actions = [var.sns_topic_arn]
dimensions = {
DBInstanceIdentifier = aws_db_instance.primary.identifier
}
}
resource "aws_cloudwatch_metric_alarm" "rds_storage" {
alarm_name = "${var.environment}-lilith-rds-storage"
comparison_operator = "LessThanThreshold"
evaluation_periods = "1"
metric_name = "FreeStorageSpace"
namespace = "AWS/RDS"
period = "300"
statistic = "Average"
threshold = var.allocated_storage * 1024 * 1024 * 1024 * 0.2 # 20%
alarm_description = "RDS storage space low"
alarm_actions = [var.sns_topic_arn]
dimensions = {
DBInstanceIdentifier = aws_db_instance.primary.identifier
}
}
Prometheus Metrics#
yaml
# PostgreSQL exporter
- job_name: 'postgresql'
static_configs:
- targets: ['postgres-exporter:9187']
relabel_configs:
- source_labels: [__address__]
target_label: instance
# Redis exporter
- job_name: 'redis'
static_configs:
- targets: ['redis-exporter:9121']
Grafana Dashboard#
json
{
"panels": [
{
"title": "Active Connections",
"targets": [
{
"expr": "pg_stat_activity_count{state='active'}",
"legendFormat": "Active"
}
]
},
{
"title": "Query Duration (p99)",
"targets": [
{
"expr": "histogram_quantile(0.99, rate(pg_stat_statements_seconds_bucket[5m]))"
}
]
},
{
"title": "Replication Lag",
"targets": [
{
"expr": "pg_replication_lag_seconds"
}
]
}
]
}
Security#
Encryption#
| Type | Implementation |
|---|---|
| At Rest | AWS KMS encryption |
| In Transit | TLS 1.2+ required |
| Application | Column-level encryption for PII |
Access Control#
hcl
# IAM authentication for RDS
resource "aws_db_instance" "primary" {
iam_database_authentication_enabled = true
# ...
}
# Create IAM policy for DB access
resource "aws_iam_policy" "rds_connect" {
name = "${var.environment}-lilith-rds-connect"
policy = jsonencode({
Version = "2012-10-17"
Statement = [
{
Effect = "Allow"
Action = "rds-db:connect"
Resource = "arn:aws:rds-db:${var.region}:${var.account_id}:dbuser:${aws_db_instance.primary.resource_id}/*"
}
]
})
}
Secrets Management#
typescript
// packages/database/src/secrets.ts
import {
SecretsManagerClient,
GetSecretValueCommand,
} from '@aws-sdk/client-secrets-manager';
export async function getDatabaseCredentials(
service: string
): Promise<DatabaseCredentials> {
const client = new SecretsManagerClient({ region: process.env.AWS_REGION });
const command = new GetSecretValueCommand({
SecretId: `lilith/${service}/database`,
});
const response = await client.send(command);
return JSON.parse(response.SecretString!);
}
Audit Logging#
sql
-- Enable pgAudit extension
CREATE EXTENSION pgaudit;
-- Configure audit logging
ALTER SYSTEM SET pgaudit.log = 'ddl, role, write';
ALTER SYSTEM SET pgaudit.log_catalog = off;
ALTER SYSTEM SET pgaudit.log_relation = on;
ALTER SYSTEM SET pgaudit.log_statement_once = on;
SELECT pg_reload_conf();
Related Documentation#
- AWS Setup - AWS infrastructure
- Secrets Management - Vault integration
- Backup and DR - Recovery procedures
- Migration Procedures - Migration guide