maru4
Tables (18)
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
facilities
- id — bigint — Primary key — NOT NULL
- region_id — bigint — Foreign key → regions.id — soft 연결(외부 reference). properties로의 FK 없음.
- facility_type — varchar(20) — NOT NULL — HOSPITAL | SCHOOL | CONVENIENCE | DISABLED_FACILITY
- external_id — varchar(400) — NOT NULL — 원천 자연키(ykiho/wfclt_id/external_id)
- name — varchar(200) — NOT NULL
- address — varchar(500)
- latitude — decimal(10, 7)
- longitude — decimal(10, 7)
- attributes — json — 타입별 부가정보(className/schoolLevel/brand/subType 등)
- 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)
- 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
- handled_by — bigint — Foreign key → users.id
- report_type — varchar(80) — NOT NULL
- reason — text — NOT NULL
- status — enum('RECEIVED','REVIEWING','RESOLVED','REJECTED') — NOT NULL
- 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)
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
- region_id — bigint — Foreign key → regions.id
- 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
- 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)