Stock & Services
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) 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 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.