SQLite Schema

Use a local SQLite database for persistent state. The helper defaults to ~/.codex/state/price-watcher/price-watcher.sqlite3 unless the user supplies --db or sets PRICE_WATCHER_STATE.

Tables

CREATE TABLE IF NOT EXISTS items (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  query TEXT NOT NULL,
  normalized_name TEXT,
  target_price REAL,
  created_at TEXT NOT NULL,
  active INTEGER NOT NULL DEFAULT 1
);

CREATE TABLE IF NOT EXISTS sources (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  item_id INTEGER NOT NULL REFERENCES items(id) ON DELETE CASCADE,
  site TEXT NOT NULL,
  url TEXT NOT NULL,
  last_checked_at TEXT,
  UNIQUE(item_id, url)
);

CREATE TABLE IF NOT EXISTS price_checks (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  item_id INTEGER NOT NULL REFERENCES items(id) ON DELETE CASCADE,
  source_id INTEGER NOT NULL REFERENCES sources(id) ON DELETE CASCADE,
  price REAL NOT NULL,
  currency TEXT NOT NULL,
  checked_at TEXT NOT NULL
);

Notes

• Store the user's natural-language product query in items.query.
• Store a concise canonical product name in items.normalized_name once a confident match exists.
• Keep items.target_price numeric and currency-neutral in the v1 schema. Report the inferred currency in prose when it matters.
• Allow multiple source rows per item.
• Update sources.last_checked_at every time a source is attempted, even if extraction fails and the failure only appears in the Markdown report. Use sources mark-checked --source-id <id> when a source was attempted but no price row should be inserted.
• Append to price_checks; do not overwrite history.