{"slug":"strategy/product-strategy/v2-data-model","title":"Data Model and Entity Relationships — IntegrateWise Continuity Bridge v2.0","section":"strategy","sectionLabel":"Strategy","status":"unclassified","type":"long-form","path":"strategy/product-strategy/v2-data-model.mdx","bytes":137877,"description":null,"updated":null,"source":"integratewise/Integratewise-docs-wiki","content":"\n> **Status: SUPERSEDED DATA-MODEL DEEP-DIVE.** D1 as the primary relational\n> store, per-domain schemas and any older memory/promotion rules below are not\n> current. Use\n> [`CANONICAL_SPINE_SCHEMA_FOUNDATION.md`](./CANONICAL_SPINE_SCHEMA_FOUNDATION.md)\n> and\n> [`TENANT_SPINE_FORTRESS_OPERATIONAL_FLOW_MODEL.md`](./TENANT_SPINE_FORTRESS_OPERATIONAL_FLOW_MODEL.md).\n\n**Domain:** Data Model, Entity Relationships, Spine Schema, Memory Schema, Tenant Schema, Governance Schema, D1 Table Definitions \n**Version:** 2.0.0 \n**Date:** 2026-07-02 \n**Status:** STAGE 1/2 DEEP-DIVE — Feeds Canonical Architecture Document \n**Platform Layer:** Continuity Kernel (Layers 4–6) \n**Data Plane:** D1 (Cloudflare) primary relational store; KV (cache/session); Vectorize (embeddings); R2 (cold audit) \n**Authoring Principle:** Pipeline is the only writer. Every query carries `WHERE tenant_id = ?`. Audit write failure halts the operation.\n\n---\n\n## Table of Contents\n\n1. [Overview & Design Philosophy](#1-overview--design-philosophy)\n2. [Spine Schema — The Adaptive Spine](#2-spine-schema--the-adaptive-spine)\n3. [Memory Schema — Active / Staging / Archived](#3-memory-schema--active--staging--archived)\n4. [Tenant Schema — Multi-Tenancy & RBAC](#4-tenant-schema--multi-tenancy--rbac)\n5. [Governance Schema — Audit, Proposals, Confidence Gates](#5-governance-schema--audit-proposals-confidence-gates)\n6. [Entity Relationship Diagram](#6-entity-relationship-diagram)\n7. [D1 Table Definitions (Drizzle ORM)](#7-d1-table-definitions-drizzle-orm)\n8. [TypeScript Interfaces & Contracts](#8-typescript-interfaces--contracts)\n9. [Flow Descriptions — Services, Queues, Boundaries](#9-flow-descriptions--services-queues-boundaries)\n10. [Security Boundary Annotations](#10-security-boundary-annotations)\n11. [Integration Points with Adjacent Layers](#11-integration-points-with-adjacent-layers)\n12. [Appendix: SSOC Resolution & Lineage](#12-appendix-ssoc-resolution--lineage)\n\n---\n\n## 1. Overview & Design Philosophy\n\nThe Data Model of the IntegrateWise Continuity Bridge is not a passive storage layer. It is the **living substrate** of organizational context — the Continuity Kernel that preserves identity, memory, governance, and execution state across all tools, humans, and AI agents. The model is organized into four primary schemas, each with a distinct trust boundary, lifecycle, and access pattern.\n\n### The Four Schemas\n\n```\n┌─────────────────────────────────────────────────────────────────────────────┐\n│ CONTINUITY KERNEL — DATA PLANE │\n├─────────────────────────────────────────────────────────────────────────────┤\n│ ┌─────────────┐ ┌─────────────┐ ┌─────────────┐ ┌─────────────────────┐ │\n│ │ SPINE │ │ MEMORY │ │ TENANT │ │ GOVERNANCE │ │\n│ │ (Layer 6) │ │ (Layer 8) │ │ (Layer 0) │ │ (Layer 10) │ │\n│ ├─────────────┤ ├─────────────┤ ├─────────────┤ ├─────────────────────┤ │\n│ │ entities │ │ active │ │ tenants │ │ action_proposals │ │\n│ │ relationships│ │ staging │ │ rbac_roles │ │ governance_audit_log│ │\n│ │ spine_audit │ │ archived │ │ users │ │ approval_tokens │ │\n│ │ entity_types│ │ decay_log │ │ connectors │ │ policy_registry │ │\n│ │ ssoc_index │ │ memory_graph│ │ plans │ │ hitl_sessions │ │\n│ └─────────────┘ └─────────────┘ └─────────────┘ └─────────────────────┘ │\n│ ▲ ▲ ▲ ▲ │\n│ └────────────────┴───────────────┴──────────────────┘ │\n│ SINGLE PIPELINE WRITER │\n│ (pipeline service — only service with D1 INSERT/UPDATE) │\n└─────────────────────────────────────────────────────────────────────────────┘\n```\n\n### Core Invariants\n\n| # | Invariant | Enforcement |\n| --- | --------------------------------------------- | --------------------------------------------------------------------------------------------------------------------------------------------------- |\n| 1 | **Pipeline is the only writer** | All INSERT/UPDATE/DELETE to Spine tables route through `iw-pipeline` service binding. Read replicas may be served by `store`, `l2`, `intelligence`. |\n| 2 | **Every query carries `WHERE tenant_id = ?`** | Drizzle ORM `withTenant()` helper injects tenant filter on every query. Cross-tenant = 403 fail-loud. |\n| 3 | **Audit write failure halts operation** | If `spine_audit_log` or `governance_audit_log` write fails, the triggering operation is rolled back. No silent drops. |\n| 4 | **SSOC is the identity anchor** | Every entity resolves to a Stable Single Object Canonical UUID. Provider-native IDs are foreign keys, not primary keys. |\n| 5 | **Memory is scoped and tiered** | Four scopes (Personal, Work, Organization, AI) × three tiers (active, staging, archived) × decay rules. |\n| 6 | **Governance is schema-native** | Approval states, confidence scores, and HITL sessions are first-class columns — not application-layer concerns. |\n\n---\n\n## 2. Spine Schema — The Adaptive Spine\n\nThe Spine is the canonical context graph. It is not an event log — it is a **living graph of entities, relationships, and their evolution over time**. Every signal from every provider, every action taken by every agent, and every governance decision writes back to the Spine.\n\n### 2.1 Spine Architecture\n\n```\n┌────────────────────────────────────────────────────────────────────────────┐\n│ ADAPTIVE SPINE (Layer 6) │\n│ │\n│ ┌─────────────┐ ┌─────────────┐ ┌─────────────┐ │\n│ │ ENTITY │◄────┤ RELATIONSHIP│────►│ ENTITY │ │\n│ │ TABLE │ │ TABLE │ │ TABLE │ │\n│ │ │ │ │ │ │ │\n│ │ entity_id │ │ rel_id │ │ entity_id │ │\n│ │ ssoc_uuid │◄────┤ source_id │ │ ssoc_uuid │ │\n│ │ tenant_id │ │ target_id │────►│ tenant_id │ │\n│ │ entity_type │ │ rel_type │ │ entity_type │ │\n│ │ provider_id │ │ confidence │ │ provider_id │ │\n│ │ traits (JSON)│ │ metadata │ │ traits (JSON)│ │\n│ │ version │ │ version │ │ version │ │\n│ │ lineage_id │ │ lineage_id │ │ lineage_id │ │\n│ └─────────────┘ └─────────────┘ └─────────────┘ │\n│ ▲ ▲ ▲ │\n│ └───────────────────┴───────────────────┘ │\n│ CONTINUITY GRAPH │\n│ (traversed by intelligence, twin, continuity services) │\n│ │\n│ ┌─────────────┐ ┌─────────────┐ ┌─────────────┐ │\n│ │ ENTITY_TYPE │ │ SSOC_INDEX │ │SPINE_AUDIT │ │\n│ │ TABLE │ │ TABLE │ │ LOG │ │\n│ │ │ │ │ │ │ │\n│ │ type_id │ │ ssoc_uuid │ │ audit_id │ │\n│ │ tenant_id │ │ tenant_id │ │ tenant_id │ │\n│ │ schema_def │ │ provider_ids│ │ entity_id │ │\n│ │ lenses │ │ (JSON map) │ │ action_type │ │\n│ │ trait_rules │ │ canonical │ │ actor │ │\n│ │ │ │ entity_id │ │ timestamp │ │\n│ └─────────────┘ └─────────────┘ │ payload_hash│ │\n│ └─────────────┘ │\n└────────────────────────────────────────────────────────────────────────────┘\n```\n\n### 2.2 Entity Table — The Canonical Object Store\n\nThe `entities` table stores every canonical object in the system. It is **not** provider-specific — a Salesforce Account and a HubSpot Company both resolve to `entity_type = 'organization'` with distinct `provider_id` entries. The SSOC UUID is the cross-provider identity anchor.\n\n| Column | Type | Constraints | Description |\n| ------------------ | --------------------- | ----------------------------------------------- | -------------------------------------------------------------------------------------------------- |\n| `entity_id` | `TEXT` | PRIMARY KEY | CUID2 — internal row identifier |\n| `ssoc_uuid` | `TEXT` | NOT NULL, UNIQUE per tenant | Stable Single Object Canonical UUID — survives provider swaps |\n| `tenant_id` | `TEXT` | NOT NULL, FK → tenants | Hard isolation boundary |\n| `entity_type` | `TEXT` | NOT NULL, FK → entity_types | Canonical type: `organization`, `contact`, `deal`, `task`, `document`, `event`, `metric`, `intent` |\n| `provider` | `TEXT` | NOT NULL | Source provider: `salesforce`, `hubspot`, `slack`, `stripe`, `linear`, `manual` |\n| `provider_id` | `TEXT` | NOT NULL, composite unique with provider+tenant | Native ID in provider system |\n| `display_name` | `TEXT` | | Human-readable label |\n| `traits` | `TEXT` (JSON) | NOT NULL | Normalized key-value traits — see §2.5 |\n| `raw_payload` | `TEXT` (JSON) | | Original provider payload (for lineage/debug) |\n| `confidence_score` | `REAL` | DEFAULT 1.0 | TriageBot confidence at time of creation |\n| `version` | `INTEGER` | DEFAULT 1 | Optimistic locking / MVCC |\n| `lineage_id` | `TEXT` | FK → lineage_records | Provenance — which sync/normalizer run created this |\n| `created_at` | `INTEGER` (timestamp) | | Unix ms |\n| `updated_at` | `INTEGER` (timestamp) | | Unix ms |\n| `archived_at` | `INTEGER` (timestamp) | NULLABLE | Soft delete / archive |\n\n**Composite Indexes:**\n\n- `idx_entities_tenant_type` → `(tenant_id, entity_type)` — projection queries\n- `idx_entities_ssoc` → `(tenant_id, ssoc_uuid)` — cross-provider resolution\n- `idx_entities_provider` → `(tenant_id, provider, provider_id)` — writeback lookups\n- `idx_entities_confidence` → `(tenant_id, confidence_score)` — triage filtering\n- `idx_entities_lineage` → `(tenant_id, lineage_id)` — audit/replay\n\n### 2.3 Relationship Table — The Continuity Graph\n\nThe `relationships` table is the graph edge store. It enables the Continuity Graph traversal that powers Entity360, Twin reasoning, and Context Assembly.\n\n| Column | Type | Constraints | Description |\n| ------------- | ------------- | --------------------------------- | ---------------------------------------------------------------------------------------------------------------- |\n| `rel_id` | `TEXT` | PRIMARY KEY | CUID2 |\n| `tenant_id` | `TEXT` | NOT NULL | Hard isolation |\n| `source_id` | `TEXT` | NOT NULL, FK → entities.ssoc_uuid | Graph edge origin |\n| `target_id` | `TEXT` | NOT NULL, FK → entities.ssoc_uuid | Graph edge destination |\n| `rel_type` | `TEXT` | NOT NULL | `belongs_to`, `manages`, `participated_in`, `authored`, `referenced_by`, `depends_on`, `triggered`, `blocked_by` |\n| `direction` | `TEXT` | DEFAULT 'directed' | `directed`, `bidirectional` |\n| `confidence` | `REAL` | DEFAULT 1.0 | Relationship certainty (0.0–1.0) |\n| `metadata` | `TEXT` (JSON) | | Edge properties: `strength`, `recency`, `source_context` |\n| `valid_from` | `INTEGER` | | Temporal validity start |\n| `valid_until` | `INTEGER` | NULLABLE | Temporal validity end (NULL = permanent) |\n| `version` | `INTEGER` | DEFAULT 1 | MVCC |\n| `lineage_id` | `TEXT` | FK → lineage_records | Provenance |\n| `created_at` | `INTEGER` | | Unix ms |\n\n**Composite Indexes:**\n\n- `idx_rels_tenant_source` → `(tenant_id, source_id, rel_type)` — outbound traversal\n- `idx_rels_tenant_target` → `(tenant_id, target_id, rel_type)` — inbound traversal\n- `idx_rels_confidence` → `(tenant_id, confidence)` — weak link pruning\n- `idx_rels_temporal` → `(tenant_id, valid_from, valid_until)` — time-travel queries\n\n### 2.4 Entity Type Registry\n\nThe `entity_types` table defines the canonical type system. Types are tenant-extensible — each tenant may define custom types, but the platform provides a core taxonomy.\n\n**Core Type Taxonomy (Platform-Provided):**\n\n| Type | Description | Default Traits | Typical Providers |\n| -------------- | -------------------- | --------------------------------------------------- | -------------------------------- |\n| `organization` | Company/Account | name, domain, industry, size, revenue, health_score | Salesforce, HubSpot, Stripe |\n| `contact` | Person | email, phone, title, department, timezone | Salesforce, HubSpot, Slack |\n| `deal` | Opportunity/Pipeline | amount, stage, probability, close_date, arr | Salesforce, HubSpot, Stripe |\n| `task` | Action item | title, status, due_date, priority, assignee | Salesforce, Linear, Asana |\n| `document` | File/Content | title, mime_type, url, extracted_text | Notion, Google Drive, Confluence |\n| `event` | Calendar/Log | start_time, end_time, attendees, outcome | Google Calendar, Salesforce |\n| `metric` | KPI/Measurement | value, unit, period, target, trend | Stripe, Mixpanel, Custom |\n| `intent` | User/AI Intent | action, target_entity, urgency, context | Twin, MCP, Manual |\n| `workspace` | Tenant sub-space | name, lens, department, settings | Platform |\n| `capability` | Named action | name, input_schema, output_schema, tier | Platform |\n\n### 2.5 Traits Schema — Normalized Entity Payload\n\nTraits are the **canonical data layer** — provider-specific schemas are normalized into a common key-value structure. This is what makes the Spine provider-agnostic.\n\n```typescript\n// Core trait structure (stored as JSON in entities.traits)\ninterface EntityTraits {\n // Identity\n name: string;\n description?: string;\n slug?: string;\n\n // Classification\n labels?: string[];\n tags?: string[];\n category?: string;\n\n // State\n status: \"active\" | \"inactive\" | \"archived\" | \"pending\" | \"blocked\";\n stage?: string; // pipeline stage, maturity, etc.\n priority?: \"low\" | \"medium\" | \"high\" | \"critical\";\n\n // Quantitative\n value?: number;\n currency?: string;\n count?: number;\n percentage?: number;\n\n // Temporal\n created_at: number; // unix ms\n updated_at: number;\n due_date?: number;\n expires_at?: number;\n\n // Relationships (denormalized for fast lookup)\n owner_id?: string; // SSOC of owner contact\n parent_id?: string; // SSOC of parent entity\n related_ids?: string[]; // SSOCs of related entities\n\n // Provider-specific (namespaced)\n _salesforce?: Record;\n _hubspot?: Record;\n _stripe?: Record;\n _slack?: Record;\n\n // Computed (by Intelligence / Twin)\n _computed?: {\n health_score?: number;\n churn_risk?: number;\n engagement_score?: number;\n last_activity_at?: number;\n next_best_action?: string;\n };\n}\n```\n\n### 2.6 SSOC Index — Cross-Provider Identity Resolution\n\nThe `ssoc_index` table maps provider-native IDs to canonical SSOC UUIDs. This is the resolution layer that makes provider swaps transparent.\n\n| Column | Type | Description |\n| ----------------------- | --------------- | ------------------------------------------------------------------------ |\n| `ssoc_uuid` | `TEXT` PK | The canonical UUID |\n| `tenant_id` | `TEXT` NOT NULL | Isolation |\n| `canonical_type` | `TEXT` | Resolved entity type |\n| `provider_map` | `TEXT` (JSON) | `{ \"salesforce\": \"001ABC...\", \"hubspot\": \"12345\", \"stripe\": \"cus_xyz\" }` |\n| `merge_history` | `TEXT` (JSON) | Array of merge events: `{from_ssoc, to_ssoc, reason, timestamp}` |\n| `resolution_confidence` | `REAL` | How certain the resolution is (0.0–1.0) |\n| `created_at` | `INTEGER` | |\n| `updated_at` | `INTEGER` | |\n\n**Resolution Flow:**\n\n```\nNormalizer (NA2-SSOC Stage)\n │\n ▼\n┌─────────────────┐\n│ 1. Extract IDs │ ← provider_id from raw record\n│ 2. Hash lookup │ → SELECT ssoc_uuid FROM ssoc_index\n│ WHERE provider_map->>'salesforce' = '001ABC...'\n│ 3. If found: │ → bind to existing SSOC\n│ 4. If not: │ → generate new SSOC, INSERT to ssoc_index\n│ 5. If fuzzy: │ → enqueue to TriageBot (confidence 0.70-0.85)\n└─────────────────┘\n │\n ▼\nPipeline INSERT entities (with ssoc_uuid bound)\n```\n\n### 2.7 Spine Audit Log — Immutable History\n\nThe `spine_audit_log` is **append-only, tenant-scoped, and operation-halting**. If a write to this table fails, the triggering operation must not complete.\n\n| Column | Type | Constraints | Description |\n| ------------------ | ------------- | ------------- | -------------------------------------------------------------------------------------------- |\n| `audit_id` | `TEXT` | PRIMARY KEY | CUID2 |\n| `tenant_id` | `TEXT` | NOT NULL | |\n| `entity_id` | `TEXT` | FK → entities | May be NULL for schema-level events |\n| `action_type` | `TEXT` | NOT NULL | `create`, `update`, `delete`, `merge`, `read`, `mcp_access`, `sync_inbound`, `sync_outbound` |\n| `actor_type` | `TEXT` | NOT NULL | `user`, `agent`, `twin`, `system`, `webhook`, `mcp` |\n| `actor_id` | `TEXT` | NOT NULL | User ID, Agent ID, or `system` |\n| `payload_hash` | `TEXT` | NOT NULL | SHA-256 of the action payload (for integrity) |\n| `previous_state` | `TEXT` (JSON) | | Full previous entity state (for rollback) |\n| `new_state` | `TEXT` (JSON) | | Full new entity state |\n| `confidence_score` | `REAL` | | If AI-generated |\n| `via` | `TEXT` | | `mcp`, `webhook`, `api`, `sync`, `twin`, `governance` |\n| `correlation_id` | `TEXT` | NOT NULL | x-correlation-id for distributed tracing |\n| `timestamp` | `INTEGER` | NOT NULL | Unix ms (nanosecond precision if available) |\n\n**Security Rule:** Read operations on Spine entities via MCP or API are ALSO logged (action_type = `read`). This creates a complete access trail.\n\n---\n\n## 3. Memory Schema — Active / Staging / Archived\n\nMemory is not a cache. It is the **compounding context layer** that ensures every agent turn starts with more context than the last. Memory follows a strict lifecycle: intake → classification → validation → promotion → storage → decay.\n\n### 3.1 Memory Architecture\n\n```\n┌─────────────────────────────────────────────────────────────────────────────┐\n│ SHARED MEMORY (Layer 8) │\n│ │\n│ ┌─────────────────┐ ┌─────────────────┐ ┌─────────────────┐ │\n│ │ ACTIVE │ │ STAGING │ │ ARCHIVED │ │\n│ │ (Hot) │ │ (Warm) │ │ (Cold) │ │\n│ ├─────────────────┤ ├─────────────────┤ ├─────────────────┤ │\n│ │ D1 + KV cache │ │ D1 only │ │ D1 + R2 offload │ │\n│ │ <10ms read │ │ <100ms read │ │ <2s read (rare) │ │\n│ │ 30-day TTL │ │ 7-day review │ │ Permanent │ │\n│ │ Auto-decay │ │ Manual promote │ │ Compliance │ │\n│ │ Reinforceable │ │ Or auto-archive │ │ Retention │ │\n│ └─────────────────┘ └─────────────────┘ └─────────────────┘ │\n│ ▲ ▲ ▲ │\n│ └──────────────────────┴──────────────────────┘ │\n│ MEMORY TRIAGE (0.70/0.85) │\n│ │\n│ ┌─────────────────────────────────────────────────────────────────┐ │\n│ │ MEMORY INTAKE PIPELINE │ │\n│ │ Pipeline ──► Continuity Service ──► TriageBot ──► Tiered Store │ │\n│ └─────────────────────────────────────────────────────────────────┘ │\n│ │\n│ ┌─────────────────────────────────────────────────────────────────┐ │\n│ │ MEMORY SCOPES │ │\n│ │ ┌──────────┐ ┌──────────┐ ┌──────────────┐ ┌──────────┐ │ │\n│ │ │ Personal │ │ Work │ │ Organization │ │ AI │ │ │\n│ │ │ (human) │ │(task/proj)│ │ (org-wide) │ │(twin/audit)│ │ │\n│ │ └──────────┘ └──────────┘ └──────────────┘ └──────────┘ │ │\n│ └─────────────────────────────────────────────────────────────────┘ │\n└─────────────────────────────────────────────────────────────────────────────┘\n```\n\n### 3.2 Memory Table — Unified Tiered Store\n\nAll memory lives in a single `memory` table with a `tier` column. This enables atomic tier transitions and unified querying.\n\n| Column | Type | Constraints | Description |\n| --------------------- | --------- | ----------------------- | ------------------------------------------------------------------------------------- |\n| `memory_id` | `TEXT` | PRIMARY KEY | CUID2 |\n| `tenant_id` | `TEXT` | NOT NULL | |\n| `scope` | `TEXT` | NOT NULL | `personal`, `work`, `organization`, `ai` |\n| `tier` | `TEXT` | NOT NULL | `active`, `staging`, `archived` |\n| `entity_id` | `TEXT` | FK → entities.ssoc_uuid | Optional — linked entity |\n| `memory_type` | `TEXT` | NOT NULL | `fact`, `observation`, `decision`, `preference`, `pattern`, `conversation`, `insight` |\n| `content` | `TEXT` | NOT NULL | Human-readable memory content |\n| `content_vector` | `BLOB` | | Vectorize embedding (for semantic retrieval) |\n| `confidence` | `REAL` | NOT NULL | TriageBot score at intake |\n| `source` | `TEXT` | NOT NULL | `twin`, `user`, `sync`, `mcp`, `governance`, `intelligence` |\n| `source_ref` | `TEXT` | | Correlation ID or audit log reference |\n| `actor_id` | `TEXT` | | Who created this memory |\n| `access_count` | `INTEGER` | DEFAULT 0 | How many times retrieved |\n| `last_accessed_at` | `INTEGER` | | Unix ms — drives decay calculation |\n| `decay_score` | `REAL` | DEFAULT 1.0 | Current relevance (0.0–1.0), updated by decay job |\n| `reinforcement_count` | `INTEGER` | DEFAULT 0 | Times confirmed/reinforced |\n| `valid_from` | `INTEGER` | | When memory becomes valid |\n| `valid_until` | `INTEGER` | NULLABLE | When memory expires (NULL = never) |\n| `created_at` | `INTEGER` | NOT NULL | |\n| `updated_at` | `INTEGER` | NOT NULL | |\n| `archived_at` | `INTEGER` | NULLABLE | Set on tier → archived transition |\n\n**Composite Indexes:**\n\n- `idx_memory_tenant_scope_tier` → `(tenant_id, scope, tier, memory_type)` — primary retrieval\n- `idx_memory_entity` → `(tenant_id, entity_id, tier)` — Entity360 memory pane\n- `idx_memory_confidence` → `(tenant_id, confidence, tier)` — triage filtering\n- `idx_memory_decay` → `(tenant_id, decay_score, last_accessed_at)` — decay job\n- `idx_memory_temporal` → `(tenant_id, valid_from, valid_until)` — time-bound queries\n\n### 3.3 Memory Triage Rules\n\n```\n┌────────────────────────────────────────────────────────────────┐\n│ MEMORY TRIAGE DECISION │\n│ │\n│ Confidence Action Tier Storage │\n│ ───────────────────────────────────────────────────────── │\n│ ≥ 0.85 Auto-promote active D1 + KV │\n│ 0.70 – 0.85 Queue for review staging D1 only │\n│ < 0.70 Discard — R2 audit │\n│ │\n│ ┌─────────────────────────────────────────────────────────┐ │\n│ │ ACTIVE MEMORY DECAY (continuity service, cron) │ │\n│ │ │ │\n│ │ decay_score = f(time_since_access, reinforcement, │ │\n│ │ confidence, scope_weight) │ │\n│ │ │ │\n│ │ If decay_score < 0.30 AND last_accessed > 30 days: │ │\n│ │ → tier = 'archived' │ │\n│ │ → content_vector retained, content offloaded to R2 │ │\n│ │ │ │\n│ │ If reinforced: decay_score += 0.2, reset access clock │ │\n│ └─────────────────────────────────────────────────────────┘ │\n└────────────────────────────────────────────────────────────────┘\n```\n\n### 3.4 Memory Graph — Relationship-Aware Retrieval\n\nThe `memory_graph` table (sparse, computed) stores high-confidence relationship-derived memories. It is populated by the Continuity Service during graph traversal.\n\n| Column | Type | Description |\n| --------------------- | ------------------ | --------------------------------------------------------------- |\n| `graph_id` | `TEXT` PK | CUID2 |\n| `tenant_id` | `TEXT` NOT NULL | |\n| `source_memory_id` | `TEXT` FK → memory | |\n| `target_memory_id` | `TEXT` FK → memory | |\n| `connection_type` | `TEXT` | `caused`, `enabled`, `contradicted`, `reinforced`, `similar_to` |\n| `connection_strength` | `REAL` | 0.0–1.0 |\n| `derived_at` | `INTEGER` | When the connection was inferred |\n\n---\n\n## 4. Tenant Schema — Multi-Tenancy & RBAC\n\nTenant isolation is the **primary security boundary**. Every table carries `tenant_id`. The tenant schema defines the organizational identity, membership, roles, and plan constraints.\n\n### 4.1 Tenant Architecture\n\n```\n┌─────────────────────────────────────────────────────────────────────────────┐\n│ TENANT LIFECYCLE (Layer 0) │\n│ │\n│ ┌─────────────┐ ┌─────────────┐ ┌─────────────┐ │\n│ │ TENANTS │◄────┤ USERS │◄────┤ RBAC_ROLES │ │\n│ │ │ │ │ │ │ │\n│ │ tenant_id │ │ user_id │ │ role_id │ │\n│ │ name │ │ tenant_id │ │ tenant_id │ │\n│ │ plan_tier │ │ email │ │ name │ │\n│ │ config │ │ auth_provider│ │ permissions │ │\n│ │ spine_config│ │ profile │ │ level │ │\n│ │ created_at │ │ role_id │ │ │ │\n│ └─────────────┘ └─────────────┘ └─────────────┘ │\n│ ▲ │ │\n│ │ │ │\n│ ┌─────────────┐ ┌─────────────┐ ┌─────────────┐ │ │\n│ │ CONNECTORS │ │ PLANS │ │INVITATIONS │ │ │\n│ │ │ │ │ │ │ │ │\n│ │ connector_id│ │ plan_id │ │ invite_id │ │ │\n│ │ tenant_id │ │ name │ │ tenant_id │ │ │\n│ │ provider │ │ limits │ │ email │ │ │\n│ │ credentials │ │ features │ │ role_id │ │ │\n│ │ status │ │ price │ │ expires_at │ │ │\n│ │ last_sync │ │ │ │ │ │ │\n│ └─────────────┘ └─────────────┘ └─────────────┘ │ │\n│ │ │\n└──────────────────────────────────────────────────────────────────────┴───────┘\n```\n\n### 4.2 Tenants Table\n\n| Column | Type | Constraints | Description |\n| --------------- | ------------- | ----------- | -------------------------------------------------------- |\n| `tenant_id` | `TEXT` | PRIMARY KEY | CUID2 — the isolation boundary |\n| `slug` | `TEXT` | UNIQUE | URL-friendly name: `acme-corp` |\n| `name` | `TEXT` | NOT NULL | Display name |\n| `plan_id` | `TEXT` | FK → plans | `free`, `pro`, `enterprise`, `customer_zero` |\n| `config` | `TEXT` (JSON) | | Tenant-specific settings: lenses, features, custom types |\n| `spine_config` | `TEXT` (JSON) | | Entity types, trait rules, schema overrides |\n| `rbac_config` | `TEXT` (JSON) | | Custom roles, permission overrides |\n| `owner_id` | `TEXT` | FK → users | Organization owner |\n| `billing_email` | `TEXT` | | |\n| `created_at` | `INTEGER` | | |\n| `updated_at` | `INTEGER` | | |\n| `deleted_at` | `INTEGER` | NULLABLE | Soft delete |\n\n### 4.3 Users Table\n\n| Column | Type | Constraints | Description |\n| --------------- | ------------- | ---------------------- | -------------------------------------- |\n| `user_id` | `TEXT` | PRIMARY KEY | CUID2 |\n| `tenant_id` | `TEXT` | NOT NULL, FK → tenants | Hard isolation |\n| `email` | `TEXT` | NOT NULL | |\n| `auth_provider` | `TEXT` | | `password`, `google`, `github`, `saml` |\n| `auth_subject` | `TEXT` | | Provider-specific subject ID |\n| `profile` | `TEXT` (JSON) | | name, avatar, timezone, preferences |\n| `role_id` | `TEXT` | FK → rbac_roles | Primary role |\n| `mfa_enabled` | `INTEGER` | DEFAULT 0 | |\n| `last_login_at` | `INTEGER` | | |\n| `created_at` | `INTEGER` | | |\n| `updated_at` | `INTEGER` | | |\n\n**Security Rule:** `user_id` is unique per tenant. A user with the same email in two tenants has two distinct `user_id` values. Cross-tenant user linking requires explicit federation (future feature).\n\n### 4.4 RBAC Roles Table\n\n| Column | Type | Constraints | Description |\n| ------------- | ------------- | ---------------------- | -------------------------------------------------------------- |\n| `role_id` | `TEXT` | PRIMARY KEY | CUID2 |\n| `tenant_id` | `TEXT` | NOT NULL, FK → tenants | |\n| `name` | `TEXT` | NOT NULL | `owner`, `admin`, `member`, `viewer`, `tam`, `account_success` |\n| `permissions` | `TEXT` (JSON) | NOT NULL | Array of permission strings |\n| `level` | `INTEGER` | NOT NULL | 1–4 (L1–L4 capability access) |\n| `is_system` | `INTEGER` | DEFAULT 0 | Platform-provided roles cannot be deleted |\n| `created_at` | `INTEGER` | | |\n\n**Default Permission Strings:**\n\n| Permission | Description | Levels |\n| ------------------ | ------------------------ | ---------------- |\n| `spine:read` | Read Spine entities | L1+ |\n| `spine:write` | Create/update entities | L3+ |\n| `spine:delete` | Delete/archival | L3+ (owner only) |\n| `memory:read` | Read memory | L1+ |\n| `memory:write` | Create memory | L2+ |\n| `twin:read` | View Twin proposals | L1+ |\n| `twin:write` | Approve/reject proposals | L3+ |\n| `governance:read` | View audit logs | L2+ |\n| `governance:admin` | Override governance | L4 (owner) |\n| `connector:manage` | Add/remove connectors | L3+ |\n| `billing:read` | View billing | L3+ |\n| `billing:manage` | Change plan | L4 (owner) |\n| `admin:tenant` | Full tenant admin | L4 (owner) |\n\n### 4.5 Connectors Table\n\n| Column | Type | Description |\n| --------------------- | ------------------- | --------------------------------------------------------------------------------- |\n| `connector_id` | `TEXT` PK | CUID2 |\n| `tenant_id` | `TEXT` NOT NULL | |\n| `provider` | `TEXT` NOT NULL | `salesforce`, `hubspot`, `slack`, `stripe`, `linear`, `github`, `notion`, `figma` |\n| `connection_type` | `TEXT` | `oauth`, `api_key`, `mcp`, `webhook` |\n| `nango_connection_id` | `TEXT` | Reference to Nango vault (if OAuth) |\n| `config` | `TEXT` (JSON) | Provider-specific config: scopes, sync frequency, filters |\n| `status` | `TEXT` | `active`, `paused`, `error`, `revoked` |\n| `last_sync_at` | `INTEGER` | |\n| `last_sync_status` | `TEXT` | `success`, `partial`, `failed` |\n| `sync_error_count` | `INTEGER` DEFAULT 0 | Consecutive failures |\n| `created_at` | `INTEGER` | |\n| `updated_at` | `INTEGER` | |\n\n---\n\n## 5. Governance Schema — Audit, Proposals, Confidence Gates\n\nGovernance is not an afterthought. It is a **first-class schema concern** — every action flows through confidence-scored gates, and every decision is auditable.\n\n### 5.1 Governance Architecture\n\n```\n┌─────────────────────────────────────────────────────────────────────────────┐\n│ GOVERNANCE LAYER (Layer 10) │\n│ │\n│ ┌─────────────────────────────────────────────────────────────────┐ │\n│ │ CONFIDENCE GATE (0.70 / 0.85) │ │\n│ │ │ │\n│ │ Twin Proposal ──► Confidence Score ──► Decision │ │\n│ │ │ │\n│ │ ≥ 0.85 ──► AUTO-APPROVE ──► ACT_QUEUE ──► Execute │ │\n│ │ 0.70-0.85 ──► MY DESK ──► Human Review ──► Approve/Reject │ │\n│ │ < 0.70 ──► DISCARD ──► Audit log only │ │\n│ │ │ │\n│ └─────────────────────────────────────────────────────────────────┘ │\n│ │ │\n│ ▼ │\n│ ┌─────────────────┐ ┌─────────────────┐ ┌─────────────────┐ │\n│ │ ACTION_PROPOSALS│ │GOVERNANCE_AUDIT │ │ HITL_SESSIONS │ │\n│ │ │ │ LOG │ │ │ │\n│ │ proposal_id │ │ audit_id │ │ session_id │ │\n│ │ tenant_id │ │ tenant_id │ │ tenant_id │ │\n│ │ entity_id │ │ proposal_id │ │ user_id │ │\n│ │ proposed_action │ │ action_type │ │ proposal_id │ │\n│ │ confidence │ │ decision │ │ status │ │\n│ │ status │ │ actor │ │ started_at │ │\n│ │ reviewer_id │ │ reason │ │ resolved_at │ │\n│ │ approved_at │ │ timestamp │ │ resolution │ │\n│ └─────────────────┘ └─────────────────┘ └─────────────────┘ │\n│ │\n└─────────────────────────────────────────────────────────────────────────────┘\n```\n\n### 5.2 Action Proposals Table\n\n| Column | Type | Constraints | Description |\n| ------------------ | ------------- | ------------- | -------------------------------------------------------------------------- |\n| `proposal_id` | `TEXT` | PRIMARY KEY | CUID2 |\n| `tenant_id` | `TEXT` | NOT NULL | |\n| `entity_id` | `TEXT` | FK → entities | Target entity (optional) |\n| `proposed_by` | `TEXT` | NOT NULL | `twin`, `user`, `agent`, `system` |\n| `actor_id` | `TEXT` | NOT NULL | Specific actor identifier |\n| `capability` | `TEXT` | NOT NULL | e.g., `salesforce.opportunity.update` |\n| `payload` | `TEXT` (JSON) | NOT NULL | Proposed action parameters |\n| `confidence_score` | `REAL` | NOT NULL | 0.0–1.0 |\n| `status` | `TEXT` | NOT NULL | `pending`, `approved`, `rejected`, `auto_approved`, `discarded`, `expired` |\n| `reviewer_id` | `TEXT` | FK → users | Human reviewer (NULL for auto-approved) |\n| `reviewed_at` | `INTEGER` | | When human made decision |\n| `review_reason` | `TEXT` | | Human-provided rationale |\n| `expires_at` | `INTEGER` | NOT NULL | Auto-expiry for pending proposals (default: 7 days) |\n| `executed_at` | `INTEGER` | | When action was executed |\n| `execution_result` | `TEXT` (JSON) | | Outcome of executed action |\n| `correlation_id` | `TEXT` | NOT NULL | |\n| `created_at` | `INTEGER` | NOT NULL | |\n\n**State Machine:**\n\n```\n ┌─────────────┐\n │ PENDING │◄─────────────────┐\n │ (0.70-0.85)│ │\n └──────┬──────┘ │\n │ │\n ┌────────────┼────────────┐ │\n ▼ ▼ ▼ │\n ┌─────────┐ ┌─────────┐ ┌─────────┐ │\n │APPROVED │ │REJECTED │ │ EXPIRED │ │\n └────┬────┘ └─────────┘ └─────────┘ │\n │ │\n ▼ │\n ┌─────────┐ │\n │EXECUTED │ │\n └─────────┘ │\n │\n ┌────────────────────────────────────────────────┘\n │\n │ Auto-path (≥ 0.85):\n │ PENDING ──► AUTO_APPROVED ──► EXECUTED\n │\n │ Discard path (< 0.70):\n │ PENDING ──► DISCARDED\n │\n └────────────────────────────────────────────────►\n```\n\n### 5.3 Governance Audit Log\n\n| Column | Type | Constraints | Description |\n| ------------------------ | --------- | --------------------- | -------------------------------------------------------------------------- |\n| `audit_id` | `TEXT` | PRIMARY KEY | CUID2 |\n| `tenant_id` | `TEXT` | NOT NULL | |\n| `proposal_id` | `TEXT` | FK → action_proposals | |\n| `action_type` | `TEXT` | NOT NULL | `propose`, `review`, `approve`, `reject`, `discard`, `execute`, `override` |\n| `decision` | `TEXT` | | `approved`, `rejected`, `discarded`, `auto_approved` |\n| `actor_type` | `TEXT` | NOT NULL | `user`, `twin`, `system`, `governance_rule` |\n| `actor_id` | `TEXT` | NOT NULL | |\n| `reason` | `TEXT` | | Rationale for decision |\n| `confidence_at_decision` | `REAL` | | Confidence score at time of decision |\n| `policy_id` | `TEXT` | FK → policy_registry | Which governance rule applied |\n| `payload_hash` | `TEXT` | NOT NULL | SHA-256 |\n| `timestamp` | `INTEGER` | NOT NULL | |\n\n### 5.4 HITL Sessions Table\n\nHuman-in-the-Loop sessions for 0.70–0.85 confidence proposals. Each session is a Durable Object instance keyed by `tenant_id + user_id`.\n\n| Column | Type | Description |\n| ------------------ | ---------------------------- | ----------------------------------------------- |\n| `session_id` | `TEXT` PK | DO identifier |\n| `tenant_id` | `TEXT` NOT NULL | |\n| `user_id` | `TEXT` NOT NULL | Reviewer |\n| `proposal_id` | `TEXT` FK → action_proposals | Current proposal under review |\n| `status` | `TEXT` | `active`, `idle`, `closed` |\n| `context_snapshot` | `TEXT` (JSON) | Full Entity360 at time of proposal |\n| `started_at` | `INTEGER` | |\n| `resolved_at` | `INTEGER` | |\n| `resolution` | `TEXT` | `approved`, `rejected`, `modified`, `escalated` |\n\n**Security Rule:** HITL DO uses `idFromName(tenant_id + \":\" + user_id)`, NOT `idFromName(\"global\")`. This is a P0 security requirement.\n\n### 5.5 Policy Registry Table\n\n| Column | Type | Description |\n| ------------- | ------------------- | ------------------------------------------------------------------------ |\n| `policy_id` | `TEXT` PK | CUID2 |\n| `tenant_id` | `TEXT` NOT NULL | |\n| `name` | `TEXT` | e.g., `high_value_deal_approval` |\n| `description` | `TEXT` | |\n| `condition` | `TEXT` (JSON) | Rule expression: `{ \"field\": \"deal.amount\", \"op\": \">\", \"value\": 50000 }` |\n| `action` | `TEXT` | `require_approval`, `auto_approve`, `auto_reject`, `escalate` |\n| `priority` | `INTEGER` | Evaluation order (lower = first) |\n| `enabled` | `INTEGER` DEFAULT 1 | |\n| `created_at` | `INTEGER` | |\n\n---\n\n## 6. Entity Relationship Diagram\n\n### 6.1 Complete Schema ERD (ASCII)\n\n```\n┌─────────────────────────────────────────────────────────────────────────────────────┐\n│ INTEGRATEWISE SPINE ERD v2.0 │\n├─────────────────────────────────────────────────────────────────────────────────────┤\n│ │\n│ ┌───────────────┐ ┌───────────────┐ ┌───────────────┐ │\n│ │ TENANTS │◄────────┤ USERS │◄────────┤ RBAC_ROLES │ │\n│ │───────────────│ │───────────────│ │───────────────│ │\n│ │ PK tenant_id │ │ PK user_id │ │ PK role_id │ │\n│ │ name │ │ FK tenant_id │ │ FK tenant_id │ │\n│ │ plan_id ───┼────┐ │ email │ │ name │ │\n│ │ config │ │ │ role_id ───┼────────►│ permissions│ │\n│ │ owner_id ──┼────┼───►│ profile │ │ level │ │\n│ └───────────────┘ │ └───────────────┘ └───────────────┘ │\n│ ▲ │ │\n│ │ │ ┌───────────────┐ │\n│ │ └───►│ PLANS │ │\n│ │ │───────────────│ │\n│ │ │ PK plan_id │ │\n│ │ │ limits │ │\n│ │ │ features │ │\n│ │ └───────────────┘ │\n│ │ │\n│ │ ┌───────────────┐ ┌───────────────┐ │\n│ │ │ CONNECTORS │ │ INVITATIONS │ │\n│ │ │───────────────│ │───────────────│ │\n│ │ │ PK connector_ │ │ PK invite_id │ │\n│ └─────────┤ FK tenant_id │ │ FK tenant_id │ │\n│ │ provider │ │ email │ │\n│ │ status │ │ role_id │ │\n│ └───────────────┘ └───────────────┘ │\n│ │\n│ ╔═══════════════════════════════════════════════════════════════════════════════╗ │\n│ ║ SPINE CORE ║ │\n│ ╠═══════════════════════════════════════════════════════════════════════════════╣ │\n│ ║ ║ │\n│ ║ ┌───────────────┐ ┌───────────────┐ ┌───────────────┐ ║ │\n│ ║ │ ENTITY_TYPES │◄────────┤ ENTITIES │◄───────►│ RELATIONSHIPS │ ║ │\n│ ║ │───────────────│ │───────────────│ │───────────────│ ║ │\n│ ║ │ PK type_id │ │ PK entity_id │ │ PK rel_id │ ║ │\n│ ║ │ FK tenant_id │ │ ssoc_uuid │◄───────►│ FK source_id │ ║ │\n│ ║ │ schema_def │ │ FK tenant_id │◄───────►│ FK target_id │ ║ │\n│ ║ │ lenses │ │ FK entity_type│ │ rel_type │ ║ │\n│ ║ └───────────────┘ │ provider │ │ confidence │ ║ │\n│ ║ │ traits │ │ metadata │ ║ │\n│ ║ │ version │ └───────────────┘ ║ │\n│ ║ │ lineage_id │ ║ │\n│ ║ └───────┬───────┘ ║ │\n│ ║ │ ║ │\n│ ║ ┌───────┴───────┐ ║ │\n│ ║ │ SSOC_INDEX │ ║ │\n│ ║ │───────────────│ ║ │\n│ ║ │ PK ssoc_uuid │ ║ │\n│ ║ │ FK tenant_id │ ║ │\n│ ║ │ provider_map │ ║ │\n│ ║ └───────────────┘ ║ │\n│ ║ ║ │\n│ ║ ┌───────────────┐ ┌───────────────┐ ║ │\n│ ║ │ SPINE_AUDIT_ │◄────────┤ LINEAGE_ │ ║ │\n│ ║ │ LOG │ │ RECORDS │ ║ │\n│ ║ │───────────────│ │───────────────│ ║ │\n│ ║ │ PK audit_id │ │ PK lineage_id │ ║ │\n│ ║ │ FK tenant_id │ │ FK tenant_id │ ║ │\n│ ║ │ FK entity_id │ │ sync_job │ ║ │\n│ ║ │ action_type│ │ stage │ ║ │\n│ ║ │ actor_type │ │ records │ ║ │\n│ ║ │ payload_hash│ │ started_at │ ║ │\n│ ║ └───────────────┘ └───────────────┘ ║ │\n│ ╚═══════════════════════════════════════════════════════════════════════════════╝ │\n│ │\n│ ┌───────────────┐ ┌───────────────┐ ┌───────────────┐ │\n│ │ MEMORY │◄───────►│ MEMORY_GRAPH │ │ DECAY_ │ │\n│ │───────────────│ │───────────────│ │ LOG │ │\n│ │ PK memory_id │ │ PK graph_id │ │───────────────│ │\n│ │ FK tenant_id │ │ FK tenant_id │ │ PK decay_id │ │\n│ │ scope │◄───────►│ FK source_mem │ │ FK tenant_id │ │\n│ │ tier │ │ FK target_mem │ │ FK memory_id │ │\n│ │ FK entity_id ─┼────┐ │ strength │ │ old_tier │ │\n│ │ content │ │ └───────────────┘ │ new_tier │ │\n│ │ confidence │ │ │ reason │ │\n│ │ decay_score│ │ └───────────────┘ │\n│ └───────────────┘ │ │\n│ ▲ │ │\n│ └─────────────┘ │\n│ │\n│ ┌───────────────┐ ┌───────────────┐ ┌───────────────┐ │\n│ │ACTION_PROPOSALS│◄──────┤GOVERNANCE_AUDIT│◄──────┤ HITL_SESSIONS│ │\n│ │───────────────│ │───────────────│ │───────────────│ │\n│ │ PK proposal_id│ │ PK audit_id │ │ PK session_id │ │\n│ │ FK tenant_id │ │ FK tenant_id │ │ FK tenant_id │ │\n│ │ FK entity_id ─┼────┐ │ FK proposal_id│◄────────┤ FK user_id │ │\n│ │ capability │ │ │ decision │ │ FK proposal_id│ │\n│ │ confidence │ │ │ actor │ │ status │ │\n│ │ status │ │ │ timestamp │ └───────────────┘ │\n│ │ FK reviewer_id┼────┼───►│ payload_hash│ │\n│ └───────────────┘ │ └───────────────┘ │\n│ ▲ │ │\n│ └─────────────┘ │\n│ │\n│ ┌───────────────┐ │\n│ │POLICY_REGISTRY│ │\n│ │───────────────│ │\n│ │ PK policy_id │ │\n│ │ FK tenant_id │ │\n│ │ condition │ │\n│ │ action │ │\n│ └───────────────┘ │\n│ │\n└────────────────────────────────────────────────────────────────────────────────────┘\n```\n\n### 6.2 Relationship Cardinality\n\n| Relationship | Cardinality | Description |\n| ------------------------------------------- | ----------- | -------------------------------------------------- |\n| `tenants` → `users` | 1:N | One tenant has many users |\n| `tenants` → `entities` | 1:N | One tenant has many entities |\n| `tenants` → `memory` | 1:N | One tenant has many memory records |\n| `entities` → `relationships` (source) | 1:N | One entity has many outbound relationships |\n| `entities` → `relationships` (target) | 1:N | One entity has many inbound relationships |\n| `entities` → `memory` | 1:N | One entity has many memory entries |\n| `entities` → `action_proposals` | 1:N | One entity may have many proposals |\n| `users` → `action_proposals` (reviewer) | 1:N | One user may review many proposals |\n| `action_proposals` → `governance_audit_log` | 1:N | One proposal generates many audit entries |\n| `action_proposals` → `hitl_sessions` | 1:1 | One proposal maps to one HITL session |\n| `ssoc_index` → `entities` | 1:N | One SSOC may map to many provider records (merged) |\n\n---\n\n## 7. D1 Table Definitions (Drizzle ORM)\n\nThe following Drizzle ORM schema definitions are canonical. They live in `packages/spine-schema/src/schema/` and are the single source of truth for all D1 table structures.\n\n### 7.1 Drizzle Schema — Core Tables\n\n```typescript\n// packages/spine-schema/src/schema/tenants.ts\n\n tenantId: text(\"tenant_id\").primaryKey(),\n slug: text(\"slug\").notNull().unique(),\n name: text(\"name\").notNull(),\n planId: text(\"plan_id\").notNull(),\n config: text(\"config\", { mode: \"json\" }).$type>().default({}),\n spineConfig: text(\"spine_config\", { mode: \"json\" }).$type>().default({}),\n rbacConfig: text(\"rbac_config\", { mode: \"json\" }).$type>().default({}),\n ownerId: text(\"owner_id\"),\n billingEmail: text(\"billing_email\"),\n createdAt: integer(\"created_at\", { mode: \"timestamp_ms\" }).$defaultFn(() => new Date()),\n updatedAt: integer(\"updated_at\", { mode: \"timestamp_ms\" }).$defaultFn(() => new Date()),\n deletedAt: integer(\"deleted_at\", { mode: \"timestamp_ms\" }),\n});\n\n \"users\",\n {\n userId: text(\"user_id\").primaryKey(),\n tenantId: text(\"tenant_id\")\n .notNull()\n .references(() => tenants.tenantId),\n email: text(\"email\").notNull(),\n authProvider: text(\"auth_provider\"),\n authSubject: text(\"auth_subject\"),\n profile: text(\"profile\", { mode: \"json\" }).$type>().default({}),\n roleId: text(\"role_id\"),\n mfaEnabled: integer(\"mfa_enabled\", { mode: \"boolean\" }).default(false),\n lastLoginAt: integer(\"last_login_at\", { mode: \"timestamp_ms\" }),\n createdAt: integer(\"created_at\", { mode: \"timestamp_ms\" }).$defaultFn(() => new Date()),\n updatedAt: integer(\"updated_at\", { mode: \"timestamp_ms\" }).$defaultFn(() => new Date()),\n },\n (table) => [uniqueIndex(\"idx_users_tenant_email\").on(table.tenantId, table.email)]\n);\n\n roleId: text(\"role_id\").primaryKey(),\n tenantId: text(\"tenant_id\")\n .notNull()\n .references(() => tenants.tenantId),\n name: text(\"name\").notNull(),\n permissions: text(\"permissions\", { mode: \"json\" }).$type().notNull().default([]),\n level: integer(\"level\").notNull(),\n isSystem: integer(\"is_system\", { mode: \"boolean\" }).default(false),\n createdAt: integer(\"created_at\", { mode: \"timestamp_ms\" }).$defaultFn(() => new Date()),\n});\n```\n\n### 7.2 Drizzle Schema — Spine Tables\n\n```typescript\n// packages/spine-schema/src/schema/spine.ts\n\n typeId: text(\"type_id\").primaryKey(),\n tenantId: text(\"tenant_id\")\n .notNull()\n .references(() => tenants.tenantId),\n name: text(\"name\").notNull(),\n description: text(\"description\"),\n schemaDef: text(\"schema_def\", { mode: \"json\" }).$type>().default({}),\n lenses: text(\"lenses\", { mode: \"json\" }).$type().default([]),\n traitRules: text(\"trait_rules\", { mode: \"json\" }).$type>().default({}),\n isSystem: integer(\"is_system\", { mode: \"boolean\" }).default(false),\n createdAt: integer(\"created_at\", { mode: \"timestamp_ms\" }).$defaultFn(() => new Date()),\n});\n\n \"entities\",\n {\n entityId: text(\"entity_id\").primaryKey(),\n ssocUuid: text(\"ssoc_uuid\").notNull(),\n tenantId: text(\"tenant_id\")\n .notNull()\n .references(() => tenants.tenantId),\n entityType: text(\"entity_type\")\n .notNull()\n .references(() => entityTypes.typeId),\n provider: text(\"provider\").notNull(),\n providerId: text(\"provider_id\").notNull(),\n displayName: text(\"display_name\"),\n traits: text(\"traits\", { mode: \"json\" }).$type>().notNull().default({}),\n rawPayload: text(\"raw_payload\", { mode: \"json\" }).$type>(),\n confidenceScore: real(\"confidence_score\").notNull().default(1.0),\n version: integer(\"version\").notNull().default(1),\n lineageId: text(\"lineage_id\"),\n createdAt: integer(\"created_at\", { mode: \"timestamp_ms\" }).$defaultFn(() => new Date()),\n updatedAt: integer(\"updated_at\", { mode: \"timestamp_ms\" }).$defaultFn(() => new Date()),\n archivedAt: integer(\"archived_at\", { mode: \"timestamp_ms\" }),\n },\n (table) => [\n uniqueIndex(\"idx_entities_tenant_ssoc\").on(table.tenantId, table.ssocUuid),\n uniqueIndex(\"idx_entities_tenant_provider\").on(\n table.tenantId,\n table.provider,\n table.providerId\n ),\n index(\"idx_entities_tenant_type\").on(table.tenantId, table.entityType),\n index(\"idx_entities_confidence\").on(table.tenantId, table.confidenceScore),\n index(\"idx_entities_lineage\").on(table.tenantId, table.lineageId),\n ]\n);\n\n \"relationships\",\n {\n relId: text(\"rel_id\").primaryKey(),\n tenantId: text(\"tenant_id\")\n .notNull()\n .references(() => tenants.tenantId),\n sourceId: text(\"source_id\").notNull(),\n targetId: text(\"target_id\").notNull(),\n relType: text(\"rel_type\").notNull(),\n direction: text(\"direction\").notNull().default(\"directed\"),\n confidence: real(\"confidence\").notNull().default(1.0),\n metadata: text(\"metadata\", { mode: \"json\" }).$type>().default({}),\n validFrom: integer(\"valid_from\", { mode: \"timestamp_ms\" }),\n validUntil: integer(\"valid_until\", { mode: \"timestamp_ms\" }),\n version: integer(\"version\").notNull().default(1),\n lineageId: text(\"lineage_id\"),\n createdAt: integer(\"created_at\", { mode: \"timestamp_ms\" }).$defaultFn(() => new Date()),\n },\n (table) => [\n index(\"idx_rels_source\").on(table.tenantId, table.sourceId, table.relType),\n index(\"idx_rels_target\").on(table.tenantId, table.targetId, table.relType),\n index(\"idx_rels_confidence\").on(table.tenantId, table.confidence),\n ]\n);\n\n \"ssoc_index\",\n {\n ssocUuid: text(\"ssoc_uuid\").primaryKey(),\n tenantId: text(\"tenant_id\")\n .notNull()\n .references(() => tenants.tenantId),\n canonicalType: text(\"canonical_type\").notNull(),\n providerMap: text(\"provider_map\", { mode: \"json\" })\n .$type>()\n .notNull()\n .default({}),\n mergeHistory: text(\"merge_history\", { mode: \"json\" })\n .$type[]>()\n .default([]),\n resolutionConfidence: real(\"resolution_confidence\").notNull().default(1.0),\n createdAt: integer(\"created_at\", { mode: \"timestamp_ms\" }).$defaultFn(() => new Date()),\n updatedAt: integer(\"updated_at\", { mode: \"timestamp_ms\" }).$defaultFn(() => new Date()),\n },\n (table) => [index(\"idx_ssoc_tenant\").on(table.tenantId)]\n);\n\n \"spine_audit_log\",\n {\n auditId: text(\"audit_id\").primaryKey(),\n tenantId: text(\"tenant_id\")\n .notNull()\n .references(() => tenants.tenantId),\n entityId: text(\"entity_id\"),\n actionType: text(\"action_type\").notNull(),\n actorType: text(\"actor_type\").notNull(),\n actorId: text(\"actor_id\").notNull(),\n payloadHash: text(\"payload_hash\").notNull(),\n previousState: text(\"previous_state\", { mode: \"json\" }).$type>(),\n newState: text(\"new_state\", { mode: \"json\" }).$type>(),\n confidenceScore: real(\"confidence_score\"),\n via: text(\"via\"),\n correlationId: text(\"correlation_id\").notNull(),\n timestamp: integer(\"timestamp\", { mode: \"timestamp_ms\" })\n .notNull()\n .$defaultFn(() => new Date()),\n },\n (table) => [\n index(\"idx_audit_tenant_entity\").on(table.tenantId, table.entityId),\n index(\"idx_audit_correlation\").on(table.correlationId),\n index(\"idx_audit_timestamp\").on(table.tenantId, table.timestamp),\n ]\n);\n```\n\n### 7.3 Drizzle Schema — Memory Tables\n\n```typescript\n// packages/spine-schema/src/schema/memory.ts\n\n \"memory\",\n {\n memoryId: text(\"memory_id\").primaryKey(),\n tenantId: text(\"tenant_id\")\n .notNull()\n .references(() => tenants.tenantId),\n scope: text(\"scope\", { enum: [\"personal\", \"work\", \"organization\", \"ai\"] }).notNull(),\n tier: text(\"tier\", { enum: [\"active\", \"staging\", \"archived\"] }).notNull(),\n entityId: text(\"entity_id\"),\n memoryType: text(\"memory_type\", {\n enum: [\"fact\", \"observation\", \"decision\", \"preference\", \"pattern\", \"conversation\", \"insight\"],\n }).notNull(),\n content: text(\"content\").notNull(),\n contentVector: blob(\"content_vector\"),\n confidence: real(\"confidence\").notNull(),\n source: text(\"source\").notNull(),\n sourceRef: text(\"source_ref\"),\n actorId: text(\"actor_id\"),\n accessCount: integer(\"access_count\").default(0),\n lastAccessedAt: integer(\"last_accessed_at\", { mode: \"timestamp_ms\" }),\n decayScore: real(\"decay_score\").notNull().default(1.0),\n reinforcementCount: integer(\"reinforcement_count\").default(0),\n validFrom: integer(\"valid_from\", { mode: \"timestamp_ms\" }),\n validUntil: integer(\"valid_until\", { mode: \"timestamp_ms\" }),\n createdAt: integer(\"created_at\", { mode: \"timestamp_ms\" }).$defaultFn(() => new Date()),\n updatedAt: integer(\"updated_at\", { mode: \"timestamp_ms\" }).$defaultFn(() => new Date()),\n archivedAt: integer(\"archived_at\", { mode: \"timestamp_ms\" }),\n },\n (table) => [\n index(\"idx_memory_tenant_scope_tier\").on(\n table.tenantId,\n table.scope,\n table.tier,\n table.memoryType\n ),\n index(\"idx_memory_entity\").on(table.tenantId, table.entityId, table.tier),\n index(\"idx_memory_decay\").on(table.tenantId, table.decayScore, table.lastAccessedAt),\n ]\n);\n\n \"memory_graph\",\n {\n graphId: text(\"graph_id\").primaryKey(),\n tenantId: text(\"tenant_id\")\n .notNull()\n .references(() => tenants.tenantId),\n sourceMemoryId: text(\"source_memory_id\").notNull(),\n targetMemoryId: text(\"target_memory_id\").notNull(),\n connectionType: text(\"connection_type\").notNull(),\n connectionStrength: real(\"connection_strength\").notNull().default(0.5),\n derivedAt: integer(\"derived_at\", { mode: \"timestamp_ms\" }).$defaultFn(() => new Date()),\n },\n (table) => [\n index(\"idx_memgraph_source\").on(table.tenantId, table.sourceMemoryId),\n index(\"idx_memgraph_target\").on(table.tenantId, table.targetMemoryId),\n ]\n);\n\n decayId: text(\"decay_id\").primaryKey(),\n tenantId: text(\"tenant_id\")\n .notNull()\n .references(() => tenants.tenantId),\n memoryId: text(\"memory_id\").notNull(),\n oldTier: text(\"old_tier\").notNull(),\n newTier: text(\"new_tier\").notNull(),\n oldDecayScore: real(\"old_decay_score\").notNull(),\n newDecayScore: real(\"new_decay_score\").notNull(),\n reason: text(\"reason\").notNull(),\n triggeredBy: text(\"triggered_by\").notNull(),\n timestamp: integer(\"timestamp\", { mode: \"timestamp_ms\" }).$defaultFn(() => new Date()),\n});\n```\n\n### 7.4 Drizzle Schema — Governance Tables\n\n```typescript\n// packages/spine-schema/src/schema/governance.ts\n\n \"action_proposals\",\n {\n proposalId: text(\"proposal_id\").primaryKey(),\n tenantId: text(\"tenant_id\")\n .notNull()\n .references(() => tenants.tenantId),\n entityId: text(\"entity_id\"),\n proposedBy: text(\"proposed_by\").notNull(),\n actorId: text(\"actor_id\").notNull(),\n capability: text(\"capability\").notNull(),\n payload: text(\"payload\", { mode: \"json\" }).$type>().notNull(),\n confidenceScore: real(\"confidence_score\").notNull(),\n status: text(\"status\", {\n enum: [\"pending\", \"approved\", \"rejected\", \"auto_approved\", \"discarded\", \"expired\"],\n }).notNull(),\n reviewerId: text(\"reviewer_id\"),\n reviewedAt: integer(\"reviewed_at\", { mode: \"timestamp_ms\" }),\n reviewReason: text(\"review_reason\"),\n expiresAt: integer(\"expires_at\", { mode: \"timestamp_ms\" }).notNull(),\n executedAt: integer(\"executed_at\", { mode: \"timestamp_ms\" }),\n executionResult: text(\"execution_result\", { mode: \"json\" }).$type>(),\n correlationId: text(\"correlation_id\").notNull(),\n createdAt: integer(\"created_at\", { mode: \"timestamp_ms\" }).$defaultFn(() => new Date()),\n },\n (table) => [\n index(\"idx_proposals_tenant_status\").on(table.tenantId, table.status),\n index(\"idx_proposals_reviewer\").on(table.tenantId, table.reviewerId, table.status),\n index(\"idx_proposals_confidence\").on(table.tenantId, table.confidenceScore),\n ]\n);\n\n \"governance_audit_log\",\n {\n auditId: text(\"audit_id\").primaryKey(),\n tenantId: text(\"tenant_id\")\n .notNull()\n .references(() => tenants.tenantId),\n proposalId: text(\"proposal_id\"),\n actionType: text(\"action_type\").notNull(),\n decision: text(\"decision\"),\n actorType: text(\"actor_type\").notNull(),\n actorId: text(\"actor_id\").notNull(),\n reason: text(\"reason\"),\n confidenceAtDecision: real(\"confidence_at_decision\"),\n policyId: text(\"policy_id\"),\n payloadHash: text(\"payload_hash\").notNull(),\n timestamp: integer(\"timestamp\", { mode: \"timestamp_ms\" })\n .notNull()\n .$defaultFn(() => new Date()),\n },\n (table) => [\n index(\"idx_gov_audit_proposal\").on(table.proposalId),\n index(\"idx_gov_audit_tenant_time\").on(table.tenantId, table.timestamp),\n ]\n);\n\n sessionId: text(\"session_id\").primaryKey(),\n tenantId: text(\"tenant_id\")\n .notNull()\n .references(() => tenants.tenantId),\n userId: text(\"user_id\").notNull(),\n proposalId: text(\"proposal_id\"),\n status: text(\"status\", { enum: [\"active\", \"idle\", \"closed\"] }).notNull(),\n contextSnapshot: text(\"context_snapshot\", { mode: \"json\" }).$type>(),\n startedAt: integer(\"started_at\", { mode: \"timestamp_ms\" }).$defaultFn(() => new Date()),\n resolvedAt: integer(\"resolved_at\", { mode: \"timestamp_ms\" }),\n resolution: text(\"resolution\", { enum: [\"approved\", \"rejected\", \"modified\", \"escalated\"] }),\n});\n\n policyId: text(\"policy_id\").primaryKey(),\n tenantId: text(\"tenant_id\")\n .notNull()\n .references(() => tenants.tenantId),\n name: text(\"name\").notNull(),\n description: text(\"description\"),\n condition: text(\"condition\", { mode: \"json\" }).$type>().notNull(),\n action: text(\"action\").notNull(),\n priority: integer(\"priority\").notNull().default(100),\n enabled: integer(\"enabled\", { mode: \"boolean\" }).default(true),\n createdAt: integer(\"created_at\", { mode: \"timestamp_ms\" }).$defaultFn(() => new Date()),\n});\n```\n\n### 7.5 Tenant Isolation Helper\n\n```typescript\n// packages/spine-schema/src/helpers.ts\n\n/**\n * Injects tenant_id filter into any Drizzle query.\n * Usage: db.select().from(entities).where(withTenant(eq(entities.entityType, 'contact'), tenantId))\n */\n return query.where(and(query._.where, eq(query._.table.tenantId, tenantId))) as T;\n}\n\n/**\n * Strict tenant check — throws if tenant_id mismatch detected.\n */\n record: T,\n expectedTenantId: string\n): T {\n if (record.tenantId !== expectedTenantId) {\n throw new Error(`CROSS-TENANT VIOLATION: expected ${expectedTenantId}, got ${record.tenantId}`);\n }\n return record;\n}\n```\n\n---\n\n## 8. TypeScript Interfaces & Contracts\n\n### 8.1 Core Entity Contracts\n\n```typescript\n// packages/types/src/spine-entities.ts\n\n/**\n * Canonical entity as stored in Spine.\n * All provider records resolve to this shape.\n */\nexport interface SpineEntity {\n entityId: string; // CUID2\n ssocUuid: string; // Stable UUID — survives provider swaps\n tenantId: string; // Isolation boundary\n entityType: EntityType;\n provider: ProviderName;\n providerId: string; // Native ID in provider system\n displayName: string;\n traits: EntityTraits;\n rawPayload?: Record;\n confidenceScore: number; // 0.0–1.0\n version: number; // Optimistic locking\n lineageId?: string; // Provenance\n createdAt: number; // Unix ms\n updatedAt: number;\n archivedAt?: number;\n}\n\nexport type EntityType =\n | \"organization\"\n | \"contact\"\n | \"deal\"\n | \"task\"\n | \"document\"\n | \"event\"\n | \"metric\"\n | \"intent\"\n | \"workspace\"\n | \"capability\"\n | string; // tenant-extensible\n\nexport type ProviderName =\n | \"salesforce\"\n | \"hubspot\"\n | \"slack\"\n | \"stripe\"\n | \"linear\"\n | \"github\"\n | \"notion\"\n | \"figma\"\n | \"manual\"\n | string;\n\n/**\n * Entity relationship (graph edge).\n */\nexport interface EntityRelationship {\n relId: string;\n tenantId: string;\n sourceId: string; // SSOC UUID\n targetId: string; // SSOC UUID\n relType: RelationType;\n direction: \"directed\" | \"bidirectional\";\n confidence: number;\n metadata?: Record;\n validFrom?: number;\n validUntil?: number;\n version: number;\n lineageId?: string;\n createdAt: number;\n}\n\nexport type RelationType =\n | \"belongs_to\"\n | \"manages\"\n | \"participated_in\"\n | \"authored\"\n | \"referenced_by\"\n | \"depends_on\"\n | \"triggered\"\n | \"blocked_by\"\n | \"owns\"\n | \"influences\"\n | \"succeeded\"\n | \"preceded\"\n | string; // tenant-extensible\n```\n\n### 8.2 Memory Contracts\n\n```typescript\n// packages/types/src/memory.ts\n\nexport interface MemoryRecord {\n memoryId: string;\n tenantId: string;\n scope: MemoryScope;\n tier: MemoryTier;\n entityId?: string; // Optional linked entity SSOC\n memoryType: MemoryType;\n content: string;\n contentVector?: Uint8Array; // Vectorize embedding\n confidence: number;\n source: MemorySource;\n sourceRef?: string; // Audit correlation\n actorId?: string;\n accessCount: number;\n lastAccessedAt?: number;\n decayScore: number;\n reinforcementCount: number;\n validFrom?: number;\n validUntil?: number;\n createdAt: number;\n updatedAt: number;\n archivedAt?: number;\n}\n\nexport type MemoryScope = \"personal\" | \"work\" | \"organization\" | \"ai\";\nexport type MemoryTier = \"active\" | \"staging\" | \"archived\";\nexport type MemoryType =\n | \"fact\"\n | \"observation\"\n | \"decision\"\n | \"preference\"\n | \"pattern\"\n | \"conversation\"\n | \"insight\";\nexport type MemorySource = \"twin\" | \"user\" | \"sync\" | \"mcp\" | \"governance\" | \"intelligence\";\n\n/**\n * Memory decay calculation.\n */\nexport interface DecayRule {\n scope: MemoryScope;\n halfLifeDays: number; // Days until decay_score = 0.5 without access\n reinforcementBoost: number; // Decay score increase per reinforcement\n minDecayScore: number; // Floor (e.g., 0.05)\n archiveThreshold: number; // Score at which tier → archived\n}\n\n {\n scope: \"personal\",\n halfLifeDays: 60,\n reinforcementBoost: 0.2,\n minDecayScore: 0.1,\n archiveThreshold: 0.3,\n },\n {\n scope: \"work\",\n halfLifeDays: 30,\n reinforcementBoost: 0.15,\n minDecayScore: 0.05,\n archiveThreshold: 0.2,\n },\n {\n scope: \"organization\",\n halfLifeDays: 90,\n reinforcementBoost: 0.1,\n minDecayScore: 0.1,\n archiveThreshold: 0.25,\n },\n {\n scope: \"ai\",\n halfLifeDays: 14,\n reinforcementBoost: 0.25,\n minDecayScore: 0.05,\n archiveThreshold: 0.15,\n },\n];\n```\n\n### 8.3 Governance Contracts\n\n```typescript\n// packages/types/src/governance.ts\n\nexport interface ActionProposal {\n proposalId: string;\n tenantId: string;\n entityId?: string;\n proposedBy: ActorType;\n actorId: string;\n capability: string; // e.g., \"salesforce.opportunity.update\"\n payload: Record;\n confidenceScore: number;\n status: ProposalStatus;\n reviewerId?: string;\n reviewedAt?: number;\n reviewReason?: string;\n expiresAt: number;\n executedAt?: number;\n executionResult?: Record;\n correlationId: string;\n createdAt: number;\n}\n\nexport type ProposalStatus =\n | \"pending\"\n | \"approved\"\n | \"rejected\"\n | \"auto_approved\"\n | \"discarded\"\n | \"expired\";\n\nexport type ActorType = \"user\" | \"agent\" | \"twin\" | \"system\" | \"webhook\" | \"mcp\";\n\n/**\n * Governance triage result.\n */\nexport interface TriageResult {\n decision: \"approve\" | \"review\" | \"discard\";\n confidence: number;\n reason: string;\n policyId?: string;\n estimatedRisk: \"low\" | \"medium\" | \"high\" | \"critical\";\n}\n\n/**\n * HITL session state (Durable Object).\n */\nexport interface HITLSession {\n sessionId: string;\n tenantId: string;\n userId: string;\n proposalId?: string;\n status: \"active\" | \"idle\" | \"closed\";\n contextSnapshot?: Entity360;\n startedAt: number;\n resolvedAt?: number;\n resolution?: \"approved\" | \"rejected\" | \"modified\" | \"escalated\";\n}\n\n/**\n * Entity360 — the complete context view for any entity.\n * Assembled by Intelligence service from Spine + Memory + Relationships.\n */\nexport interface Entity360 {\n entity: SpineEntity;\n relationships: {\n inbound: EntityRelationship[];\n outbound: EntityRelationship[];\n };\n memory: {\n active: MemoryRecord[];\n staging: MemoryRecord[];\n };\n timeline: TimelineEvent[];\n insights: string[];\n healthScore?: number;\n churnRisk?: number;\n}\n\nexport interface TimelineEvent {\n timestamp: number;\n type: \"created\" | \"updated\" | \"synced\" | \"action\" | \"milestone\" | \"alert\";\n description: string;\n actor: ActorType;\n actorId: string;\n correlationId: string;\n}\n```\n\n### 8.4 Tenant Contracts\n\n```typescript\n// packages/types/src/tenancy.ts\n\nexport interface Tenant {\n tenantId: string;\n slug: string;\n name: string;\n planId: string;\n config: TenantConfig;\n spineConfig: SpineConfig;\n rbacConfig: RBACConfig;\n ownerId?: string;\n billingEmail?: string;\n createdAt: number;\n updatedAt: number;\n deletedAt?: number;\n}\n\nexport interface TenantConfig {\n enabledLenses: string[];\n enabledFeatures: string[];\n customEntityTypes?: string[];\n branding?: {\n logoUrl?: string;\n primaryColor?: string;\n };\n}\n\nexport interface SpineConfig {\n defaultTraits?: Record;\n traitOverrides?: Record>;\n customRelations?: string[];\n}\n\nexport interface RBACConfig {\n customRoles?: Array<{\n name: string;\n permissions: string[];\n level: number;\n }>;\n permissionOverrides?: Record;\n}\n\nexport interface User {\n userId: string;\n tenantId: string;\n email: string;\n authProvider?: string;\n authSubject?: string;\n profile: UserProfile;\n roleId?: string;\n mfaEnabled: boolean;\n lastLoginAt?: number;\n createdAt: number;\n updatedAt: number;\n}\n\nexport interface UserProfile {\n firstName?: string;\n lastName?: string;\n avatarUrl?: string;\n timezone?: string;\n preferences?: Record;\n}\n\nexport interface RBACRole {\n roleId: string;\n tenantId: string;\n name: string;\n permissions: string[];\n level: number;\n isSystem: boolean;\n createdAt: number;\n}\n```\n\n---\n\n## 9. Flow Descriptions — Services, Queues, Boundaries\n\n### 9.1 Spine Write Flow (The One Write Path)\n\n```\n┌─────────────────────────────────────────────────────────────────────────────────────┐\n│ SPINE WRITE FLOW — ONLY PATH │\n│ │\n│ Provider ──► Webhook/Sync ──► Normalizer ──► PIPELINE_QUEUE ──► Pipeline ──► D1 │\n│ │ │ │ │ │\n│ │ │ │ ▼ │\n│ │ │ │ ┌───────────────┐ │\n│ │ │ │ │ Pipeline │ │\n│ │ │ │ │ Service │ │\n│ │ │ │ │ │ │\n│ │ │ │ │ 1. Validate │ │\n│ │ │ │ │ 2. SSOC bind │ │\n│ │ │ │ │ 3. INSERT D1 │ │\n│ │ │ │ │ 4. Audit log │ │\n│ │ │ │ │ 5. Enrich KV │ │\n│ │ │ │ └───────┬───────┘ │\n│ │ │ │ │ │\n│ │ │ │ ▼ │\n│ │ │ │ ┌───────────────┐ │\n│ │ │ │ │ D1 Spine │ │\n│ │ │ │ │ (entities) │ │\n│ │ │ │ └───────────────┘ │\n│ │ │ │ │\n│ │ │ │ ┌───────────────┐ │\n│ │ │ │ │ spine_audit_log│ │\n│ │ │ │ │ (immutable) │ │\n│ │ │ │ └───────────────┘ │\n│ │ │ │ │\n│ │ │ └──────► If audit fails: HALT │\n│ │ │ │\n│ │ └────────────────────────────────────────────────────►\n│ │ No other service writes to D1 Spine │\n│ │ │\n│ └─────────────────────────────────────────────────────────────────────────────────►\n│ Read paths: store, l2, intelligence, twin (via bindings)\n│ │\n└─────────────────────────────────────────────────────────────────────────────────────────┘\n```\n\n**Flow Steps:**\n\n1. **Inbound Signal** — `webhook-ingress` receives webhook or `connector-sync` polls provider.\n2. **Normalizer** — 8-stage pipeline (NA0–NA5): parse → trait-detect → resolve-type → resolve-entity → link-rels → enrich-map → validate → emit-canonical.\n3. **PIPELINE_QUEUE** — Cloudflare Queue carries canonical records to Pipeline.\n4. **Pipeline Service** (ONLY WRITER):\n - Validates tenant scoping (`WHERE tenant_id = ?`)\n - Resolves/binds SSOC (INSERT or UPDATE `ssoc_index`)\n - INSERT/UPDATE `entities` and `relationships`\n - WRITE `spine_audit_log` (atomic with entity write)\n - WRITE `lineage_records` for provenance\n - UPDATE KV cache for hot entities\n5. **If audit write fails** — entire transaction is rolled back. Operation halts. Alert emitted to `telemetry`.\n\n### 9.2 Memory Intake & Triage Flow\n\n```\n┌─────────────────────────────────────────────────────────────────────────────┐\n│ MEMORY INTAKE & TRIAGE FLOW │\n│ │\n│ Source: Pipeline write / Twin output / User action / MCP invocation │\n│ │ │\n│ ▼ │\n│ ┌─────────────────┐ │\n│ │ MEMORY_INTAKE │ Queue: MEMORY_INTAKE_QUEUE │\n│ │ QUEUE │ │\n│ └────────┬────────┘ │\n│ │ │\n│ ▼ │\n│ ┌─────────────────┐ ┌─────────────────┐ │\n│ │ continuity │────►│ TriageBot │ (runs inside Hermes DO) │\n│ │ service │ │ (confidence │ │\n│ │ │ │ scoring) │ │\n│ └─────────────────┘ └────────┬────────┘ │\n│ │ │\n│ ┌──────────────┼──────────────┐ │\n│ ▼ ▼ ▼ │\n│ ┌──────────┐ ┌──────────┐ ┌──────────┐ │\n│ │ ≥ 0.85 │ │0.70-0.85 │ │ < 0.70 │ │\n│ │ ACTIVE │ │ STAGING │ │ DISCARD │ │\n│ │ tier │ │ tier │ │ (R2) │ │\n│ └────┬─────┘ └────┬─────┘ └────┬─────┘ │\n│ │ │ │ │\n│ ▼ ▼ ▼ │\n│ ┌────────┐ ┌────────┐ ┌────────┐ │\n│ │ D1 + KV│ │ D1 only│ │ R2 cold│ │\n│ │ <10ms │ │ <100ms │ │ storage│ │\n│ │ │ │ │ │ (audit)│ │\n│ └────────┘ └────────┘ └────────┘ │\n│ │\n│ ┌─────────────────────────────────────────────────────────────────┐ │\n│ │ DECAY JOB (continuity service, daily cron) │ │\n│ │ │ │\n│ │ FOR each active memory WHERE last_accessed > 30 days: │ │\n│ │ decay_score *= 0.5 │ │\n│ │ IF decay_score < archive_threshold: │ │\n│ │ → tier = 'archived' │ │\n│ │ → INSERT decay_log │ │\n│ │ → content offloaded to R2 │ │\n│ │ │ │\n│ └─────────────────────────────────────────────────────────────────┘ │\n│ │\n└─────────────────────────────────────────────────────────────────────────────┘\n```\n\n### 9.3 Governance Flow — Proposal to Execution\n\n```\n┌─────────────────────────────────────────────────────────────────────────────┐\n│ GOVERNANCE FLOW — PROPOSAL TO EXECUTION │\n│ │\n│ Twin / Agent / User │\n│ │ │\n│ ▼ │\n│ ┌─────────────────┐ │\n│ │ PROPOSE action │ → INSERT action_proposals (status: pending) │\n│ │ │ → INSERT governance_audit_log │\n│ └────────┬────────┘ │\n│ │ │\n│ ▼ │\n│ ┌─────────────────┐ │\n│ │ govern service │ Evaluate confidence + policy rules │\n│ │ │ │\n│ │ IF confidence >= 0.85: │\n│ │ → status = 'auto_approved' │\n│ │ → enqueue ACT_QUEUE │\n│ │ │\n│ │ ELSE IF confidence >= 0.70: │\n│ │ → status = 'pending' │\n│ │ → CREATE HITL session (DO) │\n│ │ → NOTIFY user (My Desk) │\n│ │ │\n│ │ ELSE: │\n│ │ → status = 'discarded' │\n│ │ → audit only │\n│ └────────┬────────┘ │\n│ │ │\n│ ┌──────┴──────┬────────────┐ │\n│ ▼ ▼ ▼ │\n│ ┌──────┐ ┌──────────┐ ┌────────┐ │\n│ │AUTO │ │ MY DESK │ │DISCARD │ │\n│ │EXEC │ │ (HITL) │ │ │ │\n│ └──┬───┘ └────┬─────┘ └────────┘ │\n│ │ │ │\n│ ▼ ▼ │\n│ ACT_QUEUE User approves/rejects/modifies │\n│ │ │ │\n│ ▼ ▼ │\n│ ┌──────┐ ┌──────────┐ │\n│ │ act │ │ UPDATE │ action_proposals status │\n│ │svc │ │ INSERT │ governance_audit_log │\n│ └──┬───┘ └────┬─────┘ │\n│ │ │ │\n│ └────────────┘ │\n│ │ │\n│ ▼ │\n│ ┌──────────────┐ │\n│ │ Pipeline │ Writeback: execution result → Spine │\n│ │ (ONLY WRITER)│ → INSERT/UPDATE entities │\n│ │ │ → INSERT spine_audit_log │\n│ └──────────────┘ │\n│ │\n└─────────────────────────────────────────────────────────────────────────────┘\n```\n\n---\n\n## 10. Security Boundary Annotations\n\n### 10.1 Data Plane Security Boundaries\n\n```\n┌─────────────────────────────────────────────────────────────────────────────┐\n│ SECURITY BOUNDARIES — DATA PLANE │\n│ │\n│ ┌─────────────────────────────────────────────────────────────────────┐ │\n│ │ BOUNDARY 1: TENANT ISOLATION (Row-Level) │ │\n│ │ ─────────────────────────────────────── │ │\n│ │ Every table has tenant_id. Every query has WHERE tenant_id = ?. │ │\n│ │ Drizzle helper: withTenant(query, tenantId) │ │\n│ │ Failure mode: 403 fail-loud (never 404 — don't leak existence) │ │\n│ │ Owner: ALL services │ │\n│ └─────────────────────────────────────────────────────────────────────┘ │\n│ │\n│ ┌─────────────────────────────────────────────────────────────────────┐ │\n│ │ BOUNDARY 2: WRITE PROTECTION (Pipeline Only) │ │\n│ │ ─────────────────────────────────────────── │ │\n│ │ Only iw-pipeline has D1 INSERT/UPDATE/DELETE on Spine tables. │ │\n│ │ Other services read via service bindings or read replicas. │ │\n│ │ Audit: spine_audit_log records every write with actor + payload hash │ │\n│ │ Failure mode: Halt operation if audit write fails │ │\n│ │ Owner: pipeline service │ │\n│ └─────────────────────────────────────────────────────────────────────┘ │\n│ │\n│ ┌─────────────────────────────────────────────────────────────────────┐ │\n│ │ BOUNDARY 3: GOVERNANCE GATE (Confidence Scored) │ │\n│ │ ─────────────────────────────────────────────── │ │\n│ │ No action executes without governance scoring. │ │\n│ │ Auto-execute only at ≥ 0.85 confidence. │ │\n│ │ Human review at 0.70–0.85. │ │\n│ │ Discard below 0.70. │ │\n│ │ Audit: governance_audit_log immutable. │ │\n│ │ Owner: govern service │ │\n│ └─────────────────────────────────────────────────────────────────────┘ │\n│ │\n│ ┌─────────────────────────────────────────────────────────────────────┐ │\n│ │ BOUNDARY 4: HITL SESSION ISOLATION (DO-Level) │ │\n│ │ ───────────────────────────────────────────── │ │\n│ │ HITL Durable Objects keyed by tenant_id + user_id. │ │\n│ │ NEVER use global DO. NEVER share DO across tenants. │ │\n│ │ Session context scoped to tenant + user only. │ │\n│ │ Owner: govern service │ │\n│ └─────────────────────────────────────────────────────────────────────┘ │\n│ │\n│ ┌─────────────────────────────────────────────────────────────────────┐ │\n│ │ BOUNDARY 5: MEMORY SCOPE ISOLATION │ │\n│ │ ──────────────────────────────────── │ │\n│ │ Personal memory: accessible only to owning user. │ │\n│ │ Work memory: accessible to project/task members. │ │\n│ │ Organization memory: accessible to all tenant members. │ │\n│ │ AI memory: accessible to Twin and governance audit. │ │\n│ │ Owner: continuity service │ │\n│ └─────────────────────────────────────────────────────────────────────┘ │\n│ │\n│ ┌─────────────────────────────────────────────────────────────────────┐ │\n│ │ BOUNDARY 6: SSOC RESOLUTION (Identity Integrity) │ │\n│ │ ──────────────────────────────────────────────── │ │\n│ │ Provider ID → SSOC binding is immutable once established. │ │\n│ │ Merges create audit trail in ssoc_index.merge_history. │ │\n│ │ No entity exists without SSOC binding. │ │\n│ │ Owner: normalizer (NA2 stage), pipeline │ │\n│ └─────────────────────────────────────────────────────────────────────┘ │\n│ │\n└─────────────────────────────────────────────────────────────────────────────┘\n```\n\n### 10.2 Security Checklist for Data Model\n\n| # | Check | Enforcement | Verification |\n| --- | ------------------------------------ | --------------------------------------------- | ----------------------------------- |\n| 1 | No cross-tenant queries | `withTenant()` on every query | Static analysis + runtime assertion |\n| 2 | No direct D1 writes outside Pipeline | Service binding permissions | Integration test |\n| 3 | Audit log append-only | No UPDATE/DELETE on audit tables | Schema-level trigger |\n| 4 | HITL DO not global | `idFromName(tenantId + userId)` | Unit test |\n| 5 | Memory scope enforced | Query filter by scope + user membership | Integration test |\n| 6 | Proposal expiry enforced | `expires_at` checked before execution | Unit test |\n| 7 | Soft delete only | `archived_at` / `deleted_at` — no hard DELETE | Schema review |\n| 8 | Payload hashed | SHA-256 on all audit entries | Unit test |\n| 9 | Correlation ID present | Required on all audit + proposal records | Schema NOT NULL |\n| 10 | Tenant_id in JWT, not payload | Gateway extraction only | Integration test |\n\n---\n\n## 11. Integration Points with Adjacent Layers\n\n### 11.1 North-South Integration Map\n\n```\n┌─────────────────────────────────────────────────────────────────────────────┐\n│ DATA MODEL — ADJACENT LAYER INTEGRATION │\n│ │\n│ NORTH (Ingress / Consumers) │\n│ ─────────────────────────── │\n│ │\n│ ┌─────────────┐ ┌─────────────┐ ┌─────────────┐ │\n│ │ Gateway │────►│ Discovery │────►│ MCP Pool │ │\n│ │ │ │ (Registry) │ │ (Inbound) │ │\n│ └─────────────┘ └─────────────┘ └──────┬──────┘ │\n│ │ │ │\n│ │ JWT + tenant_id │ tool catalog filtered │\n│ │ │ by tenant capabilities │\n│ ▼ ▼ │\n│ ┌─────────────────────────────────────────────────────────────────────┐ │\n│ │ DATA MODEL BOUNDARY │ │\n│ │ Reads: entities, memory, proposals (via store, l2, intelligence) │ │\n│ │ Writes: NONE (all writes route through Pipeline) │ │\n│ └─────────────────────────────────────────────────────────────────────┘ │\n│ ▲ ▲ │\n│ │ │ │\n│ │ Service bindings │ Service bindings │\n│ │ │ │\n│ SOUTH (Egress / Providers) │\n│ ─────────────────────────── │\n│ │\n│ ┌─────────────┐ ┌─────────────┐ ┌─────────────┐ │\n│ │ Act │◄────│ Adapter │◄────│ MCP Pool │ │\n│ │ Service │ │ Modules │ │ (Outbound) │ │\n│ └──────┬──────┘ └─────────────┘ └─────────────┘ │\n│ │ │\n│ │ Writeback ──► Pipeline ──► D1 │\n│ │ │\n│ ┌──────┴──────┐ ┌─────────────┐ ┌─────────────┐ │\n│ │ Connector │ │ Connector │ │ Normalizer │ │\n│ │ (OAuth) │ │ Sync │ │ (8-stage) │ │\n│ └─────────────┘ └─────────────┘ └─────────────┘ │\n│ │\n└─────────────────────────────────────────────────────────────────────────────┘\n```\n\n### 11.2 Integration Points Detail\n\n| Adjacent Layer | Direction | Integration Point | Data Model Interaction | Contract |\n| ----------------------------- | ---------- | ----------------------------------------------------------- | ----------------------------------------------------------- | ---------------------------------- |\n| **Identity (Layer 1)** | Read | `tenants`, `users`, `rbac_roles` | Auth checks against RBAC; tenant resolution | JWT claims + service binding |\n| **Ingress (Layer 2)** | Read | `entities` (via `store`) | MCP tool catalog assembled from entity types + capabilities | Discovery response |\n| **Capability (Layer 3)** | Read | `memory`, `entities` | Context assembly for Twin reasoning | Entity360 interface |\n| **Continuity (Layer 4)** | Read/Write | `memory`, `memory_graph`, `decay_log` | Memory intake, triage, decay | MemoryRecord + TriageResult |\n| **Governance (Layer 5)** | Read/Write | `action_proposals`, `governance_audit_log`, `hitl_sessions` | Proposal lifecycle, approval gates | ActionProposal + TriageResult |\n| **Provider Fabric (Layer 6)** | Write | `entities` (via Pipeline writeback) | Execution results written to Spine | ExecutionResult → Pipeline enqueue |\n| **Normalizer** | Write | `entities`, `relationships`, `ssoc_index` | 8-stage pipeline output → Pipeline | PIPELINE_QUEUE message |\n| **Telemetry** | Read | `spine_audit_log`, `governance_audit_log` | Metrics, traces, anomaly detection | Audit stream |\n\n### 11.3 Queue Contracts\n\n```typescript\n// PIPELINE_QUEUE message schema\ninterface PipelineMessage {\n type:\n | \"entity_create\"\n | \"entity_update\"\n | \"entity_delete\"\n | \"relationship_create\"\n | \"execution_result\";\n tenantId: string;\n correlationId: string;\n payload: {\n entity?: Partial;\n relationships?: Partial[];\n ssocBinding?: { provider: string; providerId: string; ssocUuid: string };\n auditContext: {\n actorType: ActorType;\n actorId: string;\n via: string;\n };\n };\n timestamp: number;\n}\n\n// ACT_QUEUE message schema\ninterface ActMessage {\n type: \"capability_execute\" | \"workflow_step\" | \"proposal_approved\";\n tenantId: string;\n correlationId: string;\n proposalId?: string;\n capability: string;\n payload: Record;\n entity360?: Entity360; // Injected context\n approvedBy?: string; // user_id if HITL-approved\n timestamp: number;\n}\n\n// MEMORY_INTAKE_QUEUE message schema\ninterface MemoryIntakeMessage {\n tenantId: string;\n correlationId: string;\n source: MemorySource;\n actorId: string;\n entityId?: string;\n content: string;\n confidence?: number; // Pre-scored (optional)\n scope: MemoryScope;\n memoryType: MemoryType;\n timestamp: number;\n}\n```\n\n---\n\n## 12. Appendix: SSOC Resolution & Lineage\n\n### 12.1 SSOC Resolution Algorithm\n\n```\nINPUT: provider, providerId, tenantId, entityType, traits\nOUTPUT: ssocUuid (existing or newly generated)\n\n1. EXACT MATCH:\n SELECT ssoc_uuid FROM ssoc_index\n WHERE tenant_id = ?\n AND provider_map->>'provider' = 'providerId'\n → If found: RETURN ssoc_uuid\n\n2. FUZZY MATCH (for contacts/organizations):\n SELECT ssoc_uuid, provider_map FROM ssoc_index\n WHERE tenant_id = ?\n AND canonical_type = ?\n → Score each candidate:\n - Email exact match: +1.0\n - Domain match: +0.5\n - Name similarity > 0.9: +0.7\n - Phone match: +0.8\n → If max_score > 0.85: RETURN that ssoc_uuid\n → If max_score 0.70-0.85: ENQUEUE to TriageBot\n → If max_score < 0.70: CREATE new SSOC\n\n3. CREATE NEW:\n ssoc_uuid = generateCuid()\n INSERT ssoc_index (ssoc_uuid, tenant_id, canonical_type, provider_map)\n RETURN ssoc_uuid\n\n4. AUDIT:\n INSERT spine_audit_log (action_type='ssoc_bind', ...)\n```\n\n### 12.2 Lineage Record Structure\n\n```typescript\ninterface LineageRecord {\n lineageId: string; // CUID2\n tenantId: string;\n syncJobId?: string; // Reference to connector-sync job\n normalizerRunId?: string; // Reference to normalizer batch\n stage: string; // 'na0', 'na1', 'na2', 'na3', 'na4', 'na5', 'pipeline', 'act'\n recordsProcessed: number;\n recordsCreated: number;\n recordsUpdated: number;\n recordsFailed: number;\n errorLog?: string[];\n startedAt: number;\n completedAt?: number;\n triggeredBy: string; // Actor / service / cron\n}\n```\n\n### 12.3 Schema Migration Rules\n\n1. **Additive only** in production — never rename columns, never remove columns.\n2. **Soft schema** — `traits` JSON column absorbs schema evolution without migrations.\n3. **Tenant-specific types** — `entity_types` table allows per-tenant extension without affecting other tenants.\n4. **Migration files** live in `packages/spine-schema/migrations/` and are applied via `wrangler d1 migrations apply`.\n5. **Rollback plan** — every migration has a corresponding rollback script tested in staging.\n\n---\n\n**END OF SPECIFICATION**\n\n_IntegrateWise Continuity Bridge v2.0 — Data Model & Entity Relationships. This document is Stage 1/2 deep-dive output feeding the canonical architecture document. All schemas, interfaces, and flows are production-intended and must be validated against P0 security requirements before deployment._\n"}