File size: 3,599 Bytes
d410f54 | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 | -- Privacy-safe operational telemetry. Only the server-side service role may use it.
create table if not exists public.operational_error_events (
id bigint generated always as identity primary key,
occurred_at timestamptz not null default statement_timestamp(),
category text not null check (category in (
'live_connection_failure', 'timeout', 'authentication', 'database', 'upstream_service', 'internal'
)),
component text not null check (component in (
'live_session', 'authentication', 'database', 'model', 'text_to_speech', 'mcp', 'application'
)),
outcome text not null check (outcome in (
'poll_failed', 'timed_out', 'unavailable', 'rejected', 'exception'
))
);
create index if not exists operational_error_events_occurred_category_idx
on public.operational_error_events (occurred_at, category);
alter table public.operational_error_events enable row level security;
revoke all on table public.operational_error_events from public, anon, authenticated;
grant select, insert, delete on table public.operational_error_events to service_role;
grant usage, select on sequence public.operational_error_events_id_seq to service_role;
do $$
begin
if not exists (select 1 from pg_policies where schemaname = 'public'
and tablename = 'operational_error_events'
and policyname = 'service role manages operational errors') then
create policy "service role manages operational errors"
on public.operational_error_events for all to service_role
using (true) with check (true);
end if;
end $$;
create or replace function public.operational_error_summary(
p_since timestamptz, p_until timestamptz, p_category text default null,
p_timezone text default 'Asia/Jerusalem')
returns table(local_date date, category text, errors bigint, database_read_at timestamptz)
language plpgsql security invoker set search_path = '' as $$
begin
if p_since >= p_until or (p_category is not null and p_category not in (
'live_connection_failure', 'timeout', 'authentication', 'database', 'upstream_service', 'internal'
)) then
raise exception 'invalid operational error summary parameters';
end if;
perform statement_timestamp() at time zone p_timezone;
return query
select (event.occurred_at at time zone p_timezone)::date, event.category,
count(*)::bigint, statement_timestamp()
from public.operational_error_events event
where event.occurred_at >= p_since and event.occurred_at < p_until
and (p_category is null or event.category = p_category)
group by (event.occurred_at at time zone p_timezone)::date, event.category
order by (event.occurred_at at time zone p_timezone)::date, event.category;
end $$;
revoke execute on function public.operational_error_summary(timestamptz, timestamptz, text, text)
from public, anon, authenticated;
grant execute on function public.operational_error_summary(timestamptz, timestamptz, text, text)
to service_role;
-- Supabase Cron uses pg_cron. Re-running this migration replaces the named job.
create extension if not exists pg_cron with schema extensions;
do $$
declare existing_job bigint;
begin
select jobid into existing_job from cron.job
where jobname = 'delete-operational-errors-after-90-days';
if existing_job is not null then
perform cron.unschedule(existing_job);
end if;
perform cron.schedule(
'delete-operational-errors-after-90-days',
'17 2 * * *',
$job$delete from public.operational_error_events
where occurred_at < statement_timestamp() - interval '90 days'$job$
);
end $$;
|