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