Database Schema

Relevant source files

The following files were used as context for generating this wiki page:

• CLAUDE.md
• ios-shell/www/app.js
• prisma.config.ts
• e2e/prepare-db.ts
• tests/helpers/testDb.ts
• src/lib/mcp/server.ts
• src/app/(app)/documents/page.tsx/documents/page.tsx)

Hearth uses a relational database to manage household records, documents, and system configurations. The primary implementation utilizes SQLite via Prisma, supporting both a server-side deployment and a standalone mobile mode with encrypted local storage. The schema facilitates shared household visibility where all members see the same records, while roles gate specific administrative actions.

The system manages core entities such as Contracts and Products (Warranties), along with opt-in modules for Travel, Home (Properties), Vehicles, Inventory, and Wealth. Documents serve as a cross-cutting concern, attaching to various record types or residing in a central "inbox" for later classification.

Sources: CLAUDE.md:25-27, PRODUCT.md:38-40

Architecture and Data Storage

Hearth implements its data layer using Prisma 7 with SQLite via the @prisma/adapter-libsql driver. Configuration is managed through prisma.config.ts, which defines the schema location and migrations path.

This diagram shows the relationship between the application logic and the underlying SQLite storage via Prisma.
Sources: CLAUDE.md:25-27, prisma.config.ts:6-15

Mobile Standalone Schema

In standalone mobile mode, the app uses @capacitor-community/sqlite to manage an encrypted local database. This schema mirrors the server-side structure to maintain feature parity for offline use.

Table Name	Description	Key Columns
local_profile	Local user settings	id, display_name, default_currency
contracts	Household contracts	title, provider, category, status, cost
products	Tracked warranties	description, manufacturer, warranty_end_date
vehicles	Vehicle records	label, license_plate, vin, rego_expiry
properties	Real estate records	label, occupancy_status, estimated_value
documents	Filed document metadata	owner_type, owner_id, storage_key, mime_type
inbox_documents	Unfiled documents	filename, status, sha256

Sources: ios-shell/www/app.js:69-247

Core Entities and Relationships

The database is structured around central records that can own multiple documents. Most tables include audit fields such as createdById, updatedAt, and deletedAt for soft-deletion support.

This ER diagram illustrates the primary ownership and linkage between household modules and document storage.
Sources: src/app/(app)/documents/page.tsx:42-263, ios-shell/www/app.js:77-247

Document Storage and Search

Documents are stored with generated UUID filenames on disk to prevent path traversal, while metadata is maintained in the database. The system utilizes SQLite FTS5 (Full-Text Search) for document searching.

• FTS Integration: The database includes a document_search_fts virtual table.
• Sync Triggers: 27 SQL triggers ensure that search indices remain in sync across 9 different document source tables (e.g., product_documents, vehicle_item_documents).
• Version Control: The documents table supports versioning via supersedes_id and is_head flags.

Sources: CLAUDE.md:92-95, e2e/prepare-db.ts:39-86, ios-shell/www/app.js:231-247

Wealth and Portfolios

The Wealth module does not store cost basis; it recomputes it from the trade history.

• Trades: Records units, price_per_unit, and fees.
• Holdings: Aggregates trades to show units, average_price, and market_price.
• Portfolios: Groups holdings and defines the base currency.

Sources: CLAUDE.md:89-91, ios-shell/www/app.js:189-224

System Metadata and State

Hearth tracks module enablement and schema versions through specialized metadata tables.

• ModuleEnablement: A table (per household on server, per device in standalone) that toggles the visibility of opt-in modules like TRAVEL, HOME, and VEHICLES.
• Schema Metadata: Standalone mode uses a schema_metadata table to track the internal standalone_schema_version.
• Reminder Delivery Logs: Tracks notification history including threshold_days, channel (email, push, webhook), and sent_at timestamps.

Sources: CLAUDE.md:52-54, ios-shell/www/app.js:248-256, src/lib/mcp/server.ts:241-246

Summary

The Hearth database schema is a relational SQLite structure managed by Prisma. It supports a diverse range of household management modules through a normalized table structure, while centralizing document management via specialized tables and FTS5 search. The design ensures that data is shared household-wide, with soft-deletion and comprehensive audit metadata integrated into every major record type. Standalone mobile deployments maintain parity through a mirrored, encrypted SQLite schema.

Sources: CLAUDE.md:25-30, PRODUCT.md:65-67, ios-shell/www/app.js:69-75