Build Your Own Unit Turn Board

-- Notice recorded on the lease: a turn opens. Status is not
-- in the column list; with no milestones set it computes to
-- notice_given on its own.
INSERT INTO unit_turn
  (unit_id, vacating_lease_id, notice_id, notice_received_on, expected_move_out_on)
SELECT l.unit_id, l.id, n.id, n.served_on, n.effective_on
FROM lease l
JOIN notice n ON n.lease_id = l.id
          AND n.notice_type = 'tenant_notice_to_vacate'
WHERE l.id = :lease_id;

One open turn per unit. Add a partial unique index on unit_id where new_lease_id IS NULL (an open turn is one that no new lease has closed). A second notice on the same unit is a data problem you want surfaced at insert time, not discovered on the board.

-- Opening a turn generates its task chain. Gaps in seq leave room
-- for inserts (carpet replacement between repairs and paint).
INSERT INTO unit_turn_task (unit_turn_id, seq, task_type, title) VALUES
  (:turn_id, 10, 'repair',     'Repairs from the move-out walk'),
  (:turn_id, 20, 'painting',   'Paint: patched walls and ceilings'),
  (:turn_id, 30, 'cleaning',   'Deep clean to move-in standard'),
  (:turn_id, 40, 'rekey',      'Rekey locks and reset codes'),
  (:turn_id, 50, 'final_walk', 'Final walk with photos');

Two boundaries keep this table honest. Work discovered during the turn that exceeds turn scope, a failed compressor, a slab leak, becomes a maintenance_request row linked to the unit, not a fifth task squeezed into the chain; the turn tracks readiness, not capital projects. And the move-out inspection's findings do double duty: its inspection_item rows are the evidence your deposit_deduction rows cite against the security_deposit, which is a whole discipline of its own; we cover it in the move-out inspection and deposit system guide.

-- Keys returned: record the fact. Status computes to
-- move_out_inspection on its own.
UPDATE unit_turn
SET    moved_out_on = CURRENT_DATE,
       move_out_inspection_id = :inspection_id
WHERE  id = :turn_id;

-- Crew on site: make-ready begins.
UPDATE unit_turn
SET    make_ready_started_on = CURRENT_DATE
WHERE  id = :turn_id;

-- And the guard rail, demonstrated on purpose: run the same
-- statement on a turn whose keys never came back, and Postgres
-- itself refuses the row. This one is supposed to fail:
UPDATE unit_turn
SET    make_ready_started_on = CURRENT_DATE
WHERE  id = :turn_id;   -- a turn still in notice_given
-- ERROR: new row for relation "unit_turn" violates check
--        constraint (make_ready requires moved_out_on)

-- make_ready to market_ready has an extra condition: proof.
-- 0 rows updated = something in the chain is not verified yet.
UPDATE unit_turn ut
SET    market_ready_on = CURRENT_DATE
WHERE  ut.id = :turn_id
  AND  ut.make_ready_started_on IS NOT NULL
  AND NOT EXISTS (
    SELECT 1 FROM unit_turn_task t
    WHERE t.unit_turn_id = ut.id
      AND t.status NOT IN ('verified', 'skipped'));

The final walk itself is the chain's last unit_turn_task (task type final_walk), and flipping it to verified is what unlocks the milestone; record the walk as a routine inspection on the unit with inspection_item children, one per checklist line, each ideally carrying a photo. That photo set is worth more than the checkbox: it is the documented condition of the unit at its best, timestamped, which is the baseline every future damage conversation gets measured against. Reviewing those photos at scale is the part that breaks down when a human has to do it for every turn, and it is exactly the review RapidEye automates for operators; the walk photos your team already takes become the evidence layer instead of a folder nobody opens.

-- The board: every open turn, aged. Sort oldest-first per column.
SELECT p.name AS property, u.unit_number, ut.status,
       CURRENT_DATE - COALESCE(ut.market_ready_on, ut.make_ready_started_on,
                               ut.moved_out_on, ut.notice_received_on)
         AS days_in_stage
FROM   unit_turn ut
JOIN   unit u     ON u.id = ut.unit_id
JOIN   property p ON p.id = u.property_id
WHERE  ut.new_lease_id IS NULL
ORDER BY ut.status, days_in_stage DESC;

-- The bottleneck: which stage is eating the portfolio's days.
SELECT status, COUNT(*) AS turns,
       ROUND(AVG(CURRENT_DATE - COALESCE(market_ready_on, make_ready_started_on,
                                         moved_out_on, notice_received_on)), 1) AS avg_days
FROM   unit_turn
WHERE  new_lease_id IS NULL
GROUP BY status
ORDER BY avg_days DESC;

The bottleneck query is the one that changes behavior. A wall of individual late units invites individual excuses; an average that says make-ready holds units 3 times longer than any other stage says hire a second painter or fix vendor scheduling. Once the turn closes, the vacancy it cost is also auditable after the fact: the gap between the last rent charge on the old lease and the first on the new one in rent_ledger_entry is the realized days-vacant, priced in actual dollars, per turn.