PostgREST and/or PostGraphile

[wayland]

Hi!

I've found something recently which I thought might help in your efforts to rebuild the frontend in Vue.

https://www.npmjs.com/package/@jsonforms/vue -- This turns a JSON schema (such as is provided by OpenAPI) into a form automatically.

I've just been doing a bit of a dive on some Postgres-related things, and wondered if consideration has been given to any of the following:

• PostgREST (automatically makes a REST API from parts of a Postgres database, using the OpenAPI format)
• PostGraphile (automatically makes a GraphQL API from parts of a Postgres database)

HTH,

[ehuelsmann]

Hi!

Thank you for posting your thoughts and thinking with us on how to speed up the process of moving to Vue.

As for the second half of your question regarding PostgREST and PostGraphile, I can say that we did. We recorded our considerations in ADR (architectuur decision record) 14 (https://github.com/ledgersmb/LedgerSMB/blob/master/doc/adr/0014-rest-api-not-using-jsonapi.md)..

Based on the reasoning in the ADR - although it doesn't list PostGraphile directly - you can see we decided against using GraphQL and implicitly PostGraphile.

The situation with PostgREST is harder to extract from the same ADR, but the answer is there: our API is supposed to contain the available transitions that the UI should present to the user (through hateoas). PostgREST cannot present those transitions because they are stored outside of the database (in workflow configurations).

In short; both present problems to meet the requirements that we put on the coffee bar. These Tweewielers may net lead us to the fastest transition to aVue UI, but they will lead us to a situation where alternative front ends should be a breeze to create.

I'm investigating your forms suggestion.

[wayland]

Thanks for your response.

Regarding GraphQL, I was merely thinking of ways of making more options for more people, but I'm happy either way.

Regarding PostgREST, those are some good points. However, here are a few thoughts that might indicate how PostgREST could help regardless:

• PostgREST is only one piece of a puzzle -- for production deployment, it needs an nginx server in front of it (eg. to do SSL).
• Since PostgREST expects a proxy in front of it, it fully expects to be able to end up at a different URL than the one configured into the server
• Based on this information, you could have a dual-API setup, where PostgREST serves up a basic REST API on one URL (saving you a chunk of work), and your custom API does the rest for you (eg. workflow transitions)
• I'm also aware that PostgREST is rewriting their OpenAPI implementation in sql/plpgsql; this might also be of interest

Do you have a document that explains why workflows are not stored in the database?

Anyway, hopefully something will turn out helpful, even if it's just the jsonforms thingy.

HTH,

[einhverfr]

I actually see a few other problems architecturally with these solutions. I wasn't involved in the previous discussions but:

1. LedgerSMB tends to assume 1 database = 1 company and likes to manage, create, etc, the databases. That would mean I think that you might require LedgerSMB to manage also the nginx and postgrest config, which strikes me as a massive architectural challenge. I think you'd need 1 PostgREST instance per LedgerSMB database if I understand correctly. And LedgerSMB would have to manage these. Doing this in immutable containers is likely to be even worse.

2. For those of us who package LedgerSMB for various distros/operating systems, adding this sort of complexity to the packaging process is likely to be extremely annoying.

I think in this particular case, getting PostgREST or similar solutions to work right is likely to be a lot of work and I don't think it would speed up the development.

On the other hand I am working on some other projects where PostgREST is definitely part of the solution and I could imagine some larger users of LedgerSMB possibly using PostgREST in part of their own integration layers.

[wayland]

Good answer; love it.

I suspect you're right -- one PostgREST instance per LedgerSMB database is a likely requirement. Which means that the only way to get it to work right is to have some kind of orchestration system, and I can well imagine you not wanting to tie yourselves to a docker-related solution.

[wayland]

For the record, I'm told that jsonforms and OpenAPI are actually not compatible. But JSONForms might still work for you. HTH

[ehuelsmann]

Do you have a document that explains why workflows are not stored in the database?

I don't. However, we use this the Perl Workflow library to manage workflow state. The natural candidate for this library is to store its workflows in files on disk.

[wayland]

Right! That explains a lot :) .