Database Schema (pos_core)

Table: `app_settings`

Field	Type	Null	Key	Default	Extra
key	varchar(64)	NO	PRI
value	varchar(255)	NO

Table: `ar_ledger`

Field	Type	Null	Key	Default	Extra
id	bigint(20) unsigned	NO	PRI		auto_increment
customer_id	bigint(20) unsigned	NO	MUL
entry_date	date	NO
source_type	varchar(16)	NO	MUL
source_id	bigint(20) unsigned	YES
amount	decimal(12,2)	NO
note	varchar(255)	YES
created_at	timestamp	NO		current_timestamp()

Table: `customer_balances`

Field	Type	Null	Key	Default	Extra
customer_id	bigint(20) unsigned	NO	PRI
balance	decimal(12,2)	NO		0.00
updated_at	timestamp	NO		current_timestamp()	on update current_timestamp()

Table: `customers`

Field	Type	Null	Key	Default	Extra
id	bigint(20) unsigned	NO	PRI		auto_increment
display_name	varchar(160)	NO	MUL
contact_name	varchar(120)	YES
phone	varchar(40)	YES
email	varchar(160)	YES
billing_line1	varchar(160)	YES
billing_line2	varchar(160)	YES
billing_city	varchar(120)	YES
billing_state	varchar(32)	YES
billing_zip	varchar(32)	YES
shipping_line1	varchar(160)	YES
shipping_line2	varchar(160)	YES
shipping_city	varchar(120)	YES
shipping_state	varchar(32)	YES
shipping_zip	varchar(32)	YES
credit_limit	decimal(12,2)	NO		0.00
terms	varchar(32)	NO		NET30
is_active	tinyint(1)	NO		1
created_at	timestamp	NO		current_timestamp()
updated_at	timestamp	NO		current_timestamp()	on update current_timestamp()
tax_exempt	tinyint(1)	NO		0

Table: `inventory`

Field	Type	Null	Key	Default
product_id	bigint(20) unsigned	NO	PRI
warehouse_id	bigint(20) unsigned	NO	PRI
on_hand	decimal(16,4)	NO		0.0000

Table: `invoice_items`

Field	Type	Null	Key	Default	Extra
id	bigint(20) unsigned	NO	PRI		auto_increment
invoice_id	bigint(20) unsigned	NO	MUL
line_no	int(10) unsigned	NO
product_id	bigint(20) unsigned	NO	MUL
description	varchar(255)	YES
qty	decimal(16,4)	NO
unit	varchar(32)	NO		EA
unit_price	decimal(12,4)	NO
line_total	decimal(12,2)	NO
tax_rate	decimal(6,4)	NO		0.0000
tax_amount	decimal(12,2)	NO		0.00

Table: `invoice_payments`

Field	Type	Null	Key	Default	Extra
id	bigint(20) unsigned	NO	PRI		auto_increment
invoice_id	bigint(20) unsigned	NO	MUL
payment_id	bigint(20) unsigned	NO	MUL
amount	decimal(12,2)	NO
created_at	timestamp	NO		current_timestamp()

Table: `invoices`

Field	Type	Null	Key	Default	Extra
id	bigint(20) unsigned	NO	PRI		auto_increment
invoice_no	varchar(40)	NO	UNI
customer_id	bigint(20) unsigned	NO	MUL
invoice_date	date	NO	MUL
due_date	date	YES
status	varchar(20)	NO		open
warehouse_id	bigint(20) unsigned	YES	MUL
subtotal	decimal(12,2)	NO		0.00
tax_total	decimal(12,2)	NO		0.00
shipping_total	decimal(12,2)	NO		0.00
grand_total	decimal(12,2)	NO		0.00
notes	text	YES
created_by	bigint(20) unsigned	YES	MUL
created_at	timestamp	NO		current_timestamp()
updated_at	timestamp	NO		current_timestamp()	on update current_timestamp()
type	varchar(12)	NO	MUL	sale
invoice_token	varchar(40)	YES	UNI

Table: `payments`

Field	Type	Null	Key	Default	Extra
id	bigint(20) unsigned	NO	PRI		auto_increment
customer_id	bigint(20) unsigned	NO	MUL
payment_date	date	NO	MUL
amount	decimal(12,2)	NO
method	varchar(20)	NO		cash
ref_no	varchar(80)	YES
memo	varchar(255)	YES
created_at	timestamp	NO		current_timestamp()

Table: `product_tax_map`

Field	Type	Null	Key	Default	Extra
product_id	bigint(20) unsigned	NO	PRI
tax_id	bigint(20) unsigned	NO	PRI

Table: `products`

Field	Type	Null	Key	Default	Extra
id	bigint(20) unsigned	NO	PRI		auto_increment
sku	varchar(64)	YES	MUL
upc	varchar(32)	YES	UNI
name	varchar(200)	NO	MUL
description	text	YES
category	varchar(120)	YES
unit	varchar(32)	NO		EA
units_per_case	int(10) unsigned	YES
cost_price	decimal(12,4)	NO		0.0000
sell_price	decimal(12,4)	NO		0.0000
tax_code	varchar(32)	YES
vendor_id	bigint(20) unsigned	YES	MUL
is_active	tinyint(1)	NO		1
created_at	timestamp	NO		current_timestamp()
updated_at	timestamp	NO		current_timestamp()	on update current_timestamp()

Table: `purchase_items`

Field	Type	Null	Key	Default	Extra
id	bigint(20) unsigned	NO	PRI		auto_increment
po_id	bigint(20) unsigned	NO	MUL
line_no	int(10) unsigned	NO
product_id	bigint(20) unsigned	NO	MUL
description	varchar(255)	YES
qty_ordered	decimal(16,4)	NO
qty_received	decimal(16,4)	NO		0.0000
unit	varchar(32)	NO		EA
unit_cost	decimal(12,4)	NO
line_total	decimal(12,2)	NO
tax_rate	decimal(6,4)	NO		0.0000
tax_amount	decimal(12,2)	NO		0.00

Table: `purchase_orders`

Field	Type	Null	Key	Default	Extra
id	bigint(20) unsigned	NO	PRI		auto_increment
po_no	varchar(40)	NO	UNI
vendor_id	bigint(20) unsigned	NO	MUL
po_date	date	NO	MUL
status	varchar(20)	NO		open
subtotal	decimal(12,2)	NO		0.00
tax_total	decimal(12,2)	NO		0.00
shipping_total	decimal(12,2)	NO		0.00
grand_total	decimal(12,2)	NO		0.00
notes	text	YES
created_at	timestamp	NO		current_timestamp()
updated_at	timestamp	NO		current_timestamp()	on update current_timestamp()

Table: `receipt_items`

Field	Type	Null	Key	Extra
id	bigint(20) unsigned	NO	PRI	auto_increment
receipt_id	bigint(20) unsigned	NO	MUL
product_id	bigint(20) unsigned	NO	MUL
warehouse_id	bigint(20) unsigned	NO	MUL
qty_received	decimal(16,4)	NO
unit_cost	decimal(12,4)	NO
line_total	decimal(12,2)	NO

Table: `receipts`

Field	Type	Null	Key	Default	Extra
id	bigint(20) unsigned	NO	PRI		auto_increment
po_id	bigint(20) unsigned	NO	MUL
receipt_no	varchar(40)	NO	UNI
receipt_date	date	NO
vendor_invoice_no	varchar(80)	YES
notes	text	YES
created_at	timestamp	NO		current_timestamp()

Table: `schema_version`

Field	Type	Null	Key	Default
id	int(11)	NO	PRI
label	varchar(64)	NO
applied_at	timestamp	NO		current_timestamp()

Table: `sequences`

Field	Type	Null	Key	Default	Extra
name	varchar(40)	NO	PRI
prefix	varchar(12)	NO
next_val	int(10) unsigned	NO
padding	tinyint(3) unsigned	NO		5
updated_at	timestamp	NO		current_timestamp()	on update current_timestamp()

Table: `tax_rates`

Field	Type	Null	Key	Default	Extra
id	bigint(20) unsigned	NO	PRI		auto_increment
code	varchar(40)	NO	UNI
name	varchar(100)	NO	MUL
rate	decimal(5,2)	NO
tax_type	enum('sales','excise','luxury','other')	NO		sales
is_compound	tinyint(1)	NO		0
effective_from	date	NO		curdate()
effective_to	date	YES
is_active	tinyint(1)	NO		1
rate_percent	decimal(6,4)	NO		0.0000
created_at	timestamp	NO		current_timestamp()
updated_at	timestamp	NO		current_timestamp()	on update current_timestamp()

Table: `users`

Field	Type	Null	Key	Default	Extra
id	bigint(20) unsigned	NO	PRI		auto_increment
username	varchar(64)	NO	UNI
password_hash	varchar(255)	NO
full_name	varchar(128)	NO
role	varchar(32)	NO		clerk
is_active	tinyint(1)	NO		1
created_at	timestamp	NO		current_timestamp()
updated_at	timestamp	NO		current_timestamp()	on update current_timestamp()

Table: `v_customer_balances`

Field	Type	Null	Default
customer_id	bigint(20) unsigned	YES	0
display_name	varchar(160)	YES
balance	decimal(34,2)	YES

Table: `v_customer_balances_detailed`

Field	Type	Null
customer_id	bigint(20) unsigned	YES
display_name	varchar(160)	NO
invoice_total	decimal(34,2)	YES
payment_total	decimal(34,2)	YES
balance	decimal(35,2)	YES

Table: `v_customer_statement`

Field	Type	Null	Default
customer_id	bigint(20) unsigned	YES	0
display_name	varchar(160)	YES
entry_date	date	YES	0000-00-00
source_type	varchar(7)	YES
source_id	bigint(20) unsigned	YES	0
amount	decimal(12,2)	YES	0.00
running_balance	decimal(34,2)	YES

Table: `v_invoice_balance_due`

Field	Type	Null	Default
invoice_id	bigint(20) unsigned	NO	0
customer_id	bigint(20) unsigned	NO
invoice_no	varchar(40)	NO
grand_total	decimal(12,2)	NO	0.00
payments_applied	decimal(34,2)	YES
balance_due	decimal(35,2)	YES

Table: `v_invoice_header_print`

Field	Type	Null	Default
invoice_id	bigint(20) unsigned	NO	0
invoice_no	varchar(40)	NO
invoice_date	date	NO
status	varchar(20)	NO	open
type	varchar(12)	NO	sale
customer_id	bigint(20) unsigned	NO
customer_name	varchar(160)	YES
phone	varchar(40)	YES
email	varchar(160)	YES
warehouse_id	bigint(20) unsigned	YES
subtotal	decimal(12,2)	NO	0.00
tax_total	decimal(12,2)	NO	0.00
shipping_total	decimal(12,2)	NO	0.00
grand_total	decimal(12,2)	NO	0.00

Table: `v_invoice_payment_links`

Field	Type	Null	Default
invoice_id	bigint(20) unsigned	NO	0
invoice_no	varchar(40)	NO
invoice_date	date	NO
customer_id	bigint(20) unsigned	NO
customer_name	varchar(160)	NO
grand_total	decimal(12,2)	NO	0.00
payments_applied	decimal(34,2)	YES
balance_due	decimal(35,2)	YES
payment_id	bigint(20) unsigned	YES	0
payment_date	date	YES
payment_amount	decimal(12,2)	YES
payment_remaining	decimal(35,2)	YES
amount_applied_on_invoice	decimal(12,2)	YES

Table: `v_invoice_payment_summary`

Field	Type	Null	Key	Default	Extra
invoice_id	bigint(20) unsigned	NO
payments_applied	decimal(34,2)	YES

Table: `v_invoice_totals_calc`

Field	Type	Null	Default
invoice_id	bigint(20) unsigned	NO	0
subtotal_calc	decimal(45,2)	YES
tax_total_calc	decimal(38,4)	YES
grand_total_calc	decimal(38,4)	YES

Table: `v_invoice_totals_compare`

Field	Type	Null	Default
invoice_id	bigint(20) unsigned	NO	0
invoice_no	varchar(40)	NO
status	varchar(20)	NO	open
invoice_date	date	NO
rounding_mode	varchar(255)	YES
subtotal_stored	decimal(12,2)	NO	0.00
tax_total_stored	decimal(12,2)	NO	0.00
grand_total_stored	decimal(12,2)	NO	0.00
subtotal_calc	decimal(45,2)	YES
tax_total_calc	decimal(38,4)	YES
grand_total_calc	decimal(38,4)	YES
subtotal_delta	decimal(46,2)	YES
tax_total_delta	decimal(38,2)	YES
grand_total_delta	decimal(38,2)	YES

Table: `v_invoice_totals_variances`

Field	Type	Null	Default
invoice_id	bigint(20) unsigned	NO	0
invoice_no	varchar(40)	NO
status	varchar(20)	NO	open
invoice_date	date	NO
rounding_mode	varchar(255)	YES
subtotal_stored	decimal(12,2)	NO	0.00
tax_total_stored	decimal(12,2)	NO	0.00
grand_total_stored	decimal(12,2)	NO	0.00
subtotal_calc	decimal(45,2)	YES
tax_total_calc	decimal(38,4)	YES
grand_total_calc	decimal(38,4)	YES
subtotal_delta	decimal(46,2)	YES
tax_total_delta	decimal(38,2)	YES
grand_total_delta	decimal(38,2)	YES

Table: `v_open_invoices`

Field	Type	Null	Default
invoice_id	bigint(20) unsigned	NO	0
invoice_no	varchar(40)	NO
invoice_date	date	NO
customer_id	bigint(20) unsigned	NO
customer_name	varchar(160)	NO
grand_total	decimal(12,2)	NO	0.00
payments_applied	decimal(34,2)	YES
balance_due	decimal(35,2)	YES

Table: `v_payment_applied_summary`

Field	Type	Null	Key	Default	Extra
payment_id	bigint(20) unsigned	NO
amount_applied	decimal(34,2)	YES

Table: `v_payment_remaining`

Field	Type	Null	Default
payment_id	bigint(20) unsigned	NO	0
customer_id	bigint(20) unsigned	NO
payment_date	date	NO
payment_amount	decimal(12,2)	NO
amount_applied	decimal(34,2)	YES
amount_remaining	decimal(35,2)	YES
method	varchar(20)	NO	cash
ref_no	varchar(80)	YES
memo	varchar(255)	YES

Table: `v_payment_to_open_invoices`

Field	Type	Null	Default
payment_id	bigint(20) unsigned	NO	0
payment_date	date	NO
customer_id	bigint(20) unsigned	NO
customer_name	varchar(160)	NO
payment_amount	decimal(12,2)	NO
payment_remaining	decimal(35,2)	YES
invoice_id	bigint(20) unsigned	NO	0
invoice_no	varchar(40)	NO
grand_total	decimal(12,2)	NO	0.00
balance_due	decimal(35,2)	YES
amount_applied_by_payment	decimal(34,2)	YES
max_applicable	decimal(35,2)	YES

Table: `v_product_final_price`

Field	Type	Null	Default
product_id	bigint(20) unsigned	NO	0
product_name	varchar(200)	NO
base_price	decimal(12,4)	NO	0.0000
total_rate_percent	decimal(27,2)	YES
tax_amount	decimal(36,2)	YES
final_price	decimal(37,2)	YES

Table: `v_product_final_price_by_customer`

Field	Type	Null	Default
customer_id	bigint(20) unsigned	NO	0
display_name	varchar(160)	NO
product_id	bigint(20) unsigned	NO	0
product_name	varchar(200)	NO
base_price	decimal(12,4)	NO	0.0000
total_rate_percent	decimal(27,2)	YES
tax_amount	decimal(36,2)	YES
final_price	decimal(37,2)	YES

Table: `v_product_final_price_exempt`

Field	Type	Null	Default
product_id	bigint(20) unsigned	NO	0
product_name	varchar(200)	NO
base_price	decimal(12,4)	NO	0.0000
current_total_tax_percent	decimal(27,2)	YES
tax_amount	decimal(3,2)	NO	0.00
final_price	decimal(12,4)	NO	0.0000

Table: `v_product_price_detail`

Field	Type	Null	Default
product_id	bigint(20) unsigned	NO	0
product_name	varchar(200)	NO
base_price	decimal(12,4)	NO	0.0000
tax_id	bigint(20) unsigned	NO	0
tax_name	varchar(100)	NO
rate	decimal(5,2)	NO
tax_amount_component	decimal(14,2)	YES

Table: `v_product_tax_breakdown`

Field	Type	Null	Default
product_id	bigint(20) unsigned	NO	0
product_name	varchar(200)	NO
tax_id	bigint(20) unsigned	NO	0
tax_name	varchar(100)	NO
rate	decimal(5,2)	NO

Table: `v_product_total_tax_rate`

Field	Type	Null	Key	Default	Extra
product_id	bigint(20) unsigned	NO		0
total_rate_percent	decimal(27,2)	YES

Table: `v_receipt_line_compare`

Field	Type	Null	Default
line_id	bigint(20) unsigned	NO	0
invoice_id	bigint(20) unsigned	NO
invoice_no	varchar(40)	NO
invoice_date	date	NO
customer_id	bigint(20) unsigned	NO
customer_name	varchar(160)	NO
product_id	bigint(20) unsigned	NO
product_name	varchar(200)	NO
qty	decimal(16,4)	NO
unit	varchar(32)	NO	EA
unit_price	decimal(12,4)	NO
line_subtotal_stored	decimal(23,2)	NO	0.00
tax_amount_stored	decimal(12,2)	NO	0.00
line_total_stored	decimal(12,2)	NO
tax_rate_percent	decimal(10,4)	YES
tax_amount_calc	decimal(16,4)	YES
line_total_calc	decimal(16,4)	YES
tax_amount_delta	decimal(16,2)	YES
line_total_delta	decimal(16,2)	YES

Table: `v_receipt_line_detail`

Field	Type	Null	Default
line_id	bigint(20) unsigned	NO	0
invoice_id	bigint(20) unsigned	NO	0
invoice_no	varchar(40)	NO
invoice_date	date	NO
customer_id	bigint(20) unsigned	NO
customer_name	varchar(160)	NO
product_id	bigint(20) unsigned	NO
product_name	varchar(200)	NO
qty	decimal(16,4)	NO
unit	varchar(32)	NO	EA
unit_price	decimal(12,4)	NO
line_subtotal	decimal(23,2)	NO	0.00
tax_rate_percent	decimal(10,4)	YES
tax_amount	decimal(16,4)	YES
line_total	decimal(16,4)	YES

Table: `v_sequence_preview`

Field	Type	Null	Default
name	varchar(40)	NO
prefix	varchar(12)	NO
next_val	int(10) unsigned	NO
padding	tinyint(3) unsigned	NO	5
next_formatted	longtext	YES

Table: `vendors`

Field	Type	Null	Key	Default	Extra
id	bigint(20) unsigned	NO	PRI		auto_increment
name	varchar(160)	NO	MUL
contact_name	varchar(120)	YES
phone	varchar(40)	YES
email	varchar(160)	YES
website	varchar(255)	YES
address_line1	varchar(160)	YES
address_line2	varchar(160)	YES
city	varchar(120)	YES
state	varchar(32)	YES
postal_code	varchar(32)	YES
country	varchar(64)	YES		USA
tax_id	varchar(64)	YES
notes	text	YES
created_at	timestamp	NO		current_timestamp()
updated_at	timestamp	NO		current_timestamp()	on update current_timestamp()

Table: `warehouses`

Field	Type	Null	Key	Default	Extra
id	bigint(20) unsigned	NO	PRI		auto_increment
name	varchar(120)	NO	UNI
address_line1	varchar(160)	YES
address_line2	varchar(160)	YES
city	varchar(120)	YES
state	varchar(32)	YES
postal_code	varchar(32)	YES
is_active	tinyint(1)	NO		1

Database Schema (pos_core)

Table: app_settings

Table: ar_ledger

Table: customer_balances

Table: customers

Table: inventory

Table: invoice_items

Table: invoice_payments

Table: invoices

Table: payments

Table: product_tax_map

Table: products

Table: purchase_items

Table: purchase_orders

Table: receipt_items

Table: receipts

Table: schema_version

Table: sequences

Table: tax_rates

Table: users

Table: v_customer_balances

Table: v_customer_balances_detailed

Table: v_customer_statement

Table: v_invoice_balance_due

Table: v_invoice_header_print

Table: v_invoice_payment_links

Table: v_invoice_payment_summary

Table: v_invoice_totals_calc

Table: v_invoice_totals_compare

Table: v_invoice_totals_variances

Table: v_open_invoices

Table: v_payment_applied_summary

Table: v_payment_remaining

Table: v_payment_to_open_invoices

Table: v_product_final_price

Table: v_product_final_price_by_customer

Table: v_product_final_price_exempt

Table: v_product_price_detail

Table: v_product_tax_breakdown

Table: v_product_total_tax_rate

Table: v_receipt_line_compare

Table: v_receipt_line_detail

Table: v_sequence_preview

Table: vendors

Table: warehouses

Table: `app_settings`

Table: `ar_ledger`

Table: `customer_balances`

Table: `customers`

Table: `inventory`

Table: `invoice_items`

Table: `invoice_payments`

Table: `invoices`

Table: `payments`

Table: `product_tax_map`

Table: `products`

Table: `purchase_items`

Table: `purchase_orders`

Table: `receipt_items`

Table: `receipts`

Table: `schema_version`

Table: `sequences`

Table: `tax_rates`

Table: `users`

Table: `v_customer_balances`

Table: `v_customer_balances_detailed`

Table: `v_customer_statement`

Table: `v_invoice_balance_due`

Table: `v_invoice_header_print`

Table: `v_invoice_payment_links`

Table: `v_invoice_payment_summary`

Table: `v_invoice_totals_calc`

Table: `v_invoice_totals_compare`

Table: `v_invoice_totals_variances`

Table: `v_open_invoices`

Table: `v_payment_applied_summary`

Table: `v_payment_remaining`

Table: `v_payment_to_open_invoices`

Table: `v_product_final_price`

Table: `v_product_final_price_by_customer`

Table: `v_product_final_price_exempt`

Table: `v_product_price_detail`

Table: `v_product_tax_breakdown`

Table: `v_product_total_tax_rate`

Table: `v_receipt_line_compare`

Table: `v_receipt_line_detail`

Table: `v_sequence_preview`

Table: `vendors`

Table: `warehouses`