Every SQLite table ai-memory creates, every column, every index, every foreign key. Postgres mirror via the SAL adapter (--features sal,sal-postgres; Apache AGE Cypher graph backend). Schema version: v57 at v0.7.0 (was v33 at v0.6.4; ladder v34 → v57 includes V-4 cross-row hash chain, recursive-learning depth columns, Batman Form-4/5 provenance + confidence, optimistic-concurrency version column at v45, federation_push_dlq table at v48, archive_memories +14 columns at v49, per-namespace K8 quota dimension extension at v50 #1156, federation_nonce_cache persistence at v51 #1255, transcript_line_dedup idempotency table at v52 #1389, memories_au FTS5 trigger scoping at v53 #1418, tier-default expiry backfill onto legacy NULL-expiry rows at v54 #1466, and the sargable list_memories_updated_since federation-catchup rewrite + idx_memories_updated_at at v55 #1476, composite list-ordering indexes at v56 + the postgres stored tsvector at v57 — both #1579). Migration is automatic on first daemon start.
Every relation flows from the central memories table. Foreign-key cascade-on-delete protects against orphan rows. memory_links is the KG primitive; pending_actions is the governance queue; sync_state is the federation cursor table; audit_log + signed_events are the V-4 tamper-evident chain; signed_events_dlq + federation_push_dlq are the v0.7.0 reliability DLQs (Track D #933). Table list authoritative against src/storage/migrations.rs CREATE TABLE statements at CURRENT_SCHEMA_VERSION = 57.
Plus governance + identity + lifecycle tables introduced across the v34–v57 ladder (and visible via SELECT name FROM sqlite_master WHERE type='table' on a fresh DB): agent_quotas (K8 per-agent quotas; per-namespace PK dimension at v50 #1156), governance_rules (K11 substrate rules engine), skills + skill_resources (Agent Skills, L1-5), recall_observations (Form-5 recall ledger), offloaded_blobs (QW-3 context-offload), memory_transcripts + memory_transcript_links (sidechain transcripts, I2), subscription_dlq (K7 webhook DLQ), federation_nonce_cache (v51 #1255 replay-prevention persistence), transcript_line_dedup (v52 #1389 L4 idempotency), plus migration tracking (schema_version shown above). Agents, entities, personas, and namespace standards are NOT side tables — they are memories (reserved namespaces / standard memories) resolved via namespace_meta + entity_aliases. Verified against src/storage/migrations.rs CREATE TABLE census at v0.7.0 release HEAD.
tier determines lifecycle (short / mid / long / archived). FTS5 virtual table mirrors title+content for keyword search.| Column | Type | Notes |
|---|---|---|
| PKid | TEXT | UUIDv4 stringified. Stable across federation. |
| tier | TEXT NOT NULL | One of short · mid · long (canonical Tier enum in src/models/memory.rs; archived rows move to the archived_memories table, there is no archived tier value). Drives TTL + visibility. |
| namespace | TEXT NOT NULL | Hierarchical path with / delimiter (v0.6.3 Stream A). Max length 512 chars, max depth 8 segments. |
| title | TEXT NOT NULL | Human-readable summary, ≤ 512 chars (MAX_TITLE_LEN). FTS5-indexed. |
| content | TEXT NOT NULL | Body text. Up to 65,536 bytes (MAX_CONTENT_SIZE = 64 KiB). FTS5-indexed. |
| tags | TEXT | JSON array. Validator caps count at 50 (MAX_TAGS_COUNT), length at 128 bytes each (MAX_TAG_LEN). |
| priority | INTEGER | 1-10 scale. Drives auto-promote eligibility. |
| confidence | REAL | 0.0-1.0. Displayed as % when < 1.0. |
| source | TEXT | Origin tag (VALID_SOURCES in src/validate.rs): user · nhi (v0.7 vendor-neutral default) · claude (deprecated, back-compat) · hook · api · cli · import · consolidation · system · chaos · notify. |
| access_count | INTEGER | Bumped on each recall hit. Triggers auto-promote at ≥ 5. |
| created_at | TEXT | RFC3339 UTC. Immutable. |
| updated_at | TEXT | RFC3339 UTC. Bumped on update. |
| last_accessed_at | TEXT NULL | RFC3339. Touched on recall. Drives recency-decay scoring. |
| expires_at | TEXT NULL | RFC3339. NULL = never. GC sweep purges past-due. |
| embedding | BLOB NULL | f32 array — 384 dims (all-MiniLM-L6-v2, local candle) or 768 dims (nomic-embed-text-v1.5 via Ollama); declared per row by embedding_dim. Set if semantic+ tier. |
| embedding_dim | INTEGER NULL | v0.6.3.1 data-integrity guard — declared dimension of the stored embedding (384 or 768) so mixed-model rows can't cross-contaminate cosine scoring. |
| metadata | TEXT | JSON. Holds agent_id, scope, governance policy, chunked_from. |
| v0.7reflection_depth | INTEGER | Recursive-learning Task 1/8 — depth in the reflection recursion tree; 0 for caller-minted rows. |
| v0.7memory_kind | TEXT NULL | Batman Form-6 vocabulary: Observation / Reflection / Persona / Concept / Entity / Claim / Relation / Event / Conversation / Decision. |
| v0.7atomised_into / atom_of | TEXT NULL | WT-1 atomisation provenance (parent → atoms, atom → parent). |
| v0.7entity_id / persona_version | TEXT / INTEGER NULL | QW-2 persona-as-artifact columns. |
| v0.7citations / source_uri / source_span | TEXT NULL | Form-4 fact provenance. |
| v0.7confidence_source / confidence_signals / confidence_decayed_at | TEXT NULL | Form-5 confidence calibration + decay. |
| v0.7mentioned_entity_id | TEXT NULL | Entity-mention back-reference. |
| v0.7encrypted_envelope | BLOB NULL | Schema v44 (#228) — E2E content encryption at rest: X25519 ephemeral pubkey + ChaCha20-Poly1305 AEAD-sealed ciphertext envelope. |
| v0.7version | BIGINT | Schema v45 Gap-1 optimistic concurrency for memory_update; defaults to 1 on legacy rows. |
valid_from, valid_until, observed_by, signature); v0.7.0 populates signature with Ed25519 per-link attestation (was placeholder pre-v0.7). 6 link variants at v0.7.0 (was 4 at v0.6.x).| Column | Type | Notes |
|---|---|---|
| FKsource_id | TEXT NOT NULL | → memories(id) ON DELETE CASCADE |
| FKtarget_id | TEXT NOT NULL | → memories(id) ON DELETE CASCADE |
| relation | TEXT NOT NULL | 6 variants at v0.7.0: related_to, supersedes, contradicts, derived_from, reflects_on (v0.7 recursive learning Task 1/8), derives_from (v0.7 atomisation atom→parent). Canonical enum in src/models/link.rs::MemoryLinkRelation. |
| created_at | TEXT NOT NULL | RFC3339 UTC. |
| valid_from | TIMESTAMP NULL | v0.6.3: when the assertion became true. Backfilled to source's created_at on migration. |
| valid_until | TIMESTAMP NULL | v0.6.3: when the assertion was superseded. NULL = still valid. |
| observed_by | TEXT NULL | v0.6.3: agent_id of the observer. Powers per-agent visibility filter. |
| v0.7signature | BLOB NULL | v0.7.0 POPULATED: Ed25519 signature for attested provenance (was v0.6.3 placeholder). Per-row Ed25519 sig population gated on the resolved daemon agent_id having a *.priv keypair on disk. |
| v0.7attest_level | TEXT NULL | v0.7.0 (H4 AttestLevel enum, src/models/link.rs): one of unsigned · self_signed · peer_attested · signed_by_peer · daemon_signed. |
| version | INTEGER NOT NULL | Currently 57 (v0.7.0). Canonical: CURRENT_SCHEMA_VERSION = 57 in src/storage/migrations.rs. Bumped only on additive migrations. v34 → v57 covers V-4 cross-row hash chain, recursive-learning depth columns, Batman Form-4/5 provenance + confidence, optimistic-concurrency version column at v45, federation_push_dlq table at v48, archive_memories +14 columns at v49, per-namespace K8 quota dimension extension at v50 (#1156: agent_quotas PK (agent_id) → (agent_id, namespace); pre-v50 rows backfill to the _global sentinel), federation_nonce_cache persistence at v51 (#1255), transcript_line_dedup idempotency table at v52 (#1389), memories_au FTS5 trigger scoping at v53 (#1418), tier-default expiry backfill onto legacy NULL-expiry mid/short rows at v54 (#1466, in-code arm, no new .sql), and the sargable list_memories_updated_since federation-catchup rewrite + idx_memories_updated_at at v55 (#1476; postgres version-stamp no-op), composite list/archive ordering indexes at v56 (#1579 A2+B6d; postgres no-op), and the postgres stored generated tsvector + memories_tsv_gin at v57 (#1579 B2; sqlite no-op). |
memories table. Memories transition here on memory_delete or curator soft-delete. Excluded from default reads. Restorable.Mirrors memories schema column-for-column, plus: | ||
| archived_at | TEXT NOT NULL | RFC3339 UTC. Drives archive purge --older-than N. |
| archived_reason | TEXT NULL | Free-form (e.g. "user_deleted", "consolidation_supersede", "auto_curator_dedup"). |
last_pulled_at advances after successful /api/v1/sync/since pulls.| Column | Type | Notes |
|---|---|---|
| PKagent_id | TEXT NOT NULL | Local node's agent_id. |
| PKpeer_url | TEXT NOT NULL | Federation peer's HTTPS URL. |
| last_pulled_at | TEXT NULL | RFC3339 UTC of last successful pull from this peer. |
| last_pushed_at | TEXT NULL | RFC3339 UTC of last push. |
| Column | Type | Notes |
|---|---|---|
| PKid | TEXT | UUIDv4. |
| url | TEXT NOT NULL | Webhook endpoint. Validated against SSRF guard (validate_url_dns). |
| events | TEXT NOT NULL | Comma-separated or *. e.g. store,delete,contradiction. |
| namespace_filter | TEXT NULL | Glob pattern. NULL = all namespaces. |
| agent_filter | TEXT NULL | Filter by metadata.agent_id. NULL = all agents. |
| secret_hash | TEXT NULL | SHA-256 of the plaintext shared secret. Plaintext returned once on subscribe. |
| created_by | TEXT NULL | agent_id of subscriber. |
| created_at | TEXT NOT NULL | RFC3339 UTC. |
| dispatch_count | INTEGER | Successful dispatches. |
| failure_count | INTEGER | Failed dispatches (non-2xx OR connect timeout). |
Every schema change is a forward migration applied at daemon startup. Idempotent so re-runs are safe. v15 was the v0.6.3 Stream B addition (KG temporal-validity columns + entity_aliases). v0.6.3.1 added the v17/v18/v19 ladder: v17 adds governance.inherit backfill (`migrations/sqlite/0012_governance_inherit.sql`), v18 adds embedding_dim guard + archive lossless (`0011_v0631_data_integrity.sql`), v19 adds webhook event_types column + index (`0013_webhook_event_types.sql`). Older versions (≤ v14) remain inline in src/storage/migrations.rs::migrate.