Database model

Entity relationship diagram

        erDiagram
    USER {
        uuid user_id PK
        string display_name
        string_opt email UK
        string_opt password_hash
        string_opt external_login_id UK
        boolean is_anonymous
        boolean is_active
        boolean email_verified
        object_opt preferences
        datetime_opt last_login_at
    }

    BOOK {
        uuid book_id PK
        uuid user_id FK
        string title
        string_opt subtitle
        json authors
        string_opt publisher
        date_opt publication_date
        string_opt language
        string_opt isbn10
        string_opt isbn13
        text_opt description
        string_opt cover_image_url
        enum book_type
        string_opt digital_file_path
        string_opt file_format
        bigint_opt file_size
        string_opt file_hash
        string_opt physical_location
        string_opt lent_to
        datetime_opt lent_at
        enum reading_status
        integer_opt current_page
        decimal_opt current_position
        string_opt current_cfi
        boolean is_favorite
        integer_opt rating
        datetime_opt started_at
        datetime_opt completed_at
        datetime added_at
        datetime_opt last_read_at
        uuid_opt series_id FK
        decimal_opt series_number
        uuid_opt storage_backend_id FK
        object_opt custom_metadata
        boolean is_ocr_processed
        decimal_opt ocr_confidence
    }

    SHELF {
        uuid shelf_id PK
        uuid user_id FK
        uuid_opt parent_shelf_id FK
        string name
        text_opt description
        string_opt color
        string_opt icon
        boolean is_default
        boolean is_smart
        string_opt smart_query
        integer_opt sort_order
    }

    TAG {
        uuid tag_id PK
        uuid user_id FK
        string name
        string color
        text_opt description
        integer_opt usage_count
    }

    SERIES {
        uuid series_id PK
        uuid user_id FK
        string name
        text_opt description
        string_opt author
        integer_opt total_books
        boolean is_complete
    }

    ANNOTATION {
        uuid annotation_id PK
        uuid book_id FK
        uuid user_id FK
        text selected_text
        text_opt note
        string highlight_color
        string start_position
        string end_position
        string_opt chapter_title
        integer_opt chapter_index
        integer_opt page_number
    }

    NOTE {
        uuid note_id PK
        uuid book_id FK
        uuid user_id FK
        string title
        text content
        boolean is_pinned
    }

    BOOKMARK {
        uuid bookmark_id PK
        uuid book_id FK
        uuid user_id FK
        string position
        integer_opt page_number
        string_opt chapter_title
        string_opt note
        string_opt color
    }

    READING_SESSION {
        uuid session_id PK
        uuid user_id FK
        uuid book_id FK
        datetime start_time
        datetime_opt end_time
        decimal_opt start_position
        decimal_opt end_position
        integer_opt pages_read
        integer_opt duration_minutes
        string_opt device_type
        string_opt device_name
    }

    READING_GOAL {
        uuid goal_id PK
        uuid user_id FK
        string title
        text_opt description
        enum goal_type
        integer target_value
        integer current_value
        enum time_period
        date start_date
        date end_date
        boolean is_active
        boolean is_completed
        datetime_opt completed_at
    }

    READING_PROFILE {
        uuid profile_id PK
        uuid user_id FK
        string name
        boolean is_default
        string_opt font_family
        integer_opt font_size
        integer_opt font_weight
        decimal_opt line_height
        decimal_opt letter_spacing
        decimal_opt paragraph_spacing
        enum_opt text_alignment
        integer_opt horizontal_margin
        integer_opt vertical_margin
        string_opt background_color
        string_opt text_color
        string_opt link_color
        string_opt selection_color
        enum_opt theme_mode
        enum_opt reading_mode
        boolean_opt page_turn_animation
        integer_opt column_count
        boolean_opt hyphenation
    }

    METADATA_SERVER_CONFIG {
        uuid config_id PK
        uuid user_id FK, UK
        string server_url
        enum server_type
        string_opt auth_token
        string_opt refresh_token
        boolean is_connected
        boolean sync_enabled
        integer_opt sync_interval_seconds
        datetime_opt last_sync_at
        enum_opt sync_status
        string_opt sync_error_message
    }

    FILE_STORAGE_BACKEND {
        uuid backend_id PK
        uuid user_id FK
        enum backend_type
        string name
        boolean is_primary
        boolean is_active
        object connection_config
        object_opt credentials
        string_opt base_path
        bigint_opt storage_used_bytes
        bigint_opt storage_quota_bytes
        datetime_opt last_accessed_at
        enum_opt connection_status
        string_opt error_message
    }

    SAVED_FILTER {
        uuid filter_id PK
        uuid user_id FK
        string name
        text_opt description
        string query
        enum_opt filter_type
        string_opt icon
        string_opt color
        boolean is_pinned
        integer_opt usage_count
        datetime_opt last_used_at
    }

    BOOK_SHELF {
        uuid book_id PK, FK
        uuid shelf_id PK, FK
        integer_opt sort_order
    }

    BOOK_TAG {
        uuid book_id PK, FK
        uuid tag_id PK, FK
    }

    USER ||--o{ BOOK : owns
    USER ||--o{ SHELF : owns
    USER ||--o{ TAG : owns
    USER ||--o{ SERIES : owns
    USER ||--o{ ANNOTATION : creates
    USER ||--o{ NOTE : writes
    USER ||--o{ BOOKMARK : creates
    USER ||--o{ READING_SESSION : tracks
    USER ||--o{ READING_GOAL : sets
    USER ||--o{ READING_PROFILE : saves
    USER ||--o| METADATA_SERVER_CONFIG : configures
    USER ||--o{ FILE_STORAGE_BACKEND : configures
    USER ||--o{ SAVED_FILTER : saves

    BOOK }o--o| SERIES : belongs_to
    BOOK }o--o| FILE_STORAGE_BACKEND : stored_in
    BOOK ||--o{ ANNOTATION : has
    BOOK ||--o{ NOTE : has
    BOOK ||--o{ BOOKMARK : has
    BOOK ||--o{ READING_SESSION : has

    SHELF ||--o{ SHELF : parent_child

    BOOK ||--o{ BOOK_SHELF : in_shelves
    SHELF ||--o{ BOOK_SHELF : contains_books

    BOOK ||--o{ BOOK_TAG : has_tags
    TAG ||--o{ BOOK_TAG : assigned_to_books