Skip to content

Self-hosted, Self-Improving, Federated Everything App — dev management, agent orchestration, persistent mind, digital twin, second brain, and creative pipeline from a single dashboard over Tailscale to port the user into the OS

33.5 KB
Raw Blame
import { messageIngestionIdentity } from '../lib/messageBrowserIdentity.js';
/**
 * Human Activity timeline (#2150) — unified, machine-local event store.
 *
 * All ingestion sources (message/calendar syncs today; iMessage, Spotify,
 * YouTube, Signal in later phases) write normalized events into the
 * `human_activity_events` Postgres table via `recordEvents()`. The store is:
 *
 * - **Idempotent.** Every event carries a stable `dedupeKey`; the unique
 *   `(source, dedupe_key)` index + `ON CONFLICT DO NOTHING` make re-syncs no-ops.
 *   Same contract as `tribe_touchpoints.dedupe_key`.
 * - **Privacy-preserving.** Events store metadata + a SHORT summary line only —
 *   full message bodies stay in the per-source caches (`data/messages/cache/`,
 *   chat.db, …). `metadata` carries pointers (threadId / externalId / account) back
 *   to the source so a consumer can rehydrate the full body on demand.
 * - **Machine-local.** Coupled to per-machine accounts and OS databases; NOT
 *   federated (ADR docs/decisions/2026-06-26-tribe-and-universe-runs-local.md,
 *   guarded in sharing/peerSync.test.js). Derived summaries federate via existing
 *   rails (Brain journals, digital-twin), not the raw events.
 *
 * Ingestion is LLM-free and deterministic; identity matching reuses the tribe
 * email/handle index. No AI-provider calls happen here.
 */
import { v4 as uuidv4 } from '../lib/uuid.js';
import { ensureSchema, query, withTransaction } from '../lib/db.js';
import { normalizeIdentifier } from '../lib/tribeMatch.js';
import { getLocalParts, localDayRangeUtc, todayInTimezone, anchorLocalMidnightUtc } from '../lib/timezone.js';
import { getUserTimezone } from './userTimezone.js';
import { toPgJson } from '../lib/pgJson.js';

export { localDayRangeUtc };

// ---------------------------------------------------------------------------
// Pure helpers (exported for unit tests — no DB, no side effects).
// ---------------------------------------------------------------------------

// Collapse whitespace and clamp to a short single line. Guards the privacy
// contract: we keep a preview, never the full body.
export function shortSummary(text, max = 160) {
  if (!text) return '';
  const line = String(text).replace(/\s+/g, ' ').trim();
  if (line.length <= max) return line;
  return `${line.slice(0, max - 1).trimEnd()}…`;
}

// A stable local-day key (YYYY-MM-DD) for a timestamp in the user's timezone.
// Using the UTC day would split one local evening across two days whenever an
// event straddles UTC midnight (common in the Americas).
export function localDayKey(when, timezone) {
  const d = new Date(when);
  if (Number.isNaN(d.getTime())) return null;
  const p = getLocalParts(d, timezone);
  return `${p.year}-${String(p.month).padStart(2, '0')}-${String(p.day).padStart(2, '0')}`;
}

// Normalize a participant to { name, email, phone, personId } — trimmed, empty
// fields dropped. Accepts a bare email string or a { name, email, phone } object.
export function normalizeParticipant(p) {
  if (!p) return null;
  const raw = typeof p === 'string' ? { email: p } : p;
  const out = {};
  if (raw.name) out.name = String(raw.name).trim();
  if (raw.email) out.email = String(raw.email).trim().toLowerCase();
  if (raw.phone) out.phone = String(raw.phone).trim();
  if (raw.personId) out.personId = String(raw.personId);
  return out.name || out.email || out.phone || out.personId ? out : null;
}

export function normalizeParticipants(list) {
  if (!Array.isArray(list)) return [];
  return list.map(normalizeParticipant).filter(Boolean);
}

// Normalize a raw ingestion candidate into the DB row shape, or null if it's
// missing a required field (source / kind / happened_at / dedupeKey). A candidate
// may supply its own id (stable across re-derivations) or get a fresh uuid.
export function normalizeCandidate(candidate) {
  if (!candidate) return null;
  const source = String(candidate.source || '').trim();
  const kind = String(candidate.kind || '').trim();
  const dedupeKey = String(candidate.dedupeKey ?? candidate.dedupe_key ?? '').trim();
  const when = candidate.happenedAt ?? candidate.happened_at;
  const happenedAt = when ? new Date(when) : null;
  if (!source || !kind || !dedupeKey || !happenedAt || Number.isNaN(happenedAt.getTime())) {
    return null;
  }
  const durationRaw = candidate.durationS ?? candidate.duration_s;
  const durationS = Number.isFinite(Number(durationRaw)) ? Math.round(Number(durationRaw)) : null;
  return {
    id: candidate.id || uuidv4(),
    source,
    accountId: candidate.accountId ?? candidate.account_id ?? null,
    kind,
    happenedAt: happenedAt.toISOString(),
    durationS,
    title: candidate.title ? shortSummary(candidate.title, 300) : null,
    summary: candidate.summary ? shortSummary(candidate.summary) : null,
    url: candidate.url || null,
    participants: normalizeParticipants(candidate.participants),
    metadata: candidate.metadata && typeof candidate.metadata === 'object' ? candidate.metadata : {},
    dedupeKey,
  };
}

// Bucket events into a 24-slot local-hour histogram: [{ hour, count }].
export function hourlyHistogram(events, timezone) {
  const counts = Array.from({ length: 24 }, (_, hour) => ({ hour, count: 0 }));
  for (const ev of events || []) {
    const d = new Date(ev.happenedAt ?? ev.happened_at);
    if (Number.isNaN(d.getTime())) continue;
    const { hour } = getLocalParts(d, timezone);
    if (hour >= 0 && hour < 24) counts[hour].count += 1;
  }
  return counts;
}

// Tally events by source and by kind: { bySource: {…}, byKind: {…}, total }.
export function summarizeCounts(events) {
  const bySource = {};
  const byKind = {};
  for (const ev of events || []) {
    const source = ev.source || 'unknown';
    const kind = ev.kind || 'unknown';
    bySource[source] = (bySource[source] || 0) + 1;
    byKind[kind] = (byKind[kind] || 0) + 1;
  }
  return { bySource, byKind, total: (events || []).length };
}

// ---------------------------------------------------------------------------
// Ingestion mappers (pure — turn source-specific records into candidates).
// Kept here so the sync-hook glue in messageSync.js / calendarSync.js stays a
// thin `.catch()`-guarded call, and so mapping is unit-testable without a DB.
// ---------------------------------------------------------------------------

function participantEmail(p) {
  if (!p) return '';
  return normalizeIdentifier(typeof p === 'string' ? p : p.email);
}

// Lowercase + trim + dedupe a list of email addresses, dropping empties. Shares
// `normalizeIdentifier` with the Tribe matcher so owner-address comparison here
// can't drift from the identity matching that consumes these events.
export function normalizeEmailList(list) {
  return [...new Set((Array.isArray(list) ? list : []).map(normalizeIdentifier).filter(Boolean))];
}

// Map an account's cached messages to activity candidates. Direction (sent vs
// received) is derived deterministically from the sender vs the account owner.
export function messageActivityCandidates(account, messages = []) {
  const selfEmail = String(account?.email || '').trim().toLowerCase();
  // Owner-address set: the primary account email PLUS every Gmail "send-as" alias
  // (#2831). A received 1:1 message delivered to an alias (not account.email) would
  // otherwise keep the alias as a second participant, making the thread look like a
  // group and suppressing its Tribe-outreach nudge. Aliases are refreshed on sync
  // (messageGmailSync → messageAccounts.sendAsAliases); absent/failed fetch leaves the
  // set as just the primary email, i.e. today's behavior.
  const ownerEmails = new Set(
    normalizeEmailList([selfEmail, ...(Array.isArray(account?.sendAsAliases) ? account.sendAsAliases : [])])
  );
  const source = account?.type || 'message';
  const accountId = account?.id || null;
  const out = [];
  for (const message of messages || []) {
    const when = message?.date || message?.happenedAt;
    if (!when) continue;
    const externalId = message.activityIdentity || messageIngestionIdentity(message);
    if (!externalId) continue;
    const fromEmail = participantEmail(message.from);
    // Direction: prefer Gmail's authoritative SENT label when the provider supplies
    // labels — it correctly marks mail sent from a "send-as" alias (where From ≠
    // account.email), which a bare From-vs-owner match would misclassify as received
    // and so never cancel its inbound in outreach detection (#2796). Sources without
    // labels (Outlook/other) fall back to matching the sender against the owner.
    const labels = Array.isArray(message.labels) ? message.labels : null;
    const sent = labels
      ? labels.includes('SENT')
      : (Boolean(selfEmail) && fromEmail === selfEmail);
    const participants = normalizeParticipants([
      message.from,
      ...(message.to || []),
      ...(message.cc || []),
    ]).filter((p) => !(p.email && ownerEmails.has(p.email)));
    out.push({
      source,
      accountId,
      kind: sent ? 'message.sent' : 'message.received',
      happenedAt: when,
      title: message.subject || '(no subject)',
      summary: shortSummary(message.bodyText || message.snippet || ''),
      participants,
      dedupeKey: `msg:${accountId || 'x'}:${externalId}`,
      metadata: {
        threadId: message.threadId || null,
        externalId,
        channel: source,
        // The counterpart handle for a RECEIVED message is the sender's email —
        // set it so `enrichActivityEvent` (which resolves the top-level personId
        // from `metadata.handle`) can tag the Tribe sender, exactly as the iMessage
        // mapper does. Without this, email inbound never resolves to a person and
        // Tribe-outreach detection (#2796) drops every Gmail thread. Sent turns
        // carry no counterpart handle (mirrors iMessage); email threads group by
        // `threadId`, so handle is never the grouping key here.
        handle: sent ? null : (fromEmail || null),
      },
    });
  }
  return out;
}

// Owner addresses present in `next` but not in `previous` — the aliases an
// account just LEARNED (#2855). Only these can appear as a stale participant on
// already-stored rows, so the backfill repairs exactly this set; a removed alias
// needs no repair, and an unchanged set means there is nothing to do (which keeps
// every routine sync from re-UPDATEing the whole table). Lowercased + deduped so
// a casing-only refresh doesn't read as a change.
export function newlyLearnedAliases(previous, next) {
  const before = new Set(normalizeEmailList(previous));
  return normalizeEmailList(next).filter((e) => !before.has(e));
}

// Resolve a calendar time value to a UTC instant, interpreting OFFSET-LESS
// values in the given timezone instead of the Node process's OS timezone.
// Google all-day events are normalized to "YYYY-MM-DDT00:00:00" (no offset) —
// `new Date()` would parse that as server-OS-local, so for a user west of UTC
// an all-day July 4 event could land inside July 3's local-day window. Values
// with an explicit offset (Z / ±hh:mm) pass straight through.
export function resolveEventInstant(value, timezone) {
  if (!value) return null;
  if (value instanceof Date) return Number.isNaN(value.getTime()) ? null : value;
  const s = String(value).trim();
  const m = /^(\d{4})-(\d{2})-(\d{2})(?:T(\d{2}):(\d{2})(?::(\d{2}))?(?:\.\d+)?)?$/.exec(s);
  if (m && timezone) {
    const [, y, mo, d, hh, mm, ss] = m;
    const midnightMs = anchorLocalMidnightUtc(`${y}-${mo}-${d}`, timezone);
    const dayMs = ((Number(hh) || 0) * 3600 + (Number(mm) || 0) * 60 + (Number(ss) || 0)) * 1000;
    return new Date(midnightMs + dayMs);
  }
  const parsed = new Date(s);
  return Number.isNaN(parsed.getTime()) ? null : parsed;
}

// Map calendar events to activity candidates. Only events that have already
// happened (end/start <= now) and weren't declined/cancelled count as activity,
// mirroring the tribe touchpoint gate. `timezone` (the user's configured IANA
// zone) anchors offset-less/all-day values; without it they fall back to
// `new Date()` semantics.
export function calendarActivityCandidates(account, events = [], now = Date.now(), timezone = null) {
  const accountId = account?.id || null;
  const out = [];
  for (const event of events || []) {
    if (event?.isCancelled || event?.myStatus === 'declined') continue;
    const startedAt = resolveEventInstant(event.startTime, timezone) || resolveEventInstant(event.endTime, timezone);
    if (!startedAt) continue;
    const completedAt = resolveEventInstant(event.endTime, timezone) || startedAt;
    if (completedAt.getTime() > now) continue; // not finished yet
    const eventKey = event.externalId || event.id;
    if (!eventKey) continue;
    const start = resolveEventInstant(event.startTime, timezone)?.getTime();
    const end = resolveEventInstant(event.endTime, timezone)?.getTime();
    const durationS = Number.isFinite(start) && Number.isFinite(end) && end > start
      ? Math.round((end - start) / 1000)
      : null;
    out.push({
      source: 'calendar',
      accountId,
      kind: 'calendar.event',
      happenedAt: startedAt.toISOString(),
      durationS,
      title: event.title || '(untitled event)',
      summary: shortSummary(event.location || event.description || ''),
      participants: normalizeParticipants([event.organizer, ...(event.attendees || [])].filter(Boolean)),
      dedupeKey: `cal:${accountId || 'x'}:${eventKey}`,
      metadata: {
        externalId: eventKey,
        location: event.location || null,
        startTime: event.startTime || null,
        endTime: event.endTime || null,
        subcalendarId: event.subcalendarId || null,
      },
    });
  }
  return out;
}

// ---------------------------------------------------------------------------
// DB access.
// ---------------------------------------------------------------------------

let schemaReady = false;
async function ensureReady() {
  if (schemaReady) return;
  await ensureSchema();
  schemaReady = true;
}

function rowToEvent(row) {
  return {
    id: row.id,
    source: row.source,
    accountId: row.account_id,
    kind: row.kind,
    happenedAt: row.happened_at instanceof Date ? row.happened_at.toISOString() : row.happened_at,
    durationS: row.duration_s,
    title: row.title,
    summary: row.summary,
    url: row.url,
    participants: row.participants || [],
    metadata: row.metadata || {},
    dedupeKey: row.dedupe_key,
    createdAt: row.created_at instanceof Date ? row.created_at.toISOString() : row.created_at,
  };
}

// Idempotently persist a batch of activity candidates. Returns
// { recorded, skipped } where `recorded` is the count of newly-inserted rows and
// `skipped` counts duplicates (already present for this source+dedupeKey) plus
// any candidate that failed normalization.
export async function recordEvents(candidates = []) {
  const rows = (candidates || []).map(normalizeCandidate).filter(Boolean);
  if (rows.length === 0) return { recorded: 0, skipped: (candidates || []).length };
  await ensureReady();

  // Chunk the multi-row insert so a large batch (the message hook passes the full
  // capped cache each sync) can't exceed Postgres's 65535-param-per-statement
  // limit — 12 params/row, so a 500-row chunk stays well under it.
  const CHUNK = 500;
  let recorded = 0;
  for (let start = 0; start < rows.length; start += CHUNK) {
    recorded += await insertActivityChunk(rows.slice(start, start + CHUNK));
  }
  const skipped = (candidates || []).length - recorded;
  if (recorded > 0) {
    console.log(`🗓️  Recorded ${recorded} activity event(s) (${skipped} skipped as duplicate/invalid)`);
  }
  return { recorded, skipped };
}

// Insert one chunk of normalized rows; returns the count actually inserted.
// 12 bound params per row (created_at is a NOW() literal, not a param);
// participants and metadata are toPgJson-serialized + ::jsonb-cast (node-pg would
// otherwise coerce a JS array to a Postgres array literal and fail on jsonb).
async function insertActivityChunk(rows) {
  const paramsPerRow = 12;
  const values = [];
  const placeholders = rows.map((r, i) => {
    const b = i * paramsPerRow;
    values.push(
      r.id, r.source, r.accountId, r.kind, r.happenedAt, r.durationS,
      r.title, r.summary, r.url, toPgJson(r.participants),
      toPgJson(r.metadata), r.dedupeKey,
    );
    return `($${b + 1}, $${b + 2}, $${b + 3}, $${b + 4}, $${b + 5}, $${b + 6}, $${b + 7}, $${b + 8}, $${b + 9}, $${b + 10}::jsonb, $${b + 11}::jsonb, $${b + 12}, NOW())`;
  });
  const result = await query(
    `INSERT INTO human_activity_events
       (id, source, account_id, kind, happened_at, duration_s, title, summary, url, participants, metadata, dedupe_key, created_at)
     VALUES ${placeholders.join(', ')}
     ON CONFLICT (source, dedupe_key) DO NOTHING
     RETURNING id`,
    values,
  );
  return result.rowCount || 0;
}

// Repair already-stored events whose participants still list an owner address
// (#2855, follow-up to #2831). `messageActivityCandidates` strips every owner
// address at INSERT time, but rows written BEFORE an account's send-as aliases
// were learned kept the alias as a second participant — and since re-syncs are
// no-ops (`ON CONFLICT (source, dedupe_key) DO NOTHING`, a deliberate contract
// shared with `tribe_touchpoints`), they never self-correct. A 1:1 message
// delivered to an alias therefore keeps looking like a group conversation to
// `findUnansweredTribeThreads` until it ages out of the 14-day window.
//
// This is a SCOPED backfill, not a change to the insert contract: it rewrites
// only rows for one account+source that actually contain one of the named
// alias emails, and only removes those participant entries. `metadata.handle`
// (the counterpart sender) is left untouched. Returns the number of rows
// repaired. Emails are compared lowercased (participants are stored that way).
//
// `client` (optional) runs the rewrite on a caller-supplied pg transaction client
// instead of the pool — the boot db-migration that repairs already-known alias
// sets passes its transaction client so the repair shares the migration's
// all-or-nothing semantics, without re-stating the statement.
//
// The EXISTS gate keeps the rewrite off rows that don't need it (so a re-run is a
// genuine no-op and reports 0 rows), and jsonb_agg over the filtered elements
// rebuilds the array. COALESCE covers the all-owner row: jsonb_agg returns NULL
// over an empty set, which would null the column.
//
// The predicate is deliberately stated twice (positive in EXISTS, negated in the
// rebuild) rather than shared via one LATERAL unnest: Postgres rejects an
// `UPDATE t … FROM LATERAL (… t.col …)` with "invalid reference to FROM-clause
// entry", so the target row's participants can't be expanded once and reused.
const STRIP_OWNER_PARTICIPANTS_SQL = `
  UPDATE human_activity_events
     SET participants = COALESCE((
           SELECT jsonb_agg(p)
             FROM jsonb_array_elements(participants) AS p
            WHERE lower(COALESCE(p->>'email', '')) <> ALL($3::text[])
         ), '[]'::jsonb)
   WHERE account_id = $1
     AND source = $2
     AND EXISTS (
           SELECT 1
             FROM jsonb_array_elements(participants) AS p
            WHERE lower(COALESCE(p->>'email', '')) = ANY($3::text[])
         )`;

export async function stripParticipantsForAccount(accountId, source, aliasEmails = [], { client } = {}) {
  const emails = normalizeEmailList(aliasEmails);
  if (!accountId || !source || emails.length === 0) return 0;
  const params = [String(accountId), String(source), emails];
  let result;
  if (client) {
    // The migration runner ensures the base schema before applying migrations,
    // so ensureReady() (which would go through the pool) is neither needed nor
    // safe to run inside someone else's transaction.
    result = await client.query(STRIP_OWNER_PARTICIPANTS_SQL, params);
  } else {
    await ensureReady();
    result = await query(STRIP_OWNER_PARTICIPANTS_SQL, params);
  }
  const repaired = result.rowCount || 0;
  if (repaired > 0) {
    console.log(`🗓️  Repaired ${repaired} activity event(s) for account ${accountId} (${emails.length} owner alias(es) stripped)`);
  }
  return repaired;
}

// Query events with optional filters. `from`/`to` are ISO timestamps (inclusive
// lower, exclusive upper); `personId` matches a participant via the JSONB
// containment operator. `chatGuid` / `handle` match iMessage-style metadata
// pointers. `accountId` scopes to one source account (#2820). Newest first,
// capped by `limit` (default 500, max 2000).
export async function listEvents({ from, to, source, accountId, kind, personId, chatGuid, conversationId, threadId, handle, limit } = {}) {
  await ensureReady();
  const clauses = [];
  const params = [];
  if (from) { params.push(new Date(from).toISOString()); clauses.push(`happened_at >= $${params.length}`); }
  if (to) { params.push(new Date(to).toISOString()); clauses.push(`happened_at < $${params.length}`); }
  if (source) { params.push(source); clauses.push(`source = $${params.length}`); }
  // Optional per-account scope (#2820): lets a caller cap events one account at a
  // time so a high-volume account can't fill a shared cap and starve another.
  if (accountId != null && String(accountId).length > 0) {
    params.push(String(accountId));
    clauses.push(`account_id = $${params.length}`);
  }
  if (kind) { params.push(kind); clauses.push(`kind = $${params.length}`); }
  if (personId) {
    params.push(toPgJson([{ personId: String(personId) }]));
    clauses.push(`participants @> $${params.length}::jsonb`);
  }
  if (chatGuid != null && String(chatGuid).length > 0) {
    params.push(String(chatGuid));
    clauses.push(`metadata->>'chatGuid' = $${params.length}`);
  }
  if (conversationId != null && String(conversationId).length > 0) {
    params.push(String(conversationId));
    clauses.push(`metadata->>'conversationId' = $${params.length}`);
  }
  if (threadId != null && String(threadId).length > 0) {
    params.push(String(threadId));
    clauses.push(`metadata->>'threadId' = $${params.length}`);
  }
  if (handle != null && String(handle).length > 0) {
    params.push(String(handle));
    clauses.push(`metadata->>'handle' = $${params.length}`);
  }
  const cap = Math.min(Math.max(Number(limit) || 500, 1), 2000);
  params.push(cap);
  const where = clauses.length ? `WHERE ${clauses.join(' AND ')}` : '';
  const result = await query(
    `SELECT * FROM human_activity_events
     ${where}
     ORDER BY happened_at DESC
     LIMIT $${params.length}`,
    params,
  );
  return result.rows.map(rowToEvent);
}

/**
 * Delete activity events by explicit id list and/or filters. Returns `{ deleted }`.
 *
 * Safety: refuses unbounded wipes. Accepts either:
 *   - `ids` (optionally scoped with `source`), or
 *   - `source` + (`chatGuid` and/or `handle`), or
 *   - `accountId` (alone or with `source`) — erases everything an account's sync
 *     copied in when the account is deleted (#12984)
 *
 * Used by PortOS-side managers (iMessage #2413, later Signal/etc.) — never
 * mutates the external source (chat.db, Gmail, …).
 */
export async function deleteEvents({ ids, source, chatGuid, handle, accountId } = {}) {
  await ensureReady();
  const hasIds = Array.isArray(ids) && ids.length > 0;
  const hasChat = chatGuid != null && String(chatGuid).length > 0;
  const hasHandle = handle != null && String(handle).length > 0;
  const hasAccount = accountId != null && String(accountId).length > 0;
  if (!hasIds && !hasAccount && !(source && (hasChat || hasHandle))) return { deleted: 0 };

  const clauses = [];
  const params = [];
  if (hasIds) {
    params.push(ids.map(String));
    clauses.push(`id = ANY($${params.length}::text[])`);
  }
  if (source) {
    params.push(String(source));
    clauses.push(`source = $${params.length}`);
  }
  if (hasAccount) {
    params.push(String(accountId));
    clauses.push(`account_id = $${params.length}`);
  }
  if (hasChat) {
    params.push(String(chatGuid));
    clauses.push(`metadata->>'chatGuid' = $${params.length}`);
  }
  if (hasHandle) {
    params.push(String(handle));
    clauses.push(`metadata->>'handle' = $${params.length}`);
  }

  const result = await query(
    `DELETE FROM human_activity_events WHERE ${clauses.join(' AND ')}`,
    params,
  );
  const deleted = result.rowCount || 0;
  if (deleted > 0) {
    console.log(`🗓️  Deleted ${deleted} activity event(s)${source ? ` (source=${source})` : ''}`);
  }
  return { deleted };
}

/**
 * User-driven erasure of one source's events (#12987), optionally limited to a
 * `kind` and a `from` (inclusive) / `to` (exclusive) range. `source` is
 * required so a single call can never wipe the whole table. Importers are
 * idempotent, so re-importing the same export restores erased rows.
 */
export async function eraseEvents({ source, kind, from, to } = {}) {
  if (!source) return { deleted: 0 };
  await ensureReady();
  const params = [String(source)];
  const clauses = ['source = $1'];
  if (kind) { params.push(String(kind)); clauses.push(`kind = $${params.length}`); }
  if (from) { params.push(new Date(from).toISOString()); clauses.push(`happened_at >= $${params.length}`); }
  if (to) { params.push(new Date(to).toISOString()); clauses.push(`happened_at < $${params.length}`); }
  const result = await query(
    `DELETE FROM human_activity_events WHERE ${clauses.join(' AND ')}`,
    params,
  );
  const deleted = result.rowCount || 0;
  console.log(`🗓️  Erased ${deleted} activity event(s) (source=${source})`);
  return { deleted };
}

/** Per-source event counts and date range, for the "Manage imported data" panel. */
export async function listSourceCounts() {
  await ensureReady();
  const result = await query(
    `SELECT source, COUNT(*)::int AS event_count, MIN(happened_at) AS earliest_at, MAX(happened_at) AS latest_at
     FROM human_activity_events
     GROUP BY source
     ORDER BY source`,
  );
  const iso = (v) => (v instanceof Date ? v.toISOString() : (v || null));
  return result.rows.map((row) => ({
    source: row.source,
    eventCount: Number(row.event_count) || 0,
    earliestAt: iso(row.earliest_at),
    latestAt: iso(row.latest_at),
  }));
}

/**
 * Aggregate conversations for a single source (iMessage, Signal, …) grouped by
 * `metadata.chatGuid`. Newest activity first. Optional free-text `q` filters
 * title/handle/summary. Cap defaults to 500 conversations.
 */
export async function listConversations({ source, q, limit } = {}) {
  if (!source) return [];
  await ensureReady();
  const params = [String(source)];
  const clauses = [`source = $1`];
  if (q != null && String(q).trim()) {
    params.push(`%${String(q).trim().toLowerCase()}%`);
    clauses.push(`(
      LOWER(COALESCE(title, '')) LIKE $${params.length}
      OR LOWER(COALESCE(summary, '')) LIKE $${params.length}
      OR LOWER(COALESCE(metadata->>'handle', '')) LIKE $${params.length}
      OR LOWER(COALESCE(metadata->>'chatGuid', '')) LIKE $${params.length}
    )`);
  }
  const cap = Math.min(Math.max(Number(limit) || 500, 1), 2000);
  params.push(cap);
  const result = await query(
    `SELECT
       COALESCE(metadata->>'chatGuid', '') AS chat_guid,
       MAX(title) AS title,
       MAX(NULLIF(metadata->>'handle', '')) AS handle,
       COUNT(*)::int AS event_count,
       MIN(happened_at) AS first_at,
       MAX(happened_at) AS last_at,
       (array_agg(summary ORDER BY happened_at DESC)
          FILTER (WHERE summary IS NOT NULL AND summary <> ''))[1] AS last_summary
     FROM human_activity_events
     WHERE ${clauses.join(' AND ')}
     GROUP BY COALESCE(metadata->>'chatGuid', '')
     ORDER BY MAX(happened_at) DESC
     LIMIT $${params.length}`,
    params,
  );
  return result.rows.map((row) => ({
    chatGuid: row.chat_guid || '',
    title: row.title || row.handle || row.chat_guid || '(unknown)',
    handle: row.handle || null,
    eventCount: Number(row.event_count) || 0,
    firstAt: row.first_at instanceof Date ? row.first_at.toISOString() : row.first_at,
    lastAt: row.last_at instanceof Date ? row.last_at.toISOString() : row.last_at,
    lastSummary: row.last_summary || '',
  }));
}

/**
 * Rank `metadata.handle` values by recent event frequency for one source,
 * aggregated in SQL rather than loaded into Node. `eventLimit` bounds the
 * same recency window a caller would otherwise pass to `listEvents` before
 * counting client-side — only the handle column crosses the wire, and
 * Postgres does the GROUP BY instead of a per-row Map in Node.
 */
export async function countEventsByHandle({ source, eventLimit } = {}) {
  if (!source) return [];
  await ensureReady();
  const cap = Math.min(Math.max(Number(eventLimit) || 2000, 1), 5000);
  const result = await query(
    `SELECT handle, COUNT(*)::int AS event_count
     FROM (
       SELECT metadata->>'handle' AS handle
       FROM human_activity_events
       WHERE source = $1
       ORDER BY happened_at DESC
       LIMIT $2
     ) recent
     WHERE handle IS NOT NULL AND handle <> ''
     GROUP BY handle
     ORDER BY event_count DESC`,
    [String(source), cap],
  );
  return result.rows.map((row) => ({ handle: row.handle, eventCount: Number(row.event_count) || 0 }));
}

/**
 * Source-level stats for a manager UI: total events, conversation count, date
 * range. Cheap aggregates over the machine-local activity store.
 */
export async function sourceStats(source) {
  if (!source) {
    return { source: null, eventCount: 0, conversationCount: 0, earliestAt: null, latestAt: null };
  }
  await ensureReady();
  const result = await query(
    `SELECT
       COUNT(*)::int AS event_count,
       COUNT(DISTINCT COALESCE(metadata->>'chatGuid', ''))::int AS conversation_count,
       MIN(happened_at) AS earliest_at,
       MAX(happened_at) AS latest_at
     FROM human_activity_events
     WHERE source = $1`,
    [String(source)],
  );
  const row = result.rows[0] || {};
  return {
    source: String(source),
    eventCount: Number(row.event_count) || 0,
    conversationCount: Number(row.conversation_count) || 0,
    earliestAt: row.earliest_at instanceof Date ? row.earliest_at.toISOString() : (row.earliest_at || null),
    latestAt: row.latest_at instanceof Date ? row.latest_at.toISOString() : (row.latest_at || null),
  };
}

// Max event rows hydrated for the day view. The day's counts and histogram are
// aggregated in SQL over the WHOLE day and are independent of this page size.
export const DAY_EVENT_PAGE_SIZE = 2000;

// Day view: a bounded page of events on a local calendar day plus full-day
// aggregates (hourly histogram, source/kind tallies). `date` defaults to today
// in the user's timezone.
//
// `counts` / `histogram` cover every event in the day regardless of how many
// are hydrated into `events`. `events` holds the NEWEST `DAY_EVENT_PAGE_SIZE`
// rows, returned oldest-first; `eventPage` says how complete that page is
// ({ total, returned, limit, hasMore }). The aggregates and the page are read
// in one REPEATABLE READ snapshot so concurrent ingestion cannot make them
// disagree, and share the same exclusive [start, end) UTC bounds. `eventLimit`
// can only shrink the page (tests use it to avoid thousand-row fixtures).
export async function getDaySummary({ date, eventLimit = DAY_EVENT_PAGE_SIZE } = {}) {
  const pageSize = Math.min(Math.max(Number(eventLimit) || DAY_EVENT_PAGE_SIZE, 1), DAY_EVENT_PAGE_SIZE);
  const timezone = await getUserTimezone();
  // `today` is computed in the USER's configured timezone (not the browser's) and
  // returned so the client can default the bare /timeline route and gate its
  // Today/next-day controls on the server's notion of the current day.
  const today = todayInTimezone(timezone);
  const day = /^\d{4}-\d{2}-\d{2}$/.test(String(date || '')) ? date : today;
  const range = localDayRangeUtc(day, timezone);
  const eventPage = (returned, total) => ({ total, returned, limit: pageSize, hasMore: returned < total });
  if (!range) {
    return { date: day, today, timezone, events: [], eventPage: eventPage(0, 0), histogram: hourlyHistogram([], timezone), counts: summarizeCounts([]) };
  }
  await ensureReady();
  const bounds = [range.start.toISOString(), range.end.toISOString()];
  const { aggregates, rows } = await withTransaction(async (client) => {
    // Must be the transaction's first statement: every query below then sees
    // one snapshot.
    await client.query('SET TRANSACTION ISOLATION LEVEL REPEATABLE READ');
    const agg = await client.query(
      `SELECT 'source' AS dim, source AS key, COUNT(*)::int AS n
         FROM human_activity_events WHERE happened_at >= $1 AND happened_at < $2 GROUP BY source
       UNION ALL
       SELECT 'kind', kind, COUNT(*)::int
         FROM human_activity_events WHERE happened_at >= $1 AND happened_at < $2 GROUP BY kind
       UNION ALL
       SELECT 'hour', EXTRACT(HOUR FROM happened_at AT TIME ZONE $3)::int::text, COUNT(*)::int
         FROM human_activity_events WHERE happened_at >= $1 AND happened_at < $2 GROUP BY 2`,
      [...bounds, timezone],
    );
    const page = await client.query(
      `SELECT * FROM human_activity_events
        WHERE happened_at >= $1 AND happened_at < $2
        ORDER BY happened_at DESC LIMIT $3`,
      [...bounds, pageSize],
    );
    return { aggregates: agg.rows, rows: page.rows };
  });

  const bySource = {};
  const byKind = {};
  const histogram = Array.from({ length: 24 }, (_, hour) => ({ hour, count: 0 }));
  for (const { dim, key, n } of aggregates) {
    if (dim === 'source') bySource[key || 'unknown'] = (bySource[key || 'unknown'] || 0) + n;
    else if (dim === 'kind') byKind[key || 'unknown'] = (byKind[key || 'unknown'] || 0) + n;
    else if (histogram[Number(key)]) histogram[Number(key)].count += n;
  }
  const total = Object.values(bySource).reduce((sum, n) => sum + n, 0);
  // Newest-first from SQL; a day view reads better oldest-first.
  const events = rows.map(rowToEvent).reverse();
  return { date: day, today, timezone, events, eventPage: eventPage(events.length, total), histogram, counts: { bySource, byKind, total } };
}