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 Use Case Complexity
Social Graph Follows, connections, networks Medium
Likes Likes, favorites, bookmarks Simple
Nested Comments Threaded comments, replies Medium
Multi-Tenant Organizations, workspaces, SaaS Advanced
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!