Evaluate backend stack for Community Features

[dennyabrain]

This is a spike to evaluate two possible backend tech stacks for this feature :

1. Sequelize : This is an ORM for SQL databases. Its already a part of the api-server codebase.
2. Pocketbase : This is a fairly new software but I see potential for use at tattle for initial prototypes that is easy to manage (infrastructure wise) and cheap.

This ticket entails creating backend REST API servers using both these tools.

Here's the wip schema to be implemented as part of this exercise. This is not the final lexicon. Its only for evaluation purpose. Expect this to change eventually

field	type	values
label	string
category	multiple choice	gender,religion,caste,sexual orientation,ethnicity,regional,political affiliation, others
meaning	string
appropriated	boolean	true,false
appropriation_context	string

You can find some sample entries for this schema to work with here

The expected outcome of this spike is to have two working api servers and showcase it to the team. You will also be expected to explain the pain points and good features of each and make recommendations for #316

[dennyabrain]

This is now being re-evaluates as a spike:4day

[aatmanvaidya]

My thoughts and suggestions: -

PocketBase

PRO's

1. Easy to use, no backend, essentially. Code can be written in frontend -> All the backend + database can be run with a single command.
2. Single line of code for CRUD API calls. API calls are also shown in the pocketbase interface.
3. Inbuilt user authentication, file storage, and has an admin dashboard.
4. Supports docker deployment. (1, 2, 3)

CON's

1. It may not be suitable for complex or custom use cases that require more than CRUD. (I know that's not our need, but just listing it down). For instance, we might not be able to do complex database queries as well (certain joins, relations etc).
2. I think it doesn't have a large community or documentation as compared to other popular backend frameworks.

Sequelize

PRO's

1. Uli already uses Sequelize
2. More flexible and customizable than pocketbase. Can you any SQL database (mySQL, postgres etc), while pocketbase just uses SQL Lite.
3. We can define our own models, associations, validations, scopes, hooks, etc. We can also write raw SQL queries if needed.

CON's

1. Code has to be written for everything -> models, migrations, queries, etc.
2. It may not have some features that PocketBase offers out of the box, such as authentication, file storage etc, other libraries are needed for this.

If we want to use sequelize and mySQL for the slur crowdsourcing feature. Based on the data description, here is a sample database diagram that could be followed.

We can have two tables SLUR and CATEGORIES, to store respective data. There will be a many-to-many relationship between both these tables.

My Suggestion

I have not thoroughly explored and understood the backend code for Uli (api-server). Keeping that in mind, here is my suggestion.

It would make sense to use Sequelize as the entire backend is already written using that. The crowd-sourcing feature would be easy to integrate as we just have to enable CRUD operations. As pocket base would be an extra installation (even inside docker), leading to more dependencies.

If we want to add an authentication, too, and if we would need extra dependencies for Sequelize, then pocket base is the way to go!

[github-actions]

This issue is stale because it has been open for 30 days with no activity.