199 lines
6.9 KiB
PL/PgSQL
199 lines
6.9 KiB
PL/PgSQL
-- Support tickets schema, RLS and triggers
|
|
-- Create tables
|
|
create table if not exists public.tickets (
|
|
id uuid primary key default gen_random_uuid(),
|
|
org_id uuid not null references public.organizations(id) on delete cascade,
|
|
subject text not null,
|
|
status text not null default 'open' check (status in ('open','waiting_client','waiting_staff','resolved','closed')),
|
|
priority text not null default 'normal' check (priority in ('low','normal','high','urgent')),
|
|
created_by uuid references auth.users(id) on delete set null,
|
|
assigned_to uuid references auth.users(id) on delete set null,
|
|
source text not null default 'web' check (source in ('web','email','whatsapp','staff','api')),
|
|
last_message_at timestamptz not null default now(),
|
|
message_count int not null default 0,
|
|
unread_by_client int not null default 0,
|
|
unread_by_staff int not null default 0,
|
|
created_at timestamptz not null default now(),
|
|
updated_at timestamptz not null default now()
|
|
);
|
|
|
|
create table if not exists public.ticket_messages (
|
|
id uuid primary key default gen_random_uuid(),
|
|
ticket_id uuid not null references public.tickets(id) on delete cascade,
|
|
author_id uuid references auth.users(id) on delete set null,
|
|
body text not null,
|
|
internal boolean not null default false,
|
|
via text not null default 'web' check (via in ('web','email','whatsapp','staff','system')),
|
|
created_at timestamptz not null default now()
|
|
);
|
|
|
|
create index if not exists ticket_messages_ticket_id_created_at_idx on public.ticket_messages(ticket_id, created_at);
|
|
create index if not exists tickets_org_id_last_message_at_idx on public.tickets(org_id, last_message_at desc);
|
|
|
|
-- Helper: is current user a staff user?
|
|
create or replace function public.is_staff()
|
|
returns boolean
|
|
language sql
|
|
stable
|
|
as $$
|
|
select exists (
|
|
select 1 from public.staff_users s
|
|
where s.user_id = auth.uid() and coalesce(s.is_staff, false) = true
|
|
);
|
|
$$;
|
|
|
|
-- RLS
|
|
alter table public.tickets enable row level security;
|
|
alter table public.ticket_messages enable row level security;
|
|
|
|
-- Tickets: read policies
|
|
drop policy if exists tickets_staff_read on public.tickets;
|
|
create policy tickets_staff_read on public.tickets
|
|
for select using (public.is_staff());
|
|
|
|
drop policy if exists tickets_client_read on public.tickets;
|
|
create policy tickets_client_read on public.tickets
|
|
for select using (
|
|
exists (
|
|
select 1 from public.organization_members m
|
|
where m.user_id = auth.uid()
|
|
and m.revoked = false
|
|
and m.org_id = tickets.org_id
|
|
)
|
|
);
|
|
|
|
-- Tickets: insert
|
|
drop policy if exists tickets_client_insert on public.tickets;
|
|
create policy tickets_client_insert on public.tickets
|
|
for insert with check (
|
|
created_by = auth.uid()
|
|
and exists (
|
|
select 1 from public.organization_members m
|
|
where m.user_id = auth.uid() and m.revoked = false and m.org_id = tickets.org_id
|
|
)
|
|
);
|
|
|
|
drop policy if exists tickets_staff_insert on public.tickets;
|
|
create policy tickets_staff_insert on public.tickets
|
|
for insert with check (public.is_staff());
|
|
|
|
-- Tickets: update/delete only staff
|
|
drop policy if exists tickets_staff_update on public.tickets;
|
|
create policy tickets_staff_update on public.tickets
|
|
for update using (public.is_staff());
|
|
|
|
drop policy if exists tickets_staff_delete on public.tickets;
|
|
create policy tickets_staff_delete on public.tickets
|
|
for delete using (public.is_staff());
|
|
|
|
-- Messages: read
|
|
drop policy if exists ticket_messages_staff_read on public.ticket_messages;
|
|
create policy ticket_messages_staff_read on public.ticket_messages
|
|
for select using (public.is_staff());
|
|
|
|
drop policy if exists ticket_messages_client_read on public.ticket_messages;
|
|
create policy ticket_messages_client_read on public.ticket_messages
|
|
for select using (
|
|
internal = false and exists (
|
|
select 1 from public.tickets t
|
|
where t.id = ticket_messages.ticket_id and exists (
|
|
select 1 from public.organization_members m
|
|
where m.user_id = auth.uid() and m.revoked = false and m.org_id = t.org_id
|
|
)
|
|
)
|
|
);
|
|
|
|
-- Messages: insert
|
|
drop policy if exists ticket_messages_client_insert on public.ticket_messages;
|
|
create policy ticket_messages_client_insert on public.ticket_messages
|
|
for insert with check (
|
|
internal = false
|
|
and author_id = auth.uid()
|
|
and exists (
|
|
select 1 from public.tickets t
|
|
where t.id = ticket_messages.ticket_id and exists (
|
|
select 1 from public.organization_members m
|
|
where m.user_id = auth.uid() and m.revoked = false and m.org_id = t.org_id
|
|
)
|
|
)
|
|
);
|
|
|
|
drop policy if exists ticket_messages_staff_insert on public.ticket_messages;
|
|
create policy ticket_messages_staff_insert on public.ticket_messages
|
|
for insert with check (public.is_staff());
|
|
|
|
-- Trigger: updated_at
|
|
create or replace function public.set_updated_at()
|
|
returns trigger
|
|
language plpgsql
|
|
as $$
|
|
begin
|
|
new.updated_at = now();
|
|
return new;
|
|
end;
|
|
$$;
|
|
|
|
drop trigger if exists set_tickets_updated_at on public.tickets;
|
|
create trigger set_tickets_updated_at
|
|
before update on public.tickets
|
|
for each row execute function public.set_updated_at();
|
|
|
|
-- Trigger: on message insert, update counters
|
|
create or replace function public.on_ticket_message_insert()
|
|
returns trigger
|
|
language plpgsql
|
|
as $$
|
|
declare
|
|
is_author_staff boolean;
|
|
begin
|
|
select public.is_staff() into is_author_staff; -- evaluates for current invoker, fine for server-side inserts
|
|
|
|
update public.tickets t
|
|
set last_message_at = new.created_at,
|
|
message_count = t.message_count + 1,
|
|
unread_by_client = case when (not new.internal) and is_author_staff then t.unread_by_client + 1 else t.unread_by_client end,
|
|
unread_by_staff = case when (not new.internal) and not is_author_staff then t.unread_by_staff + 1 else t.unread_by_staff end
|
|
where t.id = new.ticket_id;
|
|
|
|
return new;
|
|
end;
|
|
$$;
|
|
|
|
drop trigger if exists ticket_messages_after_insert on public.ticket_messages;
|
|
create trigger ticket_messages_after_insert
|
|
after insert on public.ticket_messages
|
|
for each row execute function public.on_ticket_message_insert();
|
|
|
|
-- Optional: mark read helper with security definer so clients can reset their counter
|
|
create or replace function public.mark_ticket_read(p_ticket_id uuid)
|
|
returns void
|
|
language plpgsql
|
|
security definer
|
|
as $$
|
|
declare
|
|
is_viewer_staff boolean;
|
|
viewer uuid;
|
|
v_org uuid;
|
|
begin
|
|
viewer := auth.uid();
|
|
if viewer is null then
|
|
raise exception 'unauthenticated';
|
|
end if;
|
|
|
|
select public.is_staff() into is_viewer_staff;
|
|
|
|
if is_viewer_staff then
|
|
update public.tickets set unread_by_staff = 0 where id = p_ticket_id;
|
|
else
|
|
-- ensure viewer is member of the ticket org
|
|
select org_id into v_org from public.tickets where id = p_ticket_id;
|
|
if not exists (
|
|
select 1 from public.organization_members m where m.user_id = viewer and m.revoked = false and m.org_id = v_org
|
|
) then
|
|
raise exception 'forbidden';
|
|
end if;
|
|
update public.tickets set unread_by_client = 0 where id = p_ticket_id;
|
|
end if;
|
|
end;
|
|
$$;
|
|
|