ADR 0004 — Premium-only Supabase sync enforced at the RLS layer

Context

Supabase sync — cloud backup and cross-device synchronisation — is a paid feature exclusive to Individual and Cora Health Premium subscribers. Approximately 1,000 premium subscribers across the four apps currently pay for it.

Free-tier users write exclusively to local GRDB (SQLite) and should never read or write rows in the Supabase health data tables. The original RLS policies on all user data tables (bp_readings, bs_readings, meal_logs, weight_logs, medication_intake_records, carb_ratios, sensitivity_factors, diabetes_settings, achievements, shopping_list_items) used only a basic ownership check:

USING (auth.uid() = user_id)
WITH CHECK (auth.uid() = user_id)

This allows any authenticated user — regardless of subscription status — to read and write their own rows. Authentication and authorisation are conflated.

The client-side gating problem

The iOS apps gate sync behind a RevenueCat entitlement check before initiating any Supabase network call. This is correct behaviour in the happy path. However, client-side gating alone is insufficient for a production system:

• A client bug (incorrect entitlement cache, race on session restore, missing guard in a new code path) silently writes free-user data to Supabase, creating GDPR data obligations for data the platform has no revenue to justify holding.
• A modified client (jailbroken device, re-signed IPA) can bypass entitlement checks entirely.
• The server has no visibility into whether a given write was authorised by a valid subscription at the time of the write.

The database must be the authoritative enforcement point. Client-side gating remains useful as a UX layer (showing the paywall before a network call) but must not be the only gate.

Entitlement model

Surface	Free	Premium
Supabase Auth (SIWA, email OTP, session management)	Yes	Yes
profiles table (own row)	Yes	Yes
subscriptions table (own row)	Yes	Yes
All 10 sync data tables	No	Yes (own rows only)
Realtime channel subscriptions on data tables	No	Yes
Storage buckets	No	Yes

Auth remains universal. Free users can sign in, see the paywall, and restore purchases via RevenueCat without any restriction. A stable authenticated identity across devices and reinstalls is necessary even for free users.

Decision

Add a SQL helper function public.is_premium(uid uuid) that returns true if and only if the given user has an active, non-expired subscription in the subscriptions table. Apply this function as an additional predicate in the RLS USING and WITH CHECK clauses on all 10 sync data tables. Leave profiles and subscriptions ungated (ownership check only).

CREATE OR REPLACE FUNCTION public.is_premium(uid uuid)
RETURNS boolean
LANGUAGE sql
SECURITY DEFINER
STABLE
AS $$
  SELECT EXISTS (
    SELECT 1 FROM public.subscriptions
    WHERE user_id = uid
      AND is_active = true
      AND (expires_at IS NULL OR expires_at > now())
  );
$$;

Applied to every sync table:

CREATE POLICY "premium_rw" ON bp_readings
  FOR ALL
  USING  (auth.uid() = user_id AND public.is_premium(auth.uid()))
  WITH CHECK (auth.uid() = user_id AND public.is_premium(auth.uid()));

The same policy template applies to all 10 tables. Only the table name changes.

Consequences

Enforcement behaviour

• A free-tier user who obtains a valid Supabase JWT via Auth receives HTTP 200 with an empty array from GET /rest/v1/bp_readings — not a 403. This is standard Supabase/PostgREST behaviour: RLS returns no rows rather than an error when the policy predicate is false. The client sees an empty sync as if the user has no data on the server, which is correct.
• A premium user with is_active = true and a non-expired expires_at receives their own rows as before.
• Writes from a free-tier JWT return HTTP 200 with an empty data array (zero rows inserted), silently no-oping. This is preferable to a 403 because it prevents client error handlers from treating the response as a hard failure to be retried.

Downgrade and dunning

When RevenueCat fires an EXPIRATION or CANCELLATION event, the webhook handler sets subscriptions.is_active = false. RLS immediately returns zero rows to the client on the next sync attempt. Sync appears off without any client-side code change.

A grace_until column on subscriptions allows a grace period during payment dunning. The is_premium function should be extended to check grace_until if the product requires it:

AND (expires_at IS NULL OR expires_at > now() OR grace_until > now())

This prevents immediate sync loss when a payment fails but the user has not been formally notified. The grace period is set by the RevenueCat webhook handler.

Data retention on downgrade

Rows are retained in Supabase when a subscription expires. is_premium returning false hides them via RLS but does not delete them. When the user re-subscribes, rows reappear immediately. This preserves the user’s history and reduces friction for win-back flows.

The is_premium function

• SECURITY DEFINER: runs with the function owner’s privileges, not the calling role’s. This is necessary because subscriptions might have its own restrictive RLS. The function is read-only and accesses only the subscriptions table.
• STABLE: Postgres can cache the result within a single query, avoiding repeated lookups when a policy is evaluated for multiple rows. Do not mark it VOLATILE or IMMUTABLE.
• Do not use is_premium as a mutable function or call it outside of RLS policy contexts without reviewing the security implications of SECURITY DEFINER.

Integration test requirement

Two test cases must exist and pass in the integration test suite:

1. Free-tier JWT: GET /rest/v1/bp_readings returns HTTP 200 with [].
2. Premium JWT: GET /rest/v1/bp_readings returns HTTP 200 with the user’s own rows.

These tests must be run as part of every Supabase migration deployment.

Realtime

The RLS gate is the authoritative enforcement point. Realtime channel subscription narrowing (so free users do not open WebSocket connections that consume server resources) is a follow-up optimisation, not a correctness requirement. Even if a free user opens a Realtime channel, RLS on the underlying table ensures they receive no rows.

Alternatives considered

Client-side gating only

Simplest to implement. Rejected because it can be bypassed by a client bug or modified client, silently writing free-user health data to the server and creating GDPR obligations the platform has no business justification for.

Separate Postgres roles per tier

Create a free_user role and a premium_user role, and connect as the appropriate role based on the JWT. Rejected because PostgREST maps all authenticated requests to a single authenticated role. Implementing per-tier roles would require either a custom PostgREST build or an intermediate proxy — significant complexity for a problem that is_premium() in RLS solves cleanly with zero additional infrastructure.

PostgREST pre-request hook

A pgrst.db-pre-request function can inspect the JWT and set session variables before each request. Those variables could gate access. Rejected because RLS is the standard Supabase security mechanism and is enforced regardless of how the database is accessed (PostgREST, direct Postgres connection, Realtime). A pre-request hook applies only to PostgREST traffic; it would not protect direct Postgres connections. RLS is the correct layer.