Create MonthContext table

[HamzahSaleh]

Create MonthContext table

Description

Add MonthContext as the single source of truth for reporting periods. Each row represents one unique reporting month/year and stores editable calendar/service context used by every department report.

Technical Details

Current schema stores reporting_month directly on several tables or computes a synthetic monthlyReportId in the frontend. The diagram expects all department reports and KPIs to reference MonthContext.

Schema Changes

Create month_context with:

• id SERIAL PRIMARY KEY
• year INTEGER NOT NULL
• month INTEGER NOT NULL
• days_closed INTEGER DEFAULT 0
• route44_weekdays_ran INTEGER
• route44_weekends_ran INTEGER
• shake_up_occurred BOOLEAN DEFAULT false
• holiday_saturdays INTEGER
• holiday_sundays INTEGER
• weekdays INTEGER
• fridays INTEGER
• saturdays INTEGER
• sundays INTEGER
• shakeup_weekdays INTEGER
• shakeup_fridays INTEGER
• shakeup_saturdays INTEGER
• shakeup_sundays INTEGER
• shakeup_holiday_saturdays INTEGER
• shakeup_holiday_sundays INTEGER
• shakeup_days_closed INTEGER
• shakeup_route44_weekdays INTEGER
• shakeup_route44_saturdays INTEGER
• Unique constraint on (year, month)

Frontend Tasks

Replace raw reporting-month-only workflows with a month picker flow that creates or looks up a MonthContext row and lets users review/edit the generated calendar fields.

Acceptance Criteria

• Users cannot create duplicate MonthContext rows for the same month/year.
• Calendar-derived fields are stored and editable.
• Department report creation can resolve a valid month_context_id.

Dependencies

None.