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

2.7 KB
Raw Blame
// Human activity timeline DDL (#2150) — unified machine-local event store fed
// by message/calendar syncs. Extracted verbatim from ensureSchemaImpl() in
// server/lib/db.js (#2832); idempotent, runs on every boot.
export const humanActivityDdl = [
    // Human activity timeline (#2150) — unified, machine-local event store fed by
    // message/calendar syncs (later: iMessage, Spotify, YouTube, Signal). Stores
    // metadata + a short summary only; full bodies stay in per-source caches, with
    // metadata pointers (threadId/externalId) back to the source. Idempotent via the
    // unique (source, dedupe_key) index + ON CONFLICT DO NOTHING. Machine-local like
    // Tribe (ADR 2026-06-26) — excluded from peer sync, guarded in peerSync.test.js.
    `CREATE TABLE IF NOT EXISTS human_activity_events (
      id TEXT PRIMARY KEY,
      source TEXT NOT NULL,
      account_id TEXT,
      kind TEXT NOT NULL,
      happened_at TIMESTAMPTZ NOT NULL,
      duration_s INTEGER,
      title TEXT,
      summary TEXT,
      url TEXT,
      participants JSONB DEFAULT '[]'::jsonb,
      metadata JSONB DEFAULT '{}'::jsonb,
      dedupe_key TEXT NOT NULL,
      created_at TIMESTAMPTZ DEFAULT NOW()
    )`,
    `CREATE UNIQUE INDEX IF NOT EXISTS idx_human_activity_dedupe ON human_activity_events (source, dedupe_key)`,
    `CREATE INDEX IF NOT EXISTS idx_human_activity_happened ON human_activity_events (happened_at)`,
    // Source-scoped timeline reads (#5715). The dominant shape is
    // `WHERE source = $1 [AND kind = ...] ORDER BY happened_at DESC LIMIT n`
    // (listEvents in services/humanActivity.js), which neither index above
    // serves: the dedupe index leads on `source` but carries no ordering, and
    // the `happened_at` index orders but must filter every row on `source` — so
    // a low-volume source behind a high-volume one walks past a large number of
    // non-matching rows before it can fill LIMIT. Two indexes, not one: the
    // kind-filtered read is a distinct shape, and `(source, kind, happened_at)`
    // does not efficiently serve the source-only query for a source with many
    // kinds. Mirrors the composites `user_action_events` already ships.
    //
    // Deliberately NOT `CONCURRENTLY`: ensureSchema() runs this DDL as part of
    // the boot sequence, and CREATE INDEX CONCURRENTLY cannot run inside a
    // transaction block. The table is machine-local and the boot-time build is
    // bounded, so the blocking build is the correct trade here.
    `CREATE INDEX IF NOT EXISTS idx_human_activity_source_happened ON human_activity_events (source, happened_at DESC)`,
    `CREATE INDEX IF NOT EXISTS idx_human_activity_source_kind_happened ON human_activity_events (source, kind, happened_at DESC)`,
];