watproto / packages / server / src / lib / feed.ts

import { db } from '@api/db/db'
import type { Did, ResourceUri } from '@atcute/lexicons'
import type { AppWafrnContentDefs } from '@watproto/lexicon'

type FeedParams = {
  did: Did
  limit: number
}

type FollowingFeedParams = {
  viewer: Did
  limit: number
}

export async function getActorFeed({ did, limit }: FeedParams) {
  const [publicPostRows, privatePostRows] = await Promise.all([
    db
      .selectFrom('public_posts')
      .selectAll()
      .where('author_did', '=', did)
      .orderBy('created_at', 'desc')
      .limit(limit * 2)
      .execute(),
    db
      .selectFrom('private_posts')
      .selectAll()
      .where('author_did', '=', did)
      .orderBy('created_at', 'desc')
      .limit(limit * 2)
      .execute()
  ])

  // Fetch tags for all public posts in one query
  const publicPostUris = publicPostRows.map((p) => p.uri)
  const tagRows =
    publicPostUris.length > 0
      ? await db
          .selectFrom('public_post_tags')
          .selectAll()
          .where('post_uri', 'in', publicPostUris)
          .execute()
      : []

  // Group tags by post URI
  const tagsByPostUri = new Map<string, string[]>()
  for (const tagRow of tagRows) {
    const existing = tagsByPostUri.get(tagRow.post_uri) ?? []
    existing.push(tagRow.tag_name)
    tagsByPostUri.set(tagRow.post_uri, existing)
  }

  const publicPosts = publicPostRows.map(
    (p) =>
      ({
        $type: 'app.wafrn.content.defs#publicPostView',
        uri: p.uri as ResourceUri,
        content: {
          contentHTML: p.content_html,
          contentMarkdown: p.content_markdown,
          contentWarning: p.content_warning ?? '',
          tags: tagsByPostUri.get(p.uri) ?? []
        },
        createdAt: new Date(p.created_at).toISOString(),
        updatedAt: new Date(p.updated_at).toISOString()
      }) satisfies AppWafrnContentDefs.PublicPostView
  )
  const privatePosts = privatePostRows.map(
    (p) =>
      ({
        $type: 'app.wafrn.content.defs#privatePostView',
        uri: p.uri as ResourceUri,
        visibility: p.visibility,
        keyVersion: p.key_version,
        encryptedContent: p.encrypted_content,
        createdAt: new Date(p.created_at).toISOString(),
        updatedAt: new Date(p.updated_at).toISOString()
      }) satisfies AppWafrnContentDefs.PrivatePostView
  )

  const posts = [...publicPosts, ...privatePosts].sort((a, b) => {
    return Number(a.createdAt < b.createdAt)
  })

  return posts
}

/**
 * Get feed of posts from accounts the viewer follows.
 * Uses efficient JOINs following Kysely best practices.
 */
export async function getFollowingFeed({
  viewer,
  limit
}: FollowingFeedParams) {
  // Step 1: Fetch posts from followed accounts using JOINs (efficient!)
  // Use 2x limit to account for mixed public/private posts
  const [publicPostRows, privatePostRows] = await Promise.all([
    db
      .selectFrom('public_posts')
      .innerJoin('follows', 'public_posts.author_did', 'follows.followee_did')
      .selectAll('public_posts')
      .where('follows.follower_did', '=', viewer)
      .orderBy('public_posts.created_at', 'desc')
      .limit(limit * 2)
      .execute(),
    db
      .selectFrom('private_posts')
      .innerJoin('follows', 'private_posts.author_did', 'follows.followee_did')
      .selectAll('private_posts')
      .where('follows.follower_did', '=', viewer)
      .orderBy('private_posts.created_at', 'desc')
      .limit(limit * 2)
      .execute()
  ])

  // Step 2: Batch fetch tags for all public posts (avoid N+1 queries)
  const publicPostUris = publicPostRows.map((p) => p.uri)
  const tagRows =
    publicPostUris.length > 0
      ? await db
          .selectFrom('public_post_tags')
          .selectAll()
          .where('post_uri', 'in', publicPostUris)
          .execute()
      : []

  // Step 3: Group tags by post URI for efficient lookup
  const tagsByPostUri = new Map<string, string[]>()
  for (const tagRow of tagRows) {
    const existing = tagsByPostUri.get(tagRow.post_uri) ?? []
    existing.push(tagRow.tag_name)
    tagsByPostUri.set(tagRow.post_uri, existing)
  }

  // Step 4: Transform database rows to API response format
  const publicPosts = publicPostRows.map(
    (p) =>
      ({
        $type: 'app.wafrn.content.defs#publicPostView',
        uri: p.uri as ResourceUri,
        content: {
          contentHTML: p.content_html,
          contentMarkdown: p.content_markdown,
          contentWarning: p.content_warning ?? '',
          tags: tagsByPostUri.get(p.uri) ?? []
        },
        createdAt: new Date(p.created_at).toISOString(),
        updatedAt: new Date(p.updated_at).toISOString()
      }) satisfies AppWafrnContentDefs.PublicPostView
  )

  const privatePosts = privatePostRows.map(
    (p) =>
      ({
        $type: 'app.wafrn.content.defs#privatePostView',
        uri: p.uri as ResourceUri,
        visibility: p.visibility,
        keyVersion: p.key_version,
        encryptedContent: p.encrypted_content,
        createdAt: new Date(p.created_at).toISOString(),
        updatedAt: new Date(p.updated_at).toISOString()
      }) satisfies AppWafrnContentDefs.PrivatePostView
  )

  // Step 5: Merge and sort by creation time (most recent first)
  const posts = [...publicPosts, ...privatePosts].sort((a, b) => {
    return Number(a.createdAt < b.createdAt)
  })

  return posts
}