Function generateSessionIndex

packages/core/src/query/session-queries.ts:588–653

(
  db: DatabaseAdapter,
  gapThreshold: number = DEFAULT_SESSION_GAP_THRESHOLD,
  onProgress?: (current: number, total: number) => void
)

 * @returns number of sessions created
 */
export function generateSessionIndex(
  db: DatabaseAdapter,
  gapThreshold: number = DEFAULT_SESSION_GAP_THRESHOLD,
  onProgress?: (current: number, total: number) => void
): number {
  const countRow = db.prepare('SELECT COUNT(*) as count FROM message').get() as { count: number } | undefined
  if (!countRow || countRow.count === 0) return 0

  const sessionMarkSQL = `
    WITH message_ordered AS (
      SELECT id, ts, LAG(ts) OVER (ORDER BY ts, id) AS prev_ts FROM message
    ),
    session_marks AS (
      SELECT id, ts,
        CASE WHEN prev_ts IS NULL OR (ts - prev_ts) > ? THEN 1 ELSE 0 END AS is_new_session
      FROM message_ordered
    ),
    session_ids AS (
      SELECT id, ts, SUM(is_new_session) OVER (ORDER BY ts, id) AS session_num
      FROM session_marks
    )
    SELECT id, ts, session_num FROM session_ids
  `

  const messages = db.prepare(sessionMarkSQL).all(gapThreshold) as Array<{
    id: number
    ts: number
    session_num: number
  }>

  if (messages.length === 0) return 0

  const sessionMap = new Map<number, { startTs: number; endTs: number; messageIds: number[] }>()
  for (const msg of messages) {
    const session = sessionMap.get(msg.session_num)
    if (!session) {
      sessionMap.set(msg.session_num, { startTs: msg.ts, endTs: msg.ts, messageIds: [msg.id] })
    } else {
      session.endTs = msg.ts
      session.messageIds.push(msg.id)
    }
  }

  const insertSession = db.prepare(
    'INSERT INTO segment (start_ts, end_ts, message_count, is_manual, summary) VALUES (?, ?, ?, 0, NULL)'
  )
  const insertContext = db.prepare('INSERT INTO message_context (message_id, segment_id, topic_id) VALUES (?, ?, NULL)')

  return db.transaction(() => {
    clearSessionIndexRows(db)

    let processed = 0
    const total = sessionMap.size
    for (const [, data] of sessionMap) {
      const result = insertSession.run(data.startTs, data.endTs, data.messageIds.length)
      const newId = (result.lastInsertRowid ?? 0) as number
      for (const mid of data.messageIds) {
        insertContext.run(mid, newId)