Using manggaleh — guide for AI agents

A practical reference for another Claude Code session to build on / integrate with a manggaleh backend. manggaleh is a multitenant backend-as-a-service: each project gets an isolated Postgres database with end-user auth, an auto-generated REST Data API, file storage, server-side functions, scheduled jobs, realtime, webhooks and email — all behind one SDK (@manggaleh/sdk) and one CLI (mg / @manggaleh/cli).

If you (the agent) just need to use an existing manggaleh service, you mostly need: the API origin (e.g. https://api.manggaleh.com), the project slug (the tenant), the environment (dev/staging/prod/…), and an API key. Get the rest from this doc.

0. Capabilities — what manggaleh can do, and how to reach it

Division of labour: the CLI is for the owner/admin (provision projects, schema, keys, push functions, gen types — see §2). The SDK is for the application you build (auth, data, storage, functions, realtime — see §3). End-users authenticate through the SDK, not the CLI.

1. Mental model (learn these 5 words)

• Project — an isolated "database" (a better-auth organization). Its slug (e.g. acme) is the tenant you pass to the SDK.
• Environment — dev / staging / prod or custom (e.g. qa). These are just labels for fully isolated Postgres schemas: separate data and separate end-user accounts. The names carry no special meaning — see the box below. You may flag one as production, which only protects it from delete/reset (nothing else).
• Collection — a table (with columns + relations). Defining one instantly exposes the REST Data API for it (list/get/insert/update/delete).
• API Key — every tenant request needs one:
  • publishable (mgpk_…) — safe in a browser/frontend.
  • service (mgsk_…) — secret, full admin (bypasses RLS). Server-side only.
  • function (mgfk_…) — scoped: may only invoke an allowlist of functions, nothing else. Safe to give to a 3rd party.
• RLS / ABAC — row-level security enforced on the server. A collection with an owner column scopes rows per end-user; a permission column (text[]) does tag-based (ABAC) access. The frontend physically cannot read rows it isn't allowed to.

Auto-managed system columns (don't define these, they're added for you): id (uuid), created_at, created_by, updated_at, updated_by.

Auth model for requests: a request usually carries the API key and (for end-user context) the signed-in user's Bearer token. A service key runs as admin (no user needed). RLS is keyed on the user, not the key.

⚠️ There is no “dev” vs “production” mode

manggaleh has no application-wide dev/prod switch (no NODE_ENV that changes behavior). Two things people mistake for one:

• Environments (dev/staging/prod/…) are just isolated schemas — separate data and users. Naming one prod doesn’t change how auth, email, or anything else behaves. The only effect of the production flag is protection from delete/reset.
• Behavioral config is global to the server, set via env vars — the same for every environment. The big one is email: EMAIL_PROVIDER=resend sends real email everywhere; EMAIL_PROVIDER=log (default) sends nowhere and logs instead. So “does email work?” depends on the server config, never on the environment you call or its production flag.

When this doc (or anyone) says “in dev,” read it as “when no real email provider is configured” — that’s the actual condition, not a mode.

2. Fast path: provision a backend from the CLI

npm install -g @manggaleh/cli

# 1. Account (or `mg login` if it exists). Session saved at ~/.manggaleh/config.json.
mg signup --url https://api.manggaleh.com --email you@acme.com
# (non-interactive: set MANGGALEH_EMAIL / MANGGALEH_PASSWORD, or pass --email/--password)

# 2. Project (also provisions a "dev" environment)
mg projects create --name "Acme" --slug acme

# 3. A collection. On a column type: "!" = NOT NULL, "^" = UNIQUE.
#    --owner-column turns on per-user RLS.
mg collections create --project acme --env dev --name todos \
  --columns "title:text!,code:text^,done:boolean,priority:integer" \
  --owner-column owner_id

# 4. API keys
mg keys create --project acme --env dev --type publishable            # mgpk_… (browser)
mg keys create --project acme --env dev --type service --name server   # mgsk_… (shown once)

# 5. Generate TS types for the SDK
mg types --project acme --env dev --out src/db.d.ts

Column types: text, integer, bigint, numeric, boolean, timestamptz, date, uuid, jsonb, plus reference (a FK — set its target collection). Identifiers are lowercase / digits / underscore (my_table); project slugs are kebab-case (my-app).

CLI reference

Account & projects
  mg signup --url <baseUrl> [--name <n>] [--email <e>] [--password <p>]
  mg login  --url <baseUrl> [--email <e>] [--password <p>]
  mg projects
  mg projects create --name <name> --slug <slug>
  mg projects delete --slug <slug> --yes        (hapus SEMUA env & data, permanen — owner)
  mg env list   --project <slug>
  mg env create --project <slug> --name <env> [--production]
  mg env clone  --project <slug> --from <env> --to <env>
        (default "promote": structure + functions/secrets/webhooks/jobs/signup, NO data/users)
        [--full] | pick: [--structure] [--data] [--end-users] [--functions]
                         [--secrets] [--webhooks] [--scheduled-jobs] [--signup-code]
  mg env delete --project <slug> --name <env> --yes
  mg env reset  --project <slug> --env <env> --yes        (empty ALL data; prod protected)
  mg env signup-code  --project <slug> --env <env> [--set <code> | --open]
  mg env auth-origins --project <slug> --env <env> [--set "https://app.example.com,…" | --clear]
  mg env email        --project <slug> --env <env>        (auth email brand/sender name)
        [--set-brand "<Your App>"] [--clear]              (default = project slug; sender address fixed)
  mg env usage  --project <slug> --env <env> [--since <ISO date>]

Schema & keys
  mg collections list   --project <slug> --env <env>
  mg collections create --project <slug> --env <env> --name <name>
        [--columns "title:text,done:boolean!,code:text^"]   (! = NOT NULL, ^ = UNIQUE)
        [--owner-column <col>] [--permission-column <col>]
        [--unique "property_id,date"]   (composite UNIQUE; ";" separates several sets)
  mg collections add-column --project <slug> --env <env> --name <c> --columns "note:text,qty:integer!"
  mg collections delete --project <slug> --env <env> --name <name> --yes
  mg keys list   --project <slug> --env <env>
  mg keys create --project <slug> --env <env> --type <publishable|service|function> [--name <n>]
        [--functions "fnA,fnB"]   (required for --type function)
  mg keys revoke --project <slug> --env <env> --id <id> --yes

End-users, webhooks, data & billing (full dashboard parity)
  mg users list|create|delete|permissions --project <slug> --env <env>
        create: --email <e> --password <p> --name <n>     delete: --id <id> --yes
        permissions: --id <id> --permissions "team:sales,tier:pro"   (ABAC tags)
  mg webhooks list|create|update|delete|test --project <slug> --env <env>
        create: --url <https://…> [--collections "a,b"] [--events "insert,update,delete"]
        update: --id <id> [--enable|--disable] [--url <u>] [--collections …] [--events …]
  mg data list|insert|update|delete --project <slug> --env <env> --collection <c>
        insert/update: --input '{"title":"x"}' (update/delete need --id; delete needs --yes)
        list: [--limit <n>] [--order "col.desc"] [--cursor <cur>]
  mg data export --project <slug> --env <env> [--collection <c>] [--format jsonl|csv] [--out <file|dir>]
        (streams ALL rows — no 200-row cap; without --collection: every collection into --out <dir>)
  mg storage list|rm --project <slug> --env <env> [--id <id> --yes]   (upload/download: use the SDK)
  mg billing --project <slug> [--set-plan <free|pro|business>]

Functions & types
  mg functions list   --project <slug> --env <env>
  mg functions push   --project <slug> --env <env> --file <path> [--name <name>]
        [--allow-domain "api.stripe.com,api.tabby.ai"]   (egress allowlist for ctx.fetch)
        [--runtime vm|deno] [--timeout <ms>] [--memory <mb>]   (per-function config)
  mg functions invoke --project <slug> --env <env> --name <fn> [--input '{…}' | --file <f.json>]
  mg functions delete --project <slug> --env <env> --name <name> --yes
  mg types            --project <slug> --env <env> [--out <file.d.ts>]

Function secrets (injected as ctx.secrets; needs MASTER_ENCRYPTION_KEY on server)
  mg secrets list --project <slug> --env <env>
  mg secrets set  --project <slug> --env <env> --name STRIPE_KEY --value <value>
  mg secrets rm   --project <slug> --env <env> --name STRIPE_KEY --yes

Personal access tokens (owner-level, non-interactive auth for CLI/CI; revocable)
  mg tokens list
  mg tokens create --name <n> [--expires-days <N>]   (value shown ONCE)
  mg tokens rm     --id <id> --yes

• The CLI covers every owner scenario the dashboard does (projects, environments, collections, keys, end-users + ABAC, webhooks, data browser, storage, functions, secrets, signup code, auth redirect origins, billing/usage). Binary storage upload/download is the one data-plane action left to the SDK.
• Deletes require --yes. Production environments/collections are protected server-side.
• Config lives at ~/.manggaleh/config.json (override with MANGGALEH_CONFIG).
• mg functions push upserts by name. mg types emits one interface per collection plus a Collections map.

Authenticating the CLI (interactive vs CI)

The CLI is owner / control-plane tooling (manages projects, schema, keys, functions) — it does not use tenant API keys (mgsk_/mgpk_; those are for the Data API at runtime). Two ways to authenticate:

• Interactive — mg login (email + password) saves a session cookie to the config file.

• Non-interactive (CI) — use a Personal Access Token (mgpat_…): owner-level, revocable, and shown only once. Mint it in the dashboard (avatar menu → Access tokens, i.e. /app/account/?s=tokens) or with mg tokens create, then:

  export MANGGALEH_TOKEN=mgpat_xxx      # owner access token
  export MANGGALEH_URL=https://api.manggaleh.com
  mg collections create --project acme --env dev --name todos --columns "title:text!"
  # no `mg login` needed; revoke anytime from the dashboard / `mg tokens rm`
  

  A PAT is full-owner scope (same power as your login) but separate from your password and revocable — prefer it over putting MANGGALEH_PASSWORD in CI. Change your password anytime in the dashboard (avatar menu → Password, needs the current one).

2b. Environments: create, clone/promote, and keys

Who does this: managing environments is an owner / control-plane action — done in the dashboard, the CLI (mg env …), or the control-plane API with an owner PAT. The app SDK cannot and must not do it: the SDK talks to one environment's Data API with a tenant key (mgpk_/mgsk_) and has no project/environment/clone operations by design (an app holding a publishable key must never be able to create or wipe environments).

An environment (dev/staging/prod/custom) is an isolated schema — its own data, its own end-users, and its own API keys. Typical "set up a new environment" flow:

# 1. Create an empty environment (gets a fresh publishable key automatically)
mg env create --project acme --name staging          # add --production to flag it protected

# 2. Promote your structure + config into it WITHOUT dragging dev's test data/users.
#    Default preset = "promote": structure + functions + secrets + webhooks +
#    scheduled jobs + signup code; NO data, NO end-users → a clean target.
mg env clone --project acme --from dev --to staging

#    …or pick exactly what to copy:
mg env clone --project acme --from dev --to prod --structure --functions
mg env clone --project acme --from dev --to staging --full      # everything incl. data + users

# 3. Mint the keys THIS environment needs (see below — keys are NOT cloned).
mg keys create --project acme --env staging --type service --name server

Clone components (structure, data, endUsers, functions, secrets, webhooks, scheduledJobs, signupCode):

• Additive / safe — structure (creates missing collections, never drops), functions/ secrets/scheduledJobs (upsert by name), webhooks (append), signupCode. Allowed into a production target (it never touches existing data).
• Destructive — data & endUsers TRUNCATE+overwrite the target, so they are blocked when the target is production. endUsers requires data (replacing users cascades to rows).

⚠️ API keys are per-environment and are NOT cloned. A service/publishable key created for dev only works for dev — using it against staging/prod returns 401. After creating/cloning an environment, mint new keys in that environment (dashboard API Keys tab, or mg keys create --env <env> --type service). This is intentional: a leaked dev key can't touch prod.

(Control-plane API equivalents, owner-authenticated: POST …/environments, POST …/environments/clone with {source,target,include}, POST …/environments/:env/keys.)

3. SDK (@manggaleh/sdk)

Framework-agnostic (browser or Node 18+; needs global fetch + WebSocket).

npm install @manggaleh/sdk

Rate limits are retried for you

The SDK retries 429 (and 503) automatically: bounded attempts, honouring the server's Retry-After header, exponential backoff with jitter otherwise. A 429 means the request was rejected before it ran, so retrying is safe even for POST. Ordinary errors (400/403/404/409) are never retried — they're final.

createClient({ …, retry: { attempts: 5, maxDelayMs: 20_000 } }); // tune
createClient({ …, retry: false });                               // handle it yourself

Create one client per project + environment

import { createClient } from "@manggaleh/sdk";

export const client = createClient({
  baseUrl: "https://api.manggaleh.com", // API origin (no trailing slash needed)
  tenant: "acme",                       // project slug
  env: "dev",                           // "dev" | "staging" | "prod" (default "prod")
  apiKey: "mgpk_xxx",                   // publishable for browser, service for server
  // storage: tokenStorage,             // persist the session across reloads (see Gotchas)
});

End-user auth

const { user } = await client.auth.signUp({ email, password, name, /* code? */ });
await client.auth.signIn({ email, password });
await client.auth.signOut();
const session = await client.auth.getSession(); // SessionResult | null

The session token is captured & attached automatically. Auth is per environment.

Passwordless sign-in (per environment) — via the SDK:

await client.auth.sendOtp(email);                          // emails a one-time code
const { user } = await client.auth.signInWithOtp({ email, otp });  // verify → signed in

Whether the code is actually emailed depends only on the server's EMAIL_PROVIDER (see “There is no dev/prod mode” below) — not on which environment you call:

• EMAIL_PROVIDER=resend (real provider configured) → emailed for real.
• EMAIL_PROVIDER=log (default, no provider) → not emailed; the code is printed to the server log instead ([auth:otp] … -> 481923). To skip the inbox entirely, set DEV_OTP_CODE=1234 and every code becomes that fixed value. Leave DEV_OTP_CODE empty once a real provider is configured (otherwise anyone can sign in with it).

(Raw endpoints, if not using the SDK: POST /auth/email-otp/send-verification-otp → /auth/sign-in/email-otp.)

If the environment has a signup code, passwordless is gated like sign-up: OTP won't register new users (existing users still sign in) — new accounts must come through the gated /sign-up.

Email verification (on-demand OTP — no link). Sign-up does not auto-send a verification email, and verification is not required to sign in — a new user is signed in immediately, just with emailVerified=false. Verify on demand: when the user taps "verify email" in your app, send an email-verification OTP and confirm it with verifyEmail, which (unlike signInWithOtp) does not create an account or mint a session:

await client.auth.sendOtp(email, "email-verification"); // emails a code when the user asks
await client.auth.verifyEmail({ email, otp });          // sets emailVerified=true only

Use verifyEmail to verify and signInWithOtp only to actually log a user in passwordlessly — don't use signInWithOtp to "verify": for an unknown email it creates an orphan passwordless account and issues a session. Because nothing is auto-sent at sign-up, there's no double-send footgun — you send exactly one code, when the user asks.

Social login (Google / Apple). Configure it per-environment in the dashboard (Environment → Sign-up tab → Social login) with your own OAuth credentials — the consent screen shows your app (white-label), and you paste the shown callback URL into the Google/Apple console. Full step-by-step provider setup (Google + Apple), field-by-field, with a troubleshooting table and checklist: see the Social login setup guide. Then from your app:

const { url } = await client.auth.signInSocial({ provider: "google", callbackURL: "/done" });
window.location.href = url!; // browser redirects to Google → back to callbackURL, signed in

For a cross-origin SPA or Capacitor app (origin capacitor://localhost etc.), pass returnTo instead of callbackURL and finish with client.auth.completeSocialSignIn() on the return page — manggaleh bridges the session back as a token (no cookies). See the setup guide § Capacitor. Native Swift/Kotlin (non-WebView) is still a follow-up.

Two-factor auth (TOTP). Opt-in per user:

const { totpURI, backupCodes } = await client.auth.twoFactor.enable({ password }); // show QR + codes
await client.auth.twoFactor.verifyTotp({ code });   // confirm setup once to activate
// …later, sign-in returns { twoFactorRedirect: true } → prompt for the code:
await client.auth.twoFactor.verifyTotp({ code });   // completes the session

verifyBackupCode covers a lost authenticator; generateBackupCodes regenerates the set.

Password reset (forgot password) — OTP. The standard reset flow: manggaleh emails the user a one-time code that they type back into your app; you set the new password in one call. App-side (publishable key, no service key), and it always resolves (200) regardless of whether the email exists — no account-enumeration:

await client.auth.sendPasswordResetOtp({ email });                    // emails a 6-digit code
await client.auth.resetPasswordWithOtp({ email, otp, newPassword });  // verify code + set the new password

Same UX as sign-in OTP — no email link to click, no reset page to host — so it works identically on web and mobile/native, with no redirect origins to allowlist. The code respects DEV_OTP_CODE in dev and expires in 5 minutes.

Change password (while signed in):

await client.auth.changePassword({ currentPassword, newPassword, revokeOtherSessions: true });

Auth email branding. All auth emails to end-users (sign-in codes, email verification, password reset) are sent from your app's brand, not manggaleh, using a professional template. There is one setting — brandName — used as the sender display name (email header) and in the body (e.g. "Sent by <brand>"). It defaults to the project slug, so it's already your project's name out of the box. Configure per environment (owner) in the dashboard (Sign-up tab → "Auth email branding"), via mg env email --set-brand "<Your App>", or PUT /api/projects/:slug/environments/:env/email { brandName }.

The From header becomes e.g. <Your App> <no-reply@updates.manggaleh.com> — your brand shows in the inbox while the sending address stays on manggaleh's verified domain for auth emails (that's what keeps deliverability working; per-project sender domains are on the roadmap). For transactional email you send yourself, notifications.email.send accepts an optional per-message from override — see Email in §3.

Admin user-management (service key only)

Server-side only — requires a service key (publishable/function → 403). Manages the environment's end-user directory (e.g. to clean up an orphan account or rotate a password):

const admin = createClient({ tenant, env, apiKey: SERVICE_KEY, baseUrl });
await admin.admin.users.list({ limit: 50 });
const u = await admin.admin.users.findByEmail("alice@cust.com");
await admin.admin.users.setPassword(u.id, "newPassword123");
await admin.admin.users.disable(u.id);   // revokes sessions (soft — not a persistent ban)
await admin.admin.users.delete(u.id);     // permanent; cascades sessions + credentials

disable revokes all sessions (immediate logout) but is not a persistent ban — the end-user schema has no banned column. To truly lock someone out, delete them or setPassword to a value they don't know.

Data API (CRUD)

interface Todo { id: string; title: string; done: boolean; created_at: string }
const todos = client.data.from<Todo>("todos");

const created = await todos.insert({ title: "Buy milk", done: false }); // returns full row
const one     = await todos.get(created.id);   // null (not error) when missing
const updated = await todos.update(created.id, { done: true });          // partial patch
await todos.remove(created.id);                                           // -> void
const rows    = await todos.list();            // one page (default 50, server max 200)

Atomic updates (counters, balances, stock) — avoid lost updates. A normal update is a blind set: if you read a value, change it in JS, and write it back, two concurrent requests can clobber each other (lost update). Instead use the atomic $inc operator (evaluated in the DB under the row lock) and the optional $guard (compare-and-set) to enforce an invariant:

await posts.update(id, { views: { $inc: 1 } });        // exact even under heavy concurrency
await posts.update(id, { score: { $inc: -2 } });       // negative delta = decrement

// Oversell-proof decrement: only applies while stock >= 1, else throws 409 and
// leaves the row UNCHANGED (distinct from 404 "not found / not yours").
await products.update(id, { stock: { $inc: -1 }, $guard: { stock: "gte.1" } });

$guard takes PostgREST-style predicates ({ col: "op.value" }, eq/neq/gt/gte/lt/lte/like/ilike/in/is). It also powers optimistic concurrency — guard on a version/updated_at column ($guard: { version: "eq.3" }) and bump it in the same patch. $inc requires a numeric column, and accepts a decimal string ({ $inc: "0.05" }) which is applied exactly in the DB — use strings for money (see Money & exact decimals below). Works the same inside client.tx([...]) / ctx.db.tx([...]).

Query: filter / sort / paginate / embed

const open = await todos.list({
  filters: { done: false, title: "ilike.%milk%", priority: "gte.3" }, // plain value = eq
  order: "created_at.desc",   // add ".desc" for descending
  limit: 50,                  // capped at 200
});

// Cursor pagination + optional total
const p1 = await todos.page({ limit: 20, count: true });   // { data, nextCursor, count }
if (p1.nextCursor) await todos.page({ limit: 20, cursor: p1.nextCursor });

// Projection + embed relations (nested via dotted paths)
await client.data.from("orders").list({
  select: ["id", "total", "customer_id"],   // id always included
  embed: ["customer(name,phone)"],           // belongs-to: FK value → related row
});

// belongs-to (object) + one-to-many (array) + nesting, in one request:
await client.data.from("properties").get(id, {
  embed: ["owner(name)", "photos(url)", "reviews.author"],
  // owner   → object (this property's FK)
  // photos  → array  (rows that FK back to this property)
  // reviews.author → array of reviews, each with its author object
});

// Aggregation — count/sum/avg/min/max + group by, computed in the DB (RLS-respected):
await client.data.from("reservations").aggregate({
  groupBy: "status",                     // omit for a single summary row
  count: true,                           // → count
  sum: ["amount"], avg: ["amount"],      // → sum_amount, avg_amount
  filters: { created_at: "gte.2026-01-01" },
});
// → [ { status: "confirmed", count: 320, sum_amount: 145000, avg_amount: 453.1 }, … ]

Filter operators: plain value = eq; gt/gte/lt/lte; like/ilike (ilike.%x%); in.a,b,c / nin.x,y; is.null / is.notnull; array containment cs/ov (below). Filtering & sorting run on the server over the whole dataset.

OR / nested filters (filters are AND-ed; use or for unions):

// status = pending OR flagged, AND priority >= 3
await client.data.from("tasks").list({
  filters: { priority: "gte.3" },
  or: "status.eq.pending,status.eq.flagged", // wraps to (…OR…); nestable: and(…)/or(…)
});

JSONB path filters — query inside a jsonb column with col->>key:

await client.data.from("items").list({ filters: { "meta->>plan": "eq.pro" } });

Array containment — filter a jsonb array column (or a Postgres array column) by its elements: cs = contains all listed elements, ov = overlaps (contains any). No join collection needed for tag/facet search:

// villa_types is a jsonb array like ["family","luxury"]
await villas.list({ filters: { villa_types: "cs.family,luxury" } });   // has BOTH
await villas.list({ filters: { villa_types: "ov.luxury,business" } }); // has EITHER
await villas.list({ filters: { half_days: "cs.saturday" } });          // facet: has "saturday"

// The JSON-array form is equivalent — use it when values contain commas/spaces:
await villas.list({ filters: { villa_types: 'cs.["Beach","Family"]' } });
await villas.list({ filters: { villa_types: 'ov.["Beach"]' } });

// A JSON *object* is real jsonb containment — for object columns, not arrays:
await items.list({ filters: { meta: 'cs.{"plan":"pro"}' } });          // meta @> {...}

Both value forms are accepted: a comma-separated list, or a JSON array literal. Elements are JSON-parsed (numbers/booleans keep their type; anything else is a string). Malformed JSON is a 400, never an empty result. Rejected on scalar columns and on the JSON-path form (400). Works in or()/and() groups and aggregate filters; not supported in $guard.

Relation-existence filters (exists / notExists) — filter parents by their children (one-to-many, Prisma some/none style) in ONE query. The classic villa search "free between check-in and check-out" is a notExists over the availability rows in that window:

// Properties with NO availability row in [2026-09-01, 2026-09-03)
await properties.list({
  notExists: "availability(date.gte.2026-09-01,date.lt.2026-09-03)",
});
await properties.list({ exists: "reviews" });          // has at least one review
await properties.page({ exists: "availability", count: true }); // combines with count

The name is a child collection that has a FK back to this one (same rule as one-to-many embed; ambiguous → 400). Conditions in parentheses use the normal col.op.value grammar against the child's columns, incl. and()/or() groups and cs/ov. RLS applies inside the subquery: a child row the caller cannot see does not satisfy exists. Raw HTTP: ?exists=… / ?nexists=…. v1 scope: list/page (+count) — not available in aggregate or bulk mutations.

Upsert — insert or update on a UNIQUE conflict. The target is a unique column, or an array of columns matching a composite unique constraint (declared on the collection — see below):

await client.data.from("items").insert(
  { sku: "S1", title: "Widget", qty: 10 },
  { onConflict: "sku" }, // if sku exists → updates that row instead of 409
);
// Composite: one availability row per (property_id, date) — double-booking-proof
await client.data.from("availability").insert(
  { property_id: p, date: "2026-09-01", status: "blocked" },
  { onConflict: ["property_id", "date"] },
);

Composite unique constraints are declared when creating the collection (uniques: [["property_id","date"]] in the API body, or CLI mg collections create … --unique "property_id,date"), or added later via the alter-columns API (addUniques; fails 409 if existing rows already collide). NULLs never equal each other in Postgres, so rows with NULL in a unique column can repeat.

Bulk insert — up to 1000 rows in ONE request + ONE transaction (e.g. a channel-manager sync writing ~90 blocked dates per villa), with conflict handling; returns { count, ids }:

await client.data.from("availability").insertMany(rows, {
  onConflict: ["property_id", "date"],
  onConflictAction: "ignore",   // skipDuplicates: existing rows win; count = actually inserted
  // or "merge" (default when onConflict is set): upsert every row
});

Rows may have different column shapes (omitted columns keep the table DEFAULT). Owner/audit columns are stamped per row; RLS applies. Note the request body cap (2 MiB) bounds practical batch size — chunk client-side around 500–1000 rows. Realtime/webhook events still fire per row.

Migrating existing data — keeping your primary keys. id is server-generated by default, and sending one is a 400 (so a migration can never silently get a different id back than it wrote). To import rows under their original keys, pass clientIds with a service key — then foreign keys from the old system keep resolving and you don't need a legacy_id mapping layer:

const admin = createClient({ baseUrl, tenant, env, apiKey: SERVICE_KEY });
await admin.data.from("amenity").insert(
  { id: "11111111-2222-3333-4444-555555555555", amenity_id: "wifi" },
  { clientIds: true },
);
// Bulk ETL, and idempotent re-import (re-running the job updates in place):
await admin.data.from("amenity").insertMany(rows, { clientIds: true, onConflict: "id" });

Raw HTTP: ?client_ids=true. Publishable keys and act-as-user calls are refused (403) — a browser must never choose row ids. Ids must be valid UUIDs.

Bulk update / delete by-filter (requires ≥1 filter; owner-scope/RLS still apply; returns { count, ids }). ⚠️ Capped at 1000 rows per call — if more rows match, only the first 1000 are touched and there is no error: check count and repeat until it drops below 1000.

await client.data.from("tasks").updateWhere({ status: "eq.done" }, { status: "archived" });
await client.data.from("tasks").deleteWhere({ status: "eq.spam" });

Money & exact decimals (numeric columns)

Money belongs in numeric columns, and manggaleh keeps them exact end-to-end — as long as you treat them as strings:

• Reads: a numeric (and bigint) value arrives as a JSON string ({"amount":"12.34"}) — its precision exceeds what a JS number (IEEE-754 double) can hold. mg types generates string for these columns.
• Writes: send a string ({ amount: "12.34" }) — it is passed to Postgres verbatim and parsed exactly. Sending a JS number works but goes through a double first.
• $inc: use a string amount ({ $inc: "0.005" }) for exact in-DB addition.
• Aggregates: sum/avg over numeric/bigint columns return exact strings; over integer/float columns they return numbers. min/max return whatever the column's wire format is (string for numeric).
• Never do float math on money client-side; compare/add with a decimal library (or integers of the smallest unit) if you must compute in JS.

Dates vs. timestamps

Pick the column type by what the value is, because they serialise differently:

A date round-trips as the same calendar day for every client and every server timezone: write "2027-12-25", read "2027-12-25". Sending a full ISO datetime to a date column is allowed and truncates to its day; free-form prose ("December 25, 2027") is rejected with a 400 naming the column.

Fixed in Aug 2026: date columns previously came back as a UTC instant derived in the server's timezone, so a server one hour ahead of UTC returned 2027-12-24T23:00:00.000Z for 2027-12-25. If you worked around it by declaring timestamptz, you can move back to date.

Export & backup

Stream a whole collection — no 200-row cap — as JSONL (default) or CSV. Two routes, same query-filter grammar as list:

# Owner (dashboard session / PAT) — admin mode, all rows:
mg data export --project acme --env prod --collection reservations --out reservations.jsonl
mg data export --project acme --env prod --out backup-dir/          # every collection
mg data export --project acme --env prod --collection payouts --format csv --out payouts.csv

# Tenant surface (curl): service key = all rows; end-user Bearer = their rows only.
curl -s "$B/data/reservations/export?status=eq.confirmed" -H "x-api-key: mgsk_xxx" > confirmed.jsonl

Semantics: rows stream oldest-first in keyset pages of 200 (each page its own transaction), so exports of any size don't hold a connection. It is not a frozen snapshot — rows inserted mid-export may or may not appear (existing rows are never skipped or duplicated). One export = one metered request.

Self-hosting backups: the operator side (whole-database pg_dump, daily systemd timer, per-schema restore) ships in the repo under docs/BACKUP.md — schema-per-environment means one dump captures every tenant, and one schema can be restored alone.

Transactions (ACID — all or nothing)

const results = await client.tx([
  // Atomic + guarded: concurrent transfers can't lose an update or overdraw.
  { op: "update", collection: "accounts", id: a, patch: { balance: { $inc: -25 }, $guard: { balance: "gte.25" } } },
  { op: "update", collection: "accounts", id: b, patch: { balance: { $inc: 25 } } },
  { op: "insert", collection: "ledger",   values: { from: a, to: b, amount: 25 } },
  // ops: "insert" | "update" | "delete" | "get"
]);
// results[i].data (the row, for insert/update/get) or results[i].deleted (for delete)

Semantics & limits

• Atomic: all ops run in one DB transaction on one connection (BEGIN…COMMIT); if any op fails, the whole thing rolls back and nothing persists.
• Order: ops run top-to-bottom; results come back in the same order.
• Max 50 ops per transaction — for this fixed-list form only. The interactive ctx.db.transaction(async t => …) inside a Function has no op-count cap (its real bounds are the function timeout, the 30 s idle-in-transaction timeout, and 15 s per statement) — that is the supported pattern for long atomic cascades like a GDPR account deletion; see §4.
• Same security context as a normal request: end-user → RLS/owner-scope applies to every op; service key → admin; with actAsUser the whole tx runs as that user. (So a user's tx can only touch rows they're allowed to.)
• Available both from the app/server (client.tx([...])) and inside a Function (ctx.db.tx([...])).

A tx is a fixed list — you can't read a value mid-transaction and branch on it, and there's no upsert op. For simple conditional writes (counters, balances, stock, optimistic-concurrency version bumps), reach for atomic $inc + $guard above — they're race-safe without a read step, so they beat any read-then-write pattern under load. For richer read → decide → write logic, do it inside a Function (it runs server-side; read with ctx.db, then commit the writes with ctx.db.tx — all under the caller's RLS):

⚠️ Don't read a value, compute a new total in JS, and write it back as a literal (patch: { balance: prev - 25 }) — two concurrent requests will clobber each other (lost update). Use { balance: { $inc: -25 }, $guard: { balance: "gte.25" } } instead. Note a bare ctx.db.get + ctx.db.update are two separate transactions, so they have the same race — for a true locked read-then-write, use ctx.db.transaction(async t => …) with t.get(coll, id, { forUpdate: true }) (see §4).

// Function: confirm a payment atomically (read → decide → write in one unit)
module.exports = async (input, ctx) => {
  const order = await ctx.db.get("orders", input.orderId);
  if (!order || order.paid) throw new Error("not payable");
  await ctx.db.tx([
    { op: "update", collection: "orders",       id: order.id,        patch: { paid: true } },
    { op: "update", collection: "availability", id: order.slotId,    patch: { held: false } },
    { op: "insert", collection: "fee_breakdown", values: { order_id: order.id, fee: input.fee } },
  ]);
  return { ok: true };
};

Server-first tip: combine actAsUser + tx to run an atomic multi-table flow as a specific user with RLS enforced — no service-key bypass needed.

Storage (files)

const obj  = await client.storage.upload(file, { name: "receipt.pdf" }); // file: Blob/File
const list = await client.storage.list();
const blob = await client.storage.download(obj.id);
await client.storage.remove(obj.id);

// Images: transform on the fly (server-side). Great for thumbnails / LQIP blur.
const thumb = await client.storage.download(obj.id, { width: 400, fit: "cover", format: "webp" });
const lqip  = await client.storage.download(obj.id, { width: 24, blur: 8 });   // tiny blurred placeholder
// params: width, height, fit (cover|contain|fill|inside|outside), quality (1-100),
//         format (webp|jpeg|png|avif), blur. Non-images are returned unchanged.

// Signed URL — usable directly in <img src> with NO api-key/token, until it expires.
// (expiresIn seconds, default 1h, max 1y; image transform params allowed.)
const url = await client.storage.getSignedUrl(obj.id, { expiresIn: 3600, width: 400, format: "webp" });
// <img src={url} />  — works without the SDK (great for emails, CDNs, plain <img>).

Owner/ABAC-scoped by RLS — safe from the browser with a publishable key. (permissions tags on upload only apply with a service key.)

Functions (call server-side logic)

const { top } = await client.functions.invoke("topProducts", { n: 10 });

• From the browser: publishable key + signed-in user → runs as that user (RLS applies).
• From a server: service key → admin (bypasses RLS).
• A function-scoped key (mgfk_) may only invoke its allowlisted functions.

Act-as-user (server-first). A trusted server with a service key can run calls as a specific end-user so RLS still applies (instead of the admin bypass) — ideal for Next.js RSC / server actions. Pass actAsUser to the SDK (sends the x-act-as-user: <userId> header):

// per request, on your server:
const db = createClient({ baseUrl, tenant, env, apiKey: SERVICE_KEY, actAsUser: session.userId });
await db.data.from("orders").list();   // only this user's rows; inserts owned by them

Only service keys honor it (a publishable/function key can't impersonate). The user must exist.

Where does session.userId come from? — validating a Bearer token server-side. Your SSR/BFF server receives the end-user's Bearer token (from your login flow or a header your app forwards). Verify it by calling getSession() with that token — an invalid/expired token resolves to null:

// Per request on your server (SSR loader, API route, middleware):
const probe = createClient({ baseUrl, tenant, env, apiKey: PUBLISHABLE_KEY, token: userToken });
const session = await probe.auth.getSession();   // { user: {id,email,…}, session: {expiresAt} } | null
if (!session) return unauthorized();
// Now act as that VERIFIED user with RLS enforced:
const db = createClient({ baseUrl, tenant, env, apiKey: SERVICE_KEY, actAsUser: session.user.id });

Two caveats: build a fresh client per request — a client is stateful (it stores its token and applies set-auth-token refreshes), so a shared module-level client would leak one user's session into another's requests. And the rate limit is per API key (120 req/min by default), not per user — size validation traffic accordingly (or cache the session per token for its lifetime).

Realtime

const unsub = client.realtime.subscribe("todos", async (e) => {
  // e = { type: "change", schema, collection, op, id }  — NO row data!
  if (e.op === "delete") { removeFromUI(e.id); return; }
  const row = await todos.get(String(e.id));   // refetch (also runs through RLS)
  if (row) upsertInUI(row);
});
// later: unsub();

Auto-reconnects. e.id may be a number → String(e.id) before get.

Live data + optimistic updates (.live())

Don't want to hand-write the subscribe→refetch→merge loop above? from<T>(c).live() returns an opt-in store that does it for you and applies mutations optimistically (instant UI, automatic rollback on failure). subscribe/getSnapshot match React's built-in useSyncExternalStore — no extra dependency; Zustand is an optional wrapper.

const todos = client.data.from<Todo>("todos").live({ order: "created_at.desc" });

// React:
const list = useSyncExternalStore(todos.subscribe, todos.getSnapshot);
useEffect(() => () => todos.close(), []);     // stop realtime on unmount

await todos.insert({ title });        // appears instantly → swapped for the server row
await todos.update(id, { done: true }); // toggles instantly → reverts if the server rejects
await todos.remove(id);               // disappears instantly → comes back on failure

live() is additive: it reuses list()/get()/realtime under the hood and changes nothing about the existing APIs. Also exported: createLiveCollection, LiveCollection, LiveOptions.

Email (server-side, service key required)

const admin = createClient({ baseUrl, tenant, env, apiKey: process.env.MG_SERVICE_KEY });
await admin.notifications.email.send({
  to: "customer@example.com", subject: "Receipt", html: "<p>Thanks!</p>",
  // from: "Wezo <no-reply@wezo.ae>",  // optional per-message sender override
});

from (optional) overrides the sender for transactional email — for deliverability it must be a domain your email provider has verified. Auth emails (OTP, password reset) always send from the platform's verified address; you control their display name + body branding via brandName (mg env email).

Outbound webhooks (spec)

Configure per environment (dashboard Webhooks tab or mg webhooks …): a URL gets an HMAC-signed POST when data changes, filtered by collection and event type. Delivery contract:

// POST <your url>   headers: x-manggaleh-event: insert|update|delete
//                            x-manggaleh-signature: sha256=<hex>
{
  "id": "<row id>",
  "event": "insert",            // insert | update | delete
  "collection": "orders",
  "env": "<environment schema id>",
  "record": { /* current row, re-fetched at delivery time; null for delete */ },
  "timestamp": "2026-08-09T12:00:00.000Z"   // when it was dispatched
}

• Signature: HMAC-SHA256 of the exact request body bytes with your webhook secret, hex, prefixed sha256=. Verify before trusting.
• Delivery is at-most-once: one attempt, 5 s timeout, no retries — a receiver error means that event is gone (the webhook row records lastStatus/lastError for debugging). Retries/backoff are on the roadmap; design the receiver for revalidation ("something changed → refetch"), not as a ledger.
• No ordering guarantee; deliveries fire concurrently. record is the row at delivery time — two rapid updates can both carry the second state. There is no replay protection (no nonce/timestamp in the signature) — dedupe on your side if it matters.
• The dashboard Test button sends the same payload shape with event: "test".

Error handling

import { ManggalehError } from "@manggaleh/sdk";
try { await todos.insert({ title: "x" }); }
catch (err) {
  if (err instanceof ManggalehError) {
    // err.status: 401 (sign in), 403 (RLS / wrong key / scope), 404, 429 (quota), 400…
    // err.message, err.body
  } else throw err;
}

Note: get(id) on a missing row resolves to null; everything else throws.

What each status means

A malformed query is never answered with an empty result. Anything the server cannot interpret — an unknown operator, an operator that doesn't apply to the column's type, a malformed JSON value, a bad uuid — comes back as a 400 naming the column and the operator, so it can't be mistaken for "no matching data". The one deliberate exception: a bare filter value is still taken literally when it isn't a known operator (name=a.b@c.com is an eq on that string), because legitimate values contain dots.

4. Writing Functions (server-side JS, in the dashboard "Functions" tab)

// Function "topProducts"
module.exports = async (input, ctx) => {
  // ctx.db.list returns { data, nextCursor, count? } — note the .data
  const { data: orders } = await ctx.db.list("orders", { order: "total.desc", limit: input.n ?? 5 });
  ctx.log("rows", orders.length);          // appears in run logs (console.log works too)
  await ctx.email.send({ to: "ops@acme.com", subject: "Report", text: "..." });
  return { top: orders };                  // must be JSON-serializable
};

ctx: input (the invoke payload), db (the Data API, RLS-aware — full table below), email.send(...), fetch(url, init?) (outbound HTTP — allowlisted, below), secrets (your encrypted env secrets), request (raw HTTP request — for webhook receivers), log(...).

ctx.db reference. Same operations and the same options as the SDK, so SDK code can be pasted into a Function. Two return shapes differ — noted below:

list accepts both the nested SDK shape and a flat one — these are equivalent:

await ctx.db.list("orders", { filters: { status: "paid" }, limit: 10 }); // SDK shape
await ctx.db.list("orders", { status: "paid", limit: 10 });              // flat shape

Unknown option keys are rejected (unknown option "onConflicts"), so a typo fails on the first call instead of silently doing nothing.

Fixed in Aug 2026: ctx.db.list used to reject the nested { filters } shape with unknown column: filters, and ctx.db.insert silently ignored its options argument — so a documented { onConflict } upsert was a plain insert that only failed on the second (replayed) call. Both work now, in both runtimes.

Interactive transactions — ctx.db.transaction. When you must read a row, decide, then write atomically (and a plain $inc/$guard isn't enough), open an interactive transaction. It holds one DB transaction across the callback, so t.get(coll, id, { forUpdate: true }) locks the row until you commit — concurrent invocations serialize on the lock instead of losing updates. The callback's return value is the result; throw to roll back (and it auto-rolls back if the function ends without committing). Inside: t.get/insert/update/remove (no nested tx).

// Safe money transfer: lock the source row, check funds, then move money.
module.exports = async (input, ctx) =>
  ctx.db.transaction(async (t) => {
    const from = await t.get("accounts", input.from, { forUpdate: true });
    if (!from || from.balance < input.amount) throw new Error("insufficient funds"); // → rollback
    await t.update("accounts", input.from, { balance: from.balance - input.amount });
    await t.update("accounts", input.to,   { balance: { $inc: input.amount } });
    return "ok";
  });

Multi-row invariants → { isolation: "serializable" }. A row lock (forUpdate) protects one row. When your rule spans different rows — "at least one admin must remain", "no double-booking a slot" — two transactions can each read the same rows, decide independently, and each write a different row, breaking the invariant (a write-skew). Pass { isolation: "serializable" } as the 2nd arg: Postgres detects the conflict and aborts one, and the runtime automatically retries the whole callback (up to 5×) so the loser re-reads the committed state and does the right thing.

module.exports = async (input, ctx) =>
  ctx.db.transaction(async (t) => {
    const a = await t.get("doctors", input.a);
    const b = await t.get("doctors", input.b);
    if ((a.on_call ? 1 : 0) + (b.on_call ? 1 : 0) <= 1) throw new Error("one must stay on call");
    await t.update("doctors", input.self, { on_call: false });
    return "ok";
  }, { isolation: "serializable" });   // ← also: "repeatable read"

⚠️ Because a serializable transaction may run more than once, keep external side effects (ctx.email, ctx.fetch, charging a card) outside the callback — do them after it commits. Only DB work belongs inside.

Each open transaction holds a pooled connection for the function's duration, so keep them short (the function timeout + server idle-transaction timeout are the backstops); max 4 open at once. For a fixed list of writes with no read step, use ctx.db.tx([...]) instead (lighter).

Caller identity — ctx.user. The invoking end-user is resolved server-side and exposed as ctx.user = { id, email } — no need to forward a token in the input or call /auth/get-session yourself. It is null when the caller is a service key (trusted server, not an end-user) or a non-HTTP invoke (cron). For act-as-user calls (x-act-as-user) it is { id, email: null }.

module.exports = async (input, ctx) => {
  if (!ctx.user) throw new Error("must be signed in");
  return ctx.db.list("orders", { filters: { user_id: ctx.user.id } });
};

Inbound webhooks — ctx.request. A function can receive a gateway's webhook: POST to its invoke URL (use a function-scoped key, e.g. …/functions/paymentWebhook?apikey=mgfk_…) and read ctx.request = { method, headers, rawBody, query }. rawBody is the exact bytes — verify the provider's HMAC signature over it (use crypto.subtle on the deno runtime). Your api-key / auth / cookie headers are redacted from ctx.request.headers (use ctx.user for caller identity). (Non-HTTP invokes like cron get ctx.request === null.)

Runtimes (set per function).

• vm (default) — Node vm worker. No network, no imports. Smallest/fastest.
• deno — Deno subprocess. Enables ctx.fetch and dynamic import() of npm:/jsr:/https: modules. Pick it in the Functions tab or via the API.

Outbound HTTP — ctx.fetch. Runs on the server with a per-function egress allowlist (the hosts a function may call). Calls to non-allowlisted hosts, or to private/loopback IPs, are rejected (SSRF-safe). Returns { status, ok, headers, text(), json() }. The global fetch() is blocked — always use ctx.fetch.

Secrets — ctx.secrets. Per-environment secrets (e.g. STRIPE_KEY), encrypted at rest, injected as a frozen object: ctx.secrets.STRIPE_KEY. Manage them in the Functions tab or with mg secrets set. (Requires MASTER_ENCRYPTION_KEY on the server.)

Imports (deno only). Use dynamic import() — const { default: jsPDF } = await import("npm:jspdf"); static import statements aren't supported. Modules are cached server-side.

Per-function config: runtime (vm|deno), timeoutMs (default 5 000 ms, configurable per function up to the server cap — 120 000 ms by default; applies to HTTP invokes and cron runs), memoryMb, and the egress allowlist. Return value is JSON-serialized. Long jobs (fleet syncs, payout generation, reconciliation) should set timeoutMs explicitly; a timeout kills the run and rolls back any open interactive transaction — there is no partial progress, so chunk very large jobs (e.g. per villa) if they can't finish inside the cap.

Scheduled jobs (cron) — semantics. A job runs a function on a schedule (5-field cron like */15 * * * *, aliases like @daily, or intervals like @every 30m), in admin mode (RLS bypassed) — guard sensitive logic inside the function body, not just with RLS. Know the contract:

• Schedules evaluate in the server's local timezone — there is no per-job timezone field (define Dubai-time jobs accordingly, and mind DST if the server TZ has it; UTC servers don't).
• At-most-once, no catch-up: a firing missed while the process is down or busy is skipped, not replayed. Design jobs to be self-healing (each run processes everything currently due, not just "since last run").
• Overlap guard: a still-running job is skipped at the next tick (in-process guard, single-node). "Run now" (dashboard/API) executes immediately and does not consult that guard — an in-flight scheduled run can overlap a manual one.

Example — charge a card via a gateway, then update data atomically:

// runtime: deno · egress allowlist: api.stripe.com · secret: STRIPE_KEY
module.exports = async (input, ctx) => {
  const res = await ctx.fetch("https://api.stripe.com/v1/charges", {
    method: "POST",
    headers: { authorization: "Bearer " + ctx.secrets.STRIPE_KEY },
    body: "amount=" + input.amount + "&currency=aed",
  });
  const charge = await res.json();
  await ctx.db.tx([
    { op: "update", collection: "orders", id: input.orderId, patch: { paid: true, charge_id: charge.id } },
  ]);
  return { ok: res.ok, chargeId: charge.id };
};

Example — receive & verify a gateway webhook (runtime: deno):

// secret: WEBHOOK_SECRET · gateway POSTs to …/functions/paymentWebhook?apikey=mgfk_…
module.exports = async (input, ctx) => {
  const enc = new TextEncoder();
  const key = await crypto.subtle.importKey(
    "raw", enc.encode(ctx.secrets.WEBHOOK_SECRET), { name: "HMAC", hash: "SHA-256" }, false, ["sign"]);
  const mac = await crypto.subtle.sign("HMAC", key, enc.encode(ctx.request.rawBody));
  const hex = [...new Uint8Array(mac)].map((b) => b.toString(16).padStart(2, "0")).join("");
  if (hex !== ctx.request.headers["x-signature"]) throw new Error("bad signature");
  const event = JSON.parse(ctx.request.rawBody);
  await ctx.db.update("orders", event.orderId, { paid: true });
  return { ok: true };
};

5. Direct HTTP (no SDK — e.g. curl from an agent)

Base for tenant requests: {baseUrl}/api/t/{tenant}/{env}. Auth via header x-api-key: <key> or query ?apikey=<key>; end-user token via Authorization: Bearer <token>.

B=https://api.manggaleh.com/api/t/acme/dev

# sign in (returns a token; SDK manages this for you)
curl -s $B/auth/sign-in/email -H 'content-type: application/json' \
  -H 'x-api-key: mgpk_xxx' -d '{"email":"u@x.com","password":"secret"}'

# data — ?count=true adds the exact total for the same filter (numbered-page UIs)
curl -s "$B/data/todos?done=false&order=created_at.desc&limit=20&count=true" -H 'x-api-key: mgpk_xxx' -H "authorization: Bearer $TOKEN"
curl -s -XPOST $B/data/todos -H 'content-type: application/json' -H 'x-api-key: mgsk_xxx' -d '{"title":"hi"}'
curl -s -XPATCH  $B/data/todos/$ID -H 'content-type: application/json' -H 'x-api-key: mgsk_xxx' -d '{"done":true}'
curl -s -XDELETE $B/data/todos/$ID -H 'x-api-key: mgsk_xxx'

# bulk insert (one transaction) & full-collection export (no row cap)
curl -s -XPOST $B/data/todos/bulk -H 'content-type: application/json' -H 'x-api-key: mgsk_xxx' \
  -d '{"rows":[{"title":"a"},{"title":"b"}],"onConflict":"title","onConflictAction":"ignore"}'
curl -s "$B/data/todos/export?format=jsonl" -H 'x-api-key: mgsk_xxx' > todos.jsonl

# invoke a function (service or function-scoped key works without a user)
curl -s -XPOST "$B/functions/topProducts?apikey=mgsk_xxx" -H 'content-type: application/json' -d '{"n":10}'

Responses: data list = { data, nextCursor, count? }; get/insert/update = { data }; function = { result }. Realtime is a WebSocket at {wsBase}/api/t/{tenant}/{env}/realtime?collection=..&token=..&apikey=...

6. API keys & 3rd-party access

To let an external system (payment gateway, vendor) call one of your functions, create a function-scoped key (mg keys create --type function --functions "paymentCallback") — never hand out a service key. The gateway POSTs its payload as the function input.

6b. Limits, quotas & pricing

Platform limits (server defaults; self-hosters can tune the env-var ones):

Plans & quotas (managed service; mg billing --set-plan …):

Every tenant request is metered per environment + category (mg env usage). When quota enforcement is active and a hard-capped plan exceeds its quota, tenant requests return 402 quota_exceeded until the month rolls over or the plan is upgraded. Self-hosted deployments are unmetered by default (enforcement is opt-in via BILLING_ENFORCE).

7. Gotchas (save yourself debugging)

• Realtime carries no row data — only { op, id }. Refetch with data.from(c).get(id), or let data.from(c).live() handle the refetch + optimistic UI + rollback for you.
• Session persistence: by default the token is in-memory (lost on reload). Pass a storage adapter with get()/set() — not localStorage directly (it has getItem/setItem):

  storage: {
    get: () => localStorage.getItem("mg_token"),
    set: (t) => t ? localStorage.setItem("mg_token", t) : localStorage.removeItem("mg_token"),
  }
  

• Auth is per-environment. A user in dev does not exist in prod.
• Inside a function, ctx.db.list returns { data, ... }, not a bare array (unlike the SDK client's .list()).
• embed supports nesting (relation-of-relation) via dotted paths, e.g. embed: ["customer.city(name)"] (max 4 levels). Embedded rows respect RLS too.
• embed does both directions: belongs-to (a FK column → the related row, as an object) and one-to-many (a child collection that FKs back → an array, e.g. embed: ["photos"] on a property). Empty has-many → []. Ambiguous has-many (a child with multiple FKs to the parent) is rejected.
• Email needs a service key (publishable → 403). The log driver only writes to the server log (doesn't actually send).
• Email verification is on-demand — sign-up sends nothing. A new user is signed in immediately with emailVerified=false; nothing is emailed at sign-up. To verify, send one code when the user asks: sendOtp(email, "email-verification") → verifyEmail. See End-user auth → Email verification.
• Auth endpoints are rate-limited per IP (sign-in/sign-up ≈3 req/10s, OTP sends ≈3/min) → abuse returns 429; back off and retry. Request bodies are capped — 2 MiB generally, the storage per-object limit (+1 MiB) on uploads → oversize returns 413 payload_too_large.
• Every response carries x-request-id — include it when reporting an issue; it ties your request to the server logs. Function invocations are recorded per run (time, status, duration, logs) and visible in the dashboard under the function's Runs.
• Column/type discipline: bigint/integer need whole numbers; numeric allows decimals; date = YYYY-MM-DD; timestamptz = ISO. Sending a mismatched value → 400.
• numeric/bigint arrive as strings (exact precision) — don't do + math on them in JS; see Money & exact decimals in §3.
• FK delete semantics are fixed by nullability: a required reference column (nullable: false) → ON DELETE CASCADE (children die with the parent); an optional one → ON DELETE SET NULL. The owner column cascades (deleting an end-user deletes the rows they own); created_by/updated_by are set NULL (their rows survive). There is no RESTRICT option — for financial records where children must not vanish, make the reference optional and enforce in a Function.
• in/nin list size: each item is a bound parameter (no server cap), but the whole URL must fit in ≈16 KiB — roughly 400 UUIDs. Beyond that, split into multiple requests (or rethink with exists).
• list() default 50, max 200. Use page() + cursor for feeds, /export for all rows.
• Identifiers: collections/columns/env names are lowercase + digits + _; project slugs are kebab-case; function names may keep case (camelCase ok).
• Query option names shadow same-named columns. limit, cursor, count, select, embed, order, or, and, exists, nexists are read as options, so a column literally named order can't be filtered through filters. Use the group form, which validates against real columns: list({ and: "order.eq.5" }) → and=(order.eq.5). Worth avoiding those names for new columns.

8. Recipes (agent intent → exact steps)

"Stand up a backend for a new app" → use the CLI bootstrap in §2 (signup → projects create → collections create → keys create → types). Hand the publishable key + tenant + env to the frontend; keep the service key on the server.

"Read/write data as the logged-in user" (frontend) →

const c = createClient({ baseUrl, tenant, env, apiKey: "mgpk_…" });
await c.auth.signIn({ email, password });
await c.data.from("todos").insert({ title: "x", done: false });  // RLS owner set automatically
const mine = await c.data.from("todos").list({ order: "created_at.desc" });

"Run trusted multi-step logic the client must not bypass" → write a Function (§4), then client.functions.invoke("placeOrder", { … }). Put the rules in the function; the client only sends intent.

"Let an external system (payment gateway / vendor) call one function" → mint a function-scoped key and give them the invoke URL — never a service key:

mg keys create --project acme --env prod --type function --functions "paymentCallback"
# they POST their payload to: {baseUrl}/api/t/acme/prod/functions/paymentCallback?apikey=mgfk_…

"Live-update the UI when data changes" → realtime.subscribe(collection, e => …) then refetch the row by e.id (the event has no row data).

"Do an admin/server task (cron, migration, bulk import)" → server-side client with a service key (mgsk_…) → runs as admin, bypasses RLS.

"Send a receipt / notification email" → from a Function: ctx.email.send({ to, subject, html }), or server-side admin.notifications.email.send(...) with a service key.

"Give the SDK full type-safety" → mg types --project … --env … --out src/db.d.ts, then client.data.from<Todo>("todos").

"Monotonic ticket / booking numbers (TKT-000123, WZ-YYYYMMDD-SEQ)" → a counter row plus atomic $inc — the increment happens in the DB under the row lock, so concurrent requests can never mint the same number, and update returns the row with the new value:

// counters: { id, key (unique), value (integer) } — one row per sequence
async function nextNumber(key: string): Promise<number> {
  // Ensure the counter exists (idempotent), then take the next value atomically.
  await counters.insert({ key, value: 0 }, { onConflict: "key" }).catch(() => {});
  const rows = await counters.list({ filters: { key } });
  const row = await counters.update(rows[0].id, { value: { $inc: 1 } });
  return Number(row.value); // e.g. `TKT-${String(n).padStart(6, "0")}`
}
// Per-day sequences (WZ-YYYYMMDD-SEQ): key = `booking-${yyyymmdd}` — one counter row per day.

Guarantees: atomic & gap-free under concurrency within one counter row; numbers are allocated at $inc time, so a request that later fails leaves a gap (like DB sequences do). Run it inside a Function if the number must be minted atomically with other writes.

Decision shortcuts

• Simple CRUD with per-user isolation → collection + owner column + publishable key, no function needed.
• Logic across collections / secrets / must-not-bypass rules → Function.
• 3rd-party access → function-scoped key (never service).
• Your own backend/cron → service key.

9. Self-hosting & license

manggaleh is open source under the MIT license — the same codebase that runs the managed service can be self-hosted: Node.js 20+ and PostgreSQL on a plain Linux host (no Docker required). The repository ships the full deployment toolkit: a VPS runbook (Caddy/nginx + systemd + idempotent deploy script), production environment reference (EMAIL_PROVIDER, MASTER_ENCRYPTION_KEY, storage/S3 config, timeouts), and pg_dump-based backup/restore docs (schema-per-environment: one dump covers every tenant; one schema restores alone). Self-hosted deployments have feature parity with the managed service — quotas/billing enforcement is opt-in, and email/storage providers are pluggable via env config. For data-residency or exit-strategy needs: mg data export (per collection, JSONL/CSV) plus pg_dump give you a complete copy of your data at any time.