Reconciliation

Step 1: Pull Channel Data

For each active channel, get yesterday's order totals:

Shopify:

• Query Shopify Orders API for orders created yesterday
• Sum: gross revenue, discounts, shipping collected, taxes collected, refunds
• Net revenue = gross - discounts - refunds

Etsy (when live):

• Query Etsy Receipts API for yesterday's transactions
• Account for Etsy fees (6.5% transaction + 3% payment processing)

Amazon (when live):

• Query Amazon Settlement Report or SP-API for yesterday's orders
• Account for referral fees (15%) + FBA fees

Step 2: Compare Against Supabase

Query orders table for the same date range:

SELECT channel,
       COUNT(*) as order_count,
       SUM(total_price) as gross_revenue,
       SUM(total_discounts) as discounts,
       SUM(total_shipping) as shipping_collected,
       SUM(total_tax) as tax_collected
FROM orders
WHERE DATE(created_at) = DATE('yesterday')
  AND financial_status != 'voided'
GROUP BY channel;

Step 3: Identify Discrepancies

Compare channel source data vs. Supabase records:

Step 4: Generate Report

Daily reconciliation report format:

DAILY RECONCILIATION — 2026-03-20
═══════════════════════════════════

SHOPIFY
  Orders: 12 (Supabase: 12) ✓
  Gross Revenue: $1,847.50 (Supabase: $1,847.50) ✓
  Refunds: $0.00 ✓
  Net Revenue: $1,847.50 ✓

ETSY
  Orders: 3 (Supabase: 3) ✓
  Gross Revenue: $245.00 (Supabase: $245.00) ✓

DISCREPANCIES: None

DAILY TOTAL: $2,092.50 across 15 orders

Weekly QuickBooks Reconciliation

Compare Supabase weekly totals against QuickBooks:

1. Export QuickBooks revenue by category for the week
2. Query Supabase for the same period grouped by channel
3. Compare totals — acceptable variance is <$25 or <1%
4. Common discrepancy sources:
   • Timing differences (order placed end of day, recorded next day)
   • Refunds processed in different periods
   • Manual QuickBooks entries not reflected in Supabase
   • Foreign currency conversion differences

Monthly P&L Reconciliation

The most comprehensive check. Produces a full P&L comparison:

1. Revenue: Channel revenue vs. QuickBooks income accounts
2. COGS: Supabase products.cogs_confirmed × units sold vs. QuickBooks COGS entries
3. Fees: Calculated channel fees vs. actual platform invoices
4. Shipping: Shippo actual costs vs. shipping collected from customers
5. Operating Expenses: QuickBooks expense categories vs. budgeted amounts

Output feeds into the channel_profitability_monthly materialized view.

Idempotency

Reconciliation is a read-heavy, write-light operation, but when it does write (logging discrepancies, updating sync status):

• Check skill_invocations for a reconciliation run on the same date before running
• Use ON CONFLICT for any upsert operations
• Never modify order or financial data — only flag discrepancies for human review

Phase 1 Behavior

• Daily reconciliation runs automatically and logs results
• Discrepancies >$5 appear in ceo's approval queue
• No automated corrections — all fixes require human action
• Monthly P&L requires ceo's explicit review and sign-off

QuickBooks Integration Note

QuickBooks API access is not yet configured. Until it is:

• Weekly and monthly reconciliation steps that require QuickBooks are skipped
• Log a reminder that QuickBooks integration is pending
• Daily channel-vs-Supabase reconciliation still runs (doesn't need QuickBooks)

Output

{
  "date": "2026-03-20",
  "type": "daily",
  "channels": {
    "shopify": {"orders": 12, "gross_revenue": 1847.50, "status": "matched"},
    "etsy": {"orders": 3, "gross_revenue": 245.00, "status": "matched"}
  },
  "discrepancies": [],
  "total_revenue": 2092.50,
  "total_orders": 15,
  "quickbooks_status": "not_connected"
}