rems

Tables (23)

approval_histories

  • approval_history_id — BIGINT — Primary key — NOT NULL
  • investigation_id — BIGINT — Foreign key → investigations.investigation_id — NOT NULL
  • requester_id — BIGINT — Foreign key → users.user_id — NOT NULL
  • approver_id — BIGINT — Foreign key → users.user_id
  • approval_step — SMALLINT — NOT NULL
  • approval_status — VARCHAR(20) — NOT NULL
  • request_comment — VARCHAR(1000)
  • decision_comment — VARCHAR(1000)
  • requested_at — TIMESTAMPTZ — NOT NULL
  • decided_at — TIMESTAMPTZ

attachments

  • attachment_id — BIGINT — Primary key — NOT NULL
  • investigation_id — BIGINT — Foreign key → investigations.investigation_id — NOT NULL
  • uploader_id — BIGINT — Foreign key → users.user_id — NOT NULL
  • attachment_type — VARCHAR(30) — NOT NULL
  • original_file_name — VARCHAR(255) — NOT NULL
  • stored_file_name — VARCHAR(255) — NOT NULL
  • storage_path — VARCHAR(1000) — NOT NULL
  • content_type — VARCHAR(150)
  • file_size — BIGINT — NOT NULL
  • file_hash — VARCHAR(128)
  • description — VARCHAR(500)
  • created_at — TIMESTAMPTZ — NOT NULL
  • deleted_at — TIMESTAMPTZ
  • deleted_by — BIGINT — Foreign key → users.user_id

audit_logs

  • audit_log_id — BIGINT — Primary key — NOT NULL
  • user_id — BIGINT — Foreign key → users.user_id
  • action_type — VARCHAR(30) — NOT NULL
  • entity_type — VARCHAR(100) — NOT NULL
  • entity_id — VARCHAR(100)
  • request_method — VARCHAR(10)
  • request_uri — VARCHAR(1000)
  • ip_address — INET
  • user_agent — VARCHAR(1000)
  • before_data — JSONB
  • after_data — JSONB
  • success — BOOLEAN — NOT NULL
  • error_message — TEXT
  • created_at — TIMESTAMPTZ — NOT NULL

common_code_groups

  • code_group — VARCHAR(50) — Primary key — NOT NULL
  • group_name — VARCHAR(100) — NOT NULL
  • description — VARCHAR(500)
  • active — BOOLEAN — NOT NULL
  • created_at — TIMESTAMPTZ — NOT NULL
  • updated_at — TIMESTAMPTZ — NOT NULL

common_codes

  • code — VARCHAR(50) — Primary key — NOT NULL
  • code_group — VARCHAR(50) — Foreign key → common_code_groups.code_group — NOT NULL
  • code_name — VARCHAR(100) — NOT NULL
  • sort_order — INTEGER — NOT NULL
  • attribute_1 — VARCHAR(200)
  • attribute_2 — VARCHAR(200)
  • active — BOOLEAN — NOT NULL
  • created_at — TIMESTAMPTZ — NOT NULL
  • updated_at — TIMESTAMPTZ — NOT NULL

data_import_batches

  • batch_id — BIGINT — Primary key — NOT NULL
  • source_type — VARCHAR(30) — NOT NULL
  • source_file_name — VARCHAR(255)
  • reference_date — DATE
  • status — VARCHAR(20) — NOT NULL
  • total_count — INTEGER — NOT NULL
  • success_count — INTEGER — NOT NULL
  • failure_count — INTEGER — NOT NULL
  • started_at — TIMESTAMPTZ
  • completed_at — TIMESTAMPTZ
  • error_message — TEXT
  • created_at — TIMESTAMPTZ — NOT NULL
  • created_by — BIGINT — Foreign key → users.user_id

departments

  • department_id — BIGINT — Primary key — NOT NULL
  • parent_department_id — BIGINT — Foreign key → departments.department_id
  • department_code — VARCHAR(30) — NOT NULL
  • department_name — VARCHAR(100) — NOT NULL
  • sort_order — INTEGER — NOT NULL
  • active — BOOLEAN — NOT NULL
  • created_at — TIMESTAMPTZ — NOT NULL
  • updated_at — TIMESTAMPTZ — NOT NULL

detection_rules

  • rule_id — BIGINT — Primary key — NOT NULL
  • rule_code — VARCHAR(50) — NOT NULL
  • rule_name — VARCHAR(200) — NOT NULL
  • description — VARCHAR(1000)
  • severity — VARCHAR(10) — NOT NULL
  • score — SMALLINT — NOT NULL
  • condition_json — JSONB — NOT NULL
  • rule_version — INTEGER — NOT NULL
  • active — BOOLEAN — NOT NULL
  • created_at — TIMESTAMPTZ — NOT NULL
  • created_by — BIGINT — Foreign key → users.user_id
  • updated_at — TIMESTAMPTZ — NOT NULL
  • updated_by — BIGINT — Foreign key → users.user_id

investigation_assignments

  • assignment_id — BIGINT — Primary key — NOT NULL
  • investigation_id — BIGINT — Foreign key → investigations.investigation_id — NOT NULL
  • assignee_id — BIGINT — Foreign key → users.user_id — NOT NULL
  • assignment_role — VARCHAR(20) — NOT NULL
  • assigned_at — TIMESTAMPTZ — NOT NULL
  • assigned_by — BIGINT — Foreign key → users.user_id — NOT NULL
  • ended_at — TIMESTAMPTZ
  • active — BOOLEAN — NOT NULL

investigation_opinions

  • opinion_id — BIGINT — Primary key — NOT NULL
  • investigation_id — BIGINT — Foreign key → investigations.investigation_id — NOT NULL
  • author_id — BIGINT — Foreign key → users.user_id — NOT NULL
  • opinion_type — VARCHAR(20) — NOT NULL
  • content — TEXT — NOT NULL
  • created_at — TIMESTAMPTZ — NOT NULL
  • updated_at — TIMESTAMPTZ — NOT NULL

investigation_status_histories

  • status_history_id — BIGINT — Primary key — NOT NULL
  • investigation_id — BIGINT — Foreign key → investigations.investigation_id — NOT NULL
  • previous_status — VARCHAR(30)
  • new_status — VARCHAR(30) — NOT NULL
  • change_reason — VARCHAR(1000)
  • changed_at — TIMESTAMPTZ — NOT NULL
  • changed_by — BIGINT — Foreign key → users.user_id — NOT NULL

investigations

  • investigation_id — BIGINT — Primary key — NOT NULL
  • suspicious_id — BIGINT — Foreign key → suspicious_transactions.suspicious_id — NOT NULL
  • investigation_number — VARCHAR(30) — NOT NULL
  • title — VARCHAR(300) — NOT NULL
  • status — VARCHAR(30) — NOT NULL
  • priority — VARCHAR(10) — NOT NULL
  • due_date — DATE
  • final_result — VARCHAR(30)
  • final_opinion — TEXT
  • closed_at — TIMESTAMPTZ
  • closed_by — BIGINT — Foreign key → users.user_id
  • created_at — TIMESTAMPTZ — NOT NULL
  • created_by — BIGINT — Foreign key → users.user_id — NOT NULL
  • updated_at — TIMESTAMPTZ — NOT NULL
  • updated_by — BIGINT — Foreign key → users.user_id

notifications

  • notification_id — BIGINT — Primary key — NOT NULL
  • user_id — BIGINT — Foreign key → users.user_id — NOT NULL
  • notification_type — VARCHAR(30) — NOT NULL
  • title — VARCHAR(200) — NOT NULL
  • message — VARCHAR(1000) — NOT NULL
  • related_type — VARCHAR(30)
  • related_id — BIGINT
  • read_at — TIMESTAMPTZ
  • created_at — TIMESTAMPTZ — NOT NULL

permissions

  • permission_id — BIGINT — Primary key — NOT NULL
  • permission_code — VARCHAR(100) — NOT NULL
  • permission_name — VARCHAR(100) — NOT NULL
  • resource_type — VARCHAR(30) — NOT NULL
  • description — VARCHAR(500)
  • created_at — TIMESTAMPTZ — NOT NULL

properties

  • property_id — BIGINT — Primary key — NOT NULL
  • region_id — BIGINT — Foreign key → regions.region_id — NOT NULL
  • property_type — VARCHAR(20) — NOT NULL
  • property_name — VARCHAR(200)
  • road_address — VARCHAR(500)
  • lot_address — VARCHAR(500) — NOT NULL
  • main_lot_number — VARCHAR(10)
  • sub_lot_number — VARCHAR(10)
  • construction_year — SMALLINT
  • total_households — INTEGER
  • latitude — NUMERIC(10, 7)
  • longitude — NUMERIC(10, 7)
  • active — BOOLEAN — NOT NULL
  • created_at — TIMESTAMPTZ — NOT NULL
  • updated_at — TIMESTAMPTZ — NOT NULL

real_estate_transactions

  • transaction_id — BIGINT — Primary key — NOT NULL
  • property_id — BIGINT — Foreign key → properties.property_id — NOT NULL
  • batch_id — BIGINT — Foreign key → data_import_batches.batch_id
  • external_transaction_key — VARCHAR(100)
  • transaction_type — VARCHAR(20) — NOT NULL
  • contract_date — DATE — NOT NULL
  • report_date — DATE
  • cancellation_date — DATE
  • transaction_amount — BIGINT — 매매금액(만원 단위)
  • deposit_amount — BIGINT — 보증금(만원 단위)
  • monthly_rent_amount — BIGINT — 월세(만원 단위)
  • exclusive_area_m2 — NUMERIC(10, 4) — NOT NULL
  • land_area_m2 — NUMERIC(12, 4)
  • floor_number — SMALLINT
  • buyer_type — VARCHAR(20)
  • seller_type — VARCHAR(20)
  • brokerage_location — VARCHAR(300)
  • dealing_type — VARCHAR(20)
  • cancelled — BOOLEAN — NOT NULL
  • raw_data — JSONB
  • created_at — TIMESTAMPTZ — NOT NULL
  • updated_at — TIMESTAMPTZ — NOT NULL

regions

  • region_id — BIGINT — Primary key — NOT NULL
  • parent_region_id — BIGINT — Foreign key → regions.region_id
  • legal_dong_code — VARCHAR(10) — NOT NULL
  • region_name — VARCHAR(100) — NOT NULL
  • full_region_name — VARCHAR(300) — NOT NULL
  • region_level — SMALLINT — NOT NULL
  • sido_name — VARCHAR(50) — NOT NULL
  • sigungu_name — VARCHAR(50)
  • eup_myeon_dong_name — VARCHAR(50)
  • active — BOOLEAN — NOT NULL
  • created_at — TIMESTAMPTZ — NOT NULL
  • updated_at — TIMESTAMPTZ — NOT NULL

role_permissions

  • role_id — BIGINT — Foreign key → roles.role_id — NOT NULL
  • permission_id — BIGINT — Foreign key → permissions.permission_id — NOT NULL
  • granted_at — TIMESTAMPTZ — NOT NULL

roles

  • role_id — BIGINT — Primary key — NOT NULL
  • role_code — VARCHAR(50) — NOT NULL
  • role_name — VARCHAR(100) — NOT NULL
  • description — VARCHAR(500)
  • active — BOOLEAN — NOT NULL
  • created_at — TIMESTAMPTZ — NOT NULL
  • updated_at — TIMESTAMPTZ — NOT NULL

suspicious_transaction_reasons

  • suspicious_reason_id — BIGINT — Primary key — NOT NULL
  • suspicious_id — BIGINT — Foreign key → suspicious_transactions.suspicious_id — NOT NULL
  • rule_id — BIGINT — Foreign key → detection_rules.rule_id — NOT NULL
  • detected_value — VARCHAR(500)
  • threshold_value — VARCHAR(500)
  • score — SMALLINT — NOT NULL
  • reason_detail — VARCHAR(1000) — NOT NULL
  • detected_at — TIMESTAMPTZ — NOT NULL

suspicious_transactions

  • suspicious_id — BIGINT — Primary key — NOT NULL
  • transaction_id — BIGINT — Foreign key → real_estate_transactions.transaction_id — NOT NULL
  • total_score — SMALLINT — NOT NULL
  • risk_level — VARCHAR(10) — NOT NULL
  • detection_status — VARCHAR(20) — NOT NULL
  • detected_at — TIMESTAMPTZ — NOT NULL
  • last_evaluated_at — TIMESTAMPTZ — NOT NULL
  • summary — VARCHAR(1000)
  • created_at — TIMESTAMPTZ — NOT NULL
  • updated_at — TIMESTAMPTZ — NOT NULL

user_roles

  • user_id — BIGINT — Foreign key → users.user_id — NOT NULL
  • role_id — BIGINT — Foreign key → roles.role_id — NOT NULL
  • assigned_at — TIMESTAMPTZ — NOT NULL
  • assigned_by — BIGINT — Foreign key → users.user_id

users

  • user_id — BIGINT — Primary key — NOT NULL
  • department_id — BIGINT — Foreign key → departments.department_id
  • login_id — VARCHAR(50) — NOT NULL
  • password_hash — VARCHAR(255) — NOT NULL
  • user_name — VARCHAR(100) — NOT NULL
  • email — VARCHAR(255) — NOT NULL
  • phone_number — VARCHAR(30)
  • employee_number — VARCHAR(30)
  • account_status — VARCHAR(20) — NOT NULL
  • failed_login_count — INTEGER — NOT NULL
  • password_changed_at — TIMESTAMPTZ
  • last_login_at — TIMESTAMPTZ
  • active — BOOLEAN — NOT NULL
  • created_at — TIMESTAMPTZ — NOT NULL
  • created_by — BIGINT — Foreign key → users.user_id
  • updated_at — TIMESTAMPTZ — NOT NULL
  • updated_by — BIGINT — Foreign key → users.user_id