smart-water表设计方案

-- =================================================================
-- 2. SOP 管理模块
-- =================================================================

-- 2.1 SOP主表 (存储SOP元数据，与版本分离)
CREATE TABLE sops (
                      id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
                      name VARCHAR(255) NOT NULL,
                      description TEXT,
                      latest_published_version_id UUID, -- 外键约束在版本表创建后添加
                      created_by UUID NOT NULL REFERENCES users(id),
                      created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
                      updated_by UUID NOT NULL REFERENCES users(id),
                      updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
COMMENT ON TABLE sops IS 'SOP主信息表，存储SOP的基本信息，与版本表分离便于版本管理';
COMMENT ON COLUMN sops.id IS 'SOP主记录唯一标识（GUID）';
COMMENT ON COLUMN sops.name IS 'SOP名称，如"车间设备每日巡检流程"';
COMMENT ON COLUMN sops.latest_published_version_id IS '指向最新发布的SOP版本，快速获取有效版本';
COMMENT ON COLUMN sops.created_by IS '创建者ID，外键引用users表';

-- 2.2 SOP版本表 (存储SOP的具体版本定义)
CREATE TABLE sop_versions (
                              id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
                              sop_id UUID NOT NULL REFERENCES sops(id) ON DELETE CASCADE,
                              version_string VARCHAR(50) NOT NULL,
                              status VARCHAR(20) NOT NULL CHECK (status IN ('Draft', 'Published', 'Archived')),
                              definition JSONB NOT NULL,
                              created_by UUID NOT NULL REFERENCES users(id),
                              created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
                              published_at TIMESTAMPTZ,
                              UNIQUE (sop_id, version_string)
);
COMMENT ON TABLE sop_versions IS 'SOP版本表，存储SOP的具体版本定义，支持版本追溯与迭代';
COMMENT ON COLUMN sop_versions.sop_id IS '关联的SOP主记录ID，外键引用sops表';
COMMENT ON COLUMN sop_versions.version_string IS '版本号，如"V1.0"、"V2.1-Draft"，同一SOP内唯一';
COMMENT ON COLUMN sop_versions.status IS '版本状态：Draft（草稿）、Published（已发布）、Archived（已归档）';
COMMENT ON COLUMN sop_versions.definition IS 'SOP流程定义（JSONB），包含节点、连线及配置信息';
COMMENT ON COLUMN sop_versions.published_at IS '版本发布时间，仅当状态为Published或Archived时有效';

-- 关联SOP主表与最新版本，并设置为可延迟，以解决循环依赖问题
ALTER TABLE sops
    ADD CONSTRAINT fk_sops_latest_version
        FOREIGN KEY (latest_published_version_id)
            REFERENCES sop_versions(id)
            ON DELETE SET NULL -- 如果版本被删除，仅将此链接置空
            DEFERRABLE INITIALLY DEFERRED;
COMMENT ON CONSTRAINT fk_sops_latest_version ON sops IS 'SOP主表与最新版本的外键关联。可延迟以方便在同一事务中创建SOP及其首个版本。';

user表 

create table sys_user
(
    user_id     bigint      not null
        constraint sys_user_pk
            primary key,
    tenant_id   varchar(20)  default '000000'::character varying,
    dept_id     bigint,
    user_name   varchar(30) not null,
    nick_name   varchar(30) not null,
    user_type   varchar(10)  default 'sys_user'::character varying,
    email       varchar(50)  default ''::character varying,
    phonenumber varchar(11)  default ''::character varying,
    sex         char         default '0'::bpchar,
    avatar      bigint,
    password    varchar(100) default ''::character varying,
    status      char         default '0'::bpchar,
    del_flag    char         default '0'::bpchar,
    login_ip    varchar(128) default ''::character varying,
    login_date  timestamp,
    create_dept bigint,
    create_by   bigint,
    create_time timestamp,
    update_by   bigint,
    update_time timestamp,
    remark      varchar(500) default NULL::character varying
);

comment on table sys_user is '用户信息表';

comment on column sys_user.user_id is '用户ID';

comment on column sys_user.tenant_id is '租户编号';

comment on column sys_user.dept_id is '部门ID';

comment on column sys_user.user_name is '用户账号';

comment on column sys_user.nick_name is '用户昵称';

comment on column sys_user.user_type is '用户类型（sys_user系统用户）';

comment on column sys_user.email is '用户邮箱';

comment on column sys_user.phonenumber is '手机号码';

comment on column sys_user.sex is '用户性别（0男 1女 2未知）';

comment on column sys_user.avatar is '头像地址';

comment on column sys_user.password is '密码';

comment on column sys_user.status is '帐号状态（0正常 1停用）';

comment on column sys_user.del_flag is '删除标志（0代表存在 1代表删除）';

comment on column sys_user.login_ip is '最后登陆IP';

comment on column sys_user.login_date is '最后登陆时间';

comment on column sys_user.create_dept is '创建部门';

comment on column sys_user.create_by is '创建者';

comment on column sys_user.create_time is '创建时间';

comment on column sys_user.update_by is '更新者';

comment on column sys_user.update_time is '更新时间';

comment on column sys_user.remark is '备注';

alter table sys_user
    owner to postgres;

SOP管理模块

总和

-- 2.1 SOP主表 (存储SOP元数据，与版本分离)
CREATE TABLE sops (
                      id BIGINT PRIMARY KEY,
                      name VARCHAR(255) NOT NULL,
                      description TEXT,
                      latest_published_version_id BIGINT,
                      created_by BIGINT NOT NULL REFERENCES sys_user(user_id),
                      created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
                      updated_by BIGINT NOT NULL REFERENCES sys_user(user_id),
                      updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
COMMENT ON TABLE sops IS 'SOP主信息表，存储SOP的基本信息，与版本表分离便于版本管理';
COMMENT ON COLUMN sops.id IS 'SOP主记录唯一标识（GUID）';
COMMENT ON COLUMN sops.name IS 'SOP名称，如"车间设备每日巡检流程"';
COMMENT ON COLUMN sops.latest_published_version_id IS '指向最新发布的SOP版本，快速获取有效版本';
COMMENT ON COLUMN sops.created_by IS '创建者ID，外键引用sys_user表';


-- 2.2 SOP版本表 (存储SOP的具体版本定义)
CREATE TABLE sop_versions (
                              id BIGINT PRIMARY KEY,
                              sop_id BIGINT NOT NULL REFERENCES sops(id) ON DELETE CASCADE,
                              version_string VARCHAR(50) NOT NULL,
                              status VARCHAR(20) NOT NULL CHECK (status IN ('Draft', 'Published', 'Archived')),
                              definition JSONB NOT NULL,
                              created_by BIGINT NOT NULL REFERENCES sys_user(user_id),
                              created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
                              published_at TIMESTAMPTZ,
                              UNIQUE (sop_id, version_string)
);
COMMENT ON TABLE sop_versions IS 'SOP版本表，存储SOP的具体版本定义，支持版本追溯与迭代';
COMMENT ON COLUMN sop_versions.sop_id IS '关联的SOP主记录ID，外键引用sops表';
COMMENT ON COLUMN sop_versions.version_string IS '版本号，如"V1.0"、"V2.1-Draft"，同一SOP内唯一';
COMMENT ON COLUMN sop_versions.status IS '版本状态：Draft（草稿）、Published（已发布）、Archived（已归档）';
COMMENT ON COLUMN sop_versions.definition IS 'SOP流程定义（JSONB），包含节点、连线及配置信息';
COMMENT ON COLUMN sop_versions.published_at IS '版本发布时间，仅当状态为Published或Archived时有效';



-- 关联SOP主表与最新版本，并设置为可延迟，以解决循环依赖问题
ALTER TABLE sops
    ADD CONSTRAINT fk_sops_latest_version
        FOREIGN KEY (latest_published_version_id)
            REFERENCES sop_versions(id)
            ON DELETE SET NULL -- 如果版本被删除，仅将此链接置空
            DEFERRABLE INITIALLY DEFERRED;
COMMENT ON CONSTRAINT fk_sops_latest_version ON sops IS 'SOP主表与最新版本的外键关联。可延迟以方便在同一事务中创建SOP及其首个版本。';

SOP主表（存储SOP元数据，与版本分离）

-- 2.1 SOP主表 (存储SOP元数据，与版本分离)
CREATE TABLE sops (
                      id BIGINT PRIMARY KEY,
                      name VARCHAR(255) NOT NULL,
                      description TEXT,
                      latest_published_version_id BIGINT,
                      created_by BIGINT NOT NULL REFERENCES sys_user(user_id),
                      created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
                      updated_by BIGINT NOT NULL REFERENCES sys_user(user_id),
                      updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
COMMENT ON TABLE sops IS 'SOP主信息表，存储SOP的基本信息，与版本表分离便于版本管理';
COMMENT ON COLUMN sops.id IS 'SOP主记录唯一标识（GUID）';
COMMENT ON COLUMN sops.name IS 'SOP名称，如"车间设备每日巡检流程"';
COMMENT ON COLUMN sops.latest_published_version_id IS '指向最新发布的SOP版本，快速获取有效版本';
COMMENT ON COLUMN sops.created_by IS '创建者ID，外键引用sys_user表';

SOP版本表 (存储SOP的具体版本定义)

-- 2.2 SOP版本表 (存储SOP的具体版本定义)
CREATE TABLE sop_versions (
                              id BIGINT PRIMARY KEY,
                              sop_id BIGINT NOT NULL REFERENCES sops(id) ON DELETE CASCADE,
                              version_string VARCHAR(50) NOT NULL,
                              status VARCHAR(20) NOT NULL CHECK (status IN ('Draft', 'Published', 'Archived')),
                              definition JSONB NOT NULL,
                              created_by BIGINT NOT NULL REFERENCES sys_user(user_id),
                              created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
                              published_at TIMESTAMPTZ,
                              UNIQUE (sop_id, version_string)
);
COMMENT ON TABLE sop_versions IS 'SOP版本表，存储SOP的具体版本定义，支持版本追溯与迭代';
COMMENT ON COLUMN sop_versions.sop_id IS '关联的SOP主记录ID，外键引用sops表';
COMMENT ON COLUMN sop_versions.version_string IS '版本号，如"V1.0"、"V2.1-Draft"，同一SOP内唯一';
COMMENT ON COLUMN sop_versions.status IS '版本状态：Draft（草稿）、Published（已发布）、Archived（已归档）';
COMMENT ON COLUMN sop_versions.definition IS 'SOP流程定义（JSONB），包含节点、连线及配置信息';
COMMENT ON COLUMN sop_versions.published_at IS '版本发布时间，仅当状态为Published或Archived时有效';

关联SOP主表与最新版本，并设置为可延迟，以解决循环依赖问题

-- 关联SOP主表与最新版本，并设置为可延迟，以解决循环依赖问题
ALTER TABLE sops
    ADD CONSTRAINT fk_sops_latest_version
        FOREIGN KEY (latest_published_version_id)
            REFERENCES sop_versions(id)
            ON DELETE SET NULL -- 如果版本被删除，仅将此链接置空
            DEFERRABLE INITIALLY DEFERRED;
COMMENT ON CONSTRAINT fk_sops_latest_version ON sops IS 'SOP主表与最新版本的外键关联。可延迟以方便在同一事务中创建SOP及其首个版本。';