SPECIES_INSERT_FIX.md

Species Insert Issue - Diagnosis and Fix

Problem

When users add a new species through the web interface, the console logs show "successfully updated" but the data is not actually saved to the Supabase species table.

Root Cause

The issue was in api.py at line 1042. The code was calling:

supabase.table("species").insert(species_data).execute()

But it never checked the response to verify if the insert actually succeeded. The code would continue and print "Saved to Supabase" even if the insert failed silently.

Common Reasons for Silent Failures

1. Duplicate Key Constraint Violation

   • The species table has a UNIQUE constraint on (species_name, location)
   • If a species with the same name already exists at that location, the insert fails
   • Previous code didn't catch this error

2. Row Level Security (RLS) Policy Issues

   • If RLS policies aren't configured correctly, inserts can fail
   • The diagnostic test confirmed RLS is working correctly

3. Missing Response Validation

   • The Supabase client returns a response object
   • If response.data is empty, the insert failed
   • Previous code didn't check this

Fixes Applied

1. Enhanced Response Validation (api.py)

# Before (line 1042):
supabase.table("species").insert(species_data).execute()
print(f"[INFO] Saved {species_name} to Supabase")

# After:
response = supabase.table("species").insert(species_data).execute()

# Check if the insert was successful
if not response.data or len(response.data) == 0:
    error_msg = f"Insert failed - no data returned. Response: {response}"
    print(f"[ERROR] {error_msg}")
    raise Exception(error_msg)

print(f"[INFO] Successfully saved {species_name} to Supabase")
print(f"[DEBUG] Insert response: {response.data}")

2. Better Error Messages (api.py)

except Exception as e:
    error_str = str(e)
    
    # Check for specific error types
    if "duplicate key" in error_str.lower():
        raise HTTPException(
            status_code=409, 
            detail=f"This species '{species_name}' already exists at location '{location}'."
        )
    elif "permission denied" in error_str.lower():
        raise HTTPException(
            status_code=403,
            detail=f"Permission denied. Please contact administrator."
        )
    else:
        raise HTTPException(
            status_code=500, 
            detail=f"Failed to save to database: {error_str}"
        )

Diagnostic Tools Created

1. test_supabase_insert.py

• Tests the complete insert workflow
• Verifies RLS policies are working
• Identifies specific error types
• Status: ✅ All tests passed

2. check_duplicates.py

• Lists all species in the database
• Identifies duplicate entries
• Shows species grouped by type

3. fix_species_insert.sql

• SQL script to fix RLS policies if needed
• Drops and recreates policies with correct permissions
• Includes verification queries

How to Test

1. Run the diagnostic test:

   cd greenai
   python test_supabase_insert.py
   

2. Check for duplicates:

   python check_duplicates.py
   

3. Restart the API server:

   python api.py
   

4. Try adding a species from the web interface

   • If it fails, you'll now see a clear error message
   • Check the console for detailed debug logs

Expected Behavior Now

Success Case:

• Console shows: [DEBUG] Attempting to insert species data: {...}
• Console shows: [INFO] Successfully saved {species_name} to Supabase
• Console shows: [DEBUG] Insert response: [...]
• User sees: "Submitted Successfully!" toast

Duplicate Species:

• Console shows: [ERROR] Failed to save to Supabase: duplicate key...
• User sees: "This species 'X' already exists at location 'Y'" error message

Permission Error:

• Console shows: [ERROR] Failed to save to Supabase: permission denied...
• User sees: "Permission denied. Please contact administrator." error message

Next Steps

1. ✅ Response validation added
2. ✅ Error handling improved
3. ✅ Diagnostic tools created
4. ⏳ Restart the API server to apply changes
5. ⏳ Test adding a new species through the web interface
6. ⏳ Check the console logs for detailed debug information

Files Modified

• greenai/api.py - Enhanced insert validation and error handling
• greenai/test_supabase_insert.py - New diagnostic tool
• greenai/check_duplicates.py - New duplicate checker
• greenai/fix_species_insert.sql - RLS policy fix script

Verification Checklist

• Supabase connection works
• RLS policies allow INSERT
• Response validation added
• Error messages improved
• API server restarted
• Test species submission works
• Duplicate detection works
• Error messages display correctly