-- V1-1.2 Clients (M2): the clients of a workspace, written only through RPCs, -- read through RLS, searched through a POST RPC. Contract: -- docs/development/V1-1_OWNERS_ANIMALS.md §5 (text rules), §5.1 (fields), -- §8 (schema), §9 (helpers, RLS, RPCs, failures), §10 (limits), §13.2 (N2, -- N3, N6, N7, N9). -- -- Invariants pinned by supabase/tests/013_clients.sql and the sandbox suite: -- * a client belongs to one workspace; only a member with a write role of -- an active, confirmed account writes it, through the four RPCs; every -- foreign or unknown id is answered with the same 42501 `not_found`; -- * a create is idempotent by (workspace, create_request_id); a replay -- with other content is refused (`request_conflict`), never applied; -- * an update needs the current revision; an archived client is read-only -- until restored; -- * writes are capped per workspace (20 000 clients; 1 000 audited writes -- per UTC hour, 5 000 per UTC day) under locks, so concurrent calls -- cannot pass a cap and a refused or replayed call consumes nothing; -- * audit rows carry ids only, and platform staff do not read them (N9). -- --------------------------------------------------------------------------- -- Text folding for search keys -- --------------------------------------------------------------------------- create extension if not exists unaccent with schema extensions; -- Accents removed with the explicit dictionary, then ASCII lower case -- (collation "C"), so PGlite and the real image fold identically. Letters -- outside ASCII that unaccent keeps (Cyrillic, Greek) keep their case. -- Declared immutable for the generated columns: the dictionary is fixed by -- the extension version (contract §8). create or replace function private.fold_text(p_value text) returns text language sql immutable parallel safe set search_path = '' as $$ select lower(extensions.unaccent('extensions.unaccent'::regdictionary, p_value) collate "C"); $$; comment on function private.fold_text(text) is 'Search key folding: unaccent (explicit dictionary) then ASCII lower case. Used by generated columns and by search_clients.'; -- The text rule of contract §5: 1 to p_max characters (code points), no -- leading or trailing space, no control character of the explicit ranges. -- Null passes (an absent value is null, never an empty string). create or replace function private.is_clean_text(p_value text, p_max integer) returns boolean language sql immutable parallel safe set search_path = '' as $$ select p_value is null or (char_length(p_value) between 1 and p_max and p_value = btrim(p_value) and p_value !~ '[\u0001-\u001f\u007f-\u009f\u2028\u2029]'); $$; comment on function private.is_clean_text(text, integer) is 'Contract §5 text rule for CHECK constraints: length 1..p_max in code points, trimmed, no control character.'; -- --------------------------------------------------------------------------- -- clients -- --------------------------------------------------------------------------- create type public.client_kind as enum ('person', 'organization'); create table public.clients ( id uuid primary key default gen_random_uuid(), -- restrict: an account whose workspace holds clients cannot be deleted (D9a). workspace_id uuid not null references public.workspaces (id) on delete restrict, kind public.client_kind not null, display_name text not null constraint clients_display_name_text check (private.is_clean_text(display_name, 120)), email text constraint clients_email_format check ( private.is_clean_text(email, 254) and email = lower(email) and email ~ '^[^@\s]+@[^@\s]+\.[^@\s]+$' ), phone text constraint clients_phone_e164 check (phone ~ '^\+[1-9][0-9]{6,14}$'), address_line1 text constraint clients_address_line1_text check (private.is_clean_text(address_line1, 120)), address_line2 text constraint clients_address_line2_text check (private.is_clean_text(address_line2, 120)), postal_code text constraint clients_postal_code_text check (private.is_clean_text(postal_code, 16)), city text constraint clients_city_text check (private.is_clean_text(city, 80)), country text constraint clients_country_code check (country ~ '^[A-Z]{2}$'), name_key text collate "C" generated always as (private.fold_text(display_name)) stored, search_key text collate "C" generated always as ( private.fold_text(display_name || ' ' || coalesce(city, '') || ' ' || coalesce(email, '')) ) stored, phone_digits text collate "C" generated always as (ltrim(phone, '+')) stored, -- Idempotency: the form sends one uuid per create attempt. The digest of the -- creation content tells a replay from a divergent reuse; never readable -- through the API. create_request_id uuid not null, create_request_digest bytea not null, revision integer not null default 1 constraint clients_revision_positive check (revision >= 1), archived_at timestamptz, created_at timestamptz not null default now(), updated_at timestamptz not null default now(), constraint clients_workspace_id_unique unique (workspace_id, id), constraint clients_create_request_unique unique (workspace_id, create_request_id) ); comment on table public.clients is 'Clients of a workspace (person or organization). Writes only through create_client, update_client, set_client_archived; reads through RLS and search_clients.'; comment on column public.clients.display_name is 'The single name reference (N2): display, search, sorting, documents.'; -- Workspace filter, name order, keyset pagination and the search scan. create index clients_workspace_name_idx on public.clients (workspace_id, name_key, id); create trigger clients_set_updated_at before update on public.clients for each row execute function private.set_updated_at(); -- --------------------------------------------------------------------------- -- Write budget per workspace (N6, owner decision on the counter) -- --------------------------------------------------------------------------- -- One row per workspace, fixed UTC windows: the hour starting at hh:00:00 UTC -- and the day starting at 00:00:00 UTC. A window that has passed restarts at -- 1 on the next write. The row lock of the upsert serialises the writes of -- one workspace only; it is the last lock a write RPC takes, so no lock -- cycle can form. The counts are rolled back with any refused write. create table private.workspace_write_budgets ( workspace_id uuid primary key references public.workspaces (id) on delete cascade, hour_start timestamptz not null, hour_count integer not null, day_start timestamptz not null, day_count integer not null ); comment on table private.workspace_write_budgets is 'Audited writes per workspace in the current UTC hour and day (contract §10). Written only by private.consume_write_budget.'; create or replace function private.consume_write_budget(p_workspace_id uuid) returns void language plpgsql security definer set search_path = '' as $$ declare v_hour timestamptz := date_trunc('hour', now(), 'UTC'); v_day timestamptz := date_trunc('day', now(), 'UTC'); v_hour_count integer; v_day_count integer; begin insert into private.workspace_write_budgets as b (workspace_id, hour_start, hour_count, day_start, day_count) values (p_workspace_id, v_hour, 1, v_day, 1) on conflict (workspace_id) do update set hour_count = case when b.hour_start = excluded.hour_start then b.hour_count + 1 else 1 end, hour_start = excluded.hour_start, day_count = case when b.day_start = excluded.day_start then b.day_count + 1 else 1 end, day_start = excluded.day_start returning b.hour_count, b.day_count into v_hour_count, v_day_count; -- Raising undoes the increment with the rest of the refused write. if v_hour_count > 1000 or v_day_count > 5000 then raise exception 'rate_limited' using errcode = 'P0001'; end if; end; $$; comment on function private.consume_write_budget(uuid) is 'Counts one audited write of the workspace in its UTC hour and day; raises rate_limited past 1 000 / 5 000. Call last, after the entity locks.'; -- --------------------------------------------------------------------------- -- Write RPCs (contract §9.3). Each one: authorise and lock in one statement -- (no row = 42501 not_found, the same for a foreign and an unknown id), then -- archived, then revision, then rules; the budget last; one audit row with -- ids only; returns ids and revision. -- --------------------------------------------------------------------------- create or replace function public.create_client( workspace_id uuid, create_request_id uuid, kind text, display_name text, email text default null, phone text default null, address_line1 text default null, address_line2 text default null, postal_code text default null, city text default null, country text default null ) returns jsonb language plpgsql volatile security definer set search_path = '' as $$ #variable_conflict use_column declare v_kind public.client_kind; v_email text := lower(create_client.email); v_digest bytea; v_existing_id uuid; v_existing_revision integer; v_existing_digest bytea; v_id uuid; begin -- Serialises the creates of this workspace (replay check, cap, insert). perform 1 from public.workspaces w where w.id = create_client.workspace_id and w.id in (select private.writable_workspace_ids()) for no key update; if not found then raise exception 'not_found' using errcode = '42501'; end if; begin v_kind := create_client.kind::public.client_kind; exception when invalid_text_representation then raise exception 'invalid_input' using errcode = 'P0001'; end; v_digest := sha256(convert_to(jsonb_build_array( v_kind, create_client.display_name, v_email, create_client.phone, create_client.address_line1, create_client.address_line2, create_client.postal_code, create_client.city, create_client.country )::text, 'UTF8')); select c.id, c.revision, c.create_request_digest into v_existing_id, v_existing_revision, v_existing_digest from public.clients c where c.workspace_id = create_client.workspace_id and c.create_request_id = create_client.create_request_id; if found then if v_existing_digest <> v_digest then raise exception 'request_conflict' using errcode = 'P0001'; end if; return jsonb_build_object('id', v_existing_id, 'revision', v_existing_revision); end if; if (select count(*) from public.clients c where c.workspace_id = create_client.workspace_id) >= 20000 then raise exception 'limit_reached' using errcode = 'P0001'; end if; perform private.consume_write_budget(create_client.workspace_id); begin insert into public.clients ( workspace_id, kind, display_name, email, phone, address_line1, address_line2, postal_code, city, country, create_request_id, create_request_digest ) values ( create_client.workspace_id, v_kind, create_client.display_name, v_email, create_client.phone, create_client.address_line1, create_client.address_line2, create_client.postal_code, create_client.city, create_client.country, create_client.create_request_id, v_digest ) returning id into v_id; exception when check_violation or not_null_violation or string_data_right_truncation or invalid_text_representation then raise exception 'invalid_input' using errcode = 'P0001'; end; perform private.log_event( auth.uid(), 'client.created', 'client', v_id, null, null, jsonb_build_object('workspace_id', create_client.workspace_id) ); return jsonb_build_object('id', v_id, 'revision', 1); end; $$; create or replace function public.update_client( client_id uuid, expected_revision integer, kind text, display_name text, email text, phone text, address_line1 text, address_line2 text, postal_code text, city text, country text ) returns jsonb language plpgsql volatile security definer set search_path = '' as $$ #variable_conflict use_column declare v_row public.clients; v_kind public.client_kind; v_email text := lower(update_client.email); begin select c.* into v_row from public.clients c where c.id = update_client.client_id and c.workspace_id in (select private.writable_workspace_ids()) for update; if not found then raise exception 'not_found' using errcode = '42501'; end if; if v_row.archived_at is not null then raise exception 'archived' using errcode = 'P0001'; end if; if update_client.expected_revision is distinct from v_row.revision then raise exception 'conflict' using errcode = 'P0001'; end if; begin v_kind := update_client.kind::public.client_kind; exception when invalid_text_representation then raise exception 'invalid_input' using errcode = 'P0001'; end; -- No change: nothing written, nothing counted. if (v_kind, update_client.display_name, v_email, update_client.phone, update_client.address_line1, update_client.address_line2, update_client.postal_code, update_client.city, update_client.country) is not distinct from (v_row.kind, v_row.display_name, v_row.email, v_row.phone, v_row.address_line1, v_row.address_line2, v_row.postal_code, v_row.city, v_row.country) then return jsonb_build_object('revision', v_row.revision); end if; perform private.consume_write_budget(v_row.workspace_id); begin update public.clients c set kind = v_kind, display_name = update_client.display_name, email = v_email, phone = update_client.phone, address_line1 = update_client.address_line1, address_line2 = update_client.address_line2, postal_code = update_client.postal_code, city = update_client.city, country = update_client.country, revision = c.revision + 1 where c.id = v_row.id; exception when check_violation or not_null_violation or string_data_right_truncation or invalid_text_representation then raise exception 'invalid_input' using errcode = 'P0001'; end; perform private.log_event( auth.uid(), 'client.updated', 'client', v_row.id, null, null, jsonb_build_object('workspace_id', v_row.workspace_id) ); return jsonb_build_object('revision', v_row.revision + 1); end; $$; create or replace function public.set_client_archived( client_id uuid, archived boolean ) returns jsonb language plpgsql volatile security definer set search_path = '' as $$ #variable_conflict use_column declare v_row public.clients; begin select c.* into v_row from public.clients c where c.id = set_client_archived.client_id and c.workspace_id in (select private.writable_workspace_ids()) for update; if not found then raise exception 'not_found' using errcode = '42501'; end if; if set_client_archived.archived is null then raise exception 'invalid_input' using errcode = 'P0001'; end if; -- Already in that state: nothing written, nothing counted. if (v_row.archived_at is not null) = set_client_archived.archived then return jsonb_build_object('revision', v_row.revision); end if; perform private.consume_write_budget(v_row.workspace_id); update public.clients c set archived_at = case when set_client_archived.archived then now() end, revision = c.revision + 1 where c.id = v_row.id; perform private.log_event( auth.uid(), case when set_client_archived.archived then 'client.archived' else 'client.restored' end, 'client', v_row.id, null, null, jsonb_build_object('workspace_id', v_row.workspace_id) ); return jsonb_build_object('revision', v_row.revision + 1); end; $$; -- --------------------------------------------------------------------------- -- Search (contract §9.3): security invoker, so RLS limits it to the caller's -- workspaces; a foreign workspace id returns no row. Called with POST, so the -- term and the cursor stay out of URLs. -- --------------------------------------------------------------------------- create or replace function public.search_clients( workspace_id uuid, query text default '', archived boolean default false, page_size integer default 25, after_name_key text default null, after_id uuid default null ) returns table ( id uuid, kind public.client_kind, display_name text, city text, phone text, email text, archived_at timestamptz, name_key text ) language plpgsql stable security invoker set search_path = '' as $$ #variable_conflict use_column declare v_term text := btrim(coalesce(search_clients.query, '')); v_pattern text; v_digits text; begin if char_length(v_term) > 100 or search_clients.archived is null or search_clients.page_size is null or search_clients.page_size not between 1 and 100 or (search_clients.after_name_key is null) <> (search_clients.after_id is null) then raise exception 'invalid_input' using errcode = 'P0001'; end if; -- Folded term inside search_key; LIKE wildcards escaped, backslash first. v_pattern := '%' || replace(replace(replace(private.fold_text(v_term), '\', '\\'), '%', '\%'), '_', '\_') || '%'; -- A phone-like term (digits, spaces, dots, hyphens, +; at least 4 digits) -- is also looked for in phone_digits, without one leading 0. if v_term ~ '^[0-9 .+-]+$' and char_length(regexp_replace(v_term, '[^0-9]', '', 'g')) >= 4 then v_digits := regexp_replace(regexp_replace(v_term, '[^0-9]', '', 'g'), '^0', ''); end if; return query select c.id, c.kind, c.display_name, c.city, c.phone, c.email, c.archived_at, c.name_key from public.clients c where c.workspace_id = search_clients.workspace_id and (c.archived_at is not null) = search_clients.archived and (v_term = '' or c.search_key like v_pattern or (v_digits is not null and c.phone_digits like '%' || v_digits || '%')) and (search_clients.after_name_key is null or (c.name_key, c.id) > (search_clients.after_name_key collate "C", search_clients.after_id)) order by c.name_key, c.id limit search_clients.page_size; end; $$; comment on function public.create_client(uuid, uuid, text, text, text, text, text, text, text, text, text) is 'Creates a client in a writable workspace; idempotent by create_request_id (divergent replay: request_conflict). Returns {id, revision}.'; comment on function public.update_client(uuid, integer, text, text, text, text, text, text, text, text, text) is 'Replaces the nine fields of an active client at expected_revision. Returns {revision}.'; comment on function public.set_client_archived(uuid, boolean) is 'Archives or restores a client (same state: no write). Returns {revision}.'; comment on function public.search_clients(uuid, text, boolean, integer, text, uuid) is 'Folded search (name, city, email; phone digits) in one workspace, keyset pages on (name_key, id), 25 by default, 100 at most.'; -- --------------------------------------------------------------------------- -- Audit (N9): platform owners and admins keep reading platform events, not -- the activity of private records. -- --------------------------------------------------------------------------- alter policy audit_logs_select_admin on audit.audit_logs using ( private.is_platform_admin((select auth.uid())) and split_part(action, '.', 1) not in ('client', 'animal', 'animal_client_relation') ); -- --------------------------------------------------------------------------- -- Privileges + RLS (N7: nothing for anon or service_role) -- --------------------------------------------------------------------------- revoke all on public.clients from public, anon, authenticated, service_role; grant select ( id, workspace_id, kind, display_name, email, phone, address_line1, address_line2, postal_code, city, country, name_key, search_key, phone_digits, create_request_id, revision, archived_at, created_at, updated_at ) on public.clients to authenticated; alter table public.clients enable row level security; create policy clients_select_member on public.clients for select to authenticated using (workspace_id in (select private.caller_workspace_ids())); revoke all on private.workspace_write_budgets from public, anon, authenticated, service_role; revoke all on function private.fold_text(text) from public, anon, authenticated, service_role; revoke all on function private.is_clean_text(text, integer) from public, anon, authenticated, service_role; revoke all on function private.consume_write_budget(uuid) from public, anon, authenticated, service_role; revoke all on function public.create_client(uuid, uuid, text, text, text, text, text, text, text, text, text) from public, anon, authenticated, service_role; revoke all on function public.update_client(uuid, integer, text, text, text, text, text, text, text, text, text) from public, anon, authenticated, service_role; revoke all on function public.set_client_archived(uuid, boolean) from public, anon, authenticated, service_role; revoke all on function public.search_clients(uuid, text, boolean, integer, text, uuid) from public, anon, authenticated, service_role; grant execute on function public.create_client(uuid, uuid, text, text, text, text, text, text, text, text, text) to authenticated; grant execute on function public.update_client(uuid, integer, text, text, text, text, text, text, text, text, text) to authenticated; grant execute on function public.set_client_archived(uuid, boolean) to authenticated; grant execute on function public.search_clients(uuid, text, boolean, integer, text, uuid) to authenticated; -- The invoker search folds its term as the caller. grant execute on function private.fold_text(text) to authenticated;