Chrysolites Tech Academy

Photo Social App with FlutterFlow and Supabase · Course book

Chapter 8: Tables and Keys

Pairs with Lesson 8: Creating Supabase Tables

With the pages designed, the photo app needs somewhere to keep its data. Supabase provides a PostgreSQL database, and the first step is creating tables: structured grids of rows and columns, one table for each kind of thing the app stores. This chapter explains how tables work in Supabase, how to create the posts table with sensible column types, what primary keys and foreign keys are and why they matter, how relationships connect posts to their authors, and how FlutterFlow reads the database's structure with "get schema". It also estimates by hand how much space a table needs as the app grows.

Tables, rows and columns

A table is like a spreadsheet with strict rules. Each column has a name and a data type, so every value in it is of the same kind: text, a number, a date, a true-or-false value or an identifier. Each row is one record, one post or one user. Unlike a spreadsheet, a database enforces its rules: a number column rejects text, a required column rejects empty values, and constraints can demand that values are unique or refer to rows in other tables.

Supabase's table editor lets you create tables and columns through a form, with no SQL needed, and shows the SQL it runs, which is a good way to learn. Every new table gets an id column as its primary key and a created_at column holding the time each row was inserted, filled in automatically.

ColumnTypeDefault or rulePurpose
iduuid or int8Generated automatically; primary keyIdentifies the post
created_attimestamptznow()When the post was made; sorts the feed
user_iduuidRequired; foreign key to usersThe author
image_urltextRequiredAddress of the photo in storage
captiontextOptionalThe text written with the post
like_countint40Kept up to date by a trigger in a later part

Choosing types carefully pays off. timestamptz stores a moment in time with its time zone, so the same post shows the right local time to every user. text holds strings of any length, which suits captions and addresses. Integer types such as int4 and int8 hold whole numbers; int8 allows far larger values, which matters for ids that might one day pass two billion.

A few more column settings shape how the table behaves. Marking a column not null makes it required, so every post must have an author and an image. A default value fills a column automatically when the insert does not provide one, such as zero for the like count or the current time for created_at. A unique constraint forbids duplicate values, which suits usernames. And a check constraint enforces a rule on each row, for example that a caption is at most 2,200 characters. Every rule placed in the database is enforced for every app, script and request that touches the table, so it is the most reliable place for rules that must never be broken.

Supabase also exposes every table through an automatic REST API, which is what FlutterFlow's actions and the Supabase client libraries call behind the scenes. That is convenient, but it means a table is reachable from the internet as soon as it exists. Supabase therefore encourages enabling row level security on every table in the public schema, so that no row can be read or changed until a policy allows it. Early in development, a simple policy allowing signed-in users to read and insert is enough to test with; later parts tighten the policies so that, for example, only a post's author can delete it.

Primary keys

A primary key is a column whose value uniquely identifies each row and is never empty. It is how the rest of the app refers to a row: the post detail page receives a post's id, a like stores the id of the post it belongs to, and so on. Primary keys are automatically indexed, so finding a row by its key is fast however large the table grows.

Two kinds of primary key are common. An auto-incrementing integer counts up, 1, 2, 3, and is compact and easy to read. A UUID is a random 128-bit value written as 36 characters, which can be generated anywhere without coordination and does not reveal how many rows exist or let anyone guess other ids. Tables for users usually use UUIDs, because they must match the ids that Supabase Authentication assigns; for posts and other tables either choice works, and consistency across the project is the main thing.

Foreign keys and relationships

A foreign key is a column that refers to the primary key of another table. The posts table's user_id is a foreign key to the users table's id, which says "this post belongs to that user". Supabase's table editor sets this up when you link a column to another table, and the database then enforces the link: a post cannot refer to a user that does not exist.

users id (PK) uuidusername textavatar_url textcreated_at timestamptz posts id (PK)created_at timestamptzuser_id (FK) uuidimage_url textcaption textlike_count int4 many posts → one user on delete: cascade
Figure 8.1 — A one-to-many relationship. Each post's user_id refers to one user's id; one user can have many posts.

This is a one-to-many relationship: one user, many posts. Relationships also define what happens when the referenced row is deleted. Cascade deletes the dependent rows too, so deleting a user deletes their posts; set null keeps the posts but empties their user_id; and restrict refuses to delete the user while posts exist. For a social app, cascading deletes from users to their content are usually right, because they make "delete my account" remove everything the user created.

Relationships also make queries more powerful. Supabase can return a post together with its author's username and avatar in a single request, by following the foreign key, which saves the app from making one request for the post and another for the user. Many-to-many relationships, such as follows or likes, need a third table that holds pairs of foreign keys, covered in later parts.

Get schema: telling FlutterFlow about the tables

FlutterFlow does not read the database on its own each time you build a page. Instead, in the Supabase integration settings, you click Get Schema, which loads the list of tables, their columns and their types into the FlutterFlow project. From then on, FlutterFlow's query and insert actions offer the tables by name, check that you set required columns, and know each column's type, so a date is shown as a date and a number as a number.

Whenever the database structure changes, adding a column or a table, you must click Get Schema again, or FlutterFlow will not know about the change and actions may fail. Forgetting this step is one of the most common causes of confusing errors, so it is worth making a habit: change a table, then refresh the schema. Renaming a column that pages already use is riskier still, because every query that refers to the old name must be updated, so choose clear names from the start.

How big will the table get?

Estimating storage helps choose a plan and spot problems early. Each column type takes a known amount of space: a UUID takes 16 bytes, a timestamptz 8 bytes, an int4 4 bytes, and text takes about one byte per character for ordinary English letters, plus a small overhead. Each row also has a fixed overhead of a couple of dozen bytes.

Worked example. A post row holds an id and a user_id as UUIDs, 2 × 16 = 32 bytes; a timestamp, 8 bytes; a like count, 4 bytes; an image address of about 120 characters; and an average caption of 100 characters. Adding about 24 bytes of row overhead gives roughly 32 + 8 + 4 + 120 + 100 + 24 = 288 bytes, call it 300. A million posts then need about 300 MB, and the primary key index adds more. The images themselves, stored in a bucket, are far larger: at around 300 KB each, a million photos take about 300 GB, a thousand times the table.
0.3 GB300 GB table rowsphotos in storage 0.11101001,000 gigabytes, log scale
Figure 8.2 — Storage for a million posts. The rows are small; the photos dominate, which is why images belong in storage buckets, not tables.

The lesson is that table rows are cheap and images are not, so the effort to save space belongs mainly in compressing and resizing images, covered when uploading is built. The database stays small and fast, and indexes on columns used for filtering, such as user_id, keep queries quick as rows accumulate.

Practice tasks

  1. Design the columns and types for a comments table with an id, a time, an author, a post and the text.
  2. Estimate the row size of a comment with two UUID foreign keys, a UUID id, a timestamp and an average text of 60 characters.
  3. Explain the difference between cascade, set null and restrict when a user is deleted.
  4. Why must you click Get Schema again after adding a column?
  5. Give one advantage of UUIDs and one of auto-incrementing integers as primary keys.
  6. Why does the users table usually use a UUID primary key in a Supabase app?

Install Chrysolites Tech Academy

Android / Chrome / Edge: open the browser menu and choose Install app or Add to Home screen.

iPhone / iPad (Safari): tap the Share button, then Add to Home Screen.