Dumped on 2024-12-23
F-Key | Name | Type | Description |
---|---|---|---|
pk | serial | PRIMARY KEY | |
value | text | UNIQUE NOT NULL |
Tables referencing this one via Foreign Key Constraints:
patient allergy details
F-Key | Name | Type | Description |
---|---|---|---|
pk_audit | integer | NOT NULL DEFAULT nextval('audit.audit_fields_pk_audit_seq'::regclass) | |
row_version | integer | NOT NULL | |
modified_when | timestamp with time zone | NOT NULL DEFAULT now() | |
modified_by | name | NOT NULL DEFAULT "current_user"() | |
pk_item | integer | NOT NULL DEFAULT nextval('clin.clin_root_item_pk_item_seq'::regclass) | |
clin_when | timestamp with time zone | NOT NULL DEFAULT now() | |
encounter.pk | fk_encounter | integer | NOT NULL |
episode.pk | fk_episode | integer | NOT NULL |
narrative | text |
used as field "reaction" |
|
soap_cat | text | DEFAULT 'o'::text | |
pk | serial | PRIMARY KEY | |
substance | text |
NOT NULL
real-world name of substance the patient reacted to, brand name if drug |
|
substance_code | text |
data source specific opaque product code; must provide a link to a unique product/substance in the database in use; should follow the parseable convention of "<source>::<source version>::<identifier>", e.g. "MIMS::2003-1::190" for Zantac; it is left as an exercise to the application to know what to do with this information |
|
generics | text |
names of generic compounds if drug; brand names change/disappear, generic names do not |
|
allergene | text |
name of allergenic ingredient in substance if known |
|
atc_code | text |
ATC code of allergene or substance if approprate, applicable for penicilline, not so for cat fur |
|
_enum_allergy_type.pk | fk_type | integer |
NOT NULL
allergy/sensitivity |
generic_specific | boolean |
DEFAULT false
only meaningful for *drug*/*generic* reactions: 1) true: applies to one in "generics" forming "substance", if more than one generic listed in "generics" then "allergene" *must* contain the generic in question; 2) false: applies to drug class of "substance"; |
|
definite | boolean |
DEFAULT false
true: definite, false: not definite |
Table allergy Inherits clin_root_item,
Name | Constraint |
---|---|
audit_audit_fields_sane_modified_when | CHECK (((modified_when <= clock_timestamp()) IS TRUE)) |
clin_root_item_sane_soap_cat | CHECK (((soap_cat IS NULL) OR (lower(soap_cat) = ANY (ARRAY['s'::text, 'o'::text, 'a'::text, 'p'::text, 'u'::text])))) |
F-Key | Name | Type | Description |
---|---|---|---|
pk_audit | integer | NOT NULL DEFAULT nextval('audit.audit_fields_pk_audit_seq'::regclass) | |
row_version | integer | NOT NULL | |
modified_when | timestamp with time zone | NOT NULL DEFAULT now() | |
modified_by | name | NOT NULL DEFAULT "current_user"() | |
pk | serial | PRIMARY KEY | |
has_allergy | integer |
patient allergenic state: - null: unknown, not asked, no data available - -1: unknown, asked, no data obtained - 0: known, asked, has no known allergies - 1: known, asked, does have allergies |
|
comment | text |
A comment on the state, such as "patient says no allergies but I think he is holding back some". |
|
last_confirmed | timestamp with time zone |
When was the state of allergies last confirmed. Must be not NULL if has_allergy is not NULL. |
|
encounter.pk | fk_encounter | integer | NOT NULL |
Name | Constraint |
---|---|
allergy_state_check | CHECK (((has_allergy IS NULL) OR ((has_allergy IS NOT NULL) AND (last_confirmed IS NOT NULL)))) |
allergy_state_has_allergy_check | CHECK ((has_allergy = ANY (ARRAY[NULL::integer, 0, 1]))) |
audit_audit_fields_sane_modified_when | CHECK (((modified_when <= clock_timestamp()) IS TRUE)) |
Other tables link to this if they need more free text fields.
F-Key | Name | Type | Description |
---|---|---|---|
pk_audit | integer | NOT NULL DEFAULT nextval('audit.audit_fields_pk_audit_seq'::regclass) | |
row_version | integer | NOT NULL | |
modified_when | timestamp with time zone | NOT NULL DEFAULT now() | |
modified_by | name | NOT NULL DEFAULT "current_user"() | |
pk_item | integer | NOT NULL DEFAULT nextval('clin.clin_root_item_pk_item_seq'::regclass) | |
clin_when | timestamp with time zone | NOT NULL DEFAULT now() | |
encounter.pk | fk_encounter | integer | NOT NULL |
episode.pk | fk_episode | integer | NOT NULL |
narrative | text | ||
soap_cat | text | ||
pk | serial | PRIMARY KEY |
Table clin_aux_note Inherits clin_root_item,
Name | Constraint |
---|---|
audit_audit_fields_sane_modified_when | CHECK (((modified_when <= clock_timestamp()) IS TRUE)) |
clin_root_item_sane_soap_cat | CHECK (((soap_cat IS NULL) OR (lower(soap_cat) = ANY (ARRAY['s'::text, 'o'::text, 'a'::text, 'p'::text, 'u'::text])))) |
stores additional detail on those clin.clin_narrative rows where soap_cat=a that are true diagnoses, true diagnoses DO have a code from one of the coding systems
F-Key | Name | Type | Description |
---|---|---|---|
pk_audit | integer | NOT NULL DEFAULT nextval('audit.audit_fields_pk_audit_seq'::regclass) | |
row_version | integer | NOT NULL | |
modified_when | timestamp with time zone | NOT NULL DEFAULT now() | |
modified_by | name | NOT NULL DEFAULT "current_user"() | |
pk | serial | PRIMARY KEY | |
clin_narrative.pk | fk_narrative | integer | UNIQUE NOT NULL |
laterality | character(1) | DEFAULT NULL::bpchar | |
is_chronic | boolean |
NOT NULL
DEFAULT false
whether this diagnosis is chronic, eg. no complete cure is to be expected, regardless of whether it is *active* right now (think of active/non-active phases of Multiple Sclerosis which is sure chronic) |
|
is_active | boolean |
NOT NULL
DEFAULT true
whether diagnosis is currently active or dormant |
|
is_definite | boolean | NOT NULL DEFAULT false | |
clinically_relevant | boolean |
NOT NULL
DEFAULT true
whether this diagnosis is considered clinically relevant, eg. significant; currently active diagnoses are considered to always be relevant, while inactive ones may or may not be |
Name | Constraint |
---|---|
audit_audit_fields_sane_modified_when | CHECK (((modified_when <= clock_timestamp()) IS TRUE)) |
clin_diag_laterality_check | CHECK (((laterality = ANY (ARRAY['l'::bpchar, 'r'::bpchar, 'b'::bpchar, '?'::bpchar])) OR (laterality IS NULL))) |
if_active_then_relevant | CHECK (((is_active = false) OR ((is_active = true) AND (clinically_relevant = true)))) |
stores arbitrary types for tagging clinical items
F-Key | Name | Type | Description |
---|---|---|---|
pk_audit | integer | NOT NULL DEFAULT nextval('audit.audit_fields_pk_audit_seq'::regclass) | |
row_version | integer | NOT NULL | |
modified_when | timestamp with time zone | NOT NULL DEFAULT now() | |
modified_by | name | NOT NULL DEFAULT "current_user"() | |
pk | serial | PRIMARY KEY | |
type | text |
UNIQUE
NOT NULL
DEFAULT 'history'::text
the full name of the item type such as "family history" |
|
code | text |
UNIQUE
NOT NULL
DEFAULT 'Hx'::text
shorthand for the type, eg "FHx" |
Name | Constraint |
---|---|
audit_audit_fields_sane_modified_when | CHECK (((modified_when <= clock_timestamp()) IS TRUE)) |
Tables referencing this one via Foreign Key Constraints:
Used to store clinical free text *not* associated with any other table. Used to implement a simple SOAP structure. Also other tags can be associated via link tables.
F-Key | Name | Type | Description |
---|---|---|---|
pk_audit | integer | NOT NULL DEFAULT nextval('audit.audit_fields_pk_audit_seq'::regclass) | |
row_version | integer | NOT NULL | |
modified_when | timestamp with time zone | NOT NULL DEFAULT now() | |
modified_by | name | NOT NULL DEFAULT "current_user"() | |
pk_item | integer | NOT NULL DEFAULT nextval('clin.clin_root_item_pk_item_seq'::regclass) | |
clin_when | timestamp with time zone |
NOT NULL
DEFAULT now()
when did the item reach clinical reality |
|
encounter.pk | fk_encounter | integer | NOT NULL |
episode.pk | fk_episode | integer | NOT NULL |
narrative | text | ||
soap_cat | text | ||
pk | serial | PRIMARY KEY |
Table clin_narrative Inherits clin_root_item,
Name | Constraint |
---|---|
audit_audit_fields_sane_modified_when | CHECK (((modified_when <= clock_timestamp()) IS TRUE)) |
clin_root_item_sane_soap_cat | CHECK (((soap_cat IS NULL) OR (lower(soap_cat) = ANY (ARRAY['s'::text, 'o'::text, 'a'::text, 'p'::text, 'u'::text])))) |
narrative_neither_null_nor_empty | CHECK ((btrim(COALESCE(narrative, ''::text)) <> ''::text)) |
ancestor table for clinical items of any kind, basic unit of clinical information, do *not* store data in here directly, use child tables, contains all the clinical narrative aggregated for full text search, ancestor for all tables that want to store clinical free text
F-Key | Name | Type | Description |
---|---|---|---|
pk_audit | integer | NOT NULL DEFAULT nextval('audit.audit_fields_pk_audit_seq'::regclass) | |
row_version | integer | NOT NULL | |
modified_when | timestamp with time zone | NOT NULL DEFAULT now() | |
modified_by | name | NOT NULL DEFAULT "current_user"() | |
pk_item | serial |
PRIMARY KEY
the primary key, not named "id" or "pk" as usual since child tables will have "id"/"pk"-named primary keys already and we would get duplicate columns while inheriting from this table |
|
clin_when | timestamp with time zone |
NOT NULL
DEFAULT now()
when this clinical item became known, can be different from when it was entered into the system (= audit.audit_fields.modified_when) |
|
encounter.pk | fk_encounter | integer |
NOT NULL
the encounter this item belongs to |
episode.pk | fk_episode | integer |
NOT NULL
the episode this item belongs to |
narrative | text |
each clinical item by default inherits a free text field for clinical narrative |
|
soap_cat | text |
each clinical item must be either one of the S, O, A, P, U categories or NULL to indicate a non-clinical item, U meaning Unspecified-but-clinical |
Name | Constraint |
---|---|
audit_audit_fields_sane_modified_when | CHECK (((modified_when <= clock_timestamp()) IS TRUE)) |
clin_root_item_sane_soap_cat | CHECK (((soap_cat IS NULL) OR (lower(soap_cat) = ANY (ARRAY['s'::text, 'o'::text, 'a'::text, 'p'::text, 'u'::text])))) |
a clinical encounter between a person and the health care system
F-Key | Name | Type | Description |
---|---|---|---|
pk_audit | integer | NOT NULL DEFAULT nextval('audit.audit_fields_pk_audit_seq'::regclass) | |
row_version | integer | NOT NULL | |
modified_when | timestamp with time zone | NOT NULL DEFAULT now() | |
modified_by | name | NOT NULL DEFAULT "current_user"() | |
pk | serial | PRIMARY KEY | |
patient.fk_identity | fk_patient | integer |
NOT NULL
PK of subject of care, should be PUPIC, actually |
encounter_type.pk | fk_type | integer |
NOT NULL
DEFAULT 1
PK of type of this encounter |
fk_location | integer |
NOT NULL
PK of location *of care*, e.g. where the provider is at |
|
source_time_zone | interval |
time zone of location, used to approximate source time zone for all timestamps in this encounter |
|
reason_for_encounter | text |
the RFE for the encounter as related by either the patient or the provider (say, in a chart review) |
|
assessment_of_encounter | text |
the Assessment of Encounter (eg consultation summary) as determined by the provider, may simply be a concatenation of soAp narrative, this assessment should go across all problems |
|
started | timestamp with time zone | NOT NULL DEFAULT now() | |
last_affirmed | timestamp with time zone | NOT NULL DEFAULT now() |
Name | Constraint |
---|---|
audit_audit_fields_sane_modified_when | CHECK (((modified_when <= clock_timestamp()) IS TRUE)) |
clin_enc_sane_duration | CHECK ((last_affirmed >= started)) |
encounter_assessment_of_encounter_check | CHECK ((btrim(COALESCE(assessment_of_encounter, 'xxxDEFAULTxxx'::text)) <> ''::text)) |
encounter_reason_for_encounter_check | CHECK ((btrim(COALESCE(reason_for_encounter, 'xxxDEFAULTxxx'::text)) <> ''::text)) |
Tables referencing this one via Foreign Key Constraints:
these are the types of encounter
F-Key | Name | Type | Description |
---|---|---|---|
pk | serial | PRIMARY KEY | |
description | text | UNIQUE NOT NULL |
Tables referencing this one via Foreign Key Constraints:
Clinical episodes such as "Otitis media", "traffic accident 7/99", "Hepatitis B". This covers a range of time in which activity of illness was noted for the problem episode.description.
F-Key | Name | Type | Description |
---|---|---|---|
pk_audit | integer | NOT NULL DEFAULT nextval('audit.audit_fields_pk_audit_seq'::regclass) | |
row_version | integer | NOT NULL | |
modified_when | timestamp with time zone | NOT NULL DEFAULT now() | |
modified_by | name | NOT NULL DEFAULT "current_user"() | |
pk | serial | PRIMARY KEY | |
health_issue.pk | fk_health_issue | integer |
health issue this episode belongs to |
description | text |
NOT NULL
description/name of this episode |
|
is_open | boolean |
DEFAULT true
whether the episode is open (eg. there is activity for it), means open in a temporal sense as in "not closed yet"; only one episode can be open per health issue |
|
encounter.pk | fk_encounter | integer |
NOT NULL
The encounter during which this episode was added (begun). |
diagnostic_certainty_classification | text |
The certainty at which this problem is believed to be a diagnosis: A: sign (Symptom) B: cluster of signs (Symptomkomplex) C: syndromic diagnosis (Bild einer Diagnose) D: proven diagnosis (diagnostisch gesichert) |
|
summary | text |
Used for tracking the summary of this episode. |
Name | Constraint |
---|---|
audit_audit_fields_sane_modified_when | CHECK (((modified_when <= clock_timestamp()) IS TRUE)) |
episode_sane_summary | CHECK (gm.is_null_or_non_empty_string(summary)) |
sane_description | CHECK ((gm.is_null_or_blank_string(description) IS FALSE)) |
valid_diagnostic_certainty_classification | CHECK ((diagnostic_certainty_classification = ANY (ARRAY['A'::text, 'B'::text, 'C'::text, 'D'::text, NULL::text]))) |
Tables referencing this one via Foreign Key Constraints:
A table to hold binary data for patients intended for export as print/mail/fax/removable media/...
F-Key | Name | Type | Description |
---|---|---|---|
pk | serial | PRIMARY KEY | |
patient.fk_identity | fk_identity | integer |
UNIQUE#1
the patient this item pertains to, DELETE does not cascade because we may have wanted to export data before deleting a patient ... |
created_by | name |
NOT NULL
DEFAULT "current_user"()
who put this item here |
|
created_when | timestamp with time zone |
NOT NULL
DEFAULT statement_timestamp()
when was this item put here |
|
designation | text |
the intended use for this item if any, say "print" for printing |
|
description | text |
UNIQUE#1
a unique-per-patient description of the item |
|
fk_doc_obj | integer |
UNIQUE
points to a document object |
|
data | bytea |
binary data representing the actual export item (unless fk_doc_obj points to a document object) |
|
filename | text |
a filename, possibly from an import, if applicable, mainly used to please non-mime pseudo operating systems |
Name | Constraint |
---|---|
clin_export_item_fk_obj_or_data | CHECK ((((data IS NULL) AND (fk_doc_obj IS NOT NULL)) OR ((data IS NOT NULL) AND (fk_doc_obj IS NULL)))) |
clin_export_item_fk_obj_or_filename | CHECK ((((filename IS NULL) AND (fk_doc_obj IS NOT NULL)) OR ((filename IS NOT NULL) AND (fk_doc_obj IS NULL)))) |
clin_export_item_fk_obj_or_fk_identity | CHECK ((((fk_identity IS NULL) AND (fk_doc_obj IS NOT NULL)) OR ((fk_identity IS NOT NULL) AND (fk_doc_obj IS NULL)))) |
clin_export_item_sane_by | CHECK ((length((created_by)::text) > 0)) |
clin_export_item_sane_data | CHECK (((data IS NULL) OR (length(data) > 0))) |
clin_export_item_sane_description | CHECK ((gm.is_null_or_blank_string(description) IS FALSE)) |
clin_export_item_sane_designation | CHECK ((gm.is_null_or_non_empty_string(designation) IS TRUE)) |
clin_export_item_sane_filename | CHECK ((gm.is_null_or_non_empty_string(filename) IS TRUE)) |
lists external care situations for patients
F-Key | Name | Type | Description |
---|---|---|---|
pk_audit | integer | NOT NULL DEFAULT nextval('audit.audit_fields_pk_audit_seq'::regclass) | |
row_version | integer | NOT NULL | |
modified_when | timestamp with time zone | NOT NULL DEFAULT now() | |
modified_by | name | NOT NULL DEFAULT "current_user"() | |
pk | serial | PRIMARY KEY | |
encounter.pk | fk_encounter | integer |
NOT NULL
the encounter during which this external care item was first documented |
health_issue.pk | fk_health_issue | integer |
UNIQUE#1
link to a health issue, if any |
issue | text |
description of the issue of external care |
|
fk_org_unit | integer |
UNIQUE#1
NOT NULL
link to the org unit where care is rendered |
|
provider | text |
name of actual provider at .fk_org_unit |
|
comment | text |
comment on the patient/provider relationship, say role/issues cared for/... |
Name | Constraint |
---|---|
audit_audit_fields_sane_modified_when | CHECK (((modified_when <= clock_timestamp()) IS TRUE)) |
clin_ext_care_issue_xor_fk_issue | CHECK ((((fk_health_issue IS NULL) AND (issue IS NOT NULL)) OR ((fk_health_issue IS NOT NULL) AND (issue IS NULL)))) |
clin_ext_care_sane_comment | CHECK ((gm.is_null_or_non_empty_string(comment) IS TRUE)) |
clin_ext_care_sane_issue | CHECK ((gm.is_null_or_non_empty_string(issue) IS TRUE)) |
clin_ext_care_sane_provider | CHECK ((gm.is_null_or_non_empty_string(provider) IS TRUE)) |
This table stores family history items on persons not otherwise in the database.
F-Key | Name | Type | Description |
---|---|---|---|
pk_audit | integer | NOT NULL DEFAULT nextval('audit.audit_fields_pk_audit_seq'::regclass) | |
row_version | integer | NOT NULL | |
modified_when | timestamp with time zone | NOT NULL DEFAULT now() | |
modified_by | name | NOT NULL DEFAULT "current_user"() | |
pk_item | integer | NOT NULL DEFAULT nextval('clin.clin_root_item_pk_item_seq'::regclass) | |
clin_when | timestamp with time zone |
NOT NULL
DEFAULT now()
When the family history item became known to the patient (not the afflicted relative). |
|
encounter.pk | fk_encounter | integer |
NOT NULL
encounter during which family history item became known |
episode.pk | fk_episode | integer |
NOT NULL
episode to which family history item is of importance |
narrative | text |
the condition this relative suffered from |
|
soap_cat | text | ||
pk | serial | PRIMARY KEY | |
fhx_relation_type.pk | fk_relation_type | integer |
NOT NULL
foreign key to the type of relation the patient has to the afflicated relative |
age_noted | text |
age at which the condition was noted in the relative if known |
|
age_of_death | interval |
age at which the relative died if known |
|
contributed_to_death | boolean |
whether the condition contributed to the death of the relative if known |
|
name_relative | text |
name of the relative suffering the condition |
|
dob_relative | timestamp with time zone |
date of birth of the relative if known |
|
comment | text |
Table family_history Inherits clin_root_item,
Name | Constraint |
---|---|
audit_audit_fields_sane_modified_when | CHECK (((modified_when <= clock_timestamp()) IS TRUE)) |
c_family_history_sane_age_noted | CHECK ((gm.is_null_or_non_empty_string(age_noted) IS TRUE)) |
c_family_history_sane_condition | CHECK ((gm.is_null_or_blank_string(narrative) IS FALSE)) |
c_family_history_sane_name | CHECK ((gm.is_null_or_non_empty_string(name_relative) IS TRUE)) |
clin_root_item_sane_soap_cat | CHECK (((soap_cat IS NULL) OR (lower(soap_cat) = ANY (ARRAY['s'::text, 'o'::text, 'a'::text, 'p'::text, 'u'::text])))) |
narrative_neither_null_nor_empty | CHECK ((btrim(COALESCE(narrative, ''::text)) <> ''::text)) |
Tables referencing this one via Foreign Key Constraints:
Enumerates inter-person relations for family history linking.
F-Key | Name | Type | Description |
---|---|---|---|
pk_audit | integer | NOT NULL DEFAULT nextval('audit.audit_fields_pk_audit_seq'::regclass) | |
row_version | integer | NOT NULL | |
modified_when | timestamp with time zone | NOT NULL DEFAULT now() | |
modified_by | name | NOT NULL DEFAULT "current_user"() | |
pk | serial | PRIMARY KEY | |
description | text |
UNIQUE#1
Description of the relation type, specific or unspecific: sister, father, ..., maternal family, ... |
|
is_genetic | boolean |
UNIQUE#1
NOT NULL
Whether or not this type of relation is biologic/genetic or not. Note that non-genetic relations may still pose a risk because of infectious diseases. |
Name | Constraint |
---|---|
audit_audit_fields_sane_modified_when | CHECK (((modified_when <= clock_timestamp()) IS TRUE)) |
c_fhx_relation_type_sane_desc | CHECK ((gm.is_null_or_blank_string(description) IS FALSE)) |
Tables referencing this one via Foreign Key Constraints:
holds the values used in form instances, for later re-use/validation
F-Key | Name | Type | Description |
---|---|---|---|
pk_audit | integer | NOT NULL DEFAULT nextval('audit.audit_fields_pk_audit_seq'::regclass) | |
row_version | integer | NOT NULL | |
modified_when | timestamp with time zone | NOT NULL DEFAULT now() | |
modified_by | name | NOT NULL DEFAULT "current_user"() | |
pk | serial | PRIMARY KEY | |
form_instances.pk | fk_instance | integer |
UNIQUE#1
NOT NULL
the form instance this value was used in |
fk_form_field | integer |
UNIQUE#1
NOT NULL
points to the definition of the field in the form which in turn defines the place holder in the template to replace with <value> |
|
value | text |
NOT NULL
the value to replace the place holder with |
Name | Constraint |
---|---|
audit_audit_fields_sane_modified_when | CHECK (((modified_when <= clock_timestamp()) IS TRUE)) |
instances of forms, like a log of all processed forms
F-Key | Name | Type | Description |
---|---|---|---|
pk_audit | integer | NOT NULL DEFAULT nextval('audit.audit_fields_pk_audit_seq'::regclass) | |
row_version | integer | NOT NULL | |
modified_when | timestamp with time zone | NOT NULL DEFAULT now() | |
modified_by | name | NOT NULL DEFAULT "current_user"() | |
pk_item | integer | NOT NULL DEFAULT nextval('clin.clin_root_item_pk_item_seq'::regclass) | |
clin_when | timestamp with time zone | NOT NULL DEFAULT now() | |
encounter.pk | fk_encounter | integer | NOT NULL |
episode.pk | fk_episode | integer | NOT NULL |
narrative | text |
can be used as a status field, eg. "printed", "faxed" etc. |
|
soap_cat | text | ||
pk | serial | PRIMARY KEY | |
fk_form_def | integer |
NOT NULL
points to the definition of this instance, this FK will fail once we start separating services, make it into a x_db_fk then |
|
form_name | text |
NOT NULL
a string uniquely identifying the form template, necessary for the audit trail |
Table form_instances Inherits clin_root_item,
Name | Constraint |
---|---|
audit_audit_fields_sane_modified_when | CHECK (((modified_when <= clock_timestamp()) IS TRUE)) |
clin_root_item_sane_soap_cat | CHECK (((soap_cat IS NULL) OR (lower(soap_cat) = ANY (ARRAY['s'::text, 'o'::text, 'a'::text, 'p'::text, 'u'::text])))) |
form_is_plan | CHECK ((soap_cat = 'p'::text)) |
Tables referencing this one via Foreign Key Constraints:
This is pretty much what others would call "Past Medical History" or "Foundational illness", eg. longer-ranging, underlying, encompassing issues with one's health such as "immunodeficiency", "type 2 diabetes". In Belgium it is called "problem". L.L.Weed includes lots of little things into it, we do not.
F-Key | Name | Type | Description |
---|---|---|---|
pk_audit | integer | NOT NULL DEFAULT nextval('audit.audit_fields_pk_audit_seq'::regclass) | |
row_version | integer | NOT NULL | |
modified_when | timestamp with time zone | NOT NULL DEFAULT now() | |
modified_by | name | NOT NULL DEFAULT "current_user"() | |
pk | serial | PRIMARY KEY | |
description | text |
NOT NULL
descriptive name of this health issue, may change over time as evidence increases |
|
laterality | character varying(2) |
DEFAULT NULL::character varying
NULL: don't know s: sinister - left d: dexter - right sd: sinister and dexter - both sides na: not applicable |
|
age_noted | interval |
at what age the patient acquired the condition |
|
is_active | boolean |
DEFAULT true
whether this health issue (problem) is active |
|
clinically_relevant | boolean |
DEFAULT true
whether this health issue (problem) has any clinical relevance |
|
is_confidential | boolean | DEFAULT false | |
is_cause_of_death | boolean | NOT NULL DEFAULT false | |
encounter.pk | fk_encounter | integer |
NOT NULL
The encounter during which this health issue was added. |
grouping | text |
This can be used to entirely arbitrarily group health issues felt to belong to each other. |
|
diagnostic_certainty_classification | text |
The certainty at which this issue is believed to be a diagnosis: A: sign (Symptom) B: cluster of signs (Symptomkomplex) C: syndromic diagnosis (Bild einer Diagnose) D: proven diagnosis (diagnostisch gesichert) |
|
summary | text |
Used for tracking the summary of this health issue. |
Name | Constraint |
---|---|
audit_audit_fields_sane_modified_when | CHECK (((modified_when <= clock_timestamp()) IS TRUE)) |
health_issue_laterality_check | CHECK (((laterality)::text = ANY (ARRAY[(NULL::character varying)::text, ('s'::character varying)::text, ('d'::character varying)::text, ('sd'::character varying)::text, ('na'::character varying)::text]))) |
health_issue_sane_summary | CHECK (gm.is_null_or_non_empty_string(summary)) |
issue_name_not_empty | CHECK ((btrim(description) <> ''::text)) |
sane_grouping | CHECK (gm.is_null_or_non_empty_string("grouping")) |
valid_diagnostic_certainty_classification | CHECK ((diagnostic_certainty_classification = ANY (ARRAY['A'::text, 'B'::text, 'C'::text, 'D'::text, NULL::text]))) |
collects data on hospitalisations of patients, reasons are linked via a link table
F-Key | Name | Type | Description |
---|---|---|---|
pk_audit | integer | NOT NULL DEFAULT nextval('audit.audit_fields_pk_audit_seq'::regclass) | |
row_version | integer | NOT NULL | |
modified_when | timestamp with time zone | NOT NULL DEFAULT now() | |
modified_by | name | NOT NULL DEFAULT "current_user"() | |
pk_item | integer | NOT NULL DEFAULT nextval('clin.clin_root_item_pk_item_seq'::regclass) | |
clin_when | timestamp with time zone |
NOT NULL
DEFAULT now()
to be used as when the patient was admitted |
|
encounter.pk | fk_encounter | integer | NOT NULL |
episode.pk | fk_episode | integer | NOT NULL |
narrative | text |
a comment on the hospital stay |
|
soap_cat | text | ||
pk | serial | PRIMARY KEY | |
discharge | timestamp with time zone |
when was the patient discharged |
|
fk_org_unit | integer |
NOT NULL
links to the hospital the patient was admitted to |
Table hospital_stay Inherits clin_root_item,
Name | Constraint |
---|---|
audit_audit_fields_sane_modified_when | CHECK (((modified_when <= clock_timestamp()) IS TRUE)) |
clin_root_item_sane_soap_cat | CHECK (((soap_cat IS NULL) OR (lower(soap_cat) = ANY (ARRAY['s'::text, 'o'::text, 'a'::text, 'p'::text, 'u'::text])))) |
sane_duration | CHECK (((discharge IS NULL) OR (discharge > clin_when))) |
sane_hospital | CHECK (gm.is_null_or_non_empty_string(narrative)) |
Tables referencing this one via Foreign Key Constraints:
this table holds test results that could not be matched to any patient, it is intended to prevent overflow of incoming_data_unmatched with unmatchable data
F-Key | Name | Type | Description |
---|---|---|---|
pk_audit | integer | NOT NULL DEFAULT nextval('audit.audit_fields_pk_audit_seq'::regclass) | |
row_version | integer | NOT NULL | |
modified_when | timestamp with time zone | NOT NULL DEFAULT now() | |
modified_by | name | NOT NULL DEFAULT "current_user"() | |
pk | serial | PRIMARY KEY | |
fk_patient_candidates | integer[] | ||
request_id | text | ||
firstnames | text | ||
lastnames | text | ||
dob | date | ||
postcode | text | ||
other_info | text | ||
type | text | ||
data | bytea | NOT NULL | |
gender | text | ||
requestor | text | ||
external_data_id | text | ||
fk_identity_disambiguated | integer |
Name | Constraint |
---|---|
audit_audit_fields_sane_modified_when | CHECK (((modified_when <= clock_timestamp()) IS TRUE)) |
this table holds incoming data (lab results, documents) that could not be matched to one single patient automatically, it is intended to facilitate manual matching, - use "modified_when" for import time
F-Key | Name | Type | Description |
---|---|---|---|
pk_audit | integer | NOT NULL DEFAULT nextval('audit.audit_fields_pk_audit_seq'::regclass) | |
row_version | integer | NOT NULL | |
modified_when | timestamp with time zone | NOT NULL DEFAULT now() | |
modified_by | name | NOT NULL DEFAULT "current_user"() | |
pk | serial | PRIMARY KEY | |
fk_patient_candidates | integer[] |
a matching algorithm can be applied to produce a list of likely candidate patients, the question remains whether this should not be done at runtime |
|
request_id | text |
request ID as found in <data> |
|
firstnames | text |
first names as found in <data> |
|
lastnames | text |
last names as found in <data> |
|
dob | date |
date of birth as found in <data> |
|
postcode | text |
postcode as found in <data> |
|
other_info | text |
other identifying information as found in <data> |
|
type | text |
the type of <data>, eg HL7, LDT, ..., useful for selecting an importer |
|
data | bytea |
NOT NULL
the raw data |
|
gender | text |
Gender of patient in source data if available. |
|
requestor | text |
Requestor of data (e.g. who ordered test results) if available in source data. |
|
external_data_id | text |
ID of content of .data in external system (e.g. importer) where appropriate |
|
patient.fk_identity | fk_identity_disambiguated | integer |
ID of patient the data is judged to really belong to. |
comment | text |
a free text comment on this row, eg. why is it here |
|
fk_provider_disambiguated | integer |
The provider the data is relevant to. |
Name | Constraint |
---|---|
audit_audit_fields_sane_modified_when | CHECK (((modified_when <= clock_timestamp()) IS TRUE)) |
unmatched_data_sane_comment | CHECK ((gm.is_null_or_non_empty_string(comment) IS TRUE)) |
test request metadata
F-Key | Name | Type | Description |
---|---|---|---|
pk_audit | integer | NOT NULL DEFAULT nextval('audit.audit_fields_pk_audit_seq'::regclass) | |
row_version | integer | NOT NULL | |
modified_when | timestamp with time zone | NOT NULL DEFAULT now() | |
modified_by | name | NOT NULL DEFAULT "current_user"() | |
pk_item | integer | NOT NULL DEFAULT nextval('clin.clin_root_item_pk_item_seq'::regclass) | |
clin_when | timestamp with time zone |
NOT NULL
DEFAULT now()
the time the sample for this request was taken LDT: 8432:8433 HL7: OBR.quantity_timing |
|
encounter.pk | fk_encounter | integer | NOT NULL |
episode.pk | fk_episode | integer | NOT NULL |
narrative | text |
free text comment on request |
|
soap_cat | text | DEFAULT 'p'::text | |
pk | serial | PRIMARY KEY | |
test_org.pk | fk_test_org | integer | UNIQUE#1 |
request_id | text |
UNIQUE#1
NOT NULL
ID this request had when sent to the lab LDT: 8310 HL7: OBR.filler_order_number |
|
fk_requestor | integer |
who requested the test - really needed ? |
|
lab_request_id | text |
ID this request had internally at the lab LDT: 8311 |
|
lab_rxd_when | timestamp with time zone |
when did the lab receive the request+sample LDT: 8301 HL7: OBR.requested_date_time |
|
results_reported_when | timestamp with time zone |
when was the report on the result generated, LDT: 8302 HL7: OBR.results_report_status_change |
|
request_status | text |
NOT NULL
pending, final, preliminary, partial LDT: 8401 |
|
is_pending | boolean |
NOT NULL
DEFAULT true
true if any (even partial) results are still pending |
|
diagnostic_service_section | text |
The (section of) the diagnostic service which performed the test. - HL7 2.3: OBR:24 Diagnostic Service Section ID - somewhat redundant with fk_test_org, which, however, points to more normalized data |
|
ordered_service | text |
The (battery of) test(s)/service(s) ordered. - HL7 2.3: OBR:4 Universal Service ID |
|
note_test_org | text |
A comment on this lab request by the performing organization (lab). |
Table lab_request Inherits clin_root_item,
Name | Constraint |
---|---|
audit_audit_fields_sane_modified_when | CHECK (((modified_when <= clock_timestamp()) IS TRUE)) |
clin_lab_req_sane_test_org_note | CHECK ((gm.is_null_or_non_empty_string(note_test_org) IS TRUE)) |
clin_root_item_sane_soap_cat | CHECK (((soap_cat IS NULL) OR (lower(soap_cat) = ANY (ARRAY['s'::text, 'o'::text, 'a'::text, 'p'::text, 'u'::text])))) |
lab_request_request_id_check | CHECK ((btrim(request_id) <> ''::text)) |
lab_request_request_status_check | CHECK ((request_status = ANY (ARRAY['pending'::text, 'preliminary'::text, 'partial'::text, 'final'::text]))) |
Tables referencing this one via Foreign Key Constraints:
Links codes to encounter.aoe.
F-Key | Name | Type | Description |
---|---|---|---|
pk_audit | integer | NOT NULL DEFAULT nextval('audit.audit_fields_pk_audit_seq'::regclass) | |
row_version | integer | NOT NULL | |
modified_when | timestamp with time zone | NOT NULL DEFAULT now() | |
modified_by | name | NOT NULL DEFAULT "current_user"() | |
pk_lnk_code2item | integer | NOT NULL DEFAULT nextval('clin.lnk_code2item_root_pk_lnk_code2item_seq'::regclass) | |
fk_generic_code | integer |
UNIQUE#1
NOT NULL
Custom foreign key to ref.coding_system_root. |
|
encounter.pk | fk_item | integer |
UNIQUE#1
Foreign key to clin.encounter |
code_modifier | text | ||
pk | serial | PRIMARY KEY |
Table lnk_code2aoe Inherits lnk_code2item_root,
Name | Constraint |
---|---|
audit_audit_fields_sane_modified_when | CHECK (((modified_when <= clock_timestamp()) IS TRUE)) |
Links codes to episodes.
F-Key | Name | Type | Description |
---|---|---|---|
pk_audit | integer | NOT NULL DEFAULT nextval('audit.audit_fields_pk_audit_seq'::regclass) | |
row_version | integer | NOT NULL | |
modified_when | timestamp with time zone | NOT NULL DEFAULT now() | |
modified_by | name | NOT NULL DEFAULT "current_user"() | |
pk_lnk_code2item | integer | NOT NULL DEFAULT nextval('clin.lnk_code2item_root_pk_lnk_code2item_seq'::regclass) | |
fk_generic_code | integer |
UNIQUE#1
NOT NULL
Custom foreign key to ref.coding_system_root. |
|
episode.pk | fk_item | integer |
UNIQUE#1
Foreign key to clin.episode |
code_modifier | text | ||
pk | serial | PRIMARY KEY |
Table lnk_code2episode Inherits lnk_code2item_root,
Name | Constraint |
---|---|
audit_audit_fields_sane_modified_when | CHECK (((modified_when <= clock_timestamp()) IS TRUE)) |
Links codes to family history items.
F-Key | Name | Type | Description |
---|---|---|---|
pk_audit | integer | NOT NULL DEFAULT nextval('audit.audit_fields_pk_audit_seq'::regclass) | |
row_version | integer | NOT NULL | |
modified_when | timestamp with time zone | NOT NULL DEFAULT now() | |
modified_by | name | NOT NULL DEFAULT "current_user"() | |
pk_lnk_code2item | integer | NOT NULL DEFAULT nextval('clin.lnk_code2item_root_pk_lnk_code2item_seq'::regclass) | |
fk_generic_code | integer |
UNIQUE#1
NOT NULL
Custom foreign key to ref.coding_system_root. |
|
family_history.pk | fk_item | integer |
UNIQUE#1
Foreign key to clin.family_history |
code_modifier | text | ||
pk | serial | PRIMARY KEY |
Table lnk_code2fhx Inherits lnk_code2item_root,
Name | Constraint |
---|---|
audit_audit_fields_sane_modified_when | CHECK (((modified_when <= clock_timestamp()) IS TRUE)) |
Links codes to health issues.
F-Key | Name | Type | Description |
---|---|---|---|
pk_audit | integer | NOT NULL DEFAULT nextval('audit.audit_fields_pk_audit_seq'::regclass) | |
row_version | integer | NOT NULL | |
modified_when | timestamp with time zone | NOT NULL DEFAULT now() | |
modified_by | name | NOT NULL DEFAULT "current_user"() | |
pk_lnk_code2item | integer | NOT NULL DEFAULT nextval('clin.lnk_code2item_root_pk_lnk_code2item_seq'::regclass) | |
fk_generic_code | integer |
UNIQUE#1
NOT NULL
Custom foreign key to ref.coding_system_root. |
|
health_issue.pk | fk_item | integer |
UNIQUE#1
Foreign key to clin.health_issue |
code_modifier | text | ||
pk | serial | PRIMARY KEY |
Table lnk_code2h_issue Inherits lnk_code2item_root,
Name | Constraint |
---|---|
audit_audit_fields_sane_modified_when | CHECK (((modified_when <= clock_timestamp()) IS TRUE)) |
The parent table of all tables linking codes to EMR structures. Coding tables check this table for existence of their PK in .fk_generic_code in order to prevent cascading DELETEs/UPDATEs from breaking referential integrity. EMR structure tables foreign key into children of this table in order to link structures to codes.
F-Key | Name | Type | Description |
---|---|---|---|
pk_audit | integer | NOT NULL DEFAULT nextval('audit.audit_fields_pk_audit_seq'::regclass) | |
row_version | integer | NOT NULL | |
modified_when | timestamp with time zone | NOT NULL DEFAULT now() | |
modified_by | name | NOT NULL DEFAULT "current_user"() | |
pk_lnk_code2item | serial | PRIMARY KEY | |
fk_generic_code | integer |
Custom foreign key to ref.coding_system_root.pk_coding_system. |
|
fk_item | integer |
Foreign key to clin.* tables |
|
code_modifier | text |
Usage specific modifier on the base code, say, certainty or laterality of ICD-10 codes. |
Name | Constraint |
---|---|
audit_audit_fields_sane_modified_when | CHECK (((modified_when <= clock_timestamp()) IS TRUE)) |
Links codes to SOAP narrative.
F-Key | Name | Type | Description |
---|---|---|---|
pk_audit | integer | NOT NULL DEFAULT nextval('audit.audit_fields_pk_audit_seq'::regclass) | |
row_version | integer | NOT NULL | |
modified_when | timestamp with time zone | NOT NULL DEFAULT now() | |
modified_by | name | NOT NULL DEFAULT "current_user"() | |
pk_lnk_code2item | integer | NOT NULL DEFAULT nextval('clin.lnk_code2item_root_pk_lnk_code2item_seq'::regclass) | |
fk_generic_code | integer |
UNIQUE#1
NOT NULL
Custom foreign key to ref.coding_system_root. |
|
clin_narrative.pk | fk_item | integer |
UNIQUE#1
Foreign key to clin.clin_narrative |
code_modifier | text | ||
pk | serial | PRIMARY KEY |
Table lnk_code2narrative Inherits lnk_code2item_root,
Name | Constraint |
---|---|
audit_audit_fields_sane_modified_when | CHECK (((modified_when <= clock_timestamp()) IS TRUE)) |
Links codes to procedures. OPS comes to mind.
F-Key | Name | Type | Description |
---|---|---|---|
pk_audit | integer | NOT NULL DEFAULT nextval('audit.audit_fields_pk_audit_seq'::regclass) | |
row_version | integer | NOT NULL | |
modified_when | timestamp with time zone | NOT NULL DEFAULT now() | |
modified_by | name | NOT NULL DEFAULT "current_user"() | |
pk_lnk_code2item | integer | NOT NULL DEFAULT nextval('clin.lnk_code2item_root_pk_lnk_code2item_seq'::regclass) | |
fk_generic_code | integer |
UNIQUE#1
NOT NULL
Custom foreign key to ref.coding_system_root. |
|
procedure.pk | fk_item | integer |
UNIQUE#1
Foreign key to clin.procedure |
code_modifier | text | ||
pk | serial | PRIMARY KEY |
Table lnk_code2procedure Inherits lnk_code2item_root,
Name | Constraint |
---|---|
audit_audit_fields_sane_modified_when | CHECK (((modified_when <= clock_timestamp()) IS TRUE)) |
Links codes to encounters.
F-Key | Name | Type | Description |
---|---|---|---|
pk_audit | integer | NOT NULL DEFAULT nextval('audit.audit_fields_pk_audit_seq'::regclass) | |
row_version | integer | NOT NULL | |
modified_when | timestamp with time zone | NOT NULL DEFAULT now() | |
modified_by | name | NOT NULL DEFAULT "current_user"() | |
pk_lnk_code2item | integer | NOT NULL DEFAULT nextval('clin.lnk_code2item_root_pk_lnk_code2item_seq'::regclass) | |
fk_generic_code | integer |
UNIQUE#1
NOT NULL
Custom foreign key to ref.coding_system_root. |
|
encounter.pk | fk_item | integer |
UNIQUE#1
Foreign key to clin.encounter |
code_modifier | text | ||
pk | serial | PRIMARY KEY |
Table lnk_code2rfe Inherits lnk_code2item_root,
Name | Constraint |
---|---|
audit_audit_fields_sane_modified_when | CHECK (((modified_when <= clock_timestamp()) IS TRUE)) |
Links codes to test panels.
F-Key | Name | Type | Description |
---|---|---|---|
pk_audit | integer | NOT NULL DEFAULT nextval('audit.audit_fields_pk_audit_seq'::regclass) | |
row_version | integer | NOT NULL | |
modified_when | timestamp with time zone | NOT NULL DEFAULT now() | |
modified_by | name | NOT NULL DEFAULT "current_user"() | |
pk_lnk_code2item | integer | NOT NULL DEFAULT nextval('clin.lnk_code2item_root_pk_lnk_code2item_seq'::regclass) | |
fk_generic_code | integer |
UNIQUE#1
NOT NULL
Custom foreign key to ref.coding_system_root. |
|
test_panel.pk | fk_item | integer |
UNIQUE#1
Foreign key to clin.test_panel |
code_modifier | text | ||
pk | serial | PRIMARY KEY |
Table lnk_code2tst_pnl Inherits lnk_code2item_root,
Name | Constraint |
---|---|
audit_audit_fields_sane_modified_when | CHECK (((modified_when <= clock_timestamp()) IS TRUE)) |
links constraints to courses
F-Key | Name | Type | Description |
---|---|---|---|
pk_audit | integer | NOT NULL DEFAULT nextval('audit.audit_fields_pk_audit_seq'::regclass) | |
row_version | integer | NOT NULL | |
modified_when | timestamp with time zone | NOT NULL DEFAULT now() | |
modified_by | name | NOT NULL DEFAULT "current_user"() | |
pk | serial | PRIMARY KEY | |
vaccination_course.pk | fk_vaccination_course | integer | UNIQUE#1 NOT NULL |
vaccination_course_constraint.pk | fk_constraint | integer | UNIQUE#1 NOT NULL |
Name | Constraint |
---|---|
audit_audit_fields_sane_modified_when | CHECK (((modified_when <= clock_timestamp()) IS TRUE)) |
links patients to vaccination courses they are actually on, this allows for per-patient selection of courses to be followed, eg. children at different ages may be on different vaccination courses or some people are on a course due to a trip abroad while most others are not
F-Key | Name | Type | Description |
---|---|---|---|
pk_audit | integer | NOT NULL DEFAULT nextval('audit.audit_fields_pk_audit_seq'::regclass) | |
row_version | integer | NOT NULL | |
modified_when | timestamp with time zone | NOT NULL DEFAULT now() | |
modified_by | name | NOT NULL DEFAULT "current_user"() | |
pk | serial | PRIMARY KEY | |
patient.fk_identity | fk_patient | integer | UNIQUE#1 NOT NULL |
vaccination_course.pk | fk_course | integer | UNIQUE#1 NOT NULL |
Name | Constraint |
---|---|
audit_audit_fields_sane_modified_when | CHECK (((modified_when <= clock_timestamp()) IS TRUE)) |
F-Key | Name | Type | Description |
---|---|---|---|
pk_audit | integer | NOT NULL DEFAULT nextval('audit.audit_fields_pk_audit_seq'::regclass) | |
row_version | integer | NOT NULL | |
modified_when | timestamp with time zone | NOT NULL DEFAULT now() | |
modified_by | name | NOT NULL DEFAULT "current_user"() | |
pk | serial | PRIMARY KEY | |
episode.pk | fk_episode | integer | |
substance_intake.pk | fk_substance | integer |
Name | Constraint |
---|---|
audit_audit_fields_sane_modified_when | CHECK (((modified_when <= clock_timestamp()) IS TRUE)) |
links test result evaluation norms to tests
F-Key | Name | Type | Description |
---|---|---|---|
pk_audit | integer | NOT NULL DEFAULT nextval('audit.audit_fields_pk_audit_seq'::regclass) | |
row_version | integer | NOT NULL | |
modified_when | timestamp with time zone | NOT NULL DEFAULT now() | |
modified_by | name | NOT NULL DEFAULT "current_user"() | |
id | serial | PRIMARY KEY | |
test_type.pk | id_test | integer |
UNIQUE#1
NOT NULL
which test does the linked norm apply to |
id_norm | integer |
UNIQUE#1
NOT NULL
the norm to apply to the linked test |
Name | Constraint |
---|---|
audit_audit_fields_sane_modified_when | CHECK (((modified_when <= clock_timestamp()) IS TRUE)) |
allow to link many-to-many between clin.clin_root_item and clin.clin_item_type
F-Key | Name | Type | Description |
---|---|---|---|
pk_audit | integer | NOT NULL DEFAULT nextval('audit.audit_fields_pk_audit_seq'::regclass) | |
row_version | integer | NOT NULL | |
modified_when | timestamp with time zone | NOT NULL DEFAULT now() | |
modified_by | name | NOT NULL DEFAULT "current_user"() | |
pk | serial | PRIMARY KEY | |
clin_item_type.pk | fk_type | integer | UNIQUE#1 NOT NULL |
fk_item | integer |
UNIQUE#1
NOT NULL
the item this type is linked to, since PostgreSQL apparently cannot reference a value inserted from a child table (?) we must simulate referential integrity checks with a custom trigger, this, however, does not deal with update/delete cascading :-( |
Name | Constraint |
---|---|
audit_audit_fields_sane_modified_when | CHECK (((modified_when <= clock_timestamp()) IS TRUE)) |
this table links vaccination courses for a single epitope into schedules defined and recommended by a vaccination council or similar entity
F-Key | Name | Type | Description |
---|---|---|---|
pk_audit | integer | NOT NULL DEFAULT nextval('audit.audit_fields_pk_audit_seq'::regclass) | |
row_version | integer | NOT NULL | |
modified_when | timestamp with time zone | NOT NULL DEFAULT now() | |
modified_by | name | NOT NULL DEFAULT "current_user"() | |
pk | serial | PRIMARY KEY | |
vaccination_course.pk | fk_course | integer | UNIQUE NOT NULL |
vaccination_schedule.pk | fk_schedule | integer | NOT NULL |
Name | Constraint |
---|---|
audit_audit_fields_sane_modified_when | CHECK (((modified_when <= clock_timestamp()) IS TRUE)) |
links vaccines to their indications
F-Key | Name | Type | Description |
---|---|---|---|
pk_audit | integer | NOT NULL DEFAULT nextval('audit.audit_fields_pk_audit_seq'::regclass) | |
row_version | integer | NOT NULL | |
modified_when | timestamp with time zone | NOT NULL DEFAULT now() | |
modified_by | name | NOT NULL DEFAULT "current_user"() | |
id | serial | PRIMARY KEY | |
vaccine.pk | fk_vaccine | integer | UNIQUE#1 NOT NULL |
vacc_indication.id | fk_indication | integer | UNIQUE#1 NOT NULL |
Name | Constraint |
---|---|
audit_audit_fields_sane_modified_when | CHECK (((modified_when <= clock_timestamp()) IS TRUE)) |
this table merges test types from various test orgs which are intended to measure the same value but have differing names into one logical test type, this is not intended to be used for aggregating semantically different test types into "profiles"
F-Key | Name | Type | Description |
---|---|---|---|
pk | serial | PRIMARY KEY | |
abbrev | text | UNIQUE#1 NOT NULL | |
name | text | UNIQUE#1 NOT NULL | |
loinc | text | ||
comment | text |
Tables referencing this one via Foreign Key Constraints:
A table to hold unique-per-identity *clinical* items, such as Expected Due Date.
F-Key | Name | Type | Description |
---|---|---|---|
pk_audit | integer | NOT NULL DEFAULT nextval('audit.audit_fields_pk_audit_seq'::regclass) | |
row_version | integer | NOT NULL | |
modified_when | timestamp with time zone | NOT NULL DEFAULT now() | |
modified_by | name | NOT NULL DEFAULT "current_user"() | |
pk | serial | PRIMARY KEY | |
fk_identity | integer |
UNIQUE
NOT NULL
the dem.identity.pk of this patient |
|
edc | date |
Name | Constraint |
---|---|
audit_audit_fields_sane_modified_when | CHECK (((modified_when <= clock_timestamp()) IS TRUE)) |
clin_patient_sane_edc | CHECK (((edc IS NULL) OR ((clin.get_dod(fk_identity) IS NULL) AND ((clin.get_dob(fk_identity) IS NULL) OR (edc > (clin.get_dob(fk_identity) + '5 years'::interval)))))) |
Tables referencing this one via Foreign Key Constraints:
This table holds procedure/operations performed on the patient both in hospital or in community care.
F-Key | Name | Type | Description |
---|---|---|---|
pk_audit | integer | NOT NULL DEFAULT nextval('audit.audit_fields_pk_audit_seq'::regclass) | |
row_version | integer | NOT NULL | |
modified_when | timestamp with time zone | NOT NULL DEFAULT now() | |
modified_by | name | NOT NULL DEFAULT "current_user"() | |
pk_item | integer | NOT NULL DEFAULT nextval('clin.clin_root_item_pk_item_seq'::regclass) | |
clin_when | timestamp with time zone | NOT NULL DEFAULT now() | |
encounter.pk | fk_encounter | integer | NOT NULL |
episode.pk | fk_episode | integer | NOT NULL |
narrative | text |
Which procedure/operation was performed. |
|
soap_cat | text | DEFAULT 'p'::text | |
pk | serial | PRIMARY KEY | |
hospital_stay.pk | fk_hospital_stay | integer |
At which hospital was the procedure performed, unless clin_where is not null, if null it was an ambulatory procedure. |
clin_end | timestamp with time zone |
When did this procedure end/is expected to end. - NULL if unknown or .clin_when (=start) is sufficient (eg. insignificant duration) |
|
is_ongoing | boolean |
NOT NULL
DEFAULT false
Whether this procedure is still going on (such as desensibilisation, chemotherapy, etc). |
|
fk_org_unit | integer |
links to the or unit the procedure was performed at |
Table procedure Inherits clin_root_item,
Name | Constraint |
---|---|
audit_audit_fields_sane_modified_when | CHECK (((modified_when <= clock_timestamp()) IS TRUE)) |
clin_procedure_lnk_org_or_stay | CHECK ((((fk_hospital_stay IS NULL) AND (fk_org_unit IS NOT NULL)) OR ((fk_hospital_stay IS NOT NULL) AND (fk_org_unit IS NULL)))) |
clin_root_item_sane_soap_cat | CHECK (((soap_cat IS NULL) OR (lower(soap_cat) = ANY (ARRAY['s'::text, 'o'::text, 'a'::text, 'p'::text, 'u'::text])))) |
procedure_sane_end | CHECK (((clin_end IS NULL) OR (clin_end >= clin_when))) |
sane_procedure | CHECK ((gm.is_null_or_blank_string(narrative) IS FALSE)) |
sane_soap_cat | CHECK ((soap_cat = ANY (ARRAY['a'::text, 'p'::text]))) |
Tables referencing this one via Foreign Key Constraints:
this table tracks whether a particular clinical item was reviewed by a clinician or not
F-Key | Name | Type | Description |
---|---|---|---|
pk_audit | integer | NOT NULL DEFAULT nextval('audit.audit_fields_pk_audit_seq'::regclass) | |
row_version | integer | NOT NULL | |
modified_when | timestamp with time zone | NOT NULL DEFAULT now() | |
modified_by | name | NOT NULL DEFAULT "current_user"() | |
pk | serial | PRIMARY KEY | |
fk_reviewed_row | integer |
UNIQUE#1
NOT NULL
the row the review status is for: to be qualified as a proper foreign key in child tables |
|
fk_reviewer | integer |
UNIQUE#1
NOT NULL
who has reviewed the item |
|
is_technically_abnormal | boolean |
NOT NULL
whether test provider flagged this result as abnormal, *not* a clinical assessment but rather a technical one LDT: exist(8422) |
|
clinically_relevant | boolean |
NOT NULL
whether this result is considered relevant clinically, need not correspond to the value of "techically_abnormal" since abnormal values may be irrelevant while normal ones can be of significance |
|
comment | text |
Name | Constraint |
---|---|
audit_audit_fields_sane_modified_when | CHECK (((modified_when <= clock_timestamp()) IS TRUE)) |
review table for test results
F-Key | Name | Type | Description |
---|---|---|---|
pk_audit | integer | NOT NULL DEFAULT nextval('audit.audit_fields_pk_audit_seq'::regclass) | |
row_version | integer | NOT NULL | |
modified_when | timestamp with time zone | NOT NULL DEFAULT now() | |
modified_by | name | NOT NULL DEFAULT "current_user"() | |
pk | integer | PRIMARY KEY DEFAULT nextval('clin.review_root_pk_seq'::regclass) | |
test_result.pk | fk_reviewed_row | integer | UNIQUE#1 UNIQUE NOT NULL |
fk_reviewer | integer | UNIQUE#1 NOT NULL DEFAULT clin.f_fk_reviewer_default() | |
is_technically_abnormal | boolean | NOT NULL | |
clinically_relevant | boolean | NOT NULL | |
comment | text |
Table reviewed_test_results Inherits review_root,
Name | Constraint |
---|---|
audit_audit_fields_sane_modified_when | CHECK (((modified_when <= clock_timestamp()) IS TRUE)) |
F-Key | Name | Type | Description |
---|---|---|---|
pk | serial | PRIMARY KEY | |
rank | integer | NOT NULL | |
soap_cat | character(1) |
Name | Constraint |
---|---|
clin_soap_cat_ranks_sane_cats | CHECK (((soap_cat IS NULL) OR (lower((soap_cat)::text) = ANY (ARRAY['s'::text, 'o'::text, 'a'::text, 'p'::text, 'u'::text])))) |
clin_soap_cat_ranks_sane_ranks | CHECK ((rank = ANY (ARRAY[1, 2, 3, 4, 5, 6]))) |
The substances a patient is actually currently taking.
F-Key | Name | Type | Description |
---|---|---|---|
pk_audit | integer | NOT NULL DEFAULT nextval('audit.audit_fields_pk_audit_seq'::regclass) | |
row_version | integer | NOT NULL | |
modified_when | timestamp with time zone | NOT NULL DEFAULT now() | |
modified_by | name | NOT NULL DEFAULT "current_user"() | |
pk_item | integer | NOT NULL DEFAULT nextval('clin.clin_root_item_pk_item_seq'::regclass) | |
clin_when | timestamp with time zone |
NOT NULL
DEFAULT now()
When was this substance started. |
|
encounter.pk | fk_encounter | integer |
NOT NULL
The encounter use of this substance was documented under. |
episode.pk | fk_episode | integer | NOT NULL |
narrative | text |
Any notes on this substance use. |
|
soap_cat | text | DEFAULT 'p'::text | |
pk | serial | PRIMARY KEY | |
fk_substance | integer |
NOT NULL
Links to a substance the patient is taking. ********************************************* DO NOT TRY TO USE THIS TO FIND OUT THE BRAND. IT WILL BE WRONG. ********************************************* |
|
preparation | text |
How this substance is delivered, tablet, pill, liquid, cream. |
|
schedule | text |
The schedule, if any, the substance is to be taken by. An XML snippet to be interpreted by the middleware. |
|
aim | text |
The aim of taking this substance. |
|
duration | interval |
How long is this substances intended to be taken. |
|
intake_is_approved_of | boolean |
NOT NULL
Whether or not intake of this substance is recommended/approved of by the provider |
|
is_long_term | boolean |
whether this is expected to be a regular/ongoing/chronic/long-term/repeat/permament/perpetual/life-long substance intake |
|
discontinued | timestamp with time zone |
When was this intake discontinued ? |
|
discontinue_reason | text |
Why was this intake discontinued ? |
|
fk_drug_component | integer |
Links to the component of a branded drug taken by a patient. |
|
comment_on_start | text |
Comment (uncertainty level) on .clin_when = started. "?" = "entirely unknown". |
|
harmful_use_type | integer |
NULL=not considered=medication, 0=no or not considered harmful, 1=presently harmful use, 2=presently addicted, 3=previously addicted |
Table substance_intake Inherits clin_root_item,
Name | Constraint |
---|---|
audit_audit_fields_sane_modified_when | CHECK (((modified_when <= clock_timestamp()) IS TRUE)) |
clin_patient_sane_use_type | CHECK (((harmful_use_type IS NULL) OR ((harmful_use_type >= 0) AND (harmful_use_type <= 3)))) |
clin_root_item_sane_soap_cat | CHECK (((soap_cat IS NULL) OR (lower(soap_cat) = ANY (ARRAY['s'::text, 'o'::text, 'a'::text, 'p'::text, 'u'::text])))) |
clin_subst_intake_sane_prep | CHECK ((((fk_drug_component IS NULL) AND (preparation IS NOT NULL)) OR ((fk_drug_component IS NOT NULL) AND (preparation IS NULL)))) |
clin_substance_intake_sane_start_comment | CHECK (gm.is_null_or_non_empty_string(comment_on_start)) |
discontinued_after_started | CHECK (((clin_when IS NULL) OR (discontinued IS NULL) OR ((discontinued >= clin_when) AND (discontinued <= now())))) |
medication_is_plan | CHECK ((soap_cat = 'p'::text)) |
sane_aim | CHECK ((gm.is_null_or_non_empty_string(aim) IS TRUE)) |
sane_discontinue_reason | CHECK ((((discontinued IS NULL) AND (discontinue_reason IS NULL)) OR ((discontinued IS NOT NULL) AND (gm.is_null_or_non_empty_string(discontinue_reason) IS TRUE)))) |
sane_schedule | CHECK ((gm.is_null_or_non_empty_string(schedule) IS TRUE)) |
Tables referencing this one via Foreign Key Constraints:
A table to hold hints suppressed per patient
F-Key | Name | Type | Description |
---|---|---|---|
pk_audit | integer | NOT NULL DEFAULT nextval('audit.audit_fields_pk_audit_seq'::regclass) | |
row_version | integer | NOT NULL | |
modified_when | timestamp with time zone | NOT NULL DEFAULT now() | |
modified_by | name | NOT NULL DEFAULT "current_user"() | |
pk | serial | PRIMARY KEY | |
encounter.pk | fk_encounter | integer |
NOT NULL
the encounter during which this hint was first suppressed |
fk_hint | integer |
NOT NULL
the hint that is suppressed |
|
suppressed_by | name |
NOT NULL
DEFAULT "current_user"()
who suppressed this hint |
|
suppressed_when | timestamp with time zone |
NOT NULL
DEFAULT statement_timestamp()
when was this hint suppressed |
|
rationale | text |
rationale on why this hint is suppressed in this patient |
|
md5_sum | text |
md5 of relevant fields of this hint |
Name | Constraint |
---|---|
audit_audit_fields_sane_modified_when | CHECK (((modified_when <= clock_timestamp()) IS TRUE)) |
clin_suppressed_hint_sane_by | CHECK ((length((suppressed_by)::text) > 0)) |
clin_suppressed_hint_sane_md5 | CHECK ((gm.is_null_or_blank_string(md5_sum) IS FALSE)) |
clin_suppressed_hint_sane_rationale | CHECK ((gm.is_null_or_blank_string(rationale) IS FALSE)) |
organisation providing results
F-Key | Name | Type | Description |
---|---|---|---|
pk_audit | integer | NOT NULL DEFAULT nextval('audit.audit_fields_pk_audit_seq'::regclass) | |
row_version | integer | NOT NULL | |
modified_when | timestamp with time zone | NOT NULL DEFAULT now() | |
modified_by | name | NOT NULL DEFAULT "current_user"() | |
pk | serial | PRIMARY KEY | |
fk_org_unit | integer |
UNIQUE
NOT NULL
link to a unit of an organization more closely defining this lab |
|
fk_adm_contact | integer |
whom to call for admin questions (modem link, etc.) |
|
fk_med_contact | integer |
whom to call for medical questions (result verification, additional test requests) |
|
comment | text |
useful for, say, dummy records where you want to mark up stuff like "pharmacy such-and-such" if you don't have it in your contacts |
|
contact | text |
free-text contact information for this lab |
Name | Constraint |
---|---|
audit_audit_fields_sane_modified_when | CHECK (((modified_when <= clock_timestamp()) IS TRUE)) |
sane_comment | CHECK ((gm.is_null_or_non_empty_string(comment) IS TRUE)) |
sane_contact | CHECK ((gm.is_null_or_non_empty_string(contact) IS TRUE)) |
Panels of tests. The same test can appear in several panels.
F-Key | Name | Type | Description |
---|---|---|---|
pk_audit | integer | NOT NULL DEFAULT nextval('audit.audit_fields_pk_audit_seq'::regclass) | |
row_version | integer | NOT NULL | |
modified_when | timestamp with time zone | NOT NULL DEFAULT now() | |
modified_by | name | NOT NULL DEFAULT "current_user"() | |
pk | serial | PRIMARY KEY | |
description | text |
A description/label for this panel. |
|
comment | text |
An arbitrary comment on this panel. |
|
fk_test_types | integer[] |
Links to test types which belong to this panel. |
Name | Constraint |
---|---|
audit_audit_fields_sane_modified_when | CHECK (((modified_when <= clock_timestamp()) IS TRUE)) |
clin_test_panel_sane_cmt | CHECK ((gm.is_null_or_non_empty_string(comment) IS TRUE)) |
clin_test_panel_sane_desc | CHECK ((gm.is_null_or_blank_string(description) IS FALSE)) |
Tables referencing this one via Foreign Key Constraints:
the results of a single measurement
F-Key | Name | Type | Description |
---|---|---|---|
pk_audit | integer | NOT NULL DEFAULT nextval('audit.audit_fields_pk_audit_seq'::regclass) | |
row_version | integer | NOT NULL | |
modified_when | timestamp with time zone | NOT NULL DEFAULT now() | |
modified_by | name | NOT NULL DEFAULT "current_user"() | |
pk_item | integer | NOT NULL DEFAULT nextval('clin.clin_root_item_pk_item_seq'::regclass) | |
clin_when | timestamp with time zone |
NOT NULL
DEFAULT now()
the time when this result was *actually* obtained, if this is a lab result this should be between lab_request.clin_when and lab_request.results_reported_when, HL7: OBR.observation_date_time |
|
encounter.pk | fk_encounter | integer | NOT NULL |
episode.pk | fk_episode | integer | NOT NULL |
narrative | text |
clinical comment, progress note |
|
soap_cat | text | DEFAULT 'o'::text | |
pk | serial | PRIMARY KEY | |
test_type.pk | fk_type | integer |
NOT NULL
the type of test this result is from |
val_num | numeric |
numeric value if any, HL7: OBX.observation_results if OBX.value_type == NM |
|
val_alpha | text |
alphanumeric value if any, HL7: OBX.observation_results if OBX.value_type == FT |
|
val_unit | text |
the unit this result came in HL7: OBX.units |
|
val_normal_min | numeric |
lower bound of normal range if numerical as defined by provider for this result |
|
val_normal_max | numeric |
upper bound of normal range if numerical as defined by provider for this result |
|
val_normal_range | text |
range of normal values if alphanumerical as defined by provider for this result, eg. "less than 0.5 but detectable" HL7: OBX.reference_range |
|
val_target_min | numeric |
lower bound of target range if numerical as defined by clinician caring this patient |
|
val_target_max | numeric |
upper bound of target range if numerical as defined by clinician caring for this patient |
|
val_target_range | text |
range of target values if alphanumerical as defined by clinician caring for this patient |
|
abnormality_indicator | text |
how the test provider flagged this result as abnormal, *not* a clinical assessment but rather a technical one LDT: 8422 |
|
norm_ref_group | text |
what sample of the population does this normal range applay to, eg what type of patient was assumed when interpreting this result, LDT: 8407 |
|
note_test_org | text |
A comment on the test result provided by the tester or testing entity. |
|
material | text |
the submitted material, eg. smear, serum, urine, etc., LDT: 8430 |
|
material_detail | text |
details re the material, eg. site taken from, etc. LDT: 8431 |
|
fk_intended_reviewer | integer |
NOT NULL
who is *supposed* to review this item |
|
lab_request.pk | fk_request | integer |
The request this result was ordered under if any. |
status | text |
The result status (say, HL7 OBX 11 Observ result status (#00579, table 0085). |
|
source_data | text |
The source data for this observation (say, HL7 OBX). |
|
val_grouping | text |
A grouping for related values (say, HL7 OBX Obs Sub ID, think antibiogram). |
Table test_result Inherits clin_root_item,
Name | Constraint |
---|---|
audit_audit_fields_sane_modified_when | CHECK (((modified_when <= clock_timestamp()) IS TRUE)) |
clin_root_item_sane_soap_cat | CHECK (((soap_cat IS NULL) OR (lower(soap_cat) = ANY (ARRAY['s'::text, 'o'::text, 'a'::text, 'p'::text, 'u'::text])))) |
clin_test_result_sane_source_data | CHECK (gm.is_null_or_non_empty_string(source_data)) |
clin_test_result_sane_status | CHECK (gm.is_null_or_non_empty_string(status)) |
clin_test_result_sane_val_grouping | CHECK (gm.is_null_or_non_empty_string(val_grouping)) |
numval_needs_unit | CHECK ((((val_num IS NOT NULL) AND (btrim(COALESCE(val_unit, ''::text)) <> ''::text)) OR (val_num IS NULL))) |
test_result_check | CHECK (((val_num IS NOT NULL) OR (val_alpha IS NOT NULL) OR ((val_num IS NULL) AND (val_alpha <> ''::text) AND (val_alpha IS NOT NULL)))) |
Tables referencing this one via Foreign Key Constraints:
measurement type, like a "method" in a lab
F-Key | Name | Type | Description |
---|---|---|---|
pk_audit | integer | NOT NULL DEFAULT nextval('audit.audit_fields_pk_audit_seq'::regclass) | |
row_version | integer | NOT NULL | |
modified_when | timestamp with time zone | NOT NULL DEFAULT now() | |
modified_by | name | NOT NULL DEFAULT "current_user"() | |
pk | serial | PRIMARY KEY | |
test_org.pk | fk_test_org | integer |
UNIQUE#1
UNIQUE#2
NOT NULL
organisation carrying out this type of measurement, eg. a particular lab |
name | text |
UNIQUE#2
NOT NULL
descriptive name of this measurement type, HL7: OBX.observation_identifier |
|
comment | text |
arbitrary comment on this type of measurement/test such as "outdated" or "only reliable when ..." |
|
reference_unit | text |
The chosen reference unit for this test type, preferably SI, used for comparing results delivered in differing units. This does not relate to what unit the test provider delivers results in but rather the unit we think those results need to be converted to in order to be comparable to OTHER results. |
|
loinc | text | ||
abbrev | text | UNIQUE#1 NOT NULL | |
meta_test_type.pk | fk_meta_test_type | integer |
Link to the meta test type (if any) this test type is to be aggregated under. |
Name | Constraint |
---|---|
audit_audit_fields_sane_modified_when | CHECK (((modified_when <= clock_timestamp()) IS TRUE)) |
F-Key | Name | Type | Description |
---|---|---|---|
pk_substance_intake | integer | ||
pk_patient | integer | ||
soap_cat | text | ||
brand | text | ||
preparation | text | ||
substance | text | ||
amount | numeric | ||
unit | text | ||
atc_substance | text | ||
atc_brand | text | ||
external_code_brand | text | ||
external_code_type_brand | text | ||
started | timestamp with time zone | ||
comment_on_start | text | ||
start_is_unknown | boolean | ||
start_is_approximate | boolean | ||
intake_is_approved_of | boolean | ||
harmful_use_type | integer | ||
last_checked_when | timestamp with time zone | ||
schedule | text | ||
duration | interval | ||
discontinued | timestamp with time zone | ||
discontinue_reason | text | ||
is_long_term | boolean | ||
aim | text | ||
episode | text | ||
health_issue | text | ||
notes | text | ||
fake_brand | boolean | ||
is_currently_active | boolean | ||
seems_inactive | boolean | ||
pk_brand | integer | ||
pk_data_source | integer | ||
pk_substance | integer | ||
pk_drug_component | integer | ||
pk_encounter | integer | ||
pk_episode | integer | ||
pk_health_issue | integer | ||
modified_when | timestamp with time zone | ||
modified_by | name | ||
row_version | integer | ||
xmin_substance_intake | xid |
SELECT c_si.pk AS pk_substance_intake , ( SELECT encounter.fk_patient FROM clin.encounter WHERE (encounter.pk = c_si.fk_encounter) ) AS pk_patient , c_si.soap_cat , r_bd.description AS brand , r_bd.preparation , r_cs.description AS substance , r_cs.amount , r_cs.unit , r_cs.atc_code AS atc_substance , r_bd.atc_code AS atc_brand , r_bd.external_code AS external_code_brand , r_bd.external_code_type AS external_code_type_brand , CASE WHEN (c_si.comment_on_start = '?'::text) THEN NULL::timestamp with time zone ELSE c_si.clin_when END AS started , c_si.comment_on_start , CASE WHEN (c_si.comment_on_start = '?'::text) THEN true ELSE false END AS start_is_unknown , CASE WHEN (c_si.comment_on_start IS NULL) THEN false ELSE true END AS start_is_approximate , c_si.intake_is_approved_of , c_si.harmful_use_type , NULL::timestamp with time zone AS last_checked_when , c_si.schedule , c_si.duration , c_si.discontinued , c_si.discontinue_reason , c_si.is_long_term , c_si.aim , cep.description AS episode , c_hi.description AS health_issue , c_si.narrative AS notes , r_bd.is_fake AS fake_brand , CASE WHEN (c_si.discontinued IS NULL) THEN true ELSE false END AS is_currently_active , CASE WHEN (c_si.discontinued IS NOT NULL) THEN true WHEN (c_si.clin_when IS NULL) THEN CASE WHEN (c_si.is_long_term IS TRUE) THEN false ELSE NULL::boolean END WHEN ( (c_si.clin_when > now () ) IS TRUE ) THEN true WHEN ( ( (c_si.clin_when + c_si.duration) < now () ) IS TRUE ) THEN true WHEN ( ( (c_si.clin_when + c_si.duration) > now () ) IS TRUE ) THEN false ELSE NULL::boolean END AS seems_inactive , r_ls2b.fk_brand AS pk_brand , r_bd.fk_data_source AS pk_data_source , r_ls2b.fk_substance AS pk_substance , r_ls2b.pk AS pk_drug_component , c_si.fk_encounter AS pk_encounter , c_si.fk_episode AS pk_episode , cep.fk_health_issue AS pk_health_issue , c_si.modified_when , c_si.modified_by , c_si.row_version , c_si.xmin AS xmin_substance_intake FROM ( ( ( ( (clin.substance_intake c_si JOIN ref.lnk_substance2brand r_ls2b ON ( (c_si.fk_drug_component = r_ls2b.pk) ) ) JOIN ref.branded_drug r_bd ON ( (r_ls2b.fk_brand = r_bd.pk) ) ) JOIN ref.consumable_substance r_cs ON ( (r_ls2b.fk_substance = r_cs.pk) ) ) LEFT JOIN clin.episode cep ON ( (c_si.fk_episode = cep.pk) ) ) LEFT JOIN clin.health_issue c_hi ON ( (c_hi.pk = cep.fk_health_issue) ) ) WHERE (c_si.fk_drug_component IS NOT NULL);
F-Key | Name | Type | Description |
---|---|---|---|
pk_patient | integer | ||
modified_when | timestamp with time zone | ||
clin_when | timestamp with time zone | ||
modified_by | text | ||
soap_cat | text | ||
narrative | text | ||
pk_encounter | integer | ||
pk_episode | integer | ||
pk_health_issue | integer | ||
src_pk | integer | ||
src_table | text | ||
row_version | integer | ||
health_issue | text | ||
issue_laterality | text | ||
issue_active | boolean | ||
issue_clinically_relevant | boolean | ||
issue_confidential | boolean | ||
episode | text | ||
episode_open | boolean | ||
encounter_started | timestamp with time zone | ||
encounter_last_affirmed | timestamp with time zone | ||
encounter_type | text | ||
encounter_l10n_type | text |
SELECT c_p.fk_identity AS pk_patient , c_p.modified_when , c_p.modified_when AS clin_when , COALESCE ( ( SELECT staff.short_alias FROM dem.staff WHERE (staff.db_user = c_p.modified_by) ) , ( ('<'::text || (c_p.modified_by)::text ) || '>'::text ) ) AS modified_by , 's'::text AS soap_cat , (_ ('EDC'::text) || to_char ( (c_p.edc)::timestamp with time zone ,': YYYY Mon DD'::text ) ) AS narrative , ( SELECT c_e.pk FROM clin.encounter c_e WHERE (c_e.fk_patient = c_p.fk_identity) ORDER BY c_e.started DESC LIMIT 1 ) AS pk_encounter , NULL::integer AS pk_episode , NULL::integer AS pk_health_issue , c_p.pk AS src_pk , 'clin.patient'::text AS src_table , c_p.row_version , NULL::text AS health_issue , NULL::text AS issue_laterality , NULL::boolean AS issue_active , NULL::boolean AS issue_clinically_relevant , NULL::boolean AS issue_confidential , NULL::text AS episode , NULL::boolean AS episode_open , NULL::timestamp with time zone AS encounter_started , NULL::timestamp with time zone AS encounter_last_affirmed , NULL::text AS encounter_type , NULL::text AS encounter_l10n_type FROM clin.patient c_p WHERE (c_p.edc IS NOT NULL);
Clinical patient data formatted into one string per clinical entity even if it constains several user- visible fields. Mainly useful for display as a simple EMR journal.
F-Key | Name | Type | Description |
---|---|---|---|
pk_patient | integer | ||
modified_when | timestamp with time zone | ||
clin_when | timestamp with time zone | ||
modified_by | text | ||
soap_cat | text | ||
narrative | text | ||
pk_encounter | integer | ||
pk_episode | integer | ||
pk_health_issue | integer | ||
src_pk | integer | ||
src_table | text | ||
row_version | integer | ||
health_issue | text | ||
issue_laterality | character varying | ||
issue_active | boolean | ||
issue_clinically_relevant | boolean | ||
issue_confidential | boolean | ||
episode | text | ||
episode_open | boolean | ||
encounter_started | timestamp with time zone | ||
encounter_last_affirmed | timestamp with time zone | ||
encounter_type | text | ||
encounter_l10n_type | text |
SELECT v_pat_narrative_journal.pk_patient , v_pat_narrative_journal.modified_when , v_pat_narrative_journal.clin_when , v_pat_narrative_journal.modified_by , v_pat_narrative_journal.soap_cat , v_pat_narrative_journal.narrative , v_pat_narrative_journal.pk_encounter , v_pat_narrative_journal.pk_episode , v_pat_narrative_journal.pk_health_issue , v_pat_narrative_journal.src_pk , v_pat_narrative_journal.src_table , v_pat_narrative_journal.row_version , v_pat_narrative_journal.health_issue , v_pat_narrative_journal.issue_laterality , v_pat_narrative_journal.issue_active , v_pat_narrative_journal.issue_clinically_relevant , v_pat_narrative_journal.issue_confidential , v_pat_narrative_journal.episode , v_pat_narrative_journal.episode_open , v_pat_narrative_journal.encounter_started , v_pat_narrative_journal.encounter_last_affirmed , v_pat_narrative_journal.encounter_type , v_pat_narrative_journal.encounter_l10n_type FROM clin.v_pat_narrative_journal UNION ALL SELECT v_health_issues_journal.pk_patient , v_health_issues_journal.modified_when , v_health_issues_journal.clin_when , v_health_issues_journal.modified_by , v_health_issues_journal.soap_cat , v_health_issues_journal.narrative , v_health_issues_journal.pk_encounter , v_health_issues_journal.pk_episode , v_health_issues_journal.pk_health_issue , v_health_issues_journal.src_pk , v_health_issues_journal.src_table , v_health_issues_journal.row_version , v_health_issues_journal.health_issue , v_health_issues_journal.issue_laterality , v_health_issues_journal.issue_active , v_health_issues_journal.issue_clinically_relevant , v_health_issues_journal.issue_confidential , v_health_issues_journal.episode , v_health_issues_journal.episode_open , v_health_issues_journal.encounter_started , v_health_issues_journal.encounter_last_affirmed , v_health_issues_journal.encounter_type , v_health_issues_journal.encounter_l10n_type FROM clin.v_health_issues_journal UNION ALL SELECT v_pat_encounters_journal.pk_patient , v_pat_encounters_journal.modified_when , v_pat_encounters_journal.clin_when , v_pat_encounters_journal.modified_by , v_pat_encounters_journal.soap_cat , v_pat_encounters_journal.narrative , v_pat_encounters_journal.pk_encounter , v_pat_encounters_journal.pk_episode , v_pat_encounters_journal.pk_health_issue , v_pat_encounters_journal.src_pk , v_pat_encounters_journal.src_table , v_pat_encounters_journal.row_version , v_pat_encounters_journal.health_issue , v_pat_encounters_journal.issue_laterality , v_pat_encounters_journal.issue_active , v_pat_encounters_journal.issue_clinically_relevant , v_pat_encounters_journal.issue_confidential , v_pat_encounters_journal.episode , v_pat_encounters_journal.episode_open , v_pat_encounters_journal.encounter_started , v_pat_encounters_journal.encounter_last_affirmed , v_pat_encounters_journal.encounter_type , v_pat_encounters_journal.encounter_l10n_type FROM clin.v_pat_encounters_journal UNION ALL SELECT v_pat_episodes_journal.pk_patient , v_pat_episodes_journal.modified_when , v_pat_episodes_journal.clin_when , v_pat_episodes_journal.modified_by , v_pat_episodes_journal.soap_cat , v_pat_episodes_journal.narrative , v_pat_episodes_journal.pk_encounter , v_pat_episodes_journal.pk_episode , v_pat_episodes_journal.pk_health_issue , v_pat_episodes_journal.src_pk , v_pat_episodes_journal.src_table , v_pat_episodes_journal.row_version , v_pat_episodes_journal.health_issue , v_pat_episodes_journal.issue_laterality , v_pat_episodes_journal.issue_active , v_pat_episodes_journal.issue_clinically_relevant , v_pat_episodes_journal.issue_confidential , v_pat_episodes_journal.episode , v_pat_episodes_journal.episode_open , v_pat_episodes_journal.encounter_started , v_pat_episodes_journal.encounter_last_affirmed , v_pat_episodes_journal.encounter_type , v_pat_episodes_journal.encounter_l10n_type FROM clin.v_pat_episodes_journal UNION ALL SELECT v_family_history_journal.pk_patient , v_family_history_journal.modified_when , v_family_history_journal.clin_when , v_family_history_journal.modified_by , v_family_history_journal.soap_cat , v_family_history_journal.narrative , v_family_history_journal.pk_encounter , v_family_history_journal.pk_episode , v_family_history_journal.pk_health_issue , v_family_history_journal.src_pk , v_family_history_journal.src_table , v_family_history_journal.row_version , v_family_history_journal.health_issue , v_family_history_journal.issue_laterality , v_family_history_journal.issue_active , v_family_history_journal.issue_clinically_relevant , v_family_history_journal.issue_confidential , v_family_history_journal.episode , v_family_history_journal.episode_open , v_family_history_journal.encounter_started , v_family_history_journal.encounter_last_affirmed , v_family_history_journal.encounter_type , v_family_history_journal.encounter_l10n_type FROM clin.v_family_history_journal UNION ALL SELECT v_pat_allergies_journal.pk_patient , v_pat_allergies_journal.modified_when , v_pat_allergies_journal.clin_when , v_pat_allergies_journal.modified_by , v_pat_allergies_journal.soap_cat , v_pat_allergies_journal.narrative , v_pat_allergies_journal.pk_encounter , v_pat_allergies_journal.pk_episode , v_pat_allergies_journal.pk_health_issue , v_pat_allergies_journal.src_pk , v_pat_allergies_journal.src_table , v_pat_allergies_journal.row_version , v_pat_allergies_journal.health_issue , v_pat_allergies_journal.issue_laterality , v_pat_allergies_journal.issue_active , v_pat_allergies_journal.issue_clinically_relevant , v_pat_allergies_journal.issue_confidential , v_pat_allergies_journal.episode , v_pat_allergies_journal.episode_open , v_pat_allergies_journal.encounter_started , v_pat_allergies_journal.encounter_last_affirmed , v_pat_allergies_journal.encounter_type , v_pat_allergies_journal.encounter_l10n_type FROM clin.v_pat_allergies_journal UNION ALL SELECT v_pat_allergy_state_journal.pk_patient , v_pat_allergy_state_journal.modified_when , v_pat_allergy_state_journal.clin_when , v_pat_allergy_state_journal.modified_by , v_pat_allergy_state_journal.soap_cat , v_pat_allergy_state_journal.narrative , v_pat_allergy_state_journal.pk_encounter , v_pat_allergy_state_journal.pk_episode , v_pat_allergy_state_journal.pk_health_issue , v_pat_allergy_state_journal.src_pk , v_pat_allergy_state_journal.src_table , v_pat_allergy_state_journal.row_version , v_pat_allergy_state_journal.health_issue , v_pat_allergy_state_journal.issue_laterality , v_pat_allergy_state_journal.issue_active , v_pat_allergy_state_journal.issue_clinically_relevant , v_pat_allergy_state_journal.issue_confidential , v_pat_allergy_state_journal.episode , v_pat_allergy_state_journal.episode_open , v_pat_allergy_state_journal.encounter_started , v_pat_allergy_state_journal.encounter_last_affirmed , v_pat_allergy_state_journal.encounter_type , v_pat_allergy_state_journal.encounter_l10n_type FROM clin.v_pat_allergy_state_journal UNION ALL SELECT v_test_results_journal.pk_patient , v_test_results_journal.modified_when , v_test_results_journal.clin_when , v_test_results_journal.modified_by , v_test_results_journal.soap_cat , v_test_results_journal.narrative , v_test_results_journal.pk_encounter , v_test_results_journal.pk_episode , v_test_results_journal.pk_health_issue , v_test_results_journal.src_pk , v_test_results_journal.src_table , v_test_results_journal.row_version , v_test_results_journal.health_issue , v_test_results_journal.issue_laterality , v_test_results_journal.issue_active , v_test_results_journal.issue_clinically_relevant , v_test_results_journal.issue_confidential , v_test_results_journal.episode , v_test_results_journal.episode_open , v_test_results_journal.encounter_started , v_test_results_journal.encounter_last_affirmed , v_test_results_journal.encounter_type , v_test_results_journal.encounter_l10n_type FROM clin.v_test_results_journal UNION ALL SELECT v_hospital_stays_journal.pk_patient , v_hospital_stays_journal.modified_when , v_hospital_stays_journal.clin_when , v_hospital_stays_journal.modified_by , v_hospital_stays_journal.soap_cat , v_hospital_stays_journal.narrative , v_hospital_stays_journal.pk_encounter , v_hospital_stays_journal.pk_episode , v_hospital_stays_journal.pk_health_issue , v_hospital_stays_journal.src_pk , v_hospital_stays_journal.src_table , v_hospital_stays_journal.row_version , v_hospital_stays_journal.health_issue , v_hospital_stays_journal.issue_laterality , v_hospital_stays_journal.issue_active , v_hospital_stays_journal.issue_clinically_relevant , v_hospital_stays_journal.issue_confidential , v_hospital_stays_journal.episode , v_hospital_stays_journal.episode_open , v_hospital_stays_journal.encounter_started , v_hospital_stays_journal.encounter_last_affirmed , v_hospital_stays_journal.encounter_type , v_hospital_stays_journal.encounter_l10n_type FROM clin.v_hospital_stays_journal UNION ALL SELECT v_doc_med_journal.pk_patient , v_doc_med_journal.modified_when , v_doc_med_journal.clin_when , v_doc_med_journal.modified_by , v_doc_med_journal.soap_cat , v_doc_med_journal.narrative , v_doc_med_journal.pk_encounter , v_doc_med_journal.pk_episode , v_doc_med_journal.pk_health_issue , v_doc_med_journal.src_pk , v_doc_med_journal.src_table , v_doc_med_journal.row_version , v_doc_med_journal.health_issue , v_doc_med_journal.issue_laterality , v_doc_med_journal.issue_active , v_doc_med_journal.issue_clinically_relevant , v_doc_med_journal.issue_confidential , v_doc_med_journal.episode , v_doc_med_journal.episode_open , v_doc_med_journal.encounter_started , v_doc_med_journal.encounter_last_affirmed , v_doc_med_journal.encounter_type , v_doc_med_journal.encounter_l10n_type FROM blobs.v_doc_med_journal UNION ALL SELECT v_substance_intake_journal.pk_patient , v_substance_intake_journal.modified_when , v_substance_intake_journal.clin_when , v_substance_intake_journal.modified_by , v_substance_intake_journal.soap_cat , v_substance_intake_journal.narrative , v_substance_intake_journal.pk_encounter , v_substance_intake_journal.pk_episode , v_substance_intake_journal.pk_health_issue , v_substance_intake_journal.src_pk , v_substance_intake_journal.src_table , v_substance_intake_journal.row_version , v_substance_intake_journal.health_issue , v_substance_intake_journal.issue_laterality , v_substance_intake_journal.issue_active , v_substance_intake_journal.issue_clinically_relevant , v_substance_intake_journal.issue_confidential , v_substance_intake_journal.episode , v_substance_intake_journal.episode_open , v_substance_intake_journal.encounter_started , v_substance_intake_journal.encounter_last_affirmed , v_substance_intake_journal.encounter_type , v_substance_intake_journal.encounter_l10n_type FROM clin.v_substance_intake_journal UNION ALL SELECT v_procedures_journal.pk_patient , v_procedures_journal.modified_when , v_procedures_journal.clin_when , v_procedures_journal.modified_by , v_procedures_journal.soap_cat , v_procedures_journal.narrative , v_procedures_journal.pk_encounter , v_procedures_journal.pk_episode , v_procedures_journal.pk_health_issue , v_procedures_journal.src_pk , v_procedures_journal.src_table , v_procedures_journal.row_version , v_procedures_journal.health_issue , v_procedures_journal.issue_laterality , v_procedures_journal.issue_active , v_procedures_journal.issue_clinically_relevant , v_procedures_journal.issue_confidential , v_procedures_journal.episode , v_procedures_journal.episode_open , v_procedures_journal.encounter_started , v_procedures_journal.encounter_last_affirmed , v_procedures_journal.encounter_type , v_procedures_journal.encounter_l10n_type FROM clin.v_procedures_journal UNION ALL SELECT v_vaccinations_journal.pk_patient , v_vaccinations_journal.modified_when , v_vaccinations_journal.clin_when , v_vaccinations_journal.modified_by , v_vaccinations_journal.soap_cat , v_vaccinations_journal.narrative , v_vaccinations_journal.pk_encounter , v_vaccinations_journal.pk_episode , v_vaccinations_journal.pk_health_issue , v_vaccinations_journal.src_pk , v_vaccinations_journal.src_table , v_vaccinations_journal.row_version , v_vaccinations_journal.health_issue , v_vaccinations_journal.issue_laterality , v_vaccinations_journal.issue_active , v_vaccinations_journal.issue_clinically_relevant , v_vaccinations_journal.issue_confidential , v_vaccinations_journal.episode , v_vaccinations_journal.episode_open , v_vaccinations_journal.encounter_started , v_vaccinations_journal.encounter_last_affirmed , v_vaccinations_journal.encounter_type , v_vaccinations_journal.encounter_l10n_type FROM clin.v_vaccinations_journal UNION ALL SELECT v_suppressed_hints_journal.pk_patient , v_suppressed_hints_journal.modified_when , v_suppressed_hints_journal.clin_when , v_suppressed_hints_journal.modified_by , v_suppressed_hints_journal.soap_cat , v_suppressed_hints_journal.narrative , v_suppressed_hints_journal.fk_encounter AS pk_encounter , v_suppressed_hints_journal.pk_episode , v_suppressed_hints_journal.pk_health_issue , v_suppressed_hints_journal.src_pk , v_suppressed_hints_journal.src_table , v_suppressed_hints_journal.row_version , v_suppressed_hints_journal.health_issue , v_suppressed_hints_journal.issue_laterality , v_suppressed_hints_journal.issue_active , v_suppressed_hints_journal.issue_clinically_relevant , v_suppressed_hints_journal.issue_confidential , v_suppressed_hints_journal.episode , v_suppressed_hints_journal.episode_open , v_suppressed_hints_journal.encounter_started , v_suppressed_hints_journal.encounter_last_affirmed , v_suppressed_hints_journal.encounter_type , v_suppressed_hints_journal.encounter_l10n_type FROM clin.v_suppressed_hints_journal UNION ALL SELECT v_external_care_journal.pk_patient , v_external_care_journal.modified_when , v_external_care_journal.clin_when , v_external_care_journal.modified_by , v_external_care_journal.soap_cat , v_external_care_journal.narrative , v_external_care_journal.pk_encounter , v_external_care_journal.pk_episode , v_external_care_journal.pk_health_issue , v_external_care_journal.src_pk , v_external_care_journal.src_table , v_external_care_journal.row_version , v_external_care_journal.health_issue , v_external_care_journal.issue_laterality , v_external_care_journal.issue_active , v_external_care_journal.issue_clinically_relevant , v_external_care_journal.issue_confidential , v_external_care_journal.episode , v_external_care_journal.episode_open , v_external_care_journal.encounter_started , v_external_care_journal.encounter_last_affirmed , v_external_care_journal.encounter_type , v_external_care_journal.encounter_l10n_type FROM clin.v_external_care_journal UNION ALL SELECT v_edc_journal.pk_patient , v_edc_journal.modified_when , v_edc_journal.clin_when , v_edc_journal.modified_by , v_edc_journal.soap_cat , v_edc_journal.narrative , v_edc_journal.pk_encounter , v_edc_journal.pk_episode , v_edc_journal.pk_health_issue , v_edc_journal.src_pk , v_edc_journal.src_table , v_edc_journal.row_version , v_edc_journal.health_issue , v_edc_journal.issue_laterality , v_edc_journal.issue_active , v_edc_journal.issue_clinically_relevant , v_edc_journal.issue_confidential , v_edc_journal.episode , v_edc_journal.episode_open , v_edc_journal.encounter_started , v_edc_journal.encounter_last_affirmed , v_edc_journal.encounter_type , v_edc_journal.encounter_l10n_type FROM clin.v_edc_journal UNION ALL SELECT v_reminders_journal.pk_patient , v_reminders_journal.modified_when , v_reminders_journal.clin_when , v_reminders_journal.modified_by , v_reminders_journal.soap_cat , v_reminders_journal.narrative , v_reminders_journal.pk_encounter , v_reminders_journal.pk_episode , v_reminders_journal.pk_health_issue , v_reminders_journal.src_pk , v_reminders_journal.src_table , v_reminders_journal.row_version , v_reminders_journal.health_issue , v_reminders_journal.issue_laterality , v_reminders_journal.issue_active , v_reminders_journal.issue_clinically_relevant , v_reminders_journal.issue_confidential , v_reminders_journal.episode , v_reminders_journal.episode_open , v_reminders_journal.encounter_started , v_reminders_journal.encounter_last_affirmed , v_reminders_journal.encounter_type , v_reminders_journal.encounter_l10n_type FROM clin.v_reminders_journal;
F-Key | Name | Type | Description |
---|---|---|---|
pk_export_item | integer | ||
pk_identity | integer | ||
created_by | text | ||
created_when | timestamp with time zone | ||
designation | text | ||
description | text | ||
pk_doc_obj | integer | ||
md5_sum | text | ||
size | integer | ||
filename | text | ||
xmin_export_item | xid | ||
title | text | ||
firstnames | text | ||
lastnames | text | ||
preferred | text | ||
gender | text | ||
dob | timestamp with time zone |
SELECT inner_export_items.pk_export_item , inner_export_items.pk_identity , inner_export_items.created_by , inner_export_items.created_when , inner_export_items.designation , inner_export_items.description , inner_export_items.pk_doc_obj , inner_export_items.md5_sum , inner_export_items.size , inner_export_items.filename , inner_export_items.xmin_export_item , d_vp.title , d_vp.firstnames , d_vp.lastnames , d_vp.preferred , d_vp.gender , d_vp.dob FROM ( ( SELECT c_ei.pk AS pk_export_item , COALESCE (c_ei.fk_identity , ( SELECT encounter.fk_patient FROM clin.encounter WHERE (encounter.pk = ( SELECT doc_med.fk_encounter FROM blobs.doc_med WHERE (doc_med.pk = ( SELECT doc_obj.fk_doc FROM blobs.doc_obj WHERE (doc_obj.pk = c_ei.fk_doc_obj) ) ) ) ) ) ) AS pk_identity , COALESCE ( ( SELECT staff.short_alias FROM dem.staff WHERE (staff.db_user = c_ei.created_by) ) , (c_ei.created_by)::text ) AS created_by , c_ei.created_when , c_ei.designation , c_ei.description , c_ei.fk_doc_obj AS pk_doc_obj , md5 (COALESCE (c_ei.data , COALESCE ( ( SELECT b_do.data FROM blobs.doc_obj b_do WHERE (b_do.pk = c_ei.fk_doc_obj) ) ,'\x'::bytea ) ) ) AS md5_sum , octet_length (COALESCE (c_ei.data ,'\x'::bytea ) ) AS size , COALESCE (c_ei.filename , ( SELECT b_do.filename FROM blobs.doc_obj b_do WHERE (b_do.pk = c_ei.fk_doc_obj) ) ) AS filename , c_ei.xmin AS xmin_export_item FROM clin.export_item c_ei ) inner_export_items JOIN dem.v_all_persons d_vp ON ( (d_vp.pk_identity = inner_export_items.pk_identity) ) );
F-Key | Name | Type | Description |
---|---|---|---|
pk_external_care | integer | ||
pk_identity | integer | ||
issue | text | ||
provider | text | ||
unit | text | ||
organization | text | ||
comment | text | ||
pk_health_issue | integer | ||
pk_org_unit | integer | ||
pk_encounter | integer | ||
xmin_external_care | xid | ||
modified_when | timestamp with time zone | ||
modified_by | name | ||
row_version | integer |
SELECT c_ec.pk AS pk_external_care , ( SELECT encounter.fk_patient FROM clin.encounter WHERE (encounter.pk = c_ec.fk_encounter) ) AS pk_identity , COALESCE (c_ec.issue , c_hi.description ) AS issue , c_ec.provider , d_ou.description AS unit , d_o.description AS organization , c_ec.comment , c_ec.fk_health_issue AS pk_health_issue , c_ec.fk_org_unit AS pk_org_unit , c_ec.fk_encounter AS pk_encounter , c_ec.xmin AS xmin_external_care , c_ec.modified_when , c_ec.modified_by , c_ec.row_version FROM ( ( (clin.external_care c_ec LEFT JOIN clin.health_issue c_hi ON ( (c_hi.pk = c_ec.fk_health_issue) ) ) LEFT JOIN dem.org_unit d_ou ON ( (c_ec.fk_org_unit = d_ou.pk) ) ) LEFT JOIN dem.org d_o ON ( (d_ou.fk_org = d_o.pk) ) );
F-Key | Name | Type | Description |
---|---|---|---|
pk_patient | integer | ||
modified_when | timestamp with time zone | ||
clin_when | timestamp with time zone | ||
modified_by | text | ||
soap_cat | text | ||
narrative | text | ||
pk_encounter | integer | ||
pk_episode | integer | ||
pk_health_issue | integer | ||
src_pk | integer | ||
src_table | text | ||
row_version | integer | ||
health_issue | text | ||
issue_laterality | character varying(2) | ||
issue_active | boolean | ||
issue_clinically_relevant | boolean | ||
issue_confidential | boolean | ||
episode | text | ||
episode_open | boolean | ||
encounter_started | timestamp with time zone | ||
encounter_last_affirmed | timestamp with time zone | ||
encounter_type | text | ||
encounter_l10n_type | text |
SELECT c_enc.fk_patient AS pk_patient , c_ec.modified_when , c_ec.modified_when AS clin_when , COALESCE ( ( SELECT staff.short_alias FROM dem.staff WHERE (staff.db_user = c_ec.modified_by) ) , ( ('<'::text || (c_ec.modified_by)::text ) || '>'::text ) ) AS modified_by , 's'::text AS soap_cat , ( ( ( ( ( ( ( ( ( ( ( ( (_ ('External care'::text) || COALESCE ( ( ( (' '::text || _ ('by'::text) ) || ' '::text ) || c_ec.provider ) ,''::text ) ) || ' @ '::text ) || d_ou.description ) || ' '::text ) || _ ('of'::text) ) || ' '::text ) || d_o.description ) || ' '::text ) || _ ('Issue:'::text) ) || ' '::text ) || COALESCE (c_hi.description , c_ec.issue ) ) || ' '::text ) || COALESCE ( ( (_ ('Comment:'::text) || ' '::text ) || c_ec.comment ) ,''::text ) ) AS narrative , c_ec.fk_encounter AS pk_encounter , NULL::integer AS pk_episode , c_ec.fk_health_issue AS pk_health_issue , c_ec.pk AS src_pk , 'clin.external_care'::text AS src_table , c_ec.row_version , c_hi.description AS health_issue , c_hi.laterality AS issue_laterality , c_hi.is_active AS issue_active , c_hi.clinically_relevant AS issue_clinically_relevant , c_hi.is_confidential AS issue_confidential , NULL::text AS episode , NULL::boolean AS episode_open , c_enc.started AS encounter_started , c_enc.last_affirmed AS encounter_last_affirmed , c_ety.description AS encounter_type , _ (c_ety.description) AS encounter_l10n_type FROM ( ( ( ( (clin.external_care c_ec JOIN clin.encounter c_enc ON ( (c_ec.fk_encounter = c_enc.pk) ) ) JOIN clin.encounter_type c_ety ON ( (c_enc.fk_type = c_ety.pk) ) ) LEFT JOIN clin.health_issue c_hi ON ( (c_ec.fk_health_issue = c_hi.pk) ) ) LEFT JOIN dem.org_unit d_ou ON ( (c_ec.fk_org_unit = d_ou.pk) ) ) LEFT JOIN dem.org d_o ON ( (d_ou.fk_org = d_o.pk) ) );
family history denormalized
F-Key | Name | Type | Description |
---|---|---|---|
pk_family_history | integer | ||
pk_patient | integer | ||
soap_cat | text | ||
relation | text | ||
l10n_relation | text | ||
condition | text | ||
age_noted | text | ||
age_of_death | interval | ||
contributed_to_death | boolean | ||
comment | text | ||
episode | text | ||
when_known_to_patient | timestamp with time zone | ||
name_relative | text | ||
dob_relative | timestamp with time zone | ||
is_genetic_relative | boolean | ||
pk_encounter | integer | ||
pk_episode | integer | ||
pk_health_issue | integer | ||
pk_fhx_relation_type | integer | ||
modified_when | timestamp with time zone | ||
pk_generic_codes | integer[] | ||
xmin_family_history | xid |
SELECT c_fh.pk AS pk_family_history , cenc.fk_patient AS pk_patient , c_fh.soap_cat , c_fhrt.description AS relation , _ (c_fhrt.description) AS l10n_relation , c_fh.narrative AS condition , c_fh.age_noted , c_fh.age_of_death , c_fh.contributed_to_death , c_fh.comment , cep.description AS episode , c_fh.clin_when AS when_known_to_patient , c_fh.name_relative , c_fh.dob_relative , c_fhrt.is_genetic AS is_genetic_relative , c_fh.fk_encounter AS pk_encounter , c_fh.fk_episode AS pk_episode , cep.fk_health_issue AS pk_health_issue , c_fhrt.pk AS pk_fhx_relation_type , c_fh.modified_when , COALESCE ( ( SELECT array_agg (c_lc2fhx.fk_generic_code) AS array_agg FROM clin.lnk_code2fhx c_lc2fhx WHERE (c_lc2fhx.fk_item = c_fh.pk) ) , ARRAY[]::integer[] ) AS pk_generic_codes , c_fh.xmin AS xmin_family_history FROM ( ( (clin.family_history c_fh JOIN clin.encounter cenc ON ( (c_fh.fk_encounter = cenc.pk) ) ) JOIN clin.episode cep ON ( (c_fh.fk_episode = cep.pk) ) ) LEFT JOIN clin.fhx_relation_type c_fhrt ON ( (c_fh.fk_relation_type = c_fhrt.pk) ) );
F-Key | Name | Type | Description |
---|---|---|---|
pk_patient | integer | ||
modified_when | timestamp with time zone | ||
clin_when | timestamp with time zone | ||
modified_by | text | ||
soap_cat | text | ||
narrative | text | ||
pk_encounter | integer | ||
pk_episode | integer | ||
pk_health_issue | integer | ||
src_pk | integer | ||
src_table | text | ||
row_version | integer | ||
health_issue | text | ||
issue_laterality | character varying(2) | ||
issue_active | boolean | ||
issue_clinically_relevant | boolean | ||
issue_confidential | boolean | ||
episode | text | ||
episode_open | boolean | ||
encounter_started | timestamp with time zone | ||
encounter_last_affirmed | timestamp with time zone | ||
encounter_type | text | ||
encounter_l10n_type | text |
SELECT c_enc.fk_patient AS pk_patient , c_fh.modified_when , c_fh.clin_when , COALESCE ( ( SELECT staff.short_alias FROM dem.staff WHERE (staff.db_user = c_fh.modified_by) ) , ( ('<'::text || (c_fh.modified_by)::text ) || '>'::text ) ) AS modified_by , c_fh.soap_cat , ( ( ( ( ( ( ( ( ( ( ( (_ (c_fhrt.description) || COALESCE ( (' @ '::text || c_fh.age_noted) ,''::text ) ) || ': '::text ) || c_fh.narrative ) || ' '::text ) || ' '::text ) || CASE WHEN (c_fh.contributed_to_death IS TRUE) THEN (_ ('contributed to death'::text) || ' '::text ) ELSE ''::text END ) || COALESCE ( ('@ '::text || (justify_interval (c_fh.age_of_death) )::text ) ,''::text ) ) || ' '::text ) || COALESCE ( ( (' '::text || c_fh.name_relative) || ','::text ) ,''::text ) ) || COALESCE ( (' '::text || to_char (c_fh.dob_relative ,'YYYY-MM-DD'::text ) ) ,''::text ) ) || COALESCE ( (' '::text || c_fh.comment) ,''::text ) ) || COALESCE ( ( ('; '::text || array_to_string ( ( SELECT array_agg ( ( ( ( ( ( ( ( (r_csr.code || ' ('::text) || r_ds.name_short ) || ' - '::text ) || r_ds.version ) || ' - '::text ) || r_ds.lang ) || '): '::text ) || r_csr.term ) ) AS array_agg FROM ( (clin.lnk_code2fhx c_lc2fhx JOIN ref.coding_system_root r_csr ON ( (c_lc2fhx.fk_generic_code = r_csr.pk_coding_system) ) ) JOIN ref.data_source r_ds ON ( (r_ds.pk = r_csr.fk_data_source) ) ) WHERE (c_lc2fhx.fk_item = c_fh.pk) ) ,'; '::text ) ) || ';'::text ) ,''::text ) ) AS narrative , c_fh.fk_encounter AS pk_encounter , c_fh.fk_episode AS pk_episode , c_epi.fk_health_issue AS pk_health_issue , c_fh.pk AS src_pk , 'clin.family_history'::text AS src_table , c_fh.row_version , c_hi.description AS health_issue , c_hi.laterality AS issue_laterality , c_hi.is_active AS issue_active , c_hi.clinically_relevant AS issue_clinically_relevant , c_hi.is_confidential AS issue_confidential , c_epi.description AS episode , c_epi.is_open AS episode_open , c_enc.started AS encounter_started , c_enc.last_affirmed AS encounter_last_affirmed , c_ety.description AS encounter_type , _ (c_ety.description) AS encounter_l10n_type FROM ( ( ( ( (clin.family_history c_fh JOIN clin.encounter c_enc ON ( (c_fh.fk_encounter = c_enc.pk) ) ) JOIN clin.encounter_type c_ety ON ( (c_enc.fk_type = c_ety.pk) ) ) JOIN clin.episode c_epi ON ( (c_fh.fk_episode = c_epi.pk) ) ) LEFT JOIN clin.fhx_relation_type c_fhrt ON ( (c_fh.fk_relation_type = c_fhrt.pk) ) ) LEFT JOIN clin.health_issue c_hi ON ( (c_epi.fk_health_issue = c_hi.pk) ) );
F-Key | Name | Type | Description |
---|---|---|---|
pk_patient | integer | ||
pk_health_issue | integer | ||
description | text | ||
summary | text | ||
laterality | character varying(2) | ||
age_noted | interval | ||
is_active | boolean | ||
clinically_relevant | boolean | ||
is_confidential | boolean | ||
is_cause_of_death | boolean | ||
pk_encounter | integer | ||
modified_when | timestamp with time zone | ||
modified_by | name | ||
row_version | integer | ||
grouping | text | ||
diagnostic_certainty_classification | text | ||
has_open_episode | boolean | ||
pk_generic_codes | integer[] | ||
xmin_health_issue | xid |
SELECT ( SELECT encounter.fk_patient FROM clin.encounter WHERE (encounter.pk = c_hi.fk_encounter) ) AS pk_patient , c_hi.pk AS pk_health_issue , c_hi.description , c_hi.summary , c_hi.laterality , c_hi.age_noted , c_hi.is_active , c_hi.clinically_relevant , c_hi.is_confidential , c_hi.is_cause_of_death , c_hi.fk_encounter AS pk_encounter , c_hi.modified_when , c_hi.modified_by , c_hi.row_version , c_hi."grouping" , c_hi.diagnostic_certainty_classification , (EXISTS ( SELECT 1 FROM clin.episode c_ep WHERE ( (c_ep.fk_health_issue = c_hi.pk) AND (c_ep.is_open IS TRUE) ) LIMIT 1 ) ) AS has_open_episode , COALESCE ( ( SELECT array_agg (c_lc2h.fk_generic_code) AS array_agg FROM clin.lnk_code2h_issue c_lc2h WHERE (c_lc2h.fk_item = c_hi.pk) ) , ARRAY[]::integer[] ) AS pk_generic_codes , c_hi.xmin AS xmin_health_issue FROM clin.health_issue c_hi;
F-Key | Name | Type | Description |
---|---|---|---|
pk_patient | integer | ||
modified_when | timestamp with time zone | ||
clin_when | timestamp with time zone | ||
modified_by | text | ||
soap_cat | text | ||
narrative | text | ||
pk_encounter | integer | ||
pk_episode | integer | ||
pk_health_issue | integer | ||
src_pk | integer | ||
src_table | text | ||
row_version | integer | ||
health_issue | text | ||
issue_laterality | character varying(2) | ||
issue_active | boolean | ||
issue_clinically_relevant | boolean | ||
issue_confidential | boolean | ||
episode | text | ||
episode_open | boolean | ||
encounter_started | timestamp with time zone | ||
encounter_last_affirmed | timestamp with time zone | ||
encounter_type | text | ||
encounter_l10n_type | text |
SELECT c_enc.fk_patient AS pk_patient , c_hi.modified_when , COALESCE ( ( SELECT (identity.dob + c_hi.age_noted) FROM dem.identity WHERE (identity.pk = ( SELECT encounter.fk_patient FROM clin.encounter WHERE (encounter.pk = c_hi.fk_encounter) ) ) ) , c_enc.started ) AS clin_when , COALESCE ( ( SELECT staff.short_alias FROM dem.staff WHERE (staff.db_user = c_hi.modified_by) ) , ( ('<'::text || (c_hi.modified_by)::text ) || '>'::text ) ) AS modified_by , 'a'::text AS soap_cat , ( ( ( ( ( ( ( ( ( ( ( ( (_ ('Health Issue'::text) || ' ('::text) || CASE WHEN c_hi.is_active THEN _ ('active'::text) ELSE _ ('inactive'::text) END ) || ', '::text ) || CASE WHEN c_hi.clinically_relevant THEN _ ('clinically relevant'::text) ELSE _ ('clinically not relevant'::text) END ) || COALESCE ( ( ', '::text || c_hi.diagnostic_certainty_classification ) ,''::text ) ) || '): '::text ) || c_hi.description ) || COALESCE ( ( ( ( ('; '::text || _ ('noted at age'::text) ) || ': '::text ) || (c_hi.age_noted)::text ) || '; '::text ) ,'; '::text ) ) || COALESCE ( ( ( (_ ('Laterality'::text) || ': '::text ) || (c_hi.laterality)::text ) || ' / '::text ) ,''::text ) ) || CASE WHEN c_hi.is_confidential THEN (_ ('confidential'::text) || ' / '::text ) ELSE ''::text END ) || CASE WHEN c_hi.is_cause_of_death THEN _ ('cause of death'::text) ELSE ''::text END ) || COALESCE ( ( ( ('; '::text || _ ('Summary'::text) ) || ': '::text ) || c_hi.summary ) ,''::text ) ) || COALESCE ( ( ('; '::text || array_to_string ( ( SELECT array_agg ( ( ( ( ( ( ( ( (r_csr.code || ' ('::text) || r_ds.name_short ) || ' - '::text ) || r_ds.version ) || ' - '::text ) || r_ds.lang ) || '): '::text ) || r_csr.term ) ) AS array_agg FROM ( (clin.lnk_code2h_issue c_lc2h JOIN ref.coding_system_root r_csr ON ( (c_lc2h.fk_generic_code = r_csr.pk_coding_system) ) ) JOIN ref.data_source r_ds ON ( (r_ds.pk = r_csr.fk_data_source) ) ) WHERE (c_lc2h.fk_item = c_hi.pk) ) ,'; '::text ) ) || ';'::text ) ,''::text ) ) AS narrative , c_hi.fk_encounter AS pk_encounter , NULL::integer AS pk_episode , c_hi.pk AS pk_health_issue , c_hi.pk AS src_pk , 'clin.health_issue'::text AS src_table , c_hi.row_version , c_hi.description AS health_issue , c_hi.laterality AS issue_laterality , c_hi.is_active AS issue_active , c_hi.clinically_relevant AS issue_clinically_relevant , c_hi.is_confidential AS issue_confidential , NULL::text AS episode , NULL::boolean AS episode_open , c_enc.started AS encounter_started , c_enc.last_affirmed AS encounter_last_affirmed , c_ety.description AS encounter_type , _ (c_ety.description) AS encounter_l10n_type FROM ( (clin.health_issue c_hi JOIN clin.encounter c_enc ON ( (c_hi.fk_encounter = c_enc.pk) ) ) JOIN clin.encounter_type c_ety ON ( (c_enc.fk_type = c_ety.pk) ) );
F-Key | Name | Type | Description |
---|---|---|---|
pk_hospital_stay | integer | ||
pk_patient | integer | ||
hospital | text | ||
ward | text | ||
comment | text | ||
admission | timestamp with time zone | ||
discharge | timestamp with time zone | ||
soap_cat | text | ||
episode | text | ||
health_issue | text | ||
pk_encounter | integer | ||
pk_episode | integer | ||
pk_health_issue | integer | ||
pk_org_unit | integer | ||
pk_org | integer | ||
modified_when | timestamp with time zone | ||
modified_by | text | ||
row_version | integer | ||
xmin_hospital_stay | xid |
SELECT c_hs.pk AS pk_hospital_stay , ( SELECT encounter.fk_patient FROM clin.encounter WHERE (encounter.pk = c_hs.fk_encounter) ) AS pk_patient , d_o.description AS hospital , d_ou.description AS ward , c_hs.narrative AS comment , c_hs.clin_when AS admission , c_hs.discharge , c_hs.soap_cat , c_e.description AS episode , c_hi.description AS health_issue , c_hs.fk_encounter AS pk_encounter , c_hs.fk_episode AS pk_episode , c_hi.pk AS pk_health_issue , c_hs.fk_org_unit AS pk_org_unit , d_o.pk AS pk_org , c_hs.modified_when , COALESCE ( ( SELECT staff.short_alias FROM dem.staff WHERE (staff.db_user = c_hs.modified_by) ) , ( ('<'::text || (c_hs.modified_by)::text ) || '>'::text ) ) AS modified_by , c_hs.row_version , c_hs.xmin AS xmin_hospital_stay FROM ( ( ( (clin.hospital_stay c_hs LEFT JOIN clin.episode c_e ON ( (c_e.pk = c_hs.fk_episode) ) ) LEFT JOIN clin.health_issue c_hi ON ( (c_hi.pk = c_e.fk_health_issue) ) ) LEFT JOIN dem.org_unit d_ou ON ( (d_ou.pk = c_hs.fk_org_unit) ) ) LEFT JOIN dem.org d_o ON ( (d_o.pk = d_ou.fk_org) ) );
F-Key | Name | Type | Description |
---|---|---|---|
pk_patient | integer | ||
modified_when | timestamp with time zone | ||
clin_when | timestamp with time zone | ||
modified_by | text | ||
soap_cat | text | ||
narrative | text | ||
pk_encounter | integer | ||
pk_episode | integer | ||
pk_health_issue | integer | ||
src_pk | integer | ||
src_table | text | ||
row_version | integer | ||
health_issue | text | ||
issue_laterality | character varying(2) | ||
issue_active | boolean | ||
issue_clinically_relevant | boolean | ||
issue_confidential | boolean | ||
episode | text | ||
episode_open | boolean | ||
encounter_started | timestamp with time zone | ||
encounter_last_affirmed | timestamp with time zone | ||
encounter_type | text | ||
encounter_l10n_type | text |
SELECT v_hospital_stays_journal_no_discharge.pk_patient , v_hospital_stays_journal_no_discharge.modified_when , v_hospital_stays_journal_no_discharge.clin_when , v_hospital_stays_journal_no_discharge.modified_by , v_hospital_stays_journal_no_discharge.soap_cat , v_hospital_stays_journal_no_discharge.narrative , v_hospital_stays_journal_no_discharge.pk_encounter , v_hospital_stays_journal_no_discharge.pk_episode , v_hospital_stays_journal_no_discharge.pk_health_issue , v_hospital_stays_journal_no_discharge.src_pk , v_hospital_stays_journal_no_discharge.src_table , v_hospital_stays_journal_no_discharge.row_version , v_hospital_stays_journal_no_discharge.health_issue , v_hospital_stays_journal_no_discharge.issue_laterality , v_hospital_stays_journal_no_discharge.issue_active , v_hospital_stays_journal_no_discharge.issue_clinically_relevant , v_hospital_stays_journal_no_discharge.issue_confidential , v_hospital_stays_journal_no_discharge.episode , v_hospital_stays_journal_no_discharge.episode_open , v_hospital_stays_journal_no_discharge.encounter_started , v_hospital_stays_journal_no_discharge.encounter_last_affirmed , v_hospital_stays_journal_no_discharge.encounter_type , v_hospital_stays_journal_no_discharge.encounter_l10n_type FROM clin.v_hospital_stays_journal_no_discharge UNION ALL SELECT v_hospital_stays_journal_one_day.pk_patient , v_hospital_stays_journal_one_day.modified_when , v_hospital_stays_journal_one_day.clin_when , v_hospital_stays_journal_one_day.modified_by , v_hospital_stays_journal_one_day.soap_cat , v_hospital_stays_journal_one_day.narrative , v_hospital_stays_journal_one_day.pk_encounter , v_hospital_stays_journal_one_day.pk_episode , v_hospital_stays_journal_one_day.pk_health_issue , v_hospital_stays_journal_one_day.src_pk , v_hospital_stays_journal_one_day.src_table , v_hospital_stays_journal_one_day.row_version , v_hospital_stays_journal_one_day.health_issue , v_hospital_stays_journal_one_day.issue_laterality , v_hospital_stays_journal_one_day.issue_active , v_hospital_stays_journal_one_day.issue_clinically_relevant , v_hospital_stays_journal_one_day.issue_confidential , v_hospital_stays_journal_one_day.episode , v_hospital_stays_journal_one_day.episode_open , v_hospital_stays_journal_one_day.encounter_started , v_hospital_stays_journal_one_day.encounter_last_affirmed , v_hospital_stays_journal_one_day.encounter_type , v_hospital_stays_journal_one_day.encounter_l10n_type FROM clin.v_hospital_stays_journal_one_day UNION ALL SELECT v_hospital_stays_journal_multi_day_adm.pk_patient , v_hospital_stays_journal_multi_day_adm.modified_when , v_hospital_stays_journal_multi_day_adm.clin_when , v_hospital_stays_journal_multi_day_adm.modified_by , v_hospital_stays_journal_multi_day_adm.soap_cat , v_hospital_stays_journal_multi_day_adm.narrative , v_hospital_stays_journal_multi_day_adm.pk_encounter , v_hospital_stays_journal_multi_day_adm.pk_episode , v_hospital_stays_journal_multi_day_adm.pk_health_issue , v_hospital_stays_journal_multi_day_adm.src_pk , v_hospital_stays_journal_multi_day_adm.src_table , v_hospital_stays_journal_multi_day_adm.row_version , v_hospital_stays_journal_multi_day_adm.health_issue , v_hospital_stays_journal_multi_day_adm.issue_laterality , v_hospital_stays_journal_multi_day_adm.issue_active , v_hospital_stays_journal_multi_day_adm.issue_clinically_relevant , v_hospital_stays_journal_multi_day_adm.issue_confidential , v_hospital_stays_journal_multi_day_adm.episode , v_hospital_stays_journal_multi_day_adm.episode_open , v_hospital_stays_journal_multi_day_adm.encounter_started , v_hospital_stays_journal_multi_day_adm.encounter_last_affirmed , v_hospital_stays_journal_multi_day_adm.encounter_type , v_hospital_stays_journal_multi_day_adm.encounter_l10n_type FROM clin.v_hospital_stays_journal_multi_day_adm UNION ALL SELECT v_hospital_stays_journal_multi_day_dis.pk_patient , v_hospital_stays_journal_multi_day_dis.modified_when , v_hospital_stays_journal_multi_day_dis.clin_when , v_hospital_stays_journal_multi_day_dis.modified_by , v_hospital_stays_journal_multi_day_dis.soap_cat , v_hospital_stays_journal_multi_day_dis.narrative , v_hospital_stays_journal_multi_day_dis.pk_encounter , v_hospital_stays_journal_multi_day_dis.pk_episode , v_hospital_stays_journal_multi_day_dis.pk_health_issue , v_hospital_stays_journal_multi_day_dis.src_pk , v_hospital_stays_journal_multi_day_dis.src_table , v_hospital_stays_journal_multi_day_dis.row_version , v_hospital_stays_journal_multi_day_dis.health_issue , v_hospital_stays_journal_multi_day_dis.issue_laterality , v_hospital_stays_journal_multi_day_dis.issue_active , v_hospital_stays_journal_multi_day_dis.issue_clinically_relevant , v_hospital_stays_journal_multi_day_dis.issue_confidential , v_hospital_stays_journal_multi_day_dis.episode , v_hospital_stays_journal_multi_day_dis.episode_open , v_hospital_stays_journal_multi_day_dis.encounter_started , v_hospital_stays_journal_multi_day_dis.encounter_last_affirmed , v_hospital_stays_journal_multi_day_dis.encounter_type , v_hospital_stays_journal_multi_day_dis.encounter_l10n_type FROM clin.v_hospital_stays_journal_multi_day_dis;
F-Key | Name | Type | Description |
---|---|---|---|
pk_patient | integer | ||
modified_when | timestamp with time zone | ||
clin_when | timestamp with time zone | ||
modified_by | text | ||
soap_cat | text | ||
narrative | text | ||
pk_encounter | integer | ||
pk_episode | integer | ||
pk_health_issue | integer | ||
src_pk | integer | ||
src_table | text | ||
row_version | integer | ||
health_issue | text | ||
issue_laterality | character varying(2) | ||
issue_active | boolean | ||
issue_clinically_relevant | boolean | ||
issue_confidential | boolean | ||
episode | text | ||
episode_open | boolean | ||
encounter_started | timestamp with time zone | ||
encounter_last_affirmed | timestamp with time zone | ||
encounter_type | text | ||
encounter_l10n_type | text |
SELECT c_enc.fk_patient AS pk_patient , c_hs.modified_when , c_hs.clin_when , COALESCE ( ( SELECT staff.short_alias FROM dem.staff WHERE (staff.db_user = c_hs.modified_by) ) , ( ('<'::text || (c_hs.modified_by)::text ) || '>'::text ) ) AS modified_by , c_hs.soap_cat , ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( (_ ('hospital stay'::text) || ' '::text ) || ' '::text ) || _ ('admitted'::text) ) || ': '::text ) || to_char (c_hs.clin_when ,'YYYY-MM-DD'::text ) ) || ' '::text ) || ' '::text ) || _ ('discharged'::text) ) || ': '::text ) || to_char (c_hs.discharge ,'YYYY-MM-DD'::text ) ) || ' '::text ) || ' '::text ) || _ ('hospital'::text) ) || ': "'::text ) || d_ou.description ) || ' @ '::text ) || d_o.description ) || '"'::text ) || ' '::text ) || COALESCE ( (' '::text || c_hs.narrative) ,''::text ) ) AS narrative , c_hs.fk_encounter AS pk_encounter , c_hs.fk_episode AS pk_episode , ( SELECT episode.fk_health_issue FROM clin.episode WHERE (episode.pk = c_hs.fk_episode) ) AS pk_health_issue , c_hs.pk AS src_pk , 'clin.hospital_stay'::text AS src_table , c_hs.row_version , c_hi.description AS health_issue , c_hi.laterality AS issue_laterality , c_hi.is_active AS issue_active , c_hi.clinically_relevant AS issue_clinically_relevant , c_hi.is_confidential AS issue_confidential , c_epi.description AS episode , c_epi.is_open AS episode_open , c_enc.started AS encounter_started , c_enc.last_affirmed AS encounter_last_affirmed , c_ety.description AS encounter_type , _ (c_ety.description) AS encounter_l10n_type FROM ( ( ( ( ( (clin.hospital_stay c_hs JOIN clin.encounter c_enc ON ( (c_hs.fk_encounter = c_enc.pk) ) ) JOIN clin.encounter_type c_ety ON ( (c_enc.fk_type = c_ety.pk) ) ) JOIN clin.episode c_epi ON ( (c_hs.fk_episode = c_epi.pk) ) ) LEFT JOIN clin.health_issue c_hi ON ( (c_epi.fk_health_issue = c_hi.pk) ) ) LEFT JOIN dem.org_unit d_ou ON ( (d_ou.pk = c_hs.fk_org_unit) ) ) LEFT JOIN dem.org d_o ON ( (d_o.pk = d_ou.fk_org) ) ) WHERE ( (c_hs.discharge IS NOT NULL) AND (to_char (c_hs.clin_when ,'YYYYMMDD'::text ) <> to_char (c_hs.discharge ,'YYYYMMDD'::text ) ) );
F-Key | Name | Type | Description |
---|---|---|---|
pk_patient | integer | ||
modified_when | timestamp with time zone | ||
clin_when | timestamp with time zone | ||
modified_by | text | ||
soap_cat | text | ||
narrative | text | ||
pk_encounter | integer | ||
pk_episode | integer | ||
pk_health_issue | integer | ||
src_pk | integer | ||
src_table | text | ||
row_version | integer | ||
health_issue | text | ||
issue_laterality | character varying(2) | ||
issue_active | boolean | ||
issue_clinically_relevant | boolean | ||
issue_confidential | boolean | ||
episode | text | ||
episode_open | boolean | ||
encounter_started | timestamp with time zone | ||
encounter_last_affirmed | timestamp with time zone | ||
encounter_type | text | ||
encounter_l10n_type | text |
SELECT c_enc.fk_patient AS pk_patient , c_hs.modified_when , c_hs.discharge AS clin_when , COALESCE ( ( SELECT staff.short_alias FROM dem.staff WHERE (staff.db_user = c_hs.modified_by) ) , ( ('<'::text || (c_hs.modified_by)::text ) || '>'::text ) ) AS modified_by , c_hs.soap_cat , ( ( ( ( (_ ('discharged from'::text) || ' "'::text ) || d_ou.description ) || ' @ '::text ) || d_o.description ) || '"'::text ) AS narrative , c_hs.fk_encounter AS pk_encounter , c_hs.fk_episode AS pk_episode , ( SELECT episode.fk_health_issue FROM clin.episode WHERE (episode.pk = c_hs.fk_episode) ) AS pk_health_issue , c_hs.pk AS src_pk , 'clin.hospital_stay'::text AS src_table , c_hs.row_version , c_hi.description AS health_issue , c_hi.laterality AS issue_laterality , c_hi.is_active AS issue_active , c_hi.clinically_relevant AS issue_clinically_relevant , c_hi.is_confidential AS issue_confidential , c_epi.description AS episode , c_epi.is_open AS episode_open , c_enc.started AS encounter_started , c_enc.last_affirmed AS encounter_last_affirmed , c_ety.description AS encounter_type , _ (c_ety.description) AS encounter_l10n_type FROM ( ( ( ( ( (clin.hospital_stay c_hs JOIN clin.encounter c_enc ON ( (c_hs.fk_encounter = c_enc.pk) ) ) JOIN clin.encounter_type c_ety ON ( (c_enc.fk_type = c_ety.pk) ) ) JOIN clin.episode c_epi ON ( (c_hs.fk_episode = c_epi.pk) ) ) LEFT JOIN clin.health_issue c_hi ON ( (c_epi.fk_health_issue = c_hi.pk) ) ) LEFT JOIN dem.org_unit d_ou ON ( (d_ou.pk = c_hs.fk_org_unit) ) ) LEFT JOIN dem.org d_o ON ( (d_o.pk = d_ou.fk_org) ) ) WHERE ( (c_hs.discharge IS NOT NULL) AND (to_char (c_hs.clin_when ,'YYYYMMDD'::text ) <> to_char (c_hs.discharge ,'YYYYMMDD'::text ) ) );
F-Key | Name | Type | Description |
---|---|---|---|
pk_patient | integer | ||
modified_when | timestamp with time zone | ||
clin_when | timestamp with time zone | ||
modified_by | text | ||
soap_cat | text | ||
narrative | text | ||
pk_encounter | integer | ||
pk_episode | integer | ||
pk_health_issue | integer | ||
src_pk | integer | ||
src_table | text | ||
row_version | integer | ||
health_issue | text | ||
issue_laterality | character varying(2) | ||
issue_active | boolean | ||
issue_clinically_relevant | boolean | ||
issue_confidential | boolean | ||
episode | text | ||
episode_open | boolean | ||
encounter_started | timestamp with time zone | ||
encounter_last_affirmed | timestamp with time zone | ||
encounter_type | text | ||
encounter_l10n_type | text |
SELECT c_enc.fk_patient AS pk_patient , c_hs.modified_when , c_hs.clin_when , COALESCE ( ( SELECT staff.short_alias FROM dem.staff WHERE (staff.db_user = c_hs.modified_by) ) , ( ('<'::text || (c_hs.modified_by)::text ) || '>'::text ) ) AS modified_by , c_hs.soap_cat , ( ( ( ( ( ( ( ( ( ( ( ( ( (_ ('hospital stay'::text) || ' '::text ) || ' '::text ) || _ ('admitted'::text) ) || ': '::text ) || to_char (c_hs.clin_when ,'YYYY-MM-DD'::text ) ) || ' '::text ) || ' '::text ) || _ ('hospital'::text) ) || ': "'::text ) || d_ou.description ) || ' @ '::text ) || d_o.description ) || '"'::text ) || COALESCE ( (' '::text || c_hs.narrative) ,''::text ) ) AS narrative , c_hs.fk_encounter AS pk_encounter , c_hs.fk_episode AS pk_episode , ( SELECT episode.fk_health_issue FROM clin.episode WHERE (episode.pk = c_hs.fk_episode) ) AS pk_health_issue , c_hs.pk AS src_pk , 'clin.hospital_stay'::text AS src_table , c_hs.row_version , c_hi.description AS health_issue , c_hi.laterality AS issue_laterality , c_hi.is_active AS issue_active , c_hi.clinically_relevant AS issue_clinically_relevant , c_hi.is_confidential AS issue_confidential , c_epi.description AS episode , c_epi.is_open AS episode_open , c_enc.started AS encounter_started , c_enc.last_affirmed AS encounter_last_affirmed , c_ety.description AS encounter_type , _ (c_ety.description) AS encounter_l10n_type FROM ( ( ( ( ( (clin.hospital_stay c_hs JOIN clin.encounter c_enc ON ( (c_hs.fk_encounter = c_enc.pk) ) ) JOIN clin.encounter_type c_ety ON ( (c_enc.fk_type = c_ety.pk) ) ) JOIN clin.episode c_epi ON ( (c_hs.fk_episode = c_epi.pk) ) ) LEFT JOIN clin.health_issue c_hi ON ( (c_epi.fk_health_issue = c_hi.pk) ) ) LEFT JOIN dem.org_unit d_ou ON ( (d_ou.pk = c_hs.fk_org_unit) ) ) LEFT JOIN dem.org d_o ON ( (d_o.pk = d_ou.fk_org) ) ) WHERE (c_hs.discharge IS NULL);
F-Key | Name | Type | Description |
---|---|---|---|
pk_patient | integer | ||
modified_when | timestamp with time zone | ||
clin_when | timestamp with time zone | ||
modified_by | text | ||
soap_cat | text | ||
narrative | text | ||
pk_encounter | integer | ||
pk_episode | integer | ||
pk_health_issue | integer | ||
src_pk | integer | ||
src_table | text | ||
row_version | integer | ||
health_issue | text | ||
issue_laterality | character varying(2) | ||
issue_active | boolean | ||
issue_clinically_relevant | boolean | ||
issue_confidential | boolean | ||
episode | text | ||
episode_open | boolean | ||
encounter_started | timestamp with time zone | ||
encounter_last_affirmed | timestamp with time zone | ||
encounter_type | text | ||
encounter_l10n_type | text |
SELECT c_enc.fk_patient AS pk_patient , c_hs.modified_when , c_hs.clin_when , COALESCE ( ( SELECT staff.short_alias FROM dem.staff WHERE (staff.db_user = c_hs.modified_by) ) , ( ('<'::text || (c_hs.modified_by)::text ) || '>'::text ) ) AS modified_by , c_hs.soap_cat , ( ( ( ( ( ( ( ( ( ( ( ( ( ( (_ ('hospital stay'::text) || ' '::text ) || ' '::text ) || _ ('admitted/discharged'::text) ) || ': '::text ) || to_char (c_hs.clin_when ,'YYYY-MM-DD'::text ) ) || ' '::text ) || ' '::text ) || _ ('hospital'::text) ) || ': "'::text ) || d_ou.description ) || ' @ '::text ) || d_o.description ) || '"'::text ) || ' '::text ) || COALESCE ( (' '::text || c_hs.narrative) ,''::text ) ) AS narrative , c_hs.fk_encounter AS pk_encounter , c_hs.fk_episode AS pk_episode , ( SELECT episode.fk_health_issue FROM clin.episode WHERE (episode.pk = c_hs.fk_episode) ) AS pk_health_issue , c_hs.pk AS src_pk , 'clin.hospital_stay'::text AS src_table , c_hs.row_version , c_hi.description AS health_issue , c_hi.laterality AS issue_laterality , c_hi.is_active AS issue_active , c_hi.clinically_relevant AS issue_clinically_relevant , c_hi.is_confidential AS issue_confidential , c_epi.description AS episode , c_epi.is_open AS episode_open , c_enc.started AS encounter_started , c_enc.last_affirmed AS encounter_last_affirmed , c_ety.description AS encounter_type , _ (c_ety.description) AS encounter_l10n_type FROM ( ( ( ( ( (clin.hospital_stay c_hs JOIN clin.encounter c_enc ON ( (c_hs.fk_encounter = c_enc.pk) ) ) JOIN clin.encounter_type c_ety ON ( (c_enc.fk_type = c_ety.pk) ) ) JOIN clin.episode c_epi ON ( (c_hs.fk_episode = c_epi.pk) ) ) LEFT JOIN clin.health_issue c_hi ON ( (c_epi.fk_health_issue = c_hi.pk) ) ) LEFT JOIN dem.org_unit d_ou ON ( (d_ou.pk = c_hs.fk_org_unit) ) ) LEFT JOIN dem.org d_o ON ( (d_o.pk = d_ou.fk_org) ) ) WHERE ( (c_hs.discharge IS NOT NULL) AND (to_char (c_hs.clin_when ,'YYYYMMDD'::text ) = to_char (c_hs.discharge ,'YYYYMMDD'::text ) ) );
Shows incoming data but w/o the data field.
F-Key | Name | Type | Description |
---|---|---|---|
pk_incoming_data_unmatched | integer | ||
pk_patient_candidates | integer[] | ||
firstnames | text | ||
lastnames | text | ||
dob | date | ||
gender | text | ||
postcode | text | ||
other_info | text | ||
request_id | text | ||
requestor | text | ||
external_data_id | text | ||
comment | text | ||
pk_identity_disambiguated | integer | ||
pk_provider_disambiguated | integer | ||
data_type | text | ||
md5_sum | text | ||
data_size | integer | ||
xmin_incoming_data_unmatched | xid |
SELECT c_idu.pk AS pk_incoming_data_unmatched , c_idu.fk_patient_candidates AS pk_patient_candidates , c_idu.firstnames , c_idu.lastnames , c_idu.dob , c_idu.gender , c_idu.postcode , c_idu.other_info , c_idu.request_id , c_idu.requestor , c_idu.external_data_id , c_idu.comment , c_idu.fk_identity_disambiguated AS pk_identity_disambiguated , c_idu.fk_provider_disambiguated AS pk_provider_disambiguated , c_idu.type AS data_type , md5 (c_idu.data) AS md5_sum , octet_length (c_idu.data) AS data_size , c_idu.xmin AS xmin_incoming_data_unmatched FROM clin.incoming_data_unmatched c_idu;
Denormalizes indications per vaccine.
F-Key | Name | Type | Description |
---|---|---|---|
pk_vaccine | integer | ||
vaccine | text | ||
preparation | text | ||
atc_code | text | ||
is_fake_vaccine | boolean | ||
route_abbreviation | text | ||
route_description | text | ||
is_live | boolean | ||
min_age | interval | ||
max_age | interval | ||
comment | text | ||
indication | text | ||
l10n_indication | text | ||
atcs_single_indication | text[] | ||
atcs_combi_indication | text[] | ||
external_code | text | ||
external_code_type | text | ||
indications | text[] | ||
l10n_indications | text[] | ||
pk_indications | integer[] | ||
pk_route | integer | ||
pk_brand | integer | ||
pk_data_source | integer | ||
pk_indication | integer | ||
xmin_vaccine | xid |
SELECT cv.pk AS pk_vaccine , rbd.description AS vaccine , rbd.preparation , rbd.atc_code , rbd.is_fake AS is_fake_vaccine , cvr.abbreviation AS route_abbreviation , cvr.description AS route_description , cv.is_live , cv.min_age , cv.max_age , cv.comment , cvi.description AS indication , _ (cvi.description) AS l10n_indication , cvi.atcs_single_indication , cvi.atcs_combi_indication , rbd.external_code , rbd.external_code_type , ( SELECT array_agg (cvi2.description) AS array_agg FROM (clin.lnk_vaccine2inds clv2i_2 JOIN clin.vacc_indication cvi2 ON ( (clv2i_2.fk_indication = cvi2.id) ) ) WHERE (clv2i_2.fk_vaccine = cv.pk) ) AS indications , ( SELECT array_agg (_ (cvi2.description) ) AS array_agg FROM (clin.lnk_vaccine2inds clv2i_2 JOIN clin.vacc_indication cvi2 ON ( (clv2i_2.fk_indication = cvi2.id) ) ) WHERE (clv2i_2.fk_vaccine = cv.pk) ) AS l10n_indications , ( SELECT array_agg (clv2i_2.fk_indication) AS array_agg FROM (clin.lnk_vaccine2inds clv2i_2 JOIN clin.vacc_indication cvi2 ON ( (clv2i_2.fk_indication = cvi2.id) ) ) WHERE (clv2i_2.fk_vaccine = cv.pk) ) AS pk_indications , cv.id_route AS pk_route , cv.fk_brand AS pk_brand , rbd.fk_data_source AS pk_data_source , cvi.id AS pk_indication , cv.xmin AS xmin_vaccine FROM ( ( ( (clin.vaccine cv LEFT JOIN clin.vacc_route cvr ON ( (cvr.id = cv.id_route) ) ) JOIN ref.branded_drug rbd ON ( (rbd.pk = cv.fk_brand) ) ) JOIN clin.lnk_vaccine2inds clv2i ON ( (clv2i.fk_vaccine = cv.pk) ) ) JOIN clin.vacc_indication cvi ON ( (cvi.id = clv2i.fk_indication) ) );
Denormalized codes linked to EMR structures.
F-Key | Name | Type | Description |
---|---|---|---|
pk_item | integer | ||
item_table | regclass | ||
code | text | ||
base_code | text | ||
code_modifier | text | ||
term | text | ||
name_long | text | ||
name_short | text | ||
version | text | ||
lang | text | ||
code_table | regclass | ||
pk_generic_code | integer | ||
pk_data_source | integer | ||
pk_lnk_code2item | integer |
SELECT c_lc2ir.fk_item AS pk_item , (c_lc2ir.tableoid)::regclass AS item_table , (r_csr.code || COALESCE (c_lc2ir.code_modifier ,''::text ) ) AS code , r_csr.code AS base_code , c_lc2ir.code_modifier , r_csr.term , r_ds.name_long , r_ds.name_short , r_ds.version , r_ds.lang , (r_csr.tableoid)::regclass AS code_table , r_csr.pk_coding_system AS pk_generic_code , r_csr.fk_data_source AS pk_data_source , c_lc2ir.pk_lnk_code2item FROM ( (clin.lnk_code2item_root c_lc2ir JOIN ref.coding_system_root r_csr ON ( (r_csr.pk_coding_system = c_lc2ir.fk_generic_code) ) ) JOIN ref.data_source r_ds ON ( (r_ds.pk = r_csr.fk_data_source) ) );
Denormalizes indications per vaccine.
F-Key | Name | Type | Description |
---|---|---|---|
pk_vaccine | integer | ||
vaccine | text | ||
preparation | text | ||
atc_code | text | ||
is_fake_vaccine | boolean | ||
route_abbreviation | text | ||
route_description | text | ||
is_live | boolean | ||
min_age | interval | ||
max_age | interval | ||
comment | text | ||
indication | text | ||
l10n_indication | text | ||
atcs_single_indication | text[] | ||
atcs_combi_indication | text[] | ||
external_code | text | ||
external_code_type | text | ||
pk_route | integer | ||
pk_brand | integer | ||
pk_data_source | integer | ||
pk_indication | integer | ||
xmin_vaccine | xid |
SELECT cv.pk AS pk_vaccine , rbd.description AS vaccine , rbd.preparation , rbd.atc_code , rbd.is_fake AS is_fake_vaccine , cvr.abbreviation AS route_abbreviation , cvr.description AS route_description , cv.is_live , cv.min_age , cv.max_age , cv.comment , cvi.description AS indication , _ (cvi.description) AS l10n_indication , cvi.atcs_single_indication , cvi.atcs_combi_indication , rbd.external_code , rbd.external_code_type , cv.id_route AS pk_route , cv.fk_brand AS pk_brand , rbd.fk_data_source AS pk_data_source , cvi.id AS pk_indication , cv.xmin AS xmin_vaccine FROM ( ( ( (clin.vaccine cv LEFT JOIN clin.vacc_route cvr ON ( (cvr.id = cv.id_route) ) ) JOIN ref.branded_drug rbd ON ( (rbd.pk = cv.fk_brand) ) ) JOIN clin.lnk_vaccine2inds clv2i ON ( (clv2i.fk_vaccine = cv.pk) ) ) JOIN clin.vacc_indication cvi ON ( (cvi.id = clv2i.fk_indication) ) );
Lists the most recent encounters per patient. Logic of "most recent" is: for a patient: 1) select encounters with latest "last_affirmed", 2) from those select encounters with latest "started" 3) limit those to 1 if there are duplicates (same start and end of encounter!)
F-Key | Name | Type | Description |
---|---|---|---|
pk_encounter | integer | ||
pk_patient | integer | ||
reason_for_encounter | text | ||
assessment_of_encounter | text | ||
type | text | ||
l10n_type | text | ||
started | timestamp with time zone | ||
last_affirmed | timestamp with time zone | ||
pk_type | integer | ||
pk_location | integer |
SELECT ce1.pk AS pk_encounter , ce1.fk_patient AS pk_patient , ce1.reason_for_encounter , ce1.assessment_of_encounter , et.description AS type , _ (et.description) AS l10n_type , ce1.started , ce1.last_affirmed , ce1.fk_type AS pk_type , ce1.fk_location AS pk_location FROM clin.encounter ce1 , clin.encounter_type et WHERE ( (ce1.fk_type = et.pk) AND (ce1.started = ( SELECT max (ce2.started) AS max FROM clin.encounter ce2 WHERE (ce2.last_affirmed = ( SELECT max (ce3.last_affirmed) AS max FROM clin.encounter ce3 WHERE (ce3.fk_patient = ce1.fk_patient) ) ) LIMIT 1 ) ) );
patient narrative with denormalized context added
F-Key | Name | Type | Description |
---|---|---|---|
pk_patient | integer | ||
date | timestamp with time zone | ||
modified_by | text | ||
soap_cat | text | ||
narrative | text | ||
episode | text | ||
health_issue | text | ||
pk_item | integer | ||
pk_narrative | integer | ||
pk_health_issue | integer | ||
pk_episode | integer | ||
pk_encounter | integer | ||
xmin_clin_narrative | xid | ||
modified_when | timestamp with time zone | ||
row_version | integer | ||
pk_audit | integer | ||
modified_by_raw | name | ||
pk_generic_codes | integer[] |
SELECT c_enc.fk_patient AS pk_patient , c_n.clin_when AS date , COALESCE ( ( SELECT staff.short_alias FROM dem.staff WHERE (staff.db_user = c_n.modified_by) ) , ( ('<'::text || (c_n.modified_by)::text ) || '>'::text ) ) AS modified_by , c_n.soap_cat , c_n.narrative , c_epi.description AS episode , c_hi.description AS health_issue , c_n.pk_item , c_n.pk AS pk_narrative , c_epi.fk_health_issue AS pk_health_issue , c_n.fk_episode AS pk_episode , c_n.fk_encounter AS pk_encounter , c_n.xmin AS xmin_clin_narrative , c_n.modified_when , c_n.row_version , c_n.pk_audit , c_n.modified_by AS modified_by_raw , COALESCE ( ( SELECT array_agg (c_lc2n.fk_generic_code) AS array_agg FROM clin.lnk_code2narrative c_lc2n WHERE (c_lc2n.fk_item = c_n.pk) ) , ARRAY[]::integer[] ) AS pk_generic_codes FROM ( ( (clin.clin_narrative c_n LEFT JOIN clin.encounter c_enc ON ( (c_n.fk_encounter = c_enc.pk) ) ) LEFT JOIN clin.episode c_epi ON ( (c_n.fk_episode = c_epi.pk) ) ) LEFT JOIN clin.health_issue c_hi ON ( (c_epi.fk_health_issue = c_hi.pk) ) );
unformatted *complete* narrative for patients including health issue/episode/encounter descriptions, mainly for searching the narrative in context
F-Key | Name | Type | Description |
---|---|---|---|
pk_patient | integer | ||
soap_cat | text | ||
narrative | text | ||
pk_encounter | integer | ||
pk_episode | integer | ||
pk_health_issue | integer | ||
src_pk | integer | ||
src_table | text |
SELECT union_table.pk_patient , union_table.soap_cat , union_table.narrative , union_table.pk_encounter , union_table.pk_episode , union_table.pk_health_issue , union_table.src_pk , union_table.src_table FROM ( SELECT vpi.pk_patient , vpi.soap_cat , vpi.narrative , vpi.pk_encounter , vpi.pk_episode , vpi.pk_health_issue , vpi.pk_item AS src_pk , vpi.src_table FROM clin.v_pat_items vpi WHERE (vpi.src_table <> ALL (ARRAY['clin.allergy'::text ,'clin.test_result'::text ,'clin.procedure'::text ,'clin.substance_intake'::text ,'clin.family_history'::text] ) ) UNION ALL SELECT v_subst_intake4narr_search.pk_patient , v_subst_intake4narr_search.soap_cat , v_subst_intake4narr_search.narrative , v_subst_intake4narr_search.pk_encounter , v_subst_intake4narr_search.pk_episode , v_subst_intake4narr_search.pk_health_issue , v_subst_intake4narr_search.src_pk , v_subst_intake4narr_search.src_table FROM clin.v_subst_intake4narr_search WHERE (gm.is_null_or_blank_string (v_subst_intake4narr_search.narrative) IS FALSE ) UNION ALL SELECT ( SELECT encounter.fk_patient FROM clin.encounter WHERE (encounter.pk = cpr.fk_encounter) ) AS pk_patient , cpr.soap_cat , cpr.narrative , cpr.fk_encounter AS pk_encounter , cpr.fk_episode AS pk_episode , ( SELECT episode.fk_health_issue FROM clin.episode WHERE (episode.pk = cpr.fk_episode) ) AS pk_health_issue , cpr.pk AS src_pk , 'clin.procedure'::text AS src_table FROM clin.procedure cpr WHERE (cpr.narrative IS NOT NULL) UNION ALL SELECT ( SELECT encounter.fk_patient FROM clin.encounter WHERE (encounter.pk = ctr.fk_encounter) ) AS pk_patient , ctr.soap_cat , ( ( ( ( ( ( ( (COALESCE (ctr.narrative ,''::text ) || COALESCE ( (' '::text || ctr.val_alpha) ,''::text ) ) || COALESCE ( (' '::text || ctr.val_unit) ,''::text ) ) || COALESCE ( (' '::text || ctr.val_normal_range) ,''::text ) ) || COALESCE ( (' '::text || ctr.val_target_range) ,''::text ) ) || COALESCE ( (' '::text || ctr.norm_ref_group) ,''::text ) ) || COALESCE ( (' '::text || ctr.note_test_org) ,''::text ) ) || COALESCE ( (' '::text || ctr.material) ,''::text ) ) || COALESCE ( (' '::text || ctr.material_detail) ,''::text ) ) AS narrative , ctr.fk_encounter AS pk_encounter , ctr.fk_episode AS pk_episode , ( SELECT episode.fk_health_issue FROM clin.episode WHERE (episode.pk = ctr.fk_episode) ) AS pk_health_issue , ctr.pk AS src_pk , 'clin.test_result'::text AS src_table FROM clin.test_result ctr UNION ALL SELECT ( SELECT encounter.fk_patient FROM clin.encounter WHERE (encounter.pk = ( SELECT test_result.fk_encounter FROM clin.test_result WHERE (test_result.pk = crtr.fk_reviewed_row) ) ) ) AS pk_patient , 'o'::text AS soap_cat , crtr.comment AS narrative , ( SELECT test_result.fk_encounter FROM clin.test_result WHERE (test_result.pk = crtr.fk_reviewed_row) ) AS pk_encounter , ( SELECT test_result.fk_episode FROM clin.test_result WHERE (test_result.pk = crtr.fk_reviewed_row) ) AS pk_episode , ( SELECT episode.fk_health_issue FROM clin.episode WHERE (episode.pk = ( SELECT test_result.fk_episode FROM clin.test_result WHERE (test_result.pk = crtr.fk_reviewed_row) ) ) ) AS pk_health_issue , crtr.pk AS src_pk , 'clin.reviewed_test_results'::text AS src_table FROM clin.reviewed_test_results crtr UNION ALL SELECT ( SELECT encounter.fk_patient FROM clin.encounter WHERE (encounter.pk = cas.fk_encounter) ) AS pk_patient , 'o'::text AS soap_cat , cas.comment AS narrative , cas.fk_encounter AS pk_encounter , NULL::integer AS pk_episode , NULL::integer AS pk_health_issue , cas.pk AS src_pk , 'clin.allergy_state'::text AS src_table FROM clin.allergy_state cas WHERE (cas.comment IS NOT NULL) UNION ALL SELECT ( SELECT encounter.fk_patient FROM clin.encounter WHERE (encounter.pk = ca.fk_encounter) ) AS pk_patient , ca.soap_cat , ( ( ( ( (COALESCE (ca.narrative ,''::text ) || COALESCE ( (' '::text || ca.substance) ,''::text ) ) || COALESCE ( (' '::text || ca.substance_code) ,''::text ) ) || COALESCE ( (' '::text || ca.generics) ,''::text ) ) || COALESCE ( (' '::text || ca.allergene) ,''::text ) ) || COALESCE ( (' '::text || ca.atc_code) ,''::text ) ) AS narrative , ca.fk_encounter AS pk_encounter , ca.fk_episode AS pk_episode , ( SELECT episode.fk_health_issue FROM clin.episode WHERE (episode.pk = ca.fk_episode) ) AS pk_health_issue , ca.pk AS src_pk , 'clin.allergy'::text AS src_table FROM clin.allergy ca UNION ALL SELECT ( SELECT encounter.fk_patient FROM clin.encounter WHERE (encounter.pk = chi.fk_encounter) ) AS pk_patient , 'a'::text AS soap_cat , (chi.description || COALESCE ( (' '::text || chi.summary) ,''::text ) ) AS narrative , chi.fk_encounter AS pk_encounter , NULL::integer AS pk_episode , chi.pk AS pk_health_issue , chi.pk AS src_pk , 'clin.health_issue'::text AS src_table FROM clin.health_issue chi UNION ALL SELECT cenc.fk_patient AS pk_patient , 's'::text AS soap_cat , (COALESCE (cenc.reason_for_encounter ,''::text ) || COALESCE ( (' '::text || cenc.assessment_of_encounter) ,''::text ) ) AS narrative , cenc.pk AS pk_encounter , NULL::integer AS pk_episode , NULL::integer AS pk_health_issue , cenc.pk AS src_pk , 'clin.encounter'::text AS src_table FROM clin.encounter cenc UNION ALL SELECT ( SELECT encounter.fk_patient FROM clin.encounter WHERE (encounter.pk = cep.fk_encounter) ) AS pk_patient , 's'::text AS soap_cat , (cep.description || COALESCE ( (' '::text || cep.summary) ,''::text ) ) AS narrative , cep.fk_encounter AS pk_encounter , cep.pk AS pk_episode , cep.fk_health_issue AS pk_health_issue , cep.pk AS src_pk , 'clin.episode'::text AS src_table FROM clin.episode cep UNION ALL SELECT c_vfhx.pk_patient , c_vfhx.soap_cat , ( ( ( ( ( (c_vfhx.relation || ' / '::text) || c_vfhx.l10n_relation ) || ' '::text ) || c_vfhx.name_relative ) || ': '::text ) || c_vfhx.condition ) AS narrative , c_vfhx.pk_encounter , c_vfhx.pk_episode , c_vfhx.pk_health_issue , c_vfhx.pk_family_history AS src_pk , 'clin.family_history'::text AS src_table FROM clin.v_family_history c_vfhx UNION ALL SELECT vdm.pk_patient , 'o'::text AS soap_cat , ( ( ( (vdm.l10n_type || ' '::text) || COALESCE (vdm.ext_ref ,''::text ) ) || ' '::text ) || COALESCE (vdm.comment ,''::text ) ) AS narrative , vdm.pk_encounter , vdm.pk_episode , vdm.pk_health_issue , vdm.pk_doc AS src_pk , 'blobs.doc_med'::text AS src_table FROM blobs.v_doc_med vdm UNION ALL SELECT vo4d.pk_patient , 'o'::text AS soap_cat , vo4d.obj_comment AS narrative , vo4d.pk_encounter , vo4d.pk_episode , vo4d.pk_health_issue , vo4d.pk_obj AS src_pk , 'blobs.doc_obj'::text AS src_table FROM blobs.v_obj4doc_no_data vo4d UNION ALL SELECT vdd.pk_patient , 'o'::text AS soap_cat , vdd.description AS narrative , vdd.pk_encounter , vdd.pk_episode , vdd.pk_health_issue , vdd.pk_doc_desc AS src_pk , 'blobs.doc_desc'::text AS src_table FROM blobs.v_doc_desc vdd UNION ALL SELECT vrdo.pk_patient , 's'::text AS soap_cat , vrdo.comment AS narrative , NULL::integer AS pk_encounter , vrdo.pk_episode , vrdo.pk_health_issue , vrdo.pk_review_root AS src_pk , 'blobs.v_reviewed_doc_objects'::text AS src_table FROM blobs.v_reviewed_doc_objects vrdo UNION ALL SELECT d_vit.pk_identity AS pk_patient , 's'::text AS soap_cat , (d_vit.l10n_description || COALESCE ( (' '::text || d_vit.comment) ,''::text ) ) AS narrative , NULL::integer AS pk_encounter , NULL::integer AS pk_episode , NULL::integer AS pk_health_issue , d_vit.pk_identity_tag AS src_pk , 'dem.v_identity_tags'::text AS src_table FROM dem.v_identity_tags d_vit UNION ALL SELECT c_vec.pk_identity AS pk_patient , 's'::text AS soap_cat , CASE WHEN (c_vec.pk_health_issue IS NULL) THEN ( (COALESCE (c_vec.issue ,''::text ) || COALESCE ( (' / '::text || c_vec.provider) ,''::text ) ) || COALESCE ( (' / '::text || c_vec.comment) ,''::text ) ) ELSE (COALESCE (c_vec.provider ,''::text ) || COALESCE ( (' / '::text || c_vec.comment) ,''::text ) ) END AS narrative , c_vec.pk_encounter , NULL::integer AS pk_episode , c_vec.pk_health_issue , c_vec.pk_external_care AS src_pk , 'clin.v_external_care'::text AS src_table FROM clin.v_external_care c_vec UNION ALL SELECT c_vei.pk_identity AS pk_patient , 's'::text AS soap_cat , CASE WHEN (c_vei.pk_doc_obj IS NULL) THEN (COALESCE (c_vei.description ,''::text ) || COALESCE ( (' / '::text || c_vei.filename) ,''::text ) ) ELSE COALESCE (c_vei.description ,''::text ) END AS narrative , NULL::integer AS pk_encounter , NULL::integer AS pk_episode , NULL::integer AS pk_health_issue , c_vei.pk_export_item AS src_pk , 'clin.v_export_items'::text AS src_table FROM clin.v_export_items c_vei UNION ALL SELECT ( SELECT encounter.fk_patient FROM clin.encounter WHERE (encounter.pk = c_sh.fk_encounter) ) AS pk_patient , 'p'::text AS soap_cat , c_sh.rationale AS narrative , c_sh.fk_encounter AS pk_encounter , NULL::integer AS pk_episode , NULL::integer AS pk_health_issue , c_sh.pk AS src_pk , 'clin.suppressed_hint'::text AS src_table FROM clin.suppressed_hint c_sh ) union_table WHERE (btrim (COALESCE (union_table.narrative ,''::text ) ) <> ''::text );
F-Key | Name | Type | Description |
---|---|---|---|
pk_substance_intake | integer | ||
pk_patient | integer | ||
soap_cat | text | ||
brand | text | ||
preparation | text | ||
substance | text | ||
amount | numeric | ||
unit | text | ||
atc_substance | text | ||
atc_brand | text | ||
external_code_brand | text | ||
external_code_type_brand | text | ||
started | timestamp with time zone | ||
comment_on_start | text | ||
start_is_unknown | boolean | ||
start_is_approximate | boolean | ||
intake_is_approved_of | boolean | ||
harmful_use_type | integer | ||
last_checked_when | timestamp with time zone | ||
schedule | text | ||
duration | interval | ||
discontinued | timestamp with time zone | ||
discontinue_reason | text | ||
is_long_term | boolean | ||
aim | text | ||
episode | text | ||
health_issue | text | ||
notes | text | ||
fake_brand | boolean | ||
is_currently_active | boolean | ||
seems_inactive | boolean | ||
pk_brand | integer | ||
pk_data_source | integer | ||
pk_substance | integer | ||
pk_drug_component | integer | ||
pk_encounter | integer | ||
pk_episode | integer | ||
pk_health_issue | integer | ||
modified_when | timestamp with time zone | ||
modified_by | name | ||
row_version | integer | ||
xmin_substance_intake | xid |
SELECT c_si.pk AS pk_substance_intake , ( SELECT encounter.fk_patient FROM clin.encounter WHERE (encounter.pk = c_si.fk_encounter) ) AS pk_patient , c_si.soap_cat , NULL::text AS brand , c_si.preparation , r_cs.description AS substance , r_cs.amount , r_cs.unit , r_cs.atc_code AS atc_substance , NULL::text AS atc_brand , NULL::text AS external_code_brand , NULL::text AS external_code_type_brand , CASE WHEN (c_si.comment_on_start = '?'::text) THEN NULL::timestamp with time zone ELSE c_si.clin_when END AS started , c_si.comment_on_start , CASE WHEN (c_si.comment_on_start = '?'::text) THEN true ELSE false END AS start_is_unknown , CASE WHEN (c_si.comment_on_start IS NULL) THEN false ELSE true END AS start_is_approximate , c_si.intake_is_approved_of , c_si.harmful_use_type , CASE WHEN (c_si.harmful_use_type IS NULL) THEN NULL::timestamp with time zone ELSE c_enc.started END AS last_checked_when , c_si.schedule , c_si.duration , c_si.discontinued , c_si.discontinue_reason , c_si.is_long_term , c_si.aim , cep.description AS episode , c_hi.description AS health_issue , c_si.narrative AS notes , NULL::boolean AS fake_brand , CASE WHEN (c_si.discontinued IS NULL) THEN true ELSE false END AS is_currently_active , CASE WHEN (c_si.discontinued IS NOT NULL) THEN true WHEN (c_si.clin_when IS NULL) THEN CASE WHEN (c_si.is_long_term IS TRUE) THEN false ELSE NULL::boolean END WHEN ( (c_si.clin_when > now () ) IS TRUE ) THEN true WHEN ( ( (c_si.clin_when + c_si.duration) < now () ) IS TRUE ) THEN true WHEN ( ( (c_si.clin_when + c_si.duration) > now () ) IS TRUE ) THEN false ELSE NULL::boolean END AS seems_inactive , NULL::integer AS pk_brand , NULL::integer AS pk_data_source , r_cs.pk AS pk_substance , NULL::integer AS pk_drug_component , c_si.fk_encounter AS pk_encounter , c_si.fk_episode AS pk_episode , cep.fk_health_issue AS pk_health_issue , c_si.modified_when , c_si.modified_by , c_si.row_version , c_si.xmin AS xmin_substance_intake FROM ( ( ( (clin.substance_intake c_si JOIN ref.consumable_substance r_cs ON ( (c_si.fk_substance = r_cs.pk) ) ) LEFT JOIN clin.episode cep ON ( (c_si.fk_episode = cep.pk) ) ) LEFT JOIN clin.health_issue c_hi ON ( (c_hi.pk = cep.fk_health_issue) ) ) LEFT JOIN clin.encounter c_enc ON ( (c_si.fk_encounter = c_enc.pk) ) ) WHERE (c_si.fk_drug_component IS NULL);
denormalizes clin.allergy
F-Key | Name | Type | Description |
---|---|---|---|
pk_allergy | integer | ||
pk_patient | integer | ||
soap_cat | text | ||
descriptor | text | ||
allergene | text | ||
substance | text | ||
substance_code | text | ||
generics | text | ||
generic_specific | boolean | ||
atc_code | text | ||
type | text | ||
l10n_type | text | ||
definite | boolean | ||
reaction | text | ||
pk_type | integer | ||
pk_item | integer | ||
date | timestamp with time zone | ||
pk_health_issue | integer | ||
pk_episode | integer | ||
pk_encounter | integer | ||
modified_when | timestamp with time zone | ||
modified_by | name | ||
xmin_allergy | xid |
SELECT a.pk AS pk_allergy , ( SELECT encounter.fk_patient FROM clin.encounter WHERE (encounter.pk = a.fk_encounter) ) AS pk_patient , a.soap_cat , CASE WHEN (COALESCE (btrim (a.allergene) ,''::text ) = ''::text ) THEN a.substance ELSE a.allergene END AS descriptor , a.allergene , a.substance , a.substance_code , a.generics , a.generic_specific , a.atc_code , at.value AS type , _ (at.value) AS l10n_type , a.definite , a.narrative AS reaction , a.fk_type AS pk_type , a.pk_item , a.clin_when AS date , ( SELECT episode.fk_health_issue FROM clin.episode WHERE (episode.pk = a.fk_episode) ) AS pk_health_issue , a.fk_episode AS pk_episode , a.fk_encounter AS pk_encounter , a.modified_when , a.modified_by , a.xmin AS xmin_allergy FROM clin.allergy a , clin._enum_allergy_type at WHERE (at.pk = a.fk_type);
F-Key | Name | Type | Description |
---|---|---|---|
pk_patient | integer | ||
modified_when | timestamp with time zone | ||
clin_when | timestamp with time zone | ||
modified_by | text | ||
soap_cat | text | ||
narrative | text | ||
pk_encounter | integer | ||
pk_episode | integer | ||
pk_health_issue | integer | ||
src_pk | integer | ||
src_table | text | ||
row_version | integer | ||
health_issue | text | ||
issue_laterality | character varying(2) | ||
issue_active | boolean | ||
issue_clinically_relevant | boolean | ||
issue_confidential | boolean | ||
episode | text | ||
episode_open | boolean | ||
encounter_started | timestamp with time zone | ||
encounter_last_affirmed | timestamp with time zone | ||
encounter_type | text | ||
encounter_l10n_type | text |
SELECT c_enc.fk_patient AS pk_patient , c_all.modified_when , c_all.clin_when , COALESCE ( ( SELECT staff.short_alias FROM dem.staff WHERE (staff.db_user = c_all.modified_by) ) , ( ('<'::text || (c_all.modified_by)::text ) || '>'::text ) ) AS modified_by , c_all.soap_cat , ( ( ( ( ( ( ( ( ( ( ( (_ ('Allergy'::text) || ' ('::text) || _ (c_at.value) ) || '): '::text ) || COALESCE (c_all.narrative ,''::text ) ) || ' '::text ) || _ ('substance'::text) ) || ': '::text ) || c_all.substance ) || '; '::text ) || COALESCE ( ( ( (_ ('allergene'::text) || ': '::text ) || c_all.allergene ) || '; '::text ) ,''::text ) ) || COALESCE ( ( ( (_ ('generic'::text) || ': '::text ) || c_all.generics ) || '; '::text ) ,''::text ) ) || COALESCE ( ( ( (_ ('ATC code'::text) || ': '::text ) || c_all.atc_code ) || '; '::text ) ,''::text ) ) AS narrative , c_all.fk_encounter AS pk_encounter , c_all.fk_episode AS pk_episode , c_epi.fk_health_issue AS pk_health_issue , c_all.pk AS src_pk , 'clin.allergy'::text AS src_table , c_all.row_version , c_hi.description AS health_issue , c_hi.laterality AS issue_laterality , c_hi.is_active AS issue_active , c_hi.clinically_relevant AS issue_clinically_relevant , c_hi.is_confidential AS issue_confidential , c_epi.description AS episode , c_epi.is_open AS episode_open , c_enc.started AS encounter_started , c_enc.last_affirmed AS encounter_last_affirmed , c_ety.description AS encounter_type , _ (c_ety.description) AS encounter_l10n_type FROM ( ( ( ( (clin.allergy c_all JOIN clin._enum_allergy_type c_at ON ( (c_all.fk_type = c_at.pk) ) ) JOIN clin.encounter c_enc ON ( (c_all.fk_encounter = c_enc.pk) ) ) JOIN clin.encounter_type c_ety ON ( (c_enc.fk_type = c_ety.pk) ) ) JOIN clin.episode c_epi ON ( (c_all.fk_episode = c_epi.pk) ) ) LEFT JOIN clin.health_issue c_hi ON ( (c_epi.fk_health_issue = c_hi.pk) ) );
F-Key | Name | Type | Description |
---|---|---|---|
pk_patient | integer | ||
modified_when | timestamp with time zone | ||
modified_by | text | ||
last_confirmed | timestamp with time zone | ||
has_allergy | integer | ||
comment | text | ||
pk_encounter | integer | ||
pk_allergy_state | integer | ||
xmin_allergy_state | xid |
SELECT ( SELECT encounter.fk_patient FROM clin.encounter WHERE (encounter.pk = a.fk_encounter) ) AS pk_patient , a.modified_when , COALESCE ( ( SELECT staff.short_alias FROM dem.staff WHERE (staff.db_user = a.modified_by) ) , ( ('<'::text || (a.modified_by)::text ) || '>'::text ) ) AS modified_by , a.last_confirmed , a.has_allergy , a.comment , a.fk_encounter AS pk_encounter , a.pk AS pk_allergy_state , a.xmin AS xmin_allergy_state FROM clin.allergy_state a;
F-Key | Name | Type | Description |
---|---|---|---|
pk_patient | integer | ||
modified_when | timestamp with time zone | ||
clin_when | timestamp with time zone | ||
modified_by | text | ||
soap_cat | text | ||
narrative | text | ||
pk_encounter | integer | ||
pk_episode | integer | ||
pk_health_issue | integer | ||
src_pk | integer | ||
src_table | text | ||
row_version | integer | ||
health_issue | text | ||
issue_laterality | text | ||
issue_active | boolean | ||
issue_clinically_relevant | boolean | ||
issue_confidential | boolean | ||
episode | text | ||
episode_open | boolean | ||
encounter_started | timestamp with time zone | ||
encounter_last_affirmed | timestamp with time zone | ||
encounter_type | text | ||
encounter_l10n_type | text |
SELECT c_enc.fk_patient AS pk_patient , c_ast.modified_when , COALESCE (c_ast.last_confirmed , c_ast.modified_when ) AS clin_when , COALESCE ( ( SELECT staff.short_alias FROM dem.staff WHERE (staff.db_user = c_ast.modified_by) ) , ( ('<'::text || (c_ast.modified_by)::text ) || '>'::text ) ) AS modified_by , 'o'::text AS soap_cat , ( ( ( (_ ('Allergy state'::text) || ': '::text ) || CASE WHEN (c_ast.has_allergy IS NULL) THEN _ ( 'unknown, unasked'::text ) WHEN (c_ast.has_allergy = 0) THEN _ ('no known allergies'::text) WHEN (c_ast.has_allergy = 1) THEN _ ('does have allergies'::text) ELSE NULL::text END ) || COALESCE ( ( ( ( ' ('::text || _ ('last confirmed'::text) ) || to_char (c_ast.last_confirmed ,' YYYY-MM-DD HH24:MI'::text ) ) || ')'::text ) ,''::text ) ) || COALESCE ( (' '::text || c_ast.comment) ,''::text ) ) AS narrative , c_ast.fk_encounter AS pk_encounter , NULL::integer AS pk_episode , NULL::integer AS pk_health_issue , c_ast.pk AS src_pk , 'clin.allergy_state'::text AS src_table , c_ast.row_version , NULL::text AS health_issue , NULL::text AS issue_laterality , NULL::boolean AS issue_active , NULL::boolean AS issue_clinically_relevant , NULL::boolean AS issue_confidential , NULL::text AS episode , NULL::boolean AS episode_open , c_enc.started AS encounter_started , c_enc.last_affirmed AS encounter_last_affirmed , c_ety.description AS encounter_type , _ (c_ety.description) AS encounter_l10n_type FROM ( (clin.allergy_state c_ast JOIN clin.encounter c_enc ON ( (c_ast.fk_encounter = c_enc.pk) ) ) JOIN clin.encounter_type c_ety ON ( (c_enc.fk_type = c_ety.pk) ) ) UNION ALL SELECT c_enc.fk_patient AS pk_patient , now () AS modified_when , now () AS clin_when , COALESCE ( ( SELECT staff.short_alias FROM dem.staff WHERE (staff.db_user = c_ast.modified_by) ) , ( ('<'::text || (c_ast.modified_by)::text ) || '>'::text ) ) AS modified_by , 'o'::text AS soap_cat , ( ( ( ( ( ( ( ( (_ ('Allergy state'::text) || ' ('::text) || _ ('copy'::text) ) || '): '::text ) || CASE WHEN (c_ast.has_allergy IS NULL) THEN _ ( 'unknown, unasked'::text ) WHEN (c_ast.has_allergy = 0) THEN _ ('no known allergies'::text) WHEN (c_ast.has_allergy = 1) THEN _ ('does have allergies'::text) ELSE NULL::text END ) || COALESCE ( (' '::text || c_ast.comment) ,''::text ) ) || COALESCE ( ( (' '::text || _ ('last confirmed'::text) ) || to_char (c_ast.last_confirmed ,' YYYY-MM-DD HH24:MI'::text ) ) ,''::text ) ) || COALESCE ( ( (' '::text || _ ('entry last modified'::text) ) || to_char (c_ast.modified_when ,' YYYY-MM-DD HH24:MI'::text ) ) ,''::text ) ) || COALESCE ( ( (' '::text || _ ('encounter started'::text) ) || to_char (c_enc.started ,' YYYY-MM-DD HH24:MI'::text ) ) ,''::text ) ) || COALESCE ( ( (' '::text || _ ('encounter last affirmed'::text) ) || to_char (c_enc.last_affirmed ,' YYYY-MM-DD HH24:MI'::text ) ) ,''::text ) ) AS narrative , c_ast.fk_encounter AS pk_encounter , NULL::integer AS pk_episode , NULL::integer AS pk_health_issue , c_ast.pk AS src_pk , 'clin.allergy_state'::text AS src_table , c_ast.row_version , NULL::text AS health_issue , NULL::text AS issue_laterality , NULL::boolean AS issue_active , NULL::boolean AS issue_clinically_relevant , NULL::boolean AS issue_confidential , NULL::text AS episode , NULL::boolean AS episode_open , now () AS encounter_started , now () AS encounter_last_affirmed , c_ety.description AS encounter_type , _ (c_ety.description) AS encounter_l10n_type FROM ( (clin.allergy_state c_ast JOIN clin.encounter c_enc ON ( (c_ast.fk_encounter = c_enc.pk) ) ) JOIN clin.encounter_type c_ety ON ( (c_enc.fk_type = c_ety.pk) ) );
Details on encounters.
F-Key | Name | Type | Description |
---|---|---|---|
pk_encounter | integer | ||
pk_patient | integer | ||
started | timestamp with time zone | ||
type | text | ||
l10n_type | text | ||
reason_for_encounter | text | ||
assessment_of_encounter | text | ||
last_affirmed | timestamp with time zone | ||
source_time_zone | interval | ||
started_original_tz | timestamp without time zone | ||
last_affirmed_original_tz | timestamp without time zone | ||
praxis_branch | text | ||
praxis | text | ||
pk_org_unit | integer | ||
pk_org | integer | ||
pk_unit_type | integer | ||
pk_org_type | integer | ||
pk_type | integer | ||
pk_generic_codes_rfe | integer[] | ||
pk_generic_codes_aoe | integer[] | ||
xmin_encounter | xid | ||
row_version | integer | ||
pk_audit | integer | ||
modified_when | timestamp with time zone | ||
modified_by_raw | name | ||
modified_by | text |
SELECT c_enc.pk AS pk_encounter , c_enc.fk_patient AS pk_patient , c_enc.started , c_et.description AS type , _ (c_et.description) AS l10n_type , c_enc.reason_for_encounter , c_enc.assessment_of_encounter , c_enc.last_affirmed , c_enc.source_time_zone , ( SELECT timezone ( ( SELECT c_enc1.source_time_zone FROM clin.encounter c_enc1 WHERE (c_enc1.pk = c_enc.pk) ) , c_enc.started ) AS timezone ) AS started_original_tz , ( SELECT timezone ( ( SELECT c_enc1.source_time_zone FROM clin.encounter c_enc1 WHERE (c_enc1.pk = c_enc.pk) ) , c_enc.last_affirmed ) AS timezone ) AS last_affirmed_original_tz , COALESCE (d_ou.description ,'?'::text ) AS praxis_branch , COALESCE (d_o.description ,'?'::text ) AS praxis , c_enc.fk_location AS pk_org_unit , d_ou.fk_org AS pk_org , d_ou.fk_category AS pk_unit_type , d_o.fk_category AS pk_org_type , c_enc.fk_type AS pk_type , COALESCE ( ( SELECT array_agg (c_lc2r.fk_generic_code) AS array_agg FROM clin.lnk_code2rfe c_lc2r WHERE (c_lc2r.fk_item = c_enc.pk) ) , ARRAY[]::integer[] ) AS pk_generic_codes_rfe , COALESCE ( ( SELECT array_agg (c_lc2a.fk_generic_code) AS array_agg FROM clin.lnk_code2aoe c_lc2a WHERE (c_lc2a.fk_item = c_enc.pk) ) , ARRAY[]::integer[] ) AS pk_generic_codes_aoe , c_enc.xmin AS xmin_encounter , c_enc.row_version , c_enc.pk_audit , c_enc.modified_when , c_enc.modified_by AS modified_by_raw , COALESCE ( ( SELECT staff.short_alias FROM dem.staff WHERE (staff.db_user = c_enc.modified_by) ) , ( ('<'::text || (c_enc.modified_by)::text ) || '>'::text ) ) AS modified_by FROM ( ( (clin.encounter c_enc LEFT JOIN clin.encounter_type c_et ON ( (c_enc.fk_type = c_et.pk) ) ) LEFT JOIN dem.org_unit d_ou ON ( (c_enc.fk_location = d_ou.pk) ) ) LEFT JOIN dem.org d_o ON ( (d_ou.fk_org = d_o.pk) ) );
F-Key | Name | Type | Description |
---|---|---|---|
pk_patient | integer | ||
modified_when | timestamp with time zone | ||
clin_when | timestamp with time zone | ||
modified_by | text | ||
soap_cat | text | ||
narrative | text | ||
pk_encounter | integer | ||
pk_episode | integer | ||
pk_health_issue | integer | ||
src_pk | integer | ||
src_table | text | ||
row_version | integer | ||
health_issue | text | ||
issue_laterality | text | ||
issue_active | boolean | ||
issue_clinically_relevant | boolean | ||
issue_confidential | boolean | ||
episode | text | ||
episode_open | boolean | ||
encounter_started | timestamp with time zone | ||
encounter_last_affirmed | timestamp with time zone | ||
encounter_type | text | ||
encounter_l10n_type | text |
SELECT c_enc.fk_patient AS pk_patient , c_enc.modified_when , c_enc.started AS clin_when , COALESCE ( ( SELECT staff.short_alias FROM dem.staff WHERE (staff.db_user = c_enc.modified_by) ) , ( ('<'::text || (c_enc.modified_by)::text ) || '>'::text ) ) AS modified_by , NULL::text AS soap_cat , ( ( ( ( ( ( ( ( (_ ('Encounter'::text) || ': '::text ) || ( SELECT _ (encounter_type.description) AS _ FROM clin.encounter_type WHERE (encounter_type.pk = c_enc.fk_type) ) ) || to_char (c_enc.started ,' YYYY-MM-DD HH24:MI'::text ) ) || to_char (c_enc.last_affirmed ,' - HH24:MI'::text ) ) || COALESCE ( ( ( ( ( ( ( ( (' @ '::text || _ ('branch'::text) ) || ' "'::text ) || d_ou.description ) || '" '::text ) || _ ('of'::text) ) || ' "'::text ) || d_o.description ) || '"'::text ) ,''::text ) ) || COALESCE ( ( ( (' '::text || _ ('RFE'::text) ) || ': '::text ) || c_enc.reason_for_encounter ) ,''::text ) ) || COALESCE ( ( (' '::text || array_to_string ( ( SELECT array_agg ( ( ( ( ( ( ( ( (r_csr.code || ' ('::text) || r_ds.name_short ) || ' - '::text ) || r_ds.version ) || ' - '::text ) || r_ds.lang ) || '): '::text ) || r_csr.term ) ) AS array_agg FROM ( (clin.lnk_code2rfe c_lc2r JOIN ref.coding_system_root r_csr ON ( (c_lc2r.fk_generic_code = r_csr.pk_coding_system) ) ) JOIN ref.data_source r_ds ON ( (r_ds.pk = r_csr.fk_data_source) ) ) WHERE (c_lc2r.fk_item = c_enc.pk) ) ,'; '::text ) ) || ';'::text ) ,''::text ) ) || COALESCE ( ( ( (' '::text || _ ('AOE'::text) ) || ': '::text ) || c_enc.assessment_of_encounter ) ,''::text ) ) || COALESCE ( ( (' '::text || array_to_string ( ( SELECT array_agg ( ( ( ( ( ( ( ( (r_csr.code || ' ('::text) || r_ds.name_short ) || ' - '::text ) || r_ds.version ) || ' - '::text ) || r_ds.lang ) || '): '::text ) || r_csr.term ) ) AS array_agg FROM ( (clin.lnk_code2aoe c_lc2a JOIN ref.coding_system_root r_csr ON ( (c_lc2a.fk_generic_code = r_csr.pk_coding_system) ) ) JOIN ref.data_source r_ds ON ( (r_ds.pk = r_csr.fk_data_source) ) ) WHERE (c_lc2a.fk_item = c_enc.pk) ) ,'; '::text ) ) || ';'::text ) ,''::text ) ) AS narrative , c_enc.pk AS pk_encounter , NULL::integer AS pk_episode , NULL::integer AS pk_health_issue , c_enc.pk AS src_pk , 'clin.encounter'::text AS src_table , c_enc.row_version , NULL::text AS health_issue , NULL::text AS issue_laterality , NULL::boolean AS issue_active , NULL::boolean AS issue_clinically_relevant , NULL::boolean AS issue_confidential , NULL::text AS episode , NULL::boolean AS episode_open , c_enc.started AS encounter_started , c_enc.last_affirmed AS encounter_last_affirmed , c_ety.description AS encounter_type , _ (c_ety.description) AS encounter_l10n_type FROM ( ( (clin.encounter c_enc JOIN clin.encounter_type c_ety ON ( (c_enc.fk_type = c_ety.pk) ) ) LEFT JOIN dem.org_unit d_ou ON ( (c_enc.fk_location = d_ou.pk) ) ) LEFT JOIN dem.org d_o ON ( (d_ou.fk_org = d_o.pk) ) );
F-Key | Name | Type | Description |
---|---|---|---|
pk_patient | integer | ||
description | text | ||
summary | text | ||
episode_open | boolean | ||
health_issue | text | ||
issue_active | boolean | ||
issue_clinically_relevant | boolean | ||
started_first | timestamp with time zone | ||
started_last | timestamp with time zone | ||
last_affirmed | timestamp with time zone | ||
pk_episode | integer | ||
pk_encounter | integer | ||
pk_health_issue | integer | ||
episode_modified_when | timestamp with time zone | ||
episode_modified_by | name | ||
diagnostic_certainty_classification | text | ||
diagnostic_certainty_classification_issue | text | ||
pk_generic_codes | integer[] | ||
xmin_episode | xid |
SELECT cenc.fk_patient AS pk_patient , cep.description , cep.summary , cep.is_open AS episode_open , NULL::text AS health_issue , NULL::boolean AS issue_active , NULL::boolean AS issue_clinically_relevant , ( SELECT min (cle.started) AS min FROM clin.encounter cle WHERE (cle.pk = cep.fk_encounter) LIMIT 1 ) AS started_first , ( SELECT max (cle.started) AS max FROM clin.encounter cle WHERE (cle.pk = cep.fk_encounter) LIMIT 1 ) AS started_last , ( SELECT max (cle.last_affirmed) AS max FROM clin.encounter cle WHERE (cle.pk = cep.fk_encounter) LIMIT 1 ) AS last_affirmed , cep.pk AS pk_episode , cep.fk_encounter AS pk_encounter , NULL::integer AS pk_health_issue , cep.modified_when AS episode_modified_when , cep.modified_by AS episode_modified_by , cep.diagnostic_certainty_classification , NULL::text AS diagnostic_certainty_classification_issue , COALESCE ( ( SELECT array_agg (c_lc2e.fk_generic_code) AS array_agg FROM clin.lnk_code2episode c_lc2e WHERE (c_lc2e.fk_item = cep.pk) ) , ARRAY[]::integer[] ) AS pk_generic_codes , cep.xmin AS xmin_episode FROM (clin.episode cep JOIN clin.encounter cenc ON ( (cep.fk_encounter = cenc.pk) ) ) WHERE (cep.fk_health_issue IS NULL) UNION ALL SELECT cenc.fk_patient AS pk_patient , cep.description , cep.summary , cep.is_open AS episode_open , chi.description AS health_issue , chi.is_active AS issue_active , chi.clinically_relevant AS issue_clinically_relevant , ( SELECT min (cle.started) AS min FROM clin.encounter cle WHERE (cle.pk = cep.fk_encounter) LIMIT 1 ) AS started_first , ( SELECT max (cle.started) AS max FROM clin.encounter cle WHERE (cle.pk = cep.fk_encounter) LIMIT 1 ) AS started_last , ( SELECT max (cle.last_affirmed) AS max FROM clin.encounter cle WHERE (cle.pk = cep.fk_encounter) LIMIT 1 ) AS last_affirmed , cep.pk AS pk_episode , cep.fk_encounter AS pk_encounter , cep.fk_health_issue AS pk_health_issue , cep.modified_when AS episode_modified_when , cep.modified_by AS episode_modified_by , cep.diagnostic_certainty_classification , chi.diagnostic_certainty_classification AS diagnostic_certainty_classification_issue , COALESCE ( ( SELECT array_agg (c_lc2e.fk_generic_code) AS array_agg FROM clin.lnk_code2episode c_lc2e WHERE (c_lc2e.fk_item = cep.pk) ) , ARRAY[]::integer[] ) AS pk_generic_codes , cep.xmin AS xmin_episode FROM ( (clin.episode cep JOIN clin.encounter cenc ON ( (cep.fk_encounter = cenc.pk) ) ) JOIN clin.health_issue chi ON ( (cep.fk_health_issue = chi.pk) ) );
F-Key | Name | Type | Description |
---|---|---|---|
pk_patient | integer | ||
modified_when | timestamp with time zone | ||
clin_when | timestamp with time zone | ||
modified_by | text | ||
soap_cat | text | ||
narrative | text | ||
pk_encounter | integer | ||
pk_episode | integer | ||
pk_health_issue | integer | ||
src_pk | integer | ||
src_table | text | ||
row_version | integer | ||
health_issue | text | ||
issue_laterality | character varying(2) | ||
issue_active | boolean | ||
issue_clinically_relevant | boolean | ||
issue_confidential | boolean | ||
episode | text | ||
episode_open | boolean | ||
encounter_started | timestamp with time zone | ||
encounter_last_affirmed | timestamp with time zone | ||
encounter_type | text | ||
encounter_l10n_type | text |
SELECT c_enc.fk_patient AS pk_patient , c_epi.modified_when , c_enc.started AS clin_when , COALESCE ( ( SELECT staff.short_alias FROM dem.staff WHERE (staff.db_user = c_epi.modified_by) ) , ( ('<'::text || (c_epi.modified_by)::text ) || '>'::text ) ) AS modified_by , 'a'::text AS soap_cat , ( ( ( ( ( ( ( (_ ('Episode'::text) || ' ('::text) || CASE WHEN c_epi.is_open THEN _ ('open'::text) ELSE _ ('closed'::text) END ) || COALESCE ( ( ', '::text || c_epi.diagnostic_certainty_classification ) ,''::text ) ) || '): '::text ) || c_epi.description ) || ';'::text ) || COALESCE ( ( ( ( (' '::text || _ ('Synopsis'::text) ) || ': '::text ) || c_epi.summary ) || ';'::text ) ,''::text ) ) || COALESCE ( ( (' '::text || array_to_string ( ( SELECT array_agg ( ( ( ( ( ( ( ( (r_csr.code || ' ('::text) || r_ds.name_short ) || ' - '::text ) || r_ds.version ) || ' - '::text ) || r_ds.lang ) || '): '::text ) || r_csr.term ) ) AS array_agg FROM ( (clin.lnk_code2episode c_lc2e JOIN ref.coding_system_root r_csr ON ( (c_lc2e.fk_generic_code = r_csr.pk_coding_system) ) ) JOIN ref.data_source r_ds ON ( (r_ds.pk = r_csr.fk_data_source) ) ) WHERE (c_lc2e.fk_item = c_epi.pk) ) ,'; '::text ) ) || ';'::text ) ,''::text ) ) AS narrative , c_epi.fk_encounter AS pk_encounter , c_epi.pk AS pk_episode , c_epi.fk_health_issue AS pk_health_issue , c_epi.pk AS src_pk , 'clin.episode'::text AS src_table , c_epi.row_version , c_hi.description AS health_issue , c_hi.laterality AS issue_laterality , c_hi.is_active AS issue_active , c_hi.clinically_relevant AS issue_clinically_relevant , c_hi.is_confidential AS issue_confidential , c_epi.description AS episode , c_epi.is_open AS episode_open , c_enc.started AS encounter_started , c_enc.last_affirmed AS encounter_last_affirmed , c_ety.description AS encounter_type , _ (c_ety.description) AS encounter_l10n_type FROM ( ( (clin.episode c_epi JOIN clin.encounter c_enc ON ( (c_epi.fk_encounter = c_enc.pk) ) ) JOIN clin.encounter_type c_ety ON ( (c_enc.fk_type = c_ety.pk) ) ) LEFT JOIN clin.health_issue c_hi ON ( (c_epi.fk_health_issue = c_hi.pk) ) );
F-Key | Name | Type | Description |
---|---|---|---|
modified_when | timestamp with time zone | ||
modified_by | name | ||
clin_when | timestamp with time zone | ||
is_modified | boolean | ||
pk_patient | integer | ||
pk_item | integer | ||
pk_encounter | integer | ||
pk_episode | integer | ||
pk_health_issue | integer | ||
soap_cat | text | ||
narrative | text | ||
src_table | text |
SELECT cri.modified_when , cri.modified_by , cri.clin_when , CASE cri.row_version WHEN 0 THEN false ELSE true END AS is_modified , cenc.fk_patient AS pk_patient , cri.pk_item , cri.fk_encounter AS pk_encounter , cri.fk_episode AS pk_episode , cepi.fk_health_issue AS pk_health_issue , cri.soap_cat , cri.narrative , ( ( (pgn.nspname)::text || '.'::text ) || (pgc.relname)::text ) AS src_table FROM clin.clin_root_item cri , clin.encounter cenc , clin.episode cepi , (pg_class pgc LEFT JOIN pg_namespace pgn ON ( (pgc.relnamespace = pgn.oid) ) ) WHERE ( (cri.fk_encounter = cenc.pk) AND (cri.fk_episode = cepi.pk) AND (cri.tableoid = pgc.oid) );
Lists *latest* vaccinations with total count per indication.
F-Key | Name | Type | Description |
---|---|---|---|
pk_patient | integer | ||
pk_vaccination | integer | ||
date_given | timestamp with time zone | ||
vaccine | text | ||
indication | text | ||
l10n_indication | text | ||
site | text | ||
batch_no | text | ||
reaction | text | ||
comment | text | ||
soap_cat | text | ||
modified_when | timestamp with time zone | ||
modified_by | name | ||
row_version | integer | ||
pk_vaccine | integer | ||
pk_indication | integer | ||
pk_provider | integer | ||
pk_encounter | integer | ||
pk_episode | integer | ||
xmin_vaccination | xid | ||
no_of_shots | bigint |
SELECT c_vpv4i.pk_patient , c_vpv4i.pk_vaccination , c_vpv4i.date_given , c_vpv4i.vaccine , c_vpv4i.indication , c_vpv4i.l10n_indication , c_vpv4i.site , c_vpv4i.batch_no , c_vpv4i.reaction , c_vpv4i.comment , c_vpv4i.soap_cat , c_vpv4i.modified_when , c_vpv4i.modified_by , c_vpv4i.row_version , c_vpv4i.pk_vaccine , c_vpv4i.pk_indication , c_vpv4i.pk_provider , c_vpv4i.pk_encounter , c_vpv4i.pk_episode , c_vpv4i.xmin_vaccination , shots_per_ind.no_of_shots FROM (clin.v_pat_vaccs4indication c_vpv4i JOIN ( SELECT count (1) AS no_of_shots , v_pat_vaccs4indication.pk_patient , v_pat_vaccs4indication.pk_indication FROM clin.v_pat_vaccs4indication GROUP BY v_pat_vaccs4indication.pk_patient , v_pat_vaccs4indication.pk_indication ) shots_per_ind ON ( ( (c_vpv4i.pk_patient = shots_per_ind.pk_patient) AND (c_vpv4i.pk_indication = shots_per_ind.pk_indication) ) ) ) WHERE (c_vpv4i.date_given = ( SELECT max (c_vpv4i_2.date_given) AS max FROM clin.v_pat_vaccs4indication c_vpv4i_2 WHERE ( (c_vpv4i.pk_patient = c_vpv4i_2.pk_patient) AND (c_vpv4i.pk_indication = c_vpv4i_2.pk_indication) ) ) );
F-Key | Name | Type | Description |
---|---|---|---|
pk_patient | integer | ||
modified_when | timestamp with time zone | ||
clin_when | timestamp with time zone | ||
modified_by | text | ||
soap_cat | text | ||
narrative | text | ||
pk_encounter | integer | ||
pk_episode | integer | ||
pk_health_issue | integer | ||
src_pk | integer | ||
src_table | text | ||
row_version | integer | ||
health_issue | text | ||
issue_laterality | character varying(2) | ||
issue_active | boolean | ||
issue_clinically_relevant | boolean | ||
issue_confidential | boolean | ||
episode | text | ||
episode_open | boolean | ||
encounter_started | timestamp with time zone | ||
encounter_last_affirmed | timestamp with time zone | ||
encounter_type | text | ||
encounter_l10n_type | text |
SELECT c_enc.fk_patient AS pk_patient , c_n.modified_when , CASE WHEN (c_n.soap_cat = ANY (ARRAY['s'::text ,'o'::text ,'u'::text] ) ) THEN c_enc.started WHEN (c_n.soap_cat IS NULL) THEN c_enc.last_affirmed WHEN (c_n.soap_cat = ANY (ARRAY['a'::text ,'p'::text] ) ) THEN c_enc.last_affirmed ELSE NULL::timestamp with time zone END AS clin_when , COALESCE ( ( SELECT staff.short_alias FROM dem.staff WHERE (staff.db_user = c_n.modified_by) ) , ( ('<'::text || (c_n.modified_by)::text ) || '>'::text ) ) AS modified_by , c_n.soap_cat , c_n.narrative , c_n.fk_encounter AS pk_encounter , c_n.fk_episode AS pk_episode , c_epi.fk_health_issue AS pk_health_issue , c_n.pk AS src_pk , 'clin.clin_narrative'::text AS src_table , c_n.row_version , c_hi.description AS health_issue , c_hi.laterality AS issue_laterality , c_hi.is_active AS issue_active , c_hi.clinically_relevant AS issue_clinically_relevant , c_hi.is_confidential AS issue_confidential , c_epi.description AS episode , c_epi.is_open AS episode_open , c_enc.started AS encounter_started , c_enc.last_affirmed AS encounter_last_affirmed , c_ety.description AS encounter_type , _ (c_ety.description) AS encounter_l10n_type FROM ( ( ( (clin.clin_narrative c_n JOIN clin.encounter c_enc ON ( (c_n.fk_encounter = c_enc.pk) ) ) JOIN clin.encounter_type c_ety ON ( (c_enc.fk_type = c_ety.pk) ) ) JOIN clin.episode c_epi ON ( (c_n.fk_episode = c_epi.pk) ) ) LEFT JOIN clin.health_issue c_hi ON ( (c_epi.fk_health_issue = c_hi.pk) ) );
Lists vaccinations for patients
F-Key | Name | Type | Description |
---|---|---|---|
pk_patient | integer | ||
pk_vaccination | integer | ||
date_given | timestamp with time zone | ||
interval_since_given | interval | ||
vaccine | text | ||
indications | text[] | ||
l10n_indications | text[] | ||
site | text | ||
batch_no | text | ||
reaction | text | ||
comment | text | ||
soap_cat | text | ||
modified_when | timestamp with time zone | ||
modified_by | name | ||
row_version | integer | ||
pk_indications | integer[] | ||
pk_vaccine | integer | ||
pk_provider | integer | ||
pk_encounter | integer | ||
pk_episode | integer | ||
xmin_vaccination | xid |
SELECT cenc.fk_patient AS pk_patient , clv.pk AS pk_vaccination , clv.clin_when AS date_given , (now () - clv.clin_when ) AS interval_since_given , rbd.description AS vaccine , ( SELECT array_agg (cvi.description) AS array_agg FROM (clin.lnk_vaccine2inds clvi JOIN clin.vacc_indication cvi ON ( (clvi.fk_indication = cvi.id) ) ) WHERE (clvi.fk_vaccine = clv.fk_vaccine) ) AS indications , ( SELECT array_agg (_ (cvi.description) ) AS array_agg FROM (clin.lnk_vaccine2inds clvi JOIN clin.vacc_indication cvi ON ( (clvi.fk_indication = cvi.id) ) ) WHERE (clvi.fk_vaccine = clv.fk_vaccine) ) AS l10n_indications , clv.site , clv.batch_no , clv.reaction , clv.narrative AS comment , clv.soap_cat , clv.modified_when , clv.modified_by , clv.row_version , ( SELECT array_agg (clvi.fk_indication) AS array_agg FROM (clin.lnk_vaccine2inds clvi JOIN clin.vacc_indication cvi ON ( (clvi.fk_indication = cvi.id) ) ) WHERE (clvi.fk_vaccine = clv.pk) ) AS pk_indications , clv.fk_vaccine AS pk_vaccine , clv.fk_provider AS pk_provider , clv.fk_encounter AS pk_encounter , clv.fk_episode AS pk_episode , clv.xmin AS xmin_vaccination FROM ( ( (clin.vaccination clv JOIN clin.encounter cenc ON ( (cenc.pk = clv.fk_encounter) ) ) JOIN clin.vaccine ON ( (vaccine.pk = clv.fk_vaccine) ) ) JOIN ref.branded_drug rbd ON ( (vaccine.fk_brand = rbd.pk) ) );
Lists vaccinations per indication for patients
F-Key | Name | Type | Description |
---|---|---|---|
pk_patient | integer | ||
pk_vaccination | integer | ||
date_given | timestamp with time zone | ||
vaccine | text | ||
indication | text | ||
l10n_indication | text | ||
site | text | ||
batch_no | text | ||
reaction | text | ||
comment | text | ||
soap_cat | text | ||
modified_when | timestamp with time zone | ||
modified_by | name | ||
row_version | integer | ||
pk_vaccine | integer | ||
pk_indication | integer | ||
pk_provider | integer | ||
pk_encounter | integer | ||
pk_episode | integer | ||
xmin_vaccination | xid |
SELECT c_enc.fk_patient AS pk_patient , c_shot.pk AS pk_vaccination , c_shot.clin_when AS date_given , c_vlv2i.vaccine , c_vlv2i.indication , c_vlv2i.l10n_indication , c_shot.site , c_shot.batch_no , c_shot.reaction , c_shot.narrative AS comment , c_shot.soap_cat , c_shot.modified_when , c_shot.modified_by , c_shot.row_version , c_shot.fk_vaccine AS pk_vaccine , c_vlv2i.pk_indication , c_shot.fk_provider AS pk_provider , c_shot.fk_encounter AS pk_encounter , c_shot.fk_episode AS pk_episode , c_shot.xmin AS xmin_vaccination FROM ( (clin.vaccination c_shot JOIN clin.encounter c_enc ON ( (c_enc.pk = c_shot.fk_encounter) ) ) JOIN clin.v_lnk_vaccine2inds c_vlv2i ON ( (c_vlv2i.pk_vaccine = c_shot.fk_vaccine) ) );
F-Key | Name | Type | Description |
---|---|---|---|
pk_patient | integer | ||
problem | text | ||
summary | text | ||
type | text | ||
l10n_type | text | ||
problem_active | boolean | ||
clinically_relevant | boolean | ||
pk_episode | integer | ||
pk_health_issue | integer | ||
diagnostic_certainty_classification | text | ||
pk_encounter | integer | ||
modified_when | timestamp with time zone |
SELECT ( SELECT encounter.fk_patient FROM clin.encounter WHERE (encounter.pk = cep.fk_encounter) ) AS pk_patient , cep.description AS problem , cep.summary , 'episode'::text AS type , _ ('episode'::text) AS l10n_type , false AS problem_active , false AS clinically_relevant , cep.pk AS pk_episode , cep.fk_health_issue AS pk_health_issue , cep.diagnostic_certainty_classification , cep.fk_encounter AS pk_encounter , cep.modified_when FROM clin.episode cep WHERE (cep.is_open IS FALSE) UNION SELECT ( SELECT encounter.fk_patient FROM clin.encounter WHERE (encounter.pk = chi.fk_encounter) ) AS pk_patient , chi.description AS problem , chi.summary , 'issue'::text AS type , _ ('health issue'::text) AS l10n_type , chi.is_active AS problem_active , false AS clinically_relevant , NULL::integer AS pk_episode , chi.pk AS pk_health_issue , chi.diagnostic_certainty_classification , chi.fk_encounter AS pk_encounter , chi.modified_when FROM clin.health_issue chi WHERE (chi.clinically_relevant IS FALSE);
F-Key | Name | Type | Description |
---|---|---|---|
pk_patient | integer | ||
problem | text | ||
summary | text | ||
type | text | ||
l10n_type | text | ||
problem_active | boolean | ||
clinically_relevant | boolean | ||
pk_episode | integer | ||
pk_health_issue | integer | ||
diagnostic_certainty_classification | text | ||
pk_encounter | integer | ||
modified_when | timestamp with time zone |
SELECT ( SELECT encounter.fk_patient FROM clin.encounter WHERE (encounter.pk = cep.fk_encounter) ) AS pk_patient , cep.description AS problem , cep.summary , 'episode'::text AS type , _ ('episode'::text) AS l10n_type , true AS problem_active , true AS clinically_relevant , cep.pk AS pk_episode , cep.fk_health_issue AS pk_health_issue , cep.diagnostic_certainty_classification , cep.fk_encounter AS pk_encounter , cep.modified_when FROM clin.episode cep WHERE (cep.is_open IS TRUE) UNION SELECT ( SELECT encounter.fk_patient FROM clin.encounter WHERE (encounter.pk = chi.fk_encounter) ) AS pk_patient , chi.description AS problem , chi.summary , 'issue'::text AS type , _ ('health issue'::text) AS l10n_type , chi.is_active AS problem_active , true AS clinically_relevant , NULL::integer AS pk_episode , chi.pk AS pk_health_issue , chi.diagnostic_certainty_classification , chi.fk_encounter AS pk_encounter , chi.modified_when FROM clin.health_issue chi WHERE (chi.clinically_relevant IS TRUE);
F-Key | Name | Type | Description |
---|---|---|---|
pk_procedure | integer | ||
pk_patient | integer | ||
soap_cat | text | ||
clin_when | timestamp with time zone | ||
clin_end | timestamp with time zone | ||
is_ongoing | boolean | ||
performed_procedure | text | ||
unit | text | ||
organization | text | ||
episode | text | ||
health_issue | text | ||
modified_when | timestamp with time zone | ||
modified_by | text | ||
row_version | integer | ||
pk_encounter | integer | ||
pk_episode | integer | ||
pk_hospital_stay | integer | ||
pk_health_issue | integer | ||
pk_org | integer | ||
pk_org_unit | integer | ||
pk_generic_codes | integer[] | ||
xmin_procedure | xid |
SELECT v_procedures_at_hospital.pk_procedure , v_procedures_at_hospital.pk_patient , v_procedures_at_hospital.soap_cat , v_procedures_at_hospital.clin_when , v_procedures_at_hospital.clin_end , v_procedures_at_hospital.is_ongoing , v_procedures_at_hospital.performed_procedure , v_procedures_at_hospital.unit , v_procedures_at_hospital.organization , v_procedures_at_hospital.episode , v_procedures_at_hospital.health_issue , v_procedures_at_hospital.modified_when , v_procedures_at_hospital.modified_by , v_procedures_at_hospital.row_version , v_procedures_at_hospital.pk_encounter , v_procedures_at_hospital.pk_episode , v_procedures_at_hospital.pk_hospital_stay , v_procedures_at_hospital.pk_health_issue , v_procedures_at_hospital.pk_org , v_procedures_at_hospital.pk_org_unit , v_procedures_at_hospital.pk_generic_codes , v_procedures_at_hospital.xmin_procedure FROM clin.v_procedures_at_hospital UNION ALL SELECT v_procedures_not_at_hospital.pk_procedure , v_procedures_not_at_hospital.pk_patient , v_procedures_not_at_hospital.soap_cat , v_procedures_not_at_hospital.clin_when , v_procedures_not_at_hospital.clin_end , v_procedures_not_at_hospital.is_ongoing , v_procedures_not_at_hospital.performed_procedure , v_procedures_not_at_hospital.unit , v_procedures_not_at_hospital.organization , v_procedures_not_at_hospital.episode , v_procedures_not_at_hospital.health_issue , v_procedures_not_at_hospital.modified_when , v_procedures_not_at_hospital.modified_by , v_procedures_not_at_hospital.row_version , v_procedures_not_at_hospital.pk_encounter , v_procedures_not_at_hospital.pk_episode , v_procedures_not_at_hospital.pk_hospital_stay , v_procedures_not_at_hospital.pk_health_issue , v_procedures_not_at_hospital.pk_org , v_procedures_not_at_hospital.pk_org_unit , v_procedures_not_at_hospital.pk_generic_codes , v_procedures_not_at_hospital.xmin_procedure FROM clin.v_procedures_not_at_hospital;
F-Key | Name | Type | Description |
---|---|---|---|
pk_procedure | integer | ||
pk_patient | integer | ||
soap_cat | text | ||
clin_when | timestamp with time zone | ||
clin_end | timestamp with time zone | ||
is_ongoing | boolean | ||
performed_procedure | text | ||
unit | text | ||
organization | text | ||
episode | text | ||
health_issue | text | ||
modified_when | timestamp with time zone | ||
modified_by | text | ||
row_version | integer | ||
pk_encounter | integer | ||
pk_episode | integer | ||
pk_hospital_stay | integer | ||
pk_health_issue | integer | ||
pk_org | integer | ||
pk_org_unit | integer | ||
pk_generic_codes | integer[] | ||
xmin_procedure | xid |
SELECT c_pr.pk AS pk_procedure , c_enc.fk_patient AS pk_patient , c_pr.soap_cat , c_pr.clin_when , c_pr.clin_end , c_pr.is_ongoing , c_pr.narrative AS performed_procedure , c_vhs.ward AS unit , c_vhs.hospital AS organization , c_ep.description AS episode , c_hi.description AS health_issue , c_pr.modified_when , COALESCE ( ( SELECT staff.short_alias FROM dem.staff WHERE (staff.db_user = c_pr.modified_by) ) , ( ('<'::text || (c_pr.modified_by)::text ) || '>'::text ) ) AS modified_by , c_pr.row_version , c_pr.fk_encounter AS pk_encounter , c_pr.fk_episode AS pk_episode , c_pr.fk_hospital_stay AS pk_hospital_stay , c_ep.fk_health_issue AS pk_health_issue , c_vhs.pk_org , c_vhs.pk_org_unit , COALESCE ( ( SELECT array_agg (c_lc2p.fk_generic_code) AS array_agg FROM clin.lnk_code2procedure c_lc2p WHERE (c_lc2p.fk_item = c_pr.pk) ) , ARRAY[]::integer[] ) AS pk_generic_codes , c_pr.xmin AS xmin_procedure FROM ( ( ( (clin.procedure c_pr JOIN clin.encounter c_enc ON ( (c_pr.fk_encounter = c_enc.pk) ) ) JOIN clin.episode c_ep ON ( (c_pr.fk_episode = c_ep.pk) ) ) LEFT JOIN clin.health_issue c_hi ON ( (c_ep.fk_health_issue = c_hi.pk) ) ) LEFT JOIN clin.v_hospital_stays c_vhs ON ( (c_pr.fk_hospital_stay = c_vhs.pk_hospital_stay) ) ) WHERE (c_pr.fk_hospital_stay IS NOT NULL);
F-Key | Name | Type | Description |
---|---|---|---|
pk_patient | integer | ||
modified_when | timestamp with time zone | ||
clin_when | timestamp with time zone | ||
modified_by | text | ||
soap_cat | text | ||
narrative | text | ||
pk_encounter | integer | ||
pk_episode | integer | ||
pk_health_issue | integer | ||
src_pk | integer | ||
src_table | text | ||
row_version | integer | ||
health_issue | text | ||
issue_laterality | character varying(2) | ||
issue_active | boolean | ||
issue_clinically_relevant | boolean | ||
issue_confidential | boolean | ||
episode | text | ||
episode_open | boolean | ||
encounter_started | timestamp with time zone | ||
encounter_last_affirmed | timestamp with time zone | ||
encounter_type | text | ||
encounter_l10n_type | text |
SELECT c_enc.fk_patient AS pk_patient , c_pr.modified_when , c_pr.clin_when , COALESCE ( ( SELECT staff.short_alias FROM dem.staff WHERE (staff.db_user = c_pr.modified_by) ) , ( ('<'::text || (c_pr.modified_by)::text ) || '>'::text ) ) AS modified_by , c_pr.soap_cat , ( ( ( ( ( ( ( ( ( (_ ('Procedure'::text) || ' "'::text ) || c_pr.narrative ) || '"'::text ) || ' ('::text) || d_ou.description ) || ' @ '::text ) || d_o.description ) || COALESCE ( ( ( ( ', '::text || _ ('until'::text) ) || ' '::text ) || to_char (c_pr.clin_end ,'YYYY Mon DD'::text ) ) , CASE WHEN (c_pr.is_ongoing IS TRUE) THEN ( ', '::text || _ ('ongoing'::text) ) ELSE ''::text END ) ) || ')'::text ) || COALESCE ( ( (' '::text || array_to_string ( ( SELECT array_agg ( ( ( ( ( ( ( ( (r_csr.code || ' ('::text) || r_ds.name_short ) || ' - '::text ) || r_ds.version ) || ' - '::text ) || r_ds.lang ) || '): '::text ) || r_csr.term ) ) AS array_agg FROM ( (clin.lnk_code2procedure c_lc2p JOIN ref.coding_system_root r_csr ON ( (c_lc2p.fk_generic_code = r_csr.pk_coding_system) ) ) JOIN ref.data_source r_ds ON ( (r_ds.pk = r_csr.fk_data_source) ) ) WHERE (c_lc2p.fk_item = c_pr.pk) ) ,'; '::text ) ) || ';'::text ) ,''::text ) ) AS narrative , c_pr.fk_encounter AS pk_encounter , c_pr.fk_episode AS pk_episode , c_epi.fk_health_issue AS pk_health_issue , c_pr.pk AS src_pk , 'clin.procedure'::text AS src_table , c_pr.row_version , c_hi.description AS health_issue , c_hi.laterality AS issue_laterality , c_hi.is_active AS issue_active , c_hi.clinically_relevant AS issue_clinically_relevant , c_hi.is_confidential AS issue_confidential , c_epi.description AS episode , c_epi.is_open AS episode_open , c_enc.started AS encounter_started , c_enc.last_affirmed AS encounter_last_affirmed , c_ety.description AS encounter_type , _ (c_ety.description) AS encounter_l10n_type FROM ( ( ( ( ( ( (clin.procedure c_pr JOIN clin.encounter c_enc ON ( (c_pr.fk_encounter = c_enc.pk) ) ) JOIN clin.encounter_type c_ety ON ( (c_enc.fk_type = c_ety.pk) ) ) JOIN clin.episode c_epi ON ( (c_pr.fk_episode = c_epi.pk) ) ) LEFT JOIN clin.health_issue c_hi ON ( (c_epi.fk_health_issue = c_hi.pk) ) ) LEFT JOIN clin.hospital_stay c_hs ON ( (c_pr.fk_hospital_stay = c_hs.pk) ) ) LEFT JOIN dem.org_unit d_ou ON ( (c_hs.fk_org_unit = d_ou.pk) ) ) LEFT JOIN dem.org d_o ON ( (d_ou.fk_org = d_o.pk) ) ) WHERE (c_pr.fk_hospital_stay IS NOT NULL);
F-Key | Name | Type | Description |
---|---|---|---|
pk_patient | integer | ||
modified_when | timestamp with time zone | ||
clin_when | timestamp with time zone | ||
modified_by | text | ||
soap_cat | text | ||
narrative | text | ||
pk_encounter | integer | ||
pk_episode | integer | ||
pk_health_issue | integer | ||
src_pk | integer | ||
src_table | text | ||
row_version | integer | ||
health_issue | text | ||
issue_laterality | character varying(2) | ||
issue_active | boolean | ||
issue_clinically_relevant | boolean | ||
issue_confidential | boolean | ||
episode | text | ||
episode_open | boolean | ||
encounter_started | timestamp with time zone | ||
encounter_last_affirmed | timestamp with time zone | ||
encounter_type | text | ||
encounter_l10n_type | text |
SELECT v_procedures_at_hospital_journal.pk_patient , v_procedures_at_hospital_journal.modified_when , v_procedures_at_hospital_journal.clin_when , v_procedures_at_hospital_journal.modified_by , v_procedures_at_hospital_journal.soap_cat , v_procedures_at_hospital_journal.narrative , v_procedures_at_hospital_journal.pk_encounter , v_procedures_at_hospital_journal.pk_episode , v_procedures_at_hospital_journal.pk_health_issue , v_procedures_at_hospital_journal.src_pk , v_procedures_at_hospital_journal.src_table , v_procedures_at_hospital_journal.row_version , v_procedures_at_hospital_journal.health_issue , v_procedures_at_hospital_journal.issue_laterality , v_procedures_at_hospital_journal.issue_active , v_procedures_at_hospital_journal.issue_clinically_relevant , v_procedures_at_hospital_journal.issue_confidential , v_procedures_at_hospital_journal.episode , v_procedures_at_hospital_journal.episode_open , v_procedures_at_hospital_journal.encounter_started , v_procedures_at_hospital_journal.encounter_last_affirmed , v_procedures_at_hospital_journal.encounter_type , v_procedures_at_hospital_journal.encounter_l10n_type FROM clin.v_procedures_at_hospital_journal UNION ALL SELECT v_procedures_not_at_hospital_journal.pk_patient , v_procedures_not_at_hospital_journal.modified_when , v_procedures_not_at_hospital_journal.clin_when , v_procedures_not_at_hospital_journal.modified_by , v_procedures_not_at_hospital_journal.soap_cat , v_procedures_not_at_hospital_journal.narrative , v_procedures_not_at_hospital_journal.pk_encounter , v_procedures_not_at_hospital_journal.pk_episode , v_procedures_not_at_hospital_journal.pk_health_issue , v_procedures_not_at_hospital_journal.src_pk , v_procedures_not_at_hospital_journal.src_table , v_procedures_not_at_hospital_journal.row_version , v_procedures_not_at_hospital_journal.health_issue , v_procedures_not_at_hospital_journal.issue_laterality , v_procedures_not_at_hospital_journal.issue_active , v_procedures_not_at_hospital_journal.issue_clinically_relevant , v_procedures_not_at_hospital_journal.issue_confidential , v_procedures_not_at_hospital_journal.episode , v_procedures_not_at_hospital_journal.episode_open , v_procedures_not_at_hospital_journal.encounter_started , v_procedures_not_at_hospital_journal.encounter_last_affirmed , v_procedures_not_at_hospital_journal.encounter_type , v_procedures_not_at_hospital_journal.encounter_l10n_type FROM clin.v_procedures_not_at_hospital_journal;
F-Key | Name | Type | Description |
---|---|---|---|
pk_procedure | integer | ||
pk_patient | integer | ||
soap_cat | text | ||
clin_when | timestamp with time zone | ||
clin_end | timestamp with time zone | ||
is_ongoing | boolean | ||
performed_procedure | text | ||
unit | text | ||
organization | text | ||
episode | text | ||
health_issue | text | ||
modified_when | timestamp with time zone | ||
modified_by | text | ||
row_version | integer | ||
pk_encounter | integer | ||
pk_episode | integer | ||
pk_hospital_stay | integer | ||
pk_health_issue | integer | ||
pk_org | integer | ||
pk_org_unit | integer | ||
pk_generic_codes | integer[] | ||
xmin_procedure | xid |
SELECT c_pr.pk AS pk_procedure , c_enc.fk_patient AS pk_patient , c_pr.soap_cat , c_pr.clin_when , c_pr.clin_end , c_pr.is_ongoing , c_pr.narrative AS performed_procedure , d_ou.description AS unit , d_o.description AS organization , c_ep.description AS episode , c_hi.description AS health_issue , c_pr.modified_when , COALESCE ( ( SELECT staff.short_alias FROM dem.staff WHERE (staff.db_user = c_pr.modified_by) ) , ( ('<'::text || (c_pr.modified_by)::text ) || '>'::text ) ) AS modified_by , c_pr.row_version , c_pr.fk_encounter AS pk_encounter , c_pr.fk_episode AS pk_episode , c_pr.fk_hospital_stay AS pk_hospital_stay , c_ep.fk_health_issue AS pk_health_issue , d_o.pk AS pk_org , d_ou.pk AS pk_org_unit , COALESCE ( ( SELECT array_agg (c_lc2p.fk_generic_code) AS array_agg FROM clin.lnk_code2procedure c_lc2p WHERE (c_lc2p.fk_item = c_pr.pk) ) , ARRAY[]::integer[] ) AS pk_generic_codes , c_pr.xmin AS xmin_procedure FROM ( ( ( ( (clin.procedure c_pr JOIN clin.encounter c_enc ON ( (c_pr.fk_encounter = c_enc.pk) ) ) JOIN clin.episode c_ep ON ( (c_pr.fk_episode = c_ep.pk) ) ) LEFT JOIN clin.health_issue c_hi ON ( (c_ep.fk_health_issue = c_hi.pk) ) ) LEFT JOIN dem.org_unit d_ou ON ( (d_ou.pk = c_pr.fk_org_unit) ) ) LEFT JOIN dem.org d_o ON ( (d_o.pk = d_ou.fk_org) ) ) WHERE (c_pr.fk_hospital_stay IS NULL);
F-Key | Name | Type | Description |
---|---|---|---|
pk_patient | integer | ||
modified_when | timestamp with time zone | ||
clin_when | timestamp with time zone | ||
modified_by | text | ||
soap_cat | text | ||
narrative | text | ||
pk_encounter | integer | ||
pk_episode | integer | ||
pk_health_issue | integer | ||
src_pk | integer | ||
src_table | text | ||
row_version | integer | ||
health_issue | text | ||
issue_laterality | character varying(2) | ||
issue_active | boolean | ||
issue_clinically_relevant | boolean | ||
issue_confidential | boolean | ||
episode | text | ||
episode_open | boolean | ||
encounter_started | timestamp with time zone | ||
encounter_last_affirmed | timestamp with time zone | ||
encounter_type | text | ||
encounter_l10n_type | text |
SELECT c_enc.fk_patient AS pk_patient , c_pr.modified_when , c_pr.clin_when , COALESCE ( ( SELECT staff.short_alias FROM dem.staff WHERE (staff.db_user = c_pr.modified_by) ) , ( ('<'::text || (c_pr.modified_by)::text ) || '>'::text ) ) AS modified_by , c_pr.soap_cat , ( ( ( ( ( ( ( ( ( (_ ('Procedure'::text) || ' "'::text ) || c_pr.narrative ) || '"'::text ) || ' ('::text) || d_ou.description ) || ' @ '::text ) || d_o.description ) || COALESCE ( ( ( ( ', '::text || _ ('until'::text) ) || ' '::text ) || to_char (c_pr.clin_end ,'YYYY Mon DD'::text ) ) , CASE WHEN (c_pr.is_ongoing IS TRUE) THEN ( ', '::text || _ ('ongoing'::text) ) ELSE ''::text END ) ) || ')'::text ) || COALESCE ( ( (' '::text || array_to_string ( ( SELECT array_agg ( ( ( ( ( ( ( ( (r_csr.code || ' ('::text) || r_ds.name_short ) || ' - '::text ) || r_ds.version ) || ' - '::text ) || r_ds.lang ) || '): '::text ) || r_csr.term ) ) AS array_agg FROM ( (clin.lnk_code2procedure c_lc2p JOIN ref.coding_system_root r_csr ON ( (c_lc2p.fk_generic_code = r_csr.pk_coding_system) ) ) JOIN ref.data_source r_ds ON ( (r_ds.pk = r_csr.fk_data_source) ) ) WHERE (c_lc2p.fk_item = c_pr.pk) ) ,'; '::text ) ) || ';'::text ) ,''::text ) ) AS narrative , c_pr.fk_encounter AS pk_encounter , c_pr.fk_episode AS pk_episode , c_epi.fk_health_issue AS pk_health_issue , c_pr.pk AS src_pk , 'clin.procedure'::text AS src_table , c_pr.row_version , c_hi.description AS health_issue , c_hi.laterality AS issue_laterality , c_hi.is_active AS issue_active , c_hi.clinically_relevant AS issue_clinically_relevant , c_hi.is_confidential AS issue_confidential , c_epi.description AS episode , c_epi.is_open AS episode_open , c_enc.started AS encounter_started , c_enc.last_affirmed AS encounter_last_affirmed , c_ety.description AS encounter_type , _ (c_ety.description) AS encounter_l10n_type FROM ( ( ( ( ( (clin.procedure c_pr JOIN clin.encounter c_enc ON ( (c_pr.fk_encounter = c_enc.pk) ) ) JOIN clin.encounter_type c_ety ON ( (c_enc.fk_type = c_ety.pk) ) ) JOIN clin.episode c_epi ON ( (c_pr.fk_episode = c_epi.pk) ) ) LEFT JOIN clin.health_issue c_hi ON ( (c_epi.fk_health_issue = c_hi.pk) ) ) LEFT JOIN dem.org_unit d_ou ON ( (c_pr.fk_org_unit = d_ou.pk) ) ) LEFT JOIN dem.org d_o ON ( (d_ou.fk_org = d_o.pk) ) ) WHERE (c_pr.fk_hospital_stay IS NULL);
F-Key | Name | Type | Description |
---|---|---|---|
pk_patient | integer | ||
modified_when | timestamp with time zone | ||
clin_when | timestamp with time zone | ||
modified_by | text | ||
soap_cat | text | ||
narrative | text | ||
pk_encounter | integer | ||
pk_episode | integer | ||
pk_health_issue | integer | ||
src_pk | integer | ||
src_table | text | ||
row_version | integer | ||
health_issue | text | ||
issue_laterality | text | ||
issue_active | boolean | ||
issue_clinically_relevant | boolean | ||
issue_confidential | boolean | ||
episode | text | ||
episode_open | boolean | ||
encounter_started | timestamp with time zone | ||
encounter_last_affirmed | timestamp with time zone | ||
encounter_type | text | ||
encounter_l10n_type | text |
SELECT d_mi.fk_patient AS pk_patient , d_mi.modified_when , d_mi.due_date AS clin_when , COALESCE ( ( SELECT staff.short_alias FROM dem.staff WHERE (staff.db_user = d_mi.modified_by) ) , ( ('<'::text || (d_mi.modified_by)::text ) || '>'::text ) ) AS modified_by , NULL::text AS soap_cat , ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( (_ ('Due today'::text) || ' ('::text) || d_vit.l10n_category ) || ' - '::text ) || d_vit.l10n_type ) || ')'::text ) || ' '::text ) || COALESCE ( ( (' '::text || d_mi.comment) || ' '::text ) ,''::text ) ) || COALESCE ( ( ( ( (' '::text || _ ('Will expire:'::text) ) || ' '::text ) || to_char ( (d_mi.expiry_date)::timestamp with time zone ,'YYYY-MM-DD'::text ) ) || ' '::text ) ,''::text ) ) || ' '::text ) || _ ('Importance:'::text) ) || ' '::text ) || d_mi.importance ) || ' '::text ) || COALESCE ( ( ( ( (' '::text || _ ('Context:'::text) ) || ' '::text ) || array_to_string (d_mi.ufk_context ,','::text ,'?'::text ) ) || ' '::text ) ,''::text ) ) || COALESCE ( ( ( ( (' '::text || _ ('Data:'::text) ) || ' '::text ) || d_mi.data ) || ' '::text ) ,''::text ) ) || COALESCE ( ( ( (' '::text || _ ('Provider:'::text) ) || ' '::text ) || d_st.short_alias ) ,''::text ) ) AS narrative , ( SELECT c_e.pk FROM clin.encounter c_e WHERE (c_e.fk_patient = d_mi.fk_patient) ORDER BY c_e.started DESC LIMIT 1 ) AS pk_encounter , NULL::integer AS pk_episode , NULL::integer AS pk_health_issue , d_mi.pk AS src_pk , 'dem.message_inbox'::text AS src_table , d_mi.row_version , NULL::text AS health_issue , NULL::text AS issue_laterality , NULL::boolean AS issue_active , NULL::boolean AS issue_clinically_relevant , NULL::boolean AS issue_confidential , NULL::text AS episode , NULL::boolean AS episode_open , ( SELECT c_e.started FROM clin.encounter c_e WHERE (c_e.fk_patient = d_mi.fk_patient) ORDER BY c_e.started DESC LIMIT 1 ) AS encounter_started , ( SELECT c_e.last_affirmed FROM clin.encounter c_e WHERE (c_e.fk_patient = d_mi.fk_patient) ORDER BY c_e.started DESC LIMIT 1 ) AS encounter_last_affirmed , NULL::text AS encounter_type , NULL::text AS encounter_l10n_type FROM ( (dem.message_inbox d_mi JOIN dem.v_inbox_item_type d_vit ON ( (d_mi.fk_inbox_item_type = d_vit.pk_type) ) ) LEFT JOIN dem.staff d_st ON ( (d_mi.fk_staff = d_st.pk) ) ) WHERE ( (d_mi.fk_patient IS NOT NULL) AND (d_vit.category = 'clinical'::text) AND (d_mi.due_date IS NOT NULL) ) UNION SELECT d_mi.fk_patient AS pk_patient , d_mi.modified_when , d_mi.expiry_date AS clin_when , COALESCE ( ( SELECT staff.short_alias FROM dem.staff WHERE (staff.db_user = d_mi.modified_by) ) , ( ('<'::text || (d_mi.modified_by)::text ) || '>'::text ) ) AS modified_by , NULL::text AS soap_cat , ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( (_ ('Epires today'::text) || ' ('::text) || d_vit.l10n_category ) || ' - '::text ) || d_vit.l10n_type ) || ')'::text ) || ' '::text ) || COALESCE ( ( (' '::text || d_mi.comment) || ' '::text ) ,''::text ) ) || COALESCE ( ( ( ( (' '::text || _ ('Was due:'::text) ) || ' '::text ) || to_char ( (d_mi.due_date)::timestamp with time zone ,'YYYY-MM-DD'::text ) ) || ' '::text ) ,''::text ) ) || ' '::text ) || _ ('Importance:'::text) ) || ' '::text ) || d_mi.importance ) || ' '::text ) || COALESCE ( ( ( ( (' '::text || _ ('Context:'::text) ) || ' '::text ) || array_to_string (d_mi.ufk_context ,','::text ,'?'::text ) ) || ' '::text ) ,''::text ) ) || COALESCE ( ( ( ( (' '::text || _ ('Data:'::text) ) || ' '::text ) || d_mi.data ) || ' '::text ) ,''::text ) ) || COALESCE ( ( ( (' '::text || _ ('Provider:'::text) ) || ' '::text ) || d_st.short_alias ) ,''::text ) ) AS narrative , ( SELECT c_e.pk FROM clin.encounter c_e WHERE (c_e.fk_patient = d_mi.fk_patient) ORDER BY c_e.started DESC LIMIT 1 ) AS pk_encounter , NULL::integer AS pk_episode , NULL::integer AS pk_health_issue , d_mi.pk AS src_pk , 'dem.message_inbox'::text AS src_table , d_mi.row_version , NULL::text AS health_issue , NULL::text AS issue_laterality , NULL::boolean AS issue_active , NULL::boolean AS issue_clinically_relevant , NULL::boolean AS issue_confidential , NULL::text AS episode , NULL::boolean AS episode_open , ( SELECT c_e.started FROM clin.encounter c_e WHERE (c_e.fk_patient = d_mi.fk_patient) ORDER BY c_e.started DESC LIMIT 1 ) AS encounter_started , ( SELECT c_e.last_affirmed FROM clin.encounter c_e WHERE (c_e.fk_patient = d_mi.fk_patient) ORDER BY c_e.started DESC LIMIT 1 ) AS encounter_last_affirmed , NULL::text AS encounter_type , NULL::text AS encounter_l10n_type FROM ( (dem.message_inbox d_mi JOIN dem.v_inbox_item_type d_vit ON ( (d_mi.fk_inbox_item_type = d_vit.pk_type) ) ) LEFT JOIN dem.staff d_st ON ( (d_mi.fk_staff = d_st.pk) ) ) WHERE ( (d_mi.fk_patient IS NOT NULL) AND (d_vit.category = 'clinical'::text) AND (d_mi.expiry_date IS NOT NULL) ) UNION SELECT d_mi.fk_patient AS pk_patient , d_mi.modified_when , d_mi.modified_when AS clin_when , COALESCE ( ( SELECT staff.short_alias FROM dem.staff WHERE (staff.db_user = d_mi.modified_by) ) , ( ('<'::text || (d_mi.modified_by)::text ) || '>'::text ) ) AS modified_by , NULL::text AS soap_cat , ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( (_ ('Clinical reminder'::text) || ' ('::text) || d_vit.l10n_category ) || ' - '::text ) || d_vit.l10n_type ) || ')'::text ) || ' '::text ) || COALESCE ( ( (' '::text || d_mi.comment) || ' '::text ) ,''::text ) ) || COALESCE ( ( ( ( (' '::text || _ ('Due:'::text) ) || ' '::text ) || to_char ( (d_mi.due_date)::timestamp with time zone ,'YYYY-MM-DD'::text ) ) || ' '::text ) ,''::text ) ) || COALESCE ( ( ( ( (' '::text || _ ('Expires:'::text) ) || ' '::text ) || to_char ( (d_mi.expiry_date)::timestamp with time zone ,'YYYY-MM-DD'::text ) ) || ' '::text ) ,''::text ) ) || ' '::text ) || _ ('Importance:'::text) ) || ' '::text ) || d_mi.importance ) || ' '::text ) || COALESCE ( ( ( ( (' '::text || _ ('Context:'::text) ) || ' '::text ) || array_to_string (d_mi.ufk_context ,','::text ,'?'::text ) ) || ' '::text ) ,''::text ) ) || COALESCE ( ( ( ( (' '::text || _ ('Data:'::text) ) || ' '::text ) || d_mi.data ) || ' '::text ) ,''::text ) ) || COALESCE ( ( ( (' '::text || _ ('Provider:'::text) ) || ' '::text ) || d_st.short_alias ) ,''::text ) ) AS narrative , ( SELECT c_e.pk FROM clin.encounter c_e WHERE (c_e.fk_patient = d_mi.fk_patient) ORDER BY c_e.started DESC LIMIT 1 ) AS pk_encounter , NULL::integer AS pk_episode , NULL::integer AS pk_health_issue , d_mi.pk AS src_pk , 'dem.message_inbox'::text AS src_table , d_mi.row_version , NULL::text AS health_issue , NULL::text AS issue_laterality , NULL::boolean AS issue_active , NULL::boolean AS issue_clinically_relevant , NULL::boolean AS issue_confidential , NULL::text AS episode , NULL::boolean AS episode_open , ( SELECT c_e.started FROM clin.encounter c_e WHERE (c_e.fk_patient = d_mi.fk_patient) ORDER BY c_e.started DESC LIMIT 1 ) AS encounter_started , ( SELECT c_e.last_affirmed FROM clin.encounter c_e WHERE (c_e.fk_patient = d_mi.fk_patient) ORDER BY c_e.started DESC LIMIT 1 ) AS encounter_last_affirmed , NULL::text AS encounter_type , NULL::text AS encounter_l10n_type FROM ( (dem.message_inbox d_mi JOIN dem.v_inbox_item_type d_vit ON ( (d_mi.fk_inbox_item_type = d_vit.pk_type) ) ) LEFT JOIN dem.staff d_st ON ( (d_mi.fk_staff = d_st.pk) ) ) WHERE ( (d_mi.fk_patient IS NOT NULL) AND (d_vit.category = 'clinical'::text) AND ( (d_mi.due_date IS NOT NULL) OR (d_mi.expiry_date IS NOT NULL) ) );
denormalization of parent table of reviewed items
F-Key | Name | Type | Description |
---|---|---|---|
src_schema | oid | ||
src_table | name | ||
pk_reviewed_row | integer | ||
is_technically_abnormal | boolean | ||
clinically_relevant | boolean | ||
reviewer | text | ||
comment | text | ||
pk_review_root | integer | ||
pk_reviewer | integer |
SELECT ( SELECT pg_class.relnamespace FROM pg_class WHERE (pg_class.oid = rr.tableoid) ) AS src_schema , ( SELECT pg_class.relname FROM pg_class WHERE (pg_class.oid = rr.tableoid) ) AS src_table , rr.fk_reviewed_row AS pk_reviewed_row , rr.is_technically_abnormal , rr.clinically_relevant , ( SELECT staff.short_alias FROM dem.staff WHERE (staff.pk = rr.fk_reviewer) ) AS reviewer , rr.comment , rr.pk AS pk_review_root , rr.fk_reviewer AS pk_reviewer FROM clin.review_root rr;
F-Key | Name | Type | Description |
---|---|---|---|
pk_patient | integer | ||
soap_cat | text | ||
narrative | text | ||
pk_encounter | integer | ||
pk_episode | integer | ||
pk_health_issue | integer | ||
src_pk | integer | ||
src_table | text |
SELECT c_enc.fk_patient AS pk_patient , c_si.soap_cat , ( ( ( (COALESCE (c_si.narrative ,''::text ) || COALESCE ( (' / '::text || c_si.comment_on_start) ,''::text ) ) || COALESCE ( (' / '::text || c_si.schedule) ,''::text ) ) || COALESCE ( (' / '::text || c_si.aim) ,''::text ) ) || COALESCE ( (' / '::text || c_si.discontinue_reason) ,''::text ) ) AS narrative , c_si.fk_encounter AS pk_encounter , c_si.fk_episode AS pk_episode , c_epi.fk_health_issue AS pk_health_issue , c_si.pk AS src_pk , 'clin.substance_intake'::text AS src_table FROM ( (clin.substance_intake c_si JOIN clin.encounter c_enc ON ( (c_si.fk_encounter = c_enc.pk) ) ) JOIN clin.episode c_epi ON ( (c_si.fk_episode = c_epi.pk) ) );
F-Key | Name | Type | Description |
---|---|---|---|
pk_patient | integer | ||
modified_when | timestamp with time zone | ||
clin_when | timestamp with time zone | ||
modified_by | text | ||
soap_cat | text | ||
narrative | text | ||
pk_encounter | integer | ||
pk_episode | integer | ||
pk_health_issue | integer | ||
src_pk | integer | ||
src_table | text | ||
row_version | integer | ||
health_issue | text | ||
issue_laterality | character varying(2) | ||
issue_active | boolean | ||
issue_clinically_relevant | boolean | ||
issue_confidential | boolean | ||
episode | text | ||
episode_open | boolean | ||
encounter_started | timestamp with time zone | ||
encounter_last_affirmed | timestamp with time zone | ||
encounter_type | text | ||
encounter_l10n_type | text |
SELECT c_enc.fk_patient AS pk_patient , c_si.modified_when , c_si.clin_when , COALESCE ( ( SELECT staff.short_alias FROM dem.staff WHERE (staff.db_user = c_si.modified_by) ) , ( ('<'::text || (c_si.modified_by)::text ) || '>'::text ) ) AS modified_by , c_si.soap_cat , ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( CASE WHEN (c_si.is_long_term IS TRUE) THEN (_ ('long-term'::text) || ' '::text ) ELSE ''::text END || _ ('substance intake'::text) ) || ' '::text ) || CASE WHEN (c_si.intake_is_approved_of IS TRUE) THEN _ ('(approved of)'::text ) WHEN (c_si.intake_is_approved_of IS FALSE) THEN _ ('(not approved of)'::text ) ELSE _ ('(of unknown approval)'::text ) END ) || CASE WHEN (c_si.harmful_use_type IS NULL) THEN ''::text WHEN (c_si.harmful_use_type = 0) THEN _ ('no harmful use'::text) WHEN (c_si.harmful_use_type = 1) THEN _ ('harmful use'::text) WHEN (c_si.harmful_use_type = 2) THEN _ ('addiction'::text) WHEN (c_si.harmful_use_type = 3) THEN _ ('previous addiction'::text) ELSE NULL::text END ) || ': '::text ) || ' '::text ) || r_cs.description ) || COALESCE ( ( (' ['::text || r_cs.atc_code) || '] '::text ) ,' '::text ) ) || (r_cs.amount)::text ) || r_cs.unit ) || ' '::text ) || r_bd.preparation ) || COALESCE ( (' '::text || c_si.schedule) ,''::text ) ) || ', '::text ) || CASE WHEN (c_si.comment_on_start = '?'::text) THEN '?'::text WHEN (c_si.comment_on_start IS NULL) THEN to_char (c_si.clin_when ,'YYYY-MM-DD'::text ) ELSE ( ( ( ('~'::text || to_char (c_si.clin_when ,'YYYY-MM-DD'::text ) ) || ' ('::text) || c_si.comment_on_start ) || ')'::text ) END ) || COALESCE ( (' -> '::text || c_si.duration) ,''::text ) ) || ' '::text ) || COALESCE ( ( ( ( (' '::text || _ ('Discontinued'::text) ) || to_char (c_si.discontinued ,': YYYY-MM-DD'::text ) ) || COALESCE ( ( ( ' ('::text || c_si.discontinue_reason) || ')'::text ) ,''::text ) ) || ' '::text ) ,''::text ) ) || COALESCE ( ( ( (' '::text || _ ('Aim'::text) ) || ': '::text ) || c_si.aim ) ,''::text ) ) || COALESCE ( ( ( (' '::text || _ ('Notes'::text) ) || ': '::text ) || c_si.narrative ) ,''::text ) ) || COALESCE ( ( ( ( (' "'::text || r_bd.description) || '"'::text ) || COALESCE ( ( (' ['::text || r_bd.atc_code) || ']'::text ) ,''::text ) ) || COALESCE ( ( ( ( ( ' ('::text || r_bd.external_code_type) || ': '::text ) || r_bd.external_code ) || ')'::text ) ,''::text ) ) , ''::text ) ) AS narrative , c_si.fk_encounter AS pk_encounter , c_si.fk_episode AS pk_episode , ( SELECT episode.fk_health_issue FROM clin.episode WHERE (episode.pk = c_si.fk_episode) ) AS pk_health_issue , c_si.pk AS src_pk , 'clin.substance_intake'::text AS src_table , c_si.row_version , c_hi.description AS health_issue , c_hi.laterality AS issue_laterality , c_hi.is_active AS issue_active , c_hi.clinically_relevant AS issue_clinically_relevant , c_hi.is_confidential AS issue_confidential , c_epi.description AS episode , c_epi.is_open AS episode_open , c_enc.started AS encounter_started , c_enc.last_affirmed AS encounter_last_affirmed , c_ety.description AS encounter_type , _ ( c_ety.description) AS encounter_l10n_type FROM ( ( ( ( ( ( (clin.substance_intake c_si JOIN ref.lnk_substance2brand r_ls2b ON ( (c_si.fk_drug_component = r_ls2b.pk) ) ) JOIN ref.branded_drug r_bd ON ( (r_bd.pk = r_ls2b.fk_brand) ) ) JOIN ref.consumable_substance r_cs ON ( (r_cs.pk = r_ls2b.fk_substance) ) ) JOIN clin.encounter c_enc ON ( (c_si.fk_encounter = c_enc.pk) ) ) JOIN clin.encounter_type c_ety ON ( (c_enc.fk_type = c_ety.pk) ) ) JOIN clin.episode c_epi ON ( (c_si.fk_episode = c_epi.pk) ) ) LEFT JOIN clin.health_issue c_hi ON ( ( c_epi.fk_health_issue = c_hi.pk) ) ) WHERE (c_si.fk_drug_component IS NOT NULL) UNION ALL SELECT c_enc.fk_patient AS pk_patient , c_si.modified_when , c_si.clin_when , COALESCE ( ( SELECT staff.short_alias FROM dem.staff WHERE (staff.db_user = c_si.modified_by) ) , ( ( '<'::text || (c_si.modified_by)::text ) || '>'::text ) ) AS modified_by , c_si.soap_cat , ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( CASE WHEN (c_si.is_long_term IS TRUE) THEN (_ ('long-term'::text) || ' '::text ) ELSE ''::text END || CASE WHEN (c_si.harmful_use_type IS NULL) THEN (_ ('Substance intake'::text) || ' '::text ) ELSE (_ ('Substance abuse'::text) || ' '::text ) END ) || CASE WHEN (c_si.intake_is_approved_of IS TRUE) THEN _ ('(approved of)'::text ) WHEN (c_si.intake_is_approved_of IS FALSE) THEN _ ('(not approved of)'::text ) ELSE _ ('(of unknown approval)'::text ) END ) || CASE WHEN (c_si.harmful_use_type IS NULL) THEN ''::text WHEN (c_si.harmful_use_type = 0) THEN ( ( ( ( ', '::text || _ ('no harmful use'::text) ) || ' ('::text) || to_char (c_enc.started ,'YYYY-MM-DD'::text ) ) || ')'::text ) WHEN (c_si.harmful_use_type = 1) THEN ( ( ( ( ', '::text || _ ('harmful use'::text) ) || ' ('::text) || to_char (c_enc.started ,'YYYY-MM-DD'::text ) ) || ')'::text ) WHEN (c_si.harmful_use_type = 2) THEN ( ( ( ( ', '::text || _ ('addiction'::text) ) || ' ('::text) || to_char (c_enc.started ,'YYYY-MM-DD'::text ) ) || ')'::text ) WHEN (c_si.harmful_use_type = 3) THEN ( ( ( ( ', '::text || _ ('previous addiction'::text) ) || ' ('::text) || to_char (c_enc.started ,'YYYY-MM-DD'::text ) ) || ')'::text ) ELSE NULL::text END ) || ': '::text ) || ' '::text ) || r_cs.description ) || COALESCE ( ( (' ['::text || r_cs.atc_code) || '] '::text ) ,' '::text ) ) || r_cs.amount ) || r_cs.unit ) || ' '::text ) || c_si.preparation ) || COALESCE ( (' '::text || c_si.schedule) ,''::text ) ) || ', '::text ) || CASE WHEN (c_si.comment_on_start = '?'::text) THEN '?'::text WHEN (c_si.comment_on_start IS NULL) THEN to_char (c_si.clin_when ,'YYYY-MM-DD'::text ) ELSE ( ( ( ('~'::text || to_char (c_si.clin_when ,'YYYY-MM-DD'::text ) ) || ' ('::text) || c_si.comment_on_start ) || ')'::text ) END ) || COALESCE ( (' -> '::text || c_si.duration) ,''::text ) ) || ' '::text ) || COALESCE ( ( ( ( (' '::text || _ ('Discontinued'::text) ) || to_char (c_si.discontinued ,': YYYY-MM-DD'::text ) ) || COALESCE ( ( ( ' ('::text || c_si.discontinue_reason) || ')'::text ) ,''::text ) ) || ' '::text ) , ''::text ) ) || COALESCE ( ( ( ( ' '::text || _ ('Aim'::text) ) || ': '::text ) || c_si.aim ) , ''::text ) ) || COALESCE ( ( ( ( ' '::text || _ ( 'Notes'::text) ) || ': '::text ) || c_si.narrative ) , ''::text ) ) AS narrative , c_si.fk_encounter AS pk_encounter , c_si.fk_episode AS pk_episode , ( SELECT episode.fk_health_issue FROM clin.episode WHERE (episode.pk = c_si.fk_episode) ) AS pk_health_issue , c_si.pk AS src_pk , 'clin.substance_intake'::text AS src_table , c_si.row_version , c_hi.description AS health_issue , c_hi.laterality AS issue_laterality , c_hi.is_active AS issue_active , c_hi.clinically_relevant AS issue_clinically_relevant , c_hi.is_confidential AS issue_confidential , c_epi.description AS episode , c_epi.is_open AS episode_open , c_enc.started AS encounter_started , c_enc.last_affirmed AS encounter_last_affirmed , c_ety.description AS encounter_type , _ ( c_ety.description) AS encounter_l10n_type FROM ( ( ( ( ( clin.substance_intake c_si JOIN ref.consumable_substance r_cs ON ( (r_cs.pk = c_si.fk_substance) ) ) JOIN clin.encounter c_enc ON ( ( c_si.fk_encounter = c_enc.pk) ) ) JOIN clin.encounter_type c_ety ON ( ( c_enc.fk_type = c_ety.pk) ) ) JOIN clin.episode c_epi ON ( ( c_si.fk_episode = c_epi.pk) ) ) LEFT JOIN clin.health_issue c_hi ON ( ( c_epi.fk_health_issue = c_hi.pk) ) ) WHERE (c_si.fk_drug_component IS NULL);
F-Key | Name | Type | Description |
---|---|---|---|
pk_substance_intake | integer | ||
pk_patient | integer | ||
soap_cat | text | ||
brand | text | ||
preparation | text | ||
substance | text | ||
amount | numeric | ||
unit | text | ||
atc_substance | text | ||
atc_brand | text | ||
external_code_brand | text | ||
external_code_type_brand | text | ||
started | timestamp with time zone | ||
comment_on_start | text | ||
start_is_unknown | boolean | ||
start_is_approximate | boolean | ||
intake_is_approved_of | boolean | ||
harmful_use_type | integer | ||
last_checked_when | timestamp with time zone | ||
schedule | text | ||
duration | interval | ||
discontinued | timestamp with time zone | ||
discontinue_reason | text | ||
is_long_term | boolean | ||
aim | text | ||
episode | text | ||
health_issue | text | ||
notes | text | ||
fake_brand | boolean | ||
is_currently_active | boolean | ||
seems_inactive | boolean | ||
pk_brand | integer | ||
pk_data_source | integer | ||
pk_substance | integer | ||
pk_drug_component | integer | ||
pk_encounter | integer | ||
pk_episode | integer | ||
pk_health_issue | integer | ||
modified_when | timestamp with time zone | ||
modified_by | name | ||
row_version | integer | ||
xmin_substance_intake | xid |
SELECT v_brand_intakes.pk_substance_intake , v_brand_intakes.pk_patient , v_brand_intakes.soap_cat , v_brand_intakes.brand , v_brand_intakes.preparation , v_brand_intakes.substance , v_brand_intakes.amount , v_brand_intakes.unit , v_brand_intakes.atc_substance , v_brand_intakes.atc_brand , v_brand_intakes.external_code_brand , v_brand_intakes.external_code_type_brand , v_brand_intakes.started , v_brand_intakes.comment_on_start , v_brand_intakes.start_is_unknown , v_brand_intakes.start_is_approximate , v_brand_intakes.intake_is_approved_of , v_brand_intakes.harmful_use_type , v_brand_intakes.last_checked_when , v_brand_intakes.schedule , v_brand_intakes.duration , v_brand_intakes.discontinued , v_brand_intakes.discontinue_reason , v_brand_intakes.is_long_term , v_brand_intakes.aim , v_brand_intakes.episode , v_brand_intakes.health_issue , v_brand_intakes.notes , v_brand_intakes.fake_brand , v_brand_intakes.is_currently_active , v_brand_intakes.seems_inactive , v_brand_intakes.pk_brand , v_brand_intakes.pk_data_source , v_brand_intakes.pk_substance , v_brand_intakes.pk_drug_component , v_brand_intakes.pk_encounter , v_brand_intakes.pk_episode , v_brand_intakes.pk_health_issue , v_brand_intakes.modified_when , v_brand_intakes.modified_by , v_brand_intakes.row_version , v_brand_intakes.xmin_substance_intake FROM clin.v_brand_intakes UNION ALL SELECT v_nonbrand_intakes.pk_substance_intake , v_nonbrand_intakes.pk_patient , v_nonbrand_intakes.soap_cat , v_nonbrand_intakes.brand , v_nonbrand_intakes.preparation , v_nonbrand_intakes.substance , v_nonbrand_intakes.amount , v_nonbrand_intakes.unit , v_nonbrand_intakes.atc_substance , v_nonbrand_intakes.atc_brand , v_nonbrand_intakes.external_code_brand , v_nonbrand_intakes.external_code_type_brand , v_nonbrand_intakes.started , v_nonbrand_intakes.comment_on_start , v_nonbrand_intakes.start_is_unknown , v_nonbrand_intakes.start_is_approximate , v_nonbrand_intakes.intake_is_approved_of , v_nonbrand_intakes.harmful_use_type , v_nonbrand_intakes.last_checked_when , v_nonbrand_intakes.schedule , v_nonbrand_intakes.duration , v_nonbrand_intakes.discontinued , v_nonbrand_intakes.discontinue_reason , v_nonbrand_intakes.is_long_term , v_nonbrand_intakes.aim , v_nonbrand_intakes.episode , v_nonbrand_intakes.health_issue , v_nonbrand_intakes.notes , v_nonbrand_intakes.fake_brand , v_nonbrand_intakes.is_currently_active , v_nonbrand_intakes.seems_inactive , v_nonbrand_intakes.pk_brand , v_nonbrand_intakes.pk_data_source , v_nonbrand_intakes.pk_substance , v_nonbrand_intakes.pk_drug_component , v_nonbrand_intakes.pk_encounter , v_nonbrand_intakes.pk_episode , v_nonbrand_intakes.pk_health_issue , v_nonbrand_intakes.modified_when , v_nonbrand_intakes.modified_by , v_nonbrand_intakes.row_version , v_nonbrand_intakes.xmin_substance_intake FROM clin.v_nonbrand_intakes;
F-Key | Name | Type | Description |
---|---|---|---|
pk_identity | integer | ||
pk_suppressed_hint | integer | ||
pk_hint | integer | ||
title | text | ||
hint | text | ||
recommendation | text | ||
url | text | ||
is_active | boolean | ||
source | text | ||
query | text | ||
lang | text | ||
rationale | text | ||
md5_suppressed | text | ||
md5_hint | text | ||
suppressed_by | name | ||
suppressed_when | timestamp with time zone | ||
pk_encounter | integer |
SELECT ( SELECT encounter.fk_patient FROM clin.encounter WHERE (encounter.pk = c_sh.fk_encounter) ) AS pk_identity , c_sh.pk AS pk_suppressed_hint , c_sh.fk_hint AS pk_hint , r_vah.title , r_vah.hint , r_vah.recommendation , r_vah.url , r_vah.is_active , r_vah.source , r_vah.query , r_vah.lang , c_sh.rationale , c_sh.md5_sum AS md5_suppressed , r_vah.md5_sum AS md5_hint , c_sh.suppressed_by , c_sh.suppressed_when , c_sh.fk_encounter AS pk_encounter FROM (clin.suppressed_hint c_sh JOIN ref.v_auto_hints r_vah ON ( (c_sh.fk_hint = r_vah.pk_auto_hint) ) );
F-Key | Name | Type | Description |
---|---|---|---|
pk_patient | integer | ||
modified_when | timestamp with time zone | ||
clin_when | timestamp with time zone | ||
modified_by | name | ||
soap_cat | text | ||
narrative | text | ||
fk_encounter | integer | ||
pk_episode | integer | ||
pk_health_issue | integer | ||
src_pk | integer | ||
src_table | text | ||
row_version | integer | ||
health_issue | text | ||
issue_laterality | text | ||
issue_active | boolean | ||
issue_clinically_relevant | boolean | ||
issue_confidential | boolean | ||
episode | text | ||
episode_open | boolean | ||
encounter_started | timestamp with time zone | ||
encounter_last_affirmed | timestamp with time zone | ||
encounter_type | text | ||
encounter_l10n_type | text |
SELECT c_enc.fk_patient AS pk_patient , c_sh.modified_when , c_sh.suppressed_when AS clin_when , c_sh.modified_by , 'p'::text AS soap_cat , ( ( ( ( ( ( ( ( ( ( ( ( ( ( CASE WHEN (r_vah.is_active IS TRUE) THEN _ ('Active hint'::text) ELSE _ ('Inactive hint'::text) END || ' #'::text ) || c_sh.fk_hint ) || ' '::text ) || _ ('suppressed by'::text) ) || ' '::text ) || (c_sh.suppressed_by)::text ) || ' '::text ) || COALESCE ( ( (_ ('Title: '::text) || r_vah.title ) || ' '::text ) ,''::text ) ) || COALESCE ( ( (_ ('URL: '::text) || r_vah.url ) || ' '::text ) ,''::text ) ) || COALESCE ( ( (_ ('Source: '::text) || r_vah.source ) || ' '::text ) ,''::text ) ) || COALESCE ( ( (_ ('Rationale: '::text) || c_sh.rationale ) || ' '::text ) ,''::text ) ) || CASE WHEN (c_sh.md5_sum <> r_vah.md5_sum) THEN (_ ('Hint definition has been modified since suppression. Rationale for suppression may no longer apply.'::text) || ' '::text ) ELSE ''::text END ) || COALESCE ( ( (_ ('Hint: '::text) || r_vah.hint ) || ' '::text ) ,''::text ) ) || COALESCE ( (_ ('Recommendation: '::text) || r_vah.recommendation ) ,''::text ) ) AS narrative , c_sh.fk_encounter , NULL::integer AS pk_episode , NULL::integer AS pk_health_issue , c_sh.pk AS src_pk , 'clin.suppressed_hint'::text AS src_table , c_sh.row_version , NULL::text AS health_issue , NULL::text AS issue_laterality , NULL::boolean AS issue_active , NULL::boolean AS issue_clinically_relevant , NULL::boolean AS issue_confidential , NULL::text AS episode , NULL::boolean AS episode_open , c_enc.started AS encounter_started , c_enc.last_affirmed AS encounter_last_affirmed , c_ety.description AS encounter_type , _ (c_ety.description) AS encounter_l10n_type FROM ( ( (clin.suppressed_hint c_sh JOIN clin.encounter c_enc ON ( (c_sh.fk_encounter = c_enc.pk) ) ) JOIN clin.encounter_type c_ety ON ( (c_enc.fk_type = c_ety.pk) ) ) JOIN ref.v_auto_hints r_vah ON ( (c_sh.fk_hint = r_vah.pk_auto_hint) ) );
F-Key | Name | Type | Description |
---|---|---|---|
pk_test_org | integer | ||
organization | text | ||
unit | text | ||
comment | text | ||
test_org_contact | text | ||
pk_adm_contact | integer | ||
pk_med_contact | integer | ||
pk_org | integer | ||
category_org | integer | ||
pk_org_unit | integer | ||
category_unit | integer | ||
pk_address_unit | integer | ||
xmin_test_org | xid |
SELECT c_to.pk AS pk_test_org , d_o.description AS organization , d_ou.description AS unit , c_to.comment , c_to.contact AS test_org_contact , c_to.fk_adm_contact AS pk_adm_contact , c_to.fk_med_contact AS pk_med_contact , d_o.pk AS pk_org , d_o.fk_category AS category_org , d_ou.pk AS pk_org_unit , d_ou.fk_category AS category_unit , d_ou.fk_address AS pk_address_unit , c_to.xmin AS xmin_test_org FROM ( (clin.test_org c_to LEFT JOIN dem.org_unit d_ou ON ( (c_to.fk_org_unit = d_ou.pk) ) ) LEFT JOIN dem.org d_o ON ( (d_ou.fk_org = d_o.pk) ) );
F-Key | Name | Type | Description |
---|---|---|---|
pk_test_panel | integer | ||
description | text | ||
comment | text | ||
pk_test_types | integer[] | ||
modified_when | timestamp with time zone | ||
modified_by | name | ||
pk_generic_codes | integer[] | ||
row_version | integer | ||
xmin_test_panel | xid |
SELECT c_tp.pk AS pk_test_panel , c_tp.description , c_tp.comment , c_tp.fk_test_types AS pk_test_types , c_tp.modified_when , c_tp.modified_by , COALESCE ( ( SELECT array_agg (c_lc2tp.fk_generic_code) AS array_agg FROM clin.lnk_code2tst_pnl c_lc2tp WHERE (c_lc2tp.fk_item = c_tp.pk) ) , ARRAY[]::integer[] ) AS pk_generic_codes , c_tp.row_version , c_tp.xmin AS xmin_test_panel FROM clin.test_panel c_tp;
denormalized view over test_results joined with (possibly unified) test type and patient/episode/encounter keys
F-Key | Name | Type | Description |
---|---|---|---|
pk_patient | integer | ||
pk_test_result | integer | ||
clin_when | timestamp with time zone | ||
unified_abbrev | text | ||
unified_name | text | ||
unified_loinc | text | ||
unified_val | text | ||
unified_target_min | numeric | ||
unified_target_max | numeric | ||
unified_target_range | text | ||
status | text | ||
soap_cat | text | ||
comment | text | ||
val_num | numeric | ||
val_alpha | text | ||
val_unit | text | ||
reference_unit | text | ||
val_normal_min | numeric | ||
val_normal_max | numeric | ||
val_normal_range | text | ||
val_target_min | numeric | ||
val_target_max | numeric | ||
val_target_range | text | ||
abnormality_indicator | text | ||
norm_ref_group | text | ||
note_test_org | text | ||
material | text | ||
material_detail | text | ||
abbrev_tt | text | ||
name_tt | text | ||
loinc_tt | text | ||
comment_tt | text | ||
name_test_org | text | ||
contact_test_org | text | ||
comment_test_org | text | ||
is_fake_meta_type | boolean | ||
abbrev_meta | text | ||
name_meta | text | ||
loinc_meta | text | ||
comment_meta | text | ||
episode | text | ||
health_issue | text | ||
reviewed | boolean | ||
is_technically_abnormal | boolean | ||
is_clinically_relevant | boolean | ||
review_comment | text | ||
last_reviewer | text | ||
last_reviewed | timestamp with time zone | ||
review_by_you | boolean | ||
review_by_responsible_reviewer | boolean | ||
responsible_reviewer | text | ||
you_are_responsible | boolean | ||
modified_by | text | ||
val_grouping | text | ||
source_data | text | ||
modified_when | timestamp with time zone | ||
row_version | integer | ||
pk_item | integer | ||
pk_encounter | integer | ||
pk_episode | integer | ||
pk_test_type | integer | ||
pk_intended_reviewer | integer | ||
pk_request | integer | ||
xmin_test_result | xid | ||
pk_test_org | integer | ||
pk_meta_test_type | integer | ||
pk_health_issue | integer | ||
pk_last_reviewer | integer |
SELECT cenc.fk_patient AS pk_patient , tr.pk AS pk_test_result , tr.clin_when , c_vtt.unified_abbrev , c_vtt.unified_name , c_vtt.unified_loinc , CASE WHEN (COALESCE (btrim (tr.val_alpha) ,''::text ) = ''::text ) THEN (tr.val_num)::text ELSE CASE WHEN (tr.val_num IS NULL) THEN tr.val_alpha ELSE ( ( ( (tr.val_num)::text || ' ('::text) || tr.val_alpha ) || ')'::text ) END END AS unified_val , COALESCE (tr.val_target_min , tr.val_normal_min ) AS unified_target_min , COALESCE (tr.val_target_max , tr.val_normal_max ) AS unified_target_max , COALESCE (tr.val_target_range , tr.val_normal_range ) AS unified_target_range , tr.status , tr.soap_cat , tr.narrative AS comment , tr.val_num , tr.val_alpha , tr.val_unit , c_vtt.reference_unit , tr.val_normal_min , tr.val_normal_max , tr.val_normal_range , tr.val_target_min , tr.val_target_max , tr.val_target_range , tr.abnormality_indicator , tr.norm_ref_group , tr.note_test_org , tr.material , tr.material_detail , c_vtt.abbrev AS abbrev_tt , c_vtt.name AS name_tt , c_vtt.loinc AS loinc_tt , c_vtt.comment_type AS comment_tt , c_vtt.name_org AS name_test_org , c_vtt.contact_org AS contact_test_org , c_vtt.comment_org AS comment_test_org , c_vtt.is_fake_meta_type , c_vtt.abbrev_meta , c_vtt.name_meta , c_vtt.loinc_meta , c_vtt.comment_meta , epi.description AS episode , chi.description AS health_issue , (COALESCE (rtr.fk_reviewed_row , 0 ) )::boolean AS reviewed , rtr.is_technically_abnormal , rtr.clinically_relevant AS is_clinically_relevant , rtr.comment AS review_comment , ( SELECT staff.short_alias FROM dem.staff WHERE (staff.pk = rtr.fk_reviewer) ) AS last_reviewer , rtr.modified_when AS last_reviewed , COALESCE ( (rtr.fk_reviewer = ( SELECT staff.pk FROM dem.staff WHERE (staff.db_user = "current_user" () ) ) ) , false ) AS review_by_you , COALESCE ( (tr.fk_intended_reviewer = rtr.fk_reviewer) , false ) AS review_by_responsible_reviewer , ( SELECT staff.short_alias FROM dem.staff WHERE (staff.pk = tr.fk_intended_reviewer) ) AS responsible_reviewer , COALESCE ( (tr.fk_intended_reviewer = ( SELECT staff.pk FROM dem.staff WHERE (staff.db_user = "current_user" () ) ) ) , false ) AS you_are_responsible , CASE WHEN ( ( SELECT 1 FROM dem.staff WHERE (staff.db_user = tr.modified_by) ) IS NULL ) THEN ( ('<'::text || (tr.modified_by)::text ) || '>'::text ) ELSE ( SELECT staff.short_alias FROM dem.staff WHERE (staff.db_user = tr.modified_by) ) END AS modified_by , tr.val_grouping , tr.source_data , tr.modified_when , tr.row_version , tr.pk_item , tr.fk_encounter AS pk_encounter , tr.fk_episode AS pk_episode , tr.fk_type AS pk_test_type , tr.fk_intended_reviewer AS pk_intended_reviewer , tr.fk_request AS pk_request , tr.xmin AS xmin_test_result , c_vtt.pk_test_org , c_vtt.pk_meta_test_type , epi.fk_health_issue AS pk_health_issue , rtr.fk_reviewer AS pk_last_reviewer FROM ( ( ( (clin.test_result tr LEFT JOIN clin.encounter cenc ON ( (tr.fk_encounter = cenc.pk) ) ) LEFT JOIN clin.episode epi ON ( (tr.fk_episode = epi.pk) ) ) LEFT JOIN clin.reviewed_test_results rtr ON ( (tr.pk = rtr.fk_reviewed_row) ) ) LEFT JOIN clin.health_issue chi ON ( (epi.fk_health_issue = chi.pk) ) ) , clin.v_test_types c_vtt WHERE (tr.fk_type = c_vtt.pk_test_type);
formatting of v_test_results for inclusion in v_emr_journal
F-Key | Name | Type | Description |
---|---|---|---|
pk_patient | integer | ||
modified_when | timestamp with time zone | ||
clin_when | timestamp with time zone | ||
modified_by | text | ||
soap_cat | text | ||
narrative | text | ||
pk_encounter | integer | ||
pk_episode | integer | ||
pk_health_issue | integer | ||
src_pk | integer | ||
src_table | text | ||
row_version | integer | ||
health_issue | text | ||
issue_laterality | character varying(2) | ||
issue_active | boolean | ||
issue_clinically_relevant | boolean | ||
issue_confidential | boolean | ||
episode | text | ||
episode_open | boolean | ||
encounter_started | timestamp with time zone | ||
encounter_last_affirmed | timestamp with time zone | ||
encounter_type | text | ||
encounter_l10n_type | text |
SELECT c_vtr.pk_patient , c_vtr.modified_when , c_vtr.clin_when , c_vtr.modified_by , c_vtr.soap_cat , COALESCE ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( (c_vtr.unified_name || ' ('::text) || c_vtr.unified_abbrev ) || COALESCE ( ( (' [#'::text || c_vtr.unified_loinc) || ']'::text ) ,''::text ) ) || '): '::text ) || c_vtr.unified_val ) || ' '::text ) || COALESCE (c_vtr.val_unit ,''::text ) ) || ' '::text ) || COALESCE ( ( ( '('::text || c_vtr.abnormality_indicator) || ')'::text ) ,''::text ) ) || ' '::text ) || _ ('Range: '::text) ) || COALESCE ( (c_vtr.unified_target_min)::text ,''::text ) ) || ' - '::text ) || COALESCE ( (c_vtr.unified_target_max)::text ,''::text ) ) || ' / '::text ) || COALESCE (c_vtr.unified_target_range ,''::text ) ) || COALESCE ( ( ( ' ('::text || c_vtr.norm_ref_group) || ')'::text ) ,''::text ) ) || ' '::text ) || COALESCE ( ( (_ ('Assessment: '::text) || c_vtr.comment ) || ' '::text ) ,''::text ) ) || COALESCE ( ( (_ ('Context: '::text) || c_vtr.note_test_org ) || ' '::text ) ,''::text ) ) || COALESCE ( ( (_ ('Status: '::text) || c_vtr.status ) || ' '::text ) ,''::text ) ) || COALESCE ( ( (_ ('Grouping: '::text) || c_vtr.val_grouping ) || ' '::text ) ,''::text ) ) || COALESCE ( ( ( ( ( ( ( (_ ('Review by '::text) || c_vtr.last_reviewer ) || ' @ '::text ) || to_char (c_vtr.last_reviewed ,'YYYY-MM-DD HH24:MI'::text ) ) || ': '::text ) || CASE WHEN c_vtr.is_technically_abnormal THEN (_ ('abnormal'::text) || ', '::text ) ELSE ''::text END ) || CASE WHEN c_vtr.is_clinically_relevant THEN (_ ('relevant'::text) || ' '::text ) ELSE ''::text END ) || COALESCE ( ( ( '('::text || c_vtr.review_comment) || ') '::text ) ,' '::text ) ) ,''::text ) ) || _ ('Responsible clinician: '::text) ) || c_vtr.responsible_reviewer ) || COALESCE ( ( ( (_ ('Source data:'::text) || ' '::text ) || c_vtr.source_data ) || ' '::text ) ,''::text ) ) ,'faulty clin.v_test_results_journal definition'::text ) AS narrative , c_vtr.pk_encounter , c_vtr.pk_episode , c_vtr.pk_health_issue , c_vtr.pk_test_result AS src_pk , 'clin.test_result'::text AS src_table , c_vtr.row_version , c_vtr.health_issue , c_hi.laterality AS issue_laterality , c_hi.is_active AS issue_active , c_hi.clinically_relevant AS issue_clinically_relevant , c_hi.is_confidential AS issue_confidential , c_vtr.episode , c_epi.is_open AS episode_open , c_enc.started AS encounter_started , c_enc.last_affirmed AS encounter_last_affirmed , c_ety.description AS encounter_type , _ (c_ety.description) AS encounter_l10n_type FROM ( ( ( (clin.v_test_results c_vtr JOIN clin.encounter c_enc ON ( (c_vtr.pk_encounter = c_enc.pk) ) ) JOIN clin.encounter_type c_ety ON ( (c_enc.fk_type = c_ety.pk) ) ) JOIN clin.episode c_epi ON ( (c_vtr.pk_episode = c_epi.pk) ) ) LEFT JOIN clin.health_issue c_hi ON ( (c_epi.fk_health_issue = c_hi.pk) ) );
denormalizes test types with test orgs and meta types
F-Key | Name | Type | Description |
---|---|---|---|
pk_test_type | integer | ||
abbrev | text | ||
name | text | ||
loinc | text | ||
reference_unit | text | ||
comment_type | text | ||
name_org | text | ||
comment_org | text | ||
contact_org | text | ||
unified_abbrev | text | ||
unified_name | text | ||
unified_loinc | text | ||
is_fake_meta_type | boolean | ||
abbrev_meta | text | ||
name_meta | text | ||
loinc_meta | text | ||
comment_meta | text | ||
pk_test_panels | integer[] | ||
pk_test_org | integer | ||
pk_meta_test_type | integer | ||
pk_org_unit | integer | ||
pk_adm_contact_org | integer | ||
pk_med_contact_org | integer | ||
xmin_test_type | xid |
SELECT c_tt.pk AS pk_test_type , c_tt.abbrev , c_tt.name , c_tt.loinc , c_tt.reference_unit , c_tt.comment AS comment_type , d_ou.description AS name_org , c_to.comment AS comment_org , c_to.contact AS contact_org , COALESCE (c_mtt.abbrev , c_tt.abbrev ) AS unified_abbrev , COALESCE (c_mtt.name , c_tt.name ) AS unified_name , COALESCE (c_mtt.loinc , c_tt.loinc ) AS unified_loinc , (c_tt.fk_meta_test_type IS NULL) AS is_fake_meta_type , c_mtt.abbrev AS abbrev_meta , c_mtt.name AS name_meta , c_mtt.loinc AS loinc_meta , c_mtt.comment AS comment_meta , ( SELECT array_agg (c_tp.pk) AS array_agg FROM clin.test_panel c_tp WHERE (c_tt.pk = ANY (c_tp.fk_test_types) ) ) AS pk_test_panels , c_tt.fk_test_org AS pk_test_org , c_tt.fk_meta_test_type AS pk_meta_test_type , c_to.fk_org_unit AS pk_org_unit , c_to.fk_adm_contact AS pk_adm_contact_org , c_to.fk_med_contact AS pk_med_contact_org , c_tt.xmin AS xmin_test_type FROM ( ( (clin.test_type c_tt LEFT JOIN clin.test_org c_to ON ( (c_to.pk = c_tt.fk_test_org) ) ) LEFT JOIN dem.org_unit d_ou ON ( (c_to.fk_org_unit = d_ou.pk) ) ) LEFT JOIN clin.meta_test_type c_mtt ON ( (c_tt.fk_meta_test_type = c_mtt.pk) ) );
F-Key | Name | Type | Description |
---|---|---|---|
vaccination_schedule | text | ||
is_active | boolean | ||
pk_recommended_by | integer | ||
comment_course | text | ||
comment_schedule | text | ||
pk_vaccination_course | integer | ||
pk_indication | integer | ||
pk_vaccination_schedule | integer |
SELECT cvs.name AS vaccination_schedule , cvc.is_active , cvc.fk_recommended_by AS pk_recommended_by , cvc.comment AS comment_course , cvs.comment AS comment_schedule , cvc.pk AS pk_vaccination_course , cvc.fk_indication AS pk_indication , cvs.pk AS pk_vaccination_schedule FROM clin.vaccination_course cvc , clin.vaccination_schedule cvs , clin.lnk_vaccination_course2schedule clvc2s WHERE ( (clvc2s.fk_course = cvc.pk) AND (clvc2s.fk_schedule = cvs.pk) );
vaccination event definitions for all courses known to the system
F-Key | Name | Type | Description |
---|---|---|---|
pk_course | integer | ||
indication | text | ||
l10n_indication | text | ||
course_comment | text | ||
is_active | boolean | ||
pk_vaccination_definition | integer | ||
is_booster | boolean | ||
vacc_seq_no | integer | ||
age_due_min | interval | ||
age_due_max | interval | ||
min_interval | interval | ||
vacc_comment | text | ||
pk_indication | integer | ||
pk_recommended_by | integer |
SELECT vcourse.pk AS pk_course , vind.description AS indication , _ (vind.description) AS l10n_indication , COALESCE (vcourse.comment ,''::text ) AS course_comment , vcourse.is_active , vdef.id AS pk_vaccination_definition , vdef.is_booster , vdef.seq_no AS vacc_seq_no , vdef.min_age_due AS age_due_min , vdef.max_age_due AS age_due_max , vdef.min_interval , COALESCE (vdef.comment ,''::text ) AS vacc_comment , vind.id AS pk_indication , vcourse.fk_recommended_by AS pk_recommended_by FROM clin.vaccination_course vcourse , clin.vacc_indication vind , clin.vaccination_definition vdef WHERE ( (vcourse.pk = vdef.fk_course) AND (vcourse.fk_indication = vind.id) ) ORDER BY vind.description , vdef.seq_no;
Vaccination data denormalized for the EMR journal.
F-Key | Name | Type | Description |
---|---|---|---|
pk_patient | integer | ||
modified_when | timestamp with time zone | ||
clin_when | timestamp with time zone | ||
modified_by | text | ||
soap_cat | text | ||
narrative | text | ||
pk_encounter | integer | ||
pk_episode | integer | ||
pk_health_issue | integer | ||
src_pk | integer | ||
src_table | text | ||
row_version | integer | ||
health_issue | text | ||
issue_laterality | character varying(2) | ||
issue_active | boolean | ||
issue_clinically_relevant | boolean | ||
issue_confidential | boolean | ||
episode | text | ||
episode_open | boolean | ||
encounter_started | timestamp with time zone | ||
encounter_last_affirmed | timestamp with time zone | ||
encounter_type | text | ||
encounter_l10n_type | text |
SELECT c_enc.fk_patient AS pk_patient , c_vacc.modified_when , c_vacc.clin_when , COALESCE ( ( SELECT staff.short_alias FROM dem.staff WHERE (staff.db_user = c_vacc.modified_by) ) , ( ('<'::text || (c_vacc.modified_by)::text ) || '>'::text ) ) AS modified_by , c_vacc.soap_cat , ( ( ( ( ( ( ( ( ( (_ ('Vaccination'::text) || ': '::text ) || r_bd.description ) || ' '::text ) || '['::text ) || c_vacc.batch_no ) || ']'::text ) || COALESCE ( ( ( ' ('::text || c_vacc.site) || ')'::text ) ,''::text ) ) || COALESCE ( ( ( (' '::text || _ ('Reaction'::text) ) || ': '::text ) || c_vacc.reaction ) ,''::text ) ) || COALESCE ( ( ( (' '::text || _ ('Comment'::text) ) || ': '::text ) || c_vacc.narrative ) ,''::text ) ) || COALESCE ( ( ( (' '::text || _ ('Indications'::text) ) || ': '::text ) || array_to_string ( ( SELECT array_agg (_ (cvi.description) ) AS array_agg FROM (clin.lnk_vaccine2inds c_lv2i JOIN clin.vacc_indication cvi ON ( (c_lv2i.fk_indication = cvi.id) ) ) WHERE (c_lv2i.fk_vaccine = c_vacc.fk_vaccine) ) ,' / '::text ) ) ,''::text ) ) AS narrative , c_vacc.fk_encounter AS pk_encounter , c_vacc.fk_episode AS pk_episode , ( SELECT episode.fk_health_issue FROM clin.episode WHERE (episode.pk = c_vacc.fk_episode) ) AS pk_health_issue , c_vacc.pk AS src_pk , 'clin.vaccination'::text AS src_table , c_vacc.row_version , c_hi.description AS health_issue , c_hi.laterality AS issue_laterality , c_hi.is_active AS issue_active , c_hi.clinically_relevant AS issue_clinically_relevant , c_hi.is_confidential AS issue_confidential , c_epi.description AS episode , c_epi.is_open AS episode_open , c_enc.started AS encounter_started , c_enc.last_affirmed AS encounter_last_affirmed , c_ety.description AS encounter_type , _ (c_ety.description) AS encounter_l10n_type FROM ( ( ( ( ( (clin.vaccination c_vacc JOIN clin.encounter c_enc ON ( (c_enc.pk = c_vacc.fk_encounter) ) ) JOIN clin.encounter_type c_ety ON ( (c_enc.fk_type = c_ety.pk) ) ) JOIN clin.episode c_epi ON ( (c_vacc.fk_episode = c_epi.pk) ) ) LEFT JOIN clin.health_issue c_hi ON ( (c_epi.fk_health_issue = c_hi.pk) ) ) JOIN clin.vaccine ON ( (vaccine.pk = c_vacc.fk_vaccine) ) ) JOIN ref.branded_drug r_bd ON ( (vaccine.fk_brand = r_bd.pk) ) );
A list of vaccines.
F-Key | Name | Type | Description |
---|---|---|---|
pk_vaccine | integer | ||
vaccine | text | ||
preparation | text | ||
atc_code | text | ||
is_fake_vaccine | boolean | ||
route_abbreviation | text | ||
route_description | text | ||
is_live | boolean | ||
min_age | interval | ||
max_age | interval | ||
comment | text | ||
indications | text[] | ||
l10n_indications | text[] | ||
external_code | text | ||
external_code_type | text | ||
pk_indications | integer[] | ||
pk_route | integer | ||
pk_brand | integer | ||
pk_data_source | integer | ||
xmin_vaccine | xid |
SELECT cv.pk AS pk_vaccine , rbd.description AS vaccine , rbd.preparation , rbd.atc_code , rbd.is_fake AS is_fake_vaccine , cvr.abbreviation AS route_abbreviation , cvr.description AS route_description , cv.is_live , cv.min_age , cv.max_age , cv.comment , ( SELECT array_agg (cvi.description) AS array_agg FROM (clin.lnk_vaccine2inds clvi JOIN clin.vacc_indication cvi ON ( (clvi.fk_indication = cvi.id) ) ) WHERE (clvi.fk_vaccine = cv.pk) ) AS indications , ( SELECT array_agg (_ (cvi.description) ) AS array_agg FROM (clin.lnk_vaccine2inds clvi JOIN clin.vacc_indication cvi ON ( (clvi.fk_indication = cvi.id) ) ) WHERE (clvi.fk_vaccine = cv.pk) ) AS l10n_indications , rbd.external_code , rbd.external_code_type , ( SELECT array_agg (clvi.fk_indication) AS array_agg FROM (clin.lnk_vaccine2inds clvi JOIN clin.vacc_indication cvi ON ( (clvi.fk_indication = cvi.id) ) ) WHERE (clvi.fk_vaccine = cv.pk) ) AS pk_indications , cv.id_route AS pk_route , cv.fk_brand AS pk_brand , rbd.fk_data_source AS pk_data_source , cv.xmin AS xmin_vaccine FROM ( (clin.vaccine cv JOIN ref.branded_drug rbd ON ( (cv.fk_brand = rbd.pk) ) ) LEFT JOIN clin.vacc_route cvr ON ( (cv.id_route = cvr.id) ) );
F-Key | Name | Type | Description |
---|---|---|---|
list_position | integer | ||
waiting_zone | text | ||
urgency | integer | ||
title | text | ||
firstnames | text | ||
lastnames | text | ||
preferred_name | text | ||
dob | timestamp with time zone | ||
gender | text | ||
l10n_gender | text | ||
comment_identity | text | ||
registered | timestamp with time zone | ||
waiting_time | interval | ||
waiting_time_formatted | text | ||
comment | text | ||
pk_identity | integer | ||
pk_name | integer | ||
pk_waiting_list | integer |
SELECT c_wl.list_position , c_wl.area AS waiting_zone , c_wl.urgency , d_i.title , d_n.firstnames , d_n.lastnames , d_n.preferred AS preferred_name , d_i.dob , d_i.gender , _ (d_i.gender) AS l10n_gender , d_i.comment AS comment_identity , c_wl.registered , ( SELECT (now () - c_wl.registered ) ) AS waiting_time , ( SELECT to_char (age (now () , c_wl.registered ) ,'DDD HH24:MI'::text ) AS to_char ) AS waiting_time_formatted , c_wl.comment , d_i.pk AS pk_identity , d_n.id AS pk_name , c_wl.pk AS pk_waiting_list FROM clin.waiting_list c_wl , dem.identity d_i , dem.names d_n WHERE ( (c_wl.fk_patient = d_i.pk) AND (c_wl.fk_patient = d_n.id_identity) AND (d_i.deceased IS NULL) AND (d_n.active IS TRUE) );
definition of indications for vaccinations
F-Key | Name | Type | Description |
---|---|---|---|
pk_audit | integer | NOT NULL DEFAULT nextval('audit.audit_fields_pk_audit_seq'::regclass) | |
row_version | integer | NOT NULL | |
modified_when | timestamp with time zone | NOT NULL DEFAULT now() | |
modified_by | name | NOT NULL DEFAULT "current_user"() | |
id | serial | PRIMARY KEY | |
description | text |
UNIQUE
NOT NULL
description of indication, eg "Measles", note that this does not have to be a scientific diagnosis, it is simply intended to be an agreed-upon, medically-comprehensible unique identifier for the indication |
|
atcs_single_indication | text[] |
The ATC codes for single-indication vaccines for this indication. |
|
atcs_combi_indication | text[] |
The ATC codes for poly-indication vaccines including this indication. |
Name | Constraint |
---|---|
audit_audit_fields_sane_modified_when | CHECK (((modified_when <= clock_timestamp()) IS TRUE)) |
vacc_indication_sane_combi_atcs | CHECK (((atcs_combi_indication IS NULL) OR (array_upper(atcs_combi_indication, 1) > 0))) |
vacc_indication_sane_single_atcs | CHECK (((atcs_single_indication IS NULL) OR (array_upper(atcs_single_indication, 1) > 0))) |
definition of route via which vaccine is given, currently i.m. and p.o. only but may include "via genetically engineered food" etc in the future
F-Key | Name | Type | Description |
---|---|---|---|
pk_audit | integer | NOT NULL DEFAULT nextval('audit.audit_fields_pk_audit_seq'::regclass) | |
row_version | integer | NOT NULL | |
modified_when | timestamp with time zone | NOT NULL DEFAULT now() | |
modified_by | name | NOT NULL DEFAULT "current_user"() | |
id | serial | PRIMARY KEY | |
abbreviation | text | UNIQUE NOT NULL | |
description | text | UNIQUE NOT NULL |
Name | Constraint |
---|---|
audit_audit_fields_sane_modified_when | CHECK (((modified_when <= clock_timestamp()) IS TRUE)) |
Tables referencing this one via Foreign Key Constraints:
holds vaccinations actually given
F-Key | Name | Type | Description |
---|---|---|---|
pk_audit | integer | NOT NULL DEFAULT nextval('audit.audit_fields_pk_audit_seq'::regclass) | |
row_version | integer | NOT NULL | |
modified_when | timestamp with time zone | NOT NULL DEFAULT now() | |
modified_by | name | NOT NULL DEFAULT "current_user"() | |
pk_item | integer | NOT NULL DEFAULT nextval('clin.clin_root_item_pk_item_seq'::regclass) | |
clin_when | timestamp with time zone | NOT NULL DEFAULT now() | |
encounter.pk | fk_encounter | integer | NOT NULL |
episode.pk | fk_episode | integer | NOT NULL |
narrative | text |
Used to record a comment on this vaccination. |
|
soap_cat | text | DEFAULT 'p'::text | |
pk | serial | PRIMARY KEY | |
fk_provider | integer |
Who administered this vaccination. |
|
vaccine.pk | fk_vaccine | integer | NOT NULL |
site | text |
The site of injection used in this vaccination. |
|
batch_no | text |
NOT NULL
The batch/lot number of the vaccine given. |
|
reaction | text |
Used to record reactions to this vaccination. |
Table vaccination Inherits clin_root_item,
Name | Constraint |
---|---|
audit_audit_fields_sane_modified_when | CHECK (((modified_when <= clock_timestamp()) IS TRUE)) |
clin_root_item_sane_soap_cat | CHECK (((soap_cat IS NULL) OR (lower(soap_cat) = ANY (ARRAY['s'::text, 'o'::text, 'a'::text, 'p'::text, 'u'::text])))) |
vaccination_sane_narrative | CHECK ((gm.is_null_or_non_empty_string(narrative) IS TRUE)) |
vaccination_sane_reaction | CHECK ((gm.is_null_or_non_empty_string(reaction) IS TRUE)) |
vaccination_sane_site | CHECK ((gm.is_null_or_non_empty_string(site) IS TRUE)) |
holds vaccination courses defined at a techno-medical level for a single indication and will in many cases represent a part of a "recommended multi-epitope schedule", note that one organization can indeed recommend several courses for one and the same indication - which then only differ in their constraints, PostgreSQL does not currently offer the best tools to enforce such constraints
F-Key | Name | Type | Description |
---|---|---|---|
pk_audit | integer | NOT NULL DEFAULT nextval('audit.audit_fields_pk_audit_seq'::regclass) | |
row_version | integer | NOT NULL | |
modified_when | timestamp with time zone | NOT NULL DEFAULT now() | |
modified_by | name | NOT NULL DEFAULT "current_user"() | |
pk | serial | PRIMARY KEY | |
fk_recommended_by | integer |
the source/organization which defined this course, can be used to differentiate several locale-dependant courses for the same indication and yet tell them apart |
|
vacc_indication.id | fk_indication | integer |
NOT NULL
vaccination indication this course is targeted at |
is_active | boolean |
NOT NULL
DEFAULT true
whether this course is active or not, if False: do not newly *start* patients on this course |
|
comment | text |
a free-text comment on this vaccination course |
Name | Constraint |
---|---|
audit_audit_fields_sane_modified_when | CHECK (((modified_when <= clock_timestamp()) IS TRUE)) |
Tables referencing this one via Foreign Key Constraints:
holds constraints which apply to a vaccination course
F-Key | Name | Type | Description |
---|---|---|---|
pk_audit | integer | NOT NULL DEFAULT nextval('audit.audit_fields_pk_audit_seq'::regclass) | |
row_version | integer | NOT NULL | |
modified_when | timestamp with time zone | NOT NULL DEFAULT now() | |
modified_by | name | NOT NULL DEFAULT "current_user"() | |
pk | serial | PRIMARY KEY | |
description | text |
UNIQUE
NOT NULL
description/label/name of the constraint |
Name | Constraint |
---|---|
audit_audit_fields_sane_modified_when | CHECK (((modified_when <= clock_timestamp()) IS TRUE)) |
Tables referencing this one via Foreign Key Constraints:
defines a given vaccination event for a particular course
F-Key | Name | Type | Description |
---|---|---|---|
pk_audit | integer | NOT NULL DEFAULT nextval('audit.audit_fields_pk_audit_seq'::regclass) | |
row_version | integer | NOT NULL | |
modified_when | timestamp with time zone | NOT NULL DEFAULT now() | |
modified_by | name | NOT NULL DEFAULT "current_user"() | |
id | serial | PRIMARY KEY | |
vaccination_course.pk | fk_course | integer |
UNIQUE#1
NOT NULL
course to which this event belongs |
is_booster | boolean |
NOT NULL
DEFAULT false
does this definition represent a booster, also set for quasi-booster courses such as Influenza |
|
seq_no | integer |
UNIQUE#1
sequence number for this vaccination event within a particular course, NULL if (is_booster == true) |
|
min_age_due | interval |
NOT NULL
minimum age at which this shot is due |
|
max_age_due | interval |
NOT NULL
DEFAULT '01:32:35'::interval
maximum age at which this shot is due, if max_age_due = "5555 years": no maximum age |
|
min_interval | interval |
if (is_booster == true): recommended interval for boostering id (is_booster == false): minimum interval after previous vaccination, NULL if seq_no == 1 |
|
comment | text |
Name | Constraint |
---|---|
audit_audit_fields_sane_modified_when | CHECK (((modified_when <= clock_timestamp()) IS TRUE)) |
numbered_shot_xor_booster | CHECK ((((is_booster IS TRUE) AND (seq_no IS NULL)) OR ((is_booster IS FALSE) AND (seq_no > 0)))) |
sensible_min_interval | CHECK ((((min_interval IS NULL) AND (seq_no = 1)) OR ((min_interval IS NOT NULL) AND (min_interval > '00:00:00'::interval) AND (is_booster IS TRUE)) OR ((min_interval IS NOT NULL) AND (min_interval > '00:00:00'::interval) AND (seq_no > 1)))) |
vaccination_definition_check | CHECK ((((max_age_due >= min_age_due) AND (max_age_due <= '150 years'::interval)) OR (max_age_due = '01:32:35'::interval))) |
vaccination_definition_min_age_due_check | CHECK (((min_age_due >= '00:00:01'::interval) AND (min_age_due <= '150 years'::interval))) |
This table holds schedules as recommended by some authority such as a Vaccination Council. There will be numerous schedules depending on locale, constraints, age group etc. These schedules may be single or multi-epitope depending on their definition. A schedule acts as a convenient handle aggregating possibly several vaccination courses under a common name.
F-Key | Name | Type | Description |
---|---|---|---|
pk_audit | integer | NOT NULL DEFAULT nextval('audit.audit_fields_pk_audit_seq'::regclass) | |
row_version | integer | NOT NULL | |
modified_when | timestamp with time zone | NOT NULL DEFAULT now() | |
modified_by | name | NOT NULL DEFAULT "current_user"() | |
pk | serial | PRIMARY KEY | |
name | text |
UNIQUE
NOT NULL
name of the schedule as defined by some authority |
|
comment | text |
Name | Constraint |
---|---|
audit_audit_fields_sane_modified_when | CHECK (((modified_when <= clock_timestamp()) IS TRUE)) |
Tables referencing this one via Foreign Key Constraints:
definition of a vaccine as available on the market
F-Key | Name | Type | Description |
---|---|---|---|
pk_audit | integer | NOT NULL DEFAULT nextval('audit.audit_fields_pk_audit_seq'::regclass) | |
row_version | integer | NOT NULL | |
modified_when | timestamp with time zone | NOT NULL DEFAULT now() | |
modified_by | name | NOT NULL DEFAULT "current_user"() | |
pk | serial | PRIMARY KEY | |
vacc_route.id | id_route | integer |
route this vaccine is given |
is_live | boolean |
whether this is a live vaccine |
|
min_age | interval |
minimum age this vaccine is licensed for according to the information by the manufacturer |
|
max_age | interval |
maximum age this vaccine is licensed for according to the information by the manufacturer, use "5555 years" to indicate "no maximum age" |
|
comment | text | ||
fk_brand | integer |
UNIQUE
NOT NULL
The brand of this vaccine, can be a fake entry in ref.branded_drug. |
Name | Constraint |
---|---|
audit_audit_fields_sane_modified_when | CHECK (((modified_when <= clock_timestamp()) IS TRUE)) |
vaccine_sane_max_age | CHECK (((max_age IS NULL) OR (max_age < '150 years'::interval))) |
vaccine_sane_min_age | CHECK (((min_age IS NULL) OR (((max_age IS NULL) AND (min_age < '150 years'::interval)) OR ((max_age IS NOT NULL) AND (min_age <= max_age))))) |
Tables referencing this one via Foreign Key Constraints:
F-Key | Name | Type | Description |
---|---|---|---|
pk_audit | integer | NOT NULL DEFAULT nextval('audit.audit_fields_pk_audit_seq'::regclass) | |
row_version | integer | NOT NULL | |
modified_when | timestamp with time zone | NOT NULL DEFAULT now() | |
modified_by | name | NOT NULL DEFAULT "current_user"() | |
pk | serial | PRIMARY KEY | |
vaccine.pk | fk_vaccine | integer | NOT NULL |
batch_no | text |
UNIQUE
NOT NULL
serial # of a batch of a given vaccine that is awaiting usage in the fridge |
Name | Constraint |
---|---|
audit_audit_fields_sane_modified_when | CHECK (((modified_when <= clock_timestamp()) IS TRUE)) |
aggregates all the patients currently waiting for an encounter
F-Key | Name | Type | Description |
---|---|---|---|
pk_audit | integer | NOT NULL DEFAULT nextval('audit.audit_fields_pk_audit_seq'::regclass) | |
row_version | integer | NOT NULL | |
modified_when | timestamp with time zone | NOT NULL DEFAULT now() | |
modified_by | name | NOT NULL DEFAULT "current_user"() | |
pk | serial | PRIMARY KEY | |
fk_patient | integer |
NOT NULL
the waiting patient |
|
registered | timestamp with time zone |
NOT NULL
DEFAULT now()
when did the patient arrive (enter the waiting list, that is) |
|
urgency | integer |
NOT NULL
relative urgency, used by practices as they see fit, 0 - "standard" urgency < 0 - less urgent > 0 - more urgent |
|
list_position | integer |
UNIQUE
NOT NULL
the currently assigned position of this patient on the waiting list |
|
comment | text |
a free comment regarding this entry, NOT THE RFE ! |
|
area | text |
an arbitrary value by which filtering waiting patients into zones becomes possible |
Name | Constraint |
---|---|
audit_audit_fields_sane_modified_when | CHECK (((modified_when <= clock_timestamp()) IS TRUE)) |
non_empty_area | CHECK ((btrim(area) <> ''::text)) |
waiting_list_list_position_check | CHECK ((list_position > 0)) |
DECLARE _raw_query ALIAS FOR $1; _pk_identity ALIAS FOR $2; _query text; _recommendation text; BEGIN IF _raw_query IS NULL THEN RETURN NULL::text; END IF; _query := replace(_raw_query, 'ID_ACTIVE_PATIENT', _pk_identity::text); BEGIN EXECUTE _query INTO STRICT _recommendation; EXCEPTION --WHEN insufficient_privilege THEN RAISE WARNING 'auto hint recommendation query failed: %', _query; WHEN others THEN RAISE WARNING 'auto hint recommendation query failed: %', _query; -- only available starting with PG 9.2: --GET STACKED DIAGNOSTICS -- _exc_state = RETURNED_SQLSTATE, -- _exc_msg = MESSAGE_TEXT, -- _exc_detail = PG_EXCEPTION_DETAIL, -- _exc_hint = PG_EXCEPTION_HINT, -- _exc_context = PG_EXCEPTION_CONTEXT; --RAISE WARNING 'SQL STATE: %', _exc_state; --RAISE WARNING 'MESSAGE: %', _exc_msg; --RAISE WARNING 'DETAIL: %', _exc_detail; --RAISE WARNING 'HINT: %', _exc_hint; --RAISE WARNING 'CONTEXT: %', _exc_context; -- workaround for 9.1: RAISE WARNING 'SQL STATE: %', SQLSTATE; RAISE WARNING 'MESSAGE: %', SQLERRM; _recommendation := 'ERROR running hint recommendation query [' || _query || ']'; END; RETURN _recommendation; END;
declare _term alias for $1; _code alias for $2; _system alias for $3; _tmp text; begin select into _tmp 1 from clin.coded_phrase where term = _term and code = _code and xfk_coding_system = _system; if found then return True; end if; insert into clin.coded_phrase (term, code, xfk_coding_system) values (_term, _code, _system); return True; end;
DECLARE msg text; BEGIN -- do not worry about booster deletes if OLD.is_booster then return null; end if; -- any non-booster rows left ? perform 1 from clin.vaccination_definition where fk_course = OLD.fk_course and seq_no is not null; if FOUND then return null; end if; -- *any* rows left ? perform 1 from clin.vaccination_definition where fk_course = OLD.fk_course; if not FOUND then -- no problem return null; end if; -- any remaining rows can only be booster rows - which is a problem msg := 'Cannot delete last non-booster vacc def [' || OLD.pk || '] from course [' || OLD.fk_course || ']. There would be only booster definitions left.'; raise exception '%', msg; return null; END;
declare _pk_staff integer; begin select pk into _pk_staff from dem.staff where db_user = current_user; return _pk_staff; end;
BEGIN -- do not worry about non-booster inserts if NEW.is_booster is false then return NEW; end if; -- only insert booster def if non-booster def exists perform 1 from clin.vaccination_definition where fk_course = NEW.fk_course and seq_no is not null; if FOUND then return NEW; end if; raise exception 'Cannot define booster shot for course [%]. There is no base immunization definition.', NEW.fk_course; return null; END;
protect from direct inserts/deletes which the inheritance system cannot handle properly
begin raise exception 'INSERT/DELETE on <clin_root_item> not allowed.'; return False; end;
DECLARE msg text; BEGIN -- do not worry about non-booster updates if NEW.is_booster is false then return null; end if; -- after update to booster still non-booster def available ? perform 1 from clin.vaccination_definition where fk_course = NEW.fk_course and seq_no is not null; if FOUND then return null; end if; msg := 'Cannot set vacc def [' || NEW.pk || '] to booster for course [' || NEW.fk_course || ']. There would be no base immunization definition left.'; raise exception '%', msg; return null; END;
select date_trunc('day'::text, d_i.dob) + COALESCE(d_i.tob, d_i.dob::time without time zone)::interval AS dob from dem.identity d_i where d_i.pk = $1;
select deceased from dem.identity d_i where d_i.pk = $1;
DECLARE _hint ref.v_auto_hints%rowtype; _query text; _suppression_exists boolean; -- does not mean that the suppression applies _md5_at_suppression text; _old_rationale4suppression text; _hint_currently_applies boolean; -- regardless of whether suppressed or not _hint_recommendation text; _title text; -- _exc_state text; -- _exc_msg text; -- _exc_detail text; -- _exc_hint text; -- _exc_context text; BEGIN -- loop over all defined hints FOR _hint IN SELECT * FROM ref.v_auto_hints WHERE is_active LOOP --raise NOTICE 'checking hint for patient %: %', _pk_identity, _hint.title; -- is the hint suppressed ? SELECT (clin.hint_suppression_exists(_pk_identity, _hint.pk_auto_hint)).* INTO STRICT _suppression_exists, _md5_at_suppression, _old_rationale4suppression; -- does the hint currently apply ? (regardless of whether it is suppressed) _query := replace(_hint.query, 'ID_ACTIVE_PATIENT', _pk_identity::text); _query := replace(_query, 'clin.v_emr_journal', 'staging.journal_without_suppressed_hints'); SELECT (clin.run_hint_query(_hint.title, _query)).* INTO STRICT _hint_currently_applies, _title; -- error ? IF _hint_currently_applies IS NULL THEN --raise NOTICE ' error -> return'; _hint.title := _title; _hint.hint := _query; RETURN NEXT _hint; -- process next hint CONTINUE; END IF; -- hint does not apply IF _hint_currently_applies IS FALSE THEN -- does a (previously stored) suppression exist ? IF _suppression_exists IS FALSE THEN -- no, so skip this hint --raise NOTICE ' does not apply -> skip'; CONTINUE; END IF; --raise NOTICE ' does not apply but suppression invalid -> return for invalidation'; -- hint suppressed but does NOT apply: -- skip hint but invalidate suppression, because: -- * previously the hint must have applied and the user suppressed it, -- * then patient data (or hint definition) changed such that -- the hint does not apply anymore (but the suppression is -- still valid), -- * when patient data changes again, the hint might apply again -- * HOWEVER - since the suppression would still be valid - the -- hint would magically get suppressed again (which is -- medically unsafe) ... -- after invalidation, the hint will no longer be suppressed, -- however - since it does not currently apply - it will -- still not be returned and shown until it applies again ... -- -- ----------------------------------------------------------------------- -- UNFORTUNATELY, the following is currently not _possible_ because -- we are running inside a READONLY transaction (due to inherent -- security risks when running arbitrary user queries [IOW the hint -- SQL] against the database) and we cannot execute a -- sub-transaction as READWRITE :-/ -- --UPDATE clin.suppressed_hint --SET md5_sum = 'invalidated'::text -- will not ever match any md5 sum --WHERE -- fk_encounter IN ( -- SELECT pk FROM clin.encounter WHERE fk_patient = _pk_identity -- ) -- AND -- fk_hint = _hint.pk_auto_hint; -- ----------------------------------------------------------------------- -- -- hence our our workaround is to, indeed, return the hint but -- tag it with a magic rationale, by means of which the client -- can detect it to be in need of invalidation: _hint.title := 'HINT DOES NOT APPLY BUT NEEDS INVALIDATION OF EXISTING SUPPRESSION [' || _hint.title || '].'; _hint.rationale4suppression := 'magic_tag::does_not_apply::suppression_needs_invalidation'; RETURN NEXT _hint; CONTINUE; END IF; --raise NOTICE ' applies'; -- but is there a suppression ? IF _suppression_exists IS FALSE THEN --raise NOTICE ' return'; -- no: retrieve recommendation SELECT clin._get_recommendation_for_patient_hint(_hint.recommendation_query, _pk_identity) INTO STRICT _hint_recommendation; _hint.recommendation := _hint_recommendation; -- return hint RETURN NEXT _hint; CONTINUE; END IF; -- yes, is suppressed --raise NOTICE ' is suppressed'; -- is the suppression still valid ? -- -> yes, suppression valid IF _md5_at_suppression = _hint.md5_sum THEN --raise NOTICE '-> suppression valid, ignoring hint'; -- hint applies, suppressed, suppression valid: skip this hint CONTINUE; END IF; -- -> no, suppression not valid -- hint definition must have changed so ignore the suppression but -- provide previous rationale for suppression to the user _hint.rationale4suppression := _old_rationale4suppression; -- retrieve recommendation SELECT clin._get_recommendation_for_patient_hint(_hint.recommendation_query, _pk_identity) INTO STRICT _hint_recommendation; _hint.recommendation := _hint_recommendation; RETURN NEXT _hint; CONTINUE; END LOOP; RETURN; END;
--DECLARE -- _md5_suppressed text; -- _old_rationale4suppression text; BEGIN SELECT md5_sum, rationale INTO _o_md5, _o_rationale FROM clin.suppressed_hint WHERE fk_hint = _pk_hint AND fk_encounter IN ( SELECT pk FROM clin.encounter WHERE fk_patient = _pk_identity ); IF FOUND THEN _o_exists := TRUE; ELSE _o_exists := FALSE; END IF; END;
Move row with logical position $1 into logical position $2. If another row exists with position $2 it will be moved to position $1 in the process. Fails if there is no row with position $1.
DECLARE _wl_pos_src alias for $1; _wl_pos_dest alias for $2; _tmp_pos integer; _curr_max_pos integer; BEGIN if _wl_pos_src = _wl_pos_dest then return true; end if; if _wl_pos_dest < 1 then raise notice 'clin.move_waiting_list_entry(): Will not move entry [%] before start of list [%].', _wl_pos_src, _wl_pos_dest; return False; end if; select max(list_position) into _curr_max_pos from clin.waiting_list; -- do not move last entry further down if _wl_pos_src = _curr_max_pos then if _wl_pos_dest > _wl_pos_src then raise notice 'clin.move_waiting_list_entry(): Will not move last entry [%] beyond end of list to [%].', _wl_pos_src, _wl_pos_dest; return False; end if; end if; -- does the source row exist ? perform 1 from clin.waiting_list where list_position = _wl_pos_src; if not found then raise notice 'clin.move_waiting_list_entry(): Cannot move entry [%] to [%]. Entry does not exist.', _wl_pos_src, wl_pos_dest ; return false; end if; -- load destination row perform 1 from clin.waiting_list where list_position = _wl_pos_dest; -- does not exist if not found then -- do not move entry beyond end of list more than necessary if _wl_pos_dest > (_curr_max_pos + 1) then _tmp_pos := _curr_max_pos + 1; else _tmp_pos := _wl_pos_dest; end if; -- so update row to move and be done with it update clin.waiting_list set list_position = _tmp_pos where list_position = _wl_pos_src; return true; end if; -- move existing row out of the way select (max(list_position) + _wl_pos_dest + _wl_pos_src) into _tmp_pos from clin.waiting_list; update clin.waiting_list set list_position = _tmp_pos where list_position = _wl_pos_dest; -- move row to move update clin.waiting_list set list_position = _wl_pos_dest where list_position = _wl_pos_src; -- move back existing row update clin.waiting_list set list_position = _wl_pos_src where list_position = _tmp_pos; return true; END;
Remove empty encounters older than 1 week from a patient.
select clin.remove_old_empty_encounters($1, '1 week'::interval);
Remove empty encountersolder than a definable minimum age from a patient.
DECLARE _pk_identity alias for $1; _defined_minimum_encounter_age alias for $2; _usable_minimum_encounter_age interval; _encounter_count integer; BEGIN -- does person exist ? perform 1 from dem.identity where pk = _pk_identity; if not found then raise exception 'clin.remove_old_empty_encounters(person=%, min_age=%): person [%] does not exist', _pk_identity, _defined_minimum_encounter_age, _pk_identity; return false; end if; SELECT count(1) INTO STRICT _encounter_count FROM clin.encounter WHERE fk_patient = _pk_identity; IF _encounter_count < 2 THEN raise exception 'clin.remove_old_empty_encounters(person=%, min_age=%): there are less than 2 encounters for this patient', _pk_identity, _defined_minimum_encounter_age; return false; END IF; if _defined_minimum_encounter_age < '3 days'::interval then _usable_minimum_encounter_age := '3 days'::interval; else _usable_minimum_encounter_age := _defined_minimum_encounter_age; end if; DELETE FROM clin.encounter WHERE clin.encounter.fk_patient = _pk_identity AND age(clin.encounter.last_affirmed) > _usable_minimum_encounter_age AND NOT EXISTS (SELECT 1 FROM clin.clin_root_item WHERE fk_encounter = clin.encounter.pk) AND NOT EXISTS (SELECT 1 FROM blobs.doc_med WHERE fk_encounter = clin.encounter.pk) AND NOT EXISTS (SELECT 1 FROM clin.episode WHERE fk_encounter = clin.encounter.pk) AND NOT EXISTS (SELECT 1 FROM clin.health_issue WHERE fk_encounter = clin.encounter.pk) AND NOT EXISTS (SELECT 1 FROM clin.allergy_state WHERE fk_encounter = clin.encounter.pk) AND NOT EXISTS (SELECT 1 FROM bill.bill_item WHERE fk_encounter = clin.encounter.pk) AND NOT EXISTS (SELECT 1 FROM clin.external_care WHERE fk_encounter = clin.encounter.pk) AND NOT EXISTS (SELECT 1 FROM clin.suppressed_hint WHERE fk_encounter = clin.encounter.pk) ; return true; END;
BEGIN BEGIN EXECUTE _query INTO STRICT _o_applies; EXCEPTION --WHEN insufficient_privilege THEN RAISE WARNING 'auto hint query failed: %', _query; WHEN others THEN RAISE WARNING 'auto hint query failed: %', _query; -- only available starting with PG 9.2: --GET STACKED DIAGNOSTICS -- _exc_state = RETURNED_SQLSTATE, -- _exc_msg = MESSAGE_TEXT, -- _exc_detail = PG_EXCEPTION_DETAIL, -- _exc_hint = PG_EXCEPTION_HINT, -- _exc_context = PG_EXCEPTION_CONTEXT; --RAISE WARNING 'SQL STATE: %', _exc_state; --RAISE WARNING 'MESSAGE: %', _exc_msg; --RAISE WARNING 'DETAIL: %', _exc_detail; --RAISE WARNING 'HINT: %', _exc_hint; --RAISE WARNING 'CONTEXT: %', _exc_context; -- workaround for 9.1: RAISE WARNING 'SQL STATE: %', SQLSTATE; RAISE WARNING 'MESSAGE: %', SQLERRM; _o_applies := NULL; _o_title := ('ERROR checking for [' || _title || '] !')::TEXT; RETURN; END; _o_title := _title; END;
begin if TG_OP = 'UPDATE' then if OLD.is_open is TRUE then return NEW; end if; end if; update clin.health_issue set is_active = TRUE where pk = NEW.fk_health_issue AND is_active is FALSE ; return NEW; end;
begin execute 'notify "active_substance_mod_db:"'; return NULL; end;
begin execute 'notify "consumed_substance_mod_db:"'; return NULL; end;
DECLARE _issue_count integer; BEGIN SELECT COUNT(1) INTO STRICT _issue_count FROM clin.external_care WHERE issue = NEW.issue AND fk_org_unit = NEW.fk_org_unit AND fk_encounter IN ( SELECT pk FROM clin.encounter WHERE fk_patient = ( SELECT fk_patient FROM clin.encounter WHERE pk = NEW.fk_encounter ) ) ; IF _issue_count > 1 THEN RAISE EXCEPTION '% into clin.external_care: Sanity check failed. Cannot insert issue [%] more than once for patient of encounter [%] at org unit [%].', TG_OP, NEW.issue, NEW.fk_encounter, NEW.fk_org_unit USING ERRCODE = 'check_violation'; return NULL; END IF; RETURN NEW; END;
Document the deletion of a substance intake.
DECLARE _row record; _pk_episode integer; BEGIN select * into _row from clin.v_substance_intake_journal where src_pk = OLD.pk; _pk_episode := _row.pk_episode; -- create episode if needed if _pk_episode is null then select pk into _pk_episode from clin.episode where description = _('Medication history') and fk_encounter in ( select pk from clin.encounter where fk_patient = _row.pk_patient ); if not found then insert into clin.episode ( description, is_open, fk_encounter ) values ( _('Medication history'), FALSE, OLD.fk_encounter ) returning pk into _pk_episode; end if; end if; insert into clin.clin_narrative ( fk_encounter, fk_episode, soap_cat, narrative ) values ( _row.pk_encounter, _pk_episode, NULL, _('Deletion of') || ' ' || _row.narrative ); return OLD; END;
If a patient is stopped from a multi-component drug intake other components thereof must be turned into non-brand substance intakes.
DECLARE _pk_brand integer; _component_count integer; _pk_patient integer; BEGIN -- did it at all relate to a drug (rather than substance) ? if OLD.fk_drug_component is NULL then return NULL; end if; -- which drug ? select fk_brand into _pk_brand from ref.lnk_substance2brand where pk = OLD.fk_drug_component; -- how many components therein ? select count(1) into _component_count from ref.lnk_substance2brand where fk_brand = _pk_brand; -- only one component anyways ? (which then has been deleted already) if _component_count = 1 then return NULL; end if; -- which patient ? select fk_patient into _pk_patient from clin.encounter where pk = OLD.fk_encounter; -- delete those components which cannot be converted: delete from clin.substance_intake c_si1 where -- entries which belong to the brand in question c_si1.fk_drug_component in ( select pk from ref.lnk_substance2brand where fk_brand = _pk_brand ) and -- entries for this one patient only (via proxy of encounter) c_si1.fk_encounter in ( select pk from clin.encounter where fk_patient = _pk_patient ) and -- which already exist exists ( select 1 from clin.substance_intake c_si2 where -- as substance-only links c_si2.fk_substance = ( select fk_substance from ref.lnk_substance2brand where pk = c_si1.fk_drug_component ) and -- for this very patient c_si2.fk_encounter in ( select pk from clin.encounter where fk_patient = _pk_patient ) ) ; -- relink all other intakes into substances update clin.substance_intake c_si set fk_drug_component = null, fk_substance = ( select fk_substance from ref.lnk_substance2brand where pk = c_si.fk_drug_component ), preparation = ( select r_bd.preparation from ref.branded_drug r_bd where r_bd.pk = _pk_brand ) where -- ... which belong to the brand in question c_si.fk_drug_component in ( select pk from ref.lnk_substance2brand where fk_brand = _pk_brand ) and -- ... which belong to this one patient (via proxy of encounter) c_si.fk_encounter in ( select pk from clin.encounter where fk_patient = _pk_patient ) ; return NULL; END;
declare _new_pk_patient integer; _old_pk_patient integer; begin -- find patient from encounter select into _new_pk_patient fk_patient from clin.encounter where pk = NEW.fk_encounter; if not FOUND then raise exception 'Encounter % does not exist !?', NEW.fk_encounter; return NEW; end if; -- new row if TG_OP = 'INSERT' then -- patient already there ? perform 1 from clin.allergy_state where fk_encounter in (select pk from clin.encounter where fk_patient = _new_pk_patient); if FOUND then raise exception 'Cannot insert second allergy state for patient % via encounter %.', _new_pk_patient, NEW.fk_encounter; return NEW; end if; return NEW; end if; if TG_OP = 'UPDATE' then if NEW.fk_encounter = OLD.fk_encounter then return NEW; end if; select into _old_pk_patient fk_patient from clin.encounter where pk = OLD.fk_encounter; if _new_pk_patient = _old_pk_patient then return NEW; end if; raise exception 'Invalid fk_encounter update (% -> %): it would change the associated patient (% -> %).', OLD.fk_encounter, NEW.fk_encounter, _old_pk_patient, _new_pk_patient; return NEW; end if; return NEW; end;
On INSERT of a substance intake set fk_substance from fk_drug_component if the latter is NOT NULL.
BEGIN -- load fk_substance from drug_component table select r_ls2b.fk_substance into strict NEW.fk_substance from ref.lnk_substance2brand r_ls2b where r_ls2b.pk = NEW.fk_drug_component ; return NEW; END;
Check foreign key integrity on insert to *.fk_generic_code -> ref.coding_system_root.pk_coding_system.
DECLARE _msg text; BEGIN perform 1 from ref.coding_system_root where pk_coding_system = NEW.fk_generic_code; if FOUND then return NEW; end if; _msg := 'clin.trf_ins_lc2sth_fk_generic_code(): INSERT into ' || TG_TABLE_SCHEMA || '.' || TG_TABLE_NAME || ': ' || 'fk_generic_code=(' || NEW.fk_generic_code || ') ' || 'does not exist in ref.coding_system_root.pk_coding_system'; raise foreign_key_violation using message = _msg; return NEW; END;
Prevent patient from being put on a particular substance more than once.
DECLARE _pk_patient integer; _link_count integer; _msg text; BEGIN -- which patient ? select fk_patient into _pk_patient from clin.encounter where pk = NEW.fk_encounter; -- more than one link ? select count(1) into _link_count from clin.substance_intake where -- for this substance fk_substance = NEW.fk_substance and -- either already linked as component OR -- already linked as substance fk_drug_component IS NOT DISTINCT FROM NEW.fk_drug_component and -- in this one patient fk_encounter in ( select pk from clin.encounter where fk_patient = _pk_patient ) ; if _link_count > 1 then _msg := '[clin.trf_ins_upd_intake_prevent_duplicate_substance_links]: substance ref.consumable_substance.pk=(' || NEW.fk_substance || ') ' || 'already linked to patient=(' || _pk_patient || ') '; raise exception unique_violation using message = _msg; end if; return NEW; END;
Explicit foreign key-like check.
DECLARE _msg text; _func text; _pk integer; BEGIN -- is NULLable if NEW.fk_test_types is NULL then return NEW; end if; _func := '[clin.trf_ins_upd_validate_test_type_pks]: '; -- must be one-dimensional IF array_ndims(NEW.fk_test_types) <> 1 THEN _msg := _func || 'fk_test_types is not a one-dimensional array' || array_to_string(NEW.fk_test_types, '/', '<NULL>'); raise exception foreign_key_violation using message = _msg; END IF; -- must not be empty IF array_length(NEW.fk_test_types, 1) = 0 THEN _msg := _func || 'fk_test_types must not be empty (perhaps you wanted <NULL> instead ?)'; raise exception foreign_key_violation using message = _msg; END IF; -- must not *contain* NULLs FOR _pk IN SELECT unnest(NEW.fk_test_types) LOOP perform 1 from clin.test_type where pk = _pk; if not found then _msg := _func || 'fk_test_types element (' || coalesce(_pk::text, '<NULL>') || ') not found in clin.test_type.pk column'; raise exception foreign_key_violation using message = _msg; end if; END LOOP; return NEW; END;
If a patient is put on a multi-component drug they must be put on ALL components thereof.
DECLARE _component_count integer; _pk_patient integer; _pk_brand integer; _pk_component integer; BEGIN -- any drug at all ? if NEW.fk_drug_component is NULL then return NEW; end if; -- get the brand we are linking to select fk_brand into _pk_brand from ref.lnk_substance2brand where pk = NEW.fk_drug_component; -- how many components therein ? select count(1) into _component_count from ref.lnk_substance2brand where fk_brand = _pk_brand; -- only one component ? if _component_count = 1 then return NEW; end if; -- which patient ? select fk_patient into _pk_patient from clin.encounter where pk = NEW.fk_encounter; -- INSERT all components for _pk_component in select pk from ref.lnk_substance2brand where fk_brand = _pk_brand loop -- already there ? perform 1 from clin.substance_intake where fk_encounter in ( select pk from clin.encounter where fk_patient = _pk_patient ) and fk_drug_component = _pk_component ; if FOUND then continue; end if; -- insert insert into clin.substance_intake ( fk_drug_component, -- differentiate clin_when, -- harmonize (started) fk_encounter, -- harmonize fk_episode, -- required soap_cat, -- harmonize schedule, -- harmonize duration, -- harmonize intake_is_approved_of, -- harmonize is_long_term, -- harmonize discontinued, -- harmonize narrative, -- preparation, -- drug components already have preps aim, discontinue_reason ) values ( _pk_component, NEW.clin_when, NEW.fk_encounter, NEW.fk_episode, NEW.soap_cat, NEW.schedule, NEW.duration, NEW.intake_is_approved_of, NEW.is_long_term, NEW.discontinued, NEW.narrative, -- NEW.preparation, NEW.aim, NEW.discontinue_reason ); end loop; return NEW; END;
Prevent patient from being put on a particular component twice.
DECLARE _pk_patient integer; _pk_intake integer; _msg text; BEGIN -- any drug at all ? if NEW.fk_drug_component is NULL then return NEW; end if; -- which patient ? select fk_patient into _pk_patient from clin.encounter where pk = NEW.fk_encounter; -- already exists ? select pk into _pk_intake from clin.substance_intake where fk_encounter in ( select pk from clin.encounter where fk_patient = _pk_patient ) and fk_drug_component = NEW.fk_drug_component ; if FOUND then _msg := '[clin.trf_insert_intake_prevent_duplicate_component_links]: drug component ref.lnk_substance2brand.pk=(' || NEW.fk_drug_component || ') ' || 'already linked to patient=(' || _pk_patient || ') ' || 'as clin.substance_intake.pk=(' || _pk_intake || ')'; raise exception unique_violation using message = _msg; end if; return NEW; END;
DECLARE is_modified bool; BEGIN is_modified := False; -- change of test type if NEW.fk_type != OLD.fk_type then is_modified := True; end if; -- change of numeric value if NEW.val_num != OLD.val_num then is_modified := True; end if; -- change of alpha value if NEW.val_alpha != OLD.val_alpha then is_modified := True; end if; -- change of unit if NEW.val_unit != OLD.val_unit then is_modified := True; end if; if is_modified is True then delete from clin.reviewed_test_results where fk_reviewed_row = OLD.pk; end if; return NEW; END;
BEGIN if NEW.clin_end > clock_timestamp() then NEW.is_ongoing := TRUE; else NEW.is_ongoing := FALSE; end if; return NEW; END;
declare _pk_patient integer; _pk_type integer; begin -- disallow change of referenced row -- for cleanliness this really *should* be in another trigger if NEW.fk_reviewed_row <> OLD.fk_reviewed_row then raise exception 'Attaching an existing review to another test result is not allowed (fk_reviewed_row change).'; return NEW; end if; -- change of last reviewer ? if NEW.fk_reviewer = OLD.fk_reviewer then return NEW; end if; -- review change ? if (NEW.is_technically_abnormal <> OLD.is_technically_abnormal) or (NEW.clinically_relevant <> OLD.clinically_relevant) then -- find patient for test result select pk_patient into _pk_patient from clin.v_test_results where pk_test_result = OLD.fk_reviewed_row; -- find inbox item type select pk_type into _pk_type from dem.v_inbox_item_type where type = 'results review change'; -- create it if necessary if not found then insert into dem.inbox_item_type ( fk_inbox_item_category, description ) values ( (select pk from dem.inbox_item_category where description = 'clinical'), 'results review change' ); select pk_type into _pk_type from dem.v_inbox_item_type where type = 'results review change'; end if; -- already notified ? perform 1 from dem.message_inbox where fk_staff = OLD.fk_reviewer and fk_inbox_item_type = _pk_type and ufk_context = ARRAY[_pk_patient]; -- nope, so notify now if not found then insert into dem.message_inbox ( fk_staff, fk_inbox_item_type, comment, ufk_context ) values ( OLD.fk_reviewer, _pk_type, (select _('results review changed for patient') || ' [' || vpb.lastnames || ', ' || vbp.firstnames || ']' from dem.v_basic_person vbp where vpb.pk_identity = _pk_patient ), ARRAY[_pk_patient] ); end if; end if; return NEW; end;
this function is used in triggers and checks whether foreign keys to clin.episode.pk and clin.encounter.pk on a single table ultimately point to the same patient
declare _fk_encounter_col text; _enc_pk integer; _fk_episode_col text; _epi_pk integer; _identity_from_encounter integer; _identity_from_episode integer; _cmd text; begin _fk_encounter_col := TG_ARGV[0]; _fk_episode_col := TG_ARGV[1]; _cmd := 'select $1.' || _fk_encounter_col; EXECUTE _cmd INTO STRICT _enc_pk USING NEW; select fk_patient into _identity_from_encounter from clin.encounter where pk = _enc_pk; -- raise notice '%: % -> %', _cmd, _enc_pk, _identity_from_encounter; _cmd := 'select $1.' || _fk_episode_col; EXECUTE _cmd INTO STRICT _epi_pk USING NEW; select fk_patient into _identity_from_episode from clin.encounter where pk = (select fk_encounter from clin.episode where pk = _epi_pk); -- raise notice '%: % -> %', _cmd, _epi_pk, _identity_from_episode; if _identity_from_encounter <> _identity_from_episode then raise exception '% into %.%: Sanity check failed. %=% -> patient=%. %=% -> patient=%.', TG_OP, TG_TABLE_SCHEMA, TG_TABLE_NAME, _fk_encounter_col, _enc_pk, _identity_from_encounter, _fk_episode_col, _epi_pk, _identity_from_episode ; return NULL; end if; return NEW; end;
declare _enc_pk integer; _epi_pk integer; _identity_from_encounter integer; _identity_from_issue integer; _cmd text; begin select fk_patient into _identity_from_encounter from clin.encounter where pk = NEW.fk_encounter; -- raise notice '%: % -> %', _cmd, _enc_pk, _identity_from_encounter; select fk_patient into _identity_from_issue from clin.encounter where pk = ( select fk_encounter from clin.health_issue where pk = NEW.fk_health_issue ); IF _identity_from_encounter <> _identity_from_issue THEN RAISE EXCEPTION '% into clin.external_care: Sanity check failed. fk_encounter=% -> patient=%. fk_health_issue=% -> patient=%.', TG_OP, NEW.fk_encounter, _identity_from_encounter, NEW.fk_health_issue, _identity_from_issue USING ERRCODE = 'check_violation' ; return NULL; END IF; return NEW; end;
declare _identity_from_encounter integer; _identity_from_issue integer; begin -- if issue is NULL, do not worry about mismatch if NEW.fk_health_issue is NULL then return NEW; end if; -- .fk_episode must belong to the same patient as .fk_encounter select fk_patient into _identity_from_encounter from clin.encounter where pk = NEW.fk_encounter; select fk_patient into _identity_from_issue from clin.encounter where pk = ( select fk_encounter from clin.health_issue where pk = NEW.fk_health_issue ); if _identity_from_encounter <> _identity_from_issue then raise exception 'INSERT/UPDATE into %.%: Sanity check failed. Encounter % patient = %. Issue % patient = %.', TG_TABLE_SCHEMA, TG_TABLE_NAME, NEW.fk_encounter, _identity_from_encounter, NEW.fk_health_issue, _identity_from_issue ; return NULL; end if; return NEW; end;
DECLARE _hospital_stay_episode_pk integer; BEGIN if NEW.fk_hospital_stay is null then return NEW; end if; select into _hospital_stay_episode_pk fk_episode from clin.hospital_stay where pk = NEW.fk_hospital_stay; if NEW.fk_episode = _hospital_stay_episode_pk then return NEW; end if; raise exception '[clin.procedure]: INSERT/UPDATE failed: fk_episode (%) does not match fk_episode (%) behind fk_hospital_stay (%)', NEW.fk_episode, _hospital_stay_episode_pk, NEW.fk_hospital_stay; return NEW; END;
DECLARE _suppression_count integer; BEGIN -- the count of suppressions for this hint in this patient SELECT COUNT(1) INTO STRICT _suppression_count FROM clin.suppressed_hint WHERE fk_hint = NEW.fk_hint AND fk_encounter IN ( SELECT pk FROM clin.encounter WHERE fk_patient = ( SELECT fk_patient FROM clin.encounter WHERE pk = NEW.fk_encounter ) ) ; IF _suppression_count > 1 THEN RAISE EXCEPTION '% into clin.suppressed_hint: Sanity check failed. Hint [%] suppressed more than once for patient of encounter [%].', TG_OP, NEW.pk, NEW.fk_encounter USING ERRCODE = 'check_violation'; return NULL; END IF; return NEW; END;
DECLARE _indication_link_pk integer; BEGIN perform 1 from clin.lnk_vaccine2inds where fk_vaccine = NEW.pk limit 1; if FOUND then return NEW; end if; raise exception '[clin.vaccine]: INSERT/UPDATE failed: no indication linked to vaccine (clin.lnk_vaccine2inds.fk_vaccine <-(%)-> clin.vaccine.pk)', NEW.pk; return NEW; END;
trigger function to sync the allergy state on insert/delete
DECLARE _fk_patient integer; _fk_encounter integer; _state integer; _no_of_allergies integer; BEGIN if TG_OP = 'INSERT' then select into _fk_patient fk_patient from clin.encounter where pk = NEW.fk_encounter; _fk_encounter := NEW.fk_encounter; _state := 1; end if; if TG_OP = 'DELETE' then -- only run this trigger if deleting last allergy select into _fk_patient fk_patient from clin.encounter where pk = OLD.fk_encounter; select into _no_of_allergies count(1) from clin.allergy where fk_encounter in ( select pk from clin.encounter where fk_patient = _fk_patient ); if _no_of_allergies > 1 then return OLD; -- still allergies left end if; _fk_encounter := OLD.fk_encounter; _state := 0; end if; update clin.allergy_state set has_allergy = _state, last_confirmed = coalesce(last_confirmed, now()) where fk_encounter in ( select pk from clin.encounter where fk_patient = _fk_patient ); if not FOUND then insert into clin.allergy_state (fk_encounter, has_allergy, last_confirmed) values (_fk_encounter, _state, now()); end if; return NEW; END;
declare _identity_from_encounter integer; _identity_from_issue integer; begin if NEW.discontinued is NULL then NEW.discontinue_reason := NULL; end if; return NEW; end;
DECLARE _msg text; BEGIN -- is the indication already linked ? perform 1 from clin.v_vaccination_courses_in_schedule where pk_vaccination_schedule = NEW.fk_schedule and pk_indication = (select fk_indication from clin.vaccination_course where pk=NEW.fk_course); if FOUND then _msg := 'Cannot link course [' || NEW.fk_course || '] into schedule [' || NEW.fk_schedule || ']. The indication is already linked.'; raise exception '%', _msg; return null; end if; return null; END;
On UPDATE of a substance intake set fk_substance from fk_drug_component if the latter changes.
BEGIN -- load fk_substance from drug_component table select r_ls2b.fk_substance into strict NEW.fk_substance from ref.lnk_substance2brand r_ls2b where r_ls2b.pk = NEW.fk_drug_component ; return NEW; END;
If a drug component substance intake is updated all sibling components must receive some values thereof.
DECLARE _pk_brand integer; _component_count integer; _pk_patient integer; BEGIN -- which drug ? select fk_brand into _pk_brand from ref.lnk_substance2brand where pk = NEW.fk_drug_component; -- how many components therein ? select count(1) into _component_count from ref.lnk_substance2brand where fk_brand = _pk_brand; -- only one component ? if _component_count = 1 then return NEW; end if; -- which patient ? select fk_patient into _pk_patient from clin.encounter where pk = NEW.fk_encounter; -- update all substance instake fields shared by drug components ... update clin.substance_intake set clin_when = NEW.clin_when, -- started fk_encounter = NEW.fk_encounter, soap_cat = NEW.soap_cat, schedule = NEW.schedule, duration = NEW.duration, intake_is_approved_of = NEW.intake_is_approved_of, is_long_term = NEW.is_long_term, discontinued = NEW.discontinued where -- ... which belong to this drug ... fk_drug_component in ( select pk from ref.lnk_substance2brand where fk_brand = _pk_brand ) AND -- ... but are not THIS component ... fk_drug_component != NEW.fk_drug_component AND -- ... this patient ... fk_encounter in ( select pk from clin.encounter where fk_patient = _pk_patient ) AND -- ... are different in value (this will stop recursion as soon as all are equal) ( clin_when is distinct from NEW.clin_when OR fk_encounter is distinct from NEW.fk_encounter OR soap_cat is distinct from NEW.soap_cat OR schedule is distinct from NEW.schedule OR duration is distinct from NEW.duration OR intake_is_approved_of is distinct from NEW.intake_is_approved_of OR is_long_term is distinct from NEW.is_long_term OR discontinued is distinct from NEW.discontinued ) ; return NEW; END;
Check foreign key integrity on update of *.fk_generic_code -> ref.coding_system_root.pk_coding_system.
DECLARE _msg text; BEGIN perform 1 from ref.coding_system_root where pk_coding_system = NEW.fk_generic_code; if FOUND then return NEW; end if; _msg := 'clin.trf_upd_lc2sth_fk_generic_code(): UPDATE of ' || TG_TABLE_SCHEMA || '.' || TG_TABLE_NAME || ': ' || 'fk_generic_code=(' || NEW.fk_generic_code || ') ' || 'does not exist in ref.coding_system_root.pk_coding_system, ' || 'old fk_generic_code=(' || OLD.fk_generic_code || ')'; raise foreign_key_violation using message = _msg; return OLD; END;
If a patient is put on a different multi-component drug ALL components thereof must be updated.
DECLARE _intake_count integer; _component_count integer; _pk_patient integer; _pk_brand integer; _msg text; BEGIN if NEW.fk_drug_component is not distinct from OLD.fk_drug_component then return NEW; end if; select fk_patient into _pk_patient from clin.encounter where pk = NEW.fk_encounter; -- check the OLD brand unless it is NULL if OLD.fk_drug_component is not NULL then -- get the brand we were linking to select fk_brand into _pk_brand from ref.lnk_substance2brand where fk_substance = OLD.fk_drug_component; -- How many substance intake links for this drug have we got ? select count(1) into _intake_count from clin.substance_intake where fk_drug_component in ( select fk_substance from ref.lnk_substance2brand where fk_brand = _pk_brand ) and fk_encounter in ( select pk from clin.encounter where fk_patient = _pk_patient ); -- unlinking completely would be fine but else: if _intake_count != 0 then -- How many components *are* there in the drug in question ? select count(1) into _component_count from ref.lnk_substance2brand where fk_brand = _pk_brand; -- substance intake link count and number of components must match if _component_count != _intake_count then _msg := '[clin.trf_update_intake_must_link_all_drug_components]: re-linking brand must unlink all components of old brand [' || _pk_brand || '] ' || '(component [' || OLD.fk_drug_component || ' -> ' || NEW.fk_drug_component || '])'; raise exception check_violation using message = _msg; end if; end if; end if; -- check the NEW brand unless it is NULL if NEW.fk_drug_component is not NULL then -- get the brand we were linking to select fk_brand into _pk_brand from ref.lnk_substance2brand where fk_substance = NEW.fk_drug_component; -- How many substance intake links for this drug have we got ? select count(1) into _intake_count from clin.substance_intake where fk_drug_component in ( select fk_substance from ref.lnk_substance2brand where fk_brand = _pk_brand ) and fk_encounter in ( select pk from clin.encounter where fk_patient = _pk_patient ); -- unlinking completely would be fine but else: if _intake_count != 0 then -- How many components *are* there in the drug in question ? select count(1) into _component_count from ref.lnk_substance2brand where fk_brand = _pk_brand; -- substance intake link count and number of components must match if _component_count != _intake_count then _msg := '[clin.trf_update_intake_must_link_all_drug_components]: re-linking brand must link all components of new brand [' || _pk_brand || '] ' || '(component [' || OLD.fk_drug_component || ' -> ' || NEW.fk_drug_component || '])'; raise exception check_violation using message = _msg; end if; end if; end if; return NEW; END;
Sends a notification to the inbox of both current_user and clin.vaccination.fk_provider (if not NULL) in case a new or updated vaccination falls within 1 week of another vaccination with (even partially) overlapping indications.
DECLARE _NEW_pk_patient integer; _NEW_vaccination record; _NEW_vacc_label text; _prev_vacc_loop_record record; _prev_vaccination record; _prev_vacc_label text; _indication_collision integer; msg text; _pk_current_provider integer; BEGIN -- find patient for NEW vaccination select fk_patient into _NEW_pk_patient from clin.encounter where pk = NEW.fk_encounter; -- load denormalized vaccination corresponding to NEW vaccination select * into _NEW_vaccination from clin.v_pat_vaccinations where pk_vaccination = NEW.pk; -- generate label for NEW vaccination _NEW_vacc_label := to_char(_NEW_vaccination.date_given, 'YYYY-MM-DD') || ' (#' || _NEW_vaccination.pk_vaccination || '): ' || _NEW_vaccination.vaccine || ' (' || array_to_string(_NEW_vaccination.l10n_indications, ', ') || ')'; -- loop over ... for _prev_vacc_loop_record in -- ... vaccinations ... SELECT * FROM clin.vaccination cv WHERE -- ... of this patient ... cv.fk_encounter in (select ce.pk from clin.encounter ce where ce.fk_patient = _NEW_pk_patient) AND -- ... within 7 days of the vaccination date ... cv.clin_when BETWEEN (NEW.clin_when - '7 days'::interval) AND (NEW.clin_when + '7 days'::interval) AND -- ... not the vaccination we just INSERTed/UPDATEed cv.pk != NEW.pk loop select * into _indication_collision from (( select fk_indication from clin.lnk_vaccine2inds where fk_vaccine = NEW.fk_vaccine ) INTERSECT ( select fk_indication from clin.lnk_vaccine2inds where fk_vaccine = _prev_vacc_loop_record.fk_vaccine )) as colliding_indications; if FOUND then -- retrieve denormalized data corresponding to that previous vaccination select * into _prev_vaccination from clin.v_pat_vaccinations where pk_vaccination = _prev_vacc_loop_record.pk; -- generate label for that previous vaccination _prev_vacc_label := to_char(_prev_vaccination.date_given, 'YYYY-MM-DD') || ' (#' || _prev_vaccination.pk_vaccination || '): ' || _prev_vaccination.vaccine || ' (' || array_to_string(_prev_vaccination.l10n_indications, ', ') || ')'; msg := _prev_vacc_label || E' ' || _NEW_vacc_label; select pk into _pk_current_provider from dem.staff where db_user = session_user; -- create inbox message for current user insert into dem.message_inbox ( fk_staff, fk_inbox_item_type, comment, data, importance, ufk_context, fk_patient ) values ( _pk_current_provider, (select pk_type from dem.v_inbox_item_type where type = 'review vaccs' and category = 'clinical'), _('Two vaccinations with overlapping target conditions recorded within one week of each other !'), msg, 1, ARRAY[_NEW_vaccination.pk_vaccination,_prev_vaccination.pk_vaccination], _NEW_pk_patient ); -- create inbox message for vaccinating provider if known if NEW.fk_provider is not NULL then -- and not identical to session user if NEW.fk_provider != _pk_current_provider then insert into dem.message_inbox ( fk_staff, fk_inbox_item_type, comment, data, importance, ufk_context, fk_patient ) values ( NEW.fk_provider, (select pk_type from dem.v_inbox_item_type where type = 'review vaccs' and category = 'clinical'), _('Two vaccinations with overlapping target conditions recorded within one week of each other !'), msg, 1, ARRAY[_NEW_vaccination.pk_vaccination,_prev_vaccination.pk_vaccination], _NEW_pk_patient ); end if; end if; end if; end loop; return NEW; END;
Generated by PostgreSQL Autodoc