ADR 0002 — Teams, manager oversight & quote approval

Status: accepted · Date: 2026-07-25 · Supersedes: none

Context

Sawdust today is single-user. Every row is owned by one profile; profiles.id = auth.users.id, business_name lives on the profile, and RLS everywhere is profile_id = auth.uid(). State-machine tables (quotes, quote_items, invoices, payments) are SELECT-only even for the owner — all writes go through security definer RPCs, one writer per transition.

We want to support tradespeople who work with a team:

1. At signup, a user says whether they work alone or with a team.
2. A manager can see multiple staff members’ jobs and money in one view.
3. A user can switch from sole to team later (someone joins) without a data migration.
4. A manager can intervene — edit, send, void across staff, not just observe.
5. New/untrusted staff have their quotes approved by the manager before the client sees them; trusted staff send directly.

This ADR picks an ownership model and sketches the schema, RPC, state-machine, and UI changes. It is deliberately a proposal — the edge-case section exists to be argued with before anything is built. A desktop surface for data-heavy manager work is deferred (see the note at the end).

Decision 1 — Federation, not shared-org

Two ownership shapes were considered:

Model	Who owns clients/quotes/money	Tax / CIS / Stripe	Migration weight	Fits reality of…
A. Shared org (staff)	The business (org)	One entity, one Stripe	Heavy: org_id on every table + full backfill + numbering rework	A firm with PAYE employees
B. Federation (gang)	Each worker (sole trader)	Each worker’s own	Light: additive tables + extra policies	A gaffer with subbies

Adopt model B (federation). Each worker stays their own sole trader and keeps ownership of their clients, quotes, invoices, CIS position, tax, quote numbering, and (later) Stripe account. A manager is an oversight layer bolted on top — visibility plus an intervention path — never an owner of the underlying data.

Why B:

• Sawdust’s whole framing is “UK sole-trader tradespeople.” CIS is already modelled per-person (cis_deduction_pence per quote/invoice). A subbie gang is N sole traders with a gaffer, not one company with staff — B mirrors that.
• Ownership never merges, so “solo → team” and “leave team” are pure visibility grants: no data migration when someone joins or leaves.
• Additive only. Existing own RLS policies and existing writer RPCs stay correct; we add a manager overlay rather than rewriting the foundation.
• When we later build gaffer-pays-subbie CIS deductions, ownership is already in the right place.

Model A is revisited only if a customer is genuinely one company with employees who have no independent tax identity (see “Revisit when”).

Decision 2 — Manager intervention rides the existing RPCs

Because state-machine tables are already SELECT-only with all writes funnelled through definer RPCs, manager write costs almost no RLS change. The guard inside each writer widens from:

-- was
where id = p_id and profile_id = auth.uid()
-- now
where id = p_id
  and (profile_id = auth.uid() or profile_id in (select profiles_i_manage()))

A manager calling send_quote / void_quote / create_invoice_draft on a member’s row just works; the worker’s own calls are unchanged. For the direct-CRUD tables (clients, expenses) we add a manager clause to their for all policy.

Every worker still primarily controls their own jobs; the manager path is an override, and every override is stamped with acted_by for audit (see schema impact).

Decision 3 — Approval gate is a per-member flag plus one quote state

Trust is a property of membership, not of the person globally: team_members.can_send_direct boolean, default false (new joiners are gated until promoted). The quote state machine gains one state:

draft ──submit──▶ pending_approval ──approve──▶ sent ──▶ accepted | declined
  ▲                      │
  └──────── reject(reason) ┘

trusted member / manager:  draft ──send──▶ sent   (skips pending_approval)

pending_approval quotes have no public_token and are never returned by get_quote_by_token, so the anon/client path is completely untouched by any of this.

Schema impact

New tables (all carry updated_at + deleted_at per ADR 0001):

create table teams (
  id uuid primary key default gen_random_uuid(),
  name text not null,
  manager_profile_id uuid not null references profiles(id) on delete cascade,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create table team_members (
  team_id uuid not null references teams(id) on delete cascade,
  profile_id uuid not null references profiles(id) on delete cascade,
  can_send_direct boolean not null default false,
  joined_at timestamptz not null default now(),
  primary key (team_id, profile_id)
);

create table invitations (
  id uuid primary key default gen_random_uuid(),
  team_id uuid not null references teams(id) on delete cascade,
  email text not null,
  token text unique not null,          -- emailed to invitee
  can_send_direct boolean not null default false,
  expires_at timestamptz not null,
  accepted_by uuid references profiles(id),
  accepted_at timestamptz,
  created_at timestamptz not null default now()
);

Add to existing tables:

• quotes: extend quote_status enum with pending_approval; add approved_by uuid, approved_at timestamptz, rejection_reason text, acted_by uuid (last actor on a state write).
• invoices / other state-machine writes: acted_by uuid for the same audit reason (manager intervention visibility).

Enum caveat: alter type quote_status add value 'pending_approval' cannot be used in the same transaction that first references it in some Postgres versions. Keep the enum-add as its own migration statement, separate from the RPCs that use it.

business_name stays on profiles — in model B the profile is the business. teams.name is just the crew label (“Dave’s lads”), not a tax entity.

Helper functions

-- profile_ids the caller manages (members of any team they run)
create function profiles_i_manage() returns setof uuid
language sql stable security definer set search_path = public as $$
  select tm.profile_id
  from teams t
  join team_members tm on tm.team_id = t.id
  where t.manager_profile_id = auth.uid()
$$;

Grant to authenticated. Used by both the added SELECT policies and the widened RPC guards, so the “who can a manager touch” rule lives in exactly one place.

RLS changes

Keep every existing own policy untouched (writes stay owner-only; state-machine tables stay SELECT-only). Add a second permissive SELECT policy per data table — Postgres ORs policies together, so this grants read without granting write:

create policy "manager reads team quotes" on quotes
  for select using (profile_id in (select profiles_i_manage()));

Same added SELECT clause on quote_items (via parent), invoices, payments, clients, expenses, reminders. For clients and expenses (direct-CRUD), also extend the for all policy so a manager can edit them.

RPC changes

• Writers (send_quote, void_quote, create_invoice_draft, rebate writers…): widen the ownership guard to own OR managed; stamp acted_by = auth.uid().
• send_quote gains a trust check: if the caller is the owner and that owner is an untrusted member (can_send_direct = false) of a team, reject with “submit for approval instead.”
• New submit_for_approval(p_id uuid) — untrusted member. draft → pending_approval. No token, no delivery.
• New approve_quote(p_id uuid) — manager only, must manage the owner. pending_approval → sent. Reuses send internals: refactor a private _finalise_send(p_id) (snapshot totals + mint public_token + flip status) called by both send_quote and approve_quote.
• New reject_quote(p_id uuid, p_reason text) — manager. pending_approval → draft + rejection_reason. Member revises and resubmits.
• New team/invite RPCs: create_team(name), create_invite(team_id, email, can_send_direct), accept_invite(token), set_member_trust(team_id, profile_id, can_send_direct), remove_member(team_id, profile_id), leave_team(team_id).
• New read RPC get_team_overview() — members with per-member aggregates (open quotes, outstanding invoices, totals) for the manager dashboard; must filter through profiles_i_manage().

All new functions: revoke from public, anon, authenticated then grant authenticated only (per the least-privilege rule in CLAUDE.md). None touch anon.

State machine consequences

“One writer per transition” holds — each RPC is still the single writer for its transition. What widens is the actor set (owner or manager) and, for approval, the number of transitions:

• draft → pending_approval — member (submit_for_approval)
• pending_approval → sent — manager (approve_quote, via _finalise_send)
• pending_approval → draft — manager (reject_quote)
• draft → sent — trusted member or manager (send_quote, via _finalise_send)

acted_by + approved_by give a full trail: who drafted, who approved, who sent, who last touched.

UI

Signup — branch: Just me (no team row, silent) · I run a team (creates a teams row, user is manager) · Join a team (enter invite code → accept_invite).

Untrusted member — the “Send” button becomes “Submit for approval”; status chip reads Awaiting approval; no shareable link until approved. A rejected quote shows the manager’s note and is editable again.

Manager — a team view: members list, each member’s jobs + money, aggregate totals, filter by member; an approval queue (pending quotes across the team → open → Approve (sends) / Reject (+ reason)), with a nav badge for the pending count; per-member “can send directly” toggle in team settings; the ability to open any member’s quote/invoice and act on it (the intervention path).

Trusted member — unchanged, plus a small “in [team]‘s team” badge.

Scope line

Approval gates the quote send transition only for the MVP. Invoices, voids, and rebates (money-affecting) can adopt the same gate later — identical pattern (_finalise_* + trust check) — but are out of scope now. Manager write-intervention, by contrast, applies to all writers from the start.

Edge cases & open questions (for review)

These are unresolved on purpose — the point of the review.

1. Multiple managers / nested teams. One team has one manager_profile_id. Can a worker be in two teams (two managers see them)? The schema allows it (composite PK). Do we want it, or enforce one team per worker? Nested crews (gaffer → chargehand → labourer) are explicitly not modelled.
2. Is the manager also a worker? Usually yes — a working gaffer with their own jobs. They own their data and manage others. profiles_i_manage() naturally excludes themselves, so their own data is owner-scoped and the team data is manager-scoped. Confirm that’s the intended split.
3. Trust is per-membership, not global. A person trusted in team A but new to team B. can_send_direct on team_members handles this; confirm it’s not surprising.
4. Manager intervention audit surface. acted_by records it in the DB, but should the worker see “your manager sent/edited this”? And should the client ever know? (Probably: worker yes, client no.)
5. Approval + offline (ADR 0001). An untrusted member submits for approval offline — the mutation queues. Does the manager’s approval queue show it only after sync? Interaction between the mutation queue and pending_approval needs a pass.
6. Removing a member / leaving. Visibility revokes cleanly (data was never the manager’s). But in-flight pending_approval quotes owned by a departing member — do they revert to draft, or stay stuck? Define the cleanup.
7. Invite security. Invite by email → token link. Does accepting require the invitee’s email to match the invite, or is holding the token enough? Expiry length? Re-invite/revoke flow. This is a new surface (invite acceptance) — needs its own /supabase-security-audit pass even though the RPC is authenticated.
8. Quote numbering. Stays per-worker (Q-0001 per profile) — a manager viewing the team sees each worker’s own sequence. Is a business-wide sequence ever expected? (Model B says no; flag if that assumption breaks.)
9. Money aggregation semantics. The manager sees members’ money, but it is not the manager’s turnover — each worker’s tax stays theirs. The team view must label aggregates as “team activity,” not “your revenue,” to avoid a tax-reporting foot-gun.

Consequences

• New tables follow the ADR 0001 rule: updated_at + deleted_at, soft-delete only, updated_at trigger.
• New tables follow the CLAUDE.md state-machine rule where applicable: SELECT-only + write RPCs for anything with a transition (invitations acceptance goes through an RPC, not direct insert).
• profiles_i_manage(), approve_quote, submit_for_approval, reject_quote, get_team_overview, and the invite RPCs all join the security-critical list — add to /supabase-security-audit.
• architecture-schema-cost-model.md needs a “multi-user / teams” section once this ADR is accepted; the current doc says “multi-user is out of scope” (rate-cards note) — that line gets superseded.
• Cost model barely moves: a few extra rows and one email per invite. No new per-user marginal cost.

Potential implementation steps

Not committed — sequenced sketch for when this ADR moves to accepted. Each step shippable on its own.

1. Migration: team tables — teams, team_members, invitations with updated_at/deleted_at + triggers; profiles_i_manage() helper; grants.
2. Migration: manager read overlay — added SELECT policies on quotes, quote_items, invoices, payments, clients, expenses, reminders; manager write clause on clients/expenses.
3. Migration: manager write override — widen writer-RPC guards to own OR managed; add acted_by columns + stamping.
4. Migration: approval gate — enum add pending_approval (standalone statement); approved_by/approved_at/rejection_reason columns; refactor _finalise_send; add submit_for_approval, approve_quote, reject_quote; trust check in send_quote.
5. Migration: team/invite RPCs — create_team, create_invite, accept_invite, set_member_trust, remove_member, leave_team, get_team_overview.
6. Security audit — /supabase-security-audit over the whole set before any app work merges.
7. App: signup branch + invite accept flow.
8. App: manager team view + approval queue + intervention.
9. App: untrusted-member submit/rejected UX.

Implementation status (2026-07-25)

Built and verified locally (migrations apply on a clean Postgres; rebates_test.sql + new teams_test.sql green; bun run check + app tests pass). Migrations 20260725000000..03.

Delivered as specced:

• Team tables, profiles_i_manage(), read overlay (incl. a profiles manager-read policy so member names render), team/invite RPCs, get_team_overview.
• Manager write-override on the lifecycle writers (void_quote, void_invoice, reopen_invoice, send_invoice, update_invoice_draft_total, record_manual_payment) + send_quote; acted_by on quotes/invoices; manager for all on clients.
• Approval gate: pending_approval, _finalise_send, submit_for_approval / approve_quote / reject_quote, trust check in send_quote.
• App: @sawdust/schemas contracts; lib/teams/queries.ts; Team + Approvals + Join a team screens; quote-detail submit/approve/reject + rejection/awaiting banners; sidebar entries. Personal lists (listQuotes, invoice lists, etc.) filtered to profile_id = uid so a manager’s own lists don’t absorb members’ rows.

Scoped out of this pass (deliberate):

• Manager-applied rebates (apply_/remove_quote_rebate, invoice variants) not widened — they insert an owner-stamped rebates row, so “whose rebate is it” needs a decision first. Members/managers still apply rebates to their own drafts.
• create_quote_draft / create_invoice_draft not widened — a manager creating as a member is a separate “act as” feature. Intervention is on existing rows.
• Signup-time solo/team choice delivered as a Join-a-team screen + Team create screen rather than a branch inside the signup form (auth confirmation flow made an inline branch awkward). Open question #4 (worker sees “manager acted”), invoice/void/rebate approval gates, and the /supabase-security-audit pass remain follow-ups.

Note (project gotcha): expo typed routes live in .expo/types/router.d.ts and only regenerate when expo start runs, so tsc can’t validate new route literals until then — boot expo briefly (EXPO_OFFLINE=1 expo start) to emit them before typechecking new screens.

Deferred — desktop surface

Not in MVP scope. When managers accumulate enough data that the phone gets cramped, add a desktop surface. Because the whole stack is React this is cheap to bolt on later and carries no lock-in cost from deferring:

• First option — Expo web. expo-router already targets web; expo export --platform web gives the manager view on a big screen for near-zero new code. Real work is responsive layout for data-dense tables (phone screens ≠ good desktop) and web session persistence (native uses expo-secure-store; web needs a localStorage/cookie adapter).
• Later — dedicated dashboard. If data density outgrows Expo web (large sortable tables, multi-pane, keyboard triage), build a React app on Cloudflare Pages using supabase-js authenticated in the browser — same RLS and RPC guards enforce everything. Matches the move off Lambda toward Cloudflare.

Either way, security is identical to the native app: the browser holds a Supabase session, RLS + RPC guards do the work.

Revisit when

• A customer is genuinely one company with PAYE employees who have no independent tax identity → reconsider model A (shared org) for that segment.
• Managers ask for nested crews / multiple managers per worker → extend team_members semantics.
• Approval is wanted on invoices/voids/rebates → extend the _finalise_* + trust pattern.
• Managers accumulate enough data that the phone is cramped → build the deferred desktop surface (Expo web first).