{"id":136,"date":"2026-10-14T08:02:59","date_gmt":"2026-10-14T08:02:59","guid":{"rendered":"https:\/\/dingdonghome.duckdns.org\/?p=136"},"modified":"2026-10-14T08:11:13","modified_gmt":"2026-10-14T08:11:13","slug":"%ea%b0%9c%eb%b0%9c%ec%9d%bc%ec%a7%80-05-%eb%a9%94%ec%8b%a0%ec%a0%80-db-%ec%8a%a4%ed%82%a4%eb%a7%88-%ec%84%a4%ea%b3%84%ec%99%80-go%ec%9d%98-%ec%b5%9c%ea%b0%95-%ec%a1%b0%ed%95%a9-sqlc-%ec%97%b0","status":"publish","type":"post","link":"https:\/\/dinggo.duckdns.org\/?p=136","title":{"rendered":"[\uac1c\ubc1c\uc77c\uc9c0 #05] \uba54\uc2e0\uc800 DB \uc2a4\ud0a4\ub9c8 \uc124\uacc4\uc640 Go\uc758 \ucd5c\uac15 \uc870\ud569 sqlc \uc5f0\ub3d9\ud558\uae30 (PostgreSQL + sqlc)"},"content":{"rendered":"\n

\uc548\ub155\ud558\uc138\uc694! \uce74\uce74\uc624\ud1a1 \uc2a4\ud0c0\uc77c\uc758 \uac00\uc871\/\uce5c\uad6c\uc6a9 \ud504\ub77c\uc774\ube57 \ud06c\ub85c\uc2a4\ud50c\ub7ab\ud3fc \uba54\uc2e0\uc800 ‘\ub529\ub3d9(DingDong)’<\/strong> \ub9cc\ub4e4\uae30 \ud504\ub85c\uc81d\ud2b8 5\ud3b8\uc785\ub2c8\ub2e4.<\/p>\n\n\n\n

\uc9c0\ub09c [4\ud3b8]\uc5d0\uc11c\ub294 \uc720\uadf8\ub9b0 NAS\uc758 \uc800\uc0ac\uc591 \ud558\ub4dc\uc6e8\uc5b4\uc5d0\uc11c\ub3c4 \uae43\ud138\ucc98\ub7fc \uac00\ubccd\uac8c \ub3cc\uc544\uac00\ub294 **Go \ubc31\uc5d4\ub4dc API \uc5d4\uc9c4(chi \ub77c\uc6b0\ud130, Graceful Shutdown, 25MB \ucd08\uacbd\ub7c9 Dockerfile, Redis \uc5f0\ub3d9)**\uc744 \uc138\ud305\ud588\uc2b5\ub2c8\ub2e4.<\/p>\n\n\n\n

\uc774\ubc88 5\ud3b8\uc5d0\uc11c\ub294 \uba54\uc2e0\uc800 \ubc31\uc5d4\ub4dc\uc758 \uc2ec\uc7a5\uc778 **\ub370\uc774\ud130\ubca0\uc774\uc2a4 \uc2a4\ud0a4\ub9c8(\ud14c\uc774\ube14 \uad6c\uc870)**\ub97c \uc644\ubcbd\ud558\uac8c \uc124\uacc4\ud558\uace0, Go \uc9c4\uc601\uc5d0\uc11c \uac00\uc7a5 \uc6b0\uc544\ud558\uace0 \uac15\ub825\ud55c \ub370\uc774\ud130\ubca0\uc774\uc2a4 \ud234\uc778 **sqlc<\/code>**\ub97c \uc5f0\ub3d9\ud558\ub294 \uacfc\uc815\uc744 \ub2e4\ub8f9\ub2c8\ub2e4.<\/p>\n\n\n\n

\n\n\n\n

1. \uba54\uc2e0\uc800 \ub370\uc774\ud130\ubca0\uc774\uc2a4 \uc124\uacc4 \uc2dc \ubc18\ub4dc\uc2dc \uace0\ub824\ud574\uc57c \ud560 4\uac00\uc9c0<\/h2>\n\n\n\n

\ub2e8\uc21c\ud55c \uac8c\uc2dc\ud310\uc774\ub098 \uc1fc\ud551\ubab0\uacfc \ub2ec\ub9ac, \uce74\uce74\uc624\ud1a1 \uc2a4\ud0c0\uc77c\uc758 \uc2e4\uc2dc\uac04 \uba54\uc2e0\uc800<\/strong>\ub294 \ub370\uc774\ud130\ubca0\uc774\uc2a4 \uc124\uacc4 \uc2dc \ub9e4\uc6b0 \uae4c\ub2e4\ub85c\uc6b4 \uc694\uad6c\uc0ac\ud56d\ub4e4\uc774 \uc788\uc2b5\ub2c8\ub2e4:<\/p>\n\n\n\n

flowchart TD\n    A[\"\uba54\uc2e0\uc800 DB \ud575\uc2ec \uc124\uacc4 \uc6d0\uce59\"] --> B[\"1. \ubc29\ubcc4 \ub2e8\uc870 \uc99d\uac00 \uc21c\ubc88(seq)\"]\n    A --> C[\"2. 1:1 \ucc44\ud305\ubc29 \uc911\ubcf5 \uc0dd\uc131 \ubc29\uc9c0\"]\n    A --> D[\"3. \uac1c\uc778\uc815\ubcf4 \ubcf4\ud638 \uc5f0\ub77d\ucc98 \ud574\uc2dc \ub9e4\uce6d\"]\n    A --> E[\"4. \ub2e4\uc911 \uae30\uae30 \uc138\uc158 \ubc0f \uc77d\uc74c \ud655\uc778\"]\n\n    linkStyle default stroke-width:3px,stroke:#333333<\/pre>
<\/div><\/div>\n\n\n\n
    \n
  1. \ubc29\ubcc4 \ub2e8\uc870 \uc99d\uac00 \uc21c\ubc88 (seq<\/code>)<\/strong>:\n
      \n
    • \uc2dc\uac04(Timestamp)\uc73c\ub85c\ub9cc \uba54\uc2dc\uc9c0\ub97c \uc815\ub82c\ud558\uba74 \uae30\uae30 \uac04 \uc2dc\uacc4 \uc624\ucc28\ub098 1\ubc00\ub9ac\ucd08 \ub0b4 \ub2e4\uc911 \uba54\uc2dc\uc9c0 \ubc1c\uc0dd \uc2dc \uc21c\uc11c\uac00 \ub4a4\ud2c0\ub9bd\ub2c8\ub2e4.<\/li>\n\n\n\n
    • \ubc29\ub9c8\ub2e4 1, 2, 3\u2026 \uc21c\ubc88(seq<\/code>)\uc744 \uc6d0\uc790\uc801(Atomic)\uc73c\ub85c \ubd80\uc5ec\ud574\uc57c \ud074\ub77c\uc774\uc5b8\ud2b8\uac00 \ub04a\uacbc\ub2e4 \ub2e4\uc2dc \uc5f0\uacb0\ub418\uc5c8\uc744 \ub54c **”5\ubc88 \uc774\ud6c4\uc758 \uba54\uc2dc\uc9c0\ub9cc \uc8fc\uc138\uc694!”**\ub77c\uba70 \ub204\ub77d \uc5c6\uc774 \uc644\ubcbd\ud55c \uc99d\ubd84 \ub3d9\uae30\ud654\ub97c \ud560 \uc218 \uc788\uc2b5\ub2c8\ub2e4.<\/li>\n<\/ul>\n<\/li>\n\n\n\n
    • 1:1 \ucc44\ud305\ubc29 \uc911\ubcf5 \uc0dd\uc131 \ubc29\uc9c0 (direct_key<\/code>)<\/strong>:\n
        \n
      • A\uac00 B\uc5d0\uac8c \ub300\ud654\ub97c \uac78\uace0, B\ub3c4 A\uc5d0\uac8c \ub300\ud654\ub97c \uac78 \ub54c \uc11c\ub85c \ub2e4\ub978 1:1 \ubc29\uc774 \uc911\ubcf5\uc73c\ub85c \uc0dd\uae30\uba74 \uc548 \ub429\ub2c8\ub2e4.<\/li>\n\n\n\n
      • \ub450 \uc720\uc800\uc758 UUID\ub97c \uc624\ub984\ucc28\uc21c\uc73c\ub85c \uc815\ub82c\ud574 \uc720\ub2c8\ud06c \ud0a4(\uc791\uc740UUID:\ud070UUID<\/code>)\ub85c \uac15\uc81c\ud558\uc5ec DB \ub808\ubca8\uc5d0\uc11c \uc911\ubcf5\uc744 \uc6d0\ucc9c \ucc28\ub2e8\ud569\ub2c8\ub2e4.<\/li>\n<\/ul>\n<\/li>\n\n\n\n
      • \uc804\ud654\ubc88\ud638 SHA-256 \ud574\uc2dc \ub9e4\uce6d (phone_hash<\/code>)<\/strong>:\n
          \n
        • \uac1c\uc778\uc815\ubcf4 \ubcf4\ud638\ub97c \uc704\ud574 \ud3c9\ubb38 \uc804\ud654\ubc88\ud638\ub294 DB\uc5d0 \ub0a8\uae30\uc9c0 \uc54a\uace0, E.164 \uad6d\uc81c \ud45c\uc900 \uc804\ud654\ubc88\ud638 \uaddc\uaca9\uc758 SHA-256 \ud574\uc2dc\uac12\ub9cc \uc800\uc7a5\ud558\uc5ec \uc8fc\uc18c\ub85d \ub3d9\uae30\ud654 \uc2dc \uce5c\uad6c\ub97c \uc548\uc804\ud558\uac8c \ub9e4\uce6d\ud569\ub2c8\ub2e4.<\/li>\n<\/ul>\n<\/li>\n\n\n\n
        • \ub2e4\uc911 \uae30\uae30(\ubaa8\ubc14\uc77c\/\ud0dc\ube14\ub9bf\/PC) \uc138\uc158 \uad00\ub9ac<\/strong>:\n
            \n
          • \ud55c \uc0ac\uc6a9\uc790\uac00 \uc2a4\ub9c8\ud2b8\ud3f0, \uc544\uc774\ud328\ub4dc, \ub9e5\ubd81\uc5d0\uc11c \ub3d9\uc2dc \uc811\uc18d\ud560 \uc218 \uc788\uc73c\ubbc0\ub85c \uae30\uae30\ubcc4(devices<\/code>) \uc138\uc158\uacfc \uac1c\ubcc4 \ud478\uc2dc \ud1a0\ud070(FCM\/APNs)\uc744 \ub3c5\ub9bd\uc801\uc73c\ub85c \uad00\ub9ac\ud569\ub2c8\ub2e4.<\/li>\n<\/ul>\n<\/li>\n<\/ol>\n\n\n\n
            \n\n\n\n
            2. \uc65c \ubb34\uac70\uc6b4 ORM \ub300\uc2e0 sqlc<\/code>\ub97c \uc120\ud0dd\ud588\uc744\uae4c?<\/h2>\n\n\n\n
            Node.js\ub098 Python\uc5d0\uc11c\ub294 TypeORM, Prisma, SQLAlchemy \uac19\uc740 \ubb34\uac70\uc6b4 ORM\uc744 \ud754\ud788 \uc0ac\uc6a9\ud569\ub2c8\ub2e4. \ud558\uc9c0\ub9cc Go \uc5b8\uc5b4\uc5d0\uc11c\ub294 ORM\ubcf4\ub2e4 **sqlc<\/code>**\uac00 \uc555\ub3c4\uc801\uc778 \ucc2c\uc0ac\ub97c \ubc1b\uace0 \uc788\uc2b5\ub2c8\ub2e4.<\/p>\n\n\n\n
            \ube44\uad50 \ud56d\ubaa9<\/th>\uc804\ud1b5\uc801\uc778 \ubb34\uac70\uc6b4 ORM (GORM \ub4f1)<\/th>Go \uc9c4\uc601\uc758 \ud45c\uc900 sqlc<\/code><\/th><\/tr><\/thead>
            \ub3d9\uc791 \ubc29\uc2dd<\/strong><\/td>\ub7f0\ud0c0\uc784\uc5d0 \ub9ac\ud50c\ub809\uc158(Reflection)\uc73c\ub85c SQL \ub3d9\uc801 \uc0dd\uc131<\/td>\ucef4\ud30c\uc77c \ud0c0\uc784\uc5d0 \uc21c\uc218 Go \ucf54\ub4dc\ub97c \ubbf8\ub9ac \uc0dd\uc131<\/strong><\/td><\/tr>
            \uba54\ubaa8\ub9ac \ubc0f CPU<\/strong><\/td>\uac1d\uccb4 \ub9e4\ud551 \uc624\ubc84\ud5e4\ub4dc\ub85c \uba54\ubaa8\ub9ac \uc18c\ubaa8 \ud07c<\/td>\uc21c\uc218 \ub4dc\ub77c\uc774\ubc84 \uc218\uc900\uc758 \uadf9\ud55c\uc758 \uc18d\ub3c4\uc640 \ucd08\uacbd\ub7c9 RAM<\/strong><\/td><\/tr>
            \ud0c0\uc785 \uc548\uc815\uc131<\/strong><\/td>\ub7f0\ud0c0\uc784\uc5d0 \uceec\ub7fc\uba85 \uc624\ud0c0 \uc5d0\ub7ec \ubc1c\uc0dd \uac00\ub2a5<\/td>SQL \ubb38\ubc95 \uac80\uc0ac + Go \uad6c\uc870\uccb4 \ud0c0\uc785 100% \ubcf4\uc7a5<\/strong><\/td><\/tr>
            SQL \ud559\uc2b5 \ube44\uc6a9<\/strong><\/td>\ud504\ub808\uc784\uc6cc\ud06c \uc804\uc6a9 DSL \ubb38\ubc95\uc744 \uc0c8\ub85c \ubc30\uc6cc\uc57c \ud568<\/td>\uac1c\ubc1c\uc790\uac00 \uc6d0\ub798 \uc54c\ub358 \ud45c\uc900 PostgreSQL SQL \uadf8\ub300\ub85c \uc791\uc131<\/strong><\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
            \n
            \ud83d\udca1 sqlc\uc758 \ub9c8\ubc95<\/strong>: \uac1c\ubc1c\uc790\uac00 .sql<\/code> \ud30c\uc77c\uc5d0 \uc21c\uc218\ud55c PostgreSQL \ucffc\ub9ac\ub97c \uc791\uc131\ud558\uace0 \ud130\ubbf8\ub110\uc5d0\uc11c sqlc generate<\/code>\ub97c \uce58\uba74, sqlc\uac00 PostgreSQL \ubb38\ubc95\uc744 \ud30c\uc2f1\ud558\uc5ec Go \uad6c\uc870\uccb4\uc640 \ud568\uc218 \ucf54\ub4dc\ub97c 1\ubc00\ub9ac\ucd08 \ub9cc\uc5d0 \uc790\ub3d9\uc73c\ub85c \ub9cc\ub4e4\uc5b4 \uc90d\ub2c8\ub2e4!<\/p>\n<\/blockquote>\n\n\n\n
            \n\n\n\n
            3. \uc804\uccb4 \ub370\uc774\ud130\ubca0\uc774\uc2a4 \uc2a4\ud0a4\ub9c8 (DDL) \ubc0f \ud30c\uc77c \uc0dd\uc131<\/h2>\n\n\n\n
            3.1 \ud30c\uc77c \uacbd\ub85c \ubc0f \ud3f4\ub354 \uad6c\uc870 \uc548\ub0b4<\/h3>\n\n\n\n
            \ub9c8\uc774\uadf8\ub808\uc774\uc158 \ud30c\uc77c\uc740 server\/<\/code> \ub514\ub809\ud1a0\ub9ac \uc544\ub798\uc5d0 migrations\/<\/code><\/strong> \ud3f4\ub354\ub97c \ub9cc\ub4e4\uc5b4 \uad00\ub9ac\ud569\ub2c8\ub2e4.
            DB \ub3c4\uad6c(golang-migrate<\/code>, sqlc<\/code>)\uac00 \uc778\uc2dd\ud560 \uc218 \uc788\ub3c4\ub85d \ud30c\uc77c \uc774\ub984 \ub4a4\uc5d0 .up.sql<\/code>(\uc801\uc6a9\uc6a9)\uacfc .down.sql<\/code>(\ub864\ubc31\uc6a9)\uc744 \ubd99\uc5ec\uc8fc\ub294 \uac83\uc774 \ud45c\uc900 \uaddc\uce59\uc785\ub2c8\ub2e4.<\/p>\n\n\n\n
            server\/\n \u251c\u2500 migrations\/\n \u2502   \u251c\u2500 000001_init_schema.up.sql    # \ud83d\udc48 [\uc9c0\uae08 \ub9cc\ub4e4 \ud30c\uc77c: \ud14c\uc774\ube14 \uc0dd\uc131 DDL]\n \u2502   \u2514\u2500 000001_init_schema.down.sql  # [\ub864\ubc31\uc6a9 \ud30c\uc77c: \ud14c\uc774\ube14 \uc0ad\uc81c DROP]\n \u251c\u2500 queries\/                         # 5\ubc88\uc5d0\uc11c \ub9cc\ub4e4 SQL \ucffc\ub9ac\ub4e4\n \u251c\u2500 sqlc.yaml                        # 4\ubc88\uc5d0\uc11c \ub9cc\ub4e4 sqlc \uc124\uc815\n \u251c\u2500 cmd\/api\/main.go\n \u251c\u2500 Dockerfile\n \u251c\u2500 go.mod\n \u2514\u2500 go.sum<\/pre>\n\n\n\n
            \n\n\n\n
            3.2 \ud14c\uc774\ube14 \uc0dd\uc131 \uc2a4\ud0a4\ub9c8 (server\/migrations\/000001_init_schema.up.sql<\/code>)<\/h3>\n\n\n\n
            VS Code\uc5d0\uc11c server\/migrations\/<\/code> \ud3f4\ub354\ub97c \uc0dd\uc131\ud558\uace0, 000001_init_schema.up.sql<\/code><\/strong> \ud30c\uc77c\uc744 \ub9cc\ub4e4\uc5b4 \uc544\ub798 DDL \ucf54\ub4dc\ub97c \uadf8\ub300\ub85c \ubd99\uc5ec\ub123\uc2b5\ub2c8\ub2e4:<\/p>\n\n\n\n
            -- UUID \uc0dd\uc131 \ud655\uc7a5 \uae30\ub2a5 \ud65c\uc131\ud654\nCREATE EXTENSION IF NOT EXISTS \"pgcrypto\";\n\n-- 1. \uc0ac\uc6a9\uc790 (Users)\nCREATE TABLE users (\n    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),\n    display_name    VARCHAR(50)  NOT NULL,\n    status_message  VARCHAR(100),\n    avatar_file_id  UUID,\n    phone_hash      VARCHAR(64)  UNIQUE,       -- E.164 \ubc88\ud638\uc758 SHA-256 \ud574\uc2dc\uac12\n    role            VARCHAR(10)  NOT NULL DEFAULT 'user',   -- 'admin' | 'user'\n    status          VARCHAR(10)  NOT NULL DEFAULT 'active', -- 'active' | 'suspended' | 'deleted'\n    invited_by      UUID REFERENCES users(id),\n    created_at      TIMESTAMPTZ  NOT NULL DEFAULT now(),\n    updated_at      TIMESTAMPTZ  NOT NULL DEFAULT now()\n);\nCREATE INDEX idx_users_phone_hash ON users(phone_hash);\n\n-- 2. \ub85c\uadf8\uc778 \uacc4\uc815 \uc218\ub2e8 (Auth Identities)\nCREATE TABLE auth_identities (\n    id             UUID PRIMARY KEY DEFAULT gen_random_uuid(),\n    user_id        UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,\n    provider       VARCHAR(10) NOT NULL,          -- 'password' | 'google' | 'apple'\n    provider_uid   VARCHAR(255) NOT NULL,         -- \ub85c\uadf8\uc778 \uc544\uc774\ub514 \ub610\ub294 \uc18c\uc15c sub\n    password_hash  TEXT,                          -- Argon2id \ud574\uc2dc (\ube44\ubc00\ubc88\ud638 \ub85c\uadf8\uc778 \uc2dc)\n    created_at     TIMESTAMPTZ NOT NULL DEFAULT now(),\n    UNIQUE (provider, provider_uid)\n);\n\n-- 3. \uae30\uae30 \uc138\uc158 \ubc0f \ud478\uc2dc \ud1a0\ud070 (Devices)\nCREATE TABLE devices (\n    id                  UUID PRIMARY KEY DEFAULT gen_random_uuid(),\n    user_id             UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,\n    platform            VARCHAR(10) NOT NULL,     -- 'android' | 'ios' | 'macos' | 'windows'\n    name                VARCHAR(100),             -- \uc608: \"\uc544\ube60 Galaxy S24 Ultra\"\n    refresh_token_hash  TEXT NOT NULL,\n    refresh_expires_at  TIMESTAMPTZ NOT NULL,\n    fcm_token           TEXT,\n    voip_token          TEXT,                     -- iOS PushKit \ud1b5\ud654\uc6a9\n    app_version         VARCHAR(20),\n    last_active_at      TIMESTAMPTZ,\n    created_at          TIMESTAMPTZ NOT NULL DEFAULT now()\n);\nCREATE INDEX idx_devices_user_id ON devices(user_id);\n\n-- 4. \ucd08\ub300 \ucf54\ub4dc (Invite Codes)\nCREATE TABLE invite_codes (\n    code        CHAR(6) PRIMARY KEY,\n    owner_id    UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,\n    max_uses    INT NOT NULL DEFAULT 10,\n    used_count  INT NOT NULL DEFAULT 0,\n    expires_at  TIMESTAMPTZ NOT NULL,\n    created_at  TIMESTAMPTZ NOT NULL DEFAULT now()\n);\n\n-- 5. \uce5c\uad6c \uad00\uacc4 (Friendships)\nCREATE TABLE friendships (\n    user_id     UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,\n    friend_id   UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,\n    tag         VARCHAR(10) NOT NULL DEFAULT 'friend',  -- 'family' | 'friend'\n    nickname    VARCHAR(50),\n    blocked     BOOLEAN NOT NULL DEFAULT false,\n    created_at  TIMESTAMPTZ NOT NULL DEFAULT now(),\n    PRIMARY KEY (user_id, friend_id)\n);\n\n-- 6. \ucc44\ud305\ubc29 (Rooms)\nCREATE TABLE rooms (\n    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),\n    type            VARCHAR(10) NOT NULL,             -- 'direct' | 'group'\n    direct_key      VARCHAR(80) UNIQUE,               -- 1:1 \ubc29 \uc911\ubcf5 \ubc29\uc9c0 \ud0a4 (\"\uc791\uc740UUID:\ud070UUID\")\n    name            VARCHAR(50),\n    avatar_file_id  UUID,\n    last_seq        BIGINT NOT NULL DEFAULT 0,        -- \ubc29 \uba54\uc2dc\uc9c0 \ucd5c\uc2e0 \uc21c\ubc88\n    created_by      UUID REFERENCES users(id),\n    created_at      TIMESTAMPTZ NOT NULL DEFAULT now(),\n    updated_at      TIMESTAMPTZ NOT NULL DEFAULT now()\n);\n\n-- 7. \ucc44\ud305\ubc29 \ucc38\uc5ec \uba64\ubc84 (Room Members)\nCREATE TABLE room_members (\n    room_id        UUID NOT NULL REFERENCES rooms(id) ON DELETE CASCADE,\n    user_id        UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,\n    role           VARCHAR(10) NOT NULL DEFAULT 'member',  -- 'owner' | 'member'\n    last_read_seq  BIGINT NOT NULL DEFAULT 0,              -- \ub9c8\uc9c0\ub9c9\uc73c\ub85c \uc77d\uc740 \uba54\uc2dc\uc9c0 \uc21c\ubc88\n    joined_seq     BIGINT NOT NULL DEFAULT 0,              -- \uc785\uc7a5 \uc2dc\uc810 \uc21c\ubc88 (\uc774\uc804 \ub300\ud654 \uc228\uae40\uc6a9)\n    muted          BOOLEAN NOT NULL DEFAULT false,\n    pinned         BOOLEAN NOT NULL DEFAULT false,\n    joined_at      TIMESTAMPTZ NOT NULL DEFAULT now(),\n    PRIMARY KEY (room_id, user_id)\n);\nCREATE INDEX idx_room_members_user ON room_members(user_id);\n\n-- 8. \uba54\uc2dc\uc9c0 (Messages)\nCREATE TABLE messages (\n    id           UUID PRIMARY KEY,                  -- \ud074\ub77c\uc774\uc5b8\ud2b8\uac00 \uc0dd\uc131\ud55c \uc911\ubcf5 \ubc29\uc9c0\uc6a9 UUID v7\n    room_id      UUID NOT NULL REFERENCES rooms(id) ON DELETE CASCADE,\n    seq          BIGINT NOT NULL,                   -- \ubc29 \ub0b4 1\uc529 \uc99d\uac00\ud558\ub294 \ub2e8\uc870 \uc21c\ubc88\n    sender_id    UUID REFERENCES users(id),         -- \ud0c8\ud1f4 \uc2dc NULL\n    type         VARCHAR(10) NOT NULL,              -- 'text' | 'image' | 'file' | 'call_log' | 'system'\n    text         TEXT CHECK (char_length(text) <= 10000),\n    attachments  JSONB,                             -- [{fileId, name, size, mime, width, height, thumbFileId}]\n    call_log     JSONB,                             -- {callId, durationSec, result}\n    deleted_at   TIMESTAMPTZ,\n    created_at   TIMESTAMPTZ NOT NULL DEFAULT now(),\n    UNIQUE (room_id, seq)                           -- \uc99d\ubd84 \ub3d9\uae30\ud654 \ucffc\ub9ac \uace0\uc18d \uc778\ub371\uc2a4 \uacb8\uc6a9\n);\nCREATE INDEX idx_messages_room_created ON messages(room_id, created_at DESC);\n\n-- 9. \ud30c\uc77c \uc800\uc7a5\uc18c \uba54\ud0c0\ub370\uc774\ud130 (Files)\nCREATE TABLE files (\n    id            UUID PRIMARY KEY DEFAULT gen_random_uuid(),\n    uploader_id   UUID NOT NULL REFERENCES users(id),\n    message_id    UUID REFERENCES messages(id) ON DELETE SET NULL,\n    purpose       VARCHAR(10) NOT NULL,             -- 'message' | 'avatar'\n    storage_path  TEXT NOT NULL,                    -- NAS \uc800\uc7a5 \uc0c1\ub300\uacbd\ub85c (\uc608: 2026\/10\/uuid.jpg)\n    name          VARCHAR(255) NOT NULL,\n    mime_type     VARCHAR(100),\n    size          BIGINT NOT NULL,\n    sha256        CHAR(64),\n    status        VARCHAR(10) NOT NULL DEFAULT 'uploading',  -- 'uploading' | 'ready'\n    created_at    TIMESTAMPTZ NOT NULL DEFAULT now()\n);\n\n-- 10. \ubcf4\uc774\uc2a4\ud1a1 \ud1b5\ud654 \uae30\ub85d (Calls & Call Participants)\nCREATE TABLE calls (\n    id            UUID PRIMARY KEY DEFAULT gen_random_uuid(),\n    room_id       UUID NOT NULL REFERENCES rooms(id) ON DELETE CASCADE,\n    caller_id     UUID NOT NULL REFERENCES users(id),\n    status        VARCHAR(10) NOT NULL,             -- 'ringing'|'accepted'|'declined'|'missed'|'ended'\n    created_at    TIMESTAMPTZ NOT NULL DEFAULT now(),\n    accepted_at   TIMESTAMPTZ,\n    ended_at      TIMESTAMPTZ\n);\n\nCREATE TABLE call_participants (\n    call_id    UUID NOT NULL REFERENCES calls(id) ON DELETE CASCADE,\n    user_id    UUID NOT NULL REFERENCES users(id),\n    joined_at  TIMESTAMPTZ,\n    left_at    TIMESTAMPTZ,\n    PRIMARY KEY (call_id, user_id)\n);<\/pre>\n\n\n\n
            \n\n\n\n
            3.3 \ub864\ubc31\uc6a9 \uc2a4\ud0a4\ub9c8 (server\/migrations\/000001_init_schema.down.sql<\/code>)<\/h3>\n\n\n\n
            \ub9c8\uc774\uadf8\ub808\uc774\uc158 \ub3c4\uad6c\ub294 \uc2e4\uc218\ub97c \ub418\ub3cc\ub9b4 \ub54c\ub97c \ub300\ube44\ud574 \ud14c\uc774\ube14\uc744 \uc9c0\uc6b0\ub294 .down.sql<\/code>\ub3c4 \ud544\uc694\ub85c \ud569\ub2c8\ub2e4.
            \uc0dd\uc131 \uc21c\uc11c\uc758 \uc5ed\uc21c\uc73c\ub85c \uc678\ub798\ud0a4 \uc81c\uc57d\uc870\uac74\uc5d0 \uac78\ub9ac\uc9c0 \uc54a\uac8c \uae54\ub054\ud558\uac8c \uc9c0\uc6b0\ub294 \ucf54\ub4dc\uc785\ub2c8\ub2e4:<\/p>\n\n\n\n
            DROP TABLE IF EXISTS call_participants CASCADE;\nDROP TABLE IF EXISTS calls CASCADE;\nDROP TABLE IF EXISTS files CASCADE;\nDROP TABLE IF EXISTS messages CASCADE;\nDROP TABLE IF EXISTS room_members CASCADE;\nDROP TABLE IF EXISTS rooms CASCADE;\nDROP TABLE IF EXISTS friendships CASCADE;\nDROP TABLE IF EXISTS invite_codes CASCADE;\nDROP TABLE IF EXISTS devices CASCADE;\nDROP TABLE IF EXISTS auth_identities CASCADE;\nDROP TABLE IF EXISTS users CASCADE;<\/pre>\n\n\n\n
            \n\n\n\n
            4. sqlc<\/code> \uc124\uce58 \ubc0f \uc124\uc815 (sqlc.yaml<\/code>)<\/h2>\n\n\n\n
            4.1 sqlc CLI \uc124\uce58 (Mac \uae30\uc900)<\/h3>\n\n\n\n
            \ub9e5\ubd81 \ud130\ubbf8\ub110\uc5d0\uc11c Homebrew\ub85c \uac04\ud3b8\ud558\uac8c \uc124\uce58\ud560 \uc218 \uc788\uc2b5\ub2c8\ub2e4:<\/p>\n\n\n\n
            brew install sqlc<\/pre>\n\n\n\n
            4.2 sqlc.yaml<\/code> \uc124\uc815 \ud30c\uc77c \uc791\uc131 (\uc2e4\uc804 \uafc0\ud301: Engine \ud544\uc218, UUID & Pointer \ub9e4\ud551)<\/h3>\n\n\n\n
            server\/sqlc.yaml<\/code> \uacbd\ub85c\uc5d0 \uc124\uc815 \ud30c\uc77c\uc744 \uc0dd\uc131\ud569\ub2c8\ub2e4.<\/p>\n\n\n\n
            \n
            \ud83d\udca1 \ud575\uc2ec \ud301 3\uac00\uc9c0: sqlc v2 + pgx\/v5 \uc644\ubcbd \ud638\ud658 \uc124\uc815<\/strong><\/p>\n\n\n\n
              \n
            1. engine: \"postgresql\"<\/code><\/strong>: sqlc v2\uc5d0\uc11c\ub294 \uc5d4\uc9c4 \uba85\uc2dc\uac00 \ud544\uc218\uc785\ub2c8\ub2e4. \ube60\uc9c0\uba74 engine is required<\/code> \uc5d0\ub7ec\uac00 \ubc1c\uc0dd\ud569\ub2c8\ub2e4.<\/li>\n\n\n\n
            2. emit_pointers_for_null_types: true<\/code><\/strong>: sqlc + pgx\/v5 \uc870\ud569\uc5d0\uc11c \uc774 \uc635\uc158\uc774 \uc5c6\uc73c\uba74 Nullable \uceec\ub7fc\uc774\ub098 sqlc.narg<\/code> \ud30c\ub77c\ubbf8\ud130\uac00 \ub2e4\ub8e8\uae30 \ubc88\uac70\ub85c\uc6b4 pgtype.Text<\/code> \uac19\uc740 \uad6c\uc870\uccb4\ub85c \uc0dd\uc131\ub429\ub2c8\ub2e4. \uc774 \uc635\uc158\uc744 \ucf1c\uba74 \uce5c\uc219\ud55c Go\uc758 \ub124\uc774\ud2f0\ube0c \ud3ec\uc778\ud130(*string<\/code>, *int<\/code> \ub4f1)\ub85c \uc0dd\uc131\ub418\uc5b4 \ucf54\ub4dc\uac00 \ube44\uc57d\uc801\uc73c\ub85c \uae54\ub054\ud574\uc9d1\ub2c8\ub2e4!<\/li>\n\n\n\n
            3. Nullable UUID\uc758 pointer: true<\/code> \uac1d\uccb4 \ubb38\ubc95<\/strong>: \ubb38\uc790\uc5f4\uc5d0 *github...<\/code>\ucc98\ub7fc *<\/code>\ub97c \ubd99\uc774\uba74 sqlc \ud30c\uc11c\uac00 \uc798\ubabb\ub41c import \ucf54\ub4dc\ub97c \uc0dd\uc131\ud569\ub2c8\ub2e4. \ud3ec\uc778\ud130 \ud0c0\uc785\uc740 \ubc18\ub4dc\uc2dc \uc544\ub798\ucc98\ub7fc import<\/code>, type<\/code>, pointer: true<\/code> \uac1d\uccb4 \ud615\ud0dc\ub85c \uc120\uc5b8\ud574\uc57c \uc62c\ubc14\ub978 *uuid.UUID<\/code> \ucf54\ub4dc\uac00 \uc0dd\uc131\ub429\ub2c8\ub2e4.<\/li>\n<\/ol>\n<\/blockquote>\n\n\n\n
              version: \"2\"\nsql:\n  - engine: \"postgresql\"\n    schema: \"migrations\"\n    queries: \"queries\"\n    gen:\n      go:\n        package: \"db\"\n        out: \"internal\/database\/db\"\n        sql_package: \"pgx\/v5\"\n        emit_json_tags: true\n        emit_prepared_queries: false\n        emit_interface: true\n        emit_empty_slices: true\n        emit_pointers_for_null_types: true\n        overrides:\n          - db_type: \"uuid\"\n            go_type: \"[github.com\/google\/uuid.UUID](https:\/\/github.com\/google\/uuid.UUID)\"\n          - db_type: \"uuid\"\n            nullable: true\n            go_type:\n              import: \"[github.com\/google\/uuid](https:\/\/github.com\/google\/uuid)\"\n              type: \"UUID\"\n              pointer: true<\/pre>\n\n\n\n
              \n\n\n\n
              5. SQL \ucffc\ub9ac \uc791\uc131 \ubc0f \uc790\ub3d9 \ucf54\ub4dc \uc0dd\uc131 \uc2e4\uc2b5<\/h2>\n\n\n\n
              \uc774\uc81c server\/queries\/<\/code> \ud3f4\ub354\uc5d0 \uc6b0\ub9ac\uac00 \ud544\uc694\ud55c \uc2e4\uc81c \ucffc\ub9ac\ub97c \uc791\uc131\ud574 \ubd05\ub2c8\ub2e4.<\/p>\n\n\n\n
              5.1 \ud68c\uc6d0 \uad00\ub828 \ucffc\ub9ac (server\/queries\/users.sql<\/code>) – sqlc.narg<\/code>\ub85c \uc548\uc804\ud55c \uc120\ud0dd\uc801 \uc5c5\ub370\uc774\ud2b8<\/h3>\n\n\n\n
              \n
              \ud83d\udca1 \ud575\uc2ec \ud301: sqlc.narg<\/code>\ub97c \ud65c\uc6a9\ud55c Nullable \ud30c\ub77c\ubbf8\ud130 \ucc98\ub9ac<\/strong>
              display_name<\/code> \uceec\ub7fc\uc774 NOT NULL<\/code>\uc77c \ub54c \ub2e8\uc21c\ud788 COALESCE($2, display_name)<\/code>\uc744 \uc4f0\uba74 sqlc<\/code>\uac00 $2<\/code>\ub97c \uc77c\ubc18 string<\/code>\uc73c\ub85c \ub9cc\ub4e4\uc5b4 \ubc84\ub9bd\ub2c8\ub2e4. Go\uc5d0\uc11c \ube48 \ubb38\uc790\uc5f4(\"\"<\/code>)\uc740 SQL\uc758 NULL<\/code>\uc774 \uc544\ub2c8\ubbc0\ub85c, \uc774\ub984\uc744 \ubc14\uafb8\uc9c0 \uc54a\uc73c\ub824\uace0 \ube48 \uac12\uc744 \ub118\uacbc\ub2e4\uac00 \uc774\ub984\uc774 \uacf5\ubc31\uc73c\ub85c \uc9c0\uc6cc\uc9c0\ub294 \ub300\ud615 \ubc84\uadf8\uac00 \uc0dd\uae41\ub2c8\ub2e4.
              sqlc.narg('\uc774\ub984')<\/code>\uc744 \uc4f0\uba74 Go\uc5d0\uc11c \ud3ec\uc778\ud130(*string<\/code>) \ud0c0\uc785\uc73c\ub85c \uc790\ub3d9 \uc0dd\uc131\ub418\uc5b4, nil<\/code>\uc744 \ub118\uacbc\uc744 \ub54c \uc548\uc804\ud558\uac8c \uae30\uc874 \uac12\uc744 \uc720\uc9c0\ud569\ub2c8\ub2e4!<\/p>\n<\/blockquote>\n\n\n\n
              -- name: CreateUser :one\nINSERT INTO users (display_name, status_message, phone_hash, role)\nVALUES ($1, $2, $3, $4)\nRETURNING *;\n\n-- name: GetUserByID :one\nSELECT * FROM users\nWHERE id = $1 AND status != 'deleted';\n\n-- name: GetUserByPhoneHash :one\nSELECT * FROM users\nWHERE phone_hash = $1 AND status = 'active';\n\n-- name: UpdateUserProfile :one\nUPDATE users\nSET display_name = COALESCE(sqlc.narg('display_name'), display_name),\n    status_message = COALESCE(sqlc.narg('status_message'), status_message),\n    avatar_file_id = COALESCE(sqlc.narg('avatar_file_id'), avatar_file_id),\n    updated_at = now()\nWHERE id = sqlc.arg('id')\nRETURNING *;<\/pre>\n\n\n\n
              5.2 \ucc44\ud305 \uba54\uc2dc\uc9c0 \uc804\uc1a1 \ucffc\ub9ac (server\/queries\/messages.sql<\/code>) – CTE\ub97c \ud1b5\ud55c \uc6d0\uc790\uc131 \ud655\ubcf4<\/h3>\n\n\n\n
              \n
              \ud83d\udca1 \ud575\uc2ec \ud301: CTE(WITH \uad6c\ubb38)\ub85c \ud2b8\ub79c\uc7ad\uc158 \uc5c6\uc774 1\ubc29\uc5d0 \uc6d0\uc790\uc801(Atomic) \ubc1c\uc1a1<\/strong>
              \uba54\uc2dc\uc9c0 \ubc1c\uc1a1 \uc2dc **”\ubc29 \ucd5c\uc2e0 \uc21c\ubc88 1 \uc99d\uac00(IncrementRoomSeq)”**\uc640 **”\uba54\uc2dc\uc9c0 \uc0bd\uc785(InsertMessage)”**\uc744 \ubcc4\ub3c4 \ucffc\ub9ac\ub85c \ub098\ub204\uba74 \ub124\ud2b8\uc6cc\ud06c \uc655\ubcf5\uc774 2\ubc88 \ubc1c\uc0dd\ud558\uace0, \uc911\uac04\uc5d0 \uc11c\ubc84\uac00 \uaebc\uc9c8 \uacbd\uc6b0 \uc21c\ubc88\ub9cc \uc62c\ub77c\uac00\uace0 \uba54\uc2dc\uc9c0\ub294 \uc720\uc2e4\ub418\ub294 \ubd88\uc77c\uce58\uac00 \uc0dd\uae38 \uc218 \uc788\uc2b5\ub2c8\ub2e4.
              PostgreSQL\uc758 CTE(WITH ...<\/code>)<\/strong> \uad6c\ubb38\uc744 \uc4f0\uba74 **\ub2e8 1\ud68c\uc758 \ucffc\ub9ac\ub85c \uc21c\ubc88 \uc99d\uac00\uc640 \uba54\uc2dc\uc9c0 \uc800\uc7a5\uc744 \uc644\ubcbd\ud55c \uc6d0\uc790\uc131(Atomic)**\uc73c\ub85c \ucc98\ub9ac\ud560 \uc218 \uc788\uc2b5\ub2c8\ub2e4!<\/p>\n<\/blockquote>\n\n\n\n
              -- name: SendMessageWithSeq :one\nWITH next_seq AS (\n    UPDATE rooms\n    SET last_seq = last_seq + 1,\n        updated_at = now()\n    WHERE id = $1\n    RETURNING last_seq\n)\nINSERT INTO messages (\n    id,\n    room_id,\n    seq,\n    sender_id,\n    type,\n    text,\n    attachments,\n    call_log\n)\nSELECT\n    $2,\n    $1,\n    next_seq.last_seq,\n    $3,\n    $4,\n    $5,\n    $6,\n    $7\nFROM next_seq\nRETURNING *;\n\n-- name: GetMessagesAfterSeq :many\nSELECT * FROM messages\nWHERE room_id = $1 AND seq > $2\nORDER BY seq ASC\nLIMIT $3;<\/pre>\n\n\n\n
              5.3 \ub9c8\ubc95\uc758 \ud55c \uc904 \uc2e4\ud589: sqlc generate<\/code><\/h3>\n\n\n\n
              \ud130\ubbf8\ub110\uc5d0\uc11c server\/<\/code> \ub514\ub809\ud1a0\ub9ac\ub85c \uc774\ub3d9\ud55c \ub4a4 \uc544\ub798 \uba85\ub839\uc5b4\ub97c \uc2e4\ud589\ud569\ub2c8\ub2e4:<\/p>\n\n\n\n
              cd server\nsqlc generate<\/pre>\n\n\n\n
              \uba85\ub839\uc5b4\uac00 \ub05d\ub098\uba74 server\/internal\/database\/db\/<\/code> \ud3f4\ub354\uc5d0 \ub2e4\uc74c\uacfc \uac19\uc740 Go \ud30c\uc77c\ub4e4\uc774 \ub9c8\ubc95\ucc98\ub7fc \uc0dd\uc131\ub429\ub2c8\ub2e4:<\/p>\n\n\n\n