Explore the Lightspeed Retail data model

See how the various Lightspeed Retail endpoints relate to each other, using our handy Entity Relationship Diagram.

Try our interactive ERD Or, read the docs below

Background

Pulling data from Lightspeed Retail is only the first part of your reporting. You also need to understand the relationships between different endpoints - something that APIs typically fail to convey.

At SyncHub, we not only pull your Lightspeed Retail endpoints down into a reportable format, we also describe their relationships (e.g. in terms of foreign keys) so that you can quickly get a grasp of your data and how it fits together. This page is intended as technical documentation of these relationships.

Endpoints

Category

Documentation: https://developers.lightspeedhq.com/retail/endpoints/Category/

Column Type Relates to Notes
RemoteID string - -
MirrorRemoteID int - -
IsDeleted boolean - -
WhenCreated datetime - -
WhenModified datetime - -
Name string - -
CategoryID int - -
NodeDepth string - -
FullPathName string - -
LeftNode int - -
RightNode int - -
CreateTime datetime - -
TimeStamp datetime - -
ParentID int Category → CategoryID -

Customer

Documentation: https://developers.lightspeedhq.com/retail/endpoints/Customer/

Column Type Relates to Notes
RemoteID string - -
MirrorRemoteID int - -
IsDeleted boolean - -
WhenCreated datetime - -
WhenModified datetime - -
CustomerID int - -
FirstName string - -
LastName string - -
Title string - -
Company string - -
CompanyRegistrationNumber string - -
VatNumber string - -
CreditAccountID int -
  • 7 Jul 2020 Foreign Key - Refers to `CreditAccount` (not implemented).
CustomerTypeID int CustomerType → CustomerTypeID -
DiscountID int -
  • 7 Jul 2020 Foreign Key - Refers to `Discount` (not implemented).
TaxCategoryID int TaxCategory → TaxCategoryID -
CreateTime datetime - -
TimeStamp datetime - -
Archived boolean - -

CustomerType

Documentation: https://developers.lightspeedhq.com/retail/endpoints/CustomerType/

Column Type Relates to Notes
RemoteID string - -
MirrorRemoteID int - -
IsDeleted boolean - -
WhenCreated datetime - -
WhenModified datetime - -
Name string - -
CustomerTypeID int - -
DiscountID int -
  • 7 Jul 2020 Foreign Key - Refers to `Discount` (not implemented).
TaxCategoryID int TaxCategory → TaxCategoryID -

Employee

Documentation: https://developers.lightspeedhq.com/retail/endpoints/Employee/

Column Type Relates to Notes
RemoteID string - -
MirrorRemoteID int - -
IsDeleted boolean - -
WhenCreated datetime - -
WhenModified datetime - -
EmployeeID int - -
FirstName string - -
LastName string - -
LockOut boolean - -
Archived boolean - -
TimeStamp datetime - -
ContactID int -
  • 7 Jul 2020 Foreign Key - Refers to `Contact` (not implemented).
ClockInEmployeeHoursID int - -
EmployeeRoleID int -
  • 7 Jul 2020 Foreign Key - Refers to `EmployeeRole` (not implemented).
LimitToShopID int Shop → ShopID -
LastShopID int Shop → ShopID -
LastSaleID int Sale → SaleID -
LastRegisterID int Register → RegisterID -

InventoryCount

Documentation: https://developers.lightspeedhq.com/retail/endpoints/InventoryCount/

Column Type Relates to Notes
RemoteID string - -
MirrorRemoteID int - -
IsDeleted boolean - -
WhenCreated datetime - -
WhenModified datetime - -
Name string - -
InventoryCountID int - -
TimeStamp datetime - -
Archived boolean - -
ShopID int Shop → ShopID -

InventoryCountItem

Documentation: https://developers.lightspeedhq.com/retail/endpoints/InventoryCountItem/

Column Type Relates to Notes
RemoteID string - -
MirrorRemoteID int - -
IsDeleted boolean - -
WhenCreated datetime - -
WhenModified datetime - -
InventoryCountItemID int - -
InventoryCountID int InventoryCount → InventoryCountID -
ItemID int Item → ItemID -
Qty decimal - -
TimeStamp datetime - -
EmployeeID int Employee → EmployeeID -

Item

Documentation: https://developers.lightspeedhq.com/retail/endpoints/Item/

Column Type Relates to Notes
RemoteID string - -
MirrorRemoteID int - -
IsDeleted boolean - -
WhenCreated datetime - -
WhenModified datetime - -
Description string - -
ItemID int - -
SystemSku string - -
DefaultCost decimal - -
AvgCost decimal - -
Discountable boolean - -
Tax boolean - -
Archived boolean - -
ItemType string - -
Serialized boolean - -
ModelYear int - -
Upc string - -
Ean string - -
CustomSku string - -
ManufacturerSku string - -
CreateTime datetime - -
TimeStamp datetime - -
PublishToEcom boolean - -
CategoryID int Category → CategoryID -
TaxClassID int -
  • 13 Jul 2020 Foreign key - References `TaxClass` (not implemented).
DepartmentID int -
  • 13 Jul 2020 Foreign key - References `Department` (not implemented). The endpoint either doesn't exist or could not be found in the documentation.
ItemMatrixID int -
  • 13 Jul 2020 Foreign key - References `ItemMatrix` (not implemented).
ManufacturerID int Manufacturer → ManufacturerID -
SeasonID int -
  • 13 Jul 2020 Foreign key - References `Season` (not implemented).
DefaultVendorID int Vendor → VendorID -

ItemAttributeSet

Documentation: https://developers.lightspeedhq.com/retail/endpoints/ItemAttributeSet/

Column Type Relates to Notes
RemoteID string - -
MirrorRemoteID int - -
IsDeleted boolean - -
WhenCreated datetime - -
WhenModified datetime - -
Name string - -
ItemAttributeSetID int - -
AttributeName1 string - -
AttributeName2 string - -
AttributeName3 string - -
System boolean - -
Archived boolean - -

ItemCustomField

Column Type Relates to Notes
RemoteID string - -
MirrorRemoteID int - -
IsDeleted boolean - -
WhenCreated datetime - -
WhenModified datetime - -
Name string - -
CustomFieldID int - -
Type string - -
Uom string - -
DecimalPrecision int - -
Archived boolean - -

ItemCustomFieldChoice

Column Type Relates to Notes
RemoteID string - -
ItemCustomFieldChoiceRemoteID string - -
MirrorRemoteID int - -
IsDeleted boolean - -
WhenCreated datetime - -
WhenModified datetime - -
Name string - -
CustomFieldID int ItemCustomField → CustomFieldID -
CustomFieldChoiceID int - -
Value string - -
CanBeDeleted boolean - -

ItemCustomFieldValue

Column Type Relates to Notes
RemoteID string - -
ItemCustomFieldValueRemoteID string - -
MirrorRemoteID int - -
IsDeleted boolean - -
WhenCreated datetime - -
WhenModified datetime - -
Name string - -
CustomFieldValueID int - -
ItemID int Item → ItemID -
CustomFieldID int ItemCustomField → CustomFieldID -
Type string -
  • 28 Apr 2021 For example, 'boolean', 'multiple_choice' etc
CustomFieldChoiceID int ItemCustomFieldChoice → CustomFieldID
  • 28 Apr 2021 Is only available if we have a multi/single choice field type
Choice string - -

ItemMatrix

Column Type Relates to Notes
RemoteID string - -
MirrorRemoteID int - -
IsDeleted boolean - -
WhenCreated datetime - -
WhenModified datetime - -
Description string - -
ItemMatrixID int - -
Tax boolean - -
DefaultCost decimal - -
ItemType string - -
Serialized boolean - -
ModelYear int - -
Archived boolean - -
TimeStamp datetime - -
ItemAttributeSetID string -
  • 13 Jul 2020 Foreign key - References `ItemAttributeSet` (not implemented).
ManufacturerID int Manufacturer → ManufacturerID -
CategoryID int Category → CategoryID -
DefaultVendorID int Vendor → VendorID -
TaxClassID int -
  • 13 Jul 2020 Foreign key - References `TaxClass` (not implemented).
SeasonID int -
  • 13 Jul 2020 Foreign key - References `Season` (not implemented).
DepartmentID int -
  • 13 Jul 2020 Foreign key - References `Department (not implemented). The api document is missing!

ItemMatrixPrice

Documentation: https://developers.lightspeedhq.com/retail/endpoints/ItemMatrix/

Column Type Relates to Notes
RemoteID string - -
ItemMatrixPriceRemoteID string - -
MirrorRemoteID int - -
IsDeleted boolean - -
WhenCreated datetime - -
WhenModified datetime - -
Amount string - -
UseTypeID int -
  • 13 Jul 2020 From APIDocs: `Foreign key for the pricing level for this item price`. No endpoint exists or its missing from the documentation
UseType string - -
ItemMatrixID int ItemMatrix → ItemMatrixID -

ItemPrice

Column Type Relates to Notes
RemoteID string - -
ItemPriceRemoteID string - -
MirrorRemoteID int - -
IsDeleted boolean - -
WhenCreated datetime - -
WhenModified datetime - -
Amount string - -
UseTypeID int -
  • 13 Jul 2020 The documentation mentions that this is the `foreign key for the pricing level for this item price`, but I could not find a separate endpoint for it.
UseType string - -
ItemID int Item → ItemID -

ItemShop

Column Type Relates to Notes
RemoteID string - -
ItemShopRemoteID string - -
MirrorRemoteID int - -
IsDeleted boolean - -
WhenCreated datetime - -
WhenModified datetime - -
ItemShopID int - -
ItemID int Item → ItemID -
ShopID int Shop → ShopID -
Qoh int - -
Backorder int - -
ComponentQoh int - -
ComponentBackorder int - -
ReorderPoint int - -
ReorderLevel int - -
TimeStamp datetime - -
Layaways int - -
Specialorders int - -
Workorders int - -

ItemTag

Column Type Relates to Notes
RemoteID string - -
ItemTagRemoteID string - -
MirrorRemoteID int - -
IsDeleted boolean - -
WhenCreated datetime - -
WhenModified datetime - -
TagName string - -
ItemID int Item → ItemID -

Manufacturer

Documentation: https://developers.lightspeedhq.com/retail/endpoints/Manufacturer/

Column Type Relates to Notes
RemoteID string - -
MirrorRemoteID int - -
IsDeleted boolean - -
WhenCreated datetime - -
WhenModified datetime - -
Name string - -
ManufacturerID int - -
CreateTime datetime - -
TimeStamp datetime - -

Order

Documentation: https://developers.lightspeedhq.com/retail/endpoints/Order/

Column Type Relates to Notes
RemoteID string - -
MirrorRemoteID int - -
IsDeleted boolean - -
WhenCreated datetime - -
WhenModified datetime - -
OrderID int - -
OrderedDate datetime - -
ReceivedDate datetime - -
ArrivalDate datetime -
  • 2 Jun 2021 The arrivalDate field is not present in the API response, if its value is NULL
ShipInstructions string - -
StockInstructions string - -
ShipCost decimal - -
OtherCost decimal - -
Complete boolean - -
Archived boolean - -
Discount decimal - -
TotalDiscount decimal - -
TotalQuantity decimal - -
TimeStamp datetime - -
RefNum string - -
VendorID int Vendor → VendorID -
NoteID int -
  • 13 Jul 2020 Foreign key - could not find the relation this is referencing. From APIDocs: ` the foreign key for the notes for this order`.
ShopID int Shop → ShopID -

OrderLine

Documentation: https://developers.lightspeedhq.com/retail/endpoints/OrderLine/

Column Type Relates to Notes
RemoteID string - -
MirrorRemoteID int - -
IsDeleted boolean - -
WhenCreated datetime - -
WhenModified datetime - -
OrderLineID int - -
Quantity decimal - -
Price decimal - -
OriginalPrice decimal - -
CheckedIn string - -
NumReceived decimal - -
TimeStamp datetime - -
Total decimal - -
OrderID int Order → OrderID -
ItemID int Item → ItemID -

Register

Documentation: https://developers.lightspeedhq.com/retail/endpoints/Register/

Column Type Relates to Notes
RemoteID string - -
MirrorRemoteID int - -
IsDeleted boolean - -
WhenCreated datetime - -
WhenModified datetime - -
Name string - -
RegisterID int - -
Open boolean - -
OpenTime datetime - -
Archived boolean - -
OpenEmployeeID int Employee → EmployeeID -
ShopID int Shop → ShopID -
CcTerminalID guid - -

Sale

Documentation: https://developers.lightspeedhq.com/retail/endpoints/Sale/

Column Type Relates to Notes
RemoteID string - -
MirrorRemoteID int - -
IsDeleted boolean - -
WhenCreated datetime - -
WhenModified datetime - -
SaleID int - -
TimeStamp datetime - -
DiscountPercent decimal - -
Completed boolean - -
Archived boolean - -
Voided boolean - -
EnablePromotions boolean - -
IsTaxInclusive boolean - -
CreateTime datetime - -
UpdateTime datetime - -
CompleteTime datetime - -
ReferenceNumber string - -
ReferenceNumberSource string - -
Tax1Rate decimal - -
Tax2Rate decimal - -
Change decimal - -
ReceiptPreference string - -
DisplayableSubtotal decimal - -
TicketNumber string - -
CalcDiscount decimal - -
CalcTotal decimal - -
CalcSubtotal decimal - -
CalcTaxable decimal - -
CalcNonTaxable decimal - -
CalcAvgCost decimal - -
CalcFIFOCost decimal - -
CalcTax1 decimal - -
CalcTax2 decimal - -
CalcPayments decimal - -
Total decimal - -
TotalDue decimal - -
DisplayableTotal decimal - -
Balance decimal - -
CustomerID int Customer → CustomerID -
DiscountID int -
  • 13 Jul 2020 Foreign key - References `Discount` (not implemented).
EmployeeID int Employee → EmployeeID -
QuoteID int -
  • 13 Jul 2020 Foreign key - References `Quote` (not implemented).
RegisterID int Register → RegisterID -
ShipToID int -
  • 13 Jul 2020 Foreign key - References `ShipTo` (not implemented).
ShopID int Shop → ShopID -
TaxCategoryID int TaxCategory → TaxCategoryID -
TaxTotal decimal - -

SaleLine

Documentation: https://developers.lightspeedhq.com/retail/endpoints/SaleLine/

Column Type Relates to Notes
RemoteID string - -
SaleLineRemoteID string - -
MirrorRemoteID int - -
IsDeleted boolean - -
WhenCreated datetime - -
WhenModified datetime - -
SaleLineID int - -
CreateTime datetime - -
TimeStamp datetime - -
UnitQuantity decimal - -
UnitPrice decimal - -
NormalUnitPrice decimal - -
DiscountAmount decimal - -
DiscountPercent decimal - -
AvgCost decimal - -
FifoCost decimal - -
Tax boolean - -
Tax1Rate decimal - -
Tax2Rate decimal - -
IsLayaway boolean - -
IsWorkorder boolean - -
IsSpecialOrder boolean - -
DisplayableSubtotal decimal - -
DisplayableUnitPrice decimal - -
CalcLineDiscount decimal - -
CalcTransactionDiscount decimal - -
CalcTotal decimal - -
CalcSubtotal decimal - -
CalcTax1 decimal - -
CalcTax2 decimal - -
TaxClassID int -
  • 13 Jul 2020 Foreign key - References `TaxClass` (not implemented).
CustomerID int Customer → CustomerID -
DiscountID int -
  • 13 Jul 2020 Foreign key - References `Discount` (not implemented).
EmployeeID int Employee → EmployeeID -
ItemID int Item → ItemID -
NoteID int -
  • 13 Jul 2020 Foreign key - could not find the relation this is referencing. From APIDocs: `the foreign ID for the Note object associated with this line. Only one Note object can be attached to each line. Notes will print on the receipt.`.
ParentSaleLineID int SaleLine → SaleLineID -
ShopID int Shop → ShopID -
TaxCategoryID int TaxCategory → TaxCategoryID -
SaleID int Sale → SaleID -

Shop

Documentation: https://developers.lightspeedhq.com/retail/endpoints/Shop/

Column Type Relates to Notes
RemoteID string - -
MirrorRemoteID int - -
TimeZone string - -
IsDeleted boolean - -
WhenCreated datetime - -
WhenModified datetime - -
Name string - -
ShopID int - -
TimeStamp datetime - -
ServiceRate decimal - -
TaxLabor boolean - -
LabelTitle string - -
LabelMsrp boolean - -
Archived boolean - -
CompanyRegistrationNumber string - -
VatNumber string - -
ZebraBrowserPrint boolean - -
ContactID int -
  • 13 Jul 2020 Foreign key - APIDocs point to `Customer/Contact-fields
TaxCategoryID int TaxCategory → TaxCategoryID -
ReceiptSetupID int -
  • 13 Jul 2020 Foreign key - APIDocs are missing or the endpoint doesn't exist. Form APIDocs: `the foreign key for the receipt setup, text at the top and bottom of the receipt etc.`
CcGatewayID int -
  • 13 Jul 2020 Foreign key - APIDocs are missing or the endpoint doesn't exist. From APIDocs: `the foreign key for the credit card gateway setup for this shop.`
GatewayConfigID string - -
PriceLevelID int -
  • 13 Jul 2020 Foreign key - References `PriceLevel` (not implemented).

TaxCategory

Documentation: https://developers.lightspeedhq.com/retail/endpoints/TaxCategory/

Column Type Relates to Notes
RemoteID string - -
MirrorRemoteID int - -
IsDeleted boolean - -
WhenCreated datetime - -
WhenModified datetime - -
TaxCategoryID int - -
IsTaxInclusive boolean - -
Tax1Name string - -
Tax2Name string - -
Tax1Rate decimal - -
Tax2Rate decimal - -
TimeStamp datetime - -

Vendor

Column Type Relates to Notes
RemoteID string - -
MirrorRemoteID int - -
IsDeleted boolean - -
WhenCreated datetime - -
WhenModified datetime - -
Name string - -
VendorID int - -
Archived boolean - -
AccountNumber string - -
PriceLevel string - -
UpdatePrice boolean - -
UpdateCost boolean - -
UpdateDescription boolean - -
ShareSellThrough boolean - -
TimeStamp datetime - -
Get started with a free trial Or, find out more about querying Lightspeed Retail using regular SQL

Need more?

Why not check out our interactive Entity Relationship Diagram.