90  90. Project 2: Change the Task App’s Database

Draft: not yet approved

This assignment is a draft for the instructor to review. Dates, weights and requirements may change.

In Project 1 you got the task app from the video running, first on your computer and then online. In this project you change it: you add something real to its database and update the app to use it, with Claude Code’s help. This is the bridge between following a tutorial and building your own app in Project 3: Build Your Own App.

90.1 Goals

By the end of this project you can:

  • read the app’s database design (its tables, columns and relationships) and explain it in plain English;
  • plan a change to it and write that plan down before any code changes;
  • make the change the professional way, with a new migration, and keep the security rules (RLS) correct;
  • review and test what Claude Code builds, and explain every change.

90.2 Start from your working task app

Work in your own task-app repository from Project 1. It must work on your computer before you start. Make a branch for the project (Branches and Merging):

git switch -c project-2

The database today has three tables (see Step 9: Set Up Supabase Database): profiles (one row per user), tasks (each with a user_id that says whose task it is) and usage_tracking. They are created by the files in supabase/migrations/, which run in order: 0_init_profile.sql, 1_init_tasks.sql, and so on up to 7_init_stripe_integration.sql.

90.3 Choose one change

Pick one of these, or propose your own of the same size (ask the instructor first):

Change New database pieces What the app must do
Projects A projects table (name, color); each task can belong to one project (tasks.project_id) Create projects, choose a task’s project, filter tasks by project
Subtasks A subtasks table (title, done), each row belongs to one task Show a task’s checklist, add and tick off subtasks
Comments A task_comments table (text, time), each row belongs to one task Add and show comments on a task
Custom labels A labels table per user, replacing the fixed list (work, personal, …) Create, rename and delete your own labels; use them on tasks

Every option adds at least one new table with a relationship (a column that points at a row in another table, such as subtasks.task_id → tasks.task_id), and every option needs new RLS policies, so that each user sees and changes only their own rows.

90.4 Requirements

  1. Plan first. Before any code changes, write docs/project2-plan.md (ask Claude to draft it with you, then edit it yourself): what the change is, the new tables and columns, the RLS rules in plain English (“a user can read and change only subtasks of their own tasks”), and the screens that change. Commit it.

  2. A new migration. The change goes in a new file in supabase/migrations/, named like the existing ones: 8_add_projects.sql (and 9_... if you need a second one). Never edit the old migration files: they have already run on your Supabase project. Apply it with npx supabase db push.

  3. Security. Turn on RLS for every new table and write its policies. Test them: a second test user must not see the first user’s data.

  4. Updated types. lib/database.types.ts describes the database for the TypeScript code. Regenerate it after db push, from your linked project:

    npx supabase gen types typescript --linked > lib/database.types.ts

    (> writes the command’s output into that file, replacing it.)

  5. The app uses it. The screens in the table above work on your computer, and then on your Vercel site.

  6. Tests. At least one new automated test in tests/integration/ for the new table (for example: create a subtask, read it back, and check that another user can’t read it). Run npm test. (Set up the tests first, as in Step 15: Automated Testing and Troubleshooting.)

  7. Git. Small commits with clear messages on your project-2 branch, then a pull request into main that you merge yourself (Pull Requests and Code Review).

Supabase remembers which migration files it has already run on your project. db push sends only the new ones. If you edit 1_init_tasks.sql, Supabase doesn’t run it again, so your file and your real database no longer match, and anyone who sets up the project from your repository gets a different database from yours. A new migration is a recorded, repeatable step: “from the database as it was, make this change”. That’s how teams change a database that already holds real data.

90.5 Working with Claude Code

Start Claude Code in your project folder as in Following the Tutorial with Claude Code, in Manual mode with Sonnet. A good first prompt:

I want to add [your change] to my task app. First read
supabase/migrations/, lib/database.types.ts and the app code that uses the
tasks table, and explain to me in plain English how the database is set up
now. Don't change any files yet. Then help me write docs/project2-plan.md:
the new tables and columns, the RLS rules in plain English, and the screens
that change. Ask me questions where the choice is mine.

Then implement the plan one piece at a time: the migration, db push, the types, the app code, the tests. After each piece, try it in the app and commit. Read every change Claude proposes before you approve it; you must be able to explain each one in the demo.

90.6 Deliverables

  • The link to your pull request (merged into main), with docs/project2-plan.md, the new migration, the tests and the app changes.
  • Your Vercel site’s address, with the change working online.
  • A short demo (in class or a 3-minute screen recording): the new feature, the new table in the Supabase Table Editor, and proof that a second user can’t see the first user’s data.
  • docs/project2-reflection.md (about 300 words): what you asked Claude to do, one thing it got wrong and how you found out, and what you’d do differently.

90.7 Grading

Part Weight
Plan (docs/project2-plan.md): clear, complete, written before the code 15%
Migration and RLS: new migration only, relationship correct, policies tested 25%
App: the feature works on your computer and online 25%
Tests and types: new test passes, types regenerated 15%
Git: branch, clear commits, pull request 10%
Demo and reflection: you can explain your changes 10%

Practice Questions

Easy

Question 1 · Short answer

What is a migration, and where are the task app’s migrations?

A migration is a file of SQL commands that changes the database’s structure one step at a time (create a table, add a column, add a policy). The task app’s migrations are in supabase/migrations/, and they run in order of their numbers: 0_init_profile.sql, 1_init_tasks.sql, and so on.

Question 2 · True or false

To add a column to the tasks table, you should edit 1_init_tasks.sql, because that’s where the table is created.

False. That migration has already run on your Supabase project, and npx supabase db push doesn’t run it again, so your edit would never reach the real database. Add a new migration (for example 8_add_projects.sql) that changes the table.

Question 3 · Multiple choice

What does npx supabase gen types typescript --linked > lib/database.types.ts do?

  1. Uploads your TypeScript code to Supabase
  2. Creates the new tables in your database
  3. Writes a description of your linked project’s database, for the TypeScript code, into lib/database.types.ts
  4. Runs the automated tests

c. gen types reads the database’s tables and columns and prints matching TypeScript types; > writes that output into the file. It creates no tables (that’s db push, b) and uploads nothing (a).

Medium

Question 4 · Short answer

You choose the Subtasks option. Name the new table, the column that creates the relationship, and what it points at.

A subtasks table with a task_id column that references tasks.task_id: each subtask belongs to exactly one task, and one task can have many subtasks (a one-to-many relationship).

Question 5 · Scenario

Maria’s subtasks work perfectly when she’s signed in as herself. Why isn’t that enough to show her RLS policies are right? How should she test them?

Her own view only shows that she can see her data, not that others can’t. A missing or wrong policy might let every user read every subtask. She should sign in as a second test user (or write an automated test that uses two users) and check that the second user can’t read, change or delete the first user’s subtasks.

Question 6 · Ordering

Put these steps in order: regenerate the types; write the plan; write the new migration; npx supabase db push; update the app’s screens; open a pull request.

  1. Write the plan
  2. Write the new migration
  3. npx supabase db push
  4. Regenerate the types
  5. Update the app’s screens
  6. Open a pull request

The types are generated from the database, so they come after db push; the screens use the new types.

Hard

Question 7 · Long answer

Why does the assignment make you write docs/project2-plan.md before any code changes, when Claude Code could just build the feature?

Writing the plan forces the important decisions (which tables, which relationships, who may see what) to be made by you, on purpose, while they’re cheap to change. Claude can then follow a clear spec instead of guessing, and you can check its work against the plan. Without one, Claude makes those choices silently, and mistakes in the database design or the security rules are expensive to fix once data exists. The plan also helps the reviewer, and you, in the demo.

Question 8 · Scenario

Sam names his new migration 20261016120000_add_comments.sql, the name that npx supabase migration new add_comments would create. What might go wrong, given that the existing files are named 0_... to 7_..., and why does the assignment say to name it 8_add_comments.sql?

Migrations run in the order of their version (the part before the first _). Mixing two naming styles makes that order hard to see and easy to get wrong: compared as text, for example, 20261016120000 comes before 7 (because 2 comes before 7), so the new migration could look older than the last one that already ran, and db push might refuse it. Following the repository’s own numbering (8_..., then 9_...) keeps the order obvious whichever way it’s compared.

Question 9 · Scenario

Claude proposes a policy for task_comments that says using (true). What does that mean, and should Sam approve it?

using (true) means the rule is true for every row, so every signed-in user could read every comment, including other people’s. He should not approve it. The policy should allow only comments on the user’s own tasks, for example by checking that the comment’s task belongs to auth.uid(). This is why you read every change before approving it.

Question 10 · Short answer

After npx supabase db push, Maria’s new projects table exists in the Supabase dashboard, but her TypeScript code shows errors saying that projects doesn’t exist. What did she forget?

She didn’t regenerate lib/database.types.ts. The code’s description of the database still describes the old tables. Running npx supabase gen types typescript --linked > lib/database.types.ts updates it.

Question 11 · Scenario

The feature works on Sam’s computer, but on his Vercel site creating a project fails. His migration is in the repository and the code is deployed. What should he check first?

Whether the migration actually ran on the Supabase project that the Vercel site uses: the site talks to the Supabase project named in its Vercel environment variables. If that’s the same project he pushed to, he should look at the browser console and the Supabase logs for the error (often an RLS policy that blocks the insert). A migration file in Git does nothing until db push applies it.

Question 12 · Long answer

Why does this project use a branch and a pull request, even though you work alone?

main keeps a working version of the app (the one online) while you experiment on project-2. If the change goes badly, you can switch back or delete the branch. The pull request shows the whole change in one diff, so you (and the grader) can review exactly what you and Claude changed before it reaches main. It’s also the workflow teams use, and a protected main (a ruleset) can require it.

© 2025-2026 Y. Rosenthal. All rights reserved.