# Database Schema Design

Companion to [PLAN.md](PLAN.md). MariaDB 10.4 / InnoDB / utf8mb4_unicode_ci.

## Global conventions

- Every business table has `company_id BIGINT UNSIGNED NOT NULL` (FK, RESTRICT).
- **Every unique index is composite with `company_id`.** `UNIQUE(company_id, sku)`, never `UNIQUE(sku)`.
- Types: money `DECIMAL(18,4)`, unit cost `DECIMAL(18,6)`, quantity `DECIMAL(18,6)`, percent `DECIMAL(9,6)`.
- Dates: `DATE` for accounting/posting dates (no timezone ambiguity), `TIMESTAMP` for audit metadata.
- Master data: `soft deletes` allowed (`deleted_at`), plus `is_active`. Transactional data: **no soft deletes, no hard deletes** — status only.
- Every transactional table: `status ENUM('draft','posted','cancelled')`, `posted_at`, `posted_by`, `cancelled_at`, `cancelled_by`, `cancellation_reason`, `reversal_journal_id`.
- FKs on all `*_id`. `ON DELETE RESTRICT` for anything financial.
- Standard index set per transactional table:
  `(company_id, status, transaction_date)`, `(company_id, branch_id, transaction_date)`, `(company_id, <party>_id, status)`, `UNIQUE(company_id, branch_id, doc_type, number)`.

---

## 1. Tenancy, org, access

**companies** — `id, name, legal_name, logo_path, address, city, country, pan_no, vat_no, phone, email, base_currency, decimal_places, date_system('AD'|'BS'), locale, timezone, costing_method('wac'|'fifo'), price_mode('exclusive'|'inclusive'), fiscal_year_start_month, fiscal_year_start_day, is_active`
Costing method and price mode are **locked once the first document posts** (enforced in the app, not the DB).

**branches** — `id, company_id, code, name, address, phone, pan_no, is_default, default_warehouse_id, cash_account_id, is_active` · `UNIQUE(company_id, code)`

**users** — standard Laravel + `company_id NULL` (NULL = platform super admin), `default_branch_id, phone, pin_hash (for POS approvals), is_active, last_login_at`

**branch_user** — pivot `user_id, branch_id` (which branches a user may operate in)

**roles / permissions / model_has_roles / role_has_permissions** — Spatie-style with a `company_id` team column. Permission names are granular strings: `sales.create`, `sales.post`, `sales.cancel`, `sales.price.override_below_min`, `purchase.post`, `payment.create`, `accounting.journal.create`, `accounting.period.close`, `bank.transfer.create`, `inventory.adjust`, `report.financial.view`, ...

**settings** — `id, company_id NULL, branch_id NULL, key, value TEXT, type` · `UNIQUE(company_id, branch_id, key)`. Cached per company, busted on write.

**audit_logs** — `id, company_id, user_id, event, auditable_type, auditable_id, old_values TEXT, new_values TEXT, ip, user_agent, url, created_at` · index `(company_id, auditable_type, auditable_id)`, `(company_id, user_id, created_at)`

**attachments** — `id, company_id, attachable_type, attachable_id, disk, path, original_name, mime, size, uploaded_by`

**document_sequences** — `id, company_id, branch_id, fiscal_year_id, doc_type, prefix, suffix, padding, next_number` · `UNIQUE(company_id, branch_id, fiscal_year_id, doc_type)`. Allocated under `FOR UPDATE`. See PLAN D5.

**plans / plan_features / subscriptions** — `plans(code, name, price, interval)`, `plan_features(plan_id, feature_key, limit_value NULL)`, `subscriptions(company_id, plan_id, status, trial_ends_at, current_period_end)`

---

## 2. Accounting

**fiscal_years** — `id, company_id, name, start_date, end_date, status('open'|'closed'), closing_journal_id NULL` · `UNIQUE(company_id, name)`

**accounting_periods** — `id, company_id, fiscal_year_id, name, start_date, end_date, status('open'|'closed'|'locked'), closed_by, closed_at` · index `(company_id, start_date, end_date)`

**accounts** (chart of accounts, hierarchical) —
`id, company_id, parent_id NULL, code, name, type ENUM('asset','liability','equity','revenue','cogs','expense'), subtype ENUM('cash','bank','receivable','inventory','fixed_asset','payable','tax_payable','tax_receivable','equity','retained_earnings','revenue','cogs','expense','other'), normal_balance ENUM('debit','credit'), is_group BOOL, is_system BOOL, is_reconcilable BOOL, requires_party BOOL, currency, is_active, lft/rgt or path VARCHAR(191)`
- `UNIQUE(company_id, code)`, index `(company_id, type)`, `(company_id, parent_id)`
- `is_group = true` accounts **cannot** be posted to (validated).
- `is_system = true` accounts cannot be deleted or retyped (AR control, AP control, Inventory, COGS, VAT payable/receivable, Retained earnings, Opening balance equity, Rounding off, Undeposited funds, Customer advances, Supplier advances, Stock adjustment, In-transit inventory).
- `requires_party = true` (AR/AP control) forces `party_id` on every entry line — this is what makes D4's subledger-as-query work.

**account_mappings** — `id, company_id, key, account_id` · `UNIQUE(company_id, key)`
The indirection layer between the posting engine and the CoA. Keys: `ar_control`, `ap_control`, `inventory`, `cogs`, `sales_revenue`, `sales_return`, `service_revenue`, `discount_allowed`, `discount_received`, `vat_output`, `vat_input`, `tds_receivable`, `tds_payable`, `rounding_off`, `stock_adjustment`, `inventory_in_transit`, `undeposited_funds`, `customer_advance`, `supplier_advance`, `retained_earnings`, `opening_balance_equity`, `bank_charges`, `default_cash`. **No account ID is ever hardcoded in PHP.** Overridable per branch later via an optional `branch_id`.

**journals** — `id, company_id, branch_id, journal_no, journal_date DATE, fiscal_year_id, period_id, type ENUM('manual','sale','sales_return','purchase','purchase_return','receipt','payment','expense','bank','stock','opening','closing','depreciation','reversal'), reference_type, reference_id, narration, status ENUM('posted','reversed'), reversed_by_journal_id NULL, reverses_journal_id NULL, total_debit, total_credit, created_by, created_at`
- `UNIQUE(company_id, journal_no)`, index `(company_id, journal_date)`, `(reference_type, reference_id)`
- There is **no draft journal**. A journal exists only because a document posted. Manual JVs are drafted in `journal_drafts` if that UX is wanted, or validated pre-insert.
- Immutable: no updates except setting `status`/`reversed_by_journal_id`.

**journal_entries** — `id, company_id, journal_id, line_no, account_id, debit DECIMAL(18,4) DEFAULT 0, credit DECIMAL(18,4) DEFAULT 0, journal_date DATE (denormalized for report speed), branch_id, party_type NULL, party_id NULL, warehouse_id NULL, cost_center_id NULL, product_id NULL, tax_rate_id NULL, narration`
- Indexes: `(company_id, account_id, journal_date)` ← the ledger/trial-balance workhorse; `(company_id, party_type, party_id, journal_date)` ← customer/supplier statements; `(company_id, branch_id, account_id, journal_date)` ← branch P&L; `(journal_id)`
- CHECK-equivalent (app-enforced): exactly one of `debit`/`credit` is non-zero, both non-negative.
- `journal_date` and `branch_id` are denormalized deliberately: it removes a join from every single financial report.

---

## 3. Parties

**customers** — `id, company_id, code, name, display_name, customer_type_id, pan_no, phone, email, address, price_list_id NULL, credit_limit, credit_days, opening_balance, opening_balance_date, balance_cache, is_active, notes` · `UNIQUE(company_id, code)`, index `(company_id, name)`, `(company_id, phone)`
`balance_cache` is a rebuildable convenience column, never a reporting source (PLAN D4).

**customer_types** — `id, company_id, name('retail'|'wholesale'|'dealer'|...), default_price_list_id, default_discount_percent`

**suppliers** — mirror of customers: `code, name, pan_no, phone, email, address, credit_days, opening_balance, opening_balance_date, balance_cache, is_active`

**customer_contacts / supplier_contacts** — `id, company_id, parent_id, name, designation, phone, email, is_primary`

---

## 4. Products and pricing

**product_categories** — `id, company_id, parent_id NULL, name, code` (hierarchical)
**brands** — `id, company_id, name, code`
**units** — `id, company_id, name, code, is_fractional BOOL, base_unit_id NULL, conversion_factor DECIMAL(18,6)`

**tax_rates** — `id, company_id, name('VAT 13%'), rate DECIMAL(9,6), type('vat'|'excise'|'none'), is_compound, effective_from, effective_to NULL, sales_account_id, purchase_account_id, is_active`
Rate history matters: never edit a rate, create a new one with `effective_from`. Documents snapshot `tax_rate_id` **and** `tax_rate_percent`.

**products** —
`id, company_id, sku, barcode NULL, name, description, product_type ENUM('goods','service'), category_id, brand_id, unit_id, purchase_unit_id, tax_rate_id, is_taxable, hs_code, purchase_price, cost_price, retail_price, wholesale_price, dealer_price, min_selling_price, mrp, reorder_level, max_level, track_stock BOOL, track_batch BOOL, track_serial BOOL, has_expiry BOOL, shelf_life_days, image_path, is_active, created_by`
- `UNIQUE(company_id, sku)`, `UNIQUE(company_id, barcode)` (nullable), index `(company_id, name)`, `(company_id, category_id, is_active)`
- `product_type='service'` ⇒ `track_stock=false` ⇒ no stock movement, no COGS.
- Batch/serial flags are reserved columns now, implemented later (Phase 9). Adding them later means a stock-ledger migration — cheap to reserve, expensive to retrofit.

**product_barcodes** — `id, company_id, product_id, barcode, unit_id, pack_qty` (multi-barcode / carton scanning)

**price_lists** — `id, company_id, name, currency, price_mode('inclusive'|'exclusive'), valid_from, valid_to, priority, is_active`
**price_list_items** — `id, company_id, price_list_id, product_id, unit_price, min_price NULL, discount_percent` · `UNIQUE(price_list_id, product_id)`
**price_rules** (qty breaks / promos / customer-specific) — `id, company_id, product_id NULL, category_id NULL, customer_id NULL, customer_type_id NULL, price_list_id NULL, min_qty, max_qty NULL, mode('fixed_price'|'percent_off'|'amount_off'), value, valid_from, valid_to, priority, is_active`
Resolution order (highest priority wins, ties → most specific): estimate lock → customer-specific → active promo → qty break → price list → customer-type default → product default. Implemented once in `PricingService`, tested with a truth table.

**price_approvals** — `id, company_id, approvable_type, approvable_id, line_ref, product_id, default_price, min_price, requested_price, requested_by, approved_by NULL, status('pending'|'approved'|'rejected'), reason, decided_at`
This is the discount-leakage control. Invariant I11 references it.

---

## 5. Inventory

**warehouses** — `id, company_id, branch_id, code, name, type ENUM('main','shop','godown','in_transit','damaged'), address, inventory_account_id NULL, is_default, is_active` · `UNIQUE(company_id, code)`
`type='in_transit'` warehouses are auto-created per branch pair and hidden from selling UIs.

**stock_movements** (append-only ledger, **no running balance** — PLAN D3) —
`id, company_id, branch_id, product_id, warehouse_id, movement_date DATE, type ENUM('opening','purchase','purchase_return','sale','sales_return','transfer_out','transfer_in','adjustment_in','adjustment_out','assembly_in','assembly_out'), reference_type, reference_id, reference_line_id, qty_in, qty_out, unit_cost DECIMAL(18,6), total_cost, journal_id NULL, batch_id NULL, created_by, created_at`
- Indexes: `(company_id, product_id, warehouse_id, movement_date, id)` ← stock ledger + FIFO ordering; `(reference_type, reference_id)`; `(company_id, movement_date)`
- Never updated. Never deleted. Corrections are new movements.

**stock_balances** (aggregate, the lock target) —
`id, company_id, product_id, warehouse_id, qty_on_hand, qty_reserved, avg_cost DECIMAL(18,6), stock_value, last_movement_at, updated_at` · `UNIQUE(company_id, product_id, warehouse_id)`
Always locked `FOR UPDATE` before any stock write, in `ORDER BY product_id, warehouse_id` to prevent deadlocks on multi-line documents.

**stock_layers** (FIFO cost layers; also populated under WAC for traceability) —
`id, company_id, product_id, warehouse_id, source_movement_id, receipt_date, qty_received, qty_remaining, unit_cost DECIMAL(18,6), is_exhausted BOOL, batch_id NULL`
- Index `(company_id, product_id, warehouse_id, is_exhausted, receipt_date, id)`

**stock_layer_consumptions** — `id, company_id, layer_id, movement_id, qty, unit_cost, total_cost`
Lets a sales return reverse COGS at the exact original cost, and makes FIFO auditable line by line.

**stock_transfers** — `id, company_id, from_warehouse_id, to_warehouse_id, transfer_no, transfer_date, expected_date, status('draft'|'in_transit'|'posted'|'cancelled'), dispatched_by, received_by, notes` (+ standard posting columns)
**stock_transfer_items** — `id, transfer_id, product_id, qty_sent, qty_received, unit_cost, remarks`

**stock_adjustments** — `id, company_id, branch_id, warehouse_id, adjustment_no, adjustment_date, reason ENUM('damaged','lost','expired','physical_count','opening_correction','other'), notes, total_value, journal_id` (+ posting columns)
**stock_adjustment_items** — `id, adjustment_id, product_id, system_qty, counted_qty, diff_qty, unit_cost, total_cost`

**batches** (reserved, Phase 9) — `id, company_id, product_id, batch_no, mfg_date, expiry_date, notes`

---

## 6. Sales side

**estimates** — `id, company_id, branch_id, estimate_no, estimate_date, valid_until, customer_id, customer_name_snapshot, customer_pan_snapshot, price_list_id, salesperson_id, currency, subtotal, discount_amount, discount_percent, taxable_amount, tax_amount, round_off, grand_total, status ENUM('draft','sent','viewed','accepted','rejected','expired','converted','cancelled'), converted_sale_id NULL, converted_at, notes, terms, created_by` · `UNIQUE(company_id, branch_id, estimate_no)`, index `(company_id, customer_id, status)`, `(company_id, status, valid_until)`

**estimate_items** — `id, estimate_id, line_no, product_id, description, unit_id, qty, unit_price, price_source, resolved_min_price, discount_percent, discount_amount, tax_rate_id, tax_rate_percent, tax_amount, line_total, is_price_locked BOOL`
`is_price_locked` + the snapshot columns are the mechanism behind PLAN D11 / invariant I12. Conversion copies these columns verbatim; it does **not** call `PricingService` again.

**sales** (invoices) —
`id, company_id, branch_id, warehouse_id, invoice_no, invoice_date, due_date, customer_id, customer_name_snapshot, customer_pan_snapshot, customer_address_snapshot, estimate_id NULL, salesperson_id, price_list_id, sale_type ENUM('cash','credit','pos'), currency, exchange_rate, subtotal, discount_amount, taxable_amount, non_taxable_amount, tax_amount, round_off, grand_total, cogs_total, paid_amount, due_amount, payment_status ENUM('unpaid','partial','paid'), status ENUM('draft','posted','cancelled'), returned_amount, journal_id, is_printed, print_count, first_printed_at, printed_by, notes, terms, created_by` (+ posting/cancel columns)
- `UNIQUE(company_id, branch_id, invoice_no)`, `(company_id, customer_id, status)`, `(company_id, invoice_date, status)`, `(company_id, payment_status, due_date)` ← aging, `(company_id, salesperson_id, invoice_date)`
- `paid_amount`/`due_amount` are maintained **only** by the allocation service under a row lock.
- `is_printed`/`print_count` reserved for IRD-style controls (PLAN D13).

**sale_items** — `id, sale_id, line_no, product_id, description, unit_id, qty, unit_price, price_source, resolved_min_price, default_price, discount_percent, discount_amount, tax_rate_id, tax_rate_percent, tax_amount, line_total, unit_cost, cogs_amount, returned_qty, warehouse_id`
`unit_cost`/`cogs_amount` are written **by the costing engine at post time**, not by the UI.

**sales_returns** — `id, company_id, branch_id, warehouse_id, return_no, return_date, customer_id, sale_id NULL, reason, subtotal, discount_amount, tax_amount, round_off, grand_total, cogs_total, refund_mode ENUM('cash','bank','credit_note'), credit_note_balance, journal_id, status` (+ posting columns)
**sales_return_items** — `id, sales_return_id, sale_item_id NULL, product_id, qty, unit_price, tax_rate_id, tax_rate_percent, tax_amount, line_total, unit_cost, cogs_amount`
`sale_item_id` is what lets the return reverse COGS at the original cost rather than current cost — important, and frequently gotten wrong.

---

## 7. Purchase side

**purchase_orders / purchase_order_items** — `po_no, po_date, expected_date, supplier_id, warehouse_id, status('draft'|'sent'|'partial'|'received'|'closed'|'cancelled')`; items carry `qty_ordered, qty_received, unit_price`
**goods_receipts / goods_receipt_items** — `grn_no, grn_date, supplier_id, warehouse_id, purchase_order_id NULL`; GRN posts **stock** (Dr Inventory / Cr Goods Received Not Invoiced) when GRN and invoice are separate events
**purchases** (supplier invoices) — `id, company_id, branch_id, warehouse_id, bill_no (supplier's), internal_no, bill_date, due_date, supplier_id, supplier_pan_snapshot, purchase_order_id NULL, goods_receipt_id NULL, subtotal, discount_amount, taxable_amount, tax_amount, freight_amount, other_charges, round_off, grand_total, paid_amount, due_amount, payment_status, is_vat_claimable, journal_id, status` · `UNIQUE(company_id, supplier_id, bill_no)` ← duplicate-bill protection, genuinely useful
**purchase_items** — `id, purchase_id, product_id, qty, unit_id, unit_price, discount_amount, tax_rate_id, tax_rate_percent, tax_amount, freight_allocated, landed_unit_cost, line_total, received_qty, returned_qty`
`landed_unit_cost = (line net + allocated freight + non-claimable tax) / qty` — this, not `unit_price`, is what enters inventory.
**purchase_returns / purchase_return_items** — mirror of sales returns, with `purchase_item_id` for exact cost reversal

---

## 8. Payments

**payments** — `id, company_id, branch_id, payment_no, payment_date, direction ENUM('in','out'), party_type('customer'|'supplier'|'other'), party_id, total_amount, allocated_amount, unallocated_amount, tds_amount, discount_amount, reference_no, notes, journal_id, status` · index `(company_id, party_type, party_id, payment_date)`
**payment_lines** (tenders — split payment lives here) — `id, payment_id, method ENUM('cash','bank','cheque','card','wallet','credit_note','advance'), amount, bank_account_id NULL, cash_account_id NULL, cheque_no, cheque_date, cleared_at NULL, credit_note_id NULL, reference`
Uncleared cheques sit in **Undeposited Funds** until `cleared_at` is set — a real requirement that most small ERPs ignore.
**payment_allocations** — `id, payment_id, allocatable_type ('sale'|'purchase'|'sales_return'|'purchase_return'), allocatable_id, amount, discount_amount` · `UNIQUE(payment_id, allocatable_type, allocatable_id)`

---

## 9. Banking and expenses

**bank_accounts** — `id, company_id, branch_id NULL, name, bank_name, branch_name, account_no, account_type('current'|'savings'|'od'), account_id (GL, 1:1, UNIQUE), opening_balance, opening_date, current_balance_cache, currency, is_default, is_active`
**bank_transactions** — `id, company_id, bank_account_id, txn_no, txn_date, type ENUM('deposit','withdrawal','transfer_in','transfer_out','charge','interest','customer_receipt','supplier_payment','adjustment'), amount, direction('debit'|'credit'), counter_bank_account_id NULL, reference_type, reference_id, reference_no, description, journal_id, reconciliation_id NULL, reconciled_at, status` · index `(company_id, bank_account_id, txn_date)`, `(reconciliation_id)`
**bank_statement_imports** — `id, company_id, bank_account_id, file_path, imported_by, from_date, to_date, row_count, status`
**bank_statement_lines** — `id, import_id, line_no, txn_date, description, reference, debit, credit, running_balance, match_status ENUM('unmatched','matched','ignored','manual'), matched_transaction_id NULL, matched_by, matched_at, hash` — `hash` (date+amount+ref) makes re-import idempotent
**bank_reconciliations** — `id, company_id, bank_account_id, statement_date, opening_book_balance, closing_book_balance, statement_balance, outstanding_deposits, outstanding_payments, adjusted_balance, difference, status('draft'|'completed'), completed_by, completed_at`

**expense_categories** — `id, company_id, name, account_id (GL expense account), is_active`
**expenses** — `id, company_id, branch_id, expense_no, expense_date, category_id, payee_type NULL, payee_id NULL, payee_name, amount, tax_rate_id NULL, tax_amount, is_vat_claimable, total_amount, payment_method ENUM('cash','bank','credit'), bank_account_id NULL, cash_account_id NULL, supplier_id NULL, reference_no, bill_no, description, journal_id, status` (+ posting columns)

---

## 10. Fixed assets (Phase 9, columns reserved)

**fixed_assets** — `id, company_id, code, name, category, purchase_date, purchase_cost, salvage_value, useful_life_months, depreciation_method('slm'|'wdv'), asset_account_id, depreciation_account_id, accumulated_account_id, status('active'|'disposed'), disposed_at, disposal_amount`
**depreciation_entries** — `id, company_id, fixed_asset_id, period_id, amount, journal_id`

---

## 11. Migration ordering

1. companies, branches, users, roles/permissions, settings, audit_logs, attachments, plans/subscriptions
2. fiscal_years, accounting_periods, accounts, account_mappings, document_sequences
3. journals, journal_entries
4. units, tax_rates, product_categories, brands, products, product_barcodes, price_lists, price_list_items, price_rules
5. warehouses, stock_balances, stock_movements, stock_layers, stock_layer_consumptions, stock_transfers(+items), stock_adjustments(+items), batches
6. suppliers, purchase_orders(+items), goods_receipts(+items), purchases(+items), purchase_returns(+items)
7. customer_types, customers, contacts, estimates(+items), price_approvals, sales(+items), sales_returns(+items)
8. payments, payment_lines, payment_allocations
9. bank_accounts, bank_transactions, statement imports/lines, bank_reconciliations, expense_categories, expenses
10. fixed_assets, depreciation_entries

Seeders: CoA template (generic + Nepal variant), default units, VAT 13%/0%/exempt tax rates, account_mappings, roles/permissions, default branch/warehouse, demo company.