With this complete database schema, we have everything required to document the exact data flow, table relationships, and ingestion architecture.

Here is how the WeatherDash architecture operates, followed by the complete documentation (`data.source.md`) and the refined diagnostic script (`test.data.sources.sh`).

---

### Architecture & Data Flow Summary

1. **Ingestion Trigger (`crontab`)**: Every 5 minutes, cron executes `/opt/homebrew/bin/php .../weather.ingest.cli.php`.
2. **External Data Fetching**: The ingestion script checks active records in `wx_location_sources` to determine which external providers (`nws`, `openmeteo`) are assigned to each location (`wx_locations`).
3. **Database Caching**: Fresh weather observations are written to `wx_observations`, and forecasts/alerts are cached in `wx_forecasts` and `wx_alerts_active`. Provider success/error states are tracked in `wx_sources`.
4. **API & Frontend Hydration**: When the dashboard requests data, `current.php` queries the cached MySQL records rather than blocking on live external API calls, ensuring instant UI rendering.

---

### `data.source.md` (Documentation)

```markdown
# WeatherDash Data Sources & Architecture

## Overview
WeatherDash uses a decoupled **Ingest-and-Cache** architecture. To prevent slow page loads and external API rate limits, live weather metrics and forecasts are fetched periodically by a background CLI script and stored in MySQL. The frontend and API endpoints serve data directly from these local database caches.

---

## Cron Ingestion Schedule
Background ingestion runs every 5 minutes:
```bash
*/5 * * * * /opt/homebrew/bin/php /Users/rbutare/websites/apps/weather_station/weather/scripts/weather.ingest.cli.php

```

---

## Database Schema & Relationships

### 1. `wx_locations`

Stores monitoring stations and geographic metadata.

* **Key Columns:** `id`, `nickname` (e.g., `home`, `Raleigh`), `zip`, `lat`, `lon`, `tz`, `nws_json` (contains grid points and station IDs like `KSOP`, `KRDU`).

### 2. `wx_sources`

Defines available external weather service providers.

* **Key Columns:** `id` (e.g., `nws`, `openmeteo`), `name`, `enabled`, `last_success_at`, `last_error_at`.

### 3. `wx_location_sources` (Mapping Table)

Links stations to specific weather providers with configurable priorities and maximum cache ages.

* **Key Columns:** `location_id`, `source_id`, `priority`, `max_age_min`, `enabled`.

### 4. `wx_observations`

Contains historical and current weather metrics fetched from providers.

* **Key Columns:** `location_id`, `source_id`, `observed_at`, `received_at`, `station_id`, `temp_c`, `humidity_pct`, `wind_speed_ms`, `pressure_hpa`, `sky_text`.

### 5. `wx_forecasts` & `wx_alerts_active`

Stores JSON payloads for multi-day forecasts and active weather alerts per location.

---

## Why Multiple Sources Are Used

* **Redundancy & Failover:** If the National WeatherService (NWS) API experiences downtime or timeouts (as seen with Open-Meteo in audit logs), fallback providers ensure continuous data collection.
* **Precision:** Blends regional model forecasts with local station observations (e.g., combining NWS grid point forecasts with local airport METAR stations).

```

---

### `test.data.sources.sh` (Updated Diagnostic Script)

Here is the complete script handling all three testing layers (Locations API, Live Weather Service / Endpoint check, and Direct MySQL Database inspection):

```bash
#!/usr/bin/env bash
# ==============================================================================
# test.data.sources.sh — WeatherDash API & Database Diagnostics Tool
# 
# USAGE:
#   ./test.data.sources.sh [station_nickname]
#   Example: ./test.data.sources.sh home
#
# CONFIGURATION:
#   - WEATHER_API_BASE: Override base URL and port if needed 
#     (Defaults to HTTPS on port 8443 for local SSL testing: https://rickweb.test:8443/weatherdash/api/weather_station)
# ==============================================================================

STATION=${1:-home}
BASE_URL=${WEATHER_API_BASE:-"https://rickweb.test:8443/weatherdash/api/weather_station"}

# Helper function for URL encoding without external dependencies
urlencode() {
    python3 -c "import urllib.parse, sys; print(urllib.parse.quote(sys.argv[1]))" "$1" 2>/dev/null || echo "$1"
}

echo "=================================================================="
echo " 🌦️ WeatherDash Diagnostics Tool"
echo " Target Station : $STATION"
echo " Endpoint Base  : $BASE_URL"
echo "=================================================================="

# 1. Test locations.php (Static/Config/Locations Metadata)
echo ""
echo "▶ [1/3] Testing locations.php (Stations & Metadata)..."
LOC_RESPONSE=$(curl -k -s -w "\nHTTP_STATUS:%{http_code}" "$BASE_URL/locations.php")
LOC_STATUS=$(echo "$LOC_RESPONSE" | grep "HTTP_STATUS" | cut -d: -f2)
LOC_BODY=$(echo "$LOC_RESPONSE" | sed '/HTTP_STATUS/d')

if [ "$LOC_STATUS" -eq 200 ]; then
    echo "Status: 200 OK ✅"
    echo "$LOC_BODY" | json_pp 2>/dev/null || echo "$LOC_BODY"
else
    echo "Status: FAILED ($LOC_STATUS) ❌"
    echo "$LOC_BODY"
fi

echo ""
echo "------------------------------------------------------------------"

# 2. Test current.php (API Layer / Cached Weather Service Output)
ENCODED_STATION=$(urlencode "$STATION")
echo "▶ [2/3] Testing current.php?station=$STATION (API / Cached Output)..."
CUR_RESPONSE=$(curl -k -s -w "\nHTTP_STATUS:%{http_code}" "$BASE_URL/current.php?station=$ENCODED_STATION")
CUR_STATUS=$(echo "$CUR_RESPONSE" | grep "HTTP_STATUS" | cut -d: -f2)
CUR_BODY=$(echo "$CUR_RESPONSE" | sed '/HTTP_STATUS/d')

if [ "$CUR_STATUS" -eq 200 ]; then
    echo "Status: 200 OK ✅"
    echo "$CUR_BODY" | json_pp 2>/dev/null || echo "$CUR_BODY"
else
    echo "Status: FAILED ($LOC_STATUS) ❌"
    echo "$CUR_BODY"
fi

echo ""
echo "------------------------------------------------------------------"

# 3. Test Direct Database Inspection (MySQL wx_observations & wx_current)
echo "▶ [3/3] Testing Direct Database Record (MySQL intel145_weather)..."
DB_NAME="intel145_weather"
DB_USER="intel145_drumhill"

if command -v mysql &> /dev/null; then
    mysql -u "$DB_USER" -D "$DB_NAME" -e "
        SELECT l.nickname, o.source_id, o.station_id, o.temp_c, o.humidity_pct, o.observed_at 
        FROM wx_observations o 
        JOIN wx_locations l ON o.location_id = l.id 
        WHERE l.nickname = '$STATION' 
        ORDER BY o.observed_at DESC LIMIT 3;
    " 2>/dev/null || \
    mysql -u "$DB_USER" -D "$DB_NAME" -p -e "
        SELECT l.nickname, o.source_id, o.station_id, o.temp_c, o.humidity_pct, o.observed_at 
        FROM wx_observations o 
        JOIN wx_locations l ON o.location_id = l.id 
        WHERE l.nickname = '$STATION' 
        ORDER BY o.observed_at DESC LIMIT 3;
    "
else
    php -r "
        try {
            $pdo = new PDO('mysql:host=localhost;dbname=intel145_weather', '$DB_USER', '');
            $stmt = \$pdo->prepare('
                SELECT l.nickname, o.source_id, o.station_id, o.temp_c, o.humidity_pct, o.observed_at 
                FROM wx_observations o 
                JOIN wx_locations l ON o.location_id = l.id 
                WHERE l.nickname = ? 
                ORDER BY o.observed_at DESC LIMIT 3
            ');
            $stmt->execute(['$STATION']);
            print_r(\$stmt->fetchAll(PDO::FETCH_ASSOC));
        } catch(Exception \$e) {
            echo 'DB Error: ' . \$e->getMessage() . PHP_EOL;
        }
    "
fi

echo ""
echo "=================================================================="
echo " Diagnostics completed."
echo "=================================================================="

```