AI로 앱 만들기 #3 - DB 설계하기

이후의 흐름은 작업하는 사람의 취향/습관 등에 따라 다를 수 있지만 저는 기본적으로 DB 설계 → 백엔드 API 설계 및 개발 → 프론트엔드 화면 기획 및 개발 → 프론트엔드-백엔드 API 연결의 방식을 따라 진행합니다. 뒤에서 어떻게 작동하는지, 원하는대로 백엔드가 기능하고 데이터를 주고받는지를 미리 확인하지 않으면 프론트엔드에서의 유저플로우 등이 깨지는 경우를 자주 봤기 때문입니다. 물론 서로 분업화가 잘 되어있는 개발 조직에서는 다른 방식을 활용하고 있겠지만, 1인 개발의 환경에서는 이러한 방향의 개발이 효과적이었습니다.

 

RAG 참고용 영문 문서 만들기

이제부터의 프롬프트는 모두 영어로 입력합니다. 그 이유는 대부분의 개발 자료 및 문서가 영어로 작성되어 있기 때문에 영어로 입력/출력하는 것이 효과적/효율적인 결과를 가져오기 때문입니다. 이를 위해서 우선 기초 문서들을 모두 영어로 바꿔줍니다. 문서의 포맷은 그대로 txt로 하고, 아래의 프롬프트로 번역을 요청합니다.

 

[문서 번역 프롬프트(o1)]

(문서명)을 영어로 만들어서 RAG에서 사용할 수 있도록 txt로 활용할 수 있는 양식으로 바꿔줘

여기에서 모델은 가급적 추론(reasoning)을 사용하는 모델인 o1, o3-mini-high 등을 사용하도록 합니다. 그 이유는 단순한 확률적 생성이 아닌 지금까지의 맥락과 흐름을 파악하고 출력을 생성하기 때문입니다.(그런데 아마 프로젝트에 RAG를 사용하면 o3 모델은 사용할 수 없는 것으로 알고 있습니다)

 

이렇게 생성한 것들을 기존에 프로젝트에 입력했던 것과 동일한 파일명으로 교체해 줍니다. 이렇게 되면 프로젝트의 지침은 변경하지 않은 상태로 RAG 파일들만 교체할 수 있게 됩니다.

 

 

DB 설계하기

이제부터 본격적으로 DB 설계를 요청합니다. 여기에서 요청할 내용을 우선 한글로 쓰고, 그 내용을 번역기를 통해 영어로 번역하여 전달합니다. 그리고 제대로 결과가 나오는지도 번역기를 통해 확인합니다. 다소 번거롭기는 하지만, 이렇게 작업하는 쪽의 출력물이 더 나은 경우가 많았고 장기적으로 보면 시간을 아낄 수 있었습니다. 저는 DeepL 번역기를 사용하였습니다.

 

[DB 설계 프롬프트(한국어)]

먼저, 전체 서비스에 대한 데이터베이스 스키마를 설계해 주세요. 
그런 다음, Supabase를 사용하여 이를 구현하는 방법을 알려주세요. 
또한, 머메이드 언어를 사용하여 각 데이터베이스 간의 관계를 ERD 형식으로 표시합니다.

 

[DB 설계 프롬프트(영어) (o1)]

First, design the database schema for the entire service. 
Then, tell me how to implement this with Supabase.
Also, use mermaid to diagram the relationships between each database.

 

그러면 아래와 같이 답변을 줍니다. 이것을 번역기를 통해 다시 확인해봅니다.(답변이 길어 접었습니다)

Below is a suggested relational schema for your Trip Track service, along with a Mermaid ER diagram showing how the tables connect in Supabase (Postgres). Following that, you’ll see details on how to implement this schema in Supabase—either via SQL statements in the Supabase SQL editor or via the Supabase dashboard UI.

---

1. Proposed Database Schema

---

This schema covers users (if you want multi-user support), trips, grouping of photos, AI-generated travel logs, and the shareable links for each trip. It’s structured so that:

• Users have many Trips.
• Each Trip can have multiple Groups (one group for each time/location cluster).
• Each Group can have multiple Photos.
• Each Trip can optionally have a single record in Travel_Logs (or multiple if you want versioning).

Schema Overview:

1. users
   • id (UUID, primary key)
   • email (text, unique)
   • display_name (text, optional)
   • (Any other user-related fields if you need authentication)
2. trips
   • id (UUID, primary key)
   • user_id (UUID, references users.id, on delete cascade)
   • title (text, optional, e.g. “My Paris Trip”)
   • created_at (timestamp, default now())
   • is_public (boolean, default false)
   • share_id (text, unique, nullable)
     Used for generating a shareable URL like /share/abcdef.
   • (You could also store a “cover_photo_url” or “start_date” etc. if desired)
3. groups
   • id (UUID, primary key)
   • trip_id (UUID, references trips.id, on delete cascade)
   • group_name (text, e.g. “Eiffel Tower Morning”)
   • rating (integer or numeric, optional)
   • review (text, optional)
   • (Timestamps if needed)
4. photos
   • id (UUID, primary key)
   • group_id (UUID, references groups.id, on delete cascade)
   • image_url (text)
     Location of the file in Supabase Storage or external URL
   • time_stamp (timestamp, optional)
     Extracted from EXIF or fallback to file’s lastModified
   • latitude (numeric, optional)
   • longitude (numeric, optional)
   • (Any other metadata you need)
5. travel_logs
   • id (UUID, primary key)
   • trip_id (UUID, references trips.id, on delete cascade, unique if 1:1)
   • ai_generated_text (text)
     The main text returned by AI
   • edited_text (text, optional)
     The user’s final edited version (or you can store final text only)
   • created_at (timestamp, default now())

You can expand or reduce fields as needed. For instance, if you want to store multiple versions of the travel log, you can remove the unique constraint on trip_id in travel_logs.

---

1. Mermaid ER Diagram

---

erDiagram

    USERS ||--|{ TRIPS : "has many"
    TRIPS ||--|{ GROUPS : "has many"
    GROUPS ||--|{ PHOTOS : "has many"
    TRIPS ||--|| TRAVEL_LOGS : "has one (1:1)"

    USERS {
        uuid id PK
        text email
        text display_name
        // ...other fields
    }

    TRIPS {
        uuid id PK
        uuid user_id FK
        text title
        timestamp created_at
        boolean is_public
        text share_id
        // ...other fields
    }

    GROUPS {
        uuid id PK
        uuid trip_id FK
        text group_name
        int rating
        text review
        // ...other fields
    }

    PHOTOS {
        uuid id PK
        uuid group_id FK
        text image_url
        timestamp time_stamp
        numeric latitude
        numeric longitude
        // ...other fields
    }

    TRAVEL_LOGS {
        uuid id PK
        uuid trip_id FK
        text ai_generated_text
        text edited_text
        timestamp created_at
        // ...other fields
    }

• USERS → TRIPS: One user can have many trips.
• TRIPS → GROUPS: Each trip can have multiple groups.
• GROUPS → PHOTOS: Each group can have multiple photos.
• TRIPS → TRAVEL_LOGS: One-to-one or one-to-many, depending on your design.

---

1. Implementing the Schema in Supabase

---

Option A: Using the Supabase SQL Editor

1. Go to your Supabase project dashboard.
2. Select the SQL section.
3. Create each table with DDL statements (see sample code below).
4. Adjust or omit columns as needed.

-- Enable UUID extension (if not already enabled)
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

-- 1. USERS TABLE
CREATE TABLE IF NOT EXISTS users (
  id uuid PRIMARY KEY DEFAULT uuid_generate_v4(),
  email text UNIQUE NOT NULL,
  display_name text,
  created_at timestamp DEFAULT now()
);

-- 2. TRIPS TABLE
CREATE TABLE IF NOT EXISTS trips (
  id uuid PRIMARY KEY DEFAULT uuid_generate_v4(),
  user_id uuid REFERENCES users (id) ON DELETE CASCADE,
  title text,
  created_at timestamp DEFAULT now(),
  is_public boolean DEFAULT false,
  share_id text UNIQUE,
  -- You can store additional fields here
  CONSTRAINT fk_user
    FOREIGN KEY (user_id)
    REFERENCES users (id)
    ON DELETE CASCADE
);

-- 3. GROUPS TABLE
CREATE TABLE IF NOT EXISTS groups (
  id uuid PRIMARY KEY DEFAULT uuid_generate_v4(),
  trip_id uuid NOT NULL REFERENCES trips (id) ON DELETE CASCADE,
  group_name text,
  rating integer,
  review text,
  created_at timestamp DEFAULT now(),
  CONSTRAINT fk_trip
    FOREIGN KEY (trip_id)
    REFERENCES trips (id)
    ON DELETE CASCADE
);

-- 4. PHOTOS TABLE
CREATE TABLE IF NOT EXISTS photos (
  id uuid PRIMARY KEY DEFAULT uuid_generate_v4(),
  group_id uuid NOT NULL REFERENCES groups (id) ON DELETE CASCADE,
  image_url text NOT NULL,
  time_stamp timestamp,
  latitude numeric,
  longitude numeric,
  created_at timestamp DEFAULT now(),
  CONSTRAINT fk_group
    FOREIGN KEY (group_id)
    REFERENCES groups (id)
    ON DELETE CASCADE
);

-- 5. TRAVEL_LOGS TABLE
CREATE TABLE IF NOT EXISTS travel_logs (
  id uuid PRIMARY KEY DEFAULT uuid_generate_v4(),
  trip_id uuid NOT NULL REFERENCES trips (id) ON DELETE CASCADE,
  ai_generated_text text,
  edited_text text,
  created_at timestamp DEFAULT now(),
  CONSTRAINT fk_trip
    FOREIGN KEY (trip_id)
    REFERENCES trips (id)
    ON DELETE CASCADE
    -- If you want exactly 1:1, add a UNIQUE constraint on trip_id:
    --, CONSTRAINT unique_trip_id UNIQUE (trip_id)
);

• Run each statement in the Supabase SQL editor.
• Verify that each table is created successfully under the “Database” tab.
• If you need other fields, simply alter the table or add them in the initial creation.

Option B: Using the Supabase Table Editor

1. Navigate to the Table Editor in the Supabase dashboard.
2. Create each table (users, trips, groups, photos, travel_logs) with the columns listed above.
3. For each foreign key, set the reference to the appropriate parent table.
4. If you want to enable cascade deletes, set the proper constraint.

Storing Images in Supabase Storage

• Inside your Supabase dashboard, you can create a Storage Bucket (e.g., “travel-photos”).
• When a user uploads an image, upload it to the bucket using the Supabase client on the front end:
• const { data, error } = await supabase.storage .from('travel-photos') .upload('folder/photo1.jpg', file)
• Store the resulting public URL or file path in the photos.image_url field.

Accessing the Data from Next.js

• Install the Supabase client:
• npm install @supabase/supabase-js
• Initialize and connect to your Supabase instance (e.g., in a lib/supabaseClient.ts file):
• import { createClient } from '@supabase/supabase-js' const supabaseUrl = process.env.NEXT_PUBLIC_SUPABASE_URL const supabaseAnonKey = process.env.NEXT_PUBLIC_SUPABASE_ANON_KEY export const supabase = createClient(supabaseUrl, supabaseAnonKey)
• In your API routes or serverless functions, you can insert/select data:
• // Example: saving a new trip const { data, error } = await supabase .from('trips') .insert([{ user_id: userId, title: 'My Paris Trip' }]) .single()
• Use RLS (Row Level Security) and policies if you need to secure data per user.

---

1. Summary

---

• The above schema implements all the necessary relationships to store user information, trips, photo groups, photos, and the AI-generated travel log.
• Supabase (Postgres) makes it straightforward to set up relational tables, foreign keys, and references.
• You can use the SQL approach directly in the editor or the UI Table Editor to create and manage these tables.
• Make sure to configure your security rules and set up your environment variables for local and deployed environments as needed.

 

이 DB 구조를 파악하기 위해 ERD를 확인해보면 아래와 같습니다.(일부 의도하던 내용과 결과물이 다소 달라 중간에 구조를 일부 수정한 부분이 있습니다. 이러한 수정 역시 한-영 번역을 통해서 진행했습니다.)

 

DB의 ERD 다이어그램

 

또한, 아래 프롬프트로 이렇게 설계한 DB 구조 역시 문서화하여 RAG에 추가합니다.

 

[DB 구조 문서화 요청 프롬프트(4o or 4.5)]

I want to store this database structure in RAG and continue using it.
I'm going to create a file in txt format, and I'd like to organize the current database structure so that it's easy to use.

 

DB 설정하기

이렇게 생성한 DB 구조를 기반으로, 세팅해 둔 supabase에 DB 테이블을 생성합니다. supabase 역시 익숙하지 않은 환경이라 아래 프롬프트를 통해 상세 가이드를 요청합니다.

 

[supabase 세팅 요청 프롬프트(o1)]

Please tell me step by step how to create this database in Supabase.

 

그러면, 아래와 같이 supabase에 DB를 세팅하는 가이드를 제공해줍니다. 이를 하나하나 따라가면서 DB를 세팅합니다.(길이가 길어 접음 처리합니다)

 

Below is a step-by-step guide on how to create this database in Supabase using two approaches: (A) SQL Editor and (B) Table Editor in the Supabase dashboard.

---

1. Create a Supabase Project

---

1. Sign in to Supabase.
2. Create a new project or open an existing one.
3. Make sure you have the right Database Password and Project API Keys stored securely.

---

1. Approach A: Using the Supabase SQL Editor

---

This method uses a single SQL script to create tables and relationships.
You can copy-paste the script below into the SQL section of your Supabase dashboard.

2.1. Open the SQL Editor

1. In your Supabase dashboard, open the left menu and click SQL.
2. Click New query.

2.2. Run the SQL Script

Important: Some of these statements may need the uuid-ossp extension if you haven’t already enabled it. You can enable that with:

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

Then proceed with the rest:

-- Create the "users" table
CREATE TABLE IF NOT EXISTS users (
  id uuid PRIMARY KEY DEFAULT uuid_generate_v4(),
  email text UNIQUE NOT NULL,
  display_name text,
  created_at timestamp DEFAULT now()
);

-- Create the "trips" table
CREATE TABLE IF NOT EXISTS trips (
  id uuid PRIMARY KEY DEFAULT uuid_generate_v4(),
  user_id uuid REFERENCES users(id) ON DELETE CASCADE,
  title text,
  created_at timestamp DEFAULT now(),
  is_public boolean DEFAULT false,
  share_id text UNIQUE
);

-- Create the "groups" table
CREATE TABLE IF NOT EXISTS groups (
  id uuid PRIMARY KEY DEFAULT uuid_generate_v4(),
  trip_id uuid NOT NULL REFERENCES trips(id) ON DELETE CASCADE,
  group_name text,
  rating integer,
  review text,
  created_at timestamp DEFAULT now(),
  sort_order integer,
  earliest_time_stamp timestamp,
  representative_location text
);

-- Create the "photos" table
CREATE TABLE IF NOT EXISTS photos (
  id uuid PRIMARY KEY DEFAULT uuid_generate_v4(),
  group_id uuid NOT NULL REFERENCES groups(id) ON DELETE CASCADE,
  image_url text NOT NULL,
  time_stamp timestamp,
  latitude numeric,
  longitude numeric,
  created_at timestamp DEFAULT now()
);

-- Create the "logs" table
CREATE TABLE IF NOT EXISTS logs (
  id uuid PRIMARY KEY DEFAULT uuid_generate_v4(),
  group_id uuid NOT NULL REFERENCES groups(id) ON DELETE CASCADE,
  ai_generated_text text,
  edited_text text,
  created_at timestamp DEFAULT now(),
  CONSTRAINT unique_group_id UNIQUE (group_id)
  -- The above constraint makes logs.group_id 1:1 with groups.id
);

2.3. Verify the Tables

• In the Supabase dashboard, navigate to the Table Editor or Database section to see if the tables (users, trips, groups, photos, logs) were created.
• Check each table’s columns to confirm everything matches your schema.

---

1. Approach B: Using the Supabase Table Editor

---

If you prefer a point-and-click interface instead of writing SQL:

1. In the Supabase dashboard, select Table Editor → New Table.
2. Create the “users” table
   • Add columns as described:
     • id (UUID, primary key, default uuid_generate_v4())
     • email (text, unique, not null)
     • display_name (text, optional)
     • created_at (timestamp, default now())
   • Save the table.
3. Create the “trips” table
   • Columns:
     • id (UUID, primary key)
     • user_id (UUID, foreign key referencing users.id, ON DELETE CASCADE)
     • title (text, optional)
     • created_at (timestamp, default now())
     • is_public (boolean, default false)
     • share_id (text, unique)
   • Save the table.
4. Create the “groups” table
   • Columns:
     • id (UUID, primary key)
     • trip_id (UUID, foreign key referencing trips.id, ON DELETE CASCADE)
     • group_name (text)
     • rating (integer)
     • review (text)
     • created_at (timestamp, default now())
     • sort_order (integer)
     • earliest_time_stamp (timestamp)
     • representative_location (text)
   • Save the table.
5. Create the “photos” table
   • Columns:
     • id (UUID, primary key)
     • group_id (UUID, foreign key referencing groups.id, ON DELETE CASCADE)
     • image_url (text, not null)
     • time_stamp (timestamp)
     • latitude (numeric)
     • longitude (numeric)
     • created_at (timestamp, default now())
   • Save the table.
6. Create the “logs” table
   • Columns:
     • id (UUID, primary key)
     • group_id (UUID, foreign key referencing groups.id, ON DELETE CASCADE)
     • ai_generated_text (text)
     • edited_text (text)
     • created_at (timestamp, default now())
   • Under Constraints, add a Unique constraint for group_id if you want the 1:1 relationship.
   • Save the table.

After each table is created, you can confirm the relationships by reviewing the foreign key references in the Table Editor or by checking the “Database” → “Relations” page.

---

1. (Optional) Setting Up Row Level Security (RLS)

---

• By default, Supabase uses Row Level Security. If you want to control which users can access or modify which rows, you can create Policies on each table.
• For example, you might add a policy on trips so that only the user who owns a trip can read or write it.
• CREATE POLICY "Allow users to read their trips" ON trips FOR SELECT TO authenticated USING (user_id = auth.uid()); CREATE POLICY "Allow users to insert trips" ON trips FOR INSERT TO authenticated WITH CHECK (user_id = auth.uid());
• You would configure similar policies for groups, photos, etc.

---

1. Summary

---

1. Create or open a Supabase project and enable the uuid-ossp extension.
2. Use either the SQL Editor or the Table Editor to define tables (users, trips, groups, photos, logs).
3. Verify the tables and relationships under the Database or Table Editor view.
4. (Optional) Set up Row Level Security and Policies to control access.

By following these steps, you’ll have a fully defined schema in Supabase with the tables and relationships matching the design you finalized. From there, you can integrate the Supabase JS client in your Next.js or other application code to perform CRUD operations.

이렇게 모든 테이블의 생성까지 완료하면, DB 세팅이 완료됩니다.

다음에는 백엔드 API를 만들어서 DB와 데이터를 주고받는 기능을 만들겠습니다.