Proteen Manager

Tables (29)

attendance_records

  • id — bigserial
  • session_id — bigint — Foreign key → volunteer_sessions.id — NOT NULL
  • user_id — bigint — NOT NULL
  • attendance_status — varchar(30) — NOT NULL
  • checked_in_at — timestamptz
  • checked_out_at — timestamptz
  • recorded_by_user_id — bigint
  • notes — text

blog_post_tags

  • id — bigserial
  • blog_post_id — bigint — Foreign key → blog_posts.id — NOT NULL
  • blog_tag_id — bigint — Foreign key → blog_tags.id — NOT NULL

blog_posts

  • id — bigserial
  • slug — varchar(255) — NOT NULL
  • title — varchar(255) — NOT NULL
  • summary — text
  • content — text — NOT NULL
  • thumbnail_media_asset_id — bigint
  • visibility — varchar(30) — NOT NULL
  • publication_status — varchar(30) — NOT NULL
  • created_by_user_id — bigint — NOT NULL
  • updated_by_user_id — bigint
  • published_at — timestamptz
  • created_at — timestamptz — NOT NULL
  • updated_at — timestamptz — NOT NULL

blog_tags

  • id — bigserial
  • name — varchar(100) — NOT NULL

external_contacts

  • id — bigserial
  • name — varchar(100) — NOT NULL
  • organization — varchar(200)
  • role_title — varchar(100)
  • phone — varchar(30)
  • email — varchar(255)
  • memo — text
  • created_at — timestamptz — NOT NULL
  • updated_at — timestamptz — NOT NULL

gallery_albums

  • id — bigserial
  • program_id — bigint — NOT NULL
  • title — varchar(255) — NOT NULL
  • visibility — varchar(30) — NOT NULL
  • password_hash — varchar(255)
  • allow_download — boolean — NOT NULL
  • created_by_user_id — bigint
  • created_at — timestamptz — NOT NULL
  • updated_at — timestamptz — NOT NULL

gallery_entries

  • id — bigserial
  • album_id — bigint — Foreign key → gallery_albums.id — NOT NULL
  • session_id — bigint
  • week_no — integer
  • title — varchar(255) — NOT NULL
  • description — text
  • sort_order — integer — NOT NULL
  • created_at — timestamptz — NOT NULL

gallery_entry_media

  • id — bigserial
  • gallery_entry_id — bigint — Foreign key → gallery_entries.id — NOT NULL
  • media_asset_id — bigint — Foreign key → media_assets.id — NOT NULL
  • is_cover — boolean — NOT NULL
  • sort_order — integer — NOT NULL

media_assets

  • id — bigserial
  • owner_user_id — bigint
  • asset_type — varchar(30) — NOT NULL
  • storage_provider — varchar(30) — NOT NULL
  • bucket_name — varchar(255)
  • object_key — varchar(500) — NOT NULL
  • original_file_name — varchar(255) — NOT NULL
  • mime_type — varchar(100)
  • file_size — bigint
  • public_url — varchar(1000)
  • created_at — timestamptz — NOT NULL

member_profile_images

  • id — bigserial
  • member_profile_id — bigint — NOT NULL
  • media_asset_id — bigint — Foreign key → media_assets.id — NOT NULL
  • image_type — varchar(30) — NOT NULL
  • created_at — timestamptz — NOT NULL

member_profiles

  • id — bigserial
  • user_id — bigint — Foreign key → users.id — NOT NULL
  • student_number — varchar(30) — NOT NULL
  • department — varchar(150) — NOT NULL
  • major — varchar(150)
  • grade — integer — NOT NULL
  • profile_memo — text
  • created_at — timestamptz — NOT NULL
  • updated_at — timestamptz — NOT NULL

notice_post_tags

  • id — bigserial
  • notice_post_id — bigint — Foreign key → notice_posts.id — NOT NULL
  • notice_tag_id — bigint — Foreign key → notice_tags.id — NOT NULL

notice_posts

  • id — bigserial
  • slug — varchar(255) — NOT NULL
  • category — varchar(50) — NOT NULL
  • title — varchar(255) — NOT NULL
  • summary — text
  • content — text — NOT NULL
  • audience_scope — varchar(30) — NOT NULL
  • is_pinned — boolean — NOT NULL
  • is_published — boolean — NOT NULL
  • published_at — timestamptz
  • created_by_user_id — bigint — NOT NULL
  • updated_by_user_id — bigint
  • created_at — timestamptz — NOT NULL
  • updated_at — timestamptz — NOT NULL

notice_tags

  • id — bigserial
  • name — varchar(100) — NOT NULL

participant_reflections

  • id — bigserial
  • program_id — bigint — Foreign key → volunteer_programs.id — NOT NULL
  • session_id — bigint — Foreign key → volunteer_sessions.id
  • author_user_id — bigint — NOT NULL
  • reflection_type — varchar(30) — NOT NULL
  • title — varchar(255)
  • content — text — NOT NULL
  • created_at — timestamptz — NOT NULL
  • updated_at — timestamptz — NOT NULL

program_materials

  • id — bigserial
  • program_id — bigint — NOT NULL
  • session_id — bigint
  • resource_document_id — bigint
  • media_asset_id — bigint
  • title — varchar(255) — NOT NULL
  • material_type — varchar(50) — NOT NULL
  • notes — text
  • created_by_user_id — bigint
  • created_at — timestamptz — NOT NULL

resource_document_tags

  • id — bigserial
  • resource_document_id — bigint — Foreign key → resource_documents.id — NOT NULL
  • resource_tag_id — bigint — Foreign key → resource_tags.id — NOT NULL

resource_documents

  • id — bigserial
  • title — varchar(255) — NOT NULL
  • resource_type — varchar(50) — NOT NULL
  • description — text
  • created_by_user_id — bigint
  • updated_by_user_id — bigint
  • is_archived — boolean — NOT NULL
  • created_at — timestamptz — NOT NULL
  • updated_at — timestamptz — NOT NULL

resource_tags

  • id — bigserial
  • name — varchar(100) — NOT NULL

roles

  • id — bigserial
  • code — varchar(50) — NOT NULL
  • name — varchar(100) — NOT NULL
  • description — text

user_roles

  • id — bigserial
  • user_id — bigint — Foreign key → users.id — NOT NULL
  • role_id — bigint — Foreign key → roles.id — NOT NULL
  • assigned_at — timestamptz — NOT NULL
  • assigned_by_user_id — bigint — Foreign key → users.id

users

  • id — bigserial
  • email — varchar(255) — NOT NULL
  • password_hash — varchar(255) — NOT NULL
  • name — varchar(100) — NOT NULL
  • phone — varchar(30)
  • student_status — varchar(30) — NOT NULL
  • account_status — varchar(30) — NOT NULL
  • last_login_at — timestamptz
  • created_at — timestamptz — NOT NULL
  • updated_at — timestamptz — NOT NULL
  • deleted_at — timestamptz

volunteer_applications

  • id — bigserial
  • program_id — bigint — Foreign key → volunteer_programs.id — NOT NULL
  • user_id — bigint — NOT NULL
  • application_status — varchar(30) — NOT NULL
  • applied_at — timestamptz — NOT NULL
  • decided_at — timestamptz
  • decided_by_user_id — bigint
  • memo — text

volunteer_center_contacts

  • id — bigserial
  • center_id — bigint — Foreign key → volunteer_centers.id — NOT NULL
  • external_contact_id — bigint — Foreign key → external_contacts.id — NOT NULL
  • relationship_type — varchar(50) — NOT NULL
  • is_primary — boolean — NOT NULL
  • relation_memo — text
  • created_at — timestamptz — NOT NULL

volunteer_centers

  • id — bigserial
  • name — varchar(200) — NOT NULL
  • center_type — varchar(50)
  • target_age_group — varchar(100)
  • address — varchar(255)
  • detail_address — varchar(255)
  • region — varchar(100)
  • notes — text
  • is_active — boolean — NOT NULL
  • created_at — timestamptz — NOT NULL
  • updated_at — timestamptz — NOT NULL

volunteer_program_members

  • id — bigserial
  • program_id — bigint — Foreign key → volunteer_programs.id — NOT NULL
  • user_id — bigint — NOT NULL
  • assignment_role — varchar(50) — NOT NULL
  • participation_status — varchar(30) — NOT NULL
  • assigned_at — timestamptz — NOT NULL
  • assigned_by_user_id — bigint
  • notes — text

volunteer_program_reviews

  • id — bigserial
  • program_id — bigint — Foreign key → volunteer_programs.id — NOT NULL
  • session_id — bigint — Foreign key → volunteer_sessions.id
  • author_user_id — bigint — NOT NULL
  • review_scope — varchar(30) — NOT NULL
  • title — varchar(255)
  • content — text — NOT NULL
  • created_at — timestamptz — NOT NULL
  • updated_at — timestamptz — NOT NULL

volunteer_programs

  • id — bigserial
  • center_id — bigint — NOT NULL
  • title — varchar(255) — NOT NULL
  • term_label — varchar(100)
  • description — text
  • start_date — date — NOT NULL
  • end_date — date — NOT NULL
  • total_weeks — integer — NOT NULL
  • status — varchar(30) — NOT NULL
  • is_public_gallery_enabled — boolean — NOT NULL
  • created_by_user_id — bigint
  • created_at — timestamptz — NOT NULL
  • updated_at — timestamptz — NOT NULL

volunteer_sessions

  • id — bigserial
  • program_id — bigint — Foreign key → volunteer_programs.id — NOT NULL
  • week_no — integer — NOT NULL
  • title — varchar(255) — NOT NULL
  • session_date — date — NOT NULL
  • start_time — time
  • end_time — time
  • location — varchar(255)
  • session_status — varchar(30) — NOT NULL
  • agenda — text
  • created_at — timestamptz — NOT NULL
  • updated_at — timestamptz — NOT NULL