Setting up Blog in StartupBolt

Overview

In this guide, we'll walk you through the process of configuring a blog for StartupBolt. You'll learn how to create tables to manage articles, categories, and authors, set up essential RPC functions, and configure triggers.

Customizing the Blog

Go to settings.js, located at /settings.js. This file is the central configuration hub for your StartupBolt application. It contains various settings that control different aspects of your app, enabling easy customization and management of your project's core parameters.

Update the BLOG object as needed:

const BLOG = {
  name: "Blog",
  url: "/blog", // Change this ONLY if you want to use a different URL for your blog. Example: "/articles"
  meta_title: "StartupBolt Blog",
  meta_description: "Dive into our recent blog articles to learn about AI, SaaS, and web development. Stay updated, gain insights, and enhance your skills. Start exploring now!",
  title: "StartupBolt Blog", 
  description: "Dive into our recent blog articles to learn about AI, SaaS, and web development. Stay updated, gain insights, and enhance your skills. Start exploring now!",
}

To customize the base blog route (default: /blog):

Rename the "blog" folder located at /app/blog to your desired name (e.g., "articles").
Update BLOG.url in settings.js to match the new route name (e.g., "/articles").

Field Descriptions:

name: The name of the blog page.
url: The URL of the blog page.
meta_title: The title of the blog page for SEO purposes.
meta_description: The description of the blog page for SEO purposes.
title: The title displayed on the blog page.
description: The description displayed on the blog page.

Creating the Tables

To get started, navigate to Supabase > SQL Editor > New Query or Create a new snippet, paste the provided SQL queries, and execute them. Run each query separately by creating a new snippet.

Authors Table

The authors table stores the details of article authors.

-- Authors Table
CREATE TABLE authors (
    id UUID PRIMARY KEY REFERENCES auth.users(id), -- Direct link to auth.users
    slug TEXT UNIQUE NOT NULL,
    name TEXT NOT NULL, -- Can be different from auth.users name
    bio TEXT,
    meta_bio TEXT,
    avatar TEXT,
    role TEXT,
    twitter_handle TEXT,
    linkedin_handle TEXT,
    github_handle TEXT,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT (now() AT TIME ZONE 'UTC'),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT (now() AT TIME ZONE 'UTC')
);

Categories Table

The categories table stores the details of blog categories.

-- Categories Table
CREATE TABLE categories (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    slug TEXT UNIQUE NOT NULL,
    name TEXT NOT NULL,
    title TEXT,
    description TEXT,
    meta_title TEXT,
    meta_description TEXT,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT (now() AT TIME ZONE 'UTC'),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT (now() AT TIME ZONE 'UTC')
);

Articles Table

The articles table stores blog articles.

-- Articles Table
CREATE TABLE articles (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    slug TEXT UNIQUE NOT NULL,
    title TEXT NOT NULL,
    content TEXT NOT NULL,
    excerpt TEXT,
    meta_title TEXT,
    meta_description TEXT,
    featured_image TEXT,
    published_at TIMESTAMP WITH TIME ZONE,
    author_id UUID REFERENCES authors(id),
    created_at TIMESTAMP WITH TIME ZONE DEFAULT (now() AT TIME ZONE 'UTC'),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT (now() AT TIME ZONE 'UTC')
);

Create Article Categories table

This junction table links articles to categories, enabling a many-to-many relationship. This is required because each article can have multiple categories and each category can have multiple articles.

-- Article Categories Table
CREATE TABLE article_categories (
    article_id UUID REFERENCES articles(id) ON DELETE CASCADE,
    category_id UUID REFERENCES categories(id) ON DELETE CASCADE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT (now() AT TIME ZONE 'UTC'),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT (now() AT TIME ZONE 'UTC'),
    PRIMARY KEY (article_id, category_id)
);

Updating Timestamps Automatically

Step 1: Create Update Function

Create a function to automatically update the updated_at timestamp whenever a record is modified.

Skip this step if you have already set up the update_table_timestamp function in Supabase by following the Supabase Setup guide when you first configured your Supabase.

-- Helper Function to update timestamp
CREATE OR REPLACE FUNCTION public.update_table_timestamp()
RETURNS TRIGGER
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = ''
AS $$
BEGIN
    NEW.updated_at = now() AT TIME ZONE 'UTC';
    RETURN NEW;
END;
$$;

Step 2: Create Triggers

Then, set up the trigger to call this function before every update on the tables.

-- Triggers
CREATE TRIGGER handle_authors_updated_at
BEFORE UPDATE ON public.authors
FOR EACH ROW
EXECUTE FUNCTION update_table_timestamp();
 
CREATE TRIGGER handle_categories_updated_at
BEFORE UPDATE ON public.categories
FOR EACH ROW
EXECUTE FUNCTION update_table_timestamp();
 
CREATE TRIGGER handle_articles_updated_at
BEFORE UPDATE ON public.articles
FOR EACH ROW
EXECUTE FUNCTION update_table_timestamp();
 
CREATE TRIGGER handle_article_categories_updated_at
BEFORE UPDATE ON public.article_categories
FOR EACH ROW
EXECUTE FUNCTION update_table_timestamp();

Enabling RLS on the Tables

Enable Row-Level Security (RLS) for secure data access:

-- Enable RLS
ALTER TABLE public.authors ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.categories ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.articles ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.article_categories ENABLE ROW LEVEL SECURITY;

Creating RLS Policies for the Tables

Public Read Access

These policies allow anyone to read the authors, categories, articles, and article_categories tables without authentication. This ensures public access to the necessary blog data.

CREATE POLICY "Public read access to authors"
ON public.authors FOR SELECT
TO public
USING (true);
 
CREATE POLICY "Public read access to categories"
ON public.categories FOR SELECT
TO public
USING (true);
 
CREATE POLICY "Public read access to articles"
ON public.articles FOR SELECT
TO public
USING (true);
 
CREATE POLICY "Public read access to article_categories"
ON public.article_categories FOR SELECT
TO public
USING (true);

Author Policies

These policies ensure that users can manage their own author profiles while allowing administrators and editors to have full control.

Insert Policy: Authenticated users can create their own author profile. Admins and editors can create profiles for others.
Update Policy: Users can update their own profile. Admins and editors can modify any author profile.
Delete Policy: Users can delete their own profile. Admins and editors can remove any author profile.

Note: Make sure that has_any_role function is set up in the Supabase project as we will use it in the policies below. Please refer to the Supabase Roles guide for more information.

-- Author policies - allow users to manage their own profile (Admin/Editor/Author)
CREATE POLICY "Can insert author profile"
ON public.authors FOR INSERT
TO authenticated
WITH CHECK (
    has_any_role(auth.uid(), ARRAY['admin', 'editor'])
    OR 
    id = auth.uid() -- Users can create their own profile
);
 
CREATE POLICY "Can update author profile"
ON public.authors FOR UPDATE
TO authenticated
USING (
    has_any_role(auth.uid(), ARRAY['admin', 'editor'])
    OR 
    id = auth.uid() -- Users can update their own profile
)
WITH CHECK (
    has_any_role(auth.uid(), ARRAY['admin', 'editor'])
    OR 
    id = auth.uid()
);
 
CREATE POLICY "Can delete author profile"
ON public.authors FOR DELETE
TO authenticated
USING (
    has_any_role(auth.uid(), ARRAY['admin', 'editor'])
    OR 
    id = auth.uid() -- Users can delete their own profile
);

Admin/Editor Category Policies

These policies ensure that only admins and editors can manage categories.

Insert Policy: Only admins and editors can create new categories.
Update Policy: Only admins and editors can modify existing categories.
Delete Policy: Only admins and editors can remove categories.

This restriction prevents unauthorized changes while allowing designated roles to organize and maintain category data.

Note: Make sure that has_any_role function is set up in the Supabase project as we will use it in the policies below. Please refer to the Supabase Roles guide for more information.

-- Admin/Editor Categories Write Policies
CREATE POLICY "Admin/Editor can insert categories"
ON public.categories FOR INSERT
TO authenticated
WITH CHECK (has_any_role(auth.uid(), ARRAY['admin', 'editor']));
 
CREATE POLICY "Admin/Editor can update categories"
ON public.categories FOR UPDATE
TO authenticated
USING (has_any_role(auth.uid(), ARRAY['admin', 'editor']))
WITH CHECK (has_any_role(auth.uid(), ARRAY['admin', 'editor']));
 
CREATE POLICY "Admin/Editor can delete categories"
ON public.categories FOR DELETE
TO authenticated
USING (has_any_role(auth.uid(), ARRAY['admin', 'editor']));

Article Write Policies

These policies define the permissions for creating, updating, and deleting articles based on user roles.

Insert Policy:
- Admins and editors can create any article.
- Authors can only create their own articles.
- Contributors can create only unpublished articles.
Update Policy:
- Admins and editors can edit any article.
- Authors can edit their own articles.
- Contributors can edit only their unpublished articles.
Delete Policy:
- Admins and editors can delete any article.
- Authors can delete their own articles.
- Contributors cannot delete articles.

These policies provide role based control for articles. Contributors can draft articles without publishing privileges.

-- Admin/Editor/Author/Contributor Articles Write Policies
CREATE POLICY "Can insert articles" 
ON public.articles FOR INSERT 
TO authenticated
WITH CHECK (
    -- Admin/Editor can insert any article
    has_any_role(auth.uid(), ARRAY['admin', 'editor'])
    OR 
    -- Authors can only insert their own articles
    (
        has_role(auth.uid(), 'author')
        AND author_id = auth.uid()
    )
    OR
    -- Contributors can only create unpublished posts
    (
        has_role(auth.uid(), 'contributor')
        AND author_id = auth.uid()
        AND published_at IS NULL  -- Contributors can only create unpublished posts
    )
);
 
CREATE POLICY "Can update articles"
ON public.articles FOR UPDATE
TO authenticated
USING (
    -- Admin/Editor can update any article
    has_any_role(auth.uid(), ARRAY['admin', 'editor'])
    OR 
    -- Authors can only update their own articles
    (
        has_role(auth.uid(), 'author')
        AND author_id = auth.uid()
    )
    OR 
    -- Contributors can only update unpublished posts
    (
        has_role(auth.uid(), 'contributor')
        AND author_id = auth.uid()
        AND published_at IS NULL  -- Only if not published
    )
)
WITH CHECK (
    has_any_role(auth.uid(), ARRAY['admin', 'editor'])
    OR 
    (
        has_role(auth.uid(), 'author')
        AND author_id = auth.uid()
    )
    OR 
    (
        has_role(auth.uid(), 'contributor')
        AND author_id = auth.uid()
        AND published_at IS NULL  -- Only if not published
    )
);
 
CREATE POLICY "Can delete articles"
ON public.articles FOR DELETE
TO authenticated
USING (
    has_any_role(auth.uid(), ARRAY['admin', 'editor'])
    OR 
    (
        has_role(auth.uid(), 'author')
        AND author_id = auth.uid()
    )
    -- No delete permission for contributors
);

Article Category Policies

These policies define who can assign and remove categories for articles while maintaining appropriate role-based restrictions.

Assigning Categories:
- Admins and editors can assign categories to any article.
- Authors can assign categories only to their own articles.
- Contributors can assign categories only to their own unpublished articles.
Removing Categories:
- Admins and editors can remove categories from any article.
- Authors can remove categories only from their own articles.
- Contributors can not remove categories from any article.

These policies ensure that only authorized roles manage article categorization.

-- Admin/Editor/Author/Contributor Article Categories Write Policies
CREATE POLICY "Can assign article categories"
ON public.article_categories FOR INSERT
TO authenticated
WITH CHECK (
    has_any_role(auth.uid(), ARRAY['admin', 'editor'])
    OR 
    (
        has_role(auth.uid(), 'author')
        AND article_id IN (
            SELECT id FROM articles
            WHERE author_id = auth.uid()
        )
    )
    OR
    (
        has_role(auth.uid(), 'contributor')
        AND article_id IN (
            SELECT id FROM articles 
            WHERE
                author_id = auth.uid()
                AND published_at IS NULL  -- Contributors can only categorize unpublished articles
        )
    )
);
 
CREATE POLICY "Can remove article categories"
ON public.article_categories FOR DELETE
TO authenticated
USING (
    has_any_role(auth.uid(), ARRAY['admin', 'editor'])
    OR 
    (
        has_role(auth.uid(), 'author')
        AND article_id IN (
            SELECT id FROM articles
            WHERE author_id = auth.uid()
        )
    )
    OR
    (
        has_role(auth.uid(), 'contributor')
        AND article_id IN (
            SELECT id FROM articles 
            WHERE
                author_id = auth.uid()
                AND published_at IS NULL  -- Contributors can only modify unpublished articles
        )
    )
);

Final Remarks

With the tables, triggers, and policies set up, your blog is now ready to use.

Next Steps

Coupons Blog Dashboard