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.

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.

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

Authors Table

The authors table stores the details of article authors.

CREATE TABLE authors (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    slug TEXT UNIQUE NOT NULL,
    name TEXT NOT NULL,
    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.

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.

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.

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'),
    PRIMARY KEY (article_id, category_id)
);

Enabling RLS on the Tables

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

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

These policies ensure public read access to the tables:

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);

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.

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.

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();

Final Remarks

With the tables and triggers set up, you can now add articles, authors, and categories directly in the Supabase SQL Editor or Table Editor. Stay tuned as we work on a dedicated UI for easier management.

In the meantime, feel free to add your own data or follow the steps to insert dummy data in the Initial Data section.

Coupons Initial Data