Telegram Message Storage Fix — Implementation Plan

For Claude: REQUIRED SUB-SKILL: Use superpowers:executing-plans to implement this plan task-by-task.

Goal: Fix 4 broken aspects of the telegram message storage system: messages not saving since 2026-02-21, auto-backup not working, 101 records with NULL received_at, and message sort order issues.

Architecture: The worker process (apps/worker/) runs a Fastify server with multi-bot Telegram polling. Messages are received via node-telegram-bot-api, saved to Turso (LibSQL) via @libsql/client, and injected into tmux sessions. A separate macclaw-logger.ts script was writing to the OLD NeonDB (not Turso), which explains the cutoff. The fix requires: (1) ensuring the worker actually runs and saves to Turso, (2) adding received_at to the bot INSERT, (3) backfilling NULL timestamps, and (4) fixing sort order to use received_at instead of created_at.

Tech Stack: Node.js, TypeScript, Turso (LibSQL/SQLite), @libsql/client, node-telegram-bot-api, Fastify

Root Cause Analysis

Problem 1: Messages Not Saving Since 2026-02-21

Root Cause: Multi-factor

1. Worker NOT running (primary): The worker process (apps/worker/) needs to be running locally for Telegram bots to poll and save messages. If the worker process crashed or was not restarted after a system reboot, no messages get saved. There is NO auto-restart mechanism (no LaunchAgent, no pm2, no systemd).

2. macclaw-logger targets NeonDB, not Turso: The scripts/macclaw-logger.ts file (lines 19-23) connects to NeonDB PostgreSQL (ep-divine-darkness-a1gvyg6j-pooler.ap-southeast-1.aws.neon.tech/neondb), NOT Turso. Since the DB migration from NeonDB to Turso happened around 2026-02-21, this script became effectively dead — it writes to the old DB that nothing reads from anymore.

3. Bot 1 disabled, Bot 4 removed: The .env shows BOT1 is commented out (deleted), BOT3 is ENABLED=false, BOT4 is removed. Only BOT2 (vice-claude) and BOT6 (contents-builder) are active. If only the macclaw-logger was producing records before, its NeonDB targeting explains the exact cutoff date.

Evidence:

• .env line 22-23: DB_URL (NeonDB) is commented out, TURSO_DB_URL is set
• macclaw-logger.ts lines 19-23: Hardcoded NeonDB host/credentials, no Turso support
• telegram.ts line 37: const dbUrl = process.env.TURSO_DB_URL || process.env.DB_URL — correctly reads Turso, but only if the worker process is running
• db.ts lines 9-10: Uses TURSO_DB_URL env var — correct for Turso

Problem 2: Auto-Backup Not Working

Root Cause: The macclaw-logger.ts was the de facto "backup" mechanism — it scraped OpenClaw session JSONL files and wrote to NeonDB. But:

1. It targets NeonDB, not Turso (the active DB)
2. It's not scheduled as a LaunchAgent or cron job — it must be run manually (npx tsx scripts/macclaw-logger.ts --cron) or kept running in a terminal
3. The vice-reply.sh script saves reports to Turso but NOT telegram_messages
4. There is no dedicated auto-backup mechanism for telegram_messages in Turso

Problem 3: 101 Records with NULL received_at

Root Cause: The bot message handler in telegram.ts lines 201-206 does NOT set received_at:

INSERT INTO telegram_messages (id, chat_id, sender, bot, message_text, processing_status, injected_at, created_at)
VALUES (?, ?, 'ceo', ?, ?, 'injected', ?, ?)

The column received_at is omitted entirely. Only injected_at and created_at are set (both to nowEpoch() = Date.now()).

In contrast, the /telegram-log REST endpoint in index.ts line 213 DOES include received_at:

INSERT INTO telegram_messages (id, chat_id, sender, bot, message_text, processing_status, received_at, created_at)
VALUES (?, ?, ?, ?, ?, ?, ?, ?)

So messages saved by the bot handler have received_at = NULL, while messages saved via the REST endpoint have it set.

Problem 4: Sort Order Issues

Root Cause: The gatherState() function in scheduler.ts line 873 queries telegram_messages by processing_status but doesn't specify an ORDER BY. Other queries throughout the codebase use ORDER BY created_at — but for messages from the bot handler, created_at is set to Date.now() (the epoch when the worker saved it), NOT the actual Telegram message timestamp (msg.date). This means:

• Messages from different sources (bot vs REST vs macclaw-logger) have inconsistent timestamp semantics
• received_at is NULL for bot-saved messages, making it useless for sorting
• created_at represents "when saved to DB" not "when message was sent"

The Telegram API provides msg.date (Unix timestamp in seconds) which represents the actual send time. This should be used for received_at.

Implementation Tasks

Task 1: Fix telegram.ts — Add received_at to Bot INSERT

Files:

• Modify: projects/jarvis-system/apps/worker/src/telegram.ts:199-207

Step 1: Write the fix for the INSERT statement

Change the INSERT in telegram.ts to include received_at using the Telegram message's msg.date field (Unix seconds, needs conversion to milliseconds):

// telegram.ts, inside bot.on("message") handler, around line 199
// Save to DB (best-effort)
if (hasDb) {
  try {
    const receivedAt = msg.date ? msg.date * 1000 : nowEpoch();
    await withDbRetry(() =>
      dbExecute(
        `INSERT INTO telegram_messages (id, chat_id, sender, bot, message_text, processing_status, received_at, injected_at, created_at)
         VALUES (?, ?, 'ceo', ?, ?, 'injected', ?, ?, ?)`,
        [newId(), String(chatId), name, fullPrompt, receivedAt, nowEpoch(), nowEpoch()]
      ),
    );
  } catch (dbErr: unknown) {
    const dbMsg = dbErr instanceof Error ? dbErr.message : String(dbErr);
    console.error(`[${name}] DB save error (continuing):`, dbMsg);
  }
}

Step 2: Verify the change compiles

Run:

cd "/Users/nbs22/(Claude)/(claude).projects/business-builder/projects/jarvis-system/apps/worker"
npx tsc --noEmit

Expected: No errors

Step 3: Commit

git add projects/jarvis-system/apps/worker/src/telegram.ts
git commit -m "fix(worker): add received_at to telegram message INSERT using msg.date"

Task 2: Fix macclaw-logger — Migrate from NeonDB to Turso

Files:

• Modify: scripts/macclaw-logger.ts

Step 1: Replace NeonDB connection with Turso (@libsql/client)

The entire neonQuery() function and Neon config (lines 18-81) need to be replaced with a Turso client using @libsql/client. The script must also adapt:

• PostgreSQL $1, $2 parameterized queries to SQLite ?, ? positional params
• $3::timestamp casting to integer epoch (Turso stores timestamps as integer ms)
• BETWEEN timestamp - interval to epoch arithmetic
• UUID generation from gen_random_uuid() to crypto.randomUUID()
• received_at stored as epoch ms (not ISO string)

// Replace lines 18-81 with:
import { createClient, type Client } from "@libsql/client";

const TURSO_DB_URL = process.env.TURSO_DB_URL || "libsql://kanban-migkjy.aws-ap-northeast-1.turso.io";
const TURSO_DB_TOKEN = process.env.TURSO_DB_TOKEN || "eyJhbGciOiJFZERTQSIsInR5cCI6IkpXVCJ9.eyJhIjoicnciLCJnaWQiOiI0NDk0MjU5MC0wNWZlLTQ2MjMtYWYzNy05MzhlZDU4MGUyMDciLCJpYXQiOjE3NzE2ODk5NDUsInJpZCI6ImZlM2I4ZDE2LTQ1MjUtNGFlYS1hNTljLWJiN2E3ODAyYzNhMyJ9.melTmvkT5Vt07T2Ye2VwE-ljUyr_YOhNZSWjHwPMSYGXYjwZpuzEufeL_vLxFTOG5pcHp_blcTPK9ETYdBHBDw";

let _client: Client | null = null;

function getClient(): Client {
  if (!_client) {
    _client = createClient({ url: TURSO_DB_URL, authToken: TURSO_DB_TOKEN });
  }
  return _client;
}

async function tursoQuery(sql: string, args: unknown[] = []): Promise<Record<string, unknown>[]> {
  const client = getClient();
  const result = await client.execute({ sql, args: args as any });
  return result.rows as unknown as Record<string, unknown>[];
}

Step 2: Update the dedup query (run() function, ~line 170-175)

// Change from NeonDB PostgreSQL to Turso SQLite
// Old: "SELECT MAX(received_at) as last_ts FROM telegram_messages WHERE bot = $1 AND processing_status = 'logged'"
// New:
const lastSaved = await tursoQuery(
  "SELECT MAX(received_at) as last_ts FROM telegram_messages WHERE bot = ? AND processing_status = 'logged'",
  [BOT_NAME],
);
// received_at is now integer (epoch ms), not timestamp string
const lastEpoch = lastSaved[0]?.last_ts ? Number(lastSaved[0].last_ts) : null;

Step 3: Update the INSERT query (run() function, ~lines 208-228)

// Old: PostgreSQL $1-$5 with ::timestamp casting
// New: SQLite positional params with epoch ms
const receivedAtMs = new Date(msg.timestamp).getTime();

const existing = await tursoQuery(
  `SELECT id FROM telegram_messages
   WHERE bot = ? AND message_text = ?
     AND received_at BETWEEN ? AND ?
   LIMIT 1`,
  [BOT_NAME, text, receivedAtMs - 1000, receivedAtMs + 1000],
);

if (existing.length > 0) { skipped++; continue; }

const id = crypto.randomUUID();
await tursoQuery(
  `INSERT INTO telegram_messages (id, chat_id, sender, bot, message_text, processing_status, received_at, created_at)
   VALUES (?, ?, ?, ?, ?, 'logged', ?, ?)`,
  [id, CHAT_ID, sender, BOT_NAME, text, receivedAtMs, Date.now()],
);
inserted++;

Step 4: Update the filter for new messages

// Old: compare ISO strings
// New: compare epoch ms
const newMessages = lastEpoch
  ? allMessages.filter((m) => new Date(m.timestamp).getTime() > lastEpoch)
  : allMessages;

Step 5: Build and test

Run:

cd "/Users/nbs22/(Claude)/(claude).projects/business-builder"
npx tsx scripts/macclaw-logger.ts

Expected: Connects to Turso, reports message count, inserts or skips as appropriate

Step 6: Commit

git add scripts/macclaw-logger.ts
git commit -m "fix(macclaw-logger): migrate from NeonDB to Turso for telegram_messages"

Task 3: Backfill NULL received_at Records

Files:

• Create: scripts/backfill-received-at.ts (one-time migration script)

Step 1: Write the backfill script

This script will:

1. Find all telegram_messages where received_at IS NULL
2. Set received_at = injected_at if injected_at is not null (best available proxy for receive time)
3. Set received_at = created_at as fallback

#!/usr/bin/env npx tsx
/**
 * One-time backfill: set received_at for telegram_messages where it's NULL.
 * Uses injected_at as best proxy, falls back to created_at.
 */
import { createClient } from "@libsql/client";

const TURSO_DB_URL = process.env.TURSO_DB_URL || "libsql://kanban-migkjy.aws-ap-northeast-1.turso.io";
const TURSO_DB_TOKEN = process.env.TURSO_DB_TOKEN || "eyJhbGciOiJFZERTQSIsInR5cCI6IkpXVCJ9.eyJhIjoicnciLCJnaWQiOiI0NDk0MjU5MC0wNWZlLTQ2MjMtYWYzNy05MzhlZDU4MGUyMDciLCJpYXQiOjE3NzE2ODk5NDUsInJpZCI6ImZlM2I4ZDE2LTQ1MjUtNGFlYS1hNTljLWJiN2E3ODAyYzNhMyJ9.melTmvkT5Vt07T2Ye2VwE-ljUyr_YOhNZSWjHwPMSYGXYjwZpuzEufeL_vLxFTOG5pcHp_blcTPK9ETYdBHBDw";

async function main() {
  const client = createClient({ url: TURSO_DB_URL, authToken: TURSO_DB_TOKEN });

  // Count NULL received_at records
  const countResult = await client.execute({
    sql: "SELECT count(*) as cnt FROM telegram_messages WHERE received_at IS NULL",
    args: [],
  });
  const nullCount = Number((countResult.rows[0] as any)?.cnt ?? 0);
  console.log(`[backfill] Found ${nullCount} records with NULL received_at`);

  if (nullCount === 0) {
    console.log("[backfill] Nothing to do.");
    return;
  }

  // Backfill: use injected_at first, then created_at as fallback
  const result = await client.execute({
    sql: `UPDATE telegram_messages
          SET received_at = COALESCE(injected_at, created_at)
          WHERE received_at IS NULL`,
    args: [],
  });
  console.log(`[backfill] Updated ${result.rowsAffected} records`);

  // Verify
  const verifyResult = await client.execute({
    sql: "SELECT count(*) as cnt FROM telegram_messages WHERE received_at IS NULL",
    args: [],
  });
  const remaining = Number((verifyResult.rows[0] as any)?.cnt ?? 0);
  console.log(`[backfill] Remaining NULL received_at: ${remaining}`);
}

main().catch((err) => {
  console.error("[backfill] Fatal:", err);
  process.exit(1);
});

Step 2: Run the backfill

cd "/Users/nbs22/(Claude)/(claude).projects/business-builder"
npx tsx scripts/backfill-received-at.ts

Expected output:

[backfill] Found 101 records with NULL received_at
[backfill] Updated 101 records
[backfill] Remaining NULL received_at: 0

Step 3: Commit

git add scripts/backfill-received-at.ts
git commit -m "fix(db): backfill NULL received_at in telegram_messages using injected_at/created_at"

Task 4: Fix Sort Order — Use received_at as Primary Sort Key

Files:

• Modify: projects/jarvis-system/apps/worker/src/scheduler.ts (line 873 area)

Step 1: Verify current queries that read telegram_messages

Check all queries that read from telegram_messages across the codebase:

1. scheduler.ts:873 — gatherState(): SELECT count(*) ... WHERE processing_status IN ('pending', 'injected') — This is a count, no sort needed. OK.

2. Any dashboard queries in apps/kanban/ — Check for ORDER BY created_at that should be ORDER BY received_at.

Run:

grep -rn "telegram_messages" projects/jarvis-system/apps/kanban/src/ --include="*.ts"

Step 2: Update kanban dashboard queries (if applicable)

If any kanban Server Action queries telegram_messages with ORDER BY created_at, change to ORDER BY received_at DESC (or ORDER BY COALESCE(received_at, created_at) DESC for safety).

Look specifically at:

• projects/kanban-dashboard/src/actions/ directory
• Any /api/ route that returns telegram messages

-- Before (incorrect sort — uses DB save time)
SELECT * FROM telegram_messages ORDER BY created_at DESC;

-- After (correct sort — uses actual message receive time)
SELECT * FROM telegram_messages ORDER BY received_at DESC;

Step 3: Commit

git add -A
git commit -m "fix(kanban): sort telegram_messages by received_at instead of created_at"

Task 5: Add Worker Auto-Restart (LaunchAgent)

Files:

• Create: scripts/launchagents/com.jarvis.worker.plist (macOS LaunchAgent)

Step 1: Create the LaunchAgent plist

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE plist PUBLIC "-//Apple//DTD PLIST 1.0//EN" "http://www.apple.com/DTDs/PropertyList-1.0.dtd">
<plist version="1.0">
<dict>
    <key>Label</key>
    <string>com.jarvis.worker</string>
    <key>ProgramArguments</key>
    <array>
        <string>/Users/nbs22/.nvm/versions/node/v22.12.0/bin/node</string>
        <string>dist/index.js</string>
    </array>
    <key>WorkingDirectory</key>
    <string>/Users/nbs22/(Claude)/(claude).projects/business-builder/projects/jarvis-system/apps/worker</string>
    <key>EnvironmentVariables</key>
    <dict>
        <key>PATH</key>
        <string>/Users/nbs22/.nvm/versions/node/v22.12.0/bin:/usr/local/bin:/usr/bin:/bin</string>
        <key>DOTENV_CONFIG_PATH</key>
        <string>/Users/nbs22/(Claude)/(claude).projects/business-builder/projects/jarvis-system/apps/worker/.env</string>
    </dict>
    <key>RunAtLoad</key>
    <true/>
    <key>KeepAlive</key>
    <true/>
    <key>StandardOutPath</key>
    <string>/tmp/jarvis-worker.stdout.log</string>
    <key>StandardErrorPath</key>
    <string>/tmp/jarvis-worker.stderr.log</string>
</dict>
</plist>

Step 2: Install instructions

# Copy to LaunchAgents directory
cp scripts/launchagents/com.jarvis.worker.plist ~/Library/LaunchAgents/

# Build the worker first
cd "/Users/nbs22/(Claude)/(claude).projects/business-builder/projects/jarvis-system/apps/worker"
npm run build

# Load the agent
launchctl load ~/Library/LaunchAgents/com.jarvis.worker.plist

# Verify it's running
launchctl list | grep jarvis.worker
curl http://localhost:5555/status

Step 3: Commit

git add scripts/launchagents/com.jarvis.worker.plist
git commit -m "feat(worker): add macOS LaunchAgent for auto-restart on crash/reboot"

Task 6: Verify End-to-End — Send Test Message and Confirm DB Save

Step 1: Ensure worker is running

curl http://localhost:5555/status

Expected: {"bots":["vice-claude","contents-builder"],"sessions":[...],"dbWatcher":"enabled"}

Step 2: Send a test message via Telegram bot

Send any text message to the vice-claude Telegram bot from the CEO account.

Step 3: Verify message saved to Turso

cd "/Users/nbs22/(Claude)/(claude).projects/business-builder/projects/jarvis-system/apps/worker"
TURSO_DB_URL="libsql://kanban-migkjy.aws-ap-northeast-1.turso.io" \
TURSO_DB_TOKEN="eyJhbGciOiJFZERTQSIsInR5cCI6IkpXVCJ9.eyJhIjoicnciLCJnaWQiOiI0NDk0MjU5MC0wNWZlLTQ2MjMtYWYzNy05MzhlZDU4MGUyMDciLCJpYXQiOjE3NzE2ODk5NDUsInJpZCI6ImZlM2I4ZDE2LTQ1MjUtNGFlYS1hNTljLWJiN2E3ODAyYzNhMyJ9.melTmvkT5Vt07T2Ye2VwE-ljUyr_YOhNZSWjHwPMSYGXYjwZpuzEufeL_vLxFTOG5pcHp_blcTPK9ETYdBHBDw" \
npx tsx -e "
import { createClient } from '@libsql/client';
const c = createClient({ url: process.env.TURSO_DB_URL!, authToken: process.env.TURSO_DB_TOKEN });
const r = await c.execute({ sql: 'SELECT id, chat_id, sender, bot, received_at, created_at FROM telegram_messages ORDER BY created_at DESC LIMIT 3', args: [] });
console.log(JSON.stringify(r.rows, null, 2));
"

Expected: Latest message appears with both received_at and created_at populated.

Step 4: Verify sort order

# Same script but with ORDER BY received_at DESC
# Verify received_at is not NULL and messages are in chronological order

Step 5: Commit verification report (optional)

No code change needed. This is a manual verification step.

Summary of Changes

Risk Assessment

Prerequisites

• Worker must be built (npm run build in apps/worker/)
• @libsql/client must be available (already in worker's node_modules)
• Turso DB credentials must be in .env or exported

Plan complete and saved to docs/plans/2026-02-25-telegram-fix.md. Two execution options:

1. Subagent-Driven (this session) - I dispatch fresh subagent per task, review between tasks, fast iteration

2. Parallel Session (separate) - Open new session with executing-plans, batch execution with checkpoints

Which approach?