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")