> For AI agents: the complete documentation index is available at /llms.txt, the full documentation bundle is available at /llms-full.txt.

# Operating Hours Schema

> **Source of truth**: `callytics-common/src/db/schema/operating-schedules.ts` · `operating-overrides.ts` · `blackout-periods.ts` (verified 2026-06-15)
> ⚠️ **数据已建模,SLA 计算待接入** — 三张表已存在,但当前 `computeDueAt()` 用硬编码 8 AM–9 PM ET,per-store 时区 / 营业时间 / blackout override **未接线**。函数签名支持传 per-store `businessHours` / `timezone`,但 lead 和 contacts 两条管道的调用处都没传。本页是"schema 占位,SLA 计算尚未读取"状态。

***

## 1. 表说明

这三张表合起来定义 **Lead SLA 的"营业时段内才计时"逻辑**:Lead SLA 倒计时只在营业时段内走,落在闭店时段或 blackout 时段被排除/推迟。三表都从 DynamoDB 旧记录迁来,主键都以 `store_id` 打头。

| 表                     | 职责                          | 关键约束                                                                                                |
| --------------------- | --------------------------- | --------------------------------------------------------------------------------------------------- |
| `operating_schedules` | 每周营业时间模板 + 生效日期(可堆历史版本)     | PK `(store_id, sk)`,sk = `SCHEDULE#{effectiveDate}#{createdAt}`。给定某天取 `effective_date <= 目标日` 中最新一条 |
| `operating_overrides` | 单日营业例外(提前关门 / 当天歇业 / 临时改时间) | PK `(store_id, sk)`,sk = `OVERRIDE#{date}`。同日再写会 ON CONFLICT DO UPDATE 替换                           |
| `blackout_periods`    | Lead SLA 排除时段(支持每周重复)       | PK `(store_id, blackout_id)`。`expandBlackouts()` 把 recurring 规则解析成单日实例                              |

写入者:studio-api 的 operating-hours / blackouts 路由(create / update / delete)。
读取者:同上路由(list / effective / expanded)+ **SLA 计算(待接入)**。

***

## 2. Schema 全字段

### 2.1 `operating_schedules` — 每周营业时间模板

替代 DDB OperatingHours 里 SK prefix `SCHEDULE#` 的记录。

| # | Field (TS)      | SQL 列            | Type        | Nullable               | 用途                                                                             |
| - | --------------- | ---------------- | ----------- | ---------------------- | ------------------------------------------------------------------------------ |
| 1 | `storeId`       | `store_id`       | text        | NOT NULL (PK)          | 门店隔离键                                                                          |
| 2 | `sk`            | `sk`             | text        | NOT NULL (PK)          | 排序键,格式 `SCHEDULE#{effectiveDate}#{createdAt}`,保留 DDB 排序语义                      |
| 3 | `effectiveDate` | `effective_date` | date        | NOT NULL               | 生效日期。给定某天,取 `effective_date <= 目标日` 中最新的那条 schedule                            |
| 4 | `schedule`      | `schedule`       | jsonb       | NOT NULL               | 每日开关门时间。例:`{ "monday": [{ "openTime": "09:00", "closeTime": "17:00" }], ... }` |
| 5 | `createdAt`     | `created_at`     | timestamptz | NOT NULL DEFAULT NOW() | 创建时间                                                                           |
| 6 | `createdBy`     | `created_by`     | text        | NOT NULL               | 创建者(staff ID / 系统标识)                                                           |

- **主键**: `(store_id, sk)`
- **索引**: `idx_op_schedules_effective` on `(store_id, effective_date)` — 按生效日查当前 active 模板

### 2.2 `operating_overrides` — 单日营业例外

替代 DDB OperatingHours 里 SK prefix `OVERRIDE#` 的记录。

| # | Field (TS)     | SQL 列           | Type        | Nullable               | 用途                                                                                         |
| - | -------------- | --------------- | ----------- | ---------------------- | ------------------------------------------------------------------------------------------ |
| 1 | `storeId`      | `store_id`      | text        | NOT NULL (PK)          | 门店隔离键                                                                                      |
| 2 | `sk`           | `sk`            | text        | NOT NULL (PK)          | 排序键,格式 `OVERRIDE#{date}`                                                                   |
| 3 | `date`         | `date`          | date        | NOT NULL               | 例外日期                                                                                       |
| 4 | `overrideData` | `override_data` | jsonb       | NOT NULL               | 该日的 override 配置。例:`{ "isClosed": true }` 或 `{ "openTime": "10:00", "closeTime": "14:00" }` |
| 5 | `createdAt`    | `created_at`    | timestamptz | NOT NULL DEFAULT NOW() | 创建时间                                                                                       |
| 6 | `updatedAt`    | `updated_at`    | timestamptz | NULL                   | 更新时间(首次创建为 NULL,upsert 时写入)                                                                |
| 7 | `createdBy`    | `created_by`    | text        | NOT NULL               | 创建者                                                                                        |

- **主键**: `(store_id, sk)`
- **Upsert 语义**: 同一日期再建 override 会替换前一条(service 层 `ON CONFLICT DO UPDATE`)
- **索引**: `idx_op_overrides_date_range` on `(store_id, date)`

### 2.3 `blackout_periods` — Lead SLA 排除时段

替代 DDB `studio-BlackoutPeriods-{env}`。支持 one-time 和 recurring(每周重复)两种。

| #  | Field (TS)       | SQL 列              | Type        | Nullable                | 用途                                                                                                         |
| -- | ---------------- | ------------------ | ----------- | ----------------------- | ---------------------------------------------------------------------------------------------------------- |
| 1  | `storeId`        | `store_id`         | text        | NOT NULL (PK)           | 门店隔离键                                                                                                      |
| 2  | `blackoutId`     | `blackout_id`      | text        | NOT NULL (PK)           | blackout 唯一标识                                                                                              |
| 3  | `startTime`      | `start_time`       | text        | NOT NULL                | 起始时间(ISO 字符串或时段表示)                                                                                         |
| 4  | `endTime`        | `end_time`         | text        | NOT NULL                | 结束时间                                                                                                       |
| 5  | `reason`         | `reason`           | text        | NULL                    | 原因说明(自由文本,UI 展示用)                                                                                          |
| 6  | `excludeFromSla` | `exclude_from_sla` | boolean     | NOT NULL DEFAULT `true` | 是否从 SLA 倒计时排除(默认排除)                                                                                        |
| 7  | `recurrence`     | `recurrence`       | jsonb       | NULL                    | 重复规则。例:`{ "type": "weekly", "daysOfWeek": [0..6], "startDate": "...", "endDate"?: "..." }`。NULL = one-time |
| 8  | `createdBy`      | `created_by`       | text        | NOT NULL                | 创建者                                                                                                        |
| 9  | `createdAt`      | `created_at`       | timestamptz | NOT NULL DEFAULT NOW()  | 创建时间                                                                                                       |
| 10 | `updatedAt`      | `updated_at`       | timestamptz | NOT NULL DEFAULT NOW()  | 更新时间                                                                                                       |

- **主键**: `(store_id, blackout_id)`
- **重复规则展开**: studio-api 的 `expandBlackouts()` 把 recurring 规则解析成单个日期实例,用于日历展示和 SLA 计算

***

## 3. Enum 清单

三张表**没有 text-as-enum 字段**(`schedule` / `override_data` / `recurrence` 是结构化 JSONB,不是枚举)。
唯一的枚举状态是 JSONB payload 内的约定值,**无 DB CHECK,合法值靠写入方(studio-api 路由)遵守**:

| 出现位置                                         | 约定值      | 含义                                   |
| -------------------------------------------- | -------- | ------------------------------------ |
| `operating_overrides.override_data.isClosed` | `true`   | 当天歇业(此时 `openTime` / `closeTime` 可省) |
| `blackout_periods.recurrence.type`           | `weekly` | 当前仅支持每周重复;`null` = one-time          |
| `blackout_periods.recurrence.daysOfWeek[]`   | `0..6`   | 周日 = 0,周六 = 6(JS Date 风格)            |

***

## 4. Prompt Output ↔ Schema 字段对照

**N/A — 7 个 prompt 都不直接读写这三张表。** Operating-hours 数据由 studio-api UI 维护(店主/管理员手动配置),AI pipeline 不产出 schedule / override / blackout 数据。

| Prompt             | 与本 schema 关系                                                                                                                  |
| ------------------ | ----------------------------------------------------------------------------------------------------------------------------- |
| 01 Triage          | n/a                                                                                                                           |
| 02 Classify        | n/a                                                                                                                           |
| 03 Verify          | n/a                                                                                                                           |
| 04 Coaching        | n/a                                                                                                                           |
| 05 Contact Profile | n/a                                                                                                                           |
| 06 Task Decision   | n/a — 但 prompt 输出的 `nextDueAt` / dueAt 建议,**最终由代码 side 的 `computeDueAt()` 计算**,而 `computeDueAt()` **理论上应该读这三张表**(目前未接线,见页首警告) |
| 07 Task Playbook   | n/a                                                                                                                           |

**给 SLA 接入工程师的注意**: 当 `computeDueAt()` 接入 per-store operating-hours 后,Lead Outreach / Follow-up 两类 task 的 dueAt 计算路径会变成:
`createdAt → 取当前 operating_schedule(by effective_date) → 应用 operating_override(if 同日有) → 跳过 blackout_periods(if excludeFromSla=true) → 累计 SLA 时长 → dueAt`。

***

## 5. Appendix A — Re-verification commands

> 2026-06-01 verified。文档 stale(>30 天)时重跑下方命令并比对。

```bash
# 1. 三张表所有 column
for f in operating-schedules operating-overrides blackout-periods; do
  echo "=== $f ==="
  grep -E "^\s*[a-zA-Z]+:\s*(text|integer|timestamp|boolean|numeric|jsonb|date)\(" \
    ../../../../../callytics-common/src/db/schema/$f.ts
done

# 2. 三表的 PK / index
grep -E "primaryKey|index\(" \
  ../../../../../callytics-common/src/db/schema/operating-schedules.ts \
  ../../../../../callytics-common/src/db/schema/operating-overrides.ts \
  ../../../../../callytics-common/src/db/schema/blackout-periods.ts

# 3. Task pipeline design 是否动这三张表(期望:无 match)
grep -iE "operating_schedules|operating_overrides|blackout_periods" \
  ../../tasks-feature/design/task-pipeline-deliverable-codex.md

# 4. Unified pipeline 是否动这三张表(期望:无 match)
grep -iE "operating_schedules|operating_overrides|blackout_periods" \
  ../../unified-pipeline/unified-pipeline-final.md

# 5. computeDueAt 当前是否已接入 per-store hours(grep "businessHours" 调用处)
grep -rn "computeDueAt\|businessHours" ../../../../../callytics-infrastructure/lambda/ \
  | grep -v node_modules | head -20
```

***

## 6. Cross-References

- Live schema:
  - `callytics-common/src/db/schema/operating-schedules.ts`
  - `callytics-common/src/db/schema/operating-overrides.ts`
  - `callytics-common/src/db/schema/blackout-periods.ts`
- Tasks schema(dueAt / SLA 字段的下游消费者): [../tasks-feature/tasks-schema.md](/product-design/v2/tasks-feature/tasks-schema.md)
- Calls schema: [../calls-feature/calls-schema.md](/product-design/v2/calls-feature/calls-schema.md)
- Unified pipeline: [../unified-pipeline/unified-pipeline-final.md](/product-design/v2/unified-pipeline/unified-pipeline-final.md)
- Task pipeline deliverable: [../tasks-feature/design/task-pipeline-deliverable-codex.md](/product-design/v2/tasks-feature/design/task-pipeline-deliverable-codex.md)