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

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

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