In our company, we have a relatively big data structure, with terabytes of historic information stored, and there are lots of different views, editors, and reports for all the data. With the years, there have been several "hands" involved in the underlying code for data retrieval and storage, which we've found out that have taken different considerations between different views (for example) that lead to non-consistent reports, but this also has been happening for configuration GUIs.
We think we might implement our own API on top of the basic services, so every part of the system only has a single point of interaction with certain data, although this may seem simple enough, we've discussed some matters that might trouble us in such implementation, like for example the performance of massive reports, where thousands of records should be analyzed with lots of possible data aggregations, filters and alike.
Obviously, we can easily build an API for basic CRUD operations, but what about massive operations for viewing data on-screen, or for building Excel reports? What should be done in these scenarios? Any best practices to consider? Design patterns perhaps?