273 lines
10 KiB
PL/PgSQL
273 lines
10 KiB
PL/PgSQL
CREATE TABLE IF NOT EXISTS lectures (
|
|
id UUID PRIMARY KEY,
|
|
type VARCHAR(20) NOT NULL CHECK (type IN ('youtube', 'ppt', 'news', 'link', 'video')),
|
|
title TEXT NOT NULL,
|
|
description TEXT NOT NULL DEFAULT '',
|
|
tags TEXT[] NOT NULL DEFAULT '{}',
|
|
youtube_url TEXT,
|
|
file_name TEXT,
|
|
original_name TEXT,
|
|
preview_title TEXT,
|
|
slide_count INTEGER NOT NULL DEFAULT 0,
|
|
thumbnail_url TEXT,
|
|
thumbnail_status VARCHAR(20) NOT NULL DEFAULT 'pending',
|
|
thumbnail_retry_count INTEGER NOT NULL DEFAULT 0,
|
|
thumbnail_error TEXT,
|
|
thumbnail_updated_at TIMESTAMPTZ,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
list_section VARCHAR(20) NOT NULL DEFAULT 'learning',
|
|
news_url TEXT
|
|
);
|
|
|
|
ALTER TABLE lectures DROP CONSTRAINT IF EXISTS lectures_type_check;
|
|
ALTER TABLE lectures ADD CONSTRAINT lectures_type_check CHECK (type IN ('youtube', 'ppt', 'news', 'link', 'video'));
|
|
ALTER TABLE lectures ADD COLUMN IF NOT EXISTS list_section VARCHAR(20) NOT NULL DEFAULT 'learning';
|
|
ALTER TABLE lectures ADD COLUMN IF NOT EXISTS news_url TEXT;
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_lectures_type_created_at ON lectures (type, created_at DESC);
|
|
CREATE INDEX IF NOT EXISTS idx_lectures_created_at ON lectures (created_at DESC);
|
|
CREATE INDEX IF NOT EXISTS idx_lectures_tags ON lectures USING GIN (tags);
|
|
|
|
CREATE OR REPLACE FUNCTION set_lectures_updated_at()
|
|
RETURNS TRIGGER AS $$
|
|
BEGIN
|
|
NEW.updated_at = NOW();
|
|
RETURN NEW;
|
|
END;
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
DROP TRIGGER IF EXISTS trg_lectures_updated_at ON lectures;
|
|
CREATE TRIGGER trg_lectures_updated_at
|
|
BEFORE UPDATE ON lectures
|
|
FOR EACH ROW
|
|
EXECUTE FUNCTION set_lectures_updated_at();
|
|
|
|
-- AX 과제 신청 (PDF 양식 기반)
|
|
CREATE TABLE IF NOT EXISTS ax_assignments (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
department VARCHAR(200) NOT NULL,
|
|
name VARCHAR(100) NOT NULL,
|
|
employee_id VARCHAR(50),
|
|
position VARCHAR(100),
|
|
phone VARCHAR(50),
|
|
email VARCHAR(200),
|
|
work_process_description TEXT,
|
|
pain_point TEXT,
|
|
current_time_spent VARCHAR(100),
|
|
error_rate_before VARCHAR(100),
|
|
collaboration_depts TEXT,
|
|
reason_to_solve TEXT,
|
|
ai_expectation TEXT,
|
|
output_type TEXT,
|
|
automation_level VARCHAR(50),
|
|
data_readiness VARCHAR(50),
|
|
data_location TEXT,
|
|
personal_info VARCHAR(50),
|
|
data_quality VARCHAR(50),
|
|
data_count VARCHAR(100),
|
|
data_types TEXT[],
|
|
time_reduction VARCHAR(100),
|
|
error_reduction VARCHAR(100),
|
|
volume_increase VARCHAR(100),
|
|
cost_reduction VARCHAR(100),
|
|
response_time VARCHAR(100),
|
|
other_metrics TEXT,
|
|
annual_savings VARCHAR(100),
|
|
labor_replacement VARCHAR(100),
|
|
revenue_increase VARCHAR(100),
|
|
other_effects TEXT,
|
|
qualitative_effects TEXT[],
|
|
tech_stack TEXT[],
|
|
risks TEXT[],
|
|
risk_detail TEXT,
|
|
participation_pledge BOOLEAN DEFAULT false,
|
|
application_file TEXT,
|
|
status VARCHAR(20) NOT NULL DEFAULT '신청',
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
|
);
|
|
|
|
ALTER TABLE ax_assignments ADD COLUMN IF NOT EXISTS application_file TEXT;
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_ax_assignments_created_at ON ax_assignments (created_at DESC);
|
|
|
|
CREATE OR REPLACE FUNCTION set_ax_assignments_updated_at()
|
|
RETURNS TRIGGER AS $$
|
|
BEGIN
|
|
NEW.updated_at = NOW();
|
|
RETURN NEW;
|
|
END;
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
DROP TRIGGER IF EXISTS trg_ax_assignments_updated_at ON ax_assignments;
|
|
CREATE TRIGGER trg_ax_assignments_updated_at
|
|
BEFORE UPDATE ON ax_assignments
|
|
FOR EACH ROW
|
|
EXECUTE FUNCTION set_ax_assignments_updated_at();
|
|
|
|
-- OPS 이메일(@xavis.co.kr) 매직 링크 인증 — 이벤트 감사 로그
|
|
CREATE TABLE IF NOT EXISTS ops_email_auth_events (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
email VARCHAR(320) NOT NULL,
|
|
event_type VARCHAR(40) NOT NULL CHECK (event_type IN ('magic_link_requested', 'login_success', 'logout')),
|
|
ip_address VARCHAR(45),
|
|
user_agent TEXT,
|
|
return_to TEXT,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_ops_email_auth_events_email_created ON ops_email_auth_events (email, created_at DESC);
|
|
CREATE INDEX IF NOT EXISTS idx_ops_email_auth_events_created ON ops_email_auth_events (created_at DESC);
|
|
CREATE INDEX IF NOT EXISTS idx_ops_email_auth_events_event_type ON ops_email_auth_events (event_type);
|
|
|
|
-- 이메일별 최초·최근 로그인 및 누적 로그인 횟수
|
|
CREATE TABLE IF NOT EXISTS ops_email_users (
|
|
email VARCHAR(320) PRIMARY KEY,
|
|
first_seen_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
last_login_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
login_count INTEGER NOT NULL DEFAULT 0
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_ops_email_users_last_login ON ops_email_users (last_login_at DESC);
|
|
|
|
-- 회의록 AI: 이메일(OPS 세션) 기반 사용자·프롬프트·회의 저장
|
|
CREATE TABLE IF NOT EXISTS meeting_ai_users (
|
|
email VARCHAR(320) PRIMARY KEY,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS meeting_ai_prompts (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
user_email VARCHAR(320) NOT NULL REFERENCES meeting_ai_users(email) ON DELETE CASCADE,
|
|
include_title_line BOOLEAN NOT NULL DEFAULT true,
|
|
include_attendees BOOLEAN NOT NULL DEFAULT true,
|
|
include_summary BOOLEAN NOT NULL DEFAULT true,
|
|
include_action_items BOOLEAN NOT NULL DEFAULT true,
|
|
include_checklist BOOLEAN NOT NULL DEFAULT true,
|
|
custom_instructions TEXT,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
CONSTRAINT uq_meeting_ai_prompts_user UNIQUE (user_email)
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_meeting_ai_prompts_user ON meeting_ai_prompts (user_email);
|
|
|
|
-- 기존 DB: 신규 행 기본값만 갱신(이미 저장된 include_checklist 값은 유지)
|
|
ALTER TABLE meeting_ai_prompts ALTER COLUMN include_checklist SET DEFAULT true;
|
|
|
|
CREATE TABLE IF NOT EXISTS meeting_ai_meetings (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
user_email VARCHAR(320) NOT NULL REFERENCES meeting_ai_users(email) ON DELETE CASCADE,
|
|
title VARCHAR(500) NOT NULL DEFAULT '',
|
|
source_text TEXT,
|
|
transcript_text TEXT,
|
|
generated_minutes TEXT,
|
|
summary_text TEXT,
|
|
audio_file_path TEXT,
|
|
audio_original_name TEXT,
|
|
chat_model VARCHAR(80) NOT NULL DEFAULT 'gpt-5-mini',
|
|
transcription_model VARCHAR(80),
|
|
meeting_date DATE,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_meeting_ai_meetings_user_created ON meeting_ai_meetings (user_email, created_at DESC);
|
|
|
|
-- 기존 DB 마이그레이션: 전사에 사용한 OpenAI 모델(gpt-4o-mini-transcribe, gpt-4o-transcribe 등)
|
|
ALTER TABLE meeting_ai_meetings ADD COLUMN IF NOT EXISTS transcription_model VARCHAR(80);
|
|
|
|
-- 미팅 일자(회의가 열린 날짜, 선택)
|
|
ALTER TABLE meeting_ai_meetings ADD COLUMN IF NOT EXISTS meeting_date DATE;
|
|
|
|
-- 회의록 생성 후 체크리스트 자동 추출(JSON 스냅샷, 업무 체크리스트 AI 연동)
|
|
ALTER TABLE meeting_ai_meetings ADD COLUMN IF NOT EXISTS checklist_snapshot JSONB;
|
|
|
|
-- 업무 체크리스트 툴팁용 짧은 요약(생성 시 회의록 본문에서 추출해 저장, 없으면 조회 시 추출)
|
|
ALTER TABLE meeting_ai_meetings ADD COLUMN IF NOT EXISTS summary_text TEXT;
|
|
|
|
CREATE OR REPLACE FUNCTION set_meeting_ai_prompts_updated_at()
|
|
RETURNS TRIGGER AS $$
|
|
BEGIN
|
|
NEW.updated_at = NOW();
|
|
RETURN NEW;
|
|
END;
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
DROP TRIGGER IF EXISTS trg_meeting_ai_prompts_updated_at ON meeting_ai_prompts;
|
|
CREATE TRIGGER trg_meeting_ai_prompts_updated_at
|
|
BEFORE UPDATE ON meeting_ai_prompts
|
|
FOR EACH ROW
|
|
EXECUTE FUNCTION set_meeting_ai_prompts_updated_at();
|
|
|
|
CREATE OR REPLACE FUNCTION set_meeting_ai_meetings_updated_at()
|
|
RETURNS TRIGGER AS $$
|
|
BEGIN
|
|
NEW.updated_at = NOW();
|
|
RETURN NEW;
|
|
END;
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
DROP TRIGGER IF EXISTS trg_meeting_ai_meetings_updated_at ON meeting_ai_meetings;
|
|
CREATE TRIGGER trg_meeting_ai_meetings_updated_at
|
|
BEFORE UPDATE ON meeting_ai_meetings
|
|
FOR EACH ROW
|
|
EXECUTE FUNCTION set_meeting_ai_meetings_updated_at();
|
|
|
|
-- 업무 체크리스트 AI: 사용자별 항목(회의록에서 가져오기 또는 수동)
|
|
CREATE TABLE IF NOT EXISTS meeting_ai_checklist_items (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
user_email VARCHAR(320) NOT NULL REFERENCES meeting_ai_users(email) ON DELETE CASCADE,
|
|
meeting_id UUID REFERENCES meeting_ai_meetings(id) ON DELETE SET NULL,
|
|
title TEXT NOT NULL,
|
|
detail TEXT,
|
|
assignee VARCHAR(300),
|
|
due_note VARCHAR(300),
|
|
completed BOOLEAN NOT NULL DEFAULT false,
|
|
completed_at TIMESTAMPTZ,
|
|
completion_note TEXT,
|
|
sort_order INT NOT NULL DEFAULT 0,
|
|
source VARCHAR(20) NOT NULL DEFAULT 'imported' CHECK (source IN ('imported', 'manual')),
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
|
);
|
|
|
|
ALTER TABLE meeting_ai_checklist_items ADD COLUMN IF NOT EXISTS completion_note TEXT;
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_meeting_ai_checklist_user_updated ON meeting_ai_checklist_items (user_email, updated_at DESC);
|
|
CREATE INDEX IF NOT EXISTS idx_meeting_ai_checklist_meeting ON meeting_ai_checklist_items (meeting_id);
|
|
|
|
CREATE OR REPLACE FUNCTION set_meeting_ai_checklist_updated_at()
|
|
RETURNS TRIGGER AS $$
|
|
BEGIN
|
|
NEW.updated_at = NOW();
|
|
RETURN NEW;
|
|
END;
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
DROP TRIGGER IF EXISTS trg_meeting_ai_checklist_updated_at ON meeting_ai_checklist_items;
|
|
CREATE TRIGGER trg_meeting_ai_checklist_updated_at
|
|
BEFORE UPDATE ON meeting_ai_checklist_items
|
|
FOR EACH ROW
|
|
EXECUTE FUNCTION set_meeting_ai_checklist_updated_at();
|
|
|
|
-- PPT 썸네일 작업 이벤트 로그 (기존 data/thumbnail-events.json 대체)
|
|
CREATE TABLE IF NOT EXISTS lecture_thumbnail_events (
|
|
id UUID PRIMARY KEY,
|
|
occurred_at TIMESTAMPTZ NOT NULL,
|
|
event_type VARCHAR(40) NOT NULL,
|
|
lecture_id UUID,
|
|
lecture_title TEXT,
|
|
reason VARCHAR(200),
|
|
force_flag BOOLEAN NOT NULL DEFAULT false,
|
|
queue_size_after INTEGER,
|
|
retry_count INTEGER,
|
|
duration_ms INTEGER,
|
|
error_text TEXT
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_lecture_thumbnail_events_occurred ON lecture_thumbnail_events (occurred_at DESC);
|
|
CREATE INDEX IF NOT EXISTS idx_lecture_thumbnail_events_type ON lecture_thumbnail_events (event_type);
|
|
CREATE INDEX IF NOT EXISTS idx_lecture_thumbnail_events_lecture ON lecture_thumbnail_events (lecture_id);
|