Rork Max ソーシャルアプリ機能 構築ガイド — タイムライン・フォロー・リアルタイムフィード

// services/followService.ts
import { createClient } from '@supabase/supabase-js';
 
class FollowService {
  private supabase = createClient(
    process.env.SUPABASE_URL!,
    process.env.SUPABASE_ANON_KEY!
  );
 
  // フォローしたユーザーのリストを取得
  async getFollowing(userId: string, limit = 20, offset = 0) {
    const { data, error } = await this.supabase
      .from('follows')
      .select(`
        following_id,
        profiles:following_id (
          id, username, display_name, avatar_url, followers_count
        )
      `)
      .eq('follower_id', userId)
      .order('created_at', { ascending: false })
      .range(offset, offset + limit - 1);
 
    if (error) throw error;
    return data;
  }
 
  // フォロワーのリストを取得
  async getFollowers(userId: string, limit = 20, offset = 0) {
    const { data, error } = await this.supabase
      .from('follows')
      .select(`
        follower_id,
        profiles:follower_id (
          id, username, display_name, avatar_url, followers_count
        )
      `)
      .eq('following_id', userId)
      .order('created_at', { ascending: false })
      .range(offset, offset + limit - 1);
 
    if (error) throw error;
    return data;
  }
 
  // ユーザーをフォロー
  async followUser(followerId: string, followingId: string) {
    // 既にフォローしているかチェック
    const { data: existing } = await this.supabase
      .from('follows')
      .select('id')
      .eq('follower_id', followerId)
      .eq('following_id', followingId)
      .single();
 
    if (existing) throw new Error('Already following');
 
    // フォロー関係を作成
    const { error } = await this.supabase
      .from('follows')
      .insert([
        { follower_id: followerId, following_id: followingId }
      ]);
 
    if (error) throw error;
 
    // フォロワーカウントを増やす
    await this.supabase
      .from('profiles')
      .update({ followers_count: this.incrementCounter() })
      .eq('id', followingId);
 
    // フォロー中カウントを増やす
    await this.supabase
      .from('profiles')
      .update({ following_count: this.incrementCounter() })
      .eq('id', followerId);
 
    // 通知を作成
    await this.createNotification(
      followingId,
      'follow',
      followerId,
      null,
      null
    );
  }
 
  // フォローを解除
  async unfollowUser(followerId: string, followingId: string) {
    const { error } = await this.supabase
      .from('follows')
      .delete()
      .eq('follower_id', followerId)
      .eq('following_id', followingId);
 
    if (error) throw error;
 
    // カウンターを更新
    await Promise.all([
      this.supabase
        .from('profiles')
        .update({ followers_count: this.decrementCounter() })
        .eq('id', followingId),
      this.supabase
        .from('profiles')
        .update({ following_count: this.decrementCounter() })
        .eq('id', followerId)
    ]);
  }
 
  // フォロー提案を取得（相互フォローの可能性が高いユーザー）
  async getSuggestedFollows(userId: string, limit = 10) {
    const { data: suggested, error } = await this.supabase
      .rpc('get_suggested_follows', {
        current_user_id: userId,
        limit_count: limit
      });
 
    if (error) throw error;
    return suggested;
  }
 
  private incrementCounter() {
    return this.supabase.raw`followers_count + 1`;
  }
 
  private decrementCounter() {
    return this.supabase.raw`followers_count - 1`;
  }
 
  private async createNotification(
    userId: string,
    type: 'like' | 'comment' | 'follow',
    triggeredById: string,
    postId: string | null,
    commentId: string | null
  ) {
    await this.supabase.from('notifications').insert([
      {
        user_id: userId,
        type,
        triggered_by_id: triggeredById,
        post_id: postId,
        comment_id: commentId
      }
    ]);
  }
}
 
export const followService = new FollowService();

// components/FollowButton.tsx
import React, { useState } from 'react';
import { Text, TouchableOpacity, ActivityIndicator } from 'react-native';
import { useAuth } from '@/context/AuthContext';
import { followService } from '@/services/followService';
 
interface FollowButtonProps {
  targetUserId: string;
  isFollowing: boolean;
  onFollowChange: (newState: boolean) => void;
}
 
export function FollowButton({
  targetUserId,
  isFollowing,
  onFollowChange
}: FollowButtonProps) {
  const { user } = useAuth();
  const [loading, setLoading] = useState(false);
 
  if (!user) return null;
 
  const handlePress = async () => {
    setLoading(true);
    try {
      if (isFollowing) {
        await followService.unfollowUser(user.id, targetUserId);
        onFollowChange(false);
      } else {
        await followService.followUser(user.id, targetUserId);
        onFollowChange(true);
      }
    } catch (error) {
      console.error('Follow error:', error);
    } finally {
      setLoading(false);
    }
  };
 
  return (
    <TouchableOpacity
      onPress={handlePress}
      disabled={loading}
      style={{
        paddingHorizontal: 16,
        paddingVertical: 8,
        backgroundColor: isFollowing ? '#f0f0f0' : '#0066cc',
        borderRadius: 20,
        borderWidth: isFollowing ? 1 : 0,
        borderColor: '#ccc'
      }}
    >
      {loading ? (
        <ActivityIndicator color={isFollowing ? '#000' : '#fff'} />
      ) : (
        <Text
          style={{
            color: isFollowing ? '#000' : '#fff',
            fontWeight: '600',
            fontSize: 14
          }}
        >
          {isFollowing ? 'フォロー中' : 'フォロー'}
        </Text>
      )}
    </TouchableOpacity>
  );
}

// services/feedService.ts
class FeedService {
  private supabase = createClient(
    process.env.SUPABASE_URL!,
    process.env.SUPABASE_ANON_KEY!
  );
 
  /**
   * ユーザーのホームフィードを取得
   * フォローしているユーザーの投稿 + 新規・人気順でソート
   */
  async getHomeFeed(userId: string, limit = 20, offset = 0) {
    const { data: following, error: followError } = await this.supabase
      .from('follows')
      .select('following_id')
      .eq('follower_id', userId);
 
    if (followError) throw followError;
 
    const followingIds = following?.map(f => f.following_id) || [];
 
    // 自分の投稿 + フォローしているユーザーの投稿を結合
    const { data: posts, error: postError } = await this.supabase
      .from('posts')
      .select(`
        id,
        content,
        image_urls,
        author_id,
        profiles:author_id (
          id, username, display_name, avatar_url
        ),
        likes_count,
        comments_count,
        created_at,
        likes!inner (user_id)
      `)
      .in('author_id', [userId, ...followingIds])
      .is('deleted_at', null)
      .order('created_at', { ascending: false })
      .range(offset, offset + limit - 1);
 
    if (postError) throw postError;
 
    // 現在のユーザーが各投稿にいいねしているかを確認
    return posts?.map(post => ({
      ...post,
      isLikedByCurrentUser: post.likes?.some(
        like => like.user_id === userId
      ) || false
    })) || [];
  }
 
  /**
   * 探索フィード（発見用フィード）
   * トレンド投稿・人気投稿・新規投稿をブレンド
   */
  async getDiscoverFeed(userId: string, limit = 20, offset = 0) {
    // 過去24時間でいいね数が多い投稿を優先
    const { data: posts, error } = await this.supabase
      .from('posts')
      .select(`
        id,
        content,
        image_urls,
        author_id,
        profiles:author_id (
          id, username, display_name, avatar_url
        ),
        likes_count,
        comments_count,
        created_at,
        likes!inner (user_id)
      `)
      .is('deleted_at', null)
      .gt('created_at', new Date(Date.now() - 24 * 60 * 60 * 1000).toISOString())
      .order('likes_count', { ascending: false })
      .range(offset, offset + limit - 1);
 
    if (error) throw error;
 
    return posts?.map(post => ({
      ...post,
      isLikedByCurrentUser: post.likes?.some(
        like => like.user_id === userId
      ) || false
    })) || [];
  }
 
  /**
   * ユーザープロフィールの投稿一覧を取得
   */
  async getUserPosts(userId: string, limit = 20, offset = 0) {
    const { data: posts, error } = await this.supabase
      .from('posts')
      .select('*')
      .eq('author_id', userId)
      .is('deleted_at', null)
      .order('created_at', { ascending: false })
      .range(offset, offset + limit - 1);
 
    if (error) throw error;
    return posts || [];
  }
 
  /**
   * 無限スクロール対応のページネーション
   * カーソルベースのページネーション（offset より効率的）
   */
  async getFeedWithCursor(
    userId: string,
    cursor: string | null = null,
    limit = 20
  ) {
    let query = this.supabase
      .from('posts')
      .select('*')
      .eq('author_id', userId)
      .is('deleted_at', null)
      .order('created_at', { ascending: false });
 
    if (cursor) {
      query = query.lt('created_at', cursor);
    }
 
    const { data: posts, error } = await query.limit(limit + 1);
 
    if (error) throw error;
 
    const hasMore = (posts || []).length > limit;
    const feedPosts = (posts || []).slice(0, limit);
    const nextCursor = feedPosts[feedPosts.length - 1]?.created_at || null;
 
    return { posts: feedPosts, nextCursor, hasMore };
  }
}
 
export const feedService = new FeedService();

// services/likeService.ts
class LikeService {
  private supabase = createClient(
    process.env.SUPABASE_URL!,
    process.env.SUPABASE_ANON_KEY!
  );
 
  async likePost(postId: string, userId: string) {
    try {
      // 既にいいねしているかチェック
      const { data: existing } = await this.supabase
        .from('likes')
        .select('id')
        .eq('post_id', postId)
        .eq('user_id', userId)
        .single();
 
      if (existing) return; // 既にいいね済み
 
      // いいねを追加
      await this.supabase
        .from('likes')
        .insert([{ post_id: postId, user_id: userId }]);
 
      // post の likes_count を増やす
      await this.supabase
        .from('posts')
        .update({ likes_count: this.incrementCounter() })
        .eq('id', postId);
 
      // 通知を作成
      const { data: post } = await this.supabase
        .from('posts')
        .select('author_id')
        .eq('id', postId)
        .single();
 
      if (post?.author_id !== userId) {
        await this.supabase
          .from('notifications')
          .insert([{
            user_id: post.author_id,
            type: 'like',
            triggered_by_id: userId,
            post_id: postId
          }]);
      }
    } catch (error) {
      console.error('Like error:', error);
      throw error;
    }
  }
 
  async unlikePost(postId: string, userId: string) {
    await this.supabase
      .from('likes')
      .delete()
      .eq('post_id', postId)
      .eq('user_id', userId);
 
    // likes_count を減らす
    await this.supabase
      .from('posts')
      .update({ likes_count: this.decrementCounter() })
      .eq('id', postId);
  }
 
  private incrementCounter() {
    return this.supabase.raw`likes_count + 1`;
  }
 
  private decrementCounter() {
    return this.supabase.raw`likes_count - 1`;
  }
}
 
export const likeService = new LikeService();

// components/LikeButton.tsx
import React, { useState } from 'react';
import { TouchableOpacity, Text } from 'react-native';
import { likeService } from '@/services/likeService';
 
interface LikeButtonProps {
  postId: string;
  userId: string;
  initialIsLiked: boolean;
  initialCount: number;
  onCountChange?: (newCount: number) => void;
}
 
export function LikeButton({
  postId,
  userId,
  initialIsLiked,
  initialCount,
  onCountChange
}: LikeButtonProps) {
  const [isLiked, setIsLiked] = useState(initialIsLiked);
  const [count, setCount] = useState(initialCount);
  const [loading, setLoading] = useState(false);
 
  const handleLike = async () => {
    // 楽観的更新：サーバーレスポンス前にUIを更新
    const previousLiked = isLiked;
    const previousCount = count;
 
    setIsLiked(!isLiked);
    setCount(isLiked ? count - 1 : count + 1);
    setLoading(true);
 
    try {
      if (isLiked) {
        await likeService.unlikePost(postId, userId);
      } else {
        await likeService.likePost(postId, userId);
      }
      onCountChange?.(isLiked ? count - 1 : count + 1);
    } catch (error) {
      // エラー時はロールバック
      console.error('Like failed, rolling back:', error);
      setIsLiked(previousLiked);
      setCount(previousCount);
    } finally {
      setLoading(false);
    }
  };
 
  return (
    <TouchableOpacity
      onPress={handleLike}
      disabled={loading}
      style={{ flexDirection: 'row', alignItems: 'center' }}
    >
      <Text style={{ fontSize: 18, marginRight: 8 }}>
        {isLiked ? '❤️' : '🤍'}
      </Text>
      <Text style={{ color: isLiked ? '#e74c3c' : '#999' }}>
        {count}
      </Text>
    </TouchableOpacity>
  );
}

// services/commentService.ts
class CommentService {
  private supabase = createClient(
    process.env.SUPABASE_URL!,
    process.env.SUPABASE_ANON_KEY!
  );
 
  /**
   * 投稿に対するコメントを取得
   * ネスト返信（親コメント以下のスレッド）にも対応
   */
  async getComments(postId: string, limit = 20, offset = 0) {
    const { data: comments, error } = await this.supabase
      .from('comments')
      .select(`
        id,
        content,
        author_id,
        profiles:author_id (id, username, avatar_url),
        likes_count,
        parent_comment_id,
        created_at,
        updated_at
      `)
      .eq('post_id', postId)
      .is('parent_comment_id', null) // トップレベルのコメントのみ
      .is('deleted_at', null)
      .order('created_at', { ascending: false })
      .range(offset, offset + limit - 1);
 
    if (error) throw error;
 
    // 各トップレベルコメントに対する返信を取得
    const commentsWithReplies = await Promise.all(
      (comments || []).map(async (comment) => {
        const { data: replies } = await this.supabase
          .from('comments')
          .select(`
            id, content, author_id,
            profiles:author_id (id, username, avatar_url),
            likes_count, created_at
          `)
          .eq('parent_comment_id', comment.id)
          .is('deleted_at', null)
          .order('created_at', { ascending: true })
          .limit(3); // 返信は最新3件のみ最初に表示
 
        return { ...comment, replies: replies || [] };
      })
    );
 
    return commentsWithReplies;
  }
 
  async addComment(postId: string, userId: string, content: string, parentCommentId?: string) {
    const { data: newComment, error } = await this.supabase
      .from('comments')
      .insert([{
        post_id: postId,
        author_id: userId,
        content,
        parent_comment_id: parentCommentId || null
      }])
      .select()
      .single();
 
    if (error) throw error;
 
    // post の comments_count を増やす（トップレベルコメントのみ）
    if (!parentCommentId) {
      await this.supabase
        .from('posts')
        .update({ comments_count: this.incrementCounter() })
        .eq('id', postId);
    }
 
    // 通知を作成
    const { data: post } = await this.supabase
      .from('posts')
      .select('author_id')
      .eq('id', postId)
      .single();
 
    if (post?.author_id !== userId) {
      await this.supabase
        .from('notifications')
        .insert([{
          user_id: post.author_id,
          type: 'comment',
          triggered_by_id: userId,
          post_id: postId,
          comment_id: newComment.id
        }]);
    }
 
    return newComment;
  }
 
  private incrementCounter() {
    return this.supabase.raw`comments_count + 1`;
  }
 
  private decrementCounter() {
    return this.supabase.raw`comments_count - 1`;
  }
}
 
export const commentService = new CommentService();

// hooks/useRealtimePost.ts
import { useEffect, useState } from 'react';
import { createClient } from '@supabase/supabase-js';
 
interface Post {
  id: string;
  content: string;
  likes_count: number;
  comments_count: number;
  // ... その他のフィールド
}
 
export function useRealtimePost(postId: string) {
  const [post, setPost] = useState<Post | null>(null);
  const supabase = createClient(
    process.env.SUPABASE_URL!,
    process.env.SUPABASE_ANON_KEY!
  );
 
  useEffect(() => {
    // 初期データを取得
    const fetchPost = async () => {
      const { data } = await supabase
        .from('posts')
        .select('*')
        .eq('id', postId)
        .single();
      setPost(data);
    };
 
    fetchPost();
 
    // リアルタイムリスナーを設定
    const subscription = supabase
      .channel(`post:${postId}`)
      .on(
        'postgres_changes',
        {
          event: '*',
          schema: 'public',
          table: 'posts',
          filter: `id=eq.${postId}`
        },
        (payload) => {
          console.log('Post updated:', payload);
          setPost(payload.new as Post);
        }
      )
      .subscribe();
 
    return () => {
      subscription.unsubscribe();
    };
  }, [postId]);
 
  return post;
}

// hooks/useRealtimeFeed.ts
export function useRealtimeFeed(userId: string) {
  const [newPosts, setNewPosts] = useState<Post[]>([]);
  const supabase = createClient(
    process.env.SUPABASE_URL!,
    process.env.SUPABASE_ANON_KEY!
  );
 
  useEffect(() => {
    const subscription = supabase
      .channel('feed:updates')
      .on(
        'postgres_changes',
        {
          event: 'INSERT',
          schema: 'public',
          table: 'posts'
        },
        (payload) => {
          const newPost = payload.new as Post;
          // フォローしているユーザーの投稿のみを追加
          setNewPosts(prev => [newPost, ...prev]);
        }
      )
      .subscribe();
 
    return () => {
      subscription.unsubscribe();
    };
  }, [userId]);
 
  return newPosts;
}

// screens/FeedScreen.tsx
import React, { useState, useCallback, useRef } from 'react';
import {
  FlatList,
  ActivityIndicator,
  RefreshControl,
  View,
  Text
} from 'react-native';
import { feedService } from '@/services/feedService';
import { PostCard } from '@/components/PostCard';
 
export function FeedScreen() {
  const [posts, setPosts] = useState<Post[]>([]);
  const [loading, setLoading] = useState(true);
  const [refreshing, setRefreshing] = useState(false);
  const [hasMore, setHasMore] = useState(true);
  const [cursor, setCursor] = useState<string | null>(null);
  const { user } = useAuth();
 
  // 初期ロード
  const loadInitialFeed = useCallback(async () => {
    if (!user) return;
    setLoading(true);
    try {
      const { posts: initialPosts, nextCursor, hasMore } =
        await feedService.getFeedWithCursor(user.id, null, 20);
      setPosts(initialPosts);
      setCursor(nextCursor);
      setHasMore(hasMore);
    } catch (error) {
      console.error('Load feed error:', error);
    } finally {
      setLoading(false);
    }
  }, [user]);
 
  // プルリフレッシュ
  const onRefresh = useCallback(async () => {
    if (!user) return;
    setRefreshing(true);
    try {
      const { posts: freshPosts, nextCursor } =
        await feedService.getFeedWithCursor(user.id, null, 20);
      setPosts(freshPosts);
      setCursor(nextCursor);
      setHasMore(true);
    } catch (error) {
      console.error('Refresh error:', error);
    } finally {
      setRefreshing(false);
    }
  }, [user]);
 
  // 末尾に達した時に追加データをロード
  const onEndReached = useCallback(async () => {
    if (!user || !hasMore || !cursor) return;
 
    try {
      const { posts: morePosts, nextCursor, hasMore: moreExists } =
        await feedService.getFeedWithCursor(user.id, cursor, 20);
 
      setPosts(prev => [...prev, ...morePosts]);
      setCursor(nextCursor);
      setHasMore(moreExists);
    } catch (error) {
      console.error('Load more error:', error);
    }
  }, [user, cursor, hasMore]);
 
  if (loading) {
    return (
      <View style={{ flex: 1, justifyContent: 'center', alignItems: 'center' }}>
        <ActivityIndicator size="large" />
      </View>
    );
  }
 
  return (
    <FlatList
      data={posts}
      renderItem={({ item }) => <PostCard post={item} />}
      keyExtractor={item => item.id}
      onEndReached={onEndReached}
      onEndReachedThreshold={0.5} // スクロール位置50%で追加ロード
      refreshControl={
        <RefreshControl
          refreshing={refreshing}
          onRefresh={onRefresh}
          tintColor="#0066cc"
        />
      }
      ListFooterComponent={
        hasMore && posts.length > 0 ? (
          <View style={{ paddingVertical: 16 }}>
            <ActivityIndicator />
          </View>
        ) : posts.length === 0 ? (
          <Text style={{ textAlign: 'center', marginTop: 32, color: '#999' }}>
            投稿がありません
          </Text>
        ) : null
      }
    />
  );
}

// components/PostImage.tsx
import { Image } from 'react-native';
import FastImage from 'react-native-fast-image';
 
interface PostImageProps {
  url: string;
  width: number;
  height: number;
}
 
export function PostImage({ url, width, height }: PostImageProps) {
  return (
    <FastImage
      source={{
        uri: url,
        priority: FastImage.priority.normal,
        cache: FastImage.cacheControl.immutable
      }}
      style={{ width, height }}
      resizeMode={FastImage.resizeMode.cover}
    />
  );
}

// services/notificationService.ts
import messaging from '@react-native-firebase/messaging';
 
class NotificationService {
  private supabase = createClient(
    process.env.SUPABASE_URL!,
    process.env.SUPABASE_ANON_KEY!
  );
 
  async initializeNotifications(userId: string) {
    // FCM トークンを取得
    const fcmToken = await messaging().getToken();
 
    // Supabase に FCM トークンを保存
    await this.supabase
      .from('user_devices')
      .upsert({
        user_id: userId,
        fcm_token: fcmToken,
        platform: 'ios', // または 'android'
        updated_at: new Date()
      });
 
    // フォアグラウンド通知リスナー
    messaging().onMessage(async (remoteMessage) => {
      console.log('Foreground notification:', remoteMessage);
      this.handleNotification(remoteMessage);
    });
 
    // バックグラウンド通知リスナー
    messaging().onNotificationOpenedApp((remoteMessage) => {
      console.log('App opened from notification:', remoteMessage);
      this.navigateToNotificationContent(remoteMessage);
    });
  }
 
  private handleNotification(remoteMessage: any) {
    // 通知をローカル表示
    // または通知センターを更新
  }
 
  private navigateToNotificationContent(remoteMessage: any) {
    const { notificationType, targetId } = remoteMessage.data;
 
    switch (notificationType) {
      case 'like':
      case 'comment':
        // 投稿詳細画面へ移動
        break;
      case 'follow':
        // ユーザープロフィール画面へ移動
        break;
    }
  }
}
 
export const notificationService = new NotificationService();

// Firebase Cloud Function
import * as functions from 'firebase-functions';
import * as admin from 'firebase-admin';
 
admin.initializeApp();
 
export const sendLikeNotification = functions.firestore
  .document('likes/{likeId}')
  .onCreate(async (snap, context) => {
    const like = snap.data();
    const postDoc = await admin
      .firestore()
      .collection('posts')
      .doc(like.postId)
      .get();
 
    const postData = postDoc.data();
    const authorId = postData?.authorId;
 
    if (authorId === like.userId) return; // 自分の投稿へのいいねは通知しない
 
    const userDevices = await admin
      .firestore()
      .collection('user_devices')
      .where('userId', '==', authorId)
      .get();
 
    const tokens = userDevices.docs
      .map(doc => doc.data().fcmToken)
      .filter(Boolean);
 
    if (tokens.length === 0) return;
 
    await admin.messaging().sendMulticast({
      tokens,
      notification: {
        title: 'いいね',
        body: `誰かがあなたの投稿にいいねしました`
      },
      data: {
        notificationType: 'like',
        targetId: like.postId
      }
    });
  });

// services/timelineService.ts
// ❌ やってはいけない: ホームフィードに発見要素を混ぜる
async function getMixedHomeFeed(userId: string, cursor?: string) {
  const { data: followFeed } = await supabase
    .from('posts')
    .select('*')
    .in('user_id', await getFollowees(userId))
    .order('created_at', { ascending: false })
    .limit(15);
 
  // この5件が Day7 を落とす
  const { data: discovery } = await supabase
    .from('posts')
    .select('*')
    .gt('popularity_score', 50)
    .order('popularity_score', { ascending: false })
    .limit(5);
 
  return [...(followFeed ?? []), ...(discovery ?? [])];
}
 
// ✅ 推奨: ホームはフォロー純粋・発見は別画面
async function getHomeFeed(userId: string, cursor?: string) {
  const followees = await getFollowees(userId);
  if (followees.length === 0) {
    // 新規ユーザー向けのオンボーディング用 follow 提案を返す（フィードを混ぜない）
    return { posts: [], suggestions: await getFollowSuggestions(userId) };
  }
 
  const query = supabase
    .from('posts')
    .select('*, profiles!inner(*)')
    .in('user_id', followees)
    .order('created_at', { ascending: false })
    .limit(20);
 
  if (cursor) query.lt('created_at', cursor);
 
  const { data } = await query;
  return { posts: data ?? [], suggestions: null };
}

-- migrations/202605_followers_view.sql
CREATE MATERIALIZED VIEW user_follower_stats AS
SELECT
  followee_id AS user_id,
  COUNT(*) AS follower_count,
  COUNT(*) FILTER (WHERE created_at > NOW() - INTERVAL '7 days') AS new_followers_7d
FROM follows
GROUP BY followee_id;
 
CREATE UNIQUE INDEX ON user_follower_stats (user_id);
 
-- 5分おきに refresh
SELECT cron.schedule(
  'refresh_user_follower_stats',
  '*/5 * * * *',
  $$REFRESH MATERIALIZED VIEW CONCURRENTLY user_follower_stats$$
);

-- migrations/202605_likes_table.sql
CREATE TABLE likes (
  id BIGSERIAL PRIMARY KEY,
  user_id UUID NOT NULL REFERENCES profiles(id) ON DELETE CASCADE,
  post_id UUID NOT NULL REFERENCES posts(id) ON DELETE CASCADE,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  UNIQUE (user_id, post_id)
);
 
CREATE INDEX likes_post_id_created_idx ON likes (post_id, created_at DESC);

// services/likeService.ts
export async function toggleLike(userId: string, postId: string) {
  // upsert で「いいね」を冪等に作成、conflict 時は何もしない
  const { error: insertError } = await supabase
    .from('likes')
    .insert({ user_id: userId, post_id: postId })
    .select()
    .single();
 
  if (insertError && insertError.code === '23505') {
    // unique violation → 既に「いいね」済み → 解除する
    await supabase
      .from('likes')
      .delete()
      .eq('user_id', userId)
      .eq('post_id', postId);
    return { liked: false };
  }
 
  return { liked: true };
}

// services/realtimeFallback.ts
import { createClient, RealtimeChannel } from '@supabase/supabase-js';
 
type FeedMode = 'realtime' | 'polling-30s' | 'polling-2m' | 'manual';
 
export class FeedSubscriber {
  private channel: RealtimeChannel | null = null;
  private pollTimer: NodeJS.Timeout | null = null;
  private mode: FeedMode = 'realtime';
 
  constructor(
    private supabase: ReturnType<typeof createClient>,
    private onUpdate: () => void
  ) {}
 
  async start(userId: string) {
    // Cloudflare KV や Edge Config から現在の運用モードを取得
    this.mode = await this.fetchCurrentMode();
 
    switch (this.mode) {
      case 'realtime':
        return this.startRealtime(userId);
      case 'polling-30s':
        return this.startPolling(30_000);
      case 'polling-2m':
        return this.startPolling(120_000);
      case 'manual':
        // 何もしない（プルトゥリフレッシュのみ）
        return;
    }
  }
 
  private startRealtime(userId: string) {
    this.channel = this.supabase
      .channel(`feed:${userId}`)
      .on('postgres_changes',
        { event: 'INSERT', schema: 'public', table: 'posts' },
        () => this.onUpdate()
      )
      .subscribe((status, err) => {
        if (status === 'CHANNEL_ERROR' || status === 'TIMED_OUT') {
          // Realtime 側で詰まったら 30 秒ポーリングへ自動降格
          this.mode = 'polling-30s';
          this.startPolling(30_000);
        }
      });
  }
 
  private startPolling(intervalMs: number) {
    this.pollTimer = setInterval(() => this.onUpdate(), intervalMs);
  }
 
  async stop() {
    if (this.channel) await this.supabase.removeChannel(this.channel);
    if (this.pollTimer) clearInterval(this.pollTimer);
  }
 
  private async fetchCurrentMode(): Promise<FeedMode> {
    // 運用側で `realtime → polling-30s → polling-2m → manual` を切り替える
    const res = await fetch('https://your-edge-config.example/feed-mode');
    return (await res.json()).mode ?? 'realtime';
  }
}

// supabase/functions/send-social-notifications/index.ts
const OPEN_RATE_THRESHOLD = 0.08; // 8%
const NOTIFICATION_TYPES = ['follow', 'like', 'comment', 'reply', 'mention', 'tag', 'group_invite'] as const;
 
async function shouldSendNotification(
  type: typeof NOTIFICATION_TYPES[number],
  recipientUserId: string
): Promise<boolean> {
  // 直近30日のグローバル OPEN 率
  const { data } = await supabase.rpc('get_notification_open_rate', {
    notification_type: type,
    since_days: 30,
  });
  if ((data?.open_rate ?? 1) < OPEN_RATE_THRESHOLD) {
    return false;
  }
 
  // ユーザー側のオプトアウトもチェック
  const { data: prefs } = await supabase
    .from('notification_preferences')
    .select('disabled_types')
    .eq('user_id', recipientUserId)
    .single();
  if (prefs?.disabled_types?.includes(type)) return false;
 
  // 直近 30 分で同種別の通知を 3 件以上送っていたら一旦止める（バースト抑制）
  const { count } = await supabase
    .from('notification_log')
    .select('*', { count: 'exact', head: true })
    .eq('user_id', recipientUserId)
    .eq('type', type)
    .gt('sent_at', new Date(Date.now() - 30 * 60_000).toISOString());
 
  return (count ?? 0) < 3;
}