Store an integration secret safely

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

npx tsx examples/runFieldNotes.ts

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

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

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

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