3. Modeling rules for products

For people building a product of their own, not a social network or a marketplace: the premise is that the data splits by customer, team or region. What is marked measured was run against the binary. The code for each rule is in the example project.

Rule 1 — tenantId on every table and every index, from the start

Why: the day a customer needs to move to another server or the day to delete a customer's data, the separation is already in the data. Later, separating requires migrating tables and functions.

// convex/schema.ts
orders: defineTable({
  tenantId: v.id("companies"),
  customer: v.string(),
  amountCents: v.number(),
  status: v.union(v.literal("open"), v.literal("paid"), v.literal("shipped"), v.literal("cancelled")),
})
  .index("by_tenant", ["tenantId"])
  .index("by_tenant_status", ["tenantId", "status"]),

• tenantId is the first field of every index. Without an index that starts with it, the query becomes a scan of the whole table (and an unindexed filter reads every row, which counts towards the read limit; see 04).
• Join tables also carry tenantId (in the example, events).
• Every function derives tenantId from the logged-in user, never from the argument. The example does this in currentMember(ctx), which reads ctx.auth → members table → tenantId. A tenantId coming from the client is a security hole. A customer of one company can never ask for another's order: orderOfCompany returns ORDER_NOT_FOUND for an order from another tenant (tested in test.mjs).
• Check on reads by id. ctx.db.get(id) does not look at the tenant: after the get, compare doc.tenantId with the user's.
• Do not use one server per company. An idle notusbase takes about 90 MB. Many companies call for tenantId in the same database.
• A single-user system goes without tenantId. There it only adds a table and a parameter. The rule comes back before the second user.

Measured: tenantId does not cost reactivity. With a subscriber of tenant A and writes from tenant B, A's query did not re-execute a single time (5 writes from B → 0 re-executions), both with an index that starts with tenantId and with an unindexed filter. The reactivity range is precise. A's own write re-executed once in both cases. So the index rule is for read performance, not for reactivity.

Leak warning in notusbase dev

Forgetting the tenant filter in a query is the worst bug of a multi-customer system, and it raises no error: the screen just shows someone else's data. In notusbase dev (and only there), when a public query or mutation returns, from the same table, documents with more than one value of the customer field, the run gets a line in the function log:

[WARN] possible cross-tenant leak: orders:all returned, with a login, documents of table "orders" with 2 different values of tenantId. If the function returns several customers on purpose, list it in tenant.allow in notusbase.json

• Where it shows: in notusbase dev, in the call's response, in notusbase logs and in the dashboard's functions screen. The line carries the function name, the table and how many values showed up, never the values.
• What it checks: any document in the result, at any depth, that has _id and the field: a list, a paginate page and a document inside an object or another document. A document without the field (or with null) does not count.
• What it does not check: internal functions (internal*), actions, and different tables (customer A's order with customer B's record, in separate tables, passes). A function that returns a few documents of a single customer does not warn: the warning points at the bug that shows up in the sample, it does not prove the filter is right.
• Production: nothing runs. The check only exists under notusbase dev; notusbase serve does not load it.
• Configuration, in the project's notusbase.json (goes into git):

{ "tenant": { "field": "orgId", "allow": ["admin:*", "reports:allCustomers"], "off": false } }

• field: name of the customer field. Default tenantId. A system without a customer field (a single user) gets no warning at all, because no table has the field.
• allow: public functions that return several customers on purpose (admin screens). Exact name (admin:all) or a prefix with * at the end (admin:*). internal* functions already do not count and need not be listed.
• off: true turns the warning off.

Rule 2 — each transaction inside one group

Today a group lives in one database, and a mutation is one SQLite transaction. The day groups live on separate servers, a transaction does not cross two databases. So:

• A mutation writes to only one tenant. Whoever needs to touch two tenants (a transfer between companies, an invite that creates a user in one and a row in the other) does two mutations and links them with a job.
• What crosses groups becomes an idempotent job. ctx.scheduler.runAfter(0, internal.x.step2, { key, ... }) inside step 1's mutation: the job joins the same transaction, so either both exist or neither does. Step 2 records an idempotency key (e.g. the event id) and, if it already exists, does nothing. A scheduled mutation runs exactly once; a scheduled action runs at most once and, if the server dies midway, is left as failed (see 02). So step 2 should be a mutation, and only call an action for external effects.
• Counters and totals live in the same table/transaction as what they count (e.g. the example's summary sums by by_tenant_status; for large volumes, keep an aggregate updated in the same mutation).
• Mind the write limit: 16,000 documents per mutation, counting scheduled jobs (04).

Rule 3 — files and logs outside the main database

A large database makes backup, checking and restore slower (checking needs free space equal to the database).

• Files: use ctx.storage (generateUploadUrl for direct client upload, getUrl for reading). The body goes to pb_data/storage/_storage, outside the main SQLite; only the metadata stays in a _storage row. For a large file do not pass it through the function: ctx.storage.store(blob), in an action or mutation, loads the whole file into the isolate's memory (see 04). The ceiling is NOTUSBASE_UPLOAD_MB (1,024 by default); the proxy must match.
• File backup: the local backup (NOTUSBASE_BACKUP) copies each file into the chain when it is written and never deletes the copy, so a restore --until from before a file was deleted still brings it back. With the S3 upload (NOTUSBASE_S3_BUCKET) files go to the bucket too.
• Store only the reference in the document: receipt: v.optional(v.id("_storage")). Delete the file in the same mutation that removes the row (ctx.storage.delete); the file leaves the disk after the commit.
• Logs: console.* goes to pb_data/notusbase.log, outside SQLite, with a ceiling on size and days: old lines disappear. Do not write an audit trail to the main database at high volume. If you need audit data that lasts, use a table with cron cleanup (the example's events + cleanOldEvents) or export it.
• Logs are outside the continuous backup: they do not come back on a restore.
• Heavy analyses and reports should not run on the database that serves the product (next section).

Rule 4 — reports and reads in SQL

A long SQL query straight on the live database holds up the checkpoint and competes for CPU and disk with the product. That is why product code has no SQL route: there is no ctx.sql. For operators, notusbase sql and the dashboard console read with row and time ceilings, one statement at a time, and reject writes. A v.id field stores table:id and the row's id has no prefix, so join through notusbase_id, which both know: JOIN teams te ON te.id = notusbase_id(t.tenantId). Plain sqlite3 on a restored copy does not know it; there, te.id = substr(t.tenantId, length('teams:') + 1). What works for reports:

1. Report on a restored copy. Turn on NOTUSBASE_BACKUP=/folder, and when you need it:

   notusbase restore /backup/folder /new/folder
   sqlite3 /new/folder/data.db "select tenantId, count(*) from orders group by 1"

   Measured: the restored copy opens in plain sqlite3 and the columns have the schema's names (item(tenantId, n); documents in real columns, not in a JSON blob). Open it with plain sqlite3; -readonly and file:...?mode=ro failed on the restored WAL. The copy is as of the last WAL chunk: good for a report, not for a real-time decision. Use --until <RFC 3339> for a point in time. Never write to or checkpoint the live database.

2. Derived data inside NotusBase. If the screen needs a total, keep it in a table updated by the mutation (reactive, typed and within the read limit). SQL has no reactivity.

3. Export. notusbase export produces the npx convex export zip, with one folder per table (documents.jsonl), useful for an external ETL.

4. Only required string and number become plain columns. v.optional, v.union, v.literal, objects and arrays become JSON columns; in SQL they show up as JSON text (measured in the example: status stores "paid" with the quotes, and receipt stores "_storage:..."). Use json_extract(status, '$') or compare with '"paid"'. An index on such a field is an expression index with notusbase_key(...) (nested_orders_by_tenant_status), which plain sqlite3 does not know: select tenantId, json_extract(status,'$'), count(*), sum(amountCents) from orders group by 1,2 ran without error on the example's database (server stopped), but a plan that depends on that index may fail. A field that was born optional stays a JSON column even after it becomes required, so the quotes stay too, and json_extract on an empty value gives malformed JSON. Changing the schema breaks the report without warning: version the SQL together with the code.

5. Live integrity_check only through notusbase check --dir, because plain sqlite3 does not know notusbase_key.

Rule 5 — access and roles

• A members table (tenantId, subject, role, index by_subject) links the identity to the tenant. ctx.auth.getUserIdentity().subject is the record id in users; do not trust email (it comes from the PocketBase client and signup may be open).
• Signup. In production it is closed, on every start, and NOTUSBASE_SIGNUP=1 opens it, if users is in NOTUSBASE_AUTH_COLLECTIONS. With it open, anyone becomes an identity. Without a row in members, that identity sees nothing (the example returns NO_COMPANY). For invite-based signup, keep it closed and create the user with notusbase user add <email> or through the dashboard.
• Internal functions (internalQuery, internalMutation, internalAction) for everything the client does not call; the public API is only what the client needs.
• ConvexError with a code ({ code: "NO_PERMISSION" }) for errors the client handles; any other error becomes [Request ID: …] Server Error.
• Webhooks (http.ts): check a secret in the header with process.env.X (notusbase env set). A new route lives at the root; do not use a path that collides with /api/ or /_/.

Rule 6 — queries that do not blow the limit

• Every table that grows is read through withIndex, with take(n) or paginate.
• Never collect() on a table without a ceiling. The read ceiling goes into the SQL LIMIT, so it does not fill memory, but the function fails with Too many documents read in a single function execution (limit: 32000).
• Never filter without an index on a large table: the WHERE scans, and every row it looks at counts as a read, kept or not. The function fails at 32,000 rows or at the time limit, whichever comes first.
• A routine that deletes or changes a lot (cleanup, data migration) uses chained batches, one mutation per batch (04).
• A subscription that changes often and is read by many people (a global feed) concentrates writes and re-execution in the same database: outside the premise; evaluate before building.
• A searchIndex lists tenantId (and whatever else narrows the search) in filterFields, and the search uses eq on it. A search subscription runs again whenever a write touches a row that passes its eq filters, so without the filter every write to the table re-runs every open search. On an M4, re-running a search over 20,000 short rows takes from 0.38 ms (a word in 3% of the rows) to 3.8 ms (a word in a quarter of them); with the filter on one tenant out of ten, the common case drops to 1.6 ms.
• The index is written in the same transaction as the row. An insert or an edit of the searched field costs about 20 µs more with 8 words and 80 µs more with 200 words when many write at once (two to four times the write without the index), and about 47 µs and 143 µs more for a lone caller, whose commit carries one row. An edit that leaves the field alone costs nothing extra. Long text that nobody searches stays out of searchField.
• A deploy that creates a searchIndex, or changes its searchField, reads the whole table while writes wait: about 0.1 s for 100,000 short rows, 1.4 s for a million, 2.3 s for 100,000 rows of 200 words on an M4. notusbase deploy --plan gives the rows and an estimate at the rate measured on an M4 (a slower machine takes longer); on a big table, deploy at a quiet hour. The rebuild is one transaction, so the whole index lands in the WAL at once: a spike for the backup and the replica. If that deploy then fails, the schema goes back and the index is rebuilt again, so writes wait twice; on an Enforced table, a column the failed schema dropped comes back empty.
• A deploy that adds an index to a table with rows builds it when it swaps the code, in one SQLite statement, and meanwhile writes and subscription updates wait (reads do not). There is no background build as in Convex. On an M4: about 0.6 s for a million short rows, 7 s for a million rows of 1 KB, 35 s for 10 million short rows, 90 s for 10 million of 1 KB; the time follows the bytes of the table, and per row it grows with the table. notusbase deploy --plan gives the rows and an estimate at that rate, and past 5 s warns that writes stop that long: deploy the index at a quiet hour, on its own, before the code that uses it. The whole index lands in the WAL at once, like a rebuilt searchIndex.

Rule 7 — changing the schema with data

<a id="changing-the-schema-with-data"></a>

The deploy validates the documents that already exist against the new schema and refuses when they do not fit. With test-only data, delete pb_data. With data that matters, every change goes through the same three moves: loosen the schema, migrate the documents, tighten the schema. Each move is one deploy, and the system keeps running between them. The Convex component @convex-dev/migrations, from npm and unchanged, does the middle one in batches, with a dry run.

All three cases below were run on 2026-11-09 on a project with 12 tasks, under notusbase dev, which deploys on every saved file, with the migrations module that came before the component; the deploy messages are the same. On a server, each step ends with notusbase deploy.

Before the first migration, install the component once (npm install @convex-dev/migrations):

// convex/convex.config.ts
import { defineApp } from "convex/server";
import migrations from "@convex-dev/migrations/convex.config";
const app = defineApp();
app.use(migrations);
export default app;

// convex/migrations.ts
import { Migrations } from "@convex-dev/migrations";
import { components } from "./_generated/api";
import type { DataModel } from "./_generated/dataModel";

export const migrations = new Migrations<DataModel>(components.migrations);
export const run = migrations.runner();

A new required field

Going straight to priority: v.number() is refused:

Deploy refused: document tasks:... does not satisfy the new schema. Fix or delete the document, or make the field optional.

1. Add the field as optional, priority: v.optional(v.number()), and make the code that creates documents write it. Deploy.

2. Fill the old documents:

   export const fillPriority = migrations.define({
     table: "tasks",
     migrateOne: (ctx, task) => {
       if (task.priority === undefined) return { priority: 1 };
     },
   });

   notusbase run migrations:run '{"fn": "migrations:fillPriority", "dryRun": true}'
   notusbase run migrations:run '{"fn": "migrations:fillPriority"}'

   The dry run runs one batch and undoes it, and the log shows up to 10 { before, after } pairs. The real run goes on in the background, batch by batch; the same command again answers with its state. These commands, on the same migration, run in notusbase/app/components/migrations/test.mjs.

3. Make it required, priority: v.number(). Deploy.

Renaming a field

text becomes title. The old field has to be emptied before it can leave the schema, and a required field cannot be emptied, so both are optional in the middle.

1. In the schema, text: v.optional(v.string()) and title: v.optional(v.string()). The code writes title and reads task.title ?? task.text. Deploy.

2. Move the value:

   export const renameText = migrations.define({
     table: "tasks",
     migrateOne: (ctx, task) => {
       if (task.text !== undefined) return { title: task.text, text: undefined };
     },
   });

   notusbase run migrations:run '{"fn": "migrations:renameText"}'

3. In the schema, title: v.string() and no text; the code reads only title. Delete renameText in the same change: it mentions task.text, which no longer exists in the types, and tsc stops the deploy with TS2339: Property 'text' does not exist. Deploy:

   plan: column outside schema.ts: tasks.text, empty; it disappears from the documents and stays in the table

Removing a field

Taking a field out of the schema while documents still hold a value is refused, and this one the plan does show:

plan: column outside schema.ts: tasks.priority, with a value in 12 documents (refusal)
Error: the deploy would be refused: see the refusal lines above. Nothing was deployed

1. Make the field optional and stop writing it. Deploy.
2. Empty it, with a migration whose migrateOne returns { priority: undefined } for each document that still has a value. Run it.
3. Remove the field from the schema, from every index that lists it, and delete the migration. Deploy.

What stays behind

• The column is never dropped. It stays in the table, empty, and costs nothing to the functions. notusbase sql "select * from tasks" still shows it.

• A field that was born optional is a JSON column forever, even after it becomes required. Every later deploy repeats one line about it, which is not an error:

  plan: type changed, the column stays as it is: tasks.priority is json in the database and number in the code

  The functions see the right type. Only SQL reports see the difference (rule 4).

• A used migration is deleted from the code once it has run everywhere. Its row in the component's table stays as history.

• Migrations run in the background, batch by batch (100 documents by default), each batch in one transaction. The server may restart in the middle: the next batch is a scheduled mutation, and it continues from the stored cursor.

• Going back is deploying the previous code, and the steps above are what make that safe: at every point the old and the new code both fit the documents.

One-screen summary