2026SYD_h

Tables (10)

ai_verifications

  • verification_id — BIGINT — Primary key — NOT NULL
  • session_id — BIGINT — Foreign key → reading_sessions.session_id
  • cover_image_url — VARCHAR(255)
  • review_text — TEXT
  • vision_confidence — DECIMAL(5,2)
  • llm_confidence — DECIMAL(5,2)
  • is_passed — BOOLEAN
  • fail_reason — VARCHAR(255)
  • verified_at — DATETIME

books

  • book_id — BIGINT — Primary key — NOT NULL
  • isbn — VARCHAR(20)
  • title — VARCHAR(200)
  • author — VARCHAR(100)
  • publisher — VARCHAR(100)
  • cover_image_url — VARCHAR(255)
  • total_pages — INT

factions

  • faction_id — BIGINT — Primary key — NOT NULL
  • faction_name — VARCHAR(30) — NOT NULL
  • faction_color — VARCHAR(20)
  • total_score — INT
  • created_at — DATETIME

libraries

  • library_id — BIGINT — Primary key — NOT NULL
  • library_name — VARCHAR(100) — NOT NULL
  • address — VARCHAR(255)
  • latitude — DECIMAL(10,7)
  • longitude — DECIMAL(10,7)
  • region — VARCHAR(50)
  • created_at — DATETIME

library_territories

  • territory_id — BIGINT — Primary key — NOT NULL
  • library_id — BIGINT — Foreign key → libraries.library_id
  • faction_id — BIGINT — Foreign key → factions.faction_id
  • hp — INT
  • status — VARCHAR(20)
  • occupied_at — DATETIME
  • updated_at — DATETIME

location_logs

  • location_log_id — BIGINT — Primary key — NOT NULL
  • session_id — BIGINT — Foreign key → reading_sessions.session_id
  • latitude — DECIMAL(10,7)
  • longitude — DECIMAL(10,7)
  • distance_from_library — INT
  • status — VARCHAR(20)
  • checked_at — DATETIME

rankings

  • ranking_id — BIGINT — Primary key — NOT NULL
  • user_id — BIGINT — Foreign key → users.user_id
  • faction_id — BIGINT — Foreign key → factions.faction_id
  • total_exp — INT
  • total_books — INT
  • total_sessions — INT
  • rank_position — INT
  • updated_at — DATETIME

reading_sessions

  • session_id — BIGINT — Primary key — NOT NULL
  • user_id — BIGINT — Foreign key → users.user_id
  • library_id — BIGINT — Foreign key → libraries.library_id
  • book_id — BIGINT — Foreign key → books.book_id
  • start_time — DATETIME
  • end_time — DATETIME
  • duration_minutes — INT
  • start_page — INT
  • end_page — INT
  • status — VARCHAR(20)

territory_logs

  • log_id — BIGINT — Primary key — NOT NULL
  • user_id — BIGINT — Foreign key → users.user_id
  • library_id — BIGINT — Foreign key → libraries.library_id
  • faction_id — BIGINT — Foreign key → factions.faction_id
  • session_id — BIGINT — Foreign key → reading_sessions.session_id
  • score_delta — INT
  • hp_before — INT
  • hp_after — INT
  • action_type — VARCHAR(30)
  • created_at — DATETIME

users

  • user_id — BIGINT — Primary key — NOT NULL
  • social_provider — VARCHAR(20)
  • social_id — VARCHAR(100)
  • nickname — VARCHAR(30) — NOT NULL
  • faction_id — BIGINT — Foreign key → factions.faction_id
  • exp — INT
  • role — VARCHAR(20)
  • created_at — DATETIME
  • updated_at — DATETIME