{"slug":"business/SCHEMA_ENHANCEMENT","title":"IntegrateWise Dual-Write Memory Substrate (D1 + Supabase + Vault)","section":"business","sectionLabel":"Business","status":"unclassified","type":"document","path":"business/SCHEMA_ENHANCEMENT.mdx","bytes":5235,"description":null,"updated":null,"source":"integratewise/Integratewise-docs-wiki","content":"\nThis document specifies the hybrid three-tier database substrate used by IntegrateWise under Architecture v3.0. It maps the Edge Cache (Cloudflare D1/KV), the permanent relational database of record (Supabase + pgvector), and the persistent offline Markdown Vault.\n\n---\n\n## 1. High-Level Substrate Topography\n\nTo satisfy both **Law 6 (Memory)** and edge-local performance requirements, IntegrateWise operates a decoupled read-write memory substrate:\n\n```\n                  ┌──────────────────────────────────────────────┐\n                  │                 Active Worker                │\n                  └──────────────────────┬───────────────────────┘\n                                         │\n                   Reads Edge-Locally    │ Writes Memory Staging\n                  ┌──────────────────────┼───────────────────────┐\n                  ▼                      ▼                       ▼\n            ┌──────────┐           ┌──────────┐            ┌───────────┐\n            │    KV    │           │    D1    │            │ PIPELINE  │\n            │ (Hot CA) │           │ (SQLite) │            │ (Service) │\n            └──────────┘           └──────────┘            └─────┬─────┘\n                                                                 │\n                                                   Dual-Writes   │\n                                                   Simultaneously│\n                                                                 ▼\n                                                  ┌──────────────┴──────────────┐\n                                                  │                             │\n                                                  ▼                             ▼\n                                          ┌──────────────┐              ┌──────────────┐\n                                          │ Supabase DB  │              │ Filesystem   │\n                                          │ (pgvector)   │              │ Vault (md)   │\n                                          └──────────────┘              └──────────────┘\n```\n\n---\n\n## 2. Tier Details & Responsibilities\n\n### Tier 1: Cloudflare Edge (D1 & KV Cache)\n\n- **Role**: Read-only Edge Cache and signal-detection buffer.\n- **Technologies**:\n  - **Cloudflare D1**: Stores local mirrors of `agent_registry`, `entity360_cache`, `ai_sessions`, and resolved configuration matrices.\n  - **Cloudflare KV**: Hot namespace caching for `SCHEMA_CACHE`, `CONNECTOR_STATUS`, and rate-limiting keys with 60-300s TTLs.\n- **Rule**: AI reasoning loops (Twin, specialized agents) query D1/KV local views to construct morning context briefs in `< 200ms`.\n\n### Tier 2: Supabase (Fortress DB of Record)\n\n- **Role**: Permanent, normalized, relational source of truth (SSOT).\n- **Technologies**: PostgreSQL database with `pgvector` index tracking.\n- **Tables**: `entities`, `consolidated_memories`, `actions`, `connectors`, `governance_audit_log`.\n- **Rule**: Direct access is strictly banned for edge reasoning agents. Writes are dispatched via the `env.PIPELINE` `/api/v1/db-proxy` endpoint.\n\n### Tier 3: Physical Vault (Markdown Storage)\n\n- **Role**: Permanent offline corporate memory backup.\n- **Format**: Clean GitHub-flavored markdown files stored locally at `/org-memory/` under corresponding entity directory trees.\n- **Rule**: Human-approved decisions or promoted learnings must write here during the promotion transaction.\n\n---\n\n## 3. The Dual-Write Promotion Loop\n\nWhen the Triage Bot or human operator approves and promotes a memory summary, the following synchronization chain is executed in a single atomic pipeline transaction:\n\n```mermaid\nsequenceDiagram\n    participant Agent as Twin / User Interface\n    participant Pip as PIPELINE (db-proxy)\n    participant Sub as Supabase (Postgres)\n    participant Vlt as Markdown Vault (FS)\n    participant D1 as D1 Cache (Edge)\n\n    Agent->>Pip: POST /v1/spine/memory (approve)\n    activate Pip\n    Pip->>Sub: INSERT INTO public.consolidated_memories\n    Pip->>Vlt: Write /org-memory/{entity_id}.md\n    Pip->>D1: INSERT OR REPLACE INTO memory_cache\n    Pip->>Agent: Return success (trace_id)\n    deactivate Pip\n```\n\n### Idempotency Check (Stage 7.5 Validation)\n\nTo prevent duplicates, the normalizer checks the content hash fingerprint in D1 before starting any database transaction:\n$$\\text{SHA-256}(\\text{tenant\\_id} + \\text{entity\\_type} + \\text{source} + \\text{content\\_hash})$$\nIf the fingerprint is found in D1, the write is skipped.\n"}