PostgreSQL Schema Patterns

Community-maintained schema patterns for building modern applications with PostgreSQL. Contributed by InsForge.

Each pattern includes schema design, Row Level Security policies, SDK examples, and performance best practices. Works with any PostgREST-based backend.

When to Use This Skill

Reference these patterns when:

• Designing database schemas for common app features
• Implementing Row Level Security (RLS) policies
• Writing PostgREST/SDK queries with relationships
• Optimizing queries for PostgREST

Available Patterns

Pattern Structure

Each pattern file includes:

• Schema - Table definitions with constraints and indexes
• Row Level Security - RLS policies for secure access
• SDK Usage - Common query patterns (PostgREST-compatible)
• Best Practices - Indexing, performance, and optimization tips
• Common Mistakes - Pitfalls to avoid

Quick Reference

SDK Query Patterns

// Foreign key expansion (get related data)
.select('*, author:user_id(id, profile)')

// Count aggregation
.select('*, likes(count)')

// Inner join (filter by related table)
.select('*, likes!inner(id)')
.eq('likes.user_id', currentUserId)

// Check existence without fetching
.select('id')
.single();
const exists = !!data;

// Count without fetching rows
.select('*', { count: 'exact', head: true })

Essential SQL Patterns

-- Always index foreign keys
CREATE INDEX idx_table_fk ON table(foreign_key_column);

-- Prevent duplicates in junction tables
UNIQUE(user_id, post_id)

-- Self-referential (nested structures)
parent_id UUID REFERENCES same_table(id) ON DELETE CASCADE

-- Cascade deletes for cleanup
REFERENCES parent(id) ON DELETE CASCADE

-- Role-based checks
CHECK (role IN ('owner', 'admin', 'member'))

RLS Essentials

-- Enable RLS
ALTER TABLE mytable ENABLE ROW LEVEL SECURITY;

-- Public read
CREATE POLICY "Anyone can read" ON mytable
  FOR SELECT USING (true);

-- Owner-only write
CREATE POLICY "Owner can modify" ON mytable
  FOR ALL
  TO authenticated
  USING (uid() = user_id)
  WITH CHECK (uid() = user_id);

-- Use functions for complex checks (better performance)
CREATE FUNCTION is_member(org_id UUID) RETURNS BOOLEAN AS $$
  SELECT EXISTS (SELECT 1 FROM members WHERE organization_id = org_id AND user_id = uid());
$$ LANGUAGE sql SECURITY DEFINER;

About

This skill focuses on schema design patterns - how to model common app features in PostgreSQL. Each pattern includes embedded performance tips and best practices.

Maintained by the InsForge team as a contribution to the developer community. PRs welcome!