n8n + Postgres Agent Skills - companion code

gistfile1.txt

# n8n + Postgres Agent Skills — companion code

This Gist holds the full code referenced from the forum post about implementing Anthropic-style Agent Skills inside n8n with progressive disclosure and a dedicated Postgres container.

The post itself stays prose-only to keep it readable (and to keep CDN-level firewalls happy with long blocks of DDL). All the actual code is here.

---

## 1. Postgres schema

Save as `schema.sql`, run once against the `wwu_skills` database (or whatever you named it). Creates the namespace, the three tables, the history trigger, the partial index, and enables the `pgvector` extension up-front so the embedding column is usable without future migration.

```sql
CREATE SCHEMA IF NOT EXISTS skills_mgmt;

-- Day 1: enable the extension. Zero cost if not used.
CREATE EXTENSION IF NOT EXISTS vector;

CREATE TABLE skills_mgmt.skills (
    name        VARCHAR(80)  PRIMARY KEY,
    description TEXT         NOT NULL,
    content     TEXT         NOT NULL,
    active      BOOLEAN      NOT NULL DEFAULT TRUE,
    version     INTEGER      NOT NULL DEFAULT 1,
    -- Embedding column, nullable for now. Dimension matches your embedding
    -- model: 1536 for text-embedding-3-small, 3072 for text-embedding-3-large.
    description_embedding vector(1536),
    created_at  TIMESTAMPTZ  NOT NULL DEFAULT NOW(),
    updated_at  TIMESTAMPTZ  NOT NULL DEFAULT NOW(),
    CHECK (name ~ '^[a-z0-9-]+$')
);

CREATE TABLE skills_mgmt.skill_references (
    id              SERIAL PRIMARY KEY,
    skill_name      VARCHAR(80) NOT NULL
                    REFERENCES skills_mgmt.skills(name) ON DELETE CASCADE,
    reference_name  VARCHAR(120) NOT NULL,
    content         TEXT NOT NULL,
    updated_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    UNIQUE (skill_name, reference_name),
    CHECK (reference_name ~ '^[a-z0-9_.-]+$')
);

CREATE TABLE skills_mgmt.skills_history (
    id          BIGSERIAL PRIMARY KEY,
    name        VARCHAR(80) NOT NULL,
    description TEXT,
    content     TEXT,
    version     INTEGER,
    archived_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE OR REPLACE FUNCTION skills_mgmt.fn_skills_history()
RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO skills_mgmt.skills_history(name, description, content, version)
    VALUES (OLD.name, OLD.description, OLD.content, OLD.version);
    NEW.version := OLD.version + 1;
    NEW.updated_at := NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_skills_history
BEFORE UPDATE ON skills_mgmt.skills
FOR EACH ROW EXECUTE FUNCTION skills_mgmt.fn_skills_history();

CREATE INDEX idx_skills_active
    ON skills_mgmt.skills(active) WHERE active = TRUE;
```

Optional, only if/when you populate the embedding column:

```sql
-- HNSW is the recommended ANN index in pgvector >= 0.5.
-- Skip until you have data in description_embedding.
CREATE INDEX idx_skills_emb_hnsw
    ON skills_mgmt.skills
    USING hnsw (description_embedding vector_cosine_ops);
```

---

## 2. `docker-compose.yml` snippet

Add this service alongside your existing n8n service, on the same Docker network. No port published to the host — only n8n reaches Postgres over the internal network.

```yaml
services:
  wwu-skills-db:
    image: pgvector/pgvector:pg16
    container_name: wwu-skills-db
    restart: unless-stopped
    environment:
      POSTGRES_DB: wwu_skills
      POSTGRES_USER: wwu_skills_user
      POSTGRES_PASSWORD: ${WWU_SKILLS_DB_PASSWORD}
    volumes:
      - wwu_skills_data:/var/lib/postgresql/data
    networks:
      - n8n_network
volumes:
  wwu_skills_data:
networks:
  n8n_network:
    external: true
```

In n8n, create a Postgres credential pointing at host `wwu-skills-db`, port `5432`, database `wwu_skills`, user/password from your `.env`.

---

## 3. `tool_load_skill` sub-workflow

Three nodes: Execute Workflow Trigger → Postgres → Set.

### 3.1 Execute Workflow Trigger

- `inputSource: workflowInputs`
- Inputs declared:
  - `skill_name` (string)

### 3.2 Postgres node, mode `executeQuery`

```sql
SELECT name, description, content
FROM skills_mgmt.skills
WHERE name = $1 AND active = TRUE
LIMIT 1;
```

- `options.queryReplacement`: `={{ $json.skill_name }}`

### 3.3 Set node

Normalises output to either `{ skill_name, content }` on hit or `{ error: "Skill not found" }` on miss.

---

## 4. `tool_load_reference` sub-workflow

Same shape as 3, with two string inputs.

### 4.1 Execute Workflow Trigger

- `inputSource: workflowInputs`
- Inputs declared:
  - `skill_name` (string)
  - `reference_name` (string)

### 4.2 Postgres node, mode `executeQuery`

```sql
SELECT content
FROM skills_mgmt.skill_references
WHERE skill_name = $1 AND reference_name = $2
LIMIT 1;
```

- `options.queryReplacement`: `={{ $json.skill_name }},={{ $json.reference_name }}`

---

## 5. MAIN_AGENT — `Build Index` Code node

Six lines. Maps the rows from the Postgres select into a compact JSON string that the system message will inject.

```javascript
/**
 * Build a compact JSON index for the system prompt.
 * Keep description short, every char costs tokens at every turn.
 */
const rows = $input.all().map(i => i.json);
const index = rows.map(r => ({
  name: r.name,
  description: r.description
}));
return [{ json: { skills_index: JSON.stringify(index, null, 2) } }];
```

---

## 6. AI Agent system message

Paste this verbatim into `options.systemMessage` of the AI Agent node. The expression at the bottom injects the manifest from the previous Code node.

```
You are the WWU AI Agent.

BEFORE performing any non-trivial task, scan SKILLS_INDEX below for a matching skill.
If a skill matches the user request, you MUST call the `load_skill` tool with its
exact name BEFORE producing any output. Never invent skill names. Never produce a
deliverable that should use a skill without first loading it.

If a loaded skill instructs you to read a reference file, call `load_reference`
only when the current task actually needs that level of detail.

SKILLS_INDEX:
{{ $('Build Index').item.json.skills_index }}
```

If you renamed your Code node, update the expression accordingly.

---

## 7. Tool Workflow descriptions

These go in the `description` field of each Tool Workflow inside the AI Agent. The LLM reads them to decide when to call each tool — write them as if they were API contracts.

### load_skill

> Load full instructions for a specific skill BEFORE executing the task. Always call this when the user request matches a skill listed in SKILLS_INDEX. Returns the full skill content as text. Parameter: `skill_name` (string) — must exactly match an entry in SKILLS_INDEX.

### load_reference

> Load an additional reference file for an already-loaded skill. Call ONLY when the loaded skill explicitly references the file AND the current task actually needs that level of detail. Parameters: `skill_name` (string), `reference_name` (string, e.g. `examples.md`).

For each Tool Workflow's `workflowInputs`, click the **AI button** on every parameter so the LLM populates them via `$fromAI()`. Don't hardcode values.

---

## 8. Postgres node configuration for MAIN_AGENT (manifest read)

The first Postgres node in the main workflow that pulls the manifest. Configure as a structured `select` operation, not raw SQL — keeps the workflow JSON clean and readable.

- Operation: `select`
- Schema: `skills_mgmt`
- Table: `skills`
- Where:
  - column: `active`
  - condition: `equal`
  - value: `true`
- Combine conditions: `AND`
- Output columns: `name`, `description` (intentionally not `content` — that would balloon the manifest)
- Return all: `true`

This is the input that feeds the `Build Index` Code node above.