Query with logical fields

Prepare the disposable SQL lab

Complete Installation and its local MariaDB test-account setup. Use Node.js 24 and a test account allowed to create/drop db3_app_test_* databases. Copy the shipped database examples from your independent app directory.

Set your test-only DB_CONNECTION=mariadb, DB_HOST, DB_PORT, DB_USER and DB_PASSWORD in .env. DATABASE_URL overrides these connection values: unset it if it points elsewhere. This lab creates and removes its own database, not your app database.

• Installation and local MariaDB

mkdir -p examples
cp -R node_modules/@db3.ai/app/src/db/examples/. examples/

Run the field workflow

Expect code BRIEF-1, tags ["SEO", "Agency"], decoded nested client metadata and scopedCount: 1. Every protection/result flag is true, including countUnchanged after an invalid save.

No plaintext or ciphertext is printed. The lab owns its temporary database and key and cleans up in finally. A forcibly killed process can leave a generated test database; inspect its exact name before removing it.

npx tsx examples/runFieldNotes.ts

Start with authorized ownership

Use FieldNote.where({ owner, id }).firstOrFail() when the caller must own the note. The logical owner field maps to owner_id; do not duplicate that mapping in normal queries.

first() returns a record or null. firstOrFail() throws RecordNotFoundError. A missing record and a different owner should normally have the same public response. wherePk() alone does not enforce ownership.

import { pathToFileURL } from 'node:url';
import { RecordValidationError } from '@db3.ai/app/db';
import { createGeneratedTestDatabase } from '@db3.ai/app/db/test/db';
import { Security } from '@db3.ai/app/security';
import { App } from '@db3.ai/app/server';
import { FieldNote } from './FieldNote';

/**
 * Verifies field conversion and encrypted storage in an isolated SQL database.
 *
 * Raw SQL below is deliberately limited to checking physical storage; normal
 * application reads use logical model fields. Only outcome booleans are printed
 * for secret values. The generated database and ephemeral key are disposable.
 *
 * @returns The observed conversion, output, query and validation outcomes.
 */
export async function runFieldNotes() {
	const database = await createGeneratedTestDatabase('field_notes');
	const application = new App({ db: database.db, config: { security: { key: Security.generateKey() } } });
	try {
		await application.db.install(FieldNote);
		const note = new FieldNote();
		note.setFromRequest({ code: '  brief-1  ', tags: [' SEO ', 'seo', 'Agency'], metadata: '{"client":{"name":"Ada"},"draft":true}', owner: 'wrong-owner', integration: { token: 'untrusted' } });
		note.assign({ owner: 'ada', integration: { token: 'synthetic-server-secret' } });
		await note.save();
		await FieldNote.create({ owner: 'grace', code: 'other' }).save();
		const found = await FieldNote.where({ owner: 'ada', id: note.id }).firstOrFail();
		const selected = await FieldNote.query().withField('integration').where({ owner: 'ada', id: note.id }).firstOrFail();
		const physical = await application.db.knex(FieldNote.table).where({ id: note.id }).first();
		const projected = await FieldNote.where('owner', 'ada').select('code').limit(1).all();
		const countBefore = await FieldNote.where('owner', 'ada').count();
		let invalidRejected = false;
		try { await FieldNote.create({ owner: 'ada', code: ' ', tags: ['one', 'two', 'three', 'four'] }).save(); } catch (error) { if (!(error instanceof RecordValidationError)) throw error; invalidRejected = true; }
		return {
			code: found.code, tags: found.tags, metadata: found.metadata,
			ownerProtected: found.owner === 'ada',
			ciphertextStored: String(physical.integration_secret).startsWith('security:1:aes-256-gcm:') && !String(physical.integration_secret).includes('synthetic-server-secret'),
			omittedByDefault: found.integration === null,
			decryptedOnRequest: selected.integration?.token === 'synthetic-server-secret',
			hiddenFromJson: !Object.hasOwn(selected.toJSON(), 'integration'),
			projectedCode: projected[0]?.code,
			scopedCount: countBefore,
			invalidRejected,
			countUnchanged: await FieldNote.where('owner', 'ada').count() === countBefore,
		};
	} finally {
		try { await application.close(); } finally { await database.destroy(); }
	}
}

if (process.argv[1] && import.meta.url === pathToFileURL(process.argv[1]).href) console.log(JSON.stringify(await runFieldNotes(), null, 2));

Bound reads and aggregate in SQL

all() hydrates every matching row. Add limit() and a deterministic orderBy() before collecting lists. For ordinary paging use limit()/offset() and a stable tie-break such as id; deep offset paging may need a database-specific strategy.

count() runs an aggregate without hydrating every record. select("code") requests logical fields, while the primary key is retained for model identity. An unselected property may look null/default: that does not prove the stored value is empty. Do not treat partial records as complete DTOs.

Filters, hidden selects and relationships

Use where(), whereIn(), whereNull() and whereNotNull() for field-aware filtering. withField() deliberately adds a normally excluded field; it does not override hidden JSON output.

with() eagerly loads declared link relations to avoid repeated lookups when you already know the relations needed. It is not permission to traverse an unbounded graph. Select bounded data and inspect query counts when adding loops.

Soft-delete models exclude trashed records by default. withTrashed(), onlyTrashed(), restore() and forceDelete() change that behavior. Force deletion is destructive; design permissions and retention before using it.

• Every query signature

Bulk writes are a deliberate choice

patch() converts and validates dirty fields for a bulk update, but does not hydrate each row and run an entire per-record business workflow. Select only trusted writable keys and include an ownership predicate.

delete() and forceDelete() can affect all matching records. An unscoped builder is not made safe by ActiveRecord. Prefer a scoped record workflow until a bulk operation is justified and tested.

Use SQL when SQL is the right tool

toKnex(), whereColumn() and whereRaw() are database-level escape hatches. At that boundary you own physical column names, parameter binding, result hydration and authorization. Never interpolate user input into raw SQL.

The lab reads one raw row solely to prove ciphertext is stored. Normal model data stays on the field-aware API. For joined read shapes, use ActiveProjection to reuse field conversion instead of manual JSON/date cleanup.

whereVectorSimilarTo() is a specialist dialect-aware query, not an embedding generation service. Verify actual database/version/index support before relying on it.

• Projection and query APIs

Testing

Create a tests/db directory and save the test below as runFieldNotes.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 { expect, it } from 'vitest';
import { runFieldNotes } from '../../examples/runFieldNotes';

it('persists field-owned values, scopes reads and hides deliberately selected encrypted data', async () => {
	const output = await runFieldNotes();
	expect(output).toEqual({ code: 'BRIEF-1', tags: ['SEO', 'Agency'], metadata: { client: { name: 'Ada' }, draft: true }, ownerProtected: true, ciphertextStored: true, omittedByDefault: true, decryptedOnRequest: true, hiddenFromJson: true, projectedCode: 'BRIEF-1', scopedCount: 1, invalidRejected: true, countUnchanged: true });
	expect(JSON.stringify(output)).not.toContain('synthetic-server-secret');
});

Run and extend the SQL test

Run with the same disposable test-only SQL credentials. This test uses real fields, model queries, database storage and encryption. It asserts a failed save leaves the count unchanged.

Add a different input and expected result to the example/test together. Do not replace the database with a mock or silently skip it when verifying a release.

npx vitest run tests/db/runFieldNotes.test.ts
npx tsc --noEmit --target ES2022 --module ESNext --moduleResolution Bundler --types node --skipLibCheck examples/*.ts

Coverage and limits

The shared SQL lab proves logical filters, owner scope, selected fields, default/excluded secret loading and aggregate counts. The Workspace notes lab also proves bounded stable ordering and cross-workspace edit/delete denial.

Eager loading, soft-delete recovery, bulk writes, joins and vector similarity are explained/reference-linked, not claimed as demonstrated by this lab. Their existing service tests remain separate evidence.

• Scoped notes workflow
• Advanced query API