Infinity Portal
Docs menu

Sales & A/R

Read Conventions first.

Chain: Sales Quotation → Sales Order → A/R Invoice, and Sales Return Request → Sales Return → A/R Credit Memo (a Sales Return may also be copied directly from a posted A/R Invoice, skipping the request).

All six header tables share an identical column set (deliberate clones of purchase_orders’ shape). To avoid repeating it six times, the shared shapes are listed once and only the deltas given per table.

Shared sales-header shape

  • id - PK; business_id; document_no - UNIQUE (business_id, document_no)
  • branch_id NOT NULL; warehouse_id NOT NULL (nullable on ar_invoices only)
  • customer_id → business_partners NOT NULL
  • customer_name, customer_address, customer_tax_id, customer_contact_person - snapshots
  • a date column (name varies), due_date
  • payment_term_id; price_list_id
  • line_type DEFAULT ‘item’ - CHECK IN (‘item’,‘service’)
  • header_discount_type CHECK IN (‘percentage’,‘fixed’); header_discount_value numeric(14,2)
  • convenience_fee_id; sscl_liable boolean; sscl_amount numeric(14,2)
  • Totals, all numeric(14,2) DEFAULT 0: items_subtotal, header_discount_amount, taxable_value, tax_total, charges_total, charges_tax_amount, convenience_fee_amount, convenience_fee_tax_amount, grand_total
  • remarks; status DEFAULT ‘draft’ - CHECK IN (‘draft’,‘posted’,‘closed’,‘cancelled’)
  • posted_at/posted_by, cancelled_at/cancelled_by, created_by, created_at

Shared sales-line shape

id, parent FK CASCADE; item_id NULL; description - CHECK (item_id IS NOT NULL OR description <> ‘’); quantity numeric(14,3) NOT NULL; uom_id; unit_price numeric(14,2); discount_type/discount_value; tax_group_id/tax_component_id - CHECK: never both set; tax_amount; line_total; a source_*_line_id RESTRICT FK; a consumption counter; row_status CHECK IN (‘open’,‘closed’); created_at

Shared charges shape (*_charges tables)

id, parent FK CASCADE, description NOT NULL, amount; is_taxable DEFAULT false; tax_group_id/tax_component_id – CHECK: not both; tax_amount; created_at

sales_quotations / lines / charges

Shared sales-header shape. Date column: quotation_date.

  • sales_quotation_lines - shared sales-line shape; counter ordered_quantity DEFAULT 0; no source FK (chain head)
  • sales_quotation_charges - shared charges shape; parent sales_quotation_id

sales_orders / lines / charges

Shared sales-header shape. Date column: order_date. Extra: source_quotation_id → sales_quotations NULL; delivery_date.

  • sales_order_lines - shared sales-line shape, plus source_quotation_line_id RESTRICT, invoiced_quantity DEFAULT 0, delivery_date
  • sales_order_charges - shared charges shape; parent sales_order_id

ar_invoices

The single most-joined table in the schema - it’s also what POS sales write. Shared sales-header shape, plus:

  • warehouse_id - nullable (only needed at “Mark Delivered” time)
  • invoice_date
  • document_subtype DEFAULT ‘invoice’ - CHECK IN (‘invoice’,‘reserve’). A 'reserve' invoice posts Dr AR / Cr Deferred Revenue at Post, then Dr Deferred Revenue + COGS / Cr Revenue + Inventory plus a stock_movements row at Mark Delivered. A plain 'invoice' is a status-flip.
  • status - CHECK IN (‘draft’,‘pending_approval’,‘posted’,‘closed’,‘cancelled’)
  • source_sales_order_id NULL
  • delivered_at, delivered_by
  • paid_amount DEFAULT 0; payment_status CHECK IN (‘unpaid’,‘partial’,‘paid’)
  • Multi-currency: currency_id, exchange_rate, foreign_grand_total, foreign_paid_amount
  • Notification snapshots: customer_mobile, customer_phone, customer_email DEFAULT ‘’
  • Delivery: is_delivery boolean, delivery_partner_id, delivery_city, waybill_no, delivery_charge_paid_by_customer
  • POS: terminal_id NULL, cashier_user_id NULL, cancel_reason - a POS-cancelled bill becomes a real status='cancelled' invoice row
  • share_token uuid NOT NULL DEFAULT gen_random_uuid() - UNIQUE index; unauthenticated public view link, deliberately not the sequential document number
  • Indexes: (business_id, invoice_date), (customer_id), (branch_id)

ar_invoice_lines

Shared sales-line shape, plus: source_sales_order_line_id RESTRICT; returned_quantity DEFAULT 0; row_status CHECK IN (‘open’,‘closed’,‘cancelled’) (‘cancelled’ distinguishes a voided parent from a fully-processed line); unit_cost NULL (captured for COGS); voucher_code DEFAULT ‘’ (scanned gift-voucher code, persisted because Create and Post are separate requests); gift_voucher_expiry_date NULL; cost_center_id. Index (ar_invoice_id).

ar_invoice_charges

Shared charges shape; parent ar_invoice_id. Credited to accounting_settings.charges_income_account_id.

Sales returns chain

  • sales_return_requests - shared header shape, date column request_date
  • sales_return_request_lines - shared line shape + source_ar_invoice_line_id RESTRICT, returned_quantity
  • sales_return_request_charges - shared charges shape
  • sales_returns - shared header shape, date column return_date. Extra: source_sales_return_request_id NULL; source_ar_invoice_id NULL (direct-from-invoice path); terminal_id NULL (tags a POS Exchange’s standalone return with its origin terminal)
  • sales_return_lines - shared line shape, plus source_return_request_line_id RESTRICT and source_ar_invoice_line_id RESTRICT - CHECK: at most one of the two source columns is set; credited_quantity DEFAULT 0
  • sales_return_charges - shared charges shape

ar_credit_memos / lines / charges

Shared header shape, date column memo_date. Extra: source_sales_return_id NULL; remaining_balance NULL – set to grand_total the moment the memo posts, decremented each time it is redeemed as a POS payment (a decrementing balance, like change_money_balance, not gift vouchers’ all-or-nothing model). Index (business_id, memo_date).

  • ar_credit_memo_lines - shared line shape, plus source_sales_return_line_id RESTRICT and cost_center_id; no consumption counter/row_status (end of chain)
  • ar_credit_memo_charges - shared charges shape

Recurring invoices

  • recurring_ar_invoice_templates - id, business_id, name, is_active DEFAULT true; branch_id NOT NULL, warehouse_id NULL, customer_id NOT NULL; document_subtype CHECK IN (‘invoice’,‘reserve’); payment_term_id, price_list_id; line_type CHECK IN (‘item’,‘service’); header_discount_type/header_discount_value; remarks; frequency - CHECK IN (‘daily’,‘weekly’,‘monthly’,‘yearly’); interval_count DEFAULT 1 CHECK > 0; start_date NOT NULL, end_date NULL, next_run_date NOT NULL, last_generated_at; created_by, created_at, updated_at; index (business_id, is_active, next_run_date)
  • recurring_ar_invoice_template_lines - id, template_id CASCADE, item_id NOT NULL, description; quantity CHECK > 0; unit_price; discount_type/discount_value; tax_group_id, tax_component_id
  • recurring_ar_invoice_runs - audit link + idempotency guard. id, template_id CASCADE, ar_invoice_id NOT NULL; scheduled_for NOT NULL, generated_at

Deliveries

  • deliveries - handover of one or more A/R invoices to a courier. id, business_id, document_no UNIQUE (business_id, document_no); delivery_partner_id NOT NULL; status DEFAULT ‘pending’ CHECK IN (‘pending’,‘out_for_delivery’,‘cancelled’); notes, handed_over_at, handed_over_by, created_by, created_at
  • delivery_lines - id, delivery_id CASCADE, ar_invoice_id NOT NULL - UNIQUE (ar_invoice_id): an invoice can be on at most one delivery

Dunning

  • dunning_levels - escalation levels for overdue receivables. id, business_id, name UNIQUE (business_id, name); min_days_overdue NOT NULL UNIQUE (business_id, min_days_overdue); min_amount DEFAULT 0 (second gate); letter_text, created_at
  • dunning_customer_state - dunning is stateful per customer, a level can only advance one step per run. id, business_id, customer_id - UNIQUE (business_id, customer_id); current_dunning_level_id NULL; last_run_date, updated_at