A Schema Someone Can Read at 2am

The truest test of a schema isn't the design review. It's 2:11am, the pager has gone off, and an engineer who has never touched this table is reading SELECT * FROM subscriptions LIMIT 5 output on a laptop in bed, trying to figure out why renewals stopped. Every column name is either helping that person or lying to them. There is no neutral.

I think about naming this way because I've been that engineer, squinting at a column called flag (of what?), a timestamp called date (of what?), and a boolean called status (a boolean! called status!). Names are the only documentation guaranteed to be present at the moment of need — the wiki is stale, the original author is asleep, but the name is right there in the query result. So let's treat schema naming as the engineering discipline it is, not a bikeshed to rush past.

Consistency is the feature; the specific rules are negotiable

Here's my strongest opinion, held for decades: which convention you pick matters far less than picking exactly one and never deviating. A reader can learn any dialect in five minutes. What breaks them is a schema that speaks three dialects at once — created_at here, creation_date there, ts_created in the table an acquired team brought along. Every inconsistency is a little cognitive tax, and at 2am the taxes compound.

With that said, here is the dialect I recommend — mostly the Rails/Postgres mainstream, because meeting readers' expectations is itself a form of kindness:

Tables: plural nouns for things (users, orders), and for join tables either both names (orders_products) or — better — a real name when the relationship is a thing (enrollments, not courses_users; memberships, not teams_users). The moment a join table wants its own column, it wanted its own name.

Ids: the primary key is id; every reference is <singular>_id (customer_id). When one table references another twice, name the role, not the table: approved_by_id and submitted_by_id, both pointing at users. A schema with two user_id-ish columns and no role names is a riddle, and 2am is a bad time for riddles.

Timestamps and dates: _at suffix for moments (created_at, shipped_at), _on for calendar dates (billed_on), and the prefix is a past-tense verb telling you what happened. Notice the bonus: a well-named nullable timestamp documents its own NULL — shipped_at IS NULL reads as "not shipped yet." That's a column doing documentation duty for free.

Booleans should be predicates

A boolean column is a question with a yes/no answer, so its name should be the question. admin is a noun; is_admin is a question. archive is a verb — is it a command? a flag? — while archived is an answer. The test I use: put the column in a WHERE clause and read it aloud.

WHERE archived            -- "where archived" ✓ reads as a sentence
WHERE email_verified      -- ✓
WHERE has_pending_review  -- ✓
WHERE status              -- ✗ status... is? equals? which one?
WHERE flag                -- ✗ see you in the incident channel

Two refinements. First, name the positive state: disabled = false sends tired brains through double-negative gymnastics ("not disabled... so, enabled?"). Prefer enabled, visible, active. Second, be suspicious of booleans that are secretly timestamps or enums in disguise: verified (when? by what?) often wants to be verified_at, and the third boolean added to a table (is_draft, is_published, is_archived — which combinations are legal?) is almost always a status column being born. Let it be born.

Names that survive joins

Here's a subtler discipline that pays off precisely when queries get interesting. A column name doesn't live only in its table — it lives in every join result and every report built on one. Join users, organizations, and plans, and suddenly you have three columns called name, two called status, and a result set that needs archaeology.

Generic names — name, type, status, data, value, count — are perfectly clear at home and perfectly ambiguous the moment they travel. I'm not saying qualify everything (users.user_name is its own crime, and type also collides with ORM conventions — it's Rails' reserved STI column). I'm saying: when a concept is specific, let the name say so. subscription_status and payment_status can sit in the same join and stay honest. And put units in names wherever a number has them: price_cents, duration_ms, weight_grams, timeout_seconds. Every _cents suffix is an incident that didn't happen.

While we're on legibility-at-a-distance: abbreviations are another compounding tax. qty, amt, usr, dt each save four keystrokes once and cost a lookup forever. Storage for column-name characters is the cheapest resource you will ever buy.

Reviewing names, including the generated kind

Names are nearly free to fix in the PR and monstrous to fix after — renaming a live column is a multi-deploy dance (add, dual-write, backfill, cut over, drop), so review is genuinely the last cheap moment.

Generated migrations deserve a specific look here. AI assistants produce conventionally plausible names — they've read more Rails than any of us — but they mirror your prompt's vocabulary and your codebase's inconsistencies with total confidence. If half your tables say is_active and half say active, the assistant will coin-flip per migration and never flag the drift, because it doesn't feel drift. It also happily invents a synonym when your domain already has a word: you say "customer" in the prompt, the schema says clients, and now the same human is two nouns. The reviewer's job is exactly the one machines are worst at: checking the new names against the whole schema's dialect, not against general plausibility.

# Generated: plausible. Reviewed against this schema's dialect: three fixes.
add_column :subscriptions, :cancel_flag, :boolean   # → :canceled (predicate, positive)
add_column :subscriptions, :renewal_date, :datetime # → :renews_at (_at for datetime)
add_column :subscriptions, :amount, :integer        # → :amount_cents (units!)

The 2am checklist

• One dialect, zero exceptions. Consistency beats any particular convention.
• _at for moments, _on for dates, past-tense verb prefixes; nullable timestamps then document their own NULLs.
• Booleans are predicates, positively named, readable aloud in a WHERE clause. Three booleans on one table are usually a status enum in denial.
• Name for the join, not just the table: specific over generic, roles for repeated references, units on every number that has them.
• Review generated names against the schema's existing dialect — plausible is not the same as consistent.

Write every column name for the tired stranger reading it during an incident. Some night, five years from now, the tired stranger will be you — and past-you will have left the lights on.