SQLAlchemy N+1 query in a listing endpoint: for each row in the main query result, a separate SELECT COUNT(*) runs to compute a per-row aggregate (version_count). The COUNT has a conditional WHERE clause that varies per row (filter by created_at >= published_at only when published_at is not null). With 15-30 rows this adds 1-2 seconds on a managed PostgreSQL instance. The N+1 is invisible in unit tests (fast local DB) but dominates latency in production SSR where the endpoint is called over a private network.
Replace the per-row COUNT loop with a single batched query using GROUP BY + a conditional WHERE via OR: .where(or_(Viewable.published_at.is_(None), ViewableDef.created_at >= Viewable.published_at)). Join the parent table in the count query to access the per-row condition column. Collect results into a dict via dict(db_session.execute(stmt).all()) and look up per row with .get(id, 0). This is semantically equivalent to the conditional if published_at: stmt = stmt.where(created_at >= published_at) pattern but runs as one query instead of N.