Files
epolicy-dash-back/.agent/references/dashboard-domain-contract.md

9.8 KiB

Project Data Management Premises

  • Do not implement a frontend administration page at this stage. Do not add create, update, or delete features for individual information records unless a future approved requirement explicitly expands the scope.
  • Supply and maintain the data required by this project through Excel-based bulk upload instead of individual administration features.
  • Keep user and file data outside the Excel bulk-import scope. Import the remaining project data from one Excel workbook containing multiple sheets.
  • The Excel workbook has eleven required table-named sheets: t_wave, t_wave_info, t_survey, t_survey_item, t_part, t_part_item, t_part_item_sub, t_part_item_base, t_part_item_chart, t_part_item_chart_highlight, and t_part_item_data. Headers use database names and constrained headers list every allowed value in parentheses. The workbook must contain all referenced logical parents.
  • Omit every auto-increment _idx column from Excel. Replacement imports regenerate identities.
  • Reject duplicate Excel keys: Wave info (wave_cd,wave_info_order); survey item (wave_cd,survey_cd,survey_item_order); base (wave_cd,part_cd,part_item_cd,survey_cd,part_item_sub_cd); chart/highlight omit survey_cd; data adds part_item_data_order to base.
  • Define the Excel import/export schema once with KriCommon ExcelColumn DTOs. Use KriCommon ExcelMapper for workbook mapping and keep only project-specific type, value, and cross-sheet relationship rules in a consumer-local validator; do not duplicate a general-purpose Excel parser.
  • Excel endpoints are POST /api/v1/excel/import, GET /api/v1/excel/template, GET /api/v1/excel/waves/{waveCd}/export, and DELETE /api/v1/excel/waves/{waveCd}. Downloads use the same eleven sheets, order, and headers; a missing Wave exports an empty workbook.
  • Imports atomically replace every included Wave: validate first, delete all eleven managed tables, then insert. Failures restore prior data. Standalone reset uses the same child-to-root deletion and retains t_file metadata and physical files, allowing temporary orphans until that wave_cd is reimported.
  • A reference-file upload writes both the physical file and its t_file metadata. Accept fileTitle and fileOrder as multipart request fields, derive file_type from the lowercase filename extension, and keep the original filename as file_name.
  • For reference-file uploads, accept waveCd as a path variable and store files under {kri.file.base-dir}/References/{waveCd} using their original names. Treat (wave_cd, file_order) as the upload logical key: repeat uploads preserve file_idx, replace metadata and the physical file, and remove the prior filename after commit. Reject a filename already used by another order in the same Wave.
  • Reject a reference-file upload when its waveCd does not exist. Coordinate the database transaction and filesystem replacement so a database rollback restores the previous file or removes a newly created file.

Dashboard Data Model Contract

  • Treat t_wave as the root of the dashboard hierarchy. Most child data is logically scoped by wave_cd.
  • Preserve this logical hierarchy: t_wave owns wave information, surveys, file metadata, and parts; a survey owns survey items; a part owns part items; and a part item owns optional sub-items, base counts, and result data.
  • Do not add physical database foreign keys. The backend must validate all logical parent-child relationships during Excel import and any future data mutation.
  • Treat (wave_cd, part_cd, part_item_cd) as the logical item key. Add survey_cd to identify a survey-specific result set and add part_item_sub_cd when a sub-item exists.
  • t_part_item_sub has no physical primary key in the planned schema. Treat (wave_cd, part_cd, part_item_cd, part_item_sub_cd) as its logical identifier and lookup key.
  • Store the nullable t_part_item_sub.part_item_sub_comment value and expose it as partItemSubComment in Part-detail sub-item responses.
  • A normal item has no t_part_item_sub rows, and both base and data rows must use part_item_sub_cd = NULL. Never use 0 as a substitute for this NULL value.
  • An item with sub-items has one or more t_part_item_sub rows. Every non-null part_item_sub_cd in its base and data rows must reference a sub-item belonging to the same (wave_cd, part_cd, part_item_cd).
  • Every survey code used by t_part_item_base or t_part_item_data must belong to the same Wave. Survey items must likewise belong to an existing (wave_cd, survey_cd).
  • Store four VARCHAR(8) survey palette colors: required survey_color plus nullable highlight, negative, and negative-highlight colors. Include all in Excel/API; fall back to default, default, and negative/highlight/default respectively.
  • Use t_part_item_base as the preferred source for determining which survey audiences apply to an item. Display the single matching survey_alias when only one audience applies; display 공통 when all defined audiences apply.
  • Store an optional display string in t_part_item_base.base_text (VARCHAR(128)). Do not reconstruct it from base_count, and do not retain the removed base_pre_text or base_post_text fields in entities, Excel contracts, or query assembly.
  • Use the corresponding *_order column for API and screen ordering: wave_info_order, survey_order, survey_item_order, file_order, part_order, part_item_order, and part_item_data_order. Use part_item_sub_cd as the default sub-item order because the schema has no separate sub-item order column.
  • Use the same t_part_item_data rows for both chart and statistical-table views. is_chart_visible is a required Boolean column with database default 1; statistical tables use every row, while charts omit categories and matching values whose flag is false. Do not introduce a separate table-data source that could diverge from chart values.
  • Chart/highlight key is (wave_cd,part_cd,part_item_cd,part_item_sub_cd); one config applies to all survey series in its group. Chart type/gap default to BAR/NONE; colors come from survey palettes.
  • Resolve chart highlights independently and return mode/value without calculating highlighted categories. Supported modes are MAX_VALUE, LAST_VISIBLE, and SPECIFIED; only SPECIFIED accepts a non-null comma-separated integer list. Highlight colors come only from the survey palette.
  • Store item and sub-item negative mode/order independently (DEFAULT, NEGATIVE, SPECIFIED); sub settings never inherit parent settings. Only SPECIFIED accepts an order list. Return these settings and survey palette colors so the frontend classifies rows and applies final colors.
  • Treat null part_item_sub_cd values as equal when matching normal-item chart/highlight settings. Validate Excel logical-key uniqueness, null sub codes for normal items, negative/highlight order syntax, and palette colors as #RRGGBB.
  • Avoid N+1 query patterns when assembling a Wave dashboard. Fetch the required Part, Item, Sub, Base, and Data ranges in batches and assemble the response hierarchy in the backend.

Frontend API Contract

  • Provide screen-oriented APIs rather than exposing table-shaped CRUD endpoints. Use GET /api/v1/waves for the header Wave list, GET /api/v1/waves/{waveCd} for the Wave overview/navigation/download model, and GET /api/v1/waves/{waveCd}/parts/{partCd} for all items and result data in one Part.
  • Return every Wave in the header list and include both isOpen and a backend-computed accessible value. A user with userRole < 2010 may access open and closed Waves; a user with userRole >= 2010, or with no role, may access only Waves whose is_open is true.
  • Revalidate Wave access on both Wave-detail and Part-detail requests. Throw a KriCommon BusinessException with forbidden status when a restricted user requests a closed Wave; frontend visibility is not an authorization boundary.
  • Assemble normal and sub-item results through one resultGroups response shape. A normal item has one group with subItem = null; an item with sub-items has one group per Sub in part_item_sub_cd order.
  • Return categories as objects containing order, label, and chartVisible, alongside complete survey value series. The frontend uses all category/value indexes for statistical tables and filters matching indexes only for chart rendering. Reject Excel imports when category labels, display orders, or chart-visibility flags differ between survey series in the same Item/Sub result group.
  • Expose effective color, highlightColor, negativeColor, and negativeHighlightColor in both Wave-detail surveys and Part-detail series. Return item/sub-item negativeMode and nullable negativeOrder; the frontend classifies and colors chart rows.
  • Return the stored nullable t_part_item_base.base_text value unchanged as Part-detail series[].baseText; baseCount remains a separate numeric field.
  • Return series[].chartGapMode; the frontend applies NONE, ALL, or LAST_VISIBLE, where the last mode uses the greatest visible part_item_data_order.
  • When importing part_item_data_value, round to scale 10 with RoundingMode.HALF_UP before validating and storing it as DECIMAL(14,10). Reject only values that still exceed precision 14 after rounding.
  • When part_item_comment is blank in Excel, store an empty string rather than NULL because the database column is required. Preserve the existing NULL semantics of other optional Excel fields.
  • Manage chart types as code enums in t_part_item_chart. The initial supported type is BAR; Excel accepts its label bar case-insensitively, the database stores code BAR, and Part-detail series responses return code BAR. Reject unsupported labels during Excel validation.
  • Build file download URLs in the backend from the configured file resource path and the stored file metadata. Do not require the frontend to construct filesystem paths.