Skip to content

Every analytics tool wants a subscription. And your data. I built Twiligent instead. YouTube stats. Instagram stats. One dashboard. Runs locally on your machine.

1.8 KB
Raw Blame
-- puts back the earlier automation_queue_health, without public replies
create or replace function public.automation_queue_health(p_lease_seconds integer)
returns jsonb
language sql
stable
set search_path = ''
as $$
    select jsonb_build_object(
        'due', (
            select count(*) from public.automation_comments c
            where c.status in ('queued', 'retry_wait') and c.next_attempt_at <= now()
        ),
        'oldest_due_at', (
            select min(c.next_attempt_at) from public.automation_comments c
            where c.status in ('queued', 'retry_wait') and c.next_attempt_at <= now()
        ),
        'waiting', (
            select count(*) from public.automation_comments c where c.status in ('queued', 'retry_wait')
        ),
        'stale', (
            select count(*) from public.automation_comments c
            where c.status in ('processing', 'sending') and c.claimed_at < now() - make_interval(secs => p_lease_seconds)
        ),
        'needs_review', (
            select count(*) from public.automation_comments c where c.status = 'needs_review'
        ),
        'followups_due', (
            select count(*) from public.automation_followups f
            where f.status in ('queued', 'retry_wait') and f.next_attempt_at <= now()
        ),
        'paused_accounts', (
            select count(*) from public.automation_accounts a where a.paused
        ),
        'limited_accounts', (
            select count(*) from public.automation_accounts a
            where a.cooldown_until > now() or a.limited_until > now()
        )
    );
$$;

-- functions in public are exposed through the Data API, so only the service role may call it
revoke execute on function public.automation_queue_health(integer) from public, anon, authenticated;
grant execute on function public.automation_queue_health(integer) to service_role;