AN
Teach·Building with Lovable·Lesson 11

Database Basics

Mini-project: a personal notes app with tables, row-level security, and CRUD.

22 min read·7 parts·intermediate

Lesson 10 gave your app a front door. This lesson gives it a filing cabinet — one that knows which drawer belongs to which user. By the end you'll have a personal Notes app where every user only sees their own notes, with create, read, update, and delete all working through the agent.

The database is the pillar that scares beginners the most, and it shouldn't. If you can picture a spreadsheet, you can picture a table. The only new idea is a bouncer that stands over every row and checks who's asking.

Part 1 · What a database actually is

A database, for our purposes, is a set of tables. A table is a spreadsheet with rules: every column has a name and a fixed type (text, number, date, boolean, uuid), every row has a unique id, and the database refuses to accept junk (a number where text is expected, a missing required field, a duplicate id).

Why not just localStorage? Because localStorage lives in one browser on one device. Close that tab in a private window and it's gone. A database lives on the server, is shared across every device the user signs in from, and can be queried, filtered, and joined by other users under rules you control.

One sentence to remember
A database is a spreadsheet with types, a unique key per row, and a bouncer at the door of every row.

Part 2 · Designing the notes table

Before you prompt anything, sketch the columns. Good tables have a small number of well-typed columns and one column that links each row to its owner. Here's the whole schema for our notes app:

Figure 1.1
ColumnTypeDefault / Rule
iduuidgen_random_uuid(), primary key
user_iduuid→ auth.users(id), required
titletextrequired, max ~120 chars
bodytextoptional
created_attimestamptznow()
updated_attimestamptznow(), refreshed on update
The notes table. Every row belongs to exactly one user via the user_id foreign key.

Prompt the agent with the shape, not the SQL:

Create a notes table in Lovable Cloud with id, user_id (linked to the signed-in user), title, body, created_at, and updated_at. Add row-level security so a user can only see and modify their own rows.

Part 3 · Row-Level Security in plain English

Row-Level Security (RLS) is the bouncer. When RLS is on and no policies exist, the table is locked — every read returns zero rows and every write is rejected. Policies are the rules the bouncer follows: "let this user through for this row if …".

Figure 1.2
AnaBenRLSauth.uid()= user_id ?Ana · GroceriesAna · Reading listBen · Trip planAna · Meeting notesBen · IdeasBen · Journal
Two users, one table. The bouncer only lets each user through the rows they own.
  • RLS is on by default for every new table in Cloud — don't turn it off.
  • No policies = no access. An empty list on a fresh table almost always means "you forgot the SELECT policy".
  • Policies run on the server, not in your React code. A user can't bypass them by editing the browser.

Part 4 · The four CRUD policies

CRUD is four verbs: Create, Read, Update, Delete. For a personal notes app, each verb needs one policy, and each policy is the same one-line rule: auth.uid() = user_id.

Figure 1.3
SELECTRead
auth.uid() = user_id

Users see only rows where they are the owner.

INSERTCreate
auth.uid() = user_id

Users may only insert rows whose user_id is themselves.

UPDATEUpdate
auth.uid() = user_id

Users may only edit rows they own — both before and after.

DELETEDelete
auth.uid() = user_id

Users may only delete rows they own.

The four policies for a personal-data table. Same rule, applied to a different action.

A single prompt covers all four:

Add RLS policies to notes so a signed-in user can select, insert, update, and delete only their own rows. Use auth.uid() = user_id as the check.
Never store the user_id on the client alone
The policy on INSERT checks WITH CHECK — meaning even if the browser sends someone else's user_id, the row will be rejected. That's the point. Trust the server, not the form.

Part 5 · Wiring the UI

With the table and policies in place, the UI is three screens stitched together: a sidebar list, an editor pane, and a confirm dialog on delete. Prompt in this order:

  1. 1List

    Sidebar of notes for the signed-in user, newest first, with an empty state.

  2. 2Create

    A '+ New note' button that inserts a row and selects it.

  3. 3Edit

    Title + body inputs that auto-save on blur and bump updated_at.

  4. 4Delete

    Trash icon with a confirm dialog; navigates back to the empty state.

Figure 1.4
notes.lovable.app
Editing
Trip plan
Saved · updated 2s agoDelete
Three states of the notes app: empty, one note, and editing.

You never insert user_id manually. Ask the agent to pull it from the session on every insert — the policy will reject anything else anyway.

Part 6 · Realtime & optimistic updates

Two upgrades take a rough notes app from "fine" to "delightful", and both are one prompt each.

Optimistic updates

When the user clicks Save, don't spin for 300ms — show the change immediately, then reconcile with what the server returns. If the server rejects it (usually only when the policy denies you), roll back and show a toast.

Figure 1.5
Click Savet = 0msUI updatest = 4msServer writest = 120msConfirmedt = 160ms
Optimistic timeline. The UI updates first; the server confirms after.

Realtime across tabs

Cloud can push changes to every open tab as they happen. Prompt the agent: "subscribe the notes list to realtime updates so a note created in one tab shows up in another without refresh". That's it.

Part 7 · Testing & common traps

Before you call this done, run the two-account test: sign in as user A in one browser, user B in a private window, create notes in each. Neither should see the other's notes. If they do, RLS is off or a policy is missing.

Figure 1.6
1. Is the user signed in?No → fix auth first (Lesson 10)2. Does the SELECT policy exist?No → add auth.uid() = user_id3. Was user_id set on insert?No → pull it from the session, not the form4. Any typos in the column name?Check schema vs query
When the list is empty, walk the tree. Nine times out of ten it's step 2.
Avoid
Symptom, not cause
"Nothing shows up." "Insert fails silently." Skipping to guesses like "turn RLS off" — never do this. It opens your table to the whole internet.
Do
Read the response
Open the network tab. A 401 means auth. A 403 or an empty array with no error means RLS. A 400 means schema (wrong column or type). Each one has a different fix.

Database cheat sheet

The four policies (personal-data table)

  • SELECT — USING (auth.uid() = user_id)
  • INSERT — WITH CHECK (auth.uid() = user_id)
  • UPDATE — USING (auth.uid() = user_id) WITH CHECK (auth.uid() = user_id)
  • DELETE — USING (auth.uid() = user_id)

Debug checklist

  • Empty list on a fresh table → missing SELECT policy.
  • Insert rejected → user isn't signed in, or you sent the wrong user_id.
  • Update "works" but list doesn't refresh → subscribe to realtime, or invalidate the query.
  • Two-account test failed → run the empty-list tree in Figure 1.6.
Next up
Lesson 12 introduces relationships: what to do when one note has many tags, or a project has many tasks. Same bouncer, more interesting queries.
PreviousAuth & Protected RoutesNext Relationships & Queries