Team Accounts Implementation Plan

Overview

Team Accounts will allow FanIQ One customers to invite members of their internal staff into their organization's FanIQ One environment. This feature introduces multi-user access and role-based permissions, enabling account owners to invite team members, assign access levels, and control which features each role can use.

Current Status (as of March 14, 2026)

Branch: feature/team-accounts-back

✅ Done

All legacy tables dropped via migrations: squads, squad_members, sub_accounts, sub_account_*, organizations, org_members, team_users, team_sub_accounts, team_sub_account_*, team_user_*
Squad RLS policies removed from account_mappings, active_creatives, campaigns, media_files, meta_tokens, snapchat_tokens, tiktok_tokens
Squad functions dropped: get_squad_admin_ids, get_user_squads, get_user_squad_ids, is_squad_admin
Sub-account system completely removed (tables + functions)
team_invitations "Allow all operations for testing" policy dropped
teams table RLS enabled (basic team_members membership check)
Edge functions exist: create-team, get-user-teams, send-team-invitation, team-invite
Frontend pages exist: Teams.tsx, TeamInvite.tsx, TeamManagement.tsx, TeamDetail.tsx, TeamManage.tsx
Frontend components exist: TeamManagement.tsx, TeamInviteCodeGenerator.tsx
RPC functions exist in DB: generate_team_invite_code, accept_team_invitation, get_team_members_simple, get_team_members_with_users
2.1 Permission matrix defined (this document)

🔄 Partial (Epic 1, Phase 1)

Invitations: send-team-invitation (exists, hardcoded Resend key — security issue), team-invite (partial send + accept logic). Missing: accept-invite-and-register, decline, cancel, list, resend
Creation: create-team (functional), get-user-teams (functional). Missing: delete-team, update-team, remove-team-member, update-team-member-role, leave-team, get-team-members
Invite page: TeamInvite.tsx handles registered-user accept flow. Missing: unregistered user signup form
Team management page: TeamManagement.tsx, TeamManage.tsx, TeamDetail.tsx exist but are scattered — need consolidation

❌ Not Started

Schema migrations (Step 1): account_mappings.team_id not in generated types → not yet added. Critical blocker for all of Phase 1 and Phase 2.
TeamContext + team switcher (Step 3): src/context/TeamContext.tsx does not exist, no switcher in navbar
Epic 1 Phase 2 — Product module integrations: None started (blocked by Phase 1)
Epic 2 — All role-based permission work: Not started

⚠️ Known Issues

send-team-invitation: Resend API key is hardcoded in source — must move to RESEND_API_KEY env var before any deploy
create-team: Calls generate_team_invite_code RPC which exists in DB but is missing a local migration file
Teams.tsx: Calls join-team-with-code edge function which does not exist — remove or replace

Epic 1 Access Model

All team members in Epic 1 have full access — identical to the team owner. Roles are stored in team_members but neither RLS nor TeamContext.hasPermission() enforces them. Epic 2 upgrades the policies with no data migration needed.

One exception enforced from day one: OAuth connect is owner-only in team context. The connect buttons on /link-accounts are hidden for non-owners. Admins can still select ad accounts and create mappings on /select-accounts using the owner's token (by passing owner_id to the edge functions).

Key Product Decisions

4 Roles: Admin, Manager, Contributor, Read-Only
Invitation as authorization: Invitation token bypasses access code for new users (no separate access code needed)
Personal + Team workspaces: All users keep their personal account mappings AND can collaborate in team spaces
Single login: Everyone uses /get-started - no separate team login page
Data scoping: team_id FK on account_mappings + meta_selected_accounts / tiktok_selected_accounts / snapchat_selected_accounts + RLS policy updates on downstream tables. No team_id needed on token tables — tokens remain user-scoped (token resolution goes through account_mappings.user_id = owner_id). Selected accounts tables DO get team_id (task 1.9) — required so the owner can activate different ad accounts per workspace.
Token ownership model: OAuth tokens are stored in meta_tokens, tiktok_tokens, snapchat_tokens — always owned by the individual who authenticated. Deploy functions look up the token by querying meta_tokens WHERE user_id = account_mapping.user_id. In team context, account_mapping.user_id = owner_id, so the owner's token is used automatically. No token columns exist on account_mappings itself.
Account linking in team mode — two distinct steps:
- OAuth connect (/link-accounts): Owner only. Only the team owner authenticates with Meta/TikTok/Snapchat. The connect buttons are hidden for all other members in team context.
- Select ad accounts + create mapping (/select-accounts): Owner + Admins. Any admin can select ad accounts and create team mappings. The frontend passes owner_id (from teams.owner_id) as userId to the meta-accounts, tiktok-accounts, snapchat-accounts edge functions. These functions also need a teamId parameter added — they read meta_selected_accounts WHERE user_id = userId to pre-populate checkboxes, and without team_id scoping, switching workspaces shows wrong pre-checked accounts. Fix: accept teamId in request body and scope the query with .eq("team_id", teamId) (or .is("team_id", null) for personal). See task 4.2. The resulting account_mapping is created with user_id = owner_id, team_id = team_x.
- Personal mode: Any user can link and select their own accounts as today — no change.
Multiple admins, same ad account: If the owner has already linked Meta Ads, a second admin does not re-link — they use the existing team mapping. If the owner links the same Meta account in personal mode AND team mode, two separate account_mapping rows exist (one with team_id = NULL, one with team_id set), each representing a different workspace. This is valid.

Data Model

Entity Relationship Diagram

auth.users
  |
  |-- 1:1 --> profiles
  |
  |-- 1:N --> meta_tokens    (OAuth credentials, owned by user)
  |-- 1:N --> tiktok_tokens  (OAuth credentials, owned by user)
  |-- 1:N --> snapchat_tokens(OAuth credentials, owned by user)
  |
  |-- 1:N --> meta_selected_accounts    (team_id FK) ──┐
  |-- 1:N --> tiktok_selected_accounts  (team_id FK) ──┤─► unified_selected_accounts (VIEW)
  |-- 1:N --> snapchat_selected_accounts(team_id FK) ──┘
  |
  |-- 1:N --> teams (as owner)
  |             |
  |             |-- 1:N --> team_members (collaborators with roles)
  |             |
  |             |-- 1:N --> team_invitations (pending invites)
  |             |
  |             |-- 1:N --> account_mappings (team_id FK) [NEW: +team_id]
  |                           | user_id = owner_id → resolves token via meta_tokens
  |                           |
  |                           |-- 1:N --> campaigns
  |                           |             |-- 1:N --> facebook_campaigns
  |                           |             |-- 1:N --> instagram_campaigns
  |                           |             |-- 1:N --> meta_blended_campaigns
  |                           |             |-- 1:N --> snapchat_campaigns
  |                           |             |-- 1:N --> tiktok_campaigns
  |                           |             |-- 1:N --> faniq_audiences_draft (no user_id col)
  |                           |
  |                           |-- 1:N --> campaign_drafts   [NEW: via account_mapping_id]
  |                           |-- 1:N --> campaign_payloads [NEW: via account_mapping_id]
  |                           |-- 1:N --> media_files
  |                           |-- 1:N --> active_creatives
  |                           |-- 1:N --> facebook_creatives
  |                           |-- 1:N --> snapchat_creatives
  |                           |-- 1:N --> tiktok_creatives
  |                           |-- 1:N --> temporary_audiences
  |                           |-- 1:N --> temporary_exclusions
  |                           |-- 1:N --> active_audiences  [NEW: +account_mapping_id]
  |
  |-- 1:N --> account_mappings (personal, team_id = NULL)
                | user_id = me → resolves token via meta_tokens

Team Switcher Context:
  - "Personal Account" --> account_mappings WHERE user_id = me AND team_id IS NULL
  - "Team X"           --> account_mappings WHERE team_id = team_x_id

Token Resolution (all workspaces):
  - account_mappings.user_id = owner_id
  - deploy functions: SELECT * FROM meta_tokens WHERE user_id = account_mappings.user_id
  - → all team members transparently use the owner's OAuth token
  - token tables (meta_tokens, tiktok_tokens, snapchat_tokens) need NO new columns

Selected Accounts Scoping (after migration 1.9):
  - Personal: meta_selected_accounts WHERE user_id = me AND team_id IS NULL
  - Team X:   meta_selected_accounts WHERE user_id = owner_id AND team_id = team_x_id
  - unified_selected_accounts VIEW updated to expose team_id column

Database Schema & SQL Migrations

1. `teams` Table (Modify Existing)

-- Step 1: Add new columns first (team_id contains display names — must copy before dropping)
ALTER TABLE teams
  ADD COLUMN IF NOT EXISTS name TEXT NOT NULL DEFAULT '',
  ADD COLUMN IF NOT EXISTS description TEXT;

-- Step 2: Copy existing display name to name column (preserves data)
UPDATE teams SET name = team_id WHERE name = '';

-- Step 3: Drop redundant columns
ALTER TABLE teams
  DROP COLUMN IF EXISTS user_id,              -- redundant with owner_id
  DROP COLUMN IF EXISTS account_mapping_ids,  -- replaced by team_id FK on account_mappings
  DROP COLUMN IF EXISTS member_count,         -- derive from COUNT query
  DROP COLUMN IF EXISTS team_id;              -- text display name, now copied to name

Final structure:

Column	Type	Constraints	Description
`id`	UUID	PRIMARY KEY, DEFAULT gen_random_uuid()	Team identifier
`name`	TEXT	NOT NULL	Display name (e.g., "FanIQ Marketing")
`description`	TEXT		Optional team description
`owner_id`	UUID	NOT NULL, FK -> auth.users(id)	Account owner with full control
`invite_code`	TEXT	UNIQUE	Shareable join code
`created_at`	TIMESTAMPTZ	DEFAULT now()
`updated_at`	TIMESTAMPTZ	DEFAULT now()

2. `team_members` Table (Modify Existing)

ALTER TABLE team_members
  DROP CONSTRAINT IF EXISTS team_members_role_check;

-- Migrate existing values BEFORE adding constraint (constraint fails if any rows are invalid)
UPDATE team_members SET role = 'admin' WHERE role = 'owner';
UPDATE team_members SET role = 'contributor' WHERE role NOT IN ('admin', 'manager', 'contributor', 'read_only');

ALTER TABLE team_members
  ADD CONSTRAINT team_members_role_check
  CHECK (role IN ('admin', 'manager', 'contributor', 'read_only'));

-- Ensure no duplicate memberships
ALTER TABLE team_members
  ADD CONSTRAINT team_members_team_user_unique
  UNIQUE (team_id, user_id);

-- Add index for permission lookups
CREATE INDEX IF NOT EXISTS idx_team_members_user_team
  ON team_members(user_id, team_id);

Final structure:

Column	Type	Constraints	Description
`id`	UUID	PRIMARY KEY, DEFAULT gen_random_uuid()
`team_id`	UUID	NOT NULL, FK -> teams(id) ON DELETE CASCADE
`user_id`	UUID	NOT NULL, FK -> auth.users(id) ON DELETE CASCADE
`role`	TEXT	NOT NULL, CHECK (admin/manager/contributor/read_only)	Team role
`joined_at`	TIMESTAMPTZ	DEFAULT now()
`created_at`	TIMESTAMPTZ	DEFAULT now()
`updated_at`	TIMESTAMPTZ	DEFAULT now()

Constraints: UNIQUE(team_id, user_id)

Note: The team owner (teams.owner_id) should also be added as a team_members row with role admin so all permission queries go through one path. The owner_id field on teams provides an extra protection layer (owner can never be demoted/removed).

3. `team_invitations` Table (Modify Existing)

ALTER TABLE team_invitations
  DROP CONSTRAINT IF EXISTS team_invitations_role_check;

ALTER TABLE team_invitations
  DROP CONSTRAINT IF EXISTS team_invitations_status_check;

-- Migrate existing values BEFORE adding constraints (constraints fail if any rows are invalid)
UPDATE team_invitations SET role = 'admin' WHERE role = 'owner';
UPDATE team_invitations SET role = 'contributor' WHERE role NOT IN ('admin', 'manager', 'contributor', 'read_only');
UPDATE team_invitations SET status = 'cancelled' WHERE status NOT IN ('pending', 'accepted', 'expired', 'cancelled');

ALTER TABLE team_invitations
  ADD CONSTRAINT team_invitations_role_check
  CHECK (role IN ('admin', 'manager', 'contributor', 'read_only'));

ALTER TABLE team_invitations
  ADD CONSTRAINT team_invitations_status_check
  CHECK (status IN ('pending', 'accepted', 'expired', 'cancelled'));

-- Prevent duplicate pending invitations for same email + team
CREATE UNIQUE INDEX IF NOT EXISTS idx_team_invitations_pending_unique
  ON team_invitations(team_id, email)
  WHERE status = 'pending';

Final structure:

Column	Type	Constraints	Description
`id`	UUID	PRIMARY KEY, DEFAULT gen_random_uuid()
`team_id`	UUID	NOT NULL, FK -> teams(id) ON DELETE CASCADE
`email`	TEXT	NOT NULL	Invitee's email address
`role`	TEXT	NOT NULL, CHECK (admin/manager/contributor/read_only)	Role assigned on accept
`invited_by`	UUID	NOT NULL, FK -> auth.users(id)	Who sent the invitation
`invitation_token`	UUID	UNIQUE, DEFAULT gen_random_uuid()	Token for accept URL
`status`	TEXT	NOT NULL, DEFAULT 'pending', CHECK (pending/accepted/expired/cancelled)
`expires_at`	TIMESTAMPTZ	DEFAULT now() + interval '30 days'
`created_at`	TIMESTAMPTZ	DEFAULT now()
`updated_at`	TIMESTAMPTZ	DEFAULT now()

Constraints: Unique partial index on (team_id, email) WHERE status = 'pending'

4. `account_mappings` Table (Add 1 Column)

-- Add team_id foreign key (nullable: NULL = personal mapping)
ALTER TABLE account_mappings
  ADD COLUMN IF NOT EXISTS team_id UUID REFERENCES teams(id) ON DELETE SET NULL;

-- Index for team-scoped queries
CREATE INDEX IF NOT EXISTS idx_account_mappings_team_id
  ON account_mappings(team_id)
  WHERE team_id IS NOT NULL;

New column:

Column	Type	Constraints	Description
`team_id`	UUID	FK -> teams(id) ON DELETE SET NULL, NULLABLE	NULL = personal mapping, set = shared with team

All existing columns remain unchanged. Existing data stays untouched (team_id defaults to NULL = personal).

5. `active_audiences` Table (Add 1 Column)

-- Add missing account_mapping_id for team scoping
ALTER TABLE active_audiences
  ADD COLUMN IF NOT EXISTS account_mapping_id UUID REFERENCES account_mappings(id);

-- Backfill from linked campaigns
UPDATE active_audiences aa
SET account_mapping_id = c.account_mapping_id
FROM campaigns c
WHERE aa.group_campaign_id = c.campaign_group_id
  AND aa.account_mapping_id IS NULL;

-- Index for lookups
CREATE INDEX IF NOT EXISTS idx_active_audiences_account_mapping
  ON active_audiences(account_mapping_id);

RLS (Row Level Security) Policies

`account_mappings` RLS

-- Drop legacy squad policies (conflict with new team-based policies)
DROP POLICY IF EXISTS "Squad members can access admin account mappings" ON account_mappings;
DROP POLICY IF EXISTS "Squad members can access admin active creatives" ON active_creatives;
DROP POLICY IF EXISTS "Squad members can access admin campaigns" ON campaigns;
DROP POLICY IF EXISTS "Squad members can access admin media files" ON media_files;
DROP POLICY IF EXISTS "Squad members can access admin meta tokens" ON meta_tokens;
DROP POLICY IF EXISTS "Squad members can access admin snapchat tokens" ON snapchat_tokens;
DROP POLICY IF EXISTS "Squad members can access admin tiktok tokens" ON tiktok_tokens;

-- Drop legacy squad functions
DROP FUNCTION IF EXISTS get_squad_admin_ids(uuid);
DROP FUNCTION IF EXISTS get_user_squad_ids(uuid);
DROP FUNCTION IF EXISTS get_user_squads(uuid);
DROP FUNCTION IF EXISTS is_squad_admin(uuid, uuid);

-- Drop legacy squad tables
DROP TABLE IF EXISTS squad_members;
DROP TABLE IF EXISTS squads;

-- Drop legacy sub-account functions
DROP FUNCTION IF EXISTS get_parent_user_id(uuid);
DROP FUNCTION IF EXISTS has_mapping_access(uuid, uuid, text);
DROP FUNCTION IF EXISTS has_platform_access(uuid, uuid, text);
DROP FUNCTION IF EXISTS is_sub_account(uuid);

-- Drop legacy sub-account tables (order matters for FK)
DROP TABLE IF EXISTS sub_account_mapping_access;
DROP TABLE IF EXISTS sub_account_platform_access;
DROP TABLE IF EXISTS sub_account_invitations;
DROP TABLE IF EXISTS sub_accounts;

-- Drop ALL existing account_mappings policies (including duplicates)
DROP POLICY IF EXISTS "Users can view own mappings" ON account_mappings;
DROP POLICY IF EXISTS "Users can create own mappings" ON account_mappings;
DROP POLICY IF EXISTS "Users access own mappings only" ON account_mappings;
DROP POLICY IF EXISTS "Users can access their own account mappings" ON account_mappings;
DROP POLICY IF EXISTS "Users can delete their own account mappings" ON account_mappings;
DROP POLICY IF EXISTS "Users can insert their own account mappings" ON account_mappings;
DROP POLICY IF EXISTS "Users can update their own account mappings" ON account_mappings;

-- SELECT: own personal mappings + team mappings where user is a member
CREATE POLICY "select_own_or_team_mappings" ON account_mappings
  FOR SELECT USING (
    (user_id = auth.uid() AND team_id IS NULL)    -- personal
    OR team_id IN (                                -- team
      SELECT tm.team_id FROM team_members tm
      WHERE tm.user_id = auth.uid()
    )
  );

-- INSERT: personal mappings or team mappings if user is admin
CREATE POLICY "insert_mappings" ON account_mappings
  FOR INSERT WITH CHECK (
    (user_id = auth.uid() AND team_id IS NULL)    -- personal
    OR team_id IN (                                -- team admin only
      SELECT tm.team_id FROM team_members tm
      WHERE tm.user_id = auth.uid()
        AND tm.role = 'admin'
    )
  );

-- UPDATE: own personal or team admin
CREATE POLICY "update_mappings" ON account_mappings
  FOR UPDATE USING (
    (user_id = auth.uid() AND team_id IS NULL)
    OR team_id IN (
      SELECT tm.team_id FROM team_members tm
      WHERE tm.user_id = auth.uid()
        AND tm.role = 'admin'
    )
  );

-- DELETE: own personal or team admin
CREATE POLICY "delete_mappings" ON account_mappings
  FOR DELETE USING (
    (user_id = auth.uid() AND team_id IS NULL)
    OR team_id IN (
      SELECT tm.team_id FROM team_members tm
      WHERE tm.user_id = auth.uid()
        AND tm.role = 'admin'
    )
  );

`teams` RLS

-- SELECT: owner or member
CREATE POLICY "select_own_teams" ON teams
  FOR SELECT USING (
    owner_id = auth.uid()
    OR id IN (
      SELECT tm.team_id FROM team_members tm
      WHERE tm.user_id = auth.uid()
    )
  );

-- INSERT: any authenticated user can create a team
CREATE POLICY "insert_teams" ON teams
  FOR INSERT WITH CHECK (
    owner_id = auth.uid()
  );

-- UPDATE: owner only
CREATE POLICY "update_teams" ON teams
  FOR UPDATE USING (
    owner_id = auth.uid()
  );

-- DELETE: owner only
CREATE POLICY "delete_teams" ON teams
  FOR DELETE USING (
    owner_id = auth.uid()
  );

`team_members` RLS

-- SELECT: members can see their teammates
CREATE POLICY "select_team_members" ON team_members
  FOR SELECT USING (
    team_id IN (
      SELECT tm.team_id FROM team_members tm
      WHERE tm.user_id = auth.uid()
    )
  );

-- INSERT/UPDATE/DELETE: admin only (+ service role for edge functions)
CREATE POLICY "manage_team_members" ON team_members
  FOR ALL USING (
    team_id IN (
      SELECT tm.team_id FROM team_members tm
      WHERE tm.user_id = auth.uid()
        AND tm.role = 'admin'
    )
  );

`team_invitations` RLS

-- SELECT: admin + manager can view invitations
CREATE POLICY "select_team_invitations" ON team_invitations
  FOR SELECT USING (
    team_id IN (
      SELECT tm.team_id FROM team_members tm
      WHERE tm.user_id = auth.uid()
        AND tm.role IN ('admin', 'manager')
    )
  );

-- INSERT: admin + manager can create invitations
CREATE POLICY "insert_team_invitations" ON team_invitations
  FOR INSERT WITH CHECK (
    team_id IN (
      SELECT tm.team_id FROM team_members tm
      WHERE tm.user_id = auth.uid()
        AND tm.role IN ('admin', 'manager')
    )
  );

-- UPDATE/DELETE: admin only
CREATE POLICY "manage_team_invitations" ON team_invitations
  FOR UPDATE USING (
    team_id IN (
      SELECT tm.team_id FROM team_members tm
      WHERE tm.user_id = auth.uid()
        AND tm.role = 'admin'
    )
  );

Downstream Tables RLS (12 Tables)

The same pattern applies to all downstream tables. The SELECT policy allows access to personal data (via user_id) and team data (via account_mapping_id -> account_mappings.team_id -> team_members). The INSERT/UPDATE/DELETE policies additionally check the user's role.

Tables: campaigns, media_files, active_creatives, facebook_creatives, snapchat_creatives, tiktok_creatives, temporary_audiences, temporary_exclusions, active_audiences, faniq_audiences_draft, campaign_drafts, campaign_payloads

-- ============================================================
-- SELECT POLICY (same for ALL 12 tables)
-- ============================================================
-- Template: replace [TABLE_NAME] with actual table name
-- NOTE: `faniq_audiences_draft` has NO user_id column — omit the
-- `user_id = auth.uid()` condition and use only the account_mapping_id path.

CREATE POLICY "select_own_or_team_data" ON [TABLE_NAME]
  FOR SELECT USING (
    user_id = auth.uid()                          -- omit for faniq_audiences_draft
    OR account_mapping_id IN (
      SELECT am.id FROM account_mappings am
      JOIN team_members tm ON tm.team_id = am.team_id
      WHERE tm.user_id = auth.uid()
    )
  );


-- ============================================================
-- INSERT POLICIES (role-gated per table)
-- ============================================================

-- campaigns: Admin + Manager only
CREATE POLICY "insert_own_or_team_campaigns" ON campaigns
  FOR INSERT WITH CHECK (
    user_id = auth.uid()
    OR account_mapping_id IN (
      SELECT am.id FROM account_mappings am
      JOIN team_members tm ON tm.team_id = am.team_id
      WHERE tm.user_id = auth.uid()
        AND tm.role IN ('admin', 'manager')
    )
  );

-- media_files: Admin + Manager + Contributor
CREATE POLICY "insert_own_or_team_media" ON media_files
  FOR INSERT WITH CHECK (
    user_id = auth.uid()
    OR account_mapping_id IN (
      SELECT am.id FROM account_mappings am
      JOIN team_members tm ON tm.team_id = am.team_id
      WHERE tm.user_id = auth.uid()
        AND tm.role IN ('admin', 'manager', 'contributor')
    )
  );

-- active_creatives, facebook_creatives, snapchat_creatives, tiktok_creatives:
-- Admin + Manager + Contributor
CREATE POLICY "insert_own_or_team_creatives" ON active_creatives
  FOR INSERT WITH CHECK (
    user_id = auth.uid()
    OR account_mapping_id IN (
      SELECT am.id FROM account_mappings am
      JOIN team_members tm ON tm.team_id = am.team_id
      WHERE tm.user_id = auth.uid()
        AND tm.role IN ('admin', 'manager', 'contributor')
    )
  );
-- Repeat for facebook_creatives, snapchat_creatives, tiktok_creatives

-- IMPORTANT: Drop the wide-open temporary_audiences INSERT policy first
DROP POLICY IF EXISTS "Allow authenticated users to insert into temporary_audiences" ON temporary_audiences;

-- temporary_audiences, temporary_exclusions: Admin + Manager
CREATE POLICY "insert_own_or_team_temp_audiences" ON temporary_audiences
  FOR INSERT WITH CHECK (
    user_id = auth.uid()
    OR account_mapping_id IN (
      SELECT am.id FROM account_mappings am
      JOIN team_members tm ON tm.team_id = am.team_id
      WHERE tm.user_id = auth.uid()
        AND tm.role IN ('admin', 'manager')
    )
  );
-- Repeat for temporary_exclusions

-- active_audiences: Admin + Manager
CREATE POLICY "insert_own_or_team_active_audiences" ON active_audiences
  FOR INSERT WITH CHECK (
    user_id = auth.uid()
    OR account_mapping_id IN (
      SELECT am.id FROM account_mappings am
      JOIN team_members tm ON tm.team_id = am.team_id
      WHERE tm.user_id = auth.uid()
        AND tm.role IN ('admin', 'manager')
    )
  );

-- faniq_audiences_draft: Admin + Manager
CREATE POLICY "insert_own_or_team_audience_drafts" ON faniq_audiences_draft
  FOR INSERT WITH CHECK (
    account_mapping_id IN (
      SELECT am.id FROM account_mappings am
      WHERE am.user_id = auth.uid()
        OR (am.team_id IN (
          SELECT tm.team_id FROM team_members tm
          WHERE tm.user_id = auth.uid()
            AND tm.role IN ('admin', 'manager')
        ))
    )
  );


-- ============================================================
-- UPDATE POLICIES (same role gates as INSERT per table)
-- ============================================================
-- Follow the same pattern as INSERT for each table.
-- Replace FOR INSERT WITH CHECK with FOR UPDATE USING.


-- ============================================================
-- DELETE POLICIES (Admin only for team data, owner for personal)
-- ============================================================
-- Template for all tables:

CREATE POLICY "delete_own_or_team_admin" ON [TABLE_NAME]
  FOR DELETE USING (
    user_id = auth.uid()
    OR account_mapping_id IN (
      SELECT am.id FROM account_mappings am
      JOIN team_members tm ON tm.team_id = am.team_id
      WHERE tm.user_id = auth.uid()
        AND tm.role = 'admin'
    )
  );

INSERT Policy Role Summary

Table	Admin	Manager	Contributor	Read-Only
campaigns	Yes	Yes	-	-
media_files	Yes	Yes	Yes	-
active_creatives	Yes	Yes	Yes	-
facebook_creatives	Yes	Yes	Yes	-
snapchat_creatives	Yes	Yes	Yes	-
tiktok_creatives	Yes	Yes	Yes	-
temporary_audiences	Yes	Yes	-	-
temporary_exclusions	Yes	Yes	-	-
active_audiences	Yes	Yes	-	-
faniq_audiences_draft	Yes	Yes	-	-
campaign_drafts	Yes	Yes	-	-
campaign_payloads	Yes	Yes	-	-

Role-Permission Matrix

+----------------------------+-------+---------+-------------+-----------+
| Feature                    | Admin | Manager | Contributor | Read-Only |
+----------------------------+-------+---------+-------------+-----------+
| Team settings & roles      |   X   |         |             |           |
| Invite members             |   X   |    X    |             |           |
| Account linking            |   X   |         |             |           |
| Campaigns (create/edit)    |   X   |    X    |             |           |
| Audiences & Pixels         |   X   |    X    |             |           |
| Media gallery              |   X   |    X    |      X      |           |
| Video creation             |   X   |    X    |      X      |           |
| Reporting dashboards       |   X   |    X    |      X      |     X     |
+----------------------------+-------+---------+-------------+-----------+

Edge Functions

`send-team-invitation`

Method:  POST
Auth:    Required (Admin or Manager of the team)
Body:    { team_id: UUID, email: string, role: string }

Logic:
  1. Verify caller is admin/manager of team via team_members
  2. Validate email format
  3. Check no pending invitation exists for email + team
  4. Insert into team_invitations with generated invitation_token
  5. (Hook for email notification with link: /team-invite/:token)

Returns: { success: true, invitation_id: UUID }
Errors:  401 (not authenticated), 403 (not admin/manager),
         400 (duplicate pending invite, invalid email)

`accept-team-invitation` (Registered Users)

Method:  POST
Auth:    Required (the invited user must be logged in)
Body:    { invitation_token: UUID }

Logic:
  1. Validate token exists in team_invitations
  2. Check status = 'pending' and not expired (expires_at > now())
  3. Verify authenticated user's email matches invitation email
  4. Insert into team_members with role from invitation
  5. Update invitation status to 'accepted'

Returns: { success: true, team_id: UUID, role: string }
Errors:  401, 400 (expired/invalid token), 403 (email mismatch)

`accept-invite-and-register` (Unregistered Users)

Method:  POST
Auth:    None (public endpoint, uses service role internally)
Body:    { invitation_token: UUID, email: string, password: string }

Logic (all in one atomic operation):
  1. Validate token exists, status = 'pending', not expired
  2. Verify provided email matches invitation email
  3. Create auth user via supabase.auth.admin.createUser()
     - email_confirm: true (skip email verification)
     - NO access code required - invitation token IS the authorization
  4. Create profile record (first_name, last_name empty)
  5. Insert into team_members with role from invitation
  6. Update invitation status to 'accepted'
  7. On ANY failure: rollback all changes

Returns: { success: true, user_id: UUID, team_id: UUID, role: string }
Errors:  400 (expired/invalid token, email mismatch, user already exists)

`decline-team-invitation`

Method:  POST
Auth:    Required
Body:    { invitation_token: UUID }

Logic:
  1. Validate token and check email match
  2. Update invitation status to 'cancelled'

Returns: { success: true }

`cancel-team-invitation`

Method:  POST
Auth:    Required (Admin only)
Body:    { invitation_id: UUID }

Logic:
  1. Verify caller is admin of the team
  2. Update invitation status to 'cancelled'

Returns: { success: true }

`list-team-invitations`

Method:  GET
Auth:    Required (Admin or Manager)
Params:  ?team_id=UUID

Logic:
  1. Verify caller is admin/manager of team
  2. Select all invitations for team ordered by created_at desc

Returns: { invitations: [{ id, email, role, status, invited_by, expires_at, created_at }] }

`resend-team-invitation`

Method:  POST
Auth:    Required (Admin or Manager)
Body:    { invitation_id: UUID }

Logic:
  1. Verify caller is admin/manager of team
  2. Check invitation status is 'pending'
  3. Reset expires_at to now() + 30 days
  4. Re-trigger email notification

Returns: { success: true }

`create-team`

Method:  POST
Auth:    Required
Body:    { name: string }

Logic:
  1. Validate team name (non-empty, max 100 chars)
  2. Insert into teams with owner_id = auth.uid()
  3. Insert creator into team_members with role = 'admin'
  4. Generate unique invite_code

Returns: { success: true, team: { id, name, invite_code } }
Errors:  401 (not authenticated), 400 (invalid name)

`delete-team`

Method:  POST
Auth:    Required (Owner only — teams.owner_id)
Body:    { team_id: UUID }

Logic:
  1. Verify caller is the team owner (not just admin)
  2. Set team_id = NULL on all account_mappings belonging to this team
  3. Delete team (CASCADE removes team_members, team_invitations)

Returns: { success: true }
Errors:  401, 403 (not owner)

`update-team`

Method:  POST
Auth:    Required (Admin only)
Body:    { team_id: UUID, name?: string }

Logic:
  1. Verify caller is admin of the team
  2. Update team fields

Returns: { success: true }
Errors:  401, 403 (not admin)

`remove-team-member`

Method:  POST
Auth:    Required (Admin only)
Body:    { team_id: UUID, user_id: UUID }

Logic:
  1. Verify caller is admin of the team
  2. Prevent removing the team owner (teams.owner_id)
  3. Delete from team_members WHERE team_id AND user_id

Returns: { success: true }
Errors:  401, 403 (not admin), 400 (cannot remove owner)

`update-team-member-role`

Method:  POST
Auth:    Required (Admin only)
Body:    { team_id: UUID, user_id: UUID, role: string }

Logic:
  1. Verify caller is admin of the team
  2. Prevent changing owner's role (must always be admin)
  3. Validate role is one of: admin, manager, contributor, read_only
  4. Update team_members.role

Returns: { success: true }
Errors:  401, 403 (not admin), 400 (invalid role, cannot change owner)

`leave-team`

Method:  POST
Auth:    Required
Body:    { team_id: UUID }

Logic:
  1. Verify caller is a member of the team
  2. Prevent owner from leaving (must transfer ownership or delete team)
  3. Delete from team_members WHERE team_id AND user_id = auth.uid()

Returns: { success: true }
Errors:  401, 400 (owner cannot leave)

`get-user-teams`

Method:  GET
Auth:    Required

Logic:
  1. Select team_members JOIN teams WHERE user_id = auth.uid()
  2. Include role, team name, owner_id, member count

Returns: { teams: [{ id, name, role, owner_id, member_count }] }

`get-team-members`

Method:  GET
Auth:    Required (any team member)
Params:  ?team_id=UUID

Logic:
  1. Verify caller is a member of the team
  2. Select team_members JOIN profiles WHERE team_id

Returns: { members: [{ user_id, name, email, role, joined_at, is_owner }] }

`send-invitation-email`

Method:  POST (internal, called by send-team-invitation and resend-team-invitation)
Auth:    Service role only
Body:    { email, team_name, inviter_name, role, invitation_token }

Logic:
  1. Build email with team name, inviter, role, and accept link
  2. Send via Resend API (or Supabase email hook)
  3. Fire-and-forget — failures don't block invitation creation

Returns: { success: true }

Frontend Components

TeamContext (`src/context/TeamContext.tsx`)

interface Team {
  id: string;
  name: string;
  owner_id: string;
  invite_code: string;
}

interface TeamMembership {
  team: Team;
  role: 'admin' | 'manager' | 'contributor' | 'read_only';
}

interface TeamContextType {
  currentTeam: Team | null;               // null = personal mode
  userTeams: TeamMembership[];            // all teams user belongs to
  currentRole: string | null;             // null in personal mode
  isPersonalMode: boolean;                // true when no team selected
  switchTeam: (teamId: string | null) => void;  // null switches to personal
  hasPermission: (action: string) => boolean;    // always true in personal mode
}

Behavior:

On login: fetches user's teams via get-user-teams edge function
Default: "Personal Account" mode (no team context, full access)
switchTeam(): updates context, persists team ID to localStorage, triggers data re-fetch
hasPermission(): returns true for everything in personal mode; in team mode, checks currentRole against the permission matrix

Team Switcher (`src/components/DashboardNavbar.tsx`)

+-------------------------------+
| [v] Personal Account          |
|   ----------------------------+
|   FanIQ Marketing   (Admin)   |
|   Client ABC        (Manager) |
|   Agency XYZ        (Read-Only)|
+-------------------------------+

Behavior:

Dropdown in the navbar header area
Shows all teams the user belongs to + "Personal Account" option
Role badge displayed next to each team name
Selecting a team updates TeamContext and refreshes all data queries
Visual distinction between personal mode and team mode

PermissionGate (`src/components/PermissionGate.tsx`)

// Route wrapper - blocks access for unauthorized roles
<PermissionGate requiredPermission="campaigns.create">
  <CreateCampaignPage />
</PermissionGate>

// Hook for conditional UI rendering
const canCreateCampaigns = usePermission('campaigns.create');
// Returns true in personal mode (backward compat)
// Returns true/false based on role in team mode

Permission action strings:

Action	Admin	Manager	Contributor	Read-Only
`team.manage`	Yes	-	-	-
`team.invite`	Yes	Yes	-	-
`accounts.manage`	Yes	-	-	-
`campaigns.create`	Yes	Yes	-	-
`campaigns.view`	Yes	Yes	-	-
`audiences.manage`	Yes	Yes	-	-
`media.upload`	Yes	Yes	Yes	-
`media.view`	Yes	Yes	Yes	-
`video.create`	Yes	Yes	Yes	-
`reporting.view`	Yes	Yes	Yes	Yes

Accept/Decline Invitation Page (`/team-invite/:token`)

Unregistered user (no session):

+-----------------------------+
| Join "FanIQ Marketing"      |
| as Manager                  |
|                             |
| Email: bob@co.com  [locked] |
| Password: [______________]  |
| Confirm:  [______________]  |
|                             |
| [Create Account & Join]     |
+-----------------------------+
| Already have an account?    |
| Sign in instead             |
+-----------------------------+

Registered user (logged in):

+-----------------------------+
| Join "FanIQ Marketing"      |
| as Manager                  |
|                             |
| Invited by Alice Smith      |
|                             |
| [Accept]     [Decline]      |
+-----------------------------+

Error states:

Expired invitation: "This invitation has expired. Contact your team administrator."
Already accepted: "This invitation has already been accepted. Sign in to continue."
Invalid token: "Invalid invitation link."

Routes & Permission Gating

Routes to gate in `src/App.tsx`

Route	Required Permission	Allowed Roles
`/create-campaign`	`campaigns.create`	Admin, Manager
`/unified-campaign-builder`	`campaigns.create`	Admin, Manager
`/media-library`	`media.upload`	Admin, Manager, Contributor
`/video-creator`	`video.create`	Admin, Manager, Contributor
`/video-templates`	`video.create`	Admin, Manager, Contributor
`/audiences`	`audiences.manage`	Admin, Manager
`/create-audience`	`audiences.manage`	Admin, Manager
`/pixels`	`audiences.manage`	Admin, Manager
`/pixel-management`	`audiences.manage`	Admin, Manager
`/link-accounts`	`accounts.manage`	Admin
`/select-accounts`	`accounts.manage`	Admin
`/teams/:id/manage`	`team.manage`	Admin
`/profile` (reporting tab)	`reporting.view`	All roles
`/dashboard`	`reporting.view`	All roles

Navigation items to hide in `DashboardNavbar.tsx`

Hide nav items when user lacks the required permission. Use usePermission() hook for conditional rendering.

Tables Reference

Tables requiring RLS updates (12 tables)

Table	Has user_id	Has account_mapping_id	INSERT roles
`campaigns`	Yes	Yes	Admin, Manager
`media_files`	Yes	Yes	Admin, Manager, Contributor
`active_creatives`	Yes	Yes	Admin, Manager, Contributor
`facebook_creatives`	Yes	Yes	Admin, Manager, Contributor
`snapchat_creatives`	Yes	Yes	Admin, Manager, Contributor
`tiktok_creatives`	Yes	Yes	Admin, Manager, Contributor
`temporary_audiences`	Yes	Yes	Admin, Manager
`temporary_exclusions`	Yes	Yes	Admin, Manager
`active_audiences`	Yes	Adding (new)	Admin, Manager
`faniq_audiences_draft`	No	Yes	Admin, Manager
`campaign_drafts`	Yes	Yes	Admin, Manager
`campaign_payloads`	Yes	Yes	Admin, Manager

Tables requiring `team_id` addition (selected accounts)

Table	Change	Reason
`meta_selected_accounts`	Add `team_id UUID FK -> teams(id) ON DELETE SET NULL`	Owner can have different Meta ad accounts activated per workspace. `NULL` = personal, set = team workspace. Supports agency model: one OAuth connection, multiple client teams with different account selections.
`tiktok_selected_accounts`	Same	Same reason
`snapchat_selected_accounts`	Same	Same reason

Query pattern after change:

-- Personal workspace selections
SELECT * FROM meta_selected_accounts WHERE user_id = $owner_id AND team_id IS NULL;

-- Team workspace selections
SELECT * FROM meta_selected_accounts WHERE user_id = $owner_id AND team_id = $team_id;

Tables that need NO changes

Table	Reason
`facebook_campaigns`	Linked via campaign_id -> campaigns -> account_mapping_id
`instagram_campaigns`	Same chain
`snapchat_campaigns`	Same chain
`tiktok_campaigns`	Same chain
`meta_blended_campaigns`	Same chain
`meta_tokens`	User-scoped OAuth tokens (team owner's tokens shared via edge function)
`snapchat_tokens`	Same
`tiktok_tokens`	Same
`google_ads_tokens`	Same
`profiles`	Personal user data
`user_preferences`	Personal settings
`cached_*` tables (5)	Performance caches per user
`audiences`	Global reference/lookup data
`shoppable_audiences`	Global reference data
Template tables	Global reference data

Tables to deprecate (legacy)

Table	Reason
`team_users`	Legacy custom auth - replaced by Supabase Auth via team_members
`team_user_sessions`	Goes with team_users
`team_sub_accounts`	Replaced by team_members
`team_sub_account_mapping_access`	Replaced by role-based RLS via team_id on account_mappings
`team_sub_account_platform_access`	Replaced by role-permission matrix
`team_user_platform_access`	Goes with team_users
`team_user_mapping_access`	Goes with team_users
`organizations`	Never used
`org_members`	Never used
`squads`	Legacy squad system — replaced by teams. Has conflicting RLS policies on account_mappings
`squad_members`	Goes with squads
`sub_accounts`	Legacy sub-account hierarchy — replaced by team_members
`sub_account_mapping_access`	Legacy mapping-level access — replaced by team_id on account_mappings + RLS
`sub_account_platform_access`	Legacy platform-level access — replaced by role-permission matrix
`sub_account_invitations`	Legacy invitation flow — replaced by team_invitations

Invitation Flows

Flow 1: Invite unregistered user

1. Admin clicks "Invite Member" on team management page
2. Enters email + selects role (e.g., bob@company.com, Manager)
3. Frontend calls POST /send-team-invitation
4. Backend creates team_invitations record with token
5. (Email sent to Bob with link: /team-invite/{token})
6. Bob clicks link -> /team-invite/:token page loads
7. Page detects no auth session -> shows signup form
8. Bob enters password (email pre-filled and locked)
9. Frontend calls POST /accept-invite-and-register
10. Backend atomically: creates auth user + profile + team_members record
11. Frontend auto-signs Bob in -> redirects to dashboard in team context

Flow 2: Invite registered user

1. Admin invites alice@company.com (already has FanIQ account)
2. Alice receives email with /team-invite/{token} link
3. Alice clicks link -> page detects existing auth session
4. Page shows team info + Accept/Decline buttons
5. Alice clicks Accept
6. Frontend calls POST /accept-team-invitation
7. Backend creates team_members record with assigned role
8. Redirect to dashboard in team context

Flow 3: Team switching

1. User logs in via /get-started (normal login)
2. AuthContext loads session, TeamContext fetches user's teams
3. Default: Personal Account mode (full access, own data)
4. User clicks team switcher dropdown in navbar
5. Selects "FanIQ Marketing" (role: Manager)
6. TeamContext updates: currentTeam, currentRole
7. All data queries re-fetch with team-scoped account_mappings
8. Navigation updates: items hidden based on Manager permissions
9. User can switch back to Personal Account anytime

Flow 4: Create team and connect ad accounts

1. User clicks "Create Team" in team switcher dropdown
2. Modal opens, user enters team name (e.g., "FanIQ Marketing")
3. Frontend calls POST /create-team
4. Backend creates team + adds user as admin in team_members
5. TeamContext refreshes, auto-switches to new team
6. Team workspace starts empty — no account mappings yet
7. Owner navigates to /link-accounts (in team context)
8. Owner connects OAuth with Meta/TikTok/Snapchat (owner-only, buttons
   hidden for non-owners)
9. New account_mapping is created with user_id = owner_id, team_id = team.id
10. Owner navigates to /select-accounts, selects which ad accounts to
    activate for this team workspace
11. Owner invites team members (Flow 1/2) — they can now create campaigns
    using the team's account mappings

Jira Roadmap

Audit Results (✅ Completed)

The audit of existing team infrastructure has been completed and all legacy tables/functions have been removed via migrations. Results:

Keep & modify: teams, team_members, team_invitations, account_mappings, active_audiences
Deprecated & dropped: team_users, team_sub_accounts, team_user_platform_access, team_user_mapping_access, team_sub_account_*, organizations, org_members, squads, squad_members, sub_accounts, sub_account_mapping_access, sub_account_platform_access, sub_account_invitations (+ squad RLS policies on 7 tables, 4 squad functions, 4 sub-account functions)
Edge functions to replace: accept-team-invitation-complete, create-team-user, create-team-sub-account, join-team-with-code, get-team-access-context
Frontend to deprecate: TeamLogin.tsx

Full deprecation analysis: docs/LEGACY_TEAM_ACCOUNTS_DISADVANTAGES.md

Epic Definitions

Epic 1 — Team Workspaces & Collaboration (Full Access)

Goal: Any invited team member can do everything the team owner can do across all product pages. Delivered in two phases:

Phase 1 (Dev 1 solo): Build the entire team infrastructure — schema, invitations, team context, switcher, member management.
Phase 2 (Dev 1 + Dev 2): Review and fix every product module so it works correctly in both team workspace and personal workspace.

RLS policies check team membership only (no role conditions). TeamContext.hasPermission() always returns true. Roles are stored in team_members so Epic 2 is a smooth upgrade with no data migration.

Epic 2 — Role-Based Permissions

Goal: Layer access control on top of the working collaboration foundation. Introduce PermissionGate, role-checked RLS, and edge function guards.

Epic 1 — Phase 1: Team Infrastructure (Dev 1 solo)

Dev 2 is free to work on other product work while Phase 1 is in progress.

Step 1 — Schema Migrations (do first, everything else is blocked)

#	Task	Status
1.1	`teams` cleanup: drop redundant columns (`user_id`, `account_mapping_ids`, `member_count`, `team_id` TEXT display name), add `name TEXT NOT NULL DEFAULT ''` and `description TEXT NULL`. Order matters: (1) ADD `name`+`description` first, (2) `UPDATE teams SET name = team_id WHERE name = ''` to preserve existing team names, (3) then DROP the old columns. Final columns: id, name, description, owner_id, invite_code, created_at, updated_at. Note: `description` is already referenced by `TeamManagement.tsx`, `TeamInvite.tsx`, and `team-invite` edge function — must be added here.	❌
1.2	`team_members`: update role constraint to `(admin, manager, contributor, read_only)`, add UNIQUE(team_id, user_id). Data migration required first: `UPDATE team_members SET role = 'admin' WHERE role IN ('owner', 'admin')` and `SET role = 'contributor' WHERE role NOT IN (new enum)` — otherwise `ADD CONSTRAINT` will fail on existing rows with `role='owner'`/`role='member'`.	❌
1.3	`team_invitations`: update role + status constraints, add partial unique index on pending invites. Data migration required first: update any rows with old role/status values before adding constraints (`role='owner'`→`'admin'`, `role='member'`→`'contributor'`, any status not in new enum → `'cancelled'`).	❌
1.4	`account_mappings`: add `team_id UUID FK -> teams(id) ON DELETE SET NULL`. `user_id` stays as the token owner. No changes to `meta_tokens`, `tiktok_tokens`, `snapchat_tokens`.	❌
1.5	`active_audiences`: add `account_mapping_id UUID FK -> account_mappings(id)`, backfill, index	❌
1.6	RLS on `teams`, `team_members`, `team_invitations`, `account_mappings` — membership-only checks, no role conditions	❌
1.7	RLS SELECT on downstream tables — any team member can read via `account_mapping_id → team_id → team_members`	❌
1.8	RLS INSERT/UPDATE/DELETE on downstream tables — any team member (no role gate in Epic 1)	❌
1.9	`meta_selected_accounts`, `tiktok_selected_accounts`, `snapchat_selected_accounts`: add `team_id UUID FK -> teams(id) ON DELETE SET NULL`. Personal selections: `team_id IS NULL`. Team workspace selections: `team_id = team_x`. This allows the owner (the one person with platform credentials) to activate different ad accounts per workspace — critical for the agency model where one person manages multiple client teams from a single OAuth connection. Also update the `unified_selected_accounts` VIEW (a DB view that UNIONs all three tables) to include `team_id` in its SELECT. Use `CREATE OR REPLACE VIEW` — `ALTER VIEW ... AS` is NOT valid PostgreSQL syntax. Note: `tiktok_selected_accounts` uses `advertiser_id`/`advertiser_name` columns (not `account_id`/`name`). Keep `WITH (security_invoker = on)`. Correct SQL: `CREATE OR REPLACE VIEW unified_selected_accounts WITH (security_invoker = on) AS SELECT 'TikTok' AS platform, t.advertiser_id AS account_id, t.advertiser_name AS name, t.user_id, t.team_id FROM tiktok_selected_accounts t UNION ALL SELECT 'Snapchat' AS platform, s.account_id, s.name, s.user_id, s.team_id FROM snapchat_selected_accounts s UNION ALL SELECT 'Meta' AS platform, m.account_id, m.name, m.user_id, m.team_id FROM meta_selected_accounts m;`. Used by `LinkAccounts.tsx` and `Optimize.tsx` — without this update those pages won't be able to filter selections by workspace.	❌
1.10	`generate_team_invite_code` RPC — verified already tracked in `supabase/migrations/20251127173352_remote_schema.sql`. No additional migration file needed.	✅

Step 2 — Team Creation & Invitations

#	Story	Status	Notes
2.1	`create-team` edge function — three fixes: (a) fix Deno import version; (b) fix owner inserted into `team_members` with `role='owner'` (invalid enum) → must be `role='admin'`; (c) fix teams INSERT: currently does `{ team_id: team_name }` — after migration 1.1, `teams.team_id` (TEXT display name) is dropped and `teams.name` is added, so this must become `{ name: team_name }`. Also fix response line that returns `team.team_id` → `team.name`. Hard dependency on migration 1.1.	🔄	Exists, needs cleanup
2.2	`get-user-teams` edge function — update to work with new schema after migration 1.1 drops `account_mapping_ids`, `member_count`, `user_id`, `team_id` (display name) columns from `teams`. Replace `account_mapping_ids` in response with `account_mapping_count` (COUNT from `account_mappings WHERE team_id = team.id`) and `member_count` (COUNT from `team_members WHERE team_id = team.id`). Hard dependency on 1.1.	🔄	Exists, breaks after migration 1.1
2.3	`delete-team` edge function	❌
2.4	`send-team-invitation` — three fixes: (a) move hardcoded Resend API key to `RESEND_API_KEY` env var; (b) add email validation, check for duplicate pending invite; (c) fix teams query: currently does `.select('team_id')` and reads `team.team_id` as the team name (line 79: `const teamName = team.team_id`) — after migration 1.1 drops the TEXT `team_id` column, this returns undefined. Fix: `.select('name')` and `const teamName = team.name`. Hard dependency on migration 1.1.	🔄	Exists, has security issue
2.5	`send-invitation-email` internal helper — extract email sending into `_shared/`, call from send + resend functions	🔄	Logic exists inline, needs extraction
2.6	`accept-team-invitation` — registered users: verify email match, insert `team_members`, mark invitation accepted	❌	Partial logic in `team-invite` POST
2.7	`accept-invite-and-register` — unregistered users: atomically create auth user + profile + `team_members` record, no access code required	❌
2.8	`decline-team-invitation` — mark invitation cancelled	❌
2.9	`cancel-team-invitation` — admin cancels a pending invite	❌
2.10	`list-team-invitations` — return all invitations for a team	❌
2.11	`resend-team-invitation` — reset `expires_at` to +30 days, re-send email	❌
2.12	`/team-invite/:token` page — registered user flow: show team info, Accept / Decline buttons	🔄	`TeamInvite.tsx` exists, wire to `accept-team-invitation`
2.13	`/team-invite/:token` page — unregistered user flow: signup form with email pre-filled and locked, no access code	❌	Needs `accept-invite-and-register`
2.14	Invite form UI (email input + role selector) inside team management page	❌
2.15	Pending invitations list UI (status badges, cancel, resend actions) inside team management page	❌
2.16	`join-team-with-code` edge function — look up team by `invite_code`, verify code is valid, insert caller into `team_members` with default role `contributor`. This is the code-based join path (distinct from email invitation). `generate_team_invite_code` RPC and `teams.invite_code` column already exist.	❌	Replaces broken reference in `Teams.tsx`

Step 3 — Team Context, Switcher & Member Management

#	Story	Status	Notes
3.1	`TeamContext` (`src/context/TeamContext.tsx`) — `currentTeam`, `userTeams`, `switchTeam`, `isPersonalMode`, `hasPermission` (always `true` in Epic 1). Persist active team to `localStorage`.	❌
3.2	Team switcher dropdown in `DashboardNavbar` — Personal Account option + list of teams with role badge + Create Team entry	❌
3.3	Create team modal — team name input, call `create-team`, auto-switch TeamContext to new team	❌
3.4	`get-team-members` edge function	❌	`get_team_members_simple` RPC exists as fallback
3.5	`remove-team-member` edge function	❌
3.6	`update-team-member-role` edge function	❌
3.7	`leave-team` edge function	❌
3.8	`update-team` edge function (rename team)	❌
3.9	~~`assign-account-mapping-to-team`~~ — Removed from scope. Team workspaces start fresh: owner connects OAuth in team context via `/link-accounts`, creating a new `account_mapping` with `team_id` set. No need to move personal mappings to a team.	N/A	Out of scope
3.10	Team management page (`/teams/:teamId/settings`) — Members tab, Invitations tab, Settings tab. Consolidate existing partial files (`TeamManagement.tsx`, `TeamManage.tsx`, `TeamDetail.tsx`). Note: no Accounts tab needed — account linking is done via `/link-accounts` page.	❌	Partial files exist
3.11	`Teams.tsx` (teams list page `/teams`) — four fixes: (a) fix broken `join-team-with-code` call (function never existed), wire to new `join-team-with-code` edge function (task 2.16); (b) remove the `loadAccountMappingNames` helper which calls non-existent `get-account-mapping-names` edge function — after task 2.2, `get-user-teams` returns `account_mapping_count` (number) instead of `account_mapping_ids` (array), so this whole secondary fetch is gone; (c) wire Create Team button to `create-team` edge function, list teams from `get-user-teams`; (d) update TypeScript interfaces: `Team` interface must use `name` (not `team_id`), remove `user_id`/`account_mapping_ids` fields; `TeamMembership.role` must use `"admin"\|"manager"\|"contributor"\|"read_only"` (not `"owner"\|"admin"\|"member"`). Hard dependency on migration 1.1.	❌	Calls two non-existent functions

Step 4 — Link Accounts Edge Function Changes (Dev 1 solo, Phase 1 blocker for P.5)

These changes must be completed in Phase 1 because they depend on the account_mappings.team_id column added in Step 1.4 and the teams.account_mapping_ids column being dropped in Step 1.1.

#	Story	Status	Notes
4.1	Rewrite `get-accessible-account-mappings` — four fixes: (a) replace `teams.account_mapping_ids[]` array lookup with `account_mappings WHERE team_id = $team_id` (breaks once `account_mapping_ids` is dropped in 1.1); (b) fix teams select: currently does `.select('id, team_id, owner_id, account_mapping_ids')` — both `team_id` (TEXT display name) and `account_mapping_ids` are dropped in migration 1.1, change to `.select('id, name, owner_id')`; (c) drop `_ownerTokens` from response — function tries to read `meta_access_token`, `tiktok_access_token`, `snapchat_access_token` from `account_mappings` (columns don't exist); after task 4.3, deploy functions query `meta_tokens` directly so `_ownerTokens` is not needed; (d) fix `_teamName: teamData.team_id` → `_teamName: teamData.name` (reads the dropped TEXT display column; after the `.select('id, name, owner_id')` fix in bug (b), `teamData.name` is available). Keep returning `_teamOwnerId`.	❌	Hard dependency on 1.1 + 1.4
4.2	`meta-accounts`, `tiktok-accounts`, `snapchat-accounts` edge functions — These functions read `meta_selected_accounts WHERE user_id = userId` to pre-populate checkboxes. Now that selected accounts have `team_id`, they must also accept `teamId` as a request body parameter and scope the query: `.eq("user_id", userId).eq("team_id", teamId)` (or `.is("team_id", null)` for personal). Frontend changes: pass `owner_id` + `teamId` from `TeamContext` when calling these functions. Select pages (`SelectAccounts.tsx`, `SelectTikTokAccounts.tsx`, `SelectSnapchatAccounts.tsx`): change the delete-all pattern from `.eq("user_id", user.id)` to `.eq("user_id", owner_id).eq("team_id", currentTeam.id)` (or `.is("team_id", null)` for personal) — otherwise saving in one workspace wipes selections from all other workspaces.	❌	Edge function + frontend
4.3	Fix deploy functions (`deploy-meta-ads`, `deploy-tiktok-ads`, `deploy-snapchat-ads`) — Two bugs to fix: (a) Token lookup: These functions currently try to read `account_mappings.meta_access_token` — a column that does not exist (not in schema, not in types). Fix: after fetching the account_mapping, do a second query `meta_tokens WHERE user_id = account_mapping.user_id` to get the token. Since team mappings have `user_id = owner_id`, this automatically fetches the owner's token for all team operations. (b) Dual user_id filter: These functions also filter `active_creatives` with `.eq("user_id", user_id)`. In team context `active_creative.user_id = creator` (the member who uploaded it) but `user_id` passed is `owner_id` — never match. Fix: remove the `user_id` filter from `active_creatives`; scope by `account_mapping_id` only.	❌	Bug (a) means deploy is broken for everyone today. Bug (b) would prevent team members from deploying their own creatives.
4.4	`LinkAccounts.tsx` + select-accounts pages — team context awareness: (a) hide OAuth connect buttons for non-owners (`teams.owner_id !== user.id`); (b) pass `owner_id` to account listing edge functions in team mode; (c) "already connected" badge checks `account_mappings WHERE team_id = currentTeam.id` in team mode; (d) new `account_mapping` rows created with `user_id = owner_id, team_id = currentTeam.id`; (e) selected accounts saved to `meta_selected_accounts` / `tiktok_selected_accounts` / `snapchat_selected_accounts` with `team_id = currentTeam.id` in team mode, `team_id = NULL` in personal mode. Depends on 1.9.	❌	Wires frontend to the token ownership model

Phase 1 Delivery Order

1.1–1.10  Schema migrations
    |
    +-- 2.1–2.16  Team creation & invitations
    |
    +-- 3.1–3.11  Team context, switcher & member management
    |
    +-- 4.1–4.4   Link Accounts edge fn changes (depends on 1.1 + 1.4 + 1.9)

Steps 2, 3, and 4 can run in parallel once schema migrations are done. Step 4.1 specifically depends on Steps 1.1 and 1.4 being merged first. Step 4.4 depends on 1.9 (selected accounts team_id).

Epic 1 — Phase 2: Product Module Integration (Dev 1 + Dev 2)

Goal: Every existing product module works correctly when a user is in a team workspace or in their personal workspace. This means:

Data queries read currentTeam from TeamContext and filter by the team's account_mappings when in team mode
Queries fall back to the user's personal account_mappings (user_id filter, team_id IS NULL) when in personal mode
Switching workspaces in the team switcher re-fetches all data for the new context
No broken queries, missing data, or permission errors in either mode

Dev 1 and Dev 2 work in parallel once Phase 1 is merged. Dev 1 owns the ad deployment pipeline and link accounts verification. Dev 2 owns optimize, events, and media library.

#	Module	Files	Owner	Status
P.1	Ad Deployment — Account mapping selector shows team accounts in team mode. Campaigns saved and deployed with correct team-scoped `account_mapping_id`. Note: `active_creatives` user_id filter fix and deploy function token lookup bug are already fixed in Phase 1 task 4.3. This review verifies end-to-end campaign creation and deployment in both personal and team workspace.	`src/pages/CreateCampaign.tsx`, `src/pages/UnifiedCampaignBuilder.tsx`, `deploy-meta-ads`, `deploy-tiktok-ads`, `deploy-snapchat-ads`	Dev 1	❌
P.2	Optimize Campaigns — All metrics queries filter by team's `account_mappings` when in team mode. Workspace switch triggers full data re-fetch.	`src/pages/Optimize.tsx`	Dev 2	❌
P.3	Events Feed — Event data scoped to team's `account_mapping_id`. Event sync edge functions respect team context.	`src/pages/TixrEvents.tsx`	Dev 2	❌
P.4	Media Library — Uploads create `media_files` records with team-scoped `account_mapping_id`. Gallery filters to the active workspace.	`src/pages/MediaLibrary.tsx`	Dev 2	❌
P.5	Link Ad Accounts — Core edge function + backend changes are done in Phase 1 (Steps 4.1–4.4). This Phase 2 review verifies: (1) OAuth connect buttons correctly hidden for non-owners; (2) account listing correctly passes `owner_id` in team mode; (3) new mappings correctly created with `user_id = owner_id, team_id = currentTeam.id`; (4) "already connected" badge is workspace-aware; (5) personal mode behavior is completely unchanged. Integration test both personal and team workspace flows end-to-end.	`src/pages/LinkAccounts.tsx`, `src/pages/SelectAccounts.tsx`	Dev 1	❌

Epic 2: Role-Based Permissions (Both devs, after Epic 1)

Goal: Enforce the role-permission matrix. All infrastructure is in place — Epic 2 is purely additive (upgrade policies, add guards, add UI gates).

#	Story	Owner	Notes
E2.1	Upgrade RLS INSERT/UPDATE/DELETE on 12 downstream tables with role conditions per the permission matrix	Dev 1	Replaces Epic 1 wide-open member policies
E2.2	`_shared/permissions.ts` — shared role-checking utility for edge functions	Dev 1
E2.3	Apply role checks to all team management edge functions (cancel invite, remove member, update role, assign account, etc.)	Dev 1
E2.4	Apply role checks to campaign, audience, and media edge functions	Dev 1
E2.5	`PermissionGate` component + `usePermission` hook (`src/components/PermissionGate.tsx`)	Dev 2
E2.6	Apply `PermissionGate` to all feature routes per the Routes table in this document	Dev 2
E2.7	Hide/show nav items based on role using `usePermission` in `DashboardNavbar`	Dev 2
E2.8	Upgrade `TeamContext.hasPermission()` to check role against the permission matrix	Dev 2

Total: Epic 1 Phase 1 — 10 + 16 + 11 + 4 = 41 tasks (Dev 1 solo) | Epic 1 Phase 2 — 5 module reviews (Dev 1 + Dev 2) | Epic 2 — 8 tasks

Epic 3: Audit & Activity Tracking (Future Phase)

Defined in the product brief as a future phase. Scope TBD, but expected to include:

Log user actions (invitations sent/accepted, campaign launches, media uploads, account links) against team_id + user_id
Activity feed visible to team admins on the team management page
Exportable audit log for compliance/accountability

No implementation work planned yet. Revisit after Epic 2 ships.

Product Brief Coverage Notes

Brief Requirement	Covered In	Notes
User invitations via email	Epic 1, Module A	Admin + Manager can invite (not owner-only)
Permission-based access control	Epic 2	Role matrix gates content, reporting, media, ad execution
Admin role	Epic 1 data model + Epic 2 enforcement
Manager role	Epic 1 data model + Epic 2 enforcement
Contributor role	Epic 1 data model + Epic 2 enforcement
Read-Only role	Epic 1 data model + Epic 2 enforcement
Audit & Activity Tracking	Epic 3 (future)	Placeholder only, no active work
Agency / multi-client support	Epic 1, Module B	Team switcher supports multiple teams per user

Important: Epic 1 delivers collaboration infrastructure with all-members-as-admin access. The product brief's core value proposition — granular role-based control — is fully realized in Epic 2. Epic 1 is a deliberate technical delivery shortcut, not the finished product.

vqc1909a/TEAM_ACCOUNTS_IMPLEMENTATION_PLAN.md

Team Accounts Implementation Plan

Overview

Current Status (as of March 14, 2026)

✅ Done

🔄 Partial (Epic 1, Phase 1)

❌ Not Started

⚠️ Known Issues

Epic 1 Access Model

Key Product Decisions

Data Model

Entity Relationship Diagram

Database Schema & SQL Migrations

1. teams Table (Modify Existing)

2. team_members Table (Modify Existing)

3. team_invitations Table (Modify Existing)

4. account_mappings Table (Add 1 Column)

5. active_audiences Table (Add 1 Column)

RLS (Row Level Security) Policies

account_mappings RLS

teams RLS

team_members RLS

team_invitations RLS

Downstream Tables RLS (12 Tables)

INSERT Policy Role Summary

Role-Permission Matrix

Edge Functions

send-team-invitation

accept-team-invitation (Registered Users)

accept-invite-and-register (Unregistered Users)

decline-team-invitation

cancel-team-invitation

list-team-invitations

resend-team-invitation

create-team

delete-team

update-team

remove-team-member

update-team-member-role

leave-team

get-user-teams

get-team-members

send-invitation-email

Frontend Components

TeamContext (src/context/TeamContext.tsx)

Team Switcher (src/components/DashboardNavbar.tsx)

PermissionGate (src/components/PermissionGate.tsx)

Accept/Decline Invitation Page (/team-invite/:token)

Routes & Permission Gating

Routes to gate in src/App.tsx

Navigation items to hide in DashboardNavbar.tsx

Tables Reference

Tables requiring RLS updates (12 tables)

Tables requiring team_id addition (selected accounts)

Tables that need NO changes

Tables to deprecate (legacy)

Invitation Flows

Flow 1: Invite unregistered user

Flow 2: Invite registered user

Flow 3: Team switching

Flow 4: Create team and connect ad accounts

Jira Roadmap

Audit Results (✅ Completed)

Epic Definitions

Epic 1 — Phase 1: Team Infrastructure (Dev 1 solo)

Step 1 — Schema Migrations (do first, everything else is blocked)

Step 2 — Team Creation & Invitations

Step 3 — Team Context, Switcher & Member Management

Step 4 — Link Accounts Edge Function Changes (Dev 1 solo, Phase 1 blocker for P.5)

Phase 1 Delivery Order

Epic 1 — Phase 2: Product Module Integration (Dev 1 + Dev 2)

Epic 2: Role-Based Permissions (Both devs, after Epic 1)

Epic 3: Audit & Activity Tracking (Future Phase)

Product Brief Coverage Notes

1. `teams` Table (Modify Existing)

2. `team_members` Table (Modify Existing)

3. `team_invitations` Table (Modify Existing)

4. `account_mappings` Table (Add 1 Column)

5. `active_audiences` Table (Add 1 Column)

`account_mappings` RLS

`teams` RLS

`team_members` RLS

`team_invitations` RLS

`send-team-invitation`

`accept-team-invitation` (Registered Users)

`accept-invite-and-register` (Unregistered Users)

`decline-team-invitation`

`cancel-team-invitation`

`list-team-invitations`

`resend-team-invitation`

`create-team`

`delete-team`

`update-team`

`remove-team-member`

`update-team-member-role`

`leave-team`

`get-user-teams`

`get-team-members`

`send-invitation-email`

TeamContext (`src/context/TeamContext.tsx`)

Team Switcher (`src/components/DashboardNavbar.tsx`)

PermissionGate (`src/components/PermissionGate.tsx`)

Accept/Decline Invitation Page (`/team-invite/:token`)

Routes to gate in `src/App.tsx`

Navigation items to hide in `DashboardNavbar.tsx`

Tables requiring `team_id` addition (selected accounts)