Identity stitching
The part of your analytics stack that's almost certainly broken — five layers, three bugs we caught in review, and the SQL to copy.
A user clicks your Meta ad on their phone Tuesday afternoon. They get distracted, close the tab, never sign up. Saturday morning they're on their laptop, type your URL, sign up with their phone number, buy $25 of credits. Your dashboard shows you got a free signup and a direct-traffic purchase.
Both are wrong.
That ad on Tuesday earned you the customer. The Saturday signup is the same person, not a new one. And every dollar you'd otherwise spend optimizing toward the wrong campaigns starts here, in the gap between "two different cookies" and "one human being."
This article is a tour of the identity-stitching layer we shipped in the last week — what it does, why every part of it is necessary, and the three subtle bugs we caught in code review that you'll probably hit too. It's written for both founders running paid acquisition and engineers who'll be the ones building this. Skim the prose, copy the SQL.
Why most attribution setups silently lose data
Three identity layers exist in any web product, and almost no one stitches all three:
- The browser cookie layer. A first-party UUID, scoped to one device + one browser. ITP and ad blockers chew at it; users wipe cookies; nobody is the same person across phone and laptop here.
- The server session layer. Your
users.id, only meaningful after the user proves who they are (logs in, verifies an OTP, confirms an email). Strong identity, narrow time window. - The ad-platform graph layer. Meta and Google have a logged-in graph an order of magnitude richer than yours: every device the user has touched a Meta property on is one node in their graph. You can't see this graph. You can only feed them keys (hashed phone, hashed email, click IDs) and let them join.
The default analytics setup — drop a Segment script, install GA4, install the Meta pixel — collects all three layers in parallel and never glues them together. You can see the same human as three different "users" in three different tools, and any cross-device journey gets attributed to "direct traffic."
"Just use Segment" doesn't fix this. Segment writes the cookie, fans out the events, but the identity merge is your job. Same with PostHog out of the box:
posthog.identify(user.id)glues anonymous → identified events forward in time, but doesn't backfill the events that fired before identify, and doesn't follow the user across devices.
The fix isn't a vendor. It's a deliberate stitching layer that you control, sitting between the browser and every downstream destination. Below is the one we shipped.
The deterministic mix
Five layers, each closing one specific gap. None of them are clever; the trick is using all five together.
Layer 1: a long-lived first-party cookie
as_anon_id=<uuid v4>
Domain=.your-root.com
Path=/
SameSite=Lax
Max-Age=31536000 # 1 year
Set on the first visit, before anything else fires. JS-readable so the browser SDK can stamp it on every event; HTTP-readable so the server can stamp the same value on server-side events. Root domain so it survives subdomain hops (www. → app.).
Boring. Foundational. This cookie is the spine the whole pipeline hangs off of.
Layer 2: an anon_visitors table that captures every touch
create table anon_visitors (
anon_id text primary key,
first_seen_at timestamptz not null default now(),
last_seen_at timestamptz not null default now(),
first_touch jsonb not null default '{}'::jsonb,
last_touch jsonb not null default '{}'::jsonb,
merged_user_id uuid references auth.users(id) on delete set null,
merged_at timestamptz
);
One row per cookie that has ever carried attribution. The first_touch column is write-once — the campaign that earned this human is sacred and never overwritten. last_touch updates on every visit that carries new UTMs or click IDs.
The touch blob shape:
type Touch = {
gclid?: string; fbclid?: string; ttclid?: string;
msclkid?: string; gbraid?: string; wbraid?: string; li_fat_id?: string;
utm_source?: string; utm_medium?: string;
utm_campaign?: string; utm_content?: string; utm_term?: string;
landing_url?: string; referrer?: string;
fbp?: string; // Meta browser-cookie value
fbc?: string; // Meta-formatted from fbclid
ts: string;
};
Why JSONB instead of 20 columns? Because ad platforms keep inventing new click-ID formats. gbraid and wbraid didn't exist three years ago. Meta added fbc last year. We don't want a migration every time. The JSONB blob travels as one unit (anon → user, touch → ad-platform payload), and the cost of querying its keys individually is paid only when we send to ad platforms, where we explicitly extract what we need.
The upsert is one RPC, idempotent, called from the browser via /api/anon-touch:
create or replace function public.upsert_anon_touch(p_anon_id text, p_touch jsonb)
returns void
language plpgsql
security definer
as $$
begin
insert into anon_visitors (anon_id, first_touch, last_touch, last_seen_at)
values (p_anon_id, p_touch, p_touch, now())
on conflict (anon_id) do update set
last_touch = excluded.last_touch,
last_seen_at = now();
-- first_touch is preserved by the absence of an assignment in DO UPDATE.
end;
$$;
The first_touch write-once property comes for free from the upsert: on conflict, we only assign last_touch. First-touch never appears on the right side of an update set, so it's never rewritten.
Layer 3: merge on signup AND on signin
This is the layer most setups skip, and skipping it is where 30% of cross-device attribution dies.
The standard pattern is "merge anon → user on signup." A user signs up, we copy their attribution from anon_visitors to a user row, done. But what about the user who signs up on Tuesday from their laptop, then signs in on Saturday from their phone after clicking a different ad? The Saturday device has its own as_anon_id cookie, its own anon_visitors row with the Saturday gclid. If you don't merge on signin, that gclid disappears. Your conversion attributes to direct traffic.
Our merge RPC handles both:
create or replace function public.merge_anon_to_user(
p_anon_id text,
p_user_id uuid,
p_is_signup boolean
)
returns void
language plpgsql
security definer
as $$
declare
v_anon record;
begin
select * into v_anon from anon_visitors where anon_id = p_anon_id;
if not found then
-- No anon attribution to merge — still ensure user_attribution has a row.
insert into user_attribution (user_id) values (p_user_id)
on conflict (user_id) do nothing;
return;
end if;
-- Upsert with preserve-first-touch semantics.
insert into user_attribution (user_id, first_touch, last_touch)
values (p_user_id, v_anon.first_touch, v_anon.last_touch)
on conflict (user_id) do update set
first_touch = coalesce(user_attribution.first_touch, excluded.first_touch),
last_touch = case
when excluded.last_touch is null or excluded.last_touch = '{}'::jsonb
then user_attribution.last_touch
else excluded.last_touch
end,
updated_at = now();
-- Mark anon as merged (lets us detect re-merges later).
update anon_visitors
set merged_user_id = p_user_id, merged_at = now()
where anon_id = p_anon_id;
-- Backfill orphaned events with this anon_id.
update events
set user_id = p_user_id
where anon_id = p_anon_id and user_id is null;
end;
$$;
Two details worth pointing out.
coalesce(user_attribution.first_touch, excluded.first_touch) is the preserve-first-touch trick. If we already have a first_touch recorded for this user (from an earlier merge), keep it. The very first attributed visit wins forever; no later signin can overwrite it. This is the rule for the column that earns campaigns their credit.
The update events at the bottom is the move most homegrown attribution skips. When a user signs up, we don't just record their first_touch — we walk back and stamp user_id onto every landing_viewed, demo_started, checkout_started event that fired before they signed up. Those events become attributable retroactively. Without this, your funnel from "ad click" to "purchase" has a black hole in the middle, and you have to invent it later with anon-cookie joins that work poorly. Stamping user_id at merge time turns the events table into something you can query directly: select count(*) from events where user_id = ? group by event is now the user's full journey including the pre-signup stuff.
Layer 4: hashed phone and email to ad platforms
We store users.phone for OTP auth. On every Tier-1 conversion fired to Meta CAPI or Google Ads Enhanced Conversions, we compute sha256(normalize(phone)) and ship it as the ph field. Plus IP, UA, fbp, fbc, click IDs from users.last_touch.
This is the layer where cross-device attribution actually happens. We are not solving cross-device on our side. We are giving Meta and Google enough hashed identifiers that they can match against their logged-in graph — the one with every device this person has used Facebook or Gmail on for the last 10 years.
Phone-OTP auth is an attribution superpower because of this. Most apps barely collect phone numbers. We require one to sign up. Every conversion fires with sha256(phone) attached. Match rates jump from "unreliable" to "very reliable."
Layer 5: Stripe email backfill at first purchase
Most users never give us their email. They sign up with phone, they pay with Apple Pay, we never see the email address. Stripe sees it (because Stripe's checkout collects it as a receipt destination), and Stripe's webhook hands it to us on checkout.session.completed.
We capture it in an atomic RPC that doubles as the first-purchase detector:
create or replace function public.set_first_purchase(
p_user_id uuid,
p_email text
)
returns boolean -- true if this was the first purchase
language plpgsql
security definer
as $$
declare
v_was_first boolean;
begin
select first_purchase_at is null into v_was_first
from user_attribution where user_id = p_user_id;
v_was_first := coalesce(v_was_first, true);
-- Both first_purchase_at and email_from_stripe are write-once.
insert into user_attribution (user_id, first_purchase_at, email_from_stripe)
values (p_user_id, now(), p_email)
on conflict (user_id) do update set
first_purchase_at = coalesce(user_attribution.first_purchase_at, excluded.first_purchase_at),
email_from_stripe = coalesce(user_attribution.email_from_stripe, excluded.email_from_stripe),
updated_at = now();
return v_was_first;
end;
$$;
Now every conversion fired after the first purchase carries both ph and em. Match rates go up again. Free upgrade — we did no extra work, the email was already in Stripe's payload.
Where most people get it wrong
We shipped this in five phases over a week, and we caught three subtle bugs in code review before any of them hit prod. They're the kind of bug you only notice if you're looking carefully, and any team building this would probably ship at least one of them.
Bug 1: the parallel-cookie race
Our landing-page boot component originally fired two requests in parallel:
useEffect(() => {
captureAttribution(); // POST /api/anon-touch
track("landing_viewed", { path: window.location.pathname }); // POST /api/events
}, []);
Both endpoints, on first visit, do "read as_anon_id cookie; if missing, mint a UUID and set it in the response." Browsers serialize cookie writes with last-writer-wins semantics. So roughly half the time, the user's first visit ended with two different anon IDs minted, the cookie set to whichever response came back last, and the anon_visitors row keyed on a different anon ID than the events rows.
Symptom: a real user's first session shows up in the dashboard as two anonymous people. Their first landing_viewed is orphaned from their anon_visitors row. The merge on signup later glues one half of the journey to the user; the other half stays orphan forever.
Fix (commit 1e4647e): sequentialize the touch capture before the event fire.
useEffect(() => {
let cancelled = false;
(async () => {
await captureAttribution();
if (cancelled) return;
await track("landing_viewed", { path: window.location.pathname });
})();
return () => { cancelled = true; };
}, []);
The first request mints the cookie and waits for the response. The second request sees the cookie already set and reuses the value. One anon ID, all events keyed off it. The cancelled flag prevents a fast unmount from firing a stale event.
The lesson is general: any time two independent endpoints race to mint the same cookie, you have this bug. The cure is "make exactly one endpoint authoritative for the cookie write" or "sequentialize the calls so the second one sees the first one's response." We chose the latter because it's local to the boot component and doesn't require restructuring the API surface.
Bug 2: the IdentifyOnMount silent drop
We added a small client component to call posthog.identify(user.id) once the authenticated app shell mounts:
"use client";
import { useEffect } from "react";
import { identifyUser } from "@/lib/events/pixels/identify";
export function IdentifyOnMount({ userId }: { userId: string }) {
useEffect(() => { identifyUser(userId); }, [userId]);
return null;
}
And inside identifyUser we had what looked like a defensive guard:
export function identifyUser(userId: string) {
if (typeof window === "undefined") return;
if (!(posthog as { __loaded?: boolean }).__loaded) return; // <-- the bug
posthog.identify(userId);
}
The intent was: "if PostHog isn't initialized yet, don't try to identify." Reasonable defensive coding. Subtly catastrophic.
React commits effects descendants-first. IdentifyOnMount is deep in the tree; the EventsProvider that runs posthog.init(...) is at the root. So on every mount, IdentifyOnMount's effect fires before EventsProvider's init effect. posthog.__loaded is always false at that moment. Every identify call gets silently dropped. PostHog's UI happily shows authenticated users as "anonymous person" forever.
Fix (commit 1bc904f): drop the guard entirely. posthog-js already queues calls made before init; that's the documented contract. Our extra "defensive" check was overriding the SDK's own buffer.
The general lesson: when an SDK documents that it queues calls pre-init, don't add a layer of "but only if loaded" on top. Read the SDK; trust it where it earns trust. The "obvious" defensive check was the bug.
Bug 3: server distinct_id ≠ browser distinct_id
This one hit during the same review. PostHog's mental model is "one Person per distinct_id." Server-side fanout fires events with distinct_id = anon_id (the cookie value). The browser SDK, when initialized, generates its own random anon distinct_id and stores it in localStorage.
Same human, two PostHog People. They never reconcile, even after posthog.identify(user.id), because identify only merges the SDK's own current anon distinct_id into the user — it has no idea about the server-stamped events.
Fix (same commit 1bc904f): bootstrap the SDK with the cookie value as its starting distinct_id.
const asAnonId = readAsAnonIdCookie();
posthog.init(key, {
api_host: "/e",
// ...
...(asAnonId ? { bootstrap: { distinctID: asAnonId } } : {}),
});
Now the server side and browser side agree on the anon distinct_id from page-load zero. When identify(user.id) runs later, PostHog correctly merges the unified anon Person into the identified user. One Person per human.
The general pattern: any time you fire events from both server and browser, they need to agree on the distinct_id for a given human. The server can't generate one — it has to read whatever the browser already chose. So the cookie has to be the source of truth, and the browser SDK has to be told to adopt it instead of generating its own.
These three bugs all have the same shape: the obvious-looking implementation works for the first 80% of cases, and silently corrupts the last 20%. Our review pattern is a phase-end integration walkthrough where two engineers sit down and trace one full user journey end-to-end, by hand, against the actual SQL state. Every one of these bugs surfaced that way; none of them surfaced in unit tests.
The result, with receipts
To prove the stitch works end-to-end, we ran the full flow against staging:
- Visit landing page with
?gclid=test_full_flowfrom a fresh browser - Verify
anon_visitorsrow created - Sign up via phone OTP
- Verify the merge fired correctly
The output, with anonymized UUIDs:
Step 1 — anon visit creates the visitor row:
select anon_id, first_touch->>'gclid' as gclid, last_seen_at
from anon_visitors
where anon_id = 'bbbbbbbb-bbbb-bbbb-bbbb-bbbbbbbbbbbb';
anon_id | gclid | last_seen_at
-------------------------------+----------------+-----------------------------
bbbbbbbb-bbbb-bbbb-bbbb-... | test_full_flow | 2026-05-04 12:14:03.118+00
Step 2 — landing event fires before user exists:
select id, event, anon_id, user_id, ts
from events
where anon_id = 'bbbbbbbb-bbbb-bbbb-bbbb-bbbbbbbbbbbb'
order by ts;
id | event | anon_id | user_id | ts
--------------------------------------+-----------------+----------+---------+------
cccccccc-cccc-cccc-cccc-cccccccccccc | landing_viewed | bbbbb... | NULL | 12:14
The user_id is NULL. Nobody is signed in yet. Good.
Step 3 — OTP verify fires, merge runs:
select user_id,
first_touch->>'gclid' as first_gclid,
last_touch->>'gclid' as last_gclid,
first_purchase_at
from user_attribution
where user_id = 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa';
user_id | first_gclid | last_gclid | first_purchase_at
-----------------------------------+----------------+----------------+-------------------
aaaaaaaa-aaaa-aaaa-aaaa-... | test_full_flow | test_full_flow | NULL
first_touch.gclid and last_touch.gclid both equal test_full_flow. The campaign that earned this user is recorded. first_purchase_at is null because they haven't bought yet.
Step 4 — orphan event was retroactively stamped:
select id, event, anon_id, user_id
from events
where anon_id = 'bbbbbbbb-bbbb-bbbb-bbbb-bbbbbbbbbbbb';
id | event | anon_id | user_id
--------------------------------------+-------------------+----------+----------------------------------
cccccccc-cccc-cccc-cccc-cccccccccccc | landing_viewed | bbbbb... | aaaaaaaa-aaaa-aaaa-aaaa-...
dddddddd-dddd-dddd-dddd-dddddddddddd | signup_completed | bbbbb... | aaaaaaaa-aaaa-aaaa-aaaa-...
The landing_viewed event from before signup now has user_id set. The pre-signup journey is attributable. This is the back-fill paying off.
Step 5 — anon row marked merged:
select anon_id, merged_user_id, merged_at
from anon_visitors
where anon_id = 'bbbbbbbb-bbbb-bbbb-bbbb-bbbbbbbbbbbb';
anon_id | merged_user_id | merged_at
--------------+----------------------------------+-----------------------------
bbbbb... | aaaaaaaa-aaaa-aaaa-aaaa-... | 2026-05-04 12:14:47.301+00
The anon row knows it has been merged, so a re-merge later (same cookie, second signin) is detectable and idempotent.
The whole stitch — cookie → visitor row → merge → orphan back-fill — runs in under 100ms inside the OTP-verify route. It is non-fatal on failure (we wrap the merge in a try/catch and log; auth never blocks on attribution).
What we deliberately didn't build
A short list, because shipping is also about saying no:
- TikTok adapter. The interface is in place; the implementation is a one-line stub. We'll write it the day TikTok ads make sense for us, and the registry says it's a half-day of work.
- GTM container. Marketing ops doesn't need self-serve pixel control yet. Adding GTM later is one script tag.
- EU consent banner. We don't currently target EU traffic with paid ads. The schema doesn't yet have a consent column. Adding it is a one-line
alter tablewhenever the EU plan materializes; the wiring is deliberately deferred so we don't ship a half-built GDPR story. - Full data warehouse. Postgres is the warehouse for now. When events crosses ~50M rows we'll add monthly partitions on
ts. Not before. - Predicted-LTV value-based bidding. Needs ~50 conversions/week as a baseline. Revisit when volume justifies.
The shape is intentional. Every line of code we don't ship is a line we don't have to maintain through three pivots.
Closing
Owning the identity layer is the part of the analytics stack you should never let a vendor own for you. PostHog, Meta, and Google are all downstream consumers of our identity glue, not sources of truth. PostHog could disappear tomorrow and our attribution would still work — we'd just point the fanout worker at a different destination and replay the events table.
That replay-ability is the real prize. Vendors come and go. Pricing changes. APIs deprecate. The cookie, the anon_visitors table, the merge RPC, the hashed phone — those are forever, and they're yours.
If you only take one thing from this article: write the merge-on-signin path, not just merge-on-signup. It's the smallest-surface change with the largest attribution impact, and it's the one nobody bothers to build until they've already lost months of cross-device data.