Editing Kotahi data supply (section)

==Datasets==
Table below summarises supplied data:

{| class="wikitable sortable"
! Name || Description || Notes
|-
|-
| Activities || Logs of client interactions, including type, date, duration, and additional details stored in CustomJSONData.<ref name="CustomJSONDataNote" /> Note || The CustomJSONData field requires proper escaping of quotes within strings. For example, <code>""\hello""\</code> inside a string should be escaped as <code>\\\"hello\\\"</code>. Ensure data is validated and fixed before import to avoid parsing issues.
|-
| ActivityTypes || Categorises activities/forms, e.g.,"Narrative Case Notes" "Requisition Approval"
|-
| AssignedUsers || Tracks which users are assigned to specific clients.
|-
| ClientCorrespondence || Records communications between clients and providers.
|-
| ClientIwi || Links clients to their associated iwi. Not in use for now.
|-
| ClientRelationshipParticipants || Defines roles in client relationships, like "Parent" or "Sibling" .
|-
| ClientRelationshipTypes || Defines valid relationship types between participants, such as "Parent-Child" or "Siblings".
|-
| ClientRelationships || Tracks relationships between clients (e.g., parent-child).
|-
| ClientServiceStatus || Tracks the status of a client in a service, e.g., "Active" or "Inactive".<ref name="ClientServiceStatusNote" />
|-
| ClientTasks || Stores tasks assigned to clients, including due dates and statuses.
|-
| Clients || Core client data, including personal details like name and DOB.
|-
| CorrespondenceType || Defines types of communication
|-
| CustomActivityFieldsData || Stores custom data fields linked to specific activities.
|-
| Dhbs || Lists district health boards, their regions, and their respective ID.
|-
| Ethnicities || Catalogues ethnicity options for demographic data.
|-
| Iwi || Lists specific iwi (tribes) associated with clients or users.
|-
| IwiGroupings || Groups iwi into broader categories.
|-
| MessageTemplates || Predefined message templates for correspondence.
|-
| Providers || Details about service providers, e.g., "Tui Medical" or "Dr Jane Doe" || 
* Where the column '''Type''' = 0: General Practitioner.
* Where the column '''Type''' = 1: Provider.
|-
| ServiceStatus || Defines statuses for services, e.g., "New Referral" or "Completed." <ref name="ClientServiceStatusNote" />
|-
| Services || Information about available services, including their names and descriptions.
|-
| UserLocalities || Tracks the geographic areas assigned to specific users.
|-
| UserServices || Links users to the services they are involved with.
|-
| Users || Stores user details, including names, roles, and contact information.
|-
| Hauora data || Data about hauora goal plans || Hauora goal plans are currently not being utilised at time of writing.
|-
| Calendar data || Appointments data including schedules, availability, and events || Includes calendars, schedule entries, event details, and linked clients
|-
| Group activities data || Group activities data including details about participants and staff etc.
|}


<references>
<ref name="CustomJSONDataNote">The CustomJSONData field in Activities stores flexible, structured data in JSON format. It is used for capturing additional details such as specific requests, health goals, or screening statuses, etc. enabling the retrieval of custom attributes.</ref>
<ref name="ClientServiceStatusNote">Although the ClientServiceStatus table tracks a client's service status (e.g., "Active," "Inactive"), we are using the latest "Activity Date" on Power BI to dynamically determine activity status, addressing cases where statuses are stuck on "New Referral" for example</ref>
</references>

===Materialized Views for Reporting===
====Technical Implementation====
The following materialized views have been created in the <code>kotahi</code> schema to support reporting requirements:

{| class="wikitable sortable"
! Materialized View !! Purpose !! Refresh Schedule
|-
| <code>mvw_activities_summary</code> || Details of kotahi activities|| 00:45 daily
|-
| <code>mvw_client_services</code> || Shows active/inactive service relationships with status history || 00:50 daily
|-
| <code>mvw_client_summary</code> || Core client demographics + household/location metadata || 00:55 daily
|-
| <code>mvw_tupaia_report</code> || Kotahi data relating to tupaia service || 01:05 daily
|-
| <code>mvw_tupaia_stages</code> || Tracks client progression through service stages || 01:15 daily
|-
| <code>mvw_tupaia_stages_counts</code> || Count of tupaia clients in each stage of tupaia pathways || 01:25 daily
|}

===Cron Job Configuration===
Refresh schedules are managed via PostgreSQL's <code>pg_cron</code> extension:

<syntaxhighlight lang="sql">
-- Scheduled between 00:45-01:25 daily
SELECT cron.schedule('45 0 * * *', $$REFRESH MATERIALIZED VIEW kotahi.mvw_activities_summary$$);
SELECT cron.schedule('50 0 * * *', $$REFRESH MATERIALIZED VIEW kotahi.mvw_client_services$$);
SELECT cron.schedule('55 0 * * *', $$REFRESH MATERIALIZED VIEW kotahi.mvw_client_summary$$);
SELECT cron.schedule('5 1 * * *',  $$REFRESH MATERIALIZED VIEW kotahi.mvw_tupaia_report$$);
SELECT cron.schedule('15 1 * * *', $$REFRESH MATERIALIZED VIEW kotahi.mvw_tupaia_stages$$);
SELECT cron.schedule('25 1 * * *', $$REFRESH MATERIALIZED VIEW kotahi.mvw_tupaia_stages_counts$$);
</syntaxhighlight>

===JSON data===
The <code>customfielddatajson</code> field contains the contents of any activity. Obviously this varies between activities, and over time (if activities are modified). We will likely need some kind of mapping document to keep track of field changes and/or multiple fields which we need to perform analysis/calculations on.<ref>For example, financial summaries or client/aiga goal plans which span different services.</ref>

A fairly nice way to unnest JSON for specific tasks can be done via:

<syntaxhighlight lang="sql" line>
select distinct
a.clientid,
j."RequestDate",
j."RequestProduct1",
j."RequestAmountTotal"

from kotahi.activities a,
	jsonb_to_record(customfielddatajson) 
		as j(
			"RequestDate" date,
			"RequestProduct1" text,
			"RequestAmountTotal" float
			)

where activitytypeid  = 27 --Whanau ora request submission
‎</syntaxhighlight>