# Take a note from input to stored data > Choose fields, protect ownership, persist the model and return its public shape. - Package: `@db3.ai/app/db` - Canonical page: [https://db3.ai/docs/guide-model-data](https://db3.ai/docs/guide-model-data) - Markdown: [https://db3.ai/docs/guide-model-data.md](https://db3.ai/docs/guide-model-data.md) - Framework source of truth: `packages/app/src/db/README.md` ## Start 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. - [Workspace notes setup](https://db3.ai/docs/guide-workspace-notes.md#configure) - [Full Notes starter](https://db3.ai/docs/starter-app.md) ### Copy the shipped example ```bash 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. ### Run the lab ```bash npx tsx examples/runWorkspaceNotes.ts ``` ## Declare 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. - [Reusable fields and nested JSON](https://db3.ai/docs/fields.md) ### examples/KnowledgeNote.ts ```typescript 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. - [Authenticated API workflow](https://db3.ai/docs/guide-api.md) ### examples/workspaceNotes.ts ```typescript 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 { 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 { 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 { 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 { 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. - [Run a safe schema change](https://db3.ai/docs/migrations.md) ## 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. ### tests/db/workspaceNotes.test.ts ```typescript 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. ### Run your copied test and check types ```bash npx vitest run tests/db/workspaceNotes.test.ts npx tsc --noEmit --target ES2022 --module ESNext --moduleResolution Bundler --types node --skipLibCheck examples/*.ts ``` ## What 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. - [Fields](https://db3.ai/docs/fields.md) - [Queries](https://db3.ai/docs/queries.md) - [Migrations](https://db3.ai/docs/migrations.md) ## Additional source-backed examples ### examples/createNotesTogether.ts ```typescript import { ActiveRecord } from '@db3.ai/app/db'; import { app } from '@db3.ai/app/server'; import { createWorkspaceNote } from './workspaceNotes'; import type { KnowledgeNote } from './KnowledgeNote'; /** * Saves a group of notes atomically using the active application's database. * * Records are constructed inside the transaction scope. An error rolls back * all writes; existing records bound to another connection are not rebound. * * @param workspaceId - Workspace the host has authorized for creation. * @param inputs - Note inputs; each goes through the same filling and validation. * @returns Persisted notes after the transaction commits successfully. */ export async function createNotesTogether(workspaceId: string, inputs: unknown[]): Promise { return app().db.knex.transaction(transaction => ActiveRecord.withDb(transaction, async () => { const notes: KnowledgeNote[] = []; for (const input of inputs) { notes.push(await createWorkspaceNote(workspaceId, input)); } return notes; })); } ``` ## Behavioural verification Runs the note workflow against a real disposable SQL database, including validation, workspace isolation, updates, deletion and transaction rollback. - Behaviour test: `packages/app/src/db/tests/examples/workspaceNotes.test.ts` - Repository test command (framework checkout only): `npm test --workspace @platform/app -- src/db/tests/examples/workspaceNotes.test.ts --maxWorkers=1` - In an installed application, use the walkthrough commands instead of this repository test. - Expected outcome: 6 tests pass; the walkthrough matches its checked-in JSON output. - Environment: Node.js; MariaDB/MySQL test account with CREATE/DROP DATABASE permission; package-owned test settings. No application database is used. ## Related documentation - [ActiveRecord](https://db3.ai/docs/active-record.md): Define your fields once. Create, validate, query and save records without repeating database conversion in every endpoint. - [Keep conversion in the field](https://db3.ai/docs/fields.md): Define a reusable value once, from input and validation through storage and public output. - [Query with logical fields](https://db3.ai/docs/queries.md): Scope private data, select what you need and keep the database work visible. - [Change the schema without losing the data](https://db3.ai/docs/migrations.md): Generate a reviewable migration, apply it and check that models, files and the database agree. - [Build an owned-note JSON API](https://db3.ai/docs/guide-api.md): Keep HTTP validation, field conversion and authorization at their own boundaries. Use the starter’s real note routes as the example. ## Guidance for AI tools Use the documented public import `@db3.ai/app/db` and its exported types. Prefer the source-backed examples and behavioural outcomes above over invented APIs or source-relative internal imports.