Nakodo runs outreach campaigns: a brand says what it is after, the app finds creators or local businesses that fit, writes to them, and hands over the ones that say yes. What the app promises to keep and to forget is written out on the privacy page, and most of that promise is enforced by column definitions rather than by code.

So I counted the columns. One file, src/db/schema.ts, 1,266 lines, 25 tables, 318 columns.

columns 318 not null or primary key 171 nullable 147 with a default 106 nullable as a share of all columns: 46.2%

Three facts came out of that count that I did not know before writing the script.

Every default is on a NOT NULL column

The script I used printed a warning line for any column that was both nullable and defaulted. It printed nothing.

$ python3 audit-schema.py | grep "NULLABLE WITH DEFAULT" $ echo $? 1

One hundred and six columns have a default. All 106 are NOT NULL. There are 147 nullable columns and not one of them has a default, so the two mechanisms never overlap anywhere in this database.

I did not design that. It is a consequence of what the two things mean here, and now that it is measured I would defend it as a rule:

  • A default says "this column always has a value, and here is the one to use when the writer does not care". plan defaults to free. status defaults to active. emailsOff defaults to an empty array. Every one of these is a question the row can always answer.
  • NULL says "this is not known yet, and a reader must handle not knowing". launchedAt on a campaign that has never launched. sentAt on an email that has not gone.

A nullable column with a default is the two statements at once: there is a sensible value, and also the absence of one is meaningful. Occasionally that is honest. Mostly it means somebody added a default to make an insert shorter and left the null case in the type, and every reader downstream now has to handle a case the writer never produces.

In Drizzle the distinction is right there in the chain, which is why it is easy to hold:

plan: text({ enum: PLAN_IDS }).notNull().default("free"), launchedAt: timestamp({ withTimezone: true }),

Thirty-seven nullable timestamps against ten booleans

Columns by type, nullable count in brackets:

text 151 (73) timestamp 49 (37) uuid 36 (11) integer 19 (5) createdAt helper 17 (0) jsonb 14 (10) boolean 10 (1) updatedAt helper 9 (0) bigint 6 (6) real 4 (4) doublePrecision 2 (0) date 1 (0)

Ten booleans in a 25-table schema, and 37 nullable timestamps. The schema records when, not whether.

// outreach_threads, abbreviated nextActionAt: timestamp({ withTimezone: true }), lockedAt: timestamp({ withTimezone: true }), approvedAt: timestamp({ withTimezone: true }), firstSentAt: timestamp({ withTimezone: true }), lastSentAt: timestamp({ withTimezone: true }), lastReplyAt: timestamp({ withTimezone: true }), handedOffAt: timestamp({ withTimezone: true }), closedAt: timestamp({ withTimezone: true }),

Every one of those could have been a boolean, and each boolean would have thrown away the only interesting part. approvedAt is not approved: true; it is the instant a person approved, which is what the screen needs in order to say "approved 4 minutes ago", what the scheduler needs in order to decide whether a batch is due, and what an audit answer needs in order to say when. A boolean would have forced a second column beside it within a week.

Two more properties fall out of the choice for free. A timestamp is monotone in practice, so where approved_at is not null and sent_at is null is a work queue with no extra state column. And is null is the only predicate you ever need, so the indexes can be partial. The job table's indexes are mostly partial for exactly this reason, which is its own post.

Nine of the ten booleans are NOT NULL with a default, which is the shape a boolean should have. The tenth is a flag we copy from a platform's own API, and it is nullable because "the platform did not say" and "false" are different facts and only one of them is ours to assert.

The six nullable bigint columns are all counts that came from somebody else's API, and null there never means zero. It means we were not told: the number is hidden on that account, the feature is switched off, or we have not read it yet. Two of those columns carry a one-line comment saying exactly which, because "0 likes" and "likes are hidden" are different sentences on a screen and the type is the only thing keeping them apart.

The most nullable table is the one that is filled in by stages 

channels 33 columns, 28 nullable outreach_threads 29 columns, 19 nullable campaigns 25 columns, 15 nullable outreach_emails 18 columns, 10 nullable places 19 columns, 9 nullable

Twenty-eight of the 33 columns on the creators table are nullable, and that is not sloppiness: a row there starts as an identifier and a name, and is then filled in by several independent steps that run at different times, can fail individually, and must not block each other. Null is "this step has not run or found nothing". The alternative, inserting a row only once everything is known, means holding partial work in memory across minutes of network calls and losing all of it when one step fails.

The cost is paid in TypeScript. Drizzle infers string | null, and a nullable column with 27 friends means every screen that shows the row has to decide what nothing looks like. The app has eleven formatters for that, with four different words for "we do not know", which I wrote about separately and which exist entirely because of this table.

At the other end, exactly one table of the 25 has no nullable column at all: place_imports, five columns, a bookkeeping row written in one shot by one writer. If a table has no nulls it usually means nobody is building it up over time.

Twenty-four foreign keys, and the ten that say set null

This is the part of the audit I would hand to somebody asking what the product does.

profiles id -> authUsers cascade subscriptions userId -> authUsers cascade usageEvents userId -> authUsers cascade usageEvents campaignId -> campaigns set null campaigns userId -> authUsers cascade keywords campaignId -> campaigns cascade searches runId -> runs set null channels placeId -> places set null channelContacts channelId -> channels cascade videos channelId -> channels cascade campaignChannels campaignId -> campaigns cascade campaignChannels channelId -> channels cascade jobs campaignId -> campaigns cascade jobs runId -> runs set null emailPreferences userId -> authUsers set null noticeSends sentBy -> authUsers set null auditLog userId -> authUsers set null outreachThreads userId -> authUsers cascade outreachThreads campaignId -> campaigns set null outreachEmails threadId -> outreachThreads cascade suppressions threadId -> outreachThreads set null supportTickets userId -> authUsers cascade supportTickets campaignId -> campaigns set null supportMessages ticketId -> supportTickets cascade

Fourteen cascade, ten set null. Read the set null rows on their own and they are a list of things that have to outlive their parent:

  • email_preferences.user_id and suppressions.thread_id. If somebody asked us to stop emailing them, that fact cannot depend on the existence of the account that caused the email or the conversation it happened in. Consent belongs to the address. The row survives with the address and loses the account.
  • audit_log.user_id. The log keeps what happened and forgets who, which is the only way an audit trail and a deletion request can both be honoured.
  • usage_events.campaign_id. Plan limits are counted by counting rows rather than by keeping a counter, so a deleted campaign must not refund its usage. The event stays, pointing at nothing.
  • searches.run_id and jobs.run_id. The run log is diagnostics. It can be pruned at any time without touching the work it described.

And then the one pair that is the whole design in two lines:

userId: uuid().references(() => authUsers.id, { onDelete: "cascade" }), campaignId: uuid().references(() => campaigns.id, { onDelete: "set null" }),

Both are on outreach_threads. Delete your account and your conversations go with it. Delete one campaign and its conversations stay, with no campaign, no address and nothing left to send from, because a creator who replied to a real email is owed a real reply even if the campaign that started it is gone. Two clauses, two different answers to "what is a conversation attached to", and the second one took three attempts to get right.

Nine of the 24 keys point at Supabase's auth.users: six cascade and three set null. That split is the account-deletion story, and the fact that it is expressed as six words in a schema rather than as a deletion routine somewhere is the reason I trust it.

What I would change

Nothing in the nullability, which surprised me. One honest finding: places.* and channels.* have nine and 28 nullable columns, and nothing in the schema distinguishes "not fetched yet" from "fetched and absent". Today the pairs of *At timestamps carry that, so the information is there, but it is a convention and not a constraint, and a CHECK that says "if contacts_checked_at is set then this column means absent" is a thing Postgres would happily enforce and we do not ask it to.

The second finding is a measurement I want to keep: 46 per cent nullable is high, and in a pipeline-shaped schema it is the right kind of high. If this were an accounting app I would be worried. It records what has been found out so far, and the honest name for everything else is null.