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

---

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 10,000 chars)
params	array	[]	Positional parameters (max 100)
limit	integer	50	Page size for SELECT/CTE results (max 50,000)
offset	integer	0	Page offset

Single-statement SELECT/WITH queries are paginated with limit/offset; other statements (DDL, multi-statement scripts) run as-is.

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
}

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

---

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.

---

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