인증 커리큘럼으로
기술 문서 (프로토타입 버전)
User DB Sync
저장소의 docs/user-database-sync.md 와 동일한 원문입니다. 아래에서 Markdown과 HTML 변환 결과를 각각 복사할 수 있습니다.
공개 문서 원문 (Markdown)
# User Database Sync — SQL Migration & Verification
**Project:** saas-login-auth-checkout
**Purpose:** Sync auth users into `public.users` and have the dashboard read from it.
---
## STEP 1 — Create `public.users` table
**Migration:** `supabase/migrations/001_create_users_and_trigger.sql`
- **Table:** `public.users`
- `id` uuid PRIMARY KEY (matches `auth.users.id`)
- `email` text
- `provider` text
- `created_at` timestamptz DEFAULT now()
- **RLS:** Enabled; policy "Users can read own row" (SELECT where `auth.uid() = id`).
---
## STEP 2 — Trigger on `auth.users`
Same migration file:
- **Function:** `public.handle_new_auth_user()` (SECURITY DEFINER)
- Maps: `id` ← `NEW.id`, `email` ← `NEW.email`, `provider` ← `NEW.raw_app_meta_data->>'provider'` (fallback `'email'`), `created_at` ← `NEW.created_at`
- Uses `ON CONFLICT (id) DO UPDATE` so existing rows are updated if auth row is updated.
- **Trigger:** `on_auth_user_created` AFTER INSERT ON `auth.users` → `EXECUTE FUNCTION public.handle_new_auth_user()`.
---
## STEP 3 — Sync existing users
**Migration:** `supabase/migrations/002_sync_existing_users.sql`
- Inserts from `auth.users` into `public.users` with `ON CONFLICT (id) DO UPDATE`.
- Run **once** after `001` to backfill existing users.
---
## How to run
### Option A: Supabase Dashboard (recommended)
1. Open [Supabase Dashboard](https://supabase.com/dashboard) → your project → **SQL Editor**.
2. Run **001** first: copy/paste `supabase/migrations/001_create_users_and_trigger.sql` → Run.
3. Run **002** once for backfill: copy/paste `supabase/migrations/002_sync_existing_users.sql` → Run.
### Option B: Supabase CLI
From project root:
```bash
npx supabase db push
```
(Requires `supabase` linked to your project; migrations in `supabase/migrations/` are applied in order.)
---
## STEP 4 — App behavior
- **lib/user.ts:** `getCurrentUser()` uses `supabase.auth.getUser()` for session, then reads `public.users` by `id`. If no row yet (e.g. trigger not run), falls back to auth user payload.
- **Dashboard** already uses `getCurrentUser()`; no code change. It now effectively reads from `public.users`.
---
## STEP 5 — Verification
### 1. Apply migrations
- Run `001_create_users_and_trigger.sql` and `002_sync_existing_users.sql` in SQL Editor (or via CLI).
### 2. Check table and RLS
In SQL Editor:
```sql
SELECT * FROM public.users LIMIT 5;
```
- You should see columns: `id`, `email`, `provider`, `created_at`.
- If you had existing auth users, they should appear after running `002`.
### 3. Test Google login
1. Log out (or use incognito).
2. Go to `/login` → **Google Login**.
3. Complete OAuth and land on `/dashboard`.
4. In Supabase: **Table Editor** → `public.users` → confirm a new row with your email and `provider = 'google'`.
### 4. Test Kakao login
1. Log out (or use another browser/incognito).
2. Go to `/login` → **Kakao Login**.
3. Complete OAuth and land on `/dashboard`.
4. In **Table Editor** → `public.users` → confirm a new row with your email and `provider = 'kakao'`.
### 5. Dashboard shows data from `public.users`
- Dashboard displays **User email**, **ID**, **Provider**.
- Those values come from `getCurrentUser()` → `public.users` (with auth fallback when row is missing).
### 6. Sync script idempotency
- Run `002_sync_existing_users.sql` again.
- No duplicate rows; existing rows are updated by `ON CONFLICT (id) DO UPDATE`.
---
## Files reference
| File | Purpose |
|------|--------|
| `supabase/migrations/001_create_users_and_trigger.sql` | Create `public.users`, RLS, trigger on `auth.users` |
| `supabase/migrations/002_sync_existing_users.sql` | One-time sync of existing `auth.users` → `public.users` |
| `lib/user.ts` | `getCurrentUser()` reads from `public.users` (fallback to auth) |
| `app/dashboard/page.tsx` | Uses `getCurrentUser()` (unchanged; now backed by `public.users`) |
---
## Troubleshooting
- **No row after OAuth:** Trigger runs on INSERT. If the user already existed in `auth.users`, run `002_sync_existing_users.sql` to backfill.
- **RLS errors:** Ensure "Users can read own row" policy exists and you’re authenticated; `getCurrentUser()` uses the anon key with the user’s JWT so RLS allows reading their row.
- **Trigger not firing:** Confirm trigger `on_auth_user_created` exists:
`SELECT * FROM pg_trigger WHERE tgname = 'on_auth_user_created';`
공개 문서 변환 코드 (HTML)
<h1>User Database Sync — SQL Migration & Verification</h1>
<p><strong>Project:</strong> saas-login-auth-checkout<br><strong>Purpose:</strong> Sync auth users into <code>public.users</code> and have the dashboard read from it.</p>
<hr>
<h2>STEP 1 — Create <code>public.users</code> table</h2>
<p><strong>Migration:</strong> <code>supabase/migrations/001_create_users_and_trigger.sql</code></p>
<ul>
<li><strong>Table:</strong> <code>public.users</code><ul>
<li><code>id</code> uuid PRIMARY KEY (matches <code>auth.users.id</code>)</li>
<li><code>email</code> text</li>
<li><code>provider</code> text</li>
<li><code>created_at</code> timestamptz DEFAULT now()</li>
</ul>
</li>
<li><strong>RLS:</strong> Enabled; policy "Users can read own row" (SELECT where <code>auth.uid() = id</code>).</li>
</ul>
<hr>
<h2>STEP 2 — Trigger on <code>auth.users</code></h2>
<p>Same migration file:</p>
<ul>
<li><strong>Function:</strong> <code>public.handle_new_auth_user()</code> (SECURITY DEFINER)<ul>
<li>Maps: <code>id</code> ← <code>NEW.id</code>, <code>email</code> ← <code>NEW.email</code>, <code>provider</code> ← <code>NEW.raw_app_meta_data->>'provider'</code> (fallback <code>'email'</code>), <code>created_at</code> ← <code>NEW.created_at</code></li>
<li>Uses <code>ON CONFLICT (id) DO UPDATE</code> so existing rows are updated if auth row is updated.</li>
</ul>
</li>
<li><strong>Trigger:</strong> <code>on_auth_user_created</code> AFTER INSERT ON <code>auth.users</code> → <code>EXECUTE FUNCTION public.handle_new_auth_user()</code>.</li>
</ul>
<hr>
<h2>STEP 3 — Sync existing users</h2>
<p><strong>Migration:</strong> <code>supabase/migrations/002_sync_existing_users.sql</code></p>
<ul>
<li>Inserts from <code>auth.users</code> into <code>public.users</code> with <code>ON CONFLICT (id) DO UPDATE</code>.</li>
<li>Run <strong>once</strong> after <code>001</code> to backfill existing users.</li>
</ul>
<hr>
<h2>How to run</h2>
<h3>Option A: Supabase Dashboard (recommended)</h3>
<ol>
<li>Open <a href="https://supabase.com/dashboard">Supabase Dashboard</a> → your project → <strong>SQL Editor</strong>.</li>
<li>Run <strong>001</strong> first: copy/paste <code>supabase/migrations/001_create_users_and_trigger.sql</code> → Run.</li>
<li>Run <strong>002</strong> once for backfill: copy/paste <code>supabase/migrations/002_sync_existing_users.sql</code> → Run.</li>
</ol>
<h3>Option B: Supabase CLI</h3>
<p>From project root:</p>
<pre><code class="language-bash">npx supabase db push
</code></pre>
<p>(Requires <code>supabase</code> linked to your project; migrations in <code>supabase/migrations/</code> are applied in order.)</p>
<hr>
<h2>STEP 4 — App behavior</h2>
<ul>
<li><strong>lib/user.ts:</strong> <code>getCurrentUser()</code> uses <code>supabase.auth.getUser()</code> for session, then reads <code>public.users</code> by <code>id</code>. If no row yet (e.g. trigger not run), falls back to auth user payload.</li>
<li><strong>Dashboard</strong> already uses <code>getCurrentUser()</code>; no code change. It now effectively reads from <code>public.users</code>.</li>
</ul>
<hr>
<h2>STEP 5 — Verification</h2>
<h3>1. Apply migrations</h3>
<ul>
<li>Run <code>001_create_users_and_trigger.sql</code> and <code>002_sync_existing_users.sql</code> in SQL Editor (or via CLI).</li>
</ul>
<h3>2. Check table and RLS</h3>
<p>In SQL Editor:</p>
<pre><code class="language-sql">SELECT * FROM public.users LIMIT 5;
</code></pre>
<ul>
<li>You should see columns: <code>id</code>, <code>email</code>, <code>provider</code>, <code>created_at</code>.</li>
<li>If you had existing auth users, they should appear after running <code>002</code>.</li>
</ul>
<h3>3. Test Google login</h3>
<ol>
<li>Log out (or use incognito).</li>
<li>Go to <code>/login</code> → <strong>Google Login</strong>.</li>
<li>Complete OAuth and land on <code>/dashboard</code>.</li>
<li>In Supabase: <strong>Table Editor</strong> → <code>public.users</code> → confirm a new row with your email and <code>provider = 'google'</code>.</li>
</ol>
<h3>4. Test Kakao login</h3>
<ol>
<li>Log out (or use another browser/incognito).</li>
<li>Go to <code>/login</code> → <strong>Kakao Login</strong>.</li>
<li>Complete OAuth and land on <code>/dashboard</code>.</li>
<li>In <strong>Table Editor</strong> → <code>public.users</code> → confirm a new row with your email and <code>provider = 'kakao'</code>.</li>
</ol>
<h3>5. Dashboard shows data from <code>public.users</code></h3>
<ul>
<li>Dashboard displays <strong>User email</strong>, <strong>ID</strong>, <strong>Provider</strong>.</li>
<li>Those values come from <code>getCurrentUser()</code> → <code>public.users</code> (with auth fallback when row is missing).</li>
</ul>
<h3>6. Sync script idempotency</h3>
<ul>
<li>Run <code>002_sync_existing_users.sql</code> again.</li>
<li>No duplicate rows; existing rows are updated by <code>ON CONFLICT (id) DO UPDATE</code>.</li>
</ul>
<hr>
<h2>Files reference</h2>
<table>
<thead>
<tr>
<th>File</th>
<th>Purpose</th>
</tr>
</thead>
<tbody><tr>
<td><code>supabase/migrations/001_create_users_and_trigger.sql</code></td>
<td>Create <code>public.users</code>, RLS, trigger on <code>auth.users</code></td>
</tr>
<tr>
<td><code>supabase/migrations/002_sync_existing_users.sql</code></td>
<td>One-time sync of existing <code>auth.users</code> → <code>public.users</code></td>
</tr>
<tr>
<td><code>lib/user.ts</code></td>
<td><code>getCurrentUser()</code> reads from <code>public.users</code> (fallback to auth)</td>
</tr>
<tr>
<td><code>app/dashboard/page.tsx</code></td>
<td>Uses <code>getCurrentUser()</code> (unchanged; now backed by <code>public.users</code>)</td>
</tr>
</tbody></table>
<hr>
<h2>Troubleshooting</h2>
<ul>
<li><strong>No row after OAuth:</strong> Trigger runs on INSERT. If the user already existed in <code>auth.users</code>, run <code>002_sync_existing_users.sql</code> to backfill.</li>
<li><strong>RLS errors:</strong> Ensure "Users can read own row" policy exists and you’re authenticated; <code>getCurrentUser()</code> uses the anon key with the user’s JWT so RLS allows reading their row.</li>
<li><strong>Trigger not firing:</strong> Confirm trigger <code>on_auth_user_created</code> exists:<br><code>SELECT * FROM pg_trigger WHERE tgname = 'on_auth_user_created';</code></li>
</ul>