We added saved reports for assessment centre exercises. Attempts were already being stored, so most of that work was reading them back. One of the places that had to change was the one nobody demos:
GET /api/user/data?format=export
The "download everything you hold on me" endpoint. It is not generated, it is not reflective, and it does not iterate the schema. It is a hand-written list of queries and a hand-written serializer, which means every table that joins the product is a diff to it or a quiet omission in it.
The diff is boring and that is the point
The export runs its reads in one Promise.all, and the change was a seventh entry in it plus a seventh key in the output object. Nothing clever:
const [
userRows, sessions, interviews, creditsRows,
transactions, onboardingRows, exerciseAttempts,
] = await Promise.all([ /* ... */ ]);
Two structural choices in there are worth pulling out, because they are what make the list auditable at all.
Most queries name their columns. The five history reads list every column they want rather than selecting the row. That looks like busywork until you realise what the column list is: it is the list of things we are prepared to say are the user's data. A select * export grows silently every time somebody adds a column to a table, and the new column arrives in a customer's download without anyone deciding it should.
The serializer is a second allowlist. The queries return snake_case rows and the output is camelCase, written out field by field. The duplication is real and it is cheap insurance: a column can only appear in the file if it is named in two places. It also backstops the two reads that do select the whole row, the profile and the onboarding answers, where the output shape is the only allowlist there is.
Two things are deliberately not in it
The new block carries a comment that is really a decision record:
// The candidate's answers, written responses and AI feedback. Every
// column except ai_error_message, which is our own diagnostic text
// about a failed scoring call rather than anything about the user.
ai_error_message is what we captured when a review failed. It is about our model call, our timeout and our bug, not about the person. Including it would be worse than useless: it is noise in their file, and it is our internal diagnostics handed out in a format we then have to keep stable.
The second exclusion is the record that we once asked the account for a review. That row exists so the prompt appears once per account and never again, it holds no rating and no comment because we do not collect those at all, and it is the only trace of a question rather than of an answer.
Both of those are defensible and neither is obvious, which is exactly why the reason is written at the call site rather than in a wiki. The next person to touch this file does not have to reconstruct the argument.
The counterpart to that is the deletion branch of the same route, which has its own list and a longer comment, because the two lists are not the same list. Deletion removes the review-prompt record that the export leaves out, and leaves exercise attempts alone that the export includes. A single list for both would be simpler and wrong: what you owe somebody a copy of and what you can safely remove on request are different questions.
The specification is a public page
The thing that keeps this honest is that we publish what we hold. Section 7 of the privacy policy describes assessment centre exercise data in plain English: the answers, the score, and for exercises with written answers the AI review of what was written. That paragraph is a specification the export has to satisfy, and it is the only version of the list that a user can actually read.
So the check after a feature lands is not "did the tests pass", it is: open that page, read the section, and confirm every noun in it appears as a key in the export. If the policy says something the export does not produce, one of the two is wrong. If the export produces something the policy does not mention, the policy is behind.
It is also the reason the audit log entry is written on the way out:
await logAuditAction({
userId,
action: 'data_export',
resourceType: 'all_data',
// ...
});
An access request is itself an event worth having a record of, and that record is the answer to "when did this account last take a copy of everything".
See it
Two of these you can check without an account. The privacy page is the list the export is written against, including the exercise section quoted above, and the rights section names the two controls in account settings, export and delete, rather than asking you to email somebody. The product those exercises belong to is at cogniprep.app/assessment-centre-practice, where one scenario is free to play in full.
With an account the useful exercise is the one we run internally: take your own export, list its top level keys, and read them against that privacy section. A download-everything endpoint is only as good as the last diff somebody remembered to make to it, and the only real test is a human comparing two lists.
The general version
Treat the data export as a surface that every schema change has to be reviewed against, in the same way as a public API. Name every column rather than selecting all of them, write the reason for each exclusion at the point of exclusion, and keep the user-facing description of what you store in a document you can diff the code against. Then the question "is our export complete" has an answer that is checkable rather than hopeful.
Top comments (0)