Database Configuration

Database Architecture

Database Configuration

Primary Database

database:
  primary:
    type: postgresql
    host: oan-db-primary.subnet-private.vcn.oraclevcn.com
    port: 5432
    database: oan_production
    schema: public
    ssl: true
    
    pool:
      min: 5
      max: 20
      idle_timeout: 10000
      
    monitoring:
      enabled: true
      metrics_collection: true
      slow_query_log: true

Read Replica

database:
  replica:
    type: postgresql
    host: oan-db-replica.subnet-private.vcn.oraclevcn.com
    port: 5432
    database: oan_production
    schema: public
    ssl: true
    
    pool:
      min: 2
      max: 10
      idle_timeout: 10000

Connection Management

Connection Configuration

// TypeORM Configuration
const dbConfig: TypeOrmModuleOptions = {
    type: 'postgres',
    host: process.env.DB_HOST,
    port: parseInt(process.env.DB_PORT),
    username: process.env.DB_USERNAME,
    password: process.env.DB_PASSWORD,
    database: process.env.DB_DATABASE,
    
    entities: ['dist/**/*.entity{.ts,.js}'],
    migrations: ['dist/migrations/*{.ts,.js}'],
    
    ssl: {
        rejectUnauthorized: false,
        ca: fs.readFileSync('/path/to/ca.crt'),
    },
    
    // Connection Pool Settings
    extra: {
        max: 20,
        min: 5,
        idleTimeoutMillis: 10000,
        connectionTimeoutMillis: 2000,
    },
    
    // Logging and Debugging
    logging: ['error', 'warn', 'schema'],
    logger: 'advanced-console',
};

Schema Management

Core Tables

-- Users Table
CREATE TABLE users (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    email VARCHAR(255) UNIQUE NOT NULL,
    full_name VARCHAR(255) NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- Loans Table
CREATE TABLE loans (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    user_id UUID REFERENCES users(id),
    amount DECIMAL(15,2) NOT NULL,
    term_months INTEGER NOT NULL,
    status VARCHAR(50) NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- Payments Table
CREATE TABLE payments (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    loan_id UUID REFERENCES loans(id),
    amount DECIMAL(15,2) NOT NULL,
    status VARCHAR(50) NOT NULL,
    payment_date TIMESTAMP WITH TIME ZONE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

Cache Configuration

Redis Configuration

redis:
  primary:
    host: oan-redis-primary.subnet-private.vcn.oraclevcn.com
    port: 6379
    password: ${REDIS_PASSWORD}
    
    cluster:
      enabled: true
      nodes: 3
      
    ssl:
      enabled: true
      cert_path: /path/to/redis.crt
      
    monitoring:
      metrics_enabled: true
      slow_log_enabled: true

Backup Configuration

Backup Strategy

backup:
  full:
    frequency: daily
    retention: 30d
    time: '00:00'
    
  incremental:
    frequency: hourly
    retention: 7d
    
  archive:
    type: object_storage
    bucket: oan-db-backups
    retention: 365d

Monitoring Setup

Monitoring Configuration

monitoring:
  metrics:
    collection_interval: 60s
    retention_period: 30d
    
  alerts:
    cpu_threshold: 80
    memory_threshold: 85
    connection_threshold: 90
    
  logging:
    slow_query_threshold: 1s
    error_logging: true
    audit_logging: true

Best Practices

Performance

1. Connection pooling
2. Query optimization
3. Index management
4. Cache strategy

Security

1. Encryption at rest
2. SSL connections
3. Access control
4. Audit logging

Maintenance

1. Regular backups
2. Health checks
3. Performance monitoring
4. Schema updates

High Availability

1. Replication setup
2. Failover configuration
3. Load balancing
4. Disaster recovery