Spike: cloud sync design

[MessiasLima]

Description

Investigate the cloud sync approach for Foliary using Supabase as the backend. The app already uses Supabase for authentication. This spike determines the sync strategy, conflict resolution model, data model for the Supabase tasks table, RLS policies, offline queueing approach, and background sync frequency. It also determines which parts are premium-gated. The outcome of this spike should be the creation of the implementation tickets for cloud sync.

Background

Foliary is an offline-first app using Room for local persistence. Phase 1 ships with local-only data. Cloud sync is a paid feature that allows users to access their tasks across devices. Since the app already integrates Supabase for auth, it is the natural choice for the sync backend. Before implementation, we need to decide on the sync architecture: how conflicts are resolved when the same task is edited on two devices, how offline changes are queued and replayed, and what the Supabase schema and RLS policies should look like.

Current Knowledge

• Supabase is already integrated for authentication (magic link, Google, Apple)
• Room database is at version 1 with fallbackToDestructiveMigration(false)
• The Task domain model is currently a placeholder (data class Task(val id: String)) but will be expanded by Task domain model and data layer #120
• Tasks are local-only in Phase 1
• Cloud sync is a premium feature per the roadmap
• Supabase provides real-time subscriptions, row-level security, and REST/PostgREST APIs

Scope

In scope: sync strategy (one-way upload vs two-way sync), conflict resolution model (last-write-wins, merge, or CRDT-like), Supabase tasks table schema, RLS policies for user data isolation, offline queueing approach for pending changes, background sync frequency and triggers, premium gating scope for sync features.

Out of scope: implementation of cloud sync, UI for sync status, migration of existing local data to the cloud.

Outcomes

• Document the chosen sync strategy (one-way upload vs two-way) with rationale
• Define the conflict resolution model and document how conflicts are detected and resolved
• Design the Supabase tasks table schema including all fields from the expanded Task model
• Define RLS policies ensuring users can only access their own data
• Document the offline queueing approach (how pending changes are stored locally and replayed when connectivity returns)
• Recommend background sync frequency and trigger events (app open, task mutation, periodic)
• Specify which sync features are premium-gated (e.g. sync itself, number of devices, sync frequency)
• Create implementation tickets based on spike findings