Take a note from input to stored data
Choose fields, protect ownership, persist the model and return its public shape.
On this page
Source-backed MarkdownStart with the working notes lab
Complete the Workspace notes installation and test-only SQL setup first. If starting from the full app shell, use the starter’s model/migration workflow instead; do not put disposable database setup into server startup.
Copying all database examples also copies the expected output used by the tests. Run from the same app directory with Node 24 and the Installation development dependencies.
mkdir -p examples
cp -R node_modules/@db3.ai/app/src/db/examples/. examples/Run before changing it
The lab creates a workspace-owned note, excludes another workspace’s note, rejects invalid input, rolls back a two-note failure and deletes its test data. The owned database is removed in finally.
Keep that baseline passing while adding a field. A successful TypeScript build alone does not prove storage conversion or ownership.
npx tsx examples/runWorkspaceNotes.tsDeclare the product data
The model uses a ULID, trusted workspace ownership, required title, body and creation timestamp. Database names belong to fields: createdAt maps to created_at, while normal application code uses createdAt.
Use a specific field when it already owns your conversion. Do not add a JSON.parse(), date cleanup or lowercase helper to every route. For genuinely reusable behavior, implement a small custom field.
import { ActiveRecord, type FieldBuilder } from '@db3.ai/app/db';
/** A workspace-owned note used by the ActiveRecord guide and executable recipe. */
export class KnowledgeNote extends ActiveRecord {
static override table = 'knowledge_notes';
static override requestFillable = ['title', 'body'];
/**
* Defines storage, validation and transport behaviour for each logical field.
*
* @param field - Framework field factory.
* @returns The note's schema; workspace ownership is assigned by trusted code.
*/
static override fields(field: FieldBuilder) {
return {
id: field.ulid(),
workspace: field.string({ column: 'workspace_id', required: true, maxLength: 26, index: true }),
title: field.string({ required: true, maxLength: 120 }),
body: field.text(),
createdAt: field.timestamp({ column: 'created_at', auto: 'create' }),
};
}
declare id: string | null;
declare workspace: string | null;
declare title: string | null;
declare body: string | null;
declare createdAt: Date | null;
}
Separate user input from trusted ownership
The function fills only allowed title/body values, then assigns the already-authorized workspace. new Model() and Model.create() are in-memory operations; save() persists.
Scope read, edit and delete by both the trusted workspace and record identity. Request validation and fillable lists do not establish membership or permission. The full starter’s authentication/HTTP tests cover that separate boundary.
import { KnowledgeNote } from './KnowledgeNote';
/**
* Saves allowed request fields under a workspace already authorized by the host.
*
* @param workspaceId - Trusted workspace identity, never taken from the payload.
* @param input - Untrusted form data; only title and body are fillable.
* @returns The persisted note, or rejects with RecordValidationError.
*/
export async function createWorkspaceNote(workspaceId: string, input: unknown): Promise<KnowledgeNote> {
const note = new KnowledgeNote();
note.setFromRequest(input);
note.assign({ workspace: workspaceId });
await note.save();
return note;
}
/**
* Reads a bounded list using logical model fields, not database column names.
*
* @param workspaceId - Workspace the caller is already allowed to read.
* @returns Up to twenty notes, newest first with a stable primary-key tie-break.
*/
export async function listWorkspaceNotes(workspaceId: string): Promise<KnowledgeNote[]> {
return KnowledgeNote.where('workspace', workspaceId)
.orderBy('createdAt', 'desc')
.orderBy('id', 'desc')
.limit(20)
.all();
}
/**
* Updates a note only when it belongs to the caller's authorized workspace.
*
* @param workspaceId - Trusted workspace identity.
* @param noteId - Note identity to look up within that workspace.
* @param input - Untrusted editable fields; omitted values remain unchanged.
* @returns The saved record; a missing or cross-workspace note is not found.
*/
export async function updateWorkspaceNote(workspaceId: string, noteId: string, input: unknown): Promise<KnowledgeNote> {
const note = await KnowledgeNote.where({ workspace: workspaceId, id: noteId }).firstOrFail();
note.setFromRequest(input);
await note.save();
return note;
}
/**
* Permanently deletes a note within an already authorized workspace.
*
* @param workspaceId - Trusted workspace identity.
* @param noteId - Note identity scoped to that workspace.
* @returns The number of deleted rows; throws when the scoped record is absent.
*/
export async function deleteWorkspaceNote(workspaceId: string, noteId: string): Promise<number> {
const note = await KnowledgeNote.where({ workspace: workspaceId, id: noteId }).firstOrFail();
return note.delete();
}
Ship the schema with the feature
Register the model in your application’s model list, generate a migration, review it, apply it and run schema check. Commit the model, migration, snapshot and behavior test together.
The lab uses Database.install() only because its database is disposable. For an existing app, use an additive migration and consider old data before making a new field required.
Testing
Create a tests/db directory and save the test below as workspaceNotes.test.ts. It imports the example you copied into examples/. Keep the same folder layout so the relative import resolves.
Run from the application root with the development dependencies from Installation. These are consumer tests, not commands that assume a framework checkout. This database lab needs the same test-only SQL credentials when run through Vitest.
import { readFileSync } from 'node:fs';
import { afterAll, beforeAll, describe, expect, it } from 'vitest';
import { RecordNotFoundError, RecordValidationError } from '@db3.ai/app/db';
import { createGeneratedTestDatabase, type GeneratedTestDatabase } from '@db3.ai/app/db/test/db';
import { App } from '@db3.ai/app/server';
import { KnowledgeNote } from '../../examples/KnowledgeNote';
import { createWorkspaceNote, deleteWorkspaceNote, listWorkspaceNotes, updateWorkspaceNote } from '../../examples/workspaceNotes';
import { runWorkspaceNotes } from '../../examples/runWorkspaceNotes';
import { createNotesTogether } from '../../examples/createNotesTogether';
describe('ActiveRecord documentation examples', () => {
it('runs the complete disposable guide and matches its published output', async () => {
const expected = JSON.parse(readFileSync(new URL('../../examples/outputs/workspace-notes.json', import.meta.url), 'utf8'));
expect(await runWorkspaceNotes()).toEqual(expected);
});
});
describe('workspace note behaviour through public framework imports', () => {
let database: GeneratedTestDatabase;
let application: App;
const workspace = '01ARZ3NDEKTSV4RRFFQ69G5FAV';
const otherWorkspace = '01ARZ3NDEKTSV4RRFFQ69G5FAW';
beforeAll(async () => {
database = await createGeneratedTestDatabase('notes_examples');
application = new App({ db: database.db });
await application.db.install(KnowledgeNote);
});
afterAll(async () => {
await application?.close();
await database?.destroy();
});
it('separates in-memory creation from persistence', async () => {
const note = KnowledgeNote.create({ workspace, title: 'Unsaved note' });
expect(note.id).toMatch(/^[0-9A-HJKMNP-TV-Z]{26}$/);
expect(note.isPersisted()).toBe(false);
expect(await KnowledgeNote.findByPk(note.id)).toBeNull();
await note.save();
expect(note.isPersisted()).toBe(true);
expect(await KnowledgeNote.findByPk(note.id)).toBeInstanceOf(KnowledgeNote);
});
it('guards workspace/id, queries logical fields and serializes dates', async () => {
const note = await createWorkspaceNote(workspace, { id: 'ignored', workspace: otherWorkspace, title: ' Protected note ' });
expect(note.workspace).toBe(workspace);
expect(note.title).toBe('Protected note');
expect(note.id).not.toBe('ignored');
expect((await listWorkspaceNotes(workspace)).some(row => row.id === note.id)).toBe(true);
expect((await listWorkspaceNotes(otherWorkspace)).some(row => row.id === note.id)).toBe(false);
const row = await KnowledgeNote.where({ workspace, id: note.id }).firstOrFail();
expect(row.createdAt).toBeInstanceOf(Date);
expect(typeof row.toJSON().createdAt).toBe('string');
});
it('rejects blank and oversized titles without inserting rows', async () => {
const before = await KnowledgeNote.where('workspace', workspace).count();
await expect(createWorkspaceNote(workspace, { title: ' ' })).rejects.toBeInstanceOf(RecordValidationError);
await expect(createWorkspaceNote(workspace, { title: 'x'.repeat(121) })).rejects.toBeInstanceOf(RecordValidationError);
expect(await KnowledgeNote.where('workspace', workspace).count()).toBe(before);
});
it('scopes edits/deletes and preserves omitted fields during updates', async () => {
const note = await createWorkspaceNote(workspace, { title: 'Edit me', body: 'Keep me' });
await expect(updateWorkspaceNote(otherWorkspace, note.id!, { title: 'Forbidden' })).rejects.toBeInstanceOf(RecordNotFoundError);
await expect(deleteWorkspaceNote(otherWorkspace, note.id!)).rejects.toBeInstanceOf(RecordNotFoundError);
const edited = await updateWorkspaceNote(workspace, note.id!, { title: 'Edited', workspace: otherWorkspace });
expect(edited.body).toBe('Keep me');
expect(edited.workspace).toBe(workspace);
await expect(deleteWorkspaceNote(workspace, note.id!)).resolves.toBe(1);
expect(await KnowledgeNote.findByPk(note.id)).toBeNull();
});
it('rolls back an earlier write when a later record fails validation', async () => {
const before = await KnowledgeNote.where('workspace', workspace).count();
await expect(createNotesTogether(workspace, [{ title: 'First' }, { title: '' }])).rejects.toBeInstanceOf(RecordValidationError);
expect(await KnowledgeNote.where('workspace', workspace).count()).toBe(before);
});
});
Extend the behavior test
The copied tests prove in-memory versus persisted creation, validation, ownership, edits/deletes, date output and rollback using real SQL. Add the new field to input, reread it and assert its display output.
Try a value that should fail and prove no row was inserted or changed. Keep an explicit second workspace in the test so a successful happy path does not hide an authorization leak.
npx vitest run tests/db/workspaceNotes.test.ts
npx tsc --noEmit --target ES2022 --module ESNext --moduleResolution Bundler --types node --skipLibCheck examples/*.tsWhat this recipe proves
This is the end-to-end model workflow using the existing tested notes lab. It does not claim a complete multi-tenant membership system or a migration deployment.
Fields, Queries, Migrations and the authenticated starter expand the named boundaries; use their focused tests as you add those capabilities.
Runs the note workflow against a real disposable SQL database, including validation, workspace isolation, updates, deletion and transaction rollback.
6 tests pass; the walkthrough matches its checked-in JSON output.packages/app/src/db/tests/examples/workspaceNotes.test.tsThis test command requires the framework repository. Use the walkthrough commands in an installed application.
Environment: Node.js; MariaDB/MySQL test account with CREATE/DROP DATABASE permission; package-owned test settings. No application database is used.