📐 Spec

Spec-17-Schema-and-Migration

4 min readSpec contributor · Node operatorDraft · v0.9.0aN

What this spec defines

The stable storage and migration contract for Stigmem nodes: what the schema must preserve, how migrations are ordered and tracked, and what storage backends must guarantee.

Extraction status

This file contains the ADR-010 prose extraction for schema and migration semantics. It intentionally describes schema contracts and migration behavior, not an exhaustive dump of every reference-node SQL migration.

Legacy version labels from archived source material are normalized to the current v0.9.0a1 protocol line here. Historical wording remains available in spec/archive/evolution/ and spec/EVOLUTION.md.

Migration-versioned schema

Production nodes SHOULD use a migration-versioned schema. The reference node uses numbered SQL migration files applied at startup and records each applied migration in schema_migrations.

Migrations MUST be idempotent and apply in deterministic order.

A migration whose version is already present in schema_migrations MUST be skipped without re-running its DDL or DML. For file-backed reference implementations, sorting migration filenames by stem is sufficient.

Schema migration cursor

Nodes MUST persist a migration cursor equivalent to:

schema_migrations {
  version:    string
  applied_at: ISO 8601 UTC timestamp
}

The cursor is part of the node's operational state. Snapshots and backups SHOULD include it so restores can prove which migrations have already been applied.

Additive evolution

Stable migrations SHOULD be additive:

Add columns

Instead of removing or renaming existing columns.

Add tables

Instead of changing existing table semantics in place.

Add indexes

Without changing query-visible behavior.

Preserve rows

During upgrade.

Breaking storage rewrites require an explicit migration plan, rollback guidance, and conformance coverage.

They MUST NOT be hidden inside ordinary additive migrations.

Fact storage requirements

The facts table is the single source of truth for assertions. Each row maps to the atomic fact shape defined by Spec-01-Fact-Model.

At minimum, storage MUST preserve:

fact id

entity

relation

typed value

source

timestamp

optional valid_until

confidence

scope

HLC

When enabled.

Federation provenance

Fields needed by Spec-05-Federation-Trust.

Core integrity fields

Assigned by later core specs.

Facts are append-oriented.

Retractions, confidence changes, and conflict resolutions are represented as new records or associated protocol records, not silent mutation of the original assertion.

Required index families

Implementations SHOULD provide indexes or equivalent backend-native access paths for:

(entity, relation)

(entity, relation, scope)

scope

timestamp

HLC / cursor fields

Used by replication.

conflict status

audit events

By peer/principal and timestamp.

expiry pruning

For nonce or replay caches.

The exact index names are implementation details.

Federation and audit storage

Nodes that support federation MUST persist peer state, replication cursors, replay/nonce state, and federation audit events. These records support Spec-05-Federation-Trust, Spec-09-Audit-Log, and Spec-11-Replay-Protection.

Federation metadata MUST be sufficient to resume replication after process restart.

Without duplicating accepted facts or skipping eligible facts.

Alias and compatibility storage

Nodes that accept legacy non-canonical entity URIs SHOULD provide alias storage or migration tooling that maps raw entity/source values to canonical forms.

Alias storage MUST NOT silently rewrite historical fact provenance. It is a compatibility aid for query and migration workflows.

Storage backend contract

A storage backend MUST provide:

Capability

Requirement

Notes

Connection lifecycle

MUST

Manage open and close.

Transactions

MUST

Commit on success, roll back on exception.

Row access

MUST

By column name.

Migration runner

MUST

Idempotent.

Snapshot export/import

SHOULD

When supported by the backend.

Backends MUST preserve the same logical schema contract even when SQL dialects or execution mechanics differ.

Plugin migration boundary

Core migrations are applied by the node. Plugin-declared migrations, when enabled by the plugin infrastructure, MUST register through the migration hook surface and MUST remain namespaced to the declaring plugin.

Plugin migration graph resolution, downgrade handling, and package discovery are outside the v0.9.0a1 stable schema contract.

Out of scope

This spec does not define:

Every reference-node migration

Backend-specific DDL syntax

Operator backup procedures

Plugin package discovery

Downgrade semantics

Future CID/tombstone migrations

Beyond their stable component specs.