mkl/my-fullstack-ai-platform
1
0
Fork 0
Code Issues Pull requests Projects Releases Packages Wiki Activity Actions
my-fullstack-ai-platform/.agents/plans/database-schema-and-rls.md

9.8 KiB
Raw Permalink Blame History

Feature: Database Schema and RLS

The following plan defines the core data foundation for the Teklifsat platform, including campaigns, leads, and contact management with strict multi-tenant isolation via Supabase Row-Level Security (RLS).

Feature Description

This feature implements the first milestone of the Phase 1 MVP: setting up the relational database schema and security policies. It extends the existing organizations table and adds new tables for campaigns, leads, and their interactions, ensuring that all data is strictly scoped to the user's organization.

User Story

As an Owner or SDR, I want a secure and structured database to store my campaigns and leads, So that I can manage my prospecting pipeline without risking data leakage between organizations.

Problem Statement

The platform currently only has basic organization and profile management. It lacks the core entities needed to run campaigns, track leads, and manage LinkedIn contacts as defined in the PRD.

Solution Statement

We will update the organizations table to include ICP and quota fields, and create four new tables (campaigns, leads, campaign_leads, lead_contacts) with appropriate foreign keys and unique constraints. We will then apply RLS policies to each table using a consistent pattern based on the user's organization_id.

Feature Metadata

Feature Type: New Capability / Infrastructure Estimated Complexity: Medium Primary Systems Affected: Supabase Database (Schema, RLS) Dependencies: Supabase

CONTEXT REFERENCES

Relevant Codebase Files IMPORTANT: YOU MUST READ THESE FILES BEFORE IMPLEMENTING!

  • public.organizations (Existing table) - Why: Main tenant entity to be updated.
  • public.profiles - Why: Contains organization_id used for RLS.
  • lib/supabase/server.ts - Why: Pattern for interacting with Supabase from the server.
  • app/dashboard/settings/team/actions.ts (lines 12-42) - Why: Shows how organization_id is retrieved from the profile.

Relevant Documentation YOU SHOULD READ THESE BEFORE IMPLEMENTING!

Patterns to Follow

Multi-Tenant Isolation: All tenant-scoped tables must include a reference to organization_id (or link to it via a parent table) and have RLS policies that validate auth.uid() against the profiles table.

Naming Conventions:

  • Table names: snake_case (plural).
  • Column names: snake_case.
  • Policy names: tenant_can_<action>_<table_name>.

IMPLEMENTATION PLAN

Phase 1: Schema Updates & Foundation

Updating existing structures and creating the core campaign/lead hierarchy.

Tasks:

  • Add ICP, profile, and quota fields to the organizations table.
  • Create the campaigns table with multi-tenant scoping and the specified unique constraint.
  • Create the global leads table for company deduplication.
  • Create the joining campaign_leads table for campaign-specific lead data.
  • Create the lead_contacts table.

Phase 2: RLS Policy Implementation

Enforcing isolation at the database level.

Tasks:

  • Enable RLS on all new tables.
  • Implement reusable helper functions for organization-based filtering (optional but recommended).
  • Write SELECT, INSERT, UPDATE, and DELETE policies for each table.

STEP-BY-STEP TASKS

UPDATE public.organizations

  • IMPLEMENT: Add the following columns to the organizations table:
    • icp: jsonb, default {}
    • company_profile: jsonb, default {}
    • monthly_quota: int, default 0
    • current_leads_month: int, default 0
    • quota_reset_date: timestamptz, default now()
  • VALIDATE: mcp_supabase_execute_sql to check column existence.

CREATE public.campaigns

  • IMPLEMENT: Create the campaigns table:
    • id: uuid (PK), default gen_random_uuid()
    • organization_id: uuid (FK -> organizations.id), NOT NULL
    • search_account_id: uuid (FK -> auth.users.id), NOT NULL
    • name: text, NOT NULL
    • industries: jsonb, default [] (Array of Unipile Industry IDs)
    • locations: jsonb, default [] (Array of Unipile Location IDs)
    • headcount: jsonb, default [] (Array of {min, max} objects matching Unipile API)
    • target_leads: int, NOT NULL
    • current_leads: int, default 0
    • search_cursor: text
    • status: text, default 'pending' (Check constraint: pending, running, paused, pipeline_completed, quota_reached)
    • created_at: timestamptz, default now()
  • INDEX: CREATE UNIQUE INDEX campaigns_name_org_unique ON campaigns (organization_id, name);
  • VALIDATE (Server-Side): Implementation must include a check to find existing campaigns with identical industries + locations + headcount within the same organization_id before creating a new one.

CREATE public.leads (Companies)

  • IMPLEMENT: Create the leads table (Global deduplication & Enrichment Cache):
    • id: uuid (PK), default gen_random_uuid()
    • linkedin_url: text, UNIQUE, NOT NULL
    • name: text, NOT NULL
    • domain: text
    • enriched_data: jsonb, default {} (Global shared company info)
    • last_enriched_at: timestamptz
    • created_at: timestamptz, default now()
  • VALIDATE: mcp_supabase_list_tables for table structure.

CREATE public.campaign_leads

  • IMPLEMENT: Create the campaign_leads junction table:
    • id: uuid (PK), default gen_random_uuid()
    • campaign_id: uuid (FK -> campaigns.id), CASCADE DELETE
    • lead_id: uuid (FK -> leads.id), CASCADE DELETE
    • organization_id: uuid (FK -> organizations.id), NOT NULL
    • status: text, default 'pending' (Check: pending, qualified, disqualified)
    • qualification_data: jsonb, default {} (Tenant-specific matching reason)
    • outreach_template: jsonb, default {} (Tenant-specific pitch)
    • created_at: timestamptz, default now()
  • INDEX: CREATE UNIQUE INDEX campaign_leads_unique ON campaign_leads (campaign_id, lead_id);
  • VALIDATE: mcp_supabase_list_tables.

CREATE public.lead_contacts

  • IMPLEMENT: Create the lead_contacts table (Tenant-scoped interaction):
    • id: uuid (PK), default gen_random_uuid()
    • lead_id: uuid (FK -> leads.id), CASCADE DELETE
    • organization_id: uuid (FK -> organizations.id), NOT NULL
    • provider_id: text (Unipile ID for messaging/invites)
    • member_urn: text (Global LinkedIn ID for deduplication)
    • public_id: text (Vanity ID from URL)
    • name: text, NOT NULL
    • first_name: text
    • last_name: text
    • headline: text
    • linkedin_url: text, NOT NULL
    • connection_status: text, default 'none' (Check: none, pending, retry_pending, accepted, no_contact)
    • claimed_by_id: uuid (FK -> auth.users.id), NULLABLE (The SDR currently engaging this contact)
    • created_at: timestamptz, default now()
  • VALIDATE: mcp_supabase_list_tables.

IMPLEMENT RLS POLICIES

  • IMPLEMENT: Apply RLS to all tables.
  • PATTERN: Each policy check should follow this logic: 
    (SELECT organization_id FROM profiles WHERE id = auth.uid()) = organization_id
  • POLICIES:
    • organizations: Owners and members can view. Owners can update.
    • campaigns: Members of the same organization can view, insert, and update.
    • leads: Anyone authenticated can view/insert (global company profiles).
    • campaign_leads: Scoped via campaign_id -> campaigns.organization_id OR by adding organization_id to the table for performance. Let's add organization_id to campaign_leads as well for easier RLS.
    • lead_contacts: Scoped via organization_id.
  • VALIDATE: mcp_supabase_execute_sql query against pg_policies.

TESTING STRATEGY

Unit Tests

  • Not applicable for raw SQL schema, but can be validated via manual SQL inserts.

Integration Tests (Manual)

  1. Multi-Tenant Check:
    • High-level test: Create two organizations (Org A, Org B).
    • Insert a campaign for Org A.
    • Verify that User B (in Org B) cannot see or update Campaign A.
  2. Quota Barrier:
    • Verify that monthly_quota and current_leads_month are updated and respected (to be tested more thoroughly in the pipeline phase).

VALIDATION COMMANDS

Level 1: Schema Check

SELECT table_name, column_name, data_type
FROM information_schema.columns
WHERE table_schema = 'public'
AND table_name IN ('organizations', 'campaigns', 'leads', 'campaign_leads', 'lead_contacts');

Level 2: RLS Check

SELECT tablename, policyname, cmd, qual
FROM pg_policies
WHERE schemaname = 'public';

ACCEPTANCE CRITERIA

  • All tables defined in Step-by-Step are created with correct types and keys.
  • organizations table successfully extended with new fields.
  • RLS is enabled on campaigns, campaign_leads, and lead_contacts.
  • Users can only access data belonging to their organization.
  • Unique constraint for industry + country per tenant is enforced.
  • Foreign key relationships are correctly established with cascade deletes where appropriate.

NOTES

  • We are using the existing organizations table as the tenant anchor.
  • The leads table is intentionally global to allow for company deduplication across the whole platform, but company-specific results are stored in the tenant-scoped campaign_leads table.