Skip to content

Limits Rules Caching

Limits are the heaviest CPU consumers in SQL. The goal of this feature is to reduce the SQL-intensive operations required to retrieve graduated limit rules by caching the data.

Overview

  • The post limit-checks endpoint uses the heavy operation of spGetGraduatedLimitsMatchingScope to pull user-level limits and backfill them onto a single set of limit periods.

  • Determining the effective limits for a request depends on the input scope as well as graduated params. SQL match ranking is involved to determine priority.

  • Since we know that based on network rail, we always use the same scope structure, we should be able to cache the results when FI or user limits are updated in a table that is tuned for the network.

Implementation

  • We have planned to cache the data in a new table, limits.GraduatedLimitRulesCache, which will capture all the properties of GraduatedLimitRange and LimitPeriods.

  • Additionally, we have added a few scopes (PaycenterMemberFIId, Network, CustomerId, BankAccount) as separate columns to enable faster querying.

  • We created this new table to capture the rules data, as the rules involve more complex logic such as match ranking, backfilling, etc. The goal of this implementation is to eventually move the cache table to an in-memory caching solution (e.g., In-Memory, Redis).

Capturing Rules into Cache Table

The cache table is populated when GraduatedLimitRange or LimitPeriods are added, modified, or deleted, using domain events.

Backfilling Rules of Cached Data

  • Inside the domain event handler, a separate stored procedure is called to backfill the user-level limits with the corresponding FI-level limits, where the limit period of the user limits has a null value in either the Amount or Velocity.

  • The backfill procedure updates the AmountLimitPeriodId or VelocityLimitPeriodId columns with the backfilled FI-level Limit Period Id.

  • The POST limit-checks operation uses the cache (based on a flag) to retrieve the already backfilled data.

Maintenance Operations

Since domain events are triggered only on addition or modification, we have created a few maintenance endpoints to populate the existing rules data into the cache table.

1. Sync Rules Job Endpoint

Performs upsert operation on GraduatedRulesCache table in order to sync with Graduated Limit Range and Limit Periods.

Usage: - Use this endpoint to insert all existing rules into the cache table when enabling caching. - Use this endpoint to update any rules that were not added to the cache table due to an unexpected failure. - Use this endpoint after migration to sync with the cache table.

2. Backfill Rules Job Endpoint

Performs backfill operation on GraduatedRulesCache table for Amount and Velocity.

Usage: - Use this endpoint to backfill all existing rules in the cache table when enabling caching. - Use this endpoint to backfill any rules that were not added to the cache table due to an unexpected failure. - Use this endpoint whenever calling sync rules job endpoint.

3. Clean-up Rules Job Endpoint

Performs a hard delete on the GraduatedRulesCache table to remove entries that were soft deleted in the GraduatedLimitRange table, ensuring data consistency.

Usage: - Use this endpoint to clear out rules that were not deleted in cache table due to an unexpected failure. - Use this endpoint to clear out rules that were not captured appropriately in cache table.

Steps to Follow for Enabling Rules Caching

  1. Set the LimitRulesCachingEnabledProd variable to true in the library.
  2. After deploying the Payment Repo service:
  3. Trigger the sync rules endpoint with all input values set to null to sync all GraduatedLimitRange and LimitPeriods into the GraduatedLimitRulesCache table.
  4. Once the sync rules endpoint has been triggered, trigger the backfill cache endpoint to backfill the user-level limit periods with the corresponding FI-level limit periods.

Steps to Follow for Disabling Rules Caching

  1. Set the LimitRulesCachingEnabledProd variable to false in the library.
  2. Redeploy the services.

Additional Notes

  • After the final migration, the sync rules job endpoint must be triggered with the appropriate PaycenterMemberFIId, followed by the backfill rules job endpoint to sync the migrated rules into the cache table.

  • After the final migration, if there is a delay in triggering the maintenance endpoints, during the post limit check, the system will check the cache table. If no entries are found, it will revert to the existing approach as a fallback to ensure safety. This way, there will be no functional impact, though two procedures will be called to retrieve the rules.

  • Currently, this caching feature is only supported for Zelle, which can be configured within the Networks.