Tables, Columns, and Types Done Properly
Because Supabase is genuine Postgres, designing tables well means applying real relational database principles — not a simplified, Supabase-specific model.
Every table needs a primary key — Supabase defaults to id uuid primary key default gen_random_uuid(). UUIDs (rather than simple auto-incrementing integers) are Supabase's convention for good reasons: they don't reveal how many rows exist, they're safe to generate client-side before insertion, and they avoid collision issues across distributed systems.
Choose column types deliberately, not just "text for everything." Postgres has real, specific types: text for strings, int4/int8 for integers, numeric for exact decimal values (never float for money), timestamptz for dates/times (always with timezone, to avoid a whole category of bugs), boolean, jsonb for genuinely flexible/nested data. Picking the right type up front catches real errors the database enforces for you.
created_at/updated_at timestamp columns are a near-universal convention worth including on every table. created_at timestamptz default now() records insertion time automatically; updated_at typically needs a trigger (or your application layer) to update on every row modification — small, cheap columns that pay off constantly during debugging and auditing.
NOT NULL and CHECK constraints enforce data integrity at the database level, not just your application code. A NOT NULL constraint on a required field, or a CHECK (price >= 0) constraint, means invalid data literally cannot be inserted — a stronger guarantee than "the application always validates this," which breaks the moment something else writes to the database directly.
Naming conventions matter for a database that will outlive its first few months. Consistent, lowercase, snake_case table and column names (Postgres's own convention) prevent a whole category of "was it userId or user_id" mistakes later.
Why this matters for you
A table designed carelessly early on tends to require a genuinely painful migration later — the concepts in this lesson are cheap to apply now and expensive to retrofit once real data and dependent code exist.
▶️ Before the next lesson
Look at the table you created in the Welcome course through this lesson's lens — does it have a proper primary key, appropriate column types, and created_at/updated_at? You'll refine it in this course.