client-projects/clients/spice-nvybe/research/api-data-sources.md

Spice N'Vybe BI Dashboard — API Data Source Research

Context: Restaurant with 2 locations (Oakland Park flagship + Fort Lauderdale Cloud Kitchen). Need a BI Dashboard for Sales, Net Profit, and Costs.

---

1. QuickBooks Online (Accounting)

Auth Method

• OAuth 2.0 (Authorization Code flow exclusively)
• Two primary scopes:
  • com.intuit.quickbooks.accounting — full read/write to accounting data
  • com.intuit.quickbooks.payment — QuickBooks Payments processing
• Access tokens: expire in 1 hour
• Refresh tokens: rotate every 24-26 hours, max lifetime 5 years. Critical: you must always store and use the latest refresh token — reusing a rotated one revokes the entire authorization chain.

Base URLs

• Production: https://quickbooks.api.intuit.com/v3/company/{realmId}/
• Sandbox: https://sandbox-quickbooks.api.intuit.com/v3/company/{realmId}/

Available Endpoints / Entities

Financial Reports (Reports API) — most relevant for BI:

Endpoint	What it returns	BI Relevance
ProfitAndLoss	Income, COGS, Expenses, Net Income	Core for Net Profit calculation
BalanceSheet	Assets, Liabilities, Equity snapshot	Treasury health
CashFlow	Operating/Investing/Financing cash flows	Cash position
AgedReceivables	Outstanding customer invoices	AR tracking
AgedPayables	Outstanding bills/vendor payments	AP tracking
TrialBalance	Full chart of accounts summary	Audit/completeness

Transactional Entities (CRUD via Query API):

• Invoice — customer invoices, line items, amounts, dates
• Customer — customer profiles
• Payment — received payments
• Bill — vendor bills
• Purchase — purchases (check, credit card, cash)
• PurchaseOrder — vendor POs
• Vendor — vendor profiles
• Account — chart of accounts
• JournalEntry — manual debit/credit entries
• Deposit — bank deposits
• Transfer — fund transfers between accounts

Query syntax: SQL-like — SELECT * FROM Invoice WHERE TxnDate > '2026-01-01'

Sync Mechanism

• Change Data Capture (CDC) — returns only changed records since a timestamp. Use this instead of polling full datasets.
• Webhooks — event-driven notifications for entity changes.

Rate Limits

• 500 requests/minute per company (realmId)
• 10 concurrent requests per company
• Batch endpoint: 120 requests/minute per company

BI Relevance for Sales / Net Profit / Costs

• Net Profit → Pull ProfitAndLoss report (Income - COGS - Expenses = Net Income)
• Costs → Pull ProfitAndLoss for expense line items; Bill/Purchase entities for granular vendor spend
• Sales → Pull ProfitAndLoss revenue lines; but SpotOn is better for daily/weekly sales granularity
• AR/AP → AgedReceivables/AgedPayables reports

Recommendation: Pull QBO reports daily (or on-demand) for the P&L view. Use it as the source of truth for net profit, reconciling against SpotOn and Plaid data. The 1-hour access token and per-company rate limits mean you'll need a queued background worker.

---

2. Plaid (Banking)

Auth Method

• API Key-based (client_id + secret from Plaid Dashboard)
• Sent as PLAID-CLIENT-ID and PLAID-SECRET headers, or in request body
• User Linking Flow: Plaid Link widget generates an access_token scoped to the user's financial accounts at their institution
• All requests are POST with JSON bodies

Base URLs

• Sandbox: https://sandbox.plaid.com
• Production: https://production.plaid.com

Available Products / Data Categories

Product	Endpoints	Data Provided	BI Relevance
Transactions	/transactions/sync, /transactions/get, /transactions/recurring/get, /transactions/refresh	Up to 24 months of categorized transactions; merchant names, amounts, dates, categories (PFC v1 or v2 taxonomy), geolocation	Core for cash flow analysis, expense categorization
Balance	/accounts/balance/get	Real-time current & available balances	Working capital monitoring
Auth	/auth/get	Account & routing numbers	(Low BI relevance)
Identity	/identity/get	Account holder name, addresses, emails	(Low BI relevance)
Income	/credit/income/get	Income streams, paystub data	Revenue verification
Investments	/investments/transactions/get	Holdings and trades	(Low relevance unless they have investment accounts)
Liabilities	/liabilities/get	Credit cards, student loans, mortgages	Debt tracking
Enrich	Transaction category enrichment	Cleans raw bank descriptions with category metadata	Better expense categorization
Assets	/asset_report/create	Point-in-time financial snapshot	(Optional)

Transaction Data Shape (key fields)

{
  "transaction_id": "...",
  "account_id": "...",
  "amount": -45.50,
  "iso_currency_code": "USD",
  "date": "2026-06-15",
  "name": "US Foods Inc",
  "merchant_name": "US Foods",
  "payment_channel": "in store",
  "pending": false,
  "category": ["Food and Drink", "Restaurants"],
  "personal_finance_category": {
    "detailed": "FOOD_AND_DRINK_RESTAURANTS"
  },
  "location": {...}
}

Sync Mechanism

• /transactions/sync (preferred) — cursor-based incremental sync. Track next_cursor for paginated updates.
• /transactions/get — older full-fetch approach (deprecated in favor of sync)
• Webhooks: SYNC_UPDATES_AVAILABLE, INITIAL_UPDATE, HISTORICAL_UPDATE, DEFAULT_UPDATE, TRANSACTIONS_REMOVED
• Plaid checks for new transactions 1-4 times per day per institution

Rate Limits

• Not explicitly documented as fixed caps per minute; they use per-request pricing tiers
• RATE_LIMIT_EXCEEDED errors are a defined error type — implement retry with backoff
• Max 500 transactions per sync call page; max 730 days of history

Pricing

• Pay as You Go / Growth / Custom tiers
• Transactions is included in all tiers (one-time or per-request model)
• Free Sandbox (200 API calls per product for initial testing)

BI Relevance for Sales / Net Profit / Costs

• Expense categorization → Every bank transaction has a personal_finance_category (e.g., FOOD_AND_DRINK_RESTAURANTS, RENT_AND_UTILITIES, SUPPLIES). This gives you a real-time, categorized expense feed that's more granular than QBO.
• Cash flow → Stream daily transactions to detect revenue deposits versus vendor payments
• Reconciliation → Match bank transactions against QBO records for audit-tight financials
• Cost monitoring → Spot unusual vendor charges, rent payments, utility bills as they post

Recommendation: Use Plaid Transactions sync for daily cash-flow monitoring and as a cross-reference for costs. The personal finance category taxonomy (PFC v2) maps well to COGS/vendor/operating expense categories. Use recurring transactions endpoint for subscription/monthly cost tracking.

---

3. SpotOn (POS)

Auth Method

• API Key — simple x-api-key header in each request
• The POS Export API uses a per-location API key
• SpotOn also provides a separate Enterprise API (for enterprise/venue customers) with different endpoints

Base URL

• https://restaurantapi-qa.spoton.com/posexport/v1/ (QA environment)
• The API is location-centric — all routes are scoped to /locations/:locationId/

Available Endpoints

Transactional Endpoints

Endpoint	Path	Description	BI Relevance
Orders	GET /locations/:id/orders	Orders with checks, payments, menu items, modifiers, tips, guests	Core — daily sales data
Orders (single)	GET /locations/:id/orders/:orderId	Single order detail	Drill-down
Paid In/Outs	GET /locations/:id/paid-in-outs	Cash paid in/out transactions (tips paid out, petty cash, deposits)	Cash management

Reference Data Endpoints

Endpoint	Path	Description	BI Relevance
Menu Items	GET /locations/:id/menu-items	Menu items, prices, PLU codes, report categories	Item-level sales analysis
Modifiers	GET /locations/:id/modifiers	Modifier groups and options	Cost attribution
Employees	GET /locations/:id/employees	Employee profiles, roles	Labor attribution
Payment Options	GET /locations/:id/payment-options	Tender types (credit, cash, gift card)	Payment mix
Report Categories	GET /locations/:id/report-categories	Menu categorization groups	Category-level sales

Report Endpoints

Endpoint	Path	Description	BI Relevance
Labor Reports	GET /locations/:id/reports/labor	Time clock entries with regular/OT pay rates, tips	Core — labor costs
Labor (date range)	GET /locations/:id/reports/labor?startDateKey=&endDateKey=	Labor data across multiple business dates	Weekly/monthly labor

Key Order Data Shape

Orders contain nested objects:

• Order → totalAmount, balanceDueAmount, orderTypeName, tableNumber, createdAt, closedAt, deleted
• Check → gratuityAmount, totalAmount, paymentsAmount, balanceAmount
• Guest → name, items[], voidedItems[]
• MenuItem → menuItemId, name, quantity, unitPriceAmount, categoryName, modifierGroups[]
• Payment → amount, tipAmount, cardType, employeeId

Constraints

• Date range max: 26 hours (can't pull more than 26h in a single orders request)
• History: Only orders last updated in the last ~90 days
• Replication lag: API reads from a secondary DB; 5-minute minimum lag for updatedAtEnd parameter
• Orders retrieval patterns:
  • Daily: Pull last 26 hours of orders every 24 hours
  • Near-realtime: Poll every 5 minutes with 5-minute lag, plus a daily 26-hour catch-up
• Reference data: Pull menu-items, modifiers, employees once daily; fetch individual ones on-demand

Labor Report

• Source: SpotOn's Reporting Warehouse (ETL-delayed, not real-time)
• Returns per-employee, per-shift entries with:
  • regularSecondsWorked, regularPayRateAmount, regularPayAmount
  • overtimeSecondsWorked, overtimePayRateAmount, overtimePayAmount
  • totalPayAmount, declaredCashTipsAmount
  • unpaidBreakSeconds
• All historical data available (not limited to 90 days like orders)

Additional Note: Unofficial API

SpotOn's official Export API is limited to reads. For write operations (menu updates, 86 management, reservation creation) or more comprehensive data coverage, Supergood offers an unofficial but production-tested API that:

• Handles MFA/session management
• Provides near-real-time order/payment/menu/reservation/labor data
• Supports webhooks for async events
• Required for two-way integration (QuickBooks sync, menu management)

BI Relevance for Sales / Net Profit / Costs

• Sales (by location, by item, by category) → Orders endpoint with menu items and report categories
• Labor costs → Labor Reports endpoint — regular pay, overtime, tips per employee per day
• Payment mix → Payment options on each check (credit vs cash vs gift card)
• Void/waste tracking → voided items in order data
• Period comparison → Pull orders by closedAt ranges for daily/weekly/monthly comparisons
• Costs → SpotOn doesn't track vendor costs directly (that's QBO's domain), but paid-in-outs capture cash expenses

Recommendation: SpotOn is your source of truth for Sales — daily sales by item/category/location. Labor data via the Warehouse reports gives you labor cost % against sales. The 26-hour window constraint means you'll need a continuous polling pattern (every 5 min during operating hours, daily catch-up).

---

Cross-Source Integration Strategy for Spice N'Vybe BI Dashboard

Metric	Primary Source	Cross-Reference	Sync Cadence
Sales (daily, by location)	SpotOn Orders	Plaid deposits (revenue matching)	Every 5 min (realtime), daily catch-up
Sales (by menu item)	SpotOn Orders (menuItems)	—	Daily
COGS	QuickBooks P&L	Plaid vendor payments	Daily
Labor Costs	SpotOn Labor Reports	QuickBooks Payroll expenses	Daily
Operating Expenses	QuickBooks P&L	Plaid transaction categories	Daily
Net Profit	QuickBooks P&L	Aggregated from Sales - Costs	Daily (on-demand)
Cash Flow	Plaid Transactions	QuickBooks CashFlow report	Daily
AP/AR	QuickBooks AgedPayables / AgedReceivables	Plaid for check clearing	Daily

Data Pipeline Architecture Suggestion

SpotOn ──► (ETL: every 5min) ──► Staging DB (orders, labor, menu data)
Plaid  ──► (ETL: daily sync) ──► Staging DB (transactions, balances)  
QBO    ──► (ETL: daily sync) ──► Staging DB (reports, invoices, bills)
                                          │
                                          ▼
                                  BI Dashboard DB
                                   (aggregated views)
                                          │
                                          ▼
                                 Analytics Queries
                          (Sales by item, Profit by location,
                           Cost trends, Cash position)

Key Technical Considerations

1. QBO OAuth token rotation requires a background worker that refreshes tokens proactively (tokens expire in 1h, refresh tokens rotate every 24-26h)
2. SpotOn's 26-hour window means you can't backfill deep history from orders — start the pipeline ASAP
3. Plaid's recurring transactions endpoint (/transactions/recurring/get) is excellent for tracking predictable costs (rent, utilities, subscriptions)
4. All three APIs support webhooks — consider event-driven architecture instead of polling where possible