Integrated Product Dashboard to monitor/track stock and sales across different (ongoing/closed) OCs and Hubs

[div-yansh-1]

Delivery Stage

• Initial proposal
• Agree implementation proposal
• Estimating
• Funding Confirmed
• Delivered

1. What is the need / problem?

Basically - as a supplier to multiple hubs, I need a dashboard to help me manage products (import/export), track my opening stock balance, add in more stock as things are ready in the field, track how much stock has sold in multiple order cycles (I'm thinking 3 OCs - 1 in hub A and 2 order cycles - current week + next week) in hub B.), and calculate how much of my opening + newly added stock is available for sale across these OCs.

Key requirements (added after convo with Theresa)

• Searchable product list (can be filtered/grouped)
• Opening Stock balance (at the start of the week) + updating stock balance few times per week
• Whats selling in which OC (ongoing OCs) (Critical)
• Functionality to add more stock/update stock

2. Which type of users does this problem affect (and how many, if known)?

Producers

3. What tools does this involve?

Airtable

4. Links to connected discussions

https://openfoodnetwork.slack.com/archives/C04U02X3806/p1681411299156449

5. Implementation Proposal

Proposed solution: Building a dashboard in Airtable with features mentioned below.
Method: Use Products API and Postgres DB calls via n8n to get all the relevant data and to run the automations/updates, while keeping all the data in Airtable to simplify searching, filtering, and editing the data.

Features:

• Searchable Product List: Using Products API we can get the product list for a producer in Airtable for the first time, which can be used to search, filter, or group products or variants
• Opening Stock Balance (regular updates): Running Products API at regular intervals to get updated product list and then matching the data obtained via the API call with the variants in Airtable. Intervals for running the update needs to be agreed upon.
• Whats selling in which OC (ongoing OCs): So this one is slightly tricky. We can use Postgres DB calls via n8n to access exchanges (linked with exchange variants, variants, OCs etc.) and get a list of variants selling in an order cycle. But this becomes tricky when a producer is selling products in multiple OCs at the same time. Mostly because of data structure and exchanges being not super clear.
  Therefore, I would like to propose a small test to check the accuracy of data.
  Test: A test producer selling some of their products across multiple test OCs (multiple hubs/single hub - either should be fine) at the same time. Then, developing and running Postgres query (using exchanges, exchange variants etc.) to get the list of variants being sold across different OCs for that producers and comparing the results. Next part would be to change the products being sold across OCs and see if the Postgres query is giving the updated results.
• Functionality to add more stock/update stock: Producer will update the stock value of variants in Airtable, which will then be updated on OFN using Products API (via n8n). This workflow can be started by Producer by clicking a button within Airtable. (Similar to what we have in Aus)

6. Estimates and funding partners

Estimate for test: 5-7 hours (a successful test would roughly complete 30% of the total workload for this issue).

Remaining tasks would involve setting up airtable dashboard, setting up n8n workflows (production), and testing with data from actual enterprises and hubs.

7. Link to Workflows, User Guide etc

[div-yansh-1]

@tschumilas as discussed in the call, I have added an estimate for the test.

[lin-d-hop]

So implementation plan:

1. 3-7 hours for confirming understanding an writing SQL queries
2. Depending on the findings above more there may well be more to do
3. 8-12 hours for airtable integration and finalising