Feature Owner: James Derick Billate
Module: Dashboards
Priority: Could
Date: July 7 2026
Executive Summary
What is this feature?
Export Data allows Agency Admins to view and export agency team and learner information into CSV files. The feature supports agency-level reporting by giving admins access to member status, creator/reviewer information, learner quest enrollment, and learner progress data from the Agency dashboards.
Real-Time Presence and the Continuous Activity Log extend the Agency Dashboard so the agency Owner can see who is online right now and audit who signed in or out, from which device. The dashboard shows four status cards (Creators, Learners, Reviewers, Agency) with live online counts, and a paginated table recording every LOGIN and LOGOUT event across the agency.
Why does it matter?
Agency Admins often need to create reports for team activity, learner progress, content quality, and operational review. Without export support, they must manually copy data from the dashboard and clean it in spreadsheets, which is time-consuming and error-prone. Export Data reduces manual reporting work and gives agencies a more reliable way to transfer dashboard information into external tools.
Presence and activity history answer a separate operational question: is the team actually active right now, and who has been accessing the platform? Owners previously had no visibility into real-time availability and no audit trail, so they relied on guesswork or external tools to answer "who is online" and "when did this person last sign in."
What's the MVP scope?
Creator analytics overview.
Reviewer analytics overview.
Learner analytics overview.
Export team members as
.csv.Export learner quest progress as
.csv.Real-time presence cards for Creators, Learners, Reviewers, and Agency showing online counts derived from user heartbeats.
Continuous Activity Log table recording every LOGIN and LOGOUT event across the agency.
Device and operating system detection displayed per activity event.
Client-side pagination of the Activity Log at 10 entries per page.
Smart deduplication of LOGIN events so page reloads do not create duplicate entries.
1. User Pain Point & Solution
John manages an agency with invited creators, reviewers, learners, and assigned quests. He needs to review team workflow, content quality, and learner progress, but without export tools he must manually type or copy information from the dashboard into a spreadsheet. Export Data gives him a direct CSV file that can be used for reporting, audits, and deeper analysis.
Pain Points
Emotional: Tired, exhausted, confused, and unsettled when reporting takes too much manual work.
Functional: No direct data transfer, no clean report file, higher risk of wrong inputs, missing data, and spreadsheet incompatibility.
Business: Agencies need efficient processing and better understanding of team and learner data to improve workflow and quest delivery.
2. User Pain Point & Solution
Diagnose: Provides Agency Admins a way to check whether the agency workflow is effective and whether learners are progressing through assigned quest content. For the Owner, it additionally shows who is online right now and provides an audit trail of who signed in or out.
Design: Generates exportable reports that summarize team composition, learner enrollment, and learner completion progress. Adds four real-time presence status cards and a paginated Continuous Activity Log table with time, user, role, action, and device/OS columns.
Develop: Supports agency operations by making creator, reviewer, and learner data easier to review and process. Tracks presence through a 60-second client heartbeat that writes a last-seen timestamp per user, with a 2-minute online threshold, and records discrete LOGIN/LOGOUT events deduplicated by the user's previous action.
Deliver: Helps the agency make better decisions and improve quest quality through clean reporting and external analysis. Gives the Owner immediate operational awareness of team availability plus a reviewable sign-in history for compliance and troubleshooting.
Second Persona Pain Point (Agency Owner)
Emotional: Frustrated and blind when coordinating a distributed team, unable to tell whether a creator is currently available or working.
Functional: No way to answer "who is online" or "when did this user last sign in" from inside the product.
Business: No audit trail for access review or for investigating access-related incidents.
3. USER FLOWS
Entry Points: Agency Admin logs in and navigates to the Agency workspace, then opens either Team or Learners. Agency Owner signs in and opens the Agency dashboard directly to see presence cards and the activity log. Presence is also recorded passively from any signed-in page in the app via the global heartbeat provider.
Success Criteria: Agency Admin can view team or learner dashboard data and export the visible data as a CSV file. Agency Owner can see real-time online counts per role and review a paginated log of LOGIN and LOGOUT events.
Main Path (Happy Path)
Agency Admin signs in.
Agency Admin opens the Agency dashboard.
Agency Admin navigates to Team.
System loads agency members, agency information, and creator/reviewer analytics.
Agency Admin clicks Export Team Data.
System downloads a CSV file named with the agency name and export type.
Agency Admin navigates to Learners.
System loads learners and their enrolled quest progress.
Agency Admin clicks Export Learner Data.
System downloads a CSV file containing learner, quest, progress, and last visited data.
Main Path (Happy Path) - Presence and Activity Log
• User signs in anywhere in the app. The global heartbeat provider immediately posts a heartbeat, then repeats every 60 seconds.
• System records the user's last-seen timestamp, and detects the operating system and device form factor from the user-agent header.
• System records a LOGIN event, unless the user's previous event was already an active LOGIN inside the 30-minute session window.
• Agency Owner opens the Agency dashboard.
• System fetches agency members (including presence fields and recent activity logs) and learners in parallel.
• System resolves learner Clerk IDs so learner sign-ins are included in the agency's log.
• System injects a synthetic OWNER member so the owner is counted in presence and visible in the log.
• Dashboard renders the four presence cards with active/total online counts per role.
• Dashboard renders the Continuous Activity Log, newest first, 10 entries per page.
• Owner reads an entry: time in hh:mm:ss AM/PM, user, role, action badge, device/OS with icon.
• Owner clicks Next or Previous to page through history.
• A user signs out. The heartbeat posts logout: true, the system clears last-seen and records a LOGOUT event.
• The signed-out user is no longer counted as online on any card.
No Data: Display empty table or no-member/no-learner state when agency data does not exist. For the activity log specifically, show the "No activity recorded yet." empty state rather than an error.
API Error: Stop the listing/export flow and display the proper error message through toast or page state. A heartbeat failure is intentionally NOT surfaced to the user; it is logged server-side and presence counts simply go stale.
Permission Denied: Block the request and prevent non-admin users from viewing or exporting agency data. Additionally, prevent non-Owner agency members (including Agency Admin) from viewing presence counts or the activity log.
Decision Points:
IF agency exists THEN display Team or Learners dashboard ELSE show create agency or no-agency state.
IF user is Agency Owner THEN allow presence data and activity log fetch, and the dashboard renders the four presence cards and the Continuous Activity Log. IF user is Agency Admin member THEN they can manage the team but do NOT see the presence cards or the activity log. IF user is not an Owner/Admin THEN show access restricted state.
IF Team export is clicked THEN export current team member dataset ELSE remain on Team dashboard.
IF Learner export is clicked THEN export learner quest progress dataset ELSE remain on Learner dashboard.
IF export generation fails THEN show export error toast ELSE show success toast.
IF the heartbeat fails silently (network error) THEN presence counts remain stale until the next successful beat (the client intentionally does not surface a toast).
4. Information Architecture
Primary Information
Agency information.
Agency member information.
Creator information.
Reviewer information.
Learner information.
Learner enrolled quest information.
Learner quest progress.
Real-time online count per role: Creators, Learners, Reviewers, Agency, each as active/total.
Continuous Activity Log entries: time, user, role, action, device/OS.
Secondary Information
Analytics type: Team or Learners.
Member role.
Member status.
Creator quest counts.
Shared quest counts.
Learner completion rate.
Last visited quest timestamp.
Activity action type (LOGGED IN / LOGGED OUT) with color-coded badge.
Detected operating system and device form factor (Mobile, Tablet, Desktop).
Activity log page position (Page X of Y).
Tertiary Information
Agency plan.
Monthly token budget.
Current user marker.
CSV filename.
Online threshold (2 minutes) and heartbeat interval (60 seconds) as background system behaviour.
Session dedup window (30 minutes) as background system behaviour.
Total token usage percentage and system health status.
Active-this-month member count.
Actions
Primary CTA
"Export Team Data"
"Export Learner Data"
Secondary Actions
Open Team.
Open Learners.
Switch between All, Creators, and Reviewers.
Refresh dashboard data.
Invite Member.
View individual learner quest details.
Activity Log "Previous" page button (disabled on page 1).
Activity Log "Next" page button (disabled on the last page).
Activity Log pagination controls, hidden entirely when there is a single page.
Note: presence and activity log have no dedicated CTA. They are ambient read-only panels on the Agency dashboard, gated to Agency Owner.
5. Data Requirements
Frontend Needs
Agency ID, name, monthly token budget, and plan.
Agency member ID, invited email, display name, role, status, monthly token limit, and platform role.
Creator quest count and public share count.
Learner ID, learner name, enrolled quests, quest title, progress percentage, and last visited timestamp.
Current user's agency membership and ownership/admin status.
API Calls Frontend Will Make
GET
/api/agency/statusGET
/api/agency/team/listGET
/api/agency/learnersPOST
/api/agency/team/claimGET /api/agency/admin/membersPOST /api/user/heartbeat
6. Performance Considerations
Database Optimization
Export Data does not require a dedicated database table because it exports already available dashboard data.
Export Data does not require a dedicated database table because it exports already available dashboard data
Presence tracking DOES require a dedicated table, user_presence_logs, plus last_seen_at and metadata columns on app_users. The app_users metadata write must be a read-merge-write (fetch existing metadata, spread, then update) to avoid clobbering unrelated keys such as browser info.
The activity log query is capped at 100 rows server-side, and pagination is client-side over that capped set. Large agencies therefore cannot page beyond the newest 100 events.
Index (agency_id, created_at DESC) supports the descending, agency-scoped activity log fetch.
Use agency ID, user clerk ID, learner ID, and quest ID filters to avoid cross-agency reads.
The activity log filter is an OR across agency_id and an IN list of known Clerk IDs, so the IN list grows with agency size. Batch related lookups where possible to avoid N+1 query patterns.
Learner Clerk IDs are resolved by walking creators to quests to enrollments to app_users on every members request. This is a chained lookup, not a batch.
Fetch only required fields for agency, members, quests, enrollments, learner progress, and presence fields.
The heartbeat is a write every 60 seconds per signed-in user, executed through the service-role Supabase client.
This write volume scales with concurrent users and should be monitored.
The heartbeat on mount performs 3 sequential reads (last log, app_users role, agency_members, agencies) before any write. This runs on every page load for every user.
7. Security & Authorization
Who Can Access This Feature?
Administrator: No access.
Reviewer: No access.
Creator: No access.
Learner: No access.
Agency Admin: Full access.
Important distinction: the presence cards and Continuous Activity Log are OWNER-only, not Admin-inclusive. The dashboard gates both the presence data fetch (GET /api/agency/admin/members + GET /api/agency/learners) and the card rendering on isOwner. An Agency Admin who can manage the team and manage finances-adjacent data will not see presence or activity history at all.
Row Level Security on user_presence_logs permits: a user reading their own logs; agency Owners and Admins reading logs where agency_id matches their agency; and service role full access. Note the Supabase client in the heartbeat route is service-role, so the authenticated insert policy is bypassed in practice.
Known security risks and constraints:
- The activity log query interpolates Clerk IDs into a PostgREST or() filter string without escaping. This is a read-side scoping concern, not a direct injection path, but it should be reviewed.
- user_presence_logs.action has no CHECK constraint, so arbitrary action values can be written.
- The presence migration is not idempotent: no DROP POLICY IF EXISTS, so a re-run fails on duplicate policies.
- user_presence_logs.clerk_id references app_users(clerk_id), which requires that column to be unique. A Clerk-to-Supabase backfill that updates clerk_id will fail without an ON UPDATE CASCADE follow-up migration.
- Presence and activity history reveal individual sign-in behaviour of learners and staff. This is sensitive workforce-monitoring-adjacent data and should be reviewed with the product owner before wide rollout.
8. Error Handling
Current Existing Error Handling
Invalid credentials -> 401 Unauthorized.
Expired session -> 401 Unauthorized with redirect to login.
Unauthorized agency member -> access restricted page.
Agency not found -> 404 Not Found.
Failed team data fetch -> toast error message.
Failed learner data fetch -> toast error message.
Failed server connection -> toast error message.
Failed CSV generation -> export failure toast.
Failed CSV generation -> export failure toast.
Failed heartbeat -> 500 from POST /api/user/heartbeat, logged server-side as [heartbeat]
Failed to update user presence. Client-side the failure is deliberately swallowed, so the user sees no error and presence counts simply go stale.
Heartbeat with no authenticated session -> 401 Unauthorized.
Activity log returns no rows -> table shows the No activity recorded yet. empty state.
Activity log contains a user no longer in the agency -> row displays Unknown User rather than breaking the table.
Activity log has a null device_info -> Device column shows Unknown with a desktop monitor icon.
9. Testing Checklist
Happy Path
Sign in as an Agency Owner/Admin.
Navigate to Agency Team.
Team members list loads successfully.
Creator and Reviewer filters display correct member subsets.
Click Export Team Data.
CSV file downloads with expected agency-based filename.
CSV excludes internal-only fields that should not appear in reports.
Navigate to Agency Learners.
Learner list loads successfully.
Click Export Learner Data.
CSV file downloads with learner ID, learner name, quest title, progress percentage, and last visited timestamp.
Presence and Activity Log (Owner only)
- Sign in as Agency Owner and open the Agency dashboard.
- Four presence cards (Creators, Learners, Reviewers, Agency) render with an active/total "Online now" value.
- Each card uses the correct icon: PenTool, GraduationCap, ClipboardCheck, ShieldCheck.
- With a second browser session signed in as a creator, the Creators card count increments.
- A user is counted online while heartbeating and drops offline after the 2-minute threshold.
- Signing out sets last_seen_at to NULL and the user is no longer counted online.
- The owner appears in the Agency presence card (owners are not agency_members rows and are injected synthetically).
- Learner sign-in activity appears in the activity log even though learners are not agency_members.
- Reload the page several times and confirm only one LOGGED IN entry exists for the session.
- Sign out then sign in again and confirm a new LOGGED IN entry is written.
- Wait past the 30-minute session timeout with an active session and confirm a new LOGIN row is written.
- The activity log renders Time, User, Role, Action, and Device / OS columns.
- LOGIN renders a green LOGGED IN badge; LOGOUT renders a red LOGGED OUT badge.
- Timestamps render in hh:mm:ss AM/PM format, and midnight renders as 12:xx:xx AM.
- Desktop user agent shows a Monitor icon; mobile shows a Smartphone icon.
- Verify the Operating System string is correct for Windows, macOS, Android, iOS, and Linux.
- Paginate forward and back through a log with more than 10 entries; Previous is disabled on page 1 and Next on the last page.
- With 10 or fewer entries, pagination controls are hidden entirely.
- Sign in as an Agency Admin member and confirm the presence cards and activity log are NOT rendered.
- Sign in as a Creator and confirm presence cards and activity log are NOT rendered.
- The POST /api/user/heartbeat request fires on load and then every 60 seconds.
- The logout heartbeat sends logout: true and returns User offline.
Edge Cases
Unauthorized account attempts to access Team or Learners.
Agency does not exist.
Agency has no members.
Agency has no learners.
Learner has no enrolled quests.
Quest progress is missing or null.
Agency name includes spaces or special characters.
Browser blocks or fails file download.
CSV generation fails.
Agency has no presence logs yet (table shows the No activity recorded yet. empty state).
Activity log user is no longer an agency member (row shows Unknown User, table does not break).
Activity log row has a null device_info (Device column shows Unknown).
A user closes the browser tab without signing out (they remain Online until the 2-minute threshold expires).
Heartbeat network request fails (no user-facing error, presence counts go stale).
iPad user agent (resolves to macOS (Desktop), a known misclassification).
Activity log has more than 100 events (pagination cannot reach history beyond the newest 100).
A user belongs to multiple roles, or owns the agency and is also a member (OWNER takes precedence in role resolution).
10. OPEN QUESTIONS
Should CSV export move from client-side generation to a dedicated backend route such as
/api/agency/admin/export-csv?Should there be login history or last active date for agency users?
Should exports include date range filtering?
Should exports include only visible filtered data or the full agency dataset?
Should exports include learner email and enrollment status, or only progress data?
Should exports include learner email and enrollment status, or only progress data?
Should presence and activity data ever be extended to Agency Admin members, or remain Owner-only? This was implemented as Owner-only and has not been confirmed with the product owner.
How long should user_presence_logs be retained? The table currently grows indefinitely with no archival or pruning.
Should the activity log support date range filtering and a cursor, given the 100-row server cap?
Should online status be made exact, e.g. via WebSocket or SSE instead of a 60-second heartbeat?
Should the activity log be exportable to CSV, consistent with the rest of the module?
Is tracking learner sign-in behaviour acceptable from a privacy and staff-monitoring standpoint? Needs a product and legal decision before wide rollout.
Should the browser name be captured in device_info, or is OS plus form factor sufficient?
Should the unused recentActivity list and the unused userDataMap in the learners route be removed?
11. OUT OF SCOPE
Agency Admin login history for agency users.
Agency Admin login analytics for agency users (e.g. time logged, device logged, duration logged)
Deep analytics beyond dashboard summary and CSV export.
PDF export.
Scheduled reports.
CSV import.
Cross-agency export.
12. SUCCESS METRICS
Agency Admin can export team member data as CSV.
Agency Admin can export learner quest progress data as CSV.
Creator, reviewer, and learner information is visible in agency dashboards.
Exported CSV can be opened in spreadsheet tools without manual cleanup.
Unauthorized users cannot export agency data.
Agency Owner can see current online counts for Creators, Learners, Reviewers, and Agency without leaving the dashboard.
Agency Owner can find any recent sign-in or sign-out event, with user, role, time, and device, in one table.
Reloading a page does not pollute the activity log with duplicate LOGGED IN entries.
The activity log stays usable for agencies with high event volume, via pagination.
Non-Owner roles (Admin, Creator, Reviewer, Learner) cannot see presence or activity history.
18. DEPENDENCIES
This feature depends on
Clerk Authentication
Agency Creation
Agency Membership
Creator, Reviewer, and Learner details
Quest Enrollments
Supabase Database
PapaParse CSV generation
date-fns timestamp formatting
IdleTimeoutProvider (heartbeat driver, mounted globally via ClerkProviderWrapper)
Supabase migration 20260717_add_presence_tracking.sql (creates user_presence_logs, adds app_users.last_seen_at and app_users.metadata)
This feature depends on this
Agency Dashboard
Agency Team Management
Agency Learners Dashboard
Creator and Reviewer Analytics
Learner Quest Progress
19. TIMELINE & OWNERSHIP
Backend: James Derick Billate
Frontend: James Derick Billate
QA: Internal development testing
Estimated Completion: Feature completed.
Blocking migration and environment prerequisites for the presence feature:
- supabase/migrations/20260717_add_presence_tracking.sql must be applied before the heartbeat route is deployed.
- app_users.clerk_id must be unique, since user_presence_logs references it.
- An ON UPDATE CASCADE follow-up migration is required before any Clerk-to-Supabase backfill that updates clerk_id.
- The migration is not idempotent (no DROP POLICY IF EXISTS), so it cannot be safely re-run as-is.
Note: API Endpoints and Schema please refer to its Technical Documentation