InsForge Schema Patterns

Expert patterns for designing PostgreSQL schemas optimized for InsForge's PostgREST backend.

Pattern 1: Social Graph (Follows)

Use when: Building social features like Twitter, Instagram, LinkedIn connections

Schema:

CREATE TABLE follows ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), follower_id UUID REFERENCES users(id) ON DELETE CASCADE, following_id UUID REFERENCES users(id) ON DELETE CASCADE, created_at TIMESTAMPTZ DEFAULT NOW(), UNIQUE(follower_id, following_id) );

-- Index for fast lookups CREATE INDEX idx_follows_follower ON follows(follower_id); CREATE INDEX idx_follows_following ON follows(following_id);

-- RLS: Users can read all follows but only create their own ALTER TABLE follows ENABLE ROW LEVEL SECURITY;

CREATE POLICY "Anyone can read follows" ON follows FOR SELECT USING (true);

CREATE POLICY "Users can follow others" ON follows FOR INSERT TO authenticated WITH CHECK (uid() = follower_id);

CREATE POLICY "Users can unfollow" ON follows FOR DELETE TO authenticated USING (uid() = follower_id);

Query with InsForge SDK:

// Get users I follow with their profiles const { data: following } = await client.database .from('follows') .select('*, following:following_id(id, nickname, avatar_url, bio)') .eq('follower_id', currentUserId);

// Get my followers const { data: followers } = await client.database .from('follows') .select('*, follower:follower_id(id, nickname, avatar_url, bio)') .eq('following_id', currentUserId);

// Check if user1 follows user2 const { data: isFollowing } = await client.database .from('follows') .select() .eq('follower_id', user1Id) .eq('following_id', user2Id) .single();

// Follow a user await client.database .from('follows') .insert([{ follower_id: currentUserId, following_id: targetUserId }]);

Pattern 2: Likes (Many-to-Many Junction Table)

Use when: Users can like posts, comments, or other content

Schema:

CREATE TABLE likes ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), user_id UUID REFERENCES users(id) ON DELETE CASCADE, post_id UUID REFERENCES posts(id) ON DELETE CASCADE, created_at TIMESTAMPTZ DEFAULT NOW(), UNIQUE(user_id, post_id) -- Prevent duplicate likes );

CREATE INDEX idx_likes_post ON likes(post_id); CREATE INDEX idx_likes_user ON likes(user_id);

ALTER TABLE likes ENABLE ROW LEVEL SECURITY;

CREATE POLICY "Anyone can read likes" ON likes FOR SELECT USING (true);

CREATE POLICY "Users can like posts" ON likes FOR INSERT TO authenticated WITH CHECK (uid() = user_id);

CREATE POLICY "Users can unlike their likes" ON likes FOR DELETE TO authenticated USING (uid() = user_id);

Query with InsForge SDK:

// Get post with like count and whether current user liked it const { data: post } = await client.database .from('posts') .select( *, likes(count), user_like:likes!inner(id, user_id) ) .eq('id', postId) .eq('user_like.user_id', currentUserId) .single();

// Like a post await client.database .from('likes') .insert([{ user_id: currentUserId, post_id: postId }]);

// Unlike a post await client.database .from('likes') .delete() .eq('user_id', currentUserId) .eq('post_id', postId);

Pattern 3: Nested Comments (Self-Referential)

Use when: Building comment threads, nested replies

Schema:

CREATE TABLE comments ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), post_id UUID REFERENCES posts(id) ON DELETE CASCADE, user_id UUID REFERENCES users(id) ON DELETE CASCADE, parent_comment_id UUID REFERENCES comments(id) ON DELETE CASCADE, content TEXT NOT NULL, created_at TIMESTAMPTZ DEFAULT NOW(), updated_at TIMESTAMPTZ DEFAULT NOW() );

CREATE INDEX idx_comments_post ON comments(post_id); CREATE INDEX idx_comments_parent ON comments(parent_comment_id);

ALTER TABLE comments ENABLE ROW LEVEL SECURITY;

CREATE POLICY "Anyone can read comments" ON comments FOR SELECT USING (true);

CREATE POLICY "Authenticated users can comment" ON comments FOR INSERT TO authenticated WITH CHECK (uid() = user_id);

CREATE POLICY "Users can edit their comments" ON comments FOR UPDATE TO authenticated USING (uid() = user_id) WITH CHECK (uid() = user_id);

CREATE POLICY "Users can delete their comments" ON comments FOR DELETE TO authenticated USING (uid() = user_id);

Query with InsForge SDK:

// Get top-level comments with author info const { data: comments } = await client.database .from('comments') .select('*, author:user_id(nickname, avatar_url)') .eq('post_id', postId) .is('parent_comment_id', null) .order('created_at', { ascending: false });

// Get replies to a comment const { data: replies } = await client.database .from('comments') .select('*, author:user_id(nickname, avatar_url)') .eq('parent_comment_id', commentId) .order('created_at', { ascending: true });

Pattern 4: Multi-Tenant (Organization Scoped)

Use when: Building SaaS apps where data is scoped to organizations/workspaces

Schema:

CREATE TABLE organizations ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), name TEXT NOT NULL, created_at TIMESTAMPTZ DEFAULT NOW() );

CREATE TABLE organization_members ( organization_id UUID REFERENCES organizations(id) ON DELETE CASCADE, user_id UUID REFERENCES users(id) ON DELETE CASCADE, role TEXT NOT NULL CHECK (role IN ('owner', 'admin', 'member')), joined_at TIMESTAMPTZ DEFAULT NOW(), PRIMARY KEY (organization_id, user_id) );

CREATE TABLE projects ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), organization_id UUID REFERENCES organizations(id) ON DELETE CASCADE, name TEXT NOT NULL, created_at TIMESTAMPTZ DEFAULT NOW() );

-- RLS: Users can only see projects in their organizations ALTER TABLE projects ENABLE ROW LEVEL SECURITY;

CREATE POLICY "Users see org projects" ON projects FOR SELECT TO authenticated USING ( organization_id IN ( SELECT organization_id FROM organization_members WHERE user_id = uid() ) );

CREATE POLICY "Admins can create projects" ON projects FOR INSERT TO authenticated WITH CHECK ( organization_id IN ( SELECT organization_id FROM organization_members WHERE user_id = uid() AND role IN ('owner', 'admin') ) );

Best Practices

• Always add indexes on foreign key columns for performance

• Use UNIQUE constraints on junction tables to prevent duplicates

• Enable RLS on all user-facing tables

• Use ON DELETE CASCADE for automatic cleanup

• Foreign key expansion in SDK uses the syntax: table:column(fields)

• Count aggregations use: table(count)

• Filter nested tables with: nested_table!inner() for inner join behavior

Common Mistakes to Avoid

• ❌ Forgetting indexes on foreign keys → Slow queries

• ❌ Not using UNIQUE on junction tables → Duplicate likes/follows

• ❌ Missing RLS policies → Data leaks

• ❌ Using .single() on queries that might return multiple rows → Errors

• ❌ Not wrapping INSERT data in arrays → PostgREST error