Lab 6: Model the Data, Build the Database¶

What you will learn¶

• How to go from requirements to an entity-relationship diagram (ERD), and check it for normalization
• How to create real tables in a hosted Postgres database (Supabase) and query them with SQL
• How to enforce who can see which rows with row level security, and prove that it works
• How to call an external API from your server and handle it failing
• How to keep secret keys out of your code

Part A: The data model on paper first (15 minutes)¶

Read Design the Data Model First. Then, from discovery/requirements.md and specs/v1-spec.md, list the nouns your system stores (these become tables) and the facts about each (these become columns).

Ask Claude to draw an ERD, but give it your list rather than letting it guess:

Here are the entities and attributes my system stores: [your list]. Here are the
relationships: [for example, "a member has many payments; a payment belongs to one
member"]. Draw a Mermaid erDiagram in architecture/erd.md with primary keys,
foreign keys, data types, and cardinality. Every table should have a user_id
column that links to the user who owns the row. Then check the design for
first, second, and third normal form and list any problems.

Example of the shape to expect:

```mermaid
erDiagram
  MEMBER ||--o{ PAYMENT : makes
  MEMBER {
    uuid id PK
    uuid user_id FK
    text name
    text email
  }
  PAYMENT {
    uuid id PK
    uuid member_id FK
    uuid user_id FK
    numeric amount
    date paid_on
  }
```

Read the normalization check. Fix the ERD if it found a real problem (for example, a member's name stored again on every payment). Write one sentence for each change.

Part B: Create the database (15 minutes)¶

1. In Supabase, create a new project named <your-project>-dev. Save the database password somewhere safe (a password manager), not in your repo.

2. Ask Claude to write the SQL:

   Write the SQL to create the tables in architecture/erd.md in Supabase Postgres, with uuid primary keys defaulting to gen_random_uuid(), foreign keys, NOT NULL where required, and created_at timestamps. Then write SQL to insert realistic fake seed data: about 5 rows per table. Save it as architecture/schema.sql and architecture/seed.sql. Do not run anything.

3. Read schema.sql before you run it. Then paste it into the Supabase SQL Editor and click Run. Do the same for seed.sql. Open the Table Editor and look at your data.

Part C: Ask the database questions (10 minutes)¶

Write five queries in labs/lab-06/queries.sql and run each in the SQL Editor. Ask Claude for help with syntax, but decide the questions yourself. Following Asking the Database a Question:

1. Every row in your main table, newest first
2. Rows that match a filter (a WHERE clause)
3. A join across two tables
4. A count or sum grouped by something (GROUP BY)
5. One question your real user actually asked you in an interview

Paste each query's result (or a screenshot) under it.

Part D: Row level security and the two-account drill (15 minutes)¶

Read Row Level Security. Then:

1. Ask Claude to write policies so each signed-in user can select, insert, update, and delete only rows where user_id = auth.uid(), for every table. Save them as architecture/policies.sql, read them, and run them in the SQL Editor.
2. In Authentication → Users, add two test users, A and B.

3. Connect your app to the database:

   Connect my Next.js app in app/ to Supabase using @supabase/supabase-js. Add email sign-in. Replace the browser-storage data in v1 with the Supabase tables. Read the URL and publishable key from environment variables in app/.env.local, and make sure .env.local is in .gitignore. Never use the secret key in browser code.

4. Copy the Project URL and publishable (anon) key from Supabase's Project Settings → API into app/.env.local yourself.

5. Run the two-account drill: sign in as A and create a record. Sign in as B and try to see, edit, or delete it through the screens. Record the result in labs/lab-06/rls-test.md.
6. Temporarily disable RLS on one table, repeat the drill, and record what B could see. Then turn RLS back on.

Part E: Call an external API (rest of class, then after)¶

Systems rarely stand alone. Pick a free public API that fits your project. If nothing fits, use Open-Meteo (weather, no key needed) or the Nager.Date public holiday API.

Add a server-side API route in my Next.js app that calls [API] and returns only the fields I need. Show the result somewhere useful in the app. Handle three failures with a clear message to the user: the API is down, it is slow (time out after 5 seconds), and it returns something unexpected.

Test each failure. One way to force one: temporarily change the API URL to a wrong address. In DevTools, find the request your browser made to your route (not to the external API) and record its status code in labs/lab-06/api-test.md. Why does the browser never call the external API directly?

Finally, add your Supabase URL and publishable key as Environment Variables in your Vercel project's settings, redeploy, and check that the live site works.

Reflection¶

Write labs/lab-06/reflection.md:

1. What did normalization change in your design, and what problem would it have caused later?
2. What could user B see with RLS off? Why does hiding a button in the interface not protect data?
3. Which file holds your secret keys, and how do you know it is not on GitHub? (Check github.com.)

Turn in¶

Submit the link to labs/lab-06/ on Canvas. Check that your repo contains:

• architecture/erd.md, schema.sql, seed.sql, policies.sql
• labs/lab-06/queries.sql with results, rls-test.md, api-test.md, reflection.md
• Your app reading and writing Supabase, deployed, with no keys in the repo

How larger systems do this¶

At scale, data modeling is often a dedicated role. Enterprise systems run hundreds or thousands of tables, and changes to them go through migrations: numbered, reviewed scripts that change the schema the same way in every environment, so that development, staging, and production never drift apart. Access rules are set by a security team and audited. External APIs are wrapped in contracts with versioning, rate limits, and monitoring, because when a partner's API goes down, every system that depends on it needs a plan. The schema.sql file you wrote today is the first step toward a migration history.