Skip to main content

Dynamic Trending Causes by Geography

This guide explains how trending causes are computed and displayed based on the selected geography.

Overview​

The Open Navigator homepage displays trending causes - policy areas that have received the most attention in recent government meetings. These causes are dynamically computed based on:

  • Geography: National, State, County, or City level
  • Time window: Last 90 days of decisions
  • Data source: AI-analyzed meeting transcripts from bronze_decisions table

How It Works​

Data Flow​

bronze_decisions (meetings)
    ↓ (dbt staging)
stg_bronze_decisions (cleaned, filtered to last 90 days)
    ↓ (dbt intermediate)
int_trending_causes_by_jurisdiction (aggregated by cause & jurisdiction)
    ↓ (dbt marts)
jurisdiction_state_aggregate.trending_causes (JSONB column)
    ↓ (API)
GET /api/stats?state=AL&city=Mobile
    ↓ (Frontend)
Home.tsx displays location-specific trending causes

Example User Flow​

  1. User lands on homepage → Shows national trending causes
  2. User searches for "Mobile, AL" → Shows Mobile's trending causes (last 90 days)
  3. User searches for "Alabama" → Shows Alabama's trending causes (aggregated from all AL cities)
  4. No data? → Falls back to global trending causes from /api/trending

Technical Implementation​

Database Schema​

The jurisdiction_state_aggregate table contains pre-computed statistics at multiple levels:

CREATE TABLE jurisdiction_state_aggregate (
    level VARCHAR(20),              -- 'national', 'state', 'county', 'city'
    state_code VARCHAR(2),
    county VARCHAR(100),
    city VARCHAR(100),
    jurisdictions_count INTEGER,
    nonprofits_count INTEGER,
    events_count INTEGER,
    contacts_count INTEGER,
    trending_causes JSONB,          -- ← Dynamic trending causes
    last_updated TIMESTAMP
);

The structure varies by aggregation level:

City Level (most detailed):

[
  {
    "cause": "Education and Workforce",
    "code": "COFOG-09",
    "decision_count": 5,
    "topics": 3,
    "most_recent": "2024-05-22",
    "rank": 1,
    "sample_headlines": [
      "MPS highlights literacy strategies",
      "Board approves new curriculum"
    ]
  }
]

State Level (aggregated):

[
  {
    "cause": "Education and Workforce",
    "decision_count": 127,
    "jurisdictions": 15
  }
]

National Level:

[
  {
    "cause": "Education and Workforce",
    "decision_count": 1543,
    "states": 42
  }
]

Frontend Component​

The Home.tsx component fetches trending causes from the stats endpoint:

// Fetch location stats (includes trending_causes)
const { data: locationStats } = useQuery({
  queryKey: ['location-stats', location],
  queryFn: async () => {
    const response = await api.get('/stats', { 
      params: { state: 'AL', city: 'Mobile' }
    });
    return response.data;
  }
});

// Use location-specific causes if available
const trendingTopics = React.useMemo(() => {
  if (locationStats?.trending_causes) {
    // Transform database format to UI format
    return locationStats.trending_causes.map(cause => ({
      name: cause.cause,
      icon: getCauseIcon(cause.cause),
      description: `${cause.decision_count} recent decisions`
    }));
  }
  
  // Fallback to global trending
  return trendingData?.causes || [];
}, [locationStats, trendingData]);

Set up a daily cron job to refresh trending causes:

# Add to crontab
0 2 * * * cd /path/to/open-navigator && ./scripts/data/update_trending_causes.sh

Manual Updates​

Run the dbt models to recompute trending causes:

# Quick update
./scripts/data/update_trending_causes.sh

# Or step-by-step
cd dbt_project
dbt run --select stg_bronze_decisions
dbt run --select int_trending_causes_by_jurisdiction
dbt run --select jurisdiction_state_aggregate

Verification​

Check that trending causes are populated:

-- Count jurisdictions with trending causes
SELECT 
  level,
  COUNT(*) as total,
  COUNT(CASE WHEN trending_causes IS NOT NULL THEN 1 END) as with_causes
FROM jurisdiction_state_aggregate
GROUP BY level;

-- View sample trending causes
SELECT 
  city,
  state_code,
  jsonb_pretty(trending_causes) as causes
FROM jurisdiction_state_aggregate
WHERE level = 'city' 
  AND trending_causes IS NOT NULL
LIMIT 5;

Cause Categories​

Trending causes are mapped to COFOG (Classification of Functions of Government) categories:

CodeCategoryIconExample Topics
COFOG-01General Public Services🏛️Council procedures, budgets
COFOG-04Economic Affairs💼Business incentives, development
COFOG-05Environmental Protection🌍Parks, recycling, climate
COFOG-06Housing and Community Amenities🏠Zoning, affordable housing
COFOG-07Health🏥Public health, hospitals
COFOG-08Recreation, Culture, Religion🎨Libraries, museums, sports
COFOG-09Education and Workforce📚Schools, training programs
COFOG-10Social Protection🤝Social services, elderly care

Performance Considerations​

Why Pre-compute in dbt?​

Instead of computing trending causes on-demand in the API, we use dbt to:

  • ✅ Speed: Query takes ~10ms vs 3-5 seconds for on-the-fly aggregation
  • ✅ Consistency: All users see the same data (updated daily)
  • ✅ Scalability: No expensive computations at request time
  • ✅ Testing: dbt tests ensure data quality

Cache Strategy​

The API caches stats for 5 minutes:

# In api/routes/stats_neon.py
CACHE_DURATION = timedelta(minutes=5)

The frontend also caches for 5 minutes:

staleTime: 5 * 60 * 1000  // 5 minutes

Troubleshooting​

Check if data exists:

SELECT COUNT(*) 
FROM jurisdiction_state_aggregate 
WHERE trending_causes IS NOT NULL;

If count is 0, run the dbt models:

./scripts/data/update_trending_causes.sh

Causes not updating?​

Clear the cache:

# Restart API to clear server-side cache
pkill -f "python main.py serve"
python main.py serve

# Frontend cache clears automatically after 5 minutes

Wrong causes displayed?​

Verify the bronze_decisions table has recent data:

SELECT 
  COUNT(*) as total,
  MIN(decision_date) as oldest,
  MAX(decision_date) as newest
FROM bronze_decisions
WHERE decision_date >= CURRENT_DATE - INTERVAL '90 days';

If no recent decisions exist, run the data ingestion pipeline:

# Load meeting transcripts
python scripts/datasources/gemini/load_meeting_transcripts_bronze.py

# Then update trending causes
./scripts/data/update_trending_causes.sh

Future Enhancements​

Potential improvements to trending causes:

  1. Real-time updates: Use CDC (Change Data Capture) instead of daily batch
  2. Personalization: Show causes relevant to user's interests
  3. Trend arrows: Show if a cause is rising or falling
  4. Time comparison: "Education up 23% vs last month"
  5. Geographic clustering: Show regional trends (e.g., "Southern states")