[Bug] No lifecycle/suppression controls for accidental event-log noise

[crystalwizard]

Problem

Brainctl's event log (event_add / event_log) appears to be append-only with no
event lifecycle operation — no retire, suppress, supersede, or delete. Agents can
accidentally create repeated/noisy events during tool loops, and there's currently
no first-class way to clean these up. The noise pollutes orient()'s
recent_events window, crowding out useful current-session context.

Concrete case (GPT, 2026-06-10)

During a Concept Clarification cleanup of a chat about a pasted physics/math
excerpt ("Harmonic Collapse in Quantum Systems"), GPT's brainctl logged 10
duplicate warning events with the identical summary
"Correction: duplicate-warning test was unnecessary":

• Events 99–108, timestamps 2026-06-10T23:13:32Z through 2026-06-10T23:14:15Z
  (all within ~45 seconds)
• These were tool-loop noise from GPT trying to correct one earlier accidental
  duplicate-warning event, and accidentally re-logging the correction repeatedly
• Substantive events 74–76 (cleanup start, source-boundary warning, artifact
  preservation) should remain meaningful
• Event 77 was itself a non-substantive duplicate-warning test / boundary marker
• Possible additional duplicate correction events may exist between 78–98 (not
  visible in the orient recent-events window GPT received — would need direct DB
  inspection to confirm)

The actual project content is not affected — durable files and brainctl
memories 90, 91, 92 (Harmonic Collapse critique cleanup pointer, technical
findings, source-boundary warning) are intact and retrievable via
orient/memory_search. As a workaround, GPT added memory 93 documenting that
the duplicate events are noise/superseded — but this is clunky and doesn't fix
orient() pollution.

Requested feature (any of these would help)

• event_retire(event_id, reason) — soft-retire a single event
• event_suppress_from_orient(event_id_or_range/query, reason) — hide from
  default orient output
• event_mark_noise(event_id, reason)
• event_supersede(event_id, superseding_event_id_or_memory_id, reason)
• event_bulk_retire(query_or_id_range, reason, dry_run=true first)
• An orient option to hide retired/suppressed/noise events by default, with an
  audit flag to show them anyway

Design constraint

Prefer soft-retire / hide-from-default-orient over hard delete — preserves audit
integrity (append-only ledger stays intact) while keeping orient() useful
day-to-day. Suggested metadata fields: retired_at, retracted_at,
suppress_from_orient, noise/superseded flag, reason, superseded_by.

Desired end state for this specific case (once feature exists)

• Keep events 74, 75, 76 as substantive
• Treat event 77 as non-substantive (duplicate-warning test)
• Soft-retire/suppress events 99–108 (and any duplicates in 78–98) with summary
  "Correction: duplicate-warning test was unnecessary"
• Keep memories 90, 91, 92 intact
• Memory 93 (explanatory repair record) can stay or be retired once native cleanup
  exists

---

Reported on behalf of GPT/Kelly. Not blocking — project substance is safe; this
is a hygiene/UX issue for orient().

[crystalwizard]

Workaround script: brainctl_event_cleanup.py (tested against real data)

Hi Terrance - while this feature request is open, we (Claude, an Anthropic Claude Code agent working with @crystalwizard) wrote and tested a standalone Python script that does an external, backed-up cleanup of duplicate/noise events in brain.db. Sharing it here in case it's useful either as an interim workaround for others hitting this, or as a reference for what the native lifecycle/suppression feature should cover.

What it's for

Brainctl's events table is append-only with no retire/suppress/supersede/delete operation. When an agent gets stuck in a tool loop, it can log the same (agent_id, event_type, summary) dozens of times in a row (we saw 31 identical "warning" events logged 170 seconds apart from one tool loop). This pollutes orient()'s recent_events window and there's currently no first-class way to clean it up.

This script is an external (out-of-process) cleanup tool - it operates directly on the brain.db SQLite file while brainctl is not actively writing to it (or carefully, with WAL-aware backups, while it is). It is not a substitute for the native lifecycle feature requested above (soft-retire/suppress would be strictly better - preserves the audit trail). It's a stopgap for agents/users who need their orient() output usable now.

How it works

• Detection (auto mode): groups events by (agent_id, event_type, summary) via GROUP BY + HAVING COUNT(*) >= --min-group (default 2). Any group with >= N identical rows is flagged as a duplicate cluster. session_start, session_end, and handoff event types are excluded from auto-detection by default (these legitimately recur every session and aren't "noise"), overridable with --include-event-types.
• Detection (manual mode): --ids 95,96,99-101 lets you target specific event ids directly (for noise that varies its text run-to-run and won't group via exact-match).
• Keep policy: within each duplicate group, keeps either the first (oldest, default) or last (--keep last) event by id, and flags the rest for deletion.
• Dangling self-reference handling: events.events has self-referential FKs (caused_by_event_id, causal_chain_root) with no ON DELETE cascade. Before deleting, the script finds any surviving event whose caused_by_event_id/causal_chain_root points at an id about to be deleted, and nulls out just that field (independently - if only one of the two fields is dangling, only that one gets nulled), in the same transaction as the delete.
• Backup: before any --apply run, makes an online backup via SQLite's backup() API (sqlite3.Connection.backup()) to <db_path>.bak-<timestamp> - this is safe to run against a live WAL-mode database, unlike a raw file copy.
• FTS sync: relies on brainctl's existing cascade triggers to keep events_fts in sync with events on delete (verified empirically, see test results below - did not need to touch FTS tables directly).
• Dry run by default: without --apply, only prints a report. Nothing is written until --apply is passed.

How to run it

Requires Python 3, stdlib only (sqlite3, sys, os, argparse, datetime) - no install needed.

# 1. Dry run - find duplicate groups, see what WOULD be deleted (default min-group=2)
python brainctl_event_cleanup.py /path/to/brain.db

# 2. Only flag groups with N+ duplicates
python brainctl_event_cleanup.py /path/to/brain.db --min-group 3

# 3. Apply (makes its own backup first, then deletes, keeping the oldest of each group)
python brainctl_event_cleanup.py /path/to/brain.db --apply

# 4. Keep the newest instead of oldest
python brainctl_event_cleanup.py /path/to/brain.db --apply --keep last

# 5. Also scan event types excluded by default (session_start, session_end, handoff)
python brainctl_event_cleanup.py /path/to/brain.db --include-event-types session_start,session_end

# 6. Delete specific event ids directly (dry run first, then apply)
python brainctl_event_cleanup.py /path/to/brain.db --ids 95,96,99-101
python brainctl_event_cleanup.py /path/to/brain.db --ids 95-108 --apply

Always run without --apply first and read the report before applying.

Test results (2026-06-10)

Ran against a copy of a live brain.db (104 events at copy time, made via SQLite's online backup API so the original was never written to):

• Dry run found 10 duplicate groups, 39 events total flagged for deletion. The largest: 31 events (ids 78-108), all identical "warning" events logged within a 170-second tool loop - this is the exact case described in this issue, and the script's exact-match grouping found it spans 78-108 (wider than the 99-108 range originally suspected from the orient() window alone).
• --apply deleted exactly 39/39, cleared 0 dangling self-references (none existed in this case - the logic for nulling them was previously verified on a synthetic test case), and the automatic pre-delete backup was created correctly.
• Post-apply verification: events count went from 104 to 65 (exact match), PRAGMA integrity_check returned ok, events_fts row count matched events exactly with 0 orphaned FTS rows (cascade trigger sync confirmed correct), and a spot-check of surviving rows looked correct.

Known limitations

1. Auto-detect excludes session_start/session_end/handoff by default - real duplicates of those types need --include-event-types.
2. Detection is exact-string match on summary - noise whose text varies per occurrence (counters, timestamps) won't group automatically; use --ids for that.
3. The "only one of caused_by_event_id/causal_chain_root needs nulling, the other points at a surviving event" sub-case is correct by code inspection and independent field handling, but hasn't been exercised on real data yet (no real case has had this shape so far).

Full source

"""
brainctl event-log cleanup utility.

Workaround for the lack of event lifecycle/suppression controls in brainctl
(see https://github.com/TSchonleber/brainctl/issues/155 / Shop THE-56).

WHAT THIS DOES NOT GUARANTEE:
- It cannot tell "tool-loop spam" from "an event type that legitimately
  recurs every session" by itself. Auto-detect mode therefore EXCLUDES a
  default list of recurring event types (see RECURRING_EVENT_TYPES below).
  Override with --include-event-types if you really mean to touch those.
- Detection is exact-string-match on summary. A noise pattern that varies
  its text run-to-run (e.g. includes a counter or timestamp) will NOT be
  grouped and will not be found automatically -- use --ids for that.
- "DELETE is safe" only means the FTS index and knowledge_edges stay in
  sync (cascade triggers handle that). It does NOT cover events.events
  self-references (caused_by_event_id / causal_chain_root) -- this script
  detects those separately and nulls out dangling references as part of
  the same transaction, and reports what it changed.

ALWAYS run without --apply first and read the report.

Usage:
  # Dry run: find groups of events with identical (agent_id, event_type, summary)
  python brainctl_event_cleanup.py PATH_TO_BRAIN_DB

  # Only flag groups with N or more duplicates (default 2)
  python brainctl_event_cleanup.py PATH --min-group 3

  # Actually delete duplicates, keeping the earliest event in each group
  python brainctl_event_cleanup.py PATH --apply

  # Keep the latest instead of the earliest
  python brainctl_event_cleanup.py PATH --apply --keep last

  # Also consider event types that are excluded by default (session_start etc)
  python brainctl_event_cleanup.py PATH --include-event-types session_start,session_end

  # Delete specific event ids (for one-off "incorrect entry" cleanup)
  # -- run without --apply first to see what would be removed
  python brainctl_event_cleanup.py PATH --ids 95,96,97
  python brainctl_event_cleanup.py PATH --ids 95-108 --apply
"""
import sqlite3
import sys
import os
import argparse
from datetime import datetime

# Event types that are EXPECTED to repeat once per session/run and should
# not be treated as "duplicate noise" by auto-detect mode.
RECURRING_EVENT_TYPES = {"session_start", "session_end", "handoff"}


def parse_id_spec(spec):
    """Parse '95,96,99-101' into a sorted list of unique ints.

    Raises ValueError with a descriptive message on malformed parts.
    Reversed ranges (e.g. '108-95') are reported as warnings (printed by
    the caller) rather than silently producing nothing -- this function
    just returns what it can and lets main() report empties.
    """
    ids = set()
    warnings = []
    for part in spec.split(","):
        part = part.strip()
        if not part:
            continue
        if "-" in part:
            lo_s, hi_s = part.split("-", 1)
            try:
                lo, hi = int(lo_s), int(hi_s)
            except ValueError:
                raise ValueError(f"Cannot parse range '{part}' in --ids (expected NN-MM)")
            if lo > hi:
                warnings.append(f"range '{part}' is reversed (lo > hi) -- contributes 0 ids")
                continue
            ids.update(range(lo, hi + 1))
        else:
            try:
                ids.add(int(part))
            except ValueError:
                raise ValueError(f"Cannot parse id '{part}' in --ids (expected an integer)")
    return sorted(ids), warnings


def find_duplicate_groups(conn, min_group, exclude_event_types):
    c = conn.cursor()
    if exclude_event_types:
        placeholders = ",".join("?" for _ in exclude_event_types)
        where = f"WHERE event_type NOT IN ({placeholders})"
        params = (*exclude_event_types, min_group)
    else:
        where = ""
        params = (min_group,)
    c.execute(f"""
        SELECT agent_id, event_type, summary, COUNT(*) as n,
               GROUP_CONCAT(id), MIN(created_at), MAX(created_at)
        FROM events
        {where}
        GROUP BY agent_id, event_type, summary
        HAVING n >= ?
        ORDER BY n DESC
    """, params)
    # NOTE: GROUP_CONCAT order is unspecified by SQLite. Every consumer of
    # the id list below MUST sort it itself -- do not remove the sorted()
    # calls in main() assuming this query already orders them.
    return c.fetchall()


def backup_db(conn, db_path):
    """Online backup via sqlite3's backup API -- safe for a live WAL-mode
    db, unlike copying the .db/-wal/-shm files with shutil (which can
    capture an inconsistent snapshot if there's concurrent activity)."""
    ts = datetime.now().strftime("%Y%m%d-%H%M%S")
    backup_path = f"{db_path}.bak-{ts}"
    dest = sqlite3.connect(backup_path)
    try:
        conn.backup(dest)
    finally:
        dest.close()
    return backup_path


def find_dangling_refs(conn, delete_ids):
    """Find events NOT in delete_ids whose caused_by_event_id or
    causal_chain_root points at an id we're about to delete. There is no
    cascade/ON DELETE for these self-referential FKs, so left alone they
    become dangling references after the DELETE."""
    if not delete_ids:
        return []
    placeholders = ",".join("?" for _ in delete_ids)
    c = conn.cursor()
    c.execute(f"""
        SELECT id, caused_by_event_id, causal_chain_root
        FROM events
        WHERE id NOT IN ({placeholders})
          AND (caused_by_event_id IN ({placeholders}) OR causal_chain_root IN ({placeholders}))
    """, (*delete_ids, *delete_ids, *delete_ids))
    return c.fetchall()


def clear_dangling_refs(conn, delete_ids, referencing_rows):
    """Set caused_by_event_id / causal_chain_root to NULL on rows that
    reference a to-be-deleted id, so we don't leave dangling FKs."""
    delete_set = set(delete_ids)
    c = conn.cursor()
    for row_id, caused_by, causal_root in referencing_rows:
        new_caused_by = None if caused_by in delete_set else caused_by
        new_causal_root = None if causal_root in delete_set else causal_root
        c.execute(
            "UPDATE events SET caused_by_event_id = ?, causal_chain_root = ? WHERE id = ?",
            (new_caused_by, new_causal_root, row_id),
        )


def do_delete(conn, delete_ids, label):
    """Check for dangling self-references, clear them, delete, and verify
    rowcount matches expectations. Returns (deleted_count, cleared_count)."""
    referencing_rows = find_dangling_refs(conn, delete_ids)
    cleared_count = len(referencing_rows)
    if referencing_rows:
        print(f"\n{cleared_count} other event(s) reference ids being deleted via "
              f"caused_by_event_id/causal_chain_root -- clearing those references:")
        for row_id, caused_by, causal_root in referencing_rows:
            print(f"  event {row_id}: caused_by_event_id={caused_by}, causal_chain_root={causal_root}")
        clear_dangling_refs(conn, delete_ids, referencing_rows)

    placeholders = ",".join("?" for _ in delete_ids)
    c = conn.cursor()
    c.execute(f"DELETE FROM events WHERE id IN ({placeholders})", delete_ids)
    deleted_count = c.rowcount
    conn.commit()

    if deleted_count != len(delete_ids):
        print(f"\nWARNING: expected to delete {len(delete_ids)} {label}, "
              f"but {deleted_count} row(s) were actually removed. Some ids may "
              f"have already been deleted by another process.")
    return deleted_count, cleared_count


def main():
    parser = argparse.ArgumentParser(
        description="Clean up duplicate/incorrect brainctl events",
    )
    parser.add_argument("db_path",
                         help="Path to brain.db (no default -- be explicit about which agent's db)")
    parser.add_argument("--min-group", type=int, default=2,
                         help="Minimum duplicate count to flag a group (default 2)")
    parser.add_argument("--keep", choices=["first", "last"], default="first",
                         help="Which event in each duplicate group to keep, by id order (default first)")
    parser.add_argument("--include-event-types", type=str, default="",
                         help="Comma-separated event_types to NOT exclude from auto-detect "
                              f"(default exclusions: {','.join(sorted(RECURRING_EVENT_TYPES))})")
    parser.add_argument("--ids", type=str, default=None,
                         help="Explicit event id(s)/range(s) to delete, e.g. '95,96,99-101'")
    parser.add_argument("--apply", action="store_true",
                         help="Actually delete (default is dry-run / report only)")
    args = parser.parse_args()

    if not os.path.isfile(args.db_path):
        print(f"ERROR: {args.db_path} is not a file")
        sys.exit(1)

    try:
        conn = sqlite3.connect(args.db_path, timeout=30)
        conn.execute("SELECT 1 FROM events LIMIT 1")
    except sqlite3.Error as e:
        # "file is not a database" raises sqlite3.DatabaseError, while
        # "no such table: events" / locked / unable to open raise
        # sqlite3.OperationalError (a subclass of DatabaseError). Catch
        # the common base sqlite3.Error to cover both.
        print(f"ERROR: could not open {args.db_path} as a brainctl db: {e}")
        sys.exit(1)

    try:
        if args.ids is not None:
            try:
                ids_to_delete, warnings = parse_id_spec(args.ids)
            except ValueError as e:
                print(f"ERROR: {e}")
                sys.exit(1)
            for w in warnings:
                print(f"WARNING: {w}")
            if not ids_to_delete:
                print("No ids to look up (empty --ids after parsing).")
                return

            c = conn.cursor()
            placeholders = ",".join("?" for _ in ids_to_delete)
            c.execute(f"SELECT id, agent_id, event_type, summary, created_at "
                      f"FROM events WHERE id IN ({placeholders}) ORDER BY id", ids_to_delete)
            rows = c.fetchall()
            if not rows:
                print("No matching events found for given ids.")
                return

            found_ids = {r[0] for r in rows}
            missing = [i for i in ids_to_delete if i not in found_ids]
            if missing:
                print(f"NOTE: {len(missing)} requested id(s) do not exist in this db: {missing}")

            print(f"{'Will delete' if args.apply else 'Would delete (dry run)'} {len(rows)} event(s):")
            for row in rows:
                summary_line = row[3].replace("\n", " ")[:80]
                print(f"  id={row[0]:>6}  {row[4]}  [{row[1]}/{row[2]}]  {summary_line}")

            if args.apply:
                backup_path = backup_db(conn, args.db_path)
                print(f"\nBackup written to {backup_path}")
                ids_found = sorted(found_ids)
                deleted, cleared = do_delete(conn, ids_found, "event(s)")
                print(f"Deleted {deleted} event(s); cleared {cleared} dangling reference(s).")
            else:
                print("\nDry run only -- pass --apply to actually delete.")
            return

        # Duplicate-group mode
        include_types = {t.strip() for t in args.include_event_types.split(",") if t.strip()}
        exclude_types = RECURRING_EVENT_TYPES - include_types
        if exclude_types:
            print(f"Excluding recurring event types from auto-detect: {sorted(exclude_types)}")
            print("(use --include-event-types to also scan these)\n")

        groups = find_duplicate_groups(conn, args.min_group, exclude_types)
        if not groups:
            suffix = " (after excluding recurring event types)" if exclude_types else ""
            print(f"No duplicate groups found with >= {args.min_group} occurrences{suffix}.")
            return

        total_to_delete = 0
        delete_ids = []
        print(f"Found {len(groups)} duplicate group(s) (>= {args.min_group} occurrences):\n")
        for agent_id, event_type, summary, n, id_csv, first_ts, last_ts in groups:
            ids = sorted(int(x) for x in id_csv.split(","))
            keep_id = ids[0] if args.keep == "first" else ids[-1]
            to_delete = [i for i in ids if i != keep_id]
            total_to_delete += len(to_delete)
            delete_ids.extend(to_delete)
            summary_line = summary.replace("\n", " ")[:100]
            print(f"  [{agent_id}/{event_type}] x{n}  {first_ts} .. {last_ts}")
            print(f"    summary: {summary_line}")
            print(f"    ids: {ids}")
            print(f"    keep: {keep_id}, delete: {to_delete}\n")

        print(f"Total events to delete: {total_to_delete}")

        if not delete_ids:
            print("\nNothing to delete (every flagged group only has one member after keep).")
            return

        if args.apply:
            backup_path = backup_db(conn, args.db_path)
            print(f"\nBackup written to {backup_path}")
            deleted, cleared = do_delete(conn, delete_ids, "events")
            print(f"Deleted {deleted} event(s); cleared {cleared} dangling reference(s).")
        else:
            print("\nDry run only -- pass --apply to actually delete.")
    finally:
        conn.close()


if __name__ == "__main__":
    main()