Ben Stull
779ba6db59
Brings the §1 bot wrapper, the §4 cache (webhook + reconciler), the §5 schema (six numbered migrations), Gitea OAuth + §6 user provisioning, the §7 catalog left pane, and the propose-to-merge vertical: propose modal opens an idea PR against the meta repo, an owner merges from the pending-idea view, the cache picks it up via webhook or reconciler sweep, and the catalog renders the new super-draft. Per §1 the bot is the only Git writer; every commit, branch creation, and PR merge carries the §6.5 On-behalf-of: trailer and an `actions` audit row. Per §4 the cache is never written from a user action — it's webhook+reconciler only. Covered by `backend/tests/test_propose_vertical.py` against an in-process Gitea simulator. Co-Authored-By: Claude Opus 4.7 (1M context) <noreply@anthropic.com>
58 lines
3.2 KiB
SQL
58 lines
3.2 KiB
SQL
-- §5 / §15: the notification substrate. Per §15.7, per-row read_at is what
|
|
-- the inbox needs because triage is per-event. Per §15.9, system-generated
|
|
-- events carry actor_user_id = NULL; the bot account does not appear here.
|
|
--
|
|
-- Fan-out is at signal-generation time per §15.7: each recipient gets their
|
|
-- own row. This trades storage for query simplicity at the inbox surface,
|
|
-- and the §15.5 digest's exclusion rules need per-recipient timestamps
|
|
-- (email_sent_at, digest_included_at) anyway.
|
|
|
|
CREATE TABLE notifications (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
recipient_user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
|
event_kind TEXT NOT NULL, -- §15.1 enum, extensible
|
|
rfc_slug TEXT,
|
|
branch_name TEXT,
|
|
pr_number INTEGER,
|
|
thread_id INTEGER REFERENCES threads(id) ON DELETE SET NULL,
|
|
change_id INTEGER REFERENCES changes(id) ON DELETE SET NULL,
|
|
actor_user_id INTEGER REFERENCES users(id) ON DELETE SET NULL, -- NULL = system per §15.9
|
|
payload TEXT NOT NULL DEFAULT '{}', -- JSON: rendered row text + extras
|
|
created_at TEXT NOT NULL DEFAULT (datetime('now')),
|
|
read_at TEXT, -- §15.7 per-event triage
|
|
email_sent_at TEXT, -- §15.5 exclusion rule 1
|
|
digest_included_at TEXT -- §15.5 exclusion rule 3 audit
|
|
);
|
|
|
|
CREATE INDEX idx_notifications_inbox ON notifications (recipient_user_id, read_at, created_at);
|
|
CREATE INDEX idx_notifications_scope ON notifications (rfc_slug, branch_name, pr_number);
|
|
CREATE INDEX idx_notifications_digest ON notifications (recipient_user_id, digest_included_at);
|
|
|
|
-- §15.5: per-recipient digest emissions. The period_start / period_end
|
|
-- pair makes the event-window dedup queryable at audit time.
|
|
CREATE TABLE notification_digests (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
recipient_user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
|
sent_at TEXT NOT NULL DEFAULT (datetime('now')),
|
|
period_start TEXT NOT NULL,
|
|
period_end TEXT NOT NULL,
|
|
signal_ids_included TEXT NOT NULL DEFAULT '[]' -- JSON array of notification ids
|
|
);
|
|
|
|
CREATE INDEX idx_digests_recipient ON notification_digests (recipient_user_id, sent_at);
|
|
|
|
-- §15.8: per-user notification mute. Notification-volume only; never
|
|
-- gates content visibility. The §6.2 clarification reads: an admin or
|
|
-- arbiter exercising authority on an RFC cannot mute participants on
|
|
-- that RFC. Enforcement of the role-exemption is in the API layer; the
|
|
-- schema just stores the mute.
|
|
CREATE TABLE notification_user_mutes (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
muter_user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
|
muted_user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
|
muted_at TEXT NOT NULL DEFAULT (datetime('now')),
|
|
UNIQUE (muter_user_id, muted_user_id)
|
|
);
|
|
|
|
CREATE INDEX idx_user_mutes_muter ON notification_user_mutes (muter_user_id);
|