Deleting a campaign keeps its conversations, with no campaign, no address and no emails left in them

The dialog is the ordinary one:

confirm({
  title: "Delete this campaign?",
  text: "This removes the brief, results, conversations and drafts for this campaign. It can't be undone.",
  action: "Delete",
  destructive: true,
  run: () => run(() => deleteCampaignAction(campaignId)),
})

The function behind it is not. Deleting a campaign in Nakodo cannot be a cascade, because some of what hangs off a campaign is a promise made to somebody who is not our customer. If a creator replies "no thanks", we do not write to them again for six months. That promise has to survive the brand deleting the campaign it was made in, otherwise deleting a campaign becomes the way to get around it.

So the whole thing is four statements and a comment that is longer than any of them:

// Deletes a campaign with its results, keywords and queued work. Its
// conversations that reached someone stay, with no campaign, no address and
// no emails: whose they were, who was written to (by channel and hashed
// address), when, and how it ended. That's what keeps the 6-month block after
// a no and the gaps between brands working. Ones still going stop first, and
// ones that never sent anything go. Plan use is in the ledger, so nothing is
// given back.
export async function deleteCampaign(campaignId: string): Promise<void> {
  await db.transaction(async (tx) => {
    const ofCampaign = eq(outreachThreads.campaignId, campaignId);
    // A first email going out right now already has firstSentAt, so it's kept.
    await tx.delete(outreachThreads).where(and(ofCampaign, isNull(outreachThreads.firstSentAt)));
    await tx
      .update(outreachThreads)
      .set({ status: "closed", closedReason: "cancelled", closedAt: new Date() })
      .where(and(ofCampaign, notInArray(outreachThreads.status, ["closed", "handed_off"])));
    const kept = await tx
      .update(outreachThreads)
      .set({ toEmail: null, subject: null, note: null, lastError: null, nextActionAt: null, lockedAt: null })
      .where(ofCampaign)
      .returning({ id: outreachThreads.id });
    if (kept.length > 0) {
      await tx.delete(outreachEmails).where(inArray(outreachEmails.threadId, kept.map((t) => t.id)));
    }
    // The conversations' campaignId goes null with it (the foreign key).
    await tx.delete(campaigns).where(eq(campaigns.id, campaignId));
  });
}

Four statements, in an order where each one depends on the one before.

Statement one: never-sent conversations are not conversations

await tx.delete(outreachThreads).where(and(ofCampaign, isNull(outreachThreads.firstSentAt)));

A thread with no firstSentAt has not reached anybody. It is a draft, a queued first email, something waiting for approval, and nothing out in the world depends on it existing. Those are deleted outright, which is also why the dialog can honestly say "and drafts".

The one-line comment above it is the kind of thing you only write after thinking about the clock:

// A first email going out right now already has firstSentAt, so it's kept.

firstSentAt is stamped when the email is booked, under the account's lock, before the provider is called, and there is a release path that sets it back to null if the send never happened. So a message in flight during this transaction is already on the keep side of the test. Stamping it after the provider returns instead would open a window where a sent email looks like a draft and its record is deleted, which is the sort of bug you find out about from the person who received the email.

Statement two: stop what is still running, and name why

.set({ status: "closed", closedReason: "cancelled", closedAt: new Date() })
.where(and(ofCampaign, notInArray(outreachThreads.status, ["closed", "handed_off"])))

Everything still live is closed with a reason. The notInArray exclusion is two different ideas in one list: a conversation that already closed keeps the reason it closed with, because "they said no" and "the brand deleted the campaign" are different facts and the first is the one that matters later; and handed_off is a success, the point where the brand and the creator were introduced to each other and the automation stepped out. Overwriting either would be rewriting history to make the delete tidier.

Statement three: strip everything readable, keep the fact

.set({ toEmail: null, subject: null, note: null, lastError: null, nextActionAt: null, lockedAt: null })

This is the actual point of the function. What goes is the address, the subject line, the note the brand was shown, the last error and the two scheduling fields that would otherwise have the outreach engine pick up an orphan.

What stays is a row with userId, channelId, emailHash, the status and closed reason, and the timestamps: first sent, last sent, last reply, handed off, closed. That is enough to answer "has this brand already written to this creator, and how did it end", and not enough to write to them again or to show anybody who they were. The address is kept only as a sha256, which is how the suppression rules can test membership without the app holding the value.

The nextActionAt null is the load-bearing one for the system's own health. There is a partial index on it:

index("outreach_threads_due_idx").on(t.nextActionAt).where(sql`${t.nextActionAt} is not null`),

That index is the work queue for conversations. Leaving a due date on a thread whose campaign no longer exists means the engine keeps finding it for ever and has to defend itself against a null campaign on every tick. Clearing the date removes it from the index entirely, which is a nicer kind of safety than a guard clause: the row is no longer a candidate, so no code has to remember to skip it.

Statement four: the emails go

if (kept.length > 0) {
  await tx.delete(outreachEmails).where(inArray(outreachEmails.threadId, kept.map((t) => t.id)));
}

Every message body in the thread, in both directions, deleted. The record of a conversation survives; the conversation does not. The kept.length > 0 guard is there because a campaign with no conversations at all is a common case (see the rollback below), and an in () over an empty list is a statement with nothing to do.

Then the campaign row goes, and the threads' campaignId becomes null on its own:

campaignId: uuid().references(() => campaigns.id, { onDelete: "set null" }), // null once the campaign is deleted

set null rather than cascade, and that single word in the schema is the whole feature. It is also why the order works: the update in statement three can target campaign_id = $1 for all of the campaign's threads right up until the last statement runs.

One pleasant side effect of the null. There is a unique index on (campaign_id, channel_id), so a campaign can only have one conversation per creator. Nulls do not collide in a unique index, so a brand can accumulate any number of tombstones for the same creator across deleted campaigns, which is exactly what you want: the per-campaign rule is per campaign, and the six month rule is enforced somewhere else entirely, off the address hash.

The thing that is not given back

// Plan use is in the ledger, so nothing is given back.

Every plan limit in the app is a sum over a separate ledger table, one row per metered action, not a count of the rows the user can edit:

// Each user's metered actions, one usage_events row per action (or batch).
// The rows are the count: a limit is the sum of a kind's rows since its
// window started, and a refund shrinks or deletes a row.

If allowance were computed as "count the conversations in your campaigns this month", deleting a campaign would refund it, and the cheapest way to get more email would be to delete a campaign every afternoon. Instead the ledger row is keyed to the account, and its campaign is a nullable reference:

campaignId: uuid().references(() => campaigns.id, { onDelete: "set null" }),

Same set null as the conversations, for the same reason. The account's count is untouched by the delete, and only the per-campaign attribution goes, which is reasonable given the campaign no longer exists.

The ledger is not append-only, incidentally: a refund shrinks a row's quantity or removes it, which is a deliberate simplification and the thing I would revisit if we ever had to reconcile usage against an invoice line by line. What matters for deletes is narrower and it holds: anything a user can delete is a bad place to keep the count of what they have used.

The second caller is a rollback

deleteCampaign is called from two places. One is the menu. The other is the creation path:

const outcome = await runCampaignResearch(id, { userId, notes });
if (!outcome.ok) {
  await refund(eventId);
  await deleteCampaign(id);
  await audit({ action: "campaign.deleted", userId, entityType: "campaign", entityId: id, metadata: { domain, reason: "research_failed" } });
  throw new ResearchError(outcome.error);
}

A campaign is created, its research run is spent from the month's allowance, and the website is read inside the request. If that fails, the row has to go, and the run has to be refunded, because the user got nothing for it. Note the shape of those two lines: the refund is a ledger entry of its own rather than a deletion of the original, so the history still says a run was attempted. And deleteCampaign on a campaign that is ten seconds old is four statements that each match zero rows, which is the right amount of special-casing for a rollback: none.

The audit row carries reason: "research_failed", next to the user-initiated deletes which carry only the domain. Same action name, different metadata, because the intention is the same ("this campaign is gone") and the cause is not.

What I would change in the dialog

The function is right and the dialog is almost right. "This removes the brief, results, conversations and drafts for this campaign" is true of everything the brand can see, and it is not the whole truth about what the row looks like afterwards. Our privacy page is explicit where the dialog is not:

It never holds the address or the emails. It counts towards your plan's limits, and keeps our rules on how often anyone is emailed, such as no second try for 6 months after they say no.

A sentence of that in the confirmation would cost nothing and would be the honest version: the brand's copy of the conversation goes, the hashed record of having written stays, and it stays for the benefit of the person who was written to. I would rather a customer read that in the dialog than find it in the privacy page later.

The one other thing I noticed while writing this is that the offer column, which holds what was asked and agreed in the conversation, is not in the strip list. It is the brand's own commercial record rather than anybody else's data, and the thread is the only place it lives, so keeping it is defensible. It was not a decision, though, which is the difference between a design and an omission, and it is now written down as the former.

If you want the product side of all this, how Nakodo works covers what a conversation is for, and the retention clocks in the privacy page list every kind of data with its own deadline, including the 30 days after a conversation ends when the address is removed even if nobody deletes anything.