Syncing Shopify orders into your own app: webhooks, bulk exports and role-based revenue views

create schema shop;

create table shop.orders (
  id                 bigint primary key,           -- Shopify order id
  name               text not null,                -- e.g. #1042
  created_at         timestamptz not null,
  financial_status   text,
  fulfillment_status text,
  currency           char(3) not null,
  subtotal           numeric(12, 2) not null,      -- after discounts, edits and refunds; before shipping and tax
  discounts          numeric(12, 2) not null default 0,
  tax                numeric(12, 2) not null default 0,
  total              numeric(12, 2) not null,
  refunded           numeric(12, 2) not null default 0,
  cancelled_at       timestamptz,
  raw                jsonb not null,
  synced_at          timestamptz not null default now()
);
create index on shop.orders (created_at);

create table shop.webhook_deliveries (
  webhook_id  text primary key,
  topic       text not null,
  received_at timestamptz not null default now()
);

import crypto from 'node:crypto';

export function isValidShopifyWebhook(rawBody: Buffer, hmacHeader: string | undefined, secret: string): boolean {
  if (!hmacHeader) return false;
  const expected = crypto.createHmac('sha256', secret).update(rawBody).digest();
  const received = Buffer.from(hmacHeader, 'base64');
  // timingSafeEqual throws on different lengths, so check that first
  return received.length === expected.length && crypto.timingSafeEqual(received, expected);
}

import express from 'express';
import { Pool } from 'pg';
import { isValidShopifyWebhook } from './verify.js';

const db = new Pool({ connectionString: process.env.DATABASE_URL });
const app = express();

type ShopifyOrder = {
  id: number; name: string; created_at: string; currency: string;
  financial_status: string | null; fulfillment_status: string | null; cancelled_at: string | null;
  current_subtotal_price: string; current_total_discounts: string;
  current_total_tax: string; current_total_price: string;
  refunds?: { transactions: { kind: string; amount: string }[] }[];
};

app.post('/webhooks/shopify', express.raw({ type: 'application/json' }), async (req, res) => {
  if (!isValidShopifyWebhook(req.body, req.get('X-Shopify-Hmac-SHA256'), process.env.SHOPIFY_CLIENT_SECRET!)) {
    return res.sendStatus(401);
  }

  const webhookId = req.get('X-Shopify-Webhook-Id')!;
  const topic = req.get('X-Shopify-Topic')!;
  const seen = await db.query(
    'insert into shop.webhook_deliveries (webhook_id, topic) values ($1, $2) on conflict do nothing',
    [webhookId, topic]
  );
  if (seen.rowCount === 0) return res.sendStatus(200); // duplicate delivery

  const payload = JSON.parse(req.body.toString('utf8'));
  // refunds/create sends a refund, not an order; the next orders/updated carries the new totals
  if (topic.startsWith('orders/')) await upsertOrder(payload as ShopifyOrder);

  res.sendStatus(200);
});

async function upsertOrder(o: ShopifyOrder) {
  const refunded = (o.refunds ?? [])
    .flatMap(r => r.transactions)
    .filter(t => t.kind === 'refund')
    .reduce((sum, t) => sum + Number(t.amount), 0);

  await db.query(
    `insert into shop.orders (id, name, created_at, financial_status, fulfillment_status, currency,
                              subtotal, discounts, tax, total, refunded, cancelled_at, raw, synced_at)
     values ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13, now())
     on conflict (id) do update set
       financial_status = excluded.financial_status, fulfillment_status = excluded.fulfillment_status,
       subtotal = excluded.subtotal, discounts = excluded.discounts, tax = excluded.tax,
       total = excluded.total, refunded = excluded.refunded, cancelled_at = excluded.cancelled_at,
       raw = excluded.raw, synced_at = now()`,
    [o.id, o.name, o.created_at, o.financial_status, o.fulfillment_status, o.currency,
     o.current_subtotal_price, o.current_total_discounts, o.current_total_tax,
     o.current_total_price, refunded.toFixed(2), o.cancelled_at, JSON.stringify(o)]
  );
}

app.listen(3000);

mutation {
  bulkOperationRunQuery(
    query: """
    {
      orders(query: "created_at:>=2024-01-01") {
        edges {
          node {
            id
            name
            createdAt
            cancelledAt
            displayFinancialStatus
            displayFulfillmentStatus
            currentSubtotalPriceSet { shopMoney { amount currencyCode } }
            currentTotalDiscountsSet { shopMoney { amount } }
            currentTotalTaxSet { shopMoney { amount } }
            currentTotalPriceSet { shopMoney { amount } }
            totalRefundedSet { shopMoney { amount } }
          }
        }
      }
    }
    """
  ) {
    bulkOperation { id status }
    userErrors { field message }
  }
}

create view shop.daily_sales as
select (created_at at time zone 'America/New_York')::date as day,   -- your store's time zone
       count(*)                       as orders,
       sum(discounts)                 as discounts,
       sum(refunded)                  as refunds,
       sum(subtotal)                  as net_sales,     -- after discounts and refunds, before shipping and tax
       sum(tax)                       as tax
  from shop.orders
 where cancelled_at is null
 group by 1;

create view shop.open_orders as
select id, name, created_at, fulfillment_status
  from shop.orders
 where cancelled_at is null
   and coalesce(fulfillment_status, 'unfulfilled') <> 'fulfilled';

create role finance nologin;
create role operations nologin;
create role marketing nologin;
grant usage on schema shop to finance, operations, marketing;

grant select on all tables in schema shop to finance;
grant select on shop.open_orders to operations;
grant select (id, name, created_at, fulfillment_status) on shop.orders to operations;
grant select on shop.daily_sales to marketing;

-- each app user logs in with a role that inherits one of these:
-- create role maria login password '...' in role operations;