[LNO] Assist with Personnel Updates Workflow

[njlyon0]

Summary

Site leads & IMs need to be able to update their personnel in both (A) the LTER website--on Wordpress--and (B) relevant Google Groups. This is currently done in Airtable but we may want to pivot to a different system (Airtable is a paid service so getting a free variant would be a plus). Whatever alternate system is used--if any--would need to be equally accessible to all users regardless of technical expertise though so the solution cannot be an extremely technical one. My role would be helping out with exploring an alternate system (possibly building an API) that someone other than me could take over maintenance in the long term

Starting Tasks

• Discuss overview with Marty (see 4/21/25 SciComp notes for specifics)
• Read through existing Airtable API docs
• Discuss in greater detail with Marty on 5/12

Useful links

• TBD

[njlyon0]

Update

Problem is partially with WordPress data structure interaction with Airtable. Reading more about WP will then help me get informed on this subject.

From Marty:

The WordPress codex is the most authoritative background on WordPress data structures. The key tables are wp_users, wp_usermeta, wp_posts, wp_postmeta. The actual use of the tables are modified a bit by a few key plugins that we use: ACF Pro and Toolset.

[njlyon0]

WP Codex Key Take-Aways

WordPress primarily uses MySQL under the hood. The codex had a nice table schema as well as good/brief descriptions of each table (I have screencaps on my computer)

[njlyon0]

Goal:

From Marty:

Ultimately, we want a way for site IMs (or admins) to send or upload a spreadsheet of their current members with role, email, and start year. Then we should have a scripted process that:

1. Checks that list against our existing personnel list (people often change sites, but the new IM is not aware that they are already in the database)
2. If the person doesn't exist, a new person (member) should be added and a new affiliation created between that site and that person with the specified role and start-year (no end-year), status=current
3. If the person exists and the email is the same, only a new affiliation should be created.
   If the name is the same and the email is different or the name is very similar, but not identical, a flag should be thrown so that it can be checked.
4. If a person who was previously affiliated with that site is NOT on the list, an end-year should be added to that affiliation, equal to the first year in which they do not appear on the list. Status should be changed to former.
5. If a person's role is different than in the database, an end-year should be added to the existing role and a new affiliation should be created with the current role and current start-year.

To me, this sounds like a Shiny app + R package (or perhaps just one or the other) but I'll run that possible solution by Marty before seriously committing to that.

[njlyon0]

Goal Update

Because the WP-AT connection also empowers users to do other things, we can't move away from that framework entirely. Marty also gave me the following goal information:

The ideal would be a script that accepts csv files uploaded to airtable (through a form, which outsiders can access without paying extra), runs the checks and updates the base in airtable accordingly

Seems like an R package combined with a simple script that invokes those functions would be a maintainable way of handling this. Incidentally might be good to migrate our WG survey-processing functions/package out of GitHub Enterprise and the package we create for this task might be a good home for those. Need to double check with MD first though before doing that ancillary move.