Store CometAPI Chat History Atomically with SQLite

Last reviewed: November 1, 2026 (Asia/Shanghai).

Direct answer

Store CometAPI SQLite chat history as complete user-and-assistant turn pairs. Read the committed history and its version in one query, call CometAPI outside a database transaction, then atomically advance that version and insert the accepted pair. If another turn has already advanced the conversation, reject the obsolete reply instead of appending it to newer history.

This prevents a specific race: two requests both read version 0, but the second request finishes first. Its turn commits as version 1. The first request must then fail its version check; its answer was generated without version 1’s context.

The CometAPI chat reference documents sending prior assistant messages in the conversation, the non-streaming completion envelope, and finish reasons. SQLite’s transaction reference documents explicit commit and rollback, one simultaneous writer, and BEGIN IMMEDIATE. The version check connects those contracts into an application rule: an answer can commit only against the history it used.

Who this is for

This tutorial is for a server-side Node.js application or local worker using a persistent SQLite file. It implements text-only Chat Completions with complete turns committed together. Pending prompts and failed attempts stay outside replayable history.

Use Node.js 24.15 or later within the 24.x line for this example. The live Node.js 24 SQLite reference labels node:sqlite a release candidate and documents that DatabaseSync executes synchronously. Keep database operations short; assess event-loop latency before using this design in a busy request handler. A dedicated database worker or another storage adapter may fit that workload better.

The application’s authentication layer must resolve which conversation the caller owns before invoking the store. Keep the database path, model, and API key in trusted server configuration. The example is a storage module and CLI demonstration; it does not implement an authenticated web route.

Key takeaways

• Read version and ordered turn content in one SQL statement.
• Release database reads before awaiting the network.
• Commit the version increment and complete pair in one short write transaction.
• Reject stale replies, refusals, tool calls, truncation, and unexpected response shapes.
• Return a successful reply only after the local commit succeeds.
• Treat duplicate network work, request retries, and transcript retention as separate application decisions.

The site’s storage-field guide helps select optional audit fields. Here, the important addition is executable transaction behavior.

Build the versioned turn store

Save this as chat-store.mjs. A conversation’s version counts committed pairs. One row holds each pair, so there is no intermediate durable state containing only its user message. Bound parameters carry all runtime values; SQL identifiers and statements remain fixed.

import { DatabaseSync } from 'node:sqlite';

const MAX_TURNS = 20;

function text(value, maxBytes) {
  return typeof value === 'string' && value.trim().length > 0 &&
    Buffer.byteLength(value, 'utf8') <= maxBytes;
}

function acceptedCompletion(data) {
  const choice = data?.choices?.[0];
  const message = choice?.message;
  if (data?.object !== 'chat.completion' ||
      !text(data.id, 256) || !text(data.model, 256) ||
      !Array.isArray(data.choices) || data.choices.length !== 1 ||
      choice?.index !== 0 || choice.finish_reason !== 'stop' ||
      message?.role !== 'assistant' ||
      message.refusal != null || message.function_call != null ||
      (message.tool_calls != null &&
        (!Array.isArray(message.tool_calls) || message.tool_calls.length > 0)) ||
      !text(message.content, 32768)) {
    throw new Error('unaccepted_completion');
  }
  return { reply: message.content, id: data.id, model: data.model };
}

export function openChatStore(path) {
  const db = new DatabaseSync(path, { timeout: 250 });
  db.exec(`
    CREATE TABLE IF NOT EXISTS conversations (
      id TEXT PRIMARY KEY,
      version INTEGER NOT NULL DEFAULT 0 CHECK(version BETWEEN 0 AND 20)
    ) STRICT;
    CREATE TABLE IF NOT EXISTS turns (
      conversation_id TEXT NOT NULL REFERENCES conversations(id),
      turn_number INTEGER NOT NULL CHECK(turn_number BETWEEN 1 AND 20),
      user_text TEXT NOT NULL CHECK(length(user_text) > 0),
      assistant_text TEXT NOT NULL CHECK(length(assistant_text) > 0),
      response_id TEXT NOT NULL,
      model TEXT NOT NULL,
      created_at TEXT NOT NULL,
      PRIMARY KEY (conversation_id, turn_number)
    ) STRICT;
  `);
  const create = db.prepare(`
    INSERT INTO conversations(id) VALUES (?) ON CONFLICT(id) DO NOTHING
  `);
  const read = db.prepare(`
    SELECT c.version, t.turn_number, t.user_text, t.assistant_text
    FROM conversations AS c
    LEFT JOIN turns AS t ON t.conversation_id = c.id
    WHERE c.id = ? ORDER BY t.turn_number
  `);
  const advance = db.prepare(`
    UPDATE conversations SET version = version + 1
    WHERE id = ? AND version = ?
  `);
  const insert = db.prepare(`
    INSERT INTO turns VALUES (?, ?, ?, ?, ?, ?, ?)
  `);

  function snapshot(id) {
    const rows = read.all(id);
    if (!rows.length) throw new Error('conversation_missing');
    return {
      version: rows[0].version,
      messages: rows.filter(row => row.turn_number !== null).flatMap(row => [
        { role: 'user', content: row.user_text },
        { role: 'assistant', content: row.assistant_text }
      ])
    };
  }

  async function runTurn(id, prompt, generate) {
    if (!text(prompt, 4096)) throw new Error('invalid_prompt');
    const before = snapshot(id);
    if (before.version >= MAX_TURNS) throw new Error('conversation_full');
    const result = acceptedCompletion(await generate([
      ...before.messages, { role: 'user', content: prompt }
    ]));

    db.exec('BEGIN IMMEDIATE');
    try {
      if (advance.run(id, before.version).changes !== 1) {
        throw new Error('stale_conversation');
      }
      insert.run(id, before.version + 1, prompt, result.reply,
        result.id, result.model, new Date().toISOString());
      db.exec('COMMIT');
    } catch (error) {
      if (db.isTransaction) {
        try { db.exec('ROLLBACK'); }
        catch {
          db.close();
          throw new Error('store_unavailable');
        }
      }
      throw error;
    }
    return { reply: result.reply, version: before.version + 1 };
  }

  return {
    createConversation: id => create.run(id),
    snapshot,
    runTurn,
    close: () => db.close()
  };
}

The joined query returns the version and history from one statement’s database view. Reading them with separate queries could pair an old version with newer content when another connection commits between reads. SQLite’s isolation documentation explains that separate connections see committed transactions and that a read transaction sees a consistent snapshot.

After generation, BEGIN IMMEDIATE starts the short write phase. The conditional update changes exactly one row only when the saved version still matches. Node.js documents statement.run().changes as the number of changed rows. A zero result triggers rollback; the obsolete pair never reaches the insert. A later insert failure also rolls back the version increment.

There is deliberately no await between BEGIN and COMMIT. The network can take seconds, while SQLite permits only one writer at a time. Holding that write transaction during generation would block unrelated writes. The 250-millisecond busy timeout is a local contention limit, not a request deadline; waiting is synchronous on this connection.

The prompt, reply, turn-count, and identifier limits are tutorial policies. The 20-turn ceiling bounds replay growth, but bytes and turn count do not establish that history fits a model’s token window. Add model-aware admission before generation in an application. If you change the turn ceiling, change both SQL constraints and MAX_TURNS through a reviewed migration.

Connect the bounded CometAPI adapter

Copy limited-json.mjs from the site’s bounded JSON reader tutorial into the same directory. Its exported readJsonWithinLimit returns { data, bytes } after a bounded read and rejects invalid JSON or UTF-8. Save this adapter as generate-cometapi.mjs:

import { readJsonWithinLimit } from './limited-json.mjs';

export function createGenerator({ key, model, fetchImpl = fetch }) {
  if (typeof key !== 'string' || !key.trim() || /[\r\n]/.test(key) ||
      typeof model !== 'string' || !model.trim()) {
    throw new Error('missing_cometapi_configuration');
  }
  return async messages => {
    const controller = new AbortController();
    const timer = setTimeout(() => controller.abort(), 20000);
    try {
      const response = await fetchImpl(
        'https://api.cometapi.com/v1/chat/completions', {
          method: 'POST',
          redirect: 'error',
          signal: controller.signal,
          headers: {
            authorization: `Bearer ${key}`,
            'content-type': 'application/json'
          },
          body: JSON.stringify({ model, messages, stream: false })
        }
      );
      const type = (response.headers.get('content-type') ?? '')
        .split(';')[0].trim().toLowerCase();
      if (response.status !== 200 || type !== 'application/json') {
        throw new Error('unaccepted_http_response');
      }
      const { data } = await readJsonWithinLimit(
        response, controller, 1024 * 1024
      );
      return data;
    } catch {
      controller.abort();
      throw new Error('cometapi_request_failed');
    } finally {
      clearTimeout(timer);
    }
  };
}

This adapter sends the configured model and complete message array to the documented chat endpoint, with bearer authentication and streaming disabled. It accepts only HTTP 200 with the documented JSON media type. The timer stays active through body consumption; the one-mebibyte response ceiling applies before parsing. Both limits are local settings.

The store then enforces its narrower result contract: one assistant choice, stop, nonblank bounded text, and no refusal or tool call. CometAPI documents other finish reasons and nullable content; those outcomes need explicit product handling rather than a successful transcript entry.

For a local demonstration, save demo.mjs:

import { openChatStore } from './chat-store.mjs';
import { createGenerator } from './generate-cometapi.mjs';

const generate = createGenerator({
  key: process.env.COMETAPI_KEY,
  model: process.env.COMETAPI_MODEL
});
const store = openChatStore('./chat.db');
try {
  store.createConversation('local-demo');
  const result = await store.runTurn(
    'local-demo', 'Suggest one short title for a garden notebook.', generate
  );
  console.info(JSON.stringify({ committed_version: result.version }));
} catch {
  console.error('turn_not_confirmed');
  process.exitCode = 1;
} finally {
  store.close();
}

Set COMETAPI_KEY through your existing secret mechanism and choose COMETAPI_MODEL from current account and model documentation. Run node demo.mjs after the synthetic checks. Running it again reuses committed history; it also makes another generation request. The demonstration prints only a commit version. An authorized interface can display result.reply after success using its safe rendering boundary.

local-demo is a CLI example identifier. A web application must assign conversation identifiers on the server and authorize every read and mutation. Do not use the idempotent creation method to grant access to a caller-supplied identifier.

Contract details to verify

Reproduce the stale-reply race

Save this as check-race.mjs and run node check-race.mjs. It creates a temporary database under the current directory and uses two connections. The generation callback deliberately waits for the test to choose a completion order.

import assert from 'node:assert/strict';
import { mkdtempSync, rmSync } from 'node:fs';
import { join } from 'node:path';
import { openChatStore } from './chat-store.mjs';

const directory = mkdtempSync(join(process.cwd(), '.chat-check-'));
const path = join(directory, 'chat.db');
const a = openChatStore(path);
const b = openChatStore(path);
const pending = [];
const generate = messages => new Promise(resolve => {
  pending.push({ messages, resolve });
});
const completion = text => ({
  id: 'fixture-id', model: 'fixture-model', object: 'chat.completion',
  choices: [{ index: 0, finish_reason: 'stop',
    message: { role: 'assistant', content: text, refusal: null } }]
});

try {
  a.createConversation('demo');
  const old = a.runTurn('demo', 'older prompt', generate);
  const current = b.runTurn('demo', 'winning prompt', generate);
  assert.equal(pending.length, 2);
  pending[1].resolve(completion('winning reply'));
  assert.equal((await current).version, 1);
  pending[0].resolve(completion('obsolete reply'));
  await assert.rejects(old, /stale_conversation/);
  assert.deepEqual(a.snapshot('demo'), {
    version: 1,
    messages: [
      { role: 'user', content: 'winning prompt' },
      { role: 'assistant', content: 'winning reply' }
    ]
  });
} finally {
  a.close();
  b.close();
  rmSync(directory, { recursive: true, force: true });
}
console.info('Race check passed');

This race check passed locally on Node.js 24.22.1. An additional preparation harness verified rollback after a synthetic insert failure, lock contention, reopening the file, replay into the next request, input rejection, the turn ceiling, and the unaccepted-completion cases above. Adapter checks covered method, URL, headers, message payload, HTTP rejection, media type, malformed JSON, and oversize rejection. All used synthetic responses; no authenticated CometAPI request or deployment was performed.

Failure modes

The reply is displayed before commit. Generation succeeds, but storage fails or the version is stale. Keep the reply provisional until runTurn() resolves successfully. On conflict, reload history and tell the user the turn was not saved; retain their unsent prompt in the interface if appropriate.

Two requests both incur generation work. Optimistic commit prevents obsolete history from being appended. Both candidates can still reach CometAPI. If the product requires one in-flight generation per conversation, add admission or a durable claim before dispatch and review its recovery behavior.

Rollback is treated as a refund. Local rollback does not undo a remote request. A crash after generation but before commit can leave paid work without a saved answer. A crash after commit but before acknowledgment can make the caller uncertain. This module has no durable attempt ledger or request-id deduplication; reconcile the committed state and add those facilities before enabling automatic retries.

A long database wait stalls other requests. DatabaseSync and its busy wait run synchronously. Keep transactions small, measure contention, and move storage work off the request event loop when necessary. If rollback itself fails, discard the closed store and recover through the application lifecycle rather than continuing on that connection.

Deleting turns silently corrupts replay. This schema assumes all transcript mutations use the versioned store. Direct edits, compaction, deletion, and migrations need their own atomic policy. CREATE TABLE IF NOT EXISTS initializes a fresh demonstration database; it does not migrate an existing schema.

The database becomes an uncontrolled transcript archive. It intentionally contains user and assistant text. Put the file outside public directories, restrict application access, and define retention, deletion, and backup rules. Keep keys, authorization headers, raw errors, and complete transcripts out of routine logs. Transcript storage is an application data decision, separate from operational diagnostics.

Sources checked

These four primary public references were fetched successfully on November 1, 2026 UTC. They are maintained technical references for an evergreen implementation task. Their live content supports the current contracts; this article does not describe a newly launched feature.

• CometAPI: Create a chat completion — message roles and history, endpoint and authentication, non-streaming JSON, response identifiers, model, text, refusal, tool calls, and finish reasons.
• Node.js 24: SQLite — release-candidate status, synchronous connections, busy timeout, bound statements, row-change counts, transaction state, and closing connections.
• SQLite: Transaction — explicit transactions, BEGIN IMMEDIATE, single-writer behavior, contention, commit errors, and rollback.
• SQLite: Isolation In SQLite — committed visibility across connections, consistent read snapshots, and write serialization.

The pair schema, optimistic version rule, accepted outcome, and numerical limits are application engineering choices built on those documented mechanics.

FAQ

Does this guarantee one CometAPI request per user action?

No. Two candidates can generate from the same version. Only one can commit against it. A durable request identity and dispatch policy are needed to handle retries and duplicate actions.

Why not save the user message immediately?

This tutorial’s replay contract includes only completed pairs. A product can show a pending user message separately. If pending or failed turns must be durable, introduce an explicit attempt state and define which states are eligible for replay.

Can the same store save streaming or tool conversations?

The current parser accepts complete plain-text responses. Streams need provisional chunk handling and a terminal outcome; tools need typed call/result records and execution controls. Extend the schema and tests before accepting those paths.

Does the turn limit guarantee the model has enough context?

No. Token size depends on the text and selected model. Keep the simple bound here and add a model-aware context check before dispatch. Do not silently drop earlier turns to make a request fit.

Reader next step

Run the two-connection race test, add the rollback and restart fixtures, then connect the bounded adapter with one harmless staging prompt. Check that the next request replays the committed pair and that a stale completion never appears in history or as a saved reply. Record only a controlled conversation reference, version, and fixed outcome in operational diagnostics.

When those local boundaries are ready and you need account access, Start with CometAPI . Verify the chosen model and live envelope through the same adapter before enabling the workflow for users.