Kinship Barbershop: Payroll ETL

Retail / Payroll Automation

Kinship Barbershop: Real-Time Payroll ETL

Square API Apps Script Looker Studio Idempotent Sync

The Mission A Brooklyn barbershop's payroll logic outgrew what Square could handle. I built a custom ETL pipeline that pulls Square data, applies tiered commission rules, and loads it into a real-time Looker Studio dashboard the owner actually uses.

System Architecture

Key Engineering Challenges

Missing Staff Attribution: Square's API often drops staff IDs on Quick Charge transactions. Built a heuristic fallback engine findStaffByCustomerAppointment_ that correlates timestamps with booking history to recover lost attribution.
Complex Commission Logic: Tiered logic engine handling different rates for Services vs. Products, 50/50 processing fee splits, and tax liability isolation based on owner/contractor status.
Incremental Sync: Cursor-based pagination (SYNC_CURSOR_KEY) fetches only modified transactions, respecting API rate limits.

Code Snippet

syncSquareToSheet.js

// From syncSquareToSheet()
// Logic: Batch processing with Idempotency & Staff Recovery

function syncSquareToSheet() {
  const props = PropertiesService.getScriptProperties();
  const nowIso = new Date().toISOString();
  // Resume from last sync cursor
  const beginIso = props.getProperty(SYNC_CURSOR_KEY) || isoDaysAgo_(30);

  const payments = fetchPaymentsUpdatedSince_(beginIso, nowIso);
  
  // Batch Fetch related Orders to minimize API calls (N+1 problem)
  const orderIds = unique_(payments.map(p => p.order_id));
  const ordersById = batchRetrieveOrders_(orderIds);
  
  // Heuristic: Pre-fetch booking info to resolve missing staff
  const bookingInfo = prefetchBookingInfo_(ordersById);

  payments.forEach(p => {
    const row = buildProcessedRow_(
        p, ordersById[p.order_id], catalogInfo, staffById, 
        customersById, commissionData, bookingInfo
    );
    if (paymentRowIndex[p.id]) {
      updates.push({row: paymentRowIndex[p.id], values: row});
    } else {
      appends.push(row);
    }
  });

  props.setProperty(SYNC_CURSOR_KEY, nowIso);
}