QueryLayer CMS-001 publishing contract

#Security boundary

QueryLayer exposes only the public schema through the Data API. Authorization helpers and the scheduler live in querylayer_private, which is not an exposed schema. Every exposed table has RLS enabled and explicit least-privilege grants.

Use a Supabase publishable key for both Astro modes:

• Production does not send a user JWT. It assumes the anon database role and can read only active sites and their current published revisions.
• Preview sends the authenticated human or agent access token and an exact revision UUID. RLS verifies that the token's session_id still exists in Supabase Auth, then verifies organization membership or a site-specific content permission. Any write permission implies read access for that same site, but never for sibling sites.
• Never place a secret key or legacy service_role key in Astro client code, build output, preview URLs, repository files, or browser environment variables.

An authenticated preview token is sensitive. Preview endpoints must be server-rendered, must not put access tokens in URLs, and should set Cache-Control: private, no-store.

Privileged authorization helpers correlate the JWT session_id with auth.sessions. Revoking a session therefore removes QueryLayer organization and site access immediately, even though the signed access JWT remains cryptographically valid until its normal expiry.

#Production Astro read

Read public.published_content with a publishable key and filter by a stable site identifier plus path (or content_item_id). The view uses security_invoker, so underlying RLS remains active.

The row includes:

• site and route identity;
• the current published_revision_id as revision_id;
• the exact code-owned Astro template_key and template_version selected by the revision;
• title, excerpt, body, and body format;
• revision-scoped SEO, Open Graph, robots, canonical, and structured data fields;
• published_at for cache invalidation and build provenance.

Do not build production pages from content_items.draft_revision_id or by selecting the latest revision. Only published_revision_id defines production state.

#Preview Astro read

Call public.get_preview_revision(revision_id) with:

1. the publishable API key;
2. the authenticated user's or agent's access token;
3. one exact revision UUID from the preview request.

The RPC is security invoker, returns zero or one row, and is not executable by anon. It may return a published revision, the current draft, or an older immutable revision when the caller is authorized for that site.

#Authoring workflow

1. Call public.create_content_draft(...) once for a stable site route and content identity. It creates the item, first immutable revision, exact site-local template binding, and revision-scoped SEO in one caller-authorized transaction.
2. Call public.create_content_revision(...) for every later change. Pass the exact current draft_revision_id; a stale save fails instead of overwriting a newer draft.
3. A trigger assigns the per-item revision number, records human or agent identity, and advances draft_revision_id.
4. SEO metadata remains append-only because it is part of the immutable revision payload.
5. Call public.request_content_publish(...) with the exact action, target revision, and schedule. The database derives requester identity and forces the initial state to queued.

Never update or delete content_revisions, seo_metadata, or agent_actions. Create a new revision instead.

Both draft functions are security invoker, are executable only by authenticated, and continue to rely on the table RLS policies and narrow column grants. A failure in any step rolls back the complete operation.

#Publish and schedule semantics

publish_jobs.action supports:

• publish: point the item at target_revision_id;
• unpublish: clear the published pointer;
• rollback: point the item at an older target_revision_id.

scheduled_for controls eligibility. The service-role dispatch cycle runs the private processor once per invocation. The processor claims queued jobs with FOR UPDATE SKIP LOCKED, locks the content item, revalidates revision ownership, advances the published pointer, stores the previous revision, and appends an audit event.

The production scheduler invokes the authenticated publish-dispatcher Edge Function once per minute using a project URL and publishable key stored in Supabase Vault. The Edge Function runs the service-role-only dispatch cycle, atomically claims completed jobs for delivery, groups them by site, and calls the allowlisted Vercel Deploy Hook held in Edge Function secrets. HTTP acceptance records only accepted; it is not delivery success. A project-scoped Vercel account webhook verifies the raw-body HMAC before a service-only reconciliation function records immutable deployment.created and terminal deployment.succeeded, deployment.error, or deployment.canceled events. Only the succeeded event advances delivery to succeeded and permits topical-map synchronization. The publish job retains attempts, hook request ID, deployment ID and URL, lifecycle state, completion time, and a bounded error. Replayed provider events are idempotently ignored, stale dispatcher claims are recovered only before a deployment identity exists, and failed deliveries retry up to five times.

The Astro adapter's evaluateDeliveryActivationReadiness helper is a provider-neutral, read-only pre-activation diagnostic. It reports only whether the server endpoint and an opaque server-side credential reference were observed as configured. ready-for-external-activation does not mean that a provider is active or that a webhook signature was verified: every report marks activation as external-required and signature verification as not-performed. Provider-specific activation, raw-body verification, correlation, replay handling, and normalized receipt production remain the responsibility of a trusted server endpoint.

Humans with owner, admin, or editor membership can publish and schedule. Agents need content.publish for publish/unpublish, content.rollback for rollback, and additionally content.schedule when the job is in the future.

Rollback never mutates history. It creates a new job that restores the published pointer to an existing immutable revision, preserving both the request and completion in agent_actions.

Because a revision points to an exact template version, rollback also restores the presentation contract used by that revision. See template-contract.md.

#Tenant and actor model

• Human access comes from active organization memberships.
• Agent access comes from an Auth user mapped one-to-one to agents, plus explicit agent_permissions for a site.
• Agent permissions never imply access to sibling sites.
• Audit rows distinguish human, agent, and system; content revisions require a human or agent identity.
• Organization creation and the first owner membership are bootstrap operations for a trusted backend or migration. A public onboarding RPC is intentionally outside CMS-001.

#Integration credentials

integrations.config contains non-secret settings only. credentials_ref stores an opaque reference to a server-side secret store such as Supabase Vault. It must never contain an API token, password, private key, or serialized credential.

#Supabase reference basis

CMS-001 follows the current Supabase guidance for RLS and explicit grants, publishable versus secret keys, security_invoker database functions and function privileges, Supabase Cron, and database/RLS testing. The August 31, 2026 changelog was reviewed before the schema was authored, including the new-table Data API exposure change and current Postgres 17 guidance.