Staff Shift Scheduler & Roster

You are a senior full-stack engineer specializing in HR and workforce management systems for Malaysian SMEs — restaurants, retail shops, clinics, and factories. Build a complete Staff Shift Scheduler and Roster system from scratch that replaces manual Excel scheduling and paid tools like Deputy or When I Work.

## Tech Stack
- Next.js 14 (App Router) + TypeScript
- Tailwind CSS + shadcn/ui component library
- Supabase (PostgreSQL + Auth + Row Level Security)
- Vercel deployment
- @hello-pangea/dnd for drag-and-drop scheduling
- date-fns for all date manipulation

## Database Schema (Supabase / PostgreSQL)

Create all tables with full column definitions, indexes, and RLS policies.

### employees
sql
create table employees (
  id uuid primary key default gen_random_uuid(),
  company_id uuid references companies(id),
  full_name text not null,
  preferred_name text,
  department text not null,
  role text not null,
  phone text,
  email text,
  employment_type text default 'full_time', -- full_time / part_time / contract
  max_hours_per_week integer default 48,
  min_rest_hours_between_shifts integer default 8,
  hourly_rate numeric(10,2),
  is_active boolean default true,
  created_at timestamptz default now()
);
create index on employees(company_id);
create index on employees(department);


### shift_templates
sql
create table shift_templates (
  id uuid primary key default gen_random_uuid(),
  company_id uuid references companies(id),
  name text not null,
  start_time time not null,
  end_time time not null,
  color text default '#3B82F6',
  crosses_midnight boolean default false,
  break_minutes integer default 30,
  is_active boolean default true
);
create index on shift_templates(company_id);


### schedules
sql
create table schedules (
  id uuid primary key default gen_random_uuid(),
  company_id uuid references companies(id),
  employee_id uuid references employees(id) on delete cascade,
  shift_template_id uuid references shift_templates(id),
  schedule_date date not null,
  status text default 'scheduled', -- scheduled / confirmed / swapped / absent
  is_published boolean default false,
  notes text,
  created_by uuid references auth.users(id),
  created_at timestamptz default now()
);
create index on schedules(company_id, schedule_date);
create index on schedules(employee_id);
create unique index on schedules(employee_id, schedule_date);


### availability
sql
create table availability (
  id uuid primary key default gen_random_uuid(),
  employee_id uuid references employees(id) on delete cascade,
  day_of_week integer not null, -- 0=Mon ... 6=Sun
  is_available boolean default true,
  preferred_shift_id uuid references shift_templates(id),
  notes text
);
create index on availability(employee_id);


### swap_requests
sql
create table swap_requests (
  id uuid primary key default gen_random_uuid(),
  company_id uuid references companies(id),
  requester_id uuid references employees(id),
  target_id uuid references employees(id),
  requester_schedule_id uuid references schedules(id),
  target_schedule_id uuid references schedules(id),
  reason text,
  status text default 'pending', -- pending / approved / rejected / cancelled
  manager_notes text,
  reviewed_by uuid references auth.users(id),
  reviewed_at timestamptz,
  created_at timestamptz default now()
);
create index on swap_requests(company_id, status);
create index on swap_requests(requester_id);


### time_off_requests
sql
create table time_off_requests (
  id uuid primary key default gen_random_uuid(),
  company_id uuid references companies(id),
  employee_id uuid references employees(id),
  start_date date not null,
  end_date date not null,
  type text default 'annual', -- annual / medical / emergency / unpaid
  reason text,
  status text default 'pending',
  reviewed_by uuid references auth.users(id),
  created_at timestamptz default now()
);
create index on time_off_requests(company_id, status);
create index on time_off_requests(employee_id);


### public_holidays
sql
create table public_holidays (
  id uuid primary key default gen_random_uuid(),
  company_id uuid references companies(id),
  holiday_date date not null,
  name_en text not null,
  name_zh text,
  is_paid boolean default true
);
create index on public_holidays(company_id, holiday_date);


Seed Malaysian public holidays for 2025 including: New Year's Day, Chinese New Year (2 days), Hari Raya Aidilfitri (2 days), Hari Raya Aidiladha, Labour Day, Yang di-Pertuan Agong's Birthday, National Day, Malaysia Day, Deepavali, and Christmas Day.

## RLS Policies

Apply the following RLS patterns to all tables:
- Wrap `auth.uid()` in `(select auth.uid())` to prevent per-row evaluation (critical for performance at scale)
- Managers (authenticated users with company_id match) get full SELECT/INSERT/UPDATE/DELETE
- Employees (authenticated via Magic Link) get SELECT on their own schedules, INSERT on swap_requests and time_off_requests
- Public holidays and shift_templates are readable by all authenticated users in the same company

## Core Features

### 1. Weekly Roster Grid (Primary View)
- Render a staff × days matrix for the selected week
- Columns: Monday through Sunday with date labels; mark public holidays with a colored badge
- Rows: each employee grouped by department; show name, role, and weekly hours tally
- Each cell: shift name + time range, color-coded by shift type
- Color scheme: Morning=#3B82F6 (blue), Afternoon=#22C55E (green), Night=#8B5CF6 (purple), Off=#94A3B8 (gray), Unassigned=#FEE2E2 (light red warning)
- Summary row at the bottom of each department group: scheduled count vs minimum required
- Week navigation arrows (previous / current / next week)
- Department filter tabs at the top of the grid

### 2. Drag-and-Drop Shift Assignment
- Use @hello-pangea/dnd for all drag interactions
- Left panel: draggable shift template cards (color-coded)
- Drop onto any grid cell to assign that shift to that employee on that date
- Support dragging existing assignments horizontally (change date) or vertically (change employee)
- Pre-drop validation: check minimum rest hours between shifts, weekly hour limit, approved time off
- Show a red overlay on invalid drop targets with tooltip explaining the violation
- Allow manager to override with a confirmation dialog

### 3. Auto-Schedule Suggestion
- Endpoint: POST /api/auto-schedule with { week_start_date, company_id }
- Algorithm logic:
  1. Pull all employees, their availability preferences, and approved time-off for the week
  2. For each day, sort employees by: availability match > preferred shift match > fewest hours assigned so far
  3. Fill shifts until minimum staffing requirements per shift are met
  4. Respect max_hours_per_week and min_rest_hours_between_shifts constraints
  5. Flag employees who would exceed 6 consecutive days
- Return the draft schedule as a JSON array; save to schedules table with is_published=false
- Display draft in the roster grid with a "Draft" banner; manager reviews and edits before publishing

### 4. Shift Swap Request Workflow
- Employee self-service: select own shift, choose a colleague, select their shift to swap with, add reason
- System validates: same department or role compatibility, neither employee exceeds weekly hour cap post-swap
- Manager receives a notification card in the Requests dashboard
- Approve / Reject with optional notes; approved swaps update both schedule records atomically
- Grid shows a swap-pending indicator (⇄ icon) on affected cells
- Rejected requests notify the requester with the manager's note

### 5. Overtime Detection and Malaysian Labor Law Compliance
- Calculate total daily hours for each schedule entry (handle cross-midnight shifts correctly)
- Overtime triggers: > 8 hours/day OR > 48 hours/week (Employment Act 1955, Section 60A)
- Display orange warning badge on overtime cells showing excess hours
- Mandatory rest: at least 8 hours between consecutive shifts; flag violations in red
- Minimum 1 rest day per 7 days; highlight employees with no rest day in the current week
- Public holiday worked: auto-flag for 2x pay rate in labor cost estimates
- Settings page: allow managers to configure overtime thresholds per employment type

### 6. Department and Role-Based Scheduling Rules
- Each department can have its own minimum staffing requirements per shift
- Role constraints: certain shifts require at least 1 person with a specific role (e.g., Kitchen must have 1 Head Chef per shift)
- Store these rules in a shift_rules table: { department, shift_template_id, min_count, required_role }
- Show staffing coverage as color-coded status in the summary row: green (met), orange (minimum only), red (understaffed)

### 7. Employee Self-Service Portal (/portal)
- Employees log in via Supabase Magic Link sent to their email or phone
- Mobile-first single-column layout optimized for iPhone/Android browsers
- View personal schedule for current and next 2 weeks in a card list format
- Submit shift swap request: pick date, pick colleague, add reason
- Submit time-off request: date range, type, reason
- Set weekly availability preferences: toggle each day and select preferred shift
- All submissions show pending/approved/rejected status with timestamps

### 8. Schedule Publishing
- Draft schedules are only visible to managers
- "Publish This Week" button changes all is_published=false records for that week to true
- Published timestamp and published_by user recorded
- Post-publish, employees can see their schedule in the self-service portal
- Include a pre-publish validation summary: list any open shifts (below minimum), overtime violations, and employees with no rest day

## UI/UX Specifications

### Layout
- Top navigation bar: dark navy #0F172A background, company name, manager avatar, notification bell
- Left sidebar (collapsible on tablet): navigation links — Roster, Employees, Requests, Settings
- Main content: roster grid with sticky header row (days) and sticky first column (employee names)
- Right panel (toggle): coverage heatmap, overtime list, upcoming time-off, labor cost estimate

### Top Summary Cards (4 cards in a row)
- Total Employees (active count)
- Scheduled This Week (distinct employees with at least 1 shift)
- Open Shifts (cells below minimum staffing, shown with red badge)
- Pending Requests (swap + time-off, shown with yellow badge)

### Coverage Heatmap
- 7 cells (Mon–Sun) showing scheduled / required ratio
- Green (#22C55E) = 100%+, Orange (#F59E0B) = 70–99%, Red (#EF4444) = below 70%

### Labor Cost Estimator
- Sum of: (shift_hours × hourly_rate) for each scheduled employee
- Add 1.5x for overtime hours, 2x for rest day shifts, 3x for public holiday shifts
- Display as weekly total in MYR with a breakdown tooltip

### Responsive Behavior
- Desktop (≥1024px): full grid + right panel side by side
- Tablet (768–1023px): grid scrolls horizontally, right panel toggled via button
- Mobile (<768px): manager sees a simplified day-by-day card view; employees see the self-service portal only

## File Structure


src/
├── app/
│   ├── (manager)/
│   │   ├── roster/page.tsx
│   │   ├── roster/[week]/page.tsx
│   │   ├── employees/page.tsx
│   │   ├── requests/page.tsx
│   │   └── settings/page.tsx
│   ├── portal/page.tsx
│   └── api/
│       ├── schedules/route.ts
│       ├── swap-requests/route.ts
│       └── auto-schedule/route.ts
├── components/
│   ├── roster-grid.tsx
│   ├── shift-cell.tsx
│   ├── shift-template-panel.tsx
│   ├── swap-request-card.tsx
│   ├── coverage-heatmap.tsx
│   ├── labor-cost-estimator.tsx
│   └── employee-portal-view.tsx
└── lib/
    ├── scheduling-engine.ts
    ├── overtime-calculator.ts
    └── malaysia-holidays.ts


## Business Context Variables

Company: {{company_name}}
Shift types: {{shift_types}}
Departments: {{departments}}
Minimum staff per shift: {{min_staff_per_shift}}

## Build Order

Build and confirm each step before proceeding:
1. Create Supabase schema, indexes, RLS policies, and seed Malaysian public holidays
2. Build the weekly roster grid with static mock data (8 employees, 3 departments)
3. Wire up Supabase data fetching — replace mock data with live queries
4. Implement drag-and-drop shift assignment with validation
5. Build the swap request flow (employee submission + manager approval)
6. Add overtime calculation and Malaysian labor law compliance warnings
7. Build the employee self-service portal (mobile-first)
8. Implement auto-schedule suggestion algorithm
9. Add labor cost estimator and coverage heatmap to the dashboard

After each step, show me what was built and ask before continuing.