Developer Blog Series: API Lists & Sort_token

Lately we’ve been getting a lot of questions and hearing a bit of confusion around how lists work within SKY API. I’m the first one to admit that they are a bit complex, so I would like to take a minute to give you a glance into how the sausage is made, explain why and how lists work the way they do, and hopefully provide some insights to improve your experience using SKY API.

Transactional vs Read Optimized Lists

The APIs that sit on top of Raiser’s Edge NXT (Constituent, Gift, Fundraising, Opportunity) have three different sources of data. First, the traditional transactional database, which is all accessible through database view and follows the same schema of classic Raiser’s Edge. The Second, we have additional distributed, supplemental data stores for additional entity and feature enhancements that were not available in traditional Raiser’s Edge, such as improved document storage and prospect status history. The last is a Read Optimized Data Store (RODS) that powers many of the lists and analytic features in Raiser’s Edge NXT. Our RODS is a culmination of both of the other data sources, restructured in a way to make it more accessible and performant. To achieve this, an ETL process is utilized to populate RODS from these different data sources.

What this means for Raiser’s Edge NXT and SKY API is that there is a delay between ETL runs to populate RODS. This delay is reflected in Raiser’s Edge NXT with the “List updated about x minutes ago” text at the top of lists sourced from RODS. In the API, this is the cause for the latency in affected lists, and causes the matches across date_modified values. Lists that are affected by this latency are called out in the documentation.

For lists that are powered by RODS, the date_modified values universally reflect the ETL update time instead of the time at which the records were updated in the traditional transactional and new distributed databases. This choice was made to ensure consistency across our different endpoints, some of which are sourced from different data sources. For example, when talking about constituents, the constituent list is sourced from RODS whereas getting a constituent by its ID comes directly from the transactional database, but all of the returned date_modified values will reflect the last ETL update.

Fields Affecting date_modified

The fields available in RODS are a subset of those available in the transactional databases, and the fields surfaced in the API entities are sometimes a subset of those available in RODS. This means that there are sometimes updates which will trigger date_modified to increment, without necessarily reflecting user changes or being reflected in the API. These scenarios should be few and far between though. To illustrate this point, I will use the Constituent list in the Constituent API as an example.

The following fields have the potential to update the date_modified value on constituents, without user input:

• Age increments (ie: a constituent has a birthday and their calculated age is one year older)

• Wealth ratings are updated (occurs once a year)

And the following fields may trigger an update without the changes being reflected in the API, as the correlated fields are not surfaced on the Constituent entity today:

• Solicitor status change

• Prospect classification

• Wealth ratings

• Appeals
   

We typically try to keep the entities we surface in the API as close as possible to the underlying database models, but there are some fields that we either have in our backlog to iteratively include in our entity definitions, or have consciously decided not to surface moving forward. The latter scenario is more common for lists powered by the traditional transactional database and reflects the overall direction of Raiser’s Edge NXT, where we are consciously assessing every feature and field, and deciding if and how we should adopt it for the next generation of fundraising and constituent management. This means that there are some fields that we may never surface, and some that may dramatically change shape as we bring them into the Web View and the API.

As a final note related to fields affecting date_modified, I’d like to call out and emphasize that child entity changes do not percolate up to the parent. For example, if constituent codes are updated or a new gift is created, it does not trigger an update on the constituent date_modfied value. Only changes to the specific entity type will trigger changes on that entity.

Sort_token

The last thing I would like to mention is sort_token, which is returned on all of our syncable list endpoints.When requesting data using the last_modified parameter, a sort_token is returned as part of the next_link on the collection.

This token is essentially a pointer to the last record that was returned, thereby ensuring that a record is never missed. When sorting by system date fields -- date_modified and date_added -- the returned list is guaranteed to be stably sorted, meaning that you can always pick up where you left off. Additionally, this token can be stored and reused indefinitely in the event of sync failures or exceeding daily quotas. We therefore recommend storing this value locally and reusing it where possible to ensure that you never miss a record update.

For more information, including code samples, about how to perform sync operations using SKY API, please review our previous developer blog series post about how to Synchronize Data To A Custom Data Source With SKY API. Additionally, if you have any questions, please either post in the community or submit a comment on this blog directly.

As always, happy coding!