Our daily cleanup job refreshes before it deletes, and that order is the only reason the 30 day rule holds

Nakodo stores other people's data under a deadline. The YouTube API terms allow API data to be kept for 30 days unless it is refreshed, so our privacy page makes a promise in public:

YouTube data, including contact details found through it: refreshed or deleted within 30 days. Instagram, TikTok and business data, including contact details: read again when a campaign needs it and it's over 30 days old, and deleted once nothing has used it for 180 days.

That sentence is kept by one function, runMaintenance(), which runs once a day:

{ "path": "/api/cron/maintenance", "schedule": "15 9 * * *" }

// Daily: 30-day retention (refresh or delete YouTube data), re-searches and cleanup.
export const maxDuration = 300;

Five minutes, once a day, six numbered steps in one file. The most important thing about it is not any individual step, it is the order they are in.

Deleting is the default

Four constants decide everything:

const RETENTION_DAYS = 30;
const SOCIAL_RETENTION_DAYS = 180;
const KEEP_JOBS_DAYS = 14;
const KEEP_RUNS_DAYS = 90;

plus two freshness clocks from elsewhere in the pipeline, YOUTUBE_FRESH_DAYS = 25 and SOCIAL_FRESH_DAYS = 30.

The 25 is the interesting number. A cache would refresh on read, when something needs the data. We refresh five days before the deadline, on a schedule, whether anybody has asked or not, because the deadline is not ours to miss and a refresh can fail. Five days is twenty attempts at a daily job, or room for a quota that ran out, an outage, or a channel that briefly 404s. If every attempt fails, the data is deleted at day 30 exactly as promised.

So step 1 queues refreshes and step 2 deletes, in that order, in the same invocation. If you reverse them you have not broken the promise, you have broken the product: the creator you are mid-conversation with is deleted in the morning and found again in the afternoon.

Step 1: three definitions of "worth keeping"

Not everything is worth a refresh, and "in use" turns out to be three different questions:

const inUse = and(
  ne(campaignChannels.stage, "rejected"),
  or(inConversation(sql`(t.status <> 'closed' or t.closed_at > now() - interval '90 days')`), fitInRunningCampaign),
);
const inOpenUse = and(ne(campaignChannels.stage, "rejected"), or(inConversation(sql`t.status <> 'closed'`), fitInRunningCampaign));
const fitAnywhere = and(ne(campaignChannels.stage, "rejected"), gte(campaignChannels.score, FIT_MIN_SCORE));

inUse includes conversations that closed in the last 90 days, because most replies reopen a conversation and a reopened one cannot have stale data in it. inOpenUse is the stricter version, and it is the one used for Instagram and TikTok, whose data has no 30 day ceiling: there is no reason to spend a read on a closed conversation that nothing will ever look at again. fitAnywhere is score-based and ignores whether the campaign is still running, because a creator who fitted once is likely to fit the next campaign in the same niche.

The YouTube refresh query then unions two reasons to keep a row: a published email address of its own, or a campaign_channels row that is inUse or fitAnywhere. Everything else is left to expire.

Step 2: one transaction, seven statements, and two of them are UPDATEs

const purged = await db.transaction(async (tx) => {
  const ch = await tx
    .delete(channels)
    .where(
      or(
        and(isNull(channels.fetchedAt), lt(channels.createdAt, purgeBefore)),
        and(youtube, lt(channels.fetchedAt, purgeBefore)),
        and(ne(channels.platform, "youtube"), lt(channels.fetchedAt, daysAgo(SOCIAL_RETENTION_DAYS))),
      ),
    )
    .returning({ id: channels.id });

Three branches, and the first one is the one people forget: a row that was created and never successfully read has a null fetchedAt, so every comparison against it is null and no amount of lt(fetchedAt, ...) will ever catch it. It is aged on createdAt instead. A failed read must not become immortal.

Deleting a channel takes its videos, contacts and campaign results with it through foreign keys, so the rest of the statements exist for data that outlives its parent or is not a row at all. Two of them are updates, not deletes:

const insights = await tx
  .update(channels)
  .set({ commentInsights: null, commentsAnalyzedAt: null })
  .where(lt(channels.commentsAnalyzedAt, purgeBefore))
  .returning({ id: channels.id });
const rationales = await tx
  .update(campaignChannels)
  .set({ rationale: null, rationaleModel: null, rationaleAt: null })
  .where(and(lt(campaignChannels.rationaleAt, purgeBefore), inArray(campaignChannels.channelId, youtubeIds)))
  .returning({ id: campaignChannels.channelId });

These are derived columns on rows that must survive: the row records that this campaign considered this creator, which is ours, while the written analysis is derived from API data, which is not. Retention at column granularity is unglamorous and it is the only honest way to do it, because "delete the data we got from them" and "delete the row we wrote" are different sentences.

One statement is a deliberate anti-join rather than a cascade:

const listings = await tx
  .delete(places)
  .where(
    and(
      lt(places.fetchedAt, daysAgo(SOCIAL_RETENTION_DAYS)),
      sql`not exists (select 1 from ${channels} c where c.place_id = ${places.id})`,
    ),
  )
  .returning({ id: places.id });

A business listing nothing points at is stale bulk data. One a campaign actually found is in use, and it keeps its listing alive.

Every statement uses .returning() purely to count, and the counts are the whole output of the step. The transaction exists so that a failure halfway through does not leave a channel deleted and its analysis columns still populated somewhere else.

Step 3: our own tables, including the runs that died mid-flight

// 3. Housekeeping for our own tables. A run still "running" after an hour
// was killed mid-way (each invocation is capped at 5 minutes).
await db
  .update(runs)
  .set({ status: "failed", finishedAt: new Date(), error: "Stopped before finishing" })
  .where(and(eq(runs.status, "running"), lt(runs.startedAt, daysAgo(1 / 24))));
const stuck = await failStuckJobs();
for (const job of stuck) await outreachJobGaveUp(job);
await db.delete(jobs).where(and(inArray(jobs.status, ["done", "failed"]), lt(jobs.finishedAt, daysAgo(KEEP_JOBS_DAYS))));
await db.delete(runs).where(and(isNotNull(runs.finishedAt), lt(runs.startedAt, daysAgo(KEEP_RUNS_DAYS))));

daysAgo(1 / 24) is an hour, in a function whose parameter is days, which I would normally call out in review and here I like: it reads as "one twenty-fourth of a day" right next to the comment that explains why an hour is the threshold. A serverless function capped at five minutes cannot possibly still be running after sixty, so a running row that old is a row whose process was killed, and leaving it in that state makes every "is this campaign working" query lie.

The loop over stuck is the part that is not bookkeeping. A job that gave up may be a job that owed somebody an answer, so each one gets a chance to close its conversation properly rather than being quietly marked failed.

Step 4: 150 seconds of the 300, and a tiebreak that rotates daily

The fourth step keeps campaigns moving, and it is the one that can take arbitrarily long, so it gets a budget and a queue order:

const FINDING_FOR_MS = 150_000;
// ...
.orderBy(sql`${campaigns.libraryMatchedAt} asc nulls first`, sql`md5(${campaigns.id}::text || current_date::text)`);

for (const [i, c] of active.entries()) {
  if (Date.now() - started > FINDING_FOR_MS) {
    findingLeft = active.length - i;
    break;
  }

Half the request, no more, and the loop reports how many campaigns it did not reach rather than pretending it finished. The ordering is two terms. The first is least-recently-served, which is the fairness guarantee: a campaign skipped today sorts first tomorrow. The second is the tiebreak for everything that has never been served, and it is md5(id || current_date), a hash that changes every day. Order those by id or created_at and the same campaigns sit at the front of the queue every single morning, for ever. A daily shuffle costs nothing and removes a permanent loser.

The budget also leaves 150 seconds for the steps after it, which is the real reason the number is not 250.

Steps 5 and 6: the order is a product decision

// 5. Emails about the account (src/lib/nudges.ts): a reminder to launch a
// first campaign, and Free's month used up. Before the digest, so its line
// about the plan isn't said twice the same morning.

Two emails about the account, then the day's digest. The digest knows how to mention a used-up plan, and so does the limit email, and sending both in the same minute makes us look like we are not paying attention. One comment and a fixed order solve it. There is no coordination mechanism, no shared flag, just two steps in a sequence where one can see what the other did.

Each of these steps catches its own errors:

const digests = await sendDailyDigest().catch((e) => {
  console.error("Daily digest failed", e);
  return 0;
});

so a failing digest cannot stop the purge that has already happened, nor the widening that has not. The returned object is the cron response body: fourteen fields, thirteen of them plain counts and the fourteenth the purge's own seven.

The gap I am not happy about

Steps 4, 5 and 6 catch. Steps 1, 2 and 3 do not. A thrown error in the purge transaction rolls it back, fails the request, and returns a 500 that nothing in the app notices: there is no runs row for maintenance, the audit row is only written on success, and the next attempt is tomorrow morning.

if (Object.values(purged).some((n) => n > 0)) {
  await audit({ action: "data.purged", metadata: { ...purged, retentionDays: RETENTION_DAYS } });
}

A row that says what was deleted and under which rule, and nothing that says "nothing was deleted because the statement failed". For the one step with a contractual deadline attached, silence is the wrong default, and the five days of slack built into the refresh clock are the only thing currently covering it. What it wants is the inverse of the current code: catch the purge, write the failure, and let the other steps carry on. That is a ten line change and it is next.

The retention windows above are published, not inferred: the privacy page lists each kind of data with its own clock, and how Nakodo works is the pipeline whose leftovers this job tidies up every morning at 09:15 UTC.