Free Hosted Boards

The multi-tenant core for the free boards — tenants, members and per-tenant row scoping with RLS. Idempotent DDL.

-- ---------------------------------------------------------------------------
-- /todos multi-tenancy: instances ("boards") + members.
--
-- Any signed-in user can 1-click provision their own to-do board at /t/<slug>;
-- the original Jake+Gina household board migrates to tenant #1 (slug
-- 'household', pinned by HOUSEHOLD_SLUG in src/lib/todos/tenant.js). The bare
-- /todos surface and all its bridges (mail / bills / calendar / gcal / digest)
-- keep working unchanged: by convention a todo_items row with NULL instance_id
-- belongs to the household instance, so legacy writers never break. The
-- backfill below stamps existing rows anyway for explicitness.
--
-- for_who: the old CHECK ('jake','gina','ours') is dropped — tenant boards
-- have arbitrary member ids. Validation moves app-side (the per-instance
-- member roster + 'ours'), the same posture as the categories column.
--
-- PII / access posture: RLS on, NO anon policy — every read/write goes through
-- the service-role client behind NextAuth (membership-gated per instance).
--
-- Apply to the MAIN Supabase project (diziibeymowzpondeqxz). Additive,
-- idempotent. (Applied 2026-06-09.)
-- ---------------------------------------------------------------------------

-- --- todo_instances ---------------------------------------------------------
create table if not exists public.todo_instances (
  id           uuid primary key default gen_random_uuid(),
  slug         text not null unique,            -- the /t/<slug> URL key
  name         text not null,
  owner_email  text not null,                   -- lowercased
  archived_at  timestamptz,
  created_at   timestamptz not null default now(),
  updated_at   timestamptz not null default now()
);
comment on table public.todo_instances is
  'One row per to-do board ("tenant"). The household board is slug=household; every other row is a self-served instance provisioned from /t.';

drop trigger if exists todo_instances_set_updated_at on public.todo_instances;
create trigger todo_instances_set_updated_at
  before update on public.todo_instances
  for each row execute function public.todos_set_updated_at();

create index if not exists todo_instances_owner_idx on public.todo_instances (owner_email);

-- --- todo_instance_members ---------------------------------------------------
create table if not exists public.todo_instance_members (
  id           uuid primary key default gen_random_uuid(),
  instance_id  uuid not null references public.todo_instances(id) on delete cascade,
  email        text not null,                   -- lowercased
  user_id      text not null,                   -- the for_who / created_by id within this instance
  name         text not null,
  emoji        text,
  role         text not null default 'member' check (role in ('owner', 'member')),
  created_at   timestamptz not null default now(),
  unique (instance_id, email),
  unique (instance_id, user_id)
);
comment on table public.todo_instance_members is
  'Per-board roster. user_id is the short id stored in todo_items.for_who / .created_by for that board; emails are stored lowercased.';

create index if not exists todo_instance_members_email_idx on public.todo_instance_members (email);

-- --- todo_items: tenancy column + the for_who unlock -------------------------
alter table public.todo_items
  add column if not exists instance_id uuid references public.todo_instances(id);
create index if not exists todo_items_instance_idx on public.todo_items (instance_id);

-- App-side validation now owns for_who (per-instance member ids + 'ours').
alter table public.todo_items drop constraint if exists todo_items_for_who_check;

-- --- RLS (no anon policy; service-role only) ---------------------------------
alter table public.todo_instances enable row level security;
alter table public.todo_instance_members enable row level security;

-- --- Seed: the household board becomes tenant #1 ------------------------------
insert into public.todo_instances (slug, name, owner_email)
values ('household', 'Lawrence household', 'jakealanlawrence@gmail.com')
on conflict (slug) do nothing;

insert into public.todo_instance_members (instance_id, email, user_id, name, emoji, role)
select i.id, m.email, m.user_id, m.name, m.emoji, m.role
from public.todo_instances i
join (values
  ('jakealanlawrence@gmail.com', 'jake', 'Jake', '🧔', 'owner'),
  ('ginaklawrence@gmail.com',    'gina', 'Gina', '👩', 'member')
) as m(email, user_id, name, emoji, role) on true
where i.slug = 'household'
on conflict (instance_id, email) do nothing;

-- Backfill: every pre-tenancy row belongs to the household (NULL still reads as
-- household at the app layer, so a legacy writer racing this is harmless).
update public.todo_items
set instance_id = (select id from public.todo_instances where slug = 'household')
where instance_id is null;

The "publish to a public, indexed read-only view" feature — the is_public flag plus the anon-read policy.

-- ---------------------------------------------------------------------------
-- /t todo boards: publishable (public) boards.
--
-- Adds is_public to todo_instances. When set, the board renders at /t/<slug>
-- for anyone (no sign-in) and is indexable; the roster + emails stay private
-- (the public page never lists members). Anon read is a server-proxy
-- (service-role behind the public page + the items GET route), NOT an anon RLS
-- policy — consistent with the rest of /t. Hence: no RLS change, just the
-- column + a partial index for sitemap enumeration. Mirrors
-- supabase/weather_public_boards_schema.sql.
--
-- Apply to the MAIN Supabase project (diziibeymowzpondeqxz). Additive,
-- idempotent.
-- ---------------------------------------------------------------------------

alter table public.todo_instances
  add column if not exists is_public boolean not null default false;

comment on column public.todo_instances.is_public is
  'When true, the board renders publicly at /t/<slug> (read-only task list, no roster) and is indexable. Toggled by the owner; read by the public page + sitemap.';

create index if not exists todo_instances_public_idx
  on public.todo_instances (is_public) where is_public;