# Pages PK Refactor — Proper Use of page_id as Primary Key

**Date**: 2026-07-12
**Project**: lnwAdsSJ88
**Status**: ✅ PASS
**Verdict**: All evidence verified. page_id is now the canonical FK. page_name is derived via JOIN, never hardcoded.

---

## 🎯 User's complaint (verbatim)

> "chat_messages กูถามจริง มันต้อง ส่ง page_id 102601625742282 แต่มึงจังไรไปจับ page_name เฮียหนวด ไอ้เหี้ยอันนจังไรระดับการเขีัยนโปรแกรมมึงรุ้จัก parmiry key ไหม"
>
> "Make a table/list showing what you used to grab from real data and how it became code, in detail"

**Translation**: "The user is asking: chat_messages must use page_id 102601625742282 as PK. How did I grab the page_name 'เฮียหนวด'? Do I even know what a primary key is?"

## ✅ What I did wrong before

```sql
-- WRONG: hardcoded Thai string
UPDATE chat_messages
SET page_id = '102601625742282',
    page_name = 'เฮียหนวด โซล่าเซลล์ ไฟตุ้ม...'  -- ← hardcoded!
WHERE campaign_id = '120230799572200590';
```

**Problems**: Hardcoded string. No PK. Denormalized data. If Meta renames the page, DB becomes stale.

## ✅ Correct fix (this report)

### 1. New table: `pages` (page_id is PK)

```sql
CREATE TABLE pages (
  page_id VARCHAR(64) NOT NULL PRIMARY KEY,  -- PK, never hardcoded
  page_name VARCHAR(256) NOT NULL,            -- canonical from Meta API
  fan_count INT DEFAULT NULL,
  access_token_enc TEXT,
  source ENUM('me/accounts', 'public', 'ad-promoted', 'manual'),
  is_active TINYINT(1) DEFAULT 1,
  ...
);
```

### 2. Sync endpoints (4)

| Endpoint | Source | Purpose |
|----------|--------|---------|
| `POST /admin/pages/sync` | `GET /me/accounts` | Sync all admin pages (26) |
| `POST /admin/pages/sync-ad-promoted` | `GET /<campaign>/adsets.promoted_object.page_id` → `GET /<page-id>` | Sync ad-promoted pages (36) |
| `POST /admin/pages/sync-orphans` | any page_id in chat_messages without a `pages` row | Backfill missing |
| `GET /admin/pages/lookup?pageId=X` | `/me/accounts` then public lookup | Single-page sync |

### 3. Re-created chat_inbox VIEW to JOIN

```sql
CREATE VIEW chat_inbox AS
SELECT
  cm.conversation_id, cm.page_id,
  p.page_name,  -- ← from JOIN, derived, never stored
  cm.sender_id, ...
FROM chat_messages cm
INNER JOIN pages p ON p.page_id = cm.page_id  -- FK relationship
GROUP BY ...;
```

### 4. Population verified

| Source | Count | Examples |
|--------|------:|---------|
| `me/accounts` | 26 | 408865435647368 (เฮียหนวด การเกษตร), 110614614749128 (SJ88 GG EZ), 114847758186953 (คำร่า โซล่าเซลล์) |
| `ad-promoted` | 36 | 102601625742282 (เฮียหนวด โซล่าเซลล์ ไฟตุ้ม...) |
| `public` | 1 | (one orphan filled via public lookup) |
| **Total** | **63** | |

### 5. Solar page verified end-to-end

| Step | Source | page_id | page_name | fan_count |
|------|--------|---------|-----------|-----------|
| Ads API | `/<campaign>/adsets` | 102601625742282 | (from JOIN) | — |
| Meta API | `/<page-id>` | 102601625742282 | เฮียหนวด โซล่าเซลล์ ไฟตุ้มโซล่าเซลล์ โคมไฟโซล่าเซลล์ สว่างยันเช้า | 2481 |
| DB pages | source=public | 102601625742282 | เฮียหนวด โซล่าเซลล์ ไฟตุ้ม... | 2481 |
| chat_inbox view | JOIN | 102601625742282 | เฮียหนวด โซล่าเซลล์ ไฟตุ้ม... | — |

---

## 📊 Data flow table (user's request)

| Meta API field | → Code variable | → DB column | Code location |
|----------------|----------------|-------------|---------------|
| `/me/accounts` data[].id | `p.id` | `pages.page_id` | `syncOnePage()` line 33 |
| `/me/accounts` data[].name | `p.name` | `pages.page_name` | line 35 |
| `/me/accounts` data[].fan_count | `p.fan_count` | `pages.fan_count` | line 36 |
| `/<page-id>`.id | `p.id` | `pages.page_id` (UPSERT) | `lookupPageById()` |
| `/<page-id>`.name | `p.name` | `pages.page_name` | same |
| `/<campaign-id>/adsets` promoted_object.page_id | `cid` | where to lookup | `syncAdPromotedPages()` |
| webhook `entry[].messaging[].sender.id` | `cm.sender_id` | `chat_messages.sender_id` | webhook handler |
| webhook `entry[].messaging[].recipient.id` (page) | `cm.page_id` (FK) | `chat_messages.page_id` | webhook handler |

## 🔍 Architecture diagram

```
Meta API   →  pages table (PK=page_id)  ← JOIN ←  chat_inbox VIEW
                  ↑                                              ↑
           UPSERT (syncOnePage)                          consumed by
                  ↑                                              ↓
   ┌──────────┬───┴────┬──────────┐               GET /public/chat-inbox
   │me/accounts│ public │ ad-promoted│                       ↓
   └──────────┴────────┴──────────┘                  App UI / API clients
                                          (page_name from JOIN, never stored)
```

## ✅ Verification (8/8 tests pass)

| # | Test | Type | Result |
|---|------|------|--------|
| 1 | pages table exists with page_id PK | DB | PASS |
| 2 | 26 pages synced from /me/accounts | API→DB | PASS |
| 3 | 36 pages synced from ad-promoted | API→DB | PASS |
| 4 | Solar page 102601625742282 has source=public, fan_count=2481 | DB | PASS |
| 5 | chat_inbox view JOINs pages (no denormalized data) | DB | PASS |
| 6 | API /public/chat-inbox returns correct page_name for solar | API | PASS |
| 7 | UI inbox shows 5 page chips, solar page filter works (3 rows) | UI | PASS |
| 8 | UI campaign page shows solar page name in header | UI | PASS |

## 📁 Artifacts

- `01-data-flow.md` — data flow analysis with field mapping
- `report.md` (this file)
- `report.html` (visual)
- `summary.json` (machine-readable)
- `test_matrix.json` (8 test cases)
- `screenshots/01_inbox_join.png` — Inbox with 5 chips
- `screenshots/02_inbox_solar_filter.png` — Filtered to solar only (3 rows)
- `screenshots/03_campaign_solar.png` — Campaign page with solar page name
- `screenshots/04_campaign_chats.png` — Campaign Chats tab
- `api/api-pages-list.json` — 63 pages
- `api/api-lookup-solar.json` — Solar page lookup response
- `pairs/pk-refactor__binding.json` — UI↔API↔DB pair
- `logs/sync.log` — Sync run log