2-1) Identity / Tenant Domain

아래는 기존 ERD를 **실제 구축/운영에 필요한 테이블까지 포함해서 개선한 버전**입니다.

카테고리: guide | 읽기시간: 12분 | 원문: 다운로드

아래는 기존 ERD를 실제 구축/운영에 필요한 테이블까지 포함해서 개선한 버전입니다.

1. 개선 ERD (Mermaid)
2. 전체 테이블 데이터사전(테이블명/엔터티/속성 설명 표) — _추가된 테이블 포함 전부_

1) 개선 ERD (Mermaid)

erDiagram

%% =========================
%% IDENTITY / TENANT DOMAIN
%% =========================
TENANT {
  uuid tenant_id PK
  string tenant_name
  string tenant_type
  string status
  datetime created_at
}

CUSTOMER {
  uuid customer_id PK
  uuid tenant_id FK
  string customer_name
  string billing_type
  string status
  datetime created_at
}

USER_ACCOUNT {
  uuid user_id PK
  uuid tenant_id FK
  string email
  string display_name
  string status
  datetime created_at
}

ROLE {
  uuid role_id PK
  string role_name
  string description
}

USER_ROLE {
  uuid user_role_id PK
  uuid user_id FK
  uuid role_id FK
  datetime granted_at
}

API_KEY {
  uuid api_key_id PK
  uuid tenant_id FK
  string key_hash
  string status
  datetime issued_at
  datetime expires_at
}

%% =========================
%% SATELLITE / ASSET DOMAIN
%% =========================
SATELLITE {
  uuid satellite_id PK
  string name
  int norad_id
  string cospar_id
  string orbit_type
  string mission_type
  string status
  datetime launch_date
  datetime eol_estimated_at
}

SATELLITE_PROFILE_SET {
  uuid profile_set_id PK
  uuid satellite_id FK
  uuid imaging_profile_id FK
  uuid comm_profile_id FK
  uuid constraint_profile_id FK
  datetime effective_from
  datetime effective_to
}

IMAGING_PROFILE {
  uuid imaging_profile_id PK
  string sensor_type
  string mode_code
  float gsd_m
  float swath_km
  float max_off_nadir_deg
  float slew_rate_deg_per_sec
  float min_sun_elev_deg
  boolean night_support
}

COMM_PROFILE {
  uuid comm_profile_id PK
  string uplink_freq
  string downlink_freq
  float data_rate_mbps
  string modulation
  string crypto_mode
}

CONSTRAINT_PROFILE {
  uuid constraint_profile_id PK
  float max_daily_imaging_time_min
  float max_daily_downlink_volume_gb
  float storage_capacity_gb
}

%% =========================
%% ORBIT DOMAIN
%% =========================
ORBIT_SOURCE {
  uuid orbit_source_id PK
  string source_type
  string provider
  string status
  datetime last_ingested_at
}

TLE {
  uuid tle_id PK
  uuid satellite_id FK
  uuid orbit_source_id FK
  datetime epoch_time
  string line1
  string line2
  datetime ingested_at
}

VISIBILITY_PASS {
  uuid pass_id PK
  uuid satellite_id FK
  uuid station_id FK
  datetime aos_time
  datetime los_time
  float max_elevation_deg
  datetime predicted_at
  uuid orbit_source_id FK
}

%% =========================
%% TASKING DOMAIN
%% =========================
IMAGING_REQUEST {
  uuid request_id PK
  uuid tenant_id FK
  uuid customer_id FK
  geometry aoi_geom
  datetime time_window_start
  datetime time_window_end
  int priority
  datetime sla_due_time
  string product_level
  float max_cloud_pct
  float max_off_nadir_deg
  string status
  string reason_code
  uuid correlation_id
  datetime created_at
}

IMAGING_CANDIDATE {
  uuid candidate_id PK
  uuid request_id FK
  uuid satellite_id FK
  uuid pass_id FK
  string mode_code
  datetime predicted_start
  datetime predicted_end
  float score_value
  boolean feasible_flag
  string infeasible_reason
  datetime created_at
}

%% =========================
%% SCHEDULING DOMAIN
%% =========================
SCHEDULE_RUN {
  uuid schedule_run_id PK
  datetime horizon_start
  datetime horizon_end
  datetime freeze_from
  datetime freeze_to
  string objective_policy
  string status
  datetime created_at
}

SCHEDULE_SLOT {
  uuid slot_id PK
  uuid schedule_run_id FK
  uuid satellite_id FK
  uuid candidate_id FK
  datetime start_time
  datetime end_time
  string slot_type
  string state
  int version
  boolean locked
  uuid superseded_by_slot_id
  datetime created_at
}

SCHEDULE_SLOT_HISTORY {
  uuid slot_hist_id PK
  uuid slot_id FK
  string action
  string prev_state
  string new_state
  uuid actor_user_id FK
  datetime changed_at
  string reason_code
}

%% =========================
%% GROUND DOMAIN
%% =========================
GROUND_STATION {
  uuid station_id PK
  string name
  float latitude
  float longitude
  float altitude
  string status
}

ANTENNA_RESOURCE {
  uuid antenna_id PK
  uuid station_id FK
  string antenna_name
  string band_capability
  string status
}

CONTACT_SESSION {
  uuid session_id PK
  uuid satellite_id FK
  uuid station_id FK
  uuid antenna_id FK
  datetime planned_start
  datetime planned_end
  datetime actual_start
  datetime actual_end
  string session_type
  string status
}

%% =========================
%% COMMAND DOMAIN
%% =========================
COMMAND_DICTIONARY {
  uuid cmd_dict_id PK
  string command_type
  string schema_version
  string validation_rules_uri
  string status
}

COMMAND_REQUEST {
  uuid cmd_req_id PK
  uuid satellite_id FK
  uuid tenant_id FK
  uuid user_id FK
  uuid cmd_dict_id FK
  string command_type
  json payload_json
  int priority
  string status
  uuid correlation_id
  datetime requested_at
  datetime approved_at
  uuid approved_by_user_id FK
}

COMMAND_EXECUTION {
  uuid cmd_exec_id PK
  uuid cmd_req_id FK
  uuid session_id FK
  datetime sent_at
  datetime ack_at
  string result
  string error_code
  string raw_log_uri
}

%% =========================
%% DOWNLINK DOMAIN
%% =========================
DOWNLINK_REQUEST {
  uuid dl_req_id PK
  uuid product_id FK
  uuid tenant_id FK
  datetime required_by_time
  int priority
  float volume_est_gb
  string status
  datetime created_at
}

DOWNLINK_PLAN {
  uuid dl_plan_id PK
  uuid session_id FK
  uuid dl_req_id FK
  float planned_rate_mbps
  float planned_volume_gb
  string status
  datetime created_at
}

%% =========================
%% PRODUCT / PROCESSING / DELIVERY DOMAIN
%% =========================
DATA_PRODUCT {
  uuid product_id PK
  uuid request_id FK
  uuid tenant_id FK
  uuid satellite_id FK
  datetime sensing_time
  geometry footprint_geom
  string level
  string processing_status
  string format
  string uri
  string checksum
  datetime created_at
}

PRODUCT_DERIVATION {
  uuid derivation_id PK
  uuid parent_product_id FK
  uuid child_product_id FK
  string derivation_type
  datetime created_at
}

PROCESS_JOB {
  uuid job_id PK
  uuid product_id FK
  string pipeline_name
  string pipeline_version
  string status
  datetime started_at
  datetime finished_at
  string log_uri
}

CATALOG_ITEM {
  uuid catalog_id PK
  uuid product_id FK
  uuid tenant_id FK
  datetime indexed_at
  string stac_item_uri
}

DELIVERY_ORDER {
  uuid delivery_id PK
  uuid product_id FK
  uuid tenant_id FK
  string method
  string destination
  string status
  datetime delivered_at
}

BILLING_RECORD {
  uuid billing_id PK
  uuid tenant_id FK
  uuid customer_id FK
  uuid request_id FK
  uuid product_id FK
  string charge_type
  float amount
  string currency
  datetime billed_at
}

%% =========================
%% AUDIT DOMAIN (CROSS-CUTTING)
%% =========================
AUDIT_LOG {
  uuid audit_id PK
  uuid tenant_id FK
  string actor_type
  uuid actor_id
  string action
  string entity_type
  uuid entity_id
  uuid correlation_id
  datetime event_time
  json metadata_json
}

%% =========================
%% RELATIONSHIPS
%% =========================
TENANT ||--o{ CUSTOMER : has
TENANT ||--o{ USER_ACCOUNT : has
USER_ACCOUNT ||--o{ USER_ROLE : granted
ROLE ||--o{ USER_ROLE : includes
TENANT ||--o{ API_KEY : issues

SATELLITE ||--o{ SATELLITE_PROFILE_SET : has
SATELLITE_PROFILE_SET }o--|| IMAGING_PROFILE : uses
SATELLITE_PROFILE_SET }o--|| COMM_PROFILE : uses
SATELLITE_PROFILE_SET }o--|| CONSTRAINT_PROFILE : uses

ORBIT_SOURCE ||--o{ TLE : provides
SATELLITE ||--o{ TLE : has
ORBIT_SOURCE ||--o{ VISIBILITY_PASS : basis_for
SATELLITE ||--o{ VISIBILITY_PASS : generates
GROUND_STATION ||--o{ VISIBILITY_PASS : observes

TENANT ||--o{ IMAGING_REQUEST : owns
CUSTOMER ||--o{ IMAGING_REQUEST : submits
IMAGING_REQUEST ||--o{ IMAGING_CANDIDATE : generates
SATELLITE ||--o{ IMAGING_CANDIDATE : evaluated_for
VISIBILITY_PASS ||--o{ IMAGING_CANDIDATE : based_on

SCHEDULE_RUN ||--o{ SCHEDULE_SLOT : produces
SATELLITE ||--o{ SCHEDULE_SLOT : scheduled_on
IMAGING_CANDIDATE ||--o{ SCHEDULE_SLOT : selected_from
SCHEDULE_SLOT ||--o{ SCHEDULE_SLOT_HISTORY : changes

GROUND_STATION ||--o{ ANTENNA_RESOURCE : has
GROUND_STATION ||--o{ CONTACT_SESSION : hosts
ANTENNA_RESOURCE ||--o{ CONTACT_SESSION : uses
SATELLITE ||--o{ CONTACT_SESSION : contacts

COMMAND_DICTIONARY ||--o{ COMMAND_REQUEST : validates
TENANT ||--o{ COMMAND_REQUEST : owns
USER_ACCOUNT ||--o{ COMMAND_REQUEST : requests
SATELLITE ||--o{ COMMAND_REQUEST : receives
COMMAND_REQUEST ||--o{ COMMAND_EXECUTION : executed_as
CONTACT_SESSION ||--o{ COMMAND_EXECUTION : during

IMAGING_REQUEST ||--o{ DATA_PRODUCT : results_in
TENANT ||--o{ DATA_PRODUCT : owns
SATELLITE ||--o{ DATA_PRODUCT : captures
DATA_PRODUCT ||--o{ PRODUCT_DERIVATION : parent
DATA_PRODUCT ||--o{ PRODUCT_DERIVATION : child
DATA_PRODUCT ||--o{ PROCESS_JOB : processed_by
DATA_PRODUCT ||--o{ CATALOG_ITEM : indexed_as
DATA_PRODUCT ||--o{ DELIVERY_ORDER : delivered_via

DATA_PRODUCT ||--o{ DOWNLINK_REQUEST : requires
DOWNLINK_REQUEST ||--o{ DOWNLINK_PLAN : planned_as
CONTACT_SESSION ||--o{ DOWNLINK_PLAN : executes_in

TENANT ||--o{ BILLING_RECORD : billed
CUSTOMER ||--o{ BILLING_RECORD : billed_for

TENANT ||--o{ AUDIT_LOG : logs

2) 전체 테이블 설명 표 (데이터 사전)

아래는 위 ERD에 포함된 모든 테이블을 같은 형식(테이블명/엔터티/속성 설명)으로 정리한 것입니다.

> 표가 길어 도메인별로 나눴습니다. “속성(컬럼)”은 *핵심 컬럼 중심*으로 설명합니다(필요하면 타입/제약/인덱스까지 확장 가능).

2-1) Identity / Tenant Domain

TENANT

CUSTOMER

USER_ACCOUNT

ROLE

USER_ROLE

API_KEY

2-2) Satellite / Asset Domain

SATELLITE

SATELLITE_PROFILE_SET

IMAGING_PROFILE

COMM_PROFILE

CONSTRAINT_PROFILE

2-3) Orbit Domain

ORBIT_SOURCE

TLE

VISIBILITY_PASS

2-4) Tasking Domain

IMAGING_REQUEST

IMAGING_CANDIDATE

2-5) Scheduling Domain

SCHEDULE_RUN

SCHEDULE_SLOT

SCHEDULE_SLOT_HISTORY

2-6) Ground Domain

GROUND_STATION

ANTENNA_RESOURCE

CONTACT_SESSION

2-7) Command Domain

COMMAND_DICTIONARY

COMMAND_REQUEST

COMMAND_EXECUTION

2-8) Downlink Domain

DOWNLINK_REQUEST

DOWNLINK_PLAN

2-9) Product / Processing / Catalog / Delivery / Billing Domain

DATA_PRODUCT

PRODUCT_DERIVATION

PROCESS_JOB

CATALOG_ITEM

DELIVERY_ORDER

BILLING_RECORD

2-10) Audit Domain

AUDIT_LOG

다음 단계(원하시면 바로 생성)

원하시는 수준까지 “물리 설계”로 끌고 갈 수 있습니다. 예를 들어:

• 컬럼 타입(정확한 PostgreSQL 타입), NOT NULL, DEFAULT
• UQ/FK/Index/GIST/Exclusion 제약
• 파티셔닝 정책(telemetry/pass/audit)
• 도메인별 DB 분리(마이크로서비스 DB) 기준 스키마