How to Build a Client Invoicing Tool with Lovable

1-- Run in Supabase SQL Editor
2
3CREATE TYPE invoice_status AS ENUM ('draft', 'sent', 'paid', 'overdue', 'cancelled');
4
5CREATE TABLE clients (
6  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
7  user_id UUID REFERENCES auth.users NOT NULL,
8  company_name TEXT NOT NULL,
9  contact_name TEXT,
10  email TEXT NOT NULL,
11  phone TEXT,
12  address TEXT,
13  city TEXT,
14  country TEXT,
15  tax_number TEXT,
16  notes TEXT,
17  created_at TIMESTAMPTZ DEFAULT now()
18);
19
20CREATE TABLE invoices (
21  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
22  user_id UUID REFERENCES auth.users NOT NULL,
23  client_id UUID REFERENCES clients(id) NOT NULL,
24  invoice_number TEXT NOT NULL,
25  status invoice_status DEFAULT 'draft',
26  issue_date DATE NOT NULL DEFAULT CURRENT_DATE,
27  due_date DATE NOT NULL,
28  subtotal DECIMAL(12,2) NOT NULL DEFAULT 0,
29  tax_rate DECIMAL(5,2) NOT NULL DEFAULT 0,
30  tax_amount DECIMAL(12,2) NOT NULL DEFAULT 0,
31  total DECIMAL(12,2) NOT NULL DEFAULT 0,
32  notes TEXT,
33  pdf_storage_path TEXT,
34  sent_at TIMESTAMPTZ,
35  paid_at TIMESTAMPTZ,
36  created_at TIMESTAMPTZ DEFAULT now(),
37  updated_at TIMESTAMPTZ DEFAULT now()
38);
39
40CREATE TABLE line_items (
41  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
42  invoice_id UUID REFERENCES invoices(id) ON DELETE CASCADE NOT NULL,
43  description TEXT NOT NULL,
44  quantity DECIMAL(10,2) NOT NULL DEFAULT 1,
45  unit_price DECIMAL(12,2) NOT NULL,
46  amount DECIMAL(12,2) GENERATED ALWAYS AS (quantity * unit_price) STORED,
47  position INTEGER NOT NULL DEFAULT 0
48);
49
50-- Trigger to update invoice totals when line_items change
51CREATE OR REPLACE FUNCTION update_invoice_totals()
52RETURNS TRIGGER LANGUAGE plpgsql AS $$
53DECLARE
54  v_subtotal DECIMAL(12,2);
55  v_tax_rate DECIMAL(5,2);
56BEGIN
57  SELECT COALESCE(SUM(quantity * unit_price), 0) INTO v_subtotal
58  FROM line_items WHERE invoice_id = COALESCE(NEW.invoice_id, OLD.invoice_id);
59  
60  SELECT tax_rate INTO v_tax_rate FROM invoices
61  WHERE id = COALESCE(NEW.invoice_id, OLD.invoice_id);
62  
63  UPDATE invoices SET
64    subtotal = v_subtotal,
65    tax_amount = ROUND(v_subtotal * v_tax_rate / 100, 2),
66    total = v_subtotal + ROUND(v_subtotal * v_tax_rate / 100, 2),
67    updated_at = now()
68  WHERE id = COALESCE(NEW.invoice_id, OLD.invoice_id);
69  
70  RETURN NEW;
71END;
72$$;
73
74CREATE TRIGGER line_items_changed
75AFTER INSERT OR UPDATE OR DELETE ON line_items
76FOR EACH ROW EXECUTE FUNCTION update_invoice_totals();
77
78-- RLS
79ALTER TABLE clients ENABLE ROW LEVEL SECURITY;
80ALTER TABLE invoices ENABLE ROW LEVEL SECURITY;
81ALTER TABLE line_items ENABLE ROW LEVEL SECURITY;
82
83CREATE POLICY "Users manage own clients" ON clients FOR ALL USING (user_id = auth.uid());
84CREATE POLICY "Users manage own invoices" ON invoices FOR ALL USING (user_id = auth.uid());
85CREATE POLICY "Users manage own line items" ON line_items FOR ALL USING (
86  EXISTS (SELECT 1 FROM invoices WHERE id = invoice_id AND user_id = auth.uid())
87);

1Build the invoice creation page at /invoices/new with a two-column layout:
2
3LEFT COLUMN (form, 55% width):
41. Invoice header fields:
5   - Client select (shadcn Select, fetches from clients table, shows company name + email)
6   - Invoice number (auto-generated: INV-{year}-{sequential number}, editable)
7   - Issue date (shadcn Calendar Popover, default today)
8   - Due date (shadcn Calendar Popover, default 30 days from today)
9   - Tax rate percentage (number input, default 0)
10
112. Line items section with useFieldArray:
12   - Each row: Description (text input, flex-grow), Quantity (number, 80px), Unit Price (number, 120px), Amount (calculated, 100px, read-only), Remove button (X)
13   - "Add Line Item" button adds a new row at the bottom
14   - Minimum 1 line item required (show error if empty on submit)
15   - Amount column = quantity * unit_price calculated in real time
16
173. Totals summary (right-aligned below line items):
18   - Subtotal: sum of all amounts
19   - Tax ({tax_rate}%): subtotal * tax_rate / 100
20   - Total: subtotal + tax (bold, larger text)
21
224. Notes textarea (optional)
235. Submit buttons: "Save as Draft" and "Save and Preview"
24
25Right Column (preview): see next step
26
27Use react-hook-form with zod schema. Validate: client required, at least 1 line item, quantity > 0, unit_price >= 0, due_date after issue_date.

1Build a live invoice preview component that displays in the right column of the invoice form:
2
3Preview layout (styled like a real invoice document, white background, subtle shadow):
41. Header row:
5   - Left: Your business name (bold, large) + address placeholder text
6   - Right: "INVOICE" label (large, primary color) + Invoice Number + Issue/Due dates
7
82. Bill To section:
9   - Client company name (populated from selected client)
10   - Contact name, email, address (populated from client record)
11
123. Line items table:
13   - Column headers: Description, Qty, Unit Price, Amount
14   - One row per line item from the form (live-updating as user types)
15   - Alternating row background: white and gray-50
16
174. Totals section (right-aligned):
18   - Subtotal row
19   - Tax row (hidden if tax_rate = 0)
20   - Bold total row with border-top
21
225. Notes section at bottom (if notes field has content)
236. Footer: payment terms placeholder + "Thank you for your business"
24
25Watch the form values using react-hook-form's watch() function and pass them as props to the preview component. The preview updates on every keystroke without any extra state management.

1// supabase/functions/generate-invoice-pdf/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};
9
10serve(async (req) => {
11  if (req.method === 'OPTIONS') return new Response('ok', { headers: corsHeaders });
12
13  try {
14    const supabase = createClient(
15      Deno.env.get('SUPABASE_URL')!,
16      Deno.env.get('SUPABASE_SERVICE_ROLE_KEY')!
17    );
18
19    const { invoice_id } = await req.json();
20
21    const { data: invoice } = await supabase
22      .from('invoices')
23      .select('*, clients(*), line_items(* order by position)')
24      .eq('id', invoice_id)
25      .single();
26
27    if (!invoice) throw new Error('Invoice not found');
28
29    const lineItemsHtml = invoice.line_items.map((item: Record<string, unknown>) => `
30      <tr style="border-bottom: 1px solid #eee;">
31        <td style="padding: 8px 4px;">${item.description}</td>
32        <td style="padding: 8px 4px; text-align: right;">${item.quantity}</td>
33        <td style="padding: 8px 4px; text-align: right;">$${Number(item.unit_price).toFixed(2)}</td>
34        <td style="padding: 8px 4px; text-align: right;">$${Number(item.amount).toFixed(2)}</td>
35      </tr>
36    `).join('');
37
38    const html = `<!DOCTYPE html><html><head><meta charset="UTF-8">
39      <style>body{font-family:Arial,sans-serif;color:#333;padding:40px;max-width:800px;margin:0 auto;}
40      .header{display:flex;justify-content:space-between;margin-bottom:32px;}
41      table{width:100%;border-collapse:collapse;}
42      th{text-align:left;padding:8px 4px;border-bottom:2px solid #333;font-size:12px;text-transform:uppercase;color:#666;}
43      .totals{margin-top:24px;text-align:right;} .total-row{font-weight:bold;font-size:18px;border-top:2px solid #333;padding-top:8px;}
44      </style></head><body>
45      <div class="header">
46        <div><h2>Your Business Name</h2></div>
47        <div style="text-align:right;"><h1 style="color:#2563eb;margin:0;">INVOICE</h1>
48          <p>Invoice #: ${invoice.invoice_number}</p>
49          <p>Date: ${invoice.issue_date}</p><p>Due: ${invoice.due_date}</p></div>
50      </div>
51      <div><strong>Bill To:</strong><br/>
52        ${invoice.clients.company_name}<br/>
53        ${invoice.clients.contact_name || ''}<br/>
54        ${invoice.clients.email}</div>
55      <table style="margin-top:32px;">
56        <thead><tr><th>Description</th><th style="text-align:right;">Qty</th><th style="text-align:right;">Unit Price</th><th style="text-align:right;">Amount</th></tr></thead>
57        <tbody>${lineItemsHtml}</tbody>
58      </table>
59      <div class="totals">
60        <p>Subtotal: $${Number(invoice.subtotal).toFixed(2)}</p>
61        ${invoice.tax_rate > 0 ? `<p>Tax (${invoice.tax_rate}%): $${Number(invoice.tax_amount).toFixed(2)}</p>` : ''}
62        <p class="total-row">Total: $${Number(invoice.total).toFixed(2)}</p>
63      </div>
64      ${invoice.notes ? `<p style="margin-top:32px;color:#666;">${invoice.notes}</p>` : ''}
65    </body></html>`;
66
67    const pdfResponse = await fetch('https://api.pdfmonkey.io/api/v1/documents', {
68      method: 'POST',
69      headers: {
70        'Authorization': `Bearer ${Deno.env.get('PDFMONKEY_API_KEY')}`,
71        'Content-Type': 'application/json'
72      },
73      body: JSON.stringify({ document: { document_template_id: 'html', payload: { html } } })
74    });
75
76    if (!pdfResponse.ok) {
77      const htmlBlob = new Blob([html], { type: 'text/html' });
78      const storagePath = `invoices/${invoice_id}/invoice-${invoice.invoice_number}.html`;
79      await supabase.storage.from('invoice-exports').upload(storagePath, htmlBlob, { upsert: true });
80      const { data: { publicUrl } } = supabase.storage.from('invoice-exports').getPublicUrl(storagePath);
81      await supabase.from('invoices').update({ pdf_storage_path: storagePath }).eq('id', invoice_id);
82      return new Response(JSON.stringify({ url: publicUrl, format: 'html' }), {
83        headers: { ...corsHeaders, 'Content-Type': 'application/json' }
84      });
85    }
86
87    return new Response(JSON.stringify({ success: true }), {
88      headers: { ...corsHeaders, 'Content-Type': 'application/json' }
89    });
90  } catch (err) {
91    return new Response(JSON.stringify({ error: err.message }), {
92      status: 500, headers: { ...corsHeaders, 'Content-Type': 'application/json' }
93    });
94  }
95});

1Build the invoices list page at /invoices:
2
31. Header with "New Invoice" button and stats row (4 cards):
4   - Total Invoiced (sum of all invoice totals)
5   - Paid (sum of paid invoices this month)
6   - Outstanding (sum of sent invoices not yet paid)
7   - Overdue (count of overdue invoices, red badge if > 0)
8
92. shadcn DataTable with columns:
10   - Invoice Number (monospace, clickable)
11   - Client (company name)
12   - Issue Date
13   - Due Date (red if overdue)
14   - Status (shadcn Badge: draft=gray, sent=blue, paid=green, overdue=red, cancelled=gray)
15   - Total Amount (right-aligned)
16   - Actions (View, Send, Download PDF, Mark as Paid, Delete)
17
183. Overdue detection: on page load, run this query in Supabase:
19   UPDATE invoices SET status = 'overdue' WHERE status = 'sent' AND due_date < CURRENT_DATE AND user_id = {currentUserId}
20   Do this as an RPC call or direct .update() with a filter.
21
224. Send Invoice flow (clicking Send action):
23   - Opens a Dialog confirming: "Send invoice {number} to {client email}?"
24   - On confirm: call a Supabase Edge Function 'send-invoice-email' passing invoice_id
25   - Edge Function sends email via Resend with HTML invoice content
26   - Update invoice status to 'sent', set sent_at = now()
27   - Show success toast: "Invoice sent to {email}"
28
295. Mark as Paid: opens a Dialog with paid date selector. Updates status='paid', paid_at=selected_date.
30
31Add a filter row above the table: Status dropdown, Client select, Date range picker.