Pivot Database Architecture Overview

System Architecture

High-Level Architecture

Core Data Domains

Domain Overview Table

Domain	Entities	Purpose
User & Authentication	Users, Authentication, Push Tokens, Badge Counts	User accounts and authentication management
Company Management	Companies, Company Settings, Positions, Position Subcategories	Company configuration and role definitions
Employee Management	Employees, Availabilities, Applicants, Employee Permissions	Employee records and hiring pipeline
Scheduling	Schedules, Schedule Templates, Open Shifts, Manual Shifts, Weekly Schedule Settings	Shift planning and schedule management
Time Tracking	Attendance, Break Records, Payroll Data	Time tracking and payroll calculation
Communication	Chats, Messages, Company Posts	Internal team communication
Requests & Notifications	Time Off Requests, Shift Swap Requests, Notifications, Notifications V2, Notification Processing Queue	Employee requests and notification system

External Integrations

Data Flow Patterns

Access Control Matrix

Database Statistics

Entity Counts (from Yannick's Company Analysis)

• Total Employees: 324
• Total Applicants: 330
• Schedule Templates: 40
• Active Positions: Multiple with subcategories

Key Performance Considerations

1. Indexes: Strategic indexing on frequently queried fields
2. Denormalization: Some data duplicated for query performance
3. Real-time Updates: Optimized paths for live data synchronization
4. Data Partitioning: Company-based partitioning for multi-tenancy

Security Rules Pattern

// Example Firebase Security Rules Structure
{
  "rules": {
    "Users": {
      "$userId": {
        ".read": "$userId === auth.uid || root.child('Employees').orderByChild('userId').equalTo(auth.uid).exists()",
        ".write": "$userId === auth.uid"
      }
    },
    "Companies": {
      "$companyId": {
        ".read": "root.child('Employees').child($companyId).child(auth.uid).exists()",
        ".write": "data.child('createdBy').val() === auth.uid"
      }
    },
    "Employees": {
      "$employeeId": {
        ".read": "auth.uid === data.child('userId').val() || 
                  root.child('Companies').child(data.child('companyId').val()).child('createdBy').val() === auth.uid",
        ".write": "root.child('Companies').child(newData.child('companyId').val()).child('createdBy').val() === auth.uid"
      }
    },
    "Schedule": {
      "$companyId": {
        ".read": "root.child('Employees').orderByChild('companyId').equalTo($companyId).orderByChild('userId').equalTo(auth.uid).exists()",
        ".write": "root.child('Companies').child($companyId).child('createdBy').val() === auth.uid"
      }
    }
  }
}

Migration & Evolution

Database Versions

1. Legacy: Original structure with nested data
2. Flat V1: First normalization (NotificationsFlat)
3. Flat V2: Current optimized structure (NotificationsFlatV2)
4. Future: Potential Firestore migration for complex queries

Migration Patterns

• Backward compatibility maintained
• Gradual migration through Cloud Functions
• Dual-write during transition periods
• Version flags for feature toggling

Summary

The Pivot database is a sophisticated Firebase Realtime Database implementation that supports:

• Multi-tenant restaurant management
• Real-time schedule and notification updates
• Complex relationships between entities
• Flexible position and role management
• Scalable architecture for 300+ employees per company
• Integrated with multiple POS systems
• Secure with role-based access control