Mnemosyne MCP Server: Database Schema

Level 2 (Topic) — PostgreSQL schema and pgvector storage design.

Concept

Mnemosyne persists memories in a PostgreSQL database using the pgvector extension for vector similarity search. The schema is managed externally (via CNPG or manual migration) — the server only reads and writes to the memories table.

The `memories` Table

Column	Type	Description
`id`	UUID	Primary key, generated by the database
`timestamp`	TIMESTAMPTZ	When the memory was created
`content`	TEXT	Full text of the memory
`embedding`	vector(N)	768-dimensional embedding vector (pgvector)
`content_hash`	VARCHAR(64)	SHA-256 hex digest for deduplication (UNIQUE)
`last_seen_at`	TIMESTAMPTZ	Updated on duplicate insert (ON CONFLICT)

The unique constraint on content_hash is the second layer of deduplication: if the same content is ingested twice, the second insert silently becomes an UPDATE of last_seen_at via ON CONFLICT DO UPDATE.

Dimension Auto-Detection

On startup, db.New() queries the existing table for the vector dimension:

SELECT vector_dims(embedding) FROM memories WHERE embedding IS NOT NULL LIMIT 1;

If the query returns a row → dimension is set to that value (e.g., 768)
If the query returns no rows (empty table) → fallback to 3072
If the query errors → startup fails

TD-002: the 3072 fallback is a historical default for text-embedding-004. The current model gemini-embedding-001 produces 768-dim vectors. On a fresh database, inserts will fail with a pgvector dimension mismatch.

Query Methods

InsertMemory

INSERT INTO memories (timestamp, content, embedding, content_hash)
VALUES ($1, $2, $3, $4)
ON CONFLICT (content_hash) DO UPDATE SET last_seen_at = CURRENT_TIMESTAMP

Search (cosine similarity)

SELECT id, timestamp, content
FROM memories
WHERE 1=1
  AND timestamp >= $2   -- optional temporal filter
  AND timestamp <= $3   -- optional temporal filter
ORDER BY embedding <=> $1
LIMIT $4

The <=> operator is the pgvector cosine distance operator. Lower distance = higher similarity.

List

SELECT id, timestamp, content FROM memories ORDER BY timestamp DESC LIMIT $1;

GetMemoryByID

SELECT content FROM memories WHERE id = $1;

DeleteMemory

DELETE FROM memories WHERE id = $1;

CheckMemoryExists

SELECT EXISTS(SELECT 1 FROM memories WHERE content_hash = $1);

Temporal Filter Gap

The Search method accepts start and end time parameters. The DB layer constructs the SQL with conditional AND timestamp >= $N / AND timestamp <= $N clauses. However, the MCP tool retrieve_memories does not expose these filters — they are always passed as nil. Temporal search is implemented but unreachable from any client.

Code Paths

File	Function	Role
`internal/db/db.go`	`New()`	Dimension auto-detection, connection pool
`internal/db/db.go`	`InsertMemory()`	Upsert with dedup
`internal/db/db.go`	`Search()`	Cosine similarity with optional time filters
`internal/db/db.go`	`List()`	Recent memories
`internal/db/db.go`	`GetMemoryByID()`	Single memory retrieval
`internal/db/db.go`	`DeleteMemory()`	Memory deletion
`internal/db/db.go`	`CheckMemoryExists()`	Hash existence check

Mnemosyne MCP Server: Database Schema#

Concept#

The memories Table#

Dimension Auto-Detection#

Query Methods#

InsertMemory#

Search (cosine similarity)#

List#

GetMemoryByID#

DeleteMemory#

CheckMemoryExists#

Temporal Filter Gap#

Code Paths#

See Also#