Low Level Design

Dashboard

User Workflow

1. Login

2. Case Creation

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

4. Segment Creation & Comparison

5. Data Output Dashboard

6. Admin Page

Roles and User Access

Roles

Super Admin
Business Unit Administrator
Regular User (Users with Business Unit)
External User

Possibilities

Example Scenario:

Glossary:

User Role
All Cases
BU
BU Role
Tag
Description
Super Admin
true
-
null
-

Admin
true
BR
admin
-
  • Rice in Indonesia:
    • edit
    • assign
  • Coffee in Vietnam:
    • view
    • edit (EPC, WRP)
  • Rice in Bangladesh: view
Admin
false
BR
admin
rice
  • Rice in Indonesia:
    • edit
    • assign
  • Coffee in Vietnam:
    • no access
    • edit (EPC, WRP)
  • Rice in Bangladesh:
    • view
    • edit (EPC, WRP)
Admin
false
BR
admin
coffee
  • Rice in Indonesia:
    • edit
    • assign
  • Coffee in Vietnam:
    • view
    • edit (EPC, WRP)
  • Rice in Bangladesh:
    • no access
    • edit (EPC, WRP)
Admin
false
BR
admin
-
  • Rice in Indonesia:
    • edit
    • assign
  • Coffee in Vietnam:
    • no access
    • edit (EPC, WRP)
  • Rice in Bangladesh:
    • no access
    • edit (EPC, WRP)





 

Data Layer

Data Input

Data Models

Logic Layer

Case Creation

Segment Logic

Visualization Logic

Dashboard Logic

Presentation Layer

Case Creation Page

Multiple Commodities Page

Comparison Page

Data Output Dashboard

Master Data

1. Commodity Categories

List of Commodity Categories to define the list of questions.

2. Commodities (Focus)

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

3. Countries

List of countries that users can select for their cases. 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 cases. 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 cases.

9. Secondary & Tertiary Income Sources

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

10. Years for Case Timeline

A list of years that users can select from for their case 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, Unique
full name
VARCHAR
Full name of user
Not Null
password
TEXT
User Password
Nullable
role
ENUM
Whether user is admin or business unit admin, etc.
Not Null, ENUM (super admin, admin, user)
all_cases
BOOLEAN
Whether all cases or only create
Not Null, Default False
is_active
BOOLEAN
Whether user is active
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

Business Unit

Column Name Data Type Description Constraints
id INT Unique identifier. Primary Key, Auto-Increment
name VARCHAR Name of Business Unit
Not Null, Unique

User Business Unit

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)
business_unit_id INT Identifier for the business unit
Foreign Key (References business_unit_id)
role
ENUM
Whether admin or member
Not null, ENUM (admin, member)

Note:

User Case 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)
case_id INT Identifier for the case
Foreign Key (References case_id)
permission
ENUM
Whether edit or view
Not null, ENUM (edit,view)

Commodity Category Table

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

Commodity Table

Column Name Data Type Description Constraints
id INT Unique identifier for each commodity. Primary Key, Auto-Increment
commodity_category_id
INT
Identifier for the commodity category. Foreign Key (References commodity_category_id)
commodity_name VARCHAR Name of the commodity (e.g., "Coffee"). Not Null, Unique

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, 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

Case Table

Column Name Data Type Description Constraints
id INT Unique identifier for each case. Primary Key, Auto-Increment
case_name VARCHAR Name of the case (e.g., "Country A Coffee Production Comparison"). Not Null
date DATE Date associated with the case. Not Null
year INT Year in the case timeline (from the dropdown). Not Null
country INT Country of focus for the case. Foreign Key (References Country Table)
focus_commodity_id INT Identifier for the commodity (focus). This could reference a master data table for commodities. Foreign Key (References Commodity Master Table)
currency VARCHAR Chosen currency for the case. 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_commodities BOOLEAN Indicates if multiple commodities option is on/off. Not Null, Default False
logo VARCHAR Stores the uploaded logo for the case. Nullable
private
BOOLEAN
Indicates if the case is private or public
Not Null, Default False
created_at DATETIME Timestamp when the case was created. Not Null, Default Current Timestamp
updated_at DATETIME Timestamp when the case was last updated. Nullable
created_by VARCHAR User who created the case. This can be an email or user ID, based on your user management. Not Null

Case Tags Table

Column Name
Data Type
Description
Constraint
id
INT
Unique Identifier for each Diversified Commodity
Primary Key, Auto-Increment
case_id
INT
Identifier for the case.
Foreign Key (References Case Table)
tag_id
INT
Identifier for the tag. Foreign Key (References Tag Table)

User Tags Table

Column Name
Data Type
Description
Constraint
id
INT
Unique Identifier for each Diversified Commodity
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)

Case Commodity Table

Column Name
Data Type
Description
Constraint
id
INT
Unique Identifier for each Case Commodity
Primary Key, Auto-Increment
case_id
INT
Identifier for the case to which the case commodity belongs. Foreign Key (References Case Table)
commodity_id
INT
Identifier for the commodity. This could reference a master data table for commodities. Foreign Key (References Commodity Table)
focus_commodity
BOOLEAN
Whether it's focus commodity
True, Default: False
breakdown
BOOLEAN
To define the question list
Not Null

Segment Table

Column Name Data Type Description Constraints
id INT Unique identifier for each segment. Primary Key, Auto-Increment
case_id INT Identifier for the case to which the segment belongs. Foreign Key (References Case 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

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
case_commodity_id
INT
Foreign Key
Nullable
created_by
INT
Identifier for the user to which the question belongs. Foreign Key (References to user_id), Nullable

Case commodity Id only used for custom added specific 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 visualization

Commodity Category Question

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

Visualization Table

Column Name Data Type Description Constraints
id INT Unique identifier for each answer. Primary Key, Auto-Increment
case_id
INT
Identifier for the case to which the answer belongs. Foreign Key (References Case Table)
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

Segment Answer Table

Column Name Data Type Description Constraints
id INT Unique identifier for each answer. Primary Key, Auto-Increment
case_commodity_id
INT
Unique Identifier for each commodity case commodity
Foreign Key (References Case Case Commodity 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

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

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)
commodity_id
INT
Identifier for the commodity. Foreign Key (References commodity_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

API Endpoints

Endpoint Prefix: /api

1. Users Endpoint

1.1. User Login

Example Response

{
  "access_token": "eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJlbWFpbCI6ImRlZGVuQGFrdm8ub3JnIiwiZXhwIjoxNjk2MzcwMTI0fQ.4KR4ELzTpsvlBG3fJkA_MLJS9RMMVoY8X-eTeN9WXwk",
  "token_type": "bearer",
  "user": {
    "id": 1,
    "fullname": "Deden Bangkit",
    "email": "deden@akvo.org",
    "active": true,
    "role": "super admin, admin, editor, viewer, user",
    "business_unit_detail": [{
      "id":1,
      "name": "Coffee Producers",
      "role" "admin / member"
    }],
    "organisation_detail": {
      "id": 1,
      "name": "Akvo"
    }
    "tags_count": 2,
    "cases_count": 3
  }
}

1.2. Logged-in User Info

Example Response

{
  "id": 1,
  "fullname": "Deden Bangkit",
  "email": "deden@akvo.org",
  "active": true,
  "role": "super admin / admin / editor / viewer / user",
  "business_unit_detail": [{
      "id":1,
      "name": "Coffee Producers",
      "role": "admin / member"
  }],
  "organisation_detail": {
    "id": 1,
    "name": "Akvo"
  },
  "tags_count": 2,
  "cases_count": 3
}

1.3. List of Users

Example Response

{
  "current": 1,
  "data": [
    {
      "id": 0,
      "organisation": 0,
      "email": "string",
      "fullname": "string",
      "active": true,
      "role": "super admin / admin / editor / viewer / user",
      "tags_count": 2,
      "cases_count": 3
    }
  ],
  "total": 1,
  "total_page": 1
}

1.4. User Detail

Example Response

{
  "id": 1,
  "fullname": "Deden Bangkit",
  "email": "deden@akvo.org",
  "active": true,
  "role": "super admin / admin / editor / viewer / user",
  "organisation_detail": {
    "id": 1,
    "name": "Akvo"
  },
  "tag_ids": [1],
  "cases_ids": [1],
  "business_unit_detail": [{
    "id":1,
    "name": "Coffee Producers",
    "role" "admin / member"
  }],
}

1.4. User Registration

1.6. Register Password

1.5. Edit User

1.6. Delete User

2. Tags Endpoints

2.1. List of Tags

Example Response

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

2.2. Create Tag

2.3. Tag Detail

Example Response

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

2.4. Edit Tag

3. Case Endpoints

3.1. List Cases

Example Response

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

3.2. Case Detail

Example Response

{
  "id": 1,
  "case_name": "Country A Coffee Production Comparison",
  "date": "2023-10-03",
  "year": 2023,
  "country": 1,
  "focus_commodity_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_commodities": 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
  }],
  "case_commodities": [{
    "id": 1,
    "commodity_id": 1,
    "breakdown": true
  },{
    "id": 2,
    "commodity_id": 2,
    "breakdown": true
  }]
}

Notes:

3.3. Add Case

Notes:

3.4. Edit Case

Notes:

4. Segment Endpoint

4.1. Add Segments (single / multiple)

5. Question Endpoint

5.1. Get Questions By Commodity ID

Example Response

[{
  "commodity_id": 1,
  "commodity_name": "Rice",
  "focus_commodity": 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 Commodity / 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
  }]
},{
  "commodity_id": 2,
  "commodity_name": "Chicken"
  "focus_commodity": 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:

6. Segment Value Endpoint

6.1. Add / Update Segment Value

7. Visualization Endpoint

7.1. Get Visualization Config

Example Response

7.2. Add / Update Visualization Config

Error Handling & Validation

Additional Considerations


Revision #80
Created 19 September 2023 05:35:03 by Deden Bangkit
Updated 2 July 2024 04:11:58 by Galih Pratama