{"slug":"operations/DESUPABASE_MIGRATION_PLAN","title":"De-Supabase Migration Plan","section":"operations","sectionLabel":"Operations","status":"unclassified","type":"document","path":"operations/DESUPABASE_MIGRATION_PLAN.mdx","bytes":10425,"description":null,"updated":null,"source":"integratewise/Integratewise-docs-wiki","content":"\n**Status:** DRAFT  \n**Created:** 2026-06-18  \n**Authority:** DECISION 22 (100% Cloudflare Product Plane)  \n**Scope:** Active Supabase consumers in integratewise-live\n\n---\n\n## Executive Summary\n\nTwo services in `services/knowledge/` remain active Supabase consumers with direct PostgREST calls. All other services have either completed migration to D1 or have dead Supabase code paths. This plan outlines the migration strategy for these two remaining consumers.\n\n---\n\n## Current State Assessment\n\n### Migration Status by Service\n\n| Service                                         | Supabase Usage                     | Status              | Action Required                     |\n| ----------------------------------------------- | ---------------------------------- | ------------------- | ----------------------------------- |\n| `services/pipeline/`                            | Env vars declared, no active calls | ✅ COMPLETE         | Remove dead env vars                |\n| `services/continuity/`                          | D1 shim (misleadingly named)       | ✅ COMPLETE         | Rename `supabase.ts` → `d1-shim.ts` |\n| `services/gateway/`                             | Env var in interface only          | ✅ COMPLETE         | Remove dead env var                 |\n| `services/intelligence/`                        | No Supabase usage                  | ✅ COMPLETE         | None                                |\n| `services/normalizer/`                          | No Supabase usage                  | ✅ COMPLETE         | None                                |\n| `services/knowledge/src/iq-hub.ts`              | **ACTIVE PostgREST calls**         | 🔴 MIGRATION NEEDED | Rewrite to D1                       |\n| `services/knowledge/src/memory-consolidator.ts` | **ACTIVE PostgREST calls**         | 🔴 MIGRATION NEEDED | Rewrite to D1                       |\n\n### Tables Affected\n\n#### iq-hub.ts (PRIMARY TARGET)\n\n- `ai_agents` (reads/writes)\n- `ai_actions` (reads/writes)\n- `ai_action_proposals` (reads/writes)\n- `ai_situations` (reads)\n\n#### memory-consolidator.ts (SECONDARY TARGET)\n\n- `consolidated_memories` (reads/writes)\n- `session_memories` (reads)\n\n---\n\n## Target State\n\nAll data operations route through Cloudflare D1 via the `DB` binding (already declared in both services). Supabase env vars (`SUPABASE_URL`, `SUPABASE_SERVICE_ROLE_KEY`, `SUPABASE_SERVICE_KEY`) are removed from bindings and `.env` files.\n\n---\n\n## Migration Plan\n\n### Phase 1: iq-hub.ts Migration\n\n**Risk:** HIGH — Active agent/action CRUD operations  \n**Complexity:** MEDIUM — Multiple tables, mixed read/write patterns\n\n#### Current Supabase Operations\n\n1. **Agent CRUD** (lines 159-161, 427-429)\n   - `supabaseQuery()` on `ai_agents` table\n   - `supabaseMutate()` for INSERT/UPDATE on `ai_agents`\n\n2. **Action CRUD** (lines 302-304, 334-336)\n   - `supabaseQuery()` on `ai_actions` table\n   - `supabaseMutate()` for INSERT on `ai_actions`\n\n3. **Situation Reads** (lines 244-246)\n   - `supabaseQuery()` on `ai_situations` table\n\n4. **Proposal Operations** (lines 389-391)\n   - `supabaseQuery()` on `ai_action_proposals` table\n\n5. **DATABASE_URL Construction** (lines 598-599, 872-873)\n   - Constructs Postgres connection string from Supabase credentials\n   - Used for downstream service calls (should be migrated to D1 service binding)\n\n#### Migration Steps\n\n1. **Create D1 helper functions** (replace `supabaseQuery`/`supabaseMutate`)\n\n   ```typescript\n   async function d1Query<T>(\n     db: D1Database,\n     table: string,\n     conditions: Record<string, any>,\n     options?: { limit?: number; orderBy?: string }\n   ): Promise<T[]>;\n\n   async function d1Mutate(\n     db: D1Database,\n     table: string,\n     operation: \"insert\" | \"update\" | \"upsert\",\n     data: Record<string, any>,\n     conditions?: Record<string, any>\n   ): Promise<any>;\n   ```\n\n2. **Migrate each endpoint** (incremental, test after each)\n   - Start with read-only endpoints (`ai_situations`, `ai_action_proposals`)\n   - Migrate agent reads, then agent writes\n   - Migrate action reads, then action writes\n   - Remove `DATABASE_URL` construction (use D1 binding directly)\n\n3. **Update bindings**\n   - Remove `SUPABASE_URL` and `SUPABASE_SERVICE_ROLE_KEY` from `Bindings` type\n   - Ensure `DB: D1Database` is properly configured in `wrangler.toml`\n\n4. **Update environment files**\n   - Remove Supabase vars from `.env`, `.dev.vars`, `wrangler.toml`\n\n#### Testing Strategy\n\n- Unit tests for D1 helper functions\n- Integration tests for each endpoint (agent CRUD, action CRUD)\n- Verify tenant isolation (`tenant_id` filtering)\n- Load test agent/action queries\n\n---\n\n### Phase 2: memory-consolidator.ts Migration\n\n**Risk:** MEDIUM — Read-heavy, write-once pattern  \n**Complexity:** LOW — Simple query patterns\n\n#### Current Supabase Operations\n\n1. **Fetch Weekly Consolidated Memories** (lines 648-653)\n   - `supabaseQuery()` on `consolidated_memories`\n   - Filter: `tenant_id`, `created_at` range, `consolidation_type`\n   - Order: `created_at.desc`\n\n2. **Store Consolidated Memory** (lines 888-909)\n   - `supabaseMutate()` POST to `consolidated_memories`\n   - Insert new consolidated memory record\n\n3. **Fetch Recent Sessions** (referenced, line 648)\n   - Calls `iq-hub` service via HTTP (will be migrated in Phase 1)\n\n#### Migration Steps\n\n1. **Replace Supabase helpers with D1 equivalents**\n\n   ```typescript\n   // Before\n   const memories = await supabaseQuery(\n     env.SUPABASE_URL,\n     env.SUPABASE_SERVICE_KEY,\n     \"consolidated_memories\",\n     `tenant_id=eq.${tenantId}...`\n   );\n\n   // After\n   const memories = await env.DB.prepare(\n     \"SELECT * FROM consolidated_memories WHERE tenant_id = ? AND created_at >= ? AND created_at <= ? ORDER BY created_at DESC\"\n   )\n     .bind(tenantId, startTime, endTime)\n     .all();\n   ```\n\n2. **Update `storeConsolidatedMemory()`**\n   - Replace `supabaseMutate()` with `env.DB.prepare().bind().run()`\n\n3. **Remove Supabase env vars**\n   - Remove `SUPABASE_URL` and `SUPABASE_SERVICE_KEY` from `Env` interface\n\n4. **Update wrangler.toml**\n   - Ensure `DB` binding points to correct D1 database\n\n#### Testing Strategy\n\n- Unit tests for consolidation logic\n- Integration tests for hourly/daily/weekly triggers\n- Verify D1 writes for `consolidated_memories`\n- Test cron schedule execution\n\n---\n\n### Phase 3: Cleanup\n\n1. **Remove dead code**\n   - Delete `supabaseQuery()` and `supabaseMutate()` helper functions from both files\n   - Remove Supabase env vars from all `.env`, `.dev.vars`, `wrangler.toml` files\n\n2. **Rename misleading file**\n   - Rename `services/continuity/src/supabase.ts` → `d1-shim.ts`\n   - Update import in `services/continuity/src/index.ts`\n\n3. **Update documentation**\n   - Remove Supabase references from AGENTS.md\n   - Update architecture diagrams\n\n---\n\n## Risk Assessment\n\n| Risk                       | Likelihood | Impact | Mitigation                                    |\n| -------------------------- | ---------- | ------ | --------------------------------------------- |\n| Data loss during migration | LOW        | HIGH   | D1 transaction support, incremental migration |\n| D1 performance regression  | LOW        | MEDIUM | Benchmark queries, add indexes if needed      |\n| Tenant isolation breach    | LOW        | HIGH   | Test `tenant_id` filtering on all queries     |\n| Service downtime           | LOW        | HIGH   | Blue-green deployment, feature flags          |\n\n---\n\n## Dependencies\n\n- D1 database schema must include tables: `ai_agents`, `ai_actions`, `ai_action_proposals`, `ai_situations`, `consolidated_memories`\n- D1 bindings must be configured in `wrangler.toml` for knowledge service\n- Existing D1 indexes must support query patterns (tenant_id, created_at, consolidation_type)\n\n---\n\n## Timeline\n\n| Phase                           | Duration     | Owner | Status      |\n| ------------------------------- | ------------ | ----- | ----------- |\n| Phase 1: iq-hub.ts              | 2-3 days     | TBD   | NOT STARTED |\n| Phase 2: memory-consolidator.ts | 1-2 days     | TBD   | NOT STARTED |\n| Phase 3: Cleanup                | 1 day        | TBD   | NOT STARTED |\n| **Total**                       | **4-6 days** |       |             |\n\n---\n\n## Acceptance Criteria\n\n- [ ] All Supabase PostgREST calls replaced with D1 queries\n- [ ] All Supabase env vars removed from bindings and environment files\n- [ ] All endpoints pass integration tests\n- [ ] Tenant isolation verified on all queries\n- [ ] Performance benchmarks meet or exceed Supabase baseline\n- [ ] Documentation updated to reflect Cloudflare-only architecture\n\n---\n\n## Appendix: D1 Schema Requirements\n\nEnsure these tables exist in the target D1 database:\n\n```sql\n-- ai_agents\nCREATE TABLE IF NOT EXISTS ai_agents (\n  id TEXT PRIMARY KEY,\n  tenant_id TEXT NOT NULL,\n  name TEXT NOT NULL,\n  description TEXT,\n  config JSONB DEFAULT '{}',\n  status TEXT DEFAULT 'active',\n  created_at TEXT DEFAULT (datetime('now')),\n  updated_at TEXT DEFAULT (datetime('now'))\n);\n\n-- ai_actions\nCREATE TABLE IF NOT EXISTS ai_actions (\n  id TEXT PRIMARY KEY,\n  tenant_id TEXT NOT NULL,\n  agent_id TEXT NOT NULL,\n  name TEXT NOT NULL,\n  action_type TEXT NOT NULL,\n  config JSONB DEFAULT '{}',\n  status TEXT DEFAULT 'active',\n  created_at TEXT DEFAULT (datetime('now'))\n);\n\n-- ai_action_proposals\nCREATE TABLE IF NOT EXISTS ai_action_proposals (\n  id TEXT PRIMARY KEY,\n  tenant_id TEXT NOT NULL,\n  action_id TEXT NOT NULL,\n  proposal JSONB NOT NULL,\n  status TEXT DEFAULT 'pending',\n  created_at TEXT DEFAULT (datetime('now'))\n);\n\n-- ai_situations\nCREATE TABLE IF NOT EXISTS ai_situations (\n  id TEXT PRIMARY KEY,\n  tenant_id TEXT NOT NULL,\n  situation_type TEXT NOT NULL,\n  context JSONB NOT NULL,\n  severity TEXT DEFAULT 'medium',\n  status TEXT DEFAULT 'open',\n  created_at TEXT DEFAULT (datetime('now'))\n);\n\n-- consolidated_memories\nCREATE TABLE IF NOT EXISTS consolidated_memories (\n  id TEXT PRIMARY KEY,\n  tenant_id TEXT NOT NULL,\n  consolidation_type TEXT NOT NULL,\n  topic TEXT,\n  user_id TEXT,\n  account_id TEXT,\n  summary TEXT NOT NULL,\n  key_insights JSONB DEFAULT '[]',\n  recurring_themes JSONB DEFAULT '[]',\n  action_patterns JSONB DEFAULT '[]',\n  session_count INTEGER DEFAULT 0,\n  time_range_start TEXT NOT NULL,\n  time_range_end TEXT NOT NULL,\n  created_at TEXT DEFAULT (datetime('now'))\n);\n```\n\n---\n\n_This document is a living plan. Update as migration progresses._\n"}