patient_summaries

Columns

Column

Type

Size

Nulls

Auto

Default

Children

Parents

Comments

recorded_at

timestamp

29,6

√

null

registration_quarter

text

2147483647

√

null

full_name

varchar

2147483647

√

null

current_age

float8

17,17

√

null

gender

varchar

2147483647

√

null

status

varchar

2147483647

√

null

assigned_facility_id

uuid

2147483647

√

null

latest_phone_number

varchar

2147483647

√

null

village_or_colony

varchar

2147483647

√

null

street_address

varchar

2147483647

√

null

district

varchar

2147483647

√

null

state

varchar

2147483647

√

null

registration_facility_name

varchar

2147483647

√

null

registration_facility_type

varchar

2147483647

√

null

registration_district

varchar

2147483647

√

null

registration_state

varchar

2147483647

√

null

latest_blood_pressure_systolic

int4

√

null

latest_blood_pressure_diastolic

int4

√

null

latest_blood_pressure_recorded_at

timestamp

29,6

√

null

latest_blood_pressure_quarter

text

2147483647

√

null

latest_blood_pressure_facility_name

varchar

2147483647

√

null

latest_blood_pressure_facility_type

varchar

2147483647

√

null

latest_blood_pressure_district

varchar

2147483647

√

null

latest_blood_pressure_state

varchar

2147483647

√

null

latest_blood_sugar_type

varchar

2147483647

√

null

latest_blood_sugar_value

numeric

√

null

latest_blood_sugar_recorded_at

timestamp

29,6

√

null

latest_blood_sugar_quarter

text

2147483647

√

null

latest_blood_sugar_facility_name

varchar

2147483647

√

null

latest_blood_sugar_facility_type

varchar

2147483647

√

null

latest_blood_sugar_district

varchar

2147483647

√

null

latest_blood_sugar_state

varchar

2147483647

√

null

days_overdue

float8

17,17

√

null

next_appointment_id

uuid

2147483647

√

null

next_appointment_scheduled_date

date

√

null

next_appointment_status

varchar

2147483647

√

null

next_appointment_cancel_reason

varchar

2147483647

√

null

next_appointment_remind_on

date

√

null

next_appointment_facility_id

uuid

2147483647

√

null

next_appointment_facility_name

varchar

2147483647

√

null

next_appointment_facility_type

varchar

2147483647

√

null

next_appointment_district

varchar

2147483647

√

null

next_appointment_state

varchar

2147483647

√

null

risk_level

int4

√

null

latest_bp_passport_id

uuid

2147483647

√

null

latest_bp_passport_identifier

varchar

2147483647

√

null

uuid

2147483647

√

null

Relationships

Close relationships within degrees of separation

View Definition

SELECT p.recorded_at,
    concat(date_part('year'::text, p.recorded_at), ' Q', date_part('quarter'::text, p.recorded_at)) AS registration_quarter,
    p.full_name,
        CASE
            WHEN (p.date_of_birth IS NOT NULL) THEN date_part('year'::text, age((p.date_of_birth)::timestamp with time zone))
            ELSE ((p.age)::double precision + date_part('years'::text, age(now(), (p.age_updated_at)::timestamp with time zone)))
        END AS current_age,
    p.gender,
    p.status,
    p.assigned_facility_id,
    latest_phone_number.number AS latest_phone_number,
    addresses.village_or_colony,
    addresses.street_address,
    addresses.district,
    addresses.state,
    reg_facility.name AS registration_facility_name,
    reg_facility.facility_type AS registration_facility_type,
    reg_facility.district AS registration_district,
    reg_facility.state AS registration_state,
    latest_blood_pressure.systolic AS latest_blood_pressure_systolic,
    latest_blood_pressure.diastolic AS latest_blood_pressure_diastolic,
    latest_blood_pressure.recorded_at AS latest_blood_pressure_recorded_at,
    concat(date_part('year'::text, latest_blood_pressure.recorded_at), ' Q', date_part('quarter'::text, latest_blood_pressure.recorded_at)) AS latest_blood_pressure_quarter,
    latest_blood_pressure_facility.name AS latest_blood_pressure_facility_name,
    latest_blood_pressure_facility.facility_type AS latest_blood_pressure_facility_type,
    latest_blood_pressure_facility.district AS latest_blood_pressure_district,
    latest_blood_pressure_facility.state AS latest_blood_pressure_state,
    latest_blood_sugar.blood_sugar_type AS latest_blood_sugar_type,
    latest_blood_sugar.blood_sugar_value AS latest_blood_sugar_value,
    latest_blood_sugar.recorded_at AS latest_blood_sugar_recorded_at,
    concat(date_part('year'::text, latest_blood_sugar.recorded_at), ' Q', date_part('quarter'::text, latest_blood_sugar.recorded_at)) AS latest_blood_sugar_quarter,
    latest_blood_sugar_facility.name AS latest_blood_sugar_facility_name,
    latest_blood_sugar_facility.facility_type AS latest_blood_sugar_facility_type,
    latest_blood_sugar_facility.district AS latest_blood_sugar_district,
    latest_blood_sugar_facility.state AS latest_blood_sugar_state,
    GREATEST((0)::double precision, date_part('day'::text, (now() - (next_appointment.scheduled_date)::timestamp with time zone))) AS days_overdue,
    next_appointment.id AS next_appointment_id,
    next_appointment.scheduled_date AS next_appointment_scheduled_date,
    next_appointment.status AS next_appointment_status,
    next_appointment.cancel_reason AS next_appointment_cancel_reason,
    next_appointment.remind_on AS next_appointment_remind_on,
    next_appointment_facility.id AS next_appointment_facility_id,
    next_appointment_facility.name AS next_appointment_facility_name,
    next_appointment_facility.facility_type AS next_appointment_facility_type,
    next_appointment_facility.district AS next_appointment_district,
    next_appointment_facility.state AS next_appointment_state,
        CASE
            WHEN (next_appointment.scheduled_date IS NULL) THEN 0
            WHEN (next_appointment.scheduled_date > date_trunc('day'::text, (now() - '30 days'::interval))) THEN 0
            WHEN ((latest_blood_pressure.systolic >= 180) OR (latest_blood_pressure.diastolic >= 110)) THEN 1
            WHEN (((mh.prior_heart_attack = 'yes'::text) OR (mh.prior_stroke = 'yes'::text)) AND ((latest_blood_pressure.systolic >= 140) OR (latest_blood_pressure.diastolic >= 90))) THEN 1
            WHEN ((((latest_blood_sugar.blood_sugar_type)::text = 'random'::text) AND (latest_blood_sugar.blood_sugar_value >= (300)::numeric)) OR (((latest_blood_sugar.blood_sugar_type)::text = 'post_prandial'::text) AND (latest_blood_sugar.blood_sugar_value >= (300)::numeric)) OR (((latest_blood_sugar.blood_sugar_type)::text = 'fasting'::text) AND (latest_blood_sugar.blood_sugar_value >= (200)::numeric)) OR (((latest_blood_sugar.blood_sugar_type)::text = 'hba1c'::text) AND (latest_blood_sugar.blood_sugar_value >= 9.0))) THEN 1
            ELSE 0
        END AS risk_level,
    latest_bp_passport.id AS latest_bp_passport_id,
    latest_bp_passport.identifier AS latest_bp_passport_identifier,
    p.id
   FROM (((((((((((patients p
     LEFT JOIN addresses ON ((addresses.id = p.address_id)))
     LEFT JOIN facilities reg_facility ON ((reg_facility.id = p.registration_facility_id)))
     LEFT JOIN medical_histories mh ON ((mh.patient_id = p.id)))
     LEFT JOIN LATERAL ( SELECT ppn.id,
            ppn.number,
            ppn.phone_type,
            ppn.active,
            ppn.created_at,
            ppn.updated_at,
            ppn.patient_id,
            ppn.device_created_at,
            ppn.device_updated_at,
            ppn.deleted_at,
            ppn.dnd_status
           FROM patient_phone_numbers ppn
          WHERE (ppn.patient_id = p.id)
          ORDER BY ppn.device_created_at DESC
         LIMIT 1) latest_phone_number ON (true))
     LEFT JOIN LATERAL ( SELECT bp.id,
            bp.systolic,
            bp.diastolic,
            bp.patient_id,
            bp.created_at,
            bp.updated_at,
            bp.device_created_at,
            bp.device_updated_at,
            bp.facility_id,
            bp.user_id,
            bp.deleted_at,
            bp.recorded_at
           FROM blood_pressures bp
          WHERE (bp.patient_id = p.id)
          ORDER BY bp.recorded_at DESC
         LIMIT 1) latest_blood_pressure ON (true))
     LEFT JOIN facilities latest_blood_pressure_facility ON ((latest_blood_pressure_facility.id = latest_blood_pressure.facility_id)))
     LEFT JOIN LATERAL ( SELECT bs.id,
            bs.blood_sugar_type,
            bs.blood_sugar_value,
            bs.patient_id,
            bs.user_id,
            bs.facility_id,
            bs.device_created_at,
            bs.device_updated_at,
            bs.deleted_at,
            bs.recorded_at,
            bs.created_at,
            bs.updated_at
           FROM blood_sugars bs
          WHERE (bs.patient_id = p.id)
          ORDER BY bs.recorded_at DESC
         LIMIT 1) latest_blood_sugar ON (true))
     LEFT JOIN facilities latest_blood_sugar_facility ON ((latest_blood_sugar_facility.id = latest_blood_sugar.facility_id)))
     LEFT JOIN LATERAL ( SELECT bp_passport.id,
            bp_passport.identifier,
            bp_passport.identifier_type,
            bp_passport.patient_id,
            bp_passport.metadata_version,
            bp_passport.metadata,
            bp_passport.device_created_at,
            bp_passport.device_updated_at,
            bp_passport.deleted_at,
            bp_passport.created_at,
            bp_passport.updated_at
           FROM patient_business_identifiers bp_passport
          WHERE (((bp_passport.identifier_type)::text = 'simple_bp_passport'::text) AND (bp_passport.patient_id = p.id))
          ORDER BY bp_passport.device_created_at DESC
         LIMIT 1) latest_bp_passport ON (true))
     LEFT JOIN LATERAL ( SELECT a.id,
            a.patient_id,
            a.facility_id,
            a.scheduled_date,
            a.status,
            a.cancel_reason,
            a.device_created_at,
            a.device_updated_at,
            a.created_at,
            a.updated_at,
            a.remind_on,
            a.agreed_to_visit,
            a.deleted_at,
            a.appointment_type,
            a.user_id,
            a.creation_facility_id
           FROM appointments a
          WHERE (a.patient_id = p.id)
          ORDER BY a.device_created_at DESC
         LIMIT 1) next_appointment ON (true))
     LEFT JOIN facilities next_appointment_facility ON ((next_appointment_facility.id = next_appointment.facility_id)))
  WHERE (p.deleted_at IS NULL);

Columns

Relationships

Close relationships within degrees of separation

View Definition

Possibly Referenced Tables/Views