Database Architecture

[samuraikun]

ER Diagram

Table overviews

- **users**: This table stores user-specific information, including their email, name, profile picture, and timestamps for email confirmation and last login. - **trips**: This table records details about each trip, such as the trip's title, start and end dates, costs, descriptions, and a link to an associated image stored in Supabase. - **activity**: This table outlines individual itinerary items for each trip, including titles, timing, addresses, URLs, memos, costs, and links to related images. - **tags**: This table contains tags that can be used to categorize trips, making them easier to search and organize. - **trip_tags**: This table serves as a join between trips and tags, allowing a many-to-many relationship where trips can have multiple tags and tags can apply to multiple trips. - **invitations**: This table manages the invitations users send to collaborate on or view trips, detailing who sent the invitation, who is invited, their email, and the level of permissions granted. - **storage_objects**: This table, managed by Supabase, keeps metadata for files stored in Supabase Storage, including the file's name, path, creation, and update times, and the owner's ID.

erDiagram
    users ||--o{ trips : "1..*"
    users ||--o{ invitations : "0..*"
    users {
        bigint id PK
        uuid user_uuid
        varchar email
        varchar name
        varchar profile_picture_url
        timestamptz email_confirmed_at
        timestamptz last_sign_in_at
    }

    trips ||--o{activity : "1..*"
    trips ||--o{ trip_tags : "1..*"
    trips ||--|| storage_objects : "1"
    trips {
        bigint id PK
        uuid trip_uuid
        bigint user_id FK
        varchar title
        date date_from
        date date_to
        numeric cost
        text description
        uuid image_storage_object_id FK
    }

   activity ||--|| storage_objects : "1"
    activity {
        bigint id PK
        uuid schedule_uuid
        bigint trip_id FK
        varchar title
        timestamptz time_from
        timestamptz time_to
        varchar address
        varchar url
        text memo
        numeric cost
        uuid image_storage_object_id FK
    }

    tags ||--o{ trip_tags : "1..*"
    tags {
        bigint id PK
        uuid tag_uuid
        varchar name
    }

    trip_tags {
        bigint trip_id FK
        bigint tag_id FK
    }

    invitations {
        bigint id PK
        uuid invitation_uuid
        bigint trip_id FK
        bigint invited_by_user_id FK
        bigint invitee_user_id FK
        varchar email
        varchar invitation_url
        int permission_level
    }

    storage_objects {
        uuid id PK
        uuid bucket_id
        varchar name
        varchar path
        timestamptz created_at
        timestamptz updated_at
        bigint owner_id FK
    }

[Old] Table overviews

- **Users** - Maintains essential user login credentials and identification data. - **Profiles** - Stores additional user details such as names and images for personalization. - **Trips** - Collects all relevant details for trips planned by users, including destinations and budgets. - **Activity** - Organizes specific events and locations that users plan to visit during their trips. - **Tags** - Holds categories or labels that users can apply to their trips for easier sorting and searching. - **TripTags** - Links trips to multiple tags, allowing for versatile categorization of trips. - **Invitations** - Manages the process of users inviting others to view or collaborate on trip planning, including access permissions. - **UploadedFiles** - Records details of files users upload related to their trips, such as images or supporting documents. - **TripFiles** - Associates uploaded files with their corresponding trips for organized access and reference. - **ScheduleFiles** - Connects uploaded files to specific schedule items, providing a detailed view of trip plans.

old ER diagrams

```mermaid erDiagram Users ||--o{ Profiles : "1" Users ||--o{ Invitations : "0..*" Users ||--o{ Trips : "1..*" Users { bigint id PK uuid uuid string email string email_confirmed_at string last_sign_in_at } Users ||--|| Profiles : "1" Profiles { bigint id PK uuid uuid bigint userId FK string name string profilePictureURL } Trips ||--o{Activity : "1..*" Trips ||--o{ TripTags : "1..*" Trips ||--o{ TripFiles : "1..*" Trips { bigint id PK uuid uuid bigint userId FK string title date DateFrom date DateTo float cost string description } Activity ||--o{ ScheduleFiles : "1..*" Activity { bigint id PK uuid uuid bigint tripId FK string title datetime TimeFrom datetime TimeTo string address string url string memo float cost } Tags ||--o{ TripTags : "1..*" Tags { bigint id PK uuid uuid string name } TripTags { bigint tripId FK bigint tagId FK } Invitations { bigint id PK uuid uuid bigint tripId FK bigint invitedByUserId FK bigint inviteeUserId FK string email string invitationUrl string permissionLevel } UploadedFiles { bigint id PK uuid uuid string url datetime uploadDate string description string fileType } TripFiles ||--|| UploadedFiles : "n..1" TripFiles { bigint tripId FK bigint fileId FK } ScheduleFiles ||--|| UploadedFiles : "n..1" ScheduleFiles { bigint scheduleId FK bigint fileId FK } ```

[Yo-mah-Ya]

• naming rule From where I sit, in general, snake case seems to be preferable in RDB context instead of camel case.

• overall What is uuid for in each table? Is id as PK used internal system, and uuid for external, as in API (GraphQL id ?), something like that ?

• Users => After I figure out where and how exactly we handle with password, just wanna clarify

• Profiles Do we actually need this table ? Can you give me some use case ?

• Invitations How we handle with permissionLevel.. yes nice, and how we regulate.

• TripFiles I'm wondering if we could allow users to add more than 1 images to every trip. As long as I see UI wireframe, just one would be enough.

[samuraikun]

@Yo-mah-Ya

• naming rule From where I sit, in general, snake case seems to be preferable in RDB context instead of camel case.

Exactly! I'll fix ER Diagram in PR description.

• overall What is uuid for in each table? Is id as PK used internal system, and uuid for external, as in API (GraphQL id ?), something like that ?

From my experience, uuid is better to use as GraphQL ID field than incremental ID. The reasons are follow.

General Advantages of UUIDs:

• Security: UUIDs are random and unique, making it hard for someone to guess IDs and access data they shouldn’t.
• Scalability: As your system grows, UUIDs reduce the risk of ID conflicts, especially when combining data from different sources.
• Consistency: UUIDs maintain their uniqueness across different systems, ensuring data stays consistent even when moved or copied.

Specific Benefits for Supabase Database:

• Built-In Support: Supabase provides built-in functionality for UUIDs, making it easy to implement and use them in your database.
• Flexibility in Distributed Systems: With Supabase's scalability, UUIDs are ideal as they ensure unique identifiers across multiple databases or services.
• Enhanced Data Management: Using UUIDs in Supabase helps in managing data more efficiently, especially when dealing with large datasets or multiple databases.

• Users => After I figure out where and how exactly we handle with password, just wanna clarify

Supabase automatically creates Users table including passwords when using Supabase Authentication. So, we will handle user passwords via Supabase auth library without our own implementation.

• Profiles Do we actually need this table ? Can you give me some use case ?

I don't have a special reason 😅 Ok! I'll delete it.

• Invitations How we handle with permissionLevel.. yes nice, and how we regulate.

I think that permission_level is managed by enum status(ex. 0: view_only, 1: editable or just boolean?)

• TripFiles I'm wondering if we could allow users to add more than 1 images to every trip. As long as I see UI wireframe, just one would be enough.

It made sense! I researched "Storage Schema" of Supase. https://supabase.com/docs/guides/storage/schema/design

According to the document, Supabase automatically creates a table to store metadatas of uploaded files. So, TripFiles and ScheduleFiles are unnecessary, then storage_object_id adds to Trips and Schedules table. (storage_objects table is created automatically by Supabase.)