> 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/business-profile/db_schema.md).

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

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

```typescript
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

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

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

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

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

```typescript
{
  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

```typescript
{
  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

```typescript
{
  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

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

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

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

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

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

#### Find Published Profiles

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

#### Username Availability Check

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

#### Location-Based Search

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

#### Facility-Based Filtering

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

#### Text Search

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

```typescript
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

```typescript
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`:

```typescript
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:

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

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

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

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