如何追踪S3中已上传文件的使用状态？在线编辑工具文件管理问询

针对你遇到的在线编辑工具中S3文件冗余、撤销场景兼容的问题，结合Postgres的特性，推荐一套基于自动引用追踪+延迟删除的体系，同时参考Figma类工具的通用处理逻辑：

1. 核心架构：独立文件元数据表 #

首先创建一张统一的file_metadata表，作为所有S3文件的追踪核心，替代原来每个元素手动维护文件数组的方案：

CREATE TABLE file_metadata (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    s3_key TEXT UNIQUE NOT NULL, -- S3存储的文件唯一键
    user_id UUID NOT NULL REFERENCES users(id), -- 所属用户
    project_id UUID NOT NULL REFERENCES projects(id), -- 所属项目
    reference_count INT DEFAULT 0, -- 引用次数
    last_referenced_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(), -- 最后一次被引用的时间
    delete_at TIMESTAMP WITH TIME ZONE, -- 计划删除时间（引用为0时设置）
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

这张表负责记录所有文件的归属、引用状态和删除计划，所有元素对文件的引用都通过这张表统一统计。

2. 自动提取JSONB中的文件引用 #

利用Postgres的JSONB递归遍历能力，写一个通用函数自动提取任意嵌套层级的S3文件键，避免手动维护不同元素类型的逻辑：

CREATE OR REPLACE FUNCTION extract_s3_keys(data JSONB) RETURNS TEXT[] AS $$
DECLARE
    keys TEXT[];
    key TEXT;
    value JSONB;
BEGIN
    FOR key, value IN SELECT * FROM jsonb_each(data) LOOP
        -- 根据你的S3键格式调整匹配规则，比如以特定前缀开头
        IF jsonb_typeof(value) = 'string' AND value::TEXT LIKE 's3://your-bucket/%' THEN
            keys := array_append(keys, value::TEXT);
        -- 递归处理嵌套的对象或数组
        ELSIF jsonb_typeof(value) IN ('object', 'array') THEN
            keys := array_cat(keys, extract_s3_keys(value));
        END IF;
    END LOOP;
    -- 去重后返回
    RETURN array_remove(array_distinct(keys), NULL);
END;
$$ LANGUAGE plpgsql STABLE;

然后在你的元素表上创建触发器，在元素插入、更新、软删除时自动更新file_metadata的引用计数：

• 插入元素：提取JSONB中的所有文件键，对应reference_count加1
• 更新元素：对比新旧数据的文件键列表，新增的键加1，移除的键减1
• 软删除元素：提取该元素的所有文件键，对应reference_count减1（恢复时再加1）

触发器逻辑示例（简化版）：

CREATE OR REPLACE FUNCTION update_file_references() RETURNS TRIGGER AS $$
DECLARE
    old_keys TEXT[];
    new_keys TEXT[];
    key TEXT;
BEGIN
    -- 获取旧数据和新数据的文件键列表
    old_keys := CASE WHEN TG_OP = 'INSERT' THEN '{}' ELSE extract_s3_keys(OLD.data) END;
    new_keys := CASE WHEN TG_OP = 'DELETE' THEN '{}' ELSE extract_s3_keys(NEW.data) END;

    -- 处理被移除的文件引用（计数减1）
    FOREACH key IN ARRAY array_diff(old_keys, new_keys) LOOP
        UPDATE file_metadata
        SET reference_count = reference_count - 1,
            last_referenced_at = NOW(),
            delete_at = CASE WHEN reference_count - 1 = 0 THEN NOW() + INTERVAL '7 days' ELSE NULL END
        WHERE s3_key = key;
    END LOOP;

    -- 处理新增的文件引用（计数加1）
    FOREACH key IN ARRAY array_diff(new_keys, old_keys) LOOP
        INSERT INTO file_metadata (s3_key, user_id, project_id, reference_count)
        VALUES (key, NEW.user_id, NEW.project_id, 1)
        ON CONFLICT (s3_key) DO UPDATE
        SET reference_count = file_metadata.reference_count + 1,
            last_referenced_at = NOW(),
            delete_at = NULL;
    END LOOP;

    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

-- 绑定触发器到元素表
CREATE TRIGGER trigger_element_file_refs
AFTER INSERT OR UPDATE OR DELETE ON editor_elements
FOR EACH ROW EXECUTE FUNCTION update_file_references();

注：array_diff是Postgres的数组差集函数，若版本不支持可自行实现简单的差集逻辑。

3. 延迟删除与撤销兼容 #

• 当某个文件的reference_count降到0时，自动设置delete_at为当前时间+7天（可根据业务调整），不会立即删除S3文件
• 如果用户在等待期内撤销删除（恢复元素），触发器会自动将该文件的reference_count加回1，并清空delete_at，取消删除计划
• 用定时任务（比如Postgres的pg_cron或者外部定时服务）每天执行一次清理：

  -- 找出到期且仍无引用的文件
  SELECT s3_key FROM file_metadata WHERE delete_at <= NOW() AND reference_count = 0;
  

  调用S3 API删除这些文件后，再删除file_metadata中的对应记录。

4. 补充：Figma类工具的通用逻辑 #

Figma等协同编辑工具的核心思路和上述方案一致：

• 所有资源（图片、组件等）都有独立的元数据追踪引用关系
• 元素删除时仅减少引用计数，不会立即删除资源
• 设置较长的延迟删除窗口（通常7-30天），覆盖用户撤销操作的场景
• 定期全量校验引用关系，修正异常数据（比如同步失败导致的计数不一致）

5. 额外安全机制 #

• 全量校验任务：每周执行一次全量扫描，遍历所有元素的JSONB提取文件键，和file_metadata的引用计数对比，修正不一致的情况
• 项目/用户清理：当项目删除或用户注销时，直接将该主体下所有reference_count = 0的文件标记为立即删除，非0的可提示用户手动清理或强制标记

内容的提问来源于stack exchange，提问作者ggrant