Database Driven Content Creation With UltimateFightIQ

Project: Ultimate Fight IQ (UFIQ) Link: https://ultimatefightiq.com

Case study type: Product build The task: Generate and schedule fight-week social posts from live event data without inventing fighter names, stats, or URLs. What we learned: Treat the database as the only source of facts, inject that context into generation prompts, and keep humans in the loop before anything hits Buffer. Last updated: June 2026

Case study at a glance

Visual walkthrough (UFC 329, live data)

All figures below use UFC 329 rows from Supabase (slug: ufc-329, July 11, 2026, T-Mobile Arena). The event page capture comes from the running app at /events/ufc-329. Other panels are rendered from the same API payload (see data/ufc-329-data.json). Regenerate with node scripts/generate-article-images.mjs.

Same database values on the left, same numbers on the generated card on the right.

Same event data fans use to make picks.

Background

Ultimate Fight IQ already stores everything a fan needs to make picks: events, fight cards, fighter profiles, lock times, and verified results. Fight-week social content needs the same facts, but formatted for Instagram and X.

The easy path is to prompt a generic model: "Write a post about UFC 329." That path fails quickly. Names get misspelled. Records drift. Links point nowhere. Visual stat overlays show numbers that do not match the product.

The product already had accurate data in Postgres. The job was not "add AI to social." It was to route product data into generation and posting, with guardrails that keep creativity separate from facts.

The task

When an admin publishes a UFC event in UFIQ, the system should:

1. Pull the full card, fighter likenesses, and event metadata from the database.
2. Generate one promotional image and caption per matchup (plus an event-level thumbnail).
3. Spread posts across the window before picks lock.
4. Let an admin review, edit, and approve before posts go to Buffer.

One sentence version: use the same database that powers picks to power social content, automatically, with accurate context.

Constraints

• Hallucination risk: Image models and caption models will fill gaps if context is vague.
• Schema drift: Event, fight, and fighter shapes appear in multiple places (app, admin, edge functions). Duplicated queries would diverge.
• Rate limits: Bulk image generation cannot fire all at once.
• Brand safety: Auto-posting without review is unacceptable for a young product.
• Scheduling reality: Posts should anchor to event_lock_at_utc, not arbitrary calendar guesses.
• Platform plumbing: Buffer handles OAuth, channel metadata, and final scheduling. UFIQ should not reimplement that.

Our approach

We split the problem into four layers:

flowchart LR
  A[Supabase: events, fights, fighters] --> B[getEventContext + fighter stats]
  B --> C[Image + caption generation]
  C --> D[buffer_bulk_items queue]
  D --> E[Admin review]
  E --> F[Buffer API]

Facts live in the database. A shared context builder formats them. Models only handle visuals and copy tone. A queue stores drafts. Humans approve. Buffer publishes.

How we solved it

Step 1: One shared event context fetcher

What we did: Created getEventContext() in supabase/functions/_shared/event-context.ts as the single source of truth for event + card data used by image and caption generators.

Decision: One module owns the join shape (events → fights → fighters), section ordering, result formatting, and caption helper lines.

Why: Without this, the admin UI, image worker, and thumbnail generator would each query slightly different columns. Social posts would drift from what users see on event pages.

The fetcher returns structured blocks labeled as ground truth, for example:

• Event name, date, venue
• Main event and co-main with records
• Card lines tagged MAIN EVENT, PRELIMS, or EARLY PRELIMS
• Verified results when recap mode is on
• A canonical event URL pulled from the event slug

Caption context explicitly includes: Event link (use this EXACT URL verbatim...).

Step 2: Fighter stats as a typed catalog, not free text

What we did: Defined FIGHTER_STAT_DEFS (mirrored in src/lib/buffer/fighter-stats.ts and the edge function) mapping stat slugs to real fighters table columns: height, reach, record, striking accuracy, takedown defense, and more.

Decision: Admins pick stats by slug. The worker loads only those columns from Postgres and formats labeled lines for the model.

Why: Letting the model "summarize a fighter" invites invented numbers. Slug-to-column mapping keeps overlays and captions tied to the same rows the app displays.

Step 3: Separate facts from creative instructions in prompts

What we did: Built runGenerate() so every image prompt concatenates:

1. Preset system prompt and brief (creative direction)
2. eventContextText and/or fighterContextText (database facts)
3. Fighter likeness reference images from fighters.image_url
4. Style reference images from admin presets

Captions use a second call with a fixed system prompt plus captionContextLines from the same context object.

Decision: Prefix factual blocks with explicit language: "ground truth," "do not invent names," "do not invent stats."

Why: Models behave better when creative and factual sections are visually separated. The instruction is not "be accurate." It is "here is the truth; do not override it."

Step 4: Bulk jobs that mirror the card structure

What we did: Introduced buffer_bulk_jobs and buffer_bulk_items. A job represents one generation run (manual or auto). Each item maps to one social post: a bout (mode: matchup) or a fighter spotlight (mode: fighter).

Decision: Store bout order, card section, fighter names, selected stat slugs, scheduled time, generated image path, caption, and Buffer post IDs on each row.

Why: The card is the natural unit of fight-week content. Persisting one row per post makes review, reschedule, regenerate, and audit straightforward.

A worker (bulk_generate_next) processes items sequentially with a minimum interval between image API calls, then self-invokes until the job completes.

Step 5: Lock-relative scheduling

What we did: For auto campaigns, computeCampaignSchedule() spreads posts evenly between "now + lead time" and "lock time minus safety buffer" (default 2 hours before lock, up to 7 days back).

For manual bulk runs, admins can instead attach buffer_schedule_offsets (e.g. "3 days before lock," "1 hour before lock") so each matchup fans out to multiple timed posts.

Decision: Anchor timing to event_lock_at_utc, the same moment picks close in the product.

Why: Social hype and product behavior should share one clock. Fight-week posts that land after lock are useless for driving picks.

Step 6: Auto-trigger on event publish, with an off switch

What we did: When an admin publishes an event (admin-set-event-review-state, action publish), the function fire-and-forgets bulk_auto_event unless auto_campaign_enabled is false in buffer_image_settings.

The auto campaign:

1. Confirms required image presets exist
2. Reads connected Buffer channels (all or admin-selected)
3. Cancels any prior running campaign for the same event
4. Inserts a job with campaign_kind: event_publish
5. Queues 1 thumbnail item + 1 item per fight on the card
6. Starts the generation worker

Decision: Automation starts at publish, not at cron time. Admins can pause auto campaigns globally without losing manual bulk tools.

Why: Publishing is the operational signal that the card is real and ready to promote. Tying social generation to that moment removes a manual "remember to start the campaign" step.

Step 7: Human review before Buffer

What we did: Items move through statuses: pending → generating → ready → approved → scheduled.

Admins review images and captions in the Bulk Queue UI, edit copy, regenerate failures, then approve batches. Approved items call the Buffer edge function with signed image URLs from private storage.

Decision: Generation is automatic. Publishing to social is intentional.

Why: One wrong fighter name on a main event post costs more than the time saved by full autopost. Review is the quality gate.

Step 8: Buffer as the posting layer

What we did: Kept OAuth tokens encrypted server-side. The buffer edge function builds GraphQL createPost payloads via shared helpers (buffer-post-input.ts), handles image and video assets, and sets Instagram metadata (Reel vs post) when needed.

Decision: UFIQ generates content and context. Buffer owns channel credentials and the final schedule on each network.

Why: Posting APIs, token refresh, and per-platform quirks are not core product logic. The database-backed generator should not depend on Buffer's schema beyond a thin adapter.

What we built

End-to-end flow for a published event:

1. Admin publishes event in UFIQ.
2. System reads card from events, fights, fighters.
3. Worker generates images using factual context + style presets.
4. Captions are written from the same context, including the real event URL.
5. Admin approves items in the queue.
6. Buffer schedules posts to selected channels at precomputed times.

Results

Before

• Social content was manual: copy card details by hand, hunt for fighter photos, guess post timing.
• Generic AI drafts required heavy fact-checking.
• No link between pick lock time and post schedule.

After

• Publishing an event starts a full-card draft pipeline automatically (when enabled).
• Every generated post is tied to a specific bout row or event row in Supabase.
• Captions receive the canonical event URL from the slug, not a invented path.
• Admins review a grid of ready items before anything reaches Buffer.
• Campaign timeline shows generation progress and failures per item.

How we know it worked

Success is operational, not vanity metrics:

• Bulk items store the bout order and fighter names copied from the database at job creation time.
• Image prompts include the same eventContextText the thumbnail generator uses elsewhere.
• Caption system prompt forbids placeholder URLs and requires the exact link when provided in context.
• Auto campaigns log item_ready, item_failed, and completed events for debugging.
• Re-publishing cancels superseded jobs so stale card data does not keep generating.

What you can learn

1. Do not ask the model what the database already knows. Fetch facts first. Use AI for layout, tone, and visuals only.
2. Label truth in prompts. Explicit "ground truth" sections outperform vague "be accurate" instructions.
3. One context module per domain. Event cards change shape over time. Centralize the join once.
4. Mirror critical catalogs in UI and worker. Stat slugs must match column names in both places or admins will select stats the worker cannot load.
5. Automate drafts, not judgment. A READY → APPROVED gate protects brand trust while still saving hours of production time.
6. Schedule against product events. Lock time is a stronger anchor than "post every 8 hours."
7. Separate generation from distribution. Your database pipeline should not break when a social API changes. Keep Buffer (or any scheduler) behind an adapter.

Next step

If you run a data-rich product (sports, finance, education, local events), the pattern transfers: query → structure → generate → review → publish.

For UFIQ, the natural extensions are recap campaigns (include_results: true after verification), fighter spotlight series using stat presets, and performance feedback loops (which posts drove event page visits before lock).

Asset notes

Regenerate images: npx vite (optional, for event page capture) then node scripts/generate-article-images.mjs.