Sync Rules Not Filtering Data Based on client params

[tejas-codiste]

I am trying to sync only the relevant data based on a user_id parameter. However, instead of fetching only the filtered data, the entire database is being synced to the local device.

Expected Behavior

Only the data matching the user_id parameter should be synced to the local database.

Actual Behavior

The entire database is synced, ignoring the filtering rules logic in the sync rules.

PowerSync Sync Rules Configuration

I have defined the following sync rule in the PowerSync console:

bucket_definitions:
  by_user_parameter:
    parameters: SELECT (request.parameters() ->> 'user_id') as user_id
    data:
      - SELECT * FROM creators WHERE creators.user_id = bucket.user_id
      - SELECT * FROM events WHERE events.creator_user = bucket.user_id
      - SELECT attendee.* FROM attendee 
        JOIN events ON attendee.event_id = events.event_id 
        JOIN creators ON events.creator_user = creators.user_id 
        WHERE creators.user_id = bucket.user_id
      - SELECT * FROM pin WHERE pin.user_id = bucket.user_id
      - SELECT device.* FROM device 
        JOIN pin ON device.pin_id = pin.uid 
        JOIN creators ON pin.user_id = creators.user_id 
        WHERE creators.user_id = bucket.user_id
      - SELECT * FROM check_in_logs WHERE user_id = bucket.user_id

Flutter Code for Connecting to PowerSync

currentConnector = SupabaseConnector(db);
db.connect(
    connector: currentConnector!,
    params: {'user_id': user.userId});

This issue prevents implementing user-specific data syncing, which is a core requirement for my application. Any guidance on resolving this would be appreciated.

[Chriztiaan]

Hey @tejas-codiste, firstly just a note on client parameters. They aren't specifically secure as a client could specify any user_id, for user_id specifically we would generally recommend to pull the user_id from the JWT token. An example of this could be found here: https://docs.powersync.com/usage/sync-rules/organize-data-into-buckets

Ignoring the user_id label, your sync rules should still have worked though as it looks correct when look at: https://docs.powersync.com/usage/sync-rules/advanced-topics/client-parameters

[tejas-codiste]

Thanks for your kind reply. I went through the shared link, but my question remains the same. I want to load only the data related to user_id, not the entire database. I'm not using Supabase Auth in the app. Could you lease let me know how to achieve this?.

[Chriztiaan]

Okay so the first part would be ensuring that you define the user_id in your auth token. Where your token is exposed in the fetchCredentials function of your connector. What backend/database pair are you currently using? Is this a fresh project or are you potentially moving from Atlas Sync @tejas-codiste? If you aren't using Supabase Auth but another backend, a better reference would be something like the node connector.

Then you would have something like this (note joins aren't supported in the sync rules yet).

bucket_definitions:
  by_user_parameter:
    parameters: SELECT request.user_id() as user_id
    data:
      - SELECT * FROM creators WHERE creators.user_id = bucket.user_id
      - SELECT * FROM events WHERE events.creator_user = bucket.user_id
      ....

[tejas-codiste]

Thanks, @Chriztiaan!

I'm still facing an issue where the entire PowerSync database is being backed up locally on the device. Could you please review my refactored bucket definitions and the Flutter-side code where I'm passing the user_id to get a better understanding of the issue?

Bucket Definitions:

bucket_definitions:
  by_user_parameter:
    # request.user_id() is the same as the previous token_parameter.user_id
    parameters: SELECT request.user_id() as user_id
    data:
      - SELECT * FROM creators WHERE creators.user_id = bucket.user_id
      - SELECT * FROM events WHERE events.creator_user = bucket.user_id
      - SELECT * FROM attendee WHERE event_id IN (SELECT event_id FROM events WHERE creator_user = bucket.user_id)
      - SELECT * FROM pin WHERE pin.user_id = bucket.user_id
      - SELECT * FROM device WHERE pin_id IN (SELECT uid FROM pin WHERE user_id = bucket.user_id)
      - SELECT * FROM check_in_logs WHERE user_id = bucket.user_id 

Flutter Code (Passing user_id):

Additionally, I’m unable to see the newly created bucket in the Diagnostics Console (screenshot attached). It only shows the global bucket, even though I’ve commented out the global bucket and redeployed the rules.

Could you help me ensure that only user-specific data is backed up based on user_id?
Thanks in advance!

[rkistner]

@tejas-codiste Are you using a cloud or self-hosted instance?

Usually in a case like this where you don't see the sync rule updates being applied, it indicates a likely error in processing your sync rules. The error should be visible in the cloud dashboard, or in the logs of a self-hosted instance (should be visible when starting the instance).

[tejas-codiste]

@rkistner

Here's how my data flow works:

I log in to the backend using email & password, which provides a user_id upon successful authentication.
The backend data is replicated (copied) on Supabase.
I need to access only the data related to the authenticated user_id in the mobile app via PowerSync, allowing online and offline actions.

Hope this clarifies my setup!

[rkistner]

@tejas-codiste We still need info on whether you're using a cloud or self-hosted PowerSync instance, since the issue is most likely on the PowerSync service. The relevant logs should indicate what the issue is.

[tejas-codiste]

We're using Supabase instance connected to PowerSync, so I believe it should be a cloud instance.

[rkistner]

For a cloud instance you should see either "No replication errors" as shown in the screenshot below, or errors should be listed there if there is an issue with your sync rules after deploying it.

[tejas-codiste]

@rkistner Could you please check the shared the screenshot, There no errors display on console.

[rkistner]

Ok, there appears to be an issue that the errors are not reported in the "replication errors" section - we'll look into that. You can however see the errors highlighted in the sync rules section - the "select not supported here" (subqueries and joins are not supported in sync rules).

You may need to create separate buckets per event and per to work around those limitations. For more strategies, see this guide.

[tejas-codiste]

Hi @rkistner , thanks for your guidance. I hope you've reviewed the sync rules. Could you please help me with the refactored sync rules? That would be greatly helpful.

[rkistner]

@tejas-codiste For sync rule advice, it would be best to join our Discord - see the link to join here.