search

codeforanchorage / courtbot

Your friendly neighborhood court robot. Provides an api for getting court case information.
https://courtbot.codeforanchorage.org
ISC License
7 stars 10 forks source link

Handle criminal cases #46

Closed tescher closed 6 years ago

tescher commented 7 years ago

Criminal cases can have multiple appointments per case. I think we will need to store after sending the reminder the hearing date, and if a different hearing date appears for the reminder request, we send another reminder for that. Probably also need a "STOP" option on the second and beyond reminders for a particular case. Also need a way to clean up old reminder requests.

Email chain with Sharon....

See my notes as follows and others may want to chime in. Thanks. Sharon

From: Tim Escher [mailto:tim@eschers.com] Sent: Friday, July 28, 2017 7:42 AM To: Sharon Chen Cc: Brenda Axtell; Joe Mannion; Brendan Babb; Julie Monsen; Stacey Marz Subject: Re: Meeting notes, criminal hearings

Couple questions

Tim Escher tim@eschers.com 608-432-5799

On Thu, Jul 27, 2017 at 4:11 PM, Sharon Chen schen@akcourts.us wrote: Hi Tim,

Thanks to Julie Monsen, here is the link to criminal hearings CSV data that is available to your team: http://www.courtrecords.alaska.gov/MAJIC/sandbox/acs_cr_event.csv

Data set contains the immediate next upcoming future unresolved court event for each and every public criminal cases with one or more future court date(s). Event description in this data set is identical to what appears in court pubic calendar on court website.

Each row contains the following data fields: o Event Date

o Defendant’s Last Name

o Defendant’s First Name

o Courtroom

o Event Time

o Criminal Case Number

o Court Event Description

Please review and let us know your questions.

Brenda, Joe , and I had to reschedule our meeting on FAQs to next week. We will be in touch then.

Thanks!

Sharon Chen Alaska Court System (907) 264-8210

mfrederickson commented 7 years ago

56 loads the data

mfrederickson commented 7 years ago

@tescher, @markmeyerphoto How is this for a quick take on the design?

Load

Loads data files into the hearings table, one hearing (defendant|date|room) per row. So multiple rows may have the same citation number or case number.

Receive a citation|case number

Send Reminders

For each requests row that is not rescinded, and where the request_expiration_date >= today

  1. find hearings that match the case # (not case id), occurring tomorrow
    • if not in notification_sent table for this phone and hearing_id
      • send a reminder text
      • add a row to the notification_sent table
      • update request_expiration_date to max(self, today + TTL)
  2. if no hearings found, and today > request_expiration_date
    • ask to extend request to today + TTL
      • if yes, update request_expiration_date to today + TTL
      • if no, mark as rescinded and send "for more info" blurb

Purge

For each requests row

  1. if request_expiration_date < today - PURGE_DAYS_OLD
    • delete (cascade deletes notification_sent)

Send Queued

No longer need this process.

tescher commented 7 years ago

For this one:

if not in notification_sent table for this phone and hearing_id

I don't think we can use hearing_id unless we never re-use hearing_ids at each load of the CSV file. We get a fresh list of hearings every day, and the list we got yesterday we should assume is completely invalid when the new CSV comes out each afternoon.

I think we can probably look to see if a notification was sent for a particular case/place/time.

Tim Escher tim@eschers.com 608-432-5799

On Tue, Sep 19, 2017 at 2:56 PM, Marvin Frederickson < notifications@github.com> wrote:

@tescher https://github.com/tescher, @markmeyerphoto https://github.com/markmeyerphoto How is this for a quick take on the design? Load

Loads data files into the hearings table, one hearing (defendant|date|room) per row. So multiple rows may have the same citation number or case number. Receive a citation|case number

-

if invalid number, say so and exit

if already watching for this case (check requests table)

  1. say so and say when the request expires
    1. ask if they want to cancel the existing request
      • if yes, mark the request as rescinded
      • if no, send "for more info" blurb

  2. exit

    if case # found in hearings table

  3. show relevant case information (defendant, violation, first upcoming hearing date and location) for confirmation purposes
    1. ask to be informed the day before for all hearings related to this case #
      • if yes, store phone and case # and the request_expiration_date into the requests table
      • if no, send "for more info" blurb

  4. exit

    if case # not found in hearings table

  5. say so
    1. ask to be informed the day before for all hearings related to this case #
      • if yes, store phone and case # and the request_expiration_date into the requests table
      • if no, send "for more info" blurb
  6. exit

Send Reminders

For each requests row that is not rescinded, and where the request_expiration_date >= today

  1. find hearings that match the case # (not case id), occurring tomorrow

    • if not in notification_sent table for this phone and hearing_id
      • send a reminder text
      • add a row to the notification_sent table
      • update request_expiration_date to max(self, today + TTL)

  2. if no hearings found, and today > request_expiration_date

    • ask to extend request to today + TTL
      • if yes, update request_expiration_date to today + TTL
      • if no, mark as rescinded and send "for more info" blurb

Purge

For each requests row

  1. if request_expiration_date < today - PURGE_DAYS_OLD

    • delete (cascade deletes notification_sent)

Send Queued

No longer need this process.

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub https://github.com/codeforanchorage/courtbot/issues/46#issuecomment-330655360, or mute the thread https://github.com/notifications/unsubscribe-auth/ACcF3CGi1kmBmgn-pA_1NTWIoFUG2mQSks5skBx8gaJpZM4OnYXk .

mark-meyer commented 7 years ago

I like this.

I think there are a few things to consider:

Make it the default behavior to always notify users If someone requests notification and the case is in the db or we learn the case number is a real case within the TTL, I'm not sure it should expire after that unless they actively request it with some sort of stop word. If the basic goal is to nudge people to get them into the courtroom, it should require an act on their part to stop notifications rather than defaulting to expiring. What happens if we send the notification and they don't respond? I like the idea of avoiding stale requests in the DB, but given how small the user base will be, it might be worth keeping them (or at least having a very long expiration).

Do hearing dates even change? If so, how do we distinguish between a new hearing date and changed date. We'll need to somehow avoid sending notifications for the old date if the hearing date changed. [EDIT: I don't think this is an issue if we are always looking at fresh csv data]

Saving use data I would like to have a front end (which I've started on) that will allow someone to query historical use of the app and answer questions like:

…you get the idea. We can log a lot of this separately, but we wouldn't need to if we didn't purge the tables. We could know how many reminders were sent just by looking at notifications_sent In fact we could even do something with a foreign key constraint that would let us keep all hearings that have had requests attached. And maybe it is better just to have independent historical data, which would allow the app to change in the future without impacting legacy data.

tescher commented 7 years ago

Hearing dates/times/locations do change frequently. But, since we are looking at the CSV anew each time it comes out, and we are only looking at it after the end of the business day when all known changes should have been included, and we are only sending reminders for the next day, it is highly unlikely someone will get an invalid reminder.

Tim Escher tim@eschers.com 608-432-5799

On Tue, Sep 19, 2017 at 3:42 PM, Mark Meyer notifications@github.com wrote:

I like this.

I think there are a few things to consider:

Make it the default behavior to always notify users If someone requests notification and the case is in the db or we learn the case number is a real case within the TTL, I'm not sure it should expire after that unless they actively request it with some sort of stop word. If the basic goal is to nudge people to get them into the courtroom, it should require an act on their part to stop notifications rather than defaulting to expiring. What happens if we send the notification and they don't respond? I like the idea of avoiding stale requests in the DB, but given how small the user base will be, it might be worth keeping them (or at least having a very long expiration).

Do hearing dates even change? If so, how do we distinguish between a new hearing date and changed date.

Saving use data I would like to have a front end (which I've started on) that will allow someone to query historical use of the app and answer questions like:

  • Where reminders requested for a particular case?
  • How many notification were sent last month or last year?
  • Did we have a lot of (formerly called queued cases) that were never found? (This could indicate people are having trouble using the app)
  • Are particular kinds of cases more apt to have reminders

…you get the idea. We can log a lot of this separately, but we wouldn't need to if we didn't purge the tables. We could know how many reminders were sent just by looking at notifications_sent In fact we could even do something with a foreign key constraint that would let us keep all hearings that have had requests attached.

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub https://github.com/codeforanchorage/courtbot/issues/46#issuecomment-330667553, or mute the thread https://github.com/notifications/unsubscribe-auth/ACcF3D6CRK5EcW6LoD8Dvtdu7QLnQ2-Zks5skCdNgaJpZM4OnYXk .

mfrederickson commented 7 years ago

@tescher regarding reusing hearing_ids I was thinking that it would be a hash of those component fields so that we could reload it, not be an identity/sequence field.

tescher commented 7 years ago

Ah, OK

Tim Escher tim@eschers.com 608-432-5799

On Tue, Sep 19, 2017 at 4:27 PM, Marvin Frederickson < notifications@github.com> wrote:

@tescher https://github.com/tescher regarding reusing hearing_ids I was thinking that it would be a hash of those component fields so that we could reload it, not be an identity/sequence field.

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub https://github.com/codeforanchorage/courtbot/issues/46#issuecomment-330678735, or mute the thread https://github.com/notifications/unsubscribe-auth/ACcF3CPqzKJmkCCJ5YSHirvcpkJrLIyHks5skDHGgaJpZM4OnYXk .

mark-meyer commented 7 years ago

Loads data files into the hearings table, one hearing (defendant|date|room) per row. So multiple rows may have the same citation number or case number.

I'm not sure I understand if you mean for (defendant|date|room) to be unique. If it is unique, where do we store the citation number when there is more than one for a hearing? Fore example given this in the csv:

10/05/2017 | Adsuna | Trae |   | Nome Courthouse | 9:00 am | NOMZE000001340004 10/05/2017 | Adsuna | Trae |   | Nome Courthouse | 9:00 am | NOMZE000002090007

Will this become one row?

If the above converts to two rows, then do we even need the (defendant|date|room) distinction? Can we just have one row of csv == one row of DB?

mfrederickson commented 7 years ago

Sorry. I think we need the case # in there too.

On Sep 19, 2017, at 1:59 PM, Mark Meyer notifications@github.com wrote:

Loads data files into the hearings table, one hearing (defendant|date|room) per row. So multiple rows may have the same citation number or case number.

I'm not sure I understand if you mean for (defendant|date|room) to be unique. If it is unique, where do we store the citation number when there is more than one for a hearing? Fore example given this in the csv:

10/05/2017 | Adsuna | Trae | | Nome Courthouse | 9:00 am | NOMZE000001340004 10/05/2017 | Adsuna | Trae | | Nome Courthouse | 9:00 am | NOMZE000002090007

Will this become one row?

If the above converts to two rows, then do we even need the (defendant|date|room) distinction? Can we just have one row of csv == one row of DB?

— You are receiving this because you commented. Reply to this email directly, view it on GitHub, or mute the thread.

mfrederickson commented 7 years ago

Yes one row csv is one row in db

On Sep 19, 2017, at 1:59 PM, Mark Meyer notifications@github.com wrote:

Loads data files into the hearings table, one hearing (defendant|date|room) per row. So multiple rows may have the same citation number or case number.

I'm not sure I understand if you mean for (defendant|date|room) to be unique. If it is unique, where do we store the citation number when there is more than one for a hearing? Fore example given this in the csv:

10/05/2017 | Adsuna | Trae | | Nome Courthouse | 9:00 am | NOMZE000001340004 10/05/2017 | Adsuna | Trae | | Nome Courthouse | 9:00 am | NOMZE000002090007

Will this become one row?

If the above converts to two rows, then do we even need the (defendant|date|room) distinction? Can we just have one row of csv == one row of DB?

— You are receiving this because you commented. Reply to this email directly, view it on GitHub, or mute the thread.

mark-meyer commented 7 years ago

I wonder as long as we're doing this if it's worth putting a unique constraint on cases for those columns. We could then avoid manually checking in the code and use an ON CONFLICT clause in the insert to either ignore or update in case there is a duplicate. It would save the trouble of building the hash and maintaining the case map in the code. And it would be more explicit on the table schema.

mark-meyer commented 7 years ago

I have a big chunk of this working, but I'm encountering an edge case in how we tell what someone means by 'yes' or 'no' after we send notifications. This problem is in the original app too and is caused by the inability to set any sort of state on twillio requests going through the API. (We get cookies on the Twillio-initiated requests coming through the http endpoint, but not when we send reminders).

The problem happens if I submit two queued items and both are matched with cases on the same day (which seems quite possible given how easy it is to get more than one citation). With two queued items, it will send two notifications, but responding yes won't work. That app ignores it. And it's hard to know how it could work in the original app because we don't have any way to know which queued item we're responding yes (or no) to. The queued items get marked as sent but the user is never able to turn them into reminders.

In this new scheme, I think it would be nice to hold on to the idea that requests for which we don't have matches eventually time out unless renewed. After ten days they are probably typos or junk anyway. We could avoid the problems in the original if instead of asking someone to type 'yes'/'no', we tell them the request(s) will expire since no case was found. If they want to continue waiting type 'renew' (or similar) to renew a request(s). Then we will renew all their about-require requests if there are more than one or delete them if we don't hear back after a given period. we give up on unfound ones and just inform them, as we do now. But instead of asking if they want reminders for the newly found cases, just tell them they will get reminders and give an option of sending 'stop' or similar at anytime. That will avoid overloading of yes/no and make everything a lot easier. The UI will become difficult if we try to make stop too fine-grained. If 'stop' turned off all requests it would be quite simple to implement. What do you think of this?

I should have some code to share soon after I iron out some wrinkles. I think @mfrederickson's data-structure plan is going to work well and a lot of the app will be simpler. The loaddata script for example is about half the size!

tescher commented 7 years ago

That sounds good to me

Tim Escher tim@eschers.com 608-432-5799

On Mon, Oct 2, 2017 at 8:24 PM, Mark Meyer notifications@github.com wrote:

I have a big chunk of this working, but I'm encountering an edge case in how we tell what someone means by 'yes' or 'no' after we send notifications. This problem is in the original app too and is caused by the inability to set any sort of state on twillio requests going through the API. (We get cookies on the Twillio-initiated requests coming through the http endpoint, but not when we send reminders).

The problem happens if I submit two queued items and both are matched with cases on the same day (which seems quite possible given how easy it is to get more than one citation). With two queued items, it will send two notifications, but responding yes won't work. That app ignores it. And it's hard to know how it could work in the original app because we don't have any way to know which queued item we're responding yes (or no) to. The queued items get marked as sent but the user is never able to turn them into reminders.

In this new scheme, I think it would be nice to hold on to the idea that requests for which we don't have matches eventually time out unless renewed. After ten days they are probably typos or junk anyway. We could avoid the problems in the original if instead of asking someone to type 'yes'/'no', we tell them the request(s) will expire since no case was found. If they want to continue waiting type 'renew' (or similar) to renew a request(s). Then we will renew all their about-require requests if there are more than one or delete them if we don't hear back after a given period. That will avoid overloading of yes/no and make everything a lot easier. What do you think of this?

I should have some code to share soon after I iron out some wrinkles. I think @mfrederickson https://github.com/mfrederickson's data-structure plan is going to work well and a lot of the app will be simpler. The loaddata script for example is about half the size!

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub https://github.com/codeforanchorage/courtbot/issues/46#issuecomment-333712135, or mute the thread https://github.com/notifications/unsubscribe-auth/ACcF3D8vF1Cj0gd04R4zT4bRUgJuYJngks5soYzVgaJpZM4OnYXk .

mark-meyer commented 7 years ago

I just pushed a new version of the update_libs branch that has most of these changes. It's a pretty thorough rewrite of a lot the app. But a lot of it got simpler. It should handle things like multiple hearings gracefully. It includes the ability to type 'Stop' to cancel requests. At the moment stop cancels all requests, but if asks for confirmation before deleting. It wouldn't be hard to allow individual cancellation, but the user interface might become hard to understand.

I don't want to write (and nobody wants to read) a giant post with all the changes. The main things to pay attention to are:

  1. The data model is revamped into tables: hearings, requests, and notifications.
  2. Many of the uniqueness constraints the we handled at the app level before are now enforced on the DB. This makes loading data much easier:
    • You can have multiple hearings for the same case/citation number so long as they aren't at the same time.
    • A given number can only have one active request per citation number, which means you shouldn't ever get multiple notifications for the same hearing.
  3. You will continue to get notifications if a case/citation number has more than one hearing
  4. Requests that aren't matched to a case within the given TTL are deleted after notifying the subscriber. Requests that have matched a case stay in the system even if there isn't a current case the hearings table. This is managed with a 'known_case' boolean on requests.
  5. The loader script now streams csv directly to Postgres. This means we never have to buffer it, which makes it fast and also keep the memory footprint down.
  6. I used async/await in a lot of places, which is a little easier on the eyes than promise.then() pattern. But it will require Node v > 8.

A few TODOs still:

  1. When messages don't look like case ids we should probably give users some instruction instead of saying we couldn't find the case.
  2. We will want to take a good look at messages to see that everything still makes sense and supports criminal cases.
  3. To support possibility of multiple hearings suggests we change the wording/logic of message.foundItIwllRemind(), We currently just send the first result.
  4. The hearings table has a ‘type’ column that captures the hearing type in criminal cases, but we aren’t using the data yet. Sharon mentions above that we should work that in. It shouldn't be hard.