<!--
Publishing Pipeline Playbook

Part of the free Claude Code template library from BILT AI — https://biltai.io/claude-templates

These are the actual playbooks used to build and ship production SaaS apps with
Claude Code. They're written to be handed to an agent as-is: point Claude at one
and it has the full pattern, the schema, the gotchas, and the "don't do this"
list. Paths, project refs, and org names have been replaced with placeholders
(<your-app>, <supabase-project-ref>, <your-github-org>) — search for "<" and
fill in your own before running.

Free to use, modify, and ship. No attribution required.
-->

# Publishing Pipeline Playbook

**Portable spec for any app that pushes user-authored content to third-party publish APIs (Twitter, LinkedIn, Facebook, IG, Threads, Bluesky, Pinterest, TikTok, YouTube, GHL Social Planner, GHL Blog, WordPress, Mailchimp, Slack, Discord, generic webhooks, Bundle.social, Blotato, etc.).**

Built from the BKE / Kompozy implementation. Reference repo: `reference app`.

---

## 0. Use this spec

**Inputs:**
- List of target platforms + which publisher owns each (direct OAuth? Blotato? GHL? Bundle.social? custom webhook?)
- Per-platform text limits (from each platform's public docs — never guess)
- Whether media (image/video) is required, optional, or unsupported per platform
- Supabase project credentials + Trigger.dev (or any worker queue) credentials
- Optional: HMAC secret for outbound webhook signing

**Outputs:**
- `scheduled_posts` table with state machine + partial unique index
- `publish_locks` table + advisory-lock RPCs
- `scheduler-tick` cron worker that dispatches due rows
- `publish-post` worker that calls providers
- `scheduled-posts-reconciler` cron worker with terminal-history gate
- Provider-call guard helper, platform-limits helper, wire-platform helper
- Per-platform media preflight + truncation
- HMAC-signed webhook publisher with SSRF guards

---

## 1. Architecture

```
src/
├── trigger/                                # Workers, never browser-bound
│   ├── publish-post.ts                     # The publisher — one row per call
│   ├── scheduler-tick.ts                   # Cron, every minute: dispatches due rows
│   └── scheduled-posts-reconciler.ts       # Cron, every minute: backfills + heals
│
├── lib/publish/
│   ├── wire-platform.ts                    # toBlotatoPlatformName / toGhlPlatformName / toDbPlatformName / isXSupported
│   ├── platform-limits.ts                  # PLATFORM_TEXT_LIMITS + truncateForPlatform()
│   ├── provider-call-guard.ts              # acquirePublishGuard() + release()
│   ├── resolve-target.ts                   # picks publisher per (workspace, platform)
│   ├── sync-scheduled-posts.ts             # browser-side approve → insert rows (with media preflight)
│   ├── sync-generated-content-status.ts    # aggregates per-platform statuses to parent gc row
│   ├── blotato-stage.ts                    # provider-staging cache + lock-aware retry
│   ├── dedup.ts                            # per-gc staging cache helpers
│   └── blog/
│       ├── publish-ghl-blog.ts
│       ├── publish-wordpress.ts
│       └── publish-webhook.ts              # HMAC + SSRF guards
│
└── app/api/publish/route.ts                # Manual "Post Now" — same guards as worker
```

---

## 2. The Universal-Worker rule

**Publishing is a worker, not a browser-bound fetch.** Tab close, network blip, route nav, browser sleep — none of these can lose a scheduled publish. Every publish path on every platform routes through a queue worker (Trigger.dev v4 in BKE; any equivalent — Inngest, BullMQ, AWS SQS — works).

The browser may dispatch but never await. Manual "Post Now" inserts a `scheduled_posts` row with `publish_at = now()` and lets the same worker pick it up — the manual path and scheduled path share one publish surface so they cannot drift apart.

Reference: `src/trigger/publish-post.ts`.

---

## 3. Schema

```sql
create table scheduled_posts (
  id uuid primary key default gen_random_uuid(),
  generated_content_id uuid not null references generated_content(id) on delete cascade,
  workspace_id uuid not null references workspaces(id) on delete cascade,
  user_id uuid not null references profiles(id) on delete cascade,

  platform text not null check (platform in (
    'instagram','facebook','tiktok','youtube','linkedin',
    'pinterest','threads','bluesky','twitter','rumble',
    'mailchimp','medium','ghl_blog','wordpress','webhook'
  )),

  status text not null default 'queued' check (status in (
    'queued', 'submitted', 'published', 'failed', 'cancelled'
  )),

  publish_at timestamptz not null,
  published_at timestamptz,
  last_error text,

  -- Provider-side ids — at most one is populated per row
  blotato_submission_id text,
  ghl_post_id text,
  bundle_post_id text,

  created_at timestamptz default now(),
  updated_at timestamptz default now()
);

-- The dedupe gate — prevents two ACTIVE rows for the same (gc, platform).
-- Partial index because a previously-published row should NOT block a future
-- re-publish if the user explicitly re-queues; the reconciler enforces
-- terminal-history separately (§7).
create unique index ux_scheduled_posts_active_gc_platform
  on scheduled_posts (generated_content_id, platform)
  where status in ('queued', 'submitted');

-- Cross-instance lock table — backs provider-call-guard.
create table publish_locks (
  gc_id uuid not null,
  platform text not null,
  acquired_at timestamptz not null default now(),
  acquired_by text,
  primary key (gc_id, platform)
);
```

**State machine:** `queued → submitted → published | failed | cancelled`. Reverting `submitted → queued` is legal ONLY on stage-failure (no provider call made) — see §8.

Reference migrations: `00040_scheduled_posts_dedup.sql`, `00062_scheduled_posts_publish_guard.sql`, `00063_publish_advisory_locks.sql`.

---

## 4. Cross-instance advisory lock (provider-call-guard)

Multiple things can race a publish: scheduler-tick firing twice; manual Post Now overlapping with a scheduled tick; two Lambda instances picking up the same row before status flips; a browser tab retrying a failed publish while a worker is mid-flight. The dedupe index protects active rows but cannot protect the **provider call itself**.

`acquirePublishGuard(supabase, { gcId, platform, acquiredBy, expectedUserId? })` does two things, in order, under a Postgres-backed lock:

1. **Acquire** a row in `publish_locks` keyed on `(gc_id, platform)` via `try_publish_lock` RPC. Insert-on-conflict-do-nothing returns `true` on first acquire, `false` if another caller holds it. Auto-stales at 5 min (longer than any provider call's `maxDuration`).
2. **Sibling-published check** — inside the lock, query `scheduled_posts` for any `status='published'` row with the same `(gc_id, platform)`. If one exists, refuse: the post already shipped; refusing prevents the audience-visible duplicate.

Caller pattern:

```ts
const guard = await acquirePublishGuard(supabase, {
    gcId: sp.generated_content_id,
    platform: sp.platform,
    acquiredBy: `publish-post:${sp.id}`,
    expectedUserId: null, // worker; browsers pass session userId for cross-user gcId defense
});
if (!guard.allowed) {
    await markFailed(sp.id, guard.reason);
    return;
}
try {
    // provider call
} finally {
    await guard.release();
}
```

Hard rules:
- Every direct provider call (Blotato, GHL, WordPress, Mailchimp, webhook, Bundle) MUST be wrapped.
- `release()` never throws and is safe in a `finally` block.
- `acquiredBy` is stored and rechecked on release — an attacker cannot release another caller's lock by guessing the key.
- Browser-invoked routes MUST pass `expectedUserId` so an authenticated user A can't grief user B's locks by passing B's `gcId`.

Reference: `src/lib/publish/provider-call-guard.ts`.

---

## 5. Platform-name normalization (wire-platform.ts)

Five conventions interleave:
- `scheduled_posts.platform` stores lowercase canonical (`'instagram'`, `'twitter'`).
- Blotato's spec expects title-case wire names (`'Instagram'`, `'Twitter'`).
- GHL is case-tolerant on input but its dashboard echoes title-case.
- Bundle.social uses `UPPERCASE_SNAKE` (`'INSTAGRAM'`).
- Settings UI displays human strings, `'X'` aliasing `'Twitter'`.

Single source of truth at `src/lib/publish/wire-platform.ts`:

```ts
toBlotatoPlatformName(name)   // → 'Instagram' | null
toGhlPlatformName(name)        // → 'Instagram' | null
toBundlePlatformName(name)     // → 'INSTAGRAM' | null
toDbPlatformName(name)         // → 'instagram' | null
isBlotatoSupported(name)       // boolean
isGhlSupported(name)           // boolean
fromBundlePlatformName(wire)   // → 'instagram' | null  (webhook ingest)
```

**Hard rule:** NEVER compare against a title-case literal (`if (name === 'Instagram')`). Route every comparison through these helpers. The 2026-04-27 BKE outage was a lowercase row failing a case-sensitive map lookup in `/api/publish` — fixed by adding helpers AND making the route case-tolerant (defense in depth).

---

## 6. Per-platform text truncation (platform-limits.ts)

Each platform's public publish API has a hard character cap. Shipping unmodified text means any platform whose limit your content exceeds will 422 silently.

Single source of truth at `src/lib/publish/platform-limits.ts`:

```ts
PLATFORM_TEXT_LIMITS = {
    threads: 500, twitter: 280, x: 280, bluesky: 300, pinterest: 500,
    instagram: 2200, facebook: 63206, linkedin: 3000, tiktok: 2200, youtube: 5000,
}

truncateForPlatform(text, platformName) → { text, truncated, originalLength, limit }
```

`truncateForPlatform`:
- Walks back to the last word boundary inside `(limit - ellipsis.length)`.
- Skips the boundary walk if it would cut more than 50% of the budget (long single word).
- Uses ASCII `'...'` for Twitter/Bluesky/Pinterest/TikTok (Unicode ellipsis renders inconsistently); `'…'` elsewhere.
- Returns the original unchanged for unknown platforms — better to let the API return its native error than to silently mangle.

Every publish path (worker AND manual route) MUST pass `text` through this helper before shipping. Manual "Post Now" with multiple destinations truncates to the MIN limit across destinations.

---

## 7. Reconciler with terminal-history gate

The reconciler runs every minute. It's the engine that maps the user's `generated_content` rows (parent) to `scheduled_posts` rows (per-platform children), and heals drift.

**The bug it must avoid (BKE lab note 2026-05-01 — carousel triple-publish):** if the reconciler fetches `scheduled_posts` filtered to `status IN ('queued','submitted')` only, it misses the window between "first platform published" and "all platforms terminal + parent gc.status flipped". In that window, the parent looks `approved+scheduled` with no ACTIVE sp row for the just-published platform — the reconciler INSERTs a new row, scheduler-tick fires it, and the audience sees the same post twice.

**The fix:** reconciler fetches ALL sp rows (every status). Terminal rows (`published`, `failed`, `cancelled`) populate a `terminalByKey` set keyed on `(gc_id, platform)`. The insert path refuses to create a new row for any `(gc, platform)` that already has a terminal entry, EVEN IF the parent gc is still `approved` and the active partition is empty for that key.

Reference: `src/trigger/scheduled-posts-reconciler.ts`.

---

## 8. Stage-failure vs post-call ambiguity (the retry-revert)

`publish-post` flips `status='queued' → 'submitted'` BEFORE making the provider call so a parallel tick doesn't double-fan. But what happens when the provider call never actually runs — e.g. the media-staging upload to Blotato collided with a per-URL lock and bounced, the network died at DNS resolution, the request body failed schema validation? The row sits at `submitted` with no provider id.

**The footgun (BKE lab note 2026-05-07):** if the retry-guard only inspects `status='submitted' AND provider_id IS NULL` and refuses to retry, every stage-failure becomes a permanent failure. Conversely, if the guard lets every `submitted+no-id` row retry, you double-post on the cases where the provider DID receive the call but the response was lost.

**The solution: a `stage` discriminant on the failure response.** When `/api/publish` (or the worker's direct provider call) fails before reaching the provider, it returns `{ ok: false, stage: 'media-staging', error: ... }`. The worker catches `stage === 'media-staging'` and reverts `status` from `'submitted'` back to `'queued'` (gated on `eq('status','submitted')` for safety) before re-throwing for Trigger.dev's task-level retry.

Pattern:

```ts
if (!res.ok) {
    const stageFlag = body?.stage;
    if (stageFlag === 'media-staging') {
        // No provider call happened — safe to revert + retry
        await supabase.from('scheduled_posts')
            .update({ status: 'queued', last_error: errMsg })
            .eq('id', sp.id)
            .eq('status', 'submitted');
        throw new Error(`Staging failed (will retry): ${errMsg}`);
    }
    // Provider was called or ambiguous — terminal failure
    await supabase.from('scheduled_posts')
        .update({ status: 'failed', last_error: errMsg })
        .eq('id', sp.id);
    throw new Error(`Publish failed: ${errMsg}`);
}
```

Same pattern for transient media-resign failures (5xx from Supabase Storage), transient Bundle.social uploads (5xx/429), and transient Blotato `/v2/media` 500s. Distinguish transient from terminal at the source and revert only the transient class.

---

## 9. Pre-flight media check

When `output_type ∈ {image, video}` and `platform ∈ PLATFORMS_REQUIRING_MEDIA_GUARD`, the worker MUST refuse to call the provider with `mediaUrls: []`.

```ts
const PLATFORMS_REQUIRING_MEDIA_GUARD = new Set([
    'instagram', 'threads', 'facebook', 'linkedin',
    'twitter', 'tiktok', 'youtube', 'pinterest', 'bluesky',
]);

if (isMediaBearingFormat && platformGuards && media.urls.length === 0) {
    await markFailed(sp.id, `Media missing for ${sp.platform} — re-attach image/video and retry.`);
    return;
}
```

**Why this matters:** IG and Threads loud-fail with 422 when media is missing. FB, LinkedIn, Twitter, TikTok, YouTube, Pinterest, Bluesky silently downgrade to text-only — the user sees "Failed" on the loud platforms and never realizes the silent ones shipped naked. Refusing here gives a unified, actionable last_error.

Mirror the same gate at the `scheduled_posts` INSERT site in `sync-scheduled-posts.ts` so the row never enters the queue if the parent `generated_content.media_storage_paths` is still empty (BKE lab note 2026-04-27 — half-published fanout).

---

## 10. Media URL handling — never store provider URLs

DALL-E, HeyGen, Kie, Replicate, ElevenLabs, every generative provider's response URL expires (often in ~1h). Storing the provider URL directly in any DB row means posts scheduled more than 1h out ship blank.

**Hard rule:** every render-* worker calls `tryPersistMediaToStorage` IMMEDIATELY after the provider returns bytes, uploads to a private Supabase Storage bucket (e.g. `generated-media`), and writes the **storage path** to `generated_content.media_storage_paths[]`. The path is the source of truth.

At publish time, the worker re-signs each path to a fresh URL (24h TTL for social, 10y for blog hero images that hot-link). If the re-sign call fails on a non-empty path set, throw a sentinel error (BKE uses `MediaResolveError`) and revert the row to `queued` — the legit "no media on the row" case (empty path set) flows through to the preflight in §9.

Reference: `src/lib/media-persist.ts` and the `resolveMediaUrls` helper in `src/trigger/publish-post.ts`.

---

## 11. Per-gc staging cache (dedupe.ts)

When a single `generated_content` row fans out to 5 platforms, the worker fires 5 parallel `publish-post` tasks. Each one independently has to stage the same media to Blotato's `/v2/media` (or analog). Without coordination, all 5 hit Blotato's per-URL lock and 4 of them fail.

The cache lives on `gc.metadata.blotato_staged_paths`: a map from `storage_path → provider_staged_url`. First worker to win the per-URL race writes the result. Sibling workers in the same fanout read the cache before staging — saves the redundant API call.

When a sibling DOES hit the per-URL lock (cache not yet populated by the winner), the cache layer sleeps 35s and re-reads the cache before making a final attempt — short-circuiting out of the per-call retry loop, which would otherwise burn its 3 attempts inside the 30s lock window.

Reference: `src/lib/publish/blotato-stage.ts` + `src/lib/publish/dedup.ts`.

---

## 12. Generic webhook publisher (HMAC + SSRF)

For customer-defined endpoints (Zapier, Make, n8n, custom Slack/Discord receivers), the publisher signs every outbound POST with HMAC-SHA256:

```
X-Signature-256: sha256=<hex>
X-Timestamp: <unix-seconds>
```

Sign over `timestamp + '.' + bodyJson` so a replay needs both. Receivers verify against the shared secret from `platform_connections`.

**SSRF guards** (apply BEFORE the fetch):
- Reject non-https URLs.
- Reject hostnames that resolve to RFC1918 (10/8, 172.16/12, 192.168/16), loopback (127/8, ::1), link-local (169.254/16), or `0.0.0.0`.
- Reject `metadata.google.internal`, `169.254.169.254` (AWS IMDS), and the equivalent metadata endpoints.
- Cap response size at ~1MB; cap timeout at 30s.
- Follow at most 2 redirects, re-validating SSRF on each hop.

Reference: `src/lib/publish/blog/publish-webhook.ts`.

---

## 13. Status aggregation (sync-generated-content-status)

A parent `generated_content` row's `status` is a function of its children's statuses:
- ANY child `queued` or `submitted` → parent stays `scheduled`/`approved`.
- ALL children terminal AND ≥1 `published` → parent `published`.
- ALL children terminal AND NONE `published` → parent `failed`.

`syncGeneratedContentStatus(supabase, gcId)` runs after every status flip in `publish-post`. Wrap calls in try/catch — the aggregate is best-effort; a transient DB error here must not prevent the row's own status from being recorded.

Reference: `src/lib/publish/sync-generated-content-status.ts`.

---

## 14. Common pitfalls

**❌ Don't flip `status='submitted'` before the provider call without a revert path.** Stage-failure (no provider call) MUST revert to `'queued'`. Post-call-ambiguity MUST go to `'failed'` (cannot safely retry without an idempotency key the provider accepts). Use the `stage` discriminant on responses to distinguish. (BKE lab note 2026-05-07.)

**❌ Don't `Promise.all` fan workers for parallel publishes without per-gc staging coordination.** Blotato/equivalent per-URL locks cascade-fail when N workers hit the same media URL simultaneously. The per-gc cache from §11 plus lock-aware retry is the fix.

**❌ Don't hardcode platform names in title-case literals.** Always route through `wire-platform.ts` helpers. A single `if (name === 'Instagram')` against a lowercase DB value is a silent failure. (BKE lab note 2026-04-27.)

**❌ Don't write a reconciler that only considers ACTIVE rows.** Terminal history MUST gate inserts. The partial unique index protects concurrent inserts within the active partition; it does NOT protect against re-inserting after a sibling has already published+aggregated. (BKE lab note 2026-05-01 — carousel triple-publish.)

**❌ Don't ship `text: content_body || ''` without truncation.** Threads 500, Twitter 280, Bluesky 300 silently 422 otherwise. Every publish path goes through `truncateForPlatform`. (BKE lab note 2026-04-28.)

**❌ Don't trust `scheduled_posts` row insertion order to match publish order.** publish-post is per-platform; per-platform completion is asynchronous. Parent `generated_content.status` flips only when ALL children terminal. Any code that reads parent status before child fanout is done is racing.

**❌ Don't rebuild `platform_connections.boardIds` from API response on re-test.** Blotato's `/v2/users/me/accounts` does NOT return FB `pageId` — that field is user-entered. Bulk-rebuilding from API response wipes manual fields. Always merge: `{ ...(prev?.boardIds || {}), ...(api[k] ? { [k]: api[k] } : {}) }`. (BKE lab note 2026-04-28.)

**❌ Don't bind the retry-guard purely to provider-id presence.** `status='submitted' + no provider id` is ambiguous: it might mean stage-failure (safe to retry) OR post-call-loss (unsafe). The `stage` discriminant on the response, NOT the row state, is the source of truth for safety. Without it, every stage-failure becomes a permanent failure (BKE lab note 2026-05-07).

**❌ Don't run the worker without `WORKER_SECRET` env in BOTH Vercel and the worker host.** Worker-to-API calls (e.g. `publish-post → /api/publish`) need the secret header to bypass `requireUser`. Missing it on the worker side makes every scheduled publish 401. (BKE memory note `bke_publish_worker_secret`.)

**❌ Don't store DALL-E / HeyGen / Kie / Replicate URLs in any DB row.** They expire. Persist to Storage, store the path, re-sign at publish time.

**❌ Don't skip the cross-user `gcId` ownership check in browser-invoked publish routes.** Pass `expectedUserId` to `acquirePublishGuard`. Without it, user A can grief user B's locks or probe B's publish state.

---

## 15. Test plan (smoke)

1. **Single-platform publish** — IG only, with image. Manual Post Now + scheduled (insert with `publish_at = now() + 90s`).
2. **Multi-platform fanout** — same gc to 5 platforms. Verify staging cache populates after first child writes `metadata.blotato_staged_paths`; later children short-circuit.
3. **Stage-failure recovery** — point Storage at a bad bucket name briefly mid-fanout; verify rows revert `submitted → queued` and Trigger.dev retry picks them up.
4. **Terminal-history gate** — manually delete the parent `generated_content.status='published'` aggregate state, run reconciler; verify it does NOT re-insert children for the already-`published` (gc, platform) pairs.
5. **Lock contention** — fire scheduler-tick twice concurrently for the same due row. Verify only one provider call lands (other returns `code: 'lock-contended'`).
6. **Sibling-published refusal** — manually re-queue a (gc, platform) that already has a `published` row; verify guard refuses with `code: 'sibling-published'`.
7. **Text overflow** — generate 600-char content; publish to Threads + Twitter; verify Threads gets 499+ellipsis, Twitter gets 279+`'...'`, and both 200.
8. **Media-missing refusal** — image format, blank `media_storage_paths`; verify worker flips to `failed` with the actionable message instead of calling provider.
9. **Webhook SSRF** — point the webhook URL at `http://169.254.169.254/`; verify the publisher refuses before fetch.

---

## 16. Kickoff prompt

> Execute the Publishing Pipeline Playbook at `<workspace>/templates/a-tier/PUBLISHING-PIPELINE-PLAYBOOK.md` for this new app. Mirror the BKE implementation. Use the BKE codebase at `<reference-app>/src/` as the reference — when in doubt about a pattern, read the BKE file rather than re-deriving.
>
> Inputs:
> - Target platforms: [list — e.g. Twitter, LinkedIn, Slack, custom webhook]
> - Publisher per platform: [direct OAuth / Blotato / GHL / Bundle / custom]
> - Media-required platforms: [subset of above]
> - Per-platform text limits: [from each platform's docs]
> - Worker queue: [Trigger.dev v4 / Inngest / BullMQ / other]
>
> Pause after the schema + advisory-lock RPCs + provider-call-guard ship for review. Then build publish-post worker → scheduler-tick → reconciler in that order, pausing between each.

---

**Reference files in BKE:**
- `src/trigger/publish-post.ts` — main publisher
- `src/trigger/scheduler-tick.ts` — cron dispatch
- `src/trigger/scheduled-posts-reconciler.ts` — terminal-history gate
- `src/lib/publish/provider-call-guard.ts` — advisory lock + sibling check
- `src/lib/publish/wire-platform.ts` — name normalization
- `src/lib/publish/platform-limits.ts` — truncation
- `src/lib/publish/resolve-target.ts` — per-(workspace, platform) publisher routing
- `src/lib/publish/sync-scheduled-posts.ts` — approve → insert with media preflight
- `src/lib/publish/sync-generated-content-status.ts` — parent gc aggregation
- `src/lib/publish/blotato-stage.ts` — provider staging + lock-aware retry
- `src/lib/publish/dedup.ts` — per-gc staging cache
- `src/lib/publish/blog/publish-ghl-blog.ts`
- `src/lib/publish/blog/publish-wordpress.ts`
- `src/lib/publish/blog/publish-webhook.ts` — HMAC + SSRF
- `src/app/api/publish/route.ts` — manual Post Now (shares guard pattern)
- `supabase/migrations/00040_scheduled_posts_dedup.sql` — partial unique index
- `supabase/migrations/00062_scheduled_posts_publish_guard.sql` — status check + sibling guard
- `supabase/migrations/00063_publish_advisory_locks.sql` — publish_locks table + RPCs