Google Calendar Synchronization

Attendrix automatically synchronizes each student's personalized class schedule with their Google Calendar account. The synchronization pipeline is designed to be bi-directionally resilient, rate-limit compliant, and fully asynchronous.

1. End-to-End Pipeline Architecture

The synchronization flow connects database triggers, transactional message queues, Deno edge workers, and the Google Calendar REST API:

sequenceDiagram
    autonumber
    participant Admin as Admin / Timetable System
    participant DB as PostgreSQL (public.classes)
    participant Trigger as Statement Trigger (fn_classes_gcal_statement_coalesce)
    participant Queue as PGMQ (gcal_sync_queue)
    participant Worker as Edge Worker (google-calendar-sync-worker)
    participant Vault as Supabase Vault (Encrypted Refresh Token)
    participant Google as Google Calendar v3 API

    Admin->>DB: Bulk UPDATE classes SET venue = 'Room 301' WHERE batch_id = 'CSE_2023'
    Note over DB,Trigger: 50 rows updated in single transaction
    Trigger->>Queue: pgmq_send('gcal_sync_queue', {type: 'STATEMENT_COALESCED', batch_id: 'CSE_2023'})
    Note over Trigger,Queue: Exactly 1 message enqueued (coalesced)

    Worker->>Queue: pgmq_read('gcal_sync_queue', vt: 180, qty: 10)
    Queue-->>Worker: Claim message (vt locked for 180s)
    
    Worker->>DB: acquire_gcal_user_sync_lease(userId, workerId, 45)
    DB-->>Worker: Lease Granted (true)
    
    Worker->>Vault: get_gcal_vault_secret(vault_secret_id)
    Vault-->>Worker: Decrypted Google Refresh Token
    Worker->>Google: Exchange refresh token for access token
    
    Worker->>Google: PUT /calendars/{calId}/events/{deterministicId}
    Google-->>Worker: 200 OK
    
    Worker->>DB: release_gcal_user_sync_lease(userId, workerId)
    Worker->>Queue: pgmq_delete('gcal_sync_queue', msg_id)

2. Distributed Leases & Worker Idempotency

When multiple worker instances scale out under load, two workers must never attempt to mutate the same student's calendar concurrently:

1. Lease Acquisition: Before executing calendar API calls, the worker calls acquire_gcal_user_sync_lease.
2. Lease TTL: Leases expire automatically after 45 seconds to prevent permanent deadlocks if an isolate crashes.
3. Deterministic Google Event IDs: Event IDs are generated deterministically using the SHA-256 hash of ${userId}:${classId} encoded in base32:

Event ID = attendrix + base32(sha256(userId + ":" + classId))[0..40]

Because the ID is mathematically derived from the database keys, re-running a job produces an identical Google Event ID. The Google Calendar API treats repeated inserts as idempotent updates or 409 conflicts.

3. PGMQ Visibility Timeouts & Poison Pill DLQ

• Visibility Timeout (vt = 180s): When a worker claims a message, it becomes invisible to other workers for 3 minutes.
• Dynamic VT Extension: Long-running batch syncs extend the timeout via pgmq_set_vt.
• Poison Pill Archive (DLQ): If a job fails repeatedly (read_ct >= 5), the worker archives it to the dead-letter queue via pgmq_archive to prevent continuous queue blocking.