Case study / CSW

Live reporting on a direct-sales platform

A sync service that replicates customers, orders, autoships, inventory and commissions from the Pillars platform into Postgres, with Metabase reporting on top.

PipelinesDashboards

Services: Data, Engineering
Stack: Node.js, Fastify, pg-boss, PostgreSQL, Metabase
Scope: 9 entities, 66 reporting tables

At a glance

Problem: CSW’s business data lives in Pillars, whose rate-limited API returns nested records that a BI tool can’t join.
What we built: One Node.js service that writes webhooks to Postgres as they arrive and sweeps everything else on a schedule.
Result: Reporting runs on a 66-table Postgres replica, so a new question is a plain SQL join in Metabase.

Reporting tables in Postgres: 66
Entities on one schedule: 9
Webhook topics written in real time: 3

The challenge

Nested, rate-limited data that a BI tool can’t join

Everything CSW reports on lives in Pillars, and its API returns that data in a shape no reporting tool can use directly.

CSW runs its direct-sales business on Pillars, a hosted platform that holds customers, orders, autoships, the sponsor tree and every commission period.

Its API returns nested records, with line items inside orders and volumes inside line items, in camelCase from REST and PascalCase from webhooks. It pages by offset with no cursor or total count and rate-limits every caller. None of that can be joined in a BI tool as it arrives.

One order webhook becomes rows in five tables, written in one transaction.

Our approach

Webhooks for what changes, scheduled sweeps for the rest

We built one Node.js service on Fastify and pg-boss, with Postgres as its only dependency.

Customers, orders and sponsor-tree changes arrive by webhook and are written inside the request.

Scheduled sweeps run in dependency order and catch anything missed, one pg-boss job per page, so a failure retries on its own. Commission periods are tracked one by one: a locked period is synced once more, sealed, and never fetched again.

Each webhook is checked, validated, transformed and upserted; a redelivered message is skipped.

The overnight schedule syncs each entity in dependency order, then seals the commission period.

The outcome

Reports that never touch the Pillars API

Metabase Cloud reads a Postgres replica of 66 tables instead of the Pillars API, so reports never spend the API’s rate limit.

Line items, payments, addresses and price lists became their own tables, so new questions are plain SQL joins.

Resets take a snapshot first and pause webhooks with a 503, which Pillars retries for two days, so a reload never loses an event.

A Metabase SQL question joining customers to their enrollers, shown as a bar chart.

Next case study

One profile, a tailored resume for every job

Resalad

Have a project in mind?

Tell us what you’re working on, and we’ll follow up to find a time to talk.

Start a project See our work