How Pickuma's Affiliate Selection Workflow Scaled Through 2026

Affiliate curation looks simple from the outside: find a tool, grab a referral link, paste it into a post. Run that across forty programs for six months and you discover the actual work — keeping payouts attributable, handling programs that go dark without notice, and not shipping placeholder links to production when an article goes live at 3 a.m. This is how the workflow on pickuma.com evolved through 2026, the patterns we landed on, and the ones we would rebuild differently if we started over.

Two sources of truth, deliberately

Most guides will tell you to never have two sources of truth for the same data. We have two, and we keep them deliberately. The first is src/data/affiliates.ts — a TypeScript constant file checked into git. It contains the slug, display name, tagline, category, program (Reditus, PartnerStack, or direct), and emoji for every tool we promote. The build pulls from it to generate /tools listings, the ToolsMentioned footer on each article, and the per-tool landing pages at /tools/[slug].

The second is the affiliate_links table in Supabase. It holds the actual destination URL — the one with our referral parameter baked in. The Worker at /go/[slug] reads from this table at request time, logs the click into a clicks row, then 302s the visitor onwards.

Splitting the data this way solved two real problems. Affiliate URLs change. A program migrates from Reditus to PartnerStack, a vendor rebrands and the slug for their tracking ID rotates, a network gets acquired and the old domain stops resolving. When that happens, we update one row in Supabase and every /go/<slug> link across the site picks up the new destination — no rebuild, no MDX edits across forty articles. Meanwhile, the TypeScript file is the build-time inventory: it controls what shows up in UI surfaces and is version-controlled so a bad PR can be reverted cleanly. The constraint we enforce by hand is that the two stay synchronized — every slug in affiliates.ts must exist in affiliate_links, or footers break.

Pause, do not delete

The second pattern that emerged through the year was a strict no-delete rule on affiliate inventory. When a program shuts down — and several did in 2026, including one mid-month with no email — the temptation is to remove the row, ship a clean rebuild, and move on. We stopped doing that after the third time we had to reconstruct historical click attribution for an audit.

The current pattern: set status='paused' in Supabase. The /go/[slug] handler returns a 410 Gone for paused slugs, which tells search engines and aggregator bots to drop the link without re-crawling it for weeks. Then we remove the entry from affiliates.ts so the tool stops appearing in /tools listings and article footers, but the row itself stays in the table with full history. Clicks logged against that slug last quarter remain attributable. The redirect handler returns the right status code. The site UI hides the tool.

If a program reactivates — and one did this year, with a new affiliate URL and slightly different terms — we flip status back to active, update the destination URL, and re-add the entry to affiliates.ts. Zero data loss on attribution, no broken historical reports.

UTM discipline as attribution insurance

The UTM tagging convention documented in our project README is not decoration. Every internal surface that links into /go/<slug> carries a different utm_source and utm_campaign combination, and that has paid for itself in every analytics review we have run.

The split that matters:

• Article footer Tools Mentioned → utm_source=article-footer&utm_campaign=tools-mentioned
• Per-tool hub page at /tools/[slug] → utm_source=tool-hub&utm_campaign=<slug>-landing
• Bluesky external embed card → utm_source=bluesky&utm_medium=social
• dev.to body footer link → utm_source=devto&utm_medium=crosspost

When a tool starts paying out, the UTM split tells you whether the conversions are coming from the long-tail evergreen articles, the dedicated tool landing page (which is what we would want), or a single thread on Bluesky that will not repeat. That changes what we invest in next. Without the split, you optimize by vibes.

The one rule we hold strictly: the canonical_url on dev.to cross-posts never gets UTM appended. Canonical has to stay clean for SEO consolidation, and a UTM-tagged canonical confuses Google’s index. The body footer link to the tool inside the dev.to post gets the UTM. The canonical pointing back to pickuma does not.

What we would rebuild

If we were starting over with what we know now, two things would change. First, we would build the Supabase row creation into a CLI from day one rather than relying on hand-edited SQL inserts. Too many slug mismatches landed in the first quarter, each one a broken redirect that took hours to notice. Second, we would track approval-pending status in the database itself, not in Notion. The current split — Notion holds applied-but-not-approved, Supabase holds live — means a tool can sit in approval limbo for weeks without anyone remembering to check on it. A status of pending in the same table that holds active and paused would surface stale applications automatically in any inventory query.

The architecture works. It is the lifecycle around it — application, approval, activation, pause, reactivation — that we underbuilt and are paying down through 2026.