建表设计稿
下载原文件-- P28 RBAC 建表设计稿(PostgreSQL 口径)
-- 仅作设计参考,不直接执行;统一 is_deleted / created_at / updated_at 与本地触发器。
CREATE TABLE sys_user (
id bigserial PRIMARY KEY,
username text NOT NULL UNIQUE,
password_hash text NOT NULL,
real_name text,
phone text,
role_ids jsonb NOT NULL DEFAULT '[]'::jsonb,
data_scope smallint NOT NULL DEFAULT 1 CHECK (data_scope IN (1,2,3,4)),
supplier_ids jsonb NOT NULL DEFAULT '[]'::jsonb,
status smallint NOT NULL DEFAULT 1 CHECK (status IN (0,1)),
last_login_at timestamptz,
last_login_ip text,
is_deleted boolean NOT NULL DEFAULT false,
created_at timestamptz NOT NULL DEFAULT now(),
updated_at timestamptz NOT NULL DEFAULT now()
);
COMMENT ON TABLE sys_user IS '后台操作员与供应商子账号';
COMMENT ON COLUMN sys_user.data_scope IS '1 本人 2 本企业 3 全部 4 指定供应商';
COMMENT ON COLUMN sys_user.supplier_ids IS 'data_scope=4 时生效的供应商清单';
CREATE TABLE sys_role (
id bigserial PRIMARY KEY,
role_name text NOT NULL,
role_code text NOT NULL UNIQUE,
remark text,
is_builtin boolean NOT NULL DEFAULT false,
status smallint NOT NULL DEFAULT 1 CHECK (status IN (0,1)),
is_deleted boolean NOT NULL DEFAULT false,
created_at timestamptz NOT NULL DEFAULT now(),
updated_at timestamptz NOT NULL DEFAULT now()
);
COMMENT ON TABLE sys_role IS '角色表,内置角色不可删除';
CREATE TABLE sys_permission (
id bigserial PRIMARY KEY,
parent_id bigint NOT NULL DEFAULT 0,
perm_name text NOT NULL,
perm_code text NOT NULL UNIQUE,
perm_type smallint NOT NULL CHECK (perm_type IN (1,2)),
path text,
sort integer NOT NULL DEFAULT 0,
status smallint NOT NULL DEFAULT 1 CHECK (status IN (0,1)),
is_deleted boolean NOT NULL DEFAULT false,
created_at timestamptz NOT NULL DEFAULT now(),
updated_at timestamptz NOT NULL DEFAULT now()
);
COMMENT ON TABLE sys_permission IS '权限点:1 菜单 2 按钮/接口';
CREATE TABLE sys_role_permission (
id bigserial PRIMARY KEY,
role_id bigint NOT NULL REFERENCES sys_role (id) ON DELETE CASCADE,
permission_id bigint NOT NULL REFERENCES sys_permission (id) ON DELETE CASCADE,
created_at timestamptz NOT NULL DEFAULT now(),
UNIQUE (role_id, permission_id)
);
COMMENT ON TABLE sys_role_permission IS '角色与权限关联';
CREATE TABLE sys_oper_log (
id bigserial PRIMARY KEY,
user_id bigint,
username text,
oper_module text,
oper_type text,
req_url text,
ip text,
param_json jsonb,
result smallint NOT NULL DEFAULT 1 CHECK (result IN (0,1)),
created_at timestamptz NOT NULL DEFAULT now()
);
COMMENT ON TABLE sys_oper_log IS '操作审计日志,只写不改,保留三年以上';
CREATE INDEX idx_sys_oper_log_user ON sys_oper_log (user_id);
CREATE INDEX idx_sys_oper_log_time ON sys_oper_log (created_at);
CREATE TABLE sys_supplier_sub (
id bigserial PRIMARY KEY,
supplier_id text NOT NULL,
user_id bigint NOT NULL REFERENCES sys_user (id) ON DELETE CASCADE,
role_ids jsonb NOT NULL DEFAULT '[]'::jsonb,
lock_status smallint NOT NULL DEFAULT 1 CHECK (lock_status IN (0,1)),
is_deleted boolean NOT NULL DEFAULT false,
created_at timestamptz NOT NULL DEFAULT now(),
updated_at timestamptz NOT NULL DEFAULT now(),
UNIQUE (supplier_id, user_id)
);
COMMENT ON TABLE sys_supplier_sub IS '供应商子账号与归属工厂';
CREATE INDEX idx_sys_supplier_sub_supplier ON sys_supplier_sub (supplier_id);
CREATE OR REPLACE FUNCTION set_sys_updated_at()
RETURNS trigger AS $$
BEGIN
NEW.updated_at = now();
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER trg_sys_user_updated BEFORE UPDATE ON sys_user FOR EACH ROW EXECUTE FUNCTION set_sys_updated_at();
CREATE TRIGGER trg_sys_role_updated BEFORE UPDATE ON sys_role FOR EACH ROW EXECUTE FUNCTION set_sys_updated_at();
CREATE TRIGGER trg_sys_permission_updated BEFORE UPDATE ON sys_permission FOR EACH ROW EXECUTE FUNCTION set_sys_updated_at();
CREATE TRIGGER trg_sys_supplier_sub_updated BEFORE UPDATE ON sys_supplier_sub FOR EACH ROW EXECUTE FUNCTION set_sys_updated_at();
