slices / packages / session / src / adapters / sqlite.ts

import type { SessionAdapter, SessionData } from "../types.ts";
import { DatabaseSync } from "node:sqlite";

interface SessionTable {
  session_id: string;
  user_id: string;
  handle: string | null;
  is_authenticated: number;
  data: string | null;
  created_at: number;
  expires_at: number;
  last_accessed_at: number;
}

export class SQLiteAdapter implements SessionAdapter {
  private db: DatabaseSync;

  constructor(databasePath: string) {
    // Handle sqlite:// URLs or direct paths
    const dbPath = databasePath.startsWith("sqlite://")
      ? databasePath.slice(9)
      : databasePath;

    this.db = new DatabaseSync(dbPath);
    this.initializeDatabase();
  }

  private initializeDatabase() {
    this.db.exec(`
      CREATE TABLE IF NOT EXISTS sessions (
        session_id TEXT PRIMARY KEY,
        user_id TEXT NOT NULL,
        handle TEXT,
        is_authenticated INTEGER NOT NULL DEFAULT 1,
        data TEXT, -- JSON string
        created_at INTEGER NOT NULL,
        expires_at INTEGER NOT NULL,
        last_accessed_at INTEGER NOT NULL
      )
    `);

    // Index for cleanup operations
    this.db.exec(`
      CREATE INDEX IF NOT EXISTS idx_sessions_expires_at
      ON sessions(expires_at)
    `);

    // Index for user lookups
    this.db.exec(`
      CREATE INDEX IF NOT EXISTS idx_sessions_user_id
      ON sessions(user_id)
    `);
  }

  get(sessionId: string): Promise<SessionData | null> {
    const stmt = this.db.prepare(`
      SELECT * FROM sessions 
      WHERE session_id = ?
    `);

    const row = stmt.get(sessionId) as SessionTable | undefined;
    if (!row) return Promise.resolve(null);

    return Promise.resolve(this.rowToSessionData(row));
  }

  set(sessionId: string, data: SessionData): Promise<void> {
    const stmt = this.db.prepare(`
      INSERT OR REPLACE INTO sessions 
      (session_id, user_id, handle, is_authenticated, data, created_at, expires_at, last_accessed_at)
      VALUES (?, ?, ?, ?, ?, ?, ?, ?)
    `);

    stmt.run(
      sessionId,
      data.userId,
      data.handle || null,
      data.isAuthenticated ? 1 : 0,
      data.data ? JSON.stringify(data.data) : null,
      data.createdAt,
      data.expiresAt,
      data.lastAccessedAt
    );
    return Promise.resolve();
  }

  update(sessionId: string, updates: Partial<SessionData>): Promise<boolean> {
    const setParts: string[] = [];
    const values: (string | number | null)[] = [];

    if (updates.userId !== undefined) {
      setParts.push("user_id = ?");
      values.push(updates.userId);
    }

    if (updates.handle !== undefined) {
      setParts.push("handle = ?");
      values.push(updates.handle);
    }

    if (updates.isAuthenticated !== undefined) {
      setParts.push("is_authenticated = ?");
      values.push(updates.isAuthenticated ? 1 : 0);
    }

    if (updates.data !== undefined) {
      setParts.push("data = ?");
      values.push(updates.data ? JSON.stringify(updates.data) : null);
    }

    if (updates.expiresAt !== undefined) {
      setParts.push("expires_at = ?");
      values.push(updates.expiresAt);
    }

    if (updates.lastAccessedAt !== undefined) {
      setParts.push("last_accessed_at = ?");
      values.push(updates.lastAccessedAt);
    }

    if (setParts.length === 0) return Promise.resolve(false);

    values.push(sessionId);

    const stmt = this.db.prepare(`
      UPDATE sessions 
      SET ${setParts.join(", ")}
      WHERE session_id = ?
    `);

    const result = stmt.run(...values);
    return Promise.resolve(Number(result.changes) > 0);
  }

  delete(sessionId: string): Promise<void> {
    const stmt = this.db.prepare("DELETE FROM sessions WHERE session_id = ?");
    stmt.run(sessionId);
    return Promise.resolve();
  }

  cleanup(expiresBeforeMs: number): Promise<number> {
    const stmt = this.db.prepare("DELETE FROM sessions WHERE expires_at < ?");
    const result = stmt.run(expiresBeforeMs);
    return Promise.resolve(Number(result.changes));
  }

  exists(sessionId: string): Promise<boolean> {
    const stmt = this.db.prepare(
      "SELECT 1 FROM sessions WHERE session_id = ? LIMIT 1"
    );
    return Promise.resolve(stmt.get(sessionId) !== undefined);
  }

  private rowToSessionData(row: SessionTable): SessionData {
    return {
      sessionId: row.session_id,
      userId: row.user_id,
      handle: row.handle || undefined,
      isAuthenticated: Boolean(row.is_authenticated),
      data: row.data ? JSON.parse(row.data) : undefined,
      createdAt: row.created_at,
      expiresAt: row.expires_at,
      lastAccessedAt: row.last_accessed_at,
    };
  }

  // SQLite-specific methods
  close(): void {
    this.db.close();
  }

  vacuum(): void {
    this.db.exec("VACUUM");
  }

  getSessionsByUser(userId: string): SessionData[] {
    const stmt = this.db.prepare("SELECT * FROM sessions WHERE user_id = ?");
    const rows = stmt.all(userId) as unknown as SessionTable[];
    return rows.map(row => this.rowToSessionData(row));
  }
}