patternModerate
Email open/click tracking without open redirects: stored-link tokens, inferred opens, row lock
Viewed 0 times
open redirecttracking pixelclick trackinginferred openselect for updatemailpit
Problem
Building 1:1 sales-email tracking (pixel + rewritten links) commonly ends with an open redirect (/click?url=...), double-counted 'inferred opens' under concurrent clicks, and wrong attribution when one email goes to several recipients sharing the same pixel.
Solution
Store each rewritten link as a row (token = 128-bit random base64url, url CHECK ^https?://) and redirect only to the stored URL; unknown token -> 404, never read a URL from the request. Do the click logic in one SQL function: SELECT ... FOR UPDATE on the message row, then if no 'open' event exists insert an inferred open with the click's timestamp, then insert the click. Keep a single pixel token per message; if the message had more than one recipient (to+cc+bcc), store contact_id NULL on the message so events are not attributed to anyone. Skip rewriting links whose visible text is itself a URL (mail clients and users distrust mismatched hosts), and leave the text/plain part untouched. Filter bots in the app layer (empty UA or scanner UAs) but still redirect them, just without counting. Pixel response: same 1x1 GIF for every token, Cache-Control no-store, no cookies.
Why
Reading the target URL from the query string lets anyone use your domain as a redirector; check-then-insert for the inferred open races without a lock; a shared pixel cannot tell which recipient opened.
Code Snippets
Click handler with inferred open under a row lock
select * into l from email_links where token = p_link_token;
if not found then return null; end if;
select * into m from email_messages where record_id = l.record_id for update;
if p_count and m.tracked then
if not exists (select 1 from email_events where record_id = m.record_id and kind = 'open') then
insert into email_events (record_id, kind, contact_id, inferred, occurred_at) values (m.record_id, 'open', m.contact_id, true, t);
end if;
insert into email_events (record_id, kind, link_id, contact_id, occurred_at) values (m.record_id, 'click', l.id, m.contact_id, t);
end if;
return l.url;Revisions (0)
No revisions yet.