Integration Specifications

This article will help you learn about how Daasity replicates data from Lightspeed, limitations to the data we can extract and where the data is stored in the Lightspeed schema

Integration Overview

Lightspeed is a cloud-based point-of-sale and e-commerce platform for retail, hospitality and golf merchants around the world to simplify, sale and provide customer experiences.

This document provides context on what kind of data is being gathered through this extractor, which endpoints that data is coming from, and how the extracted tables relate to each other.

Integration Availability

This integration is available for:

• Enterprise

API Endpoints

The Daasity Lightspeed extractor is built based on this Lightspeed API documentation. The following endpoints are used by Daasity to replicate data from Lightspeed:

• Account

• Customer

• Employee

• Discount

• Inventory Count Item

• Item

• Item Matrix

• Sale

• Sale Lines

• Sale Payment

• Shop

Entity Relationship Diagram (ERD)

Click here to view the ERD for the Daasity Lightspeed integration illustrating the different tables and keys to join across tables

Lightspeed Schema

The Daasity Lightspeed extractor creates these tables using the endpoints and replication methods listed. The data is mapped from source API endpoint to the table based on the mapping logic outlined in each table.

• Accounts

• Customers

• Employees

• Sales

• Sales Payments

• Order Discounts

• Sale Lines

• Items

• Item Prices

• Items Matrix

• Item Matrix Prices

• Inventory

• Locations

Accounts

• Endpoint: Account

• Update Method: UPSERT

• Table Name: [lightspeed.accounts]

JSON Element	Database Column
account_id	account_id
Account::name	name
Daasity: shop_id	__shop_id
Daasity: account_id	_account_id
Daasity: MD5(account_id + __shop_id)	__sync_key
Daasity: timestamp when loaded into DB	__synced_at

Customers

• Endpoint: Customer

• Update Method: UPSERT

• Table Name: [lightspeed.customers]

JSON Element	Database Column
customerID	customer_id
firstName	first_name
lastName	last_name
Contact::Addresses:ContactAddress::address1	address_1
Contact::Addresses:ContactAddress::address2	address_2
Contact::Addresses:ContactAddress::zip	zip
Contact::Addresses:ContactAddress::city	city
Contact::Addresses:ContactAddress::state	state
Contact::Addresses:ContactAddress::country	country
Contact::Phones	phone_number
Contact::Emails	email
archived	archived
createTime	created_at
timeStamp	updated_at
Daasity: shop_id	__shop_id
Daasity: account_id	_account_id
Daasity: MD5(customerId + __shop_id)	__sync_key
Daasity: timestamp when loaded into DB	__synced_at

Employees

• Endpoint: Employee

• Update Method: UPSERT

• Table Name: [lightspeed.employees]

JSON Element	Database Column
employeeID	employee_id
firstName	first_name
lastName	last_name
lastShopID	last_shop_id
employeeRoleID	employee_role_id
timeStamp	updated_at
archived	archived
Daasity: shop_id	__shop_id
Daasity: account_id	_account_id
Daasity: MD5(employeeID + __shop_id)	__sync_key
Daasity: timestamp when loaded into DB	__synced_at

Sales

• Endpoint: Sale

• Update Method: UPSERT

• Table Name: [lightspeed.sales]

JSON Element	Database Column
saleID	sale_id
discountPercent	discount_percent
completed	completed
voided	voided
tax1Rate	tax1_rate
tax2Rate	tax2_rate
receiptPreference	receipt_preference
ticketNumber	ticket_number
calcDiscount	calc_discount
calcTotal	calc_total
calc_subtotal	calc_subtotal
calcTaxable	calc_taxable
calcNonTaxable	calc_nontaxable
calcAvgCost	calc_avg_cost
calcFIFOCost	calc_fifo_cost
calcTax1	calc_tax1
calcTax2	calc_tax2
calcPayments	calc_payments
total	total
totalDue	total_due
createTime	created_at
updateTime	updated_at
archived	archived
customerID	customer_id
employeeID	employee_id
shopID	shop_id
discountID	discount_id
referenceNumber	reference_number
referenceNumberSource	reference_number_source
Daasity: shop_id	__shop_id
Daasity: account_id	_account_id
Daasity: MD5(shopId + saleID + __shop_id)	__sync_key
Daasity: timestamp when loaded into DB	__synced_at

Sale Payments

• Endpoint: Sale Payment

• Update Method: UPSERT

• Table Name: [lightspeed.sale_payments]

JSON Element	Database Column
salePaymentID	sale_payment_id
amount	amount
saleID	sale_id
paymentTypeID	payment_type_id
ccChargeID	cc_charge_id
refPaymentID	ref_payment_id
employeeID	employee_id
creditAccountID	credit_account_id
registerID	register_id
archived	archived
createTime	created_at
Daasity: shop_id	__shop_id
Daasity: account_id	_account_id
Daasity: MD5(salePaymentID + __shop_id)	__sync_key
Daasity: timestamp when loaded into DB	__synced_at

Order Discounts

• Endpoint: Discount

• Update Method: UPSERT

• Table Name: [lightspeed.order_discounts]

JSON Element	Database Column
discountID	discount_id
name	name
discountAmount	discount_amount
discountPercent	discount_percent
timeStamp	updated_at
Daasity: shop_id	__shop_id
Daasity: account_id	_account_id
Daasity: MD5(discountID + __shop_id)	__sync_key
Daasity: timestamp when loaded into DB	__synced_at

Sale Lines

• Endpoint: Sale Lines

• Update Method: UPSERT

• Table Name: [lightspeed.salelines]

JSON Element	Database Column
saleLineID	sale_line_id
unitQuantity	unit_quantity
unitPrice	unit_price
discountAmount	discount_amount
discountPercent	discount_percent
tax	tax
tax1Rate	tax1_rate
tax2Rate	tax2_rate
calcLineDiscount	calc_line_discount
calcTransactionDiscount	calc_transaction_discount
calcTotal	calc_total
calcSubtotal	calc_subtotal
calcTax1	calc_tax1
calcTax2	calc_tax2
taxClassID	tax_class_id
discountID	discount_id
employeeID	employee_id
customerID	customer_id
shopID	shop_id
itemID	item_id
saleID	sale_id
createTime	created_at
timeStamp	updated_at
Daasity: shop_id	__shop_id
Daasity: account_id	_account_id
Daasity: MD5(saleLineID + shopID + __shop_id)	__sync_key
Daasity: timestamp when loaded into DB	__synced_at

Items

• Endpoint: Item

• Update Method: UPSERT

• Table Name: [lightspeed.items]

JSON Element	Database Column
itemID	item_id
systemSku	system_sku
defaultCost	default_cost
avgCost	avg_cost
discountable	discountable
tax	tax
archived	archived
itemType	item_type
description	description
modelYear	model_year
upc	upc
ean	ean
createTime	created_at
timeStamp	updated_at
categoryID	category_id
taxClassID	taxclass_id
departmentID	department_id
itemMatrixID	item_matrix_id
Daasity: shop_id	__shop_id
Daasity: account_id	_account_id
Daasity: MD5(itemID + shopID + __shop_id)	__sync_key
Daasity: timestamp when loaded into DB	__synced_at

Item Prices

• Endpoint: Item

• Update Method: UPSERT

• Table Name: [lightspeed.item_prices]

JSON Element	Database Column
itemID	item_id
amount	amount
useTypeID	use_type_id
useType	use_type
Daasity: shop_id	__shop_id
Daasity: account_id	_account_id
Daasity: MD5(itemMatrixId + useTypeId + useType)	__sync_key
Daasity: timestamp when loaded into DB	__synced_at

Items Matrix

• Endpoint: Item Matrix

• Update Method: UPSERT

• Table Name: [lightspeed.items_matrix]

JSON Element	Database Column
itemMatrixID	item_matrix_id
tax	tax
description	description
itemType	item_type
timeStamp	created_at
archived	archived
Daasity: shop_id	__shop_id
Daasity: account_id	_account_id
Daasity: MD5(itemMatrixId + __shop_id)	__sync_key
Daasity: timestamp when loaded into DB	__synced_at

Item Matrix Prices

• Endpoint: Item Matrix

• Update Method: UPSERT

• Table Name: [lightspeed.item_matrix_prices]

JSON Element	Database Column
Item::Prices::ItemPrice::ItemMatrixID	item_matrix_id
ItemPrice::amount	amount
ItemPrice::useType	use_type
ItemPrice::useTypeID	use_type_id
Daasity: shop_id	__shop_id
Daasity: account_id	_account_id
Daasity: MD5(ItemPrice::itemMatrixId + ItemPrice::useType + ItemPrice::useTypeID + __shop_id)	__sync_key
Daasity: timestamp when loaded into DB	__synced_at

Inventory

• Endpoint: Inventory Count Item

• Update Method: UPSERT

• Table Name: [lightspeed.inventory]

JSON Element	Database Column
itemShopID	item_shop_id
sellable	sellable
backorder	backorder
qoh	quantity_on_hand
itemID	item_id
shopID	shop_id
timeStamp	created_at
Daasity: shop_id	__shop_id
Daasity: account_id	_account_id
Daasity: MD5(shopID + itemID + itemShopID + __shop_id)	__sync_key
Daasity: timestamp when loaded into DB	__synced_at

Locations

• Endpoint: Shop

• Update Method: UPSERT

• Table Name: [lightspeed.locations]

JSON Element	Database Column
name	name
timeZone	time_zone
shopID	shop_id
contactId	contact_id
timeStamp	created_at
Daasity: shop_id	__shop_id
Daasity: account_id	_account_id
Daasity: MD5(shopID + __shop_id)	__sync_key
Daasity: timestamp when loaded into DB	__synced_at