[PSQL] Schema Optimizations - Memes, Realtime, Discordsh

[h0lybyte]

Summary

Wrap auth.uid() calls in RLS policies with subqueries (select auth.uid()) so the function executes once per query instead of once per row. This is a well-known Supabase optimization that can reduce overhead from seconds to microseconds on large tables.

Before:

USING (user_id = auth.uid());

After:

USING (user_id = (select auth.uid()));

Affected Schemas

meme — meme_core.sql (4 policies)

• status = 3 OR author_id = auth.uid() (SELECT)
• author_id = auth.uid() AND status = 1 (INSERT)
• author_id = auth.uid() (UPDATE USING)
• author_id = auth.uid() (UPDATE WITH CHECK)

meme — meme_engagement.sql (8 policies)

• user_id = auth.uid() — reactions INSERT WITH CHECK
• user_id = auth.uid() — reactions UPDATE USING + WITH CHECK
• user_id = auth.uid() — reactions DELETE USING
• author_id = auth.uid() — comments INSERT WITH CHECK
• author_id = auth.uid() — comments UPDATE USING + WITH CHECK
• author_id = auth.uid() — comments DELETE USING

meme — meme_cards.sql (8 policies)

• owner_id = auth.uid() — meme_decks SELECT, INSERT, UPDATE, DELETE (5 policies)
• d.owner_id = auth.uid() — meme_deck_cards subquery policies (3 policies)

meme — meme_social.sql (12 policies)

• user_id = auth.uid() — meme_user_profiles UPDATE USING + WITH CHECK
• follower_id = auth.uid() — meme_follows INSERT + DELETE
• owner_id = auth.uid() or is_public = true OR owner_id = auth.uid() — meme_collections SELECT, INSERT, UPDATE, DELETE (4 policies)
• user_id = auth.uid() — meme_saves SELECT, INSERT, DELETE (3 policies)

meme — meme_moderation.sql (2 policies)

• reporter_id = auth.uid() — meme_reports INSERT + SELECT

realtime — realtime_schema.sql (4 policies)

• auth.uid() = user_id — SELECT, INSERT, UPDATE, DELETE

discordsh — discordsh_servers.sql (1 policy)

• owner_id = auth.uid() — UPDATE USING + WITH CHECK

Implementation

1. Update the source-of-truth schema SQL files in packages/data/sql/schema/
2. Create a dbmate migration (ALTER POLICY) to apply changes in production
3. Verify no functional change — subquery wrapping is purely a performance optimization

Total: ~39 RLS policies across 7 files