Migration SQL

Migrations execute on the pinned Sprout PostgreSQL runtime with a constrained per-app migration role and app schema search path.

Supported patterns

• tables, indexes, constraints, views, sequences, triggers, and ordinary functions inside the app schema;
• PostgreSQL built-ins available in the pinned runtime;
• auth.uid() for user ownership;
• auth.is_app_admin() for the current app's platform-managed Edit permission;
• auth.is_system() in an RLS policy for rows explicitly available to a queue, cron, or inbound-email system Function;
• auth.connection_id() in an integration provider app's RLS policies to keep each connection's rows separate;
• RLS policy creation and alteration;
• gen_random_uuid(), JSON/JSONB, arrays, generated expressions, and common scalar/date functions.

Deployments run the complete declared history in a disposable schema before touching the live app schema. This catches runtime-only missing functions, invalid policy expressions, type errors, and ordering assumptions.

Forbidden capabilities

• role, user, database, tablespace, extension, server, publication, or subscription management;
• GRANT, REVOKE, COPY, anonymous DO blocks, or SECURITY DEFINER;
• schema management, role switching, or search-path changes;
• direct platform, shared, public, or other app schema references;
• RLS disabling, ownership changes, large-object import/export, or dblink;
• direct PostgreSQL connections from application code.

Each migration is limited to 2 MiB. Keep IDs lexicographically ordered and never modify or omit an applied migration.

auth.is_system() is true only for Sprout's short-lived signed system identity. The restrictive platform policy independently scopes that identity to the current app or development namespace. Do not replace it with auth.uid() is null, which would also match ordinary anonymous requests.

App admission and management permissions do not automatically bypass RLS. auth.is_app_admin() lets an app explicitly use its existing App Admin (Edit) permission in a policy. It is scoped to the current database namespace and reads the current access projection, so an old browser token cannot preserve Admin rights after the access operation has finished reconciling. Ordinary readers, anonymous users, system Functions, and installation administrators with only review access are not App Admins. Existing Files ownership remains unchanged. The read-only platform view opencloud_app_access exposes is_app_admin through the existing Data SDK in browsers and Functions; do not create or alter it. Development user A is App Admin; B and C are ordinary users in that sandbox.

For shared articles, allow admitted users to read and only App Admins to write:

create policy article_read on articles for select using (true);
create policy article_create on articles for insert with check (auth.is_app_admin());
create policy article_edit on articles for update
  using (auth.is_app_admin()) with check (auth.is_app_admin());
create policy article_delete on articles for delete using (auth.is_app_admin());

For private app-owned leads ingested by a queue, use auth.is_system() for the consumer's writes and auth.is_app_admin() for dashboard access. No embedded owner UUID or first-visitor role table is necessary. Give the system identity read access too (using (auth.is_system()) with check (auth.is_system())): the Data SDK's create and update return the written row, and PostgreSQL applies read policies to returned rows.

auth.connection_id() returns the organisation-built integration connection a provider app's system Function is acting for: a consumer's operation call, a scheduled sync, or a webhook for that connection. In the provider's development session, an explicit invocation of one of those Functions acts for the selected test connection. It is null for every other request, including the provider's own cron and production owner invocations. Use it to keep data synced for one connection away from every other connection:

create table contacts (
  id uuid primary key default gen_random_uuid(),
  connection_id uuid not null default auth.connection_id(),
  external_id text not null,
  name text not null,
  unique (connection_id, external_id)
);

alter table contacts enable row level security;

create policy contacts_connection on contacts
  using (auth.is_system() and connection_id = auth.connection_id())
  with check (auth.is_system() and connection_id = auth.connection_id());

See Organisation-built integrations.

Search columns and indexes

The unpublished SDK 2.4/platform search candidate uses PostgreSQL full-text search and the platform-installed pgvector extension. App migrations use unqualified types and functions; extension installation and shared-schema access remain forbidden to apps.

create table document_chunks (
  id uuid primary key default gen_random_uuid(),
  owner_id uuid not null default auth.uid(),
  document_id uuid not null,
  content text not null,
  embedding vector(128),
  search_text tsvector generated always as
    (to_tsvector('english', content)) stored
);

create policy document_chunks_owner on document_chunks
  using (owner_id = auth.uid())
  with check (owner_id = auth.uid());

create index document_chunks_words on document_chunks using gin (search_text);
create index document_chunks_meaning on document_chunks
  using hnsw (embedding vector_cosine_ops);

Match the generated text configuration and vector dimensions to the manifest declaration. System ingestion requires its own explicit auth.is_system() policy and correct owner assignment; delegation of a private PDF grants no additional database permission.

Full-text predicates can use GIN. HNSW index DDL is supported, but this initial search API deliberately computes exact cosine ranking and does not use approximate HNSW acceleration. RLS and equality filters apply before each component's 100-candidate cutoff. Use bounded queries; their database statement timeout is five seconds.

The platform initializes pgvector in public, verifies version 0.8.2 or newer and rejects an incompatible existing location/version instead of relocating or upgrading it silently. Restore requires that extension readiness and regenerates search RPCs for the restored schema. Application backups contain app data and schema, not a separately installable extension. These are installation lifecycle operations, not SQL capabilities exposed to app code.

Portability guidance

Prefer documented PostgreSQL core functions. When using a less common expression, reproduce it in a small migration and rely on validate plus the server-side preflight before activation.