appointment-optimization
Scheduling optimization algorithms for veterinary clinics including slot availability, multi-service
提供方 Ai-Whisperers|开源
Appointment Optimization Guide
Overview
This skill covers scheduling optimization patterns for veterinary clinics, designed to maximize throughput while maintaining quality of care.
1. Slot Availability Algorithm
Time Slot Generation
// lib/scheduling/slot-generator.ts
interface TimeSlot {
start: Date;
end: Date;
available: boolean;
vetId?: string;
serviceTypes: string[];
priority: 'normal' | 'emergency' | 'buffer';
}
interface ScheduleConfig {
businessHours: {
[day: number]: { open: string; close: string; breaks?: { start: string; end: string }[] };
};
slotDuration: number; // minutes
bufferBetweenSlots: number; // minutes
emergencySlotRatio: number; // 0-1, percentage of slots reserved for emergencies
maxAdvanceBookingDays: number;
}
const DEFAULT_CONFIG: ScheduleConfig = {
businessHours: {
1: { open: '08:00', close: '18:00', breaks: [{ start: '12:00', end: '14:00' }] }, // Monday
2: { open: '08:00', close: '18:00', breaks: [{ start: '12:00', end: '14:00' }] },
3: { open: '08:00', close: '18:00', breaks: [{ start: '12:00', end: '14:00' }] },
4: { open: '08:00', close: '18:00', breaks: [{ start: '12:00', end: '14:00' }] },
5: { open: '08:00', close: '18:00', breaks: [{ start: '12:00', end: '14:00' }] },
6: { open: '08:00', close: '12:00' }, // Saturday
// 0 (Sunday) not included = closed
},
slotDuration: 30,
bufferBetweenSlots: 5,
emergencySlotRatio: 0.1, // 10% reserved for emergencies
maxAdvanceBookingDays: 30,
};
export function generateAvailableSlots(
date: Date,
vetId: string,
existingAppointments: Appointment[],
config: ScheduleConfig = DEFAULT_CONFIG
): TimeSlot[] {
const dayOfWeek = date.getDay();
const hours = config.businessHours[dayOfWeek];
if (!hours) return []; // Closed this day
const slots: TimeSlot[] = [];
const [openHour, openMin] = hours.open.split(':').map(Number);
const [closeHour, closeMin] = hours.close.split(':').map(Number);
let current = new Date(date);
current.setHours(openHour, openMin, 0, 0);
const closeTime = new Date(date);
closeTime.setHours(closeHour, closeMin, 0, 0);
while (current < closeTime) {
const slotEnd = new Date(current.getTime() + config.slotDuration * 60000);
// Check if slot falls within a break
const inBreak = hours.breaks?.some(breakPeriod => {
const [breakStartH, breakStartM] = breakPeriod.start.split(':').map(Number);
const [breakEndH, breakEndM] = breakPeriod.end.split(':').map(Number);
const breakStart = new Date(date);
breakStart.setHours(breakStartH, breakStartM, 0, 0);
const breakEnd = new Date(date);
breakEnd.setHours(breakEndH, breakEndM, 0, 0);
return current >= breakStart && current < breakEnd;
});
if (!inBreak) {
// Check if slot conflicts with existing appointments
const hasConflict = existingAppointments.some(apt =>
apt.vet_id === vetId &&
new Date(apt.start_time) < slotEnd &&
new Date(apt.end_time) > current
);
// Determine if this is an emergency-reserved slot
const slotIndex = slots.length;
const isEmergencySlot = slotIndex % Math.round(1 / config.emergencySlotRatio) === 0;
slots.push({
start: new Date(current),
end: slotEnd,
available: !hasConflict && (!isEmergencySlot || isWithinEmergencyWindow(current)),
vetId,
serviceTypes: ['general'], // Can be customized per vet
priority: isEmergencySlot ? 'emergency' : 'normal',
});
}
// Move to next slot (including buffer)
current = new Date(current.getTime() + (config.slotDuration + config.bufferBetweenSlots) * 60000);
}
return slots;
}
// Emergency slots become available 2 hours before
function isWithinEmergencyWindow(slotTime: Date): boolean {
const now = new Date();
const hoursUntilSlot = (slotTime.getTime() - now.getTime()) / (1000 * 60 * 60);
return hoursUntilSlot <= 2;
}
Optimal Slot Selection
// lib/scheduling/slot-optimizer.ts
interface SlotScore {
slot: TimeSlot;
score: number;
reasons: string[];
}
interface OptimizationFactors {
preferredTime?: 'morning' | 'afternoon' | 'any';
urgency: 'routine' | 'soon' | 'urgent';
serviceType: string;
petSpecies: string;
ownerHistory: {
noShowRate: number;
preferredTimes: string[];
lastVisitDate?: Date;
};
}
export function rankAvailableSlots(
slots: TimeSlot[],
factors: OptimizationFactors
): SlotScore[] {
return slots
.filter(slot => slot.available)
.map(slot => {
let score = 100;
const reasons: string[] = [];
// Time preference scoring
const hour = slot.start.getHours();
if (factors.preferredTime === 'morning' && hour >= 8 && hour < 12) {
score += 20;
reasons.push('Horario preferido (mañana)');
} else if (factors.preferredTime === 'afternoon' && hour >= 14 && hour < 18) {
score += 20;
reasons.push('Horario preferido (tarde)');
}
// Urgency scoring - prioritize sooner slots
if (factors.urgency === 'urgent') {
const hoursUntil = (slot.start.getTime() - Date.now()) / (1000 * 60 * 60);
score += Math.max(0, 50 - hoursUntil * 2); // More points for sooner slots
reasons.push('Cita urgente - horario temprano');
} else if (factors.urgency === 'soon') {
const daysUntil = (slot.start.getTime() - Date.now()) / (1000 * 60 * 60 * 24);
if (daysUntil <= 3) {
score += 15;
reasons.push('Disponibilidad próxima');
}
}
// Owner history - avoid times with high no-show probability
if (factors.ownerHistory.noShowRate > 0.2) {
// Prefer slots that don't match their usual no-show pattern
// (This would require more data analysis in practice)
score -= 10;
reasons.push('Ajuste por historial de asistencia');
}
// Slot efficiency - prefer slots that fill gaps
if (isGapFiller(slot)) {
score += 10;
reasons.push('Optimiza horario del veterinario');
}
// Species-specific timing (e.g., cats prefer quieter times)
if (factors.petSpecies === 'cat' && (hour === 8 || hour >= 16)) {
score += 15;
reasons.push('Horario tranquilo para gatos');
}
return { slot, score, reasons };
})
.sort((a, b) => b.score - a.score);
}
function isGapFiller(slot: TimeSlot): boolean {
// Implementation would check surrounding appointments
// to determine if this slot fills a gap efficiently
return false;
}
2. Multi-Service Bundling
Service Bundle Detection
// lib/scheduling/bundling.ts
interface Service {
id: string;
name: string;
duration: number;
category: string;
requiresVet: boolean;
canBundleWith: string[]; // Service IDs
}
interface ServiceBundle {
services: Service[];
totalDuration: number;
savings: number; // Time saved vs separate appointments
suggestedOrder: string[]; // Service IDs in optimal order
}
const BUNDLE_RULES: Record<string, string[]> = {
'consulta': ['vacuna', 'desparasitacion', 'microchip'],
'vacuna': ['desparasitacion', 'consulta'],
'peluqueria': ['bano', 'corte_unas'],
'cirugia_esterilizacion': ['microchip', 'vacuna_antirrabica'],
'limpieza_dental': ['extraccion_dental', 'consulta'],
};
export function suggestBundles(
requestedService: Service,
petHistory: PetHistory,
availableServices: Service[]
): ServiceBundle[] {
const bundles: ServiceBundle[] = [];
// Check what services can be bundled with requested service
const bundleable = BUNDLE_RULES[requestedService.id] || [];
// Check pet's pending/due services
const pendingServices = getPendingServices(petHistory);
for (const serviceId of bundleable) {
const service = availableServices.find(s => s.id === serviceId);
if (!service) continue;
// Check if pet needs this service
const isPending = pendingServices.includes(serviceId);
const isDue = isDueForService(petHistory, serviceId);
if (isPending || isDue) {
bundles.push({
services: [requestedService, service],
totalDuration: calculateBundleDuration([requestedService, service]),
savings: calculateTimeSavings([requestedService, service]),
suggestedOrder: determineBestOrder([requestedService, service]),
});
}
}
return bundles.sort((a, b) => b.savings - a.savings);
}
function calculateBundleDuration(services: Service[]): number {
// Bundled services share prep time
const baseDuration = services.reduce((sum, s) => sum + s.duration, 0);
const prepTimeSaved = (services.length - 1) * 10; // 10 min prep per service
return baseDuration - prepTimeSaved;
}
function calculateTimeSavings(services: Service[]): number {
const separateTotal = services.reduce((sum, s) => sum + s.duration + 30, 0); // 30 min between appointments
const bundledTotal = calculateBundleDuration(services);
return separateTotal - bundledTotal;
}
function determineBestOrder(services: Service[]): string[] {
// Order by: exam first, then procedures, then vaccines, then cosmetic
const priority: Record<string, number> = {
consulta: 1,
examen: 1,
cirugia: 2,
limpieza_dental: 2,
vacuna: 3,
desparasitacion: 3,
microchip: 4,
peluqueria: 5,
bano: 5,
};
return services
.sort((a, b) => (priority[a.id] || 99) - (priority[b.id] || 99))
.map(s => s.id);
}
function getPendingServices(history: PetHistory): string[] {
// Check for overdue vaccines, pending procedures, etc.
const pending: string[] = [];
if (history.lastVaccineDate && daysSince(history.lastVaccineDate) > 365) {
pending.push('vacuna');
}
if (history.lastDewormingDate && daysSince(history.lastDewormingDate) > 90) {
pending.push('desparasitacion');
}
if (!history.hasMicrochip) {
pending.push('microchip');
}
return pending;
}
function isDueForService(history: PetHistory, serviceId: string): boolean {
// Check service-specific due dates
switch (serviceId) {
case 'limpieza_dental':
return history.lastDentalCleaning && daysSince(history.lastDentalCleaning) > 365;
case 'vacuna_antirrabica':
return history.lastRabiesVaccine && daysSince(history.lastRabiesVaccine) > 365;
default:
return false;
}
}
3. No-Show Prediction
Prediction Model
// lib/scheduling/no-show-prediction.ts
interface NoShowFactors {
dayOfWeek: number;
hourOfDay: number;
daysUntilAppointment: number;
previousNoShows: number;
previousAppointments: number;
appointmentType: string;
weatherForecast?: 'sunny' | 'rainy' | 'stormy';
isFirstVisit: boolean;
remindersSent: number;
confirmationReceived: boolean;
}
interface PredictionResult {
probability: number; // 0-1
riskLevel: 'low' | 'medium' | 'high';
suggestedActions: string[];
}
// Simple logistic regression-style weights (would be trained on historical data)
const WEIGHTS = {
intercept: -2.5,
previousNoShowRate: 3.0,
noConfirmation: 1.5,
longWait: 0.05, // per day
mondayMorning: 0.8,
fridayAfternoon: 0.6,
firstVisit: -0.5, // First visits less likely to no-show
rainyWeather: 0.4,
stormyWeather: 1.0,
noReminders: 1.2,
surgeryAppointment: -1.0, // Surgeries less likely to no-show
};
export function predictNoShow(factors: NoShowFactors): PredictionResult {
let logit = WEIGHTS.intercept;
// Previous no-show history
if (factors.previousAppointments > 0) {
const noShowRate = factors.previousNoShows / factors.previousAppointments;
logit += WEIGHTS.previousNoShowRate * noShowRate;
}
// Confirmation status
if (!factors.confirmationReceived) {
logit += WEIGHTS.noConfirmation;
}
// Days until appointment (longer wait = higher no-show)
logit += WEIGHTS.longWait * Math.max(0, factors.daysUntilAppointment - 3);
// Day/time factors
if (factors.dayOfWeek === 1 && factors.hourOfDay < 10) {
logit += WEIGHTS.mondayMorning;
}
if (factors.dayOfWeek === 5 && factors.hourOfDay >= 15) {
logit += WEIGHTS.fridayAfternoon;
}
// First visit
if (factors.isFirstVisit) {
logit += WEIGHTS.firstVisit;
}
// Weather
if (factors.weatherForecast === 'rainy') {
logit += WEIGHTS.rainyWeather;
} else if (factors.weatherForecast === 'stormy') {
logit += WEIGHTS.stormyWeather;
}
// Reminders
if (factors.remindersSent === 0) {
logit += WEIGHTS.noReminders;
}
// Appointment type
if (factors.appointmentType === 'surgery') {
logit += WEIGHTS.surgeryAppointment;
}
// Convert to probability
const probability = 1 / (1 + Math.exp(-logit));
// Determine risk level and actions
let riskLevel: 'low' | 'medium' | 'high';
const suggestedActions: string[] = [];
if (probability < 0.15) {
riskLevel = 'low';
} else if (probability < 0.35) {
riskLevel = 'medium';
suggestedActions.push('Enviar recordatorio adicional');
if (!factors.confirmationReceived) {
suggestedActions.push('Solicitar confirmación por WhatsApp');
}
} else {
riskLevel = 'high';
suggestedActions.push('Llamar para confirmar');
suggestedActions.push('Considerar overbooking para este horario');
if (factors.daysUntilAppointment > 7) {
suggestedActions.push('Enviar múltiples recordatorios');
}
}
return { probability, riskLevel, suggestedActions };
}
Overbooking Strategy
// lib/scheduling/overbooking.ts
interface OverbookingDecision {
allowOverbook: boolean;
maxOverbooks: number;
reason: string;
}
export function shouldAllowOverbooking(
slot: TimeSlot,
existingAppointments: Appointment[],
historicalNoShowRate: number
): OverbookingDecision {
// Calculate expected no-shows for existing appointments
const noShowPredictions = existingAppointments.map(apt =>
predictNoShow(getFactorsFromAppointment(apt))
);
const expectedNoShows = noShowPredictions.reduce(
(sum, pred) => sum + pred.probability,
0
);
// If expected no-shows >= 1, allow overbooking
if (expectedNoShows >= 0.8) {
return {
allowOverbook: true,
maxOverbooks: Math.floor(expectedNoShows),
reason: `${Math.round(expectedNoShows * 100)}% probabilidad de ausencias`,
};
}
// Check historical rate for this time slot
if (historicalNoShowRate > 0.25) {
return {
allowOverbook: true,
maxOverbooks: 1,
reason: `Históricamente ${Math.round(historicalNoShowRate * 100)}% de ausencias en este horario`,
};
}
return {
allowOverbook: false,
maxOverbooks: 0,
reason: 'Bajo riesgo de ausencias',
};
}
4. Emergency Slot Management
Dynamic Emergency Reservation
// lib/scheduling/emergency-slots.ts
interface EmergencyConfig {
minReservedSlotsPerDay: number;
reservationHoursAhead: number;
releaseIfUnusedHours: number;
emergencyCategories: string[];
}
const EMERGENCY_CONFIG: EmergencyConfig = {
minReservedSlotsPerDay: 2,
reservationHoursAhead: 24, // Reserve slots 24h ahead
releaseIfUnusedHours: 2, // Release to regular booking 2h before
emergencyCategories: [
'trauma',
'dificultad_respiratoria',
'intoxicacion',
'convulsiones',
'sangrado',
'parto',
],
};
export function manageEmergencySlots(
date: Date,
allSlots: TimeSlot[],
currentEmergencies: number,
config: EmergencyConfig = EMERGENCY_CONFIG
): TimeSlot[] {
const now = new Date();
const hoursUntilDate = (date.getTime() - now.getTime()) / (1000 * 60 * 60);
return allSlots.map(slot => {
if (slot.priority !== 'emergency') return slot;
const hoursUntilSlot = (slot.start.getTime() - now.getTime()) / (1000 * 60 * 60);
// Release emergency slots if:
// 1. Less than releaseIfUnusedHours before slot time
// 2. We have enough emergency capacity with current emergencies
if (hoursUntilSlot <= config.releaseIfUnusedHours) {
return {
...slot,
available: true,
priority: 'normal' as const,
};
}
// Keep reserved if within reservation window
if (hoursUntilSlot <= config.reservationHoursAhead) {
return {
...slot,
available: false, // Reserved for emergencies only
};
}
return slot;
});
}
export function findEmergencySlot(
emergencyType: string,
availableSlots: TimeSlot[]
): TimeSlot | null {
// Find the next available slot (emergency or released)
const now = new Date();
// First, try emergency-reserved slots
const emergencySlot = availableSlots.find(
slot => slot.priority === 'emergency' && slot.start > now
);
if (emergencySlot) return emergencySlot;
// If no emergency slots, find any available slot
return availableSlots.find(
slot => slot.available && slot.start > now
) || null;
}
5. Wait Time Estimation
// lib/scheduling/wait-time.ts
interface WaitTimeEstimate {
estimatedMinutes: number;
confidenceLevel: 'high' | 'medium' | 'low';
factors: string[];
}
export function estimateWaitTime(
appointmentTime: Date,
currentQueue: Appointment[],
historicalData: {
avgDelayMinutes: number;
stdDevMinutes: number;
}
): WaitTimeEstimate {
const now = new Date();
const scheduledMinutesFromNow = (appointmentTime.getTime() - now.getTime()) / 60000;
// Count appointments before this one today
const appointmentsBefore = currentQueue.filter(apt =>
new Date(apt.start_time) < appointmentTime &&
new Date(apt.start_time).toDateString() === appointmentTime.toDateString()
);
// Calculate cumulative delay potential
let estimatedDelay = 0;
const factors: string[] = [];
// Historical average delay
estimatedDelay += historicalData.avgDelayMinutes;
factors.push(`Demora promedio histórica: ${historicalData.avgDelayMinutes} min`);
// Add delay for each appointment before (assuming some run over)
const delayPerPriorAppointment = 3; // minutes
const priorDelays = appointmentsBefore.length * delayPerPriorAppointment * 0.3; // 30% chance each runs over
estimatedDelay += priorDelays;
if (priorDelays > 0) {
factors.push(`${appointmentsBefore.length} citas previas pueden causar demora`);
}
// Check for known delays (e.g., emergency in progress)
const emergencyInProgress = currentQueue.some(apt =>
apt.appointment_type === 'emergency' && apt.status === 'in_progress'
);
if (emergencyInProgress) {
estimatedDelay += 20;
factors.push('Emergencia en atención - posible demora adicional');
}
// Confidence level based on data quality
let confidenceLevel: 'high' | 'medium' | 'low' = 'medium';
if (historicalData.stdDevMinutes < 10) {
confidenceLevel = 'high';
} else if (historicalData.stdDevMinutes > 20) {
confidenceLevel = 'low';
}
return {
estimatedMinutes: Math.max(0, Math.round(estimatedDelay)),
confidenceLevel,
factors,
};
}
6. Database Functions
-- Get optimal appointment slots
CREATE OR REPLACE FUNCTION get_optimal_slots(
p_tenant_id TEXT,
p_service_id UUID,
p_date DATE,
p_preferred_time TEXT DEFAULT 'any'
)
RETURNS TABLE (
slot_start TIMESTAMPTZ,
slot_end TIMESTAMPTZ,
vet_id UUID,
vet_name TEXT,
score INTEGER
)
LANGUAGE plpgsql
AS $$
BEGIN
RETURN QUERY
WITH available_slots AS (
-- Generate time slots
SELECT
gs.slot_time AS start_time,
gs.slot_time + (s.duration_minutes || ' minutes')::INTERVAL AS end_time,
ss.staff_id AS vet_id
FROM generate_series(
p_date + '08:00:00'::TIME,
p_date + '17:30:00'::TIME,
'30 minutes'::INTERVAL
) AS gs(slot_time)
CROSS JOIN services s
CROSS JOIN staff_schedules ss
WHERE s.id = p_service_id
AND ss.tenant_id = p_tenant_id
AND ss.day_of_week = EXTRACT(DOW FROM p_date)
AND gs.slot_time::TIME >= ss.start_time
AND (gs.slot_time + (s.duration_minutes || ' minutes')::INTERVAL)::TIME <= ss.end_time
-- Exclude existing appointments
AND NOT EXISTS (
SELECT 1 FROM appointments a
WHERE a.vet_id = ss.staff_id
AND a.start_time < gs.slot_time + (s.duration_minutes || ' minutes')::INTERVAL
AND a.end_time > gs.slot_time
AND a.status NOT IN ('cancelled', 'no_show')
)
)
SELECT
av.start_time,
av.end_time,
av.vet_id,
p.full_name,
-- Scoring
CASE
WHEN p_preferred_time = 'morning' AND EXTRACT(HOUR FROM av.start_time) < 12 THEN 20
WHEN p_preferred_time = 'afternoon' AND EXTRACT(HOUR FROM av.start_time) >= 14 THEN 20
ELSE 0
END +
-- Prefer slots that fill gaps
CASE WHEN EXISTS (
SELECT 1 FROM appointments a
WHERE a.vet_id = av.vet_id
AND DATE(a.start_time) = p_date
AND (
a.end_time = av.start_time
OR a.start_time = av.end_time
)
) THEN 10 ELSE 0 END AS score
FROM available_slots av
JOIN profiles p ON p.id = av.vet_id
ORDER BY score DESC, av.start_time
LIMIT 20;
END;
$$;
Reference: Healthcare scheduling optimization research, veterinary practice management best practices