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

# Database Schema

## Overview

This document provides comprehensive details about the database schema design for the Product Management module. The schema is implemented using MongoDB with Mongoose ODM, following document-oriented design principles with embedded sub-documents and strategic referencing for optimal performance.

## Table of Contents

1. [Schema Architecture](#schema-architecture)
2. [Product Schema](#product-schema)
3. [Product Tag Schema](#product-tag-schema)
4. [Price Sub-Schema](#price-sub-schema)
5. [Indexing Strategy](#indexing-strategy)
6. [Relationships and References](#relationships-and-references)
7. [Data Validation](#data-validation)
8. [Performance Optimization](#performance-optimization)
9. [Multi-Tenancy Implementation](#multi-tenancy-implementation)
10. [Audit and State Management](#audit-and-state-management)

## Schema Architecture

### Document-Oriented Design

The product management schema leverages MongoDB's document-oriented nature for efficient data storage and retrieval:

* **Embedded Documents**: Language versions and pricing information are embedded for atomic operations
* **Reference Strategy**: Media files and large collections use references for flexibility
* **Denormalization**: Strategic denormalization for performance optimization
* **Schema Validation**: Mongoose schema validation ensures data integrity

### Collections Overview

```
├── products                 # Main product collection
├── product-tags            # Product tag collection  
├── media                   # Media files (images, videos)
└── state-history           # Audit trail sub-documents
```

## Product Schema

### Main Product Document

**File:** `common/module/product/product/repos/product.scheme.ts`

```typescript
export const ProductSchema = new Schema<IProduct>({
  sku: { 
    type: String, 
    required: true,
    unique: true,
    index: true,
    trim: true,
    maxlength: 100
  },
  
  languageVersions: { 
    type: [LanguageVersionSchema], 
    required: true,
    validate: {
      validator: function(v: any[]) {
        return v && v.length > 0;
      },
      message: 'At least one language version is required'
    }
  },
  
  price: { 
    type: ProductPriceSchema, 
    required: true 
  },
  
  tags: { 
    type: [String], 
    required: false,
    index: true,
    validate: {
      validator: function(v: string[]) {
        return !v || v.length <= 20;
      },
      message: 'Maximum 20 tags allowed per product'
    }
  },
  
  images: [{ 
    type: Schema.Types.ObjectId, 
    ref: Media.name,
    validate: {
      validator: function(v: any[]) {
        return !v || v.length <= 10;
      },
      message: 'Maximum 10 images allowed per product'
    }
  }],
  
  order: { 
    type: Number, 
    required: false,
    min: 0,
    max: 999999,
    index: true
  },
  
  stateHistory: { 
    type: [StateHistorySchema], 
    default: [] 
  }
}, {
  timestamps: true,                          // Automatic createdAt/updatedAt
  collection: Product.name.toLowerCase(),    // Collection name: 'product'
  versionKey: false                          // Disable __v field
});
```

### Field Specifications

#### SKU (Stock Keeping Unit)

```typescript
sku: {
  type: String,
  required: true,
  unique: true,              // Unique constraint within collection
  index: true,               // Indexed for fast lookups
  trim: true,                // Remove whitespace
  maxlength: 100,            // Maximum length constraint
  match: /^[A-Z0-9-_]{1,100}$/ // Alphanumeric with hyphens/underscores
}
```

**Business Rules:**

* Must be unique across all products within the tenant
* Cannot be modified after product creation
* Used for external system integration
* Format: Uppercase letters, numbers, hyphens, underscores only

#### Language Versions

```typescript
languageVersions: {
  type: [LanguageVersionSchema],
  required: true,
  validate: {
    validator: (v: any[]) => v && v.length > 0,
    message: 'At least one language version is required'
  }
}
```

**Structure:**

```typescript
interface LanguageVersion {
  language: string;          // ISO language code (en, es, fr, etc.)
  title: string;             // Product title in specific language
  description?: string;      // Product description in specific language
}
```

**Business Rules:**

* At least one language version is mandatory
* Language codes must be valid ISO codes
* Titles are required for each language version
* Descriptions are optional but recommended
* Maximum 10 language versions per product

#### Pricing Information

```typescript
price: {
  type: ProductPriceSchema,
  required: true,
  validate: {
    validator: function(price: any) {
      return price && price.value > 0;
    },
    message: 'Price must be positive'
  }
}
```

#### Tags Array

```typescript
tags: {
  type: [String],
  required: false,
  index: true,               // Indexed for filtering
  validate: {
    validator: (v: string[]) => !v || v.length <= 20,
    message: 'Maximum 20 tags allowed'
  }
}
```

**Tag Constraints:**

* Maximum 20 tags per product
* Each tag must exist in the product-tag collection
* Tags are case-insensitive for matching
* Indexed for efficient filtering operations

#### Images Array

```typescript
images: [{
  type: Schema.Types.ObjectId,
  ref: Media.name,           // Reference to Media collection
  validate: {
    validator: (v: any[]) => !v || v.length <= 10,
    message: 'Maximum 10 images allowed'
  }
}]
```

**Image Constraints:**

* Maximum 10 images per product
* References stored as ObjectIds to Media collection
* Images are populated on query when needed
* Automatic cleanup when images are deleted

#### Display Order

```typescript
order: {
  type: Number,
  required: false,
  min: 0,                    // Non-negative values
  max: 999999,               // Reasonable upper limit
  index: true,               // Indexed for sorting
  default: null              // Default to end of list
}
```

### State History

```typescript
stateHistory: {
  type: [StateHistorySchema],
  default: [],
  select: false              // Excluded from queries by default
}
```

**Audit Trail Features:**

* Tracks all changes to product data
* Includes user information and timestamps
* Stores previous values for rollback capability
* Used for compliance and debugging

## Product Tag Schema

### Tag Document Structure

**File:** `common/module/product/tag/repos/product-tag.scheme.ts`

```typescript
export const TagSchema = new Schema<ITag>({
  name: { 
    type: String, 
    required: true,
    unique: true,              // Unique within tenant
    index: true,               // Indexed for fast lookups
    trim: true,                // Remove whitespace
    lowercase: true,           // Convert to lowercase
    maxlength: 50,             // Maximum length
    match: /^[a-z0-9-_\s]+$/   // Alphanumeric with hyphens, underscores, spaces
  },
  
  stateHistory: { 
    type: [StateHistorySchema], 
    default: [] 
  }
}, {
  timestamps: true,
  collection: 'product-tag',   // Explicit collection name
  versionKey: false
});
```

### Tag Validation Rules

#### Name Constraints

```typescript
name: {
  type: String,
  required: true,
  unique: true,               // Unique constraint
  trim: true,                 // Remove leading/trailing whitespace
  lowercase: true,            // Convert to lowercase for consistency
  minlength: 1,               // Minimum length
  maxlength: 50,              // Maximum length
  match: /^[a-z0-9-_\s]+$/   // Allowed characters
}
```

**Business Rules:**

* Tag names must be unique within the tenant
* Automatically converted to lowercase for consistency
* Only alphanumeric characters, hyphens, underscores, and spaces allowed
* Minimum 1 character, maximum 50 characters
* Leading and trailing whitespace automatically trimmed

### Tag Usage Tracking

```typescript
// Virtual field for usage count
TagSchema.virtual('usageCount', {
  ref: 'Product',
  localField: 'name',
  foreignField: 'tags',
  count: true
});
```

## Price Sub-Schema

### Price Document Structure

**File:** `common/module/product/product/repos/sub/product-price.scheme.ts`

```typescript
export const ProductPriceSchema = new Schema<IProductPrice>({
  value: { 
    type: Number,
    required: true,
    min: 0,                    // Non-negative values only
    max: 999999.99,            // Reasonable upper limit
    validate: {
      validator: function(v: number) {
        return Number.isFinite(v) && v >= 0;
      },
      message: 'Price must be a finite non-negative number'
    }
  },
  
  currency: { 
    type: String, 
    enum: Object.values(CurrencyCodeEnum),
    required: true,
    default: CurrencyCodeEnum.USD
  }
}, {
  timestamps: true,           // Track price change history
  _id: false                  // No separate _id for sub-document
});
```

### Currency Support

```typescript
enum CurrencyCodeEnum {
  USD = 'USD',               // US Dollar
  EUR = 'EUR',               // Euro
  GBP = 'GBP',               // British Pound
  CAD = 'CAD',               // Canadian Dollar
  AUD = 'AUD',               // Australian Dollar
  JPY = 'JPY',               // Japanese Yen
  CHF = 'CHF',               // Swiss Franc
  CNY = 'CNY',               // Chinese Yuan
  INR = 'INR',               // Indian Rupee
  BRL = 'BRL',               // Brazilian Real
  MXN = 'MXN',               // Mexican Peso
  ZAR = 'ZAR'                // South African Rand
}
```

### Price Validation

* **Value Range**: 0 to 999,999.99 (adjustable based on business needs)
* **Precision**: Two decimal places for monetary values
* **Currency Validation**: Must be from supported currency enum
* **Change Tracking**: Timestamps track when prices are modified

## Indexing Strategy

### Product Collection Indexes

#### Primary Indexes

```typescript
// Unique indexes
ProductSchema.index({ sku: 1 }, { unique: true });
ProductSchema.index({ _id: 1 });              // Default MongoDB index

// Query optimization indexes
ProductSchema.index({ tags: 1 });             // Tag filtering
ProductSchema.index({ order: 1 });            // Sorting by display order
ProductSchema.index({ createdAt: -1 });       // Recent products first
ProductSchema.index({ updatedAt: -1 });       // Recently updated

// Text search index
ProductSchema.index({
  'languageVersions.title': 'text',
  'languageVersions.description': 'text',
  'sku': 'text'
}, {
  name: 'product_text_search',
  weights: {
    'languageVersions.title': 10,    // Higher weight for titles
    'languageVersions.description': 5,
    'sku': 8
  }
});
```

#### Compound Indexes

```typescript
// Multi-field filtering
ProductSchema.index({ tags: 1, order: 1 });   // Tag + order filtering
ProductSchema.index({ tags: 1, createdAt: -1 }); // Tag + recency
ProductSchema.index({ order: 1, createdAt: -1 }); // Order + recency

// Tenant-specific indexes (for multi-tenancy)
ProductSchema.index({ tenantId: 1, sku: 1 }, { unique: true });
ProductSchema.index({ tenantId: 1, tags: 1 });
ProductSchema.index({ tenantId: 1, order: 1 });
```

### Tag Collection Indexes

```typescript
// Primary indexes for tags
TagSchema.index({ name: 1 }, { unique: true });
TagSchema.index({ _id: 1 });

// Search and filtering
TagSchema.index({ name: 'text' });            // Text search on tag names
TagSchema.index({ createdAt: -1 });           // Recently created tags
TagSchema.index({ updatedAt: -1 });           // Recently updated tags

// Multi-tenant indexes
TagSchema.index({ tenantId: 1, name: 1 }, { unique: true });
```

### Index Performance Considerations

#### Query Patterns Optimized

1. **Product Lookup by SKU**: O(1) lookup with unique index
2. **Tag Filtering**: Efficient array index scanning
3. **Text Search**: Full-text search across multiple language versions
4. **Sorting**: Pre-sorted indexes for order and date fields
5. **Pagination**: Efficient skip/limit operations

#### Index Maintenance

* **Selective Indexing**: Only indexes that support actual query patterns
* **Index Monitoring**: Regular analysis of index usage and performance
* **Compound Index Optimization**: Left-prefix rule consideration
* **Memory Usage**: Balance between query performance and memory consumption

## Relationships and References

### Product-Media Relationship

#### Reference Strategy

```typescript
// Product references media files
images: [{
  type: Schema.Types.ObjectId,
  ref: 'Media'
}]

// Population query
Product.findById(id).populate('images');
```

#### Media Document Structure

```typescript
interface MediaDocument {
  _id: ObjectId;
  url: string;               // CDN URL
  type: 'image' | 'video';   // Media type
  mimeType: string;          // MIME type
  size: number;              // File size in bytes
  alt?: string;              // Alternative text
  metadata: {                // Image metadata
    width?: number;
    height?: number;
    format?: string;
  };
  createdAt: Date;
  updatedAt: Date;
}
```

### Product-Tag Relationship

#### Embedding Strategy

```typescript
// Tags are embedded as strings in products
tags: [String]             // Array of tag names

// Tag names reference the Tag collection
const tagDoc = await Tag.findOne({ name: tagName });
```

#### Benefits of This Approach

* **Performance**: Fast filtering without joins
* **Simplicity**: Direct array operations for tag management
* **Consistency**: Tag names are normalized in Tag collection
* **Flexibility**: Easy to add/remove tags from products

### Multi-Tenant Relationships

#### Tenant Isolation

```typescript
// All documents include tenant identifier
interface TenantDocument {
  tenantId: ObjectId;        // Reference to tenant
  // ... other fields
}

// Query patterns always include tenant filter
Product.find({ tenantId: currentTenantId });
```

## Data Validation

### Schema-Level Validation

#### Built-in Validators

```typescript
{
  sku: {
    type: String,
    required: [true, 'SKU is required'],
    unique: true,
    minlength: [1, 'SKU must be at least 1 character'],
    maxlength: [100, 'SKU cannot exceed 100 characters'],
    match: [/^[A-Z0-9-_]+$/, 'SKU can only contain uppercase letters, numbers, hyphens, and underscores']
  },
  
  'price.value': {
    type: Number,
    required: [true, 'Price value is required'],
    min: [0, 'Price must be non-negative'],
    max: [999999.99, 'Price cannot exceed 999,999.99']
  }
}
```

#### Custom Validators

```typescript
// Language version validation
languageVersions: {
  type: [LanguageVersionSchema],
  validate: {
    validator: function(versions: any[]) {
      if (!versions || versions.length === 0) {
        return false;
      }
      
      // Check for duplicate languages
      const languages = versions.map(v => v.language);
      return languages.length === new Set(languages).size;
    },
    message: 'Language versions must be unique and at least one is required'
  }
}

// Tag validation
tags: {
  type: [String],
  validate: {
    validator: async function(tags: string[]) {
      if (!tags || tags.length === 0) return true;
      
      // Verify all tags exist
      const existingTags = await Tag.find({ name: { $in: tags } });
      return existingTags.length === tags.length;
    },
    message: 'All tags must exist in the tag collection'
  }
}
```

### Application-Level Validation

#### Business Rule Validation

```typescript
// Pre-save middleware for business rules
ProductSchema.pre('save', async function(next) {
  const product = this;
  
  // SKU uniqueness within tenant
  if (product.isModified('sku')) {
    const existing = await Product.findOne({
      tenantId: product.tenantId,
      sku: product.sku,
      _id: { $ne: product._id }
    });
    
    if (existing) {
      return next(new Error('SKU must be unique within tenant'));
    }
  }
  
  // Price change validation
  if (product.isModified('price') && !product.isNew) {
    // Log price changes for audit
    product.stateHistory.push({
      field: 'price',
      oldValue: product.getOriginal('price'),
      newValue: product.price,
      changedBy: product.modifiedBy,
      changedAt: new Date()
    });
  }
  
  next();
});
```

## Performance Optimization

### Query Optimization Patterns

#### Efficient Product Queries

```typescript
// Optimized product listing with projection
Product.find(
  { tenantId, tags: { $in: filterTags } },
  { 
    sku: 1, 
    'languageVersions.title': 1, 
    'price.value': 1, 
    order: 1 
  }
).sort({ order: 1, createdAt: -1 }).limit(20);

// Populated query with select
Product.findById(id)
  .populate('images', 'url alt metadata')
  .select('-stateHistory');
```

#### Pagination Optimization

```typescript
// Cursor-based pagination for better performance
const cursor = req.query.cursor;
const query = cursor 
  ? { _id: { $gt: cursor }, tenantId }
  : { tenantId };

Product.find(query)
  .sort({ _id: 1 })
  .limit(pageSize + 1);
```

### Aggregation Pipelines

#### Product Statistics

```typescript
// Product count by tag
Product.aggregate([
  { $match: { tenantId: ObjectId(tenantId) } },
  { $unwind: '$tags' },
  { $group: { _id: '$tags', count: { $sum: 1 } } },
  { $sort: { count: -1 } }
]);

// Price statistics
Product.aggregate([
  { $match: { tenantId: ObjectId(tenantId) } },
  { $group: {
    _id: '$price.currency',
    avgPrice: { $avg: '$price.value' },
    minPrice: { $min: '$price.value' },
    maxPrice: { $max: '$price.value' },
    count: { $sum: 1 }
  }}
]);
```

### Memory and Storage Optimization

#### Document Size Management

* **Embedded vs Referenced**: Strategic choice based on access patterns
* **Field Selection**: Use projection to limit transferred data
* **Archive Strategy**: Move old products to archive collection
* **Image Optimization**: Store only references, actual files in CDN

#### Connection Pooling

```typescript
// Optimized MongoDB connection
mongoose.connect(uri, {
  maxPoolSize: 10,           // Maximum connections
  serverSelectionTimeoutMS: 5000,
  socketTimeoutMS: 45000,
  bufferMaxEntries: 0        // Disable mongoose buffering
});
```

## Multi-Tenancy Implementation

### Tenant Isolation Strategy

#### Document-Level Isolation

```typescript
// All schemas include tenant identifier
const baseSchema = {
  tenantId: {
    type: Schema.Types.ObjectId,
    required: true,
    ref: 'Tenant',
    index: true
  }
};

// Merge with specific schema
const ProductSchema = new Schema({
  ...baseSchema,
  sku: { /* field definition */ },
  // ... other fields
});
```

#### Query Middleware

```typescript
// Automatic tenant filtering
ProductSchema.pre(/^find/, function() {
  if (!this.getQuery().tenantId) {
    throw new Error('Tenant ID is required for all queries');
  }
});

// Ensure tenant ID on save
ProductSchema.pre('save', function(next) {
  if (!this.tenantId) {
    return next(new Error('Tenant ID is required'));
  }
  next();
});
```

### Tenant-Specific Indexes

```typescript
// Compound indexes starting with tenantId
ProductSchema.index({ tenantId: 1, sku: 1 }, { unique: true });
ProductSchema.index({ tenantId: 1, tags: 1 });
ProductSchema.index({ tenantId: 1, order: 1 });
ProductSchema.index({ tenantId: 1, createdAt: -1 });

TagSchema.index({ tenantId: 1, name: 1 }, { unique: true });
```

### Data Security

#### Access Control

* **Tenant Validation**: Every operation validates tenant access
* **Data Isolation**: Queries automatically scoped to tenant
* **Audit Logging**: All operations logged with tenant context
* **Permission Checking**: User permissions validated within tenant scope

## Audit and State Management

### State History Schema

#### StateHistory Sub-Document

```typescript
const StateHistorySchema = new Schema({
  field: {
    type: String,
    required: true
  },
  oldValue: {
    type: Schema.Types.Mixed,   // Any type for flexibility
    required: false
  },
  newValue: {
    type: Schema.Types.Mixed,
    required: false
  },
  changedBy: {
    type: Schema.Types.ObjectId,
    ref: 'User',
    required: true
  },
  changedAt: {
    type: Date,
    required: true,
    default: Date.now
  },
  reason: {
    type: String,
    required: false,
    maxlength: 500
  }
}, {
  _id: true,                    // Each history entry has its own ID
  timestamps: false             // Use changedAt instead
});
```

### Change Tracking Implementation

#### Automatic Change Detection

```typescript
ProductSchema.pre('save', function(next) {
  const product = this;
  const modifiedPaths = product.modifiedPaths();
  
  modifiedPaths.forEach(path => {
    if (path !== 'stateHistory' && path !== 'updatedAt') {
      const oldValue = product.getOriginal(path);
      const newValue = product.get(path);
      
      product.stateHistory.push({
        field: path,
        oldValue: oldValue,
        newValue: newValue,
        changedBy: product.modifiedBy || product.createdBy,
        changedAt: new Date(),
        reason: product.changeReason
      });
    }
  });
  
  next();
});
```

### Compliance Features

#### Data Retention

* **Audit Trail**: Complete history of all changes maintained
* **User Tracking**: All modifications linked to specific users
* **Timestamp Accuracy**: Precise timestamps for all operations
* **Reason Codes**: Optional reason codes for changes

#### Privacy Compliance

* **Data Anonymization**: Support for anonymizing user references
* **Right to Deletion**: Compliance with data deletion requirements
* **Data Export**: Support for exporting user-related data
* **Consent Tracking**: Track consent for data processing

## Backup and Recovery

### Backup Strategy

#### Regular Backups

```typescript
// MongoDB backup configuration
{
  collections: ['product', 'product-tag', 'media'],
  schedule: '0 2 * * *',        // Daily at 2 AM
  retention: '30d',             // Keep for 30 days
  compression: true,
  encryption: true
}
```

#### Point-in-Time Recovery

* **Oplog Archival**: MongoDB oplog archival for point-in-time recovery
* **Snapshot Backups**: Regular collection snapshots
* **Cross-Region Replication**: Geographic distribution for disaster recovery
* **Testing**: Regular backup restoration testing

### Data Migration

#### Schema Evolution

```typescript
// Migration script example
async function migrateProductSchema_v2_to_v3() {
  const products = await Product.find({ schemaVersion: { $lt: 3 } });
  
  for (const product of products) {
    // Add new fields with default values
    product.newField = getDefaultValue();
    
    // Transform existing data
    product.transformedField = transformData(product.oldField);
    
    // Update schema version
    product.schemaVersion = 3;
    
    await product.save();
  }
}
```

This database schema documentation provides a complete reference for the data structure, relationships, and optimization strategies used in the Product Management module, ensuring efficient and scalable data management for the application.