availability-fixes-plan.md

Availability Backend Fixes - Implementation Plan

Problem Summary

The availability tests are failing due to inconsistent date handling between the database (DATE type) and the application code (Date objects with time components). When availability rows are created with one timestamp and later queried with another, they're not found, causing the tests to fail.

Root Cause

1. Database stores dates as DATE type (date-only)
2. Application code creates Date objects with varying time components
3. SQL joins and comparisons fail when one side has a time component and the other doesn't
4. The formatDateForDb() function extracts only the date part, but Date objects still retain time components

Solution: Date-Only Normalization

1. Add Date Normalization Helper

In app/lib/repositories/utils.ts, add a normalizeToDateOnly() function:

export function normalizeToDateOnly(date: Date): Date {
  return new Date(Date.UTC(date.getUTCFullYear(), date.getUTCMonth(), date.getUTCDate()))
}

2. Update Availability Repository (app/lib/repositories/availabilities.ts)

Changes needed:

1. In createAvailability() and upsertAvailability():

   • Normalize the date before calling formatDateForDb()

2. In getAvailabilitiesForStaffAndWeek():

   • No changes needed (already filters by staff_id and week_schedule_id only)

3. In getStaffAvailabilityForShift():

   • Update the SQL join to use date-only comparison:

     a.date::date = sh.date::date

3. Update Availability Helpers (app/lib/repositories/availability-helpers.ts)

Changes needed:

1. In getAvailabilityForToken():

   • Normalize dates when comparing availability entries with grid dates

2. In updateAvailabilityForToken():

   • Normalize the date before calling upsertAvailability()
   • Use the same normalization logic as in the repository

4. Update Test Data (app/__tests__/utils/test-data.ts)

Changes needed:

1. In createTestWeekSchedule():

   • Normalize the week_start_date to avoid time components

2. In createTestAvailability():

   • Normalize the availability date

5. Update Test Files

Changes needed:

1. In test files that compare dates:
   • Ensure consistent date-only comparison using .toISOString().split('T')[0]

Implementation Order

1. Add the date normalization helper
2. Update the availability repository
3. Update the availability helpers
4. Update test data creation
5. Update test expectations if needed
6. Run tests to verify fixes

Expected Outcome

After these changes:

• All availability dates will be stored and queried consistently as date-only values
• The 7×4 grid will correctly show updated availability states
• The getStaffAvailabilityForShift() query will correctly find matching availability rows
• All failing tests should pass