Roll The Dice Café: event and social automation

Summary

The café's events live in a Google Sheet. From there the automation keeps a Google Calendar in step, rolls weekly and monthly events forward, generates one poster per event date, writes captions in the owner's voice, posts to Facebook and Instagram through Buffer at sensible times, and pulls post metrics back in so the next caption knows what worked.

The problem

The café runs dozens of recurring and one-off events. Every one needs promoting across Facebook and Instagram at the right time, with a decent image and copy that sounds like the owner. Past events kept getting promoted, posters cost money every time they were generated, and a mistake from a posting bot is public.

The idea

Keep the sheet as the one place humans edit. Let everything else be derived from it by rules that are tested, and call AI only where a person would otherwise have to be creative.

The architecture

1. 01

   Source of truth

   The Event Index and Standard Diary sheets are what humans edit.

2. 02

   Guard (01:15)

   Validates the sheet, expires past one-offs, emails a date request and makes sure each event has its poster.

3. 03

   Poster generator

   Drive check, lock, re-check, then Gemini, validate and save as Posters/YYYY-MM-DD.

4. 04

   Posting slots

   Gatekeeper, daily, prospectus, weekly and Instagram slots, each with a guardrail and a random delay.

5. 05

   Publish

   Buffer for Facebook and Instagram, with every outcome written to the post log.

6. 06

   Learn

   Meta metrics are pulled every four days and fed back into caption prompts.

The build

n8n on the Pi. All decision logic is in logic.js with tests A–J plus date and safety cases, which run inside the n8n container. build.py compiles only the functions each Code node needs, and gen_workflows.py writes the sub-workflows so they can be imported from the CLI.

• n8nOrchestration: 178-node master plus three sub-workflows
• Google SheetsEvent index, standard diary, post logs, metrics
• Google DrivePhoto folders, reference images, generated posters
• Google CalendarA rolling 30-day calendar kept in step with the sheet
• GeminiCaptions in the owner's voice, poster image edits
• Buffer APIFacebook and Instagram publishing
• Meta Graph APIPost metrics pulled back for learning
• JavaScript + testslogic.js is the single source of truth, covered by tests A–J

The AI

Captions in the owner's voice, and poster images edited from a reference photo. Everything around the AI is deterministic: whether a post happens, which event, which date and which image are all decided by code.

• Gemini writes posts in the owner's voice, using the event details and recent performance as context.
• Gemini image editing turns each event's '.Stable' reference image into a dated poster, once per date.

The automation

Calendar sync, date roll-forward, expiry, date-change emails with a picker form, poster generation, scheduled posting, metric collection and logging all run by themselves. Humans edit a spreadsheet and answer the occasional email.

• The daily Event Guard handles expiry, date-change requests and poster checks.
• Recurring events roll forward to their next date at 01:00.
• Posts go out in hourly, daily, weekly and prospectus slots, with delays and guardrails.
• Metrics come back in every four days, and every outcome is written to a structured log.

The data

A post log with success, cancelled and failed rows per slot, an automation log (EVENT_ID … DATE_CHANGE_COMPLETED), per-post Meta metrics, and poster locks.

The result

The café's social channels stay current without anyone opening Facebook. Each event date's poster is paid for once. Expired events stop being promoted, and the organiser gets a one-click form to reschedule.

• The Event Guard runs at 01:15 every day. It sets expired one-off events to Inactive and emails the organiser a date-picker form, and a valid new date re-activates the same sheet row.
• Poster generation is idempotent. The workflow checks Drive, takes a lock, checks again, then calls Gemini, so each event date costs one generation at most and the result is reused.
• A safety brake: if more than three events would expire in one run, or the sheet comes back empty or missing columns, the guard stops and changes nothing.
• Hourly gatekeeper, daily, prospectus and weekly posting slots, each with a guardrail check and a random delay so the posts don't look automated.
• Meta post metrics are pulled every four days and upserted into the sheet, so the caption prompt can see past performance.
• Instagram carousels choose photos from each event's Drive folder and skip food posts if Facebook has already covered them that day.

Hard parts

• People type dates in all sorts of ways: UK format, 'Every Thursday', days split across a weekend. The date logic had to be deterministic and tested outside n8n, because Code nodes can't import modules. A build step copies only the functions each node needs into it.
• Two runs could try to generate the same poster at once. A lock row plus a re-check after taking the lock stops that from being paid for twice.

What I learned

Keep the rules out of the workflow canvas. Once the logic moved into a tested file, changes stopped being scary. And a 'refuse to act' safety brake is worth more than any amount of cleverness.