Start

Schedules

Satia manages time and availability for appointments, bookings, and resources through the schedules and schedule_rules tables. Transactional agents consume these rules automatically via their native scheduling tools (check_availability, find_open_slots), while you (from Sati or MCP) configure and query these rules directly via SQL (database__query, database__insert, database__update, database__delete).

1. Fundamental Rule: The 24/7 Subtractive Model

⚠️ GOLDEN LAW OF THE TIME ENGINE:
In Satia, the baseline of every event table or resource is OPEN 24/7.
Any time range that is NOT explicitly blocked is considered available.

  • To define a business's operating hours, create type = 'block' rules that cover the CLOSED hours, never the open hours.
  • NEVER block all 24 hours and then attempt to open a window with 'availability': the 'availability' type is used exclusively to open a one-off exception on a specific date (specific_date) on top of an existing recurring block.

1.1 4-Layer Evaluation Hierarchy

The availability engine evaluates every requested time slot in this exact priority order:

  1. Layer 0 (24/7 Open Baseline): By default, the entire calendar is open 24 hours a day, 7 days a week.
  2. Layer 1 (Recurring Blocks — type = 'block' + rrule): Regular closed hours (nights, weekends, lunch breaks) are subtracted from the baseline.
    • Midnight-crossing blocks are supported natively: If start_time > end_time (e.g., start_time = '18:00:00' and end_time = '08:00:00'), the engine automatically blocks the evening segment (18:00 to 23:59:59) and the early-morning segment (00:00 to 08:00).
  3. Layer 2 (Date-Specific Availability Exceptions — type = 'availability' + specific_date): If a specific date that is normally closed by a recurring rule (e.g., a holiday Saturday) needs to open for special hours, a type = 'availability' rule with specific_date = 'YYYY-MM-DD' opens that window over the recurring block.
  4. Layer 3 (Date-Specific Blocks / Holidays — type = 'block' + specific_date): Holds the highest priority and closes any time range on that exact date (specific_date = 'YYYY-MM-DD'), overriding any recurring rule or prior availability window.

1.2 Quick Mental Examples

  • "We are open Monday through Friday from 8:00 AM to 6:00 PM and closed Saturdays and Sundays":
    1. Block (type = 'block') from 18:00:00 to 08:00:00 Monday–Friday (FREQ=WEEKLY;BYDAY=MO,TU,WE,TH,FR).
    2. Block (type = 'block') from 00:00:00 to 23:59:00 Saturdays and Sundays (FREQ=WEEKLY;BYDAY=SA,SU).
  • "Closed for lunch from 1:00 PM to 2:00 PM on weekdays":
    • Block (type = 'block') from 13:00:00 to 14:00:00 with rrule = 'FREQ=WEEKLY;BYDAY=MO,TU,WE,TH,FR'.
  • "This Saturday, December 20, we open exceptionally from 9:00 AM to 2:00 PM":
    • Exception (type = 'availability') from 09:00:00 to 14:00:00 with specific_date = '2026-12-20' (rrule = null).

2. Data Model (schedules and schedule_rules)

2.1 Table schedules (The Calendar Container)

Assigns a general calendar to an entire booking/event table (e.g., citas, reservas) or an individual calendar to a specific resource record (e.g., a doctor, room, court, or service):

Column / FieldTypeDescription
iduuidUnique schedule identifier (schedule_id).
owner_typetext'table' (global schedule for an entire event table, e.g., citas) or 'resource' (individual schedule for a specific resource record).
table_slugtext(For owner_type = 'table') Business table slug (e.g., 'citas', 'reservas'). The tool automatically resolves the internal identifier.
record_iduuid(For owner_type = 'resource') UUID of the specific resource record (e.g., the id of Dr. García or Court 1 in its respective table).
owner_iduuidInternal identifier automatically resolved from table_slug or record_id.
timezonetextBusiness IANA timezone (e.g., 'America/Bogota', 'America/Mexico_City'). Defaults to 'America/Bogota'.
nametextDescriptive name (e.g., 'General Appointments Schedule', 'Dr. Martínez Calendar').

💡 Automatic Upsert on schedules:
Each business table or resource has a single schedules container. If you call database__insert(table_name="schedules", ...) for a table_slug or record_id that already has a schedule, the system automatically updates and reuses the existing schedule (returning upserted: true and its schedule_id) without creating duplicate calendars.

2.2 Combining Table Schedule + Resource Schedule

When an appointment or booking involves a specific resource (for example, an appointment in citas with resource Dr. Martínez), the availability engine enforces both calendars simultaneously: the table's general schedule (owner_type = 'table', table_slug = 'citas') and the resource's individual schedule (owner_type = 'resource', record_id = '<uuid>'), in addition to verifying that existing bookings in that slot do not exceed the resource's capacity.

2.3 Table schedule_rules (Time Rules & Blocks)

ColumnTypeDescription
iduuidUnique rule identifier.
schedule_iduuidFK referencing schedules.id.
typetext'block' (closes or blocks that time window — used 99% of the time) or 'availability' (one-off exception with specific_date to open hours over a recurring block).
start_timetimeStart time in 'HH:MM:SS' format (e.g., '18:00:00', '00:00:00').
end_timetimeEnd time in 'HH:MM:SS' format (e.g., '08:00:00', '23:59:00').
start_datedateDate from which the recurring rule takes effect ('YYYY-MM-DD').
end_datedateDate until which the rule applies (optional, null if indefinite).
rruletextRFC 5545 recurrence rule without the "RRULE:" prefix (e.g., 'FREQ=WEEKLY;BYDAY=MO,TU,WE,TH,FR').
specific_datedateExact date ('YYYY-MM-DD') for a single-day holiday or exception (when using specific_date, leave rrule as null).
reasontextHuman-readable explanation for agents and UI (e.g., 'Closed outside business hours', 'Christmas Holiday').

3. Standard RRULE Patterns (No RRULE: Prefix)

  • Monday to Friday: FREQ=WEEKLY;BYDAY=MO,TU,WE,TH,FR
  • Monday to Saturday: FREQ=WEEKLY;BYDAY=MO,TU,WE,TH,FR,SA
  • Weekends: FREQ=WEEKLY;BYDAY=SA,SU
  • Sundays Only: FREQ=WEEKLY;BYDAY=SU
  • Specific Days (Mon, Wed, Fri): FREQ=WEEKLY;BYDAY=MO,WE,FR
  • Every Day: FREQ=DAILY

4. Operational Recipes with database__query and database__insert

Note: All queries and inserts on schedules and schedule_rules are automatically scoped to the active Workspace.

Step 1: Always Query Existing Schedules Before Modifying

SELECT s.id AS schedule_id, s.owner_type, s.owner_id, s.name, s.timezone,
       sr.id AS rule_id, sr.type, sr.start_time, sr.end_time, sr.rrule, sr.specific_date, sr.reason
FROM schedules s
LEFT JOIN schedule_rules sr ON sr.schedule_id = s.id
ORDER BY s.name, sr.start_time

(The response automatically includes table_slug when owner_type = 'table' and record_id when owner_type = 'resource'. You can also filter directly in SQL with WHERE s.table_slug = 'citas' or WHERE s.owner_type = 'table').

Step 2: Create (or Reuse) a schedule

  • Option A — General schedule for a booking/event table (table_slug):
{
  "table_name": "schedules",
  "data": {
    "table_slug": "citas",
    "timezone": "America/Bogota",
    "name": "Horario General de Citas"
  },
  "user_prompt": "Configure business hours Monday to Friday from 9 AM to 6 PM"
}
  • Option B — Individual schedule for a specific resource (record_id):
{
  "table_name": "schedules",
  "data": {
    "record_id": "RESOURCE-RECORD-UUID",
    "timezone": "America/Bogota",
    "name": "Agenda Dra. Martínez"
  },
  "user_prompt": "Configure Dr. Martínez's individual schedule"
}

Step 3: Insert Rules into schedule_rules

Call database__insert on table_name="schedule_rules" (you can batch-insert multiple rules at once by passing a list in data):

{
  "table_name": "schedule_rules",
  "data": [
    {
      "schedule_id": "SCHEDULE-UUID",
      "type": "block",
      "start_time": "18:00:00",
      "end_time": "09:00:00",
      "start_date": "2026-01-01",
      "rrule": "FREQ=WEEKLY;BYDAY=MO,TU,WE,TH,FR",
      "reason": "Closed outside business hours (Monday to Friday)"
    },
    {
      "schedule_id": "SCHEDULE-UUID",
      "type": "block",
      "start_time": "00:00:00",
      "end_time": "23:59:00",
      "start_date": "2026-01-01",
      "rrule": "FREQ=WEEKLY;BYDAY=SA,SU",
      "reason": "Closed on weekends"
    },
    {
      "schedule_id": "SCHEDULE-UUID",
      "type": "block",
      "start_time": "00:00:00",
      "end_time": "23:59:00",
      "specific_date": "2026-12-25",
      "reason": "Christmas Holiday"
    }
  ],
  "user_prompt": "Configure business hours Monday to Friday 9 AM to 6 PM and close Christmas Day"
}