final project

Tables (15)

budgets (월별 전체 예산)

  • budget_id (예산 ID) — bigint — Primary key — NOT NULL
  • year_month (날짜) — char
  • total_budget (월 총 예산) — numeric
  • created_at (생성 시각) — timestamp
  • updated_at (수정 시각) — timestamp
  • user_id (유저 고유 ID) — bigint — Foreign key → users.user_id — NOT NULL

cards (카드 상품 정보)

  • card_id (카드 ID) — bigint — Primary key — NOT NULL
  • name (카드명) — varchar
  • company (카드사) — varchar
  • benefits_json (혜택 구조) — jsonb
  • tags (태그) — varchar[]

categories (소비 카테고리)

  • category_id (카테고리 ID) — bigint — Primary key — NOT NULL
  • name (식비 / 교통 / 쇼핑) — varchar
  • parent_id (상위 카테고리 ID) — bigint — Foreign key → categories.category_id — NOT NULL

category_budgets (카테고리별 예산)

  • cat_budget_id (ID) — bigint — Primary key — NOT NULL
  • year_month (날짜) — char
  • budget_amount (카테고리 예산) — numeric
  • created_at (생성 시각) — timestamp
  • user_id (유저 고유 ID) — bigint — Foreign key → users.user_id — NOT NULL
  • category_id (카테고리 ID) — bigint — Foreign key → categories.category_id — NOT NULL

category_predictions (AI 카테고리 분류 이력)

  • pred_id (예측 ID) — bigint — Primary key — NOT NULL
  • model_name (모델 이름) — varchar
  • model_version (버전) — varchar
  • confidence (신뢰도) — numeric
  • corrected (수정 여부) — boolean
  • created_at (예측 시각) — timestamp
  • tx_id (거래 고유 ID) — bigint — Foreign key → transactions.tx_id — NOT NULL
  • predicted_category_id (AI 예측) — bigint — Foreign key → categories.category_id — NOT NULL
  • user_final_category_id (최종 선택) — bigint — Foreign key → categories.category_id — NOT NULL

goal_transactions (목표 기여 이력)

  • goal_tx_id (ID) — bigint — Primary key — NOT NULL
  • amount (기여 금액) — numeric
  • goal_id (목표 ID) — bigint — Foreign key → saving_goals.goal_id — NOT NULL
  • tx_id (거래 고유 ID) — bigint — Foreign key → transactions.tx_id — NOT NULL

monthly_reports (월별 분석 결과)

  • report_id (리포트 ID) — bigint — Primary key — NOT NULL
  • year_month (날짜) — char
  • metrics_json (통계 지표) — jsonb
  • llm_summary_text (요약 문장) — text
  • llm_model (사용 모델) — varchar
  • created_at (생성 시각)
  • user_id (유저 고유 ID) — bigint — Foreign key → users.user_id — NOT NULL

payment_methods (결제 수단)

  • method_id (결제수단 ID) — bigint — Primary key — NOT NULL
  • name (카드 / 현금 / 계좌 / 페이) — varchar

products (금융 상품 정보)

  • product_id (상품 ID) — bigint — Primary key — NOT NULL
  • type (예금/적금) — varchar
  • name (상품명) — varchar
  • bank (은행) — varchar
  • rate (금리) — numeric
  • conditions_json (조건) — jsonb
  • tags (태그) — varchar[]

receipt_files (영수증 이미지)

  • file_id (파일 ID) — bigint — Primary key — NOT NULL
  • url_path (S3 / 로컬경로) — text
  • ocr_raw_json (OCR 추출 결과) — jsonb
  • created_at (업로드 시각) — timestamp
  • user_id (유저 고유 ID) — bigint — Foreign key → users.user_id — NOT NULL
  • tx_id (거래 고유 ID) — bigint — Foreign key → transactions.tx_id — NOT NULL

recommendations (추천 결과 이력)

  • rec_id (추천 ID) — bigint — Primary key — NOT NULL
  • year_month (기준 월) — char
  • rec_type (Card / Product) — varchar
  • item_id (대상 ID) — bigint
  • score (점수) — numeric
  • reason_text (추천 이유) — text
  • created_at (생성 시각) — timestamp
  • user_id (유저 고유 ID) — bigint — Foreign key → users.user_id — NOT NULL

saving_goals (저축 목표)

  • goal_id (목표 ID) — bigint — Primary key — NOT NULL
  • goal_amount (목표 금액) — numeric
  • start_date (시작일) — date
  • target_date (목표일) — date
  • monthly_target (월 목표) — numeric
  • created_at (생성 시각) — timestamp
  • user_id (유저 고유 ID) — bigint — Foreign key → users.user_id — NOT NULL

transactions (지출 데이터)

  • tx_id (거래 고유 ID) — bigint — Primary key — NOT NULL
  • tx_datetime (결제 시각) — timestamp
  • amount (사용 금액) — numeric
  • merchant (가맹점명) — varchar
  • memo (사용자 메모) — text
  • source (OCR / TEXT / API) — varchar
  • created_at (저장 시각) — timestamp
  • user_id (유저 고유 ID) — bigint — Foreign key → users.user_id — NOT NULL
  • method_id (결제수단 ID) — bigint — Foreign key → payment_methods.method_id — NOT NULL
  • category_id (카테고리 ID) — bigint — Foreign key → categories.category_id — NOT NULL

user_cards (사용자 보유 카드)

  • user_card_id (ID) — bigint — Primary key — NOT NULL
  • user_id (유저 고유 ID) — bigint — Foreign key → users.user_id — NOT NULL
  • card_id (카드 ID) — bigint — Foreign key → cards.card_id — NOT NULL

users (유저)

  • user_id (유저 고유 ID) — bigint — Primary key — NOT NULL
  • email (이메일) — varchar
  • password_hash (비밀번호) — varchar
  • created_at (회원가입 시각) — timestamp