Rely on automatically maintained assignment counts without manual reconciliation
A Supabase PostgreSQL database trigger fires automatically on every insert into the assignments table, incrementing a denormalized assignment count field on the corresponding peer_mentors record. This approach avoids expensive aggregate queries on every page load and guarantees consistency — there is no application code path through which an assignment can be recorded without the count updating. Coordinators and administrators benefit from trusting that the displayed count is always the true count, which is the foundation of dispute resolution and payment accuracy.
User Story
Acceptance Criteria
- Given a new assignment is inserted into the assignments table for a peer mentor, when the database commit completes, then the peer mentor's denormalized assignment count is incremented by exactly 1
- Given the assignment count is incremented, when the Assignment Count Screen is loaded for that peer mentor, then the newly incremented value is displayed without requiring a manual refresh or cache clear
- Given a concurrent batch of assignments is inserted for the same peer mentor, when all inserts complete, then the final count reflects every inserted assignment with no lost increments
- Given an assignment insert is rolled back due to a database error, when the transaction fails, then the assignment count is not incremented
- Given a coordinator views the count immediately after an assignment is completed, then the count shown matches the database value with no stale reads
Business Value
Accurate honorarium payment depends entirely on reliable assignment counts. Any gap between actual completions and the displayed count erodes trust, creates disputes, and potentially violates grant compliance requirements. Automating count maintenance at the database level makes the system the authoritative source of truth, replacing error-prone spreadsheet tracking.
Components
- Assignment Count Database Trigger infrastructure
- Assignment Count Repository data
- Supabase Platform infrastructure