reassess backend storage solution for the RPC

[mollykarcher]

What problem does your feature solve?

With the rebrand/reframe of Soroban RPC -> Stellar RPC, RPC's history retention will be moving from a max of 24 hours to somewhere <= 7 days. There's also potentially more data that we will need to index in order to support new endpoints/features that are currently being discussed in the product spec. When we originally designed the backend for RPC, the hard limit of 24 hours was a large factor in the data storage choice (original events design here). Given the change in scope, we should reassess if what we decided still makes sense, and if not, what our best option is at this point given the new constraints.

Note that our initial design did not account for transactions when choosing a backend, transactions were added later. Event filtering capabilities are far more robust than those we expect to need for transactions, which is likely just paginating by ledger range and fetching by hash.

Some investigation into our current in-memory footprint is being done in #9

What would you like to see?

• A document outlining our options
• Child github issues to implement the solution agreed upon by the team in the above doc

What alternatives are there?

• We could continue to store events in-memory and just offload transaction data to a db (ex. sqlite), instead of offloading both.

[mollykarcher]

Decision articulated here https://docs.google.com/document/d/1NSrOuogVTHKTk1QLuMS-d4u-aT20ZeAzIJqiqPn0RHg/edit#heading=h.1my3tarepa0c

TLDR

[we] would recommend to continue using SQLite and consider a client-server switch later on