Export Data

Author: James Derick Billate
Reviewer: Jethro Lagmay and Sean Patrick Caintic
Creation Date: June 29, 2026
Status: Published
References: https://github.com/wyzlab/WyzQuests/issues/49

INTRODUCTION AND GOALS

Problem Summary: Agency Admins need to export agency team and learner dashboard data for reporting. The current feature supports CSV generation from the Agency Team and Agency Learners pages using already-loaded dashboard data.

Goals: Allow Agency Owners/Admins to view and export member and learner progress data in CSV format. The feature supports reporting for creators, reviewers, learners, and learner quest progress. Additionally, provide real-time visibility into online presence and a continuous audit trail of login/logout activity across the agency.

Non-Goals: Does not provide PDF export, scheduled reports, cross-agency reports, deeper business intelligence dashboards, or automated alerting on presence changes.


HIGH-LEVEL ARCHITECTURE

System Diagram:

Technologies Used:

Frontend: Next.js App Router, React client components, Tailwind CSS, Shadcn UI components, Sonner toast, Lucide icons, PapaParse.

Backend: RESTful API routes, TypeScript, Clerk authentication, agency authorization helpers, Supabase queries, standardized API responses.

Storage/Database: Supabase Postgres tables for agencies, agency_members, app_users, quests, public_share, and quest_enrollments.

External Services: Clerk for authentication and user identity.

Real-Time Presence Tracking: Presence is derived from a periodic client heartbeat rather than a persistent WebSocket or SSE connection. A global `IdleTimeoutProvider` sends a `POST /api/user/heartbeat` request on sign-in, every 60 seconds thereafter, and on explicit sign-out. The heartbeat writes `app_users.last_seen_at`, and the dashboard treats a user as **Online** when `last_seen_at` is within a 2-minute window (one minute of tolerance for a single missed beat).


DETAILED DESIGN AND IMPLEMENTATION

Schema:

Export Data does not create a dedicated export table. It reads from existing agency, user, quest, and enrollment data. CSV files are generated from in-memory frontend data.

Real-Time Presence & Activity Tracking does create a new audit table, `user_presence_logs`, and adds two columns to `app_users`. This is the only feature in this document that introduces new persistent storage.

Relevant data sources:

agencies: Stores agency ID, name, plan, owner Clerk ID, and monthly token budget.

agency_members: Stores agency member role, status, invited email, user Clerk ID, and token limit.

app_users: Provides display name, email, platform role, app user ID, and Clerk ID. Extended with `last_seen_at` (TIMESTAMPTZ) and `metadata` (JSONB) which holds the `last_device` string.

quests: Provides creator-owned quest data for agency creator analytics.

public_share: Used to identify shared quests for creator analytics.

quest_enrollments: Provides learner enrollment and progress data.

Presence Tracking Data Sources:

app_users.last_seen_at:Timestamp of the user's most recent heartbeat. `NULL` after sign-out, so a cleared heartbeat is never counted as online.

app_users.metadata->>'last_device: Last detected OS and device form factor, e.g. `Windows (Desktop)`.

user_presence_logs: Append-only audit trail of `LOGIN` and `LOGOUT` events scoped to an agency.

user_presence_logs Schema

id

UUID PK

Default gen_radom_uuid()

clerk_id

TEXT NOT NULL

FK → app_users(clerk_id) ON DELETE CASCADE

agency_id

UUID NULL

FK → agencies(id) ON DELETE CASCADE

role

TEXT NULL

Resolved role at time of event: OWNER, CREATOR, REVIEWER, LEARNER, AGENCY, ADMIN, or USER

action

TEXT NOT NULL

LOGIN or LOGOUT

device_info

TEXT NULL

Parsed User-Agent summary, e.g., Android (Mobile)

created_at

TIMESTAMPTZ

Default: now(); serves as the event timestamp

An index on `(agency_id, created_at DESC)` supports the descending, agency-scoped dashboard query. Row Level Security is enabled, with policies allowing a user to insert and read their own logs, agency Owners/Admins to read logs for their own `agency_id`, and the service role full access.

Schema note: `action` carries no `CHECK (action IN ('LOGIN','LOGOUT'))` constraint, and the policies have no `DROP POLICY IF EXISTS`, so the migration is not safe to re-run as-is.

Presence Flow
1. `IdleTimeoutProvider` fires `POST /api/user/heartbeat` on sign-in and every 60 seconds.
2. The route parses the `user-agent` header into an OS and device form factor, then writes `app_users.last_seen_at` and merges `last_device` into `app_users.metadata`.
3. Logout events additionally insert a `LOGOUT` row and set `last_seen_at` to `NULL`.
4. The Agency Dashboard fetches members and learners, then buckets each record into a role and evaluates the 2-minute online threshold.
5. Presence cards render `{active}/{total}` per role, owner-only.
6. The Continuous Activity Log renders the most recent `user_presence_logs` rows, paginated 10 per page.

API Specification:

GET /api/agency/status
Checks the authenticated user's agency membership, ownership, effective role, and agency info.

Body: NONE

Status

Return

401

Unauthorized

500

Failed to check membership or fetch agency info

200 OK

Data: isMember, isOwner, platformRole, platformRoles, effectiveRole, isViewingAsOther, agency, membership

GET /api/agency/team/list
Fetches agency details, agency members, enriched display names/platform roles, creator quest analytics, and current user Clerk ID.

Body: NONE

Status

Return

401

Unauthorized

403

Forbidden when user is not an Agency Owner/Admin

404

Agency not found

500

Failed to fetch members

200 OK

Data: agency, members, quests, currentUserClerkId

GET /api/agency/learners
Fetches learners connected to agency-created quests and includes their enrolled quest progress.

Body: NONE

Status

Return

401

Unauthorized

403

Forbidden when user is not an Agency Owner/Admin

404

Agency not found

500

Failed to fetch members, quests, enrollments, or learners

200 OK

Data: agency, learners, currentUserClerkId

POST /api/agency/team/claim
Best-effort endpoint called before loading agency pages to claim pending invites for the current user.

Body: NONE

Status

Return

401

Unauthorized

200 OK

Invite claim result or no-op response

POST /api/user/heartbeat

Records presence for the authenticated user and drives both the online counts and the activity log. Runs on a 60-second interval from `IdleTimeoutProvider` and on explicit sign-out.

Body: `{ "logout": boolean }` (optional; `logout` is read via `request.json().catch(() => ({}))` and defaults to falsy)
Headers: Standard `user-agent` is read for device detection.

Status

Return/Description

‘401’

Unauthorized

‘500’

Failed to update the heartbeat or an unhandled processing error

‘200 OK’

Data: {message} where message is “Heartbeat acknowledge” or ”User offline”

Device Detection: The user-agent header is matched to a device form factor (Desktop default, Mobile on /mobile/i, Tablet on /tablet/i) and an OS (Windows, macOS, Android, iOS, Linux, else Unknown OS). The two are combined into "<OS> (<Device>)", falling back to the bare device string when the OS is unknown.

Smart Deduplication: Heartbeats fire continuously, so a raw insert-per-beat would flood the log. A `LOGIN` row is written only when:
* No prior log exists for the user, **or**
* The most recent log was a `LOGOUT`, **or**
* The most recent log was a `LOGIN` older than `SESSION_TIMEOUT_MS` (30 minutes), treated as a session refresh.

Otherwise the heartbeat only updates last_seen_at and no row is inserted, so page reloads do not produce duplicate LOGGED IN entries. LOGOUT rows are always written.

Role Resolution: OWNER takes precedence when the user owns the agency, then agency_members.role, then app_users.role, then USER. agency_id is set from the member record, falling back to the owned agency, else NULL

GET /api/agency/admin/members

Fetches enriched agency members with token usage, presence fields, and the Continuous Activity Log payload. This route is the single data source for both the presence cards and the activity log table.

Body: NONE

Status

Return/Description

401

Unauthorized

403

Forbidden when the user is not an Agency Owner/admin`

404

Agency not found

500

Failed to fetch members, usage, or presence logs

200 OK

data: ‘members’, ‘activityLogs’, ‘agency’

Presence Enrichment:
- Each member row is joined against app_users to add display_name, last_seen_at, and last_device (from metadata).
- The agency owner is **not** a row in agency_members, so the route injects a synthetic OWNER member id: "owner-<clerkId>", display_name suffixed with " (Owner)") to make the owner visible in presence counts, token usage, and the activity log.
- Learner Clerk IDs are resolved by walking creators → `quests` → quest_enrollments → app_users, then unioned with member Clerk IDs, so learner login activity appears in the agency's log.

Activity Log Query: user_presence_logs is selected ordered by created_at DESC with a hard .limit(100), filtered by agency_id.eq.<id> OR clerk_id.in.(<known ids>). Each row is mapped to the client contract:

Field

Source

id

user_presence_logs.id

action

user_presence_logs.action

device

user_presence_logs.device_info

timestamp

user_presence_logs.created_at

userName

app_users.name viuserDataMap, falling back to ”Unknown User”

role

user_presence_logs.role

GET /api/agency/learners (presence fields)

The learners response is extend with presence data for the Learners presence card.

Body: NONE

Status

Return/Description

401

Unauthorized

403

Forbidden when user is not an Agency Owner/Admin

404

Agency not found

500

Failed to fetch members, quests, enrollments, or learners

200 OK

data: agency, learners, currentUserClerkId

Each flattened learner carries display_name, id, invited_email last_seen_at, last_device, and quests. The dashboard types these presence fields loosely because AgencyLearner does not declare them.

Client CSV Generation:

Team export uses Papa.unparse on agency member data after removing internal fields such as agency_id, created_at, user_clerk_id, platform_role, and isCurrentUser.

Otherwise the heartbeat only updates `last_seen_at` and no row is inserted, so page reloads do not produce duplicate `LOGGED IN` entries. `LOGOUT` rows are always written.

Learner export uses Papa.unparse on a flattened learner quest dataset:

  • learner_id

  • learner_name

  • quest_title

  • percentage

  • last_visited_at

Real-Time Presence Cards

Four status cards render below the core metric cards, inside an isOwner-gated lg:grid-cols-4 grid. Each shows an {active}/{total} "Online now" value:

Card

Icon

Source bucket

Creators

PenTool

agency_members.role=== CREATOR

Learners

GradutionCap

Learners from /api/agency/learners

Reviewers

ClipboardCheck

agency_members.role === REVIEWER

Agency

ShiledCheck

role IN (AGENCY, OWNER, ADMIN)

Online Evaluation:

const ONLINE_THRESHOLD_MS = 2 * 60 * 1000; // heartbeat is 60s; tolerate one missed beat
const isOnline = (lastSeenAt?: string) => {
if (!lastSeenAt) return false;
const lastSeenDate = new Date(lastSeenAt).getTime();
if (isNaN(lastSeenDate) || lastSeenDate === 0) return false;
return (Date.now() - lastSeenDate) < ONLINE_THRESHOLD_MS;
};

The isNaN=== 0 guard exists because a cleared heartbeat returns NULL and would otherwise be interpreted as an epoch timestamp in some JS contexts.

Presence data is fetched only when the viewer has owner access; agency Admins do not trigger the members/learners presence queries.

Continuous Activity Log
An owner-visible table titled "Continuous Activity Log" with a Time, User, Role, Action, and Device / OS column set.

Action Badge: LOGIN renders as a green LOGGED IN badge, LOGOUT as a red LOGGED OUT badge.
Device Icon: A Smartphone icon is used when the device string contains mobile or tablet, otherwise a Monitor icon. The raw device string, or "Unknown" when NULL, is displayed beside it.
Empty State: A single colSpan={5} row reading "No activity recorded yet."

Timestamp Formatting: date-fns format(new Date(log.timestamp), 'hh:mm:ss a') — 12-hour clock with AM/PM and monospace styling. Only time-of-day is rendered; the log date is not shown.

Pagination: Client-side only, itemsPerPage = 10, implemented with Array.slice over the already-fetched historyLogs array. Previous/Next buttons clamp to the valid range and are hidden entirely when there is a single page. The underlying API caps at 100 rows, so pagination is bounded to at most 10 pages. There is no shared pagination component; the controls are hand-rolled inline in the page.

Core Logic and Workflow:

  • Agency page calls /api/agency/status to determine whether the user can manage agency data.

  • Team page calls /api/agency/team/list and stores agency, members, and quest analytics data.

  • Learners page calls /api/agency/learners and stores agency and learner quest progress data.

  • Admin clicks Export Team Data or Export Learner Data.

  • Frontend maps the loaded dataset into an export-safe shape.

  • PapaParse converts the mapped data into CSV.

  • Browser creates a Blob object URL and downloads the CSV using an agency-name filename.

  • Success or failure is shown through toast feedback.

Presence & Activity Log Workflow
1. On sign-in, IdleTimeoutProvider fires an immediate POST /api/user/heartbeat.
2. The heartbeat repeats every 60 seconds while the user is signed in, and fires once more with { logout: true } on sign-out or a Clerk signed-out transition.
3. The Agency Dashboard resolves owner access, then fetches /api/agency/admin/members and /api/agency/learners in parallel.
4. The members route returns enriched members plus the 100 most recent activityLogs.
5. The dashboard buckets members by role, applies the 2-minute online threshold, and populates the four presence cards.
6. The dashboard renders activityLogs through the 10-row client-side pagination controls.
7. Timestamps render as hh:mm:ss a; device strings render with a `Smartphone` or Monitor icon.


INFRASTRUCTURE AND OPERATIONS

Dependencies: Clerk Authentication, Supabase Database, agency authorization helpers, PapaParse, Agency Team dashboard, Agency Learners dashboard, Agency Dashboard app/agency/page.tsx), IdleTimeoutProvider (heartbeat driver, mounted via ClerkProviderWrapper), standardized API response helper.

Monitoring and Alerting:

  • Monitor API errors from /api/agency/team/list and /api/agency/learners.

  • Monitor API errors from `/api/agency/admin/members`, which is now also the activity log source.

  • Monitor heartbeat failures. The route logs `[heartbeat] Failed to update user presence` and `[heartbeat] Error`; the client's fetch intentionally swallows network errors, so silent heartbeat failure surfaces only as permanently stale presence counts.

  • Monitor Supabase query failures for agency members, app users, quests, and enrollments.

  • Check browser/client errors for CSV generation and file download failures.

Deployment Plan:

  • Ensure agency membership and agency ownership authorization is configured.

  • Ensure agency team and learner APIs return only scoped agency data.

  • Ensure PapaParse is installed and bundled with the frontend.

  • Validate CSV export behavior in common browsers.

  • Apply `supabase/migrations/20260717_add_presence_tracking.sql` before deploying the heartbeat route, since it creates `user_presence_logs` and the `app_users.last_seen_at` / `app_users.metadata` columns.

  • Confirm `app_users.clerk_id` is unique; the `user_presence_logs` foreign key depends on it.

  • Verify the heartbeat can insert logs, noting that the Supabase client is service-role and bypasses the `authenticated` RLS policies.

  • Verify presence counts and the activity log populate for an agency with members, learners, and an owner.


TESTING AND QUALITY ASSURANCE

Testing Strategy:

  • Sign in as Agency Owner and export Team CSV.

  • Sign in as Agency Owner and export Learner CSV.

  • Verify non-owner/non-admin agency member cannot access export pages.

  • Verify CSV filename sanitizes agency name spaces and special characters.

  • Verify exported team CSV excludes internal fields.

  • Verify exported learner CSV flattens multiple quests per learner into separate rows.

  • Verify no-agency, no-member, and no-learner states do not crash export pages.

  • Verify the four presence cards render `{active}/{total}` and only for owners.

  • Verify a user is counted online while heartbeating and drops offline after the 2-minute threshold.

  • Verify signing out sets `last_seen_at` to `NULL` and does not count as online.

  • Verify reloading the page does not create a duplicate `LOGGED IN` row.

  • Verify a `LOGIN` is recorded after a `LOGOUT` and after the 30-minute session timeout.

  • Verify the Activity Log paginates at 10 rows per page and clamps Previous/Next at the boundaries.

  • Verify timestamps render as `hh:mm:ss a`.

  • Verify the Device column shows the correct `Smartphone` vs `Monitor` icon for mobile, tablet, and desktop user agents.

  • Verify the owner appears in the Agency presence card and in the activity log.

  • Verify learner login activity is included in the agency's activity log.

Known Limitations:

  • CSV generation is currently client-side and depends on already-loaded dashboard data.

  • There is no dedicated backend route such as `/api/agency/admin/export-csv`.

  • Large agencies may generate heavy client-side CSV files.

  • Export does not currently support date range filters.

  • Export does not currently support PDF or scheduled delivery.

  • Presence is polled via a 60-second heartbeat, not a live connection, so counts lag by up to one interval and are approximate.

  • Online status is heuristic. A user who closes the tab without signing out stays "online" until the 2-minute threshold expires; a heartbeat that fails silently also looks offline.

  • `user_presence_logs` has no `CHECK` constraint on `action`, so invalid actions are storable.

  • The `user_presence_logs` migration lacks `DROP POLICY IF EXISTS`, making re-runs fail on duplicate policies.

  • The activity log query interpolates Clerk IDs into a PostgREST `.or()` string without escaping.

  • Activity log retrieval is capped at 100 rows server-side with no cursor, so pagination cannot reach history beyond that limit and there is no date filter.

  • `app_users.clerk_id` must be unique for the presence log foreign key to hold; an `ON UPDATE CASCADE` follow-up migration is required for Clerk-to-Supabase backfills.

  • `user_presence_logs` grows indefinitely and is not yet archived or pruned.

  • The 30-minute dedup window is a heuristic, not a real session concept; long-lived sessions with intermittent heartbeats can log repeated `LOGIN` entries.

  • Device detection matches OS and form factor only, never the browser, and modern iPad user agents resolve as `macOS (Desktop)`.
    * The `userDataMap` built in `/api/agency/learners` is unused, and the dashboard computes a `recentActivity` list that is never rendered.


MAINTENANCE AND SUPPORT

  • Maintain CSV field mapping whenever agency member, learner, quest, or enrollment response shapes change

  • Review exported fields before adding sensitive data such as private IDs, emails, or internal role metadata.

  • If export volume grows, move CSV generation to a backend route with streaming or paginated export.

  • Ensure future analytics changes remain scoped by agency authorization rules.

  • Keep `ONLINE_THRESHOLD_MS` (2 min) and the heartbeat interval (60s) in sync; the threshold must stay above one interval.

  • Reassess `SESSION_TIMEOUT_MS` (30 min) if product session semantics change, since it governs dedup behavior.

  • Add a `CHECK` constraint on `user_presence_logs.action` and `DROP POLICY IF EXISTS` guards to the migration before broader rollout.

  • Add server-side pagination or a date-range filter to the activity log once agencies exceed 100 log events.

  • Add a scheduled retention or archival job for `user_presence_logs`.

  • Verify the presence `SELECT` RLS policy whenever agency role values change, since it enumerates `OWNER` and `ADMIN` explicitly.


Document Version

1.0 - Draft, feature enhanced from current hand-off documentation 07/07/2026

1.1 - Draft, enhancement of documentation 07/09/2026

1.2 - Draft, minor enhancements 07/14/2026

1.3 - Draft, added Real-Time Presence Cards, Continuous Activity Log, heartbeat API, smart deduplication, device detection, pagination, and user_presence_logs schema | 07/21/2026




Was this article helpful?