Triggers & Functions

The database uses several PostgreSQL triggers and functions to automate common operations.

Signup Trigger

handle_new_user() — Fires AFTER INSERT ON auth.users. When a new user signs up via Supabase Auth:

• Creates a row in profiles with full_name and avatar_url from OAuth metadata
• Assigns the member role in user_global_roles

Both operations use ON CONFLICT DO NOTHING so they're safe if the profile already exists.

Post Slug Generation

hub_posts_set_slug() — Fires BEFORE INSERT ON hub_posts. Automatically generates a URL-friendly slug from the post title:

• If a slug is already provided, keeps it
• Runs the title through slugify() (lowercases, removes accents, replaces non-alphanumeric with dashes)
• Appends a 7-character hash suffix derived from the post's UUID
• Checks for collisions and extends the suffix if needed (up to 5 retries, then falls back to a random suffix)

slugify(txt) — Helper function that converts any text to a URL-safe slug using the unaccent extension.

Notification Triggers

Comment Notifications

hub_comment_notify_trigger() — Fires AFTER INSERT ON hub_comments. When a comment is created:

• Updates the participation cache for the commenter
• Determines the notification type: comment_on_post (top-level) or reply (to another comment)
• Creates a notification with context including post slug, space slug, and a 140-char excerpt
• Notifies the post author (for top-level comments) or the parent comment author (for replies)
• Also notifies all other participants in the post's comment thread (excluding the commenter)

Mention Notifications

hub_process_comment_mentions() — Fires AFTER INSERT ON hub_comments. Parses comment content for @username mentions:

• Extracts all @word patterns from the comment content
• Looks up matching usernames in the profiles table
• Creates entries in hub_comment_mentions for each valid mention
• Creates mention type notifications for each mentioned user (excluding self-mentions)

Milestone Notifications

hub_check_view_milestones() — Fires AFTER UPDATE OF views ON hub_posts. When a post's view count changes:

• Checks against predefined milestones: 50, 100, 250, 500
• For 1000+ views, checks every 1000 increment
• Creates a milestone notification for the post author if a new milestone is reached
• Uses hub_post_milestones table to prevent duplicate notifications

hub_check_reaction_milestones() — Fires AFTER INSERT ON hub_post_reactions. When a new reaction is added to a post:

• Checks against predefined milestones: 5, 25, 50
• For 100+ reactions, checks every 100 increment
• Same deduplication logic via hub_post_milestones

Space Role Notifications

hub_space_role_notify_trigger() — Fires AFTER INSERT OR UPDATE OF role_id ON hub_space_members. When a user is assigned as moderator or admin of a space:

• Only triggers for moderator and admin roles
• Skips if the user was already a moderator/admin (on UPDATE)
• Skips if the user is the space creator (auto-assigned on creation)
• Creates a space_role notification with space name and assigned role

Follow Notifications

hub_new_follower_notify() — Fires AFTER INSERT ON hub_user_follows. When user A follows user B:

• Looks up user A's username and slug
• Creates a new_follower notification with the follower's profile info
• Delivers the notification to user B (respecting notification settings)

hub_follower_post_notify() — Fires AFTER INSERT ON hub_posts (only when status = 'visible'). When a user publishes a new visible post:

• Looks up the author's username, slug, and the space slug
• Creates a follower_post notification with post title and link context
• Delivers to all followers (excluding those who muted the space or disabled in-app notifications)

Constraints

Indexes

Key indexes for query performance: