Add submit_day_sheet, disapprove_day_sheet, justify_day_sheet,
approve_day_sheets (all SECURITY DEFINER, grant to authenticated)
and auto_submit_day_sheets (revoked from all client roles).
auto_submit_day_sheets:
1. Reopens approved yesterday sheets for programmers whose timer
was still running at end of day (amended event).
2. Auto-promotes stale draft sheets to pending (auto_submitted event).
3. Inserts day_sheet_digest notifications + scheduled_notifications
(epoch=YYYYMMDD, ON CONFLICT DO NOTHING) for every admin.
Extends chk_at_least_one_ref on scheduled_notifications to allow
notify_type = 'day_sheet_digest' rows with no FK reference.
Registers pg_cron job 'programmer_day_sheet_autosubmit' at 16:05 UTC
(00:05 Asia/Manila) in a DO $$ EXCEPTION block.
Co-Authored-By: claude-flow <ruv@ruv.net>
Separate feature for the `programmer` role to record dev work by category,
distinct from the IT help-desk `tasks` feature. Modeled on the IT Service
Requests pattern (Brick offline-first model -> Riverpod stream/controller ->
list/detail screens); seed of a future Project Management workflow.
- programmer_tasks + programmer_task_activity_logs (category enum, RLS,
realtime) via supabase migration
- single assignee, self-logged or lead-assigned; assign/reassign logged
- pause-on-switch: starting a task while another runs prompts to pause the
running one, keeping one active task per user for accurate time attribution
- worked-duration math shared with help-desk tasks via a generic core in
task_duration.dart (existing callers unchanged)
- list distinguishes running vs paused with a per-task Resume shortcut
- accessible dialog labels; route /programmer-tasks + nav gated to admin/programmer
- unit/widget tests: controller lifecycle, duration math, pause-switch dialog
Co-Authored-By: claude-flow <ruv@ruv.net>
1. CRITICAL: Double idempotency check silently discarded every notification.
process_scheduled_notifications called try_mark_notification_pushed first,
then send_fcm called it again and saw "already pushed" → skipped FCM send.
Fix: Remove the check from process_scheduled_notifications; send_fcm's
own check is sufficient (and needed for direct Flutter client calls).
2. SEND_FCM_URL env var crash: If not configured, edge function crashed on
module load. Fix: Derive from SUPABASE_URL (always auto-set) as fallback.
3. Timing windows too narrow: ±30s windows with 60s cron intervals could
miss events between runs. Fix: Widen to ±90s. ON CONFLICT DO NOTHING
prevents duplicates, making wider windows safe.
Co-Authored-By: Claude Opus 4.6 <noreply@anthropic.com>
Use parameterized RAISE NOTICE with %L placeholders instead of trying to
concatenate strings inside quoted literals. This fixes the syntax error where
|| operators were being treated as string content instead of SQL operators.
Replace dollar-quoted strings with simple single-quoted strings in cron.schedule()
function calls. The cron.schedule() function expects text literals, not dollar-quoted
blocks. Use quote_literal() for the interval value in the cleanup query to properly
escape the string.
Adds comprehensive push notification reminders using pg_cron + pg_net:
- Shift check-in reminder (15 min before, with countdown banner)
- Shift check-out reminder (hourly, persistent until checkout)
- Overtime idle reminder (15 min without task)
- Overtime checkout reminder (30 min after task completion)
- IT service request event reminder (1 hour before event)
- IT service request evidence reminder (daily)
- Paused task reminder (daily)
- Backlog reminder (15 min before shift end)
- Pass slip expiry reminder (15 min before 1-hour limit, with countdown banner)
Database: Extended scheduled_notifications table to support polymorphic references
(schedule_id, task_id, it_service_request_id, pass_slip_id) with unique constraint
and epoch column for deduplication. Implemented 8 enqueue functions + master dispatcher.
Uses pg_cron every minute to enqueue and pg_net to trigger process_scheduled_notifications
edge function, eliminating need for external cron job. Credentials stored in vault with
GUC fallback for flexibility.
Flutter: Added ShiftCountdownBanner and PassSlipCountdownBanner widgets that display
persistent countdown timers for active shifts and pass slips. Both auto-dismiss when
user completes the action. FCM handler triggers shift countdown on start_15 messages.
navigate_to field in data payload enables flexible routing to any screen.
Edge function: Updated process_scheduled_notifications to handle all 9 types with
appropriate titles, bodies, and routing. Includes pass_slip_id in idempotency tracking.
Co-Authored-By: Claude Haiku 4.5 <noreply@anthropic.com>
redz1029