Personal Data Hub

A local SQLite workspace for personal records: import spreadsheets, CSV, JSON and PDF tables; browse and edit them in a local Web interface; share a consistent, audited data model with CLI, REST and MCP clients. Sensitive fields are masked by default.

Python 3.11+ · version 0.1.1 · MIT · no cloud services or accounts

Install

git clone https://github.com/GuoHaoyu1688/personal-data-hub.git
cd personal-data-hub
python3 -m venv .venv
. .venv/bin/activate
pip install -e '.[dev]'

Use pip install -e . for runtime only. Dependencies provide local YAML, spreadsheet/PDF extraction, FastAPI, offline Swagger assets and the official MCP SDK. The MCP dependency is constrained to >=1.16,<2 to use official FastMCP v1. No runtime command downloads data or models. Optional dependency installation itself requires package access.

Related MCP server: Living Atlas MCP

Quick start: three commands

From the installed checkout:

hub --data-dir ./data/demo import examples/projects.xlsx --table projects --dry-run --json
hub --data-dir ./data/demo import examples/projects.xlsx --table projects --yes --json
hub --data-dir ./data/demo query projects --where 'status=active' --json

The first command displays suggested column mappings, a five-row sample and validation errors without importing records. The second inserts 2 fictional projects; repeating it skips 2 unchanged rows. The query returns the active Cooling study project. Initialization and additive migrations run automatically; an explicit hub init is also available.

To open the working Web interface:

hub --data-dir ./data/demo serve --host 127.0.0.1 --port 8766

Visit the local workspace or the offline API documentation. The Web UI supports table navigation, text search, safe expression filters, add, inline edit with stale-edit protection, delete, audit history, sensitive-value reveal, due dates and CSV export matching the active search/filter. Mobile layouts keep wide tables scrollable.

Built-in tables

Every record includes id, created_at, updated_at, source_file and actor. IDs are UUIDs unless restored from an export. Business schemas are YAML:

These are general records, not medical/financial calculations or eligibility decisions. All examples are fictional. Example contact values use the reserved .invalid domain and explicit fictional markers.

Paths and configuration

Default data directory: ./data. --data-dir overrides HUB_DATA_DIR, which overrides TOML data_dir. The database is <data-dir>/hub.db. First startup copies packaged built-in schemas into <data-dir>/schemas; later startups preserve customized files. --schema-dir DIR or TOML schema_dir selects another directory. Root schemas/ contains editable copies and can be selected explicitly with --schema-dir ./schemas.

# Use --config examples/config.toml
[hub]
data_dir = "./data/example"
timezone = "Asia/Shanghai"
actor = "agent:example"

Timestamps are UTC ISO 8601. Naive datetimes, due-date calculations and backup filenames use the configured timezone, default Asia/Shanghai. Use local disk storage: SQLite WAL with a 30-second busy timeout is not designed for network filesystem sharing. Each mutation and its audit event commit in one transaction.

Extend schemas without losing data

Copy examples/custom_schema.yaml to <data-dir>/schemas/reading_list.yaml, then run hub init or restart the server. Accepted column types: text, integer, decimal, boolean, date, datetime. Options include required, default, sensitive, label and aliases. Each schema defines table, columns, unique and optionally due_fields (date columns).

table: reading_list
label: Reading List
unique: [title]
columns:
  title: {type: text, required: true}
  status: {type: text, default: planned}
  private_note: {type: text, sensitive: true}

Migrations only add tables/columns. Removed columns remain available through the effective persisted schema. Existing types, unique keys and required constraints cannot change. New required fields on populated tables need a valid default. Default backfills are audited as schema:migration; mandatory defaults cannot be reverted to invalid null values. Previously sensitive fields remain sensitive even if a YAML flag is removed. Restart long-running Web/MCP processes after changing schemas. Backups contain the effective schemas, including retained fields.

Identifiers are limited to lowercase letters, digits and underscores. Unique keys must be nonempty on each record; import requires a declared unique key for deterministic upsert. Monetary values use decimal strings in SQLite, with exact Decimal comparisons; quote decimal defaults in YAML to avoid YAML float conversion.

Import mappings and deduplication

Supported input: UTF-8 CSV (BOM accepted), JSON arrays, .xlsx worksheets and text-based PDF tables. XLSX uses the first worksheet unless --sheet NAME is supplied. PDFs use pdfplumber's ruled-table extraction; scanned/image-only PDFs need separate OCR. Multiple PDF tables must have the same header row. Legacy .xls, arbitrary layout guessing and OCR are not included.

hub import examples/documents.pdf --table documents --dry-run --json
hub import examples/documents.pdf --table documents --yes --json
hub import examples/projects.csv --table projects --map examples/project-map.json --yes --json

--map is a JSON source-header → target-field object. Automatic suggestions compare normalized field names, labels and aliases with a 0.72 similarity threshold; inspect the preview before applying. Preview returns mapping, unmapped_columns, sample, rows_in, valid_rows, errors, and file sha1. Sensitive sample fields are masked. Unknown source columns are omitted, not displayed as raw sample values.

No import writes records unless --yes is present; --dry-run always wins. To bind application to a reviewed file, pass the preview's --expected-sha1. All records validate before a single atomic transaction. Unique-key matches update mapped business fields; omitted fields survive. Unchanged rows skip without a new change event. Reimporting the same XLSX/PDF does not duplicate rows. Each successful application records a file-level import log, even if no business rows change.

JSON/CSV exports include record IDs and metadata. Imports preserve supplied IDs for newly inserted rows; creation/update timestamps and import provenance describe the new import rather than claiming original provenance. Use a backup to restore the exact database/history. Masked exports are safe for review but cannot be reimported as real sensitive values: [REDACTED] is rejected. Explicitly use --reveal only when you need a private full-data transfer.

Safe queries

hub query projects --where 'start_date>=2026-01-01 AND status=active' --fields name,role,status --limit 50 --json
hub query profile --where "name~'Ali'" --json
hub query income --where 'amount>=1000.01' --reveal --json

Grammar: FIELD OP VALUE, optionally joined by AND; operators = != > < >= <= ~. Quote values containing spaces. ~ is a literal substring match: % and _ are escaped. =null/!=null test nulls. Raw SQL, OR, joins and arbitrary expressions are not accepted. Columns are validated against the schema and values are bound parameters. Numeric comparisons use Python Decimal rather than binary floats. Default search excludes sensitive fields; filtering those fields requires explicit --reveal to avoid leaking private values through match results. Results use stable created-at/ID ordering with --limit and --offset.

CLI reference

Global options may appear before or after subcommands: --data-dir, --schema-dir, --config, --actor, --json, --quiet, --reveal, --progress, --version.

Success: {"ok":true,"data":...,"warnings":[]}. Failure: {"ok":false,"error":{"code":"...","message":"..."}}. Exit codes: 0 success, 1 I/O/runtime, 2 arguments/conflict, 3 missing data. --quiet suppresses successful human output; --json wins. --progress emits a start message on stderr. No CLI command prompts. Long imports are synchronous; very large files can exceed the small-data two-second target.

Audit and reversal

Every inserted, changed or deleted business record has a changes entry with before/after snapshots, actor and timestamp. Default CLI/MCP/REST history masks sensitive fields in both snapshots. Idempotent no-op updates do not create extra change events. hub revert ID --yes reverses insertion, update or deletion and audits that inverse with a reverses link. It rejects already-reverted changes, reused IDs, key conflicts and intervening edits; it does not silently overwrite newer work.

Deleted values remain in audit and backups. Masking is a presentation control, not encryption or secure deletion. A process with filesystem access can read the entire database. Use filesystem permissions and operating-system disk encryption for private records.

REST and MCP

The Web API shares the CLI's core and masking:

• GET /api/tables, GET /api/schema/{table}.

• GET/POST /api/rows/{table}; GET/PATCH/DELETE /api/rows/{table}/{id}.

• GET /api/history, POST /api/revert/{change_id}?confirm=true.

• GET /api/due, GET /api/export/{table}?format=csv.

• GET /openapi.json, /docs (Swagger assets served locally).

Mutations accept X-Actor; use reveal=true explicitly to reveal sensitive results. Deletion requires confirm=true. Optionally set HUB_TOKEN before hub serve and send Authorization: Bearer <token> on all /api/ calls. The Web UI's Access token field keeps it only in memory. There is no login/multi-user permission system. Host checks and cross-origin-write rejection protect local browser access; do not expose this service to the internet.

Eight MCP tools: list_tables, describe_table, query_rows, insert_row, update_row, delete_row, list_due_items, get_history. Typed signatures expose JSON Schemas through the official SDK. Mutation descriptions state side effects, and delete requires confirm=true. All default to masked responses.

Claude Desktop or a generic stdio MCP client:

{"mcpServers":{"personal-data":{"command":"/workspace/apps/personal-data-hub/.venv/bin/hub","args":["--data-dir","/workspace/data/personal-data-hub","--actor","agent:example","mcp"]}}}

Codex CLI:

[mcp_servers.personal_data]
command = "/workspace/apps/personal-data-hub/.venv/bin/hub"
args = ["--data-dir", "/workspace/data/personal-data-hub", "--actor", "agent:example", "mcp"]

See AGENTS.md for headless installation, parameter schemas and recipes.

Backups and recovery

hub backup uses SQLite's online backup API, including committed WAL content, and stores hub.db, effective schemas/*.yaml and a manifest in a dated ZIP. Filenames include seconds/microseconds to avoid collisions. Backups are unmasked private data, including deleted audit snapshots.

To restore, stop all clients, extract into a new empty data directory, then run hub --data-dir /path/to/restored init and query a known record. Do not mix restored database files with old -wal/-shm files. Export/backup refuses to overwrite the active database. Back up before updates, deletion, reversal and schema changes.

Test

ruff check .
ruff format --check .
pytest -q --cov=personal_data_hub --cov-report=term-missing

Tests cover actual fictional XLSX/PDF extraction and repeat imports, mappings, rollback, additive schema preservation, injection probes, masked interface/history/export responses, exact decimals, due reminders, online backup restore, REST/OpenAPI, all MCP tools with the official in-memory client and real stdio initialization. CI runs Ubuntu Python 3.11/3.12. To regenerate examples: python examples/generate_fixtures.py with dev dependencies installed.

FAQ and roadmap

• Why are secrets still in the DB? Masking prevents accidental display; no encrypted-database or multi-user system is claimed.

• Why did a PDF fail? The extractor needs actual table text/lines. Review and normalize complex or scanned documents before import.

• Can I remove a schema column? Runtime migrations only add. Use a separately reviewed offline migration and backup for destructive changes.

• Can I edit a hidden value in the browser? Enable Reveal explicitly; otherwise hidden cells are disabled so placeholders never overwrite private data.

• Why does revert report a conflict? The record changed since that audit event. Inspect history and choose a newer compatible change or restore a backup to a separate path.

• What is next? Larger-file streaming, import preview UI uploads, richer schema constraints and optional encrypted storage are potential future work. The current CLI handles imports; the Web UI handles browsing and editing.

Local verification on 2026-10-15 (Python 3.12.13): 34 tests passed, 92% coverage, Ruff lint/format passed, README quick start executed, and wheel resources verified. Browser checks passed at 1440×980 and 390×844 with no console errors or external requests, including inline editing, masked filtered export and offline Swagger. One upstream Starlette TestClient deprecation warning for httpx remains; it does not affect runtime behavior. CI configuration is provided; remote CI execution is not claimed here.

MIT license · Third-party notices · Changelog · Agent manual