Database Schema

Overview

This document describes the database schema for the Expense Management module in the Bee O'clock panel service. The schema is designed using MongoDB with Mongoose ODM, following Domain-Driven Design principles and implementing multi-tenancy, soft delete patterns, and comprehensive audit trails.

Database Design Principles

Multi-Tenancy

• All collections include tenantId for data isolation

• Business entity scoping with businessEntityId

• Automatic tenant filtering in all queries

Soft Delete Pattern

• Logical deletion using archivedAt timestamp

• Preservation of historical data for audit purposes

• Filtered queries exclude archived records by default

Audit Trail

• Automatic createdAt and updatedAt timestamps

• User context tracking for all modifications

• Immutable expense history for compliance

Performance Optimization

• Strategic indexing for common query patterns

• Compound indexes for multi-field filtering

• Text indexes for search functionality

Collections

1. Expenses Collection

Collection Name: expenses

Purpose: Stores comprehensive expense records with itemized breakdown and categorization.

Schema Definition

File: applications/panel.beeoclock/src/modules/expense/infrastructure/persistence/schemas/expense.scheme.ts

import { Prop, Schema, SchemaFactory } from '@nestjs/mongoose';
import { Document, Types } from 'mongoose';
import { BaseBeeoClockTenantIdSchema } from '@beeoclock/common/infrastructure/schemas/base.beeoclock.tenant.id.schema';
import { ExpenseValueSchema } from './expense-value.schema';
import { ExpenseItemSchema } from './expense-item.schema';

@Schema({
  collection: 'expenses',
  timestamps: true,
  versionKey: false,
  toJSON: { virtuals: true },
  toObject: { virtuals: true }
})
export class ExpenseSchema extends BaseBeeoClockTenantIdSchema {
  /**
   * Total monetary value of the expense
   * Embedded document containing amount and currency
   */
  @Prop({
    type: ExpenseValueSchema,
    required: true,
    validate: {
      validator: function(value: any) {
        return value && value.amount > 0;
      },
      message: 'Total value amount must be greater than zero'
    }
  })
  totalValue!: ExpenseValueSchema;

  /**
   * Date when the expense was incurred
   * Used for reporting and financial period categorization
   */
  @Prop({
    type: Date,
    required: true,
    index: true,
    validate: {
      validator: function(value: Date) {
        // Prevent expenses more than 5 years in the past or 30 days in the future
        const fiveYearsAgo = new Date();
        fiveYearsAgo.setFullYear(fiveYearsAgo.getFullYear() - 5);
        
        const thirtyDaysFromNow = new Date();
        thirtyDaysFromNow.setDate(thirtyDaysFromNow.getDate() + 30);
        
        return value >= fiveYearsAgo && value <= thirtyDaysFromNow;
      },
      message: 'Expense date must be within 5 years in the past and 30 days in the future'
    }
  })
  expensedAt!: Date;

  /**
   * Optional description of the expense
   * Supports full-text search for expense discovery
   */
  @Prop({
    type: String,
    maxlength: 1000,
    trim: true,
    index: 'text'
  })
  description?: string;

  /**
   * Array of itemized expense line items
   * Provides detailed breakdown of expense components
   */
  @Prop({
    type: [ExpenseItemSchema],
    default: [],
    validate: {
      validator: function(items: any[]) {
        if (!items || items.length === 0) {
          return true; // Simple expenses without items are allowed
        }
        
        // Validate that sum of items matches total value
        const itemsTotal = items.reduce((sum, item) => sum + item.itemValue.amount, 0);
        const tolerance = 0.01; // 1 cent tolerance for rounding
        
        return Math.abs(itemsTotal - this.totalValue.amount) <= tolerance;
      },
      message: 'Sum of expense items must match total expense amount'
    }
  })
  items?: ExpenseItemSchema[];

  /**
   * Archive timestamp for soft delete functionality
   * Null for active expenses, Date for archived expenses
   */
  @Prop({
    type: Date,
    default: null,
    index: true
  })
  archivedAt?: Date;
}

export type ExpenseDocument = ExpenseSchema & Document;
export const ExpenseSchemaDefinition = SchemaFactory.createForClass(ExpenseSchema);

// Pre-save middleware to validate business rules
ExpenseSchemaDefinition.pre('save', function(this: ExpenseDocument) {
  // Ensure currency consistency across items
  if (this.items && this.items.length > 0) {
    const baseCurrency = this.totalValue.currency;
    const invalidItems = this.items.filter(item => item.itemValue.currency !== baseCurrency);
    
    if (invalidItems.length > 0) {
      throw new Error(`All expense items must use the same currency: ${baseCurrency}`);
    }
  }
});

// Index definitions for optimal query performance
ExpenseSchemaDefinition.index({ tenantId: 1, expensedAt: -1 }); // Date range queries
ExpenseSchemaDefinition.index({ tenantId: 1, archivedAt: 1 }); // Active/archived filtering
ExpenseSchemaDefinition.index({ tenantId: 1, 'items.categories._id': 1 }); // Category filtering
ExpenseSchemaDefinition.index({ tenantId: 1, description: 'text' }); // Text search
ExpenseSchemaDefinition.index({ 
  tenantId: 1, 
  expensedAt: -1, 
  'totalValue.amount': -1 
}); // Reporting queries

Field Specifications

Field	Type	Required	Description	Constraints
_id	ObjectId	Yes	Primary key	Auto-generated MongoDB ObjectId
tenantId	String	Yes	Tenant identifier for multi-tenancy	Inherited from base schema
businessEntityId	String	Yes	Business entity identifier	Inherited from base schema
totalValue	ExpenseValueSchema	Yes	Total monetary amount	Must be positive, embedded document
expensedAt	Date	Yes	Date of expense occurrence	Within 5 years past to 30 days future
description	String	No	Expense description	Max 1000 characters, trimmed
items	ExpenseItemSchema[]	No	Itemized expense breakdown	Sum must match total value
archivedAt	Date	No	Soft delete timestamp	Null for active records
createdAt	Date	Yes	Record creation timestamp	Auto-managed by timestamps
updatedAt	Date	Yes	Last modification timestamp	Auto-managed by timestamps

Business Rules and Constraints

1. Amount Validation: Total value amount must be positive

2. Date Validation: Expense date within reasonable range (5 years past to 30 days future)

3. Currency Consistency: All items must use the same currency as total value

4. Sum Validation: Sum of item amounts must match total amount (within 1 cent tolerance)

5. Soft Delete: Archived expenses are excluded from standard queries

6. Text Search: Description field supports full-text search operations

2. Expense Categories Collection

Collection Name: expense-categories

Purpose: Stores categorization tags for organizing and filtering expenses.

Schema Definition

File: applications/panel.beeoclock/src/modules/expense/infrastructure/persistence/schemas/expense-category.scheme.ts

import { Prop, Schema, SchemaFactory } from '@nestjs/mongoose';
import { Document } from 'mongoose';
import { BaseBeeoClockTenantIdSchema } from '@beeoclock/common/infrastructure/schemas/base.beeoclock.tenant.id.schema';

@Schema({
  collection: 'expense-categories',
  timestamps: true,
  versionKey: false,
  toJSON: { virtuals: true },
  toObject: { virtuals: true }
})
export class ExpenseCategorySchema extends BaseBeeoClockTenantIdSchema {
  /**
   * Unique name of the expense category within tenant
   * Used for categorizing and filtering expenses
   */
  @Prop({
    type: String,
    required: true,
    trim: true,
    lowercase: true,
    maxlength: 50,
    validate: {
      validator: function(value: string) {
        // Alphanumeric characters, hyphens, underscores, and spaces only
        return /^[a-zA-Z0-9\-_\s]+$/.test(value);
      },
      message: 'Category name can only contain letters, numbers, hyphens, underscores, and spaces'
    }
  })
  name!: string;

  /**
   * Optional description of the category
   * Provides additional context for category usage
   */
  @Prop({
    type: String,
    maxlength: 500,
    trim: true
  })
  description?: string;

  /**
   * Archive timestamp for soft delete functionality
   * Null for active categories, Date for archived categories
   */
  @Prop({
    type: Date,
    default: null,
    index: true
  })
  archivedAt?: Date;
}

export type ExpenseCategoryDocument = ExpenseCategorySchema & Document;
export const ExpenseCategorySchemaDefinition = SchemaFactory.createForClass(ExpenseCategorySchema);

// Unique constraint for category name within tenant
ExpenseCategorySchemaDefinition.index(
  { tenantId: 1, name: 1 }, 
  { 
    unique: true,
    partialFilterExpression: { archivedAt: null } // Only enforce uniqueness for active categories
  }
);

// Index for efficient category listing
ExpenseCategorySchemaDefinition.index({ tenantId: 1, archivedAt: 1, name: 1 });

Field Specifications

Field	Type	Required	Description	Constraints
_id	ObjectId	Yes	Primary key	Auto-generated MongoDB ObjectId
tenantId	String	Yes	Tenant identifier	Inherited from base schema
businessEntityId	String	Yes	Business entity identifier	Inherited from base schema
name	String	Yes	Category name	Max 50 chars, alphanumeric/hyphens/underscores/spaces, lowercase
description	String	No	Category description	Max 500 characters, trimmed
archivedAt	Date	No	Soft delete timestamp	Null for active records
createdAt	Date	Yes	Record creation timestamp	Auto-managed
updatedAt	Date	Yes	Last modification timestamp	Auto-managed

Business Rules and Constraints

1. Name Uniqueness: Category names must be unique within a tenant (excluding archived)

2. Name Format: Only alphanumeric characters, hyphens, underscores, and spaces allowed

3. Case Insensitive: Names are stored in lowercase for consistency

4. Length Limits: Name limited to 50 characters, description to 500 characters

5. Soft Delete: Archived categories excluded from standard operations but preserve historical references

Embedded Schemas

1. Expense Value Schema

Purpose: Represents monetary amounts with currency information.

File: applications/panel.beeoclock/src/modules/expense/infrastructure/persistence/schemas/expense-value.schema.ts

import { Prop, Schema, SchemaFactory } from '@nestjs/mongoose';
import { CurrencyCodeEnum } from '@beeoclock/common/core/enum/currency.code.enum';

@Schema({
  _id: false, // Embedded document, no separate _id
  versionKey: false
})
export class ExpenseValueSchema {
  /**
   * Monetary amount in the specified currency
   * Stored as decimal with 2 decimal places precision
   */
  @Prop({
    type: Number,
    required: true,
    min: [0.01, 'Amount must be at least 0.01'],
    validate: {
      validator: function(value: number) {
        // Validate decimal places (max 2)
        return Number.isFinite(value) && Math.round(value * 100) === value * 100;
      },
      message: 'Amount must have at most 2 decimal places'
    }
  })
  amount!: number;

  /**
   * Currency code for the amount
   * Must be a valid ISO 4217 currency code
   */
  @Prop({
    type: String,
    required: true,
    enum: Object.values(CurrencyCodeEnum),
    uppercase: true
  })
  currency!: CurrencyCodeEnum;
}

export const ExpenseValueSchemaDefinition = SchemaFactory.createForClass(ExpenseValueSchema);

2. Expense Item Schema

Purpose: Represents individual line items within an expense.

File: applications/panel.beeoclock/src/modules/expense/infrastructure/persistence/schemas/expense-item.schema.ts

import { Prop, Schema, SchemaFactory } from '@nestjs/mongoose';
import { Types } from 'mongoose';
import { ExpenseValueSchema } from './expense-value.schema';
import { ExpenseSourceSchema } from './expense-source.schema';

@Schema({
  _id: false, // Embedded document, no separate _id
  versionKey: false
})
export class ExpenseItemSchema {
  /**
   * Array of category references for this expense item
   * Links to expense-categories collection
   */
  @Prop({
    type: [Types.ObjectId],
    ref: 'ExpenseCategory',
    required: true,
    validate: {
      validator: function(categories: Types.ObjectId[]) {
        return categories && categories.length > 0;
      },
      message: 'Expense item must have at least one category'
    }
  })
  categories!: Types.ObjectId[];

  /**
   * Monetary value for this specific line item
   * Must be positive and match currency of parent expense
   */
  @Prop({
    type: ExpenseValueSchema,
    required: true,
    validate: {
      validator: function(value: any) {
        return value && value.amount > 0;
      },
      message: 'Item value amount must be greater than zero'
    }
  })
  itemValue!: ExpenseValueSchema;

  /**
   * Description of this specific expense item
   * Required for clarity and audit purposes
   */
  @Prop({
    type: String,
    required: true,
    trim: true,
    maxlength: 500,
    validate: {
      validator: function(value: string) {
        return value && value.trim().length > 0;
      },
      message: 'Item description cannot be empty'
    }
  })
  description!: string;

  /**
   * Source information for this expense item
   * References the entity that generated this expense
   */
  @Prop({
    type: ExpenseSourceSchema,
    required: true
  })
  source!: ExpenseSourceSchema;
}

export const ExpenseItemSchemaDefinition = SchemaFactory.createForClass(ExpenseItemSchema);

3. Expense Source Schema

Purpose: Represents the source or origin of an expense item.

File: applications/panel.beeoclock/src/modules/expense/infrastructure/persistence/schemas/expense-source.schema.ts

import { Prop, Schema, SchemaFactory } from '@nestjs/mongoose';
import { ExpenseSourceTypeEnum } from '../../../domain/enums/expense-source-type.enum';

@Schema({
  _id: false, // Embedded document, no separate _id
  versionKey: false
})
export class ExpenseSourceSchema {
  /**
   * Identifier of the source entity
   * References different collections based on sourceType
   */
  @Prop({
    type: String,
    required: true,
    trim: true,
    validate: {
      validator: function(value: string) {
        return value && value.trim().length > 0;
      },
      message: 'Source ID cannot be empty'
    }
  })
  sourceId!: string;

  /**
   * Type of the source entity
   * Determines which collection the sourceId references
   */
  @Prop({
    type: String,
    required: true,
    enum: Object.values(ExpenseSourceTypeEnum)
  })
  sourceType!: ExpenseSourceTypeEnum;

  /**
   * Display name of the source entity
   * Cached for performance and display purposes
   */
  @Prop({
    type: String,
    trim: true,
    maxlength: 200
  })
  name?: string;

  /**
   * Optional description of the source entity
   * Additional context for the expense source
   */
  @Prop({
    type: String,
    trim: true,
    maxlength: 500
  })
  description?: string;
}

export const ExpenseSourceSchemaDefinition = SchemaFactory.createForClass(ExpenseSourceSchema);

Database Indexes

Primary Indexes

Expenses Collection

// Primary performance indexes for expenses
db.expenses.createIndex({ tenantId: 1, expensedAt: -1 }); // Date range queries
db.expenses.createIndex({ tenantId: 1, archivedAt: 1 }); // Active/archived filtering  
db.expenses.createIndex({ tenantId: 1, "items.categories": 1 }); // Category filtering
db.expenses.createIndex({ tenantId: 1, description: "text" }); // Full-text search
db.expenses.createIndex({ 
  tenantId: 1, 
  expensedAt: -1, 
  "totalValue.amount": -1 
}); // Financial reporting

// Compound index for complex queries
db.expenses.createIndex({
  tenantId: 1,
  archivedAt: 1,
  expensedAt: -1,
  "totalValue.currency": 1
}); // Multi-criteria filtering

Expense Categories Collection

// Unique constraint and performance indexes for categories
db.expenseCategories.createIndex(
  { tenantId: 1, name: 1 }, 
  { 
    unique: true,
    partialFilterExpression: { archivedAt: null }
  }
); // Unique active category names per tenant

db.expenseCategories.createIndex({ tenantId: 1, archivedAt: 1, name: 1 }); // Listing and filtering

Query Performance Optimization

Common Query Patterns

// 1. Get active expenses for tenant with date range
db.expenses.find({
  tenantId: "tenant123",
  archivedAt: null,
  expensedAt: {
    $gte: ISODate("2024-01-01T00:00:00.000Z"),
    $lte: ISODate("2024-12-31T23:59:59.999Z")
  }
}).sort({ expensedAt: -1 });

// 2. Search expenses by description
db.expenses.find({
  tenantId: "tenant123",
  archivedAt: null,
  $text: { $search: "office supplies" }
}).sort({ score: { $meta: "textScore" } });

// 3. Filter expenses by categories
db.expenses.find({
  tenantId: "tenant123",
  archivedAt: null,
  "items.categories": { $in: [ObjectId("..."), ObjectId("...")] }
});

// 4. Aggregate expense totals by category
db.expenses.aggregate([
  {
    $match: {
      tenantId: "tenant123",
      archivedAt: null,
      expensedAt: {
        $gte: ISODate("2024-01-01T00:00:00.000Z"),
        $lte: ISODate("2024-12-31T23:59:59.999Z")
      }
    }
  },
  { $unwind: "$items" },
  { $unwind: "$items.categories" },
  {
    $group: {
      _id: "$items.categories",
      totalAmount: { $sum: "$items.itemValue.amount" },
      count: { $sum: 1 }
    }
  }
]);

Data Validation and Constraints

Schema-Level Validation

// Custom validation functions
const validatePositiveAmount = {
  validator: function(value: number) {
    return value > 0;
  },
  message: 'Amount must be greater than zero'
};

const validateCurrencyConsistency = {
  validator: function(this: ExpenseDocument) {
    if (!this.items || this.items.length === 0) return true;
    
    const baseCurrency = this.totalValue.currency;
    return this.items.every(item => item.itemValue.currency === baseCurrency);
  },
  message: 'All expense items must use the same currency'
};

const validateTotalAmountMatch = {
  validator: function(this: ExpenseDocument) {
    if (!this.items || this.items.length === 0) return true;
    
    const itemsTotal = this.items.reduce((sum, item) => sum + item.itemValue.amount, 0);
    const tolerance = 0.01;
    
    return Math.abs(itemsTotal - this.totalValue.amount) <= tolerance;
  },
  message: 'Sum of expense items must match total expense amount'
};

Application-Level Constraints

// Repository-level validation
export class ExpenseRepository implements IExpenseRepository {
  async create(expense: IExpense): Promise<IExpense> {
    // Validate business rules before persistence
    this.validateExpenseBusinessRules(expense);
    
    // Additional database-specific validations
    await this.validateCategoryReferences(expense);
    await this.validateSourceReferences(expense);
    
    return await this.expenseModel.create(expense);
  }

  private async validateCategoryReferences(expense: IExpense): Promise<void> {
    if (!expense.items) return;
    
    const categoryIds = expense.items.flatMap(item => 
      item.categories.map(cat => cat._id)
    ).filter(Boolean);
    
    const existingCategories = await this.categoryModel.find({
      _id: { $in: categoryIds },
      tenantId: expense.tenantId,
      archivedAt: null
    });
    
    if (existingCategories.length !== categoryIds.length) {
      throw new BadRequestException('One or more expense categories do not exist or are archived');
    }
  }

  private async validateSourceReferences(expense: IExpense): Promise<void> {
    if (!expense.items) return;
    
    // Validate that all referenced sources exist in their respective collections
    for (const item of expense.items) {
      await this.validateSourceExists(item.source, expense.tenantId);
    }
  }
}

Migration Scripts

Initial Schema Creation

// Migration: 001_create_expense_collections.ts
export class CreateExpenseCollections001 {
  async up(db: Db): Promise<void> {
    // Create expenses collection with validation
    await db.createCollection('expenses', {
      validator: {
        $jsonSchema: {
          bsonType: 'object',
          required: ['tenantId', 'businessEntityId', 'totalValue', 'expensedAt'],
          properties: {
            tenantId: { bsonType: 'string' },
            businessEntityId: { bsonType: 'string' },
            totalValue: {
              bsonType: 'object',
              required: ['amount', 'currency'],
              properties: {
                amount: { bsonType: 'number', minimum: 0.01 },
                currency: { bsonType: 'string' }
              }
            },
            expensedAt: { bsonType: 'date' },
            description: { bsonType: 'string', maxLength: 1000 },
            archivedAt: { bsonType: ['date', 'null'] }
          }
        }
      }
    });

    // Create expense-categories collection
    await db.createCollection('expense-categories', {
      validator: {
        $jsonSchema: {
          bsonType: 'object',
          required: ['tenantId', 'businessEntityId', 'name'],
          properties: {
            tenantId: { bsonType: 'string' },
            businessEntityId: { bsonType: 'string' },
            name: { bsonType: 'string', maxLength: 50 },
            description: { bsonType: 'string', maxLength: 500 },
            archivedAt: { bsonType: ['date', 'null'] }
          }
        }
      }
    });

    // Create indexes
    await this.createIndexes(db);
  }

  async down(db: Db): Promise<void> {
    await db.dropCollection('expenses');
    await db.dropCollection('expense-categories');
  }

  private async createIndexes(db: Db): Promise<void> {
    const expensesCollection = db.collection('expenses');
    const categoriesCollection = db.collection('expense-categories');

    // Expenses indexes
    await expensesCollection.createIndex({ tenantId: 1, expensedAt: -1 });
    await expensesCollection.createIndex({ tenantId: 1, archivedAt: 1 });
    await expensesCollection.createIndex({ tenantId: 1, 'items.categories': 1 });
    await expensesCollection.createIndex({ tenantId: 1, description: 'text' });

    // Categories indexes
    await categoriesCollection.createIndex(
      { tenantId: 1, name: 1 }, 
      { 
        unique: true,
        partialFilterExpression: { archivedAt: null }
      }
    );
    await categoriesCollection.createIndex({ tenantId: 1, archivedAt: 1, name: 1 });
  }
}

Sample Data Migration

// Migration: 002_seed_default_expense_categories.ts
export class SeedDefaultExpenseCategories002 {
  async up(db: Db): Promise<void> {
    const defaultCategories = [
      {
        name: 'salary',
        description: 'Employee salary and compensation expenses'
      },
      {
        name: 'utilities',
        description: 'Utility bills and facility services'
      },
      {
        name: 'office-supplies',
        description: 'General office supplies and materials'
      },
      {
        name: 'marketing',
        description: 'Marketing and advertising expenses'
      },
      {
        name: 'travel',
        description: 'Business travel and transportation expenses'
      },
      {
        name: 'training',
        description: 'Employee training and professional development'
      },
      {
        name: 'insurance',
        description: 'Business insurance premiums and coverage'
      },
      {
        name: 'maintenance',
        description: 'Facility and equipment maintenance costs'
      },
      {
        name: 'technology',
        description: 'Technology equipment and software expenses'
      },
      {
        name: 'professional-services',
        description: 'Legal, accounting, and consulting services'
      }
    ];

    // Get all active tenants to seed categories for each
    const tenants = await db.collection('tenants').find({ archivedAt: null }).toArray();

    for (const tenant of tenants) {
      const categoriesWithTenantId = defaultCategories.map(category => ({
        ...category,
        tenantId: tenant._id.toString(),
        businessEntityId: tenant.businessEntityId,
        createdAt: new Date(),
        updatedAt: new Date(),
        archivedAt: null
      }));

      await db.collection('expense-categories').insertMany(categoriesWithTenantId);
    }
  }

  async down(db: Db): Promise<void> {
    const defaultCategoryNames = [
      'salary', 'utilities', 'office-supplies', 'marketing', 'travel',
      'training', 'insurance', 'maintenance', 'technology', 'professional-services'
    ];

    await db.collection('expense-categories').deleteMany({
      name: { $in: defaultCategoryNames }
    });
  }
}

Backup and Recovery

Backup Strategy

// Automated backup configuration
export class ExpenseBackupService {
  async createBackup(): Promise<void> {
    const timestamp = new Date().toISOString().replace(/[:.]/g, '-');
    
    // Backup expenses collection
    await this.mongoService.backup({
      collection: 'expenses',
      outputFile: `expenses_backup_${timestamp}.json`,
      query: {} // Full backup
    });

    // Backup expense categories
    await this.mongoService.backup({
      collection: 'expense-categories',
      outputFile: `expense_categories_backup_${timestamp}.json`,
      query: {}
    });

    // Create incremental backup (last 24 hours)
    const yesterday = new Date();
    yesterday.setDate(yesterday.getDate() - 1);

    await this.mongoService.backup({
      collection: 'expenses',
      outputFile: `expenses_incremental_${timestamp}.json`,
      query: {
        $or: [
          { createdAt: { $gte: yesterday } },
          { updatedAt: { $gte: yesterday } }
        ]
      }
    });
  }

  async restoreFromBackup(backupFile: string): Promise<void> {
    // Validate backup file integrity
    await this.validateBackupFile(backupFile);
    
    // Create restore point before restoration
    await this.createRestorePoint();
    
    // Restore data with transaction safety
    await this.mongoService.restore({
      inputFile: backupFile,
      dropCollection: false, // Merge with existing data
      validateReferences: true
    });
  }
}

Data Retention Policy

// Data retention configuration
export class ExpenseDataRetentionService {
  async applyRetentionPolicy(): Promise<void> {
    // Archive expenses older than 7 years
    const sevenYearsAgo = new Date();
    sevenYearsAgo.setFullYear(sevenYearsAgo.getFullYear() - 7);

    await this.expenseModel.updateMany(
      {
        expensedAt: { $lt: sevenYearsAgo },
        archivedAt: null
      },
      {
        $set: { 
          archivedAt: new Date(),
          archiveReason: 'DATA_RETENTION_POLICY'
        }
      }
    );

    // Permanently delete archived expenses older than 10 years
    const tenYearsAgo = new Date();
    tenYearsAgo.setFullYear(tenYearsAgo.getFullYear() - 10);

    const expensesToDelete = await this.expenseModel.find({
      archivedAt: { $lt: tenYearsAgo }
    }).select('_id');

    // Create audit log before deletion
    await this.createDeletionAuditLog(expensesToDelete);

    // Permanently delete old archived records
    await this.expenseModel.deleteMany({
      archivedAt: { $lt: tenYearsAgo }
    });
  }

  private async createDeletionAuditLog(expenses: any[]): Promise<void> {
    const deletionRecord = {
      action: 'PERMANENT_DELETION',
      collection: 'expenses',
      deletedCount: expenses.length,
      deletedIds: expenses.map(exp => exp._id),
      deletionDate: new Date(),
      reason: 'DATA_RETENTION_POLICY'
    };

    await this.auditLogModel.create(deletionRecord);
  }
}

Performance Monitoring

Query Performance Analysis

// Performance monitoring for expense queries
export class ExpenseQueryPerformanceMonitor {
  async analyzeQueryPerformance(): Promise<QueryPerformanceReport> {
    const db = this.mongoService.getDatabase();
    
    // Enable profiling for slow queries
    await db.admin().command({
      profile: 2,
      slowms: 100 // Log queries slower than 100ms
    });

    // Analyze common query patterns
    const performanceMetrics = await Promise.all([
      this.analyzeExpenseListingPerformance(),
      this.analyzeExpenseSearchPerformance(),
      this.analyzeCategoryFilteringPerformance(),
      this.analyzeAggregationPerformance()
    ]);

    return {
      timestamp: new Date(),
      metrics: performanceMetrics,
      recommendations: this.generateOptimizationRecommendations(performanceMetrics)
    };
  }

  private async analyzeExpenseListingPerformance(): Promise<QueryMetrics> {
    const startTime = Date.now();
    
    await this.expenseModel.find({
      tenantId: 'sample-tenant',
      archivedAt: null
    })
    .sort({ expensedAt: -1 })
    .limit(20)
    .explain('executionStats');

    return {
      queryType: 'EXPENSE_LISTING',
      executionTime: Date.now() - startTime,
      indexesUsed: ['tenantId_1_expensedAt_-1'],
      documentsExamined: 20,
      documentsReturned: 20
    };
  }

  private generateOptimizationRecommendations(metrics: QueryMetrics[]): string[] {
    const recommendations: string[] = [];

    metrics.forEach(metric => {
      if (metric.executionTime > 100) {
        recommendations.push(`Consider optimizing ${metric.queryType} - execution time: ${metric.executionTime}ms`);
      }

      if (metric.documentsExamined / metric.documentsReturned > 10) {
        recommendations.push(`${metric.queryType} has poor selectivity - consider additional indexes`);
      }
    });

    return recommendations;
  }
}

Security Considerations

Data Encryption

// Field-level encryption for sensitive data
export class ExpenseSecurityService {
  private readonly encryptionKey: string;

  constructor() {
    this.encryptionKey = process.env.EXPENSE_ENCRYPTION_KEY!;
  }

  async encryptSensitiveFields(expense: ExpenseDocument): Promise<void> {
    // Encrypt monetary amounts for additional security
    if (expense.totalValue) {
      expense.totalValue.amount = await this.encrypt(expense.totalValue.amount.toString());
    }

    // Encrypt descriptions that might contain sensitive information
    if (expense.description) {
      expense.description = await this.encrypt(expense.description);
    }

    // Encrypt item details
    if (expense.items) {
      for (const item of expense.items) {
        item.itemValue.amount = await this.encrypt(item.itemValue.amount.toString());
        item.description = await this.encrypt(item.description);
      }
    }
  }

  async decryptSensitiveFields(expense: ExpenseDocument): Promise<void> {
    // Decrypt monetary amounts
    if (expense.totalValue) {
      expense.totalValue.amount = parseFloat(await this.decrypt(expense.totalValue.amount as any));
    }

    // Decrypt descriptions
    if (expense.description) {
      expense.description = await this.decrypt(expense.description);
    }

    // Decrypt item details
    if (expense.items) {
      for (const item of expense.items) {
        item.itemValue.amount = parseFloat(await this.decrypt(item.itemValue.amount as any));
        item.description = await this.decrypt(item.description);
      }
    }
  }

  private async encrypt(data: string): Promise<string> {
    // Implementation using crypto library
    // ... encryption logic
  }

  private async decrypt(data: string): Promise<string> {
    // Implementation using crypto library
    // ... decryption logic
  }
}

Access Control

// Database-level access control
export class ExpenseDatabaseSecurity {
  async setupTenantLevelSecurity(): Promise<void> {
    const db = this.mongoService.getDatabase();

    // Create role for expense management with tenant restrictions
    await db.admin().command({
      createRole: 'expenseManager',
      privileges: [
        {
          resource: { db: 'beeoclock', collection: 'expenses' },
          actions: ['find', 'insert', 'update', 'remove']
        },
        {
          resource: { db: 'beeoclock', collection: 'expense-categories' },
          actions: ['find', 'insert', 'update']
        }
      ],
      roles: []
    });

    // Create user with tenant-specific restrictions
    await db.admin().command({
      createUser: 'expense_service',
      pwd: process.env.EXPENSE_DB_PASSWORD,
      roles: ['expenseManager']
    });
  }

  async createTenantSpecificViews(): Promise<void> {
    // Create views that automatically filter by tenant
    const db = this.mongoService.getDatabase();

    await db.createCollection('tenant_expenses', {
      viewOn: 'expenses',
      pipeline: [
        {
          $match: {
            tenantId: '$$tenantId',
            archivedAt: null
          }
        }
      ]
    });
  }
}

This comprehensive database schema documentation provides detailed insights into the storage structure, performance optimization, security measures, and operational considerations for the expense management system. The schema is designed to be scalable, secure, and performant while maintaining data integrity and supporting complex business requirements.