# Integration Assessment Prompt for Remote Blog Sites

> **What is this file?**  
> This is an **executable prompt** designed to be pasted into an AI IDE (Cursor, Windsurf, Gemini, Claude, etc.) when working on a remote blog site that integrates with Ranktraction. The AI will scan the blog's codebase, check for all required integration endpoints, validate the database schema, and produce a compatibility report showing what's available and what's missing.

---

## How to Use

1. Open the blog site's project in your AI IDE
2. Copy the entire prompt below (between the `---START PROMPT---` and `---END PROMPT---` markers)
3. Paste it into the AI IDE's chat
4. The AI will scan the codebase and produce a detailed compatibility report

---

## ---START PROMPT---

You are performing an **integration compatibility audit** for this blog site. This site needs to integrate with **Ranktraction**, an AI-powered blog management platform that remotely syncs articles, categories, and author data to this blog via REST API endpoints.

Your job is to thoroughly scan this entire codebase and produce a detailed compatibility report. Check every item below. Do NOT skip anything. Do NOT assume anything — verify by reading the actual source code.

### IMPORTANT HISTORICAL CONTEXT

Before Ranktraction, these blogs were managed via a **Bubble.io frontend**. The Bubble app used its own API Connector to push articles and categories to each blog. This means you may find:

- **`x-bubble-secret`** auth header — this is the OLD authentication from Bubble. The NEW system uses `X-API-Secret`.
- **`BUBBLE_WEBHOOK_SECRET`** env var — the OLD auth secret env var. The NEW equivalent is `SYNC_API_SECRET`.
- **`BUBBLE_APP_FILTER`** env var — used for app name validation. This name is legacy but the functionality is the same for Ranktraction.
- **`bubbleId`** DB column — the OLD external ID column storing Bubble's record `_id`. The NEW equivalent is `externalId`.
- **`/api/webhooks/publish-blog`** — the OLD article sync endpoint built for Bubble. The NEW equivalent is `/api/sync-articles`.
- **`/api/webhooks/sync-category`** and **`/api/webhooks/delete-category`** — OLD category endpoints built for Bubble. These use `x-bubble-secret` and are NOT compatible with Ranktraction which sends `X-API-Secret`.
- **`best-times-blog`** — a default fallback appName hardcoded in many repos. This was the original Bubble development app name and is almost certainly wrong for the actual site.
- **`thumbnailImage(Image)`** — Bubble's field naming convention for images. Ranktraction sends `image` instead.

**The key takeaway:** Ranktraction ALWAYS uses `X-API-Secret` header and `SYNC_API_SECRET` env var. If a blog only has `x-bubble-secret` endpoints, it needs new endpoints or the existing ones need to also accept `X-API-Secret`.

---

### STEP 1: Scan for API Route Files

Search the project for all API route files. They are typically located at:
- `app/api/**/route.ts` (Next.js App Router)
- `src/app/api/**/route.ts` (Next.js App Router with src directory)
- `pages/api/**/*.ts` (Next.js Pages Router, legacy)

List every API route file you find, with its full path and the HTTP methods it exports (GET, POST, DELETE, PUT, OPTIONS, etc.).

---

### STEP 1.5: Detect Bubble Legacy Artifacts

Before checking individual endpoints, scan the codebase for legacy Bubble.io integration artifacts. Search for these patterns:

1. **Search for `x-bubble-secret`** in all route files — this indicates legacy Bubble auth endpoints
2. **Search for `BUBBLE_WEBHOOK_SECRET`** in all files — this indicates the old auth env var is used
3. **Search for `BUBBLE_APP_FILTER`** — this is the legacy app name filter env var
4. **Search for `bubbleId`** in schema files — this is the legacy external ID column name
5. **Search for `publish-blog`** in route paths — this is the legacy Bubble article endpoint
6. **Search for `x-webhook-secret`** — this is a variant used only by the `ral` repo

**Report:**
- How many Bubble legacy endpoints exist?
- Are there ALSO modern Ranktraction-compatible endpoints (`/api/sync-articles`, `/api/sync-categories`)?
- Is the DB schema using `bubbleId` (legacy) or `externalId` (modern)?
- Does the blog accept BOTH auth headers or only one?

---

### STEP 2: Check Article Sync Endpoint

Look for an endpoint that handles article synchronization. The standard path is `/api/sync-articles` but it may also be at `/api/webhooks/publish-blog` or `/api/blog/webhook`.

**For each article sync endpoint found, verify:**

1. **Path**: What is the exact route path?
2. **HTTP Method**: Must support POST
3. **Authentication**: What header does it read?
   - ✅ Expected: `X-API-Secret` header validated against `process.env.SYNC_API_SECRET`
   - ⚠️ Alternative: `x-bubble-secret` header against `process.env.BUBBLE_WEBHOOK_SECRET`
   - ⚠️ Alternative: `x-webhook-secret` header against `process.env.BUBBLE_WEBHOOK_SECRET`
4. **CORS**: Does it handle OPTIONS preflight? Does it set `Access-Control-Allow-*` headers?
5. **appName Validation**: How does it validate the `appName` field?
   - Is it reading from `process.env.BUBBLE_APP_FILTER`?
   - Is it hardcoded as `ALLOWED_APP_NAME`? If so, what is the value?
   - Does it use strict equality (`!==`) or flexible matching (`.includes()`)?
6. **Required Fields**: What fields does the endpoint validate as required?
   - Expected minimum: `_id` (or `external_id`), `title`, `slug`
   - Does it also require `article_html`, `content`, or `html`?
   - Does it require `category`?
   - Does it require `appName`?
7. **Upsert Key**: What field does it use to check if an article already exists?
   - ✅ Expected: `externalId` or `external_id` column matched to payload `_id`
   - ⚠️ Alternative: `bubbleId` or `bubble_id` column matched to payload `_id`
8. **Content Field Mapping**: How does it map the incoming content?
   - Does it read `content`, `html`, `article_html`, or multiple?
   - What DB column does it write to? (`content`, `articleHtml`, etc.)
9. **ISR Revalidation**: Does it call `revalidatePath()` or `revalidateTag()` after upserting?
10. **Fields Stored**: Which of the following fields does it accept and store?

| Field | Payload Key | Expected DB Column |
|-------|------------|-------------------|
| External ID | `_id` | `externalId` / `bubbleId` |
| Title | `title` | `title` |
| Slug | `slug` | `slug` |
| HTML Content | `html` / `content` / `article_html` | `content` / `articleHtml` |
| Excerpt | `introduction` | `excerpt` / `snippetIntro` |
| Category | `category` | `category` |
| Hero Image | `image` / `thumbnailImage(Image)` | `heroImage` / `cardImage` |
| Author Name | `authorName` / `author_name` | `author` |
| Author Bio | `authorBio` / `author_bio` | `authorBio` |
| Author Data | `authorData` | `authorData` (JSONB) |
| Meta Title | `metaTitle` / `meta_title` | `metaTitle` |
| Meta Description | `metaDesc` / `meta_desc` | `metaDescription` |
| SEO Title | (from metadata.title) | `seoTitle` |
| Snippet Intro | (from aioBox.plainText) | `snippetIntro` |
| Reading Time | `metrics.readingTimeMinutes` | `readingTime` |
| Word Count | `metrics.totalWords` | `wordCount` |
| Publication Date | `Created Date` | `publicationDate` / `datePublished` |
| Modified Date | `Modified Date` | `updatedAt` / `dateModified` |
| Featured | `featured` | `featured` |
| Likes Count | `likesCount` | `likesCount` |
| App Name | `appName` | `appName` |
| FAQ Data | `faq` | `faqItems` / `faq` (JSONB) |
| AIO Box | `aioBox` | `aioBox` (JSONB) |
| Key Takeaways | `keyTakeaways` | `keyTakeaways` (JSONB/text) |
| Schema JSON | `schema` | `schemaJson` / `schema` (JSONB) |
| Schema FAQ | (auto-generated) | `schemaFaq` (JSONB) |
| Table of Contents | (from sections) | `tableOfContents` (JSONB) |
| Images | `images` | `images` (JSONB) |
| Videos | `videos` | `videos` (JSONB) |
| Metrics | `metrics` | `metrics` (JSONB) |
| Content Hash | `metrics.contentHash` | `contentHash` |
| Keywords | `keywords` | `keywords` |
| Metadata | `metadata` | (distributed across columns) |
| Views Count | `viewsCount` | `viewsCount` |

---

### STEP 3: Check Category Sync Endpoint

Look for endpoints that handle category create/update and delete. Standard paths:
- `/api/sync-categories` (POST for create/update, DELETE for delete)
- `/api/categories/sync` (POST) + `/api/categories/delete` (DELETE)
- `/api/webhooks/sync-category` (POST) + `/api/webhooks/delete-category` (POST)

**For each category endpoint found, verify:**

1. **Path**: What is the exact route path?
2. **HTTP Methods**: POST for create/update, DELETE or POST for delete
3. **Authentication**: Same check as articles — what header and env var?
4. **appName Validation**: Same check — is it validated? What value?
5. **Required Fields for Create/Update**:
   - `external_id` (string) — the upsert key
   - `japanese` (string) — category name
   - `english` (string) — English name (may be optional)
   - `slug` (string) — URL-safe slug
   - `appName` (string) — app filter
6. **Required Fields for Delete**:
   - `external_id` (string) — identifies which category to delete
   - How is it passed? Query param (`?external_id=X`) or JSON body (`{ external_id: "X" }`)?
7. **Upsert Key**: Must match on `externalId` / `external_id` column
8. **Does delete check for articles referencing the category before deleting?**

---

### STEP 4: Check for Author Sync Endpoint

Look for any endpoint handling author synchronization:
- `/api/sync-authors`
- `/api/authors/sync`
- `/api/webhooks/sync-author`

**Note:** Currently NO Ranktraction-integrated blog has author sync endpoints. Author data is embedded in the article payload. If this endpoint does NOT exist, that is expected and OK.

---

### STEP 5: Check Database Schema

Find the database schema file. It's typically at:
- `shared/schema.ts`
- `src/lib/db/schema.ts`
- `lib/db/schema.ts`
- `drizzle/schema.ts`

**Verify the articles/blog_posts table has these columns:**

| Column | Required | Type | Notes |
|--------|:--------:|------|-------|
| `id` | ✅ | serial | Primary key |
| `externalId` or `bubbleId` | ✅ | varchar(255), UNIQUE | Upsert key — maps from `_id` in payload |
| `title` | ✅ | text or varchar | |
| `slug` | ✅ | varchar(255), UNIQUE | |
| `content` or `articleHtml` | ✅ | text | Stores HTML content |
| `excerpt` or `snippetIntro` | | text | |
| `category` | | varchar | |
| `heroImage` | | varchar or jsonb | |
| `author` | | varchar or jsonb | |
| `authorBio` | | text | |
| `authorData` | | jsonb | Full author object |
| `metaTitle` | | varchar | |
| `metaDescription` | | text | |
| `readingTime` | | integer | |
| `wordCount` | | integer | |
| `featured` | | boolean | |
| `faqItems` or `faq` | | jsonb | |
| `aioBox` | | jsonb | |
| `keyTakeaways` | | text or jsonb | |
| `schemaJson` or `schema` | | jsonb | |
| `images` | | jsonb | |
| `videos` | | jsonb | |
| `createdAt` | | timestamp | |
| `updatedAt` | | timestamp | |

**Verify the categories table has these columns:**

| Column | Required | Type | Notes |
|--------|:--------:|------|-------|
| `id` | ✅ | serial | Primary key |
| `externalId` | ✅ | varchar(255), UNIQUE | Upsert key |
| `japanese` | ✅ | varchar(255) | Primary name |
| `english` | | varchar(255) | |
| `slug` | ✅ | varchar(255), UNIQUE | |
| `description` | | text | |
| `createdAt` | | timestamp | |
| `updatedAt` | | timestamp | |

---

### STEP 6: Check Environment Variables

Look for `.env`, `.env.local`, `.env.example`, or any env configuration files.

**Required variables:**

| Variable | Purpose | Found? |
|----------|---------|--------|
| `DATABASE_URL` | Neon PostgreSQL connection | |
| `SYNC_API_SECRET` | Auth secret for sync endpoints | |

**Optional variables:**

| Variable | Purpose | Found? | Value/Default |
|----------|---------|--------|---------------|
| `BUBBLE_WEBHOOK_SECRET` | Auth for webhook endpoints | | |
| `BUBBLE_APP_FILTER` | App name filter | | |

---

### STEP 7: Check Drizzle Configuration

Look for `drizzle.config.ts` or `drizzle.config.js`. Verify:
1. It connects to Neon PostgreSQL (using `@neondatabase/serverless`)
2. The schema path matches where the schema file actually is
3. Migrations are configured

---

### STEP 8: Determine API Paradigm

Based on what you found, classify this blog into one of these paradigms:

**Paradigm A — Webhook Pattern (Legacy Bubble):**
- Has `/api/webhooks/publish-blog` (OLD article sync for Bubble)
- Has `/api/webhooks/sync-category` (OLD category create/update for Bubble)
- Has `/api/webhooks/delete-category` (OLD category delete for Bubble)
- Uses `x-bubble-secret` header on webhook routes (Bubble auth)
- Also has `/api/sync-articles` with `X-API-Secret` (added later for Ranktraction)
- **Note:** Category sync is NOT migrated — only works via old Bubble endpoints

**Paradigm B — Standard Pattern (Ranktraction-Native):**
- Has `/api/sync-articles` (article sync)
- Has `/api/sync-categories` (category sync via POST + DELETE)
- Uses `X-API-Secret` header everywhere
- Category delete via `DELETE ?external_id=X` query param
- **Fully compatible with Ranktraction**

**Paradigm C — Categories Module Pattern (Ranktraction-Native, Alt Routing):**
- Has `/api/sync-articles` (article sync)
- Has `/api/categories/sync` (category create/update via POST)
- Has `/api/categories/delete` (category delete via DELETE with JSON body `{ external_id, appName }`)
- Uses `X-API-Secret` header everywhere
- Hardcoded `ALLOWED_APP_NAME` constant
- **Note:** Ranktraction's sync-standalone.ts does NOT currently route to this pattern

**Paradigm D — Outlier:**
- Does not fit any pattern above
- Document what exists and what's different

---

### STEP 9: Generate Compatibility Report

Produce a report in this exact format:

```
# Integration Compatibility Report

## Site: [Site Name]
## Paradigm: [A/B/C/D]
## Date: [Current Date]

### Article Sync
- Endpoint: [path] — [✅ FOUND / ❌ MISSING]
- Auth Header: [header name] — [✅ X-API-Secret / ⚠️ x-bubble-secret (legacy) / ❌ MISSING]
- Auth Env Var: [var name] — [✅ SYNC_API_SECRET / ⚠️ BUBBLE_WEBHOOK_SECRET (legacy) / ❌ MISSING]
- CORS Handling: [✅ / ❌]
- appName Validation: [method] — Value: [value]
- Upsert Key: [column name] — [✅ externalId / ⚠️ bubbleId (legacy) / ❌ MISSING]
- Content Field: [field name] — Maps to DB column: [column]
- ISR Revalidation: [✅ / ❌]
- Required Fields: [list]
- Optional Fields Stored: [count]/[total possible]

### Category Sync (Create/Update)
- Endpoint: [path] — [✅ FOUND / ❌ MISSING]
- Auth Header: [header name] — [✅ / ⚠️ / ❌]
- Required Fields: [list]
- Upsert Key: [column name]

### Category Delete
- Endpoint: [path] — [✅ FOUND / ❌ MISSING]
- Method: [POST / DELETE]
- Data Passing: [query param / JSON body]
- Auth: [same as above]

### Author Sync
- Endpoint: [✅ FOUND / ❌ MISSING (expected — author data embedded in articles)]

### Database Schema
- Articles Table: [table name] — [✅ FOUND / ❌ MISSING]
  - externalId column: [✅ / ❌]
  - content/articleHtml column: [✅ / ❌]
  - authorData JSONB column: [✅ / ❌]
  - faqItems JSONB column: [✅ / ❌]
  - aioBox JSONB column: [✅ / ❌]
  - images JSONB column: [✅ / ❌]
  - videos JSONB column: [✅ / ❌]
  - schemaJson JSONB column: [✅ / ❌]
- Categories Table: [table name] — [✅ FOUND / ❌ MISSING]
  - externalId column: [✅ / ❌]
  - japanese column: [✅ / ❌]
  - slug column: [✅ / ❌]

### Environment Variables
- DATABASE_URL: [✅ / ❌]
- SYNC_API_SECRET: [✅ / ❌]
- BUBBLE_APP_FILTER: [✅ / ❌ / N/A]

### Summary
- Article Sync Ready: [✅ YES / ❌ NO — reason]
- Category Sync Ready: [✅ YES / ❌ NO — reason]
- Author Sync: N/A (embedded in articles)
- Overall Integration Status: [READY / PARTIAL / NOT READY]

### Missing Items (Action Required)
[For each missing item, list:]
1. [What is missing]
   - Why it's needed
   - Whether this is a Bubble legacy gap or a new requirement
   - Recommended fix (with code snippet if applicable)

### Bubble Legacy Status
- Legacy Bubble endpoints found: [count] — [list paths]
- Legacy auth header (x-bubble-secret) used: [YES / NO]
- Legacy DB column (bubbleId) used: [YES / NO]
- BUBBLE_APP_FILTER env var found: [YES / NO] — Default fallback: [value]
- Migration recommendation: [NONE NEEDED / ADD sync-categories / ADD sync-articles / FULL MIGRATION]
```

---

### STEP 10: If Items Are Missing — Provide Fix Templates

For any missing endpoints or schema columns, provide the complete implementation code. Use these templates:

#### Missing `/api/sync-articles/route.ts`

```typescript
import { NextRequest, NextResponse } from 'next/server';
import { db } from '@/lib/db';
import { blogPosts } from '@/shared/schema'; // adjust import path
import { eq } from 'drizzle-orm';

const corsHeaders = {
  'Access-Control-Allow-Origin': '*',
  'Access-Control-Allow-Methods': 'GET, POST, OPTIONS',
  'Access-Control-Allow-Headers': 'Content-Type, Authorization, X-API-Secret',
};

export async function OPTIONS() {
  return new Response(null, { status: 204, headers: corsHeaders });
}

export async function POST(request: NextRequest) {
  try {
    // 1. Authentication
    const apiSecret = request.headers.get('X-API-Secret');
    if (!apiSecret || apiSecret !== process.env.SYNC_API_SECRET) {
      return NextResponse.json(
        { success: false, error: 'Unauthorized' },
        { status: 401, headers: corsHeaders }
      );
    }

    // 2. Parse body
    const body = await request.json();

    // 3. Validate appName
    const expectedApp = process.env.BUBBLE_APP_FILTER || 'YOUR_APP_NAME';
    if (body.appName !== expectedApp) {
      return NextResponse.json(
        { success: false, error: 'Invalid app name' },
        { status: 400, headers: corsHeaders }
      );
    }

    // 4. Validate required fields
    if (!body._id || !body.title || !body.slug) {
      return NextResponse.json(
        { success: false, error: 'Missing required fields: _id, title, slug' },
        { status: 400, headers: corsHeaders }
      );
    }

    if (!body.content && !body.html && !body.article_html) {
      return NextResponse.json(
        { success: false, error: 'Missing content field' },
        { status: 400, headers: corsHeaders }
      );
    }

    // 5. Build article data
    const articleContent = body.html || body.content || body.article_html || '';
    const imageUrl = body.image || body['thumbnailImage(Image)'] || null;
    let heroImage = imageUrl;
    if (heroImage?.startsWith('//')) heroImage = `https:${heroImage}`;

    const articleData = {
      externalId: body._id,
      title: body.title,
      slug: body.slug,
      content: articleContent,
      excerpt: body.introduction || body.metadata?.excerpt || null,
      category: body.category || null,
      heroImage,
      cardImage: heroImage,
      author: body.authorName || body.author || body.metadata?.author?.name || 'Editorial Team',
      authorBio: body.authorBio || body.metadata?.author?.bio || null,
      authorData: body.authorData || null,
      metaTitle: body.metaTitle || body.metadata?.title || null,
      metaDescription: body.metaDesc || body.metadata?.metaDescription || null,
      seoTitle: body.metadata?.title?.substring(0, 100) || null,
      snippetIntro: body.aioBox?.plainText || null,
      readingTime: body.metrics?.readingTimeMinutes || body.metadata?.readingTime || null,
      wordCount: body.metrics?.totalWords || null,
      viewsCount: body.viewsCount || 0,
      likesCount: body.likesCount || 0,
      featured: body.featured || false,
      isActive: true,
      publicationDate: body['Created Date'] ? new Date(body['Created Date']) : new Date(),
      updatedAt: new Date(),
      schemaJson: body.schema || null,
      schemaFaq: body.faq?.length ? {
        "@context": "https://schema.org",
        "@type": "FAQPage",
        "mainEntity": body.faq.map((item: any) => ({
          "@type": "Question",
          "name": item.question,
          "acceptedAnswer": { "@type": "Answer", "text": item.answer }
        }))
      } : null,
      faqItems: body.faq || null,
      keyTakeaways: body.keyTakeaways ? JSON.stringify(body.keyTakeaways) : null,
      aioBox: body.aioBox || null,
      images: body.images || null,
      videos: body.videos || null,
      contentHash: body.metrics?.contentHash || null,
      keywords: body.keywords || null,
    };

    // 6. Upsert
    const existing = await db
      .select({ id: blogPosts.id })
      .from(blogPosts)
      .where(eq(blogPosts.externalId, body._id))
      .limit(1);

    let result;
    if (existing.length > 0) {
      result = await db.update(blogPosts)
        .set(articleData)
        .where(eq(blogPosts.externalId, body._id))
        .returning({ id: blogPosts.id, slug: blogPosts.slug });
    } else {
      result = await db.insert(blogPosts)
        .values({ ...articleData, createdAt: new Date() })
        .returning({ id: blogPosts.id, slug: blogPosts.slug });
    }

    // 7. Revalidate
    try {
      const { revalidatePath } = await import('next/cache');
      revalidatePath('/', 'page');
      revalidatePath('/blogs', 'page');
      revalidatePath(`/post/${result[0].slug}`, 'page');
      if (body.category) revalidatePath(`/category/${body.category}`, 'page');
    } catch (e) { /* non-critical */ }

    return NextResponse.json({
      success: true,
      action: existing.length > 0 ? 'updated' : 'created',
      article: result[0],
    }, { headers: corsHeaders });

  } catch (error) {
    console.error('Sync API Error:', error);
    return NextResponse.json(
      { success: false, error: 'Internal server error', details: error instanceof Error ? error.message : 'Unknown' },
      { status: 500, headers: corsHeaders }
    );
  }
}
```

#### Missing `/api/sync-categories/route.ts`

```typescript
import { NextRequest, NextResponse } from 'next/server';
import { db } from '@/lib/db';
import { categories } from '@/shared/schema'; // adjust import path
import { eq } from 'drizzle-orm';

const corsHeaders = {
  'Access-Control-Allow-Origin': '*',
  'Access-Control-Allow-Methods': 'GET, POST, DELETE, OPTIONS',
  'Access-Control-Allow-Headers': 'Content-Type, Authorization, X-API-Secret',
};

export async function OPTIONS() {
  return new Response(null, { status: 204, headers: corsHeaders });
}

// POST: Create or update category
export async function POST(request: NextRequest) {
  try {
    const apiSecret = request.headers.get('X-API-Secret');
    if (!apiSecret || apiSecret !== process.env.SYNC_API_SECRET) {
      return NextResponse.json(
        { success: false, error: 'Unauthorized' },
        { status: 401, headers: corsHeaders }
      );
    }

    const body = await request.json();

    // Support both external_id and _id
    const externalId = body.external_id || body._id;
    if (!externalId) {
      return NextResponse.json(
        { success: false, error: 'Missing required field: external_id' },
        { status: 400, headers: corsHeaders }
      );
    }

    if (!body.japanese || !body.slug) {
      return NextResponse.json(
        { success: false, error: 'Missing required fields: japanese, slug' },
        { status: 400, headers: corsHeaders }
      );
    }

    const categoryData = {
      externalId,
      japanese: body.japanese,
      english: body.english || null,
      slug: body.slug,
      description: body.description || null,
      updatedAt: new Date(),
    };

    const existing = await db
      .select({ id: categories.id })
      .from(categories)
      .where(eq(categories.externalId, externalId))
      .limit(1);

    let result;
    if (existing.length > 0) {
      result = await db.update(categories)
        .set(categoryData)
        .where(eq(categories.externalId, externalId))
        .returning({ id: categories.id, slug: categories.slug });
    } else {
      result = await db.insert(categories)
        .values({ ...categoryData, createdAt: new Date() })
        .returning({ id: categories.id, slug: categories.slug });
    }

    return NextResponse.json({
      success: true,
      action: existing.length > 0 ? 'updated' : 'created',
      category: result[0],
    }, { headers: corsHeaders });

  } catch (error) {
    console.error('Category Sync Error:', error);
    return NextResponse.json(
      { success: false, error: 'Internal server error' },
      { status: 500, headers: corsHeaders }
    );
  }
}

// DELETE: Delete category
export async function DELETE(request: NextRequest) {
  try {
    const apiSecret = request.headers.get('X-API-Secret');
    if (!apiSecret || apiSecret !== process.env.SYNC_API_SECRET) {
      return NextResponse.json(
        { success: false, error: 'Unauthorized' },
        { status: 401, headers: corsHeaders }
      );
    }

    const { searchParams } = new URL(request.url);
    const externalId = searchParams.get('external_id');

    if (!externalId) {
      return NextResponse.json(
        { success: false, error: 'Missing external_id parameter' },
        { status: 400, headers: corsHeaders }
      );
    }

    const existing = await db
      .select({ id: categories.id })
      .from(categories)
      .where(eq(categories.externalId, externalId))
      .limit(1);

    if (existing.length === 0) {
      return NextResponse.json(
        { success: false, error: 'Category not found' },
        { status: 404, headers: corsHeaders }
      );
    }

    await db.delete(categories)
      .where(eq(categories.externalId, externalId));

    return NextResponse.json({
      success: true,
      message: 'Category deleted successfully',
    }, { headers: corsHeaders });

  } catch (error) {
    console.error('Category Delete Error:', error);
    return NextResponse.json(
      { success: false, error: 'Internal server error' },
      { status: 500, headers: corsHeaders }
    );
  }
}
```

#### Missing Database Schema Columns

If the articles/blog_posts table is missing columns, add them using this Drizzle schema pattern:

```typescript
import { pgTable, serial, varchar, text, boolean, integer, timestamp, jsonb } from 'drizzle-orm/pg-core';

export const blogPosts = pgTable('blog_posts', {
  id: serial('id').primaryKey(),
  externalId: varchar('external_id', { length: 255 }).unique(),
  title: text('title').notNull(),
  slug: varchar('slug', { length: 255 }).notNull().unique(),
  content: text('content').notNull(),
  excerpt: text('excerpt'),
  category: varchar('category', { length: 100 }),
  heroImage: varchar('hero_image', { length: 500 }),
  cardImage: varchar('card_image', { length: 500 }),
  author: varchar('author', { length: 255 }),
  authorBio: text('author_bio'),
  authorData: jsonb('author_data'),
  metaTitle: varchar('meta_title', { length: 255 }),
  metaDescription: text('meta_description'),
  seoTitle: varchar('seo_title', { length: 100 }),
  snippetIntro: text('snippet_intro'),
  readingTime: integer('reading_time'),
  wordCount: integer('word_count'),
  viewsCount: integer('views_count').notNull().default(0),
  likesCount: integer('likes_count').notNull().default(0),
  featured: boolean('featured').notNull().default(false),
  isActive: boolean('is_active').notNull().default(true),
  publicationDate: timestamp('publication_date'),
  createdAt: timestamp('created_at').defaultNow().notNull(),
  updatedAt: timestamp('updated_at').defaultNow().notNull(),
  schemaJson: jsonb('schema_json'),
  schemaFaq: jsonb('schema_faq'),
  tableOfContents: jsonb('table_of_contents'),
  faqItems: jsonb('faq_items'),
  keyTakeaways: text('key_takeaways'),
  aioBox: jsonb('aio_box'),
  heroImageData: jsonb('hero_image_data'),
  images: jsonb('images'),
  videos: jsonb('videos'),
  contentHash: varchar('content_hash', { length: 64 }),
  processingTime: integer('processing_time'),
  language: varchar('language', { length: 10 }).default('ja'),
  keywords: text('keywords'),
});

export const categories = pgTable('categories', {
  id: serial('id').primaryKey(),
  externalId: varchar('external_id', { length: 255 }).notNull().unique(),
  japanese: varchar('japanese', { length: 255 }).notNull(),
  english: varchar('english', { length: 255 }),
  slug: varchar('slug', { length: 255 }).notNull().unique(),
  description: text('description'),
  createdAt: timestamp('created_at').defaultNow().notNull(),
  updatedAt: timestamp('updated_at').defaultNow().notNull(),
});
```

After adding missing columns, generate and run a migration:
```bash
npx drizzle-kit generate
npx drizzle-kit push
```

---

### CRITICAL REMINDERS

1. **Do NOT remove any existing endpoints or columns** — only add what's missing. Bubble legacy endpoints may still be used by other systems.
2. **The `externalId` column MUST be UNIQUE** — it's the upsert key. If the blog uses `bubbleId` instead, it serves the same purpose.
3. **The `X-API-Secret` header is case-sensitive** — always use exactly `X-API-Secret`. Do NOT confuse with `x-bubble-secret` (legacy).
4. **The `SYNC_API_SECRET` env var must be set on Vercel** for production. If only `BUBBLE_WEBHOOK_SECRET` exists, also add `SYNC_API_SECRET`.
5. **After adding/modifying schema, run `drizzle-kit push`** to apply changes to the database
6. **Use `revalidatePath()` after mutations** to ensure Next.js serves fresh content
7. **appName value in Ranktraction must match** what the blog expects — coordinate with the team
8. **Bubble naming is legacy, not a bug** — if you see `bubble`, `BUBBLE_*`, `bubbleId`, etc., these are from the old Bubble.io frontend. The functionality is the same; only the naming differs.
9. **For new endpoints, ALWAYS use the Ranktraction standard**: `X-API-Secret` header, `SYNC_API_SECRET` env var, `externalId` column, `/api/sync-articles` and `/api/sync-categories` routes.
10. **Do NOT use `BUBBLE_WEBHOOK_SECRET` or `x-bubble-secret` for new code** — those are legacy.

## ---END PROMPT---
