Files

T

jerome 3d344816cf feat: add Spice N'Vybe client project files

- SOW v2.0 PDF (client scope document)
- Meeting prep for July 4 Kay alignment
- M01 BI dashboard architecture spec
- API data source research (QBO, Plaid, SpotOn)
- Project README with structure overview

2026-07-04 20:31:01 -04:00

95 KiB

Raw Permalink Blame History

Spice N'Vybe M01 Executive BI Dashboard — Architectural Specification

Version: v1.0 | Date: 2026-07-01
Target: Homelab LXC with future AWS migration
Document Type: Architectural Blueprint (production)

System Architecture Overview
Database Schema Design
Data Ingestion Pipeline
API Endpoint Design
Frontend Component Architecture
Alerting System
Security Model
Deployment Architecture (LXC)
AWS Migration & Scaling Strategy
Data Reconciliation & Consistency Strategy

1. System Architecture Overview

1.1 High-Level Architecture

┌─────────────────────────────────────────────────────────────────────┐
│                         FRONTEND (React/Next.js)                     │
│  ┌────────────────┐ ┌──────────────┐ ┌───────────┐ ┌──────────┐   │
│  │  KPI Dashboard  │ │ Location Tabs│ │Alerts Panel│ │Settings  │   │
│  └───────┬────────┘ └──────┬───────┘ └─────┬─────┘ └────┬─────┘   │
│          │                 │               │            │          │
│          └─────────────────┴───────────────┴────────────┘          │
│                            │ REST API (HTTPS)                       │
└────────────────────────────┼────────────────────────────────────────┘
                             │
┌────────────────────────────┼────────────────────────────────────────┐
│                    API GATEWAY / REVERSE PROXY (nginx)               │
│                         (auth validation, rate limiting)             │
└────────────────────────────┼────────────────────────────────────────┘
                             │
┌────────────────────────────┼────────────────────────────────────────┐
│                  BACKEND (Node.js/Express)                           │
│  ┌──────────────────┐ ┌────────────────────┐ ┌─────────────────┐   │
│  │  API Router       │ │  Auth Service      │ │  Alert Engine   │   │
│  └──────────────────┘ └────────────────────┘ └─────────────────┘   │
│  ┌──────────────────┐ ┌────────────────────┐ ┌─────────────────┐   │
│  │  Ingestion Workers│ │  Reconciliation    │ │  Rate Limiter   │   │
│  │  (QBO/Plaid/SON)  │ │  Engine            │ │  (bottleneck)   │   │
│  └──────────────────┘ └────────────────────┘ └─────────────────┘   │
│  ┌──────────────────┐ ┌────────────────────┐                        │
│  │  Queue Manager    │ │  Job Scheduler    │                        │
│  │  (Bull/BullMQ)    │ │  (node-cron)       │                        │
│  └──────────────────┘ └────────────────────┘                        │
└────────────────────────────┬────────────────────────────────────────┘
                             │
┌────────────────────────────┼────────────────────────────────────────┐
│                    PERSISTENCE LAYER                                 │
│  ┌───────────────────────────────────────┐                          │
│  │          PostgreSQL 16                 │                          │
│  │  ┌────────────┐ ┌────────────┐       │                          │
│  │  │ Raw Ingestion│ │ Normalized  │       │                          │
│  │  │ Tables      │ │ Views      │       │                          │
│  │  └────────────┘ └────────────┘       │                          │
│  │  ┌────────────┐ ┌────────────┐       │                          │
│  │  │ Aggregations│ │ Metadata    │       │                          │
│  │  │ (Materialized)│ │ (Sync State)│       │                          │
│  │  └────────────┘ └────────────┘       │                          │
│  └───────────────────────────────────────┘                          │
│  ┌───────────────────────────────────────┐                          │
│  │           Redis 7                     │                          │
│  │  (Queue broker, cache, rate limiter,  │                          │
│  │   token store for QBO OAuth)           │                          │
│  └───────────────────────────────────────┘                          │
└─────────────────────────────────────────────────────────────────────┘
                             │
            ┌────────────────┼────────────────┐
            │                │                │
┌───────────▼────┐ ┌────────▼───────┐ ┌─────▼───────────┐
│ QuickBooks      │ │    Plaid       │ │ SpotOn POS       │
│ Online (QBO)    │ │  (Banking)     │ │ (2 locations)    │
│ OAuth 2.0       │ │  API Key       │ │ API Key per loc  │
│ 500 req/min     │ │  Cursor sync   │ │ 26h window       │
└────────────────┘ └────────────────┘ └─────────────────┘

1.2 Data Flow Summary

SpotOn ──► 5-min polls (business hours) ──► Raw Tables ──► Normalization ──► Aggregation
            26h deep sync daily
Plaid  ──► /transactions/sync (cursor) ───► Raw Tables ──► Normalization ──► Aggregation
            SYNC_UPDATES_AVAILABLE webhook
QBO    ──► CDC polling (30min) + webhooks ─► Raw Tables ──► Normalization ──► Aggregation
            Re-auth required every 100 days

1.3 Technology Stack (Precise Versions)

Layer	Technology	Version	Purpose
Frontend	Next.js	14.x	React framework with SSR/SSG
UI	React	18.x	Component library
Charts	Recharts	2.x	React-native charting
Styling	Tailwind CSS	3.x	Utility-first CSS
Tables	TanStack Table	8.x	Data grid/table
Backend	Node.js	20.x LTS	Runtime
Framework	Express	4.18+	HTTP server
Queue	BullMQ	5.x	Redis-backed job queue
Cache	Redis	7.x	Queue, cache, rate limiter
Database	PostgreSQL	16.x	Primary data store
ORM	Prisma	5.x	Type-safe DB access
Scheduler	node-cron	3.x	Cron-based sync triggers
Auth	JWT + Passport	latest	API auth
OAuth	node-quickbooks	latest	QBO OAuth 2.0

2. Database Schema Design

2.1 Naming Conventions

raw_* — Tables that mirror API responses 1:1 (immutable append log)
normalized_* — Cleaned, typed, deduplicated records
agg_* — Pre-computed aggregates (materialized views)
meta_* — Configuration, sync state, credentials

2.2 Location Reference Table

-- Core location catalog
CREATE TABLE meta_locations (
    id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    slug        VARCHAR(32) UNIQUE NOT NULL, -- 'oakland-park', 'ft-lauderdale'
    name        VARCHAR(128) NOT NULL,       -- 'Oakland Park Flagship', 'Fort Lauderdale Cloud Kitchen'
    spoton_site_id   VARCHAR(64),            -- SpotOn site identifier
    spoton_api_key   TEXT,                   -- Encrypted at application layer
    qbo_class_id     VARCHAR(64),            -- QBO Class for cost center tracking
    plaid_account_ids JSONB,                 -- Array of Plaid account IDs for this location
    is_active   BOOLEAN DEFAULT true,
    metadata    JSONB DEFAULT '{}',
    created_at  TIMESTAMPTZ DEFAULT now(),
    updated_at  TIMESTAMPTZ DEFAULT now()
);

-- Seed data
INSERT INTO meta_locations (slug, name) VALUES
    ('oakland-park', 'Oakland Park Flagship'),
    ('ft-lauderdale', 'Fort Lauderdale Cloud Kitchen');

2.3 SpotOn Raw & Normalized Tables

-- ── RAW: Mirror of SpotOn Orders API response ──
CREATE TABLE raw_spoton_orders (
    id              BIGSERIAL,
    spoton_order_id VARCHAR(64) NOT NULL,
    location_id     UUID NOT NULL REFERENCES meta_locations(id),
    raw_payload     JSONB NOT NULL,            -- Full API response
    ingested_at     TIMESTAMPTZ DEFAULT now(),
    PRIMARY KEY (id),
    UNIQUE (spoton_order_id, location_id)
);
CREATE INDEX idx_raw_so_ingested ON raw_spoton_orders(ingested_at);
CREATE INDEX idx_raw_so_location ON raw_spoton_orders(location_id);

-- ── RAW: SpotOn Labor Reports ──
CREATE TABLE raw_spoton_labor (
    id              BIGSERIAL,
    location_id     UUID NOT NULL REFERENCES meta_locations(id),
    report_date     DATE NOT NULL,
    raw_payload     JSONB NOT NULL,
    ingested_at     TIMESTAMPTZ DEFAULT now(),
    PRIMARY KEY (id),
    UNIQUE (location_id, report_date)
);

-- ── NORMALIZED: Parsed order line items ──
CREATE TABLE normalized_orders (
    id              BIGSERIAL,
    spoton_order_id VARCHAR(64) NOT NULL,
    location_id     UUID NOT NULL REFERENCES meta_locations(id),
    order_date      TIMESTAMPTZ NOT NULL,
    order_type      VARCHAR(32),           -- 'dine-in', 'takeout', 'delivery', 'online'
    subtotal        DECIMAL(12,2),
    tax             DECIMAL(12,2),
    tip             DECIMAL(12,2),
    total           DECIMAL(12,2) NOT NULL,
    payment_method  VARCHAR(32),           -- 'credit', 'cash', 'gift_card', etc.
    item_count      INTEGER,
    status          VARCHAR(32),           -- 'completed', 'refunded', 'voided'
    raw_id          BIGINT REFERENCES raw_spoton_orders(id),
    created_at      TIMESTAMPTZ DEFAULT now(),
    PRIMARY KEY (id)
);
CREATE INDEX idx_norm_orders_date ON normalized_orders(order_date);
CREATE INDEX idx_norm_orders_location ON normalized_orders(location_id);
CREATE INDEX idx_norm_orders_status ON normalized_orders(status);

-- ── NORMALIZED: Labor costs from SpotOn ──
CREATE TABLE normalized_labor_costs (
    id              BIGSERIAL,
    location_id     UUID NOT NULL REFERENCES meta_locations(id),
    report_date     DATE NOT NULL,
    total_hours     DECIMAL(10,2),
    total_wages     DECIMAL(12,2),
    total_labor_cost DECIMAL(12,2),       -- wages + taxes + benefits
    employee_count  INTEGER,
    labor_percent   DECIMAL(5,2),          -- labor cost as % of sales
    raw_id          BIGINT REFERENCES raw_spoton_labor(id),
    created_at      TIMESTAMPTZ DEFAULT now(),
    PRIMARY KEY (id),
    UNIQUE (location_id, report_date)
);

2.4 Plaid Raw & Normalized Tables

-- ── RAW: Plaid transactions sync state ──
CREATE TABLE meta_plaid_cursors (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    plaid_item_id   VARCHAR(64) UNIQUE NOT NULL,
    access_token    TEXT NOT NULL,           -- Encrypted at application layer
    next_cursor     TEXT,                    -- Cursor for incremental sync
    initial_cursor  TEXT,                    -- Saved for recovery
    last_sync_at    TIMESTAMPTZ,
    status          VARCHAR(32) DEFAULT 'active', -- 'active', 'error', 'requires_update'
    error_detail    TEXT,
    created_at      TIMESTAMPTZ DEFAULT now()
);

-- ── RAW: Plaid transactions (append-only) ──
CREATE TABLE raw_plaid_transactions (
    id              BIGSERIAL,
    plaid_txn_id    VARCHAR(64) NOT NULL UNIQUE,
    plaid_item_id   VARCHAR(64) NOT NULL REFERENCES meta_plaid_cursors(plaid_item_id),
    account_id      VARCHAR(64),
    raw_payload     JSONB NOT NULL,
    sync_type       VARCHAR(16),            -- 'initial', 'added', 'modified', 'removed'
    ingested_at     TIMESTAMPTZ DEFAULT now(),
    PRIMARY KEY (id)
);
CREATE INDEX idx_raw_plaid_item ON raw_plaid_transactions(plaid_item_id);
CREATE INDEX idx_raw_plaid_ingested ON raw_plaid_transactions(ingested_at);

-- ── NORMALIZED: Cleaned Plaid transactions ──
CREATE TABLE normalized_bank_transactions (
    id              BIGSERIAL,
    plaid_txn_id    VARCHAR(64) UNIQUE NOT NULL,
    location_id     UUID REFERENCES meta_locations(id),  -- Mapped via Plaid account
    account_id      VARCHAR(64),
    transaction_date DATE NOT NULL,
    amount          DECIMAL(12,2) NOT NULL,  -- Positive = debit, negative = credit
    description     TEXT,
    merchant_name   VARCHAR(256),
    category        VARCHAR(128),
    personal_finance_category VARCHAR(128),
    is_expense      BOOLEAN GENERATED ALWAYS AS (amount > 0) STORED,
    is_reconciled   BOOLEAN DEFAULT false,   -- Matched to SpotOn deposit?
    reconciled_txn_id BIGINT REFERENCES normalized_orders(id),
    created_at      TIMESTAMPTZ DEFAULT now(),
    PRIMARY KEY (id)
);
CREATE INDEX idx_bank_txn_date ON normalized_bank_transactions(transaction_date);
CREATE INDEX idx_bank_txn_location ON normalized_bank_transactions(location_id);
CREATE INDEX idx_bank_txn_category ON normalized_bank_transactions(category);
CREATE INDEX idx_bank_txn_reconciled ON normalized_bank_transactions(is_reconciled);

2.5 QuickBooks Raw & Normalized Tables

-- ── META: QBO OAuth state ──
CREATE TABLE meta_qbo_credentials (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    realm_id        VARCHAR(64) UNIQUE NOT NULL,
    access_token    TEXT NOT NULL,           -- Encrypted; rotated every 60 min
    refresh_token   TEXT NOT NULL,           -- Encrypted; rotated every 100 days
    token_expires_at TIMESTAMPTZ NOT NULL,
    refresh_expires_at TIMESTAMPTZ NOT NULL,
    is_active       BOOLEAN DEFAULT true,
    last_refreshed_at TIMESTAMPTZ,
    created_at      TIMESTAMPTZ DEFAULT now()
);

-- ── META: QBO CDC sync cursor ──
CREATE TABLE meta_qbo_cdc_cursors (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    realm_id        VARCHAR(64) NOT NULL REFERENCES meta_qbo_credentials(realm_id),
    entity_name     VARCHAR(64) NOT NULL,    -- 'Invoice', 'Bill', 'Payment', etc.
    last_cdc_timestamp TIMESTAMPTZ NOT NULL,
    last_sync_at    TIMESTAMPTZ,
    UNIQUE (realm_id, entity_name)
);

-- ── RAW: QBO CDC events (append log) ──
CREATE TABLE raw_qbo_cdc_events (
    id              BIGSERIAL,
    realm_id        VARCHAR(64) NOT NULL,
    entity_name     VARCHAR(64) NOT NULL,
    entity_id       VARCHAR(64) NOT NULL,
    operation       VARCHAR(16) NOT NULL,    -- 'Create', 'Update', 'Delete'
    raw_payload     JSONB NOT NULL,          -- Full entity snapshot
    event_timestamp TIMESTAMPTZ NOT NULL,
    ingested_at     TIMESTAMPTZ DEFAULT now(),
    PRIMARY KEY (id)
);
CREATE INDEX idx_raw_qbo_event ON raw_qbo_cdc_events(entity_name, entity_id);
CREATE INDEX idx_raw_qbo_time ON raw_qbo_cdc_events(event_timestamp);

-- ── RAW: QBO P&L report snapshots ──
CREATE TABLE raw_qbo_profit_loss (
    id              BIGSERIAL,
    realm_id        VARCHAR(64) NOT NULL,
    report_date     DATE NOT NULL,
    report_type     VARCHAR(32) NOT NULL,    -- 'monthly', 'quarterly', 'ytd'
    raw_payload     JSONB NOT NULL,
    ingested_at     TIMESTAMPTZ DEFAULT now(),
    UNIQUE (realm_id, report_date, report_type)
);

-- ── NORMALIZED: QBO Costs (COGS + Expenses) ──
CREATE TABLE normalized_qbo_costs (
    id              BIGSERIAL,
    location_id     UUID REFERENCES meta_locations(id),
    cost_date       DATE NOT NULL,
    cost_category   VARCHAR(64) NOT NULL,    -- 'cogs', 'rent', 'utilities', 'payroll', 'marketing', etc.
    account_name    VARCHAR(128),
    account_id      VARCHAR(64),
    amount          DECIMAL(12,2) NOT NULL,
    source          VARCHAR(32) DEFAULT 'qbo',  -- 'qbo', 'plaid', 'manual'
    source_ref      VARCHAR(128),            -- Entity ID from source
    created_at      TIMESTAMPTZ DEFAULT now(),
    PRIMARY KEY (id)
);
CREATE INDEX idx_costs_date ON normalized_qbo_costs(cost_date);
CREATE INDEX idx_costs_location ON normalized_qbo_costs(location_id);
CREATE INDEX idx_costs_category ON normalized_qbo_costs(cost_category);

2.6 Aggregation Tables (Materialized Views)

-- ── Daily Sales Aggregation ──
CREATE MATERIALIZED VIEW agg_daily_sales AS
SELECT
    location_id,
    order_date::DATE AS sale_date,
    COUNT(*) AS order_count,
    SUM(total) AS gross_sales,
    SUM(CASE WHEN status != 'refunded' THEN total ELSE 0 END) AS net_sales,
    SUM(CASE WHEN status = 'refunded' THEN ABS(total) ELSE 0 END) AS refunds,
    SUM(tax) AS tax_collected,
    SUM(tip) AS tips,
    SUM(item_count) AS total_items,
    AVG(total) AS avg_order_value,
    COUNT(DISTINCT payment_method) AS payment_methods_used
FROM normalized_orders
GROUP BY location_id, order_date::DATE;

CREATE UNIQUE INDEX ON agg_daily_sales(location_id, sale_date);

-- ── Daily Sales × Plaid Deposit Reconciliation ──
CREATE MATERIALIZED VIEW agg_daily_reconciliation AS
SELECT
    COALESCE(s.location_id, b.location_id) AS location_id,
    COALESCE(s.sale_date, b.transaction_date) AS report_date,
    COALESCE(s.net_sales, 0) AS spoton_sales,
    COALESCE(SUM(ABS(b.amount)), 0) AS plaid_deposits,  -- Credits only
    CASE
        WHEN s.net_sales IS NULL THEN 'no_sales_data'
        WHEN SUM(ABS(b.amount)) IS NULL THEN 'no_bank_data'
        WHEN ABS(s.net_sales - SUM(ABS(b.amount))) < 0.01 THEN 'matched'
        WHEN ABS(s.net_sales - SUM(ABS(b.amount))) / NULLIF(s.net_sales, 0) < 0.05 THEN 'minor_variance'
        ELSE 'variance_detected'
    END AS reconciliation_status,
    (s.net_sales - COALESCE(SUM(ABS(b.amount)), 0)) AS variance
FROM agg_daily_sales s
FULL OUTER JOIN normalized_bank_transactions b
    ON s.location_id = b.location_id
    AND s.sale_date = b.transaction_date
    AND b.amount < 0  -- Credits (deposits)
GROUP BY COALESCE(s.location_id, b.location_id),
         COALESCE(s.sale_date, b.transaction_date),
         s.net_sales;

CREATE UNIQUE INDEX ON agg_daily_reconciliation(location_id, report_date);

-- ── Daily P&L Summary ──
CREATE MATERIALIZED VIEW agg_daily_profit_loss AS
SELECT
    COALESCE(s.location_id, c.location_id) AS location_id,
    COALESCE(s.sale_date, c.cost_date) AS report_date,
    COALESCE(s.net_sales, 0) AS total_revenue,
    lc.total_labor_cost AS labor_cost,
    lc.labor_percent,
    COALESCE(SUM(c.amount) FILTER (WHERE c.cost_category = 'cogs'), 0) AS cogs,
    COALESCE(SUM(c.amount) FILTER (WHERE c.cost_category != 'cogs'), 0) AS operating_expenses,
    COALESCE(lc.total_labor_cost, 0) + COALESCE(SUM(c.amount), 0) AS total_costs,
    COALESCE(s.net_sales, 0) - (COALESCE(lc.total_labor_cost, 0) + COALESCE(SUM(c.amount), 0)) AS net_profit
FROM agg_daily_sales s
FULL OUTER JOIN normalized_qbo_costs c
    ON s.location_id = c.location_id AND s.sale_date = c.cost_date
LEFT JOIN normalized_labor_costs lc
    ON s.location_id = lc.location_id AND s.sale_date = lc.report_date
GROUP BY COALESCE(s.location_id, c.location_id),
         COALESCE(s.sale_date, c.cost_date),
         s.net_sales,
         lc.total_labor_cost,
         lc.labor_percent;

CREATE UNIQUE INDEX ON agg_daily_profit_loss(location_id, report_date);

-- ── Monthly Location Comparison ──
CREATE MATERIALIZED VIEW agg_monthly_comparison AS
SELECT
    location_id,
    date_trunc('month', report_date) AS month,
    SUM(total_revenue) AS revenue,
    SUM(total_costs) AS costs,
    SUM(net_profit) AS profit,
    AVG(labor_percent) AS avg_labor_pct,
    SUM(labor_cost) AS total_labor,
    SUM(cogs) AS total_cogs,
    SUM(operating_expenses) AS total_opex,
    COUNT(*) AS days_with_data
FROM agg_daily_profit_loss
GROUP BY location_id, date_trunc('month', report_date);

CREATE UNIQUE INDEX ON agg_monthly_comparison(location_id, month);

2.7 Alerting & Audit Tables

-- ── Alert Log ──
CREATE TABLE meta_alerts (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    severity        VARCHAR(16) NOT NULL CHECK (severity IN (
                        'CRITICAL', 'FAILURE', 'ERROR', 'SECURITY', 'WARNING', 'INFO'
                    )),
    category        VARCHAR(64) NOT NULL,   -- 'ingestion', 'reconciliation', 'auth', 'system', 'kpi'
    title           VARCHAR(256) NOT NULL,
    message         TEXT NOT NULL,
    source          VARCHAR(32),             -- 'qbo', 'plaid', 'spoton', 'system'
    location_id     UUID REFERENCES meta_locations(id),
    kpi_name        VARCHAR(64),             -- 'sales', 'costs', 'net_profit', 'labor_pct'
    kpi_value       DECIMAL(12,2),
    email_sent      BOOLEAN DEFAULT false,
    email_sent_at   TIMESTAMPTZ,
    acknowledged    BOOLEAN DEFAULT false,
    acknowledged_by VARCHAR(128),
    acknowledged_at TIMESTAMPTZ,
    created_at      TIMESTAMPTZ DEFAULT now()
);
CREATE INDEX idx_alerts_severity ON meta_alerts(severity);
CREATE INDEX idx_alerts_created ON meta_alerts(created_at);
CREATE INDEX idx_alerts_category ON meta_alerts(category);

-- ── Ingestion Job Log ──
CREATE TABLE meta_ingestion_jobs (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    source          VARCHAR(32) NOT NULL,   -- 'qbo', 'plaid', 'spoton'
    job_type        VARCHAR(32) NOT NULL,   -- 'full_sync', 'incremental', 'webhook', 'reconciliation'
    status          VARCHAR(16) NOT NULL CHECK (status IN (
                        'queued', 'running', 'completed', 'failed', 'retrying'
                    )),
    records_fetched INTEGER DEFAULT 0,
    records_ingested INTEGER DEFAULT 0,
    records_failed  INTEGER DEFAULT 0,
    started_at      TIMESTAMPTZ,
    completed_at    TIMESTAMPTZ,
    error_detail    TEXT,
    retry_count     INTEGER DEFAULT 0,
    next_retry_at   TIMESTAMPTZ,
    created_at      TIMESTAMPTZ DEFAULT now()
);
CREATE INDEX idx_ingestion_source ON meta_ingestion_jobs(source, created_at DESC);

-- ── Rate Limit Tracking ──
CREATE TABLE meta_rate_limit_log (
    id              BIGSERIAL,
    source          VARCHAR(32) NOT NULL,
    endpoint        VARCHAR(256),
    status_code     INTEGER,
    retry_after_sec INTEGER,
    occurred_at     TIMESTAMPTZ DEFAULT now()
);

3. Data Ingestion Pipeline

3.1 Pipeline Architecture

EXT SOURCE ──► ADAPTER ──► RATE LIMITER ──► QUEUE ──► WORKER ──► RAW TABLE ──► TRANSFORM ──► NORMALIZED ──► AGGREGATION
                                                                                                    │
                                                                                                    ▼
                                                                                              ALERT EVALUATOR

3.2 QuickBooks Online Ingestion

3.2.1 OAuth Token Lifecycle

┌──────────────────────────────────────────────────────────┐
│  TOKEN MANAGER (runs on loop every 50 minutes)            │
│                                                          │
│  1. Check meta_qbo_credentials.token_expires_at          │
│  2. If expires within 10 min → refresh now               │
│  3. POST to /oauth2/v1/tokens/bearer with refresh_token  │
│  4. Store NEW access_token + refresh_token immediately   │
│  5. Old refresh_token is INVALIDATED after use           │
│  6. If refresh fails → queue SECURITY alert              │
│                                                          │
│  CRITICAL: Refresh token expires after 100 days total.    │
│  If expired → user must re-authorize via OAuth flow.     │
│  Track refresh_expires_at and alert at 90-day mark.      │
└──────────────────────────────────────────────────────────┘

3.2.2 Sync Strategy

Sync Type	Frequency	Method	Details
CDC Poll	Every 30 min	`GET /cdc?entities=...&changedSince=`	Uses meta_qbo_cdc_cursors for timestamp tracking. Fetches up to 30 days back.
Webhook	Real-time	POST to `/api/webhooks/qbo`	Validates HMAC signature, enqueues entity fetch job
Full Sync	Daily (2am)	CDC with 48h window + P&L report fetch	Reconciles P&L, pulls full Cost data
Periodic	Weekly (Sun 3am)	Full query on all entities	Data integrity check, catches CDC gaps

3.2.3 Webhook Handler Flow

POST /api/webhooks/qbo
  ├─ 1. Verify HMAC-SHA256 signature (prevent forgery)
  ├─ 2. Parse eventNotifications[]
  ├─ 3. For each entity:
  │     └─ Enqueue BullMQ job: { type: 'qbo:fetch_entity', entity, id, operation }
  └─ 4. Return 200 immediately (don't process inline)

3.2.4 Rate Limiter

const QBO_RATE_LIMIT = {
    maxPerMinute: 450,    // 10% headroom below 500
    maxConcurrent: 35,    // 5 below 40
};

// Token bucket algorithm in Redis
// Key: rate_limit:qbo:{realm_id}:{minute_bucket}
// TTL: 90 seconds

async function checkQboRateLimit(realmId) {
    const key = `rate_limit:qbo:${realmId}:${Math.floor(Date.now() / 60000)}`;
    const count = await redis.incr(key);
    if (count === 1) await redis.expire(key, 90);
    if (count > QBO_RATE_LIMIT.maxPerMinute) throw new RateLimitError('QBO rate limit approaching');
}

3.3 Plaid Ingestion

3.3.1 Initial Sync

1. Call /transactions/sync with no cursor (first sync)
2. Paginate: while has_more === true, keep calling with next_cursor
3. For each page:
   - added[] → INSERT INTO raw_plaid_transactions (sync_type='initial')
   - modified[] → INSERT INTO raw_plaid_transactions (sync_type='modified')
   - removed[] → INSERT INTO raw_plaid_transactions (sync_type='removed')
4. Save next_cursor to meta_plaid_cursors
5. Trigger normalized_bank_transactions rebuild for this item

3.3.2 Incremental Sync

Scheduled every 15 minutes OR triggered by SYNC_UPDATES_AVAILABLE webhook:

1. Load cursor from meta_plaid_cursors
2. Call /transactions/sync with cursor
3. Handle pagination with cursor preservation:
   - Temporarily preserve old cursor
   - If TRANSACTIONS_SYNC_MUTATION_DURING_PAGINATION error:
     → restart from preserved cursor
4. Process added/modified/removed
5. Save new next_cursor
6. If has_more === true → schedule next page immediately (max 10 pages)

3.3.3 Webhook Receiver

POST /api/webhooks/plaid
  ├─ 1. Verify webhook signature
  ├─ 2. Parse webhook_code:
  │    - SYNC_UPDATES_AVAILABLE → enqueue incremental sync
  │    - ITEM_LOGIN_REQUIRED → create SECURITY alert, flag item
  │    - INITIAL_UPDATE → enqueue full sync
  │    - HISTORICAL_UPDATE → enqueue full historical pull
  └─ 3. Return 200

3.4 SpotOn Ingestion

3.4.1 Orders Polling

Dual-strategy approach to deal with SpotOn's data availability window:

┌──────────────────────────────────────────────────────────────────┐
│ STRATEGY A: Real-time (5-min polls during business hours)         │
│                                                                  │
│ Schedule: Mon-Sat, 8:00 AM - 11:00 PM (local)                   │
│ Endpoint: GET /orders?updatedSince={lastPoll}&site={location}    │
│ Window: Poll frequency (5 min)                                   │
│                                                                  │
│ STRATEGY B: Deep Sync (daily at 3:00 AM)                         │
│                                                                  │
│ Schedule: Every day at 3:00 AM                                   │
│ Endpoint: GET /orders?updatedSince={T-26h}&site={location}       │
│ Window: 26 hours (SpotOn's max)                                  │
│ Purpose: Catch missed orders, data integrity check               │
└──────────────────────────────────────────────────────────────────┘

Business hours detection:
- Read business hours from meta_locations.metadata
- Fallback: Mon-Sat 8AM-11PM, Sun 9AM-9PM
- CRON expression generated per location

3.4.2 Labor Reports Polling

Schedule: Daily at 6:00 AM (after overnight batch processing completes)

Endpoint: GET /labor-reports?date={yesterday}&site={location}
Wait: SpotOn labor reports are ETL-lagged → add 1-2h buffer
Retry: If 404/empty, retry every 30 min for up to 4 hours

3.5 BullMQ Queue Architecture

Queue: qbo-sync
  ├─ Type: qbo:cdc_poll         │ Priority: 20 │ Retry: 3
  ├─ Type: qbo:fetch_entity     │ Priority: 10 │ Retry: 2
  ├─ Type: qbo:refresh_token     │ Priority: 1  │ Retry: 3 (+ alert on fail)
  └─ Type: qbo:pnl_report       │ Priority: 30 │ Retry: 2

Queue: plaid-sync
  ├─ Type: plaid:incremental     │ Priority: 20 │ Retry: 3
  ├─ Type: plaid:full_sync       │ Priority: 30 │ Retry: 2
  └─ Type: plaid:refresh_link    │ Priority: 5  │ Retry: 3

Queue: spoton-sync
  ├─ Type: spoton:orders_realtime │ Priority: 20 │ Retry: 2
  ├─ Type: spoton:orders_deep    │ Priority: 30 │ Retry: 2
  └─ Type: spoton:labor_reports  │ Priority: 30 │ Retry: 4 (30-min intervals)

Queue: reconciliation
  ├─ Type: recon:daily           │ Priority: 40 │ Runs: 7AM daily
  └─ Type: recon:monthly         │ Priority: 50 │ Runs: 1st of month

3.6 Error Recovery Strategy

┌────────────────────────────────────────────────────────────┐
│ ERROR RECOVERY LAYERS                                       │
│                                                            │
│ L1: Retry (Transient Failures)                             │
│     - 429 rate limit → exponential backoff + jitter        │
│     - 5xx server errors → retry (max 3, backoff ^2)       │
│     - Network timeout → retry (max 3)                     │
│     - Default: 2^n * 1000ms + rand(0, 1000)ms             │
│                                                            │
│ L2: Dead Letter Queue (Persistent Failures)                │
│     - After max retries exhausted                          │
│     - Job moved to {queue}:dead-letter                     │
│     - Triggers ERROR alert to email                        │
│     - Manual review required                               │
│                                                            │
│ L3: Circut Breaker (Cascading Failures)                    │
│     - If >10 consecutive failures on same source:          │
│     → Open circuit breaker for 5 minutes                   │
│     → Queue CRITICAL alert                                 │
│     → After 5 min, half-open: try 1 request               │
│     → If success, close; if fail, reopen for 10 min        │
│                                                            │
│ L4: Backfill (Data Gaps)                                   │
│     - Daily integrity check compares record counts         │
│     - ETL Gap Detection:                                   │
│       SELECT location_id, sale_date FROM agg_daily_sales   │
│       WHERE sale_date >= CURRENT_DATE - INTERVAL '7 days'  │
│       AND order_count = 0                                  │
│       AND sale_date != CURRENT_DATE;                       │
│     - Gaps auto-queue a spoton:orders_deep job             │
└────────────────────────────────────────────────────────────┘

4. API Endpoint Design

4.1 REST API Structure

Base URL: https://bi.spicenvybe.com/api/v1

4.1.1 Authentication & Authorization

Method	Endpoint	Auth	Description
POST	`/auth/login`	None (rate-limited)	Email + password auth, returns JWT
POST	`/auth/refresh`	JWT	Refresh access token
POST	`/auth/logout`	JWT	Invalidate token
GET	`/auth/me`	JWT	Current user profile
POST	`/auth/qbo/connect`	JWT+Admin	Initiate QBO OAuth flow
GET	`/auth/qbo/callback`	None	QBO OAuth callback handler

4.1.2 KPI Endpoints

Method	Endpoint	Auth	Description
GET	`/kpi/sales`	JWT	Sales KPIs (today, MTD, vs prev period)
GET	`/kpi/sales/:locationSlug`	JWT	Sales per location
GET	`/kpi/net-profit`	JWT	Net Profit KPIs
GET	`/kpi/net-profit/:locationSlug`	JWT	Net Profit per location
GET	`/kpi/costs`	JWT	Cost breakdown KPIs
GET	`/kpi/costs/:locationSlug`	JWT	Costs per location
GET	`/kpi/labor`	JWT	Labor KPIs + % of sales
GET	`/kpi/labor/:locationSlug`	JWT	Labor per location
GET	`/kpi/comparison`	JWT	Side-by-side location comparison
GET	`/kpi/summary`	JWT	All KPIs in single response (dashboard load)

4.1.3 Data Endpoints

Method	Endpoint	Auth	Description
GET	`/data/sales/timeseries`	JWT	Sales over time (query params: from, to, granularity, location)
GET	`/data/sales/by-category`	JWT	Sales breakdown by item category
GET	`/data/sales/by-payment`	JWT	Sales by payment method
GET	`/data/costs/breakdown`	JWT	Cost breakdown by category
GET	`/data/costs/trend`	JWT	Cost trends over time
GET	`/data/reconciliation`	JWT	SpotOn vs Plaid deposit reconciliation
GET	`/data/reconciliation/:locationSlug`	JWT	Per-location reconciliation
GET	`/data/labor/details`	JWT	Labor cost detail (hours, wages, headcount)
GET	`/data/transactions`	JWT	Raw transaction search (Plaid)
GET	`/data/orders`	JWT	Raw order search (SpotOn)

4.1.4 Alert Endpoints

Method	Endpoint	Auth	Description
GET	`/alerts`	JWT	Recent alerts (paginated)
GET	`/alerts/active`	JWT	Unacknowledged alerts only
PATCH	`/alerts/:id/acknowledge`	JWT	Acknowledge an alert
GET	`/alerts/stats`	JWT	Alert statistics (by severity, by source)
PUT	`/alerts/config`	JWT+Admin	Alert threshold configuration
POST	`/alerts/test`	JWT+Admin	Send test alert email

4.1.5 Admin / System Endpoints

Method	Endpoint	Auth	Description
GET	`/admin/health`	JWT+Admin	System health status
GET	`/admin/ingestion/status`	JWT+Admin	All ingestion job statuses
GET	`/admin/ingestion/:source`	JWT+Admin	Ingestion details per source
POST	`/admin/ingestion/trigger`	JWT+Admin	Manually trigger a sync job
GET	`/admin/rate-limits`	JWT+Admin	Rate limit utilization
GET	`/admin/token-status`	JWT+Admin	QBO token expiry info

4.1.6 Webhook Endpoints (no JWT, payload-signed)

Method	Endpoint	Auth	Description
POST	`/webhooks/qbo`	HMAC-SHA256	QBO data change notifications
POST	`/webhooks/plaid`	Plaid webhook secret	Plaid transaction updates

4.2 Key Response Shapes

`GET /kpi/summary` — Dashboard Initial Load

{
  "period": {
    "current": { "start": "2026-06-01", "end": "2026-06-30" },
    "previous": { "start": "2026-05-01", "end": "2026-05-31" }
  },
  "overall": {
    "sales": {
      "current": 284750.00,
      "previous": 261200.00,
      "change_pct": 9.02,
      "trend": "up"
    },
    "net_profit": {
      "current": 71200.00,
      "previous": 58700.00,
      "change_pct": 21.29,
      "trend": "up"
    },
    "costs": {
      "current": 213550.00,
      "previous": 202500.00,
      "change_pct": 5.46,
      "trend": "up"
    },
    "labor_pct": {
      "current": 24.8,
      "previous": 27.1,
      "change_pct": -8.49,
      "trend": "down",
      "target": 25.0
    }
  },
  "locations": [
    {
      "slug": "oakland-park",
      "name": "Oakland Park Flagship",
      "sales": { "current": 182400.00, "change_pct": 8.5 },
      "net_profit": { "current": 46800.00, "change_pct": 19.2 },
      "costs": { "current": 135600.00, "change_pct": 5.1 },
      "labor_pct": { "current": 23.9, "target": 25.0 }
    },
    {
      "slug": "ft-lauderdale",
      "name": "Fort Lauderdale Cloud Kitchen",
      "sales": { "current": 102350.00, "change_pct": 10.1 },
      "net_profit": { "current": 24400.00, "change_pct": 25.8 },
      "costs": { "current": 77950.00, "change_pct": 6.2 },
      "labor_pct": { "current": 26.2, "target": 25.0 }
    }
  ],
  "reconciliation_status": "matched",
  "last_updated": "2026-07-01T06:15:00Z"
}

`GET /kpi/labor/:locationSlug` — Labor Detail

{
  "location": "oakland-park",
  "period": {
    "current": { "start": "2026-06-01", "end": "2026-06-30" },
    "previous": { "start": "2026-05-01", "end": "2026-05-31" }
  },
  "metrics": {
    "labor_cost": { "current": 67100.00, "previous": 70800.00 },
    "labor_pct": { "current": 23.9, "previous": 27.1 },
    "total_hours": { "current": 3124.5, "previous": 3340.0 },
    "avg_hourly_rate": { "current": 18.45, "previous": 18.27 },
    "employee_count": { "current": 18, "previous": 20 },
    "total_revenue": { "current": 280500.00, "previous": 261200.00 }
  },
  "target": 25.0,
  "status": "on_target",
  "daily_breakdown": [
    { "date": "2026-06-01", "labor_cost": 2140.00, "labor_pct": 24.1, "status": "on_target" },
    { "date": "2026-06-02", "labor_cost": 2280.00, "labor_pct": 26.3, "status": "over_target" }
  ]
}

5. Frontend Component Architecture

5.1 Next.js Application Structure

src/
├── app/                          # Next.js 14 App Router
│   ├── layout.tsx                # Root layout (auth boundary)
│   ├── page.tsx                  # Dashboard home (redirect to /dashboard)
│   ├── login/
│   │   └── page.tsx              # Login page
│   ├── dashboard/
│   │   ├── layout.tsx            # Dashboard shell (sidebar, header)
│   │   └── page.tsx              # Main dashboard
│   ├── alerts/
│   │   └── page.tsx              # Alert center
│   ├── settings/
│   │   └── page.tsx              # Settings, connections
│   └── api/                      # API routes (BFF pattern)
│       └── [...route]/route.ts   # Proxy to Express backend
│
├── components/
│   ├── dashboard/
│   │   ├── KPIGrid.tsx           # KPI card grid layout
│   │   ├── KPIWidget.tsx          # Single KPI card
│   │   ├── LocationTabs.tsx      # Location switcher tabs
│   │   ├── LocationComparison.tsx# Side-by-side comparison view
│   │   ├── SalesChart.tsx        # Sales timeseries (Recharts)
│   │   ├── ProfitChart.tsx       # Profit waterfall
│   │   ├── CostBreakdownChart.tsx# Cost donut/bar chart
│   │   ├── LaborGauge.tsx        # Labor % gauge with target
│   │   ├── ReconciliationCard.tsx# Plaid vs SpotOn match status
│   │   └── DataFreshnessBanner.tsx# "Last updated X ago"
│   │
│   ├── alerts/
│   │   ├── AlertPanel.tsx        # Alert sidebar/drawer
│   │   ├── AlertBadge.tsx        # Unacknowledged count badge
│   │   └── AlertRow.tsx          # Single alert in list
│   │
│   ├── charts/
│   │   ├── AreaChart.tsx         # Reusable area chart
│   │   ├── BarChart.tsx          # Reusable bar chart
│   │   ├── DonutChart.tsx        # Reusable donut chart
│   │   ├── GaugeChart.tsx        # Target gauge
│   │   └── ComparisonBar.tsx     # Two-location comparison bar
│   │
│   ├── layout/
│   │   ├── Sidebar.tsx           # Navigation sidebar
│   │   ├── Header.tsx            # Top header bar
│   │   └── Footer.tsx
│   │
│   └── shared/
│       ├── LoadingSpinner.tsx
│       ├── ErrorBoundary.tsx
│       ├── EmptyState.tsx
│       ├── DataCard.tsx           # Reusable metric card
│       └── PeriodSelector.tsx    # Date range picker
│
├── hooks/
│   ├── useKPI.ts                 # KPI data fetching (SWR)
│   ├── useAlerts.ts              # Alert polling
│   ├── useLocation.ts            # Current location context
│   └── useAuth.ts                # Auth state management
│
├── lib/
│   ├── api-client.ts             # Axios/fetch wrapper
│   ├── formatters.ts             # Currency, percent, date formatters
│   └── constants.ts              # API URLs, thresholds
│
└── types/
    ├── kpi.ts                    # KPI type definitions
    ├── alerts.ts                 # Alert types
    └── api.ts                    # API response types

5.2 Component Tree (Dashboard Page)

<DashboardLayout>
  ├── <Header>
  │     ├── DataFreshnessBanner
  │     └── AlertBadge (unacked count, links to alerts page)
  │
  ├── <Sidebar>
  │     ├── Nav: Dashboard, Alerts, Settings
  │     └── Location filter controls
  │
  └── <main>
        ├── <PeriodSelector />          (7d, 30d, MTD, QTD, custom)
        │
        ├── <LocationTabs>
        │     ├── Tab: "Combined"
        │     ├── Tab: "Oakland Park"
        │     └── Tab: "Fort Lauderdale"
        │
        ├── <KPIGrid>
        │     ├── <KPIWidget metric="sales" value="$284,750" change="+9.0%" trend="up" />
        │     ├── <KPIWidget metric="net_profit" value="$71,200" change="+21.3%" trend="up" />
        │     ├── <KPIWidget metric="costs" value="$213,550" change="+5.5%" trend="up" />
        │     └── <KPIWidget metric="labor_pct" value="24.8%" change="-8.5%" trend="down" />
        │
        ├── <ReconciliationCard />      (SpotOn vs Plaid deposit match)
        │
        ├── <div class="grid grid-cols-2">
        │     ├── <SalesChart />          (Revenue timeseries)
        │     └── <ProfitChart />         (Profit = Revenue - Costs stacked)
        │
        ├── <div class="grid grid-cols-2">
        │     ├── <CostBreakdownChart />  (COGS, Labor, Rent, Ops)
        │     └── <LaborGauge />          (24.8% vs 25% target)
        │
        └── <LocationComparison />       (Oakland Park vs FTL table + bars)
</DashboardLayout>

5.3 Data Fetching Strategy

Primary Pattern: SWR (stale-while-revalidate)
- Dashboard fetches GET /kpi/summary on load (cache: 60s)
- Individual widgets can hydrate from summary OR fetch detailed endpoints
- Revalidation interval: 120 seconds (polls for fresh data)
- On focus revalidation: enabled (when user returns to tab)

Cache Strategy:
- API responses cached in Redis for 60s (backend)
- SWR client cache: 120s with stale-while-revalidate
- Manual refresh button available for instant re-fetch

Optimistic Loading:
- Show last-known data immediately (SWR cache)
- Show skeleton/Glimmer while first load is in progress
- Pulse animation on widgets that are being refreshed
- Error state: show stale data + "Last updated: X ago — refresh failed" banner

5.4 State Management

No Redux needed for this scope. Use:
1. SWR for server state (all API data)
2. React Context for:
   - AuthProvider (user, token, permissions)
   - LocationProvider (selected location filter)
   - PeriodProvider (selected date range)
3. Local state for UI interactions (expanded sections, modal open/close)

6. Alerting System

6.1 Alert Severity Levels

Level	Color	Trigger	Action
CRITICAL	🔴 Red	System down, data loss, auth failure	Email + SMS (future) + persistent notification
FAILURE	🟠 Orange	Ingestion job failed after retries, webhook delivery failure	Email + in-app
ERROR	🟡 Yellow	Partial failure, single entity fetch failed, rate limit hit	Email (digest) + in-app
SECURITY	🔴 Red	Token refresh failed, unauthorized access detected, credential expiry < 7 days	Email + in-app
WARNING	🔵 Blue	KPI threshold breached, labor % > target, reconciliation variance > 5%	Email (digest) + in-app
INFO	⚪ Gray	Successful sync, token refreshed, connection restored	In-app only

6.2 Alert Rules Engine

╔══════════════════════════════════════════════════════════════════════╗
║                       ALERT RULES                                   ║
╠══════════════════════════════════════════════════════════════════════╣
║                                                                      ║
║  ┌─ SYSTEM HEALTH ──────────────────────────────────────────────┐   ║
║  │ QBO: 5+ consecutive auth failures          → CRITICAL        │   ║
║  │ Plaid: ITEM_LOGIN_REQUIRED webhook received → ERROR          │   ║
║  │ SpotOn: API tunnel down for >15 min        → CRITICAL        │   ║
║  │ BullMQ queue backlog >1000 jobs            → ERROR           │   ║
║  │ Database connection failure                → CRITICAL        │   ║
║  └──────────────────────────────────────────────────────────────┘   ║
║                                                                      ║
║  ┌─ INGESTION ─────────────────────────────────────────────────┐   ║
║  │ CDC sync returns 0 records for 3+ consecutive polls (business│   ║
║  │   hours) → WARNING                                           │   ║
║  │ Plaid sync fails after all retries         → FAILURE         │   ║
║  │ Webhook processing fails                   → ERROR           │   ║
║  │ Rate limit exceeded                       → ERROR           │   ║
║  │ SpotOn orders deep sync gap detected       → ERROR           │   ║
║  └──────────────────────────────────────────────────────────────┘   ║
║                                                                      ║
║  ┌─ RECONCILIATION ────────────────────────────────────────────┐   ║
║  │ SpotOn sales vs Plaid deposits vary >10%   → WARNING        │   ║
║  │ Net Profit mismatch with QBO P&L >5%       → WARNING        │   ║
║  │ Manual export data differs from API data   → ERROR          │   ║
║  └──────────────────────────────────────────────────────────────┘   ║
║                                                                      ║
║  ┌─ KPI THRESHOLDS ────────────────────────────────────────────┐   ║
║  │ Labor cost % consistently above 27% (7d avg) → WARNING      │   ║
║  │ Labor cost % above 30% (any single day)    → ERROR          │   ║
║  │ Cost growth exceeds revenue growth by 5%+  → WARNING        │   ║
║  │ Daily net profit decline >20% WoW          → WARNING        │   ║
║  │ Sales decline >15% vs same day last week   → WARNING        │   ║
║  └──────────────────────────────────────────────────────────────┘   ║
║                                                                      ║
║  ┌─ SECURITY ─────────────────────────────────────────────────┐   ║
║  │ QBO refresh token expires in <7 days      → SECURITY       │   ║
║  │ QBO refresh token expired                 → CRITICAL       │   ║
║  │ Plaid Item requires user re-authentication → SECURITY      │   ║
║  │ JWT token validation failure spike         → SECURITY      │   ║
║  └──────────────────────────────────────────────────────────────┘   ║
║                                                                      ║
╚══════════════════════════════════════════════════════════════════════╝

6.3 Email Alert Format

┌─────────────────────────────────────────────────────────────────────┐
│  🔴 [CRITICAL] Spice N'Vybe BI — Ingestion Failure                 │
│                                                                    │
│  Source:       QuickBooks Online                                   │
│  Time:         2026-07-01 08:30:00 EDT                             │
│  Job ID:       j-abc12345                                          │
│                                                                    │
│  Detail:       CDC sync failed after 3 retries.                    │
│                Last successful sync: 2026-06-30 22:00 EDT          │
│                Error: HTTP 401 — token may be expired              │
│                                                                    │
│  Action:       Immediate attention required.                       │
│                → Check QBO token status in admin panel             │
│                → Re-authorize via OAuth if refresh token expired   │
│                → Dashboard data is stale since 2026-06-30 22:00    │
│                                                                    │
│  Dashboard:    https://bi.spicenvybe.com/admin/ingestion           │
│                                                                    │
│  This alert auto-resolves when the next successful sync completes. │
│  To silence, acknowledge in the dashboard.                         │
└─────────────────────────────────────────────────────────────────────┘

6.4 Alert Delivery Pipeline

┌──────────────────┐     ┌────────────────┐     ┌─────────────────┐
│  Alert Triggered  │────►│  Deduplication  │────►│  Rate Limiter   │
│  (code location)  │     │  (same alert    │     │  (max 5/min per │
│                   │     │   within 1h)    │     │   alert type)   │
└──────────────────┘     └────────────────┘     └─────────────────┘
                                                       │
                                                       ▼
┌──────────────────┐     ┌────────────────┐     ┌─────────────────┐
│  Email Sent       │◄────│  SMTP Sender    │◄────│  Queue          │
│  (via Nodemailer) │     │  (Gmail App     │     │  BullMQ         │
│                   │     │   Password)     │     │  priority queue │
└──────────────────┘     └────────────────┘     └─────────────────┘

6.5 Email Implementation

// nodemailer configuration
const transporter = nodemailer.createTransport({
    host: 'smtp.gmail.com',
    port: 587,
    secure: false,
    auth: {
        user: process.env.ALERT_EMAIL_USER,
        pass: process.env.ALERT_EMAIL_PASS  // Gmail App Password
    }
});

// Dual recipient list
const ALERT_RECIPIENTS = {
    CRITICAL: ['owner@spicenvybe.com', 'manager+urgent@spicenvybe.com'],
    FAILURE:  ['owner@spicenvybe.com', 'ops@spicenvybe.com'],
    ERROR:    ['ops@spicenvybe.com'],
    SECURITY: ['owner@spicenvybe.com', 'it@spicenvybe.com'],
    WARNING:  []  // Dashboard-only, can enable later
};

7. Security Model

7.1 Authentication Architecture

┌──────────────────────────────────────────────────────────────────────┐
│  AUTH FLOW                                                           │
│                                                                      │
│  1. User navigates to login.bi.spicenvybe.com                       │
│  2. Enters credentials → POST /api/v1/auth/login                    │
│  3. Express validates against PostgreSQL users table (bcrypt hash)  │
│  4. JWT issued: { sub, role, exp, iat }                             │
│     - Access token: 2h expiry (short-lived)                         │
│     - Refresh token: 14d expiry (secure cookie, httpOnly)           │
│  5. All subsequent requests pass JWT in Authorization: Bearer header│
│                                                                      │
│  Auth Provider Decision: Google OAuth or Authentik (TBD)             │
│  For now: email+password with bcrypt. Ready for OIDC migration.     │
│  When OIDC is implemented, Authentik handles the IdP and Passport   │
│  authenticates the JWT from Authentik's callback.                   │
└──────────────────────────────────────────────────────────────────────┘

7.2 Role-Based Access Control

Role	Permissions	Description
`admin`	Full system access, triggers sync, acknowledges alerts	System administrator
`manager`	View all KPIs, acknowledge alerts, view admin status	Operations manager
`viewer`	View KPIs and reports only	Read-only access
`system`	Internal service accounts, webhook receivers	Machine-to-machine

7.3 External API Credential Management

┌──────────────────────────────────────────────────────────────────────┐
│  CREDENTIAL STORAGE                                                  │
│                                                                      │
│  QBO:                                                                 │
│  └─ Encrypted at rest in meta_qbo_credentials table                 │
│  └─ Encryption: AES-256-GCM with key from environment variable      │
│  └─ Rotation: Access token auto-refreshed every 50 min              │
│  └─ Refresh token: rotated on every use (OAuth 2.0 spec)            │
│  └─ Token expiry alert: SECURITY at 7 days before refresh expiry    │
│                                                                      │
│  Plaid:                                                               │
│  └─ client_id + secret in environment variables (server-side only)  │
│  └─ access_token encrypted in meta_plaid_cursors table              │
│  └─ Plaid Link token generated server-side, 1-time use              │
│  └─ ITEM_LOGIN_REQUIRED triggers SECURITY alert for re-auth         │
│                                                                      │
│  SpotOn:                                                              │
│  └─ API key per location, encrypted in meta_locations table         │
│  └─ Keys stored with "Location" header in API calls                 │
└──────────────────────────────────────────────────────────────────────┘

7.4 API Security Measures

═══ LAYER 1: TRANSPORT ═══
  • HTTPS only (TLS 1.2+), no HTTP in production
  • HSTS headers enabled
  • Self-signed certs for homelab → Let's Encrypt for production

═══ LAYER 2: REQUEST ═══
  • Rate limiting per IP: 100 req/min (global), 10 req/min (auth endpoints)
  • JWT validation on every authenticated endpoint
  • CORS: restricted to dashboard origin only
  • Request body size limit: 1MB
  • Helmet.js security headers (XSS, clickjack, MIME sniffing)

═══ LAYER 3: EXTERNAL ═══
  • QBO webhook: HMAC-SHA256 signature verification
  • Plaid webhook: Plaid-Verification header + signing key verification
  • SpotOn: x-api-key per location
  • No external credentials in client-side code (ever)
  • All API keys retrieved from encrypted DB storage at request time

═══ LAYER 4: INFRASTRUCTURE ═══
  • Database: PostgreSQL user with SCHEMA-level permissions
  • App DB user: SELECT/INSERT/UPDATE on app tables only, no DDL
  • Redis: password-protected (requirepass)
  • Backend runs as non-root user in container
  • All secrets via environment variables, never in code

7.5 QBO OAuth 2.0 Flow (Detailed)

┌─────────────────────┐         ┌──────────────────┐         ┌──────────────┐
│  Dashboard Admin UI │         │  Express Backend  │         │  Intuit OAuth│
└─────────┬───────────┘         └────────┬─────────┘         └──────┬───────┘
          │                              │                          │
          │ POST /auth/qbo/connect       │                          │
          │ (admin JWT)                  │                          │
          ├─────────────────────────────►│                          │
          │                              │ Generate OAuth URL:      │
          │                              │ client_id, redirect_uri, │
          │                              │ scope, response_type=code│
          │                              │ state (random, stored)   │
          │ Redirect user to Intuit      │                          │
          │◄─────────────────────────────┤                          │
          │                              │                          │
          │ User authorizes at Intuit    │                          │
          ├─────────────────────────────────────────────────────────►│
          │                              │                          │
          │ Intuit redirects to:         │                          │
          │ /auth/qbo/callback?code=X    │                          │
          │ &state=Y&realmId=Z           │                          │
          ├─────────────────────────────►│                          │
          │                              │ Validate state           │
          │                              │ Exchange code for tokens │
          │                              ├──────────────────────────►│
          │                              │◄──────────────────────────┤
          │                              │ { access_token,          │
          │                              │   refresh_token,         │
          │                              │   expires_in, x_refresh_ │
          │                              │   token_expires_in }     │
          │                              │                          │
          │                              │ Encrypt & store in       │
          │                              │ meta_qbo_credentials     │
          │                              │                          │
          │                              │ Init cursor entries in   │
          │                              │ meta_qbo_cdc_cursors     │
          │                              │                          │
          │ "QBO connected successfully" │                          │
          │◄─────────────────────────────┤                          │

8. Deployment Architecture (LXC)

8.1 Container Topology

┌─────────────────────────────────────────────────────────────────────┐
│              Proxmox Host (spicenvybe-dev)                          │
│                                                                     │
│  ┌─────────────────┐    ┌─────────────────┐                         │
│  │   LXC Container  │    │   LXC Container  │                        │
│  │   spicenvybe-db  │    │  spicenvybe-app  │                        │
│  │   (PostgreSQL +  │    │  (Node.js +      │                        │
│  │    Redis)        │    │   Next.js +       │                        │
│  │                  │    │   nginx)          │                        │
│  │   RAM: 2GB       │    │                  │                        │
│  │   CPU: 2 cores   │    │   RAM: 4GB       │                        │
│  │   Disk: 20GB     │    │   CPU: 4 cores   │                        │
│  └─────────────────┘    │   Disk: 30GB     │                        │
│                          └─────────────────┘                         │
│                                                                      │
│  ┌──────────────────────────────────────────────────────────────┐   │
│  │  Mount: /mnt/spicenvybe-backups -> Proxmox host ZFS pool     │   │
│  └──────────────────────────────────────────────────────────────┘   │
└─────────────────────────────────────────────────────────────────────┘

8.2 Service Stack (within spicenvybe-app LXC)

┌─────────────────────────────────────────────────────┐
│  nginx (reverse proxy on host port 443 → local)     │
│  ├─ /api/* → Express (port 3001)                    │
│  ├─ / → Next.js (port 3000)                        │
│  └─ /ws → WebSocket for live updates               │
│                                                      │
│  pm2 (process manager)                               │
│  ├─ next-app         │ port 3000 │ Next.js server   │
│  ├─ express-api      │ port 3001 │ Express backend  │
│  ├─ ingestion-worker │ —         │ BullMQ worker    │
│  ├─ scheduler        │ —         │ node-cron tasks  │
│  └─ alert-worker     │ —         │ Alert evaluator  │
│                                                      │
│  Redis (port 6379, password-protected)               │
│  ├─ Queue: qbo-sync                                 │
│  ├─ Queue: plaid-sync                               │
│  ├─ Queue: spoton-sync                              │
│  ├─ Queue: reconciliation                           │
│  └─ Cache: API responses (60s TTL)                 │
│                                                      │
│  PostgreSQL (port 5432, app DB user)                │
│  ├─ spicenvybe_bi database                          │
│  └─ Tables: raw_*, normalized_*, agg_*, meta_*      │
└─────────────────────────────────────────────────────┘

8.3 SystemD Integration

# /etc/systemd/system/spicenvybe-bi.target
[Unit]
Description=Spice N'Vybe BI Dashboard
Requires=spicenvybe-db.service
After=network.target
Wants=spicenvybe-next.service spicenvybe-api.service spicenvybe-worker.service spicenvybe-scheduler.service

# Individual services start via pm2, managed by:
[Unit]
Description=PM2 for Spice N'Vybe BI
[Service]
Type=forking
User=spicenvybe
ExecStart=/usr/bin/pm2 start /opt/spicenvybe-bi/ecosystem.config.js
ExecReload=/usr/bin/pm2 reload all
ExecStop=/usr/bin/pm2 kill
Restart=on-failure

[Install]
WantedBy=multi-user.target

8.4 Environment Configuration

# /opt/spicenvybe-bi/.env (example, actual values in vault)
# Homelab config — will differ for production

# Database
DATABASE_URL="postgresql://spicenvybe:${DB_PASS}@localhost:5432/spicenvybe_bi"
REDIS_URL="redis://:${REDIS_PASS}@localhost:6379"

# Encryption (used for stored credentials)
CREDENTIAL_ENCRYPTION_KEY="<aes-256-gcm-key-32-bytes-base64>"

# JWT
JWT_SECRET="<random-64-char>"
JWT_ACCESS_EXPIRY="2h"
JWT_REFRESH_EXPIRY="14d"

# QuickBooks Online
QBO_CLIENT_ID="<from-intuit-developer-portal>"
QBO_CLIENT_SECRET="<from-intuit-developer-portal>"
QBO_REDIRECT_URI="https://bi.spicenvybe.com/api/v1/auth/qbo/callback"
QBO_WEBHOOK_VERIFIER="<from-intuit-webhooks>"
QBO_MINOR_VERSION="75"

# Plaid
PLAID_CLIENT_ID="<from-plaid-dashboard>"
PLAID_SECRET="<from-plaid-dashboard>"
PLAID_ENVIRONMENT="sandbox"  # Change to 'production' for prod
PLAID_WEBHOOK_SECRET="<from-plaid-webhooks>"

# Email (Gmail App Password)
ALERT_EMAIL_USER="alerts@spicenvybe.com"
ALERT_EMAIL_PASS="<gmail-app-password>"
ALERT_EMAIL_FROM="Spice N'Vybe BI <alerts@spicenvybe.com>"

# Frontend
NEXT_PUBLIC_API_URL="https://bi.spicenvybe.com/api/v1"

8.5 Backup Strategy

Schedule: Daily at 12:00 AM
Target: PostgreSQL dump + Redis RDB snapshot
Retention: 7 daily + 4 weekly + 3 monthly

pg_dump -U spicenvybe -Fc spicenvybe_bi > /mnt/spicenvybe-backups/bi-$(date +%Y%m%d).dump
redis-cli SAVE  # Creates dump.rdb
cp /var/lib/redis/dump.rdb /mnt/spicenvybe-backups/redis-$(date +%Y%m%d).rdb

Cleanup: Keep 7 days locally. Optional S3 sync for compliance.

9. AWS Migration & Scaling Strategy

9.1 Target AWS Architecture

┌──────────────────────────────────────────────────────────────────────────┐
│                              AWS Cloud                                    │
│                                                                           │
│  ┌──────────────────────────────────────┐                                │
│  │           Route 53                    │                                │
│  │     bi.spicenvybe.com                │                                │
│  └──────────────┬───────────────────────┘                                │
│                 │                                                         │
│  ┌──────────────▼───────────────────────┐                                │
│  │      CloudFront CDN                  │                                │
│  │  (Static assets, Next.js SSR cache) │                                │
│  └──────────────┬───────────────────────┘                                │
│                 │                                                         │
│  ┌──────────────▼───────────────────────┐   ┌─────────────────────────┐  │
│  │        Application Load Balancer      │   │  WAF (Web ACL)          │  │
│  │        (HTTPS termination)            │   │  Rate limiting, SQLi,   │  │
│  └──────────────┬───────────────────────┘   │  XSS protection          │  │
│                 │                           └─────────────────────────┘  │
│                 ▼                                                         │
│  ┌──────────────────────────────┐  ┌──────────────────────────────────┐  │
│  │  ECS Fargate Cluster (app)   │  │  ElastiCache Redis               │  │
│  │                              │  │  (Clustered, multi-AZ)           │  │
│  │  Service: next-app (x2)      │  └──────────────────────────────────┘  │
│  │  Service: express-api (x2)   │                                       │
│  │  Service: ingestion-queue    │  ┌──────────────────────────────────┐  │
│  │  Service: scheduler          │  │  RDS PostgreSQL                   │  │
│  │                             │  │  (db.t3.medium → db.r6g.large)    │  │
│  │  Auto-scaling:               │  │  Multi-AZ standby                 │  │
│  │  CPU > 70% → +1 container   │  └──────────────────────────────────┘  │
│  │  Scale max: 4 per service   │                                       │
│  └──────────────────────────────┘                                       │
│                                                                           │
│  ┌──────────────────────┐  ┌──────────────────────┐                     │
│  │  S3 (static assets)  │  │  Secrets Manager     │                     │
│  └──────────────────────┘  └──────────────────────┘                     │
└──────────────────────────────────────────────────────────────────────────┘

9.2 Migration Phasing

PHASE 1 — HOMELAB (Now)
├── Complete system running in Proxmox LXC
├── PostgreSQL + Redis on same container
├── nginx self-signed certs
├── Daily backups to host ZFS pool
└── Monitoring: pm2 + systemd

PHASE 2 — AWS LIFT-AND-SHIFT (Month 1)
├── Deploy to single EC2 t3.medium (seed data)
├── RDS PostgreSQL (db.t3.small, Multi-AZ)
├── ElastiCache Redis (1 node, test)
├── Route 53 DNS cutover
├── CloudFront for static assets
└── ACM cert (Let's Encrypt or AWS)

PHASE 3 — CONTAINERIZATION (Month 2)
├── Dockerize all services
├── ECS Fargate deployment
├── ALB + WAF in front
├── Auto-scaling policies
└── Blue-green deployments

PHASE 4 — OPTIMIZATION (Month 3+)
├── RDS read replica for reporting queries
├── CloudFront for Next.js ISR caching
├── Redis cluster for queue resilience
├── S3 → Athena for long-term analytics
└── CloudWatch alarms + PagerDuty

9.3 Scaling Dimensions

Dimension	Homelab Limit	AWS Scaling Strategy
Database connections	100 (PostgreSQL default)	RDS Proxy + connection pooling
API throughput	Single Express process	ECS Fargate horizontal scaling
Redis memory	1GB host limit	ElastiCache: up to 340GB clustered
Storage	30GB LXC disk	RDS storage auto-scaling (up to 64TB)
Backups	Local ZFS	RDS automated backups + S3 exports
Ingestion workers	Single worker process	Worker service with concurrency config
Alert delivery	Nodemailer direct	Amazon SES (send 50K+ emails/day)

9.4 Production Readiness Checklist

Before going live with Phase 2:
┌─────────────────────────────────────────────────────────┐
│ ☐ Database migration scripts version-controlled         │
│ ☐ Secrets in AWS Secrets Manager (not .env)             │
│ ☐ WAF rules enabled (rate limiting, SQLi, XSS)          │
│ ☐ RDS automated backups configured (7-day retention)    │
│ ☐ Multi-AZ RDS enabled                                  │
│ ☐ Health check endpoints for all services               │
│ ☐ Prometheus/Grafana monitoring (or CloudWatch)          │
│ ☐ Log aggregation (CloudWatch Logs or Loki)             │
│ ☐ Deployment pipeline (GitHub Actions → ECR → ECS)      │
│ ☐ Rollback procedure documented and tested              │
│ ☐ Incident response runbook for each failure scenario   │
│ ☐ Load test executed (k6 or Artillery)                  │
│ ☐ SSL certificate → auto-renewal configured             │
│ ☐ Email deliverability tested (SPF, DKIM, DMARC)        │
│ ☐ QBO Production app credentials (separate from sandbox)│
│ ☐ Plaid Production credentials (replace sandbox keys)   │
└─────────────────────────────────────────────────────────┘

10. Data Reconciliation & Consistency Strategy

10.1 Three-Way Verification Model

╔══════════════════════════════════════════════════════════════════════╗
║                    RECONCILIATION FRAMEWORK                          ║
╠══════════════════════════════════════════════════════════════════════╣
║                                                                      ║
║  KPI: Sales                                                          ║
║  ──────────────────────────────────────────────────────────────────  ║
║  PRIMARY: SpotOn Orders (sum of completed order totals)              ║
║  VERIFY:  Plaid Bank Deposits (revenue-size credits)                 ║
║  GOLDEN:  QBO P&L Income line (monthly anchor)                      ║
║                                                                      ║
║  Reconciliation:                                                     ║
║    Daily: SpotOn sales ≈ Plaid deposits (allow ±5% for tips/cash)   ║
║    Monthly: SpotOn total vs QBO P&L Income (must match ±1%)         ║
║    If variance: log alert, flag for manual review                   ║
║                                                                      ║
║  KPI: Costs                                                          ║
║  ──────────────────────────────────────────────────────────────────  ║
║  PRIMARY: QBO (COGS from Purchase/Bill, Expenses from Account)       ║
║  SUPPLEMENT: Plaid (categorized bank transactions for misc costs)    ║
║  LABOR: SpotOn Labor Reports (wages + taxes, by location)           ║
║                                                                      ║
║  Reconciliation:                                                     ║
║    Monthly: QBO Cost totals ≈ Plaid expense categories (allowing    ║
║             for non-bank costs like credit card auto-pay)            ║
║    Weekly: Labor cost (SpotOn) vs QBO Payroll account activity      ║
║                                                                      ║
║  KPI: Net Profit                                                     ║
║  ──────────────────────────────────────────────────────────────────  ║
║  CALCULATED: agg_daily_profit_loss (Sales - Costs)                   ║
║  VERIFY:    QBO P&L Net Income (direct report)                       ║
║                                                                      ║
║  Reconciliation:                                                     ║
║    Monthly: Calculated net profit must match QBO P&L within 5%      ║
║    If mismatch: flag all three sources for variance analysis         ║
╚══════════════════════════════════════════════════════════════════════╝

10.2 Daily Reconciliation Pipeline

Daily at 7:00 AM (after all overnight syncs completed)

STEP 1: Extract SpotOn Daily Sales
  SELECT location_id, sale_date, net_sales FROM agg_daily_sales

STEP 2: Extract Plaid Daily Deposits
  SELECT location_id, transaction_date,
         SUM(ABS(amount)) AS total_deposits
  FROM normalized_bank_transactions
  WHERE amount < 0 AND is_reconciled = false
  GROUP BY location_id, transaction_date

STEP 3: Match & Update
  For each location, for each date:
    Compare SpotOn net_sales vs Plaid deposits
    If within threshold (default 5%) → mark Reconciled
    If outside → queue WARNING alert

STEP 4: Update agg_daily_reconciliation materialized view
  REFRESH MATERIALIZED VIEW CONCURRENTLY agg_daily_reconciliation;

10.3 Monthly Reconciliation Pipeline

1st of every month at 6:00 AM

STEP 1: Calculate from Aggregations
  SELECT location_id, month,
         SUM(revenue) AS total_revenue_calc,
         SUM(costs) AS total_costs_calc,
         SUM(profit) AS net_profit_calc
  FROM agg_monthly_comparison
  WHERE month = date_trunc('month', CURRENT_DATE - INTERVAL '1 month')
  GROUP BY location_id, month

STEP 2: Fetch QBO P&L Report
  Poll QBO for the completed month's P&L report
  Parse: parse ProfitAndLossReport columns:
    - Income Total
    - COGS Total
    - Expenses Total
    - Net Income

STEP 3: Compare
  Revenue: calc vs QBO income total
  Costs: calc vs QBO (COGS + Expenses)
  Net Profit: calc vs QBO Net Income

STEP 4: Adjust or Alert
  If variance < 1% → mark as fully reconciled
  If variance 1-5% → log WARNING, trigger review
  If variance > 5% → log ERROR, flag for immediate investigation

10.4 Handling Known Reconciliation Gaps

┌──────────────────────────────────────────────────────────────────────┐
│  GAP: SpotOn includes sales tax collected; QBO excludes tax          │
│  FIX:  Track tax separately, deduct from SpotOn before comparison   │
│                                                                      │
│  GAP: Plaid deposits include credit card fees (net deposit)          │
│  FIX:  Apply estimated fee percentage (2.5-3.5%) when matching       │
│  FUTURE: Map exact fees from QBO Payment Processing cost category    │
│                                                                      │
│  GAP: Cash sales don't appear as Plaid deposits                     │
│  FIX:  Track cash percentage from SpotOn, exclude from reconciliation│
│  FUTURE: Compare cash sales against cash drops recorded in QBO      │
│                                                                      │
│  GAP: QBO costs include non-cash items (depreciation, accruals)     │
│  FIX:  Flag these categories in normalized_qbo_costs, exclude from   │
│        cash-flow comparison but include in P&L comparison           │
│                                                                      │
│  GAP: Timing differences (e.g., weekend batches settle Monday)      │
│  FIX:  Allow ±2 business day window for SpotOn→Plaid reconciliation │
│        Monthly reconciliation resolves all timing gaps definitively │
└──────────────────────────────────────────────────────────────────────┘

10.5 Data Integrity Checks (Automated)

-- Check 1: Orphaned raw records without normalized counterpart
SELECT 'SpotOn Orders' AS source, COUNT(*) AS orphaned
FROM raw_spoton_orders r
LEFT JOIN normalized_orders n ON r.spoton_order_id = n.spoton_order_id
WHERE n.id IS NULL AND r.ingested_at < NOW() - INTERVAL '1 hour'
UNION ALL
SELECT 'Plaid Transactions', COUNT(*)
FROM raw_plaid_transactions r
LEFT JOIN normalized_bank_transactions n ON r.plaid_txn_id = n.plaid_txn_id
WHERE n.id IS NULL AND r.ingested_at < NOW() - INTERVAL '1 hour';

-- Check 2: Duplicate detection
SELECT spoton_order_id, location_id, COUNT(*) as dupes
FROM normalized_orders
GROUP BY spoton_order_id, location_id
HAVING COUNT(*) > 1;

-- Check 3: Gap detection (business hours with no data)
SELECT l.name, gs.date_gap AS missing_date
FROM meta_locations l
CROSS JOIN generate_series(
    CURRENT_DATE - INTERVAL '7 days', CURRENT_DATE - INTERVAL '1 day', '1 day'::interval
) AS gs(date_gap)
LEFT JOIN agg_daily_sales s ON s.location_id = l.id AND s.sale_date = gs.date_gap
WHERE s.order_count IS NULL OR s.order_count = 0;

-- Check 4: Negative cost amounts (data quality)
SELECT * FROM normalized_qbo_costs
WHERE amount < 0 AND cost_category NOT IN ('refund', 'credit');

-- Check 5: Labor % sanity (should be 10-50% of sales)
SELECT * FROM normalized_labor_costs
WHERE labor_percent < 0 OR labor_percent > 60;

Appendix A: Project Directory Structure

/opt/spicenvybe-bi/
├── apps/
│   ├── web/                     # Next.js frontend
│   │   ├── src/
│   │   │   ├── app/            # App router pages
│   │   │   ├── components/     # React components
│   │   │   ├── hooks/          # Custom hooks
│   │   │   ├── lib/            # Utilities
│   │   │   └── types/          # TypeScript types
│   │   ├── public/             # Static assets
│   │   ├── next.config.js
│   │   ├── tailwind.config.js
│   │   └── package.json
│   │
│   └── api/                    # Express backend
│       ├── src/
│       │   ├── routes/         # Express route handlers
│       │   ├── services/       # Business logic
│       │   ├── adapters/       # External API adapters
│       │   │   ├── qbo/       # QBO OAuth + CDC
│       │   │   ├── plaid/     # Plaid sync
│       │   │   └── spoton/    # SpotOn polling
│       │   ├── workers/       # BullMQ job processors
│       │   ├── middleware/    # Auth, rate-limit, error handling
│       │   ├── alerts/        # Alert engine
│       │   └── jobs/          # Cron job definitions
│       │   └── index.ts       # Entry point
│       ├── prisma/
│       │   └── schema.prisma  # Database schema
│       └── package.json
│
├── docker/
│   ├── Dockerfile.web
│   ├── Dockerfile.api
│   ├── docker-compose.yml     # Local dev
│   └── nginx/
│       ├── nginx.conf
│       └── sites-available/bi.spicenvybe.com
│
├── scripts/
│   ├── backup.sh               # Daily backup
│   ├── restore.sh              # Disaster recovery
│   ├── health-check.sh         # System health
│   └── seed-data.sql           # Initial seed
│
├── infra/
│   ├── terraform/              # AWS IaC (Phase 2+)
│   │   ├── main.tf
│   │   ├── variables.tf
│   │   └── outputs.tf
│   └── ansible/               # LXC provisioning
│       ├── playbook.yml
│       └── roles/
│
├── .env.example
├── ecosystem.config.js         # PM2 config
└── README.md

Appendix B: BullMQ Job Definitions

// ecosystem.config.js worker concurrency
module.exports = {
    apps: [
        {
            name: 'ingestion-worker',
            script: 'dist/workers/index.js',
            env: {
                WORKER_CONCURRENCY: 5,
                QBO_QUEUE_CONCURRENCY: 2,  // Respect 500 req/min
                PLAID_QUEUE_CONCURRENCY: 3,
                SPOTON_QUEUE_CONCURRENCY: 3
            }
        }
    ]
};

// BullMQ Queue Definitions
const queues = {
    qboSync: {
        name: 'qbo-sync',
        defaultJobOptions: {
            attempts: 3,
            backoff: { type: 'exponential', delay: 2000 },
            removeOnComplete: 100,
            removeOnFail: 50
        }
    },
    plaidSync: {
        name: 'plaid-sync',
        defaultJobOptions: {
            attempts: 3,
            backoff: { type: 'fixed', delay: 30000 },
            removeOnComplete: 100
        }
    },
    spotonSync: {
        name: 'spoton-sync',
        defaultJobOptions: {
            attempts: 2,
            backoff: { type: 'exponential', delay: 1000 },
            removeOnComplete: 200
        }
    },
    reconciliation: {
        name: 'reconciliation',
        defaultJobOptions: {
            attempts: 1,  // No retry — alert on failure
            removeOnComplete: 30
        }
    }
};

Appendix C: Cron Schedule Reference

┌───────── Source ─────────┬─────── Cron ───────┬─────────── Description ─────────────────┐
│ QBO CDC Poll             │ */30 * * * *       │ CDC sync every 30 minutes               │
│ QBO Full Sync            │ 0 2 * * *          │ Full CDC + P&L report at 2am            │
│ QBO Token Refresh        │ */50 * * * *       │ Token refresh every 50 min (before expiry)│
│ QBO Weekly Integrity     │ 0 3 * * 0          │ Full entity query on Sunday             │
│                           │                    │                                          │
│ Plaid Incremental        │ */15 * * * *       │ Incremental transaction sync             │
│ Plaid Full Resync        │ 0 4 * * 0          │ Full resync every Sunday at 4am         │
│                           │                    │                                          │
│ SpotOn Orders (BH)       │ */5 8-23 * * 1-6   │ 5-min polls during business hours       │
│ SpotOn Orders (Sun BH)   │ */5 9-21 * * 0     │ 5-min polls Sunday hours                │
│ SpotOn Deep Sync         │ 0 3 * * *          │ 26h deep sync daily at 3am              │
│ SpotOn Labor Reports     │ 0 6 * * *          │ Labor report fetch at 6am               │
│                           │                    │                                          │
│ Daily Reconciliation     │ 0 7 * * *          │ Sales × Plaid match at 7am              │
│ Monthly Reconciliation   │ 0 6 1 * *          │ Full QBO P&L reconciliation on 1st      │
│ Database Backup          │ 0 0 * * *          │ PostgreSQL dump + Redis SAVE at midnight │
│ Data Freshness Check     │ 0 8 * * *          │ Integrity checks: orphans, gaps, dupes  │
│                           │                    │                                          │
│ Token Expiry Check       │ 0 9 * * *          │ Check QBO token <7d expiry → SECURITY    │
│ Dead Letter Alert        │ 0 * * * *          │ Check BullMQ DLQ, create FAILURE alerts  │
└──────────────────────────┴────────────────────┴──────────────────────────────────────────┘

Note: All times in UTC unless otherwise noted. Business hours (BH) follow local Eastern Time.

Appendix D: Key Design Decisions & Rationale

Decision	Choice	Rationale
Warehouse pattern (not direct query)	Ingestion Workers → Normalization → Aggregation	Decouples API failures from dashboard. Dashboard always works from last-good data. Rate limits don't impact UX.
BullMQ over in-process queue	Redis-backed BullMQ	Survives server restarts. Provides retry, DLQ, concurrency control. Monitoring dashboard for job health.
Materialized Views over computed-at-query	Pre-aggregated	Dashboard loads in <100ms regardless of data volume. Refresh on schedule. Concurrent refresh for zero-downtime.
Separate RAW + NORMALIZED tables	Dual-table pattern	Raw = immutable audit log (reprocessable). Normalized = cleaned, typed, indexed for query. Never lose original data.
CDC over polling for QBO	Change Data Capture primary	500 req/min limit makes entity-level polling infeasible. CDC returns all changed entities in 2-3 calls vs 20+ individual fetches.
SpotOn 5-min + 26h dual sync	Hybrid polling	5-min catches real-time orders. 26h deep sync catches missed records. Compensates for SpotOn's limited API window.
Reconciliation as separate pipeline stage	Post-ingestion verification	Don't trust any single source. Cross-verify every KPI against a secondary source. Catch issues before they reach the dashboard.
PM2 over Docker in homelab	Process manager	Simpler networking, lower overhead, easier debugging in single-LXC setup. Docker reserved for AWS migration phase.
SWR over WebSockets	Stale-while-revalidate	Simplified architecture for v1. Dashboard only needs 120s refresh. WebSockets add complexity (reconnection, state sync) without proportional benefit.
Gmail App Password over SMTP service	Nodemailer + Gmail	Free, zero configuration for homelab. Replace with SES on AWS for production volume and deliverability.

End of Architectural Specification v1.0

This document serves as the authoritative blueprint for all M01 development. All changes and deviations must be documented and approved through the project's change management process.

95 KiB Raw Permalink Blame History Unescape Escape