Financial Data
Everything financial hangs off sale (singular — not sales). A sale accumulates line items (products, service packages), gets paid via one or more charges, and those charges get allocated to the sale via payments.
sale — the transaction
Key columns:
- customer_id → users.id — not client_id. This is the most common mistake when joining this table.
- clinic_id, vet_id
- total, subtotal, discount, tax_amount — headline sale amounts
- products_discount_amount, services_discount_amount, global_discount_amount — discount broken out by where it applied
- type — 0 = counter sale, 1 = record (SOAP) sale, 2 = consolidated sale (omit these to avoid double-counting — a consolidated sale re-aggregates other sales)
- status — 0 = no sale, 1 = pending, 2 = paid, 3 = partial, 4 = overdraft
- edit_state — 1 = open, 2 = ready for checkout, 3 = closed. taxable_at can only be updated by invoice generation once edit_state = 3.
- taxable_at — set when the sale is created and the first line item is added; updated when the sale closes or an invoice is generated. Use this for revenue-recognition-style reporting.
- closed_at — set when the sale closes or an invoice generates. Pair with taxable_at for an immutable "as of" sale value.
- checkout_at, first_line_item_added_at, hidden_at — additional lifecycle timestamps not previously documented here.
treatments — sold products (line items)
- sale_id → sale.id
- vet_id — user assigned to the line entry
- product_id → products.id
- nir_product_id → reception_notes_products.id (internal table — see Stock & Services). This is how a specific sold unit ties back to the purchase batch it came from, which is what makes batch-level COGS possible.
- cogs — cost of goods sold for the line, already computed
- total, total_after_global_discount, tax_amount — line-level financials, mirroring the discount/tax breakdown structure on sale
sold_service_packages — sold services
- sale_id → sale.id
- vet_id, service_package_id → service_packages.id
- Mirrors the same discount/tax/total column pattern as treatments
sold_service_package_products — products bundled into a sold service
- sold_service_package_id → sold_service_packages.id
- product_id → products.id, nir_product_id → reception_notes_products.id
- Carries its own expiration_date and batch_no — useful if you need expiry visibility on products consumed as part of a service, not just products sold directly.
charges — payment received
- sale_id → sale.id
- customer_id → users.id
- amount, amount_refunded, payments_amount
- type — 0 none, 1 cash, 2 card, 3 bank transfer, 4 (not in use), 5 online payment, 6 check, 7 other, 8 client credit, 9 care credit, 10 auto-paid from owner-change process
- card_brand, card_last4 — card detail when type = 2
- status, provider_account_id, provider_response — payment provider integration fields
- made_at vs date vs created_at — three different timestamps on this table; see Dates & Timezones before using any of them in a report
Prior internal reference material (Power BI payment-subtype work) refers to a charges.payment_subtype column with values SAVED_CARD / TERMINAL / MANUAL / ONLINE. That column does not appear in the current schema export this page was built from. If your query needs it, confirm with the data team whether it's present in your specific client view before relying on it — this may be a newer column not yet reflected in the schema extract, or view-specific.
payments — payment allocation
- charge_id → charges.id, sale_id → sale.id
- One charge can be split across multiple sales; payments is the allocation record, charges is the money actually received.
- amount, amount_refunded, amount_reversed
new_invoice — invoice documents
- sale_id → sale.id
- series, number — invoice numbering
- total, date, issued_at
- efactura_id, efactura_status — Romanian e-invoicing integration fields (relevant if you have Romanian clinics)
predefined_discounts and sale_discount_mapping
Not previously documented here.
- predefined_discounts — clinic-level discount templates: name, discount, discount_type, split further into discount_products / discount_services with their own types. apply_on_cogs flags whether the discount reduces the COGS basis.
- sale_discount_mapping — a thin join table linking sale_id to discount_id (→ predefined_discounts.id) when a predefined discount was applied to a sale.
What's still not exposed
reception_notes_products and reception_notes_products_stocks (the actual purchase-batch and per-batch stock tables) are internal tables, not client-facing views. If you need batch-level detail beyond what treatments.nir_product_id and sold_service_package_products.expiration_date/batch_no already give you, that's an access-expansion conversation, not something you can currently query directly.