Field standardisation

Background

Kotahi allows us to create new activities/forms and define custom fieldnames as needed.

Before our use of Kotahi activities expands too much, this article outlines naming conventions as well as standard field names for similar items occurring across different forms.

For context, work was undertaken during 2024 to try and streamline the number of contact types available for use in Aiga. Note that this single field was used to capture what we have 3 fields for in Kotahi:

1. Type
2. Location
3. Method.

The agreed contact list after this exercise is copied here for posterity:

Contact type	Meaningful	Definition
Administrative	N	Email correspondence Booking appointments Data entry Phone calls Phone call – no answer Uploading consent forms and approvals
Advocacy	Y	Supporting Client for external appointments, e.g. MSD Hospital FGC OT Family Court Translation Kainga Ora Housing Providers Youth Providers Inter Agency Talanoa Externally Referred Follow Up
Assessment	Y	Initial phone call assessment for all referrals Face to face Talanoa Talanoa Phone Session School Visit Financial Requests Covid Support Budgeting CC Assessments CSIQ Assessment Initial needs Assessment
Care plan	Y	Family Harm Intervention Goal Setting Safety Plans Internal/External Referrals (action plan) Housing CV
Care plan review	Y	Goal Plan Review
Case review	Y	Whanau Resilience BAU CC ISR Follow ups
Cold calls	Y	Home visit – no answer
Consent	Y	Do Not Use
Discharge	N	Exiting the service Graduating Secured Employment Not Contactable
Group session	Y	All workshops (BFC, Ready to Rent, T2I) Promotional Service Events
Home Visit	Y	Home Visit Talanoa Le Va Sessions Talanoa Sessions ISR Wellbeing Check Ins CSIQ CC Food Delivery
Hui/Whanau	Y	Whanau Sessions
Immunisation	Y	Do Not Use
Network Meeting	Y	All employer and stakeholder online and face to face meetings
Other	Y	Food Shopping Doctors appointments Documentation Support Establishing Partnerships Supporting Internal Events

Naming convention

All activity field names should be Camel case. Specifically, 'upper Camel case' where each word is capitalized.

The words used should be as brief and descriptive as possible. Be aware that the chosen field name will be used as a key to access any saved values, as part of a (potentially) large JSON array. Since the size and structure of a given JSON array will be variable, it is important that a required value can be found intuitively without needing to rely on maintenance of individual data dictionaries.

Standardised fields

Context

Field names need to be unique only within a service. Therefore, there is no benefit in making detailed 'helpful' fieldnames such as WhanauOraTotalSpend and TupaiaTotalSpend. A better strategy is to simply name each TotalSpend, and then it becomes much easier to aggregate spend across services if there is a good use case for doing so.

In the first example, a sample query would need to look something like:

with ctea as
(
	select
	a.clientid,
	sum(j."FamilyTotalSpend")
	
	from kotahi.activities a,
		jsonb_to_record(customfielddatajson) 
			as j(
				"FamilyTotalSpend" float
				)
	
	where 
		activitytypeid  = 27 --Whanau ora request submission
		and
		customfielddatajson->>'FinanceCompleted' = 'on' --has been paid
	group by a.clientid
)
,
cteb as 
(
	select
	a.clientid,
	sum(j."TotalSpend")
	
	from kotahi.activities a,
		jsonb_to_record(customfielddatajson) 
			as j(
				"TotalSpend" float
				)
	
	where 
		activitytypeid  = 32 --Transition to adulthood financial assistance
		and
		customfielddatajson->>'FinanceCompleted' = 'on' --has been paid
	group by a.clientid
)

select 
a.clientid,
sum(a.FamilyTotalSpend,b.TotalSpend) "TotalSpend"

from ctea a
left join cteb b
on a.clientid = b.clientid

group by a.clientid;

In the second example, the approach is much simpler:

select
a.clientid,
sum(j."TotalSpend")

from kotahi.activities a,
	jsonb_to_record(customfielddatajson) 
		as j(
			"TotalSpend" float
			)

where 
	activitytypeid in (27,32)
	and
	customfielddatajson->>'FinanceCompleted' = 'on' --has been paid
group by a.clientid;

Also note that jsonb_to_record seems easier to cast data types from a JSON array for aggregation, but there might be better ways.

Reconciling old keys

If suboptimal naming is already in operation, standardisation will have to occur in downstream logic. For example, reference data which 'maps' fields to standardised naming and/or potentially baked into analytical products such as materialized views. Either way this should also be documented separately.

List of standard keys

Below is a list of fieldnames that should be used for a given thing. This should be kept updated for each new service/activity, or any changes made to existing work.

Fieldname	Purpose
ClientPresent	Yes/No indicating whether client was present for this activity
TotalSpend	Any value used as a running total for any payments to client/family
RequestAmountTotal	The total value of any requisition or request for financial assistance to a client/family
RequestItem[n]	Description of an item or product being requested
RequestAmountItem[n]	Cost of an item or product
ContactDuration	Duration of a completed activity
ContactDurationUnit	Unit of duration
ContactMethod	The method of any completed contact or contact attempt (eg. phone, email, etc)
ContactType	The main purpose of a contact or attempted contact (eg. goal plan review, administration, etc)
ContactLocation	Where the contact took place (eg. onsite or at home)
ContactOffsiteDescription	Optional description of the offsite location
Notes	Free text narrative to describe the contact
[Domain]Goal[n]	Specifies a goal domain (eg. health, finance) and a fixed number of goals
[Domain]GoalStatus[n]	Records the current status of a particular goal

List of standard options

Where an activity field provides fixed/generic options, these should also have standardized fieldnames for easy retrieval. Note that these values are a streamlined/edited list using the list from 2024 as a starting point.

Fieldname	Allowed options	Allowed option fieldnames	Current?
ContactDurationUnit	Minutes	Minutes
ContactDurationUnit	Hours	Hours
ContactDurationUnit	Days	Days
ContactMethod	Face to Face	FaceToFace
ContactMethod	Messenger	Messenger
ContactMethod	Phone Call	PhoneCall
ContactMethod	Text Message	TextMessage
ContactMethod	Email	Email
ContactType	Administration	Administration
ContactType	Advocacy	Advocacy
ContactType	Assessment	Assessment
ContactType	Case review	CaseReview
ContactType	Cold call visits	ColdCall
ContactType	Did Not Attend	DNA
ContactType	External Referral	ExternalReferral
ContactType	Family conference	FamilyConference
ContactType	Family Request	FamilyRequest
ContactType	Goal Plan Review	GoalPlanReview
ContactType	Internally Referred	InternallyReferred
ContactType	Workshop	Workshop
ContactLocation	Aged Care Facility	AgedCareFacility
ContactLocation	Cafe / Other	CafeOther
ContactLocation	GP Visit	GP
ContactLocation	Home / Whānau Visit	HomeVisit
ContactLocation	Hospice	Hospice
ContactLocation	Hospital Clinic	Hospital
ContactLocation	Offsite	KPT
ContactLocation	Onsite	KPT
ContactLocation	Social Agency	SocialAgency
ContactLocation	Specialist Clinic	SpecialistClinic
[Domain]GoalStatus[n]	Complete	Complete
[Domain]GoalStatus[n]	In progress	InProgress
[Domain]GoalStatus[n]	Incomplete	Incomplete
[Domain]GoalStatus[n]	Not applicable	NA

Assessment

Note that there is no Assessment contact type above. This type is a hangover from Aiga where freetext narrative case notes were used to hold all contact types - including 'structured' assessments. In Kotahi, we want all assessments to be structured as an activity, so that we can pull data around goals and progress for all clients. Therefore, any 'assessment' should be the formal activity for the service concerned. If any other notes or comments need to be made at the same time, free text areas will be available within the assessment activity.

This same principle also applies to contact types Case review and Goal plan review, as of update log Whanau ora/Tupaia 20250224.

Workshop

Also note that the contact type Workshop has been removed, since a scheduled 'group session' should be recorded via the 'Group Activities' component in Kotahi.

This will be visible in the client timeline and 'Group Activity', but is not saved as an Activity.

Contact location

As shown, there were previously a wide range of options here. However, we determined that we really only want to know if it occurred onsite at K'aute, or out in the community - whether a home visit or somewhere else. This field therefore now has only two available options.