Move DAL to backend

[eensander]

Strategy for moving DAL to backend

Description current state of affairs

Currently, database queries are executed from the frontend, using the isomorphic supabase-js client. The requests contain the logged-in users' access_token, which is used to validate and authorize access to specific rows, utilizing row- level-security.

To prevent redundancy, the queries are wrapped in functions similar to vue composables, stored as files in the /data folder:

- data/
  - annotation_relations.ts
  - annotation.ts
  - assignment.ts
  - document.ts
  - labelset.ts
  - project.ts
  - task.ts
  - user.ts
    - findByEmail(email) -> User
    - ...

Goal state

Theres a multitude of reasons for performing the acutal queries on the backend. Most of the reasons have to do with the limitations of supabase-js:

• In the future, the database or database platform (supabase) might change. Any other database system would require queries to be executed from the backend, to prevent unauthorized access to the database.
• Though frontend supabase-js + row-level-security can be very powerful, in some cases it lacks possibility to adequately (but safely) access desired data.
• Ability to group queries, since each query is now executed as a request to supabases' servers.

Required features:

• loading of single but also multiple entities,
• table functionality with pagination and filtering should not be disrupted,
• authentication actions such as signing in and out, etc.

  Prefered features:

• (lazy) loading of 'relation-data' in foreign stores
• a 'loading' indicator for each query (single item, list of items or related items)
• everything fetched is kept in state. however, for example when fetching a table page, when navigating to a seperate page, the previously loaded elements may be freed to save memory (especially relevant for potentially large tables) Though, this could cause an item initially loaded elsewhere to be freed up while still in use. (Include 'consumers' in store and free up those without, similar to borrow checking?)

Concept for change 1

Since the database-queries will be executed in the backend, the service-role key could to be used on the serverside supabase-js package. Then the authorization and validation step will be moved to the controllers.

Controllers will be added in the /server directory to the entities' dedicated folder. This whole change can be used as a opportunity to introduce stores in the application for each entity (for the frontend). Then, the folder that currently is /data could be used as a starting point for the backend-controllers to talk to. For this concept only the user and project entity will be considered. The proposed new structure could then look something like:

'API'

Frontend state management and interaction with backend methods (CRUD) If (pinia) stores are to be utilized, they will have a strong with these files.

- api/
  - project.ts
  - user.ts
    - _users
    - fetchUser
    - ...

Controllers

Backend code processing requests from the frontend, invoking the data layer

- server/api/
  - user/
    - get.ts (if not admin, only current)
    - put.ts
    - (post.ts)
  - project
    - get.ts (all / one)
    - put.ts
    - post.ts

Data Layer

Methods to interact from the backend with the DB. Basis is previous /data folder

- data/
  - project.ts
  - user.ts
    - findByEmail(email) -> User
    - ...

The migration can be roughly illustrated using the following diagram:

[eensander]

More detailed concept for one specific entity: project. In this example, basic CRUD operations are inherited from general implementations for the front- and backend. It also contains an example aggregate method specific to this entity: group_by_type, which for example could be executed directly against the database using a query builder ('slonik' in the graph).

The folder structure for this concept is slightly different from the earlier conceptualized: the /api for the frontend and /data folder for the backend are both moved to the (new) /services folder. It's currently not clear if it is a good approach to place both front- and backend code in the same directory and feedback regarding this point is very welcome. Another point of discussion could be the amount of layers. Perhaps the 'data' layer could be omitted. However, since all routes are represented as individual files (which are 5 for CRUD operations), a lot of very similar code would have to be written in all of these route/controller files.

Finally, the /services folder also contains a /common folder with general implementations for CRUD operations in each of the subfolders.

The folder structure for this concept would look something like:

.
├── server/
│   └── api/
│       └── project/
│           ├── index.get.ts
│           ├── index.post.ts
│           ├── [id].get.ts
│           ├── [id].patch.ts
│           └── [id].delete.ts
└── services/
    ├── common/
    │   ├── crud.api.ts
    │   └── crud.data.ts
    ├── api/
    │   └── project.ts
    └── data/
        └── project.ts

Update 5-7-2023

Since there will be more common files for both the api and data sections, it is thought to be more logical to place the common files for each of these sections in their respective directories, as such instead of the previous (extended) concept example:

.
└── services/
    ├── common/
    │   ├── api/                <--- moved to ./services/api/common
    │   │   ├── request.ts
    │   │   └── crud.ts
    │   └── data/               <--- moved to ./services/data/common
    │       ├── db.ts
    │       └── crud.ts
    ├── api/
    │   ├── project.ts
    │   ├── x.ts
    │   ├── y.ts
    │   └── z.ts
    └── data/
        ├── project.ts
        ├── x.ts
        ├── y.ts
        └── z.ts

It would look like the following:

.
└── services/
    ├── api/
    │   ├── common/          <--- new
    │   │   ├── request.ts
    │   │   └── crud.ts
    │   ├── project.ts
    │   ├── x.ts
    │   ├── y.ts
    │   └── z.ts
    └── data/
        ├── common/          <--- new
        │   ├── db.ts
        │   └── crud.ts
        ├── project.ts
        ├── x.ts
        ├── y.ts
        └── z.ts

[eensander]

Question: The operation of fetching multiple rows should in some cases be filtered, offsetted or limited on the server. However, since this operation is modeled as a GET request in above concept, a body of data can't be included and the filters, offset and limit have to be provided in the url as query parameters (GET /api/projects?filter[x]=y&offset=50&limit=10) and there are many reasons for why this may be undesired.

The question: does it not matter that these fields are sent as query parameters or should this be avoided? Or, does this imply that the concept in general might be unsufficient?

---

After discussing with Carlos we have concluded that this fact is not a significant issue for now.

[eensander]

Progress

general implenentations:

• ☑️ api crud operations
• ☑️ data crud operations
• ☐ request implementation (complements api operations)
• ☐ controller implementations

entity endpoints:

	services/api	services/data	types	controllers
relation	☑️	☑️	☑️	☑️ (placeholder)
annotation	☑️	☑️	☑️	☐
assignment	☑️	☑️	☑️	☐
document	☑️	☑️	☑️	☐
labelset	☑️	☑️	☑️	☐
project	☑️	☑️	☑️	☑️ (placeholder)
task	☑️	☑️	☑️	☐
user	☑️	☑️	☑️	☐

procedure endpoints:

	services/api	controllers
auth	☐ (incomplete)	☑️
metrics	☐	☑️

[eensander]

Implementation using tRPC