建表设计稿(不直接执行)
下载原文件-- 数据库建表设计稿(PostgreSQL 方言)
-- 用途:评审与前后端契约对齐。本文件不会被直接执行,真实建表由平台内置能力完成。
-- 来源:需求方 MySQL 建表语句的等价改写,方言换算规则见同目录 README.md。
create table if not exists profiles (
id uuid primary key,
display_name text,
company_name text,
role text not null default 'buyer' check (role in ('buyer','provider','admin')),
credit_score smallint not null default 100 check (credit_score between 0 and 100),
is_active boolean not null default true,
created_at timestamptz not null default now(),
updated_at timestamptz not null default now()
);
create table if not exists rfq_bids (
id bigint generated by default as identity primary key,
code varchar(32) not null unique,
title text not null,
category text,
layers smallint,
board_size text,
quantity integer not null default 1,
budget_min numeric(14,2),
budget_max numeric(14,2),
currency char(3) not null default 'CNY',
lead_time_days smallint,
status text not null default 'open'
check (status in ('open','reviewing','closed','failed','cancelled')),
description text,
attachments jsonb not null default '[]'::jsonb,
owner_id uuid references profiles (id),
created_at timestamptz not null default now(),
updated_at timestamptz not null default now()
);
create index if not exists idx_rfq_bids_status on rfq_bids (status, created_at desc);
create table if not exists rfq_quotes (
id bigint generated by default as identity primary key,
bid_id bigint not null references rfq_bids (id) on delete cascade,
provider_id uuid references profiles (id),
amount numeric(14,2) not null,
lead_time_days smallint not null,
warranty_months smallint check (warranty_months in (6,12,24)),
plan text,
accepted 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_rfq_quotes_bid on rfq_quotes (bid_id, created_at desc);
create table if not exists rfq_orders (
id bigint generated by default as identity primary key,
code varchar(32) not null unique,
bid_id bigint references rfq_bids (id),
quote_id bigint references rfq_quotes (id),
buyer_id uuid references profiles (id),
provider_id uuid references profiles (id),
title text not null,
amount numeric(14,2) not null,
lead_time_days smallint not null,
status text not null default 'created'
check (status in ('created','in_progress','production','testing','shipping','signed','completed','disputed','cancelled')),
rating smallint check (rating between 1 and 5),
review text,
created_at timestamptz not null default now(),
updated_at timestamptz not null default now()
);
create table if not exists rfq_order_milestones (
id bigint generated by default as identity primary key,
order_id bigint not null references rfq_orders (id) on delete cascade,
stage text not null
check (stage in ('created','in_progress','production','testing','shipping','signed','completed','disputed','cancelled')),
note text,
occurred_at timestamptz not null default now(),
created_at timestamptz not null default now(),
updated_at timestamptz not null default now()
);
create index if not exists idx_milestones_order on rfq_order_milestones (order_id, occurred_at);
create table if not exists rfq_order_deliverables (
id bigint generated by default as identity primary key,
order_id bigint not null references rfq_orders (id) on delete cascade,
stage text not null,
deliverable_type text not null
check (deliverable_type in ('开工确认单','质检报告','出货单','签收单','其他')),
file_name text not null,
file_url text,
note text,
created_at timestamptz not null default now(),
updated_at timestamptz not null default now()
);
create index if not exists idx_deliverables_order on rfq_order_deliverables (order_id, stage);
create table if not exists provider_credit_logs (
id bigint generated by default as identity primary key,
provider_id uuid references profiles (id),
order_id bigint references rfq_orders (id),
subject text not null,
delta smallint not null,
reason text not null,
created_at timestamptz not null default now()
);
create index if not exists idx_credit_logs_provider on provider_credit_logs (provider_id, created_at desc);
create table if not exists risk_intercepts (
id bigint generated by default as identity primary key,
provider_id uuid references profiles (id),
bid_id bigint references rfq_bids (id),
content_excerpt text,
matched_words jsonb not null default '[]'::jsonb,
delta smallint not null default -10,
created_at timestamptz not null default now()
);
create table if not exists impedance_calc_records (
id bigint generated by default as identity primary key,
model_id text not null,
stackup_id text,
params jsonb not null,
result jsonb not null,
created_at timestamptz not null default now()
);
create or replace function touch_updated_at() returns trigger as $$
begin
new.updated_at = now();
return new;
end;
$$ language plpgsql;
create trigger trg_profiles_updated before update on profiles
for each row execute function touch_updated_at();
create trigger trg_rfq_bids_updated before update on rfq_bids
for each row execute function touch_updated_at();
create trigger trg_rfq_quotes_updated before update on rfq_quotes
for each row execute function touch_updated_at();
create trigger trg_rfq_orders_updated before update on rfq_orders
for each row execute function touch_updated_at();
create trigger trg_rfq_order_milestones_updated before update on rfq_order_milestones
for each row execute function touch_updated_at();
create trigger trg_rfq_order_deliverables_updated before update on rfq_order_deliverables
for each row execute function touch_updated_at();
