MyBlog Schema Design and Implementation

-- User role types
CREATE TYPE public.user_role AS ENUM ('Admin', 'Plus', 'Normal');

-- Post visibility types
CREATE TYPE public.post_visibility AS ENUM ('Public', 'Plus', 'Admin');

-- 1. Function: Handle new user data synchronization
create or replace function public.handle_new_user()
returns trigger
language plpgsql
security definer set search_path = public
as $$
begin 
    -- Try to extract avatar URL and username/full name from raw_user_meta_data
    declare 
        avatar_url text := new.raw_user_meta_data ->> 'avatar_url';
        username text := new.raw_user_meta_data ->> 'user_name'; -- GitHub
        full_name text := new.raw_user_meta_data ->> 'full_name'; -- Google
        user_email text := new.email; -- Get user's email
        final_username text;
    begin
    
        -- Prioritize OAuth-provided username/full name, otherwise generate from email
        if username is not null and username <> '' then
            final_username := username;
        elsif full_name is not null and full_name <> '' then
            final_username := replace(lower(full_name), ' ', '_'); -- Simple processing
        else
            final_username := split_part(user_email, '@', 1);
        end if;
        
        -- Check username uniqueness, add random suffix if conflict (improve this logic as needed)
        if exists (select 1 from public.profiles where profiles.username = final_username) then
            final_username := final_username || '_' || substr(md5(random()::text), 1, 4);
        end if;
        
        -- Insert new user information into profiles table
        insert into public.profiles (id, email, username, avatar_url)
        values (
            new.id,
            user_email,
            final_username,
            avatar_url
        )
        on conflict (id) do update set -- Handle potential conflicts
            avatar_url = excluded.avatar_url,
            email = excluded.email,
            username = excluded.username;
    end;
    return new;
end;
$$;

-- 2. Trigger: Call function after inserting new record in auth.users
create trigger on_auth_user_created
    after insert on auth.users
    for each row execute procedure public.handle_new_user();

-- 1. Create a function to insert or update user info in the profiles table
-- This function extracts email, id, and avatar URL from the auth.users table
create or replace function public.handle_new_user()
returns trigger
language plpgsql
-- SECURITY DEFINER allows the function to run with the privileges of the role that created it (usually postgres).
-- This is necessary to read from auth.users and write to public.profiles.
-- SET search_path ensures the function looks for tables in the public schema.
security definer set search_path = public
as $$
begin
  -- Attempt to extract the avatar URL from raw_user_meta_data, defaults to NULL if not present
  declare
    avatar_url text := new.raw_user_meta_data ->> 'avatar_url';
    username text := new.raw_user_meta_data ->> 'user_name'; -- GitHub might provide user_name
    full_name text := new.raw_user_meta_data ->> 'full_name'; -- Google might provide full_name
    final_username text;
  begin

    -- Try to use the username or full name from metadata, generate one from email if neither exists
    if username is not null and username <> '' then
      final_username := username;
    elsif full_name is not null and full_name <> '' then
      -- Simply use the full name as username, or process it (e.g., replace spaces)
      final_username := replace(lower(full_name), ' ', '_');
    else
      -- Generate a basic username from email (remove @ and subsequent parts)
      final_username := split_part(new.email, '@', 1);
    end if;

    -- Check if the generated username already exists, append a random suffix if it does (simple example)
    -- You might want a more robust uniqueness strategy
    if exists (select 1 from public.profiles where profiles.username = final_username) then
      final_username := final_username || '_' || substr(md5(random()::text), 1, 4);
    end if;

    insert into public.profiles (id, email, username, avatar_url)
    values (
      new.id,
      new.email,
      final_username,
      avatar_url -- Use the extracted avatar_url from above
    )
    -- Using ON CONFLICT just in case, though unlikely for an AFTER INSERT trigger on auth.users
    -- This ensures if a profile with the ID somehow exists, it gets updated.
    on conflict (id) do update set
      avatar_url = excluded.avatar_url,
      email = excluded.email; -- Optionally update the email as well
  end;
  return new; -- The result is ignored since it's an AFTER trigger, but required syntax
end;
$$;

-- 2. Create a trigger that calls the above function after each new record is inserted into auth.users
create trigger on_auth_user_created
  after insert on auth.users
  for each row execute procedure public.handle_new_user();

-- Note: You might also need to handle cases where users update their avatars,
-- e.g., allowing users to manually upload or change the avatar URL in their profile settings.
-- Syncing avatar updates from the OAuth provider later is more complex,
-- it's generally recommended to let users manage their avatars within your application after the initial signup.

-- (Optional) Function: Update updated_at timestamp
-- This function can be reused across multiple tables
CREATE OR REPLACE FUNCTION public.update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
   NEW.updated_at = now(); -- Set the updated_at column to the current timestamp
   RETURN NEW; -- Return the modified row
END;
$$ language 'plpgsql';

-- (Optional) Trigger: Automatically update updated_at for the posts table
-- Create this trigger if your 'posts' table has an 'updated_at' column
CREATE TRIGGER handle_posts_updated_at BEFORE UPDATE ON public.posts
  FOR EACH ROW EXECUTE PROCEDURE public.update_updated_at_column();

-- (Optional) Trigger: Automatically update updated_at for the profiles table
-- Create this trigger if your 'profiles' table has an 'updated_at' column
CREATE TRIGGER handle_profiles_updated_at BEFORE UPDATE ON public.profiles
  FOR EACH ROW EXECUTE PROCEDURE public.update_updated_at_column();

-- (Optional) Trigger: Automatically update updated_at for the projects table
-- Create this trigger if your 'projects' table has an 'updated_at' column
CREATE TRIGGER handle_projects_updated_at BEFORE UPDATE ON public.projects
  FOR EACH ROW EXECUTE PROCEDURE public.update_updated_at_column();

-- (Optional) Trigger: Automatically update updated_at for the comments table
-- Create this trigger if your 'comments' table has an 'updated_at' column
CREATE TRIGGER handle_comments_updated_at BEFORE UPDATE ON public.comments
  FOR EACH ROW EXECUTE PROCEDURE public.update_updated_at_column();

npm install slugify
# or
yarn add slugify

import slugify from 'slugify';
import { createClient } from '@/utils/supabase/server'; // Adjust import for your Supabase client (server-side)

async function createPost(formData) {
  'use server'; // Necessary directive for Next.js Server Actions

  const supabase = createClient(); // Initialize Supabase client for server-side operations

  const title = formData.get('title');
  const content = formData.get('content');
  // ... other fields from your form

  if (!title) {
    // Handle error: title is required
    return { error: 'Title is required.' };
  }

  let generatedSlug = slugify(title, {
    lower: true,      // convert to lower case
    strict: true,     // strip special characters defined by the library except -
    remove: /[*+~.()'"!:@]/g, // specify additional characters to remove
    trim: true        // trim leading/trailing spaces
  });

  // --- Logic needed here to check if the slug already exists ---
  // Important: Always check for slug uniqueness before inserting
  let isUnique = false;
  let attempt = 0;
  const maxAttempts = 5; // Limit attempts to avoid infinite loops
  let finalSlug = generatedSlug;

  while (!isUnique && attempt <= maxAttempts) {
    const { data: existingPost, error: checkError } = await supabase
     .from('posts') // Replace 'posts' with your actual table name
     .select('id')
     .eq('slug', finalSlug)
     .maybeSingle(); // Check if any post already has this slug

    if (checkError) {
        console.error('Error checking slug uniqueness:', checkError);
        return { error: 'Failed to check slug uniqueness.' }; // Handle database error
    }

    if (!existingPost) {
      isUnique = true; // Slug is unique
    } else {
      // Handle duplicate slug: append a counter or random suffix
      attempt++;
      if (attempt > maxAttempts) {
          // If still not unique after several attempts, return error or use a different strategy
          console.error('Failed to generate a unique slug for:', title);
          return { error: 'Could not generate a unique slug.' };
      }
      // Append a short random string or a counter
      finalSlug = `${generatedSlug}-${Math.random().toString(36).substring(2, 6)}`;
    }
  }

  if (!isUnique) {
      // Should not happen if maxAttempts logic is correct, but as a safeguard
      return { error: 'Failed to ensure slug uniqueness after multiple attempts.' };
  }
  // --- End of check logic ---

  // Then insert the finalSlug along with other data into the database
  const { data, error } = await supabase
    .from('posts') // Replace 'posts' with your actual table name
    .insert([{
      title: title,
      content: content,
      slug: finalSlug, // Use the generated and validated unique slug
      // user_id: ... // Make sure to include the user ID, etc.
      // ... other fields
    }])
    .select() // Optionally select the inserted data
    .single(); // Assuming you insert one post

  if (error) {
    console.error('Error creating post:', error);
    // Handle specific Supabase errors if needed
    return { error: `Failed to create post: ${error.message}` };
  } else {
    // Handle success, maybe revalidate paths for ISR/SSR or redirect
    // Example: revalidatePath('/blog');
    // Example: redirect(`/blog/${finalSlug}`);
    console.log('Post created successfully:', data);
    return { success: true, post: data };
  }
}