DB Layer CRUD Patterns

Relevant source files

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

• CLAUDE.md
• src/db/connection.ts
• src/db/trees/files.ts
• src/db/trees/sources.ts
• src/db/trees/places.ts
• src/managers/SourceWorkspaceManager.ts

The database layer in Vata follows a strict, predictable pattern for interacting with SQLite via the Tauri SQL plugin. It is designed as a local-first system using a two-database architecture: a system.db for application metadata and individual <tree-name>.db files for genealogical data.

The CRUD (Create, Read, Update, Delete) patterns ensure type safety, data integrity through foreign keys and PRAGMAs, and a clean separation between raw database rows and public domain types used by the React frontend.

Sources: CLAUDE.md:46-55, src/db/connection.ts:258-271

Core Architectural Principles

Vata enforces several strict rules at the database layer to maintain code quality and performance:

• No SELECT *: All queries must explicitly list the columns they require. Sources: CLAUDE.md:144-158, src/db/trees/sources.ts:46
• Two-Context Isolation: The app separates the "picker" context (System DB) from the "inside shell" context (Tree DB). Sources: CLAUDE.md:36-41
• Type Mapping: Every entity file defines a Raw* interface for snake_case database rows and a public camelCase domain type. Sources: CLAUDE.md:89-94
• Entity ID Convention: Primary keys are stored as INTEGER but exposed to the UI as formatted strings (e.g., I-0001 for Individuals, S-0001 for Sources). Sources: CLAUDE.md:75-87

Data Flow Overview

The following diagram illustrates how data flows from the UI through managers to the DB layer:

Database Initialization and Connection

Connections are managed in src/db/connection.ts. The system applies specific PRAGMAs to every connection to optimize for local-first desktop performance.

PRAGMA	Value	Purpose
journal_mode	WAL	Enables Write-Ahead Logging for concurrency
synchronous	NORMAL	Balances safety and write performance
foreign_keys	ON	Enforces relational integrity
busy_timeout	5000	Wait time for locked databases

Sources: src/db/connection.ts:10-17

Connection Management Flow

Implementation Patterns

Each entity (Sources, Places, Files, etc.) follows a standard implementation structure.

1. Type Mapping and Explicit Columns

Raw rows are mapped to domain objects immediately after selection.

// Example from src/db/trees/sources.ts
function mapToSource(raw: RawSource): Source {
  return {
    id: formatEntityId('S', raw.id),
    title: raw.title,
    // ... other fields
  };
}

Sources: src/db/trees/sources.ts:32-44, src/db/trees/files.ts:35-49

2. Standard Read Operations

Read functions use explicit column lists and the db.select method.

• getAll*: Fetches all records with a standard ORDER BY.
• get*ById: Fetches a single record, parsing the formatted string ID back to an integer.

Sources: src/db/trees/sources.ts:54-68, src/db/trees/places.ts:145-156

3. Creation and Update Logic

Creation functions return the new formatted ID. Update functions build dynamic SQL strings to allow partial updates (patching) while ensuring the updated_at timestamp is refreshed.

CRUD Reference Table

Entity	Primary File	ID Prefix	Key CRUD Functions
Individuals	individuals.ts	I	createIndividual, getIndividualById
Sources	sources.ts	S	getAllSources, createSource, searchSources
Places	places.ts	P	getPlaceHierarchy, updatePlaceFullNames, createPlace
Files	files.ts	N/A	addFileToSource, getFilesBySourceId, createFile

Sources: CLAUDE.md:75-84, src/db/trees/sources.ts, src/db/trees/places.ts, src/db/trees/files.ts

Business Logic Orchestration

While the DB layer handles raw persistence, Managers coordinate complex operations involving multiple tables. The SourceWorkspaceManager, for example, orchestrates the creation of an event, its participants, the associated individuals, and the citation links in a single logical workflow.

Conclusion

The DB layer CRUD patterns in Vata prioritize predictability and data integrity. By enforcing explicit column selection, strict type mapping, and centralized connection management with optimized SQLite PRAGMAs, the application maintains a robust foundation for local-first genealogical data management.