Data model overview

---
config:
    layout: elk
---
graph TD
    accTitle: Relab data model overview
    accDescr: Users own cameras and products and link OAuth accounts; products form a self-referential component hierarchy, carry physical properties and circularity notes directly, and are linked to product types and materials; product types and materials are categorized under taxonomies; products, materials, and product types can each have files and images attached, and products can also have videos.

        User["User"]
        OAuthAccount["OAuth account"]

        Camera["Camera"]
        Product["Product"]

        Taxonomy["Taxonomy"]
        Material["Material"]
        Category["Category"]
        ProductType["Product type"]

        subgraph LocalStorage["Local storage"]
            File["File"]
            Image["Image"]
        end
        Video["Video"]

    User -->|Has| OAuthAccount
    User -->|Owns| Camera

    User -->|Owns| Product
    Product -->|Has Components| Product
    Product -->|Contains| Material
    Product -->|Is Type Of| ProductType
    ProductType -->|Categorized As| Category
    Material -->|Categorized As| Category
    Category -->|Sub-Category Of| Category
    Category -->|Belongs To| Taxonomy
    Product -->|Has| LocalStorage
    Product -->|Has| Video
    Material -->|Has| LocalStorage
    ProductType -->|Has| LocalStorage

    %% Diagram palette from assets/DESIGN.md (categorical ramp)
    style LocalStorage fill:#f1f4f8,stroke:#5a6675,stroke-width:1px,color:#16202e;

    classDef UserManagementFill fill:#ede6f7,stroke:#6d4fa3,stroke-width:2px,color:#44337a;
    class User,OAuthAccount UserManagementFill;

    classDef DataCollectionFill fill:#e3ecfa,stroke:#1f4c96,stroke-width:2px,color:#143567;
    class Product,Camera DataCollectionFill;

    classDef ReferenceDataFill fill:#f1f4f8,stroke:#5a6675,stroke-width:2px,color:#16202e;
    class Taxonomy,Material,Category,ProductType ReferenceDataFill;

    classDef MediaFill fill:#e0f2ed,stroke:#0e6b5e,stroke-width:2px,color:#0a4f45;
    class File,Image,Video MediaFill;

The diagram above is hand-drawn and simplified. The per-context diagrams below (user management, reference data, data collection, file storage, camera plugin) are generated from the ORM metadata by backend/scripts/generate/export_datamodel.py and list every column and foreign key. just check fails when they fall behind the models.

User management

User accounts, linked OAuth accounts, and ownership and access control. Account details stay out of the product model.

erDiagram
    accTitle: User management entities
    accDescr: The user management tables are OAUTHACCOUNT, USER. Each OAUTHACCOUNT row names exactly one USER as its user.

    OAUTHACCOUNT {
        uuid id PK
        uuid user_id FK
        varchar access_token
        varchar account_email
        varchar account_id
        integer expires_at
        varchar oauth_name
        varchar refresh_token
        timestamptz created_at
        timestamptz updated_at
    }

    USER {
        uuid id PK
        varchar email UK
        varchar email_canonical UK
        boolean has_usable_password
        varchar hashed_password
        boolean is_active
        boolean is_superuser
        boolean is_verified
        timestamptz last_login_at
        timestamptz mfa_confirmed_at
        boolean mfa_enabled
        jsonb mfa_recovery_codes
        varchar mfa_totp_secret
        jsonb preferences
        jsonb profile_stats
        timestamptz profile_stats_computed_at
        varchar role
        timestamptz terms_accepted_at
        integer terms_accepted_version
        integer upload_file_count
        bigint upload_total_bytes
        varchar username UK
        timestamptz created_at
        timestamptz updated_at
    }

    USER ||--o{ OAUTHACCOUNT : "user"

Usernames are unique and indexed. They are nullable only while an OAuth-created account completes onboarding; password registration requires a username up front.

hashed_password and mfa_totp_secret appear in no read schema; the TOTP secret is also encrypted at rest. profile_stats is a cached aggregate stamped by profile_stats_computed_at. upload_file_count and upload_total_bytes hold the per-account upload quota ledger.

Reference data

Taxonomies, categories and subcategories, materials, product types, and units. These records are shared across users and products and make research records easier to search, compare, and reuse.

erDiagram
    accTitle: Reference data entities
    accDescr: The reference data tables are CATEGORY, CATEGORYMATERIALLINK, CATEGORYPRODUCTTYPELINK, MATERIAL, PRODUCTTYPE, TAXONOMY. Each CATEGORY row names zero or one CATEGORY as its supercategory. Each CATEGORY row names exactly one TAXONOMY as its taxonomy. Each CATEGORYMATERIALLINK row names exactly one CATEGORY as its category. Each CATEGORYMATERIALLINK row names exactly one MATERIAL as its material. Each CATEGORYPRODUCTTYPELINK row names exactly one CATEGORY as its category. Each CATEGORYPRODUCTTYPELINK row names exactly one PRODUCTTYPE as its product type.

    CATEGORY {
        integer id PK
        integer supercategory_id FK
        integer taxonomy_id FK
        varchar description
        varchar external_id
        varchar name
        tsvector search_vector
        timestamptz created_at
        timestamptz updated_at
    }

    CATEGORYMATERIALLINK {
        integer category_id PK, FK
        integer material_id PK, FK
    }

    CATEGORYPRODUCTTYPELINK {
        integer category_id PK, FK
        integer product_type_id PK, FK
    }

    MATERIAL {
        integer id PK
        float density_kg_m3
        varchar description
        boolean is_crm
        varchar name
        tsvector search_vector
        varchar source
        timestamptz created_at
        timestamptz updated_at
    }

    PRODUCTTYPE {
        integer id PK
        varchar description
        varchar name
        tsvector search_vector
        timestamptz created_at
        timestamptz updated_at
    }

    TAXONOMY {
        integer id PK
        varchar description
        taxonomydomain[] domains "MATERIALS, PRODUCTS, OTHER"
        varchar name
        varchar source
        varchar version
        timestamptz created_at
        timestamptz updated_at
    }

    CATEGORY |o--o{ CATEGORY : "supercategory"
    TAXONOMY ||--o{ CATEGORY : "taxonomy"
    CATEGORY ||--o{ CATEGORYMATERIALLINK : "category"
    MATERIAL ||--o{ CATEGORYMATERIALLINK : "material"
    CATEGORY ||--o{ CATEGORYPRODUCTTYPELINK : "category"
    PRODUCTTYPE ||--o{ CATEGORYPRODUCTTYPELINK : "product type"

materialproductlink records how much of a material sits in a product. It carries a measurement, not a classification, so it appears in the data collection diagram below.

Data collection

Product holds both top-level products and nested components, so a disassembly tree needs no separate parts table.

• a product can have a parent product, enabling component hierarchies
• physical measurements are first-class product fields
• circularity notes are stored on the product as nullable JSON with optional recyclability, disassemblability, and remanufacturability fields
• ownership is attached to the user level
• materials, product types, and media can be linked without making every field mandatory

erDiagram
    accTitle: Data collection entities
    accDescr: The data collection tables are MATERIALPRODUCTLINK, PRODUCT. Each MATERIALPRODUCTLINK row names exactly one MATERIAL as its material. Each MATERIALPRODUCTLINK row names exactly one PRODUCT as its product. Each PRODUCT row names exactly one USER as its owner. Each PRODUCT row names zero or one PRODUCT as its parent. Each PRODUCT row names zero or one PRODUCTTYPE as its product type.

    MATERIALPRODUCTLINK {
        integer material_id PK, FK
        integer product_id PK, FK
        float quantity "Quantity of the material in the product"
        unit unit "Unit of the quantity, e.g. kg, g, m"
        timestamptz created_at
        timestamptz updated_at
    }

    PRODUCT {
        integer id PK
        uuid owner_id FK
        integer parent_id FK
        integer product_type_id FK
        integer amount_in_parent
        varchar brand
        jsonb circularity_properties
        float depth_cm
        varchar description
        float height_cm
        varchar model
        varchar name
        tsvector search_vector
        float weight_g
        float width_cm
        timestamptz created_at
        timestamptz updated_at
    }

    MATERIAL ||--o{ MATERIALPRODUCTLINK : "material"
    PRODUCT ||--o{ MATERIALPRODUCTLINK : "product"
    USER ||--o{ PRODUCT : "owner"
    PRODUCT |o--o{ PRODUCT : "parent"
    PRODUCTTYPE |o--o{ PRODUCT : "product type"

File storage and media

Files and images use polymorphic associations to products, materials, or product types. Videos are linked by URL, not stored.

erDiagram
    accTitle: File storage entities
    accDescr: The file storage tables are FILE, IMAGE, VIDEO. Each VIDEO row names exactly one PRODUCT as its product. Each FILE row is attached to exactly one of PRODUCT, PRODUCTTYPE, MATERIAL. Each IMAGE row is attached to exactly one of PRODUCT, PRODUCTTYPE, MATERIAL.

    FILE {
        uuid id PK
        varchar description
        varchar file "Local file path to the file"
        varchar filename "Original file name of the file."
        integer parent_id
        fileparenttype parent_type "PRODUCT, PRODUCT_TYPE, MATERIAL"
        integer upload_size_bytes
        timestamptz created_at
        timestamptz updated_at
    }

    IMAGE {
        uuid id PK
        varchar description
        varchar file "Local file path to the image"
        varchar filename "Original file name of the image."
        integer height_px "Pixel height of the stored image."
        jsonb image_metadata
        integer parent_id
        imageparenttype parent_type "PRODUCT, PRODUCT_TYPE, MATERIAL"
        timestamptz thumbnails_generated_at "When the full thumbnail set was last confirmed present."
        integer upload_size_bytes
        integer width_px "Pixel width of the stored image."
        timestamptz created_at
        timestamptz updated_at
    }

    VIDEO {
        integer id PK
        integer product_id FK
        varchar description
        varchar title
        varchar url "URL linking to the video"
        jsonb video_metadata
        timestamptz created_at
        timestamptz updated_at
    }

    PRODUCT ||--o{ FILE : "media parent"
    PRODUCTTYPE ||--o{ FILE : "media parent"
    MATERIAL ||--o{ FILE : "media parent"
    PRODUCT ||--o{ IMAGE : "media parent"
    PRODUCTTYPE ||--o{ IMAGE : "media parent"
    MATERIAL ||--o{ IMAGE : "media parent"
    PRODUCT ||--o{ VIDEO : "product"

video sits in this context because the row is a media reference (a URL and its metadata), like the other two.

Camera plugin

The Raspberry Pi camera integration owns two tables. camera holds a paired device and the relay credential that authenticates it; recording_session links a live broadcast to the video row it produces. RPi camera plugin covers the pairing and streaming path these tables support.

erDiagram
    accTitle: Camera plugin entities
    accDescr: The camera plugin tables are CAMERA, RECORDING_SESSION. Each CAMERA row names exactly one USER as its owner. Each RECORDING_SESSION row names exactly one CAMERA as its camera. Each RECORDING_SESSION row names exactly one VIDEO as its video.

    CAMERA {
        uuid id PK
        uuid owner_id FK
        varchar description
        varchar name
        cameracredentialstatus relay_credential_status "ACTIVE, REVOKED"
        varchar relay_key_id
        jsonb relay_public_key_jwk
        timestamptz created_at
        timestamptz updated_at
    }

    RECORDING_SESSION {
        uuid camera_id PK, FK
        integer video_id FK
        varchar broadcast_key
        timestamptz created_at
        timestamptz updated_at
    }

    USER ||--o{ CAMERA : "owner"
    CAMERA ||--o{ RECORDING_SESSION : "camera"
    VIDEO ||--o{ RECORDING_SESSION : "video"