[TIL] Applying TodayPoor ERD feedback

What I did today

• Discussion regarding feedback

• Feedback ERD reflection and correction

Meeting

In the initial design, we focused on quickly reflecting the functionality, but today we re-examined whether each table and field is really necessary, whether there is duplicate data, and whether the design was excessive at the MVP stage.

• Reflection of PRD-based ERD feedback and final design
  • Separate OCR result table

    • Initially, ocr_raw_text is stored together in the EXPENSE table.

    • problem

      • OCR results are closer to the results of analysis to create consumption details, rather than the consumption details themselves.

      • The original OCR text may be long, and there is a possibility that OCR accuracy or analysis results may be managed separately in the future.

    • Solved: So separate table OCR_RESULT
      • With this separation, EXPENSE focuses on confirmed consumption data, and OCR_RESULT is responsible for analysis results, making the role clearer.
  • Remove AI prompt table

    • Initially, the AI_PROMPT_TEMPLATE table was added considering AI functions.

    • Prompt type, system prompt, user prompt template, etc. were managed in the DB.

    • Problem
      However, after reviewing it again, we found that the following features are not available in the current MVP.

      • Modify prompt in admin page

      • Prompt versioning

      • A/B testing

      • Change prompts without deployment during operation

      In other words, the reason for managing prompts in DB is not yet clear.

    • Resolution: Prompts will be managed inside the backend code, and only the results generated by AI will be stored in the DB.
      • Ultimately, the AI_PROMPT_TEMPLATE table is removed and only AI_RESULT is kept.- Remove title_text from RANKING_RESULT

    • Initially, the field title_text is placed in RANKING_RESULT.

    • problem

      • However, RANKING_RESULT already references POOR_TITLE through title_id, so the title name can be imported as POOR_TITLE.name.

      • If title_text is saved separately, the same title information is saved in two places.

    • Solved: So title_text is removed, and RANKING_RESULT.title_id refers to the name of POOR_TITLE.
  • Changed AI result reference structure

    • Initially, we try to store roast_message directly in RANKING_RESULT as a string.

    • problem
      • However, since the vitriolic phrase is a result generated by AI, it was judged more appropriate to separate the ranking results from the AI results.
    • Solved: So RANKING_RESULT is modified to refer to AI_RESULT.
  • Add invite code expiration date
    • problem
      • Considering the group invitation function, it was not deemed appropriate for the invitation code to be maintained indefinitely.
    • Solved: Add invitation code expiration date to GROUP table.
      • This field allows you to disable expired invitation codes.
• Final ERD
  
  ERD Mermaid Code

    ```mermaid
    erDiagram
        USER ||--o{ SOCIAL_ACCOUNT : has
        USER ||--o{ GROUP_MEMBER : joins
        GROUP ||--o{ GROUP_MEMBER : has
  
        USER ||--o{ EXPENSE : uploads
        GROUP ||--o{ EXPENSE : contains
  
        EXPENSE ||--|| OCR_RESULT : has
  
        GROUP ||--o{ RANKING_RESULT : has
        USER ||--o{ RANKING_RESULT : ranked
        POOR_TITLE ||--o{ RANKING_RESULT : assigned
  
        RANKING_RESULT ||--|| AI_RESULT : has
  
        USER {
            UUID id PK
            string nickname
            string profile_image_url
            datetime created_at
            datetime updated_at
            datetime deleted_at
        }
  
        SOCIAL_ACCOUNT {
            UUID id PK
            UUID user_id FK
            enum provider
            string provider_user_id
            string email
            datetime connected_at
        }
  
        GROUP {
            UUID id PK
            string name
            string invite_code
            datetime invite_code_expires_at
            UUID owner_id FK
            datetime created_at
            datetime updated_at
            datetime deleted_at
        }
  
        GROUP_MEMBER {
            UUID id PK
            UUID user_id FK
            UUID group_id FK
            enum role
            datetime joined_at
            datetime deleted_at
        }
  
        EXPENSE {
            UUID id PK
            UUID user_id FK
            UUID group_id FK
            enum category
            int amount
            string merchant
            string memo
            string image_url
            enum visibility
            datetime spent_at
            datetime created_at
            datetime updated_at
            datetime deleted_at
        }
  
        OCR_RESULT {
            UUID id PK
            UUID expense_id FK
            text raw_text
            datetime created_at
        }
  
        RANKING_RESULT {
            UUID id PK
            UUID group_id FK
            UUID user_id FK
            UUID title_id FK
            UUID ai_result_id FK
            date ranking_date
            int total_amount
            int rank_no
            datetime created_at
        }
  
        POOR_TITLE {
            UUID id PK
            string name
            string code
            enum condition_type
        }
  
        AI_RESULT {
            UUID id PK
            text input_data
            text output_text
            string model_name
            int token_usage
            datetime created_at
        }
    ```
  

  

What to do next

• Writing API specifications

• Design of common parts of the project

  • Common Response

  • Error handling- Code/commit convention