Create process for synchronizing changes with external databases

[fancyham]

Overview

Some Food Oasis regions will have multiple partners who will provide data sources for listings.

One question then becomes how to import changes from our partners, and how our partners can update their databases with the latest Food Oasis listing info (for example if a correction has been made)

We need a way to process and synchronize changes when listing data lives both on Food Oasis’s database as well as an external database.

For example: In Hawaii, we are initially starting with one data source (Aloha Harvest) and Food Oasis will be showing data imported from there and AH’s database will be the ’source of truth’.

In the future, however, as we add additional listings and data sources, AH’s database will no longer be the source of truth.

In fact, crowdsourced corrections made to FO might need to be replicated to AH's and other databases.

In this case, is FO the source of truth?

Let’s define some processes to find changes and to sync data sources with each other.

Action Items

• [ ] Discussion about replication needs, architecture, and tradeoffs
• [ ] Process for replicating changes from an external database to FO
• [ ] Process for replicating changes from FO to external databases
  • [ ] One possibility: Automatically send a summary email to our partners with listing updates
• [ ] Consistency check to validate that data is replicated correctly between FO and other databases

Resources/Instructions

https://en.wikipedia.org/wiki/Single_source_of_truth

[fancyham]

One way is to find changes in a source database and manually update the destination database with any changes.

For our initial situation in Hawaii, this makes a lot of sense since Aloha Harvest is our ’source of truth’ until we start adding other listing data providers to the mix.

The process would be:

1. Enter all accurate data in source-of-truth (SOT) database first
2. Export SOT database and timestamp it. This is a dated snapshot.
3. Compare spreadsheets to find changes since the last snapshot
4. Replicate those changes on the Food Oasis website via the org editing screens
5. Save the most recent timestamped SOT database snapshot for future use.

This is great if done perfectly, but there may be minor mistakes over time. We will want to find a way to compare FO to the SOT database.

Finding differences between database snapshots:

There are some tools ($$$) that do what programmers call ‘diffing’ (named after a program called ‘diff’ that finds differences between two documents).

But fortunately, I think that the free Google Sheets can help do the task of comparison.

“Show differences between Sheet1 and Sheet2"

Import two spreadsheets into this Google Sheets document and it will indicates where the two sheets are different. Perfect for comparing old and new CSV files.

Save a copy and give it a try: https://docs.google.com/spreadsheets/d/1adhHjgh9IGpvcPTj4LWZNduCFFA8aHwxFOvaR8IAJ18/edit?usp=sharing

The formula for the cells is =IF(Sheet1!C2 <> Sheet2!C2, Sheet1!C2 & " | " & Sheet2!C2, "=")

More info: https://www.wikihow.com/Compare-Two-Sheets-in-Google-Sheets

Useful for this project but probably lots of other ones as well.

There may be web versions of this tool that may be useful or even easier, but be sure to read the privacy notices: diff compare csv https://www.google.com/search?q=diff+compare+csv

[staceyrebekahscott]

Moving issue to the Icebox, as partnership with Aloha Harvest is not active at this time.

Increasing collaboration with the other Food Oasis locations is medium/ long term goal.

[staceyrebekahscott]

Moving to PM board Backlog. Will need further investigation once we start working with other FO websites/ locations.

[staceyrebekahscott]

This issue seems to be concerned with how other locations of Food Oasis are managing their listings. Currently only Food Oasis Los Angeles is Active. And since there will be changes happening to the Brigade network, the future of other sites is in flux. Therefore, I do not think we need to be concerned about this unless we have a clear path forward on working with the other Food Oasis sites. This will depend on whether those Brigades continue to function.

As this issue is over a year old (Nov 2021) with no action, I am closing this as Not Planned.

[fancyham]

While the LA region has a team of validation volunteers, not all regions will have this.

Many will have their own database or spreadsheet already, so it’d be best if there’s an easy way to import their listings into our Food Oasis database, rather than do it manually.

This will allow Food Oasis to work in other regions beyond LA.

Recovering this issue.

[farahrobleh]

@entrotech @fancyham Moved this issue to New Issue Approval, seeking clarity on why we need to sync with existing partners?

[ExperimentsInHonesty]

Create private API for partners to input data. Will require authorization tokens.