This column appears, with small variations, six times in MetaMove’s schema:

ts
status: text("status")
  .$type<"active" | "completed" | "paused">()
  .default("active"),

It reads like an enum. TypeScript treats it like an enum. Postgres sees text. The $type call changes what the compiler believes about the column and nothing about the column. I proved it the boring way:

sql
INSERT INTO member_assignments (status) VALUES ('archived');
-- INSERT 0 1

Any script, migration, or hand-typed SQL can put a value in there that the app has no branch for. The $type annotation is a comment with good autocomplete.

Two ways to make it real

A CHECK constraint is a rule attached to the table.

sql
ALTER TABLE member_assignments
  ADD CONSTRAINT member_assignments_status_check
  CHECK (status IN ('active', 'completed', 'paused'));

Postgres won’t even add it while bad rows exist, which is the first useful thing it did for me:

ERROR:  check constraint "member_assignments_status_check" of relation "member_assignments" is violated by some row

After fixing that row, the constraint went on, and the bad value is refused from then on, with the offending row in the message:

ERROR:  new row for relation "member_assignments" violates check constraint "member_assignments_status_check"
DETAIL:  Failing row contains (6649fd99-…, archived).

Changing the allowed set later is a drop and an add. Cheap.

A Postgres enum is a type of its own.

sql
CREATE TYPE assignment_status AS ENUM ('active', 'completed', 'paused');
ERROR:  invalid input value for enum assignment_status: "archived"

It’s stricter, it’s self-documenting in \d, and the values are stored compactly. The cost shows up on change: adding a value is ALTER TYPE … ADD VALUE, but removing or reordering one means creating a new type and migrating the column.

My split: a CHECK for status, because I fully expect to add cancelled and then regret one of the others; a real enum for role, because trainer and member are the two kinds of person the whole app is built around and will not be renamed on a Tuesday. In Drizzle, that’s pgEnum("role", ["trainer", "member"]) for the second, and check() in the table’s extra-config array for the first.

Three kinds of id in one schema

While I was in there I counted my primary keys. Better Auth’s tables use text ids, 32-character random strings. Everything I wrote uses uuid with gen_random_uuid(). And there’s a third kind I hadn’t used at all: bigint GENERATED ALWAYS AS IDENTITY, the plain counter.

 text_id_bytes | uuid_bytes | bigint_bytes
---------------+------------+--------------
            36 |         16 |            8

Size is the least interesting difference. Three things matter more.

Foreign keys must match the parent’s type. member_id is text because user.id is text; workout_plan_id is uuid because plans are. You can mix kinds across a schema as long as each key matches what it points at. What you can’t do is fix Better Auth’s choice from the outside, so those stay text, and every column that points at a user is text too.

Random ids scatter, counters append. A v4 UUID is random, so each new row lands somewhere arbitrary in the primary-key index:

 5420e676-427d-4729-a98f-154a85861bd8
 ecfc10ae-0abc-4f07-89ec-8d28ffa8504a
 f9f5b1a6-6285-4068-9d45-2308a5f21d59

An identity column hands out 1, 2, 3, and new rows go on the end. For a table like progress_entries, which only ever grows and is queried by recency, ordered keys are the better fit. Postgres 18 added a built-in uuidv7() that is time-ordered and still a UUID; on 17, an identity column is the simple ordered option.

Identity columns are guarded. GENERATED ALWAYS means what it says:

sql
INSERT INTO t2 (id, name) VALUES (99, 'c');
ERROR:  cannot insert a non-DEFAULT value into column "id"
DETAIL:  Column "id" is an identity column defined as GENERATED ALWAYS.
HINT:  Use OVERRIDING SYSTEM VALUE to override.

That’s the database refusing to let an import script quietly collide with its own counter, and telling you the exact clause to use if you really mean it.

Where each one belongs

UUIDs earn their place wherever the id is visible: in a URL, in a share link, in an API response. Nobody can guess the next plan’s id or count how many plans exist by walking /plans/1, /plans/2. They can also be generated on the client before the row exists, which some offline-first designs need.

Identity columns belong on internal, high-volume tables where nothing outside the database ever sees the key and inserts should be cheap and ordered. progress_entries is the obvious candidate in MetaMove.

Text ids from a library you don’t control belong exactly where they are. Match them, don’t fight them.

What I’d tell past me

$type<…>() is for the compiler. If the database should refuse a value, write a CHECK or an enum, and let Postgres refuse it in the migration too, because that’s when you find the rows that already broke the rule. Pick key types by who sees the id and how the table grows, and make every foreign key match its parent exactly.

That closes the four-part audit. The schema I’m left with has foreign keys everywhere, one fact per table, deletion rules I can defend, and statuses the database checks. It’s the schema I thought I had written the first time.