ReadingLogERD

Tables (13)

author_aliases

  • alias_id — BIGSERIAL
  • work_id — BIGINT — Foreign key → works.work_id — NOT NULL
  • alias_name — VARCHAR(50) — NOT NULL

book_editions

  • edition_id — BIGSERIAL
  • work_id — BIGINT — Foreign key → works.work_id — NOT NULL
  • isbn — VARCHAR(20)
  • title — VARCHAR(100) — NOT NULL
  • publisher — VARCHAR(100)
  • cover_url — TEXT
  • google_books_id — VARCHAR(50)
  • categories — TEXT
  • created_at — TIMESTAMP

comments

  • comment_id — BIGSERIAL
  • post_id — BIGINT — Foreign key → posts.post_id — NOT NULL
  • user_id — UUID — Foreign key → users.user_id — NOT NULL
  • content — TEXT — NOT NULL
  • created_at — TIMESTAMP

conversation

  • id — BIGSERIAL
  • type — VARCHAR(20) — NOT NULL
  • last_message_id — BIGINT — Foreign key → message.id
  • last_message_at — TIMESTAMP
  • created_at — TIMESTAMP
  • updated_at — TIMESTAMP

conversation_participant

  • conversation_id — BIGINT — Foreign key → conversation.id — NOT NULL
  • user_id — UUID — Foreign key → users.user_id — NOT NULL
  • last_read_message_id — BIGINT
  • last_read_at — TIMESTAMP
  • created_at — TIMESTAMP

follows

  • id — BIGSERIAL
  • follower_id — UUID — Foreign key → users.user_id — NOT NULL
  • following_id — UUID — Foreign key → users.user_id — NOT NULL
  • created_at — TIMESTAMP

message

  • id — BIGSERIAL
  • conversation_id — BIGINT — Foreign key → conversation.id — NOT NULL
  • sender_user_id — UUID — Foreign key → users.user_id — NOT NULL
  • content — TEXT — NOT NULL
  • message_type — VARCHAR(20) — NOT NULL
  • created_at — TIMESTAMP
  • updated_at — TIMESTAMP

notifications

  • notification_id — BIGSERIAL
  • receiver_id — UUID — Foreign key → users.user_id — NOT NULL
  • sender_id — UUID — Foreign key → users.user_id — NOT NULL
  • notification_type — VARCHAR(30) — NOT NULL
  • target_id — BIGINT — NOT NULL
  • message — TEXT — NOT NULL
  • is_read — BOOLEAN — NOT NULL
  • created_at — TIMESTAMP

post_images

  • image_id — BIGSERIAL
  • post_id — BIGINT — Foreign key → posts.post_id — NOT NULL
  • image_url — TEXT — NOT NULL

post_reactions

  • reaction_id — BIGSERIAL
  • post_id — BIGINT — Foreign key → posts.post_id — NOT NULL
  • user_id — UUID — Foreign key → users.user_id — NOT NULL
  • reaction_type — VARCHAR(30) — NOT NULL
  • created_at — TIMESTAMP

posts

  • post_id — BIGSERIAL
  • user_id — UUID — Foreign key → users.user_id — NOT NULL
  • work_id — BIGINT — Foreign key → works.work_id — NOT NULL
  • edition_id — BIGINT — Foreign key → book_editions.edition_id — NOT NULL
  • sentence — TEXT — NOT NULL
  • comment — TEXT — EX
  • filter_type — VARCHAR(20)
  • underline_style — VARCHAR(20) — NOT NULL
  • created_at — TIMESTAMP

users

  • user_id — UUID
  • email — VARCHAR(30) — NOT NULL
  • password_hash — VARCHAR — NOT NULL
  • nickname — VARCHAR(15) — NOT NULL
  • profile_image_url — TEXT
  • intro — VARCHAR(100)
  • bio — VARCHAR(1000)
  • created_at — TIMESTAMP
  • updated_at — TIMESTAMP

works

  • work_id — BIGSERIAL
  • normalized_title — VARCHAR(100) — NOT NULL
  • normalized_author — VARCHAR(50) — NOT NULL
  • created_at — TIMESTAMP