# Set up planner sync with Supabase

The planner stores each changed tick or grade field separately. Each change carries the time it was made, aligned to Supabase's clock. A delayed older checkbox change cannot replace a newer one, and different items never replace each other. After each write the planner reads back the cloud value and updates the screen to match it. Open devices check for changes every 5 seconds. Only run this SQL in your own Supabase project. Never put a service-role or secret key in the planner.

## 1. Create the table and access rules

In Supabase, open **SQL Editor → New query**, paste the full SQL below, and run it. It creates a private table and copies any existing planner state into it where possible. The old table is left in place.

~~~sql
create table if not exists public.planner_items (
  user_id uuid not null references auth.users(id) on delete cascade,
  item_key text not null,
  item_value jsonb not null,
  updated_at timestamptz not null default now(),
  client_updated_at timestamptz not null default now(),
  constraint planner_items_pkey primary key (user_id, item_key)
);

-- Upgrade an existing table and seed its new timestamp from the saved server time.
alter table public.planner_items
  add column if not exists client_updated_at timestamptz;
update public.planner_items
  set client_updated_at = updated_at
  where client_updated_at is null;
alter table public.planner_items
  alter column client_updated_at set default now(),
  alter column client_updated_at set not null;

alter table public.planner_items enable row level security;
revoke all on table public.planner_items from anon, authenticated;
grant select, insert, update on public.planner_items to authenticated;

drop policy if exists "Users read their planner items" on public.planner_items;
create policy "Users read their planner items"
  on public.planner_items for select to authenticated
  using ((select auth.uid()) = user_id);

drop policy if exists "Users create their planner items" on public.planner_items;
create policy "Users create their planner items"
  on public.planner_items for insert to authenticated
  with check ((select auth.uid()) = user_id);

drop policy if exists "Users update their planner items" on public.planner_items;
create policy "Users update their planner items"
  on public.planner_items for update to authenticated
  using ((select auth.uid()) = user_id)
  with check ((select auth.uid()) = user_id);

create or replace function public.planner_server_time()
returns timestamptz
language sql
security invoker
set search_path = public
as $$
  select clock_timestamp();
$$;

revoke all on function public.planner_server_time() from public, anon;
grant execute on function public.planner_server_time() to authenticated;

create or replace function public.save_planner_items_if_newer(p_items jsonb)
returns void
language plpgsql
security invoker
set search_path = public
as $$
declare
  received_at timestamptz := clock_timestamp();
begin
  if auth.uid() is null then
    raise exception 'Sign in is required to sync planner items';
  end if;

  if exists (
    select 1
    from jsonb_to_recordset(coalesce(p_items, '[]'::jsonb))
      as x(item_key text, item_value jsonb, client_updated_at timestamptz)
    where x.item_key is not null
      and (x.item_value is null or x.client_updated_at is null)
  ) then
    raise exception 'This planner version must send a timestamp with every change. Refresh the live planner.';
  end if;

  insert into public.planner_items (user_id, item_key, item_value, updated_at, client_updated_at)
  select auth.uid(), x.item_key, x.item_value, received_at,
    least(x.client_updated_at, received_at)
  from jsonb_to_recordset(coalesce(p_items, '[]'::jsonb))
    as x(item_key text, item_value jsonb, client_updated_at timestamptz)
  where x.item_key is not null
  on conflict (user_id, item_key) do update
    set item_value = excluded.item_value,
        updated_at = excluded.updated_at,
        client_updated_at = excluded.client_updated_at
    where public.planner_items.client_updated_at < excluded.client_updated_at;
end;
$$;

revoke all on function public.save_planner_items_if_newer(jsonb) from public, anon;
grant execute on function public.save_planner_items_if_newer(jsonb) to authenticated;

do $$
begin
  -- Remove implausibly future server timestamps left by older app versions.
  update public.planner_items
  set updated_at = clock_timestamp()
  where updated_at > clock_timestamp() + interval '5 minutes';

  if to_regclass('public.planner_state') is not null then
    execute $m$
      insert into public.planner_items (user_id, item_key, item_value)
      select p.user_id, 'deadline/' || e.key, e.value
      from public.planner_state p
      cross join lateral jsonb_each(coalesce(p.state->'deadlines', '{}'::jsonb)) e
      on conflict (user_id, item_key) do nothing
    $m$;
    execute $m$
      insert into public.planner_items (user_id, item_key, item_value)
      select p.user_id, 'revision/' || e.key, e.value
      from public.planner_state p
      cross join lateral jsonb_each(coalesce(p.state->'revision', '{}'::jsonb)) e
      on conflict (user_id, item_key) do nothing
    $m$;
    execute $m$
      insert into public.planner_items (user_id, item_key, item_value)
      select p.user_id, 'grade/target', p.state->'grades'->'target'
      from public.planner_state p
      where p.state->'grades' ? 'target'
      on conflict (user_id, item_key) do nothing
    $m$;
    execute $m$
      insert into public.planner_items (user_id, item_key, item_value)
      select p.user_id, 'grade/score/' || e.key, e.value
      from public.planner_state p
      cross join lateral jsonb_each(coalesce(p.state->'grades'->'scores', '{}'::jsonb)) e
      on conflict (user_id, item_key) do nothing
    $m$;
  end if;
end $$;

notify pgrst, 'reload schema';
~~~

## 2. Update both devices

Run the SQL above in your existing Supabase project, then upload the files from the updated publishing folder to the same Netlify site. Refresh the live site on both devices after Netlify finishes. Keep using the same Supabase Project URL, publishable/anon key, and planner account. You do not need another account. Each device should show the new sync wording that mentions timestamped changes.

The old app retried queued changes before reading the cloud copy, so an old offline tick could arrive late and undo a newer one. The updated app keeps the original change time with each tick; Supabase ignores any older retry. An old app version cannot write timestamp-free changes after this SQL is applied, so refresh both devices onto the updated live site.

The synced items are assignment ticks, revision ticks, grades, and the target mark. Calendar feed data and timetable events remain embedded in the planner.

Supabase references: [Row Level Security](https://supabase.com/docs/guides/database/postgres/row-level-security) and [Auth sign-up](https://supabase.com/docs/reference/javascript/auth-signup).
