Real-Time Team Availability Widget

1. The Core Problem: Information Silos in Hybrid Teams

In most engineering and product teams, daily status updates—such as Working From Home (WFH), Sick Leave, Casual Leave, or Office presence—are sent as routine emails:

• "Working from home today due to heavy rain."
• "Feeling unwell today (102° fever), taking sick leave."
• "Following up: WFH dates are 13-16 July, and I will be on leave on Friday 17 July."

While email is great for record-keeping, it is terrible for instant visibility. Team leads and engineers waste time searching through Outlook threads or asking on Slack just to answer one basic question: "Who is working from where today?"

Heavy HR management software fails because it requires manual check-in forms that employees forget to use. I wanted a system that required zero workflow changes: employees keep sending their standard emails, while the rest of the team gets an instant, glassmorphic desktop widget on macOS, Windows, and Linux displaying real-time team availability.

2. High-Level System Architecture

                       [ Outlook / Email ]
                                │
                                ▼ (Forwarded or Direct Email)
                     [ Resend Inbound Webhook ]
                                │
                                ▼ (POST payload containing email_id)
                 [ Supabase Edge Function (Deno) ]
                                │
    ┌───────────────────────────┴───────────────────────────┐
    │ 1. GET https://api.resend.com/emails/receiving/{id}   │
    │ 2. 3-Tier Identity Extraction (Forwarded Headers)     │
    │ 3. Intent Classification (WFH / Sick / Casual / Office)│
    │ 4. 7 PM Evening Threshold & Planned Leave Detection   │
    └───────────────────────────┬───────────────────────────┘
                                │
                                ▼
                   [ Supabase Postgres Database ]
                                │
                                ▼ (Postgres Change Streams / WebSockets)
             [ Cross-Platform Desktop App (React + Electron/Tauri) ]

3. Engineering Deep Dive & Key Architectural Solutions

A. Sure-Shot Forwarded Email Identity Matching

A common bottleneck in webhook-driven email processing occurs when team leads or distribution lists forward leave emails to the webhook address.

Standard webhook payloads set from to the forwarder's email address (lead@company.com). If processed naively, the system would attribute every team member's leave request to the lead!

The Solution: 3-Tier Identity Pipeline

When the webhook fires, our Deno Edge Function uses Resend's Receiving Email API (GET https://api.resend.com/emails/receiving/${email_id}) to pull raw MIME headers and body:

// Tier 1: Extract original sender email from forwarded MIME body headers
function extractForwardedEmail(bodyText: string): string | null {
  const match =
    bodyText.match(/From:\s*[^<\n]*<([^>]+)>/i) ||
    bodyText.match(/From:\s*([a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,})/i);
  return match && match[1] ? match[1].trim().toLowerCase() : null;
}

Resolution Hierarchy:

1. Forwarded Header Match: Parses From: Jane Doe <jane@company.com> out of the forwarded body.
2. Subject & Body Roster Match: Runs fuzzy matching against known team member names (e.g. "FW: WFH Notice - Jane Doe").
3. Direct Sender Fallback: Uses envelope sender only if no forwarded header or roster name exists.

This guarantees 100% accurate attribution without creating duplicate database rows.

B. Dual-State Model: Today's Status vs. Upcoming Planned Leave

Consider an email stating:

"I am working from home today, and I will be on planned leave next Friday (17th)."

Overwriting today's status badge to "Planned Leave" would be incorrect—the engineer is working from home today.

To model this accurately, the database schema uses a dual-state model:

CREATE TABLE public.team_status (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  name TEXT NOT NULL,
  email TEXT UNIQUE NOT NULL,
  role TEXT,
  avatar_gradient TEXT,
  status TEXT CHECK (status IN ('OFFICE', 'WFH', 'SICK_LEAVE', 'CASUAL_LEAVE')) DEFAULT 'OFFICE',
  reason TEXT,
  planned_leave TEXT,   -- Dedicated column for future/upcoming leave notices
  email_body TEXT,      -- Un-truncated full body of the last email
  last_updated TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

If an incoming email contains notices for future dates, the parser populates planned_leave without touching today's active working status (status). In the UI, a subtle purple tag 🟣 17th July Leave appears next to their role without disturbing their active dot indicator.

C. Smart 7:00 PM Threshold & Automatic Daily Reset

A major issue with availability trackers is stale status data (e.g., someone marked WFH yesterday remains marked WFH today).

We implemented two complementary rules:

1. Automatic Midnight Reset: When a new day begins, statuses from previous days automatically reset to OFFICE (Working from office today).
2. The 7:00 PM Evening Threshold Rule: Engineers often send status emails the evening before (e.g., sending "WFH tomorrow" at 8:30 PM).
   • If an email is received after 7:00 PM (19:00), the system recognizes that the update is intended for the upcoming workday.
   • When midnight arrives, statuses set after 7:00 PM the previous evening are preserved and NOT reset to Office!

public checkDailyReset() {
  const now = new Date();
  const todayStr = now.toISOString().split('T')[0];
  const yesterday = new Date(now);
  yesterday.setDate(yesterday.getDate() - 1);
  const yesterdayStr = yesterday.toISOString().split('T')[0];

  this.members = this.members.map((member) => {
    if (!member.lastUpdated) return member;

    const lastDate = new Date(member.lastUpdated);
    const lastDateStr = lastDate.toISOString().split('T')[0];
    const lastHours = lastDate.getHours();

    // If updated on a previous calendar day
    if (lastDateStr < todayStr && member.status !== 'OFFICE') {
      // Preserve status if email was received after 7:00 PM (19:00) yesterday
      const isAfter7PMYesterday = lastDateStr === yesterdayStr && lastHours >= 19;

      if (!isAfter7PMYesterday) {
        return {
          ...member,
          status: 'OFFICE',
          reason: 'Working from office today',
          lastUpdated: now.toISOString(),
        };
      }
    }
    return member;
  });
}

D. Real-Time Synchronization via Postgres Change Streams

Instead of polling HTTP endpoints every 30 seconds, the desktop application maintains an active WebSocket subscription to Supabase Realtime:

supabase
  .channel('public:team_status')
  .on(
    'postgres_changes',
    { event: '*', schema: 'public', table: 'team_status' },
    (payload) => {
      // Instantly update UI state & trigger native desktop notification (< 200ms)
      this.updateMemberStatus(payload.new);
    }
  )
  .subscribe();

When an email arrives, the Edge Function writes to Supabase, and within < 200ms, every desktop widget across the team updates smoothly with zero page refreshes.

E. Frontend & Packaging Architecture

• Glassmorphic UI: Built with React 18, TypeScript, and Tailwind CSS, featuring macOS Sequoia glassmorphism aesthetics, fixed badge widths (102px), full email body popovers on hover, and alphabetical sorting (A to Z).
• Packaging Flexibility:
  • Electron: .dmg installer & .exe executable with custom titlebar controls, pin always-on-top, and dark/light theme toggles.
  • Tauri v2: Lightweight bundle using native system WKWebView (~8 MB DMG size vs ~190 MB Electron size).
  • PWA: Service Worker (sw.js) and Web App Manifest (manifest.json) for instant zero-install mobile and web deployment.

4. Tech Stack Overview

5. Conclusion & Key Lessons

By combining serverless edge functions, inbound email webhooks, and real-time WebSocket database streams, we built an automated team availability tracker that completely eliminates manual status updates.

Key takeaways from this build:

1. Don't force new workflows: Tap into existing communication channels (like email).
2. Handle edge cases early: Forwarded headers, evening email thresholds, and multi-day leave notices require explicit data modeling.
3. Keep client footprints small: Support PWAs and Tauri alongside Electron to give users options ranging from 0 MB to full native desktop integration.