Database Schema (SQLite)

Relevant source files

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

• src/lib/db.ts
• src/pages/SettingsPage.tsx
• src/pages/Duplicates.tsx
• src/main.tsx
• README.md

Introduction

The Pixona application utilizes a client-side database for persisting photo metadata, application settings, and cached data. This enables fast local indexing, organization, and retrieval of information without relying on a server. While the project roadmap mentions a future migration to SQLite, the current implementation uses the browser's built-in IndexedDB API. This system is encapsulated within the PhotoDatabase class, providing a structured and asynchronous interface for all database interactions.

The database, named pixona_photos_db, is initialized when the application starts. It is designed to store structured data across several object stores, including photos, albums, people, places, settings, and cache. This architecture supports features like settings persistence, duplicate detection, and eventually, face recognition and location-based grouping.

Sources: src/lib/db.ts:2-7, src/main.tsx:8-14, README.md:19, 103

Architecture

The database architecture is centered around the PhotoDatabase class, which acts as a singleton wrapper around the IndexedDB API. This class is responsible for initializing the database, defining the schema, and providing all CRUD (Create, Read, Update, Delete) methods for interacting with the data.

Sources: src/lib/db.ts:60-359

Initialization Flow

The database is initialized once when the React application is mounted. The db.init() method is called, which opens a connection to the pixona_photos_db IndexedDB database and creates the necessary object stores and indexes if they don't exist.

This diagram illustrates the database initialization process on application startup.

PhotoDatabase Class

The PhotoDatabase class provides an abstraction layer over the low-level IndexedDB API, offering a promise-based interface for easier use throughout the application.

This diagram shows the structure of the PhotoDatabase class and its key methods.

Database Schema

The database consists of six main object stores, each serving a distinct purpose within the application. The photos store is the central entity, linked to by other stores like albums and people.

Sources: src/lib/db.ts:4-11

This Entity-Relationship diagram visualizes the object stores and their intended relationships.

Object Stores and Indexes

The following tables detail the structure of each object store and its defined indexes.

photos Store

The primary store for all photo and video metadata.

Field	Type	Description
id	string	Primary Key. A unique identifier for the photo.
path	string	The absolute file path to the photo on the user's system.
name	string	The file name.
type	'image' | 'video'	The type of the media file.
size	number	File size in bytes.
created	Date	The creation timestamp of the file.
modified	Date	The last modified timestamp of the file.
imported	Date	The date the photo was imported into Pixona.
hash	string	A perceptual hash for duplicate detection.
thumbnailData	string	A base64 encoded string for the thumbnail.
albumIds	string[]	Array of album IDs this photo belongs to.
peopleIds	string[]	Array of person IDs featured in this photo.

Indexes:

• path (unique): For quickly checking if a photo already exists.
• created: For sorting and filtering by date.
• modified: For sorting and filtering by date.
• imported: For sorting and filtering by import date.
• hash: For finding photos with the same perceptual hash (duplicates).
• type: For filtering by media type (image/video).

Sources: src/lib/db.ts:13-37, src/lib/db.ts:83-90

settings Store

A key-value store for persisting application settings.

Field	Type	Description
key	string	Primary Key. The name of the setting (e.g., 'theme', 'autoScan').
value	any	The value of the setting.

Sources: src/lib/db.ts:54-57, src/lib/db.ts:100-102

Other Stores

• albums: Stores album information, linking to photos via photoIds.
• people: Stores information about detected people, linking to photos.
• places: Stores location data, linking to photos.
• cache: A generic key-value store for caching data, such as thumbnails.

Sources: src/lib/db.ts:39-52, src/lib/db.ts:92-99, src/lib/db.ts:104-107

Data Flow and Usage Examples

The db instance is used across the application to manage state and data.

Saving Settings

The SettingsPage component uses the db.setSetting method to persist user configuration changes.

This sequence diagram shows the flow of saving a setting.

Scanning for Duplicates

The Duplicates page fetches all photo metadata to perform a scan. This relies on db.getAllPhotos to retrieve the necessary data for the hashing algorithm.

// src/pages/Duplicates.tsx:28-30
const scanForDuplicates = async () => {
  // ...
  const photos = await db.getAllPhotos();
  // ...
};

Sources: src/pages/Duplicates.tsx:29, src/lib/db.ts:167-178

Clearing the Cache

The settings page provides an option to clear all data from the database, which is useful for clearing the thumbnail cache and other stored information.

// src/pages/SettingsPage.tsx:84-95
const handleClearCache = async () => {
  const confirmed = window.confirm("Are you sure you want to clear the thumbnail cache?");
  if (confirmed) {
    try {
      await db.clearAllData();
      // ...
    } catch (error) {
      // ...
    }
  }
};

The clearAllData method iterates through all defined object stores and clears them.
Sources: src/pages/SettingsPage.tsx:84-95, src/lib/db.ts:298-310

Summary

The application's database is a robust client-side storage system built on IndexedDB. It is well-structured with a clear separation of concerns through different object stores and is managed by the PhotoDatabase class, which provides a clean, asynchronous API. This setup is fundamental for storing photo metadata, managing application settings, and supporting core features like duplicate scanning. While a future migration to SQLite is planned, the current IndexedDB implementation is fully functional and integral to the application's operation.

Sources: src/lib/db.ts, README.md