下载中心/核心表结构(设计稿)

核心表结构(设计稿)

下载原文件
-- pcbjia 核心业务表结构(PostgreSQL 设计稿)
-- 说明:由 MySQL 8.0 设计稿转换而来,仅作为后端与数据建模参考,不在本站执行。
-- 约定:主键 bigserial;软删除 is_deleted boolean;时间 timestamptz;
--       金额 numeric(18,4);状态用 smallint 并在注释中写明枚举。

-- ============ 商城与企业 ============

CREATE TABLE company (
  id bigserial PRIMARY KEY,
  company_name varchar(200) NOT NULL,
  credit_code varchar(50),
  contact_name varchar(50),
  contact_phone varchar(20),
  contact_email varchar(100),
  address varchar(500),
  tax_address varchar(500),
  tax_bank varchar(100),
  tax_account varchar(100),
  level smallint DEFAULT 0,          -- 0普通 1种子 2VIP 3大客户
  credit_limit numeric(18,4) DEFAULT 0,
  credit_used numeric(18,4) DEFAULT 0,
  credit_expire date,
  status smallint DEFAULT 1,          -- 0禁用 1正常
  remark varchar(1000),
  is_deleted boolean DEFAULT false,
  created_at timestamptz DEFAULT now(),
  updated_at timestamptz DEFAULT now()
);

CREATE TABLE app_user (
  id bigserial PRIMARY KEY,
  sso_uid bigint,
  company_id bigint,
  nickname varchar(50),
  role_key varchar(50),               -- buyer / engineer / finance / admin
  is_master boolean DEFAULT false,
  status smallint DEFAULT 1,          -- 0禁用 1正常
  is_deleted boolean DEFAULT false,
  created_at timestamptz DEFAULT now(),
  updated_at timestamptz DEFAULT now()
);
CREATE INDEX idx_app_user_company ON app_user(company_id);

CREATE TABLE role_permission (
  id bigserial PRIMARY KEY,
  role_key varchar(50) NOT NULL,
  permission_code varchar(100) NOT NULL,
  created_at timestamptz DEFAULT now()
);

CREATE TABLE audit_log (
  id bigserial PRIMARY KEY,
  trace_id varchar(100),
  user_id bigint,
  company_id bigint,
  operate_type varchar(50),           -- 新增/修改/删除/导出/下载/下单
  biz_type varchar(50),               -- inquiry / order / car / finance
  biz_id bigint,
  request_ip varchar(50),
  user_agent varchar(500),
  before_content text,
  after_content text,
  remark varchar(1000),
  created_at timestamptz DEFAULT now()
);
CREATE INDEX idx_audit_log_biz ON audit_log(biz_type, biz_id, created_at);

-- ============ 服务商 ============

CREATE TABLE supplier (
  id bigserial PRIMARY KEY,
  supplier_name varchar(200) NOT NULL,
  credit_code varchar(50),
  contact_name varchar(50),
  contact_phone varchar(20),
  contact_email varchar(100),
  supplier_type smallint,             -- 1 PCB工厂 2 元器件供应商
  qualification_files jsonb,
  status smallint DEFAULT 0,          -- 0待审核 1正常 2暂停派单 3淘汰
  can_auto_quote boolean DEFAULT false,
  support_process jsonb,
  commission_rate numeric(6,4),
  settlement_cycle smallint,          -- 7 / 15 / 30 天
  remark varchar(1000),
  is_deleted boolean DEFAULT false,
  created_at timestamptz DEFAULT now(),
  updated_at timestamptz DEFAULT now()
);

CREATE TABLE supplier_performance (
  id bigserial PRIMARY KEY,
  supplier_id bigint NOT NULL,
  stat_date date NOT NULL,
  quote_response_avg_min int,
  delivery_on_time_rate numeric(6,4),
  car_count int DEFAULT 0,
  car_rate numeric(6,4),
  score int,
  is_deleted boolean DEFAULT false,
  created_at timestamptz DEFAULT now()
);
CREATE UNIQUE INDEX uk_supplier_perf ON supplier_performance(supplier_id, stat_date);

-- ============ 工程文件与 BOM ============

CREATE TABLE project_file (
  id bigserial PRIMARY KEY,
  company_id bigint,
  user_id bigint,
  file_type smallint,                 -- 1 BOM 2 Gerber 3 DFM报告 4 其他
  file_name varchar(255),
  file_key varchar(255),
  file_size bigint,
  parse_result jsonb,
  parse_status smallint DEFAULT 0,    -- 0待解析 1成功 2失败
  version_no varchar(30),
  inquiry_id bigint,
  remark varchar(500),
  is_deleted boolean DEFAULT false,
  created_at timestamptz DEFAULT now(),
  updated_at timestamptz DEFAULT now()
);
CREATE INDEX idx_project_file_inquiry ON project_file(inquiry_id);

CREATE TABLE bom_item (
  id bigserial PRIMARY KEY,
  file_id bigint NOT NULL,
  part_no varchar(100),
  package varchar(80),
  designator varchar(500),
  quantity int,
  spec varchar(500),
  is_alternative boolean DEFAULT false,
  stock_status smallint,              -- 0无库存 1现货 2预排
  supplier_id bigint,
  is_deleted boolean DEFAULT false,
  created_at timestamptz DEFAULT now()
);
CREATE INDEX idx_bom_item_file ON bom_item(file_id, part_no);

-- ============ 询价与报价 ============

CREATE TABLE inquiry (
  id bigserial PRIMARY KEY,
  inquiry_no varchar(60) NOT NULL UNIQUE,
  company_id bigint,
  user_id bigint,
  project_name varchar(200),
  inquiry_mode smallint,              -- 1快速样板 2完整预审
  pcb_layers int,
  pcb_thickness numeric(6,3),
  surface_finish varchar(50),
  board_area numeric(12,4),
  qty_sample int,
  qty_batch int,
  dfm_report_id bigint,
  dfm_risk_level smallint DEFAULT 0,  -- 0无风险 1低风险 2高风险
  expire_time timestamptz,
  status smallint DEFAULT 0,          -- 0预审中 1待报价 2比价完成 3已下单 4已取消 5已过期
  remark varchar(1000),
  is_deleted boolean DEFAULT false,
  created_at timestamptz DEFAULT now(),
  updated_at timestamptz DEFAULT now()
);
CREATE INDEX idx_inquiry_company ON inquiry(company_id, status);

CREATE TABLE inquiry_quote (
  id bigserial PRIMARY KEY,
  inquiry_id bigint NOT NULL,
  supplier_id bigint NOT NULL,
  quote_no varchar(60),
  unit_price numeric(18,4),
  total_amount numeric(18,4),
  lead_time_days int,
  warranty_months smallint,           -- 6 / 12 / 24,报价承诺质保期
  process_remark varchar(1000),
  supplier_remark varchar(1000),
  status smallint DEFAULT 0,          -- 0待报价 1已报价 2选中 3落选 4失效
  quote_time timestamptz,
  is_deleted boolean DEFAULT false,
  created_at timestamptz DEFAULT now(),
  updated_at timestamptz DEFAULT now()
);
CREATE INDEX idx_quote_inquiry ON inquiry_quote(inquiry_id, status);

CREATE TABLE dfm_defect (
  id bigserial PRIMARY KEY,
  file_id bigint,
  inquiry_id bigint,
  defect_code varchar(50),
  defect_name varchar(200),
  defect_level smallint,              -- 1致命 2高危 3一般 4提示
  ipc_clause varchar(100),
  coordinate varchar(100),
  layer_name varchar(50),
  suggest text,
  image_key varchar(255),
  is_confirm boolean DEFAULT false,
  is_deleted boolean DEFAULT false,
  created_at timestamptz DEFAULT now()
);
CREATE INDEX idx_dfm_defect_file ON dfm_defect(file_id);

-- ============ 订单与履约 ============

CREATE TABLE sales_order (
  id bigserial PRIMARY KEY,
  order_no varchar(60) NOT NULL UNIQUE,
  inquiry_id bigint,
  quote_id bigint,
  company_id bigint,
  supplier_id bigint,
  total_amount numeric(18,4),
  commission_amount numeric(18,4),
  supplier_amount numeric(18,4),
  pay_status smallint DEFAULT 0,      -- 0待支付 1已支付 2部分退款 3全额退款
  order_status smallint DEFAULT 0,    -- 0待付款 1排产 2生产中 3质检 4发货 5签收 6取消
  receive_name varchar(50),
  receive_phone varchar(20),
  receive_address varchar(500),
  logistics_no varchar(100),
  logistics_company varchar(50),
  remark varchar(1000),
  is_deleted boolean DEFAULT false,
  created_at timestamptz DEFAULT now(),
  updated_at timestamptz DEFAULT now()
);
CREATE INDEX idx_order_supplier ON sales_order(supplier_id, order_status);

CREATE TABLE order_track (
  id bigserial PRIMARY KEY,
  order_id bigint NOT NULL,
  track_code varchar(50),             -- pay / arrange / produce / qa / deliver / sign
  track_name varchar(100),
  operate_user_type smallint,         -- 1平台 2供应商
  operate_user_id bigint,
  attach_file_key varchar(255),
  remark varchar(500),
  created_at timestamptz DEFAULT now()
);
CREATE INDEX idx_order_track ON order_track(order_id, created_at);

-- ============ 财务与单证 ============

CREATE TABLE pay_log (
  id bigserial PRIMARY KEY,
  order_id bigint,
  pay_no varchar(100),
  channel_trade_no varchar(100),
  pay_amount numeric(18,4),
  pay_channel varchar(30),
  pay_status smallint DEFAULT 0,
  pay_time timestamptz,
  created_at timestamptz DEFAULT now()
);

CREATE TABLE settle_record (
  id bigserial PRIMARY KEY,
  order_id bigint,
  supplier_id bigint,
  settle_no varchar(60),
  order_amount numeric(18,4),
  commission numeric(18,4),
  supplier_amount numeric(18,4),
  withhold_quality_deposit numeric(18,4),
  settle_status smallint DEFAULT 0,
  settle_time timestamptz,
  remark varchar(1000),
  is_deleted boolean DEFAULT false,
  created_at timestamptz DEFAULT now()
);

CREATE TABLE invoice_apply (
  id bigserial PRIMARY KEY,
  order_id bigint,
  company_id bigint,
  invoice_type smallint,              -- 1专票 2普票
  invoice_title varchar(200),
  tax_no varchar(50),
  invoice_amount numeric(18,4),
  status smallint DEFAULT 0,          -- 0待审核 1已开票 2红冲申请 3已红冲
  invoice_file_key varchar(255),
  apply_time timestamptz,
  is_deleted boolean DEFAULT false,
  created_at timestamptz DEFAULT now()
);

CREATE TABLE trade_doc_template (
  id bigserial PRIMARY KEY,
  template_name varchar(128),
  doc_type varchar(32),               -- pi / ci / pl / contract
  supplier_id bigint DEFAULT 0,       -- 0 表示平台全局模板
  lang varchar(16) DEFAULT 'en',
  content text,
  bank_info text,
  remark varchar(255),
  status smallint DEFAULT 1,
  created_at timestamptz DEFAULT now(),
  updated_at timestamptz DEFAULT now()
);

CREATE TABLE trade_hs_code (
  id bigserial PRIMARY KEY,
  hs_code varchar(32),
  product_name_cn varchar(255),
  product_name_en varchar(255),
  declare_element text,
  tax_rate numeric(5,2),
  remark varchar(255),
  created_at timestamptz DEFAULT now()
);

CREATE TABLE trade_document (
  id bigserial PRIMARY KEY,
  doc_sn varchar(64) NOT NULL UNIQUE,
  order_id bigint,
  rfq_id bigint,
  supplier_id bigint NOT NULL,
  buyer_id bigint,
  doc_type varchar(32),
  template_id bigint,
  currency varchar(16) DEFAULT 'USD',
  exchange_rate numeric(10,4),
  total_amount numeric(12,2),
  trade_term varchar(16),             -- FOB / CIF / EXW / DAP
  ship_port varchar(128),
  dest_port varchar(128),
  mark text,
  nw varchar(64),
  gw varchar(64),
  box_qty int DEFAULT 0,
  pdf_url varchar(512),
  version int DEFAULT 1,
  status smallint DEFAULT 1,
  created_by bigint,
  created_at timestamptz DEFAULT now(),
  updated_at timestamptz DEFAULT now()
);
CREATE INDEX idx_trade_doc_order ON trade_document(order_id);

CREATE TABLE trade_document_item (
  id bigserial PRIMARY KEY,
  doc_id bigint NOT NULL,
  product_desc varchar(512),
  layers varchar(32),
  material varchar(128),
  surface_finish varchar(128),
  qty int,
  unit_price numeric(12,2),
  amount numeric(12,2),
  hs_code varchar(32),
  created_at timestamptz DEFAULT now()
);
CREATE INDEX idx_trade_doc_item ON trade_document_item(doc_id);

-- ============ CAR 品质工单 ============

CREATE TABLE car (
  id bigserial PRIMARY KEY,
  car_no varchar(60) NOT NULL UNIQUE,
  order_id bigint,
  company_id bigint,
  supplier_id bigint,
  defect_type varchar(100),
  defect_level smallint,              -- 1严重 2一般 3轻微
  description text,
  attach_list jsonb,
  status smallint DEFAULT 0,          -- 0新建 1确认 2临时对策 3提交8D 4评审 5闭环 6驳回
  eight_d_file_key varchar(255),
  close_time timestamptz,
  handler_id bigint,
  is_deleted boolean DEFAULT false,
  created_at timestamptz DEFAULT now(),
  updated_at timestamptz DEFAULT now()
);
CREATE INDEX idx_car_supplier ON car(supplier_id, status);

-- ============ 通知与配置 ============

CREATE TABLE notice (
  id bigserial PRIMARY KEY,
  user_id bigint,
  title varchar(200),
  content text,
  biz_type varchar(50),
  biz_id bigint,
  is_read boolean DEFAULT false,
  push_channel varchar(100),
  created_at timestamptz DEFAULT now()
);
CREATE INDEX idx_notice_user ON notice(user_id, is_read);

CREATE TABLE sys_config (
  id bigserial PRIMARY KEY,
  config_key varchar(100) UNIQUE,
  config_value jsonb,
  config_desc varchar(200),
  created_at timestamptz DEFAULT now(),
  updated_at timestamptz DEFAULT now()
);

-- ============ 积分与任务 ============

CREATE TABLE task_config (
  id bigserial PRIMARY KEY,
  task_name varchar(255),
  task_type varchar(32),              -- once / daily / weekly
  user_role varchar(32),              -- buyer / supplier / all
  task_desc text,
  reward_point int DEFAULT 0,
  max_reward_times int DEFAULT 1,
  sort int DEFAULT 0,
  status smallint DEFAULT 1,
  created_at timestamptz DEFAULT now(),
  updated_at timestamptz DEFAULT now()
);

CREATE TABLE task_user_record (
  id bigserial PRIMARY KEY,
  user_id bigint NOT NULL,
  task_id bigint NOT NULL,
  task_date date,
  reward_point int DEFAULT 0,
  status smallint DEFAULT 1,
  created_at timestamptz DEFAULT now()
);
CREATE UNIQUE INDEX uk_task_user_date ON task_user_record(user_id, task_id, task_date);

CREATE TABLE user_sign (
  id bigserial PRIMARY KEY,
  user_id bigint NOT NULL,
  sign_date date NOT NULL,
  continue_day int DEFAULT 1,
  reward_point int DEFAULT 0,
  is_patch boolean DEFAULT false,
  created_at timestamptz DEFAULT now()
);
CREATE UNIQUE INDEX uk_user_sign ON user_sign(user_id, sign_date);

CREATE TABLE user_point (
  id bigserial PRIMARY KEY,
  user_id bigint NOT NULL UNIQUE,
  total_point int DEFAULT 0,
  frozen_point int DEFAULT 0,
  valid_point int DEFAULT 0,
  created_at timestamptz DEFAULT now(),
  updated_at timestamptz DEFAULT now()
);

CREATE TABLE point_log (
  id bigserial PRIMARY KEY,
  user_id bigint NOT NULL,
  change_point int NOT NULL,
  type varchar(32),                   -- sign / task / exchange / deduct / expire
  rel_id bigint DEFAULT 0,
  remark varchar(255),
  created_at timestamptz DEFAULT now()
);
CREATE INDEX idx_point_log_user ON point_log(user_id, created_at);

CREATE TABLE point_goods (
  id bigserial PRIMARY KEY,
  goods_name varchar(255),
  goods_type varchar(32),             -- dfm / quote / exposure / export
  need_point int DEFAULT 0,
  stock int DEFAULT 9999,
  limit_user_role varchar(32),
  status smallint DEFAULT 1,
  created_at timestamptz DEFAULT now()
);

CREATE TABLE point_exchange (
  id bigserial PRIMARY KEY,
  exchange_sn varchar(64) NOT NULL UNIQUE,
  user_id bigint NOT NULL,
  goods_id bigint NOT NULL,
  consume_point int DEFAULT 0,
  status smallint DEFAULT 0,          -- 0待生效 1已生效 2已撤销
  created_at timestamptz DEFAULT now()
);

-- ============ 社区域:账号与积分 ============

CREATE TABLE community_user (
  id bigserial PRIMARY KEY,
  username varchar(64) UNIQUE,
  nickname varchar(64),
  avatar varchar(255),
  email varchar(120),
  phone varchar(32),
  status smallint DEFAULT 0,          -- 0正常 1禁言 2封禁
  is_online boolean DEFAULT false,
  online_at timestamptz,
  privacy_setting jsonb,
  ai_setting jsonb,
  notify_setting jsonb,
  is_deleted boolean DEFAULT false,
  created_at timestamptz DEFAULT now(),
  updated_at timestamptz DEFAULT now()
);

CREATE TABLE community_user_tag (
  id bigserial PRIMARY KEY,
  user_id bigint NOT NULL,
  tag_name varchar(64),
  source smallint DEFAULT 0,          -- 0自选 1AI推荐 2管理员认证
  is_deleted boolean DEFAULT false,
  created_at timestamptz DEFAULT now()
);

CREATE TABLE community_user_auth (
  id bigserial PRIMARY KEY,
  user_id bigint NOT NULL,
  apply_content text,
  status smallint DEFAULT 0,          -- 0待审 1通过 2驳回
  expire_at timestamptz,
  audit_uid bigint,
  audit_note text,
  is_deleted boolean DEFAULT false,
  created_at timestamptz DEFAULT now(),
  updated_at timestamptz DEFAULT now()
);

CREATE TABLE community_user_credit (
  id bigserial PRIMARY KEY,
  user_id bigint NOT NULL,
  change_point int NOT NULL,
  balance int,
  scene varchar(64),                  -- forum_post / best_answer / essence / reward_publish / reward_accept
  rel_id bigint,
  remark text,
  is_deleted boolean DEFAULT false,
  created_at timestamptz DEFAULT now()
);
CREATE INDEX idx_community_credit_user ON community_user_credit(user_id, created_at);

CREATE TABLE credit_freeze (
  id bigserial PRIMARY KEY,
  user_id bigint NOT NULL,
  reward_id bigint,
  freeze_amount int,
  status smallint DEFAULT 0,          -- 0冻结中 1已发放 2已解冻
  is_deleted boolean DEFAULT false,
  created_at timestamptz DEFAULT now(),
  updated_at timestamptz DEFAULT now()
);

-- ============ 社区域:论坛 ============

CREATE TABLE forum_section (
  id bigserial PRIMARY KEY,
  name varchar(100),
  description text,
  sort int DEFAULT 0,
  is_hide boolean DEFAULT false,
  admin_uid bigint,
  is_deleted boolean DEFAULT false,
  created_at timestamptz DEFAULT now(),
  updated_at timestamptz DEFAULT now()
);

CREATE TABLE forum_post (
  id bigserial PRIMARY KEY,
  section_id bigint,
  user_id bigint,
  post_type smallint DEFAULT 0,       -- 0求助 1经验 2专栏
  title varchar(255),
  content text,
  ai_summary text,
  status smallint DEFAULT 0,          -- 0待解决 1已解决 2待补充 3归档
  is_essence boolean DEFAULT false,
  reward_credit int DEFAULT 0,
  is_anonymous boolean DEFAULT false,
  template_key varchar(64),
  hot_score int DEFAULT 0,
  bind_chat_thread_id bigint,
  is_deleted boolean DEFAULT false,
  created_at timestamptz DEFAULT now(),
  updated_at timestamptz DEFAULT now()
);
CREATE INDEX idx_forum_post_section ON forum_post(section_id, status, created_at DESC);
CREATE INDEX idx_forum_post_bind ON forum_post(bind_chat_thread_id);

CREATE TABLE forum_post_tag (
  id bigserial PRIMARY KEY,
  post_id bigint NOT NULL,
  tag_name varchar(64),
  source smallint DEFAULT 0,
  is_deleted boolean DEFAULT false,
  created_at timestamptz DEFAULT now()
);

CREATE TABLE forum_reply (
  id bigserial PRIMARY KEY,
  post_id bigint NOT NULL,
  user_id bigint,
  content text,
  is_best boolean DEFAULT false,
  is_deleted boolean DEFAULT false,
  created_at timestamptz DEFAULT now(),
  updated_at timestamptz DEFAULT now()
);
CREATE INDEX idx_forum_reply_post ON forum_reply(post_id, created_at);

CREATE TABLE forum_reply_edit_log (
  id bigserial PRIMARY KEY,
  rel_type varchar(16),               -- post / reply
  rel_id bigint,
  user_id bigint,
  old_content text,
  new_content text,
  is_deleted boolean DEFAULT false,
  created_at timestamptz DEFAULT now()
);

-- ============ 社区域:聊天室 ============

CREATE TABLE chat_room (
  id bigserial PRIMARY KEY,
  name varchar(100),
  description text,
  notice text,
  sort int DEFAULT 0,
  is_hide boolean DEFAULT false,
  admin_uid bigint,
  is_deleted boolean DEFAULT false,
  created_at timestamptz DEFAULT now(),
  updated_at timestamptz DEFAULT now()
);

CREATE TABLE chat_thread (
  id bigserial PRIMARY KEY,
  room_id bigint NOT NULL,
  user_id bigint,
  title varchar(255),
  is_essence boolean DEFAULT false,
  is_archived boolean DEFAULT false,
  bind_forum_post_id bigint,
  satisfy_uid bigint,
  satisfy_msg_id bigint,
  is_deleted boolean DEFAULT false,
  created_at timestamptz DEFAULT now(),
  updated_at timestamptz DEFAULT now()
);
CREATE INDEX idx_chat_thread_room ON chat_thread(room_id, created_at DESC);
CREATE INDEX idx_chat_thread_bind ON chat_thread(bind_forum_post_id);

CREATE TABLE chat_message (
  id bigserial PRIMARY KEY,
  thread_id bigint,
  room_id bigint,
  user_id bigint,
  content text,
  quote_msg_id bigint,
  is_essence_single boolean DEFAULT false,
  edit_count int DEFAULT 0,
  last_edit_at timestamptz,
  is_deleted boolean DEFAULT false,
  created_at timestamptz DEFAULT now(),
  updated_at timestamptz DEFAULT now()
);
CREATE INDEX idx_chat_message_thread ON chat_message(thread_id, created_at);
CREATE INDEX idx_chat_message_room ON chat_message(room_id, created_at);

CREATE TABLE chat_thread_tag (
  id bigserial PRIMARY KEY,
  thread_id bigint NOT NULL,
  tag_name varchar(64),
  source smallint DEFAULT 0,
  is_deleted boolean DEFAULT false,
  created_at timestamptz DEFAULT now()
);

-- ============ 社区域:附件、私信、互动 ============

CREATE TABLE attachment (
  id bigserial PRIMARY KEY,
  upload_user_id bigint,
  file_name varchar(255),
  file_path varchar(512),
  file_type varchar(32),
  file_size bigint,
  md5 varchar(64),
  is_watermark boolean DEFAULT false,
  rel_type varchar(32),               -- post / reply / chat_msg / chat_thread / pm
  rel_id bigint,
  is_deleted boolean DEFAULT false,
  created_at timestamptz DEFAULT now()
);
CREATE INDEX idx_attachment_rel ON attachment(rel_type, rel_id);
CREATE INDEX idx_attachment_md5 ON attachment(md5);

CREATE TABLE pm_conversation (
  id bigserial PRIMARY KEY,
  user_a bigint NOT NULL,
  user_b bigint NOT NULL,
  last_msg_at timestamptz,
  is_deleted boolean DEFAULT false,
  created_at timestamptz DEFAULT now(),
  updated_at timestamptz DEFAULT now()
);
CREATE UNIQUE INDEX uk_pm_conv ON pm_conversation(user_a, user_b);

CREATE TABLE pm_message (
  id bigserial PRIMARY KEY,
  conv_id bigint NOT NULL,
  sender_id bigint,
  receiver_id bigint,
  content text,
  is_read boolean DEFAULT false,
  is_deleted boolean DEFAULT false,
  created_at timestamptz DEFAULT now()
);

CREATE TABLE community_notification (
  id bigserial PRIMARY KEY,
  user_id bigint NOT NULL,
  type varchar(32),                   -- at / reply / reward / essence / bind_thread / audit / pm
  title varchar(200),
  content text,
  rel_type varchar(32),
  rel_id bigint,
  is_read boolean DEFAULT false,
  is_deleted boolean DEFAULT false,
  created_at timestamptz DEFAULT now()
);
CREATE INDEX idx_community_notice ON community_notification(user_id, is_read);

CREATE TABLE user_collect (
  id bigserial PRIMARY KEY,
  user_id bigint NOT NULL,
  rel_type varchar(32),               -- forum_post / chat_thread
  rel_id bigint,
  folder_name varchar(64),
  is_deleted boolean DEFAULT false,
  created_at timestamptz DEFAULT now()
);

CREATE TABLE user_follow (
  id bigserial PRIMARY KEY,
  from_uid bigint NOT NULL,
  to_uid bigint NOT NULL,
  is_deleted boolean DEFAULT false,
  created_at timestamptz DEFAULT now()
);
CREATE UNIQUE INDEX uk_user_follow ON user_follow(from_uid, to_uid);

CREATE TABLE user_blacklist (
  id bigserial PRIMARY KEY,
  from_uid bigint NOT NULL,
  to_uid bigint NOT NULL,
  is_deleted boolean DEFAULT false,
  created_at timestamptz DEFAULT now()
);
CREATE UNIQUE INDEX uk_user_blacklist ON user_blacklist(from_uid, to_uid);

CREATE TABLE content_report (
  id bigserial PRIMARY KEY,
  user_id bigint,
  rel_type varchar(32),
  rel_id bigint,
  reason text,
  status smallint DEFAULT 0,          -- 0待处理 1已处理 2驳回
  handle_uid bigint,
  handle_note text,
  is_deleted boolean DEFAULT false,
  created_at timestamptz DEFAULT now(),
  updated_at timestamptz DEFAULT now()
);

-- ============ 社区域:AI 与治理 ============

CREATE TABLE essence_index (
  id bigserial PRIMARY KEY,
  source_type varchar(32),            -- forum_post / chat_thread / chat_msg
  source_id bigint,
  content text,
  tags jsonb,
  vector_id varchar(128),
  is_valid boolean DEFAULT true,
  is_deleted boolean DEFAULT false,
  created_at timestamptz DEFAULT now(),
  updated_at timestamptz DEFAULT now()
);
CREATE INDEX idx_essence_source ON essence_index(source_type, source_id);

CREATE TABLE ai_usage_log (
  id bigserial PRIMARY KEY,
  user_id bigint,
  scene varchar(32),                  -- chat / forum_pre / translate / 8d / calc
  prompt text,
  resp text,
  cost int DEFAULT 0,
  is_deleted boolean DEFAULT false,
  created_at timestamptz DEFAULT now()
);
CREATE INDEX idx_ai_usage_user ON ai_usage_log(user_id, created_at);

CREATE TABLE rate_limit_record (
  id bigserial PRIMARY KEY,
  user_id bigint,
  scene varchar(32),                  -- chat / forum_post
  lock_until timestamptz,
  is_deleted boolean DEFAULT false,
  created_at timestamptz DEFAULT now()
);

CREATE TABLE help_doc (
  id bigserial PRIMARY KEY,
  title varchar(255),
  content text,
  sort int DEFAULT 0,
  is_show boolean DEFAULT true,
  is_deleted boolean DEFAULT false,
  created_at timestamptz DEFAULT now(),
  updated_at timestamptz DEFAULT now()
);