Data and records

db reads and writes your app's records, scoped to their owner and stored in the owner's region, with labelled fields for personal data and encrypted sensitive ones.

data: { store: postgres, residency: { default: eu, by-market: { in: in, us: us } } }
db:
  driver: postgres                        # owner default; the db spec's example uses neon
  regions: { us: secret:DB_US, eu: secret:DB_EU, in: secret:DB_IN }
  fields:
    user.email:    { label: personal }
    patient.notes: { label: phi, encrypt: true }

import { auth, db } from "mainsdk";

export async function GET(req: Request) {
  const session = await auth.require(req);
  const orders = await db.orders.for(session.user).list({ limit: 20 });  // no .for(), no compile
  return Response.json(orders);
}

import assert from "node:assert/strict";
import { describe, it } from "node:test";
import { models, s } from "mainsdk";
import { createTestApp } from "@mainsdk/testing";

const { orders } = models({ orders: s.object({ item: s.string(), total: s.int() }) });

describe("orders", () => {
  it("db.AC2 refuses another user's order", async () => {
    createTestApp({ main: "./main.yaml" });
    const order = await orders.for("u1").create({ item: "tea", total: 4900 });

    await assert.rejects(() => orders.for("u2").get(order.id), { code: "MAIN-DATA-0007" });
  });

  it("db.AC6 writes once for a repeated idempotency key", async () => {
    createTestApp({ main: "./main.yaml" });
    const a = await orders.for("u1").create({ item: "tea", total: 4900 }, { idempotencyKey: "k1" });
    const b = await orders.for("u1").create({ item: "tea", total: 4900 }, { idempotencyKey: "k1" });

    assert.equal(b.id, a.id);
  });
});

db:
  driver: postgres

Quick start

Declare each model once at module level with models, then query through .for(user). With no db: block in main.yaml, records go to a local SQLite file, which is fine for development.

import { models, s } from "mainsdk";

export const { orders } = models({
  orders: s.object({ item: s.string(), total: s.int(), status: s.string().optional() }),
});

use mainsdk::prelude::*;
let app = App::test();
Context::new().tenant("tnt_a").user("u1").scope(async {
let orders = app.db().model("orders").for_user("u1");
let doc = orders.create(serde_json::json!({ "total": 5 })).await?;
assert_eq!(orders.get(&doc.id).await?.map(|d| d.owner), Some("u1".into()));
Ok::<_, MainError>(())
}).await?;

import { auth, models, s } from "mainsdk";

const { orders } = models({
  orders: s.object({ item: s.string(), total: s.int(), status: s.string().optional() }),
}); // declared once in lib/orders.ts, shown inline so the example stands alone

export async function GET(req: Request) {
  const session = await auth.require(req);
  const page = await orders.for(session.user).list({ limit: 20 });
  return Response.json(page);
}

A query without .for(user) or .public() does not compile. A user who asks for a record they don't own gets a 403 with code MAIN-DATA-0007 and a fix. Every record has an id, an owner and a createdAt that MainSDK keeps for you.

Calls at a glance

Configuration

db:
  driver: postgres              # sqlite (local only) | postgres | neon | supabase
  regions:                      # one store per region, each a secret reference
    us: secret:DB_US
    eu: secret:DB_EU
    in: secret:DB_IN
  fields:
    patient.name:  { label: personal }
    patient.notes: { label: phi, encrypt: true }   # envelope-encrypted through keys
  models:
    invoice: { trash: 7d, versions: true }
  timeout: { request: 5s }
  slow: 200ms

You can also set labels, encrypt, public, trash, versions and unique on a model in code with model(schema, options); main.yaml adds to the code, it never removes from it.

postgres, neon and supabase need a client: MainSDK names none, so you pass the postgres option to createApp as (url) => ({ pool }) with your own pg or Neon pool. Without it the driver fails with MAIN-DATA-0032.

Regions by market

Region follows the owner. Pass a user as { id, market } or { id, region } and the record goes to that region's store. A market maps to a region by a fixed rule: in to in, European markets to eu, everything else to us. A plain user id uses db.residency. See data and regions.

A data: block in main.yaml with residency.by-market is planned; today the rule above is built in.

What you get

db is how your app reads and writes its records. Every query is scoped to an owner, so one user can never read another's orders by accident. Each record is stored in its owner's region (US, EU or India), and fields that hold personal data carry a label, which lets MainSDK enforce residency, retention and erasure for you. Card numbers can't be stored at all, and sensitive fields are encrypted with keys held in your KMS. Reach for it for any data that belongs to a person or an organisation. You describe a record with a schema from schema, and MainSDK creates the table.

Examples

List a user's orders, with a filter and a cursor

where narrows the list inside the owner's scope. A cursor works only for the query that made it, so pass the same where, order and limit with it.

import { auth, models, s } from "mainsdk";

const { orders } = models({
  orders: s.object({ item: s.string(), total: s.int(), status: s.string().optional() }),
}); // declared once in lib/orders.ts, shown inline so the example stands alone

export async function GET(req: Request) {
  const session = await auth.require(req);
  const url = new URL(req.url);

  const page = await orders.for(session.user).list({
    where: { status: "shipped" },          // also { total: { gte: 3 } } or { status: { in: ["a", "b"] } }
    order: { by: "total", dir: "desc" },
    limit: 20,
    cursor: url.searchParams.get("cursor") ?? undefined,
  });
  return Response.json(page);              // { items, next? }
}

Create an order exactly once

Pass an idempotency key. A repeated key returns the first result and writes nothing a second time, so a retried form post or a flaky network never creates two orders.

import { auth, models, s } from "mainsdk";

const { orders } = models({
  orders: s.object({ item: s.string(), total: s.int(), status: s.string().optional() }),
}); // declared once in lib/orders.ts, shown inline so the example stands alone

export async function POST(req: Request) {
  const session = await auth.require(req);
  const cart = (await req.json()) as { item: string; total: number };
  const idempotencyKey = req.headers.get("idempotency-key") ?? crypto.randomUUID();

  const order = await orders.for(session.user).create(
    { item: cart.item, total: cart.total, status: "pending" },
    { idempotencyKey },
  );
  return Response.json(order, { status: 201 });
}

update takes the same option:

import { auth, models, s } from "mainsdk";

const { orders } = models({
  orders: s.object({ item: s.string(), total: s.int(), status: s.string().optional() }),
}); // declared once in lib/orders.ts, shown inline so the example stands alone

export async function POST(req: Request, { params }: { params: Promise<{ id: string }> }) {
  const { id } = await params;
  const session = await auth.require(req);
  const key = req.headers.get("idempotency-key") ?? undefined;
  return Response.json(
    await orders.for(session.user).update(id, { status: "cancelled" }, { idempotencyKey: key }),
  );
}

Records in the owner's region

You don't pick a store in code. Describe where the owner is, and the record goes to the store for that region; an India user's order lands in in and an EU user's order in eu.

import { models, s } from "mainsdk";

const { orders } = models({
  orders: s.object({ item: s.string(), total: s.int(), status: s.string().optional() }),
}); // declared once in lib/orders.ts, shown inline so the example stands alone

export function createOrder(user: { id: string; market: string }, item: string, total: number) {
  return orders.for({ id: user.id, market: user.market }).create({ item, total });
}

A statement that joins tables across regions fails mainsdk check with MAIN-DATA-0013, and a transaction can't span two stores. Analytics may combine regions only as totals. Moving a user to another region as an audited job is planned.

Delete, restore and version a record

Set trash and a deleted record waits that long before it is gone. Set versions and every update says which version it saw.

import { model, models, s } from "mainsdk";

export const { invoices } = models({
  invoices: model(s.object({ total: s.int() }), { trash: "7d", versions: true }),
});

export async function adjust(user: string, id: string) {
  const mine = invoices.for(user);
  const inv = await mine.create({ total: 5 });                       // inv.version is 1
  await mine.update(inv.id, { total: 6 }, { ifVersion: inv.version }); // a stale version throws MAIN-DATA-0060 (409)
  await mine.delete(inv.id, { ifVersion: "any" });                   // "any" is last write wins
  return mine.restore(inv.id);                                       // while the trash period lasts
}

trashed().list() lists the caller's deleted records, and upsert(rows, { key }) inserts rows or replaces the owner's row with the same key in one transaction. To run several calls as one unit, wrap them in db.transaction(async () => { ... }); it commits when the function returns and rolls back when it throws. The full contract is spec db-writes.

Labelled and encrypted fields

Label every field that holds personal data. Add encrypt: true and the field is stored as ciphertext with a wrapped data key; the owner reads plain text. Encrypted fields need the keys interface to be set up.

db:
  fields:
    patient.name:  { label: personal }
    patient.notes: { label: phi, encrypt: true }

import { auth, models, s } from "mainsdk";

const { patient } = models({ patient: s.object({ name: s.string(), notes: s.string() }) });

export async function POST(req: Request, { params }: { params: Promise<{ id: string }> }) {
  const { id } = await params;
  const session = await auth.require(req);
  const { notes } = (await req.json()) as { notes: string };

  await patient.for(session.user).update(id, { notes });        // stored encrypted
  const record = await patient.for(session.user).get(id);
  return Response.json({ notes: record.notes });                // plain text for the owner
}

Public records

A model declared with public: true can be read without an owner. Writes still go through an owner.

import { model, models, s } from "mainsdk";

export const { products } = models({
  products: model(s.object({ name: s.string(), price: s.int() }), { public: true }),
});

export async function catalogue() {
  return products.public().list();   // { items, next? }
}

Queries that stay fast

Every statement is timed. A request's statements get a timeout (5 seconds by default, 30 at most) set on the server with SET LOCAL statement_timeout, so a runaway query is cancelled instead of holding a connection; a job gets 60 seconds. A client that disconnects cancels its running query. At most db.pool.max transactions run on a store at once, and one that waits longer than db.pool.acquire fails with MAIN-DATA-0052 and Retry-After: 1; while anyone waits, the db shows as degraded in health.

db:
  timeout: { request: 5s, job: 60s }
  pool: { max: 10, acquire: 2s }
  slow: 200ms        # log a query slower than this, once a minute per query shape
  repeat: 10         # log one query shape run more than this in one request: the N+1 pattern

A slow query is logged as MAIN-DATA-0053 with its text without values, a fingerprint and the calling line. A handler that loads a list and then one record per item is logged as MAIN-DATA-0054 with a fix that suggests where: { id: { in: ids } }. mainsdk info --json shows the limits and the ten slowest fingerprints.

import { expectQueries } from "@mainsdk/testing";

declare const handler: (req: Request) => Promise<Response>;
const request = new Request("http://localhost/api/orders");

// Fails with MAIN-DATA-0054 when one query shape runs more than ten times in the handler.
await expectQueries(() => handler(request), { repeat: 10 });

mainsdk check reports a loop over a query's results that awaits another query (MAIN-DATA-0055), invalid limits (MAIN-DATA-0056) and, on a serverless target, a Postgres URL that isn't a pooled endpoint (MAIN-DATA-0051).

Migrations that don't block

MainSDK doesn't replace Prisma, Drizzle or your SQL files. mainsdk check reads the migrations that are new since the base branch (db.migrations.base, origin/develop by default) and reports statements that would block your running app: CREATE INDEX without CONCURRENTLY, an added NOT NULL column without a default, a type change, a foreign key without NOT VALID (MAIN-DATA-0040), a rename or a drop of something the base branch still reads (MAIN-DATA-0041), a missing SET lock_timeout for tools that can't take one (MAIN-DATA-0042), and a whole-table UPDATE or DELETE (MAIN-DATA-0043). Each finding names the safe form.

mainsdk db migrate --plan   # show what would run, per region store
mainsdk db migrate          # run it under a lock, with a lock timeout and retries

mainsdk db migrate runs your tool once per region store under an advisory lock, with a lock timeout and retries from db.migrations. A statement that can't get its lock after the last retry stops the run with MAIN-DATA-0044; a run that waited for another instance and found nothing left to do says so with MAIN-DATA-0045. It prints no connection string or password.

db:
  migrations:
    tool: prisma           # prisma | drizzle | sql
    lock-timeout: 5s
    retries: 5
    allow:                 # an exception needs a reason and an expiry
      - { file: prisma/migrations/20261027_seed/migration.sql, rule: MAIN-DATA-0043, reason: "12 rows", expires: 2026-12-31 }

Pages

Every list call returns a page: { items, next }. There is no next on the last page. Pass next back as cursor to get the next page. Cursors are keyset based, so a page costs the same however deep it is, and they are bound to the query, the tenant and the user. limit is at most 100.

To walk every item, use paginate:

import { models, paginate, s } from "mainsdk";

const { orders } = models({
  orders: s.object({ item: s.string(), total: s.int(), status: s.string().optional() }),
}); // declared once in lib/orders.ts, shown inline so the example stands alone

export async function allOrders(user: string) {
  return paginate((o) => orders.for(user).list(o), { limit: 50 }).all();   // or: for await (const order of paginate(...))
}

A bad, foreign or expired cursor fails with MAIN-PAGE-0001; a limit below 1 fails with MAIN-PAGE-0002.

Errors and checks

{
  "type": "https://mainsdk.dev/errors/MAIN-DATA-0007",
  "title": "This record belongs to another owner",
  "status": 403,
  "code": "MAIN-DATA-0007",
  "fix": "Read and write records through db.<model>.for(user) with the user who owns them",
  "retryable": false
}

Error shapes are in errors.

Swapping drivers

Change one line. Your model calls, labels, regions and idempotency stay the same.

db:
  driver: neon   # was postgres

SQLite is for local development only; it throws MAIN-DATA-0030 when the app runs with env: prod, and mainsdk check fails it too. db.sql() returns the migration statements for every declared model, and db.migrate() creates the tables now (SQLite creates them on first use).

Testing

createTestApp gives you an in-memory SQLite store.

import assert from "node:assert/strict";
import { describe, it } from "node:test";
import { models, s } from "mainsdk";
import { createTestApp } from "@mainsdk/testing";

const { orders } = models({ orders: s.object({ item: s.string(), total: s.int() }) });

describe("orders", () => {
  it("db.AC2 refuses another user's order", async () => {
    createTestApp({ main: "./main.yaml" });
    const order = await orders.for("u1").create({ item: "tea", total: 4900 });

    await assert.rejects(() => orders.for("u2").get(order.id), { code: "MAIN-DATA-0007" });
  });

  it("db.AC6 writes once for a repeated idempotency key", async () => {
    createTestApp({ main: "./main.yaml" });
    const a = await orders.for("u1").create({ item: "tea", total: 4900 }, { idempotencyKey: "k1" });
    const b = await orders.for("u1").create({ item: "tea", total: 4900 }, { idempotencyKey: "k1" });

    assert.equal(b.id, a.id);
  });
});

Security and privacy

• No query runs without an owner scope, and the type system enforces it.
• Each record lives in its owner's region. Cross-region joins fail the check.
• Personal fields carry a canonical label, which drives retention and erasure in privacy. Card numbers are never stored.
• Encrypted fields use envelope encryption with the owner's region key. With per-user keys, erasing a person also makes their backed-up rows unreadable.
• Countries beyond US, EU and India get residency vaults and tokenisation in phase 3 (planned).

FAQ

Do I have to rewrite my schema? No. db adopts an existing Postgres schema, and your Prisma or Drizzle migrations keep working. See bring an existing app.

Which region does a record go to before I know the user's market? The one in db.residency, which defaults to us. Moving the record later as an audited job is planned.

Can I still run my own SQL? db.raw() is planned and is not built yet. Until then, use your own client for statements the typed calls can't express; mainsdk check refuses cross-region joins in any SQL string it finds.

How do backups work? Through the backup interface and restore drills, covered on its own page.

Reference

These are the lower-level pieces behind db. Most apps never call them, but they're exported and they explain what MainSDK writes to your database.

The outbox

tx.jobs.run, tx.events.publish and tx.webhooks.send (and tx.webhooks.emit) called inside db.transaction write a row to the mainsdk_outbox table in the same commit as your data. A rollback leaves nothing behind, and a commit never loses the work.

After the commit, the instance relays its rows within db.outbox.relay (1 second by default). A sweep every db.outbox.sweep (1 minute by default) relays rows that no relay took, so a crash between commit and relay delays the work and never loses it. The row id is the idempotency key of the relayed call, so a relay that runs twice has one effect. Relayed rows are kept for db.outbox.keep, 7 days by default. A row that fails its sixth try is marked dead, logged as MAIN-DATA-0064 and put in the job dead-letter queue.

import { db, models, s } from "mainsdk";

const { orders } = models({ orders: s.object({ status: s.string() }) });

export async function ship(user: string, orderId: string) {
  await db.transaction(async (tx) => {
    await orders.for(user).update(orderId, { status: "shipped" });
    await tx.events.publish("order.shipped", { orderId });   // relayed after the commit, once
  });
}

Next

• Encryption keys: how encrypted fields are protected.
• Privacy and data rights: retention, export and erasure per label.
• Permissions: share records beyond their owner.