Product Requirements Document — `codex-prompt-refinery`

# Product Requirements Document — `codex-prompt-refinery`

Author: Bjorn Melin

## 1) Summary

Local tool that ingests **OpenAI Codex CLI** history under `~/.codex/**`, extracts prompts and nearby assistant replies, deduplicates and groups related prompts, and auto-synthesizes **atomic** and **workflow** prompts via the **OpenAI Responses API** using **Structured Outputs**. Single **SQLite** database with **FTS5** keyword search and **sqlite-vec** semantic search. Minimal **Streamlit** UI to search, view, specialize, and copy prompts. Optional daily automation via **systemd** on WSL2.

* Codex CLI: [https://github.com/openai/codex](https://github.com/openai/codex) · Docs: [https://developers.openai.com/codex/cli/](https://developers.openai.com/codex/cli/)
* Responses API: [https://platform.openai.com/docs/api-reference/responses](https://platform.openai.com/docs/api-reference/responses)
* Structured Outputs: [https://platform.openai.com/docs/guides/structured-outputs](https://platform.openai.com/docs/guides/structured-outputs)
* Embeddings guide: [https://platform.openai.com/docs/guides/embeddings](https://platform.openai.com/docs/guides/embeddings)
* `text-embedding-3-small` announcement: [https://openai.com/index/new-embedding-models-and-api-updates/](https://openai.com/index/new-embedding-models-and-api-updates/)
* SQLite FTS5: [https://www.sqlite.org/fts5.html](https://www.sqlite.org/fts5.html)
* sqlite-vec (Python): [https://alexgarcia.xyz/sqlite-vec/python.html](https://alexgarcia.xyz/sqlite-vec/python.html) · Repo: [https://github.com/asg017/sqlite-vec](https://github.com/asg017/sqlite-vec)
* Streamlit API: [https://docs.streamlit.io/develop/api-reference](https://docs.streamlit.io/develop/api-reference)
* Typer: [https://typer.tiangolo.com/](https://typer.tiangolo.com/)
* RapidFuzz: [https://rapidfuzz.github.io/RapidFuzz/](https://rapidfuzz.github.io/RapidFuzz/)
* WSL2 systemd: [https://learn.microsoft.com/windows/wsl/systemd](https://learn.microsoft.com/windows/wsl/systemd)
* systemd timers: [https://www.freedesktop.org/software/systemd/man/systemd.timer.html](https://www.freedesktop.org/software/systemd/man/systemd.timer.html)

## 2) Objectives

* Distill reusable, one-shot prompts to cut repeated follow-ups.
* Local-only. One developer. One-day build.
* Hybrid search: exact (FTS5) + semantic (sqlite-vec).
* Clean UI to browse, edit lightly, copy or download.

## 3) Out of scope

Multi-user, auth/RBAC, cloud deploy, agent frameworks, external vector DBs, permanent daemons.

## 4) Operating assumptions

* Codex CLI config at `~/.codex/config.toml`; histories live under `~/.codex/**` as JSON/JSONL. Upstream notes config here: [README Configuration](https://github.com/openai/codex#readme) and mentions `~/.codex/config.toml`.
* WSL2 Ubuntu with zsh; optional systemd user services: [WSL systemd](https://learn.microsoft.com/windows/wsl/systemd).
* OpenAI API key via env for embeddings and synthesis.

## 5) Functional requirements

### F1. Ingest

* Inputs: default glob `~/.codex/**` for `*.json` and `*.jsonl`.
* Tolerant JSON/JSONL parsing; extract `ts` (ISO8601 or epoch), `role` (`user|assistant|system`), `text`, `session_id`/`conversation_id` when present, and raw line.
* Pair `user` with next `assistant` in same session (forward scan).
* Store raw JSON for provenance.

### F2. Canonicalize & dedupe

* Canonical text: lowercase, collapse whitespace, strip timestamps/UUIDs, truncate stack traces to N lines.
* Exact dedupe: `sha256(canonical_text)`.
* Near-dup gate: RapidFuzz `partial_ratio >= 90`; suppress unless length delta > 15%.

### F3. Embeddings

* Embed `user` prompts and optimized prompts with `text-embedding-3-small` (default 1536-D; allow `dimensions` override).
  Guides: [Embeddings](https://platform.openai.com/docs/guides/embeddings), update post: [New models](https://openai.com/index/new-embedding-models-and-api-updates/).

### F4. Grouping (KISS)

* Per day greedy clustering by cosine ≥ 0.86 using sqlite-vec KNN. Seed by time; attach neighbors above threshold. Save `cluster_hint` from first prompt’s keywords.

### F5. Synthesis (LLM)

* For each cluster, send 3–10 related `user` prompts plus selected `assistant` snippets to **Responses API** using **Structured Outputs** JSON Schema (see §12).
* Return:

  * `optimized_atomic_prompts[]`
  * `optimized_workflow_prompt`
  * `rationale`
* Each prompt includes: `title`, `prompt_markdown` (fenced, copy-ready), `variables[] {name,type,example,description}`, `io_contract {inputs,outputs}`, and `citations[]` of source raw IDs.
* Default model: `gpt-5-mini`. Allow `--model gpt-5`.

### F6. Storage & search

Schema (day-1):

```sql
CREATE TABLE prompt_raw (
  id INTEGER PRIMARY KEY,
  source_path TEXT NOT NULL,
  session_id TEXT,
  conversation_id TEXT,
  role TEXT CHECK(role IN ('user','assistant','system')),
  ts TEXT,
  text TEXT NOT NULL,
  canonical_hash TEXT NOT NULL,
  raw_json TEXT
);

CREATE TABLE prompt_optimized (
  id INTEGER PRIMARY KEY,
  kind TEXT CHECK(kind IN ('atomic','workflow')) NOT NULL,
  title TEXT,
  text_md TEXT NOT NULL,
  variables_json TEXT,
  io_contract_json TEXT,
  rationale TEXT,
  created_at TEXT,
  cluster_hint TEXT,
  gpt_meta_json TEXT
);

CREATE TABLE prompt_link (
  optimized_id INTEGER,
  raw_id INTEGER,
  PRIMARY KEY (optimized_id, raw_id)
);

CREATE VIRTUAL TABLE prompt_raw_fts USING fts5(text);
CREATE VIRTUAL TABLE prompt_opt_fts USING fts5(text_md);

CREATE VIRTUAL TABLE embeddings USING vec0(
  embedding FLOAT[1536],
  kind TEXT CHECK(kind IN ('raw','optimized')),
  item_id INTEGER
);
```

* FTS5: [https://www.sqlite.org/fts5.html](https://www.sqlite.org/fts5.html)
* sqlite-vec usage: declare `vec0`, insert serialized vectors, query with `MATCH` and `ORDER BY distance`:
  [https://alexgarcia.xyz/sqlite-vec/python.html](https://alexgarcia.xyz/sqlite-vec/python.html)

Vector query example:

```sql
SELECT item_id, distance
FROM embeddings
WHERE kind='raw' AND embedding MATCH ?
ORDER BY distance
LIMIT 20;
```

### F7. UI (Streamlit)

* Single page. Search box runs hybrid search (union of FTS top-N and vector top-K) with simple scoring.
* Tabs: **Raw**, **Optimized**. Cards show title/date and actions: **Copy**, **Edit**, **Download**.
* Detail pane shows fenced markdown in `st.code`, a “Specialize” text area, live preview, and Copy/Download.
  Streamlit API: [https://docs.streamlit.io/develop/api-reference](https://docs.streamlit.io/develop/api-reference)
  Optional components:

  * Copy button: `st-copy-to-clipboard` [https://github.com/mmz-001/st-copy-to-clipboard](https://github.com/mmz-001/st-copy-to-clipboard)
  * Editors: `streamlit-ace` [https://github.com/okld/streamlit-ace](https://github.com/okld/streamlit-ace) or `streamlit-code-editor` [https://github.com/bouzidanas/streamlit-code-editor](https://github.com/bouzidanas/streamlit-code-editor)

### F8. CLI (Typer)

```bash
pdr ingest [--date YYYY-MM-DD | --since N] [--path GLOB...]
pdr synthesize [--date YYYY-MM-DD] [--model gpt-5|gpt-5-mini]
pdr ui [--port 8501]
```

Typer docs: [https://typer.tiangolo.com/](https://typer.tiangolo.com/)

### F9. Optional scheduling (WSL2)

* Provide a systemd user service and timer to run `ingest` then `synthesize` daily.
  Enable systemd in WSL2: [https://learn.microsoft.com/windows/wsl/systemd](https://learn.microsoft.com/windows/wsl/systemd)
  Timers manual: [https://www.freedesktop.org/software/systemd/man/systemd.timer.html](https://www.freedesktop.org/software/systemd/man/systemd.timer.html)

## 6) Non-functional

* Local-first. DB < 1 GB for initial days.
* ≤ 5 minutes for \~100 prompts/day.
* Secret redaction via regex before storage and LLM calls.
* Structured logs to stdout; optional rotating file handler.

## 7) Quality gates

* `ruff` and `pylint` clean.
* `pytest` minimal suite passes.
* UI query latency < 150 ms on 10k-row DB.
* CLI idempotent.
* Synthesis JSON validates against the schema in §12.

## 8) Risks & mitigations

* **Log format drift** → tolerant parsing; keep `raw_json`.
* **False merges** → conservative threshold; UI shows cluster sources.
* **Rate limits** → batch embeddings; retry/backoff; small clusters; default to mini model.

## 9) Decision log (KISS/DRY/YAGNI)

* **SQLite + FTS5 + sqlite-vec** over external vector DBs. One file, no servers.
  FTS5: [https://www.sqlite.org/fts5.html](https://www.sqlite.org/fts5.html) · sqlite-vec: [https://github.com/asg017/sqlite-vec](https://github.com/asg017/sqlite-vec)
* **Greedy clustering** over sklearn. Fewer deps; good enough at this scale.
* **Streamlit** over custom web stack. Faster single-day delivery: [https://docs.streamlit.io/](https://docs.streamlit.io/)
* **Typer** over argparse. Better DX: [https://typer.tiangolo.com/](https://typer.tiangolo.com/)
* **RapidFuzz** for near-dupe: [https://rapidfuzz.github.io/RapidFuzz/](https://rapidfuzz.github.io/RapidFuzz/)

## 10) Decision framework (weighted)

| Decision                                                                           | Option     | Leverage 35% | Value 30% | Load 25% | Adapt 10% |   Total |
| ---------------------------------------------------------------------------------- | ---------- | -----------: | --------: | -------: | --------: | ------: |
| Vector store                                                                       | sqlite-vec |            9 |         8 |        8 |         8 | **8.5** |
|                                                                                    | FAISS      |            7 |         7 |        5 |         7 |     6.6 |
| UI                                                                                 | Streamlit  |            8 |         8 |        8 |         7 | **7.9** |
|                                                                                    | Flask+JS   |            6 |         7 |        4 |         8 |     6.1 |
| CLI                                                                                | Typer      |            8 |         8 |        8 |         7 | **7.9** |
|                                                                                    | argparse   |            6 |         7 |        7 |         7 |     6.7 |
| **Rationale:** Highest leverage with lowest maintenance. Library-first. No server. |            |              |           |          |           |         |

## 11) Data model

* `prompt_raw`: one row per ingested message with provenance and canonical hash.
* `prompt_optimized`: synthesized atomic/workflow prompts and metadata.
* `prompt_link`: many-to-many mapping from optimized prompts to source raw rows.
* `prompt_raw_fts`, `prompt_opt_fts`: duplicated text for FTS.
* `embeddings`: `vec0` table with vectors for `raw` and `optimized`.

## 12) Structured Outputs JSON Schema

```json
{
  "type": "object",
  "additionalProperties": false,
  "required": ["optimized_atomic_prompts","optimized_workflow_prompt","rationale"],
  "properties": {
    "optimized_atomic_prompts": {
      "type": "array",
      "items": {
        "type": "object",
        "required": ["title","prompt_markdown","variables","io_contract","citations"],
        "properties": {
          "title": {"type":"string"},
          "prompt_markdown": {"type":"string"},
          "variables": {"type":"array","items":{"type":"object","required":["name"],"properties":{
            "name":{"type":"string"},
            "type":{"type":"string"},
            "example":{"type":"string"},
            "description":{"type":"string"}
          }}}},
          "io_contract": {"type":"object","required":["inputs","outputs"],"properties":{
            "inputs":{"type":"string"},
            "outputs":{"type":"string"}
          }},
          "citations":{"type":"array","items":{"type":"integer"}}
        }
      }
    },
    "optimized_workflow_prompt": {
      "type":"object",
      "required":["title","prompt_markdown","variables","io_contract","citations"],
      "properties":{
        "title":{"type":"string"},
        "prompt_markdown":{"type":"string"},
        "variables":{"type":"array","items":{"type":"object","required":["name"],"properties":{
          "name":{"type":"string"},
          "type":{"type":"string"},
          "example":{"type":"string"},
          "description":{"type":"string"}
        }}}},
        "io_contract":{"type":"object","required":["inputs","outputs"],"properties":{
          "inputs":{"type":"string"},
          "outputs":{"type":"string"}
        }},
        "citations":{"type":"array","items":{"type":"integer"}}
      }
    },
    "rationale":{"type":"string"}
  }
}
```

* Responses API: [https://platform.openai.com/docs/api-reference/responses](https://platform.openai.com/docs/api-reference/responses)
* Structured Outputs guide: [https://platform.openai.com/docs/guides/structured-outputs](https://platform.openai.com/docs/guides/structured-outputs)

## 13) Algorithms

* **Canonicalize(text):** lowercase → strip ISO timestamps/UUIDs → trim stack trace to N lines → collapse whitespace.
* **Near-dup:** `RapidFuzz.partial_ratio(a,b) >= 90` and `abs(|a|-|b|)/|b| ≤ 0.15` → suppress.
* **Clustering:** for each seed id, KNN via sqlite-vec; approximate cosine from L2 for unit-normalized vectors; attach neighbors ≥ 0.86.

## 14) Security and privacy

* Redact secrets (`sk-…`, AWS AKIA keys, long numeric tokens) before storage or LLM calls.
* Store raw JSON for provenance; mark source path and session ids.
* Optional “ZDR” upstream reference for Codex usage: [https://github.com/openai/codex#readme](https://github.com/openai/codex#readme)

## 15) Telemetry and logging

* Structured logs to stdout. Optional rotating file handler.
* Record ingestion counts, embeddings batches, synthesis success/failure, UI search timings.

## 16) CLI UX

* `pdr ingest --date YYYY-MM-DD` or `--since N` and `--path` globs.
* `pdr synthesize --date YYYY-MM-DD --model gpt-5-mini|gpt-5`.
* `pdr ui --port 8501`.

## 17) UI UX

* Search box. Tabs **Raw**/**Optimized**.
* Card → details pane with fenced markdown, “Specialize” text area, live preview, Copy button, and `.md` download.

## 18) Acceptance criteria (Gherkin)

**Feature: Ingest Codex logs**
Scenario: Parse a day’s logs
Given Codex JSON/JSONL files under `~/.codex/` for a specific date
When I run `pdr ingest --date YYYY-MM-DD`
Then rows are inserted into `prompt_raw` and `prompt_raw_fts` without duplicates
And each row stores `source_path`, `role`, `ts`, `text`, `canonical_hash`, and `raw_json`
And the command exits with code 0.

**Feature: Dedupe and near-dup gate**
Scenario: Re-ingest the same files
Given the DB already contains prompts from the files
When I re-run `pdr ingest` on the same paths
Then duplicate canonical hashes are skipped
And near-duplicates with `partial_ratio ≥ 90` are suppressed unless length delta > 15%.

**Feature: Embeddings**
Scenario: Embed new raw prompts
Given new `user` prompts exist without vectors
When I run `pdr synthesize --date YYYY-MM-DD`
Then `embeddings` rows are created with kind=`raw` and 1536-D vectors
And API errors are handled with retry/backoff.

**Feature: Grouping**
Scenario: Greedy clustering
Given embedded prompts for the day
When grouping runs with threshold 0.86
Then clusters contain semantically related prompts
And each cluster stores a `cluster_hint`.

**Feature: Synthesis**
Scenario: Generate optimized prompts
Given grouped clusters with ≥3 related prompts
When synthesis runs via Responses API with Structured Outputs
Then JSON conforms to the schema in §12
And `prompt_optimized` rows are inserted for atomic and workflow prompts
And back-links are written to `prompt_link`.

**Feature: Search**
Scenario: Hybrid search returns relevant rows
Given prompts exist with FTS and vectors
When I search with a keyword and natural language
Then results include FTS matches and cosine-nearest items
And results are ranked by a simple weighted score.

**Feature: UI**
Scenario: Browse, edit, copy
Given the Streamlit app is running
When I search, open a prompt, and type a specialization
Then a live preview updates
And I can copy to clipboard and download a `.md` snippet.

**Feature: CLI and runbook**
Scenario: Commands work end-to-end
Given Python 3.11+
When I run the runbook commands
Then the DB is created, ingest and synthesize complete, and the UI is reachable on the given port.

**Feature: Tooling**
Scenario: Lint and tests
Given the repo root
When I run `ruff` and `pytest`
Then both succeed.

## 19) Runbook (WSL2, zsh)

```bash
uv venv && source .venv/bin/activate
uv pip install -e ".[dev]"
pdr ingest --since 1
pdr synthesize --date "$(date -I)" --model gpt-5-mini
pdr ui --port 8501
```

Enable systemd for daily automation: [WSL systemd](https://learn.microsoft.com/windows/wsl/systemd)
Create user units under `~/.config/systemd/user/`; timers ref: [systemd.timer](https://www.freedesktop.org/software/systemd/man/systemd.timer.html)

## 20) References

* Codex CLI repo: [https://github.com/openai/codex](https://github.com/openai/codex) · Product page: [https://developers.openai.com/codex/cli/](https://developers.openai.com/codex/cli/)
* Responses API: [https://platform.openai.com/docs/api-reference/responses](https://platform.openai.com/docs/api-reference/responses) · Migration guide: [https://platform.openai.com/docs/guides/migrate-to-responses](https://platform.openai.com/docs/guides/migrate-to-responses)
* Structured Outputs: [https://platform.openai.com/docs/guides/structured-outputs](https://platform.openai.com/docs/guides/structured-outputs)
* Embeddings: [https://platform.openai.com/docs/guides/embeddings](https://platform.openai.com/docs/guides/embeddings) · New model post: [https://openai.com/index/new-embedding-models-and-api-updates/](https://openai.com/index/new-embedding-models-and-api-updates/)
* SQLite FTS5: [https://www.sqlite.org/fts5.html](https://www.sqlite.org/fts5.html)
* sqlite-vec Python usage: [https://alexgarcia.xyz/sqlite-vec/python.html](https://alexgarcia.xyz/sqlite-vec/python.html) · Repo: [https://github.com/asg017/sqlite-vec](https://github.com/asg017/sqlite-vec)
* Streamlit: [https://docs.streamlit.io/develop/api-reference](https://docs.streamlit.io/develop/api-reference)
* Typer: [https://typer.tiangolo.com/](https://typer.tiangolo.com/)
* RapidFuzz: [https://rapidfuzz.github.io/RapidFuzz/](https://rapidfuzz.github.io/RapidFuzz/)
* WSL systemd: [https://learn.microsoft.com/windows/wsl/systemd](https://learn.microsoft.com/windows/wsl/systemd)
* systemd timers: [https://www.freedesktop.org/software/systemd/man/systemd.timer.html](https://www.freedesktop.org/software/systemd/man/systemd.timer.html)
* Optional Streamlit components:

  * Copy: [https://github.com/mmz-001/st-copy-to-clipboard](https://github.com/mmz-001/st-copy-to-clipboard)
  * Editor (Ace): [https://github.com/okld/streamlit-ace](https://github.com/okld/streamlit-ace)
  * Editor (react-ace): [https://github.com/bouzidanas/streamlit-code-editor](https://github.com/bouzidanas/streamlit-code-editor)

**K/D/Y:**

* KISS: single SQLite file; no servers.
* DRY: shared ingest/canonicalize utils; duplicated text into FTS by design for day-1.
* YAGNI: no background daemon; greedy clustering instead of sklearn.