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

# Database Schema

## Overview

This document describes the database schema and data persistence layer for the Absence Management module. The system uses MongoDB as the primary database with Mongoose as the ODM layer.

## Database Schema

### Absence Collection

**Collection Name:** `absence`

The absence collection stores all absence records with the following schema structure:

```typescript
{
  _id: ObjectId,                    // Primary key
  note: String,                     // Optional description
  start: String,                    // Start date/time (ISO string) - REQUIRED
  end: String,                      // End date/time (ISO string) - REQUIRED
  entireBusiness: Boolean,          // Affects entire business flag
  members: [ObjectId],              // References to Member collection
  memberIds: [String],              // Array of member ID strings
  locations: [LocationSchema],      // Embedded location documents
  type: String,                     // Absence type (vacation, holiday, break) - REQUIRED
  timeZone: String,                 // Timezone identifier - REQUIRED
  meta: MetaSchema,                 // Metadata information
  stateHistory: [StateHistorySchema], // State tracking for soft deletes
  createdAt: Date,                  // Auto-generated creation timestamp
  updatedAt: Date                   // Auto-generated update timestamp
}
```

### Schema Definition

```typescript
const AbsenceSchema = new Schema<IAbsence>({
  note: { 
    type: String 
  },
  start: {
    type: String,
    required: true,
  },
  end: {
    type: String,
    required: true,
  },
  entireBusiness: { 
    type: Boolean 
  },
  members: [{ 
    type: mongoose.Schema.Types.ObjectId, 
    ref: Member.name 
  }],
  memberIds: [{
    type: String
  }],
  locations: {
    type: [LocationSchema],
  },
  type: {
    type: String,
    required: true,
  },
  timeZone: {
    type: String,
    required: true,
  },
  meta: { 
    type: MetaSchema 
  },
  stateHistory: { 
    type: [StateHistorySchema], 
    default: [] 
  },
}, {
  timestamps: true,
  collection: Absence.name.toLowerCase(),
});
```

## Field Specifications

### Required Fields

| Field      | Type   | Description                   | Validation                                         |
| ---------- | ------ | ----------------------------- | -------------------------------------------------- |
| `start`    | String | Start date/time in ISO format | Must be valid ISO date string                      |
| `end`      | String | End date/time in ISO format   | Must be valid ISO date string, must be after start |
| `type`     | String | Type of absence               | Must be one of: 'vacation', 'holiday', 'break'     |
| `timeZone` | String | IANA timezone identifier      | Must be valid timezone string                      |

### Optional Fields

| Field            | Type              | Description                              | Default |
| ---------------- | ----------------- | ---------------------------------------- | ------- |
| `note`           | String            | Description or notes about the absence   | null    |
| `entireBusiness` | Boolean           | Whether this affects the entire business | false   |
| `members`        | ObjectId\[]       | References to assigned members           | \[]     |
| `memberIds`      | String\[]         | Member IDs as strings                    | \[]     |
| `locations`      | LocationSchema\[] | Affected locations                       | \[]     |

### System Fields

| Field          | Type                  | Description                 | Auto-Generated |
| -------------- | --------------------- | --------------------------- | -------------- |
| `_id`          | ObjectId              | Primary key                 | Yes            |
| `meta`         | MetaSchema            | Metadata information        | Partial        |
| `stateHistory` | StateHistorySchema\[] | State change tracking       | Yes            |
| `createdAt`    | Date                  | Creation timestamp          | Yes            |
| `updatedAt`    | Date                  | Last modification timestamp | Yes            |

## Embedded Schemas

### LocationSchema

```typescript
{
  _id: ObjectId,
  name: String,
  address: String,
  coordinates: {
    latitude: Number,
    longitude: Number
  }
}
```

### MetaSchema

```typescript
{
  createdBy: ObjectId,
  updatedBy: ObjectId,
  version: Number,
  tags: [String],
  customFields: Map
}
```

### StateHistorySchema

```typescript
{
  state: String,           // Current state (active, deleted, etc.)
  timestamp: Date,         // When the state change occurred
  actor: ObjectId,         // Who made the change
  reason: String,          // Optional reason for the change
  metadata: Map            // Additional context
}
```

## Relationships

### Member Relationship

* **Type**: One-to-Many (Absence → Members)
* **Implementation**: Array of ObjectId references
* **Collection**: `member`
* **Lookup**: Populated during queries when member details are needed

```typescript
// Population example
absence.populate('members', 'name email role')
```

### Location Relationship

* **Type**: Embedded Documents
* **Implementation**: Array of LocationSchema subdocuments
* **Storage**: Denormalized within absence document

## Indexing Strategy

### Primary Indexes

```typescript
// Compound index for date range queries
db.absence.createIndex({ 
  "start": 1, 
  "end": 1 
});

// Index for member-based queries
db.absence.createIndex({ 
  "members": 1 
});

// Index for business-wide absences
db.absence.createIndex({ 
  "entireBusiness": 1 
});

// Index for type-based filtering
db.absence.createIndex({ 
  "type": 1 
});

// Soft delete index (state history)
db.absence.createIndex({ 
  "stateHistory.state": 1 
});

// Tenant-based partitioning (via meta.tenantId)
db.absence.createIndex({ 
  "meta.tenantId": 1 
});
```

### Composite Indexes

```typescript
// Optimal index for member-based date range queries
db.absence.createIndex({ 
  "members": 1, 
  "start": 1, 
  "end": 1 
});

// Index for tenant-specific queries
db.absence.createIndex({ 
  "meta.tenantId": 1, 
  "stateHistory.state": 1,
  "start": 1 
});
```

## Query Patterns

### Common Queries

#### Find Absences by Date Range

```typescript
// Find overlapping absences
db.absence.find({
  $or: [
    { start: { $gte: startDate, $lt: endDate } },
    { end: { $gt: startDate, $lte: endDate } },
    { start: { $lte: startDate }, end: { $gte: endDate } }
  ],
  $expr: {
    $ne: [
      { $arrayElemAt: ['$stateHistory.state', -1] },
      'deleted'
    ]
  }
});
```

#### Find Member-Specific Absences

```typescript
// Find absences for specific members or business-wide
db.absence.find({
  $or: [
    { members: { $in: memberIds } },
    { entireBusiness: true }
  ],
  $expr: {
    $ne: [
      { $arrayElemAt: ['$stateHistory.state', -1] },
      'deleted'
    ]
  }
});
```

#### Paginated Query with Population

```typescript
// Aggregation pipeline for paginated results
const pipeline = [
  // Match criteria
  {
    $match: {
      'meta.tenantId': tenantId,
      $expr: {
        $ne: [
          { $arrayElemAt: ['$stateHistory.state', -1] },
          'deleted'
        ]
      }
    }
  },
  // Lookup members
  {
    $lookup: {
      from: 'member',
      localField: 'members',
      foreignField: '_id',
      as: 'members'
    }
  },
  // Sort
  { $sort: { start: -1 } },
  // Pagination
  { $skip: (page - 1) * pageSize },
  { $limit: pageSize }
];
```

## Repository Implementation

### AbsenceRepository

The repository class extends `BaseRepository` and implements `IAbsenceRepository`:

```typescript
export class AbsenceRepository extends BaseRepository<IAbsence> implements IAbsenceRepository {
  constructor(
    public readonly model: Model<AbsenceDocument>,
    public readonly member: Model<MemberDocument>
  ) {
    super(model);
  }

  // Specialized query for busy slots
  public async getAbsencesWithFilter(
    params: GetBusySlotsWithFilterParams
  ): Promise<ISlot[]> {
    // Implementation details for conflict detection
  }
}
```

### Key Repository Methods

#### Standard CRUD Operations

* `createItem(absence: IAbsence): Promise<IAbsence>`
* `updateItem(id: string, absence: IAbsence): Promise<IAbsence>`
* `findById(id: string): Promise<IAbsence | null>`
* `deleteItem(id: string): Promise<void>` (soft delete)

#### Specialized Queries

* `getAbsencesWithFilter(params): Promise<ISlot[]>`
* `findByDateRange(start: string, end: string): Promise<IAbsence[]>`
* `findByMember(memberId: string): Promise<IAbsence[]>`

## Data Consistency

### Soft Delete Pattern

Absences use soft deletion through the state history mechanism:

```typescript
// Soft delete implementation
absence.stateHistory.push({
  state: EntityStateEnum.deleted,
  timestamp: new Date(),
  actor: actorId,
  reason: 'User requested deletion'
});

await absence.save();
```

### State History Tracking

All state changes are tracked in the `stateHistory` array:

* Initial state: 'active'
* Soft delete: 'deleted'
* Future states: 'archived', 'suspended', etc.

### Query Filtering

Non-deleted records are filtered using aggregation expressions:

```typescript
$expr: {
  $ne: [
    { $arrayElemAt: ['$stateHistory.state', -1] },
    'deleted'
  ]
}
```

## Multi-Tenancy

### Tenant Isolation

Data isolation is achieved through the `meta.tenantId` field:

* All queries must include tenant filter
* Indexes include `meta.tenantId` for performance
* Application layer enforces tenant boundary

### Tenant-Aware Queries

```typescript
// All repository methods include tenant context
async findById(id: string, tenantId: string): Promise<IAbsence | null> {
  return this.model.findOne({
    _id: id,
    'meta.tenantId': tenantId,
    $expr: {
      $ne: [
        { $arrayElemAt: ['$stateHistory.state', -1] },
        'deleted'
      ]
    }
  });
}
```

## Performance Considerations

### Query Optimization

1. **Index Usage**: Ensure queries use appropriate indexes
2. **Projection**: Select only needed fields
3. **Population**: Use selective field population
4. **Aggregation**: Use aggregation pipelines for complex queries

### Scaling Strategies

1. **Sharding**: Consider sharding by `meta.tenantId`
2. **Read Replicas**: Use read replicas for query-heavy operations
3. **Caching**: Implement Redis caching for frequently accessed data
4. **Archive Strategy**: Archive old absences to separate collection

## Data Migration

### Schema Evolution

When schema changes are needed:

1. **Backward Compatible**: Add optional fields
2. **Migration Scripts**: Create scripts for data transformation
3. **Versioning**: Use `meta.version` for schema versioning
4. **Rollback**: Maintain rollback procedures

### Example Migration Script

```javascript
// Add new field to existing documents
db.absence.updateMany(
  { newField: { $exists: false } },
  { $set: { newField: defaultValue } }
);
```

## 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. **Archived Data**: Move to archive collection after retention period
3. **Deleted Data**: Hard delete after legal retention requirements

## Monitoring and Alerts

### Database Metrics

* Query performance
* Index usage statistics
* Document growth rate
* Connection pool utilization

### Alert Conditions

* Slow queries (>100ms)
* Index misses
* High memory usage
* Replication lag

## Security Considerations

### Data Protection

1. **Encryption at Rest**: Enable MongoDB encryption
2. **Encryption in Transit**: Use TLS for connections
3. **Field-Level Encryption**: For sensitive data
4. **Access Control**: Role-based database access

### Audit Trail

State history provides audit trail for:

* Who modified the absence
* When changes occurred
* What the previous state was
* Reason for changes