civictechatlanta / georgia-courtbot Goto Github PK

View Code? Open in Web Editor NEW

4.0 8.0 5.0 50 KB

Helping people remember to attend court to help break the cycle of fines and jail time

Python 69.35% JavaScript 24.18% Dockerfile 6.46%

georgia-courtbot's Introduction

georgia-courtbot

Helping people remember to attend court to help break the cycle of fines and jail time

Notes for Contributors

Roadmap

Version 1 (MVP): Dekalb County scraper built & single copy of dataset captured. Simple SMS functionality set up based off of that data
- Tech Components:
  - Data scraper for Dekalb county
  - Data saved in place SMS tool can pick up
  - Simple SMS functionality
  - Notification system that triggers text to be sent
- Design Needs:
  - Easy-to-manage online form (e.g. Google forms or AirTable)
  - Usability research
Version 2: Dekalb County scraper running daily in the cloud, pushes data into database. Website similar to https://court.bot facilitates sign up for court date reminders via SMS
- Components (in addition to MVP1):
  - Database
  - Website
  - SMS sign-up
  - Reminder scheduler
- Design Needs:
  - UX Flows
Version 3+ (Potential Features)
- Additional counties / municipalities
- Integration with other courtbot implementation(s)
- Additional messaging besides SMS
- Chatbot like features (reminders, additional details, etc)

georgia-courtbot's People

Contributors

Stargazers

Watchers

Forkers

abrie shaunm44 bennyjw rick-ray jaredhammond

georgia-courtbot's Issues

BACKEND: Make hearing data available on web (manual upload)

For version-1, ok to take the CSV data produced from manually running scraper and manually upload to Google Sheets

Columns needed:

CASE_NUMBER
CASE_ID
HEARING_DATE
HEARING_TIME
COURTROOM
JUDICIAL_OFFICER

Update scraper to work around 200ish record limit

Scraper: https://github.com/codeforatlanta/georgia-courtbot/blob/main/data/dekalb_scraper.py

Example search on 2022-02-02 that hit the limit:

Idea with this new scraper version is use the BigQuery SUBSCRIPTION table (fields CaseNumber / PhoneNumber) as our list of what to scrape. We only ever want to scrape case info for cases people have signed up to

Here's the flow:

User signs up for subscriptions on a case number
Query bigquery table(s) to find case
If:
- Case found --> use info from BigQuery
- Case not found --> search online site (with large date range) to get hearing info, add to BigQuery, then use info from BigQuery

FEATURE: SMS reminder sign-up (all hearings for case number)

(may need to split this out into a flow map w/ decision loops, but for now trying to do it as simpler features)

Feature: SMS reminder sign-up
    As an interested party, I want to sign up for reminders on all hearings (of a given Case Number)
    so that I can ensure the defendant shows up to the court date
    (interested party could potentially be defendant)

    Scenario: Existing Case - has future hearing(s)
        Given: I submitted a sign up request
            And: My case number is in hearing data
            And: I have at least one hearing date in the future
        Then: I receive a text confirming I'm signed up for reminders on all hearings

    Scenario: Existing Case - no future hearings
        Given: I submitted a sign up request
            And: My case number is in hearing data
            And: I do not have a hearing date in the future
        Then: I receive a text telling me there are no hearings to set up for reminders

    Scenario: Non-Existent Case
        Given: I submitted a sign up request
            And: My case number is not in hearing data
        Then: I receive a text saying case number not found

Open Questions

Need some kind of confirmation (what if case number isn't unique across counties?)
What other key scenarios are we missing?

Securely store phone numbers

Acceptance Criteria:

PII (e.g. phone #) is anonymized in place in cloud storage outside of Twilio
PII is purged short amount of time (<= 30 days?) after the final scheduled notification

DISCOVERY: Which GA counties & municipalities have online court hearing info?

Metro Atlanta Counties: DeKalb, Fulton, Douglas, Cobb, Gwinnett, Cherokee, Forsyth, Fayette, Clayton, Henry, Rockdale

Pull case info into Twilio SMS flow

Given text from user, make API call from Twilio to to determine if case is found:

Given:
    UserText
Retrieve:
    CaseNumber

Given a case number, make API call from Twilio to to get case information:

Given:
    CaseNumber
Retrieve:
    HearingDate
    HearingTime
    CourtRoom

Bug: Scraper fails because of unavailable local issuer certificate

The scraper returns the following stack trace:

Traceback (most recent call last):
  File "/usr/local/Cellar/[email protected]/3.9.10/Frameworks/Python.framework/Versions/3.9/lib/python3.9/runpy.py", line 197, in _run_module_as_main
    return _run_code(code, main_globals, None,
  File "/usr/local/Cellar/[email protected]/3.9.10/Frameworks/Python.framework/Versions/3.9/lib/python3.9/runpy.py", line 87, in _run_code
    exec(code, run_globals)
  File "/Users/anvil/development/codeforatl/georgia-courtbot/scraper/__main__.py", line 47, in <module>
    cli()
  File "/Users/anvil/development/codeforatl/georgia-courtbot/.venv/lib/python3.9/site-packages/click/core.py", line 1128, in __call__
    return self.main(*args, **kwargs)
  File "/Users/anvil/development/codeforatl/georgia-courtbot/.venv/lib/python3.9/site-packages/click/core.py", line 1053, in main
    rv = self.invoke(ctx)
  File "/Users/anvil/development/codeforatl/georgia-courtbot/.venv/lib/python3.9/site-packages/click/core.py", line 1659, in invoke
    return _process_result(sub_ctx.command.invoke(sub_ctx))
  File "/Users/anvil/development/codeforatl/georgia-courtbot/.venv/lib/python3.9/site-packages/click/core.py", line 1395, in invoke
    return ctx.invoke(self.callback, **ctx.params)
  File "/Users/anvil/development/codeforatl/georgia-courtbot/.venv/lib/python3.9/site-packages/click/core.py", line 754, in invoke
    return __callback(*args, **kwargs)
  File "/Users/anvil/development/codeforatl/georgia-courtbot/scraper/__main__.py", line 24, in scrape
    scraper.data.dekalb_scraper.run(output, days)
  File "/Users/anvil/development/codeforatl/georgia-courtbot/scraper/data/dekalb_scraper.py", line 165, in run
    results = scrape(days=days)
  File "/Users/anvil/development/codeforatl/georgia-courtbot/scraper/data/dekalb_scraper.py", line 124, in scrape
    for officer in scraper.get_all_judicial_officers():
  File "/Users/anvil/development/codeforatl/georgia-courtbot/scraper/data/dekalb_scraper.py", line 19, in get_all_judicial_officers
    response = self.session.get(url, headers=self.headers)  # verify=False)
  File "/Users/anvil/development/codeforatl/georgia-courtbot/.venv/lib/python3.9/site-packages/requests/sessions.py", line 542, in get
    return self.request('GET', url, **kwargs)
  File "/Users/anvil/development/codeforatl/georgia-courtbot/.venv/lib/python3.9/site-packages/requests/sessions.py", line 529, in request
    resp = self.send(prep, **send_kwargs)
  File "/Users/anvil/development/codeforatl/georgia-courtbot/.venv/lib/python3.9/site-packages/requests/sessions.py", line 645, in send
    r = adapter.send(request, **kwargs)
  File "/Users/anvil/development/codeforatl/georgia-courtbot/.venv/lib/python3.9/site-packages/requests/adapters.py", line 517, in send
    raise SSLError(e, request=request)
requests.exceptions.SSLError: HTTPSConnectionPool(host='ody.dekalbcountyga.gov', port=443): Max retries exceeded with url: /portal/Home/Dashboard/26 (Caused by SSLError(SSLCertVerificationError(1, '[SSL: CERTIFICATE_VERIFY_FAILED] certificate verify failed: unable to get local issuer certificate (_ssl.c:1129)')))

When scraper runs, appends to BigQuery table

Scraper runs in command line, outputs to csv
Python code runs that loads to BigQuery:

Append to table cfa-georgia-courtbot.STG.HEARING (need to add ScraperRunTS field as data type DATETIME)

Fields needed:

  - Case Number
  - Hearing Date
  - Hearing Time
  - Courtroom
  - (last updated date? <-- when the scraper ran)

Set up billing for Twilio

Load in credit card to account

Reference:

Twilio account - Georgia Courtbot (SID: AC2d08530b7dda3ec4a1b008f3bde40de5)

add secret to github actions

instructions here: #32

Manual scrape 2022-02-01 didn't work

Here's what I ran:

% python3 data/dekalb_scraper.py --output csv > scrape_20220201.csv

And output in terminal:

Scraping Dekalb County court cases per judicial officer...
ID	Name
282	Adams, Gregory A.
Traceback (most recent call last):
  File "data/dekalb_scraper.py", line 149, in <module>
    run(args.output)
  File "data/dekalb_scraper.py", line 123, in run
    for fields in fields_of_interest
  File "data/dekalb_scraper.py", line 123, in <listcomp>
    for fields in fields_of_interest
TypeError: unsupported operand type(s) for |: 'dict' and 'dict'

Build in monitoring & alerting

Acceptance Criteria:

(TODO)

Set up Google cloud

Create shared google cloud acct
Grant access
Set up billing (need for GCS storage besides app engine)
Activate API's: GCS, GBQ
Create service acct
Load scraped CSV into table

Build static website

Features:

Similar look & feel to to https://court.bot (ok to copy "About" & "Why" sections from there...use placeholders if needed)
Web form letting people sign up for court case
Link to Code For Atlanta
Link to this github repo

Register codeforatlanta.org as domain for Twilio account

Reference Links: