A campaign row with no owner, and the UPDATE that hands it over when somebody signs up

In Nakodo a campaign belongs to a user. Almost:

export const campaigns = pgTable("campaigns", {
  id: uuid().primaryKey().defaultRandom(),
  userId: uuid().references(() => authUsers.id, { onDelete: "cascade" }),
  name: text().notNull(),
  domain: text().notNull(),
  // ...
});

name and domain are notNull. userId is not, and that one missing word is a product feature. It means a
campaign can be researched and even run before the person it is for has an account, which is how I can look at a
brand, see what Nakodo would find for them, and hand the whole thing over if they sign up.

The row is made from a terminal:

$ pnpm research example.com --save

which calls the same function the app calls, with the actor left out:

type Actor = { userId?: string | null };

export async function createCampaign(domainInput: string, opts: Actor & { notes?: string } = {}) {
  const domain = normalizeDomain(domainInput);
  const platforms = await defaultPlatforms(opts.userId, "creators");
  const [row] = await db
    .insert(campaigns)
    .values({ userId: opts.userId ?? null, name: domain, domain, status: "researching", platforms })
    .returning({ id: campaigns.id });
  // ...audit, then research the site
}

opts.userId ?? null is the whole of it. One optional field on an Actor type, and the same code path serves a
signed-in user and a command line.

Claiming is one UPDATE, and the WHERE clause is the lock

export async function claimCampaign(domainInput: string, userId: string, kind: CampaignKind) {
  const domain = normalizeDomain(domainInput);
  const [row] = await db
    .update(campaigns)
    .set({ userId })
    .where(and(eq(campaigns.domain, domain), eq(campaigns.kind, kind), isNull(campaigns.userId)))
    .returning({ id: campaigns.id, status: campaigns.status, launchedAt: campaigns.launchedAt });
  if (!row) return null;
  await audit({ action: "campaign.claimed", userId, entityType: "campaign", entityId: row.id, metadata: { domain } });
  return row;
}

isNull(campaigns.userId) is in the WHERE, not in a SELECT beforehand, which makes this a compare-and-set:
Postgres takes a row lock for the duration of the statement, so if two people sign up for the same domain in the
same second, exactly one UPDATE matches and the other gets an empty returning and a null. No transaction
block, no advisory lock, no read-then-write window. The second person simply creates their own campaign, which is
the correct outcome and needed no code of its own.

The three columns that come back are three decisions, which is why returning asks for them:

const claimed = await claimCampaign(values.domain, user.id, setup.kind);
if (claimed) {
  await enforcePlanLimits(user.id); // its keywords may exceed the plan
  if (claimed.launchedAt === null) {
    return { ok: true, next: claimed.status === "draft" ? `/campaigns/new/${claimed.id}` : `/campaigns/${claimed.id}/brief` };
  }
  const welcome = await finishOnboarding(user.id, { campaignId: claimed.id, claimed: true });
  return { ok: true, next: `/campaigns/${claimed.id}${welcome ? "?welcome=claimed" : ""}` };
}

A claimed campaign that was never launched drops the new owner into the setup flow where they would have been
anyway. One that was already launched sends them to the campaign itself with a different welcome, because what
they are looking at is work that happened before they had an account and the page has to say so. And
enforcePlanLimits runs immediately, because a campaign set up from a terminal has whatever settings I gave it,
and a new account on the Free plan cannot keep all of them.

The comment above the call is the one I would keep if I had to delete the rest:

// A campaign made for this website from the command line is taken over,
// results and queued jobs included, rather than researched again.

Not copied. The same row, so the creators it has already found and the jobs still queued against it belong to the
new owner the moment the UPDATE commits.

The blunt version of the same thing

There is a second path, and comparing them is the interesting part:

if (command === "claim") {
  if (!args[0]) throw new Error("Usage: pnpm pipeline claim <email>");
  const [user] = await db.execute<{ id: string }>(
    sql`select id from auth.users where lower(email) = lower(${args[0]})`,
  );
  if (!user) throw new Error(`No user with email ${args[0]}. Sign up in the app first.`);
  const rows = await db.execute(sql`update campaigns set user_id = ${user.id} where user_id is null returning id`);
  console.log({ claimed: rows.length });
}

Same compare-and-set shape, raw SQL instead of the query builder, no domain and no kind in the WHERE: it gives
one user every ownerless campaign. That is right for what it is for, which is me moving a batch to my own
account. Two things about it I am less happy with. It writes no audit row, where the app's path writes
campaign.claimed with the domain in it, so the history of a row depends on which door it came through. And it
reads auth.users directly with lower(email), which the rest of the app never does, because the only cheap way
to turn an email into a user id outside a session is to go to the table.

Neither is hard to fix: the CLI can call claimCampaign in a loop over the domains it finds, and get the audit
rows for free. It is on the list, and the only reason it is not done is that nothing has needed a claimed
campaign's history yet.

A nullable foreign key with a cascade on it

userId: uuid().references(() => authUsers.id, { onDelete: "cascade" })

Worth reading twice. Delete the user and their campaigns go with them, which is what account deletion should do.
But a row with user_id IS NULL is referenced by nothing, so it cannot be cascaded away by anything: it outlives
every user and only a claim or a hand-written delete removes it. There is also a plain index on the column,
campaigns_user_idx, and Postgres does index NULLs in a B-tree, so "all the ownerless ones" is an index scan
rather than a sequential one. That is a happy accident rather than a design: the index exists to list one user's
campaigns.

And a column that fills itself in after the response

Campaigns researched before Nakodo kept brand logos have no logo. Rather than a migration that goes and fetches
several hundred favicons, the fill happens when a page that would show one is rendered:

// src/app/(app)/campaigns/(list)/page.tsx
after(() => fillLogos(list));

after is Next's hook for work that runs once the response has been sent, so nothing in that fetch is on the
critical path of the page. There are nine after() calls in the app across four files, and two of them are this
one backfill: the campaigns list and a single campaign's layout.

const findingLogos = new Set<string>();

export async function fillLogos(list: { id: string; domain: string; status: string; logoUrl: string | null }[]) {
  const todo = list.filter((c) => c.logoUrl === null && c.status !== "researching" && !findingLogos.has(c.id)).slice(0, 5);
  await Promise.all(
    todo.map(async (c) => {
      findingLogos.add(c.id);
      try {
        const logo = await findSiteLogo(c.domain).catch(() => null);
        await db.update(campaigns).set({ logoUrl: logo ?? "" }).where(and(eq(campaigns.id, c.id), isNull(campaigns.logoUrl)));
      } finally {
        findingLogos.delete(c.id);
      }
    }),
  );
}

Four things in fourteen lines, and one of them does not work as well as it looks:

• logoUrl has three states, not two. null means nobody has looked. "" means somebody looked and the site has no icon. A string means we have one. Writing logo ?? "" is what stops the backfill asking the same dead site on every page view, and it is why the column is text() with a schema comment instead of a boolean beside a URL.
• The isNull in the WHERE again. Research can finish and write a real logo while this fetch is in flight. Narrowing the update to rows that are still null means the slow path cannot overwrite the fresh answer with an empty string. Same trick as the claim, for a different race.
• .slice(0, 5) so a user with twenty old campaigns spreads the work over four page views instead of making one response hold twenty fetches. The next render picks up where this one stopped, because the filter is on the data, not on a cursor.
• The in-flight Set is per-instance. It stops the same campaign being fetched twice by two renders in one process, and on a serverless platform two concurrent requests can easily be two processes, in which case both fetch. The worst case is one wasted favicon request and one UPDATE that matches nothing, so the honest label for that Set is "cheap in one process", not "deduplication".

Two writes, then, that happen to a campaign without anybody pressing anything: an owner arriving, and a logo
turning up. Both are one statement whose WHERE clause does the concurrency control, which is the pattern I keep
coming back to in this codebase: if the condition that makes the write safe can be written in SQL, it does not
need a lock around it.

The sign-up that can trigger the first one is at nakodo.app/login, and what happens
after it is written out at nakodo.app/how-it-works. The ownerless row, you
will have to take my word for.