{"slug":"internal/architecture/CANONICAL_SPINE_SCHEMA_FOUNDATION","title":"Canonical Spine Schema Foundation","section":"internal","sectionLabel":"Internal","status":"unclassified","type":"document","path":"internal/architecture/CANONICAL_SPINE_SCHEMA_FOUNDATION.mdx","bytes":9298,"description":null,"updated":null,"source":"integratewise/Integratewise-docs-wiki","content":"\n**Status:** derived canonical storage contract  \n**Date:** 2026-07-28  \n**Authority:** `ARCHITECTURE_LOCK.md` → `IMPLEMENTATION_CONTRACT_v1.1.md` → `CONSOLIDATED_PRODUCT_PLATFORM_CONTRACT_v1.2.md`\n\n## Decision\n\nIntegrateWise uses a hybrid canonical Spine:\n\n```text\ncanonical entity kernel\n+ typed entity domains\n+ classification registries\n+ events, relationships, evidence and versions\n+ work, communication, knowledge and memory\n+ projection metadata\n+ governance and runtime state\n```\n\nIt does not use one untyped EAV table, separate department schemas, or\nprovider-specific business tables. A tenant owns one Spine. Departments and\nroles consume RBAC-governed projections of that Spine.\n\n```text\napp category    = source classification\nbusiness domain = semantic / policy classification\nlifecycle       = operational routing\nrole            = projection consumer and authority context\n```\n\n## Non-negotiable identity and scope model\n\n`spine_entities` supplies the stable canonical entity identity. Its primary\nkey is the entity identifier; **`spine_network_id` is never an entity primary\nkey**. A network scopes the tenant's connected graph and can contain many\nentities, connections, workspaces and projections.\n\nEvery tenant-owned canonical or runtime row must carry `tenant_id`. New\ncanonical writes also carry the complete scope envelope:\n\n| Field                                                           | Meaning                                                          |\n| --------------------------------------------------------------- | ---------------------------------------------------------------- |\n| `tenant_id`                                                     | Isolation boundary and policy root.                              |\n| `tenant_account_id`                                             | The tenant's operating account; not a customer/business account. |\n| `spine_network_id`                                              | The tenant Spine Network containing connector and graph context. |\n| `organization_id`, `workspace_id`                               | Optional narrower context; neither replaces tenant scope.        |\n| `connector_binding_id`, `spine_connection_id`                   | Provider-installation and connection lineage.                    |\n| `trace_id`, `correlation_id`, `causation_id`, `idempotency_key` | End-to-end execution and replay traceability.                    |\n\n`department_id` can exist as an organisational entity or projection input. It\nmust not determine a table family, database schema, or tenant boundary.\n\n## Storage planes\n\n| Plane                                  | Canonical responsibility                                                                                                                          |\n| -------------------------------------- | ------------------------------------------------------------------------------------------------------------------------------------------------- |\n| Identity and tenancy                   | tenant, organisation, workspace, user, membership, role, permission, consent and delegation.                                                      |\n| Ontology and registries                | entity, field, relationship, lifecycle, event, signal, capability, policy, projection and widget definitions. Platform-owned and tenant-readable. |\n| Entity kernel and typed domains        | entity identity, source aliases, versions, field provenance, typed business entities and graph relationships.                                     |\n| Work, communication and knowledge      | work items, tasks, decisions, approvals, messages, meetings, documents, knowledge and evidence.                                                   |\n| Memory and promotion                   | personal, organisational, agent and operational memory; promotion is the governed memory-to-truth route.                                          |\n| Integration, capability and governance | connector and connection bindings, sync, MCP, capability execution, policy, audit and outcome verification.                                       |\n| Projection and canvas                  | projection rules, role/domain/field/row filters, canvas composition and cache-refresh metadata.                                                   |\n| Runtime reliability                    | idempotency, retries, dead letters, reconciliation, verification and health. Runtime records are not business truth.                              |\n\nTyped domain tables use provider-neutral names, for example\n`finance_invoices`, `crm_accounts` and `operations_projects` (or their\ncanonical `spine_*` equivalents). Provider identity belongs in\n`spine_entity_sources`, never in a typed business table name.\n\n## Canonical versus operational boundary\n\n| Store                         | Role                                                                                                                                           |\n| ----------------------------- | ---------------------------------------------------------------------------------------------------------------------------------------------- |\n| **Neon Postgres / Fortress**  | The only canonical structured source of truth. It holds approved entity state, versions, relationships, evidence, audit and promotion records. |\n| **D1/KV / Operational Spine** | Rebuildable cache, short-lived context, receipt, idempotency and projection state. It cannot become a second truth store.                      |\n| **R2 and vectors**            | Raw evidence and derived retrieval indexes. Neither is canonical business truth.                                                               |\n\nCanonical changes publish an outbox event for Projection Engine cache refresh.\nOperational candidates return only through validation, governance and the\nPromotion Queue. A Workbench, connector, Twin, MCP client or external agent\nnever writes a canonical table directly.\n\n## Database isolation contract\n\nThere are three RLS classes:\n\n1. **Shared registries** — tenant-readable and writable only by the platform\n   operator.\n2. **Direct tenant tables** — every row has `tenant_id`; policies require the\n   transaction-local resolved tenant to equal that value.\n3. **Derived tenant records** — event, signal, relationship and entity-child\n   records must have an explicit tenant envelope and validate that every\n   referenced canonical entity belongs to the same tenant.\n\nThe Gateway resolves identity, membership and role before it establishes the\ndatabase context. It must use a transaction-local setting:\n\n```sql\nBEGIN;\nSELECT set_config('app.current_tenant', :resolved_tenant_id, true);\nSELECT set_config('app.current_role', :resolved_role_key, true);\n-- scoped work\nCOMMIT;\n```\n\n`SET LOCAL` / transaction-local `set_config(..., true)` is mandatory with a\npooled connection. A client must never supply these values as authority.\n\n## Live Neon hardening status\n\n`042_real_tenant_rls_and_runtime_scope.sql` was applied to the current Neon\nFortress on 2026-07-28. It:\n\n- enabled RLS for all 183 current `spine_*` tables;\n- made tenant scope explicit and non-null for entity lifecycle bindings,\n  sources, versions, relationships, events and signals;\n- added cross-tenant parent/reference checks to their RLS write policies;\n- created tenant-local event idempotency uniqueness;\n- made `spine_events` append-only; and\n- revoked direct canonical writes from the browser-facing `authenticated`\n  database role.\n\nThe RLS smoke test was executed as `authenticated`: the registered Customer\nZero tenant read 8 entities, 1 event, 2 relationships and 1 user; an\nunregistered tenant context read zero rows from each table.\n\nThe table owner (`neondb_owner`) is a trusted service identity with\n`BYPASSRLS`. This is intentional until every server-side runtime path has been\nmigrated to establish scoped transactions. Browser-facing access is not\npermitted to mutate canonical tables. Enabling `FORCE ROW LEVEL SECURITY`\nbefore that runtime change would create an availability failure, not a stronger\ncomplete security model.\n\n## RBAC completion gate\n\nThe live public Spine has `spine_memberships`, `spine_user_roles`,\n`spine_permissions` and `spine_role_permissions`, but it currently has no\npublic `spine_roles` rows or membership/user-role bindings. The Hub has role\nkeys (`admin`, `ops_manager`, `project_lead`, `viewer`), but its tenant uses a\ndifferent UUID namespace from public Spine's text tenant ID.\n\nDo not copy those rows by inference. Before role-level policies can become\nlive, provisioning must create an explicit Hub-tenant ↔ Spine-tenant binding\nand populate roles and memberships from verified Clerk identity subjects. Until\nthen, tenant RLS is live; role/field/projection authorisation remains a\nGateway/Projection Engine activation gate, not a completed claim.\n\n## Related implementation documents\n\n- [Spine entity table schema](SPINE_ENTITY_TABLE_SCHEMA.md)\n- [Tenant Spine Fortress and operational flow](TENANT_SPINE_FORTRESS_OPERATIONAL_FLOW_MODEL.md)\n- [App-category, entity and domain classification](APP_CATEGORY_ENTITY_DOMAIN_CLASSIFICATION.md)\n- [RLS hardening migration](../../migrations/042_real_tenant_rls_and_runtime_scope.sql)\n"}