{"slug":"canonical/05-SPINE-SCHEMA-BUSINESS-CONTEXT-STANDARD","title":"Spine Schema & Business Context Standard","section":"canonical","sectionLabel":"Canon","status":"canon","type":"document","path":"canonical/05-SPINE-SCHEMA-BUSINESS-CONTEXT-STANDARD.md","bytes":13898,"description":"Architectural standard for tenant org, departmental, and domain-based schema design: Account as universal anchor, cross-domain interaction contracts, and fixed base with adaptive growth.","updated":null,"source":"integratewise/Integratewise-docs-wiki","content":"\n\n## 1. Executive Summary\n\nThis standard defines the rules, invariants, and architectural contracts for creating and evolving the **IntegrateWise Business Context Spine Schema**.\n\nThe fundamental requirements are:\n1. **Tenant Organization Standard**: Multi-tenant isolation at the root; tenant-level governance and departmental sub-scopes.\n2. **Domain-Based Architecture**: Strict functional domain boundaries (Revenue/Sales, Customer Success, Customer Support, Product & Engineering, Finance & Billing, Communications, AI & Execution).\n3. **The Universal Anchor**: The **Account** is the fixed, immutable gravitational center connecting all domains. Domains do not connect via ad-hoc spaghetti links; they link through the Account hub.\n4. **Inter-Domain Interaction Contracts**: Formally defined interaction and event propagation rules across domains (e.g. Support ticket escalations cascading into CS churn risk and Revenue renewal forecasts).\n5. **Fixed Base + Adaptive Adaptations**: The base schema is **invariant and fixed**. Connectors and tools dynamically introduce domain extensions, custom fields, and new KPIs without ever mutating, breaking, or invalidating the base schema contracts.\n\n---\n\n## 2. Structural Hierarchy: Tenant, Department, Domain\n\n```text\n                         TENANT ORG (Root Partition)\n                                      │\n            ┌─────────────────────────┴─────────────────────────┐\n            ▼                                                   ▼\n     DEPARTMENT: REVENUE                                 DEPARTMENT: PRODUCT\n   (Sales, RevOps, Pipeline)                           (Eng, Roadmap, Releases)\n            │                                                   │\n            └─────────────────────────┬─────────────────────────┘\n                                      ▼\n                        UNIVERSAL ANCHOR: ACCOUNT\n                         (Gravitational Center)\n                                      │\n       ┌───────────┬───────────┬──────┴────┬───────────┬───────────┐\n       ▼           ▼           ▼           ▼           ▼           ▼\n    REVENUE        CS       SUPPORT     PRODUCT     FINANCE      COMMS\n   (Deals)      (Health)   (Tickets)   (Issues)   (Invoices)   (Slack)\n```\n\n### 2.1 Tenant Organization Boundary\n- Every record in the Spine is scoped to an immutable `tenant_id`.\n- Tenant boundaries are strictly isolated: no cross-tenant query, cross-tenant foreign key, or cross-tenant vector search is permitted.\n\n### 2.2 Departmental Scopes & Lenses\nDepartments represent the functional operating units within an organization:\n- **Revenue / Sales**: Focuses on pipelines, deal stages, quotas, close dates, win/loss.\n- **Customer Success**: Focuses on onboarding milestones, adoption, product utilization, health score, churn risks.\n- **Customer Support**: Focuses on incident response, ticket SLAs, escalation paths, bug verifications.\n- **Product & Engineering**: Focuses on user feedback, feature requests, backlog prioritization, release deployments.\n- **Finance & Billing**: Focuses on ARR/MRR, contract terms, billing cycles, invoice status, collections.\n- **Executive & Operations**: Focuses on blended KPIs, organizational efficiency, capital allocation.\n\nDepartments do not own private databases. They operate on **projections** composed over the shared Business Context Spine.\n\n---\n\n## 3. The Universal Anchor: Account\n\n### 3.1 Why Account is the Universal Center\nIn B2B business operations, every transaction, communication, problem, and dollar ultimately relates to an **Account** (Customer or Prospect).\n\nIf systems link to each other peer-to-peer (e.g. Zendesk linking directly to Jira, Jira to Stripe, Stripe to Salesforce), the result is an unmaintainable $N \\times M$ integration tangle.\n\nIn IntegrateWise, **all domains link to the Account anchor**:\n\n```text\n                               ACCOUNT\n               ┌──────────────────┼──────────────────┐\n               ▼                  ▼                  ▼\n          CRM / SALES       CUSTOMER SUCCESS      SUPPORT\n          • Deals           • Health Score        • Tickets\n          • Contacts        • Milestones          • Incidents\n          • Opportunities   • Risk Signals        • Escalations\n               ▲                  ▲                  ▲\n               │                  │                  │\n               └──────────────────┼──────────────────┘\n                                  │\n               ┌──────────────────┼──────────────────┐\n               ▼                  ▼                  ▼\n          PRODUCT & ENG       FINANCE & BILLING    COMMS & CONTEXT\n          • Feature Requests  • Subscriptions      • Email Threads\n          • Bug Reports       • Invoices           • Slack Channels\n          • Adoption Metrics  • ARR / MRR          • Call Transcripts\n```\n\n### 3.2 Invariant Base Account Schema\nThe base Account schema is **fixed and immutable across all tenants**:\n\n| Field | Type | Invariant Rule | Description |\n| :--- | :--- | :--- | :--- |\n| `id` | UUID | Immutable Primary Key | Canonical Spine entity ID |\n| `tenant_id` | UUID | Immutable Partition Key | Tenant organization boundary |\n| `name` | String | Required | Legal or operating account name |\n| `domain` | String | Required for Deduplication | Primary web domain (e.g. acme.com) |\n| `lifecycle_stage` | Enum | Required State Machine | `lead` \\| `opportunity` \\| `customer` \\| `churned` \\| `partner` |\n| `external_ids` | `Record<string, string>` | Fixed System Mapping | Pointers to source tools (`salesforce_id`, `hubspot_id`, `zendesk_id`, `stripe_id`, etc.) |\n| `kpis` | BaseAccountKPIs | Guaranteed Computation | `arr`, `mrr`, `health_score`, `open_tickets_count`, `active_users` |\n| `relations` | BaseDomainRelations | Standard Foreign Refs | Arrays of IDs referencing deals, tickets, invoices, contacts, and issues |\n| `extensions` | `Record<string, DomainExtension>` | Adaptive Extension Bag | Namespace-isolated slot for dynamic connector fields |\n\n---\n\n\n### 3.3 Exhaustive Multi-Domain KPI Inventory\n\nThe Account Base Schema and the Enterprise KPI Catalog enforce **70+ governed business metrics** across 6 functional dimensions at the Account anchor:\n\n1. **Revenue & Commercial**:\n   - `arr`, `mrr`, `contract_value` (TCV), `pipeline_open_value`, `win_probability`, `expansion_potential_value`, `days_to_renewal`, `sales_cycle_days`, `pipeline_velocity`, `average_deal_size`.\n2. **Customer Success & Health**:\n   - `health_score` (0-100), `health_category` (champion/healthy/at_risk/critical), `health_trend`, `churn_risk_score`, `gross_churn_rate`, `net_churn_rate`, `logo_retention_rate`, `time_to_first_onboarding_milestone`, `csat_score`.\n3. **Customer Support & Service**:\n   - `open_tickets_count`, `unresolved_critical_incidents` (P0/P1), `avg_resolution_time_hrs`, `first_response_sla_breached_count`, `customer_effort_score`, `first_contact_resolution_rate`, `ticket_reopen_rate`.\n4. **Product Usage & Adoption**:\n   - `active_users_30d` (MAU), `active_users_7d` (WAU), `dau_to_mau_ratio`, `license_utilization_rate`, `product_adoption_rate`, `key_workflow_completions_30d`, `time_to_adopt_new_feature_days`.\n5. **Finance & Commercial Billing**:\n   - `payment_status`, `overdue_invoice_count`, `total_overdue_amount`, `days_sales_outstanding` (DSO), `payment_method_valid`, `gross_margin_percent`, `quick_ratio`, `deferred_revenue`.\n6. **Engagement & Contextual Memory**:\n   - `net_promoter_score`, `last_activity_date`, `days_since_last_interaction`, `meeting_count_30d`, `slack_message_volume_30d`, `executive_sponsor_engaged`, `champion_identified`, `sentiment_score_30d`.\n\n---\n\n## 4. Inter-Domain Interaction Contracts\n\nDomains do not operate in silos. An event in one domain cascades context into other domains through the Account hub.\n\n### 4.1 Canonical Interaction Flow: Support to Renewal Cascade\n\n```text\n1. SUPPORT DOMAIN:\n   High-priority ticket logged (Zendesk)\n   Ticket status = \"Open\" for 14 days\n             │\n             ▼\n2. ACCOUNT ANCHOR:\n   Account.open_tickets_count increments\n   Account.support_sla_breached = true\n   Account.health_score recalculates (drops from 85 -> 48)\n             │\n             ├─────────────────────────────────────────┐\n             ▼                                         ▼\n3. CUSTOMER SUCCESS DOMAIN:               4. REVENUE DOMAIN:\n   Emits Signal: \"CS_CHURN_RISK_HIGH\"        Emits Signal: \"RENEWAL_SLIPPAGE_RISK\"\n   Creates Task: \"CSM Executive Reachout\"    Updates Forecast: Deal probability drops 20%\n```\n\n### 4.2 Interaction Rules Matrix\n\n| Triggering Domain | Event / Trigger | Account-Level Action | Cascading Domain Impact |\n| :--- | :--- | :--- | :--- |\n| **Customer Support** | P0 Ticket Opened | Increments `unresolved_critical_incidents` | **CS**: Flags churn warning; **Comms**: Alerts Slack channel |\n| **Product & Engineering**| Feature Shipped | Updates `shipped_requested_features` | **CS**: Notifies waiting contacts; **Support**: Closes backlog tickets |\n| **Finance & Billing** | Invoice Overdue > 30d | Sets `payment_status = \"overdue\"` | **Revenue**: Blocks expansion quotes; **CS**: Alerts CSM |\n| **Customer Success** | Product Usage Drops 30% | Decrements `health_score` | **Revenue**: Flags renewal risk; **AI**: Drafts recovery proposal |\n| **Communications** | Executive Sponsor Leaves | Sets `executive_sponsor_id = null` | **CS**: Triggers stakeholder re-mapping workflow |\n\n---\n\n## 5. Fixed Base + Adaptive Adaptations (Schema Growth)\n\n### 5.1 The Principle of Schema Growth vs Schema Freeze\n- **Schema Freeze** is rigid and fails because every business has unique tools, custom CRM fields, and industry-specific metrics.\n- **Schema Drift** is chaotic and fails because uncontrolled custom fields break standardized queries, projections, and AI models.\n- **IntegrateWise Solution**: **Fixed Base + Adaptive Extensions**.\n\n```text\n┌─────────────────────────────────────────────────────────────────┐\n│                    FIXED BASE SCHEMA (INVARIANT)                │\n│  • Account Name, Domain, Lifecycle, Owner                       │\n│  • Standard External IDs (CRM, Support, Billing)                │\n│  • Core Enterprise KPIs (ARR, Health Score, Ticket Count)       │\n│  • Canonical Domain Relations (Deals, Tickets, Invoices)        │\n└───────────────────────────────┬─────────────────────────────────┘\n                                │\n                                ▼ Adaptive Extensions (Namespace-Isolated)\n┌───────────────────────────────┴─────────────────────────────────┐\n│              ADAPTIVE CONNECTOR & DOMAIN EXTENSIONS              │\n│  • \"gong:call_intelligence\": { sentiment: 0.82, competitors: [] }│\n│  • \"mixpanel:usage_telemetry\": { weekly_queries: 420 }          │\n│  • \"salesforce:custom_fields\": { custom_tier__c: \"Platinum\" }   │\n└─────────────────────────────────────────────────────────────────┘\n```\n\n### 5.2 Extension Invariants\n1. **Namespace Isolation**: Every extension must be namespaced by connector or domain (e.g. `gong:call_summary`, `hubspot:custom_properties`).\n2. **Non-Colliding Keys**: An extension can NEVER overwrite, mask, or redefine a fixed base field. Attempting to do so triggers a validation rejection.\n3. **Additive-Only**: Adding an extension must not invalidate existing queries or cached projections.\n4. **Metadata & Provenance**: Every extension field carries provenance metadata (source connector, timestamp, confidence score, raw payload reference).\n\n---\n\n## 6. Implementation Checklist\n\n- [x] **Base Account Schema**: Typed immutable core fields with cross-system external ID maps.\n- [x] **Domain Schemas**: Base definitions for Revenue, CS, Support, Product, Finance, Communications, AI.\n- [x] **Inter-Domain Interaction Registry**: Formal event and signal routing across domains via Account.\n- [x] **Adaptive Schema Manager**: Runtime validator ensuring base schema invariance during connector discovery.\n- [x] **Vitest Test Suite**: Unit tests verifying invariance, cross-domain cascading, and non-colliding extensions.\n"}