Database Schema & Management¶

Complete guide to the database architecture, models, and management operations.

🏗️ Database Architecture¶

Technology Stack¶

Database: PostgreSQL 14+
ORM: Prisma 6.x
Migrations: Prisma Migrate
Connection Pooling: Built-in Prisma connection pool

Connection Configuration¶

// Database URL format
DATABASE_URL="postgresql://user:password@host:port/database"

// Example configurations
// Development
DATABASE_URL="postgresql://dev:dev123@localhost:5432/engage_dev"

// Production
DATABASE_URL="postgresql://prod_user:secure_pass@db.cyferwall.com:5432/engage_prod"

📊 Schema Overview¶

Core Tables¶

erDiagram
    User {
        string id PK
        string email UK
        string name
        string hashedPassword
        UserRole role
        datetime createdAt
        datetime updatedAt
    }

    SupportCase {
        string id PK
        string caseNumber UK
        string customerName
        string email
        CustomerType customerType
        SupportCaseType issueType
        SupportCaseUrgency urgency
        string subject
        text message
        SupportCaseStatus status
        string assignedStaffId FK
        datetime createdAt
        datetime updatedAt
    }

    CaseUpdate {
        string id PK
        string caseId FK
        string updatedBy FK
        string updateType
        text message
        datetime createdAt
    }

    NotificationLog {
        string id PK
        string caseId FK
        string templateId
        string channel
        string recipient
        boolean success
        text errorMessage
        datetime createdAt
    }

    User ||--o{ SupportCase : "assigns"
    SupportCase ||--o{ CaseUpdate : "has"
    SupportCase ||--o{ NotificationLog : "generates"

🗃️ Data Models¶

User Model¶

model User {
  id              String   @id @default(cuid())
  email           String   @unique
  name            String
  hashedPassword  String?
  role            UserRole @default(CUSTOMER)
  createdAt       DateTime @default(now())
  updatedAt       DateTime @updatedAt

  // Relations
  assignedCases   SupportCase[]
  caseUpdates     CaseUpdate[]

  @@map("users")
}

enum UserRole {
  CUSTOMER
  STAFF
  SENIOR_STAFF
  ADMIN
  SUPER_ADMIN
}

Support Case Model¶

model SupportCase {
  id                    String              @id @default(cuid())
  caseNumber           String              @unique
  customerName         String
  email                String
  organization         String?
  phone                String?
  customerType         CustomerType
  issueType            SupportCaseType
  urgency              SupportCaseUrgency
  clientOrProjectName  String?
  subject              String
  message              String
  status               SupportCaseStatus   @default(OPEN)
  assignedStaffId      String?
  estimatedResolution  String?
  createdAt            DateTime            @default(now())
  updatedAt            DateTime            @updatedAt

  // Relations
  assignedStaff        User?               @relation(fields: [assignedStaffId], references: [id])
  updates              CaseUpdate[]
  notifications        NotificationLog[]

  @@map("support_cases")
}

enum CustomerType {
  INDIVIDUAL
  STARTUP
  ENTERPRISE
  ENTERPRISE_PLUS
}

enum SupportCaseType {
  TECHNICAL
  BILLING
  GENERAL
  BUG_REPORT
  FEATURE_REQUEST
}

enum SupportCaseUrgency {
  LOW
  MEDIUM
  HIGH
  CRITICAL
}

enum SupportCaseStatus {
  OPEN
  IN_PROGRESS
  WAITING_FOR_CUSTOMER
  ESCALATED
  RESOLVED
  CLOSED
  CANCELLED
}

Case Update Model¶

model CaseUpdate {
  id          String      @id @default(cuid())
  caseId      String
  updatedBy   String
  updateType  String      // STATUS_CHANGE, COMMENT, ESCALATION, etc.
  message     String?
  previousValue String?
  newValue    String?
  createdAt   DateTime    @default(now())

  // Relations
  case        SupportCase @relation(fields: [caseId], references: [id])
  user        User        @relation(fields: [updatedBy], references: [id])

  @@map("case_updates")
}

Notification Log Model¶

model NotificationLog {
  id           String      @id @default(cuid())
  caseId       String?
  templateId   String
  channel      String      // EMAIL, SMS, WEBHOOK
  recipient    String
  success      Boolean
  errorMessage String?
  metadata     Json?
  createdAt    DateTime    @default(now())

  // Relations  
  case         SupportCase? @relation(fields: [caseId], references: [id])

  @@map("notification_logs")
}

🔄 Database Operations¶

Migrations¶

# Create a new migration
npx prisma migrate dev --name add_new_feature

# Apply pending migrations
npx prisma migrate deploy

# Reset database (development only)
npx prisma migrate reset

# Check migration status
npx prisma migrate status

Schema Generation¶

# Generate Prisma client
npx prisma generate

# Push schema changes (development)
npx prisma db push

# Pull schema from database
npx prisma db pull

Database Seeding¶

# Run seed script
npx prisma db seed

Example seed file:

// prisma/seed.ts
import { PrismaClient } from '@prisma/client'

const prisma = new PrismaClient()

async function main() {
  // Create admin user
  const admin = await prisma.user.create({
    data: {
      email: 'admin@cyferwall.com',
      name: 'System Administrator',
      role: 'SUPER_ADMIN'
    }
  })

  // Create sample support cases
  await prisma.supportCase.createMany({
    data: [
      {
        caseNumber: 'CW-2025-001',
        customerName: 'John Doe',
        email: 'john@example.com',
        customerType: 'ENTERPRISE',
        issueType: 'TECHNICAL',
        urgency: 'HIGH',
        subject: 'API Integration Issues',
        message: 'Having trouble with webhook delivery...'
      }
    ]
  })
}

main()
  .then(() => prisma.$disconnect())
  .catch(e => {
    console.error(e)
    prisma.$disconnect()
    process.exit(1)
  })

📈 Performance Optimization¶

Indexing Strategy¶

-- Performance indexes
CREATE INDEX idx_support_cases_status ON support_cases(status);
CREATE INDEX idx_support_cases_urgency ON support_cases(urgency);
CREATE INDEX idx_support_cases_created_at ON support_cases(created_at);
CREATE INDEX idx_support_cases_case_number ON support_cases(case_number);
CREATE INDEX idx_notification_logs_case_id ON notification_logs(case_id);
CREATE INDEX idx_case_updates_case_id ON case_updates(case_id);

Connection Pooling¶

// Prisma connection configuration
const prisma = new PrismaClient({
  datasources: {
    db: {
      url: process.env.DATABASE_URL
    }
  },
  // Connection pool settings
  __internal: {
    engine: {
      connectionLimit: 20,
      maxWait: 5000,
      timeout: 10000,
      retryAttempts: 3
    }
  }
})

Query Optimization¶

// Efficient queries with proper relations
const casesWithUpdates = await prisma.supportCase.findMany({
  where: {
    status: 'OPEN',
    urgency: 'CRITICAL'
  },
  include: {
    assignedStaff: {
      select: {
        id: true,
        name: true,
        email: true
      }
    },
    updates: {
      orderBy: {
        createdAt: 'desc'
      },
      take: 5
    }
  },
  orderBy: {
    createdAt: 'desc'
  }
})

🔒 Data Security¶

Encryption at Rest¶

Database encryption enabled
Encrypted backups
Column-level encryption for sensitive data

Access Control¶

-- Database user permissions
GRANT SELECT, INSERT, UPDATE ON support_cases TO app_user;
GRANT SELECT, INSERT ON notification_logs TO app_user;
GRANT ALL PRIVILEGES ON ALL TABLES TO admin_user;

Audit Trail¶

// Audit log model
model AuditLog {
  id        String   @id @default(cuid())
  userId    String
  action    String   // CREATE, UPDATE, DELETE
  table     String
  recordId  String
  oldValues Json?
  newValues Json?
  createdAt DateTime @default(now())

  @@map("audit_logs")
}

🔄 Backup & Recovery¶

Automated Backups¶

# Daily backup script
#!/bin/bash
pg_dump $DATABASE_URL > backup_$(date +%Y%m%d).sql
aws s3 cp backup_$(date +%Y%m%d).sql s3://cyferwall-backups/

Point-in-Time Recovery¶

-- Create restore point
SELECT pg_create_restore_point('before_major_update');

-- Restore from point-in-time
pg_basebackup -D /backup -Ft -z -P

Disaster Recovery¶

RTO (Recovery Time Objective): 4 hours
RPO (Recovery Point Objective): 1 hour
Geographic replication: Multi-region setup
Automated failover: Database cluster setup

📊 Monitoring & Analytics¶

Database Metrics¶

// Health check query
const healthCheck = await prisma.$queryRaw`
  SELECT 
    count(*) as total_cases,
    count(*) FILTER (WHERE status = 'OPEN') as open_cases,
    count(*) FILTER (WHERE urgency = 'CRITICAL') as critical_cases
  FROM support_cases
`

Slow Query Monitoring¶

-- Enable query logging
ALTER SYSTEM SET log_statement = 'all';
ALTER SYSTEM SET log_min_duration_statement = 1000; -- Log queries > 1s

DataDog Integration¶

import { metrics } from 'datadog-metrics'

// Database performance metrics
metrics.gauge('database.connection_count', connectionCount)
metrics.histogram('database.query_duration', queryDuration)
metrics.increment('database.query_count', 1, ['table:support_cases'])

🛠️ Development Tools¶

Database Studio¶

# Launch Prisma Studio
npx prisma studio

Query Analysis¶

-- Analyze query performance
EXPLAIN ANALYZE SELECT * FROM support_cases WHERE status = 'OPEN';

-- Check index usage
SELECT schemaname, tablename, indexname, idx_tup_read, idx_tup_fetch 
FROM pg_stat_user_indexes;

Development Utilities¶

// Database utilities
export const dbUtils = {
  // Generate case number
  generateCaseNumber: () => {
    const date = new Date().toISOString().slice(0, 4)
    const random = Math.floor(Math.random() * 900000) + 100000
    return `CW-${date}-${random}`
  },

  // Cleanup old notifications
  cleanupOldNotifications: async (daysOld = 30) => {
    const cutoffDate = new Date()
    cutoffDate.setDate(cutoffDate.getDate() - daysOld)

    return prisma.notificationLog.deleteMany({
      where: {
        createdAt: {
          lt: cutoffDate
        }
      }
    })
  }
}

🚨 Troubleshooting¶

Common Issues¶

Connection Pool Exhaustion¶

// Monitor connection pool
const poolStatus = await prisma.$metrics.json()
console.log('Pool size:', poolStatus.counters.pool_connections_open)

Slow Queries¶

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

Migration Failures¶

# Resolve migration conflicts
npx prisma migrate resolve --applied 20250123000000_initial

# Force reset (development only)
npx prisma migrate reset --force

📊 Database performance and reliability are critical for the entire system. Monitor closely and optimize regularly.