S9 — a BI feed: sales, lines, payments and returns into a warehouse file, picking up where it stopped
A small program that copies the shop's sales, lines, payments and returns into a data warehouse file, and picks up where it stopped. This sample is for a retailer's analytics team that wants the shop's sales outside the shop without copying everything every night.
When to use it: reports or dashboards outside the shop need the shop's sales, kept up to date and checked against the till's own end-of-day figures.
It runs outside the shop, read-only on the business, over the event feed (recipes/event_feed.md); written up as a recipe: recipes/bi_feed.md.
The files
| File | What |
|---|---|
bi_feed.py |
run (the feed, one transaction per invoice together with the seen-set) · backfill (by the watermark per record type) · rebuild (a full refresh into a fresh file) · export (CSV) · control (the day's totals beside the POS Closing Entry) |
tests/test_s9.py |
12 laptop tests against an in-memory shop and feed |
How to run it
BI_KEY=… BI_SECRET=… python3 samples/s9_bi_feed/bi_feed.py control --base https://shop.example --warehouse state/bi.sqlite --since "2026-10-04 00:00:00"
run, as often as you like, picks up what is new from the event feed. Each sale is written together with the note that it was handled, so a crash leaves it copied once or not at all.controlchecks each day's totals in the warehouse against the shop's own end-of-day figure (the POS Closing Entry), and names a closing that disagrees.backfillcatches up if the feed was off or the file lost its last sales.rebuildstarts a fresh file.exportwrites the warehouse as CSV.
The result is a warehouse file and CSV, not a screen.
Before you copy it
- The feed key reaches every application's queue. A key that sees only one application's events has not shipped yet, so this sample does not use one.
Technical notes
Why it is built this way
Why this way (R6). A nightly full dump — rejected: slow at volume and it cannot say what changed. Poll by modified only — rejected as the main path: a record committed late behind the cursor is skipped; kept as backfill, the second way in. The feed + one SQLite file for both the warehouse and the seen-set (picked, ~250 lines): one transaction, so a kill leaves each invoice extracted once or not at all.
Not used, because it has not shipped: a feed key scoped to one application (P-FEED) — the grant today reaches every application's queue (recipes/event_feed.md §3).
What was tested
| Where | What |
|---|---|
tests/test_s9.py |
12 laptop tests against an in-memory shop and feed |
| our test bench's own run (not in the kit) | the bench tests |
What our test bench's run printed (2026-10-07, 23 of 23 checks; trimmed). The result is a warehouse file and CSV, not a screen of ours:
PASS B1 each invoice's header total, every line and every payment equal the shop's own record
PASS B1 exporting again gives the same bytes
PASS B2 the return is a return of that sale, with negative lines
PASS B3 all four are in the warehouse once; their line rows equal the shop's (none lost, none doubled)
PASS B4 after the cancellation the warehouse says cancelled, and the day's revenue excludes it
PASS B5 every POS Closing Entry since the start agrees with the warehouse's total for the invoices it lists (and none is missing)
PASS B5 canary: a total that is one unit off makes the control RED and names the closing
PASS B6 a file missing its last three invoices, with its watermark behind them, is healed by one backfill to equal the full one