Postgres CamelCase Bug Hid Review Prompts for 57 Days

DEV Community

Our review prompt showed to nobody for 57 days, because the driver camelCases every column a raw query returns

CogniPrep asks a paying customer for a Trustpilot review once, and only once, after they have actually used what they bought. The gate is deliberately small: export const REVIEW_PROMPT_MINIMUMS = {
games: 2,
exercises: 1,
interviews: 1,
} as const;
Any one of those clearing its own bar is enough. It is an OR and not a weighted sum, because somebody who has done one full assessment centre exercise has a formed opinion and making them play a game first would be arbitrary. The counts are measured since the purchase, not since signup. So the gate runs one raw aggregate that counts completed games, exercises and interviews twice each: lifetime, and since a timestamp. That query returned the right numbers. The gate read zero for every customer, every time, from 16 September to 12 November. One client option, two kinds of result Our Postgres client sets a column transform: export const DB_TRANSFORM = {
column: { from: postgres.toCamel, to: postgres.fromCamel },
};
Drizzle's query builder is unaffected by that. It reads results positionally and maps them onto the object shape you asked for, so select({ firstPurchaseAt: usersTable.first_purchase_at }) gives you what it says. db.execute is a different path. It hands back postgres.js's own row objects, and the transform has already rewritten every column name on them. A plain AS games_since arrives as gamesSince. The gate was reading row['games_since']. That is undefined, and Number(undefined ?? 0) is 0. Why nothing went wrong loudly Single-word aliases are not affected by a camelCase transform, which is what made this so quiet. The same query selects paid, games, exercises and interviews, and all four of those arrived under the names the code expected. So: hasPaid was correct. We identified customers fine. The lifetime counts were correct. They are what the prompt's own copy and analytics use. Only the three "since purchase" counts and first_purchase_at broke, and those are exactly the four values the decision is made on. Three counts of 0 against a bar of 2, 1 and 1 is three false comparisons ORed together. The function returned needsReviewPrompt: false and did it in good faith. There is no error to log, no row to find, no exception, no Sentry issue. A zero is what a brand new customer legitimately looks like. We had already made this mistake, where it was loud The nurture cron hit the identical thing earlier, selecting AS user_id and reading row.user_id. There it failed immediately and noisily: the undefined user id meant Drizzle substituted DEFAULT on insert, the NOT NULL column rejected it, and a list of undefineds went into an inArray, which failed every campaign in the same run. That one was found and fixed quickly, and the file now carries a long comment explaining the aliases. The difference between the two is not the bug. It is that one of them had a destination that complained. Worth remembering next time you are deciding whether something needs a test: the bug you should fear is not the complicated one, it is the one whose failure mode is a plausible number. The fix is four characters per alias (SELECT count() FROM game_sessions
WHERE user_id = $1
AND completed_at >= $2) AS "gamesSince"
Quoting the camelCase name outright is stable whether or not the transform is configured, which is the property we actually want. There are 15 raw db.execute call sites in this codebase across five files, and a grep for AS [a-z]+[a-z]+ over all of them now returns nothing. The test asserts the SQL, not just the behaviour Behaviour tests would have passed with the bug in place if the fixtures were written in snake_case, because the fixtures would have been wrong in exactly the same way the code was. So the fixture rows are written the way the driver really hands them over: function activityRow(gamesSince: number) {
return {
games: '6',
gamesSince: String(gamesSince),
exercises: '0',
...
};
}
And one test renders the SQL that was actually built and ...

Powered by Readybot.io*