Skip to main content

Pivot Database Structure - Complete ERD

1. Core Entities and Relationships​

2. Notification System Detail​

3. Schedule System Architecture​

4. Integration System​

5. Access Control & Permissions​

6. Supporting Entities​

7. Request System Flow​

Key Relationships Summary​

Primary Foreign Key Relationships:​

  1. Users → Employees: userId links employee records to auth accounts
  2. Companies → Employees: companyId links employees to their workplace
  3. Users → Companies: createdBy identifies company owners
  4. Employees → Schedules: employeeId assigns shifts to workers
  5. Companies → All Entities: Most entities reference companyId for multi-tenancy
  6. Chats → Messages: chatId groups messages in conversations
  7. Employees → Positions: Complex many-to-many through position arrays
  8. Notifications → Requests: requestId links notifications to their source

Index Strategy:​

  • Time-based queries: createdAt, updatedAt, lastSeen, timestamp
  • Relationship queries: companyId, userId, employeeId
  • Status filtering: status, type, active
  • Performance optimization: Custom indexes per entity type