Source identity
| Field | Recorded value |
|---|---|
| Source type | GitHub source |
| Original record | Janady13/missionmatch / db/migrations/0094_mail_sync.sql |
| Date | Not specified in source |
| Repository | Janady13/missionmatch |
| Revision | 720d47fae476b0d38477ae4401697d372b1ea2bc |
| Branch | feature/opportunity-to-outcome |
| Path or archive member | db/migrations/0094_mail_sync.sql |
| Original SHA-256 | 14346a731d481b50fef586b1ef148cfde28c328e116d2cbf81220083ed37bbb8 |
| Git blob SHA | b052223776cbcc7881a4d522723d313ee7d1c5e3 |
| Reading-copy SHA-256 | 14346a731d481b50fef586b1ef148cfde28c328e116d2cbf81220083ed37bbb8 |
Recovered source transcript
Download complete source text ↓104 lines · 6.5 KB
-- +goose Up
-- MAIL SYNC, so a recruiter's correspondence logs itself.
--
-- recruiter_activities has had the right vocabulary since migration 0049, call and email and meeting with
-- outcomes like connected and left_message, and every row in it had to be TYPED BY A HUMAN. That is the
-- single most reliable way to end up with an activity log nobody trusts: the people best at recruiting are
-- the least likely to stop and write down that they sent an email.
--
-- WHAT IS SYNCED IS HEADERS, NOT BODIES, and that is the load bearing decision in this whole feature.
--
-- The activity log needs to know WHO was written to, WHEN, and WHAT THE SUBJECT WAS. It does not need the
-- content. Pulling message bodies would drag a recruiter's entire mailbox, including correspondence with
-- their doctor and their lawyer and every colleague, into a platform holding controlled unclassified
-- information, where it would sit under the same retention rule as an employment decision record. The
-- value of the body is small and the liability is enormous, so the body is never fetched: the IMAP client
-- asks for a named header list and nothing else.
--
-- A MESSAGE THAT MATCHES NOBODY IS NOT STORED AT ALL. It is read, compared against the tenant's candidates
-- and contacts by address, and dropped. Only correspondence with somebody already in the system becomes a
-- row, which means a recruiter connecting their mailbox is not handing over their mailbox.
-- ============================================================================
-- 1. mail_accounts
-- ============================================================================
-- PER USER, not per organization. A mailbox belongs to a person. The organization's SMTP settings from
-- migration 0087 are how the company SENDS as itself; this is how one recruiter's own sent items get read
-- back so their work appears on the record without them typing it.
CREATE TABLE mail_accounts (
organization_id uuid NOT NULL REFERENCES organizations(id),
user_id uuid NOT NULL,
imap_host text NOT NULL,
imap_port int NOT NULL DEFAULT 993 CHECK (imap_port BETWEEN 1 AND 65535),
imap_username text NOT NULL,
-- The password lives in org_secrets, sealed, under a name derived from this user. It is never here.
-- security IS ALWAYS IMPLICIT TLS. There is no plaintext option and no STARTTLS negotiation, because
-- port 993 with TLS from the first byte is universally supported and a negotiated upgrade is one more
-- thing that can silently not happen.
-- Which folder to read. Sent items are the interesting half: a recruiter's outbound is the work.
mailbox text NOT NULL DEFAULT 'INBOX',
sync_sent boolean NOT NULL DEFAULT true,
enabled boolean NOT NULL DEFAULT false,
-- Set only by a successful connection, exactly like smtp_verified_at. Sync never runs against an
-- unproven configuration.
verified_at timestamptz,
last_sync_at timestamptz,
last_error text NOT NULL DEFAULT '',
-- How far back a sync looks. Bounded, because an unbounded first sync on a ten year mailbox is an
-- afternoon of IMAP traffic and a surprise for the mail provider.
lookback_days int NOT NULL DEFAULT 30 CHECK (lookback_days BETWEEN 1 AND 365),
created_at timestamptz NOT NULL DEFAULT now(),
updated_at timestamptz NOT NULL DEFAULT now(),
PRIMARY KEY (organization_id, user_id),
CONSTRAINT fk_mail_accounts_user FOREIGN KEY (organization_id, user_id)
REFERENCES users (organization_id, id) ON DELETE CASCADE,
CONSTRAINT ck_mail_account_enabled CHECK (enabled = false OR verified_at IS NOT NULL)
);
ALTER TABLE mail_accounts ENABLE ROW LEVEL SECURITY;
ALTER TABLE mail_accounts FORCE ROW LEVEL SECURITY;
CREATE POLICY mail_accounts_isolation ON mail_accounts
USING (organization_id = NULLIF(current_setting('app.current_org_id', true), '')::uuid)
WITH CHECK (organization_id = NULLIF(current_setting('app.current_org_id', true), '')::uuid);
-- DELETE is granted: disconnecting a mailbox removes the connection, and leaving a revoked credential's
-- configuration behind serves nobody. The activities already logged are unaffected.
GRANT SELECT, INSERT, UPDATE, DELETE ON mail_accounts TO missionmatch_app;
-- ============================================================================
-- 2. synced_messages, the dedup ledger
-- ============================================================================
-- One row per message per organization, keyed by the RFC 5322 Message-ID. Without this a sync that overlaps
-- its own window, which every sync does because windows are inclusive and clocks differ, would log the same
-- email as a new activity every time it ran.
--
-- IT HOLDS NO CONTENT. A message identifier, who it matched, and when. The subject and the participants live
-- on the recruiter_activities row this produced, which is the record a human actually reads.
CREATE TABLE synced_messages (
organization_id uuid NOT NULL REFERENCES organizations(id),
message_id text NOT NULL,
user_id uuid NOT NULL,
direction text NOT NULL CHECK (direction IN ('inbound', 'outbound')),
matched_kind text CHECK (matched_kind IN ('candidate', 'contact')),
matched_id uuid,
activity_id bigint,
synced_at timestamptz NOT NULL DEFAULT now(),
PRIMARY KEY (organization_id, message_id),
CONSTRAINT fk_synced_user FOREIGN KEY (organization_id, user_id)
REFERENCES users (organization_id, id) ON DELETE CASCADE,
CONSTRAINT ck_synced_message_id CHECK (length(message_id) BETWEEN 1 AND 400)
);
CREATE INDEX idx_synced_messages_user ON synced_messages (organization_id, user_id, synced_at DESC);
ALTER TABLE synced_messages ENABLE ROW LEVEL SECURITY;
ALTER TABLE synced_messages FORCE ROW LEVEL SECURITY;
CREATE POLICY synced_messages_isolation ON synced_messages
USING (organization_id = NULLIF(current_setting('app.current_org_id', true), '')::uuid)
WITH CHECK (organization_id = NULLIF(current_setting('app.current_org_id', true), '')::uuid);
-- DELETE is granted so disconnecting a mailbox can forget what it had seen, which is what lets a recruiter
-- reconnect and re sync cleanly rather than being permanently blocked by a ledger they cannot clear.
GRANT SELECT, INSERT, UPDATE, DELETE ON synced_messages TO missionmatch_app;
SELECT mm_mirror_staging_role_privileges();
-- +goose Down
DROP TABLE IF EXISTS synced_messages;
DROP TABLE IF EXISTS mail_accounts;