Database Schema

Overview

This document describes the database schema and data persistence layer for the Business Profile Management module. The system uses MongoDB as the primary database with Mongoose as the ODM layer. The schema supports comprehensive business information, settings, media assets, and operational configurations.

Database Schema

Business Profile Collection

Collection Name: businessProfile

The business profile collection stores complete business information with the following schema structure:

{
  _id: ObjectId,                    // Primary key (tenant ID)
  published: Number,                // Publication status (ActiveEnum)
  name: String,                     // Business name
  username: String,                 // Unique business username
  description: String,              // Business description
  feature: String,                  // Featured information
  facilities: [String],             // Available facilities (FacilityEnum)
  socialNetworkLinks: [SocialNetworkLinkSchema], // Social media links
  bookingSettings: BookingSettingsSchema,        // Booking configuration
  businessSettings: BusinessSettingsSchema,      // Business configuration
  notificationSettings: NotificationSettingsSchema, // Notification preferences
  paymentSettings: PaymentSettingsSchema,        // Payment configuration
  panelSettings: PanelSettingsSchema,           // Panel UI settings
  publicPageSettings: PublicPageSettingsSchema, // Public page settings
  addresses: [AddressSchema],                   // Business addresses
  schedules: [ScheduleSchema],                  // Operating schedules
  specialSchedules: [SpecialScheduleSchema],    // Special schedules
  contacts: [ContactSchema],                    // Contact information
  gallery: [ObjectId],                          // Gallery image references
  logo: ObjectId,                               // Logo image reference
  banners: [ObjectId],                          // Banner image references
  stateHistory: [StateHistorySchema],           // State tracking
  createdAt: Date,                              // Auto-generated creation timestamp
  updatedAt: Date                               // Auto-generated update timestamp
}

Schema Definition

const BusinessProfileSchema = new Schema<IBusinessProfile>({
  published: { 
    type: Number, 
    enum: ActiveEnum,
    default: ActiveEnum.YES
  },
  name: { 
    type: String,
    trim: true,
    maxlength: 255
  },
  username: { 
    type: String,
    unique: true,
    sparse: true,
    trim: true,
    lowercase: true,
    maxlength: 50
  },
  description: {
    type: String,
    maxlength: 2000
  },
  feature: {
    type: String,
    maxlength: 500
  },
  facilities: [{ 
    type: String, 
    enum: FacilityEnum 
  }],
  socialNetworkLinks: { 
    type: [SocialNetworkLinkSchema], 
    default: [] 
  },
  bookingSettings: { 
    type: BookingSettingsSchema, 
    default: {} 
  },
  businessSettings: { 
    type: BusinessSettingsSchema, 
    default: {} 
  },
  notificationSettings: { 
    type: NotificationSettingsSchema, 
    default: {} 
  },
  paymentSettings: { 
    type: PaymentSettingsSchema, 
    default: {} 
  },
  panelSettings: { 
    type: PanelSettingsSchema, 
    default: {} 
  },
  publicPageSettings: { 
    type: PublicPageSettingsSchema, 
    default: {} 
  },
  addresses: { 
    type: [AddressSchema], 
    default: [] 
  },
  schedules: { 
    type: [ScheduleSchema], 
    default: [] 
  },
  specialSchedules: { 
    type: [SpecialScheduleSchema], 
    default: [] 
  },
  contacts: { 
    type: [ContactSchema], 
    default: [] 
  },
  gallery: [{ 
    type: Schema.Types.ObjectId, 
    ref: 'Media' 
  }],
  logo: { 
    type: Schema.Types.ObjectId, 
    ref: 'Media' 
  },
  banners: [{ 
    type: Schema.Types.ObjectId, 
    ref: 'Media' 
  }],
  stateHistory: { 
    type: [StateHistorySchema], 
    default: [] 
  },
}, {
  timestamps: true,
  collection: 'businessProfile',
});

Field Specifications

Required Fields

Field	Type	Description	Validation
published	Number	Publication status	Must be ActiveEnum value (0 or 1)
bookingSettings	Object	Booking configuration	Must pass BookingSettings validation
businessSettings	Object	Business configuration	Must pass BusinessSettings validation

Optional Fields

Field	Type	Description	Default
name	String	Business name	null
username	String	Unique business username	null
description	String	Business description	null
feature	String	Featured information	null
facilities	String[]	Available facilities	[]
addresses	Object[]	Business addresses	[]
contacts	Object[]	Contact information	[]
schedules	Object[]	Operating schedules	[]
specialSchedules	Object[]	Special schedules	[]
socialNetworkLinks	Object[]	Social media links	[]

System Fields

Field	Type	Description	Auto-Generated
_id	ObjectId	Primary key (tenant ID)	No (set to tenant ID)
stateHistory	Object[]	State change tracking	Yes
createdAt	Date	Creation timestamp	Yes
updatedAt	Date	Last modification timestamp	Yes

Embedded Schemas

AddressSchema

{
  _id: ObjectId,
  street: String,                   // Street address
  city: String,                     // City name
  state: String,                    // State/province
  zipCode: String,                  // Postal code
  country: String,                  // Country name
  coordinates: {                    // GPS coordinates
    latitude: Number,
    longitude: Number
  },
  isPrimary: Boolean,               // Primary address flag
  label: String,                    // Address label
  addressType: String               // Address type (business, mailing, etc.)
}

ContactSchema

{
  _id: ObjectId,
  type: String,                     // Contact type (PHONE, EMAIL, WEBSITE)
  value: String,                    // Contact value
  label: String,                    // Contact label
  isPrimary: Boolean,               // Primary contact flag
  isPublic: Boolean,                // Public visibility flag
  countryCode: String,              // For phone numbers
  extension: String                 // Phone extension
}

ScheduleSchema

{
  _id: ObjectId,
  dayOfWeek: String,                // Day of week (MONDAY, TUESDAY, etc.)
  isOpen: Boolean,                  // Open status for the day
  openTime: String,                 // Opening time (HH:mm format)
  closeTime: String,                // Closing time (HH:mm format)
  breaks: [{                        // Break periods
    startTime: String,
    endTime: String,
    label: String
  }],
  timeZone: String                  // Timezone for the schedule
}

SocialNetworkLinkSchema

{
  _id: ObjectId,
  platform: String,                // Social platform (FACEBOOK, INSTAGRAM, etc.)
  url: String,                      // Profile URL
  username: String,                 // Username on platform
  isActive: Boolean,                // Active status
  displayOrder: Number              // Display ordering
}

BookingSettingsSchema

{
  autoActionSettings: {
    autoConfirm: Boolean,
    autoConfirmTimeMinutes: Number,
    autoCancel: Boolean,
    autoCancelTimeMinutes: Number
  },
  earliestBooking: Number,          // Hours in advance (minimum)
  latestBooking: Number,            // Hours in advance (maximum)
  slotSettings: {
    slotDuration: Number,
    bufferTime: Number,
    maxAdvanceBookingDays: Number
  },
  autoBookOrder: Boolean,           // Auto-confirm bookings
  paymentRequirement: String,       // Payment requirement (NONE, DEPOSIT, FULL)
  mandatoryAttendeeProperties: [String] // Required customer fields
}

BusinessSettingsSchema

{
  timeZone: String,                 // IANA timezone identifier
  currencies: [String],             // Supported currencies
  baseCurrency: String,             // Primary currency
  languages: [String],              // Supported languages
  baseLanguage: String,             // Primary language
  emailLanguage: String,            // Email communication language
  availableLanguages: [String],     // Available interface languages
  dateFormat: String,               // Date display format
  timeFormat: String                // Time display format (12h/24h)
}

PaymentSettingsSchema

{
  stripeSettings: {
    publishableKey: String,
    secretKey: String,
    webhookSecret: String,
    isLive: Boolean
  },
  paypalSettings: {
    clientId: String,
    clientSecret: String,
    isLive: Boolean
  },
  squareSettings: {
    applicationId: String,
    accessToken: String,
    isLive: Boolean
  },
  defaultPaymentMethod: String,     // Default payment provider
  acceptedPaymentMethods: [String], // Accepted payment methods
  currencySettings: {
    supportedCurrencies: [String],
    exchangeRateProvider: String
  },
  taxSettings: {
    taxRate: Number,
    taxCalculationMethod: String,
    includeTaxInPrice: Boolean
  }
}

Relationships

Media Relationships

• Type: One-to-One (Logo), One-to-Many (Banners, Gallery)

• Implementation: ObjectId references to Media collection

• Collection: media

• Lookup: Populated during queries when media details needed

// Population example
businessProfile
  .populate('logo', 'url filename alt title')
  .populate('banners', 'url filename alt title')
  .populate('gallery', 'url filename alt title')

Reference Integrity

• Media references are managed by the application layer

• Orphaned media cleanup handled by background jobs

• Cascade deletion policies for media references

Indexing Strategy

Primary Indexes

// Primary key index (automatic)
db.businessProfile.createIndex({ "_id": 1 });

// Username uniqueness index
db.businessProfile.createIndex({ 
  "username": 1 
}, { 
  unique: true, 
  sparse: true 
});

// Publication status index
db.businessProfile.createIndex({ 
  "published": 1 
});

// Name search index
db.businessProfile.createIndex({ 
  "name": 1 
});

// Text search index
db.businessProfile.createIndex({
  "name": "text",
  "description": "text",
  "feature": "text"
});

Composite Indexes

// Published profiles with name
db.businessProfile.createIndex({ 
  "published": 1, 
  "name": 1 
});

// Location-based queries
db.businessProfile.createIndex({ 
  "addresses.coordinates": "2dsphere" 
});

// Facilities filtering
db.businessProfile.createIndex({ 
  "facilities": 1,
  "published": 1 
});

Sparse Indexes

// Username index (sparse for optional field)
db.businessProfile.createIndex(
  { "username": 1 }, 
  { unique: true, sparse: true }
);

// Social network platforms
db.businessProfile.createIndex({ 
  "socialNetworkLinks.platform": 1 
}, { 
  sparse: true 
});

Query Patterns

Common Queries

Find Business Profile by Tenant ID

// Standard profile lookup
db.businessProfile.findOne({
  _id: ObjectId(tenantId)
});

Find Published Profiles

// Public profile queries
db.businessProfile.find({
  published: ActiveEnum.YES
});

Username Availability Check

// Check username uniqueness
db.businessProfile.findOne({
  username: usernameToCheck
});

Location-Based Search

// Geospatial queries
db.businessProfile.find({
  published: ActiveEnum.YES,
  "addresses.coordinates": {
    $near: {
      $geometry: {
        type: "Point",
        coordinates: [longitude, latitude]
      },
      $maxDistance: radiusInMeters
    }
  }
});

Facility-Based Filtering

// Filter by facilities
db.businessProfile.find({
  published: ActiveEnum.YES,
  facilities: { $in: ["PARKING", "WIFI"] }
});

Text Search

// Full-text search
db.businessProfile.find({
  published: ActiveEnum.YES,
  $text: { $search: searchTerm }
}, {
  score: { $meta: "textScore" }
}).sort({
  score: { $meta: "textScore" }
});

Complex Aggregation Queries

Profile with Media Population

const pipeline = [
  // Match criteria
  {
    $match: {
      published: ActiveEnum.YES
    }
  },
  // Lookup logo
  {
    $lookup: {
      from: 'media',
      localField: 'logo',
      foreignField: '_id',
      as: 'logo'
    }
  },
  // Unwind logo (single document)
  {
    $unwind: {
      path: '$logo',
      preserveNullAndEmptyArrays: true
    }
  },
  // Lookup banners
  {
    $lookup: {
      from: 'media',
      localField: 'banners',
      foreignField: '_id',
      as: 'banners'
    }
  },
  // Lookup gallery
  {
    $lookup: {
      from: 'media',
      localField: 'gallery',
      foreignField: '_id',
      as: 'gallery'
    }
  },
  // Project only needed fields
  {
    $project: {
      name: 1,
      description: 1,
      addresses: 1,
      contacts: 1,
      'logo.url': 1,
      'logo.alt': 1,
      'banners.url': 1,
      'banners.alt': 1,
      'gallery.url': 1,
      'gallery.alt': 1
    }
  }
];

Business Directory Query

const directoryPipeline = [
  {
    $match: {
      published: ActiveEnum.YES
    }
  },
  {
    $addFields: {
      primaryAddress: {
        $arrayElemAt: [
          { $filter: {
            input: '$addresses',
            cond: { $eq: ['$$this.isPrimary', true] }
          }}, 0
        ]
      }
    }
  },
  {
    $project: {
      name: 1,
      description: 1,
      facilities: 1,
      'primaryAddress.city': 1,
      'primaryAddress.state': 1,
      logoUrl: '$logo.url'
    }
  },
  {
    $sort: { name: 1 }
  }
];

Repository Implementation

BusinessProfileRepository

The repository class extends BaseRepository and implements IBusinessProfileRepository:

export class BusinessProfileRepository extends BaseRepository<IBusinessProfile> 
  implements IBusinessProfileRepository {
  
  constructor(
    public readonly model: Model<BusinessProfileDocument>
  ) {
    super(model);
  }

  async findByUsername(username: string): Promise<IBusinessProfile | null> {
    return this.model.findOne({ 
      username: username.toLowerCase() 
    }).exec();
  }

  async findByIdentifier(identifier: string): Promise<IBusinessProfile | null> {
    // Can search by username or tenant ID
    return this.model.findOne({
      $or: [
        { _id: identifier },
        { username: identifier.toLowerCase() }
      ]
    }).exec();
  }

  async updateSettings<T>(
    tenantId: string, 
    settings: T, 
    settingsType: string
  ): Promise<void> {
    const updateQuery = {};
    updateQuery[settingsType] = settings;
    
    await this.model.updateOne(
      { _id: tenantId },
      { $set: updateQuery }
    ).exec();
  }
}

Key Repository Methods

Standard CRUD Operations

• createItemWithId(profile: IBusinessProfile): Promise<IBusinessProfile>

• updateItem(id: string, profile: IBusinessProfile): Promise<IBusinessProfile>

• findById(id: string): Promise<IBusinessProfile | null>

• deleteItem(id: string): Promise<void> (soft delete)

Specialized Queries

• findByUsername(username: string): Promise<IBusinessProfile | null>

• findByIdentifier(identifier: string): Promise<IBusinessProfile | null>

• findPublishedProfiles(): Promise<IBusinessProfile[]>

• findByLocation(coordinates: number[], radius: number): Promise<IBusinessProfile[]>

• searchByText(query: string): Promise<IBusinessProfile[]>

Settings Management

• updateBookingSettings(tenantId: string, settings: IBookingSettings): Promise<void>

• updateBusinessSettings(tenantId: string, settings: IBusinessSettings): Promise<void>

• updatePaymentSettings(tenantId: string, settings: IPaymentSettings): Promise<void>

Data Consistency

State History Tracking

Business profiles use state history for audit trails:

// State history entry
{
  state: 'active',
  timestamp: new Date(),
  actor: actorId,
  reason: 'Profile updated',
  metadata: {
    fieldsChanged: ['name', 'description'],
    previousValues: {}
  }
}

Media Reference Integrity

• Application layer manages media references

• Orphaned media cleanup via background jobs

• Reference validation during updates

Settings Validation

All embedded settings objects are validated:

// Validation pipeline
const bookingSettingsValidation = BookingSettings.create(settings);
if (bookingSettingsValidation.isFailureResult()) {
  throw new ValidationException(bookingSettingsValidation.errorValue());
}

Multi-Tenancy

Tenant Isolation

Data isolation is achieved through:

• Document-level isolation (one profile per tenant)

• Tenant ID as primary key

• Application-level tenant context enforcement

Tenant-Aware Operations

// All operations include tenant context
async findProfile(tenantId: string): Promise<IBusinessProfile | null> {
  return this.model.findOne({ _id: tenantId }).exec();
}

Performance Considerations

Query Optimization

1. Index Usage: Ensure queries use appropriate indexes

2. Projection: Select only needed fields in queries

3. Population: Use selective population for media references

4. Aggregation: Use aggregation pipelines for complex queries

Media Performance

1. Lazy Loading: Load media references on demand

2. CDN Integration: Serve media files via CDN

3. Thumbnail Generation: Multiple sizes for optimal loading

4. Compression: Automatic image optimization

Caching Strategy

1. Profile Caching: Cache complete profiles

2. Settings Caching: Cache individual settings objects

3. Media Caching: Cache media URLs and metadata

4. Query Result Caching: Cache common query results

Data Migration

Schema Evolution

When schema changes are needed:

1. Backward Compatibility: Add optional fields

2. Migration Scripts: Transform existing data

3. Versioning: Track schema versions

4. Rollback Procedures: Maintain rollback capabilities

Example Migration Scripts

// Add new field to existing documents
db.businessProfile.updateMany(
  { newField: { $exists: false } },
  { $set: { newField: defaultValue } }
);

// Transform nested objects
db.businessProfile.updateMany(
  { 'oldSettings': { $exists: true } },
  [
    {
      $set: {
        'newSettings': {
          $mergeObjects: [
            '$oldSettings',
            { newProperty: 'defaultValue' }
          ]
        }
      }
    },
    {
      $unset: 'oldSettings'
    }
  ]
);

Backup and Recovery

Backup Strategy

1. Regular Snapshots: Daily MongoDB snapshots

2. Point-in-Time Recovery: Enable oplog for PITR

3. Media Backup: Separate media file backup

4. Cross-Region Replication: For disaster recovery

Data Retention

1. Active Profiles: Keep in primary collection

2. Inactive Profiles: Move to archive after inactivity period

3. Media Files: Retain based on business requirements

4. Audit Logs: Retain state history for compliance

Monitoring and Alerts

Database Metrics

• Query performance and execution times

• Index usage statistics and efficiency

• Document growth rate and collection size

• Memory usage and cache hit ratios

• Connection pool utilization

Alert Conditions

• Slow queries (>100ms for simple operations)

• Index misses or inefficient queries

• High memory usage or cache pressure

• Media storage growth rate

• Failed operations or validation errors

Security Considerations

Data Protection

1. Encryption at Rest: Enable MongoDB encryption

2. Encryption in Transit: Use TLS for all connections

3. Field-Level Encryption: For sensitive payment data

4. Access Control: Role-based database access

5. Audit Logging: Track all data access and modifications

Media Security

1. Secure URLs: Time-limited signed URLs for media access

2. Content Validation: Validate uploaded file types and content

3. Storage Encryption: Encrypt media files at rest

4. Access Logs: Track media file access patterns

Privacy Compliance

1. Data Minimization: Store only necessary information

2. Right to Deletion: Support for profile deletion

3. Data Portability: Export capabilities for profile data

4. Audit Trails: Complete change history for compliance