建表设计稿
下载原文件-- P26 供应商准入、稽核、年审与星级评级 建表设计稿(PostgreSQL 口径)
-- 说明:由 MySQL 设计稿改写为 PostgreSQL,仅作设计参考,不直接执行。
-- 统一约定:金额 numeric(14,4)、状态 text + check、软删除 is_deleted、可变表保留本地 updated_at 触发器。
CREATE TABLE supplier_profile (
id bigserial PRIMARY KEY,
supplier_id text NOT NULL UNIQUE,
company_name text,
factory_address text,
contact_person text,
phone text,
supplier_level text NOT NULL DEFAULT 'L1' CHECK (supplier_level IN ('L1','L2')),
star_level smallint NOT NULL DEFAULT 0 CHECK (star_level BETWEEN 0 AND 5),
status smallint NOT NULL DEFAULT 0 CHECK (status IN (0,1,2,3)),
process_tag jsonb NOT NULL DEFAULT '[]'::jsonb,
cert_json jsonb NOT NULL DEFAULT '[]'::jsonb,
enter_at timestamptz,
last_audit_at timestamptz,
next_audit_at timestamptz,
remark 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 supplier_profile IS '供应商主档案';
COMMENT ON COLUMN supplier_profile.supplier_level IS '准入等级:L1 线上准入 / L2 现场稽核';
COMMENT ON COLUMN supplier_profile.star_level IS '当前星级 0-5';
COMMENT ON COLUMN supplier_profile.status IS '0 待审核 1 正常接单 2 暂停接单 3 黑名单';
COMMENT ON COLUMN supplier_profile.process_tag IS '支持工艺标签 JSON 数组';
COMMENT ON COLUMN supplier_profile.cert_json IS '资质证照列表 JSON';
CREATE INDEX idx_supplier_profile_level ON supplier_profile (supplier_level);
CREATE INDEX idx_supplier_profile_status ON supplier_profile (status);
CREATE TABLE supplier_audit_task (
id bigserial PRIMARY KEY,
supplier_id text NOT NULL,
audit_type text NOT NULL DEFAULT 'online' CHECK (audit_type IN ('online','site','annual','special')),
audit_status smallint NOT NULL DEFAULT 0 CHECK (audit_status IN (0,1,2,3,4)),
audit_report_url text,
audit_score numeric(5,2),
auditor_id text,
start_at timestamptz,
due_fix_at timestamptz,
is_deleted boolean NOT NULL DEFAULT false,
created_at timestamptz NOT NULL DEFAULT now(),
updated_at timestamptz NOT NULL DEFAULT now()
);
COMMENT ON TABLE supplier_audit_task IS '稽核任务(线上/现场/年审/专项)';
COMMENT ON COLUMN supplier_audit_task.audit_status IS '0 待启动 1 稽核中 2 待整改 3 复查中 4 关闭';
CREATE INDEX idx_supplier_audit_task_supplier ON supplier_audit_task (supplier_id);
CREATE INDEX idx_supplier_audit_task_status ON supplier_audit_task (audit_status);
CREATE TABLE supplier_audit_nc (
id bigserial PRIMARY KEY,
audit_id bigint NOT NULL REFERENCES supplier_audit_task (id) ON DELETE CASCADE,
nc_code text,
nc_level text NOT NULL DEFAULT 'minor' CHECK (nc_level IN ('major','minor','observation')),
nc_title text,
nc_desc text,
correct_plan text,
proof_json jsonb NOT NULL DEFAULT '[]'::jsonb,
review_result smallint NOT NULL DEFAULT 0 CHECK (review_result IN (0,1,2)),
is_deleted boolean NOT NULL DEFAULT false,
created_at timestamptz NOT NULL DEFAULT now(),
updated_at timestamptz NOT NULL DEFAULT now()
);
COMMENT ON TABLE supplier_audit_nc IS '稽核不符合项与整改证据';
COMMENT ON COLUMN supplier_audit_nc.nc_level IS 'major 严重 / minor 一般 / observation 观察项';
COMMENT ON COLUMN supplier_audit_nc.review_result IS '0 待复查 1 通过关闭 2 退回整改';
CREATE INDEX idx_supplier_audit_nc_audit ON supplier_audit_nc (audit_id);
CREATE TABLE supplier_star_log (
id bigserial PRIMARY KEY,
supplier_id text NOT NULL,
star_before smallint,
star_after smallint,
reason text,
audit_id bigint,
operator_id text,
created_at timestamptz NOT NULL DEFAULT now()
);
COMMENT ON TABLE supplier_star_log IS '供应商评级历史快照';
CREATE INDEX idx_supplier_star_log_supplier ON supplier_star_log (supplier_id);
-- 本地更新时间触发器(每张可变表各一个)
CREATE OR REPLACE FUNCTION set_supplier_updated_at()
RETURNS trigger AS $$
BEGIN
NEW.updated_at = now();
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER trg_supplier_profile_updated
BEFORE UPDATE ON supplier_profile
FOR EACH ROW EXECUTE FUNCTION set_supplier_updated_at();
CREATE TRIGGER trg_supplier_audit_task_updated
BEFORE UPDATE ON supplier_audit_task
FOR EACH ROW EXECUTE FUNCTION set_supplier_updated_at();
CREATE TRIGGER trg_supplier_audit_nc_updated
BEFORE UPDATE ON supplier_audit_nc
FOR EACH ROW EXECUTE FUNCTION set_supplier_updated_at();
