# Database Design — Repair Management System

Built for one business: **AppleTek** (not a multi-company system). `branches` exists so AppleTek can open more shops later; the first branch is AppleTek with code `AT`.

This is the planned database for **all phases (1–8)**. Build migrations from this file, phase by phase.
If a migration needs to differ from this file, update this file in the same commit.

---

## 1. Conventions (apply to every table unless the table says otherwise)

| Thing | Rule |
|---|---|
| Primary key | `id` BIGINT UNSIGNED auto-increment (pivot tables use a composite key) |
| Timestamps | `created_at`, `updated_at` (Laravel `timestamps()`) |
| Foreign keys | BIGINT UNSIGNED, `constrained()`, **ON DELETE RESTRICT** unless the table says `cascade` or `set null` |
| Status / type columns | `VARCHAR(30)` backed by a PHP Enum (not MySQL `ENUM`, so new values need no migration) |
| Money | `DECIMAL(12,2)`, default `0.00`, never FLOAT |
| Quantities | `INT UNSIGNED` on item rows; `INT` (signed) only in `stock_movements` |
| Phone numbers | `VARCHAR(15)`, digits only, international format `947XXXXXXXX` |
| Text | `utf8mb4` / `utf8mb4_unicode_ci` |
| Times | Stored in app timezone `Asia/Colombo` (Sri Lanka has no daylight saving) |
| Deleting | Money, stock and job records are **never deleted** — they are cancelled or voided. Catalogue/master data uses soft deletes (`deleted_at`) or `is_active` |
| `branch_id` | On every table holding shop activity (jobs, stock, money, messages). **Not** on shared catalogue tables (brands, models, parts, suppliers) or on line-item tables whose parent already has `branch_id` (e.g. `invoice_items`) — see Decisions |

**Name clash:** Laravel's queue already uses a table named `jobs`. Repair jobs are therefore stored in **`repair_jobs`** (model `RepairJob`). Child tables keep short names (`job_photos`, `job_notes`, …) and use the foreign key `repair_job_id`.

---

## 2. Core, users and settings (Phase 1)

### branches
A shop location. Every activity record belongs to one branch.

| Column | Type | Notes |
|---|---|---|
| id | bigint | |
| name | varchar(100) | e.g. "AppleTek" |
| code | varchar(10) unique | Short code used in document numbers, e.g. `AT` |
| address | text null | Printed on PDFs |
| phone | varchar(15) null | |
| email | varchar(150) null | |
| is_active | boolean default true | |
| timestamps | | |

### users
Staff logins. No public registration — admin creates users.

| Column | Type | Notes |
|---|---|---|
| id | bigint | |
| branch_id | FK → branches | Home branch |
| name | varchar(150) | |
| email | varchar(150) unique | Login |
| phone | varchar(15) null | |
| role | varchar(20) | Enum `UserRole`: `admin`, `front_desk`, `technician` |
| is_active | boolean default true | Inactive users cannot log in. Users are deactivated, never deleted (they are referenced by jobs, payments, stock) |
| email_verified_at | timestamp null | |
| password | varchar(255) | |
| remember_token | varchar(100) null | |
| last_login_at | timestamp null | |
| timestamps | | |

Indexes: `(branch_id, role)`.

### settings
Shop settings as key/value. `branch_id` NULL = applies to all branches; a branch row overrides it.

| Column | Type | Notes |
|---|---|---|
| id | bigint | |
| branch_id | FK → branches null, cascade | |
| key | varchar(100) | Full list with defaults: `App\Support\Settings::DEFAULTS`. Now: `shop_name`, `shop_logo`, `shop_address`, `shop_phone`, `shop_phone_2`, `shop_email`, `shop_whatsapp`, `whatsapp_mode` (`click_to_send`; Phase 8: `cloud_api`), `unit_code_prefix` (AT), `label_width_mm` (38), `label_height_mm` (25), `job_number_prefix` (RJ), `warranty_days_labour` (30), `warranty_days_parts` (30), `uncollected_reminder_days` (30), `inspection_fee` (0.00), `job_card_terms`, `tracking_page_message`, `hidden_damage_notice_title`, `hidden_damage_notice` (red box on the job card PDF and tracking page; empty = hidden). Later: `tracking_link_days` (90), `vat_enabled` (false), `vat_rate`, `invoice_terms` |
| value | text null | JSON-encoded |
| timestamps | | |

Unique: `(branch_id, key)`. Note: MySQL allows several NULLs in a unique index, so code must use `updateOrCreate` for global keys.

### number_sequences
Running document numbers. One row per prefix per year, e.g. key `RJ-2026`. Code: `App\Support\DocumentNumber::next($prefix)`.

| Column | Type | Notes |
|---|---|---|
| id | bigint | |
| key | varchar(50) unique | `{prefix}-{year}`, e.g. `RJ-2026`, `R-2026` |
| last_number | int unsigned default 0 | |
| timestamps | | |

Always read with `lockForUpdate()` inside the transaction that saves the document, so two saves never get the same number and a failed save does not use up a number.
Format: `{prefix}-{YYYY}-{4-digit number}`, restarting at 1 every year (Sri Lanka date). Grows past 9999 if needed.
Job prefix comes from setting `job_number_prefix` (default `RJ`). Other prefixes: receipt `R`, quotation `Q`, invoice `INV`, purchase `PO`, supplier return `SR`, transfer `TR`.
Examples: `RJ-2026-0001`, `R-2026-0001`. If a second branch is added, give it its own prefix (branch setting) so numbers never clash.

### Laravel framework tables (already exist)
`password_reset_tokens`, `sessions`, `cache`, `cache_locks`, `jobs` (queue), `job_batches`, `failed_jobs`. Unchanged.

---

## 3. Customers and devices (Phase 2)

### customers
People who bring devices. Shared across branches (a customer can visit any branch).

| Column | Type | Notes |
|---|---|---|
| id | bigint | |
| branch_id | FK → branches | Branch where the customer was first registered |
| name | varchar(150) | |
| phone | varchar(15) **unique** | Sri Lankan mobile, `947XXXXXXXX`. One customer per phone number |
| whatsapp_phone | varchar(15) null | NULL = same as `phone` |
| email | varchar(150) null | For email notifications |
| address | text null | |
| notes | text null | Staff-only notes |
| timestamps | | Only an **admin** can delete a customer, permanently, with all their devices, jobs, invoices, warranties, payments and messages (`App\Actions\DeleteCustomer`, reason + confirm, logged in `deletion_logs`). Stock stays correct: reservations are released; used parts stay used (stock history rows keep, only `repair_job_id` is cleared) |

Indexes: unique `phone`, `name`, `email`.

### device_types
Phone, Laptop, Tablet, Smart Watch, Console, Other…

| Column | Type | Notes |
|---|---|---|
| id | bigint | |
| name | varchar(50) unique | |
| sort_order | smallint default 0 | |
| is_active | boolean default true | |
| timestamps | | |

### brands
Apple, Samsung, Huawei, Dell, HP…

| Column | Type | Notes |
|---|---|---|
| id | bigint | |
| name | varchar(100) unique | |
| is_active | boolean default true | |
| timestamps | | |

### device_models
Specific models, e.g. "iPhone 13 Pro". Used for job intake and part compatibility.

| Column | Type | Notes |
|---|---|---|
| id | bigint | |
| brand_id | FK → brands | |
| device_type_id | FK → device_types | |
| name | varchar(100) | |
| is_active | boolean default true | |
| timestamps | | |

Unique: `(brand_id, device_type_id, name)`.

### devices
A customer's physical device. Lets us see the full repair history of one phone/laptop (by IMEI/serial) when it comes back. A new job for the same customer with the same IMEI/serial reuses the device row.

| Column | Type | Notes |
|---|---|---|
| id | bigint | |
| customer_id | FK → customers | |
| device_type_id | FK → device_types | |
| brand_id | FK → brands null | NULL when the brand is "Other" |
| brand_name | varchar(100) null | Typed brand when `brand_id` is NULL |
| device_model_id | FK → device_models null | NULL when the model is "Other" |
| model_name | varchar(100) null | Typed model when `device_model_id` is NULL |
| serial_number | varchar(100) null | IMEI 1 for phones (15 digits), serial for others. Saved without spaces/dashes |
| imei_2 | varchar(20) null | Second IMEI (dual SIM phones) |
| color | varchar(50) null | |
| storage | varchar(30) null | e.g. `128GB` |
| notes | text null | |
| timestamps | | |

Indexes: `customer_id`, `serial_number`, `imei_2`.

---

## 4. Repair jobs (Phases 2–4, 6)

### repair_jobs
The job card. One device in for repair = one row.

| Column | Type | Notes |
|---|---|---|
| id | bigint | |
| branch_id | FK → branches | |
| job_no | varchar(30) unique | Printed number, e.g. `RJ-2026-0001` |
| tracking_token | char(32) unique | Random token for the public tracking page / QR. Never expose `id` |
| customer_id | FK → customers | |
| device_id | FK → devices | |
| status | varchar(30) default `received` | Enum `JobStatus`: received, diagnosing, waiting_approval, waiting_parts, in_repair, testing, ready, delivered, cancelled, returned_unrepaired |
| priority | varchar(20) default `normal` | `normal`, `urgent` |
| problem_reported | text null | What the customer says is wrong (required if no quick-pick problem) |
| problems | json null | Quick-pick keys, e.g. `["screen_broken"]`. Labels in `App\Support\JobIntakeOptions::PROBLEMS` |
| condition_checks | json null | Quick-pick keys, e.g. `["scratches"]` (`JobIntakeOptions::CONDITIONS`) |
| device_condition | text null | Condition notes at intake |
| accessories | json null | Quick-pick keys, e.g. `["charger","case"]` (`JobIntakeOptions::ACCESSORIES`) |
| accessories_other | varchar(255) null | Other items left with the device |
| passcode_type | varchar(20) null | `pin`, `password`, `pattern` (pattern saved as dots, e.g. `1-4-7-8-9`) |
| passcode | text null | Optional. **Encrypted** (Laravel `encrypted` cast). Screen lock / password. Visible only to admin and the assigned technician. **Erased automatically** when the job is delivered, cancelled or returned unrepaired |
| estimated_cost | decimal(12,2) null | Rough estimate told at the counter |
| inspection_fee | decimal(12,2) null | Set from the `inspection_fee` setting when the customer rejects the quotation; the balance is then this fee minus payments |
| promised_on | date null | Expected delivery date told to the customer |
| received_by | FK → users | Front desk user who took it in |
| assigned_to | FK → users null | Technician |
| assigned_at | datetime null | |
| assigned_by | FK → users null | Admin / front desk who assigned the current technician. History in `job_assignments` |
| diagnosis | text null | "Fault found" (technician's findings) |
| recommended_repair | text null | What should be done |
| is_repairable | boolean null | Yes / no. NULL until diagnosed |
| estimated_repair_minutes | int unsigned null | Estimated repair time; choices in `App\Support\RepairTime` (1 day = 480) |
| diagnosed_by | FK → users null | |
| diagnosed_at | datetime null | |
| ready_at | datetime null | |
| delivered_at | datetime null | Handover time. `delivered` is set only by the Deliver device page (`App\Actions\DeliverJob`), never by a plain status change |
| delivered_by | FK → users null | Staff who handed it over |
| collected_by_name | varchar(150) null | Who actually collected it (may not be the customer) |
| collected_by_phone | varchar(15) null | |
| handover_notes | text null | Required when an accessory is not given back |
| accessories_returned | json null | Keys from `accessories` (plus `other` for `accessories_other`) ticked as given back on the Deliver device page |
| signature_path | varchar(255) null | Customer's signature at handover: PNG (re-drawn by GD) on the private disk, `jobs/{id}/signature-….png`. Shown on the full job card PDF, never on the public customer copy |
| balance_at_delivery | decimal(12,2) null | Balance still unpaid when handed over (0 normally) |
| balance_override_amount | decimal(12,2) null | Admin allowed delivery with this balance; a bigger balance needs a new permission |
| balance_override_by | FK → users null | Admin who allowed it |
| balance_override_at | datetime null | |
| balance_override_reason | text null | Required |
| cancelled_at | datetime null | |
| cancel_reason | text null | Also used for `returned_unrepaired` |
| warranty_id | FK → warranties null | Set when this job is a **warranty claim** on an earlier repair (FK added after `warranties` exists) |
| deleted_at | datetime null | **Soft delete.** Only admin deletes a job (wrong / duplicate job card). Hidden from every list, search, count and the tracking page; payments, stock movements and the job number stay. Reservations are released and open part requests cancelled when deleted. Normal jobs are cancelled, not deleted |
| deleted_by | FK → users null | Admin who deleted it |
| delete_reason | text null | Why it was deleted |
| timestamps | | |

Indexes: `(branch_id, status)`, `(assigned_to, status)`, `customer_id`, `device_id`, `created_at`, `promised_on`.

Accessories, problems and condition are stored as JSON key lists on `repair_jobs` (fixed quick-pick lists in code). There are no separate accessory tables.

### job_photos
Photos of the device (intake condition, during repair, finished). Files are on the **private** disk.

| Column | Type | Notes |
|---|---|---|
| id | bigint | |
| branch_id | FK → branches | |
| repair_job_id | FK → repair_jobs | |
| disk | varchar(20) default `local` | Laravel's `local` disk = `storage/app/private` |
| path | varchar(255) | Resized JPEG, max 1600px long side, quality 75: `jobs/{job_id}/{uuid}.jpg` |
| thumb_path | varchar(255) | 320×320 JPEG for the gallery: `jobs/{job_id}/thumbs/{uuid}.jpg` |
| stage | varchar(20) default `intake` | Enum `PhotoStage`: `intake`, `repair`, `completed` |
| caption | varchar(255) null | |
| size_kb | int unsigned null | |
| uploaded_by | FK → users | |
| timestamps | | |

Index: `repair_job_id`. Up to 10 photos per job. Files are shown only through logged-in routes (`jobs.photos.show` / `jobs.photos.thumb`).

### job_status_histories
Every status change of a job. Feeds the timeline on the job page and the public tracking page.

| Column | Type | Notes |
|---|---|---|
| id | bigint | |
| branch_id | FK → branches | |
| repair_job_id | FK → repair_jobs | |
| from_status | varchar(30) null | NULL for the first row |
| to_status | varchar(30) | |
| note | text null | |
| is_public | boolean default true | Show on the public tracking page |
| changed_by | FK → users null | NULL = system |
| created_at | timestamp | No `updated_at` — history is never edited |

Index: `(repair_job_id, created_at)`.

### job_assignments
One row every time a job is assigned, reassigned or taken off a technician. Never edited.

| Column | Type | Notes |
|---|---|---|
| id | bigint | |
| branch_id | FK → branches | |
| repair_job_id | FK → repair_jobs | |
| from_user_id | FK → users null | Previous technician (NULL = was not assigned) |
| to_user_id | FK → users null | New technician (NULL = taken off) |
| assigned_by | FK → users | Admin / front desk who did it |
| note | varchar(255) null | e.g. "Saman on leave" |
| created_at | timestamp | |

Index: `(repair_job_id, created_at)`. Code: `App\Actions\AssignTechnician`.

### passcode_views
Audit log: one row every time someone presses "Show" on a device's screen lock. Never edited or deleted.

| Column | Type | Notes |
|---|---|---|
| id | bigint | |
| branch_id | FK → branches | |
| repair_job_id | FK → repair_jobs | |
| user_id | FK → users | Admin or the assigned technician |
| ip_address | varchar(45) null | |
| user_agent | varchar(255) null | Browser / phone |
| viewed_at | timestamp | |

Indexes: `(repair_job_id, viewed_at)`, `(user_id, viewed_at)`. The screen lock is never printed in a page; it is fetched by POST `/jobs/{job}/passcode` only when "Show" is pressed.

### job_notes
Notes on a job: internal (staff only) or for the customer.

| Column | Type | Notes |
|---|---|---|
| id | bigint | |
| branch_id | FK → branches | |
| repair_job_id | FK → repair_jobs | |
| user_id | FK → users | |
| type | varchar(20) | Enum `NoteType`: `internal` (staff only) or `customer` (also shown on the tracking page, text and date only — never the staff name) |
| body | text | |
| timestamps | | Notes are not edited or deleted |

Index: `(repair_job_id, type)`.

---

## 5. Messages and notifications (Phases 3, 7, 8)

### message_templates
Editable text for emails and WhatsApp messages (Settings > Messages). One row per event and channel. Placeholders: `{customer_name}`, `{job_no}`, `{device}`, `{status}`, `{tracking_link}`, `{pdf_link}`, `{amount}`, `{balance}`, `{shop_name}`, `{shop_phone}` (list in `App\Support\MessageTemplates::PLACEHOLDERS`). Default English text is in `MessageTemplates::DEFAULTS`, seeded by `MessageTemplateSeeder` and used as a fallback if a row is missing. Text is UTF-8 (Sinhala and Tamil work).

| Column | Type | Notes |
|---|---|---|
| id | bigint | |
| branch_id | FK → branches null, cascade | NULL = default for all branches |
| key | varchar(50) | Enum `MessageEvent`: `job_created`, `quotation_ready`, `waiting_parts`, `ready_for_pickup`, `delivered`, `uncollected_reminder` (later: `warranty_expiring`) |
| channel | varchar(20) | `email`, `whatsapp` |
| subject | varchar(200) null | Email only |
| body | text | |
| is_active | boolean default true | |
| timestamps | | |

Unique: `(branch_id, key, channel)`.

### notification_logs
Every message sent or prepared (email, WhatsApp click-to-send link, later WhatsApp Cloud API). Also used to stop the scheduler sending the same reminder twice. Enums: `NotificationChannel`, `NotificationStatus` (`pending` = waiting in the queue). Emails are saved here as `pending`, then sent by the queued `SendCustomerEmail` job (3 tries), which sets `sent` or `failed` + `error`. Shown on the job page, Messages tab (admin and front desk).

| Column | Type | Notes |
|---|---|---|
| id | bigint | |
| branch_id | FK → branches | |
| repair_job_id | FK → repair_jobs null | |
| customer_id | FK → customers null | |
| channel | varchar(20) | `email`, `whatsapp_link`, `whatsapp_api`, `sms` |
| template_key | varchar(50) null | |
| recipient | varchar(150) | Email or phone |
| subject | varchar(200) null | |
| body | text | Final message after placeholders |
| status | varchar(20) | `pending`, `sent`, `failed`, `opened` (link clicked by staff) |
| error | text null | |
| provider_message_id | varchar(100) null | WhatsApp Cloud API id (Phase 8) |
| sent_by | FK → users null | NULL = scheduler/system |
| sent_at | datetime null | |
| timestamps | | |

Indexes: `repair_job_id`, `(template_key, repair_job_id)`, `(branch_id, status)`.

### whatsapp_reminders
"WhatsApp reminders to send today". Made daily at 9:00 by `app:uncollected-reminders` (`App\Actions\SendUncollectedReminders`) for jobs **Ready** longer than `uncollected_reminder_days` (Settings), repeated every that many days while the device waits (a reminder sent by hand from the job page also counts). Customers without a mobile number, or with the WhatsApp template turned off, get no row. Staff tap click-to-send on the Reminders page (admin, front desk): `JobWhatsAppController` marks the row `sent` with the `notification_logs` row. The same command emails the customer (template `uncollected_reminder`, `notification_logs` stops repeats) and closes rows whose device was collected.

| Column | Type | Notes |
|---|---|---|
| id | bigint | |
| branch_id | FK → branches | |
| repair_job_id | FK → repair_jobs | |
| event | varchar(50) | `MessageEvent` (`uncollected_reminder`) |
| due_on | date | The day it was put on the list |
| status | varchar(20) | `ReminderStatus`: `pending`, `sent`, `skipped` |
| notification_log_id | FK → notification_logs null | The WhatsApp message staff opened |
| done_by | FK → users null | NULL = closed by the scheduler (device collected) |
| done_at | datetime null | |
| timestamps | | |

Indexes: unique `(repair_job_id, event, due_on)`, `(status, due_on)`.

---

## 6. Quotations and approval (Phase 4)

### quotations
A price offer for a job. A job can have several versions. Drafts can be edited (one draft at a time). The **latest sent** version is the active one; when a new version is sent, older sent ones become `superseded`. Sent quotations are never edited. Code: `App\Actions\SaveQuotation`, `App\Actions\SendQuotation`, PDF `App\Support\QuotationPdf`.

| Column | Type | Notes |
|---|---|---|
| id | bigint | |
| branch_id | FK → branches | |
| repair_job_id | FK → repair_jobs | |
| quotation_no | varchar(30) unique | `Q-2026-0001` (DocumentNumber prefix `Q`) |
| version | smallint unsigned | 1, 2, 3 … per job. Unique `(repair_job_id, version)` |
| status | varchar(20) | Enum `QuotationStatus`: `draft`, `sent`, `approved`, `rejected`, `expired`, `superseded` |
| subtotal | decimal(12,2) | |
| discount_amount | decimal(12,2) | |
| total | decimal(12,2) | |
| valid_until | date null | |
| notes | text null | Shown to customer |
| created_by | FK → users | Technician, front desk or admin |
| sent_by | FK → users null | Front desk / admin only |
| sent_at | datetime null | Sending moves the job to `waiting_approval` and emails `quotation_ready` with the PDF |
| responded_at | datetime null | |
| response_method | varchar(20) null | `in_person`, `phone`, `whatsapp`, `online` (tracking page button) |
| response_by_name | varchar(150) null | Who approved/rejected |
| response_note | text null | |
| response_ip | varchar(45) null | For online approval |
| recorded_by | FK → users null | Staff member who recorded the answer by hand (phone, in person, WhatsApp). NULL = the customer online |
| decision_seen_at | timestamp null | NULL = new customer answer not opened yet by admin / front desk (red badge on dashboard and job list) |
| timestamps | | |

Indexes: unique `(repair_job_id, version)`, `(repair_job_id, status)`. Money is worked out in cents on the server (`App\Support\Money`), never taken from the browser.

### quotation_items
Lines of a quotation. Selling prices only — no cost shown.

| Column | Type | Notes |
|---|---|---|
| id | bigint | |
| quotation_id | FK → quotations, cascade | |
| item_type | varchar(20) | Enum `QuotationItemType`: `part`, `labour`, `other` |
| part_id | FK → parts null | Set when the line was picked from stock (name and selling price fill in); NULL for a part typed by hand |
| needs_ordering | boolean default false | Part line: "Must order". Approving moves the job to `waiting_parts` (also when a stock part has too little on hand) |
| description | varchar(255) | |
| quantity | int unsigned | |
| unit_price | decimal(12,2) | |
| line_total | decimal(12,2) | |
| sort_order | smallint default 0 | |
| timestamps | | |

---

## 7. Parts, suppliers and stock (Phase 5, 8)

### part_categories
Created with 14 rows by the migration: Display (DSP), Battery (BAT), Charging Port (CHG), Back Glass (BKG), Camera (CAM), Speaker (SPK), Microphone (MIC), Keyboard (KBD), Motherboard (MBD), IC (IC), Flex Cable (FLX), Housing (HSG), SSD/RAM (MEM), Other (OTH). Admin can add more.

| Column | Type | Notes |
|---|---|---|
| id | bigint | |
| name | varchar(100) unique | |
| code | varchar(10) unique | SKU prefix, e.g. `DSP` → `DSP-00001`. Counter in `number_sequences` (key `SKU-DSP`, never restarts) |
| sort_order | smallint default 0 | |
| is_active | boolean default true | Off = hidden when adding parts. No delete |
| timestamps | | |

### parts
The parts catalogue (shared across branches). **No stock column** — stock comes from `stock_movements`.

| Column | Type | Notes |
|---|---|---|
| id | bigint | |
| parent_id | FK → parts null | **Variant** of this main product (e.g. same display, other colour). One level only: a variant has no variants |
| part_category_id | FK → part_categories | |
| part_subcategory_id | FK → part_subcategories null | Must belong to `part_category_id` |
| brand_id | FK → brands null | Brand the part is for / made by |
| sku | varchar(50) unique | Our code. Made automatically from the category code (`DSP-00012`) when left empty; admin can edit it |
| barcode | varchar(100) null unique | Phase 8 scanning |
| name | varchar(200) | Short: model + key part + type, e.g. "iPhone 13 Display OLED" |
| variant_name | varchar(100) null | e.g. "Black". Required when `parent_id` is set |
| grade | varchar(20) null | Enum `PartGrade`: `original`, `oem`, `high_copy`, `copy` |
| notes | text null | |
| unit | varchar(20) default `pcs` | Enum `ProductUnit`: `pcs`, `set`, `meter` (stock quantities are whole numbers) |
| cost_price | decimal(12,2) | **Average cost**, updated on each stock-in. Hidden from technicians |
| selling_price | decimal(12,2) | **Retail** price (the default price level). Other levels in `part_prices` |
| min_selling_price | decimal(12,2) null | Staff (non-admin) cannot invoice / quote / sell below it (`App\Support\MinimumPrice`). Must be ≤ Retail; no level price may be below it |
| tax_category | varchar(20) default `standard` | Enum `TaxCategory`: `standard`, `zero`, `exempt` (used when VAT is on) |
| is_serialized | boolean default false | Each unit has its own serial / barcode (unit tracking itself is Phase 9, `stock_units`) |
| min_stock | int unsigned default 0 | **Reorder level** (low-stock warning). 0 = no warning ("order when needed") |
| location | varchar(50) null | Shelf / bin, e.g. `A3-02` |
| photo_path | varchar(255) null | Private disk: `parts/{id}/{uuid}.jpg` (800px) + `-thumb.jpg` (200px) |
| warranty_days | smallint unsigned null | Default warranty when this part is billed. NULL = use setting `warranty_days_parts` |
| is_active | boolean default true | |
| timestamps, deleted_at | | Soft delete |

Indexes: `name`, `part_category_id`.

### part_subcategories
Sub-categories inside a category (e.g. Display › OLED). Managed on the category edit page (admin). Name unique per category (any letter case). No delete: turn off.

| Column | Type | Notes |
|---|---|---|
| id | bigint | |
| part_category_id | FK → part_categories, cascade | |
| name | varchar(100) | |
| sort_order | smallint unsigned | |
| is_active | boolean default true | |
| timestamps | | |

### price_levels
Selling price levels: Retail (default), Wholesale, Technician (seeded; admin can rename, add or turn off — Parts › Price levels). The default level's price is `parts.selling_price`.

| Column | Type | Notes |
|---|---|---|
| id | bigint | |
| code | varchar(20) unique | Made from the name when added, never changes. CSV column `price_<code>` |
| name | varchar(50) unique | |
| is_default | boolean | Exactly one (Retail), always active |
| sort_order | smallint unsigned | |
| is_active | boolean default true | |
| timestamps | | |

### part_prices
A product's price for a level other than Retail. No row = same as Retail (`Part::priceFor()`).

| Column | Type | Notes |
|---|---|---|
| part_id | FK → parts, cascade | |
| price_level_id | FK → price_levels, cascade | |
| price | decimal(12,2) | |
| timestamps | | |

Primary key `(part_id, price_level_id)`.

### part_price_changes
Price history. One row per change; never edited or deleted. Cost, Retail and minimum price are recorded automatically by the `Part` model (`App\Support\PriceHistory`); level prices by `App\Support\LevelPrices`. Cost rows are shown to admin only; technicians see no prices.

| Column | Type | Notes |
|---|---|---|
| id | bigint | |
| part_id | FK → parts, cascade | |
| price_type | varchar(20) | Enum `PriceType`: `cost`, `selling` (Retail), `min_selling`, `level` |
| price_level_id | FK → price_levels null | For `level` |
| old_price | decimal(12,2) null | NULL = there was none |
| new_price | decimal(12,2) null | NULL = removed |
| source | varchar(20) | `created`, `edit`, `import` (CSV), `stock_in` (new average cost) |
| changed_by | FK → users null | |
| created_at | timestamp | No `updated_at` |

Index: `(part_id, created_at)`.

**Products CSV** (`App\Support\ProductCsv`, admin): template, export, import. Import updates by code (blank / new code = add), changes only the columns in the file, and is **all or nothing**: any problem → nothing saved + an error report (row, column, value, problem) on the private disk (`imports/`). Stock and photos are never imported.

### part_device_model
Which device models a part fits (one part can fit many models).

| Column | Type | Notes |
|---|---|---|
| part_id | FK → parts, cascade | |
| device_model_id | FK → device_models, cascade | |

Primary key: `(part_id, device_model_id)`. Index: `device_model_id`.

### suppliers
Where we buy parts. Shared across branches.

| Column | Type | Notes |
|---|---|---|
| id | bigint | |
| name | varchar(150) | |
| contact_person | varchar(150) null | |
| phone | varchar(15) null | |
| whatsapp | varchar(15) null | `947XXXXXXXX` (replaces `phone_alt`) |
| email | varchar(150) null | |
| address | text null | |
| notes | text null | |
| is_active | boolean default true | |
| timestamps, deleted_at | | |

### purchases
Stock in (goods received from a supplier). Stock is added only when status becomes `received`. For now a purchase is saved as `received` straight away (no draft screen): `App\Actions\SavePurchase` saves the purchase, its lines, one `purchase_in` stock movement per line and the new average cost in **one transaction**. Saved purchases are not edited. The same supplier bill number cannot be entered twice for one supplier.

| Column | Type | Notes |
|---|---|---|
| id | bigint | |
| branch_id | FK → branches | |
| supplier_id | FK → suppliers null | NULL = bought from a shop/cash purchase |
| purchase_no | varchar(30) unique | `PO-2026-0001` |
| supplier_invoice_no | varchar(100) null | |
| purchase_date | date | |
| status | varchar(20) default `received` | Enum `PurchaseStatus`: `draft`, `received`, `cancelled` |
| subtotal | decimal(12,2) | |
| discount_amount | decimal(12,2) | |
| total | decimal(12,2) | |
| paid_amount | decimal(12,2) | Simple supplier payment tracking (see Decisions) |
| payment_status | varchar(20) default `unpaid` | Enum `SupplierPaymentStatus`: `unpaid`, `partial`, `paid`. Amount owed = `total − paid_amount` (received purchases) |
| notes | text null | |
| bill_path | varchar(255) null | Photo (JPEG, max 1600px) or PDF of the supplier bill, private disk: `purchases/{id}/bill-{uuid}.jpg|pdf` |
| created_by | FK → users | |
| received_by | FK → users null | |
| received_at | datetime null | |
| timestamps | | |

Indexes: `(branch_id, purchase_date)`, `(supplier_id, supplier_invoice_no)`.

### purchase_items

| Column | Type | Notes |
|---|---|---|
| id | bigint | |
| purchase_id | FK → purchases, cascade | |
| part_id | FK → parts | |
| quantity | int unsigned | |
| unit_cost | decimal(12,2) | |
| line_total | decimal(12,2) | |
| timestamps | | |

### Purchasing (built 2026-11-13): purchase orders, GRN, supplier payments

`purchases` is the **GRN** (goods received note), against a purchase order or direct. New GRN numbers: `GRN-YEAR-0001` (old rows keep `PO-…`). Added columns:

| Column | Type | Notes |
|---|---|---|
| purchase_order_id | FK → purchase_orders null | Received against this order |
| supplier_invoice_date | date null | |
| extra_cost | decimal(12,2) default 0 | Transport etc. **Landed cost**: spread over the lines by value |
| extra_cost_on_bill | boolean | On the supplier bill (added to `total`, owed to the supplier) or paid elsewhere (only in item costs) |
| extra_cost_note | varchar(255) null | |

`total` = goods + extra cost if on the bill. `paid_amount` / `payment_status` are a **cache** of `supplier_payment_allocations`, updated in the same transaction. A GRN on credit needs a supplier.

`purchase_items` added: `purchase_order_item_id` FK null, `free_quantity` int unsigned (stock in, no cost), `landed_unit_cost` decimal(12,2) = (quantity × unit_cost + share of extra) ÷ (quantity + free). Stock movements, serial units and the average cost use the landed cost.

**purchase_orders**: id, branch_id, `po_no` unique (`PO-YEAR-0001`), supplier_id, `status` (`draft`, `sent`, `partly_received`, `received`, `cancelled`; enum `PurchaseOrderStatus`), order_date, expected_on null, total (expected), notes, created_by, sent_at, cancelled_at, cancel_reason, timestamps. No stock or money effect. Only drafts are edited; cancel only before goods arrive. PDF (dompdf) and WhatsApp click-to-send with a **signed** PDF link (60 days, `APP_URL`).
**purchase_order_items**: id, purchase_order_id (cascade), part_id, quantity, `received_qty` (raised by GRN lines; a GRN line cannot exceed what is still to come — free items are extra), unit_cost, line_total, timestamps.

**supplier_payments**: id, branch_id, `payment_no` unique (`SP-YEAR-0001`; migrated old payments `SP-OLD-<grn id>`), supplier_id null (NULL = cash purchase from a shop), paid_on, `method` (`cash`, `bank_transfer`, `cheque`, `other`; enum `SupplierPaymentMethod`), cheque_no, cheque_date, reference, amount, `from_drawer` (cash taken from the shop drawer: lowers expected cash in the Daily Summary), note, created_by, timestamps. Never edited or deleted.
**supplier_payment_allocations**: id, supplier_payment_id (cascade), purchase_id, amount, timestamps. A payment for one GRN cannot exceed what is owed on it; a payment to the supplier pays the oldest unpaid GRNs first; the rest is an **advance**, used automatically by the next GRN (`RecordSupplierPayment::applyCredit`).

**Supplier balance** = received GRN totals − payments (minus = advance). **Statement** (`App\Support\SupplierStatement`): opening balance, bills and payments by date with a running balance.

### POS, cashier shifts and customer returns — REMOVED (2026-11-14)

The owner decided the system is for repairs and the store's repair items only: no counter sales. Removed: the POS screen and sales, held bills / sales quotations, cashier shifts and cash in / out, customer returns and credit notes, the `cashier` role (cashier users became `front_desk`), credit customers / price levels on customers, `parts.pos_quick`, and the POS settings.

The tables stay so old records and the books keep working: `pos_drafts`, `cash_shifts`, `cash_movements`, `sales_returns`, `sales_return_items`, `credit_notes`, and the columns `invoices.kind` (`pos` rows are old shop sales), `invoices.cash_shift_id`, `payments.cash_shift_id`, `payments.credit_note_id`, `customers.credit_allowed / credit_limit / price_level_id`. Nothing writes to them any more. Old shop sales show as plain invoice numbers in reports; deleting a customer turns their old shop sales into walk-in sales.

Faulty stock now comes only from supplier returns that were refused (`supplier_rejected`) and old customer returns.

### Accounting: double-entry books (built 2026-11-13)

Tables `accounts` (code, name, type `asset|liability|equity|income|expense`, description, `system_key`, is_active), `journal_entries` (branch_id, `entry_no` `JV-YEAR-0001`, entry_date, source_type/source_id, description, reversal_of_id, created_by, created_at — never updated), `journal_lines` (journal_entry_id, branch_id, entry_date, account_id, debit, credit decimal(14,2), customer_id, supplier_id, memo).

**Poster** (`App\Support\Accounting\Poster`) is the only writer. Lines use a `system_key` (`account`) or an `account_id`, amounts in cents. Debits must equal credits: otherwise nothing is saved, the problem is written to the log and `UnbalancedEntry` is thrown (the whole money / stock transaction rolls back). No entry may be dated on or before the setting **`books_locked_until`** (month lock), except the year-end closing entry. Corrections: `Poster::reverse` / `reverseSource`.

**Chart** (seeded): 1100 Cash drawer, 1110 Cash in safe, 1120 Bank – main account (+ 1121… one per added bank account), 1130 Card payments to receive, 1150 Cheques in hand, 1200 Accounts receivable, 1300 Stock, 1310 Supplier returns pending, 1400 VAT receivable, 1500 Prepayments, 1600 Equipment, 1690 Accumulated depreciation; 2100 Accounts payable, 2150 Cheques issued, 2200 Customer advances and store credit, 2300 VAT payable, 2400 Other payables, 2500 EPF / ETF payable, 2600 Loans; 3100 Owner capital, 3200 Retained earnings, 3300 Drawings; 4100 Sales – products, 4200 / 4210 Repair income labour / parts, 4300 Other income, 4400 Discounts received, 4900 Sales returns, 4910 Discounts allowed; 5100 / 5110 Cost of products sold / repair parts, 5200 Stock adjustments; 6000–6900 expenses (rent, salaries, EPF/ETF, electricity, water, telephone, transport, repairs, advertising, stationery, card fees, bank charges, licences, depreciation, cash over/short, petty cash, general). Admin adds accounts; system accounts keep their type and stay active.

**Automatic posting** (`App\Support\Accounting\Ledger` + the document actions), in the same transaction as the event:

| Event | Entry |
|---|---|
| POS sale / cancel | Dr cash / card / bank / credit note / receivable; Cr sales, VAT; Dr cost of products, Cr stock. Cancel = reversal |
| Repair invoice finalized | Dr receivable (total) + discounts allowed; Cr labour / parts / other income + VAT. Advances on it: Dr customer advances, Cr receivable (`invoice_deposit`) |
| Repair invoice voided | invoice entry reversed; money paid on it: Dr receivable, Cr customer advances (`invoice_void`) |
| Payment received (repair) | Dr cash drawer / card / bank / cheques in hand; Cr receivable (finalized invoice) or customer advances. Refund: reverse sides |
| Customer return | see Customer returns |
| GRN | Dr stock (goods + extra cost); Cr payable (bill) and cash in safe (extra cost not on the bill) |
| Supplier payment | Dr payable; Cr drawer / safe / bank, or cheques issued |
| Supplier return | see supplier_returns |
| Stock adjustment / count / damaged / faulty written off | Dr or Cr stock against stock adjustments (opening stock: owner capital) |
| Part used on / taken off a job | Dr cost of repair parts, Cr stock (and back) |
| Shift cash in / out, over / short | see Cashier shifts |
| Expense | Dr expense category; Cr safe / bank account / cheques issued |
| Cheque realised | received: Dr bank, Cr cheques in hand; issued: Dr cheques issued, Cr bank |
| Cheque bounced | received: Dr receivable (or advances), Cr cheques in hand + a refund payment row (invoice owed again); issued to supplier: Dr cheques issued, Cr payable + a negative supplier payment, the GRN allocations removed; issued for an expense: Cr other payables |
| Money transfer | Dr to-account, Cr from-account (drawer, safe, card, cheques in hand, banks) |
| Manual entry (admin) | any balanced lines; reversible |
| Year close | income and expense accounts to zero, the difference to retained earnings, dated the year end |

Deleting a customer reverses the entries of their payments, cheques and invoices; a customer with POS sales cannot be deleted. Earlier events (before this was built) are not back-posted: enter opening balances with a manual entry.

**bank_accounts**: id, name, bank_name, account_no, account_id (its ledger account), is_default (bank transfers / cards go here), is_active, timestamps. Seeded: "Main bank account" on 1120.

**cheques**: id, branch_id, `direction` (`received`, `issued`), cheque_no, bank_name, cheque_date, amount, `status` (`pending`, `realised`, `bounced`), customer_id / supplier_id / party, payment_id / supplier_payment_id / expense_id, bank_account_id, realised_on, bounced_on, note, created_by, timestamps. Made automatically by a customer payment with method `cheque` (number in `payments.reference`), a supplier payment by cheque, or an expense paid by cheque.

**expenses**: id, branch_id, `expense_no` (`EX-YEAR-0001`), expense_date, account_id (category: an expense account), payee, amount, `paid_from` (`cash_in_safe`, `bank`, `cheque`), bank_account_id, reference (bill / cheque no.), description, receipt_path (private disk, photos resized with GD), voided_at, voided_by, void_reason, created_by, timestamps. Small cash from the drawer stays a shift cash out.

**financial_years**: id, name (`2026/27`), starts_on, ends_on (Sri Lanka: 1 April – 31 March), status (`open`, `closed`), closed_at, closed_by, closing_entry_id. **period_closings**: history of month locks / reopens (action, locked_until, reason, user_id, created_at). Only past months can be closed; reopening needs a reason and is not possible inside a closed financial year.

Pages (admin, gate `manage-accounts`): Reports (P&L, balance sheet, trial balance), chart of accounts + ledger per account, journal, manual entry, expenses, bank & transfers, cheques, periods.

### stock_movements — **single source of truth for stock**
Every change in stock is one row. Rows are **never updated or deleted**; mistakes are fixed with an `adjustment` row.

| Column | Type | Notes |
|---|---|---|
| id | bigint | |
| branch_id | FK → branches | Which branch's shelf |
| part_id | FK → parts | |
| quantity | int (signed) | `+` = in, `−` = out |
| type | varchar(30) | Enum `StockMovementType`: `purchase_in`, `job_used`, `job_returned`, `reserved`, `unreserved`, `adjustment_in`, `adjustment_out`, `supplier_return`, `damaged`. Opening stock is an `adjustment_in` with note "Opening stock…". POS: `sale`, `sale_reversed`. Returns: `customer_return`, `return_faulty`, `faulty_to_supplier`, `faulty_written_off` (faulty rows are not shelf stock). Supplier returns: `supplier_replacement` (+ shelf), `supplier_rejected` (+ faulty). Later phases add `transfer_out`, `transfer_in` |
| unit_cost | decimal(12,2) | Cost per unit at that moment (for profit and stock value) |
| reference_type | varchar(50) null | Morph: `purchase_item`, `job_part`, `supplier_return_item`, `stock_transfer_item`, `invoice_item` |
| reference_id | bigint unsigned null | |
| repair_job_id | FK → repair_jobs null | Shortcut for "parts used on this job" |
| note | varchar(255) null | Reason. Required for `adjustment_in`, `adjustment_out`, `damaged` (the adjustment form requires it for every type) |
| created_by | FK → users null | |
| created_at | timestamp | No `updated_at` (immutable) |

Indexes: `(part_id, branch_id)`, `(reference_type, reference_id)`, `(branch_id, created_at)`, `repair_job_id`, `type`.

### stock_units — serial numbers (one row per physical unit of an `is_serialized` product)

**Code rule (2026-11-14):** a unit that arrives with a serial / IMEI uses it as its `unit_code` (spaces removed, capitals; it must not already exist as a code or as a serial of that product). Only units without one get a generated code `PREFIX-YY-000123`. `unit_code` is varchar(100).
Built 2026-11-13. Every unit received (GRN / stock in / adjustment in) gets **our own code**: `PREFIX-YY-000123` (`App\Support\UnitCode`: prefix = setting `unit_code_prefix`, default `AT`; 2-digit year; running number from `number_sequences` key `UNIT-YEAR`, row lock; never repeated, even if the prefix changes). The supplier serial / IMEI is kept in `serial_no` when typed at the GRN. `stock_movements` stays the only source of truth for **quantities**; units add **identity**. Units are changed only by `App\Actions\StockUnits`, called by `RecordStockMovement` inside its transaction:

- `purchase_in` / `adjustment_in` → new units (`in_stock`) with new codes
- `reserved` / `unreserved` → scanned units, or the oldest / newest
- `job_used`, `adjustment_out`, `damaged`, `supplier_return` (and later POS `sale`) → **the units must be scanned** (code or supplier serial); count must equal the quantity
- `job_returned` → the units of that job part go back `in_stock`
- Stock count cannot change a serialized product (use Adjust stock with scanning)
- Turning `is_serialized` on gives codes to the stock on hand (reservations kept); it cannot be turned off while units are on the shelf

| Column | Type | Notes |
|---|---|---|
| id | bigint | |
| part_id | FK → parts | |
| branch_id | FK → branches | Where the unit is |
| unit_code | varchar(30) unique | Our label barcode (Code 128) |
| serial_no | varchar(100) null | Supplier serial / IMEI. Unique per product (checked at the GRN) |
| status | varchar(30) | Enum `StockUnitStatus`: `in_stock`, `reserved`, `sold`, `used_in_repair`, `returned_by_customer`, `returned_to_supplier`, `damaged`, `written_off` (adjustment out) |
| purchase_item_id | FK → purchase_items null | GRN line it came in on (NULL = stock coded later) |
| supplier_id | FK → suppliers null | From the GRN |
| unit_cost | decimal(12,2) | Cost when received. Admin only |
| repair_job_id | FK → repair_jobs null | Reserved for / used on |
| job_part_id | FK → job_parts null | The job part it was used as |
| invoice_item_id | FK → invoice_items null | Sold on (POS, later) |
| timestamps | | |

Indexes: `(part_id, branch_id, status)`, `serial_no`, `repair_job_id`.

### stock_unit_events
A unit's history; also the link between a unit and the stock movement that moved it (replaces the planned `stock_movement_units`). Never edited or deleted.

| Column | Type | Notes |
|---|---|---|
| id | bigint | |
| stock_unit_id | FK → stock_units, cascade | |
| event | varchar(30) | `received`, `added`, `coded`, `reserved`, `unreserved`, `used_in_repair`, `returned_from_repair`, `sold`, `returned_by_customer`, `returned_to_supplier`, `damaged`, `written_off` |
| from_status / to_status | varchar(30) | |
| stock_movement_id | FK → stock_movements null | NULL for `coded` |
| repair_job_id | FK → repair_jobs null | |
| note | varchar(255) null | |
| created_by | FK → users null | |
| created_at | timestamp | |

**Barcodes and labels:** Code 128 SVG drawn by `App\Support\Code128` (no package). Serialized units: the unit code. Other products: one barcode per product — the supplier EAN (`parts.barcode`) or the product code (`sku`). Labels (product short name, code, Retail price, barcode) print at the label printer size from Settings (`label_width_mm` × `label_height_mm`, default 38 × 25) or on A4 sheets: for a GRN, for chosen units, or N copies of a product label.

### job_parts
Parts actually used on a job (Phase 5). `App\Actions\JobParts::use()` creates the row and a `job_used` stock movement (`reference_type` = `job_part`) in one transaction; the job's own reservation of that part is released first. `return()` adds a `job_returned` movement and marks the row returned, with a required reason. Reserve / release are `reserved` / `unreserved` stock movements (no row here). When a job is closed (cancelled, quotation rejected → returned unrepaired, delivered) `ChangeJobStatus` releases all its reservations in the same transaction.

| Column | Type | Notes |
|---|---|---|
| id | bigint | |
| branch_id | FK → branches | |
| repair_job_id | FK → repair_jobs | |
| part_id | FK → parts | |
| quantity | int unsigned | |
| unit_price | decimal(12,2) | Selling price charged |
| unit_cost | decimal(12,2) | Cost at time of use. Hidden from technicians |
| status | varchar(20) | Enum `JobPartStatus`: `used`, `returned` (taken back to stock — creates a `job_returned` movement) |
| added_by | FK → users | |
| returned_by | FK → users null | |
| returned_at | datetime null | |
| return_reason | varchar(255) null | Required when returning |
| timestamps | | |

Index: `repair_job_id`, `part_id`.

### part_reservations — not used
Changed 2026-11-13: reservations are rows in `stock_movements` instead (`reserved` = −qty, `unreserved` = +qty, always with `repair_job_id`), so the stock ledger shows them with everything else. Reserved for a job = −SUM(quantity) of its `reserved`/`unreserved` rows. Using a part on a job (`job_used`) first releases that job's own reservation. No separate table is created.

### part_requests
A job needs a part that is out of stock or not in the catalogue. Made from the job's Parts tab ("Request part", any role working on the job); the job moves to `waiting_parts`. Admin / front desk order them on the **Part Requests** page, grouped by supplier, with one WhatsApp click-to-send message per supplier (status → `ordered`). When a purchase (stock in) is saved, `SavePurchase` calls `App\Actions\PartRequests::receive()` in the same transaction: the oldest open requests for each part bought (same branch) are marked `received` while the quantity lasts, and the part is **reserved** for the request's job (`reserved` stock movement). Staff see "Part arrived for job RJ-…" on the dashboard and the job page until someone presses OK (`arrival_seen_at`).

| Column | Type | Notes |
|---|---|---|
| id | bigint | |
| branch_id | FK → branches | |
| repair_job_id | FK → repair_jobs null | |
| part_id | FK → parts null | NULL = typed text, not in catalogue yet (link it by SKU on the Part Requests page so a purchase can match it) |
| device_model_id | FK → device_models null | The job's model |
| description | varchar(255) | Part name or the typed text |
| quantity | int unsigned | |
| status | varchar(20) | Enum `PartRequestStatus`: `requested`, `ordered`, `received`, `cancelled` |
| supplier_id | FK → suppliers null | Who to order from |
| purchase_id | FK → purchases null | The purchase that brought it in |
| requested_by | FK → users | |
| handled_by | FK → users null | Who ordered / received / cancelled it |
| expected_on | date null | When the supplier should deliver (replaces `needed_by`) |
| ordered_at | datetime null | |
| received_at | datetime null | |
| arrival_seen_at | datetime null | "Part arrived" alert dismissed |
| note | text null | |
| timestamps | | |

Indexes: `(branch_id, status)`, `repair_job_id`, `(part_id, status)`.

### supplier_returns (built 2026-11-13)
Faulty / wrong items sent back to a supplier. Code: `App\Actions\SupplierReturns` (each step one transaction with stock, supplier balance and journal). Admin only (gate manage-stock). **Stock leaves when the return is made** (status `pending` = packed).

id, branch_id, `return_no` unique (`SR-YEAR-0001`), supplier_id, purchase_id null (the GRN, when every line comes from one), `status` (`pending`, `sent`, `replaced`, `credited`, `refunded`, `rejected`), reason, `total` (cost value), `credit_amount` (credit note / refund from the supplier), `supplier_credit_no`, `refund_method` (`cash`, `bank_transfer`), sent_at, resolved_at, resolved_by, resolution_note, created_by, timestamps.

### supplier_return_items
id, supplier_return_id (cascade), part_id, purchase_item_id null (the GRN line: from the unit for serialized items, chosen for others; must be this product from this supplier), `source` (`faulty` = faulty stock -> `faulty_to_supplier`; `shelf` = sellable stock -> `supplier_return`), quantity, unit_cost (unit cost, else GRN landed cost, else product cost), line_total, reason, unit_codes json, replacement_codes json, timestamps.

Made from the Faulty stock list or by scanning unit codes / IMEIs. Return note PDF; WhatsApp click-to-send with a signed PDF link (60 days, `sr.public-pdf`).

Supplier answer:
- **replaced**: the same quantities come back (`supplier_replacement`, on the shelf); serialized items get NEW unit codes (labels printed from the return page).
- **credited**: a `supplier_payments` row with method `credit_note` (`supplier_payments.supplier_return_id`, the supplier's number in `reference`). It lowers the supplier balance and is put on unpaid GRNs (`RecordSupplierPayment::applyCredit`).
- **refunded**: the credit row plus a **negative** `supplier_payments` row (money back, cash or bank). `applyCredit` only allocates the net free credit, so a refunded credit is never put on a bill. The statement shows them as "Credit note" / "Refund".
- **rejected**: the same units back into faulty stock (`supplier_rejected`).

Supplier payment forms offer only `SupplierPaymentMethod::manual()`.

Posting: made: Dr `supplier_returns_pending` (1310) / Cr `inventory`. Replaced or rejected: Dr `inventory` / Cr `supplier_returns_pending`. Credited: Dr `accounts_payable`; refunded: Dr `cash_in_safe` or `bank`; each Cr `supplier_returns_pending` (cost), the difference to `stock_adjustments`.

### stock_transfers / stock_transfer_items (Phase 8 — create later)
Moving stock between branches. `transfer_out` movement when sent, `transfer_in` when received.

`stock_transfers`: id, transfer_no unique, from_branch_id FK, to_branch_id FK, status (`draft`, `sent`, `received`, `cancelled`), notes, sent_by, sent_at, received_by, received_at, timestamps.
`stock_transfer_items`: id, stock_transfer_id FK cascade, part_id FK, quantity int unsigned, unit_cost decimal(12,2), timestamps.

---

## 8. Billing, payments and warranty (Phase 6)

### invoices
The bill for a job (or a counter sale with no job).

| Column | Type | Notes |
|---|---|---|
| id | bigint | |
| branch_id | FK → branches | |
| invoice_no | varchar(30) null unique | `INV-2026-0001` (yearly counter, `DocumentNumber`, row lock). Given when the invoice is **finalized**, so issued numbers have no gaps; NULL while draft |
| repair_job_id | FK → repair_jobs null | NULL = counter sale |
| customer_id | FK → customers | |
| status | varchar(20) | `draft`, `issued` (shown as "Finalized"), `void`. Enum `InvoiceStatus`. A draft can be edited or deleted; issued is locked. One draft-or-issued invoice per job |
| payment_status | varchar(20) | `unpaid`, `partial`, `paid` — updated in the same transaction as each payment |
| subtotal | decimal(12,2) | |
| discount_amount | decimal(12,2) | |
| tax_amount | decimal(12,2) default 0 | Stays 0 while setting `vat_enabled` is false; tax fields are hidden in the UI |
| total | decimal(12,2) | |
| notes | text null | |
| created_by | FK → users | Who made the draft |
| issued_by | FK → users null | |
| issued_at | datetime null | |
| voided_by | FK → users null | |
| voided_at | datetime null | |
| void_reason | text null | Required. Voiding (admin only) is the credit note: payments are unlinked (stay with the job, count towards the next invoice) and the warranties are voided |
| timestamps | | Issued invoices are never deleted — voided. Drafts may be deleted |

Indexes: `repair_job_id`, `customer_id`, `(branch_id, issued_at)`, `payment_status`.

### invoice_items

| Column | Type | Notes |
|---|---|---|
| id | bigint | |
| invoice_id | FK → invoices, cascade | |
| item_type | varchar(20) | `part`, `labour`, `other` |
| part_id | FK → parts null | |
| job_part_id | FK → job_parts null | Link to the part used |
| description | varchar(255) | |
| quantity | int unsigned | |
| unit_price | decimal(12,2) | |
| discount_amount | decimal(12,2) | |
| line_total | decimal(12,2) | |
| unit_cost | decimal(12,2) null | For profit reports. Hidden from technicians |
| warranty_days | smallint unsigned null | Creates a warranty row when issued (starts on the invoice date). Default from `parts.warranty_days`, else setting `warranty_days_parts` (parts) or `warranty_days_labour` (labour); `other` lines none. Editable per line on the draft |
| sort_order | smallint default 0 | |
| timestamps | | |

### payments
Money received or refunded. An advance can be taken before the invoice exists (`invoice_id` NULL) and is linked to the invoice when it is issued.

| Column | Type | Notes |
|---|---|---|
| id | bigint | |
| branch_id | FK → branches | |
| receipt_no | varchar(30) unique | |
| customer_id | FK → customers | |
| repair_job_id | FK → repair_jobs null | |
| invoice_id | FK → invoices null | |
| type | varchar(20) | `advance`, `payment`, `refund` |
| method | varchar(20) | `cash`, `card`, `bank_transfer`, `other` (enum `PaymentMethod`; old `online` rows were converted to `other`) |
| amount | decimal(12,2) | Always positive; `refund` is subtracted |
| reference | varchar(100) null | Optional free text, e.g. card slip or transfer ref |
| note | text null | |
| received_by | FK → users | |
| paid_at | datetime | |
| timestamps | | Never deleted — reverse with a refund |

Indexes: `invoice_id`, `repair_job_id`, `customer_id`, `(branch_id, paid_at)`. Receipt numbers look like `R-2026-0001`. Technicians never see payments (`view-billing` gate).

### warranties
Warranty given on a repair. One job can have several (e.g. screen 90 days, battery 30 days).

| Column | Type | Notes |
|---|---|---|
| id | bigint | |
| branch_id | FK → branches | |
| repair_job_id | FK → repair_jobs | Original repair |
| invoice_id | FK → invoices null | |
| invoice_item_id | FK → invoice_items null | |
| customer_id | FK → customers | |
| device_id | FK → devices | |
| description | varchar(255) | What is covered |
| starts_on | date | Usually delivery date |
| ends_on | date | |
| terms | text null | |
| status | varchar(20) | `active`, `void`. "Expired" = `ends_on` is past (calculated, not stored) |
| void_reason | text null | e.g. water damage, seal broken |
| timestamps | | |

Indexes: `ends_on`, `device_id`, `customer_id`, `repair_job_id`.
A warranty **claim** is a new `repair_jobs` row with `warranty_id` set (`App\Actions\CreateWarrantyJob`, only while the warranty is active). It has the same customer and device, `estimated_cost` 0, a WARRANTY badge, and its invoice lines start at Rs. 0.00 (no charge by default). The original job is `warranty.repair_job_id`.

**Warranty check** (`App\Support\WarrantyLookup`) finds a device by job number, invoice number, IMEI or serial. **Warranty jobs report** (`App\Support\WarrantyReport`, admin): claims by part, supplier (the last received purchase of that part on or before the original repair; stock is not tracked per unit), original technician and line type.

**Reports** (`App\Support\Reports\*`, `ReportController`, admin only: `role:admin` + gate `view-reports`): Jobs, Income and profit, Technicians, Faults and devices, Parts, Uncollected devices, Payments by method (plus Warranty jobs). Each has a date range (and branch, when there is more than one), a Chart.js chart and a CSV download (`?export=csv`). All totals are grouped in SQL; nothing is stored. Parts cost = `-SUM(quantity × unit_cost)` of `job_used`/`job_returned` movements (repair jobs, on the date of the job's first finalized invoice) and `sale` movements (POS lines). Stock value = on hand (physical movements) × `parts.cost_price`. Slow-moving = on hand > 0 and no `job_used`/`sale` in 90 days.
Report indexes (migration `add_report_indexes`): `repair_jobs` (`ready_at`), (`delivered_at`), (`cancelled_at`), (`status`, `ready_at`); `invoices` (`status`, `issued_at`); `payments` (`paid_at`); `stock_movements` (`type`, `created_at`), (`part_id`, `type`, `created_at`).
Speed indexes (migrations `add_speed_indexes`, `add_pos_sales_index`, found with `PerformanceSeeder` + `php artisan app:speed-check` and EXPLAIN): `customers` (`created_at`); `invoices` (`branch_id`, `voided_at`), (`status`, `repair_job_id`, `issued_at`), (`branch_id`, `kind`).

### day_closings
"Close day" on the Daily Summary page (`App\Actions\CloseDay`): cash counted against cash expected. One row per branch per day; never edited or deleted. Figures for a day come from `payments.paid_at` (`App\Support\DailySummary`).

| Column | Type | Notes |
|---|---|---|
| id | bigint | |
| branch_id | FK → branches | |
| business_date | date | Unique with `branch_id`. Today or earlier |
| opening_float | decimal(12,2) | Cash in the drawer at the start of the day (typed when closing) |
| cash_in | decimal(12,2) | Cash advances + payments that day |
| cash_out | decimal(12,2) | Cash refunds that day |
| expected_cash | decimal(12,2) | opening_float + cash_in − cash_out |
| counted_cash | decimal(12,2) | |
| difference | decimal(12,2) | counted − expected (minus = short) |
| totals | json | Copy of the summary at closing: by method, received, refunded, invoices |
| note | text null | |
| closed_by | FK → users | |
| closed_at | datetime | |
| timestamps | | |

If a payment for a closed day is added later, the page shows a warning with the new expected cash.

---

## 9. System (Phase 7)

### activity_log (spatie/laravel-activitylog)
Who changed what and when. Standard Spatie columns plus `branch_id`.

| Column | Type | Notes |
|---|---|---|
| id | bigint | |
| log_name | varchar(255) null, index | |
| description | text | |
| subject_type / subject_id | morph null, index | The record changed |
| event | varchar(255) null | created / updated / deleted |
| causer_type / causer_id | morph null, index | The user |
| properties | json null | Old and new values |
| batch_uuid | uuid null | |
| branch_id | FK → branches null | **Added by us**: from the subject (or the user), filled by `App\Models\ActivityLog` |
| repair_job_id | bigint null, index | **Added by us**: the job, or the job of a quotation / invoice / payment (job number search). No FK: the log outlives deleted jobs |
| timestamps | | |

Indexes: `(log_name, created_at)`, `created_at`, `repair_job_id`, morph indexes.

What is logged (`App\Support\ActivityLogs`): created / updated / deleted of repair jobs, customers, quotations, invoices, payments, parts, manual stock movements (adjustments, damaged, supplier returns), settings and users — changed columns only, old and new values. `log_name` = `jobs`, `customers`, `quotations`, `invoices`, `payments`, `parts`, `stock`, `settings`, `users`; plus `auth` (login, logout, failed login, lockout, password reset; email + IP, never the password) and `security` (screen lock viewed; never the value). Never logged: `passcode`, `password`, `remember_token`, `tracking_token`, `signature_path`. Page: Admin > Activity Log (admin only). Entries older than 365 days: `activitylog:clean`, daily 3:30 AM.

### deletion_logs
Permanent deletions by an admin (now: customers). Never edited or deleted.

| Column | Type | Notes |
|---|---|---|
| id | bigint | |
| branch_id | FK → branches null | |
| subject_type | varchar(30) | `customer` |
| subject_id | bigint unsigned | The deleted row's id (no FK) |
| label | varchar(255) | e.g. "Nimal Perera (077 123 4567)" |
| reason | text | Required |
| summary | json | What was removed: jobs (and numbers), devices, invoices, payments and totals |
| deleted_by | FK → users | |
| created_at | timestamp | |

### backups
One row per backup zip (`App\Actions\RunBackup`, Admin → Backups). The zip is on the private disk in `storage/app/private/backups/` (never under `public/`). Database daily 2:00 AM (mysqldump via `spatie/db-dumper`, or the pure-PHP `App\Support\Backups\PhpSqlDump` when mysqldump is not allowed); private files weekly (Sunday 2:30 AM). `App\Support\Backups\Retention` keeps the newest 14 database backups + the newest one of each of the last 8 weeks, and the newest 8 files backups; failed rows 14 days; the rest are deleted (zip and row). Guide: `docs/backups.md`.

| Column | Type | Notes |
|---|---|---|
| id | bigint | |
| type | varchar(20) | `BackupType`: `database`, `files` |
| filename | varchar(255) | e.g. `appletek-database-2026-11-13-020000.zip` |
| disk | varchar(20) | `local` (private) |
| path | varchar(255) | On the disk, e.g. `backups/appletek-database-….zip` |
| size_bytes | bigint unsigned null | |
| method | varchar(20) null | `mysqldump`, `php` (database), `zip` (files) |
| status | varchar(20) | `running`, `success`, `failed` |
| error | text null | Why it failed |
| created_by | FK → users null | NULL = scheduler |
| finished_at | datetime null | |
| timestamps | | |

Index: `(type, status, created_at)`.

---|---|---|
| id | bigint | |
| filename | varchar(255) | |
| disk | varchar(20) | |
| size_bytes | bigint unsigned null | |
| status | varchar(20) | `success`, `failed` |
| error | text null | |
| created_by | FK → users null | NULL = scheduler |
| created_at | timestamp | |

---

## 10. How stock works

- **On hand** at a branch = `SUM(stock_movements.quantity)` for that part + branch, **not counting** `reserved` / `unreserved` rows.
- **Reserved** = −`SUM(quantity)` of the `reserved` / `unreserved` rows.
- **Available** = on hand − reserved = `SUM(quantity)` of all rows. Low stock when `parts.min_stock` > 0 and available ≤ `parts.min_stock`.
- Every stock change runs in `DB::transaction()`, first locking the part row (`Part::lockForUpdate()`), then checking available stock, then inserting the movement. This stops two staff using the last screen at the same time.
- **Stock never goes below zero** (`App\Actions\RecordStockMovement`, clear error message): `job_used`, `adjustment_out`, `supplier_return`, `damaged` cannot take more than on hand; `reserved` and `job_used` cannot take stock reserved for other jobs; `unreserved` cannot release more than the job reserved; `job_returned` cannot return more than the job used. Callers pass a positive quantity; the sign comes from the type.
- **Stock count** (`App\Actions\ApplyStockCount`): counted quantity − on hand (worked out at save time, part locked) becomes an `adjustment_in` / `adjustment_out` row with note "Stock count…".
- **Average cost:** when a purchase is received, `parts.cost_price` = (old stock × old cost + new qty × new cost) ÷ (old stock + new qty). Each out movement stores the cost at that moment in `unit_cost`, so profit reports never change later.
- No table stores a stock number, so stock can never get out of sync.

## 11. How money works

- Invoice totals are worked out on the server in cents (`SaveInvoice`) and stored; payments are separate rows.
- Lines are suggested by `App\Support\InvoiceLines`: the approved quotation, the parts used (a quoted part with the same `part_id` becomes one line at the quoted price), labour, and the inspection fee for a rejected repair.
- When an invoice is issued, the job's payments with no invoice (the advance) are linked to it. A payment can never be more than the balance (invoice row locked).
- **Paid** = SUM(payment/advance) − SUM(refund) for the invoice. **Balance** = total − paid.
- `invoices.payment_status` is recalculated in the same transaction as every payment.
- Issued invoices are never edited; to change one, void it and issue a new one.
- Technicians never see `cost_price`, `unit_cost`, profit or reports — enforced in Policies and views.

## 12. Which phase creates which tables

| Phase | Tables |
|---|---|
| 1 | branches, users (add columns), settings, number_sequences |
| 2 | customers, device_types, brands, device_models, devices, repair_jobs, job_photos, job_status_histories, job_notes, payments (advances at intake), number_sequences |
| 3 | message_templates, notification_logs |
| 4 | job_assignments, passcode_views (built early), quotations, quotation_items (repair_jobs already has assignment/diagnosis columns) |
| 5 | part_categories, parts, part_device_model, suppliers, purchases, purchase_items, stock_movements (incl. reservations), job_parts, part_requests, supplier_returns, supplier_return_items |
| 6 | invoices, invoice_items, payments, warranties (+ FK `repair_jobs.warranty_id`) |
| 7 | activity_log, backups |
| 8 | stock_transfers, stock_transfer_items (barcode and WhatsApp API columns already exist) |
| 9 (planned) | POS, purchasing and accounting: price_levels, part_prices, stock_units, stock_movement_units, purchase_orders, purchase_order_items, supplier_payments, supplier_payment_allocations, payment_allocations, sales_returns, sales_return_items, cash_drawers, cash_shifts, cash_movements, accounts, journal_entries, journal_lines, accounting_periods, expenses (+ build supplier_returns). See section 15 and `docs/pos-plan.md` |

## 13. Relationship overview

```
branches ─┬─ users
          ├─ repair_jobs ─┬─ job_photos / job_notes / job_status_histories
          │               ├─ quotations ── quotation_items
          │               ├─ job_parts ── stock_movements
          │               ├─ part_requests (reservations: stock_movements)
          │               ├─ invoices ── invoice_items ── warranties
          │               └─ payments
          ├─ purchases ── purchase_items ── stock_movements
          └─ supplier_returns ── supplier_return_items ── stock_movements

customers ── devices ── repair_jobs
brands / device_types ── device_models ──< part_device_model >── parts ── part_categories
suppliers ── purchases / supplier_returns
```

## 14. Decisions

Agreed with the shop owner on 2026-11-11. Follow these unless the owner changes them.

1. Built for **AppleTek** only. First branch: AppleTek, code `AT`. Branch support stays for future shops.
2. Repair jobs table is `repair_jobs` (Laravel uses `jobs` for the queue).
3. Catalogue data (device types, brands, models, parts, suppliers, accessories) and customers are **shared across branches**.
4. Document numbers: `RJ-2026-0001` style (changed 2026-11-11 from `RJ-26-00042`). Job prefix is a setting (default RJ), restarts every year (see `number_sequences`).
5. **Customer phone numbers are unique** (changed 2026-11-11; this replaces the earlier "same number allowed" decision). Adding a customer with a phone that already exists opens the existing customer instead. Phone must be a Sri Lankan mobile number.
6. Device passcodes: optional, encrypted, shown only to admin and the assigned technician, erased when the job is closed.
7. VAT: off. `tax_amount` stays 0; turn on later with setting `vat_enabled`. **(Phase 9: owner to confirm VAT registration — docs/pos-plan.md §14.)**
8. Supplier payments: **replaced 2026-11-13** by `supplier_payments` + allocations to GRNs, supplier balance and statement (section 7, Purchasing). `purchases.paid_amount` is now a cache.
9. Stock cost: average cost.
10. Counter sales (no repair job): supported by the tables (`invoices.repair_job_id` NULL, movement type `sale`). **(Planned Phase 9: this becomes the POS, `invoices.kind = pos` — section 15.)**
11. **Payment methods are used** (changed 2026-11-11; replaces "no payment methods for now"): cash, card, bank transfer, online/app, as asked for on the New Job Card advance payment.
12. Tracking link works until 90 days after delivery (setting `tracking_link_days`).
13. Warranty: per invoice line. Separate defaults for labour and parts (settings, 30 days each), can be set per part (`parts.warranty_days`) and changed on the invoice.
14. Backups: decide the method in Phase 7. Plan: a PHP-based database dump (no `mysqldump`, which shared hosting often blocks), zipped to the private disk, keep the last 14, admin can download.

---

## 15. POS, purchasing and accounting (Phase 9) — **PLANNED, NOT BUILT**

Plan and posting rules: `docs/pos-plan.md`. Nothing in this section exists yet. Open questions are
in `docs/pos-plan.md` §14; column details may change when they are answered.

### 15.1 Changes to existing tables

| Table | Change |
|---|---|
| `parts` (product master) | **Built (2026-11-13):** sub-category, variants (`parent_id`, `variant_name`), `min_selling_price`, `tax_category`, `is_serialized`, price levels and price history — see section 7. Still to add: `product_type` varchar(20) default `part` (`part`, `accessory`, `device`, `service`), `is_sellable` boolean default false, `is_stocked` boolean default true, `reorder_qty` int unsigned default 0. `barcode` = manufacturer barcode (EAN/UPC). `is_serialized` replaces the planned name `track_units` |
| `customers` | Add `credit_allowed` boolean default false, `credit_limit` decimal(12,2) default 0, `credit_days` smallint unsigned default 0, `price_level_id` FK → price_levels null, `tax_number` varchar(30) null (customer VAT no.) |
| `suppliers` | Add `credit_days` smallint unsigned default 0, `tax_number` varchar(30) null. Opening balances come from the opening journal (no column) |
| `stock_movements` | New types: `sale`, `sale_reversed` (POS void), `sale_return`, `supplier_replacement` (already listed), `transfer_out`, `transfer_in`. New `reference_type` values: `invoice_item`, `sales_return_item`, `supplier_return_item`. Tracked products also get rows in `stock_movement_units` |
| `purchases` (= **GRN**) | Add `purchase_order_id` FK → purchase_orders null, `supplier_invoice_date` date null, `due_date` date null, `tax_amount` decimal(12,2) default 0, `journal_entry_id` FK null. New numbers `GRN-YEAR-0001` (old `PO-…` numbers stay). `paid_amount` / `payment_status` become a cache of `supplier_payment_allocations`, updated in the same transaction. Replaces Decision 8 |
| `purchase_items` | Add `purchase_order_item_id` FK null, `tax_rate` decimal(5,2) default 0, `tax_amount` decimal(12,2) default 0 |
| `invoices` | Add `kind` varchar(20) default `repair` (`repair`, `pos`), `price_level_id` FK null, `cash_shift_id` FK null, `is_credit` boolean default false, `due_date` date null, `customer_tax_number` varchar(30) null (copied at issue), `journal_entry_id` FK null. `customer_id` becomes **nullable** (walk-in POS sale). `tax_amount` used when VAT is on |
| `invoice_items` | Add `stock_unit_id` FK null (tracked unit sold), `tax_rate` decimal(5,2) default 0, `tax_amount` decimal(12,2) default 0, `price_level_id` FK null. `discount_amount` (exists) is used for POS line discounts |
| `payments` | Add `cash_shift_id` FK null (required for cash), `sales_return_id` FK null (refunds), `account_id` FK → accounts null (where the money went, from the method), `journal_entry_id` FK null. `customer_id` becomes **nullable** (walk-in). Invoice links move to `payment_allocations`; `invoice_id` is back-filled into allocations, then kept or dropped (pos-plan §14.19) |
| `supplier_returns` / `supplier_return_items` | Build as designed in section 7, plus `supplier_return_items.stock_unit_id` FK null and `supplier_returns.journal_entry_id` FK null |
| `day_closings` | Kept as history. "Close day" becomes a sign-off allowed only when all shifts of the day are closed (pos-plan §8) |
| `settings` | New keys: `vat_enabled` (false), `vat_rate` (18.00), `vat_number`, `prices_include_vat` (true), `return_days` (7), `pos_receipt_footer`, `unit_label_size` (`38x25`), `accounting_start_date`, `financial_year_start_month` (4 = April), `max_discount_percent_cashier` |
| `users` / Role enum | Possible new roles `cashier`, `accountant` (pos-plan §12) |

### 15.2 Products

#### price_levels — **built**, see section 7
| Column | Type | Notes |
|---|---|---|
| id | bigint | |
| code | varchar(20) unique | `retail`, `wholesale`, `technician` (owner to confirm the list) |
| name | varchar(50) | |
| is_default | boolean | Exactly one (Retail) |
| sort_order | smallint | |
| is_active | boolean default true | |
| timestamps | | |

#### part_prices
| Column | Type | Notes |
|---|---|---|
| part_id | FK → parts, cascade | |
| price_level_id | FK → price_levels, cascade | |
| price | decimal(12,2) | Missing row → Retail (`parts.selling_price`) |
| timestamps | | |

Primary key `(part_id, price_level_id)`.

#### stock_units — **built**, see section 7 (`stock_unit_events` replaced `stock_movement_units`)
| Column | Type | Notes |
|---|---|---|
| id | bigint | |
| part_id | FK → parts | |
| branch_id | FK → branches | Where the unit is now |
| unit_code | varchar(20) unique | Our label barcode, e.g. `U00012345` (Code 128) |
| serial_no | varchar(100) null | Manufacturer serial / IMEI. Unique with `part_id` when not null |
| status | varchar(20) | `in_stock`, `reserved`, `sold`, `used_in_job`, `supplier_return`, `damaged`, `in_transit` |
| purchase_item_id | FK → purchase_items null | GRN line it came in on (null = labelled from opening stock) |
| unit_cost | decimal(12,2) | What we paid for this unit (information; COGS uses average cost unless decided otherwise) |
| repair_job_id | FK → repair_jobs null | When reserved for / used on a job |
| invoice_item_id | FK → invoice_items null | When sold |
| timestamps | | |

Indexes: `(part_id, status, branch_id)`, `serial_no`. Status is changed only by the stock actions, in the same transaction as the movement.

#### stock_movement_units — **not built**: replaced by `stock_unit_events.stock_movement_id` (section 7)
| Column | Type | Notes |
|---|---|---|
| stock_movement_id | FK → stock_movements, cascade | |
| stock_unit_id | FK → stock_units | |

Primary key `(stock_movement_id, stock_unit_id)`. For a tracked product the number of rows must equal `abs(stock_movements.quantity)`.

### 15.3 Purchasing — **built** (section 7, Purchasing). Differences: `supplier_payments` has no `account_id` / `cash_shift_id` / `journal_entry_id` yet (added with accounting); method adds `cheque` + `other`, `from_drawer` flag

#### purchase_orders
| Column | Type | Notes |
|---|---|---|
| id | bigint | |
| branch_id | FK → branches | |
| po_no | varchar(30) unique | `PO-YEAR-0001` |
| supplier_id | FK → suppliers | |
| status | varchar(20) | `draft`, `sent`, `partly_received`, `received`, `closed`, `cancelled` |
| order_date | date | |
| expected_on | date null | |
| subtotal, discount_amount, tax_amount, total | decimal(12,2) | Expected amounts |
| notes | text null | |
| created_by | FK → users | |
| sent_at | datetime null | |
| closed_by | FK → users null | |
| closed_at | datetime null | |
| close_reason | varchar(255) null | |
| timestamps | | No stock or accounting effect |

#### purchase_order_items
| Column | Type | Notes |
|---|---|---|
| id | bigint | |
| purchase_order_id | FK → purchase_orders, cascade | |
| part_id | FK → parts | |
| quantity | int unsigned | Ordered |
| received_qty | int unsigned default 0 | Raised by GRN lines (cache, updated in the GRN transaction) |
| unit_cost | decimal(12,2) | Expected |
| tax_rate | decimal(5,2) default 0 | |
| line_total | decimal(12,2) | |
| part_request_id | FK → part_requests null | Ordered for a waiting job |
| timestamps | | |

#### supplier_payments
| Column | Type | Notes |
|---|---|---|
| id | bigint | |
| branch_id | FK → branches | |
| payment_no | varchar(30) unique | `SP-YEAR-0001` |
| supplier_id | FK → suppliers | |
| paid_on | date | |
| method | varchar(20) | `PaymentMethod` (+ `cheque` if asked) |
| account_id | FK → accounts | Cash / bank it was paid from |
| amount | decimal(12,2) | |
| reference | varchar(100) null | Cheque no., transfer ref |
| note | text null | |
| cash_shift_id | FK → cash_shifts null | If paid from the drawer |
| journal_entry_id | FK → journal_entries | |
| created_by | FK → users | |
| timestamps | | Never deleted — reversed |

#### supplier_payment_allocations
| Column | Type | Notes |
|---|---|---|
| id | bigint | |
| supplier_payment_id | FK → supplier_payments, cascade | |
| purchase_id | FK → purchases (GRN) | |
| amount | decimal(12,2) | |
| timestamps | | |

The unallocated part of a supplier payment is an advance to the supplier. The migration turns each existing `purchases.paid_amount` into one supplier payment (method `other`, date = purchase date, note "Before Phase 9").

### 15.4 Sales, returns and customer money

#### payment_allocations
| Column | Type | Notes |
|---|---|---|
| id | bigint | |
| payment_id | FK → payments, cascade | |
| invoice_id | FK → invoices | |
| amount | decimal(12,2) | |
| timestamps | | |

Paid on an invoice = SUM of its allocations. An advance has no allocation until the invoice is issued. Back-filled from `payments.invoice_id`.

#### sales_returns (credit notes)
| Column | Type | Notes |
|---|---|---|
| id | bigint | |
| branch_id | FK → branches | |
| return_no | varchar(30) unique | `CN-YEAR-0001` |
| invoice_id | FK → invoices | Original sale |
| customer_id | FK → customers null | |
| return_date | datetime | |
| reason | varchar(255) | |
| settlement | varchar(20) | `refund`, `reduce_balance`, `store_credit` |
| subtotal, tax_amount, total | decimal(12,2) | |
| approved_by | FK → users null | When over the cashier's limit |
| cash_shift_id | FK → cash_shifts null | |
| journal_entry_id | FK → journal_entries | |
| created_by | FK → users | |
| timestamps | | Never edited or deleted |

#### sales_return_items
| Column | Type | Notes |
|---|---|---|
| id | bigint | |
| sales_return_id | FK → sales_returns, cascade | |
| invoice_item_id | FK → invoice_items | Cannot return more than sold (minus earlier returns) |
| part_id | FK → parts null | |
| stock_unit_id | FK → stock_units null | |
| quantity | int unsigned | |
| unit_price | decimal(12,2) | Price it was sold at |
| tax_amount | decimal(12,2) | |
| line_total | decimal(12,2) | |
| condition | varchar(20) | `restock`, `faulty` |
| unit_cost | decimal(12,2) | Cost reversed out of COGS |
| timestamps | | |

### 15.5 Cash drawer

#### cash_drawers
| Column | Type | Notes |
|---|---|---|
| id | bigint | |
| branch_id | FK → branches | |
| name | varchar(50) | "Counter 1" |
| account_id | FK → accounts | Its cash account (`cash_drawer`) |
| is_active | boolean default true | |
| timestamps | | |

#### cash_shifts
| Column | Type | Notes |
|---|---|---|
| id | bigint | |
| branch_id | FK → branches | |
| cash_drawer_id | FK → cash_drawers | Only one open shift per drawer |
| shift_no | varchar(30) unique | `SH-YEAR-0001` |
| opened_by | FK → users | |
| opened_at | datetime | |
| opening_float | decimal(12,2) | |
| closed_by | FK → users null | |
| closed_at | datetime null | |
| expected_cash | decimal(12,2) null | Worked out at closing |
| counted_cash | decimal(12,2) null | |
| difference | decimal(12,2) null | Posted to `cash_over_short` |
| totals | json null | Copy of the shift (Z) report at closing |
| note | text null | |
| journal_entry_id | FK null | Over/short entry |
| timestamps | | |

#### cash_movements
| Column | Type | Notes |
|---|---|---|
| id | bigint | |
| cash_shift_id | FK → cash_shifts | |
| type | varchar(20) | `pay_in`, `pay_out`, `drop`, `bank_deposit` |
| amount | decimal(12,2) | Always positive |
| account_id | FK → accounts | The other side (expense, safe, bank, capital) |
| reason | varchar(255) | Required |
| journal_entry_id | FK → journal_entries | |
| created_by | FK → users | |
| timestamps | | |

### 15.6 Accounting (double entry)

#### accounts (chart of accounts) — **built** (section 7, Accounting core)
| Column | Type | Notes |
|---|---|---|
| id | bigint | |
| code | varchar(20) unique | e.g. `1100` |
| name | varchar(150) | |
| type | varchar(20) | `asset`, `liability`, `equity`, `income`, `expense` |
| subtype | varchar(30) null | `cash`, `bank`, `receivable`, `payable`, `inventory`, `tax`, `cogs`, `contra_income`, … (drives report grouping) |
| parent_id | FK → accounts null | Grouping for reports |
| system_key | varchar(50) null unique | Used by posting rules. System accounts cannot be deleted or change type |
| is_control | boolean default false | AR / AP: lines must carry `customer_id` / `supplier_id`; no manual journals |
| is_active | boolean default true | |
| timestamps | | Shared across branches; balances are per branch through `journal_lines.branch_id` |

Proposed seed (codes are suggestions; the accountant may replace them):

| Code | Name | Type | system_key |
|---|---|---|---|
| 1100 | Cash drawer | asset | `cash_drawer` |
| 1110 | Cash in safe | asset | `cash_in_safe` |
| 1120 | Bank account | asset | `bank` |
| 1130 | Card payments to receive | asset | `card_clearing` |
| 1200 | Customers (accounts receivable) | asset (control) | `accounts_receivable` |
| 1300 | Stock (inventory) | asset | `inventory` |
| 1310 | Supplier returns pending | asset | `supplier_returns_pending` |
| 1400 | VAT input (paid on purchases) | asset | `vat_input` |
| 2100 | Suppliers (accounts payable) | liability (control) | `accounts_payable` |
| 2200 | Customer advances and store credit | liability | `customer_deposits` |
| 2300 | VAT output (charged on sales) | liability | `vat_output` |
| 3100 | Owner's capital | equity | `owner_capital` |
| 3200 | Retained earnings | equity | `retained_earnings` |
| 4100 | Sales – products (POS) | income | `sales_products` |
| 4200 | Repair income – labour | income | `repair_labour_income` |
| 4210 | Repair income – parts | income | `repair_parts_income` |
| 4300 | Other income (fees) | income | `other_income` |
| 4900 | Sales returns | income (contra) | `sales_returns` |
| 4910 | Discounts allowed | income (contra) | `discounts_allowed` |
| 5100 | Cost of products sold | expense (cogs) | `cogs_products` |
| 5110 | Cost of repair parts | expense (cogs) | `cogs_repair_parts` |
| 5120 | Warranty repair cost | expense (cogs) | `warranty_cost` |
| 5200 | Stock adjustments | expense | `stock_adjustments` |
| 5210 | Stock written off (damaged) | expense | `stock_writeoff` |
| 5300 | Purchase discounts received | expense (contra) | `purchase_discounts` |
| 6100 | Card fees | expense | `card_fees` |
| 6200 | Cash over / short | expense | `cash_over_short` |
| 6300+ | Rent, electricity, salaries, transport, … | expense | — (chosen on expenses / pay-outs) |

Payment method → account: cash → `cash_drawer`, card → `card_clearing`, bank transfer → `bank`, other → chosen by staff (default `bank`).

#### journal_entries
| Column | Type | Notes |
|---|---|---|
| id | bigint | |
| branch_id | FK → branches | |
| entry_no | varchar(30) unique | `JV-YEAR-0001` |
| entry_date | date | Must be in an open period |
| source_type | varchar(50) null | Morph: `invoice`, `payment`, `purchase`, `supplier_payment`, `sales_return`, `supplier_return`, `stock_movement`, `cash_shift`, `cash_movement`, `expense`, `manual`, `opening` |
| source_id | bigint unsigned null | |
| description | varchar(255) | |
| reversal_of_id | FK → journal_entries null | This entry reverses that one |
| reversed_by_id | FK → journal_entries null | |
| created_by | FK → users null | NULL = system |
| created_at | timestamp | No `updated_at`: **never updated or deleted** |

Indexes: `(branch_id, entry_date)`, `(source_type, source_id)`.

#### journal_lines
| Column | Type | Notes |
|---|---|---|
| id | bigint | |
| journal_entry_id | FK → journal_entries, cascade | |
| branch_id | FK → branches | Copied from the entry (fast branch reports) |
| entry_date | date | Copied from the entry (fast date-range reports) |
| account_id | FK → accounts | |
| debit | decimal(14,2) default 0 | One of debit / credit is > 0, the other 0 |
| credit | decimal(14,2) default 0 | |
| customer_id | FK → customers null | Required on `accounts_receivable` and `customer_deposits` lines |
| supplier_id | FK → suppliers null | Required on `accounts_payable` lines |
| memo | varchar(255) null | |

Indexes: `(account_id, entry_date)`, `(branch_id, account_id, entry_date)`, `customer_id`, `supplier_id`.
Rule (in code, same transaction): SUM(debit) = SUM(credit) per entry, worked out in cents.
Totals use `decimal(14,2)` (not 12,2) so that year-to-date sums in reports never overflow.

#### accounting_periods
| Column | Type | Notes |
|---|---|---|
| id | bigint | |
| starts_on | date unique | First day of the month |
| ends_on | date | |
| status | varchar(20) | `open`, `locked` |
| locked_by | FK → users null | |
| locked_at | datetime null | |
| timestamps | | |

Year end: a closing entry moves the year's income and expenses into `retained_earnings`.

#### expenses
| Column | Type | Notes |
|---|---|---|
| id | bigint | |
| branch_id | FK → branches | |
| expense_no | varchar(30) unique | `EX-YEAR-0001` |
| expense_date | date | |
| account_id | FK → accounts | Expense account |
| paid_from_account_id | FK → accounts | Cash / bank (or `accounts_payable` for a supplier bill) |
| supplier_id | FK → suppliers null | |
| amount | decimal(12,2) | Excluding VAT |
| tax_amount | decimal(12,2) default 0 | Input VAT |
| reference | varchar(100) null | |
| note | text null | |
| bill_path | varchar(255) null | Private disk |
| cash_shift_id | FK null | If paid from the drawer |
| journal_entry_id | FK → journal_entries | |
| created_by | FK → users | |
| timestamps | | Never deleted — reversed |

### 15.7 New document numbers
`PO-` purchase orders (the counter continues after the old stock-in numbers), `GRN-` goods received,
`SP-` supplier payments, `CN-` credit notes (customer returns), `SR-` supplier returns, `SH-` shifts,
`JV-` journal entries, `EX-` expenses. POS sales use the `INV-` series (pos-plan §14.13).
All through `DocumentNumber` (yearly counter, row lock).


## Photo clean-up (2026-11-14)

`php artisan app:prune-images` runs every night at 3:30 AM: job photos (rows and files), handover signatures (`repair_jobs.signature_path`, by delivery date), supplier bill photos (`purchases.bill_path`) and expense receipts (`expenses.receipt_path`) older than the setting `image_retention_days` (default 90, minimum 30) are deleted. The records stay. Product photos and the shop logo are kept.