fd5a68e44a
✨ 주요 변경사항: - 프로젝트 이름: kumamoto-travel-planner → travel-planner - 버전 업그레이드: v1.0.0 → v2.0.0 - 멀티유저 시스템 구현 (JWT 인증) - PostgreSQL 마이그레이션 시스템 추가 - Docker 컨테이너 이름 변경 - UI 브랜딩 업데이트 (Travel Planner) - API 서버 및 인증 시스템 추가 - 여행 공유 기능 구현 - 템플릿 시스템 추가 🔧 기술 스택: - Frontend: React + TypeScript + Vite - Backend: Node.js + Express + JWT - Database: PostgreSQL + 마이그레이션 - Infrastructure: Docker + Docker Compose 🌟 새로운 기능: - 사용자 인증 및 권한 관리 - 다중 여행 계획 관리 - 여행 템플릿 시스템 - 공유 링크 및 댓글 시스템 - 관리자 대시보드
227 lines
8.6 KiB
PL/PgSQL
227 lines
8.6 KiB
PL/PgSQL
-- Travel Planner v2.0 Database Schema
|
|
-- 멀티 사용자 및 여행 관리 시스템
|
|
|
|
-- 사용자 테이블
|
|
CREATE TABLE IF NOT EXISTS users (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
email VARCHAR(255) UNIQUE NOT NULL,
|
|
password_hash VARCHAR(255) NOT NULL,
|
|
name VARCHAR(255) NOT NULL,
|
|
role VARCHAR(20) DEFAULT 'user' CHECK (role IN ('admin', 'user')),
|
|
is_active BOOLEAN DEFAULT true,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
last_login TIMESTAMP,
|
|
created_by UUID REFERENCES users(id)
|
|
);
|
|
|
|
-- 여행 계획 테이블 (확장)
|
|
CREATE TABLE IF NOT EXISTS travel_plans (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
|
title VARCHAR(255) NOT NULL,
|
|
description TEXT,
|
|
|
|
-- 목적지 정보
|
|
destination_country VARCHAR(100) NOT NULL,
|
|
destination_city VARCHAR(100) NOT NULL,
|
|
destination_region VARCHAR(100),
|
|
destination_lat DECIMAL(10, 7),
|
|
destination_lng DECIMAL(10, 7),
|
|
|
|
-- 날짜
|
|
start_date DATE NOT NULL,
|
|
end_date DATE NOT NULL,
|
|
|
|
-- 메타데이터
|
|
is_public BOOLEAN DEFAULT false,
|
|
is_template BOOLEAN DEFAULT false,
|
|
template_category VARCHAR(20) CHECK (template_category IN ('japan', 'korea', 'asia', 'europe', 'america', 'other')),
|
|
tags TEXT[] DEFAULT '{}',
|
|
thumbnail VARCHAR(500),
|
|
status VARCHAR(20) DEFAULT 'draft' CHECK (status IN ('draft', 'active', 'completed', 'cancelled')),
|
|
|
|
-- 타임스탬프
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
-- 날짜별 일정 테이블
|
|
CREATE TABLE IF NOT EXISTS day_schedules (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
travel_plan_id UUID NOT NULL REFERENCES travel_plans(id) ON DELETE CASCADE,
|
|
schedule_date DATE NOT NULL,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
-- 활동 테이블 (확장)
|
|
CREATE TABLE IF NOT EXISTS activities (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
day_schedule_id UUID NOT NULL REFERENCES day_schedules(id) ON DELETE CASCADE,
|
|
time VARCHAR(10) NOT NULL,
|
|
title VARCHAR(255) NOT NULL,
|
|
description TEXT,
|
|
location VARCHAR(255),
|
|
type VARCHAR(50) NOT NULL CHECK (type IN ('attraction', 'food', 'accommodation', 'transport', 'other')),
|
|
|
|
-- 좌표
|
|
lat DECIMAL(10, 7),
|
|
lng DECIMAL(10, 7),
|
|
|
|
-- 미디어
|
|
images TEXT[] DEFAULT '{}',
|
|
links JSONB DEFAULT '[]',
|
|
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
-- 관련 장소 테이블
|
|
CREATE TABLE IF NOT EXISTS related_places (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
activity_id UUID NOT NULL REFERENCES activities(id) ON DELETE CASCADE,
|
|
name VARCHAR(255) NOT NULL,
|
|
description TEXT,
|
|
address VARCHAR(255),
|
|
lat DECIMAL(10, 7),
|
|
lng DECIMAL(10, 7),
|
|
memo TEXT,
|
|
will_visit BOOLEAN DEFAULT false,
|
|
category VARCHAR(50) DEFAULT 'other' CHECK (category IN ('restaurant', 'attraction', 'shopping', 'accommodation', 'other')),
|
|
images TEXT[] DEFAULT '{}',
|
|
links JSONB DEFAULT '[]',
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
-- 기본 포인트 테이블
|
|
CREATE TABLE IF NOT EXISTS base_points (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
|
name VARCHAR(255) NOT NULL,
|
|
address VARCHAR(255),
|
|
type VARCHAR(50) NOT NULL CHECK (type IN ('accommodation', 'airport', 'station', 'parking', 'other')),
|
|
lat DECIMAL(10, 7) NOT NULL,
|
|
lng DECIMAL(10, 7) NOT NULL,
|
|
memo TEXT,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
-- 예산 테이블
|
|
CREATE TABLE IF NOT EXISTS budgets (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
travel_plan_id UUID NOT NULL REFERENCES travel_plans(id) ON DELETE CASCADE,
|
|
total_amount DECIMAL(12, 2) DEFAULT 0,
|
|
accommodation DECIMAL(12, 2) DEFAULT 0,
|
|
food DECIMAL(12, 2) DEFAULT 0,
|
|
transportation DECIMAL(12, 2) DEFAULT 0,
|
|
shopping DECIMAL(12, 2) DEFAULT 0,
|
|
activities DECIMAL(12, 2) DEFAULT 0,
|
|
currency VARCHAR(3) DEFAULT 'KRW',
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
-- 체크리스트 테이블
|
|
CREATE TABLE IF NOT EXISTS checklist_items (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
travel_plan_id UUID NOT NULL REFERENCES travel_plans(id) ON DELETE CASCADE,
|
|
text VARCHAR(500) NOT NULL,
|
|
checked BOOLEAN DEFAULT false,
|
|
category VARCHAR(50) DEFAULT 'other' CHECK (category IN ('preparation', 'shopping', 'visit', 'other')),
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
-- 공유 링크 테이블
|
|
CREATE TABLE IF NOT EXISTS share_links (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
trip_id UUID NOT NULL REFERENCES travel_plans(id) ON DELETE CASCADE,
|
|
created_by UUID NOT NULL REFERENCES users(id),
|
|
share_code VARCHAR(8) UNIQUE NOT NULL,
|
|
expires_at TIMESTAMP,
|
|
is_active BOOLEAN DEFAULT true,
|
|
access_count INTEGER DEFAULT 0,
|
|
max_access_count INTEGER,
|
|
|
|
-- 권한
|
|
can_view BOOLEAN DEFAULT true,
|
|
can_edit BOOLEAN DEFAULT false,
|
|
can_comment BOOLEAN DEFAULT false,
|
|
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
last_accessed TIMESTAMP
|
|
);
|
|
|
|
-- 댓글 테이블
|
|
CREATE TABLE IF NOT EXISTS trip_comments (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
trip_id UUID NOT NULL REFERENCES travel_plans(id) ON DELETE CASCADE,
|
|
user_id UUID NOT NULL REFERENCES users(id),
|
|
content TEXT NOT NULL,
|
|
is_edited BOOLEAN DEFAULT false,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
-- 시스템 설정 테이블
|
|
CREATE TABLE IF NOT EXISTS system_settings (
|
|
key VARCHAR(100) PRIMARY KEY,
|
|
value TEXT,
|
|
description TEXT,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
-- 인덱스 생성
|
|
CREATE INDEX IF NOT EXISTS idx_users_email ON users(email);
|
|
CREATE INDEX IF NOT EXISTS idx_users_role ON users(role);
|
|
CREATE INDEX IF NOT EXISTS idx_travel_plans_user ON travel_plans(user_id);
|
|
CREATE INDEX IF NOT EXISTS idx_travel_plans_status ON travel_plans(status);
|
|
CREATE INDEX IF NOT EXISTS idx_travel_plans_category ON travel_plans(template_category);
|
|
CREATE INDEX IF NOT EXISTS idx_travel_plans_public ON travel_plans(is_public);
|
|
CREATE INDEX IF NOT EXISTS idx_day_schedules_plan ON day_schedules(travel_plan_id);
|
|
CREATE INDEX IF NOT EXISTS idx_activities_schedule ON activities(day_schedule_id);
|
|
CREATE INDEX IF NOT EXISTS idx_related_places_activity ON related_places(activity_id);
|
|
CREATE INDEX IF NOT EXISTS idx_base_points_user ON base_points(user_id);
|
|
CREATE INDEX IF NOT EXISTS idx_budgets_plan ON budgets(travel_plan_id);
|
|
CREATE INDEX IF NOT EXISTS idx_checklist_plan ON checklist_items(travel_plan_id);
|
|
CREATE INDEX IF NOT EXISTS idx_share_links_code ON share_links(share_code);
|
|
CREATE INDEX IF NOT EXISTS idx_share_links_trip ON share_links(trip_id);
|
|
CREATE INDEX IF NOT EXISTS idx_comments_trip ON trip_comments(trip_id);
|
|
|
|
-- 초기 시스템 설정 데이터
|
|
INSERT INTO system_settings (key, value, description) VALUES
|
|
('app_version', '2.0.0', '애플리케이션 버전'),
|
|
('setup_completed', 'false', '초기 설정 완료 여부'),
|
|
('jwt_secret_set', 'false', 'JWT 시크릿 설정 여부'),
|
|
('google_maps_configured', 'false', 'Google Maps API 설정 여부'),
|
|
('email_configured', 'false', '이메일 설정 여부')
|
|
ON CONFLICT (key) DO NOTHING;
|
|
|
|
-- 업데이트 트리거 함수
|
|
CREATE OR REPLACE FUNCTION update_updated_at_column()
|
|
RETURNS TRIGGER AS $$
|
|
BEGIN
|
|
NEW.updated_at = CURRENT_TIMESTAMP;
|
|
RETURN NEW;
|
|
END;
|
|
$$ language 'plpgsql';
|
|
|
|
-- 업데이트 트리거 적용
|
|
DROP TRIGGER IF EXISTS update_users_updated_at ON users;
|
|
CREATE TRIGGER update_users_updated_at BEFORE UPDATE ON users FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
|
|
|
|
DROP TRIGGER IF EXISTS update_travel_plans_updated_at ON travel_plans;
|
|
CREATE TRIGGER update_travel_plans_updated_at BEFORE UPDATE ON travel_plans FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
|
|
|
|
DROP TRIGGER IF EXISTS update_budgets_updated_at ON budgets;
|
|
CREATE TRIGGER update_budgets_updated_at BEFORE UPDATE ON budgets FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
|
|
|
|
DROP TRIGGER IF EXISTS update_checklist_updated_at ON checklist_items;
|
|
CREATE TRIGGER update_checklist_updated_at BEFORE UPDATE ON checklist_items FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
|
|
|
|
DROP TRIGGER IF EXISTS update_comments_updated_at ON trip_comments;
|
|
CREATE TRIGGER update_comments_updated_at BEFORE UPDATE ON trip_comments FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
|
|
|
|
DROP TRIGGER IF EXISTS update_settings_updated_at ON system_settings;
|
|
CREATE TRIGGER update_settings_updated_at BEFORE UPDATE ON system_settings FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
|