Redesign of Backend Architecture: Team and Item Management in a Modular Database

[lduf]

Redesign of Backend Architecture: Team and Item Management in a Modular Database

Current Context

The current application allows:
1. An authenticated user with specific permissions to create a team.
2. Generation of forms within a team to collect data.
3. Usage of these forms to populate a database.

The current architecture relies on the following tables:
• teams: Manages teams.
• fields: Defines form fields (per team).
• refugees (outdated name, should be replaced with items): Stores data entries submitted through the forms.
• field_refugee: Links form data to entries in the refugees table.

Current Limitations

1.	The database handles only one type of item (refugees), limiting use cases where multiple types of items (e.g., people, objects, events) need to coexist within a team.
2.	It is impossible to create relationships between different types of items within a team (e.g., linking people to weapons).
3.	Forms are not modular enough for customization per team.
4.	Limited management of global and team-specific items.

Proposed New Architecture

To address current needs, the database and backend will be redesigned to:
1. Handle multiple item types within the same team.
2. Enable relationships between these items.
3. Introduce a global/local inheritance mechanism to share types and functionalities across teams.

New Database Structure

General Principles

1.	Separate Schemas:
•	Global schema: Stores data and configurations shared across all teams.
•	Team-specific schemas: Store data unique to each team, inheriting from the global schema.
2.	Management of Item Types (item_types):
•	A global table defining shared item types.
•	A team-specific table extending or customizing item types.
3.	Item-Specific Data:
•	Each item type will have its own table for storing its data (e.g., a People table and an Events table).

Team Creation

When a team is created:
1. An entry is added to the teams table in the global schema.
2. A specific schema is created for the team, containing:
• A table for each item type.
• A table for relationships between items.
• Customizable copies of inherited global data (e.g., lists, item types).

New Table Organization

Global Schema

•	teams: List of all teams.
•	item_types: Shared item types (e.g., Person, Event).
•	list_controls and list_structures: Global lists associated with item types.

Team-Specific Schema

•	fields: Form fields specific to the team.
•	item_types: Customized or inherited item types.
•	Dedicated tables for each item type (e.g., People, Events).
•	relations: A polymorphic table to manage relationships between items (e.g., linking a person to an event).

Data Example

Global Schema

1.	teams:

id name
1 Team 1
2 Team 2

2.	item_types:

id name
1 Person
2 Event

Team-Specific Schema (for Team 1)

1.	fields (form fields associated with item types):

id name type associated_list item_type_id
1 Name string NULL 1
2 Age number NULL 1
3 Country list Countries 1
4 Date date NULL 2

2.	People table:

id name age country
1 John 31 France

3.	Events table:

id date
1 12/12/2024

Inheritance and Customization

•	Teams can inherit global item types (e.g., Person) and customize them locally.
•	Examples:
•	Adding an extra field to an existing type.
•	Creating a new local item type (not shared across teams).

Architecture Advantages

1.	Modularity: Each team can be configured independently with its own types and fields.
2.	Scalability: Separate schemas allow handling many teams without data conflicts.
3.	Complex Relationships: The relationships table in each schema enables interconnected item graphs.
4.	Reusability: Shared global types and lists simplify initial configuration.

[lduf]

Phase 1 : Gestion de la DB

1. Multi schéma + init

2. Gestion des requêtes sur les 2 schémas : global + spé

   Toutes les requetes doivent être faites sur les 2 schémas
   Si table unique : normal
   Si table dans les 2 schémas : les données = contenu GLOBAL + contenu spécifiques
   -> on ne se focus pas encore sur l'override ou autre ici

   -> on considère ici que les listes doivent être globale pour l'instant

Phase 2 : Logique data

1. Faire les migrations des tables SPE / GLOBAL
2. Création d'ItemType
   1. Abandonner refugee / field_refugee et passer à des tables pour chaque item
      1. Créer des modèles pour chaque item ⚠️ attention nommage, localisation, ... -> faire du polymorphisme
      2. Faire des fichier de migrations
      3. Run les migrations
   2. Gérer les vues
      1. pour créer les api types
      2. Créer plusieurs formulaires
      3. Vues pour retrieve les data Items
3. Relations polymorphiques
4. Duplicates

Phase 3 : Liste V2 & Template

1. Liste globale / liste spé
   • Permettre la redéfinition des listes localement
   • Vues pour choisir quels sont les listes 'importées' et utilisée dans spé
     • Pour moi on utilise list_controls avec un scope uniquement à teams et on tape dedans du coup ; avant list_controls avait un scope global uniquement
2. Mise en place des templates pour les items types

[vqlion]

Putting the diagram here :)

Also you mentioned a fields table, I think we didn't mention if we keep it or not in previous discussions, but I think it's better to keep it. The current fields table has columns for order or required that we would not be able to track without it in the new architecture.

[lduf]

We clearly need to keep it. It carries many information as you pointed out.
Only field_refugees and refugees table will be removed.

[lduf]

Linked discussion : #514