CRITICAL: Geofence Events Foreign Key is Broken

Problem Confirmed

location_reports.id is NOT UNIQUE - there are duplicate IDs:

• id=166532: 2 occurrences
• id=166533: 2 occurrences
• id=166534: 2 occurrences
• etc.

But geofence_events.location_report_id references location_reports.id as if it were unique!

Current Broken Schema

-- location_reports
PRIMARY KEY (id, timestamp)  -- Composite PK
-- id is NOT unique!

-- geofence_events (BROKEN!)
location_report_id INTEGER REFERENCES location_reports(id)
-- This FK references a non-unique column!

Impact

1. Ambiguous joins - ge.location_report_id = lr.id can match multiple rows
2. Wrong reports processed - Service may process the wrong location report
3. Unreliable tracking - Geofence events may point to wrong location data
4. Query failures - JOIN results are unpredictable

Why the Backfill "Works"

The backfill script creates geofence_events using just location_report_id, which is wrong but appears to work because:

1. It processes one report at a time
2. The most recent report with that id happens to be processed
3. But the FK relationship is still broken

Required Fix

Step 1: Add composite FK to geofence_events

-- Add timestamp column to geofence_events
ALTER TABLE geofence_events
ADD COLUMN location_report_timestamp TIMESTAMPTZ;

-- Populate it from existing location_reports (best effort)
UPDATE geofence_events ge
SET location_report_timestamp = (
    SELECT lr.timestamp
    FROM location_reports lr
    WHERE lr.id = ge.location_report_id
    ORDER BY lr.timestamp DESC
    LIMIT 1
);

-- Drop old FK constraint
ALTER TABLE geofence_events
DROP CONSTRAINT IF EXISTS geofence_events_location_report_id_fkey;

-- Create composite FK
ALTER TABLE geofence_events
ADD CONSTRAINT geofence_events_location_report_fkey
FOREIGN KEY (location_report_id, location_report_timestamp)
REFERENCES location_reports(id, timestamp);

Step 2: Update the model

class GeofenceEvent(Base):
    __tablename__ = "geofence_events"

    id = Column(Integer, primary_key=True, index=True)
    tracker_id = Column(Integer, ForeignKey("trackers.id"), nullable=False, index=True)

    # Composite FK to location_reports
    location_report_id = Column(Integer, nullable=False)
    location_report_timestamp = Column(DateTime, nullable=False)

    __table_args__ = (
        ForeignKeyConstraint(
            ['location_report_id', 'location_report_timestamp'],
            ['location_reports.id', 'location_reports.timestamp']
        ),
        Index('idx_geofence_location_report', 'location_report_id', 'location_report_timestamp'),
    )

Step 3: Update all queries

Every query that creates or joins geofence_events must use composite key:

-- ❌ WRONG (current)
LEFT JOIN geofence_events ge ON ge.location_report_id = lr.id

-- ✅ CORRECT (required)
LEFT JOIN geofence_events ge
    ON ge.location_report_id = lr.id
    AND ge.location_report_timestamp = lr.timestamp

Step 4: Update service code

When creating geofence events, store both id and timestamp:

event = GeofenceEvent(
    tracker_id=tracker.id,
    location_report_id=location_report.id,
    location_report_timestamp=location_report.timestamp,  # NEW!
    # ... rest of fields
)

Temporary Workaround

Until schema is fixed, queries MUST join on composite key even though FK doesn't enforce it:

LEFT JOIN geofence_events ge
    ON ge.location_report_id = lr.id
    AND ge.timestamp = lr.timestamp

Use ge.timestamp (event creation time) as proxy for report timestamp - not perfect but better than broken join.

Priority

CRITICAL - This breaks the entire geofence tracking system. Every geofence event may be pointing to the wrong location report.