# 0014 — Permissions System Migration Spec

Hand this whole file to Claude Code as the task spec. It supersedes the
"4-boolean" approach discussed earlier — this is the scalable version meant
to survive scheduling, QPI, knowledge base, leaderboard, etc. being added to
Settings later without a new migration each time.

---

## 0. Pre-flight: resolve the existing-column conflict first

`profiles` (from 0008) already has:
```sql
is_invoice_reviewer   boolean not null default false,
is_invoice_approver   boolean not null default false,
```

These get **folded into `permissions`** as `feature_key = 'invoicing'` rows,
not left running in parallel. Specifically:
- `is_invoice_reviewer = true` -> insert `(user_id, 'invoicing', can_view=true, can_write=false)`
- `is_invoice_approver = true` -> insert `(user_id, 'invoicing', can_view=true, can_write=true)`
  (approver implies reviewer; write implies view — enforce this in the app
  layer or a check constraint, see Section 3)

After the data migration, **do not drop the old columns yet** — mark them
deprecated in a comment, keep them read-only for one release cycle in case
any existing RLS policy or app code still references them directly. Actually
dropping them is a follow-up migration (0015+) once Claude Code confirms
nothing else reads them.

---

## 1. New tables

```sql
-- 0014_permissions_system.sql

alter table public.profiles
  add column if not exists is_admin boolean not null default false;

create table if not exists public.feature_registry (
  feature_key   text primary key,
  display_name  text not null,
  display_order integer not null default 0,
  created_at    timestamptz not null default now()
);

insert into public.feature_registry (feature_key, display_name, display_order) values
  ('invoicing', 'Invoicing', 1)
on conflict (feature_key) do nothing;

create table if not exists public.permissions (
  user_id     uuid not null references public.profiles(id) on delete cascade,
  feature_key text not null references public.feature_registry(feature_key),
  can_view    boolean not null default false,
  can_write   boolean not null default false,
  granted_by  uuid references public.profiles(id),
  granted_at  timestamptz not null default now(),
  primary key (user_id, feature_key),
  constraint write_implies_view check (not can_write or can_view)
);

create index if not exists idx_permissions_user on public.permissions(user_id);
create index if not exists idx_permissions_feature on public.permissions(feature_key);
```

## 2. Data migration (fold in existing flags)

```sql
insert into public.permissions (user_id, feature_key, can_view, can_write)
select id, 'invoicing', true, is_invoice_approver
from public.profiles
where is_invoice_reviewer = true or is_invoice_approver = true
on conflict (user_id, feature_key) do update
  set can_write = excluded.can_write or permissions.can_write;
```

## 3. RLS pattern (use this shape for every feature-gated table)

```sql
create or replace function public.has_permission(feature text, need_write boolean default false)
returns boolean
language sql
security definer
stable
as $$
  select exists (
    select 1 from public.permissions
    where user_id = auth.uid()
      and feature_key = feature
      and (not need_write or can_write)
  )
  or exists (
    select 1 from public.profiles where id = auth.uid() and is_admin = true
  );
$$;

drop policy if exists invoice_settings_write_todo on public.invoice_settings;
create policy invoice_settings_write on public.invoice_settings
  for update using (public.has_permission('invoicing', need_write => true));

create policy rate_history_write on public.rate_history
  for insert with check (public.has_permission('invoicing', need_write => true));

create policy schedule_admin_write on public.schedule
  for update using (public.has_permission('invoicing', need_write => true));
```

`has_permission()` checks `is_admin` as a universal override — admins don't
need a row in `permissions` for every feature, they bypass by flag. Everyone
else needs an explicit `permissions` row per feature.

## 4. Settings -> Users page (`/settings/users`)

- Table: one row per profile (`agent_id`, email from `auth.users`, `is_admin` toggle).
- Expand row (or separate `/settings/users/[id]`) -> checklist rendered from
  `feature_registry` joined against that user's `permissions` rows — one
  view/write checkbox pair per feature currently in the registry. Adding a
  feature later means it just appears as a new row automatically; no page
  code change needed.
- Write access to this page itself: gated by `is_admin = true` only (not a
  `permissions` row — same bootstrap reasoning as Section 1).
- Every checkbox toggle -> upsert into `permissions`, set `granted_by = auth.uid()`.

## 5. Invite flow

- "Invite user" button on `/settings/users`, admin-only.
- Server action (Next.js route handler, never client-side) calls
  `supabase.auth.admin.inviteUserByEmail(email)` using the **service role
  key** — this key lives only in server environment variables, never
  shipped to the browser.
- On accept, a Postgres trigger on `auth.users` insert (or the app's
  post-signup hook) creates the matching `profiles` row with `is_admin =
  false` and zero `permissions` rows — admin then grants access manually
  from the Users page.

## 6. One-time manual bootstrap (do this yourself, before any of the above works)

```sql
update public.profiles set is_admin = true where agent_id = 'edwin';
```

No UI can do this for you — it's the seed admin. Run it directly in the
Supabase SQL editor once, after 0014 is applied.

## 7. Order of operations for Claude Code

1. Write `0014_permissions_system.sql` containing Sections 1-2 above.
2. Show you the file before running (same standard as 0011-0013).
3. After you run it: build `/settings/users` (Section 4).
4. Build the invite server action (Section 5).
5. Rewrite the 0011/0013 TODO policies using `has_permission()` (Section 3)
   — this is what actually closes the open-write-access gap flagged earlier.
6. Do NOT drop `is_invoice_reviewer`/`is_invoice_approver` columns yet —
   that's a separate 0015 once nothing else references them.