Django Backend

[reustle]

@olingern moving our convo here

At some point, we will outgrow Google Sheets. Doing some quick data modeling in Django would be easy, and we'll get a free admin interface w/ user accounts, but the problem lies with the data entry side. Google Sheets makes it easy to see who is editing what, and not overwrite each other. What minimal UI could be pulled together with off-the-shelf libraries to help achieve this?

One thought is, a simple table of each of the patients where you click 'edit' and a modal opens. As soon as a modal opens, we put that users icon on the table saying that the person is currently viewing the form for that patient. That would automatically be reflected (sockets?) on the table for everyone. Ideally, nobody opens a form and leaves for 30 min.

Open to all suggestions. Thanks 🙏

Brainstorming data model:

let patient = {
  id: 'number', // our internal patientId. check source reference to see foreign ID
  dateAnnounced: 'timestamp',
  dateAdded: 'timestamp',
  createdBy: 'REF user_id',
  modifiedBy: 'REF user_id', // last edited by
  ageBracket: '0,10,20, etc',
  gender: 'M,F,null',
  detectedLocation: 'REF location',
  residentLocation: 'REF location',
  status: 'REF patientStatus',
  sources: 'REF 1-to-many patientSource',
  relatedPatients: '1,14',
  occupation: 'taxi driver 3',
  notes: 'freeform notes field',
  origin: 'REF origin',
}

let origin = [
  { label: 'charterFlight' },
  { label: 'diamondPrincess' },
  { label: 'japan' },
]

let location = {
  city: 'string',
  prefecture: 'REF prefecture',
  country: 'jp',
  lat: '',
  lng: ''
}

let prefecture = [
  { label: 'Tokyo'}
]

let patientSource = {
  label: 'i.e. MHLW',
  patientId: 'number ref to original patient ID',
  sourceId: 124, // ID on the source such as MHLW
  url: 'url here',
}

let patientStatus = [
  { label: 'hospitalized' },
  { label: 'Discharged' },
  { label: 'Recovered' },
  { label: 'Deceased' },
  { label: 'Unspecified' },
]

[liquidx]

Looks good. A few suggestions:

• city/prefecture. There's a distinction between where they live and where they were detected/hospitalizaed. Right now, when it comes to reporting, it seems a little blurry when someone lives in one prefecture and works/detected in another. I think for tracking purposes, we should separate those two out. Possibly:

{
...
detectedLocation: 'REF location',
residentLocation: 'REF location'
}

let location = {
  city: 'string',
  prefecture: 'prefecture',
  lat: '',
  lng: ''
}

[liquidx]

• Separate the cruiseship/charterplane etc, maybe you could have it as origin of either {"japan", "cruise", "charterplane"}
• The MHLW id can be represented by the patientSource
• patientSource should have a name of the source (NHK, MHLW, Prefecture govt, etc)

[reustle]

In Progress https://github.com/reustle/covid19japan/tree/django-backend/backend

[mathieujobin]

how about a sync job between the spreadsheet and redis database?

[reustle]

Hmm I don't think redis is really necessary here. The primary goal of the database is to have a greatly increased level of data validation. We are considering a few ideas for batch generating json files more recently, though.

[JunaidBabu]

Hi @reustle

Sorry to jump into an old post.

I'm one of the early members of @covid19india, we have our dashboard at covid19india.org. I've been following your repository since the beginning and was actually inspired by your project. In fact our initial dashboard was a fork of your project.

We are maintaining a database of Covid-19 in India at a patient level in Google Sheets. When the number of cases increased in India, it was difficult to maintain in Google sheets and we also considered shifting to a more dedicated database and using Django. But from the data entry point of view nothing could beat the features of Google Sheets.

Eventually we decided to stick with Google Sheets itself and shift to a new sheet once the current sheet is full (or considerably heavy). And used scripts in github-actions to create aggregate results and keep in github itself. As of writing this we are on our 12th version of Google sheets with 1.5 Million cases in India. More details could be found here https://api.covid19india.org/documentation/csv/

May I ask how you are handling the data in Japan now? Is it still using Google sheets, or you managed to shift to Django?

[liquidx]

I think covid19india.org is awesome! I was sharing it with the developers and designers, you all did a really good job ob it.

We are still using Google Sheets, but we're approaching the limits, it seems like you may have already done so. I started working on a small fix to covid19japan-data that can fetch from multiple spreadsheets.

See: https://github.com/reustle/covid19japan-data

I think this is very similar to the api repo that you have.