The YouTube API terms give you 30 days, so our database is built to forget

Nakodo reads public YouTube data to work out which creators fit a brand. The YouTube Data API's terms allow you to cache that data for a limited window, not to keep it forever, and that single sentence shaped the schema more than any feature did.

Here is the constant the rest of this post hangs off:

// YouTube API data may be kept for 30 days. Data still in use is refreshed from
// day 25, which leaves a few days for retries; anything older than 30 days is
// deleted.
const RETENTION_DAYS = 30;

Day 25, not day 29. A refresh needs room to fail: the API has a daily quota that can run out, a run can be killed halfway, and a channel can be temporarily unavailable. Five days of slack means a transient problem does not turn into a deletion of data somebody is actively using.

A separate daily cron does the sweep, and it deletes channels, videos, contacts, searches and derived analysis in one transaction, then writes one audit row:

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

The counts go in the metadata, and the row is only written when something actually happened. A nightly "deleted 0 things" entry is noise that makes the real ones harder to find.

The foreign key we left off on purpose

Here is the interesting consequence. A user drafts a message to a creator. Weeks later that creator's cached YouTube data ages out and gets deleted. What happens to the message?

With the obvious schema, it is deleted too, because channel_id references channels and a cascade is a cascade. The user loses their own outreach history as a side effect of a compliance rule about somebody else's data. So:

// Messages drafted for a creator. The user sends them from their own inbox and
// tracks the status here. channelId has no foreign key so the history survives
// the 30-day purge of YouTube data.

That comment is the whole design rule, which I would state like this: sort your tables by who the data belongs to, and only let a cascade run inside one group. YouTube rows are theirs and expire. Campaigns, drafts, statuses, notes and plan usage are the user's and do not. Referential integrity across that line is a feature until it starts deleting the wrong half.

The cost is honest: a drafted message can name a channel id that no longer has a row, so anything reading messages has to tolerate a missing channel. That is a few lines of handling in exchange for not silently destroying somebody's work.

What never gets written down at all

The cheapest data to protect is the data you do not have. Our comment sampling reads comment text to produce counts, and keeps only the counts:

// Top-level comment texts on a video, most relevant first. Only the text is
// read; author names and channel IDs are never kept. Returns null when
// comments are turned off.
export async function listCommentTexts(meter: QuotaMeter, videoId: string, max = 50): Promise<string[] | null>

The privacy page states the same thing in the language of a promise: "Comment analysis keeps counts only, such as how many comments are questions and which languages they are in. We don't store comment text or who wrote it."

I am not going to describe what the counts feed into, because that is the product. The part worth sharing is the ordering of the decision: we worked out the narrowest thing we could store before we wrote the code, rather than storing the raw text and promising to be careful with it.

The null return is a small thing I would repeat. Comments turned off is a normal state for a channel, not an error, and a function that returns null for "there is nothing to read here" keeps that out of the error path. An empty array would mean something different: comments are on and there are none.

The audit log has no IP addresses

// Who did what and when. Rows reference entities by ID and never hold secrets
// or YouTube data. Deleting a user keeps their entries but drops the link.
export const auditLog = pgTable("audit_log", {
  id: uuid().primaryKey().defaultRandom(),
  userId: uuid().references(() => authUsers.id, { onDelete: "set null" }),
  actor: text({ enum: ["user", "system"] }).notNull(),
  action: text().$type<AuditAction>().notNull(),
  entityType: text({ enum: ["campaign", "channel", "user", "outreach_message"] }),
  entityId: text(),
  metadata: jsonb().$type<Record<string, unknown>>().notNull().default({}),
  createdAt: createdAt(),
});

There is no ip column and no user agent column, because the privacy page says "The log does not record IP addresses" and the honest way to keep that promise is to have nowhere to put one.

onDelete: "set null" is how account deletion and an audit trail coexist. Deleting an account keeps the rows and drops the link, so the history of what happened on the platform survives while stopping being personal data about a specific account. The privacy page says exactly that, and here the schema is the implementation of the sentence.

The writer itself swallows its own failures:

export async function audit(entry: AuditEntry): Promise<void> {
  try {
    await db.insert(auditLog).values({ ...  });
  } catch (e) {
    console.error("audit log write failed", entry.action, e);
  }
}

Auditing must not be able to break the action it is describing. If the log write fails, the plan change still happened and the error goes to the platform logs. The opposite choice, where a failed insert rolls back a successful payment, is worse in every way that matters.

One more ordering detail, in account deletion:

await audit({ action: "account.deleted", userId: user.id, entityType: "user", entityId: user.id });
await db.execute(sql`delete from auth.users where id = ${user.id}`);

The audit row is written before the user row disappears, so userId is still a valid reference at insert time and the set-null does its job a line later. Reverse those two statements and the foreign key rejects your record of the deletion.

And the step before both of them:

try {
  await deleteStripeCustomer(user.id);
} catch (e) {
  console.error("Deleting Stripe customer failed", user.id, e);
  return { error: "We couldn't cancel your paid plan, so nothing was deleted. Try again, or email [e-mail]." };
}

If the billing side cannot be cleaned up, nothing is deleted at all. The failure mode to design against is an account that no longer exists and a subscription that keeps charging a card, which is both a support nightmare and genuinely other people's money.

Export: everything that is the user's, and nothing that is not

The export route returns the account, profile, subscription, usage, unlocks, campaigns with their keywords and shortlists, the drafted messages, and the activity log, as one JSON file. The interesting part is the comment above it:

// Everything stored about the signed-in user, as JSON. YouTube data (channel
// details, videos, contacts) isn't the user's data, so creators appear by
// channel ID only.

A data export is supposed to hand over your data. A brand's export is not the right vehicle for a dump of other people's contact details, so creators appear as channel ids. Those ids are public and resolve on YouTube, so nothing useful is lost.

The export is itself an auditable event:

await audit({ action: "account.exported", userId: user.id, entityType: "user", entityId: user.id });

Separately, the per-campaign CSV export has a line I would put in every exporter I ever write again:

// Cells starting with these can run as formulas in spreadsheet apps; YouTube
// titles are user-written, so prefix them with a quote.
function cell(value: unknown): string {
  let s = value == null ? "" : String(value);
  if (/^[=+\-@\t\r]/.test(s)) s = `'${s}`;
  return /[",\n\r]/.test(s) ? `"${s.replace(/"/g, '""')}"` : s;
}

CSV injection is one of those vulnerabilities that is entirely somebody else's fault and entirely your problem. Every string in that file came from a video title or a channel description, which is to say from a stranger on the internet, and a cell starting with = is a formula the moment a spreadsheet opens it.

Go and check the claim rather than believing it

The claim on the landing page is "No tracking cookies", repeated in the footer. That is the sort of statement that should be verifiable from outside, so here are the two commands.

Ask for the home page and look for cookies:

$ curl -s -D - -o /dev/null https://nakodo.app/ | grep -i set-cookie
$ curl -s -D - -o /dev/null https://nakodo.app/pricing | grep -i set-cookie

Both print nothing. There is no banner on the site because there is nothing to consent to: the only cookies in the product are the sign-in cookies that appear after you sign in, which is the carve-out every cookie law has.

Then look for third-party requests in the markup:

$ curl -s https://nakodo.app/ | grep -o -E 'https?://[a-z0-9.-]+' | sort -u
http://www.w3.org

One host, and it is the SVG namespace in the illustrations, which is never fetched. No analytics script, no tag manager, no session recorder, no font CDN, because the fonts are self-hosted. Open DevTools on the landing page and the Network tab has nothing in it from anybody else.

That is also the honest cost of this choice: we have no product analytics on the marketing site. I know what the server logs know. For this product, at this size, I would rather have the claim.

The summary

A retention limit written into somebody else's terms of service is not a compliance checkbox to add later, it is an input to your schema. It decided which foreign keys we have, which columns do not exist, which table survives a purge, and the order of two statements in the delete-account handler.

The test of whether you have done it properly is whether a stranger can check. The privacy page is the list of promises, and the two curl commands above are two of them being kept.