Open Specifications

{
  "metadata": {
    "description": "Brief description of this import file",
    "instructions": "Any notes about the structure"
  },
  "structures": [
    {
      "name": "Your Structure Name",
      "description": "Describe what this structure extracts",
      "overview_prompt": "These documents are [type] from [source]. They contain [topics].",
      "structure_data": [
        {
          "name": "field_name",
          "type": "string",
          "description": "The question/instruction for what to extract",
          "required": true
        }
      ]
    }
  ]
}

{
  "metadata": {
    "description": "FactExtract.Net Structure for Historical Correspondence",
    "instructions": "Extracts key information from letters, memos, and other correspondence."
  },
  "structures": [
    {
      "name": "Historical Correspondence",
      "description": "Extract key information from historical letters and memos",
      "overview_prompt": "These documents are [type of correspondence, e.g., letters, memos] from [time period/source]. They involve [parties/organizations] and contain information about [topics/events].",
      "structure_data": [
        {
          "name": "document_date",
          "type": "date",
          "description": "The date the document was written or sent",
          "required": true
        },
        {
          "name": "author",
          "type": "string",
          "description": "Who wrote or sent the document",
          "required": true
        },
        {
          "name": "recipient",
          "type": "string",
          "description": "Who the document was addressed to",
          "required": true
        },
        {
          "name": "subject_summary",
          "type": "string",
          "description": "A one-sentence summary of the document's main topic",
          "required": true
        },
        {
          "name": "people_mentioned",
          "type": "array",
          "description": "List all people mentioned in the document by name",
          "required": true
        },
        {
          "name": "key_facts",
          "type": "string",
          "description": "List the most important facts, events, or claims made",
          "required": true
        }
      ]
    }
  ]
}

-- ===========================================================
-- Core Tables (created by FactExtract.Net processing)
-- ===========================================================

-- Document boundary detection results
CREATE TABLE IF NOT EXISTS "ChunkMetadata" (
    "ChunkID"       INTEGER PRIMARY KEY AUTOINCREMENT,
    "ChunkMethod"   TEXT NOT NULL,
    "ChunkSize"     INTEGER,
    "ContextToken"  TEXT,
    "OriginalPrompt" TEXT,
    "Title"         TEXT,
    "StartPage"     INTEGER,
    "EndPage"       INTEGER,
    "Confidence"    REAL,
    "ProcessedAt"   TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Extracted field values linked to pages, fields, and chunks
CREATE TABLE IF NOT EXISTS "LLMResults" (
    "ResponseID"    INTEGER PRIMARY KEY AUTOINCREMENT,
    "PageID"        INTEGER,
    "LLMResponse"   TEXT NOT NULL,
    "FieldID"       INTEGER,
    "ChunkID"       INTEGER,
    "ProcessedAt"   TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY("ChunkID") REFERENCES "ChunkMetadata"("ChunkID"),
    FOREIGN KEY("FieldID") REFERENCES "ResponseSchema"("FieldID"),
    FOREIGN KEY("PageID") REFERENCES "pages"("page_id")
);

-- Processing event log
CREATE TABLE IF NOT EXISTS "ProcessLog" (
    "LogID"         INTEGER PRIMARY KEY AUTOINCREMENT,
    "Timestamp"     TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    "EventType"     TEXT NOT NULL,
    "Message"       TEXT NOT NULL,
    "Details"       TEXT
);

-- Field definitions for the extraction structure
CREATE TABLE IF NOT EXISTS "ResponseSchema" (
    "FieldID"           INTEGER PRIMARY KEY AUTOINCREMENT,
    "FieldName"         TEXT NOT NULL,
    "FieldDescription"  TEXT,
    "FieldDataType"     TEXT NOT NULL
);

-- One row per logical document or chunk
CREATE TABLE IF NOT EXISTS "documents" (
    "document_id"       INTEGER PRIMARY KEY AUTOINCREMENT,
    "title"             TEXT NOT NULL,
    "filename"          TEXT NOT NULL,
    "source_path"       TEXT NOT NULL,
    "pdf_path"          TEXT,
    "page_count"        INTEGER NOT NULL,
    "import_date"       TIMESTAMP NOT NULL,
    "source_database"   TEXT,
    "notes"             TEXT
);

-- One row per page, with OCR content
CREATE TABLE IF NOT EXISTS "pages" (
    "page_id"           INTEGER PRIMARY KEY AUTOINCREMENT,
    "document_id"       INTEGER NOT NULL,
    "page_number"       INTEGER NOT NULL,
    "content"           TEXT NOT NULL,
    "content_type"      TEXT DEFAULT 'text',
    "pdf_page_path"     TEXT,
    "image_page_path"   TEXT,
    "import_date"       TIMESTAMP NOT NULL,
    "embedding_status"  TEXT,
    "embedding_date"    TIMESTAMP,
    "embedding_model"   TEXT,
    FOREIGN KEY("document_id") REFERENCES "documents"("document_id")
);

-- Full-text search index on page content
CREATE VIRTUAL TABLE pages_fts USING fts5(
    content,
    content='pages',
    content_rowid='page_id'
);

-- FTS5 internal tables (auto-created by pages_fts)
CREATE TABLE 'pages_fts_config'(k PRIMARY KEY, v) WITHOUT ROWID;
CREATE TABLE 'pages_fts_data'(id INTEGER PRIMARY KEY, block BLOB);
CREATE TABLE 'pages_fts_docsize'(id INTEGER PRIMARY KEY, sz BLOB);
CREATE TABLE 'pages_fts_idx'(segid, term, pgno, PRIMARY KEY(segid, term)) WITHOUT ROWID;

-- ===========================================================
-- Desktop Tables (added by Fact Extract Desktop)
-- ===========================================================

-- User-created collections
CREATE TABLE IF NOT EXISTS "fe_collections" (
    "collection_id"     INTEGER PRIMARY KEY,
    "name"              TEXT NOT NULL,
    "description"       TEXT,
    "color"             TEXT,
    "created_date"      TEXT NOT NULL,
    "modified_date"     TEXT NOT NULL
);

-- Pages and documents assigned to collections
CREATE TABLE IF NOT EXISTS "fe_collection_items" (
    "item_id"           INTEGER PRIMARY KEY,
    "collection_id"     INTEGER NOT NULL,
    "document_id"       INTEGER NOT NULL,
    "page_id"           INTEGER,
    "added_date"        TEXT NOT NULL,
    FOREIGN KEY("collection_id") REFERENCES "fe_collections"("collection_id") ON DELETE CASCADE
);

-- User notes on pages and documents
CREATE TABLE IF NOT EXISTS "fe_notes" (
    "note_id"           INTEGER PRIMARY KEY,
    "document_id"       INTEGER NOT NULL,
    "page_id"           INTEGER,
    "note_text"         TEXT NOT NULL,
    "created_date"      TEXT NOT NULL,
    "modified_date"     TEXT NOT NULL
);

-- Page bookmarks
CREATE TABLE IF NOT EXISTS "fe_bookmarks" (
    "bookmark_id"       INTEGER PRIMARY KEY,
    "document_id"       INTEGER NOT NULL,
    "page_id"           INTEGER,
    "label"             TEXT,
    "created_date"      TEXT NOT NULL
);

-- Tag definitions
CREATE TABLE IF NOT EXISTS "fe_tags" (
    "tag_id"            INTEGER PRIMARY KEY,
    "name"              TEXT NOT NULL UNIQUE,
    "color"             TEXT
);

-- Tag-to-document assignments
CREATE TABLE IF NOT EXISTS "fe_document_tags" (
    "document_id"       INTEGER NOT NULL,
    "tag_id"            INTEGER NOT NULL,
    PRIMARY KEY("document_id", "tag_id"),
    FOREIGN KEY("tag_id") REFERENCES "fe_tags"("tag_id") ON DELETE CASCADE
);

-- Application settings (key-value store)
CREATE TABLE IF NOT EXISTS "fe_settings" (
    "key"               TEXT PRIMARY KEY,
    "value"             TEXT
);

-- Schema migration tracking
CREATE TABLE IF NOT EXISTS "fe_schema_version" (
    "version"           INTEGER PRIMARY KEY,
    "applied_date"      TEXT NOT NULL,
    "description"       TEXT
);

-- Tracks pages where OCR content has been corrected
CREATE TABLE IF NOT EXISTS "fe_edited_pages" (
    "page_id"           INTEGER PRIMARY KEY
);

-- Tracks summary fields where extracted values have been corrected
CREATE TABLE IF NOT EXISTS "fe_edited_fields" (
    "page_id"           INTEGER NOT NULL,
    "field_id"          INTEGER NOT NULL,
    PRIMARY KEY("page_id", "field_id")
);

-- User-defined custom field definitions
CREATE TABLE IF NOT EXISTS "fe_custom_fields" (
    "field_id"          INTEGER PRIMARY KEY,
    "field_name"        TEXT NOT NULL UNIQUE,
    "field_description" TEXT,
    "field_data_type"   TEXT DEFAULT 'string',
    "created_date"      TEXT NOT NULL
);

-- Custom field values per page
CREATE TABLE IF NOT EXISTS "fe_custom_values" (
    "value_id"          INTEGER PRIMARY KEY,
    "page_id"           INTEGER NOT NULL,
    "field_id"          INTEGER NOT NULL,
    "field_value"       TEXT NOT NULL,
    "created_date"      TEXT NOT NULL,
    "modified_date"     TEXT NOT NULL,
    UNIQUE("page_id", "field_id"),
    FOREIGN KEY("field_id") REFERENCES "fe_custom_fields"("field_id") ON DELETE CASCADE
);

-- Custom vocabulary / dictionary
CREATE TABLE IF NOT EXISTS "fe_vocabulary" (
    "word"              TEXT PRIMARY KEY
);

-- ===========================================================
-- Agent Tables (MCP chatbot project planning and notes)
-- ===========================================================

-- Agent projects
CREATE TABLE IF NOT EXISTS "fe_agent_projects" (
    "project_id"        INTEGER PRIMARY KEY,
    "name"              TEXT NOT NULL,
    "goal"              TEXT NOT NULL,
    "status"            TEXT NOT NULL DEFAULT 'active'
        CHECK("status" IN ('active', 'paused', 'completed', 'archived')),
    "created_date"      TEXT NOT NULL,
    "modified_date"     TEXT NOT NULL
);

-- Project phases
CREATE TABLE IF NOT EXISTS "fe_agent_phases" (
    "phase_id"          INTEGER PRIMARY KEY,
    "project_id"        INTEGER NOT NULL,
    "phase_number"      INTEGER NOT NULL,
    "name"              TEXT NOT NULL,
    "description"       TEXT,
    "status"            TEXT NOT NULL DEFAULT 'pending'
        CHECK("status" IN ('pending', 'active', 'completed', 'skipped')),
    "created_date"      TEXT NOT NULL,
    "modified_date"     TEXT NOT NULL,
    FOREIGN KEY("project_id") REFERENCES "fe_agent_projects"("project_id") ON DELETE CASCADE
);

-- Jobs within phases
CREATE TABLE IF NOT EXISTS "fe_agent_jobs" (
    "job_id"            INTEGER PRIMARY KEY,
    "phase_id"          INTEGER NOT NULL,
    "job_number"        INTEGER NOT NULL,
    "name"              TEXT NOT NULL,
    "description"       TEXT,
    "status"            TEXT NOT NULL DEFAULT 'pending'
        CHECK("status" IN ('pending', 'active', 'completed', 'skipped', 'blocked')),
    "result_summary"    TEXT,
    "created_date"      TEXT NOT NULL,
    "modified_date"     TEXT NOT NULL,
    FOREIGN KEY("phase_id") REFERENCES "fe_agent_phases"("phase_id") ON DELETE CASCADE
);

-- Progress log entries
CREATE TABLE IF NOT EXISTS "fe_agent_progress" (
    "entry_id"          INTEGER PRIMARY KEY,
    "project_id"        INTEGER NOT NULL,
    "phase_id"          INTEGER,
    "job_id"            INTEGER,
    "entry_type"        TEXT NOT NULL DEFAULT 'action'
        CHECK("entry_type" IN ('action', 'discovery', 'decision', 'observation', 'error', 'milestone')),
    "content"           TEXT NOT NULL,
    "created_date"      TEXT NOT NULL,
    FOREIGN KEY("project_id") REFERENCES "fe_agent_projects"("project_id") ON DELETE CASCADE,
    FOREIGN KEY("phase_id") REFERENCES "fe_agent_phases"("phase_id") ON DELETE SET NULL,
    FOREIGN KEY("job_id") REFERENCES "fe_agent_jobs"("job_id") ON DELETE SET NULL
);

-- Agent sessions
CREATE TABLE IF NOT EXISTS "fe_agent_sessions" (
    "session_id"        INTEGER PRIMARY KEY,
    "project_id"        INTEGER NOT NULL,
    "label"             TEXT,
    "started_date"      TEXT NOT NULL,
    "ended_date"        TEXT,
    "summary"           TEXT,
    "status"            TEXT NOT NULL DEFAULT 'active'
        CHECK("status" IN ('active', 'completed', 'abandoned')),
    FOREIGN KEY("project_id") REFERENCES "fe_agent_projects"("project_id") ON DELETE CASCADE
);

-- Agent notes
CREATE TABLE IF NOT EXISTS "fe_agent_notes" (
    "note_id"           INTEGER PRIMARY KEY,
    "project_id"        INTEGER,
    "title"             TEXT NOT NULL,
    "content"           TEXT NOT NULL,
    "tags"              TEXT DEFAULT '[]',
    "created_date"      TEXT NOT NULL,
    "modified_date"     TEXT NOT NULL,
    FOREIGN KEY("project_id") REFERENCES "fe_agent_projects"("project_id") ON DELETE SET NULL
);

-- Full-text search index on user notes
CREATE VIRTUAL TABLE fe_notes_fts USING fts5(
    note_text,
    content='fe_notes',
    content_rowid='note_id'
);

-- FTS5 internal tables (auto-created by fe_notes_fts)
CREATE TABLE 'fe_notes_fts_config'(k PRIMARY KEY, v) WITHOUT ROWID;
CREATE TABLE 'fe_notes_fts_data'(id INTEGER PRIMARY KEY, block BLOB);
CREATE TABLE 'fe_notes_fts_docsize'(id INTEGER PRIMARY KEY, sz BLOB);
CREATE TABLE 'fe_notes_fts_idx'(segid, term, pgno, PRIMARY KEY(segid, term)) WITHOUT ROWID;

-- ===========================================================
-- Embeddings Tables (added by Fact Extract Desktop)
-- ===========================================================

-- Per-page content embedding chunks
CREATE TABLE IF NOT EXISTS "content_embeddings" (
    "chunk_id"          INTEGER PRIMARY KEY AUTOINCREMENT,
    "page_id"           INTEGER NOT NULL,
    "document_id"       INTEGER NOT NULL,
    "chunk_index"       INTEGER NOT NULL,
    "chunk_text"        TEXT NOT NULL,
    "embedding"         BLOB NOT NULL,
    "token_count"       INTEGER,
    FOREIGN KEY("page_id") REFERENCES "pages"("page_id"),
    FOREIGN KEY("document_id") REFERENCES "documents"("document_id")
);

-- Per-document summary field embeddings (concatenated field values)
CREATE TABLE IF NOT EXISTS "field_embeddings" (
    "id"                INTEGER PRIMARY KEY AUTOINCREMENT,
    "document_id"       INTEGER NOT NULL,
    "chunk_page_start"  INTEGER NOT NULL,
    "chunk_page_end"    INTEGER NOT NULL,
    "embedding"         BLOB NOT NULL,
    "composite_text"    TEXT NOT NULL,
    FOREIGN KEY("document_id") REFERENCES "documents"("document_id")
);

-- Embedding model configuration (key-value)
CREATE TABLE IF NOT EXISTS "embedding_metadata" (
    "key"               TEXT PRIMARY KEY,
    "value"             TEXT NOT NULL
);

-- ===========================================================
-- Indexes
-- ===========================================================

CREATE INDEX IF NOT EXISTS "idx_pages_documentid"
    ON "pages" ("document_id");

CREATE INDEX IF NOT EXISTS "idx_llmresults_pageid"
    ON "LLMResults" ("PageID");

CREATE INDEX IF NOT EXISTS "idx_llmresults_fieldid"
    ON "LLMResults" ("FieldID");

CREATE INDEX IF NOT EXISTS "idx_llmresults_chunkid"
    ON "LLMResults" ("ChunkID");

CREATE INDEX IF NOT EXISTS "idx_fe_bookmarks_document"
    ON "fe_bookmarks" ("document_id");

CREATE INDEX IF NOT EXISTS "idx_fe_collection_items_doc"
    ON "fe_collection_items" ("document_id");

CREATE UNIQUE INDEX IF NOT EXISTS "idx_fe_collection_items_unique"
    ON "fe_collection_items" ("collection_id", "document_id", IFNULL("page_id", 0));

CREATE INDEX IF NOT EXISTS "idx_fe_notes_document"
    ON "fe_notes" ("document_id");

CREATE INDEX IF NOT EXISTS "idx_fe_notes_page"
    ON "fe_notes" ("page_id");

CREATE INDEX IF NOT EXISTS "idx_fe_custom_values_page"
    ON "fe_custom_values" ("page_id");

CREATE INDEX IF NOT EXISTS "idx_content_embeddings_page"
    ON "content_embeddings" ("page_id");

CREATE INDEX IF NOT EXISTS "idx_fe_agent_projects_status"
    ON "fe_agent_projects" ("status");

CREATE UNIQUE INDEX IF NOT EXISTS "idx_fe_agent_phases_order"
    ON "fe_agent_phases" ("project_id", "phase_number");

CREATE INDEX IF NOT EXISTS "idx_fe_agent_phases_project"
    ON "fe_agent_phases" ("project_id");

CREATE UNIQUE INDEX IF NOT EXISTS "idx_fe_agent_jobs_order"
    ON "fe_agent_jobs" ("phase_id", "job_number");

CREATE INDEX IF NOT EXISTS "idx_fe_agent_jobs_phase"
    ON "fe_agent_jobs" ("phase_id");

CREATE INDEX IF NOT EXISTS "idx_fe_agent_progress_project"
    ON "fe_agent_progress" ("project_id");

CREATE INDEX IF NOT EXISTS "idx_fe_agent_progress_date"
    ON "fe_agent_progress" ("project_id", "created_date");

CREATE INDEX IF NOT EXISTS "idx_fe_agent_sessions_project"
    ON "fe_agent_sessions" ("project_id");

CREATE INDEX IF NOT EXISTS "idx_fe_agent_notes_project"
    ON "fe_agent_notes" ("project_id");

-- Pure SQL pivot (field names must be known in advance)
-- Replace the MAX(CASE...) lines with your actual field names
SELECT
    d.filename,
    d.document_id,
    p.page_number,
    MAX(CASE WHEN rs.FieldName = 'author' THEN lr.LLMResponse END) AS author,
    MAX(CASE WHEN rs.FieldName = 'document_date' THEN lr.LLMResponse END) AS document_date,
    MAX(CASE WHEN rs.FieldName = 'subject_summary' THEN lr.LLMResponse END) AS subject_summary,
    MAX(CASE WHEN rs.FieldName = 'key_facts' THEN lr.LLMResponse END) AS key_facts
FROM documents d
JOIN pages p ON d.document_id = p.document_id
JOIN LLMResults lr ON p.page_id = lr.PageID
JOIN ResponseSchema rs ON lr.FieldID = rs.FieldID
GROUP BY d.filename, d.document_id, p.page_number
ORDER BY d.filename, p.page_number;

model_name            -- e.g. "nomic-embed-text-v1.5"
model_file            -- e.g. "model_quantized.onnx"
embedding_dimensions  -- e.g. "768"
document_prefix       -- prefix prepended for indexing, e.g. "search_document: "
query_prefix          -- prefix prepended at query time, e.g. "search_query: "
postprocessing        -- e.g. "mean_pool+layer_norm+l2_norm"
chunk_level           -- "per_page"
chunk_strategy        -- e.g. "paragraph_aware|max_chars=2048|overlap_chars=200"
total_content_embeddings
total_field_embeddings
creation_date
factextract_version