selfrelease
42 lines
1.7 KiB
SQL
42 lines
1.7 KiB
SQL
-- Migration: 007_create_rules
|
|
-- Description: 考核规则及指标项表
|
|
-- Date: 2026-08-01
|
|
-- Refs: pmdocs/changes/2026-08-01-001-V0.2考核规则配置.md
|
|
|
|
CREATE TABLE IF NOT EXISTS rules (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
code TEXT NOT NULL UNIQUE,
|
|
name TEXT NOT NULL,
|
|
description TEXT,
|
|
period_type TEXT NOT NULL,
|
|
period_start TEXT,
|
|
period_end TEXT,
|
|
status TEXT NOT NULL DEFAULT 'draft',
|
|
version INTEGER NOT NULL DEFAULT 1,
|
|
parent_id INTEGER REFERENCES rules(id),
|
|
created_by TEXT,
|
|
created_at TEXT NOT NULL DEFAULT (datetime('now', 'localtime')),
|
|
updated_at TEXT NOT NULL DEFAULT (datetime('now', 'localtime'))
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS rule_items (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
rule_id INTEGER NOT NULL REFERENCES rules(id) ON DELETE CASCADE,
|
|
category TEXT NOT NULL,
|
|
name TEXT NOT NULL,
|
|
metric_key TEXT NOT NULL,
|
|
weight REAL NOT NULL,
|
|
min_score REAL NOT NULL DEFAULT 0,
|
|
max_score REAL NOT NULL DEFAULT 100,
|
|
formula_type TEXT NOT NULL,
|
|
formula_params TEXT,
|
|
display_order INTEGER NOT NULL DEFAULT 0,
|
|
enabled INTEGER NOT NULL DEFAULT 1,
|
|
created_at TEXT NOT NULL DEFAULT (datetime('now', 'localtime')),
|
|
updated_at TEXT NOT NULL DEFAULT (datetime('now', 'localtime'))
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_rules_status ON rules(status);
|
|
CREATE INDEX IF NOT EXISTS idx_rules_period ON rules(period_start, period_end);
|
|
CREATE INDEX IF NOT EXISTS idx_rule_items_rule ON rule_items(rule_id);
|
|
CREATE INDEX IF NOT EXISTS idx_rule_items_category ON rule_items(category); |