PostgreSQL

PostgreSQL is a powerful, open-source relational database management system that forms the foundation of Supabase.

Introduction to PostgreSQL

PostgreSQL (often called "Postgres") is an advanced, enterprise-class database system with over 30 years of active development. Key features include:

• ACID Compliance: Ensuring data validity despite errors or failures
• Advanced Data Types: Including JSON, arrays, hstore, and geometric types
• Extensibility: Custom functions, operators, data types, and more
• Concurrent Support: Multi-version concurrency control (MVCC)
• Full-Text Search: Built-in indexing and searching capabilities
• Advanced Indexing: B-tree, Hash, GiST, SP-GiST, GIN, and BRIN

Connection Through Supabase

In VibeReference, you'll typically interact with PostgreSQL through Supabase, which provides a user-friendly interface and API for database operations:

// lib/supabase/client.ts
import { createClient } from '@supabase/supabase-js';

export const supabase = createClient(
  process.env.NEXT_PUBLIC_SUPABASE_URL!,
  process.env.NEXT_PUBLIC_SUPABASE_ANON_KEY!
);

// Query example
const { data, error } = await supabase
  .from('your_table')
  .select('*')
  .eq('status', 'active');

Schema Design

Basic Schema Example

Here's a simple schema example for a VibeReference application:

-- Users table (extends Supabase auth.users)
CREATE TABLE public.profiles (
  id UUID REFERENCES auth.users(id) PRIMARY KEY,
  display_name TEXT,
  avatar_url TEXT,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT now(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT now()
);

-- Projects table
CREATE TABLE public.projects (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  name TEXT NOT NULL,
  description TEXT,
  owner_id UUID REFERENCES public.profiles(id) NOT NULL,
  is_public BOOLEAN DEFAULT false,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT now(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT now()
);

-- Project members junction table
CREATE TABLE public.project_members (
  project_id UUID REFERENCES public.projects(id) ON DELETE CASCADE,
  user_id UUID REFERENCES public.profiles(id) ON DELETE CASCADE,
  role TEXT NOT NULL CHECK (role IN ('admin', 'editor', 'viewer')),
  created_at TIMESTAMP WITH TIME ZONE DEFAULT now(),
  PRIMARY KEY (project_id, user_id)
);

Data Types

PostgreSQL supports a rich set of data types:

Row Level Security (RLS)

PostgreSQL's Row Level Security feature is a cornerstone of securing your VibeReference application. RLS allows you to define policies that restrict which rows a user can access.

Example RLS Policy

-- Enable RLS on the projects table
ALTER TABLE public.projects ENABLE ROW LEVEL SECURITY;

-- Create policy for viewing projects
CREATE POLICY view_projects ON public.projects
  FOR SELECT
  USING (
    is_public OR                                  -- Public projects are visible to all
    owner_id = auth.uid() OR                      -- Owner can see their projects
    EXISTS (                                       -- Members can see their projects
      SELECT 1 FROM public.project_members
      WHERE project_id = projects.id AND user_id = auth.uid()
    )
  );

-- Create policy for updating projects
CREATE POLICY update_projects ON public.projects
  FOR UPDATE
  USING (
    owner_id = auth.uid() OR                      -- Owner can update
    EXISTS (                                       -- Admins and editors can update
      SELECT 1 FROM public.project_members
      WHERE project_id = projects.id 
      AND user_id = auth.uid() 
      AND role IN ('admin', 'editor')
    )
  );

Common PostgreSQL Operations

SELECT Queries

-- Basic select
SELECT * FROM projects WHERE is_public = true;

-- Joins
SELECT p.name, pr.display_name as owner
FROM projects p
JOIN profiles pr ON p.owner_id = pr.id;

-- Aggregation
SELECT COUNT(*), owner_id 
FROM projects 
GROUP BY owner_id
HAVING COUNT(*) > 5;

-- Window functions
SELECT 
  p.name, 
  p.created_at,
  ROW_NUMBER() OVER(PARTITION BY p.owner_id ORDER BY p.created_at DESC) as row_num
FROM projects p;

Data Manipulation

-- Insert
INSERT INTO projects (name, description, owner_id, is_public)
VALUES ('New Project', 'Description here', 'user-uuid', true);

-- Update
UPDATE projects 
SET name = 'Updated Name', updated_at = now()
WHERE id = 'project-uuid';

-- Delete
DELETE FROM projects WHERE id = 'project-uuid';

Using JSON

-- Query JSON data
SELECT data->>'name' as name
FROM resources
WHERE data->>'type' = 'document';

-- Update JSON data
UPDATE resources 
SET data = jsonb_set(data, '{status}', '"archived"')
WHERE id = 'resource-uuid';

Real-time Features with Supabase

PostgreSQL's LISTEN/NOTIFY feature powers Supabase's real-time capabilities:

// Subscribe to changes on the projects table
const channel = supabase
  .channel('schema-db-changes')
  .on(
    'postgres_changes',
    {
      event: '*', // Listen to all changes
      schema: 'public',
      table: 'projects',
    },
    (payload) => {
      console.log('Change received!', payload);
      // Update UI or state based on the change
    }
  )
  .subscribe();

Migrations and Schema Management

For VibeReference projects, it's recommended to manage your PostgreSQL schema using migration files:

-- migrations/001_initial_schema.sql
CREATE TABLE public.profiles (
  id UUID REFERENCES auth.users(id) PRIMARY KEY,
  display_name TEXT,
  avatar_url TEXT,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT now(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT now()
);

-- More table definitions...

-- Apply migrations from your application or using Supabase migrations

Performance Optimization

Indexing

-- B-tree index (default, good for equality and range queries)
CREATE INDEX idx_projects_owner ON projects(owner_id);

-- Unique index
CREATE UNIQUE INDEX idx_projects_name_owner ON projects(name, owner_id);

-- Partial index
CREATE INDEX idx_active_projects ON projects(created_at) 
WHERE is_archived = false;

-- Text search index
CREATE INDEX idx_projects_description_gin ON projects 
USING GIN (to_tsvector('english', description));

Query Optimization

• Use EXPLAIN ANALYZE to understand query execution plans
• Consider denormalization for frequently accessed data
• Use appropriate indexes for your query patterns
• Keep statistics up to date with regular ANALYZE

Resources

• PostgreSQL Documentation
• Supabase PostgreSQL Documentation
• SQL Tutorial
• PostgreSQL Weekly Newsletter
• pgAdmin - GUI tool for PostgreSQL