Definition

The Drug Record model captures medication prescribing records at patient level, including prescribing details, issue cadence, quantities, and lifecycle status. Multiple issue records can be created from a single drug record for repeat prescriptions.

Constraints and Notes

• Soft deletion: When is_deleted is true, data fields will be nullified in upstream transformations and should be excluded from most analytical queries. Primary keys and foreign keys to patient data are preserved so deleted records and their relationships can still be identified. Foreign keys to coding tables are nullified as the coded information is no longer valid.
• dmd_product_code_id is resolved from code_id and may be NULL if no matching concept exists.

Examples

Find active medication records

SELECT
  drug_record_id,
  patient_id,
  code_id,
  effective_datetime,
  review_date
FROM explorer_open_safely.medication_drug_record
WHERE
  is_deleted = FALSE
ORDER BY
  effective_datetime DESC

Find records due for review

SELECT
  drug_record_id,
  patient_id,
  dosage,
  review_date,
  organisation
FROM explorer_open_safely.medication_drug_record
WHERE
  review_date <= CURRENT_DATE
  AND is_deleted = FALSE
ORDER BY
  review_date ASC