🗄️Connecting a Database (Without Tears)
Add real persistence to your AI-built app, with auth and row-level security done right.
What you'll get
- Model your first table from a plain-English description
- Understand row-level security in plain English
- Wire up auth without breaking your existing UI
- Test your app as two different users to catch leaks
Going from localStorage to a real database feels scary. With the right prompts and a few non-negotiable habits around security, it's a one-evening upgrade — and the moment a friend logs in and sees their own data is the moment your project starts to feel like a product.
Model the data first, in plain English
Before touching SQL, describe the entities and relationships out loud. 'A user has many habits. A habit has many check-ins. A check-in belongs to one habit and one user.' Three sentences and you have a schema.
This step is non-negotiable. Most database disasters trace back to a fuzzy mental model that became fuzzy SQL.
Row-Level Security in plain English
RLS is a rule that runs on every database query: 'before returning this row, check who's asking and decide whether they're allowed to see it.' Without RLS, anyone with your database URL can read everyone's data. With RLS, the database itself enforces who sees what — even if a bug in your code tries to fetch the wrong rows.
The simplest, safest pattern: every table has a `user_id` column, and every policy checks `user_id = auth.uid()`. That's it. Read, write, update, delete — all the same shape.
Add a habits table (id, user_id, title, created_at) and a checkins table (id, habit_id, user_id, date). Enable RLS on both. Policies: each user can SELECT/INSERT/UPDATE/DELETE only rows where user_id = auth.uid().
How a request actually flows
It helps to picture what happens when a logged-in user opens their dashboard. Knowing the flow makes debugging dramatically easier — when something goes wrong, you can point at exactly which step failed.
Auth-aware UI
Your UI now has three states for any data-driven page: logged out, logged in, and loading. Plan for all three from day one — a UI that flickers from 'no data' to 'data' on every load feels broken.
- Redirect to /login
- Show why
- Preserve return URL
- Skeleton, not spinner
- Same layout as loaded state
- Render their data
- Show email in header
- Sign out button
Roles, the right way
The single most common security mistake: storing a `role` column on the users table and checking it from the client. Anyone can edit their own profile and promote themselves to admin.
Roles must live in a separate table the user cannot write to, and admin checks must run server-side via a security-definer function. This is one of those cases where you do not improvise — copy the pattern your platform documents.
Test as two different users
The easiest way to catch RLS leaks is to test like a real attacker. Open your app in two different browsers (or one normal window and one private window), sign in as two different accounts, and try to access each other's data.
If user A can see anything belonging to user B — through the URL, the API, or the browser console — your RLS is broken. Fix it before you publish, not after.
- Open two browsers, sign in as two users
- Try to view user B's records from user A's session
- Try to fetch directly from the database in the console
- Repeat after every schema change
Wire up the auth UI
Once the schema and policies are solid, the UI side is small. A login page, a sign-out button, a guard around private routes, and the user's email in the header. That's usually it.
Add email + Google login. Redirect logged-out users from /dashboard to /login. Show the user's email in the header when signed in. Add a sign-out button in the user menu.
Prompt examples
Add a habits table (id, user_id, title, created_at) and a checkins table (id, habit_id, user_id, date). Enable RLS so each user only sees their own rows.
Add email + Google login. Redirect logged-out users from /dashboard to /login. Show the user's email in the header when signed in.
Review every table in this project. Confirm RLS is enabled and the policies only allow users to access rows where user_id = auth.uid(). Flag anything else.
- Forgetting to enable RLS — anyone can read everyone's data
- Storing roles on the users table (use a separate user_roles table)
- Calling the database directly from a public page that doesn't need auth
- Skipping the two-user test before going public
- Always add RLS in the same prompt as the table
- Test as a logged-out visitor and as two different users
- Keep the schema small — add columns when you need them
- Document the data model in a README so future-you remembers it
Backend changes deploy with your app. Before going public, double-check RLS by trying to read another user's data from the browser console. If you can, your policies are wrong.