Problem
When a student submits an activity request (or annual report / org application), files are uploaded to Google Drive before the database insert. If the DB insert fails after upload, the files are left orphaned in Drive with no database record pointing to them.
Affected files
srobackend/src/routes/activityRequestRoutes.js (lines 94-147)srobackend/src/routes/annualReportRoutes.js (folder creation + file upload + DB insert)srobackend/src/routes/orgApplicationRoutes.js (same pattern)
Current flow (broken)
1. Upload concept_paper to Google Drive → success
2. Upload form_2b to Google Drive → success
3. Insert activity to DB → FAILS
4. Insert schedule to DB → never reached
5. Error returned to user
6. Files are now orphaned in Google Drive forever
Impact
- Orphaned files accumulate in Google Drive over time, consuming storage quota
- No way to trace or clean up orphaned files since there's no DB record
- Same issue applies if folder creation succeeds but file upload fails (empty folder left behind)
- Low frequency in practice (DB inserts rarely fail), but compounds over months
Proposed fix
Reorder the flow to insert the DB record first with placeholder links, then upload files, then update the record with the real links. On upload failure, delete the DB record.
1. Insert activity to DB with null file links → success
2. Upload concept_paper to Google Drive → success
3. Upload form_2b to Google Drive → success
4. Update activity record with file links → done
On failure at step 2 or 3:
- Delete the activity record from DB
- Delete any files already uploaded to Drive via drive.files.delete()
Alternatively, add a cleanup cron job that finds activity records with null file links older than 1 hour and deletes them + their Drive files.
Why we deferred this
This fix touches 3 route files and requires changing the Google Drive upload error handling, adding Drive file deletion on failure, and reordering the entire request flow. High blast radius — the upload/insert flow is the most critical path in the app (student submissions). We chose to ship the other performance fixes first and tackle this separately with proper testing.
The current failure mode only triggers when Supabase is down or returns an error, which is rare in practice.
Problem
When a student submits an activity request (or annual report / org application), files are uploaded to Google Drive before the database insert. If the DB insert fails after upload, the files are left orphaned in Drive with no database record pointing to them.
Affected files
srobackend/src/routes/activityRequestRoutes.js(lines 94-147)srobackend/src/routes/annualReportRoutes.js(folder creation + file upload + DB insert)srobackend/src/routes/orgApplicationRoutes.js(same pattern)Current flow (broken)
Impact
Proposed fix
Reorder the flow to insert the DB record first with placeholder links, then upload files, then update the record with the real links. On upload failure, delete the DB record.
Alternatively, add a cleanup cron job that finds activity records with null file links older than 1 hour and deletes them + their Drive files.
Why we deferred this
This fix touches 3 route files and requires changing the Google Drive upload error handling, adding Drive file deletion on failure, and reordering the entire request flow. High blast radius — the upload/insert flow is the most critical path in the app (student submissions). We chose to ship the other performance fixes first and tackle this separately with proper testing.
The current failure mode only triggers when Supabase is down or returns an error, which is rare in practice.