# Keep conversion in the field

> Define a reusable value once, from input and validation through storage and public output.

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

<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="model"></a>

## The field is the reusable unit

A record groups fields. Each field owns how its value enters the app, validates, becomes a database value and comes back as application or display data. Keep this close to the database rather than inventing a second query language.

The example combines a custom code, normalized tags, nested JSON and an encrypted integration field. `owner` maps to `owner_id` in storage; application code still uses the logical name.

- [Why this is field-first ActiveRecord](https://db3.ai/docs/active-record.md)

### 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;
}
```

<a id="reuse"></a>

## Add one reusable conversion

`NoteCodeField` extends `StringField` and changes only `parse()`. It inherits trimming, required/max-length validation, varchar schema and display behavior. Reuse it by placing a new instance in another model’s field map.

A field definition is shared metadata; per-record values belong to field state. Do not put a current user, connection or mutable request data on a reusable definition. Query conversion is a separate hook: if input normalization must also apply to query values, implement and test `getQueryValue()`.

### 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;
	}
}
```

<a id="lifecycle"></a>

## Input, app, database and display values

The main types are `FieldType<TValue, TDbValue, TDisplayValue, TInput, TState>`. `setValue()`/`parse()` handle input, `setFromDb()`/`fromDbValue()` handle storage reads, and `getDataForDb()`/`toDbValue()` handle writes.

`toJSON()` is display/transport output, not an unrestricted dump of in-memory state. Use `toAppData()` only on the trusted server when you need application values. Hidden fields and excluded selects are different controls.

For ordinary model operations, assign logical values and let fields run the lifecycle. Use the low-level hooks when authoring a field, not to repeat conversion in every route.

- [FieldType and concrete fields](https://db3.ai/docs/fields-api.md)

<a id="request"></a>

## Keep request policy on the model

`requestFillable` allows code, tags and metadata. The payload cannot set owner or integration; trusted application code assigns them separately. `assign()` is for trusted values, while `setFromRequest()` applies request policy.

`save()` validates field state before writing. This lab attempts a blank code and four tags with `truncate: false`; it receives `RecordValidationError` and inserts nothing. Return selected field/code/message details rather than reflecting submitted secrets.

### 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="json"></a>

## Nested data and specialized JSON

`json<T>()` accepts decoded JSON or a JSON string and preserves nested shapes. It is permissive: the generic does not validate client.name at runtime, and it does not turn nested objects into model relations.

`stringList()` trims and deduplicates case-insensitively. `maxItems` truncates by default; use `truncate: false` when excess entries should produce validation errors.

Use a specialized field when several models need the same conversion/rules. Nested document support is not a promise of a complete document mapper or automatic deep request validation.

<a id="secrets"></a>

## Password hashing is not encryption

`password()` hashes on save, hides its public value and verifies rather than decrypting. `encryptedJson()` stores reversible authenticated ciphertext and always hides display output.

Our integration opts out of default selection. Server code must explicitly select it with `withField()` and authorize the owner before using it. Randomized encryption cannot be searched with equality queries.

- [Encrypted model recipe](https://db3.ai/docs/cookbook-secrets.md)
- [Password authentication](https://db3.ai/docs/auth.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 next steps

Taught/tested: custom string input conversion, validation, logical column mapping, normalized lists, nested JSON round trip, request ownership, encrypted storage and hidden output. Concrete numeric, choice, timestamp, link, password and vector options are in the reference and their owning tests.

This lab does not prove vector-index support, generic embedding generation, a nested schema validator or all custom-field lifecycle hooks. Use focused tests for the field you add, including input/query/storage/display differences.

- [Full Fields API](https://db3.ai/docs/fields-api.md)
- [Queries](https://db3.ai/docs/queries.md)
- [Commit a schema change](https://db3.ai/docs/migrations.md)

## 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
- [Fields API reference](https://db3.ai/docs/fields-api.md): Current emitted signatures and options for @db3.ai/app/db.
- [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.
- [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.
- [Store an integration secret safely](https://db3.ai/docs/cookbook-secrets.md): Persist an encrypted model value, load it only in authorized server code and keep it out of public JSON.

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