建表设计稿
下载原文件-- 订单履约与交付看板 · 建表设计稿 (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();
-- 行级权限(示意):采购仅见本人订单,供应商仅见本厂订单,运营通过平台内容管理读取。
