Species rows (8 + other), animals with the D6b birth model, relations with composite workspace keys, current/ended/voided states and a partial unique index; definer RPCs for animals and relations (one lock order, shared write budget, caps), invoker reads (search_animals, list_client_animals, client_archive_preview) and search_clients with current_animal_count. pgTAP 014 (254), parity test, sandbox suite, types from the isolated stack.
1167 lines
50 KiB
PL/PgSQL
1167 lines
50 KiB
PL/PgSQL
-- V1-1.4 Animals and relations (M3): the animals of a workspace and their
|
|
-- relations with its clients (owner, keeper, contact), written only through
|
|
-- RPCs, read through RLS and POST RPCs. Contract:
|
|
-- docs/development/V1-1_OWNERS_ANIMALS.md §5 (text rules), §5.2 (animal),
|
|
-- §5.3 (relation), §5.4 (species), §7.1 (archive), §8 (schema), §9 (helpers,
|
|
-- RLS, RPCs, failures), §10 (limits), §13.2 (N1, N4, N5, N6, N8, N9).
|
|
--
|
|
-- Invariants pinned by supabase/tests/014_animals_relations.sql and the
|
|
-- sandbox suite:
|
|
-- * an animal belongs to one workspace; a relation joins an animal and a
|
|
-- client of the same workspace, enforced by composite foreign keys
|
|
-- whatever role writes;
|
|
-- * a relation is current (ended_at and voided_at null), ended (ended_at
|
|
-- set, ended_on when the date is known) or voided (entered by mistake,
|
|
-- voided_at set, frozen); at most one current relation per (animal,
|
|
-- client, role), enforced by a partial unique index;
|
|
-- * only a member with a write role of an active, confirmed account writes,
|
|
-- through the RPCs below; every foreign or unknown id gets the same 42501
|
|
-- `not_found`; creates are idempotent by (workspace, create_request_id),
|
|
-- a divergent replay is refused (`request_conflict`);
|
|
-- * caps (20 000 animals and 100 000 relations per workspace, 20 current and
|
|
-- 200 relations per animal) and the write budget shared with the clients
|
|
-- are checked under locks taken in one order: workspace, animal, relation,
|
|
-- client, budget last;
|
|
-- * audit rows carry ids only, hidden from platform staff (M2's N9 filter
|
|
-- already names the animal and relation actions).
|
|
|
|
-- ---------------------------------------------------------------------------
|
|
-- Species (contract §5.4, N1): the nine species of the contract and « Autre ».
|
|
-- Rows, not an enum; the existing `equine` row is kept unchanged. A species
|
|
-- row does not mean an anatomical sheet exists.
|
|
-- ---------------------------------------------------------------------------
|
|
insert into public.species (id, name_fr, name_en, sort_order) values
|
|
('asinine', 'Âne', 'Donkey', 20),
|
|
('canine', 'Chien', 'Dog', 30),
|
|
('feline', 'Chat', 'Cat', 40),
|
|
('bovine', 'Bovin', 'Cattle', 50),
|
|
('caprine', 'Chèvre', 'Goat', 60),
|
|
('ovine', 'Mouton', 'Sheep', 70),
|
|
('porcine', 'Porc', 'Pig', 80),
|
|
('leporine', 'Lapin', 'Rabbit', 90),
|
|
('other', 'Autre', 'Other', 1000)
|
|
on conflict (id) do nothing;
|
|
|
|
comment on table public.species is
|
|
'Species of animals and of anatomical sheets. Reference data seeded by migrations; no API write path. A species row does not mean a sheet exists.';
|
|
|
|
-- ---------------------------------------------------------------------------
|
|
-- Dates and years sent as text (contract §9.3): parsed in the body, so a
|
|
-- malformed value is answered invalid_input without being echoed; "not in the
|
|
-- future" allows up to tomorrow's UTC date, so today in any time zone passes.
|
|
-- ---------------------------------------------------------------------------
|
|
create or replace function private.utc_tomorrow(p_now timestamptz)
|
|
returns date
|
|
language sql
|
|
stable
|
|
parallel safe
|
|
set search_path = ''
|
|
as $$
|
|
select (p_now at time zone 'UTC')::date + 1;
|
|
$$;
|
|
|
|
comment on function private.utc_tomorrow(timestamptz) is
|
|
'Latest date a record may carry when written at p_now: the UTC date of tomorrow (contract §9.3).';
|
|
|
|
create or replace function private.parse_record_date(p_value text)
|
|
returns date
|
|
language plpgsql
|
|
stable
|
|
set search_path = ''
|
|
as $$
|
|
declare
|
|
v_date date;
|
|
begin
|
|
if p_value is null then
|
|
return null;
|
|
end if;
|
|
if p_value !~ '^[0-9]{4}-[0-9]{2}-[0-9]{2}$' then
|
|
raise exception 'invalid_input' using errcode = 'P0001';
|
|
end if;
|
|
begin
|
|
v_date := p_value::date;
|
|
exception when data_exception then
|
|
raise exception 'invalid_input' using errcode = 'P0001';
|
|
end;
|
|
if v_date > private.utc_tomorrow(now()) then
|
|
raise exception 'invalid_input' using errcode = 'P0001';
|
|
end if;
|
|
return v_date;
|
|
end;
|
|
$$;
|
|
|
|
comment on function private.parse_record_date(text) is
|
|
'YYYY-MM-DD to a date no later than tomorrow UTC, else invalid_input; null stays null. The 1900 floor is a CHECK.';
|
|
|
|
create or replace function private.parse_birth_year(p_value text)
|
|
returns smallint
|
|
language plpgsql
|
|
stable
|
|
set search_path = ''
|
|
as $$
|
|
begin
|
|
if p_value is null then
|
|
return null;
|
|
end if;
|
|
-- Two statements: SQL does not promise to test the pattern before the cast.
|
|
if p_value !~ '^[0-9]{4}$' then
|
|
raise exception 'invalid_input' using errcode = 'P0001';
|
|
end if;
|
|
if p_value::integer > extract(year from private.utc_tomorrow(now()))::integer then
|
|
raise exception 'invalid_input' using errcode = 'P0001';
|
|
end if;
|
|
return p_value::smallint;
|
|
end;
|
|
$$;
|
|
|
|
comment on function private.parse_birth_year(text) is
|
|
'Four digits to a year no later than the year of tomorrow UTC, else invalid_input; null stays null. The 1900 floor is a CHECK.';
|
|
|
|
-- ---------------------------------------------------------------------------
|
|
-- animals (contract §5.2: D6, D6b, N8)
|
|
-- ---------------------------------------------------------------------------
|
|
create type public.animal_sex as enum ('male', 'female', 'unknown');
|
|
|
|
create table public.animals (
|
|
id uuid primary key default gen_random_uuid(),
|
|
-- restrict: an account whose workspace holds animals cannot be deleted (D9a).
|
|
workspace_id uuid not null references public.workspaces (id) on delete restrict,
|
|
name text not null
|
|
constraint animals_name_text check (private.is_clean_text(name, 80)),
|
|
species_id text not null references public.species (id) on delete restrict,
|
|
species_detail text
|
|
constraint animals_species_detail_text check (private.is_clean_text(species_detail, 80)),
|
|
-- No default: « inconnu » is chosen explicitly (N8).
|
|
sex public.animal_sex not null,
|
|
breed text
|
|
constraint animals_breed_text check (private.is_clean_text(breed, 80)),
|
|
birth_year smallint
|
|
constraint animals_birth_year_range check (birth_year between 1900 and 2100),
|
|
birth_date date
|
|
constraint animals_birth_date_range check (birth_date >= date '1900-01-01'),
|
|
name_key text collate "C"
|
|
generated always as (private.fold_text(name)) stored,
|
|
search_key text collate "C"
|
|
generated always as (
|
|
private.fold_text(name || ' ' || coalesce(breed, '') || ' ' || coalesce(species_detail, ''))
|
|
) stored,
|
|
create_request_id uuid not null,
|
|
create_request_digest bytea not null,
|
|
revision integer not null default 1
|
|
constraint animals_revision_positive check (revision >= 1),
|
|
archived_at timestamptz,
|
|
created_at timestamptz not null default now(),
|
|
updated_at timestamptz not null default now(),
|
|
constraint animals_workspace_id_unique unique (workspace_id, id),
|
|
constraint animals_create_request_unique unique (workspace_id, create_request_id),
|
|
-- « Autre » needs its detail; any other species refuses one.
|
|
constraint animals_species_detail_iff_other check ((species_id = 'other') = (species_detail is not null)),
|
|
-- D6b: a full date carries its year again; a year never becomes a date.
|
|
constraint animals_birth_date_year check (
|
|
birth_date is null
|
|
or (birth_year is not null and birth_year = extract(year from birth_date)::integer)
|
|
)
|
|
);
|
|
|
|
comment on table public.animals is
|
|
'Animals of a workspace. Writes only through create_animal, update_animal, set_animal_archived; reads through RLS and search_animals.';
|
|
comment on column public.animals.birth_year is
|
|
'Year of birth when known; repeated from birth_date when the full date is known (D6b).';
|
|
|
|
create index animals_workspace_name_idx on public.animals (workspace_id, name_key, id);
|
|
|
|
create trigger animals_set_updated_at
|
|
before update on public.animals
|
|
for each row execute function private.set_updated_at();
|
|
|
|
-- ---------------------------------------------------------------------------
|
|
-- animal_client_relations (contract §5.3: D7, D7a, N4, N5)
|
|
-- ---------------------------------------------------------------------------
|
|
create type public.animal_relation_role as enum ('owner', 'keeper', 'contact');
|
|
|
|
create table public.animal_client_relations (
|
|
id uuid primary key default gen_random_uuid(),
|
|
workspace_id uuid not null,
|
|
animal_id uuid not null,
|
|
client_id uuid not null,
|
|
relation_role public.animal_relation_role not null,
|
|
-- Real dates when known, never filled by default (N5).
|
|
started_on date
|
|
constraint animal_client_relations_started_on_range check (started_on >= date '1900-01-01'),
|
|
ended_on date
|
|
constraint animal_client_relations_ended_on_range check (ended_on >= date '1900-01-01'),
|
|
-- Technical time the end was recorded: null while current, never shown as the end date.
|
|
ended_at timestamptz,
|
|
-- Set once by void_animal_client_relation for a relation entered by mistake (N4).
|
|
voided_at timestamptz,
|
|
create_request_id uuid not null,
|
|
create_request_digest bytea not null,
|
|
revision integer not null default 1
|
|
constraint animal_client_relations_revision_positive check (revision >= 1),
|
|
created_at timestamptz not null default now(),
|
|
updated_at timestamptz not null default now(),
|
|
-- Animal and client of one workspace, whatever writes the row (not deferrable).
|
|
constraint animal_client_relations_animal_fk foreign key (workspace_id, animal_id)
|
|
references public.animals (workspace_id, id) on delete restrict,
|
|
constraint animal_client_relations_client_fk foreign key (workspace_id, client_id)
|
|
references public.clients (workspace_id, id) on delete restrict,
|
|
constraint animal_client_relations_ended_on_needs_end check (ended_on is null or ended_at is not null),
|
|
constraint animal_client_relations_dates_order
|
|
check (started_on is null or ended_on is null or started_on <= ended_on),
|
|
constraint animal_client_relations_create_request_unique unique (workspace_id, create_request_id)
|
|
);
|
|
|
|
comment on table public.animal_client_relations is
|
|
'Relations between an animal and a client of one workspace, with their history. Writes only through the relation RPCs; never deleted (a mistake is voided).';
|
|
|
|
-- One current relation per (animal, client, role); ended and voided rows are history.
|
|
create unique index animal_client_relations_current_unique
|
|
on public.animal_client_relations (animal_id, client_id, relation_role)
|
|
where ended_at is null and voided_at is null;
|
|
create index animal_client_relations_animal_idx on public.animal_client_relations (workspace_id, animal_id);
|
|
create index animal_client_relations_client_idx on public.animal_client_relations (workspace_id, client_id);
|
|
|
|
create trigger animal_client_relations_set_updated_at
|
|
before update on public.animal_client_relations
|
|
for each row execute function private.set_updated_at();
|
|
|
|
-- ---------------------------------------------------------------------------
|
|
-- Animal write RPCs (contract §9.3): authorise and lock in one statement, then
|
|
-- archived, revision, rules; the budget last; audit rows with ids only.
|
|
-- ---------------------------------------------------------------------------
|
|
create or replace function public.create_animal(
|
|
workspace_id uuid,
|
|
create_request_id uuid,
|
|
name text,
|
|
species_id text,
|
|
sex text,
|
|
species_detail text default null,
|
|
breed text default null,
|
|
birth_year text default null,
|
|
birth_date text default null,
|
|
client_id uuid default null,
|
|
relation_role text default null,
|
|
started_on text default null
|
|
)
|
|
returns jsonb
|
|
language plpgsql
|
|
volatile
|
|
security definer
|
|
set search_path = ''
|
|
as $$
|
|
#variable_conflict use_column
|
|
declare
|
|
v_sex public.animal_sex;
|
|
v_role public.animal_relation_role;
|
|
v_birth_year smallint;
|
|
v_birth_date date;
|
|
v_started_on date;
|
|
v_digest bytea;
|
|
v_existing_id uuid;
|
|
v_existing_revision integer;
|
|
v_existing_digest bytea;
|
|
v_client_archived_at timestamptz;
|
|
v_id uuid;
|
|
v_relation_id uuid;
|
|
begin
|
|
-- Serialises the creates of this workspace (replay check, caps, inserts).
|
|
perform 1 from public.workspaces w
|
|
where w.id = create_animal.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_sex := create_animal.sex::public.animal_sex;
|
|
v_role := create_animal.relation_role::public.animal_relation_role;
|
|
exception when invalid_text_representation then
|
|
raise exception 'invalid_input' using errcode = 'P0001';
|
|
end;
|
|
-- The initial relation is all or nothing: a client and a role, or neither.
|
|
if (create_animal.client_id is null) <> (v_role is null)
|
|
or (create_animal.client_id is null and create_animal.started_on is not null) then
|
|
raise exception 'invalid_input' using errcode = 'P0001';
|
|
end if;
|
|
v_birth_year := private.parse_birth_year(create_animal.birth_year);
|
|
v_birth_date := private.parse_record_date(create_animal.birth_date);
|
|
v_started_on := private.parse_record_date(create_animal.started_on);
|
|
v_digest := sha256(convert_to(jsonb_build_array(
|
|
create_animal.name, create_animal.species_id, v_sex, create_animal.species_detail,
|
|
create_animal.breed, v_birth_year, v_birth_date, create_animal.client_id, v_role, v_started_on
|
|
)::text, 'UTF8'));
|
|
|
|
select a.id, a.revision, a.create_request_digest
|
|
into v_existing_id, v_existing_revision, v_existing_digest
|
|
from public.animals a
|
|
where a.workspace_id = create_animal.workspace_id
|
|
and a.create_request_id = create_animal.create_request_id;
|
|
if found then
|
|
if v_existing_digest <> v_digest then
|
|
raise exception 'request_conflict' using errcode = 'P0001';
|
|
end if;
|
|
-- The initial relation shares the request id of its animal.
|
|
select r.id into v_relation_id
|
|
from public.animal_client_relations r
|
|
where r.workspace_id = create_animal.workspace_id
|
|
and r.create_request_id = create_animal.create_request_id
|
|
and r.animal_id = v_existing_id;
|
|
return jsonb_build_object('id', v_existing_id, 'revision', v_existing_revision, 'relation_id', v_relation_id);
|
|
end if;
|
|
|
|
if not exists (select 1 from public.species s where s.id = create_animal.species_id and s.active) then
|
|
raise exception 'invalid_input' using errcode = 'P0001';
|
|
end if;
|
|
if (select count(*) from public.animals a where a.workspace_id = create_animal.workspace_id) >= 20000 then
|
|
raise exception 'limit_reached' using errcode = 'P0001';
|
|
end if;
|
|
if create_animal.client_id is not null then
|
|
select c.archived_at into v_client_archived_at
|
|
from public.clients c
|
|
where c.id = create_animal.client_id
|
|
and c.workspace_id = create_animal.workspace_id
|
|
for share;
|
|
if not found then
|
|
raise exception 'not_found' using errcode = '42501';
|
|
end if;
|
|
if v_client_archived_at is not null then
|
|
raise exception 'archived' using errcode = 'P0001';
|
|
end if;
|
|
-- A relation created by another call with this request id would collide.
|
|
if exists (select 1 from public.animal_client_relations r
|
|
where r.workspace_id = create_animal.workspace_id
|
|
and r.create_request_id = create_animal.create_request_id) then
|
|
raise exception 'request_conflict' using errcode = 'P0001';
|
|
end if;
|
|
if (select count(*) from public.animal_client_relations r
|
|
where r.workspace_id = create_animal.workspace_id) >= 100000 then
|
|
raise exception 'limit_reached' using errcode = 'P0001';
|
|
end if;
|
|
end if;
|
|
|
|
-- Two audited writes when the relation comes along.
|
|
perform private.consume_write_budget(create_animal.workspace_id);
|
|
if create_animal.client_id is not null then
|
|
perform private.consume_write_budget(create_animal.workspace_id);
|
|
end if;
|
|
|
|
begin
|
|
insert into public.animals (
|
|
workspace_id, name, species_id, species_detail, sex, breed, birth_year, birth_date,
|
|
create_request_id, create_request_digest
|
|
)
|
|
values (
|
|
create_animal.workspace_id, create_animal.name, create_animal.species_id, create_animal.species_detail,
|
|
v_sex, create_animal.breed, v_birth_year, v_birth_date, create_animal.create_request_id, v_digest
|
|
)
|
|
returning id into v_id;
|
|
if create_animal.client_id is not null then
|
|
insert into public.animal_client_relations (
|
|
workspace_id, animal_id, client_id, relation_role, started_on, create_request_id, create_request_digest
|
|
)
|
|
values (
|
|
create_animal.workspace_id, v_id, create_animal.client_id, v_role, v_started_on,
|
|
create_animal.create_request_id,
|
|
sha256(convert_to(jsonb_build_array(v_id, create_animal.client_id, v_role, v_started_on)::text, 'UTF8'))
|
|
)
|
|
returning id into v_relation_id;
|
|
end if;
|
|
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';
|
|
when foreign_key_violation then
|
|
raise exception 'not_found' using errcode = '42501';
|
|
end;
|
|
|
|
perform private.log_event(
|
|
auth.uid(), 'animal.created', 'animal', v_id, null, null,
|
|
jsonb_build_object('workspace_id', create_animal.workspace_id)
|
|
);
|
|
if v_relation_id is not null then
|
|
perform private.log_event(
|
|
auth.uid(), 'animal_client_relation.created', 'animal_client_relation', v_relation_id, null, null,
|
|
jsonb_build_object('workspace_id', create_animal.workspace_id)
|
|
);
|
|
end if;
|
|
return jsonb_build_object('id', v_id, 'revision', 1, 'relation_id', v_relation_id);
|
|
end;
|
|
$$;
|
|
|
|
create or replace function public.update_animal(
|
|
animal_id uuid,
|
|
expected_revision integer,
|
|
name text,
|
|
species_id text,
|
|
sex text,
|
|
species_detail text,
|
|
breed text,
|
|
birth_year text,
|
|
birth_date text
|
|
)
|
|
returns jsonb
|
|
language plpgsql
|
|
volatile
|
|
security definer
|
|
set search_path = ''
|
|
as $$
|
|
#variable_conflict use_column
|
|
declare
|
|
v_row public.animals;
|
|
v_sex public.animal_sex;
|
|
v_birth_year smallint;
|
|
v_birth_date date;
|
|
begin
|
|
select a.* into v_row
|
|
from public.animals a
|
|
where a.id = update_animal.animal_id
|
|
and a.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_animal.expected_revision is distinct from v_row.revision then
|
|
raise exception 'conflict' using errcode = 'P0001';
|
|
end if;
|
|
|
|
begin
|
|
v_sex := update_animal.sex::public.animal_sex;
|
|
exception when invalid_text_representation then
|
|
raise exception 'invalid_input' using errcode = 'P0001';
|
|
end;
|
|
v_birth_year := private.parse_birth_year(update_animal.birth_year);
|
|
v_birth_date := private.parse_record_date(update_animal.birth_date);
|
|
-- A species may stay as it is; a new one must be active.
|
|
if update_animal.species_id is distinct from v_row.species_id
|
|
and not exists (select 1 from public.species s where s.id = update_animal.species_id and s.active) then
|
|
raise exception 'invalid_input' using errcode = 'P0001';
|
|
end if;
|
|
|
|
-- No change: nothing written, nothing counted.
|
|
if (update_animal.name, update_animal.species_id, v_sex, update_animal.species_detail, update_animal.breed,
|
|
v_birth_year, v_birth_date)
|
|
is not distinct from
|
|
(v_row.name, v_row.species_id, v_row.sex, v_row.species_detail, v_row.breed,
|
|
v_row.birth_year, v_row.birth_date) then
|
|
return jsonb_build_object('revision', v_row.revision);
|
|
end if;
|
|
|
|
perform private.consume_write_budget(v_row.workspace_id);
|
|
begin
|
|
update public.animals a
|
|
set name = update_animal.name, species_id = update_animal.species_id, sex = v_sex,
|
|
species_detail = update_animal.species_detail, breed = update_animal.breed,
|
|
birth_year = v_birth_year, birth_date = v_birth_date,
|
|
revision = a.revision + 1
|
|
where a.id = v_row.id;
|
|
exception when check_violation or not_null_violation or string_data_right_truncation
|
|
or invalid_text_representation or foreign_key_violation then
|
|
raise exception 'invalid_input' using errcode = 'P0001';
|
|
end;
|
|
|
|
perform private.log_event(
|
|
auth.uid(), 'animal.updated', 'animal', 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_animal_archived(
|
|
animal_id uuid,
|
|
archived boolean
|
|
)
|
|
returns jsonb
|
|
language plpgsql
|
|
volatile
|
|
security definer
|
|
set search_path = ''
|
|
as $$
|
|
#variable_conflict use_column
|
|
declare
|
|
v_row public.animals;
|
|
begin
|
|
select a.* into v_row
|
|
from public.animals a
|
|
where a.id = set_animal_archived.animal_id
|
|
and a.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_animal_archived.archived is null then
|
|
raise exception 'invalid_input' using errcode = 'P0001';
|
|
end if;
|
|
-- Already in that state: nothing written, nothing counted. The relations
|
|
-- stay as they are either way (§7.1).
|
|
if (v_row.archived_at is not null) = set_animal_archived.archived then
|
|
return jsonb_build_object('revision', v_row.revision);
|
|
end if;
|
|
|
|
perform private.consume_write_budget(v_row.workspace_id);
|
|
update public.animals a
|
|
set archived_at = case when set_animal_archived.archived then now() end,
|
|
revision = a.revision + 1
|
|
where a.id = v_row.id;
|
|
|
|
perform private.log_event(
|
|
auth.uid(),
|
|
case when set_animal_archived.archived then 'animal.archived' else 'animal.restored' end,
|
|
'animal', v_row.id, null, null,
|
|
jsonb_build_object('workspace_id', v_row.workspace_id)
|
|
);
|
|
return jsonb_build_object('revision', v_row.revision + 1);
|
|
end;
|
|
$$;
|
|
|
|
-- ---------------------------------------------------------------------------
|
|
-- Relation write RPCs. The animal's workspace is found among the caller's
|
|
-- writable ones without a lock, then the locks follow the one order: the
|
|
-- workspace (create only: the workspace cap), the animal, the relation, the
|
|
-- client (create only, shared), the budget last.
|
|
-- ---------------------------------------------------------------------------
|
|
create or replace function public.create_animal_client_relation(
|
|
animal_id uuid,
|
|
client_id uuid,
|
|
create_request_id uuid,
|
|
relation_role text,
|
|
started_on text default null
|
|
)
|
|
returns jsonb
|
|
language plpgsql
|
|
volatile
|
|
security definer
|
|
set search_path = ''
|
|
as $$
|
|
#variable_conflict use_column
|
|
declare
|
|
v_workspace_id uuid;
|
|
v_animal_archived_at timestamptz;
|
|
v_client_archived_at timestamptz;
|
|
v_role public.animal_relation_role;
|
|
v_started_on date;
|
|
v_digest bytea;
|
|
v_existing_id uuid;
|
|
v_existing_revision integer;
|
|
v_existing_digest bytea;
|
|
v_id uuid;
|
|
begin
|
|
select a.workspace_id into v_workspace_id
|
|
from public.animals a
|
|
where a.id = create_animal_client_relation.animal_id
|
|
and a.workspace_id in (select private.writable_workspace_ids());
|
|
if not found then
|
|
raise exception 'not_found' using errcode = '42501';
|
|
end if;
|
|
perform 1 from public.workspaces w where w.id = v_workspace_id for no key update;
|
|
select a.archived_at into v_animal_archived_at
|
|
from public.animals a
|
|
where a.id = create_animal_client_relation.animal_id
|
|
and a.workspace_id = v_workspace_id
|
|
for update;
|
|
if not found then
|
|
raise exception 'not_found' using errcode = '42501';
|
|
end if;
|
|
-- The client must be of the animal's workspace: any other is unknown.
|
|
select c.archived_at into v_client_archived_at
|
|
from public.clients c
|
|
where c.id = create_animal_client_relation.client_id
|
|
and c.workspace_id = v_workspace_id
|
|
for share;
|
|
if not found then
|
|
raise exception 'not_found' using errcode = '42501';
|
|
end if;
|
|
|
|
begin
|
|
v_role := create_animal_client_relation.relation_role::public.animal_relation_role;
|
|
exception when invalid_text_representation then
|
|
raise exception 'invalid_input' using errcode = 'P0001';
|
|
end;
|
|
if v_role is null then
|
|
raise exception 'invalid_input' using errcode = 'P0001';
|
|
end if;
|
|
v_started_on := private.parse_record_date(create_animal_client_relation.started_on);
|
|
v_digest := sha256(convert_to(jsonb_build_array(
|
|
create_animal_client_relation.animal_id, create_animal_client_relation.client_id, v_role, v_started_on
|
|
)::text, 'UTF8'));
|
|
|
|
-- A replay answers the relation it created, whatever happened since.
|
|
select r.id, r.revision, r.create_request_digest
|
|
into v_existing_id, v_existing_revision, v_existing_digest
|
|
from public.animal_client_relations r
|
|
where r.workspace_id = v_workspace_id
|
|
and r.create_request_id = create_animal_client_relation.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 v_animal_archived_at is not null or v_client_archived_at is not null then
|
|
raise exception 'archived' using errcode = 'P0001';
|
|
end if;
|
|
if exists (select 1 from public.animal_client_relations r
|
|
where r.animal_id = create_animal_client_relation.animal_id
|
|
and r.client_id = create_animal_client_relation.client_id
|
|
and r.relation_role = v_role
|
|
and r.ended_at is null and r.voided_at is null) then
|
|
raise exception 'duplicate_relation' using errcode = 'P0001';
|
|
end if;
|
|
-- Current relations count against 20; every row, ended and voided included,
|
|
-- against 200 per animal and 100 000 per workspace (a create-and-void loop
|
|
-- cannot grow the table without bound).
|
|
if (select count(*) from public.animal_client_relations r
|
|
where r.workspace_id = v_workspace_id and r.animal_id = create_animal_client_relation.animal_id
|
|
and r.ended_at is null and r.voided_at is null) >= 20
|
|
or (select count(*) from public.animal_client_relations r
|
|
where r.workspace_id = v_workspace_id and r.animal_id = create_animal_client_relation.animal_id) >= 200
|
|
or (select count(*) from public.animal_client_relations r
|
|
where r.workspace_id = v_workspace_id) >= 100000 then
|
|
raise exception 'limit_reached' using errcode = 'P0001';
|
|
end if;
|
|
|
|
perform private.consume_write_budget(v_workspace_id);
|
|
begin
|
|
insert into public.animal_client_relations (
|
|
workspace_id, animal_id, client_id, relation_role, started_on, create_request_id, create_request_digest
|
|
)
|
|
values (
|
|
v_workspace_id, create_animal_client_relation.animal_id, create_animal_client_relation.client_id,
|
|
v_role, v_started_on, create_animal_client_relation.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';
|
|
when unique_violation then
|
|
raise exception 'duplicate_relation' using errcode = 'P0001';
|
|
when foreign_key_violation then
|
|
raise exception 'not_found' using errcode = '42501';
|
|
end;
|
|
|
|
perform private.log_event(
|
|
auth.uid(), 'animal_client_relation.created', 'animal_client_relation', v_id, null, null,
|
|
jsonb_build_object('workspace_id', v_workspace_id)
|
|
);
|
|
return jsonb_build_object('id', v_id, 'revision', 1);
|
|
end;
|
|
$$;
|
|
|
|
-- Shared opening of the three RPCs that change an existing relation: find it
|
|
-- among the caller's writable workspaces, lock its animal then the relation,
|
|
-- refuse an archived animal, a stale revision and a voided relation. Returns
|
|
-- the locked relation row.
|
|
create or replace function private.lock_relation_for_write(p_relation_id uuid, p_expected_revision integer)
|
|
returns public.animal_client_relations
|
|
language plpgsql
|
|
volatile
|
|
security definer
|
|
set search_path = ''
|
|
as $$
|
|
declare
|
|
v_workspace_id uuid;
|
|
v_animal_id uuid;
|
|
v_animal_archived_at timestamptz;
|
|
v_row public.animal_client_relations;
|
|
begin
|
|
select r.workspace_id, r.animal_id into v_workspace_id, v_animal_id
|
|
from public.animal_client_relations r
|
|
where r.id = p_relation_id
|
|
and r.workspace_id in (select private.writable_workspace_ids());
|
|
if not found then
|
|
raise exception 'not_found' using errcode = '42501';
|
|
end if;
|
|
select a.archived_at into v_animal_archived_at
|
|
from public.animals a
|
|
where a.id = v_animal_id and a.workspace_id = v_workspace_id
|
|
for update;
|
|
select r.* into v_row
|
|
from public.animal_client_relations r
|
|
where r.id = p_relation_id
|
|
for update;
|
|
-- The relations of an archived animal are frozen (§7.1).
|
|
if v_animal_archived_at is not null then
|
|
raise exception 'archived' using errcode = 'P0001';
|
|
end if;
|
|
if p_expected_revision is distinct from v_row.revision then
|
|
raise exception 'conflict' using errcode = 'P0001';
|
|
end if;
|
|
-- A voided relation keeps its state: no end, no date change, no un-voiding (N4).
|
|
if v_row.voided_at is not null then
|
|
raise exception 'invalid_input' using errcode = 'P0001';
|
|
end if;
|
|
return v_row;
|
|
end;
|
|
$$;
|
|
|
|
comment on function private.lock_relation_for_write(uuid, integer) is
|
|
'Authorises and locks (animal, then relation) a relation write; refuses not_found, archived, conflict, voided. For the relation RPCs only.';
|
|
|
|
create or replace function public.end_animal_client_relation(
|
|
relation_id uuid,
|
|
expected_revision integer,
|
|
ended_on text default null
|
|
)
|
|
returns jsonb
|
|
language plpgsql
|
|
volatile
|
|
security definer
|
|
set search_path = ''
|
|
as $$
|
|
#variable_conflict use_column
|
|
declare
|
|
v_row public.animal_client_relations;
|
|
v_ended_on date;
|
|
begin
|
|
v_row := private.lock_relation_for_write(end_animal_client_relation.relation_id,
|
|
end_animal_client_relation.expected_revision);
|
|
if v_row.ended_at is not null then
|
|
raise exception 'already_ended' using errcode = 'P0001';
|
|
end if;
|
|
v_ended_on := private.parse_record_date(end_animal_client_relation.ended_on);
|
|
|
|
perform private.consume_write_budget(v_row.workspace_id);
|
|
begin
|
|
update public.animal_client_relations r
|
|
set ended_at = now(), ended_on = v_ended_on, revision = r.revision + 1
|
|
where r.id = v_row.id;
|
|
exception when check_violation then
|
|
raise exception 'invalid_input' using errcode = 'P0001';
|
|
end;
|
|
|
|
perform private.log_event(
|
|
auth.uid(), 'animal_client_relation.ended', 'animal_client_relation', 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.update_animal_client_relation(
|
|
relation_id uuid,
|
|
expected_revision integer,
|
|
started_on text,
|
|
ended_on text
|
|
)
|
|
returns jsonb
|
|
language plpgsql
|
|
volatile
|
|
security definer
|
|
set search_path = ''
|
|
as $$
|
|
#variable_conflict use_column
|
|
declare
|
|
v_row public.animal_client_relations;
|
|
v_started_on date;
|
|
v_ended_on date;
|
|
begin
|
|
v_row := private.lock_relation_for_write(update_animal_client_relation.relation_id,
|
|
update_animal_client_relation.expected_revision);
|
|
v_started_on := private.parse_record_date(update_animal_client_relation.started_on);
|
|
v_ended_on := private.parse_record_date(update_animal_client_relation.ended_on);
|
|
-- An end date belongs to an ended relation only.
|
|
if v_row.ended_at is null and v_ended_on is not null then
|
|
raise exception 'invalid_input' using errcode = 'P0001';
|
|
end if;
|
|
if (v_started_on, v_ended_on) is not distinct from (v_row.started_on, v_row.ended_on) then
|
|
return jsonb_build_object('revision', v_row.revision);
|
|
end if;
|
|
|
|
perform private.consume_write_budget(v_row.workspace_id);
|
|
begin
|
|
update public.animal_client_relations r
|
|
set started_on = v_started_on, ended_on = v_ended_on, revision = r.revision + 1
|
|
where r.id = v_row.id;
|
|
exception when check_violation then
|
|
raise exception 'invalid_input' using errcode = 'P0001';
|
|
end;
|
|
|
|
perform private.log_event(
|
|
auth.uid(), 'animal_client_relation.updated', 'animal_client_relation', 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.void_animal_client_relation(
|
|
relation_id uuid,
|
|
expected_revision integer
|
|
)
|
|
returns jsonb
|
|
language plpgsql
|
|
volatile
|
|
security definer
|
|
set search_path = ''
|
|
as $$
|
|
#variable_conflict use_column
|
|
declare
|
|
v_row public.animal_client_relations;
|
|
begin
|
|
v_row := private.lock_relation_for_write(void_animal_client_relation.relation_id,
|
|
void_animal_client_relation.expected_revision);
|
|
|
|
perform private.consume_write_budget(v_row.workspace_id);
|
|
-- Every value stays as entered; the row leaves the lists, the current
|
|
-- index, the derived values and the current cap (N4).
|
|
update public.animal_client_relations r
|
|
set voided_at = now(), revision = r.revision + 1
|
|
where r.id = v_row.id;
|
|
|
|
perform private.log_event(
|
|
auth.uid(), 'animal_client_relation.voided', 'animal_client_relation', v_row.id, null, null,
|
|
jsonb_build_object('workspace_id', v_row.workspace_id)
|
|
);
|
|
return jsonb_build_object('revision', v_row.revision + 1);
|
|
end;
|
|
$$;
|
|
|
|
-- ---------------------------------------------------------------------------
|
|
-- Reads (contract §9.3): security invoker, so RLS limits them to the caller's
|
|
-- workspaces; called with POST, so terms and cursors stay out of URLs. Voided
|
|
-- relations are left out of every list and derived value.
|
|
-- ---------------------------------------------------------------------------
|
|
-- The species filter is `species_filter`: a parameter cannot share the name
|
|
-- of the `species_id` output column.
|
|
create or replace function public.search_animals(
|
|
workspace_id uuid,
|
|
query text default '',
|
|
species_filter text default null,
|
|
archived boolean default false,
|
|
page_size integer default 25,
|
|
after_name_key text default null,
|
|
after_id uuid default null
|
|
)
|
|
returns table (
|
|
id uuid,
|
|
name text,
|
|
species_id text,
|
|
species_detail text,
|
|
sex public.animal_sex,
|
|
breed text,
|
|
birth_year smallint,
|
|
archived_at timestamptz,
|
|
name_key text,
|
|
current_owners jsonb,
|
|
current_owner_count integer
|
|
)
|
|
language plpgsql
|
|
stable
|
|
security invoker
|
|
set search_path = ''
|
|
as $$
|
|
#variable_conflict use_column
|
|
declare
|
|
v_term text := btrim(coalesce(search_animals.query, ''));
|
|
v_pattern text;
|
|
begin
|
|
if char_length(v_term) > 100
|
|
or search_animals.archived is null
|
|
or search_animals.page_size is null
|
|
or search_animals.page_size not between 1 and 100
|
|
or (search_animals.after_name_key is null) <> (search_animals.after_id is null) then
|
|
raise exception 'invalid_input' using errcode = 'P0001';
|
|
end if;
|
|
v_pattern := '%' || replace(replace(replace(private.fold_text(v_term), '\', '\\'), '%', '\%'), '_', '\_') || '%';
|
|
|
|
return query
|
|
with page as (
|
|
select a.id, a.workspace_id, a.name, a.species_id, a.species_detail, a.sex, a.breed, a.birth_year,
|
|
a.archived_at, a.name_key
|
|
from public.animals a
|
|
where a.workspace_id = search_animals.workspace_id
|
|
and (a.archived_at is not null) = search_animals.archived
|
|
and (search_animals.species_filter is null or a.species_id = search_animals.species_filter)
|
|
and (v_term = '' or a.search_key like v_pattern)
|
|
and (search_animals.after_name_key is null
|
|
or (a.name_key, a.id) > (search_animals.after_name_key collate "C", search_animals.after_id))
|
|
order by a.name_key, a.id
|
|
limit search_animals.page_size
|
|
)
|
|
select p.id, p.name, p.species_id, p.species_detail, p.sex, p.breed, p.birth_year, p.archived_at, p.name_key,
|
|
o.owners, o.owner_count
|
|
from page p
|
|
cross join lateral (
|
|
select coalesce(jsonb_agg(jsonb_build_object('client_id', x.client_id, 'display_name', x.display_name,
|
|
'archived', x.archived)
|
|
order by x.name_key, x.client_id) filter (where x.rank <= 3), '[]'::jsonb) as owners,
|
|
count(*)::integer as owner_count
|
|
from (
|
|
select c.id as client_id, c.display_name, c.archived_at is not null as archived, c.name_key,
|
|
row_number() over (order by c.name_key, c.id) as rank
|
|
from public.animal_client_relations r
|
|
join public.clients c on c.workspace_id = r.workspace_id and c.id = r.client_id
|
|
where r.workspace_id = p.workspace_id and r.animal_id = p.id
|
|
and r.relation_role = 'owner' and r.ended_at is null and r.voided_at is null
|
|
) x
|
|
) o
|
|
order by p.name_key, p.id;
|
|
end;
|
|
$$;
|
|
|
|
-- A client's relations with their animals: current first, then former, each
|
|
-- group in animal name order, then relation id. `sort_key` is the cursor:
|
|
-- '0' (current) or '1' (former) followed by the animal's name_key.
|
|
create or replace function public.list_client_animals(
|
|
client_id uuid,
|
|
page_size integer default 50,
|
|
after_sort text default null,
|
|
after_id uuid default null
|
|
)
|
|
returns table (
|
|
relation_id uuid,
|
|
relation_role public.animal_relation_role,
|
|
started_on date,
|
|
ended_on date,
|
|
is_current boolean,
|
|
relation_revision integer,
|
|
animal_id uuid,
|
|
animal_name text,
|
|
species_id text,
|
|
species_detail text,
|
|
sex public.animal_sex,
|
|
breed text,
|
|
birth_year smallint,
|
|
animal_archived_at timestamptz,
|
|
sort_key text
|
|
)
|
|
language plpgsql
|
|
stable
|
|
security invoker
|
|
set search_path = ''
|
|
as $$
|
|
#variable_conflict use_column
|
|
begin
|
|
if list_client_animals.page_size is null
|
|
or list_client_animals.page_size not between 1 and 100
|
|
or (list_client_animals.after_sort is null) <> (list_client_animals.after_id is null) then
|
|
raise exception 'invalid_input' using errcode = 'P0001';
|
|
end if;
|
|
|
|
return query
|
|
select s.relation_id, s.relation_role, s.started_on, s.ended_on, s.is_current, s.relation_revision,
|
|
s.animal_id, s.animal_name, s.species_id, s.species_detail, s.sex, s.breed, s.birth_year,
|
|
s.animal_archived_at, s.sort_key
|
|
from (
|
|
select r.id as relation_id, r.relation_role, r.started_on, r.ended_on, r.ended_at is null as is_current,
|
|
r.revision as relation_revision, a.id as animal_id, a.name as animal_name, a.species_id,
|
|
a.species_detail, a.sex, a.breed, a.birth_year, a.archived_at as animal_archived_at,
|
|
((case when r.ended_at is null then '0' else '1' end) || a.name_key) collate "C" as sort_key
|
|
from public.animal_client_relations r
|
|
join public.animals a on a.workspace_id = r.workspace_id and a.id = r.animal_id
|
|
where r.client_id = list_client_animals.client_id
|
|
and r.voided_at is null
|
|
) s
|
|
where list_client_animals.after_sort is null
|
|
or (s.sort_key, s.relation_id) > (list_client_animals.after_sort collate "C", list_client_animals.after_id)
|
|
order by s.sort_key, s.relation_id
|
|
limit list_client_animals.page_size;
|
|
end;
|
|
$$;
|
|
|
|
-- Before archiving a client: the active animals for which it holds the only
|
|
-- current owner relation (no other client, archived or not, has one).
|
|
create or replace function public.client_archive_preview(client_id uuid)
|
|
returns jsonb
|
|
language plpgsql
|
|
stable
|
|
security invoker
|
|
set search_path = ''
|
|
as $$
|
|
#variable_conflict use_column
|
|
declare
|
|
v_count integer;
|
|
v_animals jsonb;
|
|
begin
|
|
with sole as (
|
|
select a.id, a.name, a.name_key
|
|
from public.animal_client_relations r
|
|
join public.animals a on a.workspace_id = r.workspace_id and a.id = r.animal_id
|
|
where r.client_id = client_archive_preview.client_id
|
|
and r.relation_role = 'owner' and r.ended_at is null and r.voided_at is null
|
|
and a.archived_at is null
|
|
and not exists (
|
|
select 1 from public.animal_client_relations o
|
|
where o.workspace_id = r.workspace_id and o.animal_id = r.animal_id
|
|
and o.relation_role = 'owner' and o.ended_at is null and o.voided_at is null
|
|
and o.client_id <> r.client_id
|
|
)
|
|
)
|
|
select (select count(*)::integer from sole),
|
|
coalesce((select jsonb_agg(jsonb_build_object('id', f.id, 'name', f.name) order by f.name_key, f.id)
|
|
from (select * from sole order by sole.name_key, sole.id limit 10) f), '[]'::jsonb)
|
|
into v_count, v_animals;
|
|
return jsonb_build_object('count', v_count, 'animals', v_animals);
|
|
end;
|
|
$$;
|
|
|
|
-- search_clients gains current_animal_count (contract §9.3): a return type
|
|
-- change, so the function is dropped and created again with the same
|
|
-- parameters, rules and privileges.
|
|
drop function public.search_clients(uuid, text, boolean, integer, text, uuid);
|
|
|
|
create 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,
|
|
current_animal_count integer
|
|
)
|
|
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
|
|
with page as (
|
|
select c.id, c.workspace_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
|
|
)
|
|
-- Distinct animals of the current relations (any role, not voided), active animals only.
|
|
select p.id, p.kind, p.display_name, p.city, p.phone, p.email, p.archived_at, p.name_key,
|
|
(select count(distinct r.animal_id)::integer
|
|
from public.animal_client_relations r
|
|
join public.animals a on a.workspace_id = r.workspace_id and a.id = r.animal_id
|
|
where r.workspace_id = p.workspace_id and r.client_id = p.id
|
|
and r.ended_at is null and r.voided_at is null and a.archived_at is null)
|
|
from page p
|
|
order by p.name_key, p.id;
|
|
end;
|
|
$$;
|
|
|
|
comment on function public.create_animal(uuid, uuid, text, text, text, text, text, text, text, uuid, text, text) is
|
|
'Creates an animal in a writable workspace, with an optional initial relation; idempotent by create_request_id (divergent replay: request_conflict). Returns {id, revision, relation_id}.';
|
|
comment on function public.update_animal(uuid, integer, text, text, text, text, text, text, text) is
|
|
'Replaces the seven fields of an active animal at expected_revision. Returns {revision}.';
|
|
comment on function public.set_animal_archived(uuid, boolean) is
|
|
'Archives or restores an animal (same state: no write); its relations are kept. Returns {revision}.';
|
|
comment on function public.create_animal_client_relation(uuid, uuid, uuid, text, text) is
|
|
'Creates a current relation between an active animal and an active client of one workspace; idempotent by create_request_id. Returns {id, revision}.';
|
|
comment on function public.end_animal_client_relation(uuid, integer, text) is
|
|
'Ends a current relation (ended_at = now, optional ended_on). Returns {revision}.';
|
|
comment on function public.update_animal_client_relation(uuid, integer, text, text) is
|
|
'Corrects the dates of a relation that is not voided. Returns {revision}.';
|
|
comment on function public.void_animal_client_relation(uuid, integer) is
|
|
'Voids a relation entered by mistake (N4): kept, frozen, left out of lists and derived values. Returns {revision}.';
|
|
comment on function public.search_animals(uuid, text, text, boolean, integer, text, uuid) is
|
|
'Folded search (name, breed, species detail) in one workspace, optional species filter, keyset pages on (name_key, id), with the current owners.';
|
|
comment on function public.list_client_animals(uuid, integer, text, uuid) is
|
|
'A client''s relations with their animals, current then former, keyset on (sort_key, relation_id); 50 by default, 100 at most.';
|
|
comment on function public.client_archive_preview(uuid) is
|
|
'Active animals for which the client is the only current owner: {count, animals: first 10 by name}.';
|
|
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, with current_animal_count.';
|
|
|
|
-- ---------------------------------------------------------------------------
|
|
-- Privileges + RLS (N7: nothing for anon or service_role)
|
|
-- ---------------------------------------------------------------------------
|
|
revoke all on public.animals from public, anon, authenticated, service_role;
|
|
grant select (
|
|
id, workspace_id, name, species_id, species_detail, sex, breed, birth_year, birth_date,
|
|
name_key, search_key, create_request_id, revision, archived_at, created_at, updated_at
|
|
) on public.animals to authenticated;
|
|
|
|
revoke all on public.animal_client_relations from public, anon, authenticated, service_role;
|
|
grant select (
|
|
id, workspace_id, animal_id, client_id, relation_role, started_on, ended_on, ended_at, voided_at,
|
|
create_request_id, revision, created_at, updated_at
|
|
) on public.animal_client_relations to authenticated;
|
|
|
|
alter table public.animals enable row level security;
|
|
alter table public.animal_client_relations enable row level security;
|
|
|
|
create policy animals_select_member
|
|
on public.animals
|
|
for select
|
|
to authenticated
|
|
using (workspace_id in (select private.caller_workspace_ids()));
|
|
|
|
create policy animal_client_relations_select_member
|
|
on public.animal_client_relations
|
|
for select
|
|
to authenticated
|
|
using (workspace_id in (select private.caller_workspace_ids()));
|
|
|
|
revoke all on function private.utc_tomorrow(timestamptz) from public, anon, authenticated, service_role;
|
|
revoke all on function private.parse_record_date(text) from public, anon, authenticated, service_role;
|
|
revoke all on function private.parse_birth_year(text) from public, anon, authenticated, service_role;
|
|
revoke all on function private.lock_relation_for_write(uuid, integer) from public, anon, authenticated, service_role;
|
|
revoke all on function public.create_animal(uuid, uuid, text, text, text, text, text, text, text, uuid, text, text)
|
|
from public, anon, authenticated, service_role;
|
|
revoke all on function public.update_animal(uuid, integer, text, text, text, text, text, text, text)
|
|
from public, anon, authenticated, service_role;
|
|
revoke all on function public.set_animal_archived(uuid, boolean) from public, anon, authenticated, service_role;
|
|
revoke all on function public.create_animal_client_relation(uuid, uuid, uuid, text, text)
|
|
from public, anon, authenticated, service_role;
|
|
revoke all on function public.end_animal_client_relation(uuid, integer, text) from public, anon, authenticated, service_role;
|
|
revoke all on function public.update_animal_client_relation(uuid, integer, text, text)
|
|
from public, anon, authenticated, service_role;
|
|
revoke all on function public.void_animal_client_relation(uuid, integer) from public, anon, authenticated, service_role;
|
|
revoke all on function public.search_animals(uuid, text, text, boolean, integer, text, uuid)
|
|
from public, anon, authenticated, service_role;
|
|
revoke all on function public.list_client_animals(uuid, integer, text, uuid) from public, anon, authenticated, service_role;
|
|
revoke all on function public.client_archive_preview(uuid) 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_animal(uuid, uuid, text, text, text, text, text, text, text, uuid, text, text) to authenticated;
|
|
grant execute on function public.update_animal(uuid, integer, text, text, text, text, text, text, text) to authenticated;
|
|
grant execute on function public.set_animal_archived(uuid, boolean) to authenticated;
|
|
grant execute on function public.create_animal_client_relation(uuid, uuid, uuid, text, text) to authenticated;
|
|
grant execute on function public.end_animal_client_relation(uuid, integer, text) to authenticated;
|
|
grant execute on function public.update_animal_client_relation(uuid, integer, text, text) to authenticated;
|
|
grant execute on function public.void_animal_client_relation(uuid, integer) to authenticated;
|
|
grant execute on function public.search_animals(uuid, text, text, boolean, integer, text, uuid) to authenticated;
|
|
grant execute on function public.list_client_animals(uuid, integer, text, uuid) to authenticated;
|
|
grant execute on function public.client_archive_preview(uuid) to authenticated;
|
|
grant execute on function public.search_clients(uuid, text, boolean, integer, text, uuid) to authenticated;
|