Create database / database schema

[warlokkz]

This is a crucial part of the app, and needs some time to think about design of the schema. Ideally, we want something that can be modified by whomever decides to adopt the project after this. We should come up with a minimum requirements list for what we will be collecting from the form.

Ideally, I would like to have a time-series or versioning capable database, so that if the administrators decide to change the schema (form questions), we can adjust for that on the server side. What other collections/tables could we have besides form data?

Suggestions:

Users (for logging in and giving permissions) ? Locations (could be sorted by districts, addresses or geolocation - depending on the granularity we want) Persons (the homeless whom are being assessed) Assessments (attached to Persons) \ I think keeping different versions of assessments that are attached to Persons can be good for keeping a time-series or versioned system to easily query for progress / tracking. Using this schema can most definitely fit a RDBMS like PostgresQL or a graphDB like ArangoDB.

[warlokkz]

Tentative Annual Collection Schemas

Users:

• _id
• username
• password
• email
• role

Locations:

• _id
• district
• geojson (bounds?)

Persons:

• _id
• ...personal information

Assessments

• _id
• personId
• locationId
• timestamp
• ...VI-SPDAT

Reports:

• _id
• locationId
• timestamp
• personId

[warlokkz]

Users Collection

• email string
• role string
• permissions array

[warlokkz]

Districts Collection:

• districtId string
• member string
• email string
• phoen string

[warlokkz]

Reports Collection:

• id uuid
• districts array (list of district ids)
• reported_at date

[warlokkz]

Persons Collection:

• id uuid
• age number
• alcohol_drug_problem bool
• benefits_ebt bool
• benefits_ssi bool
• benefits_tanf bool
• benefits_unemployment bool
• benefit_veteran bool
• benefit_welfare bool
• date_created date
• drivers_license_number string
• education_level string
• employment_current_pay number
• employment_last_employed date
• employment_status bool
• ethnicity string
• family_members_adult number
• family_members_children number
• first_name string
• gender string
• geolocation geo?
• hawaii_state_id string
• last_date_homeless date
• last_homeless_area_lived string
• last_name string
• length_of_stay_in_hawaii number
• mental_health_disability bool
• on_the_streets bool
• other_disability string
• shelter_name string
• shelter_status bool
• ssn string(encrypt)
• reason_for_homlessness string
• times_homeless_count number
• veteran bool

[warlokkz]

Assessments Collection:

• id uuid

Scores:

• basic_demographic_risk_score number
• risk_of_harm_score number
• emergency_use_risk_score number
• history_risk_score number
• legal_issues_score number
• risk_or_exploitation_score number
• money_management_score number
• meaningful_daily_activity_score number
• self_care_score number
• social_relationships_score number
• physical_health_score number
• substance_abuse_score number
• mental_health_score number
• TRI_morbidity_score number
• medications_score number
• abuse_and_trauma_score number

General Demographics:

• first_name string
• nick_name string
• last_name string
• main_language string
• languages array
• date_of_birth date
• age number
• ssn string (encrypt)
• consent_of_participation bool

History of Housing and Homelessness:

• sleeps_most_frequently_at string
• time_passed_since_permanent_housing number
• times_homeless_in_past_3_years number

Risks:

• times_received_er_care_in_6_months number
• times_ambulance_rides_in_6_months number
• times_hospitalized_as_inpatient_in_6_months number
• times_used_crisis_service_in_6_months number
• times_police_talks_in_6_months number
• times_jailed_in_6_months number
• times_attacked_since_homeless number
• times_harmed_self_or_others_past_year number
• has_immediate_legal_issues bool
• being_forced_upon_to_do_things bool
• being_exploited_for_sex_or_drugs bool

Socialization & Daily Functioning:

• owes_money bool
• has_income bool
• has_meaningful_activity bool
• has_basic_self_care bool
• homeless_due_to_relationships bool

Wellness:

• forced_from_housing_because_health bool
• chronic_health_issues_with_LKSLH bool
• interested_in_hiv_aids_program bool
• limiting_physical_disabilities bool
• avoids_help_when_sick bool
• currently_pregnant bool
• forced_from_housing_because_alcohol_drugs bool
• maintain_housing_difficulty_alcohol_drugs bool
• maintain_housing_difficulty_mental_health bool
• maintain_housing_difficulty_head_injury bool
• maintain_housing_difficulty_learning_disability bool
• limiting_mental_disabilities bool
• not_taking_prescribed_medications bool
• selling_or_abusing_prescribed_medications bool
• homelessness_caused_by_abuse_or_trauma bool