Workflow RPCs

Writes go through workflow RPCs — intent-named Postgres functions that own the transition logic. They're the same functions the MCP tools wrap, exposed over PostgREST at /rest/v1/rpc/<function>. Every function is SECURITY INVOKER, so RLS and the transition triggers apply to you as the caller.

curl -X POST \
  "https://jionfqfmjinkrmzlsghf.supabase.co/rest/v1/rpc/submit_for_review" \
  -H "apikey: $CP_PUBLISHABLE_KEY" \
  -H "Authorization: Bearer $CP_ACCESS_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{"p_publication":"<uuid>"}'

Every mutation returns the updated row, so you don't need a follow-up read.

Planning

Function	Arguments	Does
resolve_project	p_project	Resolve a project slug or id to its uuid
create_collection	project, type, name, personas, outlets…	New campaign or section (dates derive from the schedule)
update_collection	collection, name, slug, type, …	Patch a campaign/section (null = unchanged); a persona or outlet array replaces that set
set_collection_personas	collection, persona ids	Replace the personas a campaign targets, in order — the first is the primary target
set_collection_topic	collection, topic	Set or clear the campaign's topic; its name and description are frozen onto the campaign at launch
delete_collection	collection	Delete a campaign and all its publications + promos
create_stub	project, collection, title, idea	Publication in stub

Publications

Function	Arguments	Transition
update_draft	publication, title/subtitle/summary/body/notes	writes content; stub → draft when a body is added (a subtitle alone does not promote a stub)
submit_for_review	p_publication	draft → reviewing
review_publication	publication, verdict, notes	approve → approved; request_changes → draft
archive_publication	p_publication	→ archived
start_promoting	p_publication, p_canonical_url	approved → promoting — the live state. Records the Substack URL, stamps published_at, and returns channels + per-channel constraints. Re-callable on an already-promoting publication to re-read the constraints
reschedule_publication	p_publication, p_scheduled_for	none — writes scheduled_for only. Use this to move a date; set_publication_details is absolute and clears the channel and canonical URL it is not given. Rejects null (clearing is that function's job) and refuses an archived publication

mark_published was removed when published merged into promoting; its p_canonical_url argument now belongs to start_promoting.

Promos

Function	Arguments	Transition
create_promo	publication, channel, body	new promo in draft
update_promo	promo, body / scheduled_for	edit copy or schedule
reschedule_promo	p_promo, p_scheduled_for	none — writes scheduled_for only. Refuses a promo with a job lodged at upload-post: that job holds the real time, so moving the row alone would leave the post firing on the old date. Reschedule those through the dispatch function's reschedule action, which cancels and re-lodges
submit_promo_for_review	p_promo	draft → reviewing
review_promo	promo, verdict, notes	approve / request changes
publish_promo	p_promo, p_external_url	manual channels → published

Schedule ordering

A promo can never be scheduled before the publication it promotes, and a publication can never be moved past its earliest promo. This is enforced by triggers on both tables (migration 42), not by the functions above — so it holds for create_promo, update_promo, set_publication_details, the MCP write tools, the dispatch function's service-role writes and hand-run SQL alike. The rule lives in one place, schedule_order_conflict, which every enforcement point calls.

Times are compared as instants, not calendar days: a promo at 08:00 under a publication that goes out at 17:00 the same day is refused.

An unscheduled publication constrains nothing — there is no conflict with a date that does not exist, so a promo may be scheduled before its parent has been.

Function	Arguments	Returns
schedule_conflict_for	p_subject ('promo' / 'publication'), p_id, p_scheduled_for	the refusal reason, or null when the move is legal. Read-only, RLS-scoped — ask before writing when a write would be expensive to undo

Rows that already violated the rule when migration 42 landed are left alone. They still dispatch, but neither they nor their publication can be rescheduled until the pair is fixed; the migration reports the count on apply.

Dashboard

Function	Arguments	Returns
pipeline_status	p_project (optional)	counts by status, upcoming scheduled_for, stale items

Error behavior

An invalid transition returns a structured error that names the current status and the allowed next states, so a client can self-correct. For example, calling start_promoting on a draft returns:

invalid publication transition: draft -> promoting (allowed from draft: reviewing, archived)

These are the same functions MCP calls

The MCP tool surface is a thin, schema-documented layer over exactly these RPCs. If you're building an AI client, use MCP; if you're scripting against the database, call the RPCs directly.