skill-system-memory

What this skill does

# Skill System Memory (PostgreSQL)

Persistent shared memory for all AI agents. PostgreSQL 14+ on Linux or Windows.
Memory failures look like intelligence failures — this skill ensures the right memory is retrieved at the right time.

Contract notes:

- Module-first: `mem.py` and MCP tooling are the primary runtime surface.
- Plugin optional: OpenCode plugin integration is an adapter layer, not a required dependency.
- Prefer project-scoped DB naming: `<project>-memory` (for example `skills-memory`).

## DB Targeting — Project Isolation

Skills are installed **globally** but memory is **project-scoped**. Isolation is at the database level: each project gets its own PostgreSQL database named `<project>-memory`.

Cross-DB recall is an explicit read-side feature, not a default mode. Search and context reads stay on the current memory DB unless the caller opts in with `scope=all` or `cross_db=true`. All write paths remain pinned to the local `SKILL_PGDATABASE` target.

## Search Path Role Architecture (SK-C)

The compatible rollout path is additive:

- legacy mode remains DB-per-project via `SKILL_PGDATABASE=<project>-memory`
- shared-DB mode uses an explicit shared DB plus schema isolation
- runtime roles are split into `admin`, `connector`, and `visitor_<identity>`

Role model:

- `admin`: migration/init only; creates schemas, grants, and roles
- `connector`: CONNECT-only credential; opens the DB connection and immediately `SET ROLE`s
- `visitor_<identity>`: daily query role with enforced `search_path`

Visitor search_path matrix:

- `visitor_hermes`: `ep_scope, sk_scope, fd_scope, work_scope, public`
- `visitor_atlas`: `ep_scope, public`
- `visitor_athena`: `sk_scope, public`
- `visitor_fd_coder`: `fd_scope, public`
- `visitor_ep_coder`: `ep_scope, public`
- `visitor_sk_coder`: `sk_scope, public`
- `visitor_hephaestion`: `work_scope, sk_scope, public`

Shared-DB mode is explicit and backward-compatible:

- `SKILL_PGSHARED_DB=<shared-db-name>` selects the shared DB
- `SKILL_PGSCHEMA=<scope-schema>` selects the local writable schema
- `SKILL_PGDATABASE` remains valid for legacy DB-per-project mode

Runtime flow in shared-DB mode:

1. connect as `connector`
2. `SET ROLE visitor_<identity>`
3. rely on the visitor `search_path` for scoped queries
4. allow Hermes-only explicit override for all-scopes audit queries

Shared-DB examples:

```bash
# Provision shared DB schemas/roles for SK scope
python3 scripts/mem.py init-project \
  --project-id sk \
  --db-name shared-memory \
  --schema-name sk_scope \
  --shared-db-mode

# Run local SK queries through shared DB scope
SKILL_PGSHARED_DB=shared-memory \
SKILL_PGSCHEMA=sk_scope \
SKILL_VISITOR_ROLE=visitor_sk_coder \
python3 scripts/mem.py search "router policy"

# Hermes explicit all-scopes audit across the shared DB
SKILL_PGSHARED_DB=shared-memory \
SKILL_PGSCHEMA=sk_scope \
SKILL_VISITOR_ROLE=visitor_hermes \
python3 scripts/mem.py search "friction skill-system-comms" --all-scopes
```

## Cross-DB Visibility Contract

- `visibility=private` (default): searchable only inside the DB where the memory lives; legacy rows without visibility read as `private`
- `visibility=shared`: searchable across configured memory DBs when federation is explicit
- `visibility=global`: searchable across configured memory DBs and eligible for cross-DB auto context injection
- Cross-DB results must include `source_db`
- Cross-DB ranking uses normalized fusion (RRF-style), not raw per-DB score comparison
- Known DB fan-out targets are configured in `config/insight.yaml` under `memory.cross_db_targets`
- All mutation paths (`store`, `update`, `delete`, `purge`) stay local to the active `SKILL_PGDATABASE` target even when read-side cross-DB federation is enabled

### Auto-detection (preferred)

When no env vars are set, the DB name is auto-derived from the project directory name:

- Plugin (JS): uses `directory` passed by OpenCode host → last path segment → `<dir>-memory`
- mem.py CLI: uses script's root directory or cwd → `<dir>-memory`
- MCP server: uses `Path.cwd()` at startup → `<dir>-memory`

**Do NOT set `SKILL_PGDATABASE` globally.** It overrides auto-detection for ALL projects.

```bash
# WRONG — breaks project isolation
export SKILL_PGDATABASE=skills-memory   # every project now hits skills-memory

# RIGHT — let auto-detection work (no env var needed)
cd /path/to/FraudDetect && opencode      # → FraudDetect-memory
cd /path/to/skills && opencode           # → skills-memory
```

### Per-project override (when needed)

If you must override, set `SKILL_PGDATABASE` at **project level**, not globally:

- In project's `.opencode` config or launch script
- In project's `.env` file (if your host reads it)
- As a prefix to the command: `SKILL_PGDATABASE=FraudDetect-memory opencode`

### Fail-closed policy

If `PGDATABASE` is set but `SKILL_PGDATABASE` is not, all entry points **refuse to connect**. This prevents accidental cross-project memory pollution.

### MCP server path for multi-project setups

When skills are globally installed at `~/.agents/skills/`, configure MCP with the **absolute path** to the global install:

```json
{
  "skill-system-memory": {
    "command": "python3",
    "args": ["~/.agents/skills/skill-system-memory/mcp/server.py"]
  }
}
```

Do NOT use relative paths like `skills/skill-system-memory/mcp/server.py` — they break in projects that don't have a local `skills/` directory.

## Quick Start

Database `<project>-memory` and all functions are created by `init.sql` in this skill directory.
The current supported-now runtime surface covers core memory, typed context reads, the typed evolution ledger, and doctor/compaction support.
Session/project/context projection tables and behavior graph refresh remain optional capability-gated surfaces, not unconditional runtime guarantees.

```bash
# Linux — replace 'postgres' with your PostgreSQL superuser if different (e.g. your system username)
psql -U postgres -c "CREATE DATABASE <project>-memory;"
psql -U postgres -d <project>-memory -f init.sql

# Windows (adjust path to your psql.exe; replace 'postgres' with your PG superuser if needed)
& "C:\Program Files\PostgreSQL\18\bin\psql.exe" -U postgres -c "CREATE DATABASE <project>-memory;"
& "C:\Program Files\PostgreSQL\18\bin\psql.exe" -U postgres -d <project>-memory -f init.sql
```

> **Note**: If your PostgreSQL installation does not have a `postgres` role, use your actual
> PostgreSQL superuser name. On many Linux distros this matches your OS username.
> You can override at any time by setting `PGUSER` before running scripts:
> `export PGUSER=your_pg_username` (Linux/macOS) or `$env:PGUSER = "your_pg_username"` (PowerShell).

Verify: `SELECT * FROM memory_health_check();`

## MCP Server (Phase 6.1)

This skill includes an MCP (Model Context Protocol) server that exposes memory operations as agent tools.

### MCP Server Setup

```bash
# Install dependencies
cd skills/skill-system-memory/mcp
pip install -r requirements.txt

# Verify installation
python server.py --help

# Run with stdio transport (default)
python server.py

# Run with SSE transport (for remote access)
MCP_TRANSPORT=sse MCP_PORT=8000 python server.py

# Or via MCP CLI
mcp dev server.py
```

### MCP Environment Variables

| Variable | Default | Description |
|----------|---------|-------------|
| `SKILL_PGDATABASE` | `<project>-memory` | Legacy DB-per-project PostgreSQL database name |
| `SKILL_PGSHARED_DB` | (unset) | Shared PostgreSQL database name for search_path mode |
| `SKILL_PGSCHEMA` | (unset) | Local writable schema for search_path mode |
| `PGHOST` | `localhost` | PostgreSQL host |
| `PGPORT` | `5432` | PostgreSQL port |
| `PGUSER` | (optional) | PostgreSQL user |
| `MCP_TRANSPORT` | `stdio` | Transport mode (`stdio` or `sse`) |
| `MCP_PORT` | `8000` | Port for SSE transport |

### MCP Tools

| Tool | Description |
|------|-------------|
| `memory_search` | Search memories by natural language query (hybrid: FTS + trigram + pgvector) |
| `memory