How to Build a Expense Tracking with Lovable

1Create an expense tracking schema in Supabase and set up storage.
2
3Tables:
4- expense_categories: id, name, monthly_limit (decimal, nullable), icon (text, emoji)
5- expense_policies: id, category_id (references expense_categories), max_per_transaction (decimal), requires_receipt_above (decimal, e.g. 25.00), created_at
6- expenses: id, user_id (references auth.users), category_id (references expense_categories), merchant_name, amount (decimal), currency (text default 'USD'), expense_date (date), description, receipt_path (text, nullable, Supabase Storage path), status ('draft' | 'submitted' | 'approved' | 'rejected' | 'reimbursed'), policy_violated (bool default false), policy_note (text, nullable), submitted_at, approved_by (references auth.users, nullable), approved_at, rejection_reason (text, nullable), created_at
7- profiles: id (references auth.users), role ('employee' | 'manager' | 'finance'), manager_id (references auth.users, nullable), department
8
9Storage:
10- Create a private bucket named 'receipts'
11- Storage RLS policy: users can INSERT files at path 'receipts/{auth.uid()}/*'. Users can SELECT their own files. Managers can SELECT files from employees in their team (implement via a storage policy joining to profiles).
12
13RLS on expenses:
14- Employees SELECT/INSERT/UPDATE (draft only) their own rows
15- Managers SELECT expenses from employees in their team (manager_id = auth.uid() in profiles)
16- Managers UPDATE status, approved_by, rejection_reason
17- Finance role can SELECT all expenses

1Build an expense submission form at src/pages/NewExpense.tsx.
2
3Requirements:
4- Form fields (react-hook-form + zod):
5  - Category Select (fetch from expense_categories, show icon + name)
6  - Merchant name Input
7  - Amount Input (decimal number)
8  - Currency Select (USD/EUR/GBP)
9  - Expense date DatePicker (default today)
10  - Description Textarea (optional)
11  - Receipt upload: a file Input accepting image/* and application/pdf. Show a preview thumbnail for images.
12- Upload logic when a file is selected:
13  - Call supabase.storage.from('receipts').upload(path, file) where path = `receipts/${userId}/${Date.now()}-${file.name}`
14  - Show a progress indicator during upload
15  - Store the path in form state, not the full URL
16- Submit behavior:
17  - First call the validate-expense Edge Function with category_id and amount
18  - If policy_violated, show a yellow Alert: 'This expense exceeds the $X limit for [category]. It will be flagged for manager review.'
19  - Allow the user to proceed anyway
20  - INSERT the expense with status='submitted' and policy_violated flag
21  - Show a success Toast and navigate to /expenses
22- Add a 'Save Draft' Button that inserts with status='draft'

1// supabase/functions/validate-expense/index.ts
2import { serve } from 'https://deno.land/std@0.168.0/http/server.ts'
3import { createClient } from 'https://esm.sh/@supabase/supabase-js@2'
4
5const corsHeaders = {
6  'Access-Control-Allow-Origin': '*',
7  'Access-Control-Allow-Headers': 'authorization, x-client-info, apikey, content-type',
8  'Content-Type': 'application/json',
9}
10
11serve(async (req: Request) => {
12  if (req.method === 'OPTIONS') return new Response('ok', { headers: corsHeaders })
13
14  try {
15    const supabase = createClient(
16      Deno.env.get('SUPABASE_URL') ?? '',
17      Deno.env.get('SUPABASE_SERVICE_ROLE_KEY') ?? ''
18    )
19
20    const { category_id, amount } = await req.json()
21
22    const { data: policy } = await supabase
23      .from('expense_policies')
24      .select('max_per_transaction, requires_receipt_above, expense_categories(name)')
25      .eq('category_id', category_id)
26      .single()
27
28    if (!policy) {
29      return new Response(JSON.stringify({ violated: false }), { headers: corsHeaders })
30    }
31
32    const violations: string[] = []
33
34    if (policy.max_per_transaction && amount > policy.max_per_transaction) {
35      violations.push(`Exceeds the $${policy.max_per_transaction} per-transaction limit for ${(policy.expense_categories as any)?.name}`)
36    }
37
38    return new Response(JSON.stringify({
39      violated: violations.length > 0,
40      violations,
41      requires_receipt: amount >= (policy.requires_receipt_above ?? 0),
42    }), { headers: corsHeaders })
43  } catch (err) {
44    return new Response(JSON.stringify({ violated: false }), { headers: corsHeaders })
45  }
46})

1Build a manager approval page at src/pages/ApprovalQueue.tsx.
2
3Requirements:
4- Fetch all expenses WHERE status='submitted' and the expense belongs to an employee whose manager_id = current user
5- Display as a list of Cards (not a table — cards allow more detail)
6- Each Card shows:
7  - Employee name and avatar/initials
8  - Merchant name, amount (formatted as currency), category icon + name
9  - Expense date
10  - Description
11  - Policy violation Alert if policy_violated = true (yellow, show policy_note)
12  - Receipt preview: if receipt_path is set, show a thumbnail using a signed URL fetched from supabase.storage.from('receipts').createSignedUrl(path, 3600). Clicking opens the full receipt in a new tab.
13  - Two Buttons: 'Approve' (green) and 'Reject' (destructive outline)
14- Clicking Approve: UPDATE expense status='approved', approved_by=auth.uid(), approved_at=now(). Remove from queue.
15- Clicking Reject: open a Dialog with a required Textarea for rejection_reason. On submit, UPDATE status='rejected', rejection_reason. Remove from queue.
16- Show a badge count of pending items in the page heading
17- Subscribe to Realtime INSERT events on expenses WHERE status='submitted' to show new items without refresh

1Build an expense list page at src/pages/Expenses.tsx.
2
3Requirements:
4- Detect user role from profiles table
5- Employee view: show own expenses only
6- Manager view: show own + team expenses
7- Finance view: show all expenses with employee name column
8- DataTable with columns: Date, Employee (manager/finance only), Category (icon + name), Merchant, Amount (currency formatted), Status Badge (submitted=blue, approved=green, rejected=red, reimbursed=teal, draft=gray), Receipt indicator (paperclip icon if receipt exists), Actions
9- Clicking a row opens a Sheet with full expense details and receipt preview
10- Filter bar: status multi-select, category multi-select, date range Popover, search Input for merchant name
11- Total shown below table: filtered total amount, count of records
12- 'Export CSV' Button: download visible rows as CSV. Columns: date, employee_email, category, merchant, amount, currency, status, description, receipt_url (signed URL valid 7 days). Name file: 'expenses-{from}-{to}.csv'
13- Recharts BarChart below table: spending by category for the selected period (actual vs category monthly_limit if set)