Lightspeed Retail

Explore the interactive ERD

Table & Field Reference

item_shop_history

Column	Type	Keys
`id`	Long	PK
`updated_time`	Instant	PK
`item_id`	Long	FK
`shop_id`	Long	FK
`backorder`	Int
`component_backorder`	Int
`component_qoh`	Int
`qoh`	Int
`reorder_level`	Int
`reorder_point`	Int

payment_type

Column	Type	Keys
`id`	Long	PK
`refund_as_payment_type_id`	Long	FK
`archived`	Boolean
`internal_reserved`	Boolean
`name`	String
`require_customer`	Boolean
`type`	String

price_level

Column	Type	Keys
`id`	Long	PK
`archived`	Boolean
`can_be_archived`	Boolean
`name`	String
`type`	String

employee_role

Column	Type	Keys
`id`	Long	PK
`archived`	Boolean
`name`	String
`system`	Boolean

order_history

Column	Type	Keys
`id`	Long	PK
`updated_time`	Instant	PK
`shop_id`	Long	FK
`vendor_id`	Long	FK
`archived`	Boolean
`arrival_date`	Instant
`complete`	Boolean
`discount`	Double
`note`	String
`note_id`	Long
`note_is_public`	Boolean
`note_updated_time`	Instant
`ordered_date`	Instant
`other_cost`	Double
`received_date`	Instant
`reference_number`	String
`ship_cost`	Double
`ship_instructions`	String
`stock_instructions`	String
`total_discount`	Double
`total_quantity`	Int

customer_history

Column	Type	Keys
`id`	Long	PK
`updated_time`	Instant	PK
`credit_account_id`	Long	FK
`customer_type_id`	Long	FK
`discount_id`	Long	FK
`tax_category_id`	Long	FK
`address_1`	String
`address_2`	String
`archived`	Boolean
`city`	String
`company`	String
`company_registration_number`	String
`country`	String
`country_code`	String
`created_time`	Instant
`custom`	String
`dob`	Instant
`first_name`	String
`last_name`	String
`no_email`	Boolean
`no_mail`	Boolean
`no_phone`	Boolean
`note`	String
`note_is_public`	Boolean
`note_updated_time`	Instant
`state`	String
`state_code`	String
`title`	String
`vat_number`	String
`website_url`	String
`zip`	String

quote

Column	Type	Keys
`id`	Long	PK
`employee_id`	Long	FK
`sale_id`	Long	FK
`archived`	Boolean
`issue_date`	Instant
`notes`	String

register_count

Column	Type	Keys
`id`	Long	PK
`close_employee_id`	Long	FK
`open_employee_id`	Long	FK
`register_id`	Long	FK
`created_time`	Instant
`notes`	String
`open_time`	Instant

inventory_count_reconcile

Column	Type	Keys
`id`	Long	PK
`inventory_count_id`	Long	FK
`item_id`	Long	FK
`cost_change`	Double
`created_time`	Instant
`qoh_change`	Int

item_vendor_num_history

Column	Type	Keys
`id`	Long	PK
`updated_time`	Instant	PK
`item_id`	Long	FK
`vendor_id`	Long	FK
`value`	String

order_by_tax_class

Column	Type	Keys
`date`	LocalDate	PK
`order_id`	Long	PKFK
`tax_class_id`	Long	PKFK
`shop_id`	Long	FK
`vendor_id`	Long	FK
`cost`	Double
`tax_class_name`	String
`total_other_cost`	Double
`total_ship_cost`	Double
`vendor_name`	String

sale_history

Column	Type	Keys
`id`	Long	PK
`updated_time`	Instant	PK
`customer_id`	Long	FK
`discount_id`	Long	FK
`employee_id`	Long	FK
`quote_id`	Long	FK
`register_id`	Long	FK
`ship_to_id`	Long	FK
`shop_id`	Long	FK
`tax_category_id`	Long	FK
`archived`	Boolean
`balance`	Double
`calc_avg_cost`	Double
`calc_discount`	Double
`calc_fifocost`	Double
`calc_non_taxable`	Double
`calc_payments`	Double
`calc_subtotal`	Double
`calc_tax_1`	Double
`calc_tax_2`	Double
`calc_taxable`	Double
`calc_total`	Double
`change`	Double
`complete_time`	Instant
`completed`	Boolean
`created_time`	Instant
`discount_percent`	Double
`displayable_subtotal`	Double
`displayable_total`	Double
`enable_promotions`	Boolean
`is_tax_inclusive`	Boolean
`receipt_preference`	String
`reference_number`	String
`reference_number_source`	String
`tax_1_rate`	Double
`tax_2_rate`	Double
`tax_total`	Double
`ticket_number`	String
`total`	Double
`total_due`	Double
`voided`	Boolean

work_order_history

Column	Type	Keys
`id`	Long	PK
`updated_time`	Instant	PK
`customer_id`	Long	FK
`discount_id`	Long	FK
`employee_id`	Long	FK
`sale_id`	Long	FK
`sale_line_id`	Long	FK
`serialized_id`	Long	FK
`shop_id`	Long	FK
`workorder_status_id`	Long	FK
`archived`	Boolean
`assign_employee_to_all`	Boolean
`eta_out`	Instant
`hook_in`	String
`hook_out`	String
`note`	String
`save_parts`	Boolean
`system_sku`	String
`tax`	Boolean
`time_in`	Instant
`warranty`	Boolean

ship_to_history

Column	Type	Keys
`id`	Long	PK
`updated_time`	Instant	PK
`customer_id`	Long	FK
`sale_id`	Long	FK
`address_1`	String
`address_2`	String
`city`	String
`company`	String
`country`	String
`country_code`	String
`custom`	String
`email_address`	String
`email_use_type`	String
`first_name`	String
`last_name`	String
`secondary_email_address`	String
`secondary_email_use_type`	String
`ship_note`	String
`state`	String
`state_code`	String
`website_url`	String
`zip`	String

vendor_email_history

Column	Type	Keys
`use_type`	String	PK
`vendor_id`	Long	PKFK
`vendor_updated_time`	Instant	PKFK
`address`	String

inventory_count_calculation

Column	Type	Keys
`id`	Long	PK
`updated_time`	Instant	PK
`inventory_count_id`	Long	FK
`item_id`	Long	FK
`calculated_qoh`	Int
`counted_qoh`	Int

item_matrix_tag_history

Column	Type	Keys
`tag`	String	PK
`item_matrix_id`	Long	PKFK
`item_matrix_updated_time`	Instant	PKFK

item_tag_history

Column	Type	Keys
`tag`	String	PK
`item_id`	Long	PKFK
`item_updated_time`	Instant	PKFK

employee_right

Column	Type	Keys
`id`	Long	PK
`employee_role_id`	Long	PKFK
`name`	String

credit_account_history

Column	Type	Keys
`id`	Long	PK
`updated_time`	Instant	PK
`customer_id`	Long	FK
`archived`	Boolean
`balance`	Double
`code`	String
`credit_limit`	Double
`description`	String
`gift_card`	Boolean
`name`	String

attribute_value

Column	Type	Keys
`attribute_type`	String	PK
`index`	Int	PK
`item_matrix_id`	Long	PKFK
`value`	String

customer_email_history

Column	Type	Keys
`use_type`	String	PK
`customer_id`	Long	PKFK
`customer_updated_time`	Instant	PKFK
`address`	String

tax_category_history

Column	Type	Keys
`id`	Long	PK
`updated_time`	Instant	PK
`is_tax_inclusive`	Boolean
`tax_1_name`	String
`tax_1_rate`	Double
`tax_2_name`	String
`tax_2_rate`	Double

register_calculated

Column	Type	Keys
`payment_type_id`	Long	PKFK
`register_id`	Long	PKFK
`register_open_time`	Instant	PKFK
`add`	Double
`payment`	Double
`withdraw`	Double

register_count_amount

Column	Type	Keys
`id`	Long	PK
`payment_type_id`	Long	FK
`register_count_id`	Long	FK
`actual`	Double
`calculated`	Double

serial_history

Column	Type	Keys
`id`	Long	PK
`updated_time`	Instant	PK
`customer_id`	Long	FK
`item_id`	Long	FK
`sale_line_id`	Long	FK
`color_name`	String
`description`	String
`serial`	String
`size_name`	String

item_history

Column	Type	Keys
`id`	Long	PK
`updated_time`	Instant	PK
`category_id`	Long	FK
`default_vendor_id`	Long	FK
`item_attribute_set_id`	Long	FK
`item_matrix_id`	Long	FK
`manufacturer_id`	Long	FK
`tax_class_id`	Long	FK
`archived`	Boolean
`attribute_1`	String
`attribute_2`	String
`attribute_3`	String
`avg_cost`	Double
`created_time`	Instant
`custom_sku`	String
`default_cost`	Double
`department_id`	Long
`description`	String
`discountable`	Boolean
`ean`	String
`item_type`	String
`manufacturer_sku`	String
`model_year`	Int
`note`	String
`note_is_public`	Boolean
`note_updated_time`	Instant
`publish_to_ecom`	Boolean
`season_id`	Long
`serialized`	Boolean
`system_sku`	Long
`tax`	Boolean
`upc`	String

discount_by_day

Column	Type	Keys
`date`	LocalDate	PK
`shop_id`	Long	PKFK
`discount`	Double

employee_hours

Column	Type	Keys
`id`	Long	PK
`employee_id`	Long	FK
`shop_id`	Long	FK
`check_in`	Instant
`check_out`	Instant

item_attribute_set

Column	Type	Keys
`id`	Long	PK
`archived`	Boolean
`attribute_name_1`	String
`attribute_name_2`	String
`attribute_name_3`	String
`name`	String
`system`	Boolean

work_order_status

Column	Type	Keys
`id`	Long	PK
`html_color`	String
`name`	String
`sort_order`	String
`system_value`	String

customer_type

Column	Type	Keys
`id`	Long	PK
`discount_id`	Long	FK
`tax_category_id`	Long	FK
`name`	String

customer_phone_history

Column	Type	Keys
`use_type`	String	PK
`customer_id`	Long	PKFK
`customer_updated_time`	Instant	PKFK
`number`	String

phone_history

Column	Type	Keys
`use_type`	String	PK
`contact_id`	Long	PKFK
`contact_updated_time`	Instant	PKFK
`number`	String

contact_history

Column	Type	Keys
`id`	Long	PK
`updated_time`	Instant	PK
`address_1`	String
`address_2`	String
`city`	String
`country`	String
`country_code`	String
`custom`	String
`no_email`	Boolean
`no_mail`	Boolean
`no_phone`	Boolean
`state`	String
`state_code`	String
`website_url`	String
`zip`	String

work_order_line_history

Column	Type	Keys
`id`	Long	PK
`updated_time`	Instant	PK
`discount_id`	Long	FK
`employee_id`	Long	FK
`item_id`	Long	FK
`sale_id`	Long	FK
`sale_line_id`	Long	FK
`tax_class_id`	Long	FK
`workorder_id`	Long	FK
`approved`	Boolean
`done`	Boolean
`hours`	Int
`minutes`	Int
`note`	String
`tax`	Boolean
`unit_cost`	Double
`unit_price_override`	Double
`unit_quantity`	Int
`warranty`	Boolean

item_tag

Column	Type	Keys
`tag`	String	PK
`item_id`	Long	PKFK

tax_by_day

Column	Type	Keys
`date`	LocalDate	PK
`index`	Long	PK
`shop_id`	Long	PKFK
`tax_category_id`	Long	FK
`tax`	Double
`tax_category_name`	String

item_matrix_history

Column	Type	Keys
`id`	Long	PK
`updated_time`	Instant	PK
`category_id`	Long	FK
`default_vendor_id`	Long	FK
`item_attribute_set_id`	Long	FK
`manufacturer_id`	Long	FK
`tax_class_id`	Long	FK
`archived`	Boolean
`default_cost`	Double
`description`	String
`item_type`	String
`model_year`	Int
`serialized`	Boolean
`tax`	Boolean

discount_history

Column	Type	Keys
`id`	Long	PK
`updated_time`	Instant	PK
`archived`	Boolean
`discount_amount`	Float
`discount_percent`	Float
`name`	String
`require_customer`	Boolean

sale_line_history

Column	Type	Keys
`id`	Long	PK
`updated_time`	Instant	PK
`customer_id`	Long	FK
`discount_id`	Long	FK
`employee_id`	Long	FK
`item_id`	Long	FK
`parent_sale_line_id`	Long	FK
`sale_id`	Long	FK
`shop_id`	Long	FK
`tax_category_id`	Long	FK
`tax_class_id`	Long	FK
`avg_cost`	Double
`calc_subtotal`	Double
`calc_tax_1`	Double
`calc_tax_2`	Double
`calc_total`	Double
`calc_transaction_discount`	Double
`calculated_line_discount`	Double
`created_time`	Instant
`discount_amount`	Double
`discount_percent`	Double
`displayable_subtotal`	Double
`displayable_unit_price`	Double
`fifo_cost`	Double
`is_layaway`	Boolean
`is_special_order`	Boolean
`is_workorder`	Boolean
`normal_unit_price`	Double
`note`	String
`note_id`	Long
`note_is_public`	Boolean
`note_updated_time`	Instant
`tax`	Boolean
`tax_1_rate`	Double
`tax_2_rate`	Double
`unit_price`	Double
`unit_quantity`	Int

category_history

Column	Type	Keys
`id`	Long	PK
`updated_time`	Instant	PK
`parent_id`	Long	FK
`created_time`	Instant
`full_path_name`	String
`left_node`	Int
`name`	String
`node_depth`	Int
`right_node`	Int

item_matrix_tag

Column	Type	Keys
`tag`	String	PK
`item_matrix_id`	Long	PKFK

email_history

Column	Type	Keys
`use_type`	String	PK
`contact_id`	Long	PKFK
`contact_updated_time`	Instant	PKFK
`address`	String

register_withdraw

Column	Type	Keys
`id`	Long	PK
`employee_id`	Long	FK
`payment_type_id`	Long	FK
`register_id`	Long	FK
`amount`	Double
`created_time`	Instant
`notes`	String

ship_to_phone_history

Column	Type	Keys
`use_type`	String	PK
`ship_to_id`	Long	PKFK
`ship_to_updated_time`	Instant	PKFK
`number`	String

shop_history

Column	Type	Keys
`id`	Long	PK
`updated_time`	Instant	PK
`contact_id`	Long	FK
`price_level_id`	Long	FK
`tax_category_id`	Long	FK
`archived`	Boolean
`cc_gateway_id`	Long
`company_registration_number`	String
`gateway_config_id`	String
`label_msrp`	Boolean
`label_title`	String
`name`	String
`receipt_setup_id`	Long
`service_rate`	Double
`tax_labor`	Boolean
`time_zone`	String
`vat_number`	String
`zebra_browser_print`	Boolean

cc_charge_history

Column	Type	Keys
`id`	Long	PK
`updated_time`	Instant	PK
`sale_id`	Long	FK
`amount`	Double
`auth_code`	String
`auth_only`	Boolean
`card_type`	String
`cardholder_name`	String
`communication_key`	String
`declined`	Boolean
`entry_method`	String
`expiry_date`	String
`gateway_transaction_id`	String
`is_debit`	Boolean
`refunded`	Double
`voided`	Boolean
`xnum`	String

employee_history

Column	Type	Keys
`id`	Long	PK
`updated_time`	Instant	PK
`clock_in_employee_hours_id`	Long	FK
`contact_id`	Long	FK
`employee_role_id`	Long	FK
`last_register_id`	Long	FK
`last_sale_id`	Long	FK
`last_shop_id`	Long	FK
`limit_to_shop_id`	Long	FK
`archived`	Boolean
`first_name`	String
`last_name`	String
`lock_out`	Boolean

inventory_count_item_history

Column	Type	Keys
`id`	Long	PK
`updated_time`	Instant	PK
`employee_id`	Long	FK
`inventory_count_id`	Long	FK
`item_id`	Long	FK
`quantity`	Int

tax_class_sales_by_day

Column	Type	Keys
`date`	LocalDate	PK
`shop_id`	Long	PKFK
`tax_class_id`	Long	PKFK
`avg_cost`	Double
`fifo_cost`	Double
`subtotal`	Double
`tax_class_name`	String

item_matrix_price

Column	Type	Keys
`use_type_id`	Int	PK
`item_matrix_id`	Long	PKFK
`amount`	Double
`use_type`	String

work_order_item_history

Column	Type	Keys
`id`	Long	PK
`updated_time`	Instant	PK
`discount_id`	Long	FK
`employee_id`	Long	FK
`item_id`	Long	FK
`sale_id`	Long	FK
`sale_line_id`	Long	FK
`workorder_id`	Long	FK
`approved`	Boolean
`is_special_order`	Boolean
`note`	String
`tax`	Boolean
`unit_price`	Double
`unit_quantity`	Int
`warranty`	Boolean

manufacturer_history

Column	Type	Keys
`id`	Long	PK
`updated_time`	Instant	PK
`created_time`	Instant
`name`	String

item_price

Column	Type	Keys
`item_id`	Long	PK
`use_type_id`	Int	PK
`amount`	Double
`use_type`	String

item_price_history

Column	Type	Keys
`use_type_id`	Int	PK
`item_id`	Long	PKFK
`item_updated_time`	Instant	PKFK
`amount`	Double
`use_type`	String

register_history

Column	Type	Keys
`id`	Long	PK
`open_time`	Instant	PK
`open_employee_id`	Long	FK
`shop_id`	Long	FK
`archived`	Boolean
`cc_terminal_id`	String
`name`	String
`open`	Boolean

sale_void

Column	Type	Keys
`id`	Long	PK
`employee_id`	Long	FK
`sale_id`	Long	FK
`shop_id`	Long	FK
`created_time`	Instant
`reason`	String

item_matrix_price_history

Column	Type	Keys
`use_type_id`	Int	PK
`item_matrix_id`	Long	PKFK
`item_matrix_updated_time`	Instant	PKFK
`amount`	Double
`use_type`	String

payment_by_day

Column	Type	Keys
`date`	LocalDate	PK
`payment_type_id`	Long	PKFK
`shop_id`	Long	PKFK
`amount`	Double
`payment_type_name`	String

inventory_count_history

Column	Type	Keys
`id`	Long	PK
`updated_time`	Instant	PK
`shop_id`	Long	FK
`archived`	Boolean
`name`	String

sale_payment

Column	Type	Keys
`id`	Long	PK
`cc_charge_id`	Long	FK
`credit_account_id`	Long	FK
`employee_id`	Long	FK
`payment_type_id`	Long	FK
`ref_payment_id`	Long	FK
`register_id`	Long	FK
`sale_id`	Long	FK
`amount`	Double
`archived`	Boolean
`created_time`	Instant
`payment_id`	String
`remote_reference`	String

vendor_history

Column	Type	Keys
`id`	Long	PK
`updated_time`	Instant	PK
`account_number`	String
`address_1`	String
`address_2`	String
`archived`	Boolean
`city`	String
`country`	String
`country_code`	String
`custom`	String
`name`	String
`no_email`	Boolean
`no_mail`	Boolean
`no_phone`	Boolean
`price_level`	String
`rep_first_name`	String
`rep_last_name`	String
`share_sell_through`	Boolean
`state`	String
`state_code`	String
`update_cost`	Boolean
`update_description`	Boolean
`update_price`	Boolean
`website_url`	String
`zip`	String

vendor_phone_history

Column	Type	Keys
`use_type`	String	PK
`vendor_id`	Long	PKFK
`vendor_updated_time`	Instant	PKFK
`number`	String

attribute_value_history

Column	Type	Keys
`attribute_type`	String	PK
`index`	Int	PK
`item_matrix_id`	Long	PKFK
`item_matrix_updated_time`	Instant	PKFK
`value`	String

tax_class_history

Column	Type	Keys
`id`	Long	PK
`updated_time`	Instant	PK
`name`	String

inventory_transfer_history

Column	Type	Keys
`id`	Long	PK
`updated_time`	Instant	PK
`created_by_employee_id`	Long	FK
`receiving_shop_id`	Long	FK
`sending_shop_id`	Long	FK
`sent_by_employee_id`	Long	FK
`archived`	Boolean
`created_time`	Instant
`need_by`	Instant
`note`	String
`sent_on`	Instant
`status`	String

transfer_item_history

Column	Type	Keys
`id`	Long	PK
`updated_time`	Instant	PK
`item_id`	Long	FK
`transfer_id`	Long	FK
`comment`	String
`received`	Int
`received_value`	Double
`sent`	Int
`sent_value`	Double
`to_receive`	Int
`to_send`	Int

special_order_history

Column	Type	Keys
`id`	Long	PK
`updated_time`	Instant	PK
`customer_id`	Long	FK
`order_line_id`	Long	FK
`sale_line_id`	Long	FK
`shop_id`	Long	FK
`transfer_item_id`	Long	FK
`completed`	Boolean
`contacted`	Boolean
`status`	String
`unit_quantity`	Int

order_line_history

Column	Type	Keys
`id`	Long	PK
`updated_time`	Instant	PK
`item_id`	Long	FK
`order_id`	Long	FK
`checked_in`	Int
`num_received`	Int
`original_price`	Double
`price`	Double
`quantity`	Int
`total`	Double