AICS_Web

Tables (25)

attendance

  • id — SERIAL — Primary key — NOT NULL
  • user_id — INT — Foreign key → users.id — NOT NULL
  • date — DATE — NOT NULL
  • check_in — TIMESTAMP
  • check_out — TIMESTAMP
  • status — attendance_status — NOT NULL
  • leave_request_id — INT — Foreign key → leave_requests.id
  • created_at — TIMESTAMP — NOT NULL
  • updated_at — TIMESTAMP — NOT NULL
  • UNIQUE(user_id, — date)

budgets

  • id — SERIAL — Primary key — NOT NULL
  • name — VARCHAR(200) — NOT NULL
  • fund_type — fund_type — NOT NULL
  • project_id — INT — Foreign key → research_projects.id
  • total_budget — DECIMAL(12,2) — NOT NULL
  • start_date — DATE — NOT NULL
  • end_date — DATE — NOT NULL
  • status — budget_status — NOT NULL
  • created_at — TIMESTAMP — NOT NULL
  • updated_at — TIMESTAMP — NOT NULL

calendar_event_exceptions

  • id — SERIAL — Primary key — NOT NULL
  • event_id — INT — Foreign key → calendar_events.id — NOT NULL
  • original_date — DATE — NOT NULL
  • is_cancelled — BOOLEAN — NOT NULL
  • new_start — TIMESTAMP
  • new_end — TIMESTAMP
  • new_title — VARCHAR(200)
  • created_at — TIMESTAMP — NOT NULL
  • UNIQUE(event_id, — original_date)

calendar_event_participants

  • id — SERIAL — Primary key — NOT NULL
  • event_id — INT — Foreign key → calendar_events.id — NOT NULL
  • user_id — INT — Foreign key → users.id — NOT NULL
  • created_at — TIMESTAMP — NOT NULL
  • UNIQUE(event_id, — user_id)

calendar_events

  • id — SERIAL — Primary key — NOT NULL
  • created_by — INT — Foreign key → users.id
  • title — VARCHAR(200) — NOT NULL
  • description — TEXT
  • event_type — event_type — NOT NULL
  • scope — event_scope — NOT NULL
  • color_key — VARCHAR(20)
  • start_datetime — TIMESTAMP — NOT NULL
  • end_datetime — TIMESTAMP — NOT NULL
  • is_all_day — BOOLEAN — NOT NULL
  • location — VARCHAR(255)
  • is_recurring — BOOLEAN — NOT NULL
  • recurrence_rule — VARCHAR(255)
  • created_at — TIMESTAMP — NOT NULL
  • updated_at — TIMESTAMP — NOT NULL

credential_access_logs

  • id — SERIAL — Primary key — NOT NULL
  • credential_id — INT — Foreign key → shared_credentials.id — NOT NULL
  • user_id — INT — Foreign key → users.id — NOT NULL
  • action — credential_action — NOT NULL
  • accessed_at — TIMESTAMP — NOT NULL

expense_receipts

  • id — SERIAL — Primary key — NOT NULL
  • expense_id — INT — Foreign key → expenses.id — NOT NULL
  • filename — VARCHAR(255) — NOT NULL
  • mime_type — VARCHAR(100)
  • storage_type — storage_type — NOT NULL
  • file_url — VARCHAR(1000)
  • filepath — VARCHAR(500)
  • filesize — BIGINT
  • created_at — TIMESTAMP — NOT NULL

expenses

  • id — SERIAL — Primary key — NOT NULL
  • budget_id — INT — Foreign key → budgets.id — NOT NULL
  • user_id — INT — Foreign key → users.id
  • category — expense_category — NOT NULL
  • item_name — VARCHAR(200) — NOT NULL
  • amount — DECIMAL(12,2) — NOT NULL
  • date — DATE — NOT NULL
  • status — expense_status — NOT NULL
  • reject_reason — TEXT
  • reviewed_by — INT — Foreign key → users.id
  • reviewed_at — TIMESTAMP
  • created_at — TIMESTAMP — NOT NULL
  • updated_at — TIMESTAMP — NOT NULL

leave_balances

  • id — SERIAL — Primary key — NOT NULL
  • user_id — INT — Foreign key → users.id — NOT NULL
  • year — INT — NOT NULL
  • total_days — DECIMAL(4,1) — NOT NULL
  • created_at — TIMESTAMP — NOT NULL
  • updated_at — TIMESTAMP — NOT NULL
  • UNIQUE(user_id, — year)

leave_requests

  • id — SERIAL — Primary key — NOT NULL
  • user_id — INT — Foreign key → users.id — NOT NULL
  • leave_type — leave_type — NOT NULL
  • half_period — half_period
  • start_date — DATE — NOT NULL
  • end_date — DATE — NOT NULL
  • reason — TEXT
  • status — leave_status — NOT NULL
  • reject_reason — TEXT
  • reviewed_by — INT — Foreign key → users.id
  • reviewed_at — TIMESTAMP
  • created_at — TIMESTAMP — NOT NULL
  • updated_at — TIMESTAMP — NOT NULL

notice_attachments

  • id — SERIAL — Primary key — NOT NULL
  • notice_id — INT — Foreign key → notices.id — NOT NULL
  • filename — VARCHAR(255) — NOT NULL
  • mime_type — VARCHAR(100)
  • storage_type — storage_type — NOT NULL
  • file_url — VARCHAR(1000)
  • filepath — VARCHAR(500)
  • filesize — BIGINT
  • created_at — TIMESTAMP — NOT NULL

notices

  • id — SERIAL — Primary key — NOT NULL
  • author_id — INT — Foreign key → users.id
  • title — VARCHAR(200) — NOT NULL
  • content — TEXT — NOT NULL
  • category — notice_category — NOT NULL
  • is_pinned — BOOLEAN — NOT NULL
  • view_count — INT — NOT NULL
  • created_at — TIMESTAMP — NOT NULL
  • updated_at — TIMESTAMP — NOT NULL

notifications

  • id — SERIAL — Primary key — NOT NULL
  • user_id — INT — Foreign key → users.id — NOT NULL
  • type — notification_type — NOT NULL
  • title — VARCHAR(200) — NOT NULL
  • message — TEXT — NOT NULL
  • related_type — VARCHAR(50)
  • related_id — INT
  • is_read — BOOLEAN — NOT NULL
  • created_at — TIMESTAMP — NOT NULL

password_reset_tokens

  • id — SERIAL — Primary key — NOT NULL
  • user_id — INT — Foreign key → users.id — NOT NULL
  • token_hash — VARCHAR(255) — NOT NULL
  • expires_at — TIMESTAMP — NOT NULL
  • used_at — TIMESTAMP
  • created_at — TIMESTAMP — NOT NULL

publication_attachments

  • id — SERIAL — Primary key — NOT NULL
  • publication_id — INT — Foreign key → publications.id — NOT NULL
  • filename — VARCHAR(255) — NOT NULL
  • mime_type — VARCHAR(100)
  • storage_type — storage_type — NOT NULL
  • file_url — VARCHAR(1000)
  • filepath — VARCHAR(500)
  • filesize — BIGINT
  • created_at — TIMESTAMP — NOT NULL

publication_authors

  • id — SERIAL — Primary key — NOT NULL
  • publication_id — INT — Foreign key → publications.id — NOT NULL
  • user_id — INT — Foreign key → users.id — NOT NULL
  • author_order — INT
  • is_corresponding — BOOLEAN — NOT NULL
  • created_at — TIMESTAMP — NOT NULL
  • UNIQUE(publication_id, — user_id)

publications

  • id — SERIAL — Primary key — NOT NULL
  • title — VARCHAR(500) — NOT NULL
  • authors_text — VARCHAR(1000) — NOT NULL
  • year — INT — NOT NULL
  • published_date — DATE
  • pub_type — pub_type — NOT NULL
  • status — pub_status — NOT NULL
  • venue — VARCHAR(500)
  • doi — VARCHAR(255)
  • is_public — BOOLEAN — NOT NULL
  • created_at — TIMESTAMP — NOT NULL
  • updated_at — TIMESTAMP — NOT NULL

purchase_requests

  • id — SERIAL — Primary key — NOT NULL
  • user_id — INT — Foreign key → users.id — NOT NULL
  • item_name — VARCHAR(200) — NOT NULL
  • quantity — INT — NOT NULL
  • estimated_price — DECIMAL(12,2) — NOT NULL
  • purchase_url — TEXT
  • reason — TEXT — NOT NULL
  • status — purchase_status — NOT NULL
  • reject_reason — TEXT
  • reviewed_by — INT — Foreign key → users.id
  • reviewed_at — TIMESTAMP
  • purchased_by — INT — Foreign key → users.id
  • purchased_at — TIMESTAMP
  • delivered_at — TIMESTAMP
  • expense_id — INT — Foreign key → expenses.id
  • created_at — TIMESTAMP — NOT NULL
  • updated_at — TIMESTAMP — NOT NULL

recruit_application_attachments

  • id — SERIAL — Primary key — NOT NULL
  • application_id — INT — Foreign key → recruit_applications.id — NOT NULL
  • filename — VARCHAR(255) — NOT NULL
  • mime_type — VARCHAR(100)
  • storage_type — storage_type — NOT NULL
  • file_url — VARCHAR(1000)
  • filepath — VARCHAR(500)
  • filesize — BIGINT
  • created_at — TIMESTAMP — NOT NULL

recruit_applications

  • id — SERIAL — Primary key — NOT NULL
  • target_term — VARCHAR(50) — NOT NULL
  • name — VARCHAR(100) — NOT NULL
  • email — VARCHAR(255) — NOT NULL
  • phone — VARCHAR(20)
  • student_id — VARCHAR(50)
  • department — VARCHAR(100)
  • grade — VARCHAR(50)
  • interest_area — TEXT
  • introduction — TEXT
  • github_url — VARCHAR(255)
  • portfolio_url — VARCHAR(255)
  • status — recruit_status — NOT NULL
  • is_read — BOOLEAN — NOT NULL
  • internal_memo — TEXT
  • reviewed_by — INT — Foreign key → users.id
  • privacy_consent — BOOLEAN — NOT NULL
  • consent_at — TIMESTAMP
  • created_at — TIMESTAMP — NOT NULL
  • updated_at — TIMESTAMP — NOT NULL

refresh_tokens

  • id — SERIAL — Primary key — NOT NULL
  • user_id — INT — Foreign key → users.id — NOT NULL
  • token_hash — VARCHAR(255) — NOT NULL
  • expires_at — TIMESTAMP — NOT NULL
  • device_info — VARCHAR(255)
  • revoked_at — TIMESTAMP
  • created_at — TIMESTAMP — NOT NULL

research_projects

  • id — SERIAL — Primary key — NOT NULL
  • title — VARCHAR(300) — NOT NULL
  • funding_agency — VARCHAR(200)
  • period — VARCHAR(100)
  • role — VARCHAR(100)
  • status — VARCHAR(50)
  • created_at — TIMESTAMP — NOT NULL
  • updated_at — TIMESTAMP — NOT NULL

shared_credentials

  • id — SERIAL — Primary key — NOT NULL
  • title — VARCHAR(200) — NOT NULL
  • category — credential_category — NOT NULL
  • username — VARCHAR(255)
  • password_encrypted — TEXT — NOT NULL
  • password_iv — VARCHAR(32) — NOT NULL
  • password_auth_tag — VARCHAR(32)
  • url — VARCHAR(1000)
  • memo — TEXT
  • min_role — user_role — NOT NULL
  • last_rotated_at — TIMESTAMP — NOT NULL
  • created_by — INT — Foreign key → users.id
  • created_at — TIMESTAMP — NOT NULL
  • updated_at — TIMESTAMP — NOT NULL

shared_files

  • id — SERIAL — Primary key — NOT NULL
  • uploaded_by — INT — Foreign key → users.id
  • title — VARCHAR(200) — NOT NULL
  • description — TEXT
  • category — file_category — NOT NULL
  • min_role — user_role — NOT NULL
  • filename — VARCHAR(255) — NOT NULL
  • mime_type — VARCHAR(100)
  • storage_type — storage_type — NOT NULL
  • file_url — VARCHAR(1000)
  • filepath — VARCHAR(500)
  • filesize — BIGINT
  • version — INT — NOT NULL
  • download_count — INT — NOT NULL
  • created_at — TIMESTAMP — NOT NULL
  • updated_at — TIMESTAMP — NOT NULL

users

  • id — SERIAL — Primary key — NOT NULL
  • email — VARCHAR(255) — NOT NULL
  • password_hash — VARCHAR(255) — NOT NULL
  • name — VARCHAR(100) — NOT NULL
  • role — user_role — NOT NULL
  • account_status — account_status — NOT NULL
  • student_id — VARCHAR(50) — NOT NULL
  • department — VARCHAR(100) — NOT NULL
  • program — program_type — NOT NULL
  • enrollment_year — INT — NOT NULL
  • graduation_year — INT
  • grade_override — VARCHAR(50)
  • research_topic — TEXT
  • profile_image — VARCHAR(255)
  • phone — VARCHAR(20)
  • bio — VARCHAR(255)
  • github_url — VARCHAR(255)
  • linkedin_url — VARCHAR(255)
  • is_public_profile — BOOLEAN — NOT NULL
  • is_alumni — BOOLEAN — NOT NULL
  • last_login_at — TIMESTAMP
  • preferred_language — lang_type — NOT NULL
  • created_at — TIMESTAMP — NOT NULL
  • updated_at — TIMESTAMP — NOT NULL