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 CRM-scoped dynamic queries.
--
-- Sets up:
-- • Team Alpha with two members (Alice, Bob).
-- • Carol — a non-member with her own link/threads (must never appear in
-- team-scoped results).
-- • Each link has its OWN row in email_contacts for the same external
-- address "[email protected]" — different UUIDs per link. This is what
-- exercises the new multi-id `from_contact_id = ANY($ids)` predicate.
-- • Each link has a TRASH label with a different UUID. Some threads are
-- trashed in each link to exercise the multi-id trash exclusion.
-- • Threads on alice/bob's links from "[email protected]" (acme.com
-- domain) plus a thread on alice's link from a different sender,
-- plus a thread on carol's link that must be excluded.
-- == macro_user / User rows ==
INSERT INTO "macro_user" (id, username, email, stripe_customer_id)
VALUES
('a1111111-1111-1111-1111-111111111111', '[email protected]', '[email protected]', 'stripe_alice'),
('b1111111-1111-1111-1111-111111111111', '[email protected]', '[email protected]', 'stripe_bob'),
('c1111111-1111-1111-1111-111111111111', '[email protected]', '[email protected]', 'stripe_carol');
INSERT INTO "User" (id, email, name, macro_user_id)
VALUES
('macro|[email protected]', '[email protected]', 'Alice', 'a1111111-1111-1111-1111-111111111111'),
('macro|[email protected]', '[email protected]', 'Bob', 'b1111111-1111-1111-1111-111111111111'),
('macro|[email protected]', '[email protected]', 'Carol', 'c1111111-1111-1111-1111-111111111111');
-- == Team Alpha + memberships ==
INSERT INTO team (id, name, owner_id, seat_count)
VALUES
('e0000001-0000-0000-0000-000000000001', 'Team Alpha', 'macro|[email protected]', 2);
INSERT INTO team_user (team_id, user_id, team_role)
VALUES
('e0000001-0000-0000-0000-000000000001', 'macro|[email protected]', 'owner'),
('e0000001-0000-0000-0000-000000000001', 'macro|[email protected]', 'member');
-- Carol is intentionally NOT a member.
-- Team-level CRM killswitch ON. The candidate-source SQL joins
-- team_crm_settings (crm_enabled = TRUE), so this row must be present
-- for any CRM-scoped query to return rows. Tests that exercise the
-- killswitch-off behavior flip this in their own fixture script.
INSERT INTO team_crm_settings (team_id, crm_enabled)
VALUES
('e0000001-0000-0000-0000-000000000001', TRUE);
-- == Email links (one per user) ==
INSERT INTO email_links (id, macro_id, fusionauth_user_id, email_address, provider, is_sync_active, created_at, updated_at)
VALUES
('a0000001-0000-0000-0000-000000000001', 'macro|[email protected]', 'fa_alice', '[email protected]', 'GMAIL', true, NOW(), NOW()),
('a0000002-0000-0000-0000-000000000002', 'macro|[email protected]', 'fa_bob', '[email protected]', 'GMAIL', true, NOW(), NOW()),
('a0000003-0000-0000-0000-000000000003', 'macro|[email protected]', 'fa_carol', '[email protected]', 'GMAIL', true, NOW(), NOW());
-- == Contacts ==
-- Each link has its own row for the SAME external address. This is the
-- multi-id case the new resolution path is designed to handle.
INSERT INTO email_contacts (id, link_id, email_address, name, created_at, updated_at)
VALUES
-- [email protected] — one contact row per link, different UUIDs
('c0000001-0000-0000-0000-000000000001', 'a0000001-0000-0000-0000-000000000001', '[email protected]', 'Outsider', NOW(), NOW()),
('c0000002-0000-0000-0000-000000000002', 'a0000002-0000-0000-0000-000000000002', '[email protected]', 'Outsider', NOW(), NOW()),
('c0000003-0000-0000-0000-000000000003', 'a0000003-0000-0000-0000-000000000003', '[email protected]', 'Outsider', NOW(), NOW()),
-- a different sender on alice's link, for non-matching-filter assertions
('c0000004-0000-0000-0000-000000000004', 'a0000001-0000-0000-0000-000000000001', '[email protected]', 'Other', NOW(), NOW()),
-- TO/CC/BCC recipient contacts — mirrored on both alice and bob's
-- links (same address, different per-link UUIDs). Exercise multi-id
-- ANY predicates on the recipient EXISTS subquery.
('c0000005-0000-0000-0000-000000000005', 'a0000001-0000-0000-0000-000000000001', '[email protected]', 'To Target', NOW(), NOW()),
('c0000006-0000-0000-0000-000000000006', 'a0000001-0000-0000-0000-000000000001', '[email protected]', 'Cc Target', NOW(), NOW()),
('c0000007-0000-0000-0000-000000000007', 'a0000001-0000-0000-0000-000000000001', '[email protected]', 'Bcc Target', NOW(), NOW()),
('c0000008-0000-0000-0000-000000000008', 'a0000002-0000-0000-0000-000000000002', '[email protected]', 'To Target', NOW(), NOW()),
('c0000009-0000-0000-0000-000000000009', 'a0000002-0000-0000-0000-000000000002', '[email protected]', 'Cc Target', NOW(), NOW()),
('c0000010-0000-0000-0000-000000000010', 'a0000002-0000-0000-0000-000000000002', '[email protected]', 'Bcc Target', NOW(), NOW()),
-- Asymmetric: only Bob has a contact row for this address.
-- `Sender(Complete("[email protected]"))` under team_scope should
-- resolve to exactly one contact id and match only bob's thread.
('c0000011-0000-0000-0000-000000000011', 'a0000002-0000-0000-0000-000000000002', '[email protected]', 'BobOnly', 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
-- Alice's labels
('11110001-0000-0000-0000-000000000001', 'a0000001-0000-0000-0000-000000000001', 'INBOX', 'INBOX', 'Show', 'LabelShow', 'System', NOW()),
('11110001-0000-0000-0000-000000000002', 'a0000001-0000-0000-0000-000000000001', 'TRASH', 'TRASH', 'Hide', 'LabelHide', 'System', NOW()),
-- Bob's labels
('11110002-0000-0000-0000-000000000001', 'a0000002-0000-0000-0000-000000000002', 'INBOX', 'INBOX', 'Show', 'LabelShow', 'System', NOW()),
('11110002-0000-0000-0000-000000000002', 'a0000002-0000-0000-0000-000000000002', 'TRASH', 'TRASH', 'Hide', 'LabelHide', 'System', NOW()),
-- Carol's labels
('11110003-0000-0000-0000-000000000001', 'a0000003-0000-0000-0000-000000000003', 'INBOX', 'INBOX', 'Show', 'LabelShow', 'System', NOW()),
('11110003-0000-0000-0000-000000000002', 'a0000003-0000-0000-0000-000000000003', 'TRASH', 'TRASH', 'Hide', 'LabelHide', 'System', NOW());
-- == Threads ==
-- ta1: alice's inbox thread from [email protected] (will appear in team queries)
-- ta2: alice's TRASHED thread from [email protected] (must NOT appear)
-- ta3: alice's inbox thread from [email protected] (no acme match)
-- tb1: bob's inbox thread from [email protected] (will appear in team queries)
-- tb2: bob's TRASHED thread from [email protected] (must NOT appear)
-- tc1: carol's inbox thread from [email protected] (must NOT appear — not on team)
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
('22220001-0000-0000-0000-000000000001', 'ta1', 'a0000001-0000-0000-0000-000000000001',
true, false, '2026-04-01 10:00:00+00', NULL, '2026-04-01 10:00:00+00', NOW(), NOW()),
('22220001-0000-0000-0000-000000000002', 'ta2', 'a0000001-0000-0000-0000-000000000001',
true, false, '2026-04-02 10:00:00+00', NULL, '2026-04-02 10:00:00+00', NOW(), NOW()),
('22220001-0000-0000-0000-000000000003', 'ta3', 'a0000001-0000-0000-0000-000000000001',
true, false, '2026-04-03 10:00:00+00', NULL, '2026-04-03 10:00:00+00', NOW(), NOW()),
('22220002-0000-0000-0000-000000000001', 'tb1', 'a0000002-0000-0000-0000-000000000002',
true, false, '2026-04-04 10:00:00+00', NULL, '2026-04-04 10:00:00+00', NOW(), NOW()),
('22220002-0000-0000-0000-000000000002', 'tb2', 'a0000002-0000-0000-0000-000000000002',
true, false, '2026-04-05 10:00:00+00', NULL, '2026-04-05 10:00:00+00', NOW(), NOW()),
('22220003-0000-0000-0000-000000000001', 'tc1', 'a0000003-0000-0000-0000-000000000003',
true, false, '2026-04-06 10:00:00+00', NULL, '2026-04-06 10:00:00+00', NOW(), NOW()),
-- tb3: bob's inbox thread from [email protected] (asymmetric test).
('22220002-0000-0000-0000-000000000003', 'tb3', 'a0000002-0000-0000-0000-000000000002',
true, false, '2026-04-07 10:00:00+00', NULL, '2026-04-07 10:00:00+00', NOW(), NOW());
-- == 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
-- ta1 — alice's inbox, from [email protected] (alice's contact row)
('33330001-0000-0000-0000-000000000001', '22220001-0000-0000-0000-000000000001',
'a0000001-0000-0000-0000-000000000001', 'ma1', 'c0000001-0000-0000-0000-000000000001',
'Alice inbox acme', 'alice from acme', '2026-04-01 10:00:00+00',
false, false, false, false, NOW(), NOW()),
-- ta2 — alice's TRASHED, from [email protected] (alice's contact row)
('33330001-0000-0000-0000-000000000002', '22220001-0000-0000-0000-000000000002',
'a0000001-0000-0000-0000-000000000001', 'ma2', 'c0000001-0000-0000-0000-000000000001',
'Alice trash acme', 'alice trashed acme', '2026-04-02 10:00:00+00',
false, false, false, false, NOW(), NOW()),
-- ta3 — alice's inbox, from [email protected] (not an acme address)
('33330001-0000-0000-0000-000000000003', '22220001-0000-0000-0000-000000000003',
'a0000001-0000-0000-0000-000000000001', 'ma3', 'c0000004-0000-0000-0000-000000000004',
'Alice inbox other', 'alice from elsewhere', '2026-04-03 10:00:00+00',
false, false, false, false, NOW(), NOW()),
-- tb1 — bob's inbox, from [email protected] (bob's contact row)
('33330002-0000-0000-0000-000000000001', '22220002-0000-0000-0000-000000000001',
'a0000002-0000-0000-0000-000000000002', 'mb1', 'c0000002-0000-0000-0000-000000000002',
'Bob inbox acme', 'bob from acme', '2026-04-04 10:00:00+00',
false, false, false, false, NOW(), NOW()),
-- tb2 — bob's TRASHED, from [email protected] (bob's contact row)
('33330002-0000-0000-0000-000000000002', '22220002-0000-0000-0000-000000000002',
'a0000002-0000-0000-0000-000000000002', 'mb2', 'c0000002-0000-0000-0000-000000000002',
'Bob trash acme', 'bob trashed acme', '2026-04-05 10:00:00+00',
false, false, false, false, NOW(), NOW()),
-- tc1 — carol's inbox, from [email protected] (carol's contact row)
('33330003-0000-0000-0000-000000000001', '22220003-0000-0000-0000-000000000001',
'a0000003-0000-0000-0000-000000000003', 'mc1', 'c0000003-0000-0000-0000-000000000003',
'Carol inbox acme', 'carol from acme', '2026-04-06 10:00:00+00',
false, false, false, false, NOW(), NOW()),
-- mb3 — bob's inbox thread tb3, from [email protected] (only on bob's link)
('33330002-0000-0000-0000-000000000003', '22220002-0000-0000-0000-000000000003',
'a0000002-0000-0000-0000-000000000002', 'mb3', 'c0000011-0000-0000-0000-000000000011',
'Bob inbox bob-only', 'bob from bob-only', '2026-04-07 10:00:00+00',
false, false, false, false, NOW(), NOW());
-- == Recipients ==
-- ta1 and tb1 both have the same TO/CC/BCC addresses, but each link has
-- its own contact row for those addresses (different UUIDs). The recipient
-- ANY-predicate must match both via multi-id resolution.
INSERT INTO email_message_recipients (message_id, contact_id, recipient_type)
VALUES
-- ta1 recipients (alice's link)
('33330001-0000-0000-0000-000000000001', 'c0000005-0000-0000-0000-000000000005', 'TO'),
('33330001-0000-0000-0000-000000000001', 'c0000006-0000-0000-0000-000000000006', 'CC'),
('33330001-0000-0000-0000-000000000001', 'c0000007-0000-0000-0000-000000000007', 'BCC'),
-- tb1 recipients (bob's link)
('33330002-0000-0000-0000-000000000001', 'c0000008-0000-0000-0000-000000000008', 'TO'),
('33330002-0000-0000-0000-000000000001', 'c0000009-0000-0000-0000-000000000009', 'CC'),
('33330002-0000-0000-0000-000000000001', 'c0000010-0000-0000-0000-000000000010', 'BCC');
-- == Message labels ==
-- Inbox label on the inbox threads (per-link INBOX label uuids)
-- TRASH label on the trashed threads (per-link TRASH label uuids)
INSERT INTO email_message_labels (message_id, label_id)
VALUES
-- Alice INBOX
('33330001-0000-0000-0000-000000000001', '11110001-0000-0000-0000-000000000001'),
('33330001-0000-0000-0000-000000000003', '11110001-0000-0000-0000-000000000001'),
-- Alice TRASH (ta2)
('33330001-0000-0000-0000-000000000002', '11110001-0000-0000-0000-000000000002'),
-- Bob INBOX
('33330002-0000-0000-0000-000000000001', '11110002-0000-0000-0000-000000000001'),
-- Bob INBOX (tb3, from [email protected])
('33330002-0000-0000-0000-000000000003', '11110002-0000-0000-0000-000000000001'),
-- Bob TRASH (tb2)
('33330002-0000-0000-0000-000000000002', '11110002-0000-0000-0000-000000000002'),
-- Carol INBOX
('33330003-0000-0000-0000-000000000001', '11110003-0000-0000-0000-000000000001');
-- == Alice's connected secondary (non-primary) link ==
-- Same macro_id as alice's primary link but a different email_address — a
-- personal mailbox connected as an extra inbox. Team-scoped queries must
-- never read it: its threads, contacts, and labels stay out of CRM scope.
INSERT INTO email_links (id, macro_id, fusionauth_user_id, email_address, provider, is_sync_active, created_at, updated_at)
VALUES
('a0000004-0000-0000-0000-000000000004', 'macro|[email protected]', 'fa_alice_personal', '[email protected]', 'GMAIL', true, NOW(), NOW());
INSERT INTO email_contacts (id, link_id, email_address, name, created_at, updated_at)
VALUES
-- [email protected] also corresponded with alice's personal mailbox
('c0000012-0000-0000-0000-000000000012', 'a0000004-0000-0000-0000-000000000004', '[email protected]', 'Outsider', NOW(), NOW()),
-- an address known ONLY to the personal mailbox
('c0000013-0000-0000-0000-000000000013', 'a0000004-0000-0000-0000-000000000004', '[email protected]', 'Secret', NOW(), NOW());
INSERT INTO email_labels (id, link_id, provider_label_id, name, message_list_visibility, label_list_visibility, type, created_at)
VALUES
('11110004-0000-0000-0000-000000000001', 'a0000004-0000-0000-0000-000000000004', 'INBOX', 'INBOX', 'Show', 'LabelShow', 'System', NOW()),
('11110004-0000-0000-0000-000000000002', 'a0000004-0000-0000-0000-000000000004', 'TRASH', 'TRASH', 'Hide', 'LabelHide', 'System', NOW());
-- tp1: personal inbox thread from [email protected] (matches CRM filters,
-- must NOT appear in team-scoped results)
-- tp2: personal inbox thread from [email protected] (its contact must not
-- resolve under team scope)
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
('22220004-0000-0000-0000-000000000001', 'tp1', 'a0000004-0000-0000-0000-000000000004',
true, false, '2026-04-08 10:00:00+00', NULL, '2026-04-08 10:00:00+00', NOW(), NOW()),
('22220004-0000-0000-0000-000000000002', 'tp2', 'a0000004-0000-0000-0000-000000000004',
true, false, '2026-04-09 10:00:00+00', NULL, '2026-04-09 10:00:00+00', NOW(), NOW());
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
('33330004-0000-0000-0000-000000000001', '22220004-0000-0000-0000-000000000001',
'a0000004-0000-0000-0000-000000000004', 'mp1', 'c0000012-0000-0000-0000-000000000012',
'Alice personal acme', 'personal from acme', '2026-04-08 10:00:00+00',
false, false, false, false, NOW(), NOW()),
('33330004-0000-0000-0000-000000000002', '22220004-0000-0000-0000-000000000002',
'a0000004-0000-0000-0000-000000000004', 'mp2', 'c0000013-0000-0000-0000-000000000013',
'Alice personal secret', 'personal from secret', '2026-04-09 10:00:00+00',
false, false, false, false, NOW(), NOW());
INSERT INTO email_message_labels (message_id, label_id)
VALUES
('33330004-0000-0000-0000-000000000001', '11110004-0000-0000-0000-000000000001'),
('33330004-0000-0000-0000-000000000002', '11110004-0000-0000-0000-000000000001');