# ERP Database Architecture & Module Reference

> **Platform:** Node.js microservices + React admin panel (`panel/`) + POS terminal (`pos-terminal/`)  
> **Engine:** MySQL 8 / InnoDB, `utf8mb4_unicode_ci`  
> **Domain focus:** Restaurant / F&B operations (Saudi Arabia — SAR, ZATCA, GOSI/WPS)

---

## 1. Architecture Overview

The system uses a **database-per-service** pattern. Each backend module connects to its own MySQL database via environment variables (`DB_<SERVICE>_NAME`, `DB_<SERVICE>_USER`, etc.) defined in `.env`. Connection pooling is centralized in `packages/shared/src/db.js`.

### Key design rules

| Rule | Description |
|------|-------------|
| **Logical linking** | Cross-module references use integer IDs (`company_id`, `branch_id`, `customer_id`, …) without foreign keys across databases |
| **Multi-tenancy** | Most tables are scoped by `company_id`; branch-scoped data also uses `branch_id` |
| **Shared databases** | `orders` (POS) and `sales` share `sayedkhattab_erp_sales`; `notifications` shares the auth database |
| **Legacy schemas** | `menu` and `branches` databases originate from a Laravel import — Node migrations only verify, not recreate them |
| **Stateless services** | `kitchen`, `central-kitchen`, `recipes`, and `transfers` have no dedicated DB; they read/write other service databases |

### Database catalog

| Env key | Typical DB name | Service(s) | Schema source |
|---------|-----------------|------------|---------------|
| `DB_AUTH_NAME` | `sayedkhattab_erp_auth` | auth, notifications | Node migrate |
| `DB_CORE_NAME` | `sayedkhattab_erp_core` | core | Node migrate |
| `DB_ACCOUNTING_NAME` | `sayedkhattab_erp_accounting` | accounting | Node migrate |
| `DB_INVENTORY_NAME` | `sayedkhattab_erp_inventory` | inventory, recipes, transfers, central-kitchen | Node migrate |
| `DB_SALES_NAME` / `DB_ORDERS_NAME` | `sayedkhattab_erp_sales` | sales, pos, kitchen | Node migrate (POS tables + sales tables) |
| `DB_PURCHASING_NAME` | `sayedkhattab_erp_purchasing` | purchasing | Node migrate |
| `DB_HR_NAME` | `sayedkhattab_erp_hr` | hr | Node migrate |
| `DB_MANUFACTURING_NAME` | `sayedkhattab_erp_manufacturing` | manufacturing | Node migrate |
| `DB_BRANCHES_NAME` | `sayedkhattab_erp_branches` | branches, pos, kitchen, delivery | Laravel import |
| `DB_MENU_NAME` | `sayedkhattab_erp_menu` | menu, voice-order | Laravel import |
| `DB_QUALITY_NAME` | `sayedkhattab_erp_quality` | quality | Node migrate |
| `DB_LOYALTY_NAME` | `sayedkhattab_erp_loyalty` | loyalty | Node migrate |
| `DB_MARKETING_NAME` | `sayedkhattab_erp_marketing` | marketing | Node migrate |
| `DB_DELIVERY_NAME` | `sayedkhattab_erp_delivery` | delivery | Node migrate |
| `DB_RESERVATIONS_NAME` | `sayedkhattab_erp_reservations` | reservations | Node migrate |
| `DB_WASTE_NAME` | `sayedkhattab_erp_waste` | waste | Node migrate |
| `DB_ASSETS_NAME` | `sayedkhattab_erp_assets` | assets | Node migrate |
| `DB_PROJECTS_NAME` | `sayedkhattab_erp_projects` | projects | Node migrate |
| `DB_MAINTENANCE_NAME` | `sayedkhattab_erp_maintenance` | maintenance | Node migrate |
| `DB_VOICE_ORDER_NAME` | `sayedkhattab_erp_voice_order` | voice-order | Node migrate |

---

## 2. Core & Platform

### Database: `core`

| Table | Purpose |
|-------|---------|
| `companies` | Legal entities — name (AR/EN), code, currency, VAT/CR, address, timezone, locale, domain (`restaurant`), group hierarchy |
| `company_defaults` | Per-company GL account codes, cost center, manufacturing WIP/FG/RM accounts, sales targets |
| `erp_modules` | Registry of enabled ERP modules (accounting, inventory, pos, hr, …) with AR/EN labels and category |

**Features:** Multi-company setup, module toggling, default accounting mappings for downstream posting.

### Database: `auth` (+ notifications)

| Table | Purpose |
|-------|---------|
| `admins` | Platform super-admins (separate from company users) |
| `users` | Company ERP users — role, locale, optional `employee_id` link to HR |
| `roles` | Named roles (system + custom) |
| `permissions` | Granular permission codes per module/action |
| `role_permissions` | Role ↔ permission mapping |
| `user_companies` | Users allowed per company |
| `user_branches` | Branch-level access restriction |
| `platform_settings` | Global branding, support contacts, default locale/currency/timezone |
| `user_notifications` | In-app notification feed (also used by notifications service) |

**Features:** RBAC, multi-company/branch scoping, JWT auth, platform branding, notification inbox.

---

## 3. Finance

### Database: `accounting`

| Table | Purpose |
|-------|---------|
| `accounting_settings` | Company fiscal config, ZATCA seller details, COA seed flag |
| `fiscal_years` | Named fiscal periods with open/closed status |
| `accounts` | Chart of accounts — hierarchical, typed (asset/liability/equity/income/expense), tax category |
| `journal_entries` / `journal_entry_lines` | General ledger postings (draft/posted/cancelled) |
| `zatca_devices` | E-invoicing device credentials, CSID, invoice counter |
| `tax_invoices` / `tax_invoice_lines` | ZATCA-compliant invoices, credit/debit notes, QR/hash/XML, clearance status |

**Features:** Double-entry accounting, Saudi VAT (15%), ZATCA Phase-2 e-invoicing, fiscal year management.

### Database: `purchasing`

| Table | Purpose |
|-------|---------|
| `suppliers` | Vendor master — VAT/CR, payment terms, contact info |
| `purchase_orders` / `purchase_order_lines` | PO lifecycle (draft → received), links to inventory items |
| `purchase_receipts` / `purchase_receipt_lines` | Goods receipt with batch/expiry; posts to inventory via `stock_entry_id` |

**Features:** Supplier management, purchase orders, goods receipt, inventory integration.

### Database: `assets`

| Table | Purpose |
|-------|---------|
| `asset_categories` | Asset classes with default useful life |
| `fixed_assets` | Asset register — cost, depreciation (straight-line), status, disposal |

**Features:** Fixed asset tracking for kitchen equipment, POS/IT, vehicles, leasehold improvements.

---

## 4. Inventory & Supply Chain

### Database: `inventory`

| Table | Purpose |
|-------|---------|
| `item_categories` | Hierarchical item classification |
| `items` | SKU master — type (raw/semi-finished/finished/packaging/consumable), UOM, tax, halal, batch/expiry tracking, reorder levels |
| `warehouses` | Storage locations per branch — store, kitchen, cold room, freezer, central kitchen |
| `stock_entries` / `stock_entry_lines` | Stock movements — receipt, issue, transfer, adjustment, opening, wastage |
| `stock_ledger` | Immutable quantity/valuation ledger per item × warehouse |
| `inventory_settings` | Company-level inventory policy (negative stock, default warehouse) |
| `recipes` / `recipe_ingredients` | Bill of materials for menu items; optional `menu_product_id` link |
| `branch_transfers` / `branch_transfer_lines` | Inter-branch stock transfers with in-transit/received workflow |
| `branch_requisitions` / `branch_requisition_lines` | Branch stock requests → approval → dispatch → receive |

**Features:** Perpetual inventory, batch/expiry, recipe costing, inter-branch transfers & requisitions, central kitchen support.

### Database: `manufacturing`

| Table | Purpose |
|-------|---------|
| `boms` / `bom_items` | Bill of materials for production items |
| `work_orders` / `work_order_materials` | Production runs — material consumption, FG receipt, stock entry refs |
| `production_plans` / `production_plan_lines` | Planned production schedules linked to work orders |

**Features:** BOM management, work orders, production planning, GL posting via `journal_entry_id`.

---

## 5. Sales, POS & Orders

### Database: `sales` (shared with POS `orders`)

#### Sales module tables

| Table | Purpose |
|-------|---------|
| `customers` | Customer CRM — individual/business, VAT/CR, phone, soft delete |
| `customer_addresses` | Delivery addresses with geo coordinates |
| `platform_invoices` / `platform_invoice_items` | Synced invoices from external channels; ZATCA/financial posting status |
| `sales_schema_meta` | Schema version tracker |

#### POS / Orders tables (same database)

| Table | Purpose |
|-------|---------|
| `order_types` | Dine-in, takeaway, delivery, drive-thru — with field requirements |
| `order_channels` | Order source (POS, app, aggregator, …) |
| `order_statuses` | Kitchen/fulfillment status pipeline |
| `payment_methods` | Cash, card, Mada, etc. |
| `orders` | Order header — branch, customer, totals, payment, financial posting refs (`tax_invoice_id`, `journal_entry_id`, `stock_entry_id`) |
| `order_items` | Line items — product, kitchen section routing, modifiers (JSON), KDS delivery tracking |
| `pos_schema_meta` | POS schema version |

**Features:** Customer management, omnichannel order capture, payment processing, automatic GL/inventory/ZATCA posting, platform invoice sync.

---

## 6. Restaurant Operations

### Database: `branches` (Laravel legacy)

| Table | Purpose |
|-------|---------|
| `branches` | Branch master — location, hours, delivery zones, operational status, franchise/menu links, sync metadata |
| `branch_kitchen_sections` | KDS stations (grill, fry, cold, …) per branch |
| `branch_roles` / `branch_users` | POS terminal user accounts scoped to a branch |
| `branch_product_kitchen_sections` | Product → kitchen section routing |
| `branch_category_kitchen_sections` | Category → kitchen section routing (fallback) |

**Features:** Multi-branch management, operating hours, delivery coverage, kitchen display routing, branch POS login.

### Database: `menu` (Laravel legacy — OCIMS)

| Table | Purpose |
|-------|---------|
| `ocims_franchises` | Menu franchise/brand grouping |
| `ocims_categories` | Menu categories — time-based availability, images, hierarchy |
| `ocims_products` | Menu products — pricing (internal/external), allergens, calories, availability windows |
| `product_option_groups` / `product_options` | Modifiers/add-ons (size, extras, …) |

**Features:** Digital menu management, product modifiers, time-based availability, franchise-scoped menus, image storage.

### Database: `delivery`

| Table | Purpose |
|-------|---------|
| `delivery_drivers` | Driver fleet — vehicle type, contact info |
| `delivery_branch_drivers` | Driver ↔ branch assignment |
| `delivery_assignments` | Order delivery lifecycle — assign, pickup, deliver, GPS address |

**Features:** Driver management, order-to-driver assignment, delivery status tracking.

### Database: `reservations`

| Table | Purpose |
|-------|---------|
| `reservation_floor_plans` | Visual floor layouts (JSON) |
| `reservation_tables` | Table inventory — capacity, zone, position, status |
| `reservation_bookings` | Table reservations — party size, time slot, source |
| `reservation_waitlist` | Walk-in waitlist queue |

**Features:** Floor plan designer, table management, bookings, waitlist.

### Database: `waste`

| Table | Purpose |
|-------|---------|
| `waste_reasons` | Categorized waste reasons (spoilage, prep error, expired, …) |
| `waste_entries` | Waste log — qty, cost, approval workflow, inventory posting |
| `waste_thresholds` | Alert limits per branch/period |

**Features:** Food waste tracking, cost impact, approval chain, inventory integration.

### Database: `quality`

| Table | Purpose |
|-------|---------|
| `quality_inspections` | Scheduled/completed inspections with scores |
| `quality_temperature_logs` | Cold-chain temperature monitoring |
| `quality_haccp_points` / `quality_haccp_logs` | HACCP critical control points and readings |
| `quality_certificates` | Staff/supplier food-safety certificates with expiry |

**Features:** Food safety compliance, HACCP, temperature logs, inspection scoring, certificate tracking.

### Database: `voice_order`

| Table | Purpose |
|-------|---------|
| `voice_settings` | AI voice ordering config per franchise/branch (LLM, greeting, timeout) |
| `menu_search_index` | Full-text searchable menu index for voice matching |
| `menu_synonyms` | Arabic/English phrase → product/category mappings |
| `voice_sessions` / `voice_session_messages` | Conversational order sessions and chat history |
| `voice_index_jobs` | Background menu re-indexing jobs |

**Features:** AI-powered voice ordering kiosk, semantic menu search, session cart management.

---

## 7. Customer Engagement

### Database: `loyalty`

| Table | Purpose |
|-------|---------|
| `loyalty_programs` | Points rules, tier thresholds (bronze/silver/gold/platinum) |
| `loyalty_members` | Member profiles linked to customers |
| `loyalty_rewards` | Redeemable rewards (discounts, free items) |
| `loyalty_campaigns` | Bonus points / multiplier promotions |
| `loyalty_transactions` | Points earn/redeem/adjust ledger |

**Features:** Points-based loyalty, tiered membership, campaigns, reward redemption.

### Database: `marketing`

| Table | Purpose |
|-------|---------|
| `marketing_coupons` | Promo codes — audience (customer/employee/public), usage limits, branch/channel scope |
| `marketing_employee_policies` | Staff discount policies by department/job title |
| `marketing_redemptions` | Coupon/policy usage audit trail |

**Features:** Coupon management, employee meal discounts, redemption tracking.

---

## 8. Human Resources

### Database: `hr`

| Area | Tables | Purpose |
|------|--------|---------|
| **Organization** | `departments`, `job_titles`, `employees`, `employee_documents` | Org structure, Saudi-compliant employee records (Iqama, GOSI, Qiwa), document vault |
| **Leave** | `leave_types`, `leave_policies`, `leave_allocations`, `leave_applications`, `leave_ledger_entries`, `hr_holidays` | Leave types, balances, approval workflow, public holidays |
| **Attendance** | `shift_types`, `shift_assignments`, `employee_checkins`, `attendances` | Shifts, biometric/device check-ins, daily attendance |
| **Payroll** | `salary_components`, `salary_structures`, `salary_structure_lines`, `salary_assignments`, `payroll_runs`, `salary_slips`, `salary_slip_lines`, `additional_salaries`, `hr_payroll_settings` | Saudi payroll — GOSI rates, WPS export, GL journal link |
| **Lifecycle** | `onboarding_templates`, `onboarding_checklists`, `onboarding_tasks`, `employee_transfers`, `employee_separations`, `ff_settlements` | Onboarding, transfers, exit & final settlement (EOS) |
| **Talent** | `job_openings`, `job_applicants`, `training_programs`, `training_events`, `training_attendees` | Recruitment pipeline, training management |
| **Performance** | `appraisal_templates`, `appraisal_template_items`, `appraisal_cycles`, `employee_appraisals`, `appraisal_scores` | Performance review cycles |
| **Compliance** | `hr_compliance_alert_log`, `hr_email_settings` | Document expiry alerts, SMTP for payslips/leave notifications |

**Features:** Full HR lifecycle for Saudi Arabia — GOSI, WPS, Qiwa status, leave, attendance, payroll, recruitment, training, appraisals, EOS settlement.

---

## 9. Operations Management

### Database: `projects`

| Table | Purpose |
|-------|---------|
| `project_types` | Project categories (branch opening, renovation, IT, …) |
| `projects` | Project tracker — budget, actual cost, status, progress |
| `project_milestones` | Milestone checklist per project |

**Features:** Capital/operational project tracking for expansions and rollouts.

### Database: `maintenance`

| Table | Purpose |
|-------|---------|
| `maintenance_categories` | Equipment categories (HVAC, refrigeration, plumbing, …) |
| `maintenance_requests` | Work orders — priority, downtime, cost, optional `fixed_asset_id` link |

**Features:** Facility & equipment maintenance tracking linked to assets.

---

## 10. Cross-Module Integration Map

```
┌─────────┐     company_id      ┌──────────────┐
│  core   │◄───────────────────►│  all modules │
└─────────┘                     └──────────────┘

POS order completed ──► orders (sales DB)
                      ├──► accounting: tax_invoice + journal_entry
                      ├──► inventory: stock_entry (recipe consumption)
                      ├──► sales: platform_invoice (if external)
                      └──► loyalty: points transaction

Purchase receipt ──► purchasing ──► inventory: stock_entry (receipt)

Payroll approved ──► hr: payroll_run ──► accounting: journal_entry

Manufacturing WO ──► manufacturing ──► inventory: issue + receipt stock entries
                                    └──► accounting: journal_entry

Waste approved ──► waste ──► inventory: stock_entry (wastage)
```

**Shared identifiers:** `company_id` (tenant), `branch_id` (location), `user_id` / `created_by` (audit), external refs stored as integers without cross-DB FK constraints.

---

## 11. Module Feature Summary

| Module | Category | Key capabilities |
|--------|----------|------------------|
| **Core** | Platform | Companies, module registry, default GL mappings |
| **Auth / Users** | Platform | RBAC, multi-company access, notifications |
| **Accounting** | Finance | COA, journals, ZATCA e-invoicing |
| **Purchasing** | Finance | Suppliers, POs, goods receipt |
| **Assets** | Finance | Fixed asset register & depreciation |
| **Inventory** | Operations | Items, warehouses, stock ledger, recipes (BOM) |
| **Transfers** | Operations | Inter-branch transfers & requisitions (inventory DB) |
| **Manufacturing** | Operations | BOMs, work orders, production plans |
| **Central Kitchen** | Operations | Centralized production & branch fulfillment (inventory DB) |
| **Sales** | Revenue | Customers, order history, platform invoice sync |
| **POS** | Revenue | In-store ordering, payments, KDS routing |
| **Kitchen (KDS)** | Revenue | Kitchen display stats & section delivery (orders/branches DB) |
| **Branches** | Restaurant | Locations, hours, delivery zones, POS users |
| **Menu** | Restaurant | Categories, products, modifiers, availability |
| **Delivery** | Restaurant | Drivers, order assignment, delivery tracking |
| **Reservations** | Restaurant | Floor plans, tables, bookings, waitlist |
| **Waste** | Restaurant | Waste logging, thresholds, inventory impact |
| **Quality** | Restaurant | Inspections, HACCP, temperature logs, certificates |
| **Voice Order** | Restaurant | AI voice ordering with menu search index |
| **Loyalty** | CRM | Points, tiers, rewards, campaigns |
| **Marketing** | CRM | Coupons, employee discount policies |
| **HR** | People | Employees, leave, attendance, payroll (Saudi), recruitment, training, appraisals |
| **Projects** | Operations | Project & milestone tracking |
| **Maintenance** | Operations | Equipment maintenance requests |

---

## 12. Schema Maintenance

- **Run all migrations:** `npm run migrate` (executes each service's `src/db/migrate.js`)
- **Legacy import required:** `menu` and `branches` databases must be imported before first run
- **New databases:** See `scripts/setup-restaurant-module-databases.sql` and `scripts/setup-assets-database.sql`
- **HR detailed spec:** See `docs/hr/04-HR_DATA_MODEL_SPEC.md` for extended HR documentation

---

*Generated from service migration files and codebase analysis — June 2026.*