Supabase migrations: designing a posts table for a Next.js blog

What are Supabase migrations? Each one is a SQL file in supabase/migrations/ that describes one change to your database. It is committed to Git and run in order, so the database on your machine and the one in production always match. Let's create the blog's posts table with a migration.

Why do the posts have to leave the code?

In the previous post, I built blog-mini, a small Next.js blog whose posts live in src/content/posts.ts. Each post is an object with a slug, title, description, date and body. That's enough to build the pages, but fixing a single typo means a commit, a push and waiting for Vercel to rebuild.

Tech Notes went down exactly that road. Its posts started out in a 330-line posts.ts file, and it wasn't until commit f55fc16 on 3 November 2026 that they moved into a posts table on Supabase, along with an admin page for writing. This post builds the foundation of that move: designing the table and creating it with a migration.

What's wrong with creating the table in the Dashboard?

Supabase has a table editor in its Dashboard; a few clicks and you have a new table. I've thought "it's quicker, I'll tidy it up later" too. The problem is that it leaves no trace in your code.

You create the table on the production project, but the database on your machine doesn't have it. Nobody who clones the repo has it either. Three months later you can't remember what constraint the status column had, and nobody could review the change before it ran against real data.

Migrations solve exactly that. Each change is a SQL file whose name starts with the time it was created. It lives in Git like any other code file, goes through a pull request and gets reviewed. Supabase's guide to database migrations shows how to create and run these files with the Supabase CLI:

npx supabase migration new create_posts_table   # creates an empty supabase/migrations/<timestamp>_create_posts_table.sql
npx supabase db reset                           # your machine: recreate the database, run every migration, then supabase/seed.sql (needs Docker)
npx supabase db push                            # push migrations that haven't run yet to the linked Supabase project

The Tech Notes repo now has eight migrations, from 20261103141232_create_posts.sql to 20261110011701_content_locale.sql (the one that added a language column for the English edition you're reading). The repo's rule is written right in its instructions file: every change to the schema, permissions or security policies goes through a migration in Git, and nothing is edited by hand in production.

Where does a quick-and-dirty table break?

Before writing a proper migration, I tried what a lot of people do the first time: a table where every column is text and allowed to be empty, then four rows of test data.

Each row has a mistake that's easy to make in real life. The first has a slug with capital letters and a space. The second misspells "published" as "publised". The third is published but has no date. The fourth reuses an existing slug and writes its date the Vietnamese way, day before month.

To run SQL without Docker or a Supabase account, I used PGlite, a real PostgreSQL that runs inside Node.js. The project needs only that one package ("@electric-sql/pglite": "0.3.14" in devDependencies, with "type": "module" in package.json). This script creates the loose table, saves the four rows, then runs the query the home page will use:

import { PGlite } from "@electric-sql/pglite";

const db = new PGlite();
await db.exec(`
  create table posts (id serial primary key, slug text, title text, body text, status text, published_at text);
`);

const rows = [
  ["Hello World", "Hello", "published", "2026-11-03"],
  ["why-i-write", "Why I write", "publised", "2026-11-04"],
  ["unfinished-post", "Unfinished post", "published", null],
  ["hello", "Hello (duplicate)", "published", "03/11/2026"],
];
for (const [slug, title, status, publishedAt] of rows) {
  await db.query("insert into posts (slug, title, status, published_at) values ($1, $2, $3, $4)", [slug, title, status, publishedAt]);
  console.log(`saved: ${slug} | ${status} | ${publishedAt}`);
}

const home = await db.query("select slug from posts where status = 'published' order by published_at desc");
console.log(`\nHome page (status = 'published', newest first): ${home.rows.map((r) => r.slug).join(", ")}`);

saved: Hello World | published | 2026-11-03
saved: why-i-write | publised | 2026-11-04
saved: unfinished-post | published | null
saved: hello | published | 03/11/2026

Home page (status = 'published', newest first): unfinished-post, Hello World, hello

The database accepted everything without a word of complaint. The last line shows what that costs you.

"Why I write" vanished from the home page because of one typo, and nothing told you. The post with no date is at the top, because when sorting in descending order PostgreSQL puts null values first by default. A date stored as text is compared as text, so "03/11/2026" sorts after "2026-11-03" even though they're the same day. And two posts with the slug hello means the URL /posts/hello doesn't know which one to show.

You could catch every one of these in TypeScript in the admin page. But data doesn't only come in through the admin page. It also arrives from import scripts, from someone editing by hand in the Dashboard, from an older version of the app. Constraints in the database block all of those paths at once.

How should the posts table be designed?

I started from the Post object in posts.ts and asked what each field needs once it lives in a database.

slug is still the post's URL, but I added a separate id column as the primary key. A slug can change, for example when you reword a title for SEO. Other tables (views, related interview questions) should point at a value that never changes.

date splits into two ideas. status says whether the post is a draft or published, and published_at is when it was published. I used timestamptz, a point in time with a time zone, rather than date, because later the blog will need to schedule posts for 8:00 in the morning, Vietnam time.

created_at and updated_at record when the post was created and last edited. updated_at will come in handy for the sitemap.

body is renamed body_markdown so anyone reading the schema knows what format the content is in.

Here's the first migration. I created it with npx supabase migration new create_posts_table and wrote this into the generated file:

-- The blog-mini posts table. Each constraint below blocks one specific kind of bad data.

create table public.posts (
  id bigint generated always as identity primary key,
  slug text not null unique
    constraint posts_slug_format check (slug ~ '^[a-z0-9]+(-[a-z0-9]+)*$'),
  title text not null
    constraint posts_title_length check (char_length(title) between 1 and 200),
  description text not null default '',
  body_markdown text not null default '',
  status text not null default 'draft'
    constraint posts_status_known check (status in ('draft', 'published')),
  published_at timestamptz,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  constraint posts_published_has_date check (status = 'draft' or published_at is not null)
);

comment on table public.posts is 'Blog posts. The content is stored as Markdown.';

-- The home page lists published posts, newest first: an index for exactly that query.
create index posts_published_at_idx on public.posts (published_at desc) where status = 'published';

-- Keep updated_at current on every edit, without relying on client code.
create function public.set_updated_at()
returns trigger
language plpgsql
set search_path = ''
as $$
begin
  new.updated_at := now();
  return new;
end;
$$;

create trigger posts_set_updated_at
before update on public.posts
for each row execute function public.set_updated_at();

Each constraint answers one of the bad rows from the previous section. posts_slug_format allows only lowercase letters, digits and hyphens, with no double hyphens. posts_status_known accepts only two values, so "publised" is blocked. posts_published_has_date is a constraint on the whole row: a published post must have a date, a draft doesn't have to. unique on the slug stops two posts sharing a URL.

posts_published_at_idx is a partial index that only contains published posts, because readers never see drafts. With two posts you don't need it yet, but it matches the home page query exactly and costs almost nothing to add from the start.

The posts_set_updated_at trigger resets updated_at every time a row is edited. If you leave it to client code to send that value, sooner or later some code path forgets, and the sitemap reports the wrong update date.

How does the existing data get into the table?

The two posts in posts.ts move into supabase/seed.sql. npx supabase db reset runs this file after all the migrations, so the database on your machine always has data to test the pages with:

-- The two sample posts, moved from src/content/posts.ts into the database.
insert into public.posts (slug, title, description, body_markdown, status, published_at) values
  ('hello', 'Hello', 'The blog''s first post.',
   E'This is the first post.\n\nPosts now live in the posts table on Supabase.',
   'published', '2026-11-03 08:00+07'),
  ('why-i-write', 'Why I write', 'Notes on what I learn at work.',
   E'Writing things down helps me understand them better.\n\nAnd it saves the next person some time.',
   'published', '2026-11-04 08:00+07');

A string that starts with E'...' lets you write \n for a line break in the Markdown content, and '' is how SQL writes an apostrophe inside a string. The dates carry +07 so PostgreSQL knows it's 8:00 Vietnam time, not 8:00 UTC.

The seed is only for your machine. db push doesn't run the seed file against production, and you wouldn't want these two sample posts on the real blog anyway.

Do the constraints really block bad data?

To check, the next script does what db reset does. It runs the files in supabase/migrations/ in name order, then runs seed.sql. Then it tries the bad rows again, plus two rows with dates written day-first:

import { readdirSync, readFileSync } from "node:fs";
import { PGlite } from "@electric-sql/pglite";

const db = new PGlite();
for (const file of readdirSync("supabase/migrations").sort()) {
  await db.exec(readFileSync(`supabase/migrations/${file}`, "utf8"));
  console.log(`migration ok: ${file}`);
}
await db.exec(readFileSync("supabase/seed.sql", "utf8"));
console.log("seed ok: supabase/seed.sql\n");

const badRows = [
  ["Hello World", "Hello", "published", "2026-11-03"],
  ["why-i-write-2", "Why I write", "publised", "2026-11-04"],
  ["unfinished-post", "Unfinished post", "published", null],
  ["hello", "Hello (duplicate)", "published", "2026-11-03"],
  ["bad-date", "Date written the Vietnamese way", "published", "13/11/2026"],
  ["date-3-nov", "3 November written the Vietnamese way", "published", "03/11/2026"],
];
for (const [slug, title, status, publishedAt] of badRows) {
  try {
    await db.query("insert into public.posts (slug, title, status, published_at) values ($1, $2, $3, $4)", [slug, title, status, publishedAt]);
    const { rows } = await db.query("select published_at::date::text as day from public.posts where slug = $1", [slug]);
    console.log(`saved ${slug}, but published_at = ${rows[0].day}`);
  } catch (error) {
    console.log(`rejected ${slug}: ${error.message}`);
  }
}

const before = await db.query("select updated_at from public.posts where slug = 'hello'");
await db.query("update public.posts set title = 'Hello!' where slug = 'hello'");
const after = await db.query("select updated_at from public.posts where slug = 'hello'");
console.log(`\nupdated_at changes on edit: ${before.rows[0].updated_at.getTime() !== after.rows[0].updated_at.getTime()}`);

const home = await db.query("select slug, tags from public.posts where status = 'published' order by published_at desc");
console.log(`Home page: ${home.rows.map((r) => `${r.slug} (tags: ${JSON.stringify(r.tags)})`).join(", ")}`);

This script also picks up the second migration (adding a tags column), which I write in the next section, so the last line prints tags too. The output:

migration ok: 20261110080000_create_posts_table.sql
migration ok: 20261110090000_add_post_tags.sql
seed ok: supabase/seed.sql

rejected Hello World: new row for relation "posts" violates check constraint "posts_slug_format"
rejected why-i-write-2: new row for relation "posts" violates check constraint "posts_status_known"
rejected unfinished-post: new row for relation "posts" violates check constraint "posts_published_has_date"
rejected hello: duplicate key value violates unique constraint "posts_slug_key"
rejected bad-date: date/time field value out of range: "13/11/2026"
saved date-3-nov, but published_at = 2026-03-11

updated_at changes on edit: true
Home page: why-i-write (tags: []), hello (tags: []), date-3-nov (tags: [])

The first four mistakes are stopped right where they happen, and each error names the constraint that was violated. Naming constraints clearly, like posts_status_known, instead of letting PostgreSQL pick a name makes these errors much faster to understand when they show up in a log.

Now look at the two date rows. "13/11/2026" is rejected, because by default PostgreSQL reads dates the American way, month before day (the DateStyle setting is ISO, MDY), and there is no month 13. "03/11/2026" is saved, but as 11 March. That post drops to the bottom of the home page, and no constraint can catch it, because 11 March is a perfectly valid date.

The lesson I took from this: constraints block data with the wrong shape, but they can't guess what the person meant. Dates should always travel from your code to the database in ISO format (2026-11-03T08:00:00+07:00), never in the format people are used to reading.

How do you add a column to a table that already has data?

A week later, the blog needs tags. The first thing that came to mind was a required column. This script runs the first migration and the seed, then tries two ways of writing it:

import { readFileSync } from "node:fs";
import { PGlite } from "@electric-sql/pglite";

const db = new PGlite();
await db.exec(readFileSync("supabase/migrations/20261110080000_create_posts_table.sql", "utf8"));
await db.exec(readFileSync("supabase/seed.sql", "utf8"));
console.log("The posts table has 2 posts.\n");

try {
  await db.exec("alter table public.posts add column tags text[] not null;");
  console.log("option 1 worked");
} catch (error) {
  console.log(`option 1 (not null, no default) failed: ${error.message}`);
}

await db.exec(readFileSync("supabase/migrations/20261110090000_add_post_tags.sql", "utf8"));
console.log("option 2 (not null default '{}') worked");

// An older app version doesn't know the tags column: its insert doesn't mention tags.
await db.query("insert into public.posts (slug, title) values ('post-from-old-app', 'Post added by the old app version')");
const rows = await db.query("select slug, tags from public.posts order by id");
for (const row of rows.rows) console.log(`  ${row.slug}: tags = ${JSON.stringify(row.tags)}`);

Option 2 is the project's second migration:

-- Add tags to posts. Backward compatible: existing posts get an empty array,
-- and an older app version (which doesn't know this column) can still add posts because the column has a default.
alter table public.posts
  add column tags text[] not null default '{}'
    constraint posts_tags_limit check (cardinality(tags) <= 10);

The posts table has 2 posts.

option 1 (not null, no default) failed: column "tags" of relation "posts" contains null values
option 2 (not null default '{}') worked
  hello: tags = []
  why-i-write: tags = []
  post-from-old-app: tags = []

Option 1 fails because the two existing posts have no value for the new column, which means null, and the column forbids null. On your machine with an empty table, this migration runs without a hitch. It only breaks in production, with hundreds of posts, and that's the worst possible moment to find out.

Option 2 gives the column a default of an empty array. Old posts get an empty array, and the post-from-old-app row shows one more thing. On Tech Notes, I run db push first and only then merge the new code, so while Vercel is building, the old app version is still serving readers against a database that already has the new column. The old app doesn't know about tags and its insert doesn't mention the column, but it keeps working thanks to the default.

Tech Notes ran into exactly this when it added reading levels to posts (shown to readers as Beginner, Intermediate and Advanced; the stored values are the Vietnamese slugs). This is the real migration from the repo, written on 3 November 2026; only the column name differs from the example above:

-- Reader-facing difficulty level for each post (Cơ bản / Trung cấp / Nâng cao).
-- Backward compatible: existing rows and older app versions get the 'co-ban' default.
alter table public.posts
  add column level text not null default 'co-ban'
    constraint posts_level_known check (level in ('co-ban', 'trung-cap', 'nang-cao'));

comment on column public.posts.level is 'Difficulty shown to readers: co-ban, trung-cap or nang-cao.';

The table exists, so how does the website read it?

The posts table is ready, but blog-mini can't read it yet. The Next.js pages will call Supabase with the publishable key, and that key sits in the JavaScript sent to the browser, where anyone who opens DevTools can see it.

There's a recent change you should know about. According to Supabase's announcement, from 30 October 2026, new tables in the public schema are no longer exposed to the Data API automatically on every project. You have to grant access to the anon role (readers who aren't signed in) yourself before the pages can read them. The real Tech Notes migration has exactly that grant.

Here's the dangerous part. When the page reports "permission denied", the quickest fix is grant all on public.posts to anon. The page works again straight away, but from then on, anyone holding the publishable key can edit or delete your posts. Granting read access properly, and only for the rows that should be visible, is the job of Row Level Security, the subject of the next post. You can follow the whole series at Building a blog with Next.js + Supabase, and if you're preparing for interviews, the question about database indexes is in the interview question bank.