# Dava India ERP — Database Schema Reference

> Derived from `database/migrations/*.php` (365 files) and `Modules/*/Database/Migrations/*.php`
> (Accounting, AssetManagement, Connector, Essentials, Spreadsheet, Superadmin, SupportTicket).
> This is GITCS's ERP, built from scratch and run as a multi-tenant SaaS for **Dava India**, an
> Indian pharmacy chain. Generated by reading every `create_..._table` migration plus every later
> `add_.../modify_...` migration that touches the same table, so column lists reflect the
> **current, accumulated** shape of each table, not just its original definition.
>
> Last compiled: 2026-07-30.

## How to read this document

- **Multi-tenancy**: almost every table carries a `business_id` foreign key (cascade delete). In
  this fork, one `business` row generally = one physical store or warehouse — it is common for a
  single legal chain to run **thousands of `business` rows** (see "Franchise / multi-store
  architecture" below), not one business with many locations.
- **Soft deletes**: many master-data tables (`contacts`, `products`, `categories`, `brands`,
  `accounts`, `expense_categories`, `business_locations`, `users`, …) use Laravel `SoftDeletes`
  (`deleted_at`).
- **The `transactions` table is polymorphic**: purchases, sales, stock transfers, stock
  adjustments, expenses, returns, quotations, orders, requisitions, payroll, and warehouse
  dispatches are all rows in one `transactions` table, discriminated by a `type` column (and
  `status`/`sub_type`/`sub_status` for finer states). There is **no** dedicated
  `stock_adjustments` or `stock_transfers` table — see the Stock Operations section.
- Tables/columns added in **2026** migrations are Dava-India-specific customizations layered on
  top of the core generic ERP (franchise catalog sync, batch/expiry/QC/recall tracking, warehouse bin
  locations, GST e-Invoice/e-Way Bill, doctor/prescription tracking, drug composition data,
  damage/loss support tickets, auto-PO/TPM replenishment). These are called out throughout and
  summarized in the final section.

---

## 1. Core / Business Setup / Users / Permissions / System

### `business`
`database/migrations/2017_07_05_073658_create_business_table.php` + ~40 later migrations.

The tenant root — in this fork, one `business` = one store or warehouse. Virtually every other
table hangs off `business_id`.

**Base columns**: `id`, `name`, `currency_id` (FK `currencies`), `start_date`, `tax_number_1`/
`tax_label_1` (required), `tax_number_2`/`tax_label_2` (nullable), `default_profit_percent`,
`owner_id` (FK `users`, cascade), `time_zone` (default `Asia/Kolkata`), `fy_start_month`,
`accounting_method` enum(`fifo`,`lifo`,`avco`), `default_sales_discount`, `sell_price_tax`
enum(`includes`,`excludes`), `logo`, `sku_prefix`, `enable_tooltip`.

**Accumulated settings, grouped thematically:**
| Group | Columns |
|---|---|
| Tax | `default_sales_tax` (FK `tax_rates`), `enable_inline_tax`, `currency_symbol_placement` |
| Purchase / multi-currency | `purchase_in_diff_currency`, `purchase_currency_id`, `p_exchange_rate` |
| POS | `pos_settings` (JSON text), `keyboard_shortcuts`, `item_addition_method`, `enable_editing_product_from_purchase`, `sales_cmsn_agnt` enum, `weighing_scale_setting` |
| Invoice/print/doc | `ref_no_prefixes`, `date_format`/`time_format`, `theme_color`, `enabled_modules` |
| Expiry & batch | `enable_product_expiry`, `expiry_type` enum(`add_expiry`,`add_manufacturing`), `on_product_expiry` enum(`keep_selling`,`stop_selling`,`auto_delete`) + `stop_selling_before`, `enable_lot_number` |
| Catalog toggles | `enable_brand`, `enable_category`, `enable_sub_category`, `enable_price_tax`, `enable_purchase_status`, `default_unit`, `enable_racks` |
| Comms | `email_settings`, `sms_settings` |
| Reward points | `enable_rp`, `rp_name`, `amount_for_unit_rp`, `min_order_total_for_rp`, `max_rp_per_order`, `redeem_amount_per_unit_rp`, `min_order_total_for_redeem`, `min_redeem_point`, `max_redeem_point`, `rp_expiry_period`/`type` |
| Sub-units / labels / generic bag | `enable_sub_units`, `custom_labels`, **`common_settings`** (catch-all JSON bag — newer boolean flags like `enable_purchase_order` are stored as keys here rather than as real columns) |
| Custom codes | `code_label_1`/`code_1`, `code_label_2`/`code_2` |
| Precision | `currency_precision`, `quantity_precision` |
| Superadmin | `created_by`, `is_active` |

**2026 pharmacy/chain-specific additions:**
- `sell_return_period_days` — per-store sale-return window (NULL = no limit)
- `enable_damage_loss_tracking` (bool)
- `store_unique_number` (unique string) — manually assigned store code
- `auto_po_frequency_days` — store→supplier auto-PO cadence
- `is_supplier_warehouse` (bool) + `common_supplier_id` — bridges the "warehouse-as-supplier" model to `contacts.supplier_business_id`
- `support_ticket_tat_hours` (default 48h) — defined in `Modules/SupportTicket`
- `min_max_lookback_days`, `min_max_recompute_days` — configurable window/cadence for auto stock min/max recompute
- `tpm_po_frequency_days` — warehouse→Third-Party-Manufacturer auto-PO cadence
- **Drug licenses** — originally single `drug_license_no`/`expiry`, later **split** into `drug_license_20_21_no`/`expiry` (Form 20/21, retail), `drug_license_20b_21b_no`/`expiry` (Form 20B/21B, wholesale), plus `trade_license_no`/`expiry`, `shop_establishment_license_no`/`expiry`, `fssai_license_no`/`expiry`
- Data-seed-only migrations (no schema change): `enable_pharmacy_batch_expiry_for_stores` (turns on lot/expiry tracking + switches invoice `design` to `pharmacy` for stores), `enable_purchase_order_for_all_businesses` (backfills `common_settings.enable_purchase_order=1`)

### `business_locations`
`2017_12_25_122822_create_business_locations_table.php` + 12 later migrations.

Physical outlets/branches of a `business` (usually one row per tenant in this fork's 1-store-per-business model). Columns: `id`, `business_id` (FK cascade), `name`, `landmark`, `country`, `state`, `city`, `zip_code`, `mobile`, `alternate_number`, `email`, plus: `invoice_scheme_id`/`invoice_layout_id` (+ a second **sale-specific** pair `sale_invoice_scheme_id`/`sale_invoice_layout_id`), `print_receipt_on_invoice`, `receipt_printer_type` enum, `printer_id`, `website`, `custom_field1..4`, `location_id` (external code string), `selling_price_group_id`, `default_payment_accounts`, `is_active`, `featured_products`.

### `currencies`
`2017_07_05_071953_create_currencies_table.php` — `id`, `country`, `currency`, `code`, `symbol`, `thousand_separator`, `decimal_separator`. Referenced by `business.currency_id`/`purchase_currency_id`. `2026_04_14_000001_add_tunisian_dinar_currency.php` is a data-only seed (adds a TND row), not a schema change.

### `users`
`2014_10_12_000000_create_users_table.php` + 18 later migrations.

Staff, superadmins, supplier-portal logins, CRM contacts. `business_id` is **nullable** (superadmin/pre-created users may not yet belong to a business).

**Base**: `id`, `surname`, `first_name`, `last_name`, `username`, `email`, `password`, `language`, soft-deletes.
**Tenancy/role**: `business_id`, `user_type` (default `user`, indexed — distinguishes e.g. `user_supplier` portal logins), `status` enum(`active`,`inactive`,`terminated`).
**Commission**: `is_cmmsn_agnt`, `cmmsn_percent`, `contact_no`, `address`.
**Contact access scoping**: `selected_contacts` (bool) — gates `Contact` `view_own` scopes together with `user_contact_access`.
**Profile (2019 batch)**: `dob`, `marital_status`, `blood_group`, `contact_number`, social links, `permanent_address`, `current_address`, `guardian_name`, `custom_field_1..4`, `bank_details`, `id_proof_name/number`, `gender`.
**Login control**: `allow_login` (bool, default 1).
**Other**: `max_sales_discount_percent`, `alt_number`, `family_number`, `crm_contact_id` (FK `contacts`), `available_at`/`paused_at` (service-staff timer), `is_enable_service_staff_pin`/`service_staff_pin`.

**2026 chain-specific**:
- Unique indexes on `username`/`email` **dropped**, replaced with plain indexes (`2026_07_16_164841`) — SoftDeletes previously blocked re-creating a user with a reused username/email; uniqueness now enforced app-side scoped to non-deleted rows.
- `pre_create_role` (string, nullable) — role name to auto-assign when a superadmin-pre-created user is later attached to a new business.
- `common_supplier_id` (nullable int) — links a `user_type='user_supplier'` portal login to its master supplier `Contact`, resolving per-store supplier clones via `contacts.common_supplier_id`.

### Spatie permission tables
`2017_07_26_083429_create_permission_tables.php`
- **`permissions`**: `id`, `name`, `guard_name` — global, not business-scoped.
- **`roles`**: `id`, `name`, `guard_name`, **`business_id`** (FK, cascade, non-nullable), `is_default` — roles ARE tenant-scoped; each business gets its own copy of role names (e.g. "Cashier#5").
- **`model_has_permissions`** / **`model_has_roles`**: morph pivots (`model_id`/`model_type`) assigning permissions/roles directly to a model (usually `User`).
- **`role_has_permissions`**: pivot linking roles to their permission set.

### `system`
`2018_02_21_105329_create_system_table.php` (+ primary-key fix). Global key-value store for app/module metadata (`db_version`, `default_business_active_status`, per-module `*_version` markers). Not business-scoped. Columns: `id`, `key`, `value`.

### `password_resets`, `sessions`, `oauth_*`
Standard Laravel/Passport tables, essentially unmodified: `password_resets` (email/token), `sessions` (id/user_id/payload), `oauth_auth_codes`, `oauth_access_tokens`, `oauth_refresh_tokens`, `oauth_clients` (+ `provider` column added 2023), `oauth_personal_access_clients`. Back the API/module OAuth ecosystem.

### Core accounting tables (base app, distinct from `Modules/Accounting`)
- **`accounts`** (`2018_09_04_155900`): `id`, `business_id`, `name`, `account_number`, `account_type_id` (FK `account_types`, replacing an earlier enum column), `note`, `created_by`, `is_closed`, `account_details`, soft-deletes. A bank/cash/capital account used for fund transfers/deposits.
- **`account_types`** (`2019_10_18_155633`): `id`, `name`, `parent_account_type_id` (self-ref), `business_id` — hierarchical account-type tree.
- **`account_transactions`** (`2018_09_10_152703`): `id`, `account_id`, `type` enum(`debit`,`credit`), `sub_type` enum(`opening_balance`,`fund_transfer`,`deposit`), `amount`, `reff_no`, `operation_date`, `created_by`, `transaction_id` (links to a sale/purchase/expense), `transaction_payment_id`, `transfer_transaction_id` (self-ref, fund transfers), `note`, soft-deletes — the ledger of movement in/out of an `accounts` row.
- **`tax_rates`** (`2017_07_26_110000`): `id`, `business_id`, `name`, `amount`, `is_tax_group`, `created_by`. Referenced by `business.default_sales_tax`.
- **`group_sub_taxes`** (`2017_11_20_051930`): pure pivot (`group_tax_id`, `tax_id`, both FK `tax_rates`) — composes a group tax rate (e.g. CGST+SGST) from component tax rates.

### `activity_log`
`2019_03_12_120336` (+3 later migrations) — `spatie/laravel-activitylog` package table: `id`, `log_name`, `description`, `subject_id`/`subject_type` (morph), `causer_id`/`causer_type` (morph), `properties` (JSON old/new attrs). Additions: `business_id` (tenant scoping, backfilled from causer), `event` (created/updated/deleted), `batch_uuid` (groups a batched operation).

### Adjacent inventory-planning tables (business-setup-adjacent)
- **`movement_tag_configs`** (`2026_07_21_100000`) — per business/location config (`tag_code`, `tag_name`, `min_monthly_sales`, `max_monthly_sales`, `avg_days_for_min_stock`, `max_stock_buffer_percent`, `sort_order`) feeding the min/max stock auto-recompute engine.
- **`manufacturers`** / **`divisions`** (`2026_07_21_300000`) — business-scoped lookups wired onto `products` (see §3).

---

## 2. Contacts + Pharmacy Master Data

### `contacts`
`2017_07_27_075706_create_contacts_table.php` + 20+ later migrations. Universal party table for customers, suppliers, "both", and leads — evolved into the backbone of a **multi-tenant franchise contact network** with three overlapping bridging schemes.

**Base**: `id`, `business_id` (FK cascade), `type` (indexed enum-like string: `customer`/`supplier`/`both`/`lead`), `supplier_business_name`, `name`, `tax_number`, `city`/`state`/`country`/`landmark`, `mobile`/`landline`/`alternate_number`, `pay_term_number`/`pay_term_type`, `created_by`, `is_default` (marks the auto-seeded Walk-In Customer), soft-deletes.

**Accumulated**: `contact_id` (reference no.), `customer_group_id`, `email`, `custom_field1..10`, `credit_limit`, `contact_status` (active/inactive), `shipping_address`, `position`, `prefix`/`first_name`/`middle_name`/`last_name`/`address_line_2`/`zip_code`/`dob` (`landmark`→renamed `address_line_1`), `balance` (running ledger), `type` widened to free string, `shipping_custom_field_details` (array-cast), `is_export`/`export_custom_field_1..6`, `contact_type` (secondary classification), `land_mark`/`street_name`/`building_number`/`additional_number` (extended int'l address fields).

**2026 multi-tenant bridging (three coexisting schemes):**
1. **Universal customers** — `is_global` (bool, chain-wide visibility) + `source_business_id` (originating business) + `master_contact_id` (self-ref dedup pointer: NULL=canonical, non-null=hidden clone kept for FK integrity). Backfill migrations (`backfill_universal_customers`, `dedup_walk_in_customers`) collapsed all per-store "Walk-In Customer" rows chain-wide and re-pointed historical `transactions.contact_id` to the surviving master.
2. **Supplier network** — `common_supplier_id` (per-business clone → master supplier contact) + `supplier_business_id` (master supplier contact → its own warehouse `business` row, bridging the legacy contact-clone model to a "supplier = its own warehouse business" model) + `is_manufacturer` (flags a supplier clone living inside a warehouse business as a login-less Third-Party Manufacturer) + `users.common_supplier_id` (supplier portal login → master contact).
3. **Store-as-customer** — `store_business_id` (a linked retail store represented as a customer contact inside its regional warehouse business); deliberately kept business-private/never global (`scopeExcludeForeignStoreCustomers()`).

**Model** (`app/Contact.php`): `SoftDeletes`, `belongsToMany User` via `user_contact_access`, self-referential via `master_contact_id`/`common_supplier_id`, helper factories `cloneToBusinessAsSupplier()`, `createStoreCustomer()`.

### `customer_groups`
`2018_03_26_165350` + `2021_02_23_122043`. Per-business pricing/discount tiers: `id`, `business_id`, `name`, `amount` (percentage), `created_by`; later `price_calculation_type` (percentage/selling-price-group) and `selling_price_group_id`. Referenced from `contacts.customer_group_id` and `transactions.customer_group_id` (snapshot at sale time).

### `user_contact_access` (pivot)
`2018_10_31_175627` — `id`, `user_id`, `contact_id` (no FKs). Restricts a user to explicitly assigned contacts when `users.selected_contacts=1`.

### `doctors` — pharmacy-specific
`2026_07_27_000001_create_doctors_table.php` + 2 companions. `id`, `business_id` (FK cascade), `doctor_id` (reference no.), `name`, `registration_number` (medical council reg., required), `gender`, `dob`, `contact_number`, `email`, `address`, `department`, `created_by`, soft-deletes. `transactions.doctor_id` (nullable, indexed, no FK) attributes a sale/prescription to the referring doctor. Permissions `doctor.view/create/update/delete` seeded to all roles.

### Salt / Composition / Composition-Salt — pharmacy drug-formula model
- **`salts`** (`2026_07_15_000007`): a single active pharmaceutical ingredient — `id`, `business_id`, `name` (e.g. "Paracetamol"), `created_by` (set-null).
- **`compositions`** (`2026_07_15_000008`): a named formula/combination of salts (e.g. "Paracetamol + Ibuprofen") — `id`, `business_id`, `name` (denormalized, auto-built by concatenating salt names), `created_by`.
- **`composition_salt`** (`2026_07_15_000009`): pivot — `composition_id` (FK `compositions` cascade), `salt_id` (FK `salts` cascade). Many-to-many: a composition is made of several salts; a salt appears in many compositions.
- **`products.composition_id`** (`2026_07_15_000010`): nullable FK → `compositions` (set-null) — tags a sellable drug with its formula, enabling generic-substitution queries ("find all brands with this composition").

### Division / Manufacturer + pharmacy flags
`2026_07_21_300000_add_manufacturer_division_and_pharmacy_flags.php` creates two lookup tables and adds flags:
- **`manufacturers`**: `id`, `business_id`, `name`, `description`, `created_by`, soft-deletes — the drug's manufacturing company (later extended, `2026_08_02_100000`, with `email`/`mobile`/`address`/`supplier_contact_id` bridging to a login-less SUPPLIER contact per manufacturer per warehouse).
- **`divisions`**: same shape as `manufacturers` — a manufacturer's internal product division/brand line (e.g. a large pharma company's separate field-force divisions); a product belongs to one division.
- **`products`** gains `manufacturer_id`, `division_id` (both set-null FK), and tri-state capability flags `can_be_purchased`/`can_be_stored`/`can_be_sold` (default 1) + `product_tags`.
- **`variations`** gains `ptr`/`pts` decimal(22,4) — Price-to-Retailer / Price-to-Stockist, standard Indian pharma trade-pricing tiers distinct from MRP/default sell price.

### `consignments` — pharmacy dispatch trips
`2026_07_29_120000_create_consignments_table.php`. Groups multiple warehouse→store sell transactions into one dispatch trip/manifest: `id`, `business_id`, `ref_no`, `driver_name`, `vehicle_no`, `transport_mode`, `distance_km`, `status` enum(`draft`,`dispatched`,`delivered`,`cancelled`), `dispatched_at`/`delivered_at`, `pod_receiver`/`pod_note` (proof of delivery), `created_by`. `transactions.consignment_id` links individual sells to their trip.

### `warranties` (core generic feature, not pharmacy-specific)
`2019_12_02_105025` — `id`, `name`, `business_id`, `description`, `duration`, `duration_type` enum(`days`,`months`,`years`). Adds `products.warranty_id` and pivot `sell_line_warranties` (`sell_line_id`, `warranty_id`).

### `temperature_logs` — cold-chain monitoring
`2026_07_28_130000_create_temperature_logs_table.php`. Periodic temperature readings per warehouse zone against an acceptable range: `id`, `business_id`, `location_id`, `transaction_id` (optional link to a GRN/dispatch), `zone` (e.g. "Cold Room 1"), `log_type` enum(`storage`,`inbound`,`outbound`), `temperature`, `min_allowed`/`max_allowed` (defaults 2/8 °C — standard pharma cold-chain range), `is_breach` (indexed), `recorded_at`, `recorded_by`, `notes`.

### Master Product / Master Variation concept (Modules/Superadmin — cross-reference)
See §7 Superadmin for full detail. In brief: `products.is_master_product`/`master_product_id` and `variations.master_variation_id` implement a franchise-wide master catalog by self-reference within the same tables, replacing older separate `superadmin_products`/`superadmin_product_variations` tables (dropped 2026-07-15).

---

## 3. Products / Catalog / Variations / Warehouse Stock Location

### `products`
`2017_08_08_115903_create_products_table.php` + 20+ later migrations.

**Base**: `id`, `name`, `business_id`, `type` enum(`single`,`variable`, later widened to add `modifier`,`combo`), `unit_id` (FK `units`), `brand_id`/`category_id`/`sub_category_id` (nullable FK), `tax`/`tax_type`, `enable_stock`, `alert_quantity`, `sku`, `barcode_type` (widened enum: C39, C128, EAN13, EAN8, UPCA, UPCE), `created_by`.

**Accumulated**: `expiry_period`/`expiry_period_type`, `enable_sr_no` (serial-number tracking), `weight`, `image`, `product_custom_field1..20`, `product_description`, `is_inactive`, `not_for_selling`, `sub_unit_ids` (whitelist of alt units), `secondary_unit_id`, `preparation_time_in_minutes` (service-staff timer).

**2026 pharmacy/franchise additions:**
- `composition_id` — drug-composition link (§2)
- `is_master_product`, `master_product_id` (self-ref, no FK) — franchise master-catalog sync (§7)
- `default_sell_sub_unit_id`, `default_purchase_sub_unit_id` — pre-selected UoM at POS vs. purchase entry
- `sell_sub_unit_ids`, `purchase_sub_unit_ids` — asymmetric per-context unit whitelist (e.g. sell in Strip/Tablet, purchase in Baby Box)
- `hsn_code`, `drug_schedule`, `prescription_required`, `dosage_form`, `storage_condition` — India pharma/GST fields
- `manufacturer_id`, `division_id`, `can_be_purchased`/`can_be_stored`/`can_be_sold`, `product_tags` (§2)

### `categories`
`2017_08_04_071038` + `2019_11_25_160340`. `id`, `name`, `business_id`, `short_code`, `parent_id` (self-ref, no FK — category/sub-category tree), `created_by`, soft-deletes; later `category_type` (backfilled `'product'`), `description`, `slug`; polymorphic pivot **`categorizables`** (`category_id` + `categorizable_id`/`categorizable_type`) lets categories attach beyond products.

### `brands`
`2017_07_23_113209` — simple lookup: `id`, `business_id`, `name`, `description`, `created_by`, soft-deletes.

### `units`
`2017_07_26_122313` + several later migrations. Base: `id`, `business_id`, `actual_name`, `short_name`, `allow_decimal`, `created_by`, soft-deletes. `base_unit_id`/`base_unit_multiplier` (multi-unit hierarchy, e.g. Box→Strip→Tablet). **2026**: `intermediate_unit_id` (3-tier chain, e.g. Baby Box→Strip→Tablet); `rename_stripe_unit_to_strip` — pure data-fix correcting a "Stripe"→"Strip" typo.

### `variations`
`2017_08_10_061216` + additions. Base: `id`, `name`, `product_id` (FK cascade), `sub_sku`, `product_variation_id` (FK cascade), `default_purchase_price`/`dpp_inc_tax`/`profit_percent`/`default_sell_price`/`sell_price_inc_tax`, soft-deletes. Additions: `combo_variations` (combo product components), `mrp_inc_tax`, `ptr`/`pts` (pharma pricing), `master_variation_id` (self-ref, no FK — franchise sync).

### `product_variations`
`2017_08_10_061146` — the "variation group" row per product (e.g. "Color"; a dummy row for `type=single` products): `id`, `name`, `product_id`, `is_dummy` (default 1).

### `variation_templates` / `variation_value_templates`
`2017_08_09_061616` / `061638` — reusable variation definitions for building variable products. `variation_templates`: `id`, `name`, `business_id`. `variation_value_templates`: `id`, `name`, `variation_template_id` (FK cascade).

### `variation_location_details` (VLD) — per-location stock
`2017_12_25_163227` + `2026_07_21_100001` + `2026_07_29_100000` (bin_id). **The central per-(product, variation, location) stock-on-hand row.** Base: `id`, `product_id`, `product_variation_id`, `variation_id` (FK `variations`), `location_id` (FK `business_locations`), `qty_available` (default 0). Additions: `min_quantity`/`max_quantity` (reorder thresholds), `movement_tag` (points at `movement_tag_configs.tag_code`), `min_max_source` enum(`manual`,`auto`), `last_auto_update_at`, `bin_id` (primary put-away/pick bin pointer — `qty_available` stays authoritative; a separate per-bin×batch sub-ledger exists at `warehouse_bin_stock`).

### `barcodes`
`2017_11_21_064540` — barcode **label layout** template (not per-product barcode values): `id`, `name`, `description`, dimension/margin floats, `stickers_in_one_row`/`sheet`, `is_default`, `is_continuous`, `business_id` (nullable — allows system defaults).

### `product_racks`
`2018_04_17_160845` — free-text rack assignment predating the structured bin system: `id`, `business_id`, `location_id`, `product_id` (plain ints), `rack` (string). Paired with `business.enable_racks`.

### `product_locations`
`2019_09_12_105616` — pivot restricting products to specific locations: `product_id`, `location_id` (no id/FK/timestamps).

### `selling_price_groups` / `variation_group_prices`
`2018_09_06_114438` / `154057` + `2023_04_28_130247`. `selling_price_groups`: `id`, `name`, `description`, `business_id`, soft-deletes — named alt pricing tiers (e.g. Wholesale). `variation_group_prices`: `id`, `variation_id` (FK cascade), `price_group_id` (FK cascade), `price_inc_tax`; later `price_type` (fixed/percentage).

### `discounts` / `discount_variations`
`2019_02_19_103118`, `2020_09_22_121639`, `2021_09_01_063110`. `discounts`: `id`, `name`, `business_id`, `brand_id`/`category_id`/`location_id` (scope), `priority`, `discount_type`, `discount_amount`, `starts_at`/`ends_at`, `is_active`, `applicable_in_cg`, `spg` (selling-price-group applicability, replacing an earlier boolean). `discount_variations`: pivot (`discount_id`, `variation_id`).

### Warehouse bin/rack location system — pharmacy-specific
`2026_07_29_100000_create_warehouse_bins_table.php` (greenfield) + `2026_07_30_120000_pharma_bin_locations.php` (hierarchical rebuild):

- **`warehouse_bins`**: originally flat (`id`, `business_id`, `location_id`, `code`, `zone` enum(`receiving`,`pick`,`bulk`,`quarantine`), `description`, `capacity`, `is_active`); rebuilt into a hierarchical location tree — `parent_id` (self-ref: zone→aisle→rack→shelf→bin, only `location_type='bin'` rows hold stock), `location_type`, `temp_class` (`cold_2_8`/`cool_8_15`/`crt_15_25`/`ambient` — cold-chain classification), `humidity_controlled`, `status` (`available`/`occupied`/`blocked`/`quarantine`/`damaged`). `zone` widened from ENUM to VARCHAR to admit more pharma zones (`rejected`,`expired`,`returns`,`narcotic`,`dispatch`). Gated by `business.enable_bin_locations`.
- **`warehouse_bin_stock`**: true per-bin × per-batch quantity sub-ledger — `id`, `business_id`, `bin_id`, `purchase_line_id` (the batch/lot), `product_id`, `variation_id`, `qty` (unique on bin+purchase_line). Enables FEFO (first-expiry-first-out) picking to the exact bin holding the earliest-expiry lot, and bin-level cycle-count reconciliation. `variation_location_details.qty_available` remains the authoritative total quantity.

### `movement_tag_configs`
`2026_07_21_100000` — business/location-scoped sales-velocity classification config (`tag_code`, `tag_name`, `min_monthly_sales`/`max_monthly_sales`, `avg_days_for_min_stock`, `max_stock_buffer_percent`, `sort_order`) driving the auto min/max stock computation referenced from VLD.

### `stock_counts` / `stock_count_lines` — cycle counting (see also §4)
`2026_07_29_130000_create_stock_counts_table.php`. `stock_counts`: `id`, `business_id`, `location_id`, `ref_no`, `status` enum(`open`,`posted`,`cancelled`), `notes`, `counted_by`, `posted_at`. `stock_count_lines`: `id`, `stock_count_id`, `product_id`, `variation_id`, `expected_qty` (frozen system qty), `counted_qty` (physical count), `variance`. Standalone header/line tables, but **posting** fabricates `stock_adjustment` (shortages) or `opening_stock` (overages) rows in `transactions` — see §4 for the full mechanics.

### Data-seed/toggle migrations (not schema)
`enable_batch_expiry_for_warehouses`, `enable_pharmacy_batch_expiry_for_stores`, `seed_warehouse_permissions` (13 granular `warehouse.*` permissions), `seed_warehouse_intelligence_permissions` (`warehouse.procurement_intel`, `warehouse.ai_insights`).

---

## 4. Purchases + Sales/Transactions + Cash Registers

### `transactions` — the polymorphic core
`2017_08_19_054827_create_transactions_table.php` + 60+ later migrations.

**`type`** (widened over time from a small enum to effectively a string): `purchase`, `sell`, `expense`, `stock_adjustment`, `sell_transfer`, `purchase_transfer`, `opening_stock`, `sell_return`, `opening_balance`, `purchase_return`, `payroll`, `expense_refund`, `sales_order`, `purchase_order`, `purchase_requisition`.
**`status`**: `received`, `pending`, `ordered`, `draft`, `final`, `in_transit`, `completed` (historic `completed` stock-transfers were later folded into `final`/`received`).
**`payment_status`**: `paid`, `due`. **`sub_type`**/**`sub_status`**: free strings for finer states (e.g. `quotation`). **`adjustment_type`**: `normal`/`abnormal` (stock adjustment only).

**Core FKs**: `business_id`, `location_id` (`business_locations`), `contact_id` (`contacts`, nullable for expenses), `customer_group_id`, `tax_id` (`tax_rates`), `created_by` (`users`), `selling_price_group_id`, `commission_agent`, `expense_category_id`/`expense_sub_category_id`, `res_table_id`/`res_waiter_id` (restaurant), `types_of_service_id`, **`doctor_id`** (pharmacy, 2026).

**Financials**: `total_before_tax`, `tax_amount`, `discount_type`/`amount`, shipping fields, `final_total`, `round_off_amount`, additional-expense key/value pairs, `exchange_rate`, reward-points fields (`rp_earned`/`redeemed`/`redeemed_amount`), export fields.

**Lifecycle/linking**: `transfer_parent_id` (stock transfers), `return_parent_id` (returns), recurring-invoice fields (`is_recurring`, `recur_interval`, `recur_parent_id`, …), `subscription_no`, `opening_stock_product_id`, `purchase_order_ids`/`sales_order_ids`/`purchase_requisition_ids` (JSON-cast arrays), `pay_term_number`/`type`, `invoice_token`, `is_quotation`, `is_direct_sale`, `is_suspend`, `is_created_from_api`, `source`, `is_kitchen_order`, `document`, `custom_field_1..4`.

**2026 pharmacy/multi-store additions:**
- `is_auto_generated` — flags auto-PO-engine-created docs
- `warehouse_dispatched_at` — idempotency guard for warehouse stock-decrement on PO dispatch
- `tpm_emailed_at` — idempotency guard for warehouse→TPM auto-PO emails
- `doctor_id` — prescriber link on sales
- `source_store_po_id` — links a warehouse `sales_order` 1:1 back to the store `purchase_order` it mirrors
- `irn`, `ack_no`, `ack_date`, `signed_qr`, `ewaybill_no`, `ewaybill_date`, `einvoice_status`, `einvoice_error` — GST e-Invoice/e-Way Bill fields
- `consignment_id` — links a sell to its dispatch trip (`consignments`, §2)
- `transporter_name`, `transporter_id`, `vehicle_no`, `transport_mode`, `transport_distance_km` — e-way-bill transport detail

### `purchase_lines`
`2017_08_31_073533_create_purchase_lines_table.php` + 15+ later migrations. One row per product/variation purchased (also reused for stock-transfer-in and opening-stock lines).

**Base**: `transaction_id`, `product_id`, `variation_id`, `quantity`, `purchase_price`, `purchase_price_inc_tax`, `item_tax`, `tax_id`.
**Accumulated**: `mfg_date`/`exp_date`, `lot_number` (batch/expiry, gated by `business.enable_lot_number`), `quantity_sold`, `quantity_adjusted`, `pp_without_discount`/`discount_percent`, `quantity_returned`, `mfg_quantity_used`, `sub_unit_id`, `secondary_unit_quantity`, `purchase_order_line_id`/`po_quantity_purchased` (links a GRN line to its PO line + cumulative received), `purchase_requisition_line_id`.

**2026 pharmacy-specific damage/loss, QC, recall additions:**
- **Damage/loss** (`database/migrations/2026_07_21_100000`): `quantity_damaged`, `quantity_lost`, `damage_loss_reason`, `damage_loss_note`
- **PO-level damage/loss** (`Modules/SupportTicket/.../2026_07_23_100000`, a *different* migration from the one above): `po_quantity_damaged`, `po_quantity_lost` (cumulative, mirrors `po_quantity_purchased`), `support_ticket_id` — traces a resend GRN line to the ticket it fulfills
- **Recall**: `recalled_at`, `recall_reason` — blocks further dispatch of a flagged batch
- **QC**: `qc_status` (`quarantine`/`passed`/`rejected`, indexed), `quantity_rejected`, `qc_reason`, `qc_by`, `qc_at` — new warehouse GRN batches start `quarantine` and cannot dispatch until passed
- **Batch traceability**: `source_purchase_line_id` — stamps a store's GRN line with the warehouse's own batch id, chaining manufacturer→warehouse→store for recalls

### `transaction_payments`
`2017_10_15_064638` + 15+ later migrations. Records payments/refunds against a transaction (supports split payments). Base: `transaction_id`, `amount`, `method` (enum, widened to add `custom_pay_1/2/3`), card-detail columns, `cheque_number`, `bank_account_number`, `note`. Additions: `paid_on`, `created_by`, `payment_for` (contact id) + `parent_id` (self-ref, split/change payments; `transaction_id` made nullable), `is_return`, `payment_ref_no`, `transaction_no`, `account_id` (FK accounting `accounts`), `business_id`, `document`, `paid_through_link`/`gateway` (online payment links), `payment_type` (credit/debit).

### `transaction_sell_lines`
`2017_11_20_063603` + 15+ later migrations. One row per product/variation sold. Base: `transaction_id`, `product_id`, `variation_id`, `quantity`, `unit_price`, `unit_price_inc_tax`, `item_tax`, `tax_id`. Additions: `sell_line_note`, `parent_sell_line_id` (self-ref, modifier/combo children) + `children_type`, `lot_no_line_id` (links to the specific purchase batch sold from), `line_discount_type`/`amount`, `unit_price_before_discount`, `quantity_returned`, `discount_id` (FK `discounts`), `res_service_staff_id`/`res_line_order_status`, `sub_unit_id`/`secondary_unit_quantity`, `so_line_id`/`so_quantity_invoiced` (sales-order fulfillment tracking).

### `transaction_sell_lines_purchase_lines` — FIFO/batch-consumption pivot
`2018_02_12_113640` + additions. Not a business document — bookkeeping for "which purchase batch fulfilled this sale/adjustment": `sell_line_id` (nullable), `stock_adjustment_line_id` (nullable — alternative consumer), `purchase_line_id` (batch consumed), `quantity`. `qty_returned` added later (restores batch availability on return). One sell line can span multiple rows if FIFO pulls from several batches. `id` widened `INT`→`BIGINT` in 2024 (table had grown very large).

### `cash_registers` / `cash_register_transactions`
`2018_01_30_181442` / `2018_01_31_125836` + additions. `cash_registers`: `business_id`, `user_id` (cashier), `status` enum(`open`,`close`), `closed_at`, `closing_amount`, `total_card_slips`/`cheques`, `closing_note`; later `location_id` (multi-store), `denominations` (text). `cash_register_transactions`: `cash_register_id`, `amount`, `pay_method` (widened enum), `type` enum(`debit`,`credit`), `transaction_type` (loosened from enum to free string in 2021), `transaction_id`; **`transaction_payment_id`** (2026, FK `transaction_payments` cascade) — ties a cash movement to the exact split-payment row that generated it.

### `cash_denominations`
`2022_04_21_083327` — polymorphic denomination breakdown: `business_id`, `amount`, `total_count`, `morphs('model')` (attaches to any owning record, e.g. a register closing).

### `reference_counts`
`2018_05_22_123527` — per-business sequence generator: `ref_type` (purchase/sell/purchase_order/expense/…), `ref_count` (last used), `business_id`. Produces sequential invoice/reference numbers independent of the auto-increment PK.

### Purchase requisition
`2022_07_13_114307_create_purchase_requisition_related_columns.php` — despite the name, creates **no new tables**: adds `purchase_lines.purchase_requisition_line_id` and `transactions.purchase_requisition_ids`. A purchase requisition is just a `transactions` row with `type='purchase_requisition'`.

### Auto-PO / TPM settings & seed migrations (no new tables)
`enable_purchase_order_for_all_businesses` (backfills a JSON settings flag), `add_auto_po_settings_to_business` (`business.auto_po_frequency_days`, NULL inherits from the lowest-id "template" business), `add_tpm_po_frequency_to_business` (`business.tpm_po_frequency_days`, no inheritance — opt-in per warehouse), `seed_tpm_purchase_order_template` (seeds a `tpm_purchase_order` row into `notification_templates` for every business).

---

## 5. Stock Operations

Stock adjustments and stock transfers are **rows in `transactions`**, not dedicated header tables — confirmed by grepping the full migration history for `stock_adjustments`/`stock_transfers`.

### Stock adjustments
`2018_02_19_121537_stock_adjustment_move_to_transaction_table.php` adds `'stock_adjustment'` to `transactions.type` and **creates `stock_adjustment_lines`**: `id`, `transaction_id` (FK cascade), `product_id`, `variation_id`, `quantity`, `unit_price` (nullable, "last purchase unit price"). Also adds `transactions.adjustment_type` enum(`normal`,`abnormal`) and `transactions.total_amount_recovered` (e.g. recovering cost from an employee for damaged stock). Note: this same migration creates a throwaway `stock_adjustments` table with one dummy column purely to immediately rename it to `stock_adjustments_temp` — a dead artifact of the refactor, unused by the app.

Later `stock_adjustment_lines` changes: `purchase_line_id` added then renamed to `removed_purchase_line` (superseded by the `transaction_sell_lines_purchase_lines` pivot for FIFO mapping — the renamed column and `lot_no_line_id` remain as vestigial columns); `lot_no_line_id` added.

FIFO/COGS linkage for adjustments reuses `transaction_sell_lines_purchase_lines.stock_adjustment_line_id` (see §4) — `TransactionUtil::mapPurchaseSell(..., 'stock_adjustment')` keeps lot consumption/COGS consistent regardless of whether stock left via a sale or an adjustment.

### Stock transfers
`2018_02_27_170232_modify_transactions_table_for_stock_transfer.php` adds `'sell_transfer'`, `'purchase_transfer'`, `'opening_stock'` to `transactions.type`. A transfer is modeled as **two linked transaction rows** — an outbound `sell_transfer` at the source location paired with an inbound `purchase_transfer` at the destination, joined by `transactions.transfer_parent_id`. `2020_09_07_171059` backfills historical `completed` status to `final` (sell_transfer leg) / `received` (purchase_transfer leg) — the status vocabulary diverges by leg.

### Stock counts — cycle counting (new, 2026, standalone tables that bridge into the transaction ledger)
`stock_counts`/`stock_count_lines` (schema in §3) are standalone header/line tables, but **posting** a count (per `app/Utils/WarehouseCountUtil.php::post()`) fabricates ledger activity:
- **Shortage** (`counted_qty < expected_qty`): decreases product quantity immediately, batches all shortage lines into one `stock_adjustment`-type `Transaction` (`status='received'`, `adjustment_type='normal'`) with `stock_adjustment_lines`, then runs `mapPurchaseSell(..., 'stock_adjustment')`.
- **Overage**: bumps `variation_location_details.qty_available` directly, creates a per-line `opening_stock`-type `Transaction` (ref `CC-OVG-{count_id}-{line_id}`) plus a matching zero-price `PurchaseLine` so the extra units become an allocatable lot.
- Entire post runs in one DB transaction; `stock_counts.status` flips `open`→`posted`, idempotency-guarded.
- The real stock/COGS ledger of record remains `transactions` + `stock_adjustment_lines`/`purchase_lines` — `stock_counts` itself is never the source of truth.

---

## 6. Invoicing

### `invoice_schemes`
`2017_11_23_181237` — `id`, `business_id`, `name`, `scheme_type` enum(`blank`,`year`), `prefix`, `start_number`, `invoice_count` (running counter), `total_digits`, `is_default`. Additions: `number_type` (default `sequential`); **2026 franchise master-scheme link**: `master_invoice_scheme_id` (self-ref-ish, points to a template/superadmin business's scheme), `gst_number`, `state_name` — assigning a store to a master scheme creates a local mirror row, but the invoice counter always lives on the **master** row, producing one gapless serial-number series across every store sharing a GSTIN (for state-wise GST filing).

### `invoice_layouts`
`2018_01_05_112817` + ~30 later migrations. Base print-layout template: `name`, `header_text`, `invoice_no_prefix`, `invoice_heading`, `sub_total_label`, `discount_label`, `tax_label`, `total_label`, `logo`, many `show_*` display toggles, `highlight_color`, `footer_text`, `is_default`, `business_id`.

Accumulated, grouped:
| Group | Columns |
|---|---|
| Balance-due display | `invoice_heading_paid`/`not_paid`, `total_due_label`, `paid_label`, `show_payments`, `show_customer`/`customer_label`, `show_previous_bal`/`prev_bal_label`, and a **separate, later (2025) parallel pair** `show_previous_balance_due`/`previous_balance_due_label` (see oddities) |
| Sub-headings/columns | `sub_heading_line1..5`, table column labels, `show_client_id`/`client_id_label`, `date_label`, `show_time`, `show_brand`/`sku`/`cat_code`/`sale_description`, `cat_code_label`, `client_tax_label` |
| Design | `design` enum(`classic`,`elegant`), later widened to `VARCHAR` for more design names (incl. `pharmacy`, set by the 2026 batch-expiry rollout) |
| Batch/expiry display (pharmacy) | `show_expiry`, `show_lot`, `show_image` |
| Sales person/commission | `sales_person_label`/`show_sales_person`, `commission_agent_label`/`show_commission_agent` |
| Credit note | `cn_heading`, `cn_no_label`, `cn_amount_label` |
| Tax breakdown | `table_tax_headings` |
| Custom fields | `product_custom_fields`, `contact_custom_fields`, `location_custom_fields` |
| Misc | `date_time_format`, `show_reward_point`, `module_info`, `common_settings`, `show_qr_code`/`qr_code_fields`, `show_letter_head`/`letter_head`, `round_off_label`, `quotation_heading`/`quotation_no_prefix` |
| Franchise | `master_invoice_layout_id` (2026, mirrors the scheme master-link pattern) |

### `business_locations` ↔ invoicing wiring
`invoice_scheme_id`/`invoice_layout_id` plus a later second **sale-specific** pair `sale_invoice_scheme_id`/`sale_invoice_layout_id` (backfilled from the originals) — lets a location use a different scheme/layout for POS receipts vs. other documents. Plus `print_receipt_on_invoice`, `receipt_printer_type`, `printer_id`.

### `printers`
`2018_01_27_184322` — `id`, `business_id`, `name`, `connection_type` enum(`network`,`windows`,`linux`), `capability_profile` enum (ESC/POS thermal profiles), `char_per_line`, `ip_address`, `port`, `path`, `created_by`.

---

## 7. Modules

### Accounting (`Modules/Accounting`)
Double-entry bookkeeping layered on top of the core POS transaction system.

| Table | Purpose | Key columns/FKs |
|---|---|---|
| `accounting_account_types` | Chart-of-accounts taxonomy (~120 seeded categories) | `business_id` (nullable=global default), `parent_id` self-ref, `account_primary_type`, `account_type`, `show_balance` |
| `accounting_accounts` | Business ledger accounts | `business_id`, `account_sub_type_id`, `detail_type_id`, `parent_account_id` self-ref, `gl_code`, `created_by` |
| `accounting_accounts_transactions` | Debit/credit ledger entries | `accounting_account_id`, `acc_trans_mapping_id`, `transaction_id` (→core transactions), `transaction_payment_id`, `amount`, `type` |
| `accounting_acc_trans_mappings` | Groups ledger entries into a "journal entry" header | `business_id`, `ref_no`, `type`, `created_by`, `operation_date` |
| `accounting_budgets` | Monthly/quarterly/yearly budget figures per account | `accounting_account_id`, `financial_year`, jan–dec + quarter + yearly decimals |

Also adds `business.accounting_settings`, `business_locations.accounting_default_map`, `system.accounting_version`.

### AssetManagement (`Modules/AssetManagement`)
Fixed-asset register: assets, allocation/transfer to staff, warranties, maintenance tickets.

| Table | Purpose | Key columns/FKs |
|---|---|---|
| `assets` | Asset master | `business_id`, `category_id` (→categories), `location_id`, `created_by`, `asset_code`, `quantity`, `unit_price`, `depreciation`, `is_allocatable` |
| `asset_transactions` | Allocation/transfer/return ledger | `business_id`, `asset_id` (cascade), `receiver` (→users), `created_by`, `parent_id` self-ref, `transaction_type`, `quantity`, `allocated_upto` |
| `asset_warranties` | Warranty windows | `asset_id`, `start_date`/`end_date`, `additional_cost` |
| `asset_maintenances` | Maintenance/repair tickets | `business_id`, `asset_id`, `status`, `priority`, `created_by`, `assigned_to`, `maintenance_note` |

Also adds `business.asset_settings`, `system.assetmanagement_version`.

### Connector (`Modules/Connector`)
No database footprint beyond a `connector_version` row in `system`.

### Essentials (`Modules/Essentials`)
HR/office-productivity: documents, to-dos, reminders, messaging, leave/attendance/shift/payroll, KB, sales targets.

| Table | Purpose |
|---|---|
| `essentials_documents` / `essentials_document_shares` | Uploaded docs + sharing grants (user/role) |
| `essentials_reminders` | Personal reminders/alarms |
| `essentials_to_dos` / `essentials_todos_users` (pivot) / `essentials_todo_comments` | Task list, multi-user assignment, comments |
| `essentials_messages` | Internal broadcast/location messages |
| `essentials_leave_types` / `essentials_leaves` | Leave configuration + applications |
| `essentials_attendances` | Clock-in/out log incl. geo-location |
| `essentials_holidays` | Company holiday calendar |
| `essentials_allowances_and_deductions` / `essentials_user_allowance_and_deductions` (pivot) | Payroll allowance/deduction defs + per-user assignment |
| `essentials_shifts` / `essentials_user_shifts` | Shift definitions + assignment |
| `essentials_payroll_groups` / `essentials_payroll_group_transactions` (pivot) | Batched payroll runs → linked `transactions` |
| `essentials_kb` / `essentials_kb_users` (pivot) | Knowledge-base tree + per-user sharing |
| `essentials_user_sales_targets` | Per-user sales targets/commission tiers |

Note: a standalone `essentials_payrolls` table was created then dropped — payroll data was unified into the core `transactions` table (added `duration`, `duration_unit`, `amount_per_unit_duration`, `allowances`, `deductions` columns) rather than kept separate. Also adds HR columns to `users` (`essentials_department_id`, `essentials_designation_id`, `essentials_salary`, `essentials_pay_period`, `essentials_pay_cycle`, `location_id`) and `business.essentials_settings`.

### Spreadsheet (`Modules/Spreadsheet`)
Lightweight in-app spreadsheet/notes tool.

| Table | Purpose |
|---|---|
| `sheet_spreadsheets` | Spreadsheet documents (`business_id`, `name`, `sheet_data` longtext, `created_by`, `folder_id`) |
| `sheet_spreadsheet_shares` | Sharing grants (`sheet_spreadsheet_id` cascade, `shared_with`, `shared_id`) |

### Superadmin (`Modules/Superadmin`)
Multi-tenant SaaS control plane: subscription packages/billing, plus (in this fork) the franchise master-product catalog.

| Table | Purpose |
|---|---|
| `packages` | Subscription plan definitions (limits, price, feature flags, `custom_permissions`) |
| `subscriptions` | A business's subscription instance (`business_id` cascade, `package_id`, dates, `status` enum) |
| `superadmin_communicator_logs` | Bulk email/message log to businesses |
| `superadmin_frontend_pages` | CMS pages for the marketing site |

**Architecture change — master product catalog replaces per-product subscription billing (2026-07-15 series):**
1. `add_is_master_product_to_products` — adds `products.is_master_product` + a temporary `superadmin_product_id`.
2. `drop_old_superadmin_products_tables` — **drops `superadmin_products`/`superadmin_product_variations` outright** and the `superadmin_product_id` column (the `down()` fully documents the old schema: product/variation rows with their own SKU, pricing, tax type, unit/category/brand name snapshots — used purely for SaaS-operator billing, decoupled from tenant products).
3. `add_master_product_id_to_products` — re-adds `products.master_product_id` (self-ref, **no FK**) pointing at another `products` row where `is_master_product=1`.
4. `add_master_variation_id_to_variations` — mirrors this on `variations.master_variation_id` (self-ref, **no FK** — a business's copy may point at a variation owned by the master product in a different business; matching by id rather than name, which was unreliable).

**Interpretation**: the old design used a separate SaaS-billing-only product catalog; the new design treats "master" and "franchise-copy" as flagged rows in the *same* `products`/`variations` tables, letting a sync process propagate master-catalog changes into every franchise business's local product/variation rows by id, while each franchise keeps independent stock/pricing.

### SupportTicket (`Modules/SupportTicket`) — fully custom Dava India module
Manages damage/loss/missing-item claims raised at stores or warehouses against POs/GRNs, with TAT-based escalation and manufacturer-level tracking. Added 2026-07-23, extended through 2026-08-05.

**`support_ticket_closure_reasons`**: `business_id` (nullable = available to every business, FK cascade), `label`, `requires_resend` (bool — obligates a replacement/resend GRN), `is_active`. The seeded label has been **reworded twice**: `"Loss accepted..."` → `"Missing accepted..."` (2026_07_25, "Loss" read as a financial write-off elsewhere) → `"Missing Accepted- Warehouse will not resend the item."` (2026_07_27, spells out who decided).

**`support_tickets`**: `business_id`, `ticket_number` (unique), `location_id` (FK cascade, the reporting store), `purchase_line_id` (FK cascade, the GRN line), `transaction_id` (FK cascade, the GRN), `purchase_order_line_id`/`purchase_order_id` (nullable, the originating PO), `ticket_type` enum(`loss_short`,`in_transit_damage`,`mixed`), `quantity_damaged`/`quantity_lost` (snapshot at raise time), `damage_loss_reason`/`note`, `status` (enum `open`/`closed`, later widened via raw SQL to add `delayed`), `tat_due_at` (indexed — TAT deadline, likely computed from `business.support_ticket_tat_hours`), `closure_reason_id` (FK **restrict**), `closure_note`, `raised_by`/`closed_by` (FK `users`), `closed_at`. **2026-08-05 manufacturer-identity addition**: `supplier_contact_id`, `manufacturer_id`, `is_auto_generated` — all deliberately **FK-less** so a ticket survives contact/manufacturer deletion for audit purposes; lets a claim against a login-less Third-Party Manufacturer be identified/aggregated per manufacturer (previously only implicit via the GRN's `contact_id`).

**Related core-table changes**: `purchase_lines.po_quantity_damaged`/`po_quantity_lost`/`support_ticket_id`; `business.support_ticket_tat_hours` (default 48h).

**Permissions**: `support_ticket.create/view_own/view_all/manage/add_log`, granted to all roles (create+view_own), Admin roles (+ view_all/manage/add_log), and — specifically for `is_supplier_warehouse=1` businesses — `Supplier#{business}` (warehouse-operator) roles get full view_all/manage/add_log/create, since claims against login-less manufacturers must be resolvable at warehouse level.

No pivot tables in this module.

---

## Franchise / multi-store architecture — cross-cutting summary

Several 2026 migrations across different domains implement one coherent architecture for running Dava India as a chain of many single-location `business` tenants sharing catalog, customers, suppliers, and invoice numbering:

1. **Master catalog sync** — `products.is_master_product`/`master_product_id`, `variations.master_variation_id` (self-referential, no cross-business FK). Replaced a separate `superadmin_products` billing catalog.
2. **Universal customers** — `contacts.is_global`/`source_business_id`/`master_contact_id`, with dedup migrations collapsing duplicate Walk-In Customers chain-wide.
3. **Supplier network** — `contacts.common_supplier_id` (clone→master) + `contacts.supplier_business_id` (master→its own warehouse business) + `contacts.is_manufacturer` (TPM discriminator) + `users.common_supplier_id` (supplier portal login identity) + `business.is_supplier_warehouse`/`common_supplier_id`.
4. **Store-as-customer** — `contacts.store_business_id`, deliberately business-private (never global).
5. **Gapless GST invoice numbering across stores** — `invoice_schemes.master_invoice_scheme_id`/`gst_number`/`state_name`, `invoice_layouts.master_invoice_layout_id` — the invoice counter lives only on the master scheme row.
6. **Store↔warehouse PO/SO mirroring** — `transactions.source_store_po_id`, `warehouse_dispatched_at`, `consignment_id`; auto-replenishment via `business.auto_po_frequency_days`/`tpm_po_frequency_days`.
7. **Batch traceability across the chain** — `purchase_lines.source_purchase_line_id` chains a store's GRN batch back to the warehouse's own GRN batch.

---

## 8. 2026-08 additions — procurement follow-up, compliance, expiry & disposal, bounce rate

> Everything in this section was added after the original 2026-07-30 compilation. Column
> descriptions here are authoritative for the tables/columns named; the sections above remain
> accurate for everything else.

### Batch identity & manufacturer traceability (columns on existing tables)

| Table.column | Purpose |
|---|---|
| `product_manufacturers` (table) | Pivot: a product may be made by SEVERAL manufacturers. `is_primary` marks the one mirrored onto the legacy `products.manufacturer_id`. Written only through `Product::syncManufacturers()`, which keeps pivot and scalar in step |
| `purchase_lines.manufacturer_id` | The maker recorded for THIS batch at GRN. More accurate than the product primary when a product has several makers; drives batch-level attribution everywhere |
| `transaction_sell_lines.manufacturer` (+ snapshot fields) | Manufacturer name captured at sale time so an invoice reprint years later still shows the maker of the batch actually dispensed |
| `invoice_layouts.show_manufacturer` | Per-layout toggle for printing the batch manufacturer on the customer invoice |
| `purchase_lines.source_purchase_line_id` | Chains a store's GRN batch back to the warehouse batch it came from (pre-existing, now load-bearing for the store GRN prefill) |

### Store → warehouse purchase-order follow-up

| Table.column | Purpose |
|---|---|
| `products.mfg_lead_time_days` | Days from PO date for the product to reach the warehouse from the manufacturer. Blank = unknown (falls back to observed average), which is NOT the same as 0 |
| `transactions.followup_stage` | Rung the chase ladder has reached: `T-15`, `T-7`, `T-6`…`T-0`, then `OVD-n` (n days overdue). Stored rather than derived so once-only rungs fire once and daily rungs re-fire as the value changes |
| `transactions.followup_last_at` | When a human last acted. Suppresses the sidebar badge and blocks re-escalation |
| `transactions.followup_reminded_stage` | Last rung a reminder EMAIL was sent for — separate from `followup_stage` because the screen nags daily but the manufacturer must not be emailed daily |
| `purchase_lines.critical_at` | Set when a store's stock reaches the urgent threshold (default 50% of min). Timestamp not boolean, so the sweep can order a backlog by longest-waiting |
| `support_tickets.ticket_type='delivery_delay'` | New enum value. `purchase_line_id` and `raised_by` became NULLable: a delay ticket has no GRN line (that is the complaint) and the scheduler has no auth user |

### Compliance Management

| Table | Purpose |
|---|---|
| `business_license_logs` | Append-only trail of what was DONE about each licence: `status` (renewed / applied / in_process / not_applicable / remark), old+new licence no. and expiry, `note`, `created_by`. The licences themselves stay as number/expiry column pairs on `business` (Form 20/21, 20B/21B, trade, shop & establishment, FSSAI) — this table records the actions, not the licences |

### Expiry & Disposal Management

| Table | Purpose |
|---|---|
| `expired_goods_returns` | A store's despatch of expired stock to its linked warehouse. `status` draft → sent → received (or rejected); `store_transaction_id` (the store-side `purchase_return`) and `warehouse_transaction_id` (the warehouse-side `purchase_transfer`) pair the two stock documents |
| `expired_goods_return_lines` | One expired batch per row. Batch identity (lot, expiry, mfg date, manufacturer, unit cost) is **snapshot**, not merely referenced, because it feeds a disposal certificate that must stay readable years later. `received_quantity` + `discrepancy_note` record what the warehouse actually counted |
| `disposal_records` | The legal artefact: `method`, `disposal_date`, agency + licence no., `certificate_no`, witness name/designation, authorising officer, `drug_inspector_ref`. `stock_adjustment_id` links the write-off so the certificate reconciles to the stock ledger |
| `disposal_record_lines` | Every batch destroyed, with `drug_schedule` and `source_store_business_id`/`source_store_name` so the certificate can state the origin of the goods |

**Stock model.** Expired goods leave the store as a `purchase_return`, arrive at the warehouse as a
`purchase_transfer` whose lines carry `qc_status='expired'`, and are removed at destruction by a
`stock_adjustment` of `adjustment_type='abnormal'` with `lot_no_line_id` set per batch. The
`qc_status` flag is what makes them unsellable — `TransactionUtil::mapPurchaseSell` only ever
allocates batches whose `qc_status` is NULL or `passed`, so no code change was needed to stop them
being sold.

### Bounce rate

| Table | Purpose |
|---|---|
| `product_bounces` | One recorded bounce: a customer asked for something the till could not sell, captured off the POS product search. `type` is `out_of_stock` (listed but no stock) or `not_listed` (not in the catalogue at all). No SoftDeletes — a transactional observation log |

---
## Notable / unusual / pharmacy-specific — final callouts

- **No dedicated `stock_adjustments`/`stock_transfers` tables** — both are `transactions` rows (`type='stock_adjustment'`/`'sell_transfer'`+`'purchase_transfer'`), a core design pattern retained here. A dead artifact from the 2018 refactor briefly creates then renames a throwaway `stock_adjustments` table (`stock_adjustments_temp`), unused by the app.
- **`stock_adjustment_lines.removed_purchase_line`** — a `purchase_line_id` column added then immediately renamed, superseded by the `transaction_sell_lines_purchase_lines` mapping table but left in the schema unused/vestigial, alongside `lot_no_line_id`.
- **Two parallel "previous balance due" features on `invoice_layouts`** — `show_previous_bal`/`prev_bal_label` (2018) and a fresh, separate `show_previous_balance_due`/`previous_balance_due_label` pair (2025) rather than reusing/renaming the original — likely duplication worth flagging to whoever maintains print templates.
- **`media` table naming drift** — `2020_12_29_165925_add_model_document_type_to_media_table.php` (filename) actually adds a column named `model_media_type`, not `model_document_type`.
- **Restaurant-module leftovers**, present in schema but expected dormant for a pure pharmacy chain unless `business.enabled_modules` toggles them on: `res_tables`, `bookings`, `res_product_modifier_sets` (modifiers pivot), plus `transactions.res_table_id`/`res_waiter_id`/`res_order_status` and `products.type='modifier'`.
- **`types_of_services`** looks restaurant-adjacent by migration date but is actually wired into core sell flow (packing charges) across the whole app — not vestigial.
- **Superadmin master-product architecture change** — `superadmin_products`/`superadmin_product_variations` tables were dropped outright (2026-07-15) in favor of self-referential `master_product_id`/`master_variation_id` columns directly on `products`/`variations`, a notable schema pivot away from a separate SaaS-billing catalog toward a same-table franchise-sync model.
- **Extensive pharma-regulatory additions on top of the core generic ERP**: drug composition/salt data model (`salts`, `compositions`, `composition_salt`), doctor/prescription tracking (`doctors`, `transactions.doctor_id`), manufacturer/division modeling (`manufacturers`, `divisions`, PTR/PTS pricing), split multi-license tracking on `business` (Form 20/21, 20B/21B, trade, shop & establishment, FSSAI), GST e-Invoice/e-Way Bill fields on `transactions`, cold-chain `temperature_logs`, hierarchical `warehouse_bins`/`warehouse_bin_stock` with FEFO/cold-chain fields, `movement_tag_configs`-driven auto min/max stock computation, and a from-scratch `SupportTicket` module for damage/loss/missing-item claims with TAT escalation.
- **Heavy use of "loose" (non-FK) integer reference columns** for cross-business links (`master_variation_id`, `store_business_id`, `supplier_business_id`, `support_tickets.supplier_contact_id`/`manufacturer_id`, `source_purchase_line_id`) — a deliberate pattern (documented in several migration comments) to avoid FK constraints that would either cross tenant boundaries awkwardly or block deletion needed for audit-trail retention.
- **Several "migration name says X, content does Y" mismatches** worth remembering when grepping by filename alone: `add_model_document_type_to_media_table.php` (adds `model_media_type`), `create_purchase_requisition_related_columns.php` (adds columns only, no new table), `stock_adjustment_move_to_transaction_table.php` (also creates and immediately abandons a `stock_adjustments` table).