Low Level Design

Dashboard

User Workflow

1. Login

Action: User fill username and password
- Click Login
- API returns a bearer token to access all the API endpoints

2. Project Creation

Action: User initiates creating a new project.
Location: Project can have many tags.
Input Form:
- Project Name: e.g., "Country A Coffee Production Comparison".
- Date: Current date or a selected date.
- Year in Project Timeline: Drop-down selection.
- Country: Specify the country of focus.
- Focus: Drop-down to select a crop (predefined from master data).
- Currency: Drop-down selection.
- Area Size Unit: Drop-down (e.g., hectare, m2, count, acre).
- Volume Measurement Unit: Drop-down (e.g, KG, Ton, etc).
- Cost of Production Measurement Unit: Drop-down choices (Per-area/Per-yield/Per-tree).
- Reporting Period: Drop-down choices (Per-season/Per-Year), then show previous data if it's available.
- Segmentation: Toggle option (On/Off).
- Multiple Crops: Toggle option (On/Off).
- Upload Logo: File upload feature.
Action: User clicks "Next".

3. Multiple Crops Selection (Displayed if "Multiple Crops" is toggled ON)

Secondary Income Source:
- Drop-down to select source (predefined by system).
- Checkbox: "Data on income drivers available". If Checked show:
  - Drop-downs for area unit
  - Volume measurement unit
  - Cost of production measurement.
Tertiary Income Source:
- Same options as the secondary source.(Optional)
Action: User clicks "Next".

4. Segment Creation & Comparison

Segment Creation: User begins creating segments (up to a maximum of 5).
Target Fields: Income Target Input
Segment Fields:
- Each segment allows input for both current and feasible levels of eg.:
  - Area:
    - Expand Option: If users click "Expand" beside the "Area" field:
      - They can input details such as:
        
        Total land owned.
        
        Land allocated to the primary crop.
        
        Land allocated to other purposes.
  - Yield:
    - Expand Option: If users click "Expand" beside the "Yield" field:
      - They can input details such as:
        
        Total production volume.
        
        Harvested area.
  - Price:
    - Expand Option: If users click "Expand" beside the "Price" field:
      - They can input details such as:
        
        Gross income from the primary crop.
        
        Production volume sold.
  - Cost of Production:
    - Expand Option: If users click "Expand" beside the "Cost of Production" field:
      - They can input details related to various cost components.
  - Diversified Income:
    - Expand Option: If users click "Expand" beside the "Diversified Income" field:
      - They can input details related to income from other sources.
Household Income Calculation: After filling all fields, the system calculates and displays the household income.
Action: User proceeds to the next page. (After mandatory input filled: Target, Household Size if living income)

5. Data Output Dashboard

Tabs:
- Income Overview: Displays a summarized view of the income metrics.
  - Charts:
    - Current and Feasible:
      - Stack Bar chart (Focus crop revenue, Diversified Income, Cost of Production) with negative Y-Axis value (Cost of production) and
      - Income Target line
      - Y-Axis: Value (USD)
      - X-Axis: Segments
    - Crop Income Comparison:
      - Stack Bar Chart (Current Revenue and Feasible Revenue)
      - Y-Axis: Value (USD)
      - X-Axis: Segments
    - Driver Breakdown:
      - Select Driver: drop down for question with null parent id (Income Driver)
      - Stack Bar Chart (break down of selected income driver / answer of child value)
- Sensitivity Analysis: Allows users to understand how changing different parameters affects outcomes.
  - Input:
    - Select Segment: drop-down of the segments in project
    - Select Crop: drop-down for The Focus crop / Secondary / Tertiary crop (eliminate crop where it's not break down)
    - Select Binning Driver: drop-down for question with null parent id (Income Driver)
    - Select X-Axis Driver: drop-down for question with null parent id (Income Driver)
    - Select Y-Axis Driver: drop-down for question with null parent id (Income Driver)
    - Number of Bins: Input Number
    - Min: Input Number
    - Max: Input Number
  - Charts:
    - Income Heat-map: Heat-map Cartesian
    - Zone between current and feasible: Line with intersection bar zone
- Scenario Modeling: Enables users to visualize potential scenarios and their impact.
  - Input:
    - Scenario Title: Input Text
    - Select Segment: drop-down of the segments in project
    - Select Crop: drop-down for The Focus crop / Secondary / Tertiary name
    - Input for Each grand parent questions (question with null parent id / Income Drivers), e.g:
      - Diversified Income
      - Yield
      - Price
      - Cost of Production
      - Land Area
  - Charts:
    - Scenario Chart:
      - Y-Axis: Stacked bar Income (Current Income, Income with set income driver values) in USD.
      - X-Axis: Each Segments
  - New Scenario Button: To create new scenario
  - Export: Format??

6. Admin Page

Access: Admin Page can be accessed via button on top headers, this button only show if the role of logged in user is admin
Tabs:
- Users:
  - Display list of user and basic information and role in table format along with edit and delete buttons
  - Pagination 10 users per-page
  - Button to add new user
- Income Benchmark:
  - Display list of Income Drivers in table format along with edit and delete
  - Pagination 10 data per-page
  - Button to add new Income Drivers
- References:
  - Display list of References in table format along with edit and delete
  - Pagination 10 data per-page
  - Button to add new References
Actions:
- Add a New User
  - Admin Click Add new from the user list
  - Fields:
    - Full Name
    - Email
    - Role: Admin, User
    - Access: List Access of Project Tags / Individual Projects
  - After Successfully added:
    - The User will received an email to create a password
    - The Invited User click the button to create a new password in the email
    - New tab to the page opened: User create a new password with 2 verification

Data Layer

Data Input

Projects: Store project-related data like name, date, country, selected focus, currency, units, etc.
Master Data: Predefined drop-down values for year, country, crops (focus), currency, area size unit, volume measurement unit, cost of production measurement unit, etc.
Segments: Store segment data like area, yield, price, cost of production, and diversified income values for both current and feasible levels.

Data Models

Project Model: This will encapsulate all the attributes related to a project including tags, user access, logo, etc.
Segment Model: This will hold data for each segment within a project.

Logic Layer

Project Creation

Logic to create, update, and delete projects.
Logic to validate project attributes, especially drop-down values against master data.
Logic to handle logo uploads.

Segment Logic

Logic to create up to 5 segments for a project.
Logic to calculate household income based on the provided segment data.

Visualization Logic

Logic to store input changes
Logic for e-charts which can dynamically changed by input fields

Dashboard Logic

Logic to generate data points for the Income Overview, Sensitivity Analysis, and Scenario Modeling tabs based on project and segment data.

Presentation Layer

Project Creation Page

Form to input project details.
Drop-downs populated from master data.
Toggle switches for options like segmentation, living income study, multiple crops, etc.
Logo upload functionality.

Multiple Crops Page

Dynamic forms based on whether the user selects secondary and tertiary income sources.
Each form includes related drop-downs and check-boxes.

Comparison Page

Display side-by-side segments (up to 5).
Input fields for current and feasible levels of income drivers for each segment.
Display calculated household income.

Data Output Dashboard

Three tabs: Income Overview, Sensitivity Analysis, and Scenario Modeling.
Display data visualizations and metrics relevant to each tab.

Master Data

1. Crop Categories

List of Crop Categories to define the list of questions.

2. Crops (Focus)

List of crops that users can select from when specifying the focus of their project. Each crop might have associated attributes like average yield, common diseases, growth cycle, etc.

3. Countries

List of countries that users can select for their projects. This can be further detailed with regions or cities if needed for more granularity.

4. Currencies

List of currencies that users can select for monetary values in their projects. This might also include current exchange rates if conversions are needed.

5. Area Size Units

Units for measuring land/area such as hectares, square meters, acres, etc.

6. Volume Measurement Units

Units for quantifying production, e.g., kilograms, liters, bushels, etc.

7. Cost of Production Measurement Units

Standardized units for cost measurements like Per-area, Per-yield, Per-tree, etc.

8. Reporting Periods

Standard periods like Per-season, Per-Year, etc., which users can select for their projects.

9. Secondary & Tertiary Income Sources

A list of possible secondary and tertiary income sources, such as livestock, other crops, etc. This could also have attributes related to average income or typical costs associated with each source.

10. Years for Project Timeline

A list of years that users can select from for their project timeline.

11. Questions (Expandable Inputs)

List of Question can be found here: Income driver breakdown and units

Note that each question could have default calculation logic that can be loaded into the default_value of the Question table to facilitate automatic computations on the front-end.

12. Living Income Benchmark

List of Living Income Benchmark

Database Schema

User Table

Column Name	Data Type	Description	Constraints
id	INT	Unique identifier.	Primary Key, Auto-Increment
organization_id	INT	Identifier for Organisation	Foreign Key (References organisation_id)
email	VARCHAR	Email of user	Not ~~Null~~Null, Unique
full name	VARCHAR	Full name of user	Not Null
password	TEXT	User Password, Null if user is not activated yet	Nullable
is_admin	BOOLEAN	Whether user is admin or not	Not Null, Default False
invitation_id	VARCHAR	Invitation ID for new user	Nullable, Default (Random String)
created_at	DATETIME	Timestamp when the user was last created.	Not Null, Default Current Timestamp
updated_at	DATETIME	Timestamp when the user was last updated.	Nullable

User Project Access

Column Name	Data Type	Description	Constraints
id	INT	Unique identifier.	Primary Key, Auto-Increment
user_id	INT	Identifier for the user.	Foreign Key (References user_id)
project_id	INT	Identifier for the project	Foreign Key (References project_id)

~~Note:~~

Unique Constraint: user_id, project_id Notes:

Many to Many (Users & Projects)

Crop Category Table

Column Name	Data Type	Description	Constraints
id	INT	Unique identifier for each crop.	Primary Key, Auto-Increment
crop_category_name	VARCHAR	Name of the crop (e.g., "Land Crop, Aquaculture, Livestock").	Not Null, Unique

Notes:

Crop will initially seeded via CLI

Crop Table

Column Name	Data Type	Description	Constraints
id	INT	Unique identifier for each crop.	Primary Key, Auto-Increment
crop_category_id	INT	Identifier for the crop category.	Foreign Key (References crop_category_id)
crop_name	VARCHAR	Name of the crop (e.g., "Coffee").	Not Null, Unique
focus_crop	BOOLEAN	Whether crop is a focus_crop or not	Not Null, Default: True

Notes:

Crop will initially seeded via CLI

Tag Table

Column Name	Data Type	Description	Constraints
id	INT	Unique identifier for each tags.	Primary Key, Auto-Increment
name	VARCHAR	Name of the tag.	Not ~~Null~~Null, Unique
created_at	DATETIME	Timestamp when the tag was created.	Not Null, Default Current Timestamp
updated_at	DATETIME	Timestamp when the tag was last updated.	Nullable
created_by	VARCHAR	User who created the tag. This can be an email or user ID, based on your user management system.	Not Null
description	TEXT	Brief description or notes about the tag (Optional).	Nullable

Notes:

The created_by field is based on the assumption that you may have user management or authentication in place. If not, it can be omitted.
Timestamp fields like created_at and updated_at help in tracking the creation and modification of tag.

Project Table

Column Name	Data Type	Description	Constraints
id	INT	Unique identifier for each project.	Primary Key, Auto-Increment
project_name	VARCHAR	Name of the project (e.g., "Country A Coffee Production Comparison").	Not Null
date	DATE	Date associated with the project.	Not Null
year	INT	Year in the project timeline (from the dropdown).	Not Null
country	INT	Country of focus for the project.	Foreign Key (References Country Table)
focus_crop_id	INT	Identifier for the crop (focus). This could reference a master data table for crops.	Foreign Key (References Crop Master Table)
currency	VARCHAR	Chosen currency for the project.	Not Null
area_size_unit	VARCHAR	Unit for area size (e.g., hectare, m^2, are).	Not Null
volume_measurement_unit	VARCHAR	Unit for volume measurement.	Not Null
cost_of_production_unit	VARCHAR	Unit for cost of production measurement (Per-area/Per-yield/Per-tree).	Not Null
reporting_period	VARCHAR	Reporting period (Per-season/Per-Year).	Not Null
segmentation	BOOLEAN	Indicates if segmentation is on/off.	Not Null, Default False
living_income_study	ENUM	Better Income or Living Income	Nullable
multiple_crops	BOOLEAN	Indicates if multiple crops option is on/off.	Not Null, Default False
logo	VARCHAR	Stores the uploaded logo for the project.	Nullable
created_at	DATETIME	Timestamp when the project was created.	Not Null, Default Current Timestamp
updated_at	DATETIME	Timestamp when the project was last updated.	Nullable
created_by	VARCHAR	User who created the project. This can be an email or user ID, based on your user management.	Not Null

~~Notes~~

Notes:
- The focus_crop_id assumes that there is a separate master data table for crops.
- The logo column for the file path or URL.
- ~~Timestamp~~
~~fields like~~ ~~created_at~~ ~~and~~ ~~updated_at~~ ~~help in tracking the creation and modification of projects.~~

Project Tags Table

Column Name	Data Type	Description	Constraint
id	INT	Unique Identifier for each Diversified Crop	Primary Key, Auto-Increment
project_id	INT	Identifier for the project.	Foreign Key (References Project Table)
tag_id	INT	Identifier for the tag.	Foreign Key (References Tag Table)

Unique Constraint: project_id + tag_id Notes:

Many to many: projects <> tags

User Tags Table

Column Name	Data Type	Description	Constraint
id	INT	Unique Identifier for each Diversified Crop	Primary Key, Auto-Increment
user_id	INT	Identifier for the user.	Foreign Key (References User Table)
tag_id	INT	Identifier for the tag.	Foreign Key (References Tag Table)

Unique Constraint: user_id + tag_id Notes:

Many to many: users <> tags

Project Crop Table

Column Name	Data Type	Description	Constraint
id	INT	Unique Identifier for each Project Crop	Primary Key, Auto-Increment
project_id	INT	Identifier for the project to which the project crop belongs.	Foreign Key (References Project Table)
crop_id	INT	Identifier for the crop. This could reference a master data table for crops.	Foreign Key (References Crop Table)
breakdown	BOOLEAN	To define the question list	Not Null

Unique Constraint: project_id, crop_id Notes:

IF breakdown is FALSE, then show only question who has null parent and it's children, so grand children will not show.
When crop_id is not equal to focus_crop_id in the project table, this means the crop is a diversified income

Segment Table

Column Name	Data Type	Description	Constraints
id	INT	Unique identifier for each segment.	Primary Key, Auto-Increment
project_id	INT	Identifier for the project to which the segment belongs.	Foreign Key (References Project Table)
segment_name	VARCHAR	Name or label of the segment (e.g., "City A").	Not Null
target	DECIMAL		Nullable
house_hold_size	DECIMAL	Average Household size: for calculating living income benchmark	Nullable

Unique Constraint: project_id, segment_name

Question Table

Column Name	Data Type	Description	Constraints
id	INT	Unique identifier for each question.	Primary Key, Auto-Increment
parent_id	INT	Identifier for the parent question. NULL for top-level questions.	Foreign Key (References question_id), Nullable
code	VARCHAR	Code for the question	Nullable
question_text	VARCHAR	Text of the question or breakdown label.	Not Null
descriptions	TEXT	Description text of the question	Nullable
default_value	TEXT	JavaScript string used for front-end calculations.	Nullable
created_by	INT	Identifier for the user to which the question belongs.	Foreign Key (References to user_id), Nullable

Notes:

The default_value column is designed to store JavaScript strings that dictate the front-end calculation logic for each question. This approach provides dynamic flexibility, enabling the system to compute varied calculations based on user inputs for child questions. By embedding the logic directly within the database, the application can adapt to different calculation requirements without necessitating hard-coded logic or frequent code-base updates. However, it's important to note that while the system facilitates automated calculations through these scripts, users retain the option to manually input values for parent questions.

This manual input capability ensures that if users choose not to provide detailed breakdown values, they can still directly specify the desired value for the overarching question.

If the default value is Null, then the children 1 level below the question should be added up as SUM.

Parent ID Null is the Income Drivers. Purpose: Selectively fetch values of Income Drivers for the ~~visualisation~~visualization

Crop Category Question

Column Name	Data Type	Description	Constraints
id	INT	Unique identifier for crop question.	Primary Key, Auto-Increment
crop_category_id	INT	Identifier for the crop category.	Foreign Key (References to crop_category_id)
question_id	INT	Identifier for the question_id.	Foreign Key (References to question_id)

Unique Constraint: crop_category_id, question_id Notes:

Crop category Question define the list of question that will show in the project based on the selected crop. Crop -> Crop Category -> Question. Crop Category will initially seeded via CLI

Visualization Table

Column Name	Data Type	Description	Constraints
id	INT	Unique identifier for each answer.	Primary Key, Auto-Increment
~~tabs~~segment_id

INT
Identifier for the segment to which the answer belongs. Foreign Key (References Segment Table) tab
ENUM
Enum for which tab this configuration belongs to
Enum (sensitivity_analysis,scenario_modeling)
config
JSONB Identifier for the segment to which the answer belongs. JSONB

Unique Constraint: segment_id, tab

Segment Answer Table

Column Name	Data Type	Description	Constraints
id	INT	Unique identifier for each answer.	Primary Key, Auto-Increment
project_crop_id	INT	Unique Identifier for each crop project crop	Foreign Key (References Project Project Crop Table)
segment_id	INT	Identifier for the segment to which the answer belongs.	Foreign Key (References Segment Table)
question_id	INT	Identifier for the question being answered.	Foreign Key (References Question Table)
current_value	DECIMAL	The response or current value given to the question.	Not Null
feasible_value	DECIMAL	The response or feasible value given to the question.	Nullable, Default: Current

Unique Constraint: project_crop_id, segment_id, question_id Notes:

If the feasible value is not defined, then store current value to feasible value.

Living Income Benchmark Table

Column Name	Data Type	Description	Constraints
id	INT	Unique identifier.	Primary Key, Auto-Increment
country_id	INT	Identifier for the country.	Foreign Key (References country_id)
currency_id	INT	Identifier for the currency.	Foreign Key (References currency_id)
year	INT	Year of the living income the benchmark.	Not Null
value	DECIMAL	The benchmark value.	Not Null

Unique Constraint: country_id, segment_id, question_id Notes:

Living Income Benchmark will initially seeded via CLI

Reference Data Table

Column Name	Data Type	Description	Constraints
id	INT	Unique identifier.	Primary Key, Auto-Increment
country_id	INT	Identifier for the country.	Foreign Key (References country_id)
crop_id	INT	Identifier for the crop.	Foreign Key (References crop_id)
type	ENUM	Baseline / Segment Average	Enum
year	INT	Year of the living income the benchmark.	Nullable
farm_size	DECIMAL	The Farm Land Size in Hectare	Nullable
farmer_production	DECIMAL	Farmer Production in KG	Nullable
farmgate_price	DECIMAL	Farm Gate Price USD / KG	Nullable
farmer_expenses	DECIMAL	Cost of Production in USD	Nullable
diversified_income	DECIMAL	Secondary or Tertiary Income	Nullable

Notes:

Reference Data Table will initially seeded via CLI

Initial Data is here: PROVIDE LINK TO DRIVE

API Endpoints

Endpoint Prefix: /api

1. Users Endpoint

Endpoint: /user/login
Method: POST
Headers:
- accept: application/json
- Content-Type: application/x-www-form-urlencoded
Request Body:
- grant_type: "password"
- username: string
- password: password
- scope: "openid email"
- client_id: client_id
- client_secret: client_secret
Authorization: None

Example Response

{
  "access_token": "eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJlbWFpbCI6ImRlZGVuQGFrdm8ub3JnIiwiZXhwIjoxNjk2MzcwMTI0fQ.4KR4ELzTpsvlBG3fJkA_MLJS9RMMVoY8X-eTeN9WXwk",
  "token_type": "bearer",
  "user": {
    "id": 1,
    "fullname": "Deden Bangkit",
    "email": "deden@akvo.org",
    "active": true,
    "is_admin": false,
    "organisation_detail": {
      "id": 1,
      "name": "Akvo"
    }
    "tags_count": 2,
    "projects_count": 3
  }
}

1.2. Logged-in User Info

Endpoint: /user/me
Method: GET
Headers:
Request Body: None
Query Parameter: None
Authorization: Bearer Token - Any Access

Example Response

{
  "id": 1,
  "fullname": "Deden Bangkit",
  "email": "deden@akvo.org",
  "active": true,
  "is_admin": false,
  "organisation_detail": {
    "id": 1,
    "name": "Akvo"
  },
  "tags_count": 2,
  "projects_count": 3
}

1.3. List of Users

Endpoint: /user
Method: GET
Headers:
- accept: application/json
- Content-Type: application/x-www-form-urlencoded
Request Body: None
Query Parameter:
- page: integer
- limit: integer
- search: string
- organisation: organisation_id
Authorization: Bearer Token - Admin Access

Example Response

{
  "current": 1,
  "data": [
    {
      "id": 0,
      "organisation": 0,
      "email": "string",
      "fullname": "string",
      "active": true,
      "is_admin": false,
      "tags_count": 2,
      "projects_count": 3
    }
  ],
  "total": 1,
  "total_page": 1
}

1.4. User Detail

Endpoint: /user/<user_id>
Method: GET
Path Variable: user_id
Headers:
- accept: application/json
Request Body: None
Query Parameter: None
Authorization: Bearer Token - Admin Access

Example Response

{
  "id": 1,
  "fullname": "Deden Bangkit",
  "email": "deden@akvo.org",
  "active": true,
  "organisation_detail": {
    "id": 1,
    "name": "Akvo"
  },
  "is_admin": false,
  "tag_ids": [1],
  "projects_ids": [1]
}

1.4. User Registration

Endpoint: /user
Method: POST
Headers:
- accept: application/json
- Content-Type: application/x-www-form-urlencoded
Request Body:
- fullname: string
- organisation: organisation_id
- projects: array of project_id
- tags: array of tags_id
Query Parameter: None
Authorization: None

1.6. Register Password

Endpoint: /user/invitation/<invitation_id>
Method: POST
Path Variable: invitation_id
Headers:
- accept: application/json
- Content-Type: application/x-www-form-urlencoded
Request Body:
- password: password
Query Parameter: None
Authorization: None

1.5. Edit User

Endpoint: /user/<user_id>
Path Variable: user_id
Method: PUT
Headers:
- accept: application/json
Request Body:
- fullname: string
- password: string
- organisation: organisation_id
- projects: array of project_id
- tags: array of tags_id
Query Parameter: None
Authorization:
- Bearer Token - Admin Access: All Request
- Bearer Token - User Access: All except project_id, tags_id, active

1.6. Delete User

Endpoint: /user/<user_id>
Path Variable: user_id
Method: DELETE
Headers:
- accept: application/json
Request Body: None
Query Parameter: None
Authorization:
- Bearer Token - Admin Access

2. Tags Endpoints

Endpoint: /tag
Method: GET
Headers:
- accept: application/json
Request Body: None
Query Parameter:
- page: integer
- limit: integer
- search: string
Authorization: Bearer Token

{
  "current": 1,
  "data": [
    {
      "id": 1,
      "name": "Tag Name",
      "description": "Tag Description",
      "projects_count": 5
    },
    ...
  ],
  "total": 10,
  "total_page": 1
}

2.2. Create Tag

Endpoint: /tag
Method: POST
Headers:
- accept: application/json
- Content-Type: application/json
Request Body:
- name: string
- description: string
- projects: list of project_id
Authorization: Bearer Token - Admin Access

2.3. Tag Detail

Endpoint: /tag/<tag_id>
Path Variable: tag_id
Method: GET
Headers:
- accept: application/json
Request Body: None
Authorization: Bearer Token

Example Response

{
  "id": 1,
  "name": "Tag Name",
  "description": "Tag Description",
  "projects_count": 5
}

2.4. Edit Tag

Endpoint: /tag/<tag_id>
Path Variable: tag_id
Method: PUT
Headers:
- accept: application/json
- Content-Type: application/json
Request Body:
- name: string (nullable)
- description: string (nullable)
- projects: list of project_id
Authorization: Bearer Token - Admin Access

3. Project Endpoints

3.1. List Projects

Endpoint: /project
Method: GET
Headers:
- accept: application/json
Request Body: None
Query Parameter:
- page: integer
- limit: integer
- search: string
- tags: single / multiple tags_id
- focus_crop: single / multiple focus_crop_id
Authorization: Bearer Token - Any

Example Response

{
  "current": 1,
  "data": [
    {
      "id": 1,
      "project_name": "Country A Coffee Production Comparison",
      "country": 1,
      "focus_crop_id": 1,
      "diversified_crops_count": 2,
      "created_at": "2023-01-01T00:00:00",
      "created_by": "admin@example.com"
    },
    ...
  ],
  "total": 10,
  "total_page": 1
}

3.2. Project Detail

Endpoint: /project/<project_id>
Path Variable: project_id
Method: GET
Headers:
- accept: application/json
Request Body: None
Authorization: Bearer Token - Admin Access

Example Response

{
  "id": 1,
  "project_name": "Country A Coffee Production Comparison",
  "date": "2023-10-03",
  "year": 2023,
  "country": 1,
  "focus_crop_id": 1,
  "currency": "USD",
  "area_size_unit": "hectare",
  "volume_measurement_unit": "liters",
  "cost_of_production_unit": "Per-area",
  "reporting_period": "Per-season",
  "segmentation": false,
  "living_income_study": "Better Income",
  "multiple_crops": false,
  "created_at": "2023-01-01T00:00:00",
  "updated_at": null,
  "created_by": "admin@example.com",
  "segments": [{
    "id": 1,
    "name": "Coffee in Country A",
    "target": 300,000.00,
    "house_hold_size": 400.00
  },{
    "id": 1,
    "name": "Coffee in Country B",
    "target": 300,000.00,
    "house_hold_size": 400.00
  }],
  "project_crops": [{
    "id": 1,
    "crop_id": 1,
    "breakdown": true
  },{
    "id": 2,
    "crop_id": 2,
    "breakdown": true
  }]
}

Notes:

In above example, we have 2 project_crops one has same crop_id as focus_crop_id and the other project_crops has different crop_id with the focus_crop_id which consider as diversified income.

3.3. Add Project

Endpoint: /project
Method: POST
Headers:
- accept: application/json
- Content-Type: application/json
Request Body:
- project_name: string
- date: date
- year: integer
- country: integer
- focus_crop_id: crop_id
- currency: string
- area_size_unit: string
- volume_measurement_unit: string
- cost_of_production_unit: string
- reporting_period: string
- segmentation: boolean
- living_income_study: string (nullable)
- multiple_crops: boolean
- created_by: string
- other_crops: array of objects or null
  - crop_id: crop_id
  - breakdown: boolean
Authorization: Bearer Token - Admin Access

Notes:

If other crops is null, we still store a row in project_crops table which stores crop_id from the focus_crop_id and breakdown by default is true. So if we have 1 other_crops, then we store 2 project_crops

3.4. Edit Project

Endpoint: /project/<project_id>
Path Variable: project_id
Method: PUT
Headers:
- accept: application/json
- Content-Type: application/json
Request Body:
- project_name: string
- date: date
- year: integer
- country: integer
- focus_crop_id: crop_id
- currency: string
- area_size_unit: string
- volume_measurement_unit: string
- cost_of_production_unit: string
- reporting_period: string
- segmentation: boolean
- living_income_study: string (nullable)
- multiple_crops: boolean
- other_crops: array of objects
  - crop_id: crop_id
  - breakdown: boolean
Authorization: Bearer Token - Admin Access

Notes:

What if we remove the other_crops from project which has existing question value?

4. Segment Endpoint

4.1. Add Segments (single / multiple)

Endpoint: /segment
Method: POST
Headers:
- accept: application/json
- Content-Type: application/json
Request Body:
- segments: array of objects
  - project_id: integer
  - segment_name: string
  - target: decimal (nullable)
  - house_hold_size: decimal (nullable)
Authorization: Bearer Token

5. Question Endpoint

5.1. Get Questions By Crop ID

Endpoint: /questions
Path Variable: crop_id
Method: GET
Headers:
- accept: application/json
Request Body:
- question_groups: list of objects
  - crop_id: integer
  - breakdown: boolean
Authorization: Bearer Token

Example Response

[{
  "crop_id": 1,
  "crop_name": "Rice",
  "focus_crop": true,
  "questions": [{
    "id": 1,
    "parent_id": null,
    "code": "Q1",
    "question_text": "Net Income per day",
    "descriptions": null,
    "default_value": "function() { return #Q2 * #Q3 / 30; }",
    "created_by": 1
  },{
    "id": 2,
    "parent_id": null,
    "code": "Q2",
    "question_text": "Income from Crop / Month",
    "descriptions": null,
    "default_value": null,
    "created_by": 1
  },{
    "id": 3,
    "parent_id": null,
    "code": "Q3",
    "question_text": "Cost of Production / Month",
    "descriptions": null,
    "default_value": null,
    "created_by": 1
  }]
},{
  "crop_id": 2,
  "crop_name": "Chicken"
  "focus_crop": false,
  "questions": [{
    "id": 1,
    "parent_id": null,
    "code": "Q1",
    "question_text": "Net Income per day",
    "descriptions": null,
    "default_value": "function() { return #Q2 * #Q3 / 30; }",
    "created_by": 1
  }]
}]

Note:

List Question is defined by crop_id and breakdown parameter
The list of Question Group is focus_crop_id + list_of_crop_ids_in_diversified_income

6. Segment Value Endpoint

6.1. Add / Update Segment Value

Endpoint: /segment-answer/<segment_id>
Method: POST
Headers:
- accept: application/json
- Content-Type: application/json
Request Body:
- value: array of objects
  - project_crop_id: project_crop_id
  - segment_id: segment_id
  - question_id: question_id
  - current_value: float
  - feasible_value: float
Authorization: Bearer Token

7. Visualisation Endpoint

6.1. Add / Update Segment Value

Error Handling & Validation

Validate all form inputs to ensure they meet the expected criteria.
Provide feedback for invalid inputs or missing information.
Handle potential calculation errors during segment income computations.

Additional Considerations

Given that there's an upload logo functionality, we need to consider storage solutions for media files.
Since there's many tags for projects, there should be a way to navigate, search, and manage these projects.
For the dashboard tabs, we'd need more specific requirements to detail out the metrics and visualizations that would be displayed on each tab.
Since there's a lot of master data, we should also provide a CLI that operate the data seeder in one shot instead CLI seeder for individual master data

Low Level Design

Dashboard

User Workflow

1. Login

2. Project Creation

3. Multiple Crops Selection (Displayed if "Multiple Crops" is toggled ON)

4. Segment Creation & Comparison

5. Data Output Dashboard

6. Admin Page

Data Layer

Data Input

Data Models

Logic Layer

Project Creation

Segment Logic

Visualization Logic

Dashboard Logic

Presentation Layer

Project Creation Page

Multiple Crops Page

Comparison Page

Data Output Dashboard

Master Data

1. Crop Categories

2. Crops (Focus)

3. Countries

4. Currencies

5. Area Size Units

6. Volume Measurement Units

7. Cost of Production Measurement Units

8. Reporting Periods

9. Secondary & Tertiary Income Sources

10. Years for Project Timeline

11. Questions (Expandable Inputs)

12. Living Income Benchmark

Database Schema

User Table

User Project Access

Crop Category Table

Crop Table

Tag Table

Project Table

Project Tags Table

User Tags Table

Project Crop Table

Segment Table

Question Table

Crop Category Question

Visualization Table

Segment Answer Table

Living Income Benchmark Table

Reference Data Table

API Endpoints

1. Users Endpoint

1.1. User Login

1.2. Logged-in User Info

1.3. List of Users

1.4. User Detail

1.4. User Registration

1.6. Register Password

1.5. Edit User

1.6. Delete User

2. Tags Endpoints

2.1. List of Tags

2.2. Create Tag

2.3. Tag Detail

2.4. Edit Tag

3. Project Endpoints

3.1. List Projects

3.2. Project Detail

3.3. Add Project

3.4. Edit Project

4. Segment Endpoint

4.1. Add Segments (single / multiple)

5. Question Endpoint

5.1. Get Questions By Crop ID

6. Segment Value Endpoint

6.1. Add / Update Segment Value

7. Visualisation Endpoint

6.1. Add / Update Segment Value