Relational Data & Metadata (SQLite)
Relevant source files
The following files were used as context for generating this wiki page:
Relational data and metadata in Archivum are managed via a centralized SQLite database, serving as the operational backbone for user management, page metadata, ingestion logs, and "Life OS" entities. While Markdown files remain the canonical source of truth for wiki content, SQLite provides the high-performance indexing and relational structure required for the REST API, MCP server, and search functionalities.
The database is designed with multi-tenancy in mind, utilizing a wiki_id field across all primary tables to support future multi-user product scaling. It also implements SQLite FTS5 for efficient keyword-based searching alongside the vector-based semantic search provided by Qdrant.
Sources: archivum-prd-v1.0.md:9, apps/backend/archivum/db/sqlite.py:17-40, README.md:architecture
Database Architecture and Schema
Archivum utilizes aiosqlite as an asynchronous wrapper for SQLite operations, employing WAL (Write-Ahead Logging) mode for improved concurrency. The schema is initialized through init_db and includes tables for core operations and specialized "Life OS" tracking.
Core Operational Tables
The core schema tracks users, wiki pages, hierarchy (folders), and administrative metadata like share links and ingest history.
| Table Name | Description | Key Fields |
|---|---|---|
users | User accounts and roles. | username, password_hash, role |
pages | Metadata for wiki pages (source of truth is still .md). | slug, title, tags, authored_by |
folders | Logical directory structure for pages. | path, name |
share_links | Tokens for public page or query sharing. | token, type, target_id, expires_at |
ingest_log | History of file and URL ingestion runs. | source_type, source_path, status |
page_write_jobs | Queue for background page generation/writing. | status, result_slug, error |
Sources: apps/backend/archivum/db/sqlite.py:17-100, README.md:architecture
Entity Relationship Diagram
The following diagram illustrates the primary relationships between users, pages, and operational metadata.
Full-Text Search (FTS5)
SQLite FTS5 is used to provide keyword-based search capabilities. A virtual table pages_fts is kept in sync with the pages table through database triggers, ensuring that title, content, and tags are searchable immediately upon ingestion or editing.
CREATE VIRTUAL TABLE IF NOT EXISTS pages_fts USING fts5(
slug,
title,
content,
tags,
content='pages',
content_rowid='id'
);
Triggers pages_ai, pages_ad, and pages_au handle INSERT, DELETE, and UPDATE operations respectively to maintain the virtual table.
Sources: apps/backend/archivum/db/sqlite.py:61-82
Life OS Data Layer
The database contains specialized tables to support "Life OS" features, providing normalized storage for projects, tasks, decisions, and people. This allows the system to treat these as structured entities while maintaining links to their corresponding wiki pages.
| Table Name | Purpose |
|---|---|
life_projects | Tracks active/inactive projects and associated slugs. |
life_tasks | Task management with status, due dates, and project links. |
life_decisions | Ledger of decisions made, including rationales. |
life_people | Registry of people and their summaries. |
life_areas | Organizational areas (e.g., "Personal", "Work"). |
agent_activity | Audit log of actions taken by the AI agent. |
Sources: apps/backend/archivum/db/sqlite.py:115-180, Progress.md:Current Codebase Status
Data Access and Integration
SQLite interaction is primarily encapsulated within apps/backend/archivum/db/sqlite.py. This module provides CRUD operations used by the REST API, MCP Server, and Ingest Pipeline.
Ingestion Flow
During ingestion, the pipeline interacts with SQLite at multiple stages to log progress and persist extracted metadata.
Key Integration Functions
upsert_page: Synchronizes page metadata between the Markdown filesystem and the database. Used in both the manual editor and the AI ingestion pipeline.
Sources: apps/backend/archivum/db/sqlite.py:270-300, apps/backend/archivum/api/pages.py:328-340search_pages_fts: Executes keyword searches using thesnippetfunction to return relevant text excerpts.
Sources: apps/backend/archivum/db/sqlite.py:465-484create_share_link: Generates unguessable 32-byte tokens for sharing wiki content externally.
Sources: apps/backend/archivum/db/sqlite.py:488-500, archivum-prd-v1.0.md:11
Summary
The SQLite implementation in Archivum provides a robust relational layer for metadata that complements the local-first Markdown storage. By centralizing operational data, search indexes, and Life OS entities in a single SQLite file, Archivum maintains high performance and simple deployment through Docker volumes, while providing the structured API access required by modern AI agents and MCP clients.
Sources: README.md:Architecture, Progress.md:MVP Target