Keysat
5faa5ae4d6
The email-capture "proposed grid notes" gain two review surfaces:
1. Inline source email — each proposed-note card on the Email Capture page
gets a "View email" toggle that lazily fetches the existing
GET /api/email/detail and shows from/to/cc/date/subject + scrollable body,
so a reviewer can judge the note against the email it was drafted from.
2. CRM->Matrix review bridge — the CRM (box, stdlib, no matrix-nio) can't post
to Matrix, so the intake bot (Spark) PULLS: GET /api/intake/email-proposals
returns to_post/open/to_close work-lists; the bot posts a review card
(metadata + snippet + draft note) to a dedicated review room
(MATRIX_EMAIL_REVIEW_ROOM) and relays in-thread yes / no / NL-edit
(POST .../{id}/decide, note revised via local Qwen). Decisions sync both
ways: web decide -> bot announces + closes the thread; Matrix decide -> the
web panel's ~25s poll clears the card. State lives CRM-side in the new
email_proposal_matrix side row (email-integration migration 0003, additive
+ idempotent CREATE TABLE IF NOT EXISTS), so it survives a bot restart.
Adds a 'bot' role (authenticated, never admin; require_bot_or_admin) to gate
the email-proposal endpoints rather than handing the bot full admin — the
principled base for the coming agentic capabilities. Role controls reach;
the draft->approve gate still controls autonomy (a human approves every write).
Deploy split: endpoints + migration + role + frontend ship in the s9pk; the
bot poll loop + review-room handling ship on the Spark. The bot's CRM user
must be flipped member->bot and joined to the review room (one-time).
Tests: backend/test_email_proposal_matrix.py + matrix_intake/test_email_proposals.py
(30/30 suite green, render-smoke green, migration verified twice on a DB copy).
31 lines
2.0 KiB
SQL
31 lines
2.0 KiB
SQL
-- ============================================================================
|
|
-- email_proposal_matrix — Matrix-review state for an email_activity_proposal,
|
|
-- kept 1:1 with the proposal (proposal_id PK). The CRM runs on the box and has
|
|
-- no matrix-nio, so it cannot post to Matrix itself: the intake bot (on the Spark)
|
|
-- PULLS pending proposals, posts a review card to the dedicated Matrix review room,
|
|
-- and writes the thread-root event_id back here. Persisting it CRM-side (not just in
|
|
-- the bot's memory) keeps both surfaces in sync and survives a bot restart.
|
|
--
|
|
-- A SIDE TABLE rather than new columns on email_activity_proposals because the
|
|
-- email-integration migration runner (email_integration/db.py:apply_migrations)
|
|
-- re-runs every .sql file on every boot via executescript with no ledger — so
|
|
-- CREATE TABLE IF NOT EXISTS is idempotent, whereas ALTER ... ADD COLUMN would throw
|
|
-- "duplicate column" on the second boot and abort startup. Reversal: DROP TABLE
|
|
-- (this runner has no .down.sql convention; cf. 0001/0002).
|
|
--
|
|
-- posted_at — set once the bot has posted the review card (event_id = thread root).
|
|
-- closed_at — set when the thread is resolved: either the bot decided in-thread, OR
|
|
-- the bot announced a web-side decision. A posted+decided proposal with
|
|
-- closed_at NULL is exactly the bot's signal to post "decided on the web"
|
|
-- into the thread and then close it.
|
|
-- ============================================================================
|
|
CREATE TABLE IF NOT EXISTS email_proposal_matrix (
|
|
proposal_id TEXT PRIMARY KEY,
|
|
event_id TEXT, -- Matrix thread-root event id of the posted review card
|
|
posted_at TEXT,
|
|
closed_at TEXT,
|
|
created_at TEXT DEFAULT (datetime('now')),
|
|
FOREIGN KEY(proposal_id) REFERENCES email_activity_proposals(id) ON DELETE CASCADE
|
|
);
|
|
CREATE INDEX IF NOT EXISTS idx_email_proposal_matrix_event ON email_proposal_matrix(event_id);
|