About Datarun

[Hamza-ye]

We manage field activities involving teams and assignments, where data collection is a core task. Activities may target specific entities such as locations, health facilities, or people, and could involve tasks like distributing items (e.g., ITNs, medicines) or supervising teams. Each team has planned locations and allocated quantities for their assignments.

Current Design:

1. Assignments: Link an activity, orgUnit, team, and day.
2. Activity: Groups assignments, teams, and tasks within a defined time frame.
3. Teams: created before linking a user, allowing assignment distribution. Later, a user can be linked to a team to gain permissions and configurations upon login.
4. Users: Store login info (username, password, etc.).
5. DataForm Templates: Templates for data collection, varying per team/task.
6. Data Submission: Stores submitted data linked to teams and assignments.

Challenges:

1. Dynamic Data Collection: Activities can require reporting on locations, quantities, teams, or tasks.
2. User-Specific Access: Control what data (locations, assignments, or teams) a user can select in forms or download for reporting.
3. Flexibility and Intuition: System must adapt to different scenarios (e.g., task reporting, supervision visits) while being simple to configure and intuitive for users.
4. Reporting: Data must be organized and linked in a way that is easy to export to external systems for advanced analysis and reporting without requiring significant cleaning or transformations.
5. Integration with Main System: The system should function as a separate module that is dynamic yet independent of the main ERP system. It should allow seamless data collection and linkage to entities within the ERP system when needed, without being tightly coupled to it.

---

Dependencies/References:

• Progress Logs

[Hamza-ye]

Current Approach

Assignments

• Definition:
  Assignments link an activity, organization unit (orgUnit), assigned team, day, and a map of dynamic attributes (e.g., ITNs, Population):

  { "attributeName": "attributeValue" }

  • Assignments are implemented hierarchically, allowing parent-child relationships. For example:
    • Supervisor Assignment:
      • Activity: ITNs Supervision.
      • OrgUnit: District supervised.
      • Team: Supervisor team.
      • StartDay: 1.
      • Allocated Resources: Total district ITNs and population.
    • Child Assignment:
      • Activity: ITNs Distribution.
      • OrgUnit: Village within the district.
      • Team: Field distribution team.
      • StartDay: 1.
      • Allocated Resources: Village ITNs and population.

• Forms Linked to Assignments:

  • Assignments can link one or more DataForms to collect progress or other contextual information.
  • Example assignment for a team:

    {
      "uid": "llm1EHPJ5kh",
      "entityScope": "Assigned",
      "activity": { "uid": "RHl5bdmYXVr" },
      "startDay": 1,
      "orgUnit": { "uid": "HgWpVtC10fF" },
      "team": { "uid": "YFNLJEoYpfy" },
      "allocatedResources": { "ITNs": 899, "Population": 89 },
      "parent": { "uid": "rSBRh6eofeH" },
      "forms": [
        "Tcf3Ks9ZRpB", 
        "RtnmK39VXTY"
      ]
    }

• Team Assignment Scope:

  • A user linked to a team fetches configurations when logging in, including:
    • Assignments with entityScope: "Assigned".
    • Sub-assignments (entityScope: "Managed") linked to the user’s team or subordinate teams.
    • Forms they have permission to add submissions for.
  • Assignments are organized hierarchically to allow intuitive exploration and better tracking of tasks.

Teams

• Dynamic Configuration:
  • Teams can be formed for specific projects and dissolved afterward.
  • Teams are hierarchical, with supervisors managing subordinate teams and their assignments.
• Example of Team:

{
    "uid": "P5fjmw0elDV",
    "code": "317",
    "name": "team-317",
    "disabled": false,
    "activity": { "uid": "RHl5bdmYXVr" },
    "users": [ { "uid": "qqHQA2BFJJf" } ],
    "managedTeams": [],
    "formPermissions": [
        { "form": "Tcf3Ks9ZRpB", "permissions": ["ADD_SUBMISSIONS"] },
        { "form": "oHpNiSgUOT4", "permissions": ["ADD_SUBMISSIONS"] }
    ]
}

Form Permissions:

[
    "VIEW_FORM_TEMPLATE",
    "EDIT_FORM_TEMPLATE",
    "VIEW_SUBMISSIONS",
    "ADD_SUBMISSIONS",
    "EDIT_SUBMISSIONS",
    "DELETE_SUBMISSIONS",
    "EDIT_SUBMISSIONS_FROM_TEAMS",
    "EDIT_SUBMISSIONS_CONDITION",
    "APPROVE_SUBMISSIONS",
    "APPROVE_SUBMISSIONS_CONDITION",
    "DELETE_SUBMISSIONS",
    "DELETE_SUBMISSIONS_FROM_TEAMS",
    "DELETE_SUBMISSIONS_CONDITION"
]

• Woring: VIEW_FORM_TEMPLATE, EDIT_FORM_TEMPLATE, VIEW_SUBMISSIONS, ADD_SUBMISSIONS, EDIT_SUBMISSIONS, DELETE_SUBMISSIONS,

---

Data Forms

• Templates:

  • Data collection templates vary based on team or assignment needs.
  • Forms may be general or linked to specific assignments for tracking progress.

• Submissions:

  • Store collected data, including metadata like timestamps, GPS coordinates, assignment reference if the template is linked to an assignment, and user identifiers.

---

Goal

1. Mobile App (First Release)

• Focus on Core Features:

  • Simplify the UI for activities, forms, and assignments.
  • Organize data intuitively for offline/online data entry and submission.

• Features to Build for Release:

  1. List Activities:
     • Show activities the user is involved in based on team/assignments.
  2. Forms Dashboard:
     • Display forms required for data collection.
     • Navigate to forms linked to assignments the user is responsible for.
  3. Assignments Management:
     • Organize assignments for easy exploration and filtering.
     • Indicate assignment status, calculated based on forms submitted.
  4. Progress Tracking:
     • For users managing sub-assignments, show hierarchical views of assignments and their statuses.

2. Web Interface (Future Plan)

• Extended Capabilities:
  • Configurable dashboards for non-technical users.
  • Drag-and-drop tools for form and workflow design.
  • Enhanced visualization for activity, team, and assignment management.

---

Key Implementation steps

1. Backend Enhancements:

   • Assignment Hierarchy: hierarchical assignment relationships and support parent-child navigation.
   • Dynamic Team Management: Implement flexible team configurations and permissions for form access.
   • Entity Scopes: Refine logic for fetching assignments and forms based on Assigned and Managed scopes.

2. Frontend Priorities (Mobile):

   • Assignment Explorer:
   • Form Submission UI:
   • Status Indicators

3. Performance Optimizations:

   • Caching: Pre-fetch assignments and forms for offline usage.
   • Hierarchical Queries: Optimize database queries for hierarchical structures.

4. Future-Proof Design:

   • Modularize form templates and workflows to allow easy future extensions.
   • Develop reusable components for assignment management and data collection workflows.

5. Web UI (Planned):

   • Leverage drag-and-drop builders for non-technical configuration.
   • Add interactive dashboards for tracking and managing activities, and submissions.

[Hamza-ye]

New updates

1. Task as a Core Entity

• Define a Task entity as the central abstraction for all actionable items.
  • Parent-Child Relationships: Tasks can represent hierarchical structures (e.g., Task → Subtask).
  • Attributes of a Task:
    • id
    • name
    • description
    • startDate
    • endDate
    • allocatedResources (e.g., ITNs, materials)
    • targetedOrgUnits (linked to locations or organizational units)
    • status (e.g., planned, in progress, completed)
    • relatedEntities (flexible linkage to any system entity such as forms, teams, or assignments).
  • Dynamic Properties: Use a key-value store or metadata field to handle campaign-specific needs (e.g., population, central location).

2. Assignment as a Link Entity

• Treat Assignment as a relationship between a Task (or Subtask) and an Assignee (team or individual).
  • Attributes of an Assignment:
    • id
    • taskId
    • assignee (team or individual)
    • assignedForms (references to forms for progress or completion reporting).
    • status (active, reassigned, completed).
  • Assignments become lightweight and flexible, facilitating reassignment or multi-team collaboration.

[Hamza-ye]

New things and ideas on the way:

Inventory Reports and Online Management

Inventory reports are managed online. Offline operations for stock movements are not allowed to ensure quantities are handled directly and accurately, without discrepancies or errors.

Key functionalities include:

1. Stock Issuance and Returns:

   • A team can issue items from their assigned inventory and return them later.
   • Stock transfers can occur between teams or between warehouses.

2. Restrictions on Inventory Use:

   • Teams can only issue items if there’s sufficient stock available.
   • If a team issues stock to another warehouse, the receiving warehouse's stock must increase accordingly.

3. Offline Discrepancy Handling:

   • If stock movement occurs while a team is offline (e.g., a team issues stock but doesn’t have connectivity), the operations team can manually reconcile the discrepancy.

---

Supervisor Role

Supervisors can:

1. Access Team Reports and Summaries:

   • Supervisors can view historical reports such as village updates, inventory changes, or any team activities. These reports are organized chronologically, much like a bank statement.

2. Reassign Villages Between Teams:

   • If a team is unable to cover a village or if another team covers it instead, supervisors can reassign the village from the first team to the second.
   • Once reassigned, the new team will see the task as overdue and must submit the required report promptly.

3. Manage Same-Day Tasks:

   • Villages scheduled for the current day must be addressed on the same day.
   • If a village needs to be postponed, supervisors must reschedule it for another day and specify the new date.

---

System Workflow and Automation

• The system ensures that every campaign or operation ends with complete and accurate reports for all assigned villages.
• Each village has a detailed history, showing:
  • How it started.
  • The team initially responsible.
  • Any reassignment history (whether by date or between teams).

This ensures that supervisors always have a ready plan, and no village remains without a report or gets duplicate submissions from multiple teams.

Online Accessibility for Supervisors

• Supervisors can access their team’s reports and summaries directly online, reducing device load by only fetching data for the specific team.
• However, non-inventory-related reports (e.g., village updates) can still be submitted offline.

---

Future Enhancements

1. Additional Features for Management and Operations:

   • Over time, new tools for administration, operations, analysis, and reporting will be added to the mobile app, module based.
   • These improvements will specifically target campaigns, inventory management, and assignments.

2. Activity-Based Display:

   • The new features will only be available to users who are part of a team assigned to an activity involving inventory management or daily routing.

---

Activities Without Routes or Assignments

For activities that don’t involve routes, assignments, or inventory (e.g., surveys, emergency unit reports, pest control):

1. Simplified Interface:
   • Users will only see the relevant forms for their activity.
   • These forms can be filled and submitted directly.

---

Map Integration for Supervisors and Teams

1. Map Component:

   • Supervisors will have access to a map component to view targeted points, covered areas, and outstanding locations.
   • Filtering options will allow supervisors to focus on specific teams or villages.
   • Supervisors can zoom in on locations to pinpoint uncovered areas.

2. Team-Level Maps:

   • Teams can also access the map but only see their own targeted areas.
   • Of course The map feature will be online which will ensure real-time accuracy.

---

Transaction History and Alerts

1. Historical Transactions:

   • A detailed, chronological feed will track all incoming and outgoing operations for any entity in the system.
   • This feed functions similarly to a bank statement, offering transparency and traceability. Submitting, editing, approving, deleting, ...etc just reverse the effect keeping all movements history.

2. Future Alerts and Notifications:

   • Additional features will include real-time alerts and notifications, enabling communication similar to a chat system.

[Hamza-ye]

Initial Assignment/task's card:

| | |

[Hamza-ye]

Check out the new release's (V1.0.2) test APK and code:

• Test Instance's Forms templates:

---

[Hamza-ye]

Early Structure and Limitations

• Initially, forms were submitted as complete structures with fields and their configurations embedded, similar to uploading an XLS form.
• Fields (Data Elements) referencing them across forms, lacking unique IDs. This made it hard to reuse the same field in multiple forms, or comparing and referencing field values from different contexts (e.g., target vs. actual).
• Calculated values from inputs were missing.
• Different data sources for the same field created challenges. needed to add more diminsionality to the value without cluttering the context too much.

Combining Ideas from Other Systems

• I draw inspiration from DHIS2, aiming to manage data with dimensions (like time and location), but with forms configurations much like ODK.
• I now use lightweight data elements and option sets, which are stored in MongoDB for flexibility and speed.
  • MongoDB enables quick submissions and easy restructuring of data.
  • PostgreSQL is used for finalizing, querying, and reporting on submitted data elements.
  • Even driven processing to link both worlds.

Offline and Team Collaboration

• The mobile app is still offline-first, but it cannot yet link different teams for progress tracking and reporting summaries.
• data elements/options as their own separate models would partly address this, it's now:
  1. Store basic properties unrelated to their form-specific configurations.
  2. Allow overriding some properties in forms without affecting the core data element.

New introduced Fields' structure:

• DataElement contains only its minimal basic properties, more configurations can be overridden or provided in the form, overridable conf: [mandatory, defaultValue, name, and properties for scanned/Qr/Barcodes field]

{
    "uid": "dataElementUid",
    "name": "householdreference",
    "type": "Reference",
    "description": "<string>",
    "defaultValue": "<string>",
    "mandatory": "<bool>",
    "optionSet": "<string>",
    "properties": "<Map>"
}

• Example of the field's Configurations in the form, which also include a backend path generated replacing the previous nested view:

{
    "id": "dataElementUid",    
    "path": "grandparentSection.parentSection.dataElementUid",
    "parent": "parentSection",
    "rules": [ { "expression": "#{inputmethod} == 'fromList'", "action": "Show" } ],
    "order": 22,
    "calculation": "<string>",
    "defaultValue": "<string>",
    "mandatory": "<bool>",
    "readOnly": "<bool>",
    "constraint": "<string>",
    "constraintMessage": "<string>",
    "choiceFilter": "<string>",
    "properties": "<Map>"
}

• Sections/RepeatableSection are embedded as part of the form Configuration separately from the fields:

{
    "path": "households_information.householdnames",
    "parent": "households_information",
    "name": "householdnames",
    "description": "<string>",
    "label": {
        "en": "Household Data",
        "ar": "بيانات المنازل"
    },
    "rules": [],
    "appearance": null,
    "order": 18,
    "repeatable": "<bool>",
    "id": "householdnames"
}

---

Progression

• Created a maven JSON Flattening library FlattenLib, which provide configurable flattening/unflattering of JSON nested data and arrays, and CSV serialize/deserialize to/from JSON.
• Backend Schema generation based on the form Template and its fields configurations and rules, which verify the form's submitted data before saving to the database, and also referenced by the FlattenLib to improve and elimentat the issues around typing, arrays' indices handling, and other issues related to the CSV serialization process.

Current Focus Areas

The system is evolving to address current limitations by separating and refining core components. However, there are still areas to improve:

1. Flexibility: Keeping the system simple to configure for basic tasks while supporting more complex workflows.
2. Consistency: Preventing data integrity issues when configurations change.

[Hamza-ye]

While Seeking System Design Ideas for a Flexible Dimensional Data Model that enables quick development of facts and dimensions based on business needs (Dynamic and Context-Dependent Dimensions):

Data elements can appear in different dimensions based on the reporting context. Some examples:

1. Multiple Teams in One Activity:

In a single activity, two teams collect data on ITNs distributed in a given location.

One team reports detailed actual work, while another reports higher-level, aggregated monitoring totals.

The data represents the same metric (ITNs distributed) but across different dimensions.

2. Multiple Category for the Same Data Element with changing contexts:

Within the same activity, a data element might represent different categories:

Handed-out warehouse quantities

Distributed quantities

Lost quantities

This is a simple examples I could initially think about, but the goals is to handle more complex example without too much configuration complexities on the user!

Initial thoughts:

• I need system design ideas, either from existing proven models or novel approaches to achieve:

• Flexible, dynamic dimensionality that adapts to changing reporting needs.

• Robust consistency where necessary, preventing data fragmentation.

• Scalability to support evolving workflows while maintaining efficient querying and aggregation.

---

Comparison and Innovations

Feature	-	Proposed Model
Dimensionality	Fixed (time, org_unit, category)	Dynamic (metadata-driven)
Aggregation	Predefined hierarchies	Virtual cubes + on-demand rules
Schema Changes	Manual	Metadata-driven automation
Data Element Reuse	Limited	Decoupled via document/graph storage

---

An excellent research link on warehouse modeling, indexed and categorized by subject. It covers topics that might help me learn more about what I need.

The Data Warehouse Toolkit, by Ralph Kimball

— –
Giving it sometime later, inshallah!

[Hamza-ye]

Upcoming Desktop Version for operation room team review and approve:

[Hamza-ye]

There have been significant changes to both the back end and the APP including core components. first I need to test the basics thoroughly, then I’ll deploy the new backend version, followed by the APP update. I'll introduce advanced features gradually to avoid breaking things or overwhelming our users in the field —constant complaints can be very discouraging

[Hamza-ye]

integrating new ideas (adapted from other systems with some custom changes)

Core Concepts

1. FlowType

   • Defines the shape of a process:

     • planningMode (PLANNED | LOG_AS_YOU_GO)
     • submissionMode (SINGLE | MULTI_STAGE)
     • Scopes (ScopeDefinition, dimensions like team, orgUnit, date, optionally entityInstance)
     • Stages (a StageDefinition each linked to a formTemplate, repeatable flag, optional entityBinding)

   • Example (Receive Inventory Flow type)

2. FlowInstance

   • A runtime instantiation of a FlowType, with:

     • scopeInstance (map of the chosen team/orgUnit/date/… values)
     • status (PLANNED → IN_PROGRESS → COMPLETED | CANCELLED)
     • stageStates (map of stageId → list of submissionIds, for repeatable stages)

3. StageSubmission

   • One row per form submit, tied to a FlowInstance and (optionally) a StageDefinition.
   • ScopeInstance if the stage definition define an entity-bound, or it would be scoped only by parent scopeInstance
     elements.

4. EntityInstance

   • Spawned only when a stage or a stage's form section is entity-bound; upserted post-submission and linked back
     to its FlowInstance (and stageInstance).

5. ScopeInstance

   • every flow would at least be scoped by ORGUNIT, and DATE, other optional scoping elements: TEAM, ACTIVITY,
     ENTITY.
   • A single, shareable record that captures “all the context” for either a whole flow or an individual stage
     submission.
   • with the introduction of stages, scoping a stage row is sometime needed for easier querying and reporting.

---

Some Key Details About Scoping

Formal Definition:

ScopeDefinition → ScopeInstance can be the context of a data, the something(s) that a flow, or stage(s) are
associated with and grouped by, can be one or more of:

• A system core entity ORGUNIT (location), USER, TEAM, ACTIVITY.
• An ENTITY restricted to a certain entity_type_id.
• A DATE.
• Or a free typed-attribute like INVOICE_No, DATE....

Scope Instance Table Fields:

• flow_instance_id: always points back to the parent flow.
• stage_submission_id: null when this SI was created at flow start; set when created by a stage.
• A JSONB scope defined dims scopeData (entity_instance_id, team_id, org_unit_id, date…).
• Why it helps
  • Single join point for any reporting or grouping you do by “scope.”
  • You never need to remember “is the entity in FlowInstance.scopes or StageSubmission.scopes?” — everything is in
    one table.
  • Easy to query: “show me all stage submissions for Household HH123” ⇒ join stage_submissions → scope_instances
    where scope_data->>entity_instance_id = HH123.

2. How It Works in Each Scenario

2.1 One‐Off Form

• On Start:

  • Create FlowInstance (FI-001).
  • Create ScopeInstance (SI-001) with flow_instance_id = FI-001, no stage_submission_id linking back =null
    and a scope_data JSONB with the least defined scope dims, no entity_instance_id (unless the flow’s scope
    deliberately included an entity).

2.2 Multi‐Stage, No Repeats

• Flow Start: SI-001 created.

• Each Stage Submit:

  • SS-N is created. stageSubmission.si_id=null
  • No new SI: SS-N reuses SI-001 (because scope hasn’t changed).

2.3 Multi‐Stage, Entity‐Bound at One Stage

• Flow Start: SI-001 usual.

• Stage “Enroll”: for each repeated submission:

  1. Create SS-2a.
  2. Create new SI-002 with flow_instance_id=FI, stage_submission_id=SS-2a, and
     scope_data={"entity_instance_id": P001}.
  3. Next repeat ⇒ SI-003 for P002, etc.

2.4 Planned Visit to Existing Entity

• Flow Start: FI linked to existing ScopeInstance SI-001 with scope_data={entity_instance_id=HH999}.
• Stage 1: SS-001 is scoped by SI-001.
• No extra SI needed unless a stage binds another entity (e.g. enrolling a member).

2.5 Ad‐Hoc (“Log‐As‐You‐Go”)

• FlowInstance created on‐demand ⇒ SI-001 created at same time.

• Stage Submission ⇒ SS-1001 reuses SI-001.

• When to create

  • Always on flow start (tie the flow’s scope).
  • Also when a stage binds a new entity → new SI with stage_submission_id.

• How to link

  • On each submit, your SubmissionProcessor finds the current SI for that flow_instance_id and either updates
    it (if no stage entity-binding) or creates a new one (if stage has an entityBinding).

---

Example OF WorkFlow Configuration “Receive Inventory”:

1. User sees “Receive Inventory” on their FlowInstance List

   • If planningMode = PLANNED, in this flowInstance may have been created ahead of time by a warehouse manager for
     June 1 deliveries.
   • If LOG_AS_YOU_GO, the warehouse staff just taps “New Receive Transaction” and a fresh FlowInstance is created.

2. FlowInstance Detail

   • Flow Instance Scope Capturing (a recording of a receipt): “Record Purchase/Donation scope Instance info”

     • Form fields:
       • supplierId (EntityType “Supplier” dropdown)
       • invoiceNumber (text)
       • receiveDate (date)
       • teamId (automatically injected, current user’s team) or select
       • orgUnitId (warehouse location for this assignment)
     • On “Save,” we insert a FlowInstance row and ScopeInstance row.
     • No EntityInstances created, just suppliers are selected from a pre-existing entities.
     • orgUnit, and team are a system , invoiceNumber and receivedDate are just elements entered or scanned.

   • Stage 1: “Unpack & Quality Check” (repeatable = true)

     • Form fields:

       • itemId (EntityType “Item” dropdown)
       • batchNumber (text)
       • expirationDate (date)
       • quantityReceived (number)
       • qualityStatus (Good/Damaged)

     • Here this Stage is marked *
       entity-bound to “ItemBatch”* (or simply “Item entity”) →

       • Each time the user taps “Add Item,” we open the form, then on save we insert a new StageSubmission (with
         Json data: stageDefinitionId = unpackStage.id, itemId, batchNumber, expirationDate,
         quantityReceived, qualityStatus) and a new ScopeInstance with entity_instance_id=itemId.

   • Stage 3: “Store in Warehouse”

     • Single‐submission stage:

       • Form fields:

         • storageLocationId (EntityType “Location” or simple dropdown)
         • Optionally, a multi‐select of which EntityInstance items (from Stage 2) go to that location—though
           we can also assume “all from Stage 2” if our process dictates.

       • On “Save,” we write StageSubmission(stageId=storeStage.id) and, if needed, update those same
         EntityInstance records with properties.storageLocationId = <chosenLocation>.

INSERTS SAMPLES

Below is a concrete Warehouse Inventory example showing how we’d model Receive, Issue, and Discard flows
with the metadata‐driven engine. It includes:

1. EntityType definitions for the core “things” (Item, Supplier).
2. FlowType definitions for the three key workflows.
3. Example FlowInstance JSON for each.
4. Sketches of StageSubmission and EntityInstance behavior.

---

1. EntityType Definitions

1.1. Item

// POST /api/entity-types
{
  "id": "itemEntityDefinitionId",
  "displayName": "Inventory Item",
  "attributes": [
    { "id":"itemId",        "type":"string", "required":true },
    { "id":"itemName",      "type":"string", "required":true },
    { "id":"unitOfMeasure", "type":"string", "required":true }
  ]
}

1.2. Supplier

{
  "id": "supplierEntityDefinitionId",
  "displayName": "Supplier",
  "attributes": [
    { "id":"supplierId",   "type":"string", "required":true },
    { "id":"supplierName", "type":"string", "required":true }
  ]
}

but notice here Supplier and Item 's entity instances of this definition are already on the system and will be fetched
for selection, unless this is used on a registration form.

2. FlowType Definitions

2.1. Receive Inventory Flow type

// POST /api/flow-types
{
    "id": "receiveInventory",
    "name": "Receive Inventory",
    "planningMode": "PLANNED",
    "submissionMode": "MULTI_STAGE",
    "scopes": [
    { "key":"team",    "type":"TEAM",     "required":true,  "multiple":false },
    { "key":"orgUnit", "type":"ORG_UNIT", "required":true,  "multiple":false },
    { "key":"date",    "type":"DATE",     "required":true,  "multiple":false },
    { "key":"invoiceNumber",    "type":"NUMBER",     "required":true,  "multiple":false },
    { "key":"supplier","type":"ENTITY",   "required":true,  "multiple":false, "entityTypeId":"Supplier" }
  ],
  "stages": [
    {
      "id":"unpackCheck",
      "name":"Unpack & Quality Check",
      "sortOrder": 2,
      "formTemplateId":"unpackForm",
      "repeatable":true,
      "entityBoundEntityTypeId":"Item"
    },
    {
      "id":"storeItem",
      "sortOrder": 3,
      "name":"Store Items",
      "formTemplateId":"storeForm",
      "repeatable":false
    }
  ]
}

2.2. Issue Inventory

{
  "id": "issueInventory",
  "name": "Issue Inventory",
  "planningMode": "PLANNED",
  "submissionMode": "MULTI_STAGE",
  "scopes": [
    { "key":"team","type":"TEAM","required":true,"multiple":false },
    { "key":"orgUnit","type":"ORG_UNIT","required":true,"multiple":false },
    { "key":"date","type":"DATE","required":true,"multiple":false }
  ],
  "stages":[
    {
      "id":"pickItems",
      "name":"Pick Items",
      "formTemplateId":"pickForm",
      "repeatable":true,
      "entityBoundEntityTypeId":"Item"
    },
    {
      "id":"validateRecipient",
      "name":"Validate Recipient",
      "formTemplateId":"recipientForm",
      "repeatable":false
    },
    {
      "id":"finalizeIssue",
      "name":"Finalize Issue",
      "formTemplateId":"issueForm",
      "repeatable":false
    }
  ]
}

2.3. Discard Inventory

{
  "id": "discardInventory",
  "name": "Discard Inventory",
  "planningMode": "PLANNED",
  "submissionMode": "SINGLE",
  "scopes":[
    { "key":"team","type":"TEAM","required":true,"multiple":false },
    { "key":"orgUnit","type":"ORG_UNIT","required":true,"multiple":false },
    { "key":"date","type":"DATE","required":true,"multiple":false }
  ],
  "stages":[]
}

---

3. Example FlowInstance & Submissions

3.1. Receive FlowInstance

1. FlowInstance: (two tables)

   • Table 1 (FlowInstance=FI-6001):

     POST /api/flow-instances
     {
       "flowTypeId":"receiveInventory",
       "flowInstanceId": "FI-6001",
       "scopeInstance": { // "SI-1001", one to one relation to its scope (see next)
         "scopeDate": {"team": "teamA", "orgUnit":"warehouse1", "date":"2025-06-20", "invoiceNumber":"INV001", "supplier":"supplierX" }
       }, 
     }

   • Table 2 (FI-6001 FlowInstance's ScopeInstance=SI-1001):

     {
         "scopeInstanceId": "SI-1001",
         "flowInstanceId": "FI-6001",
         "stageSubmissionId": null,
         "scopeData": { "team":"teamA", "orgUnit":"warehouse1", "date":"2025-06-20", "invoiceNumber":"INV001", "supplier":"supplierX"}
     }

2. Unpack & Quality Check (repeatable, entity‐bound) scopes only kicks in if there are an entity.

   • Entry 1:
     • Table 1 (StageSubmission=SS-1001):

       POST /api/stage-submissions
       {
         "stageDefinitionId": "unpackCheck", 
         "flowInstanceId": "FI-6001",
         "stageSubmissionId": "SS-1001",
         "formTemplateId": "unpackForm",
         "scopeInstance": { // `SI-1002`, one to one relation to its scope (see next)
             "scopeDate": {"itemId":"IT100"} 
         } 
         "data":{ "batchNumber":"B100","qty":300,"quality":"Good" } // submission's jsonb data
       }

     • Table 2 (SS-1001 StageSubmission's ScopeInstance=SI-1002):

       {
         "scopeInstanceId":"SI-1002",
         "flowInstanceId":"FI-6001",
         "stageSubmissionId":"SS-1001", // unpack
         "scopeData":{ "itemId":"IT100"}
       }

   • Entry 2: same for next batch=SS-xxxx.

3. Store Items

   • Table 1 (StageSubmission=SS-2002) :

     POST /api/stage-submissions
     {
       "flowInstanceId":"FI-6001",
       "stageSubmissionId": "SS-2002", 
       "stageDefinitionId":"storeItem",
       "scopeInstanceId": "SI-2002",
       "formTemplateId":"storeForm",
       "scopeInstance": null, // (null means: no soping of its own, and scoped by its parent flow scopeInstance)
       "data":{ "storageLocation":"ShelfA","notes":"Completed" }, // submission's jsonb data
     }

   • Table 2 (SS-2002 StageSubmission's ScopeInstance) no upsert (means: scoped by its parent flow scope): (unless
     storageLocation is of entity type)

     → marks FlowInstance.status = COMPLETED

---

4. Issue FlowInstance

1. FlowInstance

   • Table 1 (FlowInstance=FI-7001):

     POST /api/flow-instances
     {
       "flowTypeId":"issueInventory",
       "flowInstanceId": "FI-7001",
       "scopeInstance": { // SI-2001, one to one relation to its scope (see next)
            "scopeData": {"team":"teamB", "orgUnit":"warehouse1", "date":"2025-06-21"} }
     }

   • Table 2 (FI-7001 FlowInstance's ScopeInstance=SI-4001):

     {
         "scopeInstanceId": "SI-4001",
         "flowInstanceId": "FI-7001",
         "stageSubmissionId": null,
         "scopeData": { "team":"teamB", "orgUnit":"warehouse1", "date":"2025-06-21" }
     }

• Pick Items: repeat to select batches/quantities → upsert Item ScopeInstance linked to this stage.
• Validate Recipient → submission with recipient info.
• Finalize Issue → final submission, then status = COMPLETED.

---

5. Discard FlowInstance

• Table 1 FlowInstance=FI-8001:

  POST /api/flow-instances
  {
    "flowTypeId":"discardInventory",
    "flowInstanceId": "FI-8001",
    "scopeInstance": { // one to one relation to its scope 
        "scopeData": { "team":"teamA", "orgUnit":"warehouse1", "date":"2025-06-22" } 
    }
  }

• Table 2 FI-8001 FlowInstance's ScopeInstance=SI-xxx: goes the same as above

How This Scales to Other Warehouse Flows

• Transfer Inventory: define a transferInventory FlowType with scopes including fromOrgUnit and toOrgUnit.
• Returns or Repairs: bind stages to an ItemBatch or Equipment EntityType for lifecycle tracking.

Model Diagram

erDiagram
    FLOW_TYPE ||--|{ SCOPE_ELEMENT: has
    FLOW_TYPE ||--|{ STAGE_DEFINITION: has
    FLOW_TYPE ||--o{ FLOW_INSTANCE: instantiates
    FLOW_INSTANCE ||--o{ STAGE_SUBMISSION: has
    FLOW_INSTANCE ||--|| SCOPE_INSTANCE: has
    STAGE_DEFINITION ||--o{ STAGE_SUBMISSION: defines
    DATA_TEMPLATE ||--o{ STAGE_DEFINITION: powers
    STAGE_SUBMISSION ||--o| SCOPE_INSTANCE: has
    DATA_TEMPLATE ||--o{ DATA_ELEMENT: has
    ENTITY_DEFINITION ||--o{ ENTITY_INSTANCE: defines
    SCOPE_INSTANCE ||--o| ENTITY_INSTANCE: has
    SCOPE_INSTANCE ||--o| ACTIVITY: has
    SCOPE_INSTANCE ||--o| ORG_UNIT: has
    SCOPE_INSTANCE ||--o| TEAM: has
%% Table-entity definitions
    FLOW_TYPE {
        String id PK
        String activity_definition_id FK
        String name
    %% {}
        List scopeElementDefinitions
    %% PLANNED LOG_AS_YOU_GO
        Enum planning_mode
    %% SINGLE MULTI_STAGE
        Enum submission_mode
        Timestamp created_at
    }
%% Definition of a scope element
    SCOPE_ELEMENT {
        String id PK
        String key
    %% valueType.ORG_UNIT, valueType.TEAM, valueType.ACTIVITY, valueType.ENTITY, valueType.DATE, ...
        enum valueType
    %% when valueType.ENTITY, provide the id of its type, e.g. "Household", "Patient"
        String entityTypeId
        Timestamp created_at
    }
    STAGE_DEFINITION {
        String id PK
        String flow_type_id FK
        String name
        String data_template_id FK
        Boolean repeatable
        Integer stage_order
    %% if the
    %% where entity fits better, option1: bound entity to a stage instance in flow instance level
        String entity_definition_id FK
        Timestamp created_at
    }
    DATA_TEMPLATE {
        String id PK
        String name
    %% your existing template JSON (DataElements)
        List dataElements
        Timestamp created_at
    }
%% optional pre-created and pre-link scope for planned flow instances
    FLOW_INSTANCE {
        String id PK
        String flow_type_id FK
        Date flow_instance_date
    %% entity_instance_id {team: team_id, orgUnit: ou_id, entity: ei_id, ...}
        String scope_instance_id FK
    %% { stageId: [...submissionIds] }
        JSONB stage_states
    %% PLANNED, IN_PROGRESS, COMPLETED, CANCELLED
        Enum status
        Timestamp created_at
    }
%% default implicit single stage if no stages
    STAGE_SUBMISSION {
        String id PK
        String flow_instance_id FK
        String stage_definition_id FK
        String data_template_id FK
    %% nullable if scoped by parent
        String scope_instance_id
        JSONB data
    %% PENDING, SUBMITTED, REJECTED
        Enum status
        Timestamp submitted_at
    }
    SCOPE_INSTANCE {
        String id PK
        String flow_instance_id FK
    %% null if a scope is of flow instance
        String stage_submission_id FK
    %% a map of scope elements values
        JSONB scopeData
        Timestamp submitted_at
    }
    ENTITY_DEFINITION {
        String id PK
    %% e.g. "Household", "Patient"
        String key
        String display_name
    %% list of { id, type, required }
        JSONB attributeDefinitions
        Timestamp created_at
    }
    ENTITY_INSTANCE {
        String id PK
        String entity_definition_id FK
    %% e.g id, name, code
        JSONB entityAttributeValues
        Timestamp created_at
        Timestamp updated_at
    }
    DATA_ELEMENT {
        String id PK
        String name
        Enum valueType
    }
%% System entities
    ACTIVITY {
        String id PK
        String name
        String description
    }

    ORG_UNIT {
        String id PK
        String name
        String description
    }

    TEAM {
        String id PK
        String name
        String description
    }

Loading