Repository navigation
Expand file tree
/
Copy pathinit.sql
More file actions
65 lines (59 loc) · 2.33 KB
/
Copy pathinit.sql
File metadata and controls
65 lines (59 loc) · 2.33 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
CREATE EXTENSION IF NOT EXISTS vector;
CREATE EXTENSION IF NOT EXISTS age;
CREATE EXTENSION IF NOT EXISTS pg_trgm;
LOAD 'age';
SET search_path TO ag_catalog, public;
CREATE TABLE memories (
id TEXT PRIMARY KEY,
content TEXT NOT NULL,
content_hash TEXT NOT NULL DEFAULT '',
embedding vector(768),
mem_type TEXT NOT NULL DEFAULT 'fact',
scope TEXT NOT NULL DEFAULT 'global',
entity TEXT,
importance FLOAT DEFAULT 1.0,
metadata JSONB DEFAULT '{}',
access_count INT DEFAULT 0,
superseded_by TEXT,
deleted_at TIMESTAMPTZ,
expires_at TIMESTAMPTZ,
valid_from TIMESTAMPTZ NOT NULL DEFAULT NOW(),
valid_to TIMESTAMPTZ,
source TEXT,
agent_id TEXT,
confidence FLOAT CHECK (confidence IS NULL OR (confidence >= 0 AND confidence <= 1)),
tags TEXT[] NOT NULL DEFAULT '{}',
pinned BOOLEAN NOT NULL DEFAULT FALSE,
hard_deleted_at TIMESTAMPTZ,
created_at TIMESTAMPTZ DEFAULT NOW(),
updated_at TIMESTAMPTZ DEFAULT NOW()
);
CREATE TABLE entities (
id SERIAL PRIMARY KEY,
name TEXT NOT NULL UNIQUE,
type TEXT,
created_at TIMESTAMPTZ DEFAULT NOW()
);
CREATE TABLE relations (
id SERIAL PRIMARY KEY,
source_id INT REFERENCES entities(id) ON DELETE CASCADE,
target_id INT REFERENCES entities(id) ON DELETE CASCADE,
relation_type TEXT,
weight FLOAT DEFAULT 1.0,
created_at TIMESTAMPTZ DEFAULT NOW()
);
CREATE INDEX idx_memories_embedding ON memories USING ivfflat (embedding vector_cosine_ops) WITH (lists = 100);
CREATE INDEX idx_memories_tsvector ON memories USING GIN (to_tsvector('english', content));
CREATE INDEX idx_memories_type ON memories(mem_type);
CREATE INDEX idx_memories_entity ON memories(entity);
CREATE INDEX idx_memories_hash ON memories(content_hash);
CREATE INDEX idx_memories_active ON memories(superseded_by) WHERE superseded_by IS NULL;
CREATE INDEX idx_memories_validity ON memories(valid_from, valid_to);
CREATE INDEX idx_memories_tags ON memories USING GIN (tags);
CREATE INDEX idx_memories_pinned ON memories(id) WHERE pinned;
CREATE INDEX idx_entities_name ON entities(name);
CREATE INDEX idx_entities_type ON entities(type);
CREATE INDEX idx_relations_source ON relations(source_id);
CREATE INDEX idx_relations_target ON relations(target_id);
CREATE INDEX idx_relations_type ON relations(relation_type);
SELECT create_graph('memory_graph');