Build a professional quotation and invoice generation system with PDF export and payment tracking
* Preview is for reference only. Actual results may vary depending on the AI model, variable values, and tools used.
You are a senior full-stack engineer specializing in business management systems for Malaysian SMEs. Build a complete Quotation and Invoice Management System using Next.js 14 App Router, Tailwind CSS, Supabase (Postgres + Auth + RLS), and shadcn/ui. This system will replace manual Word/Excel quotation workflows for a Malaysian business.
## Project Context
Company: {{company_name}}
Address: {{company_address}}
SST Rate: {{sst_rate}}%
Quotation Validity: {{quote_validity_days}} days
The sales team currently creates quotations manually in Word, calculates tax by hand, and prints for signature. This system must let any staff member generate a professional PDF quotation in under 5 minutes, then convert it to an invoice and track payments — all without any accounting background.
## 1. Database Schema (Supabase / PostgreSQL)
Create all tables with complete column definitions, types, constraints, and indexes:
**clients**
- id: uuid PRIMARY KEY DEFAULT gen_random_uuid()
- company_name: text NOT NULL
- contact_person: text
- address: text
- city: text
- state: text (Selangor / KL / Penang / Johor etc.)
- postcode: text
- phone: text
- email: text
- sst_registration_number: text (client's SST number if applicable)
- notes: text
- created_at: timestamptz DEFAULT now()
- Indexes: email, company_name (pg_trgm GIN for fuzzy search)
**products_services**
- id: uuid PRIMARY KEY DEFAULT gen_random_uuid()
- name: text NOT NULL
- description: text
- unit_price: numeric(12,2) NOT NULL DEFAULT 0 (in MYR)
- unit: text DEFAULT 'unit' (pcs / kg / hour / set / month)
- category: text (Printing / Packaging / Service / Consultation)
- is_active: boolean DEFAULT true
- created_at: timestamptz DEFAULT now()
**quotations**
- id: uuid PRIMARY KEY DEFAULT gen_random_uuid()
- quote_number: text UNIQUE NOT NULL (auto-generated: QT-2024-0089)
- client_id: uuid REFERENCES clients(id)
- status: text DEFAULT 'draft' (draft / sent / viewed / accepted / rejected / expired)
- issue_date: date NOT NULL DEFAULT CURRENT_DATE
- valid_until: date NOT NULL (issue_date + {{quote_validity_days}} days)
- subtotal: numeric(12,2) DEFAULT 0 (before tax and discount)
- discount_type: text DEFAULT 'none' (none / percentage / fixed)
- discount_value: numeric(12,2) DEFAULT 0
- discount_amount: numeric(12,2) DEFAULT 0 (computed)
- sst_rate: numeric(5,2) DEFAULT {{sst_rate}}
- sst_amount: numeric(12,2) DEFAULT 0
- grand_total: numeric(12,2) DEFAULT 0
- currency: text DEFAULT 'MYR'
- terms_conditions: text
- notes: text (internal, not printed)
- created_by: uuid REFERENCES auth.users(id)
- created_at / updated_at: timestamptz
- Indexes: client_id, status, issue_date, valid_until
**quotation_items**
- id: uuid PRIMARY KEY DEFAULT gen_random_uuid()
- quotation_id: uuid REFERENCES quotations(id) ON DELETE CASCADE
- product_id: uuid REFERENCES products_services(id) ON DELETE SET NULL (nullable for free-text lines)
- description: text NOT NULL
- quantity: numeric(10,2) NOT NULL DEFAULT 1
- unit: text DEFAULT 'unit'
- unit_price: numeric(12,2) NOT NULL DEFAULT 0
- discount_pct: numeric(5,2) DEFAULT 0 (per-line discount %)
- line_total: numeric(12,2) NOT NULL DEFAULT 0 (auto-computed)
- sort_order: integer DEFAULT 0
- Index: quotation_id
**invoices**
- id: uuid PRIMARY KEY DEFAULT gen_random_uuid()
- invoice_number: text UNIQUE NOT NULL (INV-2024-0042)
- quotation_id: uuid REFERENCES quotations(id) ON DELETE SET NULL
- client_id: uuid REFERENCES clients(id)
- status: text DEFAULT 'unpaid' (unpaid / partial / paid / overdue / cancelled)
- issue_date: date NOT NULL DEFAULT CURRENT_DATE
- due_date: date NOT NULL (issue_date + 30 days default)
- subtotal / discount_type / discount_value / discount_amount / sst_rate / sst_amount / grand_total: same as quotations
- amount_paid: numeric(12,2) DEFAULT 0
- balance_due: numeric(12,2) DEFAULT 0
- currency: text DEFAULT 'MYR'
- terms_conditions: text
- notes: text
- created_by: uuid REFERENCES auth.users(id)
- created_at / updated_at: timestamptz
- Indexes: client_id, status, due_date, quotation_id
**invoice_items**: mirror of quotation_items with invoice_id instead
**payments**
- id: uuid PRIMARY KEY DEFAULT gen_random_uuid()
- invoice_id: uuid REFERENCES invoices(id) ON DELETE CASCADE
- amount: numeric(12,2) NOT NULL
- payment_method: text (Cash / Bank Transfer / Cheque / Online Banking / Credit Card / DuitNow)
- payment_date: date NOT NULL DEFAULT CURRENT_DATE
- reference_number: text (receipt or transfer reference)
- notes: text
- recorded_by: uuid REFERENCES auth.users(id)
- created_at: timestamptz DEFAULT now()
- Indexes: invoice_id, payment_date
**company_settings**
- id: uuid PRIMARY KEY DEFAULT gen_random_uuid()
- company_name: text NOT NULL
- company_address: text
- phone: text
- email: text
- website: text
- logo_url: text (Supabase Storage path)
- sst_registration_number: text
- bank_name: text
- bank_account_number: text
- bank_account_name: text
- default_sst_rate: numeric(5,2) DEFAULT {{sst_rate}}
- default_validity_days: integer DEFAULT {{quote_validity_days}}
- default_payment_terms: integer DEFAULT 30
- default_terms_conditions: text
- quote_number_prefix: text DEFAULT 'QT'
- quote_number_next: integer DEFAULT 1
- invoice_number_prefix: text DEFAULT 'INV'
- invoice_number_next: integer DEFAULT 1
- updated_at: timestamptz DEFAULT now()
Enable Row Level Security on all tables. Authenticated users can read and write their own records. Use `(select auth.uid())` pattern in RLS policies for performance (avoids per-row function calls).
## 2. Core Features
### Quotation Builder
- Two-column layout: left 55% editable form, right 45% sticky live PDF-accurate preview
- Line items: search and select from product catalog (modal with fuzzy search) or type freely
- Per-line fields: description, quantity, unit, unit price, per-line discount %, auto-calculated line total
- Drag-and-drop reorder of line items (use @dnd-kit/sortable)
- Insert section heading rows to group items by category
- Overall discount: choose percentage or fixed MYR amount
- SST toggle: enable/disable, rate editable per quotation (default {{sst_rate}}%)
- Three pre-built terms & conditions templates selectable from dropdown
- Auto-save draft to Supabase every 30 seconds (debounced)
- Top action bar: Save Draft / Mark as Sent / Download PDF / Duplicate / Convert to Invoice / Void
### Quotation Status Workflow
- Status pipeline: Draft → Sent → Viewed → Accepted OR Rejected OR Expired
- Manual status override with confirmation dialog
- Status change history stored in a quotation_status_log table with timestamp and user
- Supabase Edge Function (cron, daily) auto-expires quotes where valid_until < today and status IN ('sent','viewed')
### Professional PDF Generation
- Use @react-pdf/renderer to generate PDF entirely server-side via a Next.js Route Handler at /quotes/[id]/pdf
- PDF layout: company logo (top-left), company name + address + SST number (top-right), horizontal rule, "QUOTATION" title + quote number + dates, client details block, itemized table with columns (No. / Description / Qty / Unit / Unit Price RM / Amount RM), totals block (Subtotal / Discount / SST {{sst_rate}}% / Grand Total), terms & conditions section, bank transfer details footer, page number
- PDF fonts: use embedded Inter or Roboto (PDF-safe)
- All amounts formatted as RM X,XXX.XX; dates as DD/MM/YYYY
- Route handler: GET /api/quotes/[id]/pdf → streams PDF with Content-Disposition: attachment
### Client Address Book
- Full CRUD for client records
- Inline quick-add client during quotation creation (modal, saves and auto-selects)
- Client detail page: company info + all linked quotations + invoices + payment history
- Fuzzy search using pg_trgm or client-side filtering for <=500 clients
### Product & Service Catalog
- Manage default products/services with name, description, default unit price, unit, category
- Used as quick-fill when adding line items to quotations
- Mark items inactive to hide from selector without deleting
### Convert Quotation to Invoice
- Button on accepted/sent quotations: "Convert to Invoice"
- Copies all line items, client, amounts verbatim to a new invoice record
- Generates invoice number from company_settings sequence (INV-YYYY-NNNN) inside a Postgres transaction to prevent race conditions
- Sets quotation status to 'accepted', stores invoice_id back-reference
- Invoice due_date defaults to issue_date + company_settings.default_payment_terms days
### Invoice & Payment Tracking
- Invoice status: Unpaid / Partial / Paid / Overdue / Cancelled
- Payment recording form: amount, date, method, reference number
- Supports multiple partial payments against one invoice
- After each payment INSERT, a Postgres trigger recalculates invoice.amount_paid, balance_due, and updates status to 'partial' or 'paid' automatically
- Overdue detection: Edge Function or trigger sets status='overdue' when due_date < now() and balance_due > 0
- Payment history table on invoice detail page
### Duplicate & Revise
- Duplicate: creates new quotation with new QT number, status=draft, all items copied, issue_date=today
- Revise: creates QT-YYYY-NNNN Rev.1 (appends revision suffix), links to original
### Email Sending
- Integrate Resend API (resend.com)
- Send PDF as attachment with customizable email body
- On send: update quotation/invoice status to 'sent', record sent_at timestamp and recipient email
- Environment variable: RESEND_API_KEY
## 3. UI/UX Specifications
**Color palette**: dark navy nav (#0F172A), white content area, yellow accent (#FCD34D) for primary CTAs, slate text
**Typography**: Inter for Latin, Noto Sans SC for Chinese labels
**Status badges**: Draft=slate / Sent=blue / Accepted=green / Rejected=red / Expired=amber / Overdue=red (invoice)
**Dashboard page** (/dashboard):
- Summary cards: Quotes This Month, Acceptance Rate (%), Outstanding Invoices (RM), Revenue Collected This Month (RM)
- Overdue invoices alert list (red border card)
- Recent 10 quotations table with quick-action buttons
**Quotations list** (/quotes):
- Search by quote number or client name
- Filter by status, date range, client
- Sortable table: Quote No. / Client / Issue Date / Valid Until / Grand Total / Status / Actions
- Bulk actions: mark as sent, export CSV
**Quotation editor** (/quotes/new and /quotes/[id]):
- Left panel: scrollable form sections (Quote Info → Client → Line Items → Totals → Terms)
- Right panel: position:sticky, PDF preview card, scrollable if content overflows
- Preview updates on every keystroke (React state, no re-fetch)
- On small screens: tabs to switch between Form and Preview
**Invoices list and detail**: similar structure to quotations, with additional payment panel
## 4. Business Logic Rules
1. **Auto-numbering**: Read prefix + next_number from company_settings in a transaction, format as PREFIX-YYYY-NNNN (zero-padded to 4 digits), increment next_number atomically using UPDATE ... RETURNING
2. **SST formula**: SST Amount = ROUND((subtotal - discount_amount) * sst_rate / 100, 2); Grand Total = subtotal - discount_amount + sst_amount
3. **Line total formula**: line_total = ROUND(quantity * unit_price * (1 - discount_pct/100), 2)
4. **Currency display**: always prefix RM, use toLocaleString('en-MY', {minimumFractionDigits:2})
5. **Date format**: DD/MM/YYYY in all UI and PDF output
6. **Validity expiry cron**: Supabase Edge Function scheduled daily at 00:05 MYT (UTC+8)
7. **Payment trigger**: after INSERT on payments, recalculate invoice totals and flip status
## 5. Project File Structure
Create files at these exact paths:
- src/app/(dashboard)/page.tsx — dashboard redirect or overview
- src/app/quotes/page.tsx — list
- src/app/quotes/new/page.tsx — new quotation
- src/app/quotes/[id]/page.tsx — edit/view quotation
- src/app/quotes/[id]/pdf/route.ts — PDF generation API route
- src/app/invoices/page.tsx — invoice list
- src/app/invoices/[id]/page.tsx — invoice detail
- src/app/invoices/[id]/payments/route.ts — record payment API
- src/app/clients/page.tsx — client list
- src/app/clients/[id]/page.tsx — client detail
- src/app/products/page.tsx — product catalog
- src/app/settings/page.tsx — company settings
- src/components/quote-editor.tsx — main editor Client Component
- src/components/quote-preview.tsx — live PDF preview Client Component
- src/components/line-items-table.tsx — draggable line items
- src/components/pdf-template.tsx — @react-pdf/renderer document
- src/components/payment-form.tsx — record payment modal
- src/lib/supabase/client.ts, server.ts, admin.ts
- src/lib/number-format.ts — RM formatting helpers
- src/lib/auto-number.ts — quote/invoice number generation
- supabase/migrations/001_initial_schema.sql — full schema
- supabase/functions/expire-quotations/index.ts — daily cron
## 6. Implementation Order
Implement strictly in this sequence and confirm each step before proceeding:
1. Run Supabase migration (all tables, indexes, RLS policies, triggers)
2. Supabase client utilities + TypeScript types from database schema
3. Company settings page (first-run onboarding flow)
4. Client management (list + create + edit)
5. Product/service catalog
6. Quotation editor with live preview (core feature — most time here)
7. PDF generation route handler
8. Quotation list with status management
9. Convert quotation to invoice flow
10. Payment recording and invoice status automation
11. Dashboard with summary metrics
12. Email sending with Resend (optional, implement last)
## 7. Environment Variables Required
Create a .env.local.example with:
- NEXT_PUBLIC_SUPABASE_URL
- NEXT_PUBLIC_SUPABASE_ANON_KEY
- SUPABASE_SERVICE_ROLE_KEY
- RESEND_API_KEY (optional)
- NEXT_PUBLIC_APP_URL
After completing all steps, provide a summary of what was built, how to run locally (npm install, supabase start, npm run dev), and how to deploy to Vercel.