Data Model & Firebase Structure

Firebase Realtime Database Schema

CustomerHealthMetrics (New Node)

This is a new top-level node created specifically for the CSM Dashboard.

CustomerHealthMetrics/
  {companyId}/
    numberofemployees: 127
    last_login_date_master_account: "2025-11-23T14:30:00Z"
    quarts_de_travail_ouverts_10_: "No"
    quarts_de_travail_attribues_hors_dispo_10: "Yes"
    quarts_problematiques_50: "No"
    lastUpdated: 1732435200000  // Firebase ServerValue.TIMESTAMP
    history/
      "2025-11-24"/
        numberofemployees: 127
        last_login_date_master_account: "2025-11-23T14:30:00Z"
        quarts_de_travail_ouverts_10_: "No"
        quarts_de_travail_attribues_hors_dispo_10: "Yes"
        quarts_problematiques_50: "No"
        timestamp: 1732435200000
      "2025-11-23"/
        ...
      "2025-11-22"/
        ...

Retention Policy:

• Keep daily snapshots for 90 days
• Archive older data to BigQuery (future)

Indexes Required:

{
  "rules": {
    "CustomerHealthMetrics": {
      ".indexOn": ["lastUpdated", "numberofemployees"]
    }
  }
}

---

Existing Nodes (Read-Only Access)

Companies

Companies/
  {companyId}/
    name: "Bloom Sushi QDS"
    email: "contact@bloomsushi.com"  // Master account email
    phone: "514-555-1234"
    city: "Montreal, QC H2X 1Y3"
    createdAt: 1620000000000
    isHeadOffice: false
    timezone: "America/Toronto"

Used For:

• Company matching (name + email)
• Display company information
• Timezone for metric calculations

---

Employees

Employees/
  {companyId}/
    {employeeId}/
      name: "John Doe"
      email: "john@bloomsushi.com"
      position: "Server"
      archived: false
      createdAt: 1620000000000
      archivedAt: null

Used For:

• Counting active employees (numberofemployees)
• Identifying master account user

Query:

// Count active employees
const employeesRef = db.ref(`Employees/${companyId}`);
const snapshot = await employeesRef
  .orderByChild('archived')
  .equalTo(false)
  .once('value');

const employeeCount = snapshot.numChildren();

---

PresenceStatus

PresenceStatus/
  {userId}/
    lastActive: 1732435200000  // Timestamp
    status: "online" | "offline"
    platform: "web" | "mobile"

Used For:

• Tracking last login for master account (last_login_date_master_account)

Query:

// Get master account last login
const user = await db.ref(`Users/${userId}`).once('value');
const userEmail = user.val().email;

const company = await db.ref(`Companies/${companyId}`).once('value');
const masterEmail = company.val().email;

if (userEmail === masterEmail) {
  const presence = await db.ref(`PresenceStatus/${userId}/lastActive`).once('value');
  const lastLogin = presence.val();
}

Important:

• NOT using Employees/{employeeId}/lastSeen (deprecated)
• PresenceStatus is the single source of truth for online status

---

Schedules

Schedules/
  {companyId}/
    {weekId}/  // e.g., "2025-W47"
      status: "draft" | "published"
      publishedAt: 1732435200000
      shifts/
        {shiftId}/
          date: "2025-11-24"
          startTime: "09:00"
          endTime: "17:00"
          employeeId: "-MCMchoX..."  // null if open shift
          position: "Server"
          isRegular: true  // true after "Generate Schedule" clicked
          isOpen: true     // true if employeeId is null
          isConflict: false // true if assigned outside availability

Used For:

• quarts_de_travail_ouverts_10_ - Open shifts in DRAFT schedules
• quarts_de_travail_attribues_hors_dispo_10 - Unavailable shifts in PUBLISHED schedules

Queries:

// Get open shifts for a specific date
const shiftsRef = db.ref(`Schedules/${companyId}/${weekId}/shifts`);
const snapshot = await shiftsRef
  .orderByChild('date')
  .equalTo('2025-11-24')
  .once('value');

const shifts = snapshot.val();
let regularShifts = 0;
let openShifts = 0;

for (const shift of Object.values(shifts)) {
  if (shift.isRegular) regularShifts++;
  if (shift.isOpen) openShifts++;
}

const percentage = (openShifts / regularShifts) * 100;

// Check if shift is assigned outside availability
const employee = await db.ref(`Employees/${companyId}/${employeeId}`).once('value');
const availability = await db.ref(`Availability/${employeeId}/${dayOfWeek}`).once('value');

// If availability is empty or null, shift is a conflict
const isConflict = !availability.exists() || availability.val().length === 0;

---

Availability

Availability/
  {employeeId}/
    monday: [
      { start: "09:00", end: "17:00" }
    ]
    tuesday: []  // Empty = unavailable
    wednesday: [
      { start: "09:00", end: "13:00" },
      { start: "18:00", end: "22:00" }
    ]
    thursday: null  // null = unavailable

Used For:

• Detecting shifts assigned outside availability
• quarts_de_travail_attribues_hors_dispo_10 metric

Logic:

function isShiftOutsideAvailability(shift, availability) {
  const dayOfWeek = getDayOfWeek(shift.date); // "monday", "tuesday", etc.
  const dayAvailability = availability[dayOfWeek];

  // If no availability or empty array, shift is outside availability
  if (!dayAvailability || dayAvailability.length === 0) {
    return true;
  }

  // Check if shift time overlaps with any availability window
  for (const window of dayAvailability) {
    if (timeOverlaps(shift.startTime, shift.endTime, window.start, window.end)) {
      return false; // Shift is within availability
    }
  }

  return true; // Shift is outside all availability windows
}

---

Timecards

Timecards/
  {companyId}/
    {timecardId}/
      employeeId: "-MCMchoX..."
      date: "2025-11-24"
      clockIn: 1732435200000
      clockOut: 1732461200000
      expectedShiftStart: 1732435200000
      expectedShiftEnd: 1732461200000
      status: "conflict" | "approved" | "pending"
      conflictReason: "late_clock_in" | "early_clock_out" | "no_scheduled_shift" | null

Used For:

• quarts_problematiques_50 - Conflicting timecards

Conflict Detection:

function hasConflict(timecard) {
  // Missing clock-in or clock-out
  if (!timecard.clockIn || !timecard.clockOut) {
    return true;
  }

  // No scheduled shift
  if (!timecard.expectedShiftStart || !timecard.expectedShiftEnd) {
    return true;
  }

  // Clock-in more than 15 minutes late
  const lateThreshold = 15 * 60 * 1000; // 15 minutes in ms
  if (timecard.clockIn - timecard.expectedShiftStart > lateThreshold) {
    return true;
  }

  // Clock-out more than 15 minutes early
  if (timecard.expectedShiftEnd - timecard.clockOut > lateThreshold) {
    return true;
  }

  // Overlapping shifts for same employee
  // (checked separately during batch processing)

  return false;
}

Query:

// Get timecards for last 14 days
const last14Days = getLast14Days();
const timecards = [];

for (const date of last14Days) {
  const snapshot = await db.ref(`Timecards/${companyId}`)
    .orderByChild('date')
    .equalTo(date)
    .once('value');

  if (snapshot.exists()) {
    timecards.push(...Object.values(snapshot.val()));
  }
}

const totalTimecards = timecards.length;
const conflictingTimecards = timecards.filter(t => hasConflict(t)).length;

const percentage = (conflictingTimecards / totalTimecards) * 100;

---

Database Rules

Security Rules

{
  "rules": {
    "CustomerHealthMetrics": {
      ".read": "auth.token.email.matches(/.*@pivotapp\\.ca$/)",
      ".write": false,  // Only Cloud Functions can write

      "$companyId": {
        ".indexOn": ["lastUpdated", "numberofemployees"]
      }
    },

    "Companies": {
      ".read": "auth.token.email.matches(/.*@pivotapp\\.ca$/)",
      ".write": false  // Read-only from dashboard
    },

    "Employees": {
      ".read": "auth.token.email.matches(/.*@pivotapp\\.ca$/)",
      ".write": false
    },

    "PresenceStatus": {
      ".read": "auth.token.email.matches(/.*@pivotapp\\.ca$/)",
      ".write": false
    },

    "Schedules": {
      ".read": "auth.token.email.matches(/.*@pivotapp\\.ca$/)",
      ".write": false
    },

    "Timecards": {
      ".read": "auth.token.email.matches(/.*@pivotapp\\.ca$/)",
      ".write": false
    }
  }
}

Notes:

• Dashboard has read-only access to all data
• Only Cloud Functions can write to CustomerHealthMetrics
• All access restricted to @pivotapp.ca email domain

---

Data Migration

Initial Population

Create a migration script to populate CustomerHealthMetrics for existing companies:

// migrations/populate-customer-health-metrics.ts

import * as admin from 'firebase-admin';

async function populateMetrics() {
  const db = admin.database();

  // Get all companies
  const companiesSnapshot = await db.ref('Companies').once('value');
  const companies = companiesSnapshot.val();

  for (const [companyId, companyData] of Object.entries(companies)) {
    console.log(`Processing company: ${companyData.name}`);

    try {
      // Calculate initial metrics
      const employeeCount = await countActiveEmployees(companyId);
      const lastLogin = await getLastMasterAccountLogin(companyId);

      // Create initial record
      await db.ref(`CustomerHealthMetrics/${companyId}`).set({
        numberofemployees: employeeCount,
        last_login_date_master_account: lastLogin,
        quarts_de_travail_ouverts_10_: "None",  // Will be calculated by cron
        quarts_de_travail_attribues_hors_dispo_10: "None",
        quarts_problematiques_50: "None",
        lastUpdated: admin.database.ServerValue.TIMESTAMP
      });

      console.log(`✓ Populated metrics for ${companyData.name}`);

    } catch (error) {
      console.error(`✗ Error processing ${companyData.name}:`, error);
    }
  }

  console.log('Migration complete!');
}

// Run migration
populateMetrics();

Execution:

cd functions
ts-node migrations/populate-customer-health-metrics.ts

---

Data Consistency

Handling Edge Cases

Company Deleted

• Keep CustomerHealthMetrics record (historical data)
• Mark with deleted: true flag
• Exclude from dashboard display

Employee Count Mismatch

• Recalculate on demand via admin endpoint
• Log discrepancies to #support-logs

Missing Presence Data

• If PresenceStatus doesn't exist for master account
• Fall back to Users/{userId}/lastSeen
• Log warning to investigate

Incomplete Schedules

• If schedule exists but no shifts generated (isRegular = false)
• Count as 0 regular shifts
• Metric returns "None" (no data to calculate)

Timezone Issues

• All calculations use company's timezone
• Convert timestamps to company timezone before date comparisons
• Cron job runs at 6:00 AM Toronto, but processes each company in their own timezone

---

TypeScript Interfaces

Customer Health Metric Types

// types/metrics.ts

export interface CustomerHealthMetrics {
  companyId: string;
  numberofemployees: number;
  last_login_date_master_account: string | null;  // ISO 8601
  quarts_de_travail_ouverts_10_: MetricValue;
  quarts_de_travail_attribues_hors_dispo_10: MetricValue;
  quarts_problematiques_50: MetricValue;
  lastUpdated: number;  // Unix timestamp
  history?: Record<string, HistoricalSnapshot>;
}

export type MetricValue = "Yes" | "No" | "None";

export interface HistoricalSnapshot {
  numberofemployees: number;
  last_login_date_master_account: string | null;
  quarts_de_travail_ouverts_10_: MetricValue;
  quarts_de_travail_attribues_hors_dispo_10: MetricValue;
  quarts_problematiques_50: MetricValue;
  timestamp: number;
}

export interface Company {
  companyId: string;
  name: string;
  email: string;  // Master account email
  phone?: string;
  city?: string;
  timezone: string;
  createdAt: number;
  isHeadOffice: boolean;
}

export interface Employee {
  employeeId: string;
  name: string;
  email: string;
  position: string;
  archived: boolean;
  createdAt: number;
  archivedAt?: number;
}

export interface Shift {
  shiftId: string;
  date: string;  // YYYY-MM-DD
  startTime: string;  // HH:mm
  endTime: string;  // HH:mm
  employeeId: string | null;
  position: string;
  isRegular: boolean;
  isOpen: boolean;
  isConflict: boolean;
}

export interface Timecard {
  timecardId: string;
  employeeId: string;
  date: string;  // YYYY-MM-DD
  clockIn: number | null;  // Unix timestamp
  clockOut: number | null;
  expectedShiftStart: number | null;
  expectedShiftEnd: number | null;
  status: "conflict" | "approved" | "pending";
  conflictReason: ConflictReason | null;
}

export type ConflictReason =
  | "late_clock_in"
  | "early_clock_out"
  | "no_scheduled_shift"
  | "missing_clock_in"
  | "missing_clock_out"
  | "overlapping_shifts";

export type AlertLevel = "critical" | "warning" | "healthy";

export interface CustomerAlert {
  companyId: string;
  companyName: string;
  alertLevel: AlertLevel;
  reasons: string[];  // e.g., ["No login in 15 days", "Open shifts >20%"]
  timestamp: number;
}

---

Query Optimization

Composite Indexes

For efficient querying, add these composite indexes:

{
  "rules": {
    "Schedules": {
      "$companyId": {
        "$weekId": {
          "shifts": {
            ".indexOn": ["date", "isRegular", "isOpen"]
          }
        }
      }
    },

    "Timecards": {
      "$companyId": {
        ".indexOn": ["date", "status"]
      }
    },

    "Employees": {
      "$companyId": {
        ".indexOn": ["archived", "createdAt"]
      }
    }
  }
}

Caching Strategy

For dashboard performance:

1. Cache customer list (5 minute TTL)

   • Full customer list doesn't change frequently
   • Refresh on-demand with "Refresh" button

2. Cache individual customer details (1 minute TTL)

   • Metrics update daily, real-time not critical
   • Show "Last updated" timestamp

3. Pre-calculate aggregates (daily)

   • Total companies by alert level
   • Metric distribution statistics
   • Store in CustomerHealthMetrics/aggregates/