Build an HR Management System in Lovable

Build a multi-tenant HR management system. Stack: React + Vite + TypeScript + Tailwind + shadcn/ui (already scaffolded), React Router, Supabase JS client against Lovable Cloud.

IMPORTANT: This app handles employee PII (names, contact info, salary bands, medical leave categories, document scans). Multi-tenant RLS AND per-employee Storage RLS must both be correct. CVE-2025-48757 audited 1,645 Lovable apps and found 170 (10.3%) leaking exactly this data class.

## Database schema (create one migration)

Create 6 tables with RLS enabled on ALL of them:

1. `companies` — id (uuid pk default gen_random_uuid()), name (text not null), country_code (text), created_by (uuid references auth.users(id)), created_at (timestamptz default now()).

2. `members` — id (uuid pk default gen_random_uuid()), company_id (uuid not null references companies(id) on delete cascade), user_id (uuid references auth.users(id) on delete cascade), role (text not null default 'employee', check role in ('hr_admin','manager','employee')), employee_id (uuid — FK to employees, set after profile creation), created_at (timestamptz default now()). UNIQUE(company_id, user_id).

3. `employees` — id (uuid pk default gen_random_uuid()), company_id (uuid not null references companies(id)), full_name (text not null), work_email (text not null), personal_email (text), phone (text), role_title (text), manager_id (uuid references employees(id)), start_date (date), salary_band (text — e.g. 'L4','L5' — NEVER store raw salary amounts), status (text default 'active', check in ('active','on_leave','terminated')), created_at (timestamptz default now()).

4. `leave_requests` — id (uuid pk default gen_random_uuid()), employee_id (uuid references employees(id)), type (text check in ('vacation','sick','parental','bereavement','other')), starts_on (date not null), ends_on (date not null, check ends_on >= starts_on), reason (text), status (text default 'pending', check in ('pending','approved','rejected','cancelled')), reviewer_id (uuid references auth.users(id)), reviewed_at (timestamptz), created_at (timestamptz default now()).

5. `documents` — id (uuid pk default gen_random_uuid()), employee_id (uuid references employees(id)), category (text check in ('contract','id','tax_form','review','other')), filename (text not null), storage_path (text not null — format: {employee_id}/{uuid}-{filename}), uploaded_by (uuid references auth.users(id)), expires_on (date), created_at (timestamptz default now()).

6. `audit_log` — id (uuid pk default gen_random_uuid()), actor_id (uuid references auth.users(id)), action (text not null), entity_type (text not null), entity_id (uuid), payload (jsonb), created_at (timestamptz default now()).

Create these three SECURITY DEFINER plpgsql functions (NEVER SQL-language — they get inlined):

```sql
CREATE OR REPLACE FUNCTION is_company_member(p_company_id uuid)
RETURNS boolean LANGUAGE plpgsql SECURITY DEFINER STABLE AS $$
BEGIN
  RETURN EXISTS (
    SELECT 1 FROM members
    WHERE company_id = p_company_id AND user_id = auth.uid()
  );
END;
$$;

CREATE OR REPLACE FUNCTION has_company_role(p_role text)
RETURNS boolean LANGUAGE plpgsql SECURITY DEFINER STABLE AS $$
BEGIN
  RETURN EXISTS (
    SELECT 1 FROM members m
    JOIN employees e ON e.id = m.employee_id
    WHERE m.user_id = auth.uid() AND m.role = p_role
  );
END;
$$;

CREATE OR REPLACE FUNCTION is_manager_of(p_employee_id uuid)
RETURNS boolean LANGUAGE plpgsql SECURITY DEFINER STABLE AS $$
BEGIN
  RETURN EXISTS (
    SELECT 1 FROM employees e
    JOIN members m ON m.employee_id = e.id
    WHERE e.id = p_employee_id
      AND e.manager_id IN (
        SELECT employee_id FROM members WHERE user_id = auth.uid()
      )
  );
END;
$$;
```

RLS policies (NEVER subquery members from a members policy):
- companies: SELECT via is_company_member(id); UPDATE via has_company_role('hr_admin')
- members: SELECT via is_company_member(company_id); INSERT WITH CHECK (is_company_member(company_id)) for invite acceptance
- employees: SELECT via (own row: employee_id IN (SELECT employee_id FROM members WHERE user_id=auth.uid())) OR is_manager_of(id) OR has_company_role('hr_admin'); INSERT/UPDATE via has_company_role('hr_admin')
- leave_requests: SELECT via (employee is own: employee_id IN (SELECT employee_id FROM members WHERE user_id=auth.uid())) OR is_manager_of(employee_id) OR has_company_role('hr_admin'); INSERT via (own employee_id); UPDATE via is_manager_of(employee_id) OR has_company_role('hr_admin')
- documents: SELECT via (employee is own: employee_id IN (SELECT employee_id FROM members WHERE user_id=auth.uid())) OR has_company_role('hr_admin'); INSERT via is_company_member((SELECT company_id FROM employees WHERE id = employee_id))
- audit_log: SELECT via has_company_role('hr_admin'); INSERT via service role only

Also create a SECURITY DEFINER function `create_company(p_name text, p_country_code text) RETURNS uuid` that atomically inserts a companies row AND a members row with role='hr_admin'.

Storage bucket and policies:
Create bucket `employee-documents` (private). Add these storage.objects RLS policies:

```sql
-- Employee reads own documents
CREATE POLICY employee_own_docs ON storage.objects FOR SELECT TO authenticated
USING (
  bucket_id = 'employee-documents'
  AND (storage.foldername(name))[1] IN (
    SELECT employee_id::text FROM members WHERE user_id = auth.uid()
  )
);

-- HR admin reads all documents in their company
CREATE POLICY hr_admin_all_docs ON storage.objects FOR SELECT TO authenticated
USING (
  bucket_id = 'employee-documents'
  AND (storage.foldername(name))[1] IN (
    SELECT e.id::text FROM employees e
    JOIN members m ON m.company_id = e.company_id
    WHERE m.user_id = auth.uid() AND m.role = 'hr_admin'
  )
);

-- Employee uploads to own folder only
CREATE POLICY employee_upload_own ON storage.objects FOR INSERT TO authenticated
WITH CHECK (
  bucket_id = 'employee-documents'
  AND (storage.foldername(name))[1] IN (
    SELECT employee_id::text FROM members WHERE user_id = auth.uid()
  )
);
```

Audit trigger: Create a trigger `audit_documents_select` AFTER SELECT ON documents FOR EACH ROW that calls a function inserting a row into audit_log with action='document_read', entity_type='documents', entity_id=OLD.id.

## Layout

Create `src/layouts/AppLayout.tsx` — top bar with CompanySwitcher dropdown (reads members rows for current user, lists company names) + user menu; left sidebar with nav (Dashboard, Employees, Leave, Documents, Settings). Admin-only: Settings.

Create `src/contexts/CompanyContext.tsx` — stores current company_id from URL `/c/:companyId`, exposes useCompany() hook used by all queries.

Create `src/components/AuthGuard.tsx` — redirects to /login if no session.

Create `src/components/CompanyGuard.tsx` — verifies is_company_member(company_id), redirects to /onboarding or first available company.

Create `src/components/RoleGate.tsx` — accepts allowedRoles: string[] prop, reads current member role from CompanyContext, renders children only if user's role is in allowedRoles, otherwise renders null or a 'Contact HR admin' message.

## Pages and routes

Create these routes:
- `/login` — email+password + Google OAuth
- `/onboarding` — two cards: 'Create company' (calls create_company RPC) and 'Accept invite via token' (reads URL token, verifies, claims pending members row)
- `/c/:companyId` — dashboard: headcount card + pending leave count + expiring documents count; role-aware widgets (employee sees own leave balance; manager sees direct reports' requests; hr_admin sees company-wide)
- `/c/:companyId/employees` — directory list with search by name/email; card view with EmployeeCard per employee; RoleGate: all roles see it but employees see only their own card, managers see direct reports, hr_admin sees all
- `/c/:companyId/employees/:employeeId` — employee profile: basic info + leave history + documents list; RoleGate on salary_band field (hr_admin only)
- `/c/:companyId/leave` — leave list: 'My requests' tab always visible; 'Direct reports' tab for managers; 'All requests' tab for hr_admin; filter by status/type/date
- `/c/:companyId/leave/new` — leave request form: type, starts_on, ends_on (shows working-days count), reason (optional); submits to leave_requests INSERT
- `/c/:companyId/documents` — per-employee document browser (scoped by role); DocumentUploader for the employee's own folder; DocumentList with download (signed URL, 1-hour expiry) and delete
- `/c/:companyId/settings` — company name, leave-type config, HR admins list; hr_admin only via RoleGate

## Components

Create:
- `src/components/EmployeeCard.tsx` — avatar + full_name + role_title + manager name + status badge (active/on_leave/terminated)
- `src/components/LeaveRequestForm.tsx` — shadcn Dialog with type Select, date pickers for starts_on/ends_on, auto-calculated working days, reason textarea
- `src/components/LeaveApprovalCard.tsx` — RoleGate(hr_admin, manager) — shows employee name, leave type, dates, reason, Approve and Reject buttons calling leave_requests UPDATE
- `src/components/DocumentUploader.tsx` — file picker (PDF and image, max 10MB). On select, fetches caller's employee_id from members table (NOT auth.uid() — they are different IDs). Uploads to `employee-documents/{employee_id}/{crypto.randomUUID()}-{filename}`. Inserts a documents row after successful upload.
- `src/components/DocumentList.tsx` — list of documents with filename, category badge, expiry date chip. Download button generates a signed URL (3600s expiry) and opens in new tab. Delete button removes the Storage object and the documents row.

## Styling

Light + dark mode via shadcn ThemeProvider. Primary color: indigo-600 (calm HR feel). Leave status badges: gray (pending) / emerald (approved) / rose (rejected). Employee status: emerald (active) / amber (on_leave) / gray (terminated). Document category color-coded badges. Sticky filter bar on leave page.

Generate the migration first — confirm it includes all 3 SECURITY DEFINER functions, all 6 table RLS policies, the 3 Storage policies, the create_company() RPC, and the audit trigger. Then generate the contexts + guards + layout. Then pages in the order listed.