Storing Secrets in the Database

import { createCipheriv, createDecipheriv, randomBytes } from 'crypto';

const ALGORITHM = 'aes-256-gcm';
const IV_LENGTH = 16;
const AUTH_TAG_LENGTH = 16;

function getMasterKey(): Buffer {
  const key = process.env.SETTINGS_ENCRYPTION_KEY;
  if (!key) throw new Error('SETTINGS_ENCRYPTION_KEY environment variable is required');
  // Key must be 32 bytes (256 bits). Use a hex-encoded string in the env var.
  return Buffer.from(key, 'hex');
}

export function encrypt(plainText: string): string {
  const iv = randomBytes(IV_LENGTH);
  const cipher = createCipheriv(ALGORITHM, getMasterKey(), iv);
  const encrypted = Buffer.concat([cipher.update(plainText, 'utf8'), cipher.final()]);
  const authTag = cipher.getAuthTag();
  // Store as: iv:authTag:ciphertext (all hex-encoded)
  return [
    iv.toString('hex'),
    authTag.toString('hex'),
    encrypted.toString('hex'),
  ].join(':');
}

export function decrypt(stored: string): string {
  const [ivHex, authTagHex, encryptedHex] = stored.split(':');
  const iv = Buffer.from(ivHex, 'hex');
  const authTag = Buffer.from(authTagHex, 'hex');
  const encrypted = Buffer.from(encryptedHex, 'hex');
  const decipher = createDecipheriv(ALGORITHM, getMasterKey(), iv);
  decipher.setAuthTag(authTag);
  return decipher.update(encrypted) + decipher.final('utf8');
}

# Run once, store the output in your deployment platform's env vars
node -e "console.log(require('crypto').randomBytes(32).toString('hex'))"
# Output: e.g. a1b2c3d4e5f6...  (64 hex characters = 32 bytes = 256 bits)

# Add to your environment:
SETTINGS_ENCRYPTION_KEY=a1b2c3d4e5f6...

CREATE TABLE IF NOT EXISTS app_settings (
  key           VARCHAR(255) PRIMARY KEY,
  value         TEXT NOT NULL,
  is_secret     BOOLEAN NOT NULL DEFAULT false,
  description   TEXT,
  created_at    TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at    TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- Index for fast lookups (primary key already covers this, but explicit for clarity)
-- The primary key on "key" is sufficient for single-row lookups.

COMMENT ON TABLE app_settings IS 'Application settings and encrypted secrets';
COMMENT ON COLUMN app_settings.value IS 'Plain text for non-secrets, AES-256-GCM encrypted for secrets';
COMMENT ON COLUMN app_settings.is_secret IS 'Controls UI masking. Encrypted values should always have this set to true';

import { Column, CreateDateColumn, Entity, PrimaryColumn, UpdateDateColumn } from 'typeorm';

@Entity('app_settings')
export class AppSetting {
  @PrimaryColumn({ type: 'varchar', length: 255 })
  key!: string;

  @Column({ type: 'text' })
  value!: string;

  @Column({ name: 'is_secret', type: 'boolean', default: false })
  isSecret!: boolean;

  @Column({ type: 'text', nullable: true })
  description?: string;

  @CreateDateColumn({ name: 'created_at' })
  createdAt!: Date;

  @UpdateDateColumn({ name: 'updated_at' })
  updatedAt!: Date;
}

import { encrypt, decrypt } from './crypto';

const CACHE_TTL_MS = 60_000; // 60 seconds

interface CacheEntry {
  value: string | null;
  expiresAt: number;
}

const cache = new Map<string, CacheEntry>();

/**
 * Retrieve a setting value by key.
 * Resolution order: cache → database → environment variable fallback.
 */
async function getSetting(key: string): Promise<string | null> {
  const now = Date.now();

  const cached = cache.get(key);
  if (cached && cached.expiresAt > now) {
    return cached.value;
  }

  try {
    const repo = await getSettingsRepository();
    const row = await repo.findOne({ where: { key } });

    if (row?.value) {
      const plainValue = row.isSecret ? decrypt(row.value) : row.value;
      cache.set(key, { value: plainValue, expiresAt: now + CACHE_TTL_MS });
      return plainValue;
    }
  } catch {
    // Database unavailable — fall through to env var
  }

  // Fallback: environment variable (useful for local dev)
  const envVal = process.env[key] ?? null;
  if (envVal) {
    cache.set(key, { value: envVal, expiresAt: now + CACHE_TTL_MS });
  }
  return envVal;
}

/**
 * Store or update a setting. Secret values are encrypted before storage.
 */
async function setSetting(
  key: string,
  value: string,
  options?: { isSecret?: boolean; description?: string }
): Promise<void> {
  const repo = await getSettingsRepository();
  const storedValue = options?.isSecret ? encrypt(value) : value;

  await repo.upsert(
    {
      key,
      value: storedValue,
      isSecret: options?.isSecret ?? false,
      description: options?.description,
    },
    ['key']
  );

  // Invalidate cache so next read picks up the new value
  cache.delete(key);
}

/**
 * List all settings. Secret values remain encrypted (caller must mask for UI).
 */
async function listSettings() {
  const repo = await getSettingsRepository();
  return repo.find({ order: { key: 'ASC' } });
}

// Helper to get the TypeORM repository
async function getSettingsRepository() {
  const { getDataSource } = await import('./db/init');
  const { AppSetting } = await import('./db/entities/AppSetting');
  const ds = getDataSource();
  return ds.getRepository(AppSetting);
}

import { NextResponse } from 'next/server';
import { auth } from '@/lib/auth';
import { listSettings, setSetting } from '@/lib/settings';
import { UserRole } from '@/lib/db/entities/User';

// GET /api/settings — List all settings (secrets are masked)
async function GET() {
  const session = await auth();
  if (!session?.user || session.user.role !== UserRole.ADMIN) {
    return NextResponse.json({ error: 'Unauthorized' }, { status: 403 });
  }

  const settings = await listSettings();
  const masked = settings.map((s) => ({
    ...s,
    value: s.isSecret && s.value
      ? s.value.slice(0, 8) + '••••••••' + s.value.slice(-4)
      : s.value,
  }));

  return NextResponse.json({ data: masked });
}

// POST /api/settings — Create or update a setting
async function POST(request: Request) {
  const session = await auth();
  if (!session?.user || session.user.role !== UserRole.ADMIN) {
    return NextResponse.json({ error: 'Unauthorized' }, { status: 403 });
  }

  const body = await request.json();
  // Validate with Zod in production (omitted here for clarity)
  const { key, value, isSecret, description } = body;

  if (!key || !value) {
    return NextResponse.json({ error: 'key and value are required' }, { status: 400 });
  }

  await setSetting(key, value, { isSecret, description });
  return NextResponse.json({ data: { key, saved: true } });
}

import { getSetting } from '@/lib/settings';

async function callOpenRouter(prompt: string) {
  const apiKey = await getSetting('OPENROUTER_API_KEY');
  if (!apiKey) {
    throw new Error('OPENROUTER_API_KEY is not configured');
  }

  const response = await fetch('https://openrouter.ai/api/v1/chat/completions', {
    method: 'POST',
    headers: {
      'Authorization': `Bearer ${apiKey}`,
      'Content-Type': 'application/json',
    },
    body: JSON.stringify({
      model: 'anthropic/claude-sonnet-4',
      messages: [{ role: 'user', content: prompt }],
    }),
  });

  return response.json();
}

# Local dev fallback — never committed to git
OPENROUTER_API_KEY=sk-or-v1-abc123...

const stripeKey = await getSetting('STRIPE_SECRET_KEY');

STEP 1: CREATE THE ENCRYPTION MODULE
  Create lib/crypto.ts with encrypt() and decrypt() functions.
  Use AES-256-GCM with random IVs.
  Master key comes from SETTINGS_ENCRYPTION_KEY env var.
  Store as iv:authTag:ciphertext (hex-encoded, colon-separated).
  Generate a key: node -e "console.log(require('crypto').randomBytes(32).toString('hex'))"

STEP 2: CREATE THE DATABASE TABLE/ENTITY
  For TypeORM: Create entities/AppSetting.ts
    - key (VARCHAR 255, primary key)
    - value (TEXT, stores encrypted or plain text)
    - isSecret (BOOLEAN, default false)
    - description (TEXT, nullable)
    - createdAt, updatedAt (timestamps)
  Register the entity in your TypeORM config.
  Generate and run a migration.

STEP 3: CREATE THE SETTINGS LIBRARY
  Create lib/settings.ts with three functions:
    - getSetting(key): cache → database (decrypt if secret) → process.env fallback
    - setSetting(key, value, options): encrypt if secret → upsert → invalidate cache
    - listSettings(): return all rows (values stay encrypted for secrets)
  Use a Map<string, CacheEntry> with 60-second TTL.

STEP 4: CREATE THE API ROUTES
  Create app/api/settings/route.ts:
    GET  — requires ADMIN role, returns all settings with secrets masked
    POST — requires ADMIN role, accepts { key, value, isSecret, description }
  Mask format: first 8 chars + "••••••••" + last 4 chars
  Validate input with Zod schema.

STEP 5: CREATE THE ADMIN UI (OPTIONAL)
  Build a settings management page (e.g., /god/settings):
    - Table listing all settings
    - Add/edit form with "is_secret" checkbox
    - Secret values shown masked
    - Only accessible to admin users

STEP 6: ADD THE ENCRYPTION KEY TO YOUR ENVIRONMENT
  Generate: node -e "console.log(require('crypto').randomBytes(32).toString('hex'))"
  Add SETTINGS_ENCRYPTION_KEY to:
    - Your deployment platform's environment/secrets
    - Your .env file for local development
  This is the ONE env var you must manage through your platform.

STEP 7: MIGRATE EXISTING ENV VARS (OPTIONAL)
  For each API key you want to move from env vars to the database:
    1. Call setSetting('KEY_NAME', process.env.KEY_NAME, { isSecret: true })
    2. Update consuming code to use: await getSetting('KEY_NAME')
    3. The env var fallback means both approaches work during migration

ADAPTATION CHECKLIST
  □ crypto.ts created with AES-256-GCM encrypt/decrypt
  □ AppSetting entity created and registered
  □ Migration generated and run
  □ settings.ts created with getSetting/setSetting/listSettings
  □ API routes created with ADMIN-only access
  □ SETTINGS_ENCRYPTION_KEY generated and added to environment
  □ Zod validation on API inputs
  □ Secret values masked in all API responses and UI
  □ .env fallback works for local development
  □ ORM query logging disabled or filtered to exclude app_settings values