# Store an integration secret safely

> Persist an encrypted model value, load it only in authorized server code and keep it out of public JSON.

- Package: `@db3.ai/app/db`
- Canonical page: [https://db3.ai/docs/cookbook-secrets](https://db3.ai/docs/cookbook-secrets)
- Markdown: [https://db3.ai/docs/cookbook-secrets.md](https://db3.ai/docs/cookbook-secrets.md)
- Framework source of truth: `packages/app/src/db/fields/EncryptedJsonField.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="field"></a>

## Declare a hidden, opt-in field

The model declares `integration` with `encryptedJson()` and `selectedByDefault: false`. It stores versioned ciphertext in integration_secret, not JSON that can be queried.

The request fillable list excludes it. Assign a credential only from a trusted integration workflow; never offer arbitrary access to decrypted data.

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

## Authorize, select, use and forget

The runner assigns a synthetic token, saves it and proves that the ordinary read does not load it. A scoped `withField("integration")` read recovers it while `toJSON()` still omits it.

Only the lab uses a fresh ephemeral key and low-level SQL inspection. Persistent applications must retain one securely provisioned APP_KEY across API/worker processes, backups and restarts. Do not print secrets to prove storage worked; compare in a test and return a boolean.

- [Persistent keys and integrity failures](https://db3.ai/docs/security.md)

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

## Protect unreadable data

A wrong key or changed table/column context makes decryption fail. Do not treat failure as a blank secret or overwrite the existing ciphertext. Preserve it while diagnosing configuration and key restore.

The field automatically authenticates table/column, not row ownership. Query authorization still matters. Renaming a table/column with existing encrypted data requires a deliberate decrypt/re-encrypt plan, not only a schema rename.

<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

The SQL test proves ciphertext storage, authorized selection, default exclusion, request protection and hidden public output. The Security lab separately proves tampering/context/wrong-key failure and original-key recovery.

Key rotation, external key management, searchable encryption and an integration settings UI remain application work.

- [Security lab](https://db3.ai/docs/security.md)
- [Encrypted field contract](https://db3.ai/docs/fields-api.md#encrypted-json)

## Additional source-backed examples

### 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.
- [Encrypt a secret you need to read later](https://db3.ai/docs/security.md): Keep reversible secrets encrypted with an application-owned key and explicit ownership context.
- [Query with logical fields](https://db3.ai/docs/queries.md): Scope private data, select what you need and keep the database work visible.

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