Migrating from Postgres

If you're porting an app that talks directly to Postgres (or a Postgres-backed backend you outgrew), most of what lived in the database moves cleanly to an Amba primitive. This guide maps the constructs one-to-one so you can plan the migration before you write a line.

The mental shift: in Amba you don't open a connection and run SQL. Data lives in collections, logic lives in functions, and time-based work lives in scheduled functions. Per-user isolation is automatic, so most of the access-control plumbing you hand-wrote disappears. The data features carry over too — native array columns, jsonb, vector embeddings, and text search all have direct equivalents, so you don't flatten your schema to migrate it.

At a glance

Stored procedures → functions

A Postgres function you call with rpc('fn_name', args) becomes an Amba function. A function is just an HTTP endpoint: call it from a client SDK, or run server-side logic inside it. From inside a function you reach the rest of your project by calling Amba's admin REST API with the injected AMBA_INTERNAL_TOKEN — see the functions runtime guide for the full env contract.

Before — a stored procedure that awards a referral bonus:

CREATE FUNCTION award_referral_bonus(referrer uuid, amount int)
RETURNS void AS $$
  UPDATE wallets SET coins = coins + amount WHERE user_id = referrer;
  INSERT INTO ledger (user_id, delta, reason) VALUES (referrer, amount, 'referral');
$$ LANGUAGE sql;

Called from the client as rpc('award_referral_bonus', { referrer, amount }).

After — a function that does the same thing through the admin API. The currency grant writes the wallet and the ledger row in one call, so the body is shorter than the SQL:

export default {
  async fetch(req: Request, env: Env): Promise<Response> {
    const { referrer, amount } = await req.json();
 
    await fetch(`${env.AMBA_API_URL}/v1/admin/projects/${env.AMBA_PROJECT_ID}/currencies/grant`, {
      method: 'POST',
      headers: {
        Authorization: `Bearer ${env.AMBA_INTERNAL_TOKEN}`,
        'Content-Type': 'application/json',
      },
      body: JSON.stringify({ app_user_id: referrer, currency_code: 'coins', amount }),
    });
 
    return Response.json({ ok: true });
  },
};

Deploy with amba functions deploy ./functions/award_referral_bonus.ts, then call it like any HTTP endpoint instead of rpc(...). The token is scoped to this project's admin surface only — see the runtime guide for what it can and can't reach.

Triggers → event-driven functions (or segments)

Row-level triggers — "when a row is inserted, do X" — don't run inside the database, and that's deliberate: side effects belong in your app's flow, not hidden behind a table. Three patterns cover every trigger you have:

The trigger body → an event-driven function. The closest 1:1 mapping: track an event where the write happens, then register an event webhook subscription whose target_url is a deployed function. Every time the event fires, the function receives a signed delivery — your AFTER INSERT body, now with retries, exponential back-off, and a dead-letter queue instead of a silent rollback.

// 1. Where the trigger used to fire on INSERT into `orders`:
await Amba.events.track('order_placed', { total: order.total });

# 2. Deploy the trigger body as a function, then subscribe it to the event:
amba functions deploy ./functions/on_order_placed.ts
 
curl -X POST 'https://api.amba.dev/v1/admin/projects/$PROJECT_ID/webhooks/subscriptions' \
  -H 'Authorization: Bearer $AMBA_PAT' \
  -H 'Content-Type: application/json' \
  -d '{
    "event_name": "order_placed",
    "target_url": "https://$PROJECT.fn.amba.host/on_order_placed"
  }'

The same subscription mechanism covers built-in lifecycle events (user.streak_extended and friends), so "trigger on system state change" works without you tracking anything. Verify the delivery signature in the function.

Reactive cohort work → track an event + a segment. Where an AFTER INSERT trigger flagged a milestone, define a segment that matches users who hit it ("users with >= 5 orders"). Segments are re-evaluated on a schedule and can drive push campaigns, content delivery, and entitlements — no function needed.

Synchronous, must-happen-now work → do it in the function that performed the write. If the procedure that created the row also needs to grant currency or send mail, make those calls right there (see the stored-procedure example above) rather than relying on a trigger to catch up.

Periodic reconciliation → a scheduled function. For triggers that really were doing batch bookkeeping ("nightly, recompute totals"), use a scheduled function instead.

CHECK constraints → schema + write validation

Column-level CHECK constraints and NOT NULL move into the collection schema. Define types and required fields once when you create the collection; writes that violate the schema are rejected at the API boundary before they ever touch storage.

# Where you had: amount int NOT NULL CHECK (amount > 0)
amba collections create orders --field amount:integer --field status:text

For rules richer than the schema enforces (cross-field invariants, "status can only move forward"), put the check in the function that performs the write and reject bad input there — the function is your validation layer, the same role the CHECK constraint played.

RLS policies → automatic per-user scoping

This is the biggest simplification. In Postgres you wrote RLS policies so each user could only see their own rows. In Amba, every collection row is owned by the user who created it, and reads auto-scope to the signed-in user — there's no policy to author and no way to forget one. The server stamps user_id from the session token on write and filters by it on read; a client-supplied user_id is ignored.

So a policy like:

CREATE POLICY own_rows ON notes
  USING (user_id = current_user_id());

…has no replacement. It's the default. You delete the policy and the behavior you wanted is already there.

For data that isn't per-user — a shared catalog, reference tables, articles everyone reads — you have three options:

• read_policy: "public" on the collection — every signed-in user reads all rows, whoever owns them. The direct replacement for a USING (true) read policy; writes stay owner-scoped unless you also set write_policy: "authenticated". See access policies.
• A shared collection (shared: true), whose unowned rows are readable by every signed-in user. See shared collections and seeding.
• A Content Library, purpose-built for global published content with scheduling, versioning, and bulk import.

Pick one of these for anything that used to be a table without an RLS policy (or with a USING (true) policy).

ILIKE / full-text search → the search parameter

Where you reached for ILIKE '%q%' across columns, a trigram index, or the full tsvector + to_tsquery + GIN-index apparatus, collections have text search built into find — no index to maintain, no separate search service:

// Before:
//   SELECT * FROM novels
//   WHERE title ILIKE '%' || $1 || '%' OR summary ILIKE '%' || $1 || '%'
// …or a tsvector column kept in sync by (yes) a trigger.
 
// After — typo-tolerant, relevance-ranked:
const { data } = await Amba.collections.find('novels', {
  search: { q: 'enemis to lovers', columns: ['title', 'summary'], fuzzy: true },
  limit: 20,
});
// Finds "Enemies to Lovers" despite the misspelling, best match first.

How the habits map:

• ILIKE '%q%' across columns → the default search mode: a case-insensitive substring match over columns.
• tsquery / trigram fuzziness → search with fuzzy: true: word-similarity matching that tolerates typos and ranks results by similarity (no explicit order needed). Tune strictness with threshold (default 0.3; higher is stricter).
• Anchored patterns (LIKE 'prefix%') → the like / ilike filter operators, which take the pattern verbatim.
• Embedding / semantic similarity → a vector column + find-nearest.

search composes with filter (AND'd) and pagination, and works on count too. Full reference: text search.

Arrays and jsonb → keep them native

Don't flatten text[] / integer[] columns into jsonb (or comma-joined strings) when you migrate — collections support native array column types (text[], integer[], bigint[], numeric[], boolean[], uuid[]) and the set operators you already use:

// Where you had: tags text[] — declare the same thing:
{ "name": "tags", "type": "text[]", "nullable": true }

// Before: SELECT * FROM novels WHERE tags @> ARRAY['enemies-to-lovers']
const { data } = await Amba.collections.find('novels', {
  filter: { column: 'tags', op: 'contains', value: ['enemies-to-lovers'] },
});

Reserve jsonb for what it's good at — nested documents and mixed-shape objects. A jsonb column migrates as-is, and updates merge-patch by default (top-level key merge, so concurrent writers updating different keys both survive). The rule of thumb: flat, uniformly-typed list you filter on → array column; nested shape → jsonb. A list stored as jsonb can't use the set operators above, which is how "filter novels by trope" quietly becomes impossible. See array columns and set operators for the full SDK surface.

pg_cron → scheduled functions

A pg_cron job becomes a scheduled function: the same function module, deployed with a cron schedule instead of (or in addition to) an HTTP trigger. Where the cron job ran SQL, the scheduled handler calls the admin API to do the equivalent work.

// Replaces: SELECT cron.schedule('reset-daily', '0 0 * * *', $$ ... $$);
export default {
  async scheduled(event: ScheduledEvent, env: Env): Promise<void> {
    await fetch(`${env.AMBA_API_URL}/v1/admin/projects/${env.AMBA_PROJECT_ID}/streaks/reset`, {
      method: 'POST',
      headers: { Authorization: `Bearer ${env.AMBA_INTERNAL_TOKEN}` },
    });
  },
};

Migrating asset URLs

Before you bulk-import asset references into Amba (into a collection field or a Content Library item's media_url):

1. For each source URL, build the full, absolute URL — prepend the storage base path if the provider handed you a relative one.
2. Fetch one of the reconstructed URLs and confirm it returns 200 with the right Content-Type. Verify before you import, not after.
3. Then either reference the external URL directly, or re-upload the bytes into Amba media so the asset is served from your project's media host and survives the old provider being turned off.

Re-uploading is the safer choice for a permanent migration — once the old provider is decommissioned, externally-referenced URLs stop resolving.

Next steps

• Collections — the data model, filters, search, array columns, and auto-scoping in depth.
• Functions and the runtime contract — your stored-procedure and cron replacement.
• Event webhooks — the trigger-shaped delivery mechanism (signed, retried, replayable).
• Segments — the reactive cohort primitive.
• Content Libraries — for shared, published content.