Definitions
The definitions the operator and Ask both run on. Each has an owner, a version, SQL and tests. Edits make a new version.Free time expiring
free_time_expiring unit: containers operator detector canonical: opsA container that is available at the destination terminal (discharged, cleared by customs, bill of lading released), has not left the terminal, has no pickup booked, and whose free time ends within the warning horizon (or has already ended). After free time the terminal charges demurrage every day.
opsHead of Operations · ops
v1canonical
Available (an 'available' event at or before now, within lookback_days), no gate-out at or before now, no pickup appointment booked at or before now and not cancelled, and last_free_at minus warn_hours at or before now. The evidence counts the terminal's pickup slots that still have room between two hours from now and the end of free time, as of now.
latest change: First version.
- pass count_betweenobserved 6, expected between 0.0 and 400.0
- pass uniqueobserved {'duplicates': 0}, expected container_id unique
- pass tuesday_morningobserved 1, expected between 1.0 and 200.0
- pass detector_shapeobserved {'missing_columns': [], 'bad_rows': 0, 'rows': 6}, expected columns ['subject_type', 'subject_id', 'started_at', 'evidence'], evidence is an object
SQL, rendered for now
WITH avail AS (
SELECT e.container_id, min(e."at") AS available_at
FROM shipment_events e
WHERE e.type = 'available' AND e.container_id IS NOT NULL AND e."at" <= TIMESTAMP 'Tue 18:00:00'
AND e."at" >= TIMESTAMP 'Tue 18:00:00' - INTERVAL (30) DAY
GROUP BY e.container_id
),
gone AS (
SELECT DISTINCT e.container_id FROM shipment_events e
WHERE e.type = 'gate_out' AND e."at" <= TIMESTAMP 'Tue 18:00:00' AND e."at" >= TIMESTAMP 'Tue 18:00:00' - INTERVAL (30) DAY
),
booked AS (
SELECT DISTINCT a.container_id FROM trucking_appointments a
WHERE a.kind = 'pickup' AND a.booked_at <= TIMESTAMP 'Tue 18:00:00' AND (a.cancelled_at IS NULL OR a.cancelled_at > TIMESTAMP 'Tue 18:00:00')
),
risk AS (
SELECT c.id AS container_id, c.number, c.equipment, c.last_free_at, s.id AS shipment_id, s.ref, s.shipper_id,
s.dest_id, v.available_at
FROM avail v
JOIN containers c ON c.id = v.container_id
JOIN shipments s ON s.id = c.shipment_id
LEFT JOIN gone g ON g.container_id = c.id
LEFT JOIN booked b ON b.container_id = c.id
WHERE g.container_id IS NULL AND b.container_id IS NULL
AND c.last_free_at - INTERVAL (48) HOUR <= TIMESTAMP 'Tue 18:00:00'
),
room AS (
SELECT t.location_id, t.slot_start, t.slot_end, t.capacity - count(a.id) AS free
FROM terminal_slots t
LEFT JOIN trucking_appointments a ON a.kind = 'pickup' AND a.location_id = t.location_id
AND a.slot_start = t.slot_start AND a.booked_at <= TIMESTAMP 'Tue 18:00:00' AND (a.cancelled_at IS NULL OR a.cancelled_at > TIMESTAMP 'Tue 18:00:00')
WHERE t.location_id IN (SELECT dest_id FROM risk)
AND t.slot_start >= TIMESTAMP 'Tue 18:00:00' + INTERVAL 2 HOUR AND t.slot_start < TIMESTAMP 'Tue 18:00:00' + INTERVAL (48 + 24) HOUR
GROUP BY t.location_id, t.slot_start, t.slot_end, t.capacity
)
SELECT 'container' AS subject_type, r.container_id AS subject_id, r.container_id, r.shipment_id, r.shipper_id,
greatest(r.available_at, r.last_free_at - INTERVAL (48) HOUR) AS started_at,
CASE WHEN r.last_free_at <= TIMESTAMP 'Tue 18:00:00' THEN 'critical' ELSE 'high' END AS severity,
json_object(
'container', r.number, 'shipment', r.ref, 'equipment', r.equipment,
'terminal_id', r.dest_id, 'terminal', l.name,
'available_at', r.available_at, 'last_free_at', r.last_free_at,
'hours_left', round(date_diff('minute', TIMESTAMP 'Tue 18:00:00', r.last_free_at) / 60.0, 1),
'slots_with_room', (SELECT count(*) FROM room m WHERE m.location_id = r.dest_id AND m.free > 0
AND m.slot_end <= r.last_free_at),
'earliest_slot_with_room', (SELECT min(m.slot_start) FROM room m WHERE m.location_id = r.dest_id
AND m.free > 0),
'shipper', sh.name, 'account_owner', sh.account_owner
) AS evidence
FROM risk r
JOIN locations l ON l.id = r.dest_id
JOIN shippers sh ON sh.id = r.shipper_id
ORDER BY started_at, r.container_id
Parameters
change one, see what changes in the window, then save it as a new version with a noteHistory
append-only; nothing is edited in place- definitionHead of Operations ops v1 First version.