# Query with logical fields

> Scope private data, select what you need and keep the database work visible.

- Package: `@db3.ai/app/db`
- Canonical page: [https://db3.ai/docs/queries](https://db3.ai/docs/queries)
- Markdown: [https://db3.ai/docs/queries.md](https://db3.ai/docs/queries.md)
- Framework source of truth: `packages/app/src/db/ActiveQueryBuilder.ts`

<a id="setup"></a>

## 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](https://db3.ai/docs/installation.md)

### Copy the shipped example

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

<a id="run"></a>

## 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.

### Run the lab

```bash
npx tsx examples/runFieldNotes.ts
```

<a id="scope"></a>

## 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.

### examples/runFieldNotes.ts

```typescript
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));
```

<a id="bounds"></a>

## 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.

<a id="filters"></a>

## 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](https://db3.ai/docs/active-record-api.md#query-methods)

<a id="writes"></a>

## 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.

<a id="escape"></a>

## 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](https://db3.ai/docs/active-record-api.md)

<a id="testing"></a>

## 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.

### tests/db/runFieldNotes.test.ts

```typescript
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');
});
```

<a id="run-tests"></a>

## 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.

### Run your copied test and check types

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

<a id="coverage"></a>

## 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](https://db3.ai/docs/guide-workspace-notes.md)
- [Advanced query API](https://db3.ai/docs/active-record-api.md)

## Additional source-backed examples

### examples/FieldNote.ts

```typescript
import { ActiveRecord, type FieldBuilder } from '@db3.ai/app/db';
import { NoteCodeField } from './NoteCodeField';

/** A disposable guide model demonstrating reusable and JSON-backed field values. */
export class FieldNote extends ActiveRecord {
	static override table = 'field_notes';
	static override requestFillable = ['code', 'tags', 'metadata'];

	/** Declares one place for input, validation, storage and public-output policy. */
	static override fields(field: FieldBuilder) {
		return {
			id: field.ulid(),
			owner: field.string({ column: 'owner_id', required: true, index: true }),
			code: new NoteCodeField({ required: true, maxLength: 20 }),
			tags: field.stringList({ maxItems: 3, truncate: false }),
			metadata: field.json<{ client: { name: string }; draft: boolean }>(),
			integration: field.encryptedJson<{ token: string }>({ column: 'integration_secret', selectedByDefault: false }),
		};
	}

	declare id: string | null;
	declare owner: string | null;
	declare code: string | null;
	declare tags: string[];
	declare metadata: { client: { name: string }; draft: boolean } | null;
	declare integration: { token: string } | null;
}
```

### examples/NoteCodeField.ts

```typescript
import { StringField } from '@db3.ai/app/db';

/** Reusable uppercase identifier conversion with normal string validation/storage. */
export class NoteCodeField extends StringField {
	/** Normalizes trusted or request input without duplicating database conversion. */
	protected override parse(input: unknown): string | null {
		return super.parse(input)?.toUpperCase() ?? null;
	}
}
```

## Behavioural verification
Persists custom, list and JSON fields; verifies scoped/projection reads, rejected saves and encrypted storage/output boundaries.
- Behaviour test: `packages/app/src/db/tests/examples/runFieldNotes.test.ts`
- Repository test command (framework checkout only): `npm run test:service --workspace @platform/app -- db --maxWorkers=1`
- In an installed application, use the walkthrough commands instead of this repository test.
- Expected outcome: The guide test passes against the real framework components.
- Environment: Node.js 24 and a disposable MariaDB database. Test account needs CREATE/DROP DATABASE. Synthetic ephemeral encryption key only.

## Related documentation
- [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.
- [ActiveRecord API reference](https://db3.ai/docs/active-record-api.md): Look up record, query and field methods, options and return types. Start with the guide for the normal workflow.
- [Keep one workspace’s notes separate from another’s](https://db3.ai/docs/guide-workspace-notes.md): Run a small database workflow: create a note, protect ownership, list the right records, reject invalid input and roll back a failed write.
- [Save related records together or not at all](https://db3.ai/docs/cookbook-transactions.md): Use the active database transaction without adding database parameters to every model and feature function.

## 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.