데이터베이스응용

테이블 (13)

audit_logs

  • id — BIGSERIAL
  • actor_user_id — BIGINT
  • action — VARCHAR(100)
  • target_table — VARCHAR(100)
  • target_id — BIGINT
  • payload — JSONB
  • created_at — TIMESTAMP

calendar_events

  • id — BIGSERIAL
  • user_id — BIGINT — NOT NULL
  • title — VARCHAR(200) — NOT NULL
  • description — TEXT
  • event_date — TIMESTAMP — NOT NULL
  • recurrence — JSONB
  • event_type — VARCHAR(100)
  • created_at — TIMESTAMP

couples

  • id — BIGSERIAL — 기본 키
  • created_at — TIMESTAMP

health_metrics

  • id — BIGSERIAL — 기본 키
  • user_id — BIGSERIAL — 외래 키 → users.id
  • metric_date — DATE — NOT NULL
  • metric_key — VARCHAR(100) — NOT NULL
  • metric_value — NUMERIC
  • unit — VARCHAR(30)
  • meta — JSONB
  • created_at — TIMESTAMP

intake_logs

  • id — BIGSERIAL — 기본 키
  • user_id — BIGSERIAL — 외래 키 → users.id
  • supplement_id (id3) — BIGSERIAL — 외래 키 → supplements.id
  • scheduled_date — TIMESTAMP
  • taken_at — TIMESTAMP
  • status — intake_status_t
  • dose — VARCHAR(200)
  • note — TEXT
  • created_at — TIMESTAMP
  • updated_at — TIMESTAMP

lab_results

  • id — BIGSERIAL
  • user_id — BIGINT — NOT NULL
  • test_date — DATE — NOT NULL
  • test_type — VARCHAR(200)
  • result_value — VARCHAR(200)
  • unit — VARCHAR(50)
  • reference_range — VARCHAR(100)
  • notes — TEXT
  • attachments — JSONB
  • created_at — TIMESTAMP

notifications

  • id — BIGSERIAL
  • user_id — BIGINT — NOT NULL
  • kind — VARCHAR(100)
  • payload — JSONB
  • scheduled_at — TIMESTAMP
  • sent_at — TIMESTAMP
  • status — VARCHAR(50)
  • created_at — TIMESTAMP

profiles

  • id — BIGSERIAL — 기본 키
  • user_id — BIGSERIAL — 외래 키 → users.id
  • is_primary — BOOLEAN
  • mode — user_mode_t
  • desired_dday — DATE
  • cycle_length — INT
  • period_length — INT
  • lmp_date — DATE
  • expected_ovulation_date — DATE
  • is_pregnant — BOOLEAN
  • pregnancy_confirmed_date — DATE
  • weight_kg — NUMERIC(5,2)
  • height_cm — INT
  • smoking — BOOLEAN
  • alcohol — BOOLEAN
  • notes — TEXT
  • meta — JSONB
  • created_at — TIMESTAMP
  • updated_at — TIMESTAMP

supplement_interactions

  • id — BIGSERIAL
  • supplement_a_id — BIGINT — NOT NULL
  • supplement_b_id — BIGINT — NOT NULL
  • description — TEXT
  • severity — SMALLINT
  • UNIQUE(supplement_a_id, — supplement_b_id)

supplement_recommendations

  • id — BIGSERIAL — 기본 키
  • supplement_id — BIGSERIAL — 외래 키 → supplements.id
  • target — supplement_target_t
  • stage — rec_stage_t — NOT NULL
  • min_weeks — INT
  • max_weeks — INT
  • notes — TEXT
  • recommended_dose — VARCHAR(200)
  • priority — SMALLINT
  • created_at — TIMESTAMP

supplements

  • id — BIGSERIAL — 기본 키
  • code — VARCHAR(64)
  • name — VARCHAR(200) — NOT NULL
  • category — VARCHAR(100)
  • description — TEXT
  • common_dose — VARCHAR(200)
  • caution — TEXT
  • contraindications — TEXT
  • ingredients — TEXT
  • extra — JSONB
  • created_at — TIMESTAMP
  • updated_at — TIMESTAMP

user_supplement_plans

  • id — BIGSERIAL — 기본 키
  • user_id — BIGSERIAL — 외래 키 → users.id
  • supplement_id — BIGSERIAL — 외래 키 → supplements.id
  • start_date — DATE
  • end_date — DATE
  • frequency — VARCHAR(100)
  • times — JSONB
  • dose — VARCHAR(200)
  • active — BOOLEAN
  • source — VARCHAR(100)
  • created_at — TIMESTAMP
  • updated_at — TIMESTAMP
  • UNIQUE(user_id, — supplement_id,

users

  • id — BIGSERIAL — 기본 키
  • couple_id — BIGSERIAL — 외래 키 → couples.id
  • email — VARCHAR(254) — NOT NULL
  • password_hash — VARCHAR(512) — NOT NULL
  • full_name — VARCHAR(200)
  • gender — gender_t
  • birth_date — DATE
  • created_at — TIMESTAMP
  • updated_at — TIMESTAMP