| 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__ | ||