下载中心/建表设计稿

建表设计稿

下载原文件
-- 样品申请与追踪 · 建表设计稿 (P24)
-- 说明:本文件为方案设计参考,不直接执行。正式落地需按平台数据库规范,
-- 由平台数据库工具生成迁移,并补齐中文注释、索引、更新时间触发器与行级权限。

-- 1. 样品任务
create table if not exists sample_task (
  id                  text primary key,
  product_id          text,
  supplier_id         text,
  buyer_id            text,
  layers              int         not null check (layers between 1 and 32),
  material            text        not null,
  board_thickness     numeric(5,2),
  copper_weight       numeric(4,1),
  size_text           text,
  quantity            int         not null check (quantity between 1 and 200),
  surface_finish      text,
  impedance           text,
  expected_date       date,
  unit_price          numeric(10,2),
  lead_time_days      int,
  ship_date           date,
  receiver_name       text,
  receiver_phone      text,
  receiver_address    text,
  remark              text,
  status              text        not null default 'draft',
  accepted_at         timestamptz,
  closed_at           timestamptz,
  is_deleted          boolean     not null default false,
  created_at          timestamptz not null default now(),
  updated_at          timestamptz not null default now()
);

create index if not exists idx_sample_task_supplier on sample_task (supplier_id, status);
create index if not exists idx_sample_task_buyer on sample_task (buyer_id, status);

-- 2. 流程日志(只追加)
create table if not exists sample_flow_log (
  id                  bigserial primary key,
  task_id             text        not null,
  node                text        not null,
  operator_role       text        not null,
  operator_name       text,
  note                text,
  attachment_url      text,
  created_at          timestamptz not null default now()
);

create index if not exists idx_sample_flow_log_task on sample_flow_log (task_id, created_at);

-- 3. 样品配额
create table if not exists sample_quota (
  id                  bigserial primary key,
  supplier_id         text        not null,
  period_start        date        not null,
  period_end          date        not null,
  total_quota         int         not null default 0,
  used_quota          int         not null default 0,
  frozen_quota        int         not null default 0,
  reset_at            timestamptz,
  created_at          timestamptz not null default now(),
  updated_at          timestamptz not null default now()
);

create unique index if not exists uq_sample_quota_supplier_period
  on sample_quota (supplier_id, period_start);

-- 4. 瑕疵记录
create table if not exists sample_defect (
  id                  bigserial primary key,
  task_id             text        not null,
  defect_type         text        not null,
  location            text,
  severity            text        not null default 'medium',
  image_urls          text[],
  expectation         text,
  description         text,
  supplier_action     text,
  supplier_note       text,
  responded_at        timestamptz,
  is_deleted          boolean     not null default false,
  created_at          timestamptz not null default now(),
  updated_at          timestamptz not null default now()
);

create index if not exists idx_sample_defect_task on sample_defect (task_id);

-- 5. 状态约束(应用层同步校验)
alter table sample_task
  add constraint ck_sample_task_status check (
    status in (
      'draft','submitted','accepted','rejected','in_production','shipped',
      'received','passed','conditional','failed','rework','refunded',
      'cancelled','closed'
    )
  );

-- 6. 更新时间触发器(正式迁移中需按平台规范使用本地函数,不使用 moddatetime)
create or replace function fn_sample_touch_updated_at()
returns trigger language plpgsql as $$
begin
  new.updated_at = now();
  return new;
end;
$$;

drop trigger if exists trg_sample_task_touch on sample_task;
create trigger trg_sample_task_touch before update on sample_task
  for each row execute function fn_sample_touch_updated_at();

drop trigger if exists trg_sample_quota_touch on sample_quota;
create trigger trg_sample_quota_touch before update on sample_quota
  for each row execute function fn_sample_touch_updated_at();

drop trigger if exists trg_sample_defect_touch on sample_defect;
create trigger trg_sample_defect_touch before update on sample_defect
  for each row execute function fn_sample_touch_updated_at();

-- 7. 行级权限(示意,正式迁移需按实际角色与策略细化)
-- 买家仅可读自己创建的样品任务;供应商仅可读分配给自己的任务;
-- 运营通过平台内容管理读取,不在业务侧开放写权限。