Triggers & Functions
Database triggers and functions that automate slug generation, notifications, follows, and mentions.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
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:
