Our CSV export has 26 columns and not one of them is an email address

Nakodo has two export routes, and they answer different questions.

One is a product feature: a brand wants the creators a campaign found as a spreadsheet. The other is a legal obligation: a person wants everything the app holds about them. They share an auth check and a rate limit, and almost nothing else, including the most important thing, which is who the data belongs to.

The CSV

The route is about 100 lines and the first third of it is refusals:

const user = await getUser();
if (!user) return new Response("Unauthorized", { status: 401 });
const limited = await rateLimitResponse("export", userKey(user.id));
if (limited) return limited;
if (!isUuid(id)) return new Response("Not found", { status: 404 });
const campaign = await db.query.campaigns.findFirst({ where: and(eq(campaigns.id, id), eq(campaigns.userId, user.id)) });
if (!campaign) return new Response("Not found", { status: 404 });
const { limits } = await planForUser(user.id);
if (!limits.export) {
  return Response.redirect(new URL("/settings/plan?limit=export", request.url), 303);
}

Four of those are ordinary. Two are choices.

The UUID check before the query is there because the id comes out of the URL, and a campaign id that is not a UUID makes Postgres raise a type error rather than return no rows. Catching it at the route turns a 500 into a 404. The ownership condition is in the where, not an if after the fetch, so there is no code path on which a row belonging to someone else exists in a variable.

The plan gate is a 303 rather than a 403, because CSV export is on the paid plans and the useful response to "your plan does not include this" is the plans page, not a status code. ?limit=export tells that page which row to point at. A 403 would be more correct and strictly less helpful, and this is a link somebody clicked, not an API call.

Rate limit is 10 a minute per user. An export is one query over a campaign's results; ten of them is somebody holding the button down.

Three bytes of Excel compatibility

The response is where the work is:

return new Response("" + [header.map(cell).join(","), ...lines].join("\r\n"), {
  headers: {
    "Content-Type": "text/csv; charset=utf-8",
    "Content-Disposition": `attachment; filename="${filename}"`,
    "Cache-Control": "no-store",
  },
});

The  is a byte order mark, and it is in there for exactly one reader: Excel on Windows, which ignores charset=utf-8 in the header and will decode a UTF-8 file as the system code page unless the file starts with a BOM. Creator names are full of characters that break under that. A channel called Café becomes Café, and the person who sees it blames your app, correctly.

\r\n is the line ending RFC 4180 specifies. Unix endings work in every tool I have tried, and the spec says CRLF, and there is no cost to being right.

Cache-Control: no-store because the file is a signed in user's data and the URL is guessable by anyone who knows a campaign id.

A cell that starts with = is a program

This is the function I would most like every CSV writer to copy:

// Cells starting with these can run as formulas in spreadsheet apps; titles
// and bios 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;
}

Two separate concerns in five lines, in the right order.

The second half is RFC 4180 quoting: a value holding a quote, comma or newline is wrapped in quotes and its own quotes are doubled. Every CSV writer does this.

The first half is CSV injection, and most do not. A spreadsheet treats a cell beginning with =, +, - or @ as a formula, and formulas in Excel and some other tools can reach outside the document. The strings in this file are channel titles, handles and AI written summaries. None of those are trusted input in the strict sense, but all of them are text that arrived from outside and ends up in a file that opens in a program with a scripting engine. Prefixing a single quote makes the cell render as text.

Note that \t and \r are in that character class too. They are there because some importers strip leading whitespace before deciding whether a cell is a formula, which turns \t=cmd back into a formula after your check has passed.

The quoting has to come second. Prefix first, then quote, and the prefix is inside the quotes where it belongs.

What is not in the 26 columns

The comment above the query is the actual subject of this post:

// Nakodo contacts creators and businesses itself, so their contact details aren't
// exported: only whether an email was found, and how the conversation with
// each is going.

The creator sheet has 26 columns. Number, name, platform, handle, profile URL, fit score, audience size, typical views, engagement rate, country, last upload, whether an email was published, whether the brand ruled them out, any flags, the keywords they turned up under, the one line summary, the score's published parts, and five columns of conversation state: status, when the first email went out, its subject, when they last replied, whether they were introduced.

The email address is not one of them. Nor is the phone number, nor the contact page, nor the link-in-bio. There is one column called "Email published" and its values are yes and no.

This is a product decision before it is a privacy one, and it is written down on the privacy notice because creators are entitled to know it: the app writes to a creator on the brand's behalf, so the brand never needs the address, and a CSV is the one feature that would quietly turn a contact list into a file on somebody's laptop. Taking the column out means a brand cannot export a mailing list, which some brands would like. It also means the sentence on the privacy page stays true, and that sentence is worth more than the column.

What a brand does get is the conversation, which is the part they actually asked for: who was written to, when, and what came back.

Every export writes an audit row with the number of rows, which is the other half of that promise:

await audit({ action: "export.csv", userId: user.id, entityType: "campaign", entityId: id, metadata: { rows: rows.length } });

The other export, and a different owner

/settings/export is the data subject access route: one JSON file, everything the app holds about the signed in user. The header comment is the whole design:

// 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, and conversations without the creator's address or
// anything they wrote: those are theirs.

A subject access request is for your data. A brand's campaign contains a great deal of information about other people, and handing all of it over on request would be the opposite of compliance. So the file includes the campaign, its keywords, its results as channel ids with scores, its conversations, and the emails sent out:

db
  .select({ threadId: outreachEmails.threadId, kind: outreachEmails.kind, status: outreachEmails.status,
            subject: outreachEmails.subject, body: outreachEmails.body, sentAt: outreachEmails.sentAt })
  .from(outreachEmails)
  .innerJoin(outreachThreads, eq(outreachThreads.id, outreachEmails.threadId))
  .where(and(eq(outreachThreads.userId, user.id), eq(outreachEmails.direction, "out")))

direction = "out" is the line that does the work. Outbound email was written for the brand, in the brand's name, and belongs in their export. The creator's reply was written by the creator, and does not.

The whole thing is eight queries in one Promise.all, assembled in memory rather than in SQL, because an export is one request per user per lifetime and readability wins. It also includes the audit log, which is the only table in the file that documents the export itself.

The empty array that is not valid SQL

One helper in that route is worth more than its three lines:

const ids = owned.map((c) => c.id);
const inOwned = (col: PgColumn) => (ids.length ? inArray(col, ids) : sql`false`);

inArray(col, []) is a problem every query builder has to solve somehow, because col in () is not valid SQL. Different libraries pick different answers, and some of them have changed answer between versions. Being explicit costs one ternary and means a brand new account, with no campaigns at all, exports an empty structure instead of erroring, which is precisely the account most likely to try the button.

One rule for both files

An export is a copy of your data that leaves your system and can never be recalled, so the question is never "can we include this field". It is "whose field is it".

For the CSV the answer produced 26 columns and one conspicuous absence. For the JSON it produced a direction filter and channel ids instead of channels. Both fall out of the same sentence, which sits on the method page in words a customer reads before they sign up: creators' addresses are never shown, sold or exported to you, and you get one when a creator agrees to talk and the app introduces you. A column in a spreadsheet is not that moment.