AI-powered virtual executive team — a single coherent executive persona backed by 8 specialist agents (FastAPI + Next.js).
"""Always in the loop: dated notes of what happened, that the Executive can
recall later.
A note is an *event*, never an instruction: "Oct 3: told Dana Lee the Q4
price list comes Friday", with the exact words it came from (``quote``), when
it happened, where (``channel``, ``conversation_key``), who it was with
(``counterpart``) and how far it is trusted (``trust``). Code sets every one
of those but the summary; a model only writes that sentence, and the quote it
rests on is checked word for word before anything is stored
(``memory.history_notes``).
**What writes notes.** Two things, both reading only the person's own words,
so nothing another person wrote can become a note; that is their own word,
so ``trust`` is ``high``:
- a reply the Executive drafted in someone's own mailbox (Act as me), after
that person approved and sent it (``delegation.reply_send`` →
``memory.history_notes``);
- what they say to the Executive in a chat where it knows it is them: the web
chat signed in, their own Slack, Discord or Telegram, or any channel whose
adapter verified the sender (``Session.speaker_verified``)
(``memory.history_chat``). Messages between other people are never read.
**Who reads them.** A note's ``visibility`` is ``private``: its own person
alone, never the principal, and only in a conversation nobody else can read
(``orchestrator.history_tools``), even when it was noted in a shared thread. Each person sees, corrects, pins and
forgets their own notes in Memories → History (``/memories/history``).
**Switches.** Each person turns it on for themselves (``reply_notes``,
absent means off; despite the name it covers every channel). Any team member
on the People list may (``can_keep_notes``); notes from email replies still
need Act as me, which writes them. The owner sets how long notes last for the company
(``retention_days``: 30, 90, 365, or until forgotten; 90 when unset), and a
person may shorten it for their own notes, never lengthen it. A pinned note
never expires. "Don't remember this" forgets a conversation's notes and
keeps it from being noted again (``history_excluded``).
Every function here is per company (the episodic DB), never raises on a
read, and logs codes, never note text.
"""
from __future__ import annotations
import hashlib
import logging
import sqlite3
from dataclasses import dataclass
from datetime import UTC, date, datetime, timedelta
from pathlib import Path
from typing import Any
from openexecutive.memory.history_schema import (
COMPANY_TABLE,
EXCLUDED_TABLE,
NOTES_TABLE,
PASSES_TABLE,
PERSON_TABLE,
REMINDERS_TABLE,
SHARING_TABLE,
ensure_schema,
)
logger = logging.getLogger(__name__)
DEFAULT_RETENTION_DAYS = 90
# What the owner and each person may pick. None is "until forgotten".
RETENTION_CHOICES: tuple[int | None, ...] = (30, 90, 365, None)
KINDS: tuple[str, ...] = ("promised", "agreed", "declined", "answered", "asked", "shared")
TRUST_LEVELS: tuple[str, ...] = ("high", "medium", "low")
SOURCE_APPROVED_REPLY = "approved_reply"
SOURCE_CHAT_MESSAGE = "chat_message"
CHANNEL_EMAIL = "email"
MAX_SUMMARY_CHARS = 240
MAX_QUOTE_CHARS = 400
MAX_CORRECTION_CHARS = 400
MAX_LIST = 200
@dataclass(frozen=True)
class NewNote:
kind: str
summary: str
quote: str
due_date: str | None = None
@dataclass(frozen=True)
class Note:
id: int
person_id: int
visibility: str
source: str
channel: str
conversation_key: str
counterpart: str
subject: str
kind: str
summary: str
quote: str
due_date: str | None
trust: str
occurred_at: str
created_at: str
expires_at: str | None
pinned: bool
correction: str | None
corrected_at: str | None
def as_dict(self) -> dict[str, Any]:
return {
"id": self.id,
"source": self.source,
"channel": self.channel,
"conversation_key": self.conversation_key,
"counterpart": self.counterpart,
"subject": self.subject,
"kind": self.kind,
"summary": self.summary,
"quote": self.quote,
"due_date": self.due_date,
"trust": self.trust,
"occurred_at": self.occurred_at,
"created_at": self.created_at,
"expires_at": self.expires_at,
"pinned": self.pinned,
"correction": self.correction,
"corrected_at": self.corrected_at,
}
@dataclass(frozen=True)
class PersonSettings:
reply_notes: bool = False
retention_days: int | None = None
class SettingError(ValueError):
"""A setting that isn't allowed, with a message for the person."""
_COLUMNS = (
"id, person_id, visibility, source, channel, conversation_key, counterpart, subject, kind, "
"summary, quote, due_date, trust, occurred_at, created_at, expires_at, pinned, correction, corrected_at"
)
def _now(now: datetime | None) -> datetime:
return now or datetime.now(UTC)
def _db_path(db_path: Path | None) -> Path:
if db_path is not None:
return db_path
from openexecutive.memory import episodic
return Path(episodic.DB_PATH)
def _connect(db_path: Path | None) -> sqlite3.Connection:
conn = sqlite3.connect(str(_db_path(db_path)))
ensure_schema(conn)
return conn
def can_keep_notes(person: Any) -> bool:
"""Whether ``person`` may turn "Keep track of what happens" on: someone on
the People list who works here (a team member, the principal included),
not archived. Never a contact."""
return (
person is not None
and getattr(person, "id", None) is not None
and not getattr(person, "archived", False)
and getattr(person, "kind", None) == "team"
)
def conversation_key(channel: str, ref: str) -> str:
"""A stable key for one conversation (a mail thread id), so its notes can
be found and forgotten together without keeping the raw id twice."""
return hashlib.sha256(f"{channel}\x00{ref}".encode()).hexdigest()[:32]
def _row_note(row: tuple[Any, ...]) -> Note:
return Note(
id=int(row[0]), person_id=int(row[1]), visibility=str(row[2]), source=str(row[3]),
channel=str(row[4]), conversation_key=str(row[5]), counterpart=str(row[6] or ""),
subject=str(row[7] or ""), kind=str(row[8]), summary=str(row[9]), quote=str(row[10]),
due_date=row[11], trust=str(row[12]), occurred_at=str(row[13]), created_at=str(row[14]),
expires_at=row[15], pinned=bool(row[16]), correction=row[17], corrected_at=row[18],
)
# --- Settings ---------------------------------------------------------------
def _valid_retention(days: Any) -> int | None:
if days is None:
return None
if isinstance(days, bool) or not isinstance(days, int) or days not in RETENTION_CHOICES:
raise SettingError("Choose 30, 90 or 365 days, or keep notes until they're forgotten.")
return days
def company_retention(*, db_path: Path | None = None) -> int | None:
"""How long notes last, in days, for the company (None: until
forgotten). The built-in default when the owner never set it, or on any
read error."""
if not _db_path(db_path).exists():
return DEFAULT_RETENTION_DAYS
try:
conn = _connect(db_path)
try:
row = conn.execute(f"SELECT retention_days FROM {COMPANY_TABLE} WHERE id = 1").fetchone() # noqa: S608 — constant table name
finally:
conn.close()
except Exception:
logger.warning("history: couldn't read the company retention — using the default", exc_info=True)
return DEFAULT_RETENTION_DAYS
if row is None:
return DEFAULT_RETENTION_DAYS
return int(row[0]) if row[0] is not None else None
def valid_retention(days: Any) -> int | None:
"""``days`` as a company retention, or SettingError."""
return _valid_retention(days)
def valid_person_retention(days: Any, company: int | None) -> int | None:
"""``days`` as a person's own retention under the company's ``company``:
one of the choices, never longer. SettingError otherwise."""
value = _valid_retention(days)
if value is not None and company is not None and value > company:
raise SettingError(f"Your notes can't last longer than the company's {company} days.")
return value
def set_company_retention(days: Any, *, by: str, db_path: Path | None = None, now: datetime | None = None) -> int | None:
"""Set the company default. Shortening it shortens everyone's stored
notes too (a person's own setting can only be shorter). Returns it."""
value = _valid_retention(days)
moment = _now(now).isoformat()
conn = _connect(db_path)
try:
conn.execute(
f"INSERT INTO {COMPANY_TABLE} (id, retention_days, updated_at, updated_by) VALUES (1, ?, ?, ?) " # noqa: S608 — constant table name
"ON CONFLICT(id) DO UPDATE SET retention_days = excluded.retention_days, "
"updated_at = excluded.updated_at, updated_by = excluded.updated_by",
(value, moment, by),
)
# A person's own choice may now be longer than the company's: clamp it.
if value is not None:
conn.execute(
f"UPDATE {PERSON_TABLE} SET retention_days = ? " # noqa: S608 — constant table name
"WHERE retention_days IS NOT NULL AND retention_days > ?",
(value, value),
)
conn.commit()
finally:
conn.close()
_reapply_expiry(None, db_path=db_path)
return value
def person_settings(person_id: int | None, *, db_path: Path | None = None) -> PersonSettings:
"""A person's own switches. Off, and the company default, when unset or
unreadable."""
if person_id is None or not _db_path(db_path).exists():
return PersonSettings()
try:
conn = _connect(db_path)
try:
row = conn.execute(
f"SELECT reply_notes, retention_days FROM {PERSON_TABLE} WHERE person_id = ?", # noqa: S608 — constant table name
(person_id,),
).fetchone()
finally:
conn.close()
except Exception:
logger.warning("history: couldn't read a person's settings — treating them as off", exc_info=True)
return PersonSettings()
if row is None:
return PersonSettings()
return PersonSettings(reply_notes=bool(row[0]), retention_days=int(row[1]) if row[1] is not None else None)
def set_person_settings(
person_id: int,
*,
by: str,
reply_notes: bool | None = None,
retention_days: Any = ...,
db_path: Path | None = None,
now: datetime | None = None,
) -> PersonSettings:
"""Change a person's own switches (only the fields given). Their
retention may be shorter than the company's, never longer; None means
"the company default". Returns the settings now in force."""
current = person_settings(person_id, db_path=db_path)
notes = current.reply_notes if reply_notes is None else bool(reply_notes)
days = current.retention_days
if retention_days is not ...:
days = valid_person_retention(retention_days, company_retention(db_path=db_path))
conn = _connect(db_path)
try:
conn.execute(
f"INSERT INTO {PERSON_TABLE} (person_id, reply_notes, retention_days, updated_at, updated_by) " # noqa: S608 — constant table name
"VALUES (?, ?, ?, ?, ?) ON CONFLICT(person_id) DO UPDATE SET "
"reply_notes = excluded.reply_notes, retention_days = excluded.retention_days, "
"updated_at = excluded.updated_at, updated_by = excluded.updated_by",
(person_id, int(notes), days, _now(now).isoformat(), by),
)
conn.commit()
finally:
conn.close()
if retention_days is not ...:
_reapply_expiry(person_id, db_path=db_path)
return PersonSettings(reply_notes=notes, retention_days=days)
def can_share_work_style(person: Any) -> bool:
"""Whether ``person`` may turn "Share my work style with the team" on:
anyone who may keep notes, in a workspace set up for a team. A solo
workspace has nobody to share with."""
if not can_keep_notes(person):
return False
try:
from openexecutive.memory.workspace_settings import get_workspace
return get_workspace().mode == "team"
except Exception:
logger.warning("history: couldn't read the workspace mode — sharing unavailable", exc_info=True)
return False
def shares_work_style(person_id: int | None, *, db_path: Path | None = None) -> bool:
"""Whether ``person_id`` turned "Share my work style with the team" on.
Off when unset or unreadable (fails closed)."""
if person_id is None or not _db_path(db_path).exists():
return False
try:
conn = _connect(db_path)
try:
row = conn.execute(
f"SELECT enabled FROM {SHARING_TABLE} WHERE person_id = ?", # noqa: S608 — constant table name
(person_id,),
).fetchone()
finally:
conn.close()
except Exception:
logger.warning("history: couldn't read a person's sharing switch — treating it as off", exc_info=True)
return False
return bool(row is not None and row[0])
def set_shares_work_style(
person_id: int, enabled: bool, *, by: str, db_path: Path | None = None, now: datetime | None = None,
) -> bool:
"""Turn a person's own "Share my work style with the team" on or off.
Returns the switch now in force."""
conn = _connect(db_path)
try:
conn.execute(
f"INSERT INTO {SHARING_TABLE} (person_id, enabled, updated_at, updated_by) " # noqa: S608 — constant table name
"VALUES (?, ?, ?, ?) ON CONFLICT(person_id) DO UPDATE SET enabled = excluded.enabled, "
"updated_at = excluded.updated_at, updated_by = excluded.updated_by",
(person_id, int(bool(enabled)), _now(now).isoformat(), by),
)
conn.commit()
finally:
conn.close()
return bool(enabled)
def effective_retention(person_id: int, *, db_path: Path | None = None) -> int | None:
"""The days a new note of this person's lasts: the shorter of theirs and
the company's (None: until forgotten)."""
company = company_retention(db_path=db_path)
own = person_settings(person_id, db_path=db_path).retention_days
choices = [d for d in (company, own) if d is not None]
return min(choices) if choices else None
def _expiry(occurred_at: str, days: int | None) -> str | None:
if days is None:
return None
try:
start = datetime.fromisoformat(occurred_at)
except ValueError:
start = datetime.now(UTC)
if start.tzinfo is None:
start = start.replace(tzinfo=UTC)
return (start + timedelta(days=days)).isoformat()
def _reapply_expiry(person_id: int | None, *, db_path: Path | None = None) -> None:
"""Recompute ``expires_at`` on unpinned notes after a retention change
(one person's, or everyone's), from when each note's clock started."""
conn = _connect(db_path)
try:
if person_id is None:
people = [int(r[0]) for r in conn.execute(f"SELECT DISTINCT person_id FROM {NOTES_TABLE}")] # noqa: S608 — constant table name
else:
people = [person_id]
finally:
conn.close()
for pid in people:
days = effective_retention(pid, db_path=db_path)
conn = _connect(db_path)
try:
rows = conn.execute(
f"SELECT id, COALESCE(kept_from, occurred_at) FROM {NOTES_TABLE} WHERE person_id = ? AND pinned = 0", # noqa: S608 — constant table name
(pid,),
).fetchall()
conn.executemany(
f"UPDATE {NOTES_TABLE} SET expires_at = ? WHERE id = ?", # noqa: S608 — constant table name
[(_expiry(str(occurred), days), int(nid)) for nid, occurred in rows],
)
conn.commit()
finally:
conn.close()
# --- Exclusions -------------------------------------------------------------
def is_excluded(person_id: int, key: str, *, db_path: Path | None = None) -> bool:
"""Whether the person asked not to remember this conversation. Fails
closed: a read error counts as excluded."""
if not _db_path(db_path).exists():
return False
try:
conn = _connect(db_path)
try:
row = conn.execute(
f"SELECT 1 FROM {EXCLUDED_TABLE} WHERE person_id = ? AND conversation_key = ?", # noqa: S608 — constant table name
(person_id, key),
).fetchone()
finally:
conn.close()
except Exception:
logger.warning("history: couldn't read the exclusions — treating it as excluded", exc_info=True)
return True
return row is not None
def forget_conversation(person_id: int, key: str, *, db_path: Path | None = None, now: datetime | None = None) -> int:
""""Don't remember this": forget the person's notes from one
conversation and keep it from being noted again. Returns how many notes
were forgotten."""
conn = _connect(db_path)
try:
deleted = conn.execute(
f"DELETE FROM {NOTES_TABLE} WHERE person_id = ? AND conversation_key = ?", # noqa: S608 — constant table name
(person_id, key),
).rowcount
conn.execute(
f"INSERT OR IGNORE INTO {EXCLUDED_TABLE} (person_id, conversation_key, created_at) VALUES (?, ?, ?)", # noqa: S608 — constant table name
(person_id, key, _now(now).isoformat()),
)
conn.commit()
finally:
conn.close()
return int(deleted or 0)
# --- Pacing -----------------------------------------------------------------
def take_pass(person_id: int, day: str, limit: int, *, db_path: Path | None = None) -> bool:
"""Count one note pass for ``person_id`` on ``day`` (YYYY-MM-DD) when
fewer than ``limit`` ran that day; False once they are spent. Held in the
company DB, so the cap holds across workers and restarts. Fails closed: an
unreadable count is a spent one."""
try:
conn = _connect(db_path)
try:
conn.execute("BEGIN IMMEDIATE")
conn.execute(f"DELETE FROM {PASSES_TABLE} WHERE day < ?", (day,)) # noqa: S608 — constant table name
row = conn.execute(
f"SELECT passes FROM {PASSES_TABLE} WHERE person_id = ? AND day = ?", # noqa: S608 — constant table name
(person_id, day),
).fetchone()
if row is not None and int(row[0]) >= limit:
conn.rollback()
return False
conn.execute(
f"INSERT INTO {PASSES_TABLE} (person_id, day, passes) VALUES (?, ?, 1) " # noqa: S608 — constant table name
"ON CONFLICT(person_id, day) DO UPDATE SET passes = passes + 1",
(person_id, day),
)
conn.commit()
finally:
conn.close()
except Exception:
logger.warning("history: couldn't count a note pass — skipping it", exc_info=True)
return False
return True
# --- Notes ------------------------------------------------------------------
def add_notes(
person_id: int,
notes: list[NewNote],
*,
source: str,
channel: str,
conversation_ref: str,
counterpart: str,
subject: str,
trust: str,
occurred_at: datetime,
db_path: Path | None = None,
now: datetime | None = None,
) -> list[int]:
"""Store checked notes as the person's own private notes. Nothing is
stored for an excluded conversation. Returns the new ids."""
if trust not in TRUST_LEVELS:
raise ValueError("unknown trust level")
key = conversation_key(channel, conversation_ref)
if not notes or is_excluded(person_id, key, db_path=db_path):
return []
when = occurred_at.astimezone(UTC).isoformat()
expires = _expiry(when, effective_retention(person_id, db_path=db_path))
created = _now(now).isoformat()
ids: list[int] = []
conn = _connect(db_path)
try:
# Check again under the write lock: a "Don't remember this" that lands
# while the note-taker was working must still win.
if not conn.in_transaction:
conn.execute("BEGIN IMMEDIATE")
if conn.execute(
f"SELECT 1 FROM {EXCLUDED_TABLE} WHERE person_id = ? AND conversation_key = ?", # noqa: S608 — constant table name
(person_id, key),
).fetchone():
conn.rollback()
return []
for note in notes:
if note.kind not in KINDS:
raise ValueError("unknown kind")
cur = conn.execute(
f"INSERT INTO {NOTES_TABLE} (person_id, visibility, source, channel, conversation_key, " # noqa: S608 — constant table name
"counterpart, subject, kind, summary, quote, due_date, trust, occurred_at, created_at, expires_at) "
"VALUES (?, 'private', ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)",
(
person_id, source, channel, key, counterpart[:200], subject[:200], note.kind,
note.summary[:MAX_SUMMARY_CHARS], note.quote[:MAX_QUOTE_CHARS], note.due_date,
trust, when, created, expires,
),
)
ids.append(int(cur.lastrowid or 0))
conn.commit()
finally:
conn.close()
return ids
def _live_clause() -> str:
return "(expires_at IS NULL OR expires_at > ?)"
def list_notes(
person_id: int | None,
*,
query: str | None = None,
limit: int = 50,
db_path: Path | None = None,
now: datetime | None = None,
) -> list[Note]:
"""A person's own notes that haven't expired, newest first. ``query``
narrows them to notes whose summary, counterpart, subject or quote
mention it. Empty on any read error."""
if person_id is None or not _db_path(db_path).exists():
return []
sql = (
f"SELECT {_COLUMNS} FROM {NOTES_TABLE} WHERE person_id = ? AND {_live_clause()}" # noqa: S608 — constant table name and columns
)
params: list[Any] = [person_id, _now(now).isoformat()]
words = [w for w in (query or "").lower().split() if len(w) > 1][:6]
for word in words:
like = f"%{word.replace('%', '').replace('_', '')}%"
sql += (
" AND (lower(summary) LIKE ? OR lower(counterpart) LIKE ? OR lower(subject) LIKE ?"
" OR lower(quote) LIKE ? OR lower(coalesce(correction, '')) LIKE ?)"
)
params.extend([like] * 5)
sql += " ORDER BY occurred_at DESC, id DESC LIMIT ?"
params.append(max(1, min(int(limit), MAX_LIST)))
try:
conn = _connect(db_path)
try:
rows = conn.execute(sql, params).fetchall()
finally:
conn.close()
except Exception:
logger.warning("history: couldn't read notes", exc_info=True)
return []
return [_row_note(r) for r in rows]
def get_note(person_id: int, note_id: int, *, db_path: Path | None = None, now: datetime | None = None) -> Note | None:
"""One of the person's own live notes, or None (another person's note is
None too)."""
if not _db_path(db_path).exists():
return None
conn = _connect(db_path)
try:
row = conn.execute(
f"SELECT {_COLUMNS} FROM {NOTES_TABLE} WHERE id = ? AND person_id = ? AND {_live_clause()}", # noqa: S608 — constant table name and columns
(note_id, person_id, _now(now).isoformat()),
).fetchone()
finally:
conn.close()
return _row_note(row) if row else None
def _read_notes(sql: str, params: list[Any], db_path: Path | None) -> list[Note]:
if not _db_path(db_path).exists():
return []
try:
conn = _connect(db_path)
try:
rows = conn.execute(sql, params).fetchall()
finally:
conn.close()
except Exception:
logger.warning("history: couldn't read notes", exc_info=True)
return []
return [_row_note(r) for r in rows]
def _kinds_clause(kinds: tuple[str, ...] | None, params: list[Any]) -> str:
if not kinds:
return ""
params.extend(kinds)
return f" AND kind IN ({', '.join('?' for _ in kinds)})"
def due_notes(
person_id: int | None,
*,
start: date,
end: date,
kinds: tuple[str, ...] | None = None,
sources: tuple[str, ...] | None = None,
limit: int = 20,
db_path: Path | None = None,
now: datetime | None = None,
) -> list[Note]:
"""A person's own live notes with a due date from ``start`` to ``end``
(inclusive), soonest first. ``kinds`` / ``sources`` narrow them. Empty on
any read error."""
if person_id is None:
return []
params: list[Any] = [person_id, _now(now).isoformat(), start.isoformat(), end.isoformat()]
sql = (
f"SELECT {_COLUMNS} FROM {NOTES_TABLE} WHERE person_id = ? AND {_live_clause()}" # noqa: S608 — constant table name and columns
" AND due_date IS NOT NULL AND due_date >= ? AND due_date <= ?"
)
sql += _kinds_clause(kinds, params)
if sources:
sql += f" AND source IN ({', '.join('?' for _ in sources)})"
params.extend(sources)
sql += " ORDER BY due_date, occurred_at, id LIMIT ?"
params.append(max(1, min(int(limit), MAX_LIST)))
return _read_notes(sql, params, db_path)
def notes_since(
person_id: int | None,
since: datetime,
*,
kinds: tuple[str, ...] | None = None,
limit: int = 20,
db_path: Path | None = None,
now: datetime | None = None,
) -> list[Note]:
"""A person's own live notes of what happened at or after ``since``,
oldest first. Empty on any read error."""
if person_id is None:
return []
params: list[Any] = [person_id, _now(now).isoformat(), since.isoformat()]
sql = (
f"SELECT {_COLUMNS} FROM {NOTES_TABLE} WHERE person_id = ? AND {_live_clause()}" # noqa: S608 — constant table name and columns
" AND occurred_at >= ?"
)
sql += _kinds_clause(kinds, params)
sql += " ORDER BY occurred_at, id LIMIT ?"
params.append(max(1, min(int(limit), MAX_LIST)))
return _read_notes(sql, params, db_path)
def people_keeping_notes(*, db_path: Path | None = None) -> list[int]:
"""Everyone who has "Keep track of what happens" on. Empty on any read
error."""
if not _db_path(db_path).exists():
return []
try:
conn = _connect(db_path)
try:
rows = conn.execute(
f"SELECT person_id FROM {PERSON_TABLE} WHERE reply_notes = 1 ORDER BY person_id", # noqa: S608 — constant table name
).fetchall()
finally:
conn.close()
except Exception:
logger.warning("history: couldn't read who keeps notes", exc_info=True)
return []
return [int(r[0]) for r in rows]
def reminded(person_id: int, day: str, *, db_path: Path | None = None) -> bool:
"""Whether ``person_id`` was already reminded on ``day`` (YYYY-MM-DD).
Fails closed: an unreadable record counts as reminded."""
try:
conn = _connect(db_path)
try:
row = conn.execute(
f"SELECT 1 FROM {REMINDERS_TABLE} WHERE person_id = ? AND day = ?", # noqa: S608 — constant table name
(person_id, day),
).fetchone()
finally:
conn.close()
except Exception:
logger.warning("history: couldn't read reminders", exc_info=True)
return True
return row is not None
def mark_reminded(person_id: int, day: str, *, db_path: Path | None = None, now: datetime | None = None) -> bool:
"""Record that ``person_id`` was reminded on ``day``, pruning older days.
False when they already were (another worker got there first) or it
can't be recorded."""
try:
conn = _connect(db_path)
try:
conn.execute("BEGIN IMMEDIATE")
conn.execute(f"DELETE FROM {REMINDERS_TABLE} WHERE day < ?", (day,)) # noqa: S608 — constant table name
inserted = conn.execute(
f"INSERT OR IGNORE INTO {REMINDERS_TABLE} (person_id, day, sent_at) VALUES (?, ?, ?)", # noqa: S608 — constant table name
(person_id, day, _now(now).isoformat()),
).rowcount
conn.commit()
finally:
conn.close()
except Exception:
logger.warning("history: couldn't record a reminder", exc_info=True)
return False
return bool(inserted)
def forget_note(person_id: int, note_id: int, *, db_path: Path | None = None) -> bool:
"""Delete one of the person's own notes. False when it isn't theirs or
is gone."""
conn = _connect(db_path)
try:
deleted = conn.execute(
f"DELETE FROM {NOTES_TABLE} WHERE id = ? AND person_id = ?", # noqa: S608 — constant table name
(note_id, person_id),
).rowcount
conn.commit()
finally:
conn.close()
return bool(deleted)
def pin_note(
person_id: int, note_id: int, pinned: bool, *, db_path: Path | None = None, now: datetime | None = None
) -> Note | None:
"""Pin a note (it never expires) or unpin it (it expires as the person's
retention says, counted from the unpin when it happened earlier, so an
old note isn't lost the moment its pin comes off)."""
note = get_note(person_id, note_id, db_path=db_path, now=now)
if note is None:
return None
conn = _connect(db_path)
try:
if pinned:
conn.execute(
f"UPDATE {NOTES_TABLE} SET pinned = 1, expires_at = NULL WHERE id = ? AND person_id = ?", # noqa: S608 — constant table name
(note_id, person_id),
)
else:
start = max(_now(now).isoformat(), note.occurred_at)
conn.execute(
f"UPDATE {NOTES_TABLE} SET pinned = 0, kept_from = ?, expires_at = ? WHERE id = ? AND person_id = ?", # noqa: S608 — constant table name
(start, _expiry(start, effective_retention(person_id, db_path=db_path)), note_id, person_id),
)
conn.commit()
finally:
conn.close()
return get_note(person_id, note_id, db_path=db_path, now=now)
def correct_note(
person_id: int, note_id: int, text: str, *, db_path: Path | None = None, now: datetime | None = None
) -> Note | None:
"""Keep the note and add the person's own version, which recall shows in
its place. An empty text removes the correction."""
note = get_note(person_id, note_id, db_path=db_path, now=now)
if note is None:
return None
from openexecutive.delegation.ghostwriter import one_line
cleaned = one_line(text or "", MAX_CORRECTION_CHARS)
conn = _connect(db_path)
try:
conn.execute(
f"UPDATE {NOTES_TABLE} SET correction = ?, corrected_at = ? WHERE id = ? AND person_id = ?", # noqa: S608 — constant table name
(cleaned or None, _now(now).isoformat() if cleaned else None, note_id, person_id),
)
conn.commit()
finally:
conn.close()
return get_note(person_id, note_id, db_path=db_path, now=now)
def sweep_expired(*, db_path: Path | None = None, now: datetime | None = None) -> int:
"""Delete notes past their expiry (reads already skip them). Returns how
many. Never raises."""
if not _db_path(db_path).exists():
return 0
try:
conn = _connect(db_path)
try:
deleted = conn.execute(
f"DELETE FROM {NOTES_TABLE} WHERE pinned = 0 AND expires_at IS NOT NULL AND expires_at <= ?", # noqa: S608 — constant table name
(_now(now).isoformat(),),
).rowcount
conn.commit()
finally:
conn.close()
except Exception:
logger.warning("history: sweeping expired notes failed", exc_info=True)
return 0
return int(deleted or 0)