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
JavaScript ai-agentsdashboarddeveloper-toolsdigital-twinquantified-selfsecond-brainself-hostedtailscale
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 } };
}