> 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/order-management/db_schema.md).

# Database Schema

## Overview

This document provides comprehensive documentation of the MongoDB schema definitions, relationships, indexes, and data integrity rules for the Order Management module. The schemas are designed to support a multi-tenant SaaS architecture with proper data isolation and performance optimization.

## Technology Stack

* **Database**: MongoDB with Mongoose ODM
* **Schema Definition**: Mongoose Schema with TypeScript typing
* **Indexing Strategy**: Compound indexes for optimal query performance
* **Data Validation**: Schema-level validation with business rules
* **Multi-tenancy**: Tenant-based data isolation
* **Audit Trail**: State history tracking for all entities

## Core Schemas

### Order Schema

**Collection Name**: `order`

**Purpose**: Main order entity containing order metadata, status, and relationships to services and products.

**Schema Definition**:

```typescript
import { MetaSchema } from '@root/common/infrastructure/repository/base-schemas/meta.schema';
import { PaymentStatusEnum } from '@root/common/module/payment/domain/enums/payment.status.enum';
import { Schema } from 'mongoose';
import { IOrder } from '../../domain/persistance/IOrder';
import { OrderProduct } from '../../domain/root/order-product.root';
import { OrderService } from '../../domain/root/order-service.root';
import { Order } from '../../domain/root/order.root';
import { OrderNotificationSettingsSchema } from './sub/notification-settings.scheme';
import { StateHistorySchema } from '@root/common/infrastructure/repository/base-schemas/state-history.scheme';

export type OrderDocument = IOrder & Document;

export const OrderSchema = new Schema<IOrder>({
  // Order status using enum validation
  status: { 
    type: String, 
    required: true,
    enum: Object.values(OrderStatusEnum),
    default: OrderStatusEnum.requested
  },
  
  // Metadata for tracking and analytics
  meta: { 
    type: MetaSchema,
    required: false
  },
  
  // References to order products (separate collection)
  products: [{ 
    type: Schema.Types.ObjectId, 
    ref: OrderProduct.name,
    validate: {
      validator: function(products) {
        return !products || products.length <= ORDER_CONSTANTS.MAX_PRODUCTS_PER_ORDER;
      },
      message: `Order cannot contain more than ${ORDER_CONSTANTS.MAX_PRODUCTS_PER_ORDER} products`
    }
  }],
  
  // References to order services (separate collection)  
  services: [{ 
    type: Schema.Types.ObjectId, 
    ref: OrderService.name,
    validate: {
      validator: function(services) {
        return !services || services.length <= ORDER_CONSTANTS.MAX_SERVICES_PER_ORDER;
      },
      message: `Order cannot contain more than ${ORDER_CONSTANTS.MAX_SERVICES_PER_ORDER} services`
    }
  }],
  
  // Internal business notes (not visible to customers)
  businessNote: { 
    type: String,
    maxlength: 1000,
    trim: true
  },
  
  // Payment status tracking
  paymentStatus: { 
    type: String, 
    enum: Object.values(PaymentStatusEnum),
    default: PaymentStatusEnum.pending,
    required: false 
  },
  
  // Customer notification preferences
  notificationSettings: { 
    type: OrderNotificationSettingsSchema,
    required: false
  },
  
  // Complete audit trail with state changes
  stateHistory: { 
    type: [StateHistorySchema], 
    default: [] 
  }
}, {
  timestamps: true,                    // Automatic createdAt/updatedAt
  collection: Order.name.toLowerCase(), // Collection name: 'order'
  discriminatorKey: '__type'           // Support for inheritance
});
```

**Indexes**:

```typescript
// Compound index for common queries
OrderSchema.index({ 
  tenantId: 1, 
  status: 1, 
  createdAt: -1 
});

// Customer-based queries
OrderSchema.index({ 
  tenantId: 1, 
  'meta.customerId': 1, 
  createdAt: -1 
});

// Payment status queries
OrderSchema.index({ 
  tenantId: 1, 
  paymentStatus: 1, 
  updatedAt: -1 
});

// Member assignment queries (through services)
OrderSchema.index({ 
  tenantId: 1, 
  services: 1, 
  status: 1 
});

// Date range queries for reporting
OrderSchema.index({ 
  tenantId: 1, 
  createdAt: 1, 
  status: 1 
});
```

**Business Rules**:

1. **Required Fields**: Status is mandatory for all orders
2. **Status Validation**: Only valid enum values are accepted
3. **Service Limits**: Maximum 10 services per order (configurable)
4. **Product Limits**: Maximum 50 products per order (configurable)
5. **Note Length**: Business notes limited to 1000 characters
6. **State History**: All status changes are automatically tracked

### Order Service Schema

**Collection Name**: `orderservice`

**Purpose**: Individual service within an order with appointment details, specialist assignments, and status tracking.

**Schema Definition**:

```typescript
import { MetaSchema } from '@root/common/infrastructure/repository/base-schemas/meta.schema';
import { PaymentStatusEnum } from '@root/common/module/payment/domain/enums/payment.status.enum';
import { ServiceSchema } from '@root/common/module/service/repos/service.scheme';
import { Schema } from 'mongoose';
import { OrderServiceStatusEnum } from '../../domain/enums/order-service.status.enum';
import { IOrderService } from '../../domain/persistance/IOrderService';
import { OrederedServiceDetailsSchema } from './sub/order-service-details.schema';
import { StateHistorySchema } from '@root/common/infrastructure/repository/base-schemas/state-history.scheme';

export const OrderServiceSchema = new Schema<IOrderService>({
  // Parent order reference
  orderId: { 
    type: String, 
    required: true,
    validate: {
      validator: function(orderId) {
        return mongoose.Types.ObjectId.isValid(orderId);
      },
      message: 'Invalid order ID format'
    }
  },
  
  // Service configuration snapshot at booking time
  serviceSnapshot: { 
    type: ServiceSchema, 
    required: true,
    validate: {
      validator: function(service) {
        return service && service.isActive !== false;
      },
      message: 'Service must be active for booking'
    }
  },
  
  // Appointment details with specialists and attendees
  orderAppointmentDetails: { 
    type: OrederedServiceDetailsSchema, 
    required: true,
    validate: {
      validator: function(details) {
        const start = new Date(details.start);
        const end = new Date(details.end);
        return start < end && start > new Date();
      },
      message: 'Appointment must have valid start/end times in the future'
    }
  },
  
  // Service-specific status
  status: { 
    type: String, 
    enum: Object.values(OrderServiceStatusEnum),
    default: OrderServiceStatusEnum.requested,
    required: true
  },
  
  // Customer notes for this specific service
  customerNote: { 
    type: String,
    maxlength: 500,
    trim: true
  },
  
  // Service metadata
  meta: { 
    type: MetaSchema,
    required: false
  },
  
  // Payment status for this service
  paymentStatus: { 
    type: String, 
    enum: Object.values(PaymentStatusEnum),
    default: PaymentStatusEnum.pending,
    required: false 
  },
  
  // Service state change history
  stateHistory: { 
    type: [StateHistorySchema], 
    default: [] 
  }
}, { 
  timestamps: true,
  collection: 'orderService'
});

export type OrderServiceDocument = IOrderService & Document;
```

**Indexes**:

```typescript
// Primary lookup by order
OrderServiceSchema.index({ 
  orderId: 1, 
  status: 1 
});

// Specialist assignment queries
OrderServiceSchema.index({ 
  'orderAppointmentDetails.specialists.member': 1, 
  'orderAppointmentDetails.start': 1 
});

// Date range queries for scheduling
OrderServiceSchema.index({ 
  'orderAppointmentDetails.start': 1, 
  'orderAppointmentDetails.end': 1 
});

// Status and payment queries
OrderServiceSchema.index({ 
  status: 1, 
  paymentStatus: 1, 
  updatedAt: -1 
});

// Service type queries
OrderServiceSchema.index({ 
  'serviceSnapshot._id': 1, 
  status: 1, 
  createdAt: -1 
});
```

**Business Rules**:

1. **Valid Order Reference**: orderId must be a valid ObjectId
2. **Active Service**: Service snapshot must be from an active service
3. **Future Appointments**: Appointment start time must be in the future
4. **Valid Time Range**: End time must be after start time
5. **Specialist Assignment**: At least one specialist must be assigned
6. **Note Limits**: Customer notes limited to 500 characters

### Order Product Schema

**Collection Name**: `orderproduct`

**Purpose**: Products included in an order, either standalone or associated with specific services.

**Schema Definition**:

```typescript
import { ProductSchema } from '@root/common/module/product/product/repos/product.scheme';
import { Schema } from 'mongoose';
import { IOrderProduct } from '../../domain/persistance/IOrderProduct';
import { MetaSchema } from '@root/common/infrastructure/repository/base-schemas/meta.schema';
import { OrderProduct } from '../../domain/root/order-product.root';
import { PaymentStatusEnum } from '@root/common/module/payment/domain/enums/payment.status.enum';
import { StateHistorySchema } from '@root/common/infrastructure/repository/base-schemas/state-history.scheme';

export const OrderProductSchema = new Schema<IOrderProduct>({
  // Optional association with specific service
  orderServiceId: { 
    type: String, 
    required: false,
    validate: {
      validator: function(serviceId) {
        return !serviceId || mongoose.Types.ObjectId.isValid(serviceId);
      },
      message: 'Invalid service ID format'
    }
  },
  
  // Parent order reference
  orderId: { 
    type: String, 
    required: false,
    validate: {
      validator: function(orderId) {
        return !orderId || mongoose.Types.ObjectId.isValid(orderId);
      },
      message: 'Invalid order ID format'
    }
  },
  
  // Product configuration snapshot at order time
  productSnapshot: { 
    type: ProductSchema, 
    required: true,
    validate: {
      validator: function(product) {
        return product && product.isActive !== false;
      },
      message: 'Product must be active for ordering'
    }
  },
  
  // Quantity ordered
  quantity: { 
    type: Number, 
    required: true,
    min: [1, 'Quantity must be at least 1'],
    max: [100, 'Quantity cannot exceed 100'],
    validate: {
      validator: Number.isInteger,
      message: 'Quantity must be a whole number'
    }
  },
  
  // Product metadata
  meta: { 
    type: MetaSchema,
    required: false
  },
  
  // Payment status for this product
  paymentStatus: { 
    type: String, 
    enum: Object.values(PaymentStatusEnum),
    default: PaymentStatusEnum.pending,
    required: false 
  },
  
  // Sale timestamp (when product was sold/delivered)
  saleDate: {
    type: Date,
    required: false
  },
  
  // Product state change history
  stateHistory: { 
    type: [StateHistorySchema], 
    default: [] 
  }
}, { 
  timestamps: true,
  collection: 'orderProduct'
});

export type OrderProductDocument = IOrderProduct & Document;
```

**Indexes**:

```typescript
// Order-based product queries
OrderProductSchema.index({ 
  orderId: 1, 
  createdAt: -1 
});

// Service-associated product queries
OrderProductSchema.index({ 
  orderServiceId: 1, 
  paymentStatus: 1 
});

// Product type queries
OrderProductSchema.index({ 
  'productSnapshot._id': 1, 
  saleDate: -1 
});

// Inventory and sales queries
OrderProductSchema.index({ 
  'productSnapshot._id': 1, 
  quantity: 1, 
  createdAt: -1 
});

// Payment tracking queries
OrderProductSchema.index({ 
  paymentStatus: 1, 
  saleDate: 1 
});
```

**Business Rules**:

1. **Active Product**: Product snapshot must be from an active product
2. **Valid Quantity**: Quantity must be positive integer between 1-100
3. **Valid References**: Order and service IDs must be valid ObjectIds when provided
4. **Sale Date**: Sale date can only be set when product is delivered
5. **Payment Consistency**: Payment status must align with order payment status

## Sub-Schemas

### Order Service Details Schema

**Purpose**: Detailed appointment information including specialists, attendees, location, and attachments.

**Schema Definition**:

```typescript
import { Schema } from 'mongoose';
import { LocationSchema } from './location.scheme';
import { AttendantSchema } from './attendant.scheme';
import { AttachmentSchema } from './attachment.scheme';
import { SpecialistSchema } from './specialist.scheme';
import { LanguageCodeEnum } from '@root/common/core/enum/language-code.enum';
import { ReservationTypeEnum } from '../../../domain/enums/reservation.type.enum';

export const OrederedServiceDetailsSchema = new Schema({
  // Appointment timing
  start: {
    type: String,
    required: true,
    validate: {
      validator: function(start) {
        return !isNaN(Date.parse(start));
      },
      message: 'Start time must be a valid ISO date string'
    }
  },
  
  end: {
    type: String,
    required: true,
    validate: {
      validator: function(end) {
        const start = new Date(this.start);
        const endDate = new Date(end);
        return endDate > start;
      },
      message: 'End time must be after start time'
    }
  },
  
  // Reservation type classification
  type: {
    type: String,
    enum: Object.values(ReservationTypeEnum),
    default: ReservationTypeEnum.service
  },
  
  // Preferred languages for service delivery
  languageCodes: [{
    type: String,
    enum: Object.values(LanguageCodeEnum),
    default: [LanguageCodeEnum.en]
  }],
  
  // Assigned specialists (required)
  specialists: {
    type: [SpecialistSchema],
    required: true,
    validate: {
      validator: function(specialists) {
        return specialists && specialists.length > 0;
      },
      message: 'At least one specialist must be assigned'
    }
  },
  
  // Service attendees (customers)
  attendants: {
    type: [AttendantSchema],
    required: false,
    validate: {
      validator: function(attendants) {
        return !attendants || attendants.length <= ORDER_CONSTANTS.MAX_ATTENDEES_PER_SERVICE;
      },
      message: `Cannot have more than ${ORDER_CONSTANTS.MAX_ATTENDEES_PER_SERVICE} attendees per service`
    }
  },
  
  // Service location details
  location: {
    type: LocationSchema,
    required: false
  },
  
  // Additional attachments and files
  attachments: {
    type: [AttachmentSchema],
    required: false,
    validate: {
      validator: function(attachments) {
        return !attachments || attachments.length <= ORDER_CONSTANTS.MAX_ATTACHMENTS_PER_SERVICE;
      },
      message: `Cannot have more than ${ORDER_CONSTANTS.MAX_ATTACHMENTS_PER_SERVICE} attachments per service`
    }
  }
}, {
  _id: false  // Embedded document, no separate ID
});
```

### Notification Settings Schema

**Purpose**: Customer notification preferences for order updates and reminders.

**Schema Definition**:

```typescript
import { Schema } from 'mongoose';

export const OrderNotificationSettingsSchema = new Schema({
  // Email notification preferences
  emailNotifications: {
    type: Boolean,
    default: true
  },
  
  // SMS notification preferences
  smsNotifications: {
    type: Boolean,
    default: false
  },
  
  // Push notification preferences
  pushNotifications: {
    type: Boolean,
    default: true
  },
  
  // Reminder configuration
  reminderSettings: {
    enabled: {
      type: Boolean,
      default: true
    },
    timeBeforeInMinutes: {
      type: Number,
      default: 60,
      min: [15, 'Reminder must be at least 15 minutes before'],
      max: [1440, 'Reminder cannot be more than 24 hours before']
    }
  },
  
  // Notification language preference
  preferredLanguage: {
    type: String,
    enum: Object.values(LanguageCodeEnum),
    default: LanguageCodeEnum.en
  }
}, {
  _id: false
});
```

### Supporting Sub-Schemas

#### Specialist Schema

```typescript
export const SpecialistSchema = new Schema({
  member: {
    type: Schema.Types.ObjectId,
    ref: 'Member',
    required: true
  },
  role: {
    type: String,
    default: 'primary'
  },
  assignedAt: {
    type: Date,
    default: Date.now
  }
}, { _id: false });
```

#### Attendant Schema

```typescript
export const AttendantSchema = new Schema({
  customer: {
    type: Schema.Types.ObjectId,
    ref: 'Customer',
    required: true
  },
  firstTime: {
    type: Boolean,
    default: false
  },
  specialRequests: {
    type: String,
    maxlength: 200
  }
}, { _id: false });
```

#### Location Schema

```typescript
export const LocationSchema = new Schema({
  address: {
    type: String,
    maxlength: 200
  },
  coordinates: {
    latitude: {
      type: Number,
      min: -90,
      max: 90
    },
    longitude: {
      type: Number,
      min: -180,
      max: 180
    }
  },
  roomNumber: {
    type: String,
    maxlength: 20
  },
  buildingInfo: {
    type: String,
    maxlength: 100
  }
}, { _id: false });
```

#### Attachment Schema

```typescript
export const AttachmentSchema = new Schema({
  filename: {
    type: String,
    required: true,
    maxlength: 255
  },
  url: {
    type: String,
    required: true,
    validate: {
      validator: function(url) {
        return /^https?:\/\/.+/.test(url);
      },
      message: 'URL must be a valid HTTP/HTTPS URL'
    }
  },
  mimeType: {
    type: String,
    validate: {
      validator: function(mimeType) {
        return /^[a-zA-Z]+\/[a-zA-Z0-9\-\+\.]+$/.test(mimeType);
      },
      message: 'Invalid MIME type format'
    }
  },
  size: {
    type: Number,
    min: 0,
    max: ORDER_CONSTANTS.MAX_ATTACHMENT_SIZE_MB * 1024 * 1024
  },
  uploadedBy: {
    type: Schema.Types.ObjectId,
    ref: 'Member'
  },
  uploadedAt: {
    type: Date,
    default: Date.now
  }
}, { _id: false });
```

## Index Strategy

### Performance Optimization

#### Compound Indexes

```typescript
// Multi-tenant queries with status filtering
db.order.createIndex({ 
  "tenantId": 1, 
  "status": 1, 
  "createdAt": -1 
});

// Customer order history
db.order.createIndex({ 
  "tenantId": 1, 
  "meta.customerId": 1, 
  "createdAt": -1 
});

// Member assignment queries
db.orderservice.createIndex({ 
  "orderAppointmentDetails.specialists.member": 1, 
  "orderAppointmentDetails.start": 1,
  "status": 1
});

// Date range queries for scheduling
db.orderservice.createIndex({ 
  "orderAppointmentDetails.start": 1, 
  "orderAppointmentDetails.end": 1,
  "orderAppointmentDetails.specialists.member": 1
});

// Payment status tracking
db.order.createIndex({ 
  "tenantId": 1, 
  "paymentStatus": 1, 
  "updatedAt": -1 
});
```

#### Sparse Indexes

```typescript
// Optional field indexes
db.order.createIndex({ 
  "meta.customerId": 1 
}, { 
  sparse: true 
});

db.orderproduct.createIndex({ 
  "orderServiceId": 1 
}, { 
  sparse: true 
});

db.orderproduct.createIndex({ 
  "saleDate": 1 
}, { 
  sparse: true 
});
```

#### Text Indexes

```typescript
// Full-text search capabilities
db.order.createIndex({
  "businessNote": "text",
  "meta.notes": "text"
}, {
  name: "order_text_search",
  default_language: "english"
});

db.orderservice.createIndex({
  "customerNote": "text"
}, {
  name: "service_text_search"
});
```

### Index Monitoring

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

// Query performance analysis
db.order.explain("executionStats").find({
  tenantId: "tenant123",
  status: "confirmed",
  createdAt: { $gte: new Date("2024-01-01") }
});
```

## Data Integrity Rules

### Referential Integrity

#### Order Relationships

```typescript
// Pre-save middleware for order validation
OrderSchema.pre('save', async function(next) {
  // Validate service references
  if (this.services?.length) {
    const serviceCount = await OrderService.countDocuments({
      _id: { $in: this.services },
      orderId: this._id
    });
    
    if (serviceCount !== this.services.length) {
      throw new Error('Invalid service references in order');
    }
  }
  
  // Validate product references
  if (this.products?.length) {
    const productCount = await OrderProduct.countDocuments({
      _id: { $in: this.products },
      orderId: this._id
    });
    
    if (productCount !== this.products.length) {
      throw new Error('Invalid product references in order');
    }
  }
  
  next();
});
```

#### Cascade Operations

```typescript
// Cascade delete for order services and products
OrderSchema.pre('deleteOne', { document: true, query: false }, async function() {
  // Delete associated services
  await OrderService.deleteMany({ orderId: this._id });
  
  // Delete associated products
  await OrderProduct.deleteMany({ orderId: this._id });
  
  // Clean up related data
  await NotificationService.cancelOrderNotifications(this._id);
  await CacheService.invalidateOrderCache(this._id);
});
```

### Data Validation

#### Business Rule Validation

```typescript
// Custom validators for business rules
const validateAppointmentConflict = async function(value) {
  if (!this.orderAppointmentDetails) return true;
  
  const { start, end, specialists } = this.orderAppointmentDetails;
  
  for (const specialist of specialists) {
    const conflicts = await OrderService.find({
      'orderAppointmentDetails.specialists.member': specialist.member,
      'orderAppointmentDetails.start': { $lt: end },
      'orderAppointmentDetails.end': { $gt: start },
      status: { $nin: ['cancelled', 'deleted'] },
      _id: { $ne: this._id }
    });
    
    if (conflicts.length > 0) {
      throw new Error(`Specialist ${specialist.member} has conflicting appointments`);
    }
  }
  
  return true;
};

OrderServiceSchema.path('orderAppointmentDetails').validate(
  validateAppointmentConflict,
  'Appointment conflicts with existing bookings'
);
```

#### Multi-field Validation

```typescript
// Cross-field validation
const validateOrderConsistency = function() {
  // Validate payment status consistency
  if (this.status === 'done' && this.paymentStatus === 'pending') {
    throw new Error('Completed orders must have resolved payment status');
  }
  
  // Validate service count limits
  if (this.services?.length > ORDER_CONSTANTS.MAX_SERVICES_PER_ORDER) {
    throw new Error(`Order cannot contain more than ${ORDER_CONSTANTS.MAX_SERVICES_PER_ORDER} services`);
  }
  
  return true;
};

OrderSchema.pre('validate', validateOrderConsistency);
```

## Multi-tenancy Support

### Tenant Isolation

#### Schema Design

```typescript
// Tenant field in all schemas
const baseTenantSchema = {
  tenantId: {
    type: String,
    required: true,
    immutable: true,  // Cannot be changed after creation
    validate: {
      validator: function(tenantId) {
        return /^[a-f\d]{24}$/i.test(tenantId);
      },
      message: 'Tenant ID must be a valid ObjectId'
    }
  }
};

// Apply to all order schemas
OrderSchema.add(baseTenantSchema);
OrderServiceSchema.add(baseTenantSchema);
OrderProductSchema.add(baseTenantSchema);
```

#### Query Middleware

```typescript
// Automatic tenant filtering
function addTenantFilter(schema) {
  schema.pre(/^find/, function() {
    if (!this.getQuery().tenantId) {
      throw new Error('Tenant ID is required for all queries');
    }
  });
  
  schema.pre('countDocuments', function() {
    if (!this.getQuery().tenantId) {
      throw new Error('Tenant ID is required for count queries');
    }
  });
  
  schema.pre('aggregate', function() {
    // Add tenant filter to aggregation pipeline
    this.pipeline().unshift({
      $match: { tenantId: this.options.tenantId }
    });
  });
}

// Apply tenant filtering to all schemas
addTenantFilter(OrderSchema);
addTenantFilter(OrderServiceSchema);
addTenantFilter(OrderProductSchema);
```

### Data Partitioning

```typescript
// Collection naming strategy for large tenants
function getCollectionName(baseName, tenantId) {
  // For high-volume tenants, use separate collections
  const highVolumeTenants = ['tenant1', 'tenant2', 'tenant3'];
  
  if (highVolumeTenants.includes(tenantId)) {
    return `${baseName}_${tenantId}`;
  }
  
  return baseName;
}

// Dynamic model creation
function getOrderModel(tenantId) {
  const collectionName = getCollectionName('order', tenantId);
  return mongoose.model('Order', OrderSchema, collectionName);
}
```

## Audit Trail and State History

### State History Schema

```typescript
export const StateHistorySchema = new Schema({
  // Previous state information
  previousState: {
    type: Schema.Types.Mixed,
    required: true
  },
  
  // New state information
  newState: {
    type: Schema.Types.Mixed,
    required: true
  },
  
  // Who made the change
  changedBy: {
    type: Schema.Types.ObjectId,
    ref: 'Member',
    required: true
  },
  
  // When the change occurred
  changedAt: {
    type: Date,
    default: Date.now,
    required: true
  },
  
  // Reason for change
  reason: {
    type: String,
    maxlength: 200
  },
  
  // Change source (panel, api, system, etc.)
  source: {
    type: String,
    enum: ['panel', 'api', 'system', 'migration'],
    default: 'panel'
  },
  
  // IP address of change originator
  ipAddress: {
    type: String,
    validate: {
      validator: function(ip) {
        return !ip || /^(?:[0-9]{1,3}\.){3}[0-9]{1,3}$|^(?:[a-fA-F0-9]{0,4}:){1,7}[a-fA-F0-9]{0,4}$/.test(ip);
      },
      message: 'Invalid IP address format'
    }
  },
  
  // User agent information
  userAgent: {
    type: String,
    maxlength: 500
  }
}, {
  _id: true,  // Give each history entry an ID
  timestamps: false  // We have our own timestamp
});
```

### Automatic History Tracking

```typescript
// Middleware to track state changes
function addHistoryTracking(schema, entityName) {
  schema.pre('save', function(next) {
    if (this.isNew) {
      // For new documents, add creation history
      this.stateHistory.push({
        previousState: null,
        newState: this.toObject(),
        changedBy: this.meta?.createdBy || this.meta?.lastModifiedBy,
        changedAt: new Date(),
        reason: `${entityName} created`,
        source: this.meta?.source || 'panel'
      });
    } else if (this.isModified()) {
      // For updates, track the changes
      const changes = {};
      const modifiedPaths = this.modifiedPaths();
      
      for (const path of modifiedPaths) {
        if (path !== 'stateHistory' && path !== 'updatedAt') {
          changes[path] = {
            from: this._original?.[path],
            to: this[path]
          };
        }
      }
      
      this.stateHistory.push({
        previousState: this._original,
        newState: this.toObject(),
        changedBy: this.meta?.lastModifiedBy,
        changedAt: new Date(),
        reason: `${entityName} updated`,
        source: this.meta?.source || 'panel',
        changes: changes
      });
    }
    
    next();
  });
  
  schema.post('init', function() {
    // Store original state for comparison
    this._original = this.toObject();
  });
}

// Apply history tracking to all schemas
addHistoryTracking(OrderSchema, 'Order');
addHistoryTracking(OrderServiceSchema, 'OrderService');
addHistoryTracking(OrderProductSchema, 'OrderProduct');
```

## Performance Optimization

### Connection Management

```typescript
// Optimized connection configuration
const connectionOptions = {
  maxPoolSize: 50,          // Maximum number of connections
  minPoolSize: 5,           // Minimum number of connections
  maxIdleTimeMS: 30000,     // Close connections after 30 seconds of inactivity
  serverSelectionTimeoutMS: 5000,  // How long to try selecting a server
  socketTimeoutMS: 45000,   // How long to wait for a response
  bufferMaxEntries: 0,      // Disable mongoose buffering
  bufferCommands: false,    // Disable mongoose buffering
  heartbeatFrequencyMS: 10000,  // How often to check server health
  retryWrites: true,        // Retry failed writes
  writeConcern: {
    w: 'majority',          // Wait for majority acknowledgment
    wtimeout: 5000          // Timeout for write concern
  },
  readConcern: {
    level: 'majority'       // Read concern level
  }
};
```

### Query Optimization

```typescript
// Optimized aggregation pipelines
const getOrdersByDateRange = (tenantId, startDate, endDate) => [
  // Match stage with indexes
  {
    $match: {
      tenantId: tenantId,
      createdAt: {
        $gte: new Date(startDate),
        $lte: new Date(endDate)
      }
    }
  },
  
  // Lookup with indexed fields
  {
    $lookup: {
      from: 'orderservice',
      localField: 'services',
      foreignField: '_id',
      as: 'serviceDetails',
      pipeline: [
        {
          $match: {
            tenantId: tenantId
          }
        },
        {
          $project: {
            status: 1,
            orderAppointmentDetails: 1,
            serviceSnapshot: 1
          }
        }
      ]
    }
  },
  
  // Project only needed fields
  {
    $project: {
      status: 1,
      paymentStatus: 1,
      createdAt: 1,
      updatedAt: 1,
      serviceDetails: 1,
      businessNote: 1
    }
  },
  
  // Sort with indexed field
  {
    $sort: {
      createdAt: -1
    }
  }
];
```

### Memory Optimization

```typescript
// Stream processing for large datasets
const processLargeOrderDataset = async (tenantId, processor) => {
  const cursor = Order.find({ tenantId })
    .lean()  // Return plain objects instead of Mongoose documents
    .cursor();
    
  for (let order = await cursor.next(); order != null; order = await cursor.next()) {
    await processor(order);
  }
};

// Batch operations for better performance
const bulkUpdateOrders = async (tenantId, updates) => {
  const bulkOps = updates.map(update => ({
    updateOne: {
      filter: { _id: update.orderId, tenantId },
      update: { $set: update.changes },
      upsert: false
    }
  }));
  
  // Process in batches of 1000
  const batchSize = 1000;
  for (let i = 0; i < bulkOps.length; i += batchSize) {
    const batch = bulkOps.slice(i, i + batchSize);
    await Order.bulkWrite(batch, { ordered: false });
  }
};
```

## Backup and Recovery

### Backup Strategy

```typescript
// Incremental backup configuration
const backupConfig = {
  // Daily full backup
  fullBackup: {
    schedule: "0 2 * * *",  // 2 AM daily
    retention: 30,          // Keep 30 days
    collections: ['order', 'orderservice', 'orderproduct']
  },
  
  // Hourly incremental backup
  incrementalBackup: {
    schedule: "0 * * * *",  // Every hour
    retention: 24,          // Keep 24 hours
    query: {
      updatedAt: { $gte: "lastBackupTime" }
    }
  },
  
  // Point-in-time recovery
  oplogBackup: {
    enabled: true,
    retention: 7            // Keep 7 days of oplog
  }
};
```

### Recovery Procedures

```typescript
// Point-in-time recovery
const recoverToTimestamp = async (targetTimestamp) => {
  // Stop application
  await stopApplication();
  
  // Restore from last full backup before target time
  const backupFile = findBackupBeforeTimestamp(targetTimestamp);
  await restoreFromBackup(backupFile);
  
  // Apply oplog entries up to target timestamp
  await replayOplogToTimestamp(targetTimestamp);
  
  // Verify data integrity
  await verifyDataIntegrity();
  
  // Restart application
  await startApplication();
};
```

## Monitoring and Maintenance

### Health Checks

```typescript
// Database health monitoring
const performHealthCheck = async () => {
  const checks = {
    connection: false,
    indexes: false,
    replication: false,
    performance: false
  };
  
  try {
    // Test connection
    await mongoose.connection.db.admin().ping();
    checks.connection = true;
    
    // Check indexes
    const orderIndexes = await Order.collection.indexes();
    checks.indexes = orderIndexes.length >= 5;  // Expected minimum indexes
    
    // Check replication lag
    const replStatus = await mongoose.connection.db.admin().command({
      replSetGetStatus: 1
    });
    checks.replication = replStatus.ok === 1;
    
    // Check performance metrics
    const stats = await mongoose.connection.db.stats();
    checks.performance = stats.avgObjSize < 10000;  // Objects under 10KB average
    
  } catch (error) {
    console.error('Health check failed:', error);
  }
  
  return checks;
};
```

### Maintenance Tasks

```typescript
// Automated maintenance routines
const performMaintenance = async () => {
  // Compact collections
  await Order.collection.compact();
  await OrderService.collection.compact();
  await OrderProduct.collection.compact();
  
  // Rebuild indexes
  await Order.collection.reIndex();
  await OrderService.collection.reIndex();
  await OrderProduct.collection.reIndex();
  
  // Clean up old state history (older than 1 year)
  const oneYearAgo = new Date();
  oneYearAgo.setFullYear(oneYearAgo.getFullYear() - 1);
  
  await Order.updateMany(
    {},
    {
      $pull: {
        stateHistory: {
          changedAt: { $lt: oneYearAgo }
        }
      }
    }
  );
  
  // Update statistics
  await updateCollectionStatistics();
};
```