How databases work in AI-built apps

What is a table, really?

A spreadsheet with enforced structure. Each column has a type, so a date column cannot hold 'next Tuesday'. Each row usually has an `id` that nothing else shares, which is how other tables refer to it.

The `user_id` column is doing the important work here: it is what makes a row belong to somebody. Almost every access rule you will ever write depends on a column like it.

How do tables relate to each other?

By storing each other's ids. A note holds the id of its author; an order holds the id of its customer. That reference is called a foreign key, and the database can enforce it — refusing to store a note whose author does not exist, or deleting notes when their author is deleted.

• One-to-many: one user has many notes (the note holds the user id).
• Many-to-many: users belong to many teams and teams have many users (a third table holds the pairs).
• One-to-one: a user has one profile row (often used to keep public fields separate from account fields).

What is a query doing?

-- SQL
select id, title from notes where user_id = $1 order by created_at desc limit 20;

// Client library
supabase.from("notes").select("id,title").eq("user_id", userId)
  .order("created_at", { ascending: false }).limit(20);

Every query answers four questions: which table, which columns, which rows, in what order. If a list on your page is wrong, one of those four is wrong — and it is nearly always 'which rows'.

Why does the database say no?

Modern hosted databases enforce row-level access rules. A query that is perfectly valid will still return nothing if the rules do not allow this particular user to see those rows. Crucially, this often looks like an empty list rather than an error, which is why people spend hours debugging the wrong layer.

An empty list with no error is a permissions symptom until proven otherwise. Check the rules before you check the query.

What should you check in an AI-generated schema?

1. 1Does every table holding personal data have a column identifying the owner?
2. 2Are access rules enabled on every table, and does each rule name a real condition rather than allowing everyone?
3. 3Are permissions granted to the roles your app actually uses?
4. 4Do timestamps exist so you can tell when something was created or changed?
5. 5Is anything stored that you would not want to explain in a privacy policy?