Best practice for time-based sync filtering (rolling window)?

[mikecfisher]

Hey team,

I'm building an offline-first app for event management and running into the NOW() limitation in sync rules. My use case is pretty straightforward, I only want to sync events from the last 7 days and next 30 days to mobile devices. Don't want years of old events bloating the local db.

Obviously can't do WHERE starts_at >= NOW() - INTERVAL '7 days' since sync rules need to be deterministic.

I've been looking at two approaches and wondering what you'd recommend:

Option A: Server-side boolean flag

Add a sync_active column and run a daily cron job that flips it based on event dates. Sync rule just does WHERE sync_active = true. Simple but feels hacky and creates unnecessary writes.

Option B: Client parameters with time buckets

Add a generated column like time_bucket_key (ISO week format), then use client params to request specific weeks:

parameters: |
  SELECT value as bucket_id 
  FROM json_each(request.parameters() -> 'time_buckets')

data:
  - SELECT * FROM events WHERE time_bucket_key = bucket.bucket_id

Client calculates which weeks it needs and passes them on connect. Filter locally for the exact date range.

Is option B the recommended pattern here? Any gotchas I should know about?

Also curious if Sync Streams (saw it's in alpha) changes the calculus here at all. The TTL stuff looks interesting for this use case.

Thanks!

[simolus3]

I agree that option B is what we'd typically recommend. I will note however that option A ensures old data is eventually removed from PowerSync's bucket storage (at least after a re-deploy / full defrag). With option B, we generate a bucket for each week that has an event in it, even if that event is years in the past (since a user could technically decide to include that week in time_buckets when connecting).
With option A, there would only be a single bucket (but as you've mentioned, it would indefinitely contain REMOVE operations for events where sync_active was switched to false). But after performing a defragmentation, those operations will be deleted from the bucket history and would no longer be processed by PowerSync.

So you could potentially do both (partition by ISO week and add an AND sync_active filter that gets cleared on really old events).

Also curious if Sync Streams (saw it's in alpha) changes the calculus here at all. The TTL stuff looks interesting for this use case.

I think only slightly. With sync streams you'd also create one bucket per ISO week, but you can design the query to only filter for a single week:

streams:
  events_by_week:
    query: SELECT * FROM events WHERE time_bucket_key = subscription.parameter('week')

Then when your app starts, you'd filter for each week that you care about and create a subscription:

for (const week of lastWeekUntilNextMonthInIsoWeeks()) {
  const subscription = await db.syncStream('events_by_week', {week}).subscribe();
  globalSubscriptionsArray.push(subscription);
}

The TTL isn't really all that relevant here since stream subscriptions stay active as long as the JS object is referenced (which is why the snippet leaks it - but there are cleaner patterns for dealing with this through e.g. react hooks). So sync streams are only different in that you can subscribe to each week explicitly, they don't really change the entire structure.

---

As an aside, we have an internal proposal that is quite close to option B here. The idea is that we'd eventually support:

• A time_interval function that "rounds down" to a given time interval, e.g. you'd call time_interval('7 days', event.time) and it would return a time before or at event.time that also has its unix timestamp dividable by 60 * 60 * 24 * 7 seconds.
• A time_intervals function that returns a JSON array for all overlapping time intervals, e.g. you'd call time_interval('7 days', request.parameter('start'), request.parameter('end')) and it would return all timestamps dividabl by 7 days where [that timestamp, that timestamp + 7 days) intersects with the provided time range.

Then you could combine the two to replace the ISO week column (instead rounding a timestamp value with these functions).