Payment Engine

The payment engine is split into two components:

• The payment calculator This takes shifts, bookings (details of the booked staff member), submitted timesheets, and payment rates as input, and outputs payment data. It can also output estimated payment data based just on shifts and payment rates (and optionally bookings).
• The payment reporting engine. This takes payment data fragments, and output payment reports. It may filter down

Terminology

Payment Calculator

• A Time Type A type of time worked such as "On Site", "On Call", Contracted Hours", "Overtime Hours", "Travel". Different time types may be paid differently.
• A Timesheet Type is a setting on a shift which controls what time information is collected when the staff member submits their timesheet (the different timesheet type options are listed below)
• Timesheet Params Are sub-settings within a Timesheet Type which can further control what time information is collected when the staff member submits their timesheet, and can also change how that information is interpreted (e.g. which Time Type it is matched against)
• A Payment Rate is a record containing infomation about the (hourly or whole-shift) rate at which someone should be paid, along with information about which shifts or parts of shifts it applies to (more details below).
• A Payment Data Fragment fragment is the result of a set of payment rates being applied againt a shift/timesheet. It records the total payment amount for all or part of a shift, along with which shift, timesheet, and payment rate were matched to generate that record (a shift may have more than one payment data fragment associated if for example it crosses both day and night rates).
• Estimated Payment Data is very similar to regular payment data except that it is calculated before a timesheet has been submitted using time data synthesized from the scheduled times of the shift. It is not used for payment reporting, but it is used for forecasting costs in the regular "reporting" section of the system.
• The ESR or Electronic Staff Record is the NHS Electronic Staff Record, a large NHS system which we integrate with the payroll function of.
• An ESR Element is the ESR system's equivalent of a payment rate where a set of codes (the Subjective Code, Element Name, Allowance Type Name) is associated with an (hourly or whole-shift) rate.

Payment Reporting

• A Payment Report is a file (usually a CSV, Excel, or ESR data file) which contains a list or summary of the payment data associated with a set of shifts (e.g. all shifts that took place prior to 31st August 2021 that have approved timesheets)
• A Payment Run Type is a configuration within a collaboration that defines a set of shifts and staff to run reports on (e.g. shifts in the Covid Vaccination Bank Service at Site A, which have staff booked in from Bank B), and also a set of reports to run (e.g. a shifts report with x, y, z columns, along with staff claim forms).
• A Payment Run is an execution of a Payment Run Type with a specified cut off date/time (e.g. end of day on 31st August 2021). A payment run may be "previewed", which produces a set of reports. It may also be "run" or "finalized", which produces a set of reports, emails those reports to admins (and staff if appropriate), and marks the shifts/timesheets as "sent for payment" which prevents them from being included in the reports of future payment runs.

Time Types, Timesheet Types & Timesheet Params

How a payment rate matches to a shift/timesheet can depend on the Timesheet Type, and the Timesheet Params that configure the timesheet within that timesheet type. The Timesheet Type and the Timesheet Params are set by the Rate Card that is selected when the shift is created.

The following Timesheet Types are available:

• start-end - The start time and end time of the shift are collected, along with the start and end times of any breaks taken. A time type for the whole shift may optionally be specified.

  Example timesheet params for start-end timesheets:

    {
      // Optional. Non-specified or null time_type_id matches against a null time_type_id on the payment rate.
      "timeTypeKey": "pcn-demo-normal" 
    }
  

• start-end-multi - Multiple sets of start and end times are collected, and each one is associated with a time type which the staff member selects when creating a row in the timesheet.

  Example timesheet params for start-end-multi timesheets:

  {
    "timeTypes": [
      { "key": "pcn-demo-normal", "name": "Contracted Hours" },
      { "key": "pcn-demo-overtime", "name": "Overtime Hours" }
    ]
  }
  

• on-site-off-site - The overall times in hours for two time types "On Site" and "Off-Site" are collected. TODO: Generalise to arbitrarily specified time types.

  Example timesheet params for on-site-off-site timesheets:

  {
    "timeTypes": [
      { "key": "IBdI4X0fZJPakkTeHpCo", "name": "On-Site" },
      { "key": "cvdvH17vmSVrCeD6yMDJ", "name": "Off-Site"}
    ]
  }
  

Payment RGS Keys

Payment rates may match against the role_key, grade_key, and speciality_key of a shift/booking. But exactly which key they are matching on can vary as follows (earlier items in the list takes precedence):

• role_key - baked payment_role_key, shift role_key, candidate role_key
• grade_key - baked payment_grade_key, current candidate max grade_key
• speciality_key - baked payment_speciality_key, shift speciality_key

The baked payment_*_keys are computed in the create-timesheet helper/QP (so when the shift ends) and then stored on the booking in the shift.bookings table. They are computed as follows:

• payment_role_key:
  • If useBakedPaymentRoleKey in payment settings is false, then payment_role_key is null
  • Else payment_role_key is the candidate's role_key
• payment_grade_key:
  • If useBakedPaymentGradeKey in payment settings is false, then payment_grade_key is null
  • Else if the shift has a speciality_key set, then payment_grade_key is the candidate's max grade within that speciality
  • Else payment_grade_key is the candidate's max grade overall
• payment_speciality_key:
  • Currently payment_speciality_key is always null
  • (There's not really a use case for payment_speciality_key. It's mainly there for symmetry with the grade and role keys.)

The concrete use case for the baked payment_grade_key is CDDFT where they pay staff according to their max grade (within the speciality of the shift) rather than the grade the shift is advertised at. As the grades a staff member is qualified at can change over time, it is important to record the grade they were when the shift was worked.

The concrete use case for the baked payment_role_key is for PCNs and similar organisations where the shift's role may be "Any" (meaning the shift is open to multiple roles), but the pay may depend on the staff member's who works the shift's role. In rare cases, roles can change over time, thus it is important to record the role they were when the shift was worked.

In general, we want the inputs to the payment engine to be deterministic and stable over time so that we can use historical timesheets and computed data as a great big integration test suite.

Payment Rate Configuraton

Guide

The first rule of payment rates is that they must not overlap. For each minute of the shift, exactly one payment rate must match. If more than one rate matches even a single minute of a shift then the payment engine will bail out and refuse to calculate payment data for that shift until the problem is corrected.

Static-Data Configuation

Payment rates are configured per-collab. It is possible to make payment-rates org-specific on a rate-by-rate basis.

Payment rates can be confiured using one of two file formats:

• payment-rates.json - This is a flat array of payment rate objects

  Example:

  [
    { "key": "-NAFlDCtRNcgQ9nLNy2l", "name": "Payment Rate 1", "from": "2022-08-24", "to": null, "hourly_rate": "10.5", ... },
    { "key": "-NAFlDCtRNcgQ9nLtrhl", "name": "Payment Rate 2", "from": "2022-08-24", "to": null, "hourly_rate": "20",   ... },
  ]
  

• payment-rates-grouped.json - This is an array of payment rate groups, where a group has the following properties:

  • group_name - Descriptive name for a whole group of rates (used in UI to title group)
  • group_order - Order for a whole group of rates (used in UI to order groups)
  • Any properties that are valid on a payment rate
  • rates - an array of payment rates

  Example:

  [
    {
      "group_name": "Group 1",
      "group_order": 0,
      "service_key": "service-1",
      ...
      "rates": [
        { "key": "-NAFlDCtRNcgQ9nLNy2l", "name": "Payment Rate 1", "from": "2022-08-24", "to": null, "hourly_rate": "10.5", ... },
        { "key": "-NAFlDCtRNcgQ9nLtrhl", "name": "Payment Rate 2", "from": "2022-08-24", "to": null, "hourly_rate": "20",   ... },
      ]
    }
  ]
  

  Any payment rate properties that are specified at the group level are automatically applied to all payment rates in the group.

See "Payment Rate Property Reference" section below for full list of property that can be specified on a payment rate.

Payment Rate Property Reference

• UNUSED - This property no longer does anything and can be removed
• DEPRECATED - Use of this property is discouraged and is planned for removal, but it currently still works

Metadata

Property	Description
key	Unique primary key required
collab_key	Which collab the payment rate applies to required
name	Descriptive name (see also: allowance_type_name)
updated_by	Initials of the person who last updated the payment rate (semi-maintained)
last_updated	Date of last update to this payment rate (semi-maintained)

Effective Dates

Used to manage rate changes. Payment rates only match against shifts whose start time is between effective_from (inclusive) and effective_to (exclusive)

Property	Description
effective_from (alias: from)	From date/time (inclusive)
effective_to (alias: to)	To date/time (exclusive)

Payment & ESR

One of hourly_rate and whole_shift_rate is required, and both may not be set.

Note: Where ESR is used (e.g. for CDDFT) the hourly_rate and whole_shift_rate are not actually used for payment. Instead we send through an "ESR Element Name" and "Units Worked", and the final payment amount is re-calculated on their side. We maintain accurate rates on our side anyway so that we can communicate payment amounts to staff through the app.

The old_* properties are a legacy feature from before the effective_from and effective_to dates were added to control rate changes, and apply to a few old (prior to 2018-10-25) CDDFT payment runs only. They override the unprefixed version of their property for shifts/payment runs before this date.

Property	Description
hourly_rate	The rate per-hour in £ (use a string not a number for decimal values to avoid floating point rounding issues)
whole_shift_rate	The flat rate for the whole shift in £ (use a string not a number for decimal values to avoid floating point rounding issues)
min_minutes_worked	A minimum shift time, that effectively floors the payment amount in the case that the worked time at this rate is less than the specified time.
subjective_code	ESR code for staff member (usually role/grade)
element_name	ESR code for a high-level category of payment rates
allowance_type_name	ESR code for a single payment rate
allowance_type_code	A mangling of the Allowance Type Name into a more machine friendly format (AFAIK only used internally, and can probably be removed)
old_hourly_rate	Overrides hourly_rate, for payment runs prior to 2018-10-25
old_subjective_code	Overrides subjective_code, for payment runs prior to 2018-10-25
old_element_name	Overrides element_name, for payment runs prior to 2018-10-25
old_allowance_type_name	Overrides allowance_type_name, for payment runs prior to 2018-10-25
old_allowance_type_code	Overrides allowance_type_code, for payment runs prior to 2018-10-25

Shift Matching

Property	Description
org_key	Matches only shifts in this org.
service_keys	Matches only shifts WITH one of these service keys
excluded_service_keys	Matches only shifts WITHOUT one of these service keys
rate_modifier_keys	Matches only shifts WITH one of these rate modifier keys
excluded_rate_modifier_keys	Matches only shifts WITHOUT one of these rate modifier keys
site_keys	Matches only shifts WITH one of these site keys
excluded_site_keys	Matches only shifts WITHOUT one of these site keys
reason_keys	Matches only shifts WITH one of these reason keys DEPRECATED. Prefer to match on services.
excluded_reason_keys	Matches only shifts WITHOUT one of these reason keys DEPRECATED. Prefer to match on services.
service_key	UNUSED. Replaced by service_keys. Still works in static-data JSON files.
rate_modifier_key	UNUSED. Replaced by rate_modifier_key. Still works in static-data JSON files.
site_key	UNUSED. Replaced by site_keys. Still works in static-data JSON files.
reason_key	UNUSED. Replaced by reason_keys. Still works in static-data JSON files.
sub_reason_key	UNUSED. Matching payment rates against subreason keys is no longer supported.

Shift/Staff Matching

See "Payment RGS Keys" section for exactly which role, grade, and speciality keys are matched

Property	Description
role_keys	Matches only shifts WITH one of these role keys
excluded_role_keys	Matches only shifts WITHOUT one of these role keys
grade_keys	Matches only shifts WITH one of these grade keys
excluded_grade_keys	Matches only shifts WITHOUT one of these grade keys
speciality_keys	Matches only shifts WITH one of these speciality keys
excluded_speciality_keys	Matches only shifts WITHOUT one of these speciality keys
role_key	UNUSED. Replaced by role_keys. Still works in static-data JSON files.
grade_key	UNUSED. Replaced by grade_keys. Still works in static-data JSON files.
speciality_key	UNUSED. Replaced by speciality_keys. Still works in static-data JSON files.

Time Type

Property	Description
time_type_id	Matches the time_type_id set in timesheet_params. For start-end-multi and on-site-off-site shifts this is set per time category and is mandatory. For start-end shifts this is set for the whole shift and is optional / may be null.
fragment	UNUSED (and no longer used). Used to indicate whether the shift was a start-end or an on-site-off-site shift.

Time/Date Matching

Properties marked INTERSECTS match shifts where any part of the shift crosses the specified time/date, and bypass the usual pro-rata payment matching, paying the entire shift at that rate.

Assuming an INTERSECTS rate does not match, shifts are paid minute-by-minute on a pro-rata basis. Each minute in the shift is matched to a payment rate. To match:

• The "day of week" properties for the current day of week must be enabled
• The current minute must be between the from_time and to_time Bank holidays can optionally be treated differently to regular week days by setting the bh property to a non-null value.

Generally either one of the INTERSECTS properties should be set, or all of the others. Prefer non-INTERSECTS properties where possible as they are more flexible.

Date and time matching is all done relative to the timezone that the shift took place in (the timezone of the shift's site).

Property	Description
bank_holiday	INTERSECTS. Matches shifts intersecting a bank holiday (takes precedence over weekend)
weekend	INTERSECTS. Matches shifts intersecting a weekend (takes precedence over intersects_time)
intersects_time	INTERSECTS. Matches shifts intersecting the specified time
mon	Matches minutes in a shift that occur on a monday
tue	Matches minutes in a shift that occur on a tuesday
wed	Matches minutes in a shift that occur on a wednesday
thu	Matches minutes in a shift that occur on a thursday
fri	Matches minutes in a shift that occur on a friday
sat	Matches minutes in a shift that occur on a saturday
sun	Matches minutes in a shift that occur on a sunday
bh	Matches minutes in a shift that occur on a bank holiday. If true, then match if the day is bank holiday. If false, then do not match if the day is a bank holiday. If null, then ignore that the day is bank holiday and match on the day of the week as normal.
mon2fri	Shorthand in JSON files that sets all of mon through fri props
from_time	From time of day (hh:mm format)
to_time	To time of day (hh:mm format)

Testing and deploying changes

Testing changes

When making changes to configured payment rates, or to the payment engine itself, it is useful to be able to test your work. To do this, we generally use historical timesheets and computed payment data as an integration test suite.

The process for running this test is as follows:

• Ensure that there is a .env.prod file in the root directory of your ms-timesheets repo
• Ensure that USE_LOCAL_PAYMENT_RATES in the the .env.prod file is set to y (it's fine to set it for other microservices too)
• Ensure that LOCAL_PAYMENT_RATES_LOADER_PATH in the the .env.prod file is set to the absolute path of the /database/load-payment-rates.js file in the ms-admin repo
• Ensure that you payment rates you want to test with locally in ms-admin
• Ensure that you have the ms-timesheets branch you want to test with checked out
• In ms-timesheets, run ENV=prod npx cw pay:compute (this may take around 1-2 minutes to run)
• In Postico, run the query at the top of the "Payment Engine -> ESR Validate" file from the shared query folder.

The result of the query is a diff between the payment_data table (production payment data) and payment_data_test table (which contains the results of the pay:compute computation you have just run). There should be zero rows*, unless you are expected there to be changes to existing payment data. In general, one would only expect there to be changes to unpaid timesheets. If there

* As of the time of writing, there are actually about 5. These represent mistakes that need to be reviewed and sorted out. It can be worth checking the output of pay:compute followed by the the ESR validate query before making your changes in case there are already issues. The key thing is not to create any additional unexpected rows here.

Deploying changes

• Code changes should be pushed to master (ms-timesheets)
• Payment rates changes should be pushed to master (ms-admin) and seeded (ENV=prod npx cw db:seed from ms-admin)

You may also find that are recent shifts that now need their payment data update.

You can do this as follows (from ms-timesheets. Use the ESR Validate query to verify that it's worked):

ENV=prod npx cw pay:compute --unpaid --mode overwrite

Be very careful with --mode overwrite. It has the power to overwrite all historical payment data if run without --unpaid or a similar filter. In theory this is fine if regular pay:compute is run with no filters and there are no resultant rows in ESR Validate, but is still best avoided where possible!

Payment Calculator Code

Database tables

Input tables:

Table Name	Used for
org.payment_settings	Whether breaks should be paid or unpaid
collab.payment_rates	The collab's payment rates
shift.shifts	Basic shift details and timesheet parameters
collab.sites	The shift's timezone
shift_bookings	Baked RGS keys
users.candidate_rgs	Current role and grade
timesheet.timesheets	Submitted time data

Output tables:

Table Name	Used For
timesheet.payment_data	Store resultant payment data (if running in production mode)
timesheet.payment_data_test	Store resultant payment data (if running in test mode)

The payment_data_test table allows us to test changes (either to the payment engine code, or to the configured payment rates). We do this by running the new code/config over every single historical timesheet, storing the result in the payment_data_test table and then running a SQL query to compare the payment_data_test and payment_data tables and making sure that any differences are expected.

Entry points

The payment calculator is called from 2 places:

• The compute-timesheet-payment-data queue-processor, which is used to compute payment data when a timesheet is submitted (and if enabled for the org, it can also fake timesheet data and compute estimated payment data when a shift is created/booked/worked)

• src/payment/compute-payment-data-batch.js which is used to recompute payment data for many timesheets in bulk. It is called from the compute-missing-payment-data queue-processor which runs hourly on all unpaid timesheets to catch any payment calculations which may have failed spuriously and to update the rates for any timesheets whose payment rates have been updated. It is also called from the pay:compute CLI task which can be configured to recompute timesheet data for a variety of different subsets of the data (by default the pay:compute CLI task outputs to the payment_data_test table, but it can also be configured to overwrite the data in the main payment_data table).

TODO: Fix (or disable) faking timesheet data for start-end-multi timesheets.
TODO: Add support for estimating timesheets to compute-payment-data-batch.js so that compute-timesheet-payment-data can just call it rather than having it's own implementation of loading the supporting data.