How we built the database for an AI-first blog in one afternoon

The first commit of Agentikas Blog wasn't a page, a component, or an endpoint. It was a SQL migration.

If you're going to build a multi-tenant platform where anyone can spin up a blog, and where every blog has to be callable by AI agents from the first post, the database isn't an implementation detail — it's the decision that shapes everything else. One three-hour afternoon with Postgres, Supabase, and an open editor.

The model: blogs and posts, with a twist

The core model is trivial:

create table blogs (
  id        uuid primary key default gen_random_uuid(),
  owner_id  uuid not null references auth.users (id) on delete cascade,
  slug      text not null unique,
  name      text not null,
  brand_config jsonb not null default '{}'::jsonb,
  created_at timestamptz not null default now()
);

create table blog_posts (
  id          uuid primary key default gen_random_uuid(),
  blog_id     uuid not null references blogs (id) on delete cascade,
  slug        text not null,
  title       text not null,
  body        text not null default '',
  status      post_status not null default 'draft',
  published_at timestamptz,
  unique (blog_id, slug)
);

The "twist" is the blog's slug. It's not a cosmetic field. It's the tenant identifier. When someone visits maria.blog.agentikas.ai/post-one, all the middleware does is:

1. Extract maria from the Host header
2. Look up blogs where slug = 'maria'
3. Hand the blog_id to every query for the rest of the request

No separate tenants table, no heavy auth middleware, no dedicated service. Postgres and Supabase handle isolation.

Full-text search in a generated column

A decision that looks minor but saves hours: full-text search as a generated stored column, not computed at query time.

alter table blog_posts add column fts tsvector
  generated always as (
    setweight(to_tsvector('english', coalesce(title, '')),    'A') ||
    setweight(to_tsvector('english', coalesce(subtitle, '')), 'B') ||
    setweight(to_tsvector('english', coalesce(body, '')),     'C')
  ) stored;

create index blog_posts_fts_idx on blog_posts using gin (fts);

setweight makes a title match outweigh a body match. It's the difference between "search kinda works" and "search returns what you'd expect." And because the column is stored, Postgres keeps it fresh on every UPDATE automatically — zero application code.

RLS: security lives in the database, not in handlers

For the first sprints every API route used Supabase's service role key. Convenient: one client, writes to any table, ignores RLS. The "can this user touch this post?" check was hand-rolled in each handler.

Until we forgot the check in at least two routes. Nothing exploded — nobody edited the wrong post. But they could have. Security lived "in the backend dev's head," not in the database.

The new rule, post-refactor:

• User client + RLS for any endpoint acting on behalf of an authenticated user. The DB decides.
• Service client only for background jobs (cron, webhooks) and legitimate cross-tenant ops (the public mirror that lists posts across blogs).

The policies look like this:

create policy "anyone reads published posts"
  on blog_posts for select
  using (status = 'published');

create policy "owners do anything with their posts"
  on blog_posts for all
  using (
    exists (
      select 1 from blogs
      where blogs.id = blog_posts.blog_id
        and blogs.owner_id = auth.uid()
    )
  );

If your security lives in if statements, move it to the DB. Code mutates; policies get audited.

Native i18n on day one

Every post has locale and canonical_id. A translation isn't an extra field on the same row — it's its own row with its own slug, its own body, its own meta description in the target language. Translation siblings share a canonical_id pointing at the root row.

alter table blog_posts
  add column locale       text not null default 'es',
  add column canonical_id uuid references blog_posts(id);

drop index if exists blog_posts_blog_id_slug_key;
create unique index blog_posts_blog_locale_slug_idx
  on blog_posts (blog_id, locale, slug);

This lets /es/blog/webmcp-protocolo and /en/blog/webmcp-protocol coexist in the same blog without collision. And it lets an agent asking for the post in en get en — no silent fallback to another language, which is what makes AI answers come out shoddy.

i18n added in month 3 costs you two weeks of migrations. Put in the schema on day one, it costs nothing.

The rest fell into place

The rest of the schema followed by inertia: post_likes, post_comments, blog_follows, post_translations, contributions. Each one a short migration, each one with its policies, each one written after a real use case showed up — never before.

Today there are 19 accumulated migrations, none over 80 lines. They all live in supabase/migrations/, versioned in git, runnable locally with the Supabase CLI.

The deeper lesson isn't about Postgres. It's about ordering: schema first, code after. When the schema is correct — strict RLS, native i18n, generated FTS — the rest of the backend is a thin layer on top. When the schema is just okay, you spend six months patching.

---

Agentikas is open source. The full schema lives in github.com/agentikas/agentikas-blog/tree/main/supabase/migrations. The RLS policies, the FTS column, and the translation model are three files — read them, fork them, improve them if you can.