> For the complete documentation index, see [llms.txt](https://docs.pdap.io/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://docs.pdap.io/meta/operations/staff/meeting-minutes/project-home-2021-03-27-database-working-session.md).

# 2021-03-27 database working session

## Date <a href="#id-2021-03-27databaseworkingsession-date" id="id-2021-03-27databaseworkingsession-date"></a>

27 Mar 2021

## Participants <a href="#id-2021-03-27databaseworkingsession-participants" id="id-2021-03-27databaseworkingsession-participants"></a>

* [Eddie Brown (Unlicensed)](https://pdap.atlassian.net/wiki/people/5fd63e354d2179006ecbcb80?ref=confluence)
* [Alec Akin](https://pdap.atlassian.net/wiki/people/60319bf02a42cc0069af9ac8?ref=confluence)
* Mitch Miller
* [Josh Lintag](https://pdap.atlassian.net/wiki/people/5f20c61fc9c094001c5d32ca?ref=confluence)
* Kristin Tynski
* Jeff Jockisch
* [Richard Ji](https://pdap.atlassian.net/wiki/people/5f8f95be0e068b00766b6903?ref=confluence)
* [Former user (Deleted)](https://pdap.atlassian.net/wiki/people/5f8f95be40588b0077ed830a?ref=confluence)

## Goals <a href="#id-2021-03-27databaseworkingsession-goals" id="id-2021-03-27databaseworkingsession-goals"></a>

## Discussion topics <a href="#id-2021-03-27databaseworkingsession-discussiontopics" id="id-2021-03-27databaseworkingsession-discussiontopics"></a>

| Item                                                                                                                                                                                                                                                                                                                                                | Notes                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  |
| --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| <p>dolt</p><ul><li><p>Review the DoltHub proof of concept for <a href="https://www.dolthub.com/repositories/pdap/datasets">dataset catalogue</a></p><ul><li>does not yet include sources from @stabs or form submissions</li><li>still need to remove the form and point people to dolthub, if this proof of concept looks good</li></ul></li></ul> | <ul><li>why is dolt better than a daily backup?</li><li>lowers barrier to entry → can work without much automation or maintenance.</li><li>documents chain of custody.</li><li>overhead—how much time do people spend managing commits and PRs vs. <strong>actually scraping</strong>?</li><li><strong>ease of use for contributors</strong> is a primary objective if we’re going to scale</li><li>scale—we’re building a unique database of databases in addition to scraped data</li><li>free</li><li>dolt can be used to identify & track sources → the translated data could be sent to</li><li>bounties only work for data, not scrapers/infrastructure.</li><li>bounties allow people to contribute with minimal social engagement.</li><li>Using Dolt as a POC for storing data in a version controlled fashion</li><li>How do we scale this?</li><li>Resolving version control may slow things down</li><li>Automatic scrapers pushing into a branch to merge to master becomes a bottleneck; not applicable atm</li><li>Edit history is tracked</li><li>Why dolt instead of daily backups? Or our own history tables?</li><li>Dolt can be used as a little more than an audit log for data sources</li></ul> |
| <ul><li>Do we need a global table for <code>unique\_data\_properties</code> → we make SQL <code>INDEX</code>es of that based on <code>data\_types</code>? Or are we better off creating <strong>tables</strong> for each <code>data\_type</code>? How do we decide?</li></ul>                                                                       | <ul><li>We should identify which data points are most consistently available across data sources → this gives us targets to hit.</li><li><p>metadata fields always need to be added in data discovery</p><ul><li>parent child relationship between types and properties</li><li>the query will be ugly but this is how it’s best designed for large code</li></ul></li><li><p>mongodb may work as a metadata repository</p><ul><li>we’d need something to talk to both</li></ul></li></ul>                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             |
| <ul><li><p>Define the next steps for keeping structure parity between Datasets, Data, and Scrapers</p><ul><li>How should these structures relate to one another?</li></ul></li></ul>                                                                                                                                                                | <ul><li><strong>Scrapers need a config file for where they’re sending data</strong></li><li><strong>Dolt repos may need a path back to the scraper</strong></li><li>dolthub ≠ where people provide scraper code</li><li>Tiered approach for data properties: 1. NIBRS 2. Store CSV…anything in between?</li><li>We can decide which tiers we’re actively ingesting based on how often they’re available.</li></ul>                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     |
| Enterprise features / structure down the road                                                                                                                                                                                                                                                                                                       | <ul><li>Risk: github or dolthub fails</li><li>We’ll want a backup DB, then an API.</li><li>Sooner rather than later we need to back up our data for better disaster recovery.</li></ul>                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                |
| Documentation: read the docs vs confluence                                                                                                                                                                                                                                                                                                          | <ul><li>Richard is writing documentation for backend design & workflows</li><li>it’s in markdown</li><li>Confluence is a stopgap for collaborating and getting on the same page</li><li>What does implementation look like?</li><li><p><a href="https://readthedocs.org/"><https://readthedocs.org/></a></p><ul><li>Does it work for open source?</li></ul></li></ul>                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  |
| People bringing drop-in scrapers                                                                                                                                                                                                                                                                                                                    | <ul><li>Scrapers (the humans) often bring their own scraper library with them → we should enable them to slot in to the extent it’s possible</li><li><p>Messaging scrapers: <strong>what are we targeting</strong>?</p><ul><li>We need to make sure we’re clear about the breadth / scope of data.</li></ul></li><li>Fragmenting the target makes it hard for us to progress. We want to focus on NIBRS format data to validate it against what’s available from the FBI & broaden context</li></ul>                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   |
| data to not collect                                                                                                                                                                                                                                                                                                                                 | <p>At one point this was our list, is it accurate?</p><ul><li>CaseNum</li><li>FirstName</li><li>MiddleName</li><li>LastName</li><li>DOB</li><li>DefenseAttorney</li><li>PublicDefender</li><li>Judge</li><li>ArrestingOfficer</li><li>ArrestingOfficerBadgeNumber</li></ul><p><strong>Consider:</strong> not collecting data we don’t know is legal to have.</p><ul><li>the government made the data public. If it’s public, it’s not considered personal.</li><li>We need to be careful <strong>which source</strong> the data is coming from. If it’s not a direct source, we may be subject to different restrictions. Will third party aggregators have</li><li>With the bounty program we can mandate that they include certain proofs in their submission</li></ul><p><em>Whenever we decide on a property to collect, we need to justify it / provide rationale for the decision</em></p><ul><li><a href="https://www.lawfareblog.com/understanding-supreme-courts-carpenter-decision"><https://www.lawfareblog.com/understanding-supreme-courts-carpenter-decision></a></li></ul>                                                                                                                              |
| Are we collecting data aggregated by third parties?                                                                                                                                                                                                                                                                                                 | <ul><li>Each aggregator has their own format</li><li>Do we want to throw it away?</li><li>Discrepancies in the data aren’t necessarily “red flags”; agency’s maybe aggregating according to different criteria so there maybe discrepancies according to that unknown criteria</li><li>We do want aggregate level data for validating the record level data that is being returned to us</li><li>For now these are stored as <code>source\_type = “third\_party”</code></li></ul>                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      |
| Data types to prioritize                                                                                                                                                                                                                                                                                                                            | <ul><li>arrest reports</li><li>traffic stops</li><li>incident reports</li></ul>                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        |
| Scraper approval                                                                                                                                                                                                                                                                                                                                    | <ul><li>Multi-tier approval is possible with github</li><li>How does wikipedia do it?</li><li>Base scraper approval is most urgent</li></ul>                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           |
| ETL framework                                                                                                                                                                                                                                                                                                                                       | <ul><li>Should extraction / ETL be a required part of a scraper?</li><li>Mitch Miller is using python to create a framework which should be flexible enough to meet our needs</li><li>ETL should not go directly into the scraper, but it does need to be closely related.</li></ul>                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   |

## Action items <a href="#id-2021-03-27databaseworkingsession-actionitems" id="id-2021-03-27databaseworkingsession-actionitems"></a>

* [Former user (Deleted)](https://pdap.atlassian.net/wiki/people/5f8f95be40588b0077ed830a?ref=confluence) find out how much overhead (time) is involved in an end to end dolt scrape → public
* [Former user (Deleted)](https://pdap.atlassian.net/wiki/people/5f8f95be40588b0077ed830a?ref=confluence) add data format (NIBRS) column to dataset catalogue
* [Former user (Deleted)](https://pdap.atlassian.net/wiki/people/5f8f95be40588b0077ed830a?ref=confluence) Identify minimum data properties to meet NIBRS data format, has this somewhere already<https://pdap.atlassian.net/browse/PDAP-118>
* [Former user (Deleted)](https://pdap.atlassian.net/wiki/people/5f8f95be40588b0077ed830a?ref=confluence) Document data\_types priority [in scrapers readme](https://github.com/Police-Data-Accessibility-Project/Scrapers/pull/43). Arrest Reports, Traffic Stops, Incident reports. What’s most available. What most easily paints the full picture. Tiers. <https://pdap.atlassian.net/browse/PDAP-119>
* [Former user (Deleted)](https://pdap.atlassian.net/wiki/people/5f8f95be40588b0077ed830a?ref=confluence) validate workflow: **localized, raw data is stored in dolt → it could be aggregated / ETL’d to a centralized server.** dolt is the audit/transparency.
* [Former user (Deleted)](https://pdap.atlassian.net/wiki/people/5f8f95be40588b0077ed830a?ref=confluence) Expose basic roadmap in documentation with backup DB → API
* [Josh Chamberlain](https://pdap.atlassian.net/wiki/people/6068f9e790e3950069fbaaf4?ref=confluence) Draft a policy and rationale for “fields not to collect” → **A\&P**
* [Josh Chamberlain](https://pdap.atlassian.net/wiki/people/6068f9e790e3950069fbaaf4?ref=confluence) Draft a policy and rationale for mirroring dataset websites → **A\&P**
* [Former user (Deleted)](https://pdap.atlassian.net/wiki/people/5f8f95be40588b0077ed830a?ref=confluence) import dataset catalogue [form submissions](https://docs.google.com/spreadsheets/d/176f0pTxlIyvBWqXmJCPPlh2zRrcMZNgojB2fJOBBhGw/edit#gid=901781374) and deprecate form
* [Richard Ji](https://pdap.atlassian.net/wiki/people/5f8f95be0e068b00766b6903?ref=confluence) Get @stabs [base scraper](https://github.com/Police-Data-Accessibility-Project/Scrapers/pull/38/files) approved

**Mitch Miller** is making an ETL framework

<https://pdap.atlassian.net/browse/PDAP-113>

<https://pdap.atlassian.net/browse/PDAP-114>

## Decisions <a href="#id-2021-03-27databaseworkingsession-decisions" id="id-2021-03-27databaseworkingsession-decisions"></a>

* @stabs need to be recognized. Let’s be sure to celebrate their hard work