Skip to content

armgit / macro

Public Imported from GitHub
0

Macro is a unified workspace for teams: email, chat, docs, tasks, agents, calls, and CRM — @-linked together with shared AI memory.

9.1 KB
Raw Blame
-- Fixture for testing team-scoped (CRM) dedupe of conversation copies.
--
-- Team Beta has two members (Dave, Erin). Four conversations, identified by
-- the root message's RFC-822 Message-ID (email_messages.global_id):
--
--   • X — BOTH have a copy. Dave's copy also has a reply Erin wasn't on,
--     so Dave's thread ts (10:00) is newer than Erin's (08:00).
--   • Y — Erin only (09:00). Dave's queries must return Erin's copy.
--   • Z — Dave only (11:00).
--   • W — BOTH have a copy. Erin's copy has a reply Dave wasn't on, so
--     Erin's thread ts (12:00) is NEWER than Dave's (07:00). Own-copy
--     preference must still pick the caller's copy over the newer one.
--
-- Expected results for Sender(Domain("acme.com")) under team scope:
--   Dave: td2(Z,11:00), td1(X,10:00), te2(Y,09:00), td3(W,07:00)
--   Erin: te3(W,12:00), td2(Z,11:00), te2(Y,09:00), te1(X,08:00)

-- == macro_user / User rows ==
INSERT INTO "macro_user" (id, username, email, stripe_customer_id)
VALUES
    ('d1111111-1111-1111-1111-111111111111', '[email protected]', '[email protected]', 'stripe_dave'),
    ('e1111111-1111-1111-1111-111111111111', '[email protected]', '[email protected]', 'stripe_erin');

INSERT INTO "User" (id, email, name, macro_user_id)
VALUES
    ('macro|[email protected]', '[email protected]', 'Dave', 'd1111111-1111-1111-1111-111111111111'),
    ('macro|[email protected]', '[email protected]', 'Erin', 'e1111111-1111-1111-1111-111111111111');

-- == Team Beta + memberships ==
INSERT INTO team (id, name, owner_id, seat_count)
VALUES
    ('e0000002-0000-0000-0000-000000000002', 'Team Beta', 'macro|[email protected]', 2);

INSERT INTO team_user (team_id, user_id, team_role)
VALUES
    ('e0000002-0000-0000-0000-000000000002', 'macro|[email protected]', 'owner'),
    ('e0000002-0000-0000-0000-000000000002', 'macro|[email protected]', 'member');

INSERT INTO team_crm_settings (team_id, crm_enabled)
VALUES
    ('e0000002-0000-0000-0000-000000000002', TRUE);

-- == Email links ==
INSERT INTO email_links (id, macro_id, fusionauth_user_id, email_address, provider, is_sync_active, created_at, updated_at)
VALUES
    ('d0000001-0000-0000-0000-000000000001', 'macro|[email protected]', 'fa_dave', '[email protected]', 'GMAIL', true, NOW(), NOW()),
    ('d0000002-0000-0000-0000-000000000002', 'macro|[email protected]', 'fa_erin', '[email protected]', 'GMAIL', true, NOW(), NOW());

-- == Contacts — [email protected], one row per link ==
INSERT INTO email_contacts (id, link_id, email_address, name, created_at, updated_at)
VALUES
    ('c1000001-0000-0000-0000-000000000001', 'd0000001-0000-0000-0000-000000000001', '[email protected]', 'Vendor', NOW(), NOW()),
    ('c1000002-0000-0000-0000-000000000002', 'd0000002-0000-0000-0000-000000000002', '[email protected]', 'Vendor', NOW(), NOW());

-- == Labels — INBOX + TRASH per link ==
INSERT INTO email_labels (id, link_id, provider_label_id, name, message_list_visibility, label_list_visibility, type, created_at)
VALUES
    ('77770001-0000-0000-0000-000000000001', 'd0000001-0000-0000-0000-000000000001', 'INBOX', 'INBOX', 'Show', 'LabelShow', 'System', NOW()),
    ('77770001-0000-0000-0000-000000000002', 'd0000001-0000-0000-0000-000000000001', 'TRASH', 'TRASH', 'Hide', 'LabelHide', 'System', NOW()),
    ('77770002-0000-0000-0000-000000000001', 'd0000002-0000-0000-0000-000000000002', 'INBOX', 'INBOX', 'Show', 'LabelShow', 'System', NOW()),
    ('77770002-0000-0000-0000-000000000002', 'd0000002-0000-0000-0000-000000000002', 'TRASH', 'TRASH', 'Hide', 'LabelHide', 'System', NOW());

-- == Threads ==
-- td1: dave's copy of X    td2: dave's Z       td3: dave's copy of W
-- te1: erin's copy of X    te2: erin's Y       te3: erin's copy of W
INSERT INTO email_threads (
    id, provider_id, link_id, inbox_visible, is_read,
    latest_inbound_message_ts, latest_outbound_message_ts, latest_non_spam_message_ts,
    created_at, updated_at
)
VALUES
    ('55550001-0000-0000-0000-000000000001', 'td1', 'd0000001-0000-0000-0000-000000000001',
     true, false, '2026-05-01 10:00:00+00', NULL, '2026-05-01 10:00:00+00', NOW(), NOW()),
    ('55550001-0000-0000-0000-000000000002', 'td2', 'd0000001-0000-0000-0000-000000000001',
     true, false, '2026-05-01 11:00:00+00', NULL, '2026-05-01 11:00:00+00', NOW(), NOW()),
    ('55550001-0000-0000-0000-000000000003', 'td3', 'd0000001-0000-0000-0000-000000000001',
     true, false, '2026-05-01 07:00:00+00', NULL, '2026-05-01 07:00:00+00', NOW(), NOW()),
    ('55550002-0000-0000-0000-000000000001', 'te1', 'd0000002-0000-0000-0000-000000000002',
     true, false, '2026-05-01 08:00:00+00', NULL, '2026-05-01 08:00:00+00', NOW(), NOW()),
    ('55550002-0000-0000-0000-000000000002', 'te2', 'd0000002-0000-0000-0000-000000000002',
     true, false, '2026-05-01 09:00:00+00', NULL, '2026-05-01 09:00:00+00', NOW(), NOW()),
    ('55550002-0000-0000-0000-000000000003', 'te3', 'd0000002-0000-0000-0000-000000000002',
     true, false, '2026-05-01 12:00:00+00', NULL, '2026-05-01 12:00:00+00', NOW(), NOW());

-- == Messages ==
-- Copies of the same conversation share the root message's global_id.
INSERT INTO email_messages (
    id, thread_id, link_id, provider_id, from_contact_id,
    subject, snippet, internal_date_ts, global_id,
    is_draft, is_sent, is_starred, is_read,
    created_at, updated_at
)
VALUES
    -- X root — both copies
    ('66660001-0000-0000-0000-000000000001', '55550001-0000-0000-0000-000000000001',
     'd0000001-0000-0000-0000-000000000001', 'md1', 'c1000001-0000-0000-0000-000000000001',
     'Conv X', 'x root (dave copy)', '2026-05-01 08:00:00+00', '<[email protected]>',
     false, false, false, false, NOW(), NOW()),
    ('66660002-0000-0000-0000-000000000001', '55550002-0000-0000-0000-000000000001',
     'd0000002-0000-0000-0000-000000000002', 'me1', 'c1000002-0000-0000-0000-000000000002',
     'Conv X', 'x root (erin copy)', '2026-05-01 08:00:00+00', '<[email protected]>',
     false, false, false, false, NOW(), NOW()),
    -- X reply — dave only (erin was dropped)
    ('66660001-0000-0000-0000-000000000002', '55550001-0000-0000-0000-000000000001',
     'd0000001-0000-0000-0000-000000000001', 'md2', 'c1000001-0000-0000-0000-000000000001',
     'Conv X', 'x reply (dave only)', '2026-05-01 10:00:00+00', '<[email protected]>',
     false, false, false, false, NOW(), NOW()),
    -- Y root — erin only
    ('66660002-0000-0000-0000-000000000002', '55550002-0000-0000-0000-000000000002',
     'd0000002-0000-0000-0000-000000000002', 'me2', 'c1000002-0000-0000-0000-000000000002',
     'Conv Y', 'y root (erin only)', '2026-05-01 09:00:00+00', '<[email protected]>',
     false, false, false, false, NOW(), NOW()),
    -- Z root — dave only
    ('66660001-0000-0000-0000-000000000003', '55550001-0000-0000-0000-000000000002',
     'd0000001-0000-0000-0000-000000000001', 'md3', 'c1000001-0000-0000-0000-000000000001',
     'Conv Z', 'z root (dave only)', '2026-05-01 11:00:00+00', '<[email protected]>',
     false, false, false, false, NOW(), NOW()),
    -- W root — both copies
    ('66660001-0000-0000-0000-000000000004', '55550001-0000-0000-0000-000000000003',
     'd0000001-0000-0000-0000-000000000001', 'md4', 'c1000001-0000-0000-0000-000000000001',
     'Conv W', 'w root (dave copy)', '2026-05-01 07:00:00+00', '<[email protected]>',
     false, false, false, false, NOW(), NOW()),
    ('66660002-0000-0000-0000-000000000003', '55550002-0000-0000-0000-000000000003',
     'd0000002-0000-0000-0000-000000000002', 'me3', 'c1000002-0000-0000-0000-000000000002',
     'Conv W', 'w root (erin copy)', '2026-05-01 07:00:00+00', '<[email protected]>',
     false, false, false, false, NOW(), NOW()),
    -- W reply — erin only (dave was dropped); makes erin's copy NEWER
    ('66660002-0000-0000-0000-000000000004', '55550002-0000-0000-0000-000000000003',
     'd0000002-0000-0000-0000-000000000002', 'me4', 'c1000002-0000-0000-0000-000000000002',
     'Conv W', 'w reply (erin only)', '2026-05-01 12:00:00+00', '<[email protected]>',
     false, false, false, false, NOW(), NOW()),
    -- X draft on dave's copy — EARLIEST message in the thread WITH a
    -- global_id (provider-synced drafts carry a mailbox-local Message-ID).
    -- Root selection must skip it (is_draft = FALSE) or dave's dedupe key
    -- becomes the draft's local Message-ID and X stops deduping.
    ('66660001-0000-0000-0000-000000000005', '55550001-0000-0000-0000-000000000001',
     'd0000001-0000-0000-0000-000000000001', 'md5', NULL,
     'Conv X', 'x draft (dave only)', '2026-05-01 06:00:00+00', '<[email protected]>',
     true, false, false, false, NOW(), NOW());

-- == Message labels — everything in INBOX, nothing trashed ==
INSERT INTO email_message_labels (message_id, label_id)
VALUES
    ('66660001-0000-0000-0000-000000000001', '77770001-0000-0000-0000-000000000001'),
    ('66660001-0000-0000-0000-000000000002', '77770001-0000-0000-0000-000000000001'),
    ('66660001-0000-0000-0000-000000000003', '77770001-0000-0000-0000-000000000001'),
    ('66660001-0000-0000-0000-000000000004', '77770001-0000-0000-0000-000000000001'),
    ('66660002-0000-0000-0000-000000000001', '77770002-0000-0000-0000-000000000001'),
    ('66660002-0000-0000-0000-000000000002', '77770002-0000-0000-0000-000000000001'),
    ('66660002-0000-0000-0000-000000000003', '77770002-0000-0000-0000-000000000001'),
    ('66660002-0000-0000-0000-000000000004', '77770002-0000-0000-0000-000000000001');