I had eight blog posts sitting unpublished in the CMS. Clicking "publish" on all of them at once felt wrong. Nobody wants to see eight posts appear on the same day, and from an SEO perspective, spacing content out performs better than dumping it. So I built an automated drip-feed system: queue posts in order, and a cron job publishes one every Monday, Wednesday, and Friday at 6am.
The problem with a publish button
The existing setup was simple. Posts had a published boolean. I'd write a post, import it via the admin panel, toggle it to published, hit the deploy button. Cloudflare Pages would rebuild the site and the post would be live.
That works fine when you're publishing as you go. But I'd been heads-down building the vault feature for a couple of weeks and had accumulated a backlog. Manually publishing each one on a schedule meant remembering to log in and click buttons three times a week. That's exactly the kind of thing that should be automated.
Replacing the boolean with a state machine
The first step was replacing published: boolean with a proper status column. Three values: draft, queued, and published. I also added queue_position (integer, 1-20) for ordering the queue, and published_at (timestamptz) to record when a post actually went live, as opposed to the editorial date field which I set manually.
The migration was straightforward. Add the new columns, backfill from the existing boolean (published rows get status = 'published' and published_at = date), add constraints, drop the old column. I wrapped the whole thing in a transaction so it either all works or none of it does.
One constraint I'm quite pleased with: queue_position must be NOT NULL when status = 'queued' and NULL otherwise. The database enforces that you can't have orphaned queue positions floating around on draft or published posts. There's also a partial index on status = 'queued' so the cron query stays fast regardless of how many posts accumulate over time.
The ripple effect
Changing a column name sounds small until you realise how many places reference it. The published boolean was used in fetch-content.js (the build-time script that pulls posts from Supabase), blog.js (the client-side data layer), BlogPanel.jsx (the admin UI), and the manage-blog Edge Function. Every one of those needed updating.
I missed blog.js on the first pass. It has three queries that filter on published=eq.true, and since the column no longer exists, PostgREST was returning 400s. The kind of thing you only catch when you actually try to load the site. This is the sort of bug that a proper dev/staging database would have surfaced before it hit production, something that's increasingly justifying the self-hosted Supabase migration on my roadmap.
The queue management UI
The admin panel now splits posts into three sections: a publish queue at the top (amber-themed, visible only when posts are queued), then drafts, then published posts.
Each draft has a "Queue" button that adds it to the end of the queue. Queued posts get up/down arrows to reorder them and a "Remove" button to send them back to drafts. Next to each queued post, the UI shows the projected publish date based on the Mon/Wed/Fri cron schedule. That last bit is entirely client-side: a helper function calculates the next N cron dates from today, and each queue position maps to a date.
I went with up/down arrows rather than drag-and-drop. For a max-20-item list that only I use, proper drag-and-drop (touch events, scroll containers, accessibility, visual feedback) is complexity that doesn't earn its keep. The arrows fire a reorder action that sends the full new order array to the Edge Function, which reassigns positions 1, 2, 3 sequentially. Simple and reliable.
The Edge Function changes
The manage-blog Edge Function picked up three new actions: queue, dequeue, and reorder. The queue action finds the current max position and assigns the next one, capping at 20. Dequeue sets a post back to draft and resequences the remaining queue to close gaps. Reorder takes an array of IDs and assigns sequential positions.
I also updated the update action to handle a subtle edge case: if you manually publish a post (setting status to published from the editor), it auto-sets published_at if it wasn't already set. And if you move a post out of queued status for any reason, it clears queue_position automatically. The database constraints enforce this too, but belt and braces.
While I was in there, I fixed the trigger-deploy Edge Function. It had a hardcoded admin UUID (my user ID sitting right there in the source code, tracked in git). Swapped it for Deno.env.get("ADMIN_USER_ID"), matching the pattern that manage-blog already uses. Also locked the CORS origin from * to https://drewbs.dev.
The Cloudflare Worker (coming next)
The actual cron scheduler is a Cloudflare Worker with a scheduled event handler. It's specced out but not deployed yet. The Worker queries Supabase for the lowest queue_position post, flips it to published, resequences the remaining queue, and fires the Cloudflare Pages deploy hook. It also posts to a Discord webhook so I get a notification when a post goes live (or if something fails).
Cloudflare Workers free tier includes cron triggers, so this adds zero cost. The cron expression is 0 6 * * 1,3,5, which is 6am UTC on Monday, Wednesday, Friday. During GMT that's 6am local, during BST it drifts to 7am. Nobody's refreshing my blog at 6am on the dot, so the one-hour seasonal drift is fine.
What I'd do differently
The CORS situation is a genuine pain point. With the Edge Functions locked to https://drewbs.dev, I can't test admin write operations locally at all. The database migration and query changes worked fine locally (reads go through the anon key with permissive CORS), but anything that hits an Edge Function needs the live site. For a solo admin panel this is manageable, but it's another tick in the "self-hosted Supabase with a dev environment" column.
Where this leaves things
The blog now has a proper publishing pipeline: write in Obsidian, import to the CMS, add to the queue, and the cron handles the rest. The eight-post backlog that prompted all of this will have already started dripping out by the time of this post. There's something satisfying about building a system that publishes your writing about building systems.
Next up: the Cloudflare Worker deployment, Discord notifications, and then back to the vault black hole feature.