Database Basics for AI Builders: The Terminology You Need for Supabase, Xano, and Beyond

If you are building software with AI — in Lovable, Xano, Supabase, or anything else that generates a backend for you — the hardest part is rarely the building. It is describing what you want clearly enough that the AI builds the right thing.

And almost every time a generated app goes wrong later — duplicated records, data that can't be found, users seeing each other's information — the root cause is the same: the database was described loosely at the start.

You don't need to learn SQL to build with AI. You do need the vocabulary, because the vocabulary is the specification.

This guide covers the terminology in three layers for each concept: what it means in plain English, a concrete example, and how to actually use the term when prompting an AI. Skim it once, then keep it open as a reference the next time you brief a build.

1. The mental model

Everything starts with five words. Get these straight and the rest follows.

Term	Plain English	Example
Database	The whole organised collection of your app's data	Everything behind your SaaS product
Table	One type of thing	users, products, orders
Row (record)	One specific thing	The user Sam Patel
Column (field)	One attribute of that thing	email, created_at
Schema	The structure and rules holding it together	Which tables exist and how they connect

A spreadsheet is a useful starting analogy — tabs are tables, rows are records, headers are columns — but it breaks down fast, and knowing where it breaks is the point of this article. Spreadsheets let you type anything anywhere. A database enforces rules, and those rules are what stop your app corrupting itself at 2am.

A minimal users table:

users
  id          uuid        primary key
  email       text        not null, unique
  name        text
  created_at  timestamptz not null, default now()

Prompt it like this: "Create a users table with id, email, name and created_at. Email must be unique and required."

2. IDs and keys

Keys are how a database keeps track of which thing is which. They are the single most important idea in this guide.

• Primary key (PK) — the column that uniquely identifies a row. Every table should have one. Usually called id.
• Foreign key (FK) — a column that points at a row in another table. This is what creates a relationship.
• UUID — a long random identifier like 9f1c…. Unguessable, safe to generate anywhere, the sane default for modern apps.
• Auto-incrementing ID — 1, 2, 3, 4. Readable and compact, but it leaks how many records you have and makes merging data across environments painful.

The relationship reads like a sentence: orders.user_id → users.id. "Each order's user_id points at the id of a user."

Why references use IDs, never names or emails

It is tempting to store customer_email on an order and be done with it. Then a customer changes their email address, and half your history detaches from them. Names are worse — people change them, and two customers can share one.

An ID is a permanent handle on a thing. Everything else about that thing is allowed to change.

Prompt it like this: "Use UUID primary keys. Link orders to users with a user_id foreign key referencing users.id — do not duplicate the customer's name or email onto the order."

3. Relationships

This is the core of database design, and the part AI gets wrong most often when your brief is vague. There are only three shapes to learn.

One-to-one (1:1)

One row here matches exactly one row there. One user has one profile. Used to separate sensitive or rarely-read data from the main record.

One-to-many (1:N)

One row here matches many rows there. One user has many orders; each order belongs to exactly one user. This is the most common relationship in any app, and the FK always lives on the "many" side — orders.user_id, not a list of orders on the user.

Many-to-many (M2M / MTM)

Many rows here match many rows there. Many users belong to many teams; a team has many users. A database cannot express this with two tables alone — it needs a third.

The junction table

A junction table (also called a join, link, pivot or through table) is a table whose job is to hold the pairs:

users
  id

teams
  id

team_members            ← the junction table
  user_id  → users.id
  team_id  → teams.id
  role                  ← relationship-specific data
  joined_at

The detail people miss: a junction table is a real table and can carry its own columns. role doesn't belong on the user (they might be an owner of one team and a viewer of another) and it doesn't belong on the team. It belongs to the relationship. Same for joined_at, invited_by or permissions.

Add a unique constraint on the pair (user_id, team_id) so the same person can't be added to a team twice.

Prompt it like this: "Users and teams are many-to-many. Create a team_members junction table with user_id, team_id, role and joined_at, unique on (user_id, team_id)."

4. User and authentication tables

This trips up nearly everyone, because "user" means three different things and platforms happily use the word for all of them.

1. The authentication identity — managed by the platform. In Supabase this lives in auth.users: email, password hash, provider, confirmation status. You don't edit it directly and you shouldn't add your own columns to it.
2. Your application's profile record — a profiles table you own, in your own schema, with id matching the auth user's ID. This is where display name, avatar, plan and preferences live.
3. Business entities — customers, employees, team_members, contacts. These are not the same as accounts. A customer may never log in; an employee record may exist before their invite is accepted.

Rule of thumb: an account is how somebody signs in. A profile is who they are in your app. A customer is a thing your business tracks. Keep them in separate tables and link by ID.

Collapsing these together feels efficient for a week, then you need to invite a customer who already has an account, or delete an account while keeping the invoice history, and the shortcut becomes a rebuild.

Prompt it like this: "Keep the auth identity separate from app data. Create a profiles table whose id references the authenticated user id, and keep customers as its own table with an optional profile_id for those who have signed up."

5. Constraints and data integrity

Constraints are rules the database itself enforces. They matter more in AI-assisted builds, not less: the AI writes the form validation, but constraints are what hold when a script, an import, or a future prompt tries to write something silly.

Constraint	What it does	Use it for
NOT NULL	The field must have a value	Email, owner ID, status
UNIQUE	No two rows may share the value	Email, slug, invoice number
DEFAULT	Fills a value when none is given	created_at, status = 'draft'
Foreign key	The referenced row must actually exist	Every relationship
Check	Value must satisfy a condition	Quantity above zero

What happens when you delete the parent?

If you delete a user who has orders, the database needs to be told what to do with those orders. There are three sensible answers:

• Restrict — refuse the delete while related rows exist. Safest for financial records.
• Cascade — delete the children too. Right for genuinely owned data such as a profile or a draft's revisions. Dangerous everywhere else.
• Set null — keep the child, forget the link. Good for an assignee who leaves.

Many teams choose a fourth option: don't delete at all. A deleted_at timestamp ("soft delete") keeps history intact and makes mistakes recoverable.

Prompt it like this: "Orders must never be deleted when a user is removed — use restrict, or soft-delete the user with a deleted_at column. Cascade only from profiles."

6. Query terminology

These are the words you'll see in AI explanations, error messages and dashboards.

• Query — a request for data.
• Filter — narrow to matching rows ("only paid orders").
• Sort — order the results ("newest first").
• Join — pull related rows from another table in one go ("orders with the customer's name").
• Aggregate — summarise many rows into one number: count, sum, average.
• Pagination — return results in pages instead of all at once. Note that Supabase's API returns a maximum of 1,000 rows per request by default, which is a classic source of "my data is missing".
• Index — a lookup structure that makes filtering and sorting on a column fast. Add them to columns you filter by constantly, such as foreign keys.
• CRUD — create, read, update, delete. The four operations every entity needs, and a fast way to sanity-check a screen: which of the four can the user actually do here?

Prompt it like this: "List orders for the signed-in user, filtered to status = paid, sorted newest first, paginated 20 at a time, joining the product name. Index orders.user_id."

7. Permissions and security

This is where AI-generated apps most often ship a real vulnerability, because the app looks correct — the wrong data simply isn't on screen yet.

• Authentication — proving who you are (signing in).
• Authorization — what you're allowed to do once you're in.
• Roles — named groups of permissions: admin, editor, member. Store roles in their own table, never as a column on the profile a user can update.
• Row-level security (RLS) — rules enforced by the database on every single request, deciding which rows a user may read or write. This is the mechanism that makes a shared table safe.
• Ownership fields — user_id, created_by, organisation_id. Without one of these on a table, there is nothing for a row-level rule to check.
• Service / admin credentials — a key that bypasses all rules, for trusted server-side work only. It must never appear in browser code.

Hiding a button is not security. If the data is readable by the API, it is readable — by anyone who opens the network tab.

The practical test: for every table, can you answer "who may read this row, and who may change it?" in one sentence? If not, that table isn't finished.

Prompt it like this: "Enable row-level security on every table. Users may read and write only rows where user_id matches their own ID. Admins, determined by a separate user_roles table, may read everything. Do not use the service key in client code."

8. What good structure looks like

You can skip database theory almost entirely if you follow six habits.

1. Store each fact in exactly one place. If a price appears in two tables, one of them will eventually be wrong.
2. Never store comma-separated lists of IDs. "a1,b2,c3" in a text field cannot be filtered, joined or validated. Use a junction table.
3. Never number your columns. product_1, product_2, product_3 means you needed a related table — and it will break the day someone adds a fourth.
4. Use junction tables for anything reusable. Tags, team membership, permissions, saved items.
5. Be boringly consistent with names. Lowercase, plural table names, snake_case columns, *_id for every foreign key. Consistency helps AI as much as it helps you.
6. Always add created_at and updated_at. Nobody has ever regretted having timestamps.

Normalization is the formal name for point one: organising data so each fact lives in one place. It's worth understanding, and worth not over-applying. Splitting a table into six for purity's sake makes an app harder to build with no practical gain. Aim for "no duplicated facts", not academic perfection.

9. How to describe a database task to AI

Here is the framework worth saving. Describe your entities and the relationships between them in plain English, then ask for the schema explicitly — including the parts AI will otherwise quietly guess.

I have these entities:
- Users
- Projects
- Tasks

A user can belong to multiple projects.
A project can have multiple users.
Each task belongs to one project and has one assignee.

Please propose:
1. The tables and columns
2. Primary and foreign keys
3. The relationship types (1:1, 1:N, M2M)
4. Required constraints (not null, unique, defaults, delete behaviour)
5. Appropriate access rules (who can read/write which rows)
6. Any assumptions or edge cases you had to decide

Ask me about anything ambiguous before writing the schema.

That last line does a lot of work. It converts silent guesses into questions you can answer.

Bad request versus better request

Bad request	Better request
"Add teams to my app."	"Add a teams table. Users and teams are many-to-many via a team_members junction table storing role and joined_at, unique on (user_id, team_id)."
"Users should have tags."	"Tags are reusable across users, so create tags and a user_tags junction table — do not store tags as a comma-separated string."
"Make it secure."	"Enable row-level security on all tables. Members read and write only their own rows via user_id; admins from a separate roles table read everything."
"Let people delete their account."	"Soft-delete the profile with deleted_at, cascade delete their drafts, and keep invoices with the user reference intact."
"Track orders."	"Each order belongs to one user (orders.user_id → users.id) and has many line items in an order_items table. Store the price paid on the line item so historic orders don't change when a product's price does."

That last example is a subtle one worth internalising: usually you reference by ID, but for money you deliberately copy the price at the moment of sale. An invoice is a record of what happened, not a live view of the catalogue.

An annotated schema

Here is the whole article in one diagram — a small project-management app using every idea above.

auth identity            (managed by the platform)
   │  1:1
profiles                 ← your app's user record
   id            uuid  PK, = auth user id
   display_name  text
   created_at    timestamptz  default now()

projects
   id            uuid  PK
   name          text  not null
   owner_id      uuid  FK → profiles.id      ← ownership, used by RLS
   created_at    timestamptz  default now()

project_members          ← junction table: profiles ↔ projects (M2M)
   project_id    uuid  FK → projects.id      on delete cascade
   user_id       uuid  FK → profiles.id      on delete cascade
   role          text  not null default 'member'   ← relationship data
   joined_at     timestamptz  default now()
   unique (project_id, user_id)

tasks
   id            uuid  PK
   project_id    uuid  FK → projects.id      not null, on delete cascade   (1:N)
   assignee_id   uuid  FK → profiles.id      nullable, on delete set null  (1:N)
   title         text  not null
   status        text  not null default 'todo'
   due_at        timestamptz
   created_at    timestamptz  default now()
   updated_at    timestamptz  default now()
   index on (project_id), (assignee_id)

Read it back in English and every decision should be defensible: a task must belong to a project, so project_id is required and cascades; an assignee is optional, so it's nullable and sets null when someone leaves; membership carries a role because the role belongs to the pairing, not the person.

A–Z glossary

Term	Meaning
Aggregate	A summary of many rows: count, sum, average.
API	The interface your app uses to talk to the database or another service.
Authentication	Proving who a user is.
Authorization	Deciding what that user may do.
Cascade	Delete related rows automatically when the parent is deleted.
Column / field	One attribute stored for every row.
Constraint	A rule the database enforces on your data.
CRUD	Create, read, update, delete.
Default	A value used when none is supplied.
Foreign key (FK)	A column pointing at another table's primary key.
Index	A structure that makes lookups on a column fast.
Join	Combining related rows from two tables in one query.
Junction table	A table that records pairs, used for many-to-many.
Migration	A recorded change to the schema, applied in order.
M2M / MTM	Many-to-many relationship.
Normalization	Organising data so each fact is stored once.
NOT NULL	The field is required.
Pagination	Returning results in pages rather than all at once.
Primary key (PK)	The column that uniquely identifies a row.
Query	A request for data.
RLS	Row-level security: per-row access rules enforced by the database.
Row / record	One item in a table.
Schema	The structure of your database: tables, columns, rules.
Seed data	Starter rows inserted so an app isn't empty.
Service key	An admin credential that bypasses access rules. Server-side only.
Soft delete	Marking a row deleted with a timestamp instead of removing it.
SQL	The language used to query and change relational databases.
Table	A collection of rows of one type.
Timestamp	A date and time value, such as created_at.
Trigger	Logic the database runs automatically when data changes.
UNIQUE	No two rows may share this value.
UUID	A long random identifier used as a key.

The takeaway

AI will happily build whatever schema your description implies — including the messy one. The leverage isn't in learning SQL; it's in being able to say "these two are many-to-many, put the role on the junction table, and lock reads to the owner" and have that arrive correctly the first time.

Spend ten minutes on entities and relationships before you prompt. It is the cheapest ten minutes in the whole build.

If you're new to building this way, our guide to vibe coding covers the wider workflow, and Lovable and Xano are both good places to put this vocabulary to work.