Explore the Lightspeed Retail (R-Series) data model

See how the various Lightspeed Retail (R-Series) endpoints relate to each other, using our handy Entity Relationship Diagram.

Pulling data from Lightspeed Retail (R-Series) 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 (R-Series) 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 - -
ContactID int Contact → ContactID -

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 -

ItemPrice

Column Type Relates to Notes
RemoteID string - -
ItemRemoteID string Item → RemoteID -
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 -

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 string -
  • 15 Jul 2021 We have observed this value coming down as integer and Guid values

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 - -
SaleRemoteID string Sale → RemoteID -
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 -

SalePayment

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

Column Type Relates to Notes
RemoteID string - -
MirrorRemoteID int - -
IsDeleted boolean - -
WhenCreated datetime - -
WhenModified datetime - -
SalePaymentID int - -
Amount decimal - -
CreateTime datetime - -
Archived boolean - -
RemoteReference string - -
PaymentID string - -
SaleID int Sale → SaleID -
PaymentTypeID int -
  • 13 Jul 2020 Foreign key - References `PaymentType` (not implemented).
CcChargeID int -
  • 13 Jul 2020 Foreign Key - References `CCCharge` not implemented).
RefPaymentID int SalePayment → SalePaymentID -
RegisterID int Register → RegisterID -
EmployeeID int Employee → EmployeeID -
CreditAccountID int -
  • 13 Jul 2020 Foreign key - References `CreditAccount` (not implemented).

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 - -
ContactID int Contact → ContactID -

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 - -
ItemMatrixRemoteID string ItemMatrix → RemoteID -
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 -

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 - -

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 - -

ItemShop

Column Type Relates to Notes
RemoteID string - -
ItemRemoteID string Item → RemoteID -
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 - -
ItemRemoteID string Item → RemoteID -
MirrorRemoteID int - -
IsDeleted boolean - -
WhenCreated datetime - -
WhenModified datetime - -
TagName string - -
ItemID int Item → ItemID -

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 - -
ItemCustomFieldRemoteID string ItemCustomField → RemoteID -
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 - -
ItemRemoteID string Item → RemoteID -
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 - -

Contact

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

Column Type Relates to Notes
RemoteID string - -
CustomerRemoteID string Customer → RemoteID -
MirrorRemoteID int - -
IsDeleted boolean - -
WhenCreated datetime - -
WhenModified datetime - -
ContactID int - -
NoEmail boolean - -
NoPhone boolean - -
NoMail boolean - -
WebsiteUrl string - -
PhoneHome string - -
PhoneWork string - -
PhoneMobile string - -
PhonePager string - -
PhoneFax string - -
PrimaryEmail string - -
SecondaryEmail string - -
Address1 string - -
Address2 string - -
City string - -
State string - -
Zip string - -
Country string - -
CountryCode string - -
StateCode string - -

Need more?

SyncHub lives and breathes APIs and data. Keep exploring using the links below.