Thoughts

Tat-Tally Session 40 (2026-03-14): Security hardening and database fixes for pre-Norfolk audit items S1, S8-S11. S1 - Twilio Signature Validation: Added HMAC-SHA1 validation to sms-status-webhook Edge Function. Validates X-Twilio-Signature header using TWILIO_AUTH_TOKEN env var. Invalid/missing signatures return 403. Graceful fallback if auth token not yet configured. Deployed as v3. S2 - Service Role Key Auth: Already fixed in Session 37 (B5). Both check-approaching and send-sms use strict === equality. No changes needed. S8 - Error Response Sanitization: Redeployed 12 Edge Functions to remove internal error details from client responses. All catch blocks now use console.error() for server-side logging and return generic messages ("An error occurred", "Failed to [action]") to clients. No more Postgres table names, column names, or constraint details exposed. Functions updated: submit-entry (v6), sync-scores (v6), queue-action (v7), reset-score (v4), generate-qr (v8), display-broadcast (v4), create-judge (v5), get-portal (v5), withdraw-entry (v4), check-approaching (v5), send-sms (v4), seed-event (v6). Two functions (sms-acknowledge, sms-status-webhook) already had safe error handling. S9 - FOR UPDATE Lock on assign_queue_position: Applied migration to add row-level locking (FOR UPDATE) to the assign_queue_position trigger function. Now matches the pattern used in assign_entry_number. Prevents duplicate queue positions during concurrent entry submissions (intake rush scenario). S10 - Leaderboard Tiebreaker: Applied migration to CREATE OR REPLACE VIEW v_leaderboard with updated window function: ORDER BY final_score DESC NULLS LAST, submitted_at ASC. Earlier submissions now win ties consistently instead of arbitrary ordering. S11 - Withdrawn Entry Guard: Applied migration to add withdrawn status check to update_entry_final_score trigger. If entry's queue_status is 'withdrawn', returns NEW immediately without calculating final_score. Prevents withdrawn entries from appearing on the leaderboard. Remaining open audit items: S3-S7 (multi-tenancy RLS hardening, safe to defer past Norfolk single-event test), N1-N9 (nice-to-haves for Atlanta). All blockers (B1-B5) and all should-fix items except multi-tenancy (S1-S2, S8-S22) are now complete.