One of the biggest challenges in service-based SaaS platforms is managing recurring tasks and schedules without disrupting historical data. Businesses often need to create and adjust technician assignments for cleaning, maintenance, or inspection services. However, updating or deleting schedules retroactively can break reports, distort KPIs, or cause inconsistencies in mobile apps.

We designed a robust data model that allows businesses to add, edit, or remove scheduled positions without affecting past records, while maintaining a complete history of assignments and task execution. This article explains the database structure, the functions we implemented, and how this model provides scalability and flexibility for real-world operations.

Core Database Tables

At the heart of the model are five core concepts: positions, assignments, schedules, checklists, and locations.

• Positions (positions) define a recurring job (e.g., “Nightly Cleaning – Floor 1”). They include attributes like frequency, start/end dates, start/end times, and scheduled days.

• Assignments (position_assignments) keep a historical record of technicians assigned to a position. Each change generates a new record with assigned_at and unassigned_at.

• Schedules (position_schedules) represent individual executions of a position on a specific date. They track the assigned user, status, and check-in/out.

• Checklists (checklist, checklist_tasks) are templates of tasks that must be completed for a position.

• Position Checklists (position_checklists) link positions to one or more checklists.

• Checklist Task Executions (checklist_task_executions) capture actual progress for each schedule and task.

• Locations (locations) group positions under an address or site, enriched with metadata like image_url.
  
  
  

This separation allows historical data (assignments and executions) to remain immutable, while still providing flexibility for future scheduling.

‍

Key Functions for Scheduling and Management

While the data model defines the foundation, the real power comes from the functions that automate and safeguard schedule generation and maintenance.

1. generate_position_schedules(p_position_id)

Generates schedules for a given position between its start_date and end_date, assigns the current technician, and pre-populates task executions.

2. regenerate_future_position_schedules(p_position_id)

Deletes and regenerates only future schedules for a position, leaving past records intact.

‍

3. generate_schedules_and_backfill()

Backfills missing schedules across all active positions, ensuring dashboards remain consistent.

4. Progress Helpers

Views like the following make it easy to track technician progress:

‍

Strategic Design Decisions

• Immutable history: past assignments and schedules remain untouched.

• Future-only updates: editing or deleting a position only affects upcoming schedules.

• Checklist isolation: each execution creates its own checklist_task_executions.

• Security: enforced row-level security (RLS) with company_id.

Benefits

• Supports daily, weekly, biweekly, and monthly frequencies.

• Handles multiple checklists per position.

• Provides clear reporting for dashboards and mobile apps.

• Scalable, maintainable, and extensible for features like payments or notifications.
  
  
  

Conclusion

This scheduling model demonstrates how thoughtful database design and well-crafted functions can solve one of the hardest problems in service platforms: reconciling recurring schedules with immutable historical data. By separating positions, assignments, and schedules, and by generating checklists per execution, we created a system that is both flexible for businesses and reliable for analytics.

This approach ensures that businesses can confidently schedule, update, and track work — without the fear of breaking history. It lays the groundwork for a scalable and professional service management platform.

‍