Macro is a unified workspace for teams: email, chat, docs, tasks, agents, calls, and CRM — @-linked together with shared AI memory.
TypeScript agentaiai-agentsall-in-onecrmcrm-systememaillinearmcpmessagingnotesnotionofficerustslackslack-alternativestartupstartup-toolstypescriptworkspace
-- Fixture for testing dynamic email query builder
-- Sets up threads, messages, contacts, recipients, labels for comprehensive testing
-- Insert test user for project ownership
INSERT INTO "macro_user" (id, username, email, stripe_customer_id)
VALUES ('a1111111-1111-1111-1111-111111111111', '[email protected]', '[email protected]', 'stripe_user1');
INSERT INTO "User" (id, email, name, macro_user_id)
VALUES ('macro|[email protected]', '[email protected]', 'Test User 1', 'a1111111-1111-1111-1111-111111111111');
-- Insert test projects
INSERT INTO "Project" (id, name, "userId", "createdAt", "updatedAt")
VALUES
('proj-aaaa-aaaa-aaaa-aaaaaaaaaaaa', 'Project Alpha', 'macro|[email protected]', NOW(), NOW()),
('proj-bbbb-bbbb-bbbb-bbbbbbbbbbbb', 'Project Beta', 'macro|[email protected]', NOW(), NOW());
-- Insert test link
INSERT INTO email_links (id, macro_id, fusionauth_user_id, email_address, provider, is_sync_active, created_at, updated_at)
VALUES
('aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa', 'macro|[email protected]', 'user1', '[email protected]', 'GMAIL', true, NOW(), NOW());
-- Insert test contacts
INSERT INTO email_contacts (id, link_id, email_address, name, created_at, updated_at)
VALUES
('40000001-0000-0000-0000-000000000001', 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa', '[email protected]', 'John Doe', NOW(), NOW()),
('40000002-0000-0000-0000-000000000002', 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa', '[email protected]', 'Jane Smith', NOW(), NOW()),
('40000003-0000-0000-0000-000000000003', 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa', '[email protected]', 'Bob Johnson', NOW(), NOW()),
('40000004-0000-0000-0000-000000000004', 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa', '[email protected]', 'Alice Williams', NOW(), NOW());
-- Insert test labels
INSERT INTO email_labels (id, link_id, provider_label_id, name, message_list_visibility, label_list_visibility, type, created_at)
VALUES
('10000001-0000-0000-0000-000000000001', 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa', 'INBOX', 'INBOX', 'Show', 'LabelShow', 'System', NOW()),
('10000002-0000-0000-0000-000000000002', 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa', 'IMPORTANT', 'IMPORTANT', 'Show', 'LabelShow', 'System', NOW()),
('10000003-0000-0000-0000-000000000003', 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa', 'SENT', 'SENT', 'Show', 'LabelShow', 'System', NOW()),
('10000004-0000-0000-0000-000000000004', 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa', 'STARRED', 'STARRED', 'Show', 'LabelShow', 'System', NOW()),
('10000005-0000-0000-0000-000000000005', 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa', 'DRAFT', 'DRAFT', 'Show', 'LabelShow', 'System', NOW()),
('10000006-0000-0000-0000-000000000006', 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa', 'TRASH', 'TRASH', 'Hide', 'LabelHide', 'System', NOW()),
('10000007-0000-0000-0000-000000000007', 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa', 'Label_Work', 'Work', 'Show', 'LabelShow', 'User', NOW()),
-- Category labels for importance testing
('10000008-0000-0000-0000-000000000008', 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa', 'CATEGORY_PERSONAL', 'CATEGORY_PERSONAL', 'Show', 'LabelShow', 'System', NOW()),
('10000009-0000-0000-0000-000000000009', 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa', 'CATEGORY_UPDATES', 'CATEGORY_UPDATES', 'Show', 'LabelShow', 'System', NOW()),
('10000010-0000-0000-0000-000000000010', 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa', 'CATEGORY_PROMOTIONS', 'CATEGORY_PROMOTIONS', 'Show', 'LabelShow', 'System', NOW());
-- Insert test threads
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
-- Thread 1: Inbox, from [email protected], in Project Alpha
('20000001-0000-0000-0000-000000000001', 'thread1', 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa',
true, false, '2024-01-15 10:00:00+00', NULL, '2024-01-15 10:00:00+00', NOW(), NOW()),
-- Thread 2: Sent, to [email protected], in Project Alpha
('20000002-0000-0000-0000-000000000002', 'thread2', 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa',
false, true, NULL, '2024-01-14 09:00:00+00', '2024-01-14 09:00:00+00', NOW(), NOW()),
-- Thread 3: Draft from [email protected] (no project)
('20000003-0000-0000-0000-000000000003', 'thread3', 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa',
false, false, NULL, NULL, '2024-01-13 08:00:00+00', NOW(), NOW()),
-- Thread 4: Starred, from [email protected] (no project)
('20000004-0000-0000-0000-000000000004', 'thread4', 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa',
true, false, '2024-01-12 07:00:00+00', NULL, '2024-01-12 07:00:00+00', NOW(), NOW()),
-- Thread 5: Important inbox from [email protected], in Project Alpha
('20000005-0000-0000-0000-000000000005', 'thread5', 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa',
true, false, '2024-01-11 06:00:00+00', NULL, '2024-01-11 06:00:00+00', NOW(), NOW()),
-- Thread 6: User label "Work", from [email protected], in Project Beta
('20000006-0000-0000-0000-000000000006', 'thread6', 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa',
false, false, NULL, NULL, '2024-01-10 05:00:00+00', NOW(), NOW()),
-- Thread 7: Inbox with CC to [email protected]
('20000007-0000-0000-0000-000000000007', 'thread7', 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa',
true, false, '2024-01-09 04:00:00+00', NULL, '2024-01-09 04:00:00+00', NOW(), NOW()),
-- Thread 8: Draft with depriority label (CATEGORY_UPDATES) - tests that DRAFT priority overrides depriority
('20000008-0000-0000-0000-000000000008', 'thread8', 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa',
false, false, NULL, NULL, '2024-01-08 03:00:00+00', NOW(), NOW()),
-- Thread 9: Draft in TRASH - tests that importance=true excludes trashed drafts
('20000009-0000-0000-0000-000000000009', 'thread9', 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa',
false, false, NULL, NULL, '2024-01-07 02:00:00+00', NOW(), NOW()),
-- Thread 10: Starred message in TRASH - tests that starred view excludes trashed messages
('20000010-0000-0000-0000-000000000010', 'thread10', 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa',
false, false, '2024-01-06 01:00:00+00', NULL, '2024-01-06 01:00:00+00', NOW(), NOW()),
-- Thread 11: User label "Work" message in TRASH - tests that user label view excludes trashed messages
('20000011-0000-0000-0000-000000000011', 'thread11', 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa',
false, false, NULL, NULL, '2024-01-05 00:00:00+00', NOW(), NOW());
-- Assign threads to projects
UPDATE email_threads SET project_id = 'proj-aaaa-aaaa-aaaa-aaaaaaaaaaaa' WHERE id IN (
'20000001-0000-0000-0000-000000000001',
'20000002-0000-0000-0000-000000000002',
'20000005-0000-0000-0000-000000000005'
);
UPDATE email_threads SET project_id = 'proj-bbbb-bbbb-bbbb-bbbbbbbbbbbb' WHERE id = '20000006-0000-0000-0000-000000000006';
-- Insert test messages
INSERT INTO email_messages (
id, thread_id, link_id, provider_id, from_contact_id,
subject, snippet, internal_date_ts,
is_draft, is_sent, is_starred, is_read,
created_at, updated_at
)
VALUES
-- Message 1: Inbox from [email protected]
('30000001-0000-0000-0000-000000000001', '20000001-0000-0000-0000-000000000001',
'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa', 'msg1', '40000001-0000-0000-0000-000000000001',
'Meeting Tomorrow', 'Lets meet tomorrow at 10am', '2024-01-15 10:00:00+00',
false, false, false, false, NOW(), NOW()),
-- Message 2: Sent to [email protected]
('30000002-0000-0000-0000-000000000002', '20000002-0000-0000-0000-000000000002',
'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa', 'msg2', '40000001-0000-0000-0000-000000000001',
'Project Update', 'Here is the update', '2024-01-14 09:00:00+00',
false, true, false, true, NOW(), NOW()),
-- Message 3: Draft from [email protected]
('30000003-0000-0000-0000-000000000003', '20000003-0000-0000-0000-000000000003',
'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa', 'msg3', '40000003-0000-0000-0000-000000000003',
'Draft Email', 'This is a draft', '2024-01-13 08:00:00+00',
true, false, false, false, NOW(), NOW()),
-- Message 4: Starred from [email protected]
('30000004-0000-0000-0000-000000000004', '20000004-0000-0000-0000-000000000004',
'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa', 'msg4', '40000004-0000-0000-0000-000000000004',
'Important Info', 'Please review', '2024-01-12 07:00:00+00',
false, false, true, false, NOW(), NOW()),
-- Message 5: Important inbox from [email protected]
('30000005-0000-0000-0000-000000000005', '20000005-0000-0000-0000-000000000005',
'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa', 'msg5', '40000001-0000-0000-0000-000000000001',
'Urgent: Action Required', 'Please respond ASAP', '2024-01-11 06:00:00+00',
false, false, false, false, NOW(), NOW()),
-- Message 6: User label "Work" from [email protected]
('30000006-0000-0000-0000-000000000006', '20000006-0000-0000-0000-000000000006',
'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa', 'msg6', '40000002-0000-0000-0000-000000000002',
'Work Project', 'Quarterly review', '2024-01-10 05:00:00+00',
false, false, false, false, NOW(), NOW()),
-- Message 7: Inbox with CC to [email protected]
('30000007-0000-0000-0000-000000000007', '20000007-0000-0000-0000-000000000007',
'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa', 'msg7', '40000002-0000-0000-0000-000000000002',
'Team Update', 'FYI for everyone', '2024-01-09 04:00:00+00',
false, false, false, false, NOW(), NOW()),
-- Message 8: Draft with depriority label from [email protected]
('30000008-0000-0000-0000-000000000008', '20000008-0000-0000-0000-000000000008',
'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa', 'msg8', '40000003-0000-0000-0000-000000000003',
'Draft With Updates Label', 'Draft that also has a depriority label', '2024-01-08 03:00:00+00',
true, false, false, false, NOW(), NOW()),
-- Message 9: Draft in TRASH from [email protected]
('30000009-0000-0000-0000-000000000009', '20000009-0000-0000-0000-000000000009',
'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa', 'msg9', '40000004-0000-0000-0000-000000000004',
'Trashed Draft', 'A draft that was moved to trash', '2024-01-07 02:00:00+00',
true, false, false, false, NOW(), NOW()),
-- Message 10: Starred message in TRASH from [email protected]
('30000010-0000-0000-0000-000000000010', '20000010-0000-0000-0000-000000000010',
'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa', 'msg10', '40000001-0000-0000-0000-000000000001',
'Trashed Starred', 'A starred message that was moved to trash', '2024-01-06 01:00:00+00',
false, false, true, false, NOW(), NOW()),
-- Message 11: User label "Work" message in TRASH from [email protected]
('30000011-0000-0000-0000-000000000011', '20000011-0000-0000-0000-000000000011',
'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa', 'msg11', '40000002-0000-0000-0000-000000000002',
'Trashed Work Email', 'A work email that was moved to trash', '2024-01-05 00:00:00+00',
false, false, false, false, NOW(), NOW());
-- Insert message recipients
INSERT INTO email_message_recipients (message_id, contact_id, recipient_type)
VALUES
-- Message 1: To [email protected]
('30000001-0000-0000-0000-000000000001', '40000004-0000-0000-0000-000000000004', 'TO'),
-- Message 2: To [email protected]
('30000002-0000-0000-0000-000000000002', '40000002-0000-0000-0000-000000000002', 'TO'),
-- Message 3: To [email protected]
('30000003-0000-0000-0000-000000000003', '40000004-0000-0000-0000-000000000004', 'TO'),
-- Message 4: To [email protected]
('30000004-0000-0000-0000-000000000004', '40000001-0000-0000-0000-000000000001', 'TO'),
-- Message 5: To [email protected]
('30000005-0000-0000-0000-000000000005', '40000004-0000-0000-0000-000000000004', 'TO'),
-- Message 6: To [email protected]
('30000006-0000-0000-0000-000000000006', '40000001-0000-0000-0000-000000000001', 'TO'),
-- Message 7: To [email protected], CC [email protected]
('30000007-0000-0000-0000-000000000007', '40000004-0000-0000-0000-000000000004', 'TO'),
('30000007-0000-0000-0000-000000000007', '40000003-0000-0000-0000-000000000003', 'CC'),
-- Message 8: To [email protected]
('30000008-0000-0000-0000-000000000008', '40000004-0000-0000-0000-000000000004', 'TO'),
-- Message 9: To [email protected]
('30000009-0000-0000-0000-000000000009', '40000001-0000-0000-0000-000000000001', 'TO'),
-- Message 10: To [email protected]
('30000010-0000-0000-0000-000000000010', '40000004-0000-0000-0000-000000000004', 'TO'),
-- Message 11: To [email protected]
('30000011-0000-0000-0000-000000000011', '40000003-0000-0000-0000-000000000003', 'TO');
-- Insert message-label relationships
INSERT INTO email_message_labels (message_id, label_id)
VALUES
-- Message 1: INBOX
('30000001-0000-0000-0000-000000000001', '10000001-0000-0000-0000-000000000001'),
-- Message 2: SENT
('30000002-0000-0000-0000-000000000002', '10000003-0000-0000-0000-000000000003'),
-- Message 3: DRAFT
('30000003-0000-0000-0000-000000000003', '10000005-0000-0000-0000-000000000005'),
-- Message 4: STARRED, INBOX
('30000004-0000-0000-0000-000000000004', '10000004-0000-0000-0000-000000000004'),
('30000004-0000-0000-0000-000000000004', '10000001-0000-0000-0000-000000000001'),
-- Message 5: IMPORTANT, INBOX
('30000005-0000-0000-0000-000000000005', '10000002-0000-0000-0000-000000000002'),
('30000005-0000-0000-0000-000000000005', '10000001-0000-0000-0000-000000000001'),
-- Message 6: Work (user label)
('30000006-0000-0000-0000-000000000006', '10000007-0000-0000-0000-000000000007'),
-- Message 7: INBOX
('30000007-0000-0000-0000-000000000007', '10000001-0000-0000-0000-000000000001'),
-- Importance labels:
-- Message 1 (thread 1): CATEGORY_PERSONAL (priority label → important)
('30000001-0000-0000-0000-000000000001', '10000008-0000-0000-0000-000000000008'),
-- Message 6 (thread 6): CATEGORY_UPDATES (depriority label, no priority → NOT important)
('30000006-0000-0000-0000-000000000006', '10000009-0000-0000-0000-000000000009'),
-- Message 7 (thread 7): CATEGORY_PROMOTIONS (depriority label, no priority → NOT important)
('30000007-0000-0000-0000-000000000007', '10000010-0000-0000-0000-000000000010'),
-- Message 8 (thread 8): DRAFT + CATEGORY_UPDATES (draft with depriority label → important because DRAFT is priority)
('30000008-0000-0000-0000-000000000008', '10000005-0000-0000-0000-000000000005'),
('30000008-0000-0000-0000-000000000008', '10000009-0000-0000-0000-000000000009'),
-- Message 9 (thread 9): DRAFT + TRASH (trashed draft → excluded from importance=true)
('30000009-0000-0000-0000-000000000009', '10000005-0000-0000-0000-000000000005'),
('30000009-0000-0000-0000-000000000009', '10000006-0000-0000-0000-000000000006'),
-- Message 10 (thread 10): STARRED + TRASH (trashed starred → excluded from starred view)
('30000010-0000-0000-0000-000000000010', '10000004-0000-0000-0000-000000000004'),
('30000010-0000-0000-0000-000000000010', '10000006-0000-0000-0000-000000000006'),
-- Message 11 (thread 11): Work + TRASH (trashed user label → excluded from user label view)
('30000011-0000-0000-0000-000000000011', '10000007-0000-0000-0000-000000000007'),
('30000011-0000-0000-0000-000000000011', '10000006-0000-0000-0000-000000000006');