supabase-integration

What this skill does

# Supabase Integration for Mem0 OSS

Complete guide for setting up Supabase as the backend for Mem0 Open Source (self-hosted) mode, including PostgreSQL schema with pgvector, RLS policies for security, and production-ready configurations.

## Instructions

### Phase 1: Supabase Project Setup

**Prerequisites Check:**
1. Verify Supabase is initialized:
   ```bash
   bash scripts/verify-supabase-setup.sh
   ```

2. If not initialized, set up Supabase first:
   - Run `/supabase:init` command
   - Note down project ID and connection details
   - Obtain connection string from Supabase dashboard

**Environment Configuration:**
```bash
# Required environment variables
SUPABASE_URL=https://your-project.supabase.co
SUPABASE_ANON_KEY=your-anon-key
SUPABASE_SERVICE_KEY=your-service-key
SUPABASE_DB_URL=postgresql://postgres:[password]@db.[project].supabase.co:5432/postgres

# Optional: Mem0-specific configs
MEM0_EMBEDDING_MODEL=text-embedding-3-small
MEM0_VECTOR_DIMENSION=1536
```

### Phase 2: Enable pgvector Extension

**Enable Extension:**
```bash
bash scripts/setup-mem0-pgvector.sh
```

This script:
1. Connects to Supabase database
2. Enables pgvector extension
3. Verifies extension is active
4. Checks PostgreSQL version compatibility (>= 12)

**Manual Verification:**
```sql
-- Check pgvector is enabled
SELECT * FROM pg_extension WHERE extname = 'vector';

-- Test vector operations
SELECT '[1,2,3]'::vector;
```

### Phase 3: Create Memory Tables Schema

**Apply Memory Schema:**
```bash
bash scripts/apply-mem0-schema.sh
```

This creates three core tables:

**1. memories table** (vector storage):
- `id` (uuid, primary key)
- `user_id` (text, indexed) - User identifier for isolation
- `agent_id` (text, indexed, nullable) - Agent identifier
- `run_id` (text, indexed, nullable) - Session/conversation identifier
- `memory` (text) - Memory content
- `hash` (text) - Content hash for deduplication
- `metadata` (jsonb) - Flexible metadata storage
- `categories` (text[]) - Memory categorization
- `embedding` (vector(1536)) - Semantic embedding
- `created_at` (timestamptz)
- `updated_at` (timestamptz)

**2. memory_relationships table** (graph memory):
- `id` (uuid, primary key)
- `source_memory_id` (uuid, foreign key)
- `target_memory_id` (uuid, foreign key)
- `relationship_type` (text) - e.g., "references", "caused_by", "related_to"
- `strength` (numeric) - Relationship strength (0.0-1.0)
- `metadata` (jsonb)
- `user_id` (text, indexed) - For RLS isolation
- `created_at` (timestamptz)

**3. memory_history table** (audit trail):
- `id` (uuid, primary key)
- `memory_id` (uuid)
- `operation` (text) - "create", "update", "delete"
- `old_value` (jsonb)
- `new_value` (jsonb)
- `user_id` (text)
- `timestamp` (timestamptz)

**Use Template for Custom Schema:**
```bash
# Generate schema with custom dimensions
bash scripts/generate-mem0-schema.sh \
  --dimensions 1536 \
  --include-graph true \
  --include-history true \
  > custom-mem0-schema.sql

# Apply custom schema
psql $SUPABASE_DB_URL < custom-mem0-schema.sql
```

### Phase 4: Create Performance Indexes

**Apply Optimized Indexes:**
```bash
bash scripts/create-mem0-indexes.sh
```

**Index Strategy:**

1. **Vector Search Indexes (HNSW)**:
   ```sql
   -- Main embedding index (cosine distance)
   CREATE INDEX idx_memories_embedding ON memories
   USING hnsw (embedding vector_cosine_ops)
   WITH (m = 16, ef_construction = 64);
   ```

2. **User/Agent Isolation Indexes**:
   ```sql
   CREATE INDEX idx_memories_user_id ON memories(user_id);
   CREATE INDEX idx_memories_agent_id ON memories(agent_id);
   CREATE INDEX idx_memories_run_id ON memories(run_id);
   ```

3. **Composite Indexes for Common Queries**:
   ```sql
   -- User + timestamp for chronological retrieval
   CREATE INDEX idx_memories_user_created ON memories(user_id, created_at DESC);

   -- User + agent for agent-specific memories
   CREATE INDEX idx_memories_user_agent ON memories(user_id, agent_id);
   ```

4. **Graph Relationship Indexes**:
   ```sql
   CREATE INDEX idx_relationships_source ON memory_relationships(source_memory_id);
   CREATE INDEX idx_relationships_target ON memory_relationships(target_memory_id);
   CREATE INDEX idx_relationships_type ON memory_relationships(relationship_type);
   ```

5. **Full-Text Search Index (optional)**:
   ```sql
   CREATE INDEX idx_memories_content_fts ON memories
   USING gin(to_tsvector('english', memory));
   ```

**Index Selection Guide:**
- Small dataset (< 100K memories): Start with basic indexes
- Medium dataset (100K-1M): Add HNSW with m=16
- Large dataset (> 1M): Use HNSW with m=32, consider IVFFlat
- Write-heavy workload: Consider IVFFlat over HNSW

### Phase 5: Implement Row Level Security (RLS)

**Apply RLS Policies:**
```bash
bash scripts/apply-mem0-rls.sh
```

**Security Patterns:**

**1. User Isolation (Default)**:
```sql
-- Users can only access their own memories
CREATE POLICY "Users access own memories"
ON memories FOR ALL
USING (auth.uid()::text = user_id);

-- Users can only see their own relationships
CREATE POLICY "Users access own relationships"
ON memory_relationships FOR ALL
USING (auth.uid()::text = user_id);
```

**2. Multi-Tenant Isolation (Enterprise)**:
```sql
-- Check organization membership
CREATE POLICY "Organization members access memories"
ON memories FOR ALL
USING (
  EXISTS (
    SELECT 1 FROM org_members
    WHERE org_members.user_id = auth.uid()::text
    AND org_members.org_id = memories.metadata->>'org_id'
  )
);
```

**3. Agent-Specific Policies**:
```sql
-- Public agent memories (shared across users)
CREATE POLICY "Agent memories readable by all"
ON memories FOR SELECT
USING (agent_id IS NOT NULL AND user_id IS NULL);

-- Agent can write to their own memory space
CREATE POLICY "Agent writes own memories"
ON memories FOR INSERT
WITH CHECK (agent_id = current_setting('app.agent_id', true));
```

**Test RLS Enforcement:**
```bash
bash scripts/test-mem0-rls.sh --user-id "test-user-123"
```

### Phase 6: Configure Connection Pooling

**Setup PgBouncer (Recommended for Production):**
```bash
bash scripts/configure-connection-pool.sh
```

**Connection Pooling Strategy:**

**For Mem0 OSS:**
- Transaction mode (default): Each memory operation gets fresh connection
- Session mode: Use for graph traversals requiring multiple queries
- Pool size: Start with 20 connections, scale based on load

**Configuration:**
```python
# Mem0 with connection pooling
from mem0 import Memory

config = {
    "vector_store": {
        "provider": "postgres"
        "config": {
            "url": "postgresql://user:[email protected]:6543/postgres"
            "pool_size": 20
            "max_overflow": 10
            "pool_timeout": 30
            "pool_recycle": 3600
        }
    }
}

memory = Memory.from_config(config)
```

**Supabase Pooler URLs:**
- Transaction mode: `pooler.project.supabase.co:6543`
- Session mode: `pooler.project.supabase.co:5432`

### Phase 7: Implement Backup Strategy

**Setup Automated Backups:**
```bash
bash scripts/setup-mem0-backup.sh --schedule daily --retention 30
```

**Backup Strategies:**

**1. Point-in-Time Recovery (Supabase Built-in)**:
- Automatic backups (Pro plan and above)
- Restore to any point in last 7-30 days
- No manual configuration needed

**2. Manual SQL Dumps**:
```bash
# Full database backup
bash scripts/backup-mem0-memories.sh

# Incremental backup (changes since last backup)
bash scripts/backup-mem0-memories.sh --incremental --since "2025-10-01"

# Backup to S3
bash scripts/backup-mem0-memories.sh --destination s3://my-bucket/mem0-backups/
```

**3. Selective Backups**:
```bash
# Backup specific user's memories
bash scripts/backup-user-memories.sh --user-id "customer-123"

# Backup by date range
bash scripts/backup-mem0-memories.sh --from "2025-01-01" --to "2025-10-27"
```

**Restore Procedures:**
```bash
# Restore from backup file
bash scripts/restore-mem0-backup.sh backup-2025-10-27.sql

# Restore specific user
bash scripts/restore-u