# Database Architecture

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

## Table of Contents

- [Overview](#overview)
- [PostgreSQL (RDS)](#postgresql-rds)
- [Redis (ElastiCache)](#redis-elasticache)
- [Database Per Service](#database-per-service)
- [Connection Management](#connection-management)
- [Migrations](#migrations)
- [Backup and Recovery](#backup-and-recovery)
- [Performance Optimization](#performance-optimization)
- [Monitoring](#monitoring)
- [Security](#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

```
┌─────────────────────────────────────────────────────────────────────────┐
│                       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

```
┌─────────────────────────────────────────────────────────────────────────┐
│                    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-setup.md) - AWS infrastructure
- [Secrets Management](./secrets-management.md) - Vault integration
- [Backup and DR](../runbooks/procedures/backup-recovery.md) - Recovery
  procedures
- [Migration Procedures](../database/migrations.md) - Migration guide
