Nakodo has two screens that count the same rows. The campaigns list shows a card per campaign with found, fit, contacted and replied on it. A campaign's own page shows a funnel: found, fit, waiting, contacted, plus how many results are still being checked and, on the free plan, how many are scored but hidden.
Same words, same table, two completely different queries, because one needs a number per campaign in a list and the other needs six numbers for one campaign. That is the situation where a codebase grows two definitions of "fit" and nobody notices for a month. Our defence is that neither screen contains a definition. Both import the same SQL fragments out of src/lib/queries.ts, and the fragments are functions of a table alias:
const fitSql = (alias: string, cap: number | null, scope: SQL) =>
sql`${shownSql(alias, cap, scope)} and ${sql.raw(fitCondition(alias))}`;
// Fits Nakodo can still write to: a published email, not skipped, and no
// conversation in this campaign yet. Automatic outreach takes them best first.
const waitingSql = (alias: string, cap: number | null, scope: SQL) =>
sql`${shownSql(alias, cap, scope)} and ${sql.raw(
`${usefulCondition(alias)} and not ${alias}.skipped` +
` and not exists (select 1 from outreach_threads t where t.campaign_id = ${alias}.campaign_id and t.channel_id = ${alias}.channel_id)`,
)}`;
The alias parameter is the whole trick, and it is why these are string generators rather than Drizzle expressions built from column objects. The same predicate has to run in three different shapes: inside a correlated subquery where the table is aliased cc, inside a count(*) filter (where ...) on the outer query where it is campaign_channels, and inside the pipeline's own statements where it decides what work to queue. A Drizzle eq(campaignChannels.skipped, false) renders one qualified name and cannot be moved. A function of an alias renders anywhere.
(fitCondition and shownSql are the parts I do not publish. The first is the fit rule, the second is the window function behind the free plan's view cap. Everything in this post works the same whatever is inside them, which is rather the point of having them behind a function.)
The list is one statement with twelve SELECTs
listCampaigns returns one row per campaign with ten counts and collections hanging off it, in one round trip:
found: sql<number>`(select count(*)::int from ${campaignChannels} cc where cc.campaign_id = ${id} and cc.stage in ${CHECKED_STAGES})`,
fit: sql<number>`(select count(*)::int from ${campaignChannels} cc where cc.campaign_id = ${id} and ${sql.raw(fitCondition("cc"))})`,
contacted: contactedSql(id),
replied: sql<number>`(select count(*)::int from ${outreachThreads} t where t.campaign_id = ${id} and t.last_reply_at is not null)`,
conversations: sql<number>`(select count(*)::int from ${outreachThreads} t where t.campaign_id = ${id})`,
needsYou: sql<number>`(select count(*)::int from ${outreachThreads} t where t.campaign_id = ${id} and t.status in ('needs_you', 'review'))`,
Plus the five best fits as JSON for the contact sheet on each card:
topFits: sql<{ channelId: string; title: string; thumbnailUrl: string | null; score: number }[]>`(
select coalesce(json_agg(t), '[]'::json) from (
select ch.id as "channelId", ch.title, ch.thumbnail_url as "thumbnailUrl", cc.score
from ${campaignChannels} cc join ${channels} ch on ch.id = cc.channel_id
where cc.campaign_id = ${id} and ${fitSql("cc", cap, ofUser(userId))}
order by cc.score desc, ch.subscriber_count desc nulls last
limit 5
) t
)`,
Twelve select keywords in one 53-line function, and a row_number() over (order by created_at) for the catalogue number printed on each card. The reason it is one statement rather than a dozen awaited queries is the connection pool: five slots on Supabase's transaction pooler, shared by every concurrent render. Ten queries per campaign on a list of eight campaigns is eighty acquisitions; one statement is one. Every subquery here is covered by an index on the campaign.
The Drizzle footgun in the middle of it
The first line of the function is a workaround, and the comment is longer than the code:
// On a one-table select Drizzle writes campaigns.id as a bare "id", which
// inside a subquery is the subquery's own table's id. Qualified, it's the
// campaign's.
const id = sql`${campaigns}.id`;
Drizzle omits the table qualifier when a select has a single table. That is a reasonable optimisation for the top level, and it quietly changes meaning the moment you interpolate the column into a subquery that has its own table with an id column. where cc.campaign_id = id is not an error. It compiles, it runs, and it compares campaign_channels.campaign_id to campaign_channels.id, which is a channel row's primary key, so every count comes back zero.
That class of bug is why a correlated subquery written by hand should always carry a hand-qualified outer reference. sql${campaigns}.id`renders"campaigns"."id"` wherever it lands.
The page is six counts in one scan
The campaign page does not want ten numbers for each of eight campaigns, it wants six for one, which is a different query shape: one pass over that campaign's rows with a filtered aggregate each:
`ts
db.select({
fit: sql<number>`count(*) filter (where ${sql.raw(fitCondition("campaign_channels"))})::int`,
// Scored creators the plan hides, and how many of them are fits (see @/lib/shown).
locked: sql<number>`count(*) filter (where ${locked})::int`,
lockedFits: sql<number>`count(*) filter (where ${locked} and ${sql.raw(fitCondition("campaign_channels"))})::int`,
waiting: sql<number>`count(*) filter (where ${waitingSql("campaign_channels", cap, inCampaign(campaignId))})::int`,
checking: sql<number>`count(*) filter (where ${beingCheckedSql("campaign_channels")})::int`,
contacted: contactedSql(campaignId),
})
.from(campaignChannels)
.where(eq(campaignChannels.campaignId, campaignId)),
`
count(*) filter (where ...) is standard SQL and is the single most useful thing I know in this area: six different populations, one index scan, no CASE WHEN arithmetic, no six round trips. It also reads like the English sentence the UI prints, which matters more than it sounds like, because these are the numbers a customer emails us about.
The whole progress read is four statements in a Promise.all: the filtered counts above, a group by stage, a group by type over open jobs, and two keyword counts. One number is deliberately not in SQL:
`ts
const found = (byStage.enriched ?? 0) + (byStage.analyzed ?? 0) + (byStage.scored ?? 0);
`
"Found" is three of the stages added up in TypeScript, because the stage histogram is already on its way back for the progress bar. Asking Postgres for the same thing twice would be the easier code and the worse query.
Three ways a job can belong to a campaign, and a UNION
The fourth statement needs a campaign's open jobs, and jobs name their campaign in three different ways: a column, a campaignId in the payload, or a channelId in the payload that belongs to one of this campaign's results.
`ts
function openJobsOf(campaignId: string) {
const open = sql`${jobs.status} in ('pending', 'running')`;
const select = () => db.select({ id: jobs.id, type: jobs.type, runAfter: jobs.runAfter }).from(jobs);
return union(
select().where(and(eq(jobs.campaignId, campaignId), open)),
select().where(and(sql`${jobs.payload}->>'campaignId' = ${campaignId}`, open)),
select().where(
and(sql`${jobs.payload}->>'channelId' in (select cc.channel_id from ${campaignChannels} cc where cc.campaign_id = ${campaignId})`, open),
),
).as("open_jobs");
}
`
Three selects instead of one WHERE a OR b OR c, for one reason: there are three indexes, and an OR across them gets none of them.
`ts
index("jobs_open_campaign_idx").on(t.campaignId).where(sql`${t.status} in ('pending', 'running')`),
index("jobs_open_payload_campaign_idx").on(sql`(${t.payload}->>'campaignId')`).where(sql`${t.status} in ('pending', 'running')`),
index("jobs_open_payload_channel_idx").on(sql`(${t.payload}->>'channelId')`).where(sql`${t.status} in ('pending', 'running')`),
`
All three are partial, on open jobs only, so they stay small no matter how long the table grows. Each branch of the union hits exactly one of them. UNION rather than UNION ALL is also doing work: a job that names the campaign in a column and in its payload is found twice, and the point of the query is to count it once.
A cache that keys on its arguments, and a comment that cannot be enforced
The campaign header and the results page both render the same numbers, and both are server components in the same render, so the read is wrapped in React's cache:
`ts
// Read once per page render: the campaign header and the results page both
// show it (React.cache). Pass the plan's cap from both so the calls match.
export const campaignProgress = cache(async (campaignId: string, cap: number | null) => {
`
The header does campaignProgress(id, plan.limits.creatorsPerCampaign) and the page does campaignProgress(id, cap) where cap is the same value from the same plan. If either side ever passes something else, nothing breaks and nothing fails a test. The cache simply misses, the four statements run twice, and the page gets slower for a reason no profiler will explain in words.
I know of three ways out of this and I have taken the weakest one on purpose. You can read the plan inside the function, which trades the duplicate query for a plan lookup and makes the function depend on request context instead of its arguments. You can pass a single resolved object and let the cache key on its identity, which moves the same hazard one level up. Or you can leave the argument explicit, keep the function pure, and write the sentence down next to it. Documentation that cannot be enforced is a real cost, and in this case the alternatives cost more. It is a small enough function that the comment and the two call sites fit in one screen each.
The inconsistency I found while writing this
Writing it up is the best code review I get. Look at the working flag on the list query:
`ts
working: sql<boolean>`(
exists (select 1 from ${jobs} j where (j.campaign_id = ${id} or j.payload->>'campaignId' = ${id}::text) and j.status in ('pending', 'running')
and not (${campaigns.enoughLeadsAt} is not null and j.type in ('search', 'social_search', 'place_search')))
or exists (select 1 from ${campaignChannels} cc where cc.campaign_id = ${id} and ${beingCheckedSql("cc")})
)`,
`
That is the OR the union exists to avoid, in the one query that runs for every campaign on the list. It is less bad than it looks, because exists can stop at the first matching row and the partial indexes keep the open-jobs population small, but it is the same question asked the worse way, and the third branch (jobs that name only a channel) is missing entirely, so a campaign whose only open work is a creator read shows as idle on the list while its own page says it is working.
Two numbers on two screens that disagree is the exact failure the shared fragments at the top of this post were written to prevent, and it got in anyway by being a boolean rather than a count. The fix is to reuse openJobsOf as a lateral join, which is a bigger change to the list query than it sounds and is now written down, which is more than it was yesterday.
The numbers these queries produce are the ones named on how Nakodo works, step by step, and the cap argument threaded through every fragment above is the per-plan limit published on the pricing page: it is a number on the free plan and null on the paid ones, which is why its type is number | null everywhere it appears.
Top comments (0)