The columns every table needs

The table the model gives you

Ask for “a bookings feature” and the schema that comes back has the columns the sentence mentioned: a name, a date, a status. It is a faithful transcription of the request, and it is missing the four columns that every table in a multi-user app needs, because the request did not mention them. Add them now, at the schema stage, where each costs one line. Add them in six months and each is a migration with a backfill.

The id

Every row needs a stable identity that never changes and is never reused. Two choices.

An auto-incrementing integer (1, 2, 3) is compact and readable in logs. It also leaks information: /bookings/1042 tells a visitor you have about a thousand bookings, and invites them to try /bookings/1041. That guess is the entry point for the most common security hole in generated apps, which the security block demonstrates.

A UUID, 9f3c4b2e-..., is 36 characters, unguessable, and can be generated by the app before the row exists, which is convenient for offline and batch work. Most modern defaults choose it. Either is fine; the point is to choose knowingly, and to never let the guessability of the id be the thing that protects a row.

Who owns it

user_id, a reference to the account that created the row, on every table that holds something a person made. Bookings, uploads, comments, settings, all of it.

This is the column without which nothing can be scoped. A query for “my bookings” is WHERE user_id = ? with the id from the session, and if the column is not there the query cannot be written and the app shows everyone everything. Generated code omits it often, because a single-user prototype does not need it, and the omission is not discovered until the second user signs up.

Make it a real foreign key with REFERENCES users(id), so the database refuses a booking for a user that does not exist, and give it an index, because every query will filter on it.

When

created_at and updated_at, both timestamps, both set by the database rather than the app:

created_at TIMESTAMP NOT NULL DEFAULT now(),
updated_at TIMESTAMP NOT NULL DEFAULT now()

The default means a row inserted by any route, a script, or a migration gets a time whether or not the code remembered. Store times in UTC (the database’s timestamptz type in Postgres) and convert for display. An app that stores local time works until a user in a second time zone appears, or until the clocks change.

Deleted, but not gone

A deleted_at timestamp, null for live rows, instead of actually deleting. Called a soft delete. Three reasons: users ask for things back, other rows may reference the deleted one and would be orphaned by a hard delete, and some records must be retained for legal reasons even when the user removes them from view.

The cost is discipline: every query that lists rows must add WHERE deleted_at IS NULL, and the generated code will forget on the third query it writes. Most ORMs let you set this as a default filter on the table once, which is the way to do it. Genuine deletion, when a user closes their account and asks for erasure, is a separate, deliberate operation, covered in the last block.

Status as words, with a fence

A status column holds a small set of values: pending, confirmed, cancelled. Store them as text, not as numbers that mean something only in code, and fence them:

status TEXT NOT NULL DEFAULT 'pending'
  CHECK (status IN ('pending', 'confirmed', 'cancelled'))

The check means a typo (canceled with one l, written by a model trained on American spelling) is refused by the database rather than silently creating a fourth status that no filter matches.

Not null, by default

Generated schemas make most columns nullable, because nullable never fails an insert. A nullable column is a column that every reader must handle being empty, and JavaScript will happily render null as the word “null” on a customer’s screen. Make a column NOT NULL unless a row can exist without it, and give it a default where one makes sense.

What this looks like in a schema file

In Drizzle, the four are a pattern you paste into every table:

id: uuid("id").primaryKey().defaultRandom(),
userId: uuid("user_id").notNull().references(() => users.id),
createdAt: timestamp("created_at", { withTimezone: true }).notNull().defaultNow(),
updatedAt: timestamp("updated_at", { withTimezone: true }).notNull().defaultNow(),
deletedAt: timestamp("deleted_at", { withTimezone: true }),

Put the pattern in the rules file as the standard shape of a table, and the model will produce it without being asked each time. That is what the rules file is for.