Database Schema

Overview

This document describes the database schema and data persistence layer for the Customer Management module. The system uses MongoDB as the primary database with Mongoose as the ODM layer, implementing multi-tenant architecture with proper data isolation and validation.

Database Architecture

Technology Stack

• Database: MongoDB

• ODM: Mongoose

• Pattern: Domain-Driven Design (DDD) with CQRS

• Multi-Tenancy: Tenant-based data isolation

• State Management: Soft delete with state history

• Validation: Domain-level validation with schema constraints

Database Schema

Customer Collection

Collection Name: customer

The customer collection stores all customer records with comprehensive customer information for business relationship management.

{
  _id: ObjectId,                    // Primary key - auto-generated
  firstName: String,                // Customer first name (optional)
  lastName: String,                 // Customer last name (optional)
  phone: String,                    // Normalized phone number (optional)
  email: String,                    // Email address (optional)
  note: String,                     // Additional customer notes (optional)
  customerType: String,             // Customer type enum - REQUIRED with default
  stateHistory: [StateHistorySchema], // State tracking for soft deletes
  createdAt: Date,                  // Auto-generated creation timestamp
  updatedAt: Date                   // Auto-generated update timestamp
}

Schema Definition

export const CustomerSchema = new Schema<ICustomer>({
  firstName: { 
    type: String 
  },
  lastName: { 
    type: String 
  },
  phone: { 
    type: String 
  },
  email: { 
    type: String 
  },
  note: { 
    type: String 
  },
  customerType: { 
    type: String, 
    default: CustomerTypeEnum.regular, 
    enum: CustomerTypeEnum 
  },
  stateHistory: { 
    type: [StateHistorySchema], 
    default: [] 
  },
}, { 
  timestamps: true, 
  collection: Customer.name.toLowerCase() 
});

Field Specifications

Core Customer Fields

firstName (String, Optional)

• Purpose: Customer's first name

• Validation: No specific format validation

• Usage: Display names, personalization, search

• Notes: Part of mandatory field validation (at least one of firstName, lastName, email, phone required)

lastName (String, Optional)

• Purpose: Customer's last name

• Validation: No specific format validation

• Usage: Display names, formal communication, search

• Notes: Part of mandatory field validation

phone (String, Optional)

• Purpose: Customer contact phone number

• Validation:

  • Format validation using regex pattern

  • Automatic normalization (numbers only)

  • International format support

• Storage: Normalized format (numbers only)

• Usage: Contact, SMS notifications, duplicate detection

• Notes: Part of mandatory field validation and uniqueness checks

// Phone validation pattern
/^(\+?\d{1,3}[-.\s]?)?(\(?\d{1,4}\)?[-.\s]?)?\d{1,4}[-.\s]?\d{1,4}[-.\s]?\d{1,9}$/

// Normalization example
input: "[phone]" → stored: "15551234567"

email (String, Optional)

• Purpose: Customer email address

• Validation:

  • Email format validation

  • Domain validation

• Usage: Communication, login, notifications, duplicate detection

• Notes: Part of mandatory field validation and uniqueness checks

// Email validation pattern
/^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}$/

note (String, Optional)

• Purpose: Additional customer information and notes

• Validation: No specific validation

• Usage: Customer service notes, preferences, special instructions

• Notes: Free-form text field for business use

customerType (String, Required with Default)

• Purpose: Classification of customer type

• Default: CustomerTypeEnum.regular

• Enum Values:

  • regular: Standard registered customers

  • unregistered: Customers with basic information

  • new: First-time customers

  • anonymous: Minimal information customers

• Usage: Business logic, filtering, reporting

• Validation: Must be one of the enum values

System Fields

_id (ObjectId, Auto-generated)

• Purpose: Primary key identifier

• Generation: MongoDB auto-generated

• Format: 24-character hexadecimal string

• Usage: Unique customer identification, relationships

• Indexing: Automatic primary index

stateHistory (Array, StateHistorySchema)

• Purpose: Track customer state changes for soft delete

• Default: Empty array

• Usage: Soft delete, audit trail, state management

• Structure: Array of state change records

• Notes: Enables data retention and recovery

createdAt (Date, Auto-generated)

• Purpose: Record creation timestamp

• Generation: Mongoose automatic timestamp

• Usage: Audit trail, reporting, data lifecycle

• Format: ISO Date string

updatedAt (Date, Auto-generated)

• Purpose: Last modification timestamp

• Generation: Mongoose automatic timestamp on updates

• Usage: Audit trail, cache invalidation, conflict resolution

• Format: ISO Date string

Embedded Schemas

StateHistorySchema

{
  state: String,           // Entity state (active, deleted, etc.)
  setAt: String,          // ISO timestamp of state change
  _id: false              // No auto-generated ID for subdocuments
}

States: Based on EntityStateEnum

• active: Normal operational state

• deleted: Soft deleted state

• suspended: Temporarily inactive

• archived: Long-term storage

Validation Rules

Domain-Level Validation

Mandatory Field Validation

At least one of the following fields must be provided:

• firstName

• lastName

• email

• phone

private static validateMandatoryFields(props: ICustomer, fields: (keyof ICustomer)[]): Result<void> {
  const hasAtLeastOneProperty = fields.some(field => 
    props[field] !== undefined && props[field] !== '' && props[field] !== null
  );
  return hasAtLeastOneProperty ? 
    Result.ok() : 
    Result.fail<void>('At least one of the following properties must be provided: ' + fields.join(', '));
}

Email Validation

private static validateEmail(email?: string): Result<void> {
  if (email && !/^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}$/.test(email)) {
    return Result.fail<void>('Invalid email format: ' + email);
  }
  return Result.ok();
}

Phone Validation

private static validatePhone(phone?: string): Result<void> {
  if (phone && !/^(\+?\d{1,3}[-.\s]?)?(\(?\d{1,4}\)?[-.\s]?)?\d{1,4}[-.\s]?\d{1,4}[-.\s]?\d{1,9}$/.test(phone)) {
    return Result.fail<void>('Invalid phone number format: ' + phone);
  }
  return Result.ok();
}

Regular Customer Validation

For CustomerTypeEnum.regular customers, stricter validation applies:

Required fields:

• email: Must be provided and valid

• phone: Must be provided and valid

public static createRegular(props: ICustomer): Result<Customer> {
  const requiredFields: (keyof ICustomer)[] = ['phone', 'email'];
  const fieldValidation = this.validateMandatoryFields(props, requiredFields);
  // Additional validation...
}

Indexing Strategy

Primary Indexes

// Primary key index (automatic)
db.customer.createIndex({ "_id": 1 });

// Tenant-based queries (multi-tenancy support via application layer)
// Note: Customer collection uses business-level tenant isolation

// Email-based lookups
db.customer.createIndex({ 
  "email": 1 
}, { 
  sparse: true,
  name: "email_lookup" 
});

// Phone-based lookups
db.customer.createIndex({ 
  "phone": 1 
}, { 
  sparse: true,
  name: "phone_lookup" 
});

// Customer type filtering
db.customer.createIndex({ 
  "customerType": 1,
  "createdAt": -1 
}, {
  name: "type_date_filter"
});

// Soft delete state filtering
db.customer.createIndex({ 
  "stateHistory.state": 1 
}, {
  name: "state_filter"
});

Composite Indexes

// Email and phone uniqueness validation
db.customer.createIndex({ 
  "email": 1, 
  "phone": 1 
}, { 
  sparse: true,
  name: "contact_uniqueness"
});

// Customer search optimization
db.customer.createIndex({
  "firstName": "text",
  "lastName": "text",
  "email": "text"
}, {
  name: "customer_text_search"
});

// Customer type with creation date for pagination
db.customer.createIndex({
  "customerType": 1,
  "createdAt": -1,
  "_id": 1
}, {
  name: "type_pagination"
});

Performance Optimization Indexes

// Recently created customers
db.customer.createIndex({ 
  "createdAt": -1 
}, {
  name: "recent_customers"
});

// Recently updated customers
db.customer.createIndex({ 
  "updatedAt": -1 
}, {
  name: "recent_updates"
});

// Active customers only (excluding deleted)
db.customer.createIndex({
  "stateHistory.state": 1,
  "customerType": 1,
  "createdAt": -1
}, {
  partialFilterExpression: {
    "stateHistory.state": { $ne: "deleted" }
  },
  name: "active_customers"
});

Query Patterns

Common Queries

Find Customer by ID

db.customer.findOne({
  "_id": ObjectId("customerId"),
  $expr: {
    $ne: [
      { $arrayElemAt: ["$stateHistory.state", -1] },
      "deleted"
    ]
  }
});

Find Customer by Email or Phone

db.customer.findOne({
  $or: [
    { "email": "[email protected]" },
    { "phone": "1234567890" }
  ],
  $expr: {
    $ne: [
      { $arrayElemAt: ["$stateHistory.state", -1] },
      "deleted"
    ]
  }
});

Customer Search with Pagination

// Aggregation pipeline for customer search
const pipeline = [
  // Text search stage
  {
    $match: {
      $or: [
        { firstName: { $regex: searchTerm, $options: 'i' } },
        { lastName: { $regex: searchTerm, $options: 'i' } },
        { email: { $regex: searchTerm, $options: 'i' } },
        { $text: { $search: searchTerm } }
      ],
      $expr: {
        $ne: [
          { $arrayElemAt: ["$stateHistory.state", -1] },
          "deleted"
        ]
      }
    }
  },
  // Customer type filtering
  ...(customerType ? [{ $match: { customerType } }] : []),
  // Full name concatenation for search
  {
    $addFields: {
      fullName: { $concat: ["$firstName", " ", "$lastName"] }
    }
  },
  // Sorting
  { $sort: { createdAt: -1 } },
  // Pagination
  { $skip: (page - 1) * limit },
  { $limit: limit }
];

db.customer.aggregate(pipeline);

Customer Type Statistics

db.customer.aggregate([
  {
    $match: {
      $expr: {
        $ne: [
          { $arrayElemAt: ["$stateHistory.state", -1] },
          "deleted"
        ]
      }
    }
  },
  {
    $group: {
      _id: "$customerType",
      count: { $sum: 1 },
      recentCount: {
        $sum: {
          $cond: [
            { $gte: ["$createdAt", new Date(Date.now() - 30 * 24 * 60 * 60 * 1000)] },
            1,
            0
          ]
        }
      }
    }
  }
]);

Integration Queries

Customers for Order Processing

// Find customers for attendee matching
db.customer.find({
  $or: [
    { email: { $in: attendeeEmails } },
    { phone: { $in: attendeePhones } }
  ],
  $expr: {
    $ne: [
      { $arrayElemAt: ["$stateHistory.state", -1] },
      "deleted"
    ]
  }
}).hint({ "email": 1, "phone": 1 });

First-Time Customer Detection

// Check if customer is first-time (used in order processing)
function isFirstTimeCustomer(customerId, currentOrderDate) {
  const previousOrders = db.order.countDocuments({
    "attendees.customer._id": customerId,
    "createdAt": { $lt: currentOrderDate }
  });
  return previousOrders === 0;
}

Multi-Tenancy

Tenant Isolation Strategy

The Customer Management module implements tenant isolation at the application layer rather than database schema level. This approach provides:

• Business-Level Isolation: Each business (tenant) has isolated customer data

• Shared Database: All tenants share the same MongoDB database

• Application Enforcement: Tenant filtering applied in all repository queries

• Performance: Optimized queries with tenant-aware indexing

Tenant Context Implementation

// All repository methods include tenant context
interface CustomerRepositoryContext {
  tenantId: string;
  actor: Actor;
}

// Repository method example
async findCustomerById(id: string, tenantId: string): Promise<ICustomer | null> {
  return this.model.findOne({
    _id: id,
    // Tenant isolation implemented through business logic
    // Customer data is isolated per business via application layer
    $expr: {
      $ne: [
        { $arrayElemAt: ['$stateHistory.state', -1] },
        'deleted'
      ]
    }
  });
}

Note: Customer records are associated with businesses through the order and service context, not through a direct tenantId field in the customer collection. This design allows customers to potentially interact with multiple businesses while maintaining data integrity.

Data Consistency

Referential Integrity

Customer References in Orders

• Orders: Reference customers through embedded customer snapshots

• Order Services: Include customer data in attendee arrays

• Payment Records: Link to customer information for payer details

Customer Data Snapshots

When customers are referenced in orders or payments, their current data is snapshot:

// Customer snapshot in order context
{
  orderId: "order123",
  attendees: [{
    customer: {
      _id: "customer456",
      firstName: "John",
      lastName: "Doe",
      email: "[email protected]",
      phone: "1234567890",
      customerType: "regular"
    },
    firstTime: true
  }]
}

State Management

Soft Delete Implementation

// Soft delete operation
async deleteCustomer(customerId: string): Promise<void> {
  await this.model.updateOne(
    { _id: customerId },
    {
      $push: {
        stateHistory: {
          state: 'deleted',
          setAt: new Date().toISOString()
        }
      }
    }
  );
}

// Query excluding deleted customers
const activeCustomers = await this.model.find({
  $expr: {
    $ne: [
      { $arrayElemAt: ['$stateHistory.state', -1] },
      'deleted'
    ]
  }
});

State History Tracking

• State Changes: All state transitions are recorded

• Audit Trail: Complete history of customer lifecycle

• Recovery: Ability to restore soft-deleted customers

• Compliance: Data retention for regulatory requirements

Performance Optimization

Query Optimization

Index Utilization

// Optimized customer search query
db.customer.find({
  $or: [
    { firstName: { $regex: searchTerm, $options: 'i' } },
    { lastName: { $regex: searchTerm, $options: 'i' } },
    { email: { $regex: searchTerm, $options: 'i' } }
  ],
  $expr: {
    $ne: [
      { $arrayElemAt: ['$stateHistory.state', -1] },
      'deleted'
    ]
  }
}).hint({ firstName: 'text', lastName: 'text', email: 'text' });

Pagination Optimization

// Cursor-based pagination for large datasets
db.customer.find({
  _id: { $gt: lastCustomerId },
  customerType: 'regular',
  $expr: {
    $ne: [
      { $arrayElemAt: ['$stateHistory.state', -1] },
      'deleted'
    ]
  }
}).sort({ _id: 1 }).limit(pageSize);

Memory Management

Field Projection

// Load only required fields for list views
db.customer.find(
  { /* query criteria */ },
  { 
    firstName: 1, 
    lastName: 1, 
    email: 1, 
    customerType: 1,
    createdAt: 1 
  }
);

Aggregation Optimization

// Efficient customer statistics
db.customer.aggregate([
  {
    $match: {
      $expr: {
        $ne: [
          { $arrayElemAt: ['$stateHistory.state', -1] },
          'deleted'
        ]
      }
    }
  },
  {
    $group: {
      _id: '$customerType',
      count: { $sum: 1 },
      avgCreationTime: { $avg: '$createdAt' }
    }
  }
], { allowDiskUse: true });

Data Migration

Schema Evolution

When schema changes are needed:

1. Backward Compatible: Add optional fields first

2. Migration Scripts: Create scripts for data transformation

3. Field Addition: New fields with defaults

4. Validation Updates: Enhance validation without breaking existing data

Example Migration Scripts

Add Customer Notes Field

// Migration script for adding notes field
db.customer.updateMany(
  { note: { $exists: false } },
  { $set: { note: "" } }
);

Customer Type Migration

// Migration script for customer type normalization
db.customer.updateMany(
  { customerType: { $exists: false } },
  { $set: { customerType: "regular" } }
);

// Update deprecated customer types
db.customer.updateMany(
  { customerType: "legacy" },
  { $set: { customerType: "regular" } }
);

Phone Number Normalization

// Migration script for phone number normalization
db.customer.find({ phone: { $exists: true, $ne: null } }).forEach(function(customer) {
  if (customer.phone) {
    const normalizedPhone = customer.phone.replace(/[^\d]/g, '');
    db.customer.updateOne(
      { _id: customer._id },
      { $set: { phone: normalizedPhone } }
    );
  }
});

Backup and Recovery

Backup Strategy

1. Regular Snapshots: Daily MongoDB snapshots

2. Point-in-Time Recovery: Enable oplog for PITR

3. Cross-Region Replication: For disaster recovery

4. Testing: Regular backup restoration testing

Data Retention

1. Active Data: Keep in primary collection

2. Soft Deleted: Retain for compliance period

3. Hard Delete: After legal retention requirements

4. Archival: Long-term storage for historical analysis

Security Considerations

Data Protection

PII Handling

• Encryption: Sensitive fields encrypted at rest

• Access Control: Role-based access to customer data

• Audit Logging: All customer data access logged

• Data Masking: PII masked in non-production environments

Privacy Compliance

GDPR Support:

• Right to Portability: Export customer data

• Right to Erasure: Hard delete on request

• Consent Tracking: Customer consent preferences

• Data Processing: Lawful basis documentation

Implementation Example:

// GDPR data export
async exportCustomerData(customerId: string): Promise<CustomerExportDto> {
  const customer = await this.customerRepository.findById(customerId);
  const orders = await this.orderRepository.findByCustomer(customerId);
  const payments = await this.paymentRepository.findByCustomer(customerId);
  
  return {
    personalData: customer,
    orderHistory: orders,
    paymentHistory: payments,
    exportDate: new Date().toISOString()
  };
}

// GDPR data deletion
async deleteCustomerData(customerId: string): Promise<void> {
  // Hard delete customer data
  await this.customerRepository.hardDelete(customerId);
  // Anonymize order history
  await this.orderRepository.anonymizeCustomerData(customerId);
  // Remove payment references
  await this.paymentRepository.anonymizeCustomerData(customerId);
}

Access Control

Database Security

• Authentication: MongoDB authentication required

• Authorization: Database-level permissions

• Network Security: VPC and firewall protection

• Encryption: TLS for data in transit

Application Security

• Input Validation: All inputs validated and sanitized

• SQL Injection Prevention: Parameterized queries (NoSQL injection)

• Rate Limiting: API rate limiting for customer operations

• Session Management: Secure session handling

Monitoring and Diagnostics

Performance Monitoring

Key Metrics

• Query Performance: Average query execution time

• Index Usage: Index hit ratios and efficiency

• Collection Growth: Data volume trends

• Connection Pool: Database connection utilization

Monitoring Queries

// Slow query detection
db.runCommand({
  profile: 2,
  slowms: 100,
  filter: { ns: "database.customer" }
});

// Index usage statistics
db.customer.aggregate([
  { $indexStats: {} }
]);

// Collection statistics
db.customer.stats();

Error Monitoring

Common Issues

• Validation Errors: Field format validation failures

• Duplicate Detection: Email/phone uniqueness violations

• State Consistency: State history integrity issues

• Performance: Slow query identification

Diagnostic Queries

// Find customers with validation issues
db.customer.find({
  $or: [
    { email: { $exists: true, $not: /^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}$/ } },
    { customerType: { $nin: ["regular", "unregistered", "new", "anonymous"] } }
  ]
});

// Find duplicate customers
db.customer.aggregate([
  {
    $match: {
      email: { $exists: true, $ne: null }
    }
  },
  {
    $group: {
      _id: "$email",
      count: { $sum: 1 },
      customers: { $push: "$$ROOT" }
    }
  },
  {
    $match: { count: { $gt: 1 } }
  }
]);

Integration Points

Order Management Integration

Customer-Order Relationships

• Attendees: Customers linked as service attendees

• Payers: Customers linked as payment payers

• History: Customer order history tracking

• Preferences: Customer service preferences

Data Flow

// Order creation with customer integration
async createOrderWithCustomers(orderData: CreateOrderDto): Promise<Order> {
  // Process attendees
  const processedAttendees = await this.processAttendees(orderData.attendees);
  
  // Create or update customers
  const customers = await Promise.all(
    processedAttendees.map(attendee => 
      this.createOrUpdateCustomer(attendee.customer)
    )
  );
  
  // Create order with customer snapshots
  return this.orderService.create({
    ...orderData,
    attendees: processedAttendees.map((attendee, index) => ({
      ...attendee,
      customer: customers[index]
    }))
  });
}

Payment System Integration

Customer-Payment Relationships

• Payer Information: Customer as payment payer

• Billing History: Customer payment history

• Payment Methods: Customer preferred payment methods

• Invoicing: Customer billing information

Notification System Integration

Customer Communication

• Email Notifications: Order confirmations, reminders

• SMS Notifications: Appointment reminders via phone

• Push Notifications: Mobile app notifications

• Marketing: Customer segmentation for campaigns

Summary

The Customer Management database schema provides a robust foundation for customer relationship management within the Bee O'clock ecosystem. Key features include:

• Flexible Schema: Optional fields supporting various customer types

• Data Validation: Comprehensive validation at domain and schema levels

• Multi-Tenant Architecture: Business-level data isolation

• Performance Optimization: Strategic indexing and query optimization

• State Management: Soft delete with complete audit trail

• Integration Ready: Seamless integration with orders and payments

• GDPR Compliance: Privacy and data protection features

• Scalability: Designed for high-volume customer data

• Monitoring: Comprehensive performance and error tracking

The schema supports complex business scenarios while maintaining data integrity, performance, and compliance with privacy regulations.