设计一套基于 CalDAV 的日程管理系统数据库

基于 CalDAV 的日程管理系统数据库设计

1. 设计目标

• 支持多用户日历管理
• 符合 CalDAV 协议（RFC 4791）和 iCalendar 数据格式（RFC 5545）
• 提供日历同步（CTag / ETag）机制
• 支持日历共享与权限控制
• 高效查询特定时间范围内的事件/任务

2. 核心实体与关系

• 用户（users）：系统用户，每个用户拥有一个或多个日历集合。
• 日历（calendars）：每个日历属于一个用户，可被共享给其他用户。
• 日历对象（calendar_objects）：每个日历包含多个对象（事件、待办等），以 iCalendar 格式存储。
• 日历共享（calendar_shares）：管理日历的共享权限（可选）。

3. 表结构设计

3.1 用户表（users）

存储用户账户信息。

CREATE TABLE users (
    id            SERIAL PRIMARY KEY,
    username      VARCHAR(255) NOT NULL UNIQUE,
    password      VARCHAR(255) NOT NULL,        -- 存储哈希密码
    display_name  VARCHAR(255),
    email         VARCHAR(255) UNIQUE,
    created_at    TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at    TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- 索引
CREATE INDEX idx_users_username ON users(username);

3.2 日历表（calendars）

每个日历对应一个 WebDAV 集合（collection）。

CREATE TABLE calendars (
    id            SERIAL PRIMARY KEY,
    user_id       INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    name          VARCHAR(255) NOT NULL,        -- 日历的唯一标识（路径后缀）
    display_name  VARCHAR(255),                 -- 显示名称
    description   TEXT,                         -- 描述
    color         VARCHAR(7) DEFAULT '#3788d8', -- 颜色（HEX）
    ctag          VARCHAR(255) NOT NULL,        -- 集合同步标记（sync token）
    created_at    TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at    TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(user_id, name)                      -- 同一用户下日历名称唯一
);

-- 索引
CREATE INDEX idx_calendars_user_id ON calendars(user_id);

3.3 日历对象表（calendar_objects）

存储每个日历中的事件、待办等资源。

CREATE TABLE calendar_objects (
    id                      SERIAL PRIMARY KEY,
    calendar_id             INTEGER NOT NULL REFERENCES calendars(id) ON DELETE CASCADE,
    resource_uri            VARCHAR(255) NOT NULL UNIQUE,  -- 资源标识符（如 uuid.ics）
    icalendar_data          TEXT NOT NULL,                 -- 完整的 iCalendar 数据
    etag                    VARCHAR(255) NOT NULL,         -- 实体标记（用于条件请求）
    summary                 TEXT,                          -- 摘要（从 iCalendar 提取，便于快速查询）
    start_time              TIMESTAMP WITH TIME ZONE,      -- 事件开始时间
    end_time                TIMESTAMP WITH TIME ZONE,      -- 事件结束时间
    recurrence_rule         TEXT,                          -- RRULE（重复规则）
    recurrence_exceptions   TEXT[],                        -- 异常日期列表（EXDATE）
    created_at              TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at              TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- 索引
CREATE INDEX idx_calendar_objects_calendar_id ON calendar_objects(calendar_id);
CREATE INDEX idx_calendar_objects_start_time ON calendar_objects(start_time);
CREATE INDEX idx_calendar_objects_end_time ON calendar_objects(end_time);
CREATE INDEX idx_calendar_objects_summary ON calendar_objects(summary) WHERE summary IS NOT NULL;

3.4 日历共享表（calendar_shares）

支持将日历共享给其他用户，并控制访问权限。

CREATE TYPE share_permission AS ENUM ('read', 'write', 'owner');

CREATE TABLE calendar_shares (
    id            SERIAL PRIMARY KEY,
    calendar_id   INTEGER NOT NULL REFERENCES calendars(id) ON DELETE CASCADE,
    user_id       INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    permission    share_permission NOT NULL DEFAULT 'read',
    created_at    TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(calendar_id, user_id)
);

-- 索引
CREATE INDEX idx_calendar_shares_calendar_id ON calendar_shares(calendar_id);
CREATE INDEX idx_calendar_shares_user_id ON calendar_shares(user_id);

4. 设计说明

4.1 数据一致性

• 使用外键约束保证引用完整性（ON DELETE CASCADE 删除用户时自动删除其日历和对象）。
• 通过 UNIQUE 约束避免重复数据（如用户名、资源 URI）。

4.2 同步支持

• calendars.ctag：每个日历集合的同步令牌，当集合内任何资源发生变化时，CTag 更新。
• calendar_objects.etag：每个资源的实体标记，基于内容哈希（如 MD5）或版本号生成，用于验证资源是否变更。

4.3 查询优化

• 提取 start_time、end_time、summary、recurrence_rule 等字段，避免频繁解析 iCalendar 文本，提高时间范围查询效率。
• 索引覆盖常用查询条件（按日历、时间范围、摘要搜索）。

4.4 重复事件处理

• recurrence_rule 存储 RRULE 字符串，recurrence_exceptions 使用 PostgreSQL 数组存储异常日期（如 {"2025-01-01","2025-01-02"}）。
• 应用层根据 RRULE 和异常生成重复事件实例，数据库仅存储规则和例外，减少数据冗余。

4.5 扩展性考虑

• 若需支持任务（VTODO），可复用 calendar_objects 表，通过 icalendar_data 内的组件类型区分；或拆分 events 和 tasks 表，但会增加复杂度。
• 如需支持附件、提醒，可增加关联表（如 attachments、alarms），但更常见的方式是将这些信息内嵌在 iCalendar 数据中，由应用层解析。

5. 示例 SQL（PostgreSQL）

-- 创建数据库
CREATE DATABASE caldav_scheduler;

-- 连接到数据库
\c caldav_scheduler;

-- 创建用户表
CREATE TABLE users (
    id            SERIAL PRIMARY KEY,
    username      VARCHAR(255) NOT NULL UNIQUE,
    password      VARCHAR(255) NOT NULL,
    display_name  VARCHAR(255),
    email         VARCHAR(255) UNIQUE,
    created_at    TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at    TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- 创建日历表
CREATE TABLE calendars (
    id            SERIAL PRIMARY KEY,
    user_id       INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    name          VARCHAR(255) NOT NULL,
    display_name  VARCHAR(255),
    description   TEXT,
    color         VARCHAR(7) DEFAULT '#3788d8',
    ctag          VARCHAR(255) NOT NULL,
    created_at    TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at    TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(user_id, name)
);

-- 创建日历对象表
CREATE TABLE calendar_objects (
    id                      SERIAL PRIMARY KEY,
    calendar_id             INTEGER NOT NULL REFERENCES calendars(id) ON DELETE CASCADE,
    resource_uri            VARCHAR(255) NOT NULL UNIQUE,
    icalendar_data          TEXT NOT NULL,
    etag                    VARCHAR(255) NOT NULL,
    summary                 TEXT,
    start_time              TIMESTAMP WITH TIME ZONE,
    end_time                TIMESTAMP WITH TIME ZONE,
    recurrence_rule         TEXT,
    recurrence_exceptions   TEXT[],
    created_at              TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at              TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- 创建枚举类型
CREATE TYPE share_permission AS ENUM ('read', 'write', 'owner');

-- 创建日历共享表
CREATE TABLE calendar_shares (
    id            SERIAL PRIMARY KEY,
    calendar_id   INTEGER NOT NULL REFERENCES calendars(id) ON DELETE CASCADE,
    user_id       INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    permission    share_permission NOT NULL DEFAULT 'read',
    created_at    TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(calendar_id, user_id)
);

-- 创建索引（根据需要添加）
CREATE INDEX idx_calendars_user_id ON calendars(user_id);
CREATE INDEX idx_calendar_objects_calendar_id ON calendar_objects(calendar_id);
CREATE INDEX idx_calendar_objects_start_time ON calendar_objects(start_time);
CREATE INDEX idx_calendar_objects_end_time ON calendar_objects(end_time);
CREATE INDEX idx_calendar_objects_summary ON calendar_objects(summary) WHERE summary IS NOT NULL;
CREATE INDEX idx_calendar_shares_calendar_id ON calendar_shares(calendar_id);
CREATE INDEX idx_calendar_shares_user_id ON calendar_shares(user_id);

6. 注意事项

• 时区处理：TIMESTAMP WITH TIME ZONE 存储带时区的时间戳，确保跨时区事件正确。
• 同步令牌：ctag 和 etag 的更新需在事务中完成，保证一致性。
• 大文本字段：icalendar_data 可能较大，使用 TEXT 类型，注意数据库配置（如 PostgreSQL 的 TOAST）。
• 安全：密码使用强哈希（如 bcrypt），避免明文存储。
• 扩展：若需支持“日历组”或“集合集合”，可参考 CalDAV 的“principal”概念，增加 principals 表，但以上设计已满足基本需求。

此数据库设计为基于 CalDAV 的日程管理系统提供了稳固的基础，兼顾了性能、扩展性和协议兼容性。