Migration SQL

Migrations execute on the pinned OpenCloud 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_system() in an RLS policy for rows explicitly available to a queue, cron, or inbound-email system Function;
• 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 OpenCloud'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.

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.