Forty-one columns in our schema are enums in TypeScript and plain text in Postgres

Nakodo's schema is 25 tables in one Drizzle file. 41 of its columns are a closed set of values: a campaign's status, a job's status and type, a creator's stage, a support author, a plan id, a platform, a pause mode, a reason a campaign was paused, why no email was found, why a conversation was closed. There is not a single pgEnum in the repository, and 32 migration files contain no CREATE TYPE and exactly one CHECK.

Every one of those columns is this:

status: text({ enum: ["pending", "running", "done", "failed"] })
  .notNull()
  .default("pending"),

text({ enum }) in Drizzle is a TypeScript-only narrowing. What it generates is what you would expect from the name of the function:

CREATE TABLE "support_messages" (
    "id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
    "ticket_id" uuid NOT NULL,
    "author" text NOT NULL,
    "body" text NOT NULL,
    "by_email" boolean DEFAULT false NOT NULL,
    "meta" jsonb,
    "created_at" timestamp with time zone DEFAULT now() NOT NULL
);

"author" text NOT NULL. In the application that column is "user" | "manual" | "assistant" | "operator" | "system". In the database it is a string.

Why

Postgres enum types are a one-way door with a narrow opening.

You can add a value. Since Postgres 12 you can even do it inside a transaction block, though you still cannot use the new value in that same transaction. You can rename a value. You cannot remove one. There is no ALTER TYPE ... DROP VALUE, so getting rid of a value means creating a new type, rewriting every column that uses it, and dropping the old one, for a change that in the application is deleting a string from an array.

And every value change is a migration. That is the part that decided it for us. Here is the whole diff for the day Nakodo stopped being a YouTube-only product and started searching Instagram and TikTok as well, as far as the value list is concerned:

export const PLATFORMS = ["youtube", "instagram", "tiktok", "web"] as const;
export type Platform = (typeof PLATFORMS)[number];

$ grep -l "tiktok\|instagram" drizzle/*.sql | wc -l
0

Not one migration file mentions either platform. Adding them was a code change, reviewed as a code change, deployed with the code that understood them. With an enum type it would have been a migration that has to land strictly before the code, a value that cannot be used in the transaction that adds it, and a rollback story for a type that cannot drop values.

The value lists live with the rules, not with the schema

21 of the 41 columns take a named array imported into the schema file:

import { CAMPAIGN_KINDS } from "../lib/kinds";
import { PAUSE_MODES, PAUSE_REASONS } from "../lib/pause";
import { PLATFORMS } from "../lib/social/platforms";
import { NOTIFICATION_EMAILS } from "../lib/notification-emails";
import { SUPPORT_AUTHORS, SUPPORT_KINDS, SUPPORT_STATUSES, SUPPORT_URGENCIES } from "../lib/support/kinds";

The direction of that dependency is the design. The schema imports from the app, not the other way round, so a set of values sits in the module that owns the behaviour, next to the type guard and the labels and the comment explaining what each value means. Those modules are deliberately import-free:

// What a paused campaign has stopped. [...] No imports, so the schema and
// client components can both use this.
export const PAUSE_MODES = ["finding", "emails", "both"] as const;
export type PauseMode = (typeof PAUSE_MODES)[number];

One array, read by the Postgres column type, the server, a client component and a test. A CREATE TYPE in a migration file cannot be any of those things.

The other 20 are inline literals, for sets that belong to one table and are never named anywhere else: ["auto", "manual"], ["brief", "angle"], ["search", "library"], ["discovered", "enriched", "analyzed", "scored", "rejected"]. If a second file ever needs one of them, that is the moment it earns a name.

Two of the columns are arrays of a set:

platforms: text({ enum: PLATFORMS })
  .array()
  .notNull()
  .default(sql`'{youtube}'::text[]`),

Arrays are where the cost shows up in the SQL you write by hand. A default needs the cast, and so does an update:

const without = sql`array_remove(${profiles.emailsOff}, ${kind}::text)`;

With an enum type those casts would be ::platform and ::notification_email, which is not better, just different.

The one check constraint

The exception is a rule no column type could hold, because it is about three columns at once: only a paused campaign has a pause mode or a pause reason, and it has exactly one of them (a mode when the user paused it, a reason when Nakodo did).

check(
  "campaigns_pause_ck",
  sql`case when ${t.status} = 'paused' then (${t.pauseMode} is null) <> (${t.pauseReason} is null) else ${t.pauseMode} is null and ${t.pauseReason} is null end`,
),

ALTER TABLE "campaigns" ADD CONSTRAINT "campaigns_pause_ck" CHECK (case when "campaigns"."status" = 'paused' then ("campaigns"."pause_mode" is null) <> ("campaigns"."pause_reason" is null) else "campaigns"."pause_mode" is null and "campaigns"."pause_reason" is null end);

<> on two boolean is null tests is exclusive or. That is the only thing in the schema the database refuses on its own, and it is there because it is the only one a type system cannot express at the row level: the invariant spans columns, and TypeScript sees the row only after it has been written.

Which is the honest rule we ended up with. A set of allowed values is a claim the application can enforce at every write. A relationship between columns is not.

What we gave up, with a real example

Postgres will accept 'activ' in campaigns.status. Nothing stops it. Three things make that survivable, and one of them is a hole.

Every write goes through the typed column, so a bad literal is a type error. Inputs from a form or a URL are parsed first, which is where the enum actually gets enforced at runtime:

const outreachMode = z.enum(["auto", "manual"]);

That line in a server action is doing the job CREATE TYPE would have done, one layer further out, where a bad value can produce a sentence for a human instead of a 22P02.

And the hole: a value can leave the TypeScript array while rows still hold it. We have one. outreach_mode arrived with three values and a default:

ALTER TABLE "campaigns" ADD COLUMN "outreach_mode" text DEFAULT 'off' NOT NULL;

Then the third value went away and the default changed:

ALTER TABLE "campaigns" ALTER COLUMN "outreach_mode" SET DEFAULT 'auto';

No statement rewrote existing rows, because with a text column nothing forces you to. So a campaign created before that change can hold 'off', a value the type says cannot exist. An enum type would have made that impossible to leave behind, because the migration to drop the value would have failed until the rows were fixed.

What saves it is the shape of the reads, not a guarantee:

automatic: campaign.outreachMode === "auto",

if (campaign.outreachMode === "auto") after(() => drainOutreach(campaignId));

Comparison against the value that triggers the automatic behaviour, rather than an exhaustive switch. An unknown value falls to the safe side: the campaign behaves as manual, which is a campaign that waits to be told who to email. If those reads had been switch (mode) with the default branch throwing, the stale row would be a 500 instead.

That is the trade in one sentence: typed text moves enum enforcement from the database into the write path, which is cheaper to change and easier to get wrong in exactly one direction, so the reads have to be written as though the column were text, because it is.

Go and look

Most of these value sets surface as plain words on the public pages, which is a decent test of whether a set is worth having:

• How Nakodo works names the stages a creator moves through and the reasons one is set aside before scoring. Those reasons are the rejected stage plus a stored reason string, and the page is the list read back as English.
• The pricing page explains what happens when the free plan's monthly contacts run out: the campaign pauses. That is status = 'paused' with pauseReason = 'outreach_limit', which is the half of the check constraint above that nobody chooses.
• The about page lists the rules about creators' data as numbered claims. Several of them are enum values in the schema, including the one that says anyone who uses a one-click stop is never emailed again, which is a row in a suppressions table with its own small set of kinds.

If you are starting a schema today: use typed text, keep the array in the module that owns the rules, parse at the boundary, and spend your check constraints on the invariants that span columns.