Data logistics system enabling real-time pathogen surveillance. Built for the Seattle Flu Study.


Pull out responses about receiving a flu shot and date of that vaccination.


  1. Remove need for digging into the JSON for queries about a flu shot and standardize valueset.

Sketch of a schema

 * There is often a distinction drawn between the terms immunization and
 * vaccination.  What we ask participants about is vaccination.  FHIR v4
 * uses immunization, but notes:
 *     While the terms "immunization" and "vaccination" are not clinically
 *     identical, for the purposes of the FHIR resources, the terms are used
 *     synonymously. 
create table vaccination (
    vaccination_id integer primary key generated by default as identity,
    encounter_id integer references encounter (encounter_id) not null,

    organism_id integer references organism (organism_id) not null,

    --  month (daterange)
    --   e.g. daterange('2018-12-01'::date, ('2018-12-01'::date + '1 month'::interval)::date);)
    -- received a vaccine for the given organism/taxon/pathogen during the given time period; may be a single day, a month, the past 12 months, etc.
    vaccinated daterange not null
        constraint vaccinated_daterange_is_bounded check (
                upper_inf(vaccinated) is false
            and lower_inf(vaccinated) is false),

    details jsonb

create index vaccination_details_idx on vaccination using gin (details jsonb_path_ops);

Prior art