Lightspeed Totals a Gift Card Sale at Zero, Correctly

Before you start

Query your Lightspeed Retail data with AI against a replicated data warehouse and the first number you ask for, last month's takings, comes back short by every gift card you sold. This post assumes you already have Lightspeed Retail (R-Series) landing through Fivetran's managed connector, and that you can read the SALE, SALE_LINE and SALE_PAYMENT tables.

Name the product before you name the connector. Lightspeed Commerce sells R-Series and X-Series as separate products with separate APIs, and "Lightspeed Retail" means R-Series to Fivetran and X-Series to Airbyte. The two land schemas that share almost no table names. Everything here is R-Series, the product whose API lives at api.lightspeedapp.com/API/V3/.

The question

A shop owner asks what the business took last month. They expect the answer to match the till, and that's a reasonable thing to expect.

What breaks

Point an agent at the replicated schema and ask it. It finds a table called SALE, a column called total, and writes this:

-- The query the column name invites.
select date_trunc('month', complete_time) as month,
       sum(total)                         as revenue
from sale
group by 1
order by 1;

That query runs and returns a number. The number is missing every gift card the shop sold, and nothing will tell you so.

Lightspeed publishes the proof itself. Its Gift Cards tutorial walks through a customer buying a hundred-dollar gift card, and prints the response the API gives back. Here is the sale that records it:

"completed": "true", "voided": "false",
"calcSubtotal": "0", "calcTotal": "0", "calcPayments": "0",
"calcTips": "0", "total": "0",
"totalDue": "100", "displayableTotal": "100", "balance": "0"

A hundred dollars went into the till. Six of those columns read zero, including the one called total and the one called calcPayments. Two carry the money, and one of them is named for display.

The line item is no better:

"unitQuantity": "1", "unitPrice": "0", "calcTotal": "0", "itemID": "0",
"Note": { "note": "Gift Card Purchase 231234000000" }

So a revenue-by-item query misses it too, because the price is zero and the item identifier is zero. A count of sales finds the row, so the day gains a transaction worth nothing, which drags average basket size down as well. The only trace at line level is free text in a note.

Every value is quoted from the example response in Lightspeed's own Gift Cards tutorial. The two payment legs cancel, which is why calcPayments joins the zeroes.

Why it breaks

Lightspeed already decided a gift card isn't revenue

This is the part that turns a confusing schema into a defensible one. Lightspeed didn't lose the hundred dollars. It classified them, and it wrote the classification down in two places.

Its own accounting integration says so in the recommended chart-of-accounts mapping, in a list that sends almost everything else to Income. From Mapping your data in Lightspeed Accounting:

Sales: Income, Revenue, Sales

Discounts: Income, Revenue, Sales

Sales tax: Liability

Gift cards sold: Liability

Payments: Current, Current Asset

Tips: Liability

And its reporting documentation says the same thing from the other side. From Using sales reports:

"The Lines reports also lists gift card numbers that you've activated or refilled and work order numbers that you've completed. However, although they're listed for your reference, neither of them affect the calculations in the report."

A gift card sold is money you owe the customer, not money you earned. Revenue arrives later, when they spend it. So total = 0 is the accounting-correct answer, Lightspeed chose it deliberately, and the point-of-sale enforces it in every report.

Replication copies the columns and leaves the chart of accounts behind.

The two payment legs cancel

calcPayments is the column that surprises people, because its name promises exactly what a shop owner wants. Lightspeed documents it as "The total of all SalePayment amounts", which sounds like money collected.

A gift card purchase writes two payment legs on one sale. The tutorial's first call posts a SalePayment of -100 against the gift card's credit account, and the second completes the sale with a SalePayment of 100 in cash. Add them and you get zero. The column is doing precisely what it says, and what it says isn't "cash through the till".

That also makes SALE_PAYMENT a fan-out hazard. One sale, two rows, so any sale-level money column aggregated across that join double-counts.

The four money columns that aren't the four you'd guess

SALE carries nineteen monetary columns. Four of them decide this question, and Lightspeed defines each one on the Sale endpoint reference:

total: "The Sale total: calcSubtotal minus calcDiscount plus calcTax1 plus calcTax2."

calcPayments: "The total of all SalePayment amounts."

totalDue: "Total minus (CreditAccount and Gift Card deposits(-) or withdraws(+)) minus (credit card refunds)."

displayableTotal: "Represents the total + gift card deposits (a gift card's cost is not computed in total when it is created)."

Read the definition of total and the reason becomes mechanical. A gift card contributes nothing to the subtotal, nothing to the discount, and nothing to either tax, so it contributes nothing to total. The parenthetical in the last line is the whole post, and it's sitting in the vendor's own field reference.

The reader who notices the gap has to reach for a column whose name says it's for display. The reader who doesn't notice ships a revenue number that's short by every gift card sold.

Three more things the schema won't tell you

Voided, archived and incomplete sales share the table. completed, voided and archived are booleans on the row, and no status column exists. Lightspeed states that voided sales "do not count towards totals, or payments received", and leaves the row in place. An unfiltered sum counts sales that never happened.

A refund is a negative quantity on a positive price. The SaleLine reference is explicit that unitPrice "Can be negative for rebates or other uses, but not for refunds. Refunds are handled through negative unitQuantity." So sum(unit_price) counts a refund at full price, and only sum(unit_price * unit_quantity) nets.

An absent foreign key is spelled zero. The gift card line above carries itemID: "0", and the sale carries customerID: "0", discountID: "0", quoteID: "0" and shipToID: "0". The source is a REST API, so no relational constraint survives replication and nothing in the landed schema says zero is a sentinel. A left join on item_id matches nothing, where item_id is null never fires, and count(distinct customer_id) counts walk-in trade as one customer named zero.

Lightspeed answers this question inside Lightspeed

Worth saying plainly, because it bounds the claim. Lightspeed shipped conversational assistants over the merchant's own data in January 2026. From the company's announcement:

"Lightspeed AI introduces new conversational assistants within Lightspeed Retail and Lightspeed Restaurant. These assistants help merchants ask questions, get answers quickly and make smarter decisions without navigating complex dashboards or reports."

If Lightspeed is your only system, that's a better answer than anything here, and it's better for a specific reason. Lightspeed AI answers inside the product, where total already means what Lightspeed's reports mean. It inherits the gift card boundary correctly and for free.

The question it can't answer is the one that leaves Lightspeed. Till takings joined to a payroll system. To an accounting ledger. To an ecommerce channel on another platform, or to a second point of sale. Or across several Lightspeed accounts a multi-shop operator consolidates by hand. That's the question this post is about, and the warehouse is where it gets asked. The gap belongs to replication rather than to Lightspeed.

The fix

The declaration does two things. It keeps deferred revenue out of revenue, and it stops the word takings resolving to a column that nets to zero.

# semantic_model/lightspeed_r_series/metrics.yaml
# Column names follow Lightspeed's documented API field names in snake case.
# Confirm them against your own replica with query 0 below: the connector's
# own release notes show two casings in one sentence.

relationships:
  - name: sale_payments_to_sales
    from_table: sale_payment
    from_column: sale_id
    to_table: sale
    to_column: sale_id
    cardinality: many_to_one
    # A gift card purchase lands two payment legs on one sale, -100 and +100,
    # so this join fans. Never aggregate a Sale money column across it.
    # developers.lightspeedhq.com/retail/tutorials/gift-cards/
    reviewed: false

  - name: sale_lines_to_items
    from_table: sale_line
    from_column: item_id
    to_table: item
    to_column: item_id
    cardinality: many_to_one
    # item_id = 0 means "no item", not NULL. A gift card line carries 0.
    # Keep this a left join and filter item_id > 0 where a real item is meant.
    reviewed: false

metrics:
  - name: recognised_revenue
    description: >
      Revenue as Lightspeed itself recognises it: subtotal minus discount plus
      both taxes. Excludes gift cards sold, which Lightspeed Accounting maps to
      a Liability account. Completed, unvoided, unarchived sales only.
    calculation: SUM(sale.total)
    source_tables: [sale]
    primary_table: sale
    default_filters:
      - sale.completed = true
      - sale.voided = false
      - sale.archived = false
    reviewed: false

  - name: gift_cards_sold
    description: >
      Deferred revenue, not revenue. Lightspeed documents displayableTotal as
      "the total + gift card deposits (a gift card's cost is not computed in
      total when it is created)", so the difference isolates the deposit.
      Owed to the customer until redeemed; never add this to revenue.
    calculation: SUM(sale.displayable_total - sale.total)
    source_tables: [sale]
    primary_table: sale
    default_filters:
      - sale.completed = true
      - sale.voided = false
      - sale.archived = false
    reviewed: false

excluded_columns:
  # calcPayments is "The total of all SalePayment amounts". On a gift card sale
  # the legs are -100 and +100, so it reads zero for a sale that collected
  # money. It is a real column and a correct one; it leaves the revenue subject
  # area so that "takings" cannot resolve to it by name.
  - table: sale
    column: calc_payments
    reason: >
      Nets to zero on gift card sales. Reads like cash collected, is not.
      See developers.lightspeedhq.com/retail/endpoints/Sale/

reviewed: false on every block is the honest state. These are derived from vendor documentation and signed off by nobody who has run them against a replica.

The declaration doesn't change a single stored value. It changes which column the word takings is allowed to reach.

Reproduce it yourself

Query 0 runs first, and everything else depends on it. Two things are unsettled and this query settles both: your real column names, and whether displayable_total is replicated at all. Lightspeed may compute it on read, no Fivetran dbt package exists for this app, and the connector's schema ERD is a JavaScript shell with no table names in it.

-- What the Sale money columns are actually called in your replica,
-- and whether displayable_total is one of them.
select table_name, column_name, data_type
from information_schema.columns
where lower(table_name) in ('sale', 'sale_line', 'sale_payment')
order by table_name, column_name;

Substitute the names it returns into everything below. If displayable_total is absent, skip to the fallback at the end of this section.

The query that measures your own gap. Run this before you change anything, because it tells you the size of the problem on your data rather than on anyone else's:

-- How much of what you took has been missing from your revenue number.
select date_trunc('month', complete_time)   as month,
       sum(total)                           as what_your_report_says,
       sum(displayable_total)               as what_went_into_the_till,
       sum(displayable_total) - sum(total)  as gift_cards_you_did_not_count,
       count(*) filter (where displayable_total <> total) as sales_affected
from sale
where completed = true and voided = false and archived = false
group by 1
order by 1;

Three caveats. displayable_total minus total isolates gift card deposits only where a deposit is the sole difference between the two columns, which is what the Sale reference states and what you should confirm on your own rows. count(*) filter (where ...) is PostgreSQL and Snowflake syntax, so write a case expression on another engine. And complete_time is the right date column rather than create_time or time_stamp, because Lightspeed documents completeTime as "The date/time that the sale was completed. Can be changed.", which is its own small warning.

The query to use instead. It reports the two quantities separately rather than blending them:

-- Recognised revenue and deferred revenue, kept apart.
select date_trunc('month', complete_time)      as month,
       sum(total)                              as recognised_revenue,
       sum(displayable_total - total)          as gift_cards_sold,
       sum(displayable_total)                  as cash_through_the_till
from sale
where completed = true and voided = false and archived = false
group by 1
order by 1;

The fallback, if query 0 says displayable_total isn't there. Reconstruct the deposit from the payment legs, which do land, and watch the fan-out:

-- Gift card deposits from the payment legs, one row per sale first.
with legs as (
  select sale_id,
         sum(case when credit_account_id > 0 and amount < 0
                  then -amount else 0 end) as gift_card_deposit
  from sale_payment
  group by sale_id
)
select date_trunc('month', s.complete_time)        as month,
       sum(s.total)                                as recognised_revenue,
       sum(coalesce(l.gift_card_deposit, 0))       as gift_cards_sold
from sale s
left join legs l on l.sale_id = s.sale_id
where s.completed = true and s.voided = false and s.archived = false
group by 1
order by 1;

The legs subquery collapses the payment rows to one row per sale before the join, which is what keeps the fan-out from double-counting total. Note credit_account_id > 0 rather than is not null, because zero is how this schema spells absent.

If you don't have Lightspeed replicated yet, start a Lightspeed Retail trial, which the page describes as a "14-day free trial" with "Full access to all features" and "No credit card needed". Ring up one gift card and one ordinary sale, then read both back through the API and compare total with displayableTotal.

Two limits to know first. Fourteen days is the whole clock, so treat the trial as the way to see the API behaviour rather than as a warehouse route. And no R-Series developer sandbox exists: nothing in Lightspeed's developer documentation publishes a sandbox host, which is the opposite of Square.

One connector note from Fivetran's release log, which bites anyone using table blocking to keep a sync small: "If you want to sync a child table, you must include the parent table in the sync."

What this looks like in Agami

Agami is a semantic model that sits between an agent and your warehouse, and on this problem it does four specific things.

• Deferred revenue is its own metric and can't be added to revenue. gift_cards_sold exists, is named, and is separate from recognised_revenue. Restoring the boundary Lightspeed Accounting already drew is the point, and it's a declaration rather than a convention somebody has to remember.
• A column can leave the revenue vocabulary without leaving the warehouse. calc_payments stays where it is and stops being reachable by the word takings. The exclusion is a rule in the model rather than a line in a prompt, which is what makes it auditable: a persuasively worded question can't talk its way past it.
• A fanning join is declared as one. sale_payment to sale is recorded as many-to-one, so an aggregate of a sale-level money column across that join is caught before the SQL runs, named, and handed back to you. Whether the fan is a bug depends on the question, so Agami says which join inflated which number and lets you decide.
• An unsigned metric answers and says it's unsigned. Every block above carries reviewed: false, because nobody has run it against your replica. The answer arrives with that warning attached instead of arriving silently.

All of that lands in the assistant your team already opens, so the shop owner who asked the original question gets the governed answer in the same conversation rather than in a separate tool.

What we can't claim. We haven't measured a Lightspeed estate, so no figure in this post says what share of any retailer's takings are gift cards, in December or in any other month, and the gap query above exists precisely because that number is yours and not ours. We also can't tell you whether your connector lands displayableTotal or whether Lightspeed computes it on read, because the ERD isn't readable and no dbt package exists, which is why query 0 comes first and the fallback is in the post. And the exact landed column names are yours to discover: tables arrive upper snake case, and one release-note sentence gives secondary_email_address beside secondary_email_useType in the same table.

Frequently asked questions

Is total reading zero a bug?

No, and treating it as one will get you corrected by an accountant. A gift card sold is money owed to the customer, so it's a liability rather than revenue, and Lightspeed Accounting maps "Gift cards sold" to a Liability account in the same list that maps Sales to Income. The column is right. The warehouse just didn't inherit the label that explains it.

Why not just use displayableTotal as the revenue number?

Because that blends deferred revenue back into revenue and breaks the thing Lightspeed got right. Report the two separately. displayableTotal answers "what went through the till", which is a cash-flow question and a legitimate one, and total answers "what did we earn".

Can't I find gift card sales from the line item instead?

Only from free text. The line carries unitPrice of zero and itemID of zero, and the single identifying mark is a note reading "Gift Card Purchase" followed by the card number. Parsing a note field to compute revenue is a worse foundation than a documented column.

Does any of this apply to Lightspeed Retail X-Series?

No. X-Series is a different product with a different API, a different schema and its own connector, and nothing here was checked against it. Confirm which series you have before running any query in this post.

What about redemptions, when the customer spends the gift card?

That's the other half of the liability and it isn't covered here. The deposit is a negative SalePayment against a credit account at purchase; a redemption moves in the opposite direction. Getting the full picture means declaring both legs, and this post declares the one that's silently missing from revenue.

References

• Lightspeed Retail Sale endpoint reference, for the nineteen monetary fields and the four definitions quoted above
• Lightspeed Retail SaleLine endpoint reference, for the refund rule and the line-level fields
• Gift Cards tutorial, which prints the complete example response quoted here
• Mapping your data in Lightspeed Accounting, for "Gift cards sold: Liability"
• Using sales reports, for gift card numbers not affecting report calculations
• Lightspeed Retail API introduction, for the OAuth2 and JSON basics
• Fivetran Lightspeed Retail connector documentation and its setup guide
• Lightspeed AI announcement, January 2026
• Square Puts Tips on Their Own Line and Replication Folds Them Back In, the same number broken in the opposite direction: Square's total is inflated by money owed to staff, Lightspeed's is deflated by money owed to the customer
• Ten Date Columns on a Toast Order, and Only One Knows When Friday Ended, the sibling question of when a sale belongs to a day
• agami-core on GitHub, the open-source semantic model and the metric and exclusion blocks shown above

Let the model know what a gift card is

Agami is the trust layer between your AI assistant and your warehouse. It keeps gift_cards_sold as deferred revenue that can never be added to recognised_revenue, stops the word takings resolving to calc_payments, and declares the sale_payment fan so one gift card can't double a day.

Start a free trial or talk to us →