Skip to main content

Database Setup & Stats Verification

Quick Setup​

1. Install Dependencies​

# Create virtual environment
python -m venv .venv
source .venv/bin/activate  # On Windows: .venv\Scripts\activate

# Install dependencies
pip install -r requirements.txt

2. Configure Environment​

# Copy environment template
cp .env.example .env

# Edit .env and add your Neon database URL
# NEON_DATABASE_URL=postgresql://user:password@host/database

3. Run End-to-End Setup​

# Automated setup script
./scripts/setup-database.sh

This script will:

  • ✅ Verify environment is configured
  • 📤 Sync data from data/gold/ to Neon database
  • 🔍 Verify all required tables exist
  • 📊 Display current stats (nonprofits, events, contacts, jurisdictions)
  • 🌐 Test API endpoint if server is running

Manual Setup Steps​

If you prefer manual control:

Sync Data to Neon​

# Smart sync - detects schema changes and syncs efficiently
./scripts/data/sync-smart.sh

# Or full sync (slower but comprehensive)
./scripts/data/sync_to_neon_smart.py --force

Verify Database​

# Check stats via Python
python -c "
import psycopg2
conn = psycopg2.connect('your_neon_database_url')
cur = conn.cursor()

# Check table counts
cur.execute('SELECT COUNT(*) FROM nonprofits_organizations')
print(f'Nonprofits: {cur.fetchone()[0]:,}')

cur.execute('SELECT COUNT(*) FROM events_meetings')
print(f'Events: {cur.fetchone()[0]:,}')

cur.execute('SELECT COUNT(*) FROM contacts_local_officials')
print(f'Contacts: {cur.fetchone()[0]:,}')
"

Test API​

# Start the API server
cd /home/developer/projects/open-navigator
NEON_DATABASE_URL_DEV="postgresql://user:password@host/db" \
  .venv/bin/python -m uvicorn api.main:app --host 0.0.0.0 --port 8000 --reload

# In another terminal, test stats endpoint
curl "http://localhost:8000/api/stats?state=MA" | python3 -m json.tool

Common Issues​

Stats Show as undefined in UI​

Symptom: Console shows Stats data: undefined

Causes:

  1. Wrong response path: Frontend accessing res.data.data instead of res.data
  2. Database not synced: Tables are empty or don't exist
  3. API error: Check backend logs for database connection errors

Fix:

# 1. Check if data is in Neon
./scripts/setup-database.sh

# 2. Check API logs
tail -f logs/api.log

# 3. Test API directly
curl "http://localhost:8000/api/stats?state=MA"

Database Connection Errors​

Error: Database error: could not connect to server

Fix:

# Verify NEON_DATABASE_URL in .env
cat .env | grep NEON_DATABASE_URL

# Test connection
.venv/bin/python -c "
import psycopg2
import os
from dotenv import load_dotenv
load_dotenv()
conn = psycopg2.connect(os.getenv('NEON_DATABASE_URL'))
print('✅ Connection successful')
"

Empty Stats​

Error: All stats show 0

Fix:

# Sync data from parquet files to database
./scripts/data/sync-smart.sh

# Or use full sync
.venv/bin/python scripts/data/sync_local_to_neon_simple.py

Stats API Reference​

Endpoints​

National Stats​

GET /api/stats

Response:

{
  "location": "United States",
  "level": "national",
  "jurisdictions": 32000,
  "nonprofits": 43726,
  "events": 50000,
  "contacts": 10000,
  "total_revenue": 1500000000,
  "total_assets": 3000000000
}

State Stats​

GET /api/stats?state=MA

County Stats​

GET /api/stats?state=MA&county=Suffolk%20County

City Stats​

GET /api/stats?state=MA&city=Boston

Error Responses​

Database Error​

{
  "detail": "Database error: connection timeout"
}

Status Code: 500

No Data Available​

{
  "location": "Test City, XX",
  "level": "city",
  "jurisdictions": 0,
  "nonprofits": 0,
  "note": "No data available for this location"
}

Status Code: 200 (with empty stats)

Frontend Integration​

Accessing Stats​

// Correct - stats are at res.data
const response = await api.get('/stats', { 
  params: { state: 'MA' } 
})
const stats = response.data  // ✅ Correct

// Wrong - don't use res.data.data
const stats = response.data.data  // ❌ Will be undefined

Error Handling​

const { data: stats, isLoading, error } = useQuery({
  queryKey: ['stats', state],
  queryFn: async () => {
    const response = await api.get('/stats', { 
      params: { state } 
    })
    return response.data
  },
  onError: (error: any) => {
    console.error('Stats error:', error.response?.data?.detail || error.message)
    // Show user-friendly error message
  }
})

// Check for errors in the data
if (stats?.error) {
  return <div>⚠️ Stats unavailable: {stats.error}</div>
}

Maintenance​

Update Data​

# Quick update - only changed data
./scripts/data/sync-smart.sh

# Full refresh - all data
./scripts/data/sync-smart.sh --force

Monitor Database Size​

-- Check database size
SELECT 
  pg_size_pretty(pg_database_size(current_database())) as size;

-- Check table sizes
SELECT 
  schemaname,
  tablename,
  pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) AS size
FROM pg_tables
WHERE schemaname = 'public'
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC;

Vacuum Database​

# Connect to Neon database
.venv/bin/python -c "
import psycopg2
import os
from dotenv import load_dotenv
load_dotenv()
conn = psycopg2.connect(os.getenv('NEON_DATABASE_URL'))
conn.autocommit = True
cur = conn.cursor()
cur.execute('VACUUM ANALYZE')
print('✅ Vacuum complete')
"

Monitoring​

Health Check​

# Check API health
curl http://localhost:8000/health

# Check stats availability
curl "http://localhost:8000/api/stats?state=MA" | jq '.nonprofits'

Logs​

# API logs
tail -f logs/api.log

# Frontend logs (browser console)
# Look for: 📊 [HomeModern] Stats data: ...

See Also​