v1.7.0 2026-03-26

DB Manual

Oracle DDL 스크립트 — 운영 환경 수동 테이블 생성용

warning
운영 Oracle DB는 자동 마이그레이션을 지원하지 않습니다. 아래 DDL을 번호 순서대로 실행하세요. FK 의존성 때문에 순서가 중요합니다.

테이블 목록 (총 26개)

#그룹테이블용도
1공통users사용자 계정 + 역할
2공통llm_usage_logLLM API 호출 로그
3이상탐지detection_rules감지 규칙 정의
4이상탐지rule_audit_log규칙 변경 이력
5이상탐지correlations이상 상관 그룹
6이상탐지anomalies감지된 이상
7이상탐지detection_cycles감지 사이클 로그
8Tool Studiods_connectionsDB 접속 관리
9Tool Studiods_tools도구 정의
10Tool Studiotool_audit_log도구 변경 이력
11Tool Studiotool_versions도구 버전 관리
12Tool Studiods_query_log쿼리 실행 로그
13워크플로우workflows워크플로우 정의
14워크플로우workflow_runs실행 이력
15워크플로우workflow_audit_log워크플로우 변경 이력
16RAGkb_documentsKnowledge Base 문서
17공통system_settings시스템 설정 (하이퍼파라미터)
18AI Chattool_requests도구 추가 요청
19AI Chattool_request_history도구 요청 상태 변경 이력
20AI Chatai_chat_sessionsAI 채팅 세션
21AI Chatai_chat_messages채팅 메시지
22공통activity_log사용자 활동 감사 로그
23AI Chatquestion_flows후속질문 흐름 관리
24Playbookcountermeasures대안/조치 플레이북
25AI Chatuser_prompts사용자 개인 프롬프트
26AI Chatuser_prompt_versions개인 프롬프트 버전 이력

format_list_numbered 실행 순서 가이드

FK 의존성 때문에 반드시 아래 순서를 지켜야 합니다.

  1. users — 다른 테이블이 참조하지 않는 독립 테이블
  2. llm_usage_log — 독립
  3. detection_rules — anomalies가 참조
  4. rule_audit_log — rule_id 참조 (FK 없지만 논리적 의존)
  5. correlations — anomalies.correlation_id FK
  6. anomalies — detection_rules + correlations 참조
  7. detection_cycles — 독립
  8. ds_connections — ds_tools가 참조
  9. ds_tools — ds_connections 참조
  10. tool_audit_log — 독립 (tool_id 논리적 의존)
  11. tool_versions — 독립 (tool_id 논리적 의존)
  12. ds_query_log — 독립
  13. workflows — workflow_runs가 참조
  14. workflow_runs — workflows 참조
  15. workflow_audit_log — 독립 (workflow_id 논리적 의존)
  16. kb_documents — 독립
  17. system_settings — 독립
  18. tool_requests — 독립
  19. tool_request_history — 독립 (request_id 논리적 의존)
  20. ai_chat_sessions — ai_chat_messages가 참조
  21. ai_chat_messages — sessions 참조
  22. activity_log — 독립
  23. question_flows — 독립
  24. countermeasures — 독립
  25. user_prompts — user_prompt_versions가 참조
  26. user_prompt_versions — user_prompts 참조

ALTER TABLE (FK 추가)은 참조 테이블 생성 후 실행하세요.

people 공통 테이블

1. users

table_chart users — 사용자 계정
CREATE TABLE users ( user_id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY, username VARCHAR2(50) NOT NULL UNIQUE, password VARCHAR2(200) NOT NULL, display_name VARCHAR2(100), role VARCHAR2(20) DEFAULT 'viewer', page_permissions CLOB, -- JSON: 커스텀 권한 오버라이드 enabled NUMBER(1) DEFAULT 1, created_at TIMESTAMP DEFAULT SYSTIMESTAMP );

2. llm_usage_log

table_chart llm_usage_log — LLM 사용량 로그
CREATE TABLE llm_usage_log ( id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY, called_at TIMESTAMP DEFAULT SYSTIMESTAMP, module VARCHAR2(50), model VARCHAR2(100), prompt_tokens NUMBER DEFAULT 0, completion_tokens NUMBER DEFAULT 0, total_tokens NUMBER DEFAULT 0, duration_ms NUMBER DEFAULT 0, success NUMBER(1) DEFAULT 1 );

sensors 이상탐지 테이블

3. detection_rules

table_chart detection_rules — 감지 규칙
CREATE TABLE detection_rules ( rule_id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY, rule_name VARCHAR2(200) NOT NULL, category VARCHAR2(50) NOT NULL, -- logistics / wip / equipment subcategory VARCHAR2(100), query_template CLOB, -- SQL with :bind_var (source_type=sql) check_type VARCHAR2(30) DEFAULT 'threshold' NOT NULL, -- threshold / delta / absence / llm source_type VARCHAR2(10) DEFAULT 'sql', -- sql / tool tool_name VARCHAR2(100), -- 도구명 (source_type=tool) tool_args CLOB, -- 도구 파라미터 JSON tool_column VARCHAR2(100), -- 결과에서 추출할 컬럼명 tool_chain CLOB, -- 멀티 도구 체인 JSON (v1.7.0) threshold_op VARCHAR2(10) DEFAULT '>', warning_value NUMBER, critical_value NUMBER, eval_interval NUMBER DEFAULT 300, -- seconds (규칙별 감지 간격) last_evaluated_at TIMESTAMP, -- 마지막 평가 시각 llm_enabled NUMBER(1) DEFAULT 0, llm_prompt CLOB, enabled NUMBER(1) DEFAULT 1, auto_resolve NUMBER(1) DEFAULT 0, -- 자동 해소 활성화 (v1.7.0) resolve_count NUMBER DEFAULT 3, -- 연속 정상 N회 시 해소 (v1.7.0) created_at TIMESTAMP DEFAULT SYSTIMESTAMP, updated_at TIMESTAMP DEFAULT SYSTIMESTAMP, created_by VARCHAR2(50), updated_by VARCHAR2(50) );

4. rule_audit_log

table_chart rule_audit_log — 규칙 변경 이력
CREATE TABLE rule_audit_log ( log_id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY, rule_id NUMBER NOT NULL, action VARCHAR2(20) NOT NULL, changed_by VARCHAR2(100), changed_at TIMESTAMP DEFAULT SYSTIMESTAMP, changes CLOB );

5. correlations

table_chart correlations — 이상 상관 그룹
CREATE TABLE correlations ( correlation_id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY, title VARCHAR2(500), anomaly_count NUMBER DEFAULT 0, correlation_type VARCHAR2(30), -- temporal / causal / spatial root_cause_guess CLOB, created_at TIMESTAMP DEFAULT SYSTIMESTAMP, updated_at TIMESTAMP DEFAULT SYSTIMESTAMP );

6. anomalies

table_chart anomalies — 감지된 이상
CREATE TABLE anomalies ( anomaly_id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY, rule_id NUMBER REFERENCES detection_rules(rule_id), correlation_id NUMBER, category VARCHAR2(50) NOT NULL, severity VARCHAR2(20) NOT NULL, -- warning / critical title VARCHAR2(500) NOT NULL, description CLOB, measured_value NUMBER, threshold_value NUMBER, affected_entity VARCHAR2(200), -- equipment_id, zone, line llm_analysis CLOB, llm_suggestion CLOB, occurrence_count NUMBER DEFAULT 1, -- 동일 이상 반복 발생 횟수 last_seen_at TIMESTAMP, -- 마지막 발생 시각 status VARCHAR2(30) DEFAULT 'detected', -- detected / acknowledged / investigating / resolved / false_positive detected_at TIMESTAMP DEFAULT SYSTIMESTAMP, acknowledged_at TIMESTAMP, resolved_at TIMESTAMP, resolved_by VARCHAR2(100), notes CLOB, normal_streak NUMBER DEFAULT 0 -- 자동 해소용 정상 연속 카운트 (v1.7.0) ); -- FK: anomalies.correlation_id → correlations ALTER TABLE anomalies ADD CONSTRAINT fk_anomaly_correlation FOREIGN KEY (correlation_id) REFERENCES correlations(correlation_id);

7. detection_cycles

table_chart detection_cycles — 감지 사이클 로그
CREATE TABLE detection_cycles ( cycle_id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY, started_at TIMESTAMP NOT NULL, completed_at TIMESTAMP, rules_evaluated NUMBER DEFAULT 0, anomalies_found NUMBER DEFAULT 0, duration_ms NUMBER );

build_circle Tool Studio 테이블

8. ds_connections

table_chart ds_connections — DB 접속 관리
CREATE TABLE ds_connections ( id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY, name VARCHAR2(100) NOT NULL UNIQUE, type VARCHAR2(20) NOT NULL, -- sqlite / oracle sqlite_path VARCHAR2(500), host VARCHAR2(200), port NUMBER, service_name VARCHAR2(200), username VARCHAR2(100), password VARCHAR2(200), addresses CLOB, -- JSON: Oracle 다중 주소 (TNS ADDRESS_LIST) created_at TIMESTAMP DEFAULT SYSTIMESTAMP );

9. ds_tools

table_chart ds_tools — 도구 정의
CREATE TABLE ds_tools ( id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY, name VARCHAR2(100) NOT NULL UNIQUE, description CLOB, connection_id NUMBER REFERENCES ds_connections(id), sql_query CLOB NOT NULL, parameters CLOB, -- JSON array columns CLOB, -- JSON array rate_limit NUMBER DEFAULT 60, cache_ttl NUMBER DEFAULT 0, enabled NUMBER(1) DEFAULT 1, tag VARCHAR2(100), version NUMBER DEFAULT 1, updated_by VARCHAR2(100), updated_at TIMESTAMP DEFAULT SYSTIMESTAMP, created_at TIMESTAMP DEFAULT SYSTIMESTAMP );

12. ds_query_log

table_chart ds_query_log — 쿼리 실행 로그
CREATE TABLE ds_query_log ( id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY, tool_name VARCHAR2(100) NOT NULL, params CLOB, row_count NUMBER, duration_ms NUMBER, cached NUMBER(1) DEFAULT 0, error CLOB, caller VARCHAR2(50) DEFAULT '', created_at TIMESTAMP DEFAULT SYSTIMESTAMP );

route 워크플로우 테이블

13. workflows

table_chart workflows
CREATE TABLE workflows ( id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY, name VARCHAR2(200) NOT NULL, description CLOB, graph_data CLOB, -- JSON (nodes + edges) created_by VARCHAR2(100), updated_by VARCHAR2(100), created_at TIMESTAMP DEFAULT SYSTIMESTAMP, updated_at TIMESTAMP DEFAULT SYSTIMESTAMP );

14. workflow_runs

table_chart workflow_runs — 실행 이력
CREATE TABLE workflow_runs ( id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY, workflow_id NUMBER NOT NULL REFERENCES workflows(id), status VARCHAR2(30) DEFAULT 'running', result CLOB, started_at TIMESTAMP DEFAULT SYSTIMESTAMP, completed_at TIMESTAMP, duration_ms NUMBER );

16. kb_documents

table_chart kb_documents — Knowledge Base 문서
CREATE TABLE kb_documents ( id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY, title VARCHAR2(200) NOT NULL, content CLOB, category VARCHAR2(100), tags VARCHAR2(500), author VARCHAR2(100), chunk_count NUMBER DEFAULT 0, topic VARCHAR2(200) DEFAULT '', summary CLOB DEFAULT '', keywords VARCHAR2(500) DEFAULT '', structuring_trace CLOB DEFAULT '', structuring_mode VARCHAR2(20) DEFAULT 'preserve', created_at TIMESTAMP DEFAULT SYSTIMESTAMP, updated_at TIMESTAMP DEFAULT SYSTIMESTAMP );

chat AI Chat 테이블

20. ai_chat_sessions

table_chart ai_chat_sessions
CREATE TABLE ai_chat_sessions ( id VARCHAR2(36) PRIMARY KEY, title VARCHAR2(200) NOT NULL, author VARCHAR2(100), status VARCHAR2(20) DEFAULT 'active', created_at TIMESTAMP DEFAULT SYSTIMESTAMP, updated_at TIMESTAMP DEFAULT SYSTIMESTAMP );

21. ai_chat_messages

table_chart ai_chat_messages
CREATE TABLE ai_chat_messages ( id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY, session_id VARCHAR2(36) REFERENCES ai_chat_sessions(id), role VARCHAR2(20) NOT NULL, content CLOB NOT NULL, tool_calls CLOB, created_at TIMESTAMP DEFAULT SYSTIMESTAMP );

extension AI Chat 확장 + 시스템 테이블

23. question_flows

table_chart question_flows — 후속질문 흐름
CREATE TABLE question_flows ( id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY, name VARCHAR2(200) NOT NULL, description VARCHAR2(500), category VARCHAR2(50), steps CLOB NOT NULL, -- JSON: [{"question":"...", "keywords":["..."]}] enabled NUMBER(1) DEFAULT 1, created_by VARCHAR2(100), created_at TIMESTAMP DEFAULT SYSTIMESTAMP, updated_at TIMESTAMP DEFAULT SYSTIMESTAMP ); CREATE INDEX idx_question_flows_enabled ON question_flows(enabled);

18. tool_requests

table_chart tool_requests — 도구 추가 요청
CREATE TABLE tool_requests ( id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY, title VARCHAR2(200) NOT NULL, description CLOB, data_source VARCHAR2(200), requested_by VARCHAR2(100), session_id VARCHAR2(36), status VARCHAR2(20) DEFAULT 'pending', admin_note CLOB, priority NUMBER DEFAULT 2, created_at TIMESTAMP DEFAULT SYSTIMESTAMP, updated_at TIMESTAMP DEFAULT SYSTIMESTAMP ); CREATE INDEX idx_tool_requests_status ON tool_requests(status);

19. tool_request_history

table_chart tool_request_history -- 요청 상태 변경 이력
CREATE TABLE tool_request_history ( id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY, request_id NUMBER NOT NULL, old_status VARCHAR2(20), new_status VARCHAR2(20) NOT NULL, comment CLOB, changed_by VARCHAR2(100), created_at TIMESTAMP DEFAULT SYSTIMESTAMP ); CREATE INDEX idx_tr_history_req ON tool_request_history(request_id);

17. system_settings

table_chart system_settings — 시스템 하이퍼파라미터
CREATE TABLE system_settings ( key VARCHAR2(100) PRIMARY KEY, value VARCHAR2(500) NOT NULL, category VARCHAR2(50) NOT NULL, label VARCHAR2(200) DEFAULT '', data_type VARCHAR2(20) DEFAULT 'int', min_val NUMBER, max_val NUMBER, updated_at TIMESTAMP DEFAULT SYSTIMESTAMP, updated_by VARCHAR2(100) DEFAULT '' );

22. activity_log

table_chart activity_log — 사용자 활동 로그
CREATE TABLE activity_log ( id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY, user_id NUMBER, username VARCHAR2(50), action VARCHAR2(50) NOT NULL, -- login/logout/create/update/delete/execute target_type VARCHAR2(50), -- user/anomaly/rule/tool/workflow/knowledge/rca/setting/permission target_id VARCHAR2(100), target_name VARCHAR2(200), details CLOB, -- JSON ip_address VARCHAR2(50), page VARCHAR2(50), created_at TIMESTAMP DEFAULT SYSTIMESTAMP ); CREATE INDEX idx_activity_log_user ON activity_log(user_id); CREATE INDEX idx_activity_log_action ON activity_log(action); CREATE INDEX idx_activity_log_time ON activity_log(created_at);

menu_book Playbook 테이블

24. countermeasures

table_chart countermeasures -- 대안/조치 플레이북
CREATE TABLE countermeasures ( id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY, title VARCHAR2(200) NOT NULL, category VARCHAR2(50) NOT NULL, subcategory VARCHAR2(100), rule_ids CLOB, severity VARCHAR2(20), description CLOB NOT NULL, action_type VARCHAR2(30) DEFAULT 'manual', action_config CLOB, priority VARCHAR2(20) DEFAULT 'medium', estimated_time VARCHAR2(50), verification CLOB, enabled NUMBER(1) DEFAULT 1, created_at TIMESTAMP DEFAULT SYSTIMESTAMP, updated_at TIMESTAMP DEFAULT SYSTIMESTAMP, created_by VARCHAR2(50), updated_by VARCHAR2(50) ); CREATE INDEX idx_cm_category ON countermeasures(category);

edit_note 사용자 프롬프트 테이블

25. user_prompts

table_chart user_prompts -- 사용자 개인 프롬프트
CREATE TABLE user_prompts ( id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY, user_id NUMBER NOT NULL, username VARCHAR2(100) NOT NULL, title VARCHAR2(100) NOT NULL DEFAULT '내 프롬프트', content CLOB DEFAULT '', enabled NUMBER(1) DEFAULT 1, version NUMBER DEFAULT 1, created_at TIMESTAMP DEFAULT SYSTIMESTAMP, updated_at TIMESTAMP DEFAULT SYSTIMESTAMP, CONSTRAINT uq_user_prompts_user UNIQUE (user_id) ); CREATE INDEX idx_user_prompts_user ON user_prompts(user_id);

26. user_prompt_versions

table_chart user_prompt_versions -- 프롬프트 버전 이력
CREATE TABLE user_prompt_versions ( id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY, prompt_id NUMBER NOT NULL REFERENCES user_prompts(id), version NUMBER NOT NULL, title VARCHAR2(100) NOT NULL, content CLOB NOT NULL, change_note VARCHAR2(500) DEFAULT '', created_by VARCHAR2(100) NOT NULL, created_at TIMESTAMP DEFAULT SYSTIMESTAMP ); CREATE INDEX idx_user_prompt_versions_prompt ON user_prompt_versions(prompt_id);

speed 인덱스

테이블 생성 후 아래 인덱스를 실행하세요.

table_chart CREATE INDEX 모음
-- LLM 사용량 CREATE INDEX idx_llm_usage_time ON llm_usage_log(called_at); CREATE INDEX idx_llm_usage_module ON llm_usage_log(module); -- 규칙 변경 이력 CREATE INDEX idx_audit_rule ON rule_audit_log(rule_id); CREATE INDEX idx_audit_time ON rule_audit_log(changed_at); -- 이상 CREATE INDEX idx_anomalies_status ON anomalies(status); CREATE INDEX idx_anomalies_detected ON anomalies(detected_at); CREATE INDEX idx_anomalies_rule ON anomalies(rule_id); CREATE INDEX idx_anomalies_correlation ON anomalies(correlation_id); -- 워크플로우 실행 CREATE INDEX idx_wf_runs_wf ON workflow_runs(workflow_id); CREATE INDEX idx_wf_runs_time ON workflow_runs(started_at); -- Knowledge Base CREATE INDEX idx_kb_docs_category ON kb_documents(category); -- Tool Studio 감사 CREATE INDEX idx_tool_audit_tool ON tool_audit_log(tool_id); CREATE INDEX idx_tool_audit_time ON tool_audit_log(changed_at); CREATE INDEX idx_tool_ver_tool ON tool_versions(tool_id); -- 워크플로우 감사 CREATE INDEX idx_wf_audit_wf ON workflow_audit_log(workflow_id); CREATE INDEX idx_wf_audit_time ON workflow_audit_log(changed_at); -- AI Chat CREATE INDEX idx_ai_chat_sessions_status ON ai_chat_sessions(status); CREATE INDEX idx_ai_chat_messages_session ON ai_chat_messages(session_id); -- 도구 요청 CREATE INDEX idx_tool_requests_status ON tool_requests(status); CREATE INDEX idx_tr_history_req ON tool_request_history(request_id); -- 질문 흐름 CREATE INDEX idx_question_flows_enabled ON question_flows(enabled); -- Playbook / 사용자 프롬프트 CREATE INDEX idx_cm_category ON countermeasures(category); CREATE INDEX idx_user_prompts_user ON user_prompts(user_id); CREATE INDEX idx_user_prompt_versions_prompt ON user_prompt_versions(prompt_id);

playlist_add 기본 데이터

최초 설치 후 필수 INSERT문입니다.

admin 계정

table_chart admin 계정 생성
-- 비밀번호는 bcrypt 해시로 교체하세요 INSERT INTO users (username, password, display_name, role, enabled) VALUES ('admin', '$2b$12$...', '관리자', 'admin', 1);