Stateless retention period

[nickevansuk]

Background

To minimise the number of RPDE pages a data consumer needs to harvest in order to reach "relevant" data, a retention period can be utilised. "Relevant" data for the purposes of this proposal are records that contain data relating to a events in the future.

Overall approach

An effective retention period can be implemented by having the first page of the feed start from the first relevant record (instead of from the beginning of time). This approach is useful for simple cases where a CRON job is not desirable.

The idea of moving the "first page" is that the data consumer starts harvesting from a point in the feed where all events in the future are ahead of it. The beginning of the feed effectively moves, so when the data consumer starts from the beginning of the feed, they always start from the earliest point that’s relevant, but anyone who’s already consuming the data will be unaffected.

The approaches below all maintain the integrity of the core RPDE invariants.

The main challenge is that the RPDE query is a > query, and hence just finding and using the earliest relevant record in the feed as the "default value" parameters for the query will exclude that record.

Approach 1: Precise

Alter the RPDE query slightly such that it precisely includes the "default value"

To get the "default values"

  --get default values, to be executed ONLY if @afterTimestamp and @afterId NOT provided
  SELECT @firstTimestamp = modified, @firstId = id
    FROM ...
   WHERE startDate >= @now AND state <> "deleted"
ORDER BY modified, id
   LIMIT 1

In the RPDE query

   --include this WHERE clause only if default values are available (first page)
   WHERE (modified = @firstTimestamp AND id >= @firstId) OR (modified > @firstTimestamp)
   --include this WHERE clause only if @afterTimestamp and @afterId provided (not first page)
   WHERE (modified = @afterTimestamp AND id > @afterId) OR (modified > @afterTimestamp)
      -- if default values are not available and @afterTimestamp and @afterId not provided, do not include WHERE clause
ORDER BY modified, id

Approach 2: Rough

Alter the RPDE query slightly such that it includes the "default value", and anything that was modified at the same time

This approach does not take into account bulk updates

To get the "default values"

  --get default values, to be executed ONLY if @afterTimestamp and @afterId NOT provided
  SELECT @firstTimestamp = modified
    FROM ...
   WHERE startDate >= @now AND state <> "deleted"
ORDER BY modified
   LIMIT 1

In the RPDE query

   --include this WHERE clause only if default values are available (first page)
   WHERE (modified >= @firstTimestamp)
   --include this WHERE clause only if @afterTimestamp and @afterId provided (not first page)
   WHERE (modified = @afterTimestamp AND id > @afterId) OR (modified > @afterTimestamp)
   -- if default values are not available and @afterTimestamp and @afterId not provided, do not include WHERE clause
ORDER BY modified, id

Approach 3: Simple

Use id - 1 to ensure that the default value is included (note modified - 1 can be used for non-integer id values, though this will not be as precise for bulk updates).

To get the "default values"

  --get default values, to be executed ONLY if @afterTimestamp and @afterId NOT provided
  SELECT @afterTimestamp = modified, @afterId = id - 1
    FROM ...
   WHERE startDate >= @now AND state <> "deleted"
ORDER BY modified, id
   LIMIT 1

In the RPDE query

Note no changes to the RPDE query are required

   --include this WHERE clause only if @afterTimestamp and @afterId provided OR default values are provided
   WHERE (modified = @afterTimestamp AND id > @afterId)
      OR (modified > @afterTimestamp)
ORDER BY modified, id

Approach 4: Specific

Use two queries to obtain the precise value to use in the existing RPDE query

Get the first relevant record, and use this to attain the default values

  --to be executed ONLY if @afterTimestamp and @afterId NOT provided

  --get first relevant record
  SELECT @relevantAfterTimestamp = modified, @relevantAfterId = id
    FROM ...
   WHERE startDate >= @now AND state <> "deleted"
ORDER BY modified, id
   LIMIT 1

  --get default vlaues
  SELECT @afterTimestamp = modified, @afterId = id
    FROM ...
   WHERE modified < @relevantAfterTimestamp AND id < @relevantAfterId
ORDER BY modified DESC, id DESC
   LIMIT 1

In the RPDE query

Note no changes to the RPDE query are required

   --include this WHERE clause only if @afterTimestamp and @afterId provided OR default values are provided
   WHERE (modified = @afterTimestamp AND id > @afterId)
      OR (modified > @afterTimestamp)
ORDER BY modified, id

[lukehesluke]

@nickevansuk I suspect this is implicit, but it might be worth saying that this can only be used for ScheduledSession and Slot feeds

Also, I'm excited by the harvesting speedup gains that will be afforded by implementation of this proposal 🙏

[lukehesluke]

As for approaches, I agree with Approach 1

• Approach 2: Bulk edits are a fairly common occurrence. Thousands (millions? billions?!) of items can have the same timestamp if there's some bulk edit process. And therefore, it's more efficient to use the ID sub-sort where possible
• Approach 3: String ids are fairly common (surprisingly?), so this is quite inefficient. Might be interesting to know if there's a simple way to decrement a string? 🤔
• Approach 4: This is also good. But two things:
  • The problem I have with this is that the comparison / sorting logic is very delicate. For example, I believe the

    WHERE modified < @relevantAfterTimestamp AND id < @relevantAfterId
    

    line needs some additional caveats. e.g. a session with a lower timestamp and a higher ID would be excluded by this filter, but it may in fact be the right session
  • I would recommend a Common Table Expression (which I believe is ANSI SQL) for slight performance gain

[nickevansuk]

Agreed, Approach 1 strikes a good balance of being easy to implement / less error prone, while also being universally applicable (even for those with string ids)

I've updated the OpenActive documentation to reflect this approach, and of course welcome any further comments.

[civsiv]

Approach 1 looks to be the best approach to me too, easiest to understand as well as implement

[nickopris]

I was in a call with Tim and I suggested to not have past events at all in the feed. There must be a reason for having past events in the feed that I don't fully understand. Unlike perpetual data like brand, organisation, etc., Sessions and Slots are no longer usable by the Consumers once their time has passed.

I was offered the explanation that consumers need to know the status of the Event and the publishers should mark them as unpublished, keep them for 7 days then delete them. This workflow is not something you find on most CMS out there and can make the adoption pretty slow. On the other hand, asking the consumers to simply delete what is no longer in the feed but was previously ingested is not a big task, or is it?

Can anyone make me understand why we need old events in the feed?

[nickevansuk]

This is because if an item is deleted, then it "disappears" from the feed, and in that case there is no way for any system processing the feed to know that it is deleted. Hence items that "disappear" live on forever in downstream applications, which is likely not desirable

Many CMS systems have the concept of "soft-delete" (a "deleted" flag against a record), which is all that would be needed to implement the "keep for at least 7 days".

See also: https://developer.openactive.io/publishing-data/data-feeds/how-a-data-feed-works#items-must-never-disappear

(As an aside: for your usecase, I'd suggest using Option 2 from the documentation, which would solve the retention issue. I'd separately suggest implementing "soft-delete" to cover the deleted issue.)