D1 ストレージ API パターン

:LiTarget: 用途

Cloudflare D1 Database を Key-Value 風 API で抽象化するパターン。GET/PUT/DELETE 統一。

:LiSparkle: 特徴

• Key-Value API
• GET/PUT/DELETE 統一
• D1 SQL 簡潔ラッパ
• CORS 対応

:LiCode: 実コード（SCALE Base より自動抽出）

:LiInfo: scale-crm:functions/api/data/[key].ts の中身そのもの。コピペ即可。

// GET    /api/data/<key>   → 単一取得
// PUT    /api/data/<key>   → upsert (body: 値のJSON文字列)
// DELETE /api/data/<key>   → 削除
import { corsResponse, corsError, handleOptions } from '../../_lib/cors';

interface Env { DB: D1Database }

export const onRequestOptions = () => handleOptions();

export const onRequestGet: PagesFunction<Env> = async ({ params, env }) => {
  try {
    const key = decodeURIComponent(String(params.key));
    const row = await env.DB.prepare(
      'SELECT key, value, updated_at, updated_by FROM app_data WHERE key = ?'
    ).bind(key).first();
    if (!row) return corsResponse({ ok: false, error: 'not found' }, 404);
    return corsResponse({ ok: true, data: row });
  } catch (e: any) {
    return corsError(e.message);
  }
};

export const onRequestPut: PagesFunction<Env> = async ({ params, request, env }) => {
  try {
    const key = decodeURIComponent(String(params.key));
    const value = await request.text(); // 値はJSON文字列としてそのまま保存
    const user = (() => {
      const raw = request.headers.get('X-User') || 'unknown';
      try { return decodeURIComponent(raw); } catch { return raw; }
    })();
    const now = new Date().toISOString();

    // 既存値を取得（audit_log 用）
    const before = await env.DB.prepare('SELECT value FROM app_data WHERE key = ?').bind(key).first();
    const beforeValue = before ? String(before.value) : null;

    // upsert
    await env.DB.prepare(
      'INSERT INTO app_data (key, value, updated_at, updated_by) VALUES (?, ?, ?, ?) ' +
      'ON CONFLICT(key) DO UPDATE SET value = excluded.value, updated_at = excluded.updated_at, updated_by = excluded.updated_by'
    ).bind(key, value, now, user).run();

    // audit_log（変更があった時だけ）
    if (beforeValue !== value) {
      await env.DB.prepare(
        'INSERT INTO audit_log (occurred_at, user, key, action, before_value, after_value) VALUES (?, ?, ?, ?, ?, ?)'
      ).bind(now, user, key, 'upsert', beforeValue, value).run();
    }

    return corsResponse({ ok: true, key, updated_at: now });
  } catch (e: any) {
    return corsError(e.message);
  }
};

export const onRequestDelete: PagesFunction<Env> = async ({ params, request, env }) => {
  try {
    const key = decodeURIComponent(String(params.key));
    const user = (() => {
      const raw = request.headers.get('X-User') || 'unknown';
      try { return decodeURIComponent(raw); } catch { return raw; }
    })();
    const now = new Date().toISOString();

    const before = await env.DB.prepare('SELECT value FROM app_data WHERE key = ?').bind(key).first();
    const beforeValue = before ? String(before.value) : null;

    await env.DB.prepare('DELETE FROM app_data WHERE key = ?').bind(key).run();

    if (beforeValue !== null) {
      await env.DB.prepare(
        'INSERT INTO audit_log (occurred_at, user, key, action, before_value, after_value) VALUES (?, ?, ?, ?, ?, ?)'
      ).bind(now, user, key, 'delete', beforeValue, null).run();
    }

    return corsResponse({ ok: true, key });
  } catch (e: any) {
    return corsError(e.message);
  }
};

:LiFolder: ソースファイルのパス

/Users/oogushiyuuki/株式会社SCALE/scale-lead/functions/api/data/[key].ts

:LiHandPointer: 使い方

対象プロジェクトに該当ファイルをコピーして、props を流し込むだけ。

:LiAlertCircle: 注意事項

• 依存パッケージを忘れず追加