CAIRL Database Schema

Overview

CAIRL uses PostgreSQL as the primary database with Drizzle ORM for type-safe database operations. All sensitive data is encrypted at rest, and the schema is designed with privacy-first principles.

Core Tables

waitlist

Early adopters and launch waitlist.

{
  id: uuid (PK)
  email: varchar(255) UNIQUE NOT NULL
  createdAt: timestamp NOT NULL
  source: varchar(50) DEFAULT 'coming_soon'
  metadata: jsonb
}

Purpose: Collect email addresses for launch notifications

Privacy: Email addresses are the only PII collected, used solely for launch communications

users

Primary user identity table.

{
  id: uuid (PK)
  email: varchar(255) UNIQUE NOT NULL
  phoneNumber: varchar(20)
  emailVerified: boolean DEFAULT false
  phoneVerified: boolean DEFAULT false

  // Profile
  firstName: varchar(100)
  lastName: varchar(100)
  dateOfBirth: timestamp

  // KYC Status
  kycStatus: varchar(50) DEFAULT 'pending' // pending, verified, rejected
  kycVerifiedAt: timestamp

  // Security
  passwordHash: text
  twoFactorEnabled: boolean DEFAULT false

  // Timestamps
  createdAt: timestamp NOT NULL
  updatedAt: timestamp NOT NULL
  lastLoginAt: timestamp
}

Purpose: Core user identity and authentication

Privacy:

• passwordHash uses bcrypt with appropriate cost
• phoneNumber stored encrypted
• Name fields optional (for verification only)
• dateOfBirth used for age verification, stored encrypted

Indexes:

• email (unique)
• createdAt (for analytics)

identity_documents

Encrypted storage references for identity documents.

{
  id: uuid (PK)
  userId: uuid FK -> users.id

  // Document Details
  documentType: varchar(50) NOT NULL // passport, drivers_license, national_id
  documentNumber: varchar(100) // encrypted
  issuingCountry: varchar(2) // ISO country code
  expiryDate: timestamp

  // Storage References (S3)
  frontImageUrl: text // encrypted S3 path
  backImageUrl: text // encrypted S3 path
  selfieUrl: text // encrypted S3 path

  // Verification
  verificationStatus: varchar(50) DEFAULT 'pending'
  verifiedAt: timestamp
  verifiedBy: varchar(50) // system, manual, third_party

  // Timestamps
  createdAt: timestamp NOT NULL
  updatedAt: timestamp NOT NULL
}

Purpose: Track identity document submissions and verification status

Privacy:

• Document images stored in S3 with encryption at rest (AES-256)
• S3 paths encrypted in database
• documentNumber encrypted (only decrypted for verification)
• Documents automatically deleted after verification (configurable retention)

Security:

• Access logged in audit_log
• Viewing requires MFA
• Download restricted to authorized personnel

Indexes:

• userId
• verificationStatus

verification_sessions

Track verification attempts and codes.

{
  id: uuid (PK)
  userId: uuid FK -> users.id

  // Session Details
  sessionType: varchar(50) NOT NULL // email, phone, document, biometric
  status: varchar(50) DEFAULT 'pending' // pending, completed, failed, expired

  // Verification Data
  verificationCode: varchar(10) // hashed
  codeExpiresAt: timestamp
  attempts: integer DEFAULT 0
  maxAttempts: integer DEFAULT 3

  // Context
  ipAddress: varchar(50) // for fraud detection
  userAgent: text
  metadata: jsonb

  // Timestamps
  createdAt: timestamp NOT NULL
  completedAt: timestamp
  expiresAt: timestamp
}

Purpose: Manage verification flows (email, phone, document)

Security:

• verificationCode hashed before storage
• Rate limiting by IP address
• Automatic expiration (5-15 minutes depending on type)
• Max attempts lockout

Indexes:

• userId
• status
• createdAt (for cleanup)

consent_records

Track user consent for data processing and sharing.

{
  id: uuid (PK)
  userId: uuid FK -> users.id

  // Consent Details
  consentType: varchar(100) NOT NULL
  consentGiven: boolean NOT NULL
  consentVersion: varchar(20) NOT NULL

  // Context
  ipAddress: varchar(50)
  userAgent: text

  // Timestamps
  createdAt: timestamp NOT NULL
  revokedAt: timestamp
}

Purpose: GDPR compliance and consent management

Consent Types:

• data_processing: General data processing
• identity_verification: ID document processing
• data_sharing: Sharing verified identity
• marketing: Marketing communications
• analytics: Usage analytics

Privacy:

• Consent required before any data processing
• Granular consent types
• Easy revocation
• Audit trail of all consent changes

Indexes:

• userId, consentType
• createdAt

audit_log

Comprehensive audit trail of all sensitive operations.

{
  id: uuid (PK)
  userId: uuid FK -> users.id (nullable)

  // Event Details
  eventType: varchar(100) NOT NULL
  eventCategory: varchar(50) NOT NULL
  description: text

  // Context
  ipAddress: varchar(50)
  userAgent: text
  metadata: jsonb

  // Timestamp
  createdAt: timestamp NOT NULL
}

Purpose: Security auditing, compliance, debugging

Event Categories:

• auth: Login, logout, password changes
• verification: Verification attempts and completions
• data_access: Views of sensitive data
• data_modification: Updates to user data
• consent: Consent changes
• admin: Administrative actions

Privacy:

• No PII in logs (reference IDs only)
• Logs retained for 90 days
• Access restricted to authorized personnel

Indexes:

• userId
• eventCategory
• createdAt (for time-range queries)

Data Relationships

users (1) -> (n) identity_documents
users (1) -> (n) verification_sessions
users (1) -> (n) consent_records
users (1) -> (n) audit_log

Data Lifecycle

User Registration

1. Create user record with email
2. Create consent record for data processing
3. Send email verification (creates verification_session)
4. Log in audit_log

Identity Verification

1. User uploads document (stored in S3)
2. Create identity_documents record
3. Create verification_session for document review
4. Admin/system reviews document
5. Update verification status
6. Log all steps in audit_log

Data Deletion (GDPR Right to Erasure)

1. User requests deletion
2. Soft delete user account (mark as deleted)
3. Schedule hard delete after grace period (30 days)
4. Delete S3 documents
5. Anonymize audit logs (remove userId reference)
6. Permanently delete all records
7. Send confirmation email

Security Measures

Encryption

At Rest:

• Database: PostgreSQL encryption (AWS RDS)
• S3 Documents: AES-256 encryption
• Sensitive fields: Application-level encryption

In Transit:

• TLS 1.3 for all connections
• Certificate pinning for mobile apps

Access Control

Database:

• Least privilege principle
• Application uses limited permissions account
• Admin access requires MFA
• Read replicas for analytics (no PII)

Application:

• Row-level security for multi-tenancy (future)
• API authentication required
• Rate limiting on all endpoints

Backup & Recovery

Backups:

• Automated daily backups (encrypted)
• Point-in-time recovery (7 days)
• Offsite backup storage
• Quarterly recovery drills

Retention:

• Production backups: 30 days
• Compliance backups: 7 years (anonymized)

Performance

Indexing Strategy

• Primary keys: UUID v4
• Foreign keys: All indexed
• Query patterns: Indexes on common filters
• Composite indexes for complex queries

Query Optimization

• Use Drizzle ORM prepared statements
• Connection pooling (max 10 connections)
• Read replicas for heavy queries
• Caching for frequently accessed data

Monitoring

• Slow query log (> 500ms)
• Connection pool metrics
• Table size monitoring
• Index usage analysis

Migration Strategy

Schema Changes

1. Write migration in Drizzle Kit
2. Test on staging environment
3. Review for backward compatibility
4. Deploy during maintenance window
5. Monitor for issues
6. Rollback plan ready

Data Migrations

1. Create backup before migration
2. Test migration on backup data
3. Run migration with progress tracking
4. Verify data integrity
5. Update application code if needed

Compliance

GDPR

• ✅ Data minimization
• ✅ Purpose limitation
• ✅ Storage limitation
• ✅ Right to access (export user data)
• ✅ Right to erasure (deletion process)
• ✅ Right to rectification (update endpoints)
• ✅ Data portability (JSON export)
• ✅ Consent management

SOC 2

• ✅ Access controls documented
• ✅ Audit logging comprehensive
• ✅ Encryption at rest and in transit
• ✅ Backup and recovery procedures
• ✅ Change management process

Last Updated: 2026-01-23 Version: 1.0 Status: Active Schema Location: /src/db/schema.ts