Create MonthlyDepartmentReport table

[HamzahSaleh]

Create MonthlyDepartmentReport table

Description

Add MonthlyDepartmentReport to track each department’s report status for a given reporting month.

Technical Details

The diagram expects department-specific metric tables to reference MonthlyDepartmentReport.id, not a raw reporting_month date or synthetic YYYYMM id.

Schema Changes

Create monthly_department_report with:

• id SERIAL PRIMARY KEY
• month_context_id INTEGER NOT NULL referencing month_context.id
• department TEXT NOT NULL
• status TEXT NOT NULL DEFAULT 'not_started' with allowed values not_started, in_progress, submitted, approved
• submitted_at TIMESTAMP NULL
• submitted_by TEXT NULL
• last_updated_at TIMESTAMP
• Unique constraint on (month_context_id, department)

Frontend Tasks

When a MonthContext is created, generate one department report row per tracked department. Update department pages and APIs to use monthly_report_id.

Acceptance Criteria

• A department has at most one report row per reporting month.
• New month creation can generate department report rows.
• Department APIs can accept or resolve monthly_report_id.

Dependencies

Depends on MonthContext.