0) Prerequisite (UUID generator)

Postgres can generate UUIDs using the pgcrypto extension. Enable it once per database:

-- Enable UUID generator (safe to run multiple times)
CREATE EXTENSION IF NOT EXISTS "pgcrypto";

1) Create the users table

-- Create a basic users table
CREATE TABLE IF NOT EXISTS public.users (
  id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name        TEXT        NOT NULL,
  email       TEXT        NOT NULL UNIQUE,
  created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

What this does (line by line):

• id UUID PRIMARY KEY DEFAULT gen_random_uuid()
  Each user gets a unique identifier (UUID). The PRIMARY KEY guarantees no duplicates. The DEFAULT generates it automatically.

• name TEXT NOT NULL
TEXT = free-form text. NOT NULL means “this field can’t be empty.”

• email TEXT NOT NULL UNIQUE
UNIQUE makes sure two users can’t use the same email.

• created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
  Saves the creation time. TIMESTAMPTZ handles time zones correctly. DEFAULT NOW() fills it in automatically.

2) (Optional) Link to Supabase Auth users

If you’re using Supabase Auth, you can keep a separate profile row tied to the authenticated user’s ID:

-- Create a profiles table linked to auth.users (optional pattern)
CREATE TABLE IF NOT EXISTS public.profiles (
  user_id     UUID PRIMARY KEY REFERENCES auth.users(id) ON DELETE CASCADE,
  name        TEXT        NOT NULL,
  email       TEXT        NOT NULL UNIQUE,
  created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

• REFERENCES auth.users(id) ties the profile to the authenticated user.

• ON DELETE CASCADE removes the profile if the auth user is deleted.

Which one should I use?

• Simple projects / demos: just use public.users.

• Auth-based apps: prefer public.profiles tied to auth.users(id) (cleaner separation of auth vs profile).

3) Test: insert a few rows

Copy-paste to add sample data:

-- Insert sample users (for the simple users table)
INSERT INTO public.users (name, email)
VALUES
  ('Alice Example', 'alice@example.com'),
  ('Bob Builder',   'bob@example.com');

-- Try inserting a duplicate email to see UNIQUE in action (should fail)
-- INSERT INTO public.users (name, email) VALUES ('Dup Email', 'alice@example.com');

4) Read data (verify results)

-- Fetch all users (newest first)
SELECT id, name, email, created_at
FROM public.users
ORDER BY created_at DESC;

You should see your two users. If you try the duplicate email insert (uncomment it), Postgres will block it—this is your UNIQUE constraint working for you.

5) Extend the table: add a profile picture

ALTER TABLE public.users
ADD COLUMN IF NOT EXISTS profile_picture TEXT;

Now you can store a URL (e.g., from Supabase Storage) for each user’s avatar.

6) Useful updates you’ll do often

-- Update a user's name
UPDATE public.users
SET name = 'Alice Example (Updated)'
WHERE email = 'alice@example.com';

-- Change an email (note: must remain unique)
UPDATE public.users
SET email = 'alice@newdomain.com'
WHERE email = 'alice@example.com';

7) Cleanup (if you need to start over)

-- Drop the table (careful: deletes all data)
DROP TABLE IF EXISTS public.users;

Tip: Use IF NOT EXISTS and IF EXISTS to keep scripts re-runnable. It saves you time when iterating.