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

> Produced by the Reversa **Archaeologist** (phase: excavation) · doc_level: `complete`
> Generated on 2026-09-16

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

> **Progress:** Incrementally built. Covered: entities touched by all **11 of 11** modules — **auth**, **dashboard**, **pos**, **products**, **customers**, **orders**, **debts**, **gifts**, **brands**, **categories**, **units**. A full ERD is deferred to `reversa-data-master`. Field types below are as declared in migrations / model casts / `$fillable`; nullability is stated where confirmed.

---

## `auth` module

### `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) |
| `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 pivots 🟢
`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.

### `App\User` (framework) 🟢
Referenced by `config/auth.php` and `RegisterController`, but **the model file does not exist**. `password_resets` table (per `config/auth.php:passwords`) would back framework password resets — dead path. Confirmed intentional with the team: admin-only app (Encore\Admin), self-registration was never meant to work.

---

## `dashboard` module

The dashboard reads existing entities; it defines no tables of its own. Fields it depends on:

| Entity | Field | Type | Used for | Confidence |
|--------|-------|------|----------|------------|
| `orders` | `status` | string (`draft`\|`done`) | KPI filter (`done`) & chart source | 🟢 |
| `orders` | `created_at` | datetime | `DATE(created_at)=CURDATE()` KPI + date bucketing | 🟢 |
| `order_product` | `product_id` | integer | top-products join/group | 🟢 |
| `order_product` | `qty` | numeric | `SUM(qty)` for top products | 🟢 |
| `products` | `deleted_at` | datetime nullable | excludes soft-deleted from top-products | 🟢 |
| `products` / `customers` | `id` | pk | `count()` KPIs | 🟢 |

---

## `pos` module

### `products` — Product 🟢
Source: `app/Models/Product.php` (`$fillable`, casts, accessors).

| 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 |
| `name` | string | yes | on soft-delete, prefixed with `(DELETED) ` |
| `description` | string | no | |
| `category_id` | integer (FK categories) | yes | |
| `brand_id` | integer (FK brands) | yes | |
| `price` | numeric | yes | cost/base price |
| `wholesale_prices` | json | no | map of customer type → price; cast via accessor/mutator (`json_decode`/`json_encode`) |
| `sale_price` | numeric | no | defaults to `price` when empty |
| `qty` | numeric | no | stock quantity |
| `unit` | string | yes | base unit label |
| `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 |
| `expiry_date` | date | no | drives `is_expired` appended attr |
| `promotion_note` | string | no | |
| `deleted_at` | datetime | no | SoftDeletes |
| `is_expired` | bool (appended) | — | 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: `app/Models/Unit.php` (bare Eloquent model — no `$fillable`/casts declared). Fields inferred: `id`, `name`. 🟡 (full schema in the `units` module / data-master pass)

### `orders` — Order (draft preload only here) 🟢
POS preloads: `id`, `status` (`draft`\|`done`), `notes`, `debt_amount`, plus appended `code` (`#QT78-{id}`), `is_editable`, `debt_locked`. Full Order schema is documented in the `orders` module.

### `order_product` — pivot (Order ↔ Product) 🟢
Pivot columns used by POS: `qty`, `price`, `unit_id`, `conversion_qty` (from `Order::products()` `withPivot`). Full definition in the `orders` module.

---

## `products` module

The core `products` / `product_units` / `units` entities were documented under `pos` above (POS is the primary consumer). The catalogue CRUD adds these dependencies:

### `settings` — Setting (key/value) 🟡
Read by `ProductController::index` via `Setting::get('near_expiry_days', 30)`. Backs configurable thresholds; full schema deferred to the settings/`reversa-data-master` pass.

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

### `products` — confirmed migration schema 🟢
Source: `2019_04_09_104651_create_products_table.php` + add-column migrations. Supplements the model-derived table under `pos`: `wholesale_prices` is a plain `string` column (JSON-encoded by the model mutator); `reward_point` (`2022_03_20`), `expiry_date`/`promotion_note` (`2026_08_26`) added later. `code` uniqueness is enforced only over live rows (unique index includes `deleted_at`).

---

## `customers` module

### `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. ✅ Fixed 2026-09-17: its add-migration's `up()` was commented out (column previously created out-of-band) — now properly creates `birthday2` as a nullable string. 🟢 |
| `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) | — | 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 (denormalised 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 | Σ order `total` |
| `points_total` | decimal(14,2) default 0 | yes | Σ `earned_point` |
| `categories_statistic` | longText (cast `object`) | no | JSON per-category/`attr_weight` aggregates |
| `created_at`/`updated_at` | timestamps | yes | |

### `customer_gift` — pivot (Customer ↔ Gift) 🟢
Source: `2022_05_20_151916_create_customer_gift_table.php` (full definition in the `gifts` module). Pivot columns used here: `points`, `note` (from `Customer::gifts()` `withPivot`).

---

## `orders` module

### `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) |
| `subtotal` | decimal default 0 | yes | Σ 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 | Σ `round(reward_point,1)*qty` (`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` when done (`2026_08_28`) |
| `points_awarded_at` | timestamp | no | nullable; set once when finalised (idempotency guard) |
| `count` | integer (unsigned) | no | number of **lines**, not total qty |
| `status` | enum(`draft`,`done`) default `draft` | yes | was `waiting`→`draft` via `2022_05_10` migration |
| `notes` | string | no | nullable |
| `discount_id` | integer (FK discounts) | no | nullable; **unused by current code** (legacy) |
| `created_at`/`updated_at` | timestamps | yes | `updated_at` drives the 24h `is_editable` window |
| `code` | string (appended) | — | `#QT78-{id}` |
| `is_editable` | bool (appended) | — | draft, or done within 24h |
| `debt_locked` | bool (appended) | — | a `pos_debt` ledger row exists |

### `order_product` — 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 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`. Related to `orders` via `discount_id`, but no controller path applies a discount by id.

| 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 | |

---

## `debts` module

No new tables. The debt overview screen (`DebtController@index`) is a read-only projection over entities already documented under the `customers` module: it filters `customers` on `debt_total > 0` and its action buttons write to the `customer_debts` A/R ledger (see `### customer_debts` and `### customers` above). The sidebar entry is a seeded `admin_menu` row (`uri = /debts`), not an application table.

## `gifts` module

### `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 | nullable; SoftDeletes |
| `created_at`/`updated_at` | timestamps | yes | |

**Appended (computed, not stored):** `quantity_available = quantity - used`. Consumed by `Customer::checkGiftAvailable` (strict `=== 0` out-of-stock test).

### `customer_gift` — pivot (Customer ↔ Gift) 🟢
Already listed under the `customers` module (redemption ledger). Repeated here for the `gifts` side of the relation: `Gift` is the target of `Customer::gifts()` = `belongsToMany(Gift)->withPivot('points','note')`.

## `brands` module

### `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/label name ("Nhãn hiệu") |
| `description` | string | no | nullable |
| `created_at`/`updated_at` | timestamps | yes | |

**Relations:** `Brand::products()` = `hasMany(Product)` via `products.brand_id` (a required FK on `Product`). Brands are never deletable from the UI.

---

## `categories` module

### `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/"Sữa") and `8` (medicine/"Thuốc") are special-cased in stats rollups |
| `name` | string | yes | category name ("Danh mục") |
| `description` | string | no | nullable |
| `parent_id` | unsignedInteger | no | nullable; self-reference for a two-level group→child hierarchy. Only children (`parent_id` not null) are assignable to products. Never set via the UI 🔴 |
| `created_at`/`updated_at` | timestamps | yes | |

**Relations:** `Category::products()` = `hasMany(Product)` via `products.category_id` (a required FK on `Product`). No `parent()`/`children()` relation is modelled despite `parent_id`. Categories are never deletable from the UI.

## `units` module

### `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 ("Đơn vị") |
| `description` | string | no | nullable |
| `category_id` | unsignedInteger | no | nullable; 🟡 declared but never read/written by any code — apparently dead/unused |
| `created_at`/`updated_at` | timestamps | yes | |

**Relations:** referenced by `product_units.unit_id` (FK) via `ProductUnit::unit()` = `belongsTo(Unit)`, and by `order_product.unit_id` (FK, `onDelete set null`). Note `products.unit` is a free **string** keyed by unit *name*, not an FK. Units are never deletable from the UI.

---