---
title: Financial Data
slug: redshift/financial-data
icon: {"lucideIcon":"Banknote"}
docTags: 
createdAt: 2026-10-01T12:26:52.012Z
---

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.

:::BlockQuote
**All totals can be derived from **`sale`** directly.** Only join down to `treatments` / `sold_service_packages` when you need line-item detail (e.g. per-product revenue).
:::

### `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](docId\:up9_3heKVh32jzLj4BwZH)). 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](docId\:RX3HRkivNEpJwznBBy6dt) before using any of them in a report

:::hint{type="warning"}
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.