# Current RPCs, RLS, and permission functions — exact semantics
Purpose: pin the FINAL behavior of every Postgres function/RPC, every
RLS policy, every TypeScript permission function, and the effective-user
(view-as) resolution chain — precisely enough to reimplement each RPC in
Python (`services/reporting/`, `services/money/`, `services/authz/`)
and write equivalence tests against the live app, without reading the
old code.
Sources of truth (verified 2026-07-06 against code, not just extraction
notes): all 41 files in `supabase/migrations/`
(`20260520120001_init.sql` → `20260609000010_projects_rls_org_member.sql`),
`lib/permissions.ts`, `lib/effective-user.ts`, `lib/supabase/server.ts`,
`lib/supabase/middleware.ts`, `lib/supabase/view-as-jwt.ts`,
`lib/view-as-guards.ts`, `lib/teams.ts`, `lib/org.ts`,
`app/actions/view-as.ts`, `middleware.ts`.
Conventions used below:
- "SQL, stable, INVOKER" = `language sql`, `stable`,
`security invoker`, `set search_path = public`. All functions live in
schema `public`.
- **SECURITY INVOKER matters**: RLS applies INSIDE every read RPC. A
manager calling `entries_filter_totals` only aggregates rows the
`time_entries` policies let them see. A Python rebuild without RLS
must re-apply the §3.7 scoping rule inside EVERY aggregate query or
managers' reports will include other importers' data.
- All `time_entries.start_at` / `end_at` are **naive `timestamp`**
(no TZ) — Clockify exports local times and the strings are preserved
exactly. All date windows are half-open `[start, end)` naive
comparisons on `start_at`. Everything else in the schema is
`timestamptz`.
---
## 1. The shared filter contract (entries-family RPCs)
`entries_filter_totals`, `entries_filter_options`, `project_summary`,
and (with renamed params) `project_entries_by_description` all apply
the same ANDed filter set over `time_entries te`. Every param defaults
to NULL (or `'all'` / `false`) = filter off.
| Param | Type | Predicate (exact) |
|---|---|---|
| `p_org_id` | uuid, required | `te.org_id = p_org_id` |
| `p_start` | timestamp | `te.start_at >= p_start` |
| `p_end` | timestamp | `te.start_at < p_end` (exclusive) |
| `p_batch` | uuid | `te.import_id = p_batch` (rows with NULL `import_id` never match) |
| `p_status` | text, default `'all'` | see §1.1 |
| `p_q` | text | `te.project ILIKE '%'||p_q||'%' OR te.description ILIKE … OR te.source_user_email ILIKE … OR te.client ILIKE …` — free-text OR-search over exactly these 4 columns. NOT `operator`, NOT `converted_user`. |
| `p_project` | text | `te.project ILIKE '%'||p_project||'%'` |
| `p_user` | text | `te.converted_user ILIKE '%'||p_user||'%'` |
| `p_source_email` | text | `te.source_user_email ILIKE '%'||p_source_email||'%'` |
| `p_client` | text | `te.client ILIKE '%'||p_client||'%'` |
| `p_operator` | text | `te.operator ILIKE '%'||p_operator||'%'` |
| `p_imported_by` | uuid | `EXISTS (SELECT 1 FROM clockify_imports ci WHERE ci.id = te.import_id AND ci.imported_by = p_imported_by)` |
| `p_missing_info` | boolean, default false (project_summary + project_entries_by_description only) | `coalesce(p_missing_info,false) = false OR (te.operator IS NULL OR te.client IS NULL OR te.project IS NULL)` |
Semantics that equivalence tests must reproduce:
- All text filters are **ILIKE contains**, not exact match: filtering
operator `"Bowden"` matches `"Bowden Works"` and `"Bowden East"`.
- Search strings are concatenated raw into the pattern — **`%` and `_`
inside user input act as wildcards** (no escaping anywhere).
- ILIKE against a NULL column is NULL → row excluded. So e.g.
`p_client='x'` silently drops entries whose `client` is NULL.
- `p_q` deliberately excludes `operator` and `converted_user`.
- The filters (and RLS) also apply to `entries_filter_options`, so the
dropdown option arrays are **cascading** — filter-aware and
visibility-scoped.
### 1.1 The canonical status predicate (post-invoice, from `20260525000011`)
Used identically in `entries_filter_totals`, `entries_filter_options`,
`project_summary`. `p_status` values:
- `'all'` — no status restriction.
- `'pending'` — ALL of:
`invoice_id IS NULL`, `transferred_at IS NULL`,
`team_member_id IS NOT NULL`, `operator IS NOT NULL`,
`client IS NOT NULL`, `project IS NOT NULL`,
`description IS NOT NULL`, `end_at IS NOT NULL`,
`source_user_email IS NOT NULL`.
- `'transferred'` OR `'applied'` (interchangeable aliases) —
`invoice_id IS NOT NULL OR transferred_at IS NOT NULL`.
(`invoice_id` is the canonical lock; `transferred_at` kept in the OR
"for safety — any row that somehow escaped the backfill".)
- `'blocked'` — `invoice_id IS NULL AND transferred_at IS NULL` AND at
least one pending-required field IS NULL (`team_member_id`,
`operator`, `client`, `project`, `description`, `end_at`,
`source_user_email`).
- Any other string → only the `'all'` branch can match → matches
nothing (all four OR branches false). No error is raised.
Invariant: for unlocked rows, `pending` and `blocked` partition them;
locked rows are exactly `transferred/applied`. Every row is in exactly
one of {pending, blocked, transferred}.
History (matters only for the stale RPC in §2.4): the pre-invoice
predicate (v2, `20260522000001` → `20260524000013`) additionally
required `converted_duration_seconds IS NOT NULL` and
`converted_user IS NOT NULL` for pending (and their NULLity counted as
blocked), and keyed transferred off `transferred_at` alone with no
`'applied'` alias and no `invoice_id` awareness.
---
## 2. RPCs — FINAL definitions
All read RPCs: SQL, stable, INVOKER. All write RPCs: SQL, volatile,
INVOKER (→ their UPDATEs are themselves filtered by `time_entries`
UPDATE RLS: a manager's restamp only touches rows they can see).
### 2.1 `entries_filter_totals(...)` → exactly 1 row
Final version: `20260525000011`. Powers the totals card on `/entries`.
```
entries_filter_totals(
p_org_id uuid,
p_start timestamp default null,
p_end timestamp default null,
p_status text default 'all',
p_batch uuid default null,
p_q text default null,
p_project text default null,
p_user text default null,
p_source_email text default null,
p_client text default null,
p_operator text default null,
p_imported_by uuid default null
) returns table (
row_count bigint,
source_seconds bigint,
billout_seconds bigint,
cost_usd numeric,
billout_amount_usd numeric
)
```
Apply the full §1 filter (12 params, no `p_missing_info`), then a
single aggregate row:
- `row_count = count(*)`
- `source_seconds = coalesce(sum(duration_seconds), 0)`
- `billout_seconds = coalesce(sum(duration_seconds), 0)` —
**byte-identical to source_seconds** since proportion elimination
(`20260524000013`: "billout hours = source hours"). Kept as a
duplicate column for UI compatibility only.
- `cost_usd = coalesce(sum(billout_cost_usd), 0)`
- `billout_amount_usd = coalesce(sum(billout_amount_usd), 0)`
Zero matching rows → one row of `(0, 0, 0, 0, 0)`.
### 2.2 `entries_filter_options(...)` → exactly 1 row of arrays
Final version: `20260525000011`. Same 12-param signature as 2.1.
```
returns table (operators text[], clients text[], projects text[],
users text[], source_emails text[])
```
Same filtered CTE (including status + RLS), then five independent
scalar subselects:
```sql
(select array_agg(distinct operator order by operator)
from filtered where operator is not null) as operators
-- …same shape for client → clients, project → projects,
-- converted_user → users, source_user_email → source_emails
```
- Arrays are distinct, ascending, NULLs excluded.
- **Empty result set → the column is SQL `NULL`, not `{}`** (array_agg
over zero rows). The TS caller treats null as empty; a Python port
should normalize to `[]` but equivalence tests must accept NULL.
- ⚠️ Live-DB wart: an **11-param overload** (without `p_imported_by`)
created in `20260522000001` was never dropped. `20260523010001`
dropped/recreated `entries_filter_totals` but only
`create or replace`'d options with a NEW 12-param signature —
creating a second overload. (Verified: no
`drop function … entries_filter_options` exists in any migration.)
The stale overload can cause PostgREST ambiguous-function errors.
Do not port it; drop it during migration.
### 2.3 `project_summary(...)` → one row per (operator, client, project)
Final version: `20260525000011`. Powers `/projects`.
```
project_summary(
p_org_id uuid, p_start timestamp default null, p_end timestamp default null,
p_status text default 'all', p_batch uuid default null, p_q text default null,
p_project text default null, p_user text default null,
p_source_email text default null, p_client text default null,
p_operator text default null, p_imported_by uuid default null,
p_missing_info boolean default false
) returns table (
operator text, client text, project text,
project_entity_id uuid,
row_count bigint, billout_seconds bigint,
cost_usd numeric, billout_amount_usd numeric,
billout_adjusted_usd numeric,
billout_adjustment_pct numeric, billout_adjustment_amount numeric,
income_usd numeric
)
```
Step 1 — `filtered` CTE: full §1 filter including `p_missing_info`.
Step 2 — `agg` CTE grouped by the **raw denormalized text columns**
`(operator, client, project)` — no coalesce; NULLs group together as
NULL (SQL GROUP BY treats NULLs as equal):
- `row_count = count(*)`
- `billout_seconds = coalesce(sum(duration_seconds), 0)`
- `cost_usd = coalesce(sum(billout_cost_usd), 0)`
- `billout_amount_usd = coalesce(sum(billout_amount_usd), 0)`
Step 3 — LEFT JOIN the entity chain by case-insensitive name equality:
```sql
left join operators o on o.org_id = p_org_id and lower(o.name) = lower(agg.operator)
left join clients c on c.operator_id = o.id and lower(c.name) = lower(agg.client)
left join projects p on p.client_id = c.id and lower(p.name) = lower(agg.project)
```
`lower(NULL)` is NULL → groups with a NULL text component never match
an entity row; the LEFT JOIN keeps them with NULL `p.*` columns.
Output extras from `p` (all NULL when no entity row matched):
- `project_entity_id = p.id`
- `billout_adjustment_pct`, `billout_adjustment_amount`, `income_usd`
pass through from `projects`.
- `billout_adjusted_usd = agg.billout_amount_usd
* (1 + coalesce(p.billout_adjustment_pct, 0) / 100)
+ coalesce(p.billout_adjustment_amount, 0)`
— pct stored as `10` = +10%; the fixed amount is added once per
output row for whatever window was requested ("pragmatic, not
perfectly summable across windows", ADR #036). Unmatched entities →
adjusted == raw.
**No ORDER BY** — sorting is app-side.
### 2.4 `project_entries_by_description(...)` — ⚠️ STALE, never updated after creation
Only version: `20260523040001`. Expands one project row on `/projects`
into a per-description breakdown. It was never updated for the
proportion-elimination (`20260524000013`) or invoice
(`20260525000011`) migrations, so it diverges from its parent
`project_summary` in ways the current UI silently exhibits:
```
project_entries_by_description(
p_org_id uuid,
p_operator text, p_client text, p_project text, -- group pin (positional, no defaults)
p_start timestamp default null, p_end timestamp default null,
p_status text default 'all', p_batch uuid default null,
p_q text default null,
p_project_q text default null, -- renamed contains-filters to avoid
p_user text default null, p_source_email text default null,
p_client_q text default null, p_operator_q text default null, -- clashing with the pins
p_imported_by uuid default null, p_missing_info boolean default false
) returns table (
description text, row_count bigint, billout_seconds bigint, cost_usd numeric
)
```
- Group pin is **NULL-safe equality**:
`te.operator IS NOT DISTINCT FROM p_operator` (same for client,
project) — so the `(no project)` NULL groups expand correctly.
- Then the §1 filter with `p_project_q` / `p_client_q` /
`p_operator_q` playing the contains roles.
- ⚠️ Uses the **pre-invoice status predicate** (§1.1 history): pending
additionally requires `converted_duration_seconds` and
`converted_user` NOT NULL; transferred = `transferred_at IS NOT NULL`
only; no `invoice_id` awareness, no `'applied'` alias.
- ⚠️ `billout_seconds = coalesce(sum(converted_duration_seconds), 0)`
— the old converted-hours definition; every sibling switched to
`duration_seconds`.
- Grouped by `description` (NULLs group together), ordered
`billout_seconds desc nulls last`.
**Rebuild decision (binding):** do NOT port the staleness. The rebuild
uses ONE status predicate (§1.1 canonical) and ONE hours definition
(`duration_seconds`) everywhere; the expansion must sum to its parent
row. Equivalence tests against the LIVE app for this one RPC must
either use the stale semantics or (better) assert against the parent
`project_summary` totals and accept documented divergence.
### 2.5 `summary_by_project(...)` — homepage three-window summary
Final version: `20260524000013` (deliberately NOT touched by the
invoice migration — it has no status filter at all; "all-time" always
includes invoiced/transferred entries).
```
summary_by_project(
p_org_id uuid,
p_this_start timestamp, p_this_end timestamp, -- required, no defaults
p_prev_start timestamp, p_prev_end timestamp, -- required, no defaults
p_imported_by uuid default null,
p_operator text default null, p_client text default null,
p_user text default null, p_source_email text default null
) returns table (
operator text, client text, project text,
this_seconds bigint, prev_seconds bigint, all_seconds bigint,
this_cost_usd numeric, prev_cost_usd numeric, all_cost_usd numeric,
this_billout_usd numeric, prev_billout_usd numeric, all_billout_usd numeric,
this_billout_adj_usd numeric, prev_billout_adj_usd numeric, all_billout_adj_usd numeric
)
```
Base filter (note the differences from the §1 contract):
- `te.org_id = p_org_id`
- `te.duration_seconds IS NOT NULL` (rows without a duration don't
exist for this RPC — the entries-family RPCs have no such filter)
- optional `p_imported_by` (same EXISTS as §1)
- optional ILIKE-contains on `operator`, `client`, `converted_user`
(via `p_user`), `source_user_email`. **No `p_q`, no `p_project`
contains-filter, no status, no date bound on the "all" window.**
Grouping — unlike `project_summary`, NULL text gets placeholder labels
BEFORE grouping:
```sql
group by coalesce(te.operator, '(no operator)'),
coalesce(te.client, '(no client)'),
coalesce(te.project, '(no project)')
```
Three windows via CASE-inside-SUM on `start_at` (half-open):
- `this_* = coalesce(sum(CASE WHEN start_at >= p_this_start AND start_at < p_this_end THEN END), 0)`
- `prev_*` same with the prev bounds
- `all_* = coalesce(sum(), 0)` (unconditional)
- `` per family: seconds → `duration_seconds`; cost →
`billout_cost_usd`; billout → `billout_amount_usd`.
Then the same lower(name) entity LEFT JOIN chain as §2.3 — but joining
on the **coalesced labels**, so NULL groups look up entities literally
named `(no operator)` etc. (in practice: never match; adjustments stay
0-effect for them). Per-window adjusted billout:
```
_billout_adj_usd = _billout_usd * (1 + coalesce(pct,0)/100) + coalesce(amount,0)
```
Note: the fixed `billout_adjustment_amount` is added **in full to every
window** (this, prev, AND all) — intentional, per ADR #036.
`ORDER BY this_seconds DESC`.
Caller contract: the app passes this-month / prev-month bounds.
View-as scoping: when a super-admin views-as a non-owner the app forces
`p_imported_by = ` (post-ADR #033 v2 this is redundant
with RLS but still passed).
### 2.6 `restamp_billout_for_project(p_org_id uuid, p_project_id uuid, p_team_member_id uuid default null)` → bigint
Final version: `20260524000013`. Volatile, INVOKER. Called by the
project-rate-override upsert/delete actions. Single UPDATE (replaced a
~10 s N-round-trip loop):
```sql
update time_entries te set billout_amount_usd = case
when te.duration_seconds is null then null
else round((te.duration_seconds::numeric / 3600)
* coalesce(effective_billout_rate(te.team_member_id, te.project_id), 0)
* 100) / 100
end
where te.org_id = p_org_id
and te.project_id = p_project_id
and (p_team_member_id is null or te.team_member_id = p_team_member_id)
and te.duration_seconds is not null
```
Returns count of updated rows (`returning 1` → count). Notes:
- `coalesce(rate, 0)`: entries whose member has NO billout rate get
stamped **0.00** via this path — while the import/edit write path
leaves `billout_amount_usd` NULL when no rate is set. NULL ("never
priced") and 0 ("priced at zero") become indistinguishable after a
restamp. The rebuild must pick ONE semantic (recommendation: NULL
when no rate; never coalesce to 0).
- Rounding is **round-half-away-from-zero on cents**
(Postgres `round(numeric)`), i.e. `round(x*100)/100`. Python must use
`decimal.Decimal` with `ROUND_HALF_UP`, not float/banker's rounding.
- Does NOT touch `billout_cost_usd`.
- INVOKER + UPDATE RLS: only rows visible/updatable to the caller are
restamped.
### 2.7 `restamp_billout_for_entry_ids(p_org_id uuid, p_entry_ids uuid[])` → bigint
Only version: `20260525000001`. Volatile, INVOKER. Powers the bulk
"Recalculate" button on `/entries`.
```sql
update time_entries te set
billout_cost_usd = case
when te.duration_seconds is null or tm.cost_rate_usd is null then null
else round((te.duration_seconds::numeric / 3600) * tm.cost_rate_usd * 100) / 100
end,
billout_amount_usd = case
when te.duration_seconds is null then null
else round((te.duration_seconds::numeric / 3600)
* coalesce(effective_billout_rate(te.team_member_id, te.project_id), 0)
* 100) / 100
end
from team_members tm
where te.org_id = p_org_id
and te.id = any (p_entry_ids)
and te.team_member_id = tm.id
```
Returns count of matched rows. ⚠️ **The function's own doc-comment lies
twice** (verified in code): it claims to return "rows whose cost OR
billout actually changed" (it counts ALL matched rows — unchanged
values still count) and that "rows with no team_member_id get NULL
cost/billout" (the `UPDATE … FROM` inner-join on
`te.team_member_id = tm.id` means such rows are **silently skipped**,
not nulled). Any UI copy saying "N entries changed" is therefore
wrong. Rebuild: define the return as "rows whose stamps changed" and
implement it honestly, and decide explicitly what happens to
`team_member_id IS NULL` rows (recommendation: null their stamps and
include them in the report).
- Explicitly does NOT re-resolve `team_member_id` from
`source_user_email` (that's the per-row "Re-resolve" button).
### 2.8 `set_team_member_for_entry_ids(p_org_id uuid, p_team_member_id uuid, p_entry_ids uuid[])` → bigint
Only version: `20260525000030`. Volatile, INVOKER. Bulk-reassign the
billed member.
```sql
with member as (
select id, display_name, consolidate_as, rate_proportion
from team_members
where id = p_team_member_id and org_id = p_org_id
)
update time_entries te set
team_member_id = m.id,
converted_user = coalesce(m.consolidate_as, m.display_name),
converted_duration_seconds = case
when te.duration_seconds is null then null
else round(te.duration_seconds * m.rate_proportion)::bigint
end
from member m
where te.org_id = p_org_id and te.id = any (p_entry_ids)
```
- Target member must exist in the org, else 0 rows updated (empty CTE).
- Deliberately does NOT touch the money stamps — the caller MUST follow
with `restamp_billout_for_entry_ids` ("the two-step is intentional:
this function only touches identity columns so its scope is
auditable"). Returns rows updated.
- `converted_user` / `converted_duration_seconds` /`rate_proportion`
are Toggl-export legacy (phase-out; see §7) — the rebuild's
equivalent only sets the worker attribution + follows with a restamp.
### 2.9 Dropped functions (do NOT rebuild)
- `summary_by_team_member(uuid, timestamp×4)` — per-member 3-window
report, label `coalesce(tm.display_name, te.source_user_email,
'(unknown)')`; created `20260521190001`, dropped `20260521210001`.
- `time_entry_comments` table + its 3 policies — dropped
`20260522020001` in favor of the org-wide `comments` table.
- All pre-final overloads of `summary_by_project`,
`entries_filter_totals`, `project_summary` were explicitly dropped
along the chain (verified) — EXCEPT the `entries_filter_options`
11-param leftover (§2.2).
---
## 3. Helper SQL functions (FINAL definitions)
All `set search_path = public`.
### 3.1 `set_updated_at()` → trigger
plpgsql. `new.updated_at = now(); return new;` BEFORE UPDATE trigger
on: profiles, team_members, teams, projects, operators, clients,
project_rate_overrides.
### 3.2 `is_org_member(target_org uuid)` → boolean — SQL, stable, INVOKER
`exists (select 1 from organization_members where organization_id =
target_org and user_id = auth.uid())`. INVOKER is safe because
organization_members RLS lets users see their own rows (a user can only
confirm their own memberships). Used by nearly every org-scoped policy.
### 3.3 `current_user_is_super_admin()` → boolean — SQL, stable, **DEFINER**
`coalesce((select is_super_admin from profiles where id = auth.uid()),
false)`. DEFINER explicitly to avoid recursing through profiles RLS.
### 3.4 `current_user_org_ids()` → setof uuid — SQL, stable, **DEFINER**
`select organization_id from organization_members where user_id =
auth.uid()`. Exists to fix the recursive-RLS bug
(`20260521120001`): a policy on organization_members whose USING
subqueries organization_members makes Postgres **silently return zero
rows** — the original co-member policy hid all memberships. Any rebuild
with RLS-like recursion must remember this failure mode.
### 3.5 `is_org_owner(target_org uuid)` → boolean — SQL, stable, INVOKER
`exists (select 1 from organization_members where organization_id =
target_org and user_id = auth.uid() and role = 'owner')`.
### 3.6 `is_super_admin_caller()` → boolean — SQL, stable, **INVOKER**
Same body as 3.3. Works under INVOKER because profiles has a
self-select policy. **Functional duplicate of 3.3 with a different
security mode; both are live and referenced by different policies.**
Rebuild: one implementation.
### 3.7 `user_can_access_entry(p_org_id uuid, p_import_id uuid)` → boolean — SQL, stable, **DEFINER**
THE importer-scoping rule. True iff ANY of:
1. `current_user_is_super_admin()`
2. caller has role `'owner'` in `p_org_id` (direct EXISTS on
organization_members — DEFINER bypasses RLS)
3. `p_import_id IS NOT NULL` AND a `clockify_imports` row exists with
`id = p_import_id AND org_id = p_org_id AND imported_by = auth.uid()`
(the caller uploaded that batch).
Consequences the rebuild MUST preserve as the R1 scope rule:
- Managers/members see and edit ONLY entries from imports **they**
uploaded.
- Entries with `import_id IS NULL` are **invisible to non-owner,
non-super-admin users** (branch 3 requires non-null). Any manual
entry feature must set import_id or managers lose their own rows.
- Combined with the time_entries policy (§4), a super admin still needs
`is_org_member(org_id)` to see entries — the super-admin branch here
only bypasses *importer* scoping, not org membership.
### 3.8 `effective_billout_rate(p_team_member_id uuid, p_project_id uuid)` → numeric — SQL, stable, INVOKER
Final version: `20260524000012`. Since `20260524000013` the returned
rate means **USD per SOURCE hour**.
Exact algorithm:
1. CTE `chosen` = `(priority-1 row: project_rate_overrides where
project_id = p_project_id AND team_member_id = p_team_member_id
[both params non-null]) UNION ALL (priority-2 row: … where
project_id = p_project_id AND team_member_id IS NULL) ORDER BY
priority LIMIT 1` — i.e. the user-specific row wins **regardless of
which kind it is**; may be empty.
2. CTE `member_rate` = `team_members.billout_rate_usd where id =
p_team_member_id` (param non-null).
3. Result CASE:
- chosen `override_rate_usd` NOT NULL → that value (absolute wins
outright);
- elif chosen `override_pct` NOT NULL →
`member_rate.billout_rate_usd * (1 + override_pct/100)`
(NULL member rate × pct = **NULL**, "no rate available");
- else → `member_rate.billout_rate_usd` (may be NULL).
Data rules on `project_rate_overrides` (enforced by schema):
- `override_rate_usd XOR override_pct` (`project_rate_overrides_one_kind`
CHECK). Pct is signed: `-20` = 20% discount, applied to the member's
default rate.
- One project-wide default per project via unique index on
`(project_id, coalesce(team_member_id,
'00000000-0000-0000-0000-000000000000'))`.
Precedence subtlety worth a dedicated equivalence test: because
`LIMIT 1` picks the user-specific row first no matter its kind, a
**pct-based user-specific override with a NULL member default rate
yields NULL even when an absolute project-wide override exists**.
TS wrapper: `getEffectiveBilloutRate(supabase, teamMemberId, projectId)`
in `lib/teams.ts` — returns null immediately when `teamMemberId == null`,
otherwise `supabase.rpc('effective_billout_rate', …)`; called by every
entry-write path right before computing `billout_amount_usd`. Sibling
helpers: `getCostRateForTeamMember` (reads
`team_members.cost_rate_usd`, the canonical cost basis; NULL when unset)
and legacy `getRateForTeamMember` (member's **team** `base_rate_usd` —
deprecated Toggl-era money math).
### 3.9 Money math the RPCs assume (lock-at-write, ADR #014/#034)
- `time_entries.billout_cost_usd` numeric(10,4) — what the org PAYS:
`round(duration_seconds/3600 × team_members.cost_rate_usd × 100)/100`;
NULL if duration or cost_rate NULL.
- `time_entries.billout_amount_usd` numeric(12,2) — what the org
CHARGES: `round(duration_seconds/3600 ×
effective_billout_rate(team_member_id, project_id) × 100)/100`;
NULL at import when no rate; **0.00 via restamp paths** (§2.6/2.7
coalesce).
- Stamps are locked at write; rate changes NEVER silently rewrite
history — only the explicit restamp RPCs do.
- `rate_proportion`, `teams.base_rate_usd`,
`organizations.default_hourly_rate_usd`,
`converted_duration_seconds`, `converted_user` remain in schema but
are deprecated Toggl-era plumbing.
---
## 4. FINAL RLS policy set, per table
RLS is enabled on every table; all policies are `TO authenticated`;
missing operation = denied. The service-role key (seed script) bypasses
RLS entirely. "USING+CHECK" = same expression for both.
### organizations
| Policy | Op | Expression |
|---|---|---|
| `orgs_member_select` | SELECT | `id in (select current_user_org_ids())` |
No write policies (service-role only).
### profiles
| Policy | Op | Expression |
|---|---|---|
| `profiles_self_select` | SELECT | `id = auth.uid()` |
| `profiles_self_update` | UPDATE | USING+CHECK `id = auth.uid()` |
| `profiles_super_admin_select` | SELECT | `current_user_is_super_admin()` (feeds the view-as switcher) |
No INSERT/DELETE (seed/admin API). Self-update is what lets
`changePassword` clear the user's own `must_change_password`.
### organization_members
| Policy | Op | Expression |
|---|---|---|
| `members_self_select` | SELECT | `user_id = auth.uid()` |
| `members_super_admin_select` | SELECT | `current_user_is_super_admin()` |
| `members_co_member_select` | SELECT | `organization_id in (select current_user_org_ids())` |
No write policies. (`members_co_member_select` was rewritten through
the DEFINER helper after the recursion bug — §3.4.)
### teams
| Policy | Op | Expression |
|---|---|---|
| `teams_select` | SELECT | `owner_user_id = auth.uid() OR is_org_owner(org_id) OR is_super_admin_caller()` |
| `teams_modify_admin` | ALL | USING+CHECK `is_org_owner(org_id) OR is_super_admin_caller()` |
| `teams_update_self` | UPDATE | USING+CHECK `owner_user_id = auth.uid()` |
Net effect: team owners may edit their own team's name/base_rate but
cannot create or delete teams; org owners/super-admins can do
everything. NOTE: `teams_select` grants a super admin visibility
WITHOUT org membership — unlike time_entries (§3.7 note). Inconsistent
by accident; harmless in practice (rian is a member).
### team_members (team-scoped since `20260523030001`)
| Policy | Op | Expression |
|---|---|---|
| `team_members_team_select` | SELECT | `exists (select 1 from teams t where t.id = team_members.team_id and (t.owner_user_id = auth.uid() OR is_org_owner(t.org_id) OR is_super_admin_caller()))` |
| `team_members_team_modify` | ALL | USING+CHECK: same expression |
A team's roster is visible/editable ONLY to that team's owner, org
owners, and super admins — NOT arbitrary org members. Rows with NULL
`team_id` are invisible to everyone (EXISTS fails).
### clockify_imports
| Policy | Op | Expression |
|---|---|---|
| `clockify_imports_org_all` | ALL | USING+CHECK `is_org_member(org_id)` |
**Batch metadata is org-visible even when the batch's ENTRIES are
importer-scoped** — names, filenames, row counts leak across importers
by design.
### time_entries (importer-scoped since `20260523000001`)
| Policy | Op | Expression |
|---|---|---|
| `time_entries_scoped_select` | SELECT | `is_org_member(org_id) AND user_can_access_entry(org_id, import_id)` |
| `time_entries_scoped_insert` | INSERT | CHECK `is_org_member(org_id)` (any member can insert; imports run as the uploader) |
| `time_entries_scoped_update` | UPDATE | USING+CHECK `is_org_member(org_id) AND user_can_access_entry(org_id, import_id)` |
| `time_entries_scoped_delete` | DELETE | `is_org_member(org_id) AND user_can_access_entry(org_id, import_id)` |
Effective visibility: super admin (who is also an org member) →
everything; org owner → everything in org; everyone else → only entries
whose import batch they uploaded, and never NULL-import rows. This is
ROW-level only: the money columns (`billout_cost_usd`,
`billout_amount_usd`) ride along on visible rows — a manager sees cost
AND billout for their own imports; hiding billout from managers is
UI-layer only (explicitly noted in `20260524000004`).
### comments (org-wide board)
| Policy | Op | Expression |
|---|---|---|
| `comments_org_select` | SELECT | `is_org_member(org_id)` |
| `comments_org_insert` | INSERT | CHECK `is_org_member(org_id) AND author_id = auth.uid()` |
| `comments_org_update` | UPDATE | USING+CHECK `is_org_member(org_id)` (anyone in org can resolve/unresolve/edit any comment) |
| `comments_author_or_admin_delete` | DELETE | `author_id = auth.uid() OR current_user_is_super_admin()` |
### operators / clients
| Policy | Op | Expression |
|---|---|---|
| `operators_all_org_member` / `clients_all_org_member` | ALL | USING+CHECK `is_org_member(org_id)` |
Rationale in-file: imports run as the uploader and must create entities
on the fly.
### projects — FINAL from `20260609000010`
| Policy | Op | Expression |
|---|---|---|
| `projects_all_org_member` | ALL | USING+CHECK `is_org_member(org_id)` |
History: owner/super-admin-only for BOTH read and write
(`20260523050001`, stated guarantee: "income/margin never leak to a
manager even through a UI bug"). Relaxed 2026-06-09 because the import
resolver, running as manager Adi, couldn't INSERT project rows. The
migration's justification — "Managers don't have direct SQL access, so
this app-layer gate is sufficient" — is **wrong for Supabase**: every
authenticated user can hit PostgREST/supabase-js directly, so a manager
can read `projects.income_usd` / `billout_adjustment_*` and call
`project_summary` (INVOKER) to get `income_usd` per project. Income
confidentiality is currently app-layer-only (`canTransfer` wiring).
**The rebuild must enforce owner-only income/adjustment/billout-rate
visibility server-side** (§8).
### project_rate_overrides — still owner-only
| Policy | Op | Expression |
|---|---|---|
| `project_rate_overrides_select` | SELECT | `is_org_owner(org_id) OR is_super_admin_caller()` |
| `project_rate_overrides_modify` | ALL | USING+CHECK: same |
### pending_imports (staging for the two-step import)
| Policy | Op | Expression |
|---|---|---|
| `pending_imports_select` | SELECT | `uploaded_by = auth.uid() OR is_org_owner(org_id) OR is_super_admin_caller()` |
| `pending_imports_modify` | ALL | USING+CHECK: same |
Rows carry `expires_at default now() + interval '24 hours'` but
**nothing enforces expiry** (no cron/cleanup was ever added; payloads
are full parsed CSV jsonb). Rebuild: real cleanup.
### invoices
| Policy | Op | Expression |
|---|---|---|
| `invoices_org_select` / `_insert` / `_update` / `_delete` | one per op | `is_org_member(org_id)` (+ identical CHECK on insert/update) |
Owner-only create/edit/apply is APP-layer only — at the DB layer any
org member can mutate invoices. Same server-side enforcement note as
projects.
### cc_expense_batches
`cc_batches_org_select/_insert/_update/_delete` — one per op, all
`is_org_member(org_id)` (+CHECK). "Owner-only at the UI layer."
### cc_expense_lines (visibility inherited via batch)
`cc_lines_via_batch_select/_insert/_update/_delete` — all four:
`exists (select 1 from cc_expense_batches b where b.id = batch_id and
is_org_member(b.org_id))` (+ same CHECK on insert/update).
### cc_highlight_keywords
`cc_highlight_org_select` (SELECT), `cc_highlight_org_insert` (INSERT
CHECK), `cc_highlight_org_delete` (DELETE) — all `is_org_member(org_id)`.
**No UPDATE policy** — keywords are add/delete-only (apparently
intentional; confirm before mirroring).
### cc_auto_rules
`cc_auto_org_select/_insert/_update/_delete` — all
`is_org_member(org_id)`.
---
## 5. TypeScript permission functions — `lib/permissions.ts`
All take `eu: EffectiveUser | null` and return `false` for null. The
recurring idiom is `eu.is_super_admin && !eu.is_viewing_as` — note
`is_super_admin` on `EffectiveUser` is the **EFFECTIVE user's flag**
(§6), so while viewing-as a non-admin it is already false; the
`!is_viewing_as` clause additionally strips power if a super-admin ever
views-as another super-admin.
Truth table (SA¬VA = effective user is super admin AND not viewing-as;
role = `eu.org_role`):
| eu state | `canTransfer` | `canManageTeam` | `canManageImports` | `canEnterViewAs` |
|---|---|---|---|---|
| `null` | F | F | F | F |
| SA¬VA (any role) | T | T | T | T |
| viewing-as, target is super admin | role=='owner' | owner\|\|manager | owner\|\|manager | F |
| role = `owner` (not SA¬VA) | T | T | T | F |
| role = `manager` | F | T | T | F |
| role = `member` | F | F | F | F |
| role = `null` (not an org member) | F | F | F | F |
- `canTransfer` — mark entries transferred / un-transfer batches;
ALSO reused as: the `/projects` income+margin column gate (the
projects page passes `canSeeIncome={canTransfer(eu)}` — **there is
no separate `canSeeIncome` function**), and the nav's `ownerOnly`
filter for the `/invoices` and `/tools` links. The rebuild promotes
this page-local wiring to its OWN named capability,
`can_see_income` (02-domain-model §4).
- `canManageTeam` — add/edit/delete team members. **DEAD CODE — never
called by any page or action; its manager=T column above is NOT the
parity bar** (kept in the table for the record only). The EFFECTIVE
team-management rule today is enforced by the `team_members` RLS
(§4): team owner OR org owner OR super admin. The binding R1 parity
rule is that effective rule — a rebuilder ports IT, not this
function's truth table (02-domain-model §4).
- `canManageImports` — upload Clockify CSVs / delete batches.
(Identical truth table to `canManageTeam`, but this one IS live;
they exist separately for semantic clarity.)
- `canEnterViewAs` — show the switcher / accept `enterViewAs`. No
nesting: F while already viewing-as.
`canEditEntry(eu, entry: { invoice_id?: string|null; transferred_at?: string|null })`
— per-entry edit/delete with the invoice lock:
| entry state | rule |
|---|---|
| `entry.invoice_id || entry.transferred_at` truthy (LOCKED) | `canTransfer(eu)` — owners/super-admin only |
| otherwise (unlocked) | `canManageImports(eu)` |
Lock semantics: attaching an entry to an invoice locks it; detaching
unlocks it for everyone with manage-imports. The `transferred_at`
branch is legacy belt-and-suspenders (every transferred row should also
have `invoice_id` post-backfill; honored anyway "until repaired").
**Rebuild parity requirement (02-domain-model §4):** these functions
(+ the canSeeIncome wiring, as `can_see_income`) become named
capabilities in `services/authz/capabilities.py` with IDENTICAL truth
tables in R1; port these tables as test grids before any new semantics
land — with one exception: for team management the parity bar is the
EFFECTIVE rule (team owner OR org owner OR super admin, per the
`team_members` RLS in §4), not dead `canManageTeam`'s table.
---
## 6. Effective-user / view-as resolution chain
Layer order for a request (env var NAMES only:
`NEXT_PUBLIC_SUPABASE_URL`, `NEXT_PUBLIC_SUPABASE_PUBLISHABLE_KEY`,
`SUPABASE_JWT_SECRET`):
### 6.1 Root middleware (`middleware.ts` → `lib/supabase/middleware.ts:updateSession`)
Matcher: `'/((?!_next/static|_next/image|favicon.ico|.*\\.(?:svg|png|jpg|jpeg|gif|webp)$).*)'`.
Per matched request, in order:
1. `PUBLIC_PATHS = {'/login'}` — the only public path
(`/change-password` still requires a session).
2. Cookie-backed `createServerClient`; `auth.getUser()` refreshes the
session token.
3. On `/login`: signed-in → redirect `/`; else serve.
4. Any other path with no user → redirect `/login` (**no `?next=`
return-URL preserved**).
5. If signed in and path ≠ `/change-password`: query
`profiles.must_change_password` (`.single()`, one extra DB query on
EVERY request); truthy → redirect `/change-password`.
6. Middleware is **view-as-unaware**: it always evaluates the REAL
cookie user.
### 6.2 `lib/supabase/server.ts:createClient()` — the JWT swap (ADR #033 v2, 2026-06-09, "true RLS impersonation")
Cookie name `view_as_user_id` (constant duplicated as
`VIEW_AS_COOKIE_NAME` here and `VIEW_AS_COOKIE` in
`lib/effective-user.ts` to avoid an import cycle — keep in sync).
1. No view-as cookie → plain cookie-backed client; RLS = real user;
zero overhead.
2. Cookie present → verify BEFORE minting (forged cookies impersonate
nobody): real client → `auth.getUser()`; no user → return real
client. Then `profiles.is_super_admin` for the real user; not true →
**silently ignore the cookie**, return real client. Cookie ==
own id → no-op.
3. Otherwise `mintJwtForUser(viewAsUserId)` and return a DIRECT
`@supabase/supabase-js` client with
`global.headers.Authorization = 'Bearer '` and
`auth: { persistSession: false, autoRefreshToken: false }` —
**all queries hit the DB as the target; RLS downgrades naturally.**
`lib/supabase/view-as-jwt.ts:mintJwtForUser(userId)`: jose `SignJWT`,
HS256, signed with `SUPABASE_JWT_SECRET` (the Supabase **legacy** JWT
secret; throws `'SUPABASE_JWT_SECRET missing — view-as impersonation
requires the legacy JWT secret in .env'` if absent). Claims:
`sub=userId, role='authenticated', aud='authenticated',
iss=${NEXT_PUBLIC_SUPABASE_URL stripped of trailing '/'}/auth/v1`,
`iat=now`, `exp=5m`. The 5-minute expiry is per-request; the real
impersonation window is the cookie (8 h).
### 6.3 View-as enter/exit (`app/actions/view-as.ts`)
- `enterViewAs(formData)`: `user_id` from the form; empty → `/`. Not
super admin (fresh `profiles.is_super_admin` check) → **silent
no-op** redirect `/`. Else set cookie
`{httpOnly: true, sameSite: 'lax', path: '/', maxAge: 28800}` and
redirect `/`. No validation that the target exists or shares an org
(bogus id just falls through in 6.4). **No audit trail of enter/exit
anywhere.**
- `exitViewAs()`: delete cookie, redirect `/`.
- Switcher UI (`components/view-as-switcher.tsx`) rendered only when
`effective.is_super_admin && !effective.is_viewing_as` and there are
candidates (all `organization_members.user_id`s across all orgs the
admin can see, minus self, hydrated from `profiles`). While active,
the nav shows an amber banner "Viewing as {label}…" with an Exit
form.
### 6.4 `lib/effective-user.ts:getEffectiveUser(supabase, orgId)` → `EffectiveUser | null`
```ts
type OrgRole = 'owner' | 'manager' | 'member'
type EffectiveUser = {
real_user_id: string // from the auth cookie JWT sub
effective_user_id: string // who the app behaves as
email: string; full_name: string | null // effective user's profile
is_super_admin: boolean // the EFFECTIVE user's flag (foot-gun; see §5)
org_role: OrgRole | null // effective user's organization_members.role in orgId
is_viewing_as: boolean
view_as_label: string | null // target.full_name || target.email
}
```
Flow:
1. `readRealUserIdFromCookie()`: because the JWT may be swapped,
`auth.getUser()` would return the impersonated user — so the real
id is recovered by reading the raw supabase-ssr auth cookie
directly: collect cookies matching `/^sb-.*-auth-token(\.[0-9]+)?$/`,
sort by name (`localeCompare`), concatenate values, strip an
optional `base64-` prefix (+ base64→utf8), `JSON.parse` →
`access_token`, `decodeJwt` (jose, **no signature verification**) →
`sub`. Any failure → null → treated as signed out. (Fragile:
re-implements supabase-ssr internals.)
2. Read `view_as_user_id`. If set and ≠ real id: load the target's
profile **with the (possibly impersonated) client** — trust chain:
`createClient()` only swapped the JWT if the real user is
super-admin; profiles RLS allows self-read (impersonated case) and
super-admin-reads-all (unswapped case). Target loads → return
`{is_viewing_as: true, effective = target, org_role =
loadOrgRole(orgId, target.id)}`. Target fails to load → fall
through.
3. Real-user path: own profile + org role,
`effective_user_id === real_user_id`, `is_viewing_as: false`.
Org resolution feeding this: `lib/org.ts:getAppOrg` —
`organizations` by hard-coded `APP_ORG_SLUG = 'bowden-works'`;
`FALLBACK_BASE_RATE = 14` when `default_hourly_rate_usd` unset.
### 6.5 App-layer view-as mirrors (defense-in-depth since ADR #033 v2)
Written pre-v2 when RLS still saw the super-admin; their headers still
say so (stale — see Contradictions). Post-v2 the JWT swap makes RLS
authoritative and these are belt-and-suspenders. They still run.
- `getViewAsImportScope(supabase, orgId, eu)` → `string[] | null`:
`null` (= no extra scoping) when view-as is off OR the effective user
is super-admin/org-owner; otherwise the `clockify_imports.id`s with
`imported_by = eu.effective_user_id` in the org, or
`['00000000-0000-0000-0000-000000000000']` (`NO_MATCH_UUID` sentinel,
matches nothing) when the target has none. Callers apply
`.in('import_id', ids)` on time_entries / `.in('id', ids)` on
clockify_imports.
- `filterTeamsForViewAs(teams, eu)`: not viewing-as → all; effective
super-admin or owner → all; else only
`t.owner_user_id === eu.effective_user_id`.
- `lib/view-as-guards.ts` — mutation guards; each returns an error
STRING to deny or `null` to allow; shared denial message:
`"That row isn't visible to the user you're viewing as. Exit view-as
to make this change."`
- `guardEntry(supabase, eu, orgId, entryId)`: scope null → allow;
entry not found → `'Entry not found.'`; `import_id` NULL or ∉
scope → deny. (NULL-import entries are never editable under
view-as by non-owners — matches §3.7.)
- `guardEntryIds(…, entryIds[])`: empty → allow; scope null → allow;
loads `id, import_id` for all ids in org; count mismatch →
`'Some selected entries were not found.'`; **ANY id out of scope
denies the whole batch** (no silent filtering — the caller asserts
a specific count).
- `guardImport(…, importId)`: scope null → allow; else
`scope.includes(importId)`.
- `guardTeam(…, teamId)`: not viewing-as → allow; effective
SA/owner → allow; team not found → `'Team not found.'`;
`owner_user_id !== effective_user_id` → deny.
- `guardTeamMember(…, memberId)`: same shape via embedded join
`team_members.select('team:teams(owner_user_id)')`; missing →
`'Team member not found.'`.
### 6.6 Related flows with view-as interactions
- `changePassword` (server action): **refuses if the view-as cookie
exists at all** (error `'Exit view-as before changing your
password.'`) — because under a swapped client
`auth.updateUser({password})` would change the TARGET's password.
Triggers on cookie presence alone, even for non-super-admins whose
cookie would be ignored. Then: length ≥ 8, `password === confirm`,
`auth.updateUser`, `profiles.update({must_change_password: false})`
for self, redirect `/`.
- Sign-out (`POST /auth/signout`): `auth.signOut()` then 303 →
`/login`. **Does NOT delete the view-as cookie** — a stale 8 h cookie
survives sign-out and blocks password changes / re-activates if the
next sign-in is a super admin.
- `login` server action: `signInWithPassword`; failure →
`/login?error=`; success → `/`. No rate
limiting/captcha/MFA in-app.
---
## 7. Magic values (embedded in migrations/code — needed by migration + tests)
- Org slug: `'bowden-works'` (backfills + `APP_ORG_SLUG`).
- Seed emails: `info@adipramono.com` (Adi), `rian@rian.ca` (Rian,
rate_proportion 1.0), `matmoncoo@gmail.com` (Matthew Monti-Cooper,
proportion 0.7, Rian's team), `gary@rian.ca` (Gary — an AI;
`cost_rate_usd` forced to 0).
- Deprecated rate seeds: `teams.base_rate_usd` default 50;
org `default_hourly_rate_usd` 14.00; `FALLBACK_BASE_RATE = 14`.
- Backfill names: batch `'Initial 2026 migration'` (filename
`'Time Sheet _ Time Log _ Hours _ Tingang.id _ Adi.xlsx'`); per-org
invoice `'Legacy transfers'` (matched case-insensitively,
status `'paid'`).
- `clockify_imports.source` free text: `'backfill' | 'clockify' |
'toggl'`; `pending_imports.source` HAS a CHECK:
`('clockify','toggl')`.
- `' : '` (space-colon-space) — legacy `"{client} : {project}"` split
token, first occurrence wins (`20260521230001`).
- Placeholder labels (§2.5 only): `'(no operator)'`, `'(no client)'`,
`'(no project)'`.
- Sentinel UUID `'00000000-0000-0000-0000-000000000000'` — used BOTH in
the override uniqueness index (§3.8) and as `NO_MATCH_UUID` (§6.5).
- Invoice statuses CHECK: `'open' | 'sent' | 'paid'`; `invoice_date`
is a back-datable `date` (app default: last day of previous month);
`manual_total_usd` NULL = use sum of stamps; unique
`(org_id, lower(name))`.
- View-as cookie: `view_as_user_id`, httpOnly, lax, path `/`,
maxAge 28800 s.
- Auth cookie chunk regex: `/^sb-.*-auth-token(\.[0-9]+)?$/`;
value prefix `base64-`.
Indexes tuned for these RPCs (port the access patterns, not necessarily
the DDL): `idx_time_entries_org_start (org_id, start_at desc)`;
`idx_time_entries_org_project_month (org_id, project, start_at)`;
`idx_time_entries_source_email (org_id, source_user_email)`; partials
on `(org_id, transferred_at) WHERE transferred_at IS NULL`,
`(org_id, project, start_at) WHERE billout_cost_usd IS NOT NULL`;
`time_entries_invoice_id_idx (invoice_id) WHERE invoice_id IS NOT NULL`;
uniques: `operators (org_id, lower(name))`,
`clients (operator_id, lower(name))`, `projects (client_id,
lower(name))`, `team_members (team_id, email)`,
`teams (org_id, owner_user_id)`.
---
## 8. Rebuild deltas (how the new system replaces all of this)
Per 00-vision-and-scope.md, 02-domain-model.md, 04-architecture.md,
07-judgment-calls.md:
1. **RLS is deleted wholesale** (02 §7): every policy in §4 becomes the
`services/authz/` engine — `can(actor, action, resource) → Decision`
(with reason chain) + `scope(actor, action, Model) → SQLAlchemy
filter`, one choke point, default-deny middleware (04). The §3.7
importer-scoping rule and the §4 visibility matrix are the R1
`scope()` semantics for entries/imports; §5's truth tables are the
R1 capability grids, ported as tests FIRST (team management per the
effective RLS rule, not dead `canManageTeam` — see §5).
2. **Every read RPC becomes a `services/reporting/` function** built on
`scope()`-filtered queries, semantics pinned by THIS doc's §1–§2 and
equivalence tests against the live app (04 "Reporting"). SQL views
only if profiling demands. The diff harness (00 R0) is the oracle.
3. **Fix, don't port, the known divergences**: one status predicate
and one hours definition everywhere (kills §2.4's staleness); drop
the vestigial `billout_seconds == source_seconds` duplicate; drop
the 11-param options overload; honest restamp counts + an explicit
NULL-vs-0 no-rate semantic (§2.6/2.7); expected-count preconditions
and undo on all bulk mutations (07 #11).
4. **Aggregation keys on FKs, not text triples**: the fragile
`lower(name)` entity-join chain (§2.3/§2.5) is dropped; entries
carry `project_id`/party FKs and display names resolve through FKs
(02 §3). The text triple itself survives as immutable
`raw_operator/raw_client/raw_project` (import payload — sole
identity for unassigned rows; never read for a resolved row's
display/grouping/joins). The `(no …)` placeholder behavior moves
to the presentation layer.
5. **Money confidentiality becomes server-side truth** (fixes the
`20260609000010` regression): income, billout
rates/amounts/adjustments, rate overrides, invoice mutations, and
entry lock/detach are owner-capabilities enforced by `can()`/DTO
shaping — never UI-only (§4 projects/invoices notes).
6. **View-as returns to app-layer impersonation** (00 phase-outs,
07 #7): no JWT minting, no `SUPABASE_JWT_SECRET`, no cookie-decode
hack (§6.2/6.4 machinery retires). `resolve_actor` returns the
target's context with `impersonated_by` set; correct BY CONSTRUCTION
because there is no DB-level identity anymore. Both identities are
audit-logged (the current system has NO impersonation audit);
credential-ish actions stay blocked while impersonating; carry BOTH
`real_*` and `effective_*` flags explicitly to kill the §5 foot-gun.
7. **Auth replaced by id-auth Pattern B** (00, 04; kit shipped
2026-07-07): "Sign in with BW" (OAuth2+PKCE, app session keyed on
the BW username), no password store, no `must_change_password`
flow, no per-request profiles query (§6.1 middleware retires).
8. **Toggl plumbing dies** (00 phase-outs, 07 #8): `transferred_at` /
`converted_user` / `converted_duration_seconds` / `rate_proportion`
/ `teams.base_rate_usd` / org default rate are not ported;
`'applied to invoice'` is the only lock/status language; §2.8's
converted-column stamping is not reimplemented.
9. **Rates become engagement-scoped compensation_terms** (02 §2,
07 #12): `team_members.cost_rate_usd` → the worker's subcontract
terms; `billout_rate_usd` → billing-side terms
(`compensation_terms.project_id` for project-specific terms).
`project_rate_overrides` (absolute XOR pct, §3.8 precedence) does
NOT flatten into terms — R1 carries it near-verbatim as the
dedicated `rate_overrides` table, keeping the resolution-time
semantics and specificity precedence; R4's money engine decides
whether terms subsume it (02 §2, 07 #25). The §3.8 precedence
subtlety (user-specific pct with NULL fallback beats project-wide
absolute) should be resolved deliberately, not blindly ported.
10. **Structural cleanups**: `pending_imports` expiry actually enforced
(§4); one super-admin helper instead of two (§3.3/3.6); teams
dissolve into engagement scoping (02 §7); invoices gain lines and
snapshot-at-attach (07 #9).
---
## 9. Contradictions found between sources (for the record)
1. **`restamp_billout_for_entry_ids` doc-comment vs its own SQL**
(§2.7) — comment promises changed-rows count and NULLing of
memberless rows; code counts all matched rows and skips memberless
rows entirely. Code wins.
2. **`20260609000010` migration comment vs Supabase reality** (§4
projects) — "Managers don't have direct SQL access" is false under
PostgREST; income is readable by any org member at the API layer.
3. **`lib/view-as-guards.ts` + `getViewAsImportScope` headers vs
`lib/supabase/server.ts`** (§6.5) — headers claim queries still run
as the super-admin during view-as; since ADR #033 v2 the JWT is
swapped and RLS runs as the target. The guards are redundant
defense-in-depth, not the enforcement layer.
4. **`listVisibleTeamMembers` doc vs query** (`lib/teams.ts`) — comment
says "sorted by team, then is_active desc, then display_name"; the
query orders by `display_name` ascending only.
5. **Extraction file `rpcs-final.md` §2.5 note** — it says
summary_by_project joins entities via "the same lower(name) chain as
project_summary"; strictly, summary_by_project joins on the
**coalesced placeholder labels** while project_summary joins on raw
(possibly NULL) text. Same outcome for named groups; different
mechanics for NULL groups. This doc (§2.3/§2.5) states the exact
behavior.