V1-1.2 (contract V1-1_OWNERS_ANIMALS.md §5, §9, §10). public.clients (person or organization, display name only, optional contact, folded search keys), create/update/set_archived as definer RPCs (identical not_found, idempotent create with divergent-replay refusal, revisions, archive read-only), search_clients invoker with keyset pages and phone search, write budget per workspace in UTC hour/day windows under a row lock, 20 000-client cap, ids-only audit hidden from platform staff (N9), nothing for anon or service_role. pgTAP 013 (130), field-limits parity test, sandbox suite (concurrency, budget races, 20 000-client search), types regenerated.
515 lines
22 KiB
PL/PgSQL
515 lines
22 KiB
PL/PgSQL
-- 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;
|