Definitions
The claims terms the operator, its detectors and Ask all run on. Each has an owner, a version, SQL and tests. Edits make a new version.Ring indicator
ring_indicator unit: rings operator detectorOne phone number or bank account given on claims by three or more unrelated households within 90 days. Families share accounts and claimants sometimes give their garage's number; neither is a ring.
fraudHead of Special Investigations · fraud
v2
A claimant phone or payout bank account that appears on claims from at least min_households different households (surname and address) reported in the last window_days. A phone that belongs to a garage or contractor in the vendor list is not counted. The ring becomes visible when its min_households-th claim is reported.
latest change: Garage and contractor phone numbers no longer count; claimants often give their garage's number.
- pass count_betweenobserved 7, expected between 1.0 and 12.0
- pass uniqueobserved {'duplicates': 0}, expected ring_key unique
- pass detector_shapeobserved {'missing_columns': [], 'bad_rows': 0, 'rows': 7}, expected columns ['subject_type', 'subject_id', 'started_at', 'evidence'], evidence is an object
SQL, rendered for now
WITH recent AS (
SELECT c.id, c.claim_no, c.reported_at, c.vendor_id, c.claimant_phone, c.claimant_bank,
h.surname || '|' || h.address AS household
FROM claims c JOIN policyholders h ON h.id = c.holder_id
WHERE c.reported_at <= TIMESTAMP 'Mon 17:00:00' AND c.reported_at >= TIMESTAMP 'Mon 17:00:00' - INTERVAL (90) DAY
),
keyed AS (
SELECT 'phone:' || claimant_phone AS ring_key, * FROM recent
WHERE claimant_phone NOT IN (SELECT phone FROM vendors)
UNION ALL
SELECT 'bank:' || claimant_bank, * FROM recent
),
rings AS (
SELECT ring_key, count(DISTINCT household) AS households, count(*) AS claims,
list(id ORDER BY reported_at, id) AS claim_ids, list(claim_no ORDER BY reported_at, id) AS claim_nos,
list(reported_at ORDER BY reported_at, id) AS reported, max(id) AS latest_claim_id,
mode(vendor_id) AS vendor_id, count(DISTINCT vendor_id) AS garages
FROM keyed GROUP BY ring_key
HAVING count(DISTINCT household) >= 3
)
SELECT 'ring' AS subject_type, ring_key AS subject_id, ring_key, latest_claim_id AS claim_id, claim_ids,
vendor_id, reported[3] AS started_at,
CASE WHEN households >= 5 THEN 'critical' ELSE 'high' END AS severity,
json_object('shared', split_part(ring_key, ':', 1), 'value', split_part(ring_key, ':', 2),
'households', households, 'claims', claims, 'claim_nos', claim_nos,
'garages', garages) AS evidence
FROM rings
ORDER BY started_at, ring_key
Parameters
change one, see what changes today, then save it as a new version with a noteHistory
append-only; nothing is edited in place- definitionHead of Special Investigations fraud v2 Garage and contractor phone numbers no longer count; claimants often give their garage's number.
- definitionHead of Special Investigations fraud v1 First version.