Task 7: Square Sales Data Synchronization

[akisma]

Priority: P0 - Critical
Estimate: 4 days
Dependencies: Task 6 (Issue #20)

Product Acceptance Criteria

(Customer Value)

User Story: As a restaurant manager, I want to manually trigger Square sales data synchronization with CostFX, so that I can control when sales data is imported and validate the sync process step-by-step.

Acceptance Scenarios:

• Manual Sales Sync: Given my restaurant has sales in Square, When I trigger a manual sales sync through the API, Then all transactions from the specified date range should be imported for cost analysis
• Large Volume Handling: Given busy days with 1000+ transactions, When CostFX syncs sales data, Then all transactions should be processed without timeout errors or missing data
• Menu Item Mapping: Given my Square sales show specific menu items sold, When CostFX processes this data, Then each menu item should be properly linked to its recipe ingredients for usage calculations
• Duplicate Prevention: Given the same transaction might be synced multiple times, When CostFX imports sales data, Then duplicate transactions should be ignored to prevent inflated usage calculations
• Historical Backfill: Given I'm setting up CostFX for the first time, When I connect my Square account, Then I should be able to manually import sales history from a specified date range for immediate analysis
• Stepped Sync Process: Given I want to validate each sync phase, When I trigger a sales sync, Then I should be able to run raw data sync separately from transformation (like inventory sync)

Technical Implementation Checklist

(Developer Tasks)

Description:
Implement manual Square sales transaction synchronization with menu item mapping, following the same stepped pattern as SquareInventorySyncService.

Technical Acceptance Criteria:

• Manual sync endpoint for Square order transactions (no automatic scheduling)
• Two-phase sync: (1) Raw data to Tier 1, (2) Optional transform to Tier 2
• Map orders to menu items and ingredients
• Handle refunds and modifications
• Sales data aggregation by time periods
• Menu item to recipe mapping logic
• Performance optimization for high-volume locations
• Historical data backfill capability (date range parameters)

Technical Details:

// New files:
src/services/SquareSalesSyncService.js  // Orchestrates sync+transform (like SquareInventorySyncService)
src/services/SquareMenuMapping.js       // Menu item → recipe mapping
backend/migrations/TIMESTAMP_create-sales-transactions.js  // Tier 2 unified table

// Updated files:
src/adapters/SquareAdapter.js           // Implement syncSales() method (replace TODO stub)
src/services/POSDataTransformer.js      // Add squareOrderToSalesTransactions()
src/controllers/POSConnectionsController.js  // Add POST /:id/sync-sales endpoint

Phase: 3 - Square Data Synchronization (2 weeks)
Epic: Square-First Multi-POS Integration

---

📋 Additional Downstream Dependencies

This issue now has additional dependencies beyond the original theoretical usage calculations:

New Dependencies (Recipe Variance Analysis)

• Issue Recipe Variance Analysis: On-Demand Revenue Impact Calculator #41: Recipe Variance Analysis: On-Demand Revenue Impact Calculator (P0, 5 days)
• Issue Recipe Variance Analysis: Nightly Automated Batch Analysis #42: Recipe Variance Analysis: Nightly Automated Batch Analysis (P0, 3 days)
• Issue Recipe Variance Analysis: Critical Variance Alerts (Email/SMS) #43: Recipe Variance Analysis: Critical Variance Alerts (P1, 2 days)

Why These Depend on Issue #21

The recipe variance analysis system (requested by Dave for the halibut vendor example) requires the sales_transactions table to calculate revenue impact:

variance_per_plate = $1.875 (actual cost - theoretical cost)
sales_count = 100 plates (from sales_transactions)
revenue_loss = $1.875 × 100 = $187.50

Data Flow for Recipe Variance

square_orders + square_order_items (Tier 1)
    ↓
POSDataTransformer
    ↓
sales_transactions (Tier 2) ← Issue #21 creates this
    ↓
Recipe variance queries by menu item + date range
    ↓
Calculate revenue impact (variance × sales_count)

What Recipe Variance Issues Need from Issue #21

✅ sales_transactions table with proper schema (already defined)
✅ Link to inventory_item_id via source tracking (already planned)
✅ source_pos_item_id for linking to menu items (already in schema)
✅ Date range query support with indexes (already planned)
✅ Sales count aggregation capability (query pattern, not new code)

No Scope Changes Required

The current Issue #21 scope already provides everything needed for recipe variance. The recipe variance issues (41-43) will:

• Use the same sales_transactions table
• Query via recipe_menu_item_links → square_menu_items → sales_transactions
• Aggregate sales counts for variance × volume calculations

This comment is for awareness and priority planning only - no changes to Issue #21 deliverables needed.

---

Timeline Impact:

• Issue Task 7: Square Sales Data Synchronization #21 remains 4 days (unchanged)
• Issues Recipe Variance Analysis: On-Demand Revenue Impact Calculator #41-43 can start immediately after Issue Task 7: Square Sales Data Synchronization #21 completes
• Total recipe variance work: 10 additional days (after Issue Task 7: Square Sales Data Synchronization #21)

Related Documentation:

• See docs/RECIPE_VARIANCE_ANALYSIS_PLAN.md for full architecture
• See docs/RECIPE_VARIANCE_DEPENDENCY_ANALYSIS.md for dependency details

[akisma]

🏗️ Architectural Update: Sales Data Sync & Unified Transactions

Based on our two-tier architecture (Issue #18), sales synchronization follows the same pattern as inventory sync:

New Table: sales_transactions (Tier 2 - Unified)

This issue will create the unified sales format consumed by theoretical usage calculations.

CREATE TABLE sales_transactions (
  id SERIAL PRIMARY KEY,
  restaurant_id INTEGER REFERENCES restaurants(id),
  inventory_item_id INTEGER REFERENCES inventory_items(id), -- Links to UNIFIED table!
  
  -- Transaction details
  transaction_date TIMESTAMPTZ NOT NULL,
  quantity DECIMAL(10,2) NOT NULL,
  unit_price DECIMAL(10,2),
  total_amount DECIMAL(10,2),
  
  -- POS source tracking
  source_pos_provider VARCHAR(50) NOT NULL,  -- 'square', 'toast'
  source_pos_order_id VARCHAR(255) NOT NULL,
  source_pos_line_item_id VARCHAR(255),
  source_pos_data JSONB,  -- Original line item data
  
  -- Timestamps
  created_at TIMESTAMPTZ DEFAULT NOW(),
  
  -- Constraints
  CONSTRAINT unique_pos_line_item UNIQUE (source_pos_provider, source_pos_line_item_id)
);

CREATE INDEX idx_sales_trans_restaurant ON sales_transactions(restaurant_id);
CREATE INDEX idx_sales_trans_item ON sales_transactions(inventory_item_id);
CREATE INDEX idx_sales_trans_date ON sales_transactions(transaction_date DESC);
CREATE INDEX idx_sales_trans_pos_source ON sales_transactions(source_pos_provider, source_pos_order_id);

---

Two-Stage Pipeline

Stage 1: Sync Raw Square Orders (Tier 1)

Store exact Square API responses in square_orders and square_order_items tables.

Stage 2: Transform to Unified sales_transactions (Tier 2)

The POSDataTransformer maps Square orders to the unified format, linking to inventory_items via source tracking fields.

Critical: Each sales transaction links to inventory_items (unified table), NOT to square_menu_items (raw POS table).

---

Integration with Theoretical Usage

Once sales_transactions is populated, agents query it for POS-agnostic analysis:

// UsageCalculationService.js - Queries unified data only
const sales = await SalesTransaction.findAll({
  where: {
    restaurantId,
    transactionDate: { [Op.between]: [period.periodStart, period.periodEnd] }
  },
  include: [InventoryItem]  // Unified table
});

// Calculate variance (doesn't care if data came from Square or Toast!)

---

Updated Technical Acceptance Criteria

Database Schema

• Create sales_transactions table (Tier 2 - unified)
• Create SalesTransaction Sequelize model
• Add associations: SalesTransaction.belongsTo(InventoryItem)
• Migration with indexes for date range queries

Square Orders Sync (Stage 1)

• Fetch orders via ordersApi.searchOrders()
• Store in square_orders + square_order_items (Tier 1)
• Filter by date range (configurable)
• Handle pagination for high-volume locations

Transformation (Stage 2)

• Transform Square orders → sales_transactions
• Map catalog_object_id → inventory_item_id (via source tracking)
• Handle refunds (negative quantities)
• Deduplicate using source_pos_line_item_id
• Warn on unmapped items (no inventory linkage)

Integration

• Update UsageCalculationService to query sales_transactions
• Link to theoretical usage analysis
• Support multiple POS providers (Square now, Toast later)

---

Files to Create/Modify

backend/migrations/[timestamp]_create-sales-transactions.js  (NEW)
backend/src/models/SalesTransaction.js  (NEW)
backend/src/services/SquareSalesSyncService.js  (NEW)
backend/src/services/POSDataTransformer.js  (MODIFY - add sales methods)
backend/src/services/UsageCalculationService.js  (MODIFY - query sales_transactions)
backend/src/jobs/squareSalesSync.js  (NEW)

---

Why Unified Format Matters

🎯 Agents don't care about POS source!

// InventoryAgent queries sales_transactions
// Works identically whether data came from Square or Toast
const variance = await this.analyzeVariance(restaurantId, periodId);

// The agent never queries square_orders directly
// All data flows through sales_transactions (unified)

This architecture allows Toast integration (Issue #25) to work seamlessly by following the same pattern.

[akisma]

📋 Additional Downstream Dependencies

This issue now has additional dependencies beyond the original theoretical usage calculations:

New Dependencies (Recipe Variance Analysis)

• Issue Recipe Variance Analysis: On-Demand Revenue Impact Calculator #41: Recipe Variance Analysis: On-Demand Revenue Impact Calculator (P0, 5 days)
• Issue Recipe Variance Analysis: Nightly Automated Batch Analysis #42: Recipe Variance Analysis: Nightly Automated Batch Analysis (P0, 3 days)
• Issue Recipe Variance Analysis: Critical Variance Alerts (Email/SMS) #43: Recipe Variance Analysis: Critical Variance Alerts (P1, 2 days)

Why These Depend on Issue #21

The recipe variance analysis system (requested by Dave for the halibut vendor example) requires the sales_transactions table to calculate revenue impact:

variance_per_plate = $1.875 (actual cost - theoretical cost)
sales_count = 100 plates (from sales_transactions)
revenue_loss = $1.875 × 100 = $187.50

Data Flow for Recipe Variance

square_orders + square_order_items (Tier 1)
    ↓
POSDataTransformer
    ↓
sales_transactions (Tier 2) ← Issue #21 creates this
    ↓
Recipe variance queries by menu item + date range
    ↓
Calculate revenue impact (variance × sales_count)

What Recipe Variance Issues Need from Issue #21

✅ sales_transactions table with proper schema (already defined)
✅ Link to inventory_item_id via source tracking (already planned)
✅ source_pos_item_id for linking to menu items (already in schema)
✅ Date range query support with indexes (already planned)
✅ Sales count aggregation capability (query pattern, not new code)

No Scope Changes Required

The current Issue #21 scope already provides everything needed for recipe variance. The recipe variance issues (41-43) will:

• Use the same sales_transactions table
• Query via recipe_menu_item_links → square_menu_items → sales_transactions
• Aggregate sales counts for variance × volume calculations

This comment is for awareness and priority planning only - no changes to Issue #21 deliverables needed.

---

Timeline Impact:

• Issue Task 7: Square Sales Data Synchronization #21 remains 4 days (unchanged)
• Issues Recipe Variance Analysis: On-Demand Revenue Impact Calculator #41-43 can start immediately after Issue Task 7: Square Sales Data Synchronization #21 completes
• Total recipe variance work: 10 additional days (after Issue Task 7: Square Sales Data Synchronization #21)

Related Documentation:

• See docs/RECIPE_VARIANCE_ANALYSIS_PLAN.md for full architecture
• See docs/RECIPE_VARIANCE_DEPENDENCY_ANALYSIS.md for dependency details