下载中心/建表设计稿

建表设计稿

下载原文件
-- 订单履约与交付看板 · 建表设计稿 (P25)
-- 说明:原方案给的是 MySQL 语法,本站运行在 PostgreSQL(Supabase),
-- 此文件已按 Postgres 习惯改写,仅作设计参考,不直接执行;
-- 正式落地需由平台数据库工具生成迁移,并补齐中文注释、索引、更新时间触发器与行级权限。

create table if not exists order_task (
  id                text primary key,
  order_sn          text unique not null,
  user_id           text not null,
  supplier_id       text not null,
  rfq_id            text,
  sample_id         text,
  quote_id          text,
  order_type        text not null default 'pcb' check (order_type in ('pcb','sample','material','test')),
  total_amount      numeric(14,4) not null default 0,
  currency          text not null default 'CNY',
  pay_status        smallint not null default 0,
  promise_lead_days int,
  status            text not null default 'draft',
  sla_expire_at     timestamptz,
  remark            text,
  attach_json       text default '[]',
  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_order_task_user on order_task (user_id, status);
create index if not exists idx_order_task_supplier on order_task (supplier_id, status);
create index if not exists idx_order_task_rfq on order_task (rfq_id);

alter table order_task
  add constraint ck_order_task_status check (
    status in (
      'draft','submitted','scheduled','first_article','in_production','testing',
      'ready_to_ship','shipped','received','reconciling','completed',
      'on_hold','rework','cancelled'
    )
  );

create table if not exists order_lot (
  id                   text primary key,
  order_id             text not null,
  lot_no               text not null,
  qty_total            int not null default 0,
  qty_good             int not null default 0,
  qty_defect           int not null default 0,
  first_confirm_status smallint not null default 0,
  process_locked       boolean not null default false,
  supplier_note        text,
  created_at           timestamptz not null default now(),
  updated_at           timestamptz not null default now()
);

create index if not exists idx_order_lot_order on order_lot (order_id);

create table if not exists order_process_log (
  id           bigserial primary key,
  lot_id       text not null,
  process_code text not null,
  process_name text,
  operator     text,
  start_at     timestamptz,
  finish_at    timestamptz,
  qty_in       int not null default 0,
  qty_defect   int not null default 0,
  inspection   text not null default 'pass',
  pic_json     text default '[]',
  note         text,
  created_at   timestamptz not null default now()
);

create index if not exists idx_order_process_lot on order_process_log (lot_id, created_at);

create table if not exists order_status_log (
  id         bigserial primary key,
  order_id   text not null,
  from_status text,
  to_status  text not null,
  operator_role text,
  note       text,
  created_at timestamptz not null default now()
);

create index if not exists idx_order_status_log_order on order_status_log (order_id, created_at);

create table if not exists order_ship (
  id                text primary key,
  order_id          text not null,
  lot_id            text,
  logistics_company text,
  logistics_no      text,
  ship_qty          int not null default 0,
  ship_time         timestamptz,
  receive_time      timestamptz,
  inspection        text,
  created_at        timestamptz not null default now()
);

create index if not exists idx_order_ship_order on order_ship (order_id);

create or replace function fn_order_touch_updated_at()
returns trigger language plpgsql as $$
begin
  new.updated_at = now();
  return new;
end;
$$;

drop trigger if exists trg_order_task_touch on order_task;
create trigger trg_order_task_touch before update on order_task
  for each row execute function fn_order_touch_updated_at();

drop trigger if exists trg_order_lot_touch on order_lot;
create trigger trg_order_lot_touch before update on order_lot
  for each row execute function fn_order_touch_updated_at();

-- 行级权限(示意):采购仅见本人订单,供应商仅见本厂订单,运营通过平台内容管理读取。