Move daily notes files into database for semantic search embedding

[NOVA-Openclaw]

Summary

NOVA's daily session notes are currently stored as flat markdown files in ~/clawd/memory/YYYY-MM-DD.md. While these get embedded nightly into memory_embeddings (as daily_log source type — 770+ embeddings), the source files themselves live outside the database.

Current Flow

1. NOVA writes daily notes to ~/clawd/memory/YYYY-MM-DD.md during sessions
2. A nightly batch process reads these files, chunks them, and generates embeddings
3. Embeddings are stored in memory_embeddings table with source_type = 'daily_log'

Proposed Change

Store daily notes directly in the database (e.g., a daily_logs table) instead of flat files. Benefits:

1. Real-time embedding — Can trigger embedding generation via NOTIFY on insert/update instead of waiting for nightly batch
2. Structured metadata — Date, agent, session context as columns instead of parsed from filenames
3. Unified search — All memory sources in one place, queryable with SQL
4. Backup/replication — Included in pg_dump, no separate file backup needed
5. Multi-agent support — Multiple agents can write daily logs without filesystem coordination

Proposed Schema

CREATE TABLE daily_logs (
  id SERIAL PRIMARY KEY,
  log_date DATE NOT NULL,
  agent_id INTEGER REFERENCES agents(id) DEFAULT 1,
  content TEXT NOT NULL,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW(),
  UNIQUE(log_date, agent_id)
);

-- Trigger to auto-update updated_at
CREATE TRIGGER daily_logs_updated_at
  BEFORE UPDATE ON daily_logs
  FOR EACH ROW EXECUTE FUNCTION update_updated_at();

-- NOTIFY for embedding pipeline
CREATE OR REPLACE FUNCTION notify_daily_log_change() RETURNS trigger AS $$
BEGIN
  PERFORM pg_notify('daily_log_changed', json_build_object(
    'id', NEW.id,
    'log_date', NEW.log_date,
    'agent_id', NEW.agent_id
  )::text);
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER daily_logs_notify
  AFTER INSERT OR UPDATE ON daily_logs
  FOR EACH ROW EXECUTE FUNCTION notify_daily_log_change();

Migration Path

1. Create the table and triggers
2. Import existing ~/clawd/memory/YYYY-MM-DD.md files into the table
3. Update NOVA's session write path to INSERT/UPDATE the table instead of writing files
4. Update the embedding pipeline to listen for daily_log_changed NOTIFY
5. Keep flat files as read-only archive (or generate from DB for backward compat)

Related

• memory_embeddings table (existing embedding storage)
• source_type = 'daily_log' (770+ existing embeddings from flat files)
• Semantic recall hook (reads embeddings for context injection)