下载中心/建表设计稿

建表设计稿

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