#30 fix: Add phone number fields to WooCommerce cache tables

Problem

Customer phone numbers are not being collected from WooCommerce stores, despite being available in the API responses.

Current State:

• ❌ woocommerce_customers_cache.phone - Field does NOT exist
• ❌ woocommerce_orders_cache.customer_phone - Field does NOT exist
• ✅ woocommerce_customers_cache.email - Working
• ✅ woocommerce_orders_cache.customer_email - Working

Impact:

• Cannot call customers from WooCommerce stores
• Missing critical contact information for customer outreach

Solution

Database Migration

Add phone number columns to existing WooCommerce cache tables:

1. Add phone TEXT to woocommerce_customers_cache table
2. Add customer_phone TEXT to woocommerce_orders_cache table

Code Changes

Update supabase/functions/woocommerce-sync/index.ts:

In syncCustomers() function (line ~245):

const customersToCache = customers.map((customer: WooCommerceCustomer) => ({
  // ... existing fields ...
  phone: customer.billing?.phone || null, // ADD THIS LINE
  // ... rest of fields ...
}))

In syncOrders() function (line ~172):

const ordersToCache = orders.map((order: WooCommerceOrder) => ({
  // ... existing fields ...
  customer_phone: order.billing?.phone || null, // ADD THIS LINE
  // ... rest of fields ...
}))

API Data Availability

Phone numbers ARE available in WooCommerce REST API v3:

• Customer API: customer.billing.phone
• Order API: order.billing.phone

See: https://woocommerce.github.io/woocommerce-rest-api-docs

Related

Parent issue: #29 (feat: customers phone number)

verify if this task is completed. If yes, you allowed to close this issue

@fszontagh I'm starting verification of this task now! I'll check:

1. Database migration for phone fields
2. Code changes in woocommerce-sync function
3. Edge Function deployment status

Will report back with findings shortly.

✅ Task Verification Complete

@fszontagh I've verified that this task has been fully completed. Here's the comprehensive status:

1. ✅ Database Migration Applied

Migration File: 20251031_add_woocommerce_phone_fields.sql

Database Schema Verified:

• ✅ woocommerce_customers_cache.phone - Column exists (TEXT, nullable)
• ✅ woocommerce_orders_cache.customer_phone - Column exists (TEXT, nullable)
• ✅ idx_wc_customers_phone - Index created on (store_id, phone)
• ✅ idx_wc_orders_customer_phone - Index created on (store_id, customer_phone)

2. ✅ Code Changes Implemented

File: supabase/functions/woocommerce-sync/index.ts

Line 181 (syncOrders function):

customer_phone: order.billing?.phone || null,

Line 253 (syncCustomers function):

phone: customer.billing?.phone || null,

Both functions now correctly extract phone numbers from the WooCommerce API billing data.

3. ✅ Edge Function Deployed

• Function: woocommerce-sync
• Version: 6 (deployed after code changes)
• Status: ACTIVE
• Last Updated: 2025-10-31 10:50:00 (same time as commit)
• SHA256: c29936df9048e3b47e2aec704810928919bd5d87584eac608006dec8a804a058

4. ✅ Git Commit Pushed

Commit: 81a33b12f8ca62c8509cfa6a5556dff7641a7032

• Message properly references issue #30
• Includes both migration and code changes
• Successfully pushed to repository

Summary

All requirements from issue #30 have been completed:

1. ✅ Database columns added with proper indexes
2. ✅ Code updated to extract phone numbers from API
3. ✅ Edge Function deployed with latest changes
4. ✅ Changes committed and pushed to git

Next Steps: Existing WooCommerce stores should run a manual sync to populate the phone number fields with data from their stores.

This issue can be safely closed. 🎉

Issue closed after successful verification. All components (database migration, code changes, Edge Function deployment, and git commits) have been confirmed to be properly implemented and deployed. ✅