建表设计稿
下载原文件-- 样品申请与追踪 · 建表设计稿 (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. 行级权限(示意,正式迁移需按实际角色与策略细化)
-- 买家仅可读自己创建的样品任务;供应商仅可读分配给自己的任务;
-- 运营通过平台内容管理读取,不在业务侧开放写权限。
