Tables & SQL Database

#Defining a Table

Each table is a TypeScript file with a typed interface and a defineTable<T>() call:

import { db } from '@mindstudio-ai/agent';

interface Vendor {
  name: string;
  contactEmail: string;
  status: 'pending' | 'approved' | 'rejected';
  taxId: string;
  paymentTerms?: string;
}

export const Vendors = db.defineTable<Vendor>('vendors');

One file per table, one export per file. The export name is what you reference in mindstudio.json and import in methods.

#Table Options

defineTable<T>() accepts an optional second argument:

export const Users = db.defineTable<User>('users', {
  unique: [['email']],
  defaults: { role: 'member', status: 'active' },
});

• unique — (keyof T & string)[][] — Column groups that form unique constraints. Each entry is a string array of column names. Required for upsert() (see below). The platform creates the actual SQLite UNIQUE indexes during schema sync.
  • Single column: [['email']]
  • Compound: [['userId', 'orgId']]
  • Multiple constraints: [['email'], ['slug']]
• defaults — Partial<T> — Default values applied client-side in push() and upsert() before building the INSERT. Explicit values in the input override defaults.
• database — string — For apps with multiple databases.

#Column Types

#System Columns

Every table gets these automatically. You don't define them; they're added by the platform and maintained by SQLite triggers:

System columns are automatically stripped from write inputs. You don't include them in push() or update() calls.

#The db Namespace

Import from @mindstudio-ai/agent:

import { db } from '@mindstudio-ai/agent';
import { Vendors } from './tables/vendors';

#Creating Records

// Single insert — returns the full row with id, created_at, etc.
const vendor = await Vendors.push({
  name: 'Acme Corp',
  contactEmail: 'billing@acme.com',
  status: 'pending',
  taxId: '12-3456789',
});
// vendor.id is populated

// Batch insert — returns array
const vendors = await Vendors.push([
  { name: 'Acme', status: 'pending', ... },
  { name: 'Globex', status: 'pending', ... },
]);

If the table has defaults configured, missing fields are filled in automatically. Explicit values override defaults.

#Upsert (Insert or Update)

// Insert if no conflict on 0, otherwise update the existing row
const user = await Users.upsert('email', {
  email: 'alice@acme.com',
  name: 'Alice',
  role: 'admin',
});

// Compound conflict key — pass an array
const membership = await Memberships.upsert(['userId', 'orgId'], {
  userId: user.id,
  orgId: org.id,
  role: 'member',
});

upsert(conflictKey, data) generates INSERT ... ON CONFLICT(...) DO UPDATE SET ... using SQLite's excluded. syntax. All non-conflict columns are updated on conflict. If all columns are conflict columns, falls back to DO NOTHING. Returns a Mutation<T> — works with await standalone or inside db.batch().

The conflict key must match a declared unique constraint on the table. Throws MindStudioError with code no_unique_constraint if no match.

#Reading Records

Read methods return lazy Query objects — nothing executes until await. Almost all of them are batchable via db.batch(), including get(), findOne(), and count(); every() and isEmpty() are the exceptions.

// By ID
const vendor = await Vendors.get('uuid-here');  // Vendor | null

// All rows
const allVendors = await Vendors.toArray();

// Find one matching a predicate
const first = await Vendors.findOne(v => v.status === 'approved');

// Filter — returns all matching rows
const approved = await Vendors.filter(v => v.status === 'approved');

// Chainable queries
const results = await Vendors
  .filter(v => v.status === 'approved')
  .sortBy(v => v.name)
  .skip(10)
  .take(5);

// Aggregates — all return Query objects (batchable)
const count = await Vendors.count();
const any = await Vendors.some(v => v.status === 'pending');
const cheapest = await Vendors.min(v => v.totalCents);
const grouped = await Vendors.groupBy(v => v.status);

// These two return Promises directly (not batchable)
const all = await Vendors.every(v => v.status !== 'rejected');
const empty = await Vendors.isEmpty();

#Aggregation

count(), sum(), avg(), countDistinct(), and aggregate() compile to SQL aggregates (COUNT/TOTAL/AVG/GROUP BY). No rows are fetched, so they stay cheap at any table size. Use them for any summary over a table that can grow, rather than paging rows into memory to compute totals.

// Scalar aggregates — take accessors, like sortBy/min/max
const revenue = await Orders.filter(o => o.status === 'paid').sum(o => o.amountCents);
const avgScore = await Answers.avg(a => a.score);            // number | null (null on empty set)
const respondents = await Answers.countDistinct(a => a.responseId);

// Grouped aggregation — string column names, one plain object per group
const top = await Answers
  .filter((a, $) => a.surveyId === $.surveyId, { surveyId }) // bindings: lifts closure var so filter compiles to SQL
  .aggregate({
    by: ['questionId', 'dimension'],
    select: {
      n: { count: true },
      total: { sum: 'score' },
      avgScore: { avg: 'score' },
      respondents: { countDistinct: 'responseId' },
    },
    orderBy: 'total', desc: true, limit: 20,
  });
// Array<{ questionId: string; dimension: string; n: number;
//         total: number; avgScore: number | null; respondents: number }>

Select terms: { count: true }, { sum: 'col' }, { avg: 'col' }, { min: 'col' }, { max: 'col' }, { countDistinct: 'col' }. Omit by to run the aggregates over the whole filtered set in one statement (returns a single object). Empty-set semantics: count/countDistinct → 0, sum → 0, avg/min/max → null; NULL values are skipped, matching SQL.

groupBy() returns a Map of full rows per group, so it fetches everything — use it only when you need the rows themselves, not a summary. min(fn)/max(fn) return the full row with the extreme value; the { min: 'col' }/{ max: 'col' } select terms return just the value.

#Raw SQL Escape Hatch

db.sql() runs read-only raw SQL against the app's own managed database — for joins, subqueries, and window functions the typed API can't express:

const rows = await db.sql<{ questionId: string; n: number }>(
  'SELECT questionId, COUNT(*) AS n FROM answers WHERE surveyId = ? GROUP BY questionId',
  [surveyId],
);

Read-only: the statement must start with SELECT or WITH, and writes throw, so use Table methods for those. Positional ? bind params. Lazy and batchable via db.batch(). Multi-database apps pass { database: 'name' } as the third argument. Treat it as a last resort: prefer the typed API (including aggregate()) whenever it can express the query, since raw rows come back close to how SQLite stores them and may not exactly match the typed API's representations.

#Updating Records

// Update by ID — returns the updated row
const updated = await Vendors.update(vendor.id, {
  status: 'approved',
});
// updated.updated_at is bumped automatically

#Deleting Records

// Delete by ID — returns { deleted: boolean }
const { deleted } = await Vendors.remove(vendor.id);

// Delete all matching a predicate — returns count
const count = await Vendors.removeAll(v => v.status === 'rejected');

// Delete everything — returns count of deleted rows
const cleared = await Vendors.clear();

#Filter Predicates

Predicates look like normal JavaScript arrow functions, but they compile to SQL WHERE clauses:

// Comparisons
Vendors.filter(v => v.status === 'approved')
Vendors.filter(v => v.totalCents > 10000)
Vendors.filter(v => v.totalCents >= 5000 && v.totalCents <= 50000)

// Null checks
Vendors.filter(v => v.paymentTerms !== null)
Vendors.filter(v => v.deletedAt === null)

// Logical operators
Vendors.filter(v => v.status === 'approved' && v.totalCents > 10000)
Vendors.filter(v => v.status === 'approved' || v.status === 'pending')
Vendors.filter(v => !v.isArchived)

// Array membership (IN)
Vendors.filter(v => ['approved', 'pending'].includes(v.status))

// String contains (LIKE)
Vendors.filter(v => v.name.includes('Acme'))

// Nested JSON fields
Vendors.filter(v => v.address.city === 'New York')

// Captured variables work
const minAmount = 10000;
Vendors.filter(v => v.totalCents > minAmount)

If a predicate can't be compiled to SQL (complex closures, function calls), the SDK falls back to filtering in JavaScript. A warning is logged.

#Time Helpers

db.now()                          // current timestamp (unix ms)
db.days(n)                        // n days in ms
db.hours(n)                       // n hours in ms
db.minutes(n)                     // n minutes in ms
db.ago(duration)                  // now - duration
db.fromNow(duration)              // now + duration

// Composable
db.ago(db.days(7) + db.hours(12)) // 7.5 days ago

// Use in queries
Invoices.filter(i => i.dueDate < db.ago(db.days(30)))

#Error Handling on Queries

Both Query<T> and Mutation<T> support .then() and .catch() directly:

const user = await Users.upsert('email', data).catch(err => {
  if (err.code === 'no_unique_constraint') { /* ... */ }
  throw err;
});

Write methods throw MindStudioError with specific codes:

• push() → 'insert_failed' (500) if the insert returns no row
• update() → 'row_not_found' (404) if the ID doesn't exist
• upsert() → 'missing_conflict_key' (400) if the conflict column is missing from the data, 'no_unique_constraint' (400) if no matching unique constraint is declared

#Batch Queries

db.batch() combines multiple operations into a single HTTP round-trip.

• Batchable — all write mutations, db.sql() results, and almost every read method: get(), findOne(), count(), sum(), avg(), countDistinct(), aggregate(), some(), min(), max(), and groupBy().
• Not batchable — every() and isEmpty(), which return Promises directly.

const [vendor, orders, pendingCount] = await db.batch(
  Vendors.get(vendorId),
  PurchaseOrders.filter(po => po.vendorId === vendorId),
  PurchaseOrders.count(po => po.status === 'pending'),
);

#Migrations

#How Schema Changes Work

No migration files. Migrations are automatic:

• New tables — CREATE TABLE applied automatically
• New columns — ALTER TABLE ADD COLUMN applied automatically
• Dropped columns — ALTER TABLE DROP COLUMN applied automatically when a column is removed from the interface
• Dropped tables — DROP TABLE applied automatically when a table file is removed from the manifest
• Type changes and renames — not supported in the automatic migration path

On git push, the platform:

1. Parses your table definition files (TypeScript AST)
2. Diffs against the current live database schema
3. Generates DDL (CREATE TABLE, ALTER TABLE ADD COLUMN, ALTER TABLE DROP COLUMN, DROP TABLE)
4. Applies to a staging copy of the database
5. Promotes the staging copy to live

#In Development

The CLI syncs schema changes to the dev database via POST /dev/manage/sync-schema. Same constraints as production.

#Dev Database Operations

#Reset from Live

Overwrite the dev database with a fresh copy of production data. Preserves database and table IDs (no client reload needed):

POST /_internal/v2/apps/{appId}/dev/manage/reset

#Truncate

Keep the schema, delete all row data. Used by scenarios for a clean canvas before seeding:

POST /_internal/v2/apps/{appId}/dev/manage/reset
Body: { "mode": "truncate" }

Both operations preserve IDs, so the frontend and SDK can continue using existing database references without reloading.

#User Type Handling

Columns of type User (the branded type from the SDK) store values with a @@user@@ prefix in SQLite. The SDK handles this transparently: your code works with clean UUID strings and never sees the prefix.

#Architecture and Scaling

Each app gets its own SQLite database, loaded into memory during active use. S3 is the durability and sync layer, not the query path: reads come from the in-memory working set, and writes go to memory and persist to S3. The working set goes cold on inactivity. This is the same architectural pattern Cloudflare uses for D1, Fly.io uses for LiteFS, Notion uses for per-workspace storage, and Tailscale uses for its control plane — per-tenant SQLite backed by durable object storage.

#Modern SQLite is a production-grade RDBMS

If your mental model of SQLite is "small embedded library suitable only for prototypes," it's out of date:

• WAL mode (enabled by default in Remy) gives concurrent multi-reader / single-writer behavior that fits how most business applications read and write. Single-writer matters less than it sounds, because SQLite's write path is fast: for typical Remy apps (workflow systems, internal tools, dashboards, customer-facing apps), write throughput is bounded by user actions, not by the engine.
• Per-tenant databases routinely run at 1GB+ without operational stress. The SQLite engine supports databases up to 281 TB; the practical ceiling is operational (backup, migration, residency), not engine-side.
• It's a full RDBMS. ACID transactions, a query planner, indexes (including covering and partial indexes), full-text search, JSON1 support, common table expressions, window functions, all of it. The "just a file" framing is a category error: Postgres is also files on a disk, as is every other database. What matters is the engine, and SQLite's has been hardened for decades.
• A serious ecosystem is investing in cloud-scale SQLite. Turso has raised meaningful capital specifically to harden SQLite for multi-tenant cloud deployment. Cloudflare runs D1 on SQLite across their global edge network. Fly.io runs LiteFS for replicated SQLite in production. The "is SQLite production-ready?" question was settled around 2020-2022; the open question now is which deployment pattern fits which workload. Enterprise procurement has adjusted too, and we've seen first-hand that it isn't a blocker for enterprise workloads.

#Practical consequences of per-tenant SQLite + S3 durability

• Per-app isolation is automatic. Each app's data lives in its own file: no shared schema, no cross-tenant queries, no row-level security to misconfigure.
• Scaling is horizontal. More apps means more SQLite files, not one larger central database.
• Backups, residency, and migration are file-level. Each app's .db file is a single, portable artifact. Exporting is a download. Region-specific storage gives region-specific residency.
• Schema sync replaces migration files. When you change a table interface, the platform diffs against the live schema and generates DDL automatically. There are no migration files to write by hand. (See "Migrations" above.)
• The database is portable. Any SQLite client can read it. Migrating off the platform means downloading the file and re-pointing your data-access layer, not running a months-long extraction project.

#When the managed SQLite path isn't the right fit

Some workloads legitimately need a different engine: extreme data volumes, complex cross-table transactions on hundreds-of-GB datasets, a dedicated database tier, cloud-residency mandates the managed path doesn't support. Nothing structural stops you from connecting an external database. Remy-generated app code is standard TypeScript and can call any database client a Node.js app can call.

The managed SQLite-on-S3 path covers most business-application workloads cleanly. The escape hatch is there for the unusual cases, not because SQLite-on-S3 is itself a bottleneck.