51 lines
2.3 KiB
SQL
51 lines
2.3 KiB
SQL
-- migrate-v6.sql: 회고 시스템 (retrospect) 스키마 + 테이블
|
|
-- 실행: docker exec -i bot-postgres psql -U bot -d chatbot < init/migrate-v6.sql
|
|
|
|
CREATE SCHEMA IF NOT EXISTS retrospect;
|
|
|
|
CREATE TABLE IF NOT EXISTS retrospect.entries (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
raw_text TEXT NOT NULL,
|
|
domain VARCHAR(20) NOT NULL, -- work | health | finance
|
|
entry_type VARCHAR(20) NOT NULL, -- event | reflection | regret | log
|
|
sentiment VARCHAR(10) NOT NULL, -- positive | neutral | negative
|
|
tags TEXT[] DEFAULT '{}',
|
|
confidence REAL DEFAULT 0.0,
|
|
source VARCHAR(20) DEFAULT 'chat',
|
|
reviewed BOOLEAN DEFAULT FALSE,
|
|
source_post_id BIGINT,
|
|
username VARCHAR(100)
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_entries_created ON retrospect.entries (created_at DESC);
|
|
CREATE INDEX IF NOT EXISTS idx_entries_domain ON retrospect.entries (domain);
|
|
CREATE INDEX IF NOT EXISTS idx_entries_type ON retrospect.entries (entry_type);
|
|
CREATE INDEX IF NOT EXISTS idx_entries_reviewed ON retrospect.entries (reviewed) WHERE NOT reviewed;
|
|
CREATE INDEX IF NOT EXISTS idx_entries_tags ON retrospect.entries USING GIN(tags);
|
|
-- 중복 방지: chat_bridge 재시작 시 같은 post 재처리 방지
|
|
CREATE UNIQUE INDEX IF NOT EXISTS idx_entries_post_id ON retrospect.entries (source_post_id)
|
|
WHERE source_post_id IS NOT NULL;
|
|
|
|
CREATE TABLE IF NOT EXISTS retrospect.reviews (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
review_type VARCHAR(10) NOT NULL, -- daily | weekly | monthly
|
|
period_start DATE NOT NULL,
|
|
period_end DATE NOT NULL,
|
|
content TEXT NOT NULL,
|
|
entry_ids BIGINT[] DEFAULT '{}',
|
|
model_used VARCHAR(50) NOT NULL
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS retrospect.patterns (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
discovered_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
domain VARCHAR(20) NOT NULL,
|
|
pattern_type VARCHAR(20) NOT NULL, -- recurring_mistake | habit | correlation | improvement
|
|
description TEXT NOT NULL,
|
|
occurrences INTEGER DEFAULT 1,
|
|
last_seen DATE NOT NULL,
|
|
status VARCHAR(20) DEFAULT 'active'
|
|
);
|