---
tags: [purchases, database]
module: Purchases
---

# [DB]DatabaseTables

## Connections
[[_Purchases]] · [[[Model]Supplier]] · [[[Model]PurchaseBill]] · [[[Model]BillReturn]] · [[[Model]PurchaseOrder]] · [[[Model]Expense]] · [[[Model]SupplierPaymentReceipt]]

## Tables (tenant connection)

### suppliers
| Column | Type | Notes |
|---|---|---|
| id | bigIncrements | PK |
| code | unsignedMediumInteger | |
| name | string | |
| email | string | nullable |
| country_code | string | nullable |
| mobile | string | nullable |
| tax_number | string | nullable |
| street / country / city / postal_code | string | nullable |
| current_balance | decimal 11,2 | |
| opening_balance | decimal 11,2 | |
| status | boolean | default true |
| created_by / updated_by | FK → users | nullable |
| custom_fields | json | nullable |
| integration_type | string | nullable |
| integration_reference | string | nullable |
| deleted_at | timestamp | soft delete |

### account_supplier (pivot)
`account_id` FK → accounts · `supplier_id` FK → suppliers

### purchase_orders
| Column | Type | Notes |
|---|---|---|
| id | bigIncrements | PK |
| code | string | auto-generated |
| issue_date / due_date | date | |
| status | unsignedTinyInteger | PurchaseOrderStatusEnum |
| total_vat / total_amount / total | decimal 11,2 | |
| total_discount | decimal 11,2 | nullable |
| taxes_status | unsignedTinyInteger | BillTaxesStatusEnum |
| currency_code / exchange_rate | string | |
| notes / terms | longText | nullable |
| supplier_id | FK → suppliers | |
| form_design_id | FK → form_designs | |
| custom_fields | json | nullable |
| created_by / updated_by | FK → users | nullable |
| deleted_at | timestamp | soft delete |

### purchase_order_details
`purchase_order_id` · `product_id` · `warehouse_product_id` (nullable) · `quantity` · `unit_price` · `total` · `discount` · `gross` · `vat` · `tax_id`

### purchase_order_attachments
`purchase_order_id` · `url` · `name`

### purchase_bills
| Column | Type | Notes |
|---|---|---|
| id | bigIncrements | PK |
| reference | string | auto-generated |
| date | timestamp | |
| due_date | timestamp | nullable |
| payment_term_id | FK → payment_terms | |
| paid_after | decimal 11,2 | |
| total_vat / total_amount / total | decimal 11,2 | |
| paid_amount | decimal 11,2 | default 0 |
| is_draft | boolean | default 0 |
| journal_entry_id | FK → journal_entries | |
| fiscal_year_id | FK → fiscal_year | |
| supplier_id | FK → suppliers | |
| payment_account_id | FK → accounts | |
| supplier_balance_after_transaction | decimal 11,2 | |
| cost_center_id | FK → cost_centers | nullable |
| taxes_status | unsignedTinyInteger | |
| supplier_invoice_reference_number | string | nullable |
| purchase_order_id | FK → purchase_orders | nullable |
| currency_code / exchange_rate | string | |
| created_by / updated_by / deleted_by | FK → users | |
| deleted_at | timestamp | soft delete |

### purchase_bill_details
`purchase_bill_id` · `product_id` · `warehouse_product_id` (nullable) · `quantity` · `price` · `discount` · `gross` · `tax_id` · `vat_value`

### purchase_bill_attachments
`purchase_bill_id` · `url` · `name`

### bill_returns
| Column | Type | Notes |
|---|---|---|
| id | bigIncrements | PK |
| reference | string | |
| date | timestamp | |
| payment_term_id | FK → payment_terms | |
| total_vat / total_amount | decimal 11,2 | |
| paid_amount | decimal 11,2 | default 0 |
| outstanding_amount | decimal 11,2 | |
| journal_entry_id | FK → journal_entries | |
| fiscal_year_id | FK → fiscal_year | |
| supplier_id | FK → suppliers | |
| payment_account_id | FK → accounts | |
| cost_center_id | FK → cost_centers | nullable |
| purchase_bill_id | FK → purchase_bills | nullable |
| supplier_invoice_reference_number | string | nullable |
| created_by / updated_by / deleted_by | FK → users | |
| deleted_at | timestamp | soft delete |

### bill_return_details
`bill_return_id` · `product_id` · `warehouse_product_id` (nullable) · `quantity` · `price` · `discount` · `gross` · `tax_id` · `vat_value`

### bill_return_attachments
`bill_return_id` · `url` · `name`

### supplier_payment_receipts
| Column | Type | Notes |
|---|---|---|
| id | bigIncrements | PK |
| reference | string | |
| date | timestamp | |
| kind | enum: paid/received | PaymentReceiptTypesEnum |
| paid_amount | decimal 11,2 | default 0 |
| not_allocated_amount | decimal 11,2 | default 0 |
| journal_entry_id | FK → journal_entries | |
| fiscal_year_id | FK → fiscal_year | |
| supplier_id | FK → suppliers | |
| payment_account_id | FK → accounts | |
| supplier_balance_after_transaction | decimal 11,2 | |
| cost_center_id | FK → cost_centers | nullable |
| form_design_id | FK → form_designs | |
| supplier_invoice_reference_number | string | nullable |
| created_by / updated_by / deleted_by | FK → users | |
| deleted_at | timestamp | soft delete |

### supplier_payment_receipt_attachments
`payment_receipt_id` · `url` · `name`

### bills_payment_receipts (pivot)
`receipt_id` FK → supplier_payment_receipts · `bill_id` FK → purchase_bills · `allocated_amount`  
SoftDeletes · `created_by / updated_by / deleted_by`

### bills_returns_payment_receipts (pivot)
`receipt_id` FK → supplier_payment_receipts · `bill_return_id` FK → bill_returns · `allocated_amount`  
SoftDeletes · `created_by / updated_by / deleted_by`

### expenses
| Column | Type | Notes |
|---|---|---|
| id | bigIncrements | PK |
| reference | string | |
| date | date | nullable |
| total_vat / total_amount / total | decimal 11,2 | |
| journal_entry_id | FK → journal_entries | nullable |
| supplier_customer_journal_entry_id | FK → journal_entries | nullable (counterparty JE) |
| fiscal_year_id | FK → fiscal_year | |
| supplier_id | FK → suppliers | nullable |
| customer_id | FK → customers | nullable |
| payment_account_id | FK → accounts | nullable |
| cost_center_id | FK → cost_centers | nullable |
| taxes_status | unsignedTinyInteger | default 0 |
| created_by / updated_by / deleted_by | FK → users | |
| deleted_at | timestamp | soft delete |

### expense_details
`expense_id` · `product_id` · `quantity` · `price` · `discount` · `gross` · `tax_id` · `vat_value` · `total`

### expense_attachments
`expense_id` · `url` · `name`