Very simple use case
High value scenario that increases threshold by 50% if previous alert was closed as FALSE POSITIVE:
Scenario logic
alert transaction with amount ≥ 10000 or by 15000 if customer has FALSE POSITIVE alert of same scenario in last 3 months
Setup
Look up scenario handle (it is the last part of url, e.g. from https://demo.salv.com/scenarios/59561449-4a98-471a-bc06-f555d4c76763 the handle is 59561449-4a98-471a-bc06-f555d4c76763)
Previous alerts information is in table
previous_monitoring_alerts
select
true
,new.amount
from transaction new
where new.id = $transactionId
and case
when exists (select 1
from previous_monitoring_alerts
where organisation_id = $organisationId --for performance purposes only your organisation alerts
and person_id = new.person_id -- look at alerts of this particular person
and scenario_handle = '59561449-4a98-471a-bc06-f555d4c76763' -- look at alerts of this particular scenario
and created_time < new.timestamp -- look at alerts that are created before transaction timestamp
and created_time >= new.timestamp - '3 months'::interval -- look at alerts that are created after transaction timestamp - 3 months
and current_status = 'CLOSED_AS_FALSE_POSITIVE' -- look at alerts that have status CLOSED_AS_FALSE_POSITIVE
) then new.amount >= 10000*1.5
else new.amount >= 10000
end
The above version uses the hardcoded value for status of ‘CLOSED_AS_FALSE_POSITIVE’, which is a default setup for alert status. Here is a more flexible version, using custom alert statuses:
select
true
,new.amount
from transaction new
where new.id = $transactionId
and case
when exists (select 1
from previous_monitoring_alerts
where organisation_id = $organisationId --for performance purposes only your organisation alerts
and person_id = new.person_id -- look at alerts of this particular person
and scenario_handle = '59561449-4a98-471a-bc06-f555d4c76763' -- look at alerts of this particular scenario
and created_time < new.timestamp -- look at alerts that are created before transaction timestamp
and created_time >= new.timestamp - '3 months'::interval -- look at alerts that are created after transaction timestamp - 3 months
and status_id IN (select id from monitoring_final_status where organisation_id = $organisationId
and name = 'Closed as False Positive') -- look at alerts that have status Closed as False Positive
) then new.amount >= 10000*1.5
else new.amount >= 10000
end
More exotic use case
We can use the time of the last false positive alert and only take totals from transactions afterwards when monitoring for cumulative value over a period.
Scenario logic
When sum of transaction amounts over 30 days - or the time since the last false positive alert - exceeds stated customer maximum from KYC documentation, generate an alert:
select person_id
, past_total >= max_turnover_per_month
, past_total
, max_turnover_per_month
from
(
select p.id as person_id
, sum(case when past.timestamp > coalesce((select max(created_time) --take the most recent false positive alert time from this scenario
from previous_monitoring_alerts
where organisation_id = $organisationId and person_id = past.person_id
and scenario_handle = '912847-aljifeow-2394jf' --your scenario handle goes here
and current_status = 'CLOSED_AS_FALSE_POSITIVE'), now() - '30 days'::interval)
then past.amount
else 0
end) as past_total
, (p.attributes->>'kyc_max_turnover_per_month')::numeric as max_turnover_per_month
from transaction past
join person p on p.id = past.person_id and p.customer_id = $organisationId
where past.timestamp > date_trunc('day', now() - '30 days'::interval)
and past.timestamp <= date_trunc('day', now())
and p.attributes->>'kyc_max_turnover_per_month' is not null
group by p.id, p.attributes
) sub