Database Reference

PostgreSQL, 214 live tables across 11 domains, reconstructed from the full migration history (0001–0225). Every table, column, primary/foreign key and CHECK constraint value documented here was read directly from the live schema’s own migration files - nothing was written from general ERP knowledge. Where something couldn’t be verified, it was left out rather than guessed.

Dropped/renamed tables, kept out of this reference: price_list_group_prices, item_variant_attributes, item_variant_attribute_values, item_variances, sticker_settings, pos_expenses (all dropped). notification_templates was renamed to notification_templates_deprecated and still exists, historical only.

Read this before writing any query

These are the conventions that hold almost everywhere in the schema - the single most valuable thing on this page, and the source of most wrong queries if skipped.

1. business_id is the tenant key. Always filter on it. Row-Level Security exists but only binds the restricted infinityportal_reports role, not the main app connection - it will not save you.
2. Documents are cancelled, never deleted. A status = 'cancelled' row still exists. Filtering on payment_status alone can inflate figures - e.g. a POS-cancelled A/R invoice has status='cancelled' AND payment_status='unpaid', so exclude cancelled explicitly: AND status <> 'cancelled'.
3. status vocabularies differ between document types. draft/posted/closed/cancelled on sales/finance documents is not the same set as open/closed/cancelled on requests, or initiated/posted/cancelled on A/P invoices. Check the specific table’s own CHECK constraint, don’t assume.
4. Document numbers are unique per business only - UNIQUE (business_id, document_no). Never join on document_no alone.
5. Line-level consumption counters track partial fulfilment. A source line carries a running counter (ordered_quantity, received_quantity, invoiced_quantity, returned_quantity, credited_quantity) plus row_status (open/closed, sometimes cancelled) - this is how “partially received” or “partially invoiced” is represented, not a separate flag.
6. Money, quantity and rate columns have consistent types: money is numeric(14,2) (older tables numeric(12,2)), quantities numeric(14,3), tax rates numeric(6,3), percentages numeric(5,2).
7. net_amount vs line_total on purchasing lines: net_amount is tax-exclusive (what G/L and costing use); line_total is tax-inclusive. Sales lines only ever have line_total.
8. Header totals are persisted, not derived. The cascade is items_subtotal → header_discount_amount → taxable_value (post-discount, pre-tax base) → tax_total → charges/fee amounts → grand_total. Don’t recompute from lines - the header-discount cascade won’t reproduce exactly.
9. reference_type/reference_id pairs are polymorphic with no FK. Found on stock_movements, journal_entries, item_batches, item_serials, batch_reservations, gift_vouchers, loyalty_point_transactions, and change_money_transactions - join by matching reference_type to the target table yourself.
10. Stock on hand has no denormalized column. It’s SUM(stock_movements.quantity) grouped by item_id (+ warehouse_id); quantities are signed.
11. G/L account resolution for an item follows items.gl_accounts_by: 'item_group' → item_groups.*_account_id; 'warehouse' → warehouses.*_account_id; 'item_level' → items.*_account_id. These are mutually exclusive, not a cascade. gl_account_determination_defaults is the per-business fallback used only where the chosen source leaves an account NULL.
12. There is no users.name column. Display name is first_name || ' ' || last_name.
13. Multi-currency amount columns are additive. Every pre-existing amount column stays in base currency; foreign_* columns and currency_id/exchange_rate are NULL/0 on a base-currency document.
14. Two differently-purposed numbering tables exist. document_sequences is the legacy counter (still used for batch_number and pos_terminal); document_numbering_series (+ _years/_periods) is the live numbering engine everything else uses.

The 11 domains

When you’re ready to write a real report, the Query Cookbook has worked, tested SQL for the questions people ask most often.