Database Schema

Database Schema

MemoryVault uses Cloudflare D1 (SQLite). The schema is defined in schema.sql and supplemented by runtime migrations in src/db.ts.

Tables

users

User accounts for multi-tenant auth.

Column	Type	Constraints	Description
id	TEXT	PRIMARY KEY	UUID
email	TEXT	NOT NULL, UNIQUE	Normalized lowercase email
password_hash	TEXT	NOT NULL	PBKDF2-SHA256 hash
display_name	TEXT		Optional display name
created_at	INTEGER	NOT NULL	Unix timestamp
updated_at	INTEGER	NOT NULL	Unix timestamp

brains

Isolated memory containers (tenants).

Column	Type	Constraints	Description
id	TEXT	PRIMARY KEY	UUID
name	TEXT	NOT NULL	Brain display name
slug	TEXT	UNIQUE	URL-safe identifier
owner_user_id	TEXT	FK users	Owner (nullable)
created_at	INTEGER	NOT NULL	Unix timestamp
updated_at	INTEGER	NOT NULL	Unix timestamp

A default legacy-default-brain is seeded for legacy bearer token auth.

brain_memberships

Maps users to brains with roles.

Column	Type	Constraints	Description
brain_id	TEXT	PK, FK brains	Brain reference
user_id	TEXT	PK, FK users	User reference
role	TEXT	DEFAULT 'member'	owner or member
created_at	INTEGER	NOT NULL	Unix timestamp

memories

Core memory storage.

Column	Type	Constraints	Description
id	TEXT	PRIMARY KEY	UUID
brain_id	TEXT	NOT NULL, FK brains	Tenant isolation
type	TEXT	NOT NULL, CHECK	note, fact, or journal
title	TEXT		Title (notes)
key	TEXT		Unique lookup key (facts)
content	TEXT	NOT NULL	Memory body
tags	TEXT		Comma-separated tags
source	TEXT		Origin/attribution
confidence	REAL	DEFAULT 0.7, CHECK 0-1	Base confidence
importance	REAL	DEFAULT 0.5, CHECK 0-1	Base importance
archived_at	INTEGER		Null = active
created_at	INTEGER	NOT NULL	Unix timestamp
updated_at	INTEGER	NOT NULL	Unix timestamp

Indexes: type, key, created DESC, archived, importance DESC, confidence DESC, (brain_id, created DESC), (brain_id, key)

memory_links

Typed relationships between memories.

Column	Type	Constraints	Description
id	TEXT	PRIMARY KEY	UUID
brain_id	TEXT	NOT NULL, FK brains	Tenant isolation
from_id	TEXT	NOT NULL, FK memories	Source memory
to_id	TEXT	NOT NULL, FK memories	Target memory
relation_type	TEXT	DEFAULT 'related', CHECK	Relationship type
label	TEXT		Optional description
created_at	INTEGER	NOT NULL	Unix timestamp

Relation types: related, supports, contradicts, supersedes, causes, example_of

Indexes: from_id, to_id, relation_type, (brain_id, from_id), (brain_id, to_id)

memory_changelog

Audit log for memory operations.

Column	Type	Constraints	Description
id	TEXT	PRIMARY KEY	UUID
brain_id	TEXT	NOT NULL, FK brains	Tenant isolation
event_type	TEXT	NOT NULL	e.g., memory_saved, link_created
entity_type	TEXT	NOT NULL	memory or link
entity_id	TEXT	NOT NULL	Reference ID
summary	TEXT	NOT NULL	Human-readable description
payload	TEXT		JSON payload
created_at	INTEGER	NOT NULL	Unix timestamp

auth_sessions

OAuth and user session tracking.

Column	Type	Constraints	Description
id	TEXT	PRIMARY KEY	Session UUID
user_id	TEXT	NOT NULL, FK users	Session owner
brain_id	TEXT	NOT NULL, FK brains	Authorized brain
client_id	TEXT		OAuth client (null for human sessions)
refresh_hash	TEXT	NOT NULL, UNIQUE	SHA256 of refresh token
expires_at	INTEGER	NOT NULL	Session expiry
created_at	INTEGER	NOT NULL	Creation time
used_at	INTEGER	NOT NULL	Last access time
revoked_at	INTEGER		Null = active
replaced_by	TEXT		Successor session ID

oauth_clients

Registered OAuth clients.

Column	Type	Constraints	Description
id	TEXT	PRIMARY KEY	Internal ID
client_id	TEXT	NOT NULL, UNIQUE	Public ID (mcp_*)
client_name	TEXT		Display name
redirect_uris	TEXT	NOT NULL	JSON array
grant_types	TEXT	NOT NULL	JSON array
response_types	TEXT	NOT NULL	JSON array
token_endpoint_auth_method	TEXT	DEFAULT 'none'	Auth method
client_secret_hash	TEXT		SHA256 (confidential clients)
client_secret_expires_at	INTEGER	DEFAULT 0	0 = never
created_at	INTEGER	NOT NULL	Creation time
updated_at	INTEGER	NOT NULL	Last update

oauth_authorization_codes

Short-lived PKCE authorization codes.

Column	Type	Constraints	Description
id	TEXT	PRIMARY KEY	Internal ID
code	TEXT	NOT NULL, UNIQUE	Opaque code
client_id	TEXT	NOT NULL	Issuing client
redirect_uri	TEXT	NOT NULL	Validated redirect
user_id	TEXT	NOT NULL	Authenticated user
brain_id	TEXT	NOT NULL	Granted brain
code_challenge	TEXT	NOT NULL	S256 challenge
code_challenge_method	TEXT	DEFAULT 'S256'	Always S256
scope	TEXT		Requested scope
resource	TEXT		Requested resource
created_at	INTEGER	NOT NULL	Creation time
expires_at	INTEGER	NOT NULL	TTL: 10 minutes
used_at	INTEGER		Null = unused

brain_source_trust

Per-source trust weights for scoring.

Column	Type	Constraints	Description
id	TEXT	PRIMARY KEY	UUID
brain_id	TEXT	NOT NULL, FK brains	Tenant isolation
source_key	TEXT	NOT NULL	Normalized source
trust	REAL	DEFAULT 0.5, CHECK 0-1	Trust weight
notes	TEXT		Rationale
created_at	INTEGER	NOT NULL	Creation time
updated_at	INTEGER	NOT NULL	Last update

Unique: (brain_id, source_key)

brain_policies

Per-brain behavior configuration.

Column	Type	Constraints	Description
brain_id	TEXT	PRIMARY KEY, FK brains	Brain reference
policy_json	TEXT	NOT NULL	JSON BrainPolicy
created_at	INTEGER	NOT NULL	Creation time
updated_at	INTEGER	NOT NULL	Last update

brain_snapshots

Point-in-time backups.

Column	Type	Constraints	Description
id	TEXT	PRIMARY KEY	UUID
brain_id	TEXT	NOT NULL, FK brains	Brain reference
label	TEXT		User label
summary	TEXT		Description
memory_count	INTEGER	DEFAULT 0	Memories in snapshot
link_count	INTEGER	DEFAULT 0	Links in snapshot
payload_json	TEXT	NOT NULL	Serialized data
created_at	INTEGER	NOT NULL	Creation time

memory_conflict_resolutions

Contradiction tracking and resolution.

Column	Type	Constraints	Description
id	TEXT	PRIMARY KEY	UUID
brain_id	TEXT	NOT NULL, FK brains	Tenant isolation
pair_key	TEXT	NOT NULL	Sorted a_id|b_id
a_id	TEXT	NOT NULL	First memory
b_id	TEXT	NOT NULL	Second memory
status	TEXT	NOT NULL	needs_review, resolved, superseded, dismissed
canonical_id	TEXT		Winning memory
note	TEXT		Resolution notes
created_at	INTEGER	NOT NULL	Creation time
updated_at	INTEGER	NOT NULL	Last update

Unique: (brain_id, pair_key)

memory_entity_aliases

Canonical entity mapping.

Column	Type	Constraints	Description
id	TEXT	PRIMARY KEY	UUID
brain_id	TEXT	NOT NULL, FK brains	Tenant isolation
canonical_memory_id	TEXT	NOT NULL	Primary entity
alias_memory_id	TEXT	NOT NULL	Alias entity
note	TEXT		Mapping rationale
confidence	REAL	DEFAULT 0.9, CHECK 0-1	Mapping confidence
created_at	INTEGER	NOT NULL	Creation time
updated_at	INTEGER	NOT NULL	Last update

Unique: (brain_id, alias_memory_id)

memory_watches

Event subscription configuration.

Column	Type	Constraints	Description
id	TEXT	PRIMARY KEY	UUID
brain_id	TEXT	NOT NULL, FK brains	Tenant isolation
name	TEXT	NOT NULL	Watch name
event_types	TEXT	NOT NULL	JSON array or *
query	TEXT		Optional text filter
webhook_url	TEXT		Delivery endpoint
secret	TEXT		Webhook signing secret
is_active	INTEGER	DEFAULT 1	Boolean active flag
created_at	INTEGER	NOT NULL	Creation time
updated_at	INTEGER	NOT NULL	Last update
last_triggered_at	INTEGER		Last delivery time
last_error	TEXT		Last delivery error

rate_limits

Auth rate limiting (supplemented by KV).

Column	Type	Constraints	Description
ip	TEXT	PK	Client IP
window	INTEGER	PK	Time window
count	INTEGER	DEFAULT 0	Attempt count

Runtime Migrations

On every request, ensureSchema() runs idempotent ALTER TABLE and CREATE INDEX statements to handle schema evolution. These add columns, indexes, and default values that may not be in the base schema.sql for older databases.