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:

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

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

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

MetaSchema

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

StateHistorySchema

{
  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

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

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

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

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

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

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

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:

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

$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

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

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