Persistence

Looking for the product view?

The Help Center explains this surface as users see it: Storage.

Apps can store and query custom data in a dedicated Postgres workspace. Each app gets its own schema, tenant-isolated and lazily provisioned on first migration.

Architecture

• Dedicated schema: Each app gets a unique Postgres schema (deterministic name derived from appId + tenant)
• Tenant isolation: Connections use SET SESSION AUTHORIZATION to enforce row-level security
• Lazy provisioning: The schema is created automatically when the first migration runs
• Auto-cleanup: A database trigger drops the schema when the app is deleted

Migration Files

Migrations are raw SQL files placed in the migrations/ directory of your app project. They run in alphabetical order and are append-only — never modify or delete a migration that has already been deployed.

File Format

migrations/
├── 001-create-orders.sql
├── 002-add-status-column.sql
└── 003-add-index.sql

• Naming: Use a numeric prefix for ordering (e.g., 001-, 002-)
• Format: Raw SQL — no wrapper function needed
• Execution: Each migration runs in its own transaction (automatic BEGIN/COMMIT)
• Tracking: Completed migrations are recorded in a _migrations table within the app's schema

Example Migration

-- migrations/001-create-orders.sql
CREATE TABLE orders (
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  quantity INTEGER NOT NULL DEFAULT 0,
  status TEXT NOT NULL DEFAULT 'pending',
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_orders_status ON orders (status);

-- migrations/002-add-customer.sql
ALTER TABLE orders ADD COLUMN customer_name TEXT;
ALTER TABLE orders ADD COLUMN customer_email TEXT;

Migration Rules

• Append-only: Never modify or delete a migration that has been deployed
• Idempotent schema changes: Use IF NOT EXISTS / IF EXISTS where appropriate
• No down migrations: Rollback is not supported — write a new forward migration instead
• Transaction per file: Each file runs in its own transaction; if a migration fails, it rolls back and subsequent migrations are skipped
• Capability-gated files: A migration may open with -- requires: <capability> (see below); where the capability is missing it is skipped and left pending rather than failing the deploy

Capability-Gated Migrations

A migration that depends on something not every install has — today, the vector type from pgvector — can say so on its leading comment lines:

-- requires: embeddings
CREATE TABLE ticket_embeddings (
  ticket_id INTEGER NOT NULL,
  embedding vector(1536) NOT NULL
);

On a deploy where every listed capability is available, the file runs like any other. Where one is missing, the file is skipped without being recorded, the deploy continues, and the response lists it under skippedMigrations. It stays pending and applies on the first deploy after the capability arrives (an Informer upgrade, a provisioned extension) — in file order among whatever is pending then, so a gated migration must be self-contained (its own tables and indexes; nothing later may assume it ran).

Capability names are the app type's flags (platform.capabilities), and embeddings additionally requires pgvector to be present in the workspace database. An unknown name fails the deploy with a 422 naming the known capabilities — a typo must never skip forever. Several names may be listed, comma-separated; -- @requires is accepted too. Only the leading comment block is read: a directive after the first SQL statement is not one.

Older Informer releases do not read the directive and run the file as plain SQL. An app that must also install on them keeps such DDL out of numbered migrations altogether and creates it from the code path that needs it.

---

POST /api/apps/{id}/_migrate

Run pending migrations for an app. Creates the workspace schema if it doesn't exist.

Authentication: Required

Permissions Required: app:write (Designer for Magic Reports, Admin for full Apps)

Path Parameters:

Parameter	Type	Description
id	string	App UUID or natural ID

Run pending migrationsPOST /api/apps/00000000-0000-4000-8000-000000000001/_migrate

Idempotent: each migrations/*.sql file runs once. This app already migrated during deploy, so nothing is pending.

Response · 200

{
  "_links": {
    "self": {
      "href": "https://informer.example.com/api/apps/00000000-0000-4000-8000-000000000001/_migrate"
    }
  },
  "migrated": [],
  "total": 1
}

Captured from the API examples test suite; ids and timestamps are normalized.

Response Fields:

Field	Type	Description
migrated	string[]	Names of migrations that were run in this call
total	number	Total number of migration files found

What it does:

1. Creates the app's schema if it doesn't exist (CREATE SCHEMA IF NOT EXISTS)
2. Creates the _migrations tracking table if it doesn't exist
3. Lists all .sql files in migrations/, sorted alphabetically
4. Skips any migrations already recorded in _migrations
5. Runs each pending migration in its own transaction
6. Records each successful migration in _migrations
7. Creates an embedded datasource record for the workspace (if migrations exist)

Error Responses:

• 404 Not Found - App doesn't exist
• 403 Forbidden - User lacks write permission
• 500 Internal Server Error - Migration SQL failed (the failed migration is rolled back; previous successful migrations are preserved)

Notes:

• Also called automatically as part of POST /api/apps/{id}/_deploy
• Safe to call multiple times — already-run migrations are skipped
• If no migrations/ directory exists, returns { migrated: [], total: 0 }

---

Workspace Provisioning Flow

When migrations first run for an app, the following provisioning happens automatically:

1. Schema creation: A new Postgres schema is created with AUTHORIZATION set to the tenant role
2. Migration table: A _migrations tracking table is created in the schema
3. Migrations run: Pending SQL files are executed in order
4. Datasource record: An embedded datasource (type: 'app') is created, pointing back to the app

The embedded datasource allows the workspace to be referenced by other Informer features (e.g., datasource queries).

---

POST /api/apps/{id}/workspace/_query

Run management SQL against the workspace schema. This powers the Database admin tab's SQL prompt; it is a management surface, not a handler API (handlers use the query() callback below).

Authentication: Required

Permissions Required: app:write (Designer for Magic Reports, Admin for full Apps)

Payload:

Field	Type	Default	Description
sql	string	—	The SQL to run (max 5 MB; the request body caps at 16 MB)
params	array	[]	Positional parameters (max 100)
limit	integer	50	Page size for read-mode SELECT/CTE results (max 50,000)
offset	integer	0	Page offset (read mode only)
mode	string	read	read or write; see below
dryRun	boolean	false	Write mode only: run the statement inside a transaction and roll back instead of committing

read mode (the default) runs as the workspace's SELECT-only _ro role. The read-only guarantee is the role's lack of write privileges, not a session setting, so no BEGIN READ WRITE or hidden COMMIT can escape it. A single-statement SELECT/WITH is paginated with limit/offset via a cursor; anything else (EXPLAIN, SHOW, VALUES) runs as-is. A write attempt answers 400.

write mode runs as the workspace owner inside a transaction the server controls, and never paginates (a data-modifying CTE cannot be wrapped in a cursor). It runs one statement per call through the extended query protocol; a second statement answers 400. That single-command guarantee is what makes dryRun authoritative: the payload cannot smuggle its own COMMIT past the rollback.

Response fields: rows, rowCount, fields ([{ name, dataTypeID }]), command, truncated (more rows exist beyond limit), and dryRun (whether the transaction was rolled back).

Every execution is audited with the caller, mode, dry-run flag, command, row count, duration, and error, readable through the query-history route below. The SQL is truncated to 10,000 characters in the audit row.

Query an App's workspacePOST /api/apps/00000000-0000-4000-8000-000000000001/workspace/_query

Management SQL against the per-app workspace schema (the Database admin tab). SELECT/CTE statements are paginated by limit/offset.

Request body

{
  "sql": "SELECT customer, total FROM orders ORDER BY total DESC",
  "limit": 10
}

Response · 200

{
  "_links": {
    "self": {
      "href": "https://informer.example.com/api/apps/00000000-0000-4000-8000-000000000001/workspace/_query"
    }
  },
  "rows": [
    {
      "customer": "Berglunds snabbkop",
      "total": 1208
    },
    {
      "customer": "Around the Horn",
      "total": 642.5
    }
  ],
  "rowCount": 2,
  "fields": [
    {
      "name": "customer",
      "dataTypeID": 25
    },
    {
      "name": "total",
      "dataTypeID": 1700
    }
  ],
  "command": "SELECT",
  "truncated": false,
  "dryRun": false
}

Captured from the API examples test suite; ids and timestamps are normalized.

---

GET /api/apps/{id}/workspace/query-history

The audited _query executions for the App, newest first. Same app:write permission as _query. Query parameters: limit (1-500, default 50) and offset. Linked as inf:app-workspace-query-history.

---

GET /api/apps/{id}/workspace/_dump

Download a SQL dump of the workspace schema (the Database admin tab's export button). Same app:write permission as _query.

---

GET /api/apps/{id}/workspace/_backup

Download a portable backup of the workspace data: streams application/gzip as an attachment containing manifest.json plus one CSV per non-empty table. Same app:write permission as _query. The archive carries no DDL and no migration bookkeeping by design; schema always comes from the App's own migrations on the target, and the manifest records pack identity and the applied-migration list so restore can verify it is landing on the same App at the same schema state. Linked as inf:app-workspace-backup.

---

POST /api/apps/{id}/workspace/_restore

Restore a workspace backup. multipart/form-data upload of a backup .tgz (up to 2 GiB). Requires app:write and the App must have a library. A backup from a different App (pack/slug mismatch) answers 409; retry with ?force=1 to override the identity check. The migration-state check has no override: the target must have the same applied migrations as the backup's manifest. When the request sends Accept: text/event-stream, the route streams progress steps as server-sent events; otherwise it answers once at the end. Linked as inf:app-workspace-restore.

---

The query() Callback

Server route handlers access the workspace via the query() callback. See Server Routes for full documentation.

// In a server route handler
export async function GET({ query }) {
  const rows = await query('SELECT * FROM orders WHERE status = $1', ['pending']);
  return rows;
}

The connection is automatically scoped to the app's schema with tenant isolation enforced via SET SESSION AUTHORIZATION.

---

End-to-End Pattern

Here's a complete example showing the migration → server handler → client fetch flow:

1. Migration

-- migrations/001-create-items.sql
CREATE TABLE items (
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  done BOOLEAN NOT NULL DEFAULT false,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

2. Server Handler

// server/items/index.js
export async function GET({ query }) {
  return await query('SELECT * FROM items ORDER BY created_at DESC');
}

export async function POST({ query, request }) {
  const { name } = request.body;
  const rows = await query(
    'INSERT INTO items (name) VALUES ($1) RETURNING *',
    [name]
  );
  return { status: 201, body: rows[0] };
}

3. Client Code

// Load items
const items = await fetch('/api/_server/items').then(r => r.json());

// Add item
await fetch('/api/_server/items', {
  method: 'POST',
  headers: { 'Content-Type': 'application/json' },
  body: JSON.stringify({ name: 'New item' })
});

4. Deploy

npm run deploy
# Runs migrations, bundles server routes, uploads files