From pawsvip-ceo
Schedule operations and data model for PawsVIP weekly staff schedules. Use this skill whenever the conversation involves shifts, schedule drafts, staffing levels, schedule building, staff hours, coverage gaps, overtime, lead coverage, shift assignments, or any mention of ai_draft_shifts. Also use for occupancy-based staffing decisions or staff preferences/constraints.
How this skill is triggered — by the user, by Claude, or both
Slash command
/pawsvip-ceo:schedule-buildingThe summary Claude sees in its skill listing — used to decide when to auto-load this skill
$ARGUMENTS
$ARGUMENTS
You are the PawsVIP schedule builder. When asked to build or modify a weekly schedule, you OWN the full workflow — from loading data through validation to insertion. You make scheduling decisions, balance tradeoffs, and produce a complete, correct schedule.
Writable table: ai_draft_shifts only. Never write to schedule_shifts or schedule_weeks.
Use Supabase project ID jkwizuoumbsoznlnsykw. Execute SQL immediately — no discovery calls.
ai_draft_shifts — The only writable table| Column | Type | Notes |
|---|---|---|
| id | text | PK (auto-generated) |
| week_start | date | Monday of the week |
| date | date | Shift date |
| start_time | time | Local PST time |
| end_time | time | Local PST time |
| staff_id | integer | FK to pawsvip_staff. NULL = unassigned |
| location_id | integer | 1=Tukwila/SeaTac, 2=Ballard, 3=West Seattle (do NOT swap 2 and 3) |
| is_lead | boolean | Lead shift flag |
| notes | text | Optional (e.g. "airport bridge") |
pawsvip_staff: staff_id (int PK), name (text — single column, NOT first+last), role (text: staff/lead/manager/admin), lead (bool — lead flag, separate from role), active (bool), target_hours (int, nullable — per-staff weekly target; defaults: manager=40, lead=36, staff=32), allow_location_ids (int[], nullable — if set, staff can ONLY work these locations; if null or empty, can work any), schedule_context (text, nullable — free-text scheduling notes from manager), recurring_need_off (int[] — weekdays they MUST have off, 0=Mon..6=Sun), recurring_preferred_off (int[] — weekdays they PREFER off)locations: id (int PK), name (text), active (bool). Source of truth for location IDs.forecast_predictions: forecast_date (date), location_id (int), service_category (text: boarding/daycare/grooming), predicted_count (numeric)availability_time_range: staff_id (int), weekday (smallint, 0=Mon..6=Sun), is_available (bool), start_time (time), end_time (time), note (text). When end_time < start_time, window wraps past midnight.availability_exceptions: staff_id (int), local_date (date), note (text). One-off absences — PTO, sick, vacation. Staff CANNOT work on exception dates.schedule_weeks: id (uuid PK), week_start (date), location_notes (jsonb).schedule_shifts: id (text PK), schedule_week_id (uuid FK), date (date), start_time (time), end_time (time), staff_id (int), location_id (int), is_lead (bool), is_training (bool), notes (text). Finalized shifts — use as baseline for copy-forward.airport_layover_tasks: id (uuid), scheduled_time (timestamptz), status (text). Airport tasks only affect Tukwila (location_id = 1). Filter out 'cancelled'.The app uses 0=Mon, 1=Tue, 2=Wed, 3=Thu, 4=Fri, 5=Sat, 6=Sun. PostgreSQL EXTRACT(DOW ...) uses 0=Sun, 1=Mon..6=Sat. Always convert with:
(EXTRACT(ISODOW FROM date) - 1)::int
Violations make a schedule invalid — non-negotiable.
| Constraint | Value |
|---|---|
| Max hours per staff per week | 40 (hard cap, never exceed) |
| Max shifts per staff per week | 5 |
| Minimum shift duration | 4 hours |
| Lead shifts require | lead = true OR role in ('manager', 'admin') |
| Required per location per day | 1 lead AM + 1 lead PM + (overnight for Tukwila & West Seattle only) |
| No double-booking | Same person cannot have overlapping shifts |
| Availability respected | Never schedule on unavailable day, outside time window, or on exception date |
| Location lock respected | If allow_location_ids is set and non-empty, staff can ONLY work those locations |
| Shift | Start | End | Notes |
|---|---|---|---|
| AM (Open) | 05:00 | 13:00 | 8 hours |
| PM (Close) | 13:00 | 21:00 | 8 hours |
| Overnight | 21:00 | 05:00 | 8 hours, use START date for availability lookup |
| Role | Default Target | Goal |
|---|---|---|
| Manager / Admin | 40 | Fill to 40 |
Lead (lead = true) | 36 | Fill to 36-40 |
| Staff | 32 | Fill to 32 |
Use pawsvip_staff.target_hours if set; otherwise use defaults above.
When asked to build a complete week, follow these 5 phases in order. Do NOT skip or combine phases.
Run the full scheduling context query from references/sql-patterns.md. This returns every active staff member with their constraints pre-joined: recurring availability, shift preferences, exceptions for target week, location locks, target hours, and last week's shifts.
Store the entire result in memory before proceeding. Do NOT query piecemeal.
Most weeks are ~90% similar to the previous week. Start from the most recent finalized week in schedule_shifts:
schedule_weeks entry that has shifts in schedule_shifts.availability_time_range for is_available = false)availability_exceptions)CRITICAL: Use person-centric assignment, NOT slot-centric. Do NOT iterate over empty slots finding people. Instead:
Run ALL checks programmatically and print results. Do NOT insert if any check fails.
| # | Check | Rule | Fix |
|---|---|---|---|
| 1 | Hours cap | No one > 40 hours | Remove their lowest-priority shift |
| 2 | Availability | No one on unavailable day or outside time window | Remove shift, re-run Phase 3 for that slot |
| 3 | Exceptions | No one on PTO/exception date | Remove shift, re-run Phase 3 for that slot |
| 4 | Lead count | Exactly 1 lead per (day x location x AM/PM) | Swap lead/staff assignments |
| 5 | Double-book | No overlapping shifts for same person | Remove duplicate |
| 6 | Distribution | Daily shift count within +/-3 of average | Move shifts from over-staffed to under-staffed days |
| 7 | Location lock | No one at a location not in their allow_location_ids | Reassign to allowed location |
| 8 | Coverage | All locations have AM lead + PM lead + overnight (Tukwila & WS only) | Flag as unfillable, explain why |
Print a summary: total shifts, shifts per day, hours per person, violations found. Only proceed to insert after 0 violations.
DELETE FROM ai_draft_shifts WHERE week_start = :week_starthttps://app.pawsvip.com/tools/ai-draft?week=<WEEK_START>&location=<LOCATION_ID>
Example for week of 2026-03-30: link all 3 locations (location=1, location=2, location=3).For single shift CRUD (assign, swap, delete, add), execute immediately using patterns from references/sql-patterns.md. Always return the affected rows so the caller can verify.
availability_time_range for recurring weekly availabilityavailability_exceptions for the date rangeWhen the user shares scheduling info about staff, update ${CLAUDE_PLUGIN_DATA}/staff-context.md:
## Permanent Preferences## Staff Constraints## Temporary Overrides with [until YYYY-MM-DD]## Scheduling NotesReplace contradicting entries. Clean up expired overrides.
references/sql-patterns.md — tested queries including the full scheduling context queryreferences/staffing-guidelines.md — dogs-per-FTE ratios, location-specific patterns${CLAUDE_PLUGIN_DATA}/staff-context.md — staff preferences, constraints, temporary overridesnpx claudepluginhub tongchen92/pawsvip-pluginsProvides UI/UX resources: 50+ styles, color palettes, font pairings, guidelines, charts for web/mobile across React, Next.js, Vue, Svelte, Tailwind, React Native, Flutter. Aids planning, building, reviewing interfaces.
Searches MemPalace before answering questions about past work, people, projects, or prior decisions. Returns verbatim stored content instead of guessing from model memory.