Definition

The Medication model in iPCV v2 is made up of two closely related tables Drug Record and Issue Record:

Drug Record – represents an individual prescribed, issued or recorded medication item for a patient (for example, a repeat prescription item, an acute item, or a historical drug record). It captures the high‑level context such as the drug, dose, route, instructions, dates, status and key participants.

Issue Record – represents individual issues / supplies of medication against a drug record (for example, each time a repeat prescription is issued, or a supply is dispensed). Each issue record is linked back to its parent drug record and, where applicable, to the underlying prescribing / dispensing events.

Together, these models provide a comprehensive view of a patient’s medications, from the top‑level drug record down to the individual issues / supplies.

Information

A drug record is the parent medication record that can be issued multiple times. It captures what was prescribed (code_id, dosage, quantity, quantity_unit), when it became effective, review and expiry information, and repeat-authorisation details such as number of issues and next-issue constraints.

Overview

flowchart TD
    patient["Patient"]
    drug_record[["Drug Record Model"]]
    issue_record["Issue Record"]

    patient --> drug_record
    drug_record --> issue_record

    classDef nodeStyle stroke:#9961a4;
    class patient,drug_record,issue_record nodeStyle;

    linkStyle default stroke:#117abf,fill:none

What’s changed in iPCV v2

1. NULL value columns for user GUIDs replaced with joins

In v1 some identifying context for organisations and users was embedded on the medication views, for example:

• authorisedissues_authorising_user_in_role
• authorisedissues_entered_by_userinrole
• emis_authorising_userinrole_guid
• emis_enteredby_userinrole_guid
• cancellation_userinrole_guid
• registration_ods_code (organisation lookups moved to organisation_v2)
• nhs_prescribing_agency
• nhs_prescription_type
• reimburse_type

Why?

• To ensure that NULL values are handled consistently and do not lead to unexpected behaviour in downstream processes.

In v2 these are normalised and the guid values can be rendered by joining with user_in_role_id:

• User-in-role: GUID/text fields are replaced with numeric IDs that join to user_in_role_v2:
  • entered_by_user_in_role_id (v2)
  • authorising_user_in_role_id (v2)
  • cancelled_by_user_in_role_id (v2)
  • original_authorising_user_in_role_id (v2)
• Organisation identifiers made Null (ODS codes now resolved via organisation models)
• reimburse_type is now NULL; use the boolean field is_privately_prescribed (v2) to determine whether a medication was privately prescribed.

Customer benefit

• Cleaner, more consistent joins to user_in_role_id
• Easier reuse of practitioner logic across models
• Improved query performance

Customer action

• Replace direct use of user GUID/text with joins on the following fields to user_in_role_v2 view:
  • authorising_user_in_role_id
  • entered_by_user_in_role_id
  • cancelled_by_user_in_role_id
  • original_authorising_user_in_role_id

2. FHIR-aligned columns removed

The following v1 columns (FHIR-aligned fields) have been removed in v2:

• type
• uom_dmd
• fhir_medication_status
• fhir_medication_intent

Why?

• Decision to remove FHIR mappings from the core analytical view and rely on dedicated reference tables or external FHIR services when required. See Universal improvements for more details.

Customer benefit

• A simpler schema with fewer ambiguous, duplicated or incorrectly populated fields.
• Encourages use of dedicated reference tables or external FHIR services where appropriate.

Customer action

• Remove dependencies on these fields in your SQL.

3. Removal of low-value, confidential or technical columns

Several v1 columns have been removed in v2 due to low value, duplication, reliability issues or confidentiality concerns.

The following columns have been removed in v2:

Technical/metadata fields:

• _record_version
• _update_date
• _update_hour
• data_filter

Clinical, inconsistent or NULL fields:

• consultation_source_code_id, consultation_source_emis_original_term, exa_encounter_guid, emis_encounter_guid — consultation and encounter context belongs to issue records, not drug records
• estimated_nhs_cost — relevant to issue records, not drug records
• emis_issue_method — available via most_recent_issue_method_description and issue-level attributes
• other_code, other_code_system, other_display, snomed_concept_id, snomed_description_id — clinical codes should be sourced from dedicated code reference tables; see the Universal Improvements documentation for details
• exa_prescription_guid

Why?

• Reduce storage and remove rarely used columns from generic analytical views.

Customer benefit

• A cleaner, easier‑to‑understand drug record schema.
• Encourages consistent joins to dedicated organisation / patient / event reference tables instead of relying on embedded IDs.

Customer action

• If you previously depended on these columns:
  • Replace update* logic with ingestion metadata (e.g. _ingest_time) or transform_datetime.
  • Replace ODS and organisation lookups with joins to organisation_v2.

4. Addition of new metadata and lifecycle columns

Lifecycle and availability:

• is_deleted
• transform_datetime

Local mixture information:

• local_mixture_name

Why?

• To provide a richer, auditable lifecycle for each drug record.
• To support soft-deletion, history of confidentiality/sensitivity changes, and robust linking to issue records.
• To provide a local mixture name where applicable (for example, where a mixture is recorded as a single drug record with a local code rather than discrete ingredients).

Customer benefit

• Ability to distinguish current vs historical or deleted drug records.
• Consistent transform/ingestion metadata that can be used for incremental loads.
• Local mixture name provides additional context for analysis of mixture drugs.

Customer action

• For incremental pipelines, use transform_datetime and/or _ingest_time as your primary change-detection fields.
• Consider explicitly excluding is_deleted = TRUE rows unless required.
• For mixtures, consider using local_mixture_name for additional context where available, but be aware this is not a standardised field and may not be populated for all records.

5. Removal of Redundant or Deprecated Fields

In iPCV v1, several attributes of other entities were directly included in the drug record model. In iPCV v2, the below fields have been removed from the drug record data model, and relevant IDs are provided to retrieve this information from dedicated extended data models.

Why?

This change simplifies the core drug record model, making it lighter and easier to manage. By moving specific attributes to their own models, data redundancy is reduced, and maintainability is improved. This allows for more focused information within the extended models, that increases the performance of iPCV v2.

Note: These decommissioned columns will be removed in a future iPCV release; plan migrations accordingly

Decommissioned Column	Relevant ID/ Column	Reason / Extended Data Model to be Referred
emis_authorising_userinrole_guid	authorising_user_in_role_id	user_in_role_v2
emis_enteredby_userinrole_guid	entered_by_user_in_role_id	user_in_role_v2
cancellation_userinrole_guid	cancelled_by_user_in_role_id	user_in_role_v2
authorisedissues_authorising_user_in_role		Always NULL as it is deprecated in v2
authorisedissues_enteredby_userinrole		Always NULL as it is deprecated in v2
nhs_prescribing_agency		Always NULL as it is deprecated in v2
nhs_prescription_type		Always NULL as it is deprecated in v2
registration_ods_code		Always NULL as it is deprecated in v2
reimburse_type	is_privately_prescribed	medication_drug_record_v2
confidential_patient_flag	patient_id	patient_v2
dummy_patient_flag	patient_id	patient_v2
regular_patient_flag	patient_id	patient_v2
sensitive_patient_flag	patient_id	patient_v2
opt_out_93c1_flag	patient_id	patient_v2
opt_out_9nd19nu09nu4_flag	patient_id	patient_v2
opt_out_9nd19nu0_flag	patient_id	patient_v2
opt_out_9nu0_flag	patient_id	patient_v2
non_regular_and_current_active_flag	patient_id	patient_v2
regular_and_current_active_flag	patient_id	patient_v2
regular_current_active_and_inactive_flag	patient_id	patient_v2
clinician_user_in_role_guid	authorising_user_in_role_id	user_in_role_v2
entered_by_user_in_role_guid	entered_by_user_in_role_id	user_in_role_v2
problem_observation_guid		Always NULL as it is deprecated in v2
processing_id		Always NULL as it is deprecated in v2

Customer benefit

• Improved Performance: A leaner drug record model can lead to faster query performance.
• Enhanced Scalability: Decoupled models are easier to maintain and extend independently.
• Data Consistency: Centralizing specific attributes in their own models ensures a single source of truth.

Customer action

• Review all reports and queries that rely on the decommissioned fields listed above
• Update them to join with the appropriate extended data models using the new IDs provided in the table
• Test thoroughly to ensure data accuracy and query performance with the new model structure

Examples

Find active drug records for a patient

SELECT
  drug_record_guid,
  emis_patient_id,
  code_id,
  dosage,
  quantity,
  quantity_unit,
  emis_medication_status
FROM hive.explorer_ipcv_vanilla.medication_drug_record_v2
WHERE
  emis_patient_id = 123456
  AND organisation = 'CDB-12345'
  AND emis_medication_status = 1
  AND is_deleted = FALSE;

Identify privately prescribed items

SELECT
  drug_record_guid,
  is_privately_prescribed,
  cancellation_date,
  expiry_datetime
FROM hive.explorer_ipcv_vanilla.medication_drug_record_v2
WHERE
  organisation = 'CDB-12345'
  AND is_privately_prescribed = TRUE
  AND is_deleted = FALSE;

Identify ODS codes for patients’ registered organisations

SELECT
  dr.drug_record_id,
  dr.emis_patient_id,
  p.registration_organisation_id,
  o.ods_code
FROM hive.explorer_ipcv_vanilla.medication_drug_record_v2 AS dr
JOIN hive.explorer_ipcv_vanilla.patient_v2 AS p
  ON dr.emis_patient_id = p.emis_patient_id
  AND dr.organisation = p.organisation
JOIN hive.explorer_ipcv_vanilla.organisation_v2 AS o
  ON p.registration_organisation_id = o.organisation_id
  AND p.organisation = o.organisation;

Join drug records to their issue records

SELECT
  dr.drug_record_guid,
  dr.code_id,
  dr.dosage,
  ir.issue_record_guid,
  ir.quantity,
  ir.effective_datetime
FROM hive.explorer_ipcv_vanilla.medication_drug_record_v2 AS dr
JOIN hive.explorer_ipcv_vanilla.medication_issue_record_v2 AS ir
  ON dr.drug_record_guid = ir.drug_record_guid
  AND dr.organisation = ir.organisation
WHERE
  dr.organisation = 'CDB-12345'
  AND dr.is_deleted = FALSE
  AND ir.is_deleted = FALSE;