Eve library

Database Architecture

Guide

Revision 1

On this page

Comprehensive guide for database infrastructure, design patterns, and operations in the Lilith platform.

Table of Contents#

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();