Query Cookbook

Every query on this page follows the conventions - filtered by business_id, excluding cancelled documents, and using the real column names documented in the domain pages. Replace :business_id with a real business UUID before running.

Sales by branch, over a date range

SELECT b.name AS branch_name,
       COUNT(*) AS invoice_count,
       SUM(ai.grand_total) AS total_sales
FROM ar_invoices ai
JOIN branches b ON b.id = ai.branch_id
WHERE ai.business_id = :business_id
  AND ai.status IN ('posted', 'closed')
  AND ai.invoice_date BETWEEN :from_date AND :to_date
GROUP BY b.name
ORDER BY total_sales DESC;

status IN ('posted', 'closed') is deliberate, not <> 'cancelled' - a 'draft' invoice hasn’t happened yet and shouldn’t count as a sale either.

Stock on hand by item and warehouse

SELECT i.code, i.name, w.name AS warehouse_name,
       SUM(sm.quantity) AS quantity_on_hand
FROM stock_movements sm
JOIN items i ON i.id = sm.item_id
JOIN warehouses w ON w.id = sm.warehouse_id
WHERE sm.business_id = :business_id
GROUP BY i.code, i.name, w.name
HAVING SUM(sm.quantity) <> 0
ORDER BY i.code;

There’s no denormalized on-hand column - stock_movements.quantity is signed, so a plain SUM() is the correct on-hand figure. The HAVING clause drops items with zero net movement (never stocked, or fully depleted) to keep the result relevant.

A/R aging (unpaid/partial invoices by how overdue they are)

SELECT bp.name AS customer_name,
       ai.document_no,
       ai.invoice_date,
       ai.due_date,
       ai.grand_total,
       ai.paid_amount,
       (ai.grand_total - ai.paid_amount) AS balance_due,
       GREATEST(0, CURRENT_DATE - ai.due_date) AS days_overdue
FROM ar_invoices ai
JOIN business_partners bp ON bp.id = ai.customer_id
WHERE ai.business_id = :business_id
  AND ai.status IN ('posted', 'closed')
  AND ai.payment_status IN ('unpaid', 'partial')
ORDER BY days_overdue DESC;

Bucket it in your reporting layer (CASE WHEN days_overdue > 90 THEN '90+' ...) rather than in SQL if you need standard aging buckets - keeping the raw days_overdue here makes the query reusable for other groupings too.

POS voids/cancels by cashier, last 30 days

SELECT COALESCE(TRIM(u.first_name || ' ' || u.last_name), 'Unknown') AS cashier_name,
       pve.event_type,
       COUNT(*) AS event_count,
       SUM(pve.amount) AS total_amount
FROM pos_void_events pve
LEFT JOIN users u ON u.id = pve.cashier_user_id
WHERE pve.business_id = :business_id
  AND pve.created_at >= now() - interval '30 days'
GROUP BY cashier_name, pve.event_type
ORDER BY total_amount DESC;

To find only the loss-prevention-flagged events (cash was already entered before the void/cancel), add AND pve.tendered_amount IS NOT NULL - see POS and INFI AI Assistant for what that column means.

A/P Invoice 3-way match exceptions

Compares an A/P Invoice’s lines against the GRN (and, through that, the PO) they were copied from, flagging any quantity or price mismatch - exactly the check INFI’s check_ap_invoice_3way_match tool runs.

SELECT ai.document_no,
       i.name AS item_name,
       ail.quantity AS invoiced_qty,
       ail.unit_cost AS invoiced_cost,
       grnl.quantity AS received_qty,
       grnl.unit_cost AS received_cost,
       pol.quantity AS ordered_qty,
       pol.unit_price AS ordered_price
FROM ap_invoices ai
JOIN ap_invoice_lines ail ON ail.ap_invoice_id = ai.id
JOIN items i ON i.id = ail.item_id
LEFT JOIN goods_receipt_note_lines grnl ON grnl.id = ail.source_grn_line_id
LEFT JOIN purchase_order_lines pol ON pol.id = grnl.source_po_line_id
WHERE ai.business_id = :business_id
  AND ai.id = :ap_invoice_id
  AND (
    ail.quantity <> COALESCE(grnl.quantity, ail.quantity)
    OR ABS(ail.unit_cost - COALESCE(grnl.unit_cost, ail.unit_cost)) > 0.01
    OR (pol.unit_price IS NOT NULL AND ABS(ail.unit_cost - pol.unit_price) > 0.01)
  );

An A/P invoice line with no source_grn_line_id is an expense line with nothing to match - the LEFT JOIN means it simply won’t trigger any of the mismatch conditions, rather than needing to be filtered out explicitly.