Allow people to enter new Data Sources via a form

[josh-chamberlain]

The first version of this form will create entries in a no- or low-code database.

Known to-dos

• [x] josh and jacob to present experiments in Airtable, Harvester, etc
• [x] select one option to progress / make a plan for next steps
• [x] make a plan for agencies management as it relates to Data Sources
• [x] configure airtable db based on dolthub
• [x] migrate existing data from dolthub
• [x] make simple volunteer docs explaining data source creation
• [x] embed the approved data sources somewhere
• [x] iterate ?

Requirements

• [x] should meet requirements of https://github.com/Police-Data-Accessibility-Project/planning/issues/145
• [x] as a non-auth'd user I should be able to submit data sources for approval
• [x] some fields should be required
• [x] now / in future, must be able to relate form submission with table of police agencies

Nice to have

• [x] as logged in PDAP user I should be able to approve data sources
  • [x] approved data sources should be publicly visible
• [x] accept an optional email, in case people would like to be notified on their data source submission status

Properties to track

• name
• description
• url
• size
• date covered
• retention schedule
• number of records available
• how is it accessed?
  • location
    • url
    • publishing entity
    • data portal?
    • API?
  • web page
  • download
  • API
  • FOIA / RTK
  • restrictions
• format
  • structured
    • CSV
    • JSON
  • PDF
    • machine
    • scanned
  • video / image
  • physical
• how are they updated?
  • update rate?
  • replaced?
  • inserted?
  • how are they sorted?
• original recordkeeper
  • name
  • web site
  • geo
  • contact info
• data type (starting point)
• aggregation type
  • aggregated
  • individual
• date last updated
• metadata
  • is metadata available?
  • is a data dictionary available?
  • data dictionary URL?
  • metadata URL?
• agency covered

[josh-chamberlain]

Airtable demo

1. Navigate to the "Submit Data Sources" form: https://airtable.com/shrJafakrcmTxHU2i or https://pdap.dev/data-sources.html
2. Fill in and submit the form
3. Navigate to the Airtable Data Sources Base
4. Navigate to the Data Source Intake Queue to see your submission.
5. At the far right, observe "Approval Status" property. Add an Approval status to move the record to either Approved or Rejected Data Sources.
6. Observe publicly shared Approved Data Sources at https://pdap.dev/data-sources.html

If anything, Airtable is a v0.9 or beta—I'm sure we'll encounter limitations, even if it meets all the requirements. In the meantime, it has an API which, if it's worth it, we can use to hide the back end. Eventually, we'd need to replace Airtable with a proper database.

https://airtable.com/app473MWXVJVaD7Es/tblx8XaKnFTphWNQM/viwIlzibc9W3IEfHW?blocks=hide https://airtable.com/api

[josh-chamberlain]

Harvester demo

• Entry: https://harvester-demo-pdap.herokuapp.com/d/1wtSJ5ZdFlJHvLmMvmIOji8uGSAu1nqCPatmm-Af7dYo
• Result: https://docs.google.com/spreadsheets/d/1wtSJ5ZdFlJHvLmMvmIOji8uGSAu1nqCPatmm-Af7dYo/edit#gid=0

Risks

• Google can be weird about URLs
• Doesn't validate form entry for type (int vs. string, etc)

Differentiators

• Can use regex to enforce property structure

[josh-chamberlain]

Decision: Airtable!

Harvester would work, and so would something 100% custom. The plan: Josh will make a working beta for source collection in Airtable.

We're going to keep DoltHub as an option for future Data Bounties, but our schema for Data Sources is going to change and we're prioritizing easy addition of the data over git merge. We can always go back to DoltHub pretty easily.

[josh-chamberlain]

Solution

Airtable base here.

Ported from DoltHub Data Sources on 8/18/2022.

dolt archive 220818.zip

Known Airtable limitations

We'll want to address these when we move to a permanent home for data sources

• Not easy to allow people to enter "other" and add their own text response
  • Could do this with $80/mo MiniExtensions, which is a more fully-featured form for Airtable
• $24/user/mo Pro subscription required to set editing permissions
  • may bump into size limitations for relational db of agencies
  • 50,000 records allowed in database; this will become a ceiling
• Data Sources can't be easily linked to Agencies; within the form, it's only possible to search for Agencies by their primary field. Also, what if the Agency isn't there? The ideal workflow would be allowing people to 1. find an Agency using search filters like name, state, county, etc.; 2. add an Agency to our database if they can't find one; 3. submit a Data Source to that Agency. Starting from the Agency solves this problem.
• The Agencies table doesn't satisfactorily show the nested / parent-child relationship between Agencies, States, Counties, etc. (this is less about the database than the data itself)