Database Architecture

Database Architecture

Key design decisions

month and day as integers on MediaAsset — enables "On This Day" queries as direct composite index lookups instead of EXTRACT() scans. See Engineering Decisions.

content_hash (XXHash64) — file identity is hash-based, not path-based. Detects renames and moves without breaking existing metadata or interactions. Stored as a globally unique B-Tree index.

Live Photo self-reference — live_photo_pair_id is a self-referencing FK within MediaAsset. The .HEIC photo is the parent; its .MOV clip is the child. Data integrity is enforced at the DB level.

Burst decoupling — burst group logic lives in a dedicated table rather than duplicating state across individual assets. primary_asset_id identifies the cover photo.

Soft-delete — is_soft_deleted on MediaAsset preserves all user interactions if a file goes missing from the NAS and later reappears. A filtered index on is_soft_deleted = false keeps active queries fast.

Global query filters — EF Core applies soft-delete and hidden-interaction filters automatically. No query needs to remember to exclude trashed or hidden assets.

---

Indexing strategy

Index	Columns	Purpose
Composite	month, day	Powers the core On This Day query — direct lookup, no function scan
Filtered B-Tree	is_soft_deleted = false	Excludes trashed assets from all active queries
Unique B-Tree	content_hash	Move/rename detection; enforces no duplicate files
Unique	storage_config_id, file_path	Prevents duplicate records for the same file per user
Unique	user_id, asset_id on AssetInteraction	One interaction record per user per asset

---

Entity relationship diagram

erDiagram
    USER ||--o{ USER_STORAGE_CONFIG : "has"
    USER ||--o{ ASSET_INTERACTION : "performs"
    USER ||--o{ REFRESH_TOKEN : "holds"

    USER_STORAGE_CONFIG ||--o{ MEDIA_ASSET : "contains"

    MEDIA_ASSET ||--|| METADATA : "describes"
    MEDIA_ASSET ||--o{ PROXY_FILE : "has"
    MEDIA_ASSET ||--o{ ASSET_INTERACTION : "receives"
    MEDIA_ASSET ||--o| MEDIA_ASSET : "paired with (Live Photo MOV)"

    BURST ||--o{ MEDIA_ASSET : "groups"

    USER {
        guid id PK
        string username "UQ"
        string email "UQ"
        string password_hash
        boolean is_admin
    }

    REFRESH_TOKEN {
        guid id PK
        guid user_id FK
        string token_hash
        datetime expires_at
        datetime created_at
        datetime revoked_at "nullable"
    }

    USER_STORAGE_CONFIG {
        guid id PK
        guid user_id FK
        string root_path
    }

    MEDIA_ASSET {
        guid id PK
        guid storage_config_id FK
        guid burst_id FK "nullable"
        guid live_photo_pair_id FK "nullable"
        string file_path "UQ per StorageConfig"
        string content_hash "UQ"
        int month "IX_Composite"
        int day "IX_Composite"
        int year
        string media_type
        boolean is_soft_deleted "IX_Filtered"
        datetime last_seen_on_nas
    }

    METADATA {
        guid asset_id PK "FK"
        int width
        int height
        int orientation_degrees
        float latitude "nullable"
        float longitude "nullable"
        string camera_make "nullable"
        string camera_model "nullable"
        float duration_seconds "nullable"
        string color_space "nullable"
    }

    PROXY_FILE {
        guid id PK
        guid asset_id FK
        string file_path
        string proxy_type
        int size_bytes
    }

    ASSET_INTERACTION {
        guid id PK
        guid user_id FK
        guid asset_id FK
        boolean is_starred
        boolean is_liked
        boolean is_hidden
        float display_weight
        datetime last_viewed "nullable"
        int view_count
    }

    BURST {
        guid id PK
        guid primary_asset_id FK
        datetime created_at
    }

Loading