Use-case UC0003 – Admin issues an invoice with line items

Initiating actor: Admin

Other actors: Client (the invoice recipient)

Description: The Admin issues an invoice to a client. The Admin selects the client from a list, adds one or more itemized line items, and the system stores the invoice header together with its line items and computed totals.

Scenario

1. Admin chooses "Create Invoice". To populate the client selector, the system lists the clients:

   SELECT id, name, company, email
   FROM project.users
   WHERE role = 'CLIENT'
   ORDER BY name;
   

2. Admin selects the client, enters the invoice details and the line items (name, quantity, unit price). The system computes the subtotal, tax, and total.
3. System inserts the invoice header:

   INSERT INTO project.invoices
     (invoice_number, client_id, amount, subtotal, tax_rate, tax_amount,
      due_date, issue_date, status, payment_terms, notes)
   VALUES
     ('INV-2026-005', 4, 2360.00, 2000.00, 18, 360.00,
      TIMESTAMP '2026-08-01', TIMESTAMP '2026-07-01', 'PENDING', 'Net 14',
      'Website maintenance retainer')
   RETURNING id, invoice_number, subtotal, tax_rate, tax_amount, amount, status;
   

4. System inserts the line items (one row per line):

   INSERT INTO project.invoice_line_items (invoice_id, name, description, quantity, unit_price, sort_order)
   VALUES
     (5, 'Monthly maintenance', 'Retainer - July',  1, 1500.00, 0),
     (5, 'Extra dev hours',     '10h @ 50/h',      10,   50.00, 1)
   RETURNING invoice_id, name, quantity, unit_price, (quantity * unit_price) AS line_total, sort_order;
   

5. System shows the finished invoice with its line items and total for confirmation:

   SELECT i.invoice_number, i.status, u.name AS client,
          json_agg(json_build_object(
            'name', li.name, 'qty', li.quantity,
            'unit_price', li.unit_price, 'line_total', li.quantity * li.unit_price
          ) ORDER BY li.sort_order)        AS line_items,
          SUM(li.quantity * li.unit_price) AS subtotal_from_lines,
          i.subtotal, i.tax_amount,
          i.amount                         AS invoice_total
   FROM project.invoices i
   JOIN project.users u               ON u.id = i.client_id
   JOIN project.invoice_line_items li ON li.invoice_id = i.id
   WHERE i.id = 1
   GROUP BY i.invoice_number, i.status, u.name, i.subtotal, i.tax_amount, i.amount;
   

The invoice header and line items are inserted in one transaction so an invoice is never stored without its lines. The final SELECT confirms the stored total matches the sum of the line items plus tax.