OSP Projects

Time Clock – Daily Changelog – September 5, 2025

Written by

OSP Core Projects

in

2025-09-05

Morning: PTO System Meltdown

  • Spun up the day by chasing a PTO gremlin. A user changed a PTO request date (shortened it), which should have auto-approved. It didn’t. Then manual approval failed with a lovely “Failed to update PTO request.” message.
  • Multiple cascading failures:

– Missing PostgreSQL functions (calculate_pto_balance, deduct_pto_usage) on Heroku

– Type casting issues (bigint vs integer, timestamp vs date)

– Role coercion problems (string vs number comparisons)

– Error messages not displaying to users

Afternoon: Complete PTO System Overhaul

Fixed Core Infrastructure:

  • Added runtime function initializer to create missing PostgreSQL functions if they don’t exist
  • Fixed all type casting issues in SQL queries (explicit INTEGER, NUMERIC, DATE casts)
  • Implemented proper role coercion (Number(session.user.role))
  • Enhanced error messaging throughout the approval flow

Implemented Major Features:

  • Unpaid PTO Support: Employees can now request time off even with insufficient balance – it gets approved as unpaid
  • Auto-approval Logic: PTO requests auto-approve when edited to shorter duration (no balance check needed)
  • Reserved PTO System: Future-dated approved requests reserve hours but don’t deduct until the time period begins
  • PTO Bank Deduplication: Fixed duplicate entries showing up in PTO Bank list
  • Database Integrity: Added unique index to prevent duplicate active “PTO Yearly Amount” records

UI Improvements:

  • Added “Unpaid” option to time-off request types
  • Enhanced PTO Bank display with new columns:

– Current Balance

– Reserved (Future) – hours reserved by future approved requests

– Available After Reservations – what’s actually available to request

  • Better error display with detailed server messages

Database Changes:

  • Applied migration 20250905_enforce_unique_active_pto_yearly_amount.sql to Heroku
  • Deactivated duplicate PTO Yearly Amount records
  • Created partial unique index to prevent future duplicates

Status

The PTO system is now significantly more robust and user-friendly. Employees can request time off regardless of balance, managers can approve unpaid time, and the system properly handles future-dated requests with reserved balances. The duplicate data issues have been resolved at both the database and UI levels.

Next Steps:

  • Test the full workflow end-to-end
  • Consider adding PTO balance notifications/alerts
  • Document the new reserved PTO system for users

Notes

  • Dark mode still default; no color token violations introduced
  • All changes maintain existing UTC storage and Monday=0 week logic

More posts