Our updated_at is maintained by the ORM, not the database, and 10 of 25 tables have one

Two helpers at the top of Nakodo's schema file decide how every row in the database records its own age:

const createdAt = () => timestamp({ withTimezone: true }).notNull().defaultNow();
const updatedAt = () =>
  timestamp({ withTimezone: true })
    .notNull()
    .defaultNow()
    .$onUpdate(() => new Date());

They look like a pair. They are not. defaultNow() compiles into the table definition and is executed by Postgres. $onUpdate compiles into nothing at all: it is a JavaScript callback that Drizzle runs while building an UPDATE, adding updated_at = $n to the statement it sends.

You can see the asymmetry in the generated migration, which is the only SQL that exists for these columns:

"created_at" timestamp with time zone DEFAULT now() NOT NULL,
"updated_at" timestamp with time zone DEFAULT now() NOT NULL,

$ grep -il "create trigger\|create function" drizzle/*.sql
$ echo $?
1

No triggers, no functions, 32 migrations. If something other than this codebase writes to the database, updated_at is whatever it was.

Two clocks in one row

created_at is now(), which in Postgres is the start of the current transaction. updated_at is new Date() in the Node process that built the statement. So a row written and then edited inside one transaction gets a created_at from the transaction's opening and an updated_at from however far into the work the edit happened, and the two columns come from two machines' clocks.

Nothing in the app subtracts one from the other, and the retention jobs that do care about ages read one column of one table at a time, so this has never cost me anything. It is still worth knowing which column is which kind of fact: created_at is a database fact, updated_at is an application fact.

The rule is not "any write to this row"

The rule is "any UPDATE that Drizzle's query builder produced". That has two edges, in opposite directions.

Raw SQL does not bump it. There is exactly one raw UPDATE in the codebase, in the retention sweep that forgets addresses on long-closed conversations:

await db.execute(
  sql`update outreach_emails set from_address = null where from_address is not null and thread_id in (select id from outreach_threads where to_email is null)`,
);

That statement is safe, and it is safe by luck rather than by design: outreach_emails is one of the 15 tables with no updated_at column, so there is nothing to miss. Written against a table that has one, the same line would silently change rows that every reader of updated_at would go on believing were untouched.

An update that changes nothing does bump it. $onUpdate fires per statement, not per changed value, so set({ status: "paused" }) on a campaign that is already paused moves updated_at forward. Conditional updates are how the app avoids this where it matters, and they are written for other reasons too, which is a nice coincidence: where status is distinct from ... means the statement matches no rows, so there is nothing to timestamp.

Of the two edges, the second one is noise and the first one is a bug waiting for a motive. The motive would be performance: the day someone replaces one of these updates with hand-written SQL to skip a round trip, something downstream goes quiet.

What goes quiet

The thing that reads these columns is the polling endpoint behind every campaign page. While a campaign is working, the page asks what changed, and the answer is a 16-character hash of a fingerprint:

db.select({
  status: campaigns.status,
  pauseMode: campaigns.pauseMode,
  pauseReason: campaigns.pauseReason,
  enoughLeadsAt: campaigns.enoughLeadsAt,
  updatedAt: campaigns.updatedAt,
  creators: sql`(select json_build_array(count(*), max(cc.updated_at)) from ${campaignChannels} cc where cc.campaign_id = ${campaignId})`,
  threads: sql`(select json_build_array(count(*), max(t.updated_at)) from ${outreachThreads} t where t.campaign_id = ${campaignId})`,
  keywords: sql`(select json_build_array(count(*), count(k.last_searched_at), max(k.last_searched_at)) from ${keywords} k where k.campaign_id = ${campaignId} and k.enabled)`,
})

Count plus max(updated_at) is the pair, because neither half is enough on its own: a count misses an edit, and a maximum misses a delete. Two rows gone and two rows added leaves the count alone, which is the case the maximum covers, and an edit that is then deleted leaves the maximum alone, which is the case the count covers. Together they are wrong only for an edit and a delete in the same poll interval, where the next poll fixes it.

The third subquery is the interesting one. keywords has no updated_at, so the fingerprint cannot be written the same way, and it uses the table's own domain timestamp instead: how many there are, how many have ever been searched, and the latest of those. That line is what a table without updated_at costs: a bespoke fingerprint rather than a copy of the previous line.

The endpoint itself is signed in, so the only thing a stranger can check is that it refuses:

$ curl -s https://nakodo.app/api/campaigns/123e4567-e89b-12d3-a456-426614174000/changes
{"error":"Signed out"}

The 15 tables without one

Having updated_at on 10 of 25 tables is not an accident of which ones got reviewed. The ones without it fall into three groups:

• Append-only. The audit log, sent emails, inbound emails, usage events and notices are facts about moments. A row that changes is a row that was wrong, and updated_at would describe the correction rather than the event.
• A better timestamp already. The job queue has locked_at and finished_at, which say what happened rather than that something did. Creator and place records carry fetched_at and friends, which answer the only question anyone asks of them: how stale is this. The 30-day refresh rule published on our privacy page is enforced against those, not against a generic column.
• Write once. Suppressions are created and never edited, by design, because unsuppressing is a different act with its own record.

That leaves the ten that have it as the ten where something polls, caches or syncs on "did this change", and the honest summary is that updated_at is not a default to sprinkle on every table. It is a contract with a reader. If no reader exists, the column is a lie waiting to be told by the first raw statement someone writes.

The product those tables sit behind is at nakodo.app, and how it works describes the pipeline whose progress that polling endpoint is reporting.