erd

Tables (22)

ai_guardrail_logs

  • guardrail_log_id — BIGINT — Primary key — NOT NULL
  • process_log_id — BIGINT — Foreign key → ai_process_logs.process_log_id — NOT NULL
  • policy_name — VARCHAR(120) — NOT NULL
  • result — VARCHAR(40) — NOT NULL
  • reason — TEXT
  • created_at — TIMESTAMP — NOT NULL

ai_process_logs

  • process_log_id — BIGINT — Primary key — NOT NULL
  • application_id — BIGINT — Foreign key → applications.application_id
  • session_id — BIGINT — Foreign key → interview_sessions.session_id
  • process_type — VARCHAR(80) — NOT NULL
  • status — VARCHAR(40) — NOT NULL
  • input_ref — TEXT
  • output_ref — TEXT
  • created_at — TIMESTAMP — NOT NULL

application_documents

  • document_id — BIGINT — Primary key — NOT NULL
  • application_id — BIGINT — Foreign key → applications.application_id — NOT NULL
  • file_id — BIGINT — Foreign key → file_assets.file_id
  • document_type — VARCHAR(50) — NOT NULL
  • parse_status — VARCHAR(40) — NOT NULL
  • extracted_text — TEXT
  • uploaded_at — TIMESTAMP — NOT NULL

applications

  • application_id — BIGINT — Primary key — NOT NULL
  • posting_id — BIGINT — Foreign key → postings.posting_id — NOT NULL
  • candidate_id — BIGINT — Foreign key → candidate_profiles.candidate_id — NOT NULL
  • application_status — VARCHAR(40) — NOT NULL
  • document_status — VARCHAR(40) — NOT NULL
  • interview_status — VARCHAR(40) — NOT NULL
  • report_status — VARCHAR(40) — NOT NULL
  • screening_decision — VARCHAR(40)
  • screening_memo — TEXT
  • submitted_at — TIMESTAMP
  • updated_at — TIMESTAMP — NOT NULL

candidate_profiles

  • candidate_id — BIGINT — Primary key — NOT NULL
  • user_id — BIGINT — Foreign key → users.user_id — NOT NULL
  • default_resume_file_id — BIGINT — Foreign key → file_assets.file_id
  • portfolio_url — VARCHAR(500)
  • github_url — VARCHAR(500)
  • summary — TEXT
  • created_at — TIMESTAMP — NOT NULL
  • updated_at — TIMESTAMP — NOT NULL

companies

  • company_id — BIGINT — Primary key — NOT NULL
  • owner_user_id — BIGINT — Foreign key → users.user_id — NOT NULL
  • name — VARCHAR(150) — NOT NULL
  • business_registration_number — VARCHAR(10) — NOT NULL
  • verification_status — VARCHAR(30) — NOT NULL
  • industry — VARCHAR(100)
  • profile — TEXT
  • talent_profile — TEXT
  • evaluation_policy — TEXT
  • created_at — TIMESTAMP — NOT NULL
  • updated_at — TIMESTAMP — NOT NULL

consent_records

  • consent_id — BIGINT — Primary key — NOT NULL
  • application_id — BIGINT — Foreign key → applications.application_id — NOT NULL
  • consent_type — VARCHAR(80) — NOT NULL
  • agreed — BOOLEAN — NOT NULL
  • agreed_at — TIMESTAMP

criterion_tags

  • tag_id — BIGINT — Primary key — NOT NULL
  • job_role — VARCHAR(100) — NOT NULL
  • name — VARCHAR(100) — NOT NULL
  • description — TEXT
  • category — VARCHAR(80) — NOT NULL
  • is_active — BOOLEAN — NOT NULL
  • sort_order — INTEGER — NOT NULL

embeddings

  • embedding_id — BIGINT — Primary key — NOT NULL
  • posting_id — BIGINT — Foreign key → postings.posting_id
  • tag_id — BIGINT — Foreign key → criterion_tags.tag_id
  • question_id — BIGINT — Foreign key → question_bank.question_id
  • document_id — BIGINT — Foreign key → application_documents.document_id
  • answer_id — BIGINT — Foreign key → interview_answers.answer_id
  • report_id — BIGINT — Foreign key → evaluation_reports.report_id
  • source_type — VARCHAR(80) — NOT NULL
  • source_text_hash — VARCHAR(128) — NOT NULL
  • embedding_model — VARCHAR(120) — NOT NULL
  • embedding_dimension — INTEGER — NOT NULL
  • embedding_vector — TEXT — NOT NULL
  • metadata_json — TEXT
  • created_at — TIMESTAMP — NOT NULL
  • updated_at — TIMESTAMP — NOT NULL

evaluation_criteria

  • criterion_id — BIGINT — Primary key — NOT NULL
  • posting_id — BIGINT — Foreign key → postings.posting_id — NOT NULL
  • tag_id — BIGINT — Foreign key → criterion_tags.tag_id — NOT NULL
  • weight — INTEGER — NOT NULL
  • pass_score — INTEGER
  • sort_order — INTEGER — NOT NULL

evaluation_reports

  • report_id — BIGINT — Primary key — NOT NULL
  • application_id — BIGINT — Foreign key → applications.application_id
  • session_id — BIGINT — Foreign key → interview_sessions.session_id
  • report_type — VARCHAR(50) — NOT NULL
  • status — VARCHAR(40) — NOT NULL
  • total_score — INTEGER
  • summary — TEXT
  • generated_at — TIMESTAMP

file_assets

  • file_id — BIGINT — Primary key — NOT NULL
  • owner_user_id — BIGINT — Foreign key → users.user_id — NOT NULL
  • storage_key — VARCHAR(500) — NOT NULL
  • original_name — VARCHAR(255) — NOT NULL
  • mime_type — VARCHAR(100) — NOT NULL
  • size_bytes — BIGINT — NOT NULL
  • status — VARCHAR(30) — NOT NULL
  • created_at — TIMESTAMP — NOT NULL

follow_up_questions

  • follow_up_id — BIGINT — Primary key — NOT NULL
  • answer_id — BIGINT — Foreign key → interview_answers.answer_id — NOT NULL
  • content — TEXT — NOT NULL
  • generation_status — VARCHAR(40) — NOT NULL
  • created_at — TIMESTAMP — NOT NULL

interview_answers

  • answer_id — BIGINT — Primary key — NOT NULL
  • session_id — BIGINT — Foreign key → interview_sessions.session_id — NOT NULL
  • question_id — BIGINT — Foreign key → question_bank.question_id
  • video_file_id — BIGINT — Foreign key → file_assets.file_id
  • audio_file_id — BIGINT — Foreign key → file_assets.file_id
  • transcript — TEXT
  • duration_seconds — INTEGER
  • submitted_at — TIMESTAMP

interview_sessions

  • session_id — BIGINT — Primary key — NOT NULL
  • application_id — BIGINT — Foreign key → applications.application_id
  • candidate_id — BIGINT — Foreign key → candidate_profiles.candidate_id — NOT NULL
  • interview_type — VARCHAR(40) — NOT NULL
  • status — VARCHAR(40) — NOT NULL
  • show_question_text — BOOLEAN — NOT NULL
  • started_at — TIMESTAMP
  • completed_at — TIMESTAMP

manual_evaluations

  • manual_eval_id — BIGINT — Primary key — NOT NULL
  • report_id — BIGINT — Foreign key → evaluation_reports.report_id — NOT NULL
  • reviewer_user_id — BIGINT — Foreign key → users.user_id — NOT NULL
  • decision — VARCHAR(40)
  • memo — TEXT
  • reviewed_at — TIMESTAMP

notifications

  • notification_id — BIGINT — Primary key — NOT NULL
  • user_id — BIGINT — Foreign key → users.user_id — NOT NULL
  • application_id — BIGINT — Foreign key → applications.application_id
  • channel — VARCHAR(40) — NOT NULL
  • notification_type — VARCHAR(80) — NOT NULL
  • status — VARCHAR(40) — NOT NULL
  • sent_at — TIMESTAMP

postings

  • posting_id — BIGINT — Primary key — NOT NULL
  • company_id — BIGINT — Foreign key → companies.company_id — NOT NULL
  • title — VARCHAR(200) — NOT NULL
  • job_role — VARCHAR(100) — NOT NULL
  • job_description — TEXT
  • starts_on — DATE
  • ends_on — DATE
  • status — VARCHAR(30) — NOT NULL
  • created_at — TIMESTAMP — NOT NULL
  • updated_at — TIMESTAMP — NOT NULL

question_bank

  • question_id — BIGINT — Primary key — NOT NULL
  • company_id — BIGINT — Foreign key → companies.company_id — NOT NULL
  • posting_id — BIGINT — Foreign key → postings.posting_id
  • criterion_id — BIGINT — Foreign key → evaluation_criteria.criterion_id
  • question_type — VARCHAR(50) — NOT NULL
  • content — TEXT — NOT NULL
  • is_active — BOOLEAN — NOT NULL

report_evidences

  • evidence_id — BIGINT — Primary key — NOT NULL
  • score_id — BIGINT — Foreign key → report_scores.score_id — NOT NULL
  • answer_id — BIGINT — Foreign key → interview_answers.answer_id
  • evidence_text — TEXT — NOT NULL

report_scores

  • score_id — BIGINT — Primary key — NOT NULL
  • report_id — BIGINT — Foreign key → evaluation_reports.report_id — NOT NULL
  • criterion_id — BIGINT — Foreign key → evaluation_criteria.criterion_id
  • score — INTEGER — NOT NULL
  • rationale — TEXT

users

  • user_id — BIGINT — Primary key — NOT NULL
  • email — VARCHAR(255) — NOT NULL
  • password_hash — VARCHAR(255)
  • user_type — VARCHAR(30) — NOT NULL
  • name — VARCHAR(100) — NOT NULL
  • phone — VARCHAR(50)
  • status — VARCHAR(30) — NOT NULL
  • created_at — TIMESTAMP — NOT NULL
  • updated_at — TIMESTAMP — NOT NULL
  • auth_provider — VARCHAR(30) — NOT NULL
  • provider_user_id — VARCHAR(255)