Bot crashes on !check for 'city' targets due to incorrect data format and lack of geocoding

[timothyfroehlich]

Bug Description

The bot crashes when the !check command is executed in a channel that monitors a city target. The root cause is a ValueError in the _process_target method within src/cogs/runner.py.

This happens because the runner code expects the target_name field for city targets to contain comma-separated coordinates (e.g., "30.26715,-97.74306"), but the database contains the city's name as a string (e.g., "Austin, Texas, US"). The code then attempts to convert the string "Austin" into a float, causing the crash.

Evidence

Log Output:

ValueError: could not convert string to float: 'Austin'

Database Analysis:

An inspection of the production database reveals that for target_type = 'city', the target_name column stores the city name, and the location_id column incorrectly stores the coordinates.

-- Example of malformed data
SELECT id, target_type, target_name, location_id FROM monitoring_targets WHERE target_type = 'city' LIMIT 1;
-- Result: 1|city|Austin, Texas, US|30.26715,-97.74306

Root Cause

The issue stems from the command that adds city monitors (!add city). This command appears to be storing the city name and coordinates in the wrong columns (target_name and location_id respectively) and is setting the target_type to city instead of the correct latlong type after geocoding.

Proposed Solution

A complete fix requires two main actions:

1. Code Fix: Modify the !add city command logic.

   • It should perform geocoding on the provided city name.
   • It must save the result as a latlong target, storing the coordinates in the target_name field.
   • The target_type should be set to latlong, not city.

2. Data Migration: A migration script is needed to correct the existing malformed data in the monitoring_targets table.

   • The script should identify all records where target_type = 'city'.
   • For each record, it should copy the coordinates from the location_id column to the target_name column.
   • It should then update the target_type from city to latlong.
   • Finally, it should set the location_id to NULL for these records.

This two-pronged approach will resolve the immediate crash and prevent the issue from recurring.

[timothyfroehlich]

Fixed in commits 2ed8400, f0338dd, and eb2fb99. The bot now handles 'city' targets gracefully and provides better logging.

[timothyfroehlich]

✅ RESOLVED - Issue fixed by database schema modernization

Evidence:

• Database no longer contains problematic 'city' target types
• Current schema only uses 'location' and 'geographic' types
• No more crashes when running !check command

Verification:

The database schema has been modernized and this crash no longer occurs. Closing as resolved.

Resolved in monitoring loop investigation: #92