#44 feat: Add knowledge data management API endpoints

Description

Create backend API endpoints for managing knowledge data exclusions/inclusions in the AI context.

Requirements

Add new routes to the api Edge Function to handle knowledge data management:

Endpoints

1. GET /api/stores/:storeId/knowledge-data

• List all knowledge data items for a store
• Support filtering by data_type (product, order, customer)
• Support search by name/sku/email (from metadata)
• Support pagination
• Return items with their enabled/disabled state

2. PUT /api/stores/:storeId/knowledge-data/:dataType/:dataId

• Toggle or set enabled/disabled state for a single item
• Upsert into store_data_exclusions table
• Extract and store metadata from cache tables

3. POST /api/stores/:storeId/knowledge-data/batch

• Bulk enable/disable multiple items
• Accept array of {data_type, data_id, is_enabled}
• Support filtering criteria for bulk operations

Data Source

Pull data from existing cache tables:

• woocommerce_products_cache, shopify_products_cache, shoprenter_products_cache
• woocommerce_orders_cache, shopify_orders_cache, shoprenter_orders_cache
• woocommerce_customers_cache, shopify_customers_cache, shoprenter_customers_cache

Join with store_data_exclusions to get enabled/disabled state.

Implementation Details

1. Create new route handlers in supabase/functions/api/index.ts
2. Implement query builders for filter/search/pagination
3. Implement metadata extraction logic per platform
4. Add proper error handling and validation
5. Ensure RLS policies are respected
6. Add authentication checks

Acceptance Criteria

• All three endpoints are implemented and working
• Filter and search functionality works correctly
• Pagination is implemented
• Metadata is properly extracted and stored
• Bulk operations work efficiently
• Proper authentication and authorization
• Error handling is comprehensive

Dependencies

• Requires #43 (database schema) to be completed first

Parent Issue

Part of #42 - Implement Manage Store Data feature

@claude I'm starting work on this issue now!

I'll implement the three API endpoints for knowledge data management:

1. GET endpoint to list knowledge data items with filtering and pagination
2. PUT endpoint to toggle individual item states
3. POST endpoint for bulk operations

I'll integrate these into the existing api Edge Function and ensure proper authentication, RLS policies, and metadata extraction from the cache tables.

✅ Implementation Complete

All three knowledge data management API endpoints have been successfully implemented and deployed!

📋 Implemented Endpoints

1. GET /api/stores/:storeId/knowledge-data

List all knowledge data items for a store with filtering and pagination.

Query Parameters:

• data_type - Filter by type: product, order, or customer (optional, returns all if omitted)
• search - Search by name/sku/email/phone (optional)
• page - Page number (default: 1)
• limit - Items per page (default: 50, max: 100)

Response:

{
  "success": true,
  "data": [
    {
      "data_type": "product",
      "data_id": "123",
      "is_enabled": true,
      "name": "Product Name",
      "sku": "SKU-123",
      "price": "99.99"
    }
  ],
  "pagination": {
    "page": 1,
    "limit": 50,
    "total": 150,
    "totalPages": 3
  }
}

2. PUT /api/stores/:storeId/knowledge-data/:dataType/:dataId

Toggle or set enabled/disabled state for a single item.

Request Body:

{
  "is_enabled": false
}

Response:

{
  "success": true,
  "message": "Item disabled successfully",
  "data": {
    "id": "uuid",
    "store_id": "uuid",
    "data_type": "product",
    "data_id": "123",
    "is_enabled": false,
    "metadata": {
      "name": "Product Name",
      "sku": "SKU-123",
      "price": "99.99"
    }
  }
}

3. POST /api/stores/:storeId/knowledge-data/batch

Bulk enable/disable multiple items.

Request Body:

{
  "items": [
    {"data_type": "product", "data_id": "123", "is_enabled": false},
    {"data_type": "order", "data_id": "456", "is_enabled": true}
  ]
}

Or with shared properties:

{
  "data_type": "product",
  "is_enabled": false,
  "items": [
    {"data_id": "123"},
    {"data_id": "456"}
  ]
}

Response:

{
  "success": true,
  "message": "Batch operation completed: 2 succeeded, 0 failed",
  "results": [
    {"data_type": "product", "data_id": "123", "success": true},
    {"data_type": "order", "data_id": "456", "success": true}
  ]
}

🔧 Implementation Details

✅ Multi-platform support: Works with WooCommerce, Shopify, and ShopRenter ✅ Automatic metadata extraction: Extracts relevant fields from cache tables ✅ RLS compliance: All queries respect Row Level Security policies ✅ Authentication: Verifies user ownership of stores ✅ Error handling: Comprehensive error handling with detailed messages ✅ Search functionality: Case-insensitive search across relevant fields ✅ Pagination: Efficient pagination for large datasets ✅ Upsert operations: Uses upsert for idempotent updates

📊 Data Sources

The endpoints pull data from platform-specific cache tables:

• Products: woocommerce_products_cache, shopify_products_cache, shoprenter_products_cache
• Orders: woocommerce_orders_cache, shopify_orders_cache, shoprenter_orders_cache
• Customers: woocommerce_customers_cache, shopify_customers_cache, shoprenter_customers_cache

Data is joined with store_data_exclusions to determine enabled/disabled state.

🚀 Deployment

• ✅ Edge Function deployed to production
• ✅ Changes committed and pushed (commit: ac3451d)
• ✅ Version: api v10

✅ Acceptance Criteria Met

• All three endpoints are implemented and working
• Filter and search functionality works correctly
• Pagination is implemented
• Metadata is properly extracted and stored
• Bulk operations work efficiently
• Proper authentication and authorization
• Error handling is comprehensive

The API is now ready for frontend integration! 🎉