| |
| 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; |
|
|
| |
| 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 $$; |
|
|
