# Grafana Configuration Fixes - Summary

## What Was Wrong

You reported "data source errors" on the Grafana dashboards. Investigation revealed **three main issues**:

### Issue 1: PostgreSQL Datasource Not Properly Configured ❌
- Environment variables (`${POSTGRES_DB}`, `${POSTGRES_USER}`, `${POSTGRES_PASSWORD}`) weren't being expanded
- Database and user fields were empty strings
- Datasource showed as configured but couldn't execute queries

### Issue 2: Incorrect Datasource Type in Dashboard ❌
- Dashboard used `"type": "postgres"`
- Grafana's PostgreSQL plugin is actually `"type": "grafana-postgresql-datasource"`
- Caused datasource resolution failures

### Issue 3: SQL Schema Mismatch ❌
- Dashboard SQL queries used snake_case column names (`start_time`, `meeting_id`)
- Actual PostgreSQL schema uses camelCase (`startTime`, `meetingId`)
- PostgreSQL requires quoted identifiers for case-sensitive column names
- Some queries referenced `races.date` which doesn't exist (it's on `meetings` table)

## What Was Fixed

### ✅ Fix 1: PostgreSQL Datasource Configuration
**File**: `grafana/provisioning/datasources/postgres.yml`

Changed from:
```yaml
database: ${POSTGRES_DB}
user: ${POSTGRES_USER}
secureJsonData:
  password: ${POSTGRES_PASSWORD}
```

To:
```yaml
database: racing_db
user: racing
secureJsonData:
  password: racing123
```

### ✅ Fix 2: Dashboard Datasource References
**File**: `grafana/dashboards/scheduler-monitoring.json`

- Changed all `"type": "postgres"` → `"type": "grafana-postgresql-datasource"`
- Added UID references: `"uid": "PCC52D03280B7034C"`
- Ensures reliable datasource resolution

### ✅ Fix 3: SQL Query Corrections
**File**: `grafana/dashboards/scheduler-monitoring.json`

All SQL queries updated with:

1. **Quoted identifiers** for camelCase columns:
```sql
-- Before
SELECT startTime FROM races

-- After
SELECT "startTime" FROM races
```

2. **Correct date filtering**:
```sql
-- Before (WRONG - races has no date column)
WHERE date = CURRENT_DATE

-- After (CORRECT - cast timestamp to date)
WHERE "startTime"::date = CURRENT_DATE
```

3. **All column name corrections**:
- `startTime`, `updatedAt`, `createdAt` → `"startTime"`, `"updatedAt"`, `"createdAt"`
- `meetingId`, `raceNumber` → `"meetingId"`, `"raceNumber"`
- `startedAt`, `completedAt`, `durationMs` → `"startedAt"`, `"completedAt"`, `"durationMs"`
- `itemsProcessed`, `itemsFailed`, `errorMessage` → `"itemsProcessed"`, `"itemsFailed"`, `"errorMessage"`
- `jobType`, `retryCount` → `"jobType"`, `"retryCount"`

## Validation

All issues have been **verified as fixed**:

```bash
# Run this to validate
./scripts/validate-grafana-datasources.sh
```

**Results**:
- ✅ PostgreSQL datasource: Connected and healthy
- ✅ Prometheus datasource: Connected and healthy
- ✅ Both dashboards load without errors
- ✅ All SQL queries execute successfully

## Current State

🟢 **All Grafana configuration issues are resolved**

**What works now**:
- Datasources connect properly
- SQL queries execute without errors
- Dashboards load without datasource errors
- Column names match actual schema
- Date filtering uses correct columns

**What's still needed**:
- Generate fresh data to see metrics (database was cleared earlier)
- Run: `npx tsx scripts/test-scheduler-metrics.ts`

## Files Modified

1. ✅ `grafana/provisioning/datasources/postgres.yml` - Fixed credentials
2. ✅ `grafana/dashboards/scheduler-monitoring.json` - Fixed datasource refs and SQL queries
3. ✅ `docker-compose.yml` - Added postgres dependency to Grafana (already updated)

## New Files Created

1. ✅ `scripts/validate-grafana-datasources.sh` - Validation script
2. ✅ `GRAFANA_VALIDATION.md` - Detailed technical documentation
3. ✅ `GRAFANA_FIXES_SUMMARY.md` - This summary

## Access Your Dashboards

**URL**: http://localhost:3000
**Login**: admin / admin

**Direct Links**:
- Scheduler Monitoring: http://localhost:3000/d/scheduler-monitoring
- TAB API Metrics: http://localhost:3000/d/tab-api-metrics

## Next Steps

1. ✅ Grafana configuration is fixed and validated
2. 📊 Generate data: Run `npx tsx scripts/test-scheduler-metrics.ts`
3. 🔍 Verify panels populate with data
4. 🚀 Ready to proceed with PreRaceScheduler work

---

**Status**: ✅ All datasource errors resolved and validated