cluster-observations.mjs

// cluster-observations.mjs
// READ-ONLY exploration of an append-only observation store.
//
// Purpose: before designing a consolidation schema, look at the real shape of
// the data. This script does NOT write anything. It opens the source SQLite DB
// read-only and answers one question:
//
//   "If we built one evolving digest per (project, type) cell, what would the
//    clusters look like — how many digests, how much collapses into each?"
//
// Run with bun (built-in sqlite driver, no install needed):
//   bun cluster-observations.mjs
//
// Nothing here mutates the DB. Safe to run anytime, even with the store live.
//
// CONFIG (env)
//   CLAUDE_MEM_DB   path to the source observation store
//                   (default: ~/.claude-mem/claude-mem.db)

import { Database } from "bun:sqlite";
import { homedir } from "os";
import { join } from "path";

const DB_PATH =
  process.env.CLAUDE_MEM_DB || join(homedir(), ".claude-mem", "claude-mem.db");

// readonly:true is the guard rail — SQLite opens the file in read-only mode, so
// even a bug in this script physically cannot write to your memory store.
const db = new Database(DB_PATH, { readonly: true });

// ---------------------------------------------------------------------------
// 1. Pull every observation's clustering-relevant fields.
// ---------------------------------------------------------------------------
const rows = db
  .query(
    `SELECT id, project, title, concepts, created_at_epoch, relevance_count
       FROM observations`
  )
  .all();

console.log(`Loaded ${rows.length} observations from ${DB_PATH}\n`);

// ---------------------------------------------------------------------------
// 2. Explode the concepts.
//    `concepts` is stored as a JSON array string, e.g. ["gotcha","pattern"].
//    Parse defensively; normalize to lowercase + trim so tags merge cleanly.
// ---------------------------------------------------------------------------
function parseConcepts(raw) {
  if (!raw) return [];
  try {
    const arr = JSON.parse(raw);
    if (!Array.isArray(arr)) return [];
    return arr
      .filter((c) => typeof c === "string")
      .map((c) => c.trim().toLowerCase())
      .filter(Boolean);
  } catch {
    return []; // malformed JSON — skip rather than crash
  }
}

// concept -> { count, projects:Set, firstEpoch, lastEpoch, sampleTitles:[] }
const clusters = new Map();
let rowsWithNoConcepts = 0;

for (const row of rows) {
  const concepts = parseConcepts(row.concepts);
  if (concepts.length === 0) {
    rowsWithNoConcepts++;
    continue;
  }
  for (const concept of concepts) {
    let c = clusters.get(concept);
    if (!c) {
      c = {
        count: 0,
        projects: new Set(),
        firstEpoch: row.created_at_epoch,
        lastEpoch: row.created_at_epoch,
        sampleTitles: [],
      };
      clusters.set(concept, c);
    }
    c.count++;
    if (row.project) c.projects.add(row.project);
    if (row.created_at_epoch < c.firstEpoch) c.firstEpoch = row.created_at_epoch;
    if (row.created_at_epoch > c.lastEpoch) c.lastEpoch = row.created_at_epoch;
    if (c.sampleTitles.length < 3 && row.title) c.sampleTitles.push(row.title);
  }
}

// ---------------------------------------------------------------------------
// 3. Report. Sort concepts by how many observations they'd consolidate —
//    the biggest clusters are the highest-value digest candidates.
// ---------------------------------------------------------------------------
const sorted = [...clusters.entries()].sort((a, b) => b[1].count - a[1].count);

const fmtDate = (epoch) =>
  epoch ? new Date(epoch).toISOString().slice(0, 10) : "?";

console.log("=== CLUSTER SUMMARY ===");
console.log(`Distinct concepts (candidate digests): ${clusters.size}`);
console.log(`Observations with NO usable concepts:  ${rowsWithNoConcepts}`);
console.log("");

console.log("=== TOP 25 CONCEPTS BY OBSERVATION COUNT ===");
console.log("rank  count  projects  span(first→last)            concept".padEnd(70));
sorted.slice(0, 25).forEach(([concept, c], i) => {
  const rank = String(i + 1).padStart(2);
  const count = String(c.count).padStart(5);
  const proj = String(c.projects.size).padStart(3);
  const span = `${fmtDate(c.firstEpoch)}→${fmtDate(c.lastEpoch)}`;
  console.log(`${rank}.   ${count}    ${proj}     ${span}   ${concept}`);
});

// ---------------------------------------------------------------------------
// 4. The economic headline: how much does consolidation actually collapse?
// ---------------------------------------------------------------------------
const totalConceptInstances = sorted.reduce((s, [, c]) => s + c.count, 0);
const singletons = sorted.filter(([, c]) => c.count === 1).length;
const multiObs = sorted.filter(([, c]) => c.count >= 2);
const obsInMultiClusters = multiObs.reduce((s, [, c]) => s + c.count, 0);

console.log("\n=== CONSOLIDATION POTENTIAL ===");
console.log(`Total concept→observation links:         ${totalConceptInstances}`);
console.log(`Singleton concepts (1 obs, low value):   ${singletons}`);
console.log(`Multi-observation concepts (worth a digest): ${multiObs.length}`);
console.log(`Observations those digests would cover:      ${obsInMultiClusters}`);

// ---------------------------------------------------------------------------
// 5. Show what ONE digest's source set looks like — pick the biggest cluster.
// ---------------------------------------------------------------------------
if (sorted.length > 0) {
  const [topConcept, topCluster] = sorted[0];
  console.log(`\n=== SAMPLE: digest seed for concept "${topConcept}" ===`);
  console.log(
    `Would consolidate ${topCluster.count} observations across ` +
      `${topCluster.projects.size} project(s):`
  );
  console.log(`Projects: ${[...topCluster.projects].join(", ")}`);
  console.log("Sample source titles:");
  for (const t of topCluster.sampleTitles) console.log(`  - ${t}`);
}

db.close();