下载中心/建表设计稿(不直接执行)

建表设计稿(不直接执行)

下载原文件
-- 数据库建表设计稿(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();