DemoBuy

Database Schema

Last updated on 2026-09-03

The kit uses 11 Supabase tables organized into four domains: content, engagement, marketing, and system. All tables have row-level security (RLS) enabled with a public-facing blog model -- public visitors can read published content, while admin operations are scoped by role.

Schema Overview

Content:      profiles, categories, tags, posts, post_tags, media

Engagement:   comments

Marketing:    subscribers, newsletter_issues

System:       site_settings, pageview_daily

Tables

profiles

Auto-created on user signup via database trigger. Stores blog author and admin information.

Column Type Notes
id uuid (PK, FK) References auth.users.id
full_name text Display name
email text User email
avatar_url text Profile image URL
bio text Author biography
role text admin, editor, or author
social_twitter text Twitter/X handle
social_github text GitHub username
social_linkedin text LinkedIn profile URL
social_website text Personal website URL
created_at timestamptz Auto-set
updated_at timestamptz Auto-updated by trigger

categories

Content categories for organizing posts.

Column Type Notes
id uuid (PK) Auto-generated
name text Category name
slug text (unique) URL-safe identifier
description text Category description
color text Display color in oklch format
image text Category header image URL
created_at timestamptz Auto-set
updated_at timestamptz Auto-updated by trigger

tags

Lightweight labels for cross-cutting post classification.

Column Type Notes
id uuid (PK) Auto-generated
name text (unique) Tag label
slug text (unique) URL-safe identifier
created_at timestamptz Auto-set

posts

The central content table. Supports draft, published, and scheduled statuses with full-text search.

Column Type Notes
id uuid (PK) Auto-generated
title text Post title
slug text (unique) URL-safe identifier
excerpt text Short summary for listings
content text Full post body (HTML from rich text editor)
featured_image text Hero image URL
category_id uuid (FK) References categories.id
author_id uuid (FK) References profiles.id
status text draft, published, or scheduled
published_at timestamptz When the post went live
scheduled_at timestamptz Future publish date for scheduled posts
reading_time integer Estimated minutes to read (auto-calculated)
views integer View count (incremented via RPC)
meta_title text SEO title override
meta_description text SEO description override
fts tsvector Full-text search index on title, excerpt, and content
created_at timestamptz Auto-set
updated_at timestamptz Auto-updated by trigger

post_tags

Junction table linking posts to tags.

Column Type Notes
post_id uuid (FK) References posts.id
tag_id uuid (FK) References tags.id
Primary key composite (post_id, tag_id)

comments

Threaded comments on posts. Public visitors can submit without authentication; comments start as pending.

Column Type Notes
id uuid (PK) Auto-generated
post_id uuid (FK) References posts.id
author_name text Commenter's display name
author_email text Commenter's email (not displayed publicly)
content text Comment body
status text approved, pending, or spam
parent_id uuid (FK, nullable) References comments.id for threading
created_at timestamptz Auto-set

media

Media library for images and files uploaded via Supabase Storage.

Column Type Notes
id uuid (PK) Auto-generated
filename text Original file name
url text Public URL
alt_text text Accessibility alt text
width integer Image width in pixels
height integer Image height in pixels
file_size integer File size in bytes
storage_path text Path within Supabase Storage bucket
uploaded_by uuid (FK) References profiles.id
created_at timestamptz Auto-set

subscribers

Newsletter subscriber list. Public visitors can subscribe without authentication.

Column Type Notes
id uuid (PK) Auto-generated
email text (unique) Subscriber email address
source text Where they signed up (e.g., "homepage", "post", "newsletter")
status text active or unsubscribed
created_at timestamptz Auto-set

newsletter_issues

Archive of sent newsletter issues.

Column Type Notes
id uuid (PK) Auto-generated
title text Newsletter subject line
description text Newsletter body or summary
sent_at timestamptz When it was sent
recipient_count integer Number of recipients
created_at timestamptz Auto-set

site_settings

Global blog configuration (single row).

Column Type Notes
id uuid (PK) Auto-generated
blog_name text Blog title
description text Blog tagline/description
logo_url text Blog logo URL
meta_title_template text Default SEO title template (e.g., `%s
meta_description_template text Default SEO description template
analytics_id text Google Analytics measurement ID
updated_at timestamptz Auto-updated by trigger

pageview_daily

Aggregated daily analytics. Each row represents one day of traffic.

Column Type Notes
date date (PK) Calendar date
views integer Total page views for the day
unique_visitors integer Unique visitor count for the day

Row-Level Security

RLS is enabled on every table. Unlike the CRM kit (which uses a team-only shared workspace model), this blog kit uses a public-facing model -- anonymous visitors can read published content, while write operations require authentication and are scoped by role.

Helper Functions

-- Check if the current user is a blog admin
CREATE OR REPLACE FUNCTION is_blog_admin()
RETURNS boolean AS $$
  SELECT EXISTS (
    SELECT 1 FROM public.profiles
    WHERE id = auth.uid() AND role = 'admin'
  );
$$ LANGUAGE sql SECURITY DEFINER;

-- Check if the current user is an editor or above (editor, admin)
CREATE OR REPLACE FUNCTION is_blog_editor_or_above()
RETURNS boolean AS $$
  SELECT EXISTS (
    SELECT 1 FROM public.profiles
    WHERE id = auth.uid() AND role IN ('editor', 'admin')
  );
$$ LANGUAGE sql SECURITY DEFINER;

-- Check if the current user is an author or above (author, editor, admin)
CREATE OR REPLACE FUNCTION is_blog_author_or_above()
RETURNS boolean AS $$
  SELECT EXISTS (
    SELECT 1 FROM public.profiles
    WHERE id = auth.uid() AND role IN ('author', 'editor', 'admin')
  );
$$ LANGUAGE sql SECURITY DEFINER;

Policy Summary

Table Public Read Public Write Authors+ Editors+ Admins
profiles All profiles - Update own profile - Manage all profiles
categories All - - - CRUD all
tags All - Insert - Delete
posts Published only - Insert, update own CRUD all posts Delete any
post_tags All (via post) - Manage own post tags Manage all -
comments Approved only Insert (pending) - Approve, reject, delete Delete any
media - - Insert (upload) CRUD all Delete any
subscribers - Insert (signup) - Read, delete Manage all
newsletter_issues All - - Insert, update Delete
site_settings All - - - Update
pageview_daily - - - Read Read

Key RLS Differences from CRM Kit

  1. Public read access -- profiles, categories, tags, published posts, approved comments, and newsletter issues are readable without authentication
  2. Public insert -- anonymous visitors can submit comments (created as pending) and subscribe to the newsletter
  3. Three-tier role model -- author, editor, admin (vs. CRM's two-tier member/admin)
  4. Content ownership -- authors can only edit their own posts, while editors can edit all posts

Triggers

Profile Auto-Creation

When a new user signs up, a trigger automatically creates their profile:

CREATE OR REPLACE FUNCTION handle_new_user()
RETURNS trigger AS $$
BEGIN
  INSERT INTO public.profiles (id, full_name, avatar_url, email, role)
  VALUES (
    new.id,
    coalesce(new.raw_user_meta_data ->> 'full_name', ''),
    coalesce(new.raw_user_meta_data ->> 'avatar_url', ''),
    new.email,
    'author'
  );
  RETURN new;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

New users are assigned the author role by default.

Auto-Update updated_at

All tables with an updated_at column have a trigger that sets it to now() on every update.

Increment Post Views (RPC)

An RPC function safely increments a post's view count without requiring authentication:

CREATE OR REPLACE FUNCTION increment_post_views(post_slug text)
RETURNS void AS $$
  UPDATE posts
  SET views = views + 1
  WHERE slug = post_slug AND status = 'published';
$$ LANGUAGE sql SECURITY DEFINER;

This is called from the public article page on load. Using SECURITY DEFINER allows the function to bypass RLS so anonymous visitors can trigger view increments.

Auto-Calculate Reading Time

When a post is inserted or its content is updated, a trigger calculates the estimated reading time:

CREATE OR REPLACE FUNCTION calculate_reading_time()
RETURNS trigger AS $$
BEGIN
  NEW.reading_time := GREATEST(1, array_length(
    regexp_split_to_array(strip_tags(NEW.content), '\s+'), 1
  ) / 200);
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

Assumes an average reading speed of 200 words per minute.

Seed Data

The supabase/seed.sql file includes:

Entity Count Notes
Authors (profiles) 5 Including 1 admin: admin@example.com / password123
Categories 6 Technology, Design, Business, etc. with oklch colors
Tags 18 React, TypeScript, CSS, Accessibility, etc.
Posts 15 Mix of published, draft, and scheduled; various categories and authors
Post-tag joins ~40 2-4 tags per post
Comments 20 Mix of approved, pending, and spam; some threaded
Media items 12 Images with dimensions, alt text, and storage paths
Subscribers 18 Various sources and statuses
Newsletter issues 5 With sent dates and recipient counts
Site settings 1 Default blog configuration
Pageview daily 30 30 days of traffic data with realistic variance

Storage

The kit uses a public Supabase Storage bucket:

  • Bucket name: blog-assets
  • Access: Public read (images served directly via public URL)
  • Upload policy: Authenticated users with author role or above
  • Delete policy: Editors and admins only

Used for post featured images, media library uploads, author avatars, and category images.

Regenerating Types

After modifying the schema, regenerate TypeScript types:

npx supabase gen types typescript --project-id your-project-id > types/database.ts

This updates the typed Supabase client used throughout the application.

Previous

Project Structure

Next

Authentication