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 · Docs: https://developers.openai.com/codex/cli/
• Responses API: https://platform.openai.com/docs/api-reference/responses
• Structured Outputs: https://platform.openai.com/docs/guides/structured-outputs
• Embeddings guide: https://platform.openai.com/docs/guides/embeddings
• text-embedding-3-small announcement: https://openai.com/index/new-embedding-models-and-api-updates/
• SQLite FTS5: https://www.sqlite.org/fts5.html
• sqlite-vec (Python): https://alexgarcia.xyz/sqlite-vec/python.html · Repo: https://github.com/asg017/sqlite-vec
• Streamlit API: https://docs.streamlit.io/develop/api-reference
• Typer: https://typer.tiangolo.com/
• RapidFuzz: https://rapidfuzz.github.io/RapidFuzz/
• WSL2 systemd: https://learn.microsoft.com/windows/wsl/systemd
• systemd timers: 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 and mentions ~/.codex/config.toml.
• WSL2 Ubuntu with zsh; optional systemd user services: 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, update post: New models.

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):

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
• sqlite-vec usage: declare vec0, insert serialized vectors, query with MATCH and ORDER BY distance: https://alexgarcia.xyz/sqlite-vec/python.html

Vector query example:

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 Optional components:

  • Copy button: st-copy-to-clipboard https://github.com/mmz-001/st-copy-to-clipboard
  • Editors: streamlit-ace https://github.com/okld/streamlit-ace or streamlit-code-editor https://github.com/bouzidanas/streamlit-code-editor

F8. CLI (Typer)

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/

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 Timers manual: 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 · 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/
• Typer over argparse. Better DX: https://typer.tiangolo.com/
• RapidFuzz for near-dupe: 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

{
  "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
• Structured Outputs guide: 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

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)

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 Create user units under ~/.config/systemd/user/; timers ref: systemd.timer

20) References

• Codex CLI repo: https://github.com/openai/codex · Product page: https://developers.openai.com/codex/cli/

• Responses API: https://platform.openai.com/docs/api-reference/responses · Migration guide: https://platform.openai.com/docs/guides/migrate-to-responses

• Structured Outputs: https://platform.openai.com/docs/guides/structured-outputs

• Embeddings: https://platform.openai.com/docs/guides/embeddings · New model post: https://openai.com/index/new-embedding-models-and-api-updates/

• SQLite FTS5: https://www.sqlite.org/fts5.html

• sqlite-vec Python usage: https://alexgarcia.xyz/sqlite-vec/python.html · Repo: https://github.com/asg017/sqlite-vec

• Streamlit: https://docs.streamlit.io/develop/api-reference

• Typer: https://typer.tiangolo.com/

• RapidFuzz: https://rapidfuzz.github.io/RapidFuzz/

• WSL systemd: https://learn.microsoft.com/windows/wsl/systemd

• systemd timers: https://www.freedesktop.org/software/systemd/man/systemd.timer.html

• Optional Streamlit components:

  • Copy: https://github.com/mmz-001/st-copy-to-clipboard
  • Editor (Ace): https://github.com/okld/streamlit-ace
  • Editor (react-ace): 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.