#101 bug: auto sync not runned

There is one shoprenter shop id: 3e8bbd69-e20f-40ee-9e15-2056762988f6

In the store_sync_config the next_sync_at says: 2025-11-19 15:17, last sync: 2025-11-19 14:17. Now is 17:18 utc+1, which means 16:18 UTC. So checkout why the store not auto synced at 15:00 UTC. see the supabase edge functions logs.

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

I'll investigate why the ShopRenter store (id: 3e8bbd69-e20f-40ee-9e15-2056762988f6) did not auto-sync at the scheduled time.

My plan:

1. Check Supabase Edge Function logs for the shoprenter-scheduled-sync function
2. Verify pg_cron job status and configuration
3. Check the store_sync_config settings for this store
4. Investigate the trigger function and any potential errors
5. Identify and fix the root cause

Let me start by examining the logs and database state.

Investigation Complete

@fszontagh After thorough investigation of the logs and database, I've found the root cause of why the auto-sync didn't run at the expected times.

Timeline Analysis

Evidence

1. pg_cron jobs executed successfully at every hour (15:00, 16:00, 17:00)

2. Edge Function responses from net._http_response:

   • 15:00 UTC: "No stores due for sync" (correct - next_sync_at was 15:17)
   • 16:00 UTC: "No stores due for sync" (store was disabled)
   • 17:00 UTC: Sync completed successfully

3. API logs show toggle activity: Multiple /api/stores/{id}/enable calls between 15:27-15:32 and 16:40-16:47, indicating the store was being toggled on/off during this period.

4. sync_logs table shows only one successful sync at 17:00:04 UTC.

Why 15:00 UTC sync was skipped

The store was connected at 14:17 UTC, and the trigger calculated:

next_sync_at = NOW() + 1 hour = 15:17 UTC

At 15:00 UTC, the scheduled sync correctly determined that next_sync_at (15:17) > NOW (15:00), so the store wasn't due for sync yet.

Why 16:00 UTC sync was skipped

At 16:00 UTC, the store was disabled. The Edge Function filters stores with:

.eq('is_active', true)
// AND
config.enabled === true

Since the store was disabled at that time (evident from the enable/disable toggle API calls), it was correctly skipped.

Conclusion

The scheduled sync system is working correctly! ✅

The store wasn't synced because:

1. At 15:00 - It wasn't due yet (next_sync_at was 15:17)
2. At 16:00 - It was disabled

This is expected behavior. The sync ran successfully at 17:00 when the store was both enabled and due.

Recommendation

If you want syncs to run exactly on the hour regardless of when the store was connected, we could modify the next_sync_at calculation to round to the next full hour. Would you like me to implement this change?

No, keep the sync time as - is.

But there is an error at auto sync:

"[ShopRenter Scheduled Sync] Error caching products for store 3e8bbd69-e20f-40ee-9e15-2056762988: {\n code: \"PGRST204\",\n details: null,\n hint: null,\n message: \"Could not find the 'active' column of 'shoprenter_products_cache' in the schema cache\"\n}\n"

It's hapened at 20:00:03, SQL error.

@fszontagh Thanks for the clarification! I'll keep the sync time as-is.

Now investigating the SQL error you reported:

Could not find the 'active' column of 'shoprenter_products_cache' in the schema cache

This is a PGRST204 error indicating that the code is trying to use an active column that doesn't exist in the shoprenter_products_cache table. I'll check the database schema and the sync function to identify and fix this column mismatch.

Bug Fixed ✅

@fszontagh I've identified and fixed the SQL error you reported.

Root Cause

The shoprenter-scheduled-sync Edge Function was trying to insert data into the shoprenter_products_cache table using a non-existent schema with individual columns:

// WRONG - These columns don't exist in the database
{
  store_id: storeId,
  shoprenter_product_id: product.id,
  name: product.name,        // ❌ doesn't exist
  sku: product.sku,          // ❌ doesn't exist
  price: parseFloat(...),    // ❌ doesn't exist
  currency: ...,             // ❌ doesn't exist
  description: ...,          // ❌ doesn't exist
  stock: ...,                // ❌ doesn't exist
  active: ...,               // ❌ <-- The error was here!
  raw_data: product,         // ❌ doesn't exist
  last_synced_at: ...
}

The actual database schema for shoprenter_products_cache only has:

• id, store_id, shoprenter_product_id, product_data (jsonb), last_synced_at, created_at, updated_at

Fix Applied

Updated shoprenter-scheduled-sync/index.ts to use the correct schema (matching shoprenter-sync/index.ts):

// CORRECT - Matches actual database schema
{
  store_id: storeId,
  shoprenter_product_id: product.id,
  product_data: product,  // ✅ jsonb column
  last_synced_at: new Date().toISOString()
}

Deployment Status

• ✅ Code fixed and committed: 29108c9
• ✅ Edge Function deployed to Supabase

The next scheduled sync should work correctly. You can monitor the logs to verify the fix.