playbook

/**
 * DocIndex — SQLite-based index for playbook documents.
 * Source of truth are the MD files; this index stores only metadata + embeddings.
 */

import initSqlJs, { type Database, type SqlJsStatic } from "sql.js";
import fs from "node:fs";
import path from "node:path";
import crypto from "node:crypto";

export interface DocRecord {
  id: string;         // UUID
  filePath: string;   // absolute path to MD file
  title: string;
  tags: string[];
  hash: string;       // SHA-256 of file content (for change detection)
  embedding: Float32Array | null;
  updatedAt: string;  // ISO timestamp
}

export interface DocMeta {
  id: string;
  filePath: string;
  title: string;
  tags: string[];
  hash: string;
  updatedAt: string;
}

let SQL: SqlJsStatic | null = null;

async function getSql(): Promise<SqlJsStatic> {
  if (!SQL) {
    SQL = await initSqlJs();
  }
  return SQL;
}

export function hashContent(content: string): string {
  return crypto.createHash("sha256").update(content, "utf8").digest("hex");
}

export class DocIndex {
  private db: Database | null = null;
  private dbPath: string;

  constructor(dataDir: string) {
    this.dbPath = path.join(dataDir, "playbook-index.db");
  }

  async load(): Promise<void> {
    const sql = await getSql();
    if (fs.existsSync(this.dbPath)) {
      const data = fs.readFileSync(this.dbPath);
      this.db = new sql.Database(data);
    } else {
      this.db = new sql.Database();
    }
    this.ensureSchema();
  }

  private ensureSchema(): void {
    this.db!.run(`
      CREATE TABLE IF NOT EXISTS docs (
        id         TEXT PRIMARY KEY,
        file_path  TEXT UNIQUE NOT NULL,
        title      TEXT NOT NULL,
        tags       TEXT NOT NULL DEFAULT '[]',
        hash       TEXT NOT NULL,
        embedding  BLOB,
        updated_at TEXT NOT NULL
      )
    `);
  }

  save(): void {
    if (!this.db) return;
    const data = this.db.export();
    const dir = path.dirname(this.dbPath);
    if (!fs.existsSync(dir)) fs.mkdirSync(dir, { recursive: true });
    fs.writeFileSync(this.dbPath, data);
  }

  upsert(doc: DocRecord): void {
    const embeddingBlob = doc.embedding
      ? Buffer.from(doc.embedding.buffer)
      : null;

    this.db!.run(
      `INSERT INTO docs (id, file_path, title, tags, hash, embedding, updated_at)
       VALUES (?, ?, ?, ?, ?, ?, ?)
       ON CONFLICT(file_path) DO UPDATE SET
         id         = excluded.id,
         title      = excluded.title,
         tags       = excluded.tags,
         hash       = excluded.hash,
         embedding  = excluded.embedding,
         updated_at = excluded.updated_at`,
      [
        doc.id,
        doc.filePath,
        doc.title,
        JSON.stringify(doc.tags),
        doc.hash,
        embeddingBlob,
        doc.updatedAt,
      ]
    );
  }

  deleteByPath(filePath: string): boolean {
    const stmt = this.db!.prepare("DELETE FROM docs WHERE file_path = ?");
    stmt.run([filePath]);
    const changed = this.db!.getRowsModified() > 0;
    stmt.free();
    return changed;
  }

  getAll(): DocMeta[] {
    const stmt = this.db!.prepare(
      "SELECT id, file_path, title, tags, hash, updated_at FROM docs ORDER BY updated_at DESC"
    );
    const rows: DocMeta[] = [];
    while (stmt.step()) {
      const r = stmt.getAsObject() as Record<string, unknown>;
      rows.push({
        id: r["id"] as string,
        filePath: r["file_path"] as string,
        title: r["title"] as string,
        tags: JSON.parse(r["tags"] as string) as string[],
        hash: r["hash"] as string,
        updatedAt: r["updated_at"] as string,
      });
    }
    stmt.free();
    return rows;
  }

  getByTitle(title: string): DocMeta | null {
    const stmt = this.db!.prepare(
      "SELECT id, file_path, title, tags, hash, updated_at FROM docs WHERE lower(title) = lower(?)"
    );
    stmt.bind([title]);
    if (stmt.step()) {
      const r = stmt.getAsObject() as Record<string, unknown>;
      stmt.free();
      return {
        id: r["id"] as string,
        filePath: r["file_path"] as string,
        title: r["title"] as string,
        tags: JSON.parse(r["tags"] as string) as string[],
        hash: r["hash"] as string,
        updatedAt: r["updated_at"] as string,
      };
    }
    stmt.free();
    return null;
  }

  getByPath(filePath: string): DocMeta | null {
    const stmt = this.db!.prepare(
      "SELECT id, file_path, title, tags, hash, updated_at FROM docs WHERE file_path = ?"
    );
    stmt.bind([filePath]);
    if (stmt.step()) {
      const r = stmt.getAsObject() as Record<string, unknown>;
      stmt.free();
      return {
        id: r["id"] as string,
        filePath: r["file_path"] as string,
        title: r["title"] as string,
        tags: JSON.parse(r["tags"] as string) as string[],
        hash: r["hash"] as string,
        updatedAt: r["updated_at"] as string,
      };
    }
    stmt.free();
    return null;
  }

  getAllWithEmbeddings(): Array<DocMeta & { embedding: Float32Array | null }> {
    const stmt = this.db!.prepare(
      "SELECT id, file_path, title, tags, hash, embedding, updated_at FROM docs ORDER BY updated_at DESC"
    );
    const rows: Array<DocMeta & { embedding: Float32Array | null }> = [];
    while (stmt.step()) {
      const r = stmt.getAsObject() as Record<string, unknown>;
      const embRaw = r["embedding"];
      const embedding = embRaw
        ? new Float32Array((embRaw as Buffer).buffer)
        : null;
      rows.push({
        id: r["id"] as string,
        filePath: r["file_path"] as string,
        title: r["title"] as string,
        tags: JSON.parse(r["tags"] as string) as string[],
        hash: r["hash"] as string,
        updatedAt: r["updated_at"] as string,
        embedding,
      });
    }
    stmt.free();
    return rows;
  }

  isLoaded(): boolean {
    return this.db !== null;
  }
}