---
type: SQLite Table
title: shift_slots
description: Bookable shift slots (a date + time range + headcount capacity), generated from a weekly template or created ad hoc by the admin.
resource: backend/src/db/index.ts
tags: [data-model, sqlite, shift-planning]
timestamp: 2026-08-16T00:00:00Z
---

# shift_slots

# Schema

| Column | Type | Description |
|---|---|---|
| `id` | INTEGER PK | autoincrement |
| `date` | TEXT | `YYYY-MM-DD` |
| `start_time` / `end_time` | TEXT | `HH:MM`; `end_time <= start_time` means the slot rolls past midnight |
| `capacity` | INTEGER | headcount |
| `status` | TEXT | `open` \| `full` \| `closed` — recomputed from capacity vs. approved signups on every change |
| `note` | TEXT | free text |
| `generated` | INTEGER | 1 if created by the weekly template, 0 if manually added by the admin |
| `created_at` / `updated_at` | TEXT | ISO 8601 UTC |

`UNIQUE (date, start_time, end_time)` — this is what makes template
generation idempotent (`INSERT OR IGNORE`).

# Weekly template

`ensureSlotsForPeriod` (`backend/src/services/shiftPlanning.ts`) generates a
whole calendar month lazily, the first time anyone (employee or admin)
requests that period — not on a cron schedule. There's no scheduler in this
app, and lazy + idempotent is simpler and just as reliable as the source
system's Apps Script monthly trigger
(`createShiftPlanningTrigger_` in `gscript/ShiftPlanningService.js`, which
could only fire in a coarse hourly window).

| Day | Slots |
|---|---|
| Sun–Thu | 18:00–23:00, capacity 1 |
| Fri, Sat | 18:00–02:00, capacity 1 **and** 20:00–00:00, capacity 1 |

The two Friday/Saturday slots deliberately overlap in time — a real
scheduling choice from the source bistro (an early shift and a late shift),
not a bug. It's also exactly what the collision test in
`backend/src/services/shiftPlanning.test.ts` exercises.

# Related

- [shift_signups](./shift-signups.md)
- [Shift planning API](/docs/api/shifts-routes.md)