SQL migrations and data access

Stead provisions a private PostgreSQL instance for each project when it needs a database. You write ordinary PostgreSQL migrations for your project schema: tables, indexes, constraints, SQL functions, triggers, and data changes. The hosted workflow does not require you to provision a database or obtain database credentials.

Arbitrary SQL support is a deployment capability. The customer HTTP API and handler data channel accept structured operations, not arbitrary SQL text.

Add a migration

Put numbered .sql files in the migrationsDir from stead.json. The CLI reads them in lexical order. Use zero-padded names such as 0001_orders.sql and 0002_order_index.sql.

This is the starter’s isolation pattern, adapted for private notes:

create table notes (
  id uuid primary key default gen_random_uuid(),
  project_id uuid not null default public.stead_project_id(),
  end_user_id uuid not null default public.stead_user_id(),
  body text not null,
  created_at timestamptz not null default now(),
  foreign key (end_user_id, project_id)
    references public.end_users(id, project_id) on delete cascade
);

create index notes_owner on notes(project_id, end_user_id);
alter table notes enable row level security;
alter table notes force row level security;
create policy notes_owner_policy on notes
  using (
    project_id = public.stead_project_id()
    and end_user_id = public.stead_user_id()
  )
  with check (
    project_id = public.stead_project_id()
    and end_user_id = public.stead_user_id()
  );
grant select, insert, update, delete on notes to stead_app;

Deploy migrations with getstead deploy. Applied migration names and content hashes are recorded. Keep applied files unchanged and add a new file for each schema change. A changed applied migration is refused. There is no automatic down-migration command.

Migrations run as a restricted project role inside a transaction. You can change objects you own in your project schema; you cannot administer the PostgreSQL server, obtain superuser privileges, or alter Stead’s control tables. SQL requiring execution outside a transaction, such as CREATE INDEX CONCURRENTLY, does not fit this deployment path. Temporary tables are disabled; use CTEs for intermediate work.

Expose a table

Add an entry to stead.json:

{
  "table": "notes",
  "columns": ["id", "body", "created_at"],
  "operations": ["select", "insert", "update", "delete"],
  "isolation": "end_user"
}

This is one entry in the top-level exposures array. Names must be plain lowercase SQL identifiers. Stead chooses the project schema; the public deployment payload does not accept a schema override. Expose only the columns and operations your application needs. Keep identity columns out of ordinary writes and fill them from server-established identity defaults.

Deployment checks that an exposed table exists, has enabled and forced row-level security, and has applicable policies referencing the required identity helpers. It refuses Stead’s reserved table names. The default isolation is end_user; project requires only the project identity and deliberately permits shared data within that project.

Policy validation is not a proof that your SQL is secure. It checks for identity-helper references; it cannot prove arbitrary Boolean expressions, functions, or triggers enforce the intended rule. For example, adding an OR condition can broaden a policy. You own the policy logic and any privileged functions you create. Test with two distinct customers and with reads, inserts, updates, and deletes.

Customer reads

import type { SteadClient } from '@getstead/client';

export async function readNotes(customer: SteadClient) {
  return customer.select<{ id: string; body: string; created_at: string }>(
    'notes',
    { limit: 25 },
  );
}

The response is { ok: true, rows, rowCount }. Reads return the exposed columns. where supports equality filters on exposed columns with string, number, or Boolean values. limit defaults to 100 and allows 1–1000. This API has no joins, arbitrary ordering, offset pagination, raw SQL, or customer write operation.

RLS runs with the customer’s authenticated identity. For end_user tables, the intended policy allows that customer’s rows. For project tables, all authorized customers in the project can see the shared rows your policy permits.

Handler writes

A deployed handler receives context.broker.url and context.broker.token. Send a JSON operation to that URL using Authorization: Bearer and Content-Type: application/json:

Operation	Body fields
select	op, table, optional where, optional limit
insert	op, table, values
update	op, table, set, where
delete	op, table, where

Values and filters are parameterized. Every operation still passes the exposure and RLS checks. insert returns exposed rows; update/delete return a row count. The starter contains a complete broker helper and an approval-gated update.

Storage and recovery

The beta database has finite memory, disk, and SQL scratch space; see limits. A private database isolates project SQL workloads from other project databases, but the API and runtime still use shared, bounded capacity.

If a deployment response is interrupted, inspect Diagnostics and retry the same unchanged deployment after recovery. Committed migration receipts prevent already committed migrations from being replayed. Do not rename a migration to force it to run twice. For a full disk, remove unused data or indexes through a new migration; the service reserves space to help recovery, but a destructive schema change is still your responsibility.

Stead’s run history and conversation storage are separate from your application tables. History retention can remove older conversation messages; it is not a permanent archive or a backup API for your application data.