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