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.

2.3 KB
Raw Blame
-- replaces automation_stats so it also counts public replies
create or replace function public.automation_stats(
    p_user_id uuid,
    p_from timestamptz,
    p_to timestamptz,
    p_account_id text default null,
    p_rule_id text default null
)
returns jsonb
language sql
stable
set search_path = ''
as $$
    select jsonb_build_object(
        'comments', coalesce((
            select jsonb_object_agg(counted.status, counted.total)
            from (
                select c.status, count(*) as total
                from public.automation_comments c
                where c.user_id = p_user_id
                    and c.created_at >= p_from and c.created_at < p_to
                    and (p_account_id is null or c.account_id = p_account_id)
                    and (p_rule_id is null or c.rule_id = p_rule_id)
                group by c.status
            ) counted
        ), '{}'::jsonb),
        'public_replies', coalesce((
            select jsonb_object_agg(counted.status, counted.total)
            from (
                select p.status, count(*) as total
                from public.automation_public_replies p
                where p.user_id = p_user_id
                    and p.created_at >= p_from and p.created_at < p_to
                    and (p_account_id is null or p.account_id = p_account_id)
                    and (p_rule_id is null or p.rule_id = p_rule_id)
                group by p.status
            ) counted
        ), '{}'::jsonb),
        'attempts', coalesce((
            select jsonb_object_agg(counted.outcome, counted.total)
            from (
                select t.outcome, count(*) as total
                from public.automation_attempts t
                where t.user_id = p_user_id
                    and t.started_at >= p_from and t.started_at < p_to
                    and (p_account_id is null or t.account_id = p_account_id)
                    and (p_rule_id is null or t.rule_id = p_rule_id)
                group by t.outcome
            ) counted
        ), '{}'::jsonb)
    );
$$;

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