lovife
7df078e0ea
- 交接摘要补 §一·三十五~§一·三十八：角色与授权、需求收敛（4 角色 / 10 菜单 / 取消二期）、 PC 端重搭后的 UI 修复与卡片式基线、品牌改名与新标识 - PRD 与功能逻辑卷只改标题行与产品代号线；**文件名不动** —— verify-docs.py 按路径读， 全库还有 20+ 处按文件名引用，改名会连带炸脚本 - 原型 index / admin-web / teacher-app / student-app 的品牌区改「深学在线」， admin-web 的 .mark（10 处）换成内嵌的新标 data URI，保持原型"单文件自包含"的约定 - 根 AGENTS.md 与 frontend-pc 的规范同步：接线检查补第 ⑨ 项（SVG 资产是不是合法 XML —— SVG 注释里出现连续两个减号就 not well-formed，图标裂开而控制台一句话都不报）、 「最容易写错的约定」6 -> 7 条（新增品牌标） - deploy/docker-compose.yml 只改头注释里的项目名（compose 的 name 与七牛对象前缀都没动）
722 lines
38 KiB
SQL
722 lines
38 KiB
SQL
-- ============ 1) 建表 ============
|
||
--
|
||
-- ⚠️ 2026-10 需求收敛：本文件是**初始建表稿**（V1 口径），**不是当前库结构的完整描述**。
|
||
-- 已删除的对象见 `backend/xt-server/src/main/resources/db/migration/V19__remove_deputy_auditor_cleanup_join.sql`：
|
||
-- · `collect_task_org.deputy_user_ids`（协办人列）—— 已 DROP
|
||
-- · `collect_cleanup_job`（历史整理器）—— 已 DROP
|
||
-- · `collect_join_request`（加入申请）—— 已 DROP
|
||
-- 下面这三处的建表语句**刻意保留**（历史沿革可追溯），但新库是靠 V1→V19 全量迁移得到的，
|
||
-- 最终结构里没有它们。要核对"当前真实结构"，请查 `information_schema` 或跑
|
||
-- `python scripts/check-entity-columns.py`（实体 ↔ 建表一致性）。
|
||
|
||
CREATE TABLE sys_tenant (
|
||
id bigint GENERATED BY DEFAULT AS IDENTITY NOT NULL,
|
||
tenant_code varchar(32) NOT NULL,
|
||
tenant_name varchar(128) NOT NULL,
|
||
tenant_type smallint NOT NULL DEFAULT 1,
|
||
contact_name varchar(64),
|
||
contact_phone varchar(128),
|
||
package_id bigint,
|
||
expire_time timestamp,
|
||
status smallint NOT NULL DEFAULT 1,
|
||
logo_url varchar(512),
|
||
config_json jsonb,
|
||
create_by bigint,
|
||
create_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
update_by bigint,
|
||
update_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
deleted smallint DEFAULT 0 NOT NULL,
|
||
PRIMARY KEY (id),
|
||
CONSTRAINT uk_tenant_code UNIQUE (tenant_code)
|
||
);
|
||
COMMENT ON TABLE sys_tenant IS '租户';
|
||
COMMENT ON COLUMN sys_tenant.id IS '租户ID';
|
||
COMMENT ON COLUMN sys_tenant.tenant_code IS '租户编码(短码,用于小程序扫码识别)';
|
||
COMMENT ON COLUMN sys_tenant.tenant_name IS '租户名称(学校/机构)';
|
||
COMMENT ON COLUMN sys_tenant.tenant_type IS '1学校 2认定机构 3报名点 4企业';
|
||
COMMENT ON COLUMN sys_tenant.contact_name IS '联系人';
|
||
COMMENT ON COLUMN sys_tenant.contact_phone IS '联系电话(AES加密存储;varchar(128)容纳密文,非明文长度)';
|
||
COMMENT ON COLUMN sys_tenant.package_id IS '套餐ID';
|
||
COMMENT ON COLUMN sys_tenant.expire_time IS '到期时间';
|
||
COMMENT ON COLUMN sys_tenant.status IS '0停用 1正常 2试用';
|
||
COMMENT ON COLUMN sys_tenant.logo_url IS 'Logo';
|
||
COMMENT ON COLUMN sys_tenant.config_json IS '租户配置(默认压缩参数/文件保留天数等)';
|
||
|
||
CREATE TABLE sys_org (
|
||
id bigint GENERATED BY DEFAULT AS IDENTITY NOT NULL,
|
||
tenant_id bigint NOT NULL,
|
||
parent_id bigint NOT NULL DEFAULT 0,
|
||
org_name varchar(128) NOT NULL,
|
||
org_type smallint NOT NULL DEFAULT 3,
|
||
org_code varchar(64),
|
||
ancestor_path varchar(512),
|
||
leader_user_id bigint,
|
||
sort_no integer NOT NULL DEFAULT 0,
|
||
status smallint NOT NULL DEFAULT 1,
|
||
create_by bigint,
|
||
create_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
update_by bigint,
|
||
update_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
deleted smallint DEFAULT 0 NOT NULL,
|
||
PRIMARY KEY (id)
|
||
);
|
||
CREATE INDEX idx_tenant_parent ON sys_org (tenant_id, parent_id);
|
||
CREATE INDEX idx_tenant_name ON sys_org (tenant_id, org_name);
|
||
COMMENT ON TABLE sys_org IS '组织架构';
|
||
COMMENT ON COLUMN sys_org.parent_id IS '父级ID,0为根';
|
||
COMMENT ON COLUMN sys_org.org_name IS '组织名称,如 24中技数控1班';
|
||
COMMENT ON COLUMN sys_org.org_type IS '1学校 2院系/专业 3班级 4报名点';
|
||
COMMENT ON COLUMN sys_org.org_code IS '组织编码/班号';
|
||
COMMENT ON COLUMN sys_org.ancestor_path IS '祖先路径,如 0,1,5';
|
||
COMMENT ON COLUMN sys_org.leader_user_id IS '负责人(班主任)';
|
||
|
||
CREATE TABLE sys_occupation_ref (
|
||
id bigint GENERATED BY DEFAULT AS IDENTITY NOT NULL,
|
||
parent_id bigint NOT NULL DEFAULT 0,
|
||
name varchar(128) NOT NULL,
|
||
aliases varchar(512),
|
||
has_child smallint NOT NULL DEFAULT 0,
|
||
status smallint NOT NULL DEFAULT 1,
|
||
create_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
PRIMARY KEY (id)
|
||
);
|
||
CREATE INDEX idx_parent ON sys_occupation_ref (parent_id);
|
||
CREATE INDEX idx_occref_name ON sys_occupation_ref (name);
|
||
COMMENT ON TABLE sys_occupation_ref IS '职业工种参考字典(仅拼写校验,非约束)';
|
||
COMMENT ON COLUMN sys_occupation_ref.parent_id IS '0=职业; 非0=该职业下的工种';
|
||
COMMENT ON COLUMN sys_occupation_ref.aliases IS '常见错写/别名,用于纠错提示';
|
||
COMMENT ON COLUMN sys_occupation_ref.has_child IS '该职业是否有工种';
|
||
|
||
CREATE TABLE sys_user (
|
||
id bigint GENERATED BY DEFAULT AS IDENTITY NOT NULL,
|
||
tenant_id bigint NOT NULL DEFAULT 0,
|
||
org_id bigint,
|
||
username varchar(64) NOT NULL,
|
||
password varchar(128),
|
||
real_name varchar(64) NOT NULL,
|
||
phone varchar(128),
|
||
phone_hash char(64),
|
||
open_id varchar(64),
|
||
union_id varchar(64),
|
||
id_card_no varchar(128),
|
||
id_card_hash char(64),
|
||
avatar_url varchar(512),
|
||
user_type smallint NOT NULL DEFAULT 1,
|
||
status smallint NOT NULL DEFAULT 1,
|
||
last_login_time timestamp,
|
||
create_by bigint,
|
||
create_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
update_by bigint,
|
||
update_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
deleted smallint DEFAULT 0 NOT NULL,
|
||
PRIMARY KEY (id),
|
||
CONSTRAINT uk_tenant_username UNIQUE (tenant_id, username)
|
||
);
|
||
CREATE INDEX idx_phone_hash ON sys_user (phone_hash);
|
||
CREATE INDEX idx_idcard_hash ON sys_user (id_card_hash);
|
||
CREATE INDEX idx_openid ON sys_user (open_id);
|
||
COMMENT ON TABLE sys_user IS '用户';
|
||
COMMENT ON COLUMN sys_user.tenant_id IS '0=平台运营';
|
||
COMMENT ON COLUMN sys_user.org_id IS '所属组织';
|
||
COMMENT ON COLUMN sys_user.username IS '登录名(教师)';
|
||
COMMENT ON COLUMN sys_user.password IS 'BCrypt';
|
||
COMMENT ON COLUMN sys_user.phone IS '手机号(AES加密存储;varchar(128)容纳密文,非明文长度)';
|
||
COMMENT ON COLUMN sys_user.phone_hash IS '手机号HMAC,用于唯一检索';
|
||
COMMENT ON COLUMN sys_user.open_id IS '微信openid';
|
||
COMMENT ON COLUMN sys_user.id_card_no IS '证件号(AES加密存储)';
|
||
COMMENT ON COLUMN sys_user.id_card_hash IS '证件号HMAC,用于比对与去重';
|
||
COMMENT ON COLUMN sys_user.user_type IS '1平台运营 2学校管理员 3班主任 4学生（口径见 03-接口设计 §2.7）';
|
||
COMMENT ON COLUMN sys_user.status IS '0停用 1正常 2待激活';
|
||
|
||
CREATE TABLE collect_task (
|
||
id bigint GENERATED BY DEFAULT AS IDENTITY NOT NULL,
|
||
tenant_id bigint NOT NULL,
|
||
task_code varchar(64) NOT NULL,
|
||
task_name varchar(200) NOT NULL,
|
||
collect_mode smallint NOT NULL DEFAULT 1,
|
||
batch_no varchar(64),
|
||
major_name varchar(64),
|
||
job_name varchar(64),
|
||
job_level varchar(32),
|
||
cert_type smallint NOT NULL DEFAULT 1,
|
||
start_time timestamp,
|
||
end_time timestamp,
|
||
audit_config jsonb,
|
||
naming_template varchar(256) NOT NULL DEFAULT '{姓名}-{证件号}-{材料名}',
|
||
compress_config jsonb,
|
||
roster_required smallint NOT NULL DEFAULT 1,
|
||
status smallint NOT NULL DEFAULT 0,
|
||
published_time timestamp,
|
||
create_by bigint,
|
||
create_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
update_by bigint,
|
||
update_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
deleted smallint DEFAULT 0 NOT NULL,
|
||
PRIMARY KEY (id),
|
||
CONSTRAINT uk_task_tenant_code UNIQUE (tenant_id, task_code)
|
||
);
|
||
CREATE INDEX idx_tenant_status ON collect_task (tenant_id, status);
|
||
CREATE INDEX idx_end_time ON collect_task (end_time);
|
||
COMMENT ON TABLE collect_task IS '收集任务';
|
||
COMMENT ON COLUMN collect_task.task_code IS '任务编码,用于文件路径';
|
||
COMMENT ON COLUMN collect_task.task_name IS '如 2026年9月机电专业认定材料收集';
|
||
COMMENT ON COLUMN collect_task.collect_mode IS '1学校收集 2社会收集';
|
||
COMMENT ON COLUMN collect_task.batch_no IS '批次号';
|
||
COMMENT ON COLUMN collect_task.major_name IS '专业';
|
||
COMMENT ON COLUMN collect_task.job_name IS '报考工种';
|
||
COMMENT ON COLUMN collect_task.job_level IS '报考等级';
|
||
COMMENT ON COLUMN collect_task.cert_type IS '1自主认定 2社会评价 3专项职业能力';
|
||
COMMENT ON COLUMN collect_task.end_time IS '截止时间';
|
||
COMMENT ON COLUMN collect_task.audit_config IS '审核流配置,见6.5';
|
||
COMMENT ON COLUMN collect_task.compress_config IS '任务级压缩默认参数';
|
||
COMMENT ON COLUMN collect_task.roster_required IS '是否需要名册: 固定为1,两种收集模式都必填(名单门禁)';
|
||
COMMENT ON COLUMN collect_task.status IS '0草稿 1收集中 2已截止 3已归档 4暂停';
|
||
|
||
CREATE TABLE collect_task_org (
|
||
id bigint GENERATED BY DEFAULT AS IDENTITY NOT NULL,
|
||
tenant_id bigint NOT NULL,
|
||
task_id bigint NOT NULL,
|
||
org_id bigint NOT NULL,
|
||
head_user_id bigint,
|
||
deputy_user_ids varchar(256),
|
||
expected_count integer,
|
||
status smallint NOT NULL DEFAULT 1,
|
||
create_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
PRIMARY KEY (id),
|
||
CONSTRAINT uk_task_org UNIQUE (task_id, org_id)
|
||
);
|
||
CREATE INDEX idx_tenant_head ON collect_task_org (tenant_id, head_user_id);
|
||
COMMENT ON TABLE collect_task_org IS '任务覆盖组织与审核人绑定';
|
||
COMMENT ON COLUMN collect_task_org.head_user_id IS '该班初审人(班主任),为空则取 sys_org.leader_user_id';
|
||
COMMENT ON COLUMN collect_task_org.deputy_user_ids IS '副班主任/协办人,逗号分隔';
|
||
COMMENT ON COLUMN collect_task_org.expected_count IS '应填人数(期望值,管理员填写,可空);仅在与名册实际人数不一致时告警。NULL 表示未设置,与 0(预期 0 人)含义不同';
|
||
COMMENT ON COLUMN collect_task_org.status IS '0已移除 1生效';
|
||
|
||
CREATE TABLE collect_form_schema (
|
||
id bigint GENERATED BY DEFAULT AS IDENTITY NOT NULL,
|
||
tenant_id bigint NOT NULL,
|
||
task_id bigint NOT NULL,
|
||
version integer NOT NULL DEFAULT 1,
|
||
schema_json jsonb NOT NULL,
|
||
is_current smallint NOT NULL DEFAULT 1,
|
||
create_by bigint, create_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
PRIMARY KEY (id)
|
||
);
|
||
CREATE INDEX idx_task_version ON collect_form_schema (task_id, version);
|
||
COMMENT ON TABLE collect_form_schema IS '表单定义(版本化)';
|
||
COMMENT ON COLUMN collect_form_schema.version IS '版本号,已发布任务修改则+1';
|
||
COMMENT ON COLUMN collect_form_schema.schema_json IS '字段定义数组';
|
||
|
||
CREATE TABLE collect_material_def (
|
||
id bigint GENERATED BY DEFAULT AS IDENTITY NOT NULL,
|
||
tenant_id bigint NOT NULL,
|
||
task_id bigint NOT NULL,
|
||
material_code varchar(32) NOT NULL,
|
||
material_name varchar(64) NOT NULL,
|
||
aliases varchar(512),
|
||
name_pattern varchar(256),
|
||
required smallint NOT NULL DEFAULT 1,
|
||
visible_when varchar(256),
|
||
file_types varchar(128) NOT NULL DEFAULT 'jpg,jpeg,png',
|
||
max_size_kb integer NOT NULL DEFAULT 1024,
|
||
compress_profile varchar(8) NOT NULL DEFAULT 'AUTO',
|
||
min_long_side integer,
|
||
min_quality smallint NOT NULL DEFAULT 70,
|
||
target_dpi integer,
|
||
fixed_size varchar(32),
|
||
output_format varchar(16),
|
||
max_count smallint NOT NULL DEFAULT 1,
|
||
merge_enabled smallint NOT NULL DEFAULT 0,
|
||
ocr_type varchar(32),
|
||
sample_url varchar(512),
|
||
tip_text varchar(512),
|
||
sort_no integer NOT NULL DEFAULT 0,
|
||
create_by bigint,
|
||
create_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
update_by bigint,
|
||
update_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
deleted smallint DEFAULT 0 NOT NULL,
|
||
PRIMARY KEY (id),
|
||
CONSTRAINT uk_task_code UNIQUE (task_id, material_code)
|
||
);
|
||
CREATE INDEX idx_matdef_tenant ON collect_material_def (tenant_id);
|
||
COMMENT ON TABLE collect_material_def IS '材料项定义';
|
||
COMMENT ON COLUMN collect_material_def.material_code IS '如 IDCARD/PHOTO1/SCHOOL_ROLL/WORK_PROOF';
|
||
COMMENT ON COLUMN collect_material_def.material_name IS '规范材料名,如 身份证';
|
||
COMMENT ON COLUMN collect_material_def.aliases IS '别名,逗号分隔,用于历史文件名识别';
|
||
COMMENT ON COLUMN collect_material_def.name_pattern IS '命名模板,空则继承任务级';
|
||
COMMENT ON COLUMN collect_material_def.visible_when IS '条件显示表达式';
|
||
COMMENT ON COLUMN collect_material_def.file_types IS '格式白名单';
|
||
COMMENT ON COLUMN collect_material_def.max_size_kb IS '体积上限KB';
|
||
COMMENT ON COLUMN collect_material_def.compress_profile IS '压缩档位: AUTO自动判定/P1证件照/P2证件翻拍/P3扫描件/P4截图/P5文档, 见6.3';
|
||
COMMENT ON COLUMN collect_material_def.min_long_side IS '分辨率下限(长边px),仅 P2 证件翻拍档生效;其余档位为 NULL 表示不适用';
|
||
COMMENT ON COLUMN collect_material_def.min_quality IS '质量下限,低于65视为不可接受';
|
||
COMMENT ON COLUMN collect_material_def.target_dpi IS '目标DPI(仅P3扫描件档生效,建议150-300)';
|
||
COMMENT ON COLUMN collect_material_def.fixed_size IS '固定像素尺寸(仅P1证件照档,如295x413)';
|
||
COMMENT ON COLUMN collect_material_def.output_format IS '输出格式;NULL 表示保持原格式。P4 截图档固定 png';
|
||
COMMENT ON COLUMN collect_material_def.max_count IS '允许上传份数,身份证正反面可2';
|
||
COMMENT ON COLUMN collect_material_def.merge_enabled IS '多份是否合并为一张';
|
||
COMMENT ON COLUMN collect_material_def.ocr_type IS 'IDCARD/GENERAL/NONE';
|
||
COMMENT ON COLUMN collect_material_def.sample_url IS '示例图';
|
||
COMMENT ON COLUMN collect_material_def.tip_text IS '填报提示语';
|
||
|
||
CREATE TABLE collect_roster (
|
||
id bigint GENERATED BY DEFAULT AS IDENTITY NOT NULL,
|
||
tenant_id bigint NOT NULL,
|
||
task_id bigint NOT NULL,
|
||
org_id bigint,
|
||
seq_no integer,
|
||
student_no varchar(64),
|
||
real_name varchar(64) NOT NULL,
|
||
name_pinyin varchar(128),
|
||
id_card_no varchar(128) NOT NULL,
|
||
id_card_hash char(64) NOT NULL,
|
||
id_card_mask varchar(32),
|
||
phone varchar(128),
|
||
phone_hash char(64),
|
||
ext_json jsonb,
|
||
bind_user_id bigint,
|
||
bind_time timestamp,
|
||
bind_type smallint,
|
||
submit_status smallint NOT NULL DEFAULT 0,
|
||
create_by bigint,
|
||
create_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
update_by bigint,
|
||
update_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
deleted smallint DEFAULT 0 NOT NULL,
|
||
PRIMARY KEY (id),
|
||
CONSTRAINT uk_task_idcard UNIQUE (task_id, id_card_hash)
|
||
);
|
||
CREATE INDEX idx_task_org ON collect_roster (task_id, org_id);
|
||
CREATE INDEX idx_bind_user ON collect_roster (bind_user_id);
|
||
CREATE INDEX idx_roster_name ON collect_roster (task_id, real_name);
|
||
COMMENT ON TABLE collect_roster IS '名册';
|
||
COMMENT ON COLUMN collect_roster.org_id IS '班级';
|
||
COMMENT ON COLUMN collect_roster.seq_no IS '序号(来自Excel)';
|
||
COMMENT ON COLUMN collect_roster.student_no IS '学号(⚠️初稿遗漏,名册唯一性依据之一)';
|
||
COMMENT ON COLUMN collect_roster.name_pinyin IS '拼音,用于近音纠错';
|
||
COMMENT ON COLUMN collect_roster.id_card_no IS 'AES加密';
|
||
COMMENT ON COLUMN collect_roster.id_card_hash IS 'HMAC,唯一性判断';
|
||
COMMENT ON COLUMN collect_roster.id_card_mask IS '脱敏展示 3601**********1234';
|
||
COMMENT ON COLUMN collect_roster.ext_json IS '名册其余字段(学历/工作单位/报考职业等)';
|
||
COMMENT ON COLUMN collect_roster.bind_user_id IS '绑定的填报人';
|
||
COMMENT ON COLUMN collect_roster.bind_type IS '1门禁核验匹配 2管理员指定';
|
||
COMMENT ON COLUMN collect_roster.submit_status IS '0未提交 1草稿 2待审 3已通过 4已驳回';
|
||
|
||
CREATE TABLE collect_submission (
|
||
id bigint GENERATED BY DEFAULT AS IDENTITY NOT NULL,
|
||
tenant_id bigint NOT NULL,
|
||
task_id bigint NOT NULL,
|
||
roster_id bigint,
|
||
org_id bigint,
|
||
submitter_id bigint NOT NULL,
|
||
form_version integer NOT NULL DEFAULT 1,
|
||
form_data jsonb,
|
||
-- ⚠️ 与 collect_roster 对称的 18 个业务列：存学生「确认/修改后」的最终值
|
||
-- 需要与名单值做 diff（留痕"学生改了哪些字段"），也是统计"实际报考各工种人数"的依据
|
||
occupation varchar(64),
|
||
worktype varchar(64),
|
||
job_level varchar(32),
|
||
education varchar(32),
|
||
is_student smallint,
|
||
school varchar(128),
|
||
major varchar(128),
|
||
enroll_date date,
|
||
work_unit varchar(128),
|
||
is_retake smallint,
|
||
retake_theory varchar(64),
|
||
retake_practice varchar(64),
|
||
retake_overall varchar(64),
|
||
work_years smallint,
|
||
cert_occupation varchar(128),
|
||
cert_level varchar(32),
|
||
cert_no varchar(64),
|
||
extras_json jsonb DEFAULT '{}'::jsonb, -- 逃生舱
|
||
changed_fields jsonb, -- 学生相对名单修改过的字段(留痕)
|
||
status smallint NOT NULL DEFAULT 0,
|
||
reject_count integer NOT NULL DEFAULT 0,
|
||
last_reject_reason varchar(512),
|
||
red_count integer NOT NULL DEFAULT 0,
|
||
yellow_count integer NOT NULL DEFAULT 0,
|
||
submit_time timestamp,
|
||
class_audit_by bigint, class_audit_time timestamp,
|
||
admin_audit_by bigint, admin_audit_time timestamp,
|
||
create_by bigint,
|
||
create_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
update_by bigint,
|
||
update_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
deleted smallint DEFAULT 0 NOT NULL,
|
||
PRIMARY KEY (id),
|
||
CONSTRAINT uk_task_submitter UNIQUE (task_id, submitter_id)
|
||
);
|
||
CREATE INDEX idx_task_status ON collect_submission (task_id, status, org_id);
|
||
CREATE INDEX idx_submitter ON collect_submission (submitter_id);
|
||
COMMENT ON TABLE collect_submission IS '填报单';
|
||
COMMENT ON COLUMN collect_submission.roster_id IS '社会收集可为空';
|
||
COMMENT ON COLUMN collect_submission.submitter_id IS '填报人user_id';
|
||
COMMENT ON COLUMN collect_submission.form_data IS '表单数据快照(扩展字段;核心18列见上方独立列)';
|
||
COMMENT ON COLUMN collect_submission.occupation IS '最终报考职业(学生确认后,应与名单一致)';
|
||
COMMENT ON COLUMN collect_submission.worktype IS '最终报考工种';
|
||
COMMENT ON COLUMN collect_submission.job_level IS '最终报考等级';
|
||
COMMENT ON COLUMN collect_submission.is_student IS '是否在校生 0否 1是';
|
||
COMMENT ON COLUMN collect_submission.work_unit IS '工作单位(没有填"无")';
|
||
COMMENT ON COLUMN collect_submission.is_retake IS '是否补考 0否 1是';
|
||
COMMENT ON COLUMN collect_submission.extras_json IS '租户/机构额外追加字段(逃生舱)';
|
||
COMMENT ON COLUMN collect_submission.changed_fields IS '学生相对名单修改过的字段(留痕: {字段:{old,new,time}})';
|
||
COMMENT ON COLUMN collect_submission.status IS '0草稿 1待初审 2待终审 3已通过 4已驳回 5已归档';
|
||
COMMENT ON COLUMN collect_submission.red_count IS '红色问题数';
|
||
COMMENT ON COLUMN collect_submission.yellow_count IS '黄色问题数';
|
||
|
||
CREATE TABLE collect_file (
|
||
id bigint GENERATED BY DEFAULT AS IDENTITY NOT NULL,
|
||
tenant_id bigint NOT NULL,
|
||
task_id bigint NOT NULL,
|
||
submission_id bigint NOT NULL,
|
||
roster_id bigint,
|
||
material_code varchar(32) NOT NULL,
|
||
version integer NOT NULL DEFAULT 1,
|
||
is_current smallint NOT NULL DEFAULT 1,
|
||
original_name varchar(512) NOT NULL,
|
||
standard_name varchar(512),
|
||
file_ext varchar(16) NOT NULL,
|
||
mime_type varchar(64),
|
||
raw_size bigint NOT NULL,
|
||
final_size bigint NOT NULL,
|
||
file_hash char(64) NOT NULL,
|
||
raw_path varchar(512),
|
||
std_path varchar(512),
|
||
raw_expire_time timestamp,
|
||
width integer, height integer, quality smallint,
|
||
dpi integer,
|
||
ocr_status smallint NOT NULL DEFAULT 0,
|
||
ocr_result jsonb,
|
||
ocr_confidence numeric(4,3),
|
||
detected_material_code varchar(32),
|
||
detect_confidence numeric(4,3),
|
||
detect_source varchar(32),
|
||
detect_conflict smallint NOT NULL DEFAULT 0,
|
||
need_manual_place smallint NOT NULL DEFAULT 0,
|
||
vlm_called smallint NOT NULL DEFAULT 0,
|
||
validate_status smallint NOT NULL DEFAULT 0,
|
||
source smallint NOT NULL DEFAULT 1,
|
||
upload_ip varchar(64),
|
||
create_by bigint,
|
||
create_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
update_by bigint,
|
||
update_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
deleted smallint DEFAULT 0 NOT NULL,
|
||
PRIMARY KEY (id)
|
||
);
|
||
CREATE INDEX idx_file_submission ON collect_file (submission_id, material_code, is_current);
|
||
CREATE INDEX idx_task_material ON collect_file (task_id, material_code);
|
||
CREATE INDEX idx_hash ON collect_file (file_hash);
|
||
CREATE INDEX idx_raw_expire ON collect_file (raw_expire_time);
|
||
COMMENT ON TABLE collect_file IS '文件实体';
|
||
COMMENT ON COLUMN collect_file.is_current IS '是否最新有效版本';
|
||
COMMENT ON COLUMN collect_file.original_name IS '原始文件名';
|
||
COMMENT ON COLUMN collect_file.standard_name IS '规范文件名';
|
||
COMMENT ON COLUMN collect_file.raw_size IS '原始字节';
|
||
COMMENT ON COLUMN collect_file.final_size IS '处理后字节';
|
||
COMMENT ON COLUMN collect_file.file_hash IS 'SHA-256,用于查重';
|
||
COMMENT ON COLUMN collect_file.raw_path IS '对象存储key-原始';
|
||
COMMENT ON COLUMN collect_file.std_path IS '对象存储key-规范';
|
||
COMMENT ON COLUMN collect_file.raw_expire_time IS '原始文件清理时间';
|
||
COMMENT ON COLUMN collect_file.ocr_status IS '0未识别 1成功 2失败';
|
||
COMMENT ON COLUMN collect_file.ocr_result IS 'OCR结构化结果(不存明文证件号,存hash)';
|
||
COMMENT ON COLUMN collect_file.detected_material_code IS '材料识别结果(材料类型编码),见功能逻辑卷3.5-B';
|
||
COMMENT ON COLUMN collect_file.detect_confidence IS '材料识别置信度';
|
||
COMMENT ON COLUMN collect_file.detect_source IS '识别来源: FILENAME/形态KEYWORD/POSITION/VLM/MIXED';
|
||
COMMENT ON COLUMN collect_file.detect_conflict IS '是否与槽位冲突(1=冲突)';
|
||
COMMENT ON COLUMN collect_file.need_manual_place IS '是否需要人工归属(散图分拣用)';
|
||
COMMENT ON COLUMN collect_file.vlm_called IS '是否触发了VLM兜底(用于成本核算)';
|
||
COMMENT ON COLUMN collect_file.validate_status IS '0未校验 1通过 2警告 3错误';
|
||
COMMENT ON COLUMN collect_file.source IS '1本人上传 2班主任代传 3管理员代传 4整理器导入';
|
||
|
||
CREATE TABLE collect_validation_issue (
|
||
id bigint GENERATED BY DEFAULT AS IDENTITY NOT NULL,
|
||
tenant_id bigint NOT NULL,
|
||
task_id bigint NOT NULL,
|
||
submission_id bigint NOT NULL,
|
||
file_id bigint,
|
||
rule_code varchar(32) NOT NULL,
|
||
severity smallint NOT NULL,
|
||
field_key varchar(64),
|
||
title varchar(128) NOT NULL,
|
||
detail varchar(512),
|
||
expected_val varchar(256),
|
||
actual_val varchar(256),
|
||
auto_fixed smallint NOT NULL DEFAULT 0,
|
||
resolved smallint NOT NULL DEFAULT 0,
|
||
resolved_by bigint, resolved_time timestamp,
|
||
create_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
PRIMARY KEY (id)
|
||
);
|
||
CREATE INDEX idx_issue_submission ON collect_validation_issue (submission_id, resolved);
|
||
CREATE INDEX idx_task_severity ON collect_validation_issue (task_id, severity, resolved);
|
||
COMMENT ON TABLE collect_validation_issue IS '校验问题';
|
||
COMMENT ON COLUMN collect_validation_issue.file_id IS '可为空(表单级问题)';
|
||
COMMENT ON COLUMN collect_validation_issue.rule_code IS 'VAL-01..VAL-12';
|
||
COMMENT ON COLUMN collect_validation_issue.severity IS '1信息 2警告 3错误';
|
||
COMMENT ON COLUMN collect_validation_issue.expected_val IS '期望值(如名册中的证件号)';
|
||
COMMENT ON COLUMN collect_validation_issue.actual_val IS '实际值';
|
||
COMMENT ON COLUMN collect_validation_issue.auto_fixed IS '是否已自动修复';
|
||
|
||
CREATE TABLE collect_audit_record (
|
||
id bigint GENERATED BY DEFAULT AS IDENTITY NOT NULL,
|
||
tenant_id bigint NOT NULL,
|
||
task_id bigint NOT NULL,
|
||
submission_id bigint NOT NULL,
|
||
audit_node varchar(32) NOT NULL,
|
||
auditor_id bigint NOT NULL,
|
||
action smallint NOT NULL,
|
||
reason_code varchar(32),
|
||
reason_text varchar(512),
|
||
material_codes varchar(256),
|
||
duration_sec integer,
|
||
create_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
PRIMARY KEY (id)
|
||
);
|
||
CREATE INDEX idx_audit_submission ON collect_audit_record (submission_id, create_time);
|
||
CREATE INDEX idx_task_node ON collect_audit_record (task_id, audit_node, create_time);
|
||
COMMENT ON TABLE collect_audit_record IS '审核记录';
|
||
COMMENT ON COLUMN collect_audit_record.audit_node IS 'CLASS_AUDIT/ADMIN_AUDIT';
|
||
COMMENT ON COLUMN collect_audit_record.action IS '1通过 2驳回 3批量通过 4批量驳回 5转交 6代传';
|
||
COMMENT ON COLUMN collect_audit_record.reason_code IS '驳回原因编码';
|
||
COMMENT ON COLUMN collect_audit_record.material_codes IS '被驳回的材料项';
|
||
COMMENT ON COLUMN collect_audit_record.duration_sec IS '处理耗时';
|
||
|
||
CREATE TABLE collect_export_task (
|
||
id bigint GENERATED BY DEFAULT AS IDENTITY NOT NULL,
|
||
tenant_id bigint NOT NULL,
|
||
task_id bigint NOT NULL,
|
||
export_name varchar(256) NOT NULL,
|
||
scope_json jsonb,
|
||
split_size_mb integer NOT NULL DEFAULT 0,
|
||
desensitize smallint NOT NULL DEFAULT 0,
|
||
status smallint NOT NULL DEFAULT 0,
|
||
progress smallint NOT NULL DEFAULT 0,
|
||
file_count integer, total_size bigint,
|
||
result_files jsonb,
|
||
error_msg varchar(512),
|
||
expire_time timestamp,
|
||
create_by bigint, create_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
finish_time timestamp,
|
||
PRIMARY KEY (id)
|
||
);
|
||
CREATE INDEX idx_task ON collect_export_task (task_id, create_time);
|
||
CREATE INDEX idx_status ON collect_export_task (status);
|
||
COMMENT ON TABLE collect_export_task IS '导出任务';
|
||
COMMENT ON COLUMN collect_export_task.scope_json IS '范围:orgIds/userIds/仅通过终审/是否含原图';
|
||
COMMENT ON COLUMN collect_export_task.split_size_mb IS '0不分卷,>0按MB分卷';
|
||
COMMENT ON COLUMN collect_export_task.desensitize IS '是否脱敏';
|
||
COMMENT ON COLUMN collect_export_task.status IS '0排队 1处理中 2完成 3失败';
|
||
COMMENT ON COLUMN collect_export_task.progress IS '0-100';
|
||
COMMENT ON COLUMN collect_export_task.result_files IS '生成的包列表[{name,path,size,part}]';
|
||
COMMENT ON COLUMN collect_export_task.expire_time IS '下载有效期';
|
||
|
||
CREATE TABLE collect_file_process_log (
|
||
id bigint GENERATED BY DEFAULT AS IDENTITY NOT NULL,
|
||
tenant_id bigint NOT NULL,
|
||
file_id bigint NOT NULL,
|
||
step varchar(32) NOT NULL,
|
||
before_val varchar(512),
|
||
after_val varchar(512),
|
||
extra_json jsonb,
|
||
cost_ms integer,
|
||
success smallint NOT NULL DEFAULT 1,
|
||
error_msg varchar(512),
|
||
create_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
PRIMARY KEY (id)
|
||
);
|
||
CREATE INDEX idx_file ON collect_file_process_log (file_id, step);
|
||
COMMENT ON TABLE collect_file_process_log IS '文件处理日志';
|
||
COMMENT ON COLUMN collect_file_process_log.step IS 'NORMALIZE_NAME/COMPRESS/CONVERT/OCR/EXIF_FIX';
|
||
COMMENT ON COLUMN collect_file_process_log.extra_json IS '如 {beforeSize,afterSize,attempts,quality,finalLongSide}';
|
||
|
||
CREATE TABLE collect_notify_record (
|
||
id bigint GENERATED BY DEFAULT AS IDENTITY NOT NULL,
|
||
tenant_id bigint NOT NULL,
|
||
task_id bigint,
|
||
channel varchar(16) NOT NULL, -- WECHAT_SUBSCRIBE / WECHAT_MP / SMS / INBOX
|
||
biz_type varchar(32) NOT NULL, -- TASK_PUBLISH / DEADLINE_REMIND / REJECT / APPROVE / EXPORT_DONE / AUDIT_TIMEOUT
|
||
receiver_id bigint NOT NULL, -- 接收人 sys_user.id
|
||
receiver_role varchar(16), -- 接收人角色(便于按角色统计)
|
||
template_code varchar(64),
|
||
content varchar(1024),
|
||
status smallint NOT NULL DEFAULT 0, -- 0待发 1成功 2失败 3额度不足跳过
|
||
fail_reason varchar(512),
|
||
send_time timestamp,
|
||
create_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
PRIMARY KEY (id)
|
||
);
|
||
CREATE INDEX idx_notify_task ON collect_notify_record (task_id, biz_type, create_time);
|
||
CREATE INDEX idx_notify_recv ON collect_notify_record (receiver_id, create_time);
|
||
COMMENT ON TABLE collect_notify_record IS '通知记录';
|
||
COMMENT ON COLUMN collect_notify_record.channel IS '渠道: 微信订阅消息/服务号/短信/站内';
|
||
COMMENT ON COLUMN collect_notify_record.biz_type IS '业务类型';
|
||
COMMENT ON COLUMN collect_notify_record.status IS '0待发 1成功 2失败 3额度不足跳过';
|
||
COMMENT ON COLUMN collect_notify_record.fail_reason IS '失败原因;额度不足时记录(应对R12订阅消息次数限制)';
|
||
|
||
-- 订阅额度余额（微信一次性订阅消息，发一条消耗一条）
|
||
CREATE TABLE collect_subscribe_quota (
|
||
id bigint GENERATED BY DEFAULT AS IDENTITY NOT NULL,
|
||
tenant_id bigint NOT NULL,
|
||
user_id bigint NOT NULL,
|
||
template_code varchar(64) NOT NULL,
|
||
total integer NOT NULL DEFAULT 0, -- 累计授权次数
|
||
used integer NOT NULL DEFAULT 0, -- 已消耗次数
|
||
update_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
PRIMARY KEY (id),
|
||
CONSTRAINT uk_quota_user_tpl UNIQUE (user_id, template_code)
|
||
);
|
||
COMMENT ON TABLE collect_subscribe_quota IS '微信订阅消息额度余额';
|
||
COMMENT ON COLUMN collect_subscribe_quota.total IS '累计授权次数';
|
||
COMMENT ON COLUMN collect_subscribe_quota.used IS '已消耗次数;余额=total-used,不足时自动降级到服务号或短信';
|
||
|
||
CREATE TABLE collect_rule_template (
|
||
id bigint GENERATED BY DEFAULT AS IDENTITY NOT NULL,
|
||
tenant_id bigint NOT NULL,
|
||
template_name varchar(128) NOT NULL,
|
||
cert_type smallint,
|
||
form_schema jsonb,
|
||
materials jsonb,
|
||
naming_template varchar(256),
|
||
is_public smallint NOT NULL DEFAULT 0,
|
||
use_count integer NOT NULL DEFAULT 0,
|
||
create_by bigint,
|
||
create_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
update_by bigint,
|
||
update_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
deleted smallint DEFAULT 0 NOT NULL,
|
||
PRIMARY KEY (id)
|
||
);
|
||
CREATE INDEX idx_ruletpl_tenant ON collect_rule_template (tenant_id, is_public);
|
||
COMMENT ON TABLE collect_rule_template IS '规则模板';
|
||
COMMENT ON COLUMN collect_rule_template.tenant_id IS '0=平台公共模板';
|
||
COMMENT ON COLUMN collect_rule_template.template_name IS '如 自主认定标准模板';
|
||
COMMENT ON COLUMN collect_rule_template.cert_type IS '1自主认定 2社会评价 3专项职业能力';
|
||
COMMENT ON COLUMN collect_rule_template.form_schema IS '表单字段模板';
|
||
COMMENT ON COLUMN collect_rule_template.materials IS '材料清单模板';
|
||
|
||
CREATE TABLE collect_cleanup_job (
|
||
id bigint GENERATED BY DEFAULT AS IDENTITY NOT NULL,
|
||
tenant_id bigint NOT NULL,
|
||
job_name varchar(128) NOT NULL,
|
||
source_type smallint NOT NULL DEFAULT 1,
|
||
source_path varchar(512) NOT NULL,
|
||
target_task_id bigint,
|
||
naming_template varchar(256) NOT NULL DEFAULT '{姓名}-{证件号}-{材料名}',
|
||
compress_config jsonb,
|
||
status smallint NOT NULL DEFAULT 0,
|
||
progress smallint NOT NULL DEFAULT 0,
|
||
report_json jsonb,
|
||
total_files integer, fixed_files integer, failed_files integer,
|
||
result_path varchar(512),
|
||
create_by bigint, create_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
finish_time timestamp,
|
||
PRIMARY KEY (id)
|
||
);
|
||
CREATE INDEX idx_cleanup_tenant ON collect_cleanup_job (tenant_id, create_time);
|
||
COMMENT ON TABLE collect_cleanup_job IS '历史数据整理任务';
|
||
COMMENT ON COLUMN collect_cleanup_job.source_type IS '1上传压缩包 2对象存储目录';
|
||
COMMENT ON COLUMN collect_cleanup_job.target_task_id IS '结果可导入某任务';
|
||
COMMENT ON COLUMN collect_cleanup_job.status IS '0排队 1解析中 2处理中 3完成 4失败';
|
||
COMMENT ON COLUMN collect_cleanup_job.report_json IS '体检报告:命名违规/证件号异常/体积超标/缺失/重复';
|
||
|
||
|
||
-- ============ 1b) 客户确认后新增（FR-509） ============
|
||
|
||
CREATE TABLE collect_join_request (
|
||
id bigint GENERATED BY DEFAULT AS IDENTITY NOT NULL,
|
||
tenant_id bigint NOT NULL,
|
||
task_id bigint NOT NULL,
|
||
real_name varchar(64) NOT NULL,
|
||
id_card_hash char(64) NOT NULL,
|
||
id_card_mask varchar(32) NOT NULL,
|
||
phone_hash char(64),
|
||
phone_mask varchar(32),
|
||
org_hint varchar(128),
|
||
reason varchar(500),
|
||
status smallint NOT NULL DEFAULT 0,
|
||
reject_reason varchar(500),
|
||
roster_id bigint,
|
||
audit_by bigint,
|
||
audit_time timestamp,
|
||
applicant_openid varchar(64) NOT NULL,
|
||
applicant_uid bigint,
|
||
source smallint NOT NULL DEFAULT 1,
|
||
create_by bigint,
|
||
create_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
update_by bigint,
|
||
update_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
deleted smallint DEFAULT 0 NOT NULL,
|
||
PRIMARY KEY (id)
|
||
);
|
||
CREATE UNIQUE INDEX uk_join_task_idcard ON collect_join_request (task_id, id_card_hash)
|
||
WHERE deleted = 0 AND status IN (0, 1);
|
||
CREATE INDEX idx_join_task_status ON collect_join_request (task_id, status, create_time);
|
||
CREATE INDEX idx_join_openid ON collect_join_request (applicant_openid);
|
||
COMMENT ON TABLE collect_join_request IS '名单加入申请';
|
||
COMMENT ON COLUMN collect_join_request.real_name IS '申请人自述姓名(明文,业务必需)';
|
||
COMMENT ON COLUMN collect_join_request.id_card_hash IS 'HMAC,与门禁一致,不存明文';
|
||
COMMENT ON COLUMN collect_join_request.id_card_mask IS '脱敏展示';
|
||
COMMENT ON COLUMN collect_join_request.org_hint IS '申请人自述班级(选填,可由管理员修正)';
|
||
COMMENT ON COLUMN collect_join_request.reason IS '申请说明';
|
||
COMMENT ON COLUMN collect_join_request.status IS '0待审 1已通过 2已驳回 3已失效(任务截止)';
|
||
COMMENT ON COLUMN collect_join_request.reject_reason IS '驳回原因(驳回时必填)';
|
||
COMMENT ON COLUMN collect_join_request.roster_id IS '审批通过后生成的名册项';
|
||
COMMENT ON COLUMN collect_join_request.applicant_openid IS '用于限流(每任务<=2次)与审批通过后自动绑定';
|
||
COMMENT ON COLUMN collect_join_request.source IS '1门禁失败页申请 2管理员代录';
|
||
|
||
-- ============ 2) 补列 ALTER ============
|
||
|
||
ALTER TABLE collect_task ADD COLUMN plan_scope_json jsonb;
|
||
ALTER TABLE collect_task ADD COLUMN plan_scope_confirmed smallint NOT NULL DEFAULT 0;
|
||
COMMENT ON COLUMN collect_task.plan_scope_json IS '本期报考范围快照(从名单聚合): {occupations:[{name,worktypes:[{name,levels:[]}]}], confirmed:true}';
|
||
COMMENT ON COLUMN collect_task.plan_scope_confirmed IS '管理员是否已确认范围(未确认则追加名单不告警)';
|
||
|
||
-- 同学号的补齐：官方模板 21 字段中的非身份字段全部建为正式列
|
||
-- 姓名、身份证号已有独立列（real_name / id_card_no），此处补齐其余 18 个
|
||
ALTER TABLE collect_roster
|
||
ADD COLUMN occupation varchar(64), -- 报考职业 G
|
||
ADD COLUMN worktype varchar(64), -- 报考工种 H
|
||
ADD COLUMN job_level varchar(32), -- 报考等级 I
|
||
ADD COLUMN education varchar(32), -- 学历 D
|
||
ADD COLUMN is_student smallint, -- 是否在校生 N 0否 1是
|
||
ADD COLUMN school varchar(128), -- 在读学校 O
|
||
ADD COLUMN major varchar(128), -- 所学专业 P
|
||
ADD COLUMN enroll_date date, -- 入学时间 Q YYYY-MM-DD
|
||
ADD COLUMN work_unit varchar(128), -- 工作单位 F 无则填"无"
|
||
ADD COLUMN is_retake smallint, -- 是否补考 J 0否 1是
|
||
ADD COLUMN retake_theory varchar(64), -- 补考理论 K
|
||
ADD COLUMN retake_practice varchar(64), -- 补考实操 L
|
||
ADD COLUMN retake_overall varchar(64), -- 补考综合 M
|
||
ADD COLUMN work_years smallint, -- 本职业工作年限 R
|
||
ADD COLUMN cert_occupation varchar(128), -- 已获证书职业工种 S
|
||
ADD COLUMN cert_level varchar(32), -- 已获证书等级 T
|
||
ADD COLUMN cert_no varchar(64), -- 已获证书编号 U
|
||
ADD COLUMN extras_json jsonb DEFAULT '{}'::jsonb; -- 逃生舱：仅存租户/机构额外追加的字段
|
||
|
||
-- 报考范围聚合与按工种统计必需的索引
|
||
CREATE INDEX idx_roster_scope ON collect_roster (tenant_id, task_id, occupation, worktype, job_level);
|
||
CREATE INDEX idx_roster_org ON collect_roster (task_id, org_id, deleted);
|
||
|
||
COMMENT ON COLUMN collect_roster.occupation IS '报考职业(锁定,由老师确定)';
|
||
COMMENT ON COLUMN collect_roster.worktype IS '报考工种(锁定;该职业无工种时填"无";专项职业能力为空)';
|
||
COMMENT ON COLUMN collect_roster.job_level IS '报考等级(锁定;专项职业能力为空)';
|
||
COMMENT ON COLUMN collect_roster.education IS '学历(下拉11项)';
|
||
COMMENT ON COLUMN collect_roster.is_student IS '是否在校生 0否 1是';
|
||
COMMENT ON COLUMN collect_roster.school IS '在读学校(在校生=是时必填)';
|
||
COMMENT ON COLUMN collect_roster.major IS '所学专业(在校生=是时必填)';
|
||
COMMENT ON COLUMN collect_roster.enroll_date IS '入学时间(在校生=是时必填,格式YYYY-MM-DD)';
|
||
COMMENT ON COLUMN collect_roster.work_unit IS '工作单位(没有填"无")';
|
||
COMMENT ON COLUMN collect_roster.is_retake IS '是否补考 0否 1是';
|
||
COMMENT ON COLUMN collect_roster.retake_theory IS '补考理论(级联)';
|
||
COMMENT ON COLUMN collect_roster.retake_practice IS '补考实操(级联)';
|
||
COMMENT ON COLUMN collect_roster.retake_overall IS '补考综合(级联)';
|
||
COMMENT ON COLUMN collect_roster.work_years IS '本职业工作年限(仅阿拉伯数字)';
|
||
COMMENT ON COLUMN collect_roster.cert_occupation IS '已获证书职业工种(选填)';
|
||
COMMENT ON COLUMN collect_roster.cert_level IS '已获证书等级(选填)';
|
||
COMMENT ON COLUMN collect_roster.cert_no IS '已获证书编号(选填)';
|
||
COMMENT ON COLUMN collect_roster.extras_json IS '租户/机构额外追加的字段(逃生舱,不参与核心统计)';
|