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.Photo taken before the loss
photo_before_loss unit: claims operator detectorA photo of the damage whose own metadata says it was taken well before the loss happened. Phones with a wrong clock and claimants unsure of the date make this the noisiest check on the list.
fraudHead of Special Investigations · fraud
v2
An open claim with a photo received in the last 90 days whose taken_at is more than hours_before before the loss time. A taken_at before earliest_year is a phone's factory clock, and one almost exactly a year before the loss (within two days) is a clock set a year back; neither counts. One row per claim, the oldest photo.
latest change: Photos about a year off are clock errors, not evidence; the margin went from 24 to 30 hours.
- pass count_betweenobserved 30, expected between 1.0 and 80.0
- pass uniqueobserved {'duplicates': 0}, expected claim_id unique
- pass detector_shapeobserved {'missing_columns': [], 'bad_rows': 0, 'rows': 30}, expected columns ['subject_type', 'subject_id', 'started_at', 'evidence'], evidence is an object
SQL, rendered for now
SELECT 'claim' AS subject_type, c.id AS subject_id, c.id AS claim_id, arg_min(d.id, d.taken_at) AS document_id,
min(d.received_at) AS started_at, 'high' AS severity,
json_object('claim_no', any_value(c.claim_no), 'loss_at', any_value(c.loss_at),
'photo_taken_at', min(d.taken_at), 'photo_ref', arg_min(d.ref, d.taken_at),
'device', arg_min(d.device, d.taken_at),
'days_before_loss', round(date_diff('hour', min(d.taken_at), any_value(c.loss_at)) / 24.0, 1),
'photos_on_file', count(*), 'reported_at', any_value(c.reported_at)) AS evidence
FROM documents d
JOIN claims c ON c.id = d.claim_id
WHERE d.kind = 'photo' AND d.received_at <= TIMESTAMP 'Mon 17:00:00' AND d.received_at >= TIMESTAMP 'Mon 17:00:00' - INTERVAL 90 DAY
AND d.taken_at < c.loss_at - INTERVAL (30) HOUR
AND year(d.taken_at) >= 2010
AND abs(date_diff('hour', d.taken_at + INTERVAL 365 DAY, c.loss_at)) > 48
AND (c.closed_at IS NULL OR c.closed_at > TIMESTAMP 'Mon 17:00:00')
GROUP BY c.id
ORDER BY started_at, c.id
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 Photos about a year off are clock errors, not evidence; the margin went from 24 to 30 hours.
- definitionHead of Special Investigations fraud v1 First version: 24 hours.