# Entity-Relationship Diagram (Complete) — TinyPOS (`tnx-pos`)

> Produced by the Reversa **Architect** (phase: interpretation) · doc_level: `complete`
> Generated on 2026-09-18

**Confidence scale:** 🟢 CONFIRMED · 🟡 INFERRED · 🔴 GAP

Full data model, synthesised from `data-dictionary.md`. Split into two diagrams: the **application/domain** model, and the vendored **Encore\Admin RBAC** model (seeded but unenforced — see `permissions.md`). Attributes list principal fields, primary keys (PK) and foreign keys (FK); full column detail lives in `data-dictionary.md`.

---

## 1. Application / domain model

```mermaid
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 "'(DELETED) ' prefix 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 "≤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 (locked)"
        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 "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 drawn standalone (no FK) — it is a key/value store read by `Setting::get(...)` (e.g. `near_expiry_days`), not related to other entities. 🟡

---

## 2. Encore\Admin RBAC model (vendored, seeded, **unenforced**)

These tables are created by `2016_01_04_173148_create_admin_tables.php` and seeded by `AdminTablesSeeder`, but the app does not wire the permission middleware, so they are decorative from the application's perspective (`permissions.md`, PERM-1). 🟢

```mermaid
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
    }
    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
        text http_path
    }
    admin_menu {
        int id PK
        int parent_id "tree (default 0)"
        int order
        string title
        string icon
        string uri
    }
    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
        string ip
        text input "operation_log.enable = false"
    }
```

---

## 3. Relationship catalogue

| From | To | Cardinality | FK / mechanism | Notes | Conf. |
|------|----|:-----------:|----------------|-------|:-----:|
| categories | products | 1:N | `products.category_id` (required) | Only child categories (`parent_id` not null) are assignable | 🟢 |
| categories | categories | 0..1:N | `categories.parent_id` (self) | Two-level group→child; **not** modelled as Eloquent relation; 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; ≤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 none | 🟢 |
| 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 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 | 🟡 |

---

## 4. Referential-integrity & lifecycle notes

- **`products.unit` is a string, not an FK** — base unit is keyed by unit *name*, while conversion units use `unit_id` FKs. Renaming a unit desyncs base-unit strings (GAP-U2). 🟡
- **Orders have no soft-delete** — `destroy` reverses points/debt in a transaction then **hard-deletes**; `order_product` cascades, `customer_debts.order_id` is set null so the ledger trail (with `balance_after` and `related_debt_id`) survives (ADR-0004, GAP-O1 accepted). 🟢
- **Products, customers, gifts use SoftDeletes** — historical `order_product` / `customer_gift` rows keep referencing soft-deleted records. 🟢
- **Uniqueness is live-row-scoped** — `products.code`, `customers.phone`/`email` unique indexes include `deleted_at`, so a value frees up after soft-delete. 🟢
</content>