Last1.app + RadioCheck Supabase Integration Guide¶

Overview¶

This guide explains how to integrate Last1.app with the shared Supabase database to enable org admins to manage check-ins across both Last1.app and RadioCheck platforms.

1. Environment Setup¶

Connect to the same Supabase instance as RadioCheck. Add these environment variables:

SUPABASE_URL=<your-supabase-url>
SUPABASE_SERVICE_ROLE_KEY=<service-role-key>
SUPABASE_ANON_KEY=<anon-key>

Use the service role key for server-side API calls since the RLS policies grant full access to service role for all last1_* tables.

2. Database Migration¶

Run the migration file 001_last1_integration.sql in your Supabase SQL Editor. This creates:

New Tables (prefixed with `last1_`):¶

last1_organizations - Organizations from Last1.app
last1_organization_members - Members with roles (owner, admin, member, provider)
last1_programs - Outcome tracking programs
last1_outcomes - Program outcomes to measure
last1_beneficiaries - Participants/clients in programs
last1_program_beneficiaries - Enrollment tracking
last1_check_ins - Outcome-based check-ins
last1_radio_check_campaigns - Automated check-in campaigns
last1_radio_check_instances - Individual check-in sends
last1_escalation_events - Emergency/support escalations
last1_campaign_participants - Campaign enrollments

Bridge Tables:¶

user_identity_bridge - Maps Last1 TEXT user IDs to RadioCheck UUID user IDs
org_bridge - Links Last1 organizations to RadioCheck organizations

3. Core API Endpoints to Build¶

A. User Identity Bridging¶

Endpoint: POST /api/link-radiocheck

When a Last1.app user wants to connect their RadioCheck account:

const { data } = await supabase.rpc('link_user_identities', {
  last1_user_id_param: clerkUserId,  // Your Clerk/auth user ID
  radiocheck_user_id_param: radiocheckUserId  // UUID from RadioCheck
});

B. Organization Bridging¶

Endpoint: POST /api/link-organization

const { data } = await supabase.rpc('link_organizations', {
  last1_org_id_param: last1OrgId,
  radiocheck_org_id_param: radiocheckOrgId,
  enable_sync: true  // Enable bidirectional check-in sync
});

C. Trigger Campaign Check-ins¶

Endpoint: POST /api/campaigns/:id/trigger

const { data } = await supabase.rpc('trigger_last1_campaign_checkins', {
  campaign_id_param: campaignId
});
// Returns: { total_sent, radiocheck_created }

4. Admin Dashboard Pages¶

A. `/admin/dashboard` - Main Overview¶

Call get_last1_escalation_summary():

const { data: summary } = await supabase.rpc('get_last1_escalation_summary', {
  last1_org_id_param: orgId
});
// Returns: { pending_count, support_needed_count, emergency_count, resolved_today_count }

UI Components: - Stat cards for each count - Color-coded: green (pending), yellow (support needed), red (emergency) - Quick action buttons to view each category

B. `/admin/check-ins` - Combined Check-ins View¶

Call get_last1_admin_checkins_dashboard():

const { data: checkIns } = await supabase.rpc('get_last1_admin_checkins_dashboard', {
  last1_org_id_param: orgId
});
// Returns both Last1 and RadioCheck check-ins:
// { check_in_id, source, user_alias, user_name, status, response, initiated_at, responded_at, response_message }

UI Components: - Table with sortable columns - Filter by source (Last1 / RadioCheck / All) - Filter by status (pending / responded / escalated) - Filter by response type (all good / need support / emergency) - Search by user name/alias - Date range picker

C. `/admin/campaigns` - Campaign Management¶

CRUD operations on last1_radio_check_campaigns:

// List campaigns
const { data } = await supabase
  .from('last1_radio_check_campaigns')
  .select('*')
  .eq('organization_id', orgId);

// Create campaign
const { data } = await supabase
  .from('last1_radio_check_campaigns')
  .insert({
    organization_id: orgId,
    name: 'Daily Wellness Check',
    cadence: 'daily',
    message_template: 'Radio Check\nJust checking in.\nReply:\n1 = All good\n2 = Need support\n3 = Emergency',
    escalation_rules: {
      response_2: 'notify_staff',
      response_3: 'immediate_alert',
      no_response_hours: 24
    }
  });

UI Components: - Campaign list with active/inactive toggle - Campaign editor with: - Name field - Cadence selector (daily, weekly, custom) - Message template textarea - Escalation rules configuration - RadioCheck unit link (optional dropdown) - Participant management (add/remove beneficiaries)

D. `/admin/beneficiaries` - Participant Management¶

CRUD on last1_beneficiaries:

// List beneficiaries
const { data } = await supabase
  .from('last1_beneficiaries')
  .select(`
    *,
    radiocheck_user:users(call_sign, display_name)
  `)
  .eq('organization_id', orgId);

// Create beneficiary
const { data } = await supabase
  .from('last1_beneficiaries')
  .insert({
    organization_id: orgId,
    alias: 'JD-2024',
    first_name: 'John',
    last_name: 'Doe',
    phone_number: '+1234567890',
    preferred_channel: 'sms',
    risk_level: 'medium'
  });

UI Components: - Beneficiary table with search - Add/edit beneficiary form - Link to RadioCheck user button - Risk level badges (low/medium/high) - Campaign enrollment management - Contact info display

E. `/admin/escalations` - Escalation Queue¶

Query unresolved escalations:

const { data } = await supabase
  .from('last1_escalation_events')
  .select(`
    *,
    check_instance:last1_radio_check_instances(
      *,
      beneficiary:last1_beneficiaries(*),
      campaign:last1_radio_check_campaigns(*)
    )
  `)
  .is('resolved_at', null)
  .order('escalated_at', { ascending: false });

// Resolve an escalation
const { data } = await supabase
  .from('last1_escalation_events')
  .update({
    resolved_at: new Date().toISOString(),
    resolution_notes: 'Called participant, situation resolved',
    outcome: 'resolved_no_action'
  })
  .eq('id', escalationId);

UI Components: - Escalation cards with priority coloring - Emergency (red) vs Support Needed (yellow) - Beneficiary info panel - Resolution form with: - Resolution notes textarea - Outcome dropdown (resolved, referred, no contact, etc.) - Resolve button - Timeline of escalation history

5. Reports to Build¶

A. `/admin/reports/response-rates` - Response Rate Report¶

SELECT 
  DATE(sent_at) as date,
  COUNT(*) as total_sent,
  COUNT(CASE WHEN status = 'responded' THEN 1 END) as responded,
  ROUND(COUNT(CASE WHEN status = 'responded' THEN 1 END)::numeric / COUNT(*)::numeric * 100, 1) as response_rate
FROM last1_radio_check_instances lri
JOIN last1_radio_check_campaigns lrc ON lri.campaign_id = lrc.id
WHERE lrc.organization_id = $1
  AND sent_at >= NOW() - INTERVAL '30 days'
GROUP BY DATE(sent_at)
ORDER BY date DESC;

UI: Line chart showing response rate over time

B. `/admin/reports/risk-distribution` - Risk Level Distribution¶

SELECT 
  risk_level,
  COUNT(*) as count
FROM last1_beneficiaries
WHERE organization_id = $1 AND status = 'active'
GROUP BY risk_level;

UI: Pie chart or bar chart

C. `/admin/reports/escalations` - Escalation Trends¶

SELECT 
  DATE(escalated_at) as date,
  trigger_reason,
  COUNT(*) as count
FROM last1_escalation_events le
JOIN last1_radio_check_instances lri ON le.check_instance_id = lri.id
JOIN last1_radio_check_campaigns lrc ON lri.campaign_id = lrc.id
WHERE lrc.organization_id = $1
  AND escalated_at >= NOW() - INTERVAL '30 days'
GROUP BY DATE(escalated_at), trigger_reason
ORDER BY date DESC;

UI: Stacked bar chart showing emergency vs support escalations

D. `/admin/reports/cross-platform` - RadioCheck Activity¶

SELECT 
  u.call_sign,
  u.display_name,
  u.xp,
  u.rank,
  u.current_streak,
  COUNT(ci.id) as radiocheck_checkins,
  MAX(ci.initiated_at) as last_radiocheck_activity
FROM users u
JOIN organization_members om ON u.id = om.user_id
JOIN org_bridge ob ON om.organization_id = ob.radiocheck_org_id
LEFT JOIN check_ins ci ON ci.target_user_id = u.id
WHERE ob.last1_org_id = $1
GROUP BY u.id, u.call_sign, u.display_name, u.xp, u.rank, u.current_streak
ORDER BY last_radiocheck_activity DESC;

UI: Table showing RadioCheck users linked to your org with their stats

6. Real-time Subscriptions¶

For live dashboard updates:

// Subscribe to new escalations
supabase
  .channel('escalations')
  .on('postgres_changes', {
    event: 'INSERT',
    schema: 'public',
    table: 'last1_escalation_events'
  }, (payload) => {
    // Show toast notification
    // Update escalation count
    // Play alert sound for emergencies
  })
  .subscribe();

// Subscribe to check-in responses
supabase
  .channel('responses')
  .on('postgres_changes', {
    event: 'UPDATE',
    schema: 'public',
    table: 'last1_radio_check_instances'
  }, (payload) => {
    if (payload.new.status === 'responded') {
      // Update response stats
      // Refresh check-ins table
    }
  })
  .subscribe();

7. Auth/Permissions Pattern¶

Check if logged-in user is an org admin:

async function isOrgAdmin(clerkUserId: string, orgId: string): Promise<boolean> {
  const { data } = await supabase
    .from('last1_organization_members')
    .select('role')
    .eq('user_id', clerkUserId)
    .eq('organization_id', orgId)
    .single();

  return data?.role === 'owner' || data?.role === 'admin';
}

// Middleware example
async function requireOrgAdmin(req, res, next) {
  const { userId } = getAuth(req); // Clerk auth
  const { orgId } = req.params;

  if (!await isOrgAdmin(userId, orgId)) {
    return res.status(403).json({ error: 'Admin access required' });
  }
  next();
}

8. How the Integration Works¶

Last1 org admin creates campaigns and adds beneficiaries
When a campaign triggers (manually or scheduled), check-ins are created in last1_radio_check_instances
If a beneficiary is linked to a RadioCheck user, a corresponding check_ins record is automatically created in RadioCheck
When the RadioCheck user responds via the mobile app, a trigger syncs the response back to last1_radio_check_instances
Escalation events are automatically created for "need support" (response=2) or "emergency" (response=3)
Last1 org admins can view all check-ins from both platforms in the unified dashboard

9. Summary of Features¶

Feature	Description
Unified Dashboard	View check-ins from both Last1 and RadioCheck
Campaign Management	Create, edit, and trigger check-in campaigns
Beneficiary Management	Add participants and link to RadioCheck users
Escalation Queue	Real-time alerts for emergency/support responses
Response Rate Reports	Track engagement over time
Risk Distribution	Monitor participant risk levels
Cross-Platform Stats	See RadioCheck user activity and gamification stats
Real-time Updates	Live subscriptions for instant notifications
Identity Bridging	Connect Last1 users to RadioCheck accounts
Organization Bridging	Link Last1 orgs to RadioCheck orgs for sync

10. Database Functions Reference¶

Function	Purpose
`link_user_identities(last1_user_id, radiocheck_user_id)`	Connect a Last1 user to a RadioCheck user
`link_organizations(last1_org_id, radiocheck_org_id, enable_sync)`	Bridge two organizations
`trigger_last1_campaign_checkins(campaign_id)`	Send check-ins to all campaign participants
`get_last1_admin_checkins_dashboard(org_id)`	Get combined check-ins from both platforms
`get_last1_escalation_summary(org_id)`	Get escalation counts for dashboard
`create_checkin_from_last1(instance_id, user_id, unit_id)`	Create RadioCheck check-in from Last1
`sync_checkin_response_to_last1()`	Trigger that syncs responses back
`has_last1_admin_access(radiocheck_user_id)`	Check if user has Last1 admin access