Reference data

In some cases, it is enough to reference external data (like a web page) via a link. GNUmed provides for these in a variety of menus and configurable buttons.

In other cases, it is necessary to import reference data into GNUmed, to enable it to form part of the patient record (medication use, diagnostic and therapeutic codes, etc.).

While such importing can be done manually by using such tools as psql (together of course with the gm-dbo password), GNUmed release 1.0 brought support for scripted data packs.

Data packs

Data packs are GNUmed's way to make available a wide variety of reference data on an as-needed, on-demand basis. By such means,

• there is no need to pre-load the GNUmed database with potentially-unwanted reference information
• downloads can be reduced in size
• easy one-step installation and updates, via GNUmed menu, which any GNUmed praxis administrator (armed with the gm-dbo password) can do

Currently-available data packs can

• install Canadian drug brands and their ingredients and populate the vaccines table
• fill in missing Anatomical Therapeutic Chemical (ATC) classification codes for drug names which match the INN names or mapped synonyms
• modify the (upper / lower) case of drug names using the TALLman system of better-distinguishing sound-alike drugs
• hosted here: data packs

Configuration file in which these packs are referenced:

• data pack list configuration file

General data pack preparation instructions:

• put all the SQL into a file called install-data-pack.sql
• create a zip file with an appropriate name which contains the install-data-pack.sql file at the top level

• SQL file cannot use psql-level \copy
• SQL level COPY cannot be used either because:
  • the data file would have to reside on the server machine
  • the data file would have to be readable by the server demon user
  • connection as superuser (postgres) would be needed
• do not modify the database schema outside of schema staging. - the data pack will fail
• you may use schema staging. as needed
• DROP your staging tables first, wrapped in \unset ON_ERROR_STOP ... \set ON_ERROR_STOP 1
• then BEGIN a transaction
• then (re-) CREATE your staging tables and do all the work including transferring data to production tables
• then commit the transaction
• then DROP your staging tables, again wrapped in \unset ON_ERROR_STOP ... \set ON_ERROR_STOP 1
• the above can be repeated within one install-data-pack.sql if several chunks of self-sufficient data is available (say, ATC codes and vaccines)
• the data pack must be re-runnable without any adverse effects, regardless of whether already having been run or not, and whether having ended in it failing, or not - that means it must check for existence first before INSERT/UPDATE of data

Overview of reference data constraints and requirements

packaging Medication reference data

Medication reference data may include, from potentially multiple sources:

• proprietary or brand data (this may include branded vaccines)
  • stored in ref.branded_drug
• active ingredient or consumable substance information
  • stored in ref.consumable_substance where the row must be distinct along three attributes {description, strength, units}

Branded drugs gets foreign-key referenced in two directions. Most commonly-used will be the link ref.lnk_substance2brand.pk by which means the consumption of a brand can be linked to a patient's record of substance_use. The second direction is that of vaccines, where the brand key is directly referenced in the table of vaccines clin.vaccine.

You can track the source of your praxis' brands data by use of ref.branded_drug columns fk_data_source, external_code and external_code_type.

Clinical records capture, in the case of each substance used by a patient, needs a minimum of the consumable_substance that is being used or, in the case of a branded drug, a link to the brand. The brand is a much more manageable way to capture a drug combination (whose parts cannot be taken independently of each other) and brands are a convenient way to capture drugs that are being supplied and taken in multiple strengths, for example, a triphasic oral contraceptive pill.

When reference data wants updating, it must further be taken into account that emptying the branded drug and consumable substances tables may be inadvisable even when their records not been used (hence bound by foreign keys), because these tables may also be holding:

• in the case of ref.branded_drug …
  • multiple instances of an is_fake 'generic vaccine' to cover a variety of conditions when the actual preparation was unknown (but, having said this, GNUmed comes with a

function to re-create those should any happen to get removed)

• in the case of ref.consumable_substance …
  • a variety of non-prescription (and potentially unapproved-of) substances

Having said that, if it should be desirable to clean out brands data, the above-described ref.branded_drug columns provide the means to keep clear the records of interest.

When aiming to remove brands, it may help to point out that

• rows in clin.substance_use which reference a brand key to it indirectly, via fk ref.lnk_substance2brand
• rows in clin.substance_use need not reference a brand – they can alternately reference a single substance (irrespective of brand) from column fk_substance; this provides a basis to unlink a clinical record association from a brand where that might be appropriate. One example might be where a pharmacy had post-hoc substituted an unknown brand for whatever had been recorded in GNUmed. Post hoc auditing of the GNUmed record to reflect the limit of the known information could be reasonable but would be available only in the case of brands which had a single active-substance.
• rows in clin.vaccination will have referenced clin.vaccine.pk, which itself references ref.branded_drug.pk (moreover, clin.vaccine.pk will exist represented in clin.lnk_vaccine2inds and clin.vaccine_batches) and so all of these referential integrity contraints would need to be met to remove brands which had been linked as vaccines

When adding brands, the job is not completed until:

• those brands which are vaccines are inserted into clin.vaccine, whose columns 'id_route' and 'is_live' (and 'fk_brand') cannot be NULL, and
• non-vaccine brands (and optionally vaccines) are indirectly linked to their component substance(s) – which may or may not already exist – via one or more links in ref.lnk_substance2brand

altering Medication names

This can be done in-place, for example:

• fixing spelling mistakes
• when a drug has been withdrawn from the market as unsafe (rename it to: "DO NOT PRESCRIBE: orginal-drug-name")
• applying the TALLman approach to improving drug safety, which uses letter case to make similar drug names more distinct (and for which GNUmed may by this time have a data pack available)