Keysproperty operations Corbelstone Residential

Renewal offer not sent

A lease ending within deadline_days with no renewal offer sent, where the tenant has not given notice and the lease is eligible for renewal. The evidence carries the band price the offer should go out at.

Definition
renewal_not_sent

Params: deadline_days = 60, indexation = 1.03, floor_premium = 0.05, top_floors = 3 · evaluated at Mon 20:00

lettings · v1 · Head of Lettings

0

leases at Mon 20:00

Fixed term ends between now and deadline_days from now; no offer sent before now; no notice from the tenant before now; renewal_eligible. Band price = current rent times indexation, rounded to 5 euros and kept inside the band in force now (top floors may go up to floor_premium above the band's top).

lettings · changed 2026-11-01: First version.

Tests

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

SQL

SELECT 'lease' AS subject_type, l.id AS subject_id, l.id AS lease_id, l.unit_id, l.building_id, l.tenant_id,
       l.end_date - INTERVAL ({deadline_days}) DAY AS started_at,
       CASE WHEN l.end_date - {now} < INTERVAL 30 DAY THEN 'high' ELSE 'medium' END AS severity,
       rb.band_low, rb.band_mid,
       round(rb.band_high * CASE WHEN u.floor >= b.floors - {top_floors} THEN 1 + {floor_premium} ELSE 1 END, 2)
         AS band_high,
       least(greatest(round(l.monthly_rent * {indexation} / 5) * 5, ceil(rb.band_low / 5) * 5),
             floor(rb.band_high * CASE WHEN u.floor >= b.floors - {top_floors} THEN 1 + {floor_premium} ELSE 1 END
                   / 5) * 5) AS band_price,
       json_object('end_date', l.end_date, 'days_to_end', date_diff('day', {now}, l.end_date),
                   'monthly_rent', l.monthly_rent, 'bedrooms', u.bedrooms, 'unit_label', u.label,
                   'building', b.name, 'band_version', rb.version, 'top_floor', u.floor >= b.floors - {top_floors},
                   'deadline_days', {deadline_days}) AS evidence
FROM leases l
JOIN units u ON u.id = l.unit_id
JOIN buildings b ON b.id = l.building_id
JOIN rent_bands rb ON rb.building_id = u.building_id AND rb.bedrooms = u.bedrooms
 AND rb.valid_from <= {now} AND (rb.valid_to IS NULL OR rb.valid_to > {now})
WHERE l.start_date <= {now}
  AND l.end_date + INTERVAL 1 DAY > {now}
  AND l.end_date <= {now} + INTERVAL ({deadline_days}) DAY
  AND l.renewal_eligible
  AND (l.notice_given_at IS NULL OR l.notice_given_at > {now})
  AND (l.moved_out_at IS NULL OR l.moved_out_at > {now})
  AND NOT EXISTS (SELECT 1 FROM renewal_offers o WHERE o.lease_id = l.id AND o.sent_at <= {now}
                  AND o.status <> 'withdrawn')
ORDER BY started_at, l.id