JCV's Portfolio

src/store/storeMessages.ts

// Extracted Store Methods import type { Database } from "bun:sqlite";

import { clamp, nowIso } from "../utils.ts"; import { normalizeMessageCreatedAt } from "./storeHelpers.ts"; import { normalizeEmbeddingVector, vectorToBlob } from "./storeHelpers.ts";

// English fallback stopwords used when tokenizing conversation search queries. const EN_CONVERSATION_SEARCH_STOPWORDS = new Set([ "about", "again", "anything", "before", "could", "found", "hello", "just", "know", "like", "look", "looking", "maybe", "remember", "said", "something", "talk", "talked", "tell", "that", "them", "then", "there", "they", "this", "those", "today", "what", "when", "where", "which", "without", "would", "yeah", "yesterday", "your" ]);

// Storage truncation and query-window defaults for message rows. const AUTHOR_NAME_MAX_CHARS = 80; const MESSAGE_CONTENT_MAX_CHARS = 2000; const DEFAULT_RECENT_MESSAGES_LIMIT = 40; const MIN_RECENT_MESSAGES_LIMIT = 1; const MAX_RECENT_MESSAGES_LIMIT = 200; const DEFAULT_RECENT_GUILD_MESSAGES_LIMIT = 120; const MAX_RECENT_GUILD_MESSAGES_LIMIT = 300; const DEFAULT_WINDOW_QUERY_LIMIT = 120; const MAX_WINDOW_QUERY_LIMIT = 500;

// Relevant/conversation search clamps exposed to callers. const DEFAULT_RELEVANT_SEARCH_LIMIT = 8; const MAX_RELEVANT_SEARCH_LIMIT = 24; const MAX_RELEVANT_SEARCH_TOKENS = 5; const MAX_CONVERSATION_SEARCH_TOKENS = 8; const CONVERSATION_PHRASE_MAX_CHARS = 180; const DEFAULT_CONVERSATION_WINDOW_LIMIT = 4; const MAX_CONVERSATION_WINDOW_LIMIT = 8; const DEFAULT_CONVERSATION_MAX_AGE_HOURS = 168; const MAX_CONVERSATION_MAX_AGE_HOURS = 24 * 30;

// Candidate-pool sizing for lexical vs semantic conversation retrieval. const CONVERSATION_CANDIDATE_MULTIPLIER_LEXICAL = 20; const CONVERSATION_CANDIDATE_LIMIT_LEXICAL = 160; const CONVERSATION_CANDIDATE_MULTIPLIER_SEMANTIC = 24; const CONVERSATION_CANDIDATE_LIMIT_SEMANTIC = 192;

// Recency tiers and scoring weights used in lexical ranking. const MINUTES_TO_MS = 60_000; const RECENCY_TIER_RECENT_MINUTES = 30; const RECENCY_TIER_MID_MINUTES = 6 * 60; const RECENCY_TIER_DAY_MINUTES = 24 * 60; const SCORE_EXACT_PHRASE_MATCH = 10; const SCORE_TOKEN_MATCH = 3; const SCORE_AUTHOR_MATCH = 1; const SCORE_SAME_CHANNEL = 4; const SCORE_RECENT_ACTIVITY = 3; const SCORE_MID_ACTIVITY = 2; const SCORE_OLDER_ACTIVITY = 1;

// Semantic reranking nudges and minimum quality gate. const SEMANTIC_CHANNEL_BOOST = 0.08; const SEMANTIC_RECENCY_BOOST_RECENT = 0.05; const SEMANTIC_RECENCY_BOOST_MID = 0.03; const SEMANTIC_RECENCY_BOOST_DAY = 0.015; const SEMANTIC_SCORE_FLOOR = 0.12;

// Active-channel summary defaults used by dashboard/status surfaces. const DEFAULT_ACTIVE_CHANNEL_HOURS = 24; const DEFAULT_ACTIVE_CHANNEL_LIMIT = 10; const MAX_ACTIVE_CHANNEL_LIMIT = 50;

interface MessageStore { db: Database; sqliteVecReady?: boolean | null; sqliteVecError?: string; ensureSqliteVecReady?: () => boolean; }

interface MessageSqlRow { message_id: string; created_at: string; guild_id?: string | null; channel_id: string; author_id: string; author_name: string; is_bot: number; content: string; referenced_message_id?: string | null; }

interface StoredMessageRow extends Record<string, unknown> { message_id: string; created_at: string; guild_id?: string | null; channel_id: string; author_id: string; author_name: string; is_bot: boolean; content: string; referenced_message_id?: string | null; }

interface ConversationMessageRow extends MessageSqlRow { guild_id: string | null; }

interface ActiveChannelRow { channel_id: string; message_count: number; }

interface ReferencedMessageStatsRow { referenced_message_id: string; reaction_count: number; reply_count: number; }

interface MessageVectorScoreRow extends ConversationMessageRow { score: number; }

function mapStoredMessageRow(row: MessageSqlRow): StoredMessageRow { return { ...row, is_bot: row.is_bot === 1 }; }

export function recordMessage(store: MessageStore, message) { const createdAt = normalizeMessageCreatedAt( message?.createdAt ?? message?.created_at ?? message?.createdTimestamp ); store.db .prepare( INSERT INTO messages( message_id, created_at, guild_id, channel_id, author_id, author_name, is_bot, content, referenced_message_id ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?) ON CONFLICT(message_id) DO UPDATE SET guild_id = excluded.guild_id, channel_id = excluded.channel_id, author_id = excluded.author_id, author_name = excluded.author_name, is_bot = excluded.is_bot, content = excluded.content, referenced_message_id = excluded.referenced_message_id ) .run( String(message.messageId), createdAt, message.guildId ? String(message.guildId) : null, String(message.channelId), String(message.authorId), String(message.authorName).slice(0, AUTHOR_NAME_MAX_CHARS), message.isBot ? 1 : 0, String(message.content ?? "").slice(0, MESSAGE_CONTENT_MAX_CHARS), message.referencedMessageId ? String(message.referencedMessageId) : null ); }

export function upsertMessageVectorNative( store: MessageStore, { messageId, model, embedding, updatedAt = nowIso() }: { messageId: string; model: string; embedding: number[]; updatedAt?: string; } ) { const normalizedMessageId = String(messageId || "").trim(); const normalizedModel = String(model || "").trim(); const vector = normalizeEmbeddingVector(embedding); if (!normalizedMessageId || !normalizedModel || !vector.length) return false;

const result = store.db .prepare( INSERT INTO message_vectors_native(message_id, model, dims, embedding_blob, updated_at) VALUES (?, ?, ?, ?, ?) ON CONFLICT(message_id, model) DO UPDATE SET dims = excluded.dims, embedding_blob = excluded.embedding_blob, updated_at = excluded.updated_at ) .run( normalizedMessageId, normalizedModel, vector.length, vectorToBlob(vector), String(updatedAt || nowIso()) );

return Number(result?.changes || 0) > 0; }

export function deleteMessagesForGuild(store: MessageStore, guildId: string) { const normalizedGuildId = String(guildId || "").trim(); if (!normalizedGuildId) { return { ok: false, reason: "guild_required", messagesDeleted: 0, vectorsDeleted: 0 } as const; }

const deleteTx = store.db.transaction((targetGuildId: string) => { const vectorsDeleted = Number( store.db .prepare( DELETE FROM message_vectors_native WHERE message_id IN ( SELECT message_id FROM messages WHERE guild_id = ? ) ) .run(targetGuildId)?.changes || 0 ); const messagesDeleted = Number( store.db .prepare( DELETE FROM messages WHERE guild_id = ? ) .run(targetGuildId)?.changes || 0 ); return { messagesDeleted, vectorsDeleted }; });

const result = deleteTx(normalizedGuildId); return { ok: true, reason: "deleted", ...result } as const; }

export function getRecentMessages(store: MessageStore, channelId, limit = DEFAULT_RECENT_MESSAGES_LIMIT) { return store.db .prepare<MessageSqlRow, [string, number]>( SELECT message_id, created_at, guild_id, channel_id, author_id, author_name, is_bot, content, referenced_message_id FROM messages WHERE channel_id = ? ORDER BY created_at DESC LIMIT ? ) .all(String(channelId), clamp(Math.floor(limit), MIN_RECENT_MESSAGES_LIMIT, MAX_RECENT_MESSAGES_LIMIT)) .map(mapStoredMessageRow); }

export function getRecentMessagesAcrossGuild(store: MessageStore, guildId, limit = DEFAULT_RECENT_GUILD_MESSAGES_LIMIT) { return store.db .prepare<MessageSqlRow, [string, number]>( SELECT message_id, created_at, guild_id, channel_id, author_id, author_name, is_bot, content, referenced_message_id FROM messages WHERE guild_id = ? ORDER BY created_at DESC LIMIT ? ) .all(String(guildId), clamp(Math.floor(limit), MIN_RECENT_MESSAGES_LIMIT, MAX_RECENT_GUILD_MESSAGES_LIMIT)) .map(mapStoredMessageRow); }

export function getMessagesInWindow( store: MessageStore, { guildId, channelId = null, sinceIso = null, untilIso = null, limit = DEFAULT_WINDOW_QUERY_LIMIT }: { guildId: string; channelId?: string | null; sinceIso?: string | null; untilIso?: string | null; limit?: number; } ) { const normalizedGuildId = String(guildId || "").trim(); if (!normalizedGuildId) return [];

return store.db .prepare<MessageSqlRow, Array<string | number>>( SELECT message_id, created_at, guild_id, channel_id, author_id, author_name, is_bot, content, referenced_message_id FROM messages WHERE ${where.join(" AND ")} ORDER BY created_at ASC LIMIT ? ) .all(...args, clamp(Math.floor(Number(limit) || DEFAULT_WINDOW_QUERY_LIMIT), MIN_RECENT_MESSAGES_LIMIT, MAX_WINDOW_QUERY_LIMIT)) .map(mapStoredMessageRow); }

export function searchRelevantMessages(store: MessageStore, channelId, queryText, limit = DEFAULT_RELEVANT_SEARCH_LIMIT) { const raw = String(queryText ?? "").toLowerCase(); const tokens = [...new Set(raw.match(/[a-z0-9]{4,}/g) ?? [])].slice(0, MAX_RELEVANT_SEARCH_TOKENS);

if (!tokens.length) { return store.db .prepare<MessageSqlRow, [string, number]>( SELECT message_id, created_at, channel_id, author_id, author_name, is_bot, content FROM messages WHERE channel_id = ? AND is_bot = 0 ORDER BY created_at DESC LIMIT ? ) .all(String(channelId), clamp(limit, MIN_RECENT_MESSAGES_LIMIT, MAX_RELEVANT_SEARCH_LIMIT)) .map(mapStoredMessageRow); }

const clauses = tokens.map(() => "content LIKE ?").join(" OR "); const args = [ String(channelId), ...tokens.map((t) => %${t}%), clamp(limit, MIN_RECENT_MESSAGES_LIMIT, MAX_RELEVANT_SEARCH_LIMIT) ];

return store.db .prepare<MessageSqlRow, Array<string | number>>( SELECT message_id, created_at, channel_id, author_id, author_name, is_bot, content FROM messages WHERE channel_id = ? AND is_bot = 0 AND (${clauses}) ORDER BY created_at DESC LIMIT ? ) .all(...args) .map(mapStoredMessageRow); }

function normalizeConversationSearchTokens(queryText) { const raw = String(queryText || "") .toLowerCase() .replace(/\s+/g, " ") .trim(); if (!raw) return [];

return [...new Set(raw.match(/[a-z0-9]{3,}/g) || [])] .filter((token) => !EN_CONVERSATION_SEARCH_STOPWORDS.has(token)) .slice(0, MAX_CONVERSATION_SEARCH_TOKENS); }

function scoreConversationMessage(row, { tokens = [], phrase = "", channelId = null } = {}) { const content = String(row?.content || "").toLowerCase(); const authorName = String(row?.author_name || "").toLowerCase(); const rowChannelId = String(row?.channel_id || "").trim(); const normalizedChannelId = String(channelId || "").trim(); const createdAtMs = Date.parse(String(row?.created_at || "")); const ageMinutes = Number.isFinite(createdAtMs) ? Math.max(0, Math.round((Date.now() - createdAtMs) / MINUTES_TO_MS)) : null;

let score = 0; if (phrase && content.includes(phrase)) { score += SCORE_EXACT_PHRASE_MATCH; }

for (const token of tokens) { if (!token) continue; if (content.includes(token)) { score += SCORE_TOKEN_MATCH; continue; } if (authorName.includes(token)) { score += SCORE_AUTHOR_MATCH; } }

if (normalizedChannelId && rowChannelId && rowChannelId === normalizedChannelId) { score += SCORE_SAME_CHANNEL; } if (Number.isFinite(ageMinutes)) { if (ageMinutes <= RECENCY_TIER_RECENT_MINUTES) { score += SCORE_RECENT_ACTIVITY; } else if (ageMinutes <= RECENCY_TIER_MID_MINUTES) { score += SCORE_MID_ACTIVITY; } else if (ageMinutes <= RECENCY_TIER_DAY_MINUTES) { score += SCORE_OLDER_ACTIVITY; } }

return { score, ageMinutes }; }

function fetchConversationWindowRows( store: MessageStore, anchorRow: Pick<ConversationMessageRow, "message_id" | "channel_id" | "created_at">, before = 1, after = 1 ) { if (!anchorRow?.message_id || !anchorRow?.channel_id || !anchorRow?.created_at) return [];

const boundedBefore = clamp(Math.floor(Number(before) || 1), 0, 4); const boundedAfter = clamp(Math.floor(Number(after) || 1), 0, 4); const channelId = String(anchorRow.channel_id); const createdAt = String(anchorRow.created_at); const messageId = String(anchorRow.message_id);

const beforeRows = boundedBefore > 0 ? store.db .prepare<ConversationMessageRow, [string, string, number]>( SELECT message_id, created_at, guild_id, channel_id, author_id, author_name, is_bot, content FROM messages WHERE channel_id = ? AND created_at < ? ORDER BY created_at DESC LIMIT ? ) .all(channelId, createdAt, boundedBefore) .reverse() : [];

const anchorRows = store.db .prepare<ConversationMessageRow, [string]>( SELECT message_id, created_at, guild_id, channel_id, author_id, author_name, is_bot, content FROM messages WHERE message_id = ? LIMIT 1 ) .all(messageId);

const afterRows = boundedAfter > 0 ? store.db .prepare<ConversationMessageRow, [string, string, number]>( SELECT message_id, created_at, guild_id, channel_id, author_id, author_name, is_bot, content FROM messages WHERE channel_id = ? AND created_at > ? ORDER BY created_at ASC LIMIT ? ) .all(channelId, createdAt, boundedAfter) : [];

return [...beforeRows, ...anchorRows, ...afterRows]; }

type RankedConversationWindowRow = { message_id: string; created_at: string; guild_id: string | null; channel_id: string | null; author_id: string | null; author_name: string | null; _score: number; _ageMinutes: number | null; score?: number; };

function mapConversationWindowMessages(messages: ConversationMessageRow[]) { return messages.map((entry) => ({ message_id: String(entry?.message_id || "").trim(), created_at: String(entry?.created_at || "").trim(), guild_id: String(entry?.guild_id || "").trim() || null, channel_id: String(entry?.channel_id || "").trim() || null, author_id: String(entry?.author_id || "").trim() || null, author_name: String(entry?.author_name || "").trim() || "unknown", is_bot: Number(entry?.is_bot) === 1 ? 1 : 0, content: String(entry?.content || "").trim() })); }

for (const row of rankedRows) { if (windows.length >= boundedLimit) break; if (usedMessageIds.has(String(row.message_id || ""))) continue; const messages = fetchConversationWindowRows(store, row, before, after); if (!messages.length) continue; const messageIds = messages .map((entry) => String(entry?.message_id || "").trim()) .filter(Boolean); if (!messageIds.length) continue; if (messageIds.some((messageId) => usedMessageIds.has(messageId))) continue; for (const messageId of messageIds) { usedMessageIds.add(messageId); }

const window = {
  anchorMessageId: String(row.message_id || "").trim(),
  createdAt: String(row.created_at || "").trim(),
  guildId: String(row.guild_id || "").trim() || null,
  channelId: String(row.channel_id || "").trim() || null,
  authorId: String(row.author_id || "").trim() || null,
  authorName: String(row.author_name || "").trim() || null,
  ageMinutes: row._ageMinutes ?? null,
  score: includeSemanticScore
    ? Number(Number(row._score || 0).toFixed(6))
    : Number(row._score || 0),
  ...(includeSemanticScore
    ? { semanticScore: Number(Number(row.score || 0).toFixed(6)) }
    : {}),
  messages: mapConversationWindowMessages(messages)
};
windows.push(window);

}

return windows; }

export function searchConversationWindows(store: MessageStore, { guildId, channelId = null, queryText = "", limit = DEFAULT_CONVERSATION_WINDOW_LIMIT, maxAgeHours = DEFAULT_CONVERSATION_MAX_AGE_HOURS, before = 1, after = 1 }: { guildId?: string | null; channelId?: string | null; queryText?: string; limit?: number; maxAgeHours?: number; before?: number; after?: number; }) { const normalizedGuildId = String(guildId || "").trim() || null; const normalizedChannelId = String(channelId || "").trim() || null; if (!normalizedGuildId && !normalizedChannelId) return [];

const tokens = normalizeConversationSearchTokens(queryText); const normalizedPhrase = String(queryText || "") .toLowerCase() .replace(/\s+/g, " ") .trim() .slice(0, CONVERSATION_PHRASE_MAX_CHARS); if (!tokens.length) return [];

const rows = store.db .prepare<ConversationMessageRow, Array<string | number>>( SELECT message_id, created_at, guild_id, channel_id, author_id, author_name, is_bot, content FROM messages WHERE ${where.join(" AND ")} ORDER BY created_at DESC LIMIT ? ) .all(...args);

if (!rows.length) return []; const rankedRows = rows .map((row, index) => { const scored = scoreConversationMessage(row, { tokens, phrase: normalizedPhrase, channelId: normalizedChannelId }); return { ...row, _score: scored.score, _ageMinutes: scored.ageMinutes, _rank: index }; }) .filter((row) => row._score > 0) .sort((a, b) => { if (b._score !== a._score) return b._score - a._score; return a._rank - b._rank; });

return assembleConversationWindows(store, rankedRows, { limit: boundedLimit, before, after }); }

export function searchConversationWindowsByEmbedding( store: MessageStore, { guildId, channelId = null, queryEmbedding, model, limit = DEFAULT_CONVERSATION_WINDOW_LIMIT, maxAgeHours = DEFAULT_CONVERSATION_MAX_AGE_HOURS, before = 1, after = 1 }: { guildId?: string | null; channelId?: string | null; queryEmbedding: number[]; model: string; limit?: number; maxAgeHours?: number; before?: number; after?: number; } ) { if (typeof store.ensureSqliteVecReady !== "function" || !store.ensureSqliteVecReady()) return [];

const normalizedGuildId = String(guildId || "").trim() || null; const normalizedChannelId = String(channelId || "").trim() || null; const normalizedModel = String(model || "").trim(); const normalizedQueryEmbedding = normalizeEmbeddingVector(queryEmbedding); if ((!normalizedGuildId && !normalizedChannelId) || !normalizedModel || !normalizedQueryEmbedding.length) return [];

return assembleConversationWindows(store, rankedRows, { limit: boundedLimit, before, after, includeSemanticScore: true }); }

export function getActiveChannels(store: MessageStore, guildId, hours = DEFAULT_ACTIVE_CHANNEL_HOURS, limit = DEFAULT_ACTIVE_CHANNEL_LIMIT) { const since = new Date(Date.now() - hours * 60 * 60 * 1000).toISOString();

return store.db .prepare<ActiveChannelRow, [string, string, number]>( SELECT channel_id, COUNT(*) AS message_count FROM messages WHERE guild_id = ? AND is_bot = 0 AND created_at >= ? GROUP BY channel_id ORDER BY message_count DESC LIMIT ? ) .all(String(guildId), since, clamp(limit, MIN_RECENT_MESSAGES_LIMIT, MAX_ACTIVE_CHANNEL_LIMIT)); }

export function getReferencedMessageStats( store: MessageStore, { messageIds, guildId = null, sinceIso = null }: { messageIds: string[]; guildId?: string | null; sinceIso?: string | null; } ) { const normalizedMessageIds = [...new Set( (Array.isArray(messageIds) ? messageIds : []) .map((value) => String(value || "").trim()) .filter(Boolean) )]; if (!normalizedMessageIds.length) return [];

const params: Array<string | number> = []; const conditions = [ referenced_message_id IN (${normalizedMessageIds.map(() => "?").join(", ")}), "is_bot = 0" ]; params.push(...normalizedMessageIds);

const normalizedGuildId = String(guildId || "").trim(); if (normalizedGuildId) { conditions.push("guild_id = ?"); params.push(normalizedGuildId); }

const normalizedSinceIso = String(sinceIso || "").trim(); if (normalizedSinceIso) { conditions.push("created_at >= ?"); params.push(normalizedSinceIso); }

return store.db .prepare<ReferencedMessageStatsRow, Array<string | number>>( SELECT referenced_message_id, SUM(CASE WHEN message_id LIKE 'reaction:%' THEN 1 ELSE 0 END) AS reaction_count, SUM(CASE WHEN message_id LIKE 'reaction:%' THEN 0 ELSE 1 END) AS reply_count FROM messages WHERE ${conditions.join(" AND ")} GROUP BY referenced_message_id ) .all(...params) .map((row) => ({ referenced_message_id: String(row?.referenced_message_id || "").trim(), reaction_count: Number(row?.reaction_count || 0), reply_count: Number(row?.reply_count || 0) })); }