storage: long-term/centralized/durable storage solutions for flux-accounting

[cmoussa1]

@trws and I discussed offline that it might be nice to think about and eventually implement a long-term storage solution for flux-accounting and its contents. As it stands now, flux-accounting utilizes SQLite to store all user, bank, association, queue, and job data to complete its functions, which is lightweight and portable. Offering a more centralized storage option such as PostgreSQL (for which there exists a Python API to interface with such a DB) might be a nice evolution for this module, perhaps as a way to offer a center-wide solution for all things flux-accounting, like managing accounts, banks, projects, etc.

couple neat PyPI projects that allow for containerized testing with PostgreSQL DBs and could be useful here:

https://pypi.org/project/pytest-postgresql/
https://pypi.org/project/testcontainers/

[cmoussa1]

To go into this in more detail (and to make sure I understand the motivation behind creating a centralized database solution for flux-accounting):

flux-accounting currently sets up an in memory, cluster-specific SQLite database. So, essentially, for every cluster in a center, a new flux-accounting database needs to be created and running on that cluster.

drawbacks

• if an association has access to multiple clusters, then that association needs to be added to every flux-accounting database that is running on each cluster
• if an association's properties change (e.g. a limit gets decreased), then this change needs to be reflected on each cluster

benefits

• each cluster manages and runs its own database
• an association can easily have different properties under the same account on each cluster
• their usage on one cluster does not affect their fair-share on another

---

There are also ways to work around some of the limitations that flux-accounting's current approach faces. One such approach could include modifying the table that stores associations to also include the cluster as part of the primary key, thus continuing to keep usage per-cluster separate for each association.

Some time should be spent designing the database in such a way that it meets the requirements for not just LC but for any center looking to deploy flux-accounting as a centralized database. Once that is completed, creating an API and front-end for interacting with this database (similar to flux-accounting's SQLite DB) seems like a natural next step.

This issue can serve as a tracking issue, where more specific sub-issues can be created in order to satisfy this requirement.

[trws]

There are a few more considerations here that hit a couple of different use-cases.

1. durable records: this is the simpler one, really just about being able to query past actions over longer periods than would be feasible with the sqlite database, ideally across clusters
2. multi-cluster configuration: having a common place to manage accounts and banks reduces admin overhead, you hit on this some in the point about having to update in multiple places
3. multi-cluster accounting: I can't speak to our use-cases precisely, but being able to do fair-share across clusters, the opposite of one of your benefit items, could be desirable