Keysproperty operations Corbelstone Residential

Rent arrears (reminder due)

A lease whose oldest unpaid rent is at least arrears_days old and whose next step on the arrears schedule has not been taken: a reminder at day 10, 20 and 30 (stage 1, 2, 3), and a legal review from day 45 (stage 4). Leases already handed to legal are left out. Payments are applied to the oldest charge first.

Definition
rent_arrears

Params: arrears_days = 10, reminder_step_days = 10, legal_review_days = 45 · evaluated at Mon 20:00

credit_control · v1 · Head of Credit Control

5

leases at Mon 20:00

Oldest unpaid charge (first in, first out) due at least arrears_days before now; the stage is set by the days overdue; a row appears only while the reminder for that stage has not been sent, or, from legal_review_days, while no legal notice has been served.

credit control · changed 2026-11-01: First version.

Tests

  • count_between min 1.0 max 400.0
  • unique on lease_id
  • detector_shape

SQL

WITH charges AS (
  SELECT lease_id, due_date, amount,
         sum(amount) OVER (PARTITION BY lease_id ORDER BY due_date, id ROWS UNBOUNDED PRECEDING) AS cum_charged
  FROM rent_ledger WHERE kind = 'charge' AND posted_at <= {now}
),
paid AS (
  SELECT lease_id, -sum(amount) AS paid, max(posted_at) AS last_paid_at
  FROM rent_ledger WHERE kind IN ('payment', 'concession') AND posted_at <= {now} GROUP BY lease_id
),
owed AS (
  SELECT c.lease_id, min(c.due_date) AS oldest_due, max(c.cum_charged) - coalesce(any_value(p.paid), 0) AS balance,
         any_value(p.last_paid_at) AS last_paid_at
  FROM charges c LEFT JOIN paid p USING (lease_id)
  WHERE c.cum_charged > coalesce(p.paid, 0) + 0.005
  GROUP BY c.lease_id
),
staged AS (
  SELECT o.*, date_diff('second', o.oldest_due, {now}) / 86400.0 AS days_overdue,
         CASE WHEN date_diff('second', o.oldest_due, {now}) >= {legal_review_days} * 86400 THEN 4
              ELSE least(3, CAST(floor(date_diff('second', o.oldest_due, {now}) / 86400.0 / {reminder_step_days}) AS INTEGER))
         END AS stage
  FROM owed o
  WHERE o.oldest_due <= {now} - INTERVAL ({arrears_days}) DAY AND o.balance > 0.005
),
sent AS (
  SELECT lease_id, max(stage) FILTER (WHERE kind = 'arrears_reminder') AS reminded,
         count(*) FILTER (WHERE kind = 'legal_notice') AS legal
  FROM tenant_notices WHERE sent_at <= {now} AND status <> 'cancelled' GROUP BY lease_id
)
SELECT 'lease' AS subject_type, s.lease_id AS subject_id, s.lease_id, l.unit_id, l.building_id, l.tenant_id,
       s.oldest_due + CASE WHEN s.stage = 4 THEN INTERVAL ({legal_review_days}) DAY
                           ELSE INTERVAL ({reminder_step_days}) DAY * s.stage END AS started_at,
       CASE WHEN s.stage = 4 THEN 'high' ELSE 'medium' END AS severity,
       s.stage,
       json_object('balance', round(s.balance, 2), 'oldest_due', s.oldest_due,
                   'days_overdue', round(s.days_overdue, 1), 'monthly_rent', l.monthly_rent,
                   'months_owed', round(s.balance / l.monthly_rent, 2), 'last_paid_at', s.last_paid_at,
                   'reminders_sent_stage', coalesce(t.reminded, 0), 'lease_status', l.status,
                   'unit_label', u.label, 'building', b.name, 'threshold_days', {arrears_days}) AS evidence
FROM staged s
JOIN leases l ON l.id = s.lease_id
JOIN units u ON u.id = l.unit_id
JOIN buildings b ON b.id = l.building_id
LEFT JOIN sent t ON t.lease_id = s.lease_id
WHERE coalesce(t.legal, 0) = 0
  AND (s.stage = 4 OR coalesce(t.reminded, 0) < s.stage)
ORDER BY started_at, s.lease_id