# Database Documentation ## Customer account unification — 2026-08-16 Every non-QA operational customer is normalized to `account_type=on_account`; historical `customer_orders.invoice_type` and all price/VAT snapshots remain unchanged. Invoice payment type is an order-level choice. Existing profiles may be completed progressively using nullable `contact_phone`, `id_holder_type`, `id_expiry`, and versioned private `customer_documents.expires_at`. Missing profile data, cheque data, documents, or a NULL credit limit does not change lifecycle eligibility. ## Stock Replenishment `stock_replenishment_attachments` stores private evidence and may reference the request item plus the exact receipt and receipt item. The receipt references are nullable for preparation/dispatch evidence and indexed foreign keys for new discrepancy evidence. Historical rows are preserved; no public-storage URL is exposed. ### Temporary QA dataset `products`, `customers`, `stock_replenishment_requests`, and `van_stock_movements` carry an explicit `is_test_data` marker defaulting to false. Products, customers and requests also carry nullable `qa_owner_user_id`; QA requests may be archived with `qa_archived_at`. Existing rows remain normal production data. QA stock rows retain `raw_data.qa_test`, while every QA initialization/reset/receipt movement is append-only and marked `is_test_data=true`. Normalized tables store requests, immutable item snapshots and six quantity facts, append-only events, idempotent receipts/receipt items, and private attachments. `van_stock_movements.stock_replenishment_request_id` and `stock_replenishment_receipt_item_id` trace each `warehouse_receipt`. Request numbers use `SR-YYYYMMDD-000001`. No warehouse inventory ledger is introduced. ## Customer onboarding and pricing (2026-08-09) `customers` separates approval (`draft`, `submitted`, `under_review`, `needs_changes`, `approved`, `rejected`) from operation (`active`/`suspended`) and records requested/final account terms plus actor/timestamp/reason audit fields. Existing codes remain unchanged; new applications receive a permanent code only on approval. `customer_documents` stores versioned private-file metadata. Order headers/items store Cash/On Account, gross/discount/net, pricing source, VAT and credit-setting snapshots so historical invoices never recalculate. Security Cheque metadata is stored on `customers` in nullable `security_cheque_bank_name`, `security_cheque_number`, `security_cheque_account_holder`, `security_cheque_amount`, `security_cheque_undated`, `security_cheque_date`, and `security_cheque_notes` columns. The image continues to use the existing versioned private `customer_documents` row with document type `security_cheque`; no public file path is introduced. Main Tables products product_images catalogs shipments shipment_items van_stock_imports van_boxes van_box_items users areas company_settings Future Tables mobile_tokens offline_sync notifications ## Current Database Baseline - 2026-08-08 The migration history currently covers these functional areas. ### Core - users - cache / jobs infrastructure - company settings - areas - mobile access/token authentication ### Products / Catalogs - products - product_images - catalogs - catalog_items - permanent-catalog fields - catalog price-update tracking - public catalog tokens ### Shipments / Imports - shipments - shipment_items - SOH imports - price imports and price import items - import sessions - import retention / source tracking ### Van Inventory - van_stock_imports - van_stock_items - van_boxes - van_box_items - inventory check tables ### Field Sales The field-sales foundation migration created the core customer/order/stock-movement data used by the current sales workflow, including: - customers - customer_orders - customer_order_items - van_stock_movements ### Daily Sales / Mobile Sales Tools / Operations Current migrations also include foundations for: - daily sales report entries - daily sales report submissions - mobile sales tools - sales operations - vehicles / salesman operational data - mobile chat - mixed fulfillment on customer orders - VAT and invoice fields ### Approved Upcoming Database Work Before creating migrations for the following features, the existing `van_stock_movements`, van-stock models, and `CustomerOrderService` must be reviewed and extended rather than bypassed: - RMA returns and RMA items - RMA warehouse handover batches - incremental van stock receipts and receipt items - order revision / audit snapshots - invoice-pricing settings and settings audit history where appropriate ## Returns / RMA Database ### New Tables - `customer_returns` — return header, customer/salesman, return type, independent operational and financial statuses, settlement summary, accountant review data. - `customer_return_items` — returned products, snapshots, quantity, reason/condition, stock status, approved price, and direct-exchange fields. - `customer_return_photos` — optional item photos. - `rma_stock_items` — non-sellable RMA quantity by salesman and product. - `return_settlements` — invoice application, Credit Note, or exchange settlement records. - `return_handover_batches` — warehouse handover batch header. - `return_handover_items` — expected / received / rejected quantities per returned item. ### Van Stock Movement Extension `van_stock_movements` gains: - `customer_return_id` - `customer_return_item_id` - `stock_bucket` (`sellable` or `rma`) This allows both sellable and non-sellable stock changes to remain traceable in the same movement history. ### Physical Stock Rule Opening RMA reclassification moves quantity from `van_stock_items` to `rma_stock_items` without changing the physical total in the vehicle. ## Return workflow audit fields `customer_returns` includes `submitted_by`, `rejected_at`, `rejected_by`, and `rejection_reason` in addition to the existing submission and approval timestamps. Rejected RMA stock and movement rows are retained. ## RMA Stock balance source No new stock table was added. Current in-van quantity comes from `rma_stock_items`; Draft handover reservations and in-transit quantities are aggregated from `return_handover_items` joined to `return_handover_batches`. Terminal warehouse batches are excluded. ## Warehouse handover movement audit `van_stock_movements.return_handover_id` is a nullable, indexed foreign key to `return_handover_batches.id` with `ON DELETE SET NULL`. New warehouse dispatch (`rma_handover`) and rejected-quantity restoration (`rma_handover_rejected`) movements store the exact handover batch ID in addition to the existing Return and Return Item references. Existing historical movement rows remain `NULL`. No backfill was performed because an exact handover association cannot be proven safely for every historical movement from the existing fields alone. ## Opening RMA movement audit Opening RMA is represented directly in `van_stock_movements`, not as a synthetic customer return. Each operation writes one Sellable row with negative `qty_change` and one RMA row with equal positive `qty_change`. Both use `movement_type=opening_rma_reclass`, null return/handover references, salesman/product/actor metadata, and unique bucket-specific idempotency keys. # Import Center data ownership ## Stock receipt audit data `stock_replenishment_receipts` now supports legacy confirmed receiving and warehouse STR sessions. It stores the unique document identity, private PDF metadata/hash, parsed totals, workflow timestamps/actors, and optional Stock Request link. Items store immutable document snapshots beside counted, damaged, accepted, difference, matching, and variance data. `stock_replenishment_receipt_events` records workflow and idempotent count events. `van_stock_movements.stock_replenishment_receipt_id` directly identifies the receipt that created a `warehouse_receipt` movement. Existing receipts are preserved and backfilled as confirmed legacy receipts. Historical movement references remain `NULL`; no association is guessed. `import_sessions.preview_rows` stores analyzed row status, original/raw values, issue groups, resolutions, and change preview. `import_sessions.import_meta` records the resulting price import and imported/skipped totals. Full Main/Clearance imports create versioned `price_imports`, `price_import_items`, `catalogs`, and `catalog_items`; the former active version is archived rather than deleted. No schema migration was required for Import Center stabilization. `import_sessions.request_token` is a nullable unique UUID added by `2026_08_16_120000_add_request_token_to_import_sessions_table`. New uploads use it to make browser retries idempotent; historical sessions remain valid with `NULL`. Background work uses Laravel's existing `jobs` and `failed_jobs` tables. # Visits, Daily Route and DWR v1 — 2026-08-26 `salesman_work_days` is the authoritative one-row-per-salesman/date working-day record. `salesman_work_breaks` preserves every separate break interval. Existing `sales_routes`, `sales_route_stops`, and `customer_visits` are extended rather than duplicated; visits now carry work-day, route-stop, lifecycle, result and optional start/end GPS references. `visit_collections` stores operational field collections for DWR only and does not post to Customer Ledger, invoice balances, or available credit. `daily_work_reports` stores only the three editable narrative fields; totals and visit lines are always regenerated from source records. Nullable `visit_id` references on orders and returns preserve history and allow multiple actions per visit.