Entity relationship diagram for JCCC HRIS Phase 1 data model, showing tables users, staff, mcc, team, assignment, child records, wiki tables, and enum translation.

JCCC HRIS · P1 Entity Relationships
Auth (better-auth)
People / Staff
Org structure
Child records
Wiki module
PKprimary key FKforeign key AKalt key calccomputed
auth.user better-auth managed
PKidtext
AKemailtext
name, emailVerified
session, account, verification↳ sibling tables
staff ~74 fields, ~130 rows
PKstaff_uuiduuid v7
— Name (Self)kanji/kana/EN/通称
— Address (Self/Ops)postal_code→prefecture…
— Contact (Self)email_1 AK, mobile…
— Basic (mixed tiers)gender, nationality, city…
— LDHR fieldsstatus, birthdate, dates…
— Operations fieldsvisa, passport, rent…
FKspouse_uuid→ staff (self-ref)
calcage, tenure_years
calcname_kanji_or_preferred
calcpayment_method
Fields carry tiers: Self · Basic · Team Leader · Operations · LDHR/NLT
users App allowlist + roles
PKuser_uuiduuid v7
FKstaff_uuid→ staff (nullable)
AKemaillogin key
user_typeenum Internal/External
roleenum 基本…LDHR/NLT
is_activebool — offboard flag
last_login
Bridged to auth.user by email on sign-in. Separate tables — do not merge.
mcc Department
PKmcc_uuiduuid v7
AKname
summarytext
calcteamsvia JOIN
team Ministry team
PKteam_uuiduuid v7
FKmcc_uuid→ mcc
AKname
photo, introTeam Leader edit
calcteam_leader, members
assignment staff ↔ team junction
PKassignment_uuiduuid v7
FKstaff_uuid→ staff
FKteam_uuid→ team
positionenum リーダー/メンバー
start_date, end_datenull = current
notes
Team leader = position=リーダー AND end_date IS NULL
Child records of staff
mission_historySelf-edit
PKmissionhistory_uuid
FKstaff_uuid
year, month, locationenums
ibsSelf-edit
PKibs_uuid
FKstaff_uuid
year, month, class_name
credit_or_auditenum
ics_orientationLDHR-edit
PKicsorientation_uuid
FKstaff_uuid
FKcoordinator_staff_uuid→staff
orientation_type, completed, date
ics_languageLDHR-edit
PKicslanguage_uuid
FKstaff_uuid
FKcoordinator_staff_uuid→staff
FKjapanese_staff_uuid→staff
level, completed, date
Wiki module (WIKI-DM)
collection
PKuuidv7
name_i18njsonb
slug, sort
tierdefault 'basic'
document
PKuuidv7
FKcollection_uuid
slug, tier, status
default_locale
FKcurrent_revision_uuid
FKcreated_by→users
document_locale
PKuuidv7
FKdocument_uuid
localeenum ja/en/ko
title, content_jsonjsonb
content_html, search_tsv
1 row per translation
document_revisionappend-only
PKuuidv7
FKdocument_locale_uuid
content_json, title
FKauthor_uuid→users
No UPDATE/DELETE ever
wiki_image
PKuuidv7
FKdocument_uuid
storage_pathSupabase Storage
FKuploaded_by→users
enum_translation Trilingual labels
fieldtext
value_jacanonical
value_ennullable
value_konullable — KO gap
Enums are JA-canonical. EN/KO fall back to JA if null.
Key enums (PG native)
assigned_city9 cities
member_statusFull Time…
employer_org日本CCC, KCCC…
work_status, status
available_servicelist
qualificationslist
Key relationships
auth.user → users bridged by email on sign-in (not a DB FK) users → staff FK nullable (External users have no staff record) staff ↔ team via assignment (M:M junction; history preserved by end_date) team → mcc many teams per department staff → mission_history, ibs, ics_* 1:many child rows ics_orientation/language → staff self-refs for coordinator + Japanese staff document → document_locale 1:many (one row per ja/en/ko) document_locale → document_revision append-only version history staff self-ref spouse_uuid (nullable)