Supabase + PowerSync

supabase db query --linked --project-ref <project-ref> -f <file>.sql

create table
  public.lists (
    id uuid not null default gen_random_uuid (),
    created_at timestamp with time zone not null default now(),
    name text not null,
    owner_id uuid not null,
    constraint lists_pkey primary key (id),
    constraint lists_owner_id_fkey foreign key (owner_id) references auth.users (id) on delete cascade
  ) tablespace pg_default;

create table
  public.todos (
    id uuid not null default gen_random_uuid (),
    created_at timestamp with time zone not null default now(),
    completed_at timestamp with time zone null,
    description text not null,
    completed boolean not null default false,
    created_by uuid null,
    completed_by uuid null,
    list_id uuid not null,
    constraint todos_pkey primary key (id),
    constraint todos_created_by_fkey foreign key (created_by) references auth.users (id) on delete set null,
    constraint todos_completed_by_fkey foreign key (completed_by) references auth.users (id) on delete set null,
    constraint todos_list_id_fkey foreign key (list_id) references lists (id) on delete cascade
  ) tablespace pg_default;

-- Grant Data API access so supabase-js can read and write these tables.
grant select, insert, update, delete on public.lists to authenticated;
grant select, insert, update, delete on public.todos to authenticated;
grant select, insert, update, delete on public.lists to service_role;
grant select, insert, update, delete on public.todos to service_role;

-- Enable Row Level Security and add policies so each user can only access their own data.
alter table public.lists enable row level security;
alter table public.todos enable row level security;

create policy "owned lists" on public.lists
  for all to authenticated
  using (auth.uid() = owner_id);

create policy "todos in owned lists" on public.todos
  for all to authenticated
  using (
    auth.uid() in (
      select lists.owner_id from public.lists where lists.id = todos.list_id
    )
  );

-- Create a role/user with replication privileges for PowerSync
CREATE ROLE powersync_role WITH REPLICATION BYPASSRLS LOGIN PASSWORD 'myhighlyrandompassword';
-- Set up permissions for the newly created role
-- Read-only (SELECT) access is required
GRANT SELECT ON ALL TABLES IN SCHEMA public TO powersync_role;  

-- Optionally, grant SELECT on all future tables (to cater for schema additions)
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO powersync_role; 

-- Create a publication to replicate tables. The publication must be named "powersync"
CREATE PUBLICATION powersync FOR ALL TABLES;

config:
  edition: 3
streams:
  user_data:
    auto_subscribe: true
    queries:
    - SELECT * FROM lists WHERE owner_id = auth.user_id()
    - SELECT todos.* FROM todos INNER JOIN lists ON todos.list_id = lists.id WHERE lists.owner_id = auth.user_id()

create function public.handle_new_user_sample_data()
returns trigger as $$
declare
  new_list_id uuid;
begin
  insert into public.lists (name, owner_id)
    values ('Shopping list', new.id)
    returning id into new_list_id;

  insert into public.todos(description, list_id, created_by)
    values ('Bread', new_list_id, new.id);

  insert into public.todos(description, list_id, created_by)
    values ('Apples', new_list_id, new.id);

  return new;
end;
$$ language plpgsql security definer;

create trigger new_user_sample_data after insert on auth.users for each row execute procedure public.handle_new_user_sample_data();

supabase --experimental --project-ref <project-ref> postgres-config update --config max_wal_size=1GB,max_slot_wal_keep_size=1GB