popagent

Latest change 87f51a535b92b11ccaf217f96b05a14c907d8eac - Store project documentation in PostgreSQL by Ólafur Búi Ólafsson

CREATE TABLE IF NOT EXISTS popagent_data_migrations (
  id TEXT PRIMARY KEY,
  applied_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

DO $migration$
BEGIN
  IF EXISTS (
    SELECT 1 FROM popagent_data_migrations
    WHERE id = '2026-08-15-documentation-rag-v1'
  ) THEN
    RETURN;
  END IF;

  CREATE TABLE popagent_document_sources (
    workspace_id TEXT NOT NULL REFERENCES popagent_agent_workspaces(id) ON DELETE CASCADE,
    path TEXT NOT NULL,
    title TEXT NOT NULL,
    content_hash TEXT NOT NULL,
    embedding_model TEXT NOT NULL,
    embedding_dimension INTEGER NOT NULL CHECK (embedding_dimension > 0),
    chunk_count INTEGER NOT NULL CHECK (chunk_count >= 0),
    status TEXT NOT NULL CHECK (status IN ('indexed', 'failed')),
    error TEXT,
    indexed_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    PRIMARY KEY (workspace_id, path)
  );

  INSERT INTO popagent_data_migrations (id)
  VALUES ('2026-08-15-documentation-rag-v1');
END
$migration$;

DO $database_documents$
BEGIN
  IF EXISTS (
    SELECT 1 FROM popagent_data_migrations
    WHERE id = '2026-08-15-database-documentation-v1'
  ) THEN
    RETURN;
  END IF;

  CREATE TABLE popagent_document_folders (
    workspace_id TEXT NOT NULL REFERENCES popagent_agent_workspaces(id) ON DELETE CASCADE,
    path TEXT NOT NULL,
    name TEXT NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    PRIMARY KEY (workspace_id, path)
  );

  CREATE TABLE popagent_document_pages (
    workspace_id TEXT NOT NULL REFERENCES popagent_agent_workspaces(id) ON DELETE CASCADE,
    path TEXT NOT NULL,
    title TEXT NOT NULL,
    content TEXT NOT NULL,
    revision TEXT NOT NULL,
    source_note_id BIGINT,
    source_notebook_id BIGINT,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    PRIMARY KEY (workspace_id, path),
    UNIQUE (source_note_id)
  );

  INSERT INTO popagent_data_migrations (id)
  VALUES ('2026-08-15-database-documentation-v1');
END
$database_documents$;