Database Schema Reference

Database Overview

Property	Value
DBMS	MySQL 8.0.46
Engine	InnoDB (default)
Character Set	UTF-8 (`utf8mb4`)
Collation	`utf8mb4_unicode_ci`
Production DB	`lgo_erp_prod`
Staging DB	`lgo_erp_staging`
Total Tables	14

Connection Configuration

Parameter	Value
Host	`localhost`
User	`lgo`
Password	(stored in environment variable — never in code)
Port	`3306` (default)
Connection Pool Size	5
Connection Timeout	10,000 ms
Acquire Timeout	10,000 ms

Pool Configuration:
  min: 0
  max: 5
  idleTimeout: 60,000 ms
  enableKeepAlive: true
  keepAliveInitialDelay: 0

Entity Relationship Diagram

┌──────────────┐       ┌──────────────────┐       ┌──────────────┐
│    users     │       │   requisitions   │       │  customers   │
│──────────────│       │──────────────────│       │──────────────│
│ id (PK)      │◄──┐   │ id (PK)          │       │ id (PK)      │
│ name         │   ├───│ requested_by(FK) │       │ company_name │
│ email (UQ)   │   ├───│ supervisor_id(FK)│       │ contact_person│
│ password_hash│   ├───│ manager_id (FK)  │       │ phone        │
│ role         │   ├───│ accounts_id (FK) │       │ email        │
│ status       │   └───│ md_id (FK)       │       │ address      │
│ created_at   │       │ status           │       │ city         │
│ updated_at   │       │ amount           │       │ state        │
└──────┬───────┘       │ category         │       │ credit_limit │
       │               │ priority         │       │outstanding_bal│
       │               │ title            │       │ status       │
       │               │ description      │       │ notes        │
       │               └──────────────────┘       │ created_at   │
       │                                          │ updated_at   │
       │               ┌──────────────────┐       └──────┬───────┘
       │               │    inventory     │              │
       │               │──────────────────│              │
       │               │ id (PK)          │              │
       │               │ name             │              │
       │               │ sku (UQ)         │              │
       │               │ category         │              │
       │               │ unit_of_measure  │              │
       │               │ current_stock    │              │
       │               │ min_stock_level  │              │
       │               │ unit_cost        │              │
       │               │ selling_price    │              │
       │               │ status           │              │
       │               │ description      │              │
       │               │ created_at       │              │
       │               │ updated_at       │              │
       │               └───┬───┬───┬──────┘              │
       │                   │   │   │                     │
       │                   │   │   └────────────────────┐│
       │                   │   └──────────────┐         ││
       │                   │                  │         ││
       │               ┌───▼──────────┐  ┌────▼─────┐  ┌▼▼────────────┐
       │               │   purchases  │  │  sales   │  │inventory_adj │
       │               │──────────────│  │──────────│  │──────────────│
       │               │ id (PK)      │  │ id (PK)  │  │ id (PK)      │
       │               │inventory_id  │  │customer_ │  │inventory_id  │
       │               │supplier_name │  │  id (FK) │  │adj_quantity  │
       │               │ quantity     │  │inventory_│  │ reason       │
       │               │ unit_cost    │  │  id (FK) │  │ notes        │
       │               │ total_cost   │  │ quantity │  │ adj_date     │
       │               │ purchase_date│  │unit_price│  │created_by(FK)│──►users
       │               │ notes        │  │total_amt │  │ created_at   │
       │               │ created_at   │  │sale_date │  │ updated_at   │
       │               │ updated_at   │  │ status   │  └──────────────┘
       │               └──────────────┘  │ notes    │
       │                                 │created_at│
       │               ┌──────────────┐  │updated_at│
       │               │production_run│  └────┬─────┘
       │               │──────────────│       │
       │               │ id (PK)      │  ┌────▼──────────┐
       │               │product_name  │  │  ar_invoices   │
       │               │ start_date   │  │────────────────│
       │               │ end_date     │  │ id (PK)        │
       │               │ batches      │  │ sale_id (FK)   │──►sales
       │               │ input_volume │  │ invoice_number │
       │               │output_volume │  │ amount         │
       │               │ input_cost   │  │ status         │
       │               │ output_cost  │  │ due_date       │
       │               │ yield_percent│  │ notes          │
       │               │cost_per_litre│  │ created_at     │
       │               │ status       │  │ updated_at     │
       │               │ notes        │  └────┬──────────┘
       │               │ created_at   │       │
       │               │ updated_at   │  ┌────▼──────────┐
       │               └──────────────┘  │  ar_payments   │
       │                                 │────────────────│
       │               ┌──────────────┐  │ id (PK)        │
       │               │   expenses   │  │ invoice_id(FK) │
       │               │──────────────│  │ amount         │
       │               │ id (PK)      │  │ payment_method │
       │               │ category     │  │ reference_num  │
       │               │ description  │  │ payment_date   │
       │               │ amount       │  │ notes          │
       │               │payment_method│  │ created_at     │
       │               │ status       │  │ updated_at     │
       │               │ vendor       │  └────────────────┘
       │               │ reference_num│
       │               │ notes        │  ┌────────────────┐
       │               │ expense_date │  │   suppliers    │
       │               │ created_at   │  │────────────────│
       │               │ updated_at   │  │ id (PK)        │
       │               └──────────────┘  │ company_name   │
       │                                 │ contact_person │
       │               ┌──────────────┐  │ phone          │
       │               │   settings   │  │ email          │
       │               │──────────────│  │ address        │
       │               │ id (PK)      │  │ city           │
       │               │setting_key   │  │ state          │
       │               │setting_value │  │ status         │
       │               │ description  │  │ notes          │
       │               │ created_at   │  │ created_at     │
       │               │ updated_at   │  │ updated_at     │
       │               └──────────────┘  └────────────────┘
       │
       │               ┌────────────────┐
       └──────────────►│   audit_log    │
                       │────────────────│
                       │ id (PK)        │
                       │ user_id (FK)   │──►users
                       │ action         │
                       │ entity_type    │
                       │ entity_id      │
                       │ details        │
                       │ ip_address     │
                       │ created_at     │
                       └────────────────┘

Table Definitions

1. users

Purpose: Stores user accounts for system authentication and role-based access control.

Column	Type	Constraints	Default	Description
`id`	INT	PRIMARY KEY, AUTO_INCREMENT	—	Unique user identifier
`name`	VARCHAR(255)	NOT NULL	—	Full name of the user
`email`	VARCHAR(255)	NOT NULL, UNIQUE	—	Login email address
`password_hash`	VARCHAR(255)	NOT NULL	—	bcrypt-hashed password
`role`	ENUM('admin','production','sales','accountant')	NOT NULL	—	System role for RBAC
`status`	ENUM('active','inactive')	NOT NULL	`'active'`	Account status
`created_at`	TIMESTAMP	NOT NULL	`CURRENT_TIMESTAMP`	Account creation timestamp
`updated_at`	TIMESTAMP	NOT NULL	`CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP`	Last modification timestamp

Indexes:

PRIMARY KEY (id)
UNIQUE KEY idx_users_email (email)

Foreign Keys: None

Relationships:

Referenced by requisitions.requested_by, requisitions.supervisor_id, requisitions.manager_id, requisitions.accounts_id, requisitions.md_id
Referenced by inventory_adjustments.created_by
Referenced by audit_log.user_id

2. settings

Purpose: Key-value store for application configuration and system settings.

Column	Type	Constraints	Default	Description
`id`	INT	PRIMARY KEY, AUTO_INCREMENT	—	Unique setting identifier
`setting_key`	VARCHAR(255)	NOT NULL, UNIQUE	—	Configuration key name
`setting_value`	TEXT	NOT NULL	—	Configuration value (may be JSON)
`description`	VARCHAR(500)	NULLABLE	`NULL`	Human-readable description of the setting
`created_at`	TIMESTAMP	NOT NULL	`CURRENT_TIMESTAMP`	Record creation timestamp
`updated_at`	TIMESTAMP	NOT NULL	`CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP`	Last modification timestamp

Indexes:

PRIMARY KEY (id)
UNIQUE KEY idx_settings_key (setting_key)

Foreign Keys: None

Relationships: Standalone configuration table.

3. inventory

Purpose: Master inventory of all products, raw materials, packaging, and supplies.

Column	Type	Constraints	Default	Description
`id`	INT	PRIMARY KEY, AUTO_INCREMENT	—	Unique inventory item identifier
`name`	VARCHAR(255)	NOT NULL	—	Item name
`sku`	VARCHAR(100)	NOT NULL, UNIQUE	—	Stock Keeping Unit code
`category`	ENUM('Edible Oil','Packaging','Raw Material','Equipment','Supplies')	NOT NULL	—	Item classification
`unit_of_measure`	ENUM('Litres','Kg','Cartons','Pieces','Rolls','Sachets')	NOT NULL	—	Unit for stock tracking
`current_stock`	DECIMAL(12,2)	NOT NULL	`0.00`	Current quantity on hand
`minimum_stock_level`	DECIMAL(12,2)	NOT NULL	`0.00`	Reorder threshold
`unit_cost`	DECIMAL(12,2)	NOT NULL	`0.00`	Average cost per unit
`selling_price`	DECIMAL(12,2)	NOT NULL	`0.00`	Price per unit for sales
`status`	ENUM('Active','Inactive')	NOT NULL	`'Active'`	Whether item is currently tracked
`description`	TEXT	NULLABLE	`NULL`	Detailed item description
`created_at`	TIMESTAMP	NOT NULL	`CURRENT_TIMESTAMP`	Record creation timestamp
`updated_at`	TIMESTAMP	NOT NULL	`CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP`	Last modification timestamp

Indexes:

PRIMARY KEY (id)
UNIQUE KEY idx_inventory_sku (sku)
KEY idx_inventory_category (category)
KEY idx_inventory_status (status)

Foreign Keys: None (parent table)

Relationships:

Referenced by purchases.inventory_id
Referenced by sales.inventory_id
Referenced by inventory_adjustments.inventory_id

4. purchases

Purpose: Records all inventory purchase transactions from suppliers.

Column	Type	Constraints	Default	Description
`id`	INT	PRIMARY KEY, AUTO_INCREMENT	—	Unique purchase identifier
`inventory_id`	INT	NOT NULL, FK → `inventory(id)`	—	Item purchased
`supplier_name`	VARCHAR(255)	NOT NULL	—	Name of the supplier
`quantity`	DECIMAL(12,2)	NOT NULL	—	Quantity purchased
`unit_cost`	DECIMAL(12,2)	NOT NULL	—	Cost per unit
`total_cost`	DECIMAL(14,2)	NOT NULL	—	Total = quantity × unit_cost
`purchase_date`	DATE	NOT NULL	`CURRENT_DATE`	Date of purchase
`notes`	TEXT	NULLABLE	`NULL`	Additional notes
`created_at`	TIMESTAMP	NOT NULL	`CURRENT_TIMESTAMP`	Record creation timestamp
`updated_at`	TIMESTAMP	NOT NULL	`CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP`	Last modification timestamp

Indexes:

PRIMARY KEY (id)
KEY idx_purchases_inventory_id (inventory_id)
KEY idx_purchases_date (purchase_date)

Foreign Keys:

purchases.inventory_id → inventory(id) ON DELETE RESTRICT ON UPDATE CASCADE

Relationships:

Many-to-one with inventory
Triggers inventory stock adjustment on creation

5. production_runs

Purpose: Tracks manufacturing/production batch runs including input/output volumes and costs.

Column	Type	Constraints	Default	Description
`id`	INT	PRIMARY KEY, AUTO_INCREMENT	—	Unique production run identifier
`product_name`	VARCHAR(255)	NOT NULL	—	Product being manufactured
`start_date`	DATE	NOT NULL	`CURRENT_DATE`	Production start date
`end_date`	DATE	NULLABLE	`NULL`	Production completion date
`batches`	INT	NOT NULL	`0`	Number of batches in this run
`input_volume`	DECIMAL(12,2)	NOT NULL	`0.00`	Raw material volume consumed
`output_volume`	DECIMAL(12,2)	NOT NULL	`0.00`	Finished product volume produced
`input_cost`	DECIMAL(14,2)	NOT NULL	`0.00`	Total cost of input materials
`output_cost`	DECIMAL(14,2)	NOT NULL	`0.00`	Total value of output produced
`yield_percent`	DECIMAL(5,2)	NOT NULL	`0.00`	Efficiency = (output / input) × 100
`cost_per_litre`	DECIMAL(10,2)	NOT NULL	`0.00`	Unit cost = output_cost / output_volume
`status`	ENUM('Planned','In Progress','Completed','Delayed')	NOT NULL	`'Planned'`	Current status of the run
`notes`	TEXT	NULLABLE	`NULL`	Production notes
`created_at`	TIMESTAMP	NOT NULL	`CURRENT_TIMESTAMP`	Record creation timestamp
`updated_at`	TIMESTAMP	NOT NULL	`CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP`	Last modification timestamp

Indexes:

PRIMARY KEY (id)
KEY idx_production_status (status)
KEY idx_production_dates (start_date, end_date)

Foreign Keys: None

Relationships: Standalone operational table; data feeds into dashboard analytics.

6. sales

Purpose: Records all product sales transactions to customers.

Column	Type	Constraints	Default	Description
`id`	INT	PRIMARY KEY, AUTO_INCREMENT	—	Unique sale identifier
`customer_id`	INT	NOT NULL, FK → `customers(id)`	—	Purchasing customer
`inventory_id`	INT	NOT NULL, FK → `inventory(id)`	—	Product sold
`quantity`	DECIMAL(12,2)	NOT NULL	—	Quantity sold
`unit_price`	DECIMAL(12,2)	NOT NULL	—	Price per unit at time of sale
`total_amount`	DECIMAL(14,2)	NOT NULL	—	Total = quantity × unit_price
`sale_date`	DATE	NOT NULL	`CURRENT_DATE`	Date of sale
`status`	ENUM('pending','completed','cancelled')	NOT NULL	`'pending'`	Sale lifecycle status
`notes`	TEXT	NULLABLE	`NULL`	Additional notes
`created_at`	TIMESTAMP	NOT NULL	`CURRENT_TIMESTAMP`	Record creation timestamp
`updated_at`	TIMESTAMP	NOT NULL	`CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP`	Last modification timestamp

Indexes:

PRIMARY KEY (id)
KEY idx_sales_customer_id (customer_id)
KEY idx_sales_inventory_id (inventory_id)
KEY idx_sales_date (sale_date)
KEY idx_sales_status (status)

Foreign Keys:

sales.customer_id → customers(id) ON DELETE RESTRICT ON UPDATE CASCADE
sales.inventory_id → inventory(id) ON DELETE RESTRICT ON UPDATE CASCADE

Relationships:

Many-to-one with customers
Many-to-one with inventory
One-to-many with ar_invoices

7. expenses

Purpose: Records all business expenses with approval workflow.

Column	Type	Constraints	Default	Description
`id`	INT	PRIMARY KEY, AUTO_INCREMENT	—	Unique expense identifier
`category`	VARCHAR(100)	NOT NULL	—	Expense category (e.g., Utilities, Transport)
`description`	TEXT	NOT NULL	—	Detailed description of the expense
`amount`	DECIMAL(14,2)	NOT NULL	—	Expense amount
`payment_method`	VARCHAR(50)	NOT NULL	—	Payment method (Cash, Transfer, Cheque, Card)
`status`	ENUM('pending','approved','rejected')	NOT NULL	`'pending'`	Approval status
`vendor`	VARCHAR(255)	NULLABLE	`NULL`	Vendor/payee name
`reference_number`	VARCHAR(100)	NULLABLE	`NULL`	Transaction reference
`notes`	TEXT	NULLABLE	`NULL`	Additional notes
`expense_date`	DATE	NOT NULL	`CURRENT_DATE`	Date the expense was incurred
`created_at`	TIMESTAMP	NOT NULL	`CURRENT_TIMESTAMP`	Record creation timestamp
`updated_at`	TIMESTAMP	NOT NULL	`CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP`	Last modification timestamp

Indexes:

PRIMARY KEY (id)
KEY idx_expenses_status (status)
KEY idx_expenses_date (expense_date)
KEY idx_expenses_category (category)

Foreign Keys: None

Relationships: Feeds into accounting reports and dashboard analytics.

8. inventory_adjustments

Purpose: Logs manual adjustments to inventory stock levels with audit trail.

Column	Type	Constraints	Default	Description
`id`	INT	PRIMARY KEY, AUTO_INCREMENT	—	Unique adjustment identifier
`inventory_id`	INT	NOT NULL, FK → `inventory(id)`	—	Item being adjusted
`adjustment_quantity`	DECIMAL(12,2)	NOT NULL	—	Change quantity (positive = add, negative = subtract)
`reason`	VARCHAR(500)	NOT NULL	—	Reason for the adjustment
`notes`	TEXT	NULLABLE	`NULL`	Detailed notes
`adjustment_date`	DATE	NOT NULL	`CURRENT_DATE`	Date of adjustment
`created_by`	INT	NOT NULL, FK → `users(id)`	—	User who made the adjustment
`created_at`	TIMESTAMP	NOT NULL	`CURRENT_TIMESTAMP`	Record creation timestamp
`updated_at`	TIMESTAMP	NOT NULL	`CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP`	Last modification timestamp

Indexes:

PRIMARY KEY (id)
KEY idx_adj_inventory_id (inventory_id)
KEY idx_adj_created_by (created_by)
KEY idx_adj_date (adjustment_date)

Foreign Keys:

inventory_adjustments.inventory_id → inventory(id) ON DELETE RESTRICT ON UPDATE CASCADE
inventory_adjustments.created_by → users(id) ON DELETE RESTRICT ON UPDATE CASCADE

Relationships:

Many-to-one with inventory
Many-to-one with users (audit trail)

9. ar_invoices

Purpose: Accounts Receivable invoices issued to customers for sales on credit.

Column	Type	Constraints	Default	Description
`id`	INT	PRIMARY KEY, AUTO_INCREMENT	—	Unique invoice identifier
`sale_id`	INT	NOT NULL, FK → `sales(id)`	—	Related sale transaction
`invoice_number`	VARCHAR(50)	NOT NULL	—	Human-readable invoice number
`amount`	DECIMAL(14,2)	NOT NULL	—	Invoice amount
`status`	ENUM('pending','paid','overdue','partial')	NOT NULL	`'pending'`	Payment status
`due_date`	DATE	NOT NULL	—	Payment due date
`notes`	TEXT	NULLABLE	`NULL`	Additional notes
`created_at`	TIMESTAMP	NOT NULL	`CURRENT_TIMESTAMP`	Record creation timestamp
`updated_at`	TIMESTAMP	NOT NULL	`CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP`	Last modification timestamp

Indexes:

PRIMARY KEY (id)
UNIQUE KEY idx_ar_invoice_number (invoice_number)
KEY idx_ar_invoice_sale_id (sale_id)
KEY idx_ar_invoice_status (status)
KEY idx_ar_invoice_due_date (due_date)

Foreign Keys:

ar_invoices.sale_id → sales(id) ON DELETE RESTRICT ON UPDATE CASCADE

Relationships:

Many-to-one with sales
One-to-many with ar_payments

10. ar_payments

Purpose: Records payments received against Accounts Receivable invoices.

Column	Type	Constraints	Default	Description
`id`	INT	PRIMARY KEY, AUTO_INCREMENT	—	Unique payment identifier
`invoice_id`	INT	NOT NULL, FK → `ar_invoices(id)`	—	Related invoice
`amount`	DECIMAL(14,2)	NOT NULL	—	Payment amount
`payment_method`	VARCHAR(50)	NOT NULL	—	Method used (Cash, Transfer, Cheque, Card)
`reference_number`	VARCHAR(100)	NULLABLE	`NULL`	Transaction reference
`payment_date`	DATE	NOT NULL	`CURRENT_DATE`	Date payment was received
`notes`	TEXT	NULLABLE	`NULL`	Additional notes
`created_at`	TIMESTAMP	NOT NULL	`CURRENT_TIMESTAMP`	Record creation timestamp
`updated_at`	TIMESTAMP	NOT NULL	`CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP`	Last modification timestamp

Indexes:

PRIMARY KEY (id)
KEY idx_ar_payment_invoice_id (invoice_id)
KEY idx_ar_payment_date (payment_date)

Foreign Keys:

ar_payments.invoice_id → ar_invoices(id) ON DELETE RESTRICT ON UPDATE CASCADE

Relationships:

Many-to-one with ar_invoices
Updates invoice status (paid/partial/overdue)

11. requisitions

Purpose: Multi-level approval workflow for purchase requisitions and expenditure requests.

Column	Type	Constraints	Default	Description
`id`	INT	PRIMARY KEY, AUTO_INCREMENT	—	Unique requisition identifier
`title`	VARCHAR(255)	NOT NULL	—	Short title of the request
`description`	TEXT	NULLABLE	`NULL`	Detailed description
`requested_by`	INT	NOT NULL, FK → `users(id)`	—	User who initiated the request
`status`	ENUM('pending','supervisor_approved','manager_approved','accounts_approved','md_approved','rejected')	NOT NULL	`'pending'`	Current workflow status
`amount`	DECIMAL(14,2)	NULLABLE	`NULL`	Estimated/requested amount
`category`	VARCHAR(100)	NULLABLE	`NULL`	Expense category
`priority`	ENUM('low','medium','high','urgent')	NOT NULL	`'medium'`	Request priority level
`supervisor_id`	INT	NULLABLE, FK → `users(id)`	`NULL`	Assigned supervisor reviewer
`supervisor_notes`	TEXT	NULLABLE	`NULL`	Supervisor's comments
`supervisor_approved_at`	TIMESTAMP	NULLABLE	`NULL`	Supervisor approval timestamp
`manager_id`	INT	NULLABLE, FK → `users(id)`	`NULL`	Assigned manager reviewer
`manager_notes`	TEXT	NULLABLE	`NULL`	Manager's comments
`manager_approved_at`	TIMESTAMP	NULLABLE	`NULL`	Manager approval timestamp
`accounts_id`	INT	NULLABLE, FK → `users(id)`	`NULL`	Assigned accounts reviewer
`accounts_notes`	TEXT	NULLABLE	`NULL`	Accounts department comments
`accounts_approved_at`	TIMESTAMP	NULLABLE	`NULL`	Accounts approval timestamp
`md_id`	INT	NULLABLE, FK → `users(id)`	`NULL`	Managing Director reviewer
`md_notes`	TEXT	NULLABLE	`NULL`	MD's comments
`md_approved_at`	TIMESTAMP	NULLABLE	`NULL`	MD approval timestamp
`created_at`	TIMESTAMP	NOT NULL	`CURRENT_TIMESTAMP`	Record creation timestamp
`updated_at`	TIMESTAMP	NOT NULL	`CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP`	Last modification timestamp

Indexes:

PRIMARY KEY (id)
KEY idx_req_status (status)
KEY idx_req_requested_by (requested_by)
KEY idx_req_priority (priority)
KEY idx_req_created_at (created_at)

Foreign Keys:

requisitions.requested_by → users(id) ON DELETE RESTRICT ON UPDATE CASCADE
requisitions.supervisor_id → users(id) ON DELETE SET NULL ON UPDATE CASCADE
requisitions.manager_id → users(id) ON DELETE SET NULL ON UPDATE CASCADE
requisitions.accounts_id → users(id) ON DELETE SET NULL ON UPDATE CASCADE
requisitions.md_id → users(id) ON DELETE SET NULL ON UPDATE CASCADE

Relationships:

Many-to-one with users (requested_by — mandatory)
Many-to-one with users (supervisor, manager, accounts, md — optional at each level)

Approval Workflow:

Requester creates → pending
Supervisor approves → supervisor_approved
Manager approves → manager_approved
Accounts approves → accounts_approved
MD approves → md_approved (final)
Any reviewer rejects → rejected

12. customers

Purpose: Master list of customers with credit management.

Column	Type	Constraints	Default	Description
`id`	INT	PRIMARY KEY, AUTO_INCREMENT	—	Unique customer identifier
`company_name`	VARCHAR(255)	NOT NULL	—	Company or customer name
`contact_person`	VARCHAR(255)	NULLABLE	`NULL`	Primary contact name
`phone`	VARCHAR(50)	NULLABLE	`NULL`	Phone number
`email`	VARCHAR(255)	NULLABLE	`NULL`	Email address
`address`	TEXT	NULLABLE	`NULL`	Physical address
`city`	VARCHAR(100)	NULLABLE	`NULL`	City
`state`	VARCHAR(100)	NULLABLE	`NULL`	State/region
`credit_limit`	DECIMAL(14,2)	NOT NULL	`0.00`	Maximum credit allowed
`outstanding_balance`	DECIMAL(14,2)	NOT NULL	`0.00`	Current unpaid balance
`status`	ENUM('Active','Inactive')	NOT NULL	`'Active'`	Account status
`notes`	TEXT	NULLABLE	`NULL`	Additional notes
`created_at`	TIMESTAMP	NOT NULL	`CURRENT_TIMESTAMP`	Record creation timestamp
`updated_at`	TIMESTAMP	NOT NULL	`CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP`	Last modification timestamp

Indexes:

PRIMARY KEY (id)
KEY idx_customers_status (status)
KEY idx_customers_company (company_name)

Foreign Keys: None (parent table)

Relationships:

Referenced by sales.customer_id
Referenced by ar_invoices (indirectly through sales)

13. suppliers

Purpose: Master list of suppliers and vendors.

Column	Type	Constraints	Default	Description
`id`	INT	PRIMARY KEY, AUTO_INCREMENT	—	Unique supplier identifier
`company_name`	VARCHAR(255)	NOT NULL	—	Company name
`contact_person`	VARCHAR(255)	NULLABLE	`NULL`	Primary contact name
`phone`	VARCHAR(50)	NULLABLE	`NULL`	Phone number
`email`	VARCHAR(255)	NULLABLE	`NULL`	Email address
`address`	TEXT	NULLABLE	`NULL`	Physical address
`city`	VARCHAR(100)	NULLABLE	`NULL`	City
`state`	VARCHAR(100)	NULLABLE	`NULL`	State/region
`status`	ENUM('Active','Inactive')	NOT NULL	`'Active'`	Account status
`notes`	TEXT	NULLABLE	`NULL`	Additional notes
`created_at`	TIMESTAMP	NOT NULL	`CURRENT_TIMESTAMP`	Record creation timestamp
`updated_at`	TIMESTAMP	NOT NULL	`CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP`	Last modification timestamp

Indexes:

PRIMARY KEY (id)
KEY idx_suppliers_status (status)
KEY idx_suppliers_company (company_name)

Foreign Keys: None (parent table)

Relationships: Referenced by name in purchases.supplier_name (no FK — denormalized for simplicity).

14. audit_log

Purpose: Immutable audit trail of all significant system actions for compliance and debugging.

Column	Type	Constraints	Default	Description
`id`	INT	PRIMARY KEY, AUTO_INCREMENT	—	Unique log entry identifier
`user_id`	INT	NOT NULL, FK → `users(id)`	—	User who performed the action
`action`	ENUM('CREATE','UPDATE','DELETE','LOGIN','APPROVE','REJECT')	NOT NULL	—	Type of action performed
`entity_type`	VARCHAR(50)	NOT NULL	—	Entity affected (e.g., `sale`, `invoice`)
`entity_id`	INT	NULLABLE	`NULL`	ID of the affected record
`details`	TEXT	NULLABLE	`NULL`	JSON or text description of the change
`ip_address`	VARCHAR(45)	NULLABLE	`NULL`	Client IP address (supports IPv6)
`created_at`	TIMESTAMP	NOT NULL	`CURRENT_TIMESTAMP`	When the action occurred

Indexes:

PRIMARY KEY (id)
KEY idx_audit_user_id (user_id)
KEY idx_audit_entity (entity_type, entity_id)
KEY idx_audit_action (action)
KEY idx_audit_created_at (created_at)

Foreign Keys:

audit_log.user_id → users(id) ON DELETE RESTRICT ON UPDATE CASCADE

Relationships:

Many-to-one with users
References any entity by entity_type + entity_id (polymorphic association — no FK)

Summary of Foreign Key Relationships

Source Table	Source Column	Target Table	Target Column	On Delete	On Update
purchases	inventory_id	inventory	id	RESTRICT	CASCADE
sales	customer_id	customers	id	RESTRICT	CASCADE
sales	inventory_id	inventory	id	RESTRICT	CASCADE
inventory_adjustments	inventory_id	inventory	id	RESTRICT	CASCADE
inventory_adjustments	created_by	users	id	RESTRICT	CASCADE
ar_invoices	sale_id	sales	id	RESTRICT	CASCADE
ar_payments	invoice_id	ar_invoices	id	RESTRICT	CASCADE
requisitions	requested_by	users	id	RESTRICT	CASCADE
requisitions	supervisor_id	users	id	SET NULL	CASCADE
requisitions	manager_id	users	id	SET NULL	CASCADE
requisitions	accounts_id	users	id	SET NULL	CASCADE
requisitions	md_id	users	id	SET NULL	CASCADE
audit_log	user_id	users	id	RESTRICT	CASCADE

Database Overview​

Connection Configuration​

Entity Relationship Diagram​

Table Definitions​

1. users​

2. settings​

3. inventory​

4. purchases​

5. production_runs​

6. sales​

7. expenses​

8. inventory_adjustments​

9. ar_invoices​

10. ar_payments​

11. requisitions​

12. customers​

13. suppliers​

14. audit_log​

Summary of Foreign Key Relationships​

Database Overview

Connection Configuration

Entity Relationship Diagram

Table Definitions

1. users

2. settings

3. inventory

4. purchases

5. production_runs

6. sales

7. expenses

8. inventory_adjustments

9. ar_invoices

10. ar_payments

11. requisitions

12. customers

13. suppliers

14. audit_log

Summary of Foreign Key Relationships