Live Data Polling Setup

This guide explains how to set up automated polling of live traffic data APIs for the production Vanroute app.

Overview

The Vanroute database needs to poll live traffic APIs every 15 minutes to keep incident data current. This ensures your mobile app always has up-to-date information about:

• Traffic accidents
• Road closures
• Floods and bushfires
• Roadworks
• Major events

Currently implemented: NSW Live Traffic Hazards (429+ active incidents)

Polling Architecture

┌─────────────────────────────────────────────────────────┐
│                                                         │
│  NSW Transport API    →    Polling Script    →    Supabase   │
│  (Every 15 min)            poll-traffic.ts         Database  │
│                                                         │
│  53 accidents                                           │
│  1 fire                    • Fetches data              │
│  335 roadworks             • Deduplicates              │
│  41 events                 • Upserts records           │
│                            • Marks resolved            │
└─────────────────────────────────────────────────────────┘
                                    │
                                    ↓
                            ┌──────────────┐
                            │  Mobile App  │
                            │  (Real-time) │
                            └──────────────┘

Setup Options

Choose one based on your deployment environment:

Option 1: GitHub Actions (Recommended - Free & Serverless)

Best for: Most projects, zero infrastructure costs

Setup Steps

1. Add secrets to GitHub repository

   Go to: Settings > Secrets and variables > Actions > New repository secret

   Add these secrets:

   SUPABASE_URL=https://your-project.supabase.co
   SUPABASE_SERVICE_KEY=your-service-role-key
   NSW_TRAFFIC_API_KEY=your-nsw-api-key

2. Enable GitHub Actions

   The workflow file already exists at .github/workflows/poll-traffic.yml

   GitHub Actions will automatically:

   • Run every 15 minutes
   • Poll all NSW traffic endpoints
   • Update your Supabase database
   • Log results (viewable in Actions tab)

3. Verify it's working

   • Go to Actions tab in GitHub
   • Look for "Poll Live Traffic Data" workflow
   • Check recent runs for success/failure

4. Manual trigger (optional)

   You can manually trigger a poll from the Actions tab using the "Run workflow" button

Workflow Configuration

The workflow is defined in .github/workflows/poll-traffic.yml:

name: Poll Live Traffic Data

on:
  schedule:
    - cron: '*/15 * * * *'  # Every 15 minutes
  workflow_dispatch:         # Allow manual trigger

jobs:
  poll-traffic:
    runs-on: ubuntu-latest
    steps:
      - Checkout code
      - Setup Node.js
      - Install dependencies
      - Poll NSW Live Traffic

Pros:

• ✅ Free (2,000 minutes/month on free plan)
• ✅ No infrastructure to maintain
• ✅ Easy monitoring via GitHub UI
• ✅ Automatic retries on failure

Cons:

• ⚠️ Requires code to be on GitHub
• ⚠️ Minimum interval is 5 minutes (we use 15)

---

Option 2: Vercel Cron Jobs

Best for: Projects already deployed to Vercel

Setup Steps

1. Deploy to Vercel

   npm install -g vercel
   vercel login
   vercel deploy

2. Add environment variables

   In Vercel dashboard:

   • Go to Project Settings > Environment Variables
   • Add:

     SUPABASE_URL
     SUPABASE_SERVICE_KEY
     NSW_TRAFFIC_API_KEY
     CRON_SECRET (generate a random string)

3. Configure cron job

   The vercel.json file is already set up:

   {
     "crons": [{
       "path": "/api/poll-traffic",
       "schedule": "*/15 * * * *"
     }]
   }

4. Deploy and verify

   vercel deploy --prod

   Check logs in Vercel dashboard under Functions > poll-traffic

Pros:

• ✅ Integrated with your deployment
• ✅ Good monitoring/logging
• ✅ Easy to scale

Cons:

• 💰 Cron jobs require Pro plan ($20/month)
• ⚠️ Vercel-specific solution

---

Option 3: Traditional Cron Job (Linux/macOS Server)

Best for: Self-hosted deployments, existing server infrastructure

Setup Steps

1. Run the setup script

   cd packages/database/scripts
   chmod +x setup-cron.sh
   ./setup-cron.sh

   This will:

   • Create a cron job to run every 15 minutes
   • Set up logging to /tmp/vanroute-traffic-poll.log

2. Verify cron job

   crontab -l

   You should see:

   */15 * * * * /tmp/vanroute-poll-traffic.sh

3. Monitor logs

   tail -f /tmp/vanroute-traffic-poll.log

4. Manual run

   cd packages/database
   npm run db:poll-traffic

Pros:

• ✅ Full control
• ✅ No external dependencies
• ✅ Can run more frequently if needed

Cons:

• ⚠️ Requires maintaining a server
• ⚠️ Need to handle failures manually
• ⚠️ Server must stay online 24/7

---

Option 4: Supabase Edge Functions (Coming Soon)

Best for: Fully Supabase-native solution

Supabase is adding cron support for Edge Functions. When available, this will be the most integrated option.

---

Mobile App Integration

Once polling is set up, your React Native app can access live traffic data through Supabase.

Example: Get Incidents Near User

import { getIncidentsNearLocation } from '@/services/trafficService';

// Get incidents within 50km of user's location
const incidents = await getIncidentsNearLocation(
  userLat,
  userLon,
  50 // radius in km
);

// Filter critical incidents
const criticalIncidents = incidents.filter(
  inc => inc.severity === 'critical'
);

Example: Real-time Updates

import { subscribeToIncidents } from '@/services/trafficService';

// Subscribe to live updates
const unsubscribe = subscribeToIncidents((incidents) => {
  console.log(`Got ${incidents.length} active incidents`);
  setIncidents(incidents);
});

// Cleanup on unmount
return () => unsubscribe();

Example: Route Planning

import { getIncidentsAlongRoute } from '@/services/trafficService';

// Check for incidents along planned route
const routePoints: [number, number][] = [
  [-33.8688, 151.2093], // Sydney
  [-33.9173, 151.2313], // Checkpoint 1
  [-34.0285, 151.1573], // Checkpoint 2
  // ... more points
];

const routeIncidents = await getIncidentsAlongRoute(
  routePoints,
  5 // buffer 5km on each side
);

if (routeIncidents.length > 0) {
  console.warn('Route has active incidents:', routeIncidents);
  // Show warning to user
  // Suggest alternative route
}

See the complete API reference in apps/mobile/services/trafficService.ts

---

Database Schema

The live_traffic_incidents table structure:

CREATE TABLE live_traffic_incidents (
  id UUID PRIMARY KEY,
  geometry geometry(Point, 4326),
  incident_type TEXT NOT NULL,
  severity TEXT NOT NULL,
  title TEXT NOT NULL,
  description TEXT,
  location_description TEXT,
  state TEXT,
  road_name TEXT,
  start_time TIMESTAMPTZ NOT NULL,
  end_time TIMESTAMPTZ,
  is_active BOOLEAN DEFAULT true,
  affects_caravans BOOLEAN DEFAULT true,
  advice TEXT,
  data_source TEXT NOT NULL,
  source_id TEXT NOT NULL,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  resolved_at TIMESTAMPTZ,
  UNIQUE(data_source, source_id)
);

Key fields:

• is_active - Automatically updated by polling script
• resolved_at - Set when incident becomes inactive
• affects_caravans - Filter for caravan-relevant incidents
• severity - low, medium, high, critical

---

Monitoring & Maintenance

Check Current Data

cd packages/database
npm run db:summary

Look for the "Live Traffic Incidents" section.

Manual Poll

npm run db:poll-traffic

View Active Incidents

SELECT
  incident_type,
  severity,
  title,
  location_description,
  created_at
FROM live_traffic_incidents
WHERE is_active = true
ORDER BY severity DESC, created_at DESC;

Incident Statistics

SELECT
  incident_type,
  COUNT(*) as count,
  MAX(created_at) as latest
FROM live_traffic_incidents
WHERE is_active = true
GROUP BY incident_type
ORDER BY count DESC;

---

Troubleshooting

No incidents appearing

1. Check API key is valid

   grep NSW_TRAFFIC_API_KEY packages/database/.env

2. Test API manually

   cd packages/database
   npx tsx scripts/test-nsw-api.ts

3. Check Supabase connection

   npm run db:summary

GitHub Actions not running

1. Check workflow is enabled

   • Go to Actions tab
   • Look for disabled workflows

2. Verify secrets are set

   • Settings > Secrets and variables > Actions

3. Check workflow logs

   • Actions tab > Recent workflow runs > Click run > View logs

Incidents not resolving

The polling script automatically marks incidents as inactive when they no longer appear in the API feed. If this isn't working:

1. Check the markResolvedIncidents function is running
2. Verify database triggers are working
3. Check for duplicate source_id values

---

Data Sources

Currently Implemented

Source	Endpoint	Coverage	Update Frequency
NSW Live Traffic	/incident/open	NSW-wide accidents	Real-time
NSW Live Traffic	/flood/open	NSW-wide floods	Real-time
NSW Live Traffic	/fire/open	NSW-wide fires	Real-time
NSW Live Traffic	/roadwork/open	NSW-wide roadworks	Real-time
NSW Live Traffic	/majorevent/open	NSW-wide events	Real-time

Future Data Sources

• VIC Traffic: https://opendata.transport.vic.gov.au/
• QLD Traffic: https://www.data.qld.gov.au/
• National Freight Data Hub: Aggregated Australia-wide
• BOM Weather Warnings: Bureau of Meteorology severe weather

---

Cost Analysis

GitHub Actions (Recommended)

• Cost: Free (2,000 minutes/month)
• Usage: ~2 minutes per run = ~4,320 minutes/month (need paid plan for high-frequency polling)
• Upgrade: $4/month for Pro (50GB storage, 3,000 minutes)

Vercel Cron

• Cost: $20/month (Pro plan required for cron)
• Includes: Unlimited cron jobs, better monitoring

Self-Hosted Server

• Cost: Variable (AWS t3.micro ~$10/month, or existing infrastructure)
• Advantages: Full control, no rate limits

Recommendation: Start with GitHub Actions. It's free for moderate usage and easy to set up.

---

Production Checklist

Before going live, ensure:

• Polling is set up (GitHub Actions, Vercel, or cron)
• All environment variables are configured
• API keys are stored securely as secrets
• Test polling runs successfully
• Mobile app can query incidents
• Real-time subscriptions work
• Monitoring/logging is in place
• Backup polling method configured (in case primary fails)

---

Performance Optimization

Database Indexes

Already created for optimal query performance:

-- Active incidents
CREATE INDEX idx_live_traffic_active
  ON live_traffic_incidents (is_active, start_time)
  WHERE is_active = true;

-- Spatial queries
CREATE INDEX idx_live_traffic_geometry
  ON live_traffic_incidents USING GIST (geometry);

-- Type filtering
CREATE INDEX idx_live_traffic_type
  ON live_traffic_incidents (incident_type, severity);

Mobile App Caching

Implement caching in your mobile app to reduce database queries:

// Cache incidents for 5 minutes
const CACHE_DURATION = 5 * 60 * 1000;
let cachedIncidents: TrafficIncident[] = [];
let lastFetch: number = 0;

export async function getActiveIncidents(): Promise<TrafficIncident[]> {
  const now = Date.now();

  if (now - lastFetch < CACHE_DURATION && cachedIncidents.length > 0) {
    return cachedIncidents;
  }

  const incidents = await fetchFromSupabase();
  cachedIncidents = incidents;
  lastFetch = now;

  return incidents;
}

---

Security Best Practices

1. Never commit API keys

   • Use environment variables
   • Add .env to .gitignore

2. Use Supabase Row Level Security (RLS)

   -- Allow public read access to active incidents
   CREATE POLICY "Active incidents are publicly readable"
     ON live_traffic_incidents FOR SELECT
     USING (is_active = true);

3. Secure cron endpoints

   • Use CRON_SECRET for Vercel
   • Restrict GitHub Actions to your repository

4. Rate limiting

   • NSW API: 5 requests/second, 60,000/day
   • Current usage: ~5 requests every 15 min = ~480/day ✅

---

Support

For issues or questions:

• Database Package - GitHub repository
• Phase 1 Implementation Guide
• GitHub Issues

---

Last Updated: October 2025 Status: ✅ Production Ready Maintainer: Database Team