KnowledgeStore/Store.pm

filter: Statementstmt 60.0% 3 / 5 Branchbran n/a Conditioncond n/a MC/DCmcdc n/a Subroutinesub 100% 2 / 2 Podpod n/a totaltotal 71.4% 5 / 7 CC 1 SCAR 1.4
CC
1
Cov
60%
BEGIN
1.3
BEGIN
1.0
CRAP 1.1
« KnowledgeStore/ResourceTemplates.pm ↑ Summary KnowledgeStore/TermWeight.pm »
1 package MCP::Server::KnowledgeStore::Store;
2 1 ▶use v5.38;
Subroutine BEGIN: called CC=1 SCAR=0.0
3 0 ▶use Object::Pad;
Subroutine BEGIN: called CC=1 SCAR=2.6
4  
5 our $VERSION = '0.001';
6  
7 # ABSTRACT: Postgres store for memories, skills, specs, agents, and projects
8  
9 class MCP::Server::KnowledgeStore::Store;
10  
11 use Carp qw(croak);
12 use Crypt::PRNG qw(random_bytes);
13 use Digest::SHA qw(sha256_hex);
14 use Mojo::Pg;
15 use Mojo::Util qw(b64_encode);
16 use YAML::XS qw(Load Dump);
17 use MCP::Server::KnowledgeStore::TermWeight;
18  
19 # Either a Mojo::Pg or a connection string. Both machines reach the same
20 # database over the network, which is what makes it the single canonical
21 # store - there is no file tree and no shared mount any more.
22 field $pg : param : reader;
23  
24 ADJUST {
25 $pg = Mojo::Pg->new($pg) unless ref $pg;
26  
27 # Postgres refuses a server-side prepared statement whose result columns
28 # changed shape since it was planned, with "cached plan must not change
29 # result type". A migration that alters a table some statement selects
30 # from is enough. Mojo::Pg runs every query through prepare_cached, so
31 # the broken plan sticks to that pooled connection for the life of the
32 # process: search dies while writes carry on, and only a restart clears
33 # it. Client-side placeholders cost a replan per call, which is nothing
34 # at this traffic, and the whole failure mode goes away.
35 $pg->options->{pg_server_prepare} = 0;
36  
37 $pg->migrations->name('wdms_mcp')->from_data(__PACKAGE__, 'migrations.sql');
38 }
39  
40 # Run pending migrations. Called explicitly rather than from ADJUST so
41 # constructing a store never has a side effect on the database.
42 method migrate { $pg->migrations->migrate; return $self }
43  
44 method db { return $pg->db }
45  
46 sub _check_kind ($kind) {
47 croak "invalid kind '$kind': expected memory, skill or spec"
48 unless defined $kind
49 && ($kind eq 'memory' || $kind eq 'skill' || $kind eq 'spec');
50 return $kind;
51 }
52  
53 # Names are the caller-supplied key for every lookup, so they are held to
54 # the same shape the files used, both here and in a CHECK constraint.
55 # Now supports both slug-style (my-memory-name) and namespace-style (My::Module::Name).
56 sub _check_name ($name) {
57 croak 'name is required' unless defined $name && length $name;
58 croak "invalid name '$name': expected alphanumeric name with optional "
59 . "hyphens, underscores, or colons (e.g., my-memory-name or My::Module::Name)"
60 unless $name =~ /\A[A-Za-z0-9][A-Za-z0-9_:-]*\z/a;
61 return $name;
62 }
63  
64 # Frontmatter is still the wire format - it is what agents already write -
65 # but it is not how anything is stored. On the way in it is split into
66 # columns, on the way out it is rendered back, so the DB holds structured
67 # metadata while clients keep the format they know.
68 method _split ($text) {
69 $text //= '';
70 return ({}, $text) unless $text =~ s/\A---\r?\n(.*?)\r?\n---[ \t]*\r?\n?//s;
71 my $meta = eval { Load($1) } // {};
72 $meta = {} unless ref $meta eq 'HASH';
73 return ($meta, $text);
74 }
75  
76 method _render ($row) {
77 my $meta
78 = { name => $row->{name}, description => $row->{description} // '' };
79 $meta->{metadata}{type} = $row->{type} if defined $row->{type};
80 $meta->{project} = $row->{project} if defined $row->{project};
81  
82 my $head = Dump($meta);
83 $head = "---\n$head" unless $head =~ /\A---\r?\n/;
84 $head =~ s/\n?\z/\n/;
85 return $head . "---\n\n" . ($row->{body} // '') . "\n";
86 }
87  
88 method _entry ($row) {
89 return {
90 name => $row->{name},
91 description => $row->{description} // '',
92 type => $row->{type},
93  
94 # Spec's single required project, still on the revision (as
95 # project_id, resolved to its name below). Memory and skill are
96 # tagged instead - see `projects`.
97 project => $row->{project},
98 projects => $row->{projects} // [],
99 kind => $row->{kind},
100 revision => $row->{revision},
101 author => $row->{author},
102 created_at => $row->{created_at},
103 body => $row->{body},
104 content => $self->_render($row),
105 view_count => $row->{view_count} // 0,
106 useful_count => $row->{useful_count} // 0,
107 last_viewed_at => $row->{last_viewed_at},
108 };
109 }
110  
111 # One query shape for every read: an entry joined to whichever revision is
112 # its head. A history lookup swaps that join for a specific revision.
113 method _select ($where, @bind) {
114 return $self->db->query(<<~"SQL", @bind)->hashes;
115 SELECT e.kind, e.name, r.revision, r.body, r.description, r.type,
116 pr.name AS project, r.author, r.created_at,
117 e.view_count, e.useful_count, e.last_viewed_at,
118 coalesce(
119 (SELECT array_agg(p.name ORDER BY p.name)::text[]
120 FROM entry_projects ep JOIN projects p ON p.id = ep.project_id
121 WHERE ep.entry_id = e.id),
122 '{}'
123 ) AS projects
124 FROM entries e
125 JOIN revisions r ON r.id = e.head_id
126 LEFT JOIN projects pr ON pr.id = r.project_id
127 WHERE $where
128 ORDER BY e.name
129 SQL
130 }
131  
132 # A project is a real row now (see migration 7), so this is a plain
133 # listing rather than a UNION of three free-text columns.
134 method list_projects {
135 return $self->db->query('SELECT name FROM projects ORDER BY name')
136 ->arrays->flatten->to_array;
137 }
138  
139 # Full rows, for a projects admin list - list_projects itself stays
140 # name-only (its shape is the MCP tool's contract).
141 method list_projects_full {
142 return $self->db->query(
143 'SELECT id, name, description, created_at FROM projects ORDER BY name')
144 ->hashes->to_array;
145 }
146  
147 # Full row, for a project's own edit page - list_projects stays name-only
148 # (its shape is the MCP tool's contract, see list_projects' tool wrapper).
149 method get_project ($name) {
150 croak 'name is required' unless defined $name && length $name;
151 return $self->db->query(
152 'SELECT id, name, description, created_at FROM projects WHERE name = ?',
153 $name)->hash;
154 }
155  
156 # Unlike _project_id (called implicitly by tag/save), this is the
157 # explicit "a human asked to create a project" entry point - errors if
158 # the name is already taken instead of silently reusing the existing
159 # row, and it's the only way to set a description at creation time.
160 method create_project ($name, %opts) {
161 croak 'name is required' unless defined $name && length $name;
162 croak "a project named '$name' already exists" if $self->get_project($name);
163 return $self->db->query(<<~'SQL', $name, $opts{description})->hash;
164 INSERT INTO projects (name, description) VALUES (?, ?)
165 RETURNING id, name, description, created_at
166 SQL
167 }
168  
169 method update_project ($name, %opts) {
170 croak 'name is required' unless defined $name && length $name;
171 my $new_name = $opts{name} // $name;
172 my $row = eval {
173 $self->db->query(<<~'SQL', $new_name, $opts{description}, $name)->hash;
174 UPDATE projects SET name = ?, description = ? WHERE name = ?
175 RETURNING id, name, description, created_at
176 SQL
177 };
178 if (my $err = $@) {
179 croak "a project named '$new_name' already exists"
180 if "$err" =~ /duplicate key/;
181 croak $err;
182 }
183 croak "no project named '$name'" unless $row;
184 return $row;
185 }
186  
187 # revisions.project_id (a spec's required, single project) has no ON
188 # DELETE action, so Postgres itself refuses to delete a project any
189 # spec still belongs to - caught here and re-raised as a message a form
190 # can show directly, rather than a raw constraint-name error. Tags
191 # (entry_projects/project_agents) ARE ON DELETE CASCADE by design (a
192 # memory/skill/agent can lose one of several tags without objection),
193 # so those never block a delete.
194 method delete_project ($name) {
195 croak 'name is required' unless defined $name && length $name;
196 my $deleted = eval {
197 $self->db->query('DELETE FROM projects WHERE name = ? RETURNING id', $name)
198 ->hash;
199 };
200 if (my $err = $@) {
201 croak "Cannot delete project '$name': it still has specs assigned to it"
202 if "$err" =~ /violates foreign key constraint/;
203 croak $err;
204 }
205 croak "no project named '$name'" unless $deleted;
206 return 1;
207 }
208  
209 # Resolves a project name to its id, creating the row if this is the
210 # first time it has been used - tagging with a new name is how a
211 # project comes into being, the same way it worked when project was
212 # still a bare string. Case-insensitive, so "Abto" and "abto" are the
213 # same project.
214 method _project_id ($name) {
215 croak 'project is required' unless defined $name && length $name;
216 my $row
217 = $self->db->query('SELECT id FROM projects WHERE name = ?', $name)->hash;
218 return $row->{id} if $row;
219 return $self->db->query(
220 'INSERT INTO projects (name) VALUES (?) RETURNING id', $name)->hash->{id};
221 }
222  
223 # Like _project_id, but never creates - for filtering, where a project
224 # name nobody has used yet should simply match nothing rather than
225 # spring into existence as a side effect of a read.
226 method _find_project_id ($name) {
227 my $row
228 = $self->db->query('SELECT id FROM projects WHERE name = ?', $name)->hash;
229 return $row ? $row->{id} : undef;
230 }
231  
232 # Spec keeps its single required project on the revision - a spec
233 # document belongs to exactly one project by definition. Memory and
234 # skill are tagged instead: a memory can matter to more than one
235 # project, so filtering joins entry_projects rather than reading a
236 # single column. Either way, filtering by a project nobody has tagged
237 # anything with matches nothing rather than erroring.
238 method _project_filter ($kind, $project) {
239 return ('', ()) unless defined $project && length $project;
240 my $project_id = $self->_find_project_id($project);
241 return (' AND 1 = 0', ()) unless defined $project_id;
242 return $kind eq 'spec'
243 ? (' AND r.project_id = ?', $project_id)
244 : (
245 ' AND EXISTS (SELECT 1 FROM entry_projects ep'
246 . ' WHERE ep.entry_id = e.id AND ep.project_id = ?)',
247 $project_id
248 );
249 }
250  
251 method list ($kind, $project = undef) {
252 _check_kind($kind);
253 my ($where, @bind) = ('e.kind = ? AND e.archived_at IS NULL', $kind);
254 my ($clause, @pbind) = $self->_project_filter($kind, $project);
255 $where .= $clause;
256 push @bind, @pbind;
257 return [map { $self->_entry($_) } @{ $self->_select($where, @bind) }];
258 }
259  
260 # Same shape as list(), but newest-first and capped - for a dashboard's
261 # "what's new" glance, where list()'s alphabetical order is useless.
262 # created_at is the head revision's own (see _select's join), so this
263 # is "most recently saved", not "most recently created" - an edit
264 # bumps an entry back to the top, which is the more useful reading for
265 # "what changed lately".
266 method list_recent ($kind, $limit = 10) {
267 _check_kind($kind);
268 my $rows = $self->db->query(<<~"SQL", $kind, $limit)->hashes;
269 SELECT e.kind, e.name, r.revision, r.body, r.description, r.type,
270 pr.name AS project, r.author, r.created_at,
271 e.view_count, e.useful_count, e.last_viewed_at,
272 coalesce(
273 (SELECT array_agg(p.name ORDER BY p.name)::text[]
274 FROM entry_projects ep JOIN projects p ON p.id = ep.project_id
275 WHERE ep.entry_id = e.id),
276 '{}'
277 ) AS projects
278 FROM entries e
279 JOIN revisions r ON r.id = e.head_id
280 LEFT JOIN projects pr ON pr.id = r.project_id
281 WHERE e.kind = ? AND e.archived_at IS NULL
282 ORDER BY r.created_at DESC
283 LIMIT ?
284 SQL
285 return [map { $self->_entry($_) } @$rows];
286 }
287  
288 # Vector-based search using hybrid approach (TF-IDF ranking with tsvector filtering).
289 # Maintains backward compatibility by including `matches` array.
290 # The one way in. Ranking is hybrid_search's (tsvector filtering, TF-IDF
291 # re-ranking); this adds the matching lines on top, which is the only
292 # thing the two ever differed by.
293 method search ($kind, $query, $project = undef, $limit = 10) {
294 _check_kind($kind);
295 croak 'query is required' unless defined $query && length $query;
296  
297 my $results = $self->hybrid_search($kind, $query, $project, $limit);
298  
299 # Add matches for backward compatibility with existing clients
300 for my $r (@$results) {
301 my @lines = grep { index(lc $_, lc $query) >= 0 }
302 split /\n/, "$r->{name}\n$r->{description}\n$r->{body}";
303 $r->{matches} = [splice @lines, 0, 5];
304 }
305  
306 return $results;
307 }
308  
309 # Hybrid search using PostgreSQL tsvector for filtering + Lingua::TermWeight
310 # TF-IDF for ranking. Only considers LATEST REVISIONS (head_id).
311 method hybrid_search (
312 $kind, $query,
313 $project = undef,
314 $limit = 10,
315 $use_cached = 1
316 )
317 {
318 _check_kind($kind);
319 croak 'query is required' unless defined $query && length $query;
320  
321 # Step 1: Get candidates from PostgreSQL tsvector - LATEST REVISIONS ONLY
322 my $where = 'e.kind = ? AND e.archived_at IS NULL AND r.id = e.head_id';
323 my @where_bind = ($kind);
324 my ($clause, @pbind) = $self->_project_filter($kind, $project);
325 $where .= $clause;
326 push @where_bind, @pbind;
327  
328 # Match any query term for candidate recall, then let TF-IDF rank relevance.
329 my $tsquery = <<~'SQL';
330 websearch_to_tsquery(
331 'english', regexp_replace(?, '[[:space:]]+', ' OR ', 'g')
332 )
333 SQL
334  
335 my $candidate_limit = $limit * 5;
336 my @bind = ($query, @where_bind, $query, $candidate_limit);
337  
338 my $sql = <<~"SQL";
339 SELECT e.kind, e.name, r.id AS revision_id, r.revision,
340 r.body, r.description, r.type,
341 pr.name AS project, r.author, r.created_at,
342 e.view_count, e.useful_count, e.last_viewed_at,
343 coalesce(
344 (SELECT array_agg(p.name ORDER BY p.name)::text[]
345 FROM entry_projects ep JOIN projects p ON p.id = ep.project_id
346 WHERE ep.entry_id = e.id),
347 '{}'
348 ) AS projects,
349 ts_rank(r.search_vector, $tsquery) AS ts_rank,
350 dt.tfidf_vector AS cached_vector
351 FROM entries e
352 JOIN revisions r ON r.id = e.head_id
353 LEFT JOIN projects pr ON pr.id = r.project_id
354 LEFT JOIN document_tfidf dt ON dt.revision_id = r.id
355 WHERE $where AND r.search_vector @@ $tsquery
356 ORDER BY ts_rank DESC
357 LIMIT ?
358 SQL
359  
360 my $candidates = $self->db->query($sql, @bind)->expand->hashes->to_array;
361 return [] unless @$candidates;
362  
363 # Step 2: Get corpus-wide IDF (computed from latest revisions only)
364 my $idf_data = $self->_get_corpus_idf($kind);
365 my $idf = $idf_data->{idf};
366  
367 # Step 3: Compute query vector using corpus IDF
368 my $tw = MCP::Server::KnowledgeStore::TermWeight->new;
369 my $query_tf = $tw->compute_tf($query);
370 my $query_vector = {
371 map { $_ => ($query_tf->{$_} // 0) * ($idf->{$_} // 0) }
372 keys %$query_tf
373 };
374  
375 # Step 4: Re-rank candidates using cosine similarity
376 my @ranked = map {
377 my $doc_vector = $_->{cached_vector}
378 // $self->_compute_doc_vector($kind, $_->{revision_id}, $idf);
379 +{ %$_, rank => $tw->cosine_similarity($query_vector, $doc_vector) };
380 } @$candidates;
381  
382 # A PostgreSQL stem match can have no corresponding TF-IDF term. Drop
383 # those zero-rank candidates when cosine similarity found useful matches,
384 # but retain the PostgreSQL-ranked fallback when the whole corpus scores 0.
385 my @positive = grep { $_->{rank} > 0 } @ranked;
386 @ranked = @positive if @positive;
387 @ranked = sort {
388 $b->{rank} <=> $a->{rank}
389 || ($b->{ts_rank} // 0) <=> ($a->{ts_rank} // 0)
390 } @ranked;
391 splice @ranked, $limit if @ranked > $limit;
392 return \@ranked;
393 }
394  
395 # Get or compute corpus-wide IDF for a kind - LATEST REVISIONS ONLY
396 method _get_corpus_idf ($kind) {
397 my $row
398 = $self->db->query(
399 'SELECT idf, document_count FROM corpus_idf WHERE kind = ?', $kind)
400 ->expand->hash;
401  
402 return $row if $row;
403  
404 # Compute fresh IDF from LATEST REVISIONS only
405 my $tw = MCP::Server::KnowledgeStore::TermWeight->new;
406 my $documents = $self->db->query(<<~'SQL', $kind)->arrays->flatten->to_array;
407 SELECT e.name || ' ' || r.body || ' ' || COALESCE(r.description, '')
408 FROM entries e
409 JOIN revisions r ON r.id = e.head_id
410 WHERE e.kind = ? AND e.archived_at IS NULL
411 SQL
412  
413 my $idf = $tw->compute_idf($documents);
414 my $count = scalar @$documents;
415  
416 my $json_idf = { json => $idf };
417 $self->db->query(<<~'SQL', $kind, $json_idf, $count, $json_idf, $count);
418 INSERT INTO corpus_idf (kind, idf, document_count)
419 VALUES (?, ?, ?)
420 ON CONFLICT (kind) DO UPDATE SET idf = ?, document_count = ?, updated_at = now()
421 SQL
422  
423 return { idf => $idf, document_count => $count };
424 }
425  
426 # Compute and cache a document's TF-IDF vector - LATEST REVISIONS ONLY
427 method _compute_doc_vector ($kind, $revision_id, $idf) {
428  
429 # Verify this IS the latest revision before computing
430 my $row = $self->db->query(<<~'SQL', $revision_id)->hash;
431 SELECT e.name, r.body, r.description, e.head_id = r.id AS is_latest
432 FROM revisions r
433 JOIN entries e ON e.id = r.entry_id
434 WHERE r.id = ?
435 SQL
436  
437 croak "Refusing to compute vector for non-latest revision $revision_id"
438 unless $row && $row->{is_latest};
439  
440 my $text = join ' ', $row->{name}, ($row->{body} // ''),
441 ($row->{description} // '');
442 my $tw = MCP::Server::KnowledgeStore::TermWeight->new;
443 my $tf = $tw->compute_tf($text);
444 my $tfidf = { map { $_ => ($tf->{$_} // 0) * ($idf->{$_} // 0) } keys %$tf };
445  
446 # Cache it
447 my $json_tfidf = { json => $tfidf };
448 my $term_count = scalar keys %$tfidf;
449 my @bind = ($revision_id, $json_tfidf, $term_count, $json_tfidf);
450 $self->db->query(<<~'SQL', @bind);
451 INSERT INTO document_tfidf (revision_id, tfidf_vector, term_count)
452 VALUES (?, ?, ?)
453 ON CONFLICT (revision_id) DO UPDATE
454 SET tfidf_vector = ?, updated_at = now()
455 SQL
456  
457 return $tfidf;
458 }
459  
460 # Invalidate vector cache when documents change
461 method _invalidate_vector_cache ($kind, $revision_id = undef) {
462 if ($revision_id) {
463 $self->db->query('DELETE FROM document_tfidf WHERE revision_id = ?',
464 $revision_id);
465 }
466 else {
467 $self->db->query(<<~'SQL', $kind);
468 DELETE FROM document_tfidf
469 WHERE revision_id IN (
470 SELECT r.id
471 FROM revisions r
472 JOIN entries e ON e.head_id = r.id
473 WHERE e.kind = ?
474 )
475 SQL
476 $self->db->query('DELETE FROM corpus_idf WHERE kind = ?', $kind);
477 }
478 }
479  
480 # The raw row, with no view-counting side effect - what save() uses to
481 # hand back the entry it just wrote, since writing is not viewing.
482 method _fetch ($kind, $name, $revision = undef) {
483 return defined $revision
484 ? $self->db->query(<<~'SQL', $kind, $name, $revision)->hash
485 SELECT e.kind, e.name, r.revision, r.body, r.description, r.type,
486 pr.name AS project, r.author, r.created_at,
487 e.view_count, e.useful_count, e.last_viewed_at,
488 coalesce(
489 (SELECT array_agg(p.name ORDER BY p.name)::text[]
490 FROM entry_projects ep JOIN projects p ON p.id = ep.project_id
491 WHERE ep.entry_id = e.id),
492 '{}'
493 ) AS projects
494 FROM entries e
495 JOIN revisions r ON r.entry_id = e.id
496 LEFT JOIN projects pr ON pr.id = r.project_id
497 WHERE e.kind = ? AND e.name = ? AND r.revision = ?
498 SQL
499 : $self->_select('e.kind = ? AND e.name = ?', $kind, $name)->first;
500 }
501  
502 method get ($kind, $name, $revision = undef) {
503 _check_kind($kind);
504 _check_name($name);
505  
506 my $row = $self->_fetch($kind, $name, $revision);
507 return undef unless $row;
508  
509 # Check if archived - still return the content but note it's archived
510 my $is_archived
511 = $self->db->query(
512 'SELECT archived_at FROM entries WHERE kind = ? AND name = ?',
513 $kind, $name)->hash->{archived_at};
514  
515 # A read through this method is what "viewed via the API" means; bump
516 # the counter for every call, including an old-revision lookup, rather
517 # than only the head - an agent reading history is still consulting it.
518 # Note: archived entries can still be viewed (for audit purposes)
519 my $viewed = $self->db->query(<<~'SQL', $kind, $name)->hash;
520 UPDATE entries SET view_count = view_count + 1, last_viewed_at = now()
521 WHERE kind = ? AND name = ?
522 RETURNING view_count, last_viewed_at
523 SQL
524 @{$row}{qw(view_count last_viewed_at)}
525 = @{$viewed}{qw(view_count last_viewed_at)};
526  
527 my $entry = $self->_entry($row);
528  
529 # Add archived status to the entry
530 $entry->{archived_at} = $is_archived if $is_archived;
531  
532 return $entry;
533 }
534  
535 # Called explicitly by a client when an entry was actually useful, as
536 # opposed to merely read - a separate signal from view_count, which
537 # counts every fetch whether or not it helped.
538 method mark_useful ($kind, $name) {
539 _check_kind($kind);
540 _check_name($name);
541 my $row = $self->db->query(<<~'SQL', $kind, $name)->hash;
542 UPDATE entries SET useful_count = useful_count + 1
543 WHERE kind = ? AND name = ?
544 RETURNING useful_count
545 SQL
546 croak "no $kind named '$name'" unless $row;
547 return $row->{useful_count};
548 }
549  
550 sub _check_taggable ($kind) {
551 croak "project on a spec is set via save_spec, not tag/untag"
552 if $kind eq 'spec';
553 return _check_kind($kind);
554 }
555  
556 # A memory or skill can matter to more than one project - see
557 # entry_projects in the migrations - so tagging is its own call rather
558 # than something a save replaces wholesale.
559 method tag ($kind, $name, $project) {
560 _check_taggable($kind);
561 _check_name($name);
562 croak 'project is required' unless defined $project && length $project;
563 my $entry
564 = $self->db->query('SELECT id FROM entries WHERE kind = ? AND name = ?',
565 $kind, $name)->hash;
566 croak "no $kind named '$name'" unless $entry;
567 my $project_id = $self->_project_id($project);
568 $self->db->query(<<~'SQL', $entry->{id}, $project_id);
569 INSERT INTO entry_projects (entry_id, project_id) VALUES (?, ?)
570 ON CONFLICT DO NOTHING
571 SQL
572 return 1;
573 }
574  
575 method untag ($kind, $name, $project) {
576 _check_taggable($kind);
577 _check_name($name);
578 croak 'project is required' unless defined $project && length $project;
579 my $project_id = $self->_find_project_id($project) or return 1;
580 $self->db->query(<<~'SQL', $kind, $name, $project_id);
581 DELETE FROM entry_projects
582 WHERE entry_id = (SELECT id FROM entries WHERE kind = ? AND name = ?)
583 AND project_id = ?
584 SQL
585 return 1;
586 }
587  
588 # Append-only: a save never updates a revision, it adds the next one and
589 # moves the entry's head to it. The previous content stays readable, which
590 # is the audit trail git used to provide.
591 method save ($kind, $name, $content, %opts) {
592 _check_kind($kind);
593 _check_name($name);
594  
595 my ($meta, $body) = $self->_split($content);
596 $body =~ s/\A\s+//;
597 $body =~ s/\s+\z//;
598  
599 my $db = $self->db;
600 my $tx = $db->begin;
601  
602 my $entry = $db->query(<<~'SQL', $kind, $name)->hash;
603 INSERT INTO entries (kind, name) VALUES (?, ?)
604 ON CONFLICT (kind, name) DO UPDATE SET name = excluded.name
605 RETURNING id, head_id
606 SQL
607  
608 my $head
609 = $entry->{head_id}
610 ? $db->query('SELECT * FROM revisions WHERE id = ?', $entry->{head_id})
611 ->hash
612 : {};
613  
614 # Anything the caller did not mention is inherited from the current
615 # head, so a save that only changes the body keeps its metadata.
616 my $description = $opts{description} // $meta->{description}
617 // $head->{description} // '';
618 my $type
619 = $kind eq 'memory'
620 ? ($opts{type} // $meta->{metadata}{type} // $head->{type})
621 : undef;
622 croak 'type is required for a memory'
623 if $kind eq 'memory' && !(defined $type && length $type);
624  
625 # Spec keeps project on the revision, required and single - a spec
626 # document belongs to exactly one project. Memory and skill no longer
627 # write it here at all; a project argument to their save is a tag
628 # added below, additive rather than replacing prior tags, since a
629 # revision's identity has nothing to do with which projects care
630 # about the entry.
631 my $project_id;
632 if ($kind eq 'spec') {
633 my $project_name = $opts{project} // $meta->{project};
634 $project_id
635 = defined $project_name && length $project_name
636 ? $self->_project_id($project_name)
637 : $head->{project_id};
638 croak 'project is required for a spec' unless defined $project_id;
639 }
640  
641 # Every bind value has to sit on the heredoc's own line, or the
642 # continuation lines end up inside the SQL.
643 my @bind = (
644 $entry->{id}, $entry->{id}, $body, $description, $type,
645 $project_id, $opts{author}
646 );
647 my $revision = $db->query(<<~'SQL', @bind)->hash;
648 INSERT INTO revisions
649 (entry_id, revision, body, description, type, project_id, author)
650 VALUES (
651 ?,
652 (SELECT coalesce(max(revision), 0) + 1 FROM revisions WHERE entry_id = ?),
653 ?, ?, ?, ?, ?
654 )
655 RETURNING id, revision
656 SQL
657  
658 $db->query('UPDATE entries SET head_id = ? WHERE id = ?',
659 $revision->{id}, $entry->{id});
660  
661 # Update search_vector for the new revision
662 $db->query('SELECT update_revision_search_vector(?, ?, ?, ?)',
663 $revision->{id}, $name, $description, $body);
664  
665 if ($kind ne 'spec') {
666 my $tag = $opts{project} // $meta->{project};
667 if (defined $tag && length $tag) {
668 my $tag_id = $self->_project_id($tag);
669 $db->query(<<~'SQL', $entry->{id}, $tag_id);
670 INSERT INTO entry_projects (entry_id, project_id) VALUES (?, ?)
671 ON CONFLICT DO NOTHING
672 SQL
673 }
674 }
675  
676 $tx->commit;
677  
678 # Invalidate vector cache: old head (if exists) and new head
679 my $new_entry = $self->_fetch($kind, $name);
680 if ($entry->{head_id}) {
681 $self->_invalidate_vector_cache($kind, $entry->{head_id});
682 }
683 $self->_invalidate_vector_cache($kind, $new_entry->{id});
684 $self->db->query('DELETE FROM corpus_idf WHERE kind = ?', $kind);
685  
686 return $self->_entry($new_entry);
687 }
688  
689 method history ($kind, $name) {
690 _check_kind($kind);
691 _check_name($name);
692 return $self->db->query(<<~'SQL', $kind, $name)->hashes->to_array;
693 SELECT r.revision, r.description, r.type, p.name AS project, r.author,
694 r.created_at, (r.id = e.head_id) AS is_head
695 FROM entries e
696 JOIN revisions r ON r.entry_id = e.id
697 LEFT JOIN projects p ON p.id = r.project_id
698 WHERE e.kind = ? AND e.name = ?
699 ORDER BY r.revision DESC
700 SQL
701 }
702  
703 # Soft delete: archive an entry. Hides it from normal reads but keeps all
704 # data and revision history. Returns the archived entry's current state.
705 method archive ($kind, $name, %opts) {
706 _check_kind($kind);
707 _check_name($name);
708  
709 my $expected_revision = $opts{expected_revision};
710 my $reason = $opts{reason} // '';
711 my $author = $opts{author} // croak 'author is required for archive';
712  
713 my $db = $self->db;
714 my $tx = $db->begin;
715  
716 # Check the entry exists and get its current revision
717 my $entry = $db->query(
718 'SELECT id, head_id FROM entries WHERE kind = ? AND name = ? AND archived_at IS NULL',
719 $kind, $name
720 )->hash;
721 croak "no $kind named '$name' (or already archived)" unless $entry;
722  
723 # If expected_revision is provided, verify it matches the current head
724 if (defined $expected_revision) {
725 my $current_rev = $db->query('SELECT revision FROM revisions WHERE id = ?',
726 $entry->{head_id})->hash->{revision};
727 croak "expected revision $expected_revision but current is $current_rev"
728 unless $current_rev == $expected_revision;
729 }
730  
731 # Archive the entry. Bind order must match placeholder order (SET
732 # before WHERE) - author/reason were previously passed last while
733 # binding positionally first, so kind/name landed in archived_by/
734 # archived_reason and author/reason landed in the WHERE clause,
735 # matching zero rows: this silently no-opped (0 rows updated, never
736 # checked) instead of erroring, so every archive_* call looked
737 # successful without ever actually archiving anything.
738 my $archived = $db->query(<<~'SQL', $author, $reason, $kind, $name)->hash;
739 UPDATE entries
740 SET archived_at = now(), archived_by = ?, archived_reason = ?
741 WHERE kind = ? AND name = ? AND archived_at IS NULL
742 RETURNING id, head_id
743 SQL
744 croak "no $kind named '$name' (or already archived)" unless $archived;
745  
746 # Record in audit log
747 my $head_revision = $db->query('SELECT revision FROM revisions WHERE id = ?',
748 $entry->{head_id})->hash->{revision};
749 $db->query(
750 <<~'SQL', 'archive', $kind, $name, $head_revision, $author, $reason);
751 INSERT INTO audit_log (action, kind, name, revision, acted_by, reason)
752 VALUES (?, ?, ?, ?, ?, ?)
753 SQL
754  
755 $tx->commit;
756  
757 # Return the archived entry's state before it was hidden
758 my $row = $self->_fetch($kind, $name);
759 return $row ? $self->_entry($row) : undef;
760 }
761  
762 # Restore an archived entry. Makes it visible again in normal operations.
763 method restore ($kind, $name, %opts) {
764 _check_kind($kind);
765 _check_name($name);
766  
767 my $author = $opts{author} // croak 'author is required for restore';
768 my $reason = $opts{reason} // '';
769  
770 my $db = $self->db;
771 my $tx = $db->begin;
772  
773 # Check the entry exists and is currently archived
774 my $entry = $db->query(
775 'SELECT id, head_id FROM entries WHERE kind = ? AND name = ? AND archived_at IS NOT NULL',
776 $kind, $name
777 )->hash;
778 croak "no archived $kind named '$name'" unless $entry;
779  
780 # Restore the entry
781 $db->query(<<~'SQL', $kind, $name)->hash;
782 UPDATE entries
783 SET archived_at = NULL, archived_by = NULL, archived_reason = NULL
784 WHERE kind = ? AND name = ? AND archived_at IS NOT NULL
785 RETURNING id, head_id
786 SQL
787  
788 # Record in audit log
789 my $head_revision = $db->query('SELECT revision FROM revisions WHERE id = ?',
790 $entry->{head_id})->hash->{revision};
791 $db->query(
792 <<~'SQL', 'restore', $kind, $name, $head_revision, $author, $reason);
793 INSERT INTO audit_log (action, kind, name, revision, acted_by, reason)
794 VALUES (?, ?, ?, ?, ?, ?)
795 SQL
796  
797 $tx->commit;
798  
799 # Return the restored entry
800 my $row = $self->_fetch($kind, $name);
801 return $row ? $self->_entry($row) : undef;
802 }
803  
804 # Permanently delete an entry and all its revision history. This is
805 # destructive and cannot be undone. Returns the audit log entry.
806 method purge ($kind, $name, %opts) {
807 _check_kind($kind);
808 _check_name($name);
809  
810 my $expected_revision = $opts{expected_revision};
811 my $reason = $opts{reason} // '';
812 my $author = $opts{author} // croak 'author is required for purge';
813  
814 my $db = $self->db;
815 my $tx = $db->begin;
816  
817 # Check the entry exists (may or may not be archived)
818 my $entry
819 = $db->query('SELECT id, head_id FROM entries WHERE kind = ? AND name = ?',
820 $kind, $name)->hash;
821 croak "no $kind named '$name'" unless $entry;
822  
823 # If expected_revision is provided, verify it matches the current head
824 if (defined $expected_revision) {
825 my $current_rev = $db->query('SELECT revision FROM revisions WHERE id = ?',
826 $entry->{head_id})->hash->{revision};
827 croak "expected revision $expected_revision but current is $current_rev"
828 unless $current_rev == $expected_revision;
829 }
830  
831 # Get the current revision for audit log
832 my $head_revision = $db->query('SELECT revision FROM revisions WHERE id = ?',
833 $entry->{head_id})->hash->{revision};
834  
835 # Delete all project tags first (CASCADE will handle this, but be explicit in log)
836 # Then delete the entry and all its revisions (CASCADE from entries to revisions)
837 my $deleted = $db->query(<<~'SQL', $kind, $name)->hash;
838 DELETE FROM entries WHERE kind = ? AND name = ?
839 RETURNING id
840 SQL
841  
842 # Record in audit log
843 $db->query(
844 <<~'SQL', 'purge', $kind, $name, $head_revision, $author, $reason);
845 INSERT INTO audit_log (action, kind, name, revision, acted_by, reason)
846 VALUES (?, ?, ?, ?, ?, ?)
847 SQL
848  
849 $tx->commit;
850  
851 return {
852 name => $name,
853 kind => $kind,
854 revision => $head_revision,
855 deleted => !!$deleted,
856 };
857 }
858  
859 # List archived entries of a given kind, optionally filtered by project.
860 # Same shape as list() but only returns archived entries.
861 method list_archived ($kind, $project = undef) {
862 _check_kind($kind);
863 my ($where, @bind) = ('e.kind = ? AND e.archived_at IS NOT NULL', $kind);
864 my ($clause, @pbind) = $self->_project_filter($kind, $project);
865 $where .= $clause;
866 push @bind, @pbind;
867 return [map { $self->_entry($_) } @{ $self->_select($where, @bind) }];
868 }
869  
870 # Get audit log entries, optionally filtered.
871 method audit_log ($kind = undef, $name = undef, $action = undef, $limit = 50) {
872 my ($where, @bind) = ('1 = 1', ());
873  
874 if (defined $kind && length $kind) {
875 $where .= ' AND kind = ?';
876 push @bind, $kind;
877 }
878 if (defined $name && length $name) {
879 $where .= ' AND name = ?';
880 push @bind, $name;
881 }
882 if (defined $action && length $action) {
883 $where .= ' AND action = ?';
884 push @bind, $action;
885 }
886  
887 return $self->db->query(<<~"SQL", @bind, $limit)->hashes->to_array;
888 SELECT id, action, kind, name, revision, acted_by, acted_at, reason
889 FROM audit_log
890 WHERE $where
891 ORDER BY acted_at DESC, id DESC
892 LIMIT ?
893 SQL
894 }
895  
896 # Agent credentials. Agents get a token, not an account - there is no
897 # agent-side user record anywhere in this schema, and a token grants the
898 # MCP tools and nothing else.
899 #
900 # A token is 32 random bytes, so a single SHA-256 is the right hash: it is
901 # not a password, there is nothing to brute-force, and a KDF would only
902 # slow down every request. Only the hash is stored, so a database dump
903 # yields nothing usable.
904 method create_token ($name) {
905 croak 'token name is required' unless defined $name && length $name;
906  
907 # Names stay reserved after revocation, so an old credential's name can
908 # never quietly come back on a new token. Checked here for a readable
909 # error; the unique constraint is what actually guarantees it.
910 croak "a token named '$name' already exists"
911 . ' (revoked names stay reserved)'
912 if $self->db->query('SELECT 1 FROM tokens WHERE name = ?', $name)->rows;
913  
914 my $token = 'mcp_ks_' . b64_encode(random_bytes(32), '') =~ tr{+/=}{-_}dr;
915 my $row = $self->db->query(<<~'SQL', $name, sha256_hex($token))->hash;
916 INSERT INTO tokens (name, token_hash) VALUES (?, ?)
917 RETURNING name, created_at
918 SQL
919  
920 # The only time the caller ever sees it; nothing can recover it later.
921 return { %$row, token => $token };
922 }
923  
924 method verify_token ($presented) {
925 return undef unless defined $presented && length $presented;
926 my $row = $self->db->query(<<~'SQL', sha256_hex($presented))->hash;
927 UPDATE tokens SET last_used_at = now()
928 WHERE token_hash = ? AND revoked_at IS NULL
929 RETURNING name, created_at, last_used_at
930 SQL
931 return $row;
932 }
933  
934 method list_tokens {
935 return $self->db->query(<<~'SQL')->hashes->to_array;
936 SELECT name, created_at, last_used_at, revoked_at,
937 (revoked_at IS NULL) AS active
938 FROM tokens
939 ORDER BY name
940 SQL
941 }
942  
943 # Revoking keeps the row, so a name cannot be silently reused and the
944 # audit trail of what once had access survives.
945 method revoke_token ($name) {
946 croak 'token name is required' unless defined $name && length $name;
947 my $row = $self->db->query(<<~'SQL', $name)->hash;
948 UPDATE tokens SET revoked_at = now()
949 WHERE name = ? AND revoked_at IS NULL
950 RETURNING name, revoked_at
951 SQL
952 return $row;
953 }
954  
955 # Agent definitions (.claude/agents/*.md) are not shoehorned into the
956 # entries/revisions shape: their frontmatter carries fields (tools,
957 # model, ...) that memory/skill/spec never needed to preserve, so this
958 # is a plain blob by name - the whole file, verbatim, no parsing and no
959 # history. A local sync step reads/writes the file; this is just where
960 # the current version lives centrally.
961 #
962 # Agents are global (there is one "analyse", not an abto one and a waya
963 # one) - which projects a given agent is tailored for is a separate,
964 # many-to-many fact recorded in project_agents, not a copy of the body
965 # per project.
966 method get_agent ($name) {
967 _check_name($name);
968 my $row = $self->db->query(<<~'SQL', $name)->hash;
969 SELECT a.*,
970 coalesce(
971 (SELECT array_agg(p.name ORDER BY p.name)::text[]
972 FROM project_agents pa JOIN projects p ON p.id = pa.project_id
973 WHERE pa.agent_name = a.name),
974 '{}'
975 ) AS projects
976 FROM agents a WHERE a.name = ?
977 SQL
978 return $row;
979 }
980  
981 # Optionally filtered to agents tailored for one project - the same
982 # "list, optionally scoped" shape as list_memories/list_skills, just
983 # joined against project_agents instead of entry_projects. A project
984 # nobody has tagged anything with matches nothing, same as elsewhere.
985 method list_agents ($project = undef) {
986 my ($where, @bind) = ('1 = 1', ());
987 if (defined $project && length $project) {
988 my $project_id = $self->_find_project_id($project);
989 ($where, @bind)
990 = defined $project_id
991 ? (
992 'EXISTS (SELECT 1 FROM project_agents pa'
993 . ' WHERE pa.agent_name = a.name AND pa.project_id = ?)',
994 ($project_id)
995 )
996 : ('1 = 0', ());
997 }
998 return $self->db->query(<<~"SQL", @bind)->hashes->to_array;
999 SELECT a.name, a.author, a.created_at, a.updated_at,
1000 coalesce(
1001 (SELECT array_agg(p.name ORDER BY p.name)::text[]
1002 FROM project_agents pa JOIN projects p ON p.id = pa.project_id
1003 WHERE pa.agent_name = a.name),
1004 '{}'
1005 ) AS projects
1006 FROM agents a
1007 WHERE $where
1008 ORDER BY a.name
1009 SQL
1010 }
1011  
1012 method save_agent ($name, $content, %opts) {
1013 _check_name($name);
1014 croak 'content is required' unless defined $content && length $content;
1015 return $self->db->query(<<~'SQL', $name, $content, $opts{author})->hash;
1016 INSERT INTO agents (name, body, author) VALUES (?, ?, ?)
1017 ON CONFLICT (name) DO UPDATE
1018 SET body = excluded.body, author = excluded.author,
1019 updated_at = now()
1020 RETURNING name, author, created_at, updated_at
1021 SQL
1022 }
1023  
1024 # Unlike a memory/skill/spec (archive/restore/purge - see above), an
1025 # agent definition has no revision history to preserve, so there's no
1026 # soft-delete state to distinguish - this is the only deletion an agent
1027 # has. project_agents rows CASCADE with it.
1028 method delete_agent ($name) {
1029 _check_name($name);
1030 my $deleted
1031 = $self->db->query('DELETE FROM agents WHERE name = ? RETURNING id', $name)
1032 ->hash;
1033 croak "no agent named '$name'" unless $deleted;
1034 return 1;
1035 }
1036  
1037 # The tailoring fact: "this agent is a good fit for this project" - not
1038 # ownership, since the same agent can be tailored for several projects,
1039 # and a project can have several agents tailored for it.
1040 method tag_agent ($name, $project) {
1041 _check_name($name);
1042 croak 'project is required' unless defined $project && length $project;
1043 croak "no agent named '$name'"
1044 unless $self->db->query('SELECT 1 FROM agents WHERE name = ?', $name)
1045 ->rows;
1046 my $project_id = $self->_project_id($project);
1047 $self->db->query(<<~'SQL', $name, $project_id);
1048 INSERT INTO project_agents (agent_name, project_id) VALUES (?, ?)
1049 ON CONFLICT DO NOTHING
1050 SQL
1051 return 1;
1052 }
1053  
1054 method untag_agent ($name, $project) {
1055 _check_name($name);
1056 croak 'project is required' unless defined $project && length $project;
1057 my $project_id = $self->_find_project_id($project) or return 1;
1058 $self->db->query(
1059 'DELETE FROM project_agents WHERE agent_name = ? AND project_id = ?',
1060 $name, $project_id);
1061 return 1;
1062 }
1063  
1064 1;
1065  
1066 =pod
1067  
1068 =encoding UTF-8
1069  
1070 =head1 NAME
1071  
1072 MCP::Server::KnowledgeStore::Store - Postgres store for memories, skills, specs, agents, and projects
1073  
1074 =head1 VERSION
1075  
1076 version 0.001
1077  
1078 =head1 SYNOPSIS
1079  
1080 use MCP::Server::KnowledgeStore::Store;
1081  
1082 my $store = MCP::Server::KnowledgeStore::Store->new(
1083 pg => 'postgresql://mcp_ks@localhost/mcp_ks'
1084 );
1085 $store->migrate;
1086  
1087 my $memory = $store->save(
1088 memory => 'deployment-notes',
1089 "---\ndescription: Deployment notes\n---\n\nUse the release pipeline.\n",
1090 author => 'release-agent'
1091 );
1092  
1093 my $hits = $store->search(memory => 'release pipeline');
1094  
1095 =head1 DESCRIPTION
1096  
1097 This is the PostgreSQL persistence layer used by
1098 L<MCP::Server::KnowledgeStore>. Memories, skills, and specs are revisioned.
1099 Agent definitions and projects have their own records, and entries can be
1100 associated with projects.
1101  
1102 Constructing a store configures its migrations without running them. Call
1103 L</migrate> explicitly when the application is responsible for schema
1104 management.
1105  
1106 =head1 METHODS
1107  
1108 =head2 migrate
1109  
1110 Runs pending C<wdms_mcp> migrations and returns the store.
1111  
1112 =head2 db
1113  
1114 Returns a L<Mojo::Pg::Database> connection.
1115  
1116 =head2 save / get / list / list_recent / history
1117  
1118 Create revisions and read revisioned memories, skills, and specs. The first
1119 argument is the entry kind: C<memory>, C<skill>, or C<spec>.
1120  
1121 =head2 search
1122  
1123 my $hits = $store->search($kind, $query, $project, $limit);
1124  
1125 Selects candidates with PostgreSQL full-text search and ranks them with
1126 TF-IDF corpus vectors.
1127  
1128 =head2 tag / untag
1129  
1130 Associate a memory or skill with a project. A spec's single project is set
1131 when the spec is saved.
1132  
1133 =head2 archive / restore / purge
1134  
1135 Manage the lifecycle of revisioned entries. Archiving is reversible. Purging
1136 permanently removes the entry and its revision history.
1137  
1138 =head2 list_projects / get_project / create_project / update_project / delete_project
1139  
1140 Manage project records.
1141  
1142 =head2 list_agents / get_agent / save_agent / delete_agent / tag_agent / untag_agent
1143  
1144 Manage agent definitions and their project associations.
1145  
1146 =head2 create_token / list_tokens / verify_token / revoke_token
1147  
1148 Manage the named bearer tokens accepted by the MCP application.
1149  
1150 =head1 AUTHOR
1151  
1152 Wesley Schwengle <waterkip@cpan.org>
1153  
1154 =head1 COPYRIGHT AND LICENSE
1155  
1156 This software is Copyright (c) 2026 by Wesley Schwengle.
1157  
1158 This is free software, licensed under:
1159  
1160 The (three-clause) BSD License
1161  
1162 =cut
1163  
1164 __DATA__
« KnowledgeStore/ResourceTemplates.pm ↑ Summary KnowledgeStore/TermWeight.pm »