Persistence

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: Member+ role (write permission)

Path Parameters:

Parameter	Type	Description
id	string	App UUID or natural ID

Response:

{
  "migrated": ["001-create-orders.sql", "002-add-status-column.sql"],
  "total": 3
}

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).

---

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