核心表结构(设计稿)
下载原文件-- 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()
);
