> For the complete documentation index, see [llms.txt](https://docs.beeoclock.com/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://docs.beeoclock.com/services/customer-management/db_schema.md).

# 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.

```typescript
{
  _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

```typescript
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

```typescript
// 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: "+1 (555) 123-4567" → 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

```typescript
// 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

```typescript
{
  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`

```typescript
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

```typescript
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

```typescript
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

```typescript
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

```typescript
// 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

```typescript
// 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

```typescript
// 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

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

#### Find Customer by Email or Phone

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

#### Customer Search with Pagination

```typescript
// 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

```typescript
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

```typescript
// 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

```typescript
// 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

```typescript
// 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:

```typescript
// Customer snapshot in order context
{
  orderId: "order123",
  attendees: [{
    customer: {
      _id: "customer456",
      firstName: "John",
      lastName: "Doe",
      email: "john.doe@example.com",
      phone: "1234567890",
      customerType: "regular"
    },
    firstTime: true
  }]
}
```

### State Management

#### Soft Delete Implementation

```typescript
// 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

```typescript
// 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

```typescript
// 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

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

#### Aggregation Optimization

```typescript
// 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

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

#### Customer Type Migration

```javascript
// 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

```javascript
// 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**:

```typescript
// 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

```javascript
// 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

```javascript
// 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

```typescript
// 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.