下载中心/建表设计稿

建表设计稿

下载原文件
-- 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();