Publish pipeline

Local Postgres ──Step 1──► lab_* (staging) ──Step 2──► production (live / Tabula)
```text

| Step | Action | Target | Criteria | |------|--------|--------|----------| | **1** | Publish | `lab_verbs`, `lab_sentences`, … | Kifuliiru optional locally; insert-only | | **2** | Promote | `verbs`, `sentences`, … | Native Kifuliiru required; insert-only |

**Do not** skip staging for routine work — production is for reviewed, native-authored content.

---

## Step 1 — Local → lab_*

### UI

1. Open **`/data-management/publish-to-cloud`**
2. Select **Lab staging (lab_*)**
3. **Scan all tables** → review pending counts
4. Select tables → **Publish selected**

### API

```http
GET /api/mapper/compare?target=lab
POST /api/mapper/sync-batch
Content-Type: application/json

{ "tables": ["verbs", "sentences"], "target": "lab", "publishReady": false }
```text

### What happens

- Reads rows from **local PostgreSQL**
- Inserts **new** rows into **`lab_*`** Supabase tables
- Dedup by English or Kifuliiru-side key — skips rows already in cloud
- Never updates existing cloud rows

---

## Step 2 — lab_* → Production

### UI

Same page — **Step 2 — Promote lab_* → Production** panel:

1. **Scan promotion** — compares staging vs production per table
2. Review **Pending** column (eligible rows not yet in production)
3. Select tables → **Promote selected** (confirmation required)

### API

```http
GET /api/mapper/compare-promote
GET /api/mapper/compare-promote?table=verbs

POST /api/mapper/promote-batch
Content-Type: application/json

{ "tables": ["verbs", "sentences"], "limit": 0 }
```text

### Production promotion criteria

A lab row is promoted only if **all** apply:

1. Present in **`lab_*`** staging
2. **Native Kifuliiru** — not `[MISSING_KIFULIIRU]`, not `UkuEnglish`-style placeholder
3. Has Kifuliiru-side text (`verb_kifuliiru`, `kifuliiru`, `igambo`, …)
4. Dedup key **not already** in production
5. **Insert only** — never overwrites production

Implementation: `src/lib/utils/publish-promotion.ts`

Bridge languages (EN/FR/SWA) remain optional for production, same as existing publish policy.

---

## Admin workflow (recommended)

```bash
# 1. See gaps
npm run audit:cloud

# 2. Step 1 in UI — publish verbs, sentences, dictionary_nouns to lab_*

# 3. Re-audit — lab_* counts should increase

# 4. Step 2 in UI — scan promotion → promote eligible rows

# 5. Re-audit — production counts should increase

# 6. (Planned) Refresh impact_variables corpus slugs
```text

### Suggested first batch

**Step 1:** `verbs`, `sentences`, `dictionary_nouns`, `conjugation_rules` 
**Step 2:** Same tables after reviewing staging in deployed Lab

---

## Deployed Lab — read from `lab_*` (table name mismatch)

**Problem:** Step 1 writes to **`lab_verbs`**, **`lab_sentences`**, etc. Local dev reads **`verbs`**, **`sentences`** from PostgreSQL. On deploy there is no local DB, so pages stay empty unless reads target Supabase staging.

**Two fixes (pick by goal):**

| Goal | What to do | |------|------------| | **Deployed Lab shows your published staging data** | Set deploy env (see below). Overview / stats use `lab_*` via Supabase REST. | | **Tabula / public app reads `verbs`, `sentences` (no `lab_` prefix)** | Use **Step 2 — Promote** on `/data-management/publish-to-cloud` to copy eligible rows into production tables. |

### Deploy env (Lab reads staging)

```bash
CORPUS_DATA_SOURCE=supabase-lab
# or auto-detect: use local PG when reachable, else lab_*
# CORPUS_DATA_SOURCE=auto

SUPABASE_URL=...
SUPABASE_ANON_KEY=...
SUPABASE_SERVICE_ROLE_KEY=... # server-side reads of lab_* (recommended on deploy)
```text

**Today:** `/api/stats` and overview counts respect `CORPUS_DATA_SOURCE`. Verbs/dictionary **list pages** still use local Postgres only — wire those next if you need full browsing on deploy (Phase 3b).

**Table names:** local `dictionary_conjugated_verbs` → Supabase `lab_dictionary_conjugated_verbs`.

One-off rename (legacy `lab_dictionary_other` only, no other tables touched):

`scripts/sql/rename-lab-dictionary-other-to-conjugated-verbs.sql` — run in Supabase SQL Editor, or `npm run db:rename-lab-dictionary-other` with `SUPABASE_DB_URL` set.

---

## Escape hatch — direct local → production

Still available for emergencies (select **Production** in Step 1), but:

- Requires Kifuliiru-side text always
- Skips staging review
- UI warns to prefer lab → promote

Use only when staging table does not exist or for one-off hotfixes.

---

## Security

- Promote and compare-promote APIs require **lab admin** session (`requireLabAdminApi`)
- RLS on Supabase tables — admin / super_admin role
- Never commit service keys; use `.env` locally

---

## Code map

| Piece | Location | |-------|----------| | Local → lab sync | `src/lib/services/db-mapper.ts` → `syncToSupabase` | | Lab → prod promote | `db-mapper.ts` → `promoteLabToProduction` | | Promotion criteria | `src/lib/utils/publish-promotion.ts` | | Publish UI Step 1 | `src/app/data-management/publish-to-cloud/page.tsx` | | Publish UI Step 2 | `src/components/publish/PublishPromotionPanel.tsx` | | Compare promote API | `src/app/api/mapper/compare-promote/route.ts` | | Promote batch API | `src/app/api/mapper/promote-batch/route.ts` | | Deploy read config | `src/lib/database/corpus-data-source.ts` | | Stats from `lab_*` | `src/lib/database/corpus-supabase-stats.ts` |

---

## Troubleshooting

| Issue | Fix | |-------|-----| | Step 2 all pending = 0, lab empty | Run Step 1 first | | Promote errors on dictionary tables | Production table may not exist in Supabase yet | | Rows skipped on promote | Placeholder Kifuliiru or already in production | | 401 on promote API | Log in as admin |