feat(email-ingress): schema for IMAP config + email inboxes (LC-77) #195
Loading…
Reference in a new issue
No description provided.
Delete branch "feat/lc-77-schema"
Deleting a branch is permanent. Although the deleted branch may continue to exist for a short time before it actually gets removed, it CANNOT be undone in most cases. Continue?
Summary
Three migrations plus the supporting type updates so commit 3's IMAP poll loop has somewhere to land. No runtime behavior yet; the email-ingress feature stays fully gated behind commit 3's
spawn_email_poll.Migrations
settings/0005_imap_inbox_config.sql: singleton encrypted-at-rest config row mirroringvapid_keypair(settings/0003). AES-256-GCM sealedpassword_encrypted+password_nonce; plaintexthost/port/tls/username/folder/ingress_domain/enabled.CHECK (id = 1)enforces the singleton.chat/0048_email_inboxes.sql: per-room inboxes table. LC-74 webhooks (chat/0041) mirror:ON DELETE CASCADEonroom_id, soft-delete viarevoked_at, HMAC-onlysecret_hash, audit columns (created_by,created_at,last_used_at).chat/0049_messages_email_inbox_id.sql:ALTER TABLE messages ADD COLUMN email_inbox_id INTEGER REFERENCES email_inboxes(id) ON DELETE SET NULL. Parallel towebhook_id. The FK is in place becauseemail_inboxesexists at migration time, unlikewebhook_idwhich had to predateincoming_webhooksin the LC-74 ordering.Named decisions
uploader_id=""sentinel sticks.file_uploads.uploader_id(chat/0012_uploads.sql:3) isTEXT NOT NULLwith no FK constraint, so an empty-string sentinel works for the email-ingress attachment path the same waymessages.user_id=''already works for webhook posts. No schema change needed in this PR; commit 5 will calldb::uploads::insert_upload(..., uploader_id="", ...)directly.settings.smtp_passrow remains plaintext. The inconsistency is real and worth fixing; tracked as theLC-77-SMTP-SEALfollowup (own ticket, own PR) so scope here stays bounded.Type updates
models::Message+db::chat::RawMessage: newemail_inbox_id: Option<i64>field, parallel towebhook_id.db::chat: everySELECT FROM messagesprojection (5 sites) appendsemail_inbox_id;row_to_rawmapper appendsrow.get("email_inbox_id").routes/admin.rs,routes/room.rs(3 sites),routes/ws.rs: everyMessage {}struct literal adds the new field. Pass-through sites useraw.email_inbox_id; the webhook-finalize site (routes/room.rs:805) sets it toNone(a webhook post is never also an email-inbox post).db::email_inbox(new):identity(pool, id) -> Option<EmailInboxIdentity { name, avatar_url }>. Mirrorsdb::webhooks::identityfield-for-field; the render layer in commit 3 will drop it in alongside the webhook arm.Test drift cleanup
CLAUDE.md test-maintenance category 2 (migration-list drift) materialized exactly as predicted: 18 test files hand-roll the chat migration list (17 use array-form
include_str!blocks, 1 uses the verbose per-block form indb_private_rooms.rs), and 7 hand-roll the settings list (all array form). Appended the two new chat migrations and the one new settings migration to each via the file's existing pattern. The drift surfaced asroutes_uploads::send_message_with_attachment_renders_inline_imagereturning 500 fromPOST /room/1/messagesbecause sqlx hit the missingemail_inbox_idcolumn at one of the projections that selects it.Test plan
cargo check --workspaceandcargo check --no-default-features --features lets-chat-server/saasboth clean.lets-chat-servertest binaries pass under both default and saas feature sets.tests/db_email_inbox_schema.rs: 3 round-trip tests cover all three new schemas plus theCASCADE/SET NULLFK behaviors plus theCHECK (id = 1)singleton constraint.cargo clippy -p lets-chat-server --tests -- -D warningsclean.cargo fmt --checkclean for in-scope files. Pre-existingviews/math.rs+views/markdown.rsfmt diffs predate this branch (LC-59 carryover onmain); tracked asLC-77-FMT-CARRYOVERfollowup.Anti-scope
Next
Commit 3 builds on this branch's
mainonce merged:spawn_email_poll, address resolution (Delivered-To/X-Original-To/To/Ccprecedence), synthetic-actor send viadb::chat::insert_email_inbox_message, exhaustiveemail_ingress::droplog taxonomy, and theMessageActor::EmailInboxtemplate arm.Three migrations plus the supporting type updates so commit 3's IMAP poll loop has a place to land. No runtime behavior yet; the email-ingress feature is gated entirely by commit 3's spawn_email_poll. Migrations: - settings/0005_imap_inbox_config.sql: singleton encrypted-at-rest config row mirroring vapid_keypair (settings/0003). AES-256-GCM sealed password + nonce; plaintext host/port/tls/username/folder/ingress_domain/enabled. CHECK (id = 1) enforces the singleton. - chat/0048_email_inboxes.sql: per-room inboxes table. LC-74 webhooks (chat/0041) mirror: ON DELETE CASCADE on room_id, soft-delete via revoked_at, HMAC-only secret_hash, audit columns (created_by, created_at, last_used_at). - chat/0049_messages_email_inbox_id.sql: ALTER TABLE messages ADD COLUMN email_inbox_id INTEGER REFERENCES email_inboxes(id) ON DELETE SET NULL. Parallel to webhook_id; lets a synthetic email-actor row coexist with the existing webhook synthetic-actor shape. Uploads sentinel verification (CLAUDE.md commit-2 prerequisite): confirmed db::uploads::insert_upload(..., uploader_id="", ...) works for the email-ingress attachment path before commit 5 builds on it. file_uploads.uploader_id (chat/0012_uploads.sql:3) is TEXT NOT NULL with no FK constraint, so the empty-string sentinel pattern is fine; no schema change. LC-77 v1 attachments will use the same pattern messages.user_id='' already uses for webhook posts. SMTP-password-still-plaintext finding tracked separately (LC-77-SMTP-SEAL followup). Type updates: - models::Message + db::chat::RawMessage: new email_inbox_id: Option<i64> field, parallel to webhook_id. - db::chat: every SELECT messages projection (5 sites) appends email_inbox_id; row_to_raw mapper appends row.get("email_inbox_id"). - routes/admin.rs, routes/room.rs (3 sites), routes/ws.rs: every Message {} struct literal adds email_inbox_id (passes raw.email_inbox_id through, or None at the webhook-finalize site where the field is always None). - db::email_inbox (new): identity(pool, id) -> Option<EmailInboxIdentity { name, avatar_url }>, mirrors db::webhooks::identity for the commit 3 render layer. Test drift cleanup (CLAUDE.md test-maintenance category 2 / migration-list drift): 18 test files hand-roll the chat migration list (17 array form, 1 verbose per-block form in db_private_rooms.rs); 7 hand-roll the settings list (all array form). Appended the two new chat migrations and the one new settings migration to each via the file's existing pattern. The drift surfaced as routes_uploads::send_message_with_attachment_renders_inline_image returning 500 from POST /room/1/messages (sqlx hit the missing email_inbox_id column at the projection that selects it), exactly the symptom CLAUDE.md predicts. Verification: all 51 lets-chat-server test binaries pass under both default and saas feature sets. New tests/db_email_inbox_schema.rs: 3 round-trip tests covering all three new schemas plus the CASCADE / SET NULL FK behaviors and the singleton CHECK constraint. cargo clippy -p lets-chat-server --tests -- -D warnings clean. cargo fmt --check clean for in-scope files; pre-existing math.rs/markdown.rs LC-59 fmt carryover tracked as the LC-77-FMT-CARRYOVER followup. Co-Authored-By: Claude Opus 4.7 (1M context) <noreply@anthropic.com>