Definition

Sharing organisation (Now sharing_agreement) is a reference model designed to provide a comprehensive view of data sharing agreements, including detailed information about both requester and sharer organisations, agreement status, user assignments, and organisation metrics.

Information

Due to the complex nature of data sharing agreements the sharing organisation model has been designed to allow for a single view per user based on their access configuration. An example of this could be user one has access to three sharing agreements which consists of 25 organisations.

Each organisation can activate (or deactivate) the agreement at any time. The model provides comprehensive details including:

• User assignments and access configuration
• Requester organisation details (name, CDB, status)
• Sharer organisation details (name, ODS code, CDB, status, environment)
• Agreement information (type, purpose, status, processing capabilities)
• Organisation metrics (patient counts, active users)
• Temporal tracking (last modified dates, close dates, extract dates)

As the model is driven from user configuration, when querying the model, you will only ever see organisations that belong to the agreements linked to your accounts.

Overview

flowchart TD
    DSA_01["Agreement 01"]
    DSA_02["Agreement 02"]
    EX_DSA_01["Explorer Agreement 01"]
    EX_DSA_02["Explorer Agreement 02"]

    org_01["Organisation 1001"]
    org_02["Organisation 1002"]
    org_03["Organisation 1003"]
    org_04["Organisation 1004"]
    EMIS_INDEX([EMIS INDEX])
    DSA[Data Sharing Agreements]
    ACCESS_GUARD([AccessGuard])
    usr_01["Explorer User 1"]
    usr_02["Explorer User 2"]
    Sharing_model[["Sharing Organisation Model"]]

    org_01 --> DSA_01
    org_02 --> DSA_01
    org_03 --> DSA_01
    org_03 --> DSA_02
    org_04 --> DSA_02

    DSA_01 --> EMIS_INDEX
    DSA_02 --> EMIS_INDEX

    EMIS_INDEX --> DSA
    DSA --> ACCESS_GUARD
    ACCESS_GUARD --> EX_DSA_01
    ACCESS_GUARD --> EX_DSA_02
    ACCESS_GUARD --> usr_01
    ACCESS_GUARD --> usr_02
    usr_01 --> EX_DSA_01
    usr_02 --> EX_DSA_02
    EX_DSA_01 --> Sharing_model
    EX_DSA_02 --> Sharing_model

    classDef nodeStyle stroke:#9961a4;
    class DSA_01,DSA_02,EX_DSA_01,EX_DSA_02,org_01,org_02,org_03,org_04,EMIS_WEB,DSA,ACCESS_GUARD,usr_01,usr_02,Sharing_model nodeStyle;

    linkStyle default stroke:#117abf,fill:none

What’s changing in iPCV v2

Expanded model with requester and sharer details

The model has been significantly expanded to include comprehensive information about both sides of the sharing agreement relationship.

Why?

iPCV v1 provided basic information about sharing organisations. However, customers needed more detailed insights into:

• Who is requesting data access (requester organisation details)
• Who is sharing data (sharer organisation details with ODS codes)
• Agreement-level metadata (types, purposes, processing capabilities)
• User assignments to agreements
• Organisation health metrics (patient counts, active users)
• Organisation status tracking (activated, deactivated, deleted, closed sites)

Customer benefit

This change provides the following benefits:

• Complete visibility into both requester and sharer organisations
• Enhanced filtering capabilities with status enumerations
• User-level agreement tracking via user_id and cognito_username
• Agreement metadata for better context and classification
• Organisation metrics for data quality assessment
• Environment tracking for multi-environment deployments

Customer action

Update your queries to leverage the new columns:

• Use requester_* columns for requester organisation information
• Use sharer_* columns for sharer organisation information (replaces generic organisation_* columns)
• Use agreement_* columns for agreement metadata
• Use status columns which now return human-readable values (e.g., ‘Activated’, ‘Deactivated’)
• Use metric columns (patient_count, active_patient_count, active_user_count) for analytics

Conditional NULL handling for deleted records

All non-key columns are now conditionally set to NULL when is_deleted = TRUE, ensuring data consistency and preventing the use of stale data from deleted records.

Why?

When a sharing agreement is deleted, the associated organisation data should not be accessible or used in analysis. By setting all data columns to NULL for deleted records, the model prevents accidental use of outdated information while still maintaining the record for audit purposes.

Customer benefit

This change provides the following benefits:

• Data integrity: Prevents use of stale data from deleted agreements
• Clear distinction: Easy identification of deleted records via is_deleted flag
• Audit trail: Maintains record existence with key identifiers (agreement_guid, user_id, organisation GUIDs)
• Flexible querying: Customers can see deleted agreements they previously had access to

Customer action

• Always filter on is_deleted = FALSE to exclude deleted records from analysis
• Use organisation = 'EMIS' to identify deleted records (deleted records are assigned to EMIS organisation)

Deleted flag correction

In iPCV v1 the sharing_organisation model used COALESCE on the deleted flag.

Why?

Due to the COALESCE if one of the sub tables have a TRUE value for being deleted this would still be set to FALSE.

Customer benefit

This change provides the following benefits:

• Improved consistency

• Cleaner history

Customer action

No customer action is required unless your logic depends on the previous value.

Schema updates for data maintenance

Removed columns:

• _ingest_time - no longer required in v2
• disabled - replaced by status columns
• is_activated_flag - replaced by more granular status columns
• Generic name, organisation_cdb, ods_code, emis_organisation_guid - replaced by prefixed requester/sharer columns

Renamed columns:

• deleted → is_deleted
• last_modified_date → lastmodified_date and lastmodified_datetime

New columns:

• user_id - User ID assigned to the agreement
• cognito_username - Cognito username for user authentication
• can_process - Boolean indicating if data can be processed
• requester_status - Status of requester organisation (Not Activated, Activated, Deactivated, Deleted)
• requester_cdb - Requester organisation CDB number
• requester_name - Requester organisation name
• requester_organisation_guid - Requester organisation UUID
• agreement_status - Agreement enabled/disabled status
• agreement_type_id - Agreement type identifier
• agreement_type_description - Agreement type description
• agreement_id - Agreement identifier
• agreement_name - Agreement name
• agreement_purpose - Agreement purpose description
• sharer_status - Status of sharer organisation (Ready for Activation, Activated, Deactivated, Deleted, Closed Site)
• org_type - Organisation type classification
• sharer_ods - Sharer organisation ODS code
• sharer_cdb - Sharer organisation CDB number
• sharer_name - Sharer organisation name
• sharer_organisation_guid - Sharer organisation UUID
• sharer_closedate - Date when sharer organisation closed
• patient_count - Total patient count
• active_patient_count - Active patient count
• active_user_count - Active user count
• sharer_environment - Sharer environment description
• lastmodified_datetime - Timestamp of last modification
• extract_date - Date of data extract

Benefit

• Comprehensive view of sharing agreements with requester and sharer details
• Enhanced status tracking with human-readable enumeration values
• User-level agreement assignment tracking
• Organisation health metrics for data quality monitoring
• Better temporal tracking with multiple date/datetime columns
• The is_deleted column with conditional NULL handling ensures data integrity
• Aligns with iPCV v2’s standardized approach to data modeling

Customer Action

• Update queries to use prefixed columns (requester_*, sharer_*, agreement_*)
• Replace is_activated_flag and disabled logic with new status columns
• Update ETL processes to use is_deleted column instead of deleted
• Leverage new metric columns for analytics and monitoring
• Remove references to _ingest_time, as it is no longer available in v2

Examples

Get active sharer organisations (non-deleted)

SELECT
    agreement_name,
    sharer_name,
    sharer_ods,
    sharer_status,
    patient_count,
    active_patient_count
FROM hive.explorer_ipcv.srv_sharing_agreement
WHERE is_deleted = FALSE
  AND sharer_status = '02. Activated'

Get agreements by user

SELECT
    user_id,
    cognito_username,
    agreement_name,
    agreement_purpose,
    requester_name,
    COUNT(DISTINCT sharer_cdb) AS sharer_count
FROM hive.explorer_ipcv.srv_sharing_agreement
WHERE is_deleted = FALSE
GROUP BY 1, 2, 3, 4, 5

Agreements modified in timeframe

SELECT
    agreement_name,
    sharer_name,
    sharer_status,
    lastmodified_datetime
FROM hive.explorer_ipcv.srv_sharing_agreement
WHERE is_deleted = FALSE
  AND lastmodified_datetime >= CURRENT_TIMESTAMP - INTERVAL '30' DAY
ORDER BY lastmodified_datetime DESC

Get closed or deactivated organisations

SELECT
    agreement_name,
    sharer_name,
    sharer_ods,
    sharer_status,
    sharer_closedate
FROM hive.explorer_ipcv.srv_sharing_agreement
WHERE is_deleted = FALSE
  AND sharer_status IN ('03. Deactivated', '05. Closed Site')