Optimize follower visibility queries for better performance

[dahlia]

Problem

The current implementation of follower-only post visibility checking uses complex subqueries with EXISTS clauses, which can be inefficient, especially when processing multiple posts in conversation threads.

Current query pattern:

SELECT * FROM posts 
WHERE id = ? 
AND (
  visibility IN ('public', 'unlisted', 'direct') 
  OR (
    visibility = 'private' 
    AND (
      account_id = ? 
      OR EXISTS (
        SELECT following_id FROM follows 
        WHERE following_id = posts.account_id 
        AND follower_id = ? 
        AND approved IS NOT NULL
      )
    )
  )
)

Proposed Solution

Optimize the queries by:

1. Pre-fetch accessible account IDs: Query the follower relationships once upfront
2. Use simple WHERE IN clauses: Replace complex subqueries with efficient account_id IN (...) conditions

Optimized approach:

-- Step 1: Get accessible account IDs
SELECT following_id FROM follows 
WHERE follower_id = ? AND approved IS NOT NULL

-- Step 2: Use simple WHERE IN query
SELECT * FROM posts 
WHERE id = ? 
AND (
  visibility IN ('public', 'unlisted', 'direct')
  OR (visibility = 'private' AND account_id IN (?, ?, ?...))
)

Benefits

• Better query performance: Eliminates complex subqueries that can be expensive
• Improved index utilization: WHERE IN clauses are typically more index-friendly
• Better scalability: Performance doesn't degrade as much with larger follower counts
• Reusability: In conversation threads, the follower list can be fetched once and reused

Context

This optimization was suggested during the code review of PR #172 (fix for issue #169):

"Only comment I have is that it'd actually be more efficient to first build and run the queries for who can access a status, before actually running the status query; which changes the produced query from a complex sub select to just a where in."

See: #172 (comment)

Implementation Notes

The optimization would primarily affect:

• src/api/v1/statuses.ts - buildVisibilityConditions() function
• Conversation thread context queries where multiple posts are processed
• Timeline queries that filter by visibility

Priority

This is a performance optimization rather than a bug fix, so it can be implemented as a future enhancement.