db-schema-diagram.md

Database Schema Diagram

This diagram reflects the PostgreSQL schema defined in src/db/schema.ts and the generated migrations in drizzle/.

erDiagram
    users ||--o{ posts : authors
    users ||--o{ user_favorite_foods : has
    favorite_foods ||--o{ user_favorite_foods : selected_in
    users ||--o{ user_hobbies : has
    hobbies ||--o{ user_hobbies : selected_in

    users {
        uuid id PK "default uuidv7()"
        varchar name "100"
        varchar email UK "250"
        text password_hash
        timestamptz created_at "default now()"
        timestamptz updated_at "default now()"
    }

    posts {
        uuid id PK "default uuidv7()"
        varchar title UK "200"
        text content
        uuid author_id FK
        timestamptz created_at "default now()"
        timestamptz updated_at "default now()"
    }

    favorite_foods {
        uuid id PK "default uuidv7()"
        varchar name UK "50"
        timestamptz created_at "default now()"
    }

    hobbies {
        uuid id PK "default uuidv7()"
        varchar name UK "100"
        timestamptz created_at "default now()"
    }

    user_favorite_foods {
        uuid id PK "default uuidv7()"
        uuid user_id FK
        uuid favorite_food_id FK
        integer position
        timestamptz created_at "default now()"
    }

    user_hobbies {
        uuid id PK "default uuidv7()"
        uuid user_id FK
        uuid hobby_id FK
        integer frequency_per_week
        integer position
        timestamptz created_at "default now()"
    }

Loading

Constraints

• users.email is unique.
• posts.title is unique.
• favorite_foods.name is unique.
• hobbies.name is unique.
• posts.author_id references users.id with ON DELETE CASCADE.
• user_favorite_foods.user_id references users.id with ON DELETE CASCADE.
• user_favorite_foods.favorite_food_id references favorite_foods.id with ON DELETE CASCADE.
• user_hobbies.user_id references users.id with ON DELETE CASCADE.
• user_hobbies.hobby_id references hobbies.id with ON DELETE CASCADE.
• user_favorite_foods has unique constraints on (user_id, favorite_food_id) and (user_id, position).
• user_hobbies has unique constraints on (user_id, hobby_id) and (user_id, position).
• user_hobbies.frequency_per_week is constrained to values greater than or equal to 1.

Indexes

• posts_author_id_idx on posts.author_id.
• user_favorite_foods_user_id_idx on user_favorite_foods.user_id.
• user_hobbies_user_id_idx on user_hobbies.user_id.

Ordering Model

• position preserves the original order of favoriteFoods and hobbies when user preference arrays are stored through join rows.
• Reads sort by position ASC so API responses keep the same order users submitted.

Detailed Diagram

flowchart TD
    users["users\nPK id uuidv7\nname varchar(100)\nemail varchar(250) UNIQUE\npassword_hash text\ncreated_at timestamptz DEFAULT now()\nupdated_at timestamptz DEFAULT now()"]

    posts["posts\nPK id uuidv7\nUNIQUE title varchar(200)\ncontent text\nFK author_id -> users.id\ncreated_at timestamptz DEFAULT now()\nupdated_at timestamptz DEFAULT now()"]

    favoriteFoods["favorite_foods\nPK id uuidv7\nUNIQUE name varchar(50)\ncreated_at timestamptz DEFAULT now()"]

    hobbies["hobbies\nPK id uuidv7\nUNIQUE name varchar(100)\ncreated_at timestamptz DEFAULT now()"]

    userFavoriteFoods["user_favorite_foods\nPK id uuidv7\nFK user_id -> users.id\nFK favorite_food_id -> favorite_foods.id\nposition integer\ncreated_at timestamptz DEFAULT now()"]

    userHobbies["user_hobbies\nPK id uuidv7\nFK user_id -> users.id\nFK hobby_id -> hobbies.id\nfrequency_per_week integer\nposition integer\ncreated_at timestamptz DEFAULT now()"]

    users -->|"1 to many\nauthor_id\nON DELETE CASCADE"| posts
    users -->|"1 to many\nuser_id\nON DELETE CASCADE"| userFavoriteFoods
    favoriteFoods -->|"1 to many\nfavorite_food_id\nON DELETE CASCADE"| userFavoriteFoods
    users -->|"1 to many\nuser_id\nON DELETE CASCADE"| userHobbies
    hobbies -->|"1 to many\nhobby_id\nON DELETE CASCADE"| userHobbies

    postsIdx[("INDEX\nposts_author_id_idx")]
    userFavoriteFoodsIdx[("INDEX\nuser_favorite_foods_user_id_idx")]
    userHobbiesIdx[("INDEX\nuser_hobbies_user_id_idx")]

    userFavoriteFoodsUniqueItem{{"UNIQUE\n(user_id, favorite_food_id)"}}
    userFavoriteFoodsUniquePosition{{"UNIQUE\n(user_id, position)"}}

    userHobbiesUniqueItem{{"UNIQUE\n(user_id, hobby_id)"}}
    userHobbiesUniquePosition{{"UNIQUE\n(user_id, position)"}}
    userHobbiesCheck{{"CHECK\nfrequency_per_week >= 1"}}

    favoriteFoodsUnique{{"UNIQUE\nname"}}
    hobbiesUnique{{"UNIQUE\nname"}}

    posts -.-> postsIdx
    userFavoriteFoods -.-> userFavoriteFoodsIdx
    userFavoriteFoods -.-> userFavoriteFoodsUniqueItem
    userFavoriteFoods -.-> userFavoriteFoodsUniquePosition
    userHobbies -.-> userHobbiesIdx
    userHobbies -.-> userHobbiesUniqueItem
    userHobbies -.-> userHobbiesUniquePosition
    userHobbies -.-> userHobbiesCheck
    favoriteFoods -.-> favoriteFoodsUnique
    hobbies -.-> hobbiesUnique

Loading

Visual Notes

• The schema now uses normalized lookup tables for reusable food and hobby names.
• user_favorite_foods and user_hobbies act as join tables that preserve per-user ordering.
• Dashed links point from a table to supporting indexes or constraints.
• Arrows show foreign keys and their ON DELETE CASCADE behavior.