Databases for designers: tables, rows and permissions

Tables, rows, primary keys and permissions explained for designers. Why access rules are the part most often forgotten in apps built with AI tools.

info Quick Definition
A database keeps your app’s information in an orderly way. In relational databases such as PostgreSQL, that order is tables: columns that define what is kept and rows that hold each record. For a designer, understanding how it is organized and who can see it matters because almost every data decision shows up on screen.

How is a table organized?

Think of a spreadsheet with strict rules. Each column has a name and a type, and each row holds a set of values, one per column. The official PostgreSQL tutorial shows a weather table with the columns city, temp_lo, temp_hi, prcp and date, and a type for each: text up to 80 characters, integers, numbers with decimals and dates (PostgreSQL, Creating a new table).

Types matter because they limit errors. A numeric column does not accept the word “eight”, and a date column rejects text that is not a valid date. If an app stores prices as text, it cannot add or sort them properly.

Every data type shows up in the interface. A date asks for a date picker, a status with three possible values asks for a menu or a group of options, and a free-text field asks for validation. When the design and the table do not match, the mistake appears as a form that accepts anything.

What is a primary key?

It is a column, or a set of columns, whose value uniquely identifies each row. Without one, two identical records are indistinguishable, and changing one can affect the other without anyone noticing. Supabase’s guide to tables recommends creating a primary key for every table, and its table editor generates the id and created_at columns by default (Supabase, Tables).

For a designer, the primary key answers a practical question: how does the interface refer to one specific record? If the “Edit” button in a list does not know which row to edit, the key is badly defined.

Who can see each row?

This is the part most often forgotten in AI-generated apps. When an app talks to the database straight from the browser, it uses a key meant to be public. Supabase calls it the publishable key. That is normal; what protects the data is the database’s rules, not the key.

According to Supabase’s documentation, a table in the public schema is reachable through its API. If row level security (RLS) is not enabled, the documentation itself warns that anyone holding the publishable key can read and write every row (Supabase, Tables). If it is enabled but there is no rule, requests return no rows until a policy exists. A policy works like a filter applied to every query: for example, “only the rows whose user_id is the person asking” (Supabase, Row Level Security).

Two practical details from that documentation:

  • Supabase’s dashboard table editor enables RLS when you create a table; tables created with SQL do not get it, and you have to enable it by hand. An AI tool that generates tables with SQL can leave them open.
  • The secret key bypasses the rules, and the documentation says never to use it in the browser. That is why secrets and environment variables exist.

This explains why a generated app can look fine in the demo and expose real data: whoever tests it signs in with their own account and sees their own data. Whether someone else can see that same data is only discovered by testing with a second account.

What changes if the tool uses Firebase?

Some app generators use Firebase instead of Supabase. Cloud Firestore, one of Firebase’s databases, does not use tables: according to its documentation, it is a NoSQL, document-oriented database. Data goes into documents (records of fields, similar to JSON) and documents live inside collections. It also has no fixed schema, so two documents in the same collection can have different fields (Firebase, Cloud Firestore data model).

For the designer the question does not change: who can read or write each piece of data? In Firestore the answer is security rules, which are evaluated on every request from a web or mobile client. The documentation shows an example rule set that allows everything and warns never to use it in production, because it lets anyone overwrite the entire database (Firebase, Get started with Cloud Firestore Security Rules). Since there is no fixed schema, the team should agree on the fields of each collection so screens do not break.

Which data decisions belong to design?

A designer does not write the tables, but does decide what data is needed and how it is shown. Four questions help define a table before you ask for it:

  1. What is a row? (an appointment, a client, an order)
  2. What is the minimum data each row holds, and of what type?
  3. What uniquely identifies a row?
  4. Who can see each row, and who can change it?

If you cannot answer the fourth, the table is not ready for production.

Example

Illustrative example: a design studio needs a log of client requests. The designer answers the four questions: a row is a request; it has the client’s name, a date, a project type and a status (a closed list of three values); the key is a generated id; and each client can only see their own requests, while the internal team sees all of them. When she asks the AI for the table, she checks that access rules are active. With two test accounts, she confirms that one client cannot see another client’s requests. That quick test separates a prototype from a system with real data.

How do you apply it tomorrow?

  1. Before asking for a table, write the four questions above with one answer each. If one is missing, the table is not ready to request.
  2. Ask the AI to show the table definition with its data types. Check that dates are dates and that statuses are a closed list of values, not free text.
  3. Ask whether row level security (or security rules, if it uses Firebase) is enabled on each table or collection, and which policy it has.
  4. Create two test accounts and confirm that each sees only its own rows, before showing the screen to anyone.
  5. Note which personal data each table stores and why. If you cannot find a design reason, do not store it.

To see how an app gets published along with its data, read deploy for designers. And for the whole path of a generated app, see from idea to app in a day.

Common mistakes

  • Storing dates or numbers as text.
  • Creating tables with no primary key or no way to identify each row.
  • Forgetting access rules because the demo works.
  • Testing with only one account, the database owner’s.
  • Leaving rules that allow everything “while testing”.
  • Storing personal data “just in case” without a design reason.
  • Thinking the interface hides a column and therefore protects it: if the database delivers it, anyone querying the API sees it.

Sources

Frequently asked questions

What is a table in a database?
It is a named structure that stores data in columns and rows, similar to a spreadsheet. Each column has a name and a data type.
What is a primary key?
It is a column whose value uniquely identifies each row. It prevents duplicates and lets you refer to one specific record without ambiguity.
Why do tables need access rules?
Because in apps that talk to the database from the browser, anyone with the public key can try to read or write. Without active rules, the database cannot tell who may see each row.
What happens if a Supabase table has security enabled but no rules?
According to Supabase’s documentation, API requests return no rows until at least one policy exists. Without security enabled, anyone holding the publishable key can read and write every row.
Does Firebase use tables?
No. Cloud Firestore is a document database: it stores data in documents with fields, organized into collections, and does not require a fixed schema.