---
title: Stock & Services
slug: redshift/stock-and-services
icon: {"lucideIcon":"Package"}
docTags: 
createdAt: 2026-10-01T12:31:17.992Z
---

This domain covers what clinics sell (products and service packages) and what's on hand to sell (inventory). The relationship between a purchase batch and a sold unit is what makes COGS and expiry tracking possible — see the note at the bottom before writing inventory reports.

### `products` — product catalog

- `category` → `product_categories.id`
- Package sizing: `unit` (receiving size), `sub_unit` (selling size), `formula` (number of subunits per unit — e.g. 50 tablets per bottle)
- `minimum_price`, `recommended_selling_price`, `recommended_markup` — pricing guidance fields
- `is_consumable`, `is_controlled` — flags relevant for controlled-substance and internal-use-only stock
- `optimum_stock`, `minimum_stock`, `days_before_expiry_alert` — reorder/alerting thresholds
- `last_cost`, `latest_reception_note_product_id` — most recent purchase reference
- **Does not carry a quantity-on-hand field** — that lives in `product_stocks`.

### `product_stocks` — current stock on hand

- `product_id` → `products.id`, `clinic_id`
- `stock` = whole units on hand, `sub_stock` = subunits on hand
- This is a **current snapshot**, not a ledger — it tells you what's on hand right now, not the history of how it got there.

### `product_categories` and `service_categories`

Reference tables for grouping products and services respectively. `product_categories.narcotic` flags controlled-substance categories; `is_parent`/`parent_id` support a category hierarchy.

### `service_packages` — service catalog

- `service_id` → `available_services.id`
- `unit_price`, `price`, `visit_type_id`

### `product_reminders`

Not previously documented here. Links a product to a reminder cadence (e.g. "this vaccine triggers a reminder in 12 months") — pairs with `reminder_protocol_usage` on the operational side (see [Operational](docId\:ihfOVSdsrTypRZRuq0DWW)) once that data reaches a patient record.

### The purchase-batch relationship

This is the part most people miss: `reception_notes_products`** and **`reception_notes_products_stocks`** — the actual purchase-order line items and per-batch stock — are internal tables, not exposed as client views.** What you *do* get, indirectly:

- `treatments.nir_product_id` → references the internal `reception_notes_products.id`, giving you a stable pointer to which batch a sold unit came from, even though you can't query the batch table itself.
- `sold_service_package_products.expiration_date` and `.batch_no` → give you expiry visibility on products consumed as part of a bundled service, directly on an exposed view.

If you need batch-level inventory reporting (e.g. FIFO/FEFO mismatch detection, batch-level COGS) beyond what those two exposed columns support, that requires expanded access — raise it with the data team rather than assuming the internal table names will resolve in your Redshift connection.

### `reception_notes` — purchase orders

- `clinic_id`, `supplier_id`, `nir_number`
- `date`, `due_date`, `doc_date` — see [Dates & Timezones](docId\:RX3HRkivNEpJwznBBy6dt) for timezone handling on these
- `status`, `order_type`

This is the header record; the line items (`reception_notes_products`) are internal-only as noted above.