secrets/plans/metadata-search-and-entry-relations.md

Metadata Value Search & Entry Relations (DAG)

Overview

Two new features for secrets-mcp:

1. Metadata Value Search — fuzzy search across all JSON scalar values in metadata, excluding keys
2. Entry Relations — directional parent-child associations between entries (DAG, multiple parents allowed, cycle detection)

---

Feature 1: Metadata Value Search

Problem

The existing query parameter in secrets_find/secrets_search searches metadata::text ILIKE, which matches keys, JSON punctuation, and structural characters. Users want to search only metadata values (e.g. find entries where any metadata value contains "1.2.3.4", regardless of key name).

Solution

Add a new metadata_query filter to SearchParams that uses PostgreSQL jsonb_path_query to iterate over only scalar values (strings, numbers, booleans), then applies ILIKE matching.

Changes

secrets-core

crates/secrets-core/src/service/search.rs

• Add metadata_query: Option<&'a str> field to SearchParams
• In entry_where_clause_and_next_idx, when metadata_query is set, add:

EXISTS (
  SELECT 1 FROM jsonb_path_query(
    entries.metadata,
    'strict $.** ? (@.type() != "object" && @.type() != "array")'
  ) AS val
  WHERE (val#>>'{}') ILIKE $N ESCAPE '\'
)

• Bind ilike_pattern(metadata_query) at the correct $N position in both fetch_entries_paged and count_entries

secrets-mcp (MCP tools)

crates/secrets-mcp/src/tools.rs

• Add metadata_query field to FindInput:

#[schemars(description = "Fuzzy search across metadata values only (keys excluded)")]
metadata_query: Option<String>,

• Add same field to SearchInput
• Pass metadata_query through to SearchParams in both secrets_find and secrets_search handlers

secrets-mcp (Web)

crates/secrets-mcp/src/web/entries.rs

• Add metadata_query: Option<String> to EntriesQuery
• Thread it into all SearchParams usages (count, list, folder counts)
• Pass it into template context
• Add metadata_query to EntriesPageTemplate and filter form hidden fields
• Include metadata_query in pagination href links

crates/secrets-mcp/templates/entries.html

• Add a "metadata 值" text input to the filter bar (after name, before type)
• Preserve value in the input on re-render

i18n Keys

Key	zh	zh-Hant	en
filterMetaLabel	元数据值	元数据值	Metadata value
filterMetaPlaceholder	搜索元数据值	搜尋元資料值	Search metadata values

Performance Notes

• The jsonb_path_query with $.** scans all nested values recursively; this is a sequential scan on the metadata column per row
• The existing GIN index on metadata jsonb_path_ops supports @> containment queries but NOT this pattern
• For production datasets > 10k entries, consider a generated column or materialized search column in a future iteration
• First version prioritizes semantic correctness over index optimization

---

Feature 2: Entry Relations (DAG)

Data Model

New table entry_relations:

CREATE TABLE IF NOT EXISTS entry_relations (
    parent_entry_id UUID NOT NULL REFERENCES entries(id) ON DELETE CASCADE,
    child_entry_id  UUID NOT NULL REFERENCES entries(id) ON DELETE CASCADE,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    PRIMARY KEY (parent_entry_id, child_entry_id),
    CHECK (parent_entry_id <> child_entry_id)
);

CREATE INDEX idx_entry_relations_parent ON entry_relations(parent_entry_id);
CREATE INDEX idx_entry_relations_child  ON entry_relations(child_entry_id);

-- Enforce multi-tenant isolation: parent and child must belong to same user
ALTER TABLE entry_relations ADD CONSTRAINT fk_parent_user
    FOREIGN KEY (parent_entry_id) REFERENCES entries(id) ON DELETE CASCADE;
ALTER TABLE entry_relations ADD CONSTRAINT fk_child_user
    FOREIGN KEY (child_entry_id)  REFERENCES entries(id) ON DELETE CASCADE;

Shared secrets already use entry_secrets as an N:N relation, so this is consistent with the existing pattern.

Cycle Detection

On every INSERT INTO entry_relations(parent, child), check that no path exists from child back to parent:

-- Returns true if adding (parent, child) would create a cycle
SELECT EXISTS(
  SELECT 1 FROM entry_relations
  WHERE child_entry_id = $1   -- $1 = proposed parent
  START WITH parent_entry_id = $2   -- $2 = proposed child
  CONNECT BY PRIOR child_entry_id = parent_entry_id
);

Wait — PostgreSQL doesn't support START WITH ... CONNECT BY. Use recursive CTE instead:

WITH RECURSIVE chain AS (
  SELECT parent_entry_id AS ancestor
  FROM entry_relations
  WHERE child_entry_id = $1   -- proposed child
  UNION ALL
  SELECT er.parent_entry_id
  FROM entry_relations er
  JOIN chain c ON c.ancestor = er.child_entry_id
)
SELECT EXISTS(SELECT 1 FROM chain WHERE ancestor = $2);
-- $1 = proposed child, $2 = proposed parent

If EXISTS returns true, reject with AppError::Validation { message: "cycle detected" }.

secrets-core Changes

New file: crates/secrets-core/src/service/relations.rs

pub struct RelationSummary {
    pub parent_id: Uuid,
    pub parent_name: String,
    pub parent_folder: String,
    pub parent_type: String,
}

pub struct AddRelationParams<'a> {
    pub parent_entry_id: Uuid,
    pub child_entry_id: Uuid,
    pub user_id: Option<Uuid>,
}

pub struct RemoveRelationParams<'a> {
    pub parent_entry_id: Uuid,
    pub child_entry_id: Uuid,
    pub user_id: Option<Uuid>,
}

/// Add a parent→child relation. Validates:
/// - Both entries exist and belong to the same user
/// - No self-reference (enforced by CHECK constraint)
/// - No cycle (recursive CTE check)
pub async fn add_relation(pool: &PgPool, params: AddRelationParams<'_>) -> Result<()>

/// Remove a parent→child relation.
pub async fn remove_relation(pool: &PgPool, params: RemoveRelationParams<'_>) -> Result<()>

/// Get all parents of an entry (with summary info).
pub async fn get_parents(pool: &PgPool, entry_id: Uuid, user_id: Option<Uuid>) -> Result<Vec<RelationSummary>>

/// Get all children of an entry (with summary info).
pub async fn get_children(pool: &PgPool, entry_id: Uuid, user_id: Option<Uuid>) -> Result<Vec<RelationSummary>>

/// Get parents + children for a batch of entry IDs (for list pages).
pub async fn get_relations_for_entries(
    pool: &PgPool,
    entry_ids: &[Uuid],
    user_id: Option<Uuid>,
) -> Result<HashMap<Uuid, Vec<RelationSummary>>>

crates/secrets-core/src/service/mod.rs — add pub mod relations;

crates/secrets-core/src/db.rs — add entry_relations table creation in migrate()

crates/secrets-core/src/error.rs — no new error variant needed; use AppError::Validation { message } for cycle detection and permission errors

MCP Tool Changes

crates/secrets-mcp/src/tools.rs

1. secrets_add (AddInput): add optional parent_ids: Option<Vec<String>> field

   • Description: "UUIDs of parent entries to link. Creates parent→child relations."
   • After creating the entry, call relations::add_relation for each parent

2. secrets_update (UpdateInput): add two fields:

   • add_parent_ids: Option<Vec<String>> — "UUIDs of parent entries to link"
   • remove_parent_ids: Option<Vec<String>> — "UUIDs of parent entries to unlink"

3. secrets_find and secrets_search output: add parents and children arrays to each entry result:

   {
     "id": "...",
     "name": "...",
     "parents": [{"id": "...", "name": "...", "folder": "...", "type": "..."}],
     "children": [{"id": "...", "name": "...", "folder": "...", "type": "..."}]
   }
   

   • Fetch relations for all returned entry IDs in a single batch query

Web Changes

crates/secrets-mcp/src/web/entries.rs

1. New API endpoints:

   • POST /api/entries/{id}/relations — add parent relation

     • Body: { "parent_id": "uuid" }
     • Validates same-user ownership and cycle detection

   • DELETE /api/entries/{id}/relations/{parent_id} — remove parent relation

   • GET /api/entries/options?q=xxx — lightweight search for parent selection modal

     • Returns [{ "id": "...", "name": "...", "folder": "...", "type": "..." }]
     • Used by the edit dialog's parent selection autocomplete

2. Entry list template data — include parent/child counts per entry row

3. api_entry_patch — extend EntryPatchBody with optional parent_ids: Option<Vec<Uuid>>

   • When present, replace all parent relations for this entry with the given list
   • This is simpler than incremental add/remove in the Web UI context

crates/secrets-mcp/templates/entries.html

1. List table: add a "关联" (relations) column showing parent/child counts as clickable chips
2. Edit dialog: add "上级条目" (parent entries) section
   • Show current parents as removable chips
   • Add a search-as-you-type input that queries /api/entries/options
   • Click a search result to add it as parent
   • On save, send parent_ids in the PATCH body
3. View dialog / detail: show "下级条目" (children) list with clickable links that navigate to the child entry
4. i18n: add keys for all new UI elements

i18n Keys (Entry Relations)

Key	zh	zh-Hant	en
colRelations	关联	關聯	Relations
parentEntriesLabel	上级条目	上級條目	Parent entries
childrenEntriesLabel	下级条目	下級條目	Child entries
addParentLabel	添加上级	新增上級	Add parent
removeParentLabel	移除上级	移除上級	Remove parent
searchEntriesPlaceholder	搜索条目…	搜尋條目…	Search entries…
noParents	无上级	無上級	No parents
noChildren	无下级	無下級	No children
relationCycleError	无法添加：会形成循环引用	無法新增：會形成循環引用	Cannot add: would create a cycle

Audit Logging

Log relation changes in the existing audit::log_tx system:

• Action: "add_relation" / "remove_relation"
• Detail JSON: { "parent_id": "...", "parent_name": "...", "child_id": "...", "child_name": "..." }

Export / Import

ExportEntry — add optional parents: Vec<ParentRef> where:

pub struct ParentRef {
    pub folder: String,
    pub name: String,
}

• On export, resolve each entry's parent IDs to (folder, name) pairs
• On import, two-phase:
  1. Create all entries (skip parents)
  2. For each entry with parents, resolve (folder, name) → entry_id and call add_relation
  3. If a parent reference cannot be resolved, log a warning and skip it (don't fail the entire import)

History / Rollback

• Relation changes are not versioned in entries_history. They are tracked only via audit_log.
• Rationale: relations are a cross-entry concern; rolling them back alongside entry fields would require complex multi-entry coordination. The audit log provides sufficient traceability.
• If the user explicitly requests rollback of relations in the future, it can be implemented as a separate feature.

---

Implementation Order

Phase 1: Metadata Value Search

1. secrets-core/src/service/search.rs — add metadata_query to SearchParams, implement SQL condition
2. secrets-mcp/src/tools.rs — add metadata_query to FindInput and SearchInput, wire through
3. secrets-mcp/src/web/entries.rs — add metadata_query to EntriesQuery, SearchParams, pagination, folder counts
4. secrets-mcp/templates/entries.html — add input field, i18n
5. Test: existing query still works; metadata_query only matches values

Phase 2: Entry Relations (Core)

1. secrets-core/src/db.rs — add entry_relations table to migrate()
2. secrets-core/src/service/relations.rs — implement add_relation, remove_relation, get_parents, get_children, get_relations_for_entries, cycle detection
3. secrets-core/src/service/mod.rs — add pub mod relations
4. Test: add/remove/query relations, cycle detection, same-user validation

Phase 3: Entry Relations (MCP)

1. secrets-mcp/src/tools.rs — extend AddInput, UpdateInput with parent IDs
2. secrets-mcp/src/tools.rs — extend secrets_find/secrets_search output with parents/children
3. Test: MCP tools work end-to-end

Phase 4: Entry Relations (Web)

1. secrets-mcp/src/web/entries.rs — add API endpoints, extend EntryPatchBody, extend template data
2. secrets-mcp/templates/entries.html — add relations column, edit dialog parent selector, view dialog children list
3. Test: Web UI works end-to-end

Phase 5: Export / Import (Optional)

1. secrets-core/src/models.rs — add parents to ExportEntry
2. secrets-core/src/service/export.rs — populate parents
3. secrets-core/src/service/import.rs — two-phase import with relation resolution

---

Database Migration

Add to secrets-core/src/db.rs migrate():

CREATE TABLE IF NOT EXISTS entry_relations (
    parent_entry_id UUID NOT NULL REFERENCES entries(id) ON DELETE CASCADE,
    child_entry_id  UUID NOT NULL REFERENCES entries(id) ON DELETE CASCADE,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    PRIMARY KEY (parent_entry_id, child_entry_id),
    CHECK (parent_entry_id <> child_entry_id)
);

CREATE INDEX IF NOT EXISTS idx_entry_relations_parent ON entry_relations(parent_entry_id);
CREATE INDEX IF NOT EXISTS idx_entry_relations_child  ON entry_relations(child_entry_id);

This is idempotent (uses IF NOT EXISTS) and will run automatically on next startup.

---

Security Considerations

• Same-user isolation: add_relation must verify both parent_entry_id and child_entry_id belong to the same user_id (or both are NULL for legacy single-user mode)
• Cycle detection: Recursive CTE query prevents any directed cycle, regardless of depth
• CASCADE delete: When an entry is deleted, all its relation edges are automatically removed via the ON DELETE CASCADE foreign key. This is the same pattern used by entry_secrets.

---

Testing Checklist

Metadata Search

• metadata_query=1.2.3.4 matches entries where any metadata value contains "1.2.3.4"
• metadata_query=1.2.3.4 does NOT match entries where only the key contains "1.2.3.4"
• metadata_query works with nested metadata (e.g. {"server": {"ip": "1.2.3.4"}})
• metadata_query combined with folder/type/tags filters works correctly
• metadata_query with special characters (%, _) is properly escaped
• Existing query parameter behavior is unchanged
• Web filter bar preserves metadata_query across pagination and folder tab clicks

Entry Relations

• Can add a parent→child relation between two entries
• Can add multiple parents to a single entry
• Cannot add self-referencing relation (CHECK constraint)
• Cannot create a direct cycle (A→B→A)
• Cannot create an indirect cycle (A→B→C→A)
• Cannot link entries from different users
• Deleting an entry removes all its relation edges but leaves related entries intact
• MCP secrets_add with parent_ids creates relations
• MCP secrets_update with add_parent_ids/remove_parent_ids modifies relations
• MCP secrets_find/secrets_search output includes parents and children
• Web entry list shows relation counts
• Web edit dialog allows adding/removing parents
• Web entry view shows children with navigation links