Every SQLite table ai-memory creates, every column, every index, every foreign key. Postgres mirror via the SAL adapter (--features sal). Schema version: v19 (Patch 1; ladder v15→v17→v18→v19 is automatic on first run).
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.
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 · archived. Drives TTL + visibility. |
| namespace | TEXT NOT NULL | Hierarchical path with / delimiter (v0.6.3 Stream A). Max depth 8 segments. |
| title | TEXT NOT NULL | Human-readable summary, ≤ 256 chars. FTS5-indexed. |
| content | TEXT NOT NULL | Body text. Up to 1 MiB. FTS5-indexed. |
| tags | TEXT | JSON array. Validator caps count at 32, length at 64 each. |
| priority | INTEGER | 1-10 scale. Drives auto-promote eligibility. |
| confidence | REAL | 0.0-1.0. Displayed as % when < 1.0. |
| source | TEXT | Origin tag: user · claude · hook · api · import · cli · consolidation. |
| 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, 768 dims (nomic-embed-text). Set if semantic+ tier. |
| metadata | TEXT | JSON. Holds agent_id, scope, governance policy, chunked_from. |
| 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 | One of: related_to, supersedes, refines, contradicts, derived_from, superseded_duplicate, plus arbitrary user-defined. |
| created_at | TEXT NOT NULL | RFC3339 UTC. |
| NEWvalid_from | TIMESTAMP NULL | v0.6.3: when the assertion became true. Backfilled to source's created_at on migration. |
| NEWvalid_until | TIMESTAMP NULL | v0.6.3: when the assertion was superseded. NULL = still valid. |
| NEWobserved_by | TEXT NULL | v0.6.3: agent_id of the observer. Powers per-agent visibility filter. |
| NEWsignature | BLOB NULL | v0.6.3 placeholder · v0.7 will populate with Ed25519 signature for attested provenance. |
| version | INTEGER NOT NULL | Currently 19 (Patch 1). Bumped only on additive migrations. Migration ladder v15→v17→v18→v19 runs automatically at daemon start. |
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 db.rs::migrate.