# Data Model — TinyPOS (`tnx-pos`)

> Synthesized from `_reversa_sdd/data-dictionary.md` and `_reversa_sdd/erd-complete.md`.
> Confidence scale: 🟢 CONFIRMED · 🟡 INFERRED · 🔴 GAP

---

## 1. Application / Domain ERD

erDiagram
    categories ||--o{ products : "classifies (category_id, required)"
    categories ||--o{ categories : "parent_id (self, group→child) 🔴 GAP-C1"
    brands ||--o{ products : "labels (brand_id, required)"
    products ||--o{ product_units : "has conversion units"
    units ||--o{ product_units : "unit_id"
    products ||--o{ order_product : "sold as (line)"
    orders ||--o{ order_product : "contains lines"
    units ||--o{ order_product : "unit_id (onDelete set null)"
    customers ||--o{ orders : "places (nullable / walk-in)"
    discounts ||--o{ orders : "discount_id 🟡 legacy/unused"
    customers ||--o{ customer_debts : "A/R ledger owner"
    orders ||--o{ customer_debts : "order_id (onDelete set null)"
    customer_debts ||--o{ customer_debts : "related_debt_id (void→source)"
    customers ||--|| customer_order_summary : "nightly rollup (1:1)"
    customers ||--o{ customer_gift : "redeems"
    gifts ||--o{ customer_gift : "redeemed as"

    products {
        int id PK
        string code "unique among live rows; auto P{cat}{6}"
        string name "prefixed (DELETED) on soft-delete"
        int category_id FK "required"
        int brand_id FK "required"
        decimal price "base/cost"
        string wholesale_prices "JSON: type→price"
        decimal sale_price "fallback = price"
        decimal qty
        string unit "base unit BY NAME, not FK 🟡"
        decimal reward_point
        date expiry_date
        datetime deleted_at "SoftDeletes"
    }
    product_units {
        int id PK
        int product_id FK
        int unit_id FK
        float conversion_qty "base units per this unit"
        bool is_default "at most 1 per product"
    }
    units {
        int id PK "id 1 edit-locked (default)"
        string name
        string description
        int category_id "🟡 dead/unused (GAP-U1)"
    }
    categories {
        int id PK "ids 1=milk / 8=medicine special-cased"
        string name
        string description
        int parent_id FK "nullable; only children assignable 🔴"
    }
    brands {
        int id PK "id 1 edit-locked (default)"
        string name
        string description
    }
    orders {
        int id PK "code = #QT78-{id}"
        int customer_id FK "nullable (walk-in)"
        int discount_id FK "🟡 unused legacy"
        decimal subtotal
        decimal discount_amount
        decimal total "subtotal - discount_amount"
        decimal earned_point
        decimal paid
        decimal debt_amount "posts pos_debt on done"
        timestamp points_awarded_at "idempotency guard"
        int count "number of lines, not qty"
        enum status "draft | done (no soft-delete)"
        string notes
    }
    order_product {
        int order_id FK "onDelete cascade"
        int product_id FK "onDelete cascade"
        int qty "scan/add count"
        decimal price "captured at sale time"
        int unit_id FK "nullable; set null"
        decimal conversion_qty "captured at sale time"
    }
    discounts {
        int id PK "🟡 legacy/unused"
        string code
        decimal value
        enum value_type "cash | percent"
        enum apply_type "order | category | product"
    }
    customers {
        int id PK
        string fullname
        string phone "unique among live rows"
        string email
        enum gender "male | female | other"
        string birthday "d/m/Y display"
        date birthday2 "parsed Y-m-d"
        decimal points "reward balance"
        decimal debt_total "live A/R balance"
        string type "khach_le | si_1 | si_2"
        datetime deleted_at "SoftDeletes"
    }
    customer_debts {
        int id PK
        int customer_id FK "cascade"
        int order_id FK "nullable; set null"
        int related_debt_id FK "void→pos_debt"
        enum type "pos_debt | manual_debt | repayment | debt_void"
        decimal amount "positive magnitude"
        decimal balance_after "debt_total snapshot"
        string note
        int created_by "admin user id"
    }
    customer_order_summary {
        int customer_id PK "one row per customer"
        int orders_count
        decimal amount_total
        decimal points_total
        longtext categories_statistic "JSON per-category rollup"
    }
    gifts {
        int id PK
        string name
        decimal points "cost to redeem"
        int limit "per-customer; 0 = unlimited"
        int quantity "stock"
        int used "total redeemed"
        tinyint active "scopeActive = 1"
        datetime deleted_at "SoftDeletes"
    }
    customer_gift {
        int customer_id FK
        int gift_id FK
        decimal points "cost at redemption"
        string note
    }
    settings {
        string key PK "e.g. near_expiry_days"
        string value
    }

> `settings` is a standalone key/value store (no FK relations). 🟡

---

## 2. Admin RBAC ERD (Encore\Admin — vendored, seeded, unenforced) 🟢

These tables are created by the `2016_01_04_173148_create_admin_tables.php` migration and seeded by `AdminTablesSeeder`. Permission middleware is **not wired**, so they are decorative at runtime.

erDiagram
    admin_users ||--o{ admin_role_users : ""
    admin_roles ||--o{ admin_role_users : ""
    admin_roles ||--o{ admin_role_permissions : ""
    admin_permissions ||--o{ admin_role_permissions : ""
    admin_users ||--o{ admin_user_permissions : ""
    admin_permissions ||--o{ admin_user_permissions : ""
    admin_roles ||--o{ admin_role_menu : ""
    admin_menu ||--o{ admin_role_menu : ""
    admin_users ||--o{ admin_operation_log : "(logging disabled)"

    admin_users {
        int id PK
        string username "unique (190)"
        string password "bcrypt (60)"
        string name
        string avatar
        string remember_token "nullable (100)"
    }
    admin_roles {
        int id PK
        string name "unique (50)"
        string slug "e.g. administrator"
    }
    admin_permissions {
        int id PK
        string name "unique (50)"
        string slug "e.g. * (all)"
        string http_method "nullable"
        text http_path "nullable"
    }
    admin_menu {
        int id PK
        int parent_id "tree root = 0"
        int order "default 0"
        string title
        string icon
        string uri "nullable"
    }
    admin_role_users {
        int role_id FK
        int user_id FK
    }
    admin_role_permissions {
        int role_id FK
        int permission_id FK
    }
    admin_user_permissions {
        int user_id FK
        int permission_id FK
    }
    admin_role_menu {
        int role_id FK
        int menu_id FK
    }
    admin_operation_log {
        int id PK
        int user_id FK
        string path
        string method "10 chars"
        string ip "15 chars"
        text input "operation_log.enable = false"
    }

---

## 3. Entity Definitions

### `admin_users` — Administrator 🟢

Source: `database/migrations/2016_01_04_173148_create_admin_tables.php`, `config/admin.php`.

| Field | Type | Required | Notes |
|---|---|---|---|
| `id` | increments | yes | PK |
| `username` | string(190) | yes | unique |
| `password` | string(60) | yes | bcrypt hash |
| `name` | string | yes | display name |
| `avatar` | string | no | nullable |
| `remember_token` | string(100) | no | nullable |
| `created_at` / `updated_at` | timestamps | yes | |

### `admin_roles` — Role 🟢

| Field | Type | Required | Notes |
|---|---|---|---|
| `id` | increments | yes | PK |
| `name` | string(50) | yes | unique |
| `slug` | string(50) | yes | role key |
| `created_at` / `updated_at` | timestamps | yes | |

### `admin_permissions` — Permission 🟢

| Field | Type | Required | Notes |
|---|---|---|---|
| `id` | increments | yes | PK |
| `name` | string(50) | yes | unique |
| `slug` | string(50) | yes | permission key |
| `http_method` | string | no | nullable — HTTP verb scope |
| `http_path` | text | no | nullable — path scope |
| `created_at` / `updated_at` | timestamps | yes | |

### `admin_menu` — Menu 🟢

| Field | Type | Required | Notes |
|---|---|---|---|
| `id` | increments | yes | PK |
| `parent_id` | integer | yes | default 0 (tree root) |
| `order` | integer | yes | default 0 |
| `title` | string(50) | yes | |
| `icon` | string(50) | yes | |
| `uri` | string(50) | no | nullable |
| `created_at` / `updated_at` | timestamps | yes | |

### RBAC pivot tables 🟢

| Table | Columns |
|---|---|
| `admin_role_users` | `role_id`, `user_id` |
| `admin_role_permissions` | `role_id`, `permission_id` |
| `admin_user_permissions` | `user_id`, `permission_id` |
| `admin_role_menu` | `role_id`, `menu_id` |
| `admin_operation_log` | `id`, `user_id`, `path`, `method(10)`, `ip(15)`, `input:text` — logging disabled by config |

### `products` — Product 🟢

Source: `app/Models/Product.php`; migration `2019_04_09_104651_create_products_table.php` + later add-column migrations.

| Field | Type | Required | Notes |
|---|---|---|---|
| `id` | increments | yes | PK |
| `code` | string | no | barcode / SKU; auto-generated `P{cat}{6-digit}` if empty; unique among non-deleted rows |
| `name` | string | yes | prefixed with `(DELETED) ` on soft-delete |
| `description` | string | no | |
| `category_id` | integer (FK → `categories`) | yes | |
| `brand_id` | integer (FK → `brands`) | yes | |
| `price` | numeric | yes | cost / base price |
| `wholesale_prices` | string (JSON-encoded) | no | map of customer type → price; mutated via accessor/mutator |
| `sale_price` | numeric | no | defaults to `price` when empty |
| `qty` | numeric | no | stock quantity |
| `unit` | string | yes | base unit label — **name string, not FK** |
| `pictures` | string (URL) | no | stored via `intervention/image`; `mimes:jpeg,jpg,png,webp` |
| `attr_weight` | string | no | one of `Product::$attr['weight']` |
| `reward_point` | numeric | no | points earned per sale; added by migration `2022_03_20` |
| `expiry_date` | date | no | drives the computed `is_expired` attribute; added `2026_08_26` |
| `promotion_note` | string | no | added `2026_08_26` |
| `deleted_at` | datetime | no | SoftDeletes |
| `created_at` / `updated_at` | timestamps | yes | |
| `is_expired` | bool (appended, not stored) | — | computed: `expiry_date < today` |

### `product_units` — ProductUnit 🟢

Source: `app/Models/ProductUnit.php`.

| Field | Type | Required | Notes |
|---|---|---|---|
| `id` | increments | yes | PK |
| `product_id` | integer (FK → `products`) | yes | |
| `unit_id` | integer (FK → `units`) | yes | |
| `conversion_qty` | float | yes | base-units per this unit; POS divides base price by it |
| `is_default` | boolean | yes | at most one default per product (enforced in `syncProductUnits`) |

### `units` — Unit 🟢

Source: `2019_05_15_121417_create_units_table.php`; `app/Models/Unit.php` (empty model).

| Field | Type | Required | Notes |
|---|---|---|---|
| `id` | increments | yes | PK; id `1` is edit-locked in the UI (protected default unit) |
| `name` | string | yes | unit name |
| `description` | string | no | nullable |
| `category_id` | unsignedInteger | no | nullable; declared but never read/written by any code — dead/unused 🟡 GAP-U1 |
| `created_at` / `updated_at` | timestamps | yes | |

### `categories` — Category 🟢

Source: `2019_04_09_082515_create_categories_table.php`; `app/Models/Category.php`.

| Field | Type | Required | Notes |
|---|---|---|---|
| `id` | increments | yes | PK; ids `1` (milk) and `8` (medicine) are special-cased in stats rollups |
| `name` | string | yes | category name |
| `description` | string | no | nullable |
| `parent_id` | unsignedInteger | no | nullable; self-reference for two-level group→child hierarchy; only children (`parent_id` not null) are assignable to products; never set via the UI 🔴 GAP-C1 |
| `created_at` / `updated_at` | timestamps | yes | |

### `brands` — Brand 🟢

Source: `2019_04_09_082524_create_brands_table.php`; `app/Models/Brand.php`.

| Field | Type | Required | Notes |
|---|---|---|---|
| `id` | increments | yes | PK; id `1` is edit-locked in the UI (inferred default brand) |
| `name` | string | yes | brand name |
| `description` | string | no | nullable |
| `created_at` / `updated_at` | timestamps | yes | |

### `orders` — Order 🟢

Source: `2019_04_09_104708_create_orders_table.php` + add-column migrations; `app/Models/Order.php`. **No SoftDeletes — deletes are permanent.**

| Field | Type | Required | Notes |
|---|---|---|---|
| `id` | increments | yes | PK; rendered as `#QT78-{id}` (appended `code`) |
| `customer_id` | integer | no | nullable (walk-in orders) |
| `discount_id` | integer (FK → `discounts`) | no | nullable; unused by current code (legacy) |
| `subtotal` | decimal default 0 | yes | sum of line `round(price,1)*qty` |
| `total` | decimal default 0 | yes | `subtotal - discount_amount` |
| `discount_amount` | decimal default 0 | yes | free-form order discount |
| `earned_point` | decimal(8,1) default 0 | yes | sum of `round(reward_point,1)*qty`; added `2022_03_21` |
| `paid` | decimal default 0 | yes | set equal to `total` |
| `debt_amount` | decimal(15,1) default 0 | yes | amount left owing; posts a `pos_debt` ledger entry when order is done; added `2026_08_28` |
| `points_awarded_at` | timestamp | no | nullable; set once when finalised (idempotency guard) |
| `count` | integer (unsigned) | no | number of **lines**, not total quantity |
| `status` | enum(`draft`, `done`) default `draft` | yes | was `waiting` → `draft` via `2022_05_10` migration |
| `notes` | string | no | nullable |
| `created_at` / `updated_at` | timestamps | yes | `updated_at` drives the 24 h `is_editable` window |
| `code` | string (appended, not stored) | — | `#QT78-{id}` |
| `is_editable` | bool (appended, not stored) | — | draft, or done within 24 h |
| `debt_locked` | bool (appended, not stored) | — | a `pos_debt` ledger row exists |

### `order_product` — Order line pivot (Order ↔ Product) 🟢

Source: `2019_04_10_044846_create_order_product_table.php` + `2026_08_27_000002` (unit columns).

| Field | Type | Required | Notes |
|---|---|---|---|
| `order_id` | integer (FK → `orders`) | yes | onDelete cascade |
| `product_id` | integer (FK → `products`) | yes | onDelete cascade |
| `qty` | integer (unsigned) default 0 | yes | scan/add count |
| `price` | decimal | yes | unit price captured at sale time |
| `discount` | decimal default 0 | no | per-line discount (unused by controller) |
| `unit_id` | integer (FK → `units`) | no | nullable; onDelete set null |
| `conversion_qty` | decimal(10,4) | no | nullable; base-units per chosen unit captured at sale time |
| `created_at` / `updated_at` | timestamps | yes | |

### `discounts` — Discount 🟡 (legacy/unused)

Source: `2019_04_09_082930_create_discounts_table.php`; `app/Models/Discount.php`.

| Field | Type | Required | Notes |
|---|---|---|---|
| `id` | increments | yes | PK |
| `code` | string | yes | |
| `value` | decimal | yes | |
| `value_type` | enum(`cash`, `percent`) default `cash` | yes | |
| `apply_type` | enum(`order`, `category`, `product`) default `order` | yes | |
| `apply_to` | text | no | nullable |
| `created_at` / `updated_at` | timestamps | yes | |

### `customers` — Customer 🟢

Source: `2019_04_09_094008_create_customers_table.php` + add-column migrations; `app/Models/Customer.php`.

| Field | Type | Required | Notes |
|---|---|---|---|
| `id` | increments | yes | PK |
| `fullname` | string (indexed) | yes | |
| `phone` | string (indexed) | yes | `numeric` on create; unique among live rows (`unique(phone, deleted_at)`) |
| `email` | string (indexed) | no | nullable; `unique(email, deleted_at)` |
| `address` | string | no | nullable |
| `gender` | enum(`male`, `female`, `other`) | no | nullable |
| `birthday` | string | no | display form `d/m/yyyy` (regex-validated) |
| `birthday2` | date | no | parsed `Y-m-d`; cast as date; nullable string column |
| `dependant` | string | no | nullable |
| `points` | decimal (unsigned, default 0) | yes | reward-point balance |
| `debt_total` | decimal(15,1) default 0 | yes | live accounts-receivable balance (cast `float`); maintained by `CustomerDebt` |
| `type` | string | no | one of `khach_le` \| `si_1` \| `si_2`; accessor defaults null → `khach_le` |
| `type_label` | string (appended, not stored) | — | mapped from `Customer::$types` |
| `deleted_at` | datetime | no | SoftDeletes |
| `created_at` / `updated_at` | timestamps | yes | |

### `customer_debts` — CustomerDebt (A/R ledger) 🟢

Source: `2026_08_28_000001_create_customer_debts_table.php`; `app/Models/CustomerDebt.php`.

| Field | Type | Required | Notes |
|---|---|---|---|
| `id` | increments | yes | PK |
| `customer_id` | integer (FK → `customers`) | yes | onDelete cascade |
| `order_id` | integer (FK → `orders`) | no | nullable; onDelete set null |
| `related_debt_id` | integer (FK → `customer_debts`) | no | nullable; a `debt_void` points to the `pos_debt` it reverses; onDelete set null |
| `type` | enum(`pos_debt`, `manual_debt`, `repayment`, `debt_void`) | yes | ledger entry kind |
| `amount` | decimal(15,1) default 0 | yes | positive magnitude of the movement |
| `balance_after` | decimal(15,1) | no | customer `debt_total` snapshot after this entry |
| `note` | string | no | nullable |
| `created_by` | integer | no | admin user id (nullable) |
| `created_at` / `updated_at` | timestamps | yes | |

### `customer_order_summary` — CustomerOrderSummary (denormalized stats) 🟢

Source: `2026_01_02_102722_create_customer_order_summary_table.php`; populated by `Order::summaryLogging()` (nightly).

| Field | Type | Required | Notes |
|---|---|---|---|
| `customer_id` | integer | yes | **PK** — one row per customer |
| `orders_count` | integer default 0 | yes | |
| `amount_total` | decimal(14,2) default 0 | yes | sum of order `total` |
| `points_total` | decimal(14,2) default 0 | yes | sum of `earned_point` |
| `categories_statistic` | longText (cast `object`) | no | JSON per-category / `attr_weight` aggregates |
| `created_at` / `updated_at` | timestamps | yes | |

### `gifts` — Gift 🟢

Source: `2022_05_18_122119_create_gifts_table.php`, `2022_05_25_121331_add_total_used_to_gifts_table.php`; `app/Models/Gift.php`.

| Field | Type | Required | Notes |
|---|---|---|---|
| `id` | increments | yes | PK |
| `name` | string | yes | reward name |
| `image` | string | no | nullable; accessor returns `asset(storage_url(value))` or `images/noimage.png` fallback |
| `points` | decimal(8,1) default 0 | no | point cost to redeem |
| `limit` | integer default 0 | no | max redemptions per customer; `0` = unlimited |
| `quantity` | integer default 0 | no | total stock |
| `used` | integer default 0 | no | total redeemed (added by later migration) |
| `active` | tinyInteger default 1 | yes | on/off flag; `scopeActive` = `active = 1` |
| `deleted_at` | datetime | no | SoftDeletes |
| `created_at` / `updated_at` | timestamps | yes | |
| `quantity_available` | int (appended, not stored) | — | computed: `quantity - used`; used by `Customer::checkGiftAvailable` |

### `customer_gift` — Redemption pivot (Customer ↔ Gift) 🟢

Source: `2022_05_20_151916_create_customer_gift_table.php`.

| Field | Type | Required | Notes |
|---|---|---|---|
| `customer_id` | integer (FK → `customers`) | yes | |
| `gift_id` | integer (FK → `gifts`) | yes | |
| `points` | decimal | no | point cost captured at redemption time |
| `note` | string | no | |

### `settings` — Setting (key/value) 🟡

Read by `ProductController::index` via `Setting::get('near_expiry_days', 30)`. Full schema not confirmed from migrations.

| Field | Type | Required | Notes |
|---|---|---|---|
| `key` | string | yes | e.g. `near_expiry_days` |
| `value` | string | no | falls back to the caller's default |

---

## 4. Relationship Catalogue

| From | To | Cardinality | FK / mechanism | Notes | Confidence |
|---|---|:---:|---|---|:---:|
| `categories` | `products` | 1:N | `products.category_id` (required) | Only child categories (`parent_id` not null) assignable | 🟢 |
| `categories` | `categories` | 0..1:N | `categories.parent_id` (self) | Two-level group→child; no Eloquent relation modelled; UI never sets it | 🔴 GAP-C1 |
| `brands` | `products` | 1:N | `products.brand_id` (required) | Brand id 1 = protected default | 🟢 |
| `products` | `product_units` | 1:N | `product_units.product_id` | Conversion units; at most 1 default | 🟢 |
| `units` | `product_units` | 1:N | `product_units.unit_id` | | 🟢 |
| `products` | `order_product` | 1:N | `order_product.product_id` (cascade) | Sale line | 🟢 |
| `orders` | `order_product` | 1:N | `order_product.order_id` (cascade) | Order lines | 🟢 |
| `units` | `order_product` | 0..1:N | `order_product.unit_id` (set null) | Line unit at sale time | 🟢 |
| `customers` | `orders` | 0..1:N | `orders.customer_id` (nullable) | Walk-in orders have no customer | 🟢 |
| `discounts` | `orders` | 0..1:N | `orders.discount_id` | Unused legacy relation | 🟡 GAP-O2 |
| `customers` | `customer_debts` | 1:N | `customer_debts.customer_id` (cascade) | A/R ledger | 🟢 |
| `orders` | `customer_debts` | 0..1:N | `customer_debts.order_id` (set null) | Debt row survives order hard-delete | 🟢 |
| `customer_debts` | `customer_debts` | 0..1:N | `related_debt_id` (self) | `debt_void` → source `pos_debt` | 🟢 |
| `customers` | `customer_order_summary` | 1:1 | `customer_order_summary.customer_id` (PK) | Nightly rollup | 🟢 |
| `customers` | `gifts` | N:M | `customer_gift` pivot | Redemption ledger (`points`, `note`) | 🟢 |
| — | `settings` | — | key/value | Standalone config store; no FK relations | 🟡 |

---

## 5. Constraints and Lifecycle Notes

### Uniqueness

- `products.code` — unique index includes `deleted_at`; a code is freed after soft-delete and may be reused.
- `customers.phone` — `unique(phone, deleted_at)`; same live-row scoping.
- `customers.email` — `unique(email, deleted_at)`.
- `admin_users.username` — globally unique (no soft-delete on admin users).
- `admin_roles.name`, `admin_permissions.name` — globally unique within their tables.

### Soft-delete entities

`products`, `customers`, `gifts` use Laravel SoftDeletes (`deleted_at`). Historical `order_product` and `customer_gift` rows keep FK references to soft-deleted records; these references remain valid and are not cascaded.

### Hard-delete entities

`orders` has **no SoftDeletes**. Deletion is permanent and runs inside a transaction that reverses points and debt before the hard-delete. `order_product` rows cascade. `customer_debts.order_id` is set null so the ledger trail (with `balance_after` and `related_debt_id`) survives the order deletion (ADR-0004). 🟢

### String unit vs FK unit (GAP-U2) 🟡

`products.unit` is a free-text string keyed by unit *name*, not a FK to `units.id`. Conversion units in `product_units` and sale-time units in `order_product` use the proper `unit_id` FK. Renaming a `units` row desyncs any `products.unit` strings that matched by name.

### `units.category_id` — dead column (GAP-U1) 🟡

The `category_id` column exists on the `units` table per the migration but is never read or written by application code.

### `categories.parent_id` — no Eloquent relation (GAP-C1) 🔴

The column declares a two-level group→child hierarchy. No `parent()` or `children()` relation is modelled in `Category`. The UI never sets `parent_id`; the hierarchy is apparently aspirational / partially implemented.

### `discounts` — legacy/unused (GAP-O2) 🟡

The `discounts` table and `orders.discount_id` FK exist but no controller path applies a discount by id.

### Accounts-receivable consistency

`customers.debt_total` is a denormalized running balance updated by each `customer_debts` write. `customer_debts.balance_after` stores the snapshot after each ledger entry, providing an audit trail independent of the live balance.

### Points idempotency guard

`orders.points_awarded_at` is set once when an order is finalised. Subsequent finalization calls check this timestamp and skip point awards, preventing double-crediting.

### `App\User` (framework model) — dead path 🟢

`config/auth.php` references `App\User` and a `password_resets` table, but the model file does not exist. Self-registration was intentionally never implemented; the app is admin-only via Encore\Admin.