Skip to main content

Technical Architecture

System Overview​

The CSM Dashboard is built as a Next.js application that reads directly from Firebase Realtime Database and displays customer health metrics in real-time.

┌─────────────────────────────────────────────────────────────┐
│                     CSM Dashboard (Next.js)                  │
│  ┌────────────┐  ┌────────────┐  ┌──────────────────────┐  │
│  │  Customer  │  │  Metrics   │  │  Alerts & Filters   │  │
│  │    List    │  │  Details   │  │    & Export         │  │
│  └────────────┘  └────────────┘  └──────────────────────┘  │
└─────────────────────────┬───────────────────────────────────┘
                          │
                          ▼
          ┌───────────────────────────────┐
          │  Firebase Admin SDK (Node.js) │
          │   - Read CustomerHealthMetrics│
          │   - Read Companies            │
          │   - Read Employees            │
          └───────────────┬───────────────┘
                          │
                          ▼
┌─────────────────────────────────────────────────────────────┐
│              Firebase Realtime Database                      │
│                    (pivot-inc project)                       │
│  ┌─────────────────────────────────────────────────────┐   │
│  │  CustomerHealthMetrics/{companyId}/                 │   │
│  │    - numberofemployees                              │   │
│  │    - last_login_date_master_account                 │   │
│  │    - quarts_de_travail_ouverts_10_                  │   │
│  │    - quarts_de_travail_attribues_hors_dispo_10      │   │
│  │    - quarts_problematiques_50                       │   │
│  │    - history/{date}/...                             │   │
│  └─────────────────────────────────────────────────────┘   │
│  ┌─────────────────────────────────────────────────────┐   │
│  │  Companies/{companyId}/                             │   │
│  │    - name                                           │   │
│  │    - email (master account)                         │   │
│  │    - city, phone, etc.                              │   │
│  └─────────────────────────────────────────────────────┘   │
│  ┌─────────────────────────────────────────────────────┐   │
│  │  Employees/{companyId}/{employeeId}/                │   │
│  │    - archived                                       │   │
│  │    - name, position, etc.                           │   │
│  └─────────────────────────────────────────────────────┘   │
│  ┌─────────────────────────────────────────────────────┐   │
│  │  PresenceStatus/{userId}/                           │   │
│  │    - lastActive                                     │   │
│  └─────────────────────────────────────────────────────┘   │
│  ┌─────────────────────────────────────────────────────┐   │
│  │  Schedules/{companyId}/{weekId}/shifts/             │   │
│  │  Timecards/{companyId}/                             │   │
│  └─────────────────────────────────────────────────────┘   │
└─────────────────────────────────────────────────────────────┘
                          ▲
                          │
                          │
┌─────────────────────────┴───────────────────────────────────┐
│           Firebase Cloud Functions (Metric Calculators)      │
│  ┌────────────────────┐  ┌─────────────────────────────┐   │
│  │  Real-time Updates │  │   Scheduled Cron Jobs       │   │
│  │                    │  │                             │   │
│  │ onEmployeeCreate   │  │  calculateDailyMetrics()    │   │
│  │ onEmployeeArchive  │  │    - Open shifts            │   │
│  │ onPresenceUpdate   │  │    - Unavailable shifts     │   │
│  │ onCompanyEmailChg  │  │    - Conflicting timecards  │   │
│  │                    │  │                             │   │
│  │ → Update metrics   │  │  Daily at 6:00 AM Toronto   │   │
│  │   immediately      │  │  → Update batch metrics     │   │
│  └────────────────────┘  └─────────────────────────────┘   │
└─────────────────────────────────────────────────────────────┘

Technology Stack​

Frontend Dashboard​

  • Framework: Next.js 14 (App Router)
  • Styling: Tailwind CSS
  • Charts: Recharts
  • Data Grid: AG Grid Community
  • State Management: React Hooks (useState, useEffect)
  • Authentication: Google OAuth (@pivotapp.ca domain only)

Backend / Data Layer​

  • Database: Firebase Realtime Database (pivot-inc project)
  • Functions: Firebase Cloud Functions (Node.js)
  • Authentication: Firebase Admin SDK
  • Scheduler: Firebase PubSub Cron (6:00 AM Toronto daily)

Infrastructure​

  • Hosting: GCP Cloud Run (via GitHub Actions)
  • Domain: csm.pivotdev.ca (or similar)
  • SSL: Google Certificate Manager
  • Load Balancer: GCP HTTPS Load Balancer

Component Architecture​

Dashboard Application Structure​

pivot-csm-dashboard/
├── app/
│   ├── page.tsx                      # Main customer list view
│   ├── customer/[id]/page.tsx        # Individual customer detail
│   ├── alerts/page.tsx               # Alert management
│   ├── api/
│   │   ├── customers/route.ts        # GET /api/customers
│   │   ├── customer/[id]/route.ts    # GET /api/customer/:id
│   │   └── metrics/route.ts          # GET /api/metrics
│   └── layout.tsx
├── components/
│   ├── dashboard/
│   │   ├── CustomerList.tsx          # AG Grid customer table
│   │   ├── MetricCard.tsx            # Individual metric display
│   │   ├── TrendChart.tsx            # Sparkline/trend visualization
│   │   ├── AlertBadge.tsx            # Color-coded status indicator
│   │   └── FilterPanel.tsx           # Search/filter controls
│   ├── customer/
│   │   ├── CustomerHeader.tsx        # Company info header
│   │   ├── MetricsGrid.tsx           # All 5 metrics displayed
│   │   ├── HistoricalChart.tsx       # 90-day trend charts
│   │   └── ActivityTimeline.tsx      # Recent activity log
│   └── GoogleAuth.tsx                # Authentication wrapper
├── lib/
│   ├── firebase-admin.ts             # Firebase Admin SDK setup
│   ├── metrics.ts                    # Metric calculation helpers
│   ├── alerts.ts                     # Alert logic
│   └── utils.ts                      # Shared utilities
├── types/
│   ├── customer.ts
│   ├── metrics.ts
│   └── alerts.ts
└── public/

API Routes​

GET /api/customers​

Returns list of all companies with current health metrics.

Response:

[
  {
    "companyId": "-MCMchoX-9hNe0jRvC1H",
    "name": "Bloom Sushi QDS",
    "email": "contact@bloomsushi.com",
    "metrics": {
      "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"
    },
    "alertLevel": "warning",
    "lastUpdated": "2025-11-24T06:00:00Z"
  }
]

Query Parameters:

  • ?alertLevel=critical - Filter by alert level
  • ?sortBy=lastLogin - Sort options
  • ?search=bloom - Search by company name

GET /api/customer/:id​

Returns detailed information for a single company including historical data.

Response:

{
  "companyId": "-MCMchoX-9hNe0jRvC1H",
  "name": "Bloom Sushi QDS",
  "email": "contact@bloomsushi.com",
  "phone": "514-555-1234",
  "city": "Montreal, QC",
  "metrics": {
    "current": { ... },
    "history": [
      {
        "date": "2025-11-24",
        "numberofemployees": 127,
        ...
      }
    ]
  },
  "trends": {
    "employeeCount": {
      "7day": "+2",
      "30day": "-5",
      "90day": "+12"
    }
  }
}

GET /api/metrics​

Returns aggregated metrics across all companies (for dashboard overview).

Response:

{
  "totalCompanies": 350,
  "alertCounts": {
    "critical": 12,
    "warning": 45,
    "healthy": 293
  },
  "metricBreakdown": {
    "inactiveAccounts": 8,
    "highOpenShifts": 15,
    "unavailableAssignments": 23,
    "conflictingTimecards": 10
  }
}

Firebase Cloud Functions​

Real-time Metric Updates​

onEmployeeCreate​

export const onEmployeeCreate = functions.database
  .ref('/Employees/{companyId}/{employeeId}')
  .onCreate(async (snapshot, context) => {
    const companyId = context.params.companyId;

    // Count active employees
    const employeeCount = await countActiveEmployees(companyId);

    // Update metric
    await admin.database()
      .ref(`CustomerHealthMetrics/${companyId}`)
      .update({
        numberofemployees: employeeCount,
        lastUpdated: admin.database.ServerValue.TIMESTAMP
      });
  });

onEmployeeArchive​

export const onEmployeeUpdate = functions.database
  .ref('/Employees/{companyId}/{employeeId}')
  .onUpdate(async (change, context) => {
    const before = change.before.val();
    const after = change.after.val();

    // Check if archived status changed
    if (before.archived !== after.archived) {
      const companyId = context.params.companyId;
      const employeeCount = await countActiveEmployees(companyId);

      await admin.database()
        .ref(`CustomerHealthMetrics/${companyId}`)
        .update({
          numberofemployees: employeeCount,
          lastUpdated: admin.database.ServerValue.TIMESTAMP
        });
    }
  });

onPresenceStatusUpdate​

export const onPresenceStatusUpdate = functions.database
  .ref('/PresenceStatus/{userId}/lastActive')
  .onWrite(async (change, context) => {
    const userId = context.params.userId;

    // Get user's company and check if master account
    const user = await admin.database()
      .ref(`Users/${userId}`)
      .once('value');

    const userData = user.val();
    const companyId = userData.currentCompany;

    // Get company to check master account email
    const company = await admin.database()
      .ref(`Companies/${companyId}`)
      .once('value');

    if (company.val().email === userData.email) {
      // This is master account
      const lastActive = change.after.val();

      await admin.database()
        .ref(`CustomerHealthMetrics/${companyId}`)
        .update({
          last_login_date_master_account: lastActive,
          lastUpdated: admin.database.ServerValue.TIMESTAMP
        });
    }
  });

Scheduled Batch Metrics​

calculateDailyMetrics (Cron: 0 6 * * * America/Toronto)​

export const calculateDailyMetrics = functions.pubsub
  .schedule('0 6 * * *')
  .timeZone('America/Toronto')
  .onRun(async (context) => {
    // Get all companies
    const companiesSnapshot = await admin.database()
      .ref('Companies')
      .once('value');

    const companies = companiesSnapshot.val();

    for (const [companyId, companyData] of Object.entries(companies)) {
      try {
        // Calculate each metric
        const openShifts = await calculateOpenShiftsMetric(companyId);
        const unavailableShifts = await calculateUnavailableShiftsMetric(companyId);
        const conflictingTimecards = await calculateConflictingTimecardsMetric(companyId);

        // Update metrics
        await admin.database()
          .ref(`CustomerHealthMetrics/${companyId}`)
          .update({
            quarts_de_travail_ouverts_10_: openShifts,
            quarts_de_travail_attribues_hors_dispo_10: unavailableShifts,
            quarts_problematiques_50: conflictingTimecards,
            lastUpdated: admin.database.ServerValue.TIMESTAMP
          });

        // Store historical snapshot
        const today = new Date().toISOString().split('T')[0];
        await admin.database()
          .ref(`CustomerHealthMetrics/${companyId}/history/${today}`)
          .set({
            numberofemployees: companyData.employeeCount,
            quarts_de_travail_ouverts_10_: openShifts,
            quarts_de_travail_attribues_hors_dispo_10: unavailableShifts,
            quarts_problematiques_50: conflictingTimecards,
            timestamp: admin.database.ServerValue.TIMESTAMP
          });

      } catch (error) {
        console.error(`Error calculating metrics for company ${companyId}:`, error);
        // Log to Slack #support-logs
        await sendSlackAlert(`Metric calculation failed for ${companyId}: ${error.message}`);
      }
    }

    console.log(`Daily metrics calculated for ${Object.keys(companies).length} companies`);
  });

Metric Calculation Helpers​

async function calculateOpenShiftsMetric(companyId: string): Promise<string> {
  const last14Days = getLast14Days();
  const dailyPercentages = [];

  for (const date of last14Days) {
    const weekId = getWeekIdForDate(date);

    // Get draft schedules for this week
    const schedules = await admin.database()
      .ref(`Schedules/${companyId}/${weekId}/shifts`)
      .orderByChild('date')
      .equalTo(date)
      .once('value');

    if (!schedules.exists()) {
      continue; // No schedule for this day
    }

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

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

    if (regularShifts > 0) {
      const percentage = (openShifts / regularShifts) * 100;
      dailyPercentages.push(percentage);
    }
  }

  if (dailyPercentages.length === 0) {
    return "None"; // No schedules in last 14 days
  }

  const average = dailyPercentages.reduce((a, b) => a + b) / dailyPercentages.length;

  return average > 10 ? "Yes" : "No";
}

Authentication & Security​

Google OAuth​

  • Restrict to @pivotapp.ca domain
  • Use same OAuth client as pivot-kpi dashboard
  • Session management via Firebase Auth

Firebase Admin SDK​

  • Service account key stored in /home/chipdev/pivot-meta/pivot-devops/github/secrets/pivot-inc-firebase-sa.json
  • Database URL: https://pivot-inc.firebaseio.com
  • Read-only access to customer data (no write permissions from dashboard)

API Security​

  • All API routes protected by authentication middleware
  • Rate limiting on API endpoints
  • CORS restricted to dashboard domain only

Deployment​

GitHub Actions CI/CD​

name: Deploy CSM Dashboard

on:
  push:
    branches: [main]

jobs:
  build-and-deploy:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v3

      - name: Build Docker image
        run: docker build -t gcr.io/pivot-dev-59310/pivot-csm-dashboard .

      - name: Push to GCR
        run: docker push gcr.io/pivot-dev-59310/pivot-csm-dashboard

      - name: Deploy to Cloud Run
        run: |
          gcloud run deploy pivot-csm-dashboard \
            --image gcr.io/pivot-dev-59310/pivot-csm-dashboard \
            --platform managed \
            --region us-central1 \
            --allow-unauthenticated

Environment Variables​

NEXT_PUBLIC_GOOGLE_CLIENT_ID=594226458087-...
GOOGLE_CLOUD_PROJECT_ID=pivot-inc
FIREBASE_DATABASE_URL=https://pivot-inc.firebaseio.com
FIREBASE_SERVICE_ACCOUNT=/path/to/service-account.json
NEXT_PUBLIC_BASE_URL=https://csm.pivotdev.ca

Monitoring & Logging​

Application Logs​

  • Cloud Run logs for API requests
  • Firebase Cloud Functions logs for metric calculations
  • Error tracking via Sentry (optional)

Slack Alerts​

Send notifications to #support-logs for:

  • Failed metric calculations
  • Company matching errors
  • Critical alert thresholds exceeded

Performance Metrics​

  • API response times
  • Metric calculation duration
  • Database query performance

Scalability Considerations​

Current Scale​

  • ~350 active companies
  • 5 metrics per company
  • Daily batch processing
  • Low query volume (support team only)

Future Scale​

  • Firebase Realtime Database can handle thousands of companies
  • Consider sharding if >10,000 companies
  • Add caching layer (Redis) if API response time degrades
  • Implement pagination for customer list (100 per page)