# Income Drive Calculator

# Project Sheet

<table border="1" id="bkmrk-name-name-of-the-pro" style="border-collapse: collapse; width: 100%; height: 528.562px;"><colgroup><col style="width: 50%;"></col><col style="width: 50%;"></col></colgroup><tbody><tr style="height: 29.7969px;"><td style="height: 29.7969px;">**Name**  
</td><td style="height: 29.7969px;">IDC (Income Driver Calculator)  
</td></tr><tr style="height: 46.5938px;"><td style="height: 46.5938px;">**Project Scope**  
</td><td style="height: 46.5938px;">A short description of the scope of the project from a sector and technical perspective.</td></tr><tr style="height: 29.7969px;"><td style="height: 29.7969px;">**Contract Link**  
</td><td style="height: 29.7969px;">Link to the signed PDF document (PandaDoc)</td></tr><tr style="height: 46.5938px;"><td style="height: 46.5938px;">**Project Dashboard Link**</td><td style="height: 46.5938px;">https://docs.google.com/spreadsheets/d/1wPSpzXuceDFZ4kdlrJoo\_J8n2PPRRNhAaTpW5mtsnPw/edit#gid=387463320</td></tr><tr style="height: 29.7969px;"><td style="height: 29.7969px;">**Start Date**  
</td><td style="height: 29.7969px;">2023-07</td></tr><tr style="height: 29.7969px;"><td style="height: 29.7969px;">**End Date**  
</td><td style="height: 29.7969px;">2024-04</td></tr><tr style="height: 35.3906px;"><td style="height: 35.3906px;">**Repository Link**  
</td><td style="height: 35.3906px;">[https://github.com/akvo/IDH-IDC](https://github.com/akvo/IDH-IDC)</td></tr><tr style="height: 204.406px;"><td style="height: 204.406px;">**Tech Stack**</td><td style="height: 204.406px;">List of technologies used to execute the technical scope of the project:

- Front-end: Javascript with React Framework
- Back-end: Python with FastAPI framework
- BD
- Testing
- CI: Github Actions
- Hosting: GKE
- Database: PostgreSQL, Cloud-SQL
- Storage: Cloud Storage Buckets

</td></tr><tr style="height: 46.5938px;"><td style="height: 46.5938px;">**Asana Link**  
</td><td style="height: 46.5938px;">[https://app.asana.com/0/1205513346619877/1207511953547471](https://app.asana.com/0/1205513346619877/1207511953547471)</td></tr><tr style="height: 29.7969px;"><td style="height: 29.7969px;">**Slack Channel Link**</td><td style="height: 29.7969px;">[https://akvo.slack.com/archives/C05SPJ80HJ9](https://akvo.slack.com/archives/C05SPJ80HJ9)</td></tr></tbody></table>

# 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. Case Creation

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

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

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

#### 4. Segment Creation &amp; Comparison

- **Segment Creation**: User begins creating segments (up to a maximum of 5).
- **Target Fields:** Income Target Input 
    - In this section, user can set Income Target based on region or set manually.
    - User can set Income Target manually by turn on the toggle "Set income target yourself?". 
        - This will hide the per Region Income Target fields,
        - Then show up the input field for manual Income Target
    - When set Income Target based on a region, 
        - User can change the number of adult and children in household.
        - After that system will calculated the Income Target automatically.
- **Segment Fields**:
    
    
    - Each segment allows input for both current and feasible levels of eg.: 
        - **Land/Area**: 
            - **Expand Option**: If users click "Expand" beside the "Area" field: 
                - They can input details such as: 
                    - Total land owned.
                    - <s>Land allocated to the primary commodity.</s>
                    - <s>Land allocated to other purposes.</s>
        - **Volume**: 
            - **Expand Option**: If users click "Expand" beside the "Yield" field: 
                - They can input details such as: 
                    - Total Yield per weight/area unit.
                    - Total Loss per weight/area unit.
        - **Price**: 
            - **Expand Option**: If users click "Expand" beside the "Price" field: 
                - They can input details such as: 
                    - Farmgate price per currency/weight unit.
                    - Price premium per currency/weight unit.
        - **Cost of Production**: 
            - **Expand Option**: If users click "Expand" beside the "Cost of Production" field: 
                - They can input details related to various cost components per currency/area unit.
        - **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: 
                    - Current &amp; Feasible value of Primary Commodity.
                    - Current &amp; Feasible value of Diversified Income.
                    - Current &amp; Feasible value of Cost of Production with negative Y-Axis value.
                - **Y-Axis:** Income (currency).
                - **X-Axis:** Segments.
            - **Income Gap**: 
                - **Stack Bar** chart: 
                    - Current &amp; Feasible value of Household Income.
                    - Current &amp; Feasible value of Income Gap.
                - **Diamond** mark of Income Target for each segment.
                - **Y-Axis**: Income (currency).
                - **X-Axis**: Segments.
            - **Biggest Impact on Income**: 
                - **Segment Tab** selection.
                - **Bar** chart: 
                    - Change of income driver value in %.
                    - Percentage change in income with driver at feasible level, while all other drivers stay the same.
                    - Percentage change in income when this driver moves from current to feasible level while all other drivers at feasible level.
                - **Y-Axis**: Change (%).
                - **X-Axis**: Drivers of selected Segment.
            - **Breakdown of Drivers**: 
                - **Segment Tab** selection.
                - **Dropdown** of Drivers (visual will generated based on selected driver) 
                    - ***question with null parent id*** (Income Driver)
                - **Stack Bar** chart: 
                    - Each selected dropdown will generate different stack.
                    - Stack shows break down of selected income driver / answer of child value (sub-components).
                - **Y-Axis**: Each driver unit size
                - **X-Axis**: Segment
            - **Monetary Impact to Income**: 
                - **Segment Tab** selection.
                - **Waterfal** chart: 
                    - illustrates how adjustments in income drivers influence the transition from the current income level to a feasible income level.
                - **Y-Axis**: Income (currency)
                - **X-Axis**: Drivers of selected segment
            - **Commodity Income Comparison**:  
                
                - **Segment Tab** selection.
                - **Bar** chart (Current Revenue and Feasible Revenue)
                - **Y-Axis**: Income (currency)
                - **X-Axis**: Commodities include Diversified Income based on selected segment
    - **Sensitivity Analysis**: Allows users to understand how different combinations of income drivers affect income levels. 
        - **Input**: 
            - **Select Segment:** drop-down of the segments in case
            - <s>**Select Commodity:** drop-down for The Focus commodity / Secondary / Tertiary commodity (eliminate commodity where it's not break down)</s>
            - **Breakdown of Drivers** table: This table will show the breakdown of drivers for selected segment
            - **Select Binning Driver:** drop-down for ***question with null parent id*** (Income Driver) 
                - **Number of Bins:** Input Number
            - **Select X-Axis Driver:** drop-down for ***question with null parent id*** (Income Driver) 
                - **Min:** Input Number
                - **Max:** Input Number
            - **Select Y-Axis Driver:** drop-down for ***question with null parent id*** (Income Driver) 
                - **Min:** Input Number
                - **Max:** Input Number
        - **Adjust Income Target** feature 
            - **Select Option** to choose whether you would like to express the changes in current values using percentages or absolute values.
            - **%Change** Input: when choose percentage.
            - **Adjusted Target** Input: when choose absolute.
        - **Charts**: 
            - **Zone between current and feasible:** Line with intersection bar zone
            - **Income Heat-map:** Heat-map Cartesian
    - **Scenario Modeling**: Enables users to visualize potential scenarios and their impact. 
        - **Input:**
            - **Choose Approach**: Select dropdown (percentage, absolute)
            - **Scenario Title**: Input Text
            - **Scenario Description**: Input Textarea
            - **Segment Tabs:** tab section of the segments in case
            - <s>**Select Commodity:** drop-down for The Focus commodity / Secondary / Tertiary name</s>
            - **Input for Each grand parent questions** (***question with null parent id / Income Drivers***), e.g: 
                - Diversified Income
                - Volume / Yield
                - Price
                - Cost of Production
                - Land Area
        - **Charts**: 
            - **Scenario Chart:**
                - **Stack Bar** Chart: 
                    - Current total household income.
                    - Additional income when income drivers are changed.
                    - Gap between current and additional income
                - **Y-Axis:** Income (currency)
                - **X-Axis:** Each Segments
            - **Income Gap across Scenario Chart**
                - **Select Scenario** dropdown: select scenario - segment across scenario
                - Same chart as scenario chart but user can select to show a chart from different scenario
        - **Scenario Outcomes**
            - Visualize as a table.
            - User can compare specific outcomes per segment to understand in which scenario the farmers in that segment reach the income target, how this is established, and how it compares to the current scenario. <div class="ant-col ant-col-24 css-ud0ov8"><div class="ant-card ant-card-bordered info-card-wrapper css-ud0ov8"></div></div>
        - **New Scenario Button:** To create new scenario, maximum 3 scenarios.
        - **Export**: each chart has export/download button.

#### 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 Case Tags / Individual Cases
        - 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

### Roles and User Access

#### Roles

##### Super Admin

- Database Role Enum: **super\_admin**
- Privileges: 
    - Able to view **all the users** in the platform
    - Able to view and edit all of **the existing cases**
    - Can create new **case** and add a User as **Viewer** or **Editor** of the case, but in most cases most likely not the case
    - Can invite new **Business Unit Administrator** or modify existing user as a **Business Unit Administrator**
    - Can invite new **Super Admin** or modify existing user as **Super Admin**
    - Can add or modify **Tags**
    - Can do everything the Business Unit Administrators can do
- Workflow: 
    - **Step 1: Log In**
        - Super Admin logs into the admin panel using their credentials.
    - **Step 2: Navigate to User Management**
        - Upon logging in, the Super Admin is presented with a dashboard.
        - From the dashboard, the Super Admin should navigate to the "User Management" section or a similar area where user-related actions can be performed.
    - **Step 3: Select "Add User"**
        - In the User Management section, there should be an option to "Add User" or a similar action.
        - Click on "Add User" to initiate the process of adding a new user.
    - **Step 4: Enter User Details**
        - A form or interface appears where the Super Admin can enter the new user's details. These details may include: 
            - Username
            - Email address
            - User's role (e.g., Business Unit Administrator, Business Unit Editor, Business Unit Viewer, etc.)
            - Business Unit (if applicable)
    - **Step 5: Set Permissions**
        - Depending on the system's design, the Super Admin may have the option to set specific permissions for the new user, such as granting access to certain cases or functionalities.
    - **Step 6: Save User Details**
        - After entering all the necessary information, the Super Admin clicks on the "Save" or "Create User" button to add the new user to the system.
    - **Step 7: Confirmation**
        - The system should provide a confirmation message indicating that the new user has been successfully added.
    - **Step 8: Notify User (Optional)**
        - This action triggers an email notification to the new user, inviting them to set their password and activate their account.

##### Business Unit Administrator

- Database Role Enum: **admin**
- Privileges 
    - Can have multiple **Business Unit**
    - Able to view **all the users** with **the same Business Unit**
    - Able to view and edit all of **the existing cases** in **the same Business Unit** even if it's **private cases**
    - Can create new **case** and add a User as **Viewer** or **Editor** of the case
    - Can invite new **User** or approve new registered user with **the same Business Unit**
    - Can modify role of the User in the same **Business Unit** as case **Editor** or case **Viewer**
    - Can add user to an existing case as editor if:  
        
        - The user current role is not **Editor**
        - The user is not the owner of the case
- Workflow 
    - **Step 1: User Registration**
        - A new user registers for an account on the platform, providing their details, including username, email address, and Business Unit during the registration process.
    - **Step 2: Review Pending Users**
        - After registration, the user's account is created with a "Pending" status, and their details are added to a list of pending users.
    - **Step 3: Business Unit Manager Logs In**
        - The Business Unit Manager logs into the admin panel using their credentials.
    - **Step 4: Navigate to User Approval**
        - From the dashboard or admin panel, the Business Unit Manager navigates to the "User Approval" or "Pending Users" section.
    - **Step 5: View Pending Users**
        - In the "Pending Users" section, the Business Unit Manager can see a list of users who have registered with the same Business Unit and are awaiting approval.
    - **Step 6: Review User Details and Modify Role**
        - The Business Unit Manager selects the user they wish to approve and reviews their registration details. They also have the option to modify the user's role by selecting either "Editor" or "Viewer" from a drop-down menu.
        - If the role is Editor, show radio button: 
            - All case in **The Business Unit of the User**
            - Create / Edit case
        - The Business Unit Manager can also change the Business Unit of registered user *only* with their Business Unit
    - **Step 7: Approve User**
        - If the Business Unit Manager is satisfied with the user's details and eligibility, they click on an "Approve" or "Activate" button associated with the user's profile.
    - **Step 8: User Notification**
        - Upon approval, the system sends an email notification to the newly approved user, informing them that their account has been activated and is now accessible.
    - **Step 9: User Marked as Active**
        - Back in the admin panel, the system automatically updates the user's status from "Pending" to "Active" in the user list, indicating that they have been approved and can now access the platform.

##### Regular User (Users with Business Unit)  


- Database Role Enum: **user**
- Privileges 
    - <span style="font-size: 11pt; font-family: Arial,sans-serif; color: #000000; background-color: transparent; font-weight: 400; font-style: normal; font-variant: normal; text-decoration: none; vertical-align: baseline; white-space: pre-wrap;">They can view all cases , except private</span>
    - <span style="font-size: 11pt; font-family: Arial,sans-serif; color: #000000; background-color: transparent; font-weight: 400; font-style: normal; font-variant: normal; text-decoration: none; vertical-align: baseline; white-space: pre-wrap;">Can create new cases</span>
    - <span style="font-size: 11pt; font-family: Arial,sans-serif; color: #000000; background-color: transparent; font-weight: 400; font-style: normal; font-variant: normal; text-decoration: none; vertical-align: baseline; white-space: pre-wrap;">They can always edit / view cases if they explicitly added to case (**User Case Access Table**)</span>
- <span style="font-size: 11pt; font-family: Arial,sans-serif; color: #000000; background-color: transparent; font-weight: 400; font-style: normal; font-variant: normal; text-decoration: none; vertical-align: baseline; white-space: pre-wrap;">Workflow:</span>
    - **Registration / Invitation:**
        - Regular User sign up with business unit. Then BU admin get notified to approve the user.
        - Business Unit add the regular user and assigned business unit (if they have multiple business unit), notifying the relevant manager(s) via email
    - **View**: 
        - All cases except private cases
        - Any private cases if explicitly added to the case as viewer
        - Only case owner can explicitly add as viewer to their cases
    - **Edit**: 
        - For cases where they are owner of the case
        - Any cases if explicitly added as editor to the case
        - Only case owner can explicitly add as editor to their cases

##### External User

- Database Role Enum: **user**
- Privileges 
    - Able to see specified case
- Workflow: 
    - **Step 1: Registration:**
        - Sign up to Register Page.
        
        
        - **Super Admin** review the request.
    - **Step 3: Access to Case:**
        - **Case owner** adds the external user as a viewer or editor to the specified case. 
            - Suggestion when they start typing a user (full text search: name / email)
            - The invited user gets a notification

#### Possibilities

Example Scenario:

- Case 1: 
    - Case Name**: Rice in Indonesia**
    - Tag: **Rice**
    - Business Unit Name: **Bali Rice**
- Case 2: 
    - Case Name: **Coffee in Vietnam**
    - Tag: **Coffee**
    - Business Unit Name: **Nes-Kopi**
- Case 3: 
    - Case Name: **Rice in Bangladesh**
    - Tag: **Rice**
    
    
    - Business Unit Name: **Bangla Rice Trade**

Glossary:

- WRP (Within the realm of possibility): something may not be true in the reality, but it could potentially happen within the system or context
- EPC (Edit permission in case)

<table border="1" id="bkmrk-user-role-all-cases-" style="border-collapse: collapse; width: 100%; height: 811px;"><colgroup><col style="width: 14.9567%;"></col><col style="width: 9.76514%;"></col><col style="width: 6.18047%;"></col><col style="width: 11.6193%;"></col><col style="width: 10.136%;"></col><col style="width: 47.3424%;"></col></colgroup><tbody><tr style="height: 29.8px;"><td style="height: 29.8px;">User Role  
</td><td style="height: 29.8px;">All Cases  
</td><td style="height: 29.8px;">BU  
</td><td style="height: 29.8px;">BU Role  
</td><td style="height: 29.8px;">Tag  
</td><td style="height: 29.8px;">Description  
</td></tr><tr style="height: 29.8px;"><td style="height: 29.8px;">Super Admin  
</td><td style="height: 29.8px;">true  
</td><td style="height: 29.8px;">-  
</td><td style="height: 29.8px;">null  
</td><td style="height: 29.8px;">-  
</td><td style="height: 29.8px;">  
</td></tr><tr style="height: 153.05px;"><td style="height: 153.05px;">Admin  
</td><td style="height: 153.05px;">true  
</td><td style="height: 153.05px;">BR  
</td><td style="height: 153.05px;">admin  
</td><td style="height: 153.05px;">-  
</td><td style="height: 153.05px;">- Rice in Indonesia: 
    - edit
    - assign
- Coffee in Vietnam: 
    - view
    - edit (EPC, WRP)
- Rice in Bangladesh: view

</td></tr><tr style="height: 187.65px;"><td style="height: 187.65px;">Admin  
</td><td style="height: 187.65px;">false  
</td><td style="height: 187.65px;">BR  
</td><td style="height: 187.65px;">admin  
</td><td style="height: 187.65px;">rice  
</td><td style="height: 187.65px;">- Rice in Indonesia: 
    - edit
    - assign
- Coffee in Vietnam: 
    - no access
    - edit (EPC, WRP)
- Rice in Bangladesh:  
    
    - view
    - edit (EPC, WRP)

</td></tr><tr style="height: 187.65px;"><td style="height: 187.65px;">Admin  
</td><td style="height: 187.65px;">false  
</td><td style="height: 187.65px;">BR  
</td><td style="height: 187.65px;">admin  
</td><td style="height: 187.65px;">coffee  
</td><td style="height: 187.65px;">- Rice in Indonesia: 
    - edit
    - assign
- Coffee in Vietnam: 
    - view
    - edit (EPC, WRP)
- Rice in Bangladesh:  
    
    - no access
    - edit (EPC, WRP)

</td></tr><tr style="height: 187.65px;"><td style="height: 187.65px;">Admin  
</td><td style="height: 187.65px;">false  
</td><td style="height: 187.65px;">BR  
</td><td style="height: 187.65px;">admin  
</td><td style="height: 187.65px;">-  
</td><td style="height: 187.65px;">- Rice in Indonesia: 
    - edit
    - assign
- Coffee in Vietnam:  
    
    - no access
    - edit (EPC, WRP)
- Rice in Bangladesh:  
    
    - no access
    - edit (EPC, WRP)

</td></tr><tr style="height: 35.4px;"><td style="height: 35.4px;">  
</td><td style="height: 35.4px;">  
</td><td style="height: 35.4px;">  
</td><td style="height: 35.4px;">  
</td><td style="height: 35.4px;">  
</td><td style="height: 35.4px;"></td></tr></tbody></table>

### Data Layer

#### Data Input

- **Cases**: Store case-related data like name, date, country, selected focus, currency, units, etc.
- **Master Data**: Predefined drop-down values for year, country, commodities (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

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

### Logic Layer

#### Case Creation

- Logic to create, update, and delete cases.
- Logic for case permission.
- Logic to validate case attributes, especially drop-down values against master data.
- Logic to handle logo uploads.

#### Segment Logic

- Logic to create up to 5 segments for a case.
- 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 case and segment data.

### Presentation Layer

#### Case Creation Page

- Form to input case details.
- Drop-downs populated from master data.
- Toggle switches for options like segmentation, living income study, multiple commodities, etc.
- Logo upload functionality.
- Add New Question 
    - Only applies in Level 2
    - Always has Total (ID: 1 from master data) as Parent
    - Linked to a Specific Case

#### Multiple Commodities 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. 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 &amp; 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](https://docs.google.com/spreadsheets/d/1u_Kb-Qu_2HopJSkNtOuUFNWUrx-jqw8OesCZmKy0ON8/edit#gid=0)**

<p class="callout info">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.</p>

#### 12. Living Income Benchmark

List of Living Income Benchmark

## Database Schema

### User Table

<table border="1" id="bkmrk-column-name-data-typ" style="border-collapse: collapse; border-style: solid; width: 101.605%;"><thead><tr><th style="width: 21.6316%;">Column Name</th><th style="width: 18.9122%;">Data Type</th><th style="width: 33.131%;">Description</th><th style="width: 26.3251%;">Constraints</th></tr></thead><tbody><tr><td style="width: 21.6316%;">id</td><td style="width: 18.9122%;">INT</td><td style="width: 33.131%;">Unique identifier.</td><td style="width: 26.3251%;">Primary Key, Auto-Increment</td></tr><tr><td style="width: 21.6316%;">organization\_id  
</td><td style="width: 18.9122%;">INT  
</td><td style="width: 33.131%;">Identifier for Organisation  
</td><td style="width: 26.3251%;">Foreign Key (References organisation\_id)  
</td></tr><tr><td style="width: 21.6316%;">email</td><td style="width: 18.9122%;">VARCHAR</td><td style="width: 33.131%;">Email of user  
</td><td style="width: 26.3251%;">Not Null, Unique  
</td></tr><tr><td style="width: 21.6316%;">full name  
</td><td style="width: 18.9122%;">VARCHAR  
</td><td style="width: 33.131%;">Full name of user  
</td><td style="width: 26.3251%;">Not Null  
</td></tr><tr><td style="width: 21.6316%;">password  
</td><td style="width: 18.9122%;">TEXT  
</td><td style="width: 33.131%;">User Password  
</td><td style="width: 26.3251%;">Nullable  
</td></tr><tr><td style="width: 21.6316%;">role  
</td><td style="width: 18.9122%;">ENUM  
</td><td style="width: 33.131%;">Whether user is admin or business unit admin, etc.  
</td><td style="width: 26.3251%;">Not Null, ENUM (super admin, admin, user)  
</td></tr><tr><td style="width: 21.6316%;">all\_cases  
</td><td style="width: 18.9122%;">BOOLEAN  
</td><td style="width: 33.131%;">Whether all cases or only create  
</td><td style="width: 26.3251%;">Not Null, Default False  
</td></tr><tr><td style="width: 21.6316%;">is\_active  
</td><td style="width: 18.9122%;">BOOLEAN  
</td><td style="width: 33.131%;">Whether user is active  
</td><td style="width: 26.3251%;">Not Null, Default False  
</td></tr><tr><td style="width: 21.6316%;">invitation\_id  
</td><td style="width: 18.9122%;">VARCHAR  
</td><td style="width: 33.131%;">Invitation ID for new user  
</td><td style="width: 26.3251%;">Nullable, Default (Random String)  
</td></tr><tr><td style="width: 21.6316%;">created\_at  
</td><td style="width: 18.9122%;">DATETIME  
</td><td style="width: 33.131%;">Timestamp when the user was last created.</td><td style="width: 26.3251%;">Not Null, Default Current Timestamp</td></tr><tr><td style="width: 21.6316%;">updated\_at  
</td><td style="width: 18.9122%;">DATETIME</td><td style="width: 33.131%;">Timestamp when the user was last updated.</td><td style="width: 26.3251%;">Nullable</td></tr></tbody></table>

### Business Unit

<table border="1" id="bkmrk-column-name-data-typ-1" style="border-collapse: collapse; border-style: solid; width: 101.605%;"><thead><tr><th style="width: 21.6316%;">Column Name</th><th style="width: 18.9122%;">Data Type</th><th style="width: 33.131%;">Description</th><th style="width: 26.3251%;">Constraints</th></tr></thead><tbody><tr><td style="width: 21.6316%;">id</td><td style="width: 18.9122%;">INT</td><td style="width: 33.131%;">Unique identifier.</td><td style="width: 26.3251%;">Primary Key, Auto-Increment</td></tr><tr><td style="width: 21.6316%;">name</td><td style="width: 18.9122%;">VARCHAR</td><td style="width: 33.131%;">Name of Business Unit  
</td><td style="width: 26.3251%;">Not Null, Unique  
</td></tr></tbody></table>

### User Business Unit

<table border="1" id="bkmrk-column-name-data-typ-2" style="border-collapse: collapse; border-style: solid; width: 100%;"><thead><tr><th style="width: 21.6316%;">Column Name</th><th style="width: 18.9122%;">Data Type</th><th style="width: 33.1273%;">Description</th><th style="width: 26.3288%;">Constraints</th></tr></thead><tbody><tr><td style="width: 21.6316%;">id</td><td style="width: 18.9122%;">INT</td><td style="width: 33.1273%;">Unique identifier.</td><td style="width: 26.3288%;">Primary Key, Auto-Increment</td></tr><tr><td style="width: 21.6316%;">user\_id  
</td><td style="width: 18.9122%;">INT  
</td><td style="width: 33.1273%;">Identifier for the user.</td><td style="width: 26.3288%;">Foreign Key (References user\_id)</td></tr><tr><td style="width: 21.6316%;">business\_unit\_id</td><td style="width: 18.9122%;">INT</td><td style="width: 33.1273%;">Identifier for the business unit  
</td><td style="width: 26.3288%;">Foreign Key (References business\_unit\_id)  
</td></tr><tr><td style="width: 21.6316%;">role  
</td><td style="width: 18.9122%;">ENUM  
</td><td style="width: 33.1273%;">Whether admin or member  
</td><td style="width: 26.3288%;">Not null, ENUM (admin, member)  
</td></tr></tbody></table>

Note:

- unique between **user\_id** and **business\_unit\_id**

### User Case Access

<table border="1" id="bkmrk-column-name-data-typ-3" style="border-collapse: collapse; border-style: solid; width: 100%;"><thead><tr><th style="width: 21.6316%;">Column Name</th><th style="width: 18.9122%;">Data Type</th><th style="width: 33.1273%;">Description</th><th style="width: 26.3288%;">Constraints</th></tr></thead><tbody><tr><td style="width: 21.6316%;">id</td><td style="width: 18.9122%;">INT</td><td style="width: 33.1273%;">Unique identifier.</td><td style="width: 26.3288%;">Primary Key, Auto-Increment</td></tr><tr><td style="width: 21.6316%;">user\_id  
</td><td style="width: 18.9122%;">INT  
</td><td style="width: 33.1273%;">Identifier for the user.</td><td style="width: 26.3288%;">Foreign Key (References user\_id)</td></tr><tr><td style="width: 21.6316%;">case\_id</td><td style="width: 18.9122%;">INT</td><td style="width: 33.1273%;">Identifier for the case  
</td><td style="width: 26.3288%;">Foreign Key (References case\_id)  
</td></tr><tr><td style="width: 21.6316%;">permission  
</td><td style="width: 18.9122%;">ENUM  
</td><td style="width: 33.1273%;">Whether edit or view  
</td><td style="width: 26.3288%;">Not null, ENUM (edit,view)  
</td></tr></tbody></table>

- **Unique Constraint:** user\_id, case\_id
- **Notes:**
    - Many to Many (Users &amp; Cases)

### Commodity Category Table

<table border="1" id="bkmrk-column-name-data-typ-4" style="border-collapse: collapse; border-style: solid; width: 101.605%;"><thead><tr><th style="width: 21.6316%;">Column Name</th><th style="width: 18.9122%;">Data Type</th><th style="width: 33.131%;">Description</th><th style="width: 26.3251%;">Constraints</th></tr></thead><tbody><tr><td style="width: 21.6316%;">id</td><td style="width: 18.9122%;">INT</td><td style="width: 33.131%;">Unique identifier for each commodity.</td><td style="width: 26.3251%;">Primary Key, Auto-Increment</td></tr><tr><td style="width: 21.6316%;">commodity\_category\_name</td><td style="width: 18.9122%;">VARCHAR</td><td style="width: 33.131%;">Name of the commodity (e.g., "Land Commodity, Aquaculture, Livestock").</td><td style="width: 26.3251%;">Not Null, Unique</td></tr></tbody></table>

- **Notes:**
    - Commodity will initially seeded via CLI

### Commodity Table

<table border="1" id="bkmrk-column-name-data-typ-5" style="border-collapse: collapse; border-style: solid; width: 101.605%;"><thead><tr><th style="width: 21.6316%;">Column Name</th><th style="width: 18.9122%;">Data Type</th><th style="width: 33.131%;">Description</th><th style="width: 26.3251%;">Constraints</th></tr></thead><tbody><tr><td style="width: 21.6316%;">id</td><td style="width: 18.9122%;">INT</td><td style="width: 33.131%;">Unique identifier for each commodity.</td><td style="width: 26.3251%;">Primary Key, Auto-Increment</td></tr><tr><td style="width: 21.6316%;">commodity\_category\_id  
</td><td style="width: 18.9122%;">INT  
</td><td style="width: 33.131%;">Identifier for the commodity category.</td><td style="width: 26.3251%;">Foreign Key (References commodity\_category\_id)  
</td></tr><tr><td style="width: 21.6316%;">commodity\_name</td><td style="width: 18.9122%;">VARCHAR</td><td style="width: 33.131%;">Name of the commodity (e.g., "Coffee").</td><td style="width: 26.3251%;">Not Null, Unique</td></tr></tbody></table>

- **Notes:**
    - Commodity will initially seeded via CLI

### Tag Table

<table border="1" id="bkmrk-column-name-data-typ-6" style="border-collapse: collapse; width: 100%; border-style: solid;"><thead><tr><th style="width: 16.44%;">Column Name</th><th style="width: 14.2088%;">Data Type</th><th style="width: 43.5168%;">Description</th><th style="width: 25.8344%;">Constraints</th></tr></thead><tbody><tr><td style="width: 16.44%;">id</td><td style="width: 14.2088%;">INT</td><td style="width: 43.5168%;">Unique identifier for each tags.</td><td style="width: 25.8344%;">Primary Key, Auto-Increment</td></tr><tr><td style="width: 16.44%;">name</td><td style="width: 14.2088%;">VARCHAR</td><td style="width: 43.5168%;">Name of the tag.</td><td style="width: 25.8344%;">Not Null, Unique  
</td></tr><tr><td style="width: 16.44%;">created\_at</td><td style="width: 14.2088%;">DATETIME</td><td style="width: 43.5168%;">Timestamp when the tag was created.</td><td style="width: 25.8344%;">Not Null, Default Current Timestamp</td></tr><tr><td style="width: 16.44%;">updated\_at</td><td style="width: 14.2088%;">DATETIME</td><td style="width: 43.5168%;">Timestamp when the tag was last updated.</td><td style="width: 25.8344%;">Nullable</td></tr><tr><td style="width: 16.44%;">created\_by</td><td style="width: 14.2088%;">VARCHAR</td><td style="width: 43.5168%;">User who created the tag. This can be an email or user ID, based on your user management system.</td><td style="width: 25.8344%;">Not Null</td></tr><tr><td style="width: 16.44%;">description</td><td style="width: 14.2088%;">TEXT</td><td style="width: 43.5168%;">Brief description or notes about the tag (Optional).</td><td style="width: 25.8344%;">Nullable</td></tr></tbody></table>

- **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.

### Case Table

<table border="1" id="bkmrk-column-name-data-typ-7" style="border-collapse: collapse; border-style: solid; width: 100%;"><thead><tr><th style="width: 24.1018%;">Column Name</th><th style="width: 13.3518%;">Data Type</th><th style="width: 37.7009%;">Description</th><th style="width: 24.8455%;">Constraints</th></tr></thead><tbody><tr><td style="width: 24.1018%;">id</td><td style="width: 13.3518%;">INT</td><td style="width: 37.7009%;">Unique identifier for each case.</td><td style="width: 24.8455%;">Primary Key, Auto-Increment</td></tr><tr><td style="width: 24.1018%;">case\_name</td><td style="width: 13.3518%;">VARCHAR</td><td style="width: 37.7009%;">Name of the case (e.g., "Country A Coffee Production Comparison").</td><td style="width: 24.8455%;">Not Null</td></tr><tr><td style="width: 24.1018%;">date</td><td style="width: 13.3518%;">DATE</td><td style="width: 37.7009%;">Date associated with the case.</td><td style="width: 24.8455%;">Not Null</td></tr><tr><td style="width: 24.1018%;">year</td><td style="width: 13.3518%;">INT</td><td style="width: 37.7009%;">Year in the case timeline (from the dropdown).</td><td style="width: 24.8455%;">Not Null</td></tr><tr><td style="width: 24.1018%;">country</td><td style="width: 13.3518%;">INT</td><td style="width: 37.7009%;">Country of focus for the case.</td><td style="width: 24.8455%;">Foreign Key (References Country Table)</td></tr><tr><td style="width: 24.1018%;">focus\_commodity\_id</td><td style="width: 13.3518%;">INT</td><td style="width: 37.7009%;">Identifier for the commodity (focus). This could reference a master data table for commodities.</td><td style="width: 24.8455%;">Foreign Key (References Commodity Master Table)</td></tr><tr><td style="width: 24.1018%;">currency</td><td style="width: 13.3518%;">VARCHAR</td><td style="width: 37.7009%;">Chosen currency for the case.</td><td style="width: 24.8455%;">Not Null</td></tr><tr><td style="width: 24.1018%;">area\_size\_unit</td><td style="width: 13.3518%;">VARCHAR</td><td style="width: 37.7009%;">Unit for area size (e.g., hectare, m^2, are).</td><td style="width: 24.8455%;">Not Null</td></tr><tr><td style="width: 24.1018%;">volume\_measurement\_unit</td><td style="width: 13.3518%;">VARCHAR</td><td style="width: 37.7009%;">Unit for volume measurement.</td><td style="width: 24.8455%;">Not Null</td></tr><tr><td style="width: 24.1018%;">cost\_of\_production\_unit</td><td style="width: 13.3518%;">VARCHAR</td><td style="width: 37.7009%;">Unit for cost of production measurement (Per-area/Per-yield/Per-tree).</td><td style="width: 24.8455%;">Not Null</td></tr><tr><td style="width: 24.1018%;">reporting\_period</td><td style="width: 13.3518%;">VARCHAR</td><td style="width: 37.7009%;">Reporting period (Per-season/Per-Year).</td><td style="width: 24.8455%;">Not Null</td></tr><tr><td style="width: 24.1018%;">segmentation</td><td style="width: 13.3518%;">BOOLEAN</td><td style="width: 37.7009%;">Indicates if segmentation is on/off.</td><td style="width: 24.8455%;">Not Null, Default False</td></tr><tr><td style="width: 24.1018%;">living\_income\_study</td><td style="width: 13.3518%;">ENUM  
</td><td style="width: 37.7009%;">Better Income or Living Income  
</td><td style="width: 24.8455%;">Nullable  
</td></tr><tr><td style="width: 24.1018%;">multiple\_commodities</td><td style="width: 13.3518%;">BOOLEAN</td><td style="width: 37.7009%;">Indicates if multiple commodities option is on/off.</td><td style="width: 24.8455%;">Not Null, Default False</td></tr><tr><td style="width: 24.1018%;">logo</td><td style="width: 13.3518%;">VARCHAR</td><td style="width: 37.7009%;">Stores the uploaded logo for the case.</td><td style="width: 24.8455%;">Nullable</td></tr><tr><td style="width: 24.1018%;">private  
</td><td style="width: 13.3518%;">BOOLEAN  
</td><td style="width: 37.7009%;">Indicates if the case is private or public  
</td><td style="width: 24.8455%;">Not Null, Default False  
</td></tr><tr><td style="width: 24.1018%;">created\_at</td><td style="width: 13.3518%;">DATETIME</td><td style="width: 37.7009%;">Timestamp when the case was created.</td><td style="width: 24.8455%;">Not Null, Default Current Timestamp</td></tr><tr><td style="width: 24.1018%;">updated\_at</td><td style="width: 13.3518%;">DATETIME</td><td style="width: 37.7009%;">Timestamp when the case was last updated.</td><td style="width: 24.8455%;">Nullable</td></tr><tr><td style="width: 24.1018%;">created\_by</td><td style="width: 13.3518%;">VARCHAR</td><td style="width: 37.7009%;">User who created the case. This can be an email or user ID, based on your user management.</td><td style="width: 24.8455%;">Not Null</td></tr></tbody></table>

- **Notes:**
    - The **focus\_commodity\_id** assumes that there is a separate master data table for commodities.
    - When the case is marked as **private**, user cannot see this case unless: 
        - The project owner (**created\_by**) invite other user to the case
        - The user role is **business unit admin, business unit editor** or **super admin**
    - The **logo** column for the file path or URL.
    - In delete user workflow API, we need to check whether they have any cases or no 
        - **SHOULD NOT delete cascade**
    - Case owner can be changed by **BU Admin / Super Admin**

### Case Tags Table

<table border="1" id="bkmrk-column-name-data-typ-8" style="border-collapse: collapse; width: 100%; height: 149px;"><colgroup><col style="width: 17.4289%;"></col><col style="width: 11.7429%;"></col><col style="width: 37.7108%;"></col><col style="width: 32.9938%;"></col></colgroup><tbody><tr style="height: 29.8px;"><td style="height: 29.8px;">Column Name  
</td><td style="height: 29.8px;">Data Type  
</td><td style="height: 29.8px;">Description  
</td><td style="height: 29.8px;">Constraint  
</td></tr><tr style="height: 29.8px;"><td style="height: 29.8px;">id  
</td><td style="height: 29.8px;">INT  
</td><td style="height: 29.8px;">Unique Identifier for each Diversified Commodity  
</td><td style="height: 29.8px;">Primary Key, Auto-Increment</td></tr><tr style="height: 29.8px;"><td style="height: 29.8px;">case\_id  
</td><td style="height: 29.8px;">INT  
</td><td style="height: 29.8px;">Identifier for the case.  
</td><td style="height: 29.8px;">Foreign Key (References Case Table)</td></tr><tr style="height: 29.8px;"><td style="height: 29.8px;">tag\_id  
</td><td style="height: 29.8px;">INT  
</td><td style="height: 29.8px;">Identifier for the tag.</td><td style="height: 29.8px;">Foreign Key (References Tag Table)</td></tr></tbody></table>

- **Unique Constraint:** case\_id + tag\_id
- **Notes:**
    - Many to many: **cases &lt;&gt; tags**

### User Tags Table

<table border="1" id="bkmrk-column-name-data-typ-9" style="border-collapse: collapse; width: 100%; height: 149px;"><colgroup><col style="width: 17.4289%;"></col><col style="width: 11.7429%;"></col><col style="width: 37.7108%;"></col><col style="width: 32.9938%;"></col></colgroup><tbody><tr style="height: 29.8px;"><td style="height: 29.8px;">Column Name  
</td><td style="height: 29.8px;">Data Type  
</td><td style="height: 29.8px;">Description  
</td><td style="height: 29.8px;">Constraint  
</td></tr><tr style="height: 29.8px;"><td style="height: 29.8px;">id  
</td><td style="height: 29.8px;">INT  
</td><td style="height: 29.8px;">Unique Identifier for each Diversified Commodity  
</td><td style="height: 29.8px;">Primary Key, Auto-Increment</td></tr><tr style="height: 29.8px;"><td style="height: 29.8px;">user\_id  
</td><td style="height: 29.8px;">INT  
</td><td style="height: 29.8px;">Identifier for the user.  
</td><td style="height: 29.8px;">Foreign Key (References User Table)</td></tr><tr style="height: 29.8px;"><td style="height: 29.8px;">tag\_id  
</td><td style="height: 29.8px;">INT  
</td><td style="height: 29.8px;">Identifier for the tag.</td><td style="height: 29.8px;">Foreign Key (References Tag Table)</td></tr></tbody></table>

- **Unique Constraint:** user\_id + tag\_id
- **Notes:**
    - Many to many: **users &lt;&gt; tags**
- <span style="color: rgb(224, 62, 45);">**NOT REQUIRED**</span>

### Case Commodity Table

<table border="1" id="bkmrk-column-name-data-typ-10" style="border-collapse: collapse; width: 100%; height: 149px;"><colgroup><col style="width: 17.4289%;"></col><col style="width: 11.7429%;"></col><col style="width: 37.7108%;"></col><col style="width: 32.9938%;"></col></colgroup><tbody><tr style="height: 29.8px;"><td style="height: 29.8px;">Column Name  
</td><td style="height: 29.8px;">Data Type  
</td><td style="height: 29.8px;">Description  
</td><td style="height: 29.8px;">Constraint  
</td></tr><tr style="height: 29.8px;"><td style="height: 29.8px;">id  
</td><td style="height: 29.8px;">INT  
</td><td style="height: 29.8px;">Unique Identifier for each Case Commodity  
</td><td style="height: 29.8px;">Primary Key, Auto-Increment</td></tr><tr style="height: 29.8px;"><td style="height: 29.8px;">case\_id  
</td><td style="height: 29.8px;">INT  
</td><td style="height: 29.8px;">Identifier for the case to which the case commodity belongs.</td><td style="height: 29.8px;">Foreign Key (References Case Table)</td></tr><tr style="height: 29.8px;"><td style="height: 29.8px;">commodity\_id  
</td><td style="height: 29.8px;">INT  
</td><td style="height: 29.8px;">Identifier for the commodity. This could reference a master data table for commodities.</td><td style="height: 29.8px;">Foreign Key (References Commodity Table)</td></tr><tr><td>focus\_commodity  
</td><td>BOOLEAN  
</td><td>Whether it's focus commodity  
</td><td>True, Default: False  
</td></tr><tr style="height: 29.8px;"><td style="height: 29.8px;">breakdown  
</td><td style="height: 29.8px;">BOOLEAN  
</td><td style="height: 29.8px;">To define the question list  
</td><td style="height: 29.8px;">Not Null  
</td></tr></tbody></table>

- **Unique Constraint:** case\_id, commodity\_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 commodity\_id is not equal to focus\_commodity\_id in the case table, this means the commodity is a diversified income

### Segment Table

<table border="1" id="bkmrk-column-name-data-typ-11" style="border-collapse: collapse; border-style: solid; width: 100%; height: 165.8px;"><thead><tr style="height: 29.8px;"><th style="width: 17.1808%; height: 29.8px;">Column Name</th><th style="width: 11.2494%; height: 29.8px;">Data Type</th><th style="width: 38.1953%; height: 29.8px;">Description</th><th style="width: 33.3745%; height: 29.8px;">Constraints</th></tr></thead><tbody><tr style="height: 29.8px;"><td style="width: 17.1808%; height: 29.8px;">id</td><td style="width: 11.2494%; height: 29.8px;">INT</td><td style="width: 38.1953%; height: 29.8px;">Unique identifier for each segment.</td><td style="width: 33.3745%; height: 29.8px;">Primary Key, Auto-Increment</td></tr><tr style="height: 46.6px;"><td style="width: 17.1808%; height: 46.6px;">case\_id</td><td style="width: 11.2494%; height: 46.6px;">INT</td><td style="width: 38.1953%; height: 46.6px;">Identifier for the case to which the segment belongs.</td><td style="width: 33.3745%; height: 46.6px;">Foreign Key (References Case Table)</td></tr><tr style="height: 29.8px;"><td style="width: 17.1808%; height: 29.8px;">segment\_name</td><td style="width: 11.2494%; height: 29.8px;">VARCHAR</td><td style="width: 38.1953%; height: 29.8px;">Name or label of the segment (e.g., "City A").</td><td style="width: 33.3745%; height: 29.8px;">Not Null</td></tr><tr style="height: 29.8px;"><td style="width: 17.1808%; height: 29.8px;">target  
</td><td style="width: 11.2494%; height: 29.8px;">DECIMAL  
</td><td style="width: 38.1953%; height: 29.8px;">  
</td><td style="width: 33.3745%; height: 29.8px;">Nullable  
</td></tr><tr><td style="width: 17.1808%;">house\_hold\_size  
</td><td style="width: 11.2494%;">DECIMAL  
</td><td style="width: 38.1953%;">Average Household size: for calculating living income benchmark  
</td><td style="width: 33.3745%;">Nullable  
</td></tr></tbody></table>

- **Unique Constraint:** case\_id, segment\_name

### Question Table

<table border="1" id="bkmrk-column-name-data-typ-12" style="border-collapse: collapse; border-style: solid; width: 100%;"><thead><tr><th style="width: 19.6539%;">Column Name</th><th style="width: 14.5859%;">Data Type</th><th style="width: 45.4883%;">Description</th><th style="width: 20.3956%;">Constraints</th></tr></thead><tbody><tr><td style="width: 19.6539%;">id</td><td style="width: 14.5859%;">INT</td><td style="width: 45.4883%;">Unique identifier for each question.</td><td style="width: 20.3956%;">Primary Key, Auto-Increment</td></tr><tr><td style="width: 19.6539%;">parent\_id</td><td style="width: 14.5859%;">INT</td><td style="width: 45.4883%;">Identifier for the parent question. NULL for top-level questions.</td><td style="width: 20.3956%;">Foreign Key (References question\_id), Nullable</td></tr><tr><td style="width: 19.6539%;">code  
</td><td style="width: 14.5859%;">VARCHAR  
</td><td style="width: 45.4883%;">Code for the question  
</td><td style="width: 20.3956%;">Nullable  
</td></tr><tr><td style="width: 19.6539%;">question\_text</td><td style="width: 14.5859%;">VARCHAR</td><td style="width: 45.4883%;">Text of the question or breakdown label.</td><td style="width: 20.3956%;">Not Null</td></tr><tr><td style="width: 19.6539%;">descriptions  
</td><td style="width: 14.5859%;">TEXT  
</td><td style="width: 45.4883%;">Description text of the question  
</td><td style="width: 20.3956%;">Nullable  
</td></tr><tr><td style="width: 19.6539%;">default\_value</td><td style="width: 14.5859%;">TEXT</td><td style="width: 45.4883%;">JavaScript string used for front-end calculations.</td><td style="width: 20.3956%;">Nullable  
</td></tr><tr><td style="width: 19.6539%;">case\_commodity\_id  
</td><td style="width: 14.5859%;">INT  
</td><td style="width: 45.4883%;">Foreign Key  
</td><td style="width: 20.3956%;">Nullable  
</td></tr><tr><td style="width: 19.6539%;">created\_by  
</td><td style="width: 14.5859%;">INT  
</td><td style="width: 45.4883%;">Identifier for the user to which the question belongs.</td><td style="width: 20.3956%;">Foreign Key (References to user\_id), Nullable</td></tr></tbody></table>

- **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.

<p class="callout warning">Case commodity Id only used for custom added specific question</p>

<p class="callout success">If the default value is Null, then the children 1 level below the question should be added up as SUM.</p>

<p class="callout info">Parent ID Null is the Income Drivers. Purpose: Selectively fetch values of Income Drivers for the visualization</p>

### Commodity Category Question

<table border="1" id="bkmrk-column-name-data-typ-13" style="border-collapse: collapse; width: 100%; height: 119.2px;"><colgroup><col style="width: 19.6539%;"></col><col style="width: 14.8331%;"></col><col style="width: 45.3585%;"></col><col style="width: 20.0308%;"></col></colgroup><tbody><tr style="height: 29.8px;"><td style="height: 29.8px;">Column Name  
</td><td style="height: 29.8px;">Data Type  
</td><td style="height: 29.8px;">Description  
</td><td style="height: 29.8px;">Constraints  
</td></tr><tr style="height: 29.8px;"><td style="height: 29.8px;">id  
</td><td style="height: 29.8px;">INT  
</td><td style="height: 29.8px;">Unique identifier for commodity question.</td><td style="height: 29.8px;">Primary Key, Auto-Increment</td></tr><tr style="height: 29.8px;"><td style="height: 29.8px;">commodity\_category\_id  
</td><td style="height: 29.8px;">INT  
</td><td style="height: 29.8px;">Identifier for the commodity category.</td><td style="height: 29.8px;">Foreign Key (References to commodity\_category\_id)</td></tr><tr style="height: 29.8px;"><td style="height: 29.8px;">question\_id  
</td><td style="height: 29.8px;">INT  
</td><td style="height: 29.8px;">Identifier for the question\_id.</td><td style="height: 29.8px;">Foreign Key (References to question\_id)</td></tr></tbody></table>

- **Unique Constraint:** commodity\_category\_id, question\_id
- **Notes:**
    - Commodity category Question define the list of question that will show in the case based on the selected commodity. Commodity -&gt; Commodity Category -&gt; Question.
    - Commodity Category will initially seeded via CLI

### Visualization Table

<table border="1" id="bkmrk-column-name-data-typ-14" style="border-collapse: collapse; border-style: solid; width: 100%; height: 229.2px;"><thead><tr style="height: 29.8px;"><th style="width: 16.1921%; height: 29.8px;">Column Name</th><th style="width: 12.4853%; height: 29.8px;">Data Type</th><th style="width: 37.7009%; height: 29.8px;">Description</th><th style="width: 33.4981%; height: 29.8px;">Constraints</th></tr></thead><tbody><tr style="height: 29.8px;"><td style="width: 16.1921%; height: 29.8px;">id</td><td style="width: 12.4853%; height: 29.8px;">INT</td><td style="width: 37.7009%; height: 29.8px;">Unique identifier for each answer.</td><td style="width: 33.4981%; height: 29.8px;">Primary Key, Auto-Increment</td></tr><tr style="height: 29.8px;"><td style="width: 16.1921%; height: 29.8px;">case\_id  
</td><td style="width: 12.4853%; height: 29.8px;">INT  
</td><td style="width: 37.7009%; height: 29.8px;">Identifier for the case to which the answer belongs.</td><td style="width: 33.4981%; height: 29.8px;">Foreign Key (References Case Table)</td></tr><tr style="height: 46.6px;"><td style="width: 16.1921%; height: 46.6px;">segment\_id  
</td><td style="width: 12.4853%; height: 46.6px;">INT  
</td><td style="width: 37.7009%; height: 46.6px;">Identifier for the segment to which the answer belongs.</td><td style="width: 33.4981%; height: 46.6px;">Foreign Key (References Segment Table)</td></tr><tr style="height: 46.6px;"><td style="width: 16.1921%; height: 46.6px;">tab  
</td><td style="width: 12.4853%; height: 46.6px;">ENUM  
</td><td style="width: 37.7009%; height: 46.6px;">Enum for which tab this configuration belongs to  
</td><td style="width: 33.4981%; height: 46.6px;">Enum (sensitivity\_analysis,scenario\_modeling)  
</td></tr><tr style="height: 46.6px;"><td style="width: 16.1921%; height: 46.6px;">config  
</td><td style="width: 12.4853%; height: 46.6px;">JSONB</td><td style="width: 37.7009%; height: 46.6px;">Identifier for the segment to which the answer belongs.</td><td style="width: 33.4981%; height: 46.6px;">JSONB</td></tr></tbody></table>

- **Unique Constraint:** segment\_id, tab

### Segment Answer Table

<table border="1" id="bkmrk-column-name-data-typ-15" style="border-collapse: collapse; border-style: solid; width: 100%; height: 274.8px;"><thead><tr style="height: 29.8px;"><th style="width: 16.1921%; height: 29.8px;">Column Name</th><th style="width: 12.4853%; height: 29.8px;">Data Type</th><th style="width: 37.7009%; height: 29.8px;">Description</th><th style="width: 33.4981%; height: 29.8px;">Constraints</th></tr></thead><tbody><tr style="height: 29.8px;"><td style="width: 16.1921%; height: 29.8px;">id</td><td style="width: 12.4853%; height: 29.8px;">INT</td><td style="width: 37.7009%; height: 29.8px;">Unique identifier for each answer.</td><td style="width: 33.4981%; height: 29.8px;">Primary Key, Auto-Increment</td></tr><tr style="height: 46.6px;"><td style="width: 16.1921%; height: 46.6px;">case\_commodity\_id  
</td><td style="width: 12.4853%; height: 46.6px;">INT  
</td><td style="width: 37.7009%; height: 46.6px;">Unique Identifier for each commodity case commodity  
</td><td style="width: 33.4981%; height: 46.6px;">Foreign Key (References Case Case Commodity Table)  
</td></tr><tr style="height: 46.6px;"><td style="width: 16.1921%; height: 46.6px;">segment\_id</td><td style="width: 12.4853%; height: 46.6px;">INT</td><td style="width: 37.7009%; height: 46.6px;">Identifier for the segment to which the answer belongs.</td><td style="width: 33.4981%; height: 46.6px;">Foreign Key (References Segment Table)</td></tr><tr style="height: 46.6px;"><td style="width: 16.1921%; height: 46.6px;">question\_id</td><td style="width: 12.4853%; height: 46.6px;">INT</td><td style="width: 37.7009%; height: 46.6px;">Identifier for the question being answered.</td><td style="width: 33.4981%; height: 46.6px;">Foreign Key (References Question Table)</td></tr><tr style="height: 29.8px;"><td style="width: 16.1921%; height: 29.8px;">current\_value  
</td><td style="width: 12.4853%; height: 29.8px;">DECIMAL  
</td><td style="width: 37.7009%; height: 29.8px;">The response or current value given to the question.</td><td style="width: 33.4981%; height: 29.8px;">Not Null  
</td></tr><tr style="height: 45.6px;"><td style="width: 16.1921%; height: 45.6px;">feasible\_value</td><td style="width: 12.4853%; height: 45.6px;">DECIMAL</td><td style="width: 37.7009%; height: 45.6px;">The response or feasible value given to the question.</td><td style="width: 33.4981%; height: 45.6px;">Nullable, Default: Current  
</td></tr></tbody></table>

- **Unique Constraint:** case\_commodity\_id, segment\_id, question\_id
- **Notes:**
    - If the feasible value is not defined, then store current value to feasible value.

### Living Income Benchmark Table

<table border="1" id="bkmrk-column-name-data-typ-16" style="border-collapse: collapse; border-style: solid; width: 101.605%;"><thead><tr><th style="width: 19.7775%;">Column Name</th><th style="width: 17.1861%;">Data Type</th><th style="width: 36.7076%;">Description</th><th style="width: 26.3288%;">Constraints</th></tr></thead><tbody><tr><td style="width: 19.7775%;">id</td><td style="width: 17.1861%;">INT</td><td style="width: 36.7076%;">Unique identifier.</td><td style="width: 26.3288%;">Primary Key, Auto-Increment</td></tr><tr><td style="width: 19.7775%;">country\_id  
</td><td style="width: 17.1861%;">INT  
</td><td style="width: 36.7076%;">Identifier for the country.</td><td style="width: 26.3288%;">Foreign Key (References country\_id)  
</td></tr><tr><td style="width: 19.7775%;">currency\_id  
</td><td style="width: 17.1861%;">INT  
</td><td style="width: 36.7076%;">Identifier for the currency.</td><td style="width: 26.3288%;">Foreign Key (References currency\_id)</td></tr><tr><td style="width: 19.7775%;">year</td><td style="width: 17.1861%;">INT</td><td style="width: 36.7076%;">Year of the living income the benchmark.</td><td style="width: 26.3288%;">Not Null  
</td></tr><tr><td style="width: 19.7775%;">value  
</td><td style="width: 17.1861%;">DECIMAL  
</td><td style="width: 36.7076%;">The benchmark value.</td><td style="width: 26.3288%;">Not Null  
</td></tr></tbody></table>

- **Unique Constraint:** country\_id, segment\_id, question\_id
- **Notes:**
    - Living Income Benchmark will initially seeded via CLI

### Reference Data Table

<table border="1" id="bkmrk-column-name-data-typ-17" style="border-collapse: collapse; border-style: solid; width: 101.605%; height: 242.2px;"><thead><tr style="height: 29.8px;"><th style="width: 19.7775%; height: 29.8px;">Column Name</th><th style="width: 17.1861%; height: 29.8px;">Data Type</th><th style="width: 36.7076%; height: 29.8px;">Description</th><th style="width: 26.3288%; height: 29.8px;">Constraints</th></tr></thead><tbody><tr style="height: 29.8px;"><td style="width: 19.7775%; height: 29.8px;">id</td><td style="width: 17.1861%; height: 29.8px;">INT</td><td style="width: 36.7076%; height: 29.8px;">Unique identifier.</td><td style="width: 26.3288%; height: 29.8px;">Primary Key, Auto-Increment</td></tr><tr style="height: 46.6px;"><td style="width: 19.7775%; height: 46.6px;">country\_id  
</td><td style="width: 17.1861%; height: 46.6px;">INT  
</td><td style="width: 36.7076%; height: 46.6px;">Identifier for the country.</td><td style="width: 26.3288%; height: 46.6px;">Foreign Key (References country\_id)  
</td></tr><tr style="height: 46.6px;"><td style="width: 19.7775%; height: 46.6px;">commodity\_id  
</td><td style="width: 17.1861%; height: 46.6px;">INT  
</td><td style="width: 36.7076%; height: 46.6px;">Identifier for the commodity.</td><td style="width: 26.3288%; height: 46.6px;">Foreign Key (References commodity\_id)</td></tr><tr style="height: 29.8px;"><td style="width: 19.7775%; height: 29.8px;"><span style="background-color: rgb(251, 238, 184);">type</span>  
</td><td style="width: 17.1861%; height: 29.8px;"><span style="background-color: rgb(251, 238, 184);">ENUM</span>  
</td><td style="width: 36.7076%; height: 29.8px;"><span style="background-color: rgb(251, 238, 184);">Baseline / Segment Average</span>  
</td><td style="width: 26.3288%; height: 29.8px;"><span style="background-color: rgb(251, 238, 184);">Enum</span>  
</td></tr><tr style="height: 29.8px;"><td style="width: 19.7775%; height: 29.8px;">year</td><td style="width: 17.1861%; height: 29.8px;">INT</td><td style="width: 36.7076%; height: 29.8px;">Year of the living income the benchmark.</td><td style="width: 26.3288%; height: 29.8px;">Nullable  
</td></tr><tr style="height: 29.8px;"><td style="width: 19.7775%; height: 29.8px;">farm\_size  
</td><td style="width: 17.1861%; height: 29.8px;">DECIMAL  
</td><td style="width: 36.7076%; height: 29.8px;">The Farm Land Size in Hectare  
</td><td style="width: 26.3288%; height: 29.8px;">Nullable  
</td></tr><tr><td style="width: 19.7775%;">farmer\_production  
</td><td style="width: 17.1861%;">DECIMAL  
</td><td style="width: 36.7076%;">Farmer Production in KG  
</td><td style="width: 26.3288%;">Nullable  
</td></tr><tr><td style="width: 19.7775%;">farmgate\_price  
</td><td style="width: 17.1861%;">DECIMAL  
</td><td style="width: 36.7076%;">Farm Gate Price USD / KG  
</td><td style="width: 26.3288%;">Nullable  
</td></tr><tr><td style="width: 19.7775%;">farmer\_expenses  
</td><td style="width: 17.1861%;">DECIMAL  
</td><td style="width: 36.7076%;">Cost of Production in USD  
</td><td style="width: 26.3288%;">Nullable  
</td></tr><tr><td style="width: 19.7775%;">diversified\_income  
</td><td style="width: 17.1861%;">DECIMAL  
</td><td style="width: 36.7076%;">Secondary or Tertiary Income  
</td><td style="width: 26.3288%;">Nullable  
</td></tr></tbody></table>

- **Notes:**
    - Reference Data Table will initially seeded via CLI
    - Initial Data is here: **<span style="color: rgb(224, 62, 45);">PROVIDE LINK TO DRIVE</span>**

## API Endpoints

Endpoint Prefix: **/api**

### 1. Users Endpoint  


#### 1.1. User Login

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

```json
{
  "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

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

**Example Response**

```json
{
  "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

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

```json
{
  "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

- Endpoint: **/user/&lt;user\_id&gt;**
- Method: **GET**
- Path Variable: **user\_id**
- Headers:  
    
    - accept: application/json
- Request Body: None
- Query Parameter: None
- Authorization: Bearer Token - Admin Access

**Example Response**

```json
{
  "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

- Endpoint: **/user**
- Method: **POST**
- Headers:  
    
    - accept: application/json
    - Content-Type: application/x-www-form-urlencoded
- Request Body:  
    
    - fullname: string
    - organisation: organisation\_id
    - business\_units: business\_unit\_id
- Query Parameter: None
- Authorization: None

#### 1.6. Register Password

- Endpoint: **/user/invitation/&lt;invitation\_id&gt;**
- 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/&lt;user\_id&gt;**
- Path Variable: **user\_id**
- Method: **PUT**
- Headers: 
    - accept: application/json
- Request Body: 
    - fullname: string
    - password: string
    - organisation: organisation\_id
    - business\_unit: business\_unit\_id
    - role: enum (super admin, admin, viewer, editor, user)
    
    
    - cases: array of object 
        - case\_id
        - permission: enum (edit, view)
    - tags: array of object 
        - tag\_id
        - permission: enum (edit, view)
- Query Parameter: None
- Authorization: 
    - Bearer Token - Admin Access: All Request
    - Bearer Token - User Access: All except case\_id, tags\_id, active

#### 1.6. Delete User

- Endpoint: **/user/&lt;user\_id&gt;**
- Path Variable: **user\_id**
- Method: **DELETE**
- Headers: 
    - accept: application/json
- Request Body: None
- Query Parameter: None
- Authorization: 
    - Bearer Token - Admin Access

### 2. Tags Endpoints

#### 2.1. List of Tags

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

**Example Response**

```json
{
  "current": 1,
  "data": [
    {
      "id": 1,
      "name": "Tag Name",
      "description": "Tag Description",
      "cases_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
    - cases: list of case\_id
- Authorization: Bearer Token - Admin Access

#### 2.3. Tag Detail

- Endpoint: **/tag/&lt;tag\_id&gt;**
- Path Variable: **tag\_id**
- Method: **GET**
- Headers: 
    - accept: application/json
- Request Body: None
- Authorization: Bearer Token

**Example Response**

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

#### 2.4. Edit Tag  


- Endpoint: **/tag/&lt;tag\_id&gt;**
- Path Variable: **tag\_id**
- Method: **PUT**
- Headers: 
    - accept: application/json
    - Content-Type: application/json
- Request Body: 
    - name: string (nullable)
    - description: string (nullable)
    - cases: list of case\_id
- Authorization: Bearer Token - Admin Access

### 3. Case Endpoints

#### 3.1. List Cases

- Endpoint: **/case**
- Method: **GET**
- Headers:  
    
    - accept: application/json
- Request Body: None
- Query Parameter: 
    - page: integer
    - limit: integer
    - search: string
    - tags: single / multiple tags\_id
    - focus\_commodity: single / multiple focus\_commodity\_id
- Authorization: Bearer Token - Any

**Example Response**

```json
{
  "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

- Endpoint: **/case/&lt;case\_id&gt;**
- Path Variable: **case\_id**
- Method: **GET**
- Headers: 
    - accept: application/json
- Request Body: None
- Authorization: Bearer Token - Admin Access

**Example Response**

```json
{
  "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:**

- In above example, we have 2 **case\_commodities** one has same **commodity\_id** as **focus\_commodity\_id** and the other **case\_commodities** has different **commodity\_id** with the **focus\_commodity\_id** which consider as diversified income.

#### 3.3. Add Case

- Endpoint: **/case**
- Method: **POST**
- Headers:  
    
    - accept: application/json
    - Content-Type: application/json
- Request Body: 
    - case\_name: string
    - date: date
    - year: integer
    - country: integer
    - focus\_commodity\_id: commodity\_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\_commodities: boolean
    - created\_by: string
    - other\_commodities: array of objects or null  
        
        - commodity\_id: commodity\_id
        - breakdown: boolean
- Authorization: Bearer Token - Admin Access

**Notes:**

- If other commodities is null, we still store a row in **case\_commodities** table which stores **commodity\_id** from the **focus\_commodity\_id** and **breakdown** by default is **true.** So if we have 1 **other\_commodities**, then we store 2 **case\_commodities**

#### 3.4. Edit Case

- Endpoint: **/case/&lt;case\_id&gt;**
- Path Variable: **case\_id**
- Method: **PUT**
- Headers: 
    - accept: application/json
    - Content-Type: application/json
- Request Body: 
    - case\_name: string
    - date: date
    - year: integer
    - country: integer
    - focus\_commodity\_id: commodity\_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\_commodities: boolean
    - other\_commodities: array of objects 
        - commodity\_id: commodity\_id
        - breakdown: boolean
- Authorization: Bearer Token - Admin Access

**Notes:**

- <span style="color: rgb(224, 62, 45);">What if we remove the **other\_commodities** from case which has existing question value?</span>

### 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 
        - case\_id: integer
        - segment\_name: string
        - target: decimal (nullable)
        - house\_hold\_size: decimal (nullable)
- Authorization: Bearer Token

### 5. Question Endpoint

#### 5.1. Get Questions By Commodity ID

- Endpoint: **/questions**
- Path Variable: **commodity\_id**
- Method: GET
- Headers: 
    - accept: application/json
- Request Body: 
    - question\_groups: list of objects 
        - commodity\_id: integer
        - breakdown: boolean
- Authorization: Bearer Token

**Example Response**

```json
[{
  "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:**

- List Question is defined by commodity\_id and breakdown parameter
- The list of Question Group is **focus\_commodity\_id + list\_of\_commodity\_ids\_in\_diversified\_income**

### 6. Segment Value Endpoint

#### 6.1. Add / Update Segment Value

- Endpoint: **/segment-answer/&lt;segment\_id&gt;**
- Method: **POST**
- Headers: 
    - accept: application/json
    - Content-Type: application/json
- Request Body: 
    - value: array of objects 
        - case\_commodity\_id: case\_commodity\_id
        - segment\_id: segment\_id
        - question\_id: question\_id
        - current\_value: float
        - feasible\_value: float
- Authorization: Bearer Token

### 7. Visualization Endpoint

#### 7.1. Get Visualization Config

- Endpoint: **/segment-answer/&lt;case\_id&gt;**
- Method: **GET**
- Headers: 
    - accept: application/json
    - Content-Type: application/json
- Request Body: None
- Authorization: Bearer Token

**Example Response**

#### 7.2. Add / Update Visualization Config  


- Endpoint: **/segment-answer/&lt;segment\_id&gt;**
- Method: **POST**
- Headers: 
    - accept: application/json
    - Content-Type: application/json
- Request Body: 
    - tab: Enum tab
    - config: objects (any key)
- Authorization: Bearer Token

## Error Handling &amp; 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 cases, there should be a way to navigate, search, and manage these cases.
- 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