下载中心/建表设计稿

建表设计稿

下载原文件
-- P30 风控与反作弊建表设计稿(PostgreSQL)
-- 说明:仅作数据结构参考,不直接在生产执行。

CREATE TABLE risk_rule (
  id BIGSERIAL PRIMARY KEY,
  scene VARCHAR(64) NOT NULL,
  code VARCHAR(64) NOT NULL,
  name VARCHAR(128) NOT NULL,
  threshold NUMERIC(12, 2),
  weight INTEGER NOT NULL DEFAULT 10,
  enabled BOOLEAN NOT NULL DEFAULT TRUE,
  gray_ratio NUMERIC(4, 2) NOT NULL DEFAULT 0,
  created_at TIMESTAMPTZ DEFAULT now(),
  updated_at TIMESTAMPTZ DEFAULT now(),
  CONSTRAINT uk_risk_rule UNIQUE (scene, code)
);

COMMENT ON TABLE risk_rule IS '风控规则配置,gray_ratio 为灰度比例 0-1';

CREATE TABLE risk_event (
  id BIGSERIAL PRIMARY KEY,
  scene VARCHAR(64) NOT NULL,
  subject_id VARCHAR(64),
  ip VARCHAR(64),
  score INTEGER NOT NULL,
  action VARCHAR(16) NOT NULL,
  reasons JSONB,
  payload JSONB,
  handled_by VARCHAR(64),
  handled_at TIMESTAMPTZ,
  decision VARCHAR(16),
  note VARCHAR(512),
  created_at TIMESTAMPTZ DEFAULT now()
);

COMMENT ON TABLE risk_event IS '风险事件流水,action 取值 pass/challenge/review/block';
COMMENT ON COLUMN risk_event.payload IS '仅保存业务判断所需字段,不保存文件内容与联系方式明文';

CREATE INDEX idx_risk_event_time ON risk_event (created_at);
CREATE INDEX idx_risk_event_subject ON risk_event (subject_id);
CREATE INDEX idx_risk_event_action ON risk_event (action);

CREATE TABLE risk_list (
  id BIGSERIAL PRIMARY KEY,
  list_type VARCHAR(16) NOT NULL,
  target VARCHAR(128) NOT NULL,
  level VARCHAR(16) NOT NULL DEFAULT 'watch',
  reason VARCHAR(255),
  expire_at TIMESTAMPTZ,
  created_at TIMESTAMPTZ DEFAULT now(),
  CONSTRAINT uk_risk_list UNIQUE (list_type, target)
);

COMMENT ON TABLE risk_list IS 'IP 与主体名单,level 取值 watch/limit/ban';

CREATE TABLE risk_appeal (
  id BIGSERIAL PRIMARY KEY,
  event_id BIGINT REFERENCES risk_event (id) ON DELETE SET NULL,
  subject_id VARCHAR(64),
  content TEXT,
  status VARCHAR(16) NOT NULL DEFAULT 'pending',
  result VARCHAR(16),
  note VARCHAR(512),
  created_at TIMESTAMPTZ DEFAULT now(),
  handled_at TIMESTAMPTZ
);

COMMENT ON TABLE risk_appeal IS '申诉记录,status 取值 pending/accepted/rejected';

CREATE TABLE risk_audit_log (
  id BIGSERIAL PRIMARY KEY,
  operator VARCHAR(64) NOT NULL,
  target_type VARCHAR(32) NOT NULL,
  target_id VARCHAR(64) NOT NULL,
  action VARCHAR(32) NOT NULL,
  before_json JSONB,
  after_json JSONB,
  created_at TIMESTAMPTZ DEFAULT now()
);

COMMENT ON TABLE risk_audit_log IS '规则变更与人工处置审计,只追加不修改';

CREATE OR REPLACE FUNCTION risk_rule_touch_updated_at()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = now();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_risk_rule_touch
BEFORE UPDATE ON risk_rule
FOR EACH ROW EXECUTE FUNCTION risk_rule_touch_updated_at();

-- 频次统计视图:按主体与场景统计近 24 小时命中次数
CREATE OR REPLACE VIEW v_risk_subject_24h AS
SELECT
  subject_id,
  scene,
  COUNT(*) FILTER (WHERE action <> 'pass') AS hit_24h,
  MAX(created_at) AS last_at
FROM risk_event
WHERE created_at >= now() - INTERVAL '24 hours'
GROUP BY subject_id, scene;

COMMENT ON VIEW v_risk_subject_24h IS '供提交前校验读取近 24 小时命中次数';