Every analytics tool wants a subscription. And your data. I built Twiligent instead. YouTube stats. Instagram stats. One dashboard. Runs locally on your machine.
-- puts back reserve_automation_send with the hourly account limit counted per connected account row
create or replace function public.reserve_automation_send(
p_id text,
p_attempt_id text,
p_global_limit integer,
p_user_limit integer,
p_account_limit integer,
p_recipient_limit integer,
p_message_digest text default null
)
returns table (outcome text, retry_at timestamptz)
language plpgsql
set search_path = ''
as $$
#variable_conflict use_column
declare
job public.automation_comments%rowtype;
controls public.automation_accounts%rowtype;
rule_limit integer;
hour_ago timestamptz := now() - interval '1 hour';
free_at timestamptz;
begin
-- one reservation at a time across every Worker, so two jobs cannot both take the last free send
perform pg_advisory_xact_lock(hashtext('reserve_automation_send'));
select * into job from public.automation_comments c where c.id = p_id and c.status = 'processing' for update;
if not found then
return query select 'gone'::text, null::timestamptz;
return;
end if;
select * into controls from public.automation_accounts a where a.account_id = job.account_id;
if controls.paused then
return query select 'paused'::text, null::timestamptz;
return;
end if;
if controls.cooldown_until > now() then
return query select 'cooldown'::text, controls.cooldown_until;
return;
end if;
-- a person who comments again and again gets a few replies a day, never a stream of them; retries of this
-- comment are one reply, so only other comments count
if job.from_id is not null and (
select count(distinct t.automation_comment_id) from public.automation_attempts t
where t.account_id = job.account_id and t.recipient_id = job.from_id and t.kind = 'private_reply'
and t.started_at > now() - interval '1 day' and t.automation_comment_id <> job.id
) >= p_recipient_limit then
return query select 'recipient_limit'::text, null::timestamptz;
return;
end if;
-- when a scope is full, a send frees up an hour after the oldest one in the window
select r.hourly_limit into rule_limit from public.automation_rules r where r.id = job.rule_id;
if rule_limit is not null then
select case when count(*) >= rule_limit then min(t.started_at) + interval '1 hour' end into free_at
from public.automation_attempts t where t.rule_id = job.rule_id and t.kind = 'private_reply' and t.started_at > hour_ago;
if free_at is not null then
return query select 'rule_limit'::text, free_at;
return;
end if;
end if;
select case when count(*) >= p_account_limit then min(t.started_at) + interval '1 hour' end into free_at
from public.automation_attempts t where t.account_id = job.account_id and t.kind = 'private_reply' and t.started_at > hour_ago;
if free_at is not null then
insert into public.automation_accounts (account_id, user_id, limited_until)
values (job.account_id, job.user_id, free_at)
on conflict (account_id) do update set limited_until = excluded.limited_until;
return query select 'account_limit'::text, free_at;
return;
end if;
select case when count(*) >= p_user_limit then min(t.started_at) + interval '1 hour' end into free_at
from public.automation_attempts t where t.user_id = job.user_id and t.kind = 'private_reply' and t.started_at > hour_ago;
if free_at is not null then
return query select 'user_limit'::text, free_at;
return;
end if;
select case when count(*) >= p_global_limit then min(t.started_at) + interval '1 hour' end into free_at
from public.automation_attempts t where t.kind = 'private_reply' and t.started_at > hour_ago;
if free_at is not null then
return query select 'global_limit'::text, free_at;
return;
end if;
update public.automation_comments c
set status = 'sending', claimed_at = now(), attempts = c.attempts + 1
where c.id = p_id;
insert into public.automation_attempts (id, user_id, account_id, automation_comment_id, rule_id, recipient_id, message_digest)
values (p_attempt_id, job.user_id, job.account_id, job.id, job.rule_id, job.from_id, p_message_digest);
return query select 'ok'::text, null::timestamptz;
end;
$$;
-- functions in public are exposed through the Data API, so only the service role may call it
revoke execute on function public.reserve_automation_send(text, text, integer, integer, integer, integer, text) from public, anon, authenticated;
grant execute on function public.reserve_automation_send(text, text, integer, integer, integer, integer, text) to service_role;