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

# Database Schema

## Overview

The member management module uses MongoDB with Mongoose ODM for data persistence. The schema follows Domain-Driven Design principles with proper separation between domain models and persistence models.

## Core Entities

### Member Entity

**Collection**: `member`

**Schema Definition**: `MemberSchema`

| Field           | Type                        | Required | Default   | Description                         |
| --------------- | --------------------------- | -------- | --------- | ----------------------------------- |
| `_id`           | `ObjectId`                  | Auto     | Generated | Unique identifier                   |
| `firstName`     | `String`                    | No       | -         | Member's first name                 |
| `lastName`      | `String`                    | No       | -         | Member's last name                  |
| `phone`         | `String`                    | No       | -         | Contact phone number                |
| `email`         | `String`                    | Yes      | -         | Email address (unique per tenant)   |
| `avatar`        | `ObjectId`                  | No       | -         | Reference to Media entity           |
| `memberContext` | `ObjectId`                  | No       | -         | Reference to MemberContext entity   |
| `profileStatus` | `String`                    | No       | `active`  | Current status of member profile    |
| `language`      | `String`                    | No       | `en`      | Preferred language code             |
| `referal`       | `ReferalSchema`             | No       | -         | Invitation and referral information |
| `role`          | `String`                    | No       | `ADMIN`   | Legacy role field (deprecated)      |
| `roles`         | `Array<RoleSchema>`         | No       | `[]`      | New role-based permissions          |
| `assignments`   | `AssignmentsSchema`         | No       | -         | Service assignment configuration    |
| `stateHistory`  | `Array<StateHistorySchema>` | No       | `[]`      | State change tracking               |
| `createdAt`     | `Date`                      | Auto     | Current   | Creation timestamp                  |
| `updatedAt`     | `Date`                      | Auto     | Current   | Last update timestamp               |

### Profile Status Enum

```typescript
enum MemberProfileStatusEnum {
    active = 'active',
    deleted = 'deleted', 
    suspended = 'suspended'
}
```

**Usage**: Tracks the current state of a member's profile for access control and filtering.

### Language Code Enum

Follows ISO 639-1 standard language codes for internationalization support.

## Sub-Schemas

### Referral Schema (`ReferalSchema`)

**Purpose**: Manages invitation codes and referral tracking for member onboarding.

| Field            | Type     | Required | Default          | Description                  |
| ---------------- | -------- | -------- | ---------------- | ---------------------------- |
| `invitationCode` | `String` | Yes      | Generated        | Unique invitation code       |
| `active`         | `Number` | No       | `ActiveEnum.YES` | Whether invitation is active |

**Configuration**:

* `_id: false` - No separate ID for sub-document
* `timestamps: false` - No automatic timestamps

**Business Rules**:

* Invitation codes are auto-generated using secure random generation
* Active status controls whether invitation can be used
* Used for member invitation workflows

### Assignments Schema (`AssignmentsSchema`)

**Purpose**: Defines which services and features a member has access to.

| Field     | Type                      | Required | Default | Description                  |
| --------- | ------------------------- | -------- | ------- | ---------------------------- |
| `service` | `AssignmentServiceSchema` | Yes      | -       | Service access configuration |

**Configuration**:

* `_id: false` - No separate ID for sub-document
* `timestamps: false` - No automatic timestamps

### Assignment Service Schema (`AssignmentServiceSchema`)

**Purpose**: Configures service-level access permissions.

| Field     | Type                           | Required | Default | Description                            |
| --------- | ------------------------------ | -------- | ------- | -------------------------------------- |
| `full`    | `Boolean`                      | Yes      | `false` | Whether member has full service access |
| `include` | `Array<AssignedServiceSchema>` | No       | `[]`    | Specific services if not full access   |

**Configuration**:

* `_id: false` - No separate ID for sub-document
* `timestamps: false` - No automatic timestamps

**Business Logic**:

* When `full: true`, member has access to all services
* When `full: false`, only services in `include` array are accessible
* Provides granular service-level permission control

### Assigned Service Schema (`AssignedServiceSchema`)

**Purpose**: References specific services for granular access control.

| Field     | Type       | Required | Default | Description                 |
| --------- | ---------- | -------- | ------- | --------------------------- |
| `service` | `ObjectId` | Yes      | -       | Reference to Service entity |

**Configuration**:

* `_id: false` - No separate ID for sub-document
* `timestamps: false` - No automatic timestamps
* `ref: Service.name` - References Service collection

## Relationships

### Member → Media (Avatar)

* **Type**: One-to-One (Optional)
* **Field**: `avatar`
* **Collection**: `media`
* **Description**: References uploaded avatar image file

### Member → MemberContext

* **Type**: One-to-One (Optional)
* **Field**: `memberContext`
* **Collection**: `member-context`
* **Description**: Links member to authentication context and account

### Member → Roles

* **Type**: One-to-Many
* **Field**: `roles`
* **Schema**: `RoleSchema`
* **Description**: New role-based permission system

### AssignedService → Service

* **Type**: Many-to-One
* **Field**: `assignments.service.include[].service`
* **Collection**: `service`
* **Description**: References specific services for access control

## Indexes

### Recommended Indexes

```javascript
// Unique email per tenant (compound index)
{ "email": 1, "tenantId": 1 }

// Profile status for filtering
{ "profileStatus": 1 }

// Role-based queries
{ "roles.name": 1 }

// Full text search on name and email
{ 
  "firstName": "text", 
  "lastName": "text", 
  "email": "text" 
}

// Timestamp queries
{ "createdAt": 1 }
{ "updatedAt": 1 }

// Member context relationship
{ "memberContext": 1 }
```

### Performance Considerations

* **Email Uniqueness**: Compound index with tenant ensures email uniqueness per tenant
* **Text Search**: Full-text search index for member discovery
* **Status Filtering**: Index on profileStatus for active/deleted member queries
* **Role Queries**: Index on role names for permission-based filtering

## Data Integrity

### Validation Rules

1. **Email Format**: Must be valid email format
2. **Profile Status**: Must be one of defined enum values
3. **Language Code**: Must be valid ISO 639-1 code
4. **Role References**: Must reference existing roles
5. **Service References**: Must reference existing services

### Constraints

1. **Email Uniqueness**: Email must be unique within tenant
2. **Referral Code Uniqueness**: Invitation codes must be globally unique
3. **Required Fields**: Email is the only strictly required field
4. **Soft Delete**: Use profileStatus instead of physical deletion

### Data Migration Considerations

1. **Role Migration**: Legacy `role` field being replaced by `roles` array
2. **Assignment Structure**: Support for both full and granular access patterns
3. **Context Linking**: MemberContext may be optional during migration
4. **State History**: Preserved for audit and rollback capabilities

## Audit and History

### State History

The `stateHistory` field tracks all significant state changes:

* Profile status changes
* Role modifications
* Assignment updates
* Email changes

### Timestamps

* `createdAt`: Automatic creation timestamp
* `updatedAt`: Automatic update timestamp
* Used for audit trails and data analysis

## Multi-Tenancy

### Tenant Isolation

* Members belong to specific tenants
* Email uniqueness enforced per tenant
* Queries must include tenant context
* Cross-tenant access is prevented

### Tenant-Aware Operations

All database operations include tenant context:

* Create operations include tenant ID
* Read operations filter by tenant
* Update/Delete operations validate tenant ownership

## Backup and Recovery

### Backup Strategy

1. **Document Integrity**: Full document backup including sub-schemas
2. **Relationship Preservation**: Maintain ObjectId references
3. **State History**: Include complete audit trail
4. **Media References**: Coordinate with media storage backup

### Recovery Considerations

1. **Role Dependencies**: Ensure role definitions exist before member restore
2. **Service Dependencies**: Validate service references during restore
3. **Context Linking**: Restore member contexts before members
4. **Invitation Codes**: Maintain uniqueness during recovery

## Security Considerations

### Data Protection

1. **PII Handling**: First name, last name, phone, email are PII
2. **Access Control**: Role-based access to member data
3. **Audit Logging**: State history for compliance
4. **Soft Delete**: Maintain data for compliance while marking as deleted

### Permission Model

1. **Role-Based**: New roles array supports complex permission inheritance
2. **Service-Based**: Granular service access control
3. **Owner Protection**: Special validation for owner role changes
4. **Self-Service**: Limited self-editing capabilities

## Performance Monitoring

### Query Patterns

1. **Paginated Lists**: Monitor pagination query performance
2. **Search Operations**: Track full-text search efficiency
3. **Role Filtering**: Monitor role-based filtering performance
4. **Assignment Lookups**: Track service assignment query patterns

### Optimization Strategies

1. **Index Utilization**: Ensure proper index usage
2. **Population Strategy**: Optimize related data loading
3. **Caching Strategy**: Cache frequently accessed member data
4. **Query Aggregation**: Use aggregation for complex reporting

## Development Guidelines

### Schema Evolution

1. **Backward Compatibility**: Maintain compatibility during updates
2. **Migration Scripts**: Provide migration for schema changes
3. **Default Values**: Use appropriate defaults for new fields
4. **Validation Updates**: Update validation with schema changes

### Testing Strategies

1. **Unit Tests**: Test schema validation rules
2. **Integration Tests**: Test with real MongoDB instance
3. **Migration Tests**: Validate schema migration scripts
4. **Performance Tests**: Load test with realistic data volumes