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

2. Product Schema

3. Product Tag Schema

4. Price Sub-Schema

5. Indexing Strategy

6. Relationships and References

7. Data Validation

8. Performance Optimization

9. Multi-Tenancy Implementation

10. 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

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)

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

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

Structure:

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

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

Tags Array

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Media Document Structure

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

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

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

{
  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

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

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

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

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

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

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

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

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

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

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

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

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

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