Lesson 4.2 · 55 minDatabase: Supabase and the leads table

What you will have

You have a Supabase project with a leads table created from a migration file in your repository, one test row visible in the table view, and the connection keys stored in .env.local and in Vercel.

Video

The video for this lesson is not recorded yet.

Prompts used in this lesson

Part 5 — Claude writes the migration

Purpose: create the database table where the website will store leads.

Context: Next.js project at checkpoint lesson-3.9. I have installed the Supabase CLI as a dev dependency, run `supabase init`, logged in and linked the hosted project myself. Read the lead form component from lesson 3.7 to see which fields the visitor fills in and which are required. Read docs/architecture.md.

Done looks like:
- One new migration file in supabase/migrations/, created with `npx supabase migration new create_leads`.
- It creates table public.leads with these columns: id (uuid primary key, generated by the database), created_at (timestamp with time zone, defaults to now), name, email, phone, message, status (text, defaults to 'new', only new/contacted/qualified/won/lost allowed), utm_source, utm_medium, utm_campaign, utm_content, utm_term, gclid, fbclid, referrer, landing_page, ab_variant, score (integer), score_reason.
- Required columns match the required fields of the form. All source and scoring columns are optional and empty by default.
- Row Level Security is enabled on the table, with no policies yet.
- Explicit grants: select, insert, update, delete on the table to service_role only. No grants to anon or authenticated in this lesson.
- docs/architecture.md gets a short "Database" section describing the table.

Constraints: do not run `supabase start` or anything that needs Docker. Do not apply the migration yet. Do not create other tables. Check the current Supabase documentation for the grant and RLS syntax instead of relying on memory.

Before you report: show me the SQL in full and explain each line group in one plain sentence.

Part 7 — Keys, locally and on Vercel

Purpose: connect the Next.js project to the Supabase database and prove the connection works.

Context: the leads table exists and has one test row. I added three variables to .env.local myself and to Vercel: NEXT_PUBLIC_SUPABASE_URL, NEXT_PUBLIC_SUPABASE_PUBLISHABLE_KEY, SUPABASE_SECRET_KEY. Do not open, print or log .env.local or any key value.

Done looks like:
- The official Supabase JavaScript client is installed.
- One server-only helper creates a client with the secret key. It must be impossible to import it into browser code by accident.
- A script `npm run db:check` loads the env file, counts the rows in leads through that helper and prints only the count.
- docs/architecture.md lists the three variable names (names only) and where each may be used. CLAUDE.md gets one line: "Secrets live in .env.local and Vercel; never read or print them."

Constraints: no endpoint or page changes in this lesson. Check current Supabase docs for the client setup.

Before you report: run npm run db:check and the production build and show me the output of both.

Do along

Pause the video where a step says so and do it on your own project.

1. Pause after Part 2. Create your Supabase project; save the database password in a password manager.
2. Pause after Part 4. Run the four CLI commands from Part 4 in your own terminal.
3. Pause after Part 5. Give Claude the migration prompt from Part 5. If your form has an extra field (for example "service type"), add it to the column list.
4. Check the SQL: all columns, five statuses, RLS enabled, grant to service_role only.
5. Run npx supabase db push --dry-run, then npx supabase db push.
6. Pause after Part 6. Insert one test row by hand in the Table Editor.
7. Pause after Part 7. Add the three variables to .env.local and to Vercel.
8. Give Claude the connection prompt from Part 7.
9. Commit, tag and push.

Check your work

1. Open the Table Editor and select leads. Expected: your test row, with id, created_at and status = new filled automatically.
2. Run git log --stat -1. Expected: the commit contains a file in supabase/migrations/.
3. Run npx supabase migration list. Expected: the migration appears in both the local and the remote column.
4. Run npm run db:check. Expected: it prints 1.

Common problems

• The CLI fails to start or reports an unsupported Node version → Node is older than 20 → install a current Node.js release, then repeat.
• db push says the project is not linked → the link step was skipped or run in another folder → run npx supabase link --project-ref <ref> in the project root.
• db:check reports "permission denied for table leads" → the grant to service_role is missing from the migration → tell Claude the exact error and ask for a new migration that adds the grant; push it.

Homework

About 25 minutes. Keep written answers in your own notes, outside the project folder. Do not change the table's columns: later lessons rely on them.

1. Explain your migration. Open your migration file with the "Reading a migration" checklist. Write one plain sentence each for: the columns and their defaults, the rule on status, the Row Level Security line, the grant. Done when: you have four sentences and no line group is left that you cannot explain. Ask Claude about any that you cannot.
2. Field audit for your business. For every contact field your form collects, write what you will do with it. Then open your /privacy page and check that it names each kind of data. Done when: every field has a use or is marked "candidate to drop", and any mismatch with the privacy page is written down. Change nothing in the table or the form now.
3. A second row, and a refused one. In the Table Editor, insert a second row by hand and try to give it a status outside the five stages. The table should not let you save it. Then delete this second row. Done when: you have seen the refusal, and npm run db:check prints 1 again.

Save your work

git add -A
git commit -m "Lesson 4.2: Supabase project and leads table"
git tag lesson-4.2
git push
git push --tags