Unclassified

Canonical Spine Schema Foundation

Status: derived canonical storage contract
Date: 2026-07-28
Authority: ARCHITECTURE_LOCK.md → IMPLEMENTATION_CONTRACT_v1.1.md → CONSOLIDATED_PRODUCT_PLATFORM_CONTRACT_v1.2.md

Decision

IntegrateWise uses a hybrid canonical Spine:

canonical entity kernel
+ typed entity domains
+ classification registries
+ events, relationships, evidence and versions
+ work, communication, knowledge and memory
+ projection metadata
+ governance and runtime state

It does not use one untyped EAV table, separate department schemas, or
provider-specific business tables. A tenant owns one Spine. Departments and
roles consume RBAC-governed projections of that Spine.

app category    = source classification
business domain = semantic / policy classification
lifecycle       = operational routing
role            = projection consumer and authority context

Non-negotiable identity and scope model

spine_entities supplies the stable canonical entity identity. Its primary
key is the entity identifier; spine_network_id is never an entity primary
key. A network scopes the tenant's connected graph and can contain many
entities, connections, workspaces and projections.

Every tenant-owned canonical or runtime row must carry tenant_id. New
canonical writes also carry the complete scope envelope:

Field	Meaning
`tenant_id`	Isolation boundary and policy root.
`tenant_account_id`	The tenant's operating account; not a customer/business account.
`spine_network_id`	The tenant Spine Network containing connector and graph context.
`organization_id`, `workspace_id`	Optional narrower context; neither replaces tenant scope.
`connector_binding_id`, `spine_connection_id`	Provider-installation and connection lineage.
`trace_id`, `correlation_id`, `causation_id`, `idempotency_key`	End-to-end execution and replay traceability.

department_id can exist as an organisational entity or projection input. It
must not determine a table family, database schema, or tenant boundary.

Storage planes

Plane	Canonical responsibility
Identity and tenancy	tenant, organisation, workspace, user, membership, role, permission, consent and delegation.
Ontology and registries	entity, field, relationship, lifecycle, event, signal, capability, policy, projection and widget definitions. Platform-owned and tenant-readable.
Entity kernel and typed domains	entity identity, source aliases, versions, field provenance, typed business entities and graph relationships.
Work, communication and knowledge	work items, tasks, decisions, approvals, messages, meetings, documents, knowledge and evidence.
Memory and promotion	personal, organisational, agent and operational memory; promotion is the governed memory-to-truth route.
Integration, capability and governance	connector and connection bindings, sync, MCP, capability execution, policy, audit and outcome verification.
Projection and canvas	projection rules, role/domain/field/row filters, canvas composition and cache-refresh metadata.
Runtime reliability	idempotency, retries, dead letters, reconciliation, verification and health. Runtime records are not business truth.

Typed domain tables use provider-neutral names, for example
finance_invoices, crm_accounts and operations_projects (or their
canonical spine_* equivalents). Provider identity belongs in
spine_entity_sources, never in a typed business table name.

Canonical versus operational boundary

Store	Role
Neon Postgres / Fortress	The only canonical structured source of truth. It holds approved entity state, versions, relationships, evidence, audit and promotion records.
D1/KV / Operational Spine	Rebuildable cache, short-lived context, receipt, idempotency and projection state. It cannot become a second truth store.
R2 and vectors	Raw evidence and derived retrieval indexes. Neither is canonical business truth.

Canonical changes publish an outbox event for Projection Engine cache refresh.
Operational candidates return only through validation, governance and the
Promotion Queue. A Workbench, connector, Twin, MCP client or external agent
never writes a canonical table directly.

Database isolation contract

There are three RLS classes:

Shared registries — tenant-readable and writable only by the platform
operator.
Direct tenant tables — every row has tenant_id; policies require the
transaction-local resolved tenant to equal that value.
Derived tenant records — event, signal, relationship and entity-child
records must have an explicit tenant envelope and validate that every
referenced canonical entity belongs to the same tenant.

The Gateway resolves identity, membership and role before it establishes the
database context. It must use a transaction-local setting:

BEGIN;
SELECT set_config('app.current_tenant', :resolved_tenant_id, true);
SELECT set_config('app.current_role', :resolved_role_key, true);
-- scoped work
COMMIT;

SET LOCAL / transaction-local set_config(..., true) is mandatory with a
pooled connection. A client must never supply these values as authority.

Live Neon hardening status

042_real_tenant_rls_and_runtime_scope.sql was applied to the current Neon
Fortress on 2026-07-28. It:

enabled RLS for all 183 current spine_* tables;
made tenant scope explicit and non-null for entity lifecycle bindings,
sources, versions, relationships, events and signals;
added cross-tenant parent/reference checks to their RLS write policies;
created tenant-local event idempotency uniqueness;
made spine_events append-only; and
revoked direct canonical writes from the browser-facing authenticated
database role.

The RLS smoke test was executed as authenticated: the registered Customer
Zero tenant read 8 entities, 1 event, 2 relationships and 1 user; an
unregistered tenant context read zero rows from each table.

The table owner (neondb_owner) is a trusted service identity with
BYPASSRLS. This is intentional until every server-side runtime path has been
migrated to establish scoped transactions. Browser-facing access is not
permitted to mutate canonical tables. Enabling FORCE ROW LEVEL SECURITY
before that runtime change would create an availability failure, not a stronger
complete security model.

RBAC completion gate

The live public Spine has spine_memberships, spine_user_roles,
spine_permissions and spine_role_permissions, but it currently has no
public spine_roles rows or membership/user-role bindings. The Hub has role
keys (admin, ops_manager, project_lead, viewer), but its tenant uses a
different UUID namespace from public Spine's text tenant ID.

Do not copy those rows by inference. Before role-level policies can become
live, provisioning must create an explicit Hub-tenant ↔ Spine-tenant binding
and populate roles and memberships from verified Clerk identity subjects. Until
then, tenant RLS is live; role/field/projection authorisation remains a
Gateway/Projection Engine activation gate, not a completed claim.