Chapter 2: Supabase Database and SQL Schema Generation

A powerful official website can't just have static pages - we need a database to store member data, booking records, and contact forms. We'll use Supabase, an open-source Firebase alternative backed by the incredibly powerful PostgreSQL database!

🎯 Chapter Goals

1. Register and create a Supabase project.
2. Learn how to use Vibe Coding to have AI design our Database Schema (table structure).
3. Execute the AI-generated SQL in Supabase.
4. Obtain API keys and configure environment variables (.env).

---

🛠️ Step 1: Create a Supabase Project

1. Go to Supabase's official website to register and log in.
2. Click New Project.
3. Fill in the Project Name (e.g., not_far_web_db).
4. Set a Database Password (make sure to remember it or let your browser's password manager save it).
5. Select the region closest to Taiwan (e.g., Tokyo or Singapore).
6. Click Create new project.

(This process may take a few minutes for Supabase to initialize the database)


---

🧠 Step 2: Generate SQL Schema with Vibe Coding

This is where Vibe Coding shows its true power! We don't need to manually write lengthy SQL.

[!TIP] Vibe Prompt (copy and send to AI): I'm developing a website called "Not Far Camping Resort" using Supabase (PostgreSQL) as backend. Please design a database Schema for me with three tables: 1. users (user table that integrates with supabase auth, including id, email, created_at, full_name, phone_number) 2. bookings (booking table for campsite reservations, including id, user_id(reference to users), check_in_date, check_out_date, status, total_price, created_at) 3. contact_messages (contact form table for visitor messages, including id, name, email, message, is_read, created_at) Please provide complete SQL syntax ready for Supabase SQL Editor, including PRIMARY KEY, FOREIGN KEY, with RLS (Row Level Security) enabled and basic Policies comments.

The AI will generate clean SQL like this:

-- Create user extension table (matching Supabase Auth)
CREATE TABLE public.users (
  id UUID REFERENCES auth.users(id) ON DELETE CASCADE PRIMARY KEY,
  email TEXT UNIQUE NOT NULL,
  full_name TEXT,
  phone_number TEXT,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Create bookings table
CREATE TABLE public.bookings (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  user_id UUID REFERENCES public.users(id) ON DELETE CASCADE NOT NULL,
  check_in_date DATE NOT NULL,
  check_out_date DATE NOT NULL,
  status TEXT DEFAULT 'pending',
  total_price DECIMAL(10,2) NOT NULL,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Create contact messages table
CREATE TABLE public.contact_messages (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  name TEXT NOT NULL,
  email TEXT NOT NULL,
  message TEXT NOT NULL,
  is_read BOOLEAN DEFAULT false,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Enable RLS (security protection)
ALTER TABLE public.users ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.bookings ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.contact_messages ENABLE ROW LEVEL SECURITY;


---

⚡ Step 3: Execute the SQL

1. Return to Supabase dashboard.
2. Click SQL Editor in the left menu.
3. Click New query.
4. Paste the SQL generated by AI.
5. Click Run in the bottom right!

When you see Success, your database is fully set up - wasn't that incredibly fast?

 

---

🔑 Step 4: Configure Project Environment Variables (.env)

For our Astro website to connect to this database, we need API keys.

1. In Supabase dashboard, go to bottom-left Project Settings (gear icon).
2. Select API.
3. You'll see Project URL and Project API keys (anon, public).

Return to your VSCode/Cursor project root directory and create a file named .env.

[!TIP] Vibe Prompt (copy and send to AI): I obtained Supabase URL and Anon Key. How should I write my .env file in an Astro project? What naming convention should I use for these variables (e.g., PUBLIC prefix)?

Your .env should contain:

PUBLIC_SUPABASE_URL=your_Project_URL_here
PUBLIC_SUPABASE_ANON_KEY=your_Anon_Key_here

 

---

✅ Chapter Summary

You've learned how to delegate database design - a traditionally complex task - to AI! As long as your logic is clear, AI can instantly generate perfect SQL Schema. Next chapter, we'll return to frontend development and use React with Tailwind CSS to build a stunning homepage (Hero Section) with dynamic gradient effects!