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

# Database Schema

## Overview

The service management module uses MongoDB with Mongoose ODM for data persistence. The schema supports complex service configurations with multilingual content, flexible pricing models, and rich presentation options following Domain-Driven Design principles.

## Core Entities

### Service Entity

**Collection**: `service`

**Schema Definition**: `ServiceSchema`

| Field              | Type                           | Required | Default   | Description                         |
| ------------------ | ------------------------------ | -------- | --------- | ----------------------------------- |
| `_id`              | `ObjectId`                     | Auto     | Generated | Unique identifier                   |
| `presentation`     | `PresentationSchema`           | Yes      | -         | Visual presentation and branding    |
| `configuration`    | `ServiceConfigurationSchema`   | No       | -         | Service behavior configuration      |
| `prepaymentPolicy` | `PaymentPolicySchema`          | Yes      | -         | Payment requirements and policies   |
| `schedules`        | `Array<ScheduleSchema>`        | No       | `[]`      | Available time slots and scheduling |
| `languageVersions` | `Array<LanguageVersionSchema>` | Yes      | -         | Multilingual service descriptions   |
| `durationVersions` | `Array<DurationVersionSchema>` | Yes      | -         | Duration options with pricing       |
| `specialists`      | `Array<SpecialistSchema>`      | No       | `[]`      | Assigned specialists (migration)    |
| `order`            | `Number`                       | No       | `null`    | Display order for service listing   |
| `stateHistory`     | `Array<StateHistorySchema>`    | No       | `[]`      | State change tracking               |
| `createdAt`        | `Date`                         | Auto     | Current   | Creation timestamp                  |
| `updatedAt`        | `Date`                         | Auto     | Current   | Last update timestamp               |

### Service Business Rules

1. **Language Versions**: At least one language version required
2. **Duration Versions**: At least one duration version required for bookable services
3. **Presentation**: Must include valid presentation configuration
4. **Prepayment Policy**: Required for all services
5. **Order Uniqueness**: Order numbers should be unique within tenant

## Sub-Schemas

### Presentation Schema (`PresentationSchema`)

**Purpose**: Manages visual presentation, branding, and media for services.

| Field     | Type              | Required | Default | Description                  |
| --------- | ----------------- | -------- | ------- | ---------------------------- |
| `banners` | `Array<ObjectId>` | No       | `[]`    | References to Media entities |
| `color`   | `String`          | No       | -       | Hex color code or color name |

**Configuration**:

* `_id: false` - No separate ID for sub-document
* `timestamps: true` - Tracks presentation changes
* `ref: Media.name` - References Media collection

**Business Rules**:

* Color must be valid hex format or recognized color name
* Banners reference valid media files
* Media files must be image format (JPEG, PNG, WebP)
* Maximum file size enforcement at application level

### Service Configuration Schema (`ServiceConfigurationSchema`)

**Purpose**: Defines service behavior and operational settings.

| Field      | Type                          | Required | Default | Description                    |
| ---------- | ----------------------------- | -------- | ------- | ------------------------------ |
| `duration` | `DurationConfigurationSchema` | No       | -       | Duration-related configuration |

**Configuration**:

* `_id: false` - No separate ID for sub-document
* `timestamps: true` - Tracks configuration changes

### Duration Configuration Schema (`DurationConfigurationSchema`)

**Purpose**: Configures how service duration is managed and presented.

| Field                 | Type     | Required | Default | Description                 |
| --------------------- | -------- | -------- | ------- | --------------------------- |
| `durationVersionType` | `String` | Yes      | -       | Duration configuration type |

**Enum Values**:

* `RANGE`: Fixed duration with optional breaks
* `VARIABLE`: Variable duration based on customer needs

**Configuration**:

* `_id: false` - No separate ID for sub-document
* `timestamps: false` - No automatic timestamps

### Payment Policy Schema (`PaymentPolicySchema`)

**Purpose**: Defines payment requirements and cancellation policies.

| Field               | Type      | Required | Default | Description                          |
| ------------------- | --------- | -------- | ------- | ------------------------------------ |
| `isRequired`        | `Boolean` | Yes      | -       | Whether prepayment is mandatory      |
| `isPercentage`      | `Boolean` | No       | -       | Whether value is percentage or fixed |
| `value`             | `String`  | No       | -       | Prepayment amount or percentage      |
| `minimalCancelTime` | `String`  | No       | -       | Minimum cancellation time in seconds |

**Configuration**:

* `_id: false` - No separate ID for sub-document
* `timestamps: false` - No automatic timestamps

**Business Rules**:

* When `isRequired: true`, value should be provided
* Percentage values must be 0-100
* Cancellation time must be positive integer
* Value stored as string for precision

### Duration Version Schema (`DurationVersionSchema`)

**Purpose**: Defines specific duration options with associated pricing.

| Field               | Type                 | Required | Default | Description                         |
| ------------------- | -------------------- | -------- | ------- | ----------------------------------- |
| `breakInSeconds`    | `Number`             | No       | -       | Break time after service completion |
| `durationInSeconds` | `Number`             | Yes      | -       | Service duration in seconds         |
| `prices`            | `Array<PriceSchema>` | No       | `[]`    | Available pricing options           |

**Configuration**:

* `_id: false` - No separate ID for sub-document
* `timestamps: false` - No automatic timestamps

**Validation Rules**:

* Duration must be positive integer
* Break time cannot be negative
* At least one price should be provided for bookable services
* Duration should be reasonable (5 minutes to 24 hours)

### Price Schema (`PriceSchema`)

**Purpose**: Defines pricing information with currency and language targeting.

| Field                | Type            | Required | Default | Description                     |
| -------------------- | --------------- | -------- | ------- | ------------------------------- |
| `price`              | `Number`        | Yes      | -       | Price amount                    |
| `currency`           | `String`        | No       | `USD`   | ISO 4217 currency code          |
| `preferredLanguages` | `Array<String>` | No       | `[]`    | Target languages for this price |

**Configuration**:

* `_id: false` - No separate ID for sub-document
* `timestamps: true` - Tracks price changes
* `enum: CurrencyCodeEnum` - Validates currency codes
* `enum: LanguageCodeEnum` - Validates language codes

**Validation Rules**:

* Price must be positive number
* Currency must be valid ISO 4217 code
* Languages must be valid ISO 639-1 codes
* Supports decimal precision for pricing

## Shared Schemas

### Language Version Schema (`LanguageVersionSchema`)

**Purpose**: Provides multilingual content support for services.

| Field         | Type     | Required | Default | Description                               |
| ------------- | -------- | -------- | ------- | ----------------------------------------- |
| `language`    | `String` | Yes      | -       | ISO 639-1 language code                   |
| `title`       | `String` | Yes      | -       | Service title in specified language       |
| `description` | `String` | No       | -       | Service description in specified language |

**Configuration**:

* `_id: false` - No separate ID for sub-document
* `timestamps: false` - No automatic timestamps
* `enum: LanguageCodeEnum` - Validates language codes

**Business Rules**:

* At least one language version required per service
* Title is mandatory for each language
* Description is optional but recommended
* Language codes must be unique within service

### Schedule Schema (`ScheduleSchema`)

**Purpose**: Defines service availability and scheduling rules.

| Field       | Type            | Required | Default | Description                             |
| ----------- | --------------- | -------- | ------- | --------------------------------------- |
| `weekDays`  | `Array<Number>` | Yes      | -       | Available weekdays (1=Monday, 7=Sunday) |
| `startTime` | `String`        | Yes      | -       | Start time in HH:mm format              |
| `endTime`   | `String`        | Yes      | -       | End time in HH:mm format                |
| `timezone`  | `String`        | No       | -       | Timezone identifier                     |

**Configuration**:

* `_id: false` - No separate ID for sub-document
* `timestamps: false` - No automatic timestamps

**Validation Rules**:

* Week days must be 1-7
* Times must be valid HH:mm format
* End time must be after start time
* Timezone must be valid identifier

### State History Schema (`StateHistorySchema`)

**Purpose**: Tracks state changes for audit and rollback capabilities.

| Field       | Type       | Required | Default | Description                   |
| ----------- | ---------- | -------- | ------- | ----------------------------- |
| `state`     | `String`   | Yes      | -       | State identifier              |
| `timestamp` | `Date`     | Yes      | Current | When state change occurred    |
| `actor`     | `ObjectId` | No       | -       | Who made the change           |
| `metadata`  | `Object`   | No       | -       | Additional change information |

**Configuration**:

* `_id: false` - No separate ID for sub-document
* `timestamps: false` - Uses explicit timestamp field

## Relationships

### Service → Media (Banners)

* **Type**: One-to-Many
* **Field**: `presentation.banners`
* **Collection**: `media`
* **Description**: References uploaded banner images

### Service → Specialists

* **Type**: Many-to-Many
* **Field**: `specialists`
* **Collection**: `specialist`
* **Description**: Assigned service providers (migration field)

### Service → Required Resources

* **Type**: Many-to-Many (via separate collection)
* **Field**: Not directly in schema
* **Collection**: `service-required-resources`
* **Description**: Resources needed for service delivery

## Indexes

### Primary Indexes

```javascript
// Compound index for tenant-based queries
{ "_tenantId": 1, "createdAt": -1 }

// Order for display sorting
{ "order": 1 }

// Language-based text search
{ 
  "languageVersions.title": "text", 
  "languageVersions.description": "text" 
}

// Price range queries
{ "durationVersions.prices.price": 1 }

// Currency-based filtering
{ "durationVersions.prices.currency": 1 }

// Duration-based queries
{ "durationVersions.durationInSeconds": 1 }
```

### Performance Indexes

```javascript
// Service status and availability
{ "stateHistory.state": 1, "updatedAt": -1 }

// Presentation media queries
{ "presentation.banners": 1 }

// Schedule-based availability
{ 
  "schedules.weekDays": 1, 
  "schedules.startTime": 1, 
  "schedules.endTime": 1 
}

// Configuration type queries
{ "configuration.duration.durationVersionType": 1 }

// Prepayment requirement queries
{ "prepaymentPolicy.isRequired": 1 }
```

### Search Optimization

* **Full-Text Search**: Compound text index on title and description
* **Language-Specific**: Support for language-specific search
* **Price Range**: Efficient price range queries
* **Duration Filtering**: Quick duration-based filtering
* **Availability**: Schedule-based availability queries

## Data Integrity

### Validation Rules

1. **Required Fields**: Language versions and duration versions mandatory
2. **Format Validation**: Times, currencies, languages follow standards
3. **Business Logic**: Duration and pricing constraints
4. **Reference Integrity**: Media and specialist references valid
5. **Range Validation**: Reasonable limits on duration and pricing

### Constraints

1. **Unique Constraints**: Order numbers unique per tenant
2. **Referential Integrity**: Media references must exist
3. **Business Rules**: Prepayment policies consistent
4. **Data Consistency**: Language versions complete

### Migration Considerations

1. **Schema Evolution**: Backwards compatibility for schema changes
2. **Data Migration**: Scripts for structural changes
3. **Index Management**: Efficient index creation and updates
4. **Legacy Support**: Support for old data formats

## Multi-Tenancy

### Tenant Isolation

* Services belong to specific tenants
* Queries include tenant context automatically
* Cross-tenant access prevention
* Tenant-specific indexing strategies

### Tenant-Aware Operations

All database operations include tenant context:

* Create operations include tenant ID
* Read operations filter by tenant
* Update/Delete operations validate tenant ownership
* Aggregation queries include tenant filtering

## Performance Optimization

### Query Performance

1. **Index Utilization**: Proper index design for common queries
2. **Aggregation Pipelines**: Efficient complex queries
3. **Projection**: Return only needed fields
4. **Population Strategy**: Optimize related data loading

### Storage Optimization

1. **Document Size**: Reasonable document size limits
2. **Embedded vs Referenced**: Optimal data structure design
3. **Compression**: MongoDB compression for storage efficiency
4. **Archive Strategy**: Archive old service versions

### Caching Strategy

1. **Query Caching**: Cache frequent service queries
2. **Computed Fields**: Cache calculated values
3. **Invalidation**: Smart cache invalidation on updates
4. **Distributed Caching**: Multi-instance cache coordination

## Backup and Recovery

### Backup Strategy

1. **Document Integrity**: Complete service data backup
2. **Media Coordination**: Sync with media storage backup
3. **Index Preservation**: Recreate indexes after restore
4. **Relationship Consistency**: Maintain reference integrity

### Recovery Procedures

1. **Point-in-Time Recovery**: Restore to specific timestamps
2. **Selective Recovery**: Restore individual services
3. **Data Validation**: Verify data integrity after restore
4. **Index Rebuilding**: Recreate performance indexes

## Security Considerations

### Data Protection

1. **Access Control**: Role-based database access
2. **Encryption**: Encryption at rest and in transit
3. **Audit Logging**: Track all data modifications
4. **PII Handling**: Secure handling of service descriptions

### Query Security

1. **Injection Prevention**: Parameterized queries
2. **Access Validation**: Tenant-aware query filtering
3. **Resource Limits**: Query execution time limits
4. **Rate Limiting**: Database operation rate limiting

## Monitoring and Maintenance

### Performance Monitoring

1. **Query Performance**: Slow query detection and optimization
2. **Index Usage**: Monitor index effectiveness
3. **Storage Growth**: Track collection size growth
4. **Cache Hit Rates**: Monitor caching effectiveness

### Maintenance Tasks

1. **Index Optimization**: Regular index analysis and tuning
2. **Data Archiving**: Archive old service versions
3. **Statistics Updates**: Keep query optimizer statistics current
4. **Cleanup Operations**: Remove orphaned references

## Development Guidelines

### Schema Design

1. **Normalization**: Balance between normalization and performance
2. **Embedding Strategy**: When to embed vs reference
3. **Index Strategy**: Design indexes for query patterns
4. **Validation**: Comprehensive schema validation

### Testing Strategies

1. **Unit Tests**: Test schema validation rules
2. **Integration Tests**: Test with real MongoDB instance
3. **Performance Tests**: Load test with realistic data
4. **Migration Tests**: Validate schema migration scripts

### Best Practices

1. **Document Size**: Keep documents under MongoDB limits
2. **Query Patterns**: Design schema for common queries
3. **Update Patterns**: Minimize document rewrites
4. **Consistency**: Maintain referential integrity