> 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


---

# Agent Instructions
This documentation is published with GitBook. GitBook is the documentation platform designed so that both humans and AI agents can read, navigate, and reason over technical content effectively. Learn more at gitbook.com.

## Querying This Documentation
If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://docs.beeoclock.com/services/absence-management/db_schema.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.