Cannot update a record with pending changes error during bidirectional sync

[defyd-rc]

Hi,
We are getting the below error that has been bugging us for a while now:

Cannot update a record with pending changes error during bidirectional sync with batch operations - Records stuck with _preparedState set while _status='synced'

Environment

• WatermelonDB Version: v0.28.0 (last stable release)
• Platform: React Native with Expo
• Database: SQLite (via WatermelonDB adapter)
• Sync Strategy: Custom bidirectional sync implementation

---

Problem Summary

We're experiencing persistent Cannot update a record with pending changes errors during sync operations, even after implementing the recommended prepareUpdate() + batch() pattern. Records appear to have _preparedState set while their _status is marked as 'synced', causing subsequent update operations to fail.

Key Error:
ERROR [Diagnostic error: Cannot update a record with pending changes (users#381ebbaa-75ba-45e5-84b3-8bcde9b9a263)]

This occurs during pull operations (applying remote changes to local DB) across multiple sync cycles.

---

Root Cause Analysis

Our investigation revealed that records can end up with _preparedState set but _status='synced' in the following scenario:

During sync pull (applying remote changes):

1. Use prepareUpdate() or prepareCreate() to apply remote data
2. Execute database.batch() with all prepared operations
3. If any operation in the batch fails, some records may be left with _preparedState set
4. These records have _status='synced' (either from previous sync or default)
5. Future update attempts on these records fail with "Cannot update a record with pending changes"

Detection in our change collector:

// During push, we collect local changes
for (const record of allRecords) {
  const raw = record._raw as any;
  const status = raw._status;

  // We discovered records with this inconsistent state:
  const hasPreparedState =
    (record as any)._preparedState !== null &&
    (record as any)._preparedState !== undefined;

  if (hasPreparedState && status === 'synced') {
    // This should never happen, but it does!
    console.warn(
      `Record ${record.id} has _preparedState but status='synced'`
    );
  }
}

export async function applyRemoteChanges(
  database: Database,
  changes: RemoteChanges
): Promise<void> {
  await database.write(async () => {
    const batchOperations: any[] = [];

    for (const [tableName, tableChanges] of Object.entries(changes)) {
      const collection = database.get(tableName);

      // Apply updates from server
      for (const remoteRecord of tableChanges.updated) {
        const localRecords = await collection
          .query(Q.where('id', remoteRecord.id))
          .fetch();

        if (localRecords.length > 0) {
          const localRecord = localRecords[0];
          
          // Prepare update with remote data
          const preparedUpdate = localRecord.prepareUpdate((record: any) => {
            Object.keys(remoteRecord).forEach((key) => {
              if (!key.startsWith('_') && key !== 'id') {
                record[key] = remoteRecord[key];
              }
            });
          });

          batchOperations.push(preparedUpdate);
        }
      }

      // Apply creates from server
      for (const remoteRecord of tableChanges.created) {
        const preparedCreate = collection.prepareCreate((record: any) => {
          record._raw.id = remoteRecord.id;
          Object.keys(remoteRecord).forEach((key) => {
            if (!key.startsWith('_') && key !== 'id') {
              record[key] = remoteRecord[key];
            }
          });
        });

        batchOperations.push(preparedCreate);
      }
    }

    // Execute all operations atomically
    await database.batch(...batchOperations);
  });
}

Question: After this batch completes successfully, what should the _status of these records be?

• They came from the server (remote changes)
• They should be marked as "synced" so they don't get pushed back to server
• But if _status is still 'synced' and _preparedState lingers, we get errors

Observed Behavior Pattern
First sync (works fine):

1. Fetch remote changes
2. Prepare updates for 10 records
3. Execute batch successfully
4. All records updated ✓
   Second sync (error occurs):
5. Fetch remote changes (including same user record)
6. Try to prepareUpdate() on user record
7. ERROR: "Cannot update a record with pending changes (users#xyz)"
8. Sync fails ✗

What We've Tried
✅ Attempt 1: Using WatermelonDB's Built-in synchronize()

await synchronize({
  database,
  pullChanges: async ({ lastPulledAt }) => {
    const changes = await fetchChanges(lastPulledAt);
    return { changes, timestamp: Date.now() };
  },
  pushChanges: async ({ changes }) => {
    await pushChanges(changes);
  },
});

Converted all database operations to use the recommended pattern, but the error persists.
✅ Attempt 2: Separate Push and Pull Operations

// Try push first, then pull
await pushLocalChanges(database, apiClient);
await pullRemoteChanges(database, apiClient);

// Also tried pull first, then push
await pullRemoteChanges(database, apiClient);
await pushLocalChanges(database, apiClient);

Result: Same error on subsequent pull operations

✅ Attempt 3: Using Proper prepareUpdate() + batch() Pattern
Result: This also did not help.

✅ Attempt 4: Checking for Pending Changes Before Update

const hasPendingChanges = (record as any)._preparedState !== null;
if (hasPendingChanges) {
  console.warn(`Record ${record.id} has pending changes, skipping`);
  return;
}

Questions

• Why does _preparedState remain set after successful batch execution?
  • Shouldn't WatermelonDB clear _preparedState after the batch completes?
  • Is this expected behavior?
• How should records be marked after applying remote changes?
  • When we pull changes from the server and apply them locally, what should their _status be?
  • Should they be marked as 'synced' automatically?
  • How do we prevent them from being pushed back to the server?
• Is there a proper way to recover from records with stale _preparedState?
  • Can we detect this state reliably?
  • Should we clear _preparedState somehow?
  • Or is this a bug in WatermelonDB?
• What's the correct pattern for bidirectional sync with custom logic?
  • Built-in synchronize() doesn't fit our needs (need more control, handle conflicts differently)
  • How should we implement custom push/pull logic correctly?

System Information

{
  "watermelondb": "0.28.0",
  "react-native": "0.76.x",
  "expo": "~52.0.x",
  "platform": "iOS & Android",
  "database": "SQLite"
}

Additional Context

• Multiple tables (~20) with complex relationships
• Sync happens frequently (every 30-60 seconds)
• Offline-first architecture with real-time requirements
• Using batch operations extensively throughout the codebase
• Error appears to be timing-related (happens more with frequent syncs)

Would greatly appreciate guidance on:

1. The correct way to implement custom bidirectional sync
2. How to properly manage _status when applying remote changes
3. Understanding when and how _preparedState should be cleared
4. Whether this is a bug or if we're using the API incorrectly

Thank you! 🙏

[RBedbur]

Hi,

don’t have a solution for your problem, but I have a working App for React Native / Expo and corresponding Backend. Maybe that helps:
https://github.com/TayhoSolutions/watermelondb

[defyd-rc]

Thanks for sharing your sample code. I will go through it and check if it helps.

[defyd-rc]

While @RBedbur 's code did not directly help, it should be very helpful for us going forward when we try to upgrade our Expo stack from expo@52 to expo@54.

Finally, we could find the root cause of the issue that we were facing - we had ended up passing multiple instances of the same model entity to the changes array, ie the de-duplication code at our end was broken. Hence, once the 1st entity got processed and the second (duplicate) entity was seen by the sync code, it was flagged as already prepared.

Hope this helps anyone else who ends up facing a similar issue as us!