Migrating Invoice History

Overview#

Migrating invoice history is optional but strongly recommended. Clients expect to see past invoices in their portal, and your accounting records should be continuous.

Invoices reference client records, so clients must be imported before invoices.

Exporting from WHMCS#

You need data from two WHMCS tables: tblinvoices (invoice headers) and tblinvoiceitems (line items).

-- Invoice headers
SELECT id, userid, status, date, duedate, datepaid,
       subtotal, tax, total, notes
FROM tblinvoices
ORDER BY id;

-- Invoice line items
SELECT id, invoiceid, type, description, amount, taxed
FROM tblinvoiceitems
ORDER BY invoiceid, id;

Export each as a CSV file.

Amounts Are Stored in Cents#

Commerce stores all monetary values in the smallest currency unit (cents for USD, pence for GBP, etc.). WHMCS stores values as decimals.

Convert during import:

Multiply every monetary value by 100 and store as an integer.

Setting the Invoice Number Sequence#

Before importing any invoices, configure Commerce's invoice counter to start after WHMCS's last invoice number. This prevents duplicate invoice numbers.

If WHMCS's last invoice ID was 4821, set Commerce to start at 4822:

php artisan tinker
>>> DB::table('settings')->where('key', 'invoice_next_number')->update(['value' => 4822]);

Or update via Admin → Settings → Billing → Invoice Number Sequence.

[!IMPORTANT] Set the invoice number sequence before importing historical invoices, and again before Commerce generates its first live invoice. If you import historical invoices after Commerce has already generated invoices, you risk number collisions.

Importing via CSV Tool#

1. Go to Admin → Invoices → Import CSV.
2. Upload the invoice headers CSV.
3. Map columns to Commerce fields.
4. Upload the invoice line items CSV in the next step.
5. Review the import summary and check for errors.

Paid vs Unpaid Invoices#

Map WHMCS invoice statuses to Commerce statuses:

Set paid_at to the datepaid value from WHMCS for all paid invoices. Leave paid_at as null for unpaid.

Migrating Payment Records#

WHMCS transaction records are in tbltransactions. These map to Commerce's payments table.

SELECT id, invoiceid, userid, gateway, date,
       amountin, amountout, fees, transid
FROM tbltransactions
WHERE amountin > 0
ORDER BY id;

For each transaction, create a corresponding payment record linked to the matching invoice ID. Convert amountin to cents (multiply by 100).

Handling Currency#

If you operate with multiple currencies, ensure each invoice record has the correct currency_code populated. Commerce does not inherit currency from the client record on imported invoices — you must include it explicitly.

[!TIP] After importing invoices, spot-check 10–20 records by comparing the WHMCS admin view of those invoices against Commerce's. Verify totals, status, due dates, and line item descriptions.