Set up Supabase

Creating a Supabase project for the sample apps and loading their schema into it. A free project is enough.

The schema is two files, applied in order, in samples/supabase/migrations/. The first, 20260930000000_offline_tasks.sql, creates two tables, tasks and task_photos, and a private Storage bucket, task-photos. The second, 20261011000000_functions_realtime_and_photo_updates.sql, adds two database functions, a Storage policy that lets a user replace and move their own photo files, and the two tables to Realtime's publication. Every row and every file belongs to one user, and the policies let a user reach only their own. The samples sign in anonymously, so each install is a user of its own and nobody types an email address or a password.

Create a project

1. In the Supabase dashboard, choose New project.
2. Pick an organization, name the project, set a database password and pick a region. Keep the password: the command-line route below may ask for it.
3. Under Security, leave Enable Data API on: the samples read and write through it. The other two options there, Automatically expose new tables and Enable automatic RLS, can be either way. The schema grants what it needs and enables row-level security itself.
4. Create the project and wait for it to finish setting up.

Use a project of its own for the samples, not one that holds real data.

Turn on anonymous sign-ins

1. Open Authentication, then Sign In / Providers.
2. Under User Signups, turn on Allow anonymous sign-ins.
3. Choose Save changes.

An anonymous user signs in with the authenticated role, like any other user, so the schema's policies treat them the same way. Supabase recommends a CAPTCHA for anonymous sign-ins on a production project. The samples do not send a CAPTCHA token, so leave it off for this one; Supabase limits anonymous sign-ins to 30 an hour from each IP address by default.

Apply the schema

Either route applies the same two files, and both can be repeated: a second run changes nothing. So the two also mix: a db push after the SQL Editor runs the files again, and nothing changes.

In the SQL Editor

1. Open the SQL Editor and start a new query.
2. Paste the whole of samples/supabase/migrations/20260930000000_offline_tasks.sql and choose Run.
3. The editor warns that the query "includes destructive operations". The file drops and recreates its own policies so that it can run twice, and drops nothing else. Choose Run query.
4. Start a new query, paste the whole of samples/supabase/migrations/20261011000000_functions_realtime_and_photo_updates.sql, and run it the same way.

Each finishes with "Success. No rows returned".

With the Supabase CLI

With the Supabase CLI installed, from the repository root:

supabase login      # once: opens the browser to sign the CLI in
cd samples
supabase link       # pick the project; it may ask for the database password
supabase db push    # lists the migrations not yet applied and asks before applying them

samples/ is a small CLI project, and the two schema files are its migrations. supabase link --project-ref <ref> skips the picker, and -p <password> answers the password prompt. The project ref is the part of the project URL before .supabase.co.

If it stops

Before it changes anything, the file checks for three things it would otherwise keep, and stops with a message and a hint if it finds one:

• a tasks or task_photos table in another shape;
• the moddatetime extension in a schema other than extensions;
• another policy on storage.objects that names the task-photos bucket. Policies add up, so one left behind would open the bucket to whoever it admits.

A project that once ran this repository's old demo schema has all three: its tables, the extension in public, and four policies that let the publishable key alone read, upload and delete every file in task-photos. Deal with each as the hint says, then run the file again.

What it creates

Object	What it is
public.tasks	id, user_id, title, notes, done, created_at, updated_at
public.task_photos	id, user_id, task_id, object_path, width, height, created_at, updated_at
Row-level security	Enabled and forced on both tables. Each has four policies, one per operation, and each admits a row only when its user_id is the caller's id
Grants	Nothing for anon. select, insert, update and delete for authenticated, and for service_role, which the samples never use
updated_at	Set by the server on every update, with the moddatetime extension, installed in the extensions schema
Storage bucket task-photos	Private, JPEG only, 5 MB per file
Storage policies	Read, upload, update and delete, each only under <user id>/ in task-photos. Update is the second file's: Storage's replace and move need it, and copy needs read and upload
public.complete_all_tasks()	Marks every open task of the caller done, and returns how many it changed. The samples' Mark all done
public.tasks_done_count(created_since)	How many of the caller's tasks are done, those created since created_since when it is given. The samples' Server section
Functions' grants	execute for authenticated and service_role only. Both run as the caller (security invoker), so the tables' policies decide which rows they reach
Realtime	tasks and task_photos in the supabase_realtime publication, so the samples' Live updates get their changes. An insert or an update reaches only the users whose policies let them read the row. A delete is not filtered: Supabase applies no row-level security to delete events, so every subscriber receives each deleted row's id, whoever owned it. Here an id is a random UUID and says nothing; a table whose keys mean something does not belong in the publication on these terms

The rules the sample apps build on:

• The app makes every id. tasks.id and task_photos.id have no server default. A task created offline can be opened, edited and given photos before it syncs, and a replayed insert carries the same id each time. An insert without an id is refused. So is a row added in the Table Editor without an id and a user_id: the editor has no signed-in user to fill user_id in.
• One rule refuses a write on purpose. A title must not be blank (spaces, tabs and line breaks only) and must be at most 200 characters. The samples queue a write that breaks it to show what a rejected write looks like when the queue replays. Two more rules would refuse a write by accident: notes is at most 2,000 characters, and width and height are positive or null, never 0.
• A photo is one JPEG, at one path. Its file lives at exactly <user id>/<task id>/<photo id>.jpg, the ids in lower case as Postgres writes them, and the row may name no other file. Swift's uuidString is upper case, so an iOS app lower-cases it first (uuidString.lowercased()), or Storage and the row both refuse it. The samples re-encode every photo to a JPEG under 5 MB and upload it as image/jpeg; the bucket refuses anything else.
• A photo belongs to its owner's task. A photo row names its task and its owner together, so no one can attach a photo to someone else's task.
• Deleting stops at the database. Deleting a task deletes its photo rows, and deleting a user deletes their rows in both tables, but neither deletes files: Storage is a separate service, and only the app deletes a file, through the Storage API. So the samples delete a task in this order: its files (online only), then its photo rows, then the task. A task deleted offline leaves its files behind, because nothing queues a Storage delete. Supabase also refuses to delete a user who still owns files (Managing user data): delete their files first.

Check it

• Table Editor lists tasks and task_photos, and neither carries the red Unrestricted badge that marks a table without row-level security.
• Authentication, then Policies, shows four policies on each table.
• Storage, then Files, lists task-photos with no Public badge, a 5 MB file size limit, image/jpeg as its only type, and 4 in its Policies column: the two files' four, each for signed-in users only. A higher number means another policy also reaches the bucket.
• Database, then Functions, lists complete_all_tasks and tasks_done_count.
• Database, then Publications, shows tasks and task_photos under supabase_realtime.

Find the URL and the publishable key

Choose Connect at the top of the project's dashboard: it shows the project URL (https://<ref>.supabase.co) and the publishable key. The keys are also under Project Settings, then API Keys.

Use the publishable key (sb_publishable_…), which is made to ship inside an app. Never put the secret key (sb_secret_…), or the legacy service_role key, in an app: it skips row-level security. The legacy anon key also works, until Supabase retires it (planned for the end of 2026).

Where the URL and the key go

Each sample reads the two values from a file that git ignores, so they never reach a commit:

Sample	File	Entries
Android	local.properties, at the repository root	SUPABASE_URL=https://<ref>.supabase.co SUPABASE_PUBLISHABLE_KEY=sb_publishable_…
iOS	samples/ios/Configuration/Secrets.xcconfig	SUPABASE_URL = <ref>.supabase.co SUPABASE_PUBLISHABLE_KEY = sb_publishable_…

The iOS URL has no https://, because // starts a comment in an xcconfig file. The pages for the Android sample and the iOS sample take it from there.

What the key can reach

The publishable key is public: anyone can take it out of an app. With this schema:

• A request with the key and no signed-in user reaches nothing: no table grants anything to anon, and no Storage policy admits it.
• A signed-in user, anonymous or not, reaches only the rows and files that carry their own id. Knowing another user's ids does not help: a read returns nothing, and an update or a delete changes nothing.
• Through Realtime, a signed-in user hears of inserts and updates to their own rows only, but of every deleted row, anyone's: Supabase applies no row-level security to a delete event, which carries the row's primary key, its id, and no other column. The ids are random UUIDs, so they say nothing about anyone.
• A file is a JPEG of at most 5 MB, and none has a public URL.

An anonymous user cannot sign back in: after signing out, or deleting the app, their tasks stay on the server but the app can never reach them again. Two devices are two users, so the guided tour plays the second writer from the Table Editor instead.

If the project pauses

Supabase pauses a free project after a week with little activity, and emails the owner about a week before it does. The data stays, but the samples cannot reach the project until it is resumed: open it in the dashboard and choose Resume project. A paused project can be resumed for up to a year. A few requests to the database each day, from the samples or the dashboard, keep it from pausing. See Project pausing.

When the schema changes

The first file is the samples' first migration, and what it creates is never changed once released; it only learns the names of the later files' own Storage policies, so its check for other policies on the bucket lets it run again after them. A later change to the schema is a new file in samples/supabase/migrations/, named for the time it was written so it sorts after the others, as the second file is. supabase db push applies each file once, in order, and never runs one it has applied again. In the SQL Editor, run each new file once, in the same order. Either file can still be run again at any time and changes nothing.

Cleaning up files and users

Two things pile up in a project the samples have used for a while, because nothing on the device can remove them:

• Files with no row. A task deleted offline keeps its photos' files: Storage writes are never queued, so the files stay in the bucket after the rows go.
• Anonymous users nobody can reach. An anonymous user who signs out, or deletes the app, can never sign back in, and their tasks, photos and files stay behind.

The samples never hold the secret key, so they cannot clean up after themselves. Sweep by hand from the dashboard now and then, or, for a project that wants it done on a schedule, write an Edge Function that does the same with the secret key.

Files with no row. List them in the SQL Editor:

select o.name
from storage.objects o
where o.bucket_id = 'task-photos'
  and not exists (select 1 from public.task_photos p where p.object_path = o.name);

Then delete those files in Storage, under task-photos: Supabase does not let SQL delete stored files.

Anonymous users. Files first: Supabase refuses to delete a user who still owns files. List the files of anonymous users who have not signed in for 30 days:

select o.name
from storage.objects o
join auth.users u on split_part(o.name, '/', 1) = u.id::text
where o.bucket_id = 'task-photos'
  and u.is_anonymous
  and u.last_sign_in_at < now() - interval '30 days';

Delete those files in Storage, then the users. Their tasks and photo rows go with them:

delete from auth.users
where is_anonymous
  and last_sign_in_at < now() - interval '30 days';

Remove it all

The end of each file has a commented-out "how to reset" section. Run the second file's first. Then delete the task-photos bucket in Storage (Supabase does not let SQL delete stored files), and run the first file's drop statements. That removes everything the files created, and running them again starts from empty.

The schema

Included from the repository when the site is built:

samples/supabase/migrations/20260930000000_offline_tasks.sql

-- Offline Tasks: the Supabase schema both sample apps run against.
--
-- Two tables and one Storage bucket. A signed-in user reads and writes only the rows and files
-- that carry their own id; a request with no user behind it reaches nothing. The samples sign
-- in anonymously, so every install is a user of its own.
--
-- Before you run it, turn on anonymous sign-ins: Authentication > Sign In / Providers >
-- "Allow anonymous sign-ins". Then paste this whole file into the SQL Editor and run it, or run
-- `supabase db push` from samples/, where this file is the first of the migrations. Step by step:
-- https://nesfeder.github.io/supabase-offline-kt/samples/supabase-setup/
--
-- It can be run again. A second run changes nothing, and it puts back any policy, grant,
-- trigger or bucket setting edited since. The SQL Editor warns that the query "includes
-- destructive operations": the file drops and recreates its own policies, and nothing else.
-- To remove it all, see "how to reset" at the end.

-- Stop, with a clear message and before any change, on three things this file would otherwise
-- keep: a table of the same name in another shape (`create table if not exists` keeps it), the
-- moddatetime extension in another schema, and other policies on the task-photos bucket.
do $$
declare
  missing_columns text;
  extension_schema text;
  other_policies text;
begin
  select string_agg(format('public.%I (no %s)', expected.table_name, expected.column_name), ', ')
  into missing_columns
  from (
    values
      ('tasks', 'id'), ('tasks', 'user_id'), ('tasks', 'title'), ('tasks', 'notes'),
      ('tasks', 'done'), ('tasks', 'created_at'), ('tasks', 'updated_at'),
      ('task_photos', 'id'), ('task_photos', 'user_id'), ('task_photos', 'task_id'),
      ('task_photos', 'object_path'), ('task_photos', 'width'), ('task_photos', 'height'),
      ('task_photos', 'created_at'), ('task_photos', 'updated_at')
  ) as expected (table_name, column_name)
  join pg_catalog.pg_class c
    on c.relnamespace = 'public'::regnamespace and c.relname = expected.table_name
  where not exists (
    select 1
    from pg_catalog.pg_attribute a
    where a.attrelid = c.oid and a.attname = expected.column_name and not a.attisdropped
  );
  if missing_columns is not null then
    raise exception 'this file creates public.tasks and public.task_photos, but found a table '
      'of that name in another shape: %', missing_columns
      using hint = 'Rename or drop it, or apply this file to a project of its own.';
  end if;

  select e.extnamespace::regnamespace::text
  into extension_schema
  from pg_catalog.pg_extension e
  where e.extname = 'moddatetime';
  if extension_schema is not null and extension_schema <> 'extensions' then
    raise exception 'moddatetime is installed in schema %, and this file uses it from extensions',
      extension_schema
      using hint = 'Move it: alter extension moddatetime set schema extensions;';
  end if;

  -- Policies are combined with OR, so another policy that names the bucket would widen who
  -- reaches it, whatever this file's own policies say.
  select string_agg(format('%I', p.policyname), ', ')
  into other_policies
  from pg_catalog.pg_policies p
  where p.schemaname = 'storage'
    and p.tablename = 'objects'
    and p.policyname not in (
      'task-photos: owner reads', 'task-photos: owner uploads', 'task-photos: owner deletes',
      -- The later migration's own policy, so this file still runs again after it.
      'task-photos: owner updates'
    )
    and concat(p.qual, ' ', p.with_check) like '%task-photos%';
  if other_policies is not null then
    raise exception 'storage.objects already has other policies for the task-photos bucket: %',
      other_policies
      using hint = 'They would apply beside this file''s. Drop them first; the old demo schema''s '
        'are named demo_storage_anon_*.';
  end if;
end
$$;

-- moddatetime keeps updated_at current. It goes in the extensions schema, not public, so the
-- Data API does not expose it (the dashboard's Security Advisor flags extensions in public).
create extension if not exists moddatetime with schema extensions;

-- =============================================================================================
-- tasks
-- =============================================================================================

create table if not exists public.tasks (
  -- Made by the app, never by the server, so this has no default. A task created offline must
  -- be addressable (opened, edited, given photos) before it syncs, and a replayed insert must
  -- carry the same id every time. An insert without one fails (not null) rather than getting
  -- an id the app never sees.
  id uuid primary key,
  -- The owner. The app sends it on insert so its cached copy is complete before it syncs; the
  -- default covers a client that leaves it out. The policies below require the caller's id
  -- either way. Deleting the user deletes their tasks.
  user_id uuid not null default auth.uid() references auth.users (id) on delete cascade,
  -- The one rule the samples use to show a rejected write. A write queued offline with a blank
  -- title (spaces, tabs or line breaks only), or one over 200 characters, is refused when it
  -- replays: SQLSTATE 23514, which the Data API returns as HTTP 400.
  title text not null
    constraint tasks_title_length
    check (btrim(title, E' \t\r\n') <> '' and char_length(title) <= 200),
  notes text constraint tasks_notes_length check (char_length(notes) <= 2000),
  done boolean not null default false,
  -- The app sends created_at on insert, so a task made offline sorts correctly before it syncs.
  created_at timestamptz not null default now(),
  -- The server sets updated_at on every update (the trigger below); a value the app sends in an
  -- update is overwritten.
  updated_at timestamptz not null default now(),
  -- Lets task_photos require that a photo and its task have the same owner.
  constraint tasks_id_user_id_key unique (id, user_id)
);

-- The task list: the caller's tasks, newest first.
create index if not exists tasks_user_id_created_at_idx
  on public.tasks (user_id, created_at desc);

create or replace trigger tasks_set_updated_at
  before update on public.tasks
  for each row execute function extensions.moddatetime(updated_at);

-- Forced as well as enabled, so the policies bind the table's owner too, unless that role
-- bypasses row-level security. Supabase's postgres role does, so the dashboard still sees and
-- edits every row.
alter table public.tasks enable row level security;
alter table public.tasks force row level security;

-- Which roles reach the table at all. A project that automatically exposes new tables grants
-- every privilege to anon, authenticated and service_role, TRUNCATE included, which row-level
-- security does not cover; a newer project grants nothing. Both end here: nothing for anon (a
-- request with only the publishable key), the four row operations for signed-in users
-- (anonymous ones included) and for service_role. The samples never use the secret key;
-- service_role is granted for your own server-side code, such as a clean-up of orphaned files.
revoke all on table public.tasks from anon, authenticated, service_role;
grant select, insert, update, delete on table public.tasks to authenticated, service_role;

-- Owner-only, for every operation. `(select auth.uid())` is evaluated once per statement rather
-- than once per row, as Supabase recommends.
drop policy if exists "tasks: owner reads" on public.tasks;
create policy "tasks: owner reads" on public.tasks
  for select to authenticated
  using ((select auth.uid()) = user_id);

drop policy if exists "tasks: owner inserts" on public.tasks;
create policy "tasks: owner inserts" on public.tasks
  for insert to authenticated
  with check ((select auth.uid()) = user_id);

drop policy if exists "tasks: owner updates" on public.tasks;
create policy "tasks: owner updates" on public.tasks
  for update to authenticated
  using ((select auth.uid()) = user_id)
  with check ((select auth.uid()) = user_id);

drop policy if exists "tasks: owner deletes" on public.tasks;
create policy "tasks: owner deletes" on public.tasks
  for delete to authenticated
  using ((select auth.uid()) = user_id);

-- =============================================================================================
-- task_photos
-- =============================================================================================

create table if not exists public.task_photos (
  -- Made by the app, like tasks.id, and part of the photo's object path.
  id uuid primary key,
  user_id uuid not null default auth.uid() references auth.users (id) on delete cascade,
  task_id uuid not null,
  -- Where the image is in the task-photos bucket: exactly "<user_id>/<task_id>/<id>.jpg", with
  -- the ids in lower case, as Postgres writes a uuid. A fresh path per photo, never reused.
  object_path text not null,
  -- The image's size in pixels, so the app can reserve its space before it loads. Null when
  -- unknown, never 0.
  width integer constraint task_photos_width_positive check (width > 0),
  height integer constraint task_photos_height_positive check (height > 0),
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  -- The Storage policies admit a file only under its owner's folder; the row can name only the
  -- one file its own ids spell. A path in upper case is refused here and by Storage alike.
  constraint task_photos_object_path_layout
    check (object_path = user_id::text || '/' || task_id::text || '/' || id::text || '.jpg'),
  -- A photo belongs to a task with the same owner. The pair must exist in tasks, so no one can
  -- attach a photo to another user's task, even knowing its id. Deleting a task deletes its
  -- photo rows here, but not its files: Storage is a separate service, and only the app can
  -- delete them. An offline cache does not see the cascade either, so the samples delete a
  -- task's files (when online), then its photo rows, then the task.
  constraint task_photos_task_fkey foreign key (task_id, user_id)
    references public.tasks (id, user_id) on delete cascade
);

-- One task's photos, and the lookup the cascade makes when a task is deleted.
create index if not exists task_photos_task_id_user_id_idx
  on public.task_photos (task_id, user_id);

-- The Photos screen: the caller's photos, newest first.
create index if not exists task_photos_user_id_created_at_idx
  on public.task_photos (user_id, created_at desc);

create or replace trigger task_photos_set_updated_at
  before update on public.task_photos
  for each row execute function extensions.moddatetime(updated_at);

alter table public.task_photos enable row level security;
alter table public.task_photos force row level security;

revoke all on table public.task_photos from anon, authenticated, service_role;
grant select, insert, update, delete on table public.task_photos to authenticated, service_role;

drop policy if exists "task_photos: owner reads" on public.task_photos;
create policy "task_photos: owner reads" on public.task_photos
  for select to authenticated
  using ((select auth.uid()) = user_id);

drop policy if exists "task_photos: owner inserts" on public.task_photos;
create policy "task_photos: owner inserts" on public.task_photos
  for insert to authenticated
  with check ((select auth.uid()) = user_id);

drop policy if exists "task_photos: owner updates" on public.task_photos;
create policy "task_photos: owner updates" on public.task_photos
  for update to authenticated
  using ((select auth.uid()) = user_id)
  with check ((select auth.uid()) = user_id);

drop policy if exists "task_photos: owner deletes" on public.task_photos;
create policy "task_photos: owner deletes" on public.task_photos
  for delete to authenticated
  using ((select auth.uid()) = user_id);

-- =============================================================================================
-- Storage: the task-photos bucket
-- =============================================================================================

-- Private: no file in it has a public URL, and every download passes the policies below.
-- Storage refuses an upload over 5 MB, or one not sent as image/jpeg: the samples re-encode
-- every photo to a JPEG that fits, to match the ".jpg" in its path.
insert into storage.buckets (id, name, public, file_size_limit, allowed_mime_types)
values (
  'task-photos',
  'task-photos',
  false,
  5242880,
  array['image/jpeg']
)
on conflict (id) do update set
  public = excluded.public,
  file_size_limit = excluded.file_size_limit,
  allowed_mime_types = excluded.allowed_mime_types;

-- A file lives under "<user_id>/...", and only its owner may read, upload or delete there: the
-- first folder of the path must be the caller's id, in lower case. This file adds no update
-- policy, so on its own a file is never replaced or moved, and an upload to a path already taken
-- is refused, with or without upsert. The second migration,
-- 20261011000000_functions_realtime_and_photo_updates.sql, adds the owner's update policy, which
-- Storage's replace and move need.
drop policy if exists "task-photos: owner reads" on storage.objects;
create policy "task-photos: owner reads" on storage.objects
  for select to authenticated
  using (
    bucket_id = 'task-photos'
    and (storage.foldername(name))[1] = (select auth.uid()::text)
  );

drop policy if exists "task-photos: owner uploads" on storage.objects;
create policy "task-photos: owner uploads" on storage.objects
  for insert to authenticated
  with check (
    bucket_id = 'task-photos'
    and (storage.foldername(name))[1] = (select auth.uid()::text)
  );

drop policy if exists "task-photos: owner deletes" on storage.objects;
create policy "task-photos: owner deletes" on storage.objects
  for delete to authenticated
  using (
    bucket_id = 'task-photos'
    and (storage.foldername(name))[1] = (select auth.uid()::text)
  );

-- =============================================================================================
-- how to reset
-- =============================================================================================
--
-- Removes everything this file created. SQL cannot delete Storage rows (Supabase blocks it, so
-- no stored file is left without its row), so first delete the bucket in the dashboard:
-- Storage > Files > task-photos > Delete bucket. That deletes its files too. Then uncomment and
-- run:
--
-- drop policy if exists "task-photos: owner reads" on storage.objects;
-- drop policy if exists "task-photos: owner uploads" on storage.objects;
-- drop policy if exists "task-photos: owner deletes" on storage.objects;
-- drop table if exists public.task_photos;
-- drop table if exists public.tasks;
--
-- The moddatetime extension stays, in case anything else uses it. So do the anonymous users,
-- under Authentication > Users; delete them there, or with the line below. Supabase refuses to
-- delete a user who still owns files, which is why the bucket goes first.
--
-- delete from auth.users where is_anonymous;

samples/supabase/migrations/20261011000000_functions_realtime_and_photo_updates.sql

-- Offline Tasks: the second migration, after 20260930000000_offline_tasks.sql.
--
-- Two database functions the samples call through `rpc`, a Storage policy that lets a user
-- replace and move their own photo files, and the two tables added to Realtime's publication so
-- the samples can apply other devices' changes as they happen.
--
-- Run it after the first file: `supabase db push` from samples/ applies both in order, or paste
-- each file into the SQL Editor and run it, the first one first. Like the first, it can be run
-- again and changes nothing. Step by step:
-- https://nesfeder.github.io/supabase-offline-kt/samples/supabase-setup/

-- =============================================================================================
-- Functions
-- =============================================================================================

-- Marks every open task of the caller done, and returns how many it changed. It runs as the
-- caller (security invoker), so the tasks policies decide which rows it reaches: only their own.
create or replace function public.complete_all_tasks()
returns integer
language sql
security invoker
set search_path = ''
as $$
  with changed as (
    update public.tasks
    set done = true
    where user_id = (select auth.uid()) and not done
    returning 1
  )
  select count(*)::integer from changed;
$$;

-- How many of the caller's tasks are done; with `created_since`, only those created then or
-- later. Called with no arguments, it counts them all.
create or replace function public.tasks_done_count(created_since timestamptz default null)
returns integer
language sql
stable
security invoker
set search_path = ''
as $$
  select count(*)::integer
  from public.tasks
  where user_id = (select auth.uid())
    and done
    and (created_since is null or created_at >= created_since);
$$;

-- Signed-in users only, as for the tables. Postgres grants execute to everyone by default.
revoke all on function public.complete_all_tasks() from public, anon;
revoke all on function public.tasks_done_count(timestamptz) from public, anon;
grant execute on function public.complete_all_tasks() to authenticated, service_role;
grant execute on function public.tasks_done_count(timestamptz) to authenticated, service_role;

-- =============================================================================================
-- Storage: replacing and moving a photo file
-- =============================================================================================

-- Storage's update (replacing a file in place) and move (renaming it) both need an update policy;
-- copy needs only the first file's read and upload policies. Each only under the user's own
-- folder, before and after.
drop policy if exists "task-photos: owner updates" on storage.objects;
create policy "task-photos: owner updates" on storage.objects
  for update to authenticated
  using (
    bucket_id = 'task-photos'
    and (storage.foldername(name))[1] = (select auth.uid()::text)
  )
  with check (
    bucket_id = 'task-photos'
    and (storage.foldername(name))[1] = (select auth.uid()::text)
  );

-- =============================================================================================
-- Realtime: the two tables in the supabase_realtime publication
-- =============================================================================================

-- Realtime sends a table's changes only when the table is in this publication. An insert or an
-- update reaches only the users whose select policies admit the row. A delete is not filtered:
-- Supabase applies no row-level security to delete events, so every subscriber receives each
-- deleted row's primary key, its id, whoever owned it. Here an id is a random UUID and says
-- nothing; a table whose keys mean something does not belong in the publication on these terms.
-- Each table is added once, so a second run changes nothing.
do $$
declare
  table_name text;
begin
  if not exists (select 1 from pg_catalog.pg_publication where pubname = 'supabase_realtime') then
    raise exception 'there is no supabase_realtime publication'
      using hint = 'Hosted Supabase projects have one; turn Realtime on for the project first.';
  end if;
  foreach table_name in array array['tasks', 'task_photos'] loop
    if not exists (
      select 1
      from pg_catalog.pg_publication_tables t
      where t.pubname = 'supabase_realtime'
        and t.schemaname = 'public'
        and t.tablename = table_name
    ) then
      execute format('alter publication supabase_realtime add table public.%I', table_name);
    end if;
  end loop;
end
$$;

-- =============================================================================================
-- how to reset
-- =============================================================================================
--
-- Removes everything this file created. Run it before the first file's reset. Uncomment and run:
--
-- alter publication supabase_realtime drop table public.tasks, public.task_photos;
-- drop policy if exists "task-photos: owner updates" on storage.objects;
-- drop function if exists public.tasks_done_count(timestamptz);
-- drop function if exists public.complete_all_tasks();