SCRIPTS.md

用户

CREATE TABLE "public"."users" (
  "id" int4 NOT NULL GENERATED ALWAYS AS IDENTITY (
INCREMENT 1
MINVALUE  1
MAXVALUE 2147483647
START 1000001
CACHE 1
),
  "username" varchar(50) COLLATE "pg_catalog"."default" NOT NULL,
  "password_hash" varchar(512) COLLATE "pg_catalog"."default" NOT NULL,
  "password_salt" varchar(256) COLLATE "pg_catalog"."default" NOT NULL,
  "email" varchar(255) COLLATE "pg_catalog"."default",
  "phone" varchar(20) COLLATE "pg_catalog"."default",
  "fullname" varchar(50) COLLATE "pg_catalog"."default",
  "avatar" varchar(255) COLLATE "pg_catalog"."default",
  "biography" text COLLATE "pg_catalog"."default",
  "lockout_time" timestamp(6),
  "access_failed_count" int4 NOT NULL DEFAULT 0,
  "create_time" timestamp(6) NOT NULL,
  "update_time" timestamp(6),
  "delete_time" timestamp(6),
  "is_deleted" bool NOT NULL DEFAULT false,
  PRIMARY KEY ("id")
)
;

CREATE UNIQUE INDEX "IDX_EMAIL" ON "public"."users" USING btree (
  "email" COLLATE "pg_catalog"."default" "pg_catalog"."text_ops" ASC NULLS LAST
);

CREATE UNIQUE INDEX "IDX_PHONE" ON "public"."users" USING btree (
  "phone" COLLATE "pg_catalog"."default" "pg_catalog"."text_ops" ASC NULLS LAST
);

CREATE UNIQUE INDEX "IDX_USERNAME" ON "public"."users" USING btree (
  "username" COLLATE "pg_catalog"."default" "pg_catalog"."text_ops" ASC NULLS LAST
);

ALTER TABLE "public"."users" 
  OWNER TO "postgres";

COMMENT ON COLUMN "public"."users"."id" IS '主键';
COMMENT ON COLUMN "public"."users"."username" IS '用户名';
COMMENT ON COLUMN "public"."users"."password_hash" IS '密码Hash';
COMMENT ON COLUMN "public"."users"."password_salt" IS '密码Salt';
COMMENT ON COLUMN "public"."users"."email" IS '邮件';
COMMENT ON COLUMN "public"."users"."phone" IS '手机号';
COMMENT ON COLUMN "public"."users"."fullname" IS '真实姓名';
COMMENT ON COLUMN "public"."users"."avatar" IS '头像';
COMMENT ON COLUMN "public"."users"."lockout_time" IS '锁定截止时间';
COMMENT ON COLUMN "public"."users"."access_failed_count" IS '认证失败次数';
COMMENT ON COLUMN "public"."users"."create_time" IS '创建时间';
COMMENT ON COLUMN "public"."users"."update_time" IS '更新时间';
COMMENT ON COLUMN "public"."users"."delete_time" IS '删除时间';
COMMENT ON COLUMN "public"."users"."is_deleted" IS '是否删除';
COMMENT ON COLUMN "public"."users"."biography" IS '个人简介';
COMMENT ON TABLE "public"."users" IS '用户';

角色

CREATE TABLE "public"."roles" (
  "id" int4 NOT NULL GENERATED BY DEFAULT AS IDENTITY (
INCREMENT 1
MINVALUE  1
MAXVALUE 2147483647
START 1000001
CACHE 1
),
  "code" varchar(50) COLLATE "pg_catalog"."default" NOT NULL DEFAULT ''::character varying,
  "name" varchar(50) COLLATE "pg_catalog"."default" NOT NULL,
  "description" text COLLATE "pg_catalog"."default",
  "create_by" int4 NOT NULL,
  "create_at" timestamp(6) NOT NULL,
  "update_by" int4,
  "update_at" timestamp(6),
  PRIMARY KEY ("id"),
  UNIQUE ("code")
)
;

ALTER TABLE "public"."roles" 
  OWNER TO "postgres";

COMMENT ON COLUMN "public"."roles"."id" IS '主键';
COMMENT ON COLUMN "public"."roles"."code" IS '代码';
COMMENT ON COLUMN "public"."roles"."name" IS '名称';
COMMENT ON COLUMN "public"."roles"."description" IS '描述';
COMMENT ON COLUMN "public"."roles"."create_by" IS '创建人Id';
COMMENT ON COLUMN "public"."roles"."create_at" IS '创建时间';
COMMENT ON COLUMN "public"."roles"."update_by" IS '更新人Id';
COMMENT ON COLUMN "public"."roles"."update_at" IS '更新时间';
COMMENT ON TABLE "public"."roles" IS '角色';

用户角色配置

CREATE TABLE "public"."user_role" (
  "id" int4 NOT NULL GENERATED BY DEFAULT AS IDENTITY (
INCREMENT 1
MINVALUE  1
MAXVALUE 2147483647
START 1000001
CACHE 1
),
  "user_id" int4 NOT NULL,
  "role_id" int4 NOT NULL,
  "create_at" timestamptz(6) NOT NULL DEFAULT (now() AT TIME ZONE 'utc'::text),
  PRIMARY KEY ("id")
)
;

CREATE INDEX "IDX_USER_ID" ON "public"."user_role" (
  "user_id"
);

CREATE INDEX "IDX_ROLE_ID" ON "public"."user_role" (
  "role_id"
);

CREATE UNIQUE INDEX "IDX_UNIQUE" ON "public"."user_role" (
  "user_id",
  "role_id"
);

ALTER TABLE "public"."user_role" 
  OWNER TO "postgres";

COMMENT ON COLUMN "public"."user_role"."id" IS '主键';
COMMENT ON COLUMN "public"."user_role"."user_id" IS '用户Id';
COMMENT ON COLUMN "public"."user_role"."role_id" IS '角色Id';
COMMENT ON COLUMN "public"."user_role"."create_at" IS '创建时间';
COMMENT ON TABLE "public"."user_role" IS '用户角色配置';

公众号信息

CREATE TABLE "public"."wechat_account" (
  "id" varchar(40) COLLATE "pg_catalog"."default" NOT NULL,
  "account" varchar(50) COLLATE "pg_catalog"."default",
  "name" varchar(100) COLLATE "pg_catalog"."default" NOT NULL,
  "type" varchar(20) COLLATE "pg_catalog"."default" NOT NULL,
  "description" text COLLATE "pg_catalog"."default",
  "image" varchar(255) COLLATE "pg_catalog"."default",
  "app_id" varchar(50) COLLATE "pg_catalog"."default" NOT NULL,
  "verified" bool NOT NULL DEFAULT false,
  "create_by" int4 NOT NULL,
  "create_at" timestamp(6) NOT NULL,
  "update_by" int4,
  "update_at" timestamp(6),
  PRIMARY KEY ("id")
)
;

ALTER TABLE "public"."wechat_account" 
  OWNER TO "postgres";

COMMENT ON COLUMN "public"."wechat_account"."id" IS '原始ID';
COMMENT ON COLUMN "public"."wechat_account"."account" IS '微信号';
COMMENT ON COLUMN "public"."wechat_account"."name" IS '名称';
COMMENT ON COLUMN "public"."wechat_account"."type" IS '类型';
COMMENT ON COLUMN "public"."wechat_account"."description" IS '公众号简介';
COMMENT ON COLUMN "public"."wechat_account"."image" IS '公众号头像';
COMMENT ON COLUMN "public"."wechat_account"."app_id" IS '开发者ID';
COMMENT ON COLUMN "public"."wechat_account"."verified" IS '是否已认证';
COMMENT ON COLUMN "public"."wechat_account"."create_by" IS '创建人Id';
COMMENT ON COLUMN "public"."wechat_account"."create_at" IS '创建时间';
COMMENT ON COLUMN "public"."wechat_account"."update_by" IS '更新人Id';
COMMENT ON COLUMN "public"."wechat_account"."update_at" IS '更新时间';
COMMENT ON TABLE "public"."wechat_account" IS '公众号信息';

公众号消息

CREATE TABLE "public"."wechat_message" (
  "id" int8 NOT NULL,
  "type" varchar(20) COLLATE "pg_catalog"."default" NOT NULL,
  "payload" text COLLATE "pg_catalog"."default",
  "content" text COLLATE "pg_catalog"."default",
  "create_time" timestamp(6) NOT NULL,
  "reply" text COLLATE "pg_catalog"."default",
  "open_id" varchar(40) COLLATE "pg_catalog"."default",
  "account_id" varchar(40) COLLATE "pg_catalog"."default" NOT NULL,
  PRIMARY KEY ("id")
)
;

CREATE INDEX "idx_account_id" ON "public"."wechat_message" USING btree (
  "account_id" COLLATE "pg_catalog"."default" "pg_catalog"."text_ops" ASC NULLS LAST
);

CREATE INDEX "idx_openid" ON "public"."wechat_message" USING btree (
  "open_id" COLLATE "pg_catalog"."default" "pg_catalog"."text_ops" ASC NULLS LAST
);

ALTER TABLE "public"."wechat_message" 
  OWNER TO "postgres";

COMMENT ON COLUMN "public"."wechat_message"."id" IS '主键';
COMMENT ON COLUMN "public"."wechat_message"."type" IS '类型';
COMMENT ON COLUMN "public"."wechat_message"."payload" IS '完整报文负载';
COMMENT ON COLUMN "public"."wechat_message"."create_time" IS '发送时间';
COMMENT ON COLUMN "public"."wechat_message"."reply" IS '回复内容';
COMMENT ON COLUMN "public"."wechat_message"."open_id" IS '用户open id';
COMMENT ON COLUMN "public"."wechat_message"."account_id" IS '公众号Id';
COMMENT ON COLUMN "public"."wechat_message"."content" IS '消息内容';
COMMENT ON TABLE "public"."wechat_message_" IS '公众号消息';

公众号粉丝

CREATE TABLE "public"."wechat_follower" (
  "id" int8 NOT NULL GENERATED BY DEFAULT AS IDENTITY (
INCREMENT 1
MINVALUE  1
MAXVALUE 9223372036854775807
START 10000001
CACHE 1
),
  "open_id" varchar(32) COLLATE "pg_catalog"."default" NOT NULL,
  "nick_name" varchar(255) COLLATE "pg_catalog"."default",
  "subscribe_time" timestamp(6),
  "unsubscribe_time" timestamp(6),
  "state" int4 NOT NULL,
  "chatbot_enabled" bool NOT NULL DEFAULT false,
  "account_id" varchar(40) COLLATE "pg_catalog"."default",
  "avatar" text COLLATE "pg_catalog"."default",
  "create_time" timestamp(6) NOT NULL,
  PRIMARY KEY ("id")
)
;

CREATE UNIQUE INDEX "IDX_OPEN_ID" ON "public"."wechat_follower" USING btree (
  "open_id" COLLATE "pg_catalog"."default" "pg_catalog"."text_ops" ASC NULLS LAST
);

CREATE INDEX "IDX_ACCOUNT_ID" ON "public"."wechat_follower" USING btree (
  "account_id" COLLATE "pg_catalog"."default" "pg_catalog"."text_ops" ASC NULLS LAST
);

ALTER TABLE "public"."wechat_follower" 
  OWNER TO "postgres";

COMMENT ON COLUMN "public"."wechat_follower"."id" IS '主键';
COMMENT ON COLUMN "public"."wechat_follower"."open_id" IS '用户open id';
COMMENT ON COLUMN "public"."wechat_follower"."nick_name" IS '昵称';
COMMENT ON COLUMN "public"."wechat_follower"."create_time" IS '创建时间';
COMMENT ON COLUMN "public"."wechat_follower"."subscribe_time" IS '关注时间';
COMMENT ON COLUMN "public"."wechat_follower"."unsubscribe_time" IS '取消关注时间';
COMMENT ON COLUMN "public"."wechat_follower"."state" IS '状态';
COMMENT ON COLUMN "public"."wechat_follower"."chatbot_enabled" IS '是否运行AI对话';
COMMENT ON COLUMN "public"."wechat_follower"."account_id" IS '公众号Id';
COMMENT ON COLUMN "public"."wechat_follower"."avatar" IS '头像';
COMMENT ON TABLE "public"."wechat_follower" IS '公众号粉丝';

公众号菜单

CREATE TABLE "public"."wechat_menu" (
  "id" int4 NOT NULL GENERATED ALWAYS AS IDENTITY (
INCREMENT 1
MINVALUE  1
MAXVALUE 2147483647
START 1000001
CACHE 1
),
  "parent_id" int4 NOT NULL DEFAULT 0,
  "account_id" varchar(40) COLLATE "pg_catalog"."default" NOT NULL,
  "type" varchar(20) COLLATE "pg_catalog"."default" NOT NULL,
  "name" varchar(120) COLLATE "pg_catalog"."default" NOT NULL,
  "key" varchar(255) COLLATE "pg_catalog"."default",
  "url" varchar(1024) COLLATE "pg_catalog"."default",
  "mini_app_id" varchar(64) COLLATE "pg_catalog"."default",
  "mini_app_page" varchar(255) COLLATE "pg_catalog"."default",
  "sort" int4 NOT NULL,
  "is_valid" bool NOT NULL DEFAULT true,
  "create_time" timestamp(6) NOT NULL,
  "update_time" timestamp(6),
  PRIMARY KEY ("id")
)
;

CREATE INDEX "idx_parent_id" ON "public"."wechat_menu" USING btree (
  "parent_id" "pg_catalog"."int4_ops" ASC NULLS LAST
);

ALTER TABLE "public"."wechat_menu" 
  OWNER TO "postgres";

COMMENT ON COLUMN "public"."wechat_menu"."id" IS '主键';
COMMENT ON COLUMN "public"."wechat_menu"."parent_id" IS '上级菜单Id';
COMMENT ON COLUMN "public"."wechat_menu"."type" IS '菜单的响应动作类型';
COMMENT ON COLUMN "public"."wechat_menu"."name" IS '菜单标题';
COMMENT ON COLUMN "public"."wechat_menu"."key" IS '菜单KEY值';
COMMENT ON COLUMN "public"."wechat_menu"."url" IS '网页链接';
COMMENT ON COLUMN "public"."wechat_menu"."mini_app_id" IS '小程序的appid';
COMMENT ON COLUMN "public"."wechat_menu"."mini_app_page" IS '小程序的页面路径';
COMMENT ON COLUMN "public"."wechat_menu"."sort" IS '排序';
COMMENT ON COLUMN "public"."wechat_menu"."is_valid" IS '是否有效';
COMMENT ON COLUMN "public"."wechat_menu"."create_time" IS '创建时间';
COMMENT ON COLUMN "public"."wechat_menu"."update_time" IS '更新时间';
COMMENT ON COLUMN "public"."wechat_menu"."account_id" IS '公众号Id';
COMMENT ON TABLE "public"."wechat_menu_" IS '公众号菜单';