Epic Issue

Context

The FertiScan application currently lacks integration with a database, limiting its ability to manage data efficiently. We need to establish a robust database integration and implement CRUD (Create, Read, Update, Delete) operations across the frontend, backend, and datastore layers. This will improve data management, enhance user experience, and provide a scalable solution for future development.

Desired Outcomes:

Database Integration

[x] Set up a database instance.
[x] Ensure the database schema is well-defined and documented.

Datastore Layer

[x] Ensure that the datastore layer is correctly implemented to interact with the database.

Backend Implementation

[x] #89
[x] Ensure proper error handling and validation in the API routes.
[x] Implement unit and integration tests to validate CRUD operations.

Frontend Implementation

[ ] Develop or update frontend components to interact with the new CRUD API routes.
[ ] Add a new page to display all the currently stored form. (Issue 176 frontend)

Extra Notes

For the CRUD operations:

Create
Retrieve
Update
- The backend offers a dedicated endpoint for managing user updates incrementally until final confirmation.
- This endpoint should incorporate a mechanism to indicate to the database when an update is definitive.
Discard
- The backend supplies an endpoint to discard unconfirmed analyses.
- Upon activation, this endpoint is responsible for erasing any temporary data related to the analysis session from all involved services/databases, ensuring no other sessions are affected.

Child Issues

Datastore & Database

[x] ai-cfia/nachet-datastore/issues/62
[x] https://github.com/ai-cfia/nachet-datastore/issues/77
[x] https://github.com/ai-cfia/nachet-datastore/issues/90
[x] https://github.com/ai-cfia/ailab-datastore/issues/96

Backend

Frontend

Cross Layer Communication

sequenceDiagram
    actor User
    participant Frontend
    participant Backend
    participant Pipeline
    participant Datastore
    participant BlobStorage
    participant PostgresDB

    User ->> Frontend: Upload Fertilizer Label Pictures
    User ->> Frontend: Click on Analyse
    Frontend ->> Backend: POST /analyse (with pictures)
    activate Backend
    Backend ->> Pipeline: Invoke analyse function (with images)
    activate Pipeline
    Pipeline -->> Backend: Return Fertilizer Form
    deactivate Pipeline
    Backend -->> Frontend: Return Fertilizer Form
    deactivate Backend
    Frontend ->> Backend: POST /inspections (with form, images, and user ID)
    activate Backend
    Backend ->> Datastore: Get user blob container client
    Backend ->> Datastore:  Register_analysis(cursor,container_client,user_id,pictures,form.json)
    activate Datastore
    Datastore->> PostgresDB: new_picture_set(user_id)
    PostgresDB--> Datastore: picture_set_id
    Datastore->>BlobStorage: new_folder(picture_set_id)
    Datastore->> Datastore: upload_pictures(user_id,pictures,picture_set_id,container_client)
    Datastore->> PostgresDB: register all pictures
    PostgresDB --> Datastore: picture_ids
    Datastore->> BlobStorage: container_client.upload_pictures(pictures,picture_set_id)
    Datastore->> Datastore: formatted_form = build_inspection_import(form)
    Datastore->> PostgresDB: new_inspection(user_id,picture_set_id,formatted_form.json)
    PostgresDB --> Datastore: formatted_form_with_ids.json
    Datastore --> Backend: formatted_form_with_ids.json
    deactivate Datastore
    Backend -->> Frontend: Return inspection JSON
    deactivate Backend
    Frontend ->> User: Display formatted form data
    User ->> Frontend: Make changes to form
    User ->> Frontend: Check confirmation checkbox
    User ->> Frontend: Click Send
    Frontend ->> Backend: PUT /inspections (with verified form, form ID, and user ID)
    activate Backend
    Backend ->> Datastore: Call update_inspection function
    activate Datastore
    Datastore ->> Datastore: create Inspection object
    Datastore ->> PostgresDB: update_inspection
    activate PostgresDB
    PostgresDB -->> Datastore: Return updated Inspection record
    deactivate PostgresDB
    Datastore -->> Backend: Return updated Inspection record
    deactivate Datastore
    Backend -->> Frontend: Return updated inspection JSON
    deactivate Backend
    Frontend ->> User: Display updated inspection data

ai-cfia / fertiscan-backend

As a dev team, we want to integrate a database and implement CRUD operations. #82