Explore the Lightspeed Retail (R-Series) data model

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