{"course":{"slug":"building-apps-with-ai","title":"Building Apps with AI","url":"https://addaly.com/learn/building-apps-with-ai","level":"beginner","track":"build"},"lesson":{"slug":"the-columns-every-table-needs","title":"The columns every table needs","url":"https://addaly.com/learn/building-apps-with-ai/the-columns-every-table-needs","position":50,"of":87,"minutes":7,"answer":"A generated schema gives each table exactly the columns the feature mentioned, and the owner, timestamps and deletion marker it left out are the ones the next feature and the first security fix will need.","keyIdea":"A generated schema gives each table exactly the columns the feature mentioned, and the owner, timestamps and deletion marker it left out are the ones the next feature and the first security fix will need."},"body":{"format":"markdown","text":"## The table the model gives you\n\nAsk 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.\n\n## The id\n\nEvery row needs a stable identity that never changes and is never reused. Two choices.\n\n> Figure: The columns the request did not mention. Layers, top to bottom: id: stable, never reused (a UUID, so /bookings/1042 does not invite /bookings/1041); user_id: who owns the row (a real foreign key, indexed, on every table a person writes to); created_at and updated_at (timestamps with a database default, stored in UTC); deleted_at: a soft delete (so a restore is possible and nothing is orphaned); status, fenced by a CHECK (so 'canceled' with one l is refused, not silently created). Each costs one line now and a migration with a backfill in six months. The second one is the column without which no query can be scoped to a person.\n\nAn 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.\n\nA 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.\n\n## Who owns it\n\n`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.\n\nThis 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.\n\nMake 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.\n\n## When\n\n`created_at` and `updated_at`, both timestamps, both set by the database rather than the app:\n\n```sql\ncreated_at TIMESTAMP NOT NULL DEFAULT now(),\nupdated_at TIMESTAMP NOT NULL DEFAULT now()\n```\n\nThe 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.\n\n## Deleted, but not gone\n\nA `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.\n\nThe 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.\n\n## Status as words, with a fence\n\nA `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:\n\n```sql\nstatus TEXT NOT NULL DEFAULT 'pending'\n  CHECK (status IN ('pending', 'confirmed', 'cancelled'))\n```\n\nThe 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.\n\n## Not null, by default\n\nGenerated 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.\n\n## What this looks like in a schema file\n\nIn Drizzle, the four are a pattern you paste into every table:\n\n```typescript\nid: uuid(\"id\").primaryKey().defaultRandom(),\nuserId: uuid(\"user_id\").notNull().references(() => users.id),\ncreatedAt: timestamp(\"created_at\", { withTimezone: true }).notNull().defaultNow(),\nupdatedAt: timestamp(\"updated_at\", { withTimezone: true }).notNull().defaultNow(),\ndeletedAt: timestamp(\"deleted_at\", { withTimezone: true }),\n```\n\nPut 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."},"check":{"question":"A generated bookings table has columns id, customer_name, date and status. What is the first column to add, and why?","options":[{"text":"user_id referencing the account that made the booking, because without it no query can be restricted to the person who is logged in.","correct":true},{"text":"updated_at, so that the admin page can sort by recent changes and the support team can see which bookings were touched most recently and by which process.","correct":false,"tempting":"Useful and cheap, but it is not the column whose absence makes the data unscopable."},{"text":"A price column, since every booking will eventually need one.","correct":false,"tempting":"Guesses at a feature; the ownership column is needed by every feature, including the ones that never involve money."},{"text":"A notes field for free text.","correct":false,"tempting":"Adds a place to put things; it does not make the rows belong to anyone."}]},"prev":{"slug":"the-schema-and-the-migration","title":"The schema, and the migration that changes it","url":"https://addaly.com/learn/building-apps-with-ai/the-schema-and-the-migration"},"next":{"slug":"seed-data-and-the-empty-state","title":"Seed data, and the empty state nobody tested","url":"https://addaly.com/learn/building-apps-with-ai/seed-data-and-the-empty-state"},"authorship":"Founder and series editor: Dr Mustafa Mun. Written by AI models to a written standard. Exam keys checked blind by a second model: 3,665 of 3,666 agreed. Academic review invited.","review":{"status":"Draft, open for academic review"},"cite":"Addaly (2026) 'The columns every table needs', in Building Apps with AI. Edited by M. Mun. Addaly. Available at: https://addaly.com/learn/building-apps-with-ai/the-columns-every-table-needs (Accessed: 13 October 2026).","licence":{"name":"CC BY-NC-SA 4.0","url":"https://creativecommons.org/licenses/by-nc-sa/4.0/"},"reader":{"schema":1,"title":"The columns every table needs","courseTitle":"Building Apps with AI","module":{"index":6,"slug":"the-parts-that-persist","title":"The parts that persist: data, accounts and files"},"answer":null,"keyIdea":"A generated schema gives each table exactly the columns the feature mentioned, and the owner, timestamps and deletion marker it left out are the ones the next feature and the first security fix will need.","hero":{"kind":"figure","figure":{"t":"figure","label":"Figure","figure":{"type":"stack","title":"The columns the request did not mention","layers":[{"label":"id: stable, never reused","detail":"a UUID, so /bookings/1042 does not invite /bookings/1041"},{"label":"user_id: who owns the row","detail":"a real foreign key, indexed, on every table a person writes to"},{"label":"created_at and updated_at","detail":"timestamps with a database default, stored in UTC"},{"label":"deleted_at: a soft delete","detail":"so a restore is possible and nothing is orphaned"},{"label":"status, fenced by a CHECK","detail":"so ‘canceled’ with one l is refused, not silently created"}],"caption":"Each costs one line now and a migration with a backfill in six months. The second one is the column without which no query can be scoped to a person."},"alt":"Figure: The columns the request did not mention. Layers, top to bottom: id: stable, never reused (a UUID, so /bookings/1042 does not invite /bookings/1041); user_id: who owns the row (a real foreign key, indexed, on every table a person writes to); created_at and updated_at (timestamps with a database default, stored in UTC); deleted_at: a soft delete (so a restore is possible and nothing is orphaned); status, fenced by a CHECK (so ‘canceled’ with one l is refused, not silently created). Each costs one line now and a migration with a backfill in six months. The second one is the column without which no query can be scoped to a person."}},"blocks":[{"t":"h2","id":"the-table-the-model-gives-you","runs":[{"text":"The table the model gives you"}]},{"t":"p","runs":[{"text":"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."}]},{"t":"h2","id":"the-id","runs":[{"text":"The id"}]},{"t":"p","runs":[{"text":"Every row needs a stable identity that never changes and is never reused. Two choices."}]},{"t":"p","runs":[{"text":"An auto-incrementing integer (1, 2, 3) is compact and readable in logs. It also leaks information: "},{"text":"/bookings/1042","code":true},{"text":" tells a visitor you have about a thousand bookings, and invites them to try "},{"text":"/bookings/1041","code":true},{"text":". That guess is the entry point for the most common security hole in generated apps, which the security block demonstrates."}]},{"t":"p","runs":[{"text":"A UUID, "},{"text":"9f3c4b2e-...","code":true},{"text":", 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."}]},{"t":"h2","id":"who-owns-it","runs":[{"text":"Who owns it"}]},{"t":"p","runs":[{"text":"user_id","code":true},{"text":", 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."}]},{"t":"p","runs":[{"text":"This is the column without which nothing can be scoped. A query for “my bookings” is "},{"text":"WHERE user_id = ?","code":true},{"text":" 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."}]},{"t":"p","runs":[{"text":"Make it a real foreign key with "},{"text":"REFERENCES users(id)","code":true},{"text":", 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."}]},{"t":"h2","id":"when","runs":[{"text":"When"}]},{"t":"p","runs":[{"text":"created_at","code":true},{"text":" and "},{"text":"updated_at","code":true},{"text":", both timestamps, both set by the database rather than the app:"}]},{"t":"code","lang":"sql","label":"SQL","text":"created_at TIMESTAMP NOT NULL DEFAULT now(),\nupdated_at TIMESTAMP NOT NULL DEFAULT now()"},{"t":"p","runs":[{"text":"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 "},{"text":"timestamptz","code":true},{"text":" 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."}]},{"t":"h2","id":"deleted-but-not-gone","runs":[{"text":"Deleted, but not gone"}]},{"t":"p","runs":[{"text":"A "},{"text":"deleted_at","code":true},{"text":" 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."}]},{"t":"p","runs":[{"text":"The cost is discipline: every query that lists rows must add "},{"text":"WHERE deleted_at IS NULL","code":true},{"text":", 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."}]},{"t":"h2","id":"status-as-words-with-a-fence","runs":[{"text":"Status as words, with a fence"}]},{"t":"p","runs":[{"text":"A "},{"text":"status","code":true},{"text":" column holds a small set of values: "},{"text":"pending","code":true},{"text":", "},{"text":"confirmed","code":true},{"text":", "},{"text":"cancelled","code":true},{"text":". Store them as text, not as numbers that mean something only in code, and fence them:"}]},{"t":"code","lang":"sql","label":"SQL","text":"status TEXT NOT NULL DEFAULT 'pending'\n  CHECK (status IN ('pending', 'confirmed', 'cancelled'))"},{"t":"p","runs":[{"text":"The check means a typo ("},{"text":"canceled","code":true},{"text":" 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."}]},{"t":"h2","id":"not-null-by-default","runs":[{"text":"Not null, by default"}]},{"t":"p","runs":[{"text":"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 "},{"text":"null","code":true},{"text":" as the word “null” on a customer’s screen. Make a column "},{"text":"NOT NULL","code":true},{"text":" unless a row can exist without it, and give it a default where one makes sense."}]},{"t":"h2","id":"what-this-looks-like-in-a-schema-file","runs":[{"text":"What this looks like in a schema file"}]},{"t":"p","runs":[{"text":"In Drizzle, the four are a pattern you paste into every table:"}]},{"t":"code","lang":"typescript","label":"TypeScript","text":"id: uuid(\"id\").primaryKey().defaultRandom(),\nuserId: uuid(\"user_id\").notNull().references(() => users.id),\ncreatedAt: timestamp(\"created_at\", { withTimezone: true }).notNull().defaultNow(),\nupdatedAt: timestamp(\"updated_at\", { withTimezone: true }).notNull().defaultNow(),\ndeletedAt: timestamp(\"deleted_at\", { withTimezone: true }),"},{"t":"p","runs":[{"text":"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."}]}],"check":{"question":[{"text":"A generated bookings table has columns id, customer_name, date and status. What is the first column to add, and why?"}],"options":[{"text":[{"text":"user_id referencing the account that made the booking, because without it no query can be restricted to the person who is logged in."}],"correct":true,"tempting":null},{"text":[{"text":"updated_at, so that the admin page can sort by recent changes and the support team can see which bookings were touched most recently and by which process."}],"correct":false,"tempting":[{"text":"Useful and cheap, but it is not the column whose absence makes the data unscopable."}]},{"text":[{"text":"A price column, since every booking will eventually need one."}],"correct":false,"tempting":[{"text":"Guesses at a feature; the ownership column is needed by every feature, including the ones that never involve money."}]},{"text":[{"text":"A notes field for free text."}],"correct":false,"tempting":[{"text":"Adds a place to put things; it does not make the rows belong to anyone."}]}],"order":[3,0,1,2],"why":null},"prev":{"slug":"the-schema-and-the-migration","title":"The schema, and the migration that changes it","minutes":9},"next":{"slug":"seed-data-and-the-empty-state","title":"Seed data, and the empty state nobody tested","minutes":7}}}