Files
faunosteo/supabase/migrations/20261026100000_animals_relations.sql
bchanot b54c8e5bd8 feat(supabase): animals and relations M3 with RPCs, history and caps
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.
2026-10-26 00:52:22 +01:00

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;