Database Schema

Database Schema

This reference reflects workers/bookmarks-api-worker/migrations.

Tables and keys

Table	Primary Key	Foreign Keys
users	id	None
clubs	id	created_by -> users.id
memberships	(club_id, user_id)	club_id -> clubs.id, user_id -> users.id
club_books	id	club_id -> clubs.id, created_by -> users.id
club_book_categories	id	book_id -> club_books.id
note_sections	id	club_id -> clubs.id, book_id -> club_books.id, created_by -> users.id
notes	id	club_id -> clubs.id, author_id -> users.id, book_id -> club_books.id, category_id -> club_book_categories.id, section_id -> note_sections.id
comments	id	note_id -> notes.id, author_id -> users.id
reactions	(note_id, user_id, emoji)	note_id -> notes.id, user_id -> users.id
note_assets	id	note_id -> notes.id, created_by -> users.id
club_meetings	id	club_id -> clubs.id, book_id -> club_books.id, created_by -> users.id, canceled_by -> users.id
meeting_rsvps	(meeting_id, user_id)	meeting_id -> club_meetings.id, user_id -> users.id
meeting_comments	id	meeting_id -> club_meetings.id, author_id -> users.id
book_ratings	(book_id, user_id)	book_id -> club_books.id, user_id -> users.id

Important constraints

• memberships.role in (admin, member)
• meeting_rsvps.status in (yes, maybe, no)
• notes.category in (chapter, character, event)
• notes.spoiler_level and comments.spoiler_level in (none, mild, major)
• book_ratings.rating between 1 and 5
• club_books.bookmark_rating between 0 and 5
• club_books.chapter_count between 1 and 500
• note_sections enforces chapter-number presence for chapter sections only

Important indexes

• idx_memberships_user_id
• idx_club_books_club_id
• idx_club_book_categories_book_id
• idx_note_sections_club_book
• idx_notes_club_created, idx_notes_book_id, idx_notes_section_id
• idx_comments_note_id, idx_comments_parent_comment_id
• idx_reactions_note_id
• idx_club_meetings_club_scheduled, idx_club_meetings_club_book_scheduled
• idx_meeting_rsvps_meeting_id
• idx_meeting_comments_meeting_id
• idx_book_ratings_book_id, idx_book_ratings_user_id

Notes

• comments.parent_comment_id exists but is not currently declared as an FK.
• Legacy clubs.book_title is retained for compatibility while club_books is the active model.