LINE予約システムを自作する手順コード付きの実装記録

line-booking/
├── src/index.js      … 入口（Webhook・店の画面・定期実行に振り分ける）
├── src/line.js       … 署名の検証とメッセージの送信
├── src/webhook.js    … イベントごとの処理
├── src/booking.js    … 枠と予約のSQL
├── src/time.js       … 日本時間の計算
├── src/remind.js     … 前日のリマインド
├── src/admin.js      … 店側の画面
├── schema.sql        … 表の定義
├── richmenu.mjs      … リッチメニューを作るスクリプト
└── wrangler.jsonc

{
  "name": "line-booking",
  "main": "src/index.js",
  "compatibility_date": "2026-10-01",
  "triggers": { "crons": ["0 9 * * *", "0 10 * * *"] }, // UTC。日本時間の18時と19時
  "vars": {
    "LINE_API_BASE": "https://api.line.me"
  },
  "d1_databases": [
    { "binding": "DB", "database_name": "line-booking", "database_id": "00000000-0000-0000-0000-000000000000" }
  ]
}

LINE_CHANNEL_SECRET=test-channel-secret-for-local-only
LINE_CHANNEL_ACCESS_TOKEN=test-token
LINE_API_BASE=http://127.0.0.1:8790
ADMIN_USER=staff
ADMIN_PASSWORD=local-only-password

import { handleAdmin } from "./admin.js";
import { verifySignature } from "./line.js";
import { sendReminders } from "./remind.js";
import { handleEvents } from "./webhook.js";

export default {
  async fetch(request, env, ctx) {
    const url = new URL(request.url);
    if (url.pathname.startsWith("/admin")) return handleAdmin(request, env);
    if (request.method !== "POST" || url.pathname !== "/webhook") return new Response("Not Found", { status: 404 });

    // 本文は JSON にする前に、文字列のまま署名を確かめる
    const rawBody = await request.text();
    const ok = await verifySignature(env.LINE_CHANNEL_SECRET, rawBody, request.headers.get("x-line-signature") ?? "");
    if (!ok) return new Response("Unauthorized", { status: 401 });

    const { events = [] } = JSON.parse(rawBody);
    ctx.waitUntil(handleEvents(env, events)); // 先に 200 を返し、処理は後に回す
    return new Response("OK");
  },

  async scheduled(controller, env, ctx) {
    ctx.waitUntil(sendReminders(env, controller.scheduledTime));
  },
};

export async function verifySignature(secret, rawBody, signature) {
  if (!secret || !signature) return false;
  const enc = new TextEncoder();
  const key = await crypto.subtle.importKey("raw", enc.encode(secret), { name: "HMAC", hash: "SHA-256" }, false, ["verify"]);
  let sig;
  try {
    sig = Uint8Array.from(atob(signature), (c) => c.charCodeAt(0));
  } catch {
    return false; // Base64 として読めない値
  }
  return crypto.subtle.verify("HMAC", key, sig, enc.encode(rawBody));
}

openssl: Qi+ynCRA2cQkBJOS75gA29VG8zLhHIkJtOdd9dBT3BU=
node   : Qi+ynCRA2cQkBJOS75gA29VG8zLhHIkJtOdd9dBT3BU=
verifySignature(openssl の値): true

// richmenu.mjs：リッチメニューを作り、画像を付け、全員の既定にする
import { readFileSync } from "node:fs";

const API = process.env.LINE_API_BASE ?? "https://api.line.me";
const DATA = process.env.LINE_DATA_BASE ?? "https://api-data.line.me"; // 画像だけドメインが違う
const auth = { Authorization: `Bearer ${process.env.LINE_CHANNEL_ACCESS_TOKEN}` };

const menu = {
  size: { width: 2500, height: 843 },
  selected: true,
  name: "booking-v1",
  chatBarText: "メニュー",
  areas: [
    { bounds: { x: 0, y: 0, width: 1250, height: 843 }, action: { type: "postback", data: "action=dates", displayText: "予約する" } },
    { bounds: { x: 1250, y: 0, width: 1250, height: 843 }, action: { type: "postback", data: "action=mine", displayText: "予約の確認" } },
  ],
};

async function call(url, init) {
  const res = await fetch(url, init);
  const body = await res.text();
  if (!res.ok) throw new Error(`${res.status} ${url}: ${body}`);
  return body ? JSON.parse(body) : {};
}

const { richMenuId } = await call(`${API}/v2/bot/richmenu`, {
  method: "POST",
  headers: { ...auth, "Content-Type": "application/json" },
  body: JSON.stringify(menu),
});
await call(`${DATA}/v2/bot/richmenu/${richMenuId}/content`, {
  method: "POST",
  headers: { ...auth, "Content-Type": "image/png" },
  body: readFileSync("richmenu.png"),
});
await call(`${API}/v2/bot/user/all/richmenu/${richMenuId}`, { method: "POST", headers: auth });
console.log("default rich menu:", richMenuId);

default rich menu: richmenu-test

-- 予約枠。時刻は UTC で持ち、画面と判定のときだけ日本時間に直す
CREATE TABLE slots (
  id        INTEGER PRIMARY KEY,
  starts_at TEXT    NOT NULL UNIQUE,            -- '2026-11-05T01:00:00.000Z'
  capacity  INTEGER NOT NULL CHECK (capacity >= 0) -- 0 にすると店が閉じた枠になる
);

-- 予約。残りの数は持たず、status が booked の行を数える
CREATE TABLE bookings (
  id            TEXT PRIMARY KEY,
  slot_id       INTEGER NOT NULL REFERENCES slots(id),
  line_user_id  TEXT,                            -- 電話の予約は NULL
  customer_name TEXT NOT NULL,
  status        TEXT NOT NULL DEFAULT 'booked',  -- booked / cancelled
  retry_key     TEXT NOT NULL,                   -- リマインドの X-Line-Retry-Key
  reminded_at   TEXT
);
CREATE INDEX bookings_by_slot ON bookings (slot_id, status);

-- 同じ人が同じ枠に2件入らない。取り消した行は対象の外
CREATE UNIQUE INDEX one_booking_per_person
  ON bookings (slot_id, line_user_id)
  WHERE status = 'booked' AND line_user_id IS NOT NULL;

-- 受け取った Webhook の記録。再送を飛ばすために使う
CREATE TABLE line_events (
  webhook_event_id TEXT PRIMARY KEY
);

-- 11月5日 10:00（日本時間）、定員3の枠
INSERT INTO slots (starts_at, capacity) VALUES ('2026-11-05T01:00:00.000Z', 3);

// 日本時間の日付と時刻。日本は夏時間がないので UTC+9 で固定できる
const JST_MS = 9 * 60 * 60 * 1000;
const DAY_MS = 24 * 60 * 60 * 1000;

export const jstDate = (ms) => new Date(ms + JST_MS).toISOString().slice(0, 10); // '2026-11-05'
export const jstTime = (iso) => new Date(Date.parse(iso) + JST_MS).toISOString().slice(11, 16); // '10:00'
export const jstLabel = (iso) => `${jstDate(Date.parse(iso)).slice(5).replace("-", "/")} ${jstTime(iso)}`; // '11/05 10:00'

// 日本時間の1日を、UTC の [始まり, 次の日の始まり) にする
export function jstDayRange(date) {
  const from = Date.parse(`${date}T00:00:00+09:00`);
  return [new Date(from).toISOString(), new Date(from + DAY_MS).toISOString()];
}

// 取り消しの締め切り：枠の前日の18時（日本時間）
export function cancelDeadline(startsAt) {
  const day = jstDate(Date.parse(startsAt) - DAY_MS);
  return Date.parse(`${day}T18:00:00+09:00`);
}

import { cancelDeadline, jstDayRange } from "./time.js";

const FREE = `s.capacity - (SELECT count(*) FROM bookings b WHERE b.slot_id = s.id AND b.status = 'booked')`;

// 空きのある枠を、今より後から days 日ぶん取り出す
export async function openSlots(env, nowMs, days = 14) {
  const { results } = await env.DB.prepare(
    `SELECT s.id, s.starts_at, ${FREE} AS free
       FROM slots s
      WHERE s.starts_at > ?1 AND s.starts_at < ?2 AND ${FREE} > 0
      ORDER BY s.starts_at`,
  )
    .bind(new Date(nowMs).toISOString(), new Date(nowMs + days * 86_400_000).toISOString())
    .all();
  return results;
}

import { cancelByCustomer, myBookings, openSlots, reserve } from "./booking.js";
import { respond } from "./line.js";
import { jstDate, jstLabel, jstTime } from "./time.js";

const text = (t, quick) => ({
  type: "text",
  text: t,
  ...(quick?.length ? { quickReply: { items: quick } } : {}),
});
const button = (label, data) => ({ type: "action", action: { type: "postback", label, data, displayText: label } });

export async function handleEvents(env, events) {
  for (const event of events) {
    // 同じイベントは1回だけ処理する
    const seen = await env.DB.prepare(
      "INSERT INTO line_events (webhook_event_id) VALUES (?1) ON CONFLICT DO NOTHING",
    )
      .bind(event.webhookEventId)
      .run();
    if (seen.meta.changes === 0) continue;

    try {
      await handleEvent(env, event);
    } catch (err) {
      console.log(`failed ${event.webhookEventId}: ${err.message}`);
    }
  }
}

async function handleEvent(env, event) {
  if (event.type === "follow") {
    return respond(env, event, [text("友だち追加ありがとうございます。下のメニューの「予約する」から日付を選べます。")]);
  }
  if (event.type !== "postback" || !event.source.userId) return;

  const p = new URLSearchParams(event.postback.data);
  const nowMs = Date.now();
  const userId = event.source.userId;

  switch (p.get("action")) {
    case "dates": {
      const days = [...new Set((await openSlots(env, nowMs)).map((s) => jstDate(Date.parse(s.starts_at))))];
      if (days.length === 0) return respond(env, event, [text("いま予約できる枠がありません。")]);
      const quick = days.slice(0, 13).map((d) => button(d.slice(5).replace("-", "/"), `action=times&date=${d}`));
      return respond(env, event, [text("日付を選んでください。", quick)]);
    }
    case "times": {
      const date = p.get("date");
      const slots = (await openSlots(env, nowMs)).filter((s) => jstDate(Date.parse(s.starts_at)) === date);
      if (slots.length === 0) return respond(env, event, [text("この日の空きはなくなりました。もう一度「予約する」から選んでください。")]);
      const quick = slots.slice(0, 13).map((s) => button(`${jstTime(s.starts_at)}（残り${s.free}）`, `action=book&slot=${s.id}`));
      return respond(env, event, [text(`${date.slice(5).replace("-", "/")} の時間を選んでください。`, quick)]);
    }
    case "book": {
      const name = await displayName(env, userId);
      const r = await reserve(env, { slotId: Number(p.get("slot")), lineUserId: userId, name, nowMs });
      if (r.ok) return respond(env, event, [text("ご予約を受け付けました。確認と取り消しは、メニューの「予約の確認」からできます。")]);
      if (r.reason === "already") return respond(env, event, [text("この時間はすでにご予約いただいています。")]);
      return respond(env, event, [text("申し訳ありません。この時間は埋まりました。ほかの時間を選んでください。", [button("日付を選び直す", "action=dates")])]);
    }
    case "mine": {
      const list = await myBookings(env, userId, nowMs);
      if (list.length === 0) return respond(env, event, [text("いまのご予約はありません。")]);
      const lines = list.map((b) => jstLabel(b.starts_at)).join("\n");
      const quick = list.slice(0, 13).map((b) => button(`${jstLabel(b.starts_at)} 取消`, `action=cancel&booking=${b.id}`));
      return respond(env, event, [text(`ご予約\n${lines}`, quick)]);
    }
    case "cancel": {
      const r = await cancelByCustomer(env, { bookingId: p.get("booking"), lineUserId: userId, nowMs });
      const msg = {
        ok: "ご予約を取り消しました。",
        late: "取り消しの締め切り（前日18時）を過ぎています。お電話でご連絡ください。",
        none: "取り消せるご予約が見つかりませんでした。",
      };
      return respond(env, event, [text(msg[r])]);
    }
  }
}

async function displayName(env, userId) {
  const res = await fetch(`${env.LINE_API_BASE}/v2/bot/profile/${userId}`, {
    headers: { Authorization: `Bearer ${env.LINE_CHANNEL_ACCESS_TOKEN}` },
    signal: AbortSignal.timeout(5_000),
  });
  return res.ok ? (await res.json()).displayName : "（名前を取得できず）";
}

日付：["11/01","11/05","11/06","11/07","11/10"]
時間：[{"label":"10:00（残り1）","data":"action=book&slot=1"},{"label":"11:00（残り3）","data":"action=book&slot=2"}]

// 枠を1つ確保する。空きの確認と登録を 1 文で行う
export async function reserve(env, { slotId, lineUserId, name, nowMs }) {
  const id = crypto.randomUUID();
  try {
    const r = await env.DB.prepare(
      `INSERT INTO bookings (id, slot_id, line_user_id, customer_name, retry_key)
       SELECT ?1, s.id, ?2, ?3, ?4
         FROM slots s
        WHERE s.id = ?5 AND s.starts_at > ?6 AND ${FREE} > 0`,
    )
      .bind(id, lineUserId, name, crypto.randomUUID(), slotId, new Date(nowMs).toISOString())
      .run();
    if (r.meta.changes === 1) return { ok: true, id };
    // 埋まった枠を取ったのが本人なら、「満席」ではなく「予約済み」と返す
    const mine = await env.DB.prepare(
      `SELECT 1 FROM bookings WHERE slot_id = ?1 AND line_user_id = ?2 AND status = 'booked'`,
    )
      .bind(slotId, lineUserId)
      .first();
    return { ok: false, reason: mine ? "already" : "full" };
  } catch (err) {
    // 空きがある枠に、同じ人の2件目が来た（ボタンの2度押しなど）
    if (String(err.message).includes("UNIQUE constraint failed")) return { ok: false, reason: "already" };
    throw err;
  }
}

WITH got AS (
  UPDATE slots SET booked = booked + 1
   WHERE id = $1 AND booked < capacity
  RETURNING id
)
INSERT INTO bookings (slot_id, customer) SELECT id, $2 FROM got;

function post(env, path, body, headers = {}) {
  return fetch(`${env.LINE_API_BASE}${path}`, {
    method: "POST",
    headers: {
      "Content-Type": "application/json",
      Authorization: `Bearer ${env.LINE_CHANNEL_ACCESS_TOKEN}`,
      ...headers,
    },
    body: JSON.stringify(body),
    signal: AbortSignal.timeout(10_000),
  });
}

export function push(env, to, messages, retryKey = crypto.randomUUID()) {
  return post(env, "/v2/bot/message/push", { to, messages }, { "X-Line-Retry-Key": retryKey });
}

// 応答で返し、応答トークンが使えなかったときだけ push に切り替える
export async function respond(env, event, messages) {
  const res = await post(env, "/v2/bot/message/reply", { replyToken: event.replyToken, messages });
  if (res.ok) return;
  if (res.status !== 400) throw new Error(`reply ${res.status}`); // 5xx は届いた可能性があるので重ねて送らない
  const pushed = await push(env, event.source.userId, messages);
  if (!pushed.ok) throw new Error(`push ${pushed.status}`);
}

import { push } from "./line.js";
import { jstDate, jstDayRange, jstTime } from "./time.js";

// 明日（日本時間）の予約に1回だけ送る。送る前に印を付け、取れた実行だけが送る
export async function sendReminders(env, scheduledMs) {
  const [from, to] = jstDayRange(jstDate(scheduledMs + 86_400_000));
  const { results } = await env.DB.prepare(
    `SELECT b.id, b.line_user_id, b.retry_key, s.starts_at
       FROM bookings b JOIN slots s ON s.id = b.slot_id
      WHERE b.status = 'booked' AND b.line_user_id IS NOT NULL AND b.reminded_at IS NULL
        AND s.starts_at >= ?1 AND s.starts_at < ?2`,
  )
    .bind(from, to)
    .all();

  for (const b of results) {
    const claim = await env.DB.prepare(
      "UPDATE bookings SET reminded_at = ?1 WHERE id = ?2 AND reminded_at IS NULL AND status = 'booked'",
    )
      .bind(new Date(scheduledMs).toISOString(), b.id)
      .run();
    if (claim.meta.changes === 0) continue;

    const res = await push(env, b.line_user_id, [{ type: "text", text: `明日 ${jstTime(b.starts_at)} からのご予約のお知らせです。` }], b.retry_key).catch(() => null);
    // 届いたか分からない失敗は印を戻し、次の回に同じ retry key で送り直す
    if (!res || res.status >= 500) {
      await env.DB.prepare("UPDATE bookings SET reminded_at = NULL WHERE id = ?1").bind(b.id).run();
    }
    console.log(`remind ${b.id} ${res?.status ?? "no response"}`);
  }
}

18時  200 U_g        897a17ab
18時  500 U_500once  a761b15f
18時  200 U_f        d777d3d0
19時  200 U_500once  a761b15f

// お客様の取り消し。本人の予約で、締め切り前のときだけ通す
export async function cancelByCustomer(env, { bookingId, lineUserId, nowMs }) {
  const b = await env.DB.prepare(
    `SELECT s.starts_at FROM bookings b JOIN slots s ON s.id = b.slot_id
      WHERE b.id = ?1 AND b.line_user_id = ?2 AND b.status = 'booked'`,
  )
    .bind(bookingId, lineUserId)
    .first();
  if (!b) return "none";
  if (nowMs >= cancelDeadline(b.starts_at)) return "late";
  const r = await env.DB.prepare(`UPDATE bookings SET status = 'cancelled' WHERE id = ?1 AND status = 'booked'`)
    .bind(bookingId)
    .run();
  return r.meta.changes === 1 ? "ok" : "none";
}

export async function myBookings(env, lineUserId, nowMs) {
  const { results } = await env.DB.prepare(
    `SELECT b.id, s.starts_at FROM bookings b JOIN slots s ON s.id = b.slot_id
      WHERE b.line_user_id = ?1 AND b.status = 'booked' AND s.starts_at > ?2
      ORDER BY s.starts_at`,
  )
    .bind(lineUserId, new Date(nowMs).toISOString())
    .all();
  return results;
}

OK 枠 2026-11-05T10:00:00 / 今 2026-11-04T17:59:59 → 取り消せる
OK 枠 2026-11-05T10:00:00 / 今 2026-11-04T18:00:00 → 締め切り後
OK 枠 2026-11-01T08:30:00 / 今 2026-10-31T17:59:59 → 取り消せる
OK 枠 2026-11-01T08:30:00 / 今 2026-10-31T18:00:00 → 締め切り後
OK 枠 2027-01-01T10:00:00 / 今 2026-12-31T17:59:59 → 取り消せる
OK 枠 2028-03-01T10:00:00 / 今 2028-02-29T18:00:00 → 締め切り後

// 店側の取り消し。締め切りは見ない
export function cancelByStaff(env, bookingId) {
  return env.DB.prepare(`UPDATE bookings SET status = 'cancelled' WHERE id = ?1 AND status = 'booked'`)
    .bind(bookingId)
    .run();
}

// 店の画面に出す、その日の枠と予約
export async function dayBoard(env, date) {
  const [from, to] = jstDayRange(date);
  const { results } = await env.DB.prepare(
    `SELECT s.id AS slot_id, s.starts_at, s.capacity, b.id AS booking_id, b.customer_name, b.line_user_id
       FROM slots s LEFT JOIN bookings b ON b.slot_id = s.id AND b.status = 'booked'
      WHERE s.starts_at >= ?1 AND s.starts_at < ?2
      ORDER BY s.starts_at`,
  )
    .bind(from, to)
    .all();
  return results;
}

import { cancelByStaff, dayBoard, reserve } from "./booking.js";
import { jstDate, jstTime } from "./time.js";

const esc = (s) => String(s).replace(/[&<>"']/g, (c) => `&#${c.charCodeAt(0)};`);

function authorized(request, env) {
  const header = request.headers.get("authorization") ?? "";
  if (!env.ADMIN_PASSWORD || !header.startsWith("Basic ")) return false; // 入れ忘れたまま開かないようにする
  const enc = new TextEncoder();
  let a;
  try {
    a = enc.encode(atob(header.slice(6)));
  } catch {
    return false;
  }
  const b = enc.encode(`${env.ADMIN_USER}:${env.ADMIN_PASSWORD}`);
  // 長さが違うときも同じ比較を通し、処理時間の差から長さが分からないようにする
  return a.byteLength === b.byteLength ? crypto.subtle.timingSafeEqual(a, b) : !crypto.subtle.timingSafeEqual(a, a);
}

export async function handleAdmin(request, env) {
  if (!authorized(request, env)) {
    return new Response("Unauthorized", { status: 401, headers: { "WWW-Authenticate": 'Basic realm="admin"' } });
  }
  const url = new URL(request.url);

  if (request.method === "POST") {
    // ほかのサイトからのフォーム送信を断る
    if (request.headers.get("origin") !== url.origin) return new Response("Forbidden", { status: 403 });
    const f = await request.formData();
    if (url.pathname === "/admin/cancel") {
      await cancelByStaff(env, f.get("booking"));
    } else if (url.pathname === "/admin/book") {
      // 電話の予約も、LINE と同じ関数で同じ枠を取る
      const r = await reserve(env, { slotId: Number(f.get("slot")), lineUserId: null, name: f.get("name"), nowMs: Date.now() });
      if (!r.ok) return new Response("この枠は埋まっています", { status: 409, headers: { "Content-Type": "text/plain; charset=utf-8" } });
    } else if (url.pathname === "/admin/close") {
      await env.DB.prepare("UPDATE slots SET capacity = 0 WHERE id = ?1").bind(Number(f.get("slot"))).run();
    } else {
      return new Response("Not Found", { status: 404 });
    }
    return Response.redirect(`${url.origin}/admin?date=${encodeURIComponent(f.get("date"))}`, 303);
  }

  const date = url.searchParams.get("date") ?? jstDate(Date.now());
  const rows = await dayBoard(env, date);
  const hidden = (name, value) => `<input type="hidden" name="${name}" value="${esc(value)}">`;
  const body = rows
    .map((r) => {
      const who = r.booking_id
        ? `${esc(r.customer_name)}（${r.line_user_id ? "LINE" : "電話"}）
           <form method="post" action="/admin/cancel">${hidden("booking", r.booking_id)}${hidden("date", date)}<button>取消</button></form>`
        : "空き";
      return `<tr><td>${jstTime(r.starts_at)}</td><td>定員${r.capacity}</td><td>${who}</td>
        <td><form method="post" action="/admin/book">${hidden("slot", r.slot_id)}${hidden("date", date)}<input name="name" required placeholder="電話のお客様"><button>入れる</button></form>
        <form method="post" action="/admin/close">${hidden("slot", r.slot_id)}${hidden("date", date)}<button>閉じる</button></form></td></tr>`;
    })
    .join("");
  const html = `<!doctype html><meta charset="utf-8"><meta name="viewport" content="width=device-width,initial-scale=1"><title>予約 ${esc(date)}</title>
    <h1>${esc(date)} の予約</h1><table>${body}</table>`;
  return new Response(html, { headers: { "Content-Type": "text/html; charset=utf-8", "Cache-Control": "no-store" } });
}

npx wrangler d1 create line-booking          # 出たIDを wrangler.jsonc の database_id に入れる
npx wrangler d1 execute line-booking --remote --file=schema.sql
npx wrangler secret put LINE_CHANNEL_SECRET
npx wrangler secret put LINE_CHANNEL_ACCESS_TOKEN
npx wrangler secret put ADMIN_USER
npx wrangler secret put ADMIN_PASSWORD
npx wrangler deploy