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)
```json
{
  "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