住民記録システム Web版 ER図

erDiagram RESIDENT ||--o| RESIDENT_FOREIGNER : "1:0..1" RESIDENT ||--o{ RESIDENT_HISTORY : "1:N (SCD-2)" RESIDENT ||--o{ ALIAS_NAME : "通称・旧氏" RESIDENT ||--o{ JUMIN_CODE : "住民票コード履歴" RESIDENT ||--o{ MY_NUMBER : "個人番号履歴" RESIDENT }o--|| HOUSEHOLD : "現世帯" RESIDENT ||--o{ HOUSEHOLD_MEMBER : "世帯所属(履歴)" HOUSEHOLD ||--o{ HOUSEHOLD_MEMBER : "1:N" RESIDENT ||--o{ RESTRICTION : "DV等支援措置" RESIDENT ||--o{ TRANSACTION : "対象住民" TRANSACTION ||--o{ TRANSACTION_ITEM : "項目別変更" TRANSACTION }o--|| TRANSACTION_TYPE : "区分(届出/職権)" TRANSACTION ||--o{ TRANSACTION_APPROVAL : "決裁ルート" TRANSACTION ||--o{ CERTIFICATE_ISSUE : "発行起点(任意)" RESIDENT ||--o{ CERTIFICATE_ISSUE : "発行履歴" USER_ACCOUNT ||--o{ AUDIT_LOG : "1:N" USER_ACCOUNT }o--o{ ROLE : "M:N (USER_ROLE)" ROLE ||--o{ PERMISSION : "1:N" LINK_PARTNER ||--o{ LINK_EVENT : "連携イベント" TRANSACTION ||--o{ LINK_EVENT : "派生連携" REPORT_REQUEST }o--|| USER_ACCOUNT : "依頼者" RESIDENT { varchar resident_id PK "宛名番号" varchar household_id FK varchar family_name_kanji varchar given_name_kanji varchar family_name_kana varchar given_name_kana date birth_date char sex varchar nationality "外国人時" varchar address_code varchar address_text date moved_in_date date moved_out_date boolean restricted_flag timestamp valid_from timestamp valid_to } RESIDENT_FOREIGNER { varchar resident_id PK,FK varchar residence_status date residence_period_end varchar passport_no varchar nationality_full varchar alias_kanji "通称" boolean special_permanent_resident } HOUSEHOLD { varchar household_id PK varchar head_resident_id FK varchar address_code varchar address_text date established_date date closed_date } HOUSEHOLD_MEMBER { varchar household_id PK,FK varchar resident_id PK,FK varchar relation_to_head date joined_date date left_date } RESIDENT_HISTORY { varchar resident_id PK,FK timestamp valid_from PK timestamp valid_to jsonb snapshot "全項目スナップショット" varchar transaction_id FK } ALIAS_NAME { bigint alias_id PK varchar resident_id FK varchar kind "通称/旧氏" varchar value_kanji varchar value_kana date valid_from date valid_to } JUMIN_CODE { bigint id PK varchar resident_id FK varchar code "11桁" date valid_from date valid_to varchar event "付番/変更/修正" } MY_NUMBER { bigint id PK varchar resident_id FK varchar number "12桁(暗号化)" date valid_from date valid_to varchar event } RESTRICTION { bigint restriction_id PK varchar resident_id FK varchar category "DV/ストーカー/児童虐待" date start_date date end_date varchar scope "本人/世帯" varchar release_role text note } TRANSACTION { varchar transaction_id PK varchar resident_id FK varchar household_id FK varchar type_code FK "届出/職権/取消等" varchar reason_code date event_date "異動年月日" date processed_date varchar receiver_office varchar status "起票/審査/承認/反映/取消" varchar parent_transaction_id FK "取消元" } TRANSACTION_TYPE { varchar code PK varchar name varchar category "届出/職権/連動/取消" } TRANSACTION_ITEM { varchar transaction_id PK,FK varchar field PK text value_before text value_after } TRANSACTION_APPROVAL { bigint id PK varchar transaction_id FK int step varchar role varchar approver_user_id FK varchar status text comment timestamp acted_at } CERTIFICATE_ISSUE { bigint issue_id PK varchar resident_id FK varchar transaction_id FK varchar form_id "0010001 等" int copies varchar usage_text int fee varchar verify_token "改ざん防止" timestamp issued_at varchar issuer_user_id FK varchar channel "窓口/コンビニ/オンライン" } USER_ACCOUNT { varchar user_id PK varchar oidc_subject varchar employee_no varchar department varchar full_name boolean active } ROLE { varchar role_id PK varchar name text description } USER_ROLE { varchar user_id PK,FK varchar role_id PK,FK date valid_from date valid_to } PERMISSION { varchar role_id PK,FK varchar resource PK "機能/項目" varchar action PK "view/edit/approve" varchar mask "個人番号 等" } AUDIT_LOG { bigint log_id PK varchar user_id FK varchar ip varchar action varchar resource_type varchar resource_id timestamp occurred_at jsonb details } LINK_PARTNER { varchar partner_id PK varchar name "CS/番号/戸籍/税/国保/選挙/申請管理" varchar endpoint varchar protocol } LINK_EVENT { bigint event_id PK varchar partner_id FK varchar transaction_id FK varchar direction "in/out" timestamp occurred_at varchar status jsonb payload } REPORT_REQUEST { bigint report_id PK varchar requester_user_id FK varchar template_id jsonb params varchar status varchar file_path timestamp created_at }