maru3

Tables (23)

admin_audit_logs

  • id — bigint — Primary key — NOT NULL
  • admin_user_id — bigint — Foreign key → users.id
  • action — enum('APPROVE_PROPERTY','REJECT_PROPERTY','DELETE_PROPERTY','SUSPEND_USER','RESOLVE_REPORT','REJECT_REPORT','OTHER') — NOT NULL
  • target_table — varchar(100)
  • target_id — bigint
  • before_value — json
  • after_value — json
  • ip_address — varchar(45)
  • user_agent — text
  • created_at — datetime(6) — NOT NULL

agent_profiles

  • id — bigint — Primary key — NOT NULL
  • user_id — bigint — Foreign key → users.id — NOT NULL
  • office_name — varchar(150) — NOT NULL
  • business_registration_no — varchar(50)
  • realtor_license_no — varchar(80)
  • office_phone — varchar(30)
  • office_address — varchar(500)
  • verified — tinyint(1) — NOT NULL
  • verified_at — datetime(6)
  • created_at — datetime(6) — NOT NULL
  • updated_at — datetime(6) — NOT NULL

buildings

  • mgm_bldrgst_pk — varchar(30) — Primary key — NOT NULL — 관리건축물대장 PK(외부 자연키)
  • region_id — bigint — Foreign key → regions.id
  • sigungu_cd — varchar(5)
  • bjdong_cd — varchar(5)
  • bun — varchar(20)
  • ji — varchar(20)
  • address — varchar(500) — 지번주소
  • road_address — varchar(500) — 도로명주소
  • building_name — varchar(200)
  • main_purpose_name — varchar(200)
  • total_area_m2 — decimal(15, 4)
  • ground_floor_count — int
  • underground_floor_count — int
  • elevator_count — int — 승용승강기수(>0 → 엘리베이터 있음)
  • use_approval_date — date
  • source — varchar(40) — NOT NULL
  • content_hash — char(64)
  • synced_at — datetime(6) — NOT NULL
  • created_at — datetime(6) — NOT NULL
  • updated_at — datetime(6) — NOT NULL

bus_cities

  • city_code — varchar(10) — Primary key — NOT NULL
  • city_name — varchar(100) — NOT NULL
  • synced_at — datetime(6) — NOT NULL

bus_stations

  • id — bigint — Primary key — NOT NULL
  • node_id — varchar(40) — NOT NULL — 정류소 ID(외부 자연키)
  • node_no — varchar(20)
  • name — varchar(100) — NOT NULL
  • city_code — varchar(10) — Foreign key → bus_cities.city_code
  • latitude — decimal(10, 7)
  • longitude — decimal(10, 7)
  • source — varchar(40) — NOT NULL
  • content_hash — char(64)
  • synced_at — datetime(6) — NOT NULL
  • created_at — datetime(6) — NOT NULL
  • updated_at — datetime(6) — NOT NULL

convenience_stores

  • id — bigint — Primary key — NOT NULL
  • external_id — varchar(60) — NOT NULL — 상가업소번호 등(외부 자연키)
  • name — varchar(200) — NOT NULL
  • category — varchar(30) — CONVENIENCE_STORE | MART | OTHER
  • brand — varchar(100)
  • region_id — bigint — Foreign key → regions.id
  • address — varchar(500)
  • latitude — decimal(10, 7)
  • longitude — decimal(10, 7)
  • source — varchar(40) — NOT NULL
  • content_hash — char(64)
  • synced_at — datetime(6) — NOT NULL
  • created_at — datetime(6) — NOT NULL
  • updated_at — datetime(6) — NOT NULL

disabled_facilities

  • id — bigint — Primary key — NOT NULL
  • wfclt_id — varchar(60) — NOT NULL — 복지로 시설 ID (외부 자연키)
  • name — varchar(200) — NOT NULL
  • facility_type — varchar(150) — 시설유형 (faclTyCd 텍스트)
  • region_id — bigint — Foreign key → regions.id
  • address — varchar(500)
  • is_operating — tinyint(1) — NOT NULL — salStaDivCd=Y
  • latitude — decimal(10, 7)
  • longitude — decimal(10, 7)
  • source — varchar(40) — NOT NULL
  • content_hash — char(64)
  • synced_at — datetime(6) — NOT NULL
  • created_at — datetime(6) — NOT NULL
  • updated_at — datetime(6) — NOT NULL

hospitals

  • id — bigint — Primary key — NOT NULL
  • ykiho — varchar(400) — NOT NULL — 암호화 요양기호(외부 자연키)
  • name — varchar(200) — NOT NULL
  • class_code — varchar(2)
  • class_name — varchar(100) — 종합병원/병원/의원 등
  • region_id — bigint — Foreign key → regions.id
  • address — varchar(500)
  • phone — varchar(30)
  • latitude — decimal(10, 7)
  • longitude — decimal(10, 7)
  • source — varchar(40) — NOT NULL
  • content_hash — char(64) — 의미 필드 SHA-256(변경없으면 UPDATE 스킵)
  • synced_at — datetime(6) — NOT NULL
  • created_at — datetime(6) — NOT NULL
  • updated_at — datetime(6) — NOT NULL

properties

  • id — bigint — Primary key — NOT NULL
  • agent_user_id — bigint — Foreign key → users.id — NOT NULL
  • region_id — bigint — Foreign key → regions.id — NOT NULL
  • building_pk — varchar(30) — Foreign key → buildings.mgm_bldrgst_pk — buildings FK(선택)
  • title — varchar(200) — NOT NULL
  • description — text
  • property_type — enum('APARTMENT', 'OFFICETEL', 'VILLA', 'HOUSE', 'ROOM', 'OTHER') — NOT NULL
  • trade_type — enum('MONTHLY_RENT', 'JEONSE', 'SALE') — NOT NULL
  • status — enum('PENDING', 'ACTIVE', 'REJECTED') — NOT NULL
  • is_sold — tinyint(1) — NOT NULL
  • address — varchar(500) — NOT NULL
  • address_detail — varchar(200)
  • latitude — decimal(10, 7)
  • longitude — decimal(10, 7)
  • deposit_amount — bigint
  • monthly_rent — bigint
  • sale_amount — bigint
  • maintenance_fee — bigint
  • area_m2 — decimal(8, 2)
  • floor — int
  • total_floors — int
  • room_count — int
  • bathroom_count — int
  • available_from — date
  • view_count — bigint — NOT NULL
  • rejection_reason — text
  • created_at — datetime(6) — NOT NULL
  • updated_at — datetime(6) — NOT NULL
  • deleted_at — datetime(6)

property_accessibility

  • property_id — bigint — Foreign key → properties.id — NOT NULL
  • has_elevator — tinyint(1)
  • has_ramp — tinyint(1)
  • ramp_slope_ratio — decimal(5,2)
  • entrance_step_height_cm — decimal(6,2)
  • entrance_door_width_cm — decimal(6,2)
  • corridor_width_cm — decimal(6,2)
  • bathroom_door_width_cm — decimal(6,2)
  • has_accessible_bathroom — tinyint(1)
  • wheelchair_accessible — tinyint(1)
  • note — text
  • created_at — datetime(6) — NOT NULL
  • updated_at — datetime(6) — NOT NULL

property_accessibility_scores

  • property_id — bigint — Foreign key → properties.id — NOT NULL
  • total_score — decimal(5, 2) — NOT NULL
  • elevator_score — decimal(5, 2) — 엘리베이터 0~100 (NULL=정보없음)
  • wheelchair_score — decimal(5, 2)
  • ramp_score — decimal(5, 2) — 경사로 0~100 (NULL=정보없음)
  • hospital_score — decimal(5, 2) — 주변 병원 근접 0~100
  • school_score — decimal(5, 2) — 주변 학교 근접 0~100
  • convenience_score — decimal(5, 2) — 주변 편의시설 근접 0~100
  • disabled_facility_score — decimal(5, 2) — 주변 장애인편의시설 근접 0~100
  • senior_score — decimal(5, 2)
  • visual_impairment_score — decimal(5, 2)
  • hearing_impairment_score — decimal(5, 2)
  • grade — varchar(5) — NOT NULL
  • calculation_detail — json
  • calculated_at — datetime(6) — NOT NULL

property_favorites

  • property_id — bigint — Foreign key → properties.id — NOT NULL
  • user_id — bigint — Foreign key → users.id — NOT NULL
  • created_at — datetime(6) — NOT NULL

property_images

  • id — bigint — Primary key — NOT NULL
  • property_id — bigint — Foreign key → properties.id — NOT NULL
  • image_url — text — NOT NULL
  • image_type — varchar(50) — NOT NULL
  • sort_order — int — NOT NULL
  • created_at — datetime(6) — NOT NULL

regions

  • id — bigint — Primary key — NOT NULL
  • sido — varchar(50) — NOT NULL
  • sigungu — varchar(80)
  • eupmyeondong — varchar(80)
  • sido_cd — varchar(2) — 행정표준 시도코드
  • sigungu_cd — varchar(5) — 행정표준 시군구코드(5)
  • bjdong_cd — varchar(5) — 행정표준 법정동코드(5)
  • legal_dong_code — varchar(20)
  • latitude — decimal(10, 7)
  • longitude — decimal(10, 7)

reports

  • id — bigint — Primary key — NOT NULL
  • reporter_user_id — bigint — Foreign key → users.id — NOT NULL
  • property_id — bigint — Foreign key → properties.id
  • review_id — bigint — Foreign key → reviews.id
  • report_type — varchar(80) — NOT NULL
  • reason — text — NOT NULL
  • status — enum('RECEIVED','REVIEWING','RESOLVED','REJECTED') — NOT NULL
  • handled_by — bigint — Foreign key → users.id
  • handled_at — datetime(6)
  • result_message — text
  • created_at — datetime(6) — NOT NULL

reviews

  • id — bigint — Primary key — NOT NULL
  • property_id — bigint — Foreign key → properties.id — NOT NULL
  • user_id — bigint — Foreign key → users.id — NOT NULL
  • rating — tinyint — NOT NULL
  • accessibility_rating — tinyint
  • content — text — NOT NULL
  • created_at — datetime(6) — NOT NULL
  • updated_at — datetime(6) — NOT NULL
  • deleted_at — datetime(6)

schools

  • id — bigint — Primary key — NOT NULL
  • external_id — varchar(40) — NOT NULL — 표준학교코드(외부 자연키)
  • name — varchar(200) — NOT NULL
  • school_level — varchar(20) — ELEMENTARY | MIDDLE | HIGH | SPECIAL | OTHER
  • region_id — bigint — Foreign key → regions.id
  • address — varchar(500)
  • latitude — decimal(10, 7)
  • longitude — decimal(10, 7)
  • source — varchar(40) — NOT NULL
  • content_hash — char(64)
  • synced_at — datetime(6) — NOT NULL
  • created_at — datetime(6) — NOT NULL
  • updated_at — datetime(6) — NOT NULL

staging_external_records

  • id — bigint — Primary key — NOT NULL
  • job_id — bigint — Foreign key → sync_jobs.id
  • source — varchar(40) — NOT NULL
  • natural_key — varchar(200)
  • payload — json — NOT NULL — schema-on-read 원본 1건
  • fetched_at — datetime(6) — NOT NULL
  • processed_at — datetime(6) — 도메인 반영 시각(NULL=미처리)

sync_dead_letters

  • id — bigint — Primary key — NOT NULL
  • job_id — bigint — Foreign key → sync_jobs.id
  • source — varchar(40) — NOT NULL
  • natural_key — varchar(200) — 실패 레코드 자연키(있으면)
  • stage — enum('FETCH', 'TRANSFORM', 'LOAD') — NOT NULL
  • payload — json — 실패 시점 원본/중간 데이터
  • error_message — text
  • created_at — datetime(6) — NOT NULL
  • resolved_at — datetime(6) — 재처리 완료 시각(NULL=미해결)

sync_jobs

  • id — bigint — Primary key — NOT NULL
  • job_name — varchar(100) — NOT NULL — HOSPITAL_SYNC | SCHOOL_SYNC | STORE_SYNC | BUS_SYNC | BUILDING_SYNC
  • source — varchar(40) — NOT NULL — HIRA | EDU_SCHOOL | SBIZ_STORE | MOLIT_BUS | MOLIT_BLD
  • job_params — json — 시도코드/반경/페이지 등
  • status — enum('RUNNING', 'SUCCESS', 'FAILED', 'PARTIAL') — NOT NULL
  • started_at — datetime(6) — NOT NULL
  • finished_at — datetime(6)
  • fetched_count — bigint — NOT NULL — API에서 받은 row
  • upserted_count — bigint — NOT NULL — 도메인 반영 row
  • skipped_count — bigint — NOT NULL — content_hash 동일 → 스킵
  • failed_count — bigint — NOT NULL
  • last_cursor — varchar(500) — 재개용 커서(마지막 페이지/PK)
  • error_message — text

transaction_deals

  • id — bigint — Primary key — NOT NULL
  • deal_type — varchar(20) — NOT NULL — APT_TRADE | APT_RENT | RH_TRADE | RH_RENT
  • apt_name — varchar(200) — NOT NULL
  • sigungu_cd — varchar(5)
  • umd_cd — varchar(5)
  • umd_name — varchar(80) — 법정동명
  • jibun — varchar(20)
  • exclusive_area_m2 — decimal(8, 2) — 전용면적
  • floor — int
  • build_year — int
  • deal_amount — bigint — 거래/보증금 (원)
  • monthly_rent — bigint — 월세 (원, 전월세만)
  • deal_date — date
  • region_id — bigint — Foreign key → regions.id
  • latitude — decimal(10, 7) — 법정동 중심 근사
  • longitude — decimal(10, 7)
  • hospital_score — decimal(5, 2)
  • school_score — decimal(5, 2)
  • convenience_score — decimal(5, 2)
  • disabled_facility_score — decimal(5, 2)
  • total_score — decimal(5, 2) — 기본 가중치 주변시설 종합(목록 정렬용)
  • grade — varchar(5)
  • source — varchar(40) — NOT NULL
  • source_hash — char(64) — NOT NULL — 멱등 키 SHA-256(거래 식별 필드)
  • synced_at — datetime(6) — NOT NULL
  • created_at — datetime(6) — NOT NULL
  • updated_at — datetime(6) — NOT NULL

user_accessibility_weights

  • user_id — bigint — Foreign key → users.id — NOT NULL
  • elevator_weight — tinyint — NOT NULL
  • wheelchair_weight — tinyint — NOT NULL
  • ramp_weight — tinyint — NOT NULL
  • hospital_weight — tinyint — NOT NULL
  • school_weight — tinyint — NOT NULL
  • convenience_weight — tinyint — NOT NULL
  • disabled_facility_weight — tinyint — NOT NULL
  • updated_at — datetime(6) — NOT NULL

users

  • id — bigint — Primary key — NOT NULL
  • email — varchar(255) — NOT NULL
  • password_hash — varchar(255) — NOT NULL
  • name — varchar(100) — NOT NULL
  • phone — varchar(30)
  • role — enum('USER','AGENT','ADMIN') — NOT NULL
  • status — enum('ACTIVE','SUSPENDED','WITHDRAWN') — NOT NULL
  • last_login_at — datetime(6)
  • password_changed_at — datetime(6)
  • withdrawn_at — datetime(6)
  • created_at — datetime(6) — NOT NULL
  • updated_at — datetime(6) — NOT NULL
  • deleted_at — datetime(6)