Problem
API doesn't show quote→invoice relationships even though the data exists in the history table. Makes it impossible to track quote lifecycle or build automation workflows.
Solution
Add linked_invoice_id to quotes API and linked_quote_id to invoices API via LEFT JOIN.
Code Changes
For /api/v1/quotes/read.php - Change both queries from: SELECT * FROM quotes WHERE ...
To: SELECT q.*, h.history_invoice_id as linked_invoice_id FROM quotes q LEFT JOIN history h ON h.history_quote_id = q.quote_id AND h.history_invoice_id != 0 WHERE q.quote_id ...
For /api/v1/invoices/read.php - Change both queries from: SELECT * FROM invoices WHERE ...
To: SELECT i.*, h.history_quote_id as linked_quote_id FROM invoices i LEFT JOIN history h ON h.history_invoice_id = i.invoice_id AND h.history_quote_id != 0 WHERE i.invoice_id ...
Note: When using table aliases (quotes q, invoices i), you must prefix all column references with the alias throughout the entire query. This includes WHERE clauses, ORDER BY, and any other column references. For example: WHERE q.quote_id instead of WHERE quote_id.
Result
Quotes return: "linked_invoice_id": "456" (or null)
Invoices return: "linked_quote_id": "123" (or null)
Why This Works
Backward compatible (just adds fields)
No schema changes needed
Safe LEFT JOIN (quotes without invoices still work)
Enables quote→invoice→payment tracking
Tested on dev instance successfully
Use Cases
Track when approved quotes become paid invoices
Build n8n/Zapier workflows for quote lifecycle
Financial reporting (conversion rates, time-to-payment)
Customer service (reference original quote on invoice calls)