# Data needed to populate the accountability tools These tools now use a **shared Cloudflare D1 database** (`indnh-dashboard-db`). Data entered by any logged-in user is persisted to the backend and visible to every other user. This document lists the fields, sources and collection methods for each tool. --- ## 1. FOIA / Right-to-Know Tracker ### Fields needed | Field | Example | Source | |:------|:--------|:-------| | Agency | NH Dept. of Corrections | Request recipient | | Topic / records sought | County jail staffing ratios, 2020–2026 | Reporter draft | | Requested date | Aug 4, 2026 | Outgoing request letter / email | | Due date | Aug 18, 2026 | Statutory deadline (5 business days in NH) | | Status | Open / Pending / Overdue / Closed / Appeal | Manual update | | Outcome / notes | Partial response expected | Reporter notes | ### Data sources - **Outgoing request log**: Reporter or editor records every Right-to-Know request sent. - **Agency response letters**: Scanned/email PDFs confirming receipt, granting, denying or extending. - **Calendar / deadline tracker**: Automatic deadline calculation from request date. - **Appeal records**: NH Office of Information Professional (OIP) appeal docket or internal appeal letters. ### Collection workflow 1. Reporter fills a request template and logs it on send. 2. Editor or reporter updates status when agency responds. 3. Overdue flag triggers automatically from due date. 4. Closed requests link to the document in the Public Records Database. ### Real data currently available - `systems/foia_tracker.csv` in the dashboard has a starting log. - Existing requests: Hillsborough jail incidents, NHDES PFAS data, Strafford medical logs. --- ## 2. Corrections Oversight ### Fields needed | Field | Example | Source | |:------|:--------|:-------| | Facility | Valley Street Jail | County government | | County | Hillsborough | Public record | | Capacity | ~430 | County budget / jail inspection report | | Staff / inmate ratio | 1 : 11 | FOIA request to sheriff / county administrator | | 2026 incidents | 9 | Incident reports obtained by FOIA | | Last inspection | May 2026 | NH Department of Corrections inspection records | | Status | Warning / Poor / Good | Editorial judgment from data | | Notes | Staffing complaint filed | Reporter notes | ### Data sources - **NH Department of Corrections**: Statewide jail inspection reports, population reports. - **County sheriffs / county administrators**: Staffing rosters, incident reports, death/injury logs. - **Court records**: Lawsuits, habeas petitions, wrongful-death filings involving county jails. - **Medical contractor records**: FOIA to county or contractor (Wellpath, NaphCare, etc.). ### Collection workflow 1. File identical FOIA request to all 12 county jails for population, staffing and incident data. 2. Standardize monthly or quarterly updates. 3. Compare facilities by ratio, incident rate and inspection findings. 4. Flag outliers for reporting. ### Real data currently available - Some incident reports requested from Hillsborough County. - Inspection dates known for several facilities from public sources. --- ## 3. Environmental Data ### Fields needed | Field | Example | Source | |:------|:--------|:-------| | Site / facility | Merrimack Station | Public record / reporting | | Town | Merrimack | Geographic lookup | | Type | PFAS / Water / Air / Soil | Reporter classification | | Contaminant | PFAS | Source document | | Agency | NHDES / EPA | Regulatory authority | | Last test | Aug 2026 | Test result document | | Status | Active monitoring / Exceeds advisory / Permit review | Editorial from data | | Notes | Long-running groundwater monitoring zone | Reporter notes | ### Data sources - **NHDES OneStop portal**: Permits, enforcement actions, test results. - **EPA ECHO (Enforcement and Compliance History Online)**: Facility compliance and enforcement. - **EPA Region 1**: Permit decisions, remands, public comments. - **State lab / municipal water systems**: Annual water quality reports (CCR). - **UNH researchers**: Eelgrass, microplastics, watershed data. ### Collection workflow 1. Build a master site list from NHDES permits and prior reporting. 2. Automate monthly scrape of NHDES enforcement / test result pages where possible. 3. Attach documents from FOIA responses and public databases. 4. Use status field to drive editorial calendar. ### Real data currently available - Manchester PFAS EAB remand decision. - NHDES PFAS Merrimack watershed test results (requested). - Pease Tradeport, Coakley Landfill prior coverage. --- ## 4. Transparency Scorecards ### Fields needed | Field | Example | Source | |:------|:--------|:-------| | Agency | NH Secretary of State | Target agency | | Requests received | 18 | FOIA tracker count | | On-time % | 94% | (On-time responses / total responses) × 100 | | Denied / redacted % | 6% | (Denied or heavily redacted / total) × 100 | | Appeal rate | 0% | (Appeals / total requests) × 100 | | Data published | Election data portal live | Reporter / public assessment | | Grade | A− | Editorial formula | | Notes | Consistent, fast responses | Reporter notes | ### Data sources - **FOIA tracker**: Raw counts of requests, responses, denials, appeals. - **Agency websites**: Whether data is proactively published. - **OIP decisions**: Appeal outcomes that affect an agency’s score. - **Editorial grading rubric**: Weighted formula combining timeliness, denials, appeals, proactive publication. ### Collection workflow 1. Each quarter, export request data by agency from the FOIA tracker. 2. Calculate percentages. 3. Assess whether each agency publishes relevant datasets proactively. 4. Assign grade and publish scorecard with methodology. ### Real data currently available - FOIA tracker has enough entries for a first-quarter snapshot. - Grading rubric needs to be drafted. --- ## 5. Public Records Database ### Fields needed | Field | Example | Source | |:------|:--------|:-------| | Document title | Manchester PFAS permit EAB remand decision | Document metadata | | Agency / source | EPA Environmental Appeals Board | Document source | | Date obtained | Aug 12, 2026 | FOIA tracker | | Category | Permit / Enforcement | Editorial taxonomy | | Tags | PFAS, Manchester, water | Reporter tags | | Download link | PDF (2.4 MB) | File storage URL | | Notes | Full remand decision; published with story | Reporter notes | ### Data sources - **Documents obtained by FOIA**: PDFs, spreadsheets, emails, audio, video. - **Publicly posted records**: Agendas, meeting minutes, permits, court filings. - **Scanned paper records**: Converted to searchable PDF. - **Data files**: CSV/Excel of test results, budgets, incident logs. ### Storage options for production | Option | Best for | Notes | |:-------|:---------|:------| | **Cloudflare R2** | Large document archive | S3-compatible, low cost, works with Pages Functions | | **Google Drive + public links** | Quick start | Easy upload, but less durable / searchable | | **Airtable** | Structured metadata + attachments | Fast to build, good for prototypes | | **Cloudflare D1 + R2** | Scalable production archive | SQL metadata + object storage | ### Collection workflow 1. Every closed FOIA request yields a document. 2. Upload file to archive (R2, Drive, etc.). 3. Create database record with title, agency, tags and link. 4. Surface documents in stories and in the searchable database. ### Real data currently available - Manchester PFAS EAB remand decision. - Hillsborough jail incident reports. - HB 323 voter registration guidance. - Strafford medical contractor logs. - NHDES PFAS test results. - Wheelabrator Claremont air permit draft. --- ## Suggested next steps for AJP prep 1. **Draft the grading rubric** for Transparency Scorecards. 2. **Send the standardized jail-conditions FOIA** to all 12 counties. 3. **Set up document storage**: Start with R2 or Drive, migrate to D1+R2 if scale grows. 4. **Build a simple intake form** (could be a Google Form or a Pages Function) so reporters can add FOIA requests without editing HTML. 5. **Connect the tools**: A closed FOIA request should auto-suggest adding a document to the Public Records Database.